

<?php
include './connection_sql.php';
if ($_GET['rtype'] == "detail") {
    ?>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
        <head>
            <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
            <title>VAT Shedule Report</title>
            <link rel="stylesheet" href="css/bootstrap.min.css">
                <style>
                    body {
                        font-size:12px;
                    }
                    .red {
                        background-color:#FCCF50;
                    }
                </style>

        </head>

        <body>
            <center>
                <?php
                if ($_GET['shtype'] == "sh1") {
                    include './connection_sql.php';

                    $sql = "select * from invpara where COMCODE = '".$_GET["company"]."'";
                    $result_g = $conn->query($sql);
                    $row_g = $result_g->fetch();
                    echo "<h3>" . $row_g["COMPANY"] . "</h3>";
                    echo "<h5>VAT Schedule Report From " . $_GET["dtfrom"] . " To " . $_GET["dtto"] . "</h5>";
                    ?>

                    <table class="table table-bordered">
                        <tr>
                                                        <!--<th  style="width: 50px;">d Code</b></th>-->
                            <th  style="width: 50px;">Serial No</b></th>
                            <th  style="width: 80px;">Invoice Date</b></th>
                            <th  style="width: 110px;">Tax Invoice No</b></th>
                            <th  style="width: 110px;">Purchaser's TIN</th>
                            <th  style="width: 50px;">P Code</th>
                            <th  style="width: 179px;">Name of the Purchaser</th>
                            <th  style="width: 122px;">Description</th>
                            <th  style="width: 50px;">Value of supply</th>
                            <th  style="width: 50px;">VAT Amount</th>
                        </tr>

                        <?php
                        $mtot = 0;
                        $mtot1 = 0;
                        $i = 1;
                        //and vat ='1' and vat_no <> ''
                        
                        $phraseSale = "";
                        if($_GET["company"]=="A"){
                            $phraseSale = " and REF_NO like 'PC%' ";
                        }else if($_GET["company"]=="R"){
                            $phraseSale = " and REF_NO like 'RC%' ";
                        }
                        
                        $sql = "select * from s_salma where REF_NO like '".$_SESSION["INVC"]."%' and sdate >='" . $_GET["dtfrom"] . "' and sdate <='" . $_GET["dtto"] . "'  and cancell='0' and svat =0  $phraseSale order by id";
                        foreach ($conn->query($sql) as $row) {
                            echo "<tr>";
                            //echo "<td>" . $row['C_CODE'] . "</td>";
                            echo "<td>" . $i . "</td>";
                            echo "<td>" . date("m/d/Y", strtotime($row['SDATE'])) . "</td>";
                            echo "<td>" . $row['REF_NO'] . "</td>";

                            $mvatno = substr(trim($row['vat_no']), 0, 9);
                            $sql = "select * from vendor where code ='" . $row['C_CODE'] . "'";
                            $result_g = $conn->query($sql);
                            $row_g = $result_g->fetch();
                            if (strlen($row['vat_no'] < 3)) {
                                if ($row_g['vatgroup'] <> "") {
                                    $sql = "select * from vatgroup where code ='" . $row_g['vatgroup'] . "'";
                                    $result_v = $conn->query($sql);
                                    if ($row_v = $result_v->fetch()) {
                                        $mvatno = $row_v['vatno'];
                                    }
                                } else {
                                    $sql = "select * from vatgroup where code ='PRIVATE'";
                                    $result_v = $conn->query($sql);
                                    if ($row_v = $result_v->fetch()) {
                                        $mvatno = $row_v['vatno'];
                                    }
                                }
                            }



                            echo "<td>" . $mvatno . "</td>";

                            echo "<td>" . $row['C_CODE'] . "</td>";
                            echo "<td>" . $row['CUS_NAME'] . "</td>";
                            echo "<td>Sales Of Automative Tyres</td>";

                            $net = $row["GRAND_TOT"] / (1 + ($row["GST"] / 100));

                            $vat = $net * $row["GST"] / 100;

                            echo "<td align=\"right\">" . number_format($net, 2, ".", "") . "</td>";
                            echo "<td align=\"right\">" . number_format($vat, 2, ".", "") . "</td>";

                            $mtot = $mtot + $net;
                            $mtot1 = $mtot1 + $vat;

                            $i = $i + 1;
                            echo "<tr>";
                        }
                        echo "<tr>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";

                        echo "<td></td>";

                        echo "<td></td>";
                        echo "<td></td>";


                        echo "<td align=\"right\"><b>" . number_format($mtot, 2, ".", "") . "</b></td>";
                        echo "<td align=\"right\"><b>" . number_format($mtot1, 2, ".", "") . "</b></td>";
                        echo "</tr>";
                        ?>



                </center>


                <?php
            }
            
                if ($_GET['shtype'] == "sh6") {
                    include './connection_sql.php';

                    $sql = "select * from invpara where COMCODE = '".$_GET["company"]."'";
                    $result_g = $conn->query($sql);
                    $row_g = $result_g->fetch();
                    echo "<h3>" . $row_g["COMPANY"] . "</h3>";
                    echo "<h5>SVAT Schedule Report From " . $_GET["dtfrom"] . " To " . $_GET["dtto"] . "</h5>";
                    ?>

                    <table class="table table-bordered">
                        <tr>
                                                        <!--<th  style="width: 50px;">d Code</b></th>-->
                            <th  style="width: 50px;">Serial No</b></th>
                            <th  style="width: 50px;">C_CODE</b></th>
                            <th  style="width: 150px;">C_NAME</b></th>
                            <th  style="width: 80px;">RIP TIN No.</b></th>
                            <th  style="width: 110px;">RIP SVAT NO.</b></th>
                            <th  style="width: 110px;">Credit Voucher No.</th>
                            <th  style="width: 110px;">Normal/NFE</th>
                            <th  style="width: 122px;">Invoice Date</th>
                            <th  style="width: 122px;">Suspended Tax Invoice No.</th>
                            <th  style="width: 50px;">Value Of Supply</th>
                            <th  style="width: 50px;">Suspended VAT Amount</th>
                        </tr>

                        <?php
                        $mtot = 0;
                        $mtot1 = 0;
                        $i = 1;
                        //and vat ='1' and vat_no <> ''
                        
                        $phraseSale = "";
                        if($_GET["company"]=="A"){
                            $phraseSale = " and REF_NO like 'PC%' ";
                        }else if($_GET["company"]=="R"){
                            $phraseSale = " and REF_NO like 'RC%' ";
                        }
                        
                        $sql = "select * from s_salma where sdate >='" . $_GET["dtfrom"] . "' and sdate <='" . $_GET["dtto"] . "'  and cancell='0' and svat > 0 $phraseSale  order by id";
                        foreach ($conn->query($sql) as $row) {
                            echo "<tr>";
                            //echo "<td>" . $row['C_CODE'] . "</td>";
                            echo "<td>" . $i . "</td>";
                            echo "<td>" . $row['C_CODE'] . "</td>";
                            echo "<td>" . $row['CUS_NAME'] . "</td>";

                            $mvatno = substr(trim($row['vat_no']), 0, 9);
                            $sql = "select * from vendor where code ='" . $row['C_CODE'] . "'";
                            $result_g = $conn->query($sql);
                            $row_g = $result_g->fetch();
                            if (strlen($row['vat_no'] < 3)) {
                                if ($row_g['vatgroup'] <> "") {
                                    $sql = "select * from vatgroup where code ='" . $row_g['vatgroup'] . "'";
                                    $result_v = $conn->query($sql);
                                    if ($row_v = $result_v->fetch()) {
                                        $mvatno = $row_v['vatno'];
                                    }
                                } else {
                                    $sql = "select * from vatgroup where code ='PRIVATE'";
                                    $result_v = $conn->query($sql);
                                    if ($row_v = $result_v->fetch()) {
                                        $mvatno = $row_v['vatno'];
                                    }
                                }
                            }



                            echo "<td>" . $mvatno . "</td>";
                            echo "<td>SVAT".$row_g['svatno']."</td>";
                            echo "<td>CRV</td>";
                            echo "<td>Normal</td>";
                            echo "<td>" . date("m/d/Y", strtotime($row['SDATE'])) . "</td>";
                            echo "<td>" . $row['REF_NO'] . "</td>";
                            
                            $net = $row["GRAND_TOT"] / (1 + ($row["GST"] / 100));

                            $vat = $net * $row["GST"] / 100;

                            echo "<td align=\"right\">" . number_format($net, 2, ".", "") . "</td>";
                            echo "<td align=\"right\">" . number_format($vat, 2, ".", "") . "</td>";

                            $mtot = $mtot + $net;
                            $mtot1 = $mtot1 + $vat;

                            $i = $i + 1;
                            echo "<tr>";
                        }
                        echo "<tr>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";

                        echo "<td></td>";

                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";


                        echo "<td align=\"right\"><b>" . number_format($mtot, 2, ".", "") . "</b></td>";
                        echo "<td align=\"right\"><b>" . number_format($mtot1, 2, ".", "") . "</b></td>";
                        echo "</tr>";
                        ?>



                </center>


                <?php
            }            
            
                if ($_GET['shtype'] == "sh7") {
                    include './connection_sql.php';

                    $sql = "select * from invpara where COMCODE = '".$_GET["company"]."'";
                    $result_g = $conn->query($sql);
                    $row_g = $result_g->fetch();
                    echo "<h3>" . $row_g["COMPANY"] . "</h3>";
                    echo "<h5>Sale Report From " . $_GET["dtfrom"] . " To " . $_GET["dtto"] . "</h5>";
                    ?>

                    <table class="table table-bordered">
                        <tr>
                                                        <!--<th  style="width: 50px;">d Code</b></th>-->
                            <th  style="width: 50px;">Serial No</b></th>
                            <th  style="width: 50px;">C_CODE</b></th>
                            <th  style="width: 150px;">C_NAME</b></th>
                            <th  style="width: 80px;">RIP TIN No.</b></th>
                            <th  style="width: 110px;">RIP SVAT NO.</b></th>
                            <th  style="width: 110px;">Credit Voucher No.</th>
                            <th  style="width: 110px;">Normal/NFE</th>
                            <th  style="width: 122px;">Invoice Date</th>
                            <th  style="width: 122px;">Invoice No.</th>
                            <th  style="width: 50px;">Value Of Supply</th>
                            <th  style="width: 50px;">VAT Amount</th>
                        </tr>

                        <?php
                        $mtot = 0;
                        $mtot1 = 0;
                        $i = 1;
                        //and vat ='1' and vat_no <> ''
                        
                        $phraseSale = "";
                        if($_GET["company"]=="A"){
                            $phraseSale = " and REF_NO like 'PC%' ";
                        }else if($_GET["company"]=="R"){
                            $phraseSale = " and REF_NO like 'RC%' ";
                        }
                        
                        $sql = "select * from s_salma where sdate >='" . $_GET["dtfrom"] . "' and sdate <='" . $_GET["dtto"] . "'  and cancell='0'  $phraseSale  order by id";
                        foreach ($conn->query($sql) as $row) {
                            echo "<tr>";
                            //echo "<td>" . $row['C_CODE'] . "</td>";
                            echo "<td>" . $i . "</td>";
                            echo "<td>" . $row['C_CODE'] . "</td>";
                            echo "<td>" . $row['CUS_NAME'] . "</td>";

                            $mvatno = substr(trim($row['vat_no']), 0, 9);
                            $sql = "select * from vendor where code ='" . $row['C_CODE'] . "'";
                            $result_g = $conn->query($sql);
                            $row_g = $result_g->fetch();
                            if (strlen($row['vat_no'] < 3)) {
                                if ($row_g['vatgroup'] <> "") {
                                    $sql = "select * from vatgroup where code ='" . $row_g['vatgroup'] . "'";
                                    $result_v = $conn->query($sql);
                                    if ($row_v = $result_v->fetch()) {
                                        $mvatno = $row_v['vatno'];
                                    }
                                } else {
                                    $sql = "select * from vatgroup where code ='PRIVATE'";
                                    $result_v = $conn->query($sql);
                                    if ($row_v = $result_v->fetch()) {
                                        $mvatno = $row_v['vatno'];
                                    }
                                }
                            }



                            echo "<td>" . $mvatno . "</td>";
                            $svatRef = "";
                            if($row_g['svatno'] != ""){
                                $svatRef = "SVAT".$row_g['svatno']."";
                            }
                            echo "<td>".$svatRef."</td>";
                            echo "<td>CRV</td>";
                            echo "<td>Normal</td>";
                            echo "<td>" . date("m/d/Y", strtotime($row['SDATE'])) . "</td>";
                            echo "<td>" . $row['REF_NO'] . "</td>";
                            
                            $net = $row["GRAND_TOT"] / (1 + ($row["GST"] / 100));

                            $vat = $net * $row["GST"] / 100;

                            echo "<td align=\"right\">" . number_format($net, 2, ".", "") . "</td>";
                            echo "<td align=\"right\">" . number_format($vat, 2, ".", "") . "</td>";

                            $mtot = $mtot + $net;
                            $mtot1 = $mtot1 + $vat;

                            $i = $i + 1;
                            echo "<tr>";
                        }
                        echo "<tr>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";

                        echo "<td></td>";

                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";

                        echo "<td align=\"right\"><b>" . number_format($mtot, 2, ".", "") . "</b></td>";
                        echo "<td align=\"right\"><b>" . number_format($mtot1, 2, ".", "") . "</b></td>";
                        echo "</tr>";
                        ?>



                </center>


                <?php
            }            

            if ($_GET['shtype'] == "sh5") {
                include './connection_sql.php';

                $sql = "select * from invpara where COMCODE = '".$_GET["company"]."'";
                $result_g = $conn->query($sql);
                $row_g = $result_g->fetch();
                echo "<h3>" . $row_g["COMPANY"] . "</h3>";
                echo "<h5>Purchase VAT Schedule Report From " . $_GET["dtfrom"] . " To " . $_GET["dtto"] . "</h5>";
                ?>

                <table class="table table-bordered">
                    <tr>
                                                    <!--<th  style="width: 50px;">d Code</b></th>-->
                        <th  style="width: 50px;">Serial No</b></th>
                        <th  style="width: 80px;">Invoice Date</b></th>
                        <th  style="width: 110px;">Tax Invoice No</b></th>
                        <th  style="width: 110px;">Supplier's TIN</th>
                        <th  style="width: 179px;">Name of the Supplier</th>
                        <th  style="width: 122px;">Description</th>
                        <th  style="width: 50px;">Value of purchase</th>
                        <th  style="width: 50px;">VAT Amount</th>
                    </tr>

                    <?php
                    $mtot = 0;
                    $mtot1 = 0;
                    $i = 1;
                    $sql = "SELECT PUR_DATE, REFNO, SUP_CODE, SUP_NAME, CAT,LCNO,sum(COST*REC_QTY) as COST, vatno from viewpur where REFNO like '".$_GET["company"]."%' and SDATE>='" . $_GET["dtfrom"] . "' AND SDATE<='" . $_GET["dtto"] . "'   AND cancel=0  group by PUR_DATE,REFNO,CAT,LCNO order by REFNO";
//                    echo $sql;
                    foreach ($conn->query($sql) as $row) {
                        echo "<tr>";
                        //echo "<td>" . $row['C_CODE'] . "</td>";
                        echo "<td>" . $i . "</td>";
                        echo "<td>" . date("m/d/Y", strtotime($row['PUR_DATE'])) . "</td>";
                        echo "<td>" . $row['LCNO'] . "</td>";

                        $mvatno = substr(trim($row['vatno']), 0, 9);
                        $sql = "select * from vendor where code ='" . $row['C_CODE'] . "'";
                        $result_g = $conn->query($sql);
                        $row_g = $result_g->fetch();
                        if (strlen($row['vatno'] < 3)) {
                            if ($row_g['vatgroup'] <> "") {
                                $sql = "select * from vatgroup where code ='" . $row_g['vatgroup'] . "'";
                                $result_v = $conn->query($sql);
                                if ($row_v = $result_v->fetch()) {
                                    $mvatno = $row_v['vatno'];
                                }
                            } else {
                                $sql = "select * from vatgroup where code ='PRIVATE'";
                                $result_v = $conn->query($sql);
                                if ($row_v = $result_v->fetch()) {
                                    $mvatno = $row_v['vatno'];
                                }
                            }
                        }



                        echo "<td>" . $mvatno . "</td>";

                        echo "<td>" . $row['SUP_NAME'] . "</td>";
                        echo "<td>" . $row['CAT'] . "</td>";

                        $net = $row["COST"] / (1 + (15 / 100));

                        $vat = $net * 15 / 100;

                        echo "<td align=\"right\">" . number_format($net, 2, ".", "") . "</td>";
                        echo "<td align=\"right\">" . number_format($vat, 2, ".", "") . "</td>";

                        $mtot = $mtot + $net;
                        $mtot1 = $mtot1 + $vat;

                        $i = $i + 1;
                        echo "<tr>";
                    }
                    echo "<tr>";
                    echo "<td></td>";
                    echo "<td></td>";
                    echo "<td></td>";

                    echo "<td></td>";

                    echo "<td></td>";
                    echo "<td></td>";


                    echo "<td align=\"right\"><b>" . number_format($mtot, 2, ".", "") . "</b></td>";
                    echo "<td align=\"right\"><b>" . number_format($mtot1, 2, ".", "") . "</b></td>";
                    echo "</tr>";
                    ?>



                    </center>


                    <?php
                }

                if ($_GET['shtype'] == "sh4") {
                    include './connection_sql.php';

                    $sql = "select * from invpara where COMCODE = '".$_GET["company"]."'";
                    $result_g = $conn->query($sql);
                    $row_g = $result_g->fetch();
                    echo "<h3>" . $row_g["COMPANY"] . "</h3>";
                    echo "<h5>Good Return VAT Schedule Report From " . $_GET["dtfrom"] . " To " . $_GET["dtto"] . "</h5>";
                    ?>

                    <table class="table table-bordered">
                        <tr>

                            <th colspan='2'>Dealer</th>
                            <th  style="width: 50px;">Serial No</b></th>
                            <th  style="width: 110px;">TIN No</th>
                            <th  style="width: 80px;">Invoice Date</b></th>
                            <th  style="width: 110px;">Invoice No</b></th>

                            <th  style="width: 179px;">Tax Credit / Tax Debit Note</th>
                            <th  style="width: 179px;">Date of Tax Credit / Tax Debit Note</th>
                            <th  style="width: 179px;">Tax Credit No. / Tax Debit Note No.</th>						
                            <th  style="width: 179px;">Value of Tax Credit Note / Tax Debit Note</th>						
                            <th  style="width: 50px;">VAT Amount</th>
                            <th  style="width: 50px;">Vat Rate</th>
                        </tr>

                        <?php
                        $mtot = 0;
                        $mtot1 = 0;
                        $i = 1;
                        $sql = "select * from view_c_bal_vendor_new where sdate >='" . $_GET["dtfrom"] . "' and sdate <='" . $_GET["dtto"] . "' and trn_type = 'GRN' and REFNO like 'GRN/".$_GET["company"]."%' and cancell='0' order by id";
//                    echo $sql . "</br>";
                        foreach ($conn->query($sql) as $row) {

                            $invno = "";
                            $invdt = "";
                            $vatrate = $row["vatrate"];
                            $vatno = $row['vatno'];

                            $cus_name = $row['NAME'];
                            /* 			
                              if ($row['trn_type'] == "CNT") {

                              $sql = "select * from cred where c_refno = '" . $row['REFNO'] . "'";
                              $result_g = $conn->query($sql);
                              $row_g = $result_g->fetch();


                              $date = strtotime($row['SDATE'] . ' -6 months');
                              $date = date('Y-m-d', $date);
                              $invno = $row_g['C_INVNO'];


                              $sql = "select * from s_salma where ref_no ='" . $invno . "' and gst = '" . $vatrate . "' and sdate >='" .$date . "' and sdate <='" . $row['SDATE'] . "'";
                              if ($row['REFNO'] =="CRN/000020828") {
                              //echo $sql;
                              }

                              $result_g = $conn->query($sql);
                              if ($row_g = $result_g->fetch()) {
                              $invdt = $row_g['SDATE'];
                              $vatrate = $row_g["GST"];
                              $vatno = $row_g['vat_no'];
                              $cus_name = $row_g['CUS_NAME'];
                              } else {
                              $sql = "select * from s_salma where c_code ='" . $row['CUSCODE'] . "' and gst = '" . $vatrate . "' and sdate >='" .$date . "' and sdate <='" . $row['SDATE'] . "'";
                              $result_g = $conn->query($sql);
                              if ($row_g = $result_g->fetch()) {
                              $invdt = $row_g['SDATE'];
                              $vatrate = $row_g["GST"];
                              $vatno = $row_g['vat_no'];
                              $cus_name = $row_g['CUS_NAME'];
                              }
                              }



                              }
                             */

//                        if ($row['trn_type'] == "GRN") {

                            $sql = "select INVOICENO from s_crnma where REF_NO = '" . $row['REFNO'] . "'";
                            $result_g = $conn->query($sql);
                            $row_g = $result_g->fetch();

                            //$invno = $row_g['INVOICENO'];
                            
                            /*
                            $date = strtotime($row['SDATE'] . ' -6 months');
                            $date = date('Y-m-d', $date);

                            $sql = "select * from s_salma where ref_no ='" . $invno . "' and gst = '" . $vatrate . "' and sdate >='" . $date . "' and sdate <='" . $row['SDATE'] . "'";
                            $result_g = $conn->query($sql);
                            if ($row_g = $result_g->fetch()) {
                                $invdt = $row_g['SDATE'];
                                $vatrate = $row_g["GST"];
                                $vatno = $row_g['vat_no'];
                                $cus_name = $row_g['CUS_NAME'];
                            } else {
                                $sql = "select * from s_salma where c_code ='" . $row['CUSCODE'] . "' and gst = '" . $vatrate . "' and sdate >='" . $date . "' and sdate <='" . $row['SDATE'] . "'";
                                $result_g = $conn->query($sql);
                                if ($row_g = $result_g->fetch()) {
                                    $invdt = $row_g['SDATE'];
                                    $vatrate = $row_g["GST"];
                                    $vatno = $row_g['vat_no'];
                                    $cus_name = $row_g['CUS_NAME'];
                                }
                            }
                            */
                            
                            $sql = "select * from s_salma where REF_NO = '" .$row_g["INVOICENO"]. "'";
                            $result_g = $conn->query($sql);
                            $row_g = $result_g->fetch();
                            
                            $invdt = $row_g['SDATE'];
                            $vatrate = $row_g["GST"];
                            $vatno = $row_g['vat_no'];
                            $cus_name = $row_g['CUS_NAME'];
                            $invno = $row_g['REF_NO'];

//                        }

                            /*
                              if ($row['trn_type'] == "DGRN") {

                              if (($row['Costcenter'] =="") or (is_null($row['Costcenter']))) {
                              $sql = "select * from viewdef where REFNO = '" . $row['REFNO'] . "'";
                              $result_g = $conn->query($sql);
                              $row_g = $result_g->fetch();
                              $date = strtotime($row_g['SDATE'] . ' -6 months');
                              $date = date('Y-m-d', $date);
                              $sql = "select * from view_salma_invo_smas where gst ='" . $vatrate . "' and brand = '" . $row_g['BRAND_NAME'] . "' and sdate <='" . $row['SDATE'] . "' and c_code = '" . $row_g['c_code'] . "' and sdate <='" . $row_g['SDATE'] . "' and sdate >='" . $date . "' order by id";
                              $result_g1 = $conn->query($sql);
                              if ($row_g1 = $result_g1->fetch()) {
                              $invdt = $row_g1['SDATE'];
                              $invno = $row_g1['REF_NO'];
                              $vatrate = $row_g1["GST"];
                              $vatno = $row_g1['vat_no'];
                              $cus_name = $row_g1['CUS_NAME'];
                              $sql = "update c_bal set costcenter = '" . $row_g1['REF_NO'] . "' where refno = '" . $row['REFNO'] . "'";
                              $result_g2 = $conn->query($sql);

                              } else {
                              $sql = "select * from view_salma_invo_smas where gst ='" . $vatrate . "' and c_code = '" . $row_g['c_code'] . "' and sdate <='" . $row['SDATE'] . "' and sdate <='" . $row_g['SDATE'] . "' and sdate >='" . $date . "' order by id";
                              $result_g1 = $conn->query($sql);
                              if ($row_g1 = $result_g1->fetch()) {
                              $invdt = $row_g1['SDATE'];
                              $invno = $row_g1['REF_NO'];
                              $vatrate = $row_g1["GST"];
                              $vatno = $row_g1['vat_no'];
                              $cus_name = $row_g1['CUS_NAME'];
                              }
                              }
                              } else {
                              $sql = "select * from s_salma where ref_no ='" . $row['Costcenter'] . "' limit 1";
                              $result_g1 = $conn->query($sql);
                              if ($row_g1 = $result_g1->fetch()) {
                              $invdt = $row_g1['SDATE'];
                              $invno = $row_g1['REF_NO'];
                              $vatrate = $row_g1["GST"];
                              $vatno = $row_g1['vat_no'];
                              $cus_name = $row_g1['CUS_NAME'];
                              }
                              }
                              }
                             */


                            $sql = "select * from vendor where code ='" . $row['C_CODE'] . "'";
                            $result_g = $conn->query($sql);
                            $row_g = $result_g->fetch();
                            if (strlen($vatno < 3)) {
                                if ($row_g['vatgroup'] <> "") {
                                    $sql = "select * from vatgroup where code ='" . $row_g['vatgroup'] . "'";
                                    $result_v = $conn->query($sql);
                                    if ($row_v = $result_v->fetch()) {
                                        $vatno = $row_v['vatno'];
                                    }
                                } else {
                                    $sql = "select * from vatgroup where code ='PRIVATE'";
                                    $result_v = $conn->query($sql);
                                    if ($row_v = $result_v->fetch()) {
                                        $vatno = $row_v['vatno'];
                                    }
                                }
                            }
//                            echo $invno;
                            $userdata[] = "('" . $vatno . "','" . $invdt . "','" . $invno . "','" . $row['SDATE'] . "','" . $row['REFNO'] . "','" . $row["AMOUNT"] . "','" . $vatrate . "','" . $row['CUSCODE'] . "','" . $cus_name . "')";
                        }
                        $sql = "delete from tmp_vat";
                        $result_g = $conn->query($sql);


                        $sql = "insert into tmp_vat (vatno,invdt,invno,SDATE,REFNO,AMOUNT,vatrate,c_code,cus_name) values " . implode(",", $userdata);
//                    echo $sql;
                        $result_g = $conn->query($sql);

                        /*
                        $sql = "select * from tmp_vat where invno =''";

                        foreach ($conn->query($sql) as $row) {
                            $diff = 0;
                            $date = strtotime($row['SDATE'] . ' -6 months');
                            $date = date('Y-m-d', $date);



                            if ($row['invno'] != "") {
                                $date1 = $row['invdt'];
                                $date2 = $row['SDATE'];

                                $ts1 = strtotime($date1);
                                $ts2 = strtotime($date2);

                                $year1 = date('Y', $ts1);
                                $year2 = date('Y', $ts2);

                                $month1 = date('m', $ts1);
                                $month2 = date('m', $ts2);

                                $diff = (($year2 - $year1) * 12) + ($month2 - $month1);
                            }

                            if (($diff > 6) or ( $row['invno'] == "")) {

                                $mok = "";
                                $sql = "select ref_no,sdate,CUS_NAME from s_salma where gst = '" . $row['vatrate'] . "'  and  sdate >='" . $date . "'  and sdate <='" . $row['SDATE'] . "' and c_code = '" . $row['c_code'] . "' and cancell=0";

                                $result_g1 = $conn->query($sql);
                                $b = 0;
                                foreach ($conn->query($sql) as $row1) {

                                    if ($mok == "") {
                                        $sql = "select * from tmp_vat where invno = '" . $row1['ref_no'] . "*" . "'";
                                        $result_g1 = $conn->query($sql);
                                        if (!$row_g1 = $result_g1->fetch()) {
                                            $mok = "k";
                                            $sql = "update tmp_vat set cus_name = '" . $row1['CUS_NAME'] . "' , invno = '" . $row1['ref_no'] . "*" . "',invdt='" . $row1['sdate'] . "' where id = '" . $row['id'] . "'";

                                            $result_g3 = $conn->query($sql);
                                            $b = 1;
                                        }
                                    }
                                }
                                if ($b == 0) {
                                    $sql = "select ref_no,sdate from s_salma where gst = '" . $row['vatrate'] . "' and sdate >='" . $date . "'   and sdate <='" . $row['SDATE'] . "'  and cancell=0";

                                    $result_g1 = $conn->query($sql);
                                    foreach ($conn->query($sql) as $row1) {

                                        if ($mok == "") {

                                            $sql = "select * from tmp_vat where invno = '" . $row1['ref_no'] . "*" . "'";

                                            $result_g2 = $conn->query($sql);
                                            if (!$row_g2 = $result_g2->fetch()) {
                                                $mok = "k";
                                                $sql = "update tmp_vat set invno = '" . $row1['ref_no'] . "*" . "',invdt='" . $row1['sdate'] . "' where id = '" . $row['id'] . "'";

                                                $result_g3 = $conn->query($sql);
                                                $b = 1;
                                            }
                                        }
                                    }
                                }
                            }
                        }
                        */



                        $sql = "select * from tmp_vat";

                        foreach ($conn->query($sql) as $row) {

                            echo "<tr>";

                            echo "<td>" . $row['c_code'] . "</td>";
                            echo "<td>" . $row['cus_name'] . "</td>";


                            echo "<td>" . $i . "</td>";
                            echo "<td>" . substr(trim($row['vatno']), 0, 9) . "</td>";

                            if ($row['invdt'] == "") {
                                echo "<td></td>";
                            } else {
                                $date = strtotime($row['invdt'] . ' +6 months');
                                
                                if((strtotime($row['invdt'] . ' +6 months') < strtotime(date('Y-m-d')))){
                                    echo "<td style='color:red;'>" . date("m/d/Y", strtotime($row['invdt'])) . "</td>";
                                }else{
                                    echo "<td>" . date("m/d/Y", strtotime($row['invdt'])) . "</td>";
                                }
                                
                                
                            }
                            echo "<td>" . $row['invno'] . "</td>";


                            echo "<td>Credit</td>";
                            echo "<td>" . date("m/d/Y", strtotime($row['SDATE'])) . "</td>";
                            echo "<td>" . $row['REFNO'] . "</td>";


                            $net = ($row["AMOUNT"] / (1 + ($row['vatrate'] / 100)));

                            $vat = $net * $row['vatrate'] / 100;

                            echo "<td align=\"right\">" . number_format($net, 2, ".", "") . "</td>";
                            echo "<td align=\"right\">" . number_format($vat, 2, ".", "") . "</td>";
                            echo "<td align=\"right\">" . number_format($row['vatrate'], 2, ".", "") . "</td>";

                            $mtot = $mtot + $net;
                            $mtot1 = $mtot1 + $vat;

                            $i = $i + 1;
                            echo "<tr>";
                        }

                        echo "<tr>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";


                        echo "<td align=\"right\"><b>" . number_format($mtot, 2, ".", "") . "</b></td>";
                        echo "<td align=\"right\"><b>" . number_format($mtot1, 2, ".", "") . "</b></td>";
                        echo "</tr>";
                    }
                if ($_GET['shtype'] == "sh41") {
                    include './connection_sql.php';

                    $sql = "select * from invpara where COMCODE = '".$_GET["company"]."'";
                    $result_g = $conn->query($sql);
                    $row_g = $result_g->fetch();
                    echo "<h3>" . $row_g["COMPANY"] . "</h3>";
                    echo "<h5>Credit Note VAT Schedule Report From " . $_GET["dtfrom"] . " To " . $_GET["dtto"] . "</h5>";
                    ?>

                    <table class="table table-bordered">
                        <tr>

                            <th colspan='2'>Dealer</th>
                            <th  style="width: 50px;">Serial No</b></th>
                            <th  style="width: 110px;">TIN No</th>
                            <th  style="width: 80px;">Invoice Date</b></th>
                            <th  style="width: 110px;">Invoice No</b></th>

                            <th  style="width: 179px;">Tax Credit / Tax Debit Note</th>
                            <th  style="width: 179px;">Date of Tax Credit / Tax Debit Note</th>
                            <th  style="width: 179px;">Tax Credit No. / Tax Debit Note No.</th>						
                            <th  style="width: 179px;">Value of Tax Credit Note / Tax Debit Note</th>						
                            <th  style="width: 50px;">VAT Amount</th>
                            <th  style="width: 50px;">Vat Rate</th>
                        </tr>

                        <?php
                        $mtot = 0;
                        $mtot1 = 0;
                        $i = 1;
                        $sql = "select * from view_c_bal_vendor_new where sdate >='" . $_GET["dtfrom"] . "' and sdate <='" . $_GET["dtto"] . "' and trn_type = 'CNT' and company = '".$_GET["company"]."' and cancell='0' order by id";
//                    echo $sql . "</br>";
                        foreach ($conn->query($sql) as $row) {

                            $invno = "";
                            $invdt = "";
                            $vatrate = $row["vatrate"];
                            $vatno = $row['vatno'];

                            $cus_name = $row['NAME'];
                              if ($row['trn_type'] == "CNT") {

                              $sql = "select * from cred where c_refno = '" . $row['REFNO'] . "'";
                              $result_g = $conn->query($sql);
                              $row_g = $result_g->fetch();

                              /*
                              $date = strtotime($row['SDATE'] . ' -6 months');
                              $date = date('Y-m-d', $date);
                              $invno = $row_g['C_INVNO'];


                              $sql = "select * from s_salma where ref_no ='" . $invno . "' and gst = '" . $vatrate . "' and sdate >='" .$date . "' and sdate <='" . $row['SDATE'] . "'";
                              if ($row['REFNO'] =="CRN/000020828") {
                              //echo $sql;
                              }

                              $result_g = $conn->query($sql);
                              if ($row_g = $result_g->fetch()) {
                              $invdt = $row_g['SDATE'];
                              $vatrate = $row_g["GST"];
                              $vatno = $row_g['vat_no'];
                              $cus_name = $row_g['CUS_NAME'];
                              } else {
                              $sql = "select * from s_salma where c_code ='" . $row['CUSCODE'] . "' and gst = '" . $vatrate . "' and sdate >='" .$date . "' and sdate <='" . $row['SDATE'] . "'";
                              $result_g = $conn->query($sql);
                              if ($row_g = $result_g->fetch()) {
                              $invdt = $row_g['SDATE'];
                              $vatrate = $row_g["GST"];
                              $vatno = $row_g['vat_no'];
                              $cus_name = $row_g['CUS_NAME'];
                              }
                              }
*/

                              $sql = "select * from s_salma where REF_NO = '" .$row_g["C_INVNO"]. "'";
                              $result_g = $conn->query($sql);
                              if ($row_g = $result_g->fetch()) {
                              $invdt = $row_g['SDATE'];
                              $vatrate = $row_g["GST"];
                              $vatno = $row_g['vat_no'];
                              $cus_name = $row_g['CUS_NAME'];
                              $invno = $row_g['REF_NO'];
                              }
                              
                              }



                            $sql = "select * from vendor where code ='" . $row['C_CODE'] . "'";
                            $result_g = $conn->query($sql);
                            $row_g = $result_g->fetch();
                            if (strlen($vatno < 3)) {
                                if ($row_g['vatgroup'] <> "") {
                                    $sql = "select * from vatgroup where code ='" . $row_g['vatgroup'] . "'";
                                    $result_v = $conn->query($sql);
                                    if ($row_v = $result_v->fetch()) {
                                        $vatno = $row_v['vatno'];
                                    }
                                } else {
                                    $sql = "select * from vatgroup where code ='PRIVATE'";
                                    $result_v = $conn->query($sql);
                                    if ($row_v = $result_v->fetch()) {
                                        $vatno = $row_v['vatno'];
                                    }
                                }
                            }
//                            echo $invno;
                            $userdata[] = "('" . $vatno . "','" . $invdt . "','" . $invno . "','" . $row['SDATE'] . "','" . $row['REFNO'] . "','" . $row["AMOUNT"] . "','" . $vatrate . "','" . $row['CUSCODE'] . "','" . $cus_name . "')";
                        }
                        $sql = "delete from tmp_vat";
                        $result_g = $conn->query($sql);


                        $sql = "insert into tmp_vat (vatno,invdt,invno,SDATE,REFNO,AMOUNT,vatrate,c_code,cus_name) values " . implode(",", $userdata);
//                    echo $sql;
                        $result_g = $conn->query($sql);

                        /*
                        $sql = "select * from tmp_vat where invno =''";

                        foreach ($conn->query($sql) as $row) {
                            $diff = 0;
                            $date = strtotime($row['SDATE'] . ' -6 months');
                            $date = date('Y-m-d', $date);



                            if ($row['invno'] != "") {
                                $date1 = $row['invdt'];
                                $date2 = $row['SDATE'];

                                $ts1 = strtotime($date1);
                                $ts2 = strtotime($date2);

                                $year1 = date('Y', $ts1);
                                $year2 = date('Y', $ts2);

                                $month1 = date('m', $ts1);
                                $month2 = date('m', $ts2);

                                $diff = (($year2 - $year1) * 12) + ($month2 - $month1);
                            }

                            if (($diff > 6) or ( $row['invno'] == "")) {

                                $mok = "";
                                $sql = "select ref_no,sdate,CUS_NAME from s_salma where gst = '" . $row['vatrate'] . "'  and  sdate >='" . $date . "'  and sdate <='" . $row['SDATE'] . "' and c_code = '" . $row['c_code'] . "' and cancell=0";

                                $result_g1 = $conn->query($sql);
                                $b = 0;
                                foreach ($conn->query($sql) as $row1) {

                                    if ($mok == "") {
                                        $sql = "select * from tmp_vat where invno = '" . $row1['ref_no'] . "*" . "'";
                                        $result_g1 = $conn->query($sql);
                                        if (!$row_g1 = $result_g1->fetch()) {
                                            $mok = "k";
                                            $sql = "update tmp_vat set cus_name = '" . $row1['CUS_NAME'] . "' , invno = '" . $row1['ref_no'] . "*" . "',invdt='" . $row1['sdate'] . "' where id = '" . $row['id'] . "'";

                                            $result_g3 = $conn->query($sql);
                                            $b = 1;
                                        }
                                    }
                                }
                                if ($b == 0) {
                                    $sql = "select ref_no,sdate from s_salma where gst = '" . $row['vatrate'] . "' and sdate >='" . $date . "'   and sdate <='" . $row['SDATE'] . "'  and cancell=0";

                                    $result_g1 = $conn->query($sql);
                                    foreach ($conn->query($sql) as $row1) {

                                        if ($mok == "") {

                                            $sql = "select * from tmp_vat where invno = '" . $row1['ref_no'] . "*" . "'";

                                            $result_g2 = $conn->query($sql);
                                            if (!$row_g2 = $result_g2->fetch()) {
                                                $mok = "k";
                                                $sql = "update tmp_vat set invno = '" . $row1['ref_no'] . "*" . "',invdt='" . $row1['sdate'] . "' where id = '" . $row['id'] . "'";

                                                $result_g3 = $conn->query($sql);
                                                $b = 1;
                                            }
                                        }
                                    }
                                }
                            }
                        }
                        */



                        $sql = "select * from tmp_vat where invno != ''";

                        foreach ($conn->query($sql) as $row) {

                            echo "<tr>";

                            echo "<td>" . $row['c_code'] . "</td>";
                            echo "<td>" . $row['cus_name'] . "</td>";


                            echo "<td>" . $i . "</td>";
                            echo "<td>" . substr(trim($row['vatno']), 0, 9) . "</td>";

                            if ($row['invdt'] == "") {
                                echo "<td></td>";
                            } else {
                                $date = strtotime($row['invdt'] . ' +6 months');
                                
                                if((strtotime($row['invdt'] . ' +6 months') < strtotime(date('Y-m-d')))){
                                    echo "<td style='color:red;'>" . date("m/d/Y", strtotime($row['invdt'])) . "</td>";
                                }else{
                                    echo "<td>" . date("m/d/Y", strtotime($row['invdt'])) . "</td>";
                                }
                            }
                            echo "<td>" . $row['invno'] . "</td>";


                            echo "<td>Credit</td>";
                            echo "<td>" . date("m/d/Y", strtotime($row['SDATE'])) . "</td>";
                            echo "<td>" . $row['REFNO'] . "</td>";


                            $net = ($row["AMOUNT"] / (1 + ($row['vatrate'] / 100)));

                            $vat = $net * $row['vatrate'] / 100;

                            echo "<td align=\"right\">" . number_format($net, 2, ".", "") . "</td>";
                            echo "<td align=\"right\">" . number_format($vat, 2, ".", "") . "</td>";
                            echo "<td align=\"right\">" . number_format($row['vatrate'], 2, ".", "") . "</td>";

                            $mtot = $mtot + $net;
                            $mtot1 = $mtot1 + $vat;

                            $i = $i + 1;
                            echo "<tr>";
                        }

                        echo "<tr>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";


                        echo "<td align=\"right\"><b>" . number_format($mtot, 2, ".", "") . "</b></td>";
                        echo "<td align=\"right\"><b>" . number_format($mtot1, 2, ".", "") . "</b></td>";
                        echo "</tr>";
                    }
                }

                if ($_GET['rtype'] == "summ") {
                    if ($_GET['shtype'] == "sh1") {
                        // output headers so that the file is downloaded rather than displayed
                        header('Content-Type: text/csv; charset=utf-8');
                        header('Content-Disposition: attachment; filename=114195383_VAT_SCHEDULE01_1620_20160709_ORIGINAL_V1.csv');

// create a file pointer connected to the output stream
                        $output = fopen('php://output', 'w');

// output the column headings
                        fputcsv($output, array("Serial No", "Invoice Date", "Tax Invoice No", "Purchasers TIN", "Name of the Purchaser", "Description", "Value of supply", "VAT Amount"));

                        $sql = "select * from s_salma where sdate >='" . $_GET["dtfrom"] . "' and sdate <='" . $_GET["dtto"] . "'  and cancell='0' and vat ='1' order by id";
                        $i = 1;
                        foreach ($conn->query($sql) as $row) {
                            $m1 = $i;
                            $m2 = date("m/d/Y", strtotime($row['SDATE']));
                            $m3 = $row['REF_NO'];
                            $m4 = substr(trim($row['vat_no']), 0, 9);
                            $m5 = $row['CUS_NAME'];
                            $m6 = "Purchase Of Tyres";

                            $net = $row["GRAND_TOT"] / (1 + ($row["GST"] / 100));
                            $vat = $row["GRAND_TOT"] - $net;


                            $net = number_format($row["GRAND_TOT"] / (1 + ($row["GST"] / 100)), 0, ".", "");

                            $vat = $net * $row["GST"] / 100;

                            $m7 = number_format($net, 0, ".", "");
                            $m8 = number_format($vat, 2, ".", "");
                            $i = $i + 1;

                            fputcsv($output, array($m1, $m2, $m3, $m4, $m5, $m6, $m7, $m8));
                        }
                    }

                    if ($_GET['shtype'] == "sh4") {
                        // output headers so that the file is downloaded rather than displayed
                        header('Content-Type: text/csv; charset=utf-8');
                        header('Content-Disposition: attachment; filename=114195383_VAT_SCHEDULE01_1620_20160709_ORIGINAL_V1.csv');

// create a file pointer connected to the output stream
                        $output = fopen('php://output', 'w');

// output the column headings
                        /* 		<th  style="width: 50px;"></b></th>
                          <th  style="width: 110px;"></th>
                          <th  style="width: 80px;"></b></th>
                          <th  style="width: 110px;"></b></th>

                          <th  style="width: 179px;"></th>
                          <th  style="width: 179px;"></th>
                          <th  style="width: 179px;"></th>
                          <th  style="width: 179px;"></th>
                          <th  style="width: 50px;"></th>
                         */

                        fputcsv($output, array("Serial No", "TIN No", "Invoice Date", "Invoice No", "Tax Credit / Tax Debit Note", "Date of Tax Credit / Tax Debit Note", "Tax Credit No. / Tax Debit Note No.", "Value of Tax Credit Note / Tax Debit Note", "VAT Amount"));

                        $sql = "select * from s_salma where sdate >='" . $_GET["dtfrom"] . "' and sdate <='" . $_GET["dtto"] . "'  and cancell='0' and vat ='1' order by id";
                        $i = 1;
                        foreach ($conn->query($sql) as $row) {
                            $m1 = $i;
                            $m2 = date("m/d/Y", strtotime($row['SDATE']));
                            $m3 = $row['REF_NO'];
                            $m4 = substr(trim($row['vat_no']), 0, 9);
                            $m5 = $row['CUS_NAME'];
                            $m6 = "Purchase Of Tyres";

                            $net = $row["GRAND_TOT"] / (1 + ($row["GST"] / 100));
                            $vat = $row["GRAND_TOT"] - $net;


                            $net = number_format($row["GRAND_TOT"] / (1 + ($row["GST"] / 100)), 0, ".", "");

                            $vat = $net * $row["GST"] / 100;

                            $m7 = number_format($net, 0, ".", "");
                            $m8 = number_format($vat, 2, ".", "");
                            $i = $i + 1;

                            fputcsv($output, array($m1, $m2, $m3, $m4, $m5, $m6, $m7, $m8));
                        }
                    }
                }
                ?>
