<?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;
$productQtyTotal = 0;
$costPriceTotal = 0;
$unitPriceTotal = 0;
$grandTotalCarton = 0;
$grandTotalWeight = 0;
$totalProfit = 0;
$mainCat = "";
$catName = "";
if (!isset($_SESSION['sess_userid'])) {
    header("Location: " . PROJECT_ADMIN_URL);
    exit();
}
extract($_REQUEST);
$customer_category = isset($customer_category) ? $customer_category : 'All';

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 = " 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($customer_name) && $customer_name != 'All') {
    $filterReport .= " AND inv.customer_id ='$customer_name'";
}

if (isset($customer_category) && $customer_category != 'All') {
    $filterReport .= " AND cs.category_id = '$customer_category'";
}

if (isset($advance_tax) && $advance_tax != 'All') {
    $filterReport .= " AND cs.advance_tax = '$advance_tax'";
}
if (isset($status) && $status != 'All') {
    $filterReport .= " AND inv.status ='$status'";
    $filterReport .= " GROUP BY inv.id";
} else {
    $filterReport .= " GROUP BY inv.id";
}
$filterReport .= " ORDER BY inv.code ";

$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' ";
$filterExpenseReport .= " 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 Report Tax Details</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">Customer<br>
                            <?php
                            //$customersfilter = " WHERE status = 'active' AND is_mart = 0";
                            $customersfilter = " WHERE status = 'active' ORDER BY name";
                            $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>
                      
                      <td class="col s4">Customer Category<br>
    <?php
   // $catFilter = " WHERE status = 'active' ORDER BY name";
    $custCats = CustomerCategorySetup::GetCustomerCategorySetupList($catFilter);
    ?>
    <select class="form-control" name="customer_category" id="customer_category">
        <option value="All">All</option>
        <?php
        if ($custCats) {
            foreach ($custCats as $cat) {
                $selected = (isset($customer_category) && $customer_category == $cat->catid) ? 'selected' : '';
                echo "<option value='{$cat->catid}' $selected>{$cat->catname}</option>";
            }
        }
        ?>
    </select><br/>
</td>

                      
                      
                      	<td class="col s4">Advance Tax<br>
    <select name="advance_tax" id="advance_tax" class="form-control">
        <option value="All" <?= (!isset($advance_tax) || $advance_tax == 'All') ? 'selected' : '' ?>>All</option>
        <option value="1" <?= (isset($advance_tax) && $advance_tax === '1') ? 'selected' : '' ?>>Yes</option>
        <option value="0" <?= (isset($advance_tax) && $advance_tax === '0') ? 'selected' : '' ?>>No</option>
    </select>
</td>
                      <!--  <td class="col s6"> Status<br>
                            <select id="status" name="status" class="form-control">
                                <option>All</option>
                                <option <?php if ($status == "Paid") {
                                    echo 'selected';
                                } ?> value="Paid">Paid
                                </option>
                                <option <?php if ($status == "Pending") {
                                    echo 'selected';
                                } ?> value="Pending">Pending
                                </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_report.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" id="invoiceTable">
                        <thead>
                        <tr style="border: 1px solid #666;">
                            <th>S.No</th>
                            <th>Date</th>
                            <th>Invoice #</th>
                            <th>Customer</th>
                         	<th tyle="text-align: right;">NTN/STRN No.</th>
                          	<th style="text-align: right;">Qty.(Pcs)</th>
                         	<th style="text-align: right;">Carton</th>
                          	<th style="text-align: right;">Weight(Kg)</th>
                          	<th style="text-align: right;">Value Exc. Sales tax</th>
                            <th style="text-align: right;">Sales Tax %</th>
                            <th style="text-align: right;">Sales Tax Amount</th>
                        	<th style="text-align: right;">Value Inc. Sales Tax</th>
                          	<th style="text-align: right;">Advance Tax Amount</th>
                            <th style="text-align: right;">Total</th>
                       
                        </tr>
                        </thead>
                       <tbody>
