SET QUOTED_IDENTIFIER OFF
GO
/**
*    
*    Description: Gathers all of the basic information about all services
*/

CREATE view [dbo].[LocalServices]
(
    LocalServicesLocServFID,

    --General Service Information
    LocalServicesOrdersFID,
    Service,
    ServiceStatus,
    ServiceDate,
    WorkTicket,
    JobStartTime,
    JobEndTime,
    TotalCrewHours,
    HoursPerMan,
    HoursDrive1Way,
    Quantity,
    EstimatedCrew,
    TotalCrew,
    TotalEquip,
    OriginDestination,

    --Branch Information
    LocServBranchName,
    LocServBranchID,
    LocServBranchAddress,
    LocServBranchCity,
    LocServBranchState,
    LocServBranchZip,
    LocServBranchPhone,
    LocServWorkTicketTitle,

    --Dates and Names
    CreatedOn,
    CreatedByFirstName,
    CreatedByLastName,
    LastEditedOn,
    LastEditedByFirstName,
    LastEditedByLastName,
    SubmittedOn,
    SubmittedByFirstName,
    SubmittedByLastName,
    ClearedOn,
    ClearedByFirstName,
    ClearedByLastName
)
as

select distinct
    LocalServicesLocServFID    = mainLocServ.PriKey,

    --General Service Information
    LocalServicesOrdersFID = mainLocServ.OrdPriKey,
    Service = ServType.Service,
    ServiceStatus = LSStatus.Status,
    ServiceDate = mainLocServ.ServiceDate,
    WorkTicket = mainLocServ.WkTicketNo,
    JobStartTime = mainLocServ.JobStart,
    JobEndTime = mainLocServ.JobEnd,
    TotalCrewHours = mainLocServ.TotCrewHrs,
    HoursPerMan = mainLocServ.HrsPerMan,
    HoursDrive1Way = mainLocServ.HrsDrv1Way,
    Quantity = mainLocServ.Quantity,
    EstimatedCrew = mainLocServ.Crew,
    TotalCrew =
    (
        select count( LSCrew.PriKey )
        from dbo.LocServ subLocServ1
        inner join dbo.LSCrew on LSCrew.LSPriKey = subLocServ1.PriKey
        where subLocServ1.PriKey = mainLocServ.PriKey
    ),
    TotalEquip =
    (
        select count( Serveq.PriKey )
        from LocServ as subLocServ2
        inner join dbo.Serveq on Serveq.LSPriKey = subLocServ2.PriKey
        where subLocServ2.PriKey = mainLocServ.PriKey
    ),
    OriginDestination = mainLocServ.OriginDestination,

    --Branch Information
    LocServBranchName = Branch.Name,
    LocServBranchID = Branch.BranchID,
    LocServBranchAddress = BranchAddress.Address,
    LocServBranchCity = BranchAddress.City,
    LocServBranchState = BranchAddress.State,
    LocServBranchZip = BranchAddress.Zip,
    LocServBranchPhone = BranchAddress.Phone,
    LocServWorkTicketTitle = BranchAddress.Title,

    --Dates and Names
    CreatedOn = mainLocServ.CreatedOn,
    CreatedByFirstName = CreatedBy.FirstName,
    CreatedByLastName = CreatedBy.LastName,
    LastEditedOn = mainLocServ.LastEditedOn,
    LastEditedByFirstName = LastEditedBy.FirstName,
    LastEditedByLastName = LastEditedBy.LastName,
    SubmittedOn = mainLocServ.SubmittedOn,
    SubmittedByFirstName = SubmittedBy.FirstName,
    SubmittedByLastName = SubmittedBy.LastName,
    ClearedOn = mainLocServ.ClearedOn,
    ClearedByFirstName = ClearedBy.FirstName,
    ClearedByLastName = ClearedBy.LastName
  
from LocServ as mainLocServ
left outer join Branch on Branch.BranchPriKey = mainLocServ.BranchPriKey
left outer join BranchAddress on
(
    BranchAddress.BranchFID = Branch.BranchPriKey and
    BranchAddress.BranchAddressTypeFID = ( select BranchAddressTypeID from dbo.BranchAddressType where BranchAddressType = 'Default' )
)
left outer join ServType on ServType.ServiceID = mainLocServ.ServiceID
left outer join LSStatus on LSStatus.PriKey = mainLocServ.ServStatus
left outer join SysUser as CreatedBy on CreatedBy.SysUserID = mainLocServ.CreatedBy
left outer join SysUser as LastEditedBy on LastEditedBy.SysUserID = mainLocServ.LastEditedBy
left outer join SysUser as SubmittedBy on SubmittedBy.SysUserID = mainLocServ.SubmittedBy
left outer join SysUser as ClearedBy on ClearedBy.SysUserID = mainLocServ.ClearedBy
GO
GRANT SELECT ON  [dbo].[LocalServices] TO [MssExec]
GRANT INSERT ON  [dbo].[LocalServices] TO [MssExec]
GRANT DELETE ON  [dbo].[LocalServices] TO [MssExec]
GRANT UPDATE ON  [dbo].[LocalServices] TO [MssExec]
GO