Annotation of wpscripts/ezreturn/dbops.pm, Revision 1.3
1.1 yason 1: #!/usr/bin/perl
2: use DBI;
3: use strict;
4: #use conv; # to_local()
5: package dbops;
6:
7: our $EPD_ZAYAVKA_T = 0;
8: our $EPD_PROBA_T = 1;
9: our $WP_ZAYAVKA_T = 0;
10: our $WP_PROBA_T = 1;
11:
12: our $EPD_TABLE = 'T$_EPD_File';
13: our $WP_TABLE = 'File_zayavki';
14:
1.2 yason 15: our $server = "pdc";
16: our $user = "sa";
17: our $pass = "aekghbynflvby";
1.1 yason 18: our $dsn = "DBI:Sybase:server=$server";
19: our $dbh;
20: our $sth;
21:
22: sub connect()
23: {
24: $dbh = DBI->connect($dsn, $user, $pass) or die "dbops::connect() unable to connect to server $DBI::errstr";
25: print "[dbops::connect] established database connection\n";
26: }
27:
28:
29: sub select()
30: {
1.3 ! yason 31: $dbh->do("use full_print");
1.1 yason 32: }
33:
34:
35: sub disconnect()
36: {
37: $dbh->disconnect();
38: print "[dbops::disconnect] closing database connection\n";
39: }
40:
41:
42: sub getall()
43: {
44: # files arrays
45: my @ID;
46: my @file_type;
47: my @file_name;
48: my @file_retpath;
49:
50: # for temp copies
51: my @tmp_ID;
52: my @tmp_file_type;
53: my @tmp_file_name;
54: my @tmp_file_retpath;
55:
56: my @db_path;
57: my @ab_manager;
58: my @db_ftype; # file type in db
59: my $i;
60:
61: # *****
62: # Work in upload_files table.
63: # select wpz and wpp files, if any
64: $sth = $dbh->prepare("SELECT ID,type_file,path,ab_manager FROM $WP_TABLE " .
65: "WHERE ab_want=1 AND type_file IN(0,2)")
66: or die("dbops::getall()");
67: $sth->execute() or die "dbops::getall() execute query; error $DBI::errstr";
68:
69: $i = 0;
70: while( ($ID[$i], $db_ftype[$i], $db_path[$i], $ab_manager[$i]) = $sth->fetchrow_array() ) {
71:
72: # truncate spaces in file name
73: while( index($db_path[$i], ' ', 0) != -1 ) {
74: chop($db_path[$i]);
75: }
76:
77: # extract basename
78: $file_name[$i] = substr( $db_path[$i], rindex($db_path[$i], '\\') + 1);
79:
80: # return path is based on ab_manager name (appended to /WIDEPRINT/Xxxxxxx)
81: $file_retpath[$i] = ($db_ftype[$i] == 0 ? "/WIDEPRINT/Заявки/$ab_manager[$i]" :
82: "/WIDEPRINT/Цветопробы/$ab_manager[$i]");
83:
84: # hardcode file_type
85: $file_type[$i] = ($db_ftype[$i] == 0 ? "wpz" : "wpp");
86:
87: # print "ID=$ID[$i] db_ftype=$db_ftype[$i] db_path=$db_path[$i] ab_manager=$ab_manager[$i]\n";
88: # print "file_name=$file_name[$i] file_retpath=$file_retpath[$i] file_type=$file_type[$i]\n";
89:
90: $i++;
91: }
92: # strip last elements in each array
93: $#ID--;
94: $#db_ftype--;
95: $#db_path--;
96: $#ab_manager--;
97: # $#file_name--;
98: # $#file_retpath--;
99: # $#file_type--;
100:
101: $sth->finish();
102:
103: # preserve temporary copy
104: @tmp_ID = @ID;
105: @tmp_file_type = @file_type;
106: @tmp_file_name = @file_name;
107: @tmp_file_retpath = @file_retpath;
108:
109: @ID = ( );
110: @file_type = ( );
111: @file_name = ( );
112: @file_retpath = ( );
113:
114: # *****
115: # Work in T$_EPD_File table
116: $sth = $dbh->prepare("SELECT ID,file_type,win_path,ab_manager FROM $EPD_TABLE " .
117: "WHERE ab_want=1 AND file_type IN(0,2)")
118: or die("dbops::getall()");
119: $sth->execute() or die "dbops::getall() execute query; error $DBI::errstr";
120:
121: $i = 0;
122: while( ($ID[$i], $db_ftype[$i], $db_path[$i], $ab_manager[$i]) = $sth->fetchrow_array() ) {
123:
124: # truncate spaces in file name
125: while( index($db_path[$i], ' ', 0) != -1 ) {
126: chop($db_path[$i]);
127: }
128:
129: # extract basename
130: $file_name[$i] = substr( $db_path[$i], rindex($db_path[$i], '\\') + 1);
131:
132: # return path is based on ab_manager name (appended to /WIDEPRINT/EPD_Xxxxxxx)
133: $file_retpath[$i] = ($db_ftype[$i] == 0 ? "/WIDEPRINT/EPD_Заявки/$ab_manager[$i]" :
134: "/WIDEPRINT/EPD_Цветопробы/$ab_manager[$i]");
135:
136: # hardcode file_type
137: $file_type[$i] = ($db_ftype[$i] == 0 ? "epdz" : "epdp");
138:
139: # print "ID=$ID[$i] db_ftype=$db_ftype[$i] db_path=$db_path[$i] ab_manager=$ab_manager[$i]\n";
140: # print "file_name=$file_name[$i] file_retpath=$file_retpath[$i] file_type=$file_type[$i]\n";
141:
142: $i++;
143: }
144:
145: # strip last (void) element returned by db
146: $#ID--;
147: # $#file_type--;
148: # $#file_name--;
149: # $#file_retpath--;
150:
151: $sth->finish();
152:
153: # print "wp : @tmp_ID\nepd : @ID\n";
154: # print "wp : @tmp_file_name\nepd : @file_name\n";
155:
156: # concatenate arrays
157: push(@ID, @tmp_ID);
158: push(@file_type, @tmp_file_type);
159: push(@file_name, @tmp_file_name);
160: push(@file_retpath, @tmp_file_retpath);
161:
162: # and reverse it to get wp files first
163: @ID = reverse(@ID);
164: @file_type = reverse(@file_type);
165: @file_name = reverse(@file_name);
166: @file_retpath = reverse(@file_retpath);
167:
168: # print "@ID\n";
169:
170: return(\@ID, \@file_type, \@file_name, \@file_retpath);
171: }
172:
173:
174: sub unwant($$)
175: {
176: my $type = shift;
177: my $IDfield = shift;
178: my $table;
179:
180: # select apropriate table
181: $table = ($type eq "wpz" || $type eq "wpp" ? $WP_TABLE : $EPD_TABLE);
182:
183: $sth = $dbh->prepare ("UPDATE $table SET ab_want=0 WHERE ID=$IDfield");
184: $sth->execute() or die "dbops::unwant() unable to update table! error $DBI::errstr";
185:
186: print "[dbops::unwant] marked ID=$IDfield in table $table as done\n";
187:
188: $sth->finish();
189: }
190:
191:
192: return(1);
193:
CVSweb