#!/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; }


$DATE1 = `date`;
chop $DATE1;

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

##------------------------------------------------------------------##
## CREATE TELEPHONE BILLING REPORTS:        $DATE1
##
## Process through the list of email ids and write to the output file.
##   - Exec stored procedure p001_tb_email1 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/tb.emails");

##
##  OPEN FILE AND PROCESS UNTIL EOF               
##
open(IN01, "</home/j204sc/output/tb.email.ids");
    while (<IN01>) {
      chop;
      ($phone,$fypd,$badge,$email,$typ) = split (/:/,$_,5);
#      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/DT_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,'B',$rows,$email,$phone);
      }

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

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

$BILLINGNBR = 'EXTENSION  ';
if ($typ eq 'CC') { $BILLINGNBR = 'CREDIT CARD'; }

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

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

$sql_cmd = " 
select a_phone_n
     , a_uid_x
     , f_fypd
     , f_fypd
     , to_char(sum(f_call_a), '999,990.99')
     , ' '
     , f_loc_c
     , f_div_c
     , f_dpt_c
  from t204_tb_dtbill
 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
         ";

if ($typ eq 'CC') { 
$sql_cmd = " 
select a_ccard_n
     , a_uid_x
     , f_fypd
     , f_fypd
     , to_char(sum(f_call_a), '999,990.99')
     , ' '
     , f_loc_c
     , f_div_c
     , f_dpt_c
  from t204_tb_ccbill
 where a_ccard_n = '$phone'
   and f_fypd    = '$fypd'
 group by a_ccard_n
        , a_uid_x
        , f_fypd
        , f_loc_c
        , f_div_c
        , f_dpt_c
         ";
   }

#print "SQL",$sql_cmd;


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

$stmt->execute;

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,4,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,'B',$rows,$email,$phone); 

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

  }

$stmt->finish;

##--section 0001 - end   


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

$rows = $rows + 1;
printf (OUT01 "%s %8s %1s %7s %30s %11s \n","                                                                                     ",$badge,'B',$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,'B',$rows,$email,$phone); 

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

$sql_cmd = " 
select f_ac_i
     , f_city_x
     , f_st_c
     , f_call_x
     , substr(f_call_d,1,4) || '/' ||
       substr(f_call_d,5,2) || '/' ||
       substr(f_call_d,7,2)
     , substr(f_call_t,1,2) || ':' ||
       substr(f_call_t,3,2) || ':' ||
       substr(f_call_t,5,2)
     , f_chh_n || ':' || f_cmm_n ||':'|| f_css_n
     , to_char(f_call_a, '999,990.99')
     , f_from_n
  from t204_tb_dtbill
 where a_phone_n = '$phone'
   and f_fypd    = '$fypd'
 order by f_call_d
        , f_call_t
         ";

if ($typ eq 'CC') { 
$sql_cmd = " 
select '  '   
     , f_city_x
     , '  '  
     , f_call_n
     , f_call_d
     , f_call_t
     , to_char(f_cmin_n, '00') || ':' || substr(to_char(f_csec_n, '00'),2,2)
     , to_char(f_call_a, '999,990.99')
     , f_from_n
  from t204_tb_ccbill
 where a_ccard_n = '$phone'
   and f_fypd    = '$fypd'
 order by f_call_d
        , f_call_t
         ";
   }

#print "SQL",$sql_cmd;

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

$stmt->execute;

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

$rows = $rows + 1;

   $data[0] = substr($data[0].$SP,0,2);
   $data[1] = substr($data[1].$SP,0,12);
   $data[2] = substr($data[2].$SP,0,2);
   $data[3] = substr($data[3].$SP,0,13);
   $data[4] = substr($data[4].$SP,0,10);
   $data[5] = substr($data[5].$SP,0,8);
   $data[6] =~ s/ //gi;
   $data[7] = &curr($data[7],2);
   $filler  = substr($filler.$SP,0,11);

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


  } 

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


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

$totrows = $totrows + $rows;

##--section 0003 - end   

$stmt->finish;

$BADGE = $badge;

      }

close(IN01);

close(OUT01);


print <<ENDOFFILE;

##------------------------------------------------------------------##
## TELEPHONE BILLING EMAILS: $emails   RECORDS:  $totrows 
##------------------------------------------------------------------##

 END OF JOB.   

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

$dbh->disconnect;

exit;

