Develop: Customer Credit Save

CREATE PROC [demo].[Northwind_Credit_Save_MyName]

	-- System Params not in formula
	@Interject_RequestContext nvarchar(max)
	,@Interject_ReturnError varchar(2000) OUTPUT
	,@TestMode bit = 0 -- used only for testing the stored procedure directly. It will show more results when set to 1.

AS

/*
==================================================================================
Test Code Example
Declare @Interject_ReturnError varchar(2000)

Execute [demo].[Northwind_Credit_Save]
	@Interject_RequestContext = '<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<RequestContext>
	<ExcelVersion>16.0</ExcelVersion>
	<IdsVersion>2.3.22.0</IdsVersion>
	<FileName>Interject_CustomerDemo_v1.4.xlsx</FileName>
	<FilePath></FilePath>
	<TabName>CustomerCredits</TabName>
	<CellRange>H11</CellRange>
	<SourceFunction>Save</SourceFunction>
	<UtcOffset>-7</UtcOffset>
	<ColDefItems>
		<Value Row="4" Column="2">
			<Name>CustomerID</Name>
		</Value>
		<Value Row="4" Column="8">
			<Name>CreditLimit</Name>
		</Value>
		<Value Row="4" Column="10">
			<Name>AccountFreeze</Name>
		</Value>
		<Value Row="4" Column="11">
			<Name>AccountNotes</Name>
		</Value>
	</ColDefItems>
	<ResultDefItems>
		<Value Row="6" Column="12">
			<Name>MessageToUser</Name>
		</Value>
	</ResultDefItems>
	<RowDefItems>
		<Value Row="27" Column="2" ColumnName="CustomerID">
			<Name>BOTTM</Name>
		</Value>
	</RowDefItems>
	<UserContext>
	<MachineLoginName></MachineLoginName>
	<MachineName></MachineName>
	<FullName></FullName>
	<UserId>UFR62JQWx4</UserId>
	<ClientId>CgCfW9qi</ClientId>
	<LoginName></LoginName>
	<LoginAuthTypeId>10</LoginAuthTypeId>
	<LoginDateUtc></LoginDateUtc>
	<UserRoles>
		<Role></Role>
	</UserRoles>
</UserContext>
	<UserContextEncrypted>Encrypted only through interject api protocol, not direct connection</UserContextEncrypted>
	<XMLDataToSave>
 <c Column="Row" OrigValue="Row" />
 <c Column="CustomerID" OrigValue="CustomerID" />
 <c Column="CreditLimit" OrigValue="CreditLimit" />
 <c Column="AccountFreeze" OrigValue="AccountFreeze" />
 <c Column="AccountNotes" OrigValue="AccountNotes" />
 <r>
	<Row>27</Row>
	<CustomerID>BOTTM</CustomerID>
	<CreditLimit>50000</CreditLimit>
	<AccountFreeze>Yes</AccountFreeze>
	<AccountNotes>Customer has exceeded credit limit</AccountNotes>
 </r>
	</XMLDataToSave>
</RequestContext>'
	,@Interject_ReturnError = @Interject_ReturnError output

Select 	@Interject_ReturnError as '@Interject_ReturnError'
==================================================================================
*/

ALTER PROC [demo].[Northwind_Credit_Save_MyName]

	 @Interject_RequestContext nvarchar(max)
	,@Interject_ReturnError varchar(2000) OUTPUT
	,@TestMode bit = 0

