
-- TEST for duplicate item recovery on EXEC [dbo].[Object_Import]

SET NOCOUNT ON


-- PREPARE TEST CASES
IF 1=1
BEGIN

	delete from Objects_tbl where ID IN ('2c89ff6a-3b09-4b72-9be0-7aebf01af42e','2c89ff6a-3b09-4b72-9be0-7aebf01af43e','2c89ff6a-3b09-4b72-9be0-7aebf01af44e','2c89ff6a-3b09-4b72-9be0-7aebf01af45e')
	delete from Objects_tbl where ObjectPuid = 'ObjectPuid-#Debug-NEW' OR ObjectName = 'ObjectName-#Debug' OR ObjectName2 = 'NEW-OBJECT'
	delete from dbo.JobControl_ObjectsProcessData_tbl where Objects_tbl_ID IN ('2c89ff6a-3b09-4b72-9be0-7aebf01af42e','2c89ff6a-3b09-4b72-9be0-7aebf01af43e','2c89ff6a-3b09-4b72-9be0-7aebf01af44e','2c89ff6a-3b09-4b72-9be0-7aebf01af45e') 

	insert into Objects_tbl (ID ,  ObjectClass ,                          ObjectPuid ,          ObjectName,         ObjectName2,   ObjectFromSiteID  , ObjectLastModDate)
					VALUES ('2c89ff6a-3b09-4b72-9be0-7aebf01af42e', 'I', 'ObjectPuid-#Debug1', 'ObjectName-#Debug', 'ObjectName-1', 'TC11ADDPLM-10-VMtc91' , GetDate())

	insert into Objects_tbl (ID ,  ObjectClass ,                          ObjectPuid ,          ObjectName,         ObjectName2,   ObjectFromSiteID  , ObjectLastModDate)
					VALUES ('2c89ff6a-3b09-4b72-9be0-7aebf01af43e', 'I', 'ObjectPuid-#Debug2', 'ObjectName-#Debug', 'ObjectName-2', 'TC11ADDPLM-10-VMtc91' , GetDate()+1)

	insert into Objects_tbl (ID ,  ObjectClass ,                          ObjectPuid ,          ObjectName,         ObjectName2,   ObjectFromSiteID  , ObjectLastModDate)
					VALUES ('2c89ff6a-3b09-4b72-9be0-7aebf01af44e', 'I', 'ObjectPuid-#Debug3', 'ObjectName-#Debug', 'ObjectName-3', 'TC11ADDPLM-10-VMtc91' , GetDate()+2)

	insert into Objects_tbl (ID ,  ObjectClass ,                          ObjectPuid ,          ObjectName,         ObjectName2,   ObjectFromSiteID  , ObjectLastModDate)
					VALUES ('2c89ff6a-3b09-4b72-9be0-7aebf01af45e', 'I', 'ObjectPuid-#Debug4', 'ObjectName-#Debug', 'ObjectName-4', 'TC11ADDPLM-11x' , GetDate()+3)


	INSERT INTO
	  dbo.JobControl_ObjectsProcessData_tbl
	(
	  Id,
	  Objects_tbl_ID,
	  ProcessPacketName,
	  ProcessState,
	  ProcessTimeStart,  ProcessTimeEnd,  ProcessCMDLine,  ProcessResultCode,  ProcessResultMsg,  ProcessResultExtendedData,
	  ProcessResultHasWarning,
	  ProcessResultLink,  ProcessOnComputer,  ProcessProcStateCol,  ProcessPrgUsedVer,  ProcessObjectSaveVersion,
	  Row_isLastRes,
	  TaskScheduler_tbl_ID,  ProcessCategory
	)
	VALUES 

	(
	   NEWID(),
	  '2c89ff6a-3b09-4b72-9be0-7aebf01af43e',
	  'ProcessPacketName',
	  'D.ERR',
	  GETDATE(),  GetDate()+1,  'ProcessCMDLine',  'XX',  'ProcessResultMsg',  'ProcessResultExtendedData',
	  0, --'ProcessResultHasWarning,
	  'ProcessResultLink',  'ProcessOnComputer',  'ProcStateCol01',  'ProcessPrgUsedVer',  'ProcessObjectSaveVersion',
	  0, --'Row_isLastRes,
	  null, null 
	)

	, (
	   NEWID(),
	  '2c89ff6a-3b09-4b72-9be0-7aebf01af43e',
	  'ProcessPacketName',
	  'D.OK',
	  GETDATE(),  GetDate()+2,  'ProcessCMDLine',  'XX',  'ProcessResultMsg',  'ProcessResultExtendedData',
	  0, --'ProcessResultHasWarning,
	  'ProcessResultLink',  'ProcessOnComputer',  'ProcStateCol01',  'ProcessPrgUsedVer',  'ProcessObjectSaveVersion',
	  0, --'Row_isLastRes,
	  null, null 
	)

	,(
	   NEWID(),
	  '2c89ff6a-3b09-4b72-9be0-7aebf01af42e',
	  'ProcessPacketName',
	  'D.ERR2',
	  GETDATE(),  GetDate()+1,  'ProcessCMDLine',  'XX',  'ProcessResultMsg',  'ProcessResultExtendedData',
	  0, --'ProcessResultHasWarning,
	  'ProcessResultLink',  'ProcessOnComputer',  'ProcStateCol02',  'ProcessPrgUsedVer',  'ProcessObjectSaveVersion',
	  0, --'Row_isLastRes,
	  null, null 
	)

	, (
	   NEWID(),
	  '2c89ff6a-3b09-4b72-9be0-7aebf01af42e',
	  'ProcessPacketName',
	  'D.OK2',
	  GETDATE(),  GetDate()+2,  'ProcessCMDLine',  'XX',  'ProcessResultMsg',  'ProcessResultExtendedData',
	  0, --'ProcessResultHasWarning,
	  'ProcessResultLink',  'ProcessOnComputer',  'ProcStateCol02',  'ProcessPrgUsedVer',  'ProcessObjectSaveVersion',
	  0, --'Row_isLastRes,
	  null, null 
	)

	, (
	   NEWID(),
	  '2c89ff6a-3b09-4b72-9be0-7aebf01af44e',
	  'ProcessPacketName',
	  'D.WRN',
	  GETDATE(),  GetDate()+2,  'ProcessCMDLine',  'XX',  'ProcessResultMsg',  'ProcessResultExtendedData',
	  0, --'ProcessResultHasWarning,
	  'ProcessResultLink',  'ProcessOnComputer',  'ProcStateCol01',  'ProcessPrgUsedVer',  'ProcessObjectSaveVersion',
	  0, --'Row_isLastRes,
	  null, null 
	);

