<?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 = 'pordmain';
$trandb = 'pordtran';
$zroot = $_SESSION["zdbroot"];

$formname = 'Purchase Order Register';
$file = 'purordreg_fr.php';


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


    $supplier = $_POST["supplier"];
    $p = $_GET["p"];
    $godown = $_POST["godown"];
    $ptype = $_POST["ptype"];
    $pchtype = $_POST["pchtype"];
    $dtype = $_POST["dtype"];
    $gsttype = $_POST["gsttype"];
    $transport = $_POST["transport"];

    if (!empty($transport)) {
        $trancond = " AND `transport` = '$transport' ";
    }

    if (!empty($supplier)) {
        $suppcond = " AND `party` = '$supplier' ";
    }
    if (!empty($p)) {
        $pcond = " AND `party` = '$p' ";
    }
    if (!empty($godown)) {
        $godowncond = " AND `godown` = '$godown' ";
    }
    if (!empty($ptype)) {
        $ptypecond = " AND `paidby`= '$ptype'";
    }
    if (!empty($pchtype)) {
        $pchtypecond = " AND `purtype`= '$pchtype'";
    }
    if (!empty($dtype)) {
        $dtypecond = " AND `dtype`= '$dtype' ";
    }

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




    if (($_GET["fdt"]) != '') {
        $mfdt = $_GET["fdt"];
        $mtdt = $_GET["tdt"];
        $mfrmdate = date("y-m-d", strtotime($mfdt));
        $mtodate = date("y-m-d", strtotime($mtdt));
        $dtype = $_GET["rpt"];
    } else {

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

        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");
        }
        $mfdt = date("d-m-Y", strtotime($mfrmdate));
        $mtdt = date("d-m-Y", strtotime($mtodate));
    }


    $getpmain = mysqli_query($conn, "SELECT *,DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt FROM `$maindb` WHERE `vouchdt` >= '$mfrmdate' 
                            AND `vouchdt` <= '$mtodate' $suppcond $godowncond $ptypecond $pchtypecond $gstcond $pcond $trancond ")or die(mysqli_error($conn));
    $getno = mysqli_num_rows($getpmain);
    include("includes/linkshead.php");
    ?>
    <style type="text/css">
        #tabital{
            font-style: italic;
        }
        #font1{
            font-style: italic;
        }
        #boldfont{
            font-weight: bold; 
        }


    </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-striped table-hover table-bordered">
                        <thead class="mySearchTr">
                            <tr>
                                <th width="3%;">Sr</th>
                                <th width="4%;">Voucher</th>
                                <th width="8%;">Date</th>
                                <th width="19%;">Party Name</th>
                                <th width="19%;">Transporter</th>
                                <th width="8%;">Type</th>
                                <th width="6%;" class="text-right">Sub Total</th>
                                <th width="5%;" class="text-right">Disc Amt.</th>
                                <th width="5%;" class="text-right">Taxable Amt.</th>
                                <th width="5%;" class="text-right">GST 3%</th>
                                <th width="5%;" class="text-right">GST 5%</th>
                                <th width="5%;" class="text-right">GST 12%</th>
                                <th width="5%;" class="text-right">GST 18%</th>
                                <th width="5%;" class="text-right">GST 24%</th>
                                <th width="5%;" class="text-right">Net Amount</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $ctr = 0;
                            $grgrostot = $grtotdis = $grsubtot = $grgsttot3 = $grgsttot5 = $grgsttot12 = $grgsttot18 = $grgsttot28 = $totbillamt = 0;

                            while ($pmain = mysqli_fetch_array($getpmain)) {
                                if ($pmain['paidby'] == "C") {
                                    $ptype = "CASH";
                                } else {
                                    $ptype = "CREDIT";
                                }
                                if ($pmain['purtype'] == "R") {
                                    $pctype = "Raw Material";
                                } else if ($pmain['purtype'] == "P") {
                                    $pctype = "Packing Material";
                                } else if ($pmain['purtype'] == "R") {
                                    $pctype = "Other";
                                } else {
                                    $pctype = "";
                                }

                                $gparty = mysqli_query($conn, "SELECT `accode`,`name` FROM `accmast` where `accode` = '$pmain[party]'") or die(mysqli_error($conn));
                                $sparty = 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);

                                $gtrans = mysqli_query($conn, "SELECT `code`,`name` FROM `transport` where `code` = '$pmain[transport]'") or die(mysqli_error($conn));
                                $strans = mysqli_fetch_array($gtrans);

                                $mown = mysqli_query($conn, "SELECT `accode`, `name` FROM `accmast` WHERE accode = '$pmain[debitac]' ORDER BY name") or die(mysqli_error($conn));
                                $trow = mysqli_fetch_array($mown);

                                $ctr++;
                                Echo"<tr data-href='purchord.php?mode=edit&vouchno=$pmain[voucher]' class='clickable-row2' [target]='_blank'>";
                                Echo"<td>$ctr</td>";
                                Echo"<td>$pmain[voucher]</td>";
                                Echo"<td>$pmain[mydt]</td>";
                                Echo"<td>$sparty[name]</td>";
                                Echo"<td>$strans[name]</td>";
                                Echo"<td>$pctype</td>";
                                Echo"<td class='text-right'>$pmain[grossamt]</td>";
                                Echo"<td class='text-right'>$pmain[discamt]</td>";
                                Echo"<td class='text-right'>$pmain[taxableamt]</td>";
                                Echo"<td class='text-right'>$pmain[totgst3]</td>";
                                Echo"<td class='text-right'>$pmain[totgst5]</td>";
                                Echo"<td class='text-right'>$pmain[totgst12]</td>";
                                Echo"<td class='text-right'>$pmain[totgst18]</td>";
                                Echo"<td class='text-right'>$pmain[totgst28]</td>";
                                Echo"<td class='text-right'>$pmain[billamt]</td>";
                                Echo"</tr>";

                                $grgrostot = $grgrostot + $pmain['grossamt'];
                                $grtotdis = $grtotdis + $pmain['discamt'];
                                $grsubtot = $grsubtot + $pmain['taxableamt'];
                                $grgsttot3 = $grgsttot3 + $pmain['totgst3'];
                                $grgsttot5 = $grgsttot5 + $pmain['totgst5'];
                                $grgsttot12 = $grgsttot12 + $pmain['totgst12'];
                                $grgsttot18 = $grgsttot18 + $pmain['totgst18'];
                                $grgsttot28 = $grgsttot28 + $pmain['totgst28'];
                                $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-hover table-bordered'>";
                                    Echo "<thead>";
                                    Echo "<tr class='' style='background-color:#BABFC2'>";
                                    Echo "<th style='width:5%;'>Sr</th>";
                                    Echo "<th style='width:30%;' class=''>Item Name</th>";
                                    Echo "<th style='width:10%;' class='text-right'>Qty.</th>";
                                    Echo "<th style='width:10%;' class='text-right'>Rate</th>";
                                    Echo "<th style='width:15%;' class='text-right'>Amount</th>";
                                    Echo "<th style='width:15%;' class='text-right'>Disc. Amt.</th>";
                                    Echo "<th style='width:15%;' class='text-right'>Taxable Amt.</th>";
                                    Echo "<th style='width:15%;' class='text-right'>CGST Rate</th>";
                                    Echo "<th style='width:15%;' class='text-right'>CGST Amt.</th>";
                                    Echo "<th style='width:15%;' class='text-right'>SGST Rate</th>";
                                    Echo "<th style='width:15%;' class='text-right'>SGST Amt.</th>";
                                    Echo "<th style='width:15%;' class='text-right'>IGST Rate</th>";
                                    Echo "<th style='width:15%;' class='text-right'>IGST Amt.</th>";
                                    Echo "<th style='width:15%;' class='text-right'>Total</th>";
                                    Echo "</tr>";
                                    Echo "<thead>";
                                    Echo "<tr>";