AS

	--
	-- Use helper to extract data from Interject_RequestContext. Remove items that are not needed
	-- The helper stored procedure [dbo].[RequestContext_Parse] is required for this section to function 
	--
	DECLARE @Interject_XMLDataToSave		XML	
	DECLARE @Interject_NTLogin				VARCHAR(50)

	EXEC [dbo].[RequestContext_Parse]
			@Interject_RequestContext		= @Interject_RequestContext
		,@Interject_XMLDataToSave		= @Interject_XMLDataToSave	OUTPUT
		,@MachineLoginName				= @Interject_NTLogin		OUTPUT
	
	IF @TestMode = 1
	BEGIN
		SELECT
				@Interject_XMLDataToSave	AS '@Interject_XMLDataToSave'
			,@Interject_NTLogin			AS '@Interject_NTLogin'
	END
	
	DECLARE @ErrorMessageToUser AS VARCHAR(1000) = ''
	DECLARE @NowUTC				AS DATETIME = GETUTCDATE()
	--
	-- PROCESS THE XML DATA INTO TABLE VARIABLE
	--
	
	DECLARE @DataToProcess TABLE
	(
			[_ExcelRow] INT 
		,[_MessageToUser] VARCHAR(500) DEFAULT('')
		,[CustomerID] VARCHAR(max)
		,[CreditLimit] VARCHAR(max)
		,[AccountFreeze] VARCHAR(max)
		,[_FreezeBit] BIT DEFAULT(0)
		,[AccountNotes] VARCHAR(max)
	)
	
	IF DATALENGTH(@Interject_XMLDataToSave) > 0 
	BEGIN
		declare @DataToProcessXML as XML
		-- Handle conversion of XML text into an XML variable. 
		BEGIN TRY
			SET @DataToProcessXML = CAST(@Interject_XMLDataToSave as XML)
		END TRY
		BEGIN CATCH
			SET @ErrorMessageToUser = 'Error in Parsing XML from Interject. Error: ' + ERROR_MESSAGE()
			GOTO FinalResponseToUser
		END CATCH
		
		-- Insert the XML into the table variable for further processing
		INSERT into @DataToProcess(
				[_ExcelRow]
			,[CustomerID]
			,[CreditLimit]
			,[AccountFreeze]
			,[AccountNotes]
		)
		SELECT
			
				T.c.value('./Row[1]', 'INT') as [_ExcelRow]
			,T.c.value('./CustomerID[1]', 'VARCHAR(max)') as [CustomerID]
			,T.c.value('./CreditLimit[1]', 'VARCHAR(max)')	 as [CreditLimit]
			,T.c.value('./AccountFreeze[1]', 'VARCHAR(max)') as [AccountFreeze]
			,T.c.value('./AccountNotes[1]', 'VARCHAR(max)')	 as [AccountNotes]
		FROM @DataToProcessXML.nodes('/XMLDataToSave/r') T(c)
	END
	
	-- TestMode is provided so a test save can be executed and all related data can be 
	-- easily viewed for testing while not effecting any data in the database
	IF @TestMode =1 
	BEGIN
		SELECT '@DataToProcess - After XML Processing' as ResultName
		SELECT * FROM @DataToProcess
	END
	
	--
	-- VALIDATION SECTION - Ensure inputs received from Excel are valid
	--
	
	-- Validate that the details do not have duplicates on the primary key
	UPDATE m
	SET [_MessageToUser] = [_MessageToUser] + ', Duplicate key'
	FROM @DataToProcess m
	INNER JOIN
		(
			SELECT [CustomerID] 
			FROM @DataToProcess
			WHERE [CustomerID] <> ''
			GROUP BY [CustomerID]
			HAVING COUNT(1) > 1
		) as t
		ON
			m.[CustomerID] = t.[CustomerID]
	
	-- Validate a column has valid text
	UPDATE @DataToProcess
	SET [_MessageToUser] = [_MessageToUser] + ', Account Freeze must be either Yes or left blank'
	WHERE [AccountFreeze] NOT IN ('Yes','')

	UPDATE @DataToProcess
		SET [_MessageToUser] = [_MessageToUser] + ', Account Notes must be 140 characters or less'
	FROM @DataToProcess 
	WHERE LEN([AccountNotes]) >= 140
	
	-- Now check if there were any validation issues in the detail and stop processing if true
	IF EXISTS(SELECT 1 FROM @DataToProcess WHERE [_MessageToUser] <> '')
	BEGIN
		SET @ErrorMessageToUser = 'There were errors in the details of your input. Please review the errors noted in each row.'
		GOTO FinalResponseToUser
	END
	
	
	IF @TestMode = 1 
	BEGIN
		SELECT '@DataToProcess - After Validation' as ResultName
		SELECT * FROM @DataToProcess
	END
	
	-- convert the 'yes' input into a bit for table storage
	UPDATE @DataToProcess
		SET [_FreezeBit] = 1
	WHERE [AccountFreeze] = 'Yes'

	UPDATE @DataToProcess
		SET CreditLimit = 0
	WHERE CreditLimit IS NULL
	--
	-- DATA UPDATE
	--
	
	BEGIN TRY
		
		-- this table variable will log the changes to the target table so it can be used
		-- to return helpful feedback to the user about how the save action resulted
		DECLARE @ChangeLog as TABLE
		(
				[_ExcelRow] INT	-- will capture the source row that affected the target table
			,[UpdateTypeCode] VARCHAR(10) -- Will show UPDATE, INSERT, or DELETE
			,[TargetTableKey] VARCHAR(5)
		)
		
		BEGIN TRAN t1
			--
			-- use MERGE statement that UPDATES, INSERTS, and DELETES in one action
			--
			MERGE [demo].[Northwind_AccountCredits] t -- t = the target table or view to update
			USING @DataToProcess s -- s = the source data to be used to update the target table
			ON 
				s.[CustomerID] = t.[CustomerID]
				
			WHEN MATCHED -- Handles the update based on INNER JOIN
				AND 
				(
						t.[CustomerID]		<> s.[CustomerID]
					OR t.[CreditLimit]		<> s.[CreditLimit]
					OR t.[AccountFreeze]	<> s.[_FreezeBit]
					OR t.[AccountNotes]		<> s.[AccountNotes]

				)
				THEN
				UPDATE SET
						t.[CustomerID]		 = s.[CustomerID]
					,t.[CreditLimit]	 = s.[CreditLimit]
					,t.[AccountFreeze]	 = s.[_FreezeBit]
					,t.[AccountNotes]	 = s.[AccountNotes]
					,t.[LastChangedBy]	 = @Interject_NTLogin
					,t.[LastChangedDate] = @NowUTC
			WHEN NOT MATCHED BY TARGET THEN -- Handles the insert based on LEFT JOIN 
			INSERT (
					[CustomerID]		
				,[CreditLimit]		
				,[AccountFreeze]	
				,[AccountNotes]
				,[LastChangedBy]
				,[LastChangedDate]
			)
			VALUES (
					s.[CustomerID]
				,s.[CreditLimit]
				,s.[_FreezeBit]
				,s.[AccountNotes]
				,@Interject_NTLogin
				,@NowUTC
			)
				
			-- the output captures the changes to the table and logs to a table variable
			OUTPUT 
					isnull(inserted.[CustomerID],deleted.[CustomerID]) -- include deleted in case delete is added later. Inserted is used for both Update an Insert
				,s.[_ExcelRow] 
				,$action as UpdateTypeCode -- this logs into an a change log table variable
			INTO @ChangeLog
			(
					[TargetTableKey]
				,[_ExcelRow]
				,[UpdateTypeCode]
			); 
			
			--now we have enough information to update the message to user for each row
			UPDATE dtp
			SET [_MessageToUser] = 
				CASE cl.UpdateTypeCode
					WHEN 'INSERT' THEN ', Added!' -- not used in this example
					WHEN 'UPDATE' THEN ', Updated!'
				END 
			FROM @DataToProcess dtp
				INNER JOIN @ChangeLog cl 
						ON dtp.[_ExcelRow] = cl.[_ExcelRow]
				
		IF @TestMode = 1
		BEGIN
			SELECT '@ChangeLog- Show log of changes made' as ResultName
			select * from @ChangeLog
			
			ROLLBACK TRAN t1 -- note this does not roll back changes to table variables, only real tables
			SELECT 'Changes rolled back since in TEST mode!' as TestModeNote
		END
		ELSE
		BEGIN
			COMMIT TRAN t1
		END
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRAN t1
		
		SET @ErrorMessageToUser = ERROR_MESSAGE()
		GOTO FinalResponseToUser
	END CATCH
	
