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

$maindb = "salemain";
$trandb = "saletran";
$deemmain = "deexmain";

$formname = "B2B";
$file = "gstr1_b2b.php";

$compcd = $_SESSION["comp_" . $dbname];
$zroot = $_SESSION["zdir"];
$admin = $_SESSION["admin_" . $dbname];
$zdb = $_SESSION["zdbyear_" . $dbname];
$myyear = $_SESSION[$myyear];
mysqli_select_db($conn, $zdb);


if (isset($_POST["excel"]) || isset($_GET["excel"])) {
    if (isset($_GET['fdt'])) {
        $mfrmdate = trim($_GET["fdt"]);
    } else {
        $mfrmdate = trim($_POST["fdate"]);
    }

    if (isset($_GET['tdt'])) {
        $mtodate = trim($_GET["tdt"]);
    } else {
        $mtodate = trim($_POST["tdate"]);
    }

    if (empty($mfrmdate)) {
        browse("From Date Cannot Be Empty");
    }
    if (empty($mtodate)) {
        browse("To Date Cannot Be Empty");
    }

    //////////// checking data

    $errflag = 0;

    $qrymis = mysqli_query($conn, "SELECT vouchno, taxableamt from salemain where vouchno not in ( select vouchno from saletran) ") or die(mysqli_error($conn));
    if (mysqli_num_rows($qrymis) > 0) {
        $errflag = 1;

        echo "<table style = 'width: 60%'>";
        echo "<tr style = 'height:10px; width:100%; background-color:pink'>";
        echo "<th style = 'width:100%;'>Invalid Entries. Item Not Found In These Transaction (Local Sale) </th>";
        echo "</tr>";
        echo "</table>";
        
        echo "<table style = 'width: 60%'>";
        echo "<tr style = 'height:10px;background-color: pink'>";
        echo "<th style = 'width:4%;'>Srl.</th>";
        echo "<th style = 'text-align: center;width: 8%;'>Vouchno</th>";
        echo "<th style = 'text-align: right;width: 12%;'>Taxable Amt.</th>";
        echo "</tr>";

        $srl = 0;
        while ($rowmis = mysqli_fetch_array($qrymis)) {
            $srl++;
            Echo "<tr id='boldfont'>";
            Echo "<td>$srl</td>";
            Echo "<td>$rowmis[vouchno]</td>";
            Echo "<td style='text-align:right'>$rowmis[taxableamt]</td>";
            Echo "</tr>";
        }
        echo "</table>";
    }

    $qrymis = mysqli_query($conn, "SELECT vouchno, taxableamt from deexmain where vouchno not in ( select vouchno from deextran) ") or die(mysqli_error($conn));
    if (mysqli_num_rows($qrymis) > 0) {
        $errflag = 1;

        echo "<table style = 'width: 60%'>";
        echo "<tr style = 'height:10px; width:100%; background-color:pink'>";
        echo "<th style = 'width:100%;'>Invalid Entries. Item Not Found In These Transaction (Deemed Sale) </th>";
        echo "</tr>";
        echo "</table>";
        
        echo "<table style = 'width: 60%'>";
        echo "<tr style = 'height:10px;background-color: pink'>";
        echo "<th style = 'width:4%;'>Srl.</th>";
        echo "<th style = 'text-align: center;width: 8%;'>Vouchno</th>";
        echo "<th style = 'text-align: right;width: 12%;'>Taxable Amt.</th>";
        echo "</tr>";

        $srl = 0;
        while ($rowmis = mysqli_fetch_array($qrymis)) {
            $srl++;
            Echo "<tr id='boldfont'>";
            Echo "<td>$srl</td>";
            Echo "<td>$rowmis[vouchno]</td>";
            Echo "<td style='text-align:right'>$rowmis[taxableamt]</td>";
            Echo "</tr>";
        }
        echo "</table>";
    }


    $qrymis = mysqli_query($conn, "SELECT vouchno, taxableamt from salemainexp where vouchno not in ( select vouchno from saletranexp) ") or die(mysqli_error($conn));
    if (mysqli_num_rows($qrymis) > 0) {
        $errflag = 1;

        echo "<table style = 'width: 60%'>";
        echo "<tr style = 'height:10px; width:100%; background-color:pink'>";
        echo "<th style = 'width:100%;'>Invalid Entries. Item Not Found In These Transaction (Exports Sale) </th>";
        echo "</tr>";
        echo "</table>";
        
        echo "<table style = 'width: 60%'>";
        echo "<tr style = 'height:10px;background-color: pink'>";
        echo "<th style = 'width:4%;'>Srl.</th>";
        echo "<th style = 'text-align: center;width: 8%;'>Vouchno</th>";
        echo "<th style = 'text-align: right;width: 12%;'>Taxable Amt.</th>";
        echo "</tr>";

        $srl = 0;
        while ($rowmis = mysqli_fetch_array($qrymis)) {
            $srl++;
            Echo "<tr id='boldfont'>";
            Echo "<td>$srl</td>";
            Echo "<td>$rowmis[vouchno]</td>";
            Echo "<td style='text-align:right'>$rowmis[taxableamt]</td>";
            Echo "</tr>";
        }
        echo "</table>";
    }
    ////////////////////////

    if ($errflag == 0) {

        mysqli_query($conn, "create temporary table main1
                               SELECT a.vouchno, a.vouchdt, a.party, a.invno, a.postamt, a.billamt, sum(b.subtot) as tamt, sum(b.amount) as tamt1, 
                               b.gstperc  FROM `$maindb` a, `$trandb` b 
                               WHERE a.`vouchdt` >= '$mfrmdate' AND a.`vouchdt` <= '$mtodate' and a.vouchno = b.vouchno 
                               and a.canc_flag = '' 
                               and a.party in (select `accode` from accmast where `gstno` != '') 
                               group by b.vouchno, b.gstperc ") or die(mysqli_error($conn));

        $getm1 = mysqli_query($conn, "SELECT * FROM `main1` order by vouchno") or die(mysqli_error($conn));
        
        $getm1 = mysqli_query($conn, "SELECT * FROM `main1` where postamt > 0 and gstperc != 18 group by vouchno order by vouchno") or die(mysqli_error($conn));

        if (mysqli_num_rows($getm1) > 0) {
            while ($p1 = mysqli_fetch_array($getm1)) {
                $vouchno = $p1['vouchno'];

                $getin = mysqli_query($conn, "SELECT * FROM `main1` where postamt > 0 and gstperc = 18 and vouchno = '$vouchno' ") or die(mysqli_error($conn));
                if (mysqli_num_rows($getin) <= 0) {

//                browse($vouchno) ;

                    $vouchdt = $p1['vouchdt'];
                    $invno = $p1['invno'];
                    $billamt = $p1['billamt'];
                    $post = $p1['postamt'];
                    $gstperc = 18;
                    $party = $p1['party'];

//                $tamt = $p1['tamt'];

                    mysqli_query($conn, "insert into main1 (gstperc, vouchno, vouchdt, party, invno, billamt, postamt, tamt ) values 
                                            ('$gstperc','$vouchno', '$vouchdt', '$party', '$invno', '$billamt', '$post', 0 )") or die(mysqli_error($conn));
                }
            }
        }

        mysqli_query($conn, "update main1 set tamt = (tamt + postamt) where gstperc = 18") or die(mysqli_error($conn));

//        $getm1 = mysqli_query($conn, "SELECT * FROM `main1` order by vouchno") or die(mysqli_error($conn));
//        print table ($getm1) ;
//        die();
        
//    $getpmain = mysqli_query($conn, "SELECT a.*, DATE_FORMAT(a.`vouchdt`, '%d-%m-%Y') as mydt, b.gstno, b.state, b.statecode, name as rcvrname
//                             FROM `main1` a, accmast b where a.party = b.accode order by a.vouchdt, a.invno ") or die(mysqli_error($conn));

        $getpmain = mysqli_query($conn, "SELECT a.*, DATE_FORMAT(a.`vouchdt`, '%d-%m-%Y') as mydt, b.gstno, b.state, b.statecode, name as rcvrname
                             FROM `main1` a, accmast b where a.party = b.accode and a.gstperc > 0  order by a.vouchno ") or die(mysqli_error($conn));
//    print table ($getpmain) ;
//    die() ;

        mysqli_query($conn, "create temporary table main2
                        SELECT a.vouchno, a.vouchdt, a.invno, a.party, a.postamt, a.billamt, sum(b.subtot) as tamt, b.gstperc  
                               FROM `deexmain` a, `deextran` b 
                               WHERE a.`vouchdt` >= '$mfrmdate' AND a.`vouchdt` <= '$mtodate' and a.vouchno = b.vouchno and a.canc_flag = '' and 
                               a.party in (select `accode` from accmast where `gstno` != '') 
                               group by b.vouchno, b.gstperc order by a.vouchdt, a.invno ") or die(mysqli_error($conn));

        $getm2 = mysqli_query($conn, "SELECT * FROM `main2` where postamt > 0 and gstperc != 18 group by vouchno order by vouchno") or die(mysqli_error($conn));
        if (mysqli_num_rows($getm2) > 0) {
            while ($p2 = mysqli_fetch_array($getm2)) {
                $vouchno = $p2['vouchno'];

                $getin2 = mysqli_query($conn, "SELECT * FROM `main2` where postamt > 0 and gstperc = 18 and vouchno = '$vouchno' ") or die(mysqli_error($conn));
                if (mysqli_num_rows($getin2) <= 0) {
//                browse($vouchno) ;

                    $vouchdt = $p2['vouchdt'];
                    $invno = $p2['invno'];
                    $billamt = $p2['billamt'];
                    $post = $p2['postamt'];
                    $gstperc = 18;
                    $party = $p2['party'];

//                $tamt = $p2['tamt'];

                    mysqli_query($conn, "insert into main2 (gstperc, vouchno, vouchdt, party, invno, billamt, postamt, tamt ) values 
                                            ('$gstperc','$vouchno', '$vouchdt', '$party', '$invno', '$billamt', '$post', 0 )") or die(mysqli_error($conn));
                }
            }
        }

        mysqli_query($conn, "update main2 set tamt = (tamt + postamt) where gstperc = 18") or die(mysqli_error($conn));

        $getdeemmain = mysqli_query($conn, "SELECT a.*, DATE_FORMAT(a.`vouchdt`, '%d-%m-%Y') as mydt, b.gstno, b.state, b.statecode, name as rcvrname
                             FROM `main2` a, accmast b where a.party = b.accode and a.gstperc > 0 order by a.vouchdt, a.invno ") or die(mysqli_error($conn));


        mysqli_query($conn, "create temporary table main3
                        SELECT a.vouchno, a.vouchdt, a.invno, a.party, 000000.00 as postamt, a.billamt, a.grossamt as tamt, a.gsttype as gstperc  
                               FROM `slotmain` a 
                               WHERE a.`vouchdt` >= '$mfrmdate' AND a.`vouchdt` <= '$mtodate' and a.party in (select `accode` from accmast where `gstno` != '') 
                               group by a.vouchno, a.gsttype order by a.vouchdt, a.invno ") or die(mysqli_error($conn));


        $getothmain = mysqli_query($conn, "SELECT a.*, DATE_FORMAT(a.`vouchdt`, '%d-%m-%Y') as mydt, b.gstno, b.state, b.statecode, name as rcvrname
                             FROM `main3` a, accmast b where a.party = b.accode and a.gstperc > 0 order by a.vouchdt, a.invno ") or die(mysqli_error($conn));

        
        /////////// online
        
        mysqli_query($conn, "create temporary table main4
                           SELECT a.vouchno, a.vouchdt, a.party, a.invno, a.postamt, a.billamt, sum(b.subtot) as tamt, sum(b.amount) as tamt1, 
                               b.gstperc  FROM `slonlmain` a, `slonltran` b 
                               WHERE a.`vouchdt` >= '$mfrmdate' AND a.`vouchdt` <= '$mtodate' and a.vouchno = b.vouchno and a.canc_flag = '' 
                               and a.party in (select `accode` from accmast where `gstno` != '') 
                               group by b.vouchno, b.gstperc ") or die(mysqli_error($conn));

        $getm4 = mysqli_query($conn, "SELECT * FROM `main4` order by vouchno") or die(mysqli_error($conn));

        $getm4 = mysqli_query($conn, "SELECT * FROM `main4` where postamt > 0 and gstperc != 18 group by vouchno order by vouchno") or die(mysqli_error($conn));

        if (mysqli_num_rows($getm1) > 0) {
            while ($p4 = mysqli_fetch_array($getm4)) {
                $vouchno = $p4['vouchno'];

                $getin4 = mysqli_query($conn, "SELECT * FROM `main4` where postamt > 0 and gstperc = 18 and vouchno = '$vouchno' ") or die(mysqli_error($conn));
                if (mysqli_num_rows($getin4) <= 0) {

//                browse($vouchno) ;

                    $vouchdt = $p4['vouchdt'];
                    $invno = $p4['invno'];
                    $billamt = $p4['billamt'];
                    $post = $p4['postamt'];
                    $gstperc = 18;
                    $party = $p4['party'];

//                $tamt = $p1['tamt'];

                    mysqli_query($conn, "insert into main4 (gstperc, vouchno, vouchdt, party, invno, billamt, postamt, tamt ) values 
                                            ('$gstperc','$vouchno', '$vouchdt', '$party', '$invno', '$billamt', '$post', 0 )") or die(mysqli_error($conn));
                }
            }
        }

        mysqli_query($conn, "update main4 set tamt = (tamt + postamt) where gstperc = 18") or die(mysqli_error($conn));
        

//    $getpmain = mysqli_query($conn, "SELECT a.*, DATE_FORMAT(a.`vouchdt`, '%d-%m-%Y') as mydt, b.gstno, b.state, b.statecode, name as rcvrname
//                             FROM `main1` a, accmast b where a.party = b.accode order by a.vouchdt, a.invno ") or die(mysqli_error($conn));

        $getpmain4 = mysqli_query($conn, "SELECT a.*, DATE_FORMAT(a.`vouchdt`, '%d-%m-%Y') as mydt, b.gstno, b.state, b.statecode, name as rcvrname
                             FROM `main4` a, accmast b where a.party = b.accode and a.gstperc > 0 order by a.vouchno ") or die(mysqli_error($conn));
        

        $contents .= "GST No., Receiver Name, Inv. No, Inv. Date, Inv. Value, Place of Supply,Reverse Charge, Applicable % of Tax Rate, Inv. Type, E-Comm GST, Rate, Taxable Value, Cess Amt,GST Amt\n";

        $ctr = 0;
        $totamt = 0;
        $totdisc = 0;
        $totbill = 0;
        $gstamt = 0;

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

            $ctr++;
            $gstno = $pmain['gstno'];
            $state = $pmain['state'];
            $stcode = $pmain['statecode'];
            $rcvr = $pmain['rcvrname'];

            $postamt = $pmain['postamt'];
            $invno = $pmain['invno'];
            $invdt = $pmain['mydt'];
            $invval = $pmain['billamt'];

            $revchrg = "N";
//            if ($get['deemexp'] != '') {
//                $invtype = "Deemed Exp";
//            } else {
//                $invtype = "Regular";
//            }
            $ecommgst = '';
            $invtype = "Regular";            

            $pnetamt = $pmain['billamt'];
            $rate = $pmain['gstperc'];
            $taxval = $pmain['tamt'];
//        if ($taxval == 0) {
//            $taxval = $pmain['tamt1'];
//        }
            $cess = '';
            $gstamt = ($taxval * $rate) / 100;
            $tgstamt1 = $tgstamt1 + $gstamt;

//        $contents.= $ctr . ",";
            $contents .= $gstno . ",";
            $contents .= $rcvr . ",";
            $contents .= "L" . $invno . ",";
            $contents .= $invdt . ",";
            $contents .= $invval . ",";
            $contents .= $stcode . '-' . $state . ",";
            $contents .= $revchrg . ",";
            $contents .= "" . ",";
            $contents .= $invtype . ",";
            $contents .= $ecommgst . ",";
            $contents .= $rate . ",";
            $contents .= $taxval . ",";
            $contents .= "0" . ",";
            $contents .= "$gstamt" . "\n";

            $totinvval = $totinvval + $invval;
            $tottaxval = $tottaxval + $taxval;
        }


        while ($deem = mysqli_fetch_array($getdeemmain)) {
            $ctr++;
            $gstno = $deem['gstno'];
            $invno = $deem['invno'];
            $invdt = $deem['mydt'];
            $invval = $deem['billamt'];
            $state = $deem['state'];
            $stcode = $deem['statecode'];

            $invtype = "Deemed Exp";
            $ecommgst = '';


            $pnetamt = $deem['billamt'];
            $postamt = $deem['postamt'];
            $rate = $deem['gstperc'];
            $taxval2 = $deem['tamt'];
            $cess = '';

            $gstamt = ($taxval2 * $rate) / 100;
            $tgstamt2 = $tgstamt2 + $gstamt;

//        $contents.= $ctr . ",";
            $contents .= $gstno . ",";
            $contents .= $deem['rcvrname'] . ",";
            $contents .= "DE" . $invno . ",";
            $contents .= $invdt . ",";
            $contents .= $invval . ",";
            $contents .= $stcode . '-' . $state . ",";
            $contents .= $revchrg . ",";
            $contents .= "" . ",";
            $contents .= $invtype . ",";
            $contents .= $ecommgst . ",";
            $contents .= $rate . ",";

            $contents .= $taxval2 . ",";
            $contents .= "0" . ",";
            $contents .= "$gstamt" . "\n";

            $totinvval2 = $totinvval2 + $invval;
            $tottaxval2 = $tottaxval2 + $taxval2;
        }


        while ($deem2 = mysqli_fetch_array($getothmain)) {
            $ctr++;
            $gstno = $deem2['gstno'];
            $invno = $deem2['invno'];
            $invdt = $deem2['mydt'];
            $invval = $deem2['billamt'];
            $state = $deem2['state'];
            $stcode = $deem2['statecode'];

            $invtype = "Other Sale";
            $ecommgst = '';


            $pnetamt = $deem2['billamt'];
            $postamt = $deem2['postamt'];
            $rate = $deem2['gstperc'];
            $taxval3 = $deem2['tamt'];
            $cess = '';

            $gstamt = ($taxval3 * $rate) / 100;
            $tgstamt3 = $tgstamt3 + $gstamt;

//        $contents.= $ctr . ",";
            $contents .= $gstno . ",";
            $contents .= $deem2['rcvrname'] . ",";
            $contents .= "OT" . $invno . ",";
            $contents .= $invdt . ",";
            $contents .= $invval . ",";
            $contents .= $stcode . '-' . $state . ",";
            $contents .= $revchrg . ",";
            $contents .= "" . ",";
            $contents .= $invtype . ",";
            $contents .= $ecommgst . ",";
            $contents .= $rate . ",";

            $contents .= $taxval3 . ",";
            $contents .= "0" . ",";
            $contents .= "$gstamt" . "\n";

            $totinvval3 = $totinvval3 + $invval;
            $tottaxval3 = $tottaxval3 + $taxval3;
        }
        
        while ($pmain4 = mysqli_fetch_array($getpmain4)) {

            $ctr++;
            $gstno = $pmain4['gstno'];
            $state = $pmain4['state'];
            $stcode = $pmain4['statecode'];
            $rcvr = $pmain4['rcvrname'];

            $postamt = $pmain4['postamt'];
            $invno = $pmain4['invno'];
            $invdt = $pmain4['mydt'];
            $invval = $pmain4['billamt'];

            $revchrg = "N";
//            if ($get['deemexp'] != '') {
//                $invtype = "Deemed Exp";
//            } else {
//                $invtype = "Regular";
//            }
            $ecommgst = '';
            $invtype = "Online";

            $pnetamt = $pmain4['billamt'];
            $rate = $pmain4['gstperc'];
            $taxval = $pmain4['tamt'];
//        if ($taxval == 0) {
//            $taxval = $pmain['tamt1'];
//        }
            $cess = '';
            $gstamt = ($taxval * $rate) / 100;

            $tgstamt4 = $tgstamt4 + $gstamt;

//        $contents.= $ctr . ",";
            $contents .= $gstno . ",";
            $contents .= $rcvr . ",";
            $contents .= "L" . $invno . ",";
            $contents .= $invdt . ",";
            $contents .= $invval . ",";
            $contents .= $stcode . '-' . $state . ",";
            $contents .= $revchrg . ",";
            $contents .= "" . ",";
            $contents .= $invtype . ",";
            $contents .= $ecommgst . ",";
            $contents .= $rate . ",";
            $contents .= $taxval . ",";
            $contents .= "0" . ",";
            $contents .= "$gstamt" . "\n";

            $totinvval4 = $totinvval4 + $invval;
            $tottaxval4 = $tottaxval4 + $taxval;
        }

        $grtotinvval = $totinvval2 + $totinvval2 + $totinvval + $totinvval4 ;
        $grtottaxval = $tottaxval3 + $tottaxval2 + $tottaxval + $tottaxval4 ;
        $grtotgst = $tgstamt1 + $tgstamt2 + $tgstamt3 + $tgstamt4 ;

        $contents .= " " . ",";
        $contents .= " " . ",";
        $contents .= " " . ",";
        $contents .= "Total" . ",";
        $contents .= $grtotinvval . ",";
        $contents .= " " . ",";
        $contents .= " " . ",";
        $contents .= " " . ",";
        $contents .= " " . ",";
        $contents .= " " . ",";
        $contents .= " " . ",";
        $contents .= $grtottaxval . ",";
        $contents .= " " . ",";
        $contents .= $grtotgst . "\n";
//                                

        $contents = strip_tags($contents);

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