/**
*    
*    Imports Office & Industrial info, Contacts and Locations from XML.
* NOTE: This process is INSERT-ONLY. Office & Industrial data is only pushed to MoversSuite once.
*
*    Error Codes:
*    
*    @param @inXmlText            The MSS import format XML containing the external interfaces and Order Id.
*    @param @outErrorCode        The error code which indicates any errors that happened in the session call.
*    @param @outStatusCode        The status code which indicates what happened in the session call.
*/

create procedure [dbo].[XmlImportProcessOfficeIndustrial]
    @inXmlText Xml,
    @inOrderID int,
    @outErrorCode int output,
    @outStatusCode int output
as
set nocount on

-- define our constant error codes that will be used
declare @ERRORCODE_ORDER_NOT_FOUND            int
set        @ERRORCODE_ORDER_NOT_FOUND            = 2040

-- Define status codes
declare @STATUSCODE_OFFICE_INDUSTRIAL_DATA_INSERTED int
set @STATUSCODE_OFFICE_INDUSTRIAL_DATA_INSERTED = 0
declare @STATUSCODE_NO_OFFICE_INDUSTRIAL_DATA int
set @STATUSCODE_NO_OFFICE_INDUSTRIAL_DATA = 1
declare @STATUSCODE_ERROR    int
set @STATUSCODE_ERROR     = 2

-- Initialiaze outputs
set @outErrorCode    = 0
set @outStatusCode    = @STATUSCODE_NO_OFFICE_INDUSTRIAL_DATA

-- Declare and initialize local variables used in this stored proc
declare @theErrorData    varchar(max)
set @theErrorData = ''
declare @theAppVendorName varchar(50)
declare @theMainAddressTypeId int
select @theMainAddressTypeId = AddressTypeID from AddressType where TypeName = 'Main'
declare @theDefaultCountryCode int
select @theDefaultCountryCode = PhoneCountryCode from CountryCodeStandard where CountryCodeStandardID = dbo.udfGetDefaultCountryCodeStandardID()
declare @theDefaultCountryCodeId int
select @theDefaultCountryCodeId = dbo.udfGetDefaultCountryCodeStandardID()

