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

$zdb = $_SESSION["zdbyear_" . $dbname];
$admin = $_SESSION["admin_" . $dbname];

$trandb = "actran";
$myear = $_SESSION["tit_" . $dbname];
mysqli_select_db($conn, $zdb);

if (isset($_POST["view"])) {
    $acond = "";
    $scond = "";
    $scond1 = "";

    if (!empty($_POST["acname"])) {
        $accode = trim($_POST["acname"]);
        $acond = "AND a.`party`='$accode'";
    }

    //    if (!empty($_POST["cmbstation"])) {
    //        $station = trim($_POST["cmbstation"]);
    //        $scond = "AND a.`station`='$station'";
    //    }

    $mtodate = $_POST["frmdt"];

    if ($_POST["hiddenval"] == "R") {
        $mfile = "salemain";
        $mtype = "R";
        $ttp = "R";
        $mdate = 'vouchdt';
        $fintot = "billamt";
        $grid = "osbill.php?para=R";
        $formname1 = "Outstanding Receivable";

        $salesman = $_POST['salesman'];

        if (!empty($salesman)) {
            $scond1 = " and a.`salesman`='$salesman' ";
            $scond = " and `salesman`='$salesman' ";
        }
    } else {
        $mfile = "purmain";
        $mtype = "P";
        $ttp = "P";
        $mdate = 'vouchdt';
        $fintot = "billamt";
        $grid = "osbill.php?para=P";
        $formname1 = "Outstanding Payable";
    }

    if ($_POST["dtype"] == 1) {
        $formname = "$formname1";
        $dtype = 1;
    } else {
        $formname = "$formname1";
        $dtype = 2;
    }
    include("includes/linkshead.php");

    mysqli_query($conn, "drop temporary table if exists settdbf ") or die(mysqli_error($conn));
    if ($mtype == "R") {
        mysqli_query($conn, "create temporary table settdbf 
                                    SELECT a.*, b.salesman FROM `billsett` a, salemain b WHERE a.vouchno = b.vouchno and a.`type`='$mtype' ") or die(mysqli_error($conn));
    } else {
        mysqli_query($conn, "create temporary table settdbf 
                                    SELECT * FROM `billsett` WHERE `type`='$mtype' ") or die(mysqli_error($conn));
    }

    //    $mfdt = date("d-m-Y", strtotime($mfrmdate));
    $mtdt = date("d-m-Y", strtotime($mtodate));
    ?>
    <link href="MyStyle/rep_format.css" rel="stylesheet">

    <style>
        h5 {
            font-size: 18px;
            margin-top: 7px;
            margin-left: 6px;
        }

        .report {
            margin-top: 0px;
        }
    </style>
    </head>

    <body>
        <div class="row" style="margin-top: 15px;">
            <div class="col-lg-6" style="width:100%;">
                <div class="box">
                    <?php
                    $gsumm = 0;
                    $acc = mysqli_query($conn, "SELECT a.*, b.name FROM `$mfile` a, accmast b WHERE a.vouchno != '' AND 
                                                                       a.party = b.accode and a.`billamt` > 0 AND `paidby` != 'C' $acond $scond1 GROUP BY a.party order by b.name") or die(mysqli_error($conn));
                    $getno = mysqli_num_rows($acc);
                    include("includes/head_rep.php");
                    ?>
                    <div id="sortableTable" class="body collapse in">
                        <table class="table table-bordered sortableTable responsive-table">
                            <table style="width:100%;" id="" class="table-bordered sortableTable responsive-table report">
                                <thead>
                                    <tr class="mySearchTr">
                                        <th style="width:30%" colspan='3'>Name</th>
                                        <th style="text-align:right;width:10%">Amount</th>
                                        <th style="text-align:right;width:50%" colspan='2'></th>
                                        <?php if ($mtype == "R") { ?>
                                            <th style="text-align:right;width:10%" colspan=''></th>
                                        <?php } ?>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                    while ($getac = mysqli_fetch_array($acc)) {
                                        $maccode = $getac["party"];
                                        $macname = $getac["name"];
                                        $salemsan = $getac["salesman"];

                                        $sub1 = mysqli_query($conn, "SELECT  SUM($fintot) AS amt1 FROM $mfile WHERE `party` = '$maccode' AND `billamt` > 0 AND `paidby` != 'C' $scond ") or die(mysqli_error($conn));

                                        $sub2 = mysqli_query($conn, "SELECT  SUM(`billamt`) AS amt2 FROM `prevbill` WHERE `accode` = '$maccode' AND `cashcred` != 'C' $scond ") or die(mysqli_error($conn));
                                        $msub1 = mysqli_fetch_array($sub1);
                                        $msub2 = mysqli_fetch_array($sub2);
                                        $msamt1 = $msub1["amt1"];

                                        $msamt2 = $msub2["amt2"];
                                        $qamt1 = $msamt1 + $msamt2;

                                        $trn1 = mysqli_query($conn, "SELECT SUM(setoff) AS setoff FROM `settdbf` WHERE  `party`= '$maccode' AND `type`='$mtype' 
                                                                            $scond  ") or die(mysqli_error($conn));
                                        $gtrn1 = mysqli_fetch_object($trn1);
                                        $msetoff1 = $gtrn1->setoff;

                                        $mbalamt1 = $qamt1 - $msetoff1;

                                        if ($mbalamt1 > 0) {
                                            $ledger = 'ledger_fr.php';
                                            // class='clickable-row' data-href='$ledger?view=view&acname=$macname&fdt=01-04-2022&tdt=31-03-2023&dtype=2' [target]='_blank'
                                            echo "<tr >";
                                            ?>
                                        <td colspan='3'><a href='#' onclick='window.open("ledger_fr.php?view=view&acname=<?= $maccode ?>&fdt=2022-04-01&tdt=2023-03-31&dtype=2");return false;'><?= strtoupper($macname) ?></a></td>

                                        <?php
                                        // echo "<td colspan='3'>" . strtoupper($macname) . "</td>";
                                        echo "<td style='text-align:right'>$mbalamt1</td>";
                                        echo "<td ></td>";
                                        echo "<td ></td>";
                                        if ($mtype == "R") {
                                            echo "<td ></td>";
                                        }
                                        echo "</tr>";

                                        $gsumm = $gsumm + $mbalamt1;

                                        if ($dtype == 2) {
                                            ?>
                                            <tr style="height:10px;" class="mySearchTr">
                                                <th align="left" width="10%">Date</th>
                                                <th align="left" width="5%">Year</th>
                                                <th align="left" width="15%">Bill No.</th>
                                                <th align="right" width="15%" style="text-align:right;">Bill Amount</th>
                                                <th align="right" width="15%" style="text-align:right;">Recd. Amount</th>
                                                <th align="right" width="15%" style="text-align:right;">Balance</th>
                                                <?php if ($mtype == "R") { ?>
                                                    <th align="right" width="10%" style="text-align:right;">Due Date</th>
                                                    <?php } ?>
                                            </tr>
                                            <?php
                                            $subtmp1 = mysqli_query($conn, "SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, `invno` as billno, DATE_FORMAT(`duedt`,'%d-%m-%Y') AS duedt,
                                                                DATE_FORMAT($mdate, '%d-%m-%Y') AS `billdate`,  `$fintot` as billamt, `party` as accode
                                                                FROM $mfile WHERE `party` = '$maccode' AND `billamt` > 0 AND `paidby` != 'C' $scond
                                                                UNION
                                                                SELECT `yearno`,`type`,`vouchno`, `billno`, DATE_FORMAT(`duedt`,'%d-%m-%Y') AS duedt,DATE_FORMAT(`billdate`, '%d-%m-%Y') AS 
                                                               `billdate`, `billamt`, `accode` FROM `prevbill` WHERE `accode` = '$maccode' AND `cashcred` != 'C' $scond
                                                                AND `type` = '$ttp' $scond ") or die(mysqli_error($conn));
                                            $totbillamt = 0;
                                            $totrecdamt = 0;
                                            $totbalamt = 0;

                                            while ($murl = mysqli_fetch_object($subtmp1)) {

                                                $qyear = $murl->yearno;
                                                $qbilldate = $murl->billdate;
                                                $qbill = $murl->billno;
                                                $qvouch = $murl->vouchno;
                                                $qamt = $murl->billamt;
                                                $qparty = $murl->accode;

                                                if ($murl->duedt == '00-00-0000') {
                                                    $duedt = '';
                                                } else {
                                                    $duedt = $murl->duedt;
                                                }

                                                $dt1 = date_create($mtodate);
                                                $dt2 = date_create($qbilldate);
                                                $subtdate = $dt1 - $dt2;
                                                $mldays = $subtdate;


                                                $trn = mysqli_query($conn, "SELECT SUM(setoff) AS setoff FROM `settdbf` WHERE  `party`= '$qparty' AND `yearno` ='$qyear' AND 
                                                                       `vouchno`='$qvouch' AND `type`='$mtype' $scond ") or die(mysqli_error($conn));

                                                $gtrn = mysqli_fetch_object($trn);

                                                $msetoff = $gtrn->setoff;
                                                $mbalamt = $qamt - $msetoff;

                                                if ($mtype == "R") {
                                                    $url = "sale.php?mode=edit&vouchno=$qvouch";
                                                } else {
                                                    $url = "purchase.php?mode=edit&vouchno=$qvouch";
                                                }

                                                if ($mbalamt > 0) {

                                                    echo "<tr class='clickable-row2' data-href='$url'  style='background-color:white; height:10px;'>";
                                                    echo "<td style='text-align:left;' >$qbilldate</td>";
                                                    echo "<td style='text-align:left;' >$qyear</td>";
                                                    echo "<td style='text-align:left; color:blue' >$qbill</td>";
                                                    echo "<td style='text-align:right;' >$qamt</td>";
                                                    echo "<td style='text-align:right;' >$msetoff</td>";
                                                    echo "<td style='text-align:right;' >$mbalamt</td>";
                                                    if ($mtype == "R") {
                                                        echo "<td style='text-align:right;' >$duedt</td>";
                                                    }
                                                    echo "</tr>";

                                                    $totbillamt = $totbillamt + $qamt;
                                                    $totrecdamt = $totrecdamt + $msetoff;
                                                    $totbalamt = $totbalamt + $mbalamt;

                                                    $grbillamt = $grbillamt + $qamt;
                                                    $grrecdamt = $grrecdamt + $msetoff;
                                                    $grbalamt = $grbalamt + $mbalamt;
                                                }
                                            }
                                            echo "<tr style='height:10px;'>";
                                            echo "<td style=\"text-align:left;\" ></td>";
                                            echo "<td style=\"text-align:left;\" ></td>";
                                            echo "<td style=\"text-align:right;\" ><b>Total</b></td>";
                                            echo "<td style=\"text-align:right;\" ><b>$totbillamt</b></td>";
                                            echo "<td style=\"text-align:right;\" ><b>$totrecdamt</b></td>";
                                            echo "<td style=\"text-align:right;\" ><b>$totbalamt</b></td>";
                                            if ($mtype == "R") {
                                                echo "<td style=\"text-align:right;\" ></td>";
                                            }
                                            echo "</tr>";
                                        }
                                    }
                                }
                                if ($dtype == 1) {
                                    echo "<tr style='height:10px;font-weight:600'>";
                                    echo "<td colspan='3'  style='text-align:right'>TOTAL</td>";
                                    echo "<td style='text-align:right'>$gsumm</td>";
                                    echo "<td colspan='3'></td>";
                                    echo "</tr>";
                                } else {
                                    echo "<tr style='height:10px;font-weight:600'>";
                                    echo "<td style=\"text-align:right;\" ><b>Grand Total</b></td>";
                                    echo "<td style=\"text-align:left;\" >$tbalamt1</td>";
                                    echo "<td style=\"text-align:left;\" ></td>";
                                    echo "<td style=\"text-align:right;\" ><b>$grbillamt</b></td>";
                                    echo "<td style=\"text-align:right;\" ><b>$grrecdamt</b></td>";
                                    echo "<td style=\"text-align:right;\" ><b>$grbalamt</b></td>";
                                    if ($mtype == "R") {
                                        echo "<td style=\"text-align:right;\" >$grbalamt2</td>";
                                    }
                                    echo "</tr>";
                                }
                                ?>

                                </tbody>
                            </table>
                    </div>
                </div>
            </div>
        </div>
        <?php
    }

    if (isset($_POST["excel"])) {
        $acond = "";
        $scond = "";
        $scond1 = "";

        if (!empty($_POST["acname"])) {
            $accode = trim($_POST["acname"]);
            $acond = "AND a.`party`='$accode'";
        }

        $salesman = $_POST['salesman'];

        if (!empty($salesman)) {
            $scond1 = " and a.`salesman`='$salesman' ";
            $scond = " and `salesman`='$salesman' ";
        }

        $mtodate = $_POST["frmdt"];

        if ($_POST["hiddenval"] == "R") {
            $mfile = "salemain";
            $mtype = "R";
            $ttp = "R";
            $mdate = 'vouchdt';
            $fintot = "billamt";
            $grid = "osbill.php?para=R";
            $formname1 = "Outstanding Receivable";
        } else {
            $mfile = "purmain";
            $mtype = "P";
            $ttp = "P";
            $mdate = 'vouchdt';
            $fintot = "billamt";
            $grid = "osbill.php?para=P";
            $formname1 = "Outstanding Payable";
        }
        if ($_POST["dtype"] == 1) {
            $formname = "$formname1 - Summary";
            $dtype = 1;
        } else {
            $formname = "$formname1 - Detail";
            $dtype = 2;
        }

        $mtdt = date("d-m-Y", strtotime($mtodate));

        mysqli_query($conn, "drop temporary table if exists settdbf ") or die(mysqli_error($conn));
        if ($mtype == "R") {
            mysqli_query($conn, "create temporary table settdbf 
                                    SELECT a.*, b.salesman FROM `billsett` a, salemain b WHERE a.vouchno = b.vouchno and a.`type`='$mtype' ") or die(mysqli_error($conn));
        } else {
            mysqli_query($conn, "create temporary table settdbf 
                                    SELECT * FROM `billsett` WHERE `type`='$mtype' ") or die(mysqli_error($conn));
        }


        $contents .= "Name, Amount\n";
        $acc = mysqli_query($conn, "SELECT a.*, b.name FROM `$mfile` a, accmast b WHERE a.vouchno != '' AND 
                                           a.party = b.accode and a.`billamt` > 0 AND `paidby` != 'C' $acond $scond1 
                                           GROUP BY a.party order by b.name") or die(mysqli_error($conn));
        while ($getac = mysqli_fetch_array($acc)) {

            $maccode = $getac["party"];
            $macname = $getac["name"];

            $sub1 = mysqli_query($conn, "SELECT  SUM($fintot) AS amt1 FROM $mfile WHERE `party` = '$maccode' AND `billamt` > 0 AND `paidby` != 'C' $scond ") or die(mysqli_error($conn));

            $sub2 = mysqli_query($conn, "SELECT  SUM(`billamt`) AS amt2 FROM `prevbill` WHERE `accode` = '$maccode' AND `cashcred` != 'C' $scond ") or die(mysqli_error($conn));
            $msub1 = mysqli_fetch_array($sub1);
            $msub2 = mysqli_fetch_array($sub2);
            $msamt1 = $msub1["amt1"];

            $msamt2 = $msub2["amt2"];
            $qamt1 = $msamt1 + $msamt2;

            $trn1 = mysqli_query($conn, "SELECT SUM(setoff) AS setoff FROM `settdbf` WHERE  `party`= '$maccode' AND `type`='$mtype' ") or die(mysqli_error($conn));
            $gtrn1 = mysqli_fetch_object($trn1);
            $msetoff1 = $gtrn1->setoff;

            $mbalamt1 = $qamt1 - $msetoff1;
            $macnamev = strtoupper($macname);

            if ($mbalamt1 > 0) {

                $contents .= "$macname" . ",";
                $contents .= "$mbalamt1" . "\n";

                if ($dtype == 2) {

                    $contents .= "Date, Year, Bill No., Bill Amount, Recd. Amount, Balance, Due Date\n";

                    $subtmp1 = mysqli_query($conn, "SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, `invno` as billno, DATE_FORMAT(`duedt`,'%d-%m-%Y') AS duedt,
                                                                DATE_FORMAT($mdate, '%d-%m-%Y') AS `billdate`,  `$fintot` as billamt, `party` as accode 
                                                                FROM $mfile WHERE `party` = '$maccode' AND `billamt` > 0 AND `paidby` != 'C' $scond 
                                                                UNION
                                                                SELECT `yearno`,`type`,`vouchno`, `billno`, DATE_FORMAT(`duedt`,'%d-%m-%Y') AS duedt, DATE_FORMAT(`billdate`, '%d-%m-%Y') AS 
                                                               `billdate`, `billamt`, `accode` FROM `prevbill` WHERE `accode` = '$maccode' AND `cashcred` != 'C'
                                                                AND `type` = '$ttp' $scond ") or die(mysqli_error($conn));
                    $totbillamt = 0;
                    $totrecdamt = 0;
                    $totbalamt = 0;

                    while ($murl = mysqli_fetch_object($subtmp1)) {

                        $qyear = $murl->yearno;
                        $qbilldate = $murl->billdate;
                        $qbill = $murl->billno;
                        $qvouch = $murl->vouchno;
                        $qamt = $murl->billamt;
                        $qparty = $murl->accode;

                        if ($murl->duedt == '00-00-0000') {
                            $duedt = '';
                        } else {
                            $duedt = $murl->duedt;
                        }

                        $dt1 = date_create($mtodate);
                        $dt2 = date_create($qbilldate);
                        $subtdate = $dt1 - $dt2;
                        $mldays = $subtdate;


                        $trn = mysqli_query($conn, "SELECT SUM(setoff) AS setoff FROM `settdbf` WHERE  `party`= '$qparty' AND `yearno` ='$qyear' AND 
                                                                       `vouchno`='$qvouch' AND `type`='$mtype' $scond ") or die(mysqli_error($conn));

                        $gtrn = mysqli_fetch_object($trn);

                        $msetoff = $gtrn->setoff;
                        $mbalamt = $qamt - $msetoff;

                        if ($mbalamt > 0) {

                            $contents .= "$qbilldate" . ",";
                            $contents .= "$qyear" . ",";
                            $contents .= "$qbill" . ",";
                            $contents .= "$qamt" . ",";
                            $contents .= "$msetoff" . ",";
                            if ($mtype == "R") {
                                $contents .= "$mbalamt" . ",";
                                $contents .= "$duedt" . "\n";
                            } else {
                                $contents .= "$mbalamt" . "\n";
                            }
                            $totbillamt = $totbillamt + $qamt;
                            $totrecdamt = $totrecdamt + $msetoff;
                            $totbalamt = $totbalamt + $mbalamt;
                            $totbalamt1 = $totbalamt1 + $mbalamt1;

                            $grbillamt = $grbillamt + $qamt;
                            $grrecdamt = $grrecdamt + $msetoff;
                            $grbalamt = $grbalamt + $mbalamt;
                            $grbalamt1 = $grbalamt1 + $mbalamt1;
                        }
                    }
                    $contents .= "" . ",";
                    $contents .= "" . ",";
                    $contents .= "Total" . ",";
                    $contents .= "$totbillamt" . ",";
                    $contents .= "$totrecdamt" . ",";
                    $contents .= "$totbalamt" . ",";
                    $contents .= "" . "\n";
                }
            }
        }
        $contents .= "Grand Total" . ",";
        $contents .= "$totbalamt1" . ",";
        $contents .= "" . ",";
        $contents .= "$grbillamt" . ",";
        $contents .= "$grrecdamt" . ",";
        $contents .= "$grbalamt" . ",";
        $contents .= "" . "\n";

        $contents = strip_tags($contents);

        if ($_POST["dtype"] == 1) {
            header("Content-Disposition: attachment; filename=$formname" . "_Summary" . date('d-m-Y') . ".csv");
            print $contents;
        } else {
            header("Content-Disposition: attachment; filename=$formname" . "_Detail" . date('d-m-Y') . ".csv");
            print $contents;
        }
    }
    ?>