<?php
ob_start();
require("includes/check_auth_user.php");
require("includes/dbconnect.php");
include('includes/function.php');

$maindb = "rawmain";
$trandb = "rawtran";
$formname = "Customer Weekly Register";

$zroot = $_SESSION["zdir"];
$admin = $_SESSION["admin_" . $dbname];
$zdb   = $_SESSION["zdbyear_" . $dbname];
mysqli_select_db($conn, $zdb);

$party = $_POST["party"];
$dtype = $_POST["dtype"];
$shop  = $_POST["shop"];

$partycond = $shopcond = "";

$mfrmdate = trim($_POST["fdate"]);
$mtodate  = trim($_POST["tdate"]);

if (!empty($party)) $partycond = " AND `party`='$party' ";

if      ($shop == 1) $shopcond = " AND `shop`='1' ";
elseif  ($shop == 2) $shopcond = " AND `shop`='2' ";
elseif  ($shop == 3) $shopcond = " AND `shop`='3' ";

if (empty($mfrmdate)) { browse("From Date Cannot Be Empty"); echo "<script>window.close();</script>"; exit; }
if (empty($mtodate))  { browse("To Date Cannot Be Empty");   echo "<script>window.close();</script>"; exit; }

// ── Main query: grouped by party + date ──────────────────────────────────────
// For dtype==2 we also need individual vouchnos, so fetch them in a subquery
$getpmain = mysqli_query($conn,
    "SELECT party,
            DATE_FORMAT(vouchdt, '%d-%m-%Y') AS mydt,
            vouchdt,
            SUM(subtotal) AS subtotal,
            SUM(lesother) AS lesother,
            SUM(netamt)   AS netamt,
            COUNT(*)      AS vouchers,
            GROUP_CONCAT(vouchno ORDER BY vouchno) AS vouchnos
     FROM `$maindb`
     WHERE vouchdt >= '$mfrmdate'
       AND vouchdt <= '$mtodate'
       $partycond $shopcond
     GROUP BY party, vouchdt
     ORDER BY party, vouchdt"
) or die(mysqli_error($conn));

// ── Helper: fetch tran detail rows for a list of vouchnos ────────────────────
function getTranDetail($conn, $trandb, $vouchnos_str) {
    // Aggregate material+color across all vouchers of that date
    return mysqli_query($conn,
        "SELECT t.item, t.color,
                SUM(t.yard)   AS yard,
                SUM(t.qty)    AS qty,
                SUM(t.amount) AS amount,
                AVG(t.rate)   AS rate
         FROM `$trandb` t
         WHERE t.vouchno IN ($vouchnos_str)
         GROUP BY t.vouchno
         ORDER BY t.item, t.color"
    );
}

