170 lines
4.3 KiB
Transact-SQL
170 lines
4.3 KiB
Transact-SQL
-- dbo.ep_HDCDZApi_ZpracujPrijataData
|
|
CREATE PROCEDURE dbo.ep_HDCDZApi_ZpracujPrijataData
|
|
@idJson INT=NULL
|
|
AS
|
|
|
|
|
|
-- HD Consulting Strakonice, T. Buzin
|
|
-- !! proceduru neupravujte, bude stejne pregenerovana pri startu Windows sluzby HDCDZApi !!
|
|
|
|
|
|
SET NOCOUNT ON
|
|
|
|
|
|
DECLARE
|
|
@tranPred INT,
|
|
@cnt INT,
|
|
@sql NVARCHAR(max),
|
|
@datPorizeni DATETIME,
|
|
@errMsg NVARCHAR(500),
|
|
@bChyba BIT,
|
|
@iChyba INT,
|
|
@debugXml XML,
|
|
@aktDatum DATETIME,
|
|
@logTyp TINYINT,
|
|
@logText NVARCHAR(500),
|
|
@logInt INT,
|
|
@logStr NVARCHAR(255),
|
|
@log4Mail BIT=0
|
|
|
|
DECLARE
|
|
@jsonString NVARCHAR(MAX),
|
|
@jsonString2 NVARCHAR(MAX),
|
|
@phDeviceId NVARCHAR(40),
|
|
@typDokum NVARCHAR(50),
|
|
@subtypDokum NVARCHAR(50),
|
|
@akce NVARCHAR(50),
|
|
@akceSource NVARCHAR(50),
|
|
@datumText NVARCHAR(25)
|
|
|
|
DECLARE @TabPHObj TABLE (ID INT NOT NULL, IDPHIdent INT)
|
|
|
|
|
|
-- vytvor pomocnou tabulku
|
|
/*
|
|
IF OBJECT_ID(N'tempdb..#TabJSONData', N'U') IS NOT NULL
|
|
DROP TABLE #TabJSONData
|
|
CREATE TABLE #TabJSONData (ID INT IDENTITY(1,1) NOT NULL, doc NVARCHAR(max))
|
|
*/
|
|
DROP TABLE IF EXISTS #TabJSONData
|
|
CREATE TABLE #TabJSONData (ID INT IDENTITY(1,1) NOT NULL, doc NVARCHAR(max))
|
|
|
|
|
|
|
|
|
|
|
|
IF (@idJson IS NULL)
|
|
DECLARE j CURSOR LOCAL FOR
|
|
SELECT ID, JSONData, Akce, DatPorizeni
|
|
FROM dbo._hdc_ph_PrijataJsonData
|
|
WHERE DatZpracovani IS NULL
|
|
AND Nezpracovat=0
|
|
AND ISNULL(JSONData, N'')<>N''
|
|
ORDER BY DatPorizeni ASC
|
|
ELSE
|
|
DECLARE j CURSOR LOCAL FOR
|
|
SELECT ID, JSONData, Akce, DatPorizeni
|
|
FROM dbo._hdc_ph_PrijataJsonData
|
|
WHERE ID=@idJson
|
|
OPEN j
|
|
WHILE (1=1)
|
|
BEGIN
|
|
FETCH NEXT FROM j INTO @idJson, @jsonString, @akce, @datPorizeni
|
|
IF (@@FETCH_STATUS<>0) BREAK
|
|
|
|
IF (ISJSON(@jsonString)=0)
|
|
CONTINUE
|
|
|
|
|
|
BEGIN TRY
|
|
SET @tranPred = @@TRANCOUNT
|
|
IF (@tranPred=0)
|
|
BEGIN TRAN
|
|
|
|
|
|
SET @datumText = NULL
|
|
-- smaz pomocnou tabulku
|
|
DELETE FROM #TabJSONData
|
|
INSERT #TabJSONData (doc) SELECT @jsonString
|
|
SELECT TOP(1) @akce = JSON_VALUE(doc, '$.action'),
|
|
@akceSource = JSON_VALUE(doc, '$.source'),
|
|
@datumText = JSON_VALUE(doc, '$.actionAt')
|
|
FROM #TabJSONData
|
|
|
|
SET @akce = ISNULL(@akce, N'')
|
|
SET @akceSource = ISNULL(@akceSource, N'')
|
|
SET @datumText = ISNULL(@datumText, N'')
|
|
IF (@datumText<>N'')
|
|
SET @datPorizeni = CONVERT(datetime, @datumText, 120)
|
|
|
|
IF (@akce<>N'')
|
|
UPDATE dbo._hdc_ph_PrijataJsonData SET Akce=@akce WHERE ID=@idJson AND Akce IS NULL
|
|
|
|
|
|
IF ISNULL( (SELECT COUNT(ID) FROM dbo._hdc_ph_Log WHERE IdJsonData=@idJson), 0)>=3
|
|
BEGIN
|
|
IF EXISTS(SELECT 1 FROM dbo._hdc_ph_PrijataJsonData WHERE ID=@idJson AND ISNULL(PosledniChyba,N'')=N'')
|
|
UPDATE dbo._hdc_ph_PrijataJsonData SET Nezpracovat=1,
|
|
PosledniChyba=(SELECT TOP(1) LogText FROM dbo._hdc_ph_Log
|
|
WHERE IdJsonData=@idJson ORDER BY DatPorizeni DESC)
|
|
WHERE ID=@idJson
|
|
CONTINUE
|
|
END
|
|
|
|
|
|
SET @logTyp = 0
|
|
SET @logInt = NULL
|
|
SET @logStr = N''
|
|
SET @log4Mail = 0
|
|
SET @logText = N''
|
|
|
|
|
|
|
|
-- BEGIN: CERVENE TLACITKO
|
|
IF (@akce=N'click' AND @akceSource='button')
|
|
BEGIN
|
|
SELECT TOP(1) @phDeviceId = JSON_VALUE(doc, '$.deviceId') FROM #TabJSONData
|
|
UPDATE dbo._hdc_ph_PrijataJsonData SET PHDeviceID=@phDeviceId WHERE ID=@idJson
|
|
|
|
SET @akce = N''
|
|
IF OBJECT_ID(N'dbo.ep_HDCDZApi_AkceCerveneTlacitko') IS NOT NULL
|
|
BEGIN
|
|
SET @sql = 'EXEC dbo.ep_HDCDZApi_AkceCerveneTlacitko @IDJson=@idJson, @PHDeviceId=@phDeviceId'
|
|
EXEC sp_executesql @sql, N'@idJson INT, @phDeviceId NVARCHAR(40)', @idJson, @phDeviceId
|
|
END
|
|
END -- akce: vyroba_zacatek
|
|
-- END: CERVENE TLACITKO
|
|
|
|
|
|
|
|
|
|
|
|
IF (@tranPred=0) AND (@@TRANCOUNT>0)
|
|
COMMIT TRAN
|
|
END TRY
|
|
BEGIN CATCH
|
|
IF (@tranPred=0) AND (@@TRANCOUNT>0)
|
|
ROLLBACK TRAN
|
|
SET @errMsg = ERROR_MESSAGE()
|
|
|
|
UPDATE dbo._hdc_ph_PrijataJsonData SET PosledniChyba=@errMsg WHERE ID=@idJson
|
|
|
|
IF (@logTyp>0)
|
|
INSERT dbo._hdc_ph_Log (Typ, LogText, IntValue, StrValue, IdJsonData, ForMail)
|
|
SELECT @logTyp, ISNULL(@logText, N'') + NCHAR(13)+NCHAR(10) + @errMsg, @logInt, @logStr, @idJson, @log4Mail
|
|
ELSE
|
|
INSERT dbo._hdc_ph_Log (Typ, LogText, IdJsonData, ForMail) SELECT 1, N'Chyba zpracovani PrijataJsonData: ' + @errMsg, @idJson, 1
|
|
END CATCH
|
|
SET @logTyp=0
|
|
|
|
END
|
|
CLOSE j
|
|
DEALLOCATE j
|
|
|
|
|
|
|
|
|
|
-- pro jistotu
|
|
IF OBJECT_ID(N'tempdb..#TabJSONData', N'U') IS NOT NULL
|
|
DROP TABLE #TabJSONData
|