/*

This script is designed to report issues in databases where the Pentana / Retain records are out of sync.

It:

- creates 2 reporting tables in the Retain database
- identifies the state that records are in

It is necessary to change the database names before running the script - find and replace on RetainDB and PentanaDB

Records are reported as follows:

Status											Action
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Matched											These records all match
Name will update when robot runs				The ID and GUID match but the audit name and job name don't match - the next robot run will sync the names
Record will add to Retain when robot runs		Record will add to Retain next time the robot runs


GUID mismatching, requires update				The fix script will update the GUID
Pentana-only record with erroneous Retain ID	The fix script will set the RetainID to 0, and the robot will push the audit into Retain at next run
Missing GUID									The fix will add the GUID to the Retain record

There are three status values that require further investigation:

Check:  Name and GUID vary						While the Retain ID's match, the GUID and names don't.  These should be checked with the client
												If in the real world the Pentana audit and the Retain job are the same thing, then a fix script should
												update Retain GUID with the Pentana GUID												
Check:  in Retain only							These records exist only in Retain.  If they need hooking up to Pentana records then this must be undertaken 
												as a separate scripting exercise

Duplicate Retain ID								These are Pentana records which have the same Retain ID, so two Pentana records match one Retain record
												This is improper.  A custom fix would be required.


*/

-- ##################################################################
-- Part A:  Reporting on Audits / jobs
-- ##################################################################

-- Make an overall table of all audit / job records, linked on Retain ID (as this is how the robot will see things)

Use RetainDB
GO

IF OBJECT_ID('dbo.#Allaudits', 'U') IS NOT NULL 
  DROP TABLE dbo.#Allaudits; 
  
SELECT     PentanaDB.dbo.Audit.ID, PentanaDB.dbo.Audit.GUID, PentanaDB.dbo.Audit.Name, PentanaDB.dbo.Audit.RetainID, RetainDB.dbo.JOB.JOB_ID, RetainDB.dbo.JOB.JOB_DESCR, RetainDB.dbo.JOB.JOB_TEXT1, RetainDB.dbo.JOB.JOB_DELETED_BIT
INTO #Allaudits
FROM         PentanaDB.dbo.Audit FULL OUTER JOIN
                      RetainDB.dbo.JOB ON PentanaDB.dbo.Audit.RetainID = RetainDB.dbo.JOB.JOB_ID
                      
Alter table #Allaudits add Detail varchar(1000)


-- Records where the IDs, GUIDs and names are all the same are all good:
Update #Allaudits set Detail = 'Matched'
where RetainID = JOB_ID
AND Name = JOB_DESCR
AND GUID = JOB_TEXT1

-- Records where the IDs, GUIDs all the same will change when the robot runs and this is probably OK
Update #Allaudits set Detail = 'Name will update when robot runs'
where RetainID = JOB_ID
AND Name <> JOB_DESCR
AND GUID = JOB_TEXT1


-- Records where the IDs, GUIDs and names are all in Pentana but not Retain
Update #Allaudits set Detail = 'Record will add to Retain when robot runs'
where RetainID = 0
AND Name is not null
AND GUID is not null
AND JOB_ID = 0
AND JOB_TEXT1 is null
AND JOB_DESCR is null


-- Records where no Pentana IDs, record is Retain only
Update #Allaudits set Detail = 'Check:  in Retain only'
where ID is null


-- Records where the IDs, Name both the same with a mismatching GUID
Update #Allaudits set Detail = 'GUID mismatching, requires update'
where RetainID = JOB_ID
AND Name = JOB_DESCR
AND GUID <> JOB_TEXT1


-- Records where the IDs, GUIDs and names are all in Pentana but not Retain BUT a Retain ID is present
Update #Allaudits set Detail = 'Pentana-only record with erroneous Retain ID'
where RetainID <> 0
AND Name is not null
AND GUID is not null
AND JOB_ID is null
AND JOB_TEXT1 is null
AND JOB_DESCR is null

-- Records where the IDs, GUIDs all the same will change when the robot runs and this is probably OK
Update #Allaudits set Detail = 'Check:  Name and GUID vary'
where RetainID = JOB_ID
AND Name <> JOB_DESCR
AND GUID <> JOB_TEXT1


-- Records where the IDs, GUIDs all the same will change when the robot runs and this is probably OK
Update #Allaudits set Detail = 'Missing GUID'
where RetainID = JOB_ID
AND Name = JOB_DESCR
AND JOB_TEXT1 is null