FinalResponseToUser:
--
-- PROVIDE RESPONSE TO THE SAVE ACTION
--
	
	-- if test mode, show the final table 
	IF @TestMode = 1 
	BEGIN
		SELECT '@DataToProcess - After Validation' as ResultName
		SELECT * FROM @DataToProcess
	END
	
	-- return the recordset results back to the spreadsheet, if needed.
	SELECT
			[_ExcelRow] as [Row] -- this relates to the original row of the spreadsheet the data came from
		,SUBSTRING([_MessageToUser],3,1000) as [MessageToUser] -- This is a field that, if it matches a column in the Results Range, will be placed in that column for the specified row
	FROM @DataToProcess
	
	-- if there is an error, raise error and Interject will catch and present to the user.
	-- Note that this is specifically done after the above resultset is returned, since initiating an error before
	-- will not allow a result set to be returned to provide feedback on each row 
	IF @ErrorMessageToUser <> ''
	BEGIN
		-- by adding 'UserNotice:' as a prefix to the message, Interject will not consider it a unhandled error 
		-- and will present the error to the user in a message box.
		SET @ErrorMessageToUser = 'UserNotice:' + @ErrorMessageToUser
		
		RAISERROR (@ErrorMessageToUser,
		18, -- Severity,
		1) -- State)
		RETURN
	END