<?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_fr.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"];

//    browse($mtype);
$accodecond = "";
$dtypecond = "";
$mtypecond = "";
if (!empty($acgrp)) {
    $grpcond = " AND `grpcode`='$acgrp' ";
    $grpcond1 = " AND b.`grpcode`='$acgrp' ";
}

//    if (!empty($scope)) {
//        $sccond = " AND `scope`='$scope' ";
//    }

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

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:red'>";
        echo "<th style = 'width:100%;'>Mismatch Amount In A.c/ Transaction (Actran) </th>";
        echo "</tr>";

        echo "<tr style = 'height:10px;background-color: red'>";
        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 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: red'>";
        echo "<th style = 'width:100%';>A/c. Code from A.c/ Transaction (Actran) Not Found In Account Master (Accmast) </th>";
        echo "</tr>";

        echo "<tr style = 'height:10px;background-color: red'>";
        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 ($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 "</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: red'>";
        echo "<th style = 'width: 100%;'>Group Code from A.c/ Transaction (Actran) Not Found In Group Master (Grpmast) </th>";
        echo "</tr>";

        echo "<tr style = 'height:10px;background-color: red'>";
        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: red'>";
        echo "<th style = 'width: 50%;'>Group Code From A/c. Entries (Actran) Not Found In Group Master (Grpmast) </th>";
        echo "</tr>";

        echo "<tr style = 'height:10px;background-color: red>";
        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>";
    }