// ─────────────────────────────────────────────────────────────────────────────
// VIEW
// ─────────────────────────────────────────────────────────────────────────────
if (isset($_POST["view"])) {
    include("includes/linkshead.php");
    $mfdt = date("d-m-Y", strtotime($mfrmdate));
    $mtdt = date("d-m-Y", strtotime($mtodate));
    ?>
    <style>
        .party-header { background-color:#d4edda; font-weight:bold; }
        .party-total  { background-color:#cce5ff; font-weight:bold; }
        .grand-total  { background-color:#fff3cd; font-weight:bold; }
        .detail-wrap  { background-color:#f8f9fa; }
        .detail-table { width:100%; font-style:italic; font-size:0.92em; }
        .detail-head  { background-color:#cccccc; }
        .detail-total { color:blue; font-weight:bold; }
    </style>
    </head>
    <body>
    <div class="container">
        <?php include("includes/head_rep.php"); ?>
        <div class="row">
          <div class="col-lg-12 pad">
            <h5 class="text-center">
                Party-wise Date-wise Summary
                <?php if ($dtype == 2) echo "<small>(with Material Detail)</small>"; ?>
                &nbsp;|&nbsp; <?= $mfdt ?> to <?= $mtdt ?>
            </h5>

            <table class="table table-bordered table-condensed table-hover"
                   style="background:white;width:100%">
                <thead class="mySearchTr">
                    <tr>
                        <th width="12%">Date</th>
                        <th width="8%">Vouchers</th>
                        <th width="15%" class="text-right">Amount</th>
                        <th width="15%" class="text-right">Discount</th>
                        <th width="15%" class="text-right">Bill Amt</th>
                    </tr>
                </thead>
                <tbody>
                <?php
                // Bucket rows by party
                $data = [];
                while ($row = mysqli_fetch_array($getpmain)) {
                    $data[$row['party']][] = $row;
                }

                $grandAmt = $grandDisc = $grandBill = 0;

                foreach ($data as $partyCode => $rows) {

                    // Party name
                    $res   = mysqli_query($conn, "SELECT name FROM accmast WHERE accode='$partyCode'");
                    $acc   = mysqli_fetch_array($res);
                    $pname = $acc['name'] ?? $partyCode;

                    $partyAmt = $partyDisc = $partyBill = 0;

                    // ── Party header ──────────────────────────────────────────
                    echo "<tr class='party-header'>";
                    echo "<td colspan='5'>&#9658; $pname</td>";
                    echo "</tr>";

                    foreach ($rows as $r) {

                        // ── Date summary row ──────────────────────────────────
                        echo "<tr>";
                        echo "<td><b>{$r['mydt']}</b></td>";
                        echo "<td>{$r['vouchers']}</td>";
                        echo "<td class='text-right'>{$r['subtotal']}</td>";
                        echo "<td class='text-right'>{$r['lesother']}</td>";
                        echo "<td class='text-right'>{$r['netamt']}</td>";
                        echo "</tr>";

                        // ── dtype == 2: Material detail under this date ───────
                        if ($dtype == 2) {
                            $vouchnos_str = $r['vouchnos']; // already comma-separated

                            $getTran = getTranDetail($conn, $trandb, $vouchnos_str);

                            $dtotYard = $dtotQty = $dtotAmt = 0;
                            $hasRows  = false;

                            $detailRows = '';
                            $ctr2 = 0;

                            while ($tran = mysqli_fetch_array($getTran)) {
                                $hasRows = true;

                                // Item name
                                $iRes  = mysqli_query($conn, "SELECT name FROM rawmat WHERE code='{$tran['item']}'");
                                $iRow  = mysqli_fetch_array($iRes);
                                $iname = $iRow['name'] ?? $tran['item'];

                                // Color name
                                $cRes  = mysqli_query($conn, "SELECT name FROM color WHERE code='{$tran['color']}'");
                                $cRow  = mysqli_fetch_array($cRes);
                                $cname = $cRow['name'] ?? $tran['color'];

                                $ctr2++;
                                $yard   = number_format($tran['yard'],   2);
                                $qty    = number_format($tran['qty'],     2);
                                $rate   = number_format($tran['rate'],    2);
                                $amount = number_format($tran['amount'],  2);

                                $detailRows .= "<tr>
                                    <td>$ctr2</td>
                                    <td>$iname</td>
                                    <td>$cname</td>
                                    <td class='text-right'>$yard</td>
                                    <td class='text-right'>$qty</td>
                                    <td class='text-right'>$rate</td>
                                    <td class='text-right'>$amount</td>
                                </tr>";

                                $dtotYard += $tran['yard'];
                                $dtotQty  += $tran['qty'];
                                $dtotAmt  += $tran['amount'];
                            }

                            if ($hasRows) {
                                echo "<tr class='detail-wrap'>";
                                echo "<td colspan='5' style='padding:4px 8px 8px 30px;'>";
                                echo "<table class='detail-table table table-condensed table-bordered'>";
                                echo "<thead class='detail-head'>
                                        <tr>
                                          <th width='4%'>Sr</th>
                                          <th width='22%'>Material</th>
                                          <th width='14%'>Color</th>
                                          <th width='10%' class='text-right'>Yard</th>
                                          <th width='10%' class='text-right'>Qty</th>
                                          <th width='10%' class='text-right'>Rate</th>
                                          <th width='12%' class='text-right'>Amount</th>
                                        </tr>
                                      </thead><tbody>";

                                echo $detailRows;

                                // Detail subtotal
                                echo "<tr class='detail-total'>
                                        <td colspan='3' class='text-right'><b>Total</b></td>
                                        <td class='text-right'>" . number_format($dtotYard, 2) . "</td>
                                        <td class='text-right'>" . number_format($dtotQty,  2) . "</td>
                                        <td></td>
                                        <td class='text-right'>" . number_format($dtotAmt,  2) . "</td>
                                      </tr>";

                                echo "</tbody></table>";
                                echo "</td></tr>";
                            }
                        }
                        // ─────────────────────────────────────────────────────

                        $partyAmt  += $r['subtotal'];
                        $partyDisc += $r['lesother'];
                        $partyBill += $r['netamt'];
                    }

                    // ── Party subtotal ────────────────────────────────────────
                    echo "<tr class='party-total'>";
                    echo "<td colspan='2' class='text-right'>$pname &mdash; Total</td>";
                    echo "<td class='text-right'>" . number_format($partyAmt,  2) . "</td>";
                    echo "<td class='text-right'>" . number_format($partyDisc, 2) . "</td>";
                    echo "<td class='text-right'>" . number_format($partyBill, 2) . "</td>";
                    echo "</tr>";
                    echo "<tr><td colspan='5' style='height:8px;border:none'></td></tr>";

                    $grandAmt  += $partyAmt;
                    $grandDisc += $partyDisc;
                    $grandBill += $partyBill;
                }

                // ── Grand total ───────────────────────────────────────────────
                echo "<tr class='grand-total'>";
                echo "<td colspan='2' class='text-right'><h5>Grand Total</h5></td>";
                echo "<td class='text-right'><h5>" . number_format($grandAmt,  2) . "</h5></td>";
                echo "<td class='text-right'><h5>" . number_format($grandDisc, 2) . "</h5></td>";
                echo "<td class='text-right'><h5>" . number_format($grandBill, 2) . "</h5></td>";
                echo "</tr>";
                ?>
                </tbody>
            </table>
          </div>
        </div>
    </div>
    <?php include("includes/linksfoot.php"); ?>
    </body></html>
<?php
}

// ─────────────────────────────────────────────────────────────────────────────
// EXCEL
// ─────────────────────────────────────────────────────────────────────────────
if (isset($_POST["excel"])) {

    $getpmain2 = mysqli_query($conn,
        "SELECT party,
                DATE_FORMAT(vouchdt, '%d-%m-%Y') AS mydt,
                vouchdt,
                SUM(subtotal) AS subtotal,
                SUM(lesother) AS lesother,
                SUM(netamt)   AS netamt,
                COUNT(*)      AS vouchers,
                GROUP_CONCAT(vouchno ORDER BY vouchno) AS vouchnos
         FROM `$maindb`
         WHERE vouchdt >= '$mfrmdate'
           AND vouchdt <= '$mtodate'
           $partycond $shopcond
         GROUP BY party, vouchdt
         ORDER BY party, vouchdt"
    ) or die(mysqli_error($conn));

    $data = [];
    while ($row = mysqli_fetch_array($getpmain2)) {
        $data[$row['party']][] = $row;
    }

    $contents  = "";
    $grandAmt  = $grandDisc = $grandBill = 0;

    foreach ($data as $partyCode => $rows) {
        $res   = mysqli_query($conn, "SELECT name FROM accmast WHERE accode='$partyCode'");
        $acc   = mysqli_fetch_array($res);
        $pname = $acc['name'] ?? $partyCode;

        $partyAmt = $partyDisc = $partyBill = 0;

        // Party header line
        $contents .= '"' . $pname . '"' . "\n";

        if ($dtype == 2) {
            $contents .= "Date,Vouchers,Amount,Discount,Bill Amt\n";
        } else {
            $contents .= "Date,Vouchers,Amount,Discount,Bill Amt\n";
        }

        foreach ($rows as $r) {

            $contents .= $r['mydt']     . ','
                       . $r['vouchers'] . ','
                       . $r['subtotal'] . ','
                       . $r['lesother'] . ','
                       . $r['netamt']   . "\n";

            // dtype == 2: material detail rows
            if ($dtype == 2) {
                $vouchnos_str = $r['vouchnos'];
                $getTran = getTranDetail($conn, $trandb, $vouchnos_str);

                $dtotYard = $dtotQty = $dtotAmt = 0;
                $hasRows  = false;

                $contents .= ",Sr,Material,Color,Yard,Qty,Rate,Amount\n";

                $ctr2 = 0;
                while ($tran = mysqli_fetch_array($getTran)) {
                    $hasRows = true;

                    $iRes  = mysqli_query($conn, "SELECT name FROM rawmat WHERE code='{$tran['item']}'");
                    $iRow  = mysqli_fetch_array($iRes);
                    $iname = $iRow['name'] ?? $tran['item'];

                    $cRes  = mysqli_query($conn, "SELECT name FROM color WHERE code='{$tran['color']}'");
                    $cRow  = mysqli_fetch_array($cRes);
                    $cname = $cRow['name'] ?? $tran['color'];

                    $ctr2++;
                    $contents .= ',' . $ctr2
                               . ',"' . $iname . '"'
                               . ',"' . $cname . '"'
                               . ',' . $tran['yard']
                               . ',' . $tran['qty']
                               . ',' . number_format($tran['rate'], 2)
                               . ',' . $tran['amount']
                               . "\n";

                    $dtotYard += $tran['yard'];
                    $dtotQty  += $tran['qty'];
                    $dtotAmt  += $tran['amount'];
                }

                if ($hasRows) {
                    $contents .= ',,,Total,'
                               . number_format($dtotYard, 2) . ','
                               . number_format($dtotQty,  2) . ',,'
                               . number_format($dtotAmt,  2) . "\n";
                }
            }

            $partyAmt  += $r['subtotal'];
            $partyDisc += $r['lesother'];
            $partyBill += $r['netamt'];
        }

        // Party total line
        $contents .= '"' . $pname . ' - Total",,,'
                   . number_format($partyAmt,  2) . ','
                   . number_format($partyDisc, 2) . ','
                   . number_format($partyBill, 2) . "\n\n";

        $grandAmt  += $partyAmt;
        $grandDisc += $partyDisc;
        $grandBill += $partyBill;
    }

    $contents .= 'Grand Total,,,'
               . number_format($grandAmt,  2) . ','
               . number_format($grandDisc, 2) . ','
               . number_format($grandBill, 2) . "\n";

    $contents = strip_tags($contents);

    $suffix = ($dtype == 2) ? "_Detail" : "_Summary";
    header("Content-Disposition: attachment; filename={$formname}{$suffix}_" . date('d-m-Y') . ".csv");
    print $contents;
}
?>