[BACK]Return to dbops.pm CVS log [TXT][DIR] Up to [local] / wpscripts / ezreturn

Annotation of wpscripts/ezreturn/dbops.pm, Revision 1.1.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