///

    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);
    include("includes/linkshead.php");
    ?>
    <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">
            <?php include("includes/head_rep.php"); ?>

            <!--            <div class="row">
                            <div class="col-lg-6">
                                <label class="label-control marg">Date - </label>
                                <label class="label-control marg"><?php Echo Date("d-m-Y"); ?></label>
                            </div>
                            <div class="col-lg-5">
                                <label class="label-control marg">Print - </label>
                                <label class="label-control marg"><?php Echo $formname; ?></label>
                            </div>
                            <div class="col-lg-1">
                                <a onclick="window.close()" class="btn btn-primary">GO Back</a>
                            </div>
                        </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: #64CCFC">
                                <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: #64CCFC">
                                <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 = '';

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

                                $totop2 = 0;

                                $qryop2 = mysqli_query($conn,"SELECT sum(amount) as openamt FROM `actran` where date < '$mfrdate' and 
                                                       chqret != 'Y' and `accode` in (select `accode` from `accmast` where 
                                                       `grpcode` = '$pmain[grpcode]') group by accode") or die(mysqli_error($conn));
                                while ($pop2 = mysqli_fetch_array($qryop2)) {
                                    $open2 = $pop2['openamt'];
                                    $totop2 = $totop2 + $open2;
                                }
                                $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(amount) as tamt FROM `actran` WHERE `date` >= '$mfrdate' and `date`<='$mtodate' 
                                                              and chqret != 'Y' and amount < 0 AND accode in (select accode from accmast where `grpcode`='$pmain[grpcode]') ") or die(mysqli_error($conn));

                                    $getactran2 = mysqli_query($conn,"SELECT sum(amount) as tamt2 FROM `actran` WHERE `date` >= '$mfrdate' and `date`<='$mtodate' 
                                                              and chqret != 'Y' and amount > 0 AND accode in (select accode from accmast where `grpcode`='$pmain[grpcode]') ") or die(mysqli_error($conn));
                                } else {
                                    $getactran = mysqli_query($conn,"SELECT sum(amount) as tamt FROM `actran` WHERE `accode`='$pmain[accode]' 
                                                         and `date` >= '$mfrdate' and `date`<='$mtodate' and chqret != 'Y' and amount <= 0 ") or die(mysqli_error($conn));

                                    $getactran2 = mysqli_query($conn,"SELECT sum(amount) as tamt2 FROM `actran` WHERE `accode`='$pmain[accode]' 
                                                         and `date` >= '$mfrdate' and `date`<='$mtodate' and chqret != 'Y' and amount > 0 ") 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) {
//                                        browse($mname);
                                            Echo "<tr id='boldfont'>";
                                            Echo "<td style='font-siz:14px;color:black;background-color:#9eb2b0;'class='text-center'>$pmain[grpname]</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;
                                        } else {
                                            
                                        }
                                    }
                                    $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'>";
                                    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'>";

                                    if ($dtype == "1") {
                                        Echo "<td>$pmain[grpname]</td>";
                                    } else {
                                        Echo "<td>$pmain[name]</td>";
//                                    browse($pmain[name]);
                                    }

                                    if ($scope == 2 || $scope == 3 || $scope == 4) {
                                        Echo "<td class='text-right'>$mopendeb</td>";
                                        Echo "<td class='text-right'>$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'>$mclosdeb</td>";
                                        Echo "<td class='text-right'>$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;
                                }
                            }
                            $totdiff1 = 0;
                            $totdiff2 = 0;
                            $totdiff3 = 0;

                            $totdiff1 = round($totopdeb, 2) - round($totopcred, 2);
                            $totdiff2 = round($totdurdeb, 2) - round($totdurcred, 2);
                            $totdiff3 = round($totclosdeb, 2) - round($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>$totopdeb</b></h4></td>";
                                echo "<td align='right' style='color:$colr;' id='font'><h4><b>$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>$totdurdeb</b></h4></td>";
                                echo "<td align='right' style='color:$colr;' id='font'><h4><b>$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>$totclosdeb</b></h4></td>";
                                echo "<td align='right' style='color:$colr;' id='font'><h4><b>$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>$totdiff1</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>$totdiff2</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>$totdiff3</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"])) {


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

        $contents .= "Mismatch Amount In A.c/ Transaction (Actran) \n";
        $contents .= "Sr., Source, Vouchno, Amount \n";

        $srl = 0;
        while ($rowmis = mysqli_fetch_array($qrymis)) {
            $srl++;
            $contents .= $srl . ",";
            $contents .= $rowmis[source] . ",";
            $contents .= $rowmis[vouchno] . ",";
            $contents .= $rowmis[amt] . "\n";
        }
    }

    $qrynf = mysqli_query($conn,"SELECT source, vouchno, sum(amount) as amt 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) {


        $contents .= "A/c. Code from A.c/ Transaction (Actran) Not Found In Account Master (Accmast)\n";
        $contents .= "Sr., Source, Vouchno, Amount\n";

        $srl = 0;
        while ($rownf = mysqli_fetch_array($qrynf)) {
            $srl++;
            $contents .= $srl . ",";
            $contents .= $rownf[source] . ",";
            $contents .= $rownf[vouchno] . ",";
            $contents .= $rownf[amt] . "\n";
        }
    }

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

        $contents .= "Group Code from A.c/ Transaction (Actran) Not Found In Group Master (Grpmast) \n";
        $contents .= "Sr., Source, Vouchno,  Amount\n";


        $srl = 0;
        while ($rownfa = mysqli_fetch_array($qrynfa)) {
            $srl++;

            $contents .= $srl . ",";
            $contents .= $rownfa['source'] . ",";
            $contents .= $rownfa['vouchno'] . ",";
            $contents .= $rownfa['amt'] . "\n";
        }
    }

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

        $contents .= "Group Code From A/c. Entries (Actran) Not Found In Group Master (Grpmast) \n";
        $contents .= "A/c. Name\n";



        while ($rownfg = mysqli_fetch_array($qrynfga)) {
            $contents .= $rownfg[name] . "\n";
        }
    }

    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 = '';
    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;
        $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;
        }

        $totop2 = 0;

        $qryop2 = mysqli_query($conn,"SELECT sum(amount) as openamt FROM `actran` where date < '$mfrdate' and 
                                                       chqret != 'Y' and `accode` in (select `accode` from `accmast` where 
                                                       `grpcode` = '$pmain[grpcode]') group by accode") or die(mysqli_error($conn));
        while ($pop2 = mysqli_fetch_array($qryop2)) {
            $open2 = $pop2['openamt'];
            $totop2 = $totop2 + $open2;
        }
        $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(amount) as tamt FROM `actran` WHERE `date` >= '$mfrdate' and `date`<='$mtodate' 
                                                              and chqret != 'Y' and amount < 0 AND accode in (select accode from accmast where `grpcode`='$pmain[grpcode]') ") or die(mysqli_error($conn));

            $getactran2 = mysqli_query($conn,"SELECT sum(amount) as tamt2 FROM `actran` WHERE `date` >= '$mfrdate' and `date`<='$mtodate' 
                                                              and chqret != 'Y' and amount > 0 AND accode in (select accode from accmast where `grpcode`='$pmain[grpcode]') ") or die(mysqli_error($conn));
        } else {
            $getactran = mysqli_query($conn,"SELECT sum(amount) as tamt FROM `actran` WHERE `accode`='$pmain[accode]' 
                                                         and `date` >= '$mfrdate' and `date`<='$mtodate' and chqret != 'Y' and amount <= 0 ") or die(mysqli_error($conn));

            $getactran2 = mysqli_query($conn,"SELECT sum(amount) as tamt2 FROM `actran` WHERE `accode`='$pmain[accode]' 
                                                         and `date` >= '$mfrdate' and `date`<='$mtodate' and chqret != 'Y' and amount > 0 ") 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) {

                    $contents .= $pmain["grpname"] . ",";
                    $contents .= "" . ",";
                    $contents .= "" . ",";
                    $contents .= "" . ",";
                    $contents .= "" . ",";
                    $contents .= "" . ",";
                    $contents .= "" . "\n";

                    $tname = $mname;
                } else {
                    
                }
            }
            $action = 'ledger_fr.php';

            if ($pmain[grpcode] == 001) {
                $action = 'bankbook_fr.php';
                $para = "B";
            }
            if ($pmain[grpcode] == 002) {
                $action = 'bankbook_fr.php';
                $para = "C";
            }

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

            if ($scope == 2 || $scope == 3 || $scope == 4) {

                $contents .= $mopendeb . ",";
                $contents .= $mopencred . ",";
            } else {

                $contents .= " " . ",";
                $contents .= " " . ",";
            }
            if ($scope == 3) {
                $contents .= $mdurdeb . ",";
                $contents .= $mdurcred . ",";
            } else {
                $contents .= " " . ",";
                $contents .= " " . ",";
            }
            if ($scope == 2 || $scope == 3 || $scope == 1) {

                $contents .= $mclosdeb . ",";
                $contents .= $mcloscred . "\n";
            } else {
                $contents .= " " . ",";
                $contents .= " " . "\n";
            }
            $totopdeb = $totopdeb + $mopendeb;
            $totopcred = $totopcred + $mopencred;

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

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

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

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

    $contents .= "Grand Total" . ",";
    if ($scope == 2 || $scope == 3 || $scope == 4) {
        $contents .= $totopdeb . ",";
        $contents .= $totopcred . ",";
    } else {
        $contents .= "" . ",";
        $contents .= "" . ",";
    }

    if ($scope == 3) {
        $contents .= $totdurdeb . ",";
        $contents .= $totdurcred . ",";
    } else {
        $contents .= "" . ",";
        $contents .= "" . ",";
    }

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

    if (abs($totdiff1) + abs($totdiff2) + abs($totdiff3) > 0) {
        $contents .= "Difference" . ",";
        if ($scope == 2 || $scope == 3 || $scope == 4) {
            $contents .= "" . ",";
            $contents .= $totdiff1 . ",";
        } else {
            $contents .= "" . ",";
            $contents .= "" . ",";
        }
        if ($scope == 3) {
            $contents .= "" . ",";
            $contents .= $totdiff2 . ",";
        } else {
            $contents .= "" . ",";
            $contents .= "" . ",";
        }
        if ($scope == 2 || $scope == 3 || $scope == 1) {
            $contents .= "" . ",";
            $contents .= $totdiff3 . "\n";
        } else {
            $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;
    }
}
?>

