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

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

$zroot = $_SESSION['zdir'];

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

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

    $acond = " AND a.`accode`='$accode'";
}
$mtodate = $_POST["frmdt"];

$mfile = "salemain";
$mtype = "R";
$mdate = 'vouchdt';
$fintot = "billamt";
$grid = "osbill.php?para=R";
$formname1 = "Outstanding Receivable";

$formname = "$formname1";
if ($_POST["dtype"] == 1) {
    $dtype = 1;
} else {
    $dtype = 2;
}
mysqli_query($conn, "create temporary table cmain 
                    SELECT a.party, b.name  FROM `salemain` a, accmast b WHERE a.party = b.accode and a.vouchno != '' and a.vouchdt <= '$mtodate' and a.paidby != 'C' $pcond 
                    UNION
                    SELECT a.party, b.name FROM `deexmain` a, accmast b  WHERE a.party = b.accode and a.vouchno != '' and a.vouchdt <= '$mtodate' and a.paidby != 'C' $pcond 
                    UNION
                    SELECT a.partyname as party, b.name  FROM `salemainexp` a, accmast b WHERE a.partyname = b.accode and a.vouchno != '' and a.invdate <= '$mtodate' $pcond2
                    UNION
                    SELECT a.party, b.name  FROM `slonlmain` a, accmast b WHERE a.party = b.accode and a.vouchno != '' and a.vouchdt <= '$mtodate' and a.paidby != 'C' $pcond 
                    UNION                    
                    SELECT a.accode, b.name  FROM `prevbill` a, accmast b WHERE a.accode = b.accode and a.billdate <= '$mtodate' and a.type = 'R' $acond") or die(mysqli_error($conn));


$acc = mysqli_query($conn, "SELECT party, name FROM cmain group by party order by name ") or die(mysqli_error($conn));
//print table($acc);
//$getno = mysqli_num_rows($acc);

if (isset($_POST["view"])) {
    include("includes/linkshead.php");

    $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 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 table table-bordered">
                                <thead>
                                    <tr class="mySearchTr">
                                        <th style="" colspan="5">Name</th>
                                        <th style="text-align: right;">Amount</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                    $totbalamt = 0;
                                    while ($getac = mysqli_fetch_array($acc)) {

                                        $maccode = $getac["party"];
//                                        $gparty = mysqli_query($conn, "SELECT name from accmast where accode = '$maccode' ")or die(mysqli_error($conn));
//                                        $fparty = mysqli_fetch_array($gparty);
                                        $macname = $getac["name"];
//browse($maccode);
                                        $sub1 = mysqli_query($conn, "SELECT SUM($fintot) AS amt1 FROM salemain WHERE `party` = '$maccode'  AND `paidby` != 'C' and vouchdt <= '$mtodate'") or die(mysqli_error($conn));
                                        $sub2 = mysqli_query($conn, "SELECT SUM($fintot) AS amt2 FROM deexmain WHERE `party` = '$maccode' AND `paidby` != 'C' and vouchdt <= '$mtodate'") or die(mysqli_error($conn));
                                        $sub3 = mysqli_query($conn, "SELECT SUM($fintot) AS amt3 FROM salemainexp WHERE `partyname` = '$maccode' and invdate <= '$mtodate'") or die(mysqli_error($conn));
                                        $sub5 = mysqli_query($conn, "SELECT SUM($fintot) AS amt5 FROM slonlmain WHERE `party` = '$maccode'  AND `paidby` != 'C' and vouchdt <= '$mtodate'") or die(mysqli_error($conn));

                                        $sub4 = mysqli_query($conn, "SELECT SUM(`billamt`) AS amt4 FROM `prevbill` WHERE `accode` = '$maccode' and billdate <= '$mtodate'") or die(mysqli_error($conn));

                                        $msub1 = mysqli_fetch_array($sub1);
                                        $msub2 = mysqli_fetch_array($sub2);
                                        $msub3 = mysqli_fetch_array($sub3);
                                        $msub4 = mysqli_fetch_array($sub4);
                                        $msub5 = mysqli_fetch_array($sub5);

                                        $msamt1 = $msub1["amt1"];
                                        $msamt2 = $msub2["amt2"];
                                        $msamt3 = $msub3["amt3"];
                                        $msamt4 = $msub4["amt4"];
                                        $msamt5 = $msub4["amt5"];

                                        $qamt1 = $msamt1 + $msamt2 + $msamt3 + $msamt4 + $msamt5 ;


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


                                        $mbalamt1 = $qamt1 - $msetoff1;

                                        if ($mbalamt1 > 0) {
                                            echo "<tr style='background-color:white'>";
                                            echo "<td colspan='5' style=\"text-align:left;\">" . strtoupper($macname) . "</td>";
                                            echo "<td style=\"text-align:right;\">" . number_format($mbalamt1, 2) . "</td>";
                                            echo "</tr>";
                                            $grtotbalamt = $grtotbalamt + $mbalamt1;
                                            if ($dtype == 2) {
                                                ?>        

                                                <tr style="height:10px;" class="mySearchTr">
                                                    <th align="left" width="15%">Date</th>                                   
                                                    <th align="left" width="15%">Bill No.</th>                                   
                                                    <th align="right" width="20%" style="text-align:right;">Bill Amount</th>                                   
                                                    <th align="right" width="20%" style="text-align:right;">Received Amount</th>                                   
                                                    <th align="right" width="20%" style="text-align:right;">Balance</th>                                   
                                                    <th align="right" width="9%" style="text-align:right;">Days</th>                                   
                                                </tr>
                                                <?php
                                                $subtmp1 = mysqli_query($conn, "SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, `invno` as billno,
                                                                        DATE_FORMAT($mdate, '%d-%m-%Y') AS `billdate`,  `$fintot` as billamt, `party` as accode 
                                                                        FROM salemain WHERE `party` = '$maccode' AND `paidby` != 'C' and vouchdt <= '$mtodate'
                                                                        UNION
                                                                        SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, `invno` as billno,
                                                                        DATE_FORMAT($mdate, '%d-%m-%Y') AS `billdate`,  `$fintot` as billamt, `party` as accode 
                                                                        FROM deexmain WHERE `party` = '$maccode' AND `paidby` != 'C' and vouchdt <= '$mtodate'
                                                                        UNION
                                                                        SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, `invoice` as billno,
                                                                        DATE_FORMAT(invdate, '%d-%m-%Y') AS `billdate`,  `$fintot` as billamt, `partyname` as accode 
                                                                        FROM salemainexp WHERE `partyname` = '$maccode'  and invdate <= '$mtodate'
                                                                        UNION
                                                                        SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, `invno` as billno,
                                                                        DATE_FORMAT($mdate, '%d-%m-%Y') AS `billdate`,  `$fintot` as billamt, `party` as accode 
                                                                        FROM slonlmain WHERE `party` = '$maccode' AND `paidby` != 'C' and vouchdt <= '$mtodate'
                                                                        UNION                                                                        
                                                                        SELECT `yearno`,`type`,`vouchno`, `billno`, DATE_FORMAT(`billdate`, '%d-%m-%Y') AS 
                                                                        `billdate`, `billamt`, `accode` FROM `prevbill` WHERE `accode` = '$maccode' 
                                                                        AND `type` = '$mtype' and billdate <= '$mtodate'") 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;

                                                    $dt1 = date_create($mtodate);
                                                    $dt2 = date_create($qbilldate);

                                                    $subtdate = date_diff($dt1, $dt2);
                                                    $mldays = $subtdate->format('%a');

                                                    $trn = mysqli_query($conn, "SELECT SUM(setoff) AS setoff FROM `billsett` 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 (round($mbalamt) > 0) {
                                                        $totbillamt = $totbillamt + $qamt;
                                                        $totrecdamt = $totrecdamt + $msetoff;
                                                        $totbalamt = $totbalamt + $mbalamt;
                                                        echo "<tr style='height:10px;background-color:white'>";
                                                        echo "<td style=\"text-align:left;\" scope=\"col\" width=\"300\">$qbilldate</td>";
                                                        echo "<td style=\"text-align:left;\" scope=\"col\" width=\"100\">$qbill</td>";
                                                        echo "<td style=\"text-align:right;\" scope=\"col\" width=\"300\">$qamt</td>";
                                                        echo "<td style=\"text-align:right;\" scope=\"col\" width=\"200\">$msetoff</td>";
                                                        echo "<td style=\"text-align:right;\" scope=\"col\" width=\"200\">$mbalamt</td>";
                                                        echo "<td style=\"text-align:right;\" scope=\"col\" width=\"200\">$mldays</td>";
                                                        echo "</tr>";
                                                    }
                                                }
                                                echo "<tr style='height:10px;background-color:white'>";
                                                echo "<td style=\"text-align:left;\" scope=\"col\" width=\"300\"></td>";
                                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"100\"><b>Total</b></td>";
                                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"300\"><b>$totbillamt</b></td>";
                                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"200\"><b>$totrecdamt</b></td>";
                                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"200\"><b>$totbalamt</b></td>";
                                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"200\"></td>";
                                                echo "</tr>";
                                            }
                                        }
                                    }
                                    echo "<tr style='background-color:white'>";
                                    echo "<td colspan='5' style=\"text-align:right;font-size:15px\"><b>Total</b></td>";
                                    echo "<td style=\"text-align:right;font-size:15px\">";
                                    echo "<b>$grtotbalamt</b>";
                                    echo "</td>";
                                    echo "</tr>";
                                    ?>

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

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

        $contents .= "Name,Amount\n";

        $totbalamt = 0;
        while ($getac = mysqli_fetch_array($acc)) {

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

            $sub1 = mysqli_query($conn, "SELECT SUM($fintot) AS amt1 FROM salemain WHERE `party` = '$maccode' AND `paidby` != 'C'") or die(mysqli_error($conn));
            $sub2 = mysqli_query($conn, "SELECT SUM($fintot) AS amt2 FROM deexmain WHERE `party` = '$maccode' AND `paidby` != 'C' ") or die(mysqli_error($conn));
            $sub3 = mysqli_query($conn, "SELECT SUM($fintot) AS amt3 FROM salemainexp WHERE `partyname` = '$maccode' ") or die(mysqli_error($conn));
            $sub5 = mysqli_query($conn, "SELECT SUM($fintot) AS amt1 FROM slonlmain WHERE `party` = '$maccode' AND `paidby` != 'C'") or die(mysqli_error($conn));

            $sub4 = mysqli_query($conn, "SELECT SUM(`billamt`) AS amt4 FROM `prevbill` WHERE `accode` = '$maccode' ") or die(mysqli_error($conn));

            $msub1 = mysqli_fetch_array($sub1);
            $msub2 = mysqli_fetch_array($sub2);
            $msub3 = mysqli_fetch_array($sub3);
            $msub4 = mysqli_fetch_array($sub4);
            $msub5 = mysqli_fetch_array($sub5);

            $msamt1 = $msub1["amt1"];
            $msamt2 = $msub2["amt2"];
            $msamt3 = $msub2["amt3"];
            $msamt4 = $msub2["amt4"];
            $msamt5 = $msub1["amt5"];
            
            $qamt1 = $msamt1 + $msamt2 + $msamt3 + $msamt4 + $msamt5 ; 


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

            $mbalamt1 = $qamt1 - $msetoff1;

            if ($mbalamt1 > 0) {

                $contents .= strtoupper($macname) . ",";
                $contents .= "$mbalamt1" . "\n";

                $grtotbalamt = $grtotbalamt + $mbalamt1;
                if ($dtype == 2) {

                    $contents .= "Date, Bill No., Bill Amount, Received Amount, Balance, Days\n";


                    $subtmp1 = mysqli_query($conn, "SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, `invno` as billno,
                                                                        DATE_FORMAT($mdate, '%d-%m-%Y') AS `billdate`,  `$fintot` as billamt, `party` as accode 
                                                                        FROM salemain WHERE `party` = '$maccode' AND `paidby` != 'C' 
                                                                        UNION
                                                                        SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, `invno` as billno,
                                                                        DATE_FORMAT($mdate, '%d-%m-%Y') AS `billdate`,  `$fintot` as billamt, `party` as accode 
                                                                        FROM deexmain WHERE `party` = '$maccode' AND `paidby` != 'C' 
                                                                        UNION
                                                                        SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, `invoice` as billno,
                                                                        DATE_FORMAT(invdate, '%d-%m-%Y') AS `billdate`,  `$fintot` as billamt, `partyname` as accode 
                                                                        FROM salemainexp WHERE `partyname` = '$maccode'  
                                                                        UNION
                                                                        SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, `invno` as billno,
                                                                        DATE_FORMAT($mdate, '%d-%m-%Y') AS `billdate`,  `$fintot` as billamt, `party` as accode 
                                                                        FROM slonlmain WHERE `party` = '$maccode' AND `paidby` != 'C' 
                                                                        UNION
                                                                        SELECT `yearno`,`type`,`vouchno`, `billno`, DATE_FORMAT(`billdate`, '%d-%m-%Y') AS 
                                                                        `billdate`, `billamt`, `accode` FROM `prevbill` WHERE `accode` = '$maccode' 
                                                                        AND `type` = '$mtype'") 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;

                        $dt1 = date_create($mtodate);
                        $dt2 = date_create($qbilldate);

                        $subtdate = date_diff($dt1, $dt2);
                        $mldays = $subtdate->format('%a');

                        $trn = mysqli_query($conn, "SELECT SUM(setoff) AS setoff FROM `billsett` 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 ($mbalamt > 0) {
                            $totbillamt = $totbillamt + $qamt;
                            $totrecdamt = $totrecdamt + $msetoff;
                            $totbalamt = $totbalamt + $mbalamt;

                            $contents .= $qbilldate . ",";
                            $contents .= $qbill . ",";
                            $contents .= $qamt . ",";
                            $contents .= $msetoff . ",";
                            $contents .= $mbalamt . ",";
                            $contents .= $mldays . "\n";
                        }
                    }


                    $contents .= ",Total" . ",";
                    $contents .= $totbillamt . ",";
                    $contents .= $totrecdamt . ",";
                    $contents .= $totbalamt . ",";
                    $contents .= "\n";
                }
            }
        }

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