/**
*    
*    This procedure is called to generate the xml segment for order locations (from an office & industrial order).
*    
*    Possible Error Codes:
*    @ERROR_CODE_INVALID_ORDER
*    
*    @param @inOrderID The primary key of a MoversSuite Orders record.
*    @param @outStatusCode Returns whether or not an error occurred while executing this stored procedure.
*    @param @outErrorCode Returns any errors encountered while executing this stored procedure.
*/
CREATE PROCEDURE [dbo].[XmlExportOrderLocation]
    @inXmlInterfaceID int,
    @inOrderID int,
    @outStatusCode int output,
    @outErrorCode int output
as
set nocount on;

-- Initialize the output parameters.
set @outStatusCode    = 0;
set @outErrorCode    = 0;

-- Define the error codes.
declare @ERROR_CODE_INVALID_ORDER    int;
set        @ERROR_CODE_INVALID_ORDER    = 2048;

-- Define the status codes.
declare @STATUS_CODE_PROCESSED    int;
set        @STATUS_CODE_PROCESSED    = 1;
declare @STATUS_CODE_ERROR        int;
set        @STATUS_CODE_ERROR        = 0;

declare @theMainAddressTypeId int

-- Check the order.
declare @theValidOrderFlag bit;
exec    @theValidOrderFlag = dbo.ValidateOrderID @inOrderID;

-- If the order is not valid, return error code @ERROR_CODE_INVALID_ORDER.
if ( 0 = @theValidOrderFlag )
begin
    set @outErrorCode  = @ERROR_CODE_INVALID_ORDER;
    set @outStatusCode = @STATUS_CODE_ERROR;
