USE [UMBPRM]

-- ##################################################
-- Rocky Assad (running time : 4 minutes)
-- 1/2/2012
-- Purpose: Migrate chaplains
-- Note: All load tables are cleaned up only if
-- the script completes. If it fails, you may have
-- to manually delete the tables priop to exec the
-- script again. It all depends on where it stopped.
-- ##################################################

PRINT '### Migrate chaplains - START ###'

-----------------------------------------------------
-- LOCAL VARS
-----------------------------------------------------
DECLARE @OrgNames TABLE
(
	OldName VARCHAR(200),
	NewName VARCHAR(200),
	PersonId INT
)
DECLARE @LocalContacts TABLE
(
	Id VARCHAR(25),
	GBHEM_DataCenter_Person_ID__c VARCHAR(25)
)
DECLARE @LocalAccounts TABLE
(
	Id VARCHAR(25),
	Name VARCHAR(255),
	Status__c VARCHAR(255)
)
DECLARE @RecordTypeId VARCHAR(25)
DECLARE @RecordTypeId_Default VARCHAR(25)

SET @RecordTypeId_Default = (SELECT Id FROM SALESFORCE...RecordType WHERE SObjectType = 'Account' AND Name = 'Default')
SET @RecordTypeId = (SELECT Id FROM SALESFORCE...RecordType WHERE Name = 'Chaplain Positions' AND SObjectType = 'Position__c')

INSERT INTO @OrgNames
	SELECT DISTINCT Organization, [Value], Person_Id FROM RDS_Boardwide_Clean_OrgNames

INSERT INTO @LocalAccounts
	SELECT Id, Name, Status__c  FROM SALESFORCE...Account WHERE RecordTypeId = @RecordTypeId_Default

INSERT INTO @LocalContacts
	SELECT Id, GBHEM_DataCenter_Person_ID__c FROM SALESFORCE...Contact WHERE GBHEM_DataCenter_Person_ID__c IN (SELECT person_id FROM chaplain)

-----------------------------------------------------
-- DROP LOAD TABLES IF ALREADY EXISTS
-----------------------------------------------------
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'Position__c_Load'))
BEGIN
    DROP TABLE Position__c_Load
END

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'Position__c_Delete'))
BEGIN
    DROP TABLE Position__c_Delete
END

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'Account_Load'))
BEGIN
    DROP TABLE Account_Load
END

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'Account_Delete'))
BEGIN
    DROP TABLE Account_Delete
END

-----------------------------------------------------
-- GENERATE LOAD TABLE FOR BULK INSERT & DELETE
-----------------------------------------------------
EXEC sf_generate 'Insert', 'SALESFORCE', 'Position__c_Load'
EXEC sf_generate 'Delete', 'SALESFORCE', 'Position__c_Delete'
EXEC sf_generate 'Insert', 'SALESFORCE', 'Account_Load'
EXEC sf_generate 'Delete', 'SALESFORCE', 'Account_Delete'

-----------------------------------------------------
-- DROP PROBLEM COLUMNS (NON-NULLS)
-----------------------------------------------------
ALTER TABLE Position__c_Load
DROP COLUMN OwnerId

ALTER TABLE Account_Load
DROP COLUMN UM_Study__pc, OwnerId, Advance_Course_Of_Study__pc, Course_of_Study__pc

-----------------------------------------------------
-- COMPARE LOAD TABLE WITH SF OBJECT
-----------------------------------------------------
EXEC SF_ColCompare 'Insert', 'SALESFORCE', 'Position__c_Load'
EXEC SF_ColCompare 'Delete', 'SALESFORCE', 'Position__c_Delete'
EXEC SF_ColCompare 'Insert', 'SALESFORCE', 'Account_Load'
EXEC SF_ColCompare 'Delete', 'SALESFORCE', 'Account_Delete'

-----------------------------------------------------
-- INSERT ALL CONTACTS FROM SF INTO LOCAL DEL TABLE
-----------------------------------------------------
INSERT INTO Position__c_Delete (Id)
	SELECT Id FROM SALESFORCE...Position__c WHERE RecordTypeId = @RecordTypeId

INSERT INTO Account_Delete (Id)
	SELECT Id FROM SALESFORCE...Account WHERE Migration_Key__c = 'positions.chaplain'

