Annotation of wpscripts/ezreturn/dbops.pm, Revision 1.1
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:
! 15: our $server = "MSSQL";
! 16: our $user = "it_men";
! 17: our $pass = "itmen712";
! 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: {
! 31: $dbh->do("use Manager");
! 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