<?php
include("../../classes/manager.class.php");
//ini_set('display_errors', 1);
//error_reporting(E_ALL);
$m = new Manager();
$output = '';
$datefrom = "";
$dateto = "";
$customer_name = "";
$product_id = "";
$totalDiscount = 0;
$totaladdDiscount = 0;
$productQtyTotal = 0;
$costPriceTotal = 0;
$unitPriceTotal = 0;
$PayTotal = 0;
$BalTotal = 0;
$mainCat = "";
$catName = "";
$cat_type = ""; // New variable for customer type filter

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 = " LEFT JOIN " . TABLE_INVOICE_PRODUCT . " AS invPr ON inv.id = invPr.invoice_id 
                  INNER 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 <> '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'";
        $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($status) && $status != 'All') {
    $filterReport .= " AND inv.status ='$status'";
    $filterReport .= " GROUP BY inv.id";
} else {
    $filterReport .= " GROUP BY inv.id";
}
$filterReport .= " ORDER BY cs.name ";

$filterExpenseReport = " AS ex RIGHT JOIN " . TABLE_EXPENSECATEGORYSETUP . " AS exCat ON ex.category_id = exCat.id 
                        WHERE DATE_FORMAT(lastupdated,'%Y-%m-%d')>='$qryDate' 
                        AND DATE_FORMAT(lastupdated,'%Y-%m-%d')<='$qryTo' 
                        ORDER BY exCat.parent_id";
