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

$zdb = $_SESSION["zdbyear_" . $dbname];
$admin = $_SESSION["admin_" . $dbname];
$formname = "LEDGER";
$grid = "ledger.php";
$maindb = "accmast";
$ht = "height:25px";

mysqli_select_db($conn, $zdb);
if ($admin == 1) {
    $trandb = "actran";
} else {
    $trandb = "actran";
}
if (isset($_POST["view"])) {
    $accond = "";
    $stcond = "";
    $ctcond = "";
    $scond = "";
    if (!empty($_POST["acname"])) {
        $accode = trim($_POST["acname"]);
        $accond = "AND `accode`='$accode'";
    }

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

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

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

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

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

    $prnzero = $_POST["zerobal"];
    $prnnarr = $_POST["prinarr"];

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

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

    $acc = mysqli_query($conn, "SELECT * FROM `$maindb` WHERE accode!='' $accond $ctcond $stcond $sfcond order by name") or die(mysqli_error($conn));
    include("includes/linkshead.php");
    ?>
    <style>
        th {
            background-color: #1ABC9C;
        }
    </style>
    </head>
    <body id="ledger">   
        <input type="hidden" id="ysource">
        <input type="hidden" id="yvouch">   
        <?php
        require("includes/dbconnect.php");
        ?>
        <div class="container">
            <?php include("includes/head_rep.php"); ?>
            <div class="row">
                <div class="col-lg-6" style="width:100%;">
                    <div class="box">
                        <div id="sortableTable" class="body collapse in">

                            <table style = "width:100%;" class="table table-condensed responsive-table">
                                <thead>
                                    <tr style = "height:10px;page-break-after: always">
                                        <?php if ($dtype == 1) { ?>
                                            <th align="left">Account Name<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                            <th align="right" style="text-align:right;">Balance<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                            <?php
                                        }
                                        ?>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                    mysqli_select_db($conn, $zdb);
                                    $totbal = 0;
                                    $url = '';
                                    $ctr = 1;

                                    while ($getac = mysqli_fetch_array($acc)) {
                                        $ctr++;
                                        if ($dtype == 1) {

                                            echo '<tr>';

                                            $maccode = $getac["accode"];

                                            $mopbal = $getac["openbal"];

                                            $mopdc = $getac["dc"];

                                            if ($mopdc == "D") {

                                                $mopbal = $mopbal * - 1;
                                            }



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



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

                                            $zname = mysqli_fetch_object($getpartyname);

                                            $mname = $zname->name;

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





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

                                            $getamt = mysqli_fetch_array($getbal);

                                            $amt = $getamt["amt"];



                                            $mbal = $mopbal + $amt;



                                            if ($mbal < 0) {

                                                $mdc = "DR";
                                            } else {

                                                $mdc = "CR";
                                            }

                                            $show = 1;

                                            if ($prnzero == 1) {

                                                $show = 1;
                                            } else {

                                                if ($mbal == 0) {

                                                    $show = 0;
                                                }
                                            }

                                            if ($show == 1) {

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

                                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"200\">" . abs($mbal) . " " . $mdc . "</td>";

                                                $totbal = $totbal + $mbal;
                                            }

                                            if ($totbal < 0) {

                                                $mdctot = "DR";
                                            } else {

                                                $mdctot = "CR";
                                            }
                                        } else {

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

                                            $dtot = 0;

                                            $ctot = 0;

                                            $maccode = $getac["accode"];

                                            $macname = $getac["name"];

                                            $mopbal = $getac["openbal"];

                                            $mopdc = $getac["dc"];

                                            if ($mopdc == "D") {

                                                $mopbal = $mopbal * - 1;
                                            }



                                            $ctran1 = mysqli_query($conn, "SELECT *, DATE_FORMAT(`date`, '%d-%m-%Y') as mydt FROM `$trandb` WHERE

                                                            accode = '$maccode' AND `date`>='$mfrmdate' AND `date`<='$mtodate'

                                                           ORDER BY `date`, vouchno") or die(mysqli_error($conn));



                                            $curl = mysqli_query($conn, "SELECT *, DATE_FORMAT(`date`, '%d-%m-%Y') as mydt FROM `$trandb` WHERE

                                                            accode = '$maccode' AND `date`>='$mfrmdate' AND `date`<='$mtodate'

                                                           ORDER BY `date`, vouchno") or die(mysqli_error($conn));



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



                                                switch ($murl->source) {



                                                    case "PU";

                                                        $head = "Purchase";

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

                                                        break;



                                                    case "PR";

                                                        $head = "Purchase Return";

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

                                                        break;



                                                    case "SL";

                                                        $head = "Sale";

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

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

                                                        break;



                                                    case "SR";

                                                        $head = "Sale Return";

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

                                                        break;



                                                    case "BR";

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

                                                        break;



                                                    case "BP";

                                                        $head = "Bank Payment";

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

                                                        break;



                                                    case "CR";

                                                        $head = "Cash Receipt";

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

                                                        break;



                                                    case "CP";

                                                        $head = "Cash Payment";

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

                                                        break;



                                                    case "TR";

                                                        $head = "Contra / Transfer";

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

                                                        break;



                                                    case "JV";

                                                        $head = "Journal Voucher";

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

                                                        break;



                                                    case "DG";

                                                        $head = "Discount Given";



                                                        break;



                                                    case "DR";

                                                        $head = "Discount Received";

                                                        break;



                                                    default :

                                                        $head = "Other";
                                                }
                                            }





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

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

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



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



                                            $mdebit = 0;

                                            $mcredit = 0;

                                            if ($mobal < 0) {

                                                $mdc = "DR";

                                                $mdebit = abs($mobal);

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

                                                $mdc = "CR";

                                                $mcredit = abs($mobal);

                                                $ctot = $ctot + $mcredit;
                                            }



                                            $vdate1 = strtotime($mfrmdate);

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

                                            $mvouch = "";

                                            $mdescrip = "Opening Balance";

                                            $totbal = $totbal + $mobal;

                                            if ($totbal < 0) {

                                                $mdctot = "DR";
                                            } else {

                                                $mdctot = "CR";
                                            }




                                            echo "<tr>";

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

                                            echo "</tr>";
                                            ?>
                                            <tr>
                                    </table>        
                                    <table style="width:100%" class="table table-condensed responsive-table">
                                        <tr style="height:10px;">
                                            <th width="10%" ><label style="font-size: 12px;">Date</label><i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                   
                                            <th width="4%"  align="left"><label style="font-size: 12px;">Source</label><i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                  
                                            <th width="6%"  align="left"><label style="font-size: 12px;">Voucher</label><i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                   
                                            <th width="40%" align="left"><label style="font-size: 12px;">Description</label><i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                   
                                            <th width="10%"  align="right" style="text-align:right;"><label style="font-size: 12px;">Debit</label><i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                   
                                            <th width="10%"  align="right" style="text-align:right;"><label style="font-size: 12px;">Credit</label><i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                   
                                            <th width="10%"  align="right" style="text-align:right;"><label style="font-size: 12px;">Balance</label><i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>                                   
                                        </tr>

                                        <?php
                                        echo "<tr style='height:10px;' class='clickableRow' href='$url'>";
                                        echo "<td style=\"text-align:left;\" scope=\"col\"  >$vdate</td>";
                                        echo "<td style=\"text-align:left;\" scope=\"col\"  ></td>";
                                        echo "<td style=\"text-align:left;\" scope=\"col\"  >$mvouch</td>";
                                        echo "<td style=\"text-align:left;\" scope=\"col\"  >$mdescrip</td>";
                                        echo "<td style=\"text-align:right;\" scope=\"col\" >$mdebit</td>";
                                        echo "<td style=\"text-align:right;\" scope=\"col\" >$mcredit</td>";
                                        echo "<td style=\"text-align:right;\" scope=\"col\" >" . abs($totbal) . " " . $mdctot . "</td>";
                                        echo "</tr>";


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

                                            $vdate = $row["mydt"];

                                            $msource = $row["source"];

                                            $mvouch = $row["vouchno"];

                                            $mamount = $row["amount"];



                                            $mgrpcode = $getac["grpcode"];

                                            $mdiscprov = $getac["discount"];

                                            $totdiscprov = 0;



                                            $mdebit = 0;

                                            $mcredit = 0;

                                            if ($mamount < 0) {

                                                $mdc = "DR";

                                                $mdebit = abs($mamount);

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

                                                $mdc = "CR";

                                                $mcredit = abs($mamount);

                                                $ctot = $ctot + $mcredit;
                                            }

                                            $totbal = $totbal + $mamount;

                                            if ($totbal < 0) {

                                                $mdctot = "DR";
                                            } else {

                                                $mdctot = "CR";
                                            }



                                            $mdescrip = "";

                                            $mchqdtl = "";

                                            $mnarr = "";



                                            switch ($msource) {

                                                case "PU";

                                                    $head = "Purchase";

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

                                                    if ($prnnarr == 1) {

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

                                                                        `rem1`, `rem2` FROM `purmain`

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

                                                        $nqry = mysqli_fetch_array($nqry1);

                                                        $mbill = $nqry["billno"];
                                                        $mbilldt = $nqry["mbilldate"];
                                                        $mnarr = trim($nqry["rem1"]) . " " . trim($nqry["rem2"]);

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

                                                    break;



                                                case "PR";

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

                                                    break;

                                                case "PRU";

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

                                                    break;


                                                case "SL";

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

                                                    $head = "Sale";

                                                    if ($prnnarr == 1) {

                                                        $mnarr = "";

                                                        $mchqdtl = "";
                                                    }

                                                    break;



                                                case "SR";

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

                                                    break;

                                                case "SRU";

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

                                                    break;

                                                case "BR";

                                                    $head = "Bank Receipt";

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

                                                    if ($prnnarr == 1) {

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

                                                                       `narration` FROM `bankrecp`

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



                                                        $nqry = mysqli_fetch_array($nqry1);

                                                        $mbill = $nqry["chqno"];

                                                        $mbilldt = $nqry["mbilldate"];

                                                        $mnarr = $nqry["narration"];



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

                                                    break;



                                                case "BP";

                                                    $head = "Bank Payment";

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

                                                    if ($prnnarr == 1) {

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

                                                                        `narration` FROM `bankpay`

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

                                                        $nqry = mysqli_fetch_array($nqry1);

                                                        $mbill = $nqry["chqno"];

                                                        $mbilldt = $nqry["mbilldate"];

                                                        $mnarr = $nqry["narration"];



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

                                                    break;



                                                case "CR";

                                                    $head = "Cash Receipt";

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

                                                    if ($prnnarr == 1) {

                                                        $nqry1 = mysqli_query($conn, "SELECT `narration` FROM `cashrecp`

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

                                                        $nqry = mysqli_fetch_array($nqry1);

                                                        $mnarr = $nqry["narration"];
                                                    }

                                                    break;



                                                case "CP";

                                                    $head = "Cash Payment";

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

                                                    if ($prnnarr == 1) {

                                                        $nqry1 = mysqli_query($conn, "SELECT `narration` FROM `cashpay`

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

                                                        $nqry = mysqli_fetch_array($nqry1);

                                                        $mnarr = $nqry["narration"];
                                                    }

                                                    break;



                                                case "TR";

                                                    $head = "Contra / Transfer";

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

                                                    if ($prnnarr == 1) {

                                                        $nqry1 = mysqli_query($conn, "SELECT `narration` FROM `contra`

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

                                                        $nqry = mysqli_fetch_array($nqry1);

                                                        $mnarr = $nqry["narration"];
                                                    }

                                                    break;



                                                case "JV";

                                                    $head = "Journal Voucher";



                                                    break;



                                                case "DG";

                                                    $head = "Discount Given";



                                                    break;



                                                case "DR";

                                                    $head = "Discount Received";

                                                    break;



                                                default :

                                                    $head = "Other";
                                            }

                                            echo "<tr>";

                                            echo "<td style=\"text-align:left;\"  >$vdate</td>";
                                            echo "<td style=\"text-align:left;\"  >$msource</td>";
                                            echo "<td style=\"text-align:left;\"  >$mvouch</td>";
                                            echo "<td style=\"text-align:left;\"  class=\"clickableRow\" href=\"$url\">$head</td>";
                                            echo "<td style=\"text-align:right;\" >$mdebit</td>";
                                            echo "<td style=\"text-align:right;\" >$mcredit</td>";
                                            echo "<td style=\"text-align:right;\" >" . abs($totbal) . " " . $mdctot . "</td>";

                                            echo "</tr>";


                                            if ($prnnarr == 1) {

                                                if (!empty($mchqdtl)) {

                                                    echo "<tr>";
                                                    echo "<td></td>";
                                                    echo "<td></td>";
                                                    echo "<td style='text-align:left;' >$mchqdtl</td>";
                                                    echo "<td></td>";
                                                    echo "<td></td>";
                                                    echo "<td></td>";
                                                    echo "</tr>";
                                                }

                                                if (!empty($mnarr)) {

                                                    echo "<tr>";

                                                    echo "<td></td>";
                                                    echo "<td></td>";
                                                    echo "<td style='text-align:left;' >$mnarr</td>";
                                                    echo "<td></td>";
                                                    echo "<td></td>";
                                                    echo "<td></td>";

                                                    echo "</tr>";
                                                }
                                            }
                                        }
                                        echo "<tr>";

                                        echo "<td></td>";
                                        echo "<td style='text-align:left;' ></td>";
                                        echo "<td style='text-align:left;' ></td>";
                                        echo "<td style='text-align:left;' ></td>";
                                        echo "<td style='text-align:left;' ><strong>Total</strong></td>";
                                        echo "<td style='text-align:right;' >$dtot</td>";
                                        echo "<td style='text-align:right;' >$ctot</td>";

                                        if ($totbal < 0) {

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

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

                                        echo "<tr>";

                                        if ($mgrpcode == '009') {

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

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

//                echo "<td>'Discount Provision  :  '</td>";                    
                                        }

                                        echo "</tr>";
                                    }
                                }

                                if ($dtype == 1) {

                                    echo '</tr>';

                                    echo "<tr>";

                                    echo "<td align='right'><strong>Total</strong></td>";

                                    echo "<td style=\"text-align:right;\" scope=\"col\" width=\"200\"><strong>" . abs($totbal) . " " . $mdctot . "</strong></td>";
                                    echo "</tr>";
                                }
                                ?>
                                </tbody>
                                <tfoot>
                                    <tr>
                                        <td colspan="9">
                                        </td>
                                    </tr>
                                </tfoot>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
        <?php
        include("includes/linksfoot.php");
    }
    ?>
