use arizona
;


/*=======================================================================
	
	Galenica - End Of Year - Tasks in order to force the Invoice

	STEP 01 - Déclaration des variables
	STEP 02 - Création / suppression de la table PHOU historisée
	STEP 03 - Remplissage de la table PHOU historisée
	STEP 04 - Construction de la table de changements
	STEP 05 - Application des changements 
	STEP 06 - Rétablissement des anciennes valeurs

=======================================================================*/
/*=======================================================================
	STEP 01 - Déclaration des variables
=======================================================================*/
declare @force_delete int = 08,
        @mode         int = 1, /* 1 = Check | 2 = Just do IT !!! */
        @cmdsql       varchar(max),
		@today        datetime = getdate()
;
declare @day_delay     table (
        day_from       int,
		day_to         int,
		delivery_delay int,    
		closing_mode   int)
;
/*=======================================================================
	STEP 02 - Création / suppression de la table PHOU historisée
=======================================================================*/
if (EXISTS (select * 
              from arizonacust.INFORMATION_SCHEMA.TABLES 
             where TABLE_SCHEMA = 'dbo' 
               and TABLE_NAME   = 'PH_Organizational_Unit_history')) 
begin

	if @force_delete = 1 begin	
		select @cmdsql = 'drop table arizonacust.dbo.PH_Organizational_Unit_history'
		exec (@cmdsql)
	end 

end else begin 
	
	create table arizonacust.dbo.PH_Organizational_Unit_history (
	             PH_Organizational_Unit_history_id int identity (1,1),
				 phouh_year                        int,
				 phouh_PH_Organizational_Unit_guid uniqueidentifier,
				 phouh_PHOU_AB_delivery_delay      smallint,
				 phouh_PHOU_AB_closing_mode        smallint
				 )
end 
/*=======================================================================
	STEP 03 - Remplissage de la table PHOU historisée
=======================================================================*/
insert into arizonacust.dbo.PH_Organizational_Unit_history (
	   phouh_year,
	   phouh_PH_Organizational_Unit_guid,
	   phouh_PHOU_AB_delivery_delay,
	   phouh_PHOU_AB_closing_mode)
select year(@today),
	    PH_Organizational_Unit_guid,
	    PHOU_AB_delivery_delay,
	    PHOU_AB_closing_mode
   from dbo.Organizational_Unit ou (nolock)  
   join PH_Organizational_Unit P (nolock)  
     on PHOU_Organizational_Unit = Organizational_Unit_Id 
   join dbo.OU_store_history oush (nolock) 
     on oush.OU_store_history_GUID = (
 	    select top 1 oush2.OU_store_history_GUID
 	      from dbo.OU_store_history oush2 (nolock) 
 		 where isnull(oush2.OUSH_end_date,'2050-01-01') >= @today
 		   and oush2.OUSH_organizational_unit = ou.Organizational_unit_ID
 		 order by oush2.OUSH_start_date desc 
 	    )
   join dbo.Address_criteria AC (nolock)  
     on AC.ADCR_subsidiary = ou.OU_subsidiary  
    and AC.ADCR_address = ou.OU_address  
   join dbo.Criteria C (nolock)  
     on C.Criteria_ID = AC.ADCR_criteria  
    and C.CR_code = 'PROD'  
   join dbo.Criteria_Type Ct (nolock)  
     on Ct.Criteria_type_ID = C.CR_criteria_type  
    and Ct.CRT_code = 'ENV'

  where not exists (
	    select 1
		  from arizonacust.dbo.PH_Organizational_Unit_history p (nolock) 
		 where p.phouh_year = year(@today)
		   and p.phouh_PH_Organizational_Unit_guid = PH_Organizational_Unit_guid
		)
/*=======================================================================
	STEP 04 - Construction de la table de changements
=======================================================================*/
/* 1. From December 12th to December 17th (included) set the Delivery_delay to 10	*/
insert into @day_delay (day_from, day_to, delivery_delay,closing_mode) 
select                  12,       17,     10,             0

/* 2. From December 18th to December 24th (included) set the Delivery_delay to 5	*/
insert into @day_delay (day_from, day_to, delivery_delay,closing_mode) 
select                  18,       24,     5,             0

