<?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 = "";

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

    if ($_POST["dtype"] == 1) {
        $repname = "$formname - Summary";
        $dtype = 1;
    } else {
        $repname = "$formname - Detail";
        $dtype = 2;
    }
    if ($_POST["hiddenval"] == "R") {
        $formname = "Outstanding Receivable";
        $mfile = "salemain";
        $mtype = "S";
        $mdate = 'vouchdt';
        $fintot = "billamt";
        $grid = "osbill.php?para=R";
    } else {
        $formname = "Outstanding Payable";
        $mfile = "purmain";
        $mtype = "P";
        $fintot = "nettotal";
        $mdate = 'vouchdt';
        $grid = "osbill.php?para=P";
    }
    include("includes/linkshead.php");
    ?>
    <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">
                            <thead>
                                <tr>
                                    <th style="width: 70%">Name</th>
                                    <th style="text-align: right; width: 30%">Amount</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php
                                $acc = mysqli_query($conn, "SELECT a.*, b.name FROM $mfile a, accmast b WHERE a.vouchno!='' AND a.accode = b.accode $acond $scond GROUP BY a.accode order by b.name") or die(mysqli_error($conn));
                                while ($getac = mysqli_fetch_array($acc)) {

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


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

                                    $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>";
                                        echo "<td style=\"text-align:left;\">" . strtoupper($macname) . "</td>";
                                        echo "<td style=\"text-align:right;\">";
                                        if ($dtype == 1) {
                                            echo "$mbalamt1";
                                        }
                                        echo "</td>";
                                        echo "</tr>";
                                        if ($dtype == 2) {
                                            ?>        
                                            <tr style="height:10px;">
                                                <th align="left" width="15%">Date<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                   
                                                <th align="left" width="15%">Bill No.<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                   
                                                <th align="right" width="20%" style="text-align:right;">Bill Amount<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                   
                                                <th align="right" width="20%" style="text-align:right;">Recd. Amount<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                   
                                                <th align="right" width="20%" style="text-align:right;">Balance<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                   
                                                <th align="right" width="9%" style="text-align:right;">Days<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                   
                                            </tr>
                                            <?php
                                            $subtmp1 = mysqli_query($conn, "SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, `billno`,
                                                                DATE_FORMAT($mdate, '%d-%m-%Y') AS `billdate`, `$fintot`, `accode` 
                                                                FROM $mfile WHERE `accode` = '$maccode' AND `cashcred` != '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 = $dt1 - $dt2;
                                                $mldays = $subtdate;


                                                $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;
                                                    echo "<tr style='height:10px;'>";
                                                    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;'>";
                                            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>";
//                                    echo "<td style=\"text-align:left;\" scope=\"col\" width=\"200\"></td>";
//                                    echo "<td style=\"text-align:left;\" scope=\"col\" width=\"200\"></td>";
//                                    echo "<td style=\"text-align:left;\" scope=\"col\" width=\"200\"><strong class='text-right'>Total</strong></td>";
//                                    echo "<td style=\"text-align:right;\" scope=\"col\" width=\"200\">$atot</td>";
//                                    echo "<td style=\"text-align:right;\" scope=\"col\" width=\"200\">$rtot</td>";
//                                    echo "<td style=\"text-align:right;\" scope=\"col\" width=\"200\">$btot</td>";
//                                    echo "</tr>";
                                ?>

                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
        <?php
    }
    if (isset($_POST["excel"])) {
        $acond = "";
        $scond = "";

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

        if ($_POST["dtype"] == 1) {
            $repname = "$formname - Summary";
            $dtype = 1;
        } else {
            $repname = "$formname - Detail";
            $dtype = 2;
        }
        if ($_POST["hiddenval"] == "R") {
            $formname = "Outstanding Receivable";
            $mfile = "salemain";
            $mtype = "S";
            $mdate = 'vouchdt';
            $fintot = "billamt";
            $grid = "osbill.php?para=R";
        } else {
            $formname = "Outstanding Payable";
            $mfile = "purmain";
            $mtype = "P";
            $fintot = "nettotal";
            $mdate = 'vouchdt';
            $grid = "osbill.php?para=P";
        }
        $contents.= "Name,Amount\n";


        $acc = mysqli_query($conn, "SELECT a.*, b.name FROM $mfile a, accmast b WHERE a.vouchno!='' AND a.accode = b.accode $acond $scond GROUP BY a.accode order by b.name") or die(mysqli_error($conn));
        while ($getac = mysqli_fetch_array($acc)) {

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


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

            $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)."," ;
                if ($dtype == 1) {
                    $contents.=$mbalamt1 . "\n";
                }
                if ($dtype == 2) {

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


                    $subtmp1 = mysqli_query($conn, "SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, `billno`,
                                                                DATE_FORMAT($mdate, '%d-%m-%Y') AS `billdate`, `$fintot`, `accode` 
                                                                FROM $mfile WHERE `accode` = '$maccode' AND `cashcred` != '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 = $dt1 - $dt2;
                        $mldays = $subtdate;


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



