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

$maindb = "credmain";
$trandb = "credtran";
$othdb = "credoth";
$formname = "Credit / Debit Note";

$maindb1 = "debmain";
$trandb1 = "debtran";
$othdb1 = "deboth";

//$formname = "GSTR1_4";
$file = "gstr1_cdnr.php";

$compcd = $_SESSION["comp_" . $dbname];
$zroot = $_SESSION["zdir"];
$admin = $_SESSION["admin_" . $dbname];
$myyear = $_SESSION[$myyear];

$zdb = $_SESSION["zdbyear_" . $dbname];
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");
    }

//    $getpmain = mysqli_query($conn, "(SELECT a.invno,a.invdate, a.vouchno,a.vouchdt, a.party, a.billamt, a.taxableamt, 'C' as doctype, DATE_FORMAT(a.vouchdt, '%d-%m-%Y') as mydt,
//                              b.gstperc, sum(b.subtot) as taxabletran FROM `$maindb` a, `$trandb` b WHERE a.vouchno = b.vouchno AND a.vouchdt >= '$mfrmdate' AND a.vouchdt <= '$mtodate' 
//                              AND a.party in (select accode from accmast where gstno != '') group by a.vouchno, b.gstperc order by a.vouchdt, a.vouchno)
//                              union ALL
//                              (SELECT a.invno, a.invdate, a.vouchno, a.vouchdt, a.party,a.billamt, a.taxableamt, 'D' as doctype, DATE_FORMAT(a.vouchdt, '%d-%m-%Y') as mydt, 
//                              b.gstperc, sum(b.subtot) as taxabletran FROM `$maindb1` a, `$trandb1` b WHERE a.vouchno = b.vouchno AND a.vouchdt >= '$mfrmdate' AND a.vouchdt <= '$mtodate' 
//                              AND a.party in (select accode from accmast where gstno != '') group by a.vouchno, b.gstperc  order by a.vouchdt, a.vouchno)") or die(mysqli_error($conn));

    $getpmain = mysqli_query($conn, "(SELECT a.invno,a.invdate, a.vouchno,a.vouchdt, a.party, a.billamt, a.taxableamt, 'C' as doctype, DATE_FORMAT(a.vouchdt, '%d-%m-%Y') as mydt,
                              b.gstperc, sum(b.subtot) as taxabletran FROM `$maindb` a, `$trandb` b WHERE a.vouchno = b.vouchno AND a.vouchdt >= '$mfrmdate' AND a.vouchdt <= '$mtodate' 
                              AND a.party in (select accode from accmast where gstno != '') 
                              group by a.vouchno, b.gstperc order by a.vouchdt, a.vouchno)") or die(mysqli_error($conn));
    
    $getpmain2 = mysqli_query($conn, "SELECT a.invno,a.invdate, a.vouchno, a.vouchdt, a.party, a.billamt, a.taxableamt, 'C' as doctype, DATE_FORMAT(a.vouchdt, '%d-%m-%Y') as mydt,
                              a.`gsttype`, b.amount as othamt FROM `$maindb` a, `$othdb` b WHERE a.vouchno = b.vouchno AND a.vouchdt >= '$mfrmdate' AND a.vouchdt <= '$mtodate' 
                              AND a.party in (select accode from accmast where gstno != '') 
                              group by a.vouchno order by a.vouchdt, a.vouchno") or die(mysqli_error($conn));

    $contents.="GST No., Receiver Name, Inv./Advance Receipt No., Inv./Advance Receipt Date., Note/Ref Vouch No., Note/Ref Vouch Date,Doc Type, Place of Supply, Note/Ref Vouch Value, Applicable % of Tax Rate, Rate, Taxable Value, Cess Amt,Pre GST\n";

    $ctr = 0;
    $totamt = 0;
    $totdisc = 0;
    $totbill = 0;
    $contents.= "GOODS RETURN" . "\n";

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

        $rcvrname = mysqli_query($conn, "SELECT `accode`, `name` from `accmast` where `accode`= '$pmain[party]' ")or die(mysqli_error($conn));
        $rcvr = mysqli_fetch_array($rcvrname);

        if ($pmain['pregst'] == '') {
            $pregst = "N";
        } else {
            $pregst = "Y";
        }


        $ctr++;
        $gstno = $frecpgst['gstno'];
        $name = $rcvr['name'];
        $invno = $pmain['invno'];
        $invdt = $pmain['invdate'];
        $vouchno = $pmain['vouchno'];
        $vouchdt = $pmain['mydt'];
        $doctype = $pmain['doctype'];
        $gstrate = $pmain['gstperc'];
        if ($pmain['credtype'] == 'G') {
            $credtype = "Goods Return";
        } else {
            $credtype = "Other / Corrrection in Invoice";
        }
        $state = $frecpgst['state'];
        $stcode = $frecpgst['statecode'];
        $invval = $pmain['billamt'];
        $taxval = $pmain['taxabletran'];
        $cess = '';
        $pregst = 'N';


//        $contents.= $ctr . ",";
        $contents.= $gstno . ",";
        $contents.= $name . ",";
        $contents.= $invno . ",";
        $contents.= $invdt . ",";
        $contents.= $vouchno . ",";
        $contents.= $vouchdt . ",";
        $contents.= $doctype . ",";
//        $contents.= $credtype . ",";
        $contents.= $stcode . '-' . $state . ",";
        $contents.= $invval . ",";
        $contents.= "" . ",";
        $contents.= $gstrate . ",";
        $contents.= $taxval . ",";
        $contents.= $cess . ",";
        $contents.= $pregst . "\n";

        $totinvval = $totinvval + $invval;
        $tottaxval = $tottaxval + $taxval;
    }

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


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

        $rcvrname = mysqli_query($conn, "SELECT `accode`, `name` from `accmast` where `accode`= '$pmain2[party]' ")or die(mysqli_error($conn));
        $rcvr = mysqli_fetch_array($rcvrname);

        if ($pmain['pregst'] == '') {
            $pregst = "N";
        } else {
            $pregst = "Y";
        }


        $ctr++;
        $gstno = $frecpgst['gstno'];
        $name = $rcvr['name'];
        $invno = $pmain2['invno'];
        $invdt = $pmain2['invdate'];
        $vouchno = $pmain2['vouchno'];
        $vouchdt = $pmain2['mydt'];
        $doctype = $pmain2['doctype'];
        $gstrate = $pmain2['gsttype'];
        if ($pmain2['credtype'] == 'G') {
            $credtype = "Goods Return";
        } else {
            $credtype = "Other / Corrrection in Invoice";
        }
        $state = $frecpgst['state'];
        $stcode = $frecpgst['statecode'];
        $invval = $pmain2['billamt'];
        $taxval = $pmain2['taxableamt'];
        $cess = '';
        $pregst = 'N';


//        $contents.= $ctr . ",";
        $contents.= $gstno . ",";
        $contents.= $name . ",";
        $contents.= $invno . ",";
        $contents.= $invdt . ",";
        $contents.= $vouchno . ",";
        $contents.= $vouchdt . ",";
        $contents.= $doctype . ",";
//        $contents.= $credtype . ",";
        $contents.= $stcode . '-' . $state . ",";
        $contents.= $invval . ",";
        $contents.= "" . ",";
        $contents.= $gstrate . ",";
        $contents.= $taxval . ",";
        $contents.= $cess . ",";
        $contents.= $pregst . "\n";

        $totinvval = $totinvval + $invval;
        $tottaxval = $tottaxval + $taxval;
    }


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