In the Stocks menu, select the "View / Edit Stocks" sub menu. It is linked to "view_stocks.php" page / file. This page shows stocks of all items (items that has an stock record). Stocks table in the MySQL database records stocks details of products.
An item can have many stocks records. This table only records stock purchases. To get the stock balance we get the total of all stocks in's and total of stock out's. By getting the difference of total in and total out, we can get the balance stock.
This page has another php script for pagination. If there are hundreds of items, showing all in one page is difficult and takes long time to load, therefore we uses pagination process. Also included a search function to locate an item easily.
To edit a stock record, you should click on an item name. Item name is linked to "view_stock_items.php?itm=15" with an URL parameter "itm" having the value of item index (primary key). In this page shows all stock records of the particular item.
To edit a particular stock record, click on the "Edit" link. It is linked to "edit_stocks.php?id=1" with an URL parameter "id" with the value of stocks table record id (primary key).
There are three files involved in view and edit stocks process.
view_stocks.php file:
<?php include("inc/page_header.php"); ?>
<?php
if(check('Add Stocks') != 'Valid'){
header("Location: sys_home.php?pre=error");
ob_end_flush();
}
?>
<script type="text/javascript" src="ajax/show_items_js.js"></script>
<div class="wrapper">
<div class="s60">
<div class="boxtitle">View Stocks</div>
<table width="98%">
<tr id="headrow"><td> Item Code</td><td> Item Name</td><td> Normal Price Rs.</td><td> Stock Balance</td></tr>
<?php
if(isset($_GET['page'])){
$page = $_GET['page'];
}else{
$page = 0;
}
$q = mysql_query("SELECT Item_Code, Item_Part_No, Item_Name, Stock_Retail_Price, SUM(Stock_Qty) FROM stocks, items WHERE Stock_Item = Item_Code GROUP BY Stock_Item ORDER BY Stock_Item LIMIT $page, 500");
while($r = mysql_fetch_assoc($q)){
echo "<tr id='sh2'><td> {$r['Item_Part_No']}</td><td> <a href='view_stock_items.php?itm={$r['Item_Code']}'>{$r['Item_Name']}</a></td><td><div align='right'>{$r['Stock_Retail_Price']}</div></td><td><div align='right'>{$r['SUM(Stock_Qty)']}</div></td></tr>";
}
// number of rows in stock
$q2 = mysql_query("SELECT Item_Code, Item_Part_No, Item_Name, Stock_Retail_Price, SUM(Stock_Qty) FROM stocks, items WHERE Stock_Item = Item_Code GROUP BY Stock_Item ORDER BY Stock_Item");
$num_rows = mysql_num_rows($q2);
?>
</table>
<p class="txtright">« <?php if($page != 0){ echo "<a href='view_stocks.php?page=".($page-500)."'>Previous</a>"; }else{ echo "Previous"; } ?> | <?php if(($page+500) < $num_rows){ echo "<a href='view_stocks.php?page=".($page+500)."'>Next</a>"; }else{ echo "Next"; } ?> »</p>
</div>
<div class="s40">
<?php
if(isset($error)){ echo "<div class='errordiv'>{$error}</div>"; }
if(isset($noerror)){ echo "<div class='noerrordiv'>{$noerror}</div>"; }
?>
<p><strong>Search & Edit Stocks<br>
</strong>Use this form to search and edit stock details. Please enter Item Name to start searching.</p>
<form action="view_stock_items.php" method="post">
<input id="item_name" type="text" size="40" name="item_name" onKeyUp="showItems(this.value)" onFocus="showItemList()" onBlur="hideItemList()" /> <input id="submit_1" type="submit" name="submit_1" class="btn" value="»" /><br />
<div id="item_list" style="position:absolute; z-index:2; width:264px; background-color:#FFFFCC; border:1px solid #999999; overflow:scroll; min-height:1px; visibility:hidden; "></div>
</form>
</div>
</div>
<?php include("inc/page_footer.php"); ?>
view_stock_items.php file:
<?php include("inc/page_header.php"); ?>
<?php
if(check('Add Stocks') != 'Valid'){
header("Location: sys_home.php?pre=error");
ob_end_flush();
}
?>
<script type="text/javascript" src="ajax/show_items_js.js"></script>
<div class="wrapper">
<div class="s60">
<div class="boxtitle">View Stocks</div>
<?php
if(isset($_GET['itm'])){
$item_code = $_GET['itm'];
$q = mysql_query("SELECT Item_Code, Item_Part_No, Item_Name, Stock_Date, Stock_Retail_Price, Stock_Qty FROM stocks, items WHERE Stock_Item = Item_Code AND Item_Code = '$item_code'");
}else{
$item_name = $_POST['item_name'];
$q = mysql_query("SELECT Item_Code, Item_Part_No, Item_Name, Stock_Date, Stock_Retail_Price, Stock_Qty FROM stocks, items WHERE Stock_Item = Item_Code AND Item_Name = '$item_name'");
}
$r = mysql_fetch_assoc($q);
$item_code = $r['Item_Code'];
?>
<table width="98%" style="border:2px solid #0000CC ">
<tr id="sh4"><td width="100px"> <strong>Item No:</strong></td><td> <?php echo $r['Item_Part_No']; ?></td></tr>
<tr id="sh3"><td width="100px"> <strong>Item Name:</strong></td><td> <?php echo $r['Item_Name']; ?></td></tr>
</table>
<table width="98%">
<tr id="headrow"><td> Date</td><td> Purchase Price Rs.</td><td> Retail Price Rs.</td><td> Stock Balance</td><td> Actions</td></tr>
<?php
$q = mysql_query("SELECT Stock_ID, Stock_Date, Stock_Purchase_Price, Stock_Retail_Price, Stock_Qty FROM stocks, items WHERE Stock_Item = Item_Code AND Item_Code = '$item_code'");
while($r = mysql_fetch_assoc($q)){
echo "<tr id='sh2'><td> {$r['Stock_Date']}</td><td><div align='right'>{$r['Stock_Purchase_Price']}</div></td><td><div align='right'>{$r['Stock_Retail_Price']}</div></td><td><div align='right'>{$r['Stock_Qty']}</div></td><td> <a href='edit_stocks.php?id={$r['Stock_ID']}'>Edit</a></td></tr>";
}
?>
</table>
</div>
<div class="s40">
<?php
if(isset($error)){ echo "<div class='errordiv'>{$error}</div>"; }
if(isset($noerror)){ echo "<div class='noerrordiv'>{$noerror}</div>"; }
?>
<p><strong>Search & Edit Stocks<br>
</strong>Use this form to search and edit stock details. Please enter Item Name to start searching.</p>
<form action="view_stock_items.php" method="post">
<input id="item_name" type="text" size="40" name="item_name" onKeyUp="showItems(this.value)" onFocus="showItemList()" onBlur="hideItemList()" /> <input id="submit_1" type="submit" name="submit_1" class="btn" value="»" /><br />
<div id="item_list" style="position:absolute; z-index:2; width:264px; background-color:#FFFFCC; border:1px solid #999999; overflow:scroll; min-height:1px; visibility:hidden; "></div>
</form>
</div>
</div>
<?php include("inc/page_footer.php"); ?>
edit_stocks.php file:
<?php include("inc/page_header.php"); ?>
<script type="text/javascript">
function getDisprice(dis){
var normal_price = document.getElementById("ret_price").value;
var discounted_price = (normal_price/100)*(100-dis);
document.getElementById("pur_price").value = discounted_price.toFixed(2);
}
</script>
<?php
if(check('Add Stocks') != 'Valid'){
header("Location: sys_home.php?pre=error");
ob_end_flush();
}
// get url parameters
$stock_id = $_GET['id'];
// read stock data from the database
$q = mysql_query("SELECT * FROM stocks, items WHERE Stock_Item = Item_Code AND Stock_ID = '$stock_id'");
$r = mysql_fetch_assoc($q);
$item_code = $r['Item_Code'];
$part_no = $r['Item_Part_No'];
$item_name = $r['Item_Name'];
$pur_price = $r['Stock_Purchase_Price'];
$ret_price = $r['Stock_Retail_Price'];
$qty = $r['Stock_Qty'];
$stock_discount = $r['Stock_Discount'];
/* +++++++++++++++ Form 2 Handling ++++++++++++++++++++++++++++++ */
if(isset($_POST['submit_2'])){
$pur_price = $_POST['pur_price'];
$ret_price = $_POST['ret_price'];
$qty = $_POST['qty'];
$stock_discount = $_POST['discount'];
// first check for existing record except current record for the same purchase price
$q = mysql_query("SELECT * FROM stocks WHERE Stock_Item = '$item_code' AND Stock_Purchase_Price = '$pur_price' AND Stock_ID != '$stock_id'");
if(mysql_num_rows($q) > 0){
// update the existing stock record
// get the curent Stock ID
$r = mysql_fetch_assoc($q);
$new_stock_id = $r['Stock_ID'];
mysql_query("UPDATE stocks SET Stock_Date = '$today', Stock_Purchase_Price = '$pur_price', Stock_Qty = (Stock_Qty + $qty), Stock_Discount = '$stock_discount' WHERE Stock_ID = '$new_stock_id'");
// now update the retail price of all stock records for the current item
mysql_query("UPDATE stocks SET Stock_Retail_Price = '$ret_price' WHERE Stock_Item = '$item_code'");
// delete the old stock record
mysql_query("DELETE FROM stocks WHERE Stock_ID = '$stock_id'");
$noerror = 'Stock has been updated!';
}else{
// Update the same stock
mysql_query("UPDATE stocks SET Stock_Date = '$today', Stock_Purchase_Price = '$pur_price', Stock_Qty = '$qty', Stock_Discount = '$stock_discount' WHERE Stock_ID = '$stock_id'");
// now update the retail price of all stock records for the current item
mysql_query("UPDATE stocks SET Stock_Retail_Price = '$ret_price' WHERE Stock_Item = '$item_code'");
$noerror = 'Stock has been updated!';
}
}
/* +++++++++++++++ // Form 2 Handling ++++++++++++++++++++++++++++++ */
?>
<script type="text/javascript" src="ajax/show_items_js.js"></script>
<div class="wrapper">
<div class="s60">
<div class="boxtitle">Edit Stocks</div>
<form action="<?php $_SERVER['PHP_SELF']; ?>" method="post">
<table width="100%">
<tr id="sh6"><td> Item Code:</td><td><input type="text" size="8" value="<?php echo $item_code; ?>" disabled /></td></tr>
<tr><td> Part No:</td><td><input type="text" size="20" value="<?php echo $part_no; ?>" disabled /></td></tr>
<tr id="sh6"><td> Item Name:</td><td><input type="text" size="40" value="<?php echo $item_name; ?>" disabled /></td></tr>
<tr><td> Normal Price Rs.:</td><td><input type="text" id="ret_price" name="ret_price" size="10" value="<?php echo $ret_price; ?>" /> *</td></tr>
<tr id="sh6"><td> Discount %:</td><td><input type="text" id="discount" onKeyup="getDisprice(this.value)" name="discount" size="10" value="<?php echo $stock_discount; ?>" /></td></tr>
<tr id="sh6"><td> Discounted Price Rs.:</td><td><input type="text" id="pur_price" name="pur_price" size="10" value="<?php echo $pur_price; ?>" /> *</td></tr>
<tr id="sh6"><td> Qty:</td><td><input type="text" name="qty" size="10" value="<?php echo $qty; ?>" /> *</td></tr>
<tr><td> </td><td><input type="submit" name="submit_2" value="Update Stock" class="btn" /></td></tr>
</table>
</form>
</div>
<div class="s40">
<p><strong>Edit Stocks<br>
</strong>Please read thefollowing instructions before editing stocks.</p>
<?php
if(isset($error)){ echo "<div class='errordiv'>{$error}</div>"; }
if(isset($noerror)){ echo "<div class='noerrordiv'>{$noerror}</div>"; }
?>
</div>
</div>
<?php include("inc/page_footer.php"); ?>
Comments
Post a Comment