<?php
include('accesscheck2.php');
require_once('Connections/courier.php');
require_once 'functions/functions.php';

$change_cost_price = getMenuPermission('Change Cost Price');
$see_cost_price = getMenuPermission('Can View Cost Price');
$change_selling_price = getMenuPermission('Change Selling Price');

if (!function_exists("GetSQLValueString")) {

    function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") {
        if (PHP_VERSION < 6) {
            $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
        }

        $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);



        switch ($theType) {
            case "text":
                $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
                break;
            case "long":
            case "int":
                $theValue = ($theValue != "") ? intval($theValue) : "NULL";
                break;
            case "double":
                $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
                break;
            case "date":
                $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
                break;
            case "defined":
                $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
                break;
        }
        return $theValue;
    }

}

$action = $_POST['act'];

$qr_code = $_POST['searchItem'];
if ($qr_code){
    mysql_select_db($database_courier, $courier);
    $row_rsodest_check = "SELECT * FROM service_items WHERE ins_com_code = '$qr_code'";
    $query_check = mysql_query($row_rsodest_check, $courier);
    $getResult = mysql_fetch_assoc($query_check);
    $num_row = mysql_num_rows($query_check);
    if ($num_row > 0){
        $item_id = $getResult['id'];
        $item = $getResult['item'];
        $item_branch = $_SESSION['branch_name'];
         $item_branch1 = strtolower($item_branch);
        $item_qty = $getResult['quantity_available_'.$item_branch1];
        $tracking_no = 'stock'.date('y-m-d');
        $physical = '1';
        //echo $item_variation = $item_qty - $physical;
        $item_date = date('Y-m-d');
        $item_date1 = date('Y-m');


        /////////////////////// insert item into stock taking table //////////////////

        $row_rsodest_check1 = "SELECT * FROM stocktaking WHERE item_id = '$item_id' AND DATE_FORMAT(`date`,'%Y-%m') = '$item_date1' AND tracking_no = '$tracking_no'";//exit();
        $query_check1 = mysql_query($row_rsodest_check1, $courier);
        $getResult1 = mysql_fetch_assoc($query_check1);
      echo  $num_row1 = mysql_num_rows($query_check1);
        $stock_qty =$getResult1['physical_qty'];
        if ($num_row1 > 0){
             $stock_qty1 = $stock_qty + 1;
            $update = "UPDATE `stocktaking` SET  physical_qty = '$stock_qty1' WHERE item_id = '$item_id'";
            $execute = mysql_query($update, $courier);
        }else{
            $query_insert = "INSERT INTO `stocktaking`(`item`, `item_id`, `system_qty`, `physical_qty`, `tracking_no`, `branch`, `date`) VALUES ('$item', '$item_id', '$item_qty', '$physical','$tracking_no', '$item_branch', '$item_date')";
            $execute = mysql_query($query_insert, $courier);
        }

    }

}

function GetAdjustedQuantity($item_id,$tracking_no,$item_date){
    global $courier;
    $select = "SELECT * FROM stocktaking WHERE item_id = '$item_id' AND tracking_no = '$tracking_no' AND `date` = '$item_date'";
    $rsodest_select = mysql_query($select, $courier) or die(mysql_error());
    $item_quantity = mysql_fetch_assoc($rsodest_select);
    return $item_quantity['variation'];
}


function GetNewQuantity($item_id,$tracking_no,$item_date){
    global $courier;
    $SelectCheck = "SELECT * FROM stocktaking WHERE item_id = '$item_id' AND tracking_no = '$tracking_no' AND `date` = '$item_date'";
    $ExecuteCheck = mysql_query($SelectCheck, $courier);
    $NumberOfRows = mysql_num_rows($ExecuteCheck);
    $GetItem = mysql_fetch_assoc($ExecuteCheck);
    return $GetItem['system_qty'];
}

function GetVariation($item_id,$tracking_no,$item_date){
    global $courier;
    $SelectCheck = "SELECT * FROM stocktaking WHERE item_id = '$item_id' AND tracking_no = '$tracking_no' AND `date` = '$item_date'";
    $ExecuteCheck = mysql_query($SelectCheck, $courier);
    $NumberOfRows = mysql_num_rows($ExecuteCheck);
    $GetItem = mysql_fetch_assoc($ExecuteCheck);
   return $GetItem['physical_qty'] - $GetItem['system_qty'];
}


