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

$maindb = "actran";
$formname = "Income & Expenses Report";
$file = "income_exp_fr.php";

$admin = $_SESSION["admin_" . $dbname];
$zdb = $_SESSION["zdbyear_" . $dbname];
$fseries = $_SESSION["series_" . $dbname];
$seriescond = "AND `series`='$fseries'";
$myyear = $_SESSION[$myyear];
mysqli_select_db($conn, $zdb);

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


if (empty($_POST["frdt"])) {
    browse("From Date Cannot Be Empty");
    echo "<script>window.close()</script>";
}
if (empty($_POST["todt"])) {
    browse("To Date Cannot Be Empty");
    echo "<script>window.close()</script>";
}


$getinc = mysqli_query($conn, "SELECT SUM(amount) as lamt FROM $maindb WHERE amount > 0 AND grpcode != '001' AND grpcode != '002'
                                   AND date >= '$mfrmdate' AND date <= '$mtodate' $seriescond ORDER BY vouchno,date,source") or die(mysqli_error($conn));
$pinc = mysqli_fetch_array($getinc);
$lamt = $pinc['lamt'];


$gexp = mysqli_query($conn, "SELECT SUM(amount) as ramt FROM $maindb WHERE amount < 0 AND grpcode != '001' AND grpcode != '002' 
                                   AND date >= '$mfrmdate' AND date <= '$mtodate' $seriescond ORDER BY vouchno,date,source") or die(mysqli_error($conn));
$fetexp = mysqli_fetch_array($gexp);
$ramt = $fetexp['ramt'];

$fexp = abs($ramt);

$sumamt = $lamt - $fexp;



$getincome = mysqli_query($conn, "SELECT *,DATE_FORMAT(`date`, '%d-%m-%Y')as date FROM $maindb WHERE amount > 0 AND grpcode != '001' AND grpcode != '002'
                                            AND date >= '$mfrmdate' AND date <= '$mtodate' $seriescond group by source,vouchno
                                            ORDER BY vouchno,date,source") or die(mysqli_error($conn));

$getexpenses = mysqli_query($conn, "SELECT *,DATE_FORMAT(`date`, '%d-%m-%Y')as date  FROM $maindb WHERE amount < 0 AND grpcode != '001' AND grpcode != '002' 
                                            AND date >= '$mfrmdate' AND date <= '$mtodate' $seriescond group by source,vouchno
                                            ORDER BY vouchno,date,source") or die(mysqli_error($conn));


// $get = mysqli_num_rows($getpmain);

include("includes/linkshead.php");

if (isset($_POST["view"])) {
    ?>
    <style type="text/css">
        #tabital {
            font-style: italic;
        }

        #font1 {
            font-style: italic;
        }

        #boldfont {
            font-weight: bold;
        }
    </style>
    </head>

    <body>
        <div class="container">
            <?php include("includes/head_rep.php"); ?>
            <div class="row">
                <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%;">
                                <div style="font-size: 15px;font-weight:bold"> &nbsp; Income</div>
                                <table class="table table-condensed table-hover table-bordered" style="float: left; margin: 2px">
                                    <thead class="mySearchTr">
                                        <tr>
                                            <th style="width:8%">Date</th>
                                            <th style="width:5%">Source</th>
                                            <th style="width:25%;">Account</th>
                                            <th style="width:12%;text-align:right">Amount</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php
                                        $ctr = 0;
                                        $sontot = 0;
                                        $totamt = 0;

                                        while ($pincome = mysqli_fetch_array($getincome)) {

                                            $gname = mysqli_query($conn, "SELECT accode,name FROM `accmast` where accode = '$pincome[accode]' ")or die(mysqli_error($conn));
                                            $fname = mysqli_fetch_array($gname);

                                            $name = $fname['name'];

                                            $incomeamt = number_format($pincome['amount'], 2);

                                            $ctr++;
                                            echo "<tr id='boldfont'>";
                                            echo "<td>$pincome[date]</td>";
                                            echo "<td>$pincome[source]</td>";
                                            echo "<td>$name</td>";
                                            echo "<td style='text-align:right;'>$incomeamt</td>";

                                            $intot = $pincome['amount'] + $intot;

                                            $totamt = number_format($intot, 2);

                                            echo "</tr>";
                                        }
                                        echo "<tr id='boldfont' style='font-weight:bold'>";

                                        echo "<td></td>";
                                        echo "<td></td>";
                                        echo "<td style='text-align:right;'>Total</td>";
                                        echo "<td style='text-align:right;'>$totamt</td>";
                                        echo "</tr>";

                                        if ($sumamt < 0) {
                                            $smamt = abs($sumamt);
                                            echo "<tr id='boldfont' style='background-color:#FFFF00;color:red'>";
                                            echo "<td></td>";
                                            echo "<td></td>";
                                            echo "<td style='text-align:right;'>Balance</td>";
                                            echo "<td style='text-align:right;'>" . number_format($smamt, 2) . "</td>";
                                            echo "</tr>";
                                        }
                                        ?>
                                    </tbody>
                                </table>
                            </td>


                            <td style="vertical-align: top;width: 50%;">
                                <div style="font-size: 15px;font-weight:bold"> &nbsp; Expenses</div>
                                <table class="table table-condensed table-hover table-bordered" style="float: left; margin: 2px">
                                    <thead class="mySearchTr">
                                        <tr>
                                            <th style="width:8%">Date</th>
                                            <th style="width:5%">Source</th>
                                            <th style="width:25%;">Account</th>
                                            <th style="width:12%;text-align:right">Amount</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php
                                        $ctr = 0;
                                        $sontot = 0;
                                        $amttot = 0;

                                        while ($pexp = mysqli_fetch_array($getexpenses)) {

                                            $gname = mysqli_query($conn, "SELECT accode,name FROM `accmast` where accode = '$pexp[accode]' ")or die(mysqli_error($conn));
                                            $fname = mysqli_fetch_array($gname);

                                            $ename = $fname['name'];

                                            $absamt = abs($pexp['amount']);

                                            $amount = number_format($absamt, 2);

                                            $ctr++;
                                            echo "<tr id='boldfont'>";
                                            echo "<td>$pexp[date]</td>";
                                            echo "<td>$pexp[source]</td>";
                                            echo "<td>$ename</td>";
                                            echo "<td style='text-align:right;'>$amount</td>";
                                            echo "</tr>";

                                            $exptot = $pexp['amount'] + $exptot;

                                            $amttot = number_format(abs($exptot), 2);
                                        }


                                        echo "<tr id='boldfont'>";
                                        echo "<td></td>";
                                        echo "<td></td>";
                                        echo "<td style='text-align:right;'>Total</td>";
                                        echo "<td style='text-align:right;'>$amttot</td>";
                                        echo "</tr>";

                                        if ($sumamt > 0) {
                                            echo "<tr id='boldfont' style='background-color:#FFFF00;color:red'>";
                                            echo "<td></td>";
                                            echo "<td></td>";
                                            echo "<td style='text-align:right;'>Balance</td>";
                                            echo "<td style='text-align:right;'>" . number_format($sumamt, 2) . "</td>";
                                            echo "</tr>";
                                        }
                                        ?>
                                    </tbody>
                            </td>
                    </table>

                    </tr>

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

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

    </html>
    <?php
}
if (isset($_POST["excel"])) {

    $contents .= "Voucher,Date,Father Name,Son Name,Amount,Father Name,Daugther Name,Amount,English Date,Nikah Location\n";

    $ctr = 0;
    $sontot = 0;
    $dautot = 0;

    while ($pmain = mysqli_fetch_array($getpmain)) {

        $time = date('h:i A', strtotime($pmain['engtime']));

        $address = $pmain['add1'] . $pmain['add2'] . $pmain['add3'];

        $ctr++;


        $contents .= "$pmain[vouchno]" . ",";
        $contents .= "$pmain[vouchdt]" . ",";
        $contents .= "$pmain[memno]" . "$pmain[memname]" . ",";
        $contents .= "$pmain[sonno]" . "$pmain[sonname]" . ",";
        $contents .= "$pmain[sonamt]" . ",";
        $contents .= "$pmain[fathno]" . "$pmain[fathname]" . ",";
        $contents .= "$pmain[dauno]" . "$pmain[dauname]" . ",";
        $contents .= "$pmain[dauamt]" . ",";
        $contents .= "$pmain[englishdt]" . "$pmain[engday]" . "$time" . ",";
        $contents .= "$address" . "\n";

        $sontot = $pmain['sonamt'] + $sontot;
        $dautot = $pmain['dauamt'] + $dautot;
    }
    $contents .= "" . ",";
    $contents .= "" . ",";
    $contents .= "" . ",";
    $contents .= "Total" . ",";
    $contents .= "$sontot" . ",";
    $contents .= "" . ",";
    $contents .= "" . ",";
    $contents .= "$dautot" . ",";
    $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;
    }
}
?>