/*
* Gets order information to send to Wheaton. This returns multiple result sets, all of which return data formatted for Wheaton:
* 1) Order information
* 2) Order addresses
* 3) Order phone numbers
* 4) Order emails
* 5) Order SIT info
*
* @param @inOrderId - Primary key of a MoversSuite order.
*/
CREATE procedure [dbo].[GetWheatonOrderInformation]
    @inOrderId int

as
    declare @theWheatonXmlInterfaceId int
    declare @theWheatonXmlSystemId int

    select @theWheatonXmlSystemId = XmlSystemFID
    from XmlSystemDataTypeMap
    where
        [Name] = 'Wheaton Order Information'

    select @theWheatonXmlInterfaceId = XmlInterfaceFID
    from XmlSystem
    where XmlSystemID = @theWheatonXmlSystemId

    -- First result set: Order information
    select
        XmlUserMap.UserID as SalespersonId,
        nullif( ltrim( rtrim( Sysuser.FIRSTNAME ) ), '' ) as FirstName,
        nullif( ltrim( rtrim( Sysuser.LASTNAME ) ), '' ) as LastName,
        Orders.PriKey as OrderPriKey,
        case
            when Orders.IsLead = '1' then 'LNW'
            else 'INC'
        end as OrderStatus,
        BookingAgent.AgentID as BookingAgent,
        HaulingAgent.AgentID as HaulingAgent,
        OriginAgent.AgentID as OriginAgent,
        DestinationAgent.AgentID as DestinationAgent,
        case
            when Orders.SelfHaul= 'Y' then cast(1 as bit)
            when Orders.SelfHaul='N' then cast(0 as bit)
        end as SelfHaul,
        case
            when SisterHaulAgencyBranch.BranchPriKey is null then cast( 0 as bit )
            else cast( 1 as bit )
        end    as IsHaulAgentSistserAgent,
        Orders.BookDate as BookingDate,
        Orders.Miles,
        Orders.ActLineHaul as DiscountedLinehaul,
        case
            when isnull( Orders.EstimatedWeight, 0 ) > 0 then Orders.EstimatedWeight
            else null
        end as EstimateWeight,
        case
            when isnull( Orders.[Weight], 0 ) > 0 then Orders.[Weight]
            else null
        end as ActualWeight,
        Orders.CubicFeet as [Cube],
        Orders.StartPack as PackingStart,
        Orders.EndPack as PackingEnd,
        Orders.StartLoad as LoadStart,
        Orders.EndLoad as LoadEnd,
        Orders.StartDeliv as DeliveryStart,
        Orders.EndDeliv as DeliveryEnd,
        Orders.ValuationAmount,
        nullif( ltrim( rtrim( left( Orders.CreditAuth, 9 ) ) ), '' ) as CreditAuthorization,
        XmlShipmentTypeMap.ExternalCode as ShipmentType,
        XmlValuationVanLineCodeMap.VanLineCode as ValuationType,
        XmlInterfacePayTypeMap.ExternalPayTypeCode as PaymentType,
        nullif( ltrim( rtrim( left( XmlInterfaceAccountMap.ExternalCode, 9 ) ) ), '' ) as AccountNumber,
        RatePlans.VanLineCode AS Tariff,
        case
            when isnull( Orders.Discount, 200 ) <= 100.00 then cast( Orders.Discount as decimal(5,2) )
            when isnull( Orders.Discount, 50 ) > 100.00 then cast( ( Orders.Discount / 100 ) as decimal(5,2) )
            else null
        end as DiscountPercent,
        case
            when isnull( SITJointInfo.SITDiscount, 200 ) <= 100.00 then cast( SITJointInfo.SITDiscount as decimal(5,2) )
            when isnull( SITJointInfo.SITDiscount, 50 ) > 100.00 then cast( ( SITJointInfo.SITDiscount / 100 ) as decimal(5,2) )
            else null
        end as SitDiscount
    from dbo.Orders
    left outer join dbo.XMLUserMap on Orders.SalesPerson = dbo.XmlUserMap.SysUserFID and dbo.XmlUserMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
    left outer join dbo.XmlValuationVanLineCodeMap on dbo.Orders.ValuationFID = dbo.XmlValuationVanLineCodeMap.ValuationFID and dbo.XmlValuationVanLineCodeMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
    left outer join dbo.XmlInterfacePayTypeMap on dbo.Orders.PayTypeFID = XmlInterfacePayTypeMap.PayTypeFID and dbo.XmlInterfacePayTypeMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
    left outer join dbo.XmlShipmentTypeMap on dbo.Orders.ShipmentTypeFID = XmlShipmentTypeMap.ShipmentTypeFID and dbo.XmlShipmentTypeMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
    left outer join dbo.SysUser on dbo.Orders.SalesPerson = dbo.Sysuser.SysUserID
    left outer join dbo.Branch as SisterHaulAgencyBranch on SisterHaulAgencyBranch.AgentPriKey = dbo.Orders.HaulAgent
    left outer join dbo.Accounts on dbo.Orders.AcctPriKey = Accounts.AccountPriKey
    left outer join dbo.SITJointInfo on dbo.Orders.PriKey = dbo.SITJointInfo.OrdersFID
    left outer join dbo.MilitaryOrder on dbo.Orders.PriKey = dbo.MilitaryOrder.OrdersFID
    left outer join dbo.MilitaryCarrier on dbo.MilitaryOrder.MilitaryCarrierFID = MilitaryCarrier.MilitaryCarrierID
    left outer join dbo.MilitaryWeight on dbo.MilitaryWeight.OrdersFID = dbo.Orders.PriKey
    left outer join dbo.RatePlans on dbo.Orders.RPPriKey = dbo.RatePlans.RPPriKey
    left outer join Agent as BookingAgent on Orders.BookAgent = BookingAgent.AgentPriKey
    left outer join Agent as HaulingAgent on Orders.HaulAgent = HaulingAgent.AgentPriKey
    left outer join Agent as OriginAgent on Orders.OrgAgent = OriginAgent.AgentPriKey
    left outer join Agent as DestinationAgent on Orders.DestAgent = DestinationAgent.AgentPriKey
    left outer join XmlInterfaceAccountMap on XmlInterfaceAccountMap.AccountFid = Orders.AcctPriKey and XmlInterfaceAccountMap.XmlInterfaceFid = @theWheatonXmlInterfaceId
    where Orders.PriKey = @inOrderId

    -- Second result set: Order addresses
    select
        case
            when AddressType.TypeName = 'Origin' then 'O'
            else 'D'
        end as AddressLocationType,
        AddressType.TypeName as [Description],
        nullif( ltrim( rtrim( Orders.FirstName ) ), '' ) as FirstName,
        nullif( ltrim( rtrim( Orders.LastName ) ), '' ) as LastName,
        nullif( ltrim( rtrim( left( OrderAddress.Address1, 100 ) ) ), '' ) as Street1,
        nullif( ltrim( rtrim( left( OrderAddress.Address2, 100 ) ) ), '' ) as Street2,
        nullif( ltrim( rtrim( OrderAddress.City ) ), '' ) as City,
        OrderAddress.[State],
        OrderAddress.PostalCode,
        case
            when
                len( OrderAddress.PostalCode ) > 5 and
                left( OrderAddress.PostalCode, 5 ) like '[0-9][0-9][0-9][0-9][0-9]'
                then dbo.ExtractNumericCharacters( substring( OrderAddress.PostalCode, 6 , len( OrderAddress.PostalCode ) - 5 ) )
            when
                len( OrderAddress.PostalCode ) > 6 and
                left( OrderAddress.PostalCode, 6 ) like '[a-zA-Z][0-9][a-zA-Z][0-9][a-zA-Z][0-9]'
                then dbo.ExtractNumericCharacters( substring( OrderAddress.PostalCode, 7 , len( OrderAddress.PostalCode ) - 6 ) )
            else null
        end as PostalExt
    from OrderAddress
    left outer join AddressType on AddressType.AddressTypeID = OrderAddress.AddressTypeFID
    left outer join Orders on Orders.PriKey = OrderAddress.OrderFID
    where
        OrderAddress.OrderFID = @inOrderId and
        AddressType.TypeName in ( 'Origin', 'Destination' )

    -- Third result set: Order phone numbers
    select
        XmlOrderPhoneTypeMap.ExternalCode as PhoneType,
        isnull( OrderPhoneNumber.AreaCode, '' ) + OrderPhoneNumber.LocalNumber as PhoneNumber,
        nullif( ltrim( rtrim( left( OrderPhoneNumber.Extension, 6 ) ) ), '' ) as PhoneExt,
        case
            when
                OrderPhoneType.TypeName like '%Destination%' or
                OrderPhoneType.TypeName like '%Moving To%'
                then cast(0 as bit)
            else cast(1 as bit)
        end as IsOrigin,
        case
            when
                OrderPhoneType.TypeName like '%Destination%' or
                OrderPhoneType.TypeName like '%Moving To%'
                then cast(1 as bit)
            else cast(0 as bit)
        end as IsDestination
    from OrderPhoneNumber
    left outer join XmlOrderPhoneTypeMap on
        XmlOrderPhoneTypeMap.OrderPhoneTypeFID = OrderPhoneNumber.OrderPhoneTypeFID and
        XmlOrderPhoneTypeMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
    left outer join OrderPhoneType on OrderPhoneType.OrderPhoneTypeID = OrderPhoneNumber.OrderPhoneTypeFID
    where
        OrderPhoneNumber.OrderFID = @inOrderId and
        OrderPhoneType.IsShipperPhoneType = 1

    -- Fourth result set: Order emails
    select
        XmlOrderEmailTypeMap.ExternalCode as EmailType ,
        nullif( ltrim( rtrim( left( OrderEmailAddress.EmailAddress, 60 ) ) ), '' ) as EmailAddress
    from OrderEmailAddress
    inner join XmlOrderEmailTypeMap on
        XmlOrderEmailTypeMap.OrderEmailTypeFID = OrderEmailAddress.OrderEmailTypeFID and
        XmlOrderEmailTypeMap.XmlInterfaceFID = @theWheatonXmlInterfaceId    
    where dbo.OrderEmailAddress.OrderFID = @inOrderId

    -- Fifth result set: Order SIT info
    select
        case
            when SITType.[Type] = 'Origin' then 'O'
            when SITType.[Type] = 'Destination' then 'D'
            else null
        end as SitType,
        SITInformation.EstimatedInDate as InDate,
        SITInformation.EstimatedOutDate as OutDate,
        SITInformation.[Days] as NumSitDays,
        SITInformation.[Weight],
        SITInformation.DrayageMiles as Miles
    from SITInformation
    left outer join SITType on SITInformation.SITTypeFID = SITType.SITTypeID
    left outer join Agent on Agent.AgentPriKey = SITInformation.AgentFID
    where SITInformation.OrdersFID = @inOrderId
GO
GRANT EXECUTE ON  [dbo].[GetWheatonOrderInformation] TO [MssExec]
GO