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

ini_set('display_errors', 1);
error_reporting(E_ERROR);

require '../vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;

$zdb = $_SESSION["zdbyear_" . $dbname];

mysqli_select_db($conn, $zdb);

mysqli_set_charset($conn, "utf8");

$transporter = $_REQUEST['transporter'] ?? '';
$partyid = $_REQUEST['party'] ?? '';
$mfrmdate = trim($_POST["fdate"]);
$mtodate = trim($_POST["tdate"]);

$frdt = date('d-m-Y', strtotime($mfrmdate));
$todt = date('d-m-Y', strtotime($mtodate));

$checks = (isset($_POST['check'])) ? $_POST['check'] : array();
$custcond = "";
if (count($checks) > 0) {
    foreach ($checks as $check) {
        //        echo $check . ' ';
        $mycheck2[$count2++] .= "OR `accode` = '" . $check . "' ";
    }
    foreach ($mycheck2 as $g => $values2) {
        $mcheck2 .= $values2;
    }

    $custcond = " AND (`accode` = '' " . $mcheck2 . " )";
}

//browse($custcond);
//die();

if (!empty($transporter)) {
    $transportercond = "AND transporter = '$transporter' ";
}

$partyList = [];

if ($partyid != '') {

    // SINGLE PARTY
    $partyList[] = $partyid;
} else {

    // ALL PARTIES
    $qryParty = mysqli_query($conn, "SELECT `accode`,`name` FROM `accmast` WHERE grpcode = '010' $transportercond $custcond ORDER BY `name`");
    while ($rp = mysqli_fetch_assoc($qryParty)) {
        $partyList[] = $rp['accode'];
    }
}

// $findparty = mysqli_query($conn, "SELECT `accode`,`name` FROM `accmast` WHERE `accode`='$partyid'") or die(mysqli_error($conn));
// $lparty = mysqli_fetch_array($findparty);

