как правильно решается задача с item на строку

[WMix]

задача звучит следующим образом:
будем называть одну еденицу товара item если в одном заказе присутствует более одной еденицы одного и тогоже товара на каждую еденицу должна быть запись в базе данных, при этом создатель заказа видит краткую запись с количеством. (смысл следить за каждой отдельной еденицей товара, закупочная цена, дата приема, заказа и тд.)

пример:

на экране
2 x носки х/б белые xxl

в базе
1 носки х/б белые xxl ...
2 носки х/б белые xxl ...

итак по шагам.
просмотр данных
вероятнее всего для товара нужно создать новый ключик пусть будет md5( заказ, название товара )
и последующий group by ключик.

ввод данных
1. пользователь указывает название и количество.
2. запускается цикл генерится ключик и insert на количество.
(тут просто)

простое изменение данных
1. пользователь указывает НОВОЕ количество.
2. считывается предыдущее кол-во в зависимости от разности генерится delet limit или insert.
(уже сложнее)


сложное изменение данных
1. пользователь указывает НОВОЕ количество и изменил название.
2. нужен ключик старого названия, генерирование нового ключа далее update, с последующим delet limit или insert.
(еще понятно но уже довольно сложно, за ключиком не хочется следить)


вопрос: возможно для подобных задачь уже существует решение?

 

[Redjik]

А чем не устраивает order_id,product_id,count ?
Или я задачу не понял?

 

[WMix]

хочу знать о каждой еденице отдельно, по чем купили, где купили, как долго лежала на складе и тд.
представь что просят купить 2 еденицы товара которые можно найти только со 2х рук, или пришла поставка с 50 еденицами, 22 были отправленны на различные заказы, 8 было испорченно, остальные лежат на складе на 3х различных местах.

ответом на вопрос "сколько времени пролежал тот или иной товар?" должен быть следующего характера
5 едениц пролежало 7 дней, 21 еденица 15 дней ...

 

[Ragazzo]

WMix
дак покопай всякие магенты и престошопы

 

[WMix]

написал триггер вроде так не парюсь .

CREATE TABLE IF NOT EXISTS `items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pos_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `pos_id` (`pos_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `positions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
  `qty` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


CREATE TRIGGER add_position AFTER INSERT ON positions
FOR EACH ROW
  BEGIN
    DECLARE quantity INT DEFAULT NEW.qty;
	WHILE quantity > 0 DO
		INSERT INTO items SET pos_id = NEW.id;
		SET quantity = quantity - 1;
  END WHILE;
END;

CREATE TRIGGER change_position AFTER UPDATE ON positions
FOR EACH ROW
  BEGIN
    DECLARE quantity INT;
	IF OLD.qty > NEW.qty THEN
		SET quantity = OLD.qty-NEW.qty;
		WHILE quantity > 0 DO
			DELETE FROM items WHERE pos_id = OLD.id LIMIT 1;
			SET quantity = quantity - 1;
		END WHILE;
	ELSE
		SET quantity = NEW.qty - OLD.qty;
		WHILE quantity > 0 DO
			INSERT INTO items SET pos_id = NEW.id;
			SET quantity = quantity - 1;
  		END WHILE;
	END IF;
END;

ALTER TABLE `items`
  ADD CONSTRAINT `items_ibfk_1` FOREIGN KEY (`pos_id`) REFERENCES `positions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

 

[keltanas]

Я бы попробовал хранить номенклатуру, состояние склада и журнал операций отдельно. Этого, вроде, должно хватить.
И еще стоит подумать над тем, что такое заказ, а что такое корзина товара, и это не одно и тоже. А ты, вроде как, пытаешься их смешать.
В корзине покупатель может делать что угодно, и это никак не должно менять состояние склада.
А когда человек оформляет заказ - он начинает транзакцию, которая может быть либо завершена (заказ оплачен и выдан), либо отменена. А менять данные внутри транзакции - это нельзя.

 

[WMix]

