IF EXISTS (SELECT
               1
           FROM sys.objects o
           WHERE
               o.object_id = OBJECT_ID('AKUZ.sp_RDS_LloElectronicCertificate'))
    BEGIN
        DROP PROCEDURE AKUZ.sp_RDS_LloElectronicCertificate;
    END
GO


CREATE PROCEDURE AKUZ.sp_RDS_LloElectronicCertificate @Xml xml = NULL,
                                                      @ID UNIQUEIDENTIFIER = NULL,
                                                      @IsNew BIT = 1,
                                                      @QueueName VARCHAR(100) = 'rds.registry',
                                                      @dateTime DATETIME = NULL,
                                                      @User UNIQUEIDENTIFIER = NULL
AS
BEGIN

    IF (@xml IS NULL)
        BEGIN

            DECLARE @content VARCHAR(MAX)

            SELECT
                @content = ap.SignaData
            FROM AKUZ.T_LLO_ELECTRONIC_CERTIFICATE_APPLICATION ap
            WHERE
                ap.LloElectronicCertificateApplicationID = @id


            INSERT INTO
                AKUZ.T_RABBIT_SEND_OBJECT (RabbitSendObjectID,
                                           QueueName,
                                           MessageID,
                                           Headers,
                                           ContentEncoding,
                                           Type,
                                           UserID,
                                           CreateDateTime,
                                           OwnerID,
                                           State,
                                           Content)
            SELECT
                NEWID(),
                @QueueName,
                NEWID(),
                NULL,
                'utf-8',
                'LLO_ELECTRONIC_CERTIFICATE_APPLICATION',
                NULL,
                GETDATE(),
                @id,
                0,
                dbo.ConvertTextToBinaryZip(@content, '')
        END
    ELSE
        BEGIN
            SELECT
                CONVERT(DATETIME, t.c.value('./applicationDate[1]', 'varchar(10)'), 104) AS ApplicationDate,
                t.c.value('./serial[1]', 'varchar(128)')                                 AS Serial,
                t.c.value('./number[1]', 'varchar(128)')                                 AS Number,
                t.c.value('./status[1]', 'integer')                                      AS Status,
                t.c.value('./recipeid[1]', 'uniqueidentifier')                           AS RecipeId
            INTO
                #tmp
            FROM @Xml.nodes('ELECTRONIC_CERTIFICATE_APPLICATION/body') t(c)

            UPDATE ap
            SET
                ap.Status = CASE t.Status 
                                WHEN 1 THEN 1 
                                WHEN 2 THEN 3 
                                WHEN 3 THEN 2 
                                END
            FROM akuz.T_LLO_ELECTRONIC_CERTIFICATE_APPLICATION ap
            JOIN #tmp t ON t.RecipeId = ap.RecipeGeneral
        END
END
