#!/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_billing_email5:       begin execution        **
**=====================================================**
DATE: $DATE1
ENDOFFILE
                                                                       
##------------------------------------------------------------------##
## CREATE LDAPID BILLING REPORTS:        $DATE1
##
## Process through the list of email ids and write to the output file.
##   - Exec stored procedure p001_billing_email4 for Header and 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 = ' ';

print <<ENDOFFILE;

##----------------------------------------------##
## CREATE AT&T DIALIN BILLING REPORTS
##----------------------------------------------##

ENDOFFILE

$rows = 0;
$emails = 0;

open(OUT02, ">/home/j204sc/output/dialin.emails");

open(IN02, "</home/j204sc/output/dialin.email.ids");
    while (<IN02>) {
      chop;
      ($uid,$fypd,$badge,$email) = split (/:/,$_,4);
#      print $uid,$fypd,$badge,$email; 
#      print "\n";

       $emails = $emails + 1;
       $rows = 0;


##
##--section 0000 - begin (print out email header)
##
open(INHH, "</home/j204sc/bin/DIALIN_EMAIL_HEADER");
    while (<INHH>) {
      chop;
      $hdr = substr($_.$SP.$SP,0,85);
      $rows = $rows + 1;
      printf (OUT02 "%s %8s %1s %7s %30s %11s \n",$hdr,$badge,'W',$rows,$email,$uid); 
   }

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

$sql_cmd = " 
  select a.f_fypd  
       , a.f_vndr_c
       , a.a_uid_c
       , a.a_badge_n
       , a.a_uid_x
       , a.f_loc_c
       , a.f_div_c
       , a.f_dpt_c
       , to_char(a.f_txn_a, '99990.99')
       , to_char(a.f_adm_a, '990.99')
       , to_char(a.f_txn_a + a.f_adm_a, '99990.99')
       , b.f_desc1_x
       , b.f_desc2_x
       , b.f_desc3_x
       , b.f_call_d
       , b.f_call_t
       , b.f_call_q
       , b.f_call_a
       , (select to_char(sum(f_call_q), '990.99')
            from t204_cb_ldap_details c
           where c.f_fypd      = '$fypd'
             and c.a_uid_c     = '$uid'
             and c.f_call_q    <> ' '
             and c.f_call_q    is not null
             and c.f_vndr_c    = 'ATT-DIALIN' )
    from commbill_dbo.t204_cb_ldap_charges a
       , commbill_dbo.t204_cb_ldap_details b
   where a.f_fypd      = '$fypd'
     and a.a_uid_c     = '$uid'
     and a.f_vndr_c    = 'ATT-DIALIN'
     and a.f_fypd      = b.f_fypd
     and a.a_uid_c     = b.a_uid_c
     and a.f_vndr_c    = b.f_vndr_c
   order by a.f_fypd
          , a.f_vndr_c
          , a.a_uid_c
          , b.f_call_d
          , b.f_call_t
          , b.f_desc1_x desc
     ";

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

#print "CMD:",$sql_cmd;
#exit;

$stmt->execute;

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

$rows = $rows + 1;
##
## Check for a new header
##
if ($badge ne $BADGE || $data[1] ne $DATA1) {
   $rows = $rows + 1;
   printf (OUT02 "%s%8s %1s %7s %30s %11s \n","                                                                                      ",$badge,'W',$rows,$email,$uid); 

   $rows = $rows + 1;
   printf (OUT02 "%s%8s %1s %7s %30s %11s \n","                                                                                      ",$badge,'W',$rows,$email,$uid); 

   $rows = $rows + 1;
   printf (OUT02 "%s%8s %1s %7s %30s %11s \n","LDAPID NAME                   FY  PD HOURS  AMOUNT   ADMIN  TOTAL    LOC DIV DEPT     ",$badge,'W',$rows,$email,$uid); 

   $rows = $rows + 1;
   printf (OUT02 "%s%8s %1s %7s %30s %11s \n","------ --------------------- ---- -- ------ -------- ------ -------- --- --- ----     ",$badge,'W',$rows,$email,$uid); 

   $data[2] = substr($data[2].$SP,0,6);
   $data[4] = substr($data[4].$SP,0,21);
   $FY      = substr($data[0].$SP,0,4);
   $PD      = substr($data[0].$SP,4,2);

   $data[8] = substr($data[8].$SP,1,8);
   $data[9] = substr($data[9].$SP,1,6);
   $data[10] = substr($data[10].$SP,1,8);
   $data[18] = substr($data[18].$SP,1,6);

   $data[5] = substr($data[5].$SP,0,3);
   $data[6] = substr($data[6].$SP,0,3);
   $data[7] = substr($data[7].$SP,0,4);
   $filler  = substr($SP,0,3);

   printf (OUT02 "%s %s %s %s %s %s %s %s %s %s %s %s %8s %1s %7s %30s %11s \n",$data[2],$data[4],$FY,$PD,$data[18],$data[8],$data[9],$data[10],$data[5],$data[6],$data[7],$filler,$badge,'W',$rows,$email,$uid); 


   $rows = $rows + 1;
   printf (OUT02 "%s%8s %1s %7s %30s %11s \n","                                                                                      ",$badge,'W',$rows,$email,$uid); 

   $rows = $rows + 1;
   printf (OUT02 "%s%8s %1s %7s %30s %11s \n","Charge Description                        Logon Date Time      Hours    Amount        ",$badge,'W',$rows,$email,$uid); 

   $rows = $rows + 1;
   printf (OUT02 "%s%8s %1s %7s %30s %11s \n","----------------------------------------- ---------- -------- -------- ----------     ",$badge,'W',$rows,$email,$uid); 

  }


   $D1 = substr($data[11].$SP,0,25);
   $D2 = substr($data[12].$SP,0,7);
   $D3 = substr($data[13].$SP,0,7);

   $D4 = substr($data[14].$SP,0,10);
   $D5 = substr($data[15].$SP,0,8);
   
   $D6 = $data[16];
   $D6 =~ s/ //gi;
   $D7 = $data[17];
   $D7 =~ s/ //gi;

$filler  = substr($SP,0,3);


printf (OUT02 "%s %s %s %s %s %8s %10s %s %8s %1s %7s %30s %11s \n",$D1,$D2,$D3,$D4,$D5,$D6,$D7,$filler,$badge,'W',$rows,$email,$uid); 

$BADGE = $badge;
$DATA1 = $data[1];

   }

$stmt->finish;


$rows = $rows + 1;
printf (OUT02 "%s%8s %1s %7s %30s %11s \n","---------------------------------------------------------------------------------     ",$badge,'W',$rows,$email,$uid); 

print <<ENDOFFILE;
FYPD: $fypd  LDAPID: $uid  
ENDOFFILE

##--section 0001 - end   

      }

close(IN02);

close(OUT02);

print <<ENDOFFILE;

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

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

$dbh->disconnect;

exit;
