/*
*    Validates the order info that we send to Wheaton when uploading an order to their web service.
*    Returns a bunch of bit flags indicating whether or not there is a problem with a specific field's data.
* This returns multiple result sets, all of which return flags that are true if there is an issue, or false if there is no issue.
* 1) Order information issue flags (includes SIT issue flags)
* 2) Order addresses issue flags
* 3) Order phone numbers issue flags
* 4) Order emails issue flags
*
* @param @inOrderId - Primary key of a MoversSuite order.
*/
create procedure [dbo].[ValidateWheatonOrder]
    @inOrderId int
as

declare @theWheatonXmlInterfaceId int
declare @theWheatonXmlSystemId int
declare @theSalespersonMappingIsMissing bit
declare @theSalespersonWrongLength bit
declare @theBookAgentIsMissing bit
declare @theBookAgentTooShort bit
declare @theHaulAgentTooShort bit
declare @theOriginAgentTooShort bit
declare @theDestinationAgentTooShort bit
declare @theShipmentTypeIsMissing bit
declare @theShipmentTypeMappingIsMissing bit
declare @theShipmentTypeInvalid bit
declare @theValuationTypeMappingIsMissing bit
declare @theValuationTypeInvalid bit
declare @theMilesTooLarge bit
declare @thePaymentTypeMappingIsMissing bit
declare @theInvalidPaymentType bit
declare @theAccountNumberMappingIsMissing bit
declare @theInvalidAccountNumber bit
declare @theTariffAndSalespersonAreMissing bit
declare @theRatePlanCodeMissing bit
declare @theRatePlanInvalid bit
declare @theUserIdIsMissing bit
declare @theAgentIdIsMissing bit
declare @theWebServiceUrlIsMissing bit
declare @theTokenIsMissing bit
declare @theDiscountInvalid bit
declare @theSitDiscountInvalid bit
declare @theEstimatedWeightIsMissing bit
declare @theLoadDateIsMissing bit

-- Get the Wheaton XmlInterface and XmlSystem:
select @theWheatonXmlSystemId = XmlSystemFID
    from XmlSystemDataTypeMap
    where
        [Name] = 'Wheaton Order Information'

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

