Parse user form input with join function and display SQL results

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

choosenalpha

Light Poster

30 posts since Nov 2009

Reputation Points: 10

Solved Threads: 0

1 Year Ago

What happened when you tested it? I can't test your script because I don't have all your modules and don't have your l2cgi.cfg file. Try to access your script on your web server, then open /var/log/apache2/error.log or whatever file your server uses to report errors and see what errors you find near the end of the log.

Where did you find Murex::Passwords? I don't see it on CPAN.

Don't require cgi-lib.pl. Somebody asked about cgi-lib.pl on Perl Monks in 2005 and were advised not to use it because it was old and obsolete.

d5e5

Practically a Posting Shark

810 posts since Sep 2009

Reputation Points: 159

Solved Threads: 159

1 Year Ago

Ok thanks didn't know about the require cgi-lib.pl portion thank you! And the Murex module is something in house to decrypt the password in the l2 config file. But my posting is a bit misleading my aplogies mate it should be a bit more generic. My question is how can I take the the data that is passed in the form data and place it within a subroutine which then joins the data with a comma so it can then be ran in the sql to display the results?

Here is the sql:

#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')
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"
);

And I would like the variable $spns to store the data that is taken from the form input below:

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};
}

But then the input from the form needs to be sent to a join function where I can place the SPNS in the correct format so the SELECT statement can run correctly. I'm not sure if what I have is correct with this subroutine but is this even close?

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  
}

choosenalpha

Light Poster

30 posts since Nov 2009

Reputation Points: 10

Solved Threads: 0

1 Year Ago

Please try the following oversimplified script for an example of how to format your spsns with commas.

#!/usr/bin/perl
use strict;
use warnings;
 
use CGI qw(:standard);

main();

sub main
{
####print "<<HEADER"; #Commented out. Causes "malformed header" error.

print header; #Let the CGI module print your header
print "<html><head>\n";   
print "<title>RED DRILL PAGE</title></head>\n";
print "<body>\n";
print "<h3>Red Drill Credit Exposure</h3>\n";
if (param('spns')){
    my $spns = param('spns');
    $spns = format_spn_string($spns);
    print "spns are: $spns";
}
else{
    show_form();#calling show_form subroutine
}
print"</body></html>\n";
}

sub show_form
{
        my $url = url;
        print qq{<form name="input" action=$url method="get">\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 format_spn_string{
    my $in = shift;
    my $out = join ', ', split /\s/, $in;
    return $out;
}

d5e5

Practically a Posting Shark

810 posts since Sep 2009

Reputation Points: 159

Solved Threads: 159

1 Year Ago

Thanks D5e5!! That worked for me, I placed the SQL and its respective variables within the if loop to execute the SQL. But can you possibly explain what the split command you posted below does exactly? I see the join which concatenates the string with commas, but what exactly does the split do?

my $out = join ', ', split /\s/, $in;

choosenalpha

Light Poster

30 posts since Nov 2009

Reputation Points: 10

Solved Threads: 0

1 Year Ago

split splits a string according to a pattern that separates what we want to be elements in a list and returns a list, if used in list context. Since the default is to split words separated by one or more whitespace characters, I could have taken the defaults for the split arguments, so that it would split the contents of the string in $_ (the default variable) on the default pattern. Here is a slightly improved, commented version of the subroutine, in a little script I used to test it from the command line.

#!/usr/bin/perl
use strict;
use warnings;

#The following works even when more than one space separates the words.
print format_spn_string('peas carrots     beets corn');

sub format_spn_string{
    $_ = shift; #Assign first (and only) subroutine argument to $_
    my @array = split; #Split string ($_ if unspecified) and assign list to @array
    my $out = join ', ', @array; #join elements of list. Separator is comma space
    return $out;
}

d5e5

Practically a Posting Shark

810 posts since Sep 2009

Reputation Points: 159

Solved Threads: 159

1 Year Ago

Thanks D5e5 :icon_biggrin: Your explanation is perfect, I really appreciate your help on this you are an excellent Perl Developer! I'll close out this request now.

Cheers1

split splits a string according to a pattern that separates what we want to be elements in a list and returns a list, if used in list context. Since the default is to split words separated by one or more whitespace characters, I could have taken the defaults for the split arguments, so that it would split the contents of the string in $_ (the default variable) on the default pattern. Here is a slightly improved, commented version of the subroutine, in a little script I used to test it from the command line.
#!/usr/bin/perl
use strict;
use warnings;

#The following works even when more than one space separates the words.
print format_spn_string('peas carrots     beets corn');

sub format_spn_string{
    $_ = shift; #Assign first (and only) subroutine argument to $_
    my @array = split; #Split string ($_ if unspecified) and assign list to @array
    my $out = join ', ', @array; #join elements of list. Separator is comma space
    return $out;
}

choosenalpha

Light Poster

30 posts since Nov 2009

Reputation Points: 10

Solved Threads: 0

This question has already been solved

You