#!/usr/bin/perl

BEGIN { use lib '/usr/home/cfg' ; require push_inc ; }
    
use CGI qw( :standard );
use CGI::Carp qw(fatalsToBrowser);
use LWP::Simple qw($ua get);
use Date::Calc qw(:all);
use MIME::Lite;
use Mail::Sendmail;
use Time::Piece ; 

require cfg ;

print header;   # CGI.pm method

#-------------------------------------------------------------------------------

my @ARGV                = split(/\&/, $ENV{'QUERY_STRING'});
my $type                = $ARGV[0] ;

our ($q)                = CGI -> new() ;
my $iaction             = $q -> param('iaction')  ;
my $iattachdoc          = $q -> param('iattachdoc')   ;

# our $debug = 1 ;

#------------------------------------------------------------------------------------------

&today ;

if ($iaction eq '') {
	
    &screen1 ;
	
} elsif ($iaction eq 'upload') {
	
	&db_open_ro ;
		our $db_ignore_open_close = 1 ; # do one open and one close instead of repeating it
			&read_import ;
			&read_db ;
			&process ;
			&screen3 ;
		$db_ignore_open_close = 0 ; # do one open and one close instead of repeating it
	&db_close_conn ;
	
} elsif ($iaction eq 'save') {
	 
	&common_min_load_params ;
	&db_open_upd ;
		our $db_ignore_open_close = 1 ; # do one open and one close instead of repeating it
			&read_read_import_lines ;
			&update ;
		$db_ignore_open_close = 0 ; # do one open and one close instead of repeating it
	&db_close_conn ;
	&screen2 ; 
}

exit;

#------------------------------------------------------------------------------------------

sub read_read_import_lines {
	
	our $max_unmatched_id = 0 ; 
	
	my $upload_filehandle = $q->upload('iattachdoc') ;
	
	if ($type eq 'csv') {
		
		&db_min_ro('event_production_time_unmatched','id,unmatched_line','','','') ; 
		
		foreach (keys %{$db{event_production_time_unmatched}}) {
			my $event_code = substr($db{event_production_time_unmatched}{$_}{unmatched_line},-8) ; 
			$unmatched_event_lines{$event_code} = $db{event_production_time_unmatched}{$_}{unmatched_line} ; 
			$max_unmatched_id = $_ if !$max_unmatched_id || ($max_unmatched_id && $max_unmatched_id < $_) ;
			$event_code_unmatched_id{$event_code} = $_ ; 
		}
		
		# # # $max_unmatched_id++ ;
		
		# while ( <$upload_filehandle> ) {
		# # foreach (@imported_file_data) {
			# chomp;
			
			# next if !$_ || ($_ !~ /\d+/ && $_ !~ /[a-zA-Z]/) ;
			
			# chop $_ ; 
 
			# my @linex = split(/\;/,$_) ;
			
			# my $event_code = substr($linex[-1],-8) ; 
			
			# $event_lines{$event_code} = $_ ; 
			
		# }
	}
	
} #------------------------------------------------------------------------------------------
 
