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

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

$formname = "No Payment From Customer - Datewise";
$file = "custnopay.php";

$maindb = "accmast";
$trandb = "actran";

mysqli_select_db($conn, $zdb);

$gcond = "";
$accond = "";
$stcond = "";
$ctcond = "";

if (!empty($_POST["acname"])) {
    $accode = trim($_POST["acname"]);
    $accond = " and accode = '$accode' ";
}

$mfrmdate = $_POST["frmdt"];
$mtodate = $_POST["todt"];

if (empty($mfrmdate)) {
    browse("From Date Cannot Be Empty");
    echo "<script>"
    . "window.close();"
    . "</script>";
}

if (empty($mtodate)) {
    browse("To Date Cannot Be Empty");
    echo "<script>"
    . "window.close();"
    . "</script>";
}

if ($mfrmdate > $mtodate) {
    browse("To Date Cannot Be Less Than From Date");
    header("refresh: 1; custsale.php");
    exit();
}

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

    $getacc = mysqli_query($conn, "SELECT * FROM `accmast` WHERE grpcode = '010' $accond order by name") or die(mysqli_error($conn));
    $fetacc = mysqli_fetch_array($getacc);
    $acname = $fetacc['name'];

    $gettran = mysqli_query($conn, "SELECT * FROM `accmast` WHERE grpcode = '010' and accode in (Select accode from actran
                            where date < '$mfrmdate' and (source = 'SL' or source = 'SLTX')) and accode not in 
                            (select accode from actran where amount > 0 and date >= '$mfrmdate' and date <= '$mtodate') order by name") or die(mysqli_error($conn));
