<?php
require_once("library/TCPDF/tcpdf.php");

require("includes/check_auth_user.php");
require("includes/dbconnect.php");

class MYPDF extends TCPDF
{

    public function Header()
    {

        global $repname, $mfdt, $mtdt;

        $this->SetFont('helvetica', 'B', 14);

        $this->Cell(0, 8, $repname, 0, 1, 'C');

        $this->SetFont('helvetica', '', 10);

        $this->Cell(0, 6, "Period : $mfdt To $mtdt", 0, 1, 'C');

        $this->Ln(2);
    }

    public function Footer()
    {

        $this->SetY(-12);

        $this->SetFont('helvetica', 'I', 8);

        $this->Cell(0, 5, 'Page ' . $this->getAliasNumPage() . ' / ' . $this->getAliasNbPages(), 0, false, 'C');
    }
}

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

    $gcond = "";
    $accond = "";

    if (!empty($_POST["group"])) {

        $grp = trim($_POST["group"]);

        $gcond = "AND grpcode='$grp'";
    }

    if (!empty($_GET["grp"])) {

        $grp = trim($_GET["grp"]);

        $gcond = "AND grpcode='$grp'";
    }

    if (!empty($_POST["acname"])) {

        $accode = trim($_POST["acname"]);

        $accond = "AND accode='$accode'";
    }

    if (!empty($_GET["acc"])) {

        $accode = trim($_GET["acc"]);

        $accond = "AND accode='$accode'";
    }

    if ($_GET["fdt"] != '') {

        $mfrmdate = $mfdt = $_GET["fdt"];
        $mtodate = $mtdt = $_GET["tdt"];

    } else {

        $mfrmdate = trim($_POST["frmdt"]);
        $mtodate = trim($_POST["todt"]);

        $mfdt = date("d-m-Y", strtotime($mfrmdate));
        $mtdt = date("d-m-Y", strtotime($mtodate));
    }

    $repname = "Ledger Report";

    $pdf = new MYPDF('L', PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);

    $pdf->SetCreator(PDF_CREATOR);

    $pdf->SetAuthor('ERP');

    $pdf->SetTitle($repname);

    $pdf->SetMargins(8, 22, 8);

    $pdf->SetHeaderMargin(5);

    $pdf->SetFooterMargin(8);

    $pdf->SetAutoPageBreak(TRUE, 12);

    $pdf->AddPage();

    mysqli_select_db($conn, $zdb);

    mysqli_query($conn, "DROP TEMPORARY TABLE IF EXISTS opdata");

    mysqli_query($conn, "
        CREATE TEMPORARY TABLE opdata
        SELECT accode, SUM(amount) as amt
        FROM `$trandb`
        WHERE date < '$mfrmdate'
        AND retdate='0000-00-00'
        GROUP BY accode
    ");

    mysqli_query($conn, "DROP TEMPORARY TABLE IF EXISTS baldata");

    mysqli_query($conn, "
        CREATE TEMPORARY TABLE baldata
        SELECT SUM(amount) as amt, accode
        FROM `$trandb`
        WHERE date <= '$mtodate'
        AND retdate='0000-00-00'
        GROUP BY accode
    ");

    $acc = mysqli_query($conn, "
        SELECT *
        FROM `$maindb`
        WHERE grpcode > '002'
        AND accode!=''
        $gcond
        $accond
        ORDER BY name
    ");

    $html = "";

    while ($getac = mysqli_fetch_array($acc)) {

        $maccode = $getac["accode"];
        $macname = $getac["name"];

        $mopbal = $getac["openbal"];

        if ($getac["dc"] == "D") {

            $mopbal *= -1;
        }

        $getbal = mysqli_query($conn, "
            SELECT amt
            FROM opdata
            WHERE accode='$maccode'
        ");

        $balrow = mysqli_fetch_array($getbal);

        $openingamt = $balrow["amt"];

        $opening = $mopbal + $openingamt;

        $totbal = $opening;

        $openingdr = 0;
        $openingcr = 0;

        if ($opening < 0) {

            $openingdr = abs($opening);
            $baltype = "DR";

        } else {

            $openingcr = abs($opening);
            $baltype = "CR";
        }

        $getfinal = mysqli_query($conn, "
            SELECT amt
            FROM baldata
            WHERE accode='$maccode'
        ");

        $frow = mysqli_fetch_array($getfinal);

        $finalbal = $mopbal + $frow["amt"];

        $finaltype = ($finalbal < 0) ? "DR" : "CR";

        $html .= '

        <table border=\"1\" cellpadding=\"4\" cellspacing=\"0\" width=\"100%\">
        
            <tr style=\"background-color:#26A69A;color:white;\">
                <td width=\"85%\"><b>' . strtoupper($macname) . '</b></td>
                <td width=\"15%\" align=\"right\"><b>' . number_format(abs($finalbal), 2) . ' ' . $finaltype . '</b></td>
            </tr>

        </table>

        <table border=\"1\" cellpadding=\"4\" cellspacing=\"0\" width=\"100%\">

            <tr style=\"background-color:#d9edf7;font-weight:bold;\">
                <td width=\"10%\">Date</td>
                <td width=\"8%\">Src</td>
                <td width=\"10%\">Voucher</td>
                <td width=\"37%\">Description</td>
                <td width=\"12%\" align=\"right\">Debit</td>
                <td width=\"12%\" align=\"right\">Credit</td>
                <td width=\"11%\" align=\"right\">Balance</td>
            </tr>

            <tr>
                <td>' . $mfdt . '</td>
                <td></td>
                <td></td>
                <td>Opening Balance</td>
                <td align=\"right\">' . number_format($openingdr, 2) . '</td>
                <td align=\"right\">' . number_format($openingcr, 2) . '</td>
                <td align=\"right\">' . number_format(abs($totbal), 2) . ' ' . $baltype . '</td>
            </tr>
        ';

        $dtot = 0;
        $ctot = 0;

        $qry = mysqli_query($conn, "
            SELECT *,
            DATE_FORMAT(date,'%d-%m-%Y') as mydt
            FROM `$trandb`
            WHERE accode='$maccode'
            AND date>='$mfrmdate'
            AND date<='$mtodate'
            AND retdate='0000-00-00'
            ORDER BY date,vouchno
        ");

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

            $mamount = $row["amount"];

            $debit = 0;
            $credit = 0;

            if ($mamount < 0) {

                $debit = abs($mamount);

                $dtot += $debit;

            } else {

                $credit = abs($mamount);

                $ctot += $credit;
            }

            $totbal += $mamount;

            $runningtype = ($totbal < 0) ? "DR" : "CR";

            switch ($row["source"]) {

                case "PU":
                    $head = "Purchase";
                    break;

                case "SL":
                    $head = "Sale";
                    break;

                case "BR":
                    $head = "Bank Receipt";
                    break;

                case "BP":
                    $head = "Bank Payment";
                    break;

                case "CR":
                    $head = "Cash Receipt";
                    break;

                case "CP":
                    $head = "Cash Payment";
                    break;

                case "JV":
                    $head = "Journal Voucher";
                    break;

                case "TR":
                    $head = "Contra";
                    break;

                default:
                    $head = "Other";
            }

            $html .= '

            <tr>
                <td>' . $row["mydt"] . '</td>
                <td>' . $row["source"] . '</td>
                <td>' . $row["vouchno"] . '</td>
                <td>' . $head . '</td>
                <td align=\"right\">' . number_format($debit, 2) . '</td>
                <td align=\"right\">' . number_format($credit, 2) . '</td>
                <td align=\"right\">' . number_format(abs($totbal), 2) . ' ' . $runningtype . '</td>
            </tr>
            ';
        }

        $html .= '

            <tr style=\"font-weight:bold;background-color:#f5f5f5;\">
                <td colspan=\"4\" align=\"right\">TOTAL</td>
                <td align=\"right\">' . number_format($dtot, 2) . '</td>
                <td align=\"right\">' . number_format($ctot, 2) . '</td>
                <td align=\"right\">' . number_format(abs($totbal), 2) . ' ' . $runningtype . '</td>
            </tr>

        </table>

        <br><br>
        ';
    }

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

    $pdf->writeHTML($html, true, false, true, false, '');

    ob_end_clean();

    $pdf->Output('ledger_report.pdf', 'I');

    exit;
}
?>