130 lines
6.1 KiB
MySQL
130 lines
6.1 KiB
MySQL
|
|
|||
|
|
|
|||
|
|
ALTER VIEW [dbo].[HJGL_View_CheckResultSummary1]
|
|||
|
|
/*<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ܱ<EFBFBD>-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,--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
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), --<EFBFBD>ܵ<EFBFBD><EFBFBD>ȼ<EFBFBD>
|
|||
|
|
@SER_ID NVARCHAR(50), --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
@STE_ID NVARCHAR(50), --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
@Flag CHAR(1)
|
|||
|
|
)
|
|||
|
|
/*<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ܱ<EFBFBD>*/
|
|||
|
|
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 '<EFBFBD>ϸ<EFBFBD>' WHEN '2/4' THEN '<EFBFBD>ϸ<EFBFBD>/<2F><><EFBFBD>ϸ<EFBFBD>' WHEN '4/2' THEN '<EFBFBD><EFBFBD><EFBFBD>ϸ<EFBFBD>/<2F>ϸ<EFBFBD>' WHEN '4' THEN '<EFBFBD><EFBFBD><EFBFBD>ϸ<EFBFBD>' 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 '<EFBFBD>ϸ<EFBFBD>' WHEN
|
|||
|
|
'2/4' THEN '<EFBFBD>ϸ<EFBFBD>/<2F><><EFBFBD>ϸ<EFBFBD>' WHEN '4/2' THEN '<EFBFBD><EFBFBD><EFBFBD>ϸ<EFBFBD>/<2F>ϸ<EFBFBD>' WHEN '4' THEN '<EFBFBD><EFBFBD><EFBFBD>ϸ<EFBFBD>' 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 '<EFBFBD>ϸ<EFBFBD>' WHEN '2/4' THEN '<EFBFBD>ϸ<EFBFBD>/<2F><><EFBFBD>ϸ<EFBFBD>' WHEN '4/2' THEN '<EFBFBD><EFBFBD><EFBFBD>ϸ<EFBFBD>/<2F>ϸ<EFBFBD>' WHEN '4' THEN '<EFBFBD><EFBFBD><EFBFBD>ϸ<EFBFBD>' 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 '<EFBFBD>ϸ<EFBFBD>' WHEN
|
|||
|
|
'2/4' THEN '<EFBFBD>ϸ<EFBFBD>/<2F><><EFBFBD>ϸ<EFBFBD>' WHEN '4/2' THEN '<EFBFBD><EFBFBD><EFBFBD>ϸ<EFBFBD>/<2F>ϸ<EFBFBD>' WHEN '4' THEN '<EFBFBD><EFBFBD><EFBFBD>ϸ<EFBFBD>' 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 '<EFBFBD>ϸ<EFBFBD>' WHEN '2/4' THEN '<EFBFBD>ϸ<EFBFBD>/<2F><><EFBFBD>ϸ<EFBFBD>' WHEN '4/2' THEN '<EFBFBD><EFBFBD><EFBFBD>ϸ<EFBFBD>/<2F>ϸ<EFBFBD>' WHEN '4' THEN '<EFBFBD><EFBFBD><EFBFBD>ϸ<EFBFBD>' 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 '<EFBFBD>ϸ<EFBFBD>' WHEN
|
|||
|
|
'2/4' THEN '<EFBFBD>ϸ<EFBFBD>/<2F><><EFBFBD>ϸ<EFBFBD>' WHEN '4/2' THEN '<EFBFBD><EFBFBD><EFBFBD>ϸ<EFBFBD>/<2F>ϸ<EFBFBD>' WHEN '4' THEN '<EFBFBD><EFBFBD><EFBFBD>ϸ<EFBFBD>' 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
|
|||
|
|
|
|||
|
|
|