Я бы попробовал хранить номенклатуру, состояние склада и журнал операций отдельно.

строка из корзины, превращается в строку заказа сменой статуса, эта строка также описывает товар который после придет на склад, когда товар уедет к покупателю эта же строка будет строкой счета. складской учет в другом месте это правда общение на вебсервисе, но так уж получилось, что заказ или посылка содержит несколько кастрированную сущность той корзины покупок (товар), туже самую строчку.

во всем есть только одно изменение, на некоторые вопросы ответить невозможно, тк. товар ходил скопом, удобнее работать с каждой еденицей отдельно. они приходят, хранятся, уходят, а порою и заказываются раздельно.

А когда человек оформляет заказ - он начинает транзакцию, которая может быть либо завершена (заказ оплачен и выдан), либо отменена. А менять данные внутри транзакции - это нельзя.

я не понял к чему это сказано..

 

[keltanas]

Чтобы ответить на эти вопросы, надо иметь журнал операций над товарами. Не обязательно на каждый товар на складе заводить отдельную строку. Конечно, проецировать реальный мир на виртуальный - это хорошо. Но таким образом - немного перебор, как мне кажется. К тому же ты все равно не ответишь на вопросы ЧГК.
С точки зрения планирования покупок, тебе скорее всего придется отвечать на вопросы, сколько какого товара ушло в этом месяце, сколько в прошлом? Судьба каждой позиции, как правило, нафиг никому не интересна. Интересно работать с группами товаров, с конкретными наименованиями, но не с каждой купленной позицией.
С журналом это будет сделать как 2 пальца. В бухгалтерии. например, таким журналом являются накладные, список которых позволяет знать о товаре все, что необходимо. Не думаю, что нужно придумывать свой велосипед.

я не понял к чему это сказано?

Как к чему? Мы о чем вообще?

 

[WMix]

Как к чему? Мы о чем вообще?

я о складе, и о товарах.

Но таким образом - немного перебор, как мне кажется.

возможно попробуй предложи свое решение.

пример 1.
человек заказал двое нечто и еще чтото. этого еще нет, но это уже заказ а возможно и предоплата (трансакция покупки закончилась?), купили это в 3х магазинах по 3м разным ценам, две еденицы пришли на склад а третья еще нет, клиент согласился получить первые два товара (счет, посылка) сейчас а третью потом. счет выставляется автоматически не перебирая логи изменений

пример 2.
на склад пришло 10 едениц, они были разложены на 5 мест, 2шт отправили 01.01, 5шт 07.01, 2шт 15.01 и 1шт пролежала 1 месяц после чего ее выкинули. за хранение выставляется счет за место за день. счет выставляется автоматически не перебирая логи изменений

Судьба каждой позиции, как правило, нафиг никому не интересна.

если это iPhone за 800€ то думаю очень интересно.

С точки зрения планирования покупок, тебе скорее всего придется отвечать на вопросы, сколько какого товара ушло в этом месяце, сколько в прошлом?

вот это как раз совсем не интересно. этим занимаются продавцы.

С журналом это будет сделать как 2 пальца.

журнал обязательно присутствует, но только чтоб разобраться когда чтото не бьется. лишний рот копающий логи и выставляющий счета мне лично не нужен.

 

[keltanas]

Так вот давай так определимся.
1. У тебя есть номенклатура, из которой человек выбирает себе товар и кладет в корзину (мы даже можем не знать ничего о наличии). Состояние корзины может хоть в сессии храниться.
2. Далее, он жмет в корзине "Оформить заказ". При этом, как раз в базе создается запись заказа, а также позиции. Только если он заказал 2 одинаковых iPhone, то мы просто делаем у позиции qty=2

+--------+--------+----+------+
|order_id|prod_id |qty |status|
+--------+--------+----+------+
|1       |1       |2   |stock |
+--------+--------+----+------+
|1       |2       |1   |order |
+--------+--------+----+------+

