We are developing an inventory tracking system. Basically we've got an order table in which orders are placed. When an order is payed, the status changes from 0 to 1. This table has multiple children in another table order_items.
This is the main structure.
CREATE TABLE order(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED,
status INT(1),
total INT UNSIGNED
);
CREATE TABLE order_items(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
order_id INT UNSIGNED,
article_id INT UNSIGNED,
size enum('s', 'm', 'l', 'xl'),
quantity INT UNSIGNED
);
Now, we've got a stocks table with similar architecture for the acquisitions. This is the structure.
CREATE TABLE stock(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
article_id INT UNSIGNED
);
CREATE TABLE stock_items(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
stock_id INT UNSIGNED,
size enum('s', 'm', 'l', 'xl'),
quantity INT(2)
);
The main difference is that stocks has no status field. What we are looking for is a way to sum each article size from stock_items, then sum each article size from order_items where Order.status = 1 and substract both these items to find our current inventory.
This is the table we want to get from a single query:
Size | Stocks | Sales | Available
s | 10 | 3 | 7
m | 15 | 13 | 2
l | 7 | 4 | 3
Initially we thought abouth using complex find conditions, but perhaps that's the wrong approach.
Also, since it's not a direct join, it turns out to be quite hard.
This is the code we have to retrieve the stock's total for each item.
function stocks_total($id){
$find = $this->StockItem->find('all', array(
'conditions' => array(
'StockItem.stock_id' => $this->find('list', array('conditions' => array('Stock.article_id' => $id)))
),
'fields' => array_merge(
array(
'SUM(StockItem.cantidad) as total'
),
array_keys($this->StockItem->_schema)
),
'group' => 'StockItem.size',
'order' => 'FIELD(StockItem.size, \'s\', \'m\' ,\'l\' ,\'xl\') ASC'
));
return $find;
}
Thanks.
