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

$maindb = "grpmast";
$formname = "Balance Sheet";
$file = "balsheet.php";

$action = 'trbal_fr.php';


$actdb = 'actran';

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

if (isset($_POST["view"])) {

    $getpmain1 = mysqli_query($conn, "SELECT * FROM `$maindb` WHERE `type`='L' order by name ")or die(mysqli_error($conn));
    $getpmain2 = mysqli_query($conn, "SELECT * FROM `$maindb` WHERE `type`='A' order by name ")or die(mysqli_error($conn));

    $totamt1 = 0;
    $totamt2 = 0;

    $fdt = date("d-m-Y", strtotime(getzdates($myyear, 'S')));

    mysqli_select_db($conn, $zdb);

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

    include("includes/linkshead.php");
    ?>
    </head>
    <body>
        <div class="container">


            <div class="row">
                <div class="col-lg-10 pad"></div>
                <div class="col-lg-2 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">&nbsp;&nbsp;Excel&nbsp;&nbsp;</button>
                </div>
                <div class="col-lg-12 pad tablerep">
                    <?php
                    include("includes/head_rep.php");
                    ?>
                    <table style="float: left; width: 100%;">
                        <tr>
                            <td style="vertical-align: top;">


                                <table class="table table-bordered table-condensed table-hover" style="float: left;">
                                    <tr  style="background-color: #26A69A">
                                        <th style="width: 35%;">Liabilities</th>                        
                                        <th style="width: 15%;text-align: right">Amount</th>                        
                                    </tr>
                                    <?php
                                    $ctr = 1;
                                    while ($pmain = mysqli_fetch_array($getpmain1)) {
                                        $grpcode1 = $pmain[grpcode];
                                        if ($grpcode1 == '050') {
                                            $qryop1 = mysqli_query($conn, "SELECT openbal, dc, name, currbal, currdc, accode FROM `accmast` where grpcode = '$grpcode1' ")or die(mysqli_error($conn));
                                            while ($getacc = mysqli_fetch_array($qryop1)) {
                                                $oaccode = $getacc['accode'];
                                                $open1 = $getacc['openbal'];
                                                $dc1 = $getacc['dc'];
                                                if ($dc1 == 'D') {
                                                    $open1 = $open1 * -1;
                                                }

                                                $copen1 = $getacc['currbal'];
                                                $cdc1 = $getacc['currdc'];
                                                if ($cdc1 == 'D') {
                                                    $copen1 = $copen1 * -1;
                                                }

                                                $qrytran = mysqli_query($conn, "SELECT sum(amount) as tranamt FROM $actdb WHERE date <= '$mtodate' and 
                                                       accode = '$oaccode' and chqret != 'Y' ") or die(mysqli_error($conn));
                                                $fettran = mysqli_fetch_array($qrytran);
                                                $gettran = $fettran[tranamt];

                                                $pamt1 = round($copen1 + $open1 + $gettran,2);

                                                Echo"<tr class='clickable-row2' style='background-color:white' data-href='$action?view=view&g=$grpcode1&s=3&fdt=$fdt&tdt=$tdt&rpt=3' [target]='_blank'>";
                                                Echo "<td>$getacc[name]</td>";
                                                if ($pamt1 != 0) {
                                                    Echo "<td class='text-right'>" .number_format($pamt1,2) . "</td>";
                                                } else {
                                                    Echo "<td></td>";
                                                }
                                                Echo "</tr>";
                                                $totamt1 = $totamt1 + $pamt1;
                                            }
                                        } else {
                                            $qryop1 = mysqli_query($conn, "SELECT openbal, dc FROM `accmast` where grpcode = '$grpcode1' ")or die(mysqli_error($conn));
                                            $totop1 = 0;
                                            while ($pop1 = mysqli_fetch_array($qryop1)) {
                                                $oaccode = $pop1['accode'];
                                                $open1 = $pop1['openbal'];
                                                $dc1 = $pop1['dc'];

                                                if ($dc1 == 'D') {
                                                    $open1 = $open1 * -1;
                                                }
                                                $totop1 = $totop1 + $open1;
                                            }

                                            $qryop3 = mysqli_query($conn, "SELECT sum(amount) as acamt FROM $actdb WHERE date <= '$mtodate' 
                                                  and chqret != 'Y' and 
                                                  `accode` in (select `accode` from `accmast` where `grpcode` = '$grpcode1')  ") or die(mysqli_error($conn));
                                            $pop3 = mysqli_fetch_array($qryop3);
                                            $totop3 = $pop3['acamt'];

                                            $pamt1 = round($totop1 + $totop3,2);

                                            Echo"<tr style='background-color:white' class='clickable-row2' data-href='$action?view=view&g=$grpcode1&s=3&fdt=$fdt&tdt=$tdt&rpt=3' [target]='_blank'>";
                                            Echo "<td>$pmain[name]</td>";
                                            Echo "<td class='text-right'>" .number_format($pamt1,2) . "</td>";
                                            Echo "</tr>";

                                            $totamt1 = $totamt1 + $pamt1;
                                            $ctr++;
                                        }
                                    }
                                    ?>
                                </table>
                            </td>
                            <td style="vertical-align: top;">
                                <table class="table table-bordered table-condensed table-hover" style="float: left;">
                                    <tr style="background-color: #26A69A">
                                        <th style="width:  35%;">Assets</th>                        
                                        <th style="width: 15%;text-align: right">Amount</th>                        
                                    </tr>
                                    <?php
                                    $ctr2 = 1;
                                    while ($pmain2 = mysqli_fetch_array($getpmain2)) {
                                        $grpcode = $pmain2[grpcode];
                                        
                                        if ($grpcode == '008') {
                                            $qryop2 = mysqli_query($conn, "SELECT currbal as openbal, currdc as dc FROM `accmast` where grpcode = '$grpcode' ") or die(mysqli_error($conn));
                                        } else {
                                            $qryop2 = mysqli_query($conn, "SELECT openbal, dc FROM `accmast` where grpcode = '$grpcode' ") or die(mysqli_error($conn));
                                        }
                                        $totop2 = 0;

                                        while ($pop2 = mysqli_fetch_array($qryop2)) {
                                            $open2 = $pop2[openbal];
                                            $dc2 = $pop2[dc];
                                            if ($dc2 == 'D') {
                                                $open2 = $open2 * -1;
                                            }
                                            $totop2 = $totop2 + $open2;
                                        }

                                        $qryac3 = mysqli_query($conn, "SELECT sum(amount) as acamt FROM $actdb WHERE date <= '$mtodate' and 
                                              chqret != 'Y' and `accode` in (select `accode` from `accmast` where grpcode = '$grpcode') ") or die(mysqli_error($conn));

                                        $pacc3 = mysqli_fetch_array($qryac3) ;
                                        $qamt3 = $pacc3[acamt];
                                        $pamt2 = ($totop2 + $qamt3) * -1;
                                        $pamt2 = round($pamt2,2) ;

                                        Echo"<tr style='background-color:white' class='clickable-row2' data-href='$action?view=view&g=$grpcode&s=3&fdt=$fdt&tdt=$tdt&rpt=3' [target]='_blank'>";
                                        Echo"<td>$pmain2[name]</td>";
                                        Echo"<td class='text-right'>" .number_format($pamt2,2) . "</td>";
                                        Echo "</tr>";

                                        $totamt2 = $totamt2 + $pamt2;
                                        $ctr2++;
                                    }
                                    ?>
                                </table>
                            </td>
                        </tr>
                    </table>

                    <table class = "table table-bordered table-condensed table-hover" style="width: 100%;">
                        <?php
                        $totamt1 = round($totamt1,2) ;
                        $totamt2 = round($totamt2,2) ;
                        $mdiff = round($totamt1 - $totamt2,2);

                        Echo "<tr style='background-color:white'>";
                        Echo"<td style='width: 35%; color:#26A69A'>TOTAL</td>";
                        Echo"<td class='text-right' style = 'color:#26A69A;font-weight:bold;width: 15%'>" .number_format($totamt1,2) ."</td>";
                        Echo"<td style='padding-left:4.7%;width: 35%'></td>";
                        Echo"<td class='text-right' style = 'color:#26A69A;font-weight:bold;width: 15%;'>" .number_format($totamt2,2) ."</td>";
                        Echo "</tr>";
                        if ($mdiff != 0) {
                            if ($mdiff > 0) {
                                Echo "<tr style='background-color:white'>";
                                Echo"<td style='color:red'>DIFFERENCE</td>";
                                Echo"<td class='text-right'></td>";
                                Echo"<td style='padding-left:4.7%;'></td>";
                                Echo"<td class='text-right' style='color:red'>" . number_format($mdiff,2) ." </td>";
                                Echo "</tr>";
                            } else {
                                Echo "<tr style='background-color:white'>";
                                Echo"<td style='color:red'>DIFFERENCE</td>";
                                Echo"<td class='text-right' style='color:red'>" . number_format($mdiff,2) ."</td>";
                                Echo"<td style='padding-left:4.7%;'></td>";
                                Echo"<td class='text-right'></td>";
                                Echo "</tr>";
                            }
                        }
                        ?>
                    </table>

                </div>
            </div>
        </div>
        <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>

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

if (isset($_POST["excel"])) {
    $getpmain1 = mysqli_query($conn, "SELECT * FROM `$maindb` WHERE `type`='L' order by name ")or die(mysqli_error($conn));
    $getpmain2 = mysqli_query($conn, "SELECT * FROM `$maindb` WHERE `type`='A' order by name ")or die(mysqli_error($conn));

    $totamt1 = 0;
    $totamt2 = 0;

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


    $contents .= "Liabilities,Amount \n";
    $ctr = 1;
    while ($pmain = mysqli_fetch_array($getpmain1)) {
        $grpcode1 = $pmain[grpcode];
        if ($grpcode1 == '050') {
            $qryop1 = mysqli_query($conn, "SELECT openbal, dc, name, currbal, currdc, accode FROM `accmast` where grpcode = '$grpcode1' ")or die(mysqli_error($conn));
            while ($getacc = mysqli_fetch_array($qryop1)) {
                $oaccode = $getacc['accode'];
                $open1 = $getacc['openbal'];
                $dc1 = $getacc['dc'];
                if ($dc1 == 'D') {
                    $open1 = $open1 * -1;
                }

                $copen1 = $getacc['currbal'];
                $cdc1 = $getacc['currdc'];
                if ($cdc1 == 'D') {
                    $copen1 = $copen1 * -1;
                }

                $qrytran = mysqli_query($conn, "SELECT sum(amount) as tranamt FROM $actdb WHERE date <= '$mtodate' and 
                                                       accode = '$oaccode' and chqret != 'Y' ") or die(mysqli_error($conn));
                $fettran = mysqli_fetch_array($qrytran);
                $gettran = $fettran[tranamt];

                $pamt1 = $copen1 + $open1 + $gettran;

                $contents .= $getacc[name] . ",";
                if ($pamt1 != 0) {
                    $contents .= $pamt1 . "\n";
                } else {
                    $contents .= "" . "\n";
                }
                $totamt1 = $totamt1 + $pamt1;
            }
        } else {
            $qryop1 = mysqli_query($conn, "SELECT openbal, dc FROM `accmast` where grpcode = '$grpcode1' ")or die(mysqli_error($conn));
            $totop1 = 0;
            while ($pop1 = mysqli_fetch_array($qryop1)) {
                $oaccode = $pop1['accode'];
                $open1 = $pop1['openbal'];
                $dc1 = $pop1['dc'];

                if ($dc1 == 'D') {
                    $open1 = $open1 * -1;
                }
                $totop1 = $totop1 + $open1;
            }

            $qryop3 = mysqli_query($conn, "SELECT sum(amount) as acamt FROM $actdb WHERE date <= '$mtodate' 
                                                  and chqret != 'Y' and 
                                                  `accode` in (select `accode` from `accmast` where `grpcode` = '$grpcode1')  ") or die(mysqli_error($conn));
            $pop3 = mysqli_fetch_array($qryop3);
            $totop3 = $pop3['acamt'];

            $pamt1 = $totop1 + $totop3;

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

            $totamt1 = $totamt1 + $pamt1;
            $ctr++;
        }
    }
    $contents .= "TOTAL" . ",";
    $contents .= $totamt1 . "\n";
    $contents .= "\n";
    ?>
    <?php
    $contents .= "Assets, Amount \n";
    $ctr2 = 1;
    while ($pmain2 = mysqli_fetch_array($getpmain2)) {
        $grpcode = $pmain2[grpcode];

        $qryop2 = mysqli_query($conn, "SELECT openbal, dc FROM `accmast` where grpcode = '$grpcode' ") or die(mysqli_error($conn));
        $totop2 = 0;
        while ($pop2 = mysqli_fetch_array($qryop2)) {
            $open2 = $pop2[openbal];
            $dc2 = $pop2[dc];
            if ($dc2 == 'D') {
                $open2 = $open2 * -1;
            }
            $totop2 = $totop2 + $open2;
        }


        $qryac3 = mysqli_query($conn, "SELECT sum(amount) as acamt FROM $actdb WHERE date <= '$mtodate' and 
                                              chqret != 'Y' and `accode` in (select `accode` from `accmast` where grpcode = '$grpcode') ") or die(mysqli_error($conn));
        $pacc3 = mysqli_fetch_array($qryac3);
        $qamt3 = $pacc3[acamt];
        $pamt2 = ($totop2 + $qamt3) * -1;

//                        if ($grpcode==59) {
//                        browse($totop2);
//                        browse($qamt3);
//                        browse($pamt2);
//                        }
        $contents .= $pmain2[name] . ",";
        $contents .= $pamt2 . "\n";

        $totamt2 = $totamt2 + $pamt2;
        $ctr2++;
    }
    ?>

    <?php
    $mdiff = $totamt1 - $totamt2;

//    $contents.="TOTAL" . ",";
//    $contents.=$totamt1 . ",";
    $contents .= "Total" . ",";
    $contents .= $totamt2 . "\n\n";
    if ($mdiff != 0) {
        if ($mdiff > 0) {
            $contents .= "DIFFERENCE" . ",";
            $contents .= "" . ",";
            $contents .= "" . ",";
            $contents .= $mdiff . "\n";
        } else {
            $contents .= "DIFFERENCE" . ",";
            $contents .= $mdiff . ",";
            $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;
    }
}
?>
 
