/**
*    
*    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 Material on EqType.TypeNumber = LocalServiceEquipmentRequest.EquipmentTypeFID
            where LocalServiceEquipmentRequest.LocalServiceFID = 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