[SOLVED] design issue about using FIFO for an inventory system

[needs_upgrade]

Hi guys!

 

I feel ashamed because my client complained that there are inconsistencies in the system that i've developed. He said that some of the figures in the system do not tally with the actual value in their stock room.

 

The system my follow the FIFO rule and i suspect the problem is how i implemented it. Here are the details how i did it:

 

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',

  `delivery_cost` decimal(7,2) unsigned NOT NULL default '0.00',

  `received_by` int(2) NOT NULL,

  `notes` varchar(255) collate latin1_general_ci NOT NULL,

  `line_total` decimal(13,3) unsigned NOT NULL default '0.000',

  `balance` decimal(13,3) unsigned NOT NULL default '0.000',

  `pmode` int(1) unsigned NOT NULL COMMENT '1 if cash; 2 if check',

  `cancelled` int(1) NOT NULL,

  PRIMARY KEY  (`purchase_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ;

 

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` decimal(8,3) unsigned NOT NULL,

  `unit_price` decimal(10,3) unsigned NOT NULL default '0.000',

  `unit_discount` decimal(8,3) unsigned NOT NULL default '0.000',

  `net_price` decimal(13,3) NOT NULL,

  `balance` decimal(8,3) NOT NULL,

  PRIMARY KEY  (`purchase_detail_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ;

 

 

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,

  `delivery_cost` decimal(7,2) default '0.00',

  `received_by` varchar(150) collate latin1_general_ci NOT NULL,

  `notes` varchar(255) collate latin1_general_ci default NULL,

  `line_total` decimal(13,3) unsigned NOT NULL default '0.000',

  `balance` decimal(13,3) unsigned NOT NULL default '0.000',

  `revenue` decimal(10,3) NOT NULL,

  `pmode` int(1) unsigned NOT NULL COMMENT '1 if cash; 2 if check',

  `cancelled` int(1) NOT NULL COMMENT '1 if cancelled; 2 if draft',

  PRIMARY KEY  (`sale_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ;

 

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,

  `acq_price` decimal(10,3) NOT NULL,

  `quantity` decimal(7,2) unsigned NOT NULL,

  `unit_price` decimal(10,3) unsigned NOT NULL default '0.000',

  `unit_discount` decimal(8,3) unsigned NOT NULL default '0.000',

  `net_price` decimal(13,3) NOT NULL,

  PRIMARY KEY  (`sale_detail_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ;

 

i have added the "purhchase_detail_id" field in the "sale_details" table because it is foreign key to the "purchase_detail_id" in the "purchase_details" table.

 

here's the code i use in processing the sale (taking the products from the purchase_details table);

 

function Add2Sale($product_id, $quantity, $sale_id, $unit_price, $unit_discount, $net_price) {

// 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 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_bal = $bal - $quantity;

 

$net_price = $quantity * ($unit_price - $unit_discount);

mysql_query("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')") or die(mysql_error());

mysql_query("UPDATE purchase_details SET balance='$new_bal' WHERE purchase_detail_id='$purchase_detail_id' LIMIT 1") or die(mysql_error());

 

// quantity requested will be zero

$quantity = 0;

 

} else {

// insert the sale detail and update the purchase_detail balance to zero

$net_price = $bal * ($unit_price - $unit_discount);

mysql_query("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')") or die(mysql_error());

mysql_query("UPDATE purchase_details SET balance=0 WHERE purchase_detail_id='$purchase_detail_id' LIMIT 1") or die(mysql_error());

 

// update the remaining balance of quantity

$quantity = $quantity - $bal;

 

}

} else {

$quantity = 0;

}

 

mysql_free_result($res);

}

} // end

 

 

here's how i show the values in the inventory:

 

$sql = "SELECT p.product_id, CONCAT(p.product_name,' ',p.description),

m.manufacturer_id, m.manufacturer_name, p.sell_price

FROM products p, manufacturers m

WHERE p.manufacturer_id = m.manufacturer_id

ORDER BY $sortby LIMIT $from, $max_results";

$getlist = mysql_query($sql) or die(mysql_error());

 

 

while ($row = mysql_fetch_array($getlist)) {

print "<tr>

<td align=left><a class=tddata href='product.php?product_id=$row[0]'>$row[1]</a></td>

<td align=left><a class=tddata href='manufacturer.php?manufacturer_id=$row[2]'>$row[3]</td>

<td align=right>"; $sell = number_format($row[4], 2); print "$sell</td>";

$sql = "SELECT SUM(pd.balance), SUM((pd.unit_price - pd.unit_discount) * pd.balance), p.reorder_level

FROM purchase_details pd, products p

WHERE pd.product_id = '$row[0]' AND pd.product_id = p.product_id

GROUP BY pd.product_id";

$pres = mysql_query($sql);

$prow = mysql_fetch_array($pres);

print "<td align=right>$prow[0]</td>

<td align=right>"; $gval = number_format($prow[1], 2); print "$gval</td>

</tr>";

}

 

 

Would you guys be kind to criticize my approach. can you recommend better approaches? or maybe you can give me some sources or links. Thank you very much guys.

 

[Acs]

Dude!! Use the code tags.. and summarize the code or something I am not going to read through all that code

[needs_upgrade]

Thanks for replying, Acs. But i have already solved the problem. Thanks God!