Issuing stocks to sites handled by "issue_stocks.php" file. First need to select the site and issue items by searching by name or code. This process involves several other files as well.
Here is the link to download the full project source code and database. Complete project folder file included.
Click to Download the Project Directory
- issue_stocks.php (to search and add items to the issue list)
- func/close_sale.php (to save details to the database)
- func/print_invoice.php (to print issue note)
- js/scripts.js (to transport data from issue_stocks.php file to close_sale.php file)
Some file names are not appropriate according to their nature of operations. Because this system was derived from another invoicing system and as a result of code reuse re-naming of file not done correctly. This is a bad coding practice. It is no a issue as long as the project is managed by the developer itself. But when you want to refer the project for another person, this becomes very un-organized thing.
Printing of site dispatch note is handled by a pop-up window and since most browsers block pop-ups it is recommends to avoid pop-up java scripts and instead use new window or tab scripts.
Source code of "issue_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();
}
/* +++++++++++++++ Form 1 (Submit 1) Handling ++++++++++++++++++++++ */
if(isset($_POST['submit_1'])){
$item_code = $_POST['item_code'];
$part_no = $_POST['part_no'];
$item_name = $_POST['item_name'];
if($item_code == '' && $part_no == '' && $item_name == ''){
$error = 'Please enter Item Code OR Part No OR Item Name to start!';
}else{
if($item_code != ''){
// search by Item Code
$q = mysql_query("SELECT * FROM items WHERE Item_Code = '$item_code'");
}else if($part_no != ''){
// search by Part No
$q = mysql_query("SELECT * FROM items WHERE Item_Part_No = '$part_no'");
}else{
// search by Item Name
$q = mysql_query("SELECT * FROM items WHERE Item_Name = '$item_name'");
}
// check item found in the inventory
if(mysql_num_rows($q) >0){
// item found and set variables for item code, part no and item name
$r = mysql_fetch_assoc($q);
$itemcode = $r['Item_Code'];
$partno = $r['Item_Part_No'];
$itemname = $r['Item_Name'];
// get the current stock details
$q = mysql_query("SELECT SUM(Stock_Qty), Stock_Purchase_Price, Stock_Retail_Price FROM stocks WHERE Stock_Item = '$itemcode' ORDER BY Stock_ID DESC");
$r = mysql_fetch_assoc($q);
$current_stock = $r['SUM(Stock_Qty)'];
$purchase_price = $r['Stock_Purchase_Price'];
$retail_price = $r['Stock_Retail_Price'];
}else{
// item not found and create error message
$error = 'Item not found in the inventory!';
}
}
}
/* +++++++++++++++ // Form 1 Handling ++++++++++++++++++++++++++++++ */
/* +++++++++++++++ Form 2 Handling ++++++++++++++++++++++++++++++ */
if(isset($_POST['submit_2'])){
$item_code = $_POST['item_code'];
$qty = $_POST['qty'];
$part_no = $_POST['part_no'];
$issuedto = $_POST['issuedto'];
// validate Qty
if($qty == '' || $qty == 0){
$error = 'Please enter a valid Qty!';
}else if($issuedto == 0){
$error = 'Please select which part you want to issue this item!';
}else{
// check the stock balance for the given qty
$q = mysql_query("SELECT SUM(Stock_Qty) FROM stocks WHERE Stock_Item = '$item_code'");
$r = mysql_fetch_assoc($q);
$stock_balance = $r['SUM(Stock_Qty)'];
if($stock_balance >= $qty){
// issue stock
// select all stock records for this item
$q = mysql_query("SELECT Stock_ID, Stock_Purchase_Price, Stock_Retail_Price, Stock_Qty FROM stocks WHERE Stock_Item = '$item_code' ORDER BY Stock_ID");
while($r = mysql_fetch_assoc($q)){
$stock_id = $r['Stock_ID'];
$stock_qty = $r['Stock_Qty'];
$pur_price = $r['Stock_Purchase_Price'];
$ret_price = $r['Stock_Retail_Price'];
if($stock_qty >= $qty){
// then issue all the stock from this stock record and update qty as zero for other recursives or try break
// update temp table
mysql_query("INSERT INTO temp (Temp_Stock_ID, Temp_Item, Temp_Item_Part_No, Temp_Qty, Temp_Purchase_Price, Temp_Retail_Price, Temp_SG_ID) VALUES ('$stock_id', '$item_code', '$part_no', '$qty', '$pur_price', '$ret_price', '$issuedto')");
// update current stock record, deduct qty
mysql_query("UPDATE stocks SET Stock_Qty = (Stock_Qty - $qty) WHERE Stock_ID = '$stock_id'");
// update qty as zero
$qty = '0';
break;
}else{
// issue all in this stock record and update qty for the next record
// update temp table
mysql_query("INSERT INTO temp (Temp_Stock_ID, Temp_Item, Temp_Item_Part_No, Temp_Qty, Temp_Purchase_Price, Temp_Retail_Price, Temp_SG_ID) VALUES ('$stock_id', '$item_code', '$part_no', '$stock_qty', '$pur_price', '$ret_price', '$issuedto')");
// update current stock record, set to zero
mysql_query("UPDATE stocks SET Stock_Qty = '0' WHERE Stock_ID = '$stock_id'");
// new qty value
$qty = $qty - $stock_qty;
}
}
$noerror = 'Stock has been issued!';
}else{
$error = 'There are not enough stocks to issue this quantity!';
}
}
// variables for form 2 display values
$itemname = $_POST['item_name'];
$itemcode = $_POST['item_code'];
$partno = $_POST['part_no'];
$current_stock = $_POST['current_stock'];
$retail_price = $_POST['retail_price'];
}
/* +++++++++++++++ // Form 2 Handling ++++++++++++++++++++++++++++++ */
?>
<script type="text/javascript" src="ajax/show_items_js.js"></script>
<script type="text/javascript" src="js/scripts.js"></script>
<div class="wrapper">
<div class="s60">
<div class="boxtitle">Issue Stocks</div>
<?php if(!isset($_GET['customer']) || $_GET['customer'] == 0){ // visibility settings ?>
<?php
if(isset($_GET['customer'])){
if($_GET['customer'] == 0){
$error = "Please select a site form the list!";
}
}
?>
<p>Please select a site from the list.</p>
<form method="get" action="issue_stocks.php">
<select name="customer" size="1">
<option value="0" selected>Please select...</option>
<?php
$q = mysql_query("SELECT * FROM customers ORDER BY Customer_Name");
while($r = mysql_fetch_assoc($q)){
echo "<option value='{$r['Customer_ID']}'>{$r['Customer_Name']}</option> \n";
}
?>
</select> <input class="btn" type="submit" name="submit_1" value="Next »" />
</form>
<?php }else{ ?>
<p>Please enter Item Code OR Item Name.</p>
<div style="border:2px solid #6484F0; width:90%; margin:10px auto 10px auto; padding:5px; ">
<form action="<?php $_SERVER['PHP_SELF']; ?>" method="post">
<table>
<tr><td>Item Code:</td><td colspan="3"><input type="text" size="21" name="part_no" /></td></tr>
<tr><td>Item Name:</td><td colspan="3"><input id="item_name" type="text" size="45" 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:294px; background-color:#FFFFCC; border:1px solid #999999; overflow:scroll; min-height:1px; visibility:hidden; "></div>
</td></tr>
</table>
<input type="hidden" size="8" name="item_code" />
</form>
</div>
<script type="text/javascript">
function newSeg(){
var catname=prompt("New Segment Name: ");
if(catname!=null && catname!=""){
window.location="func/new_segment.php?str="+catname;
}
}
</script>
<?php if(isset($_POST['submit_1']) || (isset($_POST['submit_2']) && isset($error))){ // control the visibility of form 2 ?>
<div style="border:2px solid #6484F0; width:90%; margin:10px auto 10px auto; padding:5px; ">
<form action="<?php $_SERVER['PHP_SELF']; ?>" method="post">
<table width="100%">
<tr id="sh5"><td> <b>Item Name:</b></td><td colspan="3"><input type="text" size="40" value="<?php echo $itemname; ?>" disabled /></td></tr>
<tr id="sh3"><td> <b>Qty</b></td><td> <b>Item Code:</b></td><td> <b>Current Stock:</b></td><td> <b>Retail Price Rs.:</b></td></tr>
<tr id="sh3"><td><input type="text" name="qty" size="10" /> *</td>
<td><input type="text" size="20" value="<?php echo $partno; ?>" disabled /></td>
<td><input type="text" size="10" value="<?php echo $current_stock; ?>" disabled /></td>
<td><input type="text" name="ret_price" size="10" value="<?php echo $retail_price; ?>" disabled /></td></tr>
<tr id="sh5"><td> <b>Issued to:</b></td><td colspan="2">
<select name="issuedto" size="1">
<option value="0" selected>Please select...</option>
<?php
$q = mysql_query("SELECT * FROM site_segments");
while($r = mysql_fetch_assoc($q)){
echo "<option value=\"{$r['SG_ID']}\">{$r['SG_Name']}</option>";
}
?>
</select> <a href="javascript: newSeg()">New Segment</a></td>
<td><input type="submit" name="submit_2" value="Issue Stock" class="btn" /></td></tr>
</table>
<input type="hidden" size="8" value="<?php echo $itemcode; ?>" disabled />
<input type="hidden" name="item_code" value="<?php echo $itemcode; ?>" />
<input type="hidden" name="part_no" value="<?php echo $partno; ?>" />
<input type="hidden" name="item_name" value="<?php echo $itemname; ?>" />
<input type="hidden" name="retail_price" value="<?php echo $retail_price; ?>" />
<input type="hidden" name="current_stock" value="<?php echo $current_stock; ?>" />
</form>
</div>
<?php } // end of form 2 visibility control ?>
<table width="98%" bgcolor="#CCCCFF">
<tr id="headrow"><td> Item Code</td><td> Item Name</td><td> Price Rs.</td><td> Qty</td><td> Total Rs.</td><td> </td></tr>
<?php
$q = mysql_query("SELECT Temp_Item, Temp_Item_Part_No, Item_Name, Temp_Retail_Price, SUM(Temp_Qty) FROM temp, items WHERE Temp_Item = Item_Code GROUP BY Temp_Item ORDER BY Temp_ID");
$gross_value = 0;
while($r = mysql_fetch_assoc($q)){
$i_code = $r['Temp_Item'];
$i_no = $r['Temp_Item_Part_No'];
$i_name = $r['Item_Name'];
$i_price = $r['Temp_Retail_Price'];
$i_qty = $r['SUM(Temp_Qty)'];
$i_total = $i_price * $i_qty;
$customer_id = $_GET['customer'];
echo "<tr id='sh0'>
<td>{$i_no}</td>
<td>{$i_name}</td><td><div align='right'>{$i_price}</div></td>
<td><div align='right'>{$i_qty}</div></td>
<td><div align='right'>".number_format($i_total,2,'.','')."</div></td>
<td><div align='right'><a href='func/remove_stock_issue.php?itm={$i_code}&cus={$customer_id}'>Remove</a></div></td>
</tr>";
$gross_value += $i_total;
}
?>
</table>
<?php } // end of visibility settings ?>
</div>
<div class="s40">
<?php
if(isset($error)){ echo "<div class='errordiv'>{$error}</div>"; }
if(isset($noerror)){ echo "<div class='noerrordiv'>{$noerror}</div>"; }
// display customer details
if(isset($_GET['customer']) && $_GET['customer'] != 0){
$customer_id = $_GET['customer'];
echo "<p>You are now issuing stocks for the following site.</p>";
$q = mysql_query("SELECT * FROM customers WHERE Customer_ID = '$customer_id'");
$r = mysql_fetch_assoc($q);
?>
<table width="98%">
<tr id="sh0"><td> <b>Name:</b></td><td> <?php echo $r['Customer_Name']; ?></td></tr>
<tr id="sh0"><td> <b>Address:</b></td><td> <?php echo $r['Customer_Address'].", ".$r['Customer_Address_2'].", ".$r['Customer_City']; ?></td></tr>
<tr id="sh0"><td> <b>Telephone:</b></td><td> <?php echo $r['Customer_Telephone']; ?></td></tr>
<tr id="sh0"><td> <b>Fax:</b></td><td> <?php echo $r['Customer_Fax']; ?></td></tr>
<tr id="sh0"><td> <b>Email:</b></td><td> <?php echo $r['Customer_Email']; ?></td></tr>
<tr id="sh0"><td> <b>Web:</b></td><td> <?php echo $r['Customer_Web']; ?></td></tr>
<tr id="sh0"><td valign="top"> <b>Comments:</b></td><td> <?php echo $r['Customer_Comments']; ?></td></tr>
</table>
<p> </p>
<table width="97%" bgcolor="#FFFF99">
<tr id="sh0"><td><p style="font-size:16px; font-weight:bold; ">Value Rs.</p></td><td><div align="right"><input type="text" id="gross_value" style="font-size:16px; font-weight:bold; text-align:right; " value="<?php echo number_format($gross_value,2,'.',''); ?>" size="14" readonly="true" /></div></td></tr>
<!--<tr id="sh0"><td><p style="font-size:12px; font-weight:bold; ">Discounts %</p></td><td><div align="right"><input type="text" size="10" id="discount" name="discount" value="0" onKeyUp="getDiscount(this.value)" /></div></td></tr>
<tr id="sh0"><td><p style="font-size:18px; font-weight:bold; color:#990000; ">Net Value Rs.</p></td><td><div align="right"><input type="text" size="12" id="net_value" style="font-size:18px; font-weight:bold; color:#990000; text-align:right; " value="<?php echo number_format($gross_value,2,'.',''); ?>" readonly="true" /></div></td></tr>
<tr id="sh0"><td><p style="font-size:16px; font-weight:bold; color:#006600; ">Payments Rs.</p></td><td><div align="right"><input type="text" size="12" id="payment" style="font-size:16px; font-weight:bold; color:#006600; text-align:right; " value="0.00" /></div></td></tr>-->
</table>
<div align="center" style="margin-top:20px; "><input type="button" value="Transfer Note" style="background-color:#990000; color:#FFFFFF; border:2px solid #660000; font-size:16px; " onClick="closeSale()" /></div>
<input type="hidden" id="customer" value="<?php echo $customer_id; ?>" />
<?php
} // end of IF display customer
?>
</div>
</div>
<?php include("inc/page_footer.php"); ?>
Source code of "func/close_sale.php" file
<script type="text/javascript">
<!-- Please enable javascripts
function newPopup(url){
popUpWindow = window.open(url,'popUpWindow','height=700,width=650,left=10,top=10,resizable=yes,scrollbars=yes,toolbar=no,menubar=no,location=no,directories=no,status=no')
}
-->
</script>
<?php
include("../inc/db_connect.php");
$customer_id = $_GET['cus'];
$discount = $_GET['dis'];
$payment = 0; //$_GET['pay'];
$date = date('Y-m-d');
// create a new sales record
mysql_query("INSERT INTO sales (Sale_Date, Sale_Customer, Sale_Payment, Sale_Discount) VALUES ('$date', '$customer_id', '$payment', '$discount')");
// get the created sales id
$q = mysql_query("SELECT MAX(Sale_ID) FROM sales");
$r = mysql_fetch_assoc($q);
$sale_id = $r['MAX(Sale_ID)'];
// read from temp
$q = mysql_query("SELECT Temp_Item, Temp_Item_Part_No, SUM(Temp_Qty), Temp_Purchase_Price, Temp_Retail_Price, Temp_SG_ID FROM temp GROUP BY Temp_Item");
$gross = 0;
$profit = 0;
while($r = mysql_fetch_assoc($q)){
$item_code = $r['Temp_Item'];
$part_no = $r['Temp_Item_Part_No'];
$qty = $r['SUM(Temp_Qty)'];
$pur_price = $r['Temp_Purchase_Price'];
$ret_price = $r['Temp_Retail_Price'];
$segment = $r['Temp_SG_ID'];
// insert into sales items
mysql_query("INSERT INTO sales_items (SI_Sale_ID, SI_Item, SI_Item_Part_No, SI_Qty, SI_Purchase_Price, SI_Retail_Price, SI_SG_ID) VALUES
('$sale_id', '$item_code', '$part_no', '$qty', '$pur_price', '$ret_price', '$segment')");
$gross += $ret_price*$qty;
$profit += ($ret_price - $pur_price)*$qty;
}
$net = ($gross/100)*(100-$discount);
$discount = ($gross/100)*$discount;
$profit = $profit-$discount;
// update the sale record
mysql_query("UPDATE sales SET Gross_Value = '$gross', Net_Value = '$net', Profit = '$profit' WHERE Sale_ID = '$sale_id'");
// empty the temp table
mysql_query("TRUNCATE temp");
// open the print invoice window
echo "<script type=\"text/javascript\"> \n";
echo "newPopup(\"print_invoice.php?sid={$sale_id}\"); \n";
echo "var t = setTimeout(\"window.location='../issue_stocks.php'\",1000); \n";
echo "</script>";
mysql_close($conn);
?>
Source code of "print_invoice.php" file
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Invoice</title>
<link rel="stylesheet" type="text/css" href="../inc/page_style.css" />
</head>
<body style="font-size:11px; background-color:#FFFFFF; background-image:none; ">
<?php include("../inc/printer.php"); ?>
<?php include("../inc/db_connect.php"); ?>
<?php
$sale_id = $_GET['sid'];
// get customer details
$q = mysql_query("SELECT * FROM sales WHERE Sale_ID = '$sale_id'");
$r = mysql_fetch_assoc($q);
$customer_id = $r['Sale_Customer'];
$discount = $r['Sale_Discount'];
$q = mysql_query("SELECT * FROM customers WHERE Customer_ID = '$customer_id'");
$r = mysql_fetch_assoc($q);
$customer_name = $r['Customer_Title'].', '.$r['Customer_Name'];
$customer_address = $r['Customer_Address'].', '.$r['Customer_Address_2'].', '.$r['Customer_City'];
//$customer_telephone = $r['Customer_Telephone'];
?>
<table border="0" width="100%">
<tr>
<td width="70%">
<table border="0">
<tr><td><strong>Customer / Site:</strong></td><td><?php echo $customer_name; ?></td></tr>
<tr><td><strong>Address:</strong></td><td><?php echo $customer_address; ?></td></tr>
</table>
</td>
<td width="30%">
<table border="0" width="95%">
<tr><td><div align="right"><strong>Date:</strong> <?php echo date('Y-m-d'); ?></div></td></tr>
<tr><td><div align="right"><strong>TN No:</strong> <?php echo $sale_id; ?></div></td></tr>
</table>
</td>
</tr>
</table>
<h1 style="text-align:center; background-color:#666666; color:#FFFFFF;">SITE DISPATCH NOTE</h1>
<table align="center" width="98%" border="1px" cellpadding="0" cellspacing="0" style="border-collapse:collapse; border:1px solid #333333;">
<tr style="background-color:#666666; color:#FFFFFF; font-weight:bold; "><td> Part No.</td><td> Item Name</td><td> Qty</td></tr>
<?php
$q = mysql_query("SELECT * FROM sales_items, items WHERE SI_Item = Item_Code AND SI_Sale_ID = '$sale_id'");
$gross = 0;
while($r = mysql_fetch_assoc($q)){
$part_no = $r['Item_Part_No'];
$item_name = $r['Item_Name'];
$qty = $r['SI_Qty'];
$price = $r['SI_Retail_Price'];
$total = $qty * $price;
$gross += $total;
echo "<tr>
<td> {$part_no}</td>
<td> {$item_name}</td>
<td><div align='right'> {$qty}</div></td>
</tr>";
}
?>
</table>
<table align="center" width="98%" border="1px" cellpadding="0" cellspacing="0" style="border-collapse:collapse; border:1px solid #333333;">
<tr>
<td align="center"><br /><br /><br />........................................................<br />Prepared By</td>
<td align="center"><br /><br /><br />........................................................<br />Checked By</td>
<td align="center"><br /><br /><br />........................................................<br />Approved By</td>
</tr>
</table>
<?php mysql_close($conn); ?>
</body>
</html>
Source code of "js/scripts.js" file
// JavaScript Document
function getDiscount(x){
var gross_value = document.getElementById('gross_value').value;
var net_value = (gross_value/100)*(100-x);
document.getElementById('net_value').value = net_value.toFixed(2);
}
function closeSale(){
//var discount = document.getElementById('discount').value;
var customer = document.getElementById('customer').value;
//var payment = document.getElementById('payment').value;
window.location = 'func/close_sale.php?cus='+customer+'&dis=0'; //+'&pay='+payment;
}
Comments
Post a Comment