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

$maindb = "item";
$trandb = "transacn";
$formname = "Bill Over Stock";
$file = "bill_o_stock.php";

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


if (isset($_POST["view"])) {
    $name = $_POST["name"];
    $asdt = $_POST["asdt"];
    $brand = $_POST["brand"];
    $type = $_POST["type"];
    $smode = $_POST["smode"];
    $godown = $_POST["godown"];

    $namecond = "";
    $brandcond = "";
    $typecond = "";
    $smodecond = "";
    $godowncond = "";

    if (!empty($name)) {
        $namecond = " AND b.`name` LIKE '$name%' ";
    }
    if (!empty($brand)) {
        $brandcond = " AND b.`brand`='$brand' ";
    }
    if (!empty($type)) {
        $typecond = " AND a.`type`='$type' ";
    }
    if (!empty($smode)) {
        $smodecond = " AND a.`salemode`='$smode' ";
    }
    if (!empty($godown)) {
        $godowncond = " AND a.`godown`='$godown' ";
    }


    $getitem = mysqli_query($conn, "SELECT * from item where code in (SELECT a.itemcode FROM `transacn` a, `item` b 
                           WHERE a.`itemcode` =  b.`code` AND a.`vouchdt` <= '$asdt' 
                           $namecond $brandcond $qualitycond $yarncond $typecond $shadecond 
                           GROUP BY a.itemcode, a.shade, a.actbatch having sum(a.totqty) < 1) 
                           group by code order by name") or die(mysqli_error($conn));

//    $getitem = mysqli_query($conn, "SELECT * FROM `item` WHERE `code`!='' $namecond $brandcond GROUP BY name ORDER BY `name`")or die(mysqli_error($conn));

    include("includes/linkshead.php");
    ?>
    <script src="MyScript/billostk.js" type="text/javascript"></script>
    </head>
    <body>
        <div class="container">
            <?php include("includes/head_rep.php"); ?>
            <div class="row">
                <div class="col-lg-12 pad">
                    <div class="panel panel-default">
                        <div class="panel-body">
                            <div class="col-lg-12 pad">
                                <table class="table table-condensed table-bordered">
                                    <tr style="background-color: #1ABC9C;">
                                        <th style="width:25%;">ITEMNAME</th>
                                        <th style="width:75%;">SHADE/QTY.</th>
                                    </tr>
                                    <?php
                                    while ($item = mysqli_fetch_array($getitem)) {
//                                        $getstockno = mysqli_query($conn, "SELECT SUM(totqty) as actqty FROM `transacn` WHERE `itemcode`='$item[code]' 
//                                                                   AND `vouchdt`<='$asdt' having sum(totqty) < 1 ORDER BY shade, actbatch") or die(mysqli_error($conn));
//                                        $stockno = mysqli_num_rows($getstockno);
//                                        if ($stockno > 0) {


                                            
//                                        $getprin = mysqli_query($conn, "SELECT SUM(totqty) as actqty, SUM(flag) as tflag, shade, actbatch
//                                                                FROM `transacn` WHERE `itemcode`='$item[code]' 
//                                                                AND `vouchdt`<='$asdt' GROUP BY shade
//                                                                having sum(totqty) < 0 ") or die(mysqli_error($conn));
//                                        $stkprin = mysqli_fetch_array($getprin);
//                                        if ($stkprin[actqty] > 0 && $stock[actqty] != 0) {  
                                            ?>
                                            <tr>
                                                <td><label class="label-control"><?php Echo $item['name']; ?></label></td>
                                                <td>
                                                    <?php
//                                                }
                                                    $getstock = mysqli_query($conn, "SELECT SUM(totqty) as actqty, SUM(flag) as tflag, shade, actbatch
                                                                    FROM `transacn` WHERE `itemcode`='$item[code]' 
                                                                    AND `vouchdt`<='$asdt' GROUP BY shade, actbatch 
                                                                    having sum(totqty) < 1 ORDER BY shade, actbatch") or die(mysqli_error($conn));
                                                    while ($stock = mysqli_fetch_array($getstock)) {
                                                        $actcol = "";
                                                        if ($stock['actqty'] < 0) {
                                                            $actcol = "style='color : red;'";
                                                        }
                                                        if ($stock['actqty'] > 0 && $stock['actqty'] != 0) {
                                                            ?>
                                                            <label class="label-control" style="color: #1ABC9C;">
                                                                <?php Echo $stock['shade'] ?>
                                                            </label><label class="label-control">
                                                                &nbsp;/&nbsp;
                                                            </label>
                                                            <label class="label-control" <?php Echo $actcol; ?>> 
                                                                <?php
                                                                if ($stock['tflag'] > 0) {
                                                                    Echo $stock['actqty'] . '<span class="text-primary"> (' . $stock['tflag'] . 'F)</span>';
                                                                } else {
                                                                    Echo $stock['actqty'];
                                                                }
                                                                ?>
                                                            </label>
                                                            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                                                            <?php
                                                            $totqty = $stock['actqty'] + $totqty;
                                                        }
                                                    }
                                                    ?>
                                                </td>
                                            </tr>
                                            <?php
//                                        }
                                            
                                    }
                                    ?>
                                    <tr>
                                        <td><label class="label-control" style="color: red;">TOTAL</label></td>
                                        <td><label class="label-control" style="color: red;"><? Echo $totqty; ?></label></td>
                                    </tr>
                                </table>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>

        <?php
    }
    if (isset($_POST["excel"])) {
        $name = $_POST["name"];
        $asdt = $_POST["asdt"];
        $brand = $_POST["brand"];
        $type = $_POST["type"];
        $smode = $_POST["smode"];
        $godown = $_POST["godown"];

        $namecond = "";
        $brandcond = "";
        $typecond = "";
        $smodecond = "";
        $godowncond = "";

        if (!empty($name)) {
            $namecond = " AND b.`name` LIKE '%$name%' ";
        }
        if (!empty($brand)) {
            $brandcond = " AND b.`brand`='$brand' ";
        }
        if (!empty($type)) {
            $typecond = " AND a.`type`='$type' ";
        }
        if (!empty($smode)) {
            $smodecond = " AND a.`salemode`='$smode' ";
        }
        if (!empty($godown)) {
            $godowncond = " AND a.`godown`='$godown' ";
        }


        $getitem = mysqli_query($conn, "SELECT * from item where code in (SELECT itemcode FROM `transacn` a, `item` b 
                           WHERE a.`itemcode` =  b.`code` AND a.`vouchdt` <= '$asdt' 
                           $brandcond $qualitycond $yarncond $typecond $shadecond
                           GROUP BY a.itemcode, a.shade, a.actbatch having sum(a.totqty) > 1) 
                           group by code order by name") or die(mysqli_error($conn));

        $contents.="ITEMNAME,SHADE/QTY\n";




        while ($item = mysqli_fetch_array($getitem)) {


            $getstock = mysqli_query($conn, "SELECT SUM(totqty) as actqty, SUM(flag) as tflag, shade, actbatch
                                                                    FROM `transacn` WHERE `itemcode`='$item[code]' 
                                                                    AND `vouchdt`<='$asdt' GROUP BY shade, actbatch 
                                                                    having sum(totqty) > 1 ORDER BY shade, actbatch") or die(mysqli_error($conn));
            while ($stock = mysqli_fetch_array($getstock)) {
                $name = $item['name'];
                $contents.=$name . ",";
                $actcol = "";
                if ($stock['actqty'] < 0) {
//                    $actcol = "style='color : red;'";
                }
                if ($stock['actqty'] != 0) {


//                    $shade = $stock[shade];
//                    $contents.=$shade . ",";

                    if ($stock['tflag'] > 0) {
                        $actflag = $stock['shade'] . "/" . $stock['actqty'] . " " . "($stock[tflag])" . "F";
                        $contents.=$actflag . "\n";
                    } else {
                        $act = $stock['actqty'];
                        $contents.=$act . "\n";
                    }

                    $totqty = $stock['actqty'] + $totqty;
                }
            }
        }
        $contents.= "\n";
        $contents.="Total" . "$totqty " . "\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;
        }
    }
    ?>
                                

