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

$maindb = "grpmast";
$trandb = "actran";

$formname = "Trial Balance";
$file = "trbal.php";

$action = 'ledger_fr.php';

$admin = $_SESSION["admin_" . $dbname];
$zdb = $_SESSION["zdbyear_" . $dbname];
$myyear = $_SESSION[$myyear];
mysqli_select_db($conn, $zdb);


if ($_GET["g"] != '') {
    $acgrp = $_GET["g"];
} else {
    $acgrp = $_POST["acgrp"];
}

if ($_GET["s"] != '') {
    $scope = $_GET["s"];
} else {
    $scope = $_POST['scope'];
}

if ($_GET["rpt"] != '') {
    $dtype = $_GET["rpt"];
} else {
    $dtype = $_POST["dtype"];
}

$dscope = $_POST["dscope"];
//$chkdebcrd = $_POST["chkdebcrd"];
//if ($dtype < 2) {
//    $chkdebcrd = 0;
//}

$accodecond = "";
$dtypecond = "";
$mtypecond = "";


if (!empty($mtype)) {
    $mtypecond = " AND `stype`='$mtype' ";
}


if ($_GET["fdt"] != '') {
    $mfrdate = trim($_GET["fdt"]);
} else {
    $mfrdate = trim($_POST["frdt"]);
    if (empty($_POST["frdt"])) {
        browse("From Date Cannot Be Empty");
        header("refresh:1; trbal.php");
    }
}

if ($_GET["tdt"] != '') {
    $mtodate = trim($_GET["tdt"]);
} else {
    $mtodate = trim($_POST["todt"]);
    if (empty($_POST["todt"])) {
        browse("To Date Cannot Be Empty");
        header("refresh:1; trbal.php");
    }
}

/// dtype = 1 = summary and 2 = detail

$mfdt = $mfrdate;
$mtdt = $mtodate;

if (!empty($acgrp)) {
    $grpcond = " AND `grpcode`='$acgrp' ";
    $grpcond1 = " AND b.`grpcode`='$acgrp' ";
}


