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

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

if (isset($_POST["view"])) {
    $acgrp = $_POST[acgrp];
    $scope = $_POST[scope];
    $dtype = $_POST[dtype];

//    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 (empty($_POST["frdt"])) {
        browse("From Date Cannot Be Empty");
        header("refresh:1; trbal.php");
    }

    if (empty($_POST["todt"])) {
        browse("To Date Cannot Be Empty");
        header("refresh:1; trbal.php");
    }

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


    $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, 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">
            <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: #78909C">
                                <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: #78909C">
                                <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)) {
                                $mopenbal = 0;
                                $mopendeb = 0;
                                $mopencred = 0;
                                $mclosdeb = 0;
                                $mcloscred = 0;
                                $mdurdeb = 0;
                                $mdurcred = 0;

                                $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 ($mopenbal > 0) {
                                    $opendc = 'C';
                                    $mopencred = $mopenbal;
                                } else {
                                    $mopendeb = $mopenbal * -1;
                                }

                                $mname = $pmain[grpname];

                                if ($dtype == "1") {
                                    $getactran = mysqli_query($conn, "SELECT sum(amount) as tamt FROM `actran` WHERE `grpcode`='$pmain[grpcode]' 
                                                         and `date` >= '$mfrdate' and `date`<='$mtodate' and chqret != 'Y' ") 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' ") or die(mysqli_error($conn));
                                }
                                $mtran = mysqli_fetch_array($getactran);
                                $mdurbal = $mtran[tamt];
//                                browse($mdurbal);
                                
                                $mclosbal = $mopenbal + $mdurbal ;

                                if ($mdurbal <= 0) {
                                    $mdurdeb = $mdurbal * -1;
                                } else {
                                    $mdurcred = $mdurbal;
                                }

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

//                                browse($mclosbal);
                                
                                $ctr++;
                                if ($dtype == "2") {
                                    if ($mname != $tname) {
//                                        browse($mname);
                                        Echo "<tr id='boldfont'>";
                                        Echo "<td style='font-siz:14px;color:blue;'>$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 {
                                        
                                    }
                                }
                                Echo "<tr id='boldfont'>";
                                if ($dtype == "1") {
                                    Echo "<td>$pmain[grpname]</td>";
                                } else {
                                    Echo "<td>$pmain[name]</td>";
                                }
                                
                                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 = $totopdeb - $totopcred;
                                $totdiff2 = $totdurdeb - $totdurcred;
                                $totdiff3 = $totclosdeb - $totcloscred;
                            }

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

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