532 lines
18 KiB
Perl
532 lines
18 KiB
Perl
#!/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] ;
|
|
my $format = $ARGV[1] ;
|
|
|
|
our ($q) = CGI -> new() ;
|
|
my $iaction = $q -> param('iaction') ;
|
|
my $iattachdoc = $q -> param('iattachdoc') ;
|
|
|
|
# our $debug = 1 ;
|
|
|
|
#------------------------------------------------------------------------------------------
|
|
|
|
&today ;
|
|
|
|
if ($iaction eq '') {
|
|
|
|
&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 ;
|
|
|
|
# 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 $_ ;
|
|
|
|
# print "\n line : $_" ;
|
|
|
|
# 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,event_system_production_seconds,event_system_cloud_recording_seconds,matched_events',"") ;
|
|
|
|
foreach my $event_id (keys %{$db{event_production_time}}) {
|
|
|
|
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{$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}) {
|
|
&db_min_upd('event_production_time',"quote_id='$event_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}'" ;
|
|
} else {
|
|
$i{quote_id} = $event_id ;
|
|
# print "\n INSERT INTO event_production_time (event_system_production_seconds,event_system_cloud_recording_seconds,matched_events,quote_id) VALUES ($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 ;
|
|
|
|
exit ;
|
|
|
|
} #------------------------------------------------------------------------------------------
|
|
|
|
sub read_import {
|
|
|
|
&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 ;
|
|
}
|
|
}
|
|
|
|
&db_min_ro('event_production_time_unmatched','id,unmatched_line','','','') ;
|
|
|
|
# my $upload_filehandle = $q->upload('iattachdoc') ;
|
|
|
|
# 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') {
|
|
|
|
foreach (keys %{$db{event_production_time_unmatched}}) {
|
|
|
|
# foreach (@imported_file_data) {
|
|
chomp;
|
|
|
|
next if !$db{event_production_time_unmatched}{$_}{unmatched_line} || ($db{event_production_time_unmatched}{$_}{unmatched_line} !~ /\d+/ && $db{event_production_time_unmatched}{$_}{unmatched_line} !~ /[a-zA-Z]/) ;
|
|
|
|
# ClubName;StartDate;EndDate;Production Hours;Production Seconds;CloudRecording HD Duration(Sec);Event Name;Event Type;event_code
|
|
$db{event_production_time_unmatched}{$_}{unmatched_line} =~ s/"(\d+),(\d+)"/$1$2/g;
|
|
# $_=~ s/\,/\;/g ;
|
|
|
|
my @linex = split(/\;/,$db{event_production_time_unmatched}{$_}{unmatched_line}) ;
|
|
|
|
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] ;
|
|
# # # # chop $event_code ;
|
|
|
|
$event_code = substr($event_code,-8) ;
|
|
|
|
next if $matched_event{$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 "No Unmatched Data!!!" ;
|
|
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&$format" 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"> </div></div>
|
|
<div class="row">
|
|
<div class="col-md-12">
|
|
<button type="button" class="btn btn-primary" id="importbutt">Upload</button>
|
|
<button type="button" class="btn btn-default" onclick="parent.BootstrapDialog.closeAll();">Close</button>
|
|
</div>
|
|
</div>
|
|
<div class="row"><div class="col-md-12"> </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
|
|
#
|
|
|
|
} #------------------------------------------------------------------------------------------
|
|
|
|
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="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 ; |