<?php
include("../../classes/manager.class.php");
//ini_set('display_errors', 1);
//error_reporting(E_ALL);
$m = new Manager();
$m = new Manager();
$output = '';
$datefrom = isset($_POST['datefrom']) ? $_POST['datefrom'] : '2024-08-01';
$dateto = isset($_POST['dateto']) ? $_POST['dateto'] : '2024-09-30';
$ingredient_id = isset($_POST['ingredient_id']) ? $_POST['ingredient_id'] : 75;

// Set up the balance variable to use in the query
$db->query("SET @balance := 0");

// The SQL query for fetching data
$sql = "
    SELECT 
        i.datetime AS `Date`,
        IFNULL(p.code, CONCAT('PIN-', i.purchase_id)) AS `Reference/Batch Number`,
        s.name AS `Supplier/Finish Product`,
        i.total_qty AS `Receiving Quantity (Dr.)`,
        NULL AS `Issue Quantity (Cr.)`,
        @balance := @balance + i.total_qty AS `Balance Quantity`,
        i.price AS `Price Per Kg`,
        i.total_qty * i.price AS `Amount`,
        s.gst AS `S.Tax Rate %`,
        (i.total_qty * i.price) * s.gst / 100 AS `S.Tax Amount`
    FROM 
        ingredient_stock i
    LEFT JOIN 
        purchase p ON i.purchase_id = p.id
    LEFT JOIN 
        supplier_setup s ON p.supplier_id = s.id
    WHERE 
        i.ingredient_id = :ingredient_id
    AND 
        i.datetime BETWEEN :datefrom AND :dateto

    UNION ALL

    SELECT 
        bt.reserved_on AS `Date`,
        CONCAT('BCH-', bt.batch_id) AS `Reference/Batch Number`,
        'Reserved for Production' AS `Supplier/Finish Product`,
        NULL AS `Receiving Quantity (Dr.)`,
        bt.reserved_qty AS `Issue Quantity (Cr.)`,
        @balance := @balance - bt.reserved_qty AS `Balance Quantity`,
        i.price AS `Price Per Kg`,
        bt.reserved_qty * i.price AS `Amount`,
        NULL AS `S.Tax Rate %`,
        NULL AS `S.Tax Amount`
    FROM 
        batch_tracking bt
    LEFT JOIN 
        ingredient_stock i ON bt.stock_id = i.id
    WHERE 
        i.ingredient_id = :ingredient_id
    AND 
        bt.reserved_on BETWEEN :datefrom AND :dateto
    ORDER BY `Date` ASC";

// Prepare and execute the query
$stmt = $db->prepare($sql);
$stmt->bindParam(':ingredient_id', $ingredient_id);
$stmt->bindParam(':datefrom', $datefrom);
$stmt->bindParam(':dateto', $dateto);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Display the report in a table
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <title>Stock Register Report</title>
</head>
<body>
    <h3>Stock Register (Raw Material)</h3>
    <p>Ingredient ID: <?= $ingredient_id ?> | From Date: <?= $datefrom ?> | To Date: <?= $dateto ?></p>
    
    <table border="1">
        <thead>
            <tr>
                <th>Date</th>
                <th>Reference/Batch Number</th>
                <th>Supplier/Finish Product</th>
                <th>Receiving Quantity (Dr.)</th>
                <th>Issue Quantity (Cr.)</th>
                <th>Balance Quantity</th>
                <th>Price Per Kg</th>
                <th>Amount</th>
                <th>S.Tax Rate %</th>
                <th>S.Tax Amount</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($rows as $row): ?>
                <tr>
                    <td><?= $row['Date'] ?></td>
                    <td><?= $row['Reference/Batch Number'] ?></td>
                    <td><?= $row['Supplier/Finish Product'] ?></td>
                    <td><?= $row['Receiving Quantity (Dr.)'] ? number_format($row['Receiving Quantity (Dr.)'], 2) : '-' ?></td>
                    <td><?= $row['Issue Quantity (Cr.)'] ? number_format($row['Issue Quantity (Cr.)'], 2) : '-' ?></td>
                    <td><?= number_format($row['Balance Quantity'], 2) ?></td>
                    <td><?= number_format($row['Price Per Kg'], 2) ?></td>
                    <td><?= number_format($row['Amount'], 2) ?></td>
                    <td><?= $row['S.Tax Rate %'] ? number_format($row['S.Tax Rate %'], 2) : '-' ?></td>
                    <td><?= $row['S.Tax Amount'] ? number_format($row['S.Tax Amount'], 2) : '-' ?></td>
                </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
</body>
</html>