3. Далее, когда манагер видит этот заказ и ставит ему стаус "в обработке".
4. Теперь этот манагер уже может либо "зарезервировать" товар на складе (если этого еще не произошло на шаге 3), либо поставить заявку отделу закупки. Это уже должно в складском модуле происходить, либо в модуле закупки (тут надо их работу разбирать подробнее).
Предполагаю, что склад - это таблица счетчиков количества товара.
В качестве резерва, мы может сделать в журнале запись о расходе этого товара, указать id строки заказа, но дату продажи оставить пустой. Счетчик новара на складе декрементировать.

log
+---------+----------+-------+------------+----+-----+
|direction|created_at|done_at|order_pos_id|qty |price| 
+---------+----------+-------+------------+----+-----+
|credit   |20130501  |null   |1           |2   |800  |  
+---------+----------+-------+------------+----+-----+

5. Если у нас товар 1 уже есть на складе, то мы можем его отгрузить.
Тогда надо разбить заказ на 2. К первому заказу можем привязать оплату.
Далее, когда клиент заберет товар по заказу, мы можем в записи журнала к этому заказу поставить дату, у позиции поставить статус shipped.

+--------+--------+----+------+
|order_id|prod_id |qty |status|
+--------+--------+----+------+
|1       |1       |2   |ship  |
+--------+--------+----+------+
|2       |2       |1   |order |
+--------+--------+----+------+

6. Когда приходит на склад недостающая позиция, то мы должны в журнале операций добавить запись о приходе товара, а в таблице склада счетчик инкрементировать.
Поскольку на этот товар уже есть резерв, то сразу создаем и расходную запись с пустой датой продажи (если отказ от резерва, то запись можно удалить) и декрементируем счетчик.
7. Когда придет по второму заказу оплата (или уже пришла), человек может придти и забрать товар.
Тогда в журнале ставишь дату отгрузки, а позицию заказа можно считать отгруженной.

log
+---------+----------+--------+------------+----+-----+
|direction|created_at|done_at |order_pos_id|qty |price| 
+---------+----------+--------+------------+----+-----+
|credit   |20130501  |20130503|1           |2   |800  |  
+---------+----------+--------+------------+----+-----+
|debit    |20130504  |20130504|            |1   |400  |  
+---------+----------+--------+------------+----+-----+
|credit   |20130506  |20130507|2           |1   |400  |  
+---------+----------+--------+------------+----+-----+

Из всего этого тебе может стать интересно, например, построить график прихода и расхода товара на склад. Из него же будет видно, что какое-то время твой айфон мог залежаться на какое-то время. Из какой закупки был этот айфон, не суть важно, т.к. срок годности у него точно нет. А также ты получишь еще более важную информацию о том, по сколько айфонов у тебя вообще залеживается.

Рентабельность имеет смысл считать за какой-то промежуток времени.

Если тебе интересно знать, сколько пролежала позиция от заказа, которую через месяц надо выкинуть (богатые люди), то возьми в журнале расходную запись с пустой датой, которая ссылается на позицию заказа и посмотри, когда она была создана. Так ты можешь посмотреть все расходные строки с пустой расходной датой, и увидеть, сколько времени они лежат в резерве.

ЗЫЖ с определением транзакции я, конечно, поспешил.

 

[WMix]

огромное спасибо, очень здорово все описанно, напоминает то что было до моего прихода

мы разбираем пример 1. (второй вариант проще нет первой части)

добавлю изменения, мы не имеем товаров, каждый товар это нечто особое и приплюснутое к складу как дополнительная фитча. или по другому, клиент заказывает все что его душа пожелает, правое дутое зеркало для автомобиля ягуар 1978 года. те id продукта появится только после его заказа. но это маленькая проблема.

3. Далее, когда манагер видит этот заказ и ставит ему стаус "в обработке".

