#!/usr/bin/perl

use DBI;

require "/home/j204sc/bin/cgi-lib2.pl";          ## Perl Handling Sub-routines
require "/home/j204sc/bin/currency.pl";          ## Currency Format Sub-routine
require "/home/j204sc/bin/DEFAULT_FONTS";        ## Default Fonts
require "/home/j204sc/bin/ORACLE_CONNECTION";    ## Oracle DB Connection Routine

if ($dbh eq '') { print "$FA Connection to Oracle Failed."; exit; }

chop $DATE1;

print <<ENDOFFILE;
**=====================================================**
** j204sc_billing_email3:       begin execution        **
**=====================================================**
DATE: $DATE1

##------------------------------------------------------------------##
## CREATE OOL BILLING REPORTS:        $DATE1
##
## Process through the list of email ids and write to the output file.
##   - Exec stored procedure p001_billing_email1 for Header Info
##   - Exec stored procedure p001_billing_email2 for Summary Info
##   - Exec stored procedure p001_billing_email3 for Detail Info
## 
## Note: Output file is fixed width for xfer to MVS for email routing.
##       the Report fields take up cols 1-86 
##       with the BADGE and key fields beginning in col 87. 
## 
##------------------------------------------------------------------##

ENDOFFILE

$rows = 0;
$emails = 0;
$totrows = 0;
$SP = '                                                                   ';

$BADGE = ' ';

$BADGE = $badge;


open(OUT01, ">/home/j204sc/output/ool.emails");

