<?php

require("includes/check_auth_user.php");
require("includes/dbconnect.php");
include ('includes/function.php');
$admin = $_SESSION["admin_" . $dbname];
$zdb = $_SESSION["zdbyear_" . $dbname];
mysqli_select_db($conn, $zdb);

$zroot = $_SESSION["zdbroot"];

if (isset($_GET['accode'])) {
    $maccode = $_GET["accode"];
    $mvouch = $_GET["vouchno"];
    $mvouchdt = $_GET["vouchdt"];
    $mamount = $_GET["amount"];
    $bnkcode = $_GET["bank"];
    $mndbf = $_GET["dbf"];
    $pvar = $_GET["pvar"];
    $msource = $_GET["source"];
    $myear = $_SESSION["tit_" . $dbname];


    $getcomp = mysqli_query($conn, "SELECT tdscode FROM $zroot.company WHERE `code` = '1' ")or die(mysqli_error($conn));
    $fcomp = mysqli_fetch_array($getcomp);
    $tdscode = $fcomp['tdscode'];

    $cqry = mysqli_query($conn, "SELECT `grpcode` FROM `accmast` WHERE `accode` = '$maccode' LIMIT 0,1 ");
    if (mysqli_num_rows($cqry) <= 0) {
        exit();
    }

    $grpcode = mysqli_fetch_assoc($cqry);

    if ($grpcode["grpcode"] == "009" || $grpcode["grpcode"] == "010" || $grpcode["grpcode"] == "014") {
        $bqry = mysqli_query($conn, "SELECT `bankinit` FROM `accmast` WHERE `accode` = '$bnkcode' LIMIT 0,1 ");
        $init = mysqli_fetch_assoc($bqry);
        
        $pbinit = $init["bankinit"];
        $binitcond = "";

        if (!empty($pbinit)) {
            $binitcond = " and bankinit = '$pbinit' ";
        }

        if ($pvar == "R") {
            $mtit = 'Settlememt Of Bills ( Receipts )';
            $mfile = 'salemain';
            $mfile2 = "deexmain";
            $mfile3 = "salemainexp";
            $mfile4 = "slonlmain";

            $mtype = "R";
        } else {
            $mtit = 'Settlememt Of Bills ( Payments )';
            $mfile = 'rawmain';
            $mfile2 = "purexpmain";

            $mfile3 = "";
            $mfile4 = "";
            $mtype = "P";
        }
        if ($pvar == "R") {
            $subtmp1 = mysqli_query($conn, "SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, `invno` as `billno`, vouchdt, 
                       DATE_FORMAT(`vouchdt`, '%d-%m-%Y') AS `billdate`, `billamt` , `party` as  `accode` FROM $mfile
                       WHERE `party` = '$maccode' AND `paidby` != 'C' 
                       UNION 
                       SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, `invno` as `billno`, vouchdt, 
                       DATE_FORMAT(`vouchdt`, '%d-%m-%Y') AS `billdate`, `billamt` , `party` as  `accode` FROM $mfile2
                       WHERE `party` = '$maccode' AND `paidby` != 'C' 
                       UNION 
                       SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, `vouchno` as `billno`, invdate as vouchdt, 
                       DATE_FORMAT(`invdate`, '%d-%m-%Y') AS `billdate`, `billamt` , `partyname` as  `accode` FROM $mfile3
                       WHERE `partyname` = '$maccode' 
                       UNION 
                       SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, `invno` as `billno`, vouchdt, 
                       DATE_FORMAT(`vouchdt`, '%d-%m-%Y') AS `billdate`, `billamt` , `party` as  `accode` FROM $mfile4
                       WHERE `party` = '$maccode' AND `paidby` != 'C'
                       UNION 
                       SELECT `yearno`,`type`,`vouchno`, `billno`, billdate as vouchdt, 
                       DATE_FORMAT(`billdate`, '%d-%m-%Y') AS `billdate`, `billamt`, `accode` FROM `prevbill` 
                       WHERE `accode` = '$maccode' AND `type` = '$mtype'  ") or die(mysqli_error($conn));
        } else {
//            $subtmp1 = mysqli_query($conn, "SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, `invno` as  `billno`, vouchdt,
//                       DATE_FORMAT(`vouchdt`, '%d-%m-%Y') AS `billdate`, `billamt`, `party` as  `accode` FROM $mfile
//                       WHERE `party` = '$maccode' AND `paidby` != 'C' 
//                       UNION 
//                       SELECT '$myear' AS `yearno`, 'J' AS `type`, `vouchno`, `vouchno` as  `billno`, date as vouchdt, 
//                       DATE_FORMAT(`date`, '%d-%m-%Y') AS `billdate`, abs(`amount`) as billamt,  `accode` FROM actran
//                       WHERE source = 'JV' and `accode` = '$tdscode' and amount > 0 
//                       union                       
//                       SELECT '$myear' AS `yearno`, 'E' AS `type`, `vouchno`, `invno` as  `billno`, vouchdt,
//                       DATE_FORMAT(`vouchdt`, '%d-%m-%Y') AS `billdate`, `billamt`, `party` as  `accode` FROM $mfile2
//                       WHERE `party` = '$maccode' AND `paidby` != 'C' 
//                       union                       
//                       SELECT `yearno`,`type`,`vouchno`, `billno`, billdate as vouchdt, 
//                       DATE_FORMAT(`billdate`, '%d-%m-%Y') AS `billdate`, `billamt`, `accode` FROM `prevbill`
//                       WHERE `accode` = '$maccode' AND `type` = '$mtype'  ") or die(mysqli_error($conn));

            $subtmp1 = mysqli_query($conn, "SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, `invno` as  `billno`, vouchdt,
                       DATE_FORMAT(`vouchdt`, '%d-%m-%Y') AS `billdate`, `billamt`, `party` as  `accode` FROM $mfile
                       WHERE `party` = '$maccode' AND `paidby` != 'C' 
                       UNION 
                       SELECT '$myear' AS `yearno`, 'E' AS `type`, `vouchno`, `invno` as  `billno`, vouchdt,
                       DATE_FORMAT(`vouchdt`, '%d-%m-%Y') AS `billdate`, `billamt`, `party` as  `accode` FROM $mfile2
                       WHERE `party` = '$maccode' AND `paidby` != 'C' 
                       union                       
                       SELECT `yearno`,`type`,`vouchno`, `billno`, billdate as vouchdt, 
                       DATE_FORMAT(`billdate`, '%d-%m-%Y') AS `billdate`, `billamt`, `accode` FROM `prevbill`
                       WHERE `accode` = '$maccode' AND `type` = '$mtype'  ") or die(mysqli_error($conn));

        }
        

        $num = mysqli_num_rows($subtmp1);
        if (mysqli_num_rows($subtmp1) > 0) {
            $counter = 0;

            echo "<div id='settle'> <table id='bill_table' border='1' class='table table-bordered' width='100%'> ";
            echo "<tr class='mySearchTr'>";
            echo "<th>Year</th>";
            echo "<th>Bill No.</th>";
            echo "<th style='width:125px;'>Date</th>";
            echo "<th id='numeric'>Bill Amount</th>";
            echo "<th id='numeric'>Adjusted</th>";
            echo "<th id='numeric'>Set Off</th>";
            echo "<th id='numeric'>Balance Amt.</th>";
            echo "<th style='width:7%' id='numeric'>Days</th>";
            echo "</tr>";

            while ($subtmp = mysqli_fetch_assoc($subtmp1)) {
                $qparty = $subtmp["accode"];
                $qyear = $subtmp["yearno"];
                $qbill = $subtmp["billno"];
                $mbilldate = $subtmp["billdate"];
                $qvouch = $subtmp["vouchno"];
                $qamt = round($subtmp["billamt"]);
                $ptype = $subtmp["type"];
                $pdate = $subtmp["vouchdt"];

                $totadj = 0;
                $totset = 0;
                

                $trn = mysqli_query($conn, "SELECT * FROM `billsett` WHERE `source` = '$msource' AND
                                   `bnkvouch` = '$mvouch'  AND `party` = '$qparty' AND 
                                   `yearno` = '$qyear' AND `vouchno` = '$qvouch' and `type` = '$ptype' ") or die(mysqli_error($conn));

                $tranqry = mysqli_fetch_assoc($trn);

                if (mysqli_num_rows($trn) > 0) {
                    $totset = round($tranqry["setoff"]);
                }
                
                $trn2 = mysqli_query($conn, "SELECT SUM(`setoff`) AS `totadj`, nodays FROM `billsett` WHERE 
                                     `yearno` = '$qyear' AND `type` = '$ptype' AND `vouchno` = '$qvouch' AND 
                                     `party` = '$qparty' LIMIT 0,1 ") or die(mysqli_error($conn));

                $tranqry2 = mysqli_fetch_assoc($trn2);
                $totadj = round($tranqry2["totadj"]);
                
                $amtadj = ($totadj - $totset);
                $setoff = $totset;
                $balamt = $qamt - $totadj;

                $nodays = $pdate - $mvouchdt;
//                print $nodays;

                if ($qamt - $amtadj > 0) {
                    $counter++;
                    echo "<tr><td><input type='text' id='yearno_$counter' name='yearno_$counter'
                         value='$qyear' size='2' readonly class='form-control'/></td>";

                    echo "<input type='hidden' id='vouchno_$counter' name='vouchno_$counter'
                         value='$qvouch' readonly size='4' class='form-control' />";

                    echo "<td><input type='text' id='billno_$counter' name='billno_$counter'
                         value='$qbill' readonly size='7' class='form-control'/></td>";

                    echo "<td><input type='text' id='billdate_$counter' name='billdate_$counter'
                         value='$subtmp[billdate]' readonly size='8' class='billdate form-control'/></td>";

                    echo "<td id='numeric'><input type='text' id='billamt_$counter'
                         name='billamt_$counter' value='$qamt' readonly size='11' style='text-align:right;' class='billamt form-control' /></td>";

                    echo "<td id='numeric'><input type='text' id='amtadj_$counter' name='amtadj_$counter'
                         value='$amtadj' readonly size='11' style='text-align:right;' class='amtadj form-control' /></td>";

                    echo "<td id='numeric'><input type='text'  id='setoff_$counter' name='setoff_$counter'
                         value='$setoff' size='11' style='text-align:right;' class='setoff form-control' tabindex='1' autofocus/></td>";

                    echo "<td id='numeric'><input type='text'  id='balamt_$counter' name='balamt_$counter'
                         value='$balamt' readonly size='11' style='text-align:right;' class='balamt form-control' /></td>";

                    echo "<td id='numeric'><input type='text'  id='days_$counter' name='days_$counter'
                         value='$nodays' readonly size='6' style='text-align:right;' class='days form-control' /></td></tr>";

                    echo "<input type='hidden'  id='type_$counter' name='type_$counter'
                         value='$ptype' readonly   class='type form-control' /></tr>";
                }
            }
        } else {
            echo "<center><h1><font color='red'>No bills found for this party</font></h1></center>";
        }
        echo "</table><input type='text' value='$counter' id='counter' name='counter' hidden='hidden' /> </div>";
    }
}
?>