END

           

-- EXECUTE TESTS




SELECT ITEM.Id AS I_DbId
     , ITEM.ObjectPuid AS I_PUID
	 , ITEM.ObjectName AS I_Name
	 , ITEM.ObjectName2 AS I_Name2
	 , ITEM.ObjectClass AS I_ClassTyp
	 , ITEM.ObjectFromSiteID AS I_FromSiteID
	 , ITEM.ObjectLastModDate AS I_LastModDate
	 , RES.Id AS RES_DbId
	 , RES.ProcessState
	 , RES.ProcessResultHasWarning
	 , RES.Row_isLastRes
	 , ITEM.ProcStateCol01 AS I_ProcStateCol01
	 , ITEM.ProcStateCol02 AS I_ProcStateCol02
FROM Objects_tbl ITEM
LEFT JOIN dbo.JobControl_ObjectsProcessData_tbl RES ON RES.Objects_tbl_ID=ITEM.ID
WHERE ITEM.ObjectPuid LIKE 'ObjectPuid-#Debug%' OR ITEM.ObjectName LIKE 'ObjectPuid-#Debug%' AND ITEM.ObjectFromSiteID = 'TC11ADDPLM-10-VMtc91'
ORDER BY ITEM.ObjectName2, RES.ProcessTimeStart