end
-- Otherwise return the xml segment and set the
-- status code to @STATUS_CODE_PROCESSED.
else
begin
    select @theMainAddressTypeId = AddressTypeID from AddressType where TypeName = 'Main'

    -- Get the order locations
    select
        1         as tag,
        null     as parent,
        [OrderLocation!1!Name!element] = OrderLocations.[Name],
        [OrderLocation!1!Contact!element] = OrderContacts.Contact,
        [OrderLocation!1!ContactName!element] = OrderContacts.[Name],
        [OrderLocation!1!LocationType!element] = OrderLocationAddressType.TypeName,
        [OrderLocation!1!Building!element] = Buildings.[Description],
        [OrderLocation!1!BuildingExternalLocationType!element] = BuildingXmlAddressLocationTypeMap.ExternalCode,
        [OrderLocation!1!BuildingCrossStreet!element] = Buildings.CrossStreet,
        [OrderLocation!1!BuildingServiceEntrance!element] = Buildings.ServiceEntrance,
        [OrderLocation!1!BuildingSiteNote!element] = Buildings.SiteNote,
        [OrderLocationAddress!2!AddressLocationType!element] = null,
        [OrderLocationAddress!2!Address!element] = null,
        [OrderLocationAddress!2!Address2!element] = null,
        [OrderLocationAddress!2!Address3!element] = null,
        [OrderLocationAddress!2!City!element] = null,
        [OrderLocationAddress!2!State!element] = null,
        [OrderLocationAddress!2!Zip!element] = null,
        [OrderLocationAddress!2!Country!element] = null,
        [OrderLocationAddress!2!CountryName!element] = null,
        [BuildingConstraint!3!Description!element] = null,
        [BuildingConstraint!3!ConstraintFlag!element] = null,
        [BuildingConstraint!3!Note!element] = null
    from OrderLocations
    left outer join OrderContacts on OrderContacts.OrderContactID = OrderLocations.OrderContactFID
    left outer join OrderLocationAddressType on
        OrderLocationAddressType.OrderLocationAddressTypeID = OrderLocations.OrderLocationAddressTypeFID    
    left outer join Buildings on Buildings.BuildingID = OrderLocations.BuildingFID
    left outer join XmlAddressLocationTypeMap as BuildingXmlAddressLocationTypeMap on
        BuildingXmlAddressLocationTypeMap.AddressLocationTypeFID = Buildings.AddressLocationTypeFID and
        BuildingXmlAddressLocationTypeMap.XmlInterfaceFID = @inXmlInterfaceID
    where OrderContacts.OrderFID = @inOrderID
    union all
    -- Get the addresses
    select
        2         as tag,
        1     as parent,
        [OrderLocation!1!Name!element] = OrderLocations.[Name],
        [OrderLocation!1!Contact!element] = OrderContacts.Contact,
        [OrderLocation!1!ContactName!element] = OrderContacts.[Name],
        [OrderLocation!1!LocationType!element] = OrderLocationAddressType.TypeName,
        [OrderLocation!1!Building!element] = Buildings.[Description],
        [OrderLocation!1!BuildingExternalLocationType!element] = BuildingXmlAddressLocationTypeMap.ExternalCode,
        [OrderLocation!1!BuildingCrossStreet!element] = null,
        [OrderLocation!1!BuildingServiceEntrance!element] = null,
        [OrderLocation!1!BuildingSiteNote!element] = null,        
        [OrderLocationAddress!2!AddressLocationType!element] = AddressLocationType.[Description],
        [OrderLocationAddress!2!Address!element] = OrderLocationAddress.Address1,
        [OrderLocationAddress!2!Address2!element] = OrderLocationAddress.Address2,
        [OrderLocationAddress!2!Address3!element] = OrderLocationAddress.Address3,
        [OrderLocationAddress!2!City!element] = OrderLocationAddress.City,
        [OrderLocationAddress!2!State!element] = OrderLocationAddress.[State],
        [OrderLocationAddress!2!Zip!element] = OrderLocationAddress.PostalCode,
        [OrderLocationAddress!2!Country!element] = CountryCodeStandard.VanlineCountryCode,
        [OrderLocationAddress!2!CountryName!element] = CountryCodeStandard.CountryName,
        [BuildingConstraint!3!Description!element] = null,
        [BuildingConstraint!3!ConstraintFlag!element] = null,
        [BuildingConstraint!3!Note!element] = null
    from OrderLocations
    left outer join OrderContacts on OrderContacts.OrderContactID = OrderLocations.OrderContactFID
    left outer join OrderLocationAddressType on
        OrderLocationAddressType.OrderLocationAddressTypeID = OrderLocations.OrderLocationAddressTypeFID
    left outer join Buildings on Buildings.BuildingID = OrderLocations.BuildingFID
    left outer join XmlAddressLocationTypeMap as BuildingXmlAddressLocationTypeMap on
        BuildingXmlAddressLocationTypeMap.AddressLocationTypeFID = Buildings.AddressLocationTypeFID and
        BuildingXmlAddressLocationTypeMap.XmlInterfaceFID = @inXmlInterfaceID
    left outer join OrderLocationAddress on
        OrderLocationAddress.OrderLocationFID = OrderLocations.OrderLocationID and
        OrderLocationAddress.AddressTypeFID = @theMainAddressTypeId
    left outer join AddressLocationType on AddressLocationType.AddressLocationTypeID = OrderLocationAddress.AddressLocationTypeFID
    left outer join CountryCodeStandard on
        CountryCodeStandard.CountryCodeStandardID = OrderLocationAddress.CountryCodeStandardFID
    where OrderContacts.OrderFID = @inOrderID
    union all
    -- Get the building constraints
    select
        3         as tag,
        1     as parent,
        [OrderLocation!1!Name!element] = OrderLocations.[Name],
        [OrderLocation!1!Contact!element] = OrderContacts.Contact,
        [OrderLocation!1!ContactName!element] = OrderContacts.[Name],
        [OrderLocation!1!LocationType!element] = OrderLocationAddressType.TypeName,
        [OrderLocation!1!Building!element] = Buildings.[Description],
        [OrderLocation!1!BuildingExternalLocationType!element] = BuildingXmlAddressLocationTypeMap.ExternalCode,
        [OrderLocation!1!BuildingCrossStreet!element] = null,
        [OrderLocation!1!BuildingServiceEntrance!element] = null,
        [OrderLocation!1!BuildingSiteNote!element] = null,
        [OrderLocationAddress!2!AddressLocationType!element] = null,
        [OrderLocationAddress!2!Address!element] = null,
        [OrderLocationAddress!2!Address2!element] = null,
        [OrderLocationAddress!2!Address3!element] = null,
        [OrderLocationAddress!2!City!element] = null,
        [OrderLocationAddress!2!State!element] = null,
        [OrderLocationAddress!2!Zip!element] = null,
        [OrderLocationAddress!2!Country!element] = null,
        [OrderLocationAddress!2!CountryName!element] = null,
        [BuildingConstraint!3!Description!element] = BuildingConstraintItems.[Description],
        [BuildingConstraint!3!ConstraintFlag!element] = OrderLocationBuildingConstraint.ConstraintFlag,
        [BuildingConstraint!3!Note!element] = OrderLocationBuildingConstraint.Note
    from OrderLocations    
    left outer join OrderContacts on OrderContacts.OrderContactID = OrderLocations.OrderContactFID
    left outer join OrderLocationAddressType on
        OrderLocationAddressType.OrderLocationAddressTypeID = OrderLocations.OrderLocationAddressTypeFID    
    left outer join Buildings on Buildings.BuildingID = OrderLocations.BuildingFID    
    left outer join XmlAddressLocationTypeMap as BuildingXmlAddressLocationTypeMap on
        BuildingXmlAddressLocationTypeMap.AddressLocationTypeFID = Buildings.AddressLocationTypeFID and
        BuildingXmlAddressLocationTypeMap.XmlInterfaceFID = @inXmlInterfaceID
    left outer join OrderLocationBuildingConstraint on
        OrderLocationBuildingConstraint.OrderLocationFID = OrderLocations.OrderLocationID
    left outer join BuildingConstraintItems on
        BuildingConstraintItems.BuildingConstraintItemID = OrderLocationBuildingConstraint.BuildingConstraintItemFID
    where OrderLocations.OrderFID = @inOrderID
    order by
        [OrderLocation!1!Name!element],
        [OrderLocation!1!Contact!element],
        [OrderLocation!1!ContactName!element],
        [OrderLocation!1!LocationType!element],
        [OrderLocation!1!Building!element],
        [OrderLocation!1!BuildingExternalLocationType!element],
        [OrderLocationAddress!2!AddressLocationType!element],
        [OrderLocationAddress!2!Address!element],
        [OrderLocationAddress!2!Zip!element],
        [BuildingConstraint!3!Description!element],
        [BuildingConstraint!3!Note!element]
    for xml explicit;

    set @outStatusCode = @STATUS_CODE_PROCESSED;
end
GO
GRANT EXECUTE ON  [dbo].[XmlExportOrderLocation] TO [MssExec]
GO