<?php session_start();
include ('../inc/dbcon.php');
include ('../inc/functions.php');
include ('../inc/repfunctions.php');
$today = strftime("%Y-%m-%d", time());

if(isset($_POST['tfdate'])){	
$fdate=$_POST['tfdate'];	
}else{
$fdate=date('Y-m-d', strtotime('-29 days', time()));	
}
if(isset($_POST['ttdate'])){
$tdate=$_POST['ttdate'];	
}else{
$tdate=$today;	
}

?>
<thead>
<tr>
 <th rowspan="3" style="text-align:center;"> Head of Accounts </th>
 <th rowspan="3" style="text-align:center;"> Beginning<br>Balance<br><?php echo date('d-M-Y', strtotime($fdate));?></th>
 <th colspan="3" style="text-align:center;"> During the Period </th>
 <th rowspan="3" style="text-align:center;"> Ending<br>Balance<br><?php echo date("d-M-Y", strtotime($tdate));?></th>
</tr>
<tr><th colspan="3" style="text-align:center;"> From: <?php echo date("d-M-Y", strtotime($fdate));?> To: <?php echo date("d-M-Y", strtotime($tdate));?></th></tr>
<tr class="tabhead">
 <th style="text-align:center;">Debit</th>
 <th style="text-align:center;">Credit</th>
 <th style="text-align:center;">Net Activity</th>
