Sale Items messed up in an inventory system

needs_upgrade · April 27, 2011

Hello guys!

I made a point of sale / inventory system. My problem is that there are instances when users enter sales and press the submit button at the same time (or almost at the same time), the items contained within those sales get interchanged.

For example items a, b and c are supposed to be contained in sale #1 and items d, e and f in sale #2. but since the submit buttons were pressed at the same time, sale #1 contains items a, b and e and sale #2 contains c, d and f.

What could be the possible reasons? I suspect that the LAN wires has something to do with it. Somebody told me that i should consider having a queueing function. How should i do that?

Any insight is highly appreciated. Thanks so much.

PFMaBiSmAd · April 27, 2011

You either haven't associated the data from each different sales transaction together with a unique identifier or you assumed something that isn't true (such as exclusive access to database values) when a race condition exists.

It would take seeing all your code necessary to reproduce the problem, knowing how you are identifying what information comes from each sale transaction, knowing how you are storing the information, and exactly what result or symptom you are getting that leads you to believe sales transactions are getting inter-mixed for any one to have a chance at actually helping you with what is causing the problem.

needs_upgrade · April 28, 2011

Thanks PFMaBiSmAd for your reply. Please take a look at my tables and my code.

Tables:

CREATE TABLE IF NOT EXISTS `purchases` (
  `purchase_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `or_num` varchar(10) COLLATE latin1_general_ci NOT NULL,
  `supplier_id` int(4) unsigned NOT NULL DEFAULT '0',
  `user_id` int(2) unsigned NOT NULL DEFAULT '0',
  `delivery_date` date NOT NULL DEFAULT '0000-00-00',
  `received_by` int(2) NOT NULL,
  `notes` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `line_total` decimal(13,2) unsigned NOT NULL DEFAULT '0.00',
  `balance` decimal(13,2) unsigned NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`purchase_id`)
);

CREATE TABLE IF NOT EXISTS `purchase_details` (
  `purchase_detail_id` int(15) unsigned NOT NULL AUTO_INCREMENT,
  `purchase_id` int(10) unsigned NOT NULL,
  `product_id` int(5) unsigned NOT NULL,
  `quantity` int(6) unsigned NOT NULL,
  `unit_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.000',
  `unit_discount` decimal(8,2) unsigned NOT NULL DEFAULT '0.000',
  `net_price` decimal(13,2) NOT NULL,
  `balance` int(6) NOT NULL,
  PRIMARY KEY (`purchase_detail_id`)
);

CREATE TABLE IF NOT EXISTS `sales` (
  `sale_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `or_num` varchar(10) COLLATE latin1_general_ci NOT NULL,
  `customer_id` int(4) unsigned NOT NULL DEFAULT '0',
  `user_id` int(2) unsigned NOT NULL DEFAULT '0',
  `delivery_date` date DEFAULT '0000-00-00',
  `due_date` date NOT NULL,
  `received_by` varchar(150) COLLATE latin1_general_ci NOT NULL,
  `notes` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `line_total` decimal(13,2) unsigned NOT NULL DEFAULT '0.00',
  `balance` decimal(13,2) unsigned NOT NULL DEFAULT '0.00',
  `revenue` decimal(10,2) NOT NULL,
  PRIMARY KEY (`sale_id`)
);

CREATE TABLE IF NOT EXISTS `sale_details` (
  `sale_detail_id` int(15) unsigned NOT NULL AUTO_INCREMENT,
  `sale_id` int(10) unsigned NOT NULL,
  `purchase_detail_id` int(15) unsigned NOT NULL,
  `product_id` int(5) unsigned NOT NULL,
  `quantity` int(6) unsigned NOT NULL,
  `issued` int(6) NOT NULL COMMENT 'actual issued quantity',
  `unit_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.000',
  `unit_discount` decimal(8,2) unsigned NOT NULL DEFAULT '0.000',
  `net_price` decimal(13,2) NOT NULL,
  PRIMARY KEY (`sale_detail_id`)
);

PHP Code:

<?PHP

