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

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

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


$fdate = $_POST['fdate'];
$tdate = $_POST['tdate'];

$transporter = trim($_POST['transporter'] ?? '');
$party = trim($_POST['party'] ?? '');


/* =========================================================
   FILTER CONDITIONS
========================================================= */

$where = "";


/* =========================
   TRANSPORTER FILTER
========================= */

if ($transporter != '') {

    $transportArray = explode(',', $transporter);

    $finalTransport = [];

    foreach ($transportArray as $tr) {

        $tr = trim($tr);

        if ($tr != '') {
            $finalTransport[] = "'" . $tr . "'";
        }

    }

    if (count($finalTransport) > 0) {

        $transportIN = implode(",", $finalTransport);

        $where .= "
            AND a.transporter IN ($transportIN)
        ";

    }

} else {

    /* BLANK = ALL grpcode 010 */

    $where .= "
        AND a.grpcode='010'
    ";

}



/* =========================
   SINGLE KARIGAR FILTER
========================= */

if ($party != '') {

    $where .= "
        AND a.accode='$party'
    ";

}

/* =========================================================
   QUERY
========================================================= */
$qry = mysqli_query($conn, "

SELECT 
    a.accode,
    a.name karigar,
    a.transporter,

    t.name transportername,

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

    IFNULL((
        SELECT SUM(amount)
        FROM stockent s
        WHERE s.party = a.accode
        AND s.vouchdt BETWEEN '$fdate' AND '$tdate'
    ),0) stocktotal,


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

    IFNULL((
        SELECT SUM(amount)
        FROM bankent b
        WHERE b.party = a.accode
        AND b.vouchdt BETWEEN '$fdate' AND '$tdate'
    ),0) banktotal,


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

    IFNULL((
        SELECT SUM(rt.amount)

        FROM rawtran rt

        LEFT JOIN rawmain rm
        ON rm.vouchno = rt.vouchno

        WHERE rm.party = a.accode

        AND rt.vouchdt BETWEEN '$fdate' AND '$tdate'

    ),0) acstotal


FROM accmast a

LEFT JOIN transport t
ON t.code = a.transporter

WHERE 1

$where

ORDER BY a.transporter,a.name

") or die(mysqli_error($conn));

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

$grandStock = 0;
$grandBank = 0;
$grandAcc = 0;



/* =========================================================
   HTML VIEW
========================================================= */

if (isset($_POST['view'])) {
    ?>

    <!DOCTYPE html>
    <html>

    <head>

        <meta charset="utf-8">

        <title>Karigar Summary</title>

        <style>
            body {
                font-family: Arial;
                background: #fff;
                padding: 20px;
            }

            table {
                width: 100%;
                border-collapse: collapse;
                font-size: 14px;
            }

            th,
            td {
                border: 1px solid #555;
                padding: 6px;
            }

            thead th {
                background: #ececc4;
                text-align: center;
            }

            .title {
                background: #efc08f !important;
                color: #3f3f8c;
                font-size: 20px;
                font-weight: bold;
            }

            .daterow {
                background: #dbe5f1 !important;
                font-size: 18px;
                font-weight: bold;
            }

            .center {
                text-align: center;
            }

            .right {
                text-align: right;
            }

            .yellow {
                background: yellow;
                font-weight: bold;
            }

            .summary {
                width: 350px;
                margin: 30px auto 0;
            }

            .summary th {
                background: #dbeaf7;
                font-size: 18px;
            }

            .finalamt {
                background: #f6c9cf;
                color: #b00000;
                font-weight: bold;
            }

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

            @media print {

                .printbtn {
                    display: none;
                }

                body {
                    padding: 0;
                }

            }

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

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

    </head>

    <body>

        <div class="printbtn">

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

        </div>



        <table>

            <thead>

                <tr>

                    <th colspan="5" class="title">

                        <?php

                        $showTransport = 'ALL KARIGAR';

                        if ($transporter != '') {

                            $findtr = mysqli_query($conn, "SELECT name FROM transport WHERE code='$transporter'");

                            $ftr = mysqli_fetch_assoc($findtr);

                            $showTransport = $ftr['name'] . " KARIGAR";

                        }

                        ?>

                        <?= strtoupper($showTransport) ?>

                    </th>

                    <th class="daterow">

                        <?= date("d-m-Y", strtotime($tdate)) ?>

                    </th>

                </tr>


                <tr>

                    <th width="5%">
                        Sr. No.
                    </th>

                    <th width="10%">
                        TRANSPORTER
                    </th>

                    <th width="35%">
                        KARIGAR NAME
                    </th>

                    <th width="18%">
                        TOTAL STOCK AMOUNT
                    </th>

                    <th width="18%">
                        TOTAL BANK AMOUNT
                    </th>

                    <th width="14%">
                        TOTAL ACCESSORIES
                    </th>

                </tr>

            </thead>



            <tbody>

                <?php

                $i = 1;

                while ($row = mysqli_fetch_assoc($qry)) {

                    $grandStock += $row['stocktotal'];
                    $grandBank += $row['banktotal'];
                    $grandAcc += $row['acstotal'];

                    ?>

                    <tr>

                        <td class="center">
                            <?= $i++ ?>
                        </td>

                        <td class="center">
                            <?= $row['transportername'] ?>
                        </td>

                        <td>
                            <?= strtoupper($row['karigar']) ?>
                        </td>

                        <td class="right">
                            <?= $row['stocktotal'] > 0 ? number_format($row['stocktotal'],2) : '-' ?>
                        </td>

                        <td class="right">
                            <?= $row['banktotal'] > 0 ? number_format($row['banktotal'],2) : '-' ?>
                        </td>

                        <td class="right">
                            <?= $row['acstotal'] > 0 ? number_format($row['acstotal'],2) : '-' ?>
                        </td>

                    </tr>

                <?php } ?>



                <tr class="yellow">

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

                    <td class="right">
                        <?= number_format($grandStock,2) ?>
                    </td>

                    <td class="right">
                        <?= number_format($grandBank,2) ?>
                    </td>

                    <td class="right">
                        <?= $grandAcc > 0 ? number_format($grandAcc,2) : '-' ?>
                    </td>

                </tr>

            </tbody>

        </table>



        <?php

        $finalAmount = ($grandAcc + $grandBank) - $grandStock;

        ?>



        <table class="summary">

            <tr>

                <th colspan="2">
                    SUMMERY
                </th>

            </tr>

            <tr>

                <td>
                    STOCK AMOUNT TOTAL
                </td>

                <td class="right">
                    <?= number_format($grandStock,2) ?>
                </td>

            </tr>

            <tr>

                <td>
                    BANK AMOUNT TOTAL
                </td>

                <td class="right">
                    <?= number_format($grandBank,2) ?>
                </td>

            </tr>

            <tr>

                <td>
                    ACCESSORIES TOTAL
                </td>

                <td class="right">
                    <?= $grandAcc > 0 ? number_format($grandAcc,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>

    </body>

    </html>

    <?php
}

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

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

    $spreadsheet = new Spreadsheet();

    $sheet = $spreadsheet->getActiveSheet();

    $sheet->setTitle('Karigar Summary');

    /* =========================================================
       COLUMN WIDTH
    ========================================================= */

    $sheet->getColumnDimension('A')->setWidth(10);
    $sheet->getColumnDimension('B')->setWidth(22);
    $sheet->getColumnDimension('C')->setWidth(40);
    $sheet->getColumnDimension('D')->setWidth(22);
    $sheet->getColumnDimension('E')->setWidth(22);
    $sheet->getColumnDimension('F')->setWidth(22);

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

    $showTransport = 'ALL KARIGAR';

    if ($transporter != '') {

        $findtr = mysqli_query($conn, "SELECT name FROM transport WHERE code='$transporter'");

        $ftr = mysqli_fetch_assoc($findtr);

        $showTransport = $ftr['name'] . " KARIGAR";
    }

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

    $sheet->setCellValue('A1', strtoupper($showTransport));

    $sheet->setCellValue('F1', date("d-m-Y", strtotime($tdate)));

    /* =========================================================
       HEADER ROW
    ========================================================= */

    $sheet->fromArray([
        [
            'Sr. No.',
            'TRANSPORTER',
            'KARIGAR NAME',
            'TOTAL STOCK AMOUNT',
            'TOTAL BANK AMOUNT',
            'TOTAL ACCESSORIES'
        ]
    ], NULL, 'A2');

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

    $sheet->getStyle('A1:F1')->applyFromArray([

        'font' => [
            'bold' => true,
            'size' => 14,
            'color' => [
                'rgb' => '3f3f8c'
            ]
        ],

        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
            'vertical' => Alignment::VERTICAL_CENTER
        ],

        'borders' => [
            'allBorders' => [
                'borderStyle' => Border::BORDER_THIN
            ]
        ]

    ]);

    $sheet->getStyle('A1:E1')
        ->getFill()
        ->setFillType(Fill::FILL_SOLID)
        ->getStartColor()
        ->setRGB('efc08f');

    $sheet->getStyle('F1')
        ->getFill()
        ->setFillType(Fill::FILL_SOLID)
        ->getStartColor()
        ->setRGB('dbe5f1');

    /* =========================================================
       HEADER STYLE
    ========================================================= */

    $sheet->getStyle('A2:F2')->applyFromArray([

        'font' => [
            'bold' => true,
            'size' => 11
        ],

        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
            'vertical' => Alignment::VERTICAL_CENTER,
            'wrapText' => true
        ],

        'fill' => [
            'fillType' => Fill::FILL_SOLID,
            'startColor' => [
                'rgb' => 'ececc4'
            ]
        ],

        'borders' => [
            'allBorders' => [
                'borderStyle' => Border::BORDER_THIN
            ]
        ]

    ]);

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

    mysqli_data_seek($qry, 0);

    $rowNo = 3;
    $i = 1;

    while ($row = mysqli_fetch_assoc($qry)) {

        $grandStock += $row['stocktotal'];
        $grandBank += $row['banktotal'];
        $grandAcc += $row['acstotal'];

        $sheet->setCellValue('A' . $rowNo, $i++);
        $sheet->setCellValue('B' . $rowNo, $row['transportername']);
        $sheet->setCellValue('C' . $rowNo, strtoupper($row['karigar']));
        $sheet->setCellValue('D' . $rowNo, $row['stocktotal']);
        $sheet->setCellValue('E' . $rowNo, $row['banktotal']);
        $sheet->setCellValue('F' . $rowNo, $row['acstotal']);

        $sheet->getStyle('A' . $rowNo . ':F' . $rowNo)
            ->applyFromArray([

                'alignment' => [
                    'vertical' => Alignment::VERTICAL_CENTER
                ],

                'borders' => [
                    'allBorders' => [
                        'borderStyle' => Border::BORDER_THIN
                    ]
                ]

            ]);

        $sheet->getStyle('A' . $rowNo . ':B' . $rowNo)
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER);

        $sheet->getStyle('D' . $rowNo . ':F' . $rowNo)
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_RIGHT);

        $rowNo++;
    }

    /* =========================================================
       TOTAL ROW
    ========================================================= */

    $sheet->mergeCells('A' . $rowNo . ':C' . $rowNo);

    $sheet->setCellValue('A' . $rowNo, 'TOTAL :');

    $sheet->setCellValue('D' . $rowNo, $grandStock);
    $sheet->setCellValue('E' . $rowNo, $grandBank);
    $sheet->setCellValue('F' . $rowNo, $grandAcc);

    $sheet->getStyle('A' . $rowNo . ':F' . $rowNo)
        ->applyFromArray([

            'font' => [
                'bold' => true
            ],

            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_RIGHT
            ],

            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => [
                    'rgb' => 'ffff00'
                ]
            ],

            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN
                ]
            ]

        ]);

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

    $finalAmount = $grandStock - $grandBank - $grandAcc;

    $sumRow = $rowNo + 3;

    $sheet->mergeCells('C' . $sumRow . ':D' . $sumRow);

    $sheet->setCellValue('C' . $sumRow, 'SUMMERY');

    $sheet->getStyle('C' . $sumRow . ':D' . $sumRow)
        ->applyFromArray([

            'font' => [
                'bold' => true,
                'size' => 12
            ],

            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER
            ],

            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => [
                    'rgb' => 'dbeaf7'
                ]
            ],

            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN
                ]
            ]

        ]);

    $sumRow++;

    $summaryData = [

        ['STOCK AMOUNT TOTAL', $grandStock],
        ['BANK AMOUNT TOTAL', $grandBank],
        ['ACCESSORIES TOTAL', $grandAcc],
        ['FINAL AMOUNT', $finalAmount]

    ];

    foreach ($summaryData as $sd) {

        $sheet->setCellValue('C' . $sumRow, $sd[0]);
        $sheet->setCellValue('D' . $sumRow, $sd[1]);

        $sheet->getStyle('C' . $sumRow . ':D' . $sumRow)
            ->applyFromArray([

                'borders' => [
                    'allBorders' => [
                        'borderStyle' => Border::BORDER_THIN
                    ]
                ]

            ]);

        $sheet->getStyle('D' . $sumRow)
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_RIGHT);

        $sumRow++;
    }

    /* FINAL AMOUNT STYLE */

    $sheet->getStyle('C' . ($sumRow - 1) . ':D' . ($sumRow - 1))
        ->applyFromArray([

            'font' => [
                'bold' => true,
                'color' => [
                    'rgb' => 'b00000'
                ]
            ],

            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => [
                    'rgb' => 'f6c9cf'
                ]
            ]

        ]);

    /* =========================================================
       NUMBER FORMAT
    ========================================================= */

    $sheet->getStyle('D3:F' . $sumRow)
        ->getNumberFormat()
        ->setFormatCode('#,##0');

    /* =========================================================
       FONT
    ========================================================= */

    $sheet->getStyle('A1:F' . $sumRow)
        ->getFont()
        ->setName('Arial');

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

    $filename = "KARIGAR_SUMMARY.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;
}