-- Validate the Wheaton order info:
select

    -- Wheaton requires that a tariff or salesperson be set on the order.
    @theTariffAndSalespersonAreMissing =
        case
            when
                Orders.RPPriKey is null and
                Sysuser.SysUserID is null
                then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theSalespersonMappingIsMissing =
        case
            when
                Sysuser.SysUserID is not null and
                dbo.IsNullOrWhiteSpace( XMLUserMap.UserID ) = 1
                then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theSalespersonWrongLength =
        case
            when
                Sysuser.SysUserID is not null and
                @theSalespersonMappingIsMissing = 0 and
                ( len( XMLUserMap.UserID ) < 5 or len( XmlUserMap.UserID ) > 15 )
                then cast( 1 as bit )
            else cast( 0 as bit )
        end,

    /*    The following fields are always required:
            ShipmentType
            BookingAgent */
    @theShipmentTypeIsMissing =
        case
            when Orders.ShipmentTypeFID is null then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theShipmentTypeMappingIsMissing =
        case
            when dbo.IsNullOrWhiteSpace( XmlShipmentTypeMap.ExternalCode ) = 1 then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theShipmentTypeInvalid =
        case
            when
                @theShipmentTypeIsMissing = 0 and
                @theShipmentTypeMappingIsMissing = 0 and
                XmlShipmentTypeMap.ExternalCode not in ( 'DP3', 'GSA', 'IND', 'NAT' )
                then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theBookAgentIsMissing = dbo.IsNullOrWhiteSpace( BookingAgent.AgentID ),
    @theBookAgentTooShort =
        case
            when
                @theBookAgentIsMissing = 0 and
                len( dbo.ExtractNumericCharacters( BookingAgent.AgentID ) ) < 4
                then cast( 1 as bit )
            else cast( 0 as bit )
        end,

    /*    The following fields are not required, but if they are specified then they need to be validated:
        HaulingAgent,
        OriginAgent,
        DestinationAgent,
        Miles
    */
    @theHaulAgentTooShort =
        case
            when
                dbo.IsNotNullOrWhiteSpace( HaulingAgent.AgentID ) = 1 and
                len( dbo.ExtractNumericCharacters( HaulingAgent.AgentID ) ) < 4
                then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theOriginAgentTooShort =
        case
            when
                dbo.IsNotNullOrWhiteSpace( OriginAgent.AgentID ) = 1 and
                len( dbo.ExtractNumericCharacters( OriginAgent.AgentID ) ) < 4
                then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theDestinationAgentTooShort =
        case
            when
                dbo.IsNotNullOrWhiteSpace( DestinationAgent.AgentID ) = 1 and
                len( dbo.ExtractNumericCharacters( DestinationAgent.AgentID ) ) < 4
                then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theMilesTooLarge =
        case
            when
                Orders.Miles is not null and
                Orders.Miles > 999999
                then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theRatePlanCodeMissing =
        case
            when
                Orders.RPPriKey is not null and
                dbo.IsNullOrWhiteSpace( RatePlans.VanLineCode ) = 1
                then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theRatePlanInvalid =
        case
            when
                Orders.RPPriKey is not null and
                @theRatePlanCodeMissing = 0 and
                dbo.IsNotNullOrWhiteSpace( RatePlans.VanLineCode ) = 1 and
                RatePlans.VanLineCode not in ( '100', '400', '4NG', 'LOG', 'TRU', 'WVL', 'WRT' )
                then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @thePaymentTypeMappingIsMissing =
        case
            when
                Orders.PayTypeFID is not null and
                dbo.IsNullOrWhiteSpace( XmlInterfacePayTypeMap.ExternalPayTypeCode ) = 1
                then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theInvalidPaymentType =
        case
            when
                dbo.IsNotNullOrWhiteSpace( XmlInterfacePayTypeMap.ExternalPayTypeCode ) = 1 and
                XmlInterfacePayTypeMap.ExternalPayTypeCode not in ( 'BIL', 'COD', 'CRE' )
                then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theAccountNumberMappingIsMissing =
        case
            when
                Orders.AcctPriKey is not null and
                dbo.IsNullOrWhiteSpace( XmlInterfaceAccountMap.ExternalCode ) = 1
                then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theInvalidAccountNumber =
        case
            when
                dbo.IsNotNullOrWhiteSpace( XmlInterfaceAccountMap.ExternalCode ) = 1 and
                ( try_convert( int, XmlInterfaceAccountMap.ExternalCode ) is null or
                cast( XmlInterfaceAccountMap.ExternalCode as int ) > 999999999 )
                then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theValuationTypeMappingIsMissing =
        case
            when
                Orders.ValuationFID is not null and
                XmlValuationVanLineCodeMap.XmlValuationVanLineCodeMapID is null
                then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theValuationTypeInvalid =
        case
            when
                dbo.IsNotNullOrWhiteSpace( XmlValuationVanLineCodeMap.VanLineCode ) = 1 and
                XmlValuationVanLineCodeMap.VanLineCode not in ( 'R', 'B', 'C', 'D', 'S', 'L' )
                then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theUserIdIsMissing =
        case
            when dbo.IsNullOrWhiteSpace( UserIdXmlSystemOption.[Value] ) = 1 then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theAgentIdIsMissing =
        case
            when dbo.IsNullOrWhiteSpace( AgentIdXmlSystemOption.[Value] ) = 1 then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theWebServiceUrlIsMissing =
        case
            when dbo.IsNullOrWhiteSpace( WebServiceUrlXmlSystemOption.[Value] ) = 1 then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theTokenIsMissing =
        case
            when dbo.IsNullOrWhiteSpace( TokenValidation.KeyValue ) = 1 then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theDiscountInvalid =
        case
            when
                Orders.Discount is not null and
                ( Orders.Discount not between 0.00 and 999.99 or
                cast( Orders.Discount as decimal(5,2) ) <> Orders.Discount )
                then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theSitDiscountInvalid =
        case
            when
                SITJointInfo.SITDiscount is not null and
                ( SITJointInfo.SITDiscount not between 0.0 and 999.99 or
                cast( SITJointInfo.SITDiscount as decimal(5,2) ) <> SITJointInfo.SITDiscount )
                then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theEstimatedWeightIsMissing =
        case
            when isnull( Orders.EstimatedWeight, 0 ) < 1 then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theLoadDateIsMissing =
        case
            when Orders.StartLoad is null then cast( 1 as bit )
            else cast( 0 as bit )
        end