if(isset($_POST['submit'])) { // if submit button has been pressed
$sale_id = mysql_real_escape_string($_POST['sale_id']);
$customer_id = mysql_real_escape_string($_POST['customer_id']);
$received_by = mysql_real_escape_string($_POST['received_by']);
$notes = mysql_real_escape_string($_POST['notes']);
$due_date = mysql_real_escape_string($_POST['due_date']);


if ($customer_id != "") { $a = TRUE; } 
else { $a = FALSE; $message[] = "Please select from the list of customers."; }
if ($received_by != "") { $b = TRUE; } 
else { $b = FALSE; $message[] = "Please enter the person who received the product sale."; }
if ($due_date != "") { $c = TRUE; } 
else { $c = FALSE; $message[] = "Please enter the due date of payment."; }

// If the data pass all tests, add the patient to the database.
if ( $a && $b && $c ) {
	// check if the sale already exists 
	$sql = "SELECT sale_id FROM sales
			WHERE customer_id = '$customer_id' AND delivery_date = CURDATE()";
	$sres = mysql_query($sql);
	$snum = mysql_num_rows($sres); 
	$srow = mysql_fetch_array($sres);
	if ($snum > 0) {
		print "Sale already exists in the database.<br>Click <a class=paging href='sale.php?sale_id=$srow[0]'>here</a> to view the product sale.<br>";
	} else {
		$sql = "INSERT INTO sales( customer_id, user_id, delivery_date, due_date, received_by, notes ) 
				VALUES( '$customer_id', '$_SESSION[user_id]', CURDATE(), '$due_date', '$received_by', '$notes' )";
		if (mysql_query($sql)) {
			$sale_id = mysql_insert_id();
			$sql = "UPDATE customers SET lastsale = CURDATE() WHERE customer_id = '$customer_id' LIMIT 1";
			mysql_query($sql) or die(mysql_error()); 
		} else {
			echo "Could not add sale into database<br>";
		}

		if ($_POST[product_id1] != "" && $_POST[quantity1] != "") {
			Add2Sale($_POST[product_id1], $_POST[quantity1], $sale_id, $_POST[unit_price1],	$_POST[unit_discount1], $_POST[net_price1]);
		} // end of product # 1

		if ($_POST[product_id2] != "" && $_POST[quantity2] != "") {
			Add2Sale($_POST[product_id2], $_POST[quantity2], $sale_id, $_POST[unit_price2], $_POST[unit_discount2], $_POST[net_price2]);
		} // end of product # 2

		if ($_POST[product_id3] != "" && $_POST[quantity3] != "") {
			Add2Sale($_POST[product_id3], $_POST[quantity3], $sale_id, $_POST[unit_price3], $_POST[unit_discount3], $_POST[net_price3]);
		} // end of product # 3

		if ($_POST[product_id4] != "" && $_POST[quantity4] != "") {
			Add2Sale($_POST[product_id4], $_POST[quantity4], $sale_id, $_POST[unit_price4], $_POST[unit_discount4], $_POST[net_price4]);
		} // end of product # 4

		if ($_POST[product_id5] != "" && $_POST[quantity5] != "") {
			Add2Sale($_POST[product_id5], $_POST[quantity5], $sale_id, $_POST[unit_price5], $_POST[unit_discount5], $_POST[net_price5]);
		} // end of product # 5

		if ($_POST[product_id6] != "" && $_POST[quantity6] != "") {
			Add2Sale($_POST[product_id6], $_POST[quantity6], $sale_id, $_POST[unit_price6], $_POST[unit_discount6], $_POST[net_price6]);
		} // end of product # 6

		if ($_POST[product_id7] != "" && $_POST[quantity7] != "") {
			Add2Sale($_POST[product_id7], $_POST[quantity7], $sale_id, $_POST[unit_price7], $_POST[unit_discount7], $_POST[net_price7]);
		} // end of product # 7

		if ($_POST[product_id8] != "" && $_POST[quantity8] != "") {
			Add2Sale($_POST[product_id8], $_POST[quantity8], $sale_id, $_POST[unit_price8], $_POST[unit_discount8], $_POST[net_price8]);
		} // end of product # 8

		if ($_POST[product_id9] != "" && $_POST[quantity9] != "") {
			Add2Sale($_POST[product_id9], $_POST[quantity9], $sale_id, $_POST[unit_price9], $_POST[unit_discount9], $_POST[net_price9]);
		} // end of product # 9

		if ($_POST[product_id10] != "" && $_POST[quantity10] != "") {
			Add2Sale($_POST[product_id10], $_POST[quantity10], $sale_id, $_POST[unit_price10], $_POST[unit_discount10], $_POST[net_price10]);
		} // end of product # 10

		$sql = "SELECT SUM(quantity*(unit_price - unit_discount)), 
					SUM((quantity * ((unit_price - unit_discount) - acq_price))) 
				FROM sale_details WHERE sale_id = '$sale_id'";
		$lt_res = mysql_query($sql);
		$lt_row = mysql_fetch_array($lt_res); $line_total = $lt_row[0]; $revenue = $lt_row[1];

		$sql = "SELECT sale_detail_id FROM sale_details WHERE sale_id = '$sale_id'";
		$res = mysql_query($sql);
		$num = mysql_num_rows($res);

		if ($num > 0) {
			$sql = "UPDATE sales 
					SET or_num = '$sale_id', line_total = '$line_total', balance = 0, revenue = '$revenue'
					WHERE sale_id = '$sale_id' LIMIT 1";
			// if suceessful to insert the sale
			if (mysql_query($sql)) {
				print "Sale has been added into the database</b>.<br>";
				show_sale($sale_id);	
			} else {
				print "Could not insert sale into the database because: <b> ".mysql_error()." </b>.<br>";
			}
		} else {
			print "Error! No product has been added into the sale.<br>";
		}
	}
} else {
	// Print out any error messages. 
	if ($message) {
		echo "<center><table><tr><td bgcolor=#FF0000><table bgcolor=#FFE4B5><tr><td><font color=red 
			face=arial size=2><b>The following problems occurred:</b><br>\n";
		foreach ($message as $key => $value) {
			echo "$value <br>\n";
		}
		echo "</font></td></tr></table></td></tr></table></center>\n";
	}
	show_form();
}
} else {
show_form();
}

