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

//$mfrdate = trim($_POST["frdt"]);
    $fdt = date("d-m-Y", strtotime(getzdates($myyear, 'S')));
//    browse($fdt);

    mysqli_select_db($conn,$zdb);

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

    include("includes/linkshead.php");
    ?>
    </head>
    <body>
        <div class="container">
            <?php
    include("includes/head_rep.php");
    ?>
          
               
            <div class="row">
                <div class="col-lg-12 pad">
                    <table class="table table-bordered table-condensed table-hover" style="float: left; width: 48%;margin: 2px">
                        <tr  style="background-color: #64CCFC">
                            <th>Liabilities</th>                        
                            <th style="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 = $copen1 + $open1 + $gettran;

                                    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'>$pamt1</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 = $totop1 + $totop3;

                                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'>$pamt1</td>";
                                Echo "</tr>";

                                $totamt1 = $totamt1 + $pamt1;
                                $ctr++;
                            }
                        }
                        ?>
                    </table>
                    <table class="table table-bordered table-condensed table-hover" style="float: left; width: 48%;margin: 2px">
                        <tr style="background-color: #64CCFC">
                            <th>Assets</th>                        
                            <th style="text-align: right">Amount</th>                        
                        </tr>
                        <?php
                        $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);
//                        }

                            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'>$pamt2</td>";
                            Echo "</tr>";

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

                    <table class = "table table-bordered table-condensed table-hover" style="width: 96%; margin: 2px">
                        <?php
                        $mdiff = $totamt1 - $totamt2;

                        Echo "<tr style='background-color:white'>";
                        Echo"<td style='width: 20%; color:#26A69A'>TOTAL</td>";
                        Echo"<td class='text-right' style = 'color:#26A69A;font-weight:bold;'>$totamt1</td>";
                        Echo"<td style='padding-left:4.7%;'></td>";
                        Echo"<td class='text-right' style = 'color:#26A69A;font-weight:bold;'>$totamt2</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'>$mdiff</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'>$mdiff</td>";
                                Echo"<td style='padding-left:4.7%;'></td>";
                                Echo"<td class='text-right'></td>";
                                Echo "</tr>";
                            }
                        }
                        ?>
                    </table>

                </div>
            </div>
        </div>

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