309 lines
11 KiB
Transact-SQL
309 lines
11 KiB
Transact-SQL
USE gaia
|
|
GO
|
|
DECLARE
|
|
@galenicaQueueID UNIQUEIDENTIFIER,
|
|
@storageAccountName VARCHAR(MAX) = 'sttpsuntestphaqueues',
|
|
@queue VARCHAR(63) = 'sun004-triafin',
|
|
@payload VARCHAR(MAX)= '<Request RequestType="EntryCreate"><Data><EntryCreate><ReferencePharmacyId>706</ReferencePharmacyId><ReferenceOperationId>1215</ReferenceOperationId><OperationId>1215</OperationId><DebtorAccountId><PharmacyCode>GSU004</PharmacyCode><Id>136552</Id></DebtorAccountId><CreditorAccountId><PharmacyCode>GSU004</PharmacyCode><Id>1500090004</Id></CreditorAccountId><ReconciliationId><PharmacyCode>GSU004</PharmacyCode><Id>1501704897</Id></ReconciliationId><UserId>8231</UserId><ValueDate>2025-12-03</ValueDate><DocumentDate>2022-04-02</DocumentDate><Amount>48.4</Amount><Subject>BASSET JOSE - YUGRRP Hsnü - 1789 LUGNORRE</Subject><Remark>Zahlung ohne QRR</Remark><EntryInputMode>Excel</EntryInputMode><ConversationId>25306</ConversationId><ConversationCreationDate>2025-12-05T10:34:24</ConversationCreationDate></EntryCreate></Data></Request>',
|
|
@headers VARCHAR(MAX),
|
|
@headersBlob VARCHAR(MAX),
|
|
@oldConversationID UNIQUEIDENTIFIER,
|
|
@blobID UNIQUEIDENTIFIER,
|
|
@messageTypeName VARCHAR(255) = 'CeresSenderMessageType',
|
|
@statusCode INT,
|
|
@statusText NVARCHAR(4000),
|
|
@conversationID UNIQUEIDENTIFIER,
|
|
@messageID UNIQUEIDENTIFIER,
|
|
@insertionTime DATETIME
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
/* -------------------- VALIDATION -------------------- */
|
|
|
|
|
|
/* -------------------- VARIABLES -------------------- */
|
|
DECLARE @token VARCHAR(MAX),
|
|
@credentialName VARCHAR(256) = NULL,
|
|
@timeoutSeconds INT = 30,
|
|
@response VARCHAR(MAX),
|
|
@rawResponse XML,
|
|
@finalURL VARCHAR(MAX),
|
|
@ttl CHAR(13) = 'messagettl=-1',
|
|
@source VARBINARY(MAX),
|
|
@originalPayload VARCHAR(MAX) = @payload,
|
|
@url VARCHAR(MAX),
|
|
@urlblob VARCHAR(MAX),
|
|
@returnValue INT = 0,
|
|
@payloadBytes INT,
|
|
@createURL VARCHAR(MAX),
|
|
@retryCount INT,
|
|
@maxRetries INT,
|
|
@retryDelaySeconds INT,
|
|
@waitTime CHAR(8),
|
|
@postURL VARCHAR(MAX);
|
|
|
|
|
|
/* -------------------- GET TOKEN -------------------- */
|
|
IF @credentialName IS NULL
|
|
BEGIN
|
|
EXEC @returnValue = sp_get_Bearer_token
|
|
@Token = @token OUTPUT,
|
|
@statusCode = @statusCode OUTPUT,
|
|
@statusText = @statusText OUTPUT;
|
|
|
|
IF @returnValue <> 0 OR @statusCode <> 200
|
|
RAISERROR('Error: Get token failed! statusCode: %d statusText: %s',16,1,@statusCode,@statusText);
|
|
END
|
|
|
|
|
|
/* -------------------- HEADERS -------------------- */
|
|
IF @headers IS NULL
|
|
SET @headers = CONCAT(
|
|
'Content-Type: application/xml',CHAR(10),
|
|
'x-ms-version: 2025-07-05',CHAR(10),
|
|
'Authorization: Bearer ',@token,CHAR(10)
|
|
);
|
|
|
|
IF @headersBlob IS NULL
|
|
SET @headersBlob = CONCAT(
|
|
'x-ms-blob-type: BlockBlob',CHAR(10),
|
|
'x-ms-version: 2025-07-05',CHAR(10),
|
|
'Authorization: Bearer ',@token,CHAR(10)
|
|
);
|
|
|
|
|
|
/* -------------------- PREPARE -------------------- */
|
|
EXEC sp_manage_GalenicaQueue;
|
|
|
|
SELECT @conversationID = ISNULL(@oldConversationID,NEWID());
|
|
|
|
SET @url = CONCAT('https://',@storageAccountName,'.queue.core.windows.net/');
|
|
SET @urlblob = CONCAT('https://',@storageAccountName,'.blob.core.windows.net/');
|
|
|
|
IF RIGHT(@queue,1) <> '/' SET @queue = CONCAT(@queue,'/');
|
|
SET @queue = LOWER(@queue);
|
|
|
|
|
|
/* -------------------- INSERT MONITORING -------------------- */
|
|
IF @galenicaQueueID IS NULL
|
|
BEGIN
|
|
SET @galenicaQueueID = NEWID();
|
|
|
|
INSERT INTO GalenicaQueue(
|
|
galenicaQueueID, conversationID, messageID,
|
|
storageAccountName, queueName, headers,
|
|
headersBlob, blobID, messageTypeName, payload
|
|
) VALUES (
|
|
@galenicaQueueID, @conversationID, @messageID,
|
|
@storageAccountName, REPLACE(@queue,'/',''), @headers,
|
|
@headersBlob, @blobID, @messageTypeName, @originalPayload
|
|
);
|
|
END
|
|
|
|
|
|
/* -------------------- BLOB UPLOAD IF NEEDED -------------------- */
|
|
SET @payloadBytes = DATALENGTH(@originalPayload);
|
|
|
|
IF @payloadBytes > 49152
|
|
BEGIN
|
|
IF @blobID IS NULL
|
|
BEGIN
|
|
EXEC @returnValue = sp_put_message_into_blob
|
|
@url = @urlblob,
|
|
@payload = @originalPayload,
|
|
@headers = @headersBlob,
|
|
@response = @response OUTPUT,
|
|
@statusCode = @statusCode OUTPUT,
|
|
@statusText = @statusText OUTPUT,
|
|
@blobID = @blobID OUTPUT;
|
|
|
|
IF @returnValue <> 0 RETURN;
|
|
END
|
|
END
|
|
ELSE
|
|
SET @blobID = NULL;
|
|
|
|
|
|
/* -------------------- BUILD MESSAGE -------------------- */
|
|
SET @postURL = CONCAT(@url,@queue,'messages?',CONCAT_WS('&',@ttl,NULLIF(@token,'')));
|
|
|
|
IF @blobID IS NULL
|
|
SET @payload = CONCAT(
|
|
'<GalenicaQueue><conversationID>',CONVERT(VARCHAR(255),@conversationID),
|
|
'</conversationID><inlinePayload><![CDATA[',@originalPayload,
|
|
']]></inlinePayload><messageTypeName>',@messageTypeName,
|
|
'</messageTypeName></GalenicaQueue>'
|
|
);
|
|
ELSE
|
|
SET @payload = CONCAT(
|
|
'<GalenicaQueue><conversationID>',CONVERT(VARCHAR(255),@conversationID),
|
|
'</conversationID><blobID>',CONVERT(VARCHAR(255),@blobID),
|
|
'</blobID><messageTypeName>',@messageTypeName,
|
|
'</messageTypeName></GalenicaQueue>'
|
|
);
|
|
|
|
--cast the payload to xml first to enforce a utf-16le bytes encoding
|
|
SET @source = CONVERT(VARBINARY(MAX), CONVERT(XML, @payload));
|
|
SET @payload =
|
|
CAST('' AS XML).value('xs:base64Binary(sql:variable("@source"))','varchar(max)');
|
|
|
|
SET @payload = CONCAT('<QueueMessage><MessageText>', @payload, '</MessageText></QueueMessage>');
|
|
print @payload
|
|
|
|
/* -------------------- SEND MESSAGE FIRST TRY -------------------- */
|
|
BEGIN TRY
|
|
EXEC @returnValue = sp_Make_Restful_Call
|
|
@url = @postURL,
|
|
@method = 'POST',
|
|
@payload = @payload,
|
|
@headers = @headers,
|
|
@credentialName = @credentialName,
|
|
@timeoutSeconds = @timeoutSeconds,
|
|
@response = @response OUTPUT,
|
|
@statusText = @statusText OUTPUT,
|
|
@statusCode = @statusCode OUTPUT,
|
|
@raiseError = 0 ;
|
|
|
|
IF @statusCode BETWEEN 400 AND 499
|
|
RAISERROR('Error in make restfull call',16,10);
|
|
END TRY
|
|
BEGIN CATCH
|
|
|
|
|
|
/* -------------------- QUEUE MISSING → CREATE IT -------------------- */
|
|
IF @statusCode = 404
|
|
BEGIN
|
|
SET @createURL = CONCAT(@url,@queue,'?',@token);
|
|
|
|
EXEC @returnValue = sp_Make_Restful_Call
|
|
@url = @createURL,
|
|
@method = 'PUT',
|
|
@payload = NULL,
|
|
@headers = @headers,
|
|
@credentialName = @credentialName,
|
|
@timeoutSeconds = @timeoutSeconds,
|
|
@response = @response OUTPUT,
|
|
@statusText = @statusText OUTPUT,
|
|
@statusCode = @statusCode OUTPUT;
|
|
|
|
|
|
/* -------------------- 409 CONFLICT → RETRY + CLEANUP -------------------- */
|
|
IF @statusCode = 409
|
|
BEGIN
|
|
SET @retryCount = 0;
|
|
SET @maxRetries = 10;
|
|
SET @retryDelaySeconds = 10;
|
|
|
|
WHILE @retryCount < @maxRetries
|
|
BEGIN
|
|
EXEC @returnValue = sp_Make_Restful_Call
|
|
@url = @createURL,
|
|
@method = 'PUT',
|
|
@payload = NULL,
|
|
@headers = @headers,
|
|
@credentialName = @credentialName,
|
|
@timeoutSeconds = @timeoutSeconds,
|
|
@response = @response OUTPUT,
|
|
@statusText = @statusText OUTPUT,
|
|
@statusCode = @statusCode OUTPUT;
|
|
|
|
IF @statusCode IN (201,204)
|
|
BREAK;
|
|
|
|
/* Cleanup blob if needed */
|
|
IF @blobID IS NOT NULL
|
|
BEGIN
|
|
EXEC @returnValue = sp_delete_message_from_blob
|
|
@storageAccountName = @storageAccountName,
|
|
@headers = @headersBlob,
|
|
@blobID = @blobID,
|
|
@response = @response OUTPUT,
|
|
@statusCode = @statusCode OUTPUT,
|
|
@statusText = @statusText OUTPUT;
|
|
END
|
|
|
|
/* Wait */
|
|
SET @waitTime = CONCAT('00:00:', RIGHT('0'+CAST(@retryDelaySeconds AS VARCHAR(2)),2));
|
|
WAITFOR DELAY @waitTime;
|
|
|
|
SET @retryCount += 1;
|
|
END
|
|
|
|
IF @statusCode NOT IN (201,204)
|
|
RAISERROR(
|
|
'Error: Create the queue failed after %d retries! statusCode: %d statusText: %s',
|
|
16,1,@retryCount,@statusCode,@statusText
|
|
);
|
|
END
|
|
|
|
/* Retry sending */
|
|
EXEC @returnValue = sp_Make_Restful_Call
|
|
@url = @postURL,
|
|
@method = 'POST',
|
|
@payload = @payload,
|
|
@headers = @headers,
|
|
@credentialName = @credentialName,
|
|
@timeoutSeconds = @timeoutSeconds,
|
|
@response = @response OUTPUT,
|
|
@statusText = @statusText OUTPUT,
|
|
@statusCode = @statusCode OUTPUT;
|
|
END
|
|
|
|
END CATCH
|
|
|
|
return
|
|
|
|
|
|
|
|
/* -------------------- UPDATE MONITORING -------------------- */
|
|
UPDATE GalenicaQueue
|
|
SET blobID = @blobID,
|
|
statusCode = @statusCode,
|
|
statusText = @statusText,
|
|
response = @response
|
|
WHERE galenicaQueueID = @galenicaQueueID;
|
|
|
|
|
|
/* -------------------- FINAL VALIDATION -------------------- */
|
|
IF @returnValue <> 0 OR @statusCode NOT BETWEEN 200 and 299
|
|
RAISERROR('Error: Create the message into the queue failed! statusCode: %d statusText: %s',
|
|
16,1,@statusCode,@statusText);
|
|
|
|
|
|
/* -------------------- PARSE RESPONSE -------------------- */
|
|
SET @rawResponse = CAST(@response AS XML);
|
|
|
|
SELECT @messageID = msg.value('(MessageId)[1]','UNIQUEIDENTIFIER'),
|
|
@insertionTime =
|
|
CONVERT(DATETIME,
|
|
CAST(
|
|
LTRIM(RTRIM(
|
|
REPLACE(
|
|
SUBSTRING(
|
|
msg.value('(InsertionTime)[1]','VARCHAR(MAX)'),
|
|
CHARINDEX(',',msg.value('(InsertionTime)[1]','VARCHAR(MAX)'))+1,
|
|
LEN(msg.value('(InsertionTime)[1]','VARCHAR(MAX)'))
|
|
),
|
|
'GMT',''
|
|
)
|
|
))
|
|
AS DATETIMEOFFSET)
|
|
AT TIME ZONE 'UTC'
|
|
AT TIME ZONE 'Central European Standard Time')
|
|
FROM @rawResponse.nodes('/QueueMessagesList/QueueMessage') AS T(msg);
|
|
|
|
|
|
UPDATE GalenicaQueue
|
|
SET messageID = @messageID,
|
|
blobID = @blobID,
|
|
statusCode = @statusCode,
|
|
statusText = @statusText,
|
|
response = @response,
|
|
insertionTime = @insertionTime
|
|
WHERE galenicaQueueID = @galenicaQueueID;
|
|
|
|
END
|
|
|
|
|
|
GO
|