File: [local] / wpscripts / ezreturn / dbops.pm (download)
Revision 1.2, Tue Jun 14 11:31:10 2011 UTC (13 years, 3 months ago) by yason
Branch: MAIN
Changes since 1.1: +3 -3 lines
remove unneeded script
|
#!/usr/bin/perl
use DBI;
use strict;
#use conv; # to_local()
package dbops;
our $EPD_ZAYAVKA_T = 0;
our $EPD_PROBA_T = 1;
our $WP_ZAYAVKA_T = 0;
our $WP_PROBA_T = 1;
our $EPD_TABLE = 'T$_EPD_File';
our $WP_TABLE = 'File_zayavki';
our $server = "pdc";
our $user = "sa";
our $pass = "aekghbynflvby";
our $dsn = "DBI:Sybase:server=$server";
our $dbh;
our $sth;
sub connect()
{
$dbh = DBI->connect($dsn, $user, $pass) or die "dbops::connect() unable to connect to server $DBI::errstr";
print "[dbops::connect] established database connection\n";
}
sub select()
{
$dbh->do("use Manager");
}
sub disconnect()
{
$dbh->disconnect();
print "[dbops::disconnect] closing database connection\n";
}
sub getall()
{
# files arrays
my @ID;
my @file_type;
my @file_name;
my @file_retpath;
# for temp copies
my @tmp_ID;
my @tmp_file_type;
my @tmp_file_name;
my @tmp_file_retpath;
my @db_path;
my @ab_manager;
my @db_ftype; # file type in db
my $i;
# *****
# Work in upload_files table.
# select wpz and wpp files, if any
$sth = $dbh->prepare("SELECT ID,type_file,path,ab_manager FROM $WP_TABLE " .
"WHERE ab_want=1 AND type_file IN(0,2)")
or die("dbops::getall()");
$sth->execute() or die "dbops::getall() execute query; error $DBI::errstr";
$i = 0;
while( ($ID[$i], $db_ftype[$i], $db_path[$i], $ab_manager[$i]) = $sth->fetchrow_array() ) {
# truncate spaces in file name
while( index($db_path[$i], ' ', 0) != -1 ) {
chop($db_path[$i]);
}
# extract basename
$file_name[$i] = substr( $db_path[$i], rindex($db_path[$i], '\\') + 1);
# return path is based on ab_manager name (appended to /WIDEPRINT/Xxxxxxx)
$file_retpath[$i] = ($db_ftype[$i] == 0 ? "/WIDEPRINT/Заявки/$ab_manager[$i]" :
"/WIDEPRINT/Цветопробы/$ab_manager[$i]");
# hardcode file_type
$file_type[$i] = ($db_ftype[$i] == 0 ? "wpz" : "wpp");
# print "ID=$ID[$i] db_ftype=$db_ftype[$i] db_path=$db_path[$i] ab_manager=$ab_manager[$i]\n";
# print "file_name=$file_name[$i] file_retpath=$file_retpath[$i] file_type=$file_type[$i]\n";
$i++;
}
# strip last elements in each array
$#ID--;
$#db_ftype--;
$#db_path--;
$#ab_manager--;
# $#file_name--;
# $#file_retpath--;
# $#file_type--;
$sth->finish();
# preserve temporary copy
@tmp_ID = @ID;
@tmp_file_type = @file_type;
@tmp_file_name = @file_name;
@tmp_file_retpath = @file_retpath;
@ID = ( );
@file_type = ( );
@file_name = ( );
@file_retpath = ( );
# *****
# Work in T$_EPD_File table
$sth = $dbh->prepare("SELECT ID,file_type,win_path,ab_manager FROM $EPD_TABLE " .
"WHERE ab_want=1 AND file_type IN(0,2)")
or die("dbops::getall()");
$sth->execute() or die "dbops::getall() execute query; error $DBI::errstr";
$i = 0;
while( ($ID[$i], $db_ftype[$i], $db_path[$i], $ab_manager[$i]) = $sth->fetchrow_array() ) {
# truncate spaces in file name
while( index($db_path[$i], ' ', 0) != -1 ) {
chop($db_path[$i]);
}
# extract basename
$file_name[$i] = substr( $db_path[$i], rindex($db_path[$i], '\\') + 1);
# return path is based on ab_manager name (appended to /WIDEPRINT/EPD_Xxxxxxx)
$file_retpath[$i] = ($db_ftype[$i] == 0 ? "/WIDEPRINT/EPD_Заявки/$ab_manager[$i]" :
"/WIDEPRINT/EPD_Цветопробы/$ab_manager[$i]");
# hardcode file_type
$file_type[$i] = ($db_ftype[$i] == 0 ? "epdz" : "epdp");
# print "ID=$ID[$i] db_ftype=$db_ftype[$i] db_path=$db_path[$i] ab_manager=$ab_manager[$i]\n";
# print "file_name=$file_name[$i] file_retpath=$file_retpath[$i] file_type=$file_type[$i]\n";
$i++;
}
# strip last (void) element returned by db
$#ID--;
# $#file_type--;
# $#file_name--;
# $#file_retpath--;
$sth->finish();
# print "wp : @tmp_ID\nepd : @ID\n";
# print "wp : @tmp_file_name\nepd : @file_name\n";
# concatenate arrays
push(@ID, @tmp_ID);
push(@file_type, @tmp_file_type);
push(@file_name, @tmp_file_name);
push(@file_retpath, @tmp_file_retpath);
# and reverse it to get wp files first
@ID = reverse(@ID);
@file_type = reverse(@file_type);
@file_name = reverse(@file_name);
@file_retpath = reverse(@file_retpath);
# print "@ID\n";
return(\@ID, \@file_type, \@file_name, \@file_retpath);
}
sub unwant($$)
{
my $type = shift;
my $IDfield = shift;
my $table;
# select apropriate table
$table = ($type eq "wpz" || $type eq "wpp" ? $WP_TABLE : $EPD_TABLE);
$sth = $dbh->prepare ("UPDATE $table SET ab_want=0 WHERE ID=$IDfield");
$sth->execute() or die "dbops::unwant() unable to update table! error $DBI::errstr";
print "[dbops::unwant] marked ID=$IDfield in table $table as done\n";
$sth->finish();
}
return(1);