<?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";
$maindb = "accmast";
$ht = "height:25px";

mysqli_select_db($conn, $zdb);
if ($admin == 1) {
    $trandb = "actran";
}
if (isset($_POST["view"])) {
    $gcond = "";
    $accond = "";
    $stcond = "";
    $ctcond = "";
    $scond = "";

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

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

    if (!empty($_POST["staff"])) {
        $st = trim($_POST["staff"]);
        $sfcond = "AND `staff`='$st'";
        $getstaffname = mysqli_query($conn, "SELECT `name` FROM `staff` WHERE code='$st'")or die(mysqli_error($conn));
        $stname = mysqli_fetch_object($getstaffname);
        $mstaff = $stname->name;
    }

    $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 = "Summary";
        $dtype = 1;
    } else {
        $repname = "Detail";
        $dtype = 2;
    }

//    print "SELECT * FROM `$maindb` WHERE accode!='' $gcond $accond $ctcond $stcond  order by name";

    $acc = mysqli_query($conn, "SELECT * FROM `$maindb` WHERE accode!='' $gcond $accond $ctcond $stcond $sfcond order by name") or die(mysqli_error($conn));
    ?>
    <!DOCTYPE HTML>
    <html lang="en-US">    
        <head>
            <meta charset="UTF-8"><title><?php echo $flag . " " . $formname; ?></title>
            <meta name="msapplication-TileColor" content="#5bc0de"/>        
            <meta name="msapplication-TileImage" content="assets/img/metis-tile.png"/>        
            <link rel="stylesheet" href="assets/lib/bootstrap/css/bootstrap.css">  
            <link rel="stylesheet" href="assets/css/main.css"/>        
            <link rel="stylesheet" href="assets/lib/Font-Awesome/css/font-awesome.css"/>
            <link rel="stylesheet" href="assets/css/theme.css">
            <link rel="stylesheet" href="assets/lib/uniform/themes/default/css/uniform.default.css">
            <link rel="stylesheet" href="assets/lib/inputlimiter/jquery.inputlimiter.1.0.css">          
            <link rel="stylesheet" href="assets/lib/chosen/chosen.min.css">       
            <link rel="stylesheet" href="assets/lib/colorpicker/css/colorpicker.css">   
            <link rel="stylesheet" href="assets/lib/tagsinput/jquery.tagsinput.css">       
            <link rel="stylesheet" href="assets/lib/daterangepicker/daterangepicker-bs3.css">                
            <link rel="stylesheet" href="assets/lib/datepicker/css/datepicker.css">                    
            <link rel="stylesheet" href="assets/lib/timepicker/css/bootstrap-timepicker.min.css">        
            <link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.10.3/themes/flick/jquery-ui.css">   
                 <!--            <script type="text/javascript" src="js/jquery.js"></script>-->  
            <script type="text/javascript" src="js/jquery-1.7.min.js"></script>        
            <script type="text/javascript" src="js/bootstrap-3.1.1.min.js"></script>        
            <script>
                (function (i, s, o, g, r, a, m) {

                    i['GoogleAnalyticsObject'] = r;

                    i[r] = i[r] || function () {

                        (i[r].q = i[r].q || []).push(arguments)

                    }, i[r].l = 1 * new Date();

                    a = s.createElement(o), m = s.getElementsByTagName(o)[0];

                    a.async = 1;

                    a.src = g;

                    m.parentNode.insertBefore(a, m)

                })(window, document, 'script', '//www.google-analytics.com/analytics.js', 'ga');

                ga('create', 'UA-1669764-16', 'onokumus.com');

                ga('send', 'pageview');

                jQuery(document).ready(function ($) {

                    $('#ledger').on('click', '.close_ledger', refresh_ledger);

                    $('.clickableRow').click(function () {

                        var form_header = $(this).attr('id');

                        var file = $(this).attr("href");

                        $("#myModalLabel").html(form_header);

                        $('#form_loader').attr("src", file);

                        $('#myModal').on('show', function () {

                        });

                        jQuery.noConflict();

                        $('#myModal').modal('show');

                    });
                    $(".remove_record").click(function () {
                        $("#ysource").val(" ");
                        $("#yvouch").val(" ");
                        var Rid = $(this).attr('id');
                        var Rrel = $(this).attr('rel');
                        $("#ysource").val(Rid);
                        $("#yvouch").val(Rrel);
                        $("#confirmModal").modal('show');

                    });
                    $(".confirm").click(function () {
                        var Cid = this.id;
                        var Csource = $("#ysource").val();
                        var Crel = $("#yvouch").val();
                        if (Cid === "confirmYes") {
                            $.get('delete_record_from_ledger.php', {source: Crel, vouchno: Csource}, function (data) {
                                alert("Record Deleted Successfully");
                                location.reload();
                            });
                        } else {

                        }

                    });


                });

                function refresh_ledger() {
                    $('#form_loader').attr('src', 'about:blank');
                    location.reload();
                }

            </script>   

            <style> 

                .clickableRow {

                    cursor: pointer;

                }    

                @media (min-width: 992px) {    

                    .modal-lg {   

                        width: 90%;     

                    }  

                }      

                .modal-content {   

                    height: 900px; 

                }       

                .modal-body {  

                    height: 80%;   

                }          

                .modal-body >iframe {  

                    height: 100% !important;

                }     

            </style>     
