-- SQL FOF_SOAP_tbl set closed date -conditional
BEGIN TRY
	BEGIN TRANSACTION FOF_SOAP_TBL_closed  
		WITH MARK N'Updating FOF_SOAP_TBL for Closed SOAP';  
	BEGIN TRANSACTION
	-- PROGRESS
		DELETE FOF_SOAP_PROGRESS_tbl  WHERE SCHEDULE_ID=CONVERT(INT,{{passScheduleID}});
		INSERT INTO FOF_SOAP_PROGRESS_tbl (
			SCHEDULE_ID,
			DOMAIN_PROGRAM_ID,
			PROGRESS_ROWID,
			Prompts_Needed_Data,
			PROGRESS_NOTED_FLAG
			)
			VALUES {{!REPEATSTART}} ( 
			conVERT(INT, {{passScheduleID}}),
			CASt({{Domain_Program_ID}} AS INT),
			CasT({{Progress_RowID}} AS INT),
			cASt({{Prompts_Needed_Data}} as varchar(50)), -- pipe separated values
			CAST({{Progress_Noted_Flag}} as INT)
			), {{!REPEATEND}};

-- MALADAPTIVE			
		DELETE FOF_SOAP_MALADAPTIVE_tbl  WHERE SCHEDULE_ID={{passScheduleID}};
		INSERT INTO FOF_SOAP_MALADAPTIVE_tbl (
			SCHEDULE_ID,
			maladaptive_RowID,
			MALADAPTIVE_IMAGE_NOTE,
			MALADAPTIVE_IMAGE
			)
			VALUES {{!REPEATSTART}} ( 
			CAST({{passScheduleID}} AS INT),
			CAST({{maladaptive_RowID}} AS INT),
			CAST({{MALADAPTIVE_IMAGE_NOTE}} as nvarchar(100)),
			convert(varbinary(max), {{Maladaptive_image}})
			), {{!REPEATEND}}
		;
		-- OTHER
		DELETE FOF_SOAP_OTHER_tbl  WHERE SCHEDULE_ID={{passScheduleID}};
		INSERT INTO FOF_SOAP_OTHER_tbl (
			SCHEDULE_ID,
			OTHER_RowID,
			OTHER_DOC_NOTE,
			OTHER_DOCUMENT_IMG
			)
			VALUES {{!REPEATSTART}} ( 
			cAST({{passScheduleID}} AS INT),
			CaST({{otherPages_RowID}} AS INT),
			CAsT({{OTHER_IMAGE_NOTE}} as nvarchar(100)),
			convert(varbinary(max), {{OTHER_image}})
			), {{!REPEATEND}};
-- SOAP
--        IF {{soap_closed_dttm}} IS NOT NULL
--			  UPDATE FOF_SOAP_tbl  
--				SET soap_closed_dttm = CONVERT(DATETIME2(2), {{soap_closed_dttm}}) 
--				WHERE SCHEDULE_ID = CONVERT(INT,{{passScheduleID}})  and soap_closed_dttm is null;

		UPDATE fof_soap_tbl 
			set DISPLAY_NAME       = CONVERT(VARCHAR(20),{{SOAP_DATA[1]}}),
				SERVICE_PLACE_ID   = CONVERT(INT, {{CLIENT_SCHEDULE_QRY[10]}}),
				CLIENT_APPEARED_ID = CONVERT(INT, {{SOAP_DATA[3]}}),
				OTHER_CONCERNS_TXT = CONVERT(VARCHAR(100), {{OTHER_CONCERNS_TXT}}),
				PLAN_STATUS_ID     = CONVERT(INT, {{SOAP_DATA[5]}}),
				OCCURENCES_QTY     = CONVERT(INT, {{SOAP_DATA[6]}}),
				NOTES              = CONVERT(VARCHAR(500),{{SOAP_DATA[7]}}),
				THEREAPY_TECH_ID   = {{SOAP_DATA[8]}}, -- this is delimited with pipes
				THERAPIST_SIG_IMG  = CONVERT(VARBINARY(MAX), {{SOAP_DATA[9]}}),
				ACTUAL_THERAPIST_AD_ID = CONVERT(VARCHAR(50), {{SOAP_DATA[15]}}),
				SOAP_CLOSED_DTTM   = CASE WHEN SOAP_CLOSED_DTTM IS NULL then {{soap_closed_dttm}}  else SOAP_CLOSED_DTTM end,
				FOF_USER           = CASE WHEN FOF_USER IS NULL THEN {{FOF_USER}} ELSE FOF_USER END 
			WHERE SCHEDULE_ID      = CONVERT(INT, {{passScheduleID}})
			IF @@ROWCOUNT=0
					BEGIN
						INSERT INTO fof_soap_tbl (
							SCHEDULE_ID, 
							DISPLAY_NAME, 
							SERVICE_PLACE_ID, 
							CLIENT_APPEARED_ID, 
							OTHER_CONCERNS_TXT, 
							PLAN_STATUS_ID, 
							OCCURENCES_QTY, 
							NOTES, 
							THEREAPY_TECH_ID, 
							THERAPIST_SIG_IMG,
							ACTUAL_THERAPIST_AD_ID,
							SOAP_CLOSED_DTTM,
							FOF_USER
								)
						VALUES (
							CONVERT(INT, {{passScheduleID}}), -- SCHEDULE_ID, 
							CONVERT(VARCHAR(20),{{SOAP_DATA[1]}}), --DISPLAY_NAME
							CONVERT(INT, {{CLIENT_SCHEDULE_QRY[10]}}), -- SERVICE_PLACE_ID, 
							CONVERT(INT, {{SOAP_DATA[3]}}),
							CONVERT(VARCHAR(100), {{OTHER_CONCERNS_TXT}}),
							CONVERT(INT, {{SOAP_DATA[5]}}),
							CONVERT(INT, {{SOAP_DATA[6]}}),-- qty
							CONVERT(VARCHAR(500),{{SOAP_DATA[7]}}), -- notes
							{{SOAP_DATA[8]}}, -- THERAPY TECH_ID
							CONVERT(VARBINARY(MAX), {{SOAP_DATA[9]}}),
							CONVERT(VARCHAR(50), {{SOAP_DATA[15]}}),
							{{soap_closed_dttm}},
							{{FOF_USER}}
					)
					END
-- CHECK CLOSED FLAG
			IF {{close_soap}} ='Yes-------' 
				BEGIN
					UPDATE fof_soap_tbl 
						SET SOAP_CLOSED_DTTM   = CASE WHEN SOAP_CLOSED_DTTM IS NULL then {{soap_closed_dttm}}  end
					WHERE SCHEDULE_ID      = CONVERT(INT, {{passScheduleID}})
				END;
				
			IF {{ALTER_ACTUAL_TIME}} = 'Yes'
				BEGIN
					UPDATE fof_soap_tbl 
						SET ACTUAL_START_TIME  = CASE WHEN {{ALTER_ACTUAL_TIME}} = 'Yes' AND ACTUAL_START_TIME IS NULL then convert(TIME(0),{{ACTUAL_START_TIME}}) else ACTUAL_START_TIME end,
						ACTUAL_END_TIME    = CASE WHEN {{ALTER_ACTUAL_TIME}} = 'Yes' AND ACTUAL_END_TIME IS NULL then convert(TIME(0),{{ACTUAL_END_TIME}}) else ACTUAL_END_TIME end
					WHERE SCHEDULE_ID      = CONVERT(INT, {{passScheduleID}})
				END;	
				
				
-- CLIENT SCHEDULE -- for cancelled
	COMMIT TRANSACTION 
END TRY
BEGIN CATCH
	ROLLBACK
END CATCH
;
COMMIT