sub update {
	 
	my %ii = () ; my %iii = %i ;
	
	&db_min_ro('event_production_time','quote_id,id,event_system_production_seconds,event_system_cloud_recording_seconds,matched_events',"") ; 
	
	my $max_id = 0 ; 
	
	foreach my $event_id (keys %{$db{event_production_time}}) {
		
		$max_id = $db{event_production_time}{$event_id}{id} if !$db{event_production_time}{$event_id}{id} || int($db{event_production_time}{$event_id}{id}) > int($max_id) ;
		
		if ($db{event_production_time}{$event_id}{event_system_production_seconds}) {
			foreach (sort split(/\|/,$db{event_production_time}{$event_id}{event_system_production_seconds})) {
				 my @abc = split(":",$_) ;  
				 $ii{$event_id}{$abc[0]}{event_system_production_seconds} += $abc[1] ;
			}				
		}
		
		if ($db{event_production_time}{$event_id}{event_system_cloud_recording_seconds}) {
			foreach (sort split(/\|/,$db{event_production_time}{$event_id}{event_system_cloud_recording_seconds})) {
				 my @abc = split(":",$_) ;  
				 $ii{$event_id}{$abc[0]}{event_system_cloud_recording_seconds} += $abc[1] ;
			}				
		}
		
		$ii{$event_id}{NA}{matched_events} = $db{event_production_time}{$event_id}{matched_events} ;
		
	}
	
	foreach (sort keys %i) {
		
		my @get_row = split("_",$_) ; 
		
		my $row_nr = $get_row[-1] ;	
		
		my $system_field = "system_$row_nr" ;
		
		my $event_code_field = "event_code_$row_nr" ;
		
		if ($_ !~ /event_code_/ && $_ =~ /event_/ && (!$i{$_} || !$i{$system_field}) && !$unmatched_event_lines{$i{$i{$event_code_field}}}) {
			
			$max_unmatched_id++ ;
			$i{$i{$event_code_field}} =~ s/\'//g ; 
			
			my %iiii = %i ;
			
			%i = () ; 
			
			$i{id} = $max_unmatched_id ;
			$i{unmatched_line} = $iiii{$iiii{$event_code_field}} ;
			
			&db_min_insert('event_production_time_unmatched') ; 
			
			# print "\n INSERT INTO event_production_time_unmatched (id,unmatched_line) VALUES ($max_unmatched_id,'$i{unmatched_line}') ; " ;
			
			%i = %iiii ;
			
		} elsif ($_ !~ /event_code_/ && $_ =~ /event_/ && $i{$_} && $i{$system_field} && $unmatched_event_lines{$i{$event_code_field}}) {
			
			# print "\n DELETE FROM event_production_time_unmatched WHERE id = '$event_code_unmatched_id{$i{$event_code_field}}' " ;
			
			&db_min_delete('event_production_time_unmatched',"`id`='$event_code_unmatched_id{$i{$event_code_field}}'") ; 
			
		}
		
		next if $_ =~ /event_code_/ || $_ !~ /event_/ || ($_ =~ /event_/ && $_ !~ /event_code_/ && !$i{$_}) ;
		
		my $system_field = "system_$row_nr" ; 
		 
		my $event_code_field = "event_code_$row_nr" ; 

		next unless $i{$system_field} ;
		
		my $production_seconds = $i{"time_$row_nr"} ;
		
		my $cloud_recording_seconds = $i{"cloud_time_$row_nr"} ;

		next if $ii{$i{$_}}{NA}{matched_events} =~ /$i{$event_code_field}/ ; 

		$ii{$i{$_}}{$i{$system_field}}{event_system_production_seconds} += $production_seconds ; 
		$ii{$i{$_}}{$i{$system_field}}{event_system_cloud_recording_seconds} += $cloud_recording_seconds ; 
		$ii{$i{$_}}{NA}{matched_events} .= ($ii{$i{$_}}{NA}{matched_events}) ? qq~|$i{$event_code_field}~ : qq~$i{$event_code_field}~ ;
		
	}
	
	foreach my $event_id (sort keys %ii) {
		
		%i = () ; 
		
		foreach my $system_id (sort keys %{$ii{$event_id}}) {
			next if $system_id eq 'NA' ;
			$i{event_system_production_seconds}			.= qq~$system_id:$ii{$event_id}{$system_id}{event_system_production_seconds}|~ ;
			$i{event_system_cloud_recording_seconds}	.= qq~$system_id:$ii{$event_id}{$system_id}{event_system_cloud_recording_seconds}|~ ;
		}
		
		chop $i{event_system_production_seconds} if $i{event_system_production_seconds} ;
		chop $i{event_system_cloud_recording_seconds} if $i{event_system_cloud_recording_seconds} ;
		
		$i{matched_events} 							= $ii{$event_id}{NA}{matched_events} ;
		
		next if !$i{event_system_production_seconds} && !$i{event_system_cloud_recording_seconds} && !$i{matched_events} ;

		$ignore{event_system_production_seconds} = ($i{event_system_production_seconds} && $i{event_system_production_seconds} eq $db{event_production_time}{$event_id}{event_system_production_seconds}) ? 1 : 0 ;
		$ignore{event_system_cloud_recording_seconds} = ($i{event_system_cloud_recording_seconds} && $i{event_system_cloud_recording_seconds} eq $db{event_production_time}{$event_id}{event_system_cloud_recording_seconds}) ? 1 : 0 ;  ;
		$ignore{matched_events} = ($i{matched_events} && $i{matched_events} eq $db{event_production_time}{$event_id}{matched_events}) ? 1 : 0 ;
		
		next if $ignore{event_system_production_seconds} && $ignore{event_system_cloud_recording_seconds} && $ignore{matched_events}  ;
		
		next unless $i{matched_events} ;
		
		if ($db{event_production_time}{$event_id}{quote_id}) {
			# print "\n UPDATE event_production_time SET event_system_production_seconds='$i{event_system_production_seconds}',event_system_cloud_recording_seconds='$i{event_system_cloud_recording_seconds}',matched_events='$i{matched_events}'" ;
			&db_min_upd('event_production_time',"quote_id='$event_id'")  ; 
		} else { 
			$i{quote_id} = $event_id ;	

			$max_id ++ ;
			$i{id} = $max_id ;	
			
			# print "\n INSERT INTO event_production_time (id,event_system_production_seconds,event_system_cloud_recording_seconds,matched_events,quote_id) VALUES ($i{id},$i{event_system_production_seconds},$i{event_system_cloud_recording_seconds},$i{matched_events},$i{quote_id})" ;
			
			&db_min_insert('event_production_time') ; 
			
		}
		
	}
	
	%i = %iii ; 
	
} #------------------------------------------------------------------------------------------

