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

#**************************************************************#
#                                                              #
#  Job j204_mgr_email3 will create the Batch Email List        #
#                                                              #
#**************************************************************#

$DATE1 = `date`;
chop $DATE1;

print <<ENDOFFILE;
**=====================================================**
** j204_mgr_email3 :  begin execution                  **
**=====================================================**

##------------------------------------------------------------------##
## CREATE MANAGER SUMMARY REPORTS:        $DATE1
##------------------------------------------------------------------##

ENDOFFILE

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

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

##
##  OPEN FILE AND PROCESS UNTIL EOF               
##
open(IN01, "</home/j204sc/output/mgr.email.ids");
    while (<IN01>) {
      chop;
      ($uid,$badge,$name,$email) = split (/:/,$_,4);
       print "MGR/CNTLR:",$uid,$badge,$name,$email; 
       print "\n";
       $email = substr($email.$SP,0,30);

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

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

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

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


$sql_cmd = " 
   select a.f_svc_c     
        , a.a_phone_n  
        , a.a_uid_x   
        , a.a_badge_n
        , to_char(a.f_txn_a, '99999,990.99') 
        , a.f_fypd    
        , a.f_loc_c     
        , a.f_div_c     
        , a.f_dpt_c     
        , a.f_dpt_x
        , a.a_mgr_x
     from t204_mgr_summary    a
        , t204_mgr_email_ids  b
    where a.f_loc_c    = b.f_loc_c
      and a.f_div_c    = b.f_div_c
      and a.f_dpt_c    = b.f_dpt_c
      and b.a_uid_c    = '$uid'
    order by a.a_mgr_x
           , a.f_loc_c
           , a.f_div_c
           , a.f_dpt_c
           , a.a_badge_n
           , a.f_svc_c
     ";


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

$stmt->execute;

#print <<ENDOFFILE;
# $sql_cmd
#ENDOFFILE


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

$rows = $rows + 1;

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

$data[2] =~ tr/a-z/A-Z/;

##--section 0001a - Check for New Department Control Break Headings
if ($prevmgr eq $badge && $prevloc eq $data[6] && $prevdiv eq $data[7] && $prevdpt eq $data[8]) {
#print "YES",$prevmgr,$prevloc,$prevdiv,$prevdpt,"\n";
   }
else {
#print "NO ",$prevmgr,$prevloc,$prevdiv,$prevdpt,"\n";
   
$blank = substr(" ".$SP.$SP.$SP.$SP,0,85);
$rows = $rows + 1;
printf (OUT01 "%s %s %1s %7s %30s %11s \n",$blank,$badge,'M',$rows,$email,$name); 
$rows = $rows + 1;
printf (OUT01 "%s %s %1s %7s %30s %11s \n",$blank,$badge,'M',$rows,$email,$name); 

$desc1 = substr("DEPT: ".$data[6]." ".$data[7]." ".$data[8]." -  ".$data[9]."    -     ".$data[10]." ".$SP.$SP,0,85);
$rows = $rows + 1;
printf (OUT01 "%s %s %1s %7s %30s %11s \n",$desc1,$badge,'M',$rows,$email,$name); 

$rows = $rows + 1;
printf (OUT01 "%s %s %1s %7s %30s %11s \n",$blank,$badge,'M',$rows,$email,$name); 

$rows = $rows + 1;
printf (OUT01 "%s %s %1s %7s %30s %11s \n","SERVICE    NUMBER      NAME                      BADGE       AMOUNT       PERIOD     ",$badge,'M',$rows,$email,$name); 

$rows = $rows + 1;
printf (OUT01 "%s %s %1s %7s %30s %11s \n","---------- ----------- ------------------------- ------- ---------------- ------     ",$badge,'M',$rows,$email,$name); 
   }
##--section 0001a - END

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

$prevmgr = $badge;       
$prevloc = $data[6];
$prevdiv = $data[7];
$prevdpt = $data[8];
#print "LOC",$prevmgr,$prevloc,$prevdiv,$prevdpt,"\n";

   } 

$stmt->finish;

$rows = $rows + 1;
printf (OUT01 "%s %s %1s %7s %30s %11s \n",$blank,$badge,'M',$rows,$email,$name); 

$totrows = $totrows + $rows;

##--section 0001 - end   

      }

close(IN01);

close(OUT01);


print <<ENDOFFILE;

##------------------------------------------------------------------##
## MANAGER SUMMARY EMAILS: $emails   RECORDS:  $totrows 
##------------------------------------------------------------------##

*AIX*Successful exit -
**=====================================================**
** j204_mgr_email3 :    end execution                  **
**=====================================================**
ENDOFFILE


$dbh->disconnect;

exit;