/* 3. From December 25th to December 31th (included) set the Delivery_delay to 2 ClosingMode   */
insert into @day_delay (day_from, day_to, delivery_delay,closing_mode) 
select                  25,       30,     2,             1

/*=======================================================================
	STEP 05 - Application des changements 
=======================================================================*/
if @mode = 2 begin 

	update phou   
	   set phou.PHOU_AB_delivery_delay = case when d.delivery_delay <= phou.PHOU_AB_delivery_delay                                            
											  then d.delivery_delay 
											  else PHOU.PHOU_AB_delivery_delay 
										 end,
		   phou.PHOU_AB_closing_mode   = case when phou.PHOU_AB_closing_mode = 1                                        
											  then PHOU.PHOU_AB_closing_mode                                         
											  else d.closing_mode                          
										 end 
	  from dbo.PH_organizational_unit PHOU (nolock)
	  join @day_delay d  
		on day(@today) between d.day_from and d.day_to
	   and month(@today) = 12
	 where PHOU_AB_delivery_delay is not null 
	   and (PHOU_AB_delivery_delay <> case when d.delivery_delay <= phou.PHOU_AB_delivery_delay                                            
										  then d.delivery_delay 
										  else PHOU.PHOU_AB_delivery_delay 
									 end
		or phou.PHOU_AB_closing_mode <> case when phou.PHOU_AB_closing_mode = 1                                        
											  then PHOU.PHOU_AB_closing_mode                                         
											  else d.closing_mode                          
										 end)


end else begin

	select phou.PH_organizational_unit_GUID,
	       phou.PHOU_AB_delivery_delay ,'-->', case when d.delivery_delay <= phou.PHOU_AB_delivery_delay                                            
											  then d.delivery_delay 
											  else PHOU.PHOU_AB_delivery_delay 
										 end,
		   phou.PHOU_AB_closing_mode   ,'-->', case when phou.PHOU_AB_closing_mode = 1                                        
											  then PHOU.PHOU_AB_closing_mode                                         
											  else d.closing_mode                          
										 end  
	  from dbo.PH_organizational_unit PHOU (nolock)
	  join @day_delay d  
		on day(@today) between d.day_from and d.day_to
	   and month(@today) = 12
	 where PHOU_AB_delivery_delay is not null
	    and (PHOU_AB_delivery_delay <> case when d.delivery_delay <= phou.PHOU_AB_delivery_delay                                            
									            	  then d.delivery_delay 
									            	  else PHOU.PHOU_AB_delivery_delay 
									             end
		or phou.PHOU_AB_closing_mode <> case when phou.PHOU_AB_closing_mode = 1                                        
											  then PHOU.PHOU_AB_closing_mode                                         
											  else d.closing_mode                          
										 end  )

end 
/*=======================================================================
	STEP 06 - Rétablissement des anciennes valeurs
=======================================================================*/ 
if month(@today) = 12 and day(@today) = 31 begin 

	if @mode = 2 begin 

		update phou
		   set phou.PHOU_AB_delivery_delay = phouh.phouh_PHOU_AB_delivery_delay,
		       phou.PHOU_AB_closing_mode   = phouh.phouh_PHOU_AB_closing_mode
          from dbo.PH_organizational_unit phou (nolock)
	      join arizonacust.dbo.PH_organizational_unit_history phouh (nolock)  
	        on phouh.phouh_PH_Organizational_Unit_guid = phou.PH_organizational_unit_GUID
		   and phouh.phouh_year = year(@today) 
 
	end else begin 

		select phou.PHOU_AB_delivery_delay ,'-->', phouh.phouh_PHOU_AB_delivery_delay,
		       phou.PHOU_AB_closing_mode   ,'-->', phouh.phouh_PHOU_AB_closing_mode
          from dbo.PH_organizational_unit phou (nolock)
	      join arizonacust.dbo.PH_organizational_unit_history phouh (nolock)  
	        on phouh.phouh_PH_Organizational_Unit_guid = phou.PH_organizational_unit_GUID
		   and phouh.phouh_year = year(@today) 

	end 

end