Greetings All,
I'm working on some code to essentially build a CGI page where the user places in there SPNs in the form with spaces(example--->) (967866 4566776). Then it calls the subroutine to run a join command to take the SPNS given by the user, and parse them with commas(example--->(967866, 4566776) and then outputs the results on the webpage. But I'm not sure if the subroutine is correct? This by far is the most complex program I have written thus far. Please let me know if the form portion is accurate as well for this? Many advanced thanks Perl scholars!
Cheers!
#!/opt/perl/1588/bin/perl -w
require "cgi-lib.pl"; #parse the input from the form and formats the html output
use CGI qw(:standard);
use strict;
use warnings;
use DBI;
use Config::Std; # Uses read_config to pull info from a config files. enhanced I
#NI format.
use vars qw ($Source_Host $Trade_ID $ACCT_PROD_STS $PROD_TYPE $CTPY_ID_TYPE $CTPY_SPN $NM_ALIAS $TRD_DATE $NXT_CSHFLW_DATE $EFFTVE_DATE $MATR_DATE $M_BUY_SELL $CURRCY $MTM_USD $RPLCE $OU $LE $OWN_SPN $ID_PRTFLO $NM_PRTFLO);
use Murex::Passwords;
&ReadParse(*input);
read_config "l2cgi.cfg" => my %cgi_config_hash;
#To process input of SPNS to place in Panama SELECT query and output on webpage
#my $spn = param('spn');
my $red_select = ("select
'Murex' AS Source_System,
DTTA.JPM_TRADE_ID,
DTT.ACCOUNTING_PRODUCT_STS,
DTT.ACCOUNTING_PRODUCT_STS,
DTT.PRODUCT_TYPE,
'SPN' as COUNTERPARTY_ID_TYPE,
DTTA.COUNTERPARTY_SPN,
A.NM_PARTY_ALIAS,
TO_CHAR(TRUNC(DTT.M_TRADE_DAT, 'DDD'), 'DD-MON-YYYY') as TRADE_DATE,
TO_CHAR(TRUNC(A.NEXT_CASHFLOW_DATE, 'DDD'), 'DD-MON-YYYY') as NEXT_CASHFLOW_DATE,
TO_CHAR(TRUNC(DTT.EFFECTIVE_DATE, 'DDD'), 'DD-MON-YYYY') as EFFECTIVE_DATE,
TO_CHAR(TRUNC(DTT.M_MATURITY, 'DDD'), 'DD-MON-YYYY')as MATURITY_DATE,
DTT.M_BUY_SELL,
DTTA.CURRENCY,
DTTA.SUM_MTM,
DTTA.SUM_MTM_USD,
REPLACE(DTTA.REGION_DATASET,'ENTITIES','EOD'),
RTCR.ID_OU,
RTCR.ID_LE,
DTTA.OWNER_SPN,
RTCR.ID_PORTFOLIO,
RTCR.NM_PORTFOLIO
from
PANAMA.DPS_TBL_TRADE_ATTRIB DTTA,
PANAMA.DPS_TBL_TRADE DTT,
(
SELECT DTTA.JPM_TRADE_ID,
MAX(DTC.CASHFLOW_DATE) AS NEXT_CASHFLOW_DATE,
RTCP.NM_PARTY_ALIAS
FROM
PANAMA.DPS_TBL_TRADE_ATTRIB DTTA,
PANAMA_REF_DATA.REF_TBL_CRDT_PARTY RTCP,
PANAMA.DPS_TBL_CASHFLOW DTC
WHERE DTTA.COUNTERPARTY_SPN IN ('$spns') <------ spn variable
AND DTTA.JPM_TRADE_ID = DTC.JPM_TRADE_ID
AND DTTA.COUNTERPARTY_SPN = RTCP.ID_PARTY
GROUP BY DTTA.JPM_TRADE_ID, RTCP.NM_PARTY_ALIAS
)
A,PANAMA_REF_DATA.REF_TBL_CRDT_PORTFOLIO RTCR
where DTT.JPM_TRADE_ID = DTTA.JPM_TRADE_ID
and (DTTA.INDEX_FLAG is NULL or DTTA.INDEX_FLAG = 'I')
and DTTA.JPM_TRADE_ID = A.JPM_TRADE_ID
and DTTA.BOOK = RTCR.NM_PORTFOLIO
order by 2"
);
my $l2_db_username = decrypt_murex_password($cgi_config_hash{'panprod_panro'}{'username'});#this sets the variable to decrypt the password within the l2cgi.cfg file, using the tacmon_read connection
my $l2_db_password = decrypt_murex_password($cgi_config_hash{'panprod_panro'}{'password'});#this sets the variable to decrypt the password within the l2cgi.cfig file, using the tacmon_read connection
my $dbh=DBI->connect($cgi_config_hash{'panprod'}{'dbi_connect'},$l2_db_username, $l2_db_password)|| die "ERROR opening database: $DBI::errstr\n";
my $sth = $dbh->prepare($red_select);
$sth ->execute;#make this a global variable, might have to revert back to local variable, will double check
#my $final_spn = $spn m/$d[0-9]\/\/;
my $output = (our @redspn); #send output to the array @redspn so it can then spliced into each row
my $w=new CGI;
my $user_entry=$w->param('user_entry');#param used for form submittal of the spns
sub main
{
print "<<HEADER";
print "<html><head>\n";
print "<title>RED DRILL PAGE</title></head>\n";
print "<body>\n";
print "<h3>Red Drill Credit Exposure</h3>\n";
show_form();#calling show_form subroutine
print"</body></html>\n";
}
if (defined $user_entry && $user_entry='Submitted')
{
sub show_form
{
print qq{<form name="input" action="Red_Drill_Mike.cgi" method="post">\n};
print qq{<table align="center" border="1" bordercolor="black" cellpadding="2" cellspacing="0">\n};
print qq{<tr>};
print qq{<td align="right">Please enter your SPNs</td};
print qq{</tr>\n};
print qq{<td align="left"><input type"text" width="7" name="spns" value="">};
print qq{<BR>Place each SPN seperated by a space</td>};
print qq{</table><center><input type="submit" value="Submitted"></center></form>\n};
}
sub process_spn
{
$spn()=@red_value;
@red_value = join (",",@red_value);
#Do a split on the data and then make a split by the space and insert a comma and then place it back in the $spn varialbe
}
foreach $output(@redspn)
{
($Source_Host, $Trade_ID, $ACCT_PROD_STS, $PROD_TYPE, $CTPY_ID_TYPE, $CTPY_SPN, $NM_ALIAS, $TRD_DATE, $NXT_CSHFLW_DATE, $EFFTVE_DATE, $MATR_DATE, $M_BUY_SELL, $CURRCY, $MTM_USD,
$RPLCE, $OU, $LE, $OWN_SPN, $ID_PRTFLO, $NM_PRTFLO )=split(",",$output );#split with a comma from each row.
if ($Source_Host eq "Source_System") #header rows
{
print"<tr>/n";
print"<th>$Source_Host</th>";
print"<th>$Trade_ID</th>";
print"<th>$ACCT_PROD_STS</th>";
print"<th>$PROD_TYPE</th>";
print"<th>$CTPY_ID_TYPE</th>";
print"<th>$CTPY_SPN</th>";
print"<th>$NM_ALIAS</th>";
print"<th>$TRD_DATE</th>";
print"<th>$NXT_CSHFLW_DATE</th>";
print"<th>$EFFTVE_DATE</th>";
print"<th>$MATR_DATE</th>";
print"<th>$M_BUY_SELL</th>";
print"<th>$CURRCY</th>";
print"<th>$MTM_USD<th/>";
print"<th>$RPLCE<th/>";
print"<th>$OU<th/>";
print"<th>$LE<th/>";
print"<th>$OWN_SPN<th/>";
print"<th>$ID_PRTFLO<th/>";
print"<th>$NM_PRTFLO<th/>";
print"<tr/>/n";
print "</table>\n";
next;
}
print"<tr>/n"; #data information from sql
print"<td>$Source_Host</td>";
print"<td align 'right'> $Trade_ID</td>/n";
print"<td align 'right'> $ACCT_PROD_STS</td>/n";
print"<td align 'right'> $PROD_TYPE</td>/n";
print"<td align 'right'> $CTPY_ID_TYPE</td>/n";
print"<td align 'right'> $CTPY_SPN</td>/n";
print"<td align 'right'> $NM_ALIAS</td>/n";
print"<td align 'right'> $TRD_DATE</td>/n";
print"<td align 'right'> $NXT_CSHFLW_DATE</td>/n";
print"<td align 'right'> $EFFTVE_DATE</td>/n";
print"<td align 'right'> $MATR_DATE</td>/n";
print"<td align 'right'> $M_BUY_SELL</td>/n";
print"<td align 'right'> $CURRCY</td>/n";
print"<td align 'right'> $MTM_USD</td>/n";
print"<td align 'right'> $RPLCE</td>/n";
print"<td align 'right'> $OU</td>/n";
print"<td align 'right'> $LE</td>/n";
print"<td align 'right'> $OWN_SPN</td>/n";
print"<td align 'right'> $ID_PRTFLO</td>/n";
print"<td align 'right'> $NM_PRTFLO</td>/n";
print"<tr/>/n";
}
}
print"</table>/n";
print <<FOOTER; #remember no space after << !
</body> <!-- end tag for main page section -->
</html> <!-- end tag for entire HTML page -->
FOOTER