<?php

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

error_reporting(E_ERROR);
ini_set('display_errors', 1);

$formname = "Credit For Registered GST.";

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

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

$zdb = $_SESSION["zdbyear_" . $dbname];
mysqli_select_db($conn, $zdb);
$para = $_POST["para"];

if (isset($_POST["excel"]) || isset($_GET["excel"])) {

    include('includes/head_excel_rep.php');

    if ($para == "PC") {
        $maindb = "credmain";
        $trandb = "credtran";
        $othdb = "credoth";
    }

    if ($para == "PD") {
        $maindb = "debmain";
        $trandb = "debtran";
        $othdb = "deboth";
    }

    if ($para == "RC") {
        $maindb = "credrawmain";
        $trandb = "credrawtran";
        $othdb = "credrawoth";
    }

    if ($para == "RD") {
        $maindb = "debrawmain";
        $trandb = "debrawtran";
        $othdb = "debothraw";
    }
    // browse($zdb);
    // browse($maindb);
    // die();

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

    // print "SELECT a.irn, DATE_FORMAT(a.uploaddate, '%d-%m-%Y') as uploaddate, a.oms, a.invno, a.vouchdt, 
    //                         a.invdt, a.vouchno, a.party, a.billamt, a.taxableamt, 'C' as doctype, DATE_FORMAT(a.vouchdt, '%d-%m-%Y') as mydt, 
    //                         b.gstperc, SUM(b.subtot) as tamt FROM `$maindb` a left JOIN `$trandb` b ON a.vouchno = b.vouchno 
    //                         WHERE 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";
    // Creating the temporary table

    mysqli_query($conn, "DROP TEMPORARY TABLE IF EXISTS main1") or die(mysqli_error($conn));
    if ($para == "PC" || $para == "RC") {
        mysqli_query($conn, "CREATE TEMPORARY TABLE main1 
                            SELECT a.irn, DATE_FORMAT(a.uploaddate, '%d-%m-%Y') as uploaddate, a.oms, a.invno, a.vouchdt, 
                            a.`invdt`, a.`vouchno`, a.`party`, a.`broker`, a.`billamt`, 'C' as doctype, DATE_FORMAT(a.vouchdt, '%d-%m-%Y') as mydt, 
                            a.`packchgs`, a.`packgtperc`,a.canc_flag, b.`gstperc`, SUM(b.subtot) as tamt FROM `$maindb` a left JOIN `$trandb` b ON a.vouchno = b.vouchno 
                            WHERE a.vouchdt >= '$mfrmdate' AND a.vouchdt <= '$mtodate'  AND 
                            a.party IN (SELECT accode FROM accmast WHERE gstno != '') 
                            GROUP BY a.vouchno, b.gstperc ") or die(mysqli_error($conn));
                            // UNION ALL a.credtype = 'G' AND
                            // SELECT a.irn, DATE_FORMAT(a.uploaddate, '%d-%m-%Y') as uploaddate, a.oms, a.invno, a.vouchdt, 
                            // a.`invdt`, a.`vouchno`, a.`party`, a.`broker`, a.`billamt`, 'C' as doctype, DATE_FORMAT(a.vouchdt, '%d-%m-%Y') as mydt, 
                            // a.`packchgs`, a.`packgtperc`,a.canc_flag, (b.cgstrate + b.sgstrate + b.igstrate) AS gstperc,SUM(b.amount) as tamt FROM `$maindb` a left JOIN `$trandb` b ON a.vouchno = b.vouchno 
                            // WHERE a.vouchdt >= '$mfrmdate' AND a.vouchdt <= '$mtodate'  AND a.credtype = 'O' AND
                            // a.party IN (SELECT accode FROM accmast WHERE gstno != '')  GROUP BY a.vouchno,(b.cgstrate + b.sgstrate + b.igstrate)
    } else {
        mysqli_query($conn, "CREATE TEMPORARY TABLE main1 
                             SELECT '' AS irn, '' AS uploaddate,a.oms,a.invno,a.vouchdt,a.invdt,a.vouchno,a.party,a.broker,a.billamt,'D' AS doctype,DATE_FORMAT(a.vouchdt, '%d-%m-%Y') AS mydt,
                             a.packchgs,a.packgtperc,a.canc_flag,b.gstperc,SUM(b.subtot) AS tamt FROM `$maindb` a LEFT JOIN `$trandb` b ON a.vouchno = b.vouchno
                             WHERE a.vouchdt BETWEEN '$mfrmdate' AND '$mtodate'  AND a.credtype = 'G'  AND a.party IN (SELECT accode FROM accmast WHERE gstno != '')
                             GROUP BY a.vouchno,b.gstperc 
                             UNION ALL
                             SELECT '' AS irn, '' AS uploaddate,a.oms,a.invno,a.vouchdt,a.invdt,a.vouchno,a.party,a.broker,a.billamt,'D' AS doctype,DATE_FORMAT(a.vouchdt, '%d-%m-%Y') AS mydt,
                             a.packchgs,a.packgtperc,a.canc_flag,(b.cgstrate + b.sgstrate + b.igstrate) AS gstperc,SUM(b.amount) AS tamt FROM `$maindb` a 
                             LEFT JOIN `$othdb` b ON a.vouchno = b.vouchno WHERE a.vouchdt BETWEEN '$mfrmdate' AND '$mtodate'
                             AND a.credtype = 'O'  AND a.party IN (SELECT accode FROM accmast WHERE gstno != '') GROUP BY a.vouchno,(b.cgstrate + b.sgstrate + b.igstrate)") or die(mysqli_error($conn));
    }
//print "SELECT a.irn, DATE_FORMAT(a.uploaddate, '%d-%m-%Y') as uploaddate, a.oms, a.invno, a.vouchdt, 
//                            a.invdt, a.vouchno, a.party, a.broker, a.billamt, a.taxableamt, 'C' as doctype, DATE_FORMAT(a.vouchdt, '%d-%m-%Y') as mydt, 
//                            b.gstperc, SUM(b.subtot) as tamt FROM `$maindb` a left JOIN `$trandb` b ON a.vouchno = b.vouchno 
//                            WHERE 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 ";
//mysqli_query($conn, "CREATE TEMPORARY TABLE main1 SELECT a.irn, DATE_FORMAT(a.uploaddate, '%d-%m-%Y') as uploaddate, a.oms, a.invno, a.vouchdt, 
//                            a.invdt, a.vouchno, a.party, a.billamt, a.taxableamt, 'C' as doctype, DATE_FORMAT(a.vouchdt, '%d-%m-%Y') as mydt, 
//                            b.gstperc, SUM(b.subtot) as tamt FROM `$maindb` a left JOIN `$trandb` b ON a.vouchno = b.vouchno ") or die(mysqli_error($conn));

    $getm1 = mysqli_query($conn, "SELECT * FROM `main1` where packchgs > 0  group by vouchno order by vouchno") or die(mysqli_error($conn));

    if (mysqli_num_rows($getm1) > 0) {
        while ($p1 = mysqli_fetch_array($getm1)) {
            $vouchno = $p1['vouchno'];

            $getin = mysqli_query($conn, "SELECT * FROM `main1` where packchgs > 0 and packgtperc > 0 and vouchno = '$vouchno' ") or die(mysqli_error($conn));
            if (mysqli_num_rows($getin) <= 0) {

//                browse($vouchno) ;

                $vouchdt = $p1['vouchdt'];
                $invno = $p1['invno'];
                $billamt = $p1['billamt'];
                $post = $p1['packchgs'];
                $gstperc = $pl['packgtperc'];
                $party = $p1['party'];
                $oms = $p1['oms'];

//                $tamt = $p1['tamt'];

                mysqli_query($conn, "insert into main1 (gstperc, vouchno, vouchdt, party, invno, billamt, packchgs, tamt, oms ) values 
                                            ('$gstperc','$vouchno', '$vouchdt', '$party', '$invno', '$billamt', '$post', 0, '$oms' )") or die(mysqli_error($conn));
            }
        }
    }
    mysqli_query($conn, "update main1 set tamt = (tamt + packchgs) where gstperc > 0") or die(mysqli_error($conn));

// Final SELECT query
    $getpmain = mysqli_query($conn, "SELECT a.*, DATE_FORMAT(a.`vouchdt`, '%d-%m-%Y') as mydt, b.gstno, b.state, b.statecode, b.name as rcvrname 
                                        FROM `main1` a LEFT JOIN accmast b ON a.party = b.accode ORDER BY a.vouchno") or die(mysqli_error($conn));

//$getpmain = mysqli_query($conn, "SELECT * from main1 ") or die(mysqli_error($conn));
// print table($getpmain);
// die();
    // mysqli_query($conn, "drop temporary table if exists main1") or die(mysqli_error($conn));
    // mysqli_query($conn, "create temporary table main1
    //                             SELECT a.irn, date_format(a.uploaddate,'%d-%m-%Y') as uploaddate,a.oms,a.invno, a.invdt, 
    //                                         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 tamt
    //                                          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));
//    $getm1 = mysqli_query($conn, "SELECT * FROM `main1` where postamt > 0 and gstperc != 18 group by vouchno order by vouchno") or die(mysqli_error($conn));
//
//    if (mysqli_num_rows($getm1) > 0) {
//        while ($p1 = mysqli_fetch_array($getm1)) {
//            $vouchno = $p1['vouchno'];
//
//            $getin = mysqli_query($conn, "SELECT * FROM `main1` where postamt > 0 and gstperc = 18 and vouchno = '$vouchno' ") or die(mysqli_error($conn));
//            if (mysqli_num_rows($getin) <= 0) {
//
//                $vouchdt = $p1['vouchdt'];
//                $invno = $p1['invno'];
//                $billamt = $p1['billamt'];
//                $post = $p1['postamt'];
//                $gstperc = 18;
//                $party = $p1['party'];
//
////                $tamt = $p1['tamt'];
//
//                mysqli_query($conn, "insert into main1 (gstperc, vouchno, vouchdt, party, invno, billamt, postamt, tamt ) values 
//                                            ('$gstperc','$vouchno', '$vouchdt', '$party', '$invno', '$billamt', '$post', 0 )") or die(mysqli_error($conn));
//            }
//        }
//    }
//    mysqli_query($conn, "update main1 set tamt = (tamt + postamt) where gstperc = 18") or die(mysqli_error($conn));
//
//    mysqli_query($conn, "update main1 set postamt = 0 where gstperc != 18") or die(mysqli_error($conn));
    // $getpmain = mysqli_query($conn, "SELECT a.*, DATE_FORMAT(a.`vouchdt`, '%d-%m-%Y') as mydt, b.gstno, b.state, b.statecode, name as rcvrname
    //                                         FROM `main1` a, accmast b where a.party = b.accode  order by a.vouchno ") or die(mysqli_error($conn));
//    print table($getpmain);
//    die();
////////////////////////////
    $contents .= "GST No., Receiver Name, Note/Ref Vouch No., Note/Ref Vouch Date,Doc Type, Place of Supply, Note/Ref Vouch Value, Applicable % of Tax Rate, Rate, Taxable Value, Integrated Tax, Central Tax,State/UT Tax,Cess Amount,IRN, IRN Date\n";

    $ctr = 0;
    $totamt = 0;
    $totdisc = 0;
    $totbill = 0;
    if ($para == "PC" || $para == "PD") {
        $contents .= "PURCHASE RETURN / GOODS RETURN" . "\n";
    } else {

        $contents .= "SALE RETURN / GOODS RETURN" . "\n";
    }

    while ($pmain = mysqli_fetch_array($getpmain)) {
        // $qrecpgst = mysqli_query($conn, "SELECT `gstno`, `state`, `statecode`,name 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++;
        $oms = $pmain['oms'];

        $gstno = $pmain['gstno'];
        $name = $pmain['rcvrname'];
        $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 = "Goods Return Other";
        }
        $state = $pmain['state'];
        $stcode = $pmain['statecode'];
        $invval = $pmain['billamt'];
        $taxval = $pmain['tamt'];
        $gstamt = ($taxval * $gstrate) / 100;

        $cess = '';
        $pregst = 'N';
        if ($oms == '1') {
            $igstamt = $gstamt;
            $cgstamt = 0;
        } else {
            $igstamt = 0;
            $cgstamt = round($gstamt / 2, 2);
        }

        if ($pmain['canc_flag'] != 'C') {

//        $contents.= $ctr . ",";
            $contents .= $gstno . ",";
            $contents .= $name . ",";
//        $contents.= $invno . ",";
//        $contents.= $invdt . ",";
            $contents .= $vouchno . ",";
            $contents .= $vouchdt . ",";
            $contents .= $credtype . ",";
//        $contents.= $credtype . ",";
            $contents .= $stcode . '-' . $state . ",";
            $contents .= $invval . ",";
            $contents .= "" . ",";
            $contents .= $gstrate . ",";
            $contents .= $taxval . ",";
            $contents .= "$igstamt" . ",";
            $contents .= "$cgstamt" . ",";
            $contents .= "$cgstamt" . ",";
            $contents .= "" . ",";
            $contents .= "$pmain[irn]" . ",";
            $contents .= "$pmain[uploaddate]" . "\n";
//        $contents.= $pregst . "\n";

            $totinvval = $totinvval + $invval;
            $tottaxval = $tottaxval + $taxval;
        } else {
            $contents .= $gstno . ",";
            $contents .= $name . ",";
//        $contents.= $invno . ",";
//        $contents.= $invdt . ",";
            $contents .= $vouchno . ",";
            $contents .= $vouchdt . ",";
            $contents .= "********* cancellled bill *********" . "\n";
        }
    }

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