<?php
ob_start();
require("includes/check_auth_user.php");
require("includes/dbconnect.php");
include('includes/function.php');

$formname = "BANK RECONCILATION";
$file = "bankreco_rep_fr.php";

$admin = $_SESSION["admin_" . $dbname];
$zdb = $_SESSION["zdbyear_" . $dbname];
$myyear = $_SESSION[$myyear];
$zroot = $_SESSION["zdbroot"];
mysqli_select_db($conn, $zdb);

if (isset($_POST["submit"])) {

    $acname = $_POST["acname"];
    $mfrmdate = $_POST['frmdate'];
    $mtodate = $_POST['todate'];
    $dtype = $_POST['dtype'];
    $scope = $_POST['scope'];

    $dtypecond = '';
    $accodecond = '';

    if (!empty($acname)) {
        $acnamecond = " AND `accode`='$acname'";
    }
    if (!empty($dtype)) {
        $dtypecond = " AND `source`='$dtype' ";
    }
    if (!empty($scope)) {
        switch ($scope) {
            case 'A':
                $scopecond = "";
                break;
            case 'C':
                $scopecond = " AND `clrdate`!='0000-00-00' ";
                break;
            case 'N':
                $scopecond = " AND `clrdate`='0000-00-00' ";
                break;
            default:
                "";
        }
    }

    $mainqry1 = mysqli_query($conn, "SELECT vouchno, chqno, date_format(clrdate,'%d-%m-%Y') as clrdate, amount,source, 
                             date_format(chqdate,'%d-%m-%Y') as chqdate FROM `actran` where 
                            `date` >= '$mfrmdate' and `date` <= '$mtodate'  and clrdate = '0000-00-00' $acnamecond $dtypecond 
                             $scopecond and (source = 'BR' or source = 'TR' ) and grpcode = '001' and amount < 0 group by source,vouchno order by source,vouchno ") or die(mysqli_error($conn));

    $mainqry2 = mysqli_query($conn, "SELECT vouchno, chqno, date_format(clrdate,'%d-%m-%Y') as clrdate, amount,source , 
                             date_format(chqdate,'%d-%m-%Y') as chqdate FROM `actran` where 
                            `date` >= '$mfrmdate' and `date` <= '$mtodate' and clrdate = '0000-00-00' $acnamecond $dtypecond 
                             $scopecond and (source = 'BP' or source = 'TR' ) and grpcode = '001' and amount > 0 group by source,vouchno order by source,vouchno ") or die(mysqli_error($conn));
//    $getno = mysqli_num_rows($result);

    $acc = mysqli_query($conn, "SELECT openbal, dc from accmast where grpcode = '001' and accode = '$acname' ")or die(mysqli_error($conn));
    $getac = mysqli_fetch_array($acc);
    $mopbal = $getac['openbal'];
    $mopdc = $getac["dc"];
    if ($mopdc == "D") {
        $mopbal = $mopbal * - 1;
    }

    $getbal = mysqli_query($conn, "SELECT SUM(amount) as amt FROM `actran` WHERE accode = '$acname' AND 
                          `date` < '$mtodate' and (`retdate` != '' or `retdate` != '0000-00-00') ") or die(mysqli_error($conn));
    $getamt = mysqli_fetch_array($getbal);
    $amt = $getamt['amt'];
    $mobal = $mopbal + $amt;

    $mfdt = date("d-m-Y", strtotime($mfrmdate));
    $mtdt = date("d-m-Y", strtotime($mtodate));
    include("includes/linkshead.php");
    ?>
    <link href="MyStyle/rep_format.css" rel="stylesheet">
    <style>
        @media print {
            .noPrint {
                display:none;
            }
        }
        .white{
            color:white;
        }
    </style>
    </head>
    <body>
        <div class="container" style="width:50%">
            <? include("includes/head_rep.php"); ?>
            <div class="row">
                <div class="col-lg-12 pad">
                    <table style="width:100%;background: white" class="table  table-condensed">
                        <tr style="height:10px;background-color:white">
                            <th style='color:#26A69A;width:90%' class='text-center'>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>Balance As Per Bank Book</b></th>
                            <th style='color:blue;width:10%' class='text-right' ><?= abs($mobal);?></th>
                        </tr>
                        <tr style="height:10px;background-color:white">
                            <th style='color:#26A69A' class='text-center' colspan='2'><b>Cheques Deposits But Remain Uncleared</b></th>
                        </tr>
                    </table>
                    <table style="width:100%;background: white" class="table table-bordered table-condensed table-hover">
                        <thead>
                            <tr style="height:10px;" class="mySearchTr">
                                <td style="width:5%" class="white">SR.NO.</td>
                                <td style="width:10%" class="white">VOUCHER</td>
                                <td style="width:10%" class="white">SOURCE</td>
                                <td style="width:15%" class="white">CHEQUE NO.</td>
                                <td style="width:15%" class="white">CHEQUE DATE</td>
                                <td style="width:15%" class="white">CLEAR DATE</td>
                                <td class="text-right white" style="width:20%">AMOUNT</td>

                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $mctr = 0;
                            $totamt = 0;
                            while ($row = mysqli_fetch_array($mainqry1)) {
                                $amount = $row['amount'];
                                $amt = abs($amount);
                                $mctr++;
                                echo "<tr>";
                                echo "<td>$mctr</td>";
                                echo "<td>$row[vouchno]</td>";
                                echo "<td>$row[source]</td>";
                                echo "<td>$row[chqno]</td>";
                                echo "<td>$row[chqdate]</td>";
                                echo "<td>$row[clrdate]</td>";
                                echo "<td class='text-right'>$amt</td>";
                                echo "</tr>";

                                $totamt = $totamt + $amt;
                            }
                            ?>
                            <tr>
                                <td class="text-right" colspan="6" style='font-size: 12pt;color:blue'>Total</td>
                                <td class="text-right" style='font-size: 12pt;color:blue'><?= round($totamt); ?></td>
                            </tr>
                        </tbody>
                    </table>
                    <table style="width:100%;background: white" class="table table-bordered table-condensed">
                        <tr style="height:10px;" class="">
                            <th>&nbsp;</th>
                        </tr>
                    </table>
                    <table style="width:100%;background: white" class="table table-bordered table-condensed">
                        <tr style="height:10px;color:white">
                            <th style='color:#26A69A' class='text-center'><b>Cheques Issues But Remain Unpresented</b></th>
                        </tr>
                    </table>
                    <table style="width:100%;background: white" class="table table-bordered table-condensed">
                        <thead>
                            <tr style="height:10px;" class="mySearchTr">
                                <td style="width:5%" class="white">SR.NO.</td>
                                <td style="width:10%" class="white">TYPE</td>
                                <td style="width:10%" class="white">VOUCHER</td>
                                <td style="width:15%" class="white">CHEQUE NO.</td>
                                <td style="width:15%" class="white">CHEQUE DATE</td>
                                <td style="width:15%" class="white">CLEAR DATE</td>
                                <td class="text-right white" style="widtd:20%">AMOUNT</td>

                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $mctr2 = 0;
                            $totamt2 = 0;
                            while ($row1 = mysqli_fetch_array($mainqry2)) {
                                $amount = $row1[amount];
                                $amt = abs($amount);
                                $mctr2++;
                                echo "<tr>";
                                echo "<td>$mctr2</td>";
                                echo "<td>$row1[source]</td>";
                                echo "<td>$row1[vouchno]</td>";
                                echo "<td>$row1[chqno]</td>";
                                echo "<td>$row1[chqdate]</td>";
                                echo "<td>$row1[clrdate]</td>";
                                echo "<td class='text-right'>$amt</td>";
                                echo "</tr>";

                                $totamt2 = $totamt2 + $amt;
                            }
                            ?>
                            <tr>
                                <td class="text-right" colspan='6' style='font-size: 12pt;color:red'>Total</td>
                                <td class="text-right" style='font-size: 12pt;color:red'><?= round($totamt2); ?></td>
                            </tr>
                        </tbody>
                    </table>
                    <?
                    
                    $balance = (abs($mobal) + $totamt) - $totamt2
                    
                    ?>
                    <table class='table'>
                        <tr style='border-bottom:1px solid black'><th colspan='2'></th></tr>
                        <tr style='border-bottom:1px solid black;font-size: 12pt'>
                            <th style="width:90%;color:#26A69A" class='text-center'><b>Balance As Per Bank Statement</b></th>
                            <th style="width:10%;color:#26A69A;font-size: 12pt"><b><?= round($balance );?></b></th></tr>
                    </table>
                </div>
            </div>
        </div>
    </div>
    </div>

    <?php
    include("includes/linksfoot.php");
}
?>
</body>
</html>



