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

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

$deexmain = "deexmain";
$deextran = "deextran";

$formname = "HSN Summary";
$file = "gstr1_hsn.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"]) || isset($_GET["excel"])) {
    if (isset($_GET['fdt'])) {
        $mfrmdate = trim($_GET["fdt"]);
    } else {
        $mfrmdate = trim($_POST["fdate"]);
    }

    if (isset($_GET['tdt'])) {
        $mtodate = trim($_GET["tdt"]);
    } else {
        $mtodate = trim($_POST["tdate"]);
    }

    if (empty($mfrmdate)) {
        browse("From Date Cannot Be Empty");
    }
    if (empty($mtodate)) {
        browse("To Date Cannot Be Empty");
    }

    mysqli_query($conn, "drop temporary table if exists saledt") or die(mysqli_error($conn));
    mysqli_query($conn, "create temporary table saledt SELECT a.vouchno, a.vouchdt, a.`billamt`, b.`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` FROM `$maindb` a, `$trandb` b WHERE a.`vouchdt` >= '$mfrmdate' AND a.`vouchdt` <= '$mtodate' 
                             and a.vouchno = b.vouchno and a.canc_flag = '' group by b.hsncode, b.gstperc
                             UNION ALL
                             SELECT a.vouchno, a.vouchdt, a.`billamt`, b.`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` FROM `$deexmain` a, `$deextran` b WHERE a.`vouchdt` >= '$mfrmdate' AND a.`vouchdt` <= '$mtodate' 
                             and a.vouchno = b.vouchno and a.canc_flag = '' group by b.hsncode, b.gstperc
                             UNION ALL
                             SELECT a.vouchno, a.invdate as vouchdt, a.`billamt`, b.`itemcode`, b.`hsncode`, sum(b.`qty`) as itemqty, 
                             sum(b.amtcurr) as tamt, 0 as tcgstamt, 0 as tsgstamt, 0 as tigstamt, 
                             sum(b.amtcurr) as totval, 0 as `gstperc` FROM `salemainexp` a, `saletranexp` b WHERE a.`invdate` >= '$mfrmdate' AND a.`invdate` <= '$mtodate' 
                             and a.vouchno = b.vouchno and a.canc_flag = '' group by b.hsncode
                             UNION ALL
                             SELECT a.vouchno, a.vouchdt, a.`billamt`, b.`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` FROM `slonlmain` a, `slonltran` b WHERE a.`vouchdt` >= '$mfrmdate' AND a.`vouchdt` <= '$mtodate' 
                             and a.vouchno = b.vouchno and a.canc_flag = '' group by b.hsncode, b.gstperc") or die(mysqli_error($conn));


    $getmain = mysqli_query($conn, "SELECT vouchno, vouchdt, `billamt`, `itemcode`, `hsncode`, sum(`itemqty`) 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 ") or die(mysqli_error($conn));

    
    $get1 = mysqli_query($conn, "SELECT count(*) as pnos, sum(postamt) as post from `salemain`  WHERE `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and oms != 1 and canc_flag = '' and postamt > 0") or die(mysqli_error($conn));
    $get2 = mysqli_query($conn, "SELECT count(*) as pnos, sum(postamt) as post from `deexmain`  WHERE `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and oms != 1 and canc_flag = '' and postamt > 0") or die(mysqli_error($conn));
    $get3 = mysqli_query($conn, "SELECT count(*) as pnos, sum(postamt) as post from `slonlmain` WHERE `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and oms != 1 and canc_flag = '' and postamt > 0") or die(mysqli_error($conn));

    $p1 = mysqli_fetch_array($get1);
    $p2 = mysqli_fetch_array($get2);
    $p3 = mysqli_fetch_array($get3);

    $c1 = $p1['post'];
    $c2 = $p2['post'];
    $c3 = $p3['post'];

    $pn1 = $p1['pnos'];
    $pn2 = $p2['pnos'];
    $pn3 = $p3['pnos'];
    
    $totpnos = $pn1 + $pn2 + $pn3 ;

    $cpost = $c1 + $c2 + $c3;
    $spost = $cpost / 2;

    $spgst = $spost * 0.18;

    $got1 = mysqli_query($conn, "SELECT count(*) as pnos, sum(postamt) as post from `salemain`  WHERE `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and oms = 1 and canc_flag = '' and postamt > 0") or die(mysqli_error($conn));
    $got2 = mysqli_query($conn, "SELECT count(*) as pnos, sum(postamt) as post from `deexmain`  WHERE `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and oms = 1 and canc_flag = '' and postamt > 0") or die(mysqli_error($conn));
    $got3 = mysqli_query($conn, "SELECT count(*) as pnos, sum(postamt) as post from `slonlmain` WHERE `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and oms = 1 and canc_flag = '' and postamt > 0") or die(mysqli_error($conn));

    $o1 = mysqli_fetch_array($got1);
    $o2 = mysqli_fetch_array($got2);
    $o3 = mysqli_fetch_array($got3);

    $po1 = $p1['pnos'];
    $po2 = $p2['pnos'];
    $po3 = $p3['pnos'];
    
    $totponos = $po1 + $po2 + $po3 ;
    
    $i1 = $o1['post'];
    $i2 = $o2['post'];
    $i3 = $o3['post'];

    $ipost = $i1 + $i2 + $i3;
    $ipgst = $ipost * 0.18;

//    browse($c1) ;
//    browse($i1) ;
//
//    browse($c2) ;
//    browse($i2) ;
//
//    browse($c3) ;
//    browse($i3) ;
//  to deduct qty and amount for credit note.

    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.`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 `credmain` a, `credtran` b 
                             WHERE a.`vouchdt` >= '$mfrmdate' AND a.`vouchdt` <= '$mtodate' 
                             and a.vouchno = b.vouchno group by b.hsncode, b.gstperc ")or die(mysqli_error($conn));

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

    $ctr = 0;
    $totqty = 0;
    $totamt = 0;
    $totdisc = 0;
    $totbill = 0;
    $ttotigst = 0;
    $ttotcgst = 0;
    $ttotsgst = 0;

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

        $ctr++;
        $hsn = $pmain['hsncode'];
        $item = $fitem['name'];
        $uqc = $fitem['unit'];
        $qty = $pmain['itemqty'];
        $gstperc = $pmain['gstperc'];

        // credit note part
        $ctotval = $ctaxval = $ctotcgst = $ctotsgst = $ctotigst = 0;

        $getcr = mysqli_query($conn, "SELECT * from `creddt` WHERE `hsncode`='$hsn' and gstperc = '$gstperc' ")or die(mysqli_error($conn));
        
        if (mysqli_num_rows($getcr) > 0) {
            $cmain = mysqli_fetch_array($getcr);
            $cqty = $cmain['itemqty'];
            $ctotval = $cmain['totval'];
            $ctaxval = $cmain['tamt'];
            $ctotcgst = $cmain['tcgstamt'];
            $ctotsgst = $cmain['tsgstamt'];
            $ctotigst = $cmain['tigstamt'];

            mysqli_query($conn, "update `creddt` set saleflag  = 1 WHERE `hsncode`='$hsn' and gstperc = '$gstperc' ")or die(mysqli_error($conn));
        }

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

    // negative insert for credit note

//    $getcred = mysqli_query($conn, "SELECT * from `creddt` order by hsncode, gstperc")or die(mysqli_error($conn));
//    print table($getcred);
//    die();
    
    $getcred = mysqli_query($conn, "SELECT * from `creddt` WHERE saleflag = 0 ")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 `product` WHERE `prodcode`='$cmain[itemcode]'")or die(mysqli_error($conn));
            $fitem = mysqli_fetch_array($getitem);

            $ctr++;
            $hsn = $cmain['hsncode'];
            $item = $fitem['name'];
            $uqc = $fitem['unit'];
            
            $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";

            $totqty = $totqty + $qty;
            $totinvval = $totinvval + $invval;
            $tottaxval = $tottaxval + $taxval;
            $ttotigst = $ttotigst + $totigst;
            $ttotcgst = $ttotcgst + $totcgst;
            $ttotsgst = $ttotsgst + $totsgst;
        }
    }

    $totpost = round($cpost + $ipost, 2) + round($ipgst, 2) + round($spgst, 2) + round($spgst, 2);
    
    $totpp = $totpnos + $ttoponos ;

    $contents.="9968" . ",";
    $contents.="COURIER" . ",";
    $contents.= "NOS" . ",";
    $contents.= $totpp . ",";
    $contents.= $totpost . ",";
    $contents.= "18" . ",";
    $contents.= round($cpost + $ipost, 2) . ",";
    $contents.= round($ipgst, 2) . ",";
    $contents.= round($spgst, 2) . ",";
    $contents.= round($spgst, 2) . ",";
    $contents.=" " . "\n";

    $totinvval = $totinvval + $totpost;
    $tottaxval = $tottaxval + $cpost + $ipost;
    $ttotigst = $ttotigst + $ipgst;

    $ttotcgst = $ttotcgst + $spgst;
    $ttotsgst = $ttotsgst + $spgst;


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


//    credit note
//    $getcred = mysqli_query($conn, "SELECT a.vouchno, a.vouchdt, a.`billamt`, b.`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` FROM `credmain` a, `credtran` b 
//                             WHERE a.`vouchdt` >= '$mfrmdate' AND a.`vouchdt` <= '$mtodate' 
//                             and a.vouchno = b.vouchno group by b.hsncode, b.gstperc ")or die(mysqli_error($conn));
//    
//    $contents.= "" . "\n";
//    $contents.= "" . "\n";
//    $contents.= ",Credit Note" . "\n";
//    $contents.= "" . "\n";
//    
//    $contents.="HSN, Description, UQC, GST %, Total Quantity, Tot Value, Taxable Value, Integrated Tax Amt, Central Tax Amt, State/UT Tax Amt, Cess Amt\n";
//
//    while ($cmain = mysqli_fetch_array($getcred)) {
//
//        $getitem = mysqli_query($conn, "SELECT `name` from `product` WHERE `prodcode`='$cmain[itemcode]'")or die(mysqli_error($conn));
//        $fitem = mysqli_fetch_array($getitem);
//
//        $ctr++;
//        $hsn = $cmain['hsncode'];
//        $item = $fitem['name'];
//        $uqc = " ";
//        $qty = $cmain['itemqty'];
//        $gstperc = $cmain['gstperc'];
//        $invval = $cmain['totval'];
//        $taxval = $cmain['tamt'];
//        $totcgst = $cmain['tcgstamt'];
//        $totsgst = $cmain['tsgstamt'];
//        
//        $totigst = $cmain['tigstamt'];
//        $cess = " ";
//
//
////        $contents.=$ctr . ",";
//        $contents.=$hsn . ",";
//        $contents.=$item . ",";
//        $contents.=$uqc . ",";
//        $contents.=$gstperc . ",";
//        $contents.=$qty . ",";
//        $contents.=$invval . ",";
//        $contents.=$taxval . ",";
//        $contents.=$totigst . ",";
//        $contents.=$totcgst . ",";
//        $contents.=$totsgst . ",";
////        $contents.=$totigst . ",";
//        $contents.=$cess . "\n";
//
//        $totinvval = $totinvval + $invval;
//        $tottaxval = $tottaxval + $taxval;
//        $ttotigst = $ttotigst + $totigst;
//        $ttotcgst = $ttotcgst + $totcgst;
//        $ttotsgst = $ttotsgst + $totsgst;
//    }


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