aisa/scripts/db/admin/amend_db_clients_with_id.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);

# &update_db_clients_from_waybills ;
# &update_db_clients_from_client_accno ;
&check_db_clients_for_duplicates ;

exit ;

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

sub check_db_clients_for_duplicates {

&db_open_ro ;

my $sql = qq(SELECT id,client,accno FROM ffwaypls_filmfreight.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) {
    $cl_id          = @$row[0] ;
    $cl_client      = @$row[1] ;
    $cl_accno       = @$row[2] ;
    
    if ($client{$cl_id}) { &load_waybill($cl_accno) ; print '<br>' . $client{$cl_id} . ' <b>DUPLIACTED</b> - ' . $cl_id . ' : ' . $cl_client . ' : ' . $cl_accno . ' ---------> ' . $wbs{$cl_accno} ; }
    
    $client{$cl_id} .= $cl_accno . ' : ' . $cl_client ;
    }
    
} #------------------------------------------------------------------------------------------

sub load_waybill {

my ($accno) = @_ ;

&db_open_ro ;

my $sql = qq(SELECT * FROM ffwaypls_filmfreight.waybills WHERE accno = '$accno') ;

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

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

$sth->finish();

&db_close_conn ;

foreach $row (@$id_array_ref) {
    &db_waybills_fields ;
    
    $wbs{$accno} .= $wb{'waybillno'} . ' (<b>' . $wb{'clientid'} . ' - ' . $wb{'client'} . '</b>) ' ;
    }
   
} #------------------------------------------------------------------------------------------

sub update_db_clients_from_client_accno {

&db_open_ro ;

my $sql = qq(SELECT id,client,accno FROM ffwaypls_filmfreight.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) {
    $cl_id          = @$row[0] ;
    $cl_client      = @$row[1] ;
    $cl_accno       = @$row[2] ;
    
    if (length($cl_accno) > 3) { next ; }
    if ($cl_id == $cl_accno)   { next ; }
    
    $client{$cl_id} = $cl_accno ;
    }
    
foreach my $id (keys %client) {
    # print "<br>*** $id -> $client{$id}" ; 
    # &update_clients($id,$client{$id}) ;    
    &update_clients($client{$id},$client{$id}) ;    
    }

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

sub update_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 ;
    }
    
foreach my $id (keys %client) {
    # print "<br>*** $id -> $client{$id}" ; 
    # &update_clients($id,$client{$id}) ;    
    # &update_clients_1($id,$client_name{$id}) ;    
    }

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

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;