-- Though if any Retain ID's are duplicated on the Pentana side, this is an issue
Update #Allaudits set Detail = 'Duplicate Retain ID' 
Where RetainID in 
(Select RetainId 
from #Allaudits
Where RetainID <> 0
Group by RetainID
Having (count(RetainID) > 1))



-- ##################################################################
-- Part B:  Reporting on Persons / Resources
-- ##################################################################

-- Make an overall table of all person / res records, linked on Retain ID (as this is how the robot will see things)


IF OBJECT_ID('dbo.#AllPeople', 'U') IS NOT NULL 
  DROP TABLE dbo.#AllPeople; 
  
SELECT     PentanaDB.dbo.Person.TypeID, PentanaDB.dbo.Person.ID, PentanaDB.dbo.Person.GUID, PentanaDB.dbo.Person.Name, PentanaDB.dbo.Person.RetainID, RetainDB.dbo.RES.RES_ID, RetainDB.dbo.RES.RES_DESCR, RetainDB.dbo.RES.RES_TEXT1, RetainDB.dbo.RES.RES_DELETED_BIT
INTO #AllPeople
FROM         PentanaDB.dbo.Person FULL OUTER JOIN
                      RetainDB.dbo.RES ON PentanaDB.dbo.Person.RetainID = RetainDB.dbo.RES.RES_ID
                      
Alter table #AllPeople add Detail varchar(1000)


-- Records where the IDs, GUIDs and names are all the same are all good:
Update #AllPeople set Detail = 'Matched'
where RetainID = RES_ID
AND Name = RES_DESCR
AND GUID = RES_TEXT1

-- Records where the IDs, GUIDs all the same will change when the robot runs and this is probably OK
Update #AllPeople set Detail = 'Name will update when robot runs'
where RetainID = RES_ID
AND Name <> RES_DESCR
AND GUID = RES_TEXT1


-- Records where the IDs, GUIDs and names are all in Pentana but not Retain
Update #AllPeople set Detail = 'Record will add to Retain when robot runs (if Staff not Contact)'
where RetainID = 0
AND Name is not null
AND GUID is not null
AND RES_ID = 0
AND RES_TEXT1 is null
AND RES_DESCR is null


-- Records where no Pentana IDs, record is Retain only
Update #AllPeople set Detail = 'Check:  in Retain only'
where ID is null


-- Records where the IDs, Name both the same with a mismatching GUID
Update #AllPeople set Detail = 'GUID mismatching, requires update'
where RetainID = RES_ID
AND Name = RES_DESCR
AND GUID <> RES_TEXT1


-- Records where the IDs, GUIDs and names are all in Pentana but not Retain BUT a Retain ID is present
Update #AllPeople set Detail = 'Pentana-only record with erroneous Retain ID'
where RetainID <> 0
AND Name is not null
AND GUID is not null
AND RES_ID is null
AND RES_TEXT1 is null
AND RES_DESCR is null


-- Records where the IDs, GUIDs all the same will change when the robot runs and this is probably OK
Update #AllPeople set Detail = 'Check:  Name and GUID vary'
where RetainID = RES_ID
AND Name <> RES_DESCR
AND GUID <> RES_TEXT1


-- Records where the IDs, names are the same but GUIDs are missing
Update #AllPeople set Detail = 'Missing GUID'
where RetainID = RES_ID
AND Name = RES_DESCR
AND RES_TEXT1 is null


-- Though if any Retain ID's are duplicated on the Pentana side, this is an issue
Update #AllPeople set Detail = 'Duplicate Retain ID' 
Where RetainID in 
(Select RetainId 
from #AllPeople
Where RetainID <> 0
Group by RetainID
Having (count(RetainID) > 1))


-- ##################################################################
-- Part C:  Report on staff / resources
-- ##################################################################

/*
-- To see all audit records
Select * from #Allaudits order by detail

-- To see all peoplerecords
Select * from #AllPeople order by detail

-- To see all audit records that require further investigation.
select * from #Allaudits where detail not in (
'Matched',
'Name will update when robot runs',
'Record will add to Retain when robot runs',
'GUID mismatching, requires update'
)
Order by Detail

-- To see all staff records that require further investigation
select * from #AllPeople where detail not in (
'Matched',
'Name will update when robot runs',
'Record will add to Retain when robot runs',
'GUID mismatching, requires update'
)
Order by Detail

-- Look out for records where DELETED_BIT = 1, because these will genuinely be orphaned as soft deleted


*/