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

$zdb = $_SESSION["zdbyear_" . $dbname];
$admin = $_SESSION["admin_" . $dbname];
$zroot = $_SESSION["zdbroot"];

$formname = "LEDGER";
$grid = "ledger.php";
$file = "ledger.php";
$maindb = "accmast";
mysqli_select_db($conn, $zdb);

$zyear = right($zdb, 4);
$zsdate = getzdates($zyear, 'S');
$zedate = getzdates($zyear, 'E');

$zsyear = left($zsdate, 4);
$zeyear = left($zedate, 4);

mysqli_select_db($conn, $zdb);

$url = "http://" . $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'];
$url = str_replace("rpt=1", "rpt=2", "$url");

$trandb = "actran";

setlocale(LC_MONETARY, "en_IN");

if (isset($_POST["view"]) || ($_GET["view"])) {
    $gcond = "";
    $accond = "";
    $stcond = "";
    $ctcond = "";

    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($_POST["cmbstation"])) {
        $station = trim($_POST["cmbstation"]);
        $stcond = " AND `station`= '$station' ";
    }

    if (!empty($_POST["city"])) {
        $city = trim($_POST["city"]);
        $ctcond = " AND `city`='$city'";
    }
    if (($_GET["fdt"]) != '') {
        $mfrmdate = $mfdt = $_GET["fdt"];
        $mtodate = $mtdt = $_GET["tdt"];
        $dtype = $_GET["rpt"];
    } else {
        $mfrmdate = trim($_POST["frmdt"]);
        $mtodate = trim($_POST["todt"]);
        if (empty($_POST["frmdt"])) {
            browse("From Date Cannot Be Empty");
            header("refresh:1; saleitemgrp.php");
        }
        if (empty($_POST["todt"])) {
            browse("To Date Cannot Be Empty");
            header("refresh:1; saleitemgrp.php");
        }
        $mfdt = date("d-m-Y", strtotime($mfrmdate));
        $mtdt = date("d-m-Y", strtotime($mtodate));
    }

    if ($mfrmdate > $mtodate) {
        browse("To Date Cannot Be Less Than From Date");
        header("refresh: 1; ledger.php");
        exit();
    }

    if (!empty($_POST["prinzero"])) {
        $prnzero = $_POST["prinzero"];
    }

    if (!empty($_GET["prz"])) {
        $prnzero = $_GET["prz"];
    }

    if (!empty($_POST["prinnarr"])) {
        $prnnarr = $_POST["prinnarr"];
    }

    if (!empty($_GET["prn"])) {
        $prnnarr = $_GET["prn"];
    }


    if ($_POST["dtype"] == 1 || $_GET["rpt"] == 1) {
        $repname = "Ledger - Summary";
        $dtype = 1;
    } else {
        $repname = "Ledger - Detail";
        $dtype = 2;
    }

    if (isset($_POST["view"])) {
        if ($_POST["dtype"] == 0) {
            $dtype = 0;
        }
    } else {
        if ($_GET["acc"]) {
            $accode = trim($_GET["acc"]);
            $accond = "AND `accode`='$accode'";
        }
    }

    $acc = mysqli_query($conn, "SELECT * FROM `$maindb` WHERE accode!='' $gcond $accond $ctcond $stcond  order by name") or die(mysqli_error($conn));

    if (mysqli_num_rows($acc) <= 0) {
        browse("Record Not Found");
        echo "<script>window.close();</script>";
        return false;
    }
//print table($acc);
    include("includes/linkshead.php");
    ?>
