#!/perl/bin/perl -w
use strict;
use DBI qw(:sql_types);

my ($callcnt, $callsql, $detcnt, $detsql, $infile, $mins, $oraconn );
my (@infields);
my (%callers);

# Get the file name from the command line
$infile = shift(@ARGV);

# Check the existence of the file
if ( -e $infile ) {
	$infile = "<" . $infile;  	 	 	  		   #add input file character to filename
	open INFILE, $infile
	or die "Cannot open input file $infile. $!";
	} else {
		die "File $infile does not exist!";
	}

# Open the database connection to Oracle 8
$oraconn = DBI->connect( 'dbi:Oracle:test.world','ca','llac', {RaiseError => 1, AutoCommit => 0})
|| die "Database connection not made: $DBI::errstr";
 
#Delete old records from callers and call_det tables
$callsql = $oraconn->prepare( q{ DELETE FROM  callers} );
$callsql->execute;
$detsql = $oraconn->prepare( q{ DELETE FROM call_det} );
$detsql->execute;
$oraconn->commit;

#Prepare the SQL insert statements for both tables
$callsql = $oraconn->prepare( q{ INSERT INTO callers VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)} );
$detsql = $oraconn->prepare( q{	INSERT INTO call_det VALUES ( ?, TO_DATE(?,'MM/DD/YY'), ?, ?, ?)} );

