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

$maindb = "salemain";
$trandb = "saletran";
$formname = "B2C Small (Unregistered";
$file = "gstr1_b2cs.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");
    }

    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, 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.billamt <= 250000 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` 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'];
                $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));


    ///////////////  deexmain
    
    mysqli_query($conn, "create temporary table main2
                        SELECT a.vouchno, a.vouchdt, a.party, a.invno, 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.billamt <= 250000 and    
                               a.party in (select `accode` from accmast where `gstno` = '') 
                               group by b.vouchno, b.gstperc ") 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) . '-dee';

                $vouchdt = $p2['vouchdt'];
                $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));
    
    
    ///////////////  online sale
    
    mysqli_query($conn, "create temporary table main3
                        SELECT a.vouchno, a.vouchdt, a.party, a.invno, a.postamt, a.billamt, sum(b.subtot) as tamt, 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.billamt <= 250000 and 
                               a.party in (select `accode` from accmast where `gstno` = '') 
                               group by a.vouchno, b.gstperc ") or die(mysqli_error($conn));

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

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

                $vouchdt = $p3['vouchdt'];
                $post = $p3['postamt'];
                $gstperc = 18;
                $party = $p3['party'];
                $tamt = $p3['tamt'];

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

//    $get3 = mysqli_query($conn, "SELECT * FROM `main3` ") or die(mysqli_error($conn));
//    print table ($get3) ;
    
    mysqli_query($conn, "update main3 set tamt = (tamt + postamt) where gstperc = 18") or die(mysqli_error($conn));

//    $get3 = mysqli_query($conn, "SELECT * FROM `main3` ") or die(mysqli_error($conn));
//    print table ($get3) ;
//    die() ;
    

//$getm3 = mysqli_query($conn, "SELECT sum(billamt), sum(tamt), sum(postamt) FROM `main3` order by vouchno") or die(mysqli_error($conn));
//print table($getm3);
//die();
    
    mysqli_query($conn, "create temporary table b2csdata 
                        SELECT * from main1  
                        UNION ALL      
                        SELECT * from main2 
                        UNION ALL      
                        SELECT * from main3 
                        order by vouchdt, vouchno ") or die(mysqli_error($conn));

//$getm3 = mysqli_query($conn, "SELECT * from b2csdata ") or die(mysqli_error($conn));
//print table($getm3);
//die();
    
    $getpmain = mysqli_query($conn, "SELECT a.state, a.statecode, b.gstperc, sum(b.tamt) as tamt FROM accmast a, b2csdata b
                             where a.accode = b.party and a.gstno = '' group by a.statecode, b.gstperc
                             order by a.statecode, a.state ") or die(mysqli_error($conn));

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

    mysqli_query($conn, "create temporary table b2csret
                         SELECT a.state, a.statecode, b.gstperc, sum(b.tamt) as tamt FROM accmast a, creddbf b
                             where a.accode = b.party and a.gstno = '' group by a.statecode, b.gstperc
                             order by a.statecode, a.state ") or die(mysqli_error($conn));
    
//$getret = mysqli_query($conn, "SELECT * FROM  b2csret  ") or die(mysqli_error($conn));
//print table($getret);
//die();
    
    $contents.="Type, Place of Supply, Applicable % of Tax Rate, Rate, Taxable Value, Return Amt., Cess Amt, E-Comm. GST\n";

    $ctr = 0;
    $totamt = 0;
    $totdisc = 0;
    $totbill = 0;
    $tottaxval = 0 ;
    $tottaxret = 0 ;
        
   
    while ($pmain = mysqli_fetch_array($getpmain)) {
        $ctr++;
        $type = 'OE';
        $state = $pmain[state];
        $stcode = $pmain[statecode];
        $gstrate = $pmain[gstperc];
        $taxval = $pmain[tamt];
        $cess = '';
        $ecommgst = '';
        
        $taxret = 0 ;
        $getret = mysqli_query($conn, "SELECT tamt FROM  b2csret where statecode = '$stcode' and gstperc = '$gstrate'  ") or die(mysqli_error($conn));
        $pret = mysqli_fetch_array($getret) ;
        $taxret = $pret['tamt'];
        
//        $contents.= $ctr . ",";
        $contents.= $type . ",";
        $contents.= $stcode . '-' . $state . ",";
        $contents.= "" . ",";
        $contents.= $gstrate . ",";
        $contents.= $taxval . ",";
        $contents.= $taxret . ",";
        $contents.= $cess . ",";
        $contents.= $ecommgst . "\n";

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

    $contents.= " " . ",";
    $contents.= "Total" . ",";
//    $contents.= " " . ",";
    $contents.= " " . ",";
    $contents.= " " . ",";
    $contents.= $tottaxval . ",";
    $contents.= $tottaxret . ",";
    $contents.= " " . "\n";

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

    $getpmain1 = mysqli_query($conn, "SELECT a.state, a.statecode, b.vouchno, b.vouchdt, b.invno, b.gstperc, b.tamt, b.postamt 
                                      FROM accmast a, b2csdata b
                                      where a.accode = b.party and a.gstno = '' order by a.statecode, a.state ") or die(mysqli_error($conn));
 
    $get = mysqli_num_rows($getpmain1);

//    print table($getpmain);
//    print table($getpmain1);
    
//    mysqli_query($conn, "drop temporary table main1") or die(mysqli_error($conn));
//    mysqli_query($conn, "drop temporary table main2") or die(mysqli_error($conn));
//    mysqli_query($conn, "drop temporary table main3") or die(mysqli_error($conn));
//    mysqli_query($conn, "drop temporary table b2csdata") or die(mysqli_error($conn));
//    mysqli_query($conn, "drop temporary table creddata") or die(mysqli_error($conn));
//    mysqli_query($conn, "drop temporary table b2csret") or die(mysqli_error($conn));
    

    $contents.="Type, Place of Supply,Applicable % of Tax Rate, Rate, Taxable Value,Vouchno, Vouchdt,Invoice No., Cess Amt, E-Comm. GST\n";

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

    while ($pmain1 = mysqli_fetch_array($getpmain1)) {

        $ctr++;
        $type1 = 'OE';
        $state1 = $pmain1[state];
        $stcode1 = $pmain1[statecode];
        $gstrate1 = $pmain1[gstperc];
        $taxval1 = $pmain1[tamt];
        $postamt1 = $pmain1[postamt];
        $cess1 = '';
        $ecommgst1 = '';

//        $contents.= $ctr . ",";
        $contents.= $type1 . ",";
        $contents.= $stcode1 . '-' . $state1 . ",";
        $contents.= "" . ",";
        $contents.= $gstrate1 . ",";
        $contents.= $taxval1 . ",";
        $contents.= $pmain1[vouchno] . ",";
        $contents.= $pmain1[vouchdt] . ",";
        $contents.= $pmain1[invno] . ",";
        $contents.= $postamt1 . ",";
        $contents.= $ecommgst1 . "\n";

        $totinvval1 = $totinvval1 + $invval1;
        $tottaxval1 = $tottaxval1 + $taxval1;
    }

    $contents.= " " . ",";
    $contents.= "Total" . ",";
//    $contents.= " " . ",";
    $contents.= " " . ",";
    $contents.= " " . ",";
    $contents.= $tottaxval1 . ",";
    $contents.= " " . "\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;
    }
}
?>