#!/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);