-----------------------------------------------------
-- DELETE ALL FROM SF
-----------------------------------------------------
EXEC SF_BulkOps 'Delete', 'SALESFORCE', 'Position__c_Delete'
EXEC SF_BulkOps 'Delete', 'SALESFORCE', 'Account_Delete'

-----------------------------------------------------
-- INSERT
-----------------------------------------------------
INSERT INTO Account_Load (Name, RecordTypeId, Migration_Key__c, Status__c)
	select
		distinct organization AS Name,
		@RecordTypeId_Default AS RecordTypeId,
		'positions.chaplain' AS Migration_Key__c,
		'Active' AS Status__c
	from
		chaplain
	where
		organization is not null
	and
		organization <> ''
	and
		organization not in (select distinct a.name from @LocalAccounts a)

EXEC SF_BulkOps 'Insert', 'SALESFORCE', 'Account_Load'

DELETE FROM @LocalAccounts
INSERT INTO @LocalAccounts
	SELECT Id, Name, Status__c  FROM SALESFORCE...Account WHERE RecordTypeId = @RecordTypeId_Default

INSERT INTO Position__c_Load (Contact__c, Position_Title__c, Preferred_Title__c, Start_Date__c, RecordTypeId, Organization_Name__c, Employment_Status__c)
		SELECT
			C.Id AS Contact__c,

			CASE
				WHEN ((ENDORSEMENT = 1 AND SETTING IN ('PC', 'PO', 'PR', 'SP')) OR (SETTING IN ('CH', 'CPE','GH','HO','LC','MFT','MH','RC','SA','VA','WP'))) THEN 'Civilian Endorsement'
				WHEN (((APPROVAL = 1 AND SETTING IN ('PO', 'SP', 'PC', 'PR')) OR (SETTING IN ('CAP', 'FI', 'TIMS')) OR (SETTING IN ('AR', 'NA') AND SETTING_STATUS = 'Vol'))) THEN 'Civilian Approval'
				WHEN ((SETTING IN ('AF', 'AR', 'NA') AND (SETTING_STATUS IN ('Active', 'Pending FT', 'Pending PT', 'Reserve', 'Guard', 'Applicant', 'Inactive') OR (SETTING_STATUS IS NULL)))) THEN 'Military Endorsement'
				WHEN (SETTING IN ('CC/AF', 'CC/AR', 'CC/NA')) THEN 'Military Chaplain Candidate'
			END AS Position_Title__c,
			LTRIM(RTRIM(ISNULL(B.POSITION_TITLE, ''))) AS Preferred_Title__c,
			A.EMPLOY_DATE AS Start_Date__c,
			@RecordTypeId AS RecordTypeId,
			D.Id AS Organization_Name__c,
			CASE
				WHEN (SETTING IN ('AF', 'AR', 'NA'))THEN
					(CASE
						WHEN SETTING_STATUS = 'Full' THEN 'Full Time'
						WHEN SETTING_STATUS = 'Part' THEN 'Part Time'
						WHEN SETTING_STATUS = 'Pend' THEN 'Pending'
						WHEN SETTING_STATUS = 'Pend-FT' THEN 'Pending Full Time'
						WHEN SETTING_STATUS = 'Pend-PT' THEN 'Pending Part Time'
						WHEN SETTING_STATUS = 'Vol' THEN 'Volunteer'
					END)
			END AS Employment_Status__c
		FROM
			CHAPLAIN_SETTING A
			JOIN CHAPLAIN B ON A.PERSON_ID = B.PERSON_ID
			JOIN @LocalContacts C ON A.person_id = C.GBHEM_DataCenter_Person_ID__c
			LEFT JOIN @LocalAccounts D ON B.organization = D.Name

-----------------------------------------------------
-- INSERT
-----------------------------------------------------
EXEC SF_BulkOps 'Insert', 'SALESFORCE', 'Position__c_Load'

-----------------------------------------------------
-- CLEAN UP
-----------------------------------------------------
IF(NOT EXISTS (SELECT * FROM Position__c_Load WHERE LTRIM(RTRIM(Error)) <> 'Operation Successful.'))
BEGIN
	DROP TABLE Position__c_Load
END

IF(NOT EXISTS (SELECT * FROM Position__c_Delete WHERE LTRIM(RTRIM(Error)) <> 'Operation Successful.'))
BEGIN
	DROP TABLE Position__c_Delete
END

PRINT '### Migrate chaplains - END ###'

EXEC roc_GrowlNotify 'Migration', 1, 'R002000', 0, 'positions_chaplain.sql'