не совсем
товара на складе нет, его нужно заказать. манагер полез по магазинам, перезвонил друзей, заказал таки, выбрал то что заказано и описал исходящий заказ.
(прописал к каждому item, id исходящего заказа, цену). возможно ему скоро придет номер трэкинга, который он добавит в исходящий заказ. возможно придет счет по email который он распечатает наклеит баркод запишет его в базу и положит в папку. (каждый item действительно стал "в обработке" резервировать я не буду это относится больше к второму примеру, вернее оно произойдет тихо и незаметно)

дальше все совсем плохо, этот заказ может придти на склад несколькими посылками (магазин был посредником и просто наш заказ раскидал 2м поставщикам) но не суть

пришла посылка, кладовщик считывает баркод, идет пойск, нахожу этот баркод, понимаю что за заказ, кому пренадлежит, предлагаю список ожидаемых товаров.

кладовщик паклацал на своем мобильном что пришло (взвесил, измерил), вуаля, клиент видит что на складе появились товары. а кладовщик увидел место куда нужно разместить то что в коробке. (item получил место а продукт размеры и вес)

клиент выбрал то что ему хочется уже сейчас. создается счет, вскоре он вылезит из принтера его положат в коробку нужного размера (я же знаю какой размер коробки)
эта коробка будет у кладовщика, он сканирует баркодик, и его начинают гонять по складу там возьми тут возьми после запакуй взвешай, распечатай сопроводительные документы и положи на палету.

я UML базы завтра добавлю если интересно, было бы очень интересно послушать твои мысли.

Тогда надо разбить заказ на 2. К первому заказу можем привязать оплату.

чтоб не разбивать заказ на 2 заказа я разбил сразу продукты, теперь каждому продукту я могу приписать и откуда и где заказан и каким счетом оплачен и и и

 

[keltanas]

Я уже потом подумал (про себя) что заказ - это всего лишь сущность, удобная для покупателя, чтобы группировать позиции заказа.
Так что все будет вертеться именно вокруг строк заказа. Сколько можем отгрузить за раз, пусть такое qty и указано (и столько же должно идти в журнале).
Если надо продать только одну вещь, из 2х заказанных, то придется побить, что уж скажешь.

Приход надо делать так же, как я описал расход, только наоборот. Попробуй отбросить из своей головы все лишние сущности, а оперировать только теми, что я описал. Этого достаточно, чтобы регистрировать движения товара.

Но, на всякий случай, приведи UML ))) Вместе додумаем, как лучше.

огромное спасибо, очень здорово все описанно, напоминает то что было до моего прихода

Я сам занимался 3 года разработкой системы, которую ты описываешь. Много было сломано копий из-за неверного представления того, что должно быть. Сейчас делаю ecommerce систему для своих нужд, и мне все эти мысли тоже пригодятся (наконец я их оформил письменно).
Так что тема больная, как ты понимаешь )))

 

[WMix]

поначалу да и по сегодняшний день, все было достаточно класичесски, те. order_positions содержал qty, и да разбиваем заказы, складываем
склад это нечто большее чем просто счетчик товаров, есть поставки, отгрузки, товары, упаковки, места, документы, заказчики, поставщики

итак UML, синие стрелочки и сущности это программа склад, вызовы по webservice и чем сущность одной базы станет в другой.
черным цветом это как раз программа заказы,
in - входящий, out исходящий. что я хочу сделать, это центральное звено items. чтоб красиво соеденять входящие и исходящие заказы.
ломать можно буквально все, из всего существующего есть только in_orders и order_positions остальное в большинстве мысли

 

[keltanas]

поначалу да и по сегодняшний день, все было достаточно класичесски, те. order_positions содержал qty,
...
ломать можно буквально все, из всего существующего есть только in_orders и order_positions остальное в большинстве мысли

так рабочей системы еще нет?

Это должно быть что-то вроде http://tiu.ru ?

А склад уже работает, или его тоже надо написать?

 

[WMix]

склад пишется параллельно, но он работает. у склада несколько другая задача, он отделен от всех покупателей, основные интерфейсы это маленькие шаги для мобильных приборов, где работник уже ничего не вводит руками (или по минимуму).
на данный момент работаю с переменной qty задачи отправлять получать считать товары и дополнительная задача это печать документов. (есть вопросы на которые ответить сложно сколько тот или иной товар пролежал на складе)