<?php
/*if ($customer_name != 'All' && $customer_name != '') {
    $customerHdr = CustomerSetup::GetCustomerSetupById($customer_name);
    echo "<h2 style='text-align: center;'>" . $customerHdr->name . "</h2>";
}
              if ($customer_category != 'All' && $customer_category != '') {
    $categoryHdr = CustomerCategorySetup::GetCustomerCategorySetupById($customer_category);
    echo "<h2 style='text-align: center;'>Category: " . htmlspecialchars($categoryHdr->name) . "</h2>";
} */

$qry = Invoice::GetInvoiceReportList($filterReport);
if ($qry) {
    $counter = 1;

    // make sure these totals are initialized before usage
    $productQtyTotal        = $productQtyTotal ?? 0;
    $grandTotalCarton       = $grandTotalCarton ?? 0;
    $grandTotalWeight       = $grandTotalWeight ?? 0;
    $unitPriceTotal         = $unitPriceTotal ?? 0;
    $salesTaxPriceTotal     = $salesTaxPriceTotal ?? 0;
    $advanceTaxPriceTotal   = $advanceTaxPriceTotal ?? 0;
    $valueIncSalexTaxTotal  = $valueIncSalexTaxTotal ?? 0;
    $totalAmountTotal       = $totalAmountTotal ?? 0;
    $totalDiscount          = $totalDiscount ?? 0;
    $totalProfit            = $totalProfit ?? 0;

    foreach ($qry as $qery) { ?>
        <tr style="border: 1px solid #666;">
            <td><?= $counter; ?></td>
            <td><?= date('d-m-Y', strtotime($qery->create_date)); ?></td>
            <?php
            // Only keep this lookup if you need is_mart; name/ntn/strn already come from $qery
            $customer    = CustomerSetup::GetCustomerSetupById($qery->customer_id);
            $salesPerson = User_setup::GetUserSetupId($qery->sales_person_id);

            // map to new aliases from your final method
            $discount_price      = $qery->invoice_discount_price;
            $salesTaxPercentage  = $qery->invoice_tax_rate;
            $salesTaxPrice       = $qery->invoice_tax_amount;
            $advanceTaxPrice     = $qery->invoice_adv_tax_amount;
            $totalAmount         = $qery->invoice_total_amount;
                       
            ?>
            <td><?= $qery->code; ?></td>
            <td><?= $qery->customer_name; ?></td>
            <td style="text-align: center;">
                <?= htmlspecialchars($qery->customer_ntn); ?>
               <!-- <?= !empty($qery->customer_strn) ? ' / ' . htmlspecialchars($qery->customer_strn) : ''; ?> -->
            </td>

            <?php
            if ($product_id == 'All' || $product_id == '') {
                $productsQtys = 0;
                $productsUnitCostPrice = 0;
                $productsUnitSalePrice = 0;
                $totalCartons = 0;
                $totalWeight  = 0;

                $filterProducts = " WHERE invoice_id = '$qery->invoice_id'";
                $productSetup = Invoice::GetInvoiceProducts($filterProducts);

                if ($productSetup) {
                    foreach ($productSetup as $productSetupRows) {
                        $productsQtys += $productSetupRows->product_qty;

                        $productDetails = ProductSetup::GetProductSetupById($productSetupRows->product_id);
                        if ($productDetails && (int)$productDetails->carton_pieces > 0) {
                            $cartons = $productSetupRows->product_qty / $productDetails->carton_pieces;
                            $weight  = ($productSetupRows->product_qty * $productDetails->weight) / 1000;
                            $totalCartons += $cartons;
                            $totalWeight  += $weight;
                        }

                        $lineCost = $productSetupRows->product_qty * $productSetupRows->unit_cost_price;
                        $productsUnitCostPrice += (float)number_format($lineCost, 2, '.', '');

                        // if you really need different price for mart vs non-mart, keep this.
                        if (!empty($customer) && (int)$customer->is_mart === 0) {
                            $lineSale = $productSetupRows->product_qty * $productSetupRows->unit_sale_price;
                        } else {
                            $lineSale = $productSetupRows->product_qty * $productSetupRows->unit_sale_price;
                        }
                        $productsUnitSalePrice += (float)number_format($lineSale, 2, '.', '');
                    }
                }

               // === 1) Compute value EXCLUDING tax AFTER invoice-level discount ===
$valueExclSalesTax = $productsUnitSalePrice - (float)$discount_price;
if ($valueExclSalesTax < 0) { $valueExclSalesTax = 0; } // safety

// (optional) keep subtotal if you use it elsewhere
$subtotal = $valueExclSalesTax - $productsUnitCostPrice;

// === 2) Totals (use discounted value now) ===
$productQtyTotal       += $productsQtys;
$costPriceTotal        += $productsUnitCostPrice;
$unitPriceTotal        += $valueExclSalesTax;   // << WAS $productsUnitSalePrice
$grandTotalWeight      += $totalWeight;
$grandTotalCarton      += $totalCartons;
$totalProfit           += $subtotal;
$salesTaxPriceTotal    += $salesTaxPrice;
$advanceTaxPriceTotal  += $advanceTaxPrice;
$totalAmountTotal      += $totalAmount;

// === 3) Value INCLUDING tax = discounted excl tax + tax amount ===
$valueIncSalesTax = $valueExclSalesTax + $salesTaxPrice;
$valueIncSalexTaxTotal += $valueIncSalesTax;
                ?>
                <td style="text-align: right;"><?= number_format($productsQtys, 2); ?></td>
                <td style="text-align: right;"><?= number_format($totalCartons, 2); ?></td>
                <td style="text-align: right;"><?= number_format($totalWeight, 2); ?></td>
               <!-- <td style="text-align: right;"><?= number_format($productsUnitSalePrice, 2); ?></td> -->
          		<td style="text-align: right;"><?= number_format($valueExclSalesTax, 2); ?></td>
                <td style="text-align: right;"><?= number_format($salesTaxPercentage, 2); ?></td>
                <td style="text-align: right;"><?= number_format($salesTaxPrice, 2); ?></td>
                <td style="text-align: right;"><?= number_format($valueIncSalesTax, 2); ?></td>
                <td style="text-align: right;"><?= number_format($advanceTaxPrice, 2); ?></td>
                <td style="text-align: right;"><?= number_format($totalAmount, 2); ?></td>
            <?php } else {
                // single-product branch (left as-is, but mapped alias where needed)
                $productName = ProductSetup::GetProductSetupById($qery->product_id);
                $productQty  = $qery->product_qty;
                $productQtyTotal += $productQty;

                $costPrice = (float)number_format(($qery->product_qty * $qery->unit_cost_price), 2, '.', '');
                $costPriceTotal += $costPrice;

                if (!empty($customer) && (int)$customer->is_mart === 0) {
                    $unitPrice = (float)number_format(($qery->product_qty * $qery->unit_sale_price), 2, '.', '');
                    $unitPriceTotal += $unitPrice;
                    $subtotal = $unitPrice - $costPrice;

                    $filterProducts = " WHERE invoice_id = '$qery->invoice_id'";
                    $productSetup = Invoice::GetInvoiceProducts($filterProducts);
                    $count = 0;
                    if ($productSetup) {
                        foreach ($productSetup as $productSetupRows) { $count++; }
                    }

                    $totalDiscountper = $qery->invoice_discount; // alias
                    $subTotalDiscount = ($unitPrice * ($totalDiscountper / 100));
                    $totalDiscount += $subTotalDiscount;
                    $subtotal -= $subTotalDiscount;

                    $totalProfit += $subtotal;
                } else {
                    $unitPrice = (float)number_format(($qery->product_qty * $qery->unit_distributor_price), 2, '.', '');
                    $unitPriceTotal += $unitPrice;
                    $subtotal = $unitPrice - $costPrice;
                    $totalProfit += $subtotal;
                } ?>
                <td style="font-size: 12px;"><?= $productName->name; ?></td>
                <td><?= $salesPerson->username; ?></td>
                <td><?= $qery->invoice_status; ?></td>
                <td><?= $qery->product_qty; ?></td>
                <td><?= $costPrice; ?></td>
                <td><?= $unitPrice; ?></td>
                <?php if (!empty($customer) && (int)$customer->is_mart === 0) { ?>
                    <td><?= number_format(($subTotalDiscount), 2, '.', ''); ?></td>
                <?php } ?>
            <?php } ?>
        </tr>
        <?php
        $counter++;
    }
}
?>
</tbody>
                      <tfoot>
                        <tr>

                            <td colspan="5" style="text-align: right; border: 1px solid #000;">
                               <strong> Total</strong>
                            </td>
                          	
                         
                           	<td style="border: 1px solid #666; text-align: right;">
                            <strong><?= number_format($productQtyTotal, 2) ?></strong>
                        	</td>
                              <td style="border: 1px solid #666; text-align: right;"><strong><?= number_format($grandTotalCarton, 2) ?></strong></td>
                          <td style="border: 1px solid #666; text-align: right;"><strong> <?= number_format($grandTotalWeight, 2) ?></strong></td>
                            <td style="border: 1px solid #666; text-align: right;"><strong><?= number_format( $unitPriceTotal, 2) ?></strong></td>
                        
                          <td style="border: 1px solid #666; text-align: right;"><strong>-</strong></td>
                          	<td style="border: 1px solid #666; text-align: right;"><strong><?= number_format( $salesTaxPriceTotal, 2) ?></strong></td>
                          	<td style="border: 1px solid #666; text-align: right;"><strong><?= number_format( $valueIncSalexTaxTotal, 2) ?></strong></td>
                            
                            <td style="border: 1px solid #666; text-align: right;"><strong><?= number_format( $advanceTaxPriceTotal, 2) ?></strong></td>
                            <td style="border: 1px solid #666; text-align: right;"><strong><?= number_format( $totalAmountTotal, 2) ?></strong></td>
                        </tr>
                        </tfoot>
                    </table>
                </div>
                <br>
                <br>
                <br>
             
                    
                    <?php
                  
            } // close view
            ?>
        </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/tax_invoice_status.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>
 <script type="text/javascript">
    function exportTableToCSV(filename) {
        var csv = [];
        var rows = document.querySelectorAll("table.demo1 tr");

        // Loop through each row in the table
        for (var i = 0; i < rows.length; i++) {
            var row = [], cols = rows[i].querySelectorAll("td, th");

            // Loop through each column in the row
            for (var j = 0; j < cols.length; j++) {
                // Clean and escape double quotes for CSV compatibility
                var cellValue = cols[j].innerText.trim().replace(/"/g, '""');
                row.push('"' + cellValue + '"');
            }

            // Push the row into the CSV array
            csv.push(row.join(","));
        }

        // Create CSV file and trigger download
        downloadCSV(csv.join("\n"), filename);
    }

    function downloadCSV(csv, filename) {
        var csvFile;
        var downloadLink;

        // Create a Blob from the CSV data
        csvFile = new Blob([csv], {type: "text/csv"});

        // Create a link element for download
        downloadLink = document.createElement("a");

        // Set the download filename
        downloadLink.download = filename;

        // Create a link to the CSV file
        downloadLink.href = window.URL.createObjectURL(csvFile);

        // Hide the download link
        downloadLink.style.display = "none";

        // Append the link to the body
        document.body.appendChild(downloadLink);

        // Automatically click the link to trigger the download
        downloadLink.click();

        // Remove the link from the document after download
        document.body.removeChild(downloadLink);
    }
   
   $('table.demo1').floatThead({
    position: 'fixed',
    scrollingTop: 65
});

</script>


</body>
</html>