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

$formname = 'Sale GST Register';
$maindb = 'gstmain';
$trandb = 'gsttran';

$grid = 'slgstreg.php';

if (isset($_POST["view"])) {
    $pcond = "";
    $pgodowncond = "";
    $cashcredcond = "";

    if (!empty($_POST["name"])) {
        $pname = trim($_POST["name"]);
        $pcond = "AND `accode` = '$pname'";
    }


    if (empty($_POST["frmdt"])) {
        browse("From Date Cannot Be Empty");
        header("refresh:1; slgstreg.php");
    }
    if (empty($_POST["todt"])) {
        browse("To Date Cannot Be Empty");
        header("refresh:1; slgstreg.php");
    }


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

    $get = mysqli_query($conn, "SELECT *, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt FROM `$maindb` WHERE
                       `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' $pcond") or die(mysqli_error($conn));

    $getno = mysqli_num_rows($get);
    include("includes/files.php");
    ?>

    </head>
    <body>

        <?php
        require("includes/dbconnect.php");
        $comp = mysqli_query($conn, "SELECT * FROM `company`") or die(mysql_errno());
        $zcomp = mysqli_fetch_assoc($comp);
        echo "<font size='4px'><b>$zcomp[compname]</font></b></font>";
        echo "<font size='2px'><b> $zcomp[add1]. $zcomp[add2].  $zcomp[add3] </b></font><br />";
        echo "Report Date : " . date("d-m-Y");
        echo "&nbsp;&nbsp;&nbsp;&nbsp;(From Date : " . date("d-m-Y", strtotime($mfrmdate)) . " &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  To Date : " . date("d-m-Y", strtotime($mtodate)) . ")" . "<br>";
        echo "Total Records : $getno";
        echo "<br />";
        echo "</font>";
        ?>

        <div class="row">
            <div class="col-lg-6" style="width:100%;">
                <div class="box">
                    <header style="border: 2px solid #809FD9">
                        <h5><?php echo $formname; ?></h5>
                        <div class="toolbar">
                            <div class="btn-group">		  
                                <div class="btn-group">

                                    <a href="#sortableTable" data-toggle="collapse" class="btn btn-default btn-sm minimize-box">
                                        <i class="fa fa-angle-up"></i>
                                    </a>

                                    <input type='button' name='submit' class='btn btn-sm btn-danger' onClick="window.location.href = '<?php echo $grid; ?>'" value="Exit"  value='Save'/> 
                                </div>
                            </div>
                        </div>
                    </header>
                    <div id="sortableTable" class="body collapse in">
                        <!--<table class="table table-bordered sortableTable responsive-table">-->
                        <table style="width:100%" class="table-bordered sortableTable responsive-table">    
                            <thead>
                                <tr style="height:10px">
                                    <th align="left" width='10%'>Invoice No.<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <th align="left" width='10%'>Date<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <th align="left" width='40%'>Party Name<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <th align="right" width='10%' style="text-align: right;">Total Qty<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <th align="right" width='10%' style="text-align: right;">Amount<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <!--<th align="right" width='10%' style="text-align: right;">Discount<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>-->
                                    <th align="right" width='10%' style="text-align: right;">Packing<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <th align="right" width='10%' style="text-align: right;">Taxable Value<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <th align="right" width='10%' style="text-align: right;">SGST Rate<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <th align="right" width='10%' style="text-align: right;">SGST Amount<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <th align="right" width='10%' style="text-align: right;">CGST Rate<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <th align="right" width='10%' style="text-align: right;">CGST Amount<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <th align="right" width='10%' style="text-align: right;">IGST Rate<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <th align="right" width='10%' style="text-align: right;">IGST Amount<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <th align="right" width='10%' style="text-align: right;">Bill Amount<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php
                                $totamt = 0;
                                $tottaxamt = 0;
                                $billamt = 0;
                                $igstamt = 0;
                                $cgstamt = 0;
                                $sgstamt = 0;
                                $tdisc = 0;
                                $tvat = 0;
                                $tpack = 0;
                                $tsubqty = 0;

                                $ctr = 0;
                                while ($row = mysqli_fetch_object($get)) {
                                    mysqli_select_db($conn, $zdb);
                                    $getparty = mysqli_query($conn, "SELECT * FROM `accmast` WHERE  `accode`='$row->accode' ORDER BY  `name`") or die(mysqli_error($conn));
                                    $party = mysqli_fetch_object($getparty);

                                    $getgodown = mysqli_query($conn, "SELECT * FROM `godown` WHERE  `code`='$row->godown' ORDER BY  `name`") or die(mysqli_error($conn));
                                    $gdwn = mysqli_fetch_object($getgodown);
                                    $ctr++;
                                    echo "<tr style='height:10px'> ";
                                    echo "<td align='left'>$row->vouchno</td>";
                                    echo "<td align='left'>$row->mydt</td>";
                                    echo "<td align='left'>$party->name</td>";
                                    echo "<td align='right'>$row->subqty</td>";
                                    echo "<td align='right'>$row->subtotal</td>";
//                                    echo "<td align='right'>$row->discount</td>";
                                    echo "<td align='right'>$row->packing</td>";
                                    echo "<td align='right'>$row->tottaxamt</td>";
                                    echo "<td align='right'>$row->sgstperc</td>";
                                    echo "<td align='right'>$row->totsgst</td>";
                                    echo "<td align='right'>$row->cgstperc</td>";
                                    echo "<td align='right'>$row->totcgst</td>";
                                    echo "<td align='right'>$row->igstperc</td>";
                                    echo "<td align='right'>$row->totigst</td>";
                                    echo "<td align='right'>$row->billamt</td>";


                                    $totigst = $totigst + $row->totigst;
                                    $totcgst = $totcgst + $row->totcgst;
                                    $totsgst = $totsgst + $row->totsgst;
                                    $tottaxamt = $tottaxamt + $row->tottaxamt;
                                    $totamt = $totamt + $row->subtotal;
                                    $tdisc = $tdisc + $row->discount;
                                    $tpack = $tpack + $row->packing;
                                    $billamt = $billamt + $row->billamt;
                                    $tsubqty += $row->subqty;

                                    echo "</tr>";
                                    if ($_POST["dtype"] == 2) {
//                                                $gettran = mysqli_query($conn, "SELECT * FROM `$trandb` WHERE vouchno='$row->vouchno' ORDER BY vouchno") or die(mysqli_error($conn));
                                        $gettran = mysqli_query($conn, "SELECT a.*, b.name as item FROM `$trandb` a, item b 
                                                           WHERE a.vouchno='$row->vouchno' AND a.itemcode = b.code ORDER BY vouchno, srno") or die(mysqli_error($conn));

                                        $style = "calibri";
                                        $size = '2px';
                                        echo "<tr style='height:0px'>";
                                        echo "<td colspan='25'>";
                                        echo "<table style='width:100%;' class='table-bordered sortableTable responsive-table'>";
                                        echo "<th align='left' width='4%'><font face=$style size='$size'>Sr.</font></th>";
                                        echo "<th align='left' width='15%'><font face=$style size='$size'>Barcode</font></th>";
                                        echo "<th align='left' width='30%'><font face=$style size='$size'>Item</font></th>";
                                        echo "<th align='left' width='15%' style=\"text-align:left;\"'><font face=$style size='$size'>HSN Code</font></th>";
                                        echo "<th align='right' width='10%' text-align: right; style=\"text-align:right;\"'><font face=$style size='$size'>Qty.</font></th>";
                                        echo "<th align='right' width='10%' text-align: right; style=\"text-align:right;\"'><font face=$style size='$size'>Rate</font></th>";
                                        echo "<th align='right' width='10%' text-align: right; style=\"text-align:right;\"'><font face=$style size='$size'>Amount</font></th>";
                                        echo "<tr style='height:0px'>";
                                        $ctrdtl = 0;
                                        $totqty2 = 0;
                                        $totamt2 = 0;

                                        while ($row2 = mysqli_fetch_object($gettran)) {

                                            $ctrdtl++;


                                            echo "<tr style='height:10px'>";
                                            echo "<td style=\"text-align:right;\" valign='top' width=\"6\"> \n <span><font face=$style size='$size'> $ctrdtl</font> </span> \n </td> \n";
                                            echo "<td style=\"text-align:left;\" valign='top'> \n <span><font face=$style size='$size'> $row2->barcode</font> </span> \n </td> \n";
                                            echo "<td style=\"text-align:left;\" valign='top'> \n <span><font face=$style size='$size'> $row2->item</font> </span> \n </td> \n";
                                            echo "<td style=\"text-align:left;\" valign='top' width=\"6\"> \n <span><font face=$style size='$size'> $row2->hsncode</font> </span> \n </td> \n";
                                            echo "<td style=\"text-align:right;\" valign='top' width=\"6\"> \n <span><font face=$style size='$size'> $row2->qty</font> </span> \n </td> \n";
                                            echo "<td style=\"text-align:right;\" valign='top' width=\"6\"> \n <span><font face=$style size='$size'> $row2->rate</font> </span> \n </td> \n";
                                            echo "<td style=\"text-align:right;\" valign='top' width=\"6\"> \n <span><font face=$style size='$size'> $row2->totamt</font> </span> \n </td> \n";
                                            echo "</tr>";

                                            $totqty2 = $totqty2 + $row2->qty;
                                            $totamt2 = $totamt2 + $row2->totamt;
                                        }
                                        echo "<tr style='height:10px'>";
                                        echo "<td style=\"text-align:right;\" valign='top' width=\"6\"> \n <span><font face=$style size='$size'> </font> </span> \n </td> \n";
                                        echo "<td style=\"text-align:left;\" valign='top'> \n <span><font face=$style size='$size'> </font> </span> \n </td> \n";
                                        echo "<td style=\"text-align:left;\" valign='top'> \n <span><font face=$style size='$size'> </font> </span> \n </td> \n";
                                        echo "<td style=\"text-align:right;\" valign='top' width=\"6\"> \n <span><font face=$style size='$size'> Total</font> </span> \n </td> \n";
                                        echo "<td style=\"text-align:right;\" valign='top' width=\"6\"> \n <span><font face=$style size='$size'>$totqty2</font> </span> \n </td> \n";
                                        echo "<td style=\"text-align:right;\" valign='top' width=\"6\"> \n <span><font face=$style size='$size'></font> </span> \n </td> \n";
                                        echo "<td style=\"text-align:right;\" valign='top' width=\"6\"> \n <span><font face=$style size='$size'>$totamt2</font> </span> \n </td> \n";
                                        echo "</tr>";

                                        echo "</table>";
                                        echo "</td>";
                                    }
                                }
                                echo "<tr style='height:10px'> ";
                                echo "<td align='left'></td>";
                                echo "<td align='right'><font><b></b></font></td>";
//                                echo "<td align='right'><font><b></b></font></td>";
                                echo "<td align='right'><font><b>Total&nbsp;&nbsp;</font></b></td>";
                                echo "<td align='right'><font><b>" . number_format($tsubqty, 2, '.', '') . "</b></font></td>";
                                echo "<td align='right'><font><b>" . number_format($totamt, 2, '.', '') . "</b></font></td>";
//                                echo "<td align='right'><font><b>" . number_format($tdisc, 2, '.', '') . "</b></font></td>";
                                echo "<td align='right'><font><b>" . number_format($tpack, 2, '.', '') . "</b></font></td>";
                                echo "<td align='right'><font><b>" . number_format($tottaxamt, 2, '.', '') . "</b></font></td>";
                                echo "<td align='right'><font><b></b></font></td>";
                                echo "<td align='right'><font color='green'><b>" . number_format($totsgst, 2, '.', '') . "</b></font></td>";
                                echo "<td align='right'><font><b></b></font></td>";
                                echo "<td align='right'><font color='green'><b>" . number_format($totcgst, 2, '.', '') . "</b></font></td>";
                                echo "<td align='right'><font><b></b></font></td>";
                                echo "<td align='right'><font color='green'><b>" . number_format($totigst, 2, '.', '') . "</b></font></td>";
                                echo "<td align='right'><font color='green'><b>" . number_format($billamt, 2, '.', '') . "</b></font></td>";
                                echo "</tr>";
                                ?>


                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
            <!-- /.col-lg-6 -->
        </div>  

        <script src="//ajax.googleapis.com/ajax/libs/jquery/2.0.3/jquery.min.js"></script>
        <script>window.jQuery || document.write('<script src="assets/lib/jquery.min.js"><\/script>')</script> 
        <script src="assets/lib/bootstrap/js/bootstrap.js"></script>
        <script src="//ajax.googleapis.com/ajax/libs/jqueryui/1.10.3/jquery-ui.min.js"></script>
        <script src="assets/lib/datatables/jquery.dataTables.js"></script>
        <script src="assets/lib/datatables/DT_bootstrap.js"></script>
        <script src="assets/lib/tablesorter/js/jquery.tablesorter.min.js"></script>
        <script src="assets/lib/touch-punch/jquery.ui.touch-punch.min.js"></script>
        <script src="assets/js/main.js"></script>
        <script>
                                        $(function () {
                                            metisTable();
                                            metisSortable();
                                        });
        </script>
    </body>
    </html>
    <?php
    $contents = "Invoice No.,Date,Party Name,Total Qty, Amount,Packing,Taxable Value,SGST Rate,SGST Amount,CGST Rate,CGST Amount,"
            . "IGST Rate,IGST Amount,Bill Amount\n";
}
// Excel part
if (isset($_POST["export"])) {
    $pcond = "";
    $pgodowncond = "";
    $cashcredcond = "";

    if (!empty($_POST["name"])) {
        $pname = trim($_POST["name"]);
        $pcond = "AND `accode` = '$pname'";
    }


    if (empty($_POST["frmdt"])) {
        browse("From Date Cannot Be Empty");
        header("refresh:1; slgstreg.php");
    }
    if (empty($_POST["todt"])) {
        browse("To Date Cannot Be Empty");
        header("refresh:1; slgstreg.php");
    }


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

    $get = mysqli_query($conn, "SELECT *, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt FROM `$maindb` WHERE
                       `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' $pcond") or die(mysqli_error($conn));


    $getno = mysqli_num_rows($get);




    $contents.= "Invoice No.,Date,Party Name,Total Qty ,Amount,Packing,Taxable Value,SGST Rate,SGST Amount,CGST Rate,CGST Amount,"
            . "IGST Rate,IGST Amount,Bill Amount,\n";


    $totamt = 0;
    $tottaxamt = 0;
    $billamt = 0;
    $igstamt = 0;
    $cgstamt = 0;
    $sgstamt = 0;
    $tdisc = 0;
    $tvat = 0;
    $tpack = 0;
    $tsubqty = 0;

    $ctr = 1;
    while ($row = mysqli_fetch_object($get)) {
        mysqli_select_db($conn, $zdb);
        $getparty = mysqli_query($conn, "SELECT * FROM `accmast` WHERE  `accode`='$row->accode' ORDER BY  `name`") or die(mysqli_error($conn));
        $party = mysqli_fetch_object($getparty);

        $getgodown = mysqli_query($conn, "SELECT * FROM `godown` WHERE  `code`='$row->godown' ORDER BY  `name`") or die(mysqli_error($conn));
        $gdwn = mysqli_fetch_object($getgodown);

        $pvouchno = $row->vouchno;
        $pmydt = $row->mydt;
        $pname = $party->name;
        $ppacking = $row->packing;
        $psubqty = $row->subqty;
        $psubtotal = $row->subtotal;
        $ptottaxamt = $row->tottaxamt;
        $psgstperc = $row->sgstperc;
        $ptotsgst = $row->totsgst;
        $pcgstperc = $row->cgstperc;
        $ptotcgst = $row->totcgst;
        $pigstperc = $row->igstperc;
        $ptotigst = $row->totigst;
        $pbillamt = $row->billamt;


        $contents.= $pvouchno . ",";
        $contents.= $pmydt . ",";
        $contents.= $pname . ",";
        $contents.= $psubqty . ",";
        $contents.= $psubtotal . ",";
        $contents.= $ppacking . ",";
        $contents.= $ptottaxamt . ",";
        $contents.= $psgstperc . ",";
        $contents.= $ptotsgst . ",";
        $contents.= $pcgstperc . ",";
        $contents.= $ptotcgst . ",";
        $contents.= $pigstperc . ",";
        $contents.= $ptotigst . ",";
        $contents.= $pbillamt . "\n";

        $ctr++;

        $totigst = $totigst + $row->totigst;
        $totcgst = $totcgst + $row->totcgst;
        $totsgst = $totsgst + $row->totsgst;
        $tottaxamt = $tottaxamt + $row->tottaxamt;
        $totamt = $totamt + $row->subtotal;
        $tdisc = $tdisc + $row->packing;
        $billamt = $billamt + $row->billamt;
        $tsubqty += $psubqty;

        if ($_POST["dtype"] == 2) {
            $gettran = mysqli_query($conn, "SELECT a.*, b.name as item FROM `$trandb` a, item b 
                                                           WHERE a.vouchno='$row->vouchno' AND a.itemcode = b.code ORDER BY vouchno, srno") or die(mysqli_error($conn));

            $contents.="Sr.No,Barcode,Item,HSN Code,Qty.,Rate,Amount\n";

            $style = "calibri";
            $size = '2px';

            $ctrdtl = 0;
            $totqty2 = 0;
            $totamt2 = 0;

            while ($row2 = mysqli_fetch_object($gettran)) {

                $ctrdtl++;

                $tbarcode = $row2->barcode;
                $titem = $row2->item;
                $thsncode = $row2->hsncode;
                $tqty = $row2->qty;
                $trate = $row2->rate;
                $ttotamt = $row2->totamt;

                $contents.= $ctrdtl . ",";
                $contents.= $tbarcode . ",";
                $contents.= $titem . ",";
                $contents.= $thsncode . ",";
                $contents.= $tqty . ",";
                $contents.= $trate . ",";
                $contents.= $ttotamt . "\n";

                $totqty2 = $totqty2 + $row2->qty;
                $totamt2 = $totamt2 + $row2->totamt;
            }

            $contents.= " " . ",";
            $contents.= " " . ",";
            $contents.= " " . ",";
            $contents.= "Total" . ",";
            $contents.= $totqty2 . ",";
            $contents.= " " . ",";
            $contents.= $totamt2 . "\n";
        }
    }

    $contents.= " " . ",";
    $contents.= " " . ",";
//    $contents.= " " . ",";
    $contents.= "Total" . ",";
    $contents.= $tsubqty . ",";
    $contents.= $totamt . ",";
    $contents.= $tdisc . ",";
    $contents.= $tottaxamt . ",";
    $contents.= " " . ",";
    $contents.= $totsgst . ",";
    $contents.= " " . ",";
    $contents.= $totcgst . ",";
    $contents.= " " . ",";
    $contents.= $totigst . ",";
    $contents.= $billamt . "\n";

    $contents = strip_tags($contents);

    if ($_POST["dtype"] == 1) {
        header("Content-Disposition: attachment; filename=SaleRegisterSummary" . date('d-m-Y') . ".csv");
        print $contents;
    } else {
        header("Content-Disposition: attachment; filename=SaleRegisterDetail" . date('d-m-Y') . ".csv");
        print $contents;
    }
}