рабочая система покупок есть но это магазин, типа ПрестоШоп (престо мария ). также есть приблуды для оптовиков, где разговор о загрузки данных и просмотра статистики. есть набросок того что я сейчас делаю (но больше набросок), он работает но требует постоянного вмешательства человека для подгонок данных что хочется избежать, работа это пойск заказанных товаров но никак не перелопачивание данных.

вот подобные формы заказов http://horostorg.ru/sdelat-zakaz http://pregrad.net/cart.php?mode=add

но по складу я с огромным удовольствием прочту любые идеи сохранения подчета иии.. это всеже основной проект. при разговоре можно смешивать склад и заказы, они хоть и работают отдельно, они работают всеже вместе )

 

[keltanas]

Ну вот я попробовал сделать свою схемку, и получилось следующее.
Я не стал это детализировать до мелочей, чтобы не мешало пониманию.

Когда человек оформляет заказ, он добавляет экземпляр Order, и к нему несколько экземпляров OrderPosition.

Далее, если этот OrderPosition был выбран из каталога, и имеет какой-то id в каталоге, мы бы уже могли сказать, есть ли он на складе, и если есть, то зарезервировать. Нам же требуется, как я понял, чтобы он был произвольный. От этого и будет отталкиваться.
Тогда менеджер должен будет вручную обработать заказ.

Чтобы определить, что товар есть на складе, надо выполнить поиск позиций в соответствии с журналом Operation. При чем нам надо искать позиции, для которых есть приходная (credit)
операция, но нет расходной (debit). При чем приходная операция должна быть закрытой, т.е. иметь дату closed_at.
По своей сути приходные документы и операции ничем не отличаются от расходных. В одном случае покупаем мы, в другом у нас. Поэтому не вижу смысла делать приход и расход в разных видах сущностей.
OrderPosition так же не обязаны иметь ссылку на заказ, если они свободно лежат на складе (неликвид).

Вообще говоря, чтобы узнать, какие товары есть на складе, надо всего лишь выбрать позиции, у которых есть приходный закрытый документ, а расходного либо нет, либо он не закрыт.

Процесс операций такой:
Заказываем товар:
* создаем приходую операцию: created_at - ставим текущую дату, closed_at = null
* товар приходит на склад: closed_at - ставим текущую дату
Товар резервируется со склада под заказ:
* создаем расходную операцию: created_at - текущая дата, closed_at = null
* товар забирают со склада: closed_at - ставим текущую дату.

В качестве избыточности (а заодно и для фиксации промежуточных стадий, если есть) для OrderPostion можно еще ввести св-во status.

И в этой схеме для решения твоей задачи, как я уже писал выше, достаточно выбрать расходные операции и посчитать разность между closed_at и created_ad. Или, например, можно выбирать операции, у которых это значение больше 7 дней.

Доставку здесь, мне кажется разумным, прикручивать к накладным (Invoice), так как обычно она считается транспортной компанией за всю "посылку". Или в более общем случае каждая доставка может включать несколько инвойсов.

Как я считаю, эта схема необходима и достаточно для решения твоей задачи.

 

[WMix]

я еще не догоняю, в понедельник на работе открою ексель и поиграю с записями.
в invoice исходящем будут фамилии адреса (клиент) и тд. во входящем будут только цыфорки. максимум поставщик. но товаров там не будет (это изменение), зачем делать лишнюю работу по вводу данных?
invoice от order был отделен по той причине, что на один заказ может быть несколько счетов (магазин был посредник)