if($action == "adjustAdd_store"){
    /////////////////Get item from items table and Update the quantity ///////////////////////////
        $item_id = $_POST['id'];
        $quantity = $_POST['idd2'];
    $opening_stock_date12 = date('Y-m-d G:i');
        $SelectCheck123 = "SELECT * FROM stocktaking WHERE id = '$item_id'";
        $ExecuteCheck123 = mysql_query($SelectCheck123, $courier);
        $GetItem123 = mysql_fetch_assoc($ExecuteCheck123);
        ///////////////Get new quantity from reconciliation table /////////////////////////
        $reconcilationVariation = GetVariation($GetItem123['item_id'], $GetItem123['tracking_no'] ,$GetItem123['date']);

        /////////////// Check if the variation is a negative value and change it to positive for comparison ///////////////////
        if ($reconcilationVariation < 0) {
            $reconcilationVariation1 = $reconcilationVariation * -1;
        }else{
            $reconcilationVariation1 = $reconcilationVariation;
        }
        $reconcilationVariation1;

         if($quantity > $reconcilationVariation1 || $quantity < $reconcilationVariation1){
            echo "<div style='color:white; background-color:#e02222; font-size:16px;'><strong>Reconciliation Quantity cannot be more or less than variation</strong></div>";
         }else{
        /////////////Calculate new quantity available //////////////////////
             $SelectItem = "SELECT * FROM service_items WHERE id = '".$GetItem123['item_id']."'";
            $ExecuteItem = mysql_query($SelectItem, $courier);
            $NumberOfRowsItem = mysql_num_rows($ExecuteItem);
            $GetItem = mysql_fetch_assoc($ExecuteItem);
            $item_id = $GetItem['id'];
            $item = $GetItem['item'];
             $item_branch = $_SESSION['branch_name'];
             $item_branch1 = strtolower($item_branch);
            $old_qty = $GetItem['quantity_available_'.$item_branch1];

            if ($reconcilationVariation < 0){
                $new_available_quantity = $old_qty - $quantity;
                $adjustedType = "Sub";
            } else{
                $new_available_quantity =$quantity + $old_qty;
                $adjustedType = "Add";
            }


        $InsertRecon = "UPDATE `service_items` SET `quantity_available_$item_branch1`='$new_available_quantity',`quantity_onhand`='$new_available_quantity', `last_updated`='$opening_stock_date12' WHERE (id = '".$GetItem123['item_id']."')";
        $ExecuteInsertRecon = mysql_query($InsertRecon, $courier);

        $UpdateRecon = "UPDATE `stocktaking` SET `adjusted_quantity` = '$quantity',new_qty = '$new_available_quantity', variation = '$quantity' ,`adjustment_type`='$adjustedType' WHERE item_id = '".$GetItem123['item_id']."' AND tracking_no = '".$GetItem123['tracking_no']."' AND `date` = '".$GetItem123['date']."'";
        $ExecuteRecon = mysql_query($UpdateRecon, $courier);

        }


    //include_once('reconcile.php');

}



?>

<div class="page-bar">
    <ul class="page-breadcrumb">
        <li>
            <i class="icon-home"></i>
            <a href="index.html">Home</a>
            <i class="fa fa-angle-right"></i>
        </li>
        <li>
            <span>Dashboard</span>
        </li>
    </ul>
    <div class="page-toolbar">
        <div class="btn-group pull-right">
            <button type="button" class="btn btn-fit-height grey-salt dropdown-toggle" data-toggle="dropdown" data-hover="dropdown" data-delay="1000" data-close-others="true"> Actions
                <i class="fa fa-angle-down"></i>
            </button>
            <ul class="dropdown-menu pull-right" role="menu">
                <li onclick="getpage('<?php echo $action?>','<?php echo ($pagenum); ?>','<?php echo $totalRows_rsodest; ?>','<?php echo $row_rsodest['id']; ?>','<?php echo $typesearch; ?>','<?php echo $searchtxt; ?>');">
                    <a href="#">
                        <i class="icon-refresh"></i> Refresh
                    </a>
                </li>
                <li onclick="getstep('list_of_all_items_procurement');">
                    <a href="#">
                        <i class="fa fa-search"></i> Search For Items
                    </a>
                </li>
                <li onclick="">
                    <a href="#">
                        <i class="fa fa-print"></i> Print Stock Taking Report
                    </a>
                </li>
            </ul>
        </div>
    </div>
