CREATE TABLE #shipment
(	
	[shipment_sales_channel_name]    [varchar](25)    NULL,
	[shipment_branch_name]           [varchar](100)   NULL,
	[shipment_customer_name]         [varchar](100)   NULL,
	[shipment_priority]              [tinyint]        NULL,
	[shipment_date]                  [datetime]       NULL,
	[shipment_kg]                    [decimal](11, 5) NULL,	
) 

create table #schedule_work
(
	 dt_tm			datetime	not null
	,dw			smallint	    null
)

create table #cars
(
	 car		int		not null
	,volume		dec(11,5)	    null
)

-- создаем индексы для расчетов		
if (select INDEXPROPERTY(object_id('calc.plan_production_finished_products'),'NoCl_stock_sort_row','IndexID')) is null
	CREATE NONCLUSTERED INDEX NoCl_stock_sort_row ON calc.plan_production_finished_products (stock_sort_row);
						
if (select INDEXPROPERTY(object_id('calc.plan_production_finished_products'),'NoCl_stock','IndexID')) is null
	CREATE NONCLUSTERED INDEX NoCl_stock
	ON calc.plan_production_finished_products (data_type, reason_ignore_in_calculate, sap_id, stock_sort_row)
	INCLUDE (row_id,stock_on_date,stock_kg,stock_current_KOS,stock_KOS_in_day,stock_shipment_kg);
							



-- берем первую заявку
select @shipment_sort_row = min(stock_sort_row) -- ключ строки для которой считаем 			
from calc.plan_production_finished_products
where data_type in ('заявки', 'план продаж') 
	and reason_ignore_in_calculate is null;


-- =============== --
-- ОСНОВНОЙ РАСЧЕТ --
-- =============== --
while not @shipment_sort_row is null
begin
	

			-- заполняем переменные по отгрузке
			select
					 @shipment_row_id	= c.row_id
					,@shipment_sap_id	= c.sap_id
					,@shipment_date		= c.shipment_date
					,@shipment_min_KOS	= c.shipment_min_KOS
					,@shipment_kg		= c.shipment_kg
			from calc.plan_production_finished_products as c
			where c.stock_sort_row = @shipment_sort_row


			-- ==================== --
			-- распределяем остатки --
			-- ==================== --
			set @stock_sort_row = 0

			while not @stock_sort_row is null -- 0 для входа в цикл, если остатки null, то выходим из цикла -- 
			begin
																
							-- заполняем остатки
							select 
									 @stock_row_id		= max(st.row_id)
									,@stock_sort_row	= max(st.stock_sort_row)
									,@stock_on_date		= max(st.stock_on_date)
									,@stock_current_KOS = max(st.stock_current_KOS)
									,@stock_KOS_in_day	= max(st.stock_KOS_in_day)
									,@stock_kg			= max(st.stock_kg)
							from (
										select top 1 
												 st.row_id
												,st.stock_sort_row
												,st.stock_on_date
												,st.stock_current_KOS
												,st.stock_KOS_in_day
												,st.stock_kg - isnull(st.stock_shipment_kg, 0) as stock_kg
										from calc.plan_production_finished_products as st
										where st.sap_id = @shipment_sap_id
										and st.stock_sort_row > @stock_sort_row
										and st.stock_kg - isnull(st.stock_shipment_kg, 0) <> 0
										and st.data_type = 'Остатки'
										and st.reason_ignore_in_calculate is null
										order by st.stock_sort_row
									) as st;



							-- ПРОВЕРКА: если остатков нет
							if @stock_sort_row is null
							begin
								CONTINUE
							end;



							-- ПРОВЕРКА: расчета КОС по заявкам или плану продаж
							if @stock_current_KOS - (@stock_KOS_in_day * DATEDIFF(day,  @stock_on_date, @shipment_date)) <= @shipment_min_KOS
							begin
								CONTINUE
							end;



							-- РАСПРЕДЕЛЯЕМ: если заказали больше чем на остатках, но берем кол-во на остатках или кол-во заказанного
							update calc.plan_production_finished_products
							set stock_shipment_kg = isnull(stock_shipment_kg, 0) + iif(@shipment_kg > @stock_kg, @stock_kg, @shipment_kg)
							where stock_sort_row in (@shipment_sort_row, @stock_sort_row);


							-- ЛОГ: остатков	
							insert into #log_change (json_text)														
							values (N'{ 
										 "data_type"		:"log остатки"
										,"from_row_id"	:' + convert(varchar(9), @stock_row_id) + '
										,"for_row_id"	:' + convert(varchar(9), @shipment_row_id) + '
										,"kg"			:' + convert(varchar(20), iif(@shipment_kg > @stock_kg, @stock_kg, @shipment_kg)  ) + ' 
										}');



							-- ПРОВЕРКА: кол-во отгруженного = 0
							select @shipment_kg = c.shipment_kg - isnull(c.stock_shipment_kg, 0)
							from calc.plan_production_finished_products as c
							where c.stock_sort_row = @shipment_sort_row;

							-- если 0 выходим из цикла
							if @shipment_kg = 0
							begin
								set @stock_sort_row = null
								CONTINUE
							end;

			end;

			select @shipment_sort_row = min(stock_sort_row) -- ключ строки для которой считаем 			
			from calc.plan_production_finished_products
			where data_type in ('заявки', 'план продаж') 
				and reason_ignore_in_calculate is null
				and stock_sort_row > @shipment_sort_row;

end;

-- удаляем индексы
drop index NoCl_stock_sort_row on calc.plan_production_finished_products
drop index NoCl_stock on calc.plan_production_finished_products