/**
* This view is for ProServ, used in custom reports. Don't rename the view without confirmation from ProServ.
*/

create view [dbo].[vLocalDispatchGrid]
as

select ServiceDate = convert(varchar(10), ls.ServiceDate, 101),
    ShipperName = rtrim(o.LastName) + isnull(', ' + rtrim(o.FirstName), ''),
    OrderNumber = o.OrderNo,
    Service = st.Service,
    Crew = ls.Crew,
    Hours = (ls.Crew * ls.HrsPerMan),
    JobStart = case
        when datepart(hh, ls.JobStart) > 12
        then convert(varchar(2), datepart(hh, ls.JobStart) - 12) + ':'
        else convert(varchar(2), datepart(hh, ls.JobStart)) + ':'
    end + case when datepart(mi, ls.JobStart) = 0 then '00' else convert(varchar(2), datepart(mi, ls.JobStart)) end +
    case when datepart(hh, ls.JobStart) > 12 then ' PM' else ' AM' end,
    JobEnd = case
        when datepart(hh, ls.JobEnd) > 12
        then convert(varchar(2), datepart(hh, ls.JobEnd) - 12) + ':'
        else convert(varchar(2), datepart(hh, ls.JobEnd)) + ':'
    end + case when datepart(mi, ls.JobEnd) = 0 then '00' else convert(varchar(2), datepart(mi, ls.JobEnd)) end +
    case when datepart(hh, ls.JobEnd) > 12 then ' PM' else ' AM' end,
    LeadCrewName = rtrim(crew.LASTNAME) + isnull(', ' + rtrim(crew.FIRSTNAME), ''),
    Equipment = (select    max(e.EqNo)
     from    Serveq se
     join    Equip e
        on    se.EqPriKey = e.PriKey
     where     ls.PriKey = se.LSPRIKEY),
    Note = (select    count(1)
     from    OrderNoteHeader onh
     join    NoteType nt
        on    onh.NoteTypeFID = nt.NoteTypeID
     where    o.PriKey = onh.OrderFID and
        nt.NoteType like '%local Disp%'),
    OriginAddress = oao.Address1,
    OriginCity = oao.City,
    OriginState = oao.State,
    OriginZip = oao.PostalCode,
    DestinationAddress = oad.Address1,
    DestinationCity = oad.City,
    DestinationState = oad.State,
    DestinationZip = oad.PostalCode,
    OrderWeight = o.Weight,
    TotalContainers = (select    sum(om.EstQuantity)
     from    OrdMaterial om
     join    MaterialItemCodeMap micm
        on    om.MaterialItemCodeMapFID = micm.MaterialItemCodeMapID and
            micm.MaterialCPUTypeFID = (select mct.MaterialCPUTypeID from MaterialCPUType mct where mct.Type = 'Container')
     where    o.PriKey = om.OrdPriKey),
    TotalCrates = (select sum(oc.Quantity)
     from    OrdCrates oc
     where    o.PriKey = oc.OrdPriKey),
    OriginPhone = '(' + opoh.AreaCode + ') ' + opoh.LocalNumber,
    OriginWorkPhone = '(' + opow.AreaCode + ') ' + opow.LocalNumber,
    DestinationPhone = '(' + opdh.AreaCode + ') ' + opdh.LocalNumber,
    DestinationWorkPhone = '(' + opdw.AreaCode + ') ' + opdw.LocalNumber,
    DispatchStatus = ds.Status,
    LocServBranchID = b.BranchID,
    OrderId = o.PriKey
from    Orders o
join    LocServ ls on o.PriKey = ls.OrdPriKey
left join OrderAddress oao on o.PriKey = oao.OrderFID and oao.AddressTypeFID = (select at.AddressTypeID from AddressType at where at.TypeName = 'Origin')
left join OrderAddress oad on o.PriKey = oad.OrderFID and oad.AddressTypeFID = (select at.AddressTypeID from AddressType at where at.TypeName = 'Destination')
left join OrderPhoneNumber opoh on o.PriKey = opoh.OrderFID and opoh.OrderPhoneTypeFID = (select opt.OrderPhoneTypeID from OrderPhoneType opt where opt.TypeName = 'Origin Home')
left join OrderPhoneNumber opow on o.PriKey = opow.OrderFID and opow.OrderPhoneTypeFID = (select opt.OrderPhoneTypeID from OrderPhoneType opt where opt.TypeName = 'Origin Work')
left join OrderPhoneNumber opdh on o.PriKey = opdh.OrderFID and opdh.OrderPhoneTypeFID = (select opt.OrderPhoneTypeID from OrderPhoneType opt where opt.TypeName = 'Destination Home')
left join OrderPhoneNumber opdw on o.PriKey = opdw.OrderFID and opdw.OrderPhoneTypeFID = (select opt.OrderPhoneTypeID from OrderPhoneType opt where opt.TypeName = 'Destination Work')
left join Branch b on b.BranchPriKey = ls.BranchPriKey
left join ServType st on st.ServiceID = ls.ServiceID
left join DispatchStatus ds on ls.DispatchStatusFID = ds.DispatchStatusID
left join LSCrew lsc on ls.PriKey = lsc.LSPriKey and lsc.LeadMan = 1
left join Sysuser crew on lsc.CrewID = crew.SysUserID
GO
GRANT SELECT ON  [dbo].[vLocalDispatchGrid] TO [MssExec]
GRANT INSERT ON  [dbo].[vLocalDispatchGrid] TO [MssExec]
GRANT DELETE ON  [dbo].[vLocalDispatchGrid] TO [MssExec]
GRANT UPDATE ON  [dbo].[vLocalDispatchGrid] TO [MssExec]
GO