if (isset($_POST["view"])) {
    ?>
    <!DOCTYPE html>
    <html>

    <head>
        <meta charset="utf-8">

        <title>Join Report</title>

        <style>
            body {
                margin: 0;
                padding: 8px;
                font-family: Arial, Helvetica, sans-serif;
                background: #fff;
                color: #000;
            }

            .reporttitle {
                text-align: center;
                font-size: 20px;
                font-weight: bold;
                margin-bottom: 10px;
                margin-top: 5px;
            }

            .printbtn {
                text-align: center;
                margin-bottom: 10px;
            }

            .printbtn button {
                padding: 7px 18px;
                font-size: 14px;
                border: 1px solid #999;
                background: #f5f5f5;
                cursor: pointer;
            }

            .mainwrap {
                width: 100%;
                display: flex;
                justify-content: space-between;
                align-items: stretch;
                gap: 6px;
            }

            /* STOCK */
            .box:nth-child(1) {
                width: 24%;
                display: flex;
            }

            /* BANK */
            .box:nth-child(2) {
                width: 29%;
                display: flex;
            }

            /* ACCESSORIES */
            .box:nth-child(3) {
                width: 47%;
                display: flex;
            }

            .box table {
                height: 100%;
            }

            table {
                width: 100%;
                border-collapse: collapse;
                table-layout: fixed;
                font-size: 12px;
                height: 100%;
            }

            thead {
                display: table-header-group;
            }

            tfoot {
                display: table-footer-group;
            }

            tr {
                page-break-inside: avoid !important;
            }

            tbody tr {
                height: 24px;
            }

            th,
            td {
                border: 1px solid #000;
                padding: 3px 4px;
                vertical-align: middle;
                overflow: hidden;
                white-space: nowrap;
            }

            th {
                background: #ececec;
                text-align: center;
                font-weight: bold;
            }

            .center {
                text-align: center;
            }

            .right {
                text-align: right;
            }

            .totalrow {
                background: #f1f1f1;
                font-weight: bold;
            }

            .highlight {
                background: #ffffff;
                font-weight: bold;
            }

            .smallfont {
                font-size: 9.5px;
            }

            .summarybox {
                width: 320px;
                margin: 20px auto 0;
            }

            .finalamt-positive {
                background: #ff4d4d;
                color: #fff;
                font-weight: bold;
            }

            .finalamt-negative {
                background: #008000;
                color: #fff;
                font-weight: bold;
            }

            @page {
                size: A4 portrait;
                margin: 6mm;
            }

            @media print {

                html,
                body {
                    width: 210mm;
                    height: 297mm;
                }

                body {
                    padding: 0;
                    margin: 0;
                    zoom: 98%;
                }

                .printbtn {
                    display: none;
                }

                table {
                    font-size: 10px;
                }

                th,
                td {
                    padding: 2px 3px;
                }

                tbody tr {
                    height: 22px;
                }

                .smallfont {
                    font-size: 8px;
                }

            }
        </style>
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>

        <script>
            $(function () {
                $(document).on("dblclick", ".clickable-row2", function () {
                    window.open(
                        $(this).attr("data-href"),
                        $(this).attr("data-target") || "_self"
                    );
                });
            });
        </script>
    </head>

    <body>

        <div class="printbtn">
            <button onclick="window.print()">PRINT</button>
        </div>

        <div id="pdfarea">

            <?php
            foreach ($partyList as $partyid) {

                /* =========================
                  PARTY NAME
                  ========================= */

                $findparty = mysqli_query($conn, "SELECT `accode`,`name` FROM `accmast` WHERE `accode`='$partyid'");
                $lparty = mysqli_fetch_array($findparty);

                /* =========================
                  STOCK
                  ========================= */

                $qStock = mysqli_query($conn, "SELECT vouchno, vouchdt, qty, rate, amount FROM stockent WHERE party='$partyid'
                                                          AND `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' ORDER BY vouchdt");
                $stockTotal = 0;
                $stockRows = [];

                while ($r = mysqli_fetch_assoc($qStock)) {

                    $stockRows[] = $r;
                    $stockTotal += $r['amount'];
                }


                /* =========================
                  BANK
                  ========================= */

                $qBank = mysqli_query($conn, "SELECT vouchno, vouchdt, prname, amount, `group` FROM bankent WHERE party='$partyid'
                                                         AND `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' 
                                                            ORDER BY
                                                                CASE
                                                                    WHEN `group` = 2 THEN 1   -- SATURDAY
                                                                    WHEN `group` = 3 THEN 2   -- WEDNESDAY
                                                                    WHEN `group` = 4 THEN 3   -- RP
                                                                    ELSE 4
                                                                END,
                                                                vouchdt ");
                $bankTotal = 0;
                $bankRows = [];

                while ($r = mysqli_fetch_assoc($qBank)) {

                    $bankRows[] = $r;
                    $bankTotal += $r['amount'];
                }


                /* =========================
                  ACCESSORIES
                  ========================= */

                $qSale = mysqli_query($conn, "SELECT sm.vouchdt, st.item, st.rate, SUM(st.qty) qty, SUM(st.amount) amount
                                                         FROM rawtran st LEFT JOIN rawmain sm ON sm.vouchno = st.vouchno
                                                         WHERE sm.party='$partyid'  AND st.`vouchdt` >= '$mfrmdate' AND st.`vouchdt` <= '$mtodate'
                                                         GROUP BY sm.vouchdt,st.item, st.rate ORDER BY sm.vouchdt, st.item");

                $accTotal = 0;
                $saleRows = [];

                while ($r = mysqli_fetch_assoc($qSale)) {

                    $saleRows[] = $r;
                    $accTotal += $r['amount'];
                }


                /* =========================
                  FINAL
                  ========================= */

                $finalAmount = ($accTotal + $bankTotal) - $stockTotal;

                $maxRows = max(
                    count($stockRows),
                    count($bankRows),
                    count($saleRows)
                );
                ?>



                <div style="page-break-after:always;">


                    <div class="reporttitle">
                        <?php
                        echo $lparty["name"] . " ( " . $frdt . " - " . $todt . ")";
                        ?>
                        <input type="hidden" id="nmparty" value="<?= $lparty['name'] ?>">
                    </div>

                    <div class="mainwrap">


                        <!-- ================= STOCK ================= -->

                        <div class="box">


                            <table class="stocktbl">

                                <thead>

                                    <tr>
                                        <th colspan="4">STOCK (MAL)</th>
                                    </tr>

                                    <colgroup>
                                        <col style="width:25%">
                                        <col style="width:20%">
                                        <col style="width:25%">
                                        <col style="width:30%">
                                    </colgroup>

                                    <tr>
                                        <th class="center">DATE</th>
                                        <th class="right">QTY</th>
                                        <th class="right">RATE</th>
                                        <th class="right">AMOUNT</th>
                                    </tr>

                                </thead>

                                <tbody>

                                    <?php
                                    for ($i = 0; $i < $maxRows; $i++) {
                                        $r = $stockRows[$i] ?? null;
                                        ?>

                                        <tr class="clickable-row2" data-href="stockent.php?vouchno=<?= $r['vouchno'] ?>&mode=edit"
                                            data-target="_blank">

                                            <td class="center">
                                                <?php echo $r ? date('j.n.y', strtotime($r['vouchdt'])) : '&nbsp;'; ?>
                                            </td>

                                            <td class="right">
                                                <?php echo $r ? number_format($r['qty']) : '&nbsp;'; ?>
                                            </td>

                                            <td class="right">
                                                <?php echo $r ? number_format($r['rate']) : '&nbsp;'; ?>
                                            </td>

                                            <td class="right">
                                                <?php echo $r ? number_format($r['amount']) : '&nbsp;'; ?>
                                            </td>

                                        </tr>

                                        <?php
                                    }
                                    ?>

                                </tbody>

                                <tfoot>

                                    <tr class="totalrow">

                                        <td colspan="3" class="right">
                                            TOTAL
                                        </td>

                                        <td class="right">
                                            <?php echo number_format($stockTotal); ?>
                                        </td>

                                    </tr>

                                </tfoot>

                            </table>

                        </div>



                        <!-- ================= BANK ================= -->

                        <div class="box">

                            <table class="banktbl">

                                <thead>

                                    <tr>
                                        <th colspan="3">BANK (PESA)</th>
                                    </tr>

                                    <colgroup>
                                        <col style="width:22%">
                                        <col style="width:48%">
                                        <col style="width:30%">
                                    </colgroup>

                                    <tr>
                                        <th class="center">DATE</th>
                                        <th class="center">PARTY NAME</th>
                                        <th class="right">AMOUNT</th>
                                    </tr>

                                </thead>

                                <tbody>

                                    <?php
                                    for ($i = 0; $i < $maxRows; $i++) {
                                        $r = $bankRows[$i] ?? null;

                                        $prname = '';
                                        $colr = "";

                                        if ($r) {

                                            if ($r['group'] == 1) {

                                                $prname = $r['prname'];
                                                $colr = "";

                                            } else {

                                                $groupoptions = [
                                                    '1' => 'BANK',
                                                    '2' => 'SATURDAY',
                                                    '3' => 'WEDNESDAY',
                                                    '4' => 'RP',
                                                    '5' => 'OTHERS'
                                                ];

                                                $prname = $groupoptions[$r['group']] ?? '';
                                                $colr = "color:blue";
                                            }
                                        }
                                        ?>

                                        <tr class="clickable-row2" data-href="bankent.php?vouchno=<?= $r['vouchno'] ?>&mode=edit"
                                            data-target="_blank">

                                            <td class="center">
                                                <?php echo $r ? date('j.n.y', strtotime($r['vouchdt'])) : '&nbsp;'; ?>
                                            </td>

                                            <td class="center" style="<?= $colr ?>">
                                                <?php echo $prname ?? '&nbsp;'; ?>
                                            </td>

                                            <td class="right">
                                                <?php echo $r ? number_format($r['amount']) : '&nbsp;'; ?>
                                            </td>

                                        </tr>

                                        <?php
                                    }
                                    ?>

                                </tbody>

                                <tfoot>

                                    <tr class="totalrow">

                                        <td colspan="2" class="right">
                                            TOTAL
                                        </td>

                                        <td class="right">
                                            <?php echo number_format($bankTotal); ?>
                                        </td>

                                    </tr>

                                </tfoot>

                            </table>

                        </div>



                        <!-- ================= ACCESSORIES ================= -->

                        <div class="box">

                            <table class="acctbl">

                                <thead>

                                    <tr>
                                        <th colspan="5">ACCESSORIES (SAMAAN)</th>
                                    </tr>

                                    <colgroup>
                                        <col style="width:18%">
                                        <col style="width:40%">
                                        <col style="width:12%">
                                        <col style="width:12%">
                                        <col style="width:18%">
                                    </colgroup>

                                    <tr>
                                        <th class="center">DATE</th>
                                        <th class="center">ITEM</th>
                                        <th class="right">QTY</th>
                                        <th class="right">RATE</th>
                                        <th class="right">TOTAL</th>
                                    </tr>

                                </thead>

                                <tbody>

                                    <?php
                                    for ($i = 0; $i < $maxRows; $i++) {
                                        $r = $saleRows[$i] ?? null;

                                        $fitem = null;

                                        if ($r && !empty($r['item'])) {
                                            $finditem = mysqli_query($conn, "SELECT `code`,`name` FROM `rawmat` WHERE `code`='{$r['item']}'") or die(mysqli_error($conn));
                                            $fitem = mysqli_fetch_object($finditem);
                                        }
                                        ?>

                                        <tr class="<?php echo $r ? 'highlight' : ''; ?>">

                                            <td class="center">
                                                <?php echo $r ? date('j.n.y', strtotime($r['vouchdt'])) : '&nbsp;'; ?>
                                            </td>

                                            <td class="smallfont center">
                                                <?php
                                                echo isset($fitem->name)
                                                    ? htmlspecialchars(mb_strimwidth($fitem->name, 0, 30, '...'))
                                                    : '&nbsp;';
                                                ?>
                                            </td>

                                            <td class="right">
                                                <?php echo $r ? number_format($r['qty']) : '&nbsp;'; ?>
                                            </td>

                                            <td class="right">
                                                <?php echo $r ? number_format($r['rate'], 2) : '&nbsp;'; ?>
                                            </td>

                                            <td class="right">
                                                <?php echo $r ? number_format($r['amount'], 2) : '&nbsp;'; ?>
                                            </td>

                                        </tr>

                                        <?php
                                    }
                                    ?>

                                </tbody>

                                <tfoot>

                                    <tr class="totalrow">

                                        <td colspan="4" class="right">
                                            TOTAL
                                        </td>

                                        <td class="right">
                                            <?php echo number_format($accTotal, 2); ?>
                                        </td>

                                    </tr>

                                </tfoot>

                            </table>

                        </div>

                    </div>



                    <!-- ================= SUMMARY ================= -->

                    <div class="summarybox">

                        <table>

                            <tr>
                                <th colspan="2">SUMMARY</th>
                            </tr>

                            <tr>
                                <td>
                                    ACCESSORIES
                                </td>
                                <td class="right">
                                    <?php echo number_format($accTotal, 2); ?>
                                </td>
                            </tr>

                            <tr>

                                <td>
                                    BANK
                                </td>

                                <td class="right">
                                    <?php echo number_format($bankTotal, 2); ?>
                                </td>

                            </tr>

                            <tr>

                                <td>
                                    STOCK
                                </td>

                                <td class="right">
                                    <?php echo number_format($stockTotal, 2); ?>
                                </td>

                            </tr>

                            <tr class="<?= ($finalAmount >= 0) ? 'finalamt-positive' : 'finalamt-negative'; ?>">

                                <td>
                                    FINAL AMOUNT
                                </td>

                                <td class="right">
                                    <?php echo number_format($finalAmount, 2); ?>
                                </td>

                            </tr>

                        </table>

                    </div>

                </div>

            <?php } ?>

        </div>

    </body>

    </html>
    <?php
}


if (isset($_POST["subpdf"]) || isset($_POST["sendwhats"])) {

    require('library/fpdf/fpdf.php');

    /* =========================
      PDF CLASS
      ========================= */

    class PDF extends FPDF
    {

        function Header()
        {
            // Optional Header
        }

        // function Footer()
        // {
        //     $this->SetY(-8);
        //     $this->SetFont('Arial', '', 7);
        //     $this->Cell(0, 5, 'Page ' . $this->PageNo(), 0, 0, 'C');
        // }
    }

    /* =========================
      SINGLE PDF MODE
      ========================= */

    if (isset($_POST["subpdf"])) {

        $pdf = new PDF('P', 'mm', 'A4');
        $pdf->SetMargins(3, 3, 3);
        // $pdf->SetAutoPageBreak(false);
    }


    foreach ($partyList as $partyid) {

        /* =========================
          SEPARATE PDF FOR WHATSAPP
          ========================= */

        if (isset($_POST["sendwhats"])) {

            $pdf = new PDF('P', 'mm', 'A4');
            $pdf->SetMargins(3, 3, 3);
            // $pdf->SetAutoPageBreak(false);
        }

        /* =========================
          PARTY DETAILS
          ========================= */

        $findparty = mysqli_query($conn, "
            SELECT accode, name ,mobile
            FROM accmast 
            WHERE accode='$partyid'
        ");

        $lparty = mysqli_fetch_assoc($findparty);
        $mobileno = $lparty['mobile'];

        /* =========================
          STOCK DATA
          ========================= */

        $qStock = mysqli_query($conn, "
            SELECT vouchdt, qty, rate, amount 
            FROM stockent 
            WHERE party='$partyid'
            AND vouchdt >= '$mfrmdate'
            AND vouchdt <= '$mtodate'
            ORDER BY vouchdt
        ");

        $stockRows = [];
        $stockTotal = 0;

        while ($r = mysqli_fetch_assoc($qStock)) {

            $stockRows[] = $r;
            $stockTotal += $r['amount'];
        }

        /* =========================
          BANK DATA
          ========================= */

        $qBank = mysqli_query($conn, "
            SELECT vouchdt, prname, amount, `group`
            FROM bankent
            WHERE party='$partyid'
            AND vouchdt >= '$mfrmdate'
            AND vouchdt <= '$mtodate'
            ORDER BY vouchdt
        ");

        $bankRows = [];
        $bankTotal = 0;

        while ($r = mysqli_fetch_assoc($qBank)) {

            $bankRows[] = $r;
            $bankTotal += $r['amount'];
        }

        /* =========================
          ACCESSORIES DATA
          ========================= */

        $qSale = mysqli_query($conn, "
            SELECT 
                sm.vouchdt,
                st.item,
                st.rate,
                SUM(st.qty) qty,
                SUM(st.amount) amount
            FROM rawtran st
            LEFT JOIN rawmain sm ON sm.vouchno = st.vouchno
            WHERE sm.party='$partyid'
            AND st.vouchdt >= '$mfrmdate'
            AND st.vouchdt <= '$mtodate'
            GROUP BY st.item, st.rate
            ORDER BY sm.vouchdt, st.item
        ");

        $saleRows = [];
        $accTotal = 0;

        while ($r = mysqli_fetch_assoc($qSale)) {

            $saleRows[] = $r;
            $accTotal += $r['amount'];
        }

        /* =========================
          FINAL CALCULATION
          ========================= */

        $finalAmount = ($accTotal + $bankTotal) - $stockTotal;

        $maxRows = max(
            count($stockRows),
            count($bankRows),
            count($saleRows)
        );

        /* =========================
          PAGE
          ========================= */
        /* =========================
           FIRST PAGE
           ========================= */

        $pdf->AddPage();

        $startY = 15;

        $x1 = 3;
        $x2 = 59;
        $x3 = 127;

        $rowH = 5;

        /* =========================
           PARTY TITLE
           ========================= */

        $pdf->SetTextColor(0, 0, 0);

        $pdf->SetFont('Arial', 'B', 16);
        $pdf->Cell(0, 8, strtoupper($lparty['name']) . " (" . $frdt . " - " . $todt . ")", 0, 1, 'C');

        $pdf->Ln(2);

        /* =========================
           STOCK HEADER
           ========================= */

        $pdf->SetXY($x1, $startY);

        $pdf->SetFont('Arial', 'B', 8);
        $pdf->Cell(55, 6, 'STOCK (MAL)', 1, 1, 'C');

        $pdf->SetX($x1);

        $pdf->SetFont('Arial', 'B', 7);

        $pdf->Cell(16, 5, 'DATE', 1, 0, 'C');
        $pdf->Cell(10, 5, 'QTY', 1, 0, 'R');
        $pdf->Cell(12, 5, 'RATE', 1, 0, 'R');
        $pdf->Cell(17, 5, 'AMOUNT', 1, 1, 'R');

        /* =========================
           BANK HEADER
           ========================= */

        $pdf->SetXY($x2, $startY);

        $pdf->SetFont('Arial', 'B', 8);
        $pdf->Cell(67, 6, 'BANK (PESA)', 1, 1, 'C');

        $pdf->SetX($x2);

        $pdf->SetFont('Arial', 'B', 7);

        $pdf->Cell(16, 5, 'DATE', 1, 0, 'C');
        $pdf->Cell(33, 5, 'PARTY NAME', 1, 0, 'C');
        $pdf->Cell(18, 5, 'AMOUNT', 1, 1, 'R');

        /* =========================
           ACCESSORIES HEADER
           ========================= */

        $pdf->SetXY($x3, $startY);

        $pdf->SetFont('Arial', 'B', 8);
        $pdf->Cell(80, 6, 'ACCESSORIES (SAMAAN)', 1, 1, 'C');

        $pdf->SetX($x3);

        $pdf->SetFont('Arial', 'B', 7);

        $pdf->Cell(12, 5, 'DATE', 1, 0, 'C');
        $pdf->Cell(34, 5, 'ITEM', 1, 0, 'C');
        $pdf->Cell(10, 5, 'QTY', 1, 0, 'R');
        $pdf->Cell(10, 5, 'RATE', 1, 0, 'R');
        $pdf->Cell(14, 5, 'TOTAL', 1, 1, 'R');


        $currentY = $startY + 11;

        for ($i = 0; $i < $maxRows; $i++) {

            /* =========================
                  AUTO PAGE BREAK
                  ========================= */

            if ($currentY > 270) {
                $pdf->AddPage();

                $currentY = 15;

                $x1 = 3;
                $x2 = 59;
                $x3 = 127;

                $rowH = 5;

                /* =========================
                  PARTY TITLE
                  ========================= */

                $pdf->SetFont('Arial', 'B', 16);
                $pdf->Cell(0, 8, strtoupper($lparty['name']), 0, 1, 'C');
                $pdf->Ln(2);

                /* =========================
                   STOCK HEADER
                   ========================= */

                $pdf->SetXY($x1, $currentY);

                $pdf->SetFont('Arial', 'B', 8);
                $pdf->Cell(55, 6, 'STOCK (MAL)', 1, 1, 'C');

                $pdf->SetX($x1);

                $pdf->SetFont('Arial', 'B', 7);

                $pdf->Cell(16, 5, 'DATE', 1, 0, 'C');
                $pdf->Cell(10, 5, 'QTY', 1, 0, 'R');
                $pdf->Cell(12, 5, 'RATE', 1, 0, 'R');
                $pdf->Cell(17, 5, 'AMOUNT', 1, 1, 'R');

                /* =========================
                   BANK HEADER
                   ========================= */

                $pdf->SetXY($x2, $currentY);

                $pdf->SetFont('Arial', 'B', 8);
                $pdf->Cell(67, 6, 'BANK (PESA)', 1, 1, 'C');

                $pdf->SetX($x2);

                $pdf->SetFont('Arial', 'B', 7);

                $pdf->Cell(16, 5, 'DATE', 1, 0, 'C');
                $pdf->Cell(33, 5, 'PARTY NAME', 1, 0, 'C');
                $pdf->Cell(18, 5, 'AMOUNT', 1, 1, 'R');

                /* =========================
                  ACCESSORIES HEADER
                  ========================= */

                $pdf->SetXY($x3, $currentY);

                $pdf->SetFont('Arial', 'B', 8);
                $pdf->Cell(80, 6, 'ACCESSORIES (SAMAAN)', 1, 1, 'C');

                $pdf->SetX($x3);

                $pdf->SetFont('Arial', 'B', 7);

                $pdf->Cell(12, 5, 'DATE', 1, 0, 'C');
                $pdf->Cell(34, 5, 'ITEM', 1, 0, 'C');
                $pdf->Cell(10, 5, 'QTY', 1, 0, 'R');
                $pdf->Cell(10, 5, 'RATE', 1, 0, 'R');
                $pdf->Cell(14, 5, 'TOTAL', 1, 1, 'R');


                $currentY += 11;
            }


            /* =====================================================
               STOCK ROW
               ===================================================== */

            $r1 = $stockRows[$i] ?? null;

            $pdf->SetXY($x1, $currentY);

            $pdf->SetFont('Arial', '', 7);

            $pdf->Cell(16, $rowH, $r1 ? date('j.n.y', strtotime($r1['vouchdt'])) : '', 1, 0, 'C');
            $pdf->Cell(10, $rowH, $r1 ? number_format($r1['qty']) : '', 1, 0, 'R');
            $pdf->Cell(12, $rowH, $r1 ? number_format($r1['rate']) : '', 1, 0, 'R');
            $pdf->Cell(17, $rowH, $r1 ? number_format($r1['amount']) : '', 1, 0, 'R');

            /* =====================================================
               BANK ROW
               ===================================================== */

            $r2 = $bankRows[$i] ?? null;

            $prname = '';

            if ($r2) {

                if ($r2['group'] == 1) {

                    $prname = $r2['prname'];

                } else {

                    $groupoptions = [
                        '1' => 'BANK',
                        '2' => 'SATURDAY',
                        '3' => 'WEDNESDAY',
                        '4' => 'RP',
                        '5' => 'OTHERS'
                    ];

                    $prname = $groupoptions[$r2['group']] ?? '';
                }
            }

            $pdf->SetXY($x2, $currentY);

            $pdf->SetFont('Arial', '', 7);

            $pdf->Cell(16, $rowH, $r2 ? date('j.n.y', strtotime($r2['vouchdt'])) : '', 1, 0, 'C');
            $pdf->Cell(33, $rowH, $prname, 1, 0, 'C');
            $pdf->Cell(18, $rowH, $r2 ? number_format($r2['amount']) : '', 1, 0, 'R');

            /* =====================================================
               ACCESSORIES ROW
               ===================================================== */

            $r3 = $saleRows[$i] ?? null;

            $itemName = '';

            if ($r3 && !empty($r3['item'])) {

                $finditem = mysqli_query($conn, "
            SELECT name 
            FROM rawmat 
            WHERE code='{$r3['item']}'
        ");

                $fitem = mysqli_fetch_assoc($finditem);

                $itemName = isset($fitem['name'])
                    ? mb_strimwidth($fitem['name'], 0, 30, '...')
                    : '';
            }

            $pdf->SetXY($x3, $currentY);

            $pdf->SetFont('Arial', '', 6);

            $pdf->Cell(12, $rowH, $r3 ? date('j.n.y', strtotime($r3['vouchdt'])) : '', 1, 0, 'C');
            $pdf->Cell(34, $rowH, $itemName, 1, 0, 'L');
            $pdf->Cell(10, $rowH, $r3 ? number_format($r3['qty']) : '', 1, 0, 'R');
            $pdf->Cell(10, $rowH, $r3 ? number_format($r3['rate'], 2) : '', 1, 0, 'R');
            $pdf->Cell(14, $rowH, $r3 ? number_format($r3['amount'], 2) : '', 1, 0, 'R');

            /* =========================
               NEXT ROW
               ========================= */

            $currentY += $rowH;
        }

        /* =====================================================
           TOTAL ROWS
           ===================================================== */

        $pdf->SetFont('Arial', 'B', 7);

        /* STOCK TOTAL */
        $pdf->SetXY($x1, $currentY);

        $pdf->Cell(38, 6, 'TOTAL', 1, 0, 'R');
        $pdf->Cell(17, 6, number_format($stockTotal), 1, 0, 'R');

        /* BANK TOTAL */
        $pdf->SetXY($x2, $currentY);

        $pdf->Cell(49, 6, 'TOTAL', 1, 0, 'R');
        $pdf->Cell(18, 6, number_format($bankTotal), 1, 0, 'R');

        /* ACCESSORIES TOTAL */
        $pdf->SetXY($x3, $currentY);

        $pdf->Cell(66, 6, 'TOTAL', 1, 0, 'R');
        $pdf->Cell(14, 6, number_format($accTotal, 2), 1, 0, 'R');


        $currentY += 12;
        /* =====================================================
          SUMMARY
          ===================================================== */

        // $pdf->SetXY(80, 250);
        $summaryY = $currentY;

        $pdf->SetXY(80, $summaryY);

        $pdf->SetFont('Arial', 'B', 8);

        $pdf->Cell(50, 6, 'SUMMARY', 1, 0, 'C');
        $pdf->Cell(30, 6, 'AMOUNT', 1, 1, 'C');

        $pdf->SetX(80);
        $pdf->Cell(50, 6, 'ACCESSORIES', 1, 0, 'L');
        $pdf->Cell(30, 6, number_format($accTotal, 2), 1, 1, 'R');

        $pdf->SetX(80);
        $pdf->Cell(50, 6, 'BANK', 1, 0, 'L');
        $pdf->Cell(30, 6, number_format($bankTotal, 2), 1, 1, 'R');

        $pdf->SetX(80);
        $pdf->Cell(50, 6, 'STOCK', 1, 0, 'L');
        $pdf->Cell(30, 6, number_format($stockTotal, 2), 1, 1, 'R');

        $pdf->SetX(80);

        $pdf->SetTextColor(255, 255, 255);

        if ($finalAmount >= 0) {
            // RED for positive
            $pdf->SetFillColor(255, 0, 0);
        } else {
            // GREEN for negative
            $pdf->SetFillColor(0, 128, 0);
        }

        $pdf->Cell(50, 7, 'FINAL AMOUNT', 1, 0, 'L', true);
        $pdf->Cell(30, 7, number_format($finalAmount, 2), 1, 1, 'R', true);

        /* =========================
          SAVE INDIVIDUAL PDF
          ========================= */

        if (isset($_POST['sendwhats'])) {

            // create fresh pdf object for every loop
            $pdfParty = clone $pdf;

            $myyear = $_SESSION["tit_" . $dbname];

            $safeName = preg_replace('/[^A-Za-z0-9_-]/', '_', trim($lparty['name']));

            $folder = $_SERVER['DOCUMENT_ROOT'] . "/admin/$myyear/jointrep/";

            if (!is_dir($folder)) {

                mkdir($folder, 0777, true);
            }

            // unique filename
            $filename = $safeName . "_" . $partyid . ".pdf";

            $localFile = $folder . $filename;

            // save pdf
            $pdfParty->Output("F", $localFile);

            // public url
            $urlsend = "https://apps2ai.com/stdbags/admin/$myyear/jointrep/" . $filename;
            //            browse($urlsend);

            //            $instid = "6A0C073B370B2";
//            $accstkn = "69fef40818d32";
            $instid = $_SESSION["instid"];
            $accstkn = $_SESSION["accstkn"];
            // $mobileno = 9082336408;

            //            $msgtech = urlencode($urlsend);
//
//            $curl = curl_init();
//            browse($mobileno);
//            browse($msgtech);

            $msgtech = urlencode($urlsend);
            $curl = curl_init();

            curl_setopt_array($curl, array(
                CURLOPT_URL => "https://web.secureleads.in/api/send?number=91$mobileno&type=media&message=JointReport%20(%20$frdt%20-%20$todt%20)&media_url=$msgtech&instance_id=$instid&access_token=$accstkn",
                CURLOPT_RETURNTRANSFER => true,
                CURLOPT_ENCODING => '',
                CURLOPT_MAXREDIRS => 10,
                CURLOPT_TIMEOUT => 0,
                CURLOPT_FOLLOWLOCATION => true,
                CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
                CURLOPT_CUSTOMREQUEST => 'POST',
                CURLOPT_HTTPHEADER => array(
                    'Cookie: stackpost_session=m1ip03t51dk5rlgaa5blakqmva57k3nd'
                ),
            ));

            $response = curl_exec($curl);

            curl_close($curl);

            echo "<pre>";
            print_r($response);

            if ($error) {
                echo $error;
            }
            echo "</pre>";

            // optional delay
            usleep(300000); // 0.3 second
            // usleep(30000000); // 30 second
        }
    }

    /* =========================
      FINAL OUTPUT
      ========================= */

    if (isset($_POST["subpdf"])) {

        $filename = strtoupper($lparty['name']) . " (" . $frdt . " - " . $todt . ")" . '-JOINT_REPORT.pdf';

        $pdf->Output('I', $filename);
    }
}


if (isset($_POST["excel"])) {

    /* =========================
      EXCEL
      ========================= */

    $spreadsheet = new Spreadsheet();

    // REMOVE DEFAULT SHEET
    $spreadsheet->removeSheetByIndex(0);

    foreach ($partyList as $partyid) {

        /* =========================
          PARTY NAME
          ========================= */

        $findparty = mysqli_query($conn, "SELECT `accode`,`name` FROM `accmast` WHERE `accode`='$partyid'");
        $lparty = mysqli_fetch_array($findparty);

        /* =========================
          STOCK
          ========================= */

        $qStock = mysqli_query($conn, "SELECT vouchdt, qty, rate, amount FROM stockent WHERE party='$partyid' 
                                              AND `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' ORDER BY vouchdt");

        $stockTotal = 0;
        $stockRows = [];

        while ($r = mysqli_fetch_assoc($qStock)) {

            $stockRows[] = $r;
            $stockTotal += $r['amount'];
        }


        /* =========================
          BANK
          ========================= */

        $qBank = mysqli_query($conn, "SELECT vouchdt, prname, amount, `group` FROM bankent WHERE party='$partyid'
                                             AND `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' ORDER BY vouchdt");

        $bankTotal = 0;
        $bankRows = [];

        while ($r = mysqli_fetch_assoc($qBank)) {

            $bankRows[] = $r;
            $bankTotal += $r['amount'];
        }


        /* =========================
          ACCESSORIES
          ========================= */

        $qSale = mysqli_query($conn, "SELECT sm.vouchdt, st.item, st.rate, SUM(st.qty) qty, SUM(st.amount) amount 
                                             FROM rawtran st LEFT JOIN rawmain sm ON sm.vouchno = st.vouchno
                                             WHERE sm.party='$partyid'
                                             AND st.`vouchdt` >= '$mfrmdate'
                                             AND st.`vouchdt` <= '$mtodate'
                                             GROUP BY st.item, st.rate
                                             ORDER BY sm.vouchdt, st.item");

        $accTotal = 0;
        $saleRows = [];

        while ($r = mysqli_fetch_assoc($qSale)) {

            $saleRows[] = $r;
            $accTotal += $r['amount'];
        }


        /* =========================
          FINAL
          ========================= */

        $finalAmount = ($accTotal + $bankTotal) - $stockTotal;

        $maxRows = max(
            count($stockRows),
            count($bankRows),
            count($saleRows)
        );

        /* =========================
          CREATE SHEET
          ========================= */

        $sheet = $spreadsheet->createSheet();

        $sheetname = substr(
            preg_replace('/[^A-Za-z0-9]/', '', $lparty['name']),
            0,
            30
        );

        if ($sheetname == '') {
            $sheetname = 'Party_' . $partyid;
        }

        $sheet->setTitle($sheetname);

        /* =========================
          COLUMN WIDTHS
          ========================= */

        $sheet->getColumnDimension('A')->setWidth(12);
        $sheet->getColumnDimension('B')->setWidth(8);
        $sheet->getColumnDimension('C')->setWidth(10);
        $sheet->getColumnDimension('D')->setWidth(14);

        $sheet->getColumnDimension('F')->setWidth(12);
        $sheet->getColumnDimension('G')->setWidth(24);
        $sheet->getColumnDimension('H')->setWidth(14);

        $sheet->getColumnDimension('J')->setWidth(12);
        $sheet->getColumnDimension('K')->setWidth(34);
        $sheet->getColumnDimension('L')->setWidth(10);
        $sheet->getColumnDimension('M')->setWidth(10);
        $sheet->getColumnDimension('N')->setWidth(14);

        /* =========================
          TITLE
          ========================= */

        $sheet->mergeCells('A1:N1');

        $sheet->setCellValue(
            'A1',
            strtoupper($lparty['name']) . " (" . $frdt . " - " . $todt . ")"
        );

        /* =========================
          STYLES
          ========================= */

        $headerStyle = [
            'font' => [
                'bold' => true,
                'size' => 10
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN
                ]
            ],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => [
                    'rgb' => 'EAEAEA'
                ]
            ]
        ];

        $dataStyle = [
            'font' => [
                'size' => 9
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN
                ]
            ]
        ];

        $yellowStyle = [
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => [
                    'rgb' => 'FFFFFF'
                ]
            ]
        ];

        $sheet->getStyle('A1')->applyFromArray([
            'font' => [
                'bold' => true,
                'size' => 16
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER
            ]
        ]);

        /* =========================
          HEADERS
          ========================= */

        $sheet->mergeCells('A3:D3');
        $sheet->setCellValue('A3', 'STOCK (MAL)');

        $sheet->mergeCells('F3:H3');
        $sheet->setCellValue('F3', 'BANK (PESA)');

        $sheet->mergeCells('J3:N3');
        $sheet->setCellValue('J3', 'ACCESSORIES (SAMAAN)');

        $sheet->fromArray([
            ['DATE', 'QTY', 'RATE', 'AMOUNT']
        ], NULL, 'A4');

        $sheet->fromArray([
            ['DATE', 'PARTY NAME', 'AMOUNT']
        ], NULL, 'F4');

        $sheet->fromArray([
            ['DATE', 'ITEM', 'QTY', 'RATE', 'TOTAL']
        ], NULL, 'J4');

        $sheet->getStyle('A3:D4')->applyFromArray($headerStyle);
        $sheet->getStyle('F3:H4')->applyFromArray($headerStyle);
        $sheet->getStyle('J3:N4')->applyFromArray($headerStyle);

        /* =========================
          DATA
          ========================= */

        $row = 5;

        for ($i = 0; $i < $maxRows; $i++) {

            /* STOCK */

            $s = $stockRows[$i] ?? null;

            $sheet->setCellValue(
                'A' . $row,
                $s ? date('d.m.y', strtotime($s['vouchdt'])) : ''
            );

            $sheet->setCellValue(
                'B' . $row,
                $s['qty'] ?? ''
            );

            $sheet->setCellValue(
                'C' . $row,
                $s['rate'] ?? ''
            );

            $sheet->setCellValue(
                'D' . $row,
                $s['amount'] ?? ''
            );

            /* BANK */

            $b = $bankRows[$i] ?? null;

            $prname = '';

            if ($b) {

                if ($b['group'] == 1) {

                    $prname = $b['prname'];
                } else {

                    $groupoptions = [
                        '1' => 'BANK',
                        '2' => 'SATURDAY',
                        '3' => 'WEDNESDAY',
                        '4' => 'RP',
                        '5' => 'OTHERS'
                    ];

                    $prname = $groupoptions[$b['group']] ?? '';
                }
            }


            $sheet->setCellValue(
                'F' . $row,
                $b ? date('d.m.y', strtotime($b['vouchdt'])) : ''
            );

            $sheet->setCellValue(
                'G' . $row,
                $prname ?? ''
            );

            $sheet->setCellValue(
                'H' . $row,
                $b['amount'] ?? ''
            );

            /* ACCESSORIES */

            $a = $saleRows[$i] ?? null;

            $fitem = null;

            $itemName = '';

            if ($a && !empty($a['item'])) {

                $finditem = mysqli_query(
                    $conn,
                    "SELECT `code`,`name`
                     FROM `rawmat`
                     WHERE `code`='{$a['item']}'"
                ) or die(mysqli_error($conn));

                $fitem = mysqli_fetch_array($finditem);


                $itemName = isset($fitem['name'])
                    ? htmlspecialchars(mb_strimwidth($fitem['name'], 0, 30, '...'))
                    : '&nbsp;';
            }

            $sheet->setCellValue(
                'J' . $row,
                $a ? date('d.m.y', strtotime($a['vouchdt'])) : ''
            );

            $sheet->setCellValue(
                'K' . $row,
                $itemName
            );

            $sheet->setCellValue(
                'L' . $row,
                $a['qty'] ?? ''
            );

            $sheet->setCellValue(
                'M' . $row,
                $a['rate'] ?? ''
            );

            $sheet->setCellValue(
                'N' . $row,
                isset($a['amount']) ? round($a['amount']) : ''
            );



            /* ROW STYLE */

            $sheet->getStyle('A' . $row . ':D' . $row)
                ->applyFromArray($dataStyle);

            $sheet->getStyle('F' . $row . ':H' . $row)
                ->applyFromArray($dataStyle);

            $sheet->getStyle('J' . $row . ':N' . $row)
                ->applyFromArray($dataStyle);

            if ($a) {

                $sheet->getStyle('J' . $row . ':N' . $row)
                    ->applyFromArray($yellowStyle);
            }

            $row++;
        }


        /* =========================
          TOTALS
          ========================= */

        $sheet->setCellValue('C' . $row, 'TOTAL');
        $sheet->setCellValue('D' . $row, $stockTotal);

        $sheet->setCellValue('G' . $row, 'TOTAL');
        $sheet->setCellValue('H' . $row, $bankTotal);

        $sheet->setCellValue('M' . $row, 'TOTAL');
        $sheet->setCellValue('N' . $row, round($accTotal));

        $sheet->getStyle('A' . $row . ':N' . $row)
            ->applyFromArray($headerStyle);

        /* =========================
          SUMMARY
          ========================= */

        $sumRow = $row + 3;

        $sheet->mergeCells('F' . $sumRow . ':H' . $sumRow);

        $sheet->setCellValue(
            'F' . $sumRow,
            'SUMMARY'
        );

        $sheet->getStyle('F' . $sumRow . ':H' . $sumRow)
            ->applyFromArray($headerStyle);

        $sumRow++;

        $sheet->setCellValue(
            'F' . $sumRow,
            'ACCESSORIES'
        );

        $sheet->setCellValue(
            'H' . $sumRow,
            round($accTotal)
        );

        $sumRow++;

        $sheet->setCellValue(
            'F' . $sumRow,
            'BANK'
        );

        $sheet->setCellValue(
            'H' . $sumRow,
            $bankTotal
        );

        $sumRow++;

        $sheet->setCellValue(
            'F' . $sumRow,
            'STOCK'
        );

        $sheet->setCellValue(
            'H' . $sumRow,
            $stockTotal
        );

        $sumRow++;

        $sheet->setCellValue(
            'F' . $sumRow,
            'FINAL AMOUNT'
        );

        $sheet->setCellValue(
            'H' . $sumRow,
            round($finalAmount)
        );

        /* =========================
          SUMMARY STYLE
          ========================= */

        /* =========================
           FINAL AMOUNT COLOR
        ========================= */

        $finalColor = ($finalAmount >= 0) ? 'FF0000' : '008000'; // red if positive, green if negative

        $sheet->getStyle(
            'F' . $sumRow . ':H' . $sumRow
        )->applyFromArray([
                    'font' => [
                        'bold' => true,
                        'color' => [
                            'rgb' => 'FFFFFF'
                        ]
                    ],
                    'fill' => [
                        'fillType' => Fill::FILL_SOLID,
                        'startColor' => [
                            'rgb' => $finalColor
                        ]
                    ],
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => Border::BORDER_THIN
                        ]
                    ]
                ]);

        /* =========================
          ALIGNMENTS
          ========================= */

        $sheet->getStyle('A1:N' . $sumRow)
            ->getAlignment()
            ->setVertical(Alignment::VERTICAL_CENTER);

        $sheet->freezePane('A5');
    }

    $spreadsheet->setActiveSheetIndex(0);

    /* =========================
      DOWNLOAD
      ========================= */

    $filename = 'JOINT_REPORT.xlsx';

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

    header('Content-Disposition: attachment;filename="' . $filename . '"');

    header('Cache-Control: max-age=0');

    $writer = new Xlsx($spreadsheet);

    $writer->save('php://output');

    exit;
}
?>