jhawarmayank
asked on
Please Assist as soon as possible
hi
i am running the queries but i dont know what happens
the connection is lost or the buffer is filled in
these are all the query message i am getting
mssql_free_result(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
mssql_free_result(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_free_result(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_free_result(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_free_result(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_free_result(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_free_result(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_free_result(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_free_result(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
mssql_query() [function.mssql-query]: Query failed in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Populating ECN fees for Prime Accounts
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Populating SRC from all of Temp
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_fetch_row(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_fetch_row(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Please help me as i have to submit this report to my client tomorrow
the complete code for the file is attached below
i am running the queries but i dont know what happens
the connection is lost or the buffer is filled in
these are all the query message i am getting
mssql_free_result(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
mssql_free_result(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_free_result(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_free_result(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_free_result(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_free_result(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_free_result(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_free_result(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_free_result(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
mssql_query() [function.mssql-query]: Query failed in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Populating ECN fees for Prime Accounts
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Populating SRC from all of Temp
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_fetch_row(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_fetch_row(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Please help me as i have to submit this report to my client tomorrow
the complete code for the file is attached below
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<style type="text/css">
.style1 {
background-color: #C0C0C0;
}
.style2 {
background-color: #FFCCCC;
}
.style3 {
background-color: #99FFCC;
}
.style4 {
background-color: #CC99FF;
}
.style5 {
background-color: #CC99FF;
}
</style>
</head>
<body>
<?php
include("connect.php");
$mo1 = $_POST['select1'];
$dy1 = $_POST['select2'];
$yr1 = $_POST['select3'];
$mo2 = $_POST['select4'];
$dy2 = $_POST['select5'];
$yr2 = $_POST['select6'];
echo"<input type=\"button\" value=\"Print\" onClick=\"window.print()\"><br>";
echo "<h1>Sales Comission Report</h1>";
echo "<br><h3>Time Period $mo1-$dy1-$yr1 to $mo2-$dy2-$yr2<br>";
echo "<br><table align='center' border='1' cellpadding='1'>";
echo "<tr><td align='center' border='1' cellpadding='1'>Account Number</td><td align='center' border='1' cellpadding='1'>Fund Name</td><td align='center' border='1' cellpadding='1'>Equity</td>".
"<td align='center' border='1' cellpadding='1'>Options</td><td align='center' border='1' cellpadding='1'>Futures</td><td align='center' border='1' cellpadding='1'>ID</td>".
"<td align='center' border='1' cellpadding='1'>Net Equity</td><td align='center' border='1' cellpadding='1'>Interest Financing</td><td align='center' border='1' cellpadding='1'>Short Stock Financing</td>".
"<td align='center' border='1' cellpadding='1'>Total Commission</td><td align='center' border='1' cellpadding='1'>Options Clearence</td><td align='center' border='1' cellpadding='1'>ID Tickets</td>".
"<td align='center' border='1' cellpadding='1'>Futures Revenue</td><td align='center' border='1' cellpadding='1'>ALGO Credit</td><td align='center' border='1' cellpadding='1'>Misc.</td>".
"<td align='center' border='1' cellpadding='1'>Total Before Soft Dollar</td><td align='center' border='1' cellpadding='1'>Soft Dollar</td><td align='center' border='1' cellpadding='1'>Total Revenue</td>".
"<td align='center' border='1' cellpadding='1'>Ticket Charges</td><td align='center' border='1' cellpadding='1'>ID Ticket Charges</td><td align='center' border='1' cellpadding='1'>Brokerage(REDI)</td>".
"<td align='center' border='1' cellpadding='1'>Brokerage(IQ)</td><td align='center' border='1' cellpadding='1'>Pipeline</td><td align='center' border='1' cellpadding='1'>Options</td>".
"<td align='center' border='1' cellpadding='1'>Options Brokerage</td><td align='center' border='1' cellpadding='1'>Futures</td><td align='center' border='1' cellpadding='1'>ECN Fees</td>".
"<td align='center' border='1' cellpadding='1'>Software/MKT Data</td><td align='center' border='1' cellpadding='1'>ALGO Debit</td><td align='center' border='1' cellpadding='1'>Misc.</td>".
"<td align='center' border='1' cellpadding='1'>Total Expenses</td><td align='center' border='1' cellpadding='1'>Income from Trading</td><td align='center' border='1' cellpadding='1'>Income from Financing</td>".
"<td align='center' border='1' cellpadding='1'>Total Income</td><td align='center' border='1' cellpadding='1'>Type</td><td align='center' border='1' cellpadding='1'>Sales Rep</td></tr>";
$oldvar=5;
$newvar='dele';
while($oldvar!=0){
switch($newvar){
case 'dele' : echo "<br>delete everything";
$qdel = "delete from dbo.temp DBCC CHECKIDENT('dbo.temp', reseed, 0)";
$rdel = mssql_query($qdel);
$qdel2 = "delete from src";
$rdel2 = mssql_query($qdel2);
$qdel3 = "delete from bofa";
$rdel3 = mssql_query($qdel3);
$qdel4 = "delete from income";
$rdel4 = mssql_query($qdel4);
$qdel5 = "delete from expense";
$rdel5 = mssql_query($qdel5);
$newvar='ieb';
$cln1="DBCC FREEPROCCACHE";
$cln2=" DBCC DROPCLEANBUFFERS";
$rslcln1 =mssql_query($cln1);
$rslcln2 =mssql_query($cln2);
break;
case 'ieb' : echo "<br>populate Income,Expense and Bofa Table";
$qpieb1 = "insert dbo.bofa(acc_no) select distinct(b.account_no) from bofa_table_1 b where (b.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2')";
$rpieb1 = mssql_query($qpieb1);
$qpieb2 = "insert dbo.income(account_no) select right(t5.trailer_1,8) from Table_5 t5 where ((t5.account_no like '4w7f%') and (t5.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$rpieb2 = mssql_query($qpieb2);
$qpieb3 = "insert dbo.expense(account_no) select right(t5.trailer_1,8) from Table_5 t5 where ((t5.account_no like '4W7G%') and (t5.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$rpieb3 = mssql_query($qpieb3);
$newvar = 'updateieb';
$cln1="DBCC FREEPROCCACHE";
$cln2=" DBCC DROPCLEANBUFFERS";
$rslcln1 =mssql_query($cln1);
$rslcln2 =mssql_query($cln2);
break;
case 'updateieb' : echo "<br>Update Income,Expense and Bofa Table";
$quieb1 = "update bofa set acc_no=substring(acc_no,1,4) from bofa where (acc_no like'4%' or acc_no like '7%')";
$ruieb1 = mssql_query($quieb1);
$quieb2 = "update income set account_no=substring(account_no,1,4) from income where (account_no like'4%' or account_no like '7%')";
$ruieb2 = mssql_query($quieb2);
$quieb3 = "delete from income where account_no like '74TV'";
$ruieb3 = mssql_query($quieb3);
$quieb4 = "update expense set account_no=substring(account_no,1,4) from expense where (account_no like'4%' or account_no like '7%')";
$ruqib4 = mssql_query($quieb4);
$quieb5 = "delete from expense where account_no = '74TV'";
$ruieb5 = mssql_query($quieb5);
$newvar = 'pvdvp';
$cln1="DBCC FREEPROCCACHE";
$cln2=" DBCC DROPCLEANBUFFERS";
$rslcln1 =mssql_query($cln1);
$rslcln2 =mssql_query($cln2);
break;
case 'pvdvp' : echo "<br>Populate Volume for DVP";
$qsel1 = "select cl.account_no from dbo.acc cl where cl.account_no like '5%5'";
$rsel1 = mssql_query($qsel1);
while($rowsel1 = mssql_fetch_row($rsel1))
{
$acci = $rowsel1['account_no'];
$q_ins1 = "insert dbo.temp(v_equity,v_options,v_futures,v_id,acc_no) select sum(abs(cast(t1.trade_quantity as decimal(13,5)))),'0','0','0','$acci' from dbo.Table_1 t1,dbo.Table_5 t5 where ((t1.account_no like '$acci') and (t1.row_count = t5.row_count) and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins1 = mssql_query($q_ins1);
mssql_free_result($r_ins1);
}
$newvar= 'pidvp';
$cln1="DBCC FREEPROCCACHE";
$cln2=" DBCC DROPCLEANBUFFERS";
$rslcln1 =mssql_query($cln1);
$rslcln2 =mssql_query($cln2);
break;
case 'pidvp' : echo "<br>Populate Income columns for DVP";
$qsel2 = "select cl.account_no from dbo.acc cl where cl.account_no like '5%5'";
$rsel2 = mssql_query($qsel2);
while($rowsel2 = mssql_fetch_row($rsel2))
{
$acci = $rowsel2['account_no'];
$q_ins2 = "insert dbo.temp(i_net_equity,acc_no) select '0','$acci' from dbo.Table_1 t1,dbo.Table_5 t5 where (( t1.account_no like '$acci') and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins2 = mssql_query($q_ins2);
$q_ins3 = "insert dbo.temp(i_interest_financing,acc_no) select '0','$acci' from dbo.Table_1 t1,dbo.Table_5 t5 where (( t1.account_no like '$acci') and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins3 = mssql_query( $q_ins3 );
$q_ins4 = "insert dbo.temp(i_short_stock_financing,acc_no) select '0','$acci' from dbo.Table_1 t1,dbo.Table_5 t5 where (( t1.account_no like '$acci') and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins4 = mssql_query( $q_ins4 );
$q_ins5 = "insert dbo.temp(i_total_commissions,acc_no) select sum(abs(cast(t1.trade_quantity as decimal(13,5)))),'$acci' from dbo.Table_1 t1,dbo.Table_5 t5 where (( t5.account_no like '$acci') and ((t5.journel_code = 'CLR') or (t5.journel_code = 'BRK')) and (t5.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins5 = mssql_query( $q_ins5 );
$q_ins6 = "insert dbo.temp(i_options_clearance,acc_no) select sum(abs(cast(t1.trade_quantity as decimal(13,5)))),'$acci' from dbo.Table_1 t1,dbo.Table_5 t5 where ((t1.account_no like '$acci') and (t5.journel_code = 'OPT') and (t1.row_count = t5.row_count ) and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins6 = mssql_query( $q_ins6 );
$q_ins7 = "insert dbo.temp(i_id_tickets,acc_no) select sum(abs(cast(t1.trade_quantity as decimal(13,5)))),'$acci' from dbo.Table_1 t1,dbo.Table_5 t5 where ((t1.account_no like '$acci') and (t5.journel_code = 'IDC') and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins7 = mssql_query( $q_ins7 );
$q_ins8 = "insert dbo.temp(i_futures_revenue,i_algo_credit,i_misc,i_soft_dollar,acc_no) select m.software_market_data,'0',m.misc,m.soft_dollar,'$acci' from dbo.manual_data m where ((m.account_no like '$acci') and (m.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins8 = mssql_query( $q_ins8 );
$q_ins9 = "insert dbo.temp(tmp.i_total_before_soft_dollar,acc_no) select sum(abs(cast(tmp.i_total_commissions as decimal(13, 5)))+ abs(cast(tmp.i_options_clearance as decimal(13,5)))+ abs(cast(tmp.i_id_tickets as decimal(13,5))) + abs(cast(tmp.i_futures_revenue as decimal(15,5)))+ abs(cast(tmp.i_algo_credit as decimal(15,5)))+ abs(cast(tmp.i_misc as decimal(15,5)))),'$acci' from dbo.temp tmp where (acc_no like '$acci')";
$r_ins9 = mssql_query( $q_ins9 );
}
mssql_free_result($r_ins2);
mssql_free_result($r_ins3);
mssql_free_result($r_ins4);
mssql_free_result($r_ins5);
mssql_free_result($r_ins6);
mssql_free_result($r_ins7);
mssql_free_result($r_ins8);
mssql_free_result($r_ins9);
$newvar= 'pedvp';
$cln1="DBCC FREEPROCCACHE";
$cln2=" DBCC DROPCLEANBUFFERS";
$rslcln1 =mssql_query($cln1);
$rslcln2 =mssql_query($cln2);
break;
case 'pedvp' : echo "<br>Populate Expense columns for DVP";
$qsel2 = "select cl.account_no from dbo.expense cl where cl.account_no like '5%5'";
$rsel2 = mssql_query($qsel2);
while($rowsel2 = mssql_fetch_row($rsel2))
{
$acce = $row['account_no'];
$q_ins9 = "insert dbo.temp(e_ticket_charges,acc_no) select sum(abs(cast(t1.trade_quantity as decimal(13,5)))),'$acce' from dbo.Table_1 t1,dbo.Table_5 t5 where ((t1.account_no like '$acce') and (t1.row_count = t5.row_count ) and (t5.journel_code = 'CLR') and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins9 = mssql_query( $q_ins9 );
$q_ins10 = "insert dbo.temp(e_id_ticket_charges,acc_no) select sum(abs(cast(t1.trade_quantity as decimal(13,5)))),'$acce' from dbo.Table_1 t1,dbo.Table_5 t5 where ((t1.account_no like '$acce') and (t5.journel_code = 'IDC') and (t1.row_count = t5.row_count ) and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins10 = mssql_query( $q_ins10 );
$q_ins11 = "insert dbo.temp(e_brokerage_redi,acc_no) select abs(cast(t1.trade_quantity as decimal(13,5)))*(.001),'$acce' from dbo.Table_1 t1,dbo.Table_5 t5 where (( t1.account_no like '$acce') and (t1.row_count = t5.row_count ) and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins11 = mssql_query( $q_ins11 );
$q_ins12 = "insert dbo.temp(e_brokerage_iq,acc_no) select sum(abs(cast(t1.trade_quantity as decimal(13,5))))*(.0011),'$acce' from dbo.Table_1 t1,dbo.Table_5 t5 where (( t1.account_no like '$acce') and (t1.row_count = t5.row_count ) and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins12 = mssql_query( $q_ins12 );
$q_ins13 = "insert dbo.temp(e_options,acc_no) select sum(abs(cast(t1.trade_quantity as decimal(13,5)))),'$acce' from dbo.Table_1 t1,dbo.Table_5 t5 where (( t1.account_no like '$acce') and (t5.journel_code ='OPT') and (t1.row_count = t5.row_count ) and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins13 = mssql_query( $q_ins13 );
$q_ins14 = "insert dbo.temp(e_options_brokerage,acc_no) select sum(abs(cast(t1.trade_quantity as decimal(13,5)))),'$acce' from dbo.Table_1 t1,dbo.Table_5 t5 where (( t1.account_no like '$acce') and (t5.journel_code ='OBR') and (t1.row_count = t5.row_count ) and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins14 = mssql_query( $q_ins14 );
$q_ins15 = "insert dbo.temp(e_futures,acc_no) select '0','$acce' from dbo.Table_1 t1where (( t1.account_no like '$acce' ) and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins15 = mssql_query( $q_ins15 );
$q_ins30 = "insert dbo.temp(tmp.e_software_mkt_data,tmp.e_misc,acc_no) select avg(m.software_market_data),avg(m.misc),'$acce' from dbo.manual_data m where (( m.account_no like '$acce') and (m.todays_date between '$mo1-$dy1-$yr1' and '$mo2/$y2/$yr2'))";
$r_ins30 = mssql_query( $q_ins30 );
$q_ins31="select sum(abs(cast(tmp.i_total_revenue as decimal(15,5)))) - sum(abs(cast(tmp.e_total_expenses as decimal(15,5)))) from dbo.client_table cl, dbo.temp tmp where tmp.acc_no like cl.account_no";
$r_ins31 = mssql_query( $q_ins31 );
}
mssql_free_result($r_ins9);
mssql_free_result($r_ins10);
mssql_free_result($r_ins11);
mssql_free_result($r_ins12);
mssql_free_result($r_ins13);
mssql_free_result($r_ins14);
mssql_free_result($r_ins15);
mssql_free_result($r_ins30);
mssql_free_result($r_ins31);
$newvar='pecndvp';
$cln1="DBCC FREEPROCCACHE";
$cln2=" DBCC DROPCLEANBUFFERS";
$rslcln1 =mssql_query($cln1);
$rslcln2 =mssql_query($cln2);
break;
case 'pecndvp' : echo "<br>Populate ECN fees for DVP";
$qsel3="select acc_no from dbo.bofa b where b.acc_no like '5%5'";
$rsel3 = mssql_query($qsel2);
while($rowsel3 = mssql_fetch_row($rsel3))
{
$acce=$rowsel3['acc_no'];
$q_ins16 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(.0026),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce') and (bofa.route ='ARCA') and (bofa.liquidity ='R') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins16 = mssql_query( $q_ins16 );
$q_ins17 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(.0030),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce' ) and (bofa.route ='ARCA') and (bofa.liquidity ='X') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins17 = mssql_query( $q_ins17 );
$q_ins18 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(-.0020),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce') and (bofa.route ='ARCA') and (bofa.liquidity ='A') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins18 = mssql_query( $q_ins18 );
$q_ins19 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(.0030),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce') and (bofa.route ='ISLD') and (bofa.liquidity ='R') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins19 = mssql_query( $q_ins19 );
$q_ins20 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(.0040),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce') and (bofa.route ='ISLD') and (bofa.liquidity ='X') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins20 = mssql_query( $q_ins20 );
$q_ins21 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(-.0020),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce') and (bofa.route ='ISLD') and (bofa.liquidity ='A') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins21 = mssql_query( $q_ins21 );
$q_ins22 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(.0030),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce') and (bofa.route ='BATS') and (bofa.liquidity ='R') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins22 = mssql_query( $q_ins22 );
$q_ins23 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(.0030),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce') and (bofa.route ='BATS') and (bofa.liquidity ='X') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins23 = mssql_query( $q_ins23 );
$q_ins24 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(-.0020),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce') and (bofa.route ='BATS') and (bofa.liquidity ='A') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins24 = mssql_query( $q_ins24 );
$q_ins25 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(.0026),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce') and (bofa.route ='EDGE') and (bofa.liquidity ='R') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins25 = mssql_query( $q_ins25 );
$q_ins26 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(.0029),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce') and (bofa.route ='EDGE') and (bofa.liquidity ='X') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins26 = mssql_query( $q_ins26 );
$q_ins27 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(-.0025),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce') and (bofa.route ='EDGE') and (bofa.liquidity ='A') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins27 = mssql_query( $q_ins27 );
$q_ins28 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(.0026),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce') and (bofa.route ='NYSE') and (bofa.liquidity ='R') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins28 = mssql_query( $q_ins28 );
$q_ins29 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(.0026),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce') and (bofa.route ='AMEX') and (bofa.liquidity ='R') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins29 = mssql_query( $q_ins29 );
}
mssql_free_result($r_ins16);
mssql_free_result($r_ins17);
mssql_free_result($r_ins18);
mssql_free_result($r_ins19);
mssql_free_result($r_ins20);
mssql_free_result($r_ins21);
mssql_free_result($r_ins22);
mssql_free_result($r_ins23);
mssql_free_result($r_ins24);
mssql_free_result($r_ins25);
mssql_free_result($r_ins26);
mssql_free_result($r_ins27);
mssql_free_result($r_ins28);
mssql_free_result($r_ins29);
$newvar='pvpb';
$cln1="DBCC FREEPROCCACHE";
$cln2=" DBCC DROPCLEANBUFFERS";
$rslcln1 =mssql_query($cln1);
$rslcln2 =mssql_query($cln2);
break;
case 'pvpb' : echo "<br>Populating Volumes for Prime Accounts";
$qsel4 = "select cl.account_no from dbo.acc cl where cl.account_no like '4%' or cl.account_no like '7%'";
$rsel4 = mssql_query($qsel4);
while($rowsel4 = mssql_fetch_row($rsel4))
{
$acci = $row['account_no'];
$q_ins1 = "insert dbo.temp(v_equity,acc_no) select sum(abs(cast(t1.trade_quantity as decimal(13,5)))),'$acci' from dbo.Table_1 t1,dbo.Table_5 t5 where ((t1.account_no = '".$acci."1209') and (t1.row_count = t5.row_count ) and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins1 = mssql_query( $q_ins1 );
$q_ins2 = "insert dbo.temp(v_options,acc_no) select sum(abs(cast(t1.trade_quantity as decimal(13,5)))),'$acci' from dbo.Table_1 t1,dbo.Table_5 t5 where ((t1.account_no = '".$acci."0409') and (t1.row_count = t5.row_count ) and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins1 = mssql_query( $q_ins1 );
$q_ins3 = "insert dbo.temp(v_futures,acc_no) select sum(abs(cast(t1.trade_quantity as decimal(13,5)))),'$acci' from dbo.Table_1 t1,dbo.Table_5 t5 where ((t1.account_no = '".$acci."F909') and (t1.row_count = t5.row_count ) and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins3 = mssql_query( $q_ins3 );
$q_ins4 = "insert dbo.temp(v_id,acc_no) select '0','$acci' from dbo.Table_1 t1,dbo.Table_5 t5 where ((t1.account_no like '$acci') and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins4 = mssql_query( $q_ins4 );
}
mssql_free_result($r_ins1);
mssql_free_result($r_ins2);
mssql_free_result($r_ins3);
mssql_free_result($r_ins4);
$newvar='pipb';
$cln1="DBCC FREEPROCCACHE";
$cln2=" DBCC DROPCLEANBUFFERS";
$rslcln1 =mssql_query($cln1);
$rslcln2 =mssql_query($cln2);
break;
case 'pipb' : echo "<br>Populating Income for Prime Accounts";
$qsel5 = "select cl.account_no from dbo.income cl where cl.account_no like '4%' or account_no like '7%'";
$rsel5 = mssql_query($qsel5);
while ($rowsel5 = mssql_fetch_row($rsel5))
{
$acci = $row['account_no'];
$q_ins5 = "insert dbo.temp(i_net_equity,acc_no) select '0','$acci' from dbo.Table_1 t1,dbo.Table_5 t5 where (( t1.account_no like '$acci%') and (t1.row_count = t5.row_count ) and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins5 = mssql_query( $q_ins5 );
$q_ins6 = "insert dbo.temp(i_interest_financing,acc_no) select '0','$acci' from dbo.Table_1 t1,dbo.Table_5 t5 where (( t1.account_no like '$acci%') and (t1.row_count = t5.row_count ) and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins6 = mssql_query( $q_ins6 );
$q_ins7 = "insert dbo.temp(i_short_stock_financing,acc_no) select '0','$acci' from dbo.Table_1 t1,dbo.Table_5 t5 where (( t1.account_no like '$acci%') and (t1.row_count = t5.row_count ) and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins7 = mssql_query( $q_ins7 );
$q_ins8 = "insert dbo.temp(i_total_commissions,acc_no) select sum(abs(cast(t1.trade_quantity as decimal(13,5)))),'$acci' from dbo.Table_1 t1,dbo.Table_5 t5 where (( t1.account_no like '$acci%') and (t1.row_count = t5.row_count ) and (t5.journel_code ='CLR' or t5.journel_code='BRK') and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins8 = mssql_query( $q_ins8 );
$q_ins9 = "insert dbo.temp(i_options_clearance,acc_no) select sum(abs(cast(t1.trade_quantity as decimal(13,5)))),'$acci' from dbo.Table_1 t1,dbo.Table_5 t5 where (( t1.account_no like '$acci%') and (t5.journel_code ='OPT') and (t1.row_count = t5.row_count ) and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins9 = mssql_query( $q_ins9 );
$q_ins10 = "insert dbo.temp(i_id_tickets,acc_no) select sum(abs(cast(t1.trade_quantity as decimal(13,5)))),'$acci' from dbo.Table_1 t1,dbo.Table_5 t5 where (( t1.account_no like '$acci%') and (t1.row_count = t5.row_count ) and (t5.journel_code ='IDC') and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins10 = mssql_query( $q_ins10 );
$q_ins11 = "insert dbo.temp(i_futures_revenue,i_algo_credit,i_misc,i_soft_dollar,acc_no) select (m.software_market_data),'0',(m.misc),(m.soft_dollar),'$acci' from dbo.Table_1 t1,dbo.Table_5 t5 ,manual_data m where ((m.account_no = '$acci') and (m.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins11 = mssql_query( $q_ins11 );
$q_ins12 = "insert dbo.temp(tmp.i_total_before_soft_dollar) select sum(abs(cast(tmp.i_total_commissions as decimal(13, 5)))+ cast(tmp.i_options_clearance as decimal(13,5))+ cast(tmp.i_id_tickets as decimal(13,5)) + cast(tmp.i_futures_revenue as decimal(15,5))+ cast(tmp.i_algo_credit as decimal(15,5))+ cast(tmp.i_misc as decimal(15,5))) from dbo.temp tmp where acc_no ='$acci'";
$r_ins12 = mssql_query( $q_ins12 );
}
mssql_free_result($r_ins5);
mssql_free_result($r_ins6);
mssql_free_result($r_ins7);
mssql_free_result($r_ins8);
mssql_free_result($r_ins9);
mssql_free_result($r_ins10);
mssql_free_result($r_ins11);
mssql_free_result($r_ins12);
$newvar='pepb';
$cln1="DBCC FREEPROCCACHE";
$cln2=" DBCC DROPCLEANBUFFERS";
$rslcln1 =mssql_query($cln1);
$rslcln2 =mssql_query($cln2);
break;
case 'pepb' : echo "<br>Populating Expenses for Prime Accounts";
$qsel6="select cl.account_no from dbo.expense cl where cl.account_no like '4%' or account_no like '7%'";
$rsel6=mssql_query($qsel6);
while($row = mssql_fetch_row($rsel6))
{
$acce = $row['account_no'];
$q_ins9 ="insert dbo.temp(e_ticket_charges,acc_no) select sum(abs(cast(t1.trade_quantity as decimal(13,5)))),'$acce' from dbo.Table_1 t1,dbo.Table_5 t5 where ( (t1.account_no like '$acce%') and (t5.journel_code ='CLR') and (t1.row_count = t5.row_count ) and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins9 = mssql_query( $q_ins9 );
$q_ins10 ="insert dbo.temp(e_id_ticket_charges,acc_no) select sum(abs(cast(t1.trade_quantity as decimal(13,5)))),'$acce' from dbo.Table_1 t1,dbo.Table_5 t5 where (( t1.account_no like '$acce%') and (t5.journel_code ='IDC') and (t1.row_count=t5.row_count) and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins10 = mssql_query( $q_ins10 );
$q_ins11 ="insert dbo.temp(e_brokerage_redi,acc_no) select sum(abs(cast(t1.trade_quantity as decimal(13,5))))*.001,'$acce' from dbo.Table_1 t1,dbo.Table_5 t5 where (( t1.account_no like '$acce%') and (t1.row_count = t5.row_count ) and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins11 = mssql_query( $q_ins11 );
$q_ins12 ="insert dbo.temp(e_brokerage_iq,acc_no) select sum(abs(cast(t1.trade_quantity as decimal(13,5))))*.0011,'$acce' from dbo.Table_1 t1,dbo.Table_5 t5 where (( t1.account_no like '$acce%') and (t1.row_count = t5.row_count ) and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins12 = mssql_query( $q_ins12 );
$q_ins13 ="insert dbo.temp(e_options,acc_no) select sum(abs(cast(t1.trade_quantity as decimal(13,5)))),'$acce' from dbo.Table_1 t1,dbo.Table_5 t5 where (( t1.account_no like '$acce%') and (t1.row_count = t5.row_count ) and (t5.journel_code ='OPT') and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins13 = mssql_query( $q_ins13 );
$q_ins14 ="insert dbo.temp(e_options_brokerage,acc_no) select sum(abs(cast(t1.trade_quantity as decimal(13,5)))),'$acce' from dbo.Table_1 t1,dbo.Table_5 t5 where (( t1.account_no like '$acce%') and (t1.row_count = t5.row_count ) and (t5.journel_code ='OBR') and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins14 = mssql_query( $q_ins14 );
$q_ins15 ="insert dbo.temp(e_futures,acc_no) select '0','$acce' from dbo.Table_1 t1,dbo.Table_5 t5 where (( t1.account_no like '$acce%') and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins15 = mssql_query( $q_ins15 );
$q_ins30="insert dbo.temp(tmp.e_software_mkt_data,tmp.e_misc,acc_no) select avg(m.software_market_data),avg(m.misc),'$acce' from dbo.Table_1 t1,dbo.Table_5 t5 ,dbo.manual_data m where ((( t5.account_no = '$acce' and m.account_no = '$acce') and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2') and (m.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2')))";
$r_ins30 = mssql_query( $q_ins30 );
$q__ins31="select sum(abs(cast(tmp.i_total_revenue as decimal(15,5)))) - sum(abs(cast(tmp.e_total_expenses as decimal(15,5)))) from dbo.client_table cl, dbo.temp tmp-- where tmp.acc_no=cl.account_no";
$r_ins31 = mssql_query( $q_ins31 );
}
mssql_free_result($r_ins9);
mssql_free_result($r_ins10);
mssql_free_result($r_ins11);
mssql_free_result($r_ins12);
mssql_free_result($r_ins13);
mssql_free_result($r_ins14);
mssql_free_result($r_ins15);
mssql_free_result($r_ins30);
mssql_free_result($r_ins31);
$newvar='pecnpb';
$cln1="DBCC FREEPROCCACHE";
$cln2=" DBCC DROPCLEANBUFFERS";
$rslcln1 =mssql_query($cln1);
$rslcln2 =mssql_query($cln2);
break;
case 'pecnpb' : echo "<br>Populating ECN fees for Prime Accounts";
$qsel7="select acc_no from dbo.bofa b where b.acc_no like '7%' or b.acc_no like '4%'";
$rsel7 = mssql_query($qsel7);
while($rowsel7 = mssql_fetch_row($rsel7))
{
$acce=$rr['acc_no'];
$q_ins16 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(.0026),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce%') and (bofa.route ='ARCA') and (bofa.liquidity ='R') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins16 = mssql_query( $q_ins16 );
$q_ins17 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(.0030),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce%' ) and (bofa.route ='ARCA') and (bofa.liquidity ='X') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins17 = mssql_query( $q_ins17 );
$q_ins18 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(-.0020),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce%') and (bofa.route ='ARCA') and (bofa.liquidity ='A') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins18 = mssql_query( $q_ins18 );
$q_ins19 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(.0030),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce%') and (bofa.route ='ISLD') and (bofa.liquidity ='R') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins19 = mssql_query( $q_ins19 );
$q_ins20 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(.0040),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce%') and (bofa.route ='ISLD') and (bofa.liquidity ='X') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins20 = mssql_query( $q_ins20 );
$q_ins21 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(-.0020),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce%') and (bofa.route ='ISLD') and (bofa.liquidity ='A') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins21 = mssql_query( $q_ins21 );
$q_ins22 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(.0030),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce%') and (bofa.route ='BATS') and (bofa.liquidity ='R') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins22 = mssql_query( $q_ins22 );
$q_ins23 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(.0030),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce%') and (bofa.route ='BATS') and (bofa.liquidity ='X') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins23 = mssql_query( $q_ins23 );
$q_ins24 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(-.0020),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce%') and (bofa.route ='BATS') and (bofa.liquidity ='A') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins24 = mssql_query( $q_ins24 );
$q_ins25 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(.0026),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce%') and (bofa.route ='EDGE') and (bofa.liquidity ='R') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins25 = mssql_query( $q_ins25 );
$q_ins26 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(.0029),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce%') and (bofa.route ='EDGE') and (bofa.liquidity ='X') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins26 = mssql_query( $q_ins26 );
$q_ins27 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(-.0025),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce%') and (bofa.route ='EDGE') and (bofa.liquidity ='A') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins27 = mssql_query( $q_ins27 );
$q_ins28 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(.0026),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce%') and (bofa.route ='NYSE') and (bofa.liquidity ='R') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins28 = mssql_query( $q_ins28 );
$q_ins29 = "insert dbo.temp(e_ecn_fees,acc_no) select abs(cast(bofa.exec_share as decimal(13,5)))*(.0026),'$acce' from dbo.bofa_table_1 bofa where (( bofa.account_no like '$acce%') and (bofa.route ='AMEX') and (bofa.liquidity ='R') and (bofa.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins29 = mssql_query( $q_ins29 );
}
mssql_free_result($r_ins16);
mssql_free_result($r_ins17);
mssql_free_result($r_ins18);
mssql_free_result($r_ins19);
mssql_free_result($r_ins20);
mssql_free_result($r_ins21);
mssql_free_result($r_ins22);
mssql_free_result($r_ins23);
mssql_free_result($r_ins24);
mssql_free_result($r_ins25);
mssql_free_result($r_ins26);
mssql_free_result($r_ins27);
mssql_free_result($r_ins28);
mssql_free_result($r_ins29);
$newvar='tempsrc';
$cln1="DBCC FREEPROCCACHE";
$cln2=" DBCC DROPCLEANBUFFERS";
$rslcln1 =mssql_query($cln1);
$rslcln2 =mssql_query($cln2);
break;
case 'tempsrc' : echo "<br>Populating SRC from all of Temp";
$qsel8="select account_no,fund_name,rep,type from dbo.acc cl";
$rsel8=mssql_query($qsel8);
while($rowsel8 = mssql_fetch_row($rsel8))
{
$ac = $row['account_no'];
$fname =$row['fund_name'];
$sales=$row['rep'];
$type=$row['type'];
$query_final="insert into dbo.src(acc_no ,fund_name ,v_equity ,v_options ,v_futures ,v_id ,i_net_equity ,i_interest_financing ,i_short_stock_financing ,i_total_commissions ,i_options_clearance ,i_id_tickets ,i_futures_revenue ,i_algo_credit ,i_misc ,i_total_before_soft_dollar ,i_soft_dollar ,i_total_revenue ,e_ticket_charges ,e_id_ticket_charges ,e_brokerage_redi ,e_brokerage_iq ,e_pipeline ,e_options ,e_options_brokerage ,e_futures ,e_ecn_fees ,e_software_mkt_data ,e_algo_debit ,e_misc ,e_total_expenses ,t_income_from_trading ,t_income_from_financing ,t_total_income ,o_type ,o_sales_rep) select '$ac','$fname' ,sum(abs(cast(t1.v_equity as decimal(13,5)))),sum(abs(cast(t1.v_options as decimal(13,5)))),sum(abs(cast(t1.v_futures as decimal(13,5)))),sum(abs(cast(t1.v_id as decimal(13,5)))),sum(abs(cast(t1.i_net_equity as decimal(13,5)))),sum(abs(cast(t1.i_interest_financing as decimal(13,5)))),sum(abs(cast(t1.i_short_stock_financing as decimal(13,5)))),sum(abs(cast(t1.i_total_commissions as decimal(13,5)))),sum(abs(cast(t1.i_options_clearance as decimal(13,5)))),sum(abs(cast(t1.i_id_tickets as decimal(13,5)))),sum(abs(cast(t1.i_futures_revenue as decimal(13,5)))),sum(abs(cast(t1.i_algo_credit as decimal(13,5)))),sum(abs(cast(t1.i_misc as decimal(13,5)))),sum(abs(cast(t1.i_total_before_soft_dollar as decimal(13,5)))),sum(abs(cast(t1.i_soft_dollar as decimal(13,5)))),sum(abs(cast(t1.i_total_revenue as decimal(13,5)))),sum(abs(cast(t1.e_ticket_charges as decimal(13,5)))),sum(abs(cast(t1.e_id_ticket_charges as decimal(13,5)))),sum(abs(cast(t1.e_brokerage_redi as decimal(13,5)))),sum(abs(cast(t1.e_brokerage_iq as decimal(13,5)))),sum(abs(cast(t1.e_pipeline as decimal(13,5)))),sum(abs(cast(t1.e_options as decimal(13,5)))),sum(abs(cast(t1.e_options_brokerage as decimal(13,5)))),sum(abs(cast(t1.e_futures as decimal(13,5)))),sum(abs(cast(t1.e_ecn_fees as decimal(13,5)))),sum(abs(cast(t1.e_software_mkt_data as decimal(13,5)))),sum(abs(cast(t1.e_algo_debit as decimal(13,5)))),sum(abs(cast(t1.e_misc as decimal(13,5)))),sum(abs(cast(t1.e_total_expenses as decimal(13,5)))),sum(abs(cast(t1.t_income_from_trading as decimal(13,5)))),sum(abs(cast(t1.t_income_from_financing as decimal(13,5)))),sum(abs(cast(t1.t_total_income as decimal(13,5)))),'$type','$sales' from dbo.temp t1 where (t1.acc_no='$ac')";
$result_final=mssql_query($query_final);
}
$newvar='src';
$cln1="DBCC FREEPROCCACHE";
$cln2=" DBCC DROPCLEANBUFFERS";
$rslcln1 =mssql_query($cln1);
$rslcln2 =mssql_query($cln2);
break;
case 'src' : echo "<br>Displaying SRC";
$qf="select * from dbo.src order by fund_name";
$rf=mssql_query($qf);
while($rowf=mssql_fetch_row($rf))
{
$acc_no=$rowf['acc_no'];
$fund_name=$rowf['fund_name'];
$v_equity=$rowf['v_equity'];
if($v_equity==""){$v_equity='0';}
$v_options=$rowf['v_options'];
if($v_options==""){$v_options='0';}
$v_futures=$rowf['v_futures'];
if($v_futures==""){$v_futures='0';}
$v_id=$rowf['v_id'];
if($v_id==""){$v_id='0';}
$i_net_equity=$rowf['i_net_equity'];
if($i_net_equity==""){$i_net_equity='0';}
$i_interest_financing=$rowf['i_interest_financing'];
if($i_interest_financing==""){$i_interest_financing='0';}
$i_short_stock_financing=$rowf['i_short_stock_financing'];
if($i_short_stock_financing==""){$i_short_stock_financing='0';}
$i_total_commissions=$rowf['i_total_commissions'];
if($i_total_commissions==""){$i_total_commissions='0';}
$i_options_clearance=$rowf['i_options_clearance'];
if($i_options_clearance==""){$i_options_clearance='0';}
$i_id_tickets=$rowf['i_id_tickets'];
if($i_id_tickets==""){$i_id_tickets='0';}
$i_futures_revenue=$rowf['i_futures_revenue'];
if($i_futures_revenue==""){$i_futures_revenue='0';}
$i_algo_credit=$rowf['i_algo_credit'];
if($i_algo_credit==""){$i_algo_credit='0';}
$i_misc=$rowf['i_misc'];
if($i_misc==""){$i_misc='0';}
$i_total_before_soft_dollar=$rowf['i_total_before_soft_dollar'];
if($i_total_before_soft_dollar==""){$i_total_before_soft_dollar='0';}
$i_soft_dollar=$rowf['i_soft_dollar'];
if($i_soft_dollar==""){$i_soft_dollar='0';}
$i_total_revenue=$rowf['i_total_revenue'];
if($i_total_revenue==""){$i_total_revenue='0';}
$e_ticket_charges=$rowf['e_ticket_charges'];
if($e_ticket_charges==""){$e_ticket_charges='0';}
$e_id_ticket_charges=$rowf['e_id_ticket_charges'];
if($e_id_ticket_charges==""){$e_id_ticket_charges='0';}
$e_brokerage_redi=$rowf['e_brokerage_redi'];
if($e_brokerage_redi==""){$e_brokerage_redi='0';}
$e_brokerage_iq=$rowf['e_brokerage_iq'];
if($e_brokerage_iq==""){$e_brokerage_iq='0';}
$e_pipeline=$rowf['e_pipeline'];
if($e_pipeline==""){$e_pipeline='0';}
$e_options=$rowf['e_options'];
if($e_options==""){$e_options='0';}
$e_options_brokerage=$rowf['e_options_brokerage'];
if($e_options_brokerage==""){$e_options_brokerage='0';}
$e_futures=$rowf['e_futures'];
if($e_futures==""){$e_futures='0';}
$e_ecn_fees=$rowf['e_ecn_fees'];
if($e_ecn_fees==""){$e_ecn_fees='0';}
$e_software_mkt_data=$rowf['e_software_mkt_data'];
if($e_software_mkt_data==""){$e_software_mkt_data='0';}
$e_algo_debit=$rowf['e_algo_debit'];
if($e_algo_debit==""){$e_algo_debit='0';}
$e_misc=$rowf['e_misc'];
if($e_misc==""){$e_misc='0';}
$e_total_expenses=$rowf['e_total_expenses'];
if($e_total_expenses==""){$e_total_expenses='0';}
$t_income_from_trading=$rowf['t_income_from_trading'];
if($t_income_from_trading==""){$t_income_from_trading='0';}
$t_income_from_financing=$rowf['t_income_from_financing'];
if($t_income_from_financing==""){$t_income_from_financing='0';}
$t_total_income=$rowf['t_total_income'];
if($t_total_income==""){$t_total_income='0';}
$o_type=$rowf['o_type'];
$o_sales_rep=$rowf['o_sales_rep'];
echo "<tr><td class\"style5\" align='center' border='1' cellpadding='1'>$acc_no </td><td class\"style5\" align='center' border='1' cellpadding='1'>$fund_name </td><td class\"style1\" align='center' border='1' cellpadding='1'>$v_equity </td><td class\"style1\" align='center' border='1' cellpadding='1'>$v_options </td><td class\"style1\" align='center' border='1' cellpadding='1'>$v_futures </td><td class\"style1\" align='center' border='1' cellpadding='1'>$v_id </td><td class=\"style2\" align='center' border='1' cellpadding='1'>$i_net_equity </td><td class=\"style2\" align='center' border='1' cellpadding='1'>$i_interest_financing </td><td class=\"style2\" align='center' border='1' cellpadding='1'>$i_short_stock_financing </td><td class=\"style2\" align='center' border='1' cellpadding='1'>$i_total_commissions </td><td class=\"style2\" align='center' border='1' cellpadding='1'>$i_options_clearance </td><td class=\"style2\" align='center' border='1' cellpadding='1'>$i_id_tickets </td><td class=\"style2\" align='center' border='1' cellpadding='1'>$i_futures_revenue </td><td class=\"style2\" align='center' border='1' cellpadding='1'>$i_algo_credit </td><td class=\"style2\" align='center' border='1' cellpadding='1'>$i_misc </td><td class=\"style2\" align='center' border='1' cellpadding='1'>$i_total_before_soft_dollar </td><td class=\"style2\" align='center' border='1' cellpadding='1'>$i_soft_dollar </td><td class=\"style2\" align='center' border='1' cellpadding='1'>$i_total_revenue </td><td class=\"style3\" align='center' border='1' cellpadding='1'>$e_ticket_charges </td><td class=\"style3\" align='center' border='1' cellpadding='1'>$e_id_ticket_charges </td><td class=\"style3\" align='center' border='1' cellpadding='1'>$e_brokerage_redi </td><td class=\"style3\" align='center' border='1' cellpadding='1'>$e_brokerage_iq </td><td class=\"style3\" align='center' border='1' cellpadding='1'>$e_pipeline </td><td class=\"style3\" align='center' border='1' cellpadding='1'>$e_options </td><td class=\"style3\" align='center' border='1' cellpadding='1'>$e_options_brokerage </td><td class=\"style3\" align='center' border='1' cellpadding='1'>$e_futures </td><td class=\"style3\" align='center' border='1' cellpadding='1'>$e_ecn_fees </td><td class=\"style3\" align='center' border='1' cellpadding='1'>$e_software_mkt_data </td><td class=\"style3\" align='center' border='1' cellpadding='1'>$e_algo_debit </td><td class=\"style3\" align='center' border='1' cellpadding='1'>$e_misc </td><td class=\"style3\" align='center' border='1' cellpadding='1'>$e_total_expenses </td><td class=\"style4\" align='center' border='1' cellpadding='1'>$t_income_from_trading </td><td class=\"style4\" align='center' border='1' cellpadding='1'>$t_income_from_financing </td><td class=\"style4\" align='center' border='1' cellpadding='1'>$t_total_income </td><td class=\"style5\" align='center' border='1' cellpadding='1'>$o_type </td><td class=\"style5\" align='center' border='1' cellpadding='1'>$o_sales_rep </td></tr>";
}
$oldvar=0;
$newvar='abc';
$cln1="DBCC FREEPROCCACHE checkpoint";
$cln2=" DBCC DROPCLEANBUFFERS";
$rslcln1 =mssql_query($cln1);
$rslcln2 =mssql_query($cln2);
break;
default : break;
}
}
$q1_shrink = "ALTER DATABASE alaris SET RECOVERY SIMPLE";
$q2_shrink = "DBCC SHRINKFILE (alaris_log, 100); ALTER DATABASE alaris SET RECOVERY FULL";
$r1_shrink = mssql_query($q1_shrink);
$r2_shrink = mssql_query($q2_shrink);
?>
</body>
</html>
Each time you run the mssql_query function, you can check for errors something like this:
$r_ins1 = mssql_query($q_ins1);
if ($r_ins1) {
mssql_free_result($r_ins1);
} else {
print "Error running query: $q_ins1<br>\n";
}ASKER
no in sqlserver the into is not necessary,
The main problem that arises is that
it executes upto line no 312 perfectly but it doesn't go any further if i comment out the mssql_free_result() lines all over the document,and starts showing the error message unable to set query
and query failed
The main problem that arises is that
it executes upto line no 312 perfectly but it doesn't go any further if i comment out the mssql_free_result() lines all over the document,and starts showing the error message unable to set query
and query failed
Well, the error "mssql_free_result(): supplied argument is not a valid MS SQL-result resource ... " indicates that the call to the mssql_query function was not successful I think. Try adding the code I suggested, and see if you get the SQL printed out (if so, you may see where it's going wrong).
ASKER
let me try and give it a shot
Actually, re-reading what you said, the error "mssql_free_result(): supplied argument is not a valid MS SQL-result resource ... " may just be because you are doing an insert statement, so you don't get results returned.
Still, the next step I'd do is adding error checking around the query execution on line 332 (I'm assuming the line numbers on this page match the line numbers in your code):
In your previous comment, did you mean 332 instead of 312? If not, why do you think something is wrong with line 312?
Still, the next step I'd do is adding error checking around the query execution on line 332 (I'm assuming the line numbers on this page match the line numbers in your code):
In your previous comment, did you mean 332 instead of 312? If not, why do you think something is wrong with line 312?
$q_ins15 ="insert dbo.temp(e_futures,acc_no) select '0','$acce' from dbo.Table_1 t1,dbo.Table_5 t5 where (( t1.account_no like '$acce%') and (t1.todays_date between '$mo1-$dy1-$yr1' and '$mo2-$dy2-$yr2'))";
$r_ins15 = mssql_query( $q_ins15 );
if ($r_ins15) {
mssql_free_result($r_ins15);
} else {
print "Error running query: $q_ins15<br>\n";
}ASKER
these are the list of errors i got after i changed the code to what you told me
Warning: mssql_query() [function.mssql-query]: Query failed in C:\xampp\htdocs\alaris\tes
Error running query: insert dbo.temp(e_futures,acc_no)
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Populating ECN fees for Prime Accounts
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_fetch_row(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Populating SRC from all of Temp
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_fetch_row(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Displaying SRC
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_fetch_array(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Query failed in C:\xampp\htdocs\alaris\tes
Error running query: insert dbo.temp(e_futures,acc_no)
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Populating ECN fees for Prime Accounts
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_fetch_row(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Populating SRC from all of Temp
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_fetch_row(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Displaying SRC
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_fetch_array(): supplied argument is not a valid MS SQL-result resource in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
Warning: mssql_query() [function.mssql-query]: Unable to set query in C:\xampp\htdocs\alaris\tes
The most obvious *potential* problem would seem to me that the acc_no column in the temp table may not accept an empty value.
If that doesn't help, try using this modified code to output a (hopefully) useful error message:
If that doesn't help, try using this modified code to output a (hopefully) useful error message:
if ($r_ins15) {
mssql_free_result($r_ins15);
} else {
print "Error ".mssql_get_last_message()." running query: $q_ins15<br>\n";
}
ASKER
hey i got the problem
in line no 317 where i enter the while loop
it simply enters the loop but the value is not being passed into $acce of the account number
so i think its breaking the code somewhere
but not able to figure out where and why?
in line no 317 where i enter the while loop
it simply enters the loop but the value is not being passed into $acce of the account number
so i think its breaking the code somewhere
but not able to figure out where and why?
ASKER
so what can be the solution for this one
please tell me like how can i come to know how to enter the loop
please tell me like how can i come to know how to enter the loop
At the start of the while loop, add this:
print_r($row);
to output the contents of row to see what we're working with.
I don't see "Populating Expenses for Prime Accounts" in your output - did you just miss copying and pasting that bit?
print_r($row);
to output the contents of row to see what we're working with.
I don't see "Populating Expenses for Prime Accounts" in your output - did you just miss copying and pasting that bit?
ASKER
yeah i might have missed that bit
the print_r($row) prints out nothing
its entering the while loop but not picking up the value in the table.
dont know why and what is wrong there
the print_r($row) prints out nothing
its entering the while loop but not picking up the value in the table.
dont know why and what is wrong there
You may need to repost this as a new question to get it resolved quickly - I'm heading home now, and won't be able to work on this for 5 days... best of luck!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
is missing an "into", I believe, and should be:
$q_ins1 = "insert into dbo.temp(v_equity,v_option