80 lines
2.3 KiB
Transact-SQL
80 lines
2.3 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
DROP PROCEDURE IF EXISTS sp_UpdateRendszerbeallitasok
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[sp_UpdateRendszerbeallitasok]
|
|
@intezmenyId INT,
|
|
@tanevId INT,
|
|
@dictionaryTipus NVARCHAR(50),
|
|
@beallitasTipus INT,
|
|
@beallitasAzonosito NVARCHAR(255)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @oldDataTmp TABLE (ID int, Prop nvarchar(50), Val nvarchar(50));
|
|
DECLARE @newDataTmp TABLE (Selected nvarchar(50), [Value] nvarchar(50), [Text] nvarchar(255));
|
|
DECLARE @newJsonTmp TABLE (options nvarchar(MAX));
|
|
DECLARE @optionList NVARCHAR(MAX);
|
|
DECLARE @oldValue nvarchar(MAX) = (select C_ERTEK from T_RENDSZERBEALLITAS rb where rb.C_INTEZMENYID = @intezmenyId and rb.C_TANEVID = @tanevId and rb.C_BEALLITASTIPUS = @beallitasTipus)
|
|
|
|
INSERT INTO @oldDataTmp (ID, Prop, Val)
|
|
(
|
|
SELECT
|
|
PARENT_ID as ID
|
|
,NAME as Property
|
|
,CAST(STRINGVALUE as nvarchar(50)) as Val
|
|
FROM fnConvertJSONToTable (@oldValue)
|
|
)
|
|
|
|
INSERT INTO @newDataTmp (Selected, [Value], [Text])
|
|
(SELECT 'false', ID, C_NAME FROM T_DICTIONARYITEMBASE WHERE C_TYPE = @dictionaryTipus and C_INTEZMENYID = @intezmenyId and C_TANEVID = @tanevId and TOROLT = 'F')
|
|
|
|
INSERT INTO @newJsonTmp
|
|
SELECT
|
|
'{"Selected":'+ Selected + ',"Text":"' + ISNULL(Text,'') + '","Value":"' + CAST(ISNULL(Value,'') as nvarchar(10)) + '"}'
|
|
FROM
|
|
(SELECT
|
|
ndt.[Value]
|
|
,ndt.[Text]
|
|
,IIF(pivotTable.Selected != ndt.Selected, pivotTable.Selected, ndt.Selected) as Selected
|
|
FROM @newDataTmp ndt
|
|
LEFT JOIN
|
|
(SELECT
|
|
pt.[Value]
|
|
,pt.[Text]
|
|
,pt.Selected
|
|
FROM
|
|
(SELECT
|
|
ID
|
|
,Prop
|
|
,Val
|
|
FROM @oldDataTmp
|
|
WHERE
|
|
Prop IS NOT NULL) as x
|
|
PIVOT
|
|
(
|
|
MAX(x.Val)
|
|
FOR x.Prop IN ([Selected],[Text],[Value])
|
|
) AS pt
|
|
WHERE
|
|
pt.Value IS NOT NULL
|
|
) pivotTable
|
|
ON pivotTable.Value = ndt.Value
|
|
) AS newJsonTable
|
|
|
|
SELECT @optionList = COALESCE(@optionList + ', ', '') + options FROM @newJsonTmp
|
|
SET @optionList = ISNULL(@optionList, '')
|
|
|
|
UPDATE T_RENDSZERBEALLITAS SET
|
|
C_ERTEK = (select '{"Id":"' + @beallitasAzonosito+ '","Options":[' + @optionList + ']}'),
|
|
C_ALAPERTEK = (select '{"Id":"' + @beallitasAzonosito+ '","Options":[' + @optionList + ']}')
|
|
WHERE
|
|
C_INTEZMENYID = @intezmenyId and C_TANEVID = @tanevId and C_BEALLITASTIPUS = @beallitasTipus
|
|
|
|
END
|
|
GO |