aisa/scripts/db/admin/amend_db_clients_with_missing.pl

#!/usr/bin/perl

print "Content-type: text/html\n\n";

BEGIN { use lib '/usr/home/cfg' ; require push_inc ; }
    
require cfg ;

&today ;

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

use DBI;
use CGI::Carp qw(fatalsToBrowser);

&select_db_clients_add ;
&select_db_clients ;
&select_db_clients_from_waybills ;
&check_clients ;

exit ;

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

sub select_db_clients_add {

&db_open_ro ;

my $sql = qq(SELECT client,accno FROM CLIENT_ADD) ;

my $sth = $dbh->prepare($sql) ;

$sth -> execute() or die "Could not execute SQL statement $sql ... maybe invalid? $!";
my $clients_array_ref = $sth->fetchall_arrayref();

$sth->finish();

&db_close_conn ;

foreach $row (@$clients_array_ref) {
    my $cl_client      = @$row[0] ;    
    my $cl_accno       = @$row[1] ;    
    
    $client_add{$cl_client} = $cl_accno ;
    }
    
# foreach my $client (keys %client) {
    # # print "<br>*** $id -> $client{$id}" ; 
    # # &update_clients($id,$client{$id}) ;    
    # # &update_clients($client{$id},$client{$id}) ;    
    # }

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

sub select_db_clients {

&db_open_ro ;

my $sql = qq(SELECT id,client,accno FROM clients) ;

my $sth = $dbh->prepare($sql) ;

$sth -> execute() or die "Could not execute SQL statement $sql ... maybe invalid? $!";
my $clients_array_ref = $sth->fetchall_arrayref();

$sth->finish();

&db_close_conn ;

foreach $row (@$clients_array_ref) {
    my $cl_id          = @$row[0] ;    
    my $cl_client      = @$row[1] ;    
    my $cl_accno       = @$row[2] ;    
    
    $clientids{$cl_id} = $cl_client ;    
    $clients{$cl_client} = $cl_id ;    
    }
    
} #------------------------------------------------------------------------------------------

sub select_db_clients_from_waybills {

&db_open_ro ;

my $sql = qq(SELECT client,accno,clientid FROM ffwaypls_filmfreight.waybills WHERE clientid <> '') ;

my $sth = $dbh->prepare($sql) ;

$sth -> execute() or die "Could not execute SQL statement $sql ... maybe invalid? $!";
my $waybill_array_ref = $sth->fetchall_arrayref();

$sth->finish();

&db_close_conn ;

foreach $row (@$waybill_array_ref) {
    $wb_client          = @$row[0] ;
    $wb_accno           = @$row[1] ;
    $wb_clientid        = @$row[2] ;
    
    # $client{$wb_clientid} = $wb_accno . ' -> ' . $wb_client ;
    # $client{$wb_clientid} = $wb_accno ;
    $client_name{$wb_clientid} = $wb_client ;
    $client_id{$wb_client} = $wb_clientid ;
    $client_accno{$wb_client} = $wb_accno ;
    }
    
} #------------------------------------------------------------------------------------------

sub check_clients {

# foreach my $client (keys %clients) {
    # # print "<br>*** $id -> $client{$id}" ; 
    # }

foreach my $name (keys %client_add) {
    # unless ($client_id{$name}) {
    unless ($clients{$name}) {
        if ($clientids{$client_id{$name}}) { $print_dup = qq( >>>>> EXISTS as $clientids{$client_id{$name}}) ; } else { $print_dup = qq() ; }
        print "<br>*** $name ------------------> $client_id{$name} $print_dup" ; 
        &insert_client($name,$client_id{$name},$client_accno{$name}) ;
        }       
    }
    
# foreach my $name (keys %client_add) {
    # unless ($client_id{$name}) {
        # print "<br>*** $name" ; 
        # }       
    # }
    
} #------------------------------------------------------------------------------------------

sub insert_client {

my ($name,$id,$accno) = @_ ;

unless ($id){
    $accno = &db_get_max_client_acc_no ;
    $id    = &db_get_max_client_id ;
    }

# &db_open_upd ;

$sql = qq(INSERT INTO ffwaypls_filmfreight.clients (`ID`,`Client`,`AccNo`,`Branc`,`OnHold`,`Dormant`,`Fuel %`,`Fuel Min.`,`Doc Fee`,`Ins %`,`Ins Min`,`Taxable`,`Billing Cycle`,`Credit Limit`,`Owing`,`DateCreated`,`RateDate`,`TradingDate`,`CutoffDay`,`SalesRep`,`PhyicalAddr`,`PostalAddr`,`Contact1`,`Designation1`,`Email1`,`Contact2`,`Designation2`,`Email2`,`Contact3`,`Designation3`,`Email3`,`Website`,`PhoneNo`,`AhPhoneNo`,`FaxNo`,`CellNo`,`RegNo`,`VatNo`,`Director`,`BankName`,`BankAccNo`,`BankBranchCode`,`BankBranchName`)  
VALUES ('$id','$name','$accno','Film Freight - Johannesburg','0','0','','0','0','0','0','-1','M','0','0.00','$now_ccyy_mm_dd','0000-00-00','0000-00-00','0','','$i{'icl_phys_address'}','$i{'icl_post_address'}','$i{'icl_contact_1'}','','$i{'icl_email_1'}','$i{'icl_contact_2'}','','$i{'icl_email_2'}','$i{'icl_contact_3'}','','$i{'icl_email_3'}','','$i{'icl_phoneno'}','','','$i{'icl_cellno'}','$i{'icl_regno'}','$i{'icl_vatno'}','','','','','') ;
) ;
          
&common_debug($sql) ;

# $sth = $dbh -> do ($sql) or die "could not execute :<BR>$sql<BR>$!" ;

# &db_close_conn ;

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

# sub update_clients_1 {

# my ($id,$client) = @_ ;

# # &db_open_upd ;

# $sql = qq(UPDATE clients SET ID = '$id' WHERE client = "$client") ;
          
# &common_debug($sql) ;

# # $sth = $dbh -> do ($sql) or die "could not execute :<BR>$sql<BR>$!" ;

# # &db_close_conn ;

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

# sub update_clients {

# my ($id,$accno) = @_ ;

# &db_open_upd ;

# $sql = qq(UPDATE ffwaypls_filmfreight.clients SET ID = '$id' WHERE accno = '$accno'
# ) ;
          
# &common_debug($sql) ;

# $sth = $dbh -> do ($sql) or die "could not execute :<BR>$sql<BR>$!" ;

# &db_close_conn ;

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

use db ;
use today ;
use common ;

1;