function func_show_ecommerce_ledger_details($str_query, $cnn_connection, $crorcq, $str_type, $str_company_id)
{
$strReturn = "";
$approved = 0;
$approvedAmt = 0;
$approvedPer = 0;
$declined = 0;
$declinedAmt = 0;
$declinedPer = 0;
$canceled = 0;
$canceledAmt = 0;
$canceledPer = 0;
$totamount = 0;
$chequeapproved = 0;
$chequeapprovedAmt = 0;
$chequeapprovedPer = 0;
$chequedeclined = 0;
$chequedeclinedAmt = 0;
$chequedeclinedPer = 0;
$creditapproved = 0;
$creditapprovedAmt = 0;
$creditapprovedPer = 0;
$creditdeclined = 0;
$creditdeclinedAmt = 0;
$creditdeclinedPer = 0;
$creditcard = 0;
$cheque = 0;
$totalnum = 0;
$chequeAmt = 0;
$chequePer = 0;
$creditcardAmt = 0;
$creditcardPer = 0;
$chequecanceled = 0;
$chequecanceledAmt = 0;
$chequecanceledPer = 0;
$creditcanceled = 0;
$creditcanceledAmt = 0;
$creditcanceledPer = 0;
$deducted_amt = 0;
$credit_count = 0;
$cardPer = 0;
$charge_back_amount = 0;
$charge_back_count = 0;
$str_companyname = "";
$i_charge_back = "";
$cancel_reason = "";
$i_credit = "";
$i_discount_rate = "";
$i_transaction_fee = "";
$i_reserve = "";
$i_net_Per = "";
$i = 0;
if ($str_type != "") {
if ($str_type != "A") {
if ($str_type == "S") {
$str_type = "savings";
} else {
if ($str_type == "C") {
$str_type = "checking";
} else {
if ($str_type == "M") {
$str_type = "Master";
} else {
if ($str_type == "V") {
$str_type = "Visa";
}
}
}
}
}
} else {
$str_type = "A";
}
if (!($show_sql = mysql_query($str_query, $cnn_connection))) {
dieLog(mysql_errno() . ": " . mysql_error() . "<BR>");
}
// print $voice_count_uploads;
// print($str_query);
while ($showval = mysql_fetch_array($show_sql)) {
$i = $i + 1;
$totalnum = $totalnum + 1;
$trans_type = $showval[0];
$trans_status = $showval[1];
$trans_cancelstatus = $showval[2];
$trans_companyName = $showval[3];
$i_charge_back = $showval[4];
$i_credit = $showval[5];
$i_discount_rate = $showval[6];
$i_transaction_fee = $showval[7];
$i_reserve = $showval[8];
$trans_accounttype = $showval[9];
$trans_cardtype = $showval[10];
$cancel_reason = $showval[11];
$trans_amount = $showval[13];
// Check and credit card calculation.
if ($trans_type == "C") {
if ($trans_cancelstatus == "N") {
/* if($trans_passstatus !="PE") {
if($trans_passstatus == "NP" && $trans_status =="P") {
$chequenonpass = $chequenonpass + 1;
$chequenonpassAmt = $chequenonpassAmt + $trans_amount;
} elseif($trans_passstatus == "PA" && $trans_status =="P") {
$chequepass = $chequepass + 1;
$chequepassAmt = $chequepassAmt + $trans_amount;
}
*/
if ($trans_status == "A") {
$chequeapproved = $chequeapproved + 1;
$chequeapprovedAmt = $chequeapprovedAmt + $trans_amount;
}
if ($trans_status == "D") {
$chequedeclined = $chequedeclined + 1;
$chequedeclinedAmt = $chequedeclinedAmt + $trans_amount;
}
/* $voice_authcount = $voice_authcount + 1;
} else {
$chequepending = $chequepending + 1;
$chequependingAmt = $chequependingAmt + $trans_amount;
}
*/
} else {
// $voice_authcount = $voice_authcount + 1;
$chequecanceled = $chequecanceled + 1;
$chequecanceledAmt = $chequecanceledAmt + $trans_amount;
if ($cancel_reason == "Chargeback") {
$charge_back_count = $charge_back_count + 1;
} else {
$credit_count = $credit_count + 1;
}
}
$cheque = $cheque + 1;
$chequeAmt = $chequeAmt + $trans_amount;
} else {
$creditcard = $creditcard + 1;
$creditcardAmt = $creditcardAmt + $trans_amount;
if ($trans_cancelstatus == "N") {
/* if($trans_passstatus !="PE") {
if($trans_passstatus == "NP" && $trans_status =="P") {
$creditnonpass = $creditnonpass + 1;
$creditnonpassAmt = $creditnonpassAmt + $trans_amount;
} elseif($trans_passstatus == "PA" && $trans_status =="P") {
$creditpass = $creditpass + 1;
$creditpassAmt = $creditpassAmt + $trans_amount;
}
*/
if ($trans_status == "A") {
$creditapproved = $creditapproved + 1;
$creditapprovedAmt = $creditapprovedAmt + $trans_amount;
} else {
$creditdeclined = $creditdeclined + 1;
$creditdeclinedAmt = $creditdeclinedAmt + $trans_amount;
}
/* $voice_authcount = $voice_authcount + 1;
} else {
$creditpending = $creditpending + 1;
$creditpendingAmt = $creditpendingAmt + $trans_amount;
}
*/
} else {
$creditcanceled = $creditcanceled + 1;
$creditcanceledAmt = $creditcanceledAmt + $trans_amount;
if ($cancel_reason == "Chargeback") {
$charge_back_count = $charge_back_count + 1;
} else {
$credit_count = $credit_count + 1;
}
}
}
}
// Total Amount and Quantity Summary Display.
$approved = $chequeapproved + $creditapproved;
$approvedAmt = $chequeapprovedAmt + $creditapprovedAmt;
$declined = $chequedeclined + $creditdeclined;
$declinedAmt = $chequedeclinedAmt + $creditdeclinedAmt;
$canceled = $chequecanceled + $creditcanceled;
$canceledAmt = $chequecanceledAmt + $creditcanceledAmt;
$totamount = $creditcardAmt + $chequeAmt;
$charge_back_amount = $charge_back_count * $i_charge_back;
$credit_amount = $credit_count * $i_credit;
$i_discount_amt = $i_discount_rate * $approvedAmt / 100;
$i_transaction_amt = $i_transaction_fee * $i;
$i_reserve_amt = $i_reserve * $approvedAmt / 100;
$deducted_amt = $charge_back_amount + $credit_amount + $canceledAmt + $i_discount_amt + $i_transaction_amt + $i_reserve_amt;
//$i_net_amt = ($totamount - $deducted_amt);
$i_net_amt = $approvedAmt - $deducted_amt;
// $i_net_Per = (($totamount - $deducted_amt)/$totamount);
if ($chequeAmt != 0) {
$chequeapprovedPer = number_format($chequeapprovedAmt / $chequeAmt * 100, 2);
$chequedeclinedPer = number_format($chequedeclinedAmt / $chequeAmt * 100, 2);
$chequecanceledPer = number_format($chequecanceledAmt / $chequeAmt * 100, 2);
$chequePer = number_format($chequedeclinedPer + $chequeapprovedPer + $chequecanceledPer, 2);
}
if ($creditcardAmt != 0) {
$creditapprovedPer = number_format($creditapprovedAmt / $creditcardAmt * 100, 2);
$creditdeclinedPer = number_format($creditdeclinedAmt / $creditcardAmt * 100, 2);
$creditcanceledPer = number_format($creditcanceledAmt / $creditcardAmt * 100, 2);
$creditcardPer = number_format($creditdeclinedPer + $creditapprovedPer + $creditcanceledPer, 2);
}
if ($totamount != 0) {
$approvedPer = number_format($approvedAmt / $totamount * 100, 2);
$declinedPer = number_format($declinedAmt / $totamount * 100, 2);
$canceledPer = number_format($canceledAmt / $totamount * 100, 2);
$cardPer = number_format($declinedPer + $approvedPer + $canceledPer, 2);
}
if ($totamount > 0) {
print "<table width='100%' border='0'>";
if ($str_company_id != "A") {
print "<tr><td align='center' colspan='3'><br><P align='center'><font face='verdana' size='2'><B>{$trans_companyName}</B></font><br></td></tr>";
}
if ($crorcq == 'H' || $crorcq == 'A') {
print "<tr><td valign='top'><br>";
print "<P align='center'><font face='verdana' size='2'><B>Credit Card Summary</span><br>";
print "<table class='lefttopright' cellpadding='5' cellspacing='0' valign='top' bgColor='#ffffff' ID='Table1' style=' margin-left: 4; margin-top: 4; margin-bottom: 5'><tr height='30' bgcolor='#CCCCCC'>";
print "<td align='center' class='cl1'><span class='subhd'>Card Details</span></td>";
print "<td align='center' class='cl1'><span class='subhd'>Quantity</span></td>";
print "<td align='right' class='cl1'><span class='subhd'>Amount (" . func_get_processing_currency($str_company_id) . ")</span></td>";
print "<td align='right' class='cl1'><span class='subhd'>Percentage (%)</span></td></tr>";
print "<tr><td align='left' class='cl1'><font face='verdana' size='1'><b>Approved</b></font></td>";
print "<td align='center' class='cl1'><font face='verdana' size='1'> {$creditapproved}</font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'> " . number_format($creditapprovedAmt, 2) . "</font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'> " . number_format($creditapprovedPer, 2) . "</font></td>";
print "</tr><tr><td align='left' class='cl1'><font face='verdana' size='1'><b>Declined</b></font></td>";
print "<td align='center' class='cl1'><font face='verdana' size='1'> {$creditdeclined}</font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'> " . number_format($creditdeclinedAmt, 2) . "</font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'> " . number_format($creditdeclinedPer, 2) . "</font></td>";
print "</tr><tr><td align='left' class='cl1'><font face='verdana' size='1'><b>Credit</b></font></td>";
print "<td align='center' class='cl1'><font face='verdana' size='1'> {$creditcanceled}</font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'> " . number_format($creditcanceledAmt, 2) . "</font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'> " . number_format($creditcanceledPer, 2) . "</font></td>";
print "</tr><tr><td align='left' class='cl1'><font face='verdana' size='1'><b>Total</b></font></td>";
print "<td align='center' class='cl1'><font face='verdana' size='1'><b> {$creditcard}</b></font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'><b> " . number_format($creditcardAmt, 2) . "</b></font></td>";
print "<td align='right' class='bottom'><font face='verdana' size='1'> " . number_format(round($creditcardPer), 2) . "</font>";
print "</td></tr></table>";
print "</td>";
}
$valgg = "";
if ($crorcq == 'C') {
$valgg = " colspan=2 ";
}
if ($crorcq == 'C') {
$valgg = " colspan=2 ";
}
if ($crorcq == 'C' || $crorcq == 'A') {
$valgg = true;
print "<td {$valgg} valign='top'><br>";
print "<P align='center'><font face='verdana' size='2'><B>Check Summary</span><br>";
print "<table class='lefttopright' cellpadding='5' cellspacing='0' valign='top' bgColor='#ffffff' ID='Table1' style=' margin-left: 4; margin-top: 4; margin-bottom: 5'><tr height='30' bgcolor='#CCCCCC'>";
print "<td align='center' class='cl1'><span class='subhd'>Check Details</span></td>";
print "<td align='center' class='cl1'><span class='subhd'>Quantity</span></td>";
print "<td align='right' class='cl1'><span class='subhd'>Amount (" . func_get_processing_currency($str_company_id) . ")</span></td>";
print "<td align='right' class='cl1'><span class='subhd'>Percentage (%)</span></td></tr>";
print "<tr><td align='left' class='cl1'><font face='verdana' size='1'><b>Approved</b></font></td>";
print "<td align='center' class='cl1'><font face='verdana' size='1'> {$chequeapproved}</font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'> " . number_format($chequeapprovedAmt, 2) . "</font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'> " . number_format($chequeapprovedPer, 2) . "</font></td>";
print "</tr><tr><td align='left' class='cl1'><font face='verdana' size='1'><b>Declined</b></font></td>";
print "<td align='center' class='cl1'><font face='verdana' size='1'> {$chequedeclined}</font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'> " . number_format($chequedeclinedAmt, 2) . "</font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'> " . number_format($chequedeclinedPer, 2) . "</font></td>";
print "</tr><tr><td align='left' class='cl1'><font face='verdana' size='1'><b>Credit</b></font></td>";
print "<td align='center' class='cl1'><font face='verdana' size='1'> {$chequecanceled}</font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'> " . number_format($chequecanceledAmt, 2) . "</font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'> " . number_format($chequecanceledPer, 2) . "</font></td>";
print "</tr><tr><td align='left' class='cl1'><font face='verdana' size='1'><b>Total</b></font></td>";
print "<td align='center' class='cl1'><font face='verdana' size='1'><b> {$cheque}</b></font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'><b> " . number_format($chequeAmt, 2) . "</b></font></td>";
print "<td align='right' class='bottom'><font face='verdana' size='1'> " . number_format(round($chequePer), 2) . "</font>";
print "</td></tr></table>";
print "</td>";
}
print "<td {$valgg} valign='top'><br>";
print "<P align='center'><font face='verdana' size='2'><B>Total Summary</span><br>";
print "<table class='lefttopright' cellpadding='5' cellspacing='0' valign='top' bgColor='#ffffff' ID='Table1' style=' margin-left: 4; margin-top: 4; margin-bottom: 5'>";
print "<tr height='30' bgcolor='#CCCCCC'><td align='center' class='cl1'><span class='subhd'>Total Details</span></td>";
print "<td align='center' class='cl1'><span class='subhd'>Quantity</span></td>";
print "<td align='right' class='cl1'><span class='subhd'>Amount (" . func_get_processing_currency($str_company_id) . ")</span></td>";
print "<td align='right' class='cl1'><span class='subhd'>Percentage (%)</span></td></tr>";
print "<tr><td align='left' class='cl1'><font face='verdana' size='1'><b>Total</b></font></td>";
print "<td align='center' class='cl1'><font face='verdana' size='1'><b> {$totalnum}</b></font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'><b> " . number_format($totamount, 2) . "</b></font></td>";
print "<td align='right' class='bottom'><font face='verdana' size='1'> " . number_format($cardPer, 2) . "</font></td></tr>";
print "<tr><td align='left' class='cl1'><font face='verdana' size='1'><b>Approved</b></font></td>";
print "<td align='center' class='cl1'><font face='verdana' size='1'><b> {$approved}</b></font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'><b> " . number_format($approvedAmt, 2) . "</b></font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'> " . number_format($approvedPer, 2) . "</font></td></tr>";
print "<tr><td align='left' class='cl1'><font face='verdana' size='1'><b>Declined</b></font></td>";
print "<td align='center' class='cl1'><font face='verdana' size='1'> {$declined}</font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'> " . number_format($declinedAmt, 2) . "</font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'> " . number_format($declinedPer, 2) . "</font></td></tr>";
print "<tr><td align='left' class='cl1'><font face='verdana' size='1'><b>Credit</b></font></td>";
print "<td align='center' class='cl1'><font face='verdana' size='1'> {$canceled}</font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'> " . number_format($canceledAmt, 2) . "</font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'> " . number_format($canceledPer, 2) . "</font></td></tr>";
print "<tr bgcolor='#CCCCCC'><td align='left' class='cl1' colspan='2'><span class='subhd'>Deductions</span></td>";
print "<td align='right' class='cl1'><span class='subhd'>Deducted Amount (" . func_get_processing_currency($str_company_id) . ")</span></td>";
print "<td align='right' class='cl1'><span class='subhd'>Amount per transaction</span></td></td></tr>";
print "<tr><td align='left' class='cl1' colspan='2'><font face='verdana' size='1'><b>Charge Back</b></font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'> " . number_format($charge_back_amount, 2) . "</font></td>";
print "<td align='right' class='bottom'><font face='verdana' size='1'> " . number_format($i_charge_back, 2) . "</font></td></tr>";
print "<tr><td align='left' class='cl1' colspan='2'><font face='verdana' size='1'><b>Credit</b></font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'> " . number_format($credit_amount, 2) . "</font></td>";
print "<td align='right' class='bottom'><font face='verdana' size='1'> " . number_format($i_credit, 2) . "</font></td></tr>";
// print("<tr><td align='left' class='cl1' colspan='2'><font face='verdana' size='1'><b>Credit</b></font></td>");
// print("<td align='right' class='cl1'><font face='verdana' size='1'> ".number_format($canceledAmt,2)."</font></td>");
// print("<td align='right' class='bottom'><font face='verdana' size='1'> -</font></td></tr>");
print "<tr><td align='left' class='cl1' colspan='2'><font face='verdana' size='1'><b>Discount</b></font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'> " . number_format($i_discount_amt, 2) . "</font></td>";
print "<td align='right' class='bottom'><font face='verdana' size='1'> " . number_format($i_discount_rate, 2) . "%</font></td></tr>";
print "<tr><td align='left' class='cl1' colspan='2'><font face='verdana' size='1'><b>Transaction Fee</b></font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'> " . number_format($i_transaction_amt, 2) . "</font></td>";
print "<td align='right' class='bottom'><font face='verdana' size='1'> " . number_format($i_transaction_fee, 2) . "</font></td></tr>";
print "<tr><td align='left' class='cl1' colspan='2'><font face='verdana' size='1'><b>Reserve Fee</b></font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'> " . number_format($i_reserve_amt, 2) . "</font></td>";
print "<td align='right' class='bottom'><font face='verdana' size='1'> " . number_format($i_reserve, 2) . "%</font></td></tr>";
print "<tr><td align='left' class='cl1' colspan='2'><font face='verdana' size='1'><b>Total Deduction</b></font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'><b> " . number_format($deducted_amt, 2) . "</b></font></td>";
print "<td align='right' class='bottom'><font face='verdana' size='1'> -</font></td></tr>";
print "<tr><td align='left' class='cl1' colspan='2'><font face='verdana' size='1'><b>Net Amount</b></font></td>";
print "<td align='right' class='cl1'><font face='verdana' size='1'><b> ";
if ($i_net_amt < 0) {
print "(";
}
print number_format($i_net_amt, 2);
if ($i_net_amt < 0) {
print ")";
}
print "</b></font></td>";
print "<td align='right' class='bottom'><font face='verdana' size='1'> -</font></td></tr>";
print "</table>";
print "</td>";
print "</tr></table>";
} else {
if ($str_company_id == "A") {
print "<br>";
/*if($str_company_id != "A")
{
print("<P align='center'><font face='verdana' size='2'><B>".func_get_value_of_field($cnn_connection,"cs_companydetails","companyname","userid",$str_company_id)."</B></font></p>");
}*/
print "<center><font face='verdana' size='1' ><B>No transactions for this period</B></font><center><br>";
}
}
}
} else {
$tran_login_type = "pmtg";
}
}
if ($i_company_id != "") {
$str_atm_verify = func_get_value_of_field($cnn_cs, "cs_companydetails", "atm_verify", "userid", $i_company_id);
$str_currency = func_get_processing_currency($i_company_id);
$sql_trans_type = "Select transaction_type from cs_companydetails where userid={$i_company_id}";
if ($show_trans_show = mysql_query($sql_trans_type)) {
if ($show_val = mysql_fetch_array($show_trans_show)) {
$tran_login_type = $show_val[0];
}
}
} else {
$str_atm_verify = func_get_value_of_field($cnn_cs, "cs_companydetails", "atm_verify", "userid", $str_company_id);
$str_currency = func_get_processing_currency($str_company_id);
}
$i_from_day = date("d");
$i_from_month = date("m");
$i_from_year = date("Y");
$i_to_day = date("d");
$i_to_month = date("m");
$i_to_year = date("Y");
$str_return_date = date("Y m d H:i", mktime(0, 0, 0, $i_from_month, $i_from_day, $i_from_year));
$i_from_year = substr($str_return_date, 0, 4);
$i_from_month = substr($str_return_date, 5, 2);
$i_from_day = substr($str_return_date, 8, 2);
$str_return_date = date("Y m d H:i", mktime(0, 0, 0, $i_to_month, $i_to_day, $i_to_year));
$i_to_year = substr($str_return_date, 0, 4);
$i_to_month = substr($str_return_date, 5, 2);
$i_to_day = substr($str_return_date, 8, 2);
$strCancelled = mysql_result($rstSelect, $iLoop, 8);
$strCancellReason = mysql_result($rstSelect, $iLoop, 9);
$strCancellOther = mysql_result($rstSelect, $iLoop, 10);
$strVoiceAuthorisation = mysql_result($rstSelect, $iLoop, 11);
$strNoPassComment = mysql_result($rstSelect, $iLoop, 12);
$strDeclineComment = mysql_result($rstSelect, $iLoop, 13);
$strTransactiontime = mysql_result($rstSelect, $iLoop, 14);
$iServiceUserId = mysql_result($rstSelect, $iLoop, 15);
$strBillingDate = mysql_result($rstSelect, $iLoop, 17);
$iUserType = mysql_result($rstSelect, $iLoop, 18);
$iUserId = mysql_result($rstSelect, $iLoop, 19);
$ireferenceNumber = mysql_result($rstSelect, $iLoop, 20);
$processingcurrency = mysql_result($rstSelect, $iLoop, 21);
$strTransactionType = func_get_value_of_field($cnn_cs, "cs_companydetails", "transaction_type", "userid", $iCompanyId);
if ($processingcurrency == "") {
$processingcurrency = func_get_processing_currency($iCompanyId);
if ($processingcurrency == "") {
$processingcurrency = "USD";
}
}
$str_user_type = "";
//if($iUserId > 0)
//{
if ($iUserType == 1) {
$str_user_type = "TSR user";
} else {
if ($iUserType == 2) {
$str_user_type = "Call center";
} else {
if ($iUserType == 3) {
$str_user_type = "Websites";