function show_form() {
?>
<form name="AddSale" action="add_sale.php" method="POST">
	<div class="post">
		<h1 class="title">Add Sale</h1>
		<div class="entry">
			<table align="center">
				<tbody class="data">
					<tr class="odd"><td align="right" width="100">Customer </td>
						<td align="left" width="275"><select name="customer_id"><option value="0">Customer</option>
							<?PHP
							$sql = "SELECT customer_id, company_name FROM customers ORDER BY company_name";
							$sres = mysql_query($sql) or die(mysql_error());
							while ($srow = mysql_fetch_array($sres)) { 
								print "<option value=$srow[0]>$srow[1]</option>"; 
							} ?></select> <font color="red">*</font></td>
						<td align="right" width="100"> </td>
						<td align="left" width="275"></td></tr>
					<tr class="odd"><td align="right">Received By </td>
						<td align="left"><input type="text" size="33" name="received_by"> <font color="red">*</font></td>
						<td align="right">Due Date </td>
						<td align="left"><input readonly type="text" size="10" name="due_date"><a class="td_img" href="javascript:due_date.popup();"><img src="lib/b_calendar.png"></a>  <font color="red">*</font></td></tr>
						<script language="JavaScript">
						<!-- // create calendar object(s) just after form tag closed
							var due_date = new calendar(document.forms['AddSale'].elements['due_date']);
							due_date.year_scroll = true; due_date.time_comp = false;
						//-->
						</script>
					<tr class="even"><td align="right">Notes </td>
						<td colspan="3" align="left"><input type="text" size="106" name="notes"></td></tr>
				</tbody>
			</table>
		</div>
	</div>
	<br>
	<div class="post">
		<h1 class="title">Product Sale Details</h1>
		<div class="entry">
			<table align="center">
				<thead class="data"><tr>
					<td align="center" width="30"></td>
					<td align="center" width="260">Product</td>
					<td align="center" width="60"># Avail</td>
					<td align="center" width="70">Price</td>
					<td align="center" width="70">Disc.</td>
					<td align="center" width="60">Qty</td>
					<td align="center" width="80">Net</td>
				</tr></thead>
				<tbody class="data">
					<?PHP
					for ($i=1; $i<11; $i++) {
					$m = fmod($i, 2); 
					if ($m == 0) {
						?><tr class="even"><?PHP
					} else {
						?><tr class="odd"><?PHP
					}
					?>
						<td align="center"><?PHP echo $i ?></td><td align="left"><select id="product_id<?PHP echo $i ?>" name="product_id<?PHP echo $i ?>" onchange="MyAjaxRequest('unit_price<?PHP echo $i ?>', 'get_price.php?product_id=', 'available<?PHP echo $i ?>', 'get_avail.php?product_id=', 'product_id<?PHP echo $i ?>')">
							<option>Product Name</option>
								<?PHP
								$sql = "SELECT product_id, product_name FROM products ORDER BY product_name";
								$pres = mysql_query($sql);
								while ($prow = mysql_fetch_array($pres)) {
									print "<option value=$prow[0]>$prow[1]</option>";
								} ?></select></td>
						<td align="center"><input type="text" size="5" id="available<?PHP echo $i ?>" name="available<?PHP echo $i ?>" readonly="true" style="text-align:right;"></td>
						<td align="center"><input type="text" size="5" id="unit_price<?PHP echo $i ?>" name="unit_price<?PHP echo $i ?>" readonly="true" style="text-align:right;"></td>
						<td align="center"><input type="text" size="4" id="unit_discount<?PHP echo $i ?>" name="unit_discount<?PHP echo $i ?>" onblur="getSTPrice()"></td>
						<td align="center"><input type="text" size="2" id="quantity<?PHP echo $i ?>" name="quantity<?PHP echo $i ?>" onblur="getSTPrice()"></td>
						<td align="center"><input type="text" size="7" id="net_price<?PHP echo $i ?>" name="net_price<?PHP echo $i ?>" readonly="true" style="text-align:right;"></td>
					</tr>
					<?PHP
					}
					?>
					<tr class="odd"><td align="center" colspan="7">
						<input type="reset">   
						<input type="submit" name="submit" value="Submit"></td></tr>
				</tbody>
			</table>
		</div>
	</div>
</form>
<?PHP
}

