#!/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_sum_email3:       begin execution            **
**=====================================================**
DATE: $DATE1

ENDOFFILE

##------------------------------------------------------------------##
## CREATE TELEPHONE BILLING SUMMARY REPORTS:        $DATE1
##
## Process through the list of email ids and write to the output file.
##   - Exec stored procedure p001_sum_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. 
## 
##------------------------------------------------------------------##

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

$BADGE = ' ';

$BADGE = $badge;

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

##
##  OPEN FILE AND PROCESS UNTIL EOF               
##
open(IN01, "</home/j204sc/output/sum.email.ids");
    while (<IN01>) {
      chop;
      ($ldapid,$badge,$email) = split (/:/,$_,3);
#      print $ldapid,$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/SUM_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,'A',$rows,$email,$ldapid);
      }

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

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


$sql_cmd = " 
  select f_fypd1
     , substr(a_uid_x,1,30)
     , a_badge_n
     , a_ldapid_c
     , f_loc_c
     , f_dpt_c
     , f_bill_x
     , f_key_c
     , sum(f_pd01_a)
  from t204_web_summary
     , t204_web_fypd
 where a_ldapid_c like '$ldapid'
   and a_ldapid_c > ' '
   and a_badge_n  like '$badge'
 group by f_fypd1
        , substr(a_uid_x,1,30)
        , a_badge_n
        , a_ldapid_c
        , f_loc_c
        , f_dpt_c
        , f_bill_x
        , f_key_c
 having sum(f_pd01_a) <> 0
 order by 1, 2, 3, 4
     ";

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

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

$stmt->execute;

$rows = 0;

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

if ($rows eq 0) {  ## PRINT HEADINGS

$rows = 4;

$data[0] = substr($data[0].$SP,0,6);
$data[1] = substr($data[1].$SP,0,30);
$data[2] = substr($data[2].$SP,0,8);
$data[3] = substr($data[3].$SP,0,8);

# FYPD HEADER
$rows = $rows + 1;
printf (OUT01 "%10s %6s %67s %8s %1s %7s %30s %11s \n","    FYPD:",$data[0]," ",$badge,'A',$rows,$email,$ldapid); 

# BLANK LINE 
$rows = $rows + 1;
printf (OUT01 "%s %8s %1s %7s %30s %11s \n","                                                                                     ",$badge,'A',$rows,$email,$ldapid); 

# NAME, BADGE, LDAPID
$rows = $rows + 1;
printf (OUT01 "%10s %30s %8s %8s %25s %8s %1s %7s %30s %11s \n","     NAME:",$data[1],$data[2],$data[3]," ",$badge,'A',$rows,$email,$ldapid); 

# BLANK LINE 
$rows = $rows + 1;
printf (OUT01 "%s %8s %1s %7s %30s %11s \n","                                                                                     ",$badge,'A',$rows,$email,$ldapid); 

# HEADING1   
$rows = $rows + 1;
printf (OUT01 "%s %8s %1s %7s %30s %11s \n","     Charge Description        Number/ID       CostCtr         Amount                ",$badge,'A',$rows,$email,$ldapid); 

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

$TOT1 = 0; 

  }  ## END PRINT HEADINGS

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

$rows = $rows + 1;

$TOT1 = $TOT1 + $data[8];

$data[4] = substr($data[4].$SP,0,2);
$data[5] = substr($data[5].$SP,0,4);
$data[6] = substr($data[6].$SP,0,25); 
$data[7] = substr($data[7].$SP,0,15);
$data[8] = &curr($data[8],2);
$filler  = substr($filler.$SP,0,15);

printf (OUT01 "%s %s %s %s %15s %s %8s %1s %7s %s %11s \n","    ",$data[6],$data[7],$data[4].$data[5],$data[8],$filler,$badge,'A',$rows,$email,$ldapid); 

   }

$stmt->finish;

##--section 0001 - end   

# SUMMARY1   
$rows = $rows + 1;
printf (OUT01 "%53s %15s %s %8s %1s %7s %s %11s \n","              ","--------------",$filler,$badge,'A',$rows,$email,$ldapid); 

$TOT1 = &curr($TOT1,2);

# SUMMARY2   
$rows = $rows + 1;
printf (OUT01 "%53s %15s %s %8s %1s %7s %s %11s \n","TOTAL CHARGES:",$TOT1,$filler,$badge,'A',$rows,$email,$ldapid); 

# END OF SECTION
if ($BADGE ne $badge) {
   open(INFF, "</home/j204sc/bin/SUM_EMAIL_FOOTER");
       while (<INFF>) {
         chop;
         $hdr = substr($_.$SP.$SP,0,85);
         $rows = $rows + 1;
         printf (OUT01 "%s %8s %1s %7s %30s %11s \n",$hdr,$badge,'A',$rows,$email,$ldapid);
      }
   close(INFF);
   }

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

$TOT1 = 0; 

$totrows = $totrows + $rows;

##--section 0003 - end   

$BADGE = $badge;


      }

close(IN01);

close(OUT01);


print <<ENDOFFILE;

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

 END OF JOB.   

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

$dbh->disconnect;

exit;