from Orders
left outer join XMLUserMap on Orders.SalesPerson = XmlUserMap.SysUserFID and XmlUserMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
left outer join XmlValuationVanLineCodeMap on Orders.ValuationFID = XmlValuationVanLineCodeMap.ValuationFID and XmlValuationVanLineCodeMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
left outer join XmlInterfacePayTypeMap on Orders.PayTypeFID = XmlInterfacePayTypeMap.PayTypeFID and XmlInterfacePayTypeMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
left outer join XmlShipmentTypeMap on Orders.ShipmentTypeFID = XmlShipmentTypeMap.ShipmentTypeFID and XmlShipmentTypeMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
left outer join SysUser on Orders.SalesPerson = Sysuser.SysUserID
left outer join Branch as SisterHaulAgencyBranch on SisterHaulAgencyBranch.AgentPriKey = Orders.HaulAgent
left outer join Accounts on Orders.AcctPriKey = Accounts.AccountPriKey
left outer join SITJointInfo on Orders.PriKey = SITJointInfo.OrdersFID
left outer join RatePlans on Orders.RPPriKey = 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 [Validation] as TokenValidation on TokenValidation.NameKey = 'WHEATON'
left outer join XmlSystemOptions as AgentIdXmlSystemOption on
    AgentIdXmlSystemOption.[Name] = 'Agent ID' and
    AgentIdXmlSystemOption.XmlSystemFID = @theWheatonXmlSystemId
left outer join XmlSystemOptions as UserIdXmlSystemOption on
    UserIdXmlSystemOption.[Name] = 'User ID' and
    UserIdXmlSystemOption.XmlSystemFID = @theWheatonXmlSystemId
left outer join XmlSystemOptions as WebServiceUrlXmlSystemOption on
    WebServiceUrlXmlSystemOption.[Name] = 'Web Service URL' and
    WebServiceUrlXmlSystemOption.XmlSystemFID = @theWheatonXmlSystemId
left outer join XmlInterfaceAccountMap on XmlInterfaceAccountMap.AccountFid = Orders.AcctPriKey
where Orders.PriKey = @inOrderId

-- Validate SITs
declare @theOriginSitExists bit
declare @theDestinationSitExists bit
declare @theOriginSitInDateMissing bit
declare @theDestinationSitInDateMissing bit

-- Initialize validation bits so that if the SITInformation record does not exist, the validation bit is set to 0.
set @theOriginSitExists = cast( 0 as bit )
set @theDestinationSitExists = cast( 0 as bit )
set @theOriginSitInDateMissing = cast( 0 as bit )
set @theDestinationSitInDateMissing = cast( 0 as bit )

select
    @theOriginSitExists =
        case
            when SITInformation.SITInformationID is not null then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theOriginSitInDateMissing =
        case
            when
                @theOriginSitExists = 1 and
                SITInformation.EstimatedInDate is null
                then cast( 1 as bit )
            else cast( 0 as bit )
        end
from SITInformation
left outer join SITType ON SITInformation.SITTypeFID = SITType.SITTypeID
where
    SITInformation.OrdersFID = @inOrderId and
    SITType.[Type] = 'Origin'

select
    @theDestinationSitExists =
        case
            when SITInformation.SITInformationID is not null then cast( 1 as bit )
            else cast( 0 as bit )
        end,
    @theDestinationSitInDateMissing =
        case
            when
                @theDestinationSitExists = 1 and
                SITInformation.EstimatedInDate is null
                then cast( 1 as bit )
            else cast( 0 as bit )
        end
from SITInformation
left outer join SITType ON SITInformation.SITTypeFID = SITType.SITTypeID
where
    SITInformation.OrdersFID = @inOrderId and
    SITType.[Type] = 'Destination'