//
//
                                    $ctr2 = 0;
                                    $totqty = 0;
                                    $totrate = 0;
                                    $totamount = $totdiscount = $totsubtot = $totcgstamt = $totsgstamt = $totigstamt = $tottotal = 0;

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

                                        $getitem = mysqli_query($conn, "SELECT `code`,`name` FROM `item` WHERE `code`='$tran[itemcode]'") or die(mysqli_error($conn));
                                        $showitem = mysqli_fetch_array($getitem);

                                        $ctr2++;
                                        Echo "<tr>";
                                        Echo "<td>$ctr2</td>";
                                        Echo "<td>$showitem[name]</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[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'];
                                        $totamount = $totamount + $tran['amount'];
                                        $totdiscount = $totdiscount + $tran['discamt'];
                                        $totsubtot = $totsubtot + $tran['subtot'];
                                        $totcgstamt = $totcgstamt + $tran['cgstamt'];
                                        $totsgstamt = $totsgstamt + $tran['sgstamt'];
                                        $totigstamt = $totigstamt + $tran['igstamt'];
                                        $tottotal = $tottotal + $tran['total'];
                                    }

                                    Echo "<tr>";
                                    Echo "<td></td>";
                                    Echo "<td  class='text-right'>Total</td>";
                                    Echo "<td class='text-right'>$totqty</td>";
                                    Echo "<td></td>";
                                    Echo "<td class='text-right'>$totamount</td>";
                                    Echo "<td class='text-right'>$totdiscount</td>";
                                    Echo "<td class='text-right'>$totsubtot</td>";
                                    Echo "<td class='text-right'></td>";
                                    Echo "<td class='text-right'>$totcgstamt</td>";
                                    Echo "<td class='text-right'></td>";
                                    Echo "<td class='text-right'>$totsgstamt</td>";
                                    Echo "<td class='text-right'></td>";
                                    Echo "<td class='text-right'>$totigstamt</td>";
                                    Echo "<td class='text-right'>$tottotal</td>";
                                    Echo "</tr>";
                                    Echo "</table>";
                                }
                            }
                            Echo "<tr style='background-color:#26A69A;'>";
                            Echo "<td></td>";
                            Echo "<td></td>";
                            Echo "<td></td>";
                            Echo "<td></td>";
                            Echo "<td></td>";
                            Echo "<td class='text-right'><b>Total</b></td>";
                            Echo "<td class='numeric text-right'><b>$grgrostot</b></td>";
                            Echo "<td class='numeric text-right'><b>$grtotdis</b></td>";
                            Echo "<td class='numeric text-right'><b>$grsubtot</b></td>";
                            Echo "<td class='numeric text-right'><b>$grgsttot3</b></td>";
                            Echo "<td class='numeric text-right'><b>$grgsttot5</b></td>";
                            Echo "<td class='numeric text-right'><b>$grgsttot12</b></td>";
                            Echo "<td class='numeric text-right'><b>$grgsttot18</b></td>";
                            Echo "<td class='numeric text-right'><b>$grgsttot28</b></td>";
                            Echo "<td class='numeric text-right'><b>$totbillamt</b></td>";
                            Echo "</tr>";
                            ?>
                        </tbody>
                    </table>
                </div> 
            </div>
        </div>
        <?php include("includes/linksfoot.php");
        ?>
    </body>
    </html>
    <?php
}
if (isset($_POST["excel"])) {
    $supplier = $_POST["supplier"];
    $godown = $_POST["godown"];
    $ptype = $_POST["ptype"];
    $pchtype = $_POST["pchtype"];
    $dtype = $_POST["dtype"];
    $gsttype = $_POST["gsttype"];
    $transport = $_POST["transport"];

    if (!empty($transport)) {
        $trancond = " AND `transport` = '$transport' ";
    }
    if (!empty($supplier)) {
        $suppcond = " AND `party` = '$supplier' ";
    }
    if (!empty($godown)) {
        $godowncond = " AND `godown` = '$godown' ";
    }
    if (!empty($ptype)) {
        $ptypecond = " AND `paidby`= '$ptype'";
    }
    if (!empty($pchtype)) {
        $pchtypecond = " AND `purtype`= '$pchtype'";
    }
    if (!empty($dtype)) {
        $dtypecond = " AND `dtype`= '$dtype' ";
    }
    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));

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

    $contents .= "Sr. ,Voucher,Date,Party Name,Transporter, Type,Sub. Total,Disc Amt.,Taxable Amt., GST 3%, GST 5%, GST 12%, GST 18%, GST 28%,Net Amount \n";

    $ctr = 0;
    $grgrostot = $grtotdis = $grsubtot = $grgsttot3 = $grgsttot5 = $grgsttot12 = $grgsttot18 = $grgsttot28 = $totbillamt = 0;

    while ($pmain = mysqli_fetch_array($getpmain)) {
        if ($pmain['paidby'] == "C") {
            $ptype = "CASH";
        } else {
            $ptype = "CREDIT";
        }
        if ($pmain['purtype'] == "R") {
            $pctype = "Raw Material";
        } else if ($pmain['purtype'] == "P") {
            $pctype = "Packing Material";
        } else if ($pmain['purtype'] == "R") {
            $pctype = "Other";
        } else {
            $pctype = "";
        }

        $gparty = mysqli_query($conn, "SELECT `accode`,`name` FROM `accmast` where `accode` = '$pmain[party]' AND grpcode='009'  ") or die(mysqli_error($conn));
        $sparty = 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);

        $mown = mysqli_query($conn, "SELECT `accode`, `name` FROM `accmast` WHERE accode = '$pmain[debitac]' ORDER BY name") or die(mysqli_error($conn));
        $trow = mysqli_fetch_array($mown);

        $gtrans = mysqli_query($conn, "SELECT `code`,`name` FROM `transport` where `code` = '$pmain[transport]'") or die(mysqli_error($conn));
        $strans = mysqli_fetch_array($gtrans);


        $ctr++;


        $contents .= $ctr . ",";
        $contents .= $pmain['voucher'] . ",";
        $contents .= $pmain['mydt'] . ",";
        $contents .= $sparty['name'] . ",";
        $contents .= $strans['name'] . ",";
        $contents .= $pctype . ",";
        $contents .= $pmain['grossamt'] . ",";
        $contents .= $pmain['discamt'] . ",";
        $contents .= $pmain['taxableamt'] . ",";
        $contents .= $pmain['totgst3'] . ",";
        $contents .= $pmain['totgst5'] . ",";
        $contents .= $pmain['totgst12'] . ",";
        $contents .= $pmain['totgst18'] . ",";
        $contents .= $pmain['totgst28'] . ",";
        $contents .= $pmain['billamt'] . "\n";

        $grgrostot = $grgrostot + $pmain['grossamt'];
        $grtotdis = $grtotdis + $pmain['discamt'];
        $grsubtot = $grsubtot + $pmain['taxableamt'];
        $grgsttot3 = $grgsttot3 + $pmain['totgst3'];
        $grgsttot5 = $grgsttot5 + $pmain['totgst5'];
        $grgsttot12 = $grgsttot12 + $pmain['totgst12'];
        $grgsttot18 = $grgsttot18 + $pmain['totgst18'];
        $grgsttot28 = $grgsttot28 + $pmain['totgst28'];
        $totbillamt = $totbillamt + $pmain['billamt'];

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

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

            $ctr2 = 0;
            $totqty = 0;
            $totrate = 0;
            $totamount = $totdiscount = $totsubtot = $totcgstamt = $totsgstamt = $totigstamt = $tottotal = 0;

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

                $getitem = mysqli_query($conn, "SELECT `code`,`name` FROM `item` WHERE `code`='$tran[itemcode]'") or die(mysqli_error($conn));
                $showitem = mysqli_fetch_array($getitem);
                $ctr2++;



                $contents .= $ctr2 . ",";
                $contents .= $showitem['name'] . ",";
                $contents .= $tran['qty'] . ",";
                $contents .= $tran['rate'] . ",";
                $contents .= $tran['amount'] . ",";
                $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'];
                $totamount = $totamount + $tran['amount'];
                $totdiscount = $totdiscount + $tran['discamt'];
                $totsubtot = $totsubtot + $tran['subtot'];
                $totcgstamt = $totcgstamt + $tran['cgstamt'];
                $totsgstamt = $totsgstamt + $tran['sgstamt'];
                $totigstamt = $totigstamt + $tran['igstamt'];
                $tottotal = $tottotal + $tran['total'];
            }



            $contents .= " " . ",";
            $contents .= "Total" . ",";
            $contents .= $totqty . ",";
            $contents .= " " . ",";
            $contents .= $totamount . ",";
            $contents .= $totdiscount . ",";
            $contents .= $totsubtot . ",";
            $contents .= "" . ",";
            $contents .= $totcgstamt . ",";
            $contents .= "" . ",";
            $contents .= $totsgstamt . ",";
            $contents .= "" . ",";
            $contents .= $totigstamt . ",";
            $contents .= $tottotal . "\n";
        }
    }

    $contents .= " " . ",";
    $contents .= " " . ",";
    $contents .= " " . ",";
    $contents .= " " . ",";
    $contents .= " " . ",";
    $contents .= "Total" . ",";
    $contents .= $grgrostot . ",";
    $contents .= $grtotdis . ",";
    $contents .= $grsubtot . ",";
    $contents .= $grgsttot3 . ",";
    $contents .= $grgsttot5 . ",";
    $contents .= $grgsttot12 . ",";
    $contents .= $grgsttot18 . ",";
    $contents .= $grgsttot28 . ",";
    $contents .= $totbillamt . "\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;
    }
}
?>