#!/usr/bin/perl
# $Id: zlist,v 1.10 2012/04/01 06:53:02 yason Exp $
use DBI;
use Term::ANSIColor qw(:constants);
use Time::HiRes qw(gettimeofday tv_interval);

$| = 1;

# database
our $server = "pdc";
our $user = "sa";
our $pass = "aekghbynflvby";
our $dsn = "DBI:Sybase:server=$server";
our $sth;

# response handlers 
our @numcard;
our @time;
our @pri;
our @path;
our @size;
our @tx_count;

my $i;	# counter
my $total = 0; # total files

###
my $time1 = [gettimeofday];

# connect to database server
db_connect();

# select database
db_select();

# fetch zayavki
db_fetch(0);

my $time2 = [gettimeofday];

print GREEN "DB response time: " . tv_interval($time1, $time2) . " seconds\n". RESET;

if( ! -e "/var/run/ztransfer.lock")
{
	print YELLOW "ztransfer is not running\n" . RESET;
}
else
{
	if( -e "/var/log/wpscripts/ztransfer/FILE" && -e "/var/log/ztransfer/ACTION")
	{
	    open(CURRENT, "/var/log/wpscripts/ztransfer/FILE");
	    my $file = <CURRENT>;
	    close(CURRENT);

	    open(CURRENT, "/var/log/wpscripts/ztransfer/ACTION");
	    my $action = <CURRENT>;
	    close(CURRENT);
	    
	    
	    print "DEBUG: $file $action\n";
	    
	    print YELLOW "ztransfer is running [ " . ($action eq 'rar' ? "compressing " : "copying ") . "$file ]\n" . RESET;
	}
	else
	{
	    print YELLOW "ztransfer is running\n" . RESET;
	}
}

# print out zayavki info
print BOLD WHITE ON_BLUE "Zayavki queue". RESET . " ($#numcard pcs.) :\n";
    $i = 0;
    while( ($numcard[$i]) )
    {
	print "$numcard[$i]\t[ executed $time[$i],  priority=$pri[$i],  file=$path[$i] (size=$size[$i]), transfer counter=$tx_count[$i] ]\n";
	$i++;
	$total++;
    }
print "\n";


# fetch probas
db_fetch(1);
	
#print "****** probas ($#numcard) ******\n";
print BOLD WHITE ON_BLUE "Probas queue". RESET . " ($#numcard pcs.) :\n";
	$i = 0;
	while( ($numcard[$i]) )
	{
	    print "$numcard[$i]\t[ executed $time[$i],  priority=$pri[$i],  file=$path[$i] (size=$size[$i]) ]\n";
	    $i++;
	    $total++;
	} 
print "\n";

# fetch TT
db_fetch(2);

print BOLD WHITE ON_BLUE "TT queue". RESET . " ($#numcard pcs.) :\n";
	$i = 0;
	while( ($numcard[$i]) )
	{
		print "$numcard[$i]\t[ executed $time[$i],  priority=$pri[$i],  file=$path[$i] (size=$size[$i]) ]\n";
		$i++;
		$total++;
	}
print "\n";

# fetch zayavki_jpg
db_fetch(3);

print BOLD WHITE ON_BLUE "zayavki_jpg queue". RESET . " ($#numcard pcs.) :\n";
	$i = 0;
	while( ($numcard[$i]) )
	{
		print "$numcard[$i]\t[ executed $time[$i],  priority=$pri[$i],  file=$path[$i] (size=$size[$i]) ]\n";
		$i++;
		$total++;
	}
print "\n";

# fetch proba_jpg
db_fetch(4);

print BOLD WHITE ON_BLUE "proba_jpg queue". RESET . " ($#numcard pcs.) :\n";
	$i = 0;
	while( ($numcard[$i]) )
	{
		print "$numcard[$i]\t[ executed $time[$i],  priority=$pri[$i],  file=$path[$i] (size=$size[$i]) ]\n";
		$i++;
		$total++;
	}
print "\n";


# print summary information
print BOLD WHITE . "Total: " . RESET . "$total files in queue.\n";

# close connection
db_disconnect();


### database functions

sub db_connect()
{
    $dbh = DBI->connect($dsn, $user, $pass) or die "unable to connect to server $DBI::errstr";
}


sub db_select()
{
    $dbh->do("use full_print");
}


sub db_fetch($)
{
    my $type_file = shift;
    my $i = 0;
    my $searchpath;

    $sth = $dbh->prepare ("SELECT numcard,path,time_file,file_priority,tx_count FROM upload_files WHERE status=0 AND type_file=$type_file") or die "SQL prepare failed\n";
    $sth->execute( ) or die "unable to execute query $query   error $DBI::errstr";

    # zero elements
    $#numcard = $#path = $#time = $#pri = $#tx_count = 0;
    
    while( ($numcard[$i], $path[$i], $time[$i], $pri[$i], $tx_count[$i]) = $sth->fetchrow_array())
    {
	# truncate spaces
	do
	{
	    chop($path[$i]);
	} 
	while( index($path[$i], ' ', 0) != -1 );
	
	# remove first 13 symbols in SMB path (\\fileserver\)
	$searchpath = substr($path[$i], 13);
	
	# hardcoded
	# remove first symbols to get only filename (i.e. \\fileserver\zayavki\ - 21 symbols)
	$path[$i] = substr($path[$i], 21) if ($type_file == 0); #zayavki
	$path[$i] = substr($path[$i], 19) if ($type_file == 1); #proba
	$path[$i] = substr($path[$i], 16) if ($type_file == 2); #TT
	$path[$i] = substr($path[$i], 25) if ($type_file == 3); #zayavki_jpg
	$path[$i] = substr($path[$i], 23) if ($type_file == 4); #proba_jpg

    	    # make local file path
	    $searchpath =~ tr/\\/\//; 
	    $searchpath = '/mnt/maket/' . $searchpath;


	    # finally, get file size
	    $size[$i] = -s $searchpath;
	    $size[$i] = ($size[$i] > 1048576 ? int($size[$i] / 1024 / 1024) . " MB" : int($size[$i] / 1024) . " KB");
	    
	    # this should work fine
	    $size[$i] = WHITE ON_RED . "File not found" . RESET if (! -e $searchpath);
    $i++;
    }
    
    $sth->finish();
    
}


sub db_disconnect()
{
    $dbh->disconnect();
}