/* =========================================================
   PDF USING FPDF
========================================================= */

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

    mysqli_data_seek($qry, 0);

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


    class PDF extends FPDF
    {
        function Header()
        {
            global $lparty;

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


    $pdf = new PDF('P', 'mm', 'A4');


    $pdf->AddPage();

    $pdf->SetAutoPageBreak(true, 15);

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

    $showTransport = 'ALL KARIGAR';

    if ($transporter != '') {

        $findtr = mysqli_query($conn, "SELECT name FROM transport WHERE code='$transporter'");

        $ftr = mysqli_fetch_assoc($findtr);

        $showTransport = $ftr['name'] . " KARIGAR";
    }

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

    $pdf->SetFillColor(239, 192, 143);

    $pdf->SetTextColor(63, 63, 140);

    $pdf->Cell(135, 12, strtoupper($showTransport), 1, 0, 'C', true);

    $pdf->SetFillColor(219, 229, 241);

    $pdf->Cell(60, 12, date("d-m-Y", strtotime($tdate)), 1, 1, 'C', true);

    $pdf->Ln(1);

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

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

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

    $pdf->SetFillColor(236, 236, 196);

    $pdf->Cell(10, 10, 'SR.', 1, 0, 'C', true);

    $pdf->Cell(23, 10, 'TRANSPORTER', 1, 0, 'C', true);

    $pdf->Cell(72, 10, 'KARIGAR NAME', 1, 0, 'C', true);

    $pdf->Cell(30, 10, 'STOCK AMOUNT', 1, 0, 'C', true);

    $pdf->Cell(30, 10, 'BANK AMOUNT', 1, 0, 'C', true);

    $pdf->Cell(30, 10, 'ACCESSORIES', 1, 1, 'C', true);

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

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

    $i = 1;

    while ($row = mysqli_fetch_assoc($qry)) {

        $grandStock += $row['stocktotal'];
        $grandBank += $row['banktotal'];
        $grandAcc += $row['acstotal'];

        $pdf->Cell(10, 8, $i++, 1, 0, 'C');

        $pdf->Cell(23, 8, $row['transportername'], 1, 0, 'C');

        $pdf->Cell(72, 8, strtoupper($row['karigar']), 1, 0, 'L');

        $pdf->Cell(30, 8, $row['stocktotal'] > 0 ? number_format($row['stocktotal']) : '-', 1, 0, 'R');

        $pdf->Cell(30, 8, $row['banktotal'] > 0 ? number_format($row['banktotal']) : '-', 1, 0, 'R');

        $pdf->Cell(30, 8, $row['acstotal'] > 0 ? number_format($row['acstotal']) : '-', 1, 1, 'R');
    }

    /* =========================================================
       TOTAL ROW
    ========================================================= */

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

    $pdf->SetFillColor(255, 255, 0);

    $pdf->Cell(105, 9, 'TOTAL :', 1, 0, 'R', true);

    $pdf->Cell(30, 9, number_format($grandStock), 1, 0, 'R', true);

    $pdf->Cell(30, 9, number_format($grandBank), 1, 0, 'R', true);

    $pdf->Cell(
        30,
        9,
        $grandAcc > 0 ? number_format($grandAcc) : '-',
        1,
        1,
        'R',
        true
    );

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

    $finalAmount = $grandStock - $grandBank - $grandAcc;

    $pdf->Ln(12);

    $startX = 70;

    $pdf->SetX($startX);

    $pdf->SetFillColor(219, 234, 247);

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

    $pdf->Cell(80, 10, 'SUMMERY', 1, 1, 'C', true);

    $summary = [

        ['STOCK AMOUNT TOTAL', number_format($grandStock)],
        ['BANK AMOUNT TOTAL', number_format($grandBank)],
        ['ACCESSORIES TOTAL', $grandAcc > 0 ? number_format($grandAcc) : '-'],
        ['FINAL AMOUNT', number_format($finalAmount)]

    ];

    foreach ($summary as $k => $s) {

        $pdf->SetX($startX);

        if ($k == 3) {

            $pdf->SetFillColor(246, 201, 207);

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

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

        } else {

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

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

            $pdf->SetFont('Arial', '', 10);
        }

        $pdf->Cell(50, 9, $s[0], 1, 0, 'L', true);

        $pdf->Cell(30, 9, $s[1], 1, 1, 'R', true);
    }

    ob_clean();
    /* =========================================================
       OUTPUT
       ========================================================= */
    $pdf->Output(
        'I',
        'KARIGAR_SUMMARY.pdf'
    );

    exit;
}

?>