-- 1) Get Order information issue flags (includes SIT issue flags)
select
    @theSalespersonMappingIsMissing as SalespersonMappingIsMissing,
    @theSalespersonWrongLength as SalespersonWrongLength,
    @theBookAgentIsMissing as BookAgentIsMissing,
    @theBookAgentTooShort as BookAgentTooShort,
    @theHaulAgentTooShort as HaulAgentTooShort,
    @theOriginAgentTooShort as OriginAgentTooShort,
    @theDestinationAgentTooShort as DestinationAgentTooShort,
    @theShipmentTypeIsMissing as ShipmentTypeIsMissing,
    @theShipmentTypeMappingIsMissing as ShipmentTypeMappingIsMissing,
    @theShipmentTypeInvalid as ShipmentTypeInvalid,
    @theValuationTypeMappingIsMissing as ValuationTypeMappingIsMissing,
    @theValuationTypeInvalid as ValuationTypeInvalid,
    @theMilesTooLarge as MilesTooLarge,
    @thePaymentTypeMappingIsMissing as PaymentTypeMappingIsMissing,
    @theInvalidPaymentType as InvalidPaymentType,
    @theAccountNumberMappingIsMissing as AccountNumberMappingIsMissing,
    @theInvalidAccountNumber as InvalidAccountNumber,
    @theTariffAndSalespersonAreMissing as TariffAndSalespersonAreMissing,
    @theRatePlanCodeMissing as RatePlanCodeIsMissing,
    @theRatePlanInvalid as RatePlanInvalid,
    @theUserIdIsMissing as UserIdIsMissing,
    @theAgentIdIsMissing as AgentIdIsMissing,
    @theWebServiceUrlIsMissing as WebServiceUrlIsMissing,
    @theTokenIsMissing as TokenIsMissing,
    @theOriginSitInDateMissing as OriginSitInDateMissing,
    @theDestinationSitInDateMissing as DestinationSitInDateMissing,
    @theDiscountInvalid as DiscountInvalid,
    @theSitDiscountInvalid as SitDiscountInvalid,
    @theEstimatedWeightIsMissing as EstimatedWeightIsMissing,
    case
        when
            @theLoadDateIsMissing = 1 and
            ( @theOriginSitExists = 0 or @theOriginSitInDateMissing = 1 )
            then cast( 1 as bit )
        else cast( 0 as bit )
    end as MissingStartLoadAndOriginEstimatedInDate

-- 2) Order addresses issue flags
select
    AddressType.TypeName as AddressType,
    case
        when dbo.IsNullOrWhiteSpace( OrderAddress.City ) = 1 then cast( 1 as bit )
        else cast( 0 as bit )
    end as CityMissing,
    case
        when dbo.IsNullOrWhiteSpace( OrderAddress.[State] ) = 1 then cast( 1 as bit )
        else cast( 0 as bit )
    end as StateMissing,
    case
        when
            dbo.IsNotNullOrWhiteSpace( OrderAddress.[State] ) = 1 and
            OrderAddress.[State] not in ( 'AK', 'AL', 'AR', 'AZ', 'CA', 'CO', 'CT', 'DC', 'DE', 'FL', 'GA', 'HI', 'IA', 'ID', 'IL', 'IN', 'KS', 'KY', 'LA', 'MA', 'MD', 'ME', 'MI', 'MN', 'MO', 'MS', 'MT', 'NC', 'ND', 'NE', 'NH', 'NJ', 'NM', 'NV', 'NY', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VA', 'VT', 'WA', 'WI', 'WV', 'WY', 'AB', 'BC', 'MB', 'NB', 'NL', 'NS', 'NT', 'NU', 'ON', 'PE', 'QC', 'SK', 'YT' )
            then cast( 1 as bit )
        else cast( 0 as bit )
    end as StateInvalid,
    case
        when dbo.IsNullOrWhiteSpace( OrderAddress.PostalCode ) = 1 then cast( 1 as bit )
        else cast( 0 as bit )
    end as PostalCodeMissing,
    case
        when
            dbo.IsNotNullOrWhiteSpace( OrderAddress.PostalCode ) = 1 and
            (
                left( OrderAddress.PostalCode, 5 ) not like '[0-9][0-9][0-9][0-9][0-9]' and
                left( dbo.ExtractAlphaNumericCharacters( OrderAddress.PostalCode ), 6 ) not like '[a-zA-Z][0-9][a-zA-Z][0-9][a-zA-Z][0-9]'
            )
            then cast( 1 as bit )
        else cast( 0 as bit )
    end as PostalCodeInvalid,
    case
        when
            dbo.IsNotNullOrWhiteSpace( OrderAddress.PostalCode ) = 1 and
            (
                (
                    left( OrderAddress.PostalCode, 5 ) like '[0-9][0-9][0-9][0-9][0-9]' and
                    dbo.ExtractNumericCharacters( substring( OrderAddress.PostalCode, 6 , len( OrderAddress.PostalCode ) - 5 ) ) not like '[0-9][0-9][0-9][0-9]'
                )
            )
            then cast( 1 as bit )
        else cast( 0 as bit )
    end as PostalExtInvalid
from OrderAddress
left outer join XmlAddressLocationTypeMap on
    XmlAddressLocationTypeMap.AddressLocationTypeFID = OrderAddress.AddressLocationTypeFID and
    XmlAddressLocationTypeMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
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' )

-- 3) Get Order phone numbers issue flags
select
    OrderPhoneType.TypeName as PhoneType,
    dbo.GetFormattedPhoneNumberWithExtension(
        OrderPhoneNumber.CountryCodeStandardFID,
        OrderPhoneNumber.AreaCode,
        OrderPhoneNumber.LocalNumber,
        OrderPhoneNumber.Extension ) as PhoneNumber,
    case
        when OrderPhoneType.OrderPhoneTypeID is null then cast( 1 as bit )
        else cast( 0 as bit )
    end as PhoneTypeMissing,
    case
        when
            OrderPhoneType.OrderPhoneTypeID is not null and
            XmlOrderPhoneTypeMap.ExternalCode is null
            then cast( 1 as bit )
        else cast( 0 as bit )
    end as PhoneTypeMappingMissing,
    case
        when
            XmlOrderPhoneTypeMap.ExternalCode is not null and
            XmlOrderPhoneTypeMap.ExternalCode not in ( 'CE', 'HO', 'OF', 'OT' )
            then cast( 1 as bit )
        else cast( 0 as bit )
    end as PhoneTypeInvalid,
    case
        when dbo.IsNullOrWhiteSpace( OrderPhoneNumber.LocalNumber ) = 1 then cast( 1 as bit )
        else cast( 0 as bit )
    end as LocalNumberMissing,
    case
        when
            len( isnull( OrderPhoneNumber.AreaCode, '' ) + replace( replace( replace( isnull( OrderPhoneNumber.LocalNumber, '' ), '-', '' ), ' ', '' ), 'x', '' ) ) <> 10 and                           
            len( replace( replace( replace( isnull( OrderPhoneNumber.LocalNumber, '' ), '-', '' ), ' ', '' ), 'x', '' ) ) <> 10
            then cast( 1 as bit )
        else cast( 0 as bit )
    end as PhoneNumberWrongLength
