<?php
include("../../classes/manager.class.php");
//ini_set('display_errors', 1);
error_reporting(0);
$m = new Manager();
$output = '';
$datefrom = "";
$dateto = "";
if (!isset($_SESSION['sess_userid'])) {
    header("Location: " . PROJECT_ADMIN_URL);
    exit();
}
extract($_REQUEST);
if ($datefrom == "") {
    $datefrom = date('Y-m-d', strtotime("-30 days"));
} else {
    $datefrom = date("Y-m-d", strtotime($datefrom));
}
if ($dateto == "") {
    $dateto = date('Y-m-d');
} else {
    $dateto = date("Y-m-d", strtotime($dateto));
}
$datefrom = date("d-m-Y", strtotime($datefrom));
$dateto = date("d-m-Y", strtotime($dateto));

$qryDate = date("Y-m-d", strtotime($datefrom));
$qryTo = date("Y-m-d", strtotime($dateto));


$filterReport = " JOIN customer_setup AS cs ON cs.id = inv.customer_id WHERE DATE_FORMAT(inv.create_date,'%Y-%m-%d')>='$qryDate' and DATE_FORMAT(inv.create_date,'%Y-%m-%d')<='$qryTo'  AND inv.status not in('Draft', 'Cancel') ";

if (isset($cat_type) && $cat_type == 'All') {
    if (isset($cat_name) && $cat_name != 'All') {
        $cat = CustomerCategorySetup::GetCustomerCategorySetupById($cat_name);
        $filterCat = " WHERE category_id = '$cat->id' AND status = 'active' ORDER BY name";
        $cust = CustomerSetup::GetCustomerSetupList($filterCat);
        $custArray = array();
        foreach($cust AS $custRow){
            $custArray[] = $custRow->id;
        }
        $customers = implode(',', $custArray);
        $filterReport .= " AND inv.customer_id IN (".$customers.")";
    }
}else{
    $filterReport .= " AND cs.is_mart = '$cat_type' ";
}

if (isset($customer_name) && $customer_name != 'All') {
    $filterReport .= " AND inv.customer_id ='$customer_name'";
}

$filterReport .= " AND cs.status ='active' ";

$filterReport .= " UNION SELECT 'payment' AS nameTable, pay.create_date, pay.customer_id, pay.amount,
'', '', '','', pay.status

 from " . TABLE_PAYMENT . " AS pay 
 
 JOIN customer_setup AS cs ON cs.id = pay.customer_id

 WHERE DATE_FORMAT(pay.create_date,'%Y-%m-%d')>='$qryDate' and DATE_FORMAT(pay.create_date,'%Y-%m-%d')<='$qryTo' AND pay.status <> 'Cancel' ";

if (isset($cat_type) && $cat_type == 'All') {
    if (isset($cat_name) && $cat_name != 'All') {
        $filterReport .= " AND pay.customer_id IN (".$customers.")";
    }
}else{
    $filterReport .= " AND cs.is_mart = '$cat_type' ";
}

if (isset($customer_name) && $customer_name != 'All') {
    $filterReport .= " AND pay.customer_id ='$customer_name'";
}

$filterReport .= " AND cs.status ='active'";

$filterReport .= " ORDER BY customer_id";


//Opening Balance


//if (isset($customer_name) && $customer_name != 'All') {
//    $filterReportOpening .= " AND pay.customer_id ='$customer_name'";
//}
//$filterReportOpening .= " ORDER BY create_date";
//Opening Balance
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <?php include('../includes/inc_head.php'); ?>
    <style>
        table{
            font-size:24px;
        }
    </style>
</head>
<body>
<!-- Start Page Loading -->
<div id="loader-wrapper">
    <div id="loader"></div>
    <div class="loader-section section-left"></div>
    <div class="loader-section section-right"></div>