склад несколько другой,
давай начнем с первого шага. приходный ордер (inbound), продавец говорит вам(на склад) придет посылка с номером 4242(tracking) в которой будет 5шт того 10 того и тд. когда придет это пересчитают и ответят поставка была но пришло 4шт и из 10 одна попорчена. сейча на складе 25 того 15 того и 12 того...
- те. мне с точки зрения продавца каждый исходящий заказ нужно просто передать на склад и сказать что это и будет поставка
- у тебя получить все новые поставки -- ВСЕ которые будут (кредит?) и closed_at = null
а куда спрятать номер поставки (tracking) -- привязать к invoice? как сгрупировать по каждой отдельной коробке которая придет -- все с invoice xyz?

но еще мне кажется, что то что я назвал items ты сгрупировал заного сделал связку n:m и назвал operation (операция или процес?). если оно так то мне не хватает того слова (аттрибута) которое обьяснит это название.

 

[keltanas]

Под Invoice я имел ввиду накладные и счет-фактуры. Т.е. реальные документы, по которым осуществляется отгрузка. Счета на оплату могут служить офертой, и они подтверждают только предложение продавца продать товар по конкретной цене. Но, они не фиксируют ни движение товара, ни оборот финансов. Вообще ничего. Для хранения счетов можно завести отдельную табличку (я у себя в программе хранил их в таблице (хоть и под разными записями) с накладными, но порядком намучился из-за этого).

в invoice исходящем будут фамилии адреса (клиент) и тд. во входящем будут только цыфорки. максимум поставщик. но товаров там не будет (это изменение), зачем делать лишнюю работу по вводу данных?

А как тогда фиксировать, что конкретный товар пришел на склад?
Не нужно делать лишнюю работу по вводу данных. Данные можно копировать из расхода.

В моей таблице поставка должна бы храниться в Delivery. В свою очередь запись в Delivery должна ссылаться на 1 или несколько записей Invoice.
Для коробок можно завести отдельную табличку.

что то что я назвал items ты сгрупировал заного сделал связку n:m и назвал operation

На счет Items - скажем, я убрал это понятие и ввел другое. Operation - это журнал приходных и расходных операций по товарам. А не сами товары, как в Items.

 

[WMix]

Delivery нет, у item собирался ставить статус "на складе" или "отправленно" (это будет сделано вызовом web-service со стороны склада, когда товар получат и проверят или отправят), отправляет клиент (выбрав товары и нажав кнопку отправить, заодно вписывает получателя - создается строчка исходящий счет (out_invoce), выбранные items получают его id ).

webservices: склад синхронизируется и тянет новые счета (out_invoice), создает "заказы на отправку" (ff_orders). после отправки подгрузит номер трэкинга отправки в таблицу исходящий счет(out_invoce).

Delivery будет на складе, а также "выполненый" "заказ на отправку" с ключем его отправки. счет будет также сгенерен (pdf) непосредственно на складе, тк. его нужно будет в посылку положить, а вот подсчитан будет ранее в магазине когда товары выбирались, чтоб пользователь мог видеть его сразу в личном кабинете, а также оплатить доставку и подбить saldo.

 

[WMix]

