#!/usr/bin/perl BEGIN { use lib '/usr/home/cfg' ; require push_inc ; } use CGI qw( :standard ); use CGI::Carp qw(fatalsToBrowser); require cfg ; print header; # CGI.pm method #--------------------------------------------------------------------------------------------------------------------------------------------------------------------- use Fcntl qw(:flock); use File::Copy; use File::Basename; use Date::Calc qw(:all); use Excel::Writer::XLSX; &today; #--------------------------------------------------------------------------------------------------------------------------------------------------------------------- @ARGV = split(/\\*\&/, $ENV{'QUERY_STRING'}); $action = $ARGV[0] ; our ($q) = CGI -> new() ; our $iaction = $q -> param('iaction') || $action ; our $isaved = $q -> param('isaved') || '' ; # our $redirtype = 'search' ; # our $debug = 1 ; # our $testing = 1 ; our $cust_cols = 1 ; # display _blank_3.pm our $camera_cnt_allowed = 1000 ; our $max_cams = 10 ; if ($useropts{tickets}{$username}) { print "Content-type: text/html\n\n"; require '401.pm' ; exit ; } #-------- page opts -------------------------------------------------------------------------------------------------------------------------------------------------- # unless ($username eq 'handre' or $username eq 'rory') { print "<<<<<<<<<<<< MAINTENANCE IN PROGRESS >>>>>>>>>" ; exit ; } # &db_open_ro; # $db_ignore_open_close=1; &page_opts ; #--------------------------------------------------------------------------------------------------------------------------------------------------------------------- $ignore{"yearly_total"} = 1 ; $ignore{"yearly_totals_1"} = 1 ; $ignore{"yearly_totals_2"} = 1 ; $ignore{"yearly_totals_3"} = 1 ; $ignore{"yearly_totals_4"} = 1 ; $ignore{"yearly_totals_5"} = 1 ; if ($iaction eq 'add'){ &add_screen; &common_min_screen1; } if ($iaction eq 'edit'){ &common_min_load_params ; &edit_screen ; &common_min_screen1; } if ($iaction eq 'save'){ &common_min_load_params ; &insert ; &edit_or_list ; } if ($iaction eq 'update'){ &common_min_load_params ; &update ; &edit_or_list ; } if ($iaction eq 'copy'){ &common_min_load_params ; &duplicate ; &edit_screen; &common_min_screen1; } if ($iaction eq 'delete'){ &common_min_load_params ; &delete ; &common_min_screen2; } &common_min_action; &db_min_insert('quotes_min') ; # $db_ignore_open_close = 0 ; # do one open and one close instead of repeating it # &db_close_conn ; exit; #------------------------------------------------------------------------------------------ sub edit_or_list { # if ($i{saveandexit} eq 'yes') { # &list_screen ; # &common_min_screen3; # } # else # { &edit_screen ; &common_min_screen1; # } } #------------------------------------------------------------------------------------------ sub insert { &add_db_fields ; $i{last_update} = "$now_ccyy_mm_dd $now_hour:$now_min:$now_sec" ; $i{datetime} = "$now_ccyy_mm_dd $now_hour:$now_min:$now_sec" ; $i{user_id} = $userid ; &set_default_i_vals; # &creat_db_cols; $i{id} = &db_min_get_max($table,'id') ; &titles_for_pdf ; &common_upload_files("costings/$i{id}") ; &process_upload_ifields; &process_totals ; &db_open_upd ; our $db_ignore_open_close = 1 ; # do one open and one close instead of repeating it &switch_table ; &db_min_insert($table) ; &update_camera; &new_table ; &db_min_insert('quotes_min') ; $db_ignore_open_close = 0 ; # do one open and one close instead of repeating it &db_close_conn ; } #------------------------------------------------------------------------------------------ sub update { unless ($i{id}) { $error = qq(NO ID) ; return ; } &edit_db_fields ; # &creat_db_cols; &titles_for_pdf ; &set_default_i_vals ; $i{last_update} = "$now_ccyy_mm_dd $now_hour:$now_min:$now_sec" ; &common_upload_files("costings/$i{id}") ; &process_upload_ifields ; &process_totals ; &db_open_upd ; our $db_ignore_open_close = 1 ; # do one open and one close instead of repeating it &switch_table ; &log_changes_1 ; &db_min_upd($table,"id='$i{id}'") ; &update_camera ; &new_table ; &log_changes_2 ; &db_min_upd('quotes_min',"id='$i{id}'") ; foreach (keys %ignore_for_now) { $ignore{$_} = '' ; } $db_ignore_open_close = 0 ; # do one open and one close instead of repeating it &db_close_conn ; } #------------------------------------------------------------------------------------------ sub process_totals { my @ttlarr = ("one_time_fee","monthly_recurring_fee","annual_fee") ; my @ttlarr2 = ("subtotal","vat","total") ; foreach my $a (@ttlarr) { foreach my $b (@ttlarr2) { my $field = "$b\_$a" ; my $field_1 = "$b\_$a\_1" ; my $field_2 = "$b\_$a\_2" ; $ignore{$field_1} = 1 ; $ignore{$field_2} = 1 ; $i{$field} = qq~$i{$field_1};$i{$field_2}~ ; } } } #------------------------------------------------------------------------------------------ sub log_changes_1 { &db_min_ro($table,"*","`id`='$i{id}'",'','') ; our $line = qq~$now_year$now_mm$now_dd$now_hour$now_min$now_sec|$now_year-$now_mm-$now_dd $now_hour:$now_min:$now_sec|quote_id='$i{id}'|user_id=$userid|changed_from|~ ; my %col_to_be_updated = () ; my %ignore_for_now = () ; foreach (keys %i) { $col_to_be_updated{$_} = 1 ; } foreach my $col (keys %{$db{$table}{$i{id}}}) { next if $ignore{$col} or $hidden{$col} == 1 or substr($col,0,3) eq 'new' or not $col_to_be_updated{$col} ; if (($i{$col} or $db{$table}{$i{id}}{$col}) and $db{$table}{$i{id}}{$col} ne $i{$col}) { # $line .= qq~$col='$i{$col}',~ ; $line .= qq~$col='$db{$table}{$i{id}}{$col}',~ ; } elsif ((not $i{$col} and not $db{$table}{$i{id}}{$col}) or (($i{$col} or $db{$table}{$i{id}}{$col}) and $db{$table}{$i{id}}{$col} eq $i{$col})) { $ignore{$col} = 1 ; $ignore_for_now{$col} = 1 ; } } } #------------------------------------------------------------------------------------------ sub log_changes_2 { &db_min_ro('quotes_min','*',"id='$i{id}'",'','') ; foreach (keys %i) { $col_to_be_updated{$_} = 1 ; } foreach my $col (keys %{$db{quotes_min}{$i{id}}}) { next if $ignore{$col} or $hidden{$col} == 1 or substr($col,0,3) eq 'new' or not $col_to_be_updated{$col} ; if (($i{$col} or $db{quotes_min}{$i{id}}{$col}) and $db{quotes_min}{$i{id}}{$col} ne $i{$col}) { # $line .= qq~$col='$i{$col}',~ ; $line .= qq~$col='$db{quotes_min}{$i{id}}{$col}',~ ; } elsif ((not $i{$col} and not $db{quotes_min}{$i{id}}{$col}) or (($i{$col} or $db{quotes_min}{$i{id}}{$col}) and $db{quotes_min}{$i{id}}{$col} eq $i{$col})) { $ignore{$col} = 1 ; $ignore_for_now{$col} = 1 ; } } chop $line if $line ; &common_log_changes("events/changes_quotes.dat",$line) if $line ; # exit ; } #------------------------------------------------------------------------------------------ sub db_min_upd_raw { my ($sql) = @_ ; &common_debug($sql) ; my $sth = $dbh->prepare($sql) ; $sth->execute() or die "Could not execute SQL statement $sql ... maybe invalid? $!"; $sth->finish(); } #------------------------------------------------------------------------------------------ sub titles_for_pdf { my $do_insert = 0; # our %sec_cnt = ('a_systems' => "3-Systems", 'b_software_licence' => "3-Software Licence (Monthly Fee)", 'c_game_breakdown_analytics' => "2-Game Breakdown and Analytics (Annual Fee)", 'd_installation_kits' => "7-Installation Kits", 'e_hardware_add_ons' => "7-Hardware Add Ons", 'f_additional_services' => "7-Additional Services", 'g_match_credits' => "4-Match Credits", 'h_other' => "5-Other", 'i_rental' => "2-Rental"); $sql_insert_pdf = qq~INSERT INTO `quotes_pdf_titles` (quote_id, old_name, new_name) VALUES ~ ; db_min_ro('quotes_pdf_titles', '*', "quote_id = $i{id}", '', '') ; foreach (keys %{$db{quotes_pdf_titles}}){ next unless $db{quotes_pdf_titles}{$_}{quote_id} == $i{id} ; $quote_id_old_pdf_titles{$db{quotes_pdf_titles}{$_}{quote_id}}{$db{quotes_pdf_titles}{$_}{old_name}} = 1 ; # &common_debug("ABC: $db{quotes_pdf_titles}{$_}{quote_id}, $db{quotes_pdf_titles}{$_}{old_name} $i{id}\n") ; } # &common_debug("ABC: ") ; $do_insert = 0 ; foreach(keys %sec_cnt){ $first_name = substr($sec_cnt{$_}, 2) ; $_k = substr($_,1) ; $ignore{$_k} = 1 ; if ($quote_id_old_pdf_titles{$i{id}}{$first_name} and $i{$_k}){ $sql_update_pdf = qq(UPDATE `quotes_pdf_titles` SET new_name = '$i{$_k}' WHERE old_name = '$first_name' and quote_id = $i{id} ;) ; &db_min_upd_raw($sql_update_pdf) ; } elsif (not $quote_id_old_pdf_titles{$i{id}}{$first_name} and $i{$_k}){ $sql_insert_pdf .= qq(($i{id}, '$first_name', '$i{$_k}'),) ; $do_insert = 1 ; } elsif ($quote_id_old_pdf_titles{$i{id}}{$first_name} and not $i{$_k}) { $sql_update_pdf = qq(UPDATE `quotes_pdf_titles` SET new_name = '$first_name' WHERE old_name = '$first_name' and quote_id = $i{id} ;) ; &db_min_upd_raw($sql_update_pdf) ; } elsif (not $quote_id_old_pdf_titles{$i{id}}{$first_name} and not $i{$_k}) { $sql_insert_pdf .= qq(($i{id}, '$first_name', '$first_name'),) ; $do_insert = 1 ; } } if ($do_insert){ chop $sql_insert_pdf ; $sql_insert_pdf .= ";" ; &db_min_upd_raw($sql_insert_pdf) ; } } #------------------------------------------------------------------------------------------ sub switch_table { &db_min_ro('quote_notes','*',"id='1'",'','') ; my @note_fields = ("note","payment_term","roe","delivery_time") ; for my $row_cnt (1 .. 12) { foreach my $fields (@note_fields) { my $field = "$fields\_$row_cnt" ; $ignore{$field} = (($i{$field} && $i{$field} eq $db{quote_notes}{1}{$field}) || (($fields eq "payment_term" || $fields eq "roe") && $row_cnt > 6) || ($fields eq "delivery_time" && $row_cnt > 4)) ? 1 : 0 ; $i{$field} = (!$i{$field} && $db{quote_notes}{1}{$field}) ? "-1" : $i{$field} ; } } # local @ttlarr = ("one_time_fee","monthly_recurring_fee","annual_fee") ; # local @ttlarr2 = ("subtotal","vat","total") ; # foreach my $a (@ttlarr) { # foreach my $b (@ttlarr2) { # my $field_1 = "1_$b\_$a" ; # my $field_2 = "2_$b\_$a" ; # my $field = "$b\_$a" ; # $i{$field} = qq~$i{$field_1};$i{$field_2}~ ; # $ignore{$field_1} = 1 ; # $ignore{$field_2} = 1 ; # } # } %y = () ; for (1 .. 12) { $y{1}{$_} = "curr_amnt_" . "$_" . "_costing_additional_services"; $y{2}{$_} = "curr_amnt_" . "$_" . "_costing_game_breakdown_analytics"; $y{3}{$_} = "curr_amnt_" . "$_" . "_costing_hardware_add_ons"; $y{4}{$_} = "curr_amnt_" . "$_" . "_costing_installation_kits"; $y{5}{$_} = "curr_amnt_" . "$_" . "_costing_match_credits"; $y{6}{$_} = "curr_amnt_" . "$_" . "_costing_other"; $y{7}{$_} = "curr_amnt_" . "$_" . "_costing_rental"; $y{8}{$_} = "curr_amnt_" . "$_" . "_costing_software_licence"; $y{9}{$_} = "curr_amnt_" . "$_" . "_costing_systems"; $y{10}{$_} = "excl_" . "$_" . "_costing_additional_services"; $y{11}{$_} = "excl_" . "$_" . "_costing_game_breakdown_analytics"; $y{12}{$_} = "excl_" . "$_" . "_costing_hardware_add_ons"; $y{13}{$_} = "excl_" . "$_" . "_costing_installation_kits"; $y{14}{$_} = "excl_" . "$_" . "_costing_match_credits"; $y{15}{$_} = "excl_" . "$_" . "_costing_other"; $y{16}{$_} = "excl_" . "$_" . "_costing_rental"; $y{17}{$_} = "excl_" . "$_" . "_costing_software_licence"; $y{18}{$_} = "excl_" . "$_" . "_costing_systems"; $y{19}{$_} = "excl_" . "$_" . "_delivery_times"; $y{20}{$_} = "excl_" . "$_" . "_notes"; $y{21}{$_} = "excl_" . "$_" . "_payment_terms"; $y{22}{$_} = "excl_" . "$_" . "_purchase_summary"; $y{23}{$_} = "excl_" . "$_" . "_roes"; $y{24}{$_} = "excl_" . "$_" . "_terms"; $y{25}{$_} = "item_" . "$_" . "_costing_additional_services"; $y{26}{$_} = "item_" . "$_" . "_costing_game_breakdown_analytics"; $y{27}{$_} = "item_" . "$_" . "_costing_hardware_add_ons"; $y{28}{$_} = "item_" . "$_" . "_costing_installation_kits"; $y{29}{$_} = "item_" . "$_" . "_costing_match_credits"; $y{30}{$_} = "item_" . "$_" . "_costing_other"; $y{31}{$_} = "item_" . "$_" . "_costing_rental"; $y{32}{$_} = "item_" . "$_" . "_costing_software_licence"; $y{33}{$_} = "item_" . "$_" . "_costing_systems"; $y{34}{$_} = "item_" . "$_" . "_purchase_summary"; $y{35}{$_} = "item_ttl_" . "$_" . "_costing_additional_services"; $y{36}{$_} = "item_ttl_" . "$_" . "_costing_game_breakdown_analytics"; $y{37}{$_} = "item_ttl_" . "$_" . "_costing_hardware_add_ons"; $y{38}{$_} = "item_ttl_" . "$_" . "_costing_installation_kits"; $y{39}{$_} = "item_ttl_" . "$_" . "_costing_match_credits"; $y{40}{$_} = "item_ttl_" . "$_" . "_costing_other"; $y{41}{$_} = "item_ttl_" . "$_" . "_costing_rental"; $y{42}{$_} = "item_ttl_" . "$_" . "_costing_software_licence"; $y{43}{$_} = "item_ttl_" . "$_" . "_costing_systems"; $y{44}{$_} = "qty_" . "$_" . "_costing_additional_services"; $y{45}{$_} = "qty_" . "$_" . "_costing_game_breakdown_analytics"; $y{46}{$_} = "qty_" . "$_" . "_costing_hardware_add_ons"; $y{47}{$_} = "qty_" . "$_" . "_costing_installation_kits"; $y{48}{$_} = "qty_" . "$_" . "_costing_match_credits"; $y{49}{$_} = "qty_" . "$_" . "_costing_other"; $y{50}{$_} = "qty_" . "$_" . "_costing_rental"; $y{51}{$_} = "qty_" . "$_" . "_costing_software_licence"; $y{52}{$_} = "qty_" . "$_" . "_costing_systems"; $y{53}{$_} = "usd_amnt_" . "$_" . "_costing_additional_services"; $y{54}{$_} = "usd_amnt_" . "$_" . "_costing_game_breakdown_analytics"; $y{55}{$_} = "usd_amnt_" . "$_" . "_costing_hardware_add_ons"; $y{56}{$_} = "usd_amnt_" . "$_" . "_costing_installation_kits"; $y{57}{$_} = "usd_amnt_" . "$_" . "_costing_match_credits"; $y{58}{$_} = "usd_amnt_" . "$_" . "_costing_other"; $y{59}{$_} = "usd_amnt_" . "$_" . "_costing_rental"; $y{60}{$_} = "usd_amnt_" . "$_" . "_costing_software_licence"; $y{61}{$_} = "usd_amnt_" . "$_" . "_costing_systems"; $y{62}{$_} = "vat_" . "$_" . "_costing_additional_services"; $y{63}{$_} = "vat_" . "$_" . "_costing_game_breakdown_analytics"; $y{64}{$_} = "vat_" . "$_" . "_costing_hardware_add_ons"; $y{65}{$_} = "vat_" . "$_" . "_costing_installation_kits"; $y{66}{$_} = "vat_" . "$_" . "_costing_match_credits"; $y{67}{$_} = "vat_" . "$_" . "_costing_other"; $y{68}{$_} = "vat_" . "$_" . "_costing_rental"; $y{69}{$_} = "vat_" . "$_" . "_costing_software_licence"; $y{70}{$_} = "vat_" . "$_" . "_costing_systems"; foreach my $a (keys %y){ foreach my $b (keys %{$y{$a}}){ $ignore{$y{$a}{$b}} = 1 ; $x{$a}{$b} = $i{$y{$a}{$b}} ; } } } } #------------------------------------------------------------------------------------------ sub new_table { # my ($insert_part) = @_ ; $iid = $i{id} ; %i = () ; $i{id} = $iid ; for (1 .. 12){ $i{curr_amnt_costing_additional_services} .= "$x{1}{$_};" ; $i{curr_amnt_costing_game_breakdown_analytics} .= "$x{2}{$_};" ; $i{curr_amnt_costing_hardware_add_ons} .= "$x{3}{$_};" ; $i{curr_amnt_costing_installation_kits} .= "$x{4}{$_};" ; $i{curr_amnt_costing_match_credits} .= "$x{5}{$_};" ; $i{curr_amnt_costing_other} .= "$x{6}{$_};" ; $i{curr_amnt_costing_rental} .= "$x{7}{$_};" ; $i{curr_amnt_costing_software_licence} .= "$x{8}{$_};" ; $i{curr_amnt_costing_systems} .= "$x{9}{$_};" ; $i{excl_costing_additional_services} .= "$x{10}{$_};" ; $i{excl_costing_game_breakdown_analytics} .= "$x{11}{$_};" ; $i{excl_costing_hardware_add_ons} .= "$x{12}{$_};" ; $i{excl_costing_installation_kits} .= "$x{13}{$_};" ; $i{excl_costing_match_credits} .= "$x{14}{$_};" ; $i{excl_costing_other} .= "$x{15}{$_};" ; $i{excl_costing_rental} .= "$x{16}{$_};" ; $i{excl_costing_software_licence} .= "$x{17}{$_};" ; $i{excl_costing_systems} .= "$x{18}{$_};" ; $i{excl_delivery_times} .= "$x{19}{$_};" ; $i{excl_notes} .= "$x{20}{$_};" if $x{20}{$_}; $i{excl_notes} .= "0;" if not $x{20}{$_}; $i{excl_payment_terms} .= "$x{21}{$_};" ; $i{excl_purchase_summary} .= "$x{22}{$_};" if $x{22}{$_}; $i{excl_purchase_summary} .= "0;" if not $x{22}{$_}; $i{excl_roes} .= "$x{23}{$_};" ; $i{excl_terms} .= "$x{24}{$_};" ; $i{item_costing_additional_services} .= "$x{25}{$_};" ; $i{item_costing_game_breakdown_analytics} .= "$x{26}{$_};" ; $i{item_costing_hardware_add_ons} .= "$x{27}{$_};" ; $i{item_costing_installation_kits} .= "$x{28}{$_};" ; $i{item_costing_match_credits} .= "$x{29}{$_};" ; $i{item_costing_other} .= "$x{30}{$_};" ; $i{item_costing_rental} .= "$x{31}{$_};" ; $i{item_costing_software_licence} .= "$x{32}{$_};" ; $i{item_costing_systems} .= "$x{33}{$_};" ; $i{item_purchase_summary} .= "$x{34}{$_};" ; $i{item_ttl_costing_additional_services} .= "$x{35}{$_};" ; $i{item_ttl_costing_game_breakdown_analytics} .= "$x{36}{$_};" ; $i{item_ttl_costing_hardware_add_ons} .= "$x{37}{$_};" ; $i{item_ttl_costing_installation_kits} .= "$x{38}{$_};" ; $i{item_ttl_costing_match_credits} .= "$x{39}{$_};" ; $i{item_ttl_costing_other} .= "$x{40}{$_};" ; $i{item_ttl_costing_rental} .= "$x{41}{$_};" ; $i{item_ttl_costing_software_licence} .= "$x{42}{$_};" ; $i{item_ttl_costing_systems} .= "$x{43}{$_};" ; $i{qty_costing_additional_services} .= "$x{44}{$_};" ; $i{qty_costing_game_breakdown_analytics} .= "$x{45}{$_};" ; $i{qty_costing_hardware_add_ons} .= "$x{46}{$_};" ; $i{qty_costing_installation_kits} .= "$x{47}{$_};" ; $i{qty_costing_match_credits} .= "$x{48}{$_};" ; $i{qty_costing_other} .= "$x{49}{$_};" ; $i{qty_costing_rental} .= "$x{50}{$_};" ; $i{qty_costing_software_licence} .= "$x{51}{$_};" ; $i{qty_costing_systems} .= "$x{52}{$_};" ; $i{usd_amnt_costing_additional_services} .= "$x{53}{$_};" ; $i{usd_amnt_costing_game_breakdown_analytics} .= "$x{54}{$_};" ; $i{usd_amnt_costing_hardware_add_ons} .= "$x{55}{$_};" ; $i{usd_amnt_costing_installation_kits} .= "$x{56}{$_};" ; $i{usd_amnt_costing_match_credits} .= "$x{57}{$_};" ; $i{usd_amnt_costing_other} .= "$x{58}{$_};" ; $i{usd_amnt_costing_rental} .= "$x{59}{$_};" ; $i{usd_amnt_costing_software_licence} .= "$x{60}{$_};" ; $i{usd_amnt_costing_systems} .= "$x{61}{$_};" ; $i{vat_costing_additional_services} .= "$x{62}{$_};" ; $i{vat_costing_game_breakdown_analytics} .= "$x{63}{$_};" ; $i{vat_costing_hardware_add_ons} .= "$x{64}{$_};" ; $i{vat_costing_installation_kits} .= "$x{65}{$_};" ; $i{vat_costing_match_credits} .= "$x{66}{$_};" ; $i{vat_costing_other} .= "$x{67}{$_};" ; $i{vat_costing_rental} .= "$x{68}{$_};" ; $i{vat_costing_software_licence} .= "$x{69}{$_};" ; $i{vat_costing_systems} .= "$x{70}{$_};" ; } # if ($insert_part){ # $sql_insert_1 = qq~INSERT INTO `quotes_min` (~ ; # $sql_insert_2 = qq~VALUES (~ ; # } foreach (keys %i){ next if $_ eq 'id' ; chop $i{$_} ; # if ($insert_part){ # $sql_insert_1 .= qq~$_,~ ; # $sql_insert_2 .= qq~'$i{$_}',~ ; # } } # if ($insert_part){ # chop $sql_insert_1 ; # chop $sql_insert_2 ; # $sql_insert_2 .= qq~) ;~ ; # $final_insert = $sql_insert_1 . $sql_insert_2 ; # &db_min_upd_raw($final_insert) ; # } } #------------------------------------------------------------------------------------------ sub update_camera { # &common_debug("1. update_camera : $i{quote_nr}"); unless ($i{quote_nr}) { $error = qq~NO QUOTE NR~ ; return ; } # &common_debug("update_camera : $error"); $q{quote_nr} = $i{quote_nr} ; # $q{quote_ref} = $i{ref} ; $q{id} = $i{id} ; %i=(); $i{quote_delivery_date} = NULL ; $i{quote_active_date} = NULL ; $i{quote_ew_date_from} = NULL ; $i{quote_ew_date_to} = NULL ; $i{quote_cam_desc} = NULL ; $i{quote_cam_excl} = 0 ; $i{quote_cam_num} = 0 ; $i{quote_nr} = 0 ; $i{quote_ref} = NULL ; # &db_min_ro('cameras','*',"quote_nr='$q{quote_nr}'",'','') ; # &db_min_ro('quotes','*','','','') ; &db_min_upd('cameras',"quote_nr='$q{quote_nr}'") ; # remove all previous links to this quote # $i{quote_nr} = $q{quote_nr} ; # $i{quote_ref} = $q{quote_ref} ; for (1 .. $camera_cnt_allowed) { # &common_debug("$_. camera_nr_$_\_camera_details [".$q{"camera_nr_$_\_camera_details"}."]"); next unless $q{"camera_nr_$_\_camera_details"} ; %i=(); $i{quote_nr} = $q{quote_nr} ; $i{quote_delivery_date} = $q{"delivery_date_$_\_camera_details"} if $q{"delivery_date_$_\_camera_details"} ; $i{quote_active_date} = $q{"active_date_$_\_camera_details"} if $q{"active_date_$_\_camera_details"} ; $i{quote_ew_date_from} = $q{"ew_date_from_$_\_camera_details"} if $q{"ew_date_from_$_\_camera_details"} ; $i{quote_ew_date_to} = $q{"ew_date_to_$_\_camera_details"} if $q{"ew_date_to_$_\_camera_details"} ; $i{quote_cam_desc} = $q{"description_$_\_camera_details"} if $q{"description_$_\_camera_details"} ; $i{quote_cam_excl} = $q{"excl_$_\_camera_details"} if $q{"excl_$_\_camera_details"} ; $i{quote_cam_num} = $_ if $_ ; $i{quote_ref} = $q{"ref_$_\_camera_details"} if $q{"ref_$_\_camera_details"} ; my @valarr = split(/\:/,$q{"camera_nr_$_\_camera_details"}) ; # $id:camera_nr:camera_system_id:serial_nr:date_delivered:camera_system:item_code:date_active my $abrv_cam_system = substr($valarr[1],0,3); # &common_debug("[".$q{"camera_nr_$_\_camera_details"}."] abrv_cam_system=$abrv_cam_system, abrv_prev_cam_system=$abrv_prev_cam_system, prev_cam_nr=$prev_cam_nr, link to: c_excl_$_\_camera_details") ; if ($abrv_cam_system eq 'VPU' and ($abrv_prev_cam_system eq 'S1' or $abrv_prev_cam_system eq 'S2' or $abrv_prev_cam_system_other eq 'Prime' or $abrv_prev_cam_system_other eq 'Coach')) { # $trigger_jquery .= qq~\$("#c_excl_$_\_camera_details").append("");\$('[data-toggle="tooltip"]').tooltip({html:true}) ;~; my $s1_cnt = $_-1; $i{quote_linked_vpu} = $q{"serial_nr_$s1_cnt\_camera_details"} if $q{"serial_nr_$s1_cnt\_camera_details"} ; } $abrv_prev_cam_system = substr($abrv_cam_system,0,2); $abrv_prev_cam_system_other = substr($abrv_cam_system,0,5); $prev_cam_nr = $valarr[1] ; my $cam_id = $valarr[0] ; next unless $cam_id ; &db_min_upd('cameras',"id='$cam_id'") ; # add the new details from the screen # $i{quote_linked_vpu} = '' ; } $i{id} = $q{id} ; } #------------------------------------------------------------------------------------------ sub duplicate { unless ($i{id}) { $error = qq~NO ID~ ; return ; } local $copy_id = $i{id} ; &db_min_copy($table,$copy_id) ; &db_min_copy('quotes_min',$copy_id) ; &common_min_copy_files("$htmlpath/uploads/workings/$copy_id","$htmlpath/uploads/workings/$i{id}") ; } #------------------------------------------------------------------------------------------ sub delete { my $dbid = 'id' ; if ($uniq_id) { $dbid = $uniq_id ; } unless ($i{$dbid}) { $error = uc "NO $dbid" ; return ; } &db_min_delete('quotes',"$dbid='$i{$dbid}'") ; &db_min_delete('quotes_min',"$dbid='$i{$dbid}'") ; &common_min_delete_files("$htmlpath/uploads/workings/$i{$dbid}") ; }#------------------------------------------------------------------------------------------ sub process_upload_ifields { foreach (keys %uploads_file) { $i{$_} = $uploads_file{$_} ; $ignore{$_} = '' ; } } #------------------------------------------------------------------------------------------ sub set_default_i_vals { my @camera_details = ('camera_nr_','serial_nr_','description_','active_date_','delivery_date_','ew_date_from_','ew_date_to_','ref_','excl_') ; for (1 .. $camera_cnt_allowed) { foreach my $cdet_ (@camera_details) { my $col = "$cdet_$_\_camera_details"; $ignore{$col} = 1 ; # set the value to only be the id $q{$col} = $i{$col} ; # set the value to only be the id } } my $srch_where_sql = "`id`='$i{id}'" ; $iaction eq 'save' ? &db_min_ro($table,'*','','id DESC',1) : &db_min_ro($table,'*',$srch_where_sql,'','') ; foreach (sort { $col_name{$a} cmp $col_name{$b} } keys %col_name) { $col = $col_name{$_} ; &common_min_debug("1. [$col] [$i{$col}]"); if (substr($col,0,4) eq 'vat_' or substr($col,0,5) eq 'excl_' or substr($col,0,4) eq 'qty_') { unless ($i{$col}) { $i{$col} = 0 ; } } # only create the column if it doesn't exist if (substr($col,0,9) eq 'usd_amnt_' or substr($col,0,10) eq 'curr_amnt_' or substr($col,0,9) eq 'item_ttl_' or substr($col,-6,6) eq '_total') { unless ($i{$col}) { $i{$col} = '0.00' ; } } # # if (substr($col,0,14) eq 'delivery_date_' or substr($col,0,12) eq 'active_date_') { # if (substr($col,0,14) eq 'delivery_date_' or substr($col,0,12) eq 'active_date_' or substr($col,0,8) eq 'ew_date_') { # unless ($i{$col}) { $i{$col} = NULL ; } # } # if ((substr($col,6,9) eq '_costing_' and substr($col,0,5) eq 'item_' and $i{$col}) or ($col eq 'quote_to' and $i{$col}) or (substr($col,-15,15) eq '_camera_details' and substr($col,0,10) eq 'camera_nr_' and $i{$col})) { # if ((substr($col,6,9) eq '_costing_' and substr($col,0,5) eq 'item_' and $i{$col}) or ($col eq 'quote_to' and $i{$col})) { if (((substr($col,6,9) eq '_costing_' or substr($col,7,9) eq '_costing_') and substr($col,0,5) eq 'item_' and $i{$col}) or ($col eq 'quote_to' and $i{$col})) { # item_1_costing_match_credits my @valarr = split(/\:/,$i{$col}) ; $i{$col} = $valarr[0] ; # set the value to only be the id } # if (substr($col,-15,15) eq '_camera_details') { # # if (substr($col,0,9) eq 'camera_nr') { # # my @valarr = split(/\:/,$i{$col}) ; # # $q{$col} = $i{$col} ; # set the value to only be the id # # $i{$col} = $valarr[0] ; # set the value to only be the id # # } elsif (substr($col,0,5) eq 'excl_') { # # # do nothing # # } else { # $ignore{$col} = 1 ; # set the value to only be the id # $q{$col} = $i{$col} ; # set the value to only be the id # # } # } # &common_min_debug("2. [$col] [$i{$col}]"); } $i{terms} = 0 unless $i{terms} ; $i{quote_accepted} = 0 unless $i{quote_accepted} ; $i{quote_pending} = 0 unless $i{quote_pending} ; $i{quote_cancelled} = 0 unless $i{quote_cancelled} ; $i{annual_invoice} = 0 unless $i{annual_invoice} ; $i{period} = 0 unless $i{period} ; $i{max_cams} = 0 unless $i{max_cams} ; $ignore{invoice_date} = 1 unless length($i{invoice_date}) == 10 ; } #---------------------------------------------------------------------------------------- sub list_screen { &db_open_ro ; our $db_ignore_open_close = 1 ; # do one open and one close instead of repeating it # my $tables = 'towns,airport_codes' ; # &db_min_ro($tables,'towns.id,towns.airport_code,airport_codes.name,towns.name','towns.airport_code=airport_codes.iata_code','','') ; # foreach my $id (keys %{$db{$tables}}) { $user{$id} = $db{$tables}{$id}{name} ; } # &db_min_ro('users','id,name','','','') ; # foreach my $id (keys %{$db{users}}) { $user{$id} = $db{users}{$id}{name} ; } # &db_min_ro('customers','id,name','','','') ; # foreach my $id (keys %{$db{customers}}) { $customers{$id} = $db{customers}{$id}{name} ; } # &db_min_ro('camera_systems','*','','','') ; # foreach my $id (keys %{$db{camera_systems}}) { $camera_system{$id} = $db{camera_systems}{$id}{name} ; } # &db_min_ro('countries','*','','','') ; # foreach my $id (keys %{$db{countries}}) { $country{$id} = $db{countries}{$id}{name} ; } if ($iaction eq 'accepted' or $i{options} eq 'accepted') { push @report_sql, "$table.quote_accepted='1'" ; } if ($iaction eq 'closed' or $i{options} eq 'closed') { push @report_sql, "($table.quote_cancelled='1' OR ($table.quote_expiry<'$now_year-$now_mm-$now_dd' AND $table.quote_accepted<>'1'))" ; } if ($iaction eq 'pending' or $i{options} eq 'pending') { push @report_sql, "$table.quote_pending='1'" ; } if ($iaction eq 'list' or $i{options} eq 'list') { push @report_sql, "$table.quote_cancelled<>'1'" ; } my $t1 = $table ; my $t2 = 'countries' ; my $t3 = 'camera_systems' ; my $t4 = 'customers' ; our $tables = "$t1,$t2,$t3,$t4" ; push @report_sql, "($t1.country_shipped=$t2.id)" ; push @report_sql, "($t1.camera_system_id=$t3.id)" ; push @report_sql, "($t1.quote_to=$t4.id)" ; my $srch_where_sql = join(' AND ', @report_sql) ; &db_min_ro($tables,"$t1.id AS 'quote_id',$t1.quote_nr,$t1.ref,$t1.qty,$t1.quote_expiry,$t1.quote_to,$t1.invoice_nr,$t1.quote_date,$t1.type,$t1.currency,$t1.country_shipped,$t1.total_1_purchase_summary,$t1.total_2_purchase_summary,$t1.total_3_purchase_summary,$t1.quote_cancelled,$t1.quote_accepted,$t1.quote_pending,$t1.activetab,$t2.name AS 'country',$t3.name AS 'camera_system',$t4.name AS 'customer'",$srch_where_sql,'','') ; $db_ignore_open_close = 0 ; # do one open and one close instead of repeating it &db_close_conn ; &tab_hash ; # load for PDF links # &common_load_quote_vars("cameras.quote_nr>0 AND cameras.quote_cam_num='1' AND (cameras.quote_delivery_date IS NOT NULL or cameras.quote_active_date IS NOT NULL) AND cameras.quote_nr=quotes.quote_nr"); &common_load_quote_vars("cameras.quote_nr>0 AND cameras.quote_nr=quotes.quote_nr"); # foreach (keys %quote_cam_delivery_date) { # $store_quote_cam_delivery_date{$_}{1} = $quote_cam_delivery_date{$_}{1} ; # } # foreach (keys %quote_cam_delivery_date) { # $store_quote_cam_active_date{$_}{1} = $quote_cam_active_date{$_}{1} ; # } "e_list ; # load for PDF links # our $fnsort = 1 ; # used &common_min_screen3 for fnsort column &common_min_table if ($iaction eq 'update' or $iaction eq 'save'){ &common_min_extra_crumb("list-$lcpage\s","List $ucfirstpage\s") ; } } #------------------------------------------------------------------------------------------ sub quote_list { our @sql_col_display = ("nr","date","customer","ref","country","camera_system_id","qty","curr","amount","invoice_nr","type","date_delivered","date_active","flag","") ; if ($i{export_to_excel}) { &report_xls_export_header("Quotes",'quotes') ; } # &common_min_debug("[$table]"); our $open_new_tab = qq~target="_blank"~ ; foreach my $id (sort {$b <=> $a} keys %{$db{$tables}}) { # &common_min_debug("[$id] [$tables] [$db{$tables}{$id}{quote_nr} ]"); unless ($id) { next ; } our $quote_expiry = $db{$tables}{$id}{quote_expiry} ; $quote_expiry =~ s/\-//iog ; # my $quote_date = $db{$tables}{$id}{quote_date} ; $quote_date =~ s/\-//iog ; # if ($quote_expiry and $quote_expiry<"$now_year$now_mm$now_dd" and $iaction ne 'report' and $iaction ne 'accepted' and $iaction ne 'closed' and $iaction ne 'all') { next ; } # $yr = substr($quote_date, 0, 4) ; # $mm = substr($quote_date, 4, 2) ; # $dd = substr($quote_date, 6, 2) ; # my ($yr2,$mm2,$dd2) = Add_Delta_Days($yr,$mm,$dd,60); # # $mm2 = sprintf("%02s", $mm2) ; # $dd2 = sprintf("%02s", $dd2) ; # $quote_date = "$yr2$mm2$dd2" ; # if ($quote_date and $quote_date<"$now_year$now_mm$now_dd" and $db{$tables}{$id}{quote_accepted}!=1 and # $iaction ne 'report' and $iaction ne 'accepted' and $iaction ne 'closed' and $iaction ne 'all' and # $i{options} ne 'accepted' and $i{options} ne 'closed' and $i{options} ne 'all') # { next ; } if ($quote_expiry and $quote_expiry<"$now_year$now_mm$now_dd" and $db{$tables}{$id}{quote_accepted}!=1 and $iaction ne 'report' and $iaction ne 'accepted' and $iaction ne 'closed' and $iaction ne 'all' and $i{options} ne 'accepted' and $i{options} ne 'closed' and $i{options} ne 'all') { next ; } # &common_min_debug("[$id] [$tables] [$db{$tables}{$id}{quote_nr} ]"); $print_tbody .= qq~~ ; $xlscol=0; my $quote_nr = $db{$tables}{$id}{quote_nr} ; foreach (@sql_col_display) { unless ($_) { next ; } # blank for the buttons column my $val = $db{$tables}{$id}{$_} ; my $align = '' ; my $nowrap = '' ; if ($_ eq 'ref') { $nowrap = 'nowrap' ; ; } if ($_ eq 'customer') { $val = $db{$tables}{$id}{customer} ; $nowrap = 'nowrap' ; ; } if ($_ eq 'date') { $val = $db{$tables}{$id}{quote_date} ; } if ($_ eq 'date_delivered') { $val = $quote_cam_delivery_date{$quote_nr}{1} ; $align = qq~ class="dt-center"~ ; } if ($_ eq 'date_active') { $val = $quote_cam_active_date{$quote_nr}{1} ; $align = qq~ class="dt-center"~ ; } if ($_ eq 'type') { $val = ucfirst $db{$tables}{$id}{type} ; $align = qq~ class="dt-center"~ ; } if ($_ eq 'camera_system_id') { $val = $db{$tables}{$id}{camera_system} ; } if ($_ eq 'curr') { $val = $db{$tables}{$id}{currency} ; $align = qq~ class="dt-center"~ ; } if ($_ eq 'country') { $val = $db{$tables}{$id}{country} ; } # if ($_ eq 'qty') { $val = $cnt_cams{$quote_nr} ; } if ($_ eq 'qty') { $val = $db{$tables}{$id}{qty} ; $val = '' if $val==0; $align = qq~ class="dt-center"~ ; } if ($_ eq 'currency') { $align = qq~ class="dt-center"~ ; } if ($_ eq 'amount') { my $ttl = sprintf("%0.2f",($db{$tables}{$id}{total_1_purchase_summary} + $db{$tables}{$id}{total_2_purchase_summary} + $db{$tables}{$id}{total_3_purchase_summary})) ; $val = &common_commify($ttl) ; $align = qq~ class="dt-right"~ ; } if ($_ eq 'nr') { $val = &common_min_get_quote_button($id,$tables) ; # my $class = 'info' ; # if ($db{$tables}{$id}{quote_cancelled}) { # $class = 'danger' ; # } elsif ($db{$tables}{$id}{quote_accepted}) { # $class = 'success' ; # } elsif ($quote_expiry and $quote_expiry<"$now_year$now_mm$now_dd") { # $class = 'danger' ; # } elsif ($db{$tables}{$id}{quote_pending}) { # $class = 'warning' ; # } # $val = qq~$quote_nr~ ; } if ($i{export_to_excel}) { &report_xls_export_process_cell('','',$val) ; # ------------------------------------------------------------------------- HTML AFTER $xlscol++; } $print_tbody .= qq~$val~ ; } my $delete_butt = '' ; my $edit_butt = '' ; my $quote_nr = $quote_nr ; $quote_nr =~ s/'//iog; unless ($quote_nr) { $quote_nr = $id ; } # if ($useropts{boss}{$username} or $useropts{super}{$username}) { } $edit_butt = qq~~ ; $delete_butt = qq~~ ; $copy_butt = qq~~; $pdf_butt = qq~~; $print_tbody .= qq~ $edit_butt $copy_butt $delete_butt $pdf_butt~ ; if ($i{export_to_excel}) { $xlsrow++ ; } } if ($i{export_to_excel}) { &report_xls_export_footer('L',15,'quotes') ; } # $fnsortcol = 8 ; # order by ol devices col $print_more_boxes .= qq~


