<?php
include './connection_sql.php';
session_start();

if ($_GET['rtype'] == "detail") {
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <title>Supplier Credit Note Report</title>
        <link rel="stylesheet" href="css/bootstrap.min.css">
        <style>
            body { font-size:12px; }
            .red { background-color:#FCCF50; }
        </style>
    </head>
    <body>
        <center>
            <?php
            // Fetch Company Details for Header
            $sql_company = "select * from invpara";
            $result_g = $conn->query($sql_company);
            $row_g = $result_g->fetch();
            
            echo "<h3>" . $row_g["COMPANY"] . "</h3>";
            echo "<h5>Supplier Credit Note Report From " . $_GET["dtfrom"] . " To " . $_GET["dtto"] . "</h5>";
            ?>

            <table class="table table-bordered">
                <tr>
                    <th style="width: 50px;">D Code</th>
                    <th style="width: 50px;">Serial No</th>
                    <th style="width: 80px;">Document Date</th>
                    <th style="width: 110px;">Credit Note No</th>
                    <th style="width: 110px;">TIN</th>
                    <th style="width: 179px;">Name of the Supplier/Purchaser</th>
                    <th style="width: 122px;">Description</th>
                    <th style="width: 50px;">Gross Value</th>
                    <th style="width: 50px;">VAT Amount</th>
                </tr>

                <?php
                $mtot = 0;
                $mtot1 = 0;
                $i = 1;

                // Explicitly target ONLY documents containing '/ARR/'
                $sql = "SELECT * FROM s_salma 
                        WHERE company='".$_SESSION['company']."' 
                        AND sdate >='" . $_GET["dtfrom"] . "' 
                        AND sdate <='" . $_GET["dtto"] . "' 
                        AND cancell='0' 
                        AND REF_NO LIKE '%/ARR/%' 
                        ORDER BY id";

                foreach ($conn->query($sql) as $row) {
                    echo "<tr>";
                    echo "<td>" . $row['C_CODE'] . "</td>";
                    echo "<td>" . $i . "</td>";
                    echo "<td>" . date("m/d/Y", strtotime($row['SDATE'])) . "</td>";
                    echo "<td>" . $row['REF_NO'] . "</td>";

                    // VAT TIN Lookup Logic 
                    $mvatno = substr(trim($row['vat_no']), 0, 9);
                    $sql_vendor = "select * from vendor where code ='" . $row['C_CODE'] . "'";
                    $result_vendor = $conn->query($sql_vendor);
                    $row_vendor = $result_vendor->fetch();
                    
                    if (strlen($row['vat_no']) < 3) {
                        if ($row_vendor['vatgroup'] <> "") {
                            $sql_vat = "select * from vatgroup where code ='" . $row_vendor['vatgroup'] . "'";
                            $result_v = $conn->query($sql_vat);
                            if ($row_v = $result_v->fetch()) {
                                $mvatno = $row_v['vatno'];
                            }
                        } else {
                            $sql_vat = "select * from vatgroup where code ='PRIVATE'";
                            $result_v = $conn->query($sql_vat);
                            if ($row_v = $result_v->fetch()) {
                                $mvatno = $row_v['vatno'];
                            }
                        }
                    }

                    echo "<td>" . $mvatno . "</td>";
                    echo "<td>" . $row['CUS_NAME'] . "</td>";
                    echo "<td>Supplier Credit Note</td>";

                    // DIRECT MAPPING AS REQUESTED
                    $gross_value = $row["GRAND_TOT"];
                    $vat_amount = $row["GST"];

                    echo "<td align=\"right\">" . number_format($gross_value, 2, ".", "") . "</td>";
                    echo "<td align=\"right\">" . number_format($vat_amount, 2, ".", "") . "</td>";

                    $mtot += $gross_value;
                    $mtot1 += $vat_amount;

                    $i++;
                    echo "</tr>";
                }
                
                // Footer Totals Row
                echo "<tr>";
                echo "<td colspan='7'></td>";
                echo "<td align=\"right\"><b>" . number_format($mtot, 2, ".", "") . "</b></td>";
                echo "<td align=\"right\"><b>" . number_format($mtot1, 2, ".", "") . "</b></td>";
                echo "</tr>";
                ?>
            </table>
        </center>
    </body>
</html>
<?php
}
?>