from dbo.OrderPhoneNumber
left outer join dbo.XmlOrderPhoneTypeMap on
    XmlOrderPhoneTypeMap.OrderPhoneTypeFID = OrderPhoneNumber.OrderPhoneTypeFID and
    XmlOrderPhoneTypeMap.XmlInterfaceFID = @theWheatonXmlInterfaceId
left outer join dbo.OrderPhoneType on OrderPhoneType.OrderPhoneTypeID = OrderPhoneNumber.OrderPhoneTypeFID
where
    OrderPhoneNumber.OrderFID = @inOrderId and
    OrderPhoneType.IsShipperPhoneType = 1

-- 4) Get Order emails issue flags
select
    OrderEmailType.EmailType as EmailType,
    nullif( ltrim( rtrim( left( OrderEmailAddress.EmailAddress, 60 ) ) ), '' ) as EmailAddress,
    case
        when OrderEmailType.OrderEmailTypeID is null then cast( 1 as bit )
        else cast( 0 as bit )
    end as EmailTypeMissing,
    case
        when
            OrderEmailType.OrderEmailTypeID is not null and
            XmlOrderEmailTypeMap.ExternalCode is null
            then cast( 1 as bit )
        else cast( 0 as bit )
    end as EmailTypeMappingMissing,
    case
        when
            XmlOrderEmailTypeMap.ExternalCode is not null and
            XmlOrderEmailTypeMap.ExternalCode not in ( 'OT', 'PE', 'WO' )
            then cast( 1 as bit )
        else cast( 0 as bit )
    end as EmailTypeInvalid,
    case
        when len( ltrim( rtrim( isnull( OrderEmailAddress.EmailAddress, '' ) ) ) ) > 60 then cast( 1 as bit )
        else cast( 0 as bit )
    end as EmailAddressTooLong
from OrderEmailAddress
left outer join XmlOrderEmailTypeMap on
    XmlOrderEmailTypeMap.OrderEmailTypeFID = OrderEmailAddress.OrderEmailTypeFID and
    XmlOrderEmailTypeMap.XmlInterfaceFID = @theWheatonXmlInterfaceId    
left outer join OrderEmailType on OrderEmailType.OrderEmailTypeID = OrderEmailAddress.OrderEmailTypeFID
where dbo.OrderEmailAddress.OrderFID = @inOrderId
GO
GRANT EXECUTE ON  [dbo].[ValidateWheatonOrder] TO [MssExec]
GO