~ ; &common_min_extra_crumb("search-$lcpage\s","Search Screen") ; } #------------------------------------------------------------------------------- sub thead { &common_min_thead ; } #------------------------------------------------------------------------------- sub add_db_fields { &hidden_fields ; $hidden{id} = 2 ; } #------------------------------------------------------------------------------------------ sub edit_db_fields { &hidden_fields ; $hidden{id} = 1 ; # $readonly{ref} = 'READONLY' ; } #------------------------------------------------------------------------------------------ sub hidden_fields { $ignore{iattachcosting_delivery_note} = 1 ; $ignore{iattachcosting_other_1} = 1 ; $ignore{iattachcosting_other_2} = 1 ; $ignore{iattachcosting_other_3} = 1 ; $ignore{iattachcosting_photos} = 1 ; $ignore{iattachcosting_po} = 1 ; $ignore{iattachcosting_signed_quote} = 1 ; $ignore{iattachcosting_sla} = 1 ; $ignore{iattachcosting_t_cs} = 1 ; $ignore{date_to} = 1 ; $ignore{date_from} = 1 ; $ignore{iaction} = 1 ; $required{qty} = 4 ; # number $required{quote_to} = 1 ; $required{quote_nr} = 1 ; $readonly{quote_nr} = 'READONLY' ; $required{currency} = 1 ; $required{roe} = 1 ; $required{period} = 1 ; $required{max_cams} = 1 ; $hidden{quote_date} = 2 ; $hidden{last_update} = 2 ; $hidden{user_id} = 2 ; $hidden{datetime} = 2 ; $ignore{saveandexit} = 1 ; $trigger_jquery_raw .= qq~var curr_changed=0;~; $trigger_jquery_raw .= qq~\$.validate();~; $extra_js .= qq~~; } #------------------------------------------------------------------------------------------ sub add_screen { &db_open_ro; $db_ignore_open_close=1; $selected{max_cams}{10} = 'SELECTED'; &select_opts('') ; %col_name = () ; &db_min_ro($table,'*',"id > 1 AND id < 10",'','') ; $page_title = substr($page_title,0,-1) ; $db{$table}{''}{quote_nr} = &db_min_get_max($table,'quote_nr') ; $db{$table}{''}{qty} = 0 ; # if ($username eq 'rory') { $db{$table}{''}{quote_nr} = 1000 ; } &add_db_fields ; &build_boxes('','save') ; $db_ignore_open_close=0; &db_close_conn; } #------------------------------------------------------------------------------------------ sub edit_screen { unless ($i{id}) { $warning = qq(NO ID) ; return ; } &db_open_ro; $db_ignore_open_close=1; # DO NOT DELETE !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! ---------------------------------------------------------------------- #transfer data from event_quotes to event_quotes_min # &common_debug("DELETE FROM quotes_min WHERE id != 203 ") ; # &common_debug("") ; # $sql_insert = "INSERT INTO quotes_min ( # id, # curr_amnt_costing_additional_services , # curr_amnt_costing_game_breakdown_analytics , # curr_amnt_costing_hardware_add_ons , # curr_amnt_costing_installation_kits , # curr_amnt_costing_match_credits, # curr_amnt_costing_other , # curr_amnt_costing_rental , # curr_amnt_costing_software_licence , # curr_amnt_costing_systems , # excl_costing_additional_services , # excl_costing_game_breakdown_analytics , # excl_costing_hardware_add_ons , # excl_costing_installation_kits , # excl_costing_match_credits , # excl_costing_other, # excl_costing_rental , # excl_costing_software_licence , # excl_costing_systems , # excl_delivery_times, # excl_notes, # excl_payment_terms , # excl_purchase_summary , # excl_roes , # excl_terms , # item_costing_additional_services, # item_costing_game_breakdown_analytics, # item_costing_hardware_add_ons, # item_costing_installation_kits, # item_costing_match_credits, # item_costing_other, # item_costing_rental, # item_costing_software_licence, # item_costing_systems, # item_purchase_summary , # item_ttl_costing_additional_services, # item_ttl_costing_game_breakdown_analytics, # item_ttl_costing_hardware_add_ons, # item_ttl_costing_installation_kits, # item_ttl_costing_match_credits, # item_ttl_costing_other, # item_ttl_costing_rental, # item_ttl_costing_software_licence, # item_ttl_costing_systems, # qty_costing_additional_services, # qty_costing_game_breakdown_analytics, # qty_costing_hardware_add_ons, # qty_costing_installation_kits, # qty_costing_match_credits, # qty_costing_other, # qty_costing_rental, # qty_costing_software_licence, # qty_costing_systems, # usd_amnt_costing_additional_services, # usd_amnt_costing_game_breakdown_analytics, # usd_amnt_costing_hardware_add_ons, # usd_amnt_costing_installation_kits, # usd_amnt_costing_match_credits, # usd_amnt_costing_other, # usd_amnt_costing_rental, # usd_amnt_costing_software_licence, # usd_amnt_costing_systems, # vat_costing_additional_services, # vat_costing_game_breakdown_analytics, # vat_costing_hardware_add_ons, # vat_costing_installation_kits, # vat_costing_match_credits, # vat_costing_other, # vat_costing_rental, # vat_costing_software_licence, # vat_costing_systems # ) VALUES " ; # $sql_select = "" ; # %concat = () ; # for (1 .. 12){ # $concat{20} .= "excl_" . "$_" . "_notes,';',"; # } # for (1 .. 7){ # $concat{1} .= "curr_amnt_" . "$_" . "_costing_additional_services,';',"; # $concat{3} .= "curr_amnt_" . "$_" . "_costing_hardware_add_ons,';',"; # $concat{4} .= "curr_amnt_" . "$_" . "_costing_installation_kits,';',"; # $concat{10} .= "excl_" . "$_" . "_costing_additional_services,';',"; # $concat{12} .= "excl_" . "$_" . "_costing_hardware_add_ons,';',"; # $concat{13} .= "excl_" . "$_" . "_costing_installation_kits,';',"; # $concat{25} .= "item_" . "$_" . "_costing_additional_services,';',"; # $concat{27} .= "item_" . "$_" . "_costing_hardware_add_ons,';',"; # $concat{28} .= "item_" . "$_" . "_costing_installation_kits,';',"; # $concat{35} .= "item_ttl_" . "$_" . "_costing_additional_services,';',"; # $concat{37} .= "item_ttl_" . "$_" . "_costing_hardware_add_ons,';',"; # $concat{38} .= "item_ttl_" . "$_" . "_costing_installation_kits,';',"; # $concat{44} .= "qty_" . "$_" . "_costing_additional_services,';',"; # $concat{46} .= "qty_" . "$_" . "_costing_hardware_add_ons,';',"; # $concat{47} .= "qty_" . "$_" . "_costing_installation_kits,';',"; # $concat{53} .= "usd_amnt_" . "$_" . "_costing_additional_services,';',"; # $concat{55} .= "usd_amnt_" . "$_" . "_costing_hardware_add_ons,';',"; # $concat{56} .= "usd_amnt_" . "$_" . "_costing_installation_kits,';',"; # $concat{62} .= "vat_" . "$_" . "_costing_additional_services,';',"; # $concat{64} .= "vat_" . "$_" . "_costing_hardware_add_ons,';',"; # $concat{65} .= "vat_" . "$_" . "_costing_installation_kits,';',"; # } # for (1 .. 6){ # $concat{21} .= "excl_" . "$_" . "_payment_terms,';',"; # $concat{23} .= "excl_" . "$_" . "_roes,';',"; # } # for (1 .. 5){ # $concat{6} .= "curr_amnt_" . "$_" . "_costing_other,';',"; # $concat{15} .= "excl_" . "$_" . "_costing_other,';',"; # $concat{30} .= "item_" . "$_" . "_costing_other,';',"; # $concat{40} .= "item_ttl_" . "$_" . "_costing_other,';',"; # $concat{49} .= "qty_" . "$_" . "_costing_other,';',"; # $concat{58} .= "usd_amnt_" . "$_" . "_costing_other,';',"; # $concat{67} .= "vat_" . "$_" . "_costing_other,';',"; # } # for (1 .. 4){ # $concat{5} .= "curr_amnt_" . "$_" . "_costing_match_credits,';',"; # $concat{14} .= "excl_" . "$_" . "_costing_match_credits,';',"; # $concat{19} .= "excl_" . "$_" . "_delivery_times,';',"; # $concat{29} .= "item_" . "$_" . "_costing_match_credits,';',"; # $concat{39} .= "item_ttl_" . "$_" . "_costing_match_credits,';',"; # $concat{48} .= "qty_" . "$_" . "_costing_match_credits,';',"; # $concat{57} .= "usd_amnt_" . "$_" . "_costing_match_credits,';',"; # $concat{66} .= "vat_" . "$_" . "_costing_match_credits,';',"; # } # for (1 .. 3){ # $concat{8} .= "curr_amnt_" . "$_" . "_costing_software_licence,';',"; # $concat{9} .= "curr_amnt_" . "$_" . "_costing_systems,';',"; # $concat{17} .= "excl_" . "$_" . "_costing_software_licence,';',"; # $concat{18} .= "excl_" . "$_" . "_costing_systems,';',"; # $concat{22} .= "excl_" . "$_" . "_purchase_summary,';',"; # $concat{32} .= "item_" . "$_" . "_costing_software_licence,';',"; # $concat{33} .= "item_" . "$_" . "_costing_systems,';',"; # $concat{34} .= "item_" . "$_" . "_purchase_summary,';',"; # $concat{42} .= "item_ttl_" . "$_" . "_costing_software_licence,';',"; # $concat{43} .= "item_ttl_" . "$_" . "_costing_systems,';',"; # $concat{51} .= "qty_" . "$_" . "_costing_software_licence,';',"; # $concat{52} .= "qty_" . "$_" . "_costing_systems,';',"; # $concat{60} .= "usd_amnt_" . "$_" . "_costing_software_licence,';',"; # $concat{61} .= "usd_amnt_" . "$_" . "_costing_systems,';',"; # $concat{69} .= "vat_" . "$_" . "_costing_software_licence,';',"; # $concat{70} .= "vat_" . "$_" . "_costing_systems,';',"; # } # for (1 .. 2){ # $concat{2} .= "curr_amnt_" . "$_" . "_costing_game_breakdown_analytics,';',"; # $concat{7} .= "curr_amnt_" . "$_" . "_costing_rental,';',"; # $concat{11} .= "excl_" . "$_" . "_costing_game_breakdown_analytics,';',"; # $concat{16} .= "excl_" . "$_" . "_costing_rental,';',"; # $concat{26} .= "item_" . "$_" . "_costing_game_breakdown_analytics,';',"; # $concat{31} .= "item_" . "$_" . "_costing_rental,';',"; # $concat{36} .= "item_ttl_" . "$_" . "_costing_game_breakdown_analytics,';',"; # $concat{41} .= "item_ttl_" . "$_" . "_costing_rental,';',"; # $concat{45} .= "qty_" . "$_" . "_costing_game_breakdown_analytics,';',"; # $concat{50} .= "qty_" . "$_" . "_costing_rental,';',"; # $concat{54} .= "usd_amnt_" . "$_" . "_costing_game_breakdown_analytics,';',"; # $concat{59} .= "usd_amnt_" . "$_" . "_costing_rental,';',"; # $concat{63} .= "vat_" . "$_" . "_costing_game_breakdown_analytics,';',"; # $concat{68} .= "vat_" . "$_" . "_costing_rental,';',"; # } # for (1 .. 1){ # $concat{24} .= "excl_" . "$_" . "_terms,';',"; # } # for (1 .. 70){ # chop $concat{$_} ; # chop $concat{$_} ; # chop $concat{$_} ; # chop $concat{$_} ; # chop $concat{$_} ; # $sql_select .= "concat($concat{$_}) AS 'concat_$_'," ; # } # chop $sql_select ; # &db_min_ro('quotes',"id, $sql_select","",'','') ; # foreach my $iid (sort keys %{$db{quotes}}){ # next if $iid == 203 ; # $sql_insert .= "($iid" ; # for (1 .. 70){ # $c = "concat_" . "$_" ; # $sql_insert .= qq(,'$db{quotes}{$iid}{$c}') ; # } # $sql_insert .= ")," ; # } # chop $sql_insert ; # $sql_insert .= ";" ; # &common_debug("TRANSFER DATA: $sql_insert") ; #---------------------------------------------------------------------------- &db_min_ro('quotes_pdf_titles','*',"quote_id='$i{id}'",'','') ; &db_min_ro('quotes_min','*',"id='$i{id}'",'','') ; # unless ($iaction eq 'save' or $iaction eq 'update') { &db_min_ro($table,'*',"id='$i{id}'",'','') ; # } $max_cams = $db{$table}{$i{id}}{max_cams} if $db{$table}{$i{id}}{max_cams} ; $page_title = substr($page_title,0,-1) ; $page_title .= " : $i{id}" ; # our $open_new_tab = qq(target="_blank") ; # opens booking page in a new tab &common_min_footer('id',"$lcpage\s"); &edit_db_fields ; $selected{max_cams}{$db{$table}{$i{id}}{max_cams}} = 'SELECTED'; &select_opts($i{id}) ; our $saveandexit = 1 ; &build_boxes($i{id},'update') ; $db_ignore_open_close=0; &db_close_conn; &common_min_extra_crumb("search-$lcpage\s","Search $ucfirstpage\s") ; } #------------------------------------------------------------------------------------------ sub build_boxes { my ($id,$action) = @_ ; our @jquery_trigger_fields = () ; our @jquery_custom_vat_ids = () ; our @jquery_duty_ids = () ; push @jquery_trigger_fields, 'inputRoe' ; push @jquery_trigger_fields, 'selectPeriod' ; # push @jquery_trigger_fields, 'selectDiscount' ; &tab_curr_symbols; foreach $col(keys %required) { &common_min_forms_required ; } &tabs_get_default_totals($id) ; our $tab_cnt = 0 ; $print_box_content_rows .= &common_min_forms_start($table) ; #-------------------------------------------------------------------------- $print_box_content_rows .= qq(
) ; &build_boxes_top($id); $print_box_content_rows .= qq(
) ; #-------------------------------------------------------------------------- $print_box_content_rows .= qq(
) ; &tab_cnt; $print_box_content_rows .= qq(
); our %tab_sections = () ; foreach (sort {$a <=> $b} keys %cntxt) { my $tab_name = &tab_names($_) ; my $active = '' ; if (($_==1) and ($db{$table}{$id}{activetab} eq '')) { $active = 'active' ; } elsif ($db{$table}{$id}{activetab} eq $tab_name) { $active = 'active' ; } my $colsleft = 12 ; my $showright = '' ; if ($cntxt{$_} eq 'one' or $cntxt{$_} eq 'seven') { $colsleft = 6 ; $showright = 1 ; } # &common_min_debug("activetab [$tab_name] $active "); $print_box_content_rows .= qq~
~; $print_box_content_rows .= qq~
~ ; $print_box_content_rows .= qq~
~ ; &build_boxes_left($id,$_); $print_box_content_rows .= qq~
~ ; if ($showright) { $print_box_content_rows .= qq~
~ ; &build_boxes_right($id,$_); $print_box_content_rows .= qq~
~ ; } $print_box_content_rows .= qq~
~ ; $print_box_content_rows .= qq~
~ ; $print_box_content_rows .= qq~
~ ; if ($id) { $print_box_content_rows .= qq~
~ ; $print_box_content_rows .= qq~~; # # if ($username eq 'rory') { $print_box_content_rows .= qq~ ~; } # if ($username eq 'rory' or $username eq 'handre') { $print_box_content_rows .= qq~ ~; } # # $print_box_content_rows .= qq~ ~; $print_box_content_rows .= &common_min_box_foot; } $print_box_content_rows .= qq~
~ ; $print_box_content_rows .= qq~
~ ; $print_box_content_rows .= qq~
~ ; } $print_box_content_rows .= qq(
) ; $print_box_content_rows .= qq(
) ; #-------------------------------------------------------------------------- $print_box_content_rows .= qq(
) ; &build_boxes_bottom($id); $print_box_content_rows .= qq(
) ; #-------------------------------------------------------------------------- $print_box_content_rows .= &common_min_forms_end($id,$table,$action,$skip) ; my $jquery_trigger_ids = '#' . join(",#",@jquery_trigger_fields) ; my $jq_tab_var = '' ; my $jq_tab = '' ; my $inp_tab_val = '' ; foreach (keys %cntxt) { $jq_tab_var .= qq(var totalTab_$_=0 ; ) ; $jq_tab .= qq(totalTab_$_=0 ; ) ; } # $inp_tab_val .= qq(\$("#inputTotal_tab_$_").val(totalTab_$_.toFixed(2)) ;) ; $trigger_jquery_raw .= qq~ \$("#selectCurrency").change(function() { curr_changed=1; // console.log("1. curr_changed: "+curr_changed) ; calcCurr(); }); \$('#quotes-form:checkbox').change(function() { calcAll(); }); function calcCosting(excl,item,qty,usd,conv,vat,ttl) { var itval = item.val(); var uval = usd.val(); var cval = conv.val(); // currency amount var qval = qty.val(); var vval = vat.val(); // var tval = ttl.val(); var xval = excl.val(); var dis = \$("#selectDiscount").val(); var roe = \$("#inputRoe").val(); // getNum() if (!itval) { calcAll(); return ; } var arrP = itval.split(":"); // 3:0.00:100.00:200.00:223.00:223.00:250.00:250.00:200.00 // id:freight:nett:main_price:gross_10_1_50:gross_10_51_plus:gross_20_1_50:gross_20_51_plus:gross_50 // // // var list = { freight:arrP[0], nett:arrP[1], main_price:arrP[2], gross_10_1_50:arrP[3], gross_10_51_plus:arrP[4], gross_20_1_50:arrP[5], gross_20_51_plus:arrP[6], gross_50:arrP[7] }; // var list = { freight:arrP[1], nett:arrP[2], main_price:arrP[3], gross_10_1_50:arrP[4], gross_10_51_plus:arrP[5], gross_20_1_50:arrP[6], gross_20_51_plus:arrP[7], gross_50:arrP[8] }; var list = { freight:arrP[1], nett:arrP[2], main_price:arrP[3], source_admin:arrP[4], perc3:arrP[5], perc5:arrP[6], perc6:arrP[6], perc8:arrP[7], perc10:arrP[8], perc15:arrP[9], perc20:arrP[10] }; if (!qval) { qval=1; qty.val(qval); } // set quantity if blank if (!uval) // set USD amount if no amount { var uval = list.main_price; if (dis==3 && qval>0) { uval=list.perc3; } else if (dis==5 && qval>0) { uval=list.perc5; } else if (dis==6 && qval>0) { uval=list.perc6; } else if (dis==8 && qval>0) { uval=list.perc8; } else if (dis==10 && qval>0) { uval=list.perc10; } else if (dis==15 && qval>0) { uval=list.perc15; } else if (dis==20 && qval>0) { uval=list.perc20; } } // console.log('item: '+item+', uval: '+uval) ; // if (!uval) // set USD amount if no amount // { // var uval = list.main_price; // // alert('main_price:'+uval) ; // if (dis==10 && qval>0 && qval<=50) // { // uval=list.gross_10_1_50; // } // else if (dis==10 && qval>50) // { // uval=list.gross_10_51_plus; // } // else if (dis==20 && qval>0 && qval<=50) // { // uval=list.gross_20_1_50; // } // else if (dis==20 && qval>50) // { // uval=list.gross_20_51_plus; // } // else if (dis==50) // { // uval=list.gross_50; // } // } var usdAmnt = uval * 1 ; // var currAmnt = usdAmnt / roe ; var currAmnt = usdAmnt * roe ; var ttlAmnt = currAmnt * qval ; usd.val(usdAmnt.toFixed(2)) ; // set USD currency amount conv.val(currAmnt.toFixed(2)) ; // set foreign currency amount ttl.val(ttlAmnt.toFixed(2)) ; // set total amount calcAll(); // recalculate the entire sheet // alert(usdAmnt+'(inputUsd_amnt_$field):'+usd+':'+qty+'(inputQty_$field)') ; // 0.00:100.00:200.00:223.00:223.00:250.00:250.00:200.00 // } function dispCamDetails(excl,camnr,qty,itcode,desc,snr,ddate,adate,f) { var camval = camnr.val(); var arrP = camval.split(":"); var qval = qty.val(); if (!camval) { excl.prop("checked", true); snr.val(""); adate.val(""); ddate.val(""); desc.val(""); //itcode.val(""); qty.val(""); return ; } var prev = getNum(f-1); var pcamval = \$("#selectCamera_nr_"+prev+"_camera_details").val(); // console.log("#selectCamera_nr_"+prev+"_camera_details"+", pcamval="+pcamval) ; if (typeof pcamval!="undefined") { console.log(pcamval) ; var arrpP = pcamval.split(":"); var checkS1 = arrpP[1].substring(0,2); var checkOther = arrpP[1].substring(0,5); if (checkS1=='S1' || checkOther=='Prime' || checkOther=='Coach') { \$("#c_excl_"+f+"_camera_details").append("");\$('[data-toggle="tooltip"]').tooltip({html:true}) ; } } var list = { id:arrP[0], camera_nr:arrP[1], camera_system_id:arrP[2], serial_nr:arrP[3], date_delivered:arrP[4], description:arrP[5], itemcode:arrP[6], date_active:arrP[7] }; if (!qval || qval<1) { qval=1; qty.val(qval); } // set quantity if blank snr.val(list.serial_nr); //ddate.val(list.date_delivered); //adate.val(list.date_active); desc.val(list.description); //itcode.val(list.itemcode); excl.prop("checked", false); } function calcCurr() { var roe = \$("#inputRoe") ; var curr = \$("#selectCurrency").val() ; var url = "$useropts{scripts}/get/get_roe.pl?" + curr ; \$.get(url, function(json) { \$.each(json, function(key, data) { roe.val(data.sell_rate); }); calcAll(); }, 'json'); } ~; $trigger_jquery_raw .= qq( var currArr = {}; // var vat_total = {} ; var total_total = {} ; var subtotal = {} ; var fs_total = {} ; $js_curr_arr var currVal = \$("#selectCurrency").val() ; var prevRoe = '' ; var curRoe = '' ; var grandTtl = 0 ; var grandSub = 0 ; var grandVat = 0 ; $jq_tab_var \$("$jquery_trigger_ids").change(function() { calcAll(); }); calcAll() ; \$("#inputYear_1_3_purchase_summary").on("change", function() { // calcAll() ; if (\$(this).val()) { \$(this).val(parseFloat(\$(this).val()).toFixed(2)); } else { \$(this).val("0.00"); } calcAll() ; }); function calcAll() { if (!\$("#selectCurrency").val()) { \$("#selectCurrency").val('ZAR'); \$("#selectCurrency").trigger("chosen:updated"); } // if (!\$("#inputRoe").val()) { \$("#inputRoe").val(1) } grandTtl = 0 ; grandSub = 0 ; grandVat = 0 ; totalOne_time_fee_1 = 0 ; subtotalOne_time_fee_1 = 0 ; vatOne_time_fee_1 = 0 ; totalMonthly_recurring_fee_1 = 0 ; subtotalMonthly_recurring_fee_1 = 0 ; vatMonthly_recurring_fee_1 = 0 ; totalAnnual_fee_1 = 0 ; subtotalAnnual_fee_1 = 0 ; vatAnnual_fee_1 = 0 ; totalOne_time_fee_2 = 0 ; subtotalOne_time_fee_2 = 0 ; vatOne_time_fee_2 = 0 ; totalMonthly_recurring_fee_2 = 0 ; subtotalMonthly_recurring_fee_2 = 0 ; vatMonthly_recurring_fee_2 = 0 ; totalAnnual_fee_2 = 0 ; subtotalAnnual_fee_2 = 0 ; vatAnnual_fee_2 = 0 ; totalYear_1_2_purchase_summary=0; totalYear_2_2_purchase_summary=0; totalYear_3_2_purchase_summary=0; totalYear_4_2_purchase_summary=0; totalYear_5_2_purchase_summary=0; totalTotal_2_purchase_summary=0; totalYear_1_3_purchase_summary=0; totalYear_2_3_purchase_summary=0; totalYear_3_3_purchase_summary=0; totalYear_4_3_purchase_summary=0; totalYear_5_3_purchase_summary=0; totalTotal_3_purchase_summary=0; $jq_tab $re_calc_jquery \$("#inputSub_total").val(grandSub.toFixed(2)) ; \$("#inputVat_total").val(grandVat.toFixed(2)) ; \$("#inputGrand_total").val(grandTtl.toFixed(2)) ; \$("#inputTotal_one_time_fee_1").val(totalOne_time_fee_1.toFixed(2)) ; \$("#inputSubtotal_one_time_fee_1").val(subtotalOne_time_fee_1.toFixed(2)) ; \$("#inputVat_one_time_fee_1").val(vatOne_time_fee_1.toFixed(2)) ; \$("#inputTotal_one_time_fee_2").val(totalOne_time_fee_2.toFixed(2)) ; \$("#inputSubtotal_one_time_fee_2").val(subtotalOne_time_fee_2.toFixed(2)) ; \$("#inputVat_one_time_fee_2").val(vatOne_time_fee_2.toFixed(2)) ; \$("#inputTotal_monthly_recurring_fee_1").val(totalMonthly_recurring_fee_1.toFixed(2)) ; \$("#inputSubtotal_monthly_recurring_fee_1").val(subtotalMonthly_recurring_fee_1.toFixed(2)) ; \$("#inputVat_monthly_recurring_fee_1").val(vatMonthly_recurring_fee_1.toFixed(2)) ; \$("#inputTotal_annual_fee_1").val(totalAnnual_fee_1.toFixed(2)) ; \$("#inputSubtotal_annual_fee_1").val(subtotalAnnual_fee_1.toFixed(2)) ; \$("#inputVat_annual_fee_1").val(vatAnnual_fee_1.toFixed(2)) ; \$("#inputYear_1_2_purchase_summary").val(totalYear_1_2_purchase_summary.toFixed(2)) ; \$("#inputYear_2_2_purchase_summary").val(totalYear_2_2_purchase_summary.toFixed(2)) ; \$("#inputYear_3_2_purchase_summary").val(totalYear_3_2_purchase_summary.toFixed(2)) ; \$("#inputTotal_2_purchase_summary").val(totalTotal_2_purchase_summary.toFixed(2)) ; let totalAnnual_fee = totalAnnual_fee_1 + totalAnnual_fee_2 ; totalYear_1_3_purchase_summary = totalAnnual_fee ; totalYear_2_3_purchase_summary = totalAnnual_fee ; totalYear_3_3_purchase_summary = totalAnnual_fee ; \$("#inputYear_2_3_purchase_summary").val(totalYear_2_3_purchase_summary.toFixed(2)) ; \$("#inputYear_3_3_purchase_summary").val(totalYear_3_3_purchase_summary.toFixed(2)) ; let totalOne_time_fee = totalOne_time_fee_1 + totalOne_time_fee_2 ; \$("#inputYear_1_1_purchase_summary").val(totalOne_time_fee.toFixed(2)) ; \$("#inputYear_2_1_purchase_summary").val('0.00') ; \$("#inputYear_3_1_purchase_summary").val('0.00') ; \$("#inputYear_4_1_purchase_summary").val('0.00') ; \$("#inputYear_5_1_purchase_summary").val('0.00') ; \$("#inputTotal_1_purchase_summary").val(totalOne_time_fee.toFixed(2)) ; let year_1_tot = totalOne_time_fee + totalYear_1_2_purchase_summary + parseFloat(\$("#inputYear_1_3_purchase_summary").val()) ; let year_2_tot = totalYear_2_2_purchase_summary + totalAnnual_fee ; let year_3_tot = totalYear_3_2_purchase_summary + totalAnnual_fee ; let year_4_tot = totalYear_4_2_purchase_summary + totalAnnual_fee ; let year_5_tot = totalYear_5_2_purchase_summary + totalAnnual_fee ; let grand_tot_pur = 0 ; \$("#inputYearly_totals_1").val(parseFloat(year_1_tot).toFixed(2)) ; \$("#inputYearly_totals_2").val(year_2_tot.toFixed(2)) ; \$("#inputYearly_totals_3").val(year_3_tot.toFixed(2)) ; if (\$("#selectPeriod").val() == 60) { \$("#inputYear_4_2_purchase_summary").val(totalYear_4_2_purchase_summary.toFixed(2)) ; \$("#inputYear_5_2_purchase_summary").val(totalYear_5_2_purchase_summary.toFixed(2)) ; totalYear_4_3_purchase_summary = totalAnnual_fee ; totalYear_5_3_purchase_summary = totalAnnual_fee ; totalTotal_3_purchase_summary = totalAnnual_fee * 4 + parseFloat(\$("#inputYear_1_3_purchase_summary").val()) ; \$("#inputYear_4_3_purchase_summary").val(totalYear_4_3_purchase_summary.toFixed(2)) ; \$("#inputYear_5_3_purchase_summary").val(totalYear_5_3_purchase_summary.toFixed(2)) ; \$("#inputYearly_totals_4").val(year_4_tot.toFixed(2)) ; \$("#inputYearly_totals_5").val(year_5_tot.toFixed(2)) ; grand_tot_pur = year_1_tot + year_2_tot + year_3_tot + year_4_tot + year_5_tot ; } else if (\$("#selectPeriod").val() == 36) { \$("#inputYear_4_2_purchase_summary").val('0.00') ; \$("#inputYear_5_2_purchase_summary").val('0.00') ; totalYear_4_3_purchase_summary = 0 ; totalYear_5_3_purchase_summary = 0 ; totalTotal_3_purchase_summary = totalAnnual_fee * 2 + parseFloat(\$("#inputYear_1_3_purchase_summary").val()) ; \$("#inputYear_4_3_purchase_summary").val('0.00') ; \$("#inputYear_5_3_purchase_summary").val('0.00') ; \$("#inputYearly_totals_4").val('0.00') ; \$("#inputYearly_totals_5").val('0.00') ; grand_tot_pur = year_1_tot + year_2_tot + year_3_tot ; } console.log("inputYear_4_2_purchase_summary : "+\$('#inputYear_4_2_purchase_summary').val()) ; \$("inputTotal_3_purchase_summary").val() ; \$("#inputTotal_3_purchase_summary").val(parseFloat(totalTotal_3_purchase_summary).toFixed(2)) ; \$("#inputYearly_total").val(parseFloat(grand_tot_pur).toFixed(2)) ; curr_changed=0; }); # $onload .= qq(onload="calcAll();"); if ($iaction eq 'add'){ $onload .= qq~onload="calcCurr();"~ ; } # $onload .= qq~onload="calcCurr();"~ ; } #---------------------------------------------------------------------------------------- sub build_boxes_top { my ($id) = @_ ; our ($lcol,$fcol) = &common_min_columns(3,6) ; $add_form_fields = '' ; $fcol = 8 ; $add_form_fields .= qq~
~ ; # $preferred_title{quote_nr} = "Ref" ; $preferred_title{quote_nr} = "Nr" ; $add_form_fields .= &common_min_form_input('quote_nr',$db{$table}{$id}{quote_nr},'') ; $add_form_fields .= &common_min_form_input('ref',$db{$table}{$id}{ref},'') ; # $add_form_fields .= &common_min_form_input('po_nr',$db{$table}{$id}{po_nr},'') ; $add_form_fields .= qq~
~ ; $add_form_fields .= qq~
~ ; if (($db{$table}{$id}{quote_date} eq '') or ($db{$table}{$id}{quote_date} eq '0000-00-00')) { $db{$table}{$id}{quote_date} = $now_ccyy_mm_dd ; } $add_form_fields .= &common_min_form_datepicker('quote_date',$db{$table}{$id}{quote_date}) ; my $plus_n_days_ccyy_mm_dd = &common_add_delta_days(14) ; if (($db{$table}{$id}{quote_expiry} eq '') or ($db{$table}{$id}{quote_expiry} eq '0000-00-00')) { $db{$table}{$id}{quote_expiry} = $plus_n_days_ccyy_mm_dd ; } $add_form_fields .= &common_min_form_datepicker('quote_expiry',$db{$table}{$id}{quote_expiry}) ; $add_form_fields .= qq~
~ ; $add_form_fields .= qq~
~ ; $dlg{quote_to} = qq~
~ ; $fcol=8; $add_form_fields .= &common_min_form_select('quote_to',$db{$table}{$id}{quote_to},'') ; $required{max_cams} = 1 ; $select{max_cams} = 1 ; $opts{max_cams} = qq~ ~ ; $fcol=6; # $add_form_fields .= &common_min_form_select('max_cams',$db{$table}{$id}{max_cams},'') ; $add_form_fields .= qq~
~ ; #
$fcol=2; $add_form_fields .= &common_min_form_label_col('max_cams') ; $fcol=2; $add_form_fields .= &common_min_form_select_col('max_cams',$db{$table}{$id}{max_cams},'') ; $fcol=2;$lcol=1; $add_form_fields .= &common_min_form_label_col('qty') ; $fcol=2; $add_form_fields .= &common_min_form_input_col('qty',$db{$table}{$id}{qty}) ; # $add_form_fields .= &common_min_form_label_col('period') ; $add_form_fields .= &common_min_form_label_col('prd') ; $sel = '' ; $sel = 'SELECTED' if $db{$table}{$id}{period} eq '36' ; $opts{period} .= qq~~ ; $sel = '' ; $sel = 'SELECTED' if $db{$table}{$id}{period} eq '60' ; $opts{period} .= qq~~ ; $sel = '' ; $required{period} = 1 ; $add_form_fields .= &common_min_form_select_col('period',$db{$table}{$id}{period}) ; $add_form_fields .= qq~
~ ; $fcol=7;# $add_form_fields .= &common_min_form_select('ship_to',$db{$table}{$id}{ship_to},'') ; # if ($username eq 'rory') { $add_form_fields .= qq~
 