</div>
<!-- End Page Loading -->
<div id="container">
    <?php include('../includes/inc_top.php'); ?>
    <div class="row">
        <div class="col s12 m12">
            <div class="">
                <h3>Customer Payment Report</h3>
            </div>
            <script>
                $(function () {
                    $('#datepicker').pickadate({
                        format: 'dd-mm-yyyy',
                        selectMonths: true,
                      selectYears: true,
                    });
                    $('#datepicker1').pickadate({
                        format: 'dd-mm-yyyy',
                        selectMonths: true,
                      selectYears: true,
                    });
                });
            </script>
            <form action="" method="get" name="search"
                  id="searchfrm">
                <table class="table">
                    <tr>
                        <td class="col s6 l6">Date From<br>
                            <input type="text" name="datefrom" id="datepicker"
                                   placeholder="Date From"
                                   value="<?= $datefrom ?>" class="form-control"/>
                            <input type="hidden" name="view" id="view" class="form-control"/>
                        </td>
                        <td class="col s6 l6"> Date To<br>
                            <input type="text" name="dateto" id="datepicker1"
                                   placeholder="Date To"
                                   value="<?= $dateto ?>" class="form-control"/>
                        </td>
                    </tr>
                    <tr>
                        <td class="col s6 l6">Type<br>
                            <select class="form-control" required id="cat_type"
                                    name="cat_type">
                                <option>All</option>
                                <option value="0" <?= ($cat_type == '0')?'selected':'' ?>>Wholesaler</option>
                                <option value="1" <?= ($cat_type == '1')?'selected':'' ?>>Local Mart</option>
                            </select>
                        </td>
                        <td class="col s6 l6">Category<br>
                            <?php
                            $catFilter = " JOIN customer_setup ON customer_category.id = customer_setup.category_id GROUP BY catid ORDER BY customer_category.name";
                            $categories = CustomerCategorySetup::GetCustomerCategorySetupList($catFilter);
                            ?>
                            <select class="form-control" required id="cat_name"
                                    name="cat_name">
                                <option>All</option>
                                <?php
                                if ($categories) {
                                    foreach ($categories AS $categoriesRow) {
                                        echo '<option ';
                                        if ($categoriesRow->catid == $cat_name) {
                                            echo 'selected';
                                        }
                                        echo ' value="' . $categoriesRow->catid . '" >' . $categoriesRow->catname . '</option>';
                                    }
                                }
                                ?>
                            </select><br/>
                        </td>
                    </tr>
                    <!--                                    <tr>-->
                    <!--                                        <td class="col l4">Customer<br>-->
                    <!--                                            --><?php
                    //                                            $customersfilter = " WHERE status = 'active'";
                    //                                            $customers = CustomerSetup::GetCustomerSetupList($customersfilter);
                    //                                            ?>
                    <!--                                            <select class="form-control" required id="customer_name"-->
                    <!--                                                    name="customer_name">-->
                    <!--                                                <option>All</option>-->
                    <!--                                                --><?php
                    //                                                if ($customers) {
                    //                                                    foreach ($customers AS $customersRow) {
                    //                                                        echo '<option ';
                    //                                                        if ($customersRow->id == $customer_name) {
                    //                                                            echo 'selected';
                    //                                                        }
                    //                                                        echo ' value="' . $customersRow->id . '" >' . $customersRow->name . '</option>';
                    //                                                    }
                    //                                                }
                    //                                                ?>
                    <!--                                            </select><br/>-->
                    <!--                                        </td>-->
                    <!--                                    </tr>-->
                    <tr>
                        <td colspan="2" class="col l12">
                            <button type="submit" class="btn waves-effect waves-light"
                                    style="float:left;">
                                Search
                            </button>
                            <?php if (isset($_GET['view'])) { ?>
                                <button
                                    onclick="exportTableToCSV('payment-<?= date('d-m-Y-h-i-a') ?>.csv')"
                                    class="btn waves-effect waves-light"
                                    style="float:left; margin-left: 15px;">
                                    Export CSV
                                </button>
                                <button
                                    id="print"
                                    class="btn waves-effect waves-light"
                                    style="float:left; margin-left: 15px;">
                                    Print
                                </button>
                            <?php } ?>
                        </td>
                    </tr>
                </table>
            </form>
            <hr><?php
            if (isset($_GET['view'])) {
                ?>
                <!--                --><?php //if ($customer_name != '' || $customer_name != 'All') {
//                    $customer = CustomerSetup::GetCustomerSetupById($customer_name);
//                    echo '<table style="width: 100%;"><tr><th style="text-align: center;">';
//                    echo "<h2>" . $customer->name . "</h2>";
//                    echo '</th></tr></table>';
//                }
//                ?>
                <div id="floatThead" class="section">
                    <table class="striped demo1">
                        <thead>
                        <tr style="border: 1px solid #666;">
                            <th>S.No</th>
                            <th>Customer</th>
                            <th>Opening</th>
                            <th>Debit</th>
                            <th>Credit</th>
                            <th>Balance</th>
                        </tr>
                        </thead>
                        <tbody>
                        <?php
                        $qry = Invoice::GetInvoicePaymentReportListsForOwner($filterReport);
                        $previousbalanceAmounttotal = 0;
                        if ($qry) {
                            $counter = 0;
                            $countersno = 1;
                            foreach ($qry as $qery) {
                                $customerids[] = $qery->customer_id;
                                $counter++;
                                $qryDatePrevious = date('Y-m-d', strtotime('-1 day', strtotime($qryDate)));

                                $filterReportOpening = " JOIN customer_setup AS cs ON cs.id = inv.customer_id WHERE DATE_FORMAT(inv.create_date,'%Y-%m-%d')>='2016-01-01' and DATE_FORMAT(inv.create_date,'%Y-%m-%d')<='$qryDatePrevious'  AND inv.status not in('Draft', 'Cancel') ";
                                $filterReportOpening .= " AND inv.customer_id ='$qery->customer_id'";
                                if (isset($cat_type) && $cat_type == 'All') {
                                    if (isset($cat_name) && $cat_name != 'All') {
                                        $filterReportOpening .= " AND inv.customer_id IN (".$customers.")";
                                    }
                                }else{
                                    $filterReportOpening .= " AND cs.is_mart = '$cat_type' ";
                                }
                                $filterReportOpening .= " AND cs.status ='active'";
                                $filterReportOpening .= " UNION SELECT 'payment' AS nameTable, pay.create_date, pay.customer_id  AS customer_id, pay.amount,
'', '', '','', ''
 from " . TABLE_PAYMENT . " AS pay 
 JOIN customer_setup AS cs ON cs.id = pay.customer_id
 WHERE DATE_FORMAT(pay.create_date,'%Y-%m-%d')>='2016-01-01' and DATE_FORMAT(pay.create_date,'%Y-%m-%d')<='$qryDatePrevious' AND pay.status <> 'Cancel' ";
                                $filterReportOpening .= " AND pay.customer_id ='$qery->customer_id'";
                                if (isset($cat_type) && $cat_type == 'All') {
                                    if (isset($cat_name) && $cat_name != 'All') {
                                        $filterReportOpening .= " AND pay.customer_id IN (".$customers.")";
                                    }
                                }else{
                                    $filterReportOpening .= " AND cs.is_mart = '$cat_type' ";
                                }
                                $filterReportOpening .= " AND cs.status ='active'";
                                $filterReportOpening .= " ORDER BY create_date";
                                $qryOpening = Invoice::GetInvoicePaymentReportListsForOwner($filterReportOpening);
                                $previoustotalDebitAmount = 0;
                                $previoustotalCreditAmount = 0;
                                $previousbalanceAmount = 0;
                                if ($qryOpening) {
                                    foreach ($qryOpening as $qeryOpening) {
//                                                        if($qeryOpening->customer_id = 40){
//                                                            echo '<pre>';
//                                                            print_r($qeryOpening);
//                                                            echo '</pre>';
//                                                        }
                                        if ($qeryOpening->nameTable == 'invoice') {
                                            $previousbalanceAmount += $qeryOpening->total_amount;
                                            $previoustotalCreditAmount -= $qeryOpening->total_amount;
                                        } else {
                                            $previousbalanceAmount -= $qeryOpening->amount;
                                            $previoustotalDebitAmount -= $qeryOpening->amount;
                                        }
                                    }
                                }
                                if ($qery->nameTable == 'invoice') {
                                    $balanceAmount -= $qery->total_amount;
                                    $totalCreditAmount += $qery->total_amount;
                                    $subbalanceAmount -= $qery->total_amount;
                                    $subtotalCreditAmount += $qery->total_amount;
                                }
                                $balanceAmount += $qery->amount;
                                $totalDebitAmount += $qery->amount;
                                $subbalanceAmount += $qery->amount;
                                $subtotalDebitAmount += $qery->amount;
                                if ((isset($qry[$counter]->customer_id) && $qry[$counter]->customer_id != $qery->customer_id) || count($qry) == 1 || $counter == count($qry)) {
                                    $customer = CustomerSetup::GetCustomerSetupById($qery->customer_id);
                                    $previousbalanceAmount += $customer->balance;
                                    $previoustotalCreditAmount -= $customer->balance;
                                    $previousbalanceAmounttotal += $previousbalanceAmount;
                                    ?>
                                    <tr style="border: 1px solid #666;">
                                        <td><?= $countersno; ?></td>
                                        <td><?= $customer->name; ?></td>
                                        <td><?= $previousbalanceAmount; ?></td>
                                        <td><?= $subtotalDebitAmount; ?></td>
                                        <td><?= $subtotalCreditAmount; ?></td>
                                        <td><?= ($subtotalCreditAmount - $subtotalDebitAmount) + $previousbalanceAmount; ?></td>
                                    </tr>
                                    <?php
                                    $subbalanceAmount = 0;
                                    $subtotalCreditAmount = 0;
                                    $subtotalDebitAmount = 0;
                                    $countersno++;
                                }
                            }

                        }
                        $customersfilter = " WHERE status = 'active'";
                        if (isset($cat_type) && $cat_type == 'All') {
                            if (isset($cat_name) && $cat_name != 'All') {
                                $customersfilter .= " AND category_id = '$cat_name'";
                            }
                        }else{
                            $customersfilter .= " AND is_mart = '$cat_type'";
                        }
                        $customers1 = CustomerSetup::GetCustomerSetupList($customersfilter);
                        if ($customers1) {
                            foreach ($customers1 AS $customersRow) {
                                if (!in_array($customersRow->id, $customerids)) {
                                    $qryDatePrevious = date('Y-m-d', strtotime('-1 day', strtotime($qryDate)));

                                    $filterReportOpening = " JOIN customer_setup AS cs ON cs.id = inv.customer_id WHERE DATE_FORMAT(inv.create_date,'%Y-%m-%d')>='2016-01-01' and DATE_FORMAT(inv.create_date,'%Y-%m-%d')<='$qryDatePrevious'  AND inv.status not in('Draft', 'Cancel') ";
                                    $filterReportOpening .= " AND inv.customer_id ='$customersRow->id'";
                                    if (isset($cat_type) && $cat_type == 'All') {
                                        if (isset($cat_name) && $cat_name != 'All') {
                                            $filterReportOpening .= " AND inv.customer_id IN (".$customers.")";
                                        }
                                    }else{
                                        $filterReportOpening .= " AND cs.is_mart = '$cat_type' ";
                                    }
                                    $filterReportOpening .= " AND cs.status ='active'";
                                    $filterReportOpening .= " UNION SELECT 'payment' AS nameTable, pay.create_date, pay.customer_id  AS customer_id, pay.amount,
'', '', '','', ''
 from " . TABLE_PAYMENT . " AS pay 
 JOIN customer_setup AS cs ON cs.id = pay.customer_id
 WHERE DATE_FORMAT(pay.create_date,'%Y-%m-%d')>='2016-01-01' and DATE_FORMAT(pay.create_date,'%Y-%m-%d')<='$qryDatePrevious' AND pay.status <> 'Cancel' ";
                                    $filterReportOpening .= " AND pay.customer_id ='$customersRow->id'";
                                    if (isset($cat_type) && $cat_type == 'All') {
                                        if (isset($cat_name) && $cat_name != 'All') {
                                            $filterReportOpening .= " AND pay.customer_id IN (".$customers.")";
                                        }
                                    }else{
                                        $filterReportOpening .= " AND cs.is_mart = '$cat_type' ";
                                    }
                                    $filterReportOpening .= " AND cs.status ='active'";
                                    $filterReportOpening .= " ORDER BY create_date";
                                    $qryOpening = Invoice::GetInvoicePaymentReportListsForOwner($filterReportOpening);
//                                                    echo '<pre>';
//                                                    print_r($qryOpening);
//                                                    echo '</pre>';
//                                                    exit;
                                    $previoustotalDebitAmount = 0;
                                    $previoustotalCreditAmount = 0;
                                    $previousbalanceAmount = 0;

                                    //echo $customersRow->id.'h ';
                                   
                                    $customer = CustomerSetup::GetCustomerSetupById($customersRow->id);
                                    if ($qryOpening) {
                                        foreach ($qryOpening as $qeryOpening) {
                                            if ($qeryOpening->nameTable == 'invoice') {
                                                $previousbalanceAmount += $qeryOpening->total_amount;
                                                $previoustotalCreditAmount -= $qeryOpening->total_amount;
                                            } else {
                                                $previousbalanceAmount -= $qeryOpening->amount;
                                                $previoustotalDebitAmount += $qeryOpening->amount;
                                            }
                                        }
                                    }
                                    $previousbalanceAmount += $customer->balance;
                                    $previoustotalCreditAmount -= $customer->balance;
                                    ?>
                                    <tr style="border: 1px solid #666;">
                                        <td><?= $countersno; ?></td>
                                        <td><?= $customer->name; ?></td>
                                        <td><?= $previousbalanceAmount; ?></td>
                                        <td><?= $subtotalDebitAmount; ?></td>
                                        <td><?= $subtotalCreditAmount; ?></td>
                                        <td><?= ($subtotalCreditAmount - $subtotalDebitAmount) + $previousbalanceAmount; ?></td>
                                    </tr>
                                    <?php

                                    $previousbalanceAmounttotal += $previousbalanceAmount;
                                    $subbalanceAmount = 0;
                                    $subtotalCreditAmount = 0;
                                    $subtotalDebitAmount = 0;
                                    $countersno++;
                                }
                            }
                        }
                        ?>
                        <tr>
                            <th colspan="2" style="text-align: right; border: 1px solid #000;">Total
                            </th>
                            <th style="border: 1px solid #000;"><?= $previousbalanceAmounttotal ?></th>
                            <th style="border: 1px solid #000;"><?= $totalDebitAmount ?> Db</th>
                            <th style="border: 1px solid #000;"><?= $totalCreditAmount ?> Cr</th>
                            <th style="border: 1px solid #000;"><?= ($totalCreditAmount + $previousbalanceAmounttotal) - $totalDebitAmount ?>
                                <?= $totalDebitAmount > $totalCreditAmount ? 'Db' : 'Cr' ?>
                            </th>
                        </tr>
                        <!--                        <tr style="border: 1px solid #666;">-->
                        <!--                            <th>Customer</th>-->
                        <!--                            <th>Opening</th>-->
                        <!--                            <th>Debit</th>-->
                        <!--                            <th>Credit</th>-->
                        <!--                            <th>Balance</th>-->
                        <!--                        </tr>-->
                        </tbody>
                    </table>
                </div>
                <?php

            } // close view
            ?>
        </div>
    </div>
</div>
</div>
<?php include('../includes/inc_footer.php'); ?>

<script type="text/javascript" src="<?= PROJECT_URL ?>js/download.js"></script>
<?php include('../includes/inc_js.php'); ?>
<script type="text/javascript" src="<?= PROJECT_URL ?>js/plugins/floatThead/jquery.floatThead.min.js"></script>
<script type="text/javascript"
        src="<?= PROJECT_URL ?>js/plugins/floatThead/jquery.floatThead-slim.min.js"></script>
<script type="application/javascript">
    $(document).ready(function () {
        $("#print").on("click", function (e) {
            var fewSeconds = 2;
            e.preventDefault();
            $('#searchfrm').prop('target', '_blank');
            $('#searchfrm').prop('action', "<?=PROJECT_ADMIN_URL?>report/print/customerpayment.php?").submit();
            setTimeout(function () {
                $('#searchfrm').prop('target', "");
                $('#searchfrm').prop('action', "");
            }, fewSeconds * 1000);
        });
        $('table.demo1').floatThead({
            position: 'fixed',
            scrollingTop: 65
        });
        $('table.demo1 thead').css('background', '#ECEFF1');
    });
</script>
</body>
</html>