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

$actdb = 'actran';

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

$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"]);
if (isset($_POST["view"])) {
    include("includes/linkshead.php");
?>
    </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">
                    <button id='cancel' type="reset" class="btn btn-primary" onclick="window.close()">
                        Go Back
                    </button>
                </div>
            </div>
            <div class="row">
                <div class="col-lg-12 pad">
                    <table class="table table-condensed table-hover" style="float: left; width: 48%;margin: 2px">
                        <tr style="background-color: #78909C">
                            <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 = $open1 + $gettran + $copen1;

                                    //                                browse($oaccode);
                                    //                                browse($getacc['name']);
                                    //                                browse($copen1);
                                    //                                browse($open1);
                                    //                                browse($gettran);
                                    //                                browse($pamt1);

                                    echo "<tr>";
                                    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>";
                                echo "<td>$pmain[name]</td>";
                                echo "<td class='text-right'>$pamt1</td>";
                                echo "</tr>";

                                $totamt1 = $totamt1 + $pamt1;
                                $ctr++;
                            }
                        }
                        ?>
                    </table>
                    <table class="table table-condensed table-hover" style="float: left; width: 48%;margin: 2px">
                        <tr style="background-color: #78909C">
                            <th>Assets</th>
                            <th style="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, currdc FROM `accmast` where grpcode = '$grpcode' ") or die(mysqli_error($conn));
                                $totop2 = 0;
                                $pops2 = mysqli_fetch_array($qryop2);
                                $currdc = $pops2['currdc'];
                                //                            if ($currdc == "D") {
                                //                                $clos2 = $pops2['currbal'] ;
                                //                            } else {
                                //                                $clos2 = $pops2['currbal'] * -1;
                                //                            }

                                $clos2 = $pops2['currbal'];
                                $pamt2 = round($clos2, 2);
                            } 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;
                            }
                            echo "<tr>";
                            echo "<td>$pmain2[name]</td>";
                            echo "<td class='text-right'>$pamt2</td>";
                            echo "</tr>";

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

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

                        echo "<tr>";
                        echo "<td style='width: 20%; color:#78909C'>TOTAL</td>";
                        echo "<td class='text-right' style = 'width:30%;color:#78909C;font-weight:bold;'>$totamt1</td>";
                        echo "<td style='padding-left:4.7%;'></td>";
                        echo "<td class='text-right' style = 'color:#78909C;font-weight:bold;'>$totamt2</td>";
                        echo "</tr>";
                        if ($mdiff != 0) {
                            if ($mdiff > 0) {
                                echo "<tr>";
                                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>";
                                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>

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

    </html>
<?php  }

if (isset($_POST["excel"])) {
    $contents .= "Date - " . Date("d-m-Y") . ",";
    $contents .= "Print - " . $formname . "\n";

    $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'];

                $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 = $open1 + $gettran + $copen1;

                $contents .= "$getacc[name]" . ",";
                if ($pamt1 != 0) {
                    $contents .= "$pamt1" . ",";
                } else {
                    $contents .= "" . ",";
                }
                $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 .= "\n";
    $contents .= "Assets, Amount \n";

    $ctr2 = 1;
    while ($pmain2 = mysqli_fetch_array($getpmain2)) {
        $grpcode = $pmain2['grpcode'];

        if ($grpcode == '008') {
            $qryop2 = mysqli_query($conn, "SELECT currbal, currdc FROM `accmast` where grpcode = '$grpcode' ") or die(mysqli_error($conn));
            $totop2 = 0;
            $pops2 = mysqli_fetch_array($qryop2);
            $currdc = $pops2['currdc'];
            //                            if ($currdc == "D") {
            //                                $clos2 = $pops2['currbal'] ;
            //                            } else {
            //                                $clos2 = $pops2['currbal'] * -1;
            //                            }

            $clos2 = $pops2['currbal'];
            $pamt2 = round($clos2, 2);
        } 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;
        }
        $contents .= "$pmain2[name]" . ",";
        $contents .= "$pamt2" . "\n";

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

    $mdiff = round($totamt1, 2) - round($totamt2, 2);

    $contents .= "Total Liabilities" . ",";
    $contents .= "$totamt1" . ",";
    $contents .= "Total Assets" . ",";
    $contents .= "$totamt2" . "\n";


    if ($mdiff != 0) {
        if ($mdiff > 0) {

            $contents .= "DIFFERENCE" . ",";
            $contents .= "" . ",";
            $contents .= "" . ",";
            $contents .= "$mdiff" . "\n";
        } else {
            $contents .= "DIFFERENCE" . ",";
            $contents .= "$mdiff" . "\n";
        }
    }

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