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

$zdb = $_SESSION["zdbyear_" . $dbname];
$admin = $_SESSION["admin_" . $dbname];
$formname = "LEDGER";
$grid = "ledger.php";
$file = "ledger.php";
$maindb = "accmast";
mysqli_select_db($conn,$zdb);
$url = "http://" . $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'];
$url = str_replace("rpt=1", "rpt=2", "$url");

$trandb = "actran";

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["zerobal"])) {
        $prnzero = $_POST["zerobal"];
    }
    if (!empty($_POST["prinnarr"])) {
        $prnnarr = $_POST["prinnarr"];
    }
    if (!empty($_GET["prz"])) {
        $prnzero = $_GET["prz"];
    }
    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;
    }
//browse($acname);
    $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>
        .bakc{
            background-color: #2FBEF3;
        }
    </style>
    </head>
    <body>   
        <div class="container">                                                

            <?php include("includes/head_rep.php"); ?>
            <div class="row">
                <table class="table table-bordered table-condensed table-striped" width="100%">
                    <thead>
                        <tr class="mySearchTr">
                            <?php if ($dtype == 1) { ?>
                                <th align="left" width="50%">Account Name</th>
                                <th align="right" width="50%" style="text-align:right;">Balance</th>
                                <?php
                            }
                            ?>
                        </tr>
                    </thead>
                    <tbody>
                        <?php
                        mysqli_select_db($conn,$zdb);
                        $totbal = 0;
                        while ($getac = mysqli_fetch_array($acc)) {

                            if ($dtype == 1) {
                                Echo"<tr  class='clickable-row2' data-href='$url' [target]='_blank'  style='background-color:white'>";

                                $maccode = $getac["accode"];
                                $mopbal = $getac["openbal"];
                                $mopdc = $getac["dc"];
                                if ($mopdc == "D") {
                                    $mopbal = $mopbal * - 1;
                                }
                                ############PARTY NAME####################
                                $getpartyname = mysqli_query($conn,"SELECT `name` FROM `accmast` WHERE `accode`='$maccode'") or die(mysqli_error($conn));
                                $zname = mysqli_fetch_object($getpartyname);

                                $mname = $zname->name;

                                $getbal = mysqli_query($conn,"SELECT SUM(amount) as amt FROM `$trandb` WHERE accode='$maccode' AND  date <= '$mfrmdate'") or die(mysqli_error($conn));
                                $getamt = mysqli_fetch_array($getbal);
                                $amt = $getamt["amt"];
                                $mbal = $mopbal + $amt;
                                if ($mbal < 0) {
                                    $mdc = "DR";
                                } else {
                                    $mdc = "CR";
                                }
                                $show = 1;
                                if ($prnzero == 1) {
                                    $show = 1;
                                } else {
                                    if ($mbal == 0) {
                                        $show = 0;
                                    }
                                }
                                if ($show == 1) {
                                    echo "<td style=\"text-align:left;\" scope=\"col\" width=\"200\">$mname</td>";
                                    echo "<td style=\"text-align:right;\" scope=\"col\" width=\"200\">" . abs($mbal) . " " . $mdc . "</td>";
                                    $totbal = $totbal + $mbal;
                                }
                                if ($totbal < 0) {
                                    $mdctot = "DR";
                                } else {
                                    $mdctot = "CR";
                                }
                            } 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'
                                                                  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));

                                while ($murl = mysqli_fetch_object($curl)) {
                                    switch ($murl->source) {
                                        case "PU";
                                            $head = "Purchase";
                                            $url = "purchase.php?vouchno=$murl->vouchno&method=ledger";
                                            break;
                                        case "PR";
                                            $head = "Purchase Return";
                                            $url = "debnote.php?vouchno=$murl->vouchno&method=ledger";
                                            break;
                                        case "SL";
                                            $head = "Sale";
                                            $url = "sale.php?vouchno=$murl->vouchno&method=ledger";
                                            break;
                                        case "CH";
                                            $head = "Challan";
                                            $url = "chalmain.php?vouchno=$murl->vouchno&method=ledger";
                                            break;
                                        case "SR";
                                            $head = "Sale Return";
                                            $url = "crednote.php?vouchno=$murl->vouchno&method=ledger";
                                            break;
                                        case "BR";
                                            $url = "bankrecp.php?vouchno=$murl->vouchno&method=ledger";
                                            break;
                                        case "BP";
                                            $head = "Bank Payment";
                                            $url = "bankpay.php?$murl->vouchno&method=ledger";
                                            break;
                                        case "CR";
                                            $head = "Cash Receipt";
                                            $url = "cashrecp.php?$murl->vouchno&method=ledger";
                                            break;
                                        case "CP";
                                            $head = "Cash Payment";
                                            $url = "cashpay.php?$murl->vouchno&method=ledger";
                                            break;
                                        case "TR";
                                            $head = "Contra / Transfer";
                                            $url = "contra.php?vouchno=$murl->vouchno&method=ledger";
                                            break;
                                        case "JV";
                                            $head = "Journal Voucher";
                                            $url = "journal.php?$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 SUM(amount) as amt FROM `actran` WHERE accode='$maccode' AND  date < '$mfrmdate'") 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'") 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 style='background-color:#26A69A'>";
                                echo "<th style=\"text-align:left;\" scope=\"col\" width=\"500\" colspan='7'><b>" . strtoupper($macname) . "</b></th>";
                                echo "<th style=\"text-align:right;\" scope=\"col\" width=\"500\" colspan='1'><b>" . abs($mbal) . $mdc . "</b></th>";
                                echo "</tr>";
                                ?>
                                <tr style="color:black;" class="bakc">
                                    <th align="left">Date</th>                                   
                                    <th align="left">Source</th>                                   
                                    <th align="left">Voucher</th>                                   
                                    <th align="left" colspan="2">Description</th>                                   
                                    <th align="right" style="text-align:right;">Debit</th>                                   
                                    <th align="right" style="text-align:right;">Credit</th>                                   
                                    <th align="right" style="text-align:right;">Balance</th>                                   
                                </tr>
                                <?php
                                echo "<tr  class='' href='$url' 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%\"></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%\">" . (round($mdebit)) . "</td>";
                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"10%\">" . (round($mcredit)) . "</td>";
                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"14%\">" . (round(abs($totbal))) . " " . $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 "PU";
                                            $head = "Purchase";
                                            $url = "purchase.php?vouchno=$mvouch&method=ledger";
                                            $totdiscprov = $totdiscprov + $mamount;
                                            if ($prnnarr == 1) {

                                                $nqry1 = mysqli_query($conn,"SELECT billno, DATE_FORMAT(`billdate`, '%d-%m-%Y') as mbilldate,
                                                                        `remark1`, `remark2` FROM `purmain`
                                                                       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 "CH";
                                            $head = "Challan";
                                            $url = "chalmain.php?vouchno=$mvouch&method=ledger";
                                            $totdiscprov = $totdiscprov + $mamount;
                                            if ($prnnarr == 1) {
                                                $nqry1 = mysqli_query($conn,"SELECT vouchno, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mbilldate
                                                                      FROM `challan`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                $nqry = mysqli_fetch_array($nqry1);
                                                $mbill = $nqry["vouchno"];
                                                $mbilldt = $nqry["mbilldate"];
                                                $mnarr = '';
                                                $mchqdtl = "Bill No. : " . + trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                                            }
                                            break;
                                        case "SL";
                                            $head = "Sale";
                                            if ($prnnarr == 1) {
                                                $mnarr = "";
                                                $mchqdtl = "";
                                            }
                                            break;
                                        case "SR";
                                            $head = "Sale Return";
                                            break;
                                        case "BR";
                                            $head = "Bank Receipt";
                                            $url = "bankrecp.php?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?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?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?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?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?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='clickableRow' href='$url' 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%\">" . (round($mdebit)) . "</td>";
                                    echo "<td style=\"text-align:right;\" scope=\"col\" width=\"10%\">" . (round($mcredit)) . "</td>";
                                    echo "<td style=\"text-align:right;\" scope=\"col\" width=\"14%\">" . (round(abs($totbal))) . " " . $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 - " . (round($opening)) . "</td>";
                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"10%\">" . (round($dtot)) . "</td>";
                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"10%\">" . (round($ctot)) . "</td>";
                                if ($totbal < 0) {
                                    echo "<td style=\"text-align:right;\" scope=\"col\" width=\"14%\"><font color='red'><strong>" . (round(abs($totbal))) . " " . $mdctot . "</strong></font></td>";
                                } else {
                                    echo "<td style=\"text-align:right;\" scope=\"col\" width=\"14%\"><font color='green'><strong>" . (round(abs($totbal))) . " " . $mdctot . "</strong></font></td>";
                                }
                                echo "</tr>";
                            }
                        }
                        if ($dtype == 1) {
                            echo '</tr>';
                            echo "<tr>";
                            echo "<td align='right'><strong>Total</strong></td>";
                            echo "<td style=\"text-align:right;\" scope=\"col\" width=\"200\"><strong>" . abs($totbal) . " " . $mdctot . "</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();
    }



    $prnzero = $_POST["zerobal"];

    $prnnarr = $_POST["prinarr"];



    if ($_POST["dtype"] == 1) {

        $repname = "Ledger - Summary";

        $dtype = 1;
    } else {

        $repname = "Ledger - Detail";

        $dtype = 2;
    }



    $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, Balance,\n";
    }



    mysqli_select_db($conn,$zdb);

    $totbal = 0;

    $url = '';



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



        if ($dtype == 1) {


            $maccode = $getac["accode"];

            $mopbal = $getac["openbal"];

            $mopdc = $getac["dc"];

            if ($mopdc == "D") {

                $mopbal = $mopbal * - 1;
            }



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



            $getpartyname = mysqli_query($conn,"SELECT `name` FROM `accmast` WHERE `accode`='$maccode'") or die(mysqli_error($conn));

            $zname = mysqli_fetch_object($getpartyname);

            $mname = $zname->name;

//                                    browse($mname);
            ############PARTY NAME####################





            $getbal = mysqli_query($conn,"SELECT SUM(amount) as amt FROM `$trandb` WHERE accode='$maccode' AND  date <= '$mtodate'") or die(mysqli_error($conn));
            $getamt = mysqli_fetch_array($getbal);
            $amt = $getamt["amt"];

            $mbal = $mopbal + $amt;
            if ($mbal < 0) {
                $mdc = "DR";
            } else {
                $mdc = "CR";
            }
            $show = 1;
            if ($prnzero == 1) {
                $show = 1;
            } else {
                if ($mbal == 0) {
                    $show = 0;
                }
            }
            if ($show == 1) {
                $contents .= $mname . ",";
                $contents .= abs($mbal) . " " . $mdc . "\n";
                $totbal = $totbal + $mbal;
            }
            if ($totbal < 0) {
                $mdctot = "DR";
            } else {

                $mdctot = "CR";
            }
        } 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'
                                                                  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));



            while ($murl = mysqli_fetch_object($curl)) {



                switch ($murl->source) {



                    case "PU";

                        $head = "Purchase";

                        $url = "purchase.php?vouchno=$murl->vouchno&method=ledger";

                        break;



                    case "PR";

                        $head = "Purchase Return";

                        $url = "debnote.php?vouchno=$murl->vouchno&method=ledger";

                        break;



                    case "SL";

                        $head = "Sale";

                        $url = "sale.php?vouchno=$murl->vouchno&method=ledger";



                        break;



                    case "SR";

                        $head = "Sale Return";

                        $url = "crednote.php?vouchno=$murl->vouchno&method=ledger";

                        break;



                    case "BR";

                        $url = "bankrecp.php?vouchno=$murl->vouchno&method=ledger";

                        break;



                    case "BP";

                        $head = "Bank Payment";

                        $url = "bankpay.php?$murl->vouchno&method=ledger";

                        break;



                    case "CR";

                        $head = "Cash Receipt";

                        $url = "cashrecp.php?$murl->vouchno&method=ledger";

                        break;



                    case "CP";

                        $head = "Cash Payment";

                        $url = "cashpay.php?$murl->vouchno&method=ledger";

                        break;



                    case "TR";

                        $head = "Contra / Transfer";

                        $url = "contra.php?vouchno=$murl->vouchno&method=ledger";

                        break;



                    case "JV";

                        $head = "Journal Voucher";

                        $url = "journal.php?$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 SUM(amount) as amt FROM `actran` WHERE accode='$maccode' AND  date < '$mfrmdate'") 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;
            } 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";
            }


            $contents .= strtoupper($macname) . "\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 = "";