</tr>
</thead>
<tbody>
<!-- Debit -->     
<?php     
$sql="SELECT DISTINCT grid,groups FROM tbl_ledger WHERE clsid IN (1,4) ORDER BY grid ASC";
$result=mysqli_query($con,$sql)or die(mysqli_error($con));
while ($rowg=mysqli_fetch_array($result)){
$gopval=(get_goupval($rowg['grid'],'D','O','G',$fdate)-get_goupval($rowg['grid'],'C','O','G',$fdate));
$gdebit=get_goupval($rowg['grid'],'D','D','G',$fdate,$tdate);
$gcredit=get_goupval($rowg['grid'],'C','D','G',$fdate,$tdate);
$gnet=($gdebit-$gcredit);
$gcval=($gopval+$gnet);    
?>
<?php if(ABS($gcval)>0){?>    
<tr data-tt-id="<?php echo $rowg['grid'];?>" class="branch collapsed" style="background-color:#e0f7fa">
<td><b>&nbsp;<?php echo $rowg['groups'];?></b></td>    
<td style="text-align:right;"><?php echo getfloatval($gopval);?></td>
<td style="text-align:right;"><?php echo getfloatval($gdebit);?></td>
<td style="text-align:right;"><?php echo getfloatval($gcredit);?></td>
<td style="text-align:right;"><?php echo getfloatval($gnet);?></td>
<td style="text-align:right;"><?php echo getfloatval($gcval);?></td>
</tr>
<?php } ?>    
<?php     
$sql="SELECT DISTINCT sgrid,sgroup FROM tbl_ledger WHERE clsid IN (1,4) AND grid='".$rowg['grid']."' ORDER BY sgrid ASC";
$results=mysqli_query($con,$sql)or die(mysqli_error($con));
while ($rowsg=mysqli_fetch_array($results)){
$sopval=(get_goupval($rowsg['sgrid'],'D','O','S',$fdate)-get_goupval($rowsg['sgrid'],'C','O','S',$fdate));
$sdebit=get_goupval($rowsg['sgrid'],'D','D','S',$fdate,$tdate);
$scredit=get_goupval($rowsg['sgrid'],'C','D','S',$fdate,$tdate);
$snet=($sdebit-$scredit);
$scval=($sopval+$snet);    
?>
<?php if(ABS($scval)>0){?>    
<tr data-tt-id="<?php echo $rowsg['sgrid'].$rowg['grid'];?>" data-tt-parent-id="<?php echo $rowg['grid'];?>" class="table-success branch collapsed" style="display: none;">
<td style="padding-left: 15px;">&nbsp;<?php echo $rowsg['sgroup'];?></td>
<td style="text-align:right;"><?php echo getfloatval($sopval);?></td>
<td style="text-align:right;"><?php echo getfloatval($sdebit);?></td>
<td style="text-align:right;"><?php echo getfloatval($scredit);?></td>
<td style="text-align:right;"><?php echo getfloatval($snet);?></td>
<td style="text-align:right;"><?php echo getfloatval($scval);?></td>
</tr>
<?php } ?>     
<?php     
$sql="SELECT id,code,ledger FROM tbl_ledger WHERE clsid IN (1,4) AND sgrid='".$rowsg['sgrid']."' ORDER BY id ASC";
$resultl=mysqli_query($con,$sql)or die(mysqli_error($con));
while ($rowl=mysqli_fetch_array($resultl)){   
$lopval=(get_ledgerval($rowl['id'],'D','O',$fdate)-get_ledgerval($rowl['id'],'C','O',$fdate));
$ldebit=get_ledgerval($rowl['id'],'D','D',$fdate,$tdate);
$lcredit=get_ledgerval($rowl['id'],'C','D',$fdate,$tdate);
$lnet=($ldebit-$lcredit);
$lcval=($lopval+$lnet);     
?>
<?php if(ABS($lcval)>0){?>    
<tr data-tt-id="<?php echo $rowl['id'].$rowsg['sgrid'].$rowg['grid'];?>" data-tt-parent-id="<?php echo $rowsg['sgrid'].$rowg['grid'];?>" class="leaf collapsed" style="display: none;">
<td style="padding-left:30px;"><?php echo $rowl['code'];?>: <?php echo $rowl['ledger'];?></td>
<td style="text-align:right;"><?php echo getfloatval($lopval);?></td>
<td style="text-align:right;"><?php echo getfloatval($ldebit);?></td>
<td style="text-align:right;"><?php echo getfloatval($lcredit);?></td>
<td style="text-align:right;"><?php echo getfloatval($lnet);?></td>
<td style="text-align:right;"><?php echo getfloatval($lcval);?></td>
</tr>
<?php } ?>     
<?php }}} ?>
<!-- /Debit -->
<!-- Credit -->
<?php     
$sql="SELECT DISTINCT grid,groups FROM tbl_ledger WHERE clsid IN (2,3,5) ORDER BY grid ASC";
$result=mysqli_query($con,$sql)or die(mysqli_error($con));
while ($rowg=mysqli_fetch_array($result)){
$gopval=(get_goupval($rowg['grid'],'C','O','G',$fdate)-get_goupval($rowg['grid'],'D','O','G',$fdate));
$gdebit=get_goupval($rowg['grid'],'D','D','G',$fdate,$tdate);
$gcredit=get_goupval($rowg['grid'],'C','D','G',$fdate,$tdate);
$gnet=($gcredit-$gdebit);
$gcval=($gopval+$gnet);    
?>
<?php if(ABS($gcval)>0){?>    
<tr data-tt-id="<?php echo $rowg['grid'];?>" class="branch collapsed" style="background-color:#eaf5ea">
<td><b>&nbsp;<?php echo $rowg['groups'];?></b></td>    
<td style="text-align:right;"><?php echo number_format($gopval,2);?></td>
<td style="text-align:right;"><?php echo $gdebit;?></td>
<td style="text-align:right;"><?php echo $gcredit;?></td>
<td style="text-align:right;"><?php echo number_format($gnet,2);?></td>
<td style="text-align:right;"><?php echo number_format($gcval,2);?></td>
</tr>
<?php } ?>    
<?php     
$sql="SELECT DISTINCT sgrid,sgroup FROM tbl_ledger WHERE clsid IN (2,3,5) AND grid='".$rowg['grid']."' ORDER BY sgrid ASC";
$results=mysqli_query($con,$sql)or die(mysqli_error($con));
while ($rowsg=mysqli_fetch_array($results)){
$sopval=(get_goupval($rowsg['sgrid'],'C','O','S',$fdate)-get_goupval($rowsg['sgrid'],'D','O','S',$fdate));
$sdebit=get_goupval($rowsg['sgrid'],'D','D','S',$fdate,$tdate);
$scredit=get_goupval($rowsg['sgrid'],'C','D','S',$fdate,$tdate);
$snet=($scredit-$sdebit);
$scval=($sopval+$snet);    
?>
<?php if(ABS($scval)>0){?>    
<tr data-tt-id="<?php echo $rowsg['sgrid'].$rowg['grid'];?>" data-tt-parent-id="<?php echo $rowg['grid'];?>" class="branch collapsed" style="display: none;">
<td style="padding-left: 15px;">&nbsp;<?php echo $rowsg['sgroup'];?></td>
<td style="text-align:right;"><?php echo number_format($sopval,2);?></td>
<td style="text-align:right;"><?php echo $sdebit;?></td>
<td style="text-align:right;"><?php echo $scredit;?></td>
<td style="text-align:right;"><?php echo number_format($snet,2);?></td>
<td style="text-align:right;"><?php echo number_format($scval,2);?></td>
</tr>
<?php } ?>    
<?php     
$sql="SELECT id,code,ledger FROM tbl_ledger WHERE clsid IN (2,3,5) AND sgrid='".$rowsg['sgrid']."' ORDER BY id ASC";
$resultl=mysqli_query($con,$sql)or die(mysqli_error($con));
while ($rowl=mysqli_fetch_array($resultl)){
$lopval=(get_ledgerval($rowl['id'],'C','O',$fdate)-get_ledgerval($rowl['id'],'D','O',$fdate));
$ldebit=get_ledgerval($rowl['id'],'D','D',$fdate,$tdate);
$lcredit=get_ledgerval($rowl['id'],'C','D',$fdate,$tdate);
$lnet=($lcredit-$ldebit);
$lcval=($lopval+$lnet);    
?>
<?php if(ABS($lcval)>0){?>    
<tr data-tt-id="<?php echo $rowl['id'].$rowsg['sgrid'].$rowg['grid'];?>" data-tt-parent-id="<?php echo $rowsg['sgrid'].$rowg['grid'];?>" class="leaf collapsed" style="display: none;">
<td style="padding-left:30px;"><?php echo $rowl['code'];?>: <?php echo $rowl['ledger'];?></td>
<td style="text-align:right;"><?php echo number_format($lopval,2);?></td>
<td style="text-align:right;"><?php echo $ldebit;?></td>
<td style="text-align:right;"><?php echo $lcredit;?></td>
<td style="text-align:right;"><?php echo number_format($lnet,2);?></td>
<td style="text-align:right;"><?php echo number_format($lcval,2);?></td>
</tr>
<?php } ?>    
<?php }}} ?>
<!-- /Credit -->    
</tbody>
<tfoot>
<?php 
$dcopval=((get_goupval('1','D','O','C',$fdate)+get_goupval('4','D','O','C',$fdate))-(get_goupval('1','C','O','C',$fdate)+get_goupval('4','C','O','C',$fdate)));
$dcdebit=(get_goupval('1','D','D','C',$fdate,$tdate)+get_goupval('4','D','D','C',$fdate,$tdate));
$dccredit=(get_goupval('1','C','D','C',$fdate,$tdate)+get_goupval('4','C','D','C',$fdate,$tdate));
$dcnet=($dcdebit-$dccredit);
$dccval=($dcopval+$dcnet);
    