~ ; } $add_form_fields .= qq~
~ ; $add_form_fields .= qq~
~ ; $lcol=3; $preferred_title{'country_shipped'} = 'Country' ; $add_form_fields .= &common_min_form_select('country_shipped',$db{$table}{$id}{country_shipped},'') ; $preferred_title{'camera_system_id'} = 'System' ; $add_form_fields .= &common_min_form_select('camera_system_id',$db{$table}{$id}{camera_system_id},'') ; $add_form_fields .= qq~
~ ; $print_box_content_rows .= &common_min_box_top('qrcode',"Quote",'BABEC2'); $print_box_content_rows .= qq~
$add_form_fields
~; $print_box_content_rows .= qq~
 
~ ; $print_box_content_rows .= &common_min_box_foot; } #---------------------------------------------------------------------------------------- sub build_boxes_left { my ($id,$tab) = @_ ; our ($lcol,$fcol) = &common_min_columns(3,6) ; &tab_build_qt_left($id,$tab); } #---------------------------------------------------------------------------------------- sub build_boxes_right { my ($id,$tab) = @_ ; our ($lcol,$fcol) = &common_min_columns(3,6) ; &tab_build_qt_right($id,$tab); } #---------------------------------------------------------------------------------------- sub build_boxes_bottom { my ($id) = @_ ; our ($lcol,$fcol) = &common_min_columns(2,2) ; $add_form_fields = '' ; # --------------- START GRAND TOTAL ---------------------------------------------------------------------------------------------------------------------- $print_box_content_rows .= &common_min_form_input('activetab',$db{$table}{$id}{activetab},'hidden') ; # # $print_box_content_rows .= &common_min_form_input('grand_total','0.00','hidden') ; # my @ttlarr = ("sub_total","vat_total","grand_total") ; # $lcol=1;$fcol=2; # foreach (@ttlarr) { # $readonly{$_} = "READONLY"; # $add_form_fields .= qq~
~ ; #
# $add_form_fields .= &common_min_form_label_col($_) ; # $add_form_fields .= &common_min_form_input_col($_,'0.00') ; # $add_form_fields .= qq~
~ ; # } # $print_box_content_rows .= qq~
~ ; # $print_box_content_rows .= $add_form_fields ; # $print_box_content_rows .= &common_min_box_foot; # --------------- END RAND TOTAL ------------------------------------------------------------------------------------------------------------------------ } #---------------------------------------------------------------------------------------- sub build_cost_form { my ($id,$tab,$rcnt,$suffix) = @_ ; $tab_sections{$tab} .= "$suffix+$rcnt|" ; $jq_cal = qq~var vat_total$suffix = 0 ; var total_total$suffix = 0 ; var subtotal$suffix = 0 ; var vat_amount = 0 ; ~ ; $jq_fs = '' ; %cnt_specific_jquery = () ; &set_check_boxes_checked($id,$rcnt,"excl",$suffix); &set_check_boxes_checked($id,$rcnt,"vat",$suffix); my $cntloop=1; # my %saved_field = () ; my @prefexes = ("excl", "item", "qty", "usd_amnt", "curr_amnt", "item_ttl", "vat") ; # my @b = ( # "_costing_additional_services", # "_costing_game_breakdown_analytics", # "_costing_hardware_add_ons", # "_costing_installation_kits", # "_costing_match_credits", # "_costing_other", # "_costing_rental", # "_costing_software_licence", # "_costing_systems", # "_delivery_times", # "_notes", # "_payment_terms", # "_purchase_summary", # "_roes", # "_terms") ; foreach my $prefex (@prefexes){ $col_name = "$prefex" . "$suffix" ; $cnt = 0 ; if($db{quotes_min}{$id}{$col_name}){ foreach my $col_input (split(";", $db{quotes_min}{$id}{$col_name})){ $cnt++ ; $saved_field{$prefex}{$suffix}{$cnt} = $col_input ; # &common_debug("$aa/$suffix : $cc") ; } } } for (1 .. $rcnt) { $display_style = '' ; $display_plus_btn = '' ; # unless ($_==1 or $db{$table}{$id}{"item_$_$suffix"}) { unless ($_==1 or $saved_field{"item"}{$suffix}{$_}) { $display_style = qq~style='display:none;'~ ; $trigger_jquery_raw .= qq~ \$("#btn_$_$suffix").click(function () { \$("#$_$suffix").toggle(); \$("#btn_$_$suffix").hide(); var v = $_ + 1 ; \$("#btn_"+v+"$suffix").show(); });~ ; if ($_>$cntloop) { $display_butt = qq~display:none;~ ; } else { $display_butt = '' ; } $display_plus_btn = qq~~; } else { $cntloop++ ; } $add_form_fields .= qq~
~ ; #---------------------------------------------------------------------------------------------------- $field = "excl_$_$suffix" ; $fcol=1 ; # if ($checkit{$field} == 1) { if ($saved_field{"excl"}{$suffix}{$_}){ $checked = 'CHECKED'; } elsif (($excl_tick{$_}) and ($iaction eq 'add')) { $checked = 'CHECKED'; } else { $checked = ''; } $label{$field} = 1 ; $add_form_fields .= &common_min_form_checkbox_col($field,$checkit{$field},$checked) ; #---------------------------------------------------------------------------------------------------- my $field = "item_$_$suffix" ; $fcol=2 ; $allow_deselect{$field} = 1 ; $preferred_placeholder{$field} = "Item $_" ; if ($suffix eq '_costing_other' or $suffix eq '_costing_rental' or ($suffix eq '_costing_systems' and $_>1) or ($suffix eq '_costing_software_licence' and $_>2) or ($suffix eq '_costing_game_breakdown_analytics' and $_>1)) { # $add_form_fields .= &common_min_form_input_col($field,$db{$table}{$id}{$field}) ; $add_form_fields .= &common_min_form_input_col($field,$saved_field{"item"}{$suffix}{$_}) ; } else { # &common_min_debug("[$field] $db{$table}{$id}{$field}") ; # $add_form_fields .= &common_min_form_select_col($field,$db{$table}{$id}{$field}) ; $add_form_fields .= &common_min_form_select_col($field,$saved_field{"item"}{$suffix}{$_}) ; } #---------------------------------------------------------------------------------------------------- $field = "qty_$_$suffix" ; $fcol=1 ; $input_style{$field} = qq(style="text-align:right;"); $preferred_placeholder{$field} = "Qty $_" ; # my $val = $db{$table}{$id}{$field} ; my $val = $saved_field{"qty"}{$suffix}{$_} ; unless ($val) { $val = $def_qty{$_} ; } $val = &q_get_val($val) ; $add_form_fields .= &common_min_form_input_col($field,$val) ; #---------------------------------------------------------------------------------------------------- $field = "usd_amnt_$_$suffix" ; $input_style{$field} = qq(style="text-align:right;"); # my $val = $db{$table}{$id}{$field} ; my $val = $saved_field{"usd_amnt"}{$suffix}{$_} ; unless ($val) { $val = $def_usd{$_} ;} $val = &q_get_val($val) ; $preferred_placeholder{$field} = "USD Amnt $_" ; $add_form_fields .= &common_min_form_input_col($field,$val,1) ; #---------------------------------------------------------------------------------------------------- $field = "curr_amnt_$_$suffix" ; $input_style{$field} = qq(style="text-align:right;"); $readonly{$field} = 'READONLY' ; # $fcol=2 ; $preferred_placeholder{$field} = "ZAR Amnt $_" ; # my $val = &q_get_val($db{$table}{$id}{$field}) ; my $val = &q_get_val($saved_field{"curr_amnt"}{$suffix}{$_}) ; $add_form_fields .= &common_min_form_input_col($field,$val,1) ; #---------------------------------------------------------------------------------------------------- $field = "item_ttl_$_$suffix" ; $input_style{$field} = qq(style="text-align:right;"); $readonly{$field} = 'READONLY' ; # $fcol=2 ; $preferred_placeholder{$field} = "Item Total $_" ; # my $val = &q_get_val($db{$table}{$id}{$field}) ; my $val = &q_get_val($saved_field{"item_ttl"}{$suffix}{$_}) ; $add_form_fields .= &common_min_form_input_col($field,$val,1) ; #---------------------------------------------------------------------------------------------------- $field = "vat_$_$suffix" ; $fcol=1 ; # if ($checkit{$field} == 1) { $checked = 'CHECKED'; } elsif (($vat_tick{$_}) and ($iaction eq 'add')) { $checked = 'CHECKED'; } else { $checked = ''; } if ($saved_field{"vat"}{$suffix}{$_} == 1) { # if ($checkit{$field} == 1) { $checked = 'CHECKED'; } elsif ($iaction eq 'add') { $checked = 'CHECKED'; } else { $checked = ''; } $label{$field} = 1 ; if ($no_vat{$suffix}) { $style_field{$field} = 'style="display:none;"' ; } $add_form_fields .= &common_min_form_checkbox_col($field,$checkit{$field},$checked) ; #---------------------------------------------------------------------------------------------------- # $add_form_fields .= qq~$display_plus_btn~ ; $add_form_fields .= qq~
~ ; $add_form_fields .= qq~
$display_plus_btn
~ ; $add_form_fields .= qq~
~ ; my $added_ttl_js = '' ; if ($suffix eq '_costing_systems' or $suffix eq '_costing_installation_kits' or $suffix eq '_costing_hardware_add_ons' or $suffix eq '_costing_additional_services' or $suffix eq '_costing_match_credits') { # if ($suffix eq '_costing_additional_services' and $db{$table}{$id}{"item_$_$suffix"}) { # OTT Monthly Management fee # $added_ttl_js = qq~ # totalMonthly_recurring_fee += (amnts[2] + amnts[1]) ; # subtotalMonthly_recurring_fee += amnts[2] ; # vatMonthly_recurring_fee += amnts[1] ; # var yrTtl = (amnts[2] + amnts[1]) * 12 # totalYear_1_2_purchase_summary += yrTtl; # totalYear_2_2_purchase_summary += yrTtl; # totalYear_3_2_purchase_summary += yrTtl; # totalTotal_2_purchase_summary += (yrTtl*3); ~; # } else { $added_ttl_js = qq~totalOne_time_fee_1 += (amnts[2] + amnts[1]) ; subtotalOne_time_fee_1 += amnts[2] ; vatOne_time_fee_1 += amnts[1] ; ~; # } } if ($suffix eq '_costing_other') { $added_ttl_js = qq~ totalOne_time_fee_2 += (amnts[2] + amnts[1]) ; subtotalOne_time_fee_2 += amnts[2] ; vatOne_time_fee_2 += amnts[1] ; ~ ; } if ($suffix eq '_costing_software_licence' or $suffix eq '_costing_rental') { $added_ttl_js = qq~ totalMonthly_recurring_fee_1 += (amnts[2] + amnts[1]) ; subtotalMonthly_recurring_fee_1 += amnts[2] ; vatMonthly_recurring_fee_1 += amnts[1] ; var yrTtl = (amnts[2] + amnts[1]) * 12 totalYear_1_2_purchase_summary += yrTtl; totalYear_2_2_purchase_summary += yrTtl; totalYear_3_2_purchase_summary += yrTtl; if (\$("#selectPeriod").val() == 60) { totalYear_4_2_purchase_summary += yrTtl; totalYear_5_2_purchase_summary += yrTtl; totalTotal_2_purchase_summary += (yrTtl*2); } totalTotal_2_purchase_summary += (yrTtl*3); \$("#selectPeriod").on("change", function() { if (\$(this).val() == 36) { totalYear_4_2_purchase_summary = 0; totalYear_5_2_purchase_summary = 0; totalTotal_2_purchase_summary -= (yrTtl*5); totalTotal_2_purchase_summary += (yrTtl*3); } else if (\$(this).val() == 60) { totalYear_4_2_purchase_summary += yrTtl; totalYear_5_2_purchase_summary += yrTtl; totalTotal_2_purchase_summary -= (yrTtl*3); totalTotal_2_purchase_summary += (yrTtl*5); } }); ~; } if ($suffix eq '_costing_game_breakdown_analytics') { $added_ttl_js = qq~ totalAnnual_fee_1 += (amnts[2] + amnts[1]) ; subtotalAnnual_fee_1 += amnts[2] ; vatAnnual_fee_1 += amnts[1] ; ~; } $jq_cal .= qq~ var amnts = calcSection('$suffix','$_'); vat_total$suffix += amnts[1] ; subtotal$suffix += amnts[2] ; total_total$suffix += (amnts[2] + amnts[1]) ; $added_ttl_js ~; } $jq_cal .= qq~ grandSub+=subtotal$suffix; grandVat+=vat_total$suffix; grandTtl+=total_total$suffix; ~ ; $re_calc_jquery .= qq~calc$suffix();setRoe('$suffix','$rcnt');~; $trigger_jquery_raw .= qq~ function calc$suffix() { $jq_cal } ~; } #---------------------------------------------------------------------------------------- sub q_get_val { my ($val) = @_ ; my $disp_val = $val ; if ($val eq '0.00') { $disp_val = '' ; } return ($disp_val); } #------------------------------------------------------------------------------------------ sub set_check_boxes_checked { my ($id,$rcnt,$prefix,$suffix) = @_ ; # my $dbfield = "$prefix$suffix" ; # my $val = $db{$table}{$id}{$dbfield} ; # if ($suffix eq '_camera_details') { &common_min_debug("[$dbfield] $val [$id,$rcnt,$prefix,$suffix]") ; } for (1 .. $rcnt) { # my $sstr = $_-1 ; my $ifield = $prefix . '_' . "$_$suffix" ; # $checkit{$ifield} = substr($val,$sstr,1) ; my $val = $db{$table}{$id}{$ifield} ; $checkit{$ifield} = $val ; # if ($suffix eq '_camera_details') { &common_min_debug(" --- [$ifield] $checkit{$ifield} [$val]") ; } } } #---------------------------------------------------------------------------------------- sub select_opts { my ($id) = @_ ; $required{type} = 1 ; $select{type} = 1 ; $selected{type}{$db{$table}{$id}{type}} = 'SELECTED' ; unless ($db{$table}{$id}{type}) { $selected{type}{purchase} = 'SELECTED' ; } $opts{type} = qq~~ ; $trigger_jquery .= qq~\$("#selectType\_chosen").css( "width", "60%" );~ ; my @currency_arr = qw( currency ); foreach my $curr (@currency_arr) { if ($db{$table}{$id}{$curr}) { $selected{$curr}{$db{$table}{$id}{$curr}} = 'SELECTED'; } else { $selected{$curr}{ZAR} = 'SELECTED'; } $select{$curr} = 1 ; $opts{$curr} = qq~ ~ ; } # $select{discount} = 1 ; # $selected{discount}{$db{$table}{$id}{discount}} = 'SELECTED' ; # $opts{discount} = qq~~ ; # $opts{discount} = qq~~ ; # $trigger_jquery .= qq~\$("#selectItem_$field\_chosen").css( "width", "96%" ) # ~ ; my $selcntry = $db{$table}{$id}{country_shipped} ; $selcntry = 242 unless $selcntry ; # Default South Africa &common_min_select_opts('country_shipped','countries','name',$selcntry,1,'country',''); &common_min_select_opts('camera_system_id','camera_systems','name',$db{$table}{$id}{camera_system_id},1,'',''); # &common_min_select_opts('quote_to','customers','name',$db{$table}{$id}{quote_to},1,'',''); &tab_customer_select_opts('customers','name','','quote_to'); # $select{ship_to} = 1 ; $required{ship_to} = 1 ; # &db_min_ro('towns','*','','','') ; # foreach (keys %{$db{'towns'}}) { # my $selected = ''; if ($db{towns}{$_}{name} eq $db{$table}{$id}{ship_to}) { $selected = 'selected="selected"'; } # $opts{ship_to} .= qq() ; # } $trigger_jquery_raw .= qq~ function calcSection(sufx,cnt) { var inpItemTtl = \$("#inputItem_ttl_"+cnt+sufx) ; var inpCurrAmnt = \$("#inputCurr_amnt_"+cnt+sufx) ; var inpItem = \$("#inputItem_"+cnt+sufx) ; var initUsdVal = \$("#inputUsd_amnt_"+cnt+sufx).val() ; var inpUsd = \$("#inputUsd_amnt_"+cnt+sufx) ; var qty = \$("#inputQty_"+cnt+sufx).val() ; var vatChkBox = \$("#checkboxVat_"+cnt+sufx) ; var exclChkBox = \$("#checkboxExcl_"+cnt+sufx) ; var selCurr = \$("#selectCurrency") ; var exclChk = 'excl_'+cnt+sufx ; var vatChk = 'vat_'+cnt+sufx ; var usdAmnt = 0 ; var vatAmnt = 0 ; if (selCurr.val() != 'ZAR') { vatChkBox.prop("checked", false) ; } // console.log("3. curr_changed: "+curr_changed +" : "+ selCurr.val()) ; if (curr_changed==1 && selCurr.val()=='ZAR') { vatChkBox.prop("checked", true); } //if (selCurr.val() != 'ZAR') { vatChkBox.prop("checked", false) ; } else { vatChkBox.prop("checked", true); } // set VAT checkbox // if (!inpItem.val()) { exclChkBox.prop("checked", false); } else { exclChkBox.prop("checked", true); } // set EXCL checkbox curRoe = \$("#inputRoe").val(); if ( (qty>0) && (inpUsd.val()>0) ) { var convPrice = convertCurr(initUsdVal,curRoe) ; var ttlPrice = convPrice * qty ; inpCurrAmnt.val(convPrice.toFixed(2)); inpItemTtl.val(ttlPrice.toFixed(2)); exclChkBox.prop("checked", false); } else { exclChkBox.prop("checked", true); } if (\$('input[name='+exclChk+']:checked').length > 0) { // only calculate if included // inpCurrAmnt.prop('readonly', true); // inpCurrAmnt.val(''); // inpUsd.prop('readonly', true); //inpUsd.val(''); // inpItemTtl.val(''); inpItemTtl.prop('readonly', true); // \$("#inputQty_"+cnt+sufx).prop('readonly', true); var ttlPrice = 0 ; inpItemTtl.val(ttlPrice.toFixed(2)); return [0,0,0]; } vatAmnt = 0 ; if ((\$('input[name='+vatChk+']:checked').length > 0) && (selCurr.val() == 'ZAR')) { // var vat = addVat(inpCurrAmnt.val()) ; var vat = addVat(inpItemTtl.val()) ; vatAmnt = vat[0]; } // usdAmnt = getNum(parseFloat(inpUsd.val())); currAmnt = getNum(parseFloat(inpCurrAmnt.val())); ttlAmnt = getNum(parseFloat(inpItemTtl.val())); // return [usdAmnt,vatAmnt]; return [currAmnt,vatAmnt,ttlAmnt]; } function getNum(val) { if (isNaN(val)) { return 0; } return val; } function convertCurr(val,curRoe) { // val = val / curRoe ; val = val * curRoe ; return val; } function addVat(amnt) { var vat_amnt = amnt*0.15 ; var subtotal = amnt - vat_amnt return [vat_amnt,subtotal]; } function setRoe(sx,rcnt) { var labelCurrAmntSx = \$("#lab_curr_amnt$sec"+sx) ; var curr = \$("#selectCurrency").val() ; if (!curr) { curr = 'Curr' ; } labelCurrAmntSx.html('Price ['+curr+']'); for (i = 0; i <= rcnt; i++) { var inpCurrAmntSx = \$("#inputCurr_amnt_"+i+sx) ; ; inpCurrAmntSx.attr("placeholder",curr+' Amnt '+i); var inpQcPh = \$("#inputqty_"+i+sx).attr("placeholder"); } } function calcTtl(qc,ica,iz) { var zar_amnt = getNum(parseFloat(\$("#inputWeight").val())) * getNum(parseFloat(qc.val())) ; iz.val(zar_amnt.toFixed(2)) ; } \$("li").click(function(){ var activeTab = \$(this).text(); \$("#inputActivetab").val(activeTab) ; }); // function selectTabs() // { // activeTab = \$("#inputActivetab").val(); // if (!activeTab) { activeTab = 'Quote Details' ; } // var tabs = \$("#myTab li"); // tabs.each(function(idx, li) { // var tab = \$(li); // if (tab.text == activeTab) // { // tab.addClass("active"); // } else { // tab.removeClass("active"); // } // }); // } ~; # $trigger_jquery .= qq~ # \$(window).bind('beforeunload', function() # { # // window.location = 'https://$ENV{SERVER_NAME}/list-quotes' ; # alert('beforeunload'); # }); # ~; $extra_css .= qq() ; } #------------------------------------------------------------------------------- sub page_opts { our $glyphicon = 'global' ; our $lcpage = 'quote' ; $ucpage = uc $lcpage ; $ucfirstpage = ucfirst $lcpage ; our $table = 'quotes' ; &common_min_add_box_icon ; } #------------------------------------------------------------------------------- sub creat_db_cols { &db_min_ro('quotes','*','','','') ; my %col_exists ; foreach (sort { $col_name{$a} cmp $col_name{$b} } keys %col_name) { $col = $col_name{$_} ; $col_exists{$col} = 1 ; } my $mysql = "ALTER TABLE `quotes`" ; # foreach (sort keys %i) { # if ($_ =~ /qty_/g) { # $mysql .= qq~ # DROP `$_`,~ ; # } # } foreach (sort keys %i) { if ($_ eq 'iaction') { next ; } if ($ignore{$_}) { next ; } if ($col_exists{$_}) { next ; } # only create the column if it doesn't exist if (((substr($_,0,3) eq 'vat') or (substr($_,0,4) eq 'excl')) and (substr($_,0,9) ne 'vat_total')) { $mysql .= qq~ ADD `$_` INT(1) NOT NULL DEFAULT '0',~ ; } elsif ($_ =~ /qty_/g) { $mysql .= qq~ ADD `$_` INT(3) NULL DEFAULT '0',~ ; } elsif ($_ =~ /roe_/g) { $mysql .= qq~ ADD `$_` VARCHAR( 120 ) NOT NULL ,~ ; } elsif ($_ =~ /description_/g) { $mysql .= qq~ ADD `$_` VARCHAR( 255 ) NOT NULL ,~ ; } elsif (substr($_,0,3) eq 'roe') { $mysql .= qq~ ADD `$_` DECIMAL(4,2) NOT NULL ,~ ; # 999999.99 to -999999.99 } elsif ($_ =~ /date_/g or $_ =~ /_date/g) { $mysql .= qq~ ADD `$_` DATE DEFAULT NULL ,~ ; } elsif ($_ =~ /datetime/g) { $mysql .= qq~ ADD `$_` datetime DEFAULT NULL ,~ ; } elsif ($_ =~ /_costing_/g or $_ =~ /_total/g or $_ =~ /total_/g or $_ =~ /year_/g or $_ =~ /_amnt_/g) { # } elsif (substr($_,0,5) eq 'item_') { $mysql .= qq~ ADD `$_` DECIMAL( 8, 2 ) NOT NULL ,~ ; # 999999.99 to -999999.99 } elsif ($_ =~ /_included/g) { $mysql .= qq~ ADD `$_` TEXT NOT NULL ,~ ; } elsif ($_ =~ /_id/g) { $mysql .= qq~ ADD `$_` INT( 6 ) NOT NULL DEFAULT '0',~ ; } else { $mysql .= qq~ ADD `$_` VARCHAR( 120 ) NOT NULL ,~ ; } } print $mysql ; exit ; } #------------------------------------------------------------------------------------------ sub report_ifields { # my $account_sql_where = '' ; if ($i{account_id}) { $account_sql_where = "`id`='$i{account_id}'" ; } elsif ($glob_account_id) { $account_sql_where = "`id`='$glob_account_id'" ; } # &db_common_ro('account','id,name',$account_sql_where,'','') ; # foreach my $id (keys %{$db{account}}) { $account_name{$id} = $db{account}{$id}{name} ; } if ($i{quote_to}) { push @report_sql, "($table.quote_to = '$i{quote_to}')" ; push @report_results, "`quote_to` = '$i{quote_to}'" ; } # if ($i{ship_to}) { # push @report_sql, "(`ship_to` = '$i{ship_to}')" ; # push @report_results, "ship_to = $i{ship_to}" ; # } if ($i{quotes_from} and $i{quotes_to}) { # check dates my $quotes_from_check = $i{quotes_from} ; $quotes_from_check =~ s/\-//g ; my $quotes_to_check = $i{quotes_to} ; $quotes_to_check =~ s/\-//g ; if ($quotes_from_check > $quotes_to_check) { $error = qq(QUOTES FROM ($i{quotes_from}) > QUOTES TO ($i{quotes_to})); &report_screen; } } if ($i{quotes_from}) { push @report_sql, "($table.quote_date >= '$i{quotes_from}')" ; push @report_results, "`quote_date` >= '$i{quotes_from}'" ; } if ($i{quotes_to}) { push @report_sql, "($table.quote_date <= '$i{quotes_to}')" ; push @report_results, "`quote_date` <= '$i{quotes_to}'" ; } if ($i{po_nr}) { push @report_sql, "($table.po_nr LIKE '%$i{po_nr}%')" ; push @report_results, "`po_nr` LIKE '$i{po_nr}'" ; } if ($i{invoice_nr}) { push @report_sql, "($table.invoice_nr LIKE '%$i{invoice_nr}%')" ; push @report_results, "`invoice_nr` LIKE '$i{invoice_nr}'" ; } if ($i{quote_nr}) { push @report_sql, "($table.quote_nr LIKE '%$i{quote_nr}%')" ; push @report_results, "`quote_nr` = '$i{quote_nr}'" ; } # if ($i{serial_nr}) { # for (1 .. 10) { # push @report_sql_or, "(`serial_nr_$_\_camera_details` LIKE '%$i{serial_nr}%')" ; # } # push @report_results, "serial_nr LIKE `$i{serial_nr}`" ; # my $sql_or = join(' OR ', @report_sql_or) ; # push @report_sql, "($sql_or)" ; # } if ($i{country}) { push @report_sql, "($table.country_shipped = '$i{country}')" ; push @report_results, "`country_shipped` = '$i{country}'" ; } if ($i{customer_name}) { push @report_sql, "($table.quote_to = '$i{customer_name}')" ; push @report_results, "`customer` = '$i{customer_name}'" ; } if ($i{accepted} or $i{options} eq 'accepted') { push @report_sql, "($table.quote_accepted='1')" ; push @report_results, "`quote_accepted` = 'Yes'" ; } elsif ($i{closed} or $i{options} eq 'closed') { # push @report_sql, "(`quote_cancelled`='1')" ; # push @report_sql, "(`quote_cancelled`='1' OR `quote_expiry`<'$now_year-$now_mm-$now_dd')" ; push @report_sql, "($table.quote_cancelled='1' OR ($table.quote_expiry<'$now_year-$now_mm-$now_dd' AND $table.quote_accepted<>'1'))" ; push @report_results, "`quote_closed` = 'Yes'" ; } elsif ($i{options} && $i{options} ne 'all') { push @report_results, "`quote` = '$i{options}'" ; } elsif ($i{options} && $i{options} eq 'all') { push @report_results, "(`quote_accepted` = 'Yes' OR `quote_pending` = 'Yes' OR `quote_closed` = 'Yes')" ; } if ($i{type}) { push @report_sql, "($table.type='$i{type}')" ; push @report_results, "`type` = '$i{type}'" ; } $report_results_msg = uc join(', ', @report_results) ; unless ($report_results_msg) { $error = qq(ENTER AT LEAST ONE SEARCH PARAMETER); &report_screen; } else { $isaved = qq(SELECT WHERE $report_results_msg) ; } } #------------------------------------------------------------------------------------------ sub report_screen { &filter_screen if $ENV{QUERY_STRING} eq 'filter' ; our $lcol = 2 ; our $fcol = 5 ; my ($set_year,$set_month,$set_day) = Add_Delta_Days($now_year,$now_mm,$now_dd,-42); # 6 weeks back $set_month = sprintf("%02s", $set_month) ; $set_day = sprintf("%02s", $set_day) ; my $set_ccyy_dd_mm = $set_year . '-' . $set_month . '-' . $set_day ; $print_box_content_rows .= &common_min_forms_start('report') ; $allow_deselect{customer_name} = 1 ; $allow_deselect{camera_system} = 1 ; $allow_deselect{country} = 1 ; $allow_deselect{options} = 1 ; $allow_deselect{type} = 1 ; &common_min_select_opts('customer_name','customers','name','','','',''); $print_box_content_rows .= &common_min_form_select('customer_name','') ; # $print_box_content_rows .= &common_min_form_input('customer_name','') ; $print_box_content_rows .= &common_min_form_input('quote_nr','') ; $print_box_content_rows .= &common_min_form_datepicker('quotes_from','') ; # $set_ccyy_dd_mm $print_box_content_rows .= &common_min_form_datepicker('quotes_to','') ; # $now_ccyy_mm_dd $print_box_content_rows .= &common_min_form_input('po_nr','') ; $print_box_content_rows .= &common_min_form_input('invoice_nr','') ; &common_min_select_opts('camera_system','camera_systems','name','','','',''); $print_box_content_rows .= &common_min_form_select('camera_system','') ; # $print_box_content_rows .= &common_min_form_input('serial_nr','') ; # $print_box_content_rows .= &common_min_form_input('country','') ; &common_min_select_opts('country','countries','name','','','',''); $print_box_content_rows .= &common_min_form_select('country','') ; # $print_box_content_rows .= &common_min_form_checkbox('invoiced','','') ; # Display all quotes that is Invoiced # $print_box_content_rows .= &common_min_form_checkbox('accepted','','') ; # Display all quotes that is Accepted # $print_box_content_rows .= &common_min_form_checkbox('closed','','') ; # Display all quotes that is Cancelled my $options_selected = 'SELECTED' if $useropts{boss}{$username} or $useropts{super}{$username} ; $opts{options} = qq~~ ; $print_box_content_rows .= &common_min_form_select('options','') ; $opts{type} = qq~~ ; $print_box_content_rows .= &common_min_form_select('type','') ; $print_box_content_rows .= &common_min_forms_end('','','report') ; &common_min_search_screen ; } #------------------------------------------------------------------------------- sub filter_screen { our $lcol = 2 ; our $fcol = 5 ; my ($set_year,$set_month,$set_day) = Add_Delta_Days($now_year,$now_mm,$now_dd,-42); # 6 weeks back $set_month = sprintf("%02s", $set_month) ; $set_day = sprintf("%02s", $set_day) ; my $set_ccyy_dd_mm = $set_year . '-' . $set_month . '-' . $set_day ; $print_box_content_rows .= &common_min_forms_start('report') ; &common_min_select_opts('customer_name','customers','name','','','',''); $print_box_content_rows .= &common_min_form_select('customer_name','') ; $print_box_content_rows .= &common_min_form_datepicker('quotes_from','') ; # $set_ccyy_dd_mm $print_box_content_rows .= &common_min_form_datepicker('quotes_to','') ; # $now_ccyy_mm_dd $opts{type} = qq~~ ; $print_box_content_rows .= &common_min_form_select('type','') ; my $options_selected = 'SELECTED' if $useropts{boss}{$username} or $useropts{super}{$username} ; $opts{options} = qq~~ ; $print_box_content_rows .= &common_min_form_select('options','') ; $print_box_content_rows .= &common_min_forms_end('','','report') ; &common_min_search_screen ; } #------------------------------------------------------------------------------- # use tabs_test2 ; use tabs ; use common ; use today ; use report ; use xlsxcreator ; 1;