-- **************************************************************
-- Script Name  	: UpdateFamilyGroupsFromExtraField.sql
-- Release Date	:  

-- ***************************************************************
-- Dependancies :
 
--	AE PM 4.60

--	22964_CommonScriptLoggingSystem.sql

-- ***************************************************************
-- Description of issue and symptoms:
  -- Update the new Family Group field with values from a nominated extra field
 
-- ***************************************************************
-- Brief description of what script does:
--	Appends a specified text to the end of the Family Group value (optional)
--	Deletes existing family groups (optional)
--	Inserts new Family Group records in the Family Group table if they do not already exist
--	Updates contact records with the new family group details if the contact does not have a
--	family group linked to it already.

-- ***************************************************************
-- Script execution instructions:

-- 1. Backup data
-- 2. Pre-run tests:
-- 	* 
-- 3. Run script(s)
-- 4. Backup data
-- 5. Check the script log for details
-- 6. Post-run tests
--	*

-- ********************************************************
Set NOCOUNT ON

Declare @ExtraFieldName varchar(255), @ExtraFieldId int
Declare @DeleteFamilyGroups int, @AppendTextToFamilyGroup int, @AppendText varchar(50)
--------- Script Paramenters -------------

Set @ExtraFieldName = 'Accounting Class'
Set @DeleteFamilyGroups = 0			--	0 = Do Not Delete existing family groups, 
									--  1 = delete family groups from Contacts and from FamilyGroup table
Set @AppendTextToFamilyGroup = 0	-- 0 = do not add standard text to end of family group name
									-- 1 = add standard text to end of family group name
Set @AppendText = 'Group'			-- the text to be appended to family group name


-- Script name
DECLARE @ScriptName		AS VARCHAR(255)
SET @ScriptName			= 'UpdateFamilyGroupsFromExtraField.sql'

-- Core variables - for version checks 
DECLARE @Result			AS INT	
DECLARE @LogID			AS INT

DECLARE @SectionName		AS VARCHAR(255)
SET @SectionName		=''

DECLARE @TextToWrite		AS VARCHAR(4000)
SET @TextToWrite		= ''

DECLARE @VIZ460			AS REAL
SET @VIZ460			=4.60

DECLARE @VIZ469			AS REAL
SET @VIZ469			=4.699

DECLARE @SQL_TRUE		AS INT
SET @SQL_TRUE			= 1

-- Start of script execution 
EXEC @LogID = LogScriptHeader @ScriptName
IF @LogID>0
BEGIN
	EXEC @Result = IsDatabaseVersionWithin @VIZ460, @VIZ469	
	IF @Result = @SQL_TRUE
	BEGIN

		Set @ExtraFieldId = (Select ExtraFieldId
							From ExtraField
							Where FieldName = @ExtraFieldName)

		If @ExtraFieldId is null
			Begin
				SET @TextToWrite =   'Invalid extra field - ' + @ExtraFieldName
				EXEC LogScriptError @LogID, @TextToWrite
			End
		Else
			Begin
				If @DeleteFamilyGroups = 1			
--											Delete family groups if option selected
					Begin
						Update Contact
						Set FamilyGroupId = NULL
						Where FamilyGroupId is not null

						SET @TextToWrite =  'Number of contacts/clients with Family Group removed = ' + Ltrim(RTrim(STR(@@Rowcount))) 
						EXEC LogScriptDetail @LogID, '', @TextToWrite 

						Delete  FamilyGroup
						Where FamilyGroupDesc <> '-UNSPECIFIED-'

						SET @TextToWrite =  'Number of Family Groups removed = ' + Ltrim(RTrim(STR(@@Rowcount))) 
						EXEC LogScriptDetail @LogID, '', @TextToWrite 
					End;

--	Insert new family group records

				Insert Into FamilyGroup (FamilyGroupDesc)
				Select Distinct  Case When @AppendTextToFamilyGroup = 1 Then LTrim(RTrim(EV.[Value])) + ' ' + @AppendText
																		Else LTrim(RTrim(EV.[Value])) End
				From ExtraValue EV 
				Inner Join ExtraField EF on EV.ExtraFieldId = EF.ExtraFieldId
				Where EV.ExtraFieldId = @ExtraFieldId and 
						(Case When @AppendTextToFamilyGroup = 1 
							Then LTrim(RTrim(EV.[Value])) + ' ' + @AppendText
							Else LTrim(RTrim(EV.[Value])) End) not in (Select FamilyGroupDesc From FamilyGroup)

				SET @TextToWrite =   'Number of Family Groups added = ' + Ltrim(RTrim(STR(@@Rowcount))) 
				EXEC LogScriptDetail @LogID, '', @TextToWrite 

--	Update contact records with family group

				Update Contact
				Set FamilyGroupId = F.FamilyGroupId
				From Contact C
				Inner Join ExtraContact EC on C.ContactId = EC.ContactId
				Inner Join ExtraValue EV on EC.ExtraValueId = EV.ExtraValueId
				Inner Join ExtraField EF on EV.ExtraFieldId = EF.ExtraFieldId
				Inner Join FamilyGroup F on (Case When @AppendTextToFamilyGroup = 1 
												Then LTrim(RTrim(EV.[Value])) + ' ' + @AppendText
												Else LTrim(RTrim(EV.[Value])) End) = F.FamilyGroupDesc
				Where EF.FieldName = @ExtraFieldName
				and C.FamilyGroupId is null

				SET @TextToWrite =   'Number of contacts/clients with Family Group added = ' + Ltrim(RTrim(STR(@@Rowcount))) 
				EXEC LogScriptDetail @LogID, '', @TextToWrite 

			End
	END
	ELSE
		EXEC LogScriptError @LogID, 'Database version is outside of acceptable range for this script.'
		
	EXEC LogScriptFooter @LogID
End