$ccopval=((get_goupval('2','C','O','C',$fdate)+get_goupval('3','C','O','C',$fdate)+get_goupval('5','C','O','C',$fdate))-(get_goupval('2','D','O','C',$fdate)+get_goupval('3','D','O','C',$fdate)+get_goupval('5','D','O','C',$fdate)));
$ccdebit=(get_goupval('2','D','D','C',$fdate,$tdate)+get_goupval('3','D','D','C',$fdate,$tdate)+get_goupval('5','D','D','C',$fdate,$tdate));
$cccredit=(get_goupval('2','C','D','C',$fdate,$tdate)+get_goupval('3','C','D','C',$fdate,$tdate)+get_goupval('5','C','D','C',$fdate,$tdate));
$ccnet=($cccredit-$ccdebit);
$cccval=($ccopval+$ccnet);    
?>    
<tr style="background-color:#ffeee8">
<td style="padding-left: 15px;"><b>Total Debit</b></td>
<td style="text-align:right;"><?php echo number_format($dcopval,2);?></td>
<td style="text-align:right;"><?php echo number_format(($dcdebit+$ccdebit),2);?></td>
<td style="text-align:right;"></td>
<td style="text-align:right;"><?php echo number_format($dcnet,2);?></td>
<td style="text-align:right;"><?php echo number_format($dccval,2);?></td>
</tr>
    
<tr style="background-color:#ffeee8">
<td style="padding-left: 15px;"><b>Total Credit</b></td>
<td style="text-align:right;"><?php echo number_format($ccopval,2);?></td>
<td style="text-align:right;"></td>
<td style="text-align:right;"><?php echo number_format(($dccredit+$cccredit),2);?></td>
<td style="text-align:right;"><?php echo number_format($ccnet,2);?></td>
<td style="text-align:right;"><?php echo number_format($cccval,2);?></td>
</tr>
<tr style="background-color:#ffeee8">
<td style="padding-left: 15px;"><b>Mismatch</b></td>
<td style="text-align:right;"><?php echo number_format(($dcopval-$ccopval),2);?></td>
<td style="text-align:right;"></td>
<td style="text-align:right;"></td>
<td style="text-align:right;"><?php echo number_format(($dcnet-$ccnet),2);?></td>
<td style="text-align:right;"><?php echo number_format(($dccval-$cccval),2);?></td>
</tr>
</tfoot>