<?php

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

$maindb = "salemain";
$trandb = "saletran";
$formname = "GSTR1_2";
$file = "gstr1_2.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"]);
    $saletype = trim($_POST["gstperc"]);

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

//    $gst5 = "AND gstperc = 5";
//    $gst12 = "AND gstperc = 12";
//    $gst18 = "AND gstperc = 18";
//    $gst24 = "AND gstperc = 24";
//
//    if ($saletype = "1") {
//        $gperc = "AND  `gstperc`='1'";
//    } elseif ($saletype = "2") {
//        $gperc = "AND  `gstperc`='2'";
//    } elseif ($saletype = "3") {
//        $gperc = "AND  `gstperc`='3'";
//    } else {
//        $gperc = "AND  `gstperc`='4'";
//    }
//    $getpmain = mysqli_query($conn, "SELECT *, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt FROM `$maindb` WHERE netamt > 250000 and `vouchdt` >= '$mfrmdate' 
//                            AND `vouchdt` <= '$mtodate' ")or die(mysqli_error($conn));
//    $get = mysqli_num_rows($getpmain);
////    browse($get);
//    $getpmain = mysqli_query($conn, "SELECT a.*, DATE_FORMAT(a.`vouchdt`, '%d-%m-%Y') as mydt,b.*, sum(b.subtot) as tamt  FROM `$maindb` a, `$trandb` b 
//                             WHERE a.totalamount > 250000 and a.`vouchdt` >= '$mfrmdate' AND a.`vouchdt` <= '$mtodate' and a.vouchno = b.vouchno 
//                             group by b.vouchno,b.gstperc order by a.vouchdt, a.vouchno ")or die(mysqli_error($conn));
//    $pmain = mysqli_fetch_array($getpmain);
//    
    $getsltype  = mysqli_query($conn, "SELECT code, name  from `saletype` order by per") or die(mysqli_error($conn));
    $getsltype2 = mysqli_query($conn, "SELECT code, name  from `saletype` order by per") or die(mysqli_error($conn));
    $getsltype3 = mysqli_query($conn, "SELECT *  from `saletype` order by per") or die(mysqli_error($conn));
    $getsltype4 = mysqli_query($conn, "SELECT *  from `saletype` order by per") or die(mysqli_error($conn));
