<?php
include("../../classes/manager.class.php");
$m = new Manager();
$output = '';
$datefrom = "";
$dateto = "";
$supplier_id = "";
$ingredient_id = "";

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));

$dateFromFormatted = date('Y-m-d', strtotime($datefrom));
$dateToFormatted = date('Y-m-d', strtotime($dateto));

// Initialize the filter with date range condition
//$filterReport = "AND DATE_FORMAT(i.datetime, '%Y-%m-%d') >= '$qryDate' 
 //                AND DATE_FORMAT(i.datetime, '%Y-%m-%d') <= '$qryTo'";

// Apply supplier filter if specified
if (isset($supplier_id) && $supplier_id != 'All') {
    $filterReport .= " AND p.supplier_id = '$supplier_id'";
}

// Apply ingredient filter if specified
if (isset($ingredient_id) && $ingredient_id != 'All') {
    $filterReport .= " AND i.ingredient_id = '$ingredient_id'";
}
// At this point, the $filterReport is ready to be passed into the method
$data = IngredientStock::GetRawMaterialStockRegisterTest($dateFromFormatted, $dateToFormatted, $supplier_id, $ingredient_id );

$openingBalance = $data['opening_balance'];
$transactions = $data['transactions'];
//echo $transactions;
//echo json_encode($openingBalance, JSON_PRETTY_PRINT);

