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

$admin = $_SESSION["admin_" . $dbname];
$zdb = $_SESSION["zdbyear_" . $dbname];
$myyear = $_SESSION[$myyear];
mysqli_select_db($conn, $zdb);

$maindb = 'debmain';
$trandb = 'debtran';
$zroot = $_SESSION["zdbroot"];

$formname = 'Purchase Return Register';
$zroot = $_SESSION["zdir"];
$file = 'purretreg_fr.php';

if (isset($_POST["view"])) {
    $party = $_POST["party"];
    $godown = $_POST["godown"];
    $staff = $_POST["staff"];

    $ptype = $_POST["ptype"];
    $dtype = $_POST["dtype"];
    $gsttype = $_POST["gsttype"];

    if (!empty($party)) {
        $partycond = " AND `party` = '$party' ";
    }
    if (!empty($godown)) {
        $godowncond = " AND `godown` = '$godown' ";
    }
    if (!empty($staff)) {
        $staffcond = " AND `staff` = '$staff '";
    }
   

    if (!empty($gsttype)) {
        if ($gsttype == 1) {
            $gstcond = "AND `oms` = '1'";
        } else {
            $gstcond = "AND `oms` = ''";
        }
    }

    if (empty($_POST["frmdt"])) {
        browse("From Date Cannot Be Empty");
        header("refresh:1; saleitemgrp.php");
    }
    if (empty($_POST["todt"])) {
        browse("To Date Cannot Be Empty");
        header("refresh:1; saleitemgrp.php");
    }

    $mfrmdate = trim($_POST["frmdt"]);
    $mtodate = trim($_POST["todt"]);

    $mfdt = date("d-m-Y", strtotime($mfrmdate));
    $mtdt = date("d-m-Y", strtotime($mtodate));


    $mainqry = mysqli_query($conn, "SELECT *, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt FROM `$maindb` WHERE `vouchdt` >= '$mfrmdate' 
                            AND `vouchdt` <= '$mtodate' $partycond $godowncond $staffcond $ptypecond $gstcond")or die(mysqli_error($conn));
    $getno = mysqli_num_rows($mainqry);

    include("includes/linkshead.php");
    ?>
    <style type="text/css">
        #tabital{
            font-style: italic;
        }
        #font1{
            font-style: italic;
        }
        #boldfont{
            font-weight: bold; 
        }
        .colr{
            background: #26A69A;
            font-size: 17px;
        }
        .dth{
            background:#BABFC2;
        }
    </style>
    </head>
    <body>
        <div class="container">
            <?php include("includes/head_rep.php"); ?>
                <div class="row">
                    <div class="col-lg-12 pad">
                        <table style="background: white;width: 100%" class="table table-condensed  table-bordered">
                            <thead class="mySearchTr">
                                <tr>
                                    <th style="width:4%;">Sr</th>
                                    <th style="width:5%;">Voucher</th>
                                    <th style="width:7%;">Date</th>
                                    <th style="width:6%;">Godown</th>
                                    <th style="width:20%;">Party Name</th>
                                    <th style="width:6%;" class="text-right">Amount</th>
                                    <th style="width:6%;" class="text-right">Disc Amt.</th>
                                    <th style="width:7%;" class="text-right">Taxable</th>
                                    <th style="width:6%;"class="text-right">GST 3%</th>
                                    <th style="width:6%;"class="text-right">GST 5%</th>
                                    <th style="width:6%;" class="text-right">GST 12%</th>
                                    <th style="width:6%;" class="text-right">GST 18%</th>
                                    <th style="width:6%;" class="text-right">GST 28%</th>
                                    <th style="width:9%;" class="text-right">Bill Amount</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php
                                $ctr = 0;
                                $totamt = 0;
                                $totdiscamt = 0;
                                $tottaxable = 0;
                                $totgst3 = 0;
                                $totgst5 = 0;
                                $totgst12 = 0;
                                $totgst18 = 0;
                                $totgst28 = 0;
                                $totpackamt = $totstamt = $totothamt = $totbillamt = 0;

                                while ($pmain = mysqli_fetch_array($mainqry)) {

                                    $gparty = mysqli_query($conn, "SELECT `accode`,`name` FROM `accmast` where `accode` = '$pmain[party]' AND grpcode='009'") or die(mysqli_error($conn));
                                    $fparty = mysqli_fetch_array($gparty);

                                    $ggodw = mysqli_query($conn, "SELECT `code`,`gcode` FROM `godown` where `code` = '$pmain[godown]'") or die(mysqli_error($conn));
                                    $sgodw = mysqli_fetch_array($ggodw);

                                    if ($gsttype == '2') {
                                        $gst3 = $pmain[totgst3];
                                        $gst5 = $pmain[totgst5];
                                        $gst12 = $pmain[totgst12];
                                        $gst18 = $pmain[totgst18];
                                        $gst28 = $pmain[totgst28];
                                        $billamt = $pmain[billamt];
                                        $subtot = $pmain[grossamt];
                                        $totdisc = $pmain[discamt];
                                        $taxable = $pmain[taxableamt];
                                    } else {
                                        $gst3 = $pmain[totgst3];
                                        $gst5 = $pmain[totgst5];
                                        $gst12 = $pmain[totgst12];
                                        $gst18 = $pmain[totgst18];
                                        $gst28 = $pmain[totgst28];
                                        $billamt = $pmain[billamt];
                                        $subtot = $pmain[grossamt];
                                        $totdisc = $pmain[discamt];
                                        $taxable = $pmain[taxableamt];
                                    }

                                    $ctr++;
//                                    Echo"<tr data-href='debnote_mi.php?mode=edit&vouchno=$pmain[voucher]&type=R' class='clickable-row2' [target]='_blank'>";
                                    Echo"<tr>";
                                    Echo"<td>$ctr</td>";
                                    Echo"<td>$pmain[voucher]</td>";
                                    Echo"<td>$pmain[mydt]</td>";
                                    Echo"<td>$sgodw[gcode]</td>";
                                    Echo"<td>$fparty[name]</td>";
                                    Echo"<td class='text-right'>$subtot</td>";
                                    Echo"<td class='text-right'>$totdisc</td>";
                                    Echo"<td class='text-right'>$taxable</td>";
                                    Echo"<td class='text-right'>$gst3</td>";
                                    Echo"<td class='text-right'>$gst5</td>";
                                    Echo"<td class='text-right'>$gst12</td>";
                                    Echo"<td class='text-right'>$gst18</td>";
                                    Echo"<td class='text-right'>$gst28</td>";
                                    Echo"<td class='text-right'>$billamt</td>";
                                    Echo"</tr>";


                                    $grsubtot = $grsubtot + $subtot;
                                    $grtotdis = $grtotdis + $totdisc;
                                    $grtottaxable = $grtottaxable + $taxable;
                                    $grtotgst3 = $grtotgst3 + $gst3;
                                    $grtotgst5 = $grtotgst5 + $gst5;
                                    $grtotgst12 = $grtotgst12 + $gst12;
                                    $grtotgst18 = $grtotgst18 + $gst18;
                                    $grtotgst28 = $grtotgst28 + $gst28;
                                    $grtotbillamt = $grtotbillamt + $billamt;

                                    $totpackamt = $totpackamt + $pmain[packamt];
                                    $totstamt = $totstamt + $pmain[stamt];
                                    $totothamt = $totothamt + $pmain[otheramt];
                                    $totbillamt = $totbillamt + $pmain[billamt];

                                    if ($_POST["dtype"] == 2) {

                                        Echo "<tr style='height:10px;'>";
                                        Echo "<td colspan='25'>";
                                        Echo "<table style='width:100%;' id='tabital' class='table table-condensed table-bordered'>";
                                        Echo "<th style='width:5%;' class='dth'>Sr</th>";
                                        Echo "<th style='width:15%;' class='dth'>Item Name</th>";
                                        Echo "<th style='width:10%;' class='dth'>HSN Code</th>";
                                        Echo "<th style='width:5%;' class='dth'>Unit</th>";
                                        Echo "<th style='width:5%;' class='text-right dth'>Qty.</th>";
                                        Echo "<th style='width:5%;' class='text-right dth'>Rate</th>";
                                        Echo "<th style='width:5%;' class='text-right dth'>Amount</th>";
                                        Echo "<th style='width:5%;' class='text-right dth'>Disc%</th>";
                                        Echo "<th style='width:5%;' class='text-right dth'>Disc. Amt.</th>";
                                        Echo "<th style='width:5%;' class='text-right dth'>Taxable</th>";
                                        Echo "<th style='width:5%;' class='text-right dth'>CGST Rate</th>";
                                        Echo "<th style='width:5%;' class='text-right dth'>CGST Amt.</th>";
                                        Echo "<th style='width:5%;' class='text-right dth'>SGST Rate</th>";
                                        Echo "<th style='width:5%;' class='text-right dth'>SGST Amt.</th>";
                                        Echo "<th style='width:5%;' class='text-right dth'>IGST Rate</th>";
                                        Echo "<th style='width:5%;' class='text-right dth'>IGST Amt.</th>";
                                        Echo "<th style='width:5%;' class='text-right dth'>Total</th>";
                                        Echo "</tr>";


                                        $gettran = mysqli_query($conn, "SELECT * FROM `$trandb` WHERE `vouchno`='$pmain[vouchno]'")or die(mysqli_error($conn));
                                        $ctr2 = 0;
                                        while ($tran = mysqli_fetch_array($gettran)) {

                                            $ctr2++;
                                            Echo "<tr>";
                                            Echo "<td>$ctr2</td>";
                                            Echo "<td>$tran[itemname]</td>";
                                            Echo "<td>$tran[hsncode]</td>";
                                            Echo "<td>$tran[unit]</td>";
                                            Echo "<td class='text-right'>$tran[qty]</td>";
                                            Echo "<td class='text-right'>$tran[rate]</td>";
                                            Echo "<td class='text-right'>$tran[amount]</td>";
                                            Echo "<td class='text-right'>$tran[discperc]</td>";
                                            Echo "<td class='text-right'>$tran[discamt]</td>";
                                            Echo "<td class='text-right'>$tran[subtot]</td>";
                                            Echo "<td class='text-right'>$tran[cgstrate]</td>";
                                            Echo "<td class='text-right'>$tran[cgstamt]</td>";
                                            Echo "<td class='text-right'>$tran[sgstrate]</td>";
                                            Echo "<td class='text-right'>$tran[sgstamt]</td>";
                                            Echo "<td class='text-right'>$tran[igstrate]</td>";
                                            Echo "<td class='text-right'>$tran[igstamt]</td>";
                                            Echo "<td class='text-right'>$tran[total]</td>";
                                            Echo "</tr>";

                                            $totqty = $totqty + $tran[qty];
                                            $totamt = $totamt + $tran[amount];
                                            $totdisc = $totdisc + $tran[discamt];
                                            $totgross = $totgross + $tran[subtot];
                                            $totcgst = $totcgst + $tran[cgstamt];
                                            $totsgst = $totsgst + $tran[sgstamt];
                                            $totigst = $totigst + $tran[igstamt];
                                            $totbill = $totbill + $tran[total];
                                        }

                                        Echo "<tr>";
                                        Echo "<td class='dth'></td>";
                                        Echo "<td class='dth'></td>";
                                        Echo "<td class='dth'></td>";
                                        Echo "<td class='text-right dth' >Total</td>";
                                        Echo "<td class='text-right dth' >$totqty</td>";
                                        Echo "<td class='text-right dth' ></td>";
                                        Echo "<td class='text-right dth' >$totamt</td>";
                                        Echo "<td class='text-right dth' ></td>";
                                        Echo "<td class='text-right dth' >$totdisc</td>";
                                        Echo "<td class='text-right dth' >$totgross</td>";
                                        Echo "<td class='text-right dth' ></td>";
                                        Echo "<td class='text-right dth' >$totcgst</td>";
                                        Echo "<td class='text-right dth' ></td>";
                                        Echo "<td class='text-right dth' >$totsgst</td>";
                                        Echo "<td class='text-right dth' ></td>";
                                        Echo "<td class='text-right dth' >$totigst</td>";
                                        Echo "<td class='text-right dth' >$totbill</td>";
                                        Echo "</tr>";

                                        Echo "</table>";
                                        Echo "</td>";
                                    }
                                }

                                Echo "<tr>";
                                Echo "<td class='colr'></td>";
                                Echo "<td class='colr'></td>";
                                Echo "<td class='colr'></td>";
                                Echo "<td class='colr'></td>";
                                Echo "<td class='text-right colr'><b>Total</b></td>";
                                Echo "<td class='numeric text-right colr'><b>$grsubtot</b></td>";
                                Echo "<td class='numeric text-right colr'><b>$grtotdis</b></td>";
                                Echo "<td class='numeric text-right colr'><b>$grtottaxable</b></td>";
                                Echo "<td class='numeric text-right colr'><b>$grtotgst3</b></td>";
                                Echo "<td class='numeric text-right colr'><b>$grtotgst5</b></td>";
                                Echo "<td class='numeric text-right colr'><b>$grtotgst12</b></td>";
                                Echo "<td class='numeric text-right colr'><b>$grtotgst18</b></td>";
                                Echo "<td class='numeric text-right colr'><b>$grtotgst28</b></td>";
                                Echo "<td class='numeric text-right colr'><b>$grtotbillamt</b></td>";
                                Echo "</tr>";
                                ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
            <?php include("includes/linksfoot.php");
            ?>
    </body>
    </html>
    <?php
}