поигрался с логом, считать количество очень даже легко, буду привинчивать.
только никак не разберусь как ты длительность хранения предлагаешь считать?

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nick` varchar(15) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `ordered` int(10) unsigned NOT NULL COMMENT 'заказано клиентами',
  `expected` int(10) unsigned NOT NULL COMMENT 'заказанно нами',
  `delivered` int(10) unsigned NOT NULL COMMENT 'сумма поставок',
  `reserved` int(10) unsigned NOT NULL COMMENT 'заказанно на отправку',
  `shipped` int(10) unsigned NOT NULL COMMENT 'сумма отправок',
  `available` int(10) unsigned NOT NULL COMMENT 'на складе',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `adate` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `order_positions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned NOT NULL,
  `qty` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `invoices` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `adate` datetime NOT NULL,
  `cdate` datetime DEFAULT NULL,
  `direction` enum('IN','OUT') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `logbook` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned NOT NULL,
  `qty` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  KEY `invoice_id` (`invoice_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

ALTER TABLE `logbook`
  ADD CONSTRAINT `logbook_ibfk_1` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `logbook_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `orders`
  ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `order_positions`
  ADD CONSTRAINT `order_positions_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `order_positions_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

DROP TRIGGER IF EXISTS `add_order_position`;
DROP TRIGGER IF EXISTS `add_log`;

DELIMITER //

CREATE TRIGGER `add_order_position` AFTER INSERT ON `order_positions`
 FOR EACH ROW BEGIN
  DECLARE _ordered   INT UNSIGNED DEFAULT 0;

  SELECT SUM(op.qty) INTO _ordered
  FROM order_positions op
  WHERE op.product_id = NEW.product_id
  GROUP BY op.product_id;

  UPDATE products SET ordered  = _ordered WHERE id = NEW.product_id;

END
//

CREATE TRIGGER `add_log` AFTER INSERT ON `logbook`
 FOR EACH ROW BEGIN
  DECLARE _ordered   INT UNSIGNED DEFAULT 0;
  DECLARE _expected  INT UNSIGNED DEFAULT 0;
  DECLARE _delivered INT UNSIGNED DEFAULT 0;
  DECLARE _reserved  INT UNSIGNED DEFAULT 0;
  DECLARE _shipped   INT UNSIGNED DEFAULT 0;
  DECLARE _available INT UNSIGNED DEFAULT 0;

  SELECT 
    SUM( if(i.direction = 'IN' AND cdate IS NULL,  l.qty, 0 ) ), 
    SUM( if(i.direction = 'IN' AND cdate IS NOT NULL,  l.qty, 0 ) ), 
    SUM( if(i.direction = 'OUT' AND cdate IS NULL, l.qty, 0 ) ),
    SUM( if(i.direction = 'OUT' AND cdate IS NOT NULL, l.qty, 0 ) ),
    SUM( if(i.direction = 'IN' AND cdate IS NOT NULL,  l.qty, 0 ) ) - SUM( if(i.direction = 'OUT', l.qty, 0 ) )
  INTO _expected, _delivered, _reserved, _shipped, _available
  FROM products p
  LEFT JOIN logbook l ON l.product_id = p.id
  LEFT JOIN invoices i ON l.invoice_id = i.id
  WHERE p.id = NEW.product_id
  GROUP BY p.id;

  UPDATE products SET 
    expected  = _expected,
    delivered = _delivered,
    reserved  = _reserved,
    shipped   = _shipped,
    available = _available
  WHERE id = NEW.product_id;

END
//
DELIMITER ;


INSERT INTO `users` (`id`, `nick`) VALUES
(1, 'wmix');

INSERT INTO `products` (`id`, `name`, `price`, `ordered`, `expected`, `delivered`, `reserved`, `shipped`, `available`) VALUES
(1, 'Samsung UE55F6500', '1187.99', 3, 5, 3, 0, 2, 1),
(2, 'Onkyo TX-NR818', '677.00', 2, 0, 3, 0, 0, 3);

INSERT INTO `orders` (`id`, `user_id`, `adate`) VALUES
(1, 1, '2013-05-21 12:38:40'),
(2, 1, '2013-05-21 13:31:06');

INSERT INTO `order_positions` (`id`, `order_id`, `product_id`, `qty`) VALUES
(1, 1, 1, 3),
(2, 1, 2, 2);

INSERT INTO `invoices` (`id`, `adate`, `cdate`, `direction`) VALUES
(1, '2013-05-21 12:41:48', '2013-05-21 12:59:44', 'IN'),
(2, '2013-05-21 12:44:55', '2013-05-21 13:30:32', 'OUT'),
(3, '2013-05-21 12:50:45', NULL, 'IN'),
(4, '2013-05-21 13:05:19', '2013-05-21 13:06:09', 'IN');


INSERT INTO `logbook` (`id`, `invoice_id`, `product_id`, `qty`) VALUES
(1, 1, 1, 3),
(2, 2, 1, 2),
(3, 3, 1, 5),
(4, 4, 2, 3);