?>
<!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>Raw Material Stock Register Summary</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 s6">Supplier<br>
                            <?php
                            // Fetch suppliers from your SupplierSetup table
                            $suppliers = SupplierSetup::GetSupplierSetupList();
                            ?>
                            <select class="form-control" required id="supplier_id"
                                    name="supplier_id">
                                <option>All</option>
                                <?php
                                if ($suppliers) {
                                    foreach ($suppliers as $supplier) {
                                        echo '<option ';
                                        if ($supplier->id == $supplier_id) {
                                            echo 'selected';
                                        }
                                        echo ' value="' . $supplier->id . '" >' . $supplier->name . '</option>';
                                    }
                                }
                                ?>
                            </select><br/>
                        </td>
                        <td class="col s6">Ingredient<br>
                            <?php
                            // Fetch ingredients from your IngredientSetup table
                          		$filter = "ORDER BY name ASC";
                            $ingredients = IngSetup::GetIngSetupList($filter);
                            ?>
                            <select class="form-control" required id="ingredient_id"
                                    name="ingredient_id">
                                <option>All</option>
                                <?php
                                if ($ingredients) {
                                    foreach ($ingredients as $ingredient) {
                                        echo '<option ';
                                        if ($ingredient->id == $ingredient_id) {
                                            echo 'selected';
                                        }
                                        echo ' value="' . $ingredient->id . '" >' . $ingredient->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('balance_stock_report_data-<?= 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" id="balanceStockReport">
                        <thead>
        <tr style="border: 1px solid #666; font-weight: bold;">
        <th style='text-align: left;'>Items (Ingredient Name)</th>
        <th style='text-align: right;'>Opening Amount Excluding Sales Tax</th>
        <th style='text-align: right;'>Opening Sales Tax Amount</th>
        <th style='text-align: right;'>Opening Qty</th>
        <th style='text-align: right;'>Receive Qty</th>
        <th style='text-align: right;'>Issue Qty</th>
        <th style='text-align: right;'>Balance Qty</th>
        <th style='text-align: right;'>Closing Amount Excluding Sales Tax</th>
        <th style='text-align: right;'>Closing Sales Tax Amount</th>
    </tr>
    </thead>
   <tbody>
<?php
$grandTotalReceivingQty = $grandTotalIssueQty = $grandTotalBalanceQty = $grandTotalAmount = $grandTotalSalesTaxAmount = 0;

// Collect all unique ingredient IDs and names from both opening balances and transactions
$ingredients = [];

// Add ingredients from opening balances
foreach ($openingBalance as $ingredientOpening) {
    $ingredients[$ingredientOpening->ingredient_id] = $ingredientOpening->ingredient_name;
}

// Add ingredients from transactions
foreach ($transactions as $row) {
    if (!isset($ingredients[$row->ingredient_id])) {
        $ingredients[$row->ingredient_id] = $row->ingredient_name;
    }
}
              
              asort($ingredients);

// Process each ingredient
foreach ($ingredients as $ingredientId => $ingredientName) {
    // Find opening balance for this ingredient, or set default zero values
    $ingredientOpening = array_values(array_filter($openingBalance, function($i) use ($ingredientId) {
        return $i->ingredient_id == $ingredientId;
    }))[0] ?? (object)[
        'opening_balance_qty' => 0,
        'opening_balance_amount' => 0,
        'ingredient_name' => $ingredientName,
    	'stax' => 0,
    ];

    // Initialize totals for each ingredient
    $totalReceivingQty = $totalIssueQty = $balanceQty = $totalAmount = $totalSalesTaxAmount = 0;
  
    // Set initial balance with opening values
    $balanceQty = $ingredientOpening->opening_balance_qty;
    $balanceAmount = $ingredientOpening->opening_balance_amount;

    // Sum up receive and issue quantities
    foreach ($transactions as $row) {
        if ($row->ingredient_id != $ingredientId) continue;

        $totalReceivingQty += $row->receiving_quantity;
        $totalIssueQty += $row->issue_quantity;

        $balanceQty += $row->receiving_quantity - $row->issue_quantity;
        $balanceAmount += ($row->receiving_quantity - $row->issue_quantity) * $row->price_per_kg;
    }

    // Check if there's no receive or issue activity
    if ($totalReceivingQty == 0 && $totalIssueQty == 0) {
        // Use opening figures as closing figures when there is no activity
        $totalAmount = $ingredientOpening->opening_balance_amount;
        $totalSalesTaxAmount = ($transactions[0]->s_tax_rate * $totalAmount) / 100;
    } else {
        // Calculate the closing figures based on activity
        $totalAmount = $balanceAmount;
        // Fix: Get the tax rate from the last transaction for this ingredient
$taxRate = 0;
foreach ($transactions as $row) {
    if ($row->ingredient_id == $ingredientId && isset($row->s_tax_rate)) {
        $taxRate = $row->s_tax_rate; // Use the last available tax rate for this ingredient
    }
}

// Apply tax only if the tax rate is greater than 0
$totalSalesTaxAmount = ($taxRate > 0) ? ($taxRate * $balanceAmount) / 100 : 0;
    }
	$totalIngredientOpeningBalance = $ingredientOpening->opening_balance_amount;
  	$staxRate = isset($ingredientOpening->stax) ? (float)$ingredientOpening->stax : 0.0;
	$totalOpeningBalanceTaxAmount = ($staxRate > 0) ? ($staxRate * $totalIngredientOpeningBalance) / 100.0
    : 0.0;
  	$totalIngredientOpening = $ingredientOpening->opening_balance_qty;
    // Display row for the ingredient
  	$EPS = 1e-9; // tolerance for floating point
$noActivity =
    (abs((float)$totalIngredientOpening) < $EPS) &&
    (abs((float)$totalReceivingQty)     < $EPS) &&
    (abs((float)$totalIssueQty)         < $EPS);

if ($noActivity) {
    continue; // don't print or add to grand totals
}
    echo "
        <tr style='border: 1px solid #666; font-weight: bold;'>
            <td>{$ingredientName}</td>
            <td style='text-align: right;'>".number_format($totalIngredientOpeningBalance, 2)."</td>
            <td style='text-align: right;'>".number_format($totalOpeningBalanceTaxAmount, 2)."</td>
            <td style='text-align: right;'>".number_format($totalIngredientOpening, 6)."</td>
            <td style='text-align: right;'>".number_format($totalReceivingQty, 6)."</td>
            <td style='text-align: right;'>".number_format($totalIssueQty, 6)."</td>
            <td style='text-align: right;'>".number_format($balanceQty, 6)."</td>
            <td style='text-align: right;'>".number_format($totalAmount, 2)."</td>
            <td style='text-align: right;'>".number_format($totalSalesTaxAmount, 2)."</td>
        </tr>";

    // Accumulate grand totals
  	$grandOpeningBalanceAmount += $totalIngredientOpeningBalance;
  	$grandOpeningBalanceTaxAmount += $totalOpeningBalanceTaxAmount;
  	$grandOpeningQuantity += $totalIngredientOpening;
    $grandTotalReceivingQty += $totalReceivingQty;
    $grandTotalIssueQty += $totalIssueQty;
    $grandTotalBalanceQty += $balanceQty;
    $grandTotalAmount += $totalAmount;
    $grandTotalSalesTaxAmount += $totalSalesTaxAmount;
}

// Display grand total row at the end
 echo "
        <tr style='font-weight: bold; background-color: #d3d3d3;'>
                
            <td style='text-align: right;'>Grand Total</td>
             <td style='text-align: right;' >".number_format($grandOpeningBalanceAmount, 2)."</td>
              <td style='text-align: right;'>".number_format($grandOpeningBalanceTaxAmount, 2)."</td>
               <td style='text-align: right;'>".number_format($grandOpeningQuantity, 6)."</td>
            <td style='text-align: right;'>".number_format($grandTotalReceivingQty, 6)."</td>
            <td style='text-align: right;'>".number_format($grandTotalIssueQty, 6)."</td>
            <td style='text-align: right;'>".number_format($grandTotalBalanceQty, 6)."</td>
            
            <td style='text-align: right;'>".number_format($grandTotalAmount, 2)."</td>
          
            <td style='text-align: right;'>".number_format($grandTotalSalesTaxAmount, 2)."</td>
        </tr>";
?>
</tbody>

                     
                            


            	   </table>
                </div>
                <?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/stock_register_raw_material_summary.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);
    }
</script>
  
</body>
</html>
