/**
*    
*    This procedure is called to generate the xml segment for order contacts (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].[XmlExportOrderContact]
    @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
declare @theMainPhoneTypeId int
declare @theFaxPhoneTypeId int
declare @theMobilePhoneTypeId 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'
    select @theMainPhoneTypeId = OrderContactPhoneType.OrderContactPhoneTypeID from OrderContactPhoneType where TypeName = 'Main'
    select @theFaxPhoneTypeId = OrderContactPhoneType.OrderContactPhoneTypeID from OrderContactPhoneType where TypeName = 'Fax'
    select @theMobilePhoneTypeId = OrderContactPhoneType.OrderContactPhoneTypeID from OrderContactPhoneType where TypeName = 'Mobile'
    
    -- Get the order contacts
    select
        1         as tag,
        null     as parent,
        [OrderContact!1!Name!element] = OrderContacts.[Name],
        [OrderContact!1!Contact!element] = OrderContacts.Contact,
        [OrderContact!1!Title!element] = OrderContacts.Title,
        [OrderContact!1!Email!element] = OrderContacts.Email,
        [OrderContact!1!Address!element] = OrderContactAddress.Address1,
        [OrderContact!1!Address2!element] = OrderContactAddress.Address2,
        [OrderContact!1!Address3!element] = OrderContactAddress.Address3,
        [OrderContact!1!City!element] = OrderContactAddress.City,
        [OrderContact!1!State!element] = OrderContactAddress.State,
        [OrderContact!1!Zip!element] = OrderContactAddress.PostalCode,
        [OrderContact!1!Country!element] = CountryCodeStandard.VanlineCountryCode,
        [OrderContact!1!CountryName!element] = CountryCodeStandard.CountryName,
        [MainPhone!2!Number!element] = null,
        [MainPhone!2!Country!element] = null,
        [MainPhone!2!CountryName!element] = null,
        [MainPhone!2!CountryCode!element] = null,
        [MainPhone!2!AreaCode!element] = null,
        [MainPhone!2!LocalNumber!element] = null,
        [MainPhone!2!Extension!element] = null,
        [Fax!3!Number!element] = null,
        [Fax!3!Country!element] = null,
        [Fax!3!CountryName!element] = null,
        [Fax!3!CountryCode!element] = null,
        [Fax!3!AreaCode!element] = null,
        [Fax!3!LocalNumber!element] = null,
        [Fax!3!Extension!element] = null,
        [MobilePhone!4!Number!element] = null,
        [MobilePhone!4!Country!element] = null,
        [MobilePhone!4!CountryName!element] = null,
        [MobilePhone!4!CountryCode!element] = null,
        [MobilePhone!4!AreaCode!element] = null,
        [MobilePhone!4!LocalNumber!element] = null,
        [MobilePhone!4!Extension!element] = null
    from OrderContacts
    left outer join OrderContactAddress on
        OrderContactAddress.OrderContactFID = OrderContacts.OrderContactID and
        OrderContactAddress.AddressTypeFID = @theMainAddressTypeId
    left outer join CountryCodeStandard on
        CountryCodeStandard.CountryCodeStandardID = OrderContactAddress.CountryCodeStandardFID
    where OrderContacts.OrderFID = @inOrderID
    union all
    -- Get the order contact's main phone number
    select
        2 as tag,
        1 as parent,
        [OrderContact!1!Name!element] = OrderContacts.[Name],
        [OrderContact!1!Contact!element] = OrderContacts.Contact,
        [OrderContact!1!Title!element] = OrderContacts.Title,
        [OrderContact!1!Email!element] = OrderContacts.Email,
        [OrderContact!1!Address!element] = null,
        [OrderContact!1!Address2!element] = null,
        [OrderContact!1!Address3!element] = null,
        [OrderContact!1!City!element] = null,
        [OrderContact!1!State!element] = null,
        [OrderContact!1!Zip!element] = null,
        [OrderContact!1!Country!element] = null,
        [OrderContact!1!CountryName!element] = null,
        [MainPhone!2!Number!element] =    dbo.GetFormattedPhoneNumber( MainPhone.CountryCodeStandardFID, MainPhone.AreaCode, MainPhone.LocalNumber ),
        [MainPhone!2!Country!element] = MainPhoneCountryCodeStandard.VanlineCountryCode,
        [MainPhone!2!CountryName!element] = MainPhoneCountryCodeStandard.CountryName,
        [MainPhone!2!CountryCode!element] = MainPhoneCountryCodeStandard.PhoneCountryCode,
        [MainPhone!2!AreaCode!element] = MainPhone.AreaCode,
        [MainPhone!2!LocalNumber!element] = MainPhone.LocalNumber,
        [MainPhone!2!Extension!element]    = MainPhone.Extension,
        [Fax!3!Number!element] = null,
        [Fax!3!Country!element] = null,
        [Fax!3!CountryName!element] = null,
        [Fax!3!CountryCode!element]    = null,
        [Fax!3!AreaCode!element]    = null,
        [Fax!3!LocalNumber!element]    = null,
        [Fax!3!Extension!element]    = null,
        [MobilePhone!4!Number!element] =    null,
        [MobilePhone!4!Country!element] = null,
        [MobilePhone!4!CountryName!element] = null,
        [MobilePhone!4!CountryCode!element]    = null,
        [MobilePhone!4!AreaCode!element]    = null,
        [MobilePhone!4!LocalNumber!element] = null,
        [MobilePhone!4!Extension!element] = null
    from OrderContacts
    left outer join OrderContactPhoneNumber as MainPhone on
        MainPhone.OrderContactFID = OrderContacts.OrderContactID and
        MainPhone.OrderContactPhoneTypeFID = @theMainPhoneTypeId
    left outer join CountryCodeStandard as MainPhoneCountryCodeStandard on
        MainPhoneCountryCodeStandard.CountryCodeStandardID = MainPhone.CountryCodeStandardFID
    where OrderContacts.OrderFID = @inOrderID
    union all
    -- Get the order contact's fax phone number
    select
        3 as tag,
        1 as parent,
        [OrderContact!1!Name!element] = OrderContacts.[Name],
        [OrderContact!1!Contact!element] = OrderContacts.Contact,
        [OrderContact!1!Title!element] = OrderContacts.Title,
        [OrderContact!1!Email!element] = OrderContacts.Email,
        [OrderContact!1!Address!element] = null,
        [OrderContact!1!Address2!element] = null,
        [OrderContact!1!Address3!element] = null,
        [OrderContact!1!City!element] = null,
        [OrderContact!1!State!element] = null,
        [OrderContact!1!Zip!element] = null,
        [OrderContact!1!Country!element] = null,
        [OrderContact!1!CountryName!element] = null,
        [MainPhone!2!Number!element] = null,
        [MainPhone!2!Country!element] = null,
        [MainPhone!2!CountryName!element] = null,
        [MainPhone!2!CountryCode!element]    = null,
        [MainPhone!2!AreaCode!element]    = null,
        [MainPhone!2!LocalNumber!element]    = null,
        [MainPhone!2!Extension!element]    = null,
        [Fax!3!Number!element] =    dbo.GetFormattedPhoneNumber( FaxPhone.CountryCodeStandardFID, FaxPhone.AreaCode, FaxPhone.LocalNumber ),
        [Fax!3!Country!element] = FaxPhoneCountryCodeStandard.VanlineCountryCode,
        [Fax!3!CountryName!element] = FaxPhoneCountryCodeStandard.CountryName,
        [Fax!3!CountryCode!element] = FaxPhoneCountryCodeStandard.PhoneCountryCode,
        [Fax!3!AreaCode!element] = FaxPhone.AreaCode,
        [Fax!3!LocalNumber!element] = FaxPhone.LocalNumber,
        [Fax!3!Extension!element]    = FaxPhone.Extension,        
        [MobilePhone!4!Number!element] =    null,
        [MobilePhone!4!Country!element] = null,
        [MobilePhone!4!CountryName!element] = null,
        [MobilePhone!4!CountryCode!element]    = null,
        [MobilePhone!4!AreaCode!element]    = null,
        [MobilePhone!4!LocalNumber!element] = null,
        [MobilePhone!4!Extension!element] = null
    from OrderContacts
    left outer join OrderContactPhoneNumber as FaxPhone on
        FaxPhone.OrderContactFID = OrderContacts.OrderContactID and
        FaxPhone.OrderContactPhoneTypeFID = @theFaxPhoneTypeId
    left outer join CountryCodeStandard as FaxPhoneCountryCodeStandard on
        FaxPhoneCountryCodeStandard.CountryCodeStandardID = FaxPhone.CountryCodeStandardFID
    where OrderContacts.OrderFID = @inOrderID
    union all
    -- Get the order contact's mobile phone number
    select
        4 as tag,
        1 as parent,
        [OrderContact!1!Name!element] = OrderContacts.[Name],
        [OrderContact!1!Contact!element] = OrderContacts.Contact,
        [OrderContact!1!Title!element] = OrderContacts.Title,
        [OrderContact!1!Email!element] = OrderContacts.Email,
        [OrderContact!1!Address!element] = null,
        [OrderContact!1!Address2!element] = null,
        [OrderContact!1!Address3!element] = null,
        [OrderContact!1!City!element] = null,
        [OrderContact!1!State!element] = null,
        [OrderContact!1!Zip!element] = null,
        [OrderContact!1!Country!element] = null,
        [OrderContact!1!CountryName!element] = null,
        [MainPhone!2!Number!element] = null,
        [MainPhone!2!Country!element] = null,
        [MainPhone!2!CountryName!element] = null,
        [MainPhone!2!CountryCode!element]    = null,
        [MainPhone!2!AreaCode!element]    = null,
        [MainPhone!2!LocalNumber!element]    = null,
        [MainPhone!2!Extension!element]    = null,
        [Fax!3!Number!element] =    null,
        [Fax!3!Country!element] = null,
        [Fax!3!CountryName!element] = null,
        [Fax!3!CountryCode!element]    = null,
        [Fax!3!AreaCode!element]    = null,
        [Fax!3!LocalNumber!element] = null,
        [Fax!3!Extension!element] = null,
        [MobilePhone!4!Number!element] =    dbo.GetFormattedPhoneNumber( MobilePhone.CountryCodeStandardFID, MobilePhone.AreaCode, MobilePhone.LocalNumber ),
        [MobilePhone!4!Country!element] = MobilePhoneCountryCodeStandard.VanlineCountryCode,
        [MobilePhone!4!CountryName!element] = MobilePhoneCountryCodeStandard.CountryName,
        [MobilePhone!4!CountryCode!element] = MobilePhoneCountryCodeStandard.PhoneCountryCode,
        [MobilePhone!4!AreaCode!element] = MobilePhone.AreaCode,
        [MobilePhone!4!LocalNumber!element] = MobilePhone.LocalNumber,
        [MobilePhone!4!Extension!element]    = MobilePhone.Extension
    from OrderContacts
    left outer join OrderContactPhoneNumber as MobilePhone on
        MobilePhone.OrderContactFID = OrderContacts.OrderContactID and
        MobilePhone.OrderContactPhoneTypeFID = @theMobilePhoneTypeId
    left outer join CountryCodeStandard as MobilePhoneCountryCodeStandard on
        MobilePhoneCountryCodeStandard.CountryCodeStandardID = MobilePhone.CountryCodeStandardFID
    where OrderContacts.OrderFID = @inOrderID
    order by
        [OrderContact!1!Name!element],
        [OrderContact!1!Contact!element],
        [OrderContact!1!Title!element],
        [OrderContact!1!Email!element],
        [MobilePhone!4!Number!element],
        [Fax!3!Number!element],
        [MainPhone!2!Number!element]
    for xml explicit;

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