<?php

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

$maindb = "rawmain";
$trandb = "rawtran";
$formname = "GSTR2_1";
$file = "gstr2_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"])) {

    $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.*, a.taxableamt, DATE_FORMAT(a.`vouchdt`, '%d-%m-%Y') as mydt, sum(b.cgstamt) as cgst,sum(b.igstamt) as igst, 
                             b.gstperc, sum(b.subtot) as tamt  FROM `$maindb` a, `$trandb` b 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.invno ")or die(mysqli_error($conn));
    $get = mysqli_num_rows($getpmain);
//    browse($get);

    $getpmain2 = mysqli_query($conn, "SELECT a.*, a.taxableamt, DATE_FORMAT(a.`vouchdt`, '%d-%m-%Y') as mydt, sum(b.cgstamt) as cgst,sum(b.igstamt) as igst, 
                             b.gstperc, sum(b.subtot) as tamt  FROM `puotmain` a, `puottran` b 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.invno ")or die(mysqli_error($conn));
    $get2 = mysqli_num_rows($getpmain2);

    $getpmain3 = mysqli_query($conn, "SELECT a.*, a.taxableamt, DATE_FORMAT(a.`vouchdt`, '%d-%m-%Y') as mydt, sum(b.cgstamt) as cgst,sum(b.igstamt) as igst, 
                             b.gstperc, sum(b.subtot) as tamt  FROM `purexpmain` a, `purexptran` b 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.invno ")or die(mysqli_error($conn));
    $get3 = mysqli_num_rows($getpmain3);

    $contents.="GST No., Receiver Name, Inv. No, Inv. Date, Type, Inv. Value, Place of Supply,Reverse Charge, Applicable % of Tax Rate, Inv. Type, E-Comm GST, Rate, Taxable Value, Integrated Tax Paid, Central Tax Paid, State/UT Tax Paid, Cess Paid, Eligible For ITC, Availed ITC Integrated Tax, Availed ITC Central Tax, Availed ITC State/UT Tax, Availed ITC Cess\n";

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

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

        $ctr++;
        $gstno = $frecpgst['gstno'];
        $invno = $pmain['invno'];
        $invdt = $pmain['mydt'];

        if ($pmain['mattype'] == 'R') {
            $mattype = "Raw Material";
        } else {
            $mattype = "Packing Material";
        }
        $state = $frecpgst['state'];
        $stcode = $frecpgst['statecode'];
        $revchrg = "N";
        if ($get['deemexp'] != '') {
            $invtype = "Deemed Exp";
        } else {
            $invtype = "Regular";
        }
        if ($get['deemexp'] != '') {
            $invtype = "Deemed Exp";
        } else {
            $invtype = "Regular";
        }
        $ecommgst = '';

//        if ($pmain[oms] == '') {
//          $gstrate = $pmain[cgstperc] * 2;
//        } else {
//          $gstrate = $pmain[igstperc];
//        }
        $invval = $pmain['billamt'];
        $rate = $pmain['gstperc'];
        $taxval = $pmain['tamt'];
        $cess = '';


//        $contents.= $ctr . ",";
        $contents.= $gstno . ",";
        $contents.= $frecpgst['name'] . ",";
        $contents.= $invno . ",";
        $contents.= $invdt . ",";
        $contents.= $mattype . ",";
        $contents.= $invval . ",";
        $contents.= $stcode . '-' . $state . ",";
        $contents.= $revchrg . ",";
        $contents.= "" . ",";
        $contents.= $invtype . ",";
        $contents.= $ecommgst . ",";
        $contents.= $rate . ",";
        $contents.= $taxval . ",";
        $contents.= $pmain['igst'] . ",";
        $contents.= $pmain['cgst'] . ",";
        $contents.= $pmain['cgst'] . ",";
        $contents.= $cess . ",";
        $contents.= "" . ",";
        $contents.= "" . ",";
        $contents.= "" . ",";
        $contents.= "" . "\n";

        $totinvval = $totinvval + $invval;
        $tottaxval = $tottaxval + $taxval;
        $totcgst = $totcgst + $pmain['cgst'];
        $totigst = $totigst + $pmain['igst'];
    }

    if ($tottaxval > 0) {

        $contents.= " " . ",";
        $contents.= " " . ",";
        $contents.= " " . ",";
        $contents.= "Total" . ",";
        $contents.= "" . ",";
        $contents.= $totinvval . ",";
        $contents.= " " . ",";
        $contents.= " " . ",";
        $contents.= " " . ",";
        $contents.= " " . ",";
        $contents.= " " . ",";
        $contents.= " " . ",";
        $contents.= $tottaxval . ",";
        $contents.= $totigst . ",";
        $contents.= $totcgst . ",";
        $contents.= $totcgst . ",";
        $contents.= " " . ",";
        $contents.= "" . ",";
        $contents.= "" . ",";
        $contents.= "" . ",";
        $contents.= "" . ",";
//    $contents.= " " . ",";
        $contents.= "" . "\n";
        $contents.= "\n";
    }
    
//----------------------------------------------------------------------------------------------------------------------------
//    $contents.="Purchase Other\n GST No., Receiver Name, Inv. No, Inv. Date, Type, Inv. Value, Place of Supply,Reverse Charge, Applicable % of Tax Rate, Inv. Type, E-Comm GST, Rate, Taxable Value, Integrated Tax Paid, Central Tax Paid, State/UT Tax Paid, Cess Paid, Eligible For ITC, Availed ITC Integrated Tax, Availed ITC Central Tax, Availed ITC State/UT Tax, Availed ITC Cess\n";

    $ctr = 0;
    $totamt = 0;
    $totdisc = 0;
    $totbill = 0;
    $mattype = "Purchase Other";

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

        $ctr++;
        $gstno = $frecpgst['gstno'];
        $invno = $pmain2['invno'];
        $invdt = $pmain2['mydt'];

        $state = $frecpgst['state'];
        $stcode = $frecpgst['statecode'];
        $revchrg = "N";
        $ecommgst = '';

//        if ($pmain[oms] == '') {
//          $gstrate = $pmain[cgstperc] * 2;
//        } else {
//          $gstrate = $pmain[igstperc];
//        }
        $invval = $pmain2['billamt'];
        $rate = $pmain2['gsttype'];
        $taxval = $pmain2['taxableamt'];
        $cess = '';


//        $contents.= $ctr . ",";
        $contents.= $gstno . ",";
        $contents.= $frecpgst['name'] . ",";
        $contents.= $invno . ",";
        $contents.= $invdt . ",";
        $contents.= $mattype . ",";
        $contents.= $invval . ",";
        $contents.= $stcode . '-' . $state . ",";
        $contents.= $revchrg . ",";
        $contents.= "" . ",";
        $contents.= $invtype . ",";
        $contents.= $ecommgst . ",";
        $contents.= $rate . ",";
        $contents.= $taxval . ",";
        $contents.= $pmain2['igstamt'] . ",";
        $contents.= $pmain2['cgstamt'] . ",";
        $contents.= $pmain2['cgstamt'] . ",";
        $contents.= $cess . ",";
        $contents.= "" . ",";
        $contents.= "" . ",";
        $contents.= "" . ",";
        $contents.= "" . "\n";

        $totinvval = $totinvval + $invval;
        $tottaxval = $tottaxval + $taxval;
        $totcgst = $totcgst + $pmain2['cgstamt'];
        $totigst = $totigst + $pmain2['igstamt'];
    }

    if ($tottaxval > 0) {
        $contents.= " " . ",";
        $contents.= " " . ",";
        $contents.= " " . ",";
        $contents.= "Total" . ",";
        $contents.= "" . ",";
        $contents.= $totinvval . ",";
        $contents.= " " . ",";
        $contents.= " " . ",";
        $contents.= " " . ",";
        $contents.= " " . ",";
        $contents.= " " . ",";
        $contents.= " " . ",";
        $contents.= $tottaxval . ",";
        $contents.= $totigst . ",";
        $contents.= $totcgst . ",";
        $contents.= $totcgst . ",";
        $contents.= " " . ",";
        $contents.= "" . ",";
        $contents.= "" . ",";
        $contents.= "" . ",";
        $contents.= "" . ",";
//    $contents.= " " . ",";
        $contents.= "" . "\n";
        $contents.= "\n";
    }

    // Purchase expense
//    $contents.="GST No., Receiver Name, Inv. No, Inv. Date, Type, Inv. Value, Place of Supply,Reverse Charge, Applicable % of Tax Rate, Inv. Type, E-Comm GST, Rate, Taxable Value, Integrated Tax Paid, Central Tax Paid, State/UT Tax Paid, Cess Paid, Eligible For ITC, Availed ITC Integrated Tax, Availed ITC Central Tax, Availed ITC State/UT Tax, Availed ITC Cess\n";

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

    $mattype = "Purchase Expense";

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

        $ctr++;
        $gstno = $frecpgst['gstno'];
        $invno = $pmain3['invno'];
        $invdt = $pmain3['mydt'];

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

//        if ($pmain[oms] == '') {
//          $gstrate = $pmain[cgstperc] * 2;
//        } else {
//          $gstrate = $pmain[igstperc];
//        }
        $invval = $pmain3['billamt'];
        $rate = $pmain3['gstperc'];
        $taxval = $pmain3['tamt'];
        $cess = '';


//        $contents.= $ctr . ",";
        $contents.= $gstno . ",";
        $contents.= $frecpgst['name'] . ",";
        $contents.= $invno . ",";
        $contents.= $invdt . ",";
        $contents.= $mattype . ",";
        $contents.= $invval . ",";
        $contents.= $stcode . '-' . $state . ",";
        $contents.= $revchrg . ",";
        $contents.= "" . ",";
        $contents.= $invtype . ",";
        $contents.= $ecommgst . ",";
        $contents.= $rate . ",";
        $contents.= $taxval . ",";
        $contents.= $pmain3['igst'] . ",";
        $contents.= $pmain3['cgst'] . ",";
        $contents.= $pmain3['cgst'] . ",";
        $contents.= $cess . ",";
        $contents.= "" . ",";
        $contents.= "" . ",";
        $contents.= "" . ",";
        $contents.= "" . "\n";

        $totinvval = $totinvval + $invval;
        $tottaxval = $tottaxval + $taxval;
        $totcgst = $totcgst + $pmain3['cgst'];
        $totigst = $totigst + $pmain3['igst'];
    }

    $contents.= " " . ",";
    $contents.= " " . ",";
    $contents.= " " . ",";
    $contents.= "Total" . ",";
    $contents.= "" . ",";
    $contents.= $totinvval . ",";
    $contents.= " " . ",";
    $contents.= " " . ",";
    $contents.= " " . ",";
    $contents.= " " . ",";
    $contents.= " " . ",";
    $contents.= " " . ",";
    $contents.= $tottaxval . ",";
    $contents.= $totigst . ",";
    $contents.= $totcgst . ",";
    $contents.= $totcgst . ",";
    $contents.= " " . ",";
    $contents.= "" . ",";
    $contents.= "" . ",";
    $contents.= "" . ",";
    $contents.= "" . ",";
//    $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;
    }
}
?>