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

$formname = "VAT Report";
$grid = "vatrep.php";
$zroot = 'cepheisy_msp_root';

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

if (isset($_POST["view"])) {
    $tpcond = "";
    $type = trim($_POST["dtype"]);
    if (!empty($type)) {
        if ($type == 1) {
            $tpcond = " and `vatperc` > 0 ";
        } else if ($type == 2) {
            $tpcond = " and `vatperc` <= 0 ";
        }
    }
    $mfrmdate = trim($_POST["frmdt"]);
    $mtodate = trim($_POST["todt"]);

    $getpur = mysqli_query($conn, "SELECT 'PU' as source, 1 as ord, vatperc, SUM(subtotal) as subtotal, SUM(litemdisc+cdiscamt+lesother) as discount, 
                        SUM(netamt) as nettotal, SUM(vatamt) as vatamt
                        FROM purmain WHERE vouchdt >= '$mfrmdate' AND vouchdt <= '$mtodate' $tpcond
                        GROUP BY vatperc ORDER BY vatperc") or die(mysqli_error($conn));



    $getpurret = mysqli_query($conn, "SELECT 'PR' as source, 2 as ord, vatperc, SUM(subtotal) as subtotal, SUM(litemdisc+cdisamt+lessoth) as discount, 
                        SUM(netamt) as nettotal, SUM(vatamt) as vatamt
                        FROM debmain WHERE vouchdt >= '$mfrmdate' AND vouchdt <= '$mtodate' $tpcond
                        GROUP BY vatperc ORDER BY vatperc") or die(mysqli_error($conn));

    $getsal = mysqli_query($conn, "SELECT 'SL' as source, 3 as ord, vatperc, SUM(subtotal) as subtotal, SUM(litemdisc+cdiscamt) as discount, 
                        SUM(netamt) as nettotal, SUM(vatamt) as vatamt
                        FROM salemain WHERE vouchdt >= '$mfrmdate' AND vouchdt <= '$mtodate' $tpcond
                        GROUP BY vatperc ORDER BY vatperc") or die(mysqli_error($conn));

    $getsalret = mysqli_query($conn, "SELECT 'SR' as source, 4 as ord, vatperc, SUM(subtotal) as subtotal, SUM(litemdisc+cdisamt+lessoth) as discount, 
                        SUM(netamt) as nettotal, SUM(vatamt) as vatamt
                        FROM credmain WHERE vouchdt >= '$mfrmdate' AND vouchdt <= '$mtodate' $tpcond
                        GROUP BY vatperc ORDER BY vatperc") or die(mysqli_error($conn));

    $getno = mysqli_num_rows($getpur);
    include("includes/linkshead.php");
//    $mycomp = $_SESSION["comp_" . $dbname];
//
//    $getcomp = mysqli_query($conn, "SELECT *,UCASE(name)as cname FROM $zroot.`company` WHERE `code`='$mycomp'")or die(mysqli_error($conn));
//    $comp = mysqli_fetch_array($getcomp);

    $mfdt = date("d-m-Y", strtotime($mfrmdate));
    $mtdt = date("d-m-Y", strtotime($mtodate));
    ?>
    <link href="MyStyle/rep_format.css" rel="stylesheet">
    
    <style>
        h5{
            font-size: 18px;
            margin-top: 7px;
            margin-left: 6px;
        }
        .report{
            margin-top: 0px;
        }
    </style>
    </head>
    <body>
        <div class="container">
            <?php
                include("includes/head_rep.php");
                ?>
<!--            <div class="row">
                <div class="row">
                    <div class="col-lg-12 col-md-12 col-sm-12 col-xs-12 text-center">
                        <?php echo "<label>$comp[cname]</label>" ?><br>
                        <?php echo "$comp[add1] . $comp[add2] . $comp[add3]" ?>
                    </div>
                </div>
                <div class="row">
                    <div class="col-lg-11 col-md-5 col-sm-5 col-xs-5">

                        <label class="label-control marg"><?php Echo $formname; ?></label>
                    </div>
                    <div class="col-lg-1 col-md-1 col-sm-1 col-xs-1 ">
                        <a onclick="window.close()" class="btn btn-primary noPrint">GO Back</a>
                    </div>
                </div>
                <div class="row">
                    <div class="col-lg-12 col-md-12 col-sm-12 col-xs-12">
                        From Date - 
                        <?php Echo $mfdt; ?> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                        To Date - 
                        <?php Echo $mtdt; ?>
                    </div>
                </div>-->
            </div>
            <div class="row" style="margin-top: 15px;">
                <div class="col-lg-6" style="width:100%;">
                    <div class="box">                    
                        <?php // include("includes/head_rep.php"); ?>                    
                        <div id="sortableTable" class="body collapse in">
                            <div class="row" style="background-color: ">
                                <div class="col-md-6">
                                    <h5 style="color: #78909C"><b>Purchase</b></h5>
                                </div>                                
                            </div>
                            <table style="width:100%" class="table-bordered sortableTable responsive-table report">     
                                <thead style="background-color: #78909C;">
                                    <tr>
                                        <th style="width: 10%; ">Sr. No.</th>
                                        <th style="width: 10%; text-align: right;">VAT %</th>                                    
                                        <th style="width: 20%; text-align: right;">Amount</th>
                                        <th style="width: 20%; text-align: right;">Discount</th>
                                        <th style="width: 20%; text-align: right;">V.A.T</th>
                                        <th style="width: 20%; text-align: right;">Bill Amount</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                    $vatperc = 0;
                                    $subtot = 0;
                                    $discount = 0;
                                    $vatamt = 0;
                                    $nettot = 0;


                                    $ctr = 0;
                                    while ($row = mysqli_fetch_object($getpur)) {

                                        $ctr++;
                                        echo "<tr style='height:10px'> ";
                                        echo "<td >$ctr</td>";
                                        echo "<td style='text-align: right;'>$row->vatperc</td>";
                                        echo "<td style='text-align: right;'>$row->subtotal</td>";
                                        echo "<td style='text-align: right;'>$row->discount</td>";
                                        echo "<td style='text-align: right;'>$row->vatamt</td>";
                                        echo "<td style='text-align: right;'>$row->nettotal</td>";
                                        echo "</tr>";
                                        $vatperc = $vatperc + $row->vatperc;
                                        $subtot = $subtot + $row->subtotal;
                                        $discount = $discount + $row->discount;
                                        $vatamt = $vatamt + $row->vatamt;
                                        $nettot = $nettot + $row->nettotal;
                                    }
                                    echo "<tr style='height:10px'> ";
                                    echo "<td></td>";
                                    echo "<td style='text-align: right;'><font><b>Total&nbsp;&nbsp;</font></b></td>";
                                    echo "<td style='text-align: right;'><font><b>" . number_format($subtot, 2, '.', '') . "</b></font></td>";
                                    echo "<td style='text-align: right;'><font><b>" . number_format($discount, 2, '.', '') . "</b></font></td>";
                                    echo "<td style='text-align: right;'><font><b>" . number_format($vatamt, 2, '.', '') . "</b></font></td>";
                                    echo "<td style='text-align: right;'><font><b>" . number_format($nettot, 2, '.', '') . "</b></font></td>";
                                    echo "</tr>";
                                    ?>


                                </tbody>
                            </table><br>
                            <div class="row" style="background-color: ">
                                <div class="col-md-6">
                                    <h5 style="color: #78909C; "><b>Purchase Return</b></h5>
                                </div>                                
                            </div>
                            <table style="width:100%" class="table-bordered sortableTable responsive-table report">     
                                <thead style="background-color: #78909C;">
                                    <tr>
                                        <th style="width: 10%; ">Sr. No.</th>
                                        <th style="width: 10%; text-align: right;">VAT %</th>                                    
                                        <th style="width: 20%; text-align: right;">Amount</th>
                                        <th style="width: 20%; text-align: right;">Discount</th>
                                        <th style="width: 20%; text-align: right;">V.A.T</th>
                                        <th style="width: 20%; text-align: right;">Bill Amount</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                    $vatperc2 = 0;
                                    $subtot2 = 0;
                                    $discount2 = 0;
                                    $vatamt2 = 0;
                                    $nettot2 = 0;


                                    $ctr2 = 0;
                                    while ($row2 = mysqli_fetch_object($getpurret)) {

                                        $ctr2++;
                                        echo "<tr style='height:10px'> ";
                                        echo "<td align='left'>$ctr2</td>";
                                        echo "<td style='text-align: right;'>$row2->vatperc</td>";
                                        echo "<td style='text-align: right;'>$row2->subtotal</td>";
                                        echo "<td style='text-align: right;'>$row2->discount</td>";
                                        echo "<td style='text-align: right;'>$row2->vatamt</td>";
                                        echo "<td style='text-align: right;'>$row2->nettotal</td>";
                                        echo "</tr>";
                                        $vatperc2 = $vatperc2 + $row2->vatperc;
                                        $subtot2 = $subtot2 + $row2->amount;
                                        $discount2 = $discount2 + $row2->discount;
                                        $vatamt2 = $vatamt2 + $row2->vatamt;
                                        $nettot2 = $nettot2 + $row2->nettotal;
                                    }
                                    echo "<tr style='height:10px'> ";
                                    echo "<td></td>";
                                    echo "<td align='right'><font><b>Total&nbsp;&nbsp;</font></b></td>";
                                    echo "<td align='right'><font><b>" . number_format($subtot2, 2, '.', '') . "</b></font></td>";
                                    echo "<td align='right'><font><b>" . number_format($discount2, 2, '.', '') . "</b></font></td>";
                                    echo "<td align='right'><font><b>" . number_format($vatamt2, 2, '.', '') . "</b></font></td>";
                                    echo "<td align='right'><font><b>" . number_format($nettot2, 2, '.', '') . "</b></font></td>";
                                    echo "</tr>";
                                    ?>


                                </tbody>
                            </table><br>
                            <div class="row" style="background-color:  ">
                                <div class="col-md-6">
                                    <h5 style="color: #78909C;"><b>Sale</b></h5>
                                </div>                                
                            </div>
                            <table style="width:100%" class="table-bordered sortableTable responsive-table report">     
                                <thead style="background-color:#78909C;">
                                    <tr>
                                        <th style="width: 10%; ">Sr. No.</th>
                                        <th style="width: 10%; text-align: right;">VAT %</th>                                    
                                        <th style="width: 20%; text-align: right;">Amount</th>
                                        <th style="width: 20%; text-align: right;">Discount</th>
                                        <th style="width: 20%; text-align: right;">V.A.T</th>
                                        <th style="width: 20%; text-align: right;">Bill Amount</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                    $vatperc3 = 0;
                                    $subtot3 = 0;
                                    $discount3 = 0;
                                    $vatamt3 = 0;
                                    $nettot3 = 0;


                                    $ctr3 = 0;
                                    while ($row3 = mysqli_fetch_object($getsal)) {

                                        $ctr3++;
                                        echo "<tr style='height:10px'> ";
                                        echo "<td >$ctr3</td>";
                                        echo "<td style='text-align: right;'>$row3->vatperc</td>";
                                        echo "<td style='text-align: right;'>$row3->subtotal</td>";
                                        echo "<td style='text-align: right;'>$row3->discount</td>";
                                        echo "<td style='text-align: right;'>$row3->vatamt</td>";
                                        echo "<td style='text-align: right;'>$row3->nettotal</td>";
                                        echo "</tr>";
                                        $vatperc3 = $vatperc3 + $row3->vatperc;
                                        $subtot3 = $subtot3 + $row3->amount;
                                        $discount3 = $discount3 + $row3->discount;
                                        $vatamt3 = $vatamt3 + $row3->vatamt;
                                        $nettot3 = $nettot3 + $row3->nettotal;
                                    }
                                    echo "<tr style='height:10px'> ";
                                    echo "<td></td>";
                                    echo "<td align='right'><font><b>Total&nbsp;&nbsp;</font></b></td>";
                                    echo "<td align='right'><font><b>" . number_format($subtot3, 2, '.', '') . "</b></font></td>";
                                    echo "<td align='right'><font><b>" . number_format($discount3, 2, '.', '') . "</b></font></td>";
                                    echo "<td align='right'><font><b>" . number_format($vatamt3, 2, '.', '') . "</b></font></td>";
                                    echo "<td align='right'><font><b>" . number_format($nettot3, 2, '.', '') . "</b></font></td>";
                                    echo "</tr>";
                                    ?>


                                </tbody>
                            </table><br>
                            <div class="row" style="background-color:  ">
                                <div class="col-md-6">
                                    <h5 style="color: #78909C;"><b>Sale Return</b></h5>
                                </div>                                
                            </div>
                            <table style="width:100%" class="table-bordered sortableTable responsive-table report">     
                                <thead style="background-color: #78909C;">
                                    <tr>
                                        <th style="width: 10%; ">Sr. No.</th>
                                        <th style="width: 10%; text-align: right;">VAT %</th>                                    
                                        <th style="width: 20%; text-align: right;">Amount</th>
                                        <th style="width: 20%; text-align: right;">Discount</th>
                                        <th style="width: 20%; text-align: right;">V.A.T</th>
                                        <th style="width: 20%; text-align: right;">Bill Amount</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                    $vatperc4 = 0;
                                    $subtot4 = 0;
                                    $discount4 = 0;
                                    $vatamt4 = 0;
                                    $nettot4 = 0;


                                    $ctr4 = 0;
                                    while ($row4 = mysqli_fetch_object($getsalret)) {

                                        $ctr4++;
                                        echo "<tr style='height:10px'> ";
                                        echo "<td >$ctr4</td>";
                                        echo "<td style='text-align: right;'>$row4->vatperc</td>";
                                        echo "<td style='text-align: right;'>$row4->subtotal</td>";
                                        echo "<td style='text-align: right;'>$row4->discount</td>";
                                        echo "<td style='text-align: right;'>$row4->vatamt</td>";
                                        echo "<td style='text-align: right;'>$row4->nettotal</td>";
                                        echo "</tr>";
                                        $vatperc4 = $vatperc4 + $row4->vatperc;
                                        $subtot4 = $subtot4 + $row4->amount;
                                        $discount4 = $discount4 + $row4->discount;
                                        $vatamt4 = $vatamt4 + $row4->vatamt;
                                        $nettot4 = $nettot4 + $row4->nettotal;
                                    }
                                    echo "<tr style='height:10px'> ";
                                    echo "<td></td>";
                                    echo "<td align='right'><font><b>Total&nbsp;&nbsp;</font></b></td>";
                                    echo "<td align='right'><font><b>" . number_format($subtot4, 2, '.', '') . "</b></font></td>";
                                    echo "<td align='right'><font><b>" . number_format($discount4, 2, '.', '') . "</b></font></td>";
                                    echo "<td align='right'><font><b>" . number_format($vatamt4, 2, '.', '') . "</b></font></td>";
                                    echo "<td align='right'><font><b>" . number_format($nettot4, 2, '.', '') . "</b></font></td>";
                                    echo "</tr>";
                                    ?>


                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </html>
        <?php
        include("includes/linksfoot.php");
    }

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

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

        $getpur = mysqli_query($conn, "SELECT 'PU' as source, 1 as ord, vatperc, SUM(subtotal) as subtotal, SUM(litemdisc+cdiscamt+lesother) as discount, 
                        SUM(netamt) as nettotal, SUM(vatamt) as vatamt
                        FROM purmain WHERE vouchdt >= '$mfrmdate' AND vouchdt <= '$mtodate' 
                        GROUP BY vatperc ORDER BY vatperc") or die(mysqli_error($conn));



        $getpurret = mysqli_query($conn, "SELECT 'PR' as source, 2 as ord, vatperc, SUM(subtotal) as subtotal, SUM(litemdisc+cdisamt+lessoth) as discount, 
                        SUM(netamt) as nettotal, SUM(vatamt) as vatamt
                        FROM debmain WHERE vouchdt >= '$mfrmdate' AND vouchdt <= '$mtodate' 
                        GROUP BY vatperc ORDER BY vatperc") or die(mysqli_error($conn));

        $getsal = mysqli_query($conn, "SELECT 'SL' as source, 3 as ord, vatperc, SUM(subtotal) as subtotal, SUM(litemdisc+cdiscamt) as discount, 
                        SUM(netamt) as nettotal, SUM(vatamt) as vatamt
                        FROM salemain WHERE vouchdt >= '$mfrmdate' AND vouchdt <= '$mtodate' 
                        GROUP BY vatperc ORDER BY vatperc") or die(mysqli_error($conn));

        $getsalret = mysqli_query($conn, "SELECT 'SR' as source, 4 as ord, vatperc, SUM(subtotal) as subtotal, SUM(litemdisc+cdisamt+lessoth) as discount, 
                        SUM(netamt) as nettotal, SUM(vatamt) as vatamt
                        FROM credmain WHERE vouchdt >= '$mfrmdate' AND vouchdt <= '$mtodate' 
                        GROUP BY vatperc ORDER BY vatperc") or die(mysqli_error($conn));

        $getno = mysqli_num_rows($getpur);

        $contents.= "Purchase" . "\n";
        $contents.= "Sr. No.,VAT %,Amount,Discount,V.A.T,Bill Amount\n";



        $vatperc = 0;
        $subtot = 0;
        $discount = 0;
        $vatamt = 0;
        $nettot = 0;


        $ctr = 0;
        while ($row = mysqli_fetch_object($getpur)) {

            $ctr++;
            $taxperc = $row->taxperc;
            $subtotal = $row->subtotal;
            $discount = $row->discount;
            $vatamount = $row->vatamt;
            $nettotal = $row->nettotal;


            $contents.=$ctr . ",";
            $contents.=$taxperc . ",";
            $contents.=$subtotal . ",";
            $contents.=$discount . ",";
            $contents.=$vatamount . ",";
            $contents.=$nettotal . "\n";
        }
        $vatperc = $vatperc + $row->vatperc;
        $subtot = $subtot + $row->subtotal;
        $discount = $discount + $row->discount;
        $vatamt = $vatamt + $row->vatamt;
        $nettot = $nettot + $row->nettotal;

        $contents.= "Purchase Return" . "\n";

        $contents.= "Sr. No.,VAT %,Amount,Discount,V.A.T,Bill Amount\n";


        $vatperc2 = 0;
        $subtot2 = 0;
        $discount2 = 0;
        $vatamt2 = 0;
        $nettot2 = 0;


        $ctr2 = 0;
        while ($row2 = mysqli_fetch_object($getpurret)) {

            $ctr2++;
            $taxperc2 = $row2->taxperc;
            $subtotal2 = $row2->subtotal;
            $discount2 = $row2->discount;
            $vatamount2 = $row2->vatamt;
            $billamt = $row2->billamt;

            $contents.=$ctr2 . ",";
            $contents.=$taxperc2 . ",";
            $contents.=$subtotal2 . ",";
            $contents.=$discount2 . ",";
            $contents.=$vatamount2 . ",";
            $contents.=$billamt . "\n";
        }
        $vatperc2 = $vatperc2 + $row2->vatperc;
        $subtot2 = $subtot2 + $row2->amount;
        $discount2 = $discount2 + $row2->discount;
        $vatamt2 = $vatamt2 + $row2->vatamt;
        $nettot2 = $nettot2 + $row2->billamt;

        $contents.= "Sale" . "\n";

        $contents.= "Sr. No.,VAT %,Amount,Discount,V.A.T,Bill Amount\n";

        $vatperc3 = 0;
        $subtot3 = 0;
        $discount3 = 0;
        $vatamt3 = 0;
        $nettot3 = 0;


        $ctr3 = 0;
        while ($row3 = mysqli_fetch_object($getsal)) {

            $ctr3++;
            $taxperc3 = $row3->taxperc;
            $subtotal3 = $row3->subtotal;
            $discount3 = $row3->discount;
            $vatamount3 = $row3->vatamt;
            $billamt3 = $row3->billamt;

            $contents.=$ctr3 . ",";
            $contents.=$taxperc3 . ",";
            $contents.=$subtotal3 . ",";
            $contents.=$discount3 . ",";
            $contents.=$vatamount3 . ",";
            $contents.=$billamt3 . "\n";
        }
        $vatperc3 = $vatperc3 + $row3->vatperc;
        $subtot3 = $subtot3 + $row3->amount;
        $discount3 = $discount3 + $row3->discount;
        $vatamt3 = $vatamt3 + $row3->vatamt;
        $nettot3 = $nettot3 + $row3->billamt;

        $contents.= "Sale Return" . "\n";

        $contents.= "Sr. No.,VAT %,Amount,Discount,V.A.T,Bill Amount\n";

        $vatperc4 = 0;
        $subtot4 = 0;
        $discount4 = 0;
        $vatamt4 = 0;
        $nettot4 = 0;


        $ctr4 = 0;
        while ($row4 = mysqli_fetch_object($getsalret)) {


            $ctr4++;
            $taxperc4 = $row4->taxperc;
            $subtotal4 = $row4->subtotal;
            $discount4 = $row4->discount;
            $vatamount4 = $row4->vatamt;
            $billamt4 = $row4->billamt;

            $contents.=$ctr4 . ",";
            $contents.=$taxperc4 . ",";
            $contents.=$subtotal4 . ",";
            $contents.=$discount4 . ",";
            $contents.=$vatamount4 . ",";
            $contents.=$billamt4 . "\n";
        }
        $vatperc4 = $vatperc4 + $row4->vatperc;
        $subtot4 = $subtot4 + $row4->amount;
        $discount4 = $discount4 + $row4->discount;
        $vatamt4 = $vatamt4 + $row4->vatamt;
        $nettot4 = $nettot4 + $row4->billamt;

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