Наткнулся на отличный пост http://michaelsmirnov.blogspot.ru/2011/10/blog-post.html. Утащу его себе когда в следующий раз придется зарубиться насчет универсальности, быстроты разработки и т.п. в 1С. Элегантно, быстро, но долго и дорого это явно. А с учетом того что в типовых это уже есть и что юзера захотят понимать почему такая цена, а не другая?
Партионный учет в интернет-магазине
Пару лет назад передо мной стояла задача организации партионного учета в интернет-магазине.
Здесь я расскажу о том, как это было сделано.
Для начала коротко о том, что такое партионный учет и зачем он нужен в интернет-магазине.
Представьте, что ваш магазин продает, скажем, кроссовки. При этом он периодически делает закупки у поставщиков и производит отгрузку покупателям.
Перед вами стоит задача определения размера прибыли, приносимой магазином.
Для того, чтобы определить торговую маржу, нам нужно знать стоимость закупки и цену продажи каждой единицы отгруженного товара. И если с ценой продажи все понятно – ее можно взять из заказа, то с ценой закупки ситуация сложнее.
Продемонстрирую это на примере.
Предположим, вы закупили 10 пар кроссовок по 100 рублей и через месяц еще 10 пар 200 рублей. Затем у вас возник заказ на 12 пар кроссовок, которые вы продали по 300 рублей (общая сумма заказа 12 x 300 = 3600 р.).
Теперь определим маржу. Она составляет (300 – 100) х 10 + (300 — 200) х 2 = 2200 р.
Т.е. мы видим, что в данном заказе присутствовало 10 пар кроссовок из первой партии, которая была закуплена по 100 рублей и 2 пары из второй партии, которая была закуплена по 200 рублей.
Для правильного подсчета маржи нам и необходим партионный учет.
Теперь о том, как это было реализовано на практике.
Прежде всего приведу схему базы данных.
Рассмотрим таблицы этой схемы.
- Products – справочник продуктов.
- ProductModifications – таблица, содержащая разновидности, модификации или варианты продукта. Например, вышеупомянутые кроссовки могут быть разного размера или цвета и для каждого из них нужен свой учет.
- Orders – таблица заказов
- OrderItems – табличная часть заказов
- ProductIncomes – таблица поступлений товаров на склад
- ProductOutcomes – таблица списаний товаров со склада
- ProductOutcomesToParties – вспомогательная таблица, определяющая закупочные цены.
Теперь более подробно об этих таблицах.
Таблица ProductModifications содержит закупочную цену последней партии (поле OriginalPrice) и текущую продажную цену (поле Price). Закупочная цена последней партии не участвует непосредственно в партионном учете. Она имеет чисто информационный характер.
Цена продажи (Price) определяет по какой цене в настоящий момент происходит продажа товара.
Таблица OrderItems содержит ссылку на модификацию товара, количество (Count) и цену продажи (Price).
Таблица ProductIncomes позволяет содержит историю поступления товаров на склад. Для каждого поступления содержится дата (IncomeDate), закупочная цена (OriginalPrice) и количество поступившего товара (Count).
Таблица ProductOutcomes содержит историю списаний товаров со склада. Она также содержит дату списания (OutcomeDate), количество товара (Count) и отпускную цену (Price). Кроме продаж, могут существовать и другие причины списания товаров со склада – например, возврат некондиционного товара или временное резервирование. Для отметки таких списаний применяются поля ReturnToAuthour и IsReserved.
Все вышеперечисленные таблицы заполняются в процессе работы интернет-магазина. Справочники товаров и поступлений заполняются администраторами, заказов – покупателями и администраторами, списания заполняются автоматически при создании заказов, либо администраторами для случая списаний без заказов.
Для того, чтобы подсчитать маржу каждого конкретного проданного товара, нам надо заполнять таблицу ProductOutcomesToParties. Для каждого списания со склада эта таблица содержит несколько записей, которые содержат закупочную цену товара и количество закупленного по данной цене товара. Если рассматривать приведенный выше пример с кроссовками, то эта таблица должна содержать две записи – для каждой из закупленных партий.
Итак, наша задача состоит в том, что правильно рассчитать закупочные цены и заполнить таблицу ProductOutcomesToParties.
Для этого мы должны полностью отследить историю движения по складу каждой модификации товара.
Для начала создадим представление ProductMoves, которое будет содержать всю историю движения товара – т.е. объединение историй поступлений и списаний.
CREATE VIEW [dbo].[ProductMoves] AS SELECT MovType, ID, ProductModificationID, Date, Price, Count, OrderID, CONVERT(bit, ReturnToAuthor) AS ReturnToAuthor, CONVERT(bit, IsReserved) AS IsReserved FROM (SELECT 1 AS MovType, ID, ProductModificationID, IncomeDate AS Date, OriginalPrice AS Price, Count, 0 AS OrderID, 0 AS ReturnToAuthor, 0 as IsReserved FROM ProductIncomes UNION ALL SELECT 2 AS MovType, ID, ProductModificationID, OutcomeDate AS Date, Price, Count, OrderID, ReturnToAuthor, IsReserved FROM ProductOutcomes) AS a
Теперь создадим вспомогательную таблицу ProductWarehouse, которая будет содержать список партий и количество товара в каждой из них. Эта таблица будет вспомогательной в процессе расчета закупочных цен.
В данной таблице у нас хранится ссылка на партию (PartyID) – т.е. на запись в таблице ProductIncomes, текущее количество товара данной партии на складе (CurrentCount), закупочная цена (Price), дата партии (PartyDate) и ссылка на модификацию продукта.
Для расчета закупочных цен создадим хранимую процедуру RecalculateWarehouse, которая решает эту задачу цен для заданной модификации товара.
Рассмотрим код процедуры. В коде процедуры приведены комментарии, объясняющие ее работу.
CREATE PROCEDURE [dbo].[RecalculateWarehouse]
@PMID int, — ID модификации товара
@Result nvarchar (max) output — Результат работы процедуры
AS
set @Result = »
— Сохраняем историю движения товара во временную таблицу.
— Делаем это для того, чтобы расчетом не мешать работе магазина.
select * into #ProductMoves
from ProductMoves (nolock)
where ProductModificationID = @PMID
declare @MoveID int, @MoveType int, @Price money, @Count int, @MoveDate datetime, @MovOrderID int
— Определяем курсор по истории движения товара.
— Данный курсор будет пробегаться по всем фактам поступлений и списаний по мере их
— наступления
— Для этого в курсоре применена сортировка по дате и типу движения
— (чтобы в случае одинаковых дат поступления всегда шли перед списаниями).
declare cur cursor local for
select MovType, ID, Price, [Count], date, OrderID
from #ProductMoves
order by date, MovType
— Открываем курсор
open cur
fetch next from cur into @MoveType, @MoveID, @Price, @Count, @MoveDate, @MovOrderID
— И бежим по всей истории движений товара по складу
while @@fetch_status = 0
begin
— Обрабатываем поступления на склад
if @MoveType = 1
begin
— Если данная партия товара еще не зарегистрирована на складе
if (select count (*)
from ProductWarehouse where PartyID = @MoveID) = 0
— Регистрируем данную партию товара на складе
insert into ProductWarehouse
(PartyID, CurrentCount, Price,
PartyDate, ProductModificationID)
select @MoveID, @Count, @Price, @MoveDate, @PMID
else — Если данная партия уже была зарегистрирована на складе
— Обновляем информацию о ней
update ProductWarehouse
set CurrentCount = @Count, Price = @Price
where PartyID = @MoveID
end
— Обрабатываем списания со склада
if @MoveType = 2
begin
— Определяем временную таблицу, в которую будут помещены
— результаты определения закупочных цен
declare @parties table (ID int, Date datetime, Price money)
declare @PresentedCount int
— Определяем количество товара на складе на момент списания
select @PresentedCount = isnull (sum ([CurrentCount]), 0)
from ProductWarehouse (nolock)
where ProductModificationID = @PMID and PartyDate < @MoveDate
— Если количество товара на складе достаточно для списания
if @PresentedCount >= @Count
begin
— Заполняем таблицу закупочных цен теми партиями,
— текущих количеств на складе которых
— достаточно для списания
insert into @parties (ID, Date, Price)
select ID, PartyDate, Price
from ProductWarehouse (nolock)
where ProductModificationID = @PMID and
CurrentCount > 0 and PartyDate < @MoveDate
order by PartyDate
declare @pid int, @PPrice money, @PDate datetime
— Определяем курсор по всем партиям, которые на
— данный момент есть на нашем складе
declare pcur cursor local for
select id, Price, Date from @parties order by Date
open pcur
declare @SaleCount int
set @SaleCount = @Count
— Удаляем результаты предыдущих расчетов
delete from ProductOutcomesToParties
where ProductOutcomeID = @MoveID
fetch next from pcur into @pid, @PPrice, @PDate
— Пробегаем по всем партиям товара до тех пор,
— пока не наберем достаточное
— количество товара, для заполнения всего списания
while @@fetch_status = 0 and @SaleCount > 0
begin
declare @CurrentCount int, @DecCount int
— Определяем текущее количество товара на складе
— для данной партии
select @CurrentCount = CurrentCount
from ProductWarehouse where ID = @pid
— Если текущего количества товара достаточно
if (@CurrentCount >= @SaleCount)
begin
— Уменьшаем текущее количество товара на
— количество требуемого товара
set @DecCount = @SaleCount
set @CurrentCount=@CurrentCount- @SaleCount
set @SaleCount = 0
— Обновляем количество товара на складе
update ProductWarehouse
set CurrentCount = @CurrentCount
where ID = @pid
— Сохраняем закупочные цены
insert into ProductOutcomesToParties
(ProductOutcomeID, [Count],
IncomePrice, IncomeDate)
select @MoveID, @DecCount, @PPrice, @PDate
end
else
— Если текущего количества товара НЕ достаточно
begin
— Полностью забираем товар из данной партии
— Затем уменьшаем кол-во требуемого товара
set @DecCount = @CurrentCount
set @SaleCount=SaleCount — @CurrentCount
set @CurrentCount = 0
— Обновляем количество товара на складе
update ProductWarehouse
set CurrentCount = @CurrentCount
where ID = @pid
— Сохраняем закупочные цены
insert into ProductOutcomesToParties
(ProductOutcomeID, [Count],
IncomePrice, IncomeDate)
select @MoveID, @DecCount, @PPrice, @PDate
end
fetch next from pcur into @pid, @PPrice, @PDate
end
close pcur
deallocate pcur
end
else
— Если количество товара на складе НЕ достаточно для списания
— Вообще говоря, такая ситуация является ошибочной
— Но все же мы ее обрабатываем на тот случай,
— если информация о поступлениях товара
— еще не была занесена в базу данных
begin
declare @PsevdoPrice money, @PsevdoDate datetime
— В качестве закупочной цены берем последнюю закупочную цену
set @PsevdoPrice = isnull (
(select top 1 Price from ProductWarehouse (nolock)
where ProductModificationID = @PMID and
PartyDate < @MoveDate
order by PartyDate desc), 0)
— Тоже самое делаем и с датой
set @PsevdoDate = isnull (
(select top 1 PartyDate from ProductWarehouse (nolock)
where ProductModificationID = @PMID and
PartyDate < @MoveDate
order by PartyDate desc), @MoveDate)
— Удаляем результаты предыдущих рассчетов
delete from ProductOutcomesToParties
where ProductOutcomeID = @MoveID
— Сохраняем закупочные цены
insert into ProductOutcomesToParties
(ProductOutcomeID, [Count], IncomePrice, IncomeDate)
select @MoveID, @Count, @PsevdoPrice, @PsevdoDate
— Обновляем количество товара на складе
update ProductWarehouse
set CurrentCount = 0
where ProductModificationID = @PMID and PartyDate < @MoveDate
set @Result = @Result + N’Ошибка рассчета. В заказе ‘
+ convert (nvarchar (16), @MovOrderID) +
N’ на дату ‘ + convert (nvarchar (16), @MoveDate, 104)
+ N’ ‘ + convert (nvarchar (16), @MoveDate, 108) +
N’ требуется списать ‘ + convert (nvarchar (50), @Count)
+ N’ шт. товара, на складе — ‘ +
convert (nvarchar (50), @PresentedCount) +
N'</b> шт. Товар был продан по цене <b>’ +
convert (nvarchar (50), @Price) +
N’, закупочная цена определена как ‘ +
convert (nvarchar (50), @PsevdoPrice) + N»
end
end
fetch next from cur
into @MoveType, @MoveID, @Price, @Count, @MoveDate, @MovOrderID
end
close cur
deallocate cur
drop table #ProductMoves
if @Result = »
set @Result = ‘OK’
Теперь создадим процедуру, которая выполняет рассчет закупочных цен для всех товаров.
CREATE PROCEDURE [dbo].[RecalculateAllWarehouse] AS create table #res (ProductID int, Result nvarchar (max)) declare @PMID int, @ProductID int declare cur cursor local for select ID, ProductID from ProductModifications (nolock) order by ID open cur fetch next from cur into @PMID, @ProductID while @@fetch_status = 0 begin declare @res nvarchar (max) exec RecalculateWarehouse @PMID, @res output insert into #res (ProductID, Result) select @ProductID, @res fetch next from cur into @PMID, @ProductID end close cur deallocate cur select * from #res order by ProductID drop table #res
Данная процедура пробегается по всем товарам и выполняет для них необходимый расчет, возвращая затем результаты работы пользователю.