##
#!/usr/local/bin/perl5_8
#Campus Main Menu
use strict;
use CGI;
use CGI::Carp qw(fatalsToBrowser); #remove for PRD
use HTML::Template;
my $CGI = CGI->new;
#clear buffers and set up web page (required)
$|=1;
print $CGI->header;
#get the main menu, and pass $CGI variables to template(s) as needed
my $template = HTML::Template->new( filename => 'nc_mainmenu.tmpl',
associate => $CGI,
loop_context_vars => 1,
global_vars => 1,
);
############################### Begin Section #######################################
#Set up and generate drop down list for report dates variable ($rpt_asofdt)
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime;
my $mo = $mon + 1;
my $dy = $mday;
my $yr = $year + 1900;
#Set array to look up month text and last day of month (add dummy to index item 0)
my @lkup_mon_text = ( 'DUMMY000','JAN','FEB','MAR','APR','MAY',
'JUN','JUL','AUG','SEP','OCT','NOV','DEC');
my @lkup_mon_long = ( 'DUMMY000','January','February','March','April','May', 'June',
'July','August','September','October','November','December');
my @lkup_last_day = ( 'DUMMY000','31','28','31','30','31',
'30','31','31','30','31','30','31');
#loop through to generate rolling drop down list of months ('25' is arbitrary)
my ( $i, $next_mmyyyy, @rollingmonth, @dt_loop );
for ( $i=1; $i<=25; $i++ )
{ $next_mmyyyy = "Month Ending $lkup_mon_long[$mo] $yr";
push(@rollingmonth, $next_mmyyyy);
#pad months 1-9 with a leading zero
if ( length($mo) == 1 )
{ $mo = "0$mo";
}
#set up hash for TMPL_LOOP params
my %dt_data;
$dt_data{dt_value} = "$yr~$mo~$dy~$lkup_mon_long[$mo] $dy, $yr";
$dt_data{dt_display} = "$next_mmyyyy";
push(@dt_loop, \%dt_data);
#determine if rolling back to previous calendar year
$mo = $mo - 1;
if ( $mo eq 0 )
{ $mo = 12;
$yr = $yr - 1;
}
$dy = $lkup_last_day[$mo];
#determine if February and a Leap Year; if so, last day = 29
if ( ( $mo eq 2 ) && ( $yr % 4 eq 0 ) )
{ $dy = 29;
}
}
#+++++++++++++++++++++++++++++++ End Section ++++++++++++++++++++++++++++++++++++++++
############################### Begin Section #######################################
#Control table used to generate report radio buttons, rpt titles...
#template vars: key->Rpt no. (rpt_no); value->Rpt Descr (rpt_descr)
my %rpt_tbl = ( '1' => 'Fiscal Year-to-Date Financial Information'
,'2' => 'QTD/PTD Financial Information'
,'3' => 'Cash Balance'
,'4' => 'Financial Status'
,'5' => 'Financial Summary Status'
,'6' => 'Summary by Phase'
,'7' => 'Summary by Segment'
);
my (@radio_loop, $key);
foreach $key ( sort keys %rpt_tbl )
{ push(@radio_loop, {rpt_no => $key, rpt_descr => $rpt_tbl{$key}});
}
#+++++++++++++++++++++++++++++++ End Section ++++++++++++++++++++++++++++++++++++++++
#pass the date loop info to the template, along with other params, and print web page
$template->param(dt_select=>\@dt_loop);
$template->param(radio =>\@radio_loop);
$template->param(title_bar=>"Main Menu");
print $template->output();
####
#!/usr/local/bin/perl5_8
#Main Summary Report program. This .pl processes the data from nc_main.pl and
#generates a Summary report based on the radio button selected on the Main Menu.
use strict;
use CGI;
use CGI::Carp qw(fatalsToBrowser); #remove for PRD
use HTML::Template;
use DBI;
use My::pswd;
my $CGI = CGI->new;
$|=1;
print $CGI->header;
my $err;
my $server = $My::pswd::server;
my $userid = $My::pswd::userid;
my $passwd = $My::pswd::passwd;
############################### Begin Section #######################################
#Set up variables
my $rpt_id = $CGI->param('rpt_id');
my @rpt_id = split (/_/, $rpt_id); #i.e., 1_summary
my $rpt_no = $rpt_id[0];
my $rpt_lvl = $rpt_id[1];
my $rpt_lvlU = ucfirst($rpt_lvl);
my $rpt_name = $CGI->param('rpt_name');
my $proj_id = $CGI->param('proj_id');
my $mycheck = $CGI->param('mycheck');
my ($rpt_type, $proj_select, $sql_select);
if ( substr($proj_id, 0, 1) == "0" )
{ $rpt_type = "PROJECT_REF";
$proj_select = "PROJECT_REF = ";
$sql_select = $rpt_type;
}
elsif ( $mycheck eq "yes" )
{ $rpt_type = "PROJECT_ID";
$proj_select = "PROJECT_ID = ";
$sql_select = $rpt_type;
}
else { $proj_id = "'" . substr($proj_id, 0, 6) . "%" . "'";
$rpt_type = "PROJECT_ID";
$proj_select = "PROJECT_ID LIKE ";
$sql_select = "SUBSTRING(PROJECT_ID,1,6)";
}
my $rpt_dates = $CGI->param('rpt_dates');
my @rpt_dates = split (/~/, $rpt_dates); #yyyymmdd~Month Ending mmm yyyy
my $sql_asofyr = $rpt_dates[0];
my $sql_asofmo = $rpt_dates[1];
my $sql_asofdy = $rpt_dates[2];
my $rpt_asofdt = $rpt_dates[3];
my $rpt_tmpl = "nc_rpt" . "$rpt_id" . ".tmpl";
my $tmpl_main = HTML::Template->new( filename => $rpt_tmpl,
associate => $CGI,
global_vars => 1,
);
#Calculate fiscal year based on $rpt_asofdt
my $attr_fyr;
if ( $sql_asofmo <= '06' )
{ $attr_fyr = $sql_asofyr;
}
else { $attr_fyr = $sql_asofyr + 1
}
$tmpl_main->param(attr_fyr => $attr_fyr);
#+++++++++++++++++++++++++++++++ End Section ++++++++++++++++++++++++++++++++++++++++
############################### Begin Section #######################################
#Control table used to generate case rows, labels, etc. (separated by a ~ character)
#key: last two digits: '00' => white row; '01' => green row; '99' => sub/total row
#value1: the first section indicates the number of account ranges for that row
#value2: the second section contains the Description
#value3: the third and subsequent sections are the account ranges (grouped by two)
my %ctrl_tbl = ( '010-00' => '1~Cash~11100~11100'
,'020-99' => '1~Total Assets~10000~19999'
,'030-00' => '1~Accounts Payable~22010~22010'
,'040-99' => '1~Total Liabilities~20000~29999~'
,'050-99' => '1~Fund Equity~30000~39999'
,'060-99' => '1~Total Revenue~40000~49999'
,'070-00' => '1~EPA Non-Teaching Salaries~51000~51199'
,'080-01' => '1~SPA Employee Salaries~51200~51299'
,'090-00' => '1~EPA Teaching Salaries~51300~51399'
,'100-01' => '1~Temporary Wages~51400~51499'
,'110-00' => '1~Other Personnel Costs~51500~51799'
,'120-01' => '1~Staff Benefits~51800~51899'
,'130-99' => '1~Total Personnel Costs~51000~51899'
,'140-00' => '1~Contracted Services~51900~51999'
,'150-01' => '1~Supplies and Materials~52000~52999'
,'160-00' => '2~Travel - Domestic~53100~53129~53140~53199'
,'170-01' => '1~Travel - Foreign~53130~53139'
,'180-00' => '2~Current Services~53000~53099~53200~53999'
,'190-01' => '1~Fixed Charges~54000~54999'
,'200-00' => '1~Capital Outlays~55000~55999'
,'210-01' => '1~Student Aid~56000~56979'
,'220-00' => '1~Subcontractors~56980~56989'
,'230-01' => '3~Other Charges~56990~58949~58951~58959~58961~59999'
,'240-00' => '1~Budget Pool~58950~58950'
,'250-01' => '1~F&A~56960~56960'
,'260-99' => '1~Total Non-Personnel Costs~51900~59999'
,'270-99' => '1~Total Costs~50000~59999'
,'280-99' => '1~Net Total~40000~59999'
,'290-00' => '1~FYTD Change in Accts Rec~11300~11399'
,'300-00' => '1~FYTD Change in Accts Pay~22010~22010'
,'888-99' => '1~Uncategorized Accounts~acct2~acct2'
,'999-99' => '1~Uncategorized Accounts~acct1~acct1'
);
my (@ctrl_split, $case_when, $exp_descr, $when_count, $p, $i, $key);
foreach $key ( sort ( keys %ctrl_tbl ) )
{ @ctrl_split = split (/~/, $ctrl_tbl{$key});
$when_count = $ctrl_split[0];
$exp_descr = $ctrl_split[1];
$case_when = " $case_when \n WHEN ACCOUNT BETWEEN '$ctrl_split[2]'
AND '$ctrl_split[3]' ";
$i = 0;
while ( $when_count - $i > 1 )
{ $p = ( $i*2)+4;
$case_when = " $case_when \n OR ACCOUNT BETWEEN
'$ctrl_split[$p]' AND '$ctrl_split[$p+1]' ";
$i++;
}
$case_when = " $case_when \n THEN '$key' ";
}
#+++++++++++++++++++++++++++++++ End Section ++++++++++++++++++++++++++++++++++++++++
############################### Begin Section #######################################
#Connect to database, fetch attributes, put in @loop_attr array to pass to template
my $dbh = DBI->connect("dbi:Sybase:server=" . $server, $userid, $passwd) ||
(&dberror("connect",$err=$DBI::errstr));
#keep fields in SELECT in this order so @loop_attr sets up template vars correctly
my $sql_attr = "
SELECT HDR.$sql_select, PROJECT_REF, DEPTID, DESCR, GR_AWARD_NBR, PROGRAM_CODE,
CONVERT(CHAR(10),START_DT,101) + ' - ' +
CONVERT(CHAR(10),END_DT,101) 'projpd',
RTRIM(CONVERT(CHAR(10),GR_BUDG_BEG,101)) + ' - ' +
RTRIM(CONVERT(CHAR(10),GR_BUDG_END,101)) 'budgpd',
FUND_CODE, PERCENTAGE_ALLOC, APPROPRIATION_NBR, EFF_STATUS,
GR_FIS_MGR, GR_EQUIP_IND, MANAGER_NAME, GR_PI_NAME
FROM FS702RPT.dbo.PS_NC_PROJ_ATTR_VW HDR
WHERE HDR.EFFDT = (SELECT MAX(EFFDT) FROM FS702RPT.dbo.PS_NC_PROJ_ATTR_VW
WHERE HDR.SETID = SETID AND HDR.$rpt_type = $rpt_type)
AND HDR.$proj_select ?
";
my $sth_attr= $dbh->prepare($sql_attr) || (&dberror("prep-1",$err=$DBI::errstr));
$sth_attr->execute($proj_id) || (&dberror("slct-1",$err=$DBI::errstr));
my (@loop_attr, $attr_exists, %row_attr);
do { while ( $attr_exists = $sth_attr->fetch )
{ $row_attr{proj_id} = $attr_exists->[0];
$row_attr{proj_ref} = $attr_exists->[1];
$row_attr{attr_dept} = $attr_exists->[2];
$row_attr{attr_descr} = $attr_exists->[3];
$row_attr{attr_award} = $attr_exists->[4];
$row_attr{attr_prog} = $attr_exists->[5];
$row_attr{attr_projpd} = $attr_exists->[6];
$row_attr{attr_budgpd} = $attr_exists->[7];
$row_attr{attr_fund} = $attr_exists->[8];
$row_attr{attr_rate} = $attr_exists->[9];
$row_attr{attr_subclass} = $attr_exists->[10];
$row_attr{attr_status} = $attr_exists->[11];
$row_attr{attr_spec} = $attr_exists->[12];
$row_attr{attr_equip} = $attr_exists->[13];
$row_attr{attr_resp} = $attr_exists->[14];
$row_attr{attr_pi} = $attr_exists->[15];
push(@loop_attr, \%row_attr);
}
};
#+++++++++++++++++++++++++++++++ End Section ++++++++++++++++++++++++++++++++++++++++
############################### Begin Section #######################################
#Unique SQL criteria for various columns
my $sql_cfte = "
ROUND ( SUM (
CASE
WHEN STATISTICS_CODE = 'FTE'
AND SCENARIO IN ('CN24','CN26','CY21','CY23')
AND (ACCOUNTING_PERIOD <= CONVERT(INT, (SUBSTRING('070809101112010203040506',
(DATEPART (MONTH, '$rpt_asofdt')*2)-1,2))) )
THEN POSTED_TOTAL_AMT
ELSE 0
END),2) 'cfte'
";
my $sql_cbud = "
ROUND ( SUM (
CASE
WHEN CURRENCY_CD = 'USD' AND SCENARIO IN ('CGLOAD','CG20','CG21','CI21','CN24','CN26','CY21','CY23','FN21')
AND (ACCOUNTING_PERIOD <= CONVERT(INT, (SUBSTRING('070809101112010203040506',
(DATEPART (MONTH, '$rpt_asofdt')*2)-1,2)))
OR ACCOUNTING_PERIOD = 0)
THEN POSTED_TOTAL_AMT
ELSE 0
END),2) 'cbud'
";
my $sql_cmo = "
ROUND ( SUM (
CASE
WHEN (ACCOUNTING_PERIOD = CONVERT(INT, ( SUBSTRING('070809101112010203040506',
(DATEPART (MONTH, '$rpt_asofdt')*2)-1,2))) )
THEN POSTED_TOTAL_AMT
ELSE 0
END),2) 'cmo'
";
my $sql_fytd = "
ROUND ( SUM (
CASE
WHEN (ACCOUNTING_PERIOD <= CONVERT(INT, (SUBSTRING('070809101112010203040506',
(DATEPART (MONTH, '$rpt_asofdt')*2)-1,2)))
OR ACCOUNTING_PERIOD = 0)
THEN POSTED_TOTAL_AMT
ELSE 0
END),2) 'fytd'
";
my $sql_pre = "
ROUND ( SUM (
CASE
WHEN (ACCOUNTING_PERIOD <= CONVERT(INT, (SUBSTRING('070809101112010203040506',
(DATEPART (MONTH, '$rpt_asofdt')*2)-1,2)))
OR ACCOUNTING_PERIOD = 0)
THEN PRE_ENCUM_AMOUNT
ELSE 0
END),2) 'pre'
";
my $sql_enc = "
ROUND ( SUM (
CASE
WHEN (ACCOUNTING_PERIOD <= CONVERT(INT, (SUBSTRING('070809101112010203040506',
(DATEPART (MONTH, '$rpt_asofdt')*2)-1,2)))
OR ACCOUNTING_PERIOD = 0)
THEN ENCUMBERED_AMOUNT
ELSE 0
END),2) 'enc'
";
my $sql_ffte = "
ROUND ( SUM (
CASE
WHEN STATISTICS_CODE = 'FTE' AND SCENARIO IN ('CN24','CN26','FY27','FY29')
AND (ACCOUNTING_PERIOD <= CONVERT(INT, (SUBSTRING('070809101112010203040506',
(DATEPART (MONTH, '$rpt_asofdt')*2)-1,2)))
OR ACCOUNTING_PERIOD = 0)
THEN POSTED_TOTAL_AMT
ELSE 0
END),2) 'ffte'
";
my $sql_fbud = "
ROUND ( SUM (
CASE
WHEN CURRENCY_CD = 'USD'
AND SCENARIO IN ('CN24','CN26','FY27','FY29')
AND (ACCOUNTING_PERIOD <= CONVERT(INT, (SUBSTRING('070809101112010203040506',
(DATEPART (MONTH, '$rpt_asofdt')*2)-1,2)))
OR ACCOUNTING_PERIOD = 0)
THEN POSTED_TOTAL_AMT
ELSE 0
END),2) 'fbud'
";
#Main SQL statement for all Budget columns
my $sql_bud = "
SELECT RTRIM($sql_select) 'PROJECT',
CASE
$case_when
ELSE '999'
END 'exp_catbud',
$sql_cfte,
$sql_cbud,
$sql_ffte,
$sql_fbud
FROM FS702RPT.dbo.PS_NC_RP_LEDG_BUDG
WHERE BUSINESS_UNIT = 'NCSU1'
AND LEDGER = 'BD_ACTUALS'
AND FISCAL_YEAR = DATEPART (YEAR,DATEADD(MONTH,6,'$rpt_asofdt'))
AND (ACCOUNTING_PERIOD <= CONVERT(INT, ( SUBSTRING ('070809101112010203040506',
( DATEPART (MONTH, '$rpt_asofdt')*2)-1,2))))
AND $proj_select ?
GROUP BY RTRIM($sql_select),
CASE
$case_when
ELSE '999'
END
";
#Main SQL statement for all Actuals columns
my $sql_act = "
SELECT RTRIM($sql_select) 'PROJECT',
CASE
$case_when
ELSE '999'
END 'exp_catact',
$sql_cmo,
$sql_fytd,
$sql_pre,
$sql_enc,
SUM(POSTED_TOTAL_AMT), SUM(PRE_ENCUM_AMOUNT), SUM(ENCUMBERED_AMOUNT)
FROM FS702RPT.dbo.PS_NC_RP_LEDGER
WHERE BUSINESS_UNIT = 'NCSU1'
AND LEDGER = 'ACTUALS'
AND CURRENCY_CD = 'USD'
AND FISCAL_YEAR = DATEPART (YEAR,DATEADD(MONTH,6,'$rpt_asofdt'))
AND ACCOUNTING_PERIOD <= CONVERT(INT, (SUBSTRING('070809101112010203040506',
(DATEPART (MONTH, '$rpt_asofdt')*2)-1,2)))
AND $proj_select ?
GROUP BY RTRIM($sql_select),
CASE
$case_when
ELSE '999'
END
";
#+++++++++++++++++++++++++++++++ End Section ++++++++++++++++++++++++++++++++++++++++
############################### Begin Section #######################################
#Generate Budget cell data
my $sth_act= $dbh->prepare($sql_bud) || (&dberror("prep-1",$err=$DBI::errstr));
$sth_act->execute($proj_id) || (&dberror("sel-1",$err=$DBI::errstr));
my @loop_data = ();
my ($bud_exists, $act_exists, %row_bud, %row_act, %col_acct, %col_descr, %col_cfte,
%col_cbud, %col_cmo, %col_fytd, %col_pre, %col_enc, %col_ffte, %col_fbud);
do { while ( $bud_exists = $sth_act->fetch )
{ $row_bud{col_acct} = $bud_exists->[1];
$row_bud{col_cfte} = $bud_exists->[2];
$row_bud{col_cbud} = $bud_exists->[3];
$row_bud{col_ffte} = $bud_exists->[4];
$row_bud{col_fbud} = $bud_exists->[5];
push(@loop_data, \%row_bud);
$col_cfte{$bud_exists->[1]} = $bud_exists->[2];
$col_cbud{$bud_exists->[1]} = $bud_exists->[3];
$col_ffte{$bud_exists->[1]} = $bud_exists->[4];
$col_fbud{$bud_exists->[1]} = $bud_exists->[5];
}
};
#Generate Actuals cell data
$sth_act= $dbh->prepare($sql_act) || (&dberror("prepare-1",$err=$DBI::errstr));
$sth_act->execute($proj_id) || (&dberror("select-1",$err=$DBI::errstr));
do { while ( $act_exists = $sth_act->fetch )
{ $row_act{col_acct} = $act_exists->[1];
$row_act{col_cmo} = $act_exists->[2];
$row_act{col_fytd} = $act_exists->[3];
$row_act{col_pre} = $act_exists->[4];
$row_act{col_enc} = $act_exists->[5];
push(@loop_data, \%row_act);
$col_cmo{$act_exists->[1]} = $act_exists->[2];
$col_fytd{$act_exists->[1]} = $act_exists->[3];
$col_pre{$act_exists->[1]} = $act_exists->[4];
$col_enc{$act_exists->[1]} = $act_exists->[5];
}
};
$dbh->disconnect || (&dberror("disconnect",$err=$DBI::errstr));
############################### Begin Section #######################################
#Generate and sort cell data for report
@loop_data = (); #ask Bill why empty
my ($acct_lbl, $acct_count, $ptr2, $i2, @loop_data2);
foreach $key ( sort(keys %ctrl_tbl) )
{ my %row_act; #ask Bill why re-initialized inside this foreach
@ctrl_split = split (/~/, $ctrl_tbl{$key});
$acct_count = $ctrl_split[0];
$acct_lbl = "$ctrl_split[2]-$ctrl_split[3]";
$i2 = 0;
while ( $acct_count - $i2 > 1 )
{ $ptr2 = ($i2*2)+4;
$acct_lbl = "$acct_lbl, $ctrl_split[$ptr2]-$ctrl_split[$ptr2+1]";
$i2++;
}
$row_act{col_acct} = $acct_lbl;
$row_act{col_descr} = $ctrl_split[1];
if ( $col_cfte{$key} ne "" )
{ $row_act{col_cfte} = $col_cfte{$key};
$row_act{col_cbud} = $col_cbud{$key};
$row_act{col_ffte} = $col_ffte{$key};
$row_act{col_fbud} = $col_fbud{$key};
}
else { $row_act{col_cfte} = 0;
$row_act{col_cbud} = 0;
$row_act{col_ffte} = 0;
$row_act{col_fbud} = 0;
}
if ( $col_cmo{$key} ne "")
{ $row_act{col_cmo} = $col_cmo{$key};
$row_act{col_fytd} = $col_fytd{$key};
$row_act{col_pre} = $col_pre{$key};
$row_act{col_enc} = $col_enc{$key};
}
else { $row_act{col_cmo} = 0;
$row_act{col_fytd} = 0;
$row_act{col_pre} = 0;
$row_act{col_enc} = 0;
}
$row_act{col_bba} = ( $row_act{col_cbud}
- $row_act{col_fytd}
+ $row_act{col_pre}
+ $row_act{col_enc} );
foreach my $k ( qw(col_cfte col_cbud col_cmo col_fytd col_pre col_enc col_bba col_ffte col_fbud) )
{ $row_act{$k} = &commify($row_act{$k}); }
push(@loop_data, \%row_act);
}
#+++++++++++++++++++++++++++++++ End Section ++++++++++++++++++++++++++++++++++++++++
############################### Begin Section #######################################
#Pass parameters and variable values from @loop arrays to template; print report
$tmpl_main->param(title_bar => "Report $rpt_no - $rpt_lvlU");
$tmpl_main->param(hdr_asofdt => "$rpt_asofdt");
$tmpl_main->param(passattrs => \@loop_attr);
$tmpl_main->param(passacts => \@loop_data);
print $tmpl_main->output();
print "mycheck value = $mycheck
\n";
print "rpt_type value = $rpt_type\n";
print "proj_id value = $proj_id
\n";
print "sql_attr value =
$sql_attr\n
";
print "sql_bud value =
$sql_bud\n
";
print "sql_act value =
$sql_act\n
";
#+++++++++++++++++++++++++++++++ End Section ++++++++++++++++++++++++++++++++++++++++
############################### Begin Section #######################################
#Subroutines below
sub dberror #Generates error messages when login fails
{
my $err_call= $_[0];
my $err_txt= $_[1];
print ("
A Database Error has occured
");
if ( $err_call eq "connect" )
{ if ( $err_txt =~ "Login failed\." )
{ print "An invalid login was submitted to the database server.";
print "Please re-submit your login credentials and retry.
";
}
else { print "
Error from $err_call= $err_txt
";
}
}
if ( $err_txt =~ "Data is unavailable; try again later." )
{ print "The database is currently offline. Please try again later.";
}
else { print "
Error from $err_call
";
print "Text of error is: $err_txt
";
print "Please contact the Help Desk at (919) 513-1178 for assistance
";
}
exit;
}
sub commify #Formats numbers (puts in commas)
{
local $_ = sprintf "%.2f", shift @_;
1 while $_ =~ s/^(-?\d+)(\d\d\d)/$1,$2/;
return $_;
}
#+++++++++++++++++++++++++++++++ End Section ++++++++++++++++++++++++++++++++++++++++