I have a module in a project that requires a ledger that looks like a bank statement.
id | date | refno | debit | credit | bal
| 50,000 | | 50,000
25000 | 25000
10000 | 15000
Credit should deduct from last ball . Thanks
Basically, you may use Row_Rendered server event to calculate/manipulate the ViewValue of the bal column.
select
id,
date,
subscriber_id,
refno,
transno,
dr,
cr,
sum(dr) over(order by id) - sum(cr) over(order by id) as balance
FROM `clientledger` where refno='03042409191516';
works perfectly on phpmyadmini created a view with this code
select `clientledger`.`id` AS `id`,`clientledger`.`date` AS `date`,`clientledger`.`subscriber_id` AS `subscriber_id`,`clientledger`.`refno` AS `refno`,`clientledger`.`transno` AS `transno`,`clientledger`.`dr` AS `dr`,`clientledger`.`cr` AS `cr`,sum(`clientledger`.`dr`) over ( order by `clientledger`.`id`) - sum(`clientledger`.`cr`) over ( order by `clientledger`.`id`) AS `balance` from `clientledger`
it works well until i filter with rfno or date.how can i pass where cause as a variable in view? like ```where refno=‘$refno’``.
You may use Recordset_Selecting server event for such case.
Finally resolved with post from add page to a custom page .
<?php
$Page->showMessage();
?>
<style>body{
font-family: Arial, Helvetica, sans-serif;
/* font-size: 25px; */
}</style>
<?php
echo'<html><script src="https://unpkg.com/sweetalert/dist/sweetalert.min.js"></script></html>';
//require"chk_portalfee_remita_response.php";
if(isset($_GET['refno']) )
{
$refno=$_GET['refno'];
$datefrom=$_GET['datefrom'];
$dateto=$_GET['dateto'];
}
else{
echo '
<script>swal("Error!", " Invalid Ref No !", "error")</script>
';
echo"<script>
setTimeout(function(){
window.location.href = 'loadledgeradd';
}, 1000);
</script>";
exit;
}
// if slug is empty lock
if($refno=='' || $datefrom=='' || $dateto=='' ){
echo '
<script>swal("Error!", " Parameter(s) Missing !", "error")</script>
';
echo"<script>
setTimeout(function(){
window.location.href = 'loadledgeradd';
}, 1000);
</script>";
exit;
}
$refisindb = ExecuteScalar("SELECT refno FROM postpayment WHERE refno= '$refno' ");
if(!$refisindb){
echo '
<script>swal("Error!", " Invalid Ref No !", "error")</script>
';
echo"<script>
setTimeout(function(){
window.location.href = 'loadledgeradd';
}, 1000);
</script>";
exit;
}
// Get data
// get customer picture
$customerid = ExecuteScalar("SELECT customerid FROM invoicetemp WHERE refno= '$refno' ");
$productname = ExecuteScalar("SELECT productname FROM invoicetemp WHERE refno= '$refno' ");
$Particulars = ExecuteScalar("SELECT Particulars FROM invoicetemp WHERE refno= '$refno' ");
$image = ExecuteScalar("SELECT `image` FROM users WHERE id= '$customerid'");
$surname = ExecuteScalar("SELECT `surname` FROM users WHERE id= '$customerid'");
$firstname = ExecuteScalar("SELECT `firstname` FROM users WHERE id= '$customerid'");
$fullname= "$surname $firstname ";
$datetempfrom=strtotime($datefrom); $showdatepaymentf=date('j M Y ',$datetempfrom);
$datetempto=strtotime($dateto); $showdatepaymentt=date('j M Y ',$datetempto);
//if not set go to login page
echo' <style>.center {
margin: auto;
width: 90%;
padding: 10px;
background-color:#fff;
}
@media (max-width: 767px) {
.center{
width: 100%;
margin-left:-2px;
}
}
</style>
<div class="container-xxl center">
<div class="text-center" style="margin-top:10px;"> <img src="./extraimg/plogo.png" alt="logo"></div>
<div><h1 class="text-center mt-20">Customer Ledger</h1></div>
<div><h5 class="text-center mt-10">('.$productname.' | '.$fullname.' )
</div>
<div><h5 class="text-center mt-10">For the Period From '.$showdatepaymentf.' to '.$showdatepaymentt.'
</div>
'
;
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Customer Ledger</title>
</head>
<body>
<div class="container-lg" style="margin-top:5px;">
<div class="table-responsive">
<table id="zctb" class="display table table-striped table-bordered table-hover " cellspacing="0" width="100%">
<thead>
<tr class="thcustom">
<th>SN</th>
<th>Date</th>
<th>Ref No</th>
<th>Type</th>
<th>Debit Amount</th>
<th>Credit Amount</th>
<th>Balance</th>
</tr>
</thead>
<tbody>
<?php
// loop to use on html tbl $refno=='' || $datefrom=='' || $dateto=='' )
$conn = $GLOBALS["Conn"];
$sql = "SELECT id, `date`, subscriber_id, refno, transno, dr, cr, sum(dr) over(order by id) - sum(cr) over(order by id) as balance FROM `clientledger` where refno='$refno' AND `date` BETWEEN '$datefrom' AND '$dateto'; ";
$rows = $conn->executeQuery($sql)->fetchAll();
$cnt=1;
foreach ($rows as $row) {
//print_r($row);
$date= $row['date'];
$datetemprw=strtotime($date); $showdaterow=date('j M Y ',$datetemprw);
$refno= $row['refno'];
$transno= $row['transno'];
$dr= $row['dr'];
$cr= $row['cr'];
if($dr== 0) {$dr='';}
if($cr== 0) {$cr='';}
$balance= $row['balance'];
?>
<tr>
<td><?php echo htmlentities($cnt);?></td>
<td><?php echo $showdaterow;?></td>
<td><?php echo $refno;?></td>
<td><?php echo $transno;?></td>
<td><?php echo $dr;?></td>
<td><?php echo $cr;?></td>
<td><?php echo $balance;?></td>
</tr>
<?php $cnt=$cnt+1; } ?>
</tbody>
</table>
</div>
</body>
</html>
The final challenge now is to get the sum of each column (Like the aggregate value)
$balancedd .= $row[‘balance’] .‘,’;
$arr= explode(‘,’, $balancedd);
$lastbal= $arr[count($arr) -2];
this worked . Thank you