set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[usp_SRV_GetALLRelativeProject]
@ServiceOrderID VARCHAR(50)
AS
/*
PAGE: 通过服务单ID 获取该服务单关联的单子信息    
Action: 服务单处理页面

CreatedBy: XXX
CreatedDate: 20101021
ModifiedHistory:

Test Scripts:

EXEC usp_SRV_GetALLRelativeProject ‘SRV000000014‘

*/ 
BEGIN
SELECT d.[Type],
d.TypeID,
d.[Desc],
d.VariationOrderID,
CASE 
WHEN d.WorkOrderStatus IS NULL THEN d.SrvOrderStatus
ELSE d.WorkOrderStatus
END AS Status,
CASE 
WHEN d.WorkOrderHName IS NULL THEN d.SrvOrderHName
ELSE d.WorkOrderHName
END AS HName
FROM (
   SELECT r.[Type],
   r.TypeID,
   r.VariationOrderID,
   r.[Desc],
   a.DictName AS SrvOrderStatus,
   hand.HName AS SrvOrderHName,
   CASE wo.Status
   WHEN 1 THEN ‘处理中‘
   WHEN 2 THEN ‘完成‘
   END AS WorkOrderStatus,
   wo.HandlerName AS WorkOrderHName
   FROM dbo.tbl_VariationOrderRelativeProject r
   INNER JOIN tbl_ServiceOrder s
   ON r.VariationOrderID = @ServiceOrderID
   AND s.ServiceOrderID = @ServiceOrderID
   LEFT JOIN (
    SELECT * FROM dbo.tbl_Dictionary
    WHERE DictType = ‘ServiceOrderStatus‘
   ) a
   ON s.StatusCode = a.DictCode
   LEFT JOIN tbl_ServiceHandlerInfo hand
   ON r.VariationOrderID = hand.ServiceOrderID
   LEFT JOIN dbo.tbl_ServiceWorkOrder wo
   ON r.TypeID = wo.ServiceWorkOrderID
) d

END