if (isset($_POST["excel"])) {
    $party = $_POST["party"];
    $godown = $_POST["godown"];
    $staff = $_POST["staff"];
    $ptype = $_POST["ptype"];
    $dtype = $_POST["dtype"];
    $gsttype = $_POST["gsttype"];

    if (!empty($party)) {
        $partycond = " AND `party` = '$party' ";
    }
    if (!empty($godown)) {
        $godowncond = " AND `godown` = '$godown' ";
    }
    if (!empty($staff)) {
        $staffcond = " AND `staff` = '$staff '";
    }
    if (!empty($ptype)) {
        $ptypecond = " AND `paidby`= '$ptype'";
    }

    if (!empty($gsttype)) {
        if ($gsttype == 1) {
            $gstcond = "AND `oms` = '1'";
        } else {
            $gstcond = "AND `oms` = ''";
        }
    }

    if (empty($_POST["frmdt"])) {
        browse("From Date Cannot Be Empty");
        header("refresh:1; saleitemgrp.php");
    }
    if (empty($_POST["todt"])) {
        browse("To Date Cannot Be Empty");
        header("refresh:1; saleitemgrp.php");
    }

    $mfrmdate = trim($_POST["frmdt"]);
    $mtodate = trim($_POST["todt"]);

    $mfdt = date("d-m-Y", strtotime($mfrmdate));
    $mtdt = date("d-m-Y", strtotime($mtodate));


    $mainqry = mysqli_query($conn, "SELECT *, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt FROM `$maindb` WHERE `vouchdt` >= '$mfrmdate' 
                            AND `vouchdt` <= '$mtodate' $partycond $godowncond $staffcond $ptypecond $gstcond")or die(mysqli_error($conn));


    $contents.="Sr, Voucher, Date, Godown, Party Name, Pay type, Amount, Disc Amt., Taxable, GST 3%, GST 5%, GST 12%, GST 18%, GST 28%, Bill Amount,\n";


    $ctr = 0;
    $totamt = 0;
    $totdiscamt = 0;
    $tottaxable = 0;
    $totgst3 = 0;
    $totgst5 = 0;
    $totgst12 = 0;
    $totgst18 = 0;
    $totgst28 = 0;
    $totpackamt = $totstamt = $totothamt = $totbillamt = 0;

    while ($pmain = mysqli_fetch_array($mainqry)) {
        if ($pmain[paidby] == "C") {
            $ptype = "CASH";
        } else {
            $ptype = "CREDIT";
        }

        $gparty = mysqli_query($conn, "SELECT `accode`,`name` FROM `accmast` where `accode` = '$pmain[party]' AND grpcode='009'") or die(mysqli_error($conn));
        $fparty = mysqli_fetch_array($gparty);

        $ggodw = mysqli_query($conn, "SELECT `code`,`gcode` FROM `godown` where `code` = '$pmain[godown]'") or die(mysqli_error($conn));
        $sgodw = mysqli_fetch_array($ggodw);

        if ($gsttype == '2') {
            $gst3 = $pmain[totgst3];
            $gst5 = $pmain[totgst5];
            $gst12 = $pmain[totgst12];
            $gst18 = $pmain[totgst18];
            $gst28 = $pmain[totgst28];
            $billamt = $pmain[billamt];
            $subtot = $pmain[grossamt];
            $totdisc = $pmain[discamt];
            $taxable = $pmain[taxableamt];
        } else {
            $gst3 = $pmain[totgst3];
            $gst5 = $pmain[totgst5];
            $gst12 = $pmain[totgst12];
            $gst18 = $pmain[totgst18];
            $gst28 = $pmain[totgst28];
            $billamt = $pmain[billamt];
            $subtot = $pmain[grossamt];
            $totdisc = $pmain[discamt];
            $taxable = $pmain[taxableamt];
        }

        $ctr++;

        $contents.=$ctr . ",";
        $contents.=$pmain[voucher] . ",";
        $contents.=$pmain[mydt] . ",";
        $contents.=$sgodw[gcode] . ",";
        $contents.=$fparty[name] . ",";
        $contents.=$ptype . ",";
        $contents.=$subtot . ",";
        $contents.=$totdisc . ",";
        $contents.=$taxable . ",";
        $contents.=$gst3 . ",";
        $contents.=$gst5 . ",";
        $contents.=$gst12 . ",";
        $contents.=$gst18 . ",";
        $contents.=$gst28 . ",";
        $contents.=$billamt . "\n";



        $grsubtot = $grsubtot + $subtot;
        $grtotdis = $grtotdis + $totdisc;
        $grtottaxable = $grtottaxable + $taxable;
        $grtotgst3 = $grtotgst3 + $gst3;
        $grtotgst5 = $grtotgst5 + $gst5;
        $grtotgst12 = $grtotgst12 + $gst12;
        $grtotgst18 = $grtotgst18 + $gst18;
        $grtotgst28 = $grtotgst28 + $gst28;
        $grtotbillamt = $grtotbillamt + $billamt;

        $totpackamt = $totpackamt + $pmain[packamt];
        $totstamt = $totstamt + $pmain[stamt];
        $totothamt = $totothamt + $pmain[otheramt];
        $totbillamt = $totbillamt + $pmain[billamt];

        if ($_POST["dtype"] == 2) {

            $contents.="Sr, Item Name, HSN Code, Unit, Qty., Rate, Amount, Disc%, Disc. Amt., Taxable, CGST Rate, CGST Amt., SGST Rate, SGST Amt., IGST Rate, IGST Amt., Total,\n";


            $gettran = mysqli_query($conn, "SELECT * FROM `$trandb` WHERE `vouchno`='$pmain[vouchno]'")or die(mysqli_error($conn));
            $ctr2 = 0;
            while ($tran = mysqli_fetch_array($gettran)) {

                $ctr2++;
                $contents.=$ctr2 . ",";
                $contents.=$tran[itemname] . ",";
                $contents.=$tran[hsncode] . ",";
                $contents.=$tran[unit] . ",";
                $contents.=$tran[qty] . ",";
                $contents.=$tran[rate] . ",";
                $contents.=$tran[amount] . ",";
                $contents.=$tran[discperc] . ",";
                $contents.=$tran[discamt] . ",";
                $contents.=$tran[subtot] . ",";
                $contents.=$tran[cgstrate] . ",";
                $contents.=$tran[cgstamt] . ",";
                $contents.=$tran[sgstrate] . ",";
                $contents.=$tran[sgstamt] . ",";
                $contents.=$tran[igstrate] . ",";
                $contents.=$tran[igstamt] . ",";
                $contents.=$tran[total] . "\n";

                $totqty = $totqty + $tran[qty];
                $totamt = $totamt + $tran[amount];
                $totdisc = $totdisc + $tran[discamt];
                $totgross = $totgross + $tran[subtot];
                $totcgst = $totcgst + $tran[cgstamt];
                $totsgst = $totsgst + $tran[sgstamt];
                $totigst = $totigst + $tran[igstamt];
                $totbill = $totbill + $tran[total];
            }

           
           $contents.=" " . ",";
           $contents.=" " . ",";
           $contents.=" " . ",";
           $contents.="Total". ",";
           $contents.=$totqty . ",";
           $contents.="" . ",";
           $contents.=$totamt . ",";
           $contents.=" " . ",";
           $contents.=$totdisc . ",";
           $contents.=$totgross . ",";
           $contents.=" " . ",";
           $contents.=$totcgst . ",";
           $contents.=" " . ",";
           $contents.=$totsgst . ",";
           $contents.=" " . ",";
           $contents.=$totigst . ",";
           $contents.=$totbill . "\n";
            
        }
    }

    
    $contents.=" " . ",";
    $contents.=" " . ",";
    $contents.=" " . ",";
    $contents.=" " . ",";
    $contents.=" " . ",";
    $contents.="Total"  . ",";
    $contents.=$grsubtot . ",";
    $contents.=$grtotdis . ",";
    $contents.=$grtottaxable . ",";
    $contents.=$grtotgst3 . ",";
    $contents.=$grtotgst5 . ",";
    $contents.=$grtotgst12 . ",";
    $contents.=$grtotgst18 . ",";
    $contents.=$grtotgst28 . ",";
    $contents.=$grtotbillamt . "\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;
    }
}
?>