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

$maindb = "grpmast";
$formname = "Balance Sheet";
$file = "profit.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 `subtype`='TE' and grpcode != '019' order by name ")or die(mysqli_error($conn));
$getpmain2 = mysqli_query($conn, "SELECT * FROM `$maindb` WHERE `subtype`='TI' and grpcode != '008' order by name ")or die(mysqli_error($conn));

$getpmain11 = mysqli_query($conn, "SELECT * FROM `$maindb` WHERE `subtype`='E' and grpcode != '019' order by name ")or die(mysqli_error($conn));
$getpmain22 = mysqli_query($conn, "SELECT * FROM `$maindb` WHERE `subtype`='I' and grpcode != '008' order by name ")or die(mysqli_error($conn));

$getopstk = mysqli_query($conn, "SELECT * FROM `$maindb` WHERE grpcode = '019' order by name ")or die(mysqli_error($conn));
$getclost = mysqli_query($conn, "SELECT * FROM `$maindb` WHERE grpcode = '008' order by name ")or die(mysqli_error($conn));

$totamt1 = 0;
$totamt2 = 0;

$mfrom = $_POST['frdt'];
$mto = $_POST['todt'];

$diffname1 = 'Gross Profit';
$diffname2 = 'Gross Loss';
$diffamt = 0;