DECLARE @importedObjectId uniqueidentifier
DECLARE @newCreateDate DATETIME = GETDATE()

-- Test 1: Neue PUID mit ObjectName Dubletten
DECLARE @newObjectPuid NVARCHAR(32) = 'ObjectPuid-#Debug2-New'; DECLARE @newObjectName NVARCHAR(32) = 'ObjectName-#Debug';

-- Test 2: Vorhandene PUID mit geändertem ObjectName
--DECLARE @newObjectPuid NVARCHAR(32) = 'ObjectPuid-#Debug2'; DECLARE @newObjectName NVARCHAR(32) = 'ObjectName-#Debug-ALTER';

-- Test 3: Neue PUID ohne Dublettern
--DECLARE @newObjectPuid NVARCHAR(32) = 'ObjectPuid-#Debug-NEW'; DECLARE @newObjectName NVARCHAR(32) = 'ObjectName-#Debug-NEW';


EXEC [dbo].[Object_Import]     @ObjectPuid = @newObjectPuid,
                               @ObjectName = @newObjectName,
                               @ObjectFromSiteID = 'TC11ADDPLM-10-VMtc91',
                               @ObjectOwningSiteID = 'TC11ADDPLM-10-VMtc91',
                               @ObjectClass = 'I',
                               @ObjectCreateDate = @newCreateDate,

							   @ObjectName2 = 'NEW-OBJECT',
							   @ObjectDescription = '',
							   @ObjectCliName = '',
							   @ObjectType = 'UGMASTER',
							   @ObjectOwner = '',
							   @ObjectGroup = '',
							   @ObjectLastModUser = '',
							   @ObjectFs_HashCode = '',
							   @ObjectSaveVersion = '',
							   @ObjectUgMaster_Type = '',
                               @ObjectUgMaster_APLevels = 0,
                               @ObjectUgMaster_CompCount = 0,
                               @ObjectUgMaster_CompCountUnique = 0,
                               @ObjectUgMaster_FsHitCount = 0,
							   @ObjectNote = '',
							   @ObjectPrjIds = '',
							   @ImportObjectNewProcStateMask = '',
							   @ImportObjectUpdProcStateMask = '',
							   @ImportObjectErr_p = '',
							   @DoUpdateRank = 0,
							   
							   @ObjectIsMFKObj = 0,

							   @IrId = NULL,
							   @IrPuid = NULL,

                               @ObjectId = @importedObjectId  Output


SELECT ITEM.Id AS I_DbId
     , ITEM.ObjectPuid AS I_PUID
	 , ITEM.ObjectName AS I_Name
	 , ITEM.ObjectName2 AS I_Name2
	 , ITEM.ObjectClass AS I_ClassTyp
	 , ITEM.ObjectFromSiteID AS I_FromSiteID
	 , ITEM.ObjectLastModDate AS I_LastModDate
	 , RES.Id AS RES_DbId
	 , RES.ProcessState
	 , RES.ProcessResultHasWarning
	 , RES.Row_isLastRes
	 , ITEM.ProcStateCol01 AS I_ProcStateCol01
	 , ITEM.ProcStateCol02 AS I_ProcStateCol02
FROM Objects_tbl ITEM
LEFT JOIN dbo.JobControl_ObjectsProcessData_tbl RES ON RES.Objects_tbl_ID=ITEM.ID
WHERE ITEM.ObjectPuid LIKE 'ObjectPuid-#Debug%' OR ITEM.ObjectName LIKE 'ObjectPuid-#Debug%' AND ITEM.ObjectFromSiteID = 'TC11ADDPLM-10-VMtc91'
ORDER BY ITEM.ObjectName2, RES.ProcessTimeStart