</div>


<div class="row" id="view_items_response_wrapper_2" style="display: none;">

</div>


<div class="row" id="">
    <div class="col-lg-12 col-md-12 col-xs-12 col-sm-12">
        <div class="portlet light ">
            <div class="portlet-title">
                <div class="caption">
                    <span class="caption-subject bold font-dark">Rx Stock Taking </span>
                </div>

            </div>
            <div class="portlet-body form">
                <div class="row"  >
                    <div class="col-lg-3 col-md-12 col-xs-12 col-sm-12">
                        <form onsubmit="getstep('stockTaking'); return false">
                            <input type="text" name="searchItem" id="searchItem" value="" autofocus>
                        </form>


                    </div>

                    <div class="col-lg-1 col-md-12 col-xs-12 col-sm-12">
                        <span style="background-color: #d9534f; width: 50px">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><br>Negative
                    </div>

                    <div class="col-lg-1 col-md-12 col-xs-12 col-sm-12">
                        <span style="background-color: #449d44; width: 50px">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><br>No Variance
                    </div>

                    <div class="col-lg-1 col-md-12 col-xs-12 col-sm-12">
                        <span style="background-color: #f0ad4e; width: 50px">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><br>Positive
                    </div>

                   <div class="col-lg-6 col-md-12 col-xs-12 col-sm-12" style="float: right">
                       <div class="col-lg-6 col-md-12 col-xs-12 col-sm-12">

                           <input type="date" name="stockreportDate" id="stockreportDate" style="float: right">
                       </div>

                       <div class="col-lg-6 col-md-12 col-xs-12 col-sm-12">
                           <input type="submit" class="btn btn-primary" name="" value="Print" id="" onclick="printStockReport('','')">
                       </div>
                   </div>

                </div>
            </div>
        </div>
    </div>
</div>

<div class="table-scrollable">
    <table class="table table-striped table-bordered table-hover dataTable no-footer" id="datatable_products" aria-describedby="datatable_products_info" role="grid">
        <thead>
        <tr>
            <th>ID</th>
            <th>ITEM NAME</th>
            <th>SYSTEM QTY BEFORE</th>
            <th>SYSTEM QTY NOW</th>
            <th>PHYSICAL QTY</th>
            <th>VARIATION</th>
            <th>BRANCH</th>
            <th>ADJUST </th>

        </tr>
        </thead>
        <tbody>
        <?php
        $count = 0;
        $currentMonth = date("Y-m");
        $row_rsodest_check = "SELECT * FROM stocktaking WHERE DATE_FORMAT(date,'%Y-%m') = '$currentMonth'";
        $query_check = mysql_query($row_rsodest_check, $courier);
        while ($row = mysql_fetch_assoc($query_check)){
            $count ++;

            $variation = $row['physical_qty'] - $row['system_qty'];

            ?>
        <tr>
            <td><?php echo $count ?></td>
            <td><?php echo $row['item'] ?></td>
            <td><?php echo $row['system_qty'] ?></td>
            <td><?php echo $row['new_qty'] ?></td>
            <td><?php echo $row['physical_qty'] ?></td>
            <td style="background-color: <?php if ($variation < 0){ echo "#d9534f";}else if ($variation > 0){echo "#f0ad4e";}else{ echo "#449d44";} ?> "><?php echo $variation ?></td>
            <td><?php echo $row['branch'] ?></td>
            <td>
                <input type="number"  name="adpjustplus" value="<?php echo GetAdjustedQuantity($row['item_id'], $row['tracking_no'] ,$row['date']) ?>" id="adpjustplus<?php echo $row['id'] ?>" style="width: 60px" onChange="getstep('adjustAdd_store', '<?php echo $row["id"]?>', this.value,'');" value="<?php echo GetAdjustedQuantity($item_id, $item_date,'Add') ?>
                ">
            </td>
        </tr>
        <?php } ?>
        </tbody>
    </table>
</div>
<script>
    $(document).ready(function() {
        focusbox = document.getElementById("searchItem");
        focusbox.focus();
    });

</script>
