<?php
include("../../classes/manager.class.php");
//ini_set('display_errors', 1);
//error_reporting(E_ALL);
$m = new Manager();
$output = '';
$datefrom = "";
$dateto = "";
$cat_name = array();
$productQtyTotal = 0;
$costPriceTotal = 0;
$unitPriceTotal = 0;
$distributorPriceTotal = 0;
$totalDiscount = 0;
$totalProfit = 0;
if (!isset($_SESSION['sess_userid'])) {
    header("Location: " . PROJECT_ADMIN_URL);
    exit();
}
extract($_REQUEST);
if ($_REQUEST) {
    //print_r($_REQUEST);
    //exit;
}
$cat_nameString = "";
if (!empty($cat_name) && (isset($cat_name[1]) || $cat_name[0] != "All")) {
    if (in_array("All", $cat_name)) {
        $catKey = array_search('All', $cat_name);
        unset($cat_name[$catKey]);
    }
    $cat_nameString = implode("','", $cat_name);
}
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 = " INNER JOIN " . TABLE_INVOICE_PRODUCT . " AS invPr ON inv.id = invPr.invoice_id RIGHT JOIN " . TABLE_CUSTOMERSETUP . " AS cs ON inv.customer_id = cs.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') AND cs.is_mart = 1 ";

if (isset($customer_name) && $customer_name != 'All') {
    $filterReport .= " AND inv.customer_id ='$customer_name'";
}
if (isset($cat_name) && (isset($cat_name[1]) || $cat_name[0] != "All")) {
    $filterReport .= " AND cs.category_id IN ('$cat_nameString')";
    //$filterReport .= " ORDER BY cs.id ";
} else {
    //$filterReport .= " ORDER BY cs.category_id, cs.id";
}

$filterReport .= " UNION SELECT *, cs.category_id AS cscategory_id, inv.code AS code, cs.id AS custid, inv.status AS status from ".TABLE_INVOICE." AS inv
INNER JOIN " . TABLE_INVOICE_PRODUCT . " AS invPr ON inv.id = invPr.invoice_id
AND 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')";

