Партионный учет в интернет-магазине

Пару лет назад передо мной стояла задача организации партионного учета в интернет-магазине.

Здесь я расскажу о том, как это было сделано.

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

Перед вами стоит задача определения размера прибыли, приносимой магазином.

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

Продемонстрирую это на примере.
Предположим, вы закупили 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

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