<!--    <style>
        @media print {
          .sk1{
              background-color: #26A69A !important;
            -webkit-print-color-adjust: exact;
            color : black !important;

          }    

        }

        .bakc{
            background-color: #2FBEF3 !important;
            -webkit-print-color-adjust: exact;
        }
    </style>-->
    </head>
    <body>   
        <div class="container">                                                

            <?php include("includes/head_rep.php"); ?>
            <div class="row">
                <table class="table table-bordered table-condensed " width="100%">
                    <thead>
                        <tr class="mySearchTr">
                            <?php if ($dtype < 2) { ?>
                                <th align="left" width="40%">Account Name</th>
                                <th align="right" width="10%" style="text-align:right;">Opening</th>
                                <th align="right" width="10%" style="text-align:right;">Debit</th>
                                <th align="right" width="10%" style="text-align:right;">Credit</th>
                                <th align="right" width="10%" style="text-align:right;">Balance</th>
                                <?php
                            }
                            ?>
                        </tr>
                    </thead>
                    <tbody>
                        <?php
                        mysqli_select_db($conn, $zdb);
                        $totbal = $totobal = $totdeb = $totcrd = 0;

                        mysqli_query($conn, "drop temporary table if exists opdata") or die(mysqli_error($conn));
                        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") or die(mysqli_error($conn));

                        mysqli_query($conn, "drop temporary table if exists drdata") or die(mysqli_error($conn));
                        mysqli_query($conn, "create temporary table drdata SELECT sum(amount) as amtdr, accode FROM `$trandb` WHERE 
                                                amount < 0 and date >= '$mfrmdate' and date <= '$mtodate' and retdate = '0000-00-00' group by accode") or die(mysqli_error($conn));

                        mysqli_query($conn, "drop temporary table if exists crdata") or die(mysqli_error($conn));
                        mysqli_query($conn, "create temporary table crdata SELECT sum(amount) as amtcr, accode FROM `$trandb` WHERE 
                                                amount > 0 and date >= '$mfrmdate' and date <= '$mtodate' and retdate = '0000-00-00' group by accode") or die(mysqli_error($conn));

                        if ($dtype == 2) {
                            mysqli_query($conn, "drop temporary table if exists baldata") or die(mysqli_error($conn));
                            mysqli_query($conn, "create temporary table baldata SELECT SUM(amount) as amt, accode FROM `$trandb` WHERE 
                                                      date <= '$mtodate' and `retdate` = '0000-00-00' ") or die(mysqli_error($conn));
                        }

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

                            if ($dtype < 2) {
                                $maccode = $getac["accode"];
                                $acname = $getac["name"];

                                $mopbal = $getac["openbal"];
                                $mopdc = $getac["dc"];
                                if ($mopdc == "D") {
                                    $mopbal = $mopbal * - 1;
                                }

                                ############PARTY NAME####################

                                $getop = mysqli_query($conn, "SELECT amt FROM `opdata` WHERE  `accode`='$maccode' ") or die(mysqli_error($conn));
                                $fetop = mysqli_fetch_array($getop);
                                $opamt = $fetop["amt"];
                                $obal = $mopbal + $opamt;
                                if ($obal < 0) {
                                    $odc = "DR";
                                } else {
                                    $odc = "CR";
                                }

//                                $gettrdr = mysqli_query($conn, "SELECT SUM(amount) as amtdr FROM `mondata` WHERE  amount < 0 ") or die(mysqli_error($conn));
                                $gettrdr = mysqli_query($conn, "SELECT amtdr FROM `drdata` WHERE  `accode`='$maccode' ") or die(mysqli_error($conn));
                                $fettrdr = mysqli_fetch_array($gettrdr);
                                $tramtdr = $fettrdr["amtdr"];
                                $tramtdr = abs($tramtdr);

//                                $gettrcr = mysqli_query($conn, "SELECT SUM(amount) as amtcr FROM `mondata` WHERE amount > 0 ") or die(mysqli_error($conn));
                                $gettrcr = mysqli_query($conn, "SELECT amtcr FROM `crdata` WHERE `accode`='$maccode' ") or die(mysqli_error($conn));
                                $fettrcr = mysqli_fetch_array($gettrcr);
                                $tramtcr = $fettrcr["amtcr"];
                                $tramtcr = abs($tramtcr);

                                $mbal = $obal - $tramtdr + $tramtcr;

                                if ($mbal < 0) {
                                    $mdc = "DR";
                                } else {
                                    $mdc = "CR";
                                }

                                $show = 1;
                                if ($prnzero == 1) {
                                    $show = 1;
                                } else {
                                    if ($mbal != 0) {
                                        $show = 1;
                                    } else {
                                        $show = 0;
                                    }
                                }

                                $obal1 = abs($obal);
                                $mbal1 = abs($mbal);
                                if ($show == 1) {
                                    Echo "<tr  style='background-color:white' class='clickable-row2'>";
                                    if ($dtype == 0) {
                                        $colr = ' color : blue';
                                    } else {
                                        $colr = ' color : black';
                                    }
                                    echo "<td style='text-align:left;  $colr'\>$acname</td>";
                                    echo "<td style='text-align:right; $colr'>" . number_format($obal1) . " " . $odc . "</td>";
                                    echo "<td style='text-align:right; $colr'>" . number_format($tramtdr) . "</td>";
                                    echo "<td style='text-align:right; $colr'>" . number_format($tramtcr) . "</td>";

                                    echo "<td style='text-align:right; background-color:#98F9F0'>" . number_format($mbal1) . " " . $mdc . "</td>";
                                    $totobal = $totobal + $obal;

                                    $totdeb = $totdeb + $tramtdr;
                                    $totcrd = $totcrd + $tramtcr;

                                    $totbal = $totbal + $mbal;
                                    echo "</tr>";
                                }

                                if ($totbal < 0) {
                                    $mdctot = "DR";
                                } else {
                                    $mdctot = "CR";
                                }

                                if ($dtype == 0) {
                                    mysqli_query($conn, "drop temporary table if exists mondata") or die(mysqli_error($conn));
                                    mysqli_query($conn, "create temporary table mondata SELECT *, month(date) as mon  FROM `$trandb` WHERE 
                                                                `accode`='$maccode' and date >= '$mfrmdate' and date <= '$mtodate' and retdate = '0000-00-00'") or die(mysqli_error($conn));

                                    $getmon = mysqli_query($conn, "SELECT SUM(amount) as monamt, date, mon  FROM `mondata` group by mon order by date") or die(mysqli_error($conn));
                                    if (mysqli_num_rows($getmon) > 0) {
                                        echo "<tr>";
                                        echo "<th style = 'background-color : #999999; width=50% ; align=left'>Month</th>";
                                        echo "<th style = 'background-color : #999999; width=10% ; text-align:right'>Opening</th>";
                                        echo "<th style = 'background-color : #999999; width=10% ; text-align:right'>Debit</th>";
                                        echo "<th style = 'background-color : #999999; width=10% ; text-align:right'>Credit</th>";
                                        echo "<th style = 'background-color : #999999; width=10% ; text-align:right'>Balance</th>";
                                        echo "</tr>";
                                        while ($fetmon = mysqli_fetch_array($getmon)) {
                                            $mon = $fetmon['mon'];
                                            $monamt = $fetmon['monamt'];

                                            if ($mon < 10) {
                                                $mond = '0' . $mon;
                                            } else {
                                                $mond = $mon;
                                            }

                                            $monopn = 0;

                                            if ($mon < 4) {
                                                $date1 = $zeyear . '-' . $mond . '-01';
                                            } else {
                                                $date1 = $zsyear . '-' . $mond . '-01';
                                            }

                                            $getmn = mysqli_query($conn, "SELECT descrip  FROM $zroot.`months` WHERE `code`='$mon' ") or die(mysqli_error($conn));
                                            $fetmn = mysqli_fetch_array($getmn);
                                            $monname = $fetmn['descrip'];


                                            $getopn = mysqli_query($conn, "SELECT SUM(amount) as oamt FROM `mondata` WHERE date < '$date1' ") or die(mysqli_error($conn));
                                            $fetopn = mysqli_fetch_array($getopn);
                                            $opnamt = $fetopn["oamt"];
                                            $monopn = $mopbal + $opnamt;

                                            if ($monopn < 0) {
                                                $mdco = "DR";
                                            } else {
                                                $mdco = "CR";
                                            }

//                                            browse($opnamt);
//                                            browse($mopbal);
//                                            browse($monopn);
//

                                            $getdeb = mysqli_query($conn, "SELECT SUM(amount) as mondeb FROM `mondata` WHERE amount < 0 and 
                                                                   month(date) = '$mon' ") or die(mysqli_error($conn));
                                            $fetdeb = mysqli_fetch_array($getdeb);
                                            $mondeb = $fetdeb['mondeb'];

                                            $getcrd = mysqli_query($conn, "SELECT SUM(amount) as moncrd FROM `mondata` WHERE amount > 0 and 
                                                                   month(date) = '$mon' ") or die(mysqli_error($conn));
                                            $fetcrd = mysqli_fetch_array($getcrd);
                                            $moncrd = $fetcrd['moncrd'];

                                            $monbal = $monopn + $mondeb + $moncrd;

                                            if ($monbal < 0) {
                                                $mdcmon = "DR";
                                            } else {
                                                $mdcmon = "CR";
                                            }

                                            $monopn1 = abs($monopn);
                                            $mondeb1 = abs($mondeb);
                                            $moncrd1 = abs($moncrd);
                                            $monbal1 = abs($monbal);

                                            echo "<tr>";
                                            echo "<td align='left'>$monname</td>";
                                            echo "<td width='10%' align='right' style='text-align:right;'>" . number_format($monopn1) . " " . $mdco . "</td>";
                                            echo "<td width='10%' align='right' style='text-align:right;'>" . number_format($mondeb1) . "</td>";
                                            echo "<td width='10%' align='right' style='text-align:right;'>" . number_format($moncrd1) . "</td>";
                                            echo "<td width='10%' align='right' style='text-align:right;'>" . number_format($monbal1) . " " . $mdcmon . "</td>";
                                            echo "</tr>";
                                        }
                                        echo "<tr>";
                                        echo "<td>&nbsp;
                                            </td>";
                                        echo "<td>&nbsp;
                                            </td>";
                                        echo "</tr>";
                                    }
                                }
                            } else {
                                $totbal = 0;
                                $dtot = 0;
                                $ctot = 0;
                                $maccode = $getac["accode"];
                                $macname = $getac["name"];
                                $mopbal = $getac["openbal"];
                                $mopdc = $getac["dc"];
                                if ($mopdc == "D") {
                                    $mopbal = $mopbal * - 1;
                                }
                                
                                $ctran1 = 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") or die(mysqli_error($conn));

                                $curl = 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") or die(mysqli_error($conn));

                                while ($murl = mysqli_fetch_object($curl)) {
                                    switch ($murl->source) {
                                        case "PU";
                                            $head = "Purchase";
                                            $url = "purchase.php?mode=edit&vouchno = $murl->vouchno&method = ledger";
                                            break;
                                        case "PUPK";
                                            $head = "Purchase Packing";
                                            $url = "purchase_mi.php?mode=edit&type=P&vouchno = $murl->vouchno&method = ledger";
                                            break;
                                        case "PURW";
                                            $head = "Purchase Raw Material";
                                            $url = "purchase_mi.php?mode=edit&type=P&vouchno = $murl->vouchno&method = ledger";
                                            break;
                                        case "PR";
                                            $head = "Purchase Return";
                                            $url = "debnote.php??mode=edit&vouchno = $murl->vouchno&method = ledger";
                                            break;
                                        case "SL";
                                            $head = "Sale";
                                            $url = "sale_ti.php?mode=edit&vouchno = $murl->vouchno&method = ledger";
                                            break;
                                        case "SLDE";
                                            $head = "Sale Deemed";
                                            $url = "sale_de.php?mode=edit&vouchno = $murl->vouchno&method = ledger";
                                            break;
                                        case "SLOT";
                                            $head = "Sale Other";
                                            $url = "saleoth.php?mode=edit&vouchno = $murl->vouchno&method = ledger";
                                            break;
                                        case "SLEX";
                                            $head = "Sale Export";
                                            $url = "sale_ex.php?mode=edit&vouchno = $murl->vouchno&method = ledger";
                                            break;
                                        case "SLON";
                                            $head = "Sale Online";
                                            $url = "sale_onl.php?mode=edit&vouchno = $murl->vouchno&method = ledger";
                                            break;
                                        case "SR";
                                            $head = "Sale Return";
                                            $url = "crednote.php?mode=edit&vouchno = $murl->vouchno&method = ledger";
                                            break;
                                        case "BR";
                                            $url = "bankrecp.php?mode=edit&vouchno = $murl->vouchno&method = ledger";
                                            break;
                                        case "BP";
                                            $head = "Bank Payment";
                                            $url = "bankpay.php?mode=edit&$murl->vouchno&method = ledger";
                                            break;
                                        case "CR";
                                            $head = "Cash Receipt";
                                            $url = "cashrecp.php?mode=edit&$murl->vouchno&method = ledger";
                                            break;
                                        case "CP";
                                            $head = "Cash Payment";
                                            $url = "cashpay.php?mode=edit&$murl->vouchno&method = ledger";
                                            break;
                                        case "TR";
                                            $head = "Contra / Transfer";
                                            $url = "contra.php?mode=edit&vouchno = $murl->vouchno&method = ledger";
                                            break;
                                        case "JV";
                                            $head = "Journal Voucher";
                                            $url = "journal.php?mode=edit&$murl->vouchno&method = ledger";
                                            break;
                                        case "DG";
                                            $head = "Discount Given";
                                            break;
                                        case "DR";
                                            $head = "Discount Received";
                                            break;
                                        default :
                                            $head = "Other";
                                    }
                                }
//                                $rtran1 = mysqli_query($conn, "SELECT *, DATE_FORMAT(`retdate`, '%d-%m-%Y') as mydt FROM `actran` WHERE
//                                                           retdate >= '$mfrmdate' AND retdate <= '$mtodate' AND
//                                                           accode = '$maccode' ORDER BY date, vouchno") or die(mysqli_error($conn));

                                $getbal = mysqli_query($conn, "SELECT amt FROM `opdata` WHERE accode='$maccode' ") or die(mysqli_error($conn));
                                $getamt = mysqli_fetch_array($getbal);
                                $amt = $getamt["amt"];
                                $mobal = $mopbal + $amt;

                                $mdebit = 0;
                                $mcredit = 0;
                                if ($mobal < 0) {
                                    $mdc = "DR";
                                    $mdebit = abs($mobal);
//                                    $dtot = $dtot + $mdebit;
//                                    $dtot = $dtot + $mdebit;
                                } else {
                                    $mdc = "CR";
                                    $mcredit = abs($mobal);
//                                    $ctot = $ctot + $mcredit;
                                }

                                $vdate1 = strtotime($mfrmdate);
                                $vdate = date('d-m-Y', $vdate1);
                                $mvouch = "";
                                $mdescrip = "Opening Balance";
                                $totbal = $totbal + $mobal;
                                if ($totbal < 0) {
                                    $mdctot = "DR";
                                } else {
                                    $mdctot = "CR";
                                }


//                                $getbal = mysqli_query($conn, "SELECT SUM(amount) as amt FROM `$trandb` WHERE accode='$maccode' AND 
//                                                      date <= '$mtodate' and `retdate` = '0000-00-00' ") or die(mysqli_error($conn));

                                $getbal = mysqli_query($conn, "SELECT amt FROM `baldata` WHERE accode='$maccode' ") or die(mysqli_error($conn));
                                $getamt = mysqli_fetch_array($getbal);
                                $amt = $getamt["amt"];
                                $mbal = $mopbal + $amt;
                                if ($mbal < 0) {
                                    $mdc = "DR";
                                } else {
                                    $mdc = "CR";
                                }
                                echo "<tr class='sk1' style='background-color:#26A69A;'>";
                                echo "<th  style=\"text-align:left;background-color:#26A69A !important;-webkit-print-color-adjust: exact;\" scope=\"col\" width=\"500\" colspan='7'><b>" . strtoupper($macname) . "</b></th>";
                                echo "<th style=\"text-align:right;background-color:#26A69A !important;-webkit-print-color-adjust: exact;\" scope=\"col\" width=\"500\" colspan='1'><b>" . abs($mbal) . $mdc . "</b></th>";
                                echo "</tr>";
                                ?>
                                <tr >
                                    <th style="color:white !important; background-color: #2FBEF3 !important;-webkit-print-color-adjust: exact;" align="left">Date</th>                                   
                                    <th style="color:white !important; background-color: #2FBEF3 !important;-webkit-print-color-adjust: exact;" align="left">Source</th>                                   
                                    <th style="color:white !important; background-color: #2FBEF3 !important;-webkit-print-color-adjust: exact;" align="left">Voucher</th>                                   
                                    <th style="color:white !important; background-color: #2FBEF3 !important;-webkit-print-color-adjust: exact;" align="left" colspan="2">Description</th>                                   
                                    <th style="color:white !important; background-color: #2FBEF3 !important;-webkit-print-color-adjust: exact;" align="right" style="text-align:right;">Debit</th>                                   
                                    <th style="color:white !important; background-color: #2FBEF3 !important;-webkit-print-color-adjust: exact;" align="right" style="text-align:right;">Credit</th>                                   
                                    <th style="color:white !important; background-color: #2FBEF3 !important;-webkit-print-color-adjust: exact;" align="right" style="text-align:right;">Balance</th>                                   
                                </tr>
                                <?php 
                                $totbal1 = abs($totbal);
                                echo "<tr class='' href='$url' style='background-color:white' target='_blank' >";
                                echo "<td style=\"text-align:left;\" scope=\"col\"  width=\"15%\">$vdate</td>";
                                echo "<td style=\"text-align:left;\" scope=\"col\"  width=\"4%\"></td>";
                                echo "<td style=\"text-align:left;\" scope=\"col\"  width=\"8%\">$mvouch</td>";
                                echo "<td colspan='2' style=\"text-align:left;\" scope=\"col\"  width=\"35%\">$mdescrip</td>";
                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"10%\">" . number_format($mdebit, 2) . "</td>";
                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"10%\">" . number_format($mcredit, 2) . "</td>";
                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"14%\">" . number_format($totbal1, 2) . " " . $mdctot . "</td>";
                                echo "</tr>";

                                if ($mcredit > 0) {
                                    $opening = $mcredit;
                                } else {
                                    $opening = $mdebit;
                                }
//                                $totbal = 0;
                                while ($row = mysqli_fetch_array($ctran1)) {
                                    $vdate = $row["mydt"];
                                    $msource = $row["source"];
                                    $mvouch = $row["vouchno"];
                                    $mamount = $row["amount"];
                                    $mgrpcode = $getac["grpcode"];
                                    $mdiscprov = $getac["discount"];
                                    $totdiscprov = 0;
                                    $mdebit = 0;
                                    $mcredit = 0;
                                    if ($mamount < 0) {
                                        $mdc = "DR";
                                        $mdebit = abs($mamount);
                                        $dtot = $dtot + $mdebit;
                                    } else {
                                        $mdc = "CR";
                                        $mcredit = abs($mamount);
                                        $ctot = $ctot + $mcredit;
                                    }
                                    $totbal = $totbal + $mamount;
                                    if ($totbal < 0) {
                                        $mdctot = "DR";
                                    } else {
                                        $mdctot = "CR";
                                    }
                                    $mdescrip = "";
                                    $mchqdtl = "";
                                    $mnarr = "";
                                    switch ($msource) {
                                        case "ETA";
                                            $head = "ETA";
                                            $url = "shipperent.php?vouchno=$mvouch&method=ledger";
//                                            if ($prnnarr == 1) {

                                                $nqry1 = mysqli_query($conn, "SELECT container, invno, DATE_FORMAT(`eta`, '%d-%m-%Y') as meta,
                                                                        `remark1` as rem1, `remark2` as rem2 FROM `shipmain` WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mcontno = $nqry["container"];
                                                $minvno = $nqry["invno"];
                                                $metadt = $nqry["meta"];
                                                $mnarr = trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);
//                                                $mchqdtl = "Bill No. : " .  trim($minvno) . " &nbsp;&nbsp;Inv.NO. : " . $minvno;
                                                $head = "ETA - " . $metadt . "  &nbsp;&nbsp;Cont.No. : " . trim($mcontno) . " &nbsp;&nbsp;Inv.No. : " . $minvno;
                                                
//                                            }
                                            break;
                                        case "PU";
                                            $head = "Purchase";
                                            $url = "purchase.php?vouchno=$mvouch&method=ledger";
                                            $totdiscprov = $totdiscprov + $mamount;
//                                            if ($prnnarr == 1) {

                                                $nqry1 = mysqli_query($conn, "SELECT containerno, invno, sealno, DATE_FORMAT(`invdt`, '%d-%m-%Y') as mbilldate,
                                                                        `rem1`, `rem2` FROM  `purmain` WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mcontno = $nqry["containerno"];
                                                $minvno = $nqry["invno"];
                                                $mbilldt = $nqry["mbilldate"];
                                                $mnarr = trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);
//                                                $mchqdtl = "Bill No. : " .  trim($minvno) . " &nbsp;&nbsp;Inv.NO. : " . $minvno;
                                                $head = "Purchase - Cont.No. : " . trim($mcontno) . " &nbsp;&nbsp;Inv.No. : " . $minvno;
                                                
//                                            }
                                            break;
                                        case "PUPK";
                                            $head = "Purchase Packing";
                                            $url = "purchase.php?mode=edit&type=P&vouchno=$mvouch&method=ledger";
//                                            $totdiscprov = $totdiscprov + $mamount;
                                            if ($prnnarr == 1) {

                                                $nqry1 = mysqli_query($conn, "SELECT invno as billno, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mbilldate,
                                                                        `rem1`, `rem2` FROM `rawmain`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mbill = $nqry["billno"];
                                                $mbilldt = $nqry["mbilldate"];
                                                $mnarr = trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);
                                                $mchqdtl = "Bill No. : " . trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                                            }
                                            break;
                                        case "PURW";
                                            $head = "Purchase Raw Material";
                                            $url = "purchase.php?mode=edit&type=R&vouchno=$mvouch&method=ledger";
//                                            $totdiscprov = $totdiscprov + $mamount;
                                            if ($prnnarr == 1) {

                                                $nqry1 = mysqli_query($conn, "SELECT invno as billno, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mbilldate,
                                                                        `rem1`, `rem2` FROM `rawmain`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mbill = $nqry["billno"];
                                                $mbilldt = $nqry["mbilldate"];
                                                $mnarr = trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);
                                                $mchqdtl = "Bill No. : " . trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                                            }
                                            break;
                                        case "PR";
                                            $head = "Purchase Return";
                                            $url = "debnote.php?vouchno=$mvouch&method=ledger";
                                            $totdiscprov = $totdiscprov + $mamount;
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT invno, DATE_FORMAT(`invdt`, '%d-%m-%Y') as mbilldate,
                                                                        `remark1`, `remark2` FROM `debmain`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mbill = $nqry["invno"];
                                                $mbilldt = $nqry["mbilldate"];
                                                $mnarr = trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);
                                                $mchqdtl = "Bill No. : " . trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                                            }
                                            break;
                                        case "SL";
                                            $head = "Sale";
                                            $url = "sale.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT invno, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mbilldate,
                                                                             `remark1`, `remark2` FROM `salemain` 
                                                                              WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mbill = $nqry["invno"];
                                                $mbilldt = $nqry["mbilldate"];

                                                $mnarr = trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);
                                                $mchqdtl = "Bill No. : " . trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                                            }
                                            break;
                                        case "SLDE";
                                            $head = "Sale Deemed";
                                            $url = "sale_de.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT invno, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mbilldate,
                                                                             `rem1`, `rem2` FROM `deexmain` 
                                                                              WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mbill = $nqry["invno"];
                                                $mbilldt = $nqry["mbilldate"];

                                                $mnarr = trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);
                                                $mchqdtl = "Bill No. : " . trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                                            }
                                            break;
                                        case "SLOT";
                                            $head = "Sale Other";
                                            $url = "saleoth.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT invno, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mbilldate,
                                                                             `rem1`, `rem2` FROM `slotmain` 
                                                                              WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mbill = $nqry["invno"];
                                                $mbilldt = $nqry["mbilldate"];

                                                $mnarr = trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);
                                                $mchqdtl = "Bill No. : " . trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                                            }
                                            break;
                                        case "SLEX";
                                            $head = "Sale Export";
                                            $url = "sale_ex.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT invoice, DATE_FORMAT(`invdate`, '%d-%m-%Y') as mbilldate,
                                                                             `rem1`, `rem2` FROM `salemainexp` 
                                                                              WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mbill = $nqry["invno"];
                                                $mbilldt = $nqry["mbilldate"];

                                                $mnarr = trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);
                                                $mchqdtl = "Bill No. : " . trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                                            }
                                            break;
                                        case "SLON";
                                            $head = "Sale Online";
                                            $url = "sale_onl.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT invno, buyer, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mbilldate,
                                                                             `rem1`, `rem2` FROM `slonlmain` 
                                                                              WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mbill = $nqry["invno"];
                                                $mbilldt = $nqry["mbilldate"];

                                                $mnarr = trim($nqry["buyer"]) . " " . trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);
                                                $mchqdtl = "Bill No. : " .  trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                                            }
                                            break;
                                        case "SR";
                                            $head = "Sale Return";
                                            $url = "crednote.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            break;
                                        case "BR";
                                            $head = "Bank Receipt";
                                            $url = "bankrecp.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT chqno, DATE_FORMAT(`chqdate`, '%d-%m-%Y') as mbilldate, 
                                                                             `remark1`, remark2 FROM `bankrecp`
                                                                              WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mbill = $nqry["chqno"];

                                                $mbilldt = $nqry["mbilldate"];
                                                $mnarr = $nqry["remark1"] . " " . $nqry["remark2"];

                                                $mchqdtl = "Cheque. : " . trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                                            }
                                            break;
                                        case "BP";
                                            $head = "Bank Payment";
                                            $url = "bankpay.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT chqno, DATE_FORMAT(`chqdate`, '%d-%m-%Y') as mbilldate, 
                                                                        `remark1`, remark2 FROM `bankpay`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mbill = $nqry["chqno"];
                                                $mbilldt = $nqry["mbilldate"];
                                                $mnarr = $nqry["remark1"] . " " . $nqry["remark2"];
                                                $mchqdtl = "Cheque. : " .  trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                                            }
                                            break;
                                        case "CR";
                                            $head = "Cash Receipt";
                                            $url = "cashrecp.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT `remark1`, remark2 FROM `cashrecp`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mnarr = $nqry["remark1"] . " " . $nqry["remark2"];
                                            }
                                            break;
                                        case "CP";
                                            $head = "Cash Payment";
                                            $url = "cashpay.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT `remark1`, remark2 FROM `cashpay`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mnarr = $nqry["remark1"] . " " . $nqry["remark2"];
                                            }
                                            break;
                                        case "TR";
                                            $head = "Contra / Transfer";
                                            $url = "contra.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT `narr1`, narr2 FROM `contra`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mnarr = $nqry["narr1"] . "" . $nqry['narr2'];
                                            }
                                            break;
                                        case "JV";
                                            $head = "Journal Voucher";
                                            $url = "jv.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT `remark1`, remark2 FROM `jvmain`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mnarr = $nqry["remark1"] . "" . $nqry['remark2'];
                                            }
                                            break;
                                        case "DG";
                                            $head = "Discount Given";
                                            break;
                                        case "DR";
                                            $head = "Discount Received";
                                            break;
                                        default :
                                            $head = "Other";
                                    }
                                    echo "<tr class='clickable-row2' data-href='$url' data-target='_blank' style='background-color:white'>";
                                    echo "<td style=\"text-align:left;\" scope=\"col\"  width=\"15%\">$vdate</td>";
                                    echo "<td style=\"text-align:left;\" scope=\"col\"  width=\"4%\">$msource</td>";
                                    echo "<td style=\"text-align:left;\" scope=\"col\"  width=\"8%\">$mvouch</td>";
                                    echo "<td style=\"text-align:left;\" scope=\"col\"  width=\"35%\" colspan='2'>$head</td>";
                                    echo "<td style=\"text-align:right;\" scope=\"col\" width=\"10%\">" . number_format($mdebit, 2) . "</td>";
                                    echo "<td style=\"text-align:right;\" scope=\"col\" width=\"10%\">" . number_format($mcredit, 2) . "</td>";
                                    echo "<td style=\"text-align:right;\" scope=\"col\" width=\"14%\">" . number_format(abs($totbal), 2) . " " . $mdctot . "</td>";
                                    echo "</tr>";
                                    if ($prnnarr == 1) {
                                        if (!empty($mchqdtl)) {
                                            echo "<tr style='background-color:white'>";
                                            echo "<td></td>";
                                            echo "<td></td>";
                                            echo "<td></td>";
                                            echo "<td>$mchqdtl</td>";
                                            echo "<td></td>";
                                            echo "<td></td>";
                                            echo "<td></td>";
                                            echo "<td></td>";
                                            echo "</tr>";
                                        }

                                        if (!empty($mnarr)) {

                                            echo "<tr style='background-color:white'>";
                                            echo "<td></td>";
                                            echo "<td></td>";
                                            echo "<td></td>";
                                            echo "<td style=\"text-align:left;\" scope=\"col\" width=\"200\">$mnarr</td>";
                                            echo "<td></td>";
                                            echo "<td></td>";
                                            echo "<td></td>";
                                            echo "<td></td>";
                                            echo "</tr>";
                                        }
                                    }
                                }
                                echo "<tr style='background-color:white'>";
                                echo "<td style=\"text-align:left;\" scope=\"col\" width=\"15%\"></td>";
                                echo "<td style=\"text-align:left;\" scope=\"col\" width=\"4%\"></td>";
                                echo "<td style=\"text-align:left;\" scope=\"col\" width=\"4%\"></td>";
                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"32%\"><strong>Total</strong></td>";
                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"10%\">Opening - " . number_format($opening, 2) . "</td>";
                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"10%\">" . number_format($dtot, 2) . "</td>";
                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"10%\">" . number_format($ctot, 2) . "</td>";
                                if ($totbal < 0) {
                                    echo "<td style=\"text-align:right;\" scope=\"col\" width=\"14%\"><font color='red'><strong>" . number_format(abs($totbal), 2) . " " . $mdctot . "</strong></font></td>";
                                } else {
                                    echo "<td style=\"text-align:right;\" scope=\"col\" width=\"14%\"><font color='green'><strong>" . number_format($totbal, 2) . " " . $mdctot . "</strong></font></td>";
                                }
                                echo "</tr>";
                            }
                        }
                        if ($dtype < 2) {
                            $totobals = abs($totobal);
                            if ($totobal < 0) {
                                $todc = 'DR';
                            } else {
                                $todc = 'CR';
                            }

                            $totbals = abs($totbal);
                            if ($totbal < 0) {
                                $tbdc = 'DR';
                            } else {
                                $tbdc = 'CR';
                            }

                            $tcolr = 'color:red';

                            echo '</tr>';
                            echo "<tr>";
                            echo "<td align='right'><strong>Total</strong></td>";
                            echo "<td style='text-align:right; $tcolr' <strong>" . number_format($totobals, 2) . " " . $todc . "</strong></td>";
                            echo "<td style='text-align:right; $tcolr' <strong>" . number_format($totdeb, 2) . "</strong></td>";
                            echo "<td style='text-align:right; $tcolr' <strong>" . number_format($totcrd, 2) . "</strong></td>";
                            echo "<td style='text-align:right; $tcolr' <strong>" . number_format($totbals, 2) . " " . $tbdc . "</strong></td>";
                            echo "<tr>";
                        }
                        ?>
                    </tbody>
                    <tfoot>
                        <tr>
                            <td colspan="9">
                            </td>
                        </tr>
                    </tfoot>
                </table>
            </div>
        </div>
    </div>
    <?php include("includes/linksfoot.php"); ?>
    </body>
    </html>
    <?php
}