$netname1 = 'Net Profit';
$netname2 = 'Net Loss';
$diffamt = 0;

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>Account</th>                        
                        <th style="text-align: right">Amount</th>                        
                    </tr>
                    <?php
                    $opstk = 0;
                    $qryops1 = mysqli_query($conn, "SELECT openbal, dc FROM `accmast` where grpcode = '019' ")or die(mysqli_error($conn));
                    while ($pops1 = mysqli_fetch_array($qryops1)) {
                        $opens1 = $pops1['openbal'];
                        $dcs1 = $pops1['dc'];
                        if ($dcs1 == 'D') {
                            $opens1 = $opens1 * -1;
                        }
                        $opstk = $opstk + $opens1;
                    }

                    Echo "<tr>";
                    Echo "<td>Opening Stock</td>";
                    Echo "<td class='text-right'>$opstk</td>";
                    Echo "</tr>";

                    $totamt1 = $opstk;
                    $ctr = 1;
                    while ($pmain = mysqli_fetch_array($getpmain1)) {
                        $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;
                        }

                        $qryacop1 = mysqli_query($conn, "SELECT sum(amount) as acamt FROM $actdb WHERE date < '$mfrom' and chqret != 'Y' and 
                                               `accode` in (select `accode` from `accmast` where `grpcode` = '$pmain[grpcode]') ") or die(mysqli_error($conn));
                        while ($popac1 = mysqli_fetch_array($qryacop1)) {
                            $openac1 = $popac1['openbal'];
                            $dcac1 = $popac1['dc'];
                            if ($dcac1 == 'D') {
                                $openac1 = $openac1 * -1;
                            }
                            $totop1 = $totop1 + $openac1;
                        }

                        $pamt1 = 0;
                        $qryac1 = mysqli_query($conn, "SELECT sum(amount) as acamt FROM $actdb WHERE date >= '$mfrom' and date <= '$mto' 
                                               and chqret != 'Y' and `accode` in 
                                               (select `accode` from `accmast` where `grpcode` = '$pmain[grpcode]') ") or die(mysqli_error($conn));
                        $pacc1 = mysqli_fetch_array($qryac1);
                        $qamt1 = $pacc1[acamt] * -1;
                        $pamt1 = ($qamt1 + $totop1);

//                        if ($pmain[grpcode] == '004') {
//                            browse($open1);
//                            browse($openac1);
//                            browse($qamt1);
//                        }
                        
                        if ($pamt1 != 0) {
                            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>Account</th>                        
                        <th style="text-align: right">Amount</th>                        
                    </tr>
                    <?php
                    $closstk = 0;
                    $qryops2 = mysqli_query($conn, "SELECT openbal, dc FROM `accmast` where grpcode = '008' ")or die(mysqli_error($conn));
                    while ($pops2 = mysqli_fetch_array($qryops2)) {
                        $opens2 = $pops2['openbal'];
                        $dcs2 = $pops2['dc'];
                        if ($dcs2 == 'D') {
                            $opens2 = $opens2 * -1;
                        }
                        $closstk = $closstk + $opens2;
                    }

                    Echo "<tr>";
                    Echo "<td>Closing Stock</td>";
                    Echo "<td class='text-right'>$closstk</td>";
                    Echo "</tr>";

                    $totamt2 = $closstk;


                    $ctr2 = 1;
                    while ($pmain2 = mysqli_fetch_array($getpmain2)) {
                        $qryop2 = mysqli_query($conn, "SELECT openbal, dc FROM `accmast` where grpcode = '$pmain2[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;
                        }

                        $qryacop2 = mysqli_query($conn, "SELECT sum(amount) as acamt FROM $actdb WHERE date < '$mfrom' and 
                                                chqret != 'Y' AND `accode` in 
                                               (select `accode` from `accmast` where `grpcode` = '$pmain2[grpcode]') ") or die(mysqli_error($conn));
                        while ($popac2 = mysqli_fetch_array($qryacop2)) {
                            $openac2 = $popac2['openbal'];
                            $dcac2 = $popac2['dc'];
                            if ($dcac2 == 'D') {
                                $openac2 = $openac2 * -1;
                            }
                            $totop2 = $totop2 + $openac2;
                        }

                        $pamt2 = 0;
                        $qryac2 = mysqli_query($conn, "SELECT sum(amount) as acamt FROM $actdb WHERE date >= '$mfrom' and date <= '$mto' 
                                               and chqret != 'Y' and `accode` in 
                                               (select accode from accmast where grpcode = '$pmain2[grpcode]') ") or die(mysqli_error($conn));
                        $pacc2 = mysqli_fetch_array($qryac2);
                        $qamt2 = $pacc2[acamt];
                        $pamt2 = $qamt2 + $totop2;

                        if ($pamt2 != 0) {
                            Echo "<tr>";
                            Echo"<td>$pmain2[name]</td>";
                            Echo"<td class='text-right'>$pamt2</td>";
                            Echo "</tr>";

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


                <?php
                $diffamt = $totamt2 - $totamt1;
                if ($diffamt > 0) {
                    $diffname2 = '';
                } else {
                    $diffname1 = '';
                }
                ?>



                <table class="table table-condensed table-hover" style="float: left; width: 96%;margin: 2px">
                    <tr style='height:25px'>
                    </tr>

                    <?php
                    Echo "<tr>";
                    if ($diffamt > 0) {
                        Echo"<td style='width:35%'>$diffname1</td>";
                        Echo"<td class='text-right' style='width:15%'>$diffamt</td>";
                        Echo"<td></td>";
                        Echo"<td></td>";
                        $totamt1 = $totamt1 + $diffamt;
                    } else {
                        Echo"<td></td>";
                        Echo"<td></td>";
                        Echo"<td>$diffname2</td>";
                        Echo"<td>$diffamt</td>";
                        $totamt2 = $totamt2 + $diffamt;
                    }
                    Echo "</tr>";

                    Echo "<tr>";
                    Echo"<td style='width:35%'>TOTAL</td>";
                    Echo"<td class='text-right' style='width:15%'>$totamt1</td>";
                    Echo"<td></td>";
                    Echo"<td class='text-right'>$totamt2</td>";
                    Echo "</tr>";
                    ?>
                </table>

                <!--//                /////////////////// expense and income part ///////////////////////-->


                <table class="table table-condensed table-hover" style="float: left; width: 48%;margin: 2px">
                    <tr  style="color:#78909C; border-color: #78909C" >
                        <th style="color:#78909C; border-color: #78909C;">Expense</th>                        
                        <th style="color:#78909C; border-color: #78909C; text-align: right;">Amount</th>                        
                    </tr>
                </table>
                <table class="table table-condensed table-hover" style="float: left; width: 48%;margin: 2px">
                    <tr  style="color:#78909C; border-color: #78909C" >
                        <th style="color:#78909C; border-color: #78909C">Income</th>                        
                        <th style="color:#78909C; border-color: #78909C; text-align: right">Amount</th>                        
                    </tr>
                </table>

                <table class="table table-condensed table-hover" style="float: left; width: 96%;margin: 2px">
                    <?php
                    Echo "<tr>";
                    if ($diffamt > 0) {
                        Echo"<td style='width:35%'></td>";
                        Echo"<td style='width:15%'></td>";
                        Echo"<td >c/f . $diffname1</td>";
                        Echo"<td class='text-right'>$diffamt</td>";
                        $totamt2 = $totamt2 + $diffamt;
                    } else {
                        Echo"<td>c/f . $diffname2</td>";
                        Echo"<td>$diffamt</td>";
                        Echo"<td></td>";
                        Echo"<td></td>";
                        $totamt1 = $totamt1 + $diffamt;
                    }
                    Echo "</tr>";
                    ?>
                </table>


                <table class="table table-condensed table-hover" style="float: left; width: 48%;margin: 2px">
                    <?php
                    $ctr = 1;
                    while ($pmain11 = mysqli_fetch_array($getpmain11)) {
                        $pamt11 = 0;
                        
                        $qryac11 = mysqli_query($conn, "SELECT sum(amount) as acamt FROM $actdb WHERE date >= '$mfrom' and date <= '$mto' 
                                                and chqret != 'Y' and 
                                               `accode` in (select `accode` from `accmast` where `grpcode` = '$pmain11[grpcode]') ") or die(mysqli_error($conn));
                        $pacc11 = mysqli_fetch_array($qryac11);
                        $qamt11 = $pacc11[acamt];
                        $pamt11 = $qamt11 * -1;
                        
//                        browse($pmain11[grpcode]);
//                        browse($pamt11);

                        if ($pamt11 != 0) {
                            Echo "<tr>";
                            Echo "<td>$pmain11[name]</td>";
                            Echo "<td class='text-right'>$pamt11</td>";
                            Echo "</tr>";

                            $totamt1 = $totamt1 + $pamt11;
                            $ctr++;
                        }
                    }
                    ?>
                </table>

                <table class="table table-condensed table-hover" style="float: left; width: 48%;margin: 2px">
                    <?php
                    $ctr = 1;
                    while ($pmain22 = mysqli_fetch_array($getpmain22)) {
                        $pamt22 = 0;
                        $qryac22 = mysqli_query($conn, "SELECT sum(amount) as acamt FROM $actdb WHERE date >= '$mfrom' and date <= '$mto' 
                                                and chqret != 'Y' and 
                                               `accode` in (select `accode` from `accmast` where `grpcode` = '$pmain22[grpcode]') ") or die(mysqli_error($conn));
                        $pacc22 = mysqli_fetch_array($qryac22);
                        $qamt22 = $pacc22[acamt];
                        $pamt22 = $qamt22;
//                        browse($pmain22[grpcode]);
//                        browse($pamt22);

                        if ($pamt11 != 0) {
                            Echo "<tr>";
                            Echo "<td>$pmain22[name]</td>";
                            Echo "<td class='text-right'>$pamt22</td>";
                            Echo "</tr>";

                            $totamt2 = $totamt2 + $pamt22;
                            $ctr++;
                        }
                    }
                    ?>


                </table>

                <table class="table table-condensed table-hover" style="float: left; width: 96%;margin: 2px">
                    <tr style='height:25px'>
                    </tr>

                    <?php
                    $netamt = $totamt2 - $totamt1;
                    if ($netamt > 0) {
                        $netname2 = '';
                    } else {
                        $netname1 = '';
                    }

                    Echo "<tr>";

                    if ($netamt > 0) {
                        Echo"<td style='width:35%'>$netname1</td>";
                        Echo"<td class='text-right' style='width:15%'>$netamt</td>";
                        Echo"<td></td>";
                        Echo"<td></td>";
                        $totamt1 = $totamt1 + $netamt;
                    } else {
                        Echo"<td></td>";
                        Echo"<td></td>";
                        Echo"<td>$netname2</td>";
                        Echo"<td>$netamt</td>";
                        $totamt2 = $totamt2 + $netamt;
                    }
                    Echo "</tr>";

                    Echo "<tr>";
                    Echo"<td style='width:35%; color:#78909C; border-color: #78909C;'>TOTAL</td>";
                    Echo"<td class='text-right' style='width:15%; color:#78909C; border-color: #78909C;'>$totamt1</td>";
                    Echo"<td style='color:#78909C; border-color: #78909C;'></td>";
                    Echo"<td class='text-right' style='color:#78909C; border-color: #78909C;'>$totamt2</td>";
                    Echo "</tr>";
                    
                    $qryprof = mysqli_query($conn, "select * from profit") or die(mysqli_error($conn));
                    $noprof = mysqli_num_rows($qryprof);
                    if ($noprof > 0) {
                        mysqli_query($conn, "update `profit` set amount = '$netamt'");
                    } else {
                        mysqli_query($conn, "insert into `profit` (amount) values ('$netamt')");
                    }
                    
                    ?>
                </table>

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

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