51 lines
1.9 KiB
Transact-SQL
51 lines
1.9 KiB
Transact-SQL
|
|
ALTER PROCEDURE [dbo].[sp_rpt_PointTrustCheckTaskList]
|
|
|
|
@projectId nvarchar(50)=NULL,
|
|
@unitId nvarchar(50)=NULL,
|
|
@workAreaId nvarchar(50)=NULL
|
|
|
|
AS
|
|
/*******现场点口/委托检测任务单*********/
|
|
SELECT jointInfo.JOT_ID,
|
|
jointInfo.ProjectId,
|
|
jointInfo.ISO_ID,
|
|
isoInfo.UnitId,
|
|
unit.UnitName,
|
|
isoInfo.WorkAreaId,
|
|
workArea.WorkAreaCode,
|
|
workArea.WorkAreaName,
|
|
isoInfo.ISO_IsoNo,
|
|
jointInfo.JOT_JointNo,
|
|
jointInfo.JOT_JointDesc,
|
|
(CASE WHEN cellWelder.WED_Code IS NOT NULL
|
|
THEN (CASE WHEN floorWelder.WED_Code IS NOT NULL AND cellWelder.WED_Code!=floorWelder.WED_Code
|
|
THEN cellWelder.WED_Code+'/'+floorWelder.WED_Code
|
|
ELSE cellWelder.WED_Code END)
|
|
ELSE floorWelder.WED_Code END) AS WelderCode,
|
|
trustItem.CH_TrustCode,
|
|
trustItem.CH_TrustDate,
|
|
trustItem.DetectionTypeCode,
|
|
NULL AS ConUnitNoPoint, --施工单位未点口
|
|
NULL AS Scaffold,--脚手架
|
|
NULL AS UnweldedOnTrust,--未焊接已委托
|
|
jointInfo.JOT_Remark,
|
|
NULL AS PointCount--现场实际点口数（道）
|
|
FROM PW_JointInfo AS jointInfo
|
|
LEFT JOIN PW_IsoInfo AS isoInfo ON isoInfo.ISO_ID = jointInfo.ISO_ID
|
|
LEFT JOIN Base_Unit AS unit ON unit.UnitId = isoInfo.UnitId
|
|
LEFT JOIN ProjectData_WorkArea AS workArea ON workArea.WorkAreaId = isoInfo.WorkAreaId
|
|
LEFT JOIN BS_Welder AS cellWelder ON cellWelder.WED_ID = jointInfo.JOT_CellWelder
|
|
LEFT JOIN BS_Welder AS floorWelder ON floorWelder.WED_ID = jointInfo.JOT_FloorWelder
|
|
LEFT JOIN (SELECT DISTINCT CH_TrustCode,CH_TrustDate,DetectionTypeCode,JOT_ID FROM CH_TrustItem
|
|
LEFT JOIN CH_Trust ON CH_Trust.CH_TrustID = CH_TrustItem.CH_TrustID
|
|
LEFT JOIN Base_DetectionType ON Base_DetectionType.DetectionTypeId = CH_Trust.CH_NDTMethod
|
|
WHERE CH_Trust.CH_TrustType='1') AS trustItem ON trustItem.JOT_ID = jointInfo.JOT_ID
|
|
WHERE jointInfo.PW_PointID is not null
|
|
AND (jointInfo.ProjectId=@projectId OR @projectId IS NULL)
|
|
AND (isoInfo.UnitId=@unitId OR @unitId IS NULL)
|
|
AND (isoInfo.WorkAreaId=@workAreaId OR @workAreaId IS NULL)
|
|
GO
|
|
|
|
|