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

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

$formname = "LEDGER";
$grid = "ledger2.php";
$file = "ledger2.php";

$maindb = "accmast";
mysqli_select_db($conn, $zdb);

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

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

mysqli_select_db($conn, $zdb);

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

$trandb = "actran";

setlocale(LC_MONETARY, "en_IN");

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

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

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

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

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

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

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

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

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

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

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


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

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

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

    if (mysqli_num_rows($acc) <= 0) {
        browse("Record Not Found");
        echo "<script>window.close();</script>";
        return false;
    }
//print table($acc);
    include("includes/linkshead.php");
    ?>
    <style>
        .bakc{
            background-color: #2FBEF3;
        }
    </style>
    </head>
    <body>   
        <div class="container">                                                

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

                        if ($dtype < 0) {
                            mysqli_query($conn, "drop temporary table if exists acc") or die(mysqli_error($conn));
                            mysqli_query($conn, "create temporary table acc SELECT * FROM `$maindb` WHERE accode!='' $gcond $accond $ctcond $stcond  order by name") or die(mysqli_error($conn));
                            
                            mysqli_query($conn, "drop temporary table if exists mondata") or die(mysqli_error($conn));
                            mysqli_query($conn, "create temporary table mondata 
                                                 SELECT a.date, a.accode, month(a.date) as mon, b.grpcode, a.amount FROM `$trandb` a, acc b  
                                                 where a.date >= '$mfrmdate' and a.date <= '$mtodate' and a.retdate = '0000-00-00' 
                                                 and a.accode = b.accode ") or die(mysqli_error($conn));

                            
                            $getd = mysqli_query($conn, "SELECT * FROM `mondata` ") or die(mysqli_error($conn));
                            

//                            echo "<tr>";
//                            echo "<th style = 'background-color : #999999; width=50% ; align=left'>Month</th>";
//                            echo "<th style = 'background-color : #999999; width=10% ; text-align:right'>Opening</th>";
//                            echo "<th style = 'background-color : #999999; width=10% ; text-align:right'>Debit</th>";
//                            echo "<th style = 'background-color : #999999; width=10% ; text-align:right'>Credit</th>";
//                            echo "<th style = 'background-color : #999999; width=10% ; text-align:right'>Balance</th>";
//                            echo "</tr>";

                            $getmon_1 = mysqli_query($conn, "SELECT date, mon  FROM `mondata` group by mon order by date") or die(mysqli_error($conn));
                            while ($fetmon_1 = mysqli_fetch_array($getmon_1)) {

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

                                $monopn = 0;

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

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


                                $getdata_1 = mysqli_query($conn, "SELECT grpcode  FROM `mondata` group by grpcode order by grpcode") or die(mysqli_error($conn));
                                while ($fetdata_1 = mysqli_fetch_array($getdata_1)) {
                                    $grpcode_1 = $fetdata_1['grpcode'];

                                    $totop1 = 0;
                                    if ($dtype == 1) {
                                        $qryop1 = mysqli_query($conn, "SELECT openbal, dc FROM `accmast` where grpcode = '$grpcode_1'  ") or die(mysqli_error($conn));
                                        while ($pop1 = mysqli_fetch_array($qryop1)) {
                                            $open1 = $pop1['openbal'];
                                            $dc1 = $pop1['dc'];
                                            if ($dc1 == 'D') {
                                                $open1 = $open1 * -1;
                                            }
                                            $totop1 = $totop1 + $open1;
                                        }
                                    } else {
                                        $totop1 = $acopbal;
                                    }

                                    $totop2 = 0;
                                    $qryop2 = mysqli_query($conn, "SELECT sum(amount) as openamt FROM `actran` where date < '$mfrdate' and 
                                                       chqret != 'Y' and `retdate` = '0000-00-00' and `accode` in (select `accode` from `accmast` where 
                                                       `grpcode` = '$grpcode_1') group by accode") or die(mysqli_error($conn));
                                    while ($pop2 = mysqli_fetch_array($qryop2)) {
                                        $open2 = $pop2['openamt'];
                                        $totop2 = $totop2 + $open2;
                                    }
                                    $monopn_l = $totop1 + $totop2;

                                    if ($monopn_1 < 0) {
                                        $mdco_1 = "DR";
                                    } else {
                                        $mdco_1 = "CR";
                                    }

//                                    $getactran_1 = mysqli_query($conn, "SELECT sum(amount) as tamt FROM `actran` WHERE `date` >= '$mfrdate' and `date`<='$mtodate' 
//                                                              and chqret != 'Y' and `retdate` = '0000-00-00' and amount < 0 
//                                                              AND accode in (select accode from accmast where `grpcode`='$grpcode_1') ") or die(mysqli_error($conn));
//
//                                    $getactran2_1 = mysqli_query($conn, "SELECT sum(amount) as tamt2 FROM `actran` WHERE `date` >= '$mfrdate' and `date`<='$mtodate' 
//                                                              and chqret != 'Y' and `retdate` = '0000-00-00' and amount > 0 
//                                                              AND accode in (select accode from accmast where `grpcode`='$grpcode_1') ") or die(mysqli_error($conn));

                                    $getactran_1 = mysqli_query($conn, "SELECT sum(amount) as tamt FROM `actran` WHERE month(date) = '$mon' 
                                                              and chqret != 'Y' and `retdate` = '0000-00-00' and amount < 0 
                                                              AND accode in (select accode from accmast where `grpcode`='$grpcode_1') ") or die(mysqli_error($conn));

                                    $getactran2_1 = mysqli_query($conn, "SELECT sum(amount) as tamt2 FROM `actran` WHERE month(date) = '$mon'
                                                              and chqret != 'Y' and `retdate` = '0000-00-00' and amount > 0 
                                                              AND accode in (select accode from accmast where `grpcode`='$grpcode_1') ") or die(mysqli_error($conn));
                                    
                                    $mtran_1 = mysqli_fetch_array($getactran_1);
                                    $mondeb_1 = $mtran_1[tamt];

                                    $mtran2_1 = mysqli_fetch_array($getactran2_1);
                                    $moncrd_1 = $mtran2_1[tamt2];


                                    $monbal_1 = $monopn_1 + $mondeb_1 + $moncrd_1;

                                    if ($monbal_1 < 0) {
                                        $mdcmon_1 = "DR";
                                    } else {
                                        $mdcmon_1 = "CR";
                                    }


                                    $monopn_1 = abs($monopn_1);
                                    $mondeb_1 = abs($mondeb_1);
                                    $moncrd_1 = abs($moncrd_1);
                                    $monbal_1 = abs($monbal_1);

                                    echo "<tr>";
                                    echo "<td align='left'>$monname</td>";
                                    echo "<td width='10%' align='right' style='text-align:right;'>" . rajnumformat($monopn_1) . " " . $mdco_1 . "</td>";
                                    echo "<td width='10%' align='right' style='text-align:right;'>" . rajnumformat($mondeb_1) . "</td>";
                                    echo "<td width='10%' align='right' style='text-align:right;'>" . rajnumformat($moncrd_1) . "</td>";
                                    echo "<td width='10%' align='right' style='text-align:right;'>" . rajnumformat($monbal_1) . " " . $mdcmon_1 . "</td>";
                                    echo "</tr>";
                                }
                            }
                        } else {

                            $totbal = $totobal = $totdeb = $totcrd = 0;
                            while ($getac = mysqli_fetch_array($acc)) {

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

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


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

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


                                    $getop = mysqli_query($conn, "SELECT SUM(amount) as amt FROM `$trandb` WHERE  `accode`='$maccode' and date < '$mfrmdate' and retdate = '0000-00-00' and date < '$mfrmdate' ") or die(mysqli_error($conn));
                                    $fetop = mysqli_fetch_array($getop);
                                    $opamt = $fetop["amt"];
                                    $obal = $mopbal + $opamt;
                                    if ($obal < 0) {
                                        $odc = "DR";
                                    } else {
                                        $odc = "CR";
                                    }

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

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

                                    $getbal = mysqli_query($conn, "SELECT SUM(amount) as amt FROM `$trandb` where `accode`='$maccode' 
                                                       and date <= '$mtodate' and retdate = '0000-00-00' ") or die(mysqli_error($conn));
                                    $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 = 1;
                                        } else {
                                            $show = 0;
                                        }
                                    }

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

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

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

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

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

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

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

                                                $monopn = 0;

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

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


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

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

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

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

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

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

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

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

                                                echo "<tr>";
                                                echo "<td align='left'>$monname</td>";
                                                echo "<td width='10%' align='right' style='text-align:right;'>" . rajnumformat($monopn1) . " " . $mdco . "</td>";
                                                echo "<td width='10%' align='right' style='text-align:right;'>" . rajnumformat($mondeb1) . "</td>";
                                                echo "<td width='10%' align='right' style='text-align:right;'>" . rajnumformat($moncrd1) . "</td>";
                                                echo "<td width='10%' align='right' style='text-align:right;'>" . rajnumformat($monbal1) . " " . $mdcmon . "</td>";
                                                echo "</tr>";
                                            }
                                            echo "<tr>";
                                            echo "<td>&nbsp;
                                            </td>";
                                            echo "<td>&nbsp;
                                            </td>";
                                            echo "</tr>";
                                        }
                                    }
                                } else {
                                    $totbal = 0;
                                    $dtot = 0;
                                    $ctot = 0;
                                    $maccode = $getac["accode"];
                                    $macname = $getac["name"];
                                    $mopbal = $getac["openbal"];
                                    $mopdc = $getac["dc"];
                                    if ($mopdc == "D") {
                                        $mopbal = $mopbal * - 1;
                                    }
                                    $ctran1 = mysqli_query($conn, "SELECT *, DATE_FORMAT(`date`, '%d-%m-%Y') as mydt FROM `$trandb`
                                            WHERE accode = '$maccode' AND `date`>='$mfrmdate' AND `date`<='$mtodate'
                                            and retdate = '0000-00-00'
                                            ORDER BY `date`, vouchno") or die(mysqli_error($conn));

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

                                    while ($murl = mysqli_fetch_object($curl)) {
                                        switch ($murl->source) {
                                            case "PU";
                                                $head = "Purchase";
                                                $url = "purchase.php?mode=edit&vouchno = $murl->vouchno&method = ledger";
                                                break;
                                            case "PUPK";
                                                $head = "Purchase Packing";
                                                $url = "purchase_mi.php?mode=edit&type=P&vouchno = $murl->vouchno&method = ledger";
                                                break;
                                            case "PURW";
                                                $head = "Purchase Raw Material";
                                                $url = "purchase_mi.php?mode=edit&type=P&vouchno = $murl->vouchno&method = ledger";
                                                break;
                                            case "PR";
                                                $head = "Purchase Return";
                                                $url = "debnote.php??mode=edit&vouchno = $murl->vouchno&method = ledger";
                                                break;
                                            case "SL";
                                                $head = "Sale";
                                                $url = "sale.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' and `retdate` = '0000-00-00' ") or die(mysqli_error($conn));
                                    $getamt = mysqli_fetch_array($getbal);
                                    $amt = $getamt["amt"];
                                    $mobal = $mopbal + $amt;
                                    $mdebit = 0;
                                    $mcredit = 0;
                                    if ($mobal < 0) {
                                        $mdc = "DR";
                                        $mdebit = abs($mobal);
//                                    $dtot = $dtot + $mdebit;
//                                    $dtot = $dtot + $mdebit;
                                    } else {
                                        $mdc = "CR";
                                        $mcredit = abs($mobal);
//                                    $ctot = $ctot + $mcredit;
                                    }
                                    $vdate1 = strtotime($mfrmdate);
                                    $vdate = date('d-m-Y', $vdate1);
                                    $mvouch = "";
                                    $mdescrip = "Opening Balance";
                                    $totbal = $totbal + $mobal;
                                    if ($totbal < 0) {
                                        $mdctot = "DR";
                                    } else {
                                        $mdctot = "CR";
                                    }
                                    $getbal = mysqli_query($conn, "SELECT SUM(amount) as amt FROM `$trandb` WHERE accode='$maccode' AND 
                                                      date <= '$mtodate' and `retdate` = '0000-00-00' ") or die(mysqli_error($conn));
                                    $getamt = mysqli_fetch_array($getbal);
                                    $amt = $getamt["amt"];
                                    $mbal = $mopbal + $amt;
                                    if ($mbal < 0) {
                                        $mdc = "DR";
                                    } else {
                                        $mdc = "CR";
                                    }
                                    echo "<tr style='background-color:#26A69A'>";
                                    echo "<th style=\"text-align:left;\" scope=\"col\" width=\"500\" colspan='7'><b>" . strtoupper($macname) . "</b></th>";
                                    echo "<th style=\"text-align:right;\" scope=\"col\" width=\"500\" colspan='1'><b>" . abs($mbal) . $mdc . "</b></th>";
                                    echo "</tr>";
                                    ?>
                                    <tr style="color:black;" class="bakc">
                                        <th align="left">Date</th>                                   
                                        <th align="left">Source</th>                                   
                                        <th align="left">Voucher</th>                                   
                                        <th align="left" colspan="2">Description</th>                                   
                                        <th align="right" style="text-align:right;">Debit</th>                                   
                                        <th align="right" style="text-align:right;">Credit</th>                                   
                                        <th align="right" style="text-align:right;">Balance</th>                                   
                                    </tr>
                                    <?php
                                    $totbal1 = abs($totbal);
                                    echo "<tr  class='' href='$url' style='background-color:white'>";
                                    echo "<td style=\"text-align:left;\" scope=\"col\"  width=\"15%\">$vdate</td>";
                                    echo "<td style=\"text-align:left;\" scope=\"col\"  width=\"4%\"></td>";
                                    echo "<td style=\"text-align:left;\" scope=\"col\"  width=\"8%\">$mvouch</td>";
                                    echo "<td colspan='2' style=\"text-align:left;\" scope=\"col\"  width=\"35%\">$mdescrip</td>";
                                    echo "<td style=\"text-align:right;\" scope=\"col\" width=\"10%\">" . number_format($mdebit, 2) . "</td>";
                                    echo "<td style=\"text-align:right;\" scope=\"col\" width=\"10%\">" . number_format($mcredit, 2) . "</td>";
                                    echo "<td style=\"text-align:right;\" scope=\"col\" width=\"14%\">" . number_format($totbal1, 2) . " " . $mdctot . "</td>";
                                    echo "</tr>";

                                    if ($mcredit > 0) {
                                        $opening = $mcredit;
                                    } else {
                                        $opening = $mdebit;
                                    }
//                                $totbal = 0;
                                    while ($row = mysqli_fetch_array($ctran1)) {
                                        $vdate = $row["mydt"];
                                        $msource = $row["source"];
                                        $mvouch = $row["vouchno"];
                                        $mamount = $row["amount"];
                                        $mgrpcode = $getac["grpcode"];
                                        $mdiscprov = $getac["discount"];
                                        $totdiscprov = 0;
                                        $mdebit = 0;
                                        $mcredit = 0;
                                        if ($mamount < 0) {
                                            $mdc = "DR";
                                            $mdebit = abs($mamount);
                                            $dtot = $dtot + $mdebit;
                                        } else {
                                            $mdc = "CR";
                                            $mcredit = abs($mamount);
                                            $ctot = $ctot + $mcredit;
                                        }
                                        $totbal = $totbal + $mamount;
                                        if ($totbal < 0) {
                                            $mdctot = "DR";
                                        } else {
                                            $mdctot = "CR";
                                        }
                                        $mdescrip = "";
                                        $mchqdtl = "";
                                        $mnarr = "";
                                        switch ($msource) {
                                            case "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 "PUPK";
                                                $head = "Purchase Packing";
                                                $url = "purchase.php?mode=edit&type=P&vouchno=$mvouch&method=ledger";
//                                            $totdiscprov = $totdiscprov + $mamount;
                                                if ($prnnarr == 1) {

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

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

                                                    $nqry1 = mysqli_query($conn, "SELECT chqno, DATE_FORMAT(`chqdate`, '%d-%m-%Y') as mbilldate, 
                                                                       `remark1`, remark2 FROM `bankrecp`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                                                    $nqry = mysqli_fetch_array($nqry1);
                                                    $mbill = $nqry["chqno"];

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

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

                                            if (!empty($mnarr)) {

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

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

                                $tcolr = 'color:red';

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

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

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



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


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


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

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


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

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

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


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

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

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

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

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


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


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


    $totbal = $totobal = $totdeb = $totcrd = 0;
    while ($getac = mysqli_fetch_array($acc)) {

        if ($dtype < 2) {

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

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

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


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


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

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

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

            $getbal = mysqli_query($conn, "SELECT SUM(amount) as amt FROM `$trandb` where `accode`='$maccode' 
                                                       and date <= '$mtodate' and retdate = '0000-00-00' ") or die(mysqli_error($conn));
            $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;
                }
            }

            $obal1 = abs($obal);
            $mbal1 = abs($mbal);
            if ($show == 1) {

                $contents .= $acname . ",";
                $contents .= $obal1 . "," . $odc . ",";
                $contents .= $tramtdr . ",";
                $contents .= $tramtcr . ",";
                $contents .= $mbal1 . "," . $mdc . "\n";

                $totobal = $totobal + $obal;

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

                $totbal = $totbal + $mbal;
            }

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

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

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

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

                        $monopn = 0;

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

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


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

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


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

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

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

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

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

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

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

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

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

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

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


            $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);
            } else {
                $mdc = "CR";
                $mcredit = abs($mobal);
            }

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

            $mvouch = "";

            $mdescrip = "Opening Balance";

            $totbal = $totbal + $mobal;

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

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

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

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

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

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

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

                $mdebit = 0;
                $mcredit = 0;

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

                $totbal = $totbal + $mamount;

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


                $mdescrip = "";
                $mchqdtl = "";
                $mnarr = "";
                $mdescrip = "";
                $mchqdtl = "";
                $mnarr = "";
                switch ($msource) {
                    case "PU";
                        $head = "Purchase";
                        $url = "purchase.php?vouchno=$mvouch&method=ledger";
                        $totdiscprov = $totdiscprov + $mamount;
                        if ($prnnarr == 1) {

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

                            $nqry1 = mysqli_query($conn, "SELECT chqno, DATE_FORMAT(`chqdate`, '%d-%m-%Y') as mbilldate, 
                                                                       `remark1`, remark2 FROM `bankrecp`
                                                                       WHERE vouchno='$mvouch'") or die(mysqli_error($conn));
                            $nqry = mysqli_fetch_array($nqry1);
                            $mbill = $nqry["chqno"];

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

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

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

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

                    if (!empty($mnarr)) {

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

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

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

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

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

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


    $contents = strip_tags($contents);

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