function Add2Sale($product_id, $quantity, $sale_id, $unit_price, $unit_discount, $net_price) { // items are sold FIFO
// while quantity requested is not zero
while ($quantity > 0) {
	// get the oldest purchase in the inventory
	$sql = "SELECT DATEDIFF(p.delivery_date, NOW()) AS diff, pd.purchase_detail_id, 
				pd.unit_price - pd.unit_discount, pd.balance
			FROM purchases p, purchase_details pd
			WHERE p.purchase_id=pd.purchase_id 
				AND pd.balance > 0 
				AND pd.product_id = '$product_id'
			ORDER BY diff, p.purchase_id LIMIT 0, 1";
	$res = mysql_query($sql); 
	$num = mysql_num_rows($res);
	if ($num > 0) {
		$row = mysql_fetch_array($res);
		// information about the purchase of the product
		$purchase_detail_id = $row[1];
		$acq_price = $row[2];
		$bal = $row[3];

		if ($quantity <= $bal) {
			// get the changes in purchae_detail after the insert of sale_detail
			$new_pdbal = $bal - $quantity;
			$net_price = $quantity * ($unit_price - $unit_discount);

			// update the purchase details table
			$upd8_pd = "UPDATE purchase_details SET balance = '$new_pdbal' 
					WHERE purchase_detail_id = '$purchase_detail_id' LIMIT 1";
			mysql_query($upd8_pd);
			if (mysql_affected_rows() == 1) {

				// insert into sale details table
				$insert_sale = "INSERT INTO sale_details (
							sale_id, purchase_detail_id, product_id, acq_price, 
							quantity, unit_price, unit_discount, net_price
							) 
						VALUES ( 
							'$sale_id', '$purchase_detail_id', '$product_id', '$acq_price', 
							'$quantity', '$unit_price', '$unit_discount', '$net_price'
							)";
				if (mysql_query($insert_sale)) {
					// quantity requested will be zero
					$quantity = 0;
				} else {
					print "<center><font face=arial size=2 color=red>ERROR!!! Failed to add into sale details.</font></center>";
				}
			} else {
				print "<center><font face=arial size=2 color=red>ERROR!!! Failed to update purchase details table.</font></center>";
			}
		} else {
			// insert the sale detail and update the purchase_detail balance to zero
			$net_price = $bal * ($unit_price - $unit_discount);

			// update the purchase details table
			$upd8_pd = "UPDATE purchase_details SET balance = 0  
					WHERE purchase_detail_id = '$purchase_detail_id' LIMIT 1";
			mysql_query($upd8_pd);
			if (mysql_affected_rows() == 1) {

				// insert into sale details table
				$insert_sale = "INSERT INTO sale_details (
							sale_id, purchase_detail_id, product_id, acq_price, 
							quantity, unit_price, unit_discount, net_price
							) 
						VALUES ( 
							'$sale_id', '$purchase_detail_id', '$product_id', '$acq_price', 
							'$bal', '$unit_price', '$unit_discount', '$net_price'
							)";
				if (mysql_query($insert_sale)) {
					// update the remaining balance of quantity
					$quantity = $quantity - $bal;
				} else {
					print "<center><font face=arial size=2 color=red>ERROR!!! Failed to add into sale details.</font></center>";
				}
			} else {
				print "<center><font face=arial size=2 color=red>ERROR!!! Failed to update purchase details table.</font></center>";
			}
		}
	} else {
		$quantity = 0;
	}

	mysql_free_result($res);
}
$sql = "UPDATE products SET lastsale = NOW() WHERE product_id = '$product_id' LIMIT 1";
mysql_query($sql) or die(mysql_error()); 
} // end
?>

