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  

create table calc.plan_production_finished_products
(

		 row_id							INT					NOT NULL	IDENTITY(1,1)	
		,data_type						varchar(100)		NOT NULL
		,name_system					varchar(7)				NULL
		,path_file						varchar(300)			NULL
		,date_file						datetime				NULL
		,download_user					varchar(50)				NULL
		,dt_tm_insert					datetime				NULL
									
		,reason_ignore_in_calculate		VARCHAR(300)			NULL

		,sap_id							BIGINT					NULL
		,sap_id_text					as convert(varchar(24), FORMAT(sap_id, '000000000000000000000000'))
		,sap_id_original				BIGINT					NULL
		,sap_id_original_text			as convert(varchar(24), FORMAT(sap_id_original, '000000000000000000000000'))
		,sap_id_expiration_date_in_days	SMALLINT				NULL

		,stuffing_id					VARCHAR(40)				NULL
		,stuffing_id_box				VARCHAR(40)				NULL

		,position_dependent_id			INT						NULL	
		,individual_marking_id			TINYINT					NULL	
		,article_nomenclature			VARCHAR(20)				NULL	
		,article_packaging				VARCHAR(25)				NULL	
		,product_finished_id			decimal(14, 0)			NULL


		--- остатки ---
		,stock_sort_row					INT						NULL -- используется для расчетов
		,stock_warehouse_name			VARCHAR(100)			NULL	
		,stock_storage_area_name		VARCHAR(100)			NULL	
		,stock_branch_name				VARCHAR(100)			NULL
		,stock_production_date			DATETIME				NULL
		,stock_on_date					DATETIME				NULL
		,stock_expiration_date			DATETIME				NULL
		,stock_kg						dec(11,5)				NULL

		,stock_current_KOS				as case when stock_production_date >= stock_expiration_date then null  
												when stock_production_date >  stock_on_date		    then null
												when stock_expiration_date <= stock_on_date		    then 0.000000	
												when stock_production_date	= stock_on_date			then 1
												else DATEDIFF(day, stock_on_date, stock_expiration_date) * 1.0 / DATEDIFF(day, stock_production_date, stock_expiration_date) end

		,stock_KOS_in_day				as case when stock_production_date >= stock_expiration_date then null	
												when stock_production_date >  stock_on_date			then null
												else 1.0 / DATEDIFF(day, stock_production_date, stock_expiration_date) end	
													



		--- набивки ---
		,stuffing_sort_row						INT					NULL -- используется для расчетов
		,stuffing_production_name				VARCHAR(100)		NULL
		,stuffing_production_date_from			DATETIME			NULL
		,stuffing_production_date_to			DATETIME			NULL
		,stuffing_available_date				DATETIME			NULL
		,stuffing_expiration_date				DATETIME			NULL
		,stuffing_maturation					tinyint				NULL
		,stuffing_maturation_and_packaging		tinyint				NULL
		,stuffing_transit_from_production_days	tinyint				null	

		,stuffing_begin_production_date_from	DATETIME			NULL -- дата с которой можем закладывать
		,stuffing_begin_production_date_to		as stuffing_begin_production_date_from + stuffing_maturation
		,stuffing_begin_available_date			as stuffing_begin_production_date_from + stuffing_maturation_and_packaging + stuffing_transit_from_production_days

		,stuffing_kg							dec(11,5)			NULL

		,stuffing_current_KOS				as case when stuffing_production_date_to >= stuffing_expiration_date	then null  
													when stuffing_production_date_to >  stuffing_available_date		then null
													when stuffing_expiration_date	 <= stuffing_available_date		then 0.000000
													when stuffing_production_date_to  = stuffing_available_date		then 1		
													else DATEDIFF(day, stuffing_available_date, stuffing_expiration_date) * 1.0 / DATEDIFF(day, stuffing_production_date_to, stuffing_expiration_date) end

		,stuffing_KOS_in_day				as case when stuffing_production_date_to >= stuffing_expiration_date then null	
													when stuffing_production_date_to >  stuffing_available_date	 then null
													else 1.0 / DATEDIFF(day, stuffing_production_date_to, stuffing_expiration_date) end	




		-- заявки и план продаж
		,shipment_sales_channel_id		TINYINT					NULL
		,shipment_sales_channel_name	VARCHAR(25)				NULL
		,shipment_branch_id				VARCHAR(20)				NULL	
		,shipment_branch_name			VARCHAR(100)			NULL	
		,shipment_customer_id			VARCHAR(20)				NULL
		,shipment_customer_name			VARCHAR(100)			NULL

		,shipment_priority				TINYINT					NULL
		,shipment_priority_for_stuffing tinyint					NULL -- для набивок когда сортировку делаем по приоритету
		,shipment_date					DATETIME				NULL
		,shipment_min_KOS				DEC(7,6)				NULL
		,shipment_kg					dec(11,5)				NULL

		-- расчетные поля

		,stock_shipment_kg				dec(11,5)				NULL
		,stock_net_need					as case when data_type in ('ЗАЯВКИ', 'ПЛАН ПРОДАЖ') then nullif( shipment_kg - isnull(stock_shipment_kg, 0)   , 0) end

		,stuffing_marking_kg			dec(11,5)				NULL			   
		,stuffing_shipment_kg			dec(11,5)				NULL	-- кол-во которое уже отгружено из набивки включая маркировку
		,stuffing_net_need				as case when data_type in ('ЗАЯВКИ', 'ПЛАН ПРОДАЖ') then nullif(   shipment_kg - isnull(stock_shipment_kg, 0) - isnull(stuffing_shipment_kg, 0)   , 0) end

)