ALTER VIEW [dbo].[HJGL_View_CheckResultSummary1]
/*无损检测结果汇总表-1*/
AS

SELECT batchItem.BatchDetailId,batch.ProjectId,batch.BatchCode,iso.ISO_IsoNo,jot.JOT_JointNo,
       jot.Sort1,jot.Sort2,jot.Sort3,jot.Sort4,jot.Sort5,
       iso.ISO_ID,jot.JOT_JointDesc,batch.BatchId,batchItem.NDT,
	   (case when welder.WED_Code is null then  
	   (case when welder2.WED_Code is not null then welder2.WED_Code end)
	   else (case when welder2.WED_Code is not null and welder.WED_Code<>welder2.WED_Code 
	   then welder.WED_Code+'/'+welder2.WED_Code 
	   else welder.WED_Code end)   end) as WED_Code,--焊工号
	   iso.ISC_ID,iso.SER_ID,jot.STE_ID,joty.JOTY_Group,jot.JOT_Dia
FROM dbo.HJGL_BO_BatchDetail batchItem
LEFT JOIN dbo.HJGL_BO_Batch batch ON batch.BatchId = batchItem.BatchId
LEFT JOIN dbo.HJGL_PW_JointInfo jot ON jot.JOT_ID=batchItem.JOT_ID
LEFT JOIN dbo.HJGL_PW_IsoInfo iso ON iso.ISO_ID=jot.ISO_ID
LEFT JOIN dbo.HJGL_BS_Welder welder ON welder.WED_ID = jot.JOT_CellWelder
LEFT JOIN dbo.HJGL_BS_Welder welder2 ON welder2.WED_ID = jot.JOT_FloorWelder
LEFT JOIN dbo.HJGL_BS_JointType joty ON joty.JOTY_ID = jot.JOTY_ID

WHERE batchItem.NDT IS NOT NULL


GO


ALTER PROCEDURE [dbo].[HJGL_sp_CheckResultSummary]
(
   @ProjectId NVARCHAR(50),
   @ISC_ID NVARCHAR(50),  --管道等级
   @SER_ID NVARCHAR(50),  --介质
   @STE_ID NVARCHAR(50),   --材质
   @Flag CHAR(1)
)
/*无损检测结果汇总表*/
AS
BEGIN
IF(@Flag='1')
BEGIN
SELECT v.*
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY case when RTReport is null then 1 else 0 end asc, RTReport,UTReport,MTReport,PTReport) AS Number,
	   BatchDetailId, ISO_IsoNo,
	   (JOT_JointNo+(case (select top (1) PointType from dbo.HJGL_BO_BatchDetail bd where bd.BatchDetailId=c.BatchDetailId ORDER BY bd.PointType) when '2' then 'K'
	                 else '' end)) as JOT_JointNo,
	   WED_Code, BatchCode,
 
	  (CASE (CASE WHEN RT IS NULL THEN ISNULL(UT,'') ELSE (CASE WHEN UT IS NULL THEN ISNULL(RT,'')
	   ELSE (ISNULL(RT,'') +'/'+ ISNULL(UT,'')) END) END) 
	   WHEN '2' THEN '合格' WHEN '2/4' THEN '合格/不合格' WHEN '4/2' THEN '不合格/合格' WHEN '4' THEN '不合格' ELSE '' END) AS RTUTResult,

       CASE WHEN RTReport IS NULL THEN ISNULL(UTReport,'') ELSE (CASE WHEN UTReport IS NULL THEN ISNULL(RTReport,'')
	   ELSE (ISNULL(RTReport,'') +'/'+ ISNULL(UTReport,'')) END) END AS RTUTReport,

	   (CASE (CASE WHEN MT IS NULL THEN ISNULL(PT,'') ELSE (CASE WHEN PT IS NULL THEN ISNULL(MT,'')
	  ELSE (ISNULL(MT,'') +'/'+ ISNULL(PT,'')) END) END) WHEN '2' THEN '合格' WHEN
	  '2/4' THEN '合格/不合格' WHEN '4/2' THEN '不合格/合格' WHEN '4' THEN '不合格' ELSE '' END) AS MTPTResult,

       CASE WHEN MTReport IS NULL THEN ISNULL(PTReport,'') ELSE (CASE WHEN PTReport IS NULL THEN ISNULL(MTReport,'')
	   ELSE (ISNULL(MTReport,'') +'/'+ ISNULL(PTReport,'')) END) END AS MTPTReport
	   ,c.Sort1,c.Sort2,c.Sort3,c.Sort4,c.Sort5