<?php             include("includes/tabsuf.php");
 ?>
            <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

        </head>
        <div id="confirmModal" class="modal fade in" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
            <div class="modal-dialog">
                <div class="modal-content" style="height:201px;">

                    <div class="modal-header">
                        <a class="btn btn-default" data-dismiss="modal"style="float: right;"><span class="glyphicon glyphicon-remove"></span></a>
                        <h4 class="modal-title" id="myModalLabel"></h4>
                    </div>
                    <div class="modal-body text-center" style="height: auto;">
                        <h4>Confirm Delete the Record?</h4>
                    </div>
                    <div class="modal-footer">
                        <div class="btn-group">
                            <button class="btn btn-primary confirm" id="confirmYes">Yes</button>
                            <button class="btn btn-danger confirm" data-dismiss="modal" id="confirmNo">No</button>
                        </div>
                    </div>

                </div><!-- /.modal-content -->
            </div><!-- /.modal-dalog -->
        </div><!-- /.modal -->
        <body id="ledger">   
            <input type="hidden" id="ysource">
            <input type="hidden" id="yvouch">
            <!--<button class="btn btn-primary btn-lg" data-toggle="modal"  data-target="#myModal">   
        
            Launch demo modal                        </button>-->            <!-- Modal -->  

            <div class="modal fade bs-example-modal-lg" id="myModal" tabindex="-1" role="dialog"  aria-labelledby="myModalLabel" aria-hidden="true"> 

                <div class="modal-dialog modal-lg" style="width: 100%;height:100%">        

                    <div class="modal-content">      

                        <div class="modal-header">   

                            <button type="button" class="close close_ledger" data-dismiss="modal" aria-hidden="true">&times;</button>
                            <h4 class="modal-title" id="myModalLabel"></h4>
                        </div>                    
                        <div class="modal-body">
                            <iframe id="form_loader" src="" style="zoom:0.60;height:550px;" width="99.6%" height="250" frameborder="0"></iframe>
                        </div>                    
                        <div class="modal-footer">                        
                            <button type="button" class="close_ledger btn btn-danger" data-dismiss="modal">Close And Refresh Ledger</button>                    
                        </div>                
                    </div>                <!-- /.modal-content -->                            
                </div>            <!-- /.modal -->                        
            </div>        <!-- /.modal -->        
            <?php
            require("includes/dbconnect.php");

            $comp = mysqli_query($conn, "SELECT * FROM `company`") or die(mysql_errno());

            $zcomp = mysqli_fetch_assoc($comp);

            echo "<font size='4px' color=''><b>$zcomp[compname]</b></font><font size='2px' color=''><b> $zcomp[add1]. $zcomp[add2].  $zcomp[add3] </b></font><br />";

            //echo "<font size='2px' color=''><b> $zcomp[add1]. $zcomp[add2].  $zcomp[add3] </b></font><br />";

            echo "<b><font size='3px'>Date : " . date("M-d-Y") . "</b>&nbsp;&nbsp;&nbsp;";

            echo "<font size='2px'>(From Date - " . date("d-m-Y", strtotime($mfrmdate)) . ")&nbsp;&nbsp;&nbsp;(To Date &nbsp;&nbsp;&nbsp;&nbsp;-  " . date("d-m-Y", strtotime($mtodate)) . ")";

            echo "<br />";
            ?>

            <div class="row">

                <div class="col-lg-6" style="width:100%;">

                    <div class="box">

                        <header>

                            <h5><?php echo $formname; ?></h5>

                            <div class="toolbar">

                                <div class="btn-group">		  

                                    <div class="btn-group">



                                        <a href="#sortableTable" data-toggle="collapse" class="btn btn-default btn-sm minimize-box">

                                            <i class="fa fa-angle-up"></i>

                                        </a>



                                        <input type='button' name='submit' class='btn btn-sm btn-danger' onClick="window.location.href = '<?php echo $grid; ?>'" value="Exit"  value='Save'/> 

                                    </div>

                                </div>

                            </div>

                        </header>         



                        <div id="sortableTable" class="body collapse in">

                            <table id="" style = "width:100%;" class="table-bordered sortableTable responsive-table">
<!--<table class="table table-bordered sortableTable responsive-table">-->
                                <thead>

                                     <tr style = "height:10px;page-break-after: always">

                                        <?php if ($dtype == 1) { ?>
                                            <th align="left">Account Name<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>

                                            <?php
                                        }
                                        ?>

                                    </tr>

                                </thead>

                                <tbody>

                                    <?php
                                    mysqli_select_db($conn, $zdb);
                                    $totbal = 0;
                                    $url = '';
                                    $ctr = 1;

                                    while ($getac = mysqli_fetch_array($acc)) {
                                        $ctr++;
                                        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 `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) {

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

                                            $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 = "purchreturn.php?vouchno=$murl->vouchno&method=ledger";

                                                        break;



                                                    case "SL";

                                                        $head = "Sale";

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

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




                                            echo "<tr>";

                                            echo "<th style=\"text-align:left;\" scope=\"col\" width=\"500\" colspan='9'><b style='font-size:13px;'>" . strtoupper($macname) . "</b></th>";

                                            echo "</tr>";
                                            ?>
                                            <!--                                    </table>
                                                                                <table class="table table-bordered sortableTable responsive-table">-->
                                            <tr>
                                    </table>        
                                    <!--<table class="table table-bordered sortableTable responsive-table">-->
                                    <table id="" style="width:100%" class="table-bordered sortableTable responsive-table">
                                        <tr style="height:10px;">
                                            <th align="left"></th>                                   
                                            <th width="25%"><label style="font-size: 12px;">Date</label><i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                   
                                            <th align="left"><label style="font-size: 12px;">Source</label><i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                  
                                            <th align="left"><label style="font-size: 12px;">Voucher</label><i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                   
                                            <th align="left"><label style="font-size: 12px;">Description</label><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;"><label style="font-size: 12px;">Debit</label><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;"><label style="font-size: 12px;">Credit</label><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;"><label style="font-size: 12px;">Balance</label><i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                   

                                        </tr>

                                        <?php
                                        echo "<tr style='height:10px;' class='clickableRow' href='$url'>";
                                        echo "<td></td>";
                                        echo "<td style=\"text-align:left;\" scope=\"col\"  width=\"25%\">$vdate</td>";

                                        echo "<td style=\"text-align:left;\" scope=\"col\"  width=\"2%\"></td>";

                                        echo "<td style=\"text-align:left;\" scope=\"col\"  width=\"3%\">$mvouch</td>";

                                        echo "<td style=\"text-align:left;\" scope=\"col\"  width=\"40%\">$mdescrip</td>";

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

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

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

                                        echo "</tr>";




                                        while ($row = mysqli_fetch_assoc($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,

                                                                        `rem1`, `rem2` 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 Fresh";
                                                    $url = "debnote.php?vouchno=$mvouch&method=ledger";

                                                    break;

                                                case "PRU";

                                                    $head = "Purchase Return USed";
                                                    $url = "debused.php?vouchno=$mvouch&method=ledger";

                                                    break;


                                                case "SL";

                                                    $url = "sale.php?vouchno=$mvouch&method=ledger";
                                                    
                                                    $head = "Sale";

                                                    if ($prnnarr == 1) {

                                                        $mnarr = "";

                                                        $mchqdtl = "";
                                                    }

                                                    break;



                                                case "SR";

                                                    $head = "Sale Return Fresh";
                                                    $url = "crednote.php?vouchno=$mvouch&method=ledger";

                                                    break;

                                                case "SRU";

                                                    $head = "Sale Return Used";
                                                    $url = "credused.php?vouchno=$mvouch&method=ledger";

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

                                            echo "<tr>";

                                            echo "<td align='center'><a style='color:red;font-size: 15px;font-weight: bold;cursor:pointer;' rel='$msource' id='$mvouch' class='remove_record'>&times;</a></td>";
                                            echo "<td style=\"text-align:left;\" scope=\"col\"  width=\"25%\">$vdate</td>";

                                            echo "<td style=\"text-align:left;\" scope=\"col\"  width=\"2%\">$msource</td>";

                                            echo "<td style=\"text-align:left;\" scope=\"col\"  width=\"3%\">$mvouch</td>";

                                            echo "<td style=\"text-align:left;\" scope=\"col\"  width=\"30%\"  class=\"clickableRow\" href=\"$url\">$head</td>";

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

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

                                            echo "<td style=\"text-align:right;\" scope=\"col\" width=\"30%\">" . 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></td>";
                                        echo "<td style=\"text-align:left;\" scope=\"col\" width=\"25%\"></td>";
                                        echo "<td style=\"text-align:left;\" scope=\"col\" width=\"2%\"></td>";
                                        echo "<td style=\"text-align:left;\" scope=\"col\" width=\"3%\"></td>";
                                        echo "<td style=\"text-align:left;\" scope=\"col\" width=\"30%\"><strong>Total</strong></td>";
                                        echo "<td style=\"text-align:right;\" scope=\"col\" width=\"5%\">$dtot</td>";
                                        echo "<td style=\"text-align:right;\" scope=\"col\" width=\"5%\">$ctot</td>";

                                        if ($totbal < 0) {

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

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

                                        echo "<tr>";

                                        if ($mgrpcode == '009') {

                                            $dprov = (($totdiscprov * $mdiscprov) / 100);

                                            echo "<td colspan='4'>Discount Provision  :  $mdiscprov%     Amount :   $dprov</td>";

//                echo "<td>'Discount Provision  :  '</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">

                                                                                                                                    <hr style="border-top: 1px solid #dddddd;"/>

                                                                                                                                </td>  -->

                                        <td colspan="9">

                                        </td>

                                    </tr>

                                </tfoot>

                            </table>

                        </div>

                    </div>

                </div>

            </div>



            <script src="//ajax.googleapis.com/ajax/libs/jquery/2.0.3/jquery.min.js"></script>

            <script>window.jQuery || document.write('<script src="assets/lib/jquery.min.js"><\/script>')</script> 

            <script src="assets/lib/bootstrap/js/bootstrap.js"></script>

            <script src="//ajax.googleapis.com/ajax/libs/jqueryui/1.10.3/jquery-ui.min.js"></script>

            <script src="assets/lib/datatables/jquery.dataTables.js"></script>

            <script src="assets/lib/datatables/DT_bootstrap.js"></script>

            <script src="assets/lib/tablesorter/js/jquery.tablesorter.min.js"></script>

            <script src="assets/lib/touch-punch/jquery.ui.touch-punch.min.js"></script>

            <script src="assets/js/main.js"></script>

            <script>

                                            $(function () {

                                                metisTable();

                                                metisSortable();

                                            });

            </script>

        </body>

    </html>

    <?php
}
if (isset($_POST["export"])) {

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

    $maindb = "accmast";

    $gcond = "";
    $accond = "";
    $stcond = "";
    $ctcond = "";
    $scond = "";

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

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

    if (!empty($_POST["staff"])) {
        $st = trim($_POST["staff"]);
        $sfcond = "AND `staff`='$st'";
        $getstaffname = mysqli_query($conn, "SELECT `name` FROM `staff` WHERE code='$st'")or die(mysqli_error($conn));
        $stname = mysqli_fetch_object($getstaffname);
        $mstaff = $stname->name;
    }

    $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 = "Summary";
        $dtype = 1;
    } else {
        $repname = "Detail";
        $dtype = 2;
    }

//    print "SELECT * FROM `$maindb` WHERE accode!='' $gcond $accond $ctcond $stcond  order by name";

    $acc = mysqli_query($conn, "SELECT * FROM `$maindb` WHERE accode!='' $gcond $accond $ctcond $stcond $sfcond order by name") or die(mysqli_error($conn));
    $zdb = $_SESSION["zdbyear_" . $dbname];

//    require("includes/dbconnect.php");
//    mysqli_select_db($conn, $zdb);

    $totbal = 0;
    $url = '';
    $ctr = 1;

    $contents = "Account Name,Balance\n";

    while ($getac = mysqli_fetch_array($acc)) {
        $ctr++;
        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));

//            $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.= "Date,Party Name,Source,Voucher,Description,Debit,Credit,Balance\n";

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

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

                //browse("HERE");

                $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,
                                                                        `rem1`, `rem2` 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";
                        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.=$macname . ",";
                $contents.=$msource . ",";
                $contents.=$mvouch . ",";
                $contents.=$head . ",";
                $contents.=$mdebit . ",";
                $contents.=$mcredit . ",";
                $contents.=abs($totbal) . "" . $mdctot . "\n";


                if ($prnnarr == 1) {

                    if (!empty($mchqdtl)) {

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

                    if (!empty($mnarr)) {

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


                $contents.="" . ",";
                $contents.="" . ",";
                $contents.="" . ",";
                $contents.="" . ",";
                $contents.="Total" . ",";
                $contents.=$dtot . ",";
                $contents.=$ctot . "\n";

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

                if ($mgrpcode == '009') {

                    $dprov = (($totdiscprov * $mdiscprov) / 100);
                    $contents.="Discount Provision  :" . $ctot . "%     Amount" . ",";
                }
            }
        }

//        if ($dtype == 1) {
//
//            $contents.="Total" . ",";
//            $contents.=abs($totbal) . " " . $mdctot . "";
//        }
    }
    $contents = strip_tags($contents);

//header to make force download the file
    if ($dtype == 1) {
        header("Content-Disposition: attachment; filename=LedgerSummary" . date('d-m-Y') . ".csv");
        print $contents;
    } else {
        header("Content-Disposition: attachment; filename=LedgerDetail" . date('d-m-Y') . ".csv");
        print $contents;
    }
}
?>
