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

$formname = 'Sale Value Itemwise';
$file = 'salitemwisereg_fr.php';

$product = $_POST["prodcode"];
$orderby = $_POST["orderby"];

if (!empty($product)) {
    $prodcond = " AND `itemcode` = '$product' ";
}

if (!empty($orderby)) {
    if ($orderby == 1) {
        $ordcond = " order by `itemcode` ";
    } else {
        $ordcond = " order by `itemcode`";
    }
}

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

mysqli_query($conn, "create temporary table slvaldbf 
                            SELECT itemcode, gram, unit, barcode, hsncode, SUM(qty) as qty, rate, 
                            sum(amount) as amount, sum(discamt) as discount, sum(cgstamt+sgstamt+igstamt) as taxamt, sum(total) as total,
                            DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt FROM `saletran` WHERE `vouchdt` >= '$mfrmdate' 
                            AND `vouchdt` <= '$mtodate' $prodcond  group by barcode                             
                            union    
                            SELECT itemcode, gram, unit, barcode, hsncode, SUM(qty) as qty, rate, 
                            sum(amount) as amount, sum(discamt) as discount, sum(cgstamt+sgstamt+igstamt) as taxamt, sum(total) as total,
                            DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt FROM `deextran` WHERE `vouchdt` >= '$mfrmdate' 
                            AND `vouchdt` <= '$mtodate' $prodcond  group by barcode                             
                            union    
                            SELECT itemcode, gram, unit, barcode, hsncode, SUM(qty) as qty, rate, 
                            sum(amtcurr) as amount, 0000000.00 as discount, 0000000.00 as taxamt, sum(amtcurr) as total,
                            DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt FROM `saletranexp` WHERE `vouchdt` >= '$mfrmdate' 
                            AND `vouchdt` <= '$mtodate' $prodcond  group by barcode                             
                            union    
                            SELECT itemcode, gram, unit, barcode, hsncode, SUM(qty) as qty, rate, 
                            sum(amount) as amount, sum(discamt) as discount, sum(cgstamt+sgstamt+igstamt) as taxamt, sum(total) as total,
                            DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt FROM `slonltran` WHERE `vouchdt` >= '$mfrmdate' 
                            AND `vouchdt` <= '$mtodate' $prodcond  group by barcode                             
                            ")or die(mysqli_error($conn));

$mainqry = mysqli_query($conn, "SELECT itemcode, gram, unit, barcode, hsncode, SUM(qty) as qty, rate, 
                            sum(amount) as amount, sum(discount) as discount, sum(taxamt) as taxamt, sum(total) as total,
                            mydt FROM `slvaldbf` group by barcode $ordcond")or die(mysqli_error($conn));

$getno = mysqli_num_rows($mainqry);

mysqli_query($conn, "drop table slvaldbf ")or die(mysqli_error($conn));


if (isset($_POST["view"])) {
    include("includes/linkshead.php");
    ?>
    <style type="text/css">
        #tabital{
            font-style: italic;
        }
        #font1{
            font-style: italic;
        }
        #boldfont{
            font-weight: bold; 
        }
        th{
            background: #26A69A;
        }
        .dth{
            background:#BABFC2;
        }
        .colr{
            background: #26A69A;
            font-size: 17px;
            color: white;

        }
        table tr td{
            font-size: 14px;
        }

    </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='10%'>Product Code</th>
                                <th width='36%'>Product Name</th>
                                <th width='7%'>Grammage</th>
                                <th width='4%'>Unit</th>
                                <th width='7%'>HSN Code</th>
                                <th width='7%' class="text-right">Qty.</th>
                                <th width='6%' class="text-right">Amount</th>
                                <th width='7%' class="text-right">Discount</th>
                                <th width='7%' class="text-right">GST</th>
                                <th width='8%' class="text-right">Net Amount</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $ctr = 0;
                            $totqty = $totamt = $totdisc = $tottax = $netamt = 0;
                            while ($fmain = mysqli_fetch_array($mainqry)) {

                                $itemcode = $fmain['itemcode'];

                                $gram = $fmain['gram'];
                                $unit = $fmain['unit'];
                                $hsncode = $fmain['hsncode'];
                                $qty = $fmain['qty'];
                                $amount = $fmain['amount'];
                                $discount = $fmain['discount'];
                                $taxamt = $fmain['taxamt'];
                                $total = $fmain['total'];

                                $gprod = mysqli_query($conn, "SELECT name from product where prodcode = '$itemcode'")or die(mysqli_error($conn));
                                $fprod = mysqli_fetch_array($gprod);
                                $itemname = $fprod['name'];

                                $ctr++;
                                Echo"<tr>";
                                Echo"<td>$ctr</td>";
                                Echo"<td>$itemcode</td>";
                                Echo"<td>$itemname</td>";
                                Echo"<td>$gram</td>";
                                Echo"<td>$unit</td>";
                                Echo"<td>$hsncode</td>";
                                Echo"<td class='text-right'>$qty</td>";
                                Echo"<td class='text-right'>" . number_format($amount,2) . "</td>";
                                Echo"<td class='text-right'>" . number_format($discount,2) . "</td>";
                                Echo"<td class='text-right'>" . number_format($taxamt,2) . "</td>";
                                Echo"<td class='text-right'>" . number_format($total,2) . "</td>";
                                Echo"</tr>";

                                $totqty = $totqty + $qty;
                                $totamt = $totamt + $amount;
                                $totdisc = $totdisc + $discount;
                                $tottax = $tottax + $taxamt;
                                $netamt = $netamt + $total;
                            }

                            Echo "<tr>";
                            Echo "<td class='text-right colr' colspan='6'><b>Total</b></td>";
                            Echo "<td class='text-right colr'><b>$totqty</b></td>";
                            Echo "<td class='text-right colr'><b>$totamt</b></td>";
                            Echo "<td class='text-right colr'><b>$totdisc</b></td>";
                            Echo "<td class='text-right colr'><b>$tottax</b></td>";
                            Echo "<td class='text-right colr'><b>$netamt</b></td>";
                            Echo "</tr>";
                            ?>
                        </tbody>
                    </table>
                </div> 
            </div>
        </div>
        <?php include("includes/linksfoot.php");
        ?>
    </body>
    </html>
    <?php
}

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

    $contents .= " Sr, Item Code, Name, Grammage, Unit, HSN Code, Qty., Amount, Discount, GST, Net Amount \n";

    $ctr = 0;
    $totqty = $totamt = $totdisc = $tottax = $netamt = 0;
    while ($fmain = mysqli_fetch_array($mainqry)) {

        $ctr++;
        $gprod = mysqli_query($conn, "SELECT name from product where prodcode = '$fmain[itemcode]' ")or die(mysqli_error($conn));
        $fprod = mysqli_fetch_array($gprod);
        $itemname = $fprod['name'];

        $contents .= $ctr . ",";
        $contents .= $fmain['itemcode'] . ",";
        $contents .= $itemname . ",";
        $contents .= $fmain['gram'] . ",";
        $contents .= $fmain['unit'] . ",";
        $contents .= $fmain['hsncode'] . ",";
        $contents .= $fmain['qty'] . ",";
        $contents .= $fmain['amount'] . ",";
        $contents .= $fmain['discount'] . ",";
        $contents .= $fmain['taxamt'] . ",";
        $contents .= $fmain['total'] . "\n";


        $totqty = $totqty + $fmain['qty'];
        $totamt = $totamt + $fmain['amount'];
        $totdisc = $totdisc + $fmain['discount'];
        $tottax = $tottax + $fmain['taxamt'];
        $netamt = $netamt + $fmain['total'];
    }


    $contents .= " " . ",";
    $contents .= " " . ",";
    $contents .= " " . ",";
    $contents .= " " . ",";
    $contents .= " " . ",";
    $contents .= "Total" . ",";
    $contents .= $totqty . ",";
    $contents .= $totamt . ",";
    $contents .= $totdisc . ",";
    $contents .= $tottax . ",";
    $contents .= $netamt . "\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;
    }
}
?>