//    $sltype = mysqli_fetch_array($getsltype);

    $contents.="PURCHASE\n";
    $contents.="Sr., Description, CGST, , SGST, ,IGST,\n";
    $contents.=" , , Amount, Tax, Amount, Tax, Amount, Tax\n";

    while ($sltype = mysqli_fetch_array($getsltype)) {
        $getcpur = mysqli_query($conn, "SELECT  sum(tottaxamt) as camt, sum(totcgst) as pctax from `purmain` where vouchdt >= '$mfrmdate'
                                AND vouchdt <= '$mtodate' and (oms ='N' or oms!='1') AND gstperc='$sltype[code]'") or die(mysqli_error($conn));
        $cpur = mysqli_fetch_array($getcpur);

        $getipur = mysqli_query($conn, "SELECT  sum(tottaxamt) as iamt, sum(totigst) as pitax from `purmain` where vouchdt >= '$mfrmdate'
                                AND vouchdt <= '$mtodate' and (oms ='Y' or oms='1') AND gstperc='$sltype[code]'") or die(mysqli_error($conn));
        $ipur = mysqli_fetch_array($getipur);

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

        $ctr++;
        $contents.= $ctr . ",";
        $contents.= $sltype[name] . ",";
        $contents.= $cpur[camt] . ",";
        $contents.= $cpur[pctax] . ",";
        $contents.= $cpur[camt] . ",";
        $contents.= $cpur[pctax] . ",";
        $contents.= $ipur[iamt] . ",";
        $contents.= $ipur[pitax] . "\n";
    }

    $contents.="\n\n\n";
    $contents.="PURCHASE RETURN\n";
    $contents.="Sr., Description, CGST, , SGST, ,IGST,\n";
    $contents.=" , , Amount, Tax, Amount, Tax, Amount, Tax\n";

    while ($sltype2 = mysqli_fetch_array($getsltype2)) {
        $getcpur = mysqli_query($conn, "SELECT  sum(tottaxamt) as camt, sum(totcgst) as pctax from `debmain` where vouchdt >= '$mfrmdate'
                                AND vouchdt <= '$mtodate' and (oms ='N' or oms!='1') AND gstperc='$sltype2[code]'") or die(mysqli_error($conn));
        $cpur = mysqli_fetch_array($getcpur);

        $getipur = mysqli_query($conn, "SELECT  sum(tottaxamt) as iamt, sum(totigst) as pitax from `debmain` where vouchdt >= '$mfrmdate'
                                AND vouchdt <= '$mtodate' and (oms ='Y' or oms='1') AND gstperc='$sltype2[code]'") or die(mysqli_error($conn));
        $ipur = mysqli_fetch_array($getipur);

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

        $ctr++;
        $contents.= $ctr . ",";
        $contents.= $sltype2[name] . ",";
        $contents.= $cpur[camt] . ",";
        $contents.= $cpur[pctax] . ",";
        $contents.= $cpur[camt] . ",";
        $contents.= $cpur[pctax] . ",";
        $contents.= $ipur[iamt] . ",";
        $contents.= $ipur[pitax] . "\n";
    }
    
    $contents.="\n\n\n";
    $contents.="SALE\n";
    $contents.="Sr., Description, CGST, , SGST, ,IGST,\n";
    $contents.=" , , Amount, Tax, Amount, Tax, Amount, Tax\n";


    while ($sltype3 = mysqli_fetch_array($getsltype3)) {

        $getcpur = mysqli_query($conn, "SELECT a.vouchno, a.oms, b.vouchno, sum(b.subtot) as camt, sum(b.cgstamt) as cgstamt, sum(b.sgstamt) as sgstamt 
                            from `salemain` a, `saletran` b where a.vouchdt >= '$mfrmdate' AND a.vouchdt <= '$mtodate' and a.vouchno = b.vouchno 
                            and (a.oms ='N' or a.oms!='1') and b.`gstperc`='$sltype3[per]'") or die(mysqli_error($conn));
        $cpur = mysqli_fetch_array($getcpur);
        
        $getcpur1 = mysqli_query($conn, "SELECT a.vouchno, a.oms, b.vouchno, sum(b.subtot) as itax, sum(b.igstamt) as igstamt 
                            from `salemain` a, `saletran` b where a.vouchdt >= '$mfrmdate' AND a.vouchdt <= '$mtodate' and a.vouchno = b.vouchno 
                            and (a.oms ='Y' or a.oms='1') and b.`gstperc`='$sltype3[per]'") or die(mysqli_error($conn));
        $cpur1 = mysqli_fetch_array($getcpur1);
//        print table($getcpur1);

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

        $ctr++;
        $contents.= $ctr . ",";
        $contents.= $sltype3[name] . ",";
        $contents.= $cpur[camt] . ",";
        $contents.= $cpur[cgstamt] . ",";
        $contents.= $cpur[camt] . ",";
        $contents.= $cpur[sgstamt] . ",";
        $contents.= $cpur1[itax] . ",";
        $contents.= $cpur1[igstamt] . "\n";
    }

    $contents.="\n\n\n";
    $contents.="SALE RETURN\n";
    $contents.="Sr., Description, CGST, , SGST, ,IGST,\n";
    $contents.=" , , Amount, Tax, Amount, Tax, Amount, Tax\n";


    while ($sltype4 = mysqli_fetch_array($getsltype4)) {

        $getcpur = mysqli_query($conn, "SELECT a.vouchno, a.oms, b.vouchno, sum(b.total) as camt, sum(b.cgstamt) as cgstamt, sum(b.sgstamt) as sgstamt 
                            from `credmain` a, `credtran` b where a.vouchdt >= '$mfrmdate' AND a.vouchdt <= '$mtodate' and a.vouchno = b.vouchno 
                            and (a.oms ='N' or a.oms!='1') and b.`gstperc`='$sltype3[per]'") or die(mysqli_error($conn));
        $cpur = mysqli_fetch_array($getcpur);
        
        $getcpur1 = mysqli_query($conn, "SELECT a.vouchno, a.oms, b.vouchno, sum(b.total) as itax, sum(b.igstamt) as igstamt 
                            from `credmain` a, `credtran` b where a.vouchdt >= '$mfrmdate' AND a.vouchdt <= '$mtodate' and a.vouchno = b.vouchno 
                            and (a.oms ='Y' or a.oms='1') and b.`gstperc`='$sltype3[per]'") or die(mysqli_error($conn));
        $cpur1 = mysqli_fetch_array($getcpur1);
//        print table($getcpur1);

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

        $ctr++;
        $contents.= $ctr . ",";
        $contents.= $sltype4[name] . ",";
        $contents.= $cpur[camt] . ",";
        $contents.= $cpur[cgstamt] . ",";
        $contents.= $cpur[camt] . ",";
        $contents.= $cpur[sgstamt] . ",";
        $contents.= $cpur1[itax] . ",";
        $contents.= $cpur1[igstamt] . "\n";
    }

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