/*
*    Adds a record to the XmlSystemRequestQueue table to request an upload or download to/from an external system.
*    Also, if the @inExternalIdentifier parameter has a value, a record is added to the XmlOrderLink table
*    to link the specified order to the specified XML System (if a link does not already exist).
*
*    Parameters:
*    @inOrderId Primary key of an Orders record.
*    @inSysUserId Primary key of the logged-in Sysuser.
*    @inXmlSystemRequestType Type of the request (Name of a XmlSystemRequestType record).
*    @inXmlSystemDataTypeMap The type of data that this request will upload/download (Name of a XmlSystemDataTypeMap record).
*    @inXmlSystemRequestStatus (Optional) Initial status to set on the request . Default is "Queued".
*    @inExternalIdentifier (Optional) External identifier for the order. Default is null.
*/

create procedure [dbo].[AddXmlSystemRequestQueue]
    @inOrderId int,
    @inSysUserId int,
    @inXmlSystemRequestType nvarchar(16),
    @inXmlSystemDataTypeMap varchar(64),
    @inXmlSystemRequestStatus nvarchar(16) = 'Queued',
    @inExternalIdentifier varchar(60) = null,
    @outOrderAlreadyLinked bit = 0 output
as

declare @theXmlSystemRequestTypeId int
declare @theXmlSystemDataTypeMapId int
declare @theXmlSystemRequestStatusId int
select @theXmlSystemRequestTypeId = XmlSystemRequestTypeId from XmlSystemRequestType where [Name] = @inXmlSystemRequestType
select @theXmlSystemDataTypeMapId = XmlSystemDataTypeMapID from XmlSystemDataTypeMap where [Name] = @inXmlSystemDataTypeMap
select @theXmlSystemRequestStatusId = XmlSystemRequestStatusId from XmlSystemRequestStatus where [Name] = @inXmlSystemRequestStatus
set @outOrderAlreadyLinked = cast( 0 as bit )

-- If an External Identifier was provided, add an XmlOrderLink for it.
if( dbo.IsNotNullOrWhiteSpace( @inExternalIdentifier ) = 1 )
begin
    declare @theXmlSystemId int
    select @theXmlSystemId = XmlSystemFID
    from XmlSystemDataTypeMap
    where [Name] = @inXmlSystemDataTypeMap

    insert into XmlOrderLink
    (
        OrderFID,
        XmlSystemFID,
        ExternalKey
    )
    select
        @inOrderId,
        @theXmlSystemId,
        @inExternalIdentifier
    where not exists
    (
        select top 1 1
        from XmlOrderLink
        where
            OrderFID = @inOrderId and
            XmlSystemFID = @theXmlSystemId
    )

    if( @@ROWCOUNT = 0 )
    begin
        set @outOrderAlreadyLinked = cast( 1 as bit )
    end
end

if( @outOrderAlreadyLinked = 0 )
begin
    -- Add a record to the queue.
    insert into XmlSystemRequestQueue
    (
        XmlSystemDataTypeMapFid,
        XmlSystemRequestTypeFid,
        OrderFid,
        SysUserFid,
        XmlSystemRequestStatusFid
    )
    select
        @theXmlSystemDataTypeMapId,
        @theXmlSystemRequestTypeId,
        @inOrderId,
        @inSysUserId,
        @theXmlSystemRequestStatusId
end
GO
GRANT EXECUTE ON  [dbo].[AddXmlSystemRequestQueue] TO [MssExec]
GO