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

function caldays($var1, $var2)
{
    if ($var1 != "0000-00-00" && $var2 != "0000-00-00") {
        $date1 = new DateTime("$var1");
        $date2 = new DateTime("$var2");

        $interval = $date1->diff($date2);
        return $interval->days;
    } else {
        return "0";
    }
}

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

$trandb = "actran";
$myear = $_SESSION["tit_" . $dbname];

$zyear = right($zdb, 4);
$mfrdate = getzdates($zyear, 'S');
$mlsdate = getzdates($zyear, 'E');
mysqli_select_db($conn, $zdb);

error_reporting(E_ERROR); // Report all PHP errors
ini_set('display_errors', 1); // Display errors on the screen



$acond = "";
$scond = "";
$scond1 = "";

$duecond = "";
$duecond1 = "";

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

$mtodate = $_POST["frmdt"];

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

    $salesman = $_POST['salesman'];
    $duedt = $_POST['duedt'];

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

    if ($duedt == '') {

    } else {
        $duecond1 = " and a.`duedt` <= '$duedt' ";
        $duecond = " and `duedt` <= '$duedt' ";
    }
} else {
    $mfile = "purmain";
    $mfile2 = "purmaingst";
    $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;
}

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.* FROM `billsett` a, sale 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));

if (isset($_POST["view"])) {
    include("includes/linkshead.php");
    ?>
    <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 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 table-condensed responsive-table report">
                                <thead>
                                    <tr class="mySearchTr">
                                        <th style="width:30%" colspan='2'>Name</th>
                                        <th style="text-align:right;width:10%" >Amount</th>
                                        <th style="text-align:right;width:60%" colspan='4'></th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                    $gsumm = 0;
                                    if ($mtype == "R") {
                                        $acc = mysqli_query($conn, "SELECT a.*, b.name FROM `$mfile` a, accmast b WHERE a.vouchno != '' AND 
                                                                       a.party = b.accode and a.`amount` > 0 
                                                                       $acond  GROUP BY a.party order by b.name") or die(mysqli_error($conn));
                                    } else {
                                        $acc = mysqli_query($conn, "SELECT * from (
                                                                       select a.party, a.salesman, a.billamt, b.name FROM `$mfile` a, accmast b WHERE a.vouchno != '' AND 
                                                                       a.party = b.accode and a.`billamt` > 0 AND `paidby` != 'C'
                                                                       $acond $scond1 $duecond1 
                                                                    union all       
                                                                       select a.party, a.salesman, a.billamt, b.name FROM `$mfile2` a, accmast b WHERE a.vouchno != '' AND 
                                                                       a.party = b.accode and a.`billamt` > 0 AND `paidby` != 'C'
                                                                       $acond $scond1 $duecond1 ) as a
                                                                    GROUP BY party order by 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 `amount` > 0 ") or die(mysqli_error($conn));


                                        $sub2 = mysqli_query($conn, "SELECT  SUM(`billamt`) AS amt2 FROM `prevbill` WHERE `accode` = '$maccode' AND `cashcred` != 'C' $scond $duecond ") 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';
                                            // 
                                            echo "<tr>";
                                            ?>
                                            <!--//                                            <td colspan='3' ><a href='$ledger?view=view&acname=$maccode&fdt=2022-04-01&tdt=2023-03-31&dtype=2' target='_blank'>" . strtoupper($macname) . "</a></td>";-->
                                            <td colspan='2'><a href='#' onclick='window.open("ledger_fr.php?view=view&acname=<?= $maccode ?>&fdt=<?= $mfrdate ?>&tdt=<?= $mlsdate ?>&dtype=2");
                                                                return false;'><?= strtoupper($macname) ?></a></td>
                                            <?php
                                            echo "<td style='text-align:right'>$mbalamt1</td>";
                                            echo "<td colspan='4'></td>";
                                            echo "</tr>";

                                            $gsumm = $gsumm + $mbalamt1;

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

                                                //    print table($subtmp1) ;
                                                //    die();
                                

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

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

                                                    $qparty = $murl->accode;


                                                    $date1 = $mtodate;
                                                    $date2 = $nbilldate;

                                                    $agdays = caldays($date1, $date2);

                                                    $trn = mysqli_query($conn, "SELECT SUM(setoff) AS setoff FROM `settdbf` WHERE `party`= '$qparty' 
                                                                                       AND `yearno` ='$qyear' AND `vouchno`='$qvouch' 
                                                                                       AND `type`='$mtype' ") 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>";
                                                        echo "<td style='text-align:right;' >$agdays</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>";
                                                echo "</tr>";
                                            }
                                        }
                                    }
                                    if ($dtype == 1) {
                                        echo "<tr style='height:10px; background-color:cyan;'>";
                                        echo "<td colspan='2'  style='text-align:right'>TOTAL</td>";
                                        echo "<td style='text-align:right'>$gsumm</td>";
                                        echo "<td colspan='4'></td>";
                                        echo "</tr>";
                                    } else {
                                        echo "<tr style='height:10px; background-color:cyan;'>";
                                        echo "<td style=\"text-align:left;\"></td>";
                                        echo "<td style=\"text-align:left;\" ></td>";
                                        echo "<td style=\"text-align:right;\" ><b>Grand Total</b></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"])) {


    $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 $duecond1
                                           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 $duecond ") or die(mysqli_error($conn));

        $sub2 = mysqli_query($conn, "SELECT  SUM(`billamt`) AS amt2 FROM `prevbill` WHERE `accode` = '$maccode' AND `cashcred` != 'C' $scond $duecond ") 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 $duecond
                                                                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 $duecond ") 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;


                    $date1 = $mtodate;
                    $date2 = $nbilldate;

                    $diff = abs(strtotime($date2) - strtotime($date1));

                    $years = floor($diff / (365 * 60 * 60 * 24));
                    $months = floor(($diff - $years * 365 * 60 * 60 * 24) / (30 * 60 * 60 * 24));
                    $days = floor(($diff - $years * 365 * 60 * 60 * 24 - $months * 30 * 60 * 60 * 24) / (60 * 60 * 24));

                    $mldays = $days;

                    $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" . ",";
                        $contents .= "$mbalamt" . "\n";
                        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;
    }
}
?>