//    print table ($gettran);
//    die() ;


    include("includes/linkshead.php");
    $mfdt = date("d-m-Y", strtotime($mfrmdate));
    $mtdt = date("d-m-Y", strtotime($mtodate));
    ?>
    <style>
        table th{
            background-color: #337AB7;
        }
    </style>
    <script type="text/javascript">
        $(document).ready(function () {
            //        left side
            $(".dtlsale").hide();
            $(".dtlsale2").hide();
            $("body").on('dblclick', '.getdtl', getDetails);
            $("body").on('dblclick', '.getdtl2', getDetails2);
            $("body").on('dblclick', '.dtlhide1', hideDetails);
            $("body").on('dblclick', '.dtlhide2', hideDetails2);
            function getDetails() {
                var trid = $(this).closest('tr').attr('id');
                var idval = trid.split('_')[1];
                $("#dtlsale_" + idval).show();
            }
            function getDetails2() {
                var trid = $(this).closest('tr').attr('id');
                var idval = trid.split('_')[1];
                $("#dtlsale2_" + idval).show();
            }
            function hideDetails() {
                var trid = $(this).closest('tr').attr('id');
                var idval = trid.split('_')[1];
                $("#dtlsale_" + idval).hide();
            }
            function hideDetails2() {
                var trid = $(this).closest('tr').attr('id');
                var idval = trid.split('_')[1];
                $("#dtlsale2_" + idval).hide();
            }

            //        right side
            $(".rdtlsale").hide();
            $(".rdtlsale2").hide();
            $("body").on('dblclick', '.rgetdtl', getDetailsr);
            $("body").on('dblclick', '.rgetdtl2', getDetailsr2);
            $("body").on('dblclick', '.rdtlhide1', hideDetailsr);
            $("body").on('dblclick', '.rdtlhide2', hideDetailsr2);
            function getDetailsr() {
                var trid = $(this).closest('tr').attr('id');
                var idval = trid.split('_')[1];
                $("#rdtlsale_" + idval).show();
            }
            function getDetailsr2() {
                var trid = $(this).closest('tr').attr('id');
                var idval = trid.split('_')[1];
                $("#rdtlsale2_" + idval).show();
            }
            function hideDetailsr() {
                var trid = $(this).closest('tr').attr('id');
                var idval = trid.split('_')[1];
                $("#rdtlsale_" + idval).hide();
            }
            function hideDetailsr2() {
                var trid = $(this).closest('tr').attr('id');
                var idval = trid.split('_')[1];
                $("#rdtlsale2_" + idval).hide();
            }
        });

    </script>


    </head>

    <body id="ledger">   
        <div class="row" style="padding : 1%">
            <div class="col-lg-1"></div>
            <div class="col-lg-10">
                <?php include ("includes/head_rep.php"); ?>
                <!--            </div>
                            <div class="col-lg-8">-->

                <div class="box">
                    <div id="sortableTable" class="body collapse in">
                        <table class="table table-bordered sortableTable responsive-table">

                            <thead>
                                <tr>
                                    <th style="width:5%">Sr.</th>
                                    <th style="width:35%">Customer Name</th>
                                    <th style="width:7%">Mobile No.</th>
                                    <th style="width:15%">City</th>
                                    <th style="width:10%">Last Paid</th>
                                    <th style="width:8%">Bank/Cash</th>
                                    <th style="width:10%">Amount</th>
                                </tr>
                            </thead>
                            <tbody class="tbody">

                                <?php
                                $totcash = $totbank = $tottrf = $mnctr = 0;

                                while ($fetacc = mysqli_fetch_array($gettran)) {

                                    $accode = $fetacc['accode'];
                                    $name = $fetacc['name'];
                                    $city = $fetacc[city];
                                    $mobile = $fetacc[mobile];

                                    $getbill = mysqli_query($conn, "SELECT *, DATE_FORMAT(date,'%d-%m-%Y') as mydt from actran 
                                                            where accode = '$accode' and date < '$mfrmdate' and amount > 0  order by date desc limit 0,1") or die(mysqli_error($conn));
                                    $fetbill = mysqli_fetch_array($getbill);
                                    $norows = mysqli_num_rows($getbill);

                                    if ($norows > 0) {
                                        $source = $fetbill['source'];
                                        $vouchno = $fetbill['vouchno'];
                                        $billdate = $fetbill['mydt'];
                                        $billamt = abs($fetbill['amount']);
                                    } else {
                                        $vouchno = 'Not Found';
                                        $billdate = '';
                                        $source = '';
                                        $billamt = '';
                                    }
                                    $mnctr++;

                                    echo "<tr>";
                                    echo "<td>$mnctr</td>";
                                    echo "<td>$name</td>";
                                    echo "<td>$mobile</td>";
                                    echo "<td>$city</td>";
                                    echo "<td>$billdate</td>";
                                    echo "<td>$source</td>";
                                    echo "<td class='text-right'>" . number_format($billamt, 2) . "</td>";
                                    echo "</tr>";
                                }
                                ?>
                            </tbody>
                        </table>    
                    </div>
                </div>
            </div>
        </div>
        <?php include("includes/linksfoot.php"); ?>
    </body>
    </html>
    <?php
}

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


    $getacc = mysqli_query($conn, "SELECT * FROM `accmast` WHERE grpcode = '010' $accond order by name") or die(mysqli_error($conn));
    $fetacc = mysqli_fetch_array($getacc);
    $acname = $fetacc['name'];

    $gettran = mysqli_query($conn, "SELECT * FROM `accmast` WHERE grpcode = '010' and accode in (Select accode from actran
                            where date < '$mfrmdate' and (source = 'SL' or source = 'SLTX')) and accode not in 
                            (select accode from actran where amount > 0 and date >= '$mfrmdate' and date <= '$mtodate') order by name") or die(mysqli_error($conn));

    $contents .= "Customer," . $acname['name'] . "\n";

    $contents .= "Sr,Customer Name,Mobile no,City,Billdate,Source,Amount\n";

    $ctr = 1;


    while ($fetacc = mysqli_fetch_array($gettran)) {

        $accode = $fetacc['accode'];
        $name = $fetacc['name'];
        $city = $fetacc[city];
        $mobile = $fetacc[mobile];

        $getbill = mysqli_query($conn, "SELECT *, DATE_FORMAT(date,'%d-%m-%Y') as mydt from actran 
                                                            where accode = '$accode' and date < '$mfrmdate' and amount > 0  order by date desc limit 0,1") or die(mysqli_error($conn));
        $fetbill = mysqli_fetch_array($getbill);
        $norows = mysqli_num_rows($getbill);

        if ($norows > 0) {
            $source = $fetbill['source'];
            $vouchno = $fetbill['vouchno'];
            $billdate = $fetbill['mydt'];
            $billamt = abs($fetbill['amount']);
        } else {
            $vouchno = 'Not Found';
            $billdate = '';
            $source = '';
            $billamt = '';
        }
        $mnctr++;




        $contents .= "$ctr" . ",";
        $contents .= "$name" . ",";
        $contents .= "$mobile" . ",";
        $contents .= "$city" . ",";
        $contents .= "$billdate" . ",";
        $contents .= "$source" . ",";
        $contents .= "$amt" . "\n";


        $totleft = $totleft + abs($amt);
        $totqty = $totqty + abs($qty);

        $ctr++;
    }

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