sub read_import {
 
	my $upload_filehandle = $q->upload('iattachdoc') ;
		
	unless ($upload_filehandle) {
		&screen1 ;
	}
 
	&db_min_ro('event_production_time_unmatched','id,unmatched_line','','','') ; 
		
	foreach (keys %{$db{event_production_time_unmatched}}) {
		my $event_code = substr($db{event_production_time_unmatched}{$_}{unmatched_line},-8) ; 
		$event_code_unmatched_id{$event_code} = $_ ; 
	}

	&db_min_ro('event_production_time','id,matched_events',"","","") ; 
	
	our %matched_event = () ;
	
	foreach my $quote_id (keys %{$db{event_production_time}}) {
		foreach my $event_code (split(/\|/,$db{event_production_time}{$quote_id}{matched_events})) {
			$matched_event{$event_code} = 1 ;
		}
	}
	
	# our $start_date = ($upload_filehandle =~ /August/) ? "$now_year-08-01" : ($upload_filehandle =~ /September/) ? "$now_year-09-01" : "" ;
	# our $end_date = ($upload_filehandle =~ /August/) ? "$now_year-08-31" : ($upload_filehandle =~ /September/) ? "$now_year-09-30" : "" ;

	# &db_open_upd ;
		# our $db_ignore_open_close = 1 ; # do one open and one close instead of repeating it
	# $print_tbody .= qq~<tr id="$id">~ ;	
	
	our %csv_line = () ; my $line_cnt = 0 ; 
	
	if ($type eq 'csv') {

		while ( <$upload_filehandle> ) {
		# foreach (@imported_file_data) {
			chomp;
			
			next if !$_ || ($_ !~ /\d+/ && $_ !~ /[a-zA-Z]/) ; 
			
			 # ClubName;StartDate;EndDate;Production Hours;Production Seconds;CloudRecording HD Duration(Sec);Event Name;Event Type;event_code
			$_ =~ s/"(\d+),(\d+)"/$1$2/g;
			# $_=~ s/\,/\;/g ; 

			$_ =~ s/\R\z// ; # Removes any line-ending character (\n, \r\n, or \r)
			
			my @linex = split(/\;/,$_) ;

			next if $linex[0] eq 'ClubName' ;

			$ProductionHours    = $linex[3] ;
			$ProductionSeconds  = $linex[-5] ;
			$CloudRecordingDur  = $linex[-4] ;
			$EventName          = $linex[-3] ;
			$EventType          = $linex[-2] ;
			$event_code			= $linex[-1] ;
			
			# $ProductionHours    = $linex[3] ;
			# $ProductionSeconds  = $linex[4] ;
			# $CloudRecordingDur  = $linex[5] ;
			# $EventName          = $linex[6] ;
			# $EventType          = $linex[7] ;
			# $EventID            = $linex[8] ;
			
			# $event_code =~ s/\R\z// ; # Removes any line-ending character (\n, \r\n, or \r)
			
			$event_code = substr($event_code,-8) ;
				
			$event_code_to_line{$event_code} = $_ ; 	
				
			next if $matched_event{$event_code} || $event_code_unmatched_id{$event_code} ; 
			
			$ClubName           = $linex[0] ;
			$StartDate          = $linex[1] ;
			
			next unless $StartDate ;
			
			my ($event,$system) = split("-",$ClubName) ; # Gymnastics Competition 148 - AISA_SOLO_10
			$event  =~ s/ $//g; $system =~ s/^ //g; # remove whitespace
			
			my @eventdetails = split(/ /,$event) ; # Gymnastics Competition 148
			
			$system = 'none' unless $system ;
			
			my ($sdate,$stime,$ampm) = split(/ /,$StartDate) ;
			my @sdateparts      = split(/\//,$sdate) ;
			my @stimeparts      = split(/\:/,$stime) ; $stimeparts[0] += 12 if $ampm eq 'PM' && $stimeparts[0] < 12 ;

			# $stimeparts[0] -= 12 if $pmam eq 'PM' && $stimeparts[0] > 11 ;	
			
			my $shr             = sprintf("%02s", $stimeparts[0]) ;
			my $smn             = sprintf("%02s", $stimeparts[1]) ;
			my $ss            	= sprintf("%02s", $stimeparts[2]) ;
			my $smm             = sprintf("%02s", $sdateparts[0]) ;
			my $sdd             = sprintf("%02s", $sdateparts[1]) ;
			my $sccyy           = $sdateparts[2] ;
			
			my $startccyymmdd   = $sccyy . $smm . $sdd ;
			
			$EndDate            = $linex[2] ;
	
			next unless $EndDate ;
			
			my ($edate,$etime,$pmam) = split(/ /,$EndDate) ;
			my @edateparts      = split(/\//,$edate) ;
			my @etimeparts      = split(/\:/,$etime) ; $etimeparts[0] += 12 if $pmam eq 'PM' ;
				
			$etimeparts[0] -= 12 if $pmam eq 'PM' && $etimeparts[0] > 11 ;	
				
			my $ehr             = sprintf("%02s", $etimeparts[0]) ;
			my $emn             = sprintf("%02s", $etimeparts[1]) ;
			my $es             	= sprintf("%02s", $etimeparts[2]) ;
			my $emm             = sprintf("%02s", $edateparts[0]) ;
			my $edd             = sprintf("%02s", $edateparts[1]) ;
			my $eccyy           = $edateparts[2] ;
			
			my $endccyymmdd     = $eccyy . $emm . $edd ;
			
			# &common_debug("$StartDate->$EndDate [$startccyymmdd,$endccyymmdd]") ;
			
			if (!$last_start_ccyymmdd || $last_start_ccyymmdd > $startccyymmdd) { 
				$last_start_ccyymmdd = $startccyymmdd ;
				$start_sql           = "$sccyy-$smm-$sdd 00:00:00" ;
			}
						  
			if (!$last_end_ccyymmdd || $last_end_ccyymmdd < $endccyymmdd) { 
				$last_end_ccyymmdd = $endccyymmdd ;
				$end_sql           = "$eccyy-$emm-$edd 23:59:59" ;
			}
				
			$csv_line{$event_code}{club_name} = $ClubName ;
			$csv_line{$event_code}{event_name} = $EventName ;
			$csv_line{$event_code}{production_hours} = $ProductionHours  ;
			$csv_line{$event_code}{production_seconds} = $ProductionSeconds  ;
			$csv_line{$event_code}{cloud_seconds} = $CloudRecordingDur ; 
			$csv_line{$event_code}{start_time} = "$sccyy-$smm-$sdd $shr:$smn:$ss" ;
			$csv_line{$event_code}{end_time} = "$eccyy-$emm-$edd $ehr:$emn:$es" ;
			
			$csv_line{$event_code}{production_hours} 	=~ s/ //g ;
			$csv_line{$event_code}{production_seconds} 	=~ s/ //g ;
			$csv_line{$event_code}{cloud_seconds} 		=~ s/ //g ;
			
			$line_cnt++ ;
			
			$csv_line{$event_code}{line_cnt} = $line_cnt ; 
			
			# $description  =~ s/^\s+|\s+$//g ; # remove whitespace at beginning and end

			# &common_date_array($startccyymmdd,$endccyymmdd);
			
			# foreach (@common_date_array) {
				# $pixellot_system{$_}{"$shr:$smn-$ehr:$emn"}{$system} = $EventName ;

				# $times_in_csv{$_}{"$shr:$smn-$ehr:$emn"}{$EventName} += 1 ;
				
				# $pixellot_system{$_}{"$shr:$smn-$ehr:$emn"}{$EventName} = "$system|$eventdetails[-1]|$ProductionHours|$ProductionSeconds|$CloudRecordingDur|$ClubName|$EventName|$event_code|" ;
				
				# &common_debug("pixellot_system : {$_}{$shr:$smn-$ehr:$emn} [$system|$ProductionHours|$ProductionSeconds|$CloudRecordingDur|$EventName|]") ;
		
			# }
			
			$x++;
			
			# $i{category_id} = 37 if $amount < 0 && ($description   =~ /WOOLWORTHS/iog || $description =~ /CHECKERS/iog || ($description =~ /KwikSpar/iog && $description =~ /food/iog)) && $account_nr != 62547665945 ; # Office Supplies
			# last if $x > 10;
		}
	}
	
		# $db_ignore_open_close = 0 ; # do one open and one close instead of repeating it
	# &db_close_conn ;
		
} #------------------------------------------------------------------------------------------

sub read_db {
	
	if (!$last_start_ccyymmdd && !$last_end_ccyymmdd) {
		print "All The Data In This File Is Already Matched Or Saved As Unmatched!!!" ;
		exit ; 
	}
	
	&production_time_read_db_to_match_events_and_systems ;
    
} #------------------------------------------------------------------------------------------

sub process {
	
	&production_time_process_matched_events_and_systems ;
 
} #------------------------------------------------------------------------------------------

sub screen1 {

	&common_min_dialog ;

	$print_box_content_rows .= qq~<div class="col-md-6"><div class="form-group"></div></div>~;
	  
	# my $date_inputs = &common_min_form_datepicker() ; 
	  
	print <<ENDOFTEXT;
	$dialog{'common'}{'head'}
		
	<div id="content" class="col-lg-12 col-sm-12">
		<!-- content starts --> 
		<div class="row">
			<div class="box col-md-12">
				<form role="form" name="form_import" id="import-form" action="?$type" method="post" enctype="multipart/form-data">
				<div class='row'>
					<div class='col-md-9'>
						<div class='control-group'>
							<div class='controls'> 
								<label for="imageInputFile">Import CSV</label>
								<input type="file" id="imageInputFile" name='iattachdoc' tabindex='1'>
								<p class="help-block">Upload file from PC.</p>
							</div>
						</div>
					</div>
				</div>
				<input type="hidden" name="iaction" id="action" value="upload">
				<div class="row"><div class="col-md-12">&nbsp;</div></div>
				<div class="row">
					<div class="col-md-12">	
						<button type="button" class="btn btn-primary" id="importbutt">Upload</button>&nbsp;
						<button type="button" class="btn btn-default" onclick="parent.BootstrapDialog.closeAll();">Close</button>
					</div>
				</div>
				<div class="row"><div class="col-md-12">&nbsp;</div></div>
				</form>
			</div>
		</div>
	</div> 
	</div><!--/fluid-row-->   
	</div><!--/.fluid-container-->

	<!-- external javascript -->

	<style>body { font-size: 14px; }.control-label { font-size: 14px; }</style>
	<script src="$useropts{'js'}/jquery.form-validator.min.js"></script>

	$dialog{'common'}{'js'}

	<script>

	\$.validate();

	\$("#importbutt").click(function() {    
		\$('#action').val('upload');
		\$('#import-form').submit();
	});

	</script>
	</body>
	</html>
ENDOFTEXT
#         

exit ;

} #------------------------------------------------------------------------------------------

sub screen2 {

	# <div class="alert alert-success" id="alertmsg" role="alert"></div>
	
	my $jquery = qq~parent.\$("#alertbar").html("<div class='alert alert-success' id='alertmsg' role='alert'><i class='glyphicon glyphicon-ok'></i> CSV SUCCESSFULLY IMPORTED</div>");
		parent.location.reload();
		parent.BootstrapDialog.closeAll() ;~ unless $debug ;
		 
	print <<ENDOFTEXT;
	<!DOCTYPE html>
	<html lang="en">
	<head>    
		<meta charset="utf-8">
		<title>$useropts{title}</title>
		<script src="$useropts{'bower_components'}/jquery/jquery.min.js"></script>   
	</head>
	<body>
	<script>

	\$(function () {
		$jquery
	});

	</script>
	</body>
	</html>
ENDOFTEXT
#

} #------------------------------------------------------------------------------------------

sub screen3 {

&thead;

if ($isaved) { $alert = &common_min_alert('success',"$ucpage $isaved!",'ok') ; } else { &common_min_alert_type ; }

# my $page = "$lcpage\s" ;

my $page = "event-quotes" ;

if ($s{no}) { $page = $lcpage ; }

&common_min_thead ; 

# &common_min_table('id',$page,'list') ;

$print_box_content_rows .= qq~
	<table id="itv-table" class="table table-striped table-bordered bootstrap-datatable datatable responsive">    
		$print_thead
		<tbody>
			$print_tbody
		</tbody>
	</table>
~ ;

&common_min_table_select_jquery("#itv-table td:nth-last-child(2),#itv-table td:nth-last-child(10)") ;

# our $savjqy = 1 ;

# &common_min_extra_crumb("manage-$lcpage\s","Manage $ucfirstpage\s") ;

# $extra_form_fields .= qq~<input type="file" id="imageInputFile" name='iattachdoc' tabindex='1'>~ ;

	# <input type="hidden" name="client_id"        value="$i{client_id}">
	# <input type="hidden" name="date_from"        value="$i{date_from}">
	# <input type="hidden" name="date_to"          value="$i{date_to}">
	# <input type="hidden" name="event_costing_id" value="$i{event_costing_id}">
	# <input type="hidden" name="event_quote_id"	 value="$i{event_quote_id}">
	# <input type="hidden" name="paid_status"      value="$i{paid_status}">
# ~ ;

$print_box_content_rows .= &common_min_forms_end('','','save') ;

require _blank_4 ;

exit ;

} #------------------------------------------------------------------------------------------

use today ;
use common ; 
use production_time ;