/**
*    
*    This procedure is called to generate the xml segment for Local Services
*    information for the given order.
*    
*    Possible Error Codes:
*    @ERROR_CODE_INVALID_ORDER
*    
*    @param @inOrderID The primary key of a MoversSuite Orders record.
*    @param @inXmlInterfaceID The primary key of a XmlInterface 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].[XmlExportLocalServicesInformation]
    @inOrderID int,
    @inXmlInterfaceID 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;
-- 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
    
    -- Get the Local Services order information
    -- This flag is passed to dbo.GetSysuserName to tell it to format the name like "FirstName LastName".
    declare @theFirstNameFlag bit
    set @theFirstNameFlag = 1

    declare @theMainAddressTypeId int
    select @theMainAddressTypeId = AddressTypeID from AddressType where TypeName = 'Main'

    select
        dbo.ConvertToStandardDate( LocalService.ServiceDate ) as ServiceDate,
        dbo.ConvertToStandardTime( LocalService.RequestedStartTime, 0 ) as RequestedStartTime,
        dbo.ConvertToStandardTime( LocalService.RequestedEndTime, 0 ) as RequestedEndTime,
        LocalService.WkTicketNo as WorkTicketNumber,
        ServType.[Service] as ServiceType,
        dbo.ConvertToStandardTime( LocalService.JobStart, 0 ) as JobStart,
        dbo.ConvertToStandardTime( LocalService.JobEnd, 0 ) as JobEnd,
        LocalService.TotCrewHrs as CrewHours,
        LocalService.HrsPerMan as HoursPerMan,
        LSStatus.[Status] as [Status],
        LocalService.HrsDrv1Way as HoursDriveOneWay,
        LocalService.Quantity as Quantity,
        LocalService.Crew as Crew,
        LocalService.OriginDestination as OriginDestination,
        dbo.ConvertToStandardDate( LocalService.CreatedOn ) as CreatedDate,
        dbo.GetSysuserName( LocalService.CreatedBy, @theFirstNameFlag ) as CreatedBy,
        dbo.ConvertToStandardDate( LastEditedOn ) as LastEditedDate,
        dbo.GetSysuserName( LocalService.LastEditedBy, @theFirstNameFlag ) as LastEditedBy,
        dbo.ConvertToStandardDate( SubmittedOn ) as SubmittedDate,
        dbo.GetSysuserName( LocalService.SubmittedBy, @theFirstNameFlag ) as SubmittedBy,
        dbo.ConvertToStandardDate( ClearedOn ) as ClearedDate,
        dbo.GetSysuserName( LocalService.ClearedBy, @theFirstNameFlag ) as ClearedBy,
        Branch.[Name] as Branch,
        DispatchStatus.[Status] as DispatchStatus,
        ContainerizationStatus.[Description] as ContainerizationStatus,
        dbo.GetSysuserName( LocalService.VoidedBy, @theFirstNameFlag ) as VoidedBy,
        dbo.ConvertToStandardDate( VoidedOn ) as VoidedDate,
        LocalService.CartonCountSource as CartonCountSource,
        dbo.GetSysuserName( LocalService.CancelledBy, @theFirstNameFlag ) as CancelledBy,
        dbo.ConvertToStandardDate( CancelledOn ) as CancelledDate,
        OperationalPlan.PlanName as OperationalPlan,
        dbo.ConvertToStandardTime( LocalService.CrewReportTime, 0 ) as CrewReportTime,
        CrewReportLocation.ReportLocation as ReportLocation,
        case when LocalService.IsPlaceHolder = 1 then 'Yes' else 'No' end as IsPlaceHolder,
        LSInstructions.Instruction as ServiceInstructions,
        OriginLocation.[Name] as [OriginLocation/Name],
        OriginLocationContact.Contact as [OriginLocation/Contact],
        OriginLocationAddress.Address1 as [OriginLocation/Address1],
        OriginLocationAddress.Address2 as [OriginLocation/Address2],
        OriginLocationAddress.Address3 as [OriginLocation/Address3],
        OriginLocationAddress.City as [OriginLocation/City],
        OriginLocationAddress.[State] as [OriginLocation/State],
        OriginLocationAddress.PostalCode as [OriginLocation/PostalCode],
        OriginCountryCode.CountryName as [OriginLocation/Country],
        DestinationLocation.[Name] as [DestinationLocation/Name],
        DestinationLocationContact.Contact as [DestinationLocation/Contact],
        DestinationLocationAddress.Address1 as [DestinationLocation/Address1],
        DestinationLocationAddress.Address2 as [DestinationLocation/Address2],
        DestinationLocationAddress.Address3 as [DestinationLocation/Address3],
        DestinationLocationAddress.City as [DestinationLocation/City],
        DestinationLocationAddress.[State] as [DestinationLocation/State],
        DestinationLocationAddress.PostalCode as [DestinationLocation/PostalCode],
        DestinationCountryCode.CountryName as [DestinationLocation/Country],
        (
            select
                LaborType.LaborType,
                LocalServiceLaborRequest.Quantity
            from LocalServiceLaborRequest
            inner join LaborType on LaborType.PriKey = LocalServiceLaborRequest.LaborTypeFID
            where LocalServiceLaborRequest.LocalServiceFID = LocalService.PriKey
            for xml path ( 'LaborRequest' ), type
        ) as [LaborRequests],
        (
            select
                EqType.[Type] as EquipmentType,
                LocalServiceEquipmentRequest.Quantity
            from LocalServiceEquipmentRequest
            inner join EqType on EqType.TypeNumber = LocalServiceEquipmentRequest.EquipmentTypeFID
            where LocalServiceEquipmentRequest.LocalServiceFID = LocalService.PriKey
            for xml path ( 'EquipmentRequest' ), type
        ) as [EquipmentRequests],
        (
            select
                MaterialType.[Description] as Material,
                MaterialCPUType.[Type] as CPUType,
                LSMaterial.EstQuantity as EstimatedQuantity
            from LSMaterial
            inner join MaterialItemCodeMap on MaterialItemCodeMap.MaterialItemCodeMapID = LSMaterial.MaterialItemCodeMapFID
            inner join MaterialCPUType on MaterialCPUType.MaterialCPUTypeID = MaterialItemCodeMap.MaterialCPUTypeFID
            inner join MaterialType on MaterialType.MaterialTypeID = MaterialItemCodeMap.MaterialTypeFID
            where LSMaterial.LocServPriKey = LocalService.PriKey
            for xml path ( 'Material' ), type
        ) as [Materials]
    from LocServ as LocalService
    inner join ServType on ServType.ServiceID = LocalService.ServiceID
    inner join Branch on Branch.BranchPriKey = LocalService.BranchPriKey
    left outer join OrderLocations as OriginLocation on OriginLocation.OrderLocationID = LocalService.OriginOrderLocationFID
    left outer join OrderLocationAddress as OriginLocationAddress on ( OriginLocation.OrderLocationID = OriginLocationAddress.OrderLocationFID and OriginLocationAddress.AddressTypeFID = @theMainAddressTypeId )
    left outer join CountryCodeStandard as OriginCountryCode on OriginLocationAddress.CountryCodeStandardFID = OriginCountryCode.CountryCodeStandardID
    left outer join OrderContacts as OriginLocationContact on OriginLocation.OrderContactFID = OriginLocationContact.OrderContactID
    left outer join OrderLocations as DestinationLocation on DestinationLocation.OrderLocationID = LocalService.DestinationOrderLocationFID
    left outer join OrderLocationAddress as DestinationLocationAddress on ( DestinationLocation.OrderLocationID = DestinationLocationAddress.OrderLocationFID and DestinationLocationAddress.AddressTypeFID = @theMainAddressTypeId )
    left outer join CountryCodeStandard as DestinationCountryCode on DestinationLocationAddress.CountryCodeStandardFID = DestinationCountryCode.CountryCodeStandardID
    left outer join OrderContacts as DestinationLocationContact on DestinationLocation.OrderContactFID = DestinationLocationContact.OrderContactID
    left outer join LSStatus on LSStatus.PriKey = LocalService.ServStatus
    left outer join DispatchStatus on DispatchStatus.DispatchStatusID = LocalService.DispatchStatusFID
    left outer join ContainerizationStatus on ContainerizationStatus.ContainerizationStatusID = LocalService.ContainerizationStatusFID
    left outer join OperationalPlan on OperationalPlan.OperationalPlanID = LocalService.OperationalPlanFID
    left outer join CrewReportLocation on CrewReportLocation.CrewReportLocationID = LocalService.CrewReportLocationFID
    left outer join LSInstructions on LSInstructions.LSPriKey = LocalService.PriKey
    where LocalService.OrdPriKey = @inOrderId
    for xml path ('LocalService'), type

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