# Process the input file
$callcnt = 0;          # Initialize caller count
$detcnt = 0;           # Initialize call detail count   
while ( <INFILE> ) {

	# Look for line in file with the "Current User" string in it
	if ( /^\|Ext\s+(\d+)\s+Current User:\s+(.*)Dept.*/ ) {

	
	    # Initialize hash to zeros
		$callers{"ttl_ocalls"} = 0;
		$callers{"ttl_day_ocalls"} = 0;
		$callers{"ttl_nite_ocalls"} = 0;       
		$callers{"ttl_omins"} = 0;
		$callers{"ttl_day_omins"} = 0;
		$callers{"ttl_nite_omins"} = 0;       
		$callers{"ttl_icalls"} = 0;
		$callers{"ttl_day_icalls"} = 0;
		$callers{"ttl_nite_icalls"} = 0;       
		$callers{"ttl_imins"} = 0;
		$callers{"ttl_day_imins"} = 0;
		$callers{"ttl_nite_imins"} = 0;
	
	    # Get initial data
		$callers{"extension"} = $1;		    #store extension number into hash
		$callers{"name"} = $2;              #store phone users name into hash 
        $callers{"name"} =~ s/\s{2,}//g;    #Remove extra spaces
		
		# Check to see if the Name is empty, if the extension is a trunk line (ext 3xxx) then
		# discard all data.  Otherwise, telephone data base field was blank in error.  Put an
		# "Unknown" in the name and proceed.
		if ( $callers{"name"} eq "" ) {     
		   if ( substr( $callers{"extension"}, 0, 1) ne "4" ) {
		      next;
		   } else {
		      $callers{"name"} = 'Unknown';
		   }
		}
		
		# Continue looping thru file - now the program is between the beginning
		# of an extension's data and the end of the extension's data.  
		# NOTE: This next 'while' will cause the program to read the next line of the input file. 

		while ( <INFILE> ) {   
			# When this if statement is true, we're at the end of an extension's
			# data, so exit the loop
			if ( /^\|Ext\s+.*\s+Totals.*Calls:/ ) {  
				last;
			}

			# If this statement is true, then a good phone call record has been
			# found - the regular expression is looking for "|mm/dd/yy"
			if ( /^\|\d{2}\/\d{2}\/\d{2}/ ) {
				@infields = split /\|/, $_;  	 		    #split the string into a list of fields
				$mins = &calc_mins( $infields[3] );         #convert the call duration to minutes

				$detsql->bind_param( 1, $callers{"extension"}, SQL_VARCHAR );
				$detsql->bind_param( 2, $infields[1], SQL_VARCHAR );
				$detsql->bind_param( 3, $infields[2], SQL_VARCHAR );
				$detsql->bind_param( 4, $mins, SQL_INTEGER );
				$detsql->bind_param( 5, $infields[6], SQL_VARCHAR );
					
				$detsql->execute;
					 
				#Increment $detcnt and commit if 25 records have been inserted
				$oraconn->commit if ((++$detcnt % 25) == 0);			
					 
				# Check to see if incoming call or not
				if ($infields[6] =~ /^INCOMING/) {
					$callers{"ttl_icalls"} += 1;            #increment for each call  
					$callers{"ttl_imins"} += $mins;         #add minutes to total

					# Check to see if it is a daytime call
					if ($infields[2] ge "07:00" && $infields[1] le "17:00") {
						$callers{"ttl_day_icalls"} += 1;     #increment for each call 
						$callers{"ttl_day_imins"} += $mins;  #add minutes to total

					}
					else {
						$callers{"ttl_nite_icalls"} += 1;    #increment for each call 
						$callers{"ttl_nite_imins"} += $mins; #add minutes to total
					}
				# Outgoing calls
				}
				else {
					$callers{"ttl_ocalls"} += 1;             #increment for each call 
					$callers{"ttl_omins"} += $mins;          #add minutes to total

					# Check to see if it is a daytime call
					if ($infields[2] ge "07:00" && $infields[1] le "17:00") {
						$callers{"ttl_day_ocalls"} += 1;     #increment for each call 
						$callers{"ttl_day_omins"} += $mins;  #add minutes to total
					}
					else {
						$callers{"ttl_nite_ocalls"} += 1;    #increment for each call 
						$callers{"ttl_nite_omins"} += $mins; #add minutes to total
					}		    
				}
			}
		}
		#Program executes here after each extension's data is read
		#Need to insert the summary record for extension just read
		$callsql->bind_param( 1, $callers{"extension"}, SQL_VARCHAR );       #store extension
		$callsql->bind_param( 2, $callers{"name"}, SQL_VARCHAR );            #store user name
		$callsql->bind_param( 3, $callers{"ttl_ocalls"}, SQL_INTEGER );      #total outgoing call count
		$callsql->bind_param( 4, $callers{"ttl_day_ocalls"}, SQL_INTEGER );  #ttl daytime outgoing call count
		$callsql->bind_param( 5, $callers{"ttl_nite_ocalls"}, SQL_INTEGER ); #ttl nitetime outgoing call count       
		$callsql->bind_param( 6, $callers{"ttl_omins"}, SQL_INTEGER );       #total outgoing call minutes
		$callsql->bind_param( 7, $callers{"ttl_day_omins"}, SQL_INTEGER );   #ttl daytime outgoing call minutes
		$callsql->bind_param( 8, $callers{"ttl_nite_omins"}, SQL_INTEGER );  #ttl nitetime outgoing call minutes       
		$callsql->bind_param( 9, $callers{"ttl_icalls"}, SQL_INTEGER );      #total outgoing call count
		$callsql->bind_param( 10, $callers{"ttl_day_icalls"}, SQL_INTEGER ); #ttl daytime outgoing call count
		$callsql->bind_param( 11, $callers{"ttl_nite_icalls"}, SQL_INTEGER );#ttl nitetime outgoing call count       
		$callsql->bind_param( 12, $callers{"ttl_imins"}, SQL_INTEGER );      #total outgoing call minutes
		$callsql->bind_param( 13, $callers{"ttl_day_imins"}, SQL_INTEGER );  #ttl daytime outgoing call minutes
		$callsql->bind_param( 14, $callers{"ttl_nite_imins"}, SQL_INTEGER ); #ttl nitetime outgoing call minutes

		$callsql->execute;			     

 		#Increment $callcnt and commit if 25 records have been inserted
		print "count=$callcnt in-process commit\n";
		$oraconn->commit if ((++$callcnt % 25) == 0);		    
	}
}
print "commit\n";
$oraconn->commit;
#print "disconnect\n";
#$oraconn->disconnect;
#print "end\n";
#exit;

#-----------------------------------------------------------------------
# calc_mins - This sub will take an elpased time format of hh:mm:ss and
# convert it to minutes.  The value returned is minutes.
#-----------------------------------------------------------------------
sub calc_mins {
	my @intime;
	@intime = split /:/,$_[0];
	return (($intime[0] * 60) + $intime[1]); #In the data, seconds is always 00   
}
#--end of calc_mins