Реализация партионного учета на MS SQL

Наткнулся на отличный пост 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 — Результат работы процедуры

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

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

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