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

$formname = 'Product Groupwise Sale Register';
$file = 'itemwisereg_fr.php';


$prodgrp = $_POST["prodgrp"];
$product = $_POST["product"];
$dtype = $_POST["dtype"];

if (!empty($product)) {
    $prodcond = " AND `itemcode` = '$product' ";
    $prodcond2 = " AND b.`prodcode` = '$product' ";
}
if (!empty($prodgrp)) {
    $prodgrpcond = " AND a.`code` = '$prodgrp' ";
}
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");
}

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

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

$getgrp = mysqli_query($conn, "SELECT code, name FROM `prodgrp` WHERE `code` = '$prodgrp' ")or die(mysqli_error($conn));
$fetgrp = mysqli_fetch_array($getgrp);
$itemgrp = $fetgrp['name'];

$productgrp = mysqli_query($conn, "SELECT a.code, a.name, b.prodcode  from prodgrp a, product b where a.code = b.prodgrp
                               $prodgrpcond $prodcond2 group by a.code order by name ")or die(mysqli_error($conn));
$getno = mysqli_num_rows($productgrp);
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;
        }
        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">
                    <?php if (!empty($prodgrp)) { ?>
                        <table class="table table-bordered table-condensed">
                            <tr class="mySearchTr">
                                <td class="h4" style="color:white" width='90%'><b>Product Group : <?php echo $itemgrp; ?></b></td>
                            <tr>
                        </table>
                    <?php } ?>
                    <table style="background: white;width: 100%" class="table table-condensed table-striped table-hover table-bordered">
                        <thead class="mySearchTr">
                            <tr>
                                <th width='5%'>Sr</th>
                                <th width='20%' >Product Group</th>
                                <th width='10%' class="text-right">Sale Amt.</th>
                                <!--<th width='10%' class="text-right">Online Sale Amt.</th>-->
                                <!--<th width='10%' class="text-right">Total Amt.</th>-->
                                <th width='10%' class="text-right">Return Amt.</th>
                                <th width='10%' class="text-right">Total Sale Amt.</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $ctr = 0;
                            $totlsale = $totdesale = $totexsale = $totonlsale = $totalsale = $totreturn = $grtotsaleamt = 0;

                            while ($fprodgrp = mysqli_fetch_array($productgrp)) {

                                $pgrpcode = $fprodgrp['code'];
                                $prodgrp2 = $fprodgrp['name'];

//                                $localsale = mysqli_query($conn, "SELECT SUM(amount) as totamt from transacn where itemgrp = '$pgrpcode' and source = 'SL' $prodcond ")or die(mysqli_error($conn));

                                $localsale = mysqli_query($conn, "SELECT SUM(amount) as totamt from transacn where vouchdt >= '$mfrdate' and vouchdt <= '$mtodate'
                                                                  and itemcode in (select code from product where prodgrp = '$pgrpcode') and source = 'SL' ")or die(mysqli_error($conn));
                                $fetch_localsale = mysqli_fetch_array($localsale);

                                if ($fetch_localsale['totamt'] > 0) {
                                    $lsale = $fetch_localsale['totamt'];
                                } else {
                                    $lsale = 0;
                                }

//                                $onlinesale = mysqli_query($conn, "SELECT SUM(amount) as totamt from transacn where vouchdt >= '$mfrdate' and vouchdt <= '$mtodate'
//                                                                  and itemcode in (select prodcode from product where prodgrp = '$pgrpcode') and source = 'SLON'  ")or die(mysqli_error($conn));
//                                $fetch_onlinesale = mysqli_fetch_array($onlinesale);
//
//                                if ($fetch_onlinesale['totamt'] > 0) {
//                                    $onlsale = $fetch_onlinesale['totamt'];
//                                } else {
//                                    $onlsale = 0;
//                                }

                                $totsale = $lsale ;

                                $salereturn = mysqli_query($conn, "SELECT SUM(amount) as totamt from transacn where vouchdt >= '$mfrdate' and vouchdt <= '$mtodate' 
                                                                  and itemcode in (select code from product where prodgrp = '$pgrpcode') and source = 'SR' ")or die(mysqli_error($conn));
                                $fetch_salereturn = mysqli_fetch_array($salereturn);

                                if ($fetch_salereturn['totamt'] > 0) {
                                    $return = $fetch_salereturn['totamt'];
                                } else {
                                    $return = 0;
                                }

                                $totsaleamt = $totsale - $return;

                                $ctr++;
                                Echo"<tr>";
                                Echo"<td>$ctr</td>";
                                Echo"<td>$prodgrp2</td>";
                                Echo"<td class='text-right'>$lsale</td>";
//                                Echo"<td class='text-right'>$onlsale</td>";
//                                Echo"<td class='text-right'>$totsale</td>";
                                Echo"<td class='text-right'>$return</td>";
                                Echo"<td class='text-right'>$totsaleamt</td>";
                                Echo"</tr>";

                                $totlsale = $totlsale + $lsale;
//                                $totonlsale = $totonlsale + $onlsale;
//                                $totalsale = $totalsale + $totsale;
                                $totreturn = $totreturn + $return;
                                $grtotsaleamt = $grtotsaleamt + $totsaleamt;

                                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='mySearchTr2'>";
                                    Echo "<th width='3%'  class='dth'>Sr</th>";
                                    Echo "<th width='38%'  class='dth'>Product</th>";
                                    Echo "<th width='8%'  class='text-right dth'>Local Sale</th>";
//                                    Echo "<th width='8%'  class='text-right dth'>Online Sale</th>";
//                                    Echo "<th width='8%'  class='text-right dth'>Total Sale</th>";
                                    Echo "<th width='8%'  class='text-right dth'>Return Sale</th>";
                                    Echo "<th width='8%'  class='text-right dth'>Total Amt.</th>";
                                    Echo "</tr>";
//                                    Echo "<thead>";
//                                    Echo "<tr>";

                                    $ctr2 = 0;
                                    $totqty = 0;
                                    $slqty = 0;
                                    $totamt = 0;

                                    $tran_qry = mysqli_query($conn, "SELECT itemcode, sum(amount) as samt, source from transacn 
                                                             where  itemcode in (select code from product where prodgrp = '$pgrpcode') group by source ")or die(mysqli_error($conn));

                                    $totlsamt = $totdeamt = $totexamt = $totalsl = $totrtamt = $grtotamt = $ctr2 = 0;
                                    while ($tran = mysqli_fetch_array($tran_qry)) {

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

                                        if ($tran['source'] == "SL") {
                                            $lsamt = $tran['samt'];
                                        } else {
                                            $lsamt = 0;
                                        }
                                        if ($tran['source'] == "SLON") {
                                            $onlamt = $tran['samt'];
                                        } else {
                                            $onlamt = 0;
                                        }
                                        if ($tran['source'] == "SR") {
                                            $rtamt = $tran['samt'];
                                        } else {
                                            $rtamt = 0;
                                        }

                                        $totsl = $lsamt;

                                        $totalamt = $totsl - $rtamt;

                                        if ($totalamt > 0) {
                                            $ctr2++;

                                            Echo "<tr style='color:seagreen'>";
                                            Echo "<td >$ctr2</td>";
                                            Echo "<td>$productname</td>";
                                            Echo "<td class='text-right'>$lsamt</td>";
//                                            Echo "<td class='text-right'>$onlamt</td>";
//                                            Echo "<td class='text-right'>$totsl</td>";
                                            Echo "<td class='text-right'>$rtamt</td>";
                                            Echo "<td class='text-right'>$totalamt</td>";
                                            Echo "</tr>";


                                            $totlsamt = $totlsamt + $lsamt;
//                                            $totonlamt = $totonlamt + $onlamt;
//                                            $totalsl = $totalsl + $totsl;
                                            $totrtamt = $totrtamt + $rtamt;
                                            $grtotamt = $grtotamt + $totalamt;
                                        }
                                    }

                                    Echo "<tr>";
                                    Echo "<td class='dth'></td>";
                                    Echo "<td class='text-right dth'>Total</td>";
                                    Echo "<td class='text-right dth'>$totlsamt</td>";
//                                    Echo "<td class='text-right dth'>$totonlamt</td>";
//                                    Echo "<td class='text-right dth'>$totalsl</td>";
                                    Echo "<td class='text-right dth'>$totrtamt</td>";
                                    Echo "<td class='text-right dth'>$grtotamt</td>";
                                    Echo "</tr>";
                                    Echo "</table>";
                                }
                            }


                            Echo "<tr>";
                            Echo "<td class='colr'></td>";
                            Echo "<td class='text-right colr'><b>Total</b></td>";
                            Echo "<td class='numeric text-right colr '><b>$totlsale</b></td>";
//                            Echo "<td class='numeric text-right colr '><b>$totonlsale</b></td>";
                            Echo "<td class='numeric text-right colr '><b>$totreturn</b></td>";
                            Echo "<td class='numeric text-right colr '><b>$grtotsaleamt</b></td>";
                            Echo "</tr>";
                            ?>
                        </tbody>
                    </table>
                </div> 
            </div>
        </div>
        <?php include("includes/linksfoot.php");
        ?>
    </body>
    </html>
    <?php
}

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

    if (!empty($prodgrp)) {

        $contents .= "Product Group : $itemgrp \n";
    }

    $contents .= "Sr., Product Group, Local Sale Amt., Deemed Sale Amt., Export Sale Amt., Online Sale Amt., Total Amr., Return Amt., Total Sale Amt.\n";

    $ctr = 0;
    $totlsale = $totdesale = $totexsale = $totonlsale = $totalsale = $totreturn = $grtotsaleamt = 0;

    while ($fprodgrp = mysqli_fetch_array($productgrp)) {

        $pgrpcode = $fprodgrp['code'];
        $prodgrp2 = $fprodgrp['name'];

        $localsale = mysqli_query($conn, "SELECT SUM(amount) as totamt from transacn where vouchdt >= '$mfrdate' and vouchdt <= '$mtodate'
                                          and itemcode in (select prodcode from product where prodgrp = '$pgrpcode') and source = 'SL' ")or die(mysqli_error($conn));
        $fetch_localsale = mysqli_fetch_array($localsale);

        if ($fetch_localsale['totamt'] > 0) {
            $lsale = $fetch_localsale['totamt'];
        } else {
            $lsale = 0;
        }
        
        $deemedsale = mysqli_query($conn, "SELECT SUM(amount) as totamt from transacn where vouchdt >= '$mfrdate' and vouchdt <= '$mtodate' 
                                           and itemcode in (select prodcode from product where prodgrp = '$pgrpcode') and source = 'SLDE' ")or die(mysqli_error($conn));
        $fetch_deemedsale = mysqli_fetch_array($deemedsale);

        if ($fetch_deemedsale['totamt'] > 0) {
            $desale = $fetch_deemedsale['totamt'];
        } else {
            $desale = 0;
        }

        $exportsale = mysqli_query($conn, "SELECT SUM(amount) as totamt from transacn where vouchdt >= '$mfrdate' and vouchdt <= '$mtodate'
                                           and itemcode in (select prodcode from product where prodgrp = '$pgrpcode') and source = 'SLEX' ")or die(mysqli_error($conn));
        $fetch_exportsale = mysqli_fetch_array($exportsale);

        if ($fetch_exportsale['totamt'] > 0) {
            $exsale = $fetch_exportsale['totamt'];
        } else {
            $exsale = 0;
        }

        $onlinesale = mysqli_query($conn, "SELECT SUM(amount) as totamt from transacn where vouchdt >= '$mfrdate' and vouchdt <= '$mtodate'
                                          and itemcode in (select prodcode from product where prodgrp = '$pgrpcode') and source = 'SLON'  ")or die(mysqli_error($conn));
        $fetch_onlinesale = mysqli_fetch_array($onlinesale);

        if ($fetch_onlinesale['totamt'] > 0) {
            $onlsale = $fetch_onlinesale['totamt'];
        } else {
            $onlsale = 0;
        }

        $totsale = $lsale + $desale + $exsale + $onlsale;

        $salereturn = mysqli_query($conn, "SELECT SUM(amount) as totamt from transacn where vouchdt >= '$mfrdate' and vouchdt <= '$mtodate'
                                          and itemcode in (select prodcode from product where prodgrp = '$pgrpcode') and source = 'SR'  ")or die(mysqli_error($conn));
        $fetch_salereturn = mysqli_fetch_array($salereturn);

        if ($fetch_salereturn['totamt'] > 0) {
            $return = $fetch_salereturn['totamt'];
        } else {
            $return = 0;
        }

        $totsaleamt = $totsale - $return;

        $ctr++;

        $contents .= $ctr . ",";
        $contents .= $prodgrp2 . ",";
        $contents .= $lsale . ",";
        $contents .= $desale . ",";
        $contents .= $exsale . ",";
        $contents .= $onlsale . ",";
        $contents .= $totsale . ",";
        $contents .= $return . ",";
        $contents .= $totsaleamt . "\n";


        $totlsale = $totlsale + $lsale;
        $totdesale = $totdesale + $desale;
        $totexsale = $totexsale + $exsale;
        $totonlsale = $totonlsale + $exsale;
        $totalsale = $totalsale + $totsale;
        $totreturn = $totreturn + $return;
        $grtotsaleamt = $grtotsaleamt + $totsaleamt;


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

            $contents .= "Sr., Product, Local Sale, Deemed Sale, Export Sale, Online Sale, Total Sale, Reurn Sale, Total Amt.,\n";

            $ctr2 = 0;
            $totqty = 0;
            $slqty = 0;
            $totamt = 0;

            $tran_qry = mysqli_query($conn, "SELECT itemcode, sum(amount) as samt, source from transacn 
                                                             where itemcode in (select prodcode from product where prodgrp = '$pgrpcode') group by source ")or die(mysqli_error($conn));


            $totlsamt = $totdeamt = $totexamt = $totalsl = $totrtamt = $grtotamt = 0;
            while ($tran = mysqli_fetch_array($tran_qry)) {

                $getproduct = mysqli_query($conn, "SELECT `name` FROM `product` WHERE `prodcode`='$tran[itemcode]'") or die(mysqli_error($conn));
                $product = mysqli_fetch_array($getproduct);
                $productname = $product['name'];

                if ($tran['source'] == "SL") {
                    $lsamt = $tran['samt'];
                } else {
                    $lsamt = 0;
                }
                if ($tran['source'] == "SLDE") {
                    $deamt = $tran['samt'];
                } else {
                    $deamt = 0;
                }
                if ($tran['source'] == "SLEX") {
                    $examt = $tran['samt'];
                } else {
                    $examt = 0;
                }
                if ($tran['source'] == "SLON") {
                    $onlamt = $tran['samt'];
                } else {
                    $onlamt = 0;
                }
                if ($tran['source'] == "SR") {
                    $rtamt = $tran['samt'];
                } else {
                    $rtamt = 0;
                }

                $totsl = $lsamt + $deamt + $examt + $onlamt;

                $totalamt = $totsl - $rtamt;

                $ctr2++;
                if ($totalamt > 0) {
                    $contents .= $ctr2 . ",";
                    $contents .= $productname . ",";
                    $contents .= $lsamt . ",";
                    $contents .= $deamt . ",";
                    $contents .= $examt . ",";
                    $contents .= $onlamt . ",";
                    $contents .= $totsl . ",";
                    $contents .= $rtamt . ",";
                    $contents .= $totalamt . "\n";


                    $totlsamt = $totlsamt + $lsamt;
                    $totdeamt = $totdeamt + $deamt;
                    $totexamt = $totexamt + $examt;
                    $totonlamt = $totonlamt + $onlamt;
                    $totalsl = $totalsl + $totsl;
                    $totrtamt = $totrtamt + $rtamt;
                    $grtotamt = $grtotamt + $totalamt;
                }
            }


            $contents .= " " . ",";
            $contents .= "Total " . ",";
            $contents .= $totlsamt . ",";
            $contents .= $totdeamt . ",";
            $contents .= $totexamt . ",";
            $contents .= $totonlamt . ",";
            $contents .= $totalsl . ",";
            $contents .= $totrtamt . ",";
            $contents .= $grtotamt . ",";
        }
    }
    $contents .= " " . ",";
    $contents .= "Total" . ",";
    $contents .= $totlsale . ",";
    $contents .= $totdesale . ",";
    $contents .= $totexsale . ",";
    $contents .= $totonlsale . ",";
    $contents .= $totalsale . ",";
    $contents .= $totreturn . ",";
    $contents .= $grtotsaleamt . "\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;
    }
}
?>