<?php

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

$formname = "GSTR1_10";
$file = "gstr1_10.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"])) {

    include('includes/head_excel_rep.php');

    $para = $_POST["para"];

    if ($para == "P") {
        $maindb = "gstmain";
        $trandb = "gsttran";
        $proddb = "item";
        $mainretdb = "credmain";
        $tranretdb = "credtran";
    } else {
        $maindb = "rawmain";
        $trandb = "rawtran";
        $proddb = "rawmat";
        $mainretdb = "credrawmain";
        $tranretdb = "credrawtran";
    }

    $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");
    }

    $contents .= "HSN, Description, UQC, Total Quantity, Tot Value, GST %, Taxable Value, Integrated Tax Amt, Central Tax Amt, State/UT Tax Amt, Cess Amt\n";

    mysqli_query($conn, "create temporary table saledt 
            SELECT 'T' as source, a.vouchno, a.vouchdt, a.`billamt`, b.`item`, b.`hsncode`, b.`qty`, 
            b.subtot as tamt, b.cgstamt as tcgstamt, b.sgstamt as tsgstamt, b.igstamt as tigstamt, 
            b.total as totval, b.gstperc FROM `$maindb` a, `$trandb` b WHERE a.`vouchdt` >= '$mfrmdate' AND a.`vouchdt` <= '$mtodate' 
            and a.vouchno = b.vouchno and a.canc_flag = '' ") or die(mysqli_error($conn));

    $getmain = mysqli_query($conn, "SELECT vouchno, vouchdt, `billamt`, `item`, `hsncode`, sum(`qty`) as itemqty, 
                                           sum(tamt) as tamt, sum(tcgstamt) as tcgstamt, sum(tsgstamt) as tsgstamt, sum(tigstamt) as tigstamt, 
                                           sum(`totval`) as totval, `gstperc` FROM `saledt` WHERE `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' 
                                           group by hsncode, gstperc order by hsncode, gstperc") or die(mysqli_error($conn));


    $get1 = mysqli_query($conn, "SELECT count(*) as pnos, sum(packchgs) as post from `$maindb`  WHERE `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and oms != '1' and canc_flag = '' and packchgs > 0") or die(mysqli_error($conn));
    $p1 = mysqli_fetch_array($get1);
    $c1 = $p1['post'];
    $pn1 = $p1['pnos'];
    $cpost = $c1;
    $spost = $cpost / 2;
    $spgst = $spost * 0.05;

    $got1 = mysqli_query($conn, "SELECT count(*) as pnos, sum(packchgs) as post from `$maindb`  WHERE `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and oms = 1 and canc_flag = '' and packchgs > 0") or die(mysqli_error($conn));
    $o1 = mysqli_fetch_array($got1);
    $po1 = $o1['pnos'];
    $i1 = $o1['post'];
    $ipost = $i1;
    $ipgst = $ipost * 0.05;


    while ($pmain = mysqli_fetch_array($getmain)) {
        $getitem = mysqli_query($conn, "SELECT `name`, `unit` from `$proddb` WHERE `code`='$pmain[item]'") or die(mysqli_error($conn));
        $fitem = mysqli_fetch_array($getitem);

        $ctr++;
        $hsn = $pmain['hsncode'];
        $item = $fitem['name'];

        //        $uqc = $fitem['unit'];
        $uqc = 'PCS-PIECES';

        $qty = $pmain['itemqty'];
        $gstperc = $pmain['gstperc'];

        $ctotval = $ctaxval = $ctotcgst = $ctotsgst = $ctotigst = 0;

        $cqty = 0;
        $ctotval = 0;
        $ctaxval = 0;
        $ctotcgst = 0;
        $ctotsgst = 0;
        $ctotigst = 0;

        $qty = $qty - $cqty;
        $invval = $pmain['totval'] - $ctotval;

        $taxval = $pmain['tamt'] - $ctaxval;
        $totcgst = $pmain['tcgstamt'] - $ctotcgst;
        $totsgst = $pmain['tsgstamt'] - $ctotsgst;
        $totigst = $pmain['tigstamt'] - $ctotigst;
        $cess = " ";

        //        $contents.=$ctr . ",";
        $contents .= $hsn . ",";
        $contents .= $item . ",";
        $contents .= $uqc . ",";
        $contents .= $qty . ",";
        $contents .= $invval . ",";
        $contents .= $gstperc . ",";
        $contents .= $taxval . ",";
        $contents .= $totigst . ",";
        $contents .= $totcgst . ",";
        $contents .= $totsgst . ",";
        //        $contents.=$totigst . ",";
        $contents .= $cess . "\n";

        $totqty = $totqty + $qty;
        $totinvval = $totinvval + $invval;
        $tottaxval = $tottaxval + $taxval;
        $ttotigst = $ttotigst + $totigst;
        $ttotcgst = $ttotcgst + $totcgst;
        $ttotsgst = $ttotsgst + $totsgst;
    }
    $contents .= "996812" . ",";
    $contents .= "COURIER IN SALE" . ",";
    $contents .= "NOS-NUMBERS" . ",";
    $contents .= $pn1 . ",";
    $contents .= $c1 . ",";
    $contents .= "5" . ",";
    $contents .= round($cpost + $ipost, 2) . ",";
    $contents .= round($ipgst, 2) . ",";
    $contents .= round($spgst, 2) . ",";
    $contents .= round($spgst, 2) . ",";
    $contents .= " " . "\n";

    $totqty = $totqty + $totpp;
    $totinvval = $totinvval + $totpost;
    $tottaxval = $tottaxval + $cpost + $ipost;
    $ttotigst = $ttotigst + $ipgst;
    $ttotcgst = $ttotcgst + $spgst;
    $ttotsgst = $ttotsgst + $spgst;

    $contents .= " " . ",";
    $contents .= "Sale Total" . ",";
    $contents .= " " . ",";
    $contents .= round($totqty, 0) . ",";
    $contents .= round($totinvval, 2) . ",";
    $contents .= " " . ",";
    $contents .= round($tottaxval, 2) . ",";
    $contents .= round($ttotigst, 2) . ",";
    $contents .= round($ttotcgst, 2) . ",";
    $contents .= round($ttotsgst, 2) . ",";
    $contents .= " " . ",";

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

    mysqli_query($conn, "drop temporary table if exists creddt") or die(mysqli_error($conn));
    mysqli_query($conn, "create temporary table creddt 
                             SELECT a.vouchno, a.vouchdt, a.`billamt`, b.`item` as itemcode, b.`hsncode`, sum(b.`qty`) as itemqty, 
                             sum(b.subtot) as tamt, sum(b.cgstamt) as tcgstamt, sum(b.sgstamt) as tsgstamt, sum(b.igstamt) as tigstamt, 
                             sum(b.`total`) as totval, b.`gstperc`, 0 as saleflag FROM `$mainretdb` a, `$tranretdb` b 
                             WHERE a.`vouchdt` >= '$mfrmdate' AND a.`vouchdt` <= '$mtodate' and a.canc_flag = '' 
                             and a.vouchno = b.vouchno group by b.hsncode, b.gstperc ") or die(mysqli_error($conn));

    $getcred = mysqli_query($conn, "SELECT * from `creddt` order by hsncode, gstperc ") or die(mysqli_error($conn));

    if (mysqli_num_rows($getcred) > 0) {
        while ($cmain = mysqli_fetch_array($getcred)) {

            $getitem = mysqli_query($conn, "SELECT `name`, `unit` from `item` WHERE `code`='$cmain[itemcode]'") or die(mysqli_error($conn));
            $fitem = mysqli_fetch_array($getitem);

            $ctr++;
            $hsn = $cmain['hsncode'];
            $item = $fitem['name'];
            //            $uqc = $fitem['unit'];
            $uqc = 'PCS-PIECES';

            $qty = $cmain['itemqty'] * -1;
            $gstperc = $cmain['gstperc'] * -1;
            $invval = $cmain['totval'] * -1;
            $taxval = $cmain['tamt'] * -1;
            $totcgst = $cmain['tcgstamt'] * -1;
            $totsgst = $cmain['tsgstamt'] * -1;
            $totigst = $cmain['tigstamt'] * -1;
            $cess = " ";

            //        $contents.=$ctr . ",";
            $contents .= $hsn . ",";
            $contents .= $item . ",";
            $contents .= $uqc . ",";
            $contents .= $qty . ",";
            $contents .= $invval . ",";
            $contents .= $gstperc . ",";
            $contents .= $taxval . ",";
            $contents .= $totigst . ",";
            $contents .= $totcgst . ",";
            $contents .= $totsgst . ",";
            //        $contents.=$totigst . ",";
            $contents .= $cess . "\n";

            $crdqty = $crdqty + $qty;
            $crdinvval = $crdinvval + $invval;
            $crdtaxval = $crdtaxval + $taxval;
            $crdtotigst = $crdtotigst + $totigst;
            $crdtotcgst = $crdtotcgst + $totcgst;
            $crdtotsgst = $crdtotsgst + $totsgst;
        }



        $contents .= " " . ",";
        $contents .= "Credit Total" . ",";
        $contents .= " " . ",";
        $contents .= round($crdqty, 0) . ",";
        $contents .= round($crdinvval, 2) . ",";
        $contents .= " " . ",";
        $contents .= round($crdtaxval, 2) . ",";
        $contents .= round($crdtotigst, 2) . ",";
        $contents .= round($crdtotcgst, 2) . ",";
        $contents .= round($crdtotsgst, 2) . ",";
        $contents .= " " . ",";
        $contents .= " " . "\n";
        $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;
    }
}
?>