/**
*    
*    This procedure is called to generate the all portions of the export xml for a given order number with a specific last name.
*
*    Parameters:
*    @param @inOrderNumber        The Orders.OrderNumber
*    @param @inLastName            The last name of the shipper
*    @param @inIntegratorKeyId    The VendoorConnect KeyId.
*    @param @inExportOptions        Passed into XmlExport. See XmlExport for details.
*    @param @inIncludeInternationalData    Determines if we are to run XmlExportInternational or not.
*    @param @inIncludeLiftVans    Passed into XmlExportInternational. See XmlExportInternational for details.
*    @param @inIncludeContainers    Passed into XmlExportInternational. See XmlExportInternational for details.
*    @param @inIncludeInternationalVoyages    Determines if we are to run XmlExportInternationalVoyages or not.
*    @param @outStatusCode        Returned from XmlExport.  See XmlExport for details.
*    @param @outErrorCode        Error code (ours or returned from XmlExport).  See XmlExport for additional details.
*/

CREATE PROCEDURE [dbo].[XmlExportFromOrderNoLastName]
    @inOrderNumber varchar(23),
    @inLastName varchar(26),
    @inIntegratorKeyId nvarchar(4),
    @inExportOptions int,
    @inIncludeInternationalData bit,
    @inIncludeLiftVans bit,
    @inIncludeContainers bit,
    @inIncludeInternationalVoyages bit,
    @outStatusCode int output,
    @outErrorCode int output
as
set nocount on;

-- Error codes we return
declare @ORDER_NOT_FOUND int
declare @INPUT_DATA_INCOMPLETE int
declare @INTERFACE_NOT_FOUND int
declare @ORDER_NOT_ACCESSIBLE int

-- Other variables needed
declare @theLastFiveYears datetime
declare @theOrderID int
declare @theBranchPriKey int
declare @theVendorConnectID int
declare @theInterfaceVendorName varchar(50)
declare @theXmlSystemID int
declare @theXmlInterfaceID int

-- Initialize the parameters and variables
select
    @outStatusCode = 0,
    @outErrorCode = 0,
    @ORDER_NOT_FOUND = 7500,
    @INPUT_DATA_INCOMPLETE = 7501,
    @INTERFACE_NOT_FOUND = 7502,
    @ORDER_NOT_ACCESSIBLE = 7503,
    @theLastFiveYears = dateadd( year, -5, getdate() )

if( isnull( @inOrderNumber, '' ) = '' or isnull( @inLastName, '' ) = '' or isnull( @inIntegratorKeyId, N'' ) = N'' )
begin
    set @outErrorCode = @INPUT_DATA_INCOMPLETE
end
else
begin
    select
        @theOrderID = PriKey,
        @theBranchPriKey = BranchPriKey
    from Orders
    where OrderNo = @inOrderNumber and
        LastName = @inLastName and
        Archived = 0 and
        Orders.CreatedOn >= @theLastFiveYears

    if( @theOrderID is null )
    begin
        set @outErrorCode = @ORDER_NOT_FOUND
    end
end

if( @outErrorCode = 0 )
begin
    select
        @theXmlSystemID = XmlSystem.XmlSystemID,
        @theXmlInterfaceID = XmlInterface.XmlInterfaceID,
        @theInterfaceVendorName = XmlInterface.VendorName,
        @theVendorConnectID = VendorConnect.VendorConnectID
    from MoversConnectVendor
    inner join VendorConnect on VendorConnect.MoversConnectVendorFID = MoversConnectVendor.MoversConnectVendorID
    inner join XmlSystemDataTypeMap on XmlSystemDataTypeMap.XmlSystemDataTypeMapID = VendorConnect.XmlSystemDataTypeMapFID
    inner join XmlSystem on XmlSystem.XmlSystemID = XmlSystemDataTypeMap.XmlSystemFID
    inner join XmlInterface on XmlInterface.XmlInterfaceID = XmlSystem.XmlInterfaceFID
    where MoversConnectVendor.IntegratorKeyID = @inIntegratorKeyId and
        MoversConnectVendor.MoversConnectInactive = 0 and
        VendorConnect.Inactive = 0

    if( @theInterfaceVendorName is null )
    begin
        -- Realistically, this cannot happen.
        set @outErrorCode = @INTERFACE_NOT_FOUND
    end
end

if( @outErrorCode = 0 )
begin
    -- In order to access an order, the branch on that order must have a VendorConnectBranch record
    -- for this VendorConnect item.
    if( not exists(
        select top 1 1
        from VendorConnectBranch
        where VendorConnectFID = @theVendorConnectID and
            BranchFID = @theBranchPriKey and
            Inactive = 0
    ) )
    begin
        set @outErrorCode = @ORDER_NOT_ACCESSIBLE
    end
end


if( @outErrorCode = 0 )
begin
    exec XmlExport
        @inOrderID = @theOrderID,
        @inInterfaceVendorName = @theInterfaceVendorName,
        @inExportOptions = @inExportOptions,
        @outStatusCode = @outStatusCode,
        @outErrorCode = @outErrorCode

    if( @inIncludeInternationalData = 1 )
    begin
        exec XmlExportInternational
            @inOrderId = @theOrderID,
            @inXmlInterfaceId = @theXmlInterfaceID,
            @inXmlSystemId = @theXmlSystemID,
            @inGetLiftVans = @inIncludeLiftVans,
            @inGetContainers = @inIncludeContainers
    end

    if( @inIncludeInternationalVoyages = 1 )
    begin
        exec XmlExportInternationalVoyages
            @inOrderId = @theOrderID,
            @inXmlInterfaceId = @theXmlInterfaceID
    end
end
else
begin
    -- This is same error XML that XmlExport would return.
    select
    1 as tag,
    null as parent,
    [Error!1!ErrorMessage!element] = @outErrorCode
    for xml explicit;
end
GO
GRANT EXECUTE ON  [dbo].[XmlExportFromOrderNoLastName] TO [MssExec]
GO