use JATesting
go

-- 
-- Identify some test items that are already configured for custom counts.
-- Scenarios: Master Import, EntityItem import, Change in MMS?

-- Check initial behaviour of the master item import. This is the one that will overwrite the custom counts.
-- > Before deploying, run an item import with the test files and confirm that the CountType is reset.
-- > Also test a case when there is no custom counts set to ensure that the logic is still correct.

-- TEST 1: Update master record non-custom counts
-- 1.1 > Find item with custom counts (seems it's only set at the entity item level at present)
-- 1.2 > Check custom count configuration in MxC and DB (note CountBitsSpecific)
-- 1.3 > Run master import with
--		a) no updates to existing count bits
--		b) update to existing count bits (CountType must always be greater than 32)
-- 1.4 > Check custom count configurations did not change for both above

-- TEST 2: Update entity item record non-custom counts
-- Same as above

-- RUN THESE QUERIES MANUALLY AS NEEDED NOT BATCHED

-- ------------------------------------------------------------------------------------------------------------------------
-- TESTS
-- ------------------------------------------------------------------------------------------------------------------------

-- TEST 1: (Item - master)

-- Find an existing master item to update... 
-- > If none are found, run the item level qry and update the master record for testing (note down the current value)
select top 50 
	*
from tbItem i with(nolock)
where CountType > 31

-- Check the tbItem details.
select CountType, ItemCode, dbo.ConvertToBinary(CountType) as CountSpecificBits, ItemID, Description
	, * 
from tbItem with(nolock)
where ItemCode = '14361'

-- CURRENT CountType VALUE FOR ItemCode 14361 is 20 (Bits: 10100)

-- Update existing item record if none are set for custom counts.
-- > Enable CustomCount1 for this item.
update tbItem
set CountType = CountType + 32
where ItemCode = '14361'

-- Check the tbItem details to make sure they have changed.
select CountType, ItemCode, dbo.ConvertToBinary(CountType) as CountSpecificBits, ItemID, Description
	, * 
from tbItem with(nolock)
where ItemCode = '14361'


-- Cleanup.
-- > Set CountType back to the original value
update tbItem
set CountType = 20
where ItemCode = '14361'

-- TESTS 2: (Entity Item)

-- If CountTypeSpecific is > 31 then we have custum count bits at work...
-- entity item table setting
select top 50 
	ei.CountTypeSpecific, dbo.ConvertToBinary(CountTypeSpecific) as CountBitsSpecific, i.ItemId, i.ItemCode, e.StoreNumber
	, e.Entity, i.Description, ei.IsSuggestedItem, ei.IntroductionDate, ei.DiscontinueDate
--	, *
from tbEntityItem ei with(nolock)
join tbItem i with(nolock) on (i.ItemId = ei.ItemId)
join tbEntity e with(nolock) on (e.EntityID = ei.EntityID)
where CountTypeSpecific > 31
and i.ItemCode = '14361' 
order by i.ItemId

-- Check the tbItem details
select CountType, ItemID, Description, * from tbItem with(nolock)
where ItemCode = '14361'


-- ----------------------------------------------------------------------------------------------------------
-- Helpers
-- ----------------------------------------------------------------------------------------------------------

-- convert a bigint to a binary string 
declare @input bigint = 31
select dbo.ConvertToBinary(@input)



