#!/usr/bin/perl -w                                                          
### you know you can probably do this with temp tables in SQL....VVP        
### Description:  change old ccodes with new ones provided                  
### Will affect rtsdetail table - ccode field                               
                                                                            
use DBI;            # Load the DBI module                                   
use CGI;        # HTML                                                      
use strict;     # keep it usable                                            
use CGI qw(:standard);                                                      
use CGI::Carp qw( fatalsToBrowser );                                        
                                                                            
                                                                            
###                                                                         
my ($sth);                                                                  
my $ccode;                                                                  
my(@data,$data);                                                            
my ($p_str,@p_str);                                                         
my $i=0;  # counter                                                         
my ($CC,@CC);                                                               
my ($po,@po);                                                               
my ($old,$new);                                                             
my (@old,@new);                                                             
my $count;                                                                  
my $total;                                                                  
my $head_date;                                                              
                                                                            
open (CC,"ccchg.txt");                                                      
        while ($_=<CC>) {                                                   
        push (@CC,$_);                                                      
        ($old,$new)=split(/\|/,$_);                                         
        push @old,$old;                                                     
        push @new,$new;                                                     
        }                                                                   
close (POCOL);                                                              
                                                                            
                                                                            
$i=@new;                                                                    
#print "$i - EYE \n";                                                       
                                                                            
### Attributes to pass to DBI->connect() to disable automatic               
### error checking                                                          
my %attr = (                                                                
    PrintError => 0,                                                        
    RaiseError => 0,                                                        
);                                                                          
                                                                            
### Perform the connection using the Informix driver                        
my $dbh = DBI->connect( "dbi:Informix:nps\@sumitt_aaa","netsite","Ppol0699" 
 ,\%attr )or die "Can't connect to database: ", $DBI::errstr, "\n";         
                                                                            
$sth=$dbh->prepare("select det_number,det_ccode,head_date from podetail a,po
er b where det_number=head_number and head_date>'2002/01/01'");             
                                                                            
$sth->execute();                                                            
# will dump out all fields $data=$sth->dump_results();                      
#$data=$sth->dump_results();                                                
                                                                            
                while ( ($po, $ccode,$head_date)  = $sth->fetchrow_array() )
                #print " $po $ccode, $head_date \n";                        
                push (@po,$po);                                             
                                                                            
        }                                                                   
$sth->finish;                                                               
                                                                            
$p_str=@po;                                                                 
print "# of POs: $p_str \n";                                                
        $sth=$dbh->prepare("select a.det_number from podetail a, poheader b 
e det_ccode=? and head_date>'2002/01/01' and a.det_number=?");              
foreach $po(@po) {                                                          
        for ($count=0; $count<=$i;$count++) {                               
        #print "old cc: $old[$count], PO checked: $po \n";                  
        $sth->execute($old[$count],$po);                                    
        $p_str=$sth->fetchrow_array();                                      
                if ($p_str) {                                               
                push (@p_str,$p_str);                                       
                print "$p_str \n";                                          
                }                                                           
$sth->finish;                                                               
        }                                                                   
}                                                                           
$total=@p_str;                                                              
print "Total: $total  AND @p_str \n";                                       
$total=@po;                                                                 
print "POs used: $total \n";