FROM dbo.HJGL_View_CheckResultSummary c
WHERE Projectid=@ProjectId AND ISC_ID=@ISC_ID AND SER_ID=@SER_ID AND STE_ID=@STE_ID) AS v
WHERE v.Number<=5 
END
ELSE IF(@Flag='2')
BEGIN
SELECT v.*
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY case when RTReport is null then 1 else 0 end asc, RTReport,UTReport,MTReport,PTReport) AS Number,
	   BatchDetailId,ISO_IsoNo,
	   (JOT_JointNo+(case (select top (1) PointType from dbo.HJGL_BO_BatchDetail bd where bd.BatchDetailId=c.BatchDetailId ORDER BY bd.PointType) when '2' then 'K'
	                 else '' end)) as JOT_JointNo,
	   WED_Code,BatchCode,

	  (CASE (CASE WHEN RT IS NULL THEN ISNULL(UT,'') ELSE (CASE WHEN UT IS NULL THEN ISNULL(RT,'')
	   ELSE (ISNULL(RT,'') +'/'+ ISNULL(UT,'')) END) END) 
	   WHEN '2' THEN '合格' WHEN '2/4' THEN '合格/不合格' WHEN '4/2' THEN '不合格/合格'  WHEN '4' THEN '不合格' ELSE '' END) AS RTUTResult,

       CASE WHEN RTReport IS NULL THEN ISNULL(UTReport,'') ELSE (CASE WHEN UTReport IS NULL THEN ISNULL(RTReport,'')
	   ELSE (ISNULL(RTReport,'') +'/'+ ISNULL(UTReport,'')) END) END AS RTUTReport,

	   (CASE (CASE WHEN MT IS NULL THEN ISNULL(PT,'') ELSE (CASE WHEN PT IS NULL THEN ISNULL(MT,'')
	           ELSE (ISNULL(MT,'') +'/'+ ISNULL(PT,'')) END) END) WHEN '2' THEN '合格' WHEN
	    '2/4' THEN '合格/不合格' WHEN '4/2' THEN '不合格/合格' WHEN '4' THEN '不合格' ELSE '' END) AS MTPTResult,

       CASE WHEN MTReport IS NULL THEN ISNULL(PTReport,'') ELSE (CASE WHEN PTReport IS NULL THEN ISNULL(MTReport,'')
	   ELSE (ISNULL(MTReport,'') +'/'+ ISNULL(PTReport,'')) END) END AS MTPTReport
	   ,c.Sort1,c.Sort2,c.Sort3,c.Sort4,c.Sort5
FROM dbo.HJGL_View_CheckResultSummary c
WHERE Projectid=@ProjectId AND ISC_ID=@ISC_ID AND SER_ID=@SER_ID AND STE_ID=@STE_ID) AS v
WHERE v.Number>5 
END
ELSE
BEGIN
SELECT v.*
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY case when RTReport is null then 1 else 0 end asc, RTReport,UTReport,MTReport,PTReport) AS Number,
	   BatchDetailId,ISO_IsoNo,
	   (JOT_JointNo+(case (select top (1) PointType from dbo.HJGL_BO_BatchDetail bd where bd.BatchDetailId=c.BatchDetailId ORDER BY bd.PointType) when '2' then 'K'
	                 else '' end)) as JOT_JointNo,
	   WED_Code, BatchCode,
     
	  (CASE (CASE WHEN RT IS NULL THEN ISNULL(UT,'') ELSE (CASE WHEN UT IS NULL THEN ISNULL(RT,'')
	   ELSE (ISNULL(RT,'') +'/'+ ISNULL(UT,'')) END) END) 
	   WHEN '2' THEN '合格' WHEN '2/4' THEN '合格/不合格' WHEN '4/2' THEN '不合格/合格' WHEN '4' THEN '不合格' ELSE '' END) AS RTUTResult,

       CASE WHEN RTReport IS NULL THEN ISNULL(UTReport,'') ELSE (CASE WHEN UTReport IS NULL THEN ISNULL(RTReport,'')
	   ELSE (ISNULL(RTReport,'') +'/'+ ISNULL(UTReport,'')) END) END AS RTUTReport,

	  (CASE (CASE WHEN MT IS NULL THEN ISNULL(PT,'') ELSE (CASE WHEN PT IS NULL THEN ISNULL(MT,'')
	         ELSE (ISNULL(MT,'') +'/'+ ISNULL(PT,'')) END) END) WHEN '2' THEN '合格' WHEN
	  '2/4' THEN '合格/不合格' WHEN '4/2' THEN '不合格/合格' WHEN '4' THEN '不合格' ELSE '' END) AS MTPTResult,

       CASE WHEN MTReport IS NULL THEN ISNULL(PTReport,'') ELSE (CASE WHEN PTReport IS NULL THEN ISNULL(MTReport,'')
	   ELSE (ISNULL(MTReport,'') +'/'+ ISNULL(PTReport,'')) END) END AS MTPTReport
	   ,c.Sort1,c.Sort2,c.Sort3,c.Sort4,c.Sort5
FROM dbo.HJGL_View_CheckResultSummary c
WHERE Projectid=@ProjectId AND ISC_ID=@ISC_ID AND SER_ID=@SER_ID AND STE_ID=@STE_ID) AS v
END		
END


GO