#!/usr/lib/perl -w -I /bin -I /rt/local/lib -I /rt/lib
use lib("/rt/local/lib", "/rt/lib", "/bin");

###################################################
#
#  Dustin York 
#  University of Washington->UW Tech->SSG 2008
#
#
###################################################


############## Helper Functions #####################################

# Checking User input:
sub usage {
print <<"USAGE";
  Usage: $0  

USAGE

#print <<"USAGE";
#  Usage: $0   
#
#USAGE
exit;
}

##################### The Work ##############################

# If the script does not recieve the correct number of vars. 
if( @ARGV != 2 )
{
        usage();
}

my $attachStart = $ARGV[0];
my $attachFinish = $ARGV[1];
my $attach2Move = $ARGV[2];
my $start = time();

# Open Error Log File.
open(ERRORLOGFILE, ">>$0.error"); 

# open log file
open(FULLLOGFILE, ">>$0.log"); 


# The limits wouldn't make sense if this check passed.
if( $attachStart > $attachFinish )
{
	print "Attachment Start is greater than Attachment Finish!";
	exit; 
}

#$attach2Move = 50 unless( $attach2Move ); 

use DBI qw(:sql_types);

my $dbh = DBI->connect('DBI:Pg:dbname=rtdb;host=rtdb;port=1234', 'fake_user')
          or die 'Couldn\'t connect to databse: ' . DBI->errstr;

my $attachmentFilesQuery = < 0    
AND id > ? AND id < ? 
AND separatedfile = 0 
ORDER BY id 
QUERY

my $sth = $dbh->prepare( $attachmentFilesQuery ) or die "Couldn't prepare Attachments statement: " . $dbh->errstr;


$sth->bind_param( 1, $attachStart - 1,  { TYPE => SQL_INTEGER } );
$sth->bind_param( 2, $attachFinish + 1, { TYPE => SQL_INTEGER } );
#$sth->bind_param( 3, $attach2Move,  { TYPE => SQL_INTEGER } );

unless( $sth->execute( ) )
{
	print '[error] Fetching attachments to separate failed:' . $DBI::errstr;
}

my $totalAttachmentsFound = $sth->rows;
if( $totalAttachmentsFound > 0 ) 
{
	print 'About to separate ' . $totalAttachmentsFound . ' files.'. "\n";
	print "Do you want to continue? (Y/N) \n";
	$continue = ;
	unless( $continue =~ /^y.*/i  ) 
	{
		exit;
	}

} else {
	print 'There are no attachments to separate from #' .$attachStart.' to #'. $attachFinish . "\n";
	exit;
}

my $currentAttachmentId = 0;

my $separateFileQuery = <prepare( $separateFileQuery ) or die "Couldn't prepare Separated File statement: " . $dbh->errstr;

my $updateFileQuery = <prepare( $updateFileQuery ) or die "Couldn't prepare Update Attachment statement: " . $dbh->errstr;

my $ticket4AttachQuery = <prepare( $ticket4AttachQuery ) or die "Couldn't prepare ticket 4 Attachment statement: " . $dbh->errstr;

my $processedAttachments = 0;

while( @data = $sth->fetchrow_array() ) {

	print ' Separating Attachment - id = '.$data[0].' - separatedFile = '.$data[12];
	$ticket4AttachSth->bind_param( 1, $data[1], { TYPE => SQL_INTEGER } );
	if( $ticket4AttachSth->execute() ) 
	{
		my @transaction = $ticket4AttachSth->fetchrow_array();
		print ' - ticketid = '.$transaction[0];
	} else {
		logError("Fetching ticket # for attachment failed: $DBI::errstr");	
	}
	 
	$currentAttachmentId = $data[0];

	$separateSth->bind_param( 1, $data[0], { TYPE => SQL_INTEGER } );
	$separateSth->bind_param( 2, $data[8], { TYPE => SQL_VARCHAR } );
	
	if( $separateSth->execute( ) ) 
	{
		print ' - Attachment #' . $data[0] . " Separated";
		$updateSth->bind_param( 1, $data[0], { TYPE => SQL_INTEGER } );
		if( $updateSth->execute( ) )
		{
			print " and Updated.\n";	
		} else {
			logError( "Attachment could not be updated: $DBI::errstr" ); 
		}
	
		$processedAttachments++;
	} else {
		logError( "Attachment #".$data[0]." was NOT separated! Not updating attachment: $DBI::errstr" );
	}
	# wait a second to slow the script down.
	sleep 1;
}
# Finishing all the statements
$sth->finish();
$ticket4AttachSth->finish();
$ticket4AttachSth->finish();
$separateSth->finish();
$updateSth->finish();


sub logError
{
	my $error = shift;
	print "\n [error] - " . $error . "\n";
	print ERRORLOGFILE " [error] ".$dt->ymd.' '.$dt->hms .' - '.$error."\n";	
}

print ' Last Attachment Id = ' . $currentAttachmentId . "\n";

my $finish = time();

my $finishStatement = "$processedAttachments of $totalAttachmentsFound during a search between attachments $attachStart and $attachFinish succesfully in " . ($finish - $start) . " seconds.\n";
print $finishStatement;
print FULLLOGFILE $finishStatement;

$dbh->disconnect;

close ERRORLOGFILE;
close FULLLOGFILE;