//browse($msource);
                switch ($msource) {



                    case "PU";

                        $head = "Purchase";

                        $url = "purchase.php?vouchno=$mvouch&method=ledger";

                        $totdiscprov = $totdiscprov + $mamount;



                        if ($prnnarr == 1) {

                            $nqry1 = mysqli_query($conn,"SELECT billno, DATE_FORMAT(`billdate`, '%d-%m-%Y') as mbilldate,

                                                                        `remark1`, `remark2` FROM `purmain`

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

                        if ($prnnarr == 1) {

                            $mnarr = "";

                            $mchqdtl = "";
                        }

                        break;



                    case "SR";

                        $head = "Sale Return";



                        break;



                    case "BR";

                        $head = "Bank Receipt";

                        $url = "bankrecp.php?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 = "Bill No. : " . + trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                        }

                        break;



                    case "BP";

                        $head = "Bank Payment";

                        $url = "bankpay.php?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 = "Bill No. : " . + trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                        }

                        break;



                    case "CR";

                        $head = "Cash Receipt";

                        $url = "cashrecp.php?vouchno=$mvouch&method=ledger";

                        if ($prnnarr == 1) {

                            $nqry1 = mysqli_query($conn,"SELECT `remark2`, remark1 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?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?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";



                        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 (!empty($mchqdtl)) {

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

                    if (!empty($mnarr)) {

                        $contents .= " " . ",";
                        $contents .= " " . ",";
                        $contents .= $mnarr . ",";
                        $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 == 1) {

        $contents .= "Total" . ",";
        $contents .= abs($totbal) . " " . $mdctot . "\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;
    }
}
?>