$filterReport .= " RIGHT JOIN " . TABLE_CUSTOMERSETUP . " AS cs ON inv.customer_id = cs.id
WHERE cs.is_mart = 1 AND inv.code IS NULL";
if (isset($customer_name) && $customer_name != 'All') {
    $filterReport .= " AND inv.customer_id ='$customer_name'";
}
if (isset($cat_name) && (isset($cat_name[1]) || $cat_name[0] != "All")) {
    $filterReport .= " AND cs.category_id IN ('$cat_nameString')";
    $filterReport .= " ORDER BY custid";
} else {
    $filterReport .= " ORDER BY cscategory_id, custid";
}
//echo $filterReport;
//exit;
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <?php include('../includes/inc_head.php'); ?>
</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>Cumulative Mart 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">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"> Date To<br>
                            <input type="text" name="dateto" id="datepicker1"
                                   placeholder="Date To"
                                   value="<?= $dateto ?>" class="form-control"/>
                        </td>
                    </tr>
                    <tr>
                        <td class="form-group col s6">Category<br>
                            <?php
                            $catFilter = " JOIN customer_setup ON customer_category.id = customer_setup.category_id WHERE customer_setup.is_mart = 1 GROUP BY catid ORDER BY customer_category.name ";
                            $categories = CustomerCategorySetup::GetCustomerCategorySetupList($catFilter);
                            ?>
                            <select class="polulate" style="width: 100%;" multiple required id="e9"
                                    name="cat_name[]">
                                <option>All</option>
                                <?php
                                if ($categories) {
                                    foreach ($categories AS $categoriesRow) {
                                        echo '<option ';
                                        if (in_array($categoriesRow->catid, $cat_name)) {
                                            echo 'selected';
                                        }
                                        echo ' value="' . $categoriesRow->catid . '" >' . ucfirst(strtolower($categoriesRow->catname)) . '</option>';
                                    }
                                }
                                ?>
                            </select><br/>
                        </td>
                        <td class="col s6">Customer<br>
                            <?php
                            if ($cat_name == 'All') {
                                $customersfilter = " WHERE status = 'active' AND is_mart = 1 AND ";
                            } else {
                                $customersfilter = " WHERE status = 'active' AND is_mart = 1 AND category_id IN ('$cat_nameString')";
                            }
                            $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 s12">
                            <button type="submit" class="btn waves-effect waves-light"
                                    style="float:left;">
                                Search
                            </button>
                            <?php if (isset($_GET['view'])) { ?>
                                <button
                                    onclick="exportTableToCSV('mart-<?= date('d-m-Y-h-i-a') ?>.csv')"
                                    class="btn waves-effect waves-light"
                                    style="float:left; margin-left: 15px;">
                                    Download 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'])) {
                ?>
                <div class="row">
                    <div class="col s12">
                        <section class="panel">
                            <div class="panel-body">
                                <div class="chartJS">
                                    <canvas id="myChart" height="500" width="1024"></canvas>
                                </div>
                            </div>
                        </section>
                    </div>
                </div>
                <hr>
                <?php if ($customer_name != '' || $customer_name != 'All') {
                    $customer = CustomerSetup::GetCustomerSetupById($customer_name);
                    if (!empty($customer)) {
                        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 bordered">
                        <thead>
                        <tr style="border: 1px solid #666;">
                            <th>S.No</th>
                            <?php if ($customer_name == '' || $customer_name == 'All') { ?>
                                <th>Customer</th>
                            <?php } ?>
                            <th>Opening Balance</th>
                            <th>Total Balance</th>
                            <th>Sub Total</th>
                            <th>Profit / Loss Distributor</th>
                            <th>Profit / Loss Factory</th>
                        </tr>
                        </thead>
                        <tbody>
                        <?php
                        $qry = Invoice::GetInvoiceReportList($filterReport);
                        if ($qry) {
                            $counter = 0;
                            $countdown = 0;
                            $reset = false;
                            $qty = 0;
                            $factoryRatePrice = 0;
                            $distributorRatePrice = 0;
                            $salesPrice = 0;
                            $subTotalDiscountPrice = 0;
                            foreach ($qry as $qery) {
                                $counter++;
                                $customer = CustomerSetup::GetCustomerSetupById($qery->custid);
                                $discount_price = $qery->discount_price;

                                $productQty = $qery->product_qty;
                                $productQtyTotal += $productQty;
                                $costPrice = number_format(($qery->product_qty * $qery->unit_cost_price), 2, '.', '');
                                $costPriceTotal += $costPrice;
                                $unitPrice = number_format(($qery->product_qty * $qery->unit_sale_price), 2, '.', '');
                                $unitPriceTotal += $unitPrice;
                                $distributorPrice = number_format(($qery->product_qty * $qery->unit_distributor_price), 2, '.', '');
                                $distributorPriceTotal += $distributorPrice;
                                $subtotal = $unitPrice - $costPrice;


                                if (PROJECT_DISCOUNT == 1 || $discount_price != 0) {
                                    $count = 0;
                                    $totalDiscountper = $qery->discount;

                                    $subTotalDiscount = ($unitPrice * ($totalDiscountper / 100));
                                    $totalDiscount += $subTotalDiscount;
                                    $subtotal -= $subTotalDiscount;
                                }
                                $totalProfit += $subtotal;

                                if (isset($qry[$counter]->custid)) {
                                    $nextCode = $qry[$counter]->custid;
                                } else {
                                    $nextCode = 0;
                                }
                                if ($reset == true) {
                                    $qty = 0;
                                    $factoryRatePrice = 0;
                                    $distributorRatePrice = 0;
                                    $salesPrice = 0;
                                    $subTotalDiscountPrice = 0;
                                    $reset = false;
                                }
                                if ($qery->custid == $nextCode) {
                                    $qty += $qery->product_qty;
                                    $factoryRatePrice += $costPrice;
                                    $distributorRatePrice += $distributorPrice;
                                    $salesPrice += $unitPrice;
                                    $subTotalDiscountPrice += $subTotalDiscount;
                                    continue;
                                } else {
                                    $qty += $qery->product_qty;
                                    $factoryRatePrice += $costPrice;
                                    $distributorRatePrice += $distributorPrice;
                                    $salesPrice += $unitPrice;
                                    $subTotalDiscountPrice += $subTotalDiscount;
                                    $reset = true;
                                    //continue;
                                    //$lastcode = $qery->code;
                                }
                                $countdown++;
                                //echo $qery->customer_id."<br>";
                                $filterpay = " WHERE pay.customer_id = " . $qery->custid . "
                                                AND DATE_FORMAT(pay.create_date,'%Y-%m-%d')>='$qryDate'
                                                AND DATE_FORMAT(pay.create_date,'%Y-%m-%d')<='$qryTo'
                                                AND pay.status <> 'Cancel'
                                                GROUP BY pay.customer_id
                                                ORDER BY cus.name ";
                                $totalPayment = Payment::GetTotalPaymentLists($filterpay);
                                //print_r($totalPayment);


                                //PreviousRecord
                                $qryDatePrevious = date('Y-m-d', strtotime('-1 day', strtotime($qryDate)));
                                $filterReportPrevious = " RIGHT JOIN " . TABLE_INVOICE_PRODUCT . " AS invPr ON inv.id = invPr.invoice_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') ";
                                $filterReportPrevious .= " AND inv.customer_id ='$qery->custid'";
                                $filterReportPrevious .= " UNION SELECT 'payment' AS nameTable, id, '' AS description , code , customer_id, '', '', amount, '',
		'', '', create_date, '', datetime, '',
		'', '', '', '', '', payment_type, cheque_bank_name, cheque_number,
		cheque_payee_name, cheque_date, bank_name, bank_account_number,
		party_name, from_bank_name FROM " . TABLE_PAYMENT . " WHERE DATE_FORMAT(create_date,'%Y-%m-%d')>='2016-01-01' and DATE_FORMAT(create_date,'%Y-%m-%d')<='$qryDatePrevious' AND status <> 'Cancel' ";
                                $filterReportPrevious .= " AND customer_id = '$qery->custid'";
                                $filterReportPrevious .= " ORDER BY create_date, code ";

                                $qryPrevious = Invoice::GetInvoiceCustomerReportList($filterReportPrevious);
                                $unitPricePrevious = 0;
                                $totalDiscountperPrevious = 0;
                                $subTotalDiscountPrevious = 0;
                                $totalProfitPrevious = 0;
                                $totalPaymentsPrevious = 0;
                                $openingBalance = 0;
                                if($qryPrevious){
                                    foreach($qryPrevious AS $qryPreviousRow){
                                        if ($qryPreviousRow->nameTable == 'invoice') {
                                            $unitPricePrevious = number_format(($qryPreviousRow->product_qty * $qryPreviousRow->unit_sale_price), 2, '.', '');
                                            $totalDiscountperPrevious = $qryPreviousRow->discount;

                                            $subTotalDiscountPrevious = ($unitPricePrevious * ($totalDiscountperPrevious / 100));

                                            $totalProfitPrevious += ($unitPricePrevious - $subTotalDiscountPrevious);
                                        } else if($qryPreviousRow->nameTable == 'payment') {
                                            $totalPaymentsPrevious += $qryPreviousRow->total_amount;
                                        }
                                    }
                                    $openingBalance = number_format(($totalProfitPrevious - $totalPaymentsPrevious), 2, '.', '');
                                }
                                ?>
                                <tr style="border: 1px solid #666;">
                                    <td><?= $countdown ?></td>
                                    <?php if ($customer_name == '' || $customer_name == 'All') {
                                        if (!empty($customer->short_code)) {
                                            $cusChart[] = $customer->short_code;
                                        } else {
                                            $cusChart[] = $customer->name;
                                        }
                                        ?>
                                        <td><?php echo $customer->name; ?></td>
                                    <?php }
                                    $totalBalance += (($salesPrice - $subTotalDiscountPrice) - $totalPayment->amount) + $openingBalance;
                                    ?>
                                    <td><?php echo number_format($openingBalance, 2, '.', ''); ?></td>
                                    <td><?php echo number_format(((($salesPrice - $subTotalDiscountPrice) - $totalPayment->amount) + $openingBalance), 2, '.', ''); ?></td>
                                    <td><?php echo number_format(($salesPrice - $subTotalDiscountPrice), 2, '.', ''); ?></td>
                                    <td><?php
                                        $distributorProfitChart[] = number_format((($salesPrice - $subTotalDiscountPrice) - $distributorRatePrice), 0, '.', '');
                                        echo number_format((($salesPrice - $subTotalDiscountPrice) - $distributorRatePrice), 2, '.', ''); ?></td>
                                    <td><?php
                                        $factoryProfitChart[] = number_format(($distributorRatePrice - $factoryRatePrice), 0, '.', '');
                                        echo number_format(($distributorRatePrice - $factoryRatePrice), 2, '.', ''); ?></td>
                                </tr>
                                <?php
                            }
                        } ?>
                        <tr>
                            <?php if ($customer_name == '' || $customer_name == 'All') {
                                $num = 3;
                            } else {
                                $num = 2;
                            } ?>
                            <th colspan="<?= $num ?>"
                                style="text-align: right; border: 1px solid #000;">
                                Total
                            </th>
                            <th style="border: 1px solid #000;"><?= number_format($totalBalance, 2, '.', '') ?></th>
                            <th style="border: 1px solid #000;"><?= number_format(($unitPriceTotal - $totalDiscount), 2, '.', '') ?></th>
                            <th style="border: 1px solid #000;"><?= number_format((($unitPriceTotal - $totalDiscount) - $distributorPriceTotal), 2, '.', '') ?></th>
                            <th style="border: 1px solid #000;"><?= number_format(($distributorPriceTotal - $costPriceTotal), 2, '.', '') ?></th>
                        </tr>
                        <tr>
                            <th>S.No</th>
                            <?php if ($customer_name == '' || $customer_name == 'All') { ?>
                                <th>Customer</th>
                            <?php } ?>
                            <th>Opening Balance</th>
                            <th>Total Balance</th>
                            <th>Sub Total</th>
                            <th>Profit / Loss Distributor</th>
                            <th>Profit / Loss Factory</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 () {
        // initialize
        $('#customer_name').material_select();

        // setup listener for custom event to re-initialize on change
        $('#customer_name').on('contentChanged', function () {
            $(this).material_select();
        });

        $('#e9').on("change", function () {
            $('body.container').block({message: 'Loading...'});
            $.get('getFilterCustomersMulti.php?id=' + $(this).val(), function (data) {
                $("#customer_name").empty();
                $("#customer_name").append(data);
                $('body.container').unblock();
                $('#customer_name').trigger('contentChanged');
            });
        });
        //            $('#cat_name').on('change', function () {
        //                $('body.container').block({message: 'Loading...'});
        //                $.get('getFilterCustomers.php?id=' + $(this).val(), function (data) {
        //                    $("#customer_name").empty();
        //                    $("#customer_name").append(data);
        //                    $('body.container').unblock();
        //                });
        //            });
        $('#customer_name').on('change', function () {
            $('body.container').block({message: 'Loading...'});
            $.get('getFilterInvoices.php?id=' + $(this).val(), function (data) {
                $("#invoice").empty();
                $("#invoice").append(data);
                $('body.container').unblock();
            });
        });
        //            $('#e9').on("change", function(){
        //                console.log();
        //                console.log($('#e9').select2().val());
        //                console.log($.inArray("", $('#e9').select2().val()));
        //                if($.inArray("All", $('#e9').select2().val()) !== -1){
        //                    $('#e9').select2().val(['All']).removeData;
        //                    //$('#e9').select2().val(['All']).trigger("change");
        //                }
        ////                $('#e9').select2().each(function(){
        ////                    console.log($(this).val());
        ////
        ////                });
        //            });
        $("#print").on("click", function (e) {
            var fewSeconds = 2;
            e.preventDefault();
            $('#searchfrm').prop('target', '_blank');
            $('#searchfrm').prop('action', "<?=PROJECT_ADMIN_URL?>report/print/cumartcat.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');
        <?php if(!empty($cat_name)){ ?>
        var ctx = document.getElementById("myChart").getContext('2d');
        var myChart = new Chart(ctx, {
            type: 'bar',
            data: {
                labels: [<?php
                    if($cusChart){
                    $countingChart = 0;
                        foreach($cusChart AS $cusChartRow){
                            $countingChart++;
                            //if($countingChart <= 10){
                            $labelName = (strlen($cusChartRow) > 10 ? substr($cusChartRow, 0, 10) : $cusChartRow);
                                echo '"'.$labelName.'",';
                            //}
                        }
                    }
                    ?>],
                datasets: [
                    {
                        label: "Profit / Loss Distributor",
                        fillColor: "#E67A77",
                        strokeColor: "#E67A77",
                        backgroundColor: "#428bca",
                        data: [<?php
                            if($distributorProfitChart){
                                $countingChart=0;
                                foreach($distributorProfitChart AS $distributorProfitChartRow){
                                    $countingChart++;
                                    //if($countingChart <= 10){
                                        echo ''.$distributorProfitChartRow.',';
                                    //}
                                }
                            }
                            ?>],
                    },
                    {
                        label: "Profit / Loss Factory",
                        fillColor: "#79D1CF",
                        strokeColor: "#79D1CF",
                        backgroundColor: "#0B8703",
                        data: [<?php
                            if($factoryProfitChart){
                                $countingChart=0;
                                foreach($factoryProfitChart AS $factoryProfitChartRow){
                                $countingChart++;
                                //if($countingChart <= 10){
                                        echo ''.$factoryProfitChartRow.',';
                                    //}
                                }
                            }
                            ?>]
                    }
                ],
            },
            options: {
                scales: {
                    yAxes: [{
                        ticks: {
                            beginAtZero: true
                        }
                    }]
                }
            }
        });
        <?php } ?>
    });
    <?php if(empty($cat_name) || $cat_name[0] == "All"){ ?>
    $("#e9 option").eq(0).attr("selected", true);
    <?php }else{?>
    var selectedOptions=[
        '<?=$cat_nameString?>'
    ];
    $.each(selectedOptions, function(i,e){
        //alert($("#e9 :selected").eq(i).val());
        $("#e9 option[value='" + e + "']").prop("selected", true);
    });
    <?php }?>
</script>
</body>
</html>