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

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

$formname = 'Minimum Stock Alert';

$maindb = 'item';
$trandb = 'transacn';

$grid = 'minstock.php';

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

    $ncond = "";
    $gcond = "";
    $subcond = "";
    if (!empty($_POST["name"])) {
        $nname = trim($_POST["name"]);
        $ncond = " and `name` LIKE '$nname%' ";
    }

    if (!empty($_POST["itemgrp"])) {
        $gname = trim($_POST["itemgrp"]);
        $gcond = " and `itemgrp` = '$gname' ";
    }

    if (!empty($_POST["location"])) {
        $location = trim($_POST["location"]);
        $subcond = " and `location` = '$location' ";
    }
    if (empty($_POST["frmdt"])) {
        browse("FROM DATE CANNOT BE EMPTY");
        header("refresh:1; $grid");
    }
    if (empty($_POST["todt"])) {
        browse("TO DATE CANNOT BE EMPTY");
        header("refresh:1; $grid");
    }


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

    $getitem = mysqli_query($conn, "SELECT * FROM `$maindb`  WHERE name != '' AND mini > 0 $ncond $gcond ") or die(mysqli_error($conn));
    $getno = mysqli_num_rows($getitem);
    
    
    ?>
    <!DOCTYPE HTML>
    <html lang="en-US">

        <head>
            <?php
            include("includes/files.php");
            ?>
        </head>
        <body>

            <?php
            require("includes/dbconnect.php");
            $comp = mysqli_query($conn, "SELECT * FROM `company`") or die(mysql_errno());
            $zcomp = mysqli_fetch_assoc($comp);
            echo "<font size='4px'><b>$zcomp[compname]</font></b></font><br />";
            echo "<font size='2px'><b> $zcomp[add1]. $zcomp[add2].  $zcomp[add3] </b></font><br /><br />";
            echo "<b><font size='3px'>Date : " . date("d-m-Y") . "</b><br />";
            echo "From Date - " . date("d-m-Y", strtotime($mfrmdate)) . "<br/>
                To Date &nbsp;&nbsp;&nbsp;&nbsp;-  " . date("d-m-Y", strtotime($mtodate)) . "<br/>";
//            echo "<b>" . "Total Records - $getno</b><br />"; 
            echo "<br />";
            echo "</font>";
            ?>

            <div class="row">
                <div class="col-lg-6" style="width:100%;">
                    <div class="box">
                        <header>
                            <h5><?php echo $formname; ?></h5>
                            <div class="toolbar">
                                <div class="btn-group">		  
                                    <div class="btn-group">

                                        <a href="#sortableTable" data-toggle="collapse" class="btn btn-default btn-sm minimize-box">
                                            <i class="fa fa-angle-up"></i>
                                        </a>

                                        <input type='button' name='submit' class='btn btn-sm btn-danger' onClick="window.location.href = '<?php echo $grid; ?>'" value="Exit"  value='Save'/> 
                                    </div>
                                </div>
                            </div>
                        </header>
                        <div id="sortableTable" class="body collapse in">
                            <table class="table table-bordered sortableTable responsive-table">
                                <thead>
                                    <tr>
                                        <th align="left" width='5%'>Sr.No<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                        <th align="left" width='25%'>Item Name<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                        <th align="right" width='10%'>Balance<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                        <th align="right" width='10%'>Minimum Level<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                        <th align="right" width='10%'>Difference<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                  
                                    $gropen = 0;
                                    $grinw = 0;
                                    $grout = 0;
                                    $grret = 0;
                                    $grdam = 0;
                                    $grbal = 0;
                                    
                                    $ctr = 0;
                                    
                                    while ($row = mysqli_fetch_object($getitem)) {
                                        
                                        $icode = $row->code ;
                                        $iname = $row->name ;
                                        $imini = $row->mini ;
                                        

                                        $getopen = mysqli_query($conn, "SELECT sum(qty) as sqty FROM `$trandb` WHERE itemcode = '$icode'
                                                             AND `vouchdt` < '$mfrmdate'  $subcond ") or die(mysqli_error($conn));
                                        $iopen = mysqli_fetch_array($getopen);
                                        $openqty = $iopen['sqty'] ;
                                        
                                        $getinw = mysqli_query($conn, "SELECT sum(qty) as sqty FROM `$trandb` WHERE itemcode = '$icode'
                                                          AND `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and source = 'IN' $subcond ") or die(mysqli_error($conn));
                                        $iinw = mysqli_fetch_array($getinw);
                                        $inwqty = $iinw['sqty'] ;

                                        $getsr = mysqli_query($conn, "SELECT sum(qty) as sqty FROM `$trandb` WHERE itemcode = '$icode'
                                                          AND  `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and source = 'OU' $subcond ") or die(mysqli_error($conn));
                                        $isr = mysqli_fetch_array($getsr);
                                        $srqty = $isr['sqty'] ;
                                        
                                        $getsl = mysqli_query($conn, "SELECT sum(qty) as sqty FROM `$trandb` WHERE itemcode = '$icode'
                                                          AND  `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and source = 'RE' $subcond ") or die(mysqli_error($conn));
                                        $isl = mysqli_fetch_array($getsl);
                                        $slqty = $isl['sqty'] ;
                                        
                                        $getdam = mysqli_query($conn, "SELECT sum(qty) as sqty FROM `$trandb` WHERE itemcode = '$icode'
                                                           AND  `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and source = 'DM' $subcond ") or die(mysqli_error($conn));
                                        $idam = mysqli_fetch_array($getdam);
                                        $damqty = $idam['sqty'] ;
                                        
                                        $inwqty = abs($inwqty) ;
                                        $srqty  = abs($srqty) ;
                                        $slqty  = abs($slqty) ;
                                        $damqty = abs($damqty) ;
                                                
                                        $balqty = $openqty + $inwqty - $srqty + $slqty - $damqty;
                                        $diff = $balqty - $imini;                                         
                                        if ($balqty <= $imini) {
                                            $ctr++;
                                            echo "<tr> ";
                                            echo "<td align='left'>$ctr</td>";
                                            echo "<td align='left'>$iname</td>";
                                            echo "<td align='left'>$balqty</td>";
                                            echo "<td align='right'>$imini</td>";
                                            echo "<td align='right'>$diff</td>";
                                            echo "</tr>";
                                            
                                        }
                                    }    
                                    ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
                <!-- /.col-lg-6 -->
            </div>  

            <?php
            include("includes/files_footer.php");
            ?>
            <script>
                $(function() {
                    metisTable();
                    metisSortable();
                });
            </script>
        </body>
    </html>
   <?php
}// Submit Tag Close
?>