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

$maindb = "grpmast";
$formname = "Profit & Loss";
$file = "profit.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 `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'];
    $grpexp = $_POST['expense'];

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

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

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

    $mfdt = date("d-m-Y", strtotime($mfrmdate));
    $mtdt = date("d-m-Y", strtotime($mtodate));
    include("includes/linkshead.php");
    ?>
    <style>
        body{
            font-size:17px;
        }
        .table td{
            font-size:14px;

        }
    </style>

    </head>
    <body>
        <div class="container tablerep" >
            <div class="row">
                <div class="col-lg-10 "></div>
                <div class="col-lg-2  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 ">
                    <? include("includes/head_rep.php"); ?>
                    <div class="col-lg-12 pad">
                        <table class="table table-condensed table-hover " style="width : 100%" valign="top">
                            <tr>
                                <td style="vertical-align: top;width: 50%;">
                                    <table class="table table-condensed table-hover " style="float: left; margin: 2px">
                                        <tr  style="background-color: #26A69A">
                                            <th style="width: 35%;">Account</th>                        
                                            <th style="width: 15%;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 == 'C') {
                                                $opens1 = $opens1 * -1;
                                            }
                                            $opstk = $opstk + $opens1;
                                        }
                                        round($opstk, 2);

                                        Echo "<tr>";
                                        Echo "<td>Opening Stock</td>";
                                        Echo "<td class='text-right'>" . number_format($opstk, 2) . "</td>";
                                        Echo "</tr>";


                                        $totamt1 = $opstk;

                                        $ctr = 1;
                                        while ($pmain = mysqli_fetch_array($getpmain1)) {
                                            $totop1 = 0;
                                            $lgrpcode = $pmain['grpcode'];

                                            $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;
                                            }
                                            round($totop1, 2);

                                            if ($grpexp == "G") {
                                                $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);
                                                $pamt1 = round($pamt1, 2);

                                                if ($pamt1 != 0) {
                                                    Echo "<tr>";
                                                    Echo "<td>$pmain[name]</td>";
                                                    Echo "<td class='text-right'>" . number_format($pamt1, 2) . "</td>";
                                                    Echo "</tr>";

                                                    $totamt1 = $totamt1 + $pamt1;
                                                    $ctr++;
                                                }
                                            } else {
                                                $pamt1 = 0;

                                                if ($lgrpcode == '004') {
                                                    $qryac1 = mysqli_query($conn, "SELECT source, sum(amount) as acamt  FROM $actdb WHERE date >= '$mfrom' and date <= '$mto' 
                                                                               and chqret != 'Y' and `accode` in 
                                                                               (select accode from accmast where grpcode = '$lgrpcode') 
                                                                               group by source order by source desc ") or die(mysqli_error($conn));
                                                } else {
                                                    $qryac1 = mysqli_query($conn, "SELECT a.source, sum(a.amount) as acamt, b.name FROM $actdb a, accmast b 
                                                                               WHERE a.date >= '$mfrom' and a.date <= '$mto' and a.accode = b.accode 
                                                                               and a.chqret != 'Y' and b.grpcode = '$lgrpcode' 
                                                                               group by a.accode order by b.name ") or die(mysqli_error($conn));
                                                }
                                                while ($pacc1 = mysqli_fetch_array($qryac1)) {
                                                    $pamt1 = abs($pacc1['acamt']);
                                                    $psource = $pacc1['source'];
                                                    $qname = '';

                                                    if ($lgrpcode == '004') {
                                                        switch ($psource) {
                                                            case "PURW":
                                                                $qname = "PURCHASE RAW MATERIAL";
                                                                break;
                                                            case "PUPK":
                                                                $qname = "PURCHASE PACKING";
                                                                break;
                                                            case "PUEX":
                                                                $qname = "PURCHASE EXPENSE";
                                                                break;
                                                            case "PUOT":
                                                                $qname = "PURCHASE OTHER";
                                                                break;
                                                            case "PRPK":
                                                                $pamt1 = abs($pacc1['acamt']) * -1;
                                                                $qname = "PURCHASE RETURN PACKING";
                                                                break;
                                                            case "PRRW":
                                                                $pamt1 = abs($pacc1['acamt']) * -1;
                                                                $qname = "PURCHASE RETURN RAW MATERIAL";
                                                                break;
                                                        }
                                                    } else {
                                                        $qname = $pacc1['name'];
                                                    }
                                                    $pamt1 = round($pamt1, 2);

                                                    if ($pamt1 != 0) {
                                                        Echo "<tr>";
                                                        Echo"<td>$qname</td>";
                                                        Echo"<td class='text-right'>" . number_format($pamt1, 2) . "</td>";
                                                        Echo "</tr>";

                                                        $totamt1 = $totamt1 + $pamt1;
                                                        $ctr++;
                                                    }
                                                    $totamt1 = round($totamt1, 2);
                                                }
                                            }
                                        }
                                        ?>
                                    </table>
                                </td>
                                <td style="width : 50%;vertical-align: top;">
                                    <table class="table table-condensed table-hover" style="float: left;margin: 2px">
                                        <tr  style="background-color: #26A69A">
                                            <th style="width :35%;">Account</th>                        
                                            <th style="width :15%;text-align: right">Amount</th>                        
                                        </tr>
                                        <?php
                                        $closstk = 0;
                                        $qryops2 = mysqli_query($conn, "SELECT currbal as openbal, currdc as 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 == 'C') {
                                                $opens2 = $opens2 * -1;
                                            }
                                            $closstk = $closstk + $opens2;
                                        }
                                        $closstk = round($closstk, 2);

                                        Echo "<tr>";
                                        Echo "<td>Closing Stock</td>";
                                        Echo "<td class='text-right'>" . number_format($closstk, 2) . "</td>";
                                        Echo "</tr>";

                                        $totamt2 = $closstk;

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

                                            $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;
                                            }
                                            $totop2 = round($totop2, 2);

                                            if ($grpexp == "G") {
                                                $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;
                                                $pamt2 = round($pamt2, 2);


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

                                                    $totamt2 = $totamt2 + $pamt2;
                                                    $ctr2++;
                                                }
                                                $totamt2 = round($totamt2, 2);
                                            } else {
                                                $pamt2 = 0;

                                                if ($qgrpcode == '005') {
                                                    $qryac2 = mysqli_query($conn, "SELECT source, sum(amount) as acamt  FROM $actdb WHERE date >= '$mfrom' and date <= '$mto' 
                                                                              and chqret != 'Y' and `accode` in 
                                                                             (select accode from accmast where grpcode = '$qgrpcode') group by source ") or die(mysqli_error($conn));
                                                } else {

                                                    $qryac2 = mysqli_query($conn, "SELECT a.source, sum(a.amount) as acamt, b.name FROM $actdb a, accmast b 
                                                                               WHERE a.date >= '$mfrom' and a.date <= '$mto' and a.accode = b.accode 
                                                                               and a.chqret != 'Y' and b.grpcode = '$qgrpcode' 
                                                                               group by a.accode order by b.name ") or die(mysqli_error($conn));
                                                }

                                                while ($pacc2 = mysqli_fetch_array($qryac2)) {
                                                    $qamt2 = $pacc2['acamt'];
                                                    $qsource = $pacc2['source'];
                                                    $pamt2 = round($qamt2, 2);

                                                    $qname = '';

                                                    if ($qgrpcode == '005') {
                                                        switch ($qsource) {
                                                            case "SL":
                                                                $qname = "LOCAL SALE";
                                                                break;
                                                            case "SLDE":
                                                                $qname = "DEEMED SALE";
                                                                break;
                                                            case "SLEX":
                                                                $qname = "SALE EXPORT";
                                                                break;
                                                            case "SLO":
                                                                $qname = "OTHER SALE";
                                                                break;
                                                            case "SLON":
                                                                $qname = "ONLINE SALE";
                                                                break;
                                                            case "SR":
                                                                $qname = "SALE RETURN";
                                                                break;
                                                            default:
                                                                $qname = "JV ENTRY";
                                                                break;
                                                        }
                                                    } else {
                                                        $qname = $pacc2['name'];
                                                    }

                                                    if ($pamt2 != 0) {
                                                        Echo "<tr>";
                                                        Echo"<td>$qname</td>";
                                                        Echo"<td class='text-right'>" . number_format($pamt2, 2) . "</td>";
                                                        Echo "</tr>";

                                                        $totamt2 = $totamt2 + $pamt2;
                                                        $ctr2++;
                                                    }
                                                    $totamt2 = round($totamt2, 2);
                                                }
                                            }
                                        }
                                        ?>
                                    </table>
                            </tr>
                        </table>
                        <?php
                        $diffamt = $totamt2 - $totamt1;
                        $diffamt = round($diffamt, 2);

                        if ($diffamt > 0) {
                            $diffname2 = '';
                        } else {
                            $diffname1 = '';
                        }
                        ?>

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

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

//                            $totamt1 = round($totamt1,2) ;
//                            $totamt2 = round($totamt2,2) ;

                            Echo "<tr style='background-color:#aef9f2'>";
                            Echo"<td style='width:35%;font-size:15px;'><b>TOTAL</b></td>";
                            Echo"<td class='text-right' style='width:15%;font-size:15px;'><b>" . number_format($totamt1, 2) . "</b></td>";
                            Echo"<td></td>";
                            Echo"<td class='text-right'style='font-size:15px;'><b>" . number_format($totamt2, 2) . "</b></td>";
                            Echo "</tr>";
                            ?>
                        </table>

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

                        <table class="table table-condensed table-hover " style="width : 100%" valign="top">
                            <tr>
                                <td style="vertical-align: top; width: 50%;">
                                    <table class="table table-condensed table-hover" style="float: left; margin: 2px">
                                        <tr  style="color:#26A69A; border-color: #26A69A" >
                                            <th style="color:#26A69A; border-color: #26A69A;width: 35%;">Expense</th>                        
                                            <th style="color:#26A69A; border-color: #26A69A; text-align: right;width: 15%;">Amount</th>                        
                                        </tr>
                                        <?php
                                        Echo "<tr>";
                                        if ($diffamt < 0) {
                                            Echo"<td style='color: red'>c/f . $diffname2</td>";
                                            Echo"<td class='text-right' style='color: red'>" . number_format($diffamt, 2) . "</td>";

                                            $totamt1 = $totamt1 + $diffamt;
                                            $totdamt1 = $diffamt;
                                        }
                                        Echo "</tr>";
                                        ?>
                                    </table>
                                </td>
                                <td style="vertical-align: top; width: 50%;">
                                    <table class="table table-condensed table-hover" style="float: left;margin: 2px">
                                        <tr  style="color:#26A69A; border-color: #26A69A" >
                                            <th style="color:#26A69A; border-color: #26A69A;width: 35%;">Income</th>                        
                                            <th style="color:#26A69A; border-color: #26A69A; text-align: right;width: 15%;">Amount</th>                        
                                        </tr>
                                        <?php
                                        Echo "<tr>";
                                        if ($diffamt > 0) {

                                            Echo"<td style='color: blue'>c/f . $diffname1</td>";
                                            Echo"<td class='text-right' style='color: blue'>" . number_format($diffamt, 2) . "</td>";
                                            $totamt2 = $totamt2 + $diffamt;
                                            $totdamt2 = $diffamt;
                                        }
                                        Echo "</tr>";
                                        ?>
                                    </table>
                                </td>
                            </tr>
                        </table>

                        <table class="table table-condensed table-hover " style="width : 100%" valign="top">
                            <tr>
                                <td style="vertical-align: top; width: 50%;">
                                    <table class="table table-condensed table-hover" style="float: left;">
                                        <?php
                                        if ($grpexp == "G") {
                                            $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;
                                                $pamt11 = round($pamt11, 2);

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

                                                    $totdamt1 = $totdamt1 + $pamt11;
                                                    $ctr++;
                                                }
                                                $totdamt1 = round($totdamt1, 2);
                                            }
                                        } else {
                                            $ctr = 1;
                                            $totdamt_1 = 0;
                                            while ($pmain11 = mysqli_fetch_array($getpmain11)) {
                                                $pamt11 = 0;
                                                $totdamt_1 = 0;

                                                $qryac11 = mysqli_query($conn, "SELECT a.accode, sum(a.amount) as acamt, b.name FROM $actdb a, accmast b
                                                                    WHERE a.date >= '$mfrom' and a.date <= '$mto' 
                                                                    and a.chqret != 'Y' and a.`accode` = b.accode and
                                                                    b.`grpcode` = '$pmain11[grpcode]' group by a.accode order by b.name ") or die(mysqli_error($conn));

                                                while ($pacc11 = mysqli_fetch_array($qryac11)) {
                                                    $qamt11 = $pacc11['acamt'];
                                                    $pamt11 = $qamt11 * -1;
                                                    $pamt11 = round($pamt11, 2);

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

                                                        $totdamt1 = $totdamt1 + $pamt11;
                                                        $totdamt_1 = $totdamt_1 + $pamt11;
                                                        $ctr++;
                                                    }
                                                }
                                            }
                                        }
                                        ?>
                                    </table>
                                </td>
                                <td style="vertical-align: top; width: 50%;">

                                    <table class="table table-condensed table-hover" style="float: right;">
                                        <?php
                                        if ($grpexp == "G") {
                                            $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);
                                                $pamt22 = $pacc22['acamt'];
                                                $pamt22 = round($pamt22, 2);

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

                                                    $totdamt2 = $totdamt2 + $pamt22;
                                                    $ctr++;
                                                }
                                            }
                                        } else {
                                            $ctr = 1;
                                            while ($pmain22 = mysqli_fetch_array($getpmain22)) {
                                                $pamt22 = 0;

                                                $qryac22 = mysqli_query($conn, "SELECT a.accode, sum(a.amount) as acamt, b.name FROM $actdb a, accmast b 
                                                                    WHERE a.date >= '$mfrom' and a.date <= '$mto' 
                                                                    and a.chqret != 'Y' and a.`accode` = b.accode and 
                                                                    b.`grpcode` = '$pmain22[grpcode]' group by a.accode order by b.name") or die(mysqli_error($conn));

                                                while ($pacc22 = mysqli_fetch_array($qryac22)) {
                                                    $pamt22 = round($pacc22['acamt'], 2);

                                                    if ($pamt22 != 0) {
                                                        Echo "<tr>";
                                                        Echo "<td>$pacc22[name]</td>";
                                                        Echo "<td class='text-right'>" . number_format($pamt22, 2) . "</td>";
                                                        Echo "</tr>";

                                                        $totdamt2 = $totdamt2 + $pamt22;
                                                        $ctr++;
                                                    }
                                                }
                                            }
                                        }
                                        ?>
                                    </table>
                                </td>
                            </tr>
                        </table>
                        <table class="table table-condensed table-hover" style="float: left; width: 100%;">
                            <tr style='height:25px'>
                            </tr>

                            <?php
                            $netamt = $totdamt2 - $totdamt1;
                            $netamt = round($netamt, 2);

                            if ($netamt > 0) {
                                $netname2 = '';
                            } else {
                                $netname1 = '';
                            }

                            Echo "<tr>";

                            if ($netamt > 0) {
                                Echo"<td style='width:35%; color: blue'>$netname1</td>";
                                Echo"<td class='text-right' style='width:15%; color: blue'>" . number_format($netamt, 2) . "</td>";
                                Echo"<td></td>";
                                Echo"<td></td>";
                                $totdamt1 = $totdamt1 + $netamt;
                            } else {
                                Echo"<td></td>";
                                Echo"<td></td>";
                                Echo"<td style='color:red;'>$netname2</td>";
                                Echo"<td style='color:red;' class='text-right'>" . number_format($netamt, 2) . "</td>";
                                $totdamt2 = $totdamt2 + abs($netamt);
                            }
                            Echo "</tr>";

                            $totdamt1 = round($totdamt1, 2);
                            $totdamt2 = round($totdamt2, 2);

                            Echo "<tr style='background-color:#9eb2b0'>";
                            Echo"<td style='width:35%; color:black; border-color: #26A69A;font-size:16px;'><b>TOTAL</b></td>";
                            Echo"<td class='text-right' style='width:15%; color:black; border-color: #26A69A;font-size:16px;'><b>" . number_format($totdamt1, 2) . "</b></td>";
                            Echo"<td style='color:#26A69A; border-color: #26A69A;'></td>";
                            Echo"<td class='text-right' style='color:black; border-color: #26A69A;font-size:16px;'><b>" . number_format($totdamt2, 2) . "</b></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>
        </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 `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;

    $contents .= "Account, Amount \n";
    $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;
    }

    $contents .= "Opening Stock" . ",";
    $contents .= $opstk . "\n";


    $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 ($pamt1 != 0) {
            $contents .= $pmain[name] . ",";
            $contents .= $pamt1 . "\n";

            $totamt1 = $totamt1 + $pamt1;
            $ctr++;
        }
    }
    $contents .= "\n\n";
    ?>
    <?php
    $contents .= "Account,Amount \n";
    ?>
    <?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;
    }

    $contents .= "Closing Stock" . ",";
    $contents .= $closstk . "\n";

    $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) {
            $contents .= $pmain2[name] . ",";
            $contents .= $pamt2 . "\n";

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

    $contents .= "\n\n";
    ?>

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

    <?php
    if ($diffamt > 0) {
        $contents .= $diffname1 . ",";
        $contents .= $diffamt . ",";
        $contents .= "" . ",";
        $contents .= "" . ",";

        $totamt1 = $totamt1 + $diffamt;
    } else {
        $contents .= "" . ",";
        $contents .= "" . ",";
        $contents .= $diffname2 . ",";
        $contents .= $diffamt . "\n";

        $totamt2 = $totamt2 + $diffamt;
    }
    $contents .= "\n";

    $contents .= "TOTAL" . ",";
    $contents .= $totamt1 . ",";
    $contents .= "" . ",";
    $contents .= $totamt2 . "\n";
    ?>

    <?php
    $contents .= "Expense, Amount \n";
    ?>
    <?php
    if ($diffamt < 0) {
        $contents .= c / f . $diffname2 . ",";
        $contents .= $diffamt . "\n";

        $totamt1 = $totamt1 + $diffamt;
    }

    $contents .= "Income, Amount \n";
    ?>

    <?php
    if ($diffamt > 0) {

        $contents .= c / f . $diffname1 . ",";
        $contents .= $diffamt . "\n";
        $totamt2 = $totamt2 + $diffamt;
    }

    $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) {
            $contents .= $pmain11[name] . ",";
            $contents .= $pamt11 . "\n";

            $totamt1 = $totamt1 + $pamt11;
            $ctr++;
        }
    }
    ?>
    <?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) {
            $contents .= $pmain22[name] . ",";
            $contents .= $pamt22 . "\n";

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


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

    if ($netamt > 0) {
        $contents .= $netname1 . ",";
        $contents .= $netamt . ",";
        $contents .= "" . ",";
        $contents .= "" . ",";

        $totamt1 = $totamt1 + $netamt;
    } else {
        $contents .= "" . ",";
        $contents .= "" . ",";
        $contents .= $netname2 . ",";
        $contents .= $netamt . ",";
        $totamt2 = $totamt2 + abs($netamt);
    }

    $contents .= "TOTAL" . ",";
    $contents .= $totamt1 . ",";
    $contents .= "" . ",";
    $contents .= $totamt2 . "\n";

    $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')");
    }

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

