<?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 = 'Raw Material Stock';
$zdb1 = 'cepheisy_sis_zyc_1_1516';
$maindb = 'rawmat';
$trandb = 'matsacn';

$grid = 'stockrw.php';

if (isset($_POST["view"])) {
    $ncond = "";
    $gcond = "";
    $subcond = "";

    if (!empty($_POST["barcode"])) {
        $gbrcd = trim($_POST["barcode"]);
        $brcond = " and `barcode` = '$gbrcd' ";
    }

    if (!empty($_POST["name"])) {
        $gnm = $_POST["name"];
        $namequery = mysqli_query($conn, "SELECT * FROM `rawmat` WHERE `name`='$gnm'") or die(mysqli_error($conn));
        $getcdfrnm = mysqli_fetch_array($namequery);
        $nmfrcode = $getcdfrnm[code];
        $ncond = " and `itemcode` = '$nmfrcode' ";
    }

    if (!empty($_POST["rawmatgrp"])) {
        $gname = trim($_POST["rawmatgrp"]);

        $grpquery = mysqli_query($conn, "SELECT * FROM `itemgrp` WHERE code ='$gname'") or die(mysqli_error($conn));
        $getcdfrgp = mysqli_fetch_array($grpquery);
        $gpfrcode = $getcdfrgp[code];

        $gcond = " and `rawmatgrp` = '$gname' ";
    }

    if (!empty($_POST["godown"])) {
        $gdname = trim($_POST["godown"]);
        $gdcond = " and `godown` = '$gdname' ";
    }

//    if (!empty($_POST["barcode"])) {
//        $location = trim($_POST["barcode"]);
//        $subcond = " and `barcode` = '$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 `$trandb` WHERE itemcode != '' $gdcond $ncond $brcond group by itemcode,godown") or die(mysqli_error($conn));
    $getno = mysqli_num_rows($getitem);
    ?>
    <!DOCTYPE HTML>
    <html lang="en-US">

        <head>
            <?php
            include("includes/files.php");
            include("includes/tabsuf.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]</b><br />";
            echo "$zcomp[add1]. $zcomp[add2].  $zcomp[add3] </b></font><br />";
            echo "<font size='2px'>Date : " . date("d-m-Y") . "</b>&nbsp;&nbsp;";
            echo "( From Date - " . date("d-m-Y", strtotime($mfrmdate)) . "
                To Date &nbsp;&nbsp;&nbsp;&nbsp;-  " . date("d-m-Y", strtotime($mtodate)) . " )&nbsp;&nbsp;";
            echo "" . "Total Records - $getno";
            echo "</font>";
            ?>

            <div class="row">
                <div class="col-lg-6" style="width:100%;">
                    <div class="box">
                        <header style="border: 0px;">
                            <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" style="border:0px;">
                            <table id="t01" class="table table-bordered sortableTable responsive-table">
                                <thead>
                                    <tr>
                                        <th align="left" width='3%' style="font-family: arial;">Sr.</th>
                                        <!--<th align="left" width='5%'>Barcode</th>-->
                                        <th align="left" width='25%'>Raw Material</th>
                                        <th align="left" width='8%'>Godown</th>
                                        <th align="left" width='8%' style="text-align: right;">Opening<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                        <th align="left" width='8%' style="text-align: right;">Add<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                        <th align="right" width='8%' style="text-align: right;">Less<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                        <th align="left" width='8%' style="text-align: right;">Balance<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                    $gropen = 0;
                                    $grsale = 0;
                                    $grpur = 0;
                                    $grbal = 0;

                                    $ctr = 0;
                                    mysqli_select_db($conn, $zdb);
                                    while ($row = mysqli_fetch_object($getitem)) {
                                        $icode = $row->itemcode;
//                                        $ibarcode = $row->barcode;
                                        $igodown = $row->godown;
//                                        $iname = $row->name ;

                                        $getitnmgd = mysqli_query($conn, "SELECT * FROM `rawmat` where code = $icode") or die(mysqli_error($conn));
                                        $nmgd = mysqli_fetch_array($getitnmgd);

//                                        $getitnmgd = mysqli_query($conn, "SELECT * FROM $zdb1.`transacn` where itemcode = $icode") or die(mysqli_error($conn));
//                                        $nmgd = mysqli_fetch_array($getitnmgd); 

                                        $getgodown1 = mysqli_query($conn, "SELECT * FROM `godown` where code = $igodown") or die(mysqli_error($conn));
                                        $godwn1 = mysqli_fetch_array($getgodown1);
                                        mysqli_select_db($conn, $zdb);

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

                                        $getpur = mysqli_query($conn, "SELECT sum(qty) as sqty FROM `$trandb` WHERE  itemcode = '$icode' and godown = '$igodown' and
                                                          `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and qty > 0 $subcond ") or die(mysqli_error($conn));
                                        $ipur = mysqli_fetch_array($getpur);
                                        $purqty = $ipur['sqty'];


                                        $getsale = mysqli_query($conn, "SELECT sum(qty) as sqty FROM `$trandb` WHERE itemcode = '$icode' and godown = '$igodown' and
                                                          `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and qty < 0 $subcond ") or die(mysqli_error($conn));
                                        $isale = mysqli_fetch_array($getsale);
                                        $saleqty = $isale['sqty'];


                                        $saleqty = $saleqty * -1;

                                        $balqty = $openqty + $purqty - $saleqty;
                                        $gropen = $gropen + $openqty;
                                        $grsale = $grsale + $saleqty;
                                        $grpur = $grpur + $purqty;
                                        $grbal = $grbal + $balqty;
                                        $ctr++;
                                        echo "<tr> ";
                                        echo "<td align='left'>$ctr</td>";
//                                        echo "<td align='left'>$ibarcode</td>";
                                        echo "<td align='left'>$nmgd[name]</td>";
                                        echo "<td align='left'>$godwn1[gdcode]</td>";
                                        echo "<td align='right'>$openqty</td>";
                                        echo "<td align='right'>$purqty</td>";
                                        echo "<td align='right'>$saleqty</td>";
                                        echo "<td align='right'>$balqty</td>";
                                        echo "</tr>";
                                    }
                                    echo "<tr style ='background-color:white;'> ";
                                    echo "<td align='left'></td>";
                                    echo "<td align='left'></td>";
//                                    echo "<td align='left'></td>";
                                    echo "<td align='left'><font size='2px'> Total</font></td>";
                                    echo "<td align='right'>$gropen</td>";
                                    echo "<td align='right'>$grpur</td>";
                                    echo "<td align='right'>$grsale</font></td>";
                                    echo "<td align='right'>$grbal</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
?>