Custom Items

*/

ALTER PROC [demo].[CustomItems_Example]

	-- Example optional parameter. There are two ways to set an initial value 
	--     1) set the default value below and not setup the parameter in the dataportals.  
	--     2) set a default value in the data portal setup.
	-- 
	-- If in #2 the default value starts with a question mark like "?Enter Year Month (YYYY-MM)", this will cause Interject
	-- on execute to ask the user to "Enter Year Month (YYYY-MM)".  This must be validated by the stored procedure or 
	-- web service
	@YearMonthToUpdate VARCHAR(10) = ''

	-- System Parameters Required
	,@Interject_CommandExecute BIT = 0 -- This tells this SP to execute the command instead of only returning description and status
	,@Interject_CommandResult VARCHAR(200) OUTPUT -- This provides the result of the execute.  it is limited to 200 characters.

	-- System Parameters Optional
	,@Interject_CommandDescription VARCHAR(200) OUTPUT -- Shows friendly description to user. Shows DataPortal name if parameter not used.
	,@Interject_CommandStatus VARCHAR(200) OUTPUT -- Will return a status such as details from the last time it was run.

	-- The below are examples of Interject reserved parameters 
	,@Interject_LocalTimeZoneOffset MONEY -- Provides a number (0.000) the represents that offset of the users time to UTC time.
	,@Interject_NTLogin VARCHAR(50) -- Provides the users computer login name for their current session.
	,@Interject_UserRoles VARCHAR(1000) -- Provides the Interject user roles for the user

	--@Interject_UserID VARCHAR(50) -- Provides the Interject User ID for their current session.
	--@Interject_LoginName VARCHAR(50) -- Provides the Interject Full name for their current session.
	--@Interject_ExcelVersion VARCHAR(50) -- Provides the Excel Version of the current session.
	--@Interject_ClientID VARCHAR(50) -- Provides the Interject Client ID for their current session.

AS


/*
==================================================================================
	CREATED DATE: 12/11/2023
	CREATED BY: Interject Default
	DESCRIPTION: Example data pull to be used with Interject

	TEST Example: Use the below to test this stored procedure:

	Declare @Interject_CommandResult as varchar(200)
	declare @Interject_CommandDescription varchar(200)
	declare @Interject_CommandStatus varchar(200)

	Execute demo.[CustomItems_Example]
		@YearMonthToUpdate = '2013-10'
		,@Interject_CommandExecute = 0  -- Change this to 1 for execute, 0 to only bring status
		,@Interject_CommandResult=@Interject_CommandResult out
		,@Interject_CommandDescription=@Interject_CommandDescription out
		,@Interject_CommandStatus=@Interject_CommandStatus out
		,@Interject_LocalTimeZoneOffset = -8
		,@Interject_NTLogin = 'samuelr'
		,@Interject_UserRoles = 'ClientAdmin'

	Select @Interject_CommandResult as '@Interject_CommandResult'
		,@Interject_CommandDescription as '@Interject_CommandDescription'
		,@Interject_CommandStatus as '@Interject_CommandStatus'

==================================================================================
*/

---
---Set description of this custom item
---
	SET @Interject_CommandDescription = 'Update Reporting Database'


---
--- Prepare to provide status 
---

---Get an example date of when the update was last performed so a status can be prepared
	DECLARE @LastUpdated DATETIME
	SET @LastUpdated=  dateadd(month,-1,getutcdate())  -- This uses UTC date since log data will typically not reflect a time zone
	SET @LastUpdated = dateadd(hour,@Interject_LocalTimeZoneOffset,@LastUpdated)  -- convert UTC date to user's time zone


---Set current status of this custom item
	DECLARE @LastUpdateYearMonth VARCHAR(7)

	SET @LastUpdateYearMonth =  CAST(YEAR(DATEADD(m,0,@LastUpdated)) AS VARCHAR(10)) 
			+ '-' + RIGHT(CAST(MONTH(DATEADD(m,0,@LastUpdated)) + 100 AS VARCHAR(4)),2)

	SET @Interject_CommandStatus = 'Month ''' + @LastUpdateYearMonth + ''' was updated on ' 
		+ LEFT(convert(varchar, @LastUpdated, 1),5)  + ' ' 
		+ ltrim(right(convert(varchar(25), @LastUpdated, 100), 7))


---
---Do more if this is an execute event
---
	IF @Interject_CommandExecute = 1
	BEGIN
		DECLARE @NewUpdateDate DATETIME
		SET @NewUpdateDate =  GETDATE()

	--
	--Handle the input parameters
	--

	--This example will specify the current month if no input is received
		IF @YearMonthToUpdate = '' 
		BEGIN

			SET @YearMonthToUpdate =  CAST(YEAR(DATEADD(m,0,@NewUpdateDate)) AS VARCHAR(10)) 
				+ '-' + RIGHT(CAST(MONTH(DATEADD(m,0,@NewUpdateDate)) + 100 AS VARCHAR(4)),2)
		END
		ELSE
		BEGIN

			--Validate to ensure input was a valid date
			IF isdate(right(@YearMonthToUpdate,2) + '/1/' + left(@YearMonthToUpdate,4)) = 0  
			BEGIN
				SET @Interject_CommandResult = 'The value entered ''' + @YearMonthToUpdate + ''' does not represent a valid date'
				RAISERROR (@Interject_CommandResult, 18,1) 
				RETURN
			END

		END

	--
	--Execute the update and return the result
	--
		BEGIN TRY
			WAITFOR DELAY '00:00:05' -- Wait for 5 seconds to simulate an actual update event

			-- Set result response
			SET @Interject_CommandResult = 'Updated month ''' + @YearMonthToUpdate + ''' successfully on '  
				+ LEFT(CONVERT(VARCHAR,@NewUpdateDate, 1),5)  + ' ' 
				+ LTRIM(RIGHT(CONVERT(VARCHAR(25), @NewUpdateDate, 100), 7))

			-- Redo status reponse based on the successful update
			SET @Interject_CommandStatus = 'Month ''' + @YearMonthToUpdate + ''' was updated on ' 
				+ LEFT(convert(varchar, @NewUpdateDate, 1),5)  + ' ' 
				+ ltrim(right(convert(varchar(25), @NewUpdateDate, 100), 7))

		END TRY
		BEGIN CATCH
				SET @Interject_CommandResult = 'Error: ' + Error_Message()
		END CATCH


	END