<?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 = "tenledger.php";
$file = "tenledger.php";
$maindb = "accmast";

mysqli_select_db($conn, $zdb);

$trandb = "actran";

if (isset($_POST["view"])) {
    $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["prinnarr"];

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

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

    $acc = mysqli_query($conn, "SELECT * FROM `$maindb` WHERE grpcode!='001' and grpcode !='002' and grpcode = '042' $gcond $accond $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: #78909C;
        }
    </style>


    </head>

    <body id="ledger">   
        <div class="row">
            <!--<div class="col-lg-6">-->
            <?php
//                    include ("includes/head_rep.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[name]</font></b></font><br />";
//                echo "<b><font color=''>$repname &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
//                &nbsp;&nbsp;&nbsp;&nbsp;<font size='3px'>Date : " . date("M-d-Y") . "</b><br />";
//                echo "From Date - " . date("d-m-Y", strtotime($mfrmdate)) . "<br/>To Date &nbsp;&nbsp;&nbsp;&nbsp;-  " . date("d-m-Y", strtotime($mtodate)) . "<br/>";
            ?>
            <!--</div>-->
            <!--            <div class="col-lg-6">
                            <label class="label-control marg">Print - </label>
                            <label class="label-control marg"><?php Echo $formname; ?></label>
                        </div>-->
            <!--            <div class="col-lg-1" style="float: right;">
                            <a href="<?= $file ?>" class="btn btn-primary">GO Back</a>
                            <a onclick="window.close()" class="btn btn-primary">GO Back</a>
                        </div>-->
        </div>
        <?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]</font></b></font><br />";
//
//        echo "<font size='2px' color=''><b> $zcomp[add1]. $zcomp[add2].  $zcomp[add3] </b></font><br /><br />";
//
//        echo "<b><font color=''>$repname &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
//
//                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
//
//                &nbsp;&nbsp;&nbsp;&nbsp;<font size='3px'>Date : " . date("M-d-Y") . "</b><br />";
//
//        echo "From Date - " . date("d-m-Y", strtotime($mfrmdate)) . "<br/>To Date &nbsp;&nbsp;&nbsp;&nbsp;-  " . date("d-m-Y", strtotime($mtodate)) . "<br/>";
//
//        echo "<br />";
        ?>

        <div class="row"> 

            <div class="col-lg-10" style="padding-left:25%;">
                <?php include ("includes/head_rep.php"); ?>
            </div>
        </div>
        <div class="row" style="padding-left:25%;">
            <div class="col-lg-8     text-center" style="width:78%;">

                <div class="box">

                    <!--                    <header>
                    
                                            <h5><?php // echo $formname;         ?></h5>
                                        </header>         -->

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

                        <table class="table table-bordered sortableTable responsive-table">

                            <thead>

                                <tr>

                                    <?php if ($dtype == 1) { ?>

                                        <th align="left">Account Name</th>
                                        <th align="right" style="text-align:right;">Balance</th>

                                        <?php
                                    }
                                    ?>

                                </tr>

                            </thead>

                            <tbody>

                                <?php
                                mysqli_select_db($conn, $zdb);

                                $totbal = 0;

                                $url = '';



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

                                    //  browse("GAYA");

                                    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"];
                                        $mgrpcode = $getac["grpcode"];

                                        $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));

//                                        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'
                                                                 ORDER BY `date`, vouchno") or die(mysqli_error($conn));



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



                                            switch ($murl->source) {



                                                case "PU";

                                                    $head = "Purchase";

                                                    $url = "purch.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 "SLTX";

                                                    $head = "Sale - GST";

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

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

                                                    break;

                                                case "PUTX";

                                                    $head = "Purchase - GST";

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

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

                                                    break;

                                                case "BRBP";

                                                    $head = "Transfer - Customer to Karigar";

                                                    $url = "custtokari.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' ORDER BY date, vouchno") or die(mysqli_error($conn));



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

                                        $mobal = $mopbal + $oamt;

                                        $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>" . strtoupper($macname) . "</b></th>";

                                        echo "</tr>";
                                        ?>

                                        <tr>

                                            <th align="left">Date</th>                                   

                                            <th align="left">Source</th>                                   

                                            <th align="left">Voucher</th>                                   

                                            <th align="left">Description</th>                                   



                                            <th align="right" style="text-align:right;">Debit</th>                                   

                                            <th align="right" style="text-align:right;">Credit</th>                                   

                                            <th align="right" style="text-align:right;">Balance</th>                                   

                                        </tr>

                                        <?php
                                        echo "<tr  class='clickable-row' 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%\"><a href='$url' target='_blank'>$mvouch</a></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"];


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

                                            $mdebit = 0;
                                            $mcredit = 0;

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

                                            $totbal = $totbal + $mamount;

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


                                            $mdescrip = "";

                                            $mchqdtl = "";

                                            $mnarr = "";


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



                                                case "PU";

                                                    $head = "Purchase";

                                                    $url = "purch.php?vouchno=$mvouch&mode=edit";

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

                                                    $head = "Sale - GST";

                                                    if ($prnnarr == 1) {

                                                        $mnarr = "";

                                                        $mchqdtl = "";
                                                    }

                                                    break;

                                                case "PUTX";

                                                    $head = "Purchase - GST";

                                                    if ($prnnarr == 1) {

                                                        $mnarr = "";

                                                        $mchqdtl = "";
                                                    }

                                                    break;

                                                case "BRBP";

                                                    $head = "Transfer - Customer to Karigar";


                                                    $nqry11 = mysqli_query($conn, "SELECT party, karigar,acnum FROM `custtokari`

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

                                                    $n1qry = mysqli_fetch_array($nqry11);



                                                    $party = $n1qry["party"];
                                                    $karigar = $n1qry["karigar"];
                                                    $acnum = $n1qry["acnum"];
                                                    if ($mgrpcode == '010') {
                                                        $getname = mysqli_query($conn, "SELECT name FROM `accmast`
                                                                            WHERE accode='$karigar'") or die(mysqli_error($conn));
                                                    } else {
                                                        $getname = mysqli_query($conn, "SELECT name FROM `accmast`
                                                                            WHERE accode='$party'") or die(mysqli_error($conn));
                                                    }


                                                    $fetname = mysqli_fetch_array($getname);
                                                    $showname = $fetname['name'];
                                                    $head = $head . '( ' . $showname . '-' . $acnum . ' )';
                                                    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, 

                                                                       narr1 as `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, 

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

                                                    $head = "Rent Bill - Residential";
                                                    break;
                                                case "RCM";

                                                    $head = "Rent Bill - Coommercial ";
                                                    break;
                                                case "CR";

                                                    $head = "Cash Receipt";

                                                    $url = "cashrecp.php?vouchno=$mvouch&mode=edit";

                                                    if ($prnnarr == 1) {

                                                        $nqry1 = mysqli_query($conn, "SELECT narr1 as  `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  narr1 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='clickable-row' 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>";

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

                                                    echo "<td></td>";

                                                    echo "<td></td>";

                                                    echo "</tr>";
                                                }

                                                if (!empty($mnarr)) {

                                                    echo "<tr>";

                                                    echo "<td></td>";

                                                    echo "<td></td>";


                                                    echo "<td></td>";
                                                    echo "<td style=\"text-align:left;\" scope=\"col\" width=\"200\">$mnarr</td>";

                                                    echo "<td></td>";

                                                    echo "<td></td>";



                                                    echo "</tr>";
                                                }
                                            }
                                        }

                                        echo "<tr style='background-color:white'>";
                                        echo "<td style=\"text-align:left;\" scope=\"col\" width=\"15%\"></td>";
                                        echo "<td style=\"text-align:left;\" scope=\"col\" width=\"4%\"></td>";
                                        echo "<td style=\"text-align:left;\" scope=\"col\" width=\"4%\"></td>";

                                        echo "<td style=\"text-align:right;\" scope=\"col\" width=\"32%\" ><strong>Opening : " . round(abs($mobal)) . "</strong>";
                                        echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Total</td>";
                                        echo "<td style=\"text-align:right;\" scope=\"col\" width=\"10%\">" . (round($dtot)) . "</td>";
                                        echo "<td style=\"text-align:right;\" scope=\"col\" width=\"10%\">" . (round($ctot)) . "</td>";
                                        if ($totbal < 0) {
                                            echo "<td style=\"text-align:right;\" scope=\"col\" width=\"14%\"><font color='red'><strong>" . (round(abs($totbal))) . " " . $mdctot . "</strong></font></td>";
                                        } else {
                                            echo "<td style=\"text-align:right;\" scope=\"col\" width=\"14%\"><font color='green'><strong>" . (round(abs($totbal))) . " " . $mdctot . "</strong></font></td>";
                                        }
                                        echo "</tr>";



                                        /*                                            echo "<tr>";

                                          if ($mgrpcode == '009') {

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

                                          echo "<td colspan='4'>Discount Provision  :  $mdiscprov%     Amount :   $dprov</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>



        <?php include("includes/linksfoot.php"); ?>


    </body>

    </html>

    <?php
}

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

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

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

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



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

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

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



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

        $station = trim($_POST["cmbstation"]);

        $stcond = " AND `station`= '$station' ";
    }



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

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

        $ctcond = " AND `city`='$city'";
    }



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

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



    if ($mfrmdate > $mtodate) {

        browse("To Date Cannot Be Less Than From Date");

        header("refresh: 1; ledger.php");

        exit();
    }



    $prnzero = $_POST["zerobal"];

    $prnnarr = $_POST["prinarr"];



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

        $repname = "Ledger - Summary";

        $dtype = 1;
    } else {

        $repname = "Ledger - Detail";

        $dtype = 2;
    }



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

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




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



    mysqli_select_db($conn, $zdb);

    $totbal = 0;

    $url = '';



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



        if ($dtype == 1) {


            $maccode = $getac["accode"];

            $mopbal = $getac["openbal"];

            $mopdc = $getac["dc"];

            if ($mopdc == "D") {

                $mopbal = $mopbal * - 1;
            }



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



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

            $zname = mysqli_fetch_object($getpartyname);

            $mname = $zname->name;

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





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

            $getamt = mysqli_fetch_array($getbal);

            $amt = $getamt["amt"];



            $mbal = $mopbal + $amt;



            if ($mbal < 0) {

                $mdc = "DR";
            } else {

                $mdc = "CR";
            }

            $show = 1;

            if ($prnzero == 1) {

                $show = 1;
            } else {

                if ($mbal == 0) {

                    $show = 0;
                }
            }

            if ($show == 1) {

                $contents .= $mname . ",";
                $contents .= abs($mbal) . " " . $mdc . "\n";

                $totbal = $totbal + $mbal;
            }

            if ($totbal < 0) {

                $mdctot = "DR";
            } else {

                $mdctot = "CR";
            }
        } else {

            /** Detail Part* */
            $totbal = 0;

            $dtot = 0;

            $ctot = 0;

            $maccode = $getac["accode"];

            $macname = $getac["name"];

            $mopbal = $getac["openbal"];

            $mopdc = $getac["dc"];

            if ($mopdc == "D") {

                $mopbal = $mopbal * - 1;
            }



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


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



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



                switch ($murl->source) {



                    case "PU";

                        $head = "Purchase";

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

                        break;



                    case "PR";

                        $head = "Purchase Return";

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

                        break;



                    case "SL";

                        $head = "Sale";

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



                        break;



                    case "SR";

                        $head = "Sale Return";

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

                        break;



                    case "BR";

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

                        break;



                    case "BP";

                        $head = "Bank Payment";

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

                        break;



                    case "CR";

                        $head = "Cash Receipt";

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

                        break;



                    case "CP";

                        $head = "Cash Payment";

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

                        break;



                    case "TR";

                        $head = "Contra / Transfer";

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

                        break;



                    case "JV";

                        $head = "Journal Voucher";

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

                        break;



                    case "DG";

                        $head = "Discount Given";



                        break;



                    case "DR";

                        $head = "Discount Received";

                        break;



                    default :

                        $head = "Other";
                }
            }





            $rtran1 = mysqli_query($conn, "SELECT *, DATE_FORMAT(`retdate`, '%d-%m-%Y') as mydt FROM `actran` WHERE

                                                           retdate >= '$mfrmdate' AND retdate <= '$mtodate' AND

                                                           accode = '$maccode' ORDER BY date, vouchno") or die(mysqli_error($conn));



            $getbal = mysqli_query($conn, "SELECT SUM(amount) as amt FROM `actran` WHERE accode='$maccode' AND  date < '$mfrmdate'") or die(mysqli_error($conn));

            $getamt = mysqli_fetch_array($getbal);

            $amt = $getamt["amt"];

            $mobal = $mopbal + $amt;



            $mdebit = 0;

            $mcredit = 0;

            if ($mobal < 0) {

                $mdc = "DR";

                $mdebit = abs($mobal);

                $dtot = $dtot + $mdebit;
            } else {

                $mdc = "CR";

                $mcredit = abs($mobal);

                $ctot = $ctot + $mcredit;
            }



            $vdate1 = strtotime($mfrmdate);

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

            $mvouch = "";

            $mdescrip = "Opening Balance";

            $totbal = $totbal + $mobal;

            if ($totbal < 0) {

                $mdctot = "DR";
            } else {

                $mdctot = "CR";
            }


            $contents .= strtoupper($macname) . "\n";


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

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


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


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

                $mdebit = 0;
                $mcredit = 0;

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

                $totbal = $totbal + $mamount;

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


                $mdescrip = "";

                $mchqdtl = "";

                $mnarr = "";


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



                    case "PU";

                        $head = "Purchase";

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

                        $totdiscprov = $totdiscprov + $mamount;



                        if ($prnnarr == 1) {

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

                                                                        `remark1`, `remark2` FROM `purmain`

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

                            $nqry = mysqli_fetch_array($nqry1);



                            $mbill = $nqry["billno"];

                            $mbilldt = $nqry["mbilldate"];

                            $mnarr = trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);



                            $mchqdtl = "Bill No. : " . + trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                        }

                        break;


                    case "PR";

                        $head = "Purchase Return";

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

                        $totdiscprov = $totdiscprov + $mamount;



                        if ($prnnarr == 1) {

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

                                                                        `remark1`, `remark2` FROM `debmain`

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

                            $nqry = mysqli_fetch_array($nqry1);



                            $mbill = $nqry["invno"];

                            $mbilldt = $nqry["mbilldate"];

                            $mnarr = trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);



                            $mchqdtl = "Bill No. : " . + trim($mbill) . " &nbsp;&nbsp;Dated : " . $mbilldt;
                        }

                        break;







                    case "SL";

                        $head = "Sale";

                        if ($prnnarr == 1) {

                            $mnarr = "";

                            $mchqdtl = "";
                        }

                        break;



                    case "SR";

                        $head = "Sale Return";



                        break;



                    case "BR";

                        $head = "Bank Receipt";

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

                        if ($prnnarr == 1) {

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

                                                                       `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 .= " " . "\n";
                    }
                }
            }

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

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

