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

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

$getdates = mysqli_query($conn, "SELECT * FROM `compyear` WHERE finyear='$myyear'") or die(mysqli_error($conn));
$fetdate = mysqli_fetch_array($getdates);
$fdate = $fetdate['startdate'];
$ldate = $fetdate['lastdate'];

mysqli_select_db($conn, $zdb);
$zroot = $_SESSION["zdbroot"];

$formname = "LEDGER";
$grid = "ledger.php";
$file = "ledger.php";
$maindb = "accmast";

mysqli_select_db($conn, $zdb);

if ($admin == 1) {
    $trandb = "actran";
} else {
    $trandb = "actran";
}

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


    $custcode = trim($_POST["custcode "]);


    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($_GET["acname"])) {

        $accode2 = trim($_GET["acname"]);

        $accond2 = "AND `accode`='$accode2'";
    }

    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 (empty($mfrmdate)) {
        $mfrmdate = $fdate;
    }

    if (empty($mtodate)) {
        $mtodate = $ldate;
    }

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

    $prnzero = $_POST["prinzero"];
    $prnnarr = $_POST["prinnarr"];

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

    if (!empty($_GET['dtype'])) {
        $dtype = 2;
        $repname = 'Ledger - Detail';
    }

    $acc = mysqli_query($conn, "SELECT * FROM `$maindb` WHERE accode!='' $gcond $accond $accond2 $ctcond $stcond  order by name") or die(mysqli_error($conn));
    include("includes/linkshead.php");
    $mfdt = date("d-m-Y", strtotime($mfrmdate));
    $mtdt = date("d-m-Y", strtotime($mtodate));
    ?>
    <style>
        table th{
            background-color: #6082BF;
        }
        table{
            width:100%;
        }
    </style>
    </head>
    <body id="ledger">   

        <div class="container">
            <div class="row">
                <div class="col-lg-11">
                    <?php include ("includes/head_rep.php"); ?>
                </div>
                <div class="col-lg-6" style="width:70%;">

                    <div class="box">
                        <div id="sortableTable" class="body collapse in">
                            <table class="table table-bordered sortableTable responsive-table">
                                <thead>
                                    <tr>
                                        <?php if ($dtype == 1) { ?>
                                            <th align="left" width="15%">School Code<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                            <th align="left" width="70%">School Name<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                            <th align="right" width="15%" style="text-align:right;">Balance<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                            <?php
                                        }
                                        ?>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                    mysqli_select_db($conn, $zdb);
                                    $totbal = 0;
                                    $url = '';
                                    while ($getac = mysqli_fetch_array($acc)) {
                                        if ($dtype == 1) {
                                            echo '<tr>';

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

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

                                            $getpartyname = mysqli_query($conn, "SELECT `custcode`, `name` FROM `accmast` WHERE `accode`='$maccode'") or die(mysqli_error($conn));
                                            $zname = mysqli_fetch_object($getpartyname);
                                            $mname = $zname->name;
                                            $mcust = $zname->custcode;
                                            ############PARTY NAME####################

                                            $getbal = mysqli_query($conn, "SELECT SUM(amount) as amt FROM `$trandb` WHERE accode='$maccode' AND  date <= '$mtodate' and chqret != 'Y'") 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=\"10\">$mcust</td>";
                                                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 {
                                            /** Detail Part* */
                                            $totbal = 0;

                                            $dtot = 0;

                                            $ctot = 0;

                                            $maccode = $getac["accode"];

                                            $mcustcode = $getac["custcode"];

                                            $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 chqret != 'Y'
//                                                               ORDER BY `date`, vouchno") or die(mysqli_error($conn));
                                            mysqli_query($conn, "create temporary table tranpart (SELECT *, DATE_FORMAT(`date`, '%d-%m-%Y') as mydt, 
                                                             date as orddate, 0 as ret FROM `$trandb` 
                                                             WHERE accode = '$maccode' AND `date`>='$mfrmdate' AND `date`<='$mtodate')
                                                             union all 
                                                             (SELECT *, DATE_FORMAT(`retdate`, '%d-%m-%Y') as mydt, retdate as orddate, 1 as ret FROM `$trandb` 
                                                                    WHERE accode = '$maccode' AND `retdate`>='$mfrmdate' AND `retdate`<='$mtodate' 
                                                                    and chqret = 'Y')") or die(mysqli_error($conn));

                                            $ctran1 = mysqli_query($conn, "SELECT * FROM `tranpart` ORDER BY `orddate`, vouchno") or die(mysqli_error($conn));

//                                        print table($ctran1);
                                            $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 chqret != 'Y'
                                                                 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";

                                                        //   browse($murl->vouchno." - SALE URL - ".$url);

                                                        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' and chqret != 'Y' 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' and chqret != 'Y'") 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";
                                            }



                                            echo "<tr>";

                                            echo "<th style=\"text-align:left;\" scope=\"col\" width=\"500\" colspan='9'><b>$mcustcode - $macname </b></th>";

                                            echo "</tr>";
                                            ?>

                                            <tr>

                                                <th align="left">Date<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                   

                                                <th align="left">Source<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                   

                                                <th align="left">Voucher<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                   

                                                <th align="left">Description<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                   



                                                <th align="right" style="text-align:right;">Debit<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                   

                                                <th align="right" style="text-align:right;">Credit<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                   

                                                <th align="right" style="text-align:right;">Balance<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                   

                                            </tr>

                                            <?php
                                            echo "<tr  class='clickableRow' href='$url'>";

                                            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 style=\"text-align:left;\" scope=\"col\"  width=\"35%\">$mdescrip</td>";

                                            echo "<td style=\"text-align:right;\" scope=\"col\" width=\"10%\">$mdebit</td>";

                                            echo "<td style=\"text-align:right;\" scope=\"col\" width=\"10%\">$mcredit</td>";

                                            echo "<td style=\"text-align:right;\" scope=\"col\" width=\"14%\">" . abs($totbal) . " " . $mdctot . "</td>";

                                            echo "</tr>";


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

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

                                                $mdebit = 0;
                                                $mcredit = 0;

                                                if ($mamount < 0) {
                                                    if ($ret == 0) {
                                                        $mdc = "DR";
                                                        $mdebit = abs($mamount);
                                                        $dtot = $dtot + $mdebit;
                                                    } else {
                                                        $mdc = "CR";
                                                        $mcredit = abs($mamount);
                                                        $ctot = $ctot + $mcredit;
                                                    }
                                                } else {
                                                    if ($ret == 0) {
                                                        $mdc = "CR";
                                                        $mcredit = abs($mamount);
                                                        $ctot = $ctot + $mcredit;
                                                    } else {
                                                        $mdc = "DR";
                                                        $mdebit = abs($mamount);
                                                        $dtot = $dtot + $mdebit;
                                                    }
                                                }
                                                if ($ret == 0) {
                                                    $totbal = $totbal + $mamount;
                                                } else {
                                                    $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 "SL";
                                                        $head = "Exam ";

                                                        if ($prnnarr == 1) {
                                                            $nqry1 = mysqli_query($conn, "SELECT billno, DATE_FORMAT(`billdt`, '%d-%m-%Y') as mbilldate,
                                                                                  examcode, `rem1`, `rem2` FROM `invmain` WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                            $fqry = mysqli_fetch_array($nqry1);
                                                            $examcode = $fqry['examcode'];
                                                            $billno = $fqry['billno'];
                                                            $billdt = $fqry['mbilldate'];
                                                            $rem1 = $fqry['rem1'];
                                                            $rem2 = $fqry['rem2'];

                                                            $eqry1 = mysqli_query($conn, "SELECT *  FROM `exam` WHERE code = '$examcode'") or die(mysqli_error($conn));
                                                            $efet = mysqli_fetch_array($eqry1);
                                                            $exam = $efet['name'];

                                                            $head = "Exam - " . $exam;
                                                            $mnarr = "";
                                                            $mchqdtl = "Bill No. : " . $billno . "  &nbsp;&nbsp;Dated : " . $billdt;
                                                        }
                                                        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, drawbank,
                                                                                 remark1, `remark2` FROM `bankrecp` WHERE vouchno='$mvouch'") or die(mysqli_error($conn));

                                                            $fqry = mysqli_fetch_array($nqry1);


                                                            $mchq = $fqry["chqno"];
                                                            $mchqdt = $fqry["mbilldate"];
                                                            $mnarr = $fqry["remark1"] . ' ' . $fqry["remark2"];
                                                            $drawbank = $fqry["drawbank"];

                                                            if ($ret == 0) {
                                                                $head = "Bank Receipt - " . "$drawbank";
                                                                $mchqdtl = "Cheque No. : " . + "$mchq" . " &nbsp;&nbsp;Dated : " . "$mchqdt";
                                                            } else {
                                                                $head = "****  Cheque Return *****";
                                                                $mchqdtl = "Cheque No. : " . + "$mchq". " &nbsp;&nbsp;Dated : " . "$mchqdt";
                                                            }
                                                        }
                                                        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, 

                                                                       concat(remark1, ' ', remark2) as   `narration` FROM `bankpay`

                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));

                                                            $nqry = mysqli_fetch_array($nqry1);

                                                            $mbill = $nqry["chqno"];

                                                            $mbilldt = $nqry["mbilldate"];

                                                            $mnarr = $nqry["narration"];


                                                            $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 chqno, DATE_FORMAT(`chqdate`, '%d-%m-%Y') as mbilldate, drawbank,
                                                                                 bankname, `remark1`, `remark2` FROM `cashrecp` WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                            $fqry = mysqli_fetch_array($nqry1);
                                                            $mchq = $fqry["chqno"];
                                                            $mchqdt = $fqry["mbilldate"];
                                                            $mnarr = $fqry["remark1"] . ' ' . $fqry["remark2"];
                                                            $drawbank = $fqry["drawbank"];

                                                            $head = "Cash Receipt ";
                                                            $mchqdtl = '';
                                                        }
                                                        break;

                                                    case "CP";

                                                        $head = "Cash Payment";
                                                        $url = "cashpay.php?vouchno=$mvouch&method=ledger";
                                                        if ($prnnarr == 1) {
                                                            $nqry1 = mysqli_query($conn, "SELECT concat(remark1, ' ', remark2) as `narration` FROM `cashpay`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));

                                                            $nqry = mysqli_fetch_array($nqry1);
                                                            $mnarr = $nqry["narration"];
                                                        }
                                                        break;

                                                    case "TR";

                                                        $head = "Contra / Transfer";
                                                        $url = "contra.php?vouchno=$mvouch&method=ledger";
                                                        if ($prnnarr == 1) {
                                                            $nqry1 = mysqli_query($conn, "SELECT `narration` FROM `contra`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));

                                                            $nqry = mysqli_fetch_array($nqry1);
                                                            $mnarr = $nqry["narration"];
                                                        }
                                                        break;

                                                    case "JV";

                                                        $head = "Journal Voucher";
                                                        break;

                                                    case "DG";
                                                        $head = "Discount Given";
                                                        break;

                                                    case "DR";
                                                        $head = "Discount Received";
                                                        break;

                                                    default :
                                                        $head = "Other";
                                                }
                                                

                                                echo "<tr  class='clickableRow' href='$url'>";
                                                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>";
                                                if ($prnnarr == 1) {
                                                    echo "<td style=\"text-align:left;\" scope=\"col\">$head<br>$mchqdtl<br>$mnarr</td>";
                                                } else {
                                                    echo "<td style=\"text-align:left;\" scope=\"col\">$head</td>";
                                                }
                                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"10%\">$mdebit</td>";
                                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"10%\">$mcredit</td>";
                                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"14%\">" . abs($totbal) . " " . $mdctot . "</td>";
                                                echo "</tr>";

//                                                if ($prnnarr == 1) {
//                                                    if (!empty($mchqdtl)) {
//                                                        echo "<tr>";
//                                                        echo "<td></td>";
//                                                        echo "<td></td>";
//                                                        echo "<td style=\"text-align:left;\" scope=\"col\" width=\"200\">$mchqdtl</td>";
//                                                        echo "<td></td>";
//                                                        echo "<td></td>";
//                                                        echo "<td></td>";
//                                                        echo "</tr>";
//                                                    }
//                                                    if (!empty($mnarr)) {
//                                                        echo "<tr>";
//                                                        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 "</tr>";
//                                                    }
//                                                }
                                            }

                                            echo "<tr>";
                                            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=\"8%\"></td>";
                                            echo "<td style=\"text-align:right;font-size:15px\" scope=\"col\" width=\"35%\"><strong>Total</strong></td>";
                                            echo "<td style=\"text-align:right;font-size:15px\" scope=\"col\" width=\"10%\">$dtot</td>";
                                            echo "<td style=\"text-align:right;font-size:15px\" scope=\"col\" width=\"10%\">$ctot</td>";
                                            if ($totbal < 0) {

                                                echo "<td style=\"text-align:right;font-size:15px\" scope=\"col\" width=\"14%\"><font color='red'><strong>" . abs($totbal) . " " . $mdctot . "</strong></font></td>";
                                            } else {

                                                echo "<td style=\"text-align:right;font-size:15px\" scope=\"col\" width=\"14%\"><font color='green'><strong>" . abs($totbal) . " " . $mdctot . "</strong></font></td>";
                                            }

                                            echo "</tr>";
                                        }
                                    }

                                    if ($dtype == 1) {

                                        echo '</tr>';
                                        echo "<tr>";
                                        echo "<td></td>";
                                        echo "<td align='right' style='font-size:15px'><strong>Total</strong></td>";
                                        echo "<td style=\"text-align:right;font-size:15px\" scope=\"col\" width=\"200\"><strong>" . abs($totbal) . " " . $mdctot . "</strong></td>";
                                        echo "<tr>";
                                    }
                                    ?>
                                </tbody>
                                <tfoot>
                                    <tr class="mySearchTr">
                                        <td colspan="9">
                                        </td>
                                    </tr>
                                </tfoot>
                            </table>
                        </div>
                    </div>
                </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"];
    browse($prnnarr);

    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 ($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;
            ############PARTY NAME####################

            $getbal = mysqli_query($conn, "SELECT SUM(amount) as amt FROM `$trandb` WHERE accode='$maccode' AND  date <= '$mtodate' and chqret != 'Y'") 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' and chqret != 'Y'
                                                                  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 chqret != 'Y'
                                                                 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' and chqret != 'Y' 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 .= "$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";

            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 "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, 
                                                 `narration` FROM `bankrecp`WHERE vouchno='$mvouch'") or die(mysqli_error($conn));

                            $nqry = mysqli_fetch_array($nqry1);
                            $mbill = $nqry["chqno"];
                            $mbilldt = $nqry["mbilldate"];
                            $mnarr = $nqry["narration"];
                            $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, 
                                                 `narration` FROM `bankpay`WHERE vouchno='$mvouch'") or die(mysqli_error($conn));

                            $nqry = mysqli_fetch_array($nqry1);
                            $mbill = $nqry["chqno"];
                            $mbilldt = $nqry["mbilldate"];
                            $mnarr = $nqry["narration"];
                            $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 `narration` FROM `cashrecp` WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                            $nqry = mysqli_fetch_array($nqry1);
                            $mnarr = $nqry["narration"];
                        }
                        break;

                    case "CP";
                        $head = "Cash Payment";
                        $url = "cashpay.php?vouchno=$mvouch&method=ledger";
                        if ($prnnarr == 1) {
                            $nqry1 = mysqli_query($conn, "SELECT `narration` FROM `cashpay` WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                            $nqry = mysqli_fetch_array($nqry1);
                            $mnarr = $nqry["narration"];
                        }
                        break;

                    case "TR";
                        $head = "Contra / Transfer";
                        $url = "contra.php?vouchno=$mvouch&method=ledger";
                        if ($prnnarr == 1) {
                            $nqry1 = mysqli_query($conn, "SELECT `narration` FROM `contra` WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                            $nqry = mysqli_fetch_array($nqry1);
                            $mnarr = $nqry["narration"];
                        }
                        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 .= " " . ",";
                        $contents .= " " . "\n";
                    }
                }
            }

            $contents .= " " . ",";
            $contents .= " " . ",";
            $contents .= " " . ",";
            $contents .= "Total" . ",";
            $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;
    }
}
?>        
