
-- test script for Copy Item Settings functionality
-- see: https://jira.hotschedules.com/browse/CFAMX-3348

-- run the queries as indicated.

-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- List first 50 entities that have no counts, these are good candidate destinations to test with as UPT settings should not be affected by counts
select top 50 Entity, EntityID from tbEntity e with(nolock)
where e.EntityTypeID = 4 -- stores
and (e.EntityID not in (
	select distinct(EntityID) from tbTransactionStockCount c with(nolock)
) and e.EntityID not in (
	select distinct(EntityID) from tbTransactionStockCountLocation c with(nolock)
))

-- Get the IDs of the two stores we are going to use for testing.
declare @sourceStoreName varchar(100) = 'kings'
	, @destinationStoreName varchar(100) = 'town ce'

select Entity as SourceStore, EntityId as SourceStoreID from tbEntity with(nolock) where Entity like '%' + @sourceStoreName + '%'
select Entity as DestinationStore, EntityId as DestinationStoreID from tbEntity with(nolock) where Entity like '%' + @destinationStoreName + '%'
go

-- Possible extra step:
-- if there are no stores with 0 counts, clear out any counts in a destination 'test' store
-- or preferrable create a new store for testing purposes to simulate the intended logic more accurately.
declare @destinationEntityId bigint = 4806 -- 00011 - Town Center at Aurora

select top 1000 * from tbTransactionStockCount with(nolock) where EntityID = @destinationEntityId
select top 1000 * from tbTransactionStockCountLocation with(nolock) where EntityID = @destinationEntityId

-- todo: delete counts
  --> tbTransactionStockCountLocation & tbTransactionStockCount

-- delete from tbTransactionStockCount where EntityId = @destinationEntityId
-- delete from tbTransactionStockCountLocation where EntityId = @destinationEntityId
go

-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- How many records match our copy criteria
declare @sourceEntityId bigint = 588 -- 01023 - Kingsland FSU
	, @destinationEntityId bigint = 4806 -- 00011 - Town Center at Aurora

select 
	count(*)
from tbEntityItem sei with(nolock)
--join tbEntity se with(nolock) on (se.EntityId = sei.EntityId)
join tbItem si with(nolock) on (si.ItemId = sei.ItemId)
join tbEntityItem dei with(nolock) on (dei.ItemID = sei.ItemID and dei.EntityID = @destinationEntityId)
--join tbEntity de with(nolock) on (de.EntityId = dei.EntityId)
--join tbItem di with(nolock) on (di.ItemId = dei.ItemId)
where sei.EntityID = @sourceEntityId
and sei.InUse = 1 and dei.InUse = 1
go

-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- take a snapshot of the inital state of the two stores
-- select all the in-use items from the source store that are also in-use in the destination store
declare @sourceEntityId bigint = 588 -- 01023 - Kingsland FSU
	, @destinationEntityId bigint = 4806 -- 00011 - Town Center at Aurora

select 
--	top 500 -- ** get a snap of first 500 items only or comment this line out for all
	sei.EntityID as SourceStoreId
	, dei.EntityID as DestinationEntityId
	, se.Entity as SourceStore
	, de.Entity as DestinationStore
	, sei.ItemId as SourceItemId
	, si.Description as SourceDescription
--	, dei.ItemId as DestinationItemId
--	, di.Description as DestinationDescription
	, sei.UPTQty as SourceUPTQty
	, sei.UPTAutoCalc as SourceUPTAutoCalc
	, sei.SafetyStockNumber as SourceSafetyStock
	, sei.SafetyStockAutoCalc as SourceSafetyStockAutoCalc
	, '<< >>' as ' << >>'-- destination columns
	, dei.UPTQty as DestinationUPTQty
	, dei.UPTAutoCalc as DestinationUPTAutoCalc
	, dei.SafetyStockNumber as DestinationSafetyStockNumber
	, dei.SafetyStockAutoCalc as DestinationSafetyStockAutoCalc
--	, *
from tbEntityItem sei with(nolock)
join tbEntity se with(nolock) on (se.EntityId = sei.EntityId)
join tbItem si with(nolock) on (si.ItemId = sei.ItemId)
join tbEntityItem dei with(nolock) on (dei.ItemID = sei.ItemID and dei.EntityID = @destinationEntityId)
join tbEntity de with(nolock) on (de.EntityId = dei.EntityId)
--join tbItem di with(nolock) on (di.ItemId = dei.ItemId)
where sei.EntityID = @sourceEntityId
and sei.InUse = 1 and dei.InUse = 1
--and dei.ItemId in (10,12,13,14,15,22)
go

-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- backup our destination data to a backup table so that we can restore the values if needed

-- create a backup table
/*
if not exists(select 1 from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'bkup_EntityItemUptSafetyStockSettings')
begin
	create table dbo.bkup_EntityItemUptSafetyStockSettings (EntityId bigint, ItemId bigint, UPTAutoCalc bit, UPTQty float, SafetyStockNumber float, SafetyStockAutoCalc bit)
end
else
	print 'Backup table bkup_EntityItemUptSafetyStockSettings already exists.'
go
*/

-- note: run this once per entity that you are using
-- if the temp table already exists, AND you want to clear out the values for the destination 
/*
declare @destinationEntityId bigint = 4806 -- 00011 - Town Center at Aurora
delete from bkup_EntityItemUptSafetyStockSettings where EntityId = @destinationEntityId
go
*/

-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- Back up the destination entity data to the table. NB to make sure there is no data already for the entity
declare @destinationEntityId bigint = 4806 -- 00011 - Town Center at Aurora

if exists (select 1 from bkup_EntityItemUptSafetyStockSettings where EntityId = @destinationEntityId)
	print 'Already contains data for the selected store, ensure you are copying data that has been reset'
