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

keltanas · 21 Май 2013

Отталкиваемся от сюда:

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

Т.о. тебе может потребоваться найти либо разницу между credit closed и debit closed - это сколько всего времени товар пролежал на складе.
Либо debit create и debit сlosed - сколько времени товар пролежал в резерве.

WMix · 21 Май 2013

записи в логе
пришло 1.01 2шт
пришло 2.01 1шт
ушло 5.01 1шт
ушло 8.01 2шт
ответ должен быть 1 шт лежала 5дней, 1шт 8дней и 1шт 7 дней.
так я только на php могу считать хотя и в этом случае не пойму как не считать то что уже подсчитано. а sql'ом еще хуже.

keltanas · 21 Май 2013

Если лог - это logbook - то он должен отслеживать order_position, а не product.
Иначе, как ты определишь, что продал именно ту позицию, которая пришла на склад?
Если ссылаешься только на product, то сможешь определить только когда и сколько товара было на складе?

WMix · 21 Май 2013

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

)

но это никак не решает хранение товаров.

WMix · 21 Май 2013

Если ссылаешься только на product, то сможешь определить только когда и сколько товара было на складе?

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

допустим это есть, как считать?
пришло 1.01 2шт на складе 2
пришло 2.01 1шт на складе 3
ушло 5.01 1шт на складе 2
ушло 8.01 2шт на складе 0

keltanas · 22 Май 2013

Ну, работая с той схемой БД, что ты привел, и тем, что было выше сказано (adata - дата создания резерва, cdate - дата отгрузки), можно составить такой запрос:

PHP:

SELECT p.name, i.direction, lb.qty, i.adate, i.cdate, (TO_DAYS(i.cdate) - TO_DAYS(i.adate)) days FROM invoices i
INNER JOIN logbook lb ON lb.invoice_id = i.id
INNER JOIN products p ON lb.product_id = p.id;

PHP:

+-------------------+-----------+-----+---------------------+---------------------+------+
| name              | direction | qty | adate               | cdate               | days |
+-------------------+-----------+-----+---------------------+---------------------+------+
| Samsung UE55F6500 | IN        |   3 | 2013-05-21 12:41:48 | 2013-05-21 12:59:44 |    0 |
| Samsung UE55F6500 | OUT       |   2 | 2013-05-21 12:44:55 | 2013-05-21 13:30:32 |    0 |
| Samsung UE55F6500 | IN        |   5 | 2013-05-21 12:50:45 | NULL                | NULL |
| Onkyo TX-NR818    | IN        |   3 | 2013-05-21 13:05:19 | 2013-05-21 13:06:09 |    0 |
+-------------------+-----------+-----+---------------------+---------------------+------+

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

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

Чтобы ответить на этот вопрос, каждая позиция должна иметь только одну строку расхода и только одну строку прихода. Тогда уже мы сможем сделать выборку и узнать, когда ее привезли, а когда отгрузили?
Отсюда опять складывается, что надо журнал привязывать не к товарам в каталоге, а к позициям в заказе.
Если надо отгрузить 2 одинаковых товара, но закупали мы их разными партиями, то надо разбить позицию в заказе на 2 и указать, какой позиции какие соответствуют записи в журнале?
Иначе, по журналу мы сможем восстановить только в какой день сколько этого товара было на складе, а не проследить судьбу каждой позиции.

В итоге, должно получится что-то типа:

PHP:

SELECT p.name, lbi.qty, lbo.qty, ii.cdate `in`, io.cdate `out`, (TO_DAYS(io.cdate) - TO_DAYS(ii.cdate)) days  FROM products as p
INNER JOIN logbook as lbo ON lbo.product_id = p.id
INNER JOIN invoices as io ON lbo.invoice_id = io.id AND io.direction = 'OUT'
INNER JOIN logbook as lbi ON lbi.product_id = p.id
INNER JOIN invoices as ii ON lbi.invoice_id = ii.id AND ii.direction = 'IN';

вывод

PHP:

+-------------------+-----+-----+---------------------+---------------------+------+
| name              | qty | qty | in                  | out                 | days |
+-------------------+-----+-----+---------------------+---------------------+------+
| Samsung UE55F6500 |   3 |   2 | 2013-05-21 12:59:44 | 2013-05-21 13:30:32 |    0 |
| Samsung UE55F6500 |   5 |   2 | NULL                | 2013-05-21 13:30:32 | NULL |
+-------------------+-----+-----+---------------------+---------------------+------+

Поскольку у нас товар, который мы продаем, имеет 2 закупки, и получается такая беда. Если он будет иметь только одну закупку, то все будет Ok.

PHP:

+-------------------+-----+-----+---------------------+---------------------+------+
| name              | qty | qty | in                  | out                 | days |
+-------------------+-----+-----+---------------------+---------------------+------+
| Samsung UE55F6500 |   3 |   2 | 2013-05-21 12:59:44 | 2013-05-21 13:30:32 |    0 |
| HTC Sensation     |   5 |   3 | 2013-05-17 02:51:50 | 2013-05-22 02:47:50 |    5 |
+-------------------+-----+-----+---------------------+---------------------+------+

WMix · 22 Май 2013

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

PHP:

CREATE TABLE IF NOT EXISTS `logbook` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(10) unsigned NOT NULL,
  `adate` date NOT NULL,
  `debit` int(10) unsigned DEFAULT NULL,
  `credit` int(10) unsigned DEFAULT NULL,
  `bilance` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

DELIMITER //

DROP TRIGGER IF EXISTS `add_log`//
CREATE TRIGGER `add_log` BEFORE INSERT ON `logbook`
 FOR EACH ROW BEGIN
  DECLARE _bilance  INT UNSIGNED DEFAULT 0;

  SELECT 
    SUM( if(l.debit IS NOT NULL, l.debit, 0 ) ) - SUM( if(l.credit IS NOT NULL, l.credit, 0 ) ) 
  INTO _bilance
  FROM logbook l
  WHERE l.product_id = NEW.product_id
  GROUP BY l.product_id;

  SET NEW.bilance = _bilance + IF(NEW.debit IS NOT NULL, NEW.debit,0 ) - IF(NEW.credit IS NOT NULL, NEW.credit,0 );


END
//
DELIMITER ;

INSERT INTO `logbook` (`id`, `product_id`, `adate`, `debit`, `credit`) VALUES
(NULL, 1, '2013-05-01', 2, NULL),
(NULL, 1, '2013-05-02', 1, NULL),
(NULL, 1, '2013-05-07', NULL, 1),
(NULL, 1, '2013-05-08', NULL, 2);

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

сейчас пробую это реализовать

keltanas · 22 Май 2013

Ох, не ищешь ты простых путей...

Explicit is better than implicit.
Simple is better than complex.
Complex is better than complicated.
Flat is better than nested.

WMix · 22 Май 2013

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

как раз наоборот, прежде чем писать я ищу простой способ.

keltanas · 22 Май 2013

Если тебе надо знать, сколько времени пролежал заказанный товар, то посчитай NOW() - createdAt у разходной записи в журнале из моей схемы.

PHP:

SELECT p.name, i.direction, lb.qty, i.adate, i.cdate, (TO_DAYS(i.cdate) - TO_DAYS(i.adate)) days FROM invoices i
INNER JOIN logbook lb ON lb.invoice_id = i.id
INNER JOIN products p ON lb.product_id = p.id
WHERE i.direction = 'OUT';

или

PHP:

SELECT p.name, i.direction, lb.qty, i.adate, i.cdate, (TO_DAYS(NOW()) - TO_DAYS(i.adate)) days FROM invoices i
INNER JOIN logbook lb ON lb.invoice_id = i.id
INNER JOIN products p ON lb.product_id = p.id
WHERE i.direction = 'OUT' AND i.cdate IS NULL;

WMix · 24 Май 2013

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

keltanas · 25 Май 2013

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

WMix · 25 Май 2013

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

PHP:

CREATE TABLE IF NOT EXISTS `logbook` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(10) unsigned NOT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `adate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `type` enum('delivered','shipped') NOT NULL,
  `quantity` int(10) unsigned NOT NULL,
  `calculated` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'учтено',
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  KEY `calculated` (`calculated`),
  KEY `type` (`type`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS `storage_duration` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `adate` date NOT NULL,
  `edate` date NOT NULL,
  `product_id` int(10) unsigned NOT NULL,
  `qty` int(10) unsigned NOT NULL,
  `duration` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

INSERT INTO `warehouse`.`logbook` (`product_id`,`type`, `quantity`) VALUES (1, 'delivered', '1');
INSERT INTO `warehouse`.`logbook` (`product_id`,`type`, `quantity`) VALUES (2, 'delivered', '3');

INSERT INTO `warehouse`.`logbook` (`product_id`,`type`, `quantity`) VALUES (1, 'delivered', '2');
INSERT INTO `warehouse`.`logbook` (`product_id`,`type`, `quantity`) VALUES (2, 'delivered', '2');

INSERT INTO `warehouse`.`logbook` (`product_id`,`type`, `quantity`) VALUES (1, 'shipped', '1');
INSERT INTO `warehouse`.`logbook` (`product_id`,`type`, `quantity`) VALUES (2, 'shipped', '2');

INSERT INTO `warehouse`.`logbook` (`product_id`,`type`, `quantity`) VALUES (1, 'shipped', '2');
INSERT INTO `warehouse`.`logbook` (`product_id`,`type`, `quantity`) VALUES (2, 'shipped', '3');

DROP PROCEDURE IF EXISTS calc;
DELIMITER //
CREATE PROCEDURE calc()
  BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE _in_id,_out_id,_product_id,_in_qty,_out_qty,_calculated, _count INT UNSIGNED;
    DECLARE _begin_date,_end_date DATETIME;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    read_line: LOOP

      SELECT count(*) INTO _count
      FROM  logbook l
      WHERE type = 'shipped' AND quantity-calculated>0;

      IF _count THEN 
        SELECT 
          il.id,
          ol.id, 
          ol.product_id,
          DATE(il.adate), 
          DATE(ol.adate), 
          il.quantity - il.calculated, 
          ol.quantity - ol.calculated 
        INTO _in_id,_out_id,_product_id,_begin_date,_end_date,_in_qty,_out_qty
        FROM logbook ol
        LEFT JOIN logbook il on ol.product_id = il.product_id
        WHERE ol.id = (
          SELECT min(id)
          FROM  logbook l
          WHERE type = 'shipped' AND quantity-calculated>0 AND product_id = ol.product_id
        ) AND il.id = (
          SELECT min(id)
          FROM  logbook l
          WHERE type = 'delivered' AND quantity-calculated>0 AND product_id = il.product_id
        )
        LIMIT 1;
        IF done THEN LEAVE read_line; END IF;
        
        IF _in_qty > _out_qty THEN SET _calculated = _out_qty;
        ELSE SET _calculated = _in_qty; END IF;
  
        INSERT INTO storage_duration SET
          adate=_begin_date,
          edate=_end_date,
          product_id=_product_id,
          qty=_calculated,
          duration = DATEDIFF(edate,adate);
  
        UPDATE logbook SET calculated = calculated+_calculated 
        WHERE id =_in_id;
  
        UPDATE logbook SET calculated = calculated+_calculated 
        WHERE id =_out_id;
      ELSE LEAVE read_line; END IF;
    END LOOP;
  END;
//

keltanas · 25 Май 2013

Ну ты монстр. Я бы никогда не стал написанием процедур заниматься )))

У меня вся логика хранилась в коде моделей. Я ее туда на силу собрал, и потом уже все преобразования только через модели делал. Производительность немного страдала, но не критично в рамках использования проекта. Но, даже если бы система прогибалась, можно было бы запустить php код на нескольких серверах. А базу масштабировать сложнее. Да и при N-количестве таких вот триггеров и процедур вдруг поменять базу было бы очень больно. Ну и конечно, просто лень разбираться )))

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

WMix · 26 Май 2013

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

WMix · 26 Май 2013

keltanas
вот назрел еще один вопрос, он практически не затрагивает предыдущую тему, но все это об одном и томже.
нужно прикрутить документы. и тут проблема что 2 различных документа имеют различный набор аттрибутов. если их обьеденить в одну табличку то будет порядка 50 столбиков, при этом заполненно будет только 1/4 в каждой строчке - никуда не годится.
если разбивать по табличкам то теряется связь. я остановился на этом выборе, но хотел спросить как делаешь это ты, может лучше выбрать подобие EAV, и на каждый документ создавать несколько строк в табличку docs_attributes, или еще другой способ?

для представления диаграмка 2го способа

keltanas · 27 Май 2013

У меня было все в одной таблице. Мы даже по началу умудрились счета впиндюрить в таблицу с накладными. Сразу скажу, что было сильно не удобно.
Но, такие вещи, как плательщик, грузополучатель, поставщик и грузоотправитель в накладной так и так надо держать. Также направление накладной, даты и пр. необходимые свойства.
А если нужны доп. поля для чего-то кастомного, то я бы сделал подобие EAV для тех полей, по которым надо производить поиск. И можно еще иметь еще какой-нибудь BLOB для хранения данных (по которым поиск не нужен) в сериализованном виде.

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

keltanas

marty cats

WMix

герр M:)ller

keltanas

marty cats

WMix

герр M:)ller

WMix

герр M:)ller

keltanas

marty cats

WMix

герр M:)ller

keltanas

marty cats

WMix

герр M:)ller

keltanas

marty cats

WMix

герр M:)ller

keltanas

marty cats

WMix

герр M:)ller

keltanas

marty cats

WMix

герр M:)ller

WMix

герр M:)ller

Вложения

keltanas

marty cats