USE [lime_crm_base_solution_v7_0_1]
GO
/****** Object:  StoredProcedure [dbo].[csp_lc_runonsqlupdate]    Script Date: 2019-09-19 14:01:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Written by: Fredrik Eriksson
-- Created: 2016-09-29

-- ##########
-- DISCLAIMER:
-- This procedure is a built in procedure in Lime Core.
-- Modifying it could have consequences beyond your imagination.
-- Or not.
-- Seriously though, you should always make sure that no other built in functionality breaks if you must modify this procedure.
-- ##########

-- Use this procedure to recalculate fields that have onsqlupdate expressions set in LISA.

-- @@tablename: (Mandatory) The database name of the table to update.
-- @@fieldnames: (Not mandatory) A semicolon separated string with the database names of the fields to update.
--				 Can be left empty if all fields with onsqlupdate expressions on the specified table should be recalculated.
-- @@whereclause: (Not mandatory) Use this string to specify conditions that have to apply for a record to be updated.
--				  For example: Only open helpdesk records, use 'enddate IS NULL'.
-- @@printsql: (Not mandatory) Set to 1 if you want the procedure to only print the SQL generated statement instead of executing it.

ALTER PROCEDURE [dbo].[csp_lc_runonsqlupdate]
	@@tablename NVARCHAR(64)
	, @@fieldnames NVARCHAR(1024) = N''
	, @@whereclause NVARCHAR(1024) = N''
	, @@printsql BIT = 1
AS
BEGIN

	-- FLAG_EXTERNALACCESS --


	DECLARE @sql NVARCHAR(MAX)
	SET @sql = N''

	SET @sql = N'UPDATE [' + @@tablename + N']' + CHAR(10) + N'SET [status] = [status]' + CHAR(10)

	SELECT @sql = @sql + CHAR(9) + N', [' + f.[name] + N'] = (' + a.[value] + N')' + CHAR(10)
	FROM [attributedata] a
	INNER JOIN [field] f
		ON a.[idrecord] = f.[idfield]
	INNER JOIN [table] t
		ON f.[idtable] = t.[idtable]
	LEFT JOIN [dbo].[cfn_lc_gettablefromstring](@@fieldnames, N';') fn
		ON fn.[value] = f.[name]
	WHERE a.[owner] = N'field'
		AND a.[name] = N'onsqlupdate'
		AND t.[name] = @@tablename
		AND
		(
			@@fieldnames = N''
			OR fn.[value] IS NOT NULL
		)

	SET @sql = @sql + N'WHERE [status] = 0'
							+ CASE
								WHEN @@whereclause <> N''
									THEN CHAR(10) + CHAR(9) + N'AND (' + @@whereclause + N')'
								ELSE N''
								END

	IF @@printsql = 1
	BEGIN
		PRINT @sql
	END
	ELSE
	BEGIN
		EXECUTE(@sql)
	END
END