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

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

$zyear = $_SESSION["tit_" . $dbname];

$formname = "JV - GST";
$maindb = "jvmain";
$trandb = "jvtran";

//$mpara = $_GET["para"];

$bgrp = "001";
$msource1 = "JV";
//    $msource2 = "BP";

$mfrmdate = trim($_POST["frmdate"]);
$mtodate = trim($_POST["todate"]);


$mfdt = date("d-m-Y", strtotime($mfrmdate));
$mtdt = date("d-m-Y", strtotime($mtodate));


if (isset($_POST["submit"])) {

//    $acond = "";
//
//    if (!empty($_POST["accode"])) {
//
//        $code = trim($_POST["accode"]);
//        $acond = "AND b.`accode`='$code'";
//    }
    $mfrmdate = trim($_POST["frmdate"]);
    $mtodate = trim($_POST["todate"]);

    if ($mfrmdate > $mtodate) {

        browse("To Date Cannot Be Less Than From Date");
        header("refresh: 1; jvbook.php");
        exit();
    }


    $repname = "JV - GST";
    $acc = mysqli_query($conn, "SELECT *,  DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt FROM `$maindb` 
                        WHERE gst = 'Y' and `vouchdt` >= '$mfrmdate' and `vouchdt`<='$mtodate' order by `vouchdt`") or die(mysqli_error($conn));
    
    include("includes/linkshead.php");
    ?>
    <body>
        <div class="container">
            <br>
            <?php include("includes/head_rep.php");
            ?>
            <div class="row">
                <div class="col-lg-12 pad">
                    <table class="table table-condensed table-bordered table-hover table-striped"  width='100%' border="0">  
                        <tr style="color:black;background-color: #26A69A">
                            <th style="width : 8%">Date</th>
                            <th style="width : 5%">Voucher</th>
                            <th style="width : 5%">Bill No.</th>
                            <th style="width : 20%">Party Name</th>
                            <th style="width : 10%">GST NO.</th>
                            <th style="width : 20%">Expense</th>
                            <th style="width : 15%">GST Type</th>                               
                            <th style="text-align:right; width : 6%">Net Amt.</th>                                   
                            <th style="text-align:right; width : 7%">GST</th>                                   
                            <th style="text-align:right; width : 7%">Total</th>  
                        </tr>
                        <tbody>
                            <?php
                            $totnet = $totgst = $totamt = 0;

                            while ($row = mysqli_fetch_array($acc)) {
                                $vdate = $row['mydt'];
                                $vouchno = $row['vouchno'];
                                $billno = $row['billno'];
                                $amount = $row['totdebit'];

                                $getparty = mysqli_query($conn, "SELECT a.accode, b.name, b.gstno  FROM `jvtran` a, accmast b WHERE a.vouchno = '$vouchno' 
                                                         and a.credit > 0  and a.`accode` = b.`accode` and (b.grpcode = '021' OR b.grpcode = '009') ") or die(mysqli_error($conn));
                                
                                $party = mysqli_fetch_array($getparty);
                                
                                $partyname = $party['name'];
                                $gstno = $party['gstno'];


                                $getexp = mysqli_query($conn, "SELECT a.accode, b.name  FROM `jvtran` a, accmast b WHERE a.vouchno = '$vouchno' 
                                                       and a.debit > 0  and a.`accode` = b.`accode` and b.grpcode != '020' ") or die(mysqli_error($conn));
                                $fetexp = mysqli_fetch_array($getexp);
                                $expname = $fetexp['name'];


                                $getgst = mysqli_query($conn, "SELECT a.accode, sum(a.debit) as dbamt, b.name  FROM `jvtran` a, accmast b
                                                       WHERE a.vouchno = '$vouchno' and a.debit > 0  and a.`accode` = b.`accode` and b.grpcode = '020' 
                                                       group by a.vouchno") or die(mysqli_error($conn));
                                $fetgst = mysqli_fetch_array($getgst);
                                $gsttype = $fetgst['name'];
                                $gstamt = $fetgst['dbamt'];

                                $netamt = $amount - $gstamt;

                                echo "<tr style='background-color:white'>";
                                echo "<td>$vdate</td>";
                                echo "<td>$vouchno</td>";
                                echo "<td>$billno</td>";
                                echo "<td>$partyname</td>";
                                echo "<td>$gstno</td>";
                                echo "<td>$expname</td>";
                                echo "<td>$gsttype</td>";
                                echo "<td class='text-right'>$netamt</td>";
                                echo "<td class='text-right'>$gstamt</td>";
                                echo "<td class='text-right'>$amount</td>";
                                echo "</tr>";

                                $totnet = $totnet + $netamt;
                                $totgst = $totgst + $gstamt;
                                $totamt = $totamt + $amount;
                            }

                            echo "<tr style='background-color: #26A69A'>";
                            echo "<td>&nbsp;</td>";
                            echo "<td>&nbsp;</td>";
                            echo "<td>&nbsp;</td>";
                            echo "<td>&nbsp;</td>";
                            echo "<td>&nbsp;</td>";
                            echo "<td>&nbsp;</td>";
                            echo "<td class='text-right'>Total</td>";
                            echo "<td class='text-right'>$totnet</td>";
                            echo "<td class='text-right'>$totgst</td>";
                            echo "<td class='text-right'>$totamt</td>";
                            echo "</tr>";
                            ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
        <?php
        include("includes/linksfoot.php");
    }
    if (isset($_POST["excel"])) {

        $mfrmdate = trim($_POST["frmdate"]);
        $mtodate = trim($_POST["todate"]);

        if ($mfrmdate > $mtodate) {

            browse("To Date Cannot Be Less Than From Date");
            header("refresh: 1; jvbook.php");
            exit();
        }

        $repname = "JV - GST";
        $acc = mysqli_query($conn, "SELECT *,  DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt FROM `$maindb` 
                        WHERE gst = 'Y' and `vouchdt` >= '$mfrmdate' and `vouchdt`<='$mtodate' order by vouchdt") or die(mysqli_error($conn));

        $contents .= "Date, Voucher, Bill No., Party Name, GST NO.,Expenses, GST Type, Net Amount, GST, Total\n";
        $totnet = $totgst = $totamt = 0;

        while ($row = mysqli_fetch_array($acc)) {
            $vdate = $row['mydt'];
            $vouchno = $row['vouchno'];
            $billno = $row['billno'];
            $amount = $row['totdebit'];

            $getparty = mysqli_query($conn, "SELECT a.accode, b.name ,b.gstno  FROM `jvtran` a, accmast b WHERE a.vouchno = '$vouchno' 
                                                         and a.credit > 0  and a.`accode` = b.`accode` and (b.grpcode = '021' OR b.grpcode = '009') ") or die(mysqli_error($conn));
            $party = mysqli_fetch_array($getparty);
            $partyname = $party['name'];
            $gstno = $party['gstno'];


            $getexp = mysqli_query($conn, "SELECT a.accode, b.name  FROM `jvtran` a, accmast b WHERE a.vouchno = '$vouchno' 
                                                       and a.debit > 0  and a.`accode` = b.`accode` and b.grpcode != '020' ") or die(mysqli_error($conn));
            $fetexp = mysqli_fetch_array($getexp);
            $expname = $fetexp['name'];


            $getgst = mysqli_query($conn, "SELECT a.accode, sum(a.debit) as dbamt, b.name  FROM `jvtran` a, accmast b
                                                       WHERE a.vouchno = '$vouchno' and a.debit > 0  and a.`accode` = b.`accode` and b.grpcode = '020' 
                                                       group by a.vouchno") or die(mysqli_error($conn));
            $fetgst = mysqli_fetch_array($getgst);
            $gsttype = $fetgst['name'];
            $gstamt = $fetgst['dbamt'];

            $netamt = $amount - $gstamt;

            $contents .= $vdate . ",";
            $contents .= $vouchno . ",";
            $contents .= $billno . ",";
            $contents .= $partyname . ",";
            $contents .= $gstno . ",";
            $contents .= $expname . ",";
            $contents .= $gsttype . ",";
            $contents .= $netamt . ",";
            $contents .= $gstamt . ",";
            $contents .= $amount . "\n";

            $totnet = $totnet + $netamt;
            $totgst = $totgst + $gstamt;
            $totamt = $totamt + $amount;
        }

        $contents .= ",,,,,,Total,";
        $contents .= $totnet . ",";
        $contents .= $totgst . ",";
        $contents .= $totamt . "\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;
        }
    }
    ?>        