Due to limitation of allowed number of characters in posts, i have removed parts that I strongly believe have nothing to do with the error. Please feel free to criticize my work. Thank you so much.

PFMaBiSmAd · April 28, 2011

Care to narrow down the problem by identifying -

exactly what result or symptom you are getting that leads you to believe sales transactions are getting inter-mixed

needs_upgrade · April 29, 2011

Example scenario:

Workstation #1:

User #1 is entering sales with these items:

Qty Product Price Total

5 ItemA $10 $50

5 ItemB $9 $45

5 ItemC $10 $50

Line Total $145

Workstation #2:

User #2 is entering sales with these items:

Qty Product Price Total

10 ItemD $10 $100

10 ItemE $12 $120

10 ItemF $15 $150

Line Total $370

After the sales were submitted:

Sale entered in workstation #1:

Qty Product Price Total

5 ItemA $10 $50

5 ItemB $9 $45

10 ItemF $15 $150

Line Total $245

Sale entered in workstation #2:

Qty Product Price Total

10 ItemD $10 $100

10 ItemE $12 $120

5 ItemC $10 $50

Line Total $270

ItemC which is supposed to be in sale entered in workstation#1 is now in sale entered in workstation#2.

ItemF which is supposed to be in sale entered in workstation#2 is now in sale entered in workstation#1.

Line Total of sales are now changed since there were alteration in the items entered.

PFMaBiSmAd · April 29, 2011

Have you investigated if the data is being stored correctly in your sale_details table for each sale_id or is that data correct and the problem occurs when you display the end result, which would be the show_sale() function which you didn't post. Posting the show_sale() function code would be helpful.

Any chance the bad data is occurring for a sale_id of zero, because your code is not accessing data from queries only after checking if the queries have executed without errors and your code could be using a non-existent (zero) value for things like the sale_id. For example, when you INSERT the row into the sales table, you are checking if that query was successful or not with an if(){}else{} statement. However, ALL the code that is dependent on that query working and the sale_id value from that query should be INSIDE the if(){...} part of that statement.

You could also be deleting/manipulating values (in the part of your code you didn't show) and that code is being executed when you don't think it is (i.e. for things like a header() redirect that don't have an exit; statement after them), resulting in a scrambling of sale_id values and data. Attaching all the code to the post as a .zip would be the quickest way of getting a solution.

The only apparent functional problem (I'll withhold comments on the code in general) in the code you did post, concerns concurrent submissions and getting and modifying the current inventory amounts. If two or more invocations of your script call the Add2Sale() function for the same $product_id, you can get incorrect results because each invocation of the script is trying to subtract from the balance at the same time and could exceed the quantity available. You either need to lock that record or check if the update resulted in a negative balance and adjust the quantity accordingly.

Edit: You should also be developing and debugging that code on a system with error_reporting set to E_ALL and display_errors set to ON. There are a number of places where you don't have array index names inside of quotes and you are also trying to access $_POST['sale_id'] to set $sale_id, which doesn't exist in the form (that you posted) and could be the cause of some of your problems if the logic that is trying to set $sale_id later in the code is failing.

PFMaBiSmAd · April 29, 2011

Also, any chance you have existing data using any particular sale_id in the sale_details table and you have at some point cleared and reset the sales table and are reusing the auto-increment id values?

PFMaBiSmAd · April 29, 2011

Related to the concurrent UPDATE problem mentioned above, the multiple update queries will leave the balance as an incorrect amount because you are selecting the starting value in one query, subtracting an amount in the php code, and then updating the balance with that result. The last update query that gets executed will 'win' and leave the balance set to the value it would have resulted in and that will replace any previous update.

You can solve this one problem by doing the subtraction in the UPDATE query and not in the php code. This will mean that the balance will be accurate (each concurrent update will subtract from the actual current balance in the table row), but could result in a negative value as previously mentioned.

Sign In

Sale Items messed up in an inventory system

Recommended Posts

needs_upgrade

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

needs_upgrade

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

needs_upgrade

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information