#!/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_email6:       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_email5 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 LDAPID BILLING REPORTS
##----------------------------------------------##

ENDOFFILE

$rows = 0;
$emails = 0;

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

open(IN02, "</home/j204sc/output/ldap.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/LDAP_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,'X',$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, '9,999,990.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
    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,'X',$rows,$email,$uid); 

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

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

   $rows = $rows + 1;
   printf (OUT02 "%s%8s %1s %7s %30s %11s \n","------ --------------------- ---- -- --------- ------- ------------- --- --- ----     ",$badge,'X',$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,0,9);
   $data[9] = substr($data[9].$SP,0,7);
   $data[10] = substr($data[10].$SP,0,13);

   $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 %8s %1s %7s %30s %11s \n",$data[2],$data[4],$FY,$PD,$data[8],$data[9],$data[10],$data[5],$data[6],$data[7],$filler,$badge,'X',$rows,$email,$uid); 


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

   $rows = $rows + 1;
   printf (OUT02 "%s%8s %1s %7s %30s %11s \n","Conf-ID  Requestor Name           People  Call Date  Time     Duration Amount         ",$badge,'X',$rows,$email,$uid); 

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


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

   # REMOVED DATE FORMAT ON 11/25/2003
   # fill in the missing zero prefix
   #if (substr($D4,7,1) eq ' ') { $D4 = '0'.substr($D4,0,9); }
   #if (substr($D4,0,1) eq ' ') { $D4 = '0'.substr($D4,1,9); }
   #$D4 = substr($D4,0,2)."-".substr($D4,2,2)."-".substr($D4,4,4);

   $D5 = substr($data[15].$SP,0,7);

   $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,'X',$rows,$email,$uid); 

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

   }

$stmt->finish;


$rows = $rows + 1;
printf (OUT02 "%s%8s %1s %7s %30s %11s \n","---------------------------------------------------------------------------------     ",$badge,'X',$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_email6:         end execution        **
**=====================================================**
ENDOFFILE

$dbh->disconnect;

exit;