?>
<!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>Invoice Status Category 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="col s4">Type<br>
                            <select class="form-control" required id="cat_type" name="cat_type">
                                <option value="All" <?= ($cat_type == 'All') ? 'selected' : '' ?>>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 s4"> Status<br>
                            <select id="status" name="status" class="form-control">
                                <option value="All">All</option>
                                <option value="Paid" <?= ($status == "Paid") ? 'selected' : '' ?>>Paid</option>
                                <option value="Pending" <?= ($status == "Pending") ? 'selected' : '' ?>>Pending / Partial Paid</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('invoice-<?= 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'])) {
                ?>
                <div id="floatThead" class="section">
                    <table class="striped demo1 bordered">
                        <thead>
                        <tr style="border: 1px solid #666;">
                            <th>S.No</th>
                            <th>Date</th>
                            <th>Invoice #</th>
                            <th>Customer</th>
                            <th>Status</th>
                            <th>Price</th>
                            <?php if ((PROJECT_DISCOUNT == 1 || !empty($discount_price))) { ?>
                                <th>Discount</th>
                                <th>Add. Discount</th>
                            <?php } ?>
                            <th>Sub Total</th>
                            <th>Payments</th>
                            <th>Balance</th>
                          	<th>Days Passed</th> <!-- New Column Added -->
                        </tr>
                        </thead>
                        <tbody>
                        <?php
                        if ($customer_name != 'All' && $customer_name != '') {
                            $customer = CustomerSetup::GetCustomerSetupById($customer_name);
                            echo "<h2 style='text-align: center;'>" . $customer->name . "</h2>";
                        }

                        $qry = Invoice::GetInvoiceReportList($filterReport);
                        if ($qry) {
                            $counter = 1;
                            foreach ($qry as $qery) { 
                           		$invoiceDate = strtotime($qery->create_date);
                                $currentDate = strtotime(date('Y-m-d'));
                                $daysPassed = floor(($currentDate - $invoiceDate) / (60 * 60 * 24));
                          ?>
                                <tr style="border: 1px solid #666;">
                                    <td><?= $counter; ?></td>
                                    <td><?php echo date('Y-m-d h:i A', strtotime($qery->create_date)); ?></td>
                                    <?php
                                    $customer = CustomerSetup::GetCustomerSetupById($qery->customer_id);
                                    $salesPerson = User_setup::GetUserSetupId($qery->sales_person_id);
                                    $discount_price = $qery->discount_price;
                                    $add_discount_price = intval($qery->add_discount_price);
                                    ?>
                                    <td><?php echo $qery->code; ?></td>
                                    <td><?php echo $customer->name; ?></td>
                                    <?php
                                    if ($product_id == 'All' || $product_id == '') {
                                        $productsQtys = 0;
                                        $productsUnitCostPrice = 0;
                                        $productsUnitSalePrice = 0;

                                        $filterProducts = " WHERE invoice_id = '$qery->invoice_id'";
                                        $productSetup = Invoice::GetInvoiceProducts($filterProducts);
                                        if ($productSetup) {
                                            foreach ($productSetup as $productSetupRows) {
                                                $productsQtys += $productSetupRows->product_qty;
                                                $productsUnitCostPrice += number_format(($productSetupRows->product_qty * $productSetupRows->unit_cost_price), 2, '.', '');
                                                $productsUnitSalePrice += number_format(($productSetupRows->product_qty * $productSetupRows->unit_sale_price), 2, '.', '');
                                            }
                                        }
                                        $subtotal = $productsUnitSalePrice - $productsUnitCostPrice;
                                        $subtotal -= $discount_price;
                                        $totalDiscount += $discount_price;
                                        $totaladdDiscount += $add_discount_price;

                                        $productQtyTotal += $productsQtys;
                                        $costPriceTotal += $productsUnitCostPrice;
                                        $unitPriceTotal += $productsUnitSalePrice;

                                        $filterPayment = " WHERE invoice_reference_code = '$qery->invoice_id' AND status <> 'Cancel'";
                                        $paymentData = Payment::GetPaymentList($filterPayment);
                                        $pyBalance = 0;
                                        foreach ($paymentData AS $paymentDataRow) {
                                            $pyBalance += $paymentDataRow->amount;
                                        }
                                        $PayTotal += $pyBalance;
                                        $totBal = ($productsUnitSalePrice - $discount_price - $add_discount_price) - $pyBalance;
                                        $BalTotal += $totBal;
                                        ?>
                                        <td><?php echo ($pyBalance < $qery->total_amount && $pyBalance != 0) ? "Partial Paid" : $qery->status; ?></td>
                                        <td><?php echo number_format(($productsUnitSalePrice), 0, '.', ''); ?></td>
                                        <td><?php echo number_format(($discount_price), 0, '.', ''); ?></td>
                                        <td><?php echo number_format(($add_discount_price), 0, '.', ''); ?></td>
                                        <td><?php echo number_format((($productsUnitSalePrice - $discount_price - $add_discount_price)), 0, '.', ''); ?></td>
                                        <td><?php echo number_format(($pyBalance), 0, '.', ''); ?></td>
                                        <td><?php echo number_format(($totBal), 0, '.', ''); ?></td>
                                  		<td><?php echo $daysPassed; ?></td>
                                    <?php } ?>
                                </tr>
                                <?php
                                $counter++;
                            }
                        }
                        ?>
                        <tr>
                            <th colspan="5" style="text-align: right; border: 1px solid #000;">Total</th>
                            <th style="border: 1px solid #666;"><?= $unitPriceTotal ?></th>
                            <?php if ((PROJECT_DISCOUNT == 1 || $discount_price != 0)) { ?>
                                <th style="border: 1px solid #666;"><?= number_format(($totalDiscount), 0, '.', '') ?></th>
                                <th style="border: 1px solid #666;"><?= number_format(($totaladdDiscount), 0, '.', '') ?></th>
                            <?php } ?>
                            <th style="border: 1px solid #666;"><?= number_format(($unitPriceTotal - $totalDiscount), 0, '.', '') ?></th>
                            <th style="border: 1px solid #666;"><?= number_format($PayTotal, 0, '.', '') ?></th>
                            <th style="border: 1px solid #666;"><?= number_format($BalTotal, 0, '.', '') ?></th>
                          	<th style="border: 1px solid #666;">-</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/invoice_status_1.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>