##
##  OPEN FILE AND PROCESS UNTIL EOF               
##
open(IN01, "</home/j204sc/output/ool.email.ids");
    while (<IN01>) {
      chop;
      ($phone,$fypd,$badge,$email) = split (/:/,$_,4);
#      print $phone,$fypd,$badge,$email; 
#      print "\n";

       $emails = $emails + 1;

##
##--section 0000 - begin (print out email header)
##

if ($BADGE ne $badge) {
    $rows = 0;
   open(INHH, "</home/j204sc/bin/OOL_EMAIL_HEADER");
       while (<INHH>) {
         chop;
         $hdr = substr($_.$SP.$SP,0,85);
         $rows = $rows + 1;
         printf (OUT01 "%s %8s %1s %7s %30s %11s \n",$hdr,$badge,'C',$rows,$email,$phone);
      }

   close(INHH);
   }
##-section 0000 - end

##
##--section 0001 - begin (print out report headings)
##

$rows = $rows + 1;
printf (OUT01 "%s %8s %1s %7s %30s %11s \n","NUMBER      NAME                       FY  PD AMT CHARGED to LOC DIV DEPT            ",$badge,'C',$rows,$email,$phone); 

$rows = $rows + 1;
printf (OUT01 "%8s %s %1s %7s %30s %11s \n","----------- ------------------------- ---- -- -----------    --- --- ----            ",$badge,'C',$rows,$email,$phone); 

$sql_cmd = " 
  select a_phone_n
       , substr(a_uid_x,1,30)
       , substr(f_fypd,1,4)
       , substr(f_fypd,5,2)
       , to_char(sum(f_txn_a + f_adm_a), '999,990.99')
       , ' '
       , f_loc_c
       , f_div_c
       , f_dpt_c
    from commbill_dbo.t204_cb_ool_charges
   where a_phone_n = '$phone'
     and f_fypd    = '$fypd'
   group by a_phone_n
          , a_uid_x
          , f_fypd
          , f_loc_c
          , f_div_c
          , f_dpt_c
   order by a_phone_n
     ";

$stmt = $dbh->prepare("
        $sql_cmd
     ") or DBI::errstr;

$stmt->execute;

#print "SQL",$sql_cmd;
#exit;

while ( @data = $stmt->fetchrow_array() ) {

$rows = $rows + 1;

$data[0] = substr($data[0].$SP,0,11);
$data[1] = substr($data[1].$SP,0,25);
$data[2] = substr($data[2].$SP,0,4);
$data[3] = substr($data[3].$SP,0,2);
$data[4] = substr($data[4].$SP,0,11); 
$data[5] = substr($data[5].$SP,0,2);
$data[6] = substr($data[6].$SP,0,3);
$data[7] = substr($data[7].$SP,0,3);
$data[8] = substr($data[8].$SP,0,4);
$data[9] = substr($data[5].$SP,0,8);
$filler  = substr($filler.$SP,0,2);

printf (OUT01 "%s %s %s %s %s %s %s %s %s %s %s %s %s %7s %s %11s \n",$data[0],$data[1],$data[2],$data[3],$data[4],$data[5],$data[6],$data[7],$data[8],$data[9],$filler,$badge,'C',$rows,$email,$phone); 

print <<ENDOFFILE;
NBR: $data[0]    NAME: $data[1]  FYPD: $fypd
ENDOFFILE

   } 

$stmt->finish;

##--section 0001 - end   


##
##--section 0002 - begin (print out charge summary)
##

$rows = $rows + 1;
printf (OUT01 "%s %8s %1s %7s %30s %11s \n","                                                                                     ",$badge,'C',$rows,$email,$phone); 

$rows = $rows + 1;
printf (OUT01 "%s %8s %1s %7s %30s %11s \n","CHARGE                                                        AMOUNT                 ",$badge,'C',$rows,$email,$phone); 

$rows = $rows + 1;
printf (OUT01 "%s %8s %1s %7s %30s %11s \n","---------- ----------------------------------------------- --------------            ",$badge,'C',$rows,$email,$phone); 

$sql_cmd = " 
select f_vndr_c
     , 'Monthly Usage Charges  '
     , to_char(f_txn_a, '99,999,990.99')  
     , 3
  from t204_cb_ool_charges   
 where a_phone_n = '$phone'
  and f_fypd    = '$fypd'
union
select f_vndr_c
     , 'Administration Charge  '
     , to_char(f_adm_a, '99,999,990.99')  
     , 4
  from t204_cb_ool_charges   
 where a_phone_n = '$phone'
   and f_fypd    = '$fypd'
union
select f_vndr_c
     , '                                 Total Charges:'
     , to_char(f_txn_a + f_adm_a, '99,999,990.99')   
     , 5
  from t204_cb_ool_charges  
 where a_phone_n = '$phone'
   and f_fypd    = '$fypd'
union
select '----------'
     , '-----------------------------------------------'
     , '--------------'
     , 6
  from dual
 order by 4, 1, 2
   ";

$stmt = $dbh->prepare("
        $sql_cmd
     ") or DBI::errstr;

$stmt->execute;


#print "SQL",$sql_cmd;
#exit;

while ( @data = $stmt->fetchrow_array() ) {

$rows = $rows + 1;

$data[0] = substr($data[0].$SP,0,10);
$data[1] = substr($data[1].$SP,0,47);
$data[2] = substr($data[2].$SP,0,14);
$filler  = substr($filler.$SP,0,11);

printf (OUT01 "%s %s %s %s %s %s %7s %s %11s \n",$data[0],$data[1],$data[2],$filler,$badge,'C',$rows,$email,$phone); 

   } 

$stmt->finish;

##--section 0002 - end   

##
##--section 0003 - begin (print out report details)
##

$rows = $rows + 1;
printf (OUT01 "%s %8s %1s %7s %30s %11s \n","                                                                                     ",$badge,'C',$rows,$email,$phone); 

$rows = $rows + 1;
printf (OUT01 "%s %8s %1s %7s %30s %11s \n","CALL CITY  ST DIALED NUMBER    DATE      TIME    MINUTES       AMOUNT                ",$badge,'C',$rows,$email,$phone); 

$rows = $rows + 1;
printf (OUT01 "%s %8s %1s %7s %30s %11s \n","---------- -- ------------- ---------- -------- ---------- --------------            ",$badge,'C',$rows,$email,$phone); 

$sql_cmd = " 
  select f_city_x  
       , f_st_c
       , f_call_n
       , f_call_d
       , f_call_t
       , substr(f_call_q,6,10)
       , substr(f_call_a,2,14)
       , f_vndr_c
    from commbill_dbo.t204_cb_ool_details
   where a_phone_n = '$phone'
     and f_fypd    = '$fypd'
   order by f_call_d 
          , f_call_t
          , f_city_x
     ";

$stmt = $dbh->prepare("
        $sql_cmd
     ") or DBI::errstr;

$stmt->execute;

#print "SQL",$sql_cmd;
#exit;

while ( @data = $stmt->fetchrow_array() ) {

$rows = $rows + 1;

if ($data[7] eq 'ARCH      ' || $data[7] eq 'SKYTEL    ') {
   $data[0] = substr($data[0].$SP,0,40);
   $data[1] = substr($data[1].$SP,0,1);
   $data[2] = substr($data[2].$SP,0,1);
   $data[3] = substr($data[3].$SP,0,1);
   $data[4] = substr($data[4].$SP,0,1);
   $data[5] = substr($data[5].$SP,0,9);
   $data[6] = substr($data[6].$SP,0,14);
   $filler  = substr($filler.$SP,0,11);
   }
else {
   $data[0] = substr($data[0].$SP,0,10);
   $data[1] = substr($data[1].$SP,0,2);
   $data[2] = substr($data[2].$SP,0,13);
   $data[3] = substr($data[3].$SP,0,10);
   $data[4] = substr($data[4].$SP,0,8);
   $data[5] = substr($data[5].$SP,0,10);
   $data[6] = substr($data[6].$SP,0,14);
   $filler  = substr($filler.$SP,0,11);
   }

printf (OUT01 "%s %s %s %s %s %s %s %s %s %s %7s %s %11s \n",$data[0],$data[1],$data[2],$data[3],$data[4],$data[5],$data[6],$filler,$badge,'C',$rows,$email,$phone); 


   } 

$stmt->finish;

$rows = $rows + 1;
printf (OUT01 "%s %8s %1s %7s %30s %11s \n","-------------------------------------------------------------------------            ",$badge,'C',$rows,$email,$phone); 


$rows = $rows + 1;
printf (OUT01 "%s %8s %1s %7s %30s %11s \n","                                                                                     ",$badge,'C',$rows,$email,$phone); 

$totrows = $totrows + $rows;

##--section 0003 - end   

$BADGE = $badge;

      }

close(IN01);

close(OUT01);


print <<ENDOFFILE;

##------------------------------------------------------------------##
## OOL EMAILS: $emails   RECORDS:  $totrows 
##------------------------------------------------------------------##

 END OF JOB.   Number of Records: $rows

*AIX*Successful exit - '
**=====================================================**
** j204sc_billing_email3:         end execution        **
**=====================================================**
ENDOFFILE

$dbh->disconnect;

exit;