if (isset($_POST["excel"])) {
    $gcond = "";
    $accond = "";
    $stcond = "";
    $ctcond = "";

    if (!empty($_POST["group"])) {
        $grp = trim($_POST["group"]);
        $gcond = "AND `grpcode`='$grp'";
    }


    if (!empty($_POST["acname"])) {
        $accode = trim($_POST["acname"]);
        $accond = "AND `accode`='$accode'";
    }


    if (!empty($_POST["cmbstation"])) {
        $station = trim($_POST["cmbstation"]);
        $stcond = " AND `station`= '$station' ";
    }


    if (!empty($_POST["city"])) {
        $city = trim($_POST["city"]);
        $ctcond = " AND `city`='$city'";
    }

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


    if ($mfrmdate > $mtodate) {
        browse("To Date Cannot Be Less Than From Date");
        header("refresh: 1; ledger.php");
        exit();
    }


    if (!empty($_POST["prinzero"])) {
        $prnzero = $_POST["prinzero"];
    }

    if (!empty($_GET["prz"])) {
        $prnzero = $_GET["prz"];
    }

    if (!empty($_POST["prinnarr"])) {
        $prnnarr = $_POST["prinnarr"];
    }

    if (!empty($_GET["prn"])) {
        $prnnarr = $_GET["prn"];
    }


    if ($_POST["dtype"] == 1) {
        $repname = "Ledger - Summary";
        $dtype = 1;
    } else {
        $repname = "Ledger - Detail";
        $dtype = 2;
    }

    if ($_POST["dtype"] == 0) {
        $repname = "Ledger - Monthly Summary";
        $dtype = 0;
    }

    $acc = mysqli_query($conn, "SELECT * FROM `$maindb` WHERE accode!='' $gcond $accond $ctcond $stcond  order by name") or die(mysqli_error($conn));

    if (mysqli_num_rows($acc) <= 0) {
        print "<script>alert('No Record Found')</script>";
        header("refresh: 1; ledger.php");
        exit();
    }

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


    if ($dtype == 1) {
        $contents .= "Account Name, Opening, , Debit, Credit, Balance, ,\n";
    }

    mysqli_select_db($conn, $zdb);
    $totbal = 0;
    $url = '';

    $totbal = $totobal = $totdeb = $totcrd = 0;

    mysqli_query($conn, "drop temporary table if exists opdata") or die(mysqli_error($conn));
    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") or die(mysqli_error($conn));

    mysqli_query($conn, "drop temporary table if exists drdata") or die(mysqli_error($conn));
    mysqli_query($conn, "create temporary table drdata SELECT sum(amount) as amtdr, accode FROM `$trandb` WHERE 
                                                amount < 0 and date >= '$mfrmdate' and date <= '$mtodate' and retdate = '0000-00-00' group by accode") or die(mysqli_error($conn));

    mysqli_query($conn, "drop temporary table if exists crdata") or die(mysqli_error($conn));
    mysqli_query($conn, "create temporary table crdata SELECT sum(amount) as amtcr, accode FROM `$trandb` WHERE 
                                                amount > 0 and date >= '$mfrmdate' and date <= '$mtodate' and retdate = '0000-00-00' group by accode") or die(mysqli_error($conn));

    if ($dtype == 2) {
        mysqli_query($conn, "drop temporary table if exists baldata") or die(mysqli_error($conn));
        mysqli_query($conn, "create temporary table baldata SELECT SUM(amount) as amt, accode FROM `$trandb` WHERE 
                                                      date <= '$mtodate' and `retdate` = '0000-00-00' ") or die(mysqli_error($conn));
    }

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

        if ($dtype < 2) {

            $maccode = $getac["accode"];
            $acname = $getac["name"];
            $mname = $acname;

            $mopbal = $getac["openbal"];
            $mopdc = $getac["dc"];
            if ($mopdc == "D") {
                $mopbal = $mopbal * - 1;
            }

            ############PARTY NAME####################

            $getop = mysqli_query($conn, "SELECT amt FROM `opdata` WHERE  `accode`='$maccode'  ") or die(mysqli_error($conn));
            $fetop = mysqli_fetch_array($getop);
            $opamt = $fetop["amt"];
            $obal = $mopbal + $opamt;
            if ($obal < 0) {
                $odc = "DR";
            } else {
                $odc = "CR";
            }

            $gettrdr = mysqli_query($conn, "SELECT amtdr FROM `drdata` WHERE `accode`='$maccode' ") or die(mysqli_error($conn));
            $fettrdr = mysqli_fetch_array($gettrdr);
            $tramtdr = $fettrdr["amtdr"];
            $tramtdr = abs($tramtdr);

            $gettrcr = mysqli_query($conn, "SELECT amtcr FROM `crdata` WHERE `accode`='$maccode' ") or die(mysqli_error($conn));
            $fettrcr = mysqli_fetch_array($gettrcr);
            $tramtcr = $fettrcr["amtcr"];
            $tramtcr = abs($tramtcr);

            $mbal = $obal - $tramtdr + $tramtcr;
            if ($mbal < 0) {
                $mdc = "DR";
            } else {
                $mdc = "CR";
            }

            $show = 1;
            if ($prnzero == 1) {
                $show = 1;
            } else {
                if ($mbal != 0) {
                    $show = 1;
                } else {
                    $show = 0;
                }
            }

            $obal1 = abs($obal);
            $mbal1 = abs($mbal);
            if ($show == 1) {
                $contents .= $acname . ",";
                $contents .= $obal1 . "," . $odc . ",";
                $contents .= $tramtdr . ",";
                $contents .= $tramtcr . ",";
                $contents .= $mbal1 . "," . $mdc . "\n";

                $totobal = $totobal + $obal;

                $totdeb = $totdeb + $tramtdr;
                $totcrd = $totcrd + $tramtcr;

                $totbal = $totbal + $mbal;
            }

             if ($totbal < 0) {
                $mdctot = "DR";
            } else {
                $mdctot = "CR";
            }

            if ($dtype == 0) {
                mysqli_query($conn, "drop temporary table if exists mondata") or die(mysqli_error($conn));
                mysqli_query($conn, "create temporary table mondata SELECT *, month(date) as mon  FROM `$trandb` WHERE 
                                                `accode`='$maccode' and date >= '$mfrmdate' and date <= '$mtodate' and retdate = '0000-00-00'") or die(mysqli_error($conn));

                $getmon = mysqli_query($conn, "SELECT SUM(amount) as monamt, date, mon  FROM `mondata` group by mon order by date") or die(mysqli_error($conn));
                if (mysqli_num_rows($getmon) > 0) {

                    $contents .= "Month, Opening, , Debit, Credit, Balance, ,\n";
                    while ($fetmon = mysqli_fetch_array($getmon)) {
                        $mon = $fetmon['mon'];
                        $monamt = $fetmon['monamt'];

                        if ($mon < 10) {
                            $mond = '0' . $mon;
                        } else {
                            $mond = $mon;
                        }

                        $monopn = 0;

                        if ($mon < 4) {
                            $date1 = $zeyear . '-' . $mond . '-01';
                        } else {
                            $date1 = $zsyear . '-' . $mond . '-01';
                        }

                        $getmn = mysqli_query($conn, "SELECT descrip  FROM $zroot.`months` WHERE `code`='$mon' ") or die(mysqli_error($conn));
                        $fetmn = mysqli_fetch_array($getmn);
                        $monname = $fetmn['descrip'];


                        $getopn = mysqli_query($conn, "SELECT SUM(amount) as oamt FROM `mondata` WHERE date < '$date1' ") or die(mysqli_error($conn));
                        $fetopn = mysqli_fetch_array($getopn);
                        $opnamt = $fetopn["oamt"];
                        $monopn = $mopbal + $opnamt;

                        if ($monopn < 0) {
                            $mdco = "DR";
                        } else {
                            $mdco = "CR";
                        }


                        $getdeb = mysqli_query($conn, "SELECT SUM(amount) as mondeb FROM `mondata` WHERE amount < 0 and 
                                                                   month(date) = '$mon' ") or die(mysqli_error($conn));
                        $fetdeb = mysqli_fetch_array($getdeb);
                        $mondeb = $fetdeb['mondeb'];

                        $getcrd = mysqli_query($conn, "SELECT SUM(amount) as moncrd FROM `mondata` WHERE amount > 0 and 
                                                                   month(date) = '$mon' ") or die(mysqli_error($conn));
                        $fetcrd = mysqli_fetch_array($getcrd);
                        $moncrd = $fetcrd['moncrd'];

                        $monbal = $monopn + $mondeb + $moncrd;

                        if ($monbal < 0) {
                            $mdcmon = "DR";
                        } else {
                            $mdcmon = "CR";
                        }

                        $monopn1 = abs($monopn);
                        $mondeb1 = abs($mondeb);
                        $moncrd1 = abs($moncrd);
                        $monbal1 = abs($monbal);

                        $contents .= $monname . ",";
                        $contents .= $monopn1 . "," . $mdco . ",";
                        $contents .= $mondeb1 . ",";
                        $contents .= $moncrd1 . ",";
                        $contents .= $monbal1 . "," . $mdcmon . "\n";
                    }
                }
            }
        } else {

            /** Detail Part* */
            $totbal = 0;
            $dtot = 0;
            $ctot = 0;
            $maccode = $getac["accode"];
            $macname = $getac["name"];
            $mopbal = $getac["openbal"];
            $mopdc = $getac["dc"];

            if ($mopdc == "D") {
                $mopbal = $mopbal * - 1;
            }

            $ctran1 = 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") or die(mysqli_error($conn));

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

            $getbal = mysqli_query($conn, "SELECT amt FROM `opdata` WHERE accode='$maccode' ") or die(mysqli_error($conn));
            $getamt = mysqli_fetch_array($getbal);
            $amt = $getamt["amt"];
            $mobal = $mopbal + $amt;

            $mdebit = 0;
            $mcredit = 0;
            if ($mobal < 0) {
                $mdc = "DR";
                $mdebit = abs($mobal);
            } else {
                $mdc = "CR";
                $mcredit = abs($mobal);
            }

            $vdate1 = strtotime($mfrmdate);
            $vdate = date('d-m-Y', $vdate1);

            $mvouch = "";

            $mdescrip = "Opening Balance";

            $totbal = $totbal + $mobal;

            if ($totbal < 0) {
                $mdctot = "DR";
            } else {
                $mdctot = "CR";
            }
            $getbal = mysqli_query($conn, "SELECT amt FROM `baldata` WHERE accode='$maccode' ") or die(mysqli_error($conn));
            $getamt = mysqli_fetch_array($getbal);
            $amt = $getamt["amt"];
            $mbal = $mopbal + $amt;
            if ($mbal < 0) {
                $mdc = "DR";
            } else {
                $mdc = "CR";
            }
            
            $contents .= strtoupper($macname) . ",";
            $contents .= abs($mbal) . $mdc . "\n";

            $contents .= "Date,Source,Voucher,Description,Debit,Credit,Balance,\n";

            $contents .= $vdate . ",";
            $contents .= " " . ",";
            $contents .= $mvouch . ",";
            $contents .= $mdescrip . ",";
            $contents .= $mdebit . ",";
            $contents .= $mcredit . ",";
            $contents .= abs($totbal) . " " . $mdctot . "\n";

            if ($mcredit > 0) {
                $opening = $mcredit;
            } else {
                $opening = $mdebit;
            }

            while ($row = mysqli_fetch_array($ctran1)) {
                $vdate = $row["mydt"];
                $msource = $row["source"];
                $mvouch = $row["vouchno"];
                $mamount = $row["amount"];

                $mgrpcode = $getac["grpcode"];
                $mdiscprov = $getac["discount"];
                $totdiscprov = 0;

                $mdebit = 0;
                $mcredit = 0;

                if ($mamount < 0) {
                    $mdc = "DR";
                    $mdebit = abs($mamount);
                    $dtot = $dtot + $mdebit;
                } else {
                    $mdc = "CR";
                    $mcredit = abs($mamount);
                    $ctot = $ctot + $mcredit;
                }

                $totbal = $totbal + $mamount;

                if ($totbal < 0) {
                    $mdctot = "DR";
                } else {
                    $mdctot = "CR";
                }


                $mdescrip = "";
                                    $mchqdtl = "";
                                    $mnarr = "";
                                    switch ($msource) {
                                        case "ETA";
                                            $head = "ETA";
                                            $url = "shipperent.php?vouchno=$mvouch&method=ledger";
//                                            if ($prnnarr == 1) {

                                                $nqry1 = mysqli_query($conn, "SELECT container, invno, DATE_FORMAT(`eta`, '%d-%m-%Y') as meta,
                                                                        `remark1` as rem1, `remark2` as rem2 FROM `shipmain` WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mcontno = $nqry["container"];
                                                $minvno = $nqry["invno"];
                                                $metadt = $nqry["meta"];
                                                $mnarr = trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);
//                                                $mchqdtl = "Bill No. : " .  trim($minvno) . " &nbsp;&nbsp;Inv.NO. : " . $minvno;
                                                $head = "ETA - " . $metadt . "  &nbsp;&nbsp;Cont.No. : " . trim($mcontno) . " &nbsp;&nbsp;Inv.No. : " . $minvno;
                                                
//                                            }
                                            break;
                                        case "PU";
                                            $head = "Purchase";
                                            $url = "purchase.php?vouchno=$mvouch&method=ledger";
                                            $totdiscprov = $totdiscprov + $mamount;
//                                            if ($prnnarr == 1) {

                                                $nqry1 = mysqli_query($conn, "SELECT containerno, invno, sealno, DATE_FORMAT(`invdt`, '%d-%m-%Y') as mbilldate,
                                                                        `rem1`, `rem2` FROM  `purmain` WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mcontno = $nqry["containerno"];
                                                $minvno = $nqry["invno"];
                                                $mbilldt = $nqry["mbilldate"];
                                                $mnarr = trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);
//                                                $mchqdtl = "Bill No. : " .  trim($minvno) . " &nbsp;&nbsp;Inv.NO. : " . $minvno;
                                                $head = "Purchase - Cont.No. : " . trim($mcontno) . " &nbsp;&nbsp;Inv.No. : " . $minvno;
                                                
//                                            }
                                            break;
                                        case "PUPK";
                                            $head = "Purchase Packing";
                                            $url = "purchase.php?mode=edit&type=P&vouchno=$mvouch&method=ledger";
//                                            $totdiscprov = $totdiscprov + $mamount;
                                            if ($prnnarr == 1) {

                                                $nqry1 = mysqli_query($conn, "SELECT invno as billno, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mbilldate,
                                                                        `rem1`, `rem2` FROM `rawmain`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mbill = $nqry["billno"];
                                                $mbilldt = $nqry["mbilldate"];
                                                $mnarr = trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);
                                                $mchqdtl = "Bill No. : " . trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                                            }
                                            break;
                                        case "PURW";
                                            $head = "Purchase Raw Material";
                                            $url = "purchase.php?mode=edit&type=R&vouchno=$mvouch&method=ledger";
//                                            $totdiscprov = $totdiscprov + $mamount;
                                            if ($prnnarr == 1) {

                                                $nqry1 = mysqli_query($conn, "SELECT invno as billno, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mbilldate,
                                                                        `rem1`, `rem2` FROM `rawmain`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mbill = $nqry["billno"];
                                                $mbilldt = $nqry["mbilldate"];
                                                $mnarr = trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);
                                                $mchqdtl = "Bill No. : " . trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                                            }
                                            break;
                                        case "PR";
                                            $head = "Purchase Return";
                                            $url = "debnote.php?vouchno=$mvouch&method=ledger";
                                            $totdiscprov = $totdiscprov + $mamount;
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT invno, DATE_FORMAT(`invdt`, '%d-%m-%Y') as mbilldate,
                                                                        `remark1`, `remark2` FROM `debmain`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mbill = $nqry["invno"];
                                                $mbilldt = $nqry["mbilldate"];
                                                $mnarr = trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);
                                                $mchqdtl = "Bill No. : " . trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                                            }
                                            break;
                                        case "SL";
                                            $head = "Sale";
                                            $url = "sale.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT invno, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mbilldate,
                                                                             `remark1`, `remark2` FROM `salemain` 
                                                                              WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mbill = $nqry["invno"];
                                                $mbilldt = $nqry["mbilldate"];

                                                $mnarr = trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);
                                                $mchqdtl = "Bill No. : " . trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                                            }
                                            break;
                                        case "SLDE";
                                            $head = "Sale Deemed";
                                            $url = "sale_de.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT invno, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mbilldate,
                                                                             `rem1`, `rem2` FROM `deexmain` 
                                                                              WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mbill = $nqry["invno"];
                                                $mbilldt = $nqry["mbilldate"];

                                                $mnarr = trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);
                                                $mchqdtl = "Bill No. : " . trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                                            }
                                            break;
                                        case "SLOT";
                                            $head = "Sale Other";
                                            $url = "saleoth.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT invno, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mbilldate,
                                                                             `rem1`, `rem2` FROM `slotmain` 
                                                                              WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mbill = $nqry["invno"];
                                                $mbilldt = $nqry["mbilldate"];

                                                $mnarr = trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);
                                                $mchqdtl = "Bill No. : " . trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                                            }
                                            break;
                                        case "SLEX";
                                            $head = "Sale Export";
                                            $url = "sale_ex.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT invoice, DATE_FORMAT(`invdate`, '%d-%m-%Y') as mbilldate,
                                                                             `rem1`, `rem2` FROM `salemainexp` 
                                                                              WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mbill = $nqry["invno"];
                                                $mbilldt = $nqry["mbilldate"];

                                                $mnarr = trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);
                                                $mchqdtl = "Bill No. : " . trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                                            }
                                            break;
                                        case "SLON";
                                            $head = "Sale Online";
                                            $url = "sale_onl.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT invno, buyer, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mbilldate,
                                                                             `rem1`, `rem2` FROM `slonlmain` 
                                                                              WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mbill = $nqry["invno"];
                                                $mbilldt = $nqry["mbilldate"];

                                                $mnarr = trim($nqry["buyer"]) . " " . trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);
                                                $mchqdtl = "Bill No. : " .  trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                                            }
                                            break;
                                        case "SR";
                                            $head = "Sale Return";
                                            $url = "crednote.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            break;
                                        case "BR";
                                            $head = "Bank Receipt";
                                            $url = "bankrecp.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT chqno, DATE_FORMAT(`chqdate`, '%d-%m-%Y') as mbilldate, 
                                                                             `remark1`, remark2 FROM `bankrecp`
                                                                              WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mbill = $nqry["chqno"];

                                                $mbilldt = $nqry["mbilldate"];
                                                $mnarr = $nqry["remark1"] . " " . $nqry["remark2"];

                                                $mchqdtl = "Cheque. : " . trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                                            }
                                            break;
                                        case "BP";
                                            $head = "Bank Payment";
                                            $url = "bankpay.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT chqno, DATE_FORMAT(`chqdate`, '%d-%m-%Y') as mbilldate, 
                                                                        `remark1`, remark2 FROM `bankpay`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mbill = $nqry["chqno"];
                                                $mbilldt = $nqry["mbilldate"];
                                                $mnarr = $nqry["remark1"] . " " . $nqry["remark2"];
                                                $mchqdtl = "Cheque. : " .  trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                                            }
                                            break;
                                        case "CR";
                                            $head = "Cash Receipt";
                                            $url = "cashrecp.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT `remark1`, remark2 FROM `cashrecp`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mnarr = $nqry["remark1"] . " " . $nqry["remark2"];
                                            }
                                            break;
                                        case "CP";
                                            $head = "Cash Payment";
                                            $url = "cashpay.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT `remark1`, remark2 FROM `cashpay`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mnarr = $nqry["remark1"] . " " . $nqry["remark2"];
                                            }
                                            break;
                                        case "TR";
                                            $head = "Contra / Transfer";
                                            $url = "contra.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT `narr1`, narr2 FROM `contra`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mnarr = $nqry["narr1"] . "" . $nqry['narr2'];
                                            }
                                            break;
                                        case "JV";
                                            $head = "Journal Voucher";
                                            $url = "jv.php?mode=edit&vouchno=$mvouch&method=ledger";
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn, "SELECT `remark1`, remark2 FROM `jvmain`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mnarr = $nqry["remark1"] . "" . $nqry['remark2'];
                                            }
                                            break;
                                        case "DG";
                                            $head = "Discount Given";
                                            break;
                                        case "DR";
                                            $head = "Discount Received";
                                            break;
                                        default :
                                            $head = "Other";
                                    }

                $contents .= $vdate . ",";
                $contents .= $msource . ",";
                $contents .= $mvouch . ",";
                $contents .= $head . ",";
                $contents .= $mdebit . ",";
                $contents .= $mcredit . ",";
                $contents .= abs($totbal) . " " . $mdctot . "\n";

                if ($prnnarr == 1) {
                    if ($mchqdtl != '') {
                        $contents .= " " . ",";
                        $contents .= " " . ",";
                        $contents .= " " . ",";
                        $contents .= $mchqdtl . ",";
                        $contents .= " " . ",";
                        $contents .= " " . ",";
                        $contents .= " " . "\n";
                    }

                    if (!empty($mnarr)) {

                        $contents.= "" . ",";
                        $contents.= "" . ",";
                        $contents.= "" . ",";
                        $contents.= $mnarr . ",";
                        $contents.= "" . ",";
                        $contents.= "" . ",";
                        $contents.= "" . "\n";
                    }
                }
            }

            $contents .= " " . ",";
            $contents .= " " . ",";
            $contents .= " " . ",";
            $contents .= "Total     Opening -" . $opening . ",";
            $contents .= $dtot . ",";
            $contents .= $ctot . ",";

            if ($totbal < 0) {
                $contents .= abs($totbal) . " " . $mdctot . "\n";
            } else {
                $contents .= abs($totbal) . " " . $mdctot . "\n";
            }
        }
    }

    if ($dtype < 2) {
        $totobals = abs($totobal);
        if ($totobal < 0) {
            $todc = 'DR';
        } else {
            $todc = 'CR';
        }

        $totbals = abs($totbal);
        if ($totbal < 0) {
            $tbdc = 'DR';
        } else {
            $tbdc = 'CR';
        }

        $contents .= "Total,";
        $contents .= $totobals . "," . $todc . ",";
        $contents .= $totdeb . ",";
        $contents .= $totcrd . ",";
        $contents .= $totbals . "," . $tbdc . "\n";
    }


    $contents = strip_tags($contents);

    if ($_POST["dtype"] == 1) {
        header("Content-Disposition: attachment; filename=$formname" . "_Summary" . date('d-m-Y') . ".csv");
        print $contents;
    } else {
        header("Content-Disposition: attachment; filename=$formname" . "_Detail" . date('d-m-Y') . ".csv");
        print $contents;
    }
}
?>