else
begin
	insert into bkup_EntityItemUptSafetyStockSettings
	select EntityID
		, ItemID
		, UPTAutoCalc
		, UPTQty
		, SafetyStockNumber
		, SafetyStockAutoCalc
	from tbEntityItem with(nolock)
	where EntityID = @destinationEntityId
	and InUse = 1
--	and ItemId in (10,12,13,14,15,22)
end
go

select * from bkup_EntityItemUptSafetyStockSettings
order by ItemId
go

-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- simulate the SP copy logic for testing
/*
declare @sourceEntityId bigint = 588 -- 01023 - Kingsland FSU
	, @destinationEntityId bigint = 4806 -- 00011 - Town Center at Aurora

-- UPT columns
update dei set
	UPTQty = sei.UPTQty
	, UPTAutoCalc = 1
	, SafetyStockNumber = sei.SafetyStockNumber
	, SafetyStockAutoCalc = 1
from tbEntityItem dei with(nolock)
join tbEntityItem sei with(nolock) on (sei.ItemID = dei.ItemID and sei.EntityID = @sourceEntityId and sei.InUse = 1)
where dei.EntityID = @destinationEntityId
and dei.InUse = 1
--and dei.ItemId in (10,12,13,14,15,22)
go
*/
-- OR

-- run the upt copy in MxC or execute the query here:
/*
declare @sourceEntityId bigint = 588 -- 01023 - Kingsland FSU
	, @destinationEntityId bigint = 4806 -- 00011 - Town Center at Aurora

exec dbo.qryMMSEntityItem_CopyUPTSettings @sourceEntityId, @destinationEntityId
go
*/

-- run the ss copy in MxC or execute the query here:
/*
declare @sourceEntityId bigint = 588 -- 01023 - Kingsland FSU
	, @destinationEntityId bigint = 4806 -- 00011 - Town Center at Aurora

exec dbo.qryMMSEntityItem_CopySafetyStockSettings @sourceEntityId, @destinationEntityId
go
*/
-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- Compare the settings afterwards to the backed-up values
declare @destinationEntityId bigint = 4806 -- 00011 - Town Center at Aurora

select 
--	top 500 -- remove to see all 3000+ lines
	dei.EntityID as StoreId
	, b.EntityID as BackupEntityId
	, dei.ItemId as ItemId
	, i.Description as Description
	, dei.UPTQty as UPTQty
	, dei.UPTAutoCalc as UPTAutoCalc
	, dei.SafetyStockNumber as SafetyStockNumber
	, dei.SafetyStockAutoCalc as SafetyStockAutoCalc
	, '<< >>' as ' << >>'-- backup columns
	, b.UPTQty as BackupUPTQty
	, b.UPTAutoCalc as BackupUPTAutoCalc
	, b.SafetyStockNumber as BackupSafetyStockNumber
	, b.SafetyStockAutoCalc as BackupSafetyStockAutoCalc
--	, *
from tbEntityItem dei with(nolock)
join tbItem i with(nolock) on (i.ItemId = dei.ItemId)
join bkup_EntityItemUptSafetyStockSettings b with(nolock) on (b.ItemID = dei.ItemID and b.EntityID = dei.EntityID)
where dei.EntityId = @destinationEntityId
and dei.InUse = 1
--and dei.ItemId in (10,12,13,14,15,22)
go

-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- reset the destination UPTQty and UPTAutoCalc values for testing
declare @destinationEntityId bigint = 4806 -- 00011 - Town Center at Aurora

select b.UPTQty as BackupUPTQty
	, b.UPTAutoCalc as BackupUPTAutoCalc
	, b.SafetyStockNumber as BackupSafetyStockNumber
	, b.SafetyStockAutoCalc as BackupSafetyStockAutoCalc
	, '<<>>' as '<<>>'
	, ei.UPTQty
	, ei.UPTAutoCalc
	, ei.SafetyStockNumber
	, ei.SafetyStockAutoCalc
from tbEntityItem ei with(nolock)
join bkup_EntityItemUptSafetyStockSettings b on (b.EntityId = ei.EntityId and b.ItemId = ei.ItemId)
where b.EntityID = @destinationEntityId
and ei.ItemId in (10,12,13,14,15,22)
go

/*
declare @destinationEntityId bigint = 4806 -- 00011 - Town Center at Aurora

update ei set
	UPTQty = b.UPTQty
	, UPTAutoCalc = b.UPTAutoCalc
	, SafetyStockNumber = b.SafetyStockNumber
	, SafetyStockAutoCalc = b.SafetyStockAutoCalc
from tbEntityItem ei with(nolock)
join bkup_EntityItemUptSafetyStockSettings b on (b.EntityId = ei.EntityId and b.ItemId = ei.ItemId)
where b.EntityID = @destinationEntityId
--and ei.ItemId in (10,12,13,14,15,22)
go

*/

-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- MISC queries
-- --------------------------------------------------------------------------------------------------------------------------------------------------

-- check if there are counts in the destination store
declare @destinationEntityId bigint = 4806 -- 00011 - Town Center at Aurora
	, @hasCount bit = 0
	, @hasLocation bit = 0

if exists (select 1 from tbTransactionStockCount with(nolock) where EntityID = @destinationEntityId) set @hasCount = 1
if exists (select 1 from tbTransactionStockCountLocation with(nolock) where EntityID = @destinationEntityId) set @hasLocation = 1

if (@hasCount = 1 or @hasLocation = 1)
	print 'Destination store already has counts associated with it. You will need to clear out the counts first'
else
	print 'Destination store has no counts. You do not need to clear any count information.'
go


