<?php

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

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

$formname = "GSTR1_3";
$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"])) {

    $mfrmdate = trim($_POST["fdate"]);
    $mtodate = trim($_POST["tdate"]);

    if (empty($_POST["fdate"])) {
        browse("From Date Cannot Be Empty");
    }
    if (empty($_POST["tdate"])) {
        browse("To Date Cannot Be Empty");
    }

    $getpmain = mysqli_query($conn, "SELECT a.*, DATE_FORMAT(a.`vouchdt`, '%d-%m-%Y') as mydt,b.*, sum(b.subtot) as tamt, b.gstperc, c.accode, c.state, c.statecode 
                                            FROM `$maindb` a, `$trandb` b, `accmast` c WHERE a.`vouchdt` >= '$mfrmdate' 
                                            AND a.`vouchdt` <= '$mtodate' and a.vouchno = b.vouchno and a.party = c.accode and a.billamt <= 250000 
                                            and c.gstno != '' and b.gstperc > 0
                             group by c.statecode, c.state, b.gstperc order by c.statecode, c.state, a.vouchdt, a.vouchno")or die(mysqli_error($conn));
    $get = mysqli_num_rows($getpmain);

    
//    $getpmain = mysqli_query($conn, "SELECT a.party, a.vouchno as voucher, a.taxableamt,  DATE_FORMAT(a.`vouchdt`, '%d-%m-%Y') as mydt, b.vouchno, b.amount, 
//                 sum(b.subtot) as tamt, b.gstperc, c.accode,  c.gstno, c.state, c.statecode FROM `$maindb` a, `$trandb` b, `accmast` c
//                 WHERE a.`vouchdt` >= '$mfrmdate' AND  a.`vouchdt` <= '$mtodate' and a.vouchno = b.vouchno  and a.party = c.accode  
//                 and c.gstno != '' and a.billamt <= 250000 group by c.state, c.statecode, b.gstperc order by a.vouchdt, a.vouchno")or die(mysqli_error($conn));
//    $get = mysqli_num_rows($getpmain);
// and a.party in (select accode from accmast where gstno != '')

    
//    $getp = mysqli_query($conn, "SELECT a.party, a.vouchno as voucher, a.taxableamt,  DATE_FORMAT(a.`vouchdt`, '%d-%m-%Y') as mydt, 
//                 b.vouchno, b.amount, 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.party in (select accode from accmast where gstno != '')
//                 order by a.vouchdt, a.vouchno")or die(mysqli_error($conn));
//    $get = mysqli_num_rows($getp);
//
//    $getpmain = mysqli_query($conn, "SELECT a.gstperc,a.taxableamt, b.state, b.statecode, a.vouchno, a.mydt, b.gstno FROM `` a, `accmast` b
//                             WHERE a.party = b.accode group by b.statecode, b.state, a.gstperc order by b.state, a.mydt, a.vouchno")or die(mysqli_error($conn));
//    $get = mysqli_num_rows($getpmain);

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

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

    while ($pmain = mysqli_fetch_array($getpmain)) {
        $qrecpgst = mysqli_query($conn, "SELECT `gstno`, `state`, `statecode` from `accmast` where `accode`= '$pmain[party]' ")or die(mysqli_error($conn));
        $frecpgst = mysqli_fetch_array($qrecpgst);

        $ctr++;
        $type = 'OE';
        $state = $pmain[state];
        $stcode = $pmain[statecode];
        $gstrate = $pmain[gstperc];
        $taxval = $pmain[tamt];
//        $state = $pmain[state];
//        $stcode = $pmain[statecode];
        $cess = '';
        $ecommgst = '';

//        $contents.= $ctr . ",";
        $contents.= $type . ",";
        $contents.= $stcode . '-' . $state . ",";
        $contents.= "" . ",";
        $contents.= $gstrate . ",";
        $contents.= $taxval . ",";
        $contents.= $cess . ",";
        $contents.= $ecommgst . "\n";

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

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

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

//    $getpmain1 = mysqli_query($conn, "SELECT a.*, DATE_FORMAT(a.`vouchdt`, '%d-%m-%Y') as mydt,b.*, taxableamt, sum(b.subtot) as tamt, b.gstperc,c.state, c.statecode  FROM `$maindb` a, `$trandb` b, `accmast` c WHERE a.`vouchdt` >= '$mfrmdate' 
//                            AND a.`vouchdt` <= '$mtodate' and a.party in (select accode from accmast where gstno != '') and a.vouchno = b.vouchno 
//                            group by b.vouchno,b.gstperc order by a.vouchdt, a.vouchno")or die(mysqli_error($conn));
//    $get1 = mysqli_num_rows($getpmain1);
    $getpmain1 = mysqli_query($conn, "SELECT a.*, DATE_FORMAT(a.`vouchdt`, '%d-%m-%Y') as mydt,b.*, sum(b.subtot) as tamt, b.gstperc, c.accode FROM `$maindb` a, `$trandb` b, `accmast` c WHERE a.`vouchdt` >= '$mfrmdate' 
                             AND a.`vouchdt` <= '$mtodate' and a.vouchno = b.vouchno and a.party = c.accode and a.billamt <= 250000 and c.gstno != '' and b.gstperc > 0
                             group by b.vouchno, b.gstperc order by a.vouchdt, a.vouchno")or die(mysqli_error($conn));
    $get = mysqli_num_rows($getpmain1);


//    print table($getpmain);
//    print table($getpmain1);

    $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)) {
        $qrecpgst = mysqli_query($conn, "SELECT `gstno`, `state`, `statecode` from `accmast` where `accode`= '$pmain1[party]' ")or die(mysqli_error($conn));
        $frecpgst = mysqli_fetch_array($qrecpgst);

        $ctr++;
        $type1 = 'OE';
        $state1 = $frecpgst[state];
        $stcode1 = $frecpgst[statecode];
        $gstrate1 = $pmain1[gstperc];
        $taxval1 = $pmain1[tamt];
//        $state = $pmain[state];
//        $stcode = $pmain[statecode];
        $cess1 = '';
        $ecommgst1 = '';

//        $contents.= $ctr . ",";
        $contents.= $type1 . ",";
        $contents.= $stcode1 . '-' . $state1 . ",";
        $contents.= "" . ",";
        $contents.= $gstrate1 . ",";
        $contents.= $taxval1 . ",";
        $contents.= $pmain1[vouchno] . ",";
        $contents.= $pmain1[vouchdt] . ",";
        $contents.= $pmain1[invno] . ",";
        $contents.= $cess1 . ",";
        $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;
    }
}
?>