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

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

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

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

    mysqli_query($conn, "create temporary table hsndbf1 as SELECT a.hsncode, a.invqty, a.amount, b.discperc, (a.amount - (a.amount * b.discperc/100)) as ttax, 
                             a.gsttran, b.totcgst, b.totsgst, b.totigst, b.oms from `$trandb` a, `$maindb` b 
                             WHERE a.`vouchdt` >= '$mfrmdate' AND a.`vouchdt` <= '$mtodate' and a.vouchno = b.vouchno ") or die(mysqli_error($conn));

    $getptran = mysqli_query($conn, "SELECT hsncode from hsndbf1 group by hsncode order by hsncode")or die(mysqli_error($conn));

    $contents.=",, Sales, \n";
    $contents.="Sr No., HSN, Description, UQC, Tot Qty, Tot Value, Integrated Tax Amt, Central Tax Amt, State/UT Tax Amt, Cess Amt\n";

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

    $totqty = 0;
    $tottaxval = 0;
    $totcgstval = 0;
    $totsgstval = 0;
    $totigstval = 0;

    $amount = 0;
    $taxval = 0;
    $totcgst = 0;
    $totsgst = 0;
    $totigst = 0;
    $cess = " ";
    while ($pmain = mysqli_fetch_array($getptran)) {

//        $gstperc = $pmain['gstperc'];
        $mhsncode = $pmain['hsncode'];
        
        $getcgst = mysqli_query($conn, "SELECT sum(invqty) as tqty, sum(ttax) as taxable, sum(gsttran) as cgst FROM hsndbf1 
                                WHERE `hsncode` = '$mhsncode' and oms != 1") or die(mysqli_error($conn));
        $ctrn     = mysqli_fetch_array($getcgst);

        $getigst = mysqli_query($conn, "SELECT sum(invqty) as tqty, sum(ttax) as taxable, sum(gsttran) as igst FROM hsndbf1 WHERE
                               `hsncode` = '$mhsncode' and oms = 1") or die(mysqli_error($conn));
        $itrn     = mysqli_fetch_array($getigst);

        
        $ctr++;
        $item = 'Fabric';
        $uqc  = "Mtr - Meters ";
        
        $taxcval = $ctrn['taxable'];
        $taxival = $itrn['taxable'];

        $cqty  = $ctrn['tqty'];
        $iqty  = $itrn['tqty'];

        $totcgst = $ctrn['cgst'] / 2 ;
        $totsgst = $ctrn['cgst'] / 2;
        
        $totigst = $itrn['igst'];
        
        $taxval = $taxcval +  $taxival ;
        $ttqty = $cqty + $iqty ;
        
        $cess = " ";

        $contents.=$ctr . ",";
        $contents.=$mhsncode . ",";
        $contents.=$item . ",";
        $contents.=$uqc . ",";
        $contents.=$ttqty . ",";
        $contents.=$taxval . ",";
        $contents.=$totigst . ",";
        $contents.=$totcgst . ",";
        $contents.=$totsgst . ",";
//        $contents.=$totigst . ",";
        $contents.=$cess . "\n";

        $totqty = $totqty + $ttqty;
        $tottaxval = $tottaxval + $taxval;
        $totcgstval = $totcgstval + $totcgst;
        $totsgstval = $totsgstval + $totsgst;
        $totigstval = $totigstval + $totigst;
    }
    $contents.= " " . ",";
    $contents.= "Total" . ",";
    $contents.= " " . ",";
    $contents.= " " . ",";
    $contents.= "$totqty" . ",";
    $contents.= "$tottaxval" . ",";
    $contents.= "$totigstval" . ",";
    $contents.= "$totcgstval" . ",";
    $contents.= "$totsgstval" . ",";
    $contents.= " " . ",";

    $contents.=",, , , , , , , , ,\n";
    $contents.=",, , , , , , , , ,\n";
    $contents.=",, , , , , , , , ,\n";
    
    ////////////////  credit note

    mysqli_query($conn, "create temporary table hsndbf2 as SELECT a.hsncode, a.invqty, a.amount, b.discperc, (a.amount - (a.amount * b.discperc/100)) as ttax, 
                             b.cgstperc, b.sgstperc, b.igstperc, b.oms from `credtran` a, `credmain` b 
                             WHERE a.`vouchdt` >= '$mfrmdate' AND a.`vouchdt` <= '$mtodate' and a.vouchno = b.vouchno ") or die(mysqli_error($conn));

    $getptran2 = mysqli_query($conn, "SELECT hsncode from hsndbf2 group by hsncode order by hsncode")or die(mysqli_error($conn));
    
    
    $contents.=",, Credit Note ,\n";
    $contents.="Sr No., HSN, Description, UQC, Tot Qty, Tot Value, Integrated Tax Amt, Central Tax Amt, State/UT Tax Amt, Cess Amt\n";

    $ctr2 = 0;
    $totamt2 = 0;
    $totdisc2 = 0;
    $totbill2 = 0;

    $totqty2 = 0;
    $tottaxval2 = 0;
    $totcgstval2 = 0;
    $totsgstval2 = 0;
    $totigstval2 = 0;

    $amount2 = 0;
    $taxval2 = 0;
    $totcgst2 = 0;
    $totsgst2 = 0;
    $totigst2 = 0;
    $cess2 = " ";
    while ($pmain2 = mysqli_fetch_array($getptran2)) {

        $mhsncode2 = $pmain2['hsncode'];
        
        $getcgst2 = mysqli_query($conn, "SELECT sum(invqty) as tqty, sum(ttax) as taxable, cgstperc, sgstperc FROM hsndbf2 
                                WHERE `hsncode` = '$mhsncode2' and oms != 1") or die(mysqli_error($conn));
        $ctrn2     = mysqli_fetch_array($getcgst2);

        $getigst2 = mysqli_query($conn, "SELECT sum(invqty) as tqty, sum(ttax) as taxable, igstperc FROM hsndbf2 WHERE
                               `hsncode` = '$mhsncode2' and oms = 1") or die(mysqli_error($conn));
        $itrn2     = mysqli_fetch_array($getigst2);

        
        $ctr2++;
        $hsn  = $pmain2[hsncode];
        $tqty = $pmain2[tqty];
        $item = 'Fabric';
        $uqc  = "Mtr - Meters ";
        
        $taxcval2 = $ctrn2['taxable'];
        $taxival2 = $itrn2['taxable'];
        
        $cqty2  = $ctrn2['tqty'];
        $iqty2  = $itrn2['tqty'];

        $totcgst2 = $ctrn2['taxable'] * ($ctrn2['cgstperc']/100);
        $totsgst2 = $ctrn2['taxable'] * ($ctrn2['sgstperc']/100);
        $totigst2 = $itrn2['taxable'] * ($itrn2['igstperc']/100);
        
        $taxval2 = $taxcval2 + $taxival2 ;
        $ttqty2 = $cqty2 + $iqty2 ;
        
        $cess2 = " ";

        $contents.=$ctr2 . ",";
        $contents.=$hsn . ",";
        $contents.=$item . ",";
        $contents.=$uqc . ",";
        $contents.=$ttqty2 . ",";
        $contents.=$taxval2 . ",";
        $contents.=$totigst2 . ",";
        $contents.=$totcgst2 . ",";
        $contents.=$totsgst2 . ",";
//        $contents.=$totigst . ",";
        $contents.=$cess2 . "\n";

        $totqty2 = $totqty2 + $ttqty2;
        $tottaxval2 = $tottaxval2 + $taxval2;
        $totcgstval2 = $totcgstval2 + $totcgst2;
        $totsgstval2 = $totsgstval2 + $totsgst2;
        $totigstval2 = $totigstval2 + $totigst2;
    }
    $contents.= " " . ",";
    $contents.= "Total" . ",";
    $contents.= " " . ",";
//    $contents.= " " . ",";
    $contents.= " " . ",";
    $contents.= "$totqty2" . ",";
    $contents.= "$tottaxval2" . ",";
    $contents.= "$totigstval2" . ",";
    $contents.= "$totcgstval2" . ",";
    $contents.= "$totsgstval2" . ",";
    $contents.= " " . ",";
   

    $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;
    }
}
?>