/////////////////////////////  checking data
if (isset($_POST["view"]) || ($_GET["view"])) {

    $qrymis = mysqli_query($conn, "SELECT source, vouchno, sum(amount) as amt FROM `actran` group by source, vouchno having sum(amount) != 0 order by source,vouchno ") or die(mysqli_error($conn));
    if (mysqli_num_rows($qrymis) > 0) {
        echo "<table style = 'width: 60%'>";
        echo "<tr style = 'height:10px; width:100%; background-color:pink'>";
        echo "<th style = 'width:100%;'>Mismatch Amount In A.c/ Transaction (Actran) </th>";
        echo "</tr>";
        echo "</table>";

        echo "<table style = 'width: 60%'>";
        echo "<tr style = 'height:10px;background-color: pink'>";
        echo "<th style = 'width:4%;'>Srl.</th>";
        echo "<th style = 'width:6%;'>Source</th>";
        echo "<th style = 'text-align: center;width: 8%;'>Vouchno</th>";
        echo "<th style = 'text-align: right;width: 12%;'>Amount</th>";
        echo "</tr>";

        $srl = 0;
        while ($rowmis = mysqli_fetch_array($qrymis)) {
            $srl++;
            Echo "<tr id='boldfont'>";
            Echo "<td>$srl</td>";
            Echo "<td>$rowmis[source]</td>";
            Echo "<td>$rowmis[vouchno]</td>";
            Echo "<td>$rowmis[amt]</td>";
            Echo "</tr>";
        }
        echo "</table>";
    }

    $qrynf = mysqli_query($conn, "SELECT source, vouchno, sum(amount) as amt, accode FROM `actran` WHERE `accode` not in (select `accode` from `accmast`) group by source,vouchno order by source,vouchno ") or die(mysqli_error($conn));
    if (mysqli_num_rows($qrynf) > 0) {
        echo "<table style = 'width: 60%'>";
        echo "<tr style = 'height:10px;background-color: pink'>";
        echo "<th style = 'width:100%';>A/c. Code from A.c/ Transaction (Actran) Not Found In Account Master (Accmast) </th>";
        echo "</tr>";
        echo "</table>";

        echo "<table style = 'width: 60%'>";
        echo "<tr style = 'height:10px;background-color: pink'>";
        echo "<th style = 'width:4%;'>Srl.</th>";
        echo "<th style = 'width:6%;'>Source</th>";
        echo "<th style = 'text-align: center;width: 8%;'>Vouchno</th>";
        echo "<th style = 'text-align: right;width: 12%;'>Amount</th>";
        echo "<th style = 'text-align: right;width: 12%;'>Accode</th>";
        echo "</tr>";

        $srl = 0;
        while ($rownf = mysqli_fetch_array($qrynf)) {
            $srl++;
            Echo "<tr id='boldfont'>";
            Echo "<td>$srl</td>";
            Echo "<td>$rownf[source]</td>";
            Echo "<td>$rownf[vouchno]</td>";
            Echo "<td>$rownf[amt]</td>";
            Echo "<td>$rownf[accode]</td>";
            Echo "</tr>";
        }
        echo "</table>";
    }

    $qrynfa = mysqli_query($conn, "SELECT source, vouchno, sum(amount) as amt FROM `actran` WHERE `grpcode` not in (select `grpcode` from `accmast`) group by source,vouchno order by source,vouchno ") or die(mysqli_error($conn));
    if (mysqli_num_rows($qrynfa) > 0) {
        echo "<table style = 'width: 60%'>";
        echo "<tr style = 'height:10px;background-color: pink'>";
        echo "<th style = 'width: 100%;'>Group Code from A.c/ Transaction (Actran) Not Found In Group Master (Grpmast) </th>";
        echo "</tr>";
        echo "</table>";

        echo "<table style = 'width: 60%'>";
        echo "<tr style = 'height:10px;background-color: pink'>";
        echo "<th style = 'width:4%;'>Srl.</th>";
        echo "<th style = 'width:6%;'>Source</th>";
        echo "<th style = 'text-align: center;width: 8%;'>Vouchno</th>";
        echo "<th style = 'text-align: right;width: 12%;'>Amount</th>";
        echo "</tr>";
        $srl = 0;
        while ($rownfa = mysqli_fetch_array($qrynfa)) {
            $srl++;
            Echo "<tr id='boldfont'>";
            Echo "<td>$srl</td>";
            Echo "<td>$rownfa[source]</td>";
            Echo "<td>$rownfa[vouchno]</td>";
            Echo "<td>$rownfa[amt]</td>";
            Echo "</tr>";
        }
        echo "</table>";
    }

    $qrynfga = mysqli_query($conn, "SELECT * FROM `accmast` WHERE `grpcode` not in (select `grpcode` from `grpmast`) group by accode order by name") or die(mysqli_error($conn));
    if (mysqli_num_rows($qrynfga) > 0) {
        echo "<table style = 'width: 60%'>";
        echo "<tr style = 'height:10px;background-color: pink'>";
        echo "<th style = 'width: 50%;'>Group Code From A/c. Entries (Actran) Not Found In Group Master (Grpmast) </th>";
        echo "</tr>";
        echo "</table>";

        echo "<table style = 'width: 60%'>";
        echo "<tr style = 'height:10px;background-color: pink'>";
        echo "<th style = 'width: 50%;'>A/c. Name</th>";
        echo "</tr>";

        while ($rownfg = mysqli_fetch_array($qrynfga)) {
            Echo "<tr id='boldfont'>";
            Echo "<td>$rownfg[name]</td>";
            Echo "</tr>";
        }
        echo "</table>";
    }

//////////////////////////////  finish  checking data

    if ($dtype == 1) {
        $getpmain = mysqli_query($conn, "SELECT grpcode, name as grpname FROM `grpmast` WHERE grpcode != '' $grpcond group by grpcode order by grpcode")or die(mysqli_error($conn));
    } else {
        $getpmain = mysqli_query($conn, "SELECT a.accode, a.dc, a.grpcode, a.name, a.openbal as opbal, a.dc, b.name as grpname 
                                 FROM `accmast` a, `grpmast` b WHERE a.grpcode = b.grpcode
                                 $grpcond1 order by b.name,a.name") or die(mysqli_error($conn));
    }

    $get = mysqli_num_rows($getpmain);
//    browse($get);


    if (isset($_GET["view"])) {
        $acgrp = $_GET["acgrp"];
        $scope = $_GET["scope"];
        $dtype = 2;
//        $chkdebcrd = 0;
    }

    include("includes/linkshead.php");
    ?>
    <script>
        $(document).ready(function () {
            $('#create_excel').click(function () {
                var excel_data = $('.tablerep').html();
                var page = "excellib.php?data=" + excel_data.trim();
                console.log(page);
                var data = {excel_data: excel_data};
                window.open('data:application/vnd.ms-excel,' + encodeURIComponent(excel_data));
            });
        });
    </script>

    <style type="text/css">
        #tabital{
            font-style: italic;
        }
        #font1{
            font-style: italic;
        }
        #boldfont{
            font-weight: bold; 
        }
    </style>
    <!--<script src="MyScript/item.js" type="text/javascript"></script>-->
    </head>
    <body>
        <div class="container tablerep" >
            <?php include("includes/head_rep.php"); ?>
            <div class="col-lg-12 pad text-right">
                <button id="create_excel" name="create_excel" style="color: #26A69A;background: white; border: 1px black solid" type="button" class="btn btn-danger"><span style="text-align: right;">&nbsp;&nbsp;Excel&nbsp;&nbsp;</span></button>
            </div>

            <div class="row">
                <div class="col-lg-12 pad">
                    <table style="width:100%;background: white" class="table table-condensed table-hover table-bordered">
                        <thead>
                            <tr style="height:10px;background-color: #26A69A">
                                <th style="width: 30%;">Account</th>
                                <th style="text-align: center;width: 11%;">Opening</th>
                                <th style="text-align: center;width: 11%;"></th>
                                <th style="text-align: center;width: 11%;">During</th>
                                <th style="text-align: center;width: 11%;"></th>
                                <th style="text-align: center;width: 11%;">Closing</th>
                                <th style="text-align: center;width: 11%;"></th>
                            </tr>
                            <tr style="height:10px;background-color: #26A69A">
                                <th style=""></th>
                                <?php
                                if ($scope == 2 || $scope == 3 || $scope == 4) {
                                    ?>
                                    <th class="text-right" style="">Debit</th>
                                    <th class="text-right" style="">Credit</th>
                                    <?php
                                } else {
                                    echo "<th></th>";
                                    echo "<th></th>";
                                }
                                if ($scope == 3) {
                                    ?>
                                    <th class="text-right" style="">Debit</th>
                                    <th class="text-right" style="">Credit</th>
                                    <?php
                                } else {
                                    echo "<th></th>";
                                    echo "<th></th>";
                                }
                                if ($scope == 2 || $scope == 3 || $scope == 1) {
                                    ?>
                                    <th class="text-right" style="">Debit</th>
                                    <th class="text-right" style="">Credit</th>
                                        <?php
                                    } else {
                                        echo "<th></th>";
                                        echo "<th></th>";
                                    }
                                    ?>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $ctr = 0;
                            $totamt = 0;
                            $topaytot = 0;
                            $paidtot = 0;
                            $totnet = 0;
                            $mamount = 0;
                            $tname = '';

                            $todr = $tocr = $tddr = $tdcr = $tcdr = $tccr = 0;
                            $totf = 0;

                            mysqli_query($conn, "drop temporary table if exists opdata") or die(mysqli_error($conn));
                            mysqli_query($conn, "create temporary table opdata SELECT accode, sum(amount) as opamt  FROM `$trandb` WHERE 
                                                date < '$mfrdate' and retdate = '0000-00-00' and chqret != 'Y' group by accode") or die(mysqli_error($conn));

                            mysqli_query($conn, "drop temporary table if exists drdata") or die(mysqli_error($conn));
                            mysqli_query($conn, "create temporary table drdata SELECT accode, sum(amount) as dramt  FROM `$trandb` WHERE 
                                                `date` >= '$mfrdate' and `date`<='$mtodate' and retdate = '0000-00-00' and chqret != 'Y' 
                                                 and amount < 0  group by accode") or die(mysqli_error($conn));

                            mysqli_query($conn, "drop temporary table if exists crdata") or die(mysqli_error($conn));
                            mysqli_query($conn, "create temporary table crdata SELECT accode, sum(amount) as cramt FROM `actran` WHERE 
                                                `date` >= '$mfrdate' and `date`<='$mtodate' and chqret != 'Y' and `retdate` = '0000-00-00' 
                                                and amount > 0 group by accode ") or die(mysqli_error($conn));

                            while ($pmain = mysqli_fetch_array($getpmain)) {
                                $acopbal = $pmain['opbal'];
                                $acopdc = $pmain['dc'];
                                $mopenbal = 0;
                                $mopendeb = 0;
                                $mopencred = 0;
                                $mclosdeb = 0;
                                $mcloscred = 0;
                                $mdurdeb = 0;
                                $mdurcred = 0;

                                if ($acopdc == 'D') {
                                    $acopbal = $acopbal * -1;
                                }

                                $totop1 = 0;
                                if ($dtype == 1) {
                                    $qryop1 = mysqli_query($conn, "SELECT openbal, dc FROM `accmast` where grpcode = '$pmain[grpcode]' ") 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;
                                if ($dtype == 1) {
                                    $qryop2 = mysqli_query($conn, "SELECT sum(opamt) as openamt FROM `opdata` where `accode`
                                                                   in (select `accode` from `accmast` where `grpcode` = '$pmain[grpcode]') ") or die(mysqli_error($conn));
                                    while ($pop2 = mysqli_fetch_array($qryop2)) {
                                        $open2 = $pop2['openamt'];
                                        $totop2 = $totop2 + $open2;
                                    }
                                } else {
                                    $qryop2 = mysqli_query($conn, "SELECT opamt FROM `opdata` where `accode`  = '$pmain[accode]' ") or die(mysqli_error($conn));
                                    $pop2 = mysqli_fetch_array($qryop2);
                                    $totop2 = $pop2['openamt'];
                                }
                                $mopenbal = $totop1 + $totop2;

                                if ($dtype == '1') {
                                    if ($mopenbal > 0) {
                                        $opendc = 'C';
                                        $mopencred = $mopenbal;
                                    } else {
                                        $mopendeb = $mopenbal * -1;
                                    }
                                } else {
                                    $opendc = $acopdc;
                                    if ($opendc == 'C') {
                                        $mopencred = $acopbal;
                                    } else {
                                        $mopendeb = abs($acopbal);
                                    }
                                    $mopenbal = $acopbal;
                                }

                                $mname = $pmain['grpname'];

                                if ($dtype == "1") {
                                    $getactran = mysqli_query($conn, "SELECT sum(dramt) as tamt FROM `drdata` WHERE accode 
                                                                             in (select accode from accmast where `grpcode`='$pmain[grpcode]') ") or die(mysqli_error($conn));

                                    $getactran2 = mysqli_query($conn, "SELECT sum(cramt) as tamt2 FROM `crdata` WHERE accode 
                                                                             in (select accode from accmast where `grpcode`='$pmain[grpcode]') ") or die(mysqli_error($conn));
                                } else {
                                    $getactran = mysqli_query($conn, "SELECT sum(dramt) as tamt FROM `drdata` WHERE 
                                                                             `accode`='$pmain[accode]' ") or die(mysqli_error($conn));

                                    $getactran2 = mysqli_query($conn, "SELECT sum(cramt) as tamt2 FROM `crdata` WHERE 
                                                                             `accode`='$pmain[accode]' ") or die(mysqli_error($conn));
                                }
                                $mtran = mysqli_fetch_array($getactran);
                                $mdurbal1 = $mtran['tamt'];

                                $mtran2 = mysqli_fetch_array($getactran2);
                                $mdurbal2 = $mtran2['tamt2'];

//                                browse($mdurbal1);
//                                browse($mdurbal2);

                                $mclosbal = $mopenbal + $mdurbal1 + $mdurbal2;

                                if ($dscope == 1) {
                                    $view = 1;
                                } else {
                                    if ($mopenbal == 0 && $mdurbal1 == 0 && $mdurbal2 == 0) {
                                        $view = 0;
                                    } else {
                                        $view = 1;
                                    }
                                }

                                $mdurdeb = $mdurbal1 * -1;
                                $mdurcred = $mdurbal2;

                                if ($mclosbal < 0) {
                                    $mclosdeb = $mclosbal * -1;
                                } else {
                                    $mcloscred = $mclosbal;
                                }

//                                browse($mclosbal);

                                if ($view == 1) {
                                    $ctr++;
                                    if ($dtype == "2") {
                                        if ($mname != $tname) {
                                            if ($totf == 1) {
                                                Echo"<tr>";
                                                Echo "<td class='text-right' style='color:red'>Total</td>";
                                                if ($scope == 2 || $scope == 3 || $scope == 4) {
                                                    Echo "<td class='text-right' style='background-color:#ACDEE9;color:red'>" . rajnumformat($todr) . "</td>";
                                                    Echo "<td class='text-right' style='background-color:#55C6F0;color:red'>" . rajnumformat($tocr) . "</td>";
                                                } else {
                                                    Echo "<td class='text-right'></td>";
                                                    Echo "<td class='text-right'></td>";
                                                }
                                                if ($scope == 3) {
                                                    Echo "<td class='text-right' style='color:red'>" . rajnumformat($tddr) . "</td>";
                                                    Echo "<td class='text-right' style='color:red'>" . rajnumformat($tdcr) . "</td>";
                                                } else {
                                                    Echo "<td class='text-right'></td>";
                                                    Echo "<td class='text-right'></td>";
                                                }
                                                if ($scope == 2 || $scope == 3 || $scope == 1) {
                                                    Echo "<td class='text-right' style='background-color:#F5F691;color:red'>" . rajnumformat($tcdr) . "</td>";
                                                    Echo "<td class='text-right' style='background-color:#F5D05C;color:red'>" . rajnumformat($tccr) . "</td>";
                                                } else {
                                                    Echo "<td class='text-right'></td>";
                                                    Echo "<td class='text-right'></td>";
                                                }
                                                Echo "</tr>";

                                                $todr = $tocr = $tddr = $tdcr = $tcdr = $tccr = 0;
                                            }

//                                        browse($mname);
                                            $pgrpname = $pmain['grpname'];
                                            Echo "<tr id='boldfont'>";
                                            Echo "<td style='font-siz:14px;color:black;background-color:#9eb2b0;'class='text-center'>$pgrpname</td>";
                                            Echo "<td class='text-right'></td>";
                                            Echo "<td class='text-right'></td>";
                                            Echo "<td class='text-right'></td>";
                                            Echo "<td class='text-right'></td>";
                                            Echo "<td class='text-right'></td>";
                                            Echo "<td class='text-right'></td>";
                                            Echo "</tr>";
                                            $tname = $mname;
                                        }
                                    }
                                    $action = 'ledger_fr.php';

                                    if ($pmain['grpcode'] == 001) {
                                        $action = 'bankbook_fr.php';
                                        $para = "B";
                                    }
                                    if ($pmain['grpcode'] == 002) {
                                        $action = 'bankbook_fr.php';
                                        $para = "C";
                                    }
//                                Echo "<tr id='boldfont'>";

                                    if ($dtype == "1") {
                                        $href = 'trbal_fr.php';
                                        Echo"<tr id='boldfont' class='clickable-row2' data-href='$href?view=view&acgrp=$pmain[grpcode]&fdt=$mfrdate&tdt=$mtodate&scope=$scope' [target]='_blank'>";
                                        Echo "<td>$pmain[grpname]</td>";
                                    } else {
                                        Echo"<tr id='boldfont' class='clickable-row2' data-href='$action?para=$para&view=view&acc=$pmain[accode]&fdt=$mfrdate&tdt=$mtodate&rpt=2&rpt2=1&stn=1&prn=2&prz=2' [target]='_blank'>";
                                        Echo "<td>$pmain[name]</td>";
//                                    browse($pmain[name]);
                                    }

                                    if ($scope == 2 || $scope == 3 || $scope == 4) {
                                        Echo "<td class='text-right' style='background-color:#ACDEE9'>" . rajnumformat($mopendeb) . "</td>";
                                        Echo "<td class='text-right' style='background-color:#55C6F0'>" . rajnumformat($mopencred) . "</td>";
                                    } else {
                                        Echo "<td class='text-right'></td>";
                                        Echo "<td class='text-right'></td>";
                                    }
                                    if ($scope == 3) {
                                        Echo "<td class='text-right'>" . rajnumformat($mdurdeb) . "</td>";
                                        Echo "<td class='text-right'>" . rajnumformat($mdurcred) . "</td>";
                                    } else {
                                        Echo "<td class='text-right'></td>";
                                        Echo "<td class='text-right'></td>";
                                    }
                                    if ($scope == 2 || $scope == 3 || $scope == 1) {
                                        Echo "<td class='text-right' style='background-color:#F5F691'>" . rajnumformat($mclosdeb) . "</td>";
                                        Echo "<td class='text-right' style='background-color:#F5D05C'>" . rajnumformat($mcloscred) . "</td>";
                                    } else {
                                        Echo "<td class='text-right'></td>";
                                        Echo "<td class='text-right'></td>";
                                    }
//                                Echo "<td>$pmain[grpcode]</td>";
                                    Echo "</tr>";
//                            Echo "<tr>";
//                                $totamt = $totamt + $pmain[amount];
                                    $totopdeb = $totopdeb + $mopendeb;
                                    $totopcred = $totopcred + $mopencred;

                                    $totdurdeb = $totdurdeb + $mdurdeb;
                                    $totdurcred = $totdurcred + $mdurcred;

                                    $totclosdeb = $totclosdeb + $mclosdeb;
                                    $totcloscred = $totcloscred + $mcloscred;

                                    $todr = $todr + $mopendeb;
                                    $tocr = $tocr + $mopencred;

                                    $tddr = $tddr + $mdurdeb;
                                    $tdcr = $tdcr + $mdurcred;

                                    $tcdr = $tcdr + $mclosdeb;
                                    $tccr = $tccr + $mcloscred;
                                }
                                $totf = 1;
                            }

///////////////////////// summary  for debtors and creditors  in detail part    //////////////////    start   //////////                
//                            if ($chkdebcrd == 1) {
//                                for ($igrp = 9; $igrp < 11; $igrp++) {
//                                    $mopenbal = 0;
//                                    $mopendeb = 0;
//                                    $mopencred = 0;
//                                    $mclosdeb = 0;
//                                    $mcloscred = 0;
//                                    $mdurdeb = 0;
//                                    $mdurcred = 0;
//
//                                    if ($igrp == 9) {
//                                        $sgrp = '00' . $igrp;
//                                    } else {
//                                        $sgrp = '0' . $igrp;
//                                    }
//
//                                    $mnameg = '';
//                                    $qryg = mysqli_query($conn, "SELECT name FROM `grpmast` where grpcode = '$sgrp' ") or die(mysqli_error($conn));
//                                    $fetg = mysqli_fetch_array($qryg);
//                                    $mnameg = $fetg['name'];
//
//                                    $totop1 = 0;
//                                    $qryop1 = mysqli_query($conn, "SELECT openbal, dc FROM `accmast` where grpcode = '$sgrp' ") 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;
//                                    }
//
//                                    $totop2 = 0;
//
//                                    $qryop2 = mysqli_query($conn, "SELECT sum(opamt) as openamt FROM `opdata` where 
//                                                       `accode` in (select `accode` from `accmast` where `grpcode` = '$sgrp' ) group by accode") or die(mysqli_error($conn));
//                                    while ($pop2 = mysqli_fetch_array($qryop2)) {
//                                        $open2 = $pop2['openamt'];
//                                        $totop2 = $totop2 + $open2;
//                                    }
//                                    $mopenbal = $totop1 + $totop2;
//
//                                    if ($mopenbal > 0) {
//                                        $opendc = 'C';
//                                        $mopencred = $mopenbal;
//                                    } else {
//                                        $mopendeb = $mopenbal * -1;
//                                    }
//
//
//                                    $getactran = mysqli_query($conn, "SELECT sum(dramt) as tamt FROM `drdata` WHERE accode 
//                                                                          in (select accode from accmast where `grpcode`='$sgrp') ") or die(mysqli_error($conn));
//
//                                    $getactran2 = mysqli_query($conn, "SELECT sum(cramt) as tamt2 FROM `crdata` WHERE accode 
//                                                                              in (select accode from accmast where `grpcode`='$sgrp') ") or die(mysqli_error($conn));
//
//                                    $mtran = mysqli_fetch_array($getactran);
//                                    $mdurbal1 = $mtran[tamt];
//
//                                    $mtran2 = mysqli_fetch_array($getactran2);
//                                    $mdurbal2 = $mtran2[tamt2];
//
//                                    $mclosbal = $mopenbal + $mdurbal1 + $mdurbal2;
//
//
//                                    $mdurdeb = $mdurbal1 * -1;
//                                    $mdurcred = $mdurbal2;
//
//                                    if ($mclosbal < 0) {
//                                        $mclosdeb = $mclosbal * -1;
//                                    } else {
//                                        $mcloscred = $mclosbal;
//                                    }
//
//
//                                    $ctr++;
//                                    Echo "<tr id='boldfont'>";
//                                    Echo "<td style='font-siz:14px;color:black;background-color:#9eb2b0;'class='text-center'>$mnameg</td>";
//                                    Echo "<td class='text-right'></td>";
//                                    Echo "<td class='text-right'></td>";
//                                    Echo "<td class='text-right'></td>";
//                                    Echo "<td class='text-right'></td>";
//                                    Echo "<td class='text-right'></td>";
//                                    Echo "<td class='text-right'></td>";
//                                    Echo "</tr>";
//                                    $action = 'ledger_fr.php';
//
////                                Echo "<tr id='boldfont'>";
//                                    Echo"<tr id='boldfont' class='clickable-row2' data-href='$action?view=view&grp=$pmain[grpcode]&acc=$pmain[accode]&fdt=$mfrdate&tdt=$mtodate&rpt=1&rpt2=1&stn=1&prn=2&prz=2&para=$para' [target]='_blank'>";
//
//                                    Echo "<td>$mnameg</td>";
//
//                                    if ($scope == 2 || $scope == 3 || $scope == 4) {
//                                        Echo "<td class='text-right' style='background-color:#ACDEE9'>$mopendeb</td>";
//                                        Echo "<td class='text-right' style='background-color:#55C6F0'>$mopencred</td>";
//                                    } else {
//                                        Echo "<td class='text-right'></td>";
//                                        Echo "<td class='text-right'></td>";
//                                    }
//                                    if ($scope == 3) {
//                                        Echo "<td class='text-right'>$mdurdeb</td>";
//                                        Echo "<td class='text-right'>$mdurcred</td>";
//                                    } else {
//                                        Echo "<td class='text-right'></td>";
//                                        Echo "<td class='text-right'></td>";
//                                    }
//                                    if ($scope == 2 || $scope == 3 || $scope == 1) {
//                                        Echo "<td class='text-right' style='background-color:#F5F691'>$mclosdeb</td>";
//                                        Echo "<td class='text-right' style='background-color:#F5D05C'>$mcloscred</td>";
//                                    } else {
//                                        Echo "<td class='text-right'></td>";
//                                        Echo "<td class='text-right'></td>";
//                                    }
//                                    Echo "</tr>";
//                                    $totopdeb = $totopdeb + $mopendeb;
//                                    $totopcred = $totopcred + $mopencred;
//
//                                    $totdurdeb = $totdurdeb + $mdurdeb;
//                                    $totdurcred = $totdurcred + $mdurcred;
//
//                                    $totclosdeb = $totclosdeb + $mclosdeb;
//                                    $totcloscred = $totcloscred + $mcloscred;
//                                }
//                            }
                            /////////////////  end of chkdebcrd
///////////////////////// summary  for debtors and creditors  in detail part    //////////////////    end 


                            $totdiff1 = 0;
                            $totdiff2 = 0;
                            $totdiff3 = 0;

                            $totdiff1 = rajnumformat($totopdeb, 2) - rajnumformat($totopcred, 2);
                            $totdiff2 = rajnumformat($totdurdeb, 2) - rajnumformat($totdurcred, 2);
                            $totdiff3 = rajnumformat($totclosdeb, 2) - rajnumformat($totcloscred, 2);

                            $colr = "green";
                            echo "<tr> ";
                            echo "<td style='color:$colr;'><h4><b>Grand Total</b></h4></td>";
                            if ($scope == 2 || $scope == 3 || $scope == 4) {
                                echo "<td align='right' style='color:$colr;' id='font'><h4><b>" . rajnumformat($totopdeb) . "</b></h4></td>";
                                echo "<td align='right' style='color:$colr;' id='font'><h4><b>" . rajnumformat($totopcred) . "</b></h4></td>";
                            } else {
                                echo "<td align='right' style='color:$colr;' id='font'><h4><b></b></h4></td>";
                                echo "<td align='right' style='color:$colr;' id='font'><h4><b></b></h4></td>";
                            }
                            if ($scope == 3) {
                                echo "<td align='right' style='color:$colr;' id='font'><h4><b>" . rajnumformat($totdurdeb) . "</b></h4></td>";
                                echo "<td align='right' style='color:$colr;' id='font'><h4><b>" . rajnumformat($totdurcred) . "</b></h4></td>";
                            } else {
                                echo "<td align='right' style='color:$colr;' id='font'><h4><b></b></h4></td>";
                                echo "<td align='right' style='color:$colr;' id='font'><h4><b></b></h4></td>";
                            }
                            if ($scope == 2 || $scope == 3 || $scope == 1) {
                                echo "<td align='right' style='color:$colr;' id='font'><h4><b>" . rajnumformat($totclosdeb) . "</b></h4></td>";
                                echo "<td align='right' style='color:$colr;' id='font'><h4><b>" . rajnumformat($totcloscred) . "</b></h4></td>";
                            } else {
                                echo "<td align='right' style='color:$colr;' id='font'><h4><b></b></h4></td>";
                                echo "<td align='right' style='color:$colr;' id='font'><h4><b></b></h4></td>";
                            }

                            echo "</tr>";

                            if (abs($totdiff1) + abs($totdiff2) + abs($totdiff3) > 0) {
                                echo "<tr> ";
                                echo "<td style='color:red;'><h4><b>Difference</b></h4></td>";
                                $colr1 = "red";
                                if ($scope == 2 || $scope == 3 || $scope == 4) {
                                    echo "<td align='right' style='color:$colr1;' id='font'><h4><b></b></h4></td>";
                                    echo "<td align='right' style='color:$colr1;' id='font'><h4><b>" . rajnumformat($totdiff1, 2) . "</b></h4></td>";
                                } else {
                                    echo "<td align='right' style='color:$colr1;' id='font'><h4><b></b></h4></td>";
                                    echo "<td align='right' style='color:$colr1;' id='font'><h4><b></b></h4></td>";
                                }
                                if ($scope == 3) {
                                    echo "<td align='right' style='color:$colr1;' id='font'><h4><b></b></h4></td>";
                                    echo "<td align='right' style='color:$colr1;' id='font'><h4><b>" . rajnumformat($totdiff2, 2) . "</b></h4></td>";
                                } else {
                                    echo "<td align='right' style='color:$colr1;' id='font'><h4><b></b></h4></td>";
                                    echo "<td align='right' style='color:$colr1;' id='font'><h4><b></b></h4></td>";
                                }
                                if ($scope == 2 || $scope == 3 || $scope == 1) {
                                    echo "<td align='right' style='color:$colr1;' id='font'><h4><b></b></h4></td>";
                                    echo "<td align='right' style='color:$colr1;' id='font'><h4><b>" . rajnumformat($totdiff3, 2) . "</b></h4></td>";
                                } else {
                                    echo "<td align='right' style='color:$colr1;' id='font'><h4><b></b></h4></td>";
                                    echo "<td align='right' style='color:$colr1;' id='font'><h4><b></b></h4></td>";
                                }
                                echo "</tr>";
                            }
                            ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>

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


if (isset($_POST["excel"])) {
    if ($dtype == "1") {
        $getpmain = mysqli_query($conn, "SELECT grpcode, name as grpname FROM `grpmast` WHERE grpcode != '' $grpcond group by grpcode order by grpcode")or die(mysqli_error($conn));
    } else {
        $getpmain = mysqli_query($conn, "SELECT a.accode, a.dc, a.grpcode, a.name, a.openbal as opbal, a.dc, b.name as grpname 
                                 FROM `accmast` a, `grpmast` b WHERE a.grpcode = b.grpcode
                                 $grpcond1 order by b.name,a.name")or die(mysqli_error($conn));
    }

    $get = mysqli_num_rows($getpmain);

    $contents .= "Account, Opening, , During, , Closing, \n";
    $contents .= ",";

    if ($scope == 2 || $scope == 3 || $scope == 4) {
        $contents .= "Debit" . ",";
        $contents .= "Credit" . ",";
    } else {
        $contents .= "" . ",";
        $contents .= "" . ",";
    }
    if ($scope == 3) {
        $contents .= "Debit" . ",";
        $contents .= "Credit" . ",";
    } else {
        $contents .= "" . ",";
        $contents .= "" . ",";
    }
    if ($scope == 2 || $scope == 3 || $scope == 1) {
        $contents .= "Debit" . ",";
        $contents .= "Credit" . "\n";
    } else {
        $contents .= "" . ",";
        $contents .= "" . "\n";
    }

    ///////////////////

    $ctr = 0;
    $totamt = 0;
    $topaytot = 0;
    $paidtot = 0;
    $totnet = 0;
    $mamount = 0;
    $tname = '';

    $todr = $tocr = $tddr = $tdcr = $tcdr = $tccr = 0;
    $totf = 0;

    mysqli_query($conn, "drop temporary table if exists opdata") or die(mysqli_error($conn));
    mysqli_query($conn, "create temporary table opdata SELECT accode, sum(amount) as opamt  FROM `$trandb` WHERE 
                                                date < '$mfrdate' and retdate = '0000-00-00' and chqret != 'Y' group by accode") or die(mysqli_error($conn));

    mysqli_query($conn, "drop temporary table if exists drdata") or die(mysqli_error($conn));
    mysqli_query($conn, "create temporary table drdata SELECT accode, sum(amount) as dramt  FROM `$trandb` WHERE 
                                                `date` >= '$mfrdate' and `date`<='$mtodate' and retdate = '0000-00-00' and chqret != 'Y' 
                                                 and amount < 0  group by accode") or die(mysqli_error($conn));

    mysqli_query($conn, "drop temporary table if exists crdata") or die(mysqli_error($conn));
    mysqli_query($conn, "create temporary table crdata SELECT accode, sum(amount) as cramt FROM `actran` WHERE 
                                                `date` >= '$mfrdate' and `date`<='$mtodate' and chqret != 'Y' and `retdate` = '0000-00-00' 
                                                and amount > 0 group by accode ") or die(mysqli_error($conn));


    while ($pmain = mysqli_fetch_array($getpmain)) {
        $acopbal = $pmain['opbal'];
        $acopdc = $pmain['dc'];
        $mopenbal = 0;
        $mopendeb = 0;
        $mopencred = 0;
        $mclosdeb = 0;
        $mcloscred = 0;
        $mdurdeb = 0;
        $mdurcred = 0;

        if ($acopdc == 'D') {
            $acopbal = $acopbal * -1;
        }

        $totop1 = 0;
        if ($dtype == 1) {
            $qryop1 = mysqli_query($conn, "SELECT openbal, dc FROM `accmast` where grpcode = '$pmain[grpcode]' ") 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;
        if ($dtype == 1) {
            $qryop2 = mysqli_query($conn, "SELECT sum(opamt) as openamt FROM `opdata` where `accode`
                                                                   in (select `accode` from `accmast` where `grpcode` = '$pmain[grpcode]') ") or die(mysqli_error($conn));
            while ($pop2 = mysqli_fetch_array($qryop2)) {
                $open2 = $pop2['openamt'];
                $totop2 = $totop2 + $open2;
            }
        } else {
            $qryop2 = mysqli_query($conn, "SELECT opamt FROM `opdata` where `accode`  = '$pmain[accode]' ") or die(mysqli_error($conn));
            $pop2 = mysqli_fetch_array($qryop2);
            $totop2 = $pop2['openamt'];
        }
        $mopenbal = $totop1 + $totop2;

        if ($dtype == '1') {
            if ($mopenbal > 0) {
                $opendc = 'C';
                $mopencred = $mopenbal;
            } else {
                $mopendeb = $mopenbal * -1;
            }
        } else {
            $opendc = $acopdc;
            if ($opendc == 'C') {
                $mopencred = $acopbal;
            } else {
                $mopendeb = abs($acopbal);
            }
            $mopenbal = $acopbal;
        }
        $mname = $pmain['grpname'];

        if ($dtype == "1") {
            $getactran = mysqli_query($conn, "SELECT sum(dramt) as tamt FROM `drdata` WHERE accode 
                                                     in (select accode from accmast where `grpcode`='$pmain[grpcode]') ") or die(mysqli_error($conn));

            $getactran2 = mysqli_query($conn, "SELECT sum(cramt) as tamt2 FROM `crdata` WHERE accode 
                                                      in (select accode from accmast where `grpcode`='$pmain[grpcode]') ") or die(mysqli_error($conn));
        } else {
            $getactran = mysqli_query($conn, "SELECT sum(dramt) as tamt FROM `drdata` WHERE `accode`='$pmain[accode]' ") or die(mysqli_error($conn));

            $getactran2 = mysqli_query($conn, "SELECT sum(cramt) as tamt2 FROM `crdata` WHERE `accode`='$pmain[accode]' ") or die(mysqli_error($conn));
        }
        $mtran = mysqli_fetch_array($getactran);
        $mdurbal1 = $mtran['tamt'];

        $mtran2 = mysqli_fetch_array($getactran2);
        $mdurbal2 = $mtran2['tamt2'];

        $mclosbal = $mopenbal + $mdurbal1 + $mdurbal2;

        if ($dscope == 1) {
            $view = 1;
        } else {
            if ($mopenbal == 0 && $mdurbal1 == 0 && $mdurbal2 == 0) {
                $view = 0;
            } else {
                $view = 1;
            }
        }

        $mdurdeb = $mdurbal1 * -1;
        $mdurcred = $mdurbal2;

        if ($mclosbal < 0) {
            $mclosdeb = $mclosbal * -1;
        } else {
            $mcloscred = $mclosbal;
        }

        if ($view == 1) {
            $ctr++;
            if ($dtype == "2") {
                if ($mname != $tname) {
                    if ($totf == 1) {
                        $contents .= "\n";
                        $contents .= "Total" . ",";

                        if ($scope == 2 || $scope == 3 || $scope == 4) {
                            $ex_todr = rajnumformat($todr);
                            $ex_tocr = rajnumformat($tocr);

                            $contents .= $ex_todr . ",";
                            $contents .= $ex_tocr . ",";
                        } else {
                            $contents .= "" . ",";
                            $contents .= "" . ",";
                        }

                        if ($scope == 3) {
                            $ex_tddr = rajnumformat($tddr);
                            $ex_tdcr = rajnumformat($tdcr);

                            $contents .= $ex_tddr . ",";
                            $contents .= $ex_tdcr . ",";
                        } else {
                            $contents .= "" . ",";
                            $contents .= "" . ",";
                        }

                        if ($scope == 2 || $scope == 3 || $scope == 1) {
                            $ex_tcdr = rajnumformat($tcdr);
                            $ex_tccr = rajnumformat($tccr);

                            $contents .= $ex_tcdr . ",";
                            $contents .= $ex_tccr . ",";
                        } else {
                            $contents .= "" . ",";
                            $contents .= "" . ",";
                        }
                        $contents .= "\n";

                        $todr = $tocr = $tddr = $tdcr = $tcdr = $tccr = 0;
                    }

                    $pgrpname = $pmain['grpname'];
                    
                    $contents .= "\n";
                    $contents .= $pgrpname . ",";
                    $contents .= ",";
                    $contents .= ",";
                    $contents .= ",";
                    $contents .= ",";
                    $contents .= ",";
                    $contents .= ",";
                    $contents .= "\n";
                    $tname = $mname;
                }
            }


            if ($dtype == "1") {
                $contents .= $pmain[grpname] . ",";
                $contents .= ",";
            } else {
                $contents .= $pmain[name] . ",";
                $contents .= ",";
            }

            if ($scope == 2 || $scope == 3 || $scope == 4) {
                $ex_mopendeb = rajnumformat($mopendeb);
                $ex_mopencred = rajnumformat($mopencred);

                $contents .= $ex_mopendeb . ",";
                $contents .= $ex_mopencred . ",";
            } else {
                $contents .= "" . ",";
                $contents .= "" . ",";
            }

            if ($scope == 3) {
                $ex_mdurdeb = rajnumformat($mdurdeb);
                $ex_mdurcred = rajnumformat($mdurcred);

                $contents .= $ex_mdurdeb . ",";
                $contents .= $ex_mdurcred . ",";
            } else {
                $contents .= "" . ",";
                $contents .= "" . ",";
            }

            if ($scope == 2 || $scope == 3 || $scope == 1) {
                $ex_mclosdeb = rajnumformat($mclosdeb);
                $ex_mcloscred = rajnumformat($mcloscred);

                $contents .= $ex_mclosdeb . ",";
                $contents .= $ex_mcloscred . ",";
            } else {
                $contents .= "" . ",";
                $contents .= "" . ",";
            }
            $contents .= "\n";

            $totopdeb = $totopdeb + $ex_mopendeb;
            $totopcred = $totopcred + $ex_mopencred;

            $totdurdeb = $totdurdeb + $ex_mdurdeb;
            $totdurcred = $totdurcred + $ex_mdurcred;

            $totclosdeb = $totclosdeb + $ex_mclosdeb;
            $totcloscred = $totcloscred + $ex_mcloscred;

            $todr = $todr + $ex_mopendeb;
            $tocr = $tocr + $ex_mopencred;

            $tddr = $tddr + $ex_mdurdeb;
            $tdcr = $tdcr + $ex_mdurcred;

            $tcdr = $tcdr + $ex_mclosdeb;
            $tccr = $tccr + $ex_mcloscred;
        }
        $totf = 1;
    }

    $totdiff1 = 0;
    $totdiff2 = 0;
    $totdiff3 = 0;

    $totdiff1 = rajnumformat($totopdeb, 2) - rajnumformat($totopcred, 2);
    $totdiff2 = rajnumformat($totdurdeb, 2) - rajnumformat($totdurcred, 2);
    $totdiff3 = rajnumformat($totclosdeb, 2) - rajnumformat($totcloscred, 2);

    $contents .= "\n";
    $contents .= "Grand Total" . ",";

    if ($scope == 2 || $scope == 3 || $scope == 4) {
        $ex_totopdeb = rajnumformat($totopdeb);
        $ex_totopcred = rajnumformat($totopcred);

        $contents .= $ex_totopdeb . ",";
        $contents .= $ex_totopcred . ",";
    } else {
        $contents .= "" . ",";
        $contents .= "" . ",";
    }

    if ($scope == 3) {
        $ex_totcurdeb = rajnumformat($totdurdeb);
        $ex_totdurcred = rajnumformat($totdurcred);

        $contents .= $ex_totdurdeb . ",";
        $contents .= $ex_totdurcred . ",";
    } else {
        $contents .= "" . ",";
        $contents .= "" . ",";
    }

    if ($scope == 2 || $scope == 3 || $scope == 1) {
        $ex_totclosdeb = rajnumformat($totclosdeb);
        $ex_totcloscred = rajnumformat($totcloscred);

        $contents .= $ex_totclosdeb . ",";
        $contents .= $ex_totcloscred . ",";
    } else {
        $contents .= "" . ",";
        $contents .= "" . ",";
    }
    $contents .= "\n";

    if (abs($totdiff1) + abs($totdiff2) + abs($totdiff3) > 0) {
        $contents .= "\n";
        $contents .= "Difference" . ",";

        if ($scope == 2 || $scope == 3 || $scope == 4) {
            $contents .= "" . ",";

            $ex_totdiff1 = rajnumformat($totdiff1, 2);
            $contents .= $ex_totdiff1 . ",";
        } else {
            $contents .= "" . ",";
            $contents .= "" . ",";
        }
        
        if ($scope == 3) {
            $contents .= "" . ",";

            $ex_totdiff2 = rajnumformat($totdiff2, 2);
            $contents .= $ex_totdiff2 . ",";
        } else {
            $contents .= "" . ",";
            $contents .= "" . ",";
        }
        
        if ($scope == 2 || $scope == 3 || $scope == 1) {
            $contents .= "" . ",";

            $ex_totdiff3 = rajnumformat($totdiff3, 2);
            $contents .= $ex_totdiff3 . ",";
        } else {
            $contents .= "" . ",";
            $contents .= "" . ",";
        }
        $contents .= "\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;
    }
}
?>