-- The Xml must contain an OfficeIndustrial element with a Order parent element or there is nothing to do.
declare @theOIElementExists bit
;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
select @theOIElementExists = 1 from @inXmlText.nodes( 'MSS:Order/MSS:OfficeIndustrial' ) as OrderTable(OrderColumn)
if ( 1 = @theOIElementExists )
begin

    -- Make sure the specified order exists.
    if not exists( select 1 from Orders where PriKey = @inOrderID )
    begin
        set @outStatusCode = @STATUSCODE_ERROR
        set @outErrorCode = @ERRORCODE_ORDER_NOT_FOUND
    end
    else
    begin

        -- Only lookup @theAppVendorName once, if we get here
        if( @theAppVendorName is null )
        begin
            set @theAppVendorName = dbo.GetExtAppVendorFromMssXmlText( @inXmlText )
        end

        declare @theXmlInterfaceId int
        select
            @theXmlInterfaceId = XmlInterface.XmlInterfaceID
        from XmlInterface
        where XmlInterface.VendorName = @theAppVendorName

        -- Get the Sysuser ID of the project manager
        declare @theProjectManagerId int
        ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
        select @theProjectManagerId = SysUserFID
        from @inXmlText.nodes( 'MSS:Order/MSS:OfficeIndustrial' ) as XmlTable(OandI)
        inner join XmlUserMap on XmlUserMap.UserID = OandI.value( 'MSS:ProjectManager[1]', 'varchar(256)' )
        inner join XmlInterface on XmlInterface.XmlInterfaceID = XmlUserMap.XmlInterfaceFID
        inner join SysUser on SysUser.SysUserID = XmlUserMap.SysUserFID
        inner join [Status] on SysUser.[Status] = [Status].PriKey
        where
            XmlInterface.XmlInterfaceID = @theXmlInterfaceId and
            [Status].[Status] = 'ACTIVE'

        -- Insert the general Office & Industrial data
        ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
        insert into OfficeIndustrialOrder
        (
            OrderFID,
            ProjectManager,
            JobStartDate,
            JobEndDate,
            HeadCount,
            ClientNote,
            OperationalNote
        )
        select
            @inOrderID,
            @theProjectManagerId,
            OandI.value( 'MSS:JobStartDate[1]', 'datetime' ),
            OandI.value( 'MSS:JobEndDate[1]', 'datetime' ),
            OandI.value( 'MSS:HeadCount[1]', 'int' ),
            OandI.value( 'MSS:ClientNote[1]', 'varchar(max)' ),
            OandI.value( 'MSS:OperationalNote[1]', 'varchar(max)' )
        from @inXmlText.nodes( 'MSS:Order/MSS:OfficeIndustrial' ) as XmlTable(OandI)
        where not exists( select top 1 1 from OfficeIndustrialOrder where OrderFID = @inOrderID )

        if ( @@ROWCOUNT > 0 )
        begin
            set @outStatusCode = @STATUSCODE_OFFICE_INDUSTRIAL_DATA_INSERTED

            -- Mark this order so that it will default to opening via the O&I module.
            update Orders set
                OpenOrderInModule = 'Office & Industrial'
            where PriKey = @inOrderID

            -- If there are any Order Contacts in the XML, import those.
            declare @theOrderContactsExist bit
            ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
            select @theOrderContactsExist = 1 from @inXmlText.nodes( '/MSS:Order/MSS:OfficeIndustrial/MSS:OrderContacts/MSS:OrderContact' ) as ContactTable(ContactColumn)
            if ( 1 = @theOrderContactsExist )
            begin
                declare @theContactIds table( OrderContactId int, ExternalID varchar(32) )
                ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
                merge OrderContacts
                using
                (
                    select
                        Contact.value( 'MSS:ExternalID[1]', 'varchar(32)' ) as ExternalID,
                        Contact.value( 'MSS:ContactName[1]', 'varchar(30)' ) as Contact,
                        Contact.value( 'MSS:Title[1]', 'varchar(50)' ) as Title,
                        Contact.value( 'MSS:Email[1]', 'varchar(120)' ) as Email,
                        Contact.value( 'MSS:CompanyName[1]', 'varchar(100)' ) as [Name]
                    from @inXmlText.nodes( '/MSS:Order/MSS:OfficeIndustrial/MSS:OrderContacts/MSS:OrderContact' ) as XmlTable(Contact)
                ) as [Source]
                on 1 = 0
                when not matched then
                insert
                (
                    OrderFID,
                    Contact,
                    Title,
                    Email,
                    [Name],
                    ImportedFlag
                )
                values
                (
                    @inOrderID,
                    [Source].Contact,
                    [Source].Title,
                    [Source].Email,
                    [Source].[Name],
                    1
                )
                output
                    inserted.OrderContactID,
                    [Source].ExternalID    
                into @theContactIds;

                -- Insert the address for the contact.
                ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
                insert into OrderContactAddress
                (
                    OrderContactFID,
                    AddressTypeFID,
                    Address1,
                    Address2,
                    Address3,
                    City,
                    [State],
                    PostalCode,
                    CountryCodeStandardFID
                )
                select
                    OrderContactFID = ContactIds.OrderContactId,
                    AddressTypeFID = @theMainAddressTypeId,
                    Address1 = ContactAddress.value( 'MSS:Address1[1]', 'Address' ),
                    Address2 = ContactAddress.value( 'MSS:Address2[1]', 'Address' ),
                    Address3 = ContactAddress.value( 'MSS:Address3[1]', 'Address' ),
                    City = ContactAddress.value( 'MSS:City[1]', 'AddressCity' ),
                    [State] = ContactAddress.value( 'MSS:State[1]', 'AddressState' ),
                    Postal = ContactAddress.value( 'MSS:Postal[1]', 'AddressPostalCode' ),
                    CountryCode = CountryCodeStandard.CountryCodeStandardID
                from @inXmlText.nodes( '/MSS:Order/MSS:OfficeIndustrial/MSS:OrderContacts/MSS:OrderContact/MSS:OrderContactAddress' ) as XmlTable(ContactAddress)
                inner join @theContactIds as ContactIds on ContactIds.ExternalID = ContactAddress.value( '../MSS:ExternalID[1]', 'varchar(32)' )
                left outer join CountryCodeStandard on CountryCodeStandard.VanlineCountryCode = ContactAddress.value( 'MSS:CountryCode[1]', 'varchar(3)' )
            
                -- Insert Order Contact Phone Numbers
                ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
                insert into OrderContactPhoneNumber
                (
                    OrderContactFID,
                    CountryCodeStandardFID,
                    AreaCode,
                    LocalNumber,
                    Extension,
                    OrderContactPhoneTypeFID
                )
                select
                    ContactIds.OrderContactId,
                    case
                        when CountryCodeStandard.CountryCodeStandardID is not null then CountryCodeStandard.CountryCodeStandardID
                        when ContactPhone.value( 'MSS:PhoneCountryCode[1]', 'int' ) = @theDefaultCountryCode then @theDefaultCountryCodeId
                        else ( select top 1 CountryCodeStandardID from CountryCodeStandard where CountryCodeStandard.PhoneCountryCode = ContactPhone.value( 'MSS:PhoneCountryCode[1]', 'int' ) )
                    end,
                    ContactPhone.value( 'MSS:AreaCode[1]', 'PhoneAreaCode' ),
                    ContactPhone.value( 'MSS:LocalNumber[1]', 'PhoneLocalNumber' ),
                    ContactPhone.value( 'MSS:Extension[1]', 'PhoneExtension' ),                
                    OrderContactPhoneType.OrderContactPhoneTypeID
                from @inXmlText.nodes( '/MSS:Order/MSS:OfficeIndustrial/MSS:OrderContacts/MSS:OrderContact/MSS:OrderContactPhoneNumbers/MSS:OrderContactPhoneNumber' ) as XmlTable(ContactPhone)
                inner join @theContactIds as ContactIds on ContactIds.ExternalID = ContactPhone.value( '../../MSS:ExternalID[1]', 'varchar(32)' )
                inner join OrderContactPhoneType on OrderContactPhoneType.TypeName = ContactPhone.value( 'MSS:Type[1]', 'varchar(50)' )
                left outer join CountryCodeStandard on CountryCodeStandard.VanlineCountryCode = ContactPhone.value( 'MSS:CountryCode[1]', 'varchar(3)' )
        
            -- end if ( 1 = @theOrderContactsExist )
            end

            -- If there are any Order Locations in the XML, import those.
            declare @theOrderLocationsExist bit
            ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
            select @theOrderLocationsExist = 1 from @inXmlText.nodes( '/MSS:Order/MSS:OfficeIndustrial/MSS:OrderLocations/MSS:OrderLocation' ) as XmlTable(LocationColumn)
            if ( 1 = @theOrderLocationsExist )
            begin
            
                -- Insert Order Locations.
                -- This is done using a "merge" statement so that we can output the External
                declare @theLocationIds table( OrderLocationId int, ExternalID varchar(32) )
                ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
                merge OrderLocations
                using
                (
                    select
                        @inOrderID as OrderId,
                        [Location].value( 'MSS:ExternalID[1]', 'varchar(32)' ) as ExternalID,
                        [Location].value( 'MSS:Name[1]', 'varchar(100)' ) as [Location],
                        ContactIds.OrderContactId,
                        1 as Imported,
                        null as BuildingFid,
                        [Location].value( 'MSS:CrossSt[1]', 'varchar(1024)' ) as CrossStreet,
                        [Location].value( 'MSS:ServiceEntrance[1]', 'varchar(1024)' ) as ServiceEntrance,
                        [Location].value( 'MSS:SiteNote[1]', 'varchar(1024)' ) as SiteNote,
                        OrderLocationAddressType.OrderLocationAddressTypeID
                    from @inXmlText.nodes( '/MSS:Order/MSS:OfficeIndustrial/MSS:OrderLocations/MSS:OrderLocation' ) as XmlTable([Location])
                    left outer join @theContactIds as ContactIds on ContactIds.ExternalID = [Location].value( 'MSS:ContactExternalID[1]', 'varchar(32)' )
                    left outer join OrderLocationAddressType on OrderLocationAddressType.TypeName = [Location].value( 'MSS:OrderLocationAddressType[1]', 'varchar(1024)' )
                ) as [Source]
                on 1 = 0
                when not matched then
                insert
                (
                    OrderFID,
                    [Name],
                    OrderContactFID,
                    ImportedFlag,
                    BuildingFID,
                    CrossStreet,
                    ServiceEntrance,
                    SiteNote,
                    OrderLocationAddressTypeFID
                )
                values
                (
                    [Source].OrderId,
                    [Source].[Location],
                    [Source].OrderContactId,
                    [Source].Imported,
                    [Source].BuildingFid,
                    [Source].CrossStreet,
                    [Source].ServiceEntrance,
                    [Source].SiteNote,
                    [Source].OrderLocationAddressTypeID
                )
                -- Output the External ID and primary key of each inserted Order Location, so that we can link them to Order Location Addresses below.
                output
                    inserted.OrderLocationID,
                    [Source].ExternalID    
                into @theLocationIds;

                -- Insert Order Location Addresses
                ;with xmlnamespaces('MssXsd-InternalImport.xsd' as MSS)
                insert into OrderLocationAddress
                (
                    OrderLocationFID,
                    AddressTypeFID,
                    AddressLocationTypeFID,
                    Address1,
                    Address2,
                    Address3,
                    City,
                    State,
                    PostalCode,
                    CountryCodeStandardFID
                )
                select
                    LocationIds.OrderLocationId,
                    @theMainAddressTypeId,
                    XmlAddressLocationTypeMap.AddressLocationTypeFID,
                    Address1 = [LocationAddress].value( 'MSS:Address1[1]', 'Address' ),
                    Address2 = [LocationAddress].value( 'MSS:Address2[1]', 'Address' ),
                    Address3 = [LocationAddress].value( 'MSS:Address3[1]', 'Address' ),
                    City = [LocationAddress].value( 'MSS:City[1]', 'AddressCity' ),
                    [State] = [LocationAddress].value( 'MSS:State[1]', 'AddressState' ),
                    Postal = [LocationAddress].value( 'MSS:Postal[1]', 'AddressPostalCode' ),
                    CountryCode = CountryCodeStandard.CountryCodeStandardID
                from @inXmlText.nodes( '/MSS:Order/MSS:OfficeIndustrial/MSS:OrderLocations/MSS:OrderLocation/MSS:OrderLocationAddress' ) as XmlTable([LocationAddress])
                inner join @theLocationIds as LocationIds on LocationIds.ExternalID = [LocationAddress].value( '../MSS:ExternalID[1]', 'varchar(32)' )
                left outer join CountryCodeStandard on CountryCodeStandard.VanlineCountryCode = [LocationAddress].value( 'MSS:CountryCode[1]', 'varchar(3)' )
                left outer join XmlAddressLocationTypeMap on
                    XmlAddressLocationTypeMap.ExternalCode = [LocationAddress].value( 'MSS:AddressLocationType[1]', 'varchar(32)' ) and
                    XmlAddressLocationTypeMap.XmlInterfaceFID = @theXmlInterfaceId
        
            -- end if ( 1 = @theOrderLocationsExist )
            end

        -- end "else"
        end
    end

-- end if exists( select 1 from openxml( @theXmlDoc, @theElement ) )
end

if( @outErrorCode > 0 )
begin
    -- Handle errors
    declare @theXmlText nvarchar(max) = cast( @inXmlText as nvarchar(max) )
    exec XmlImportHandleError
        @inErrorCode = @outErrorCode,
        @inOrderID = @inOrderID,
        @inXmlData = @inXmlText,
        @inClearErrors = 0
end
GO
GRANT EXECUTE ON  [dbo].[XmlImportProcessOfficeIndustrial] TO [MssExec]
GO