200 lines
11 KiB
Transact-SQL
200 lines
11 KiB
Transact-SQL
INSERT INTO dbo.Sys_Menu(MenuId, MenuName, Url, SortIndex, SuperMenu,MenuModule)
|
|
VALUES('93BBEACB-90BE-45B2-BA38-3250EA52A40E','检验批查询','HJGL/WeldingManage/PointManageMove.aspx',15,'4F07D4BF-5971-4D3C-6666-B2ACA8CD82FF','3')
|
|
GO
|
|
INSERT INTO dbo.Sys_Menu(MenuId, MenuName, Url, SortIndex, SuperMenu,MenuModule)
|
|
VALUES('CDBAD816-2591-4FDC-BFA2-3D076F47E17B','统计样表','HJGL/WeldingReport/StatisticsSampleTable.aspx',170,'F3B157B7-9BEE-4150-6666-087828F1C51D','3')
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[spRT_CheckResult]
|
|
(
|
|
@BatchId NVARCHAR(50)
|
|
)
|
|
AS
|
|
--射线检测结果确认表
|
|
SELECT v1.JotNum AS JotNum,v1.FilmNum, --一次检测合格焊口数和合格片子数
|
|
v2.JotNum AS NoJotNum,v2.FilmNum AS NoFilmNum, --一次检测不合格焊口数和不合格片子数
|
|
v3.JotNum AS ExpJotNum,v3.FilmNum AS ExpFilmNum, --扩透合格焊口数和合格片子数
|
|
v4.JotNum AS ExpNoJotNum,v4.FilmNum AS ExpNoFilmNum, --扩透不合格焊口数和不合格片子数
|
|
(ISNULL(v1.JotNum,0)+ISNULL(v3.JotNum,0)) AS RealJotNum, --实际
|
|
(ISNULL(v1.FilmNum,0)+ISNULL(v3.FilmNum,0)) AS RealFilmNum,
|
|
iso.ISO_IsoNo,m.STE_Code,
|
|
(SELECT TOP 1 j.JOT_JointDesc FROM dbo.HJGL_PW_JointInfo j WHERE j.ISO_ID=iso.ISO_ID) AS IsoDesc, --规格
|
|
(SELECT TOP 1 Joty_Level FROM dbo.HJGL_BS_WeldControl c1
|
|
WHERE c1.ExecStandardId=iso.ISO_Executive AND c1.ISC_ID=iso.ISC_ID) AS JotyLevel, --合格等级
|
|
(SELECT TOP 1 rate.NDTR_Name FROM dbo.HJGL_BS_WeldControl c2
|
|
LEFT JOIN dbo.HJGL_BS_NDTRate rate ON rate.NDTR_ID = c2.Joty_Rate
|
|
WHERE c2.ExecStandardId=iso.ISO_Executive AND c2.ISC_ID=iso.ISC_ID) AS NDTR_Name --探伤比例
|
|
FROM
|
|
|
|
(SELECT COUNT(trustItem.JOT_ID) AS JotNum ,SUM(c.FilmNum) AS FilmNum,batch.BatchId,iso.ISO_ID
|
|
from dbo.HJGL_CH_TrustItem trustItem
|
|
LEFT JOIN dbo.HJGL_CH_Trust trust ON trust.CH_TrustID = trustItem.CH_TrustID
|
|
LEFT JOIN dbo.HJGL_PW_JointInfo jot ON jot.JOT_ID=trustItem.JOT_ID
|
|
LEFT JOIN dbo.HJGL_PW_IsoInfo iso ON iso.ISO_ID=jot.ISO_ID
|
|
LEFT JOIN dbo.HJGL_BO_BatchDetail batchDetail ON batchDetail.BatchDetailId = trustItem.BatchDetailId
|
|
LEFT JOIN dbo.HJGL_BO_Batch batch ON batch.BatchId = batchDetail.BatchId
|
|
LEFT JOIN dbo.HJGL_BS_NDTType ndt ON ndt.NDT_ID=trust.CH_NDTMethod
|
|
LEFT JOIN (SELECT JOT_ID, COUNT(*) AS FilmNum FROM dbo.HJGL_BO_QualityRating GROUP BY JOT_ID)
|
|
c ON c.JOT_ID=trustItem.JOT_ID
|
|
WHERE batchDetail.PointType='1' and ndt.NDT_Code LIKE '%RT%'
|
|
GROUP BY batch.BatchId, iso.ISO_ID) v1
|
|
|
|
LEFT JOIN
|
|
|
|
(SELECT COUNT(trustItem.JOT_ID) AS JotNum ,SUM(c.FilmNum) AS FilmNum,batch.BatchId,iso.ISO_ID
|
|
from dbo.HJGL_CH_TrustItem trustItem
|
|
LEFT JOIN dbo.HJGL_CH_Trust trust ON trust.CH_TrustID = trustItem.CH_TrustID
|
|
LEFT JOIN dbo.HJGL_PW_JointInfo jot ON jot.JOT_ID=trustItem.JOT_ID
|
|
LEFT JOIN dbo.HJGL_PW_IsoInfo iso ON iso.ISO_ID=jot.ISO_ID
|
|
LEFT JOIN dbo.HJGL_BO_BatchDetail batchDetail ON batchDetail.BatchDetailId = trustItem.BatchDetailId
|
|
LEFT JOIN dbo.HJGL_BO_Batch batch ON batch.BatchId = batchDetail.BatchId
|
|
LEFT JOIN dbo.HJGL_BS_NDTType ndt ON ndt.NDT_ID=trust.CH_NDTMethod
|
|
LEFT JOIN (SELECT JOT_ID, COUNT(*) AS FilmNum FROM dbo.HJGL_BO_QualityRating WHERE IsPass='不合格' GROUP BY JOT_ID)
|
|
c ON c.JOT_ID=trustItem.JOT_ID
|
|
WHERE batchDetail.PointType='1' and ndt.NDT_Code LIKE '%RT%' AND trustItem.ReportNoticeDate IS NOT null
|
|
GROUP BY batch.BatchId, iso.ISO_ID) v2 ON v1.ISO_ID=v2.ISO_ID AND v1.BatchId=v2.BatchId
|
|
|
|
LEFT JOIN
|
|
|
|
(SELECT COUNT(trustItem.JOT_ID) AS JotNum ,SUM(c.FilmNum) AS FilmNum,batch.BatchId,iso.ISO_ID
|
|
from dbo.HJGL_CH_TrustItem trustItem
|
|
LEFT JOIN dbo.HJGL_CH_Trust trust ON trust.CH_TrustID = trustItem.CH_TrustID
|
|
LEFT JOIN dbo.HJGL_PW_JointInfo jot ON jot.JOT_ID=trustItem.JOT_ID
|
|
LEFT JOIN dbo.HJGL_PW_IsoInfo iso ON iso.ISO_ID=jot.ISO_ID
|
|
LEFT JOIN dbo.HJGL_BO_BatchDetail batchDetail ON batchDetail.BatchDetailId = trustItem.BatchDetailId
|
|
LEFT JOIN dbo.HJGL_BO_Batch batch ON batch.BatchId = batchDetail.BatchId
|
|
LEFT JOIN dbo.HJGL_BS_NDTType ndt ON ndt.NDT_ID=trust.CH_NDTMethod
|
|
LEFT JOIN (SELECT JOT_ID, COUNT(*) AS FilmNum FROM dbo.HJGL_BO_QualityRating GROUP BY JOT_ID)
|
|
c ON c.JOT_ID=trustItem.JOT_ID
|
|
WHERE batchDetail.PointType='2' and ndt.NDT_Code LIKE '%RT%'
|
|
GROUP BY batch.BatchId, iso.ISO_ID) v3 ON v1.ISO_ID=v3.ISO_ID AND v1.BatchId=v3.BatchId
|
|
|
|
LEFT JOIN
|
|
|
|
(SELECT COUNT(trustItem.JOT_ID) AS JotNum ,SUM(c.FilmNum) AS FilmNum,batch.BatchId,iso.ISO_ID
|
|
from dbo.HJGL_CH_TrustItem trustItem
|
|
LEFT JOIN dbo.HJGL_CH_Trust trust ON trust.CH_TrustID = trustItem.CH_TrustID
|
|
LEFT JOIN dbo.HJGL_PW_JointInfo jot ON jot.JOT_ID=trustItem.JOT_ID
|
|
LEFT JOIN dbo.HJGL_PW_IsoInfo iso ON iso.ISO_ID=jot.ISO_ID
|
|
LEFT JOIN dbo.HJGL_BO_BatchDetail batchDetail ON batchDetail.BatchDetailId = trustItem.BatchDetailId
|
|
LEFT JOIN dbo.HJGL_BO_Batch batch ON batch.BatchId = batchDetail.BatchId
|
|
LEFT JOIN dbo.HJGL_BS_NDTType ndt ON ndt.NDT_ID=trust.CH_NDTMethod
|
|
LEFT JOIN (SELECT JOT_ID, COUNT(*) AS FilmNum FROM dbo.HJGL_BO_QualityRating WHERE IsPass='不合格' GROUP BY JOT_ID)
|
|
c ON c.JOT_ID=trustItem.JOT_ID
|
|
WHERE batchDetail.PointType='2' and ndt.NDT_Code LIKE '%RT%' AND trustItem.ReportNoticeDate IS NOT null
|
|
GROUP BY batch.BatchId, iso.ISO_ID) v4 ON v1.ISO_ID=v4.ISO_ID AND v1.BatchId=v4.BatchId
|
|
|
|
LEFT JOIN dbo.HJGL_PW_IsoInfo iso ON iso.ISO_ID=v1.ISO_ID
|
|
LEFT JOIN dbo.HJGL_BS_Steel m ON m.STE_ID=iso.STE_ID
|
|
|
|
WHERE v1.BatchId=@BatchId
|
|
|
|
GO
|
|
|
|
Create PROCEDURE [dbo].[spRT_CheckRepairResult]
|
|
(
|
|
@BatchId NVARCHAR(50)
|
|
)
|
|
AS
|
|
--射线检测结果确认表（返修）
|
|
SELECT
|
|
(SELECT COUNT(DISTINCT(r1.JOT_ID)) FROM dbo.HJGL_CH_RepairItemRecord r1 WHERE r1.RepairMark='R1' and r1.BatchId=@BatchId) AS JotNumR1,
|
|
(SELECT COUNT(r1.JOT_ID) FROM dbo.HJGL_CH_RepairItemRecord r1 WHERE r1.RepairMark='R1' and r1.BatchId=@BatchId) AS FilmNumR1,
|
|
(SELECT COUNT(DISTINCT(r2.JOT_ID)) FROM dbo.HJGL_CH_RepairItemRecord r2 WHERE r2.RepairMark='R2' and r2.BatchId=@BatchId) AS JotNumR2,
|
|
(SELECT COUNT(r2.JOT_ID) FROM dbo.HJGL_CH_RepairItemRecord r2 WHERE r2.RepairMark='R2' and r2.BatchId=@BatchId) AS FilmNumR2,
|
|
(SELECT COUNT(DISTINCT(r3.JOT_ID)) FROM dbo.HJGL_CH_RepairItemRecord r3 WHERE r3.RepairMark='R3' and r3.BatchId=@BatchId) AS JotNumR3,
|
|
(SELECT COUNT(r3.JOT_ID) FROM dbo.HJGL_CH_RepairItemRecord r3 WHERE r3.RepairMark='R3' and r3.BatchId=@BatchId) AS FilmNumR3
|
|
|
|
GO
|
|
|
|
|
|
CREATE PROC [dbo].[HJGL_sp_StatisticsSampleTable]
|
|
@projectId NVARCHAR(50) = NULL,
|
|
@iso_isoNo VARCHAR(50) = NULL
|
|
AS
|
|
/*********统计样表********/
|
|
SELECT
|
|
project.ProjectCode, --施工号
|
|
IsoInfo.PressureTestPackageNo,--试压包编号
|
|
IsoInfo.ISO_ID,
|
|
IsoInfo.ISO_IsoNo, --管线号
|
|
JointInfo.JOT_JointNo, --焊缝编号
|
|
Welder.WED_Code,--焊工号
|
|
Steel.STE_Code,--材质
|
|
NDTRate.NDTR_Name,--对应检测比例
|
|
WeldReportMain.JOT_WeldDate,--焊接日期
|
|
JointInfo.JOT_Size,--寸经
|
|
Batch.BatchCode,--批次
|
|
(CASE WHEN BatchDetail.PointType=2 THEN 'K' ELSE '' END) AS PointType,--扩拍号
|
|
JointInfo.JOT_Dia,--直径
|
|
JointInfo.JOT_Sch, --壁厚
|
|
(CASE WHEN BatchDetail.NDT LIKE '%'+(SELECT TOP 1 rt.NDT_ID FROM dbo.HJGL_BS_NDTType rt WHERE rt.NDT_Code LIKE '%RT%')+'%'
|
|
THEN '√' ELSE '' END) AS RTTrust, --RT委托
|
|
(SELECT TOP 1 trust.CH_TrustDate FROM dbo.HJGL_CH_TrustItem trustItem
|
|
LEFT JOIN dbo.HJGL_CH_Trust trust ON trust.CH_TrustID=trustItem.CH_TrustID
|
|
LEFT JOIN dbo.HJGL_BS_NDTType n ON n.NDT_ID=trust.CH_NDTMethod
|
|
WHERE trustItem.BatchDetailId=BatchDetail.BatchDetailId AND n.NDT_Code LIKE '%RT%')
|
|
AS RTTrustDate, --射线检测委托日期
|
|
(SELECT TOP 1 trustItem.States FROM dbo.HJGL_CH_TrustItem trustItem
|
|
LEFT JOIN dbo.HJGL_CH_Trust trust ON trust.CH_TrustID=trustItem.CH_TrustID
|
|
LEFT JOIN dbo.HJGL_BS_NDTType n ON n.NDT_ID=trust.CH_NDTMethod
|
|
WHERE trustItem.BatchDetailId=BatchDetail.BatchDetailId AND n.NDT_Code LIKE '%RT%')
|
|
AS RTTestResult,--RT检测结果
|
|
|
|
(CASE WHEN BatchDetail.NDT LIKE '%'+(SELECT TOP 1 ut.NDT_ID FROM dbo.HJGL_BS_NDTType ut WHERE ut.NDT_Code LIKE '%UT%')+'%'
|
|
THEN '√' ELSE '' END) UTTrust,--UT委托
|
|
(SELECT TOP 1 trustItem.States FROM dbo.HJGL_CH_TrustItem trustItem
|
|
LEFT JOIN dbo.HJGL_CH_Trust trust ON trust.CH_TrustID=trustItem.CH_TrustID
|
|
LEFT JOIN dbo.HJGL_BS_NDTType n ON n.NDT_ID=trust.CH_NDTMethod
|
|
WHERE trustItem.BatchDetailId=BatchDetail.BatchDetailId AND n.NDT_Code LIKE '%UT%')
|
|
AS UTTestResult,--UT检测结果
|
|
|
|
(CASE WHEN BatchDetail.NDT LIKE '%'+(SELECT TOP 1 mt.NDT_ID FROM dbo.HJGL_BS_NDTType mt WHERE mt.NDT_Code LIKE '%MT%')+'%'
|
|
THEN '√' ELSE '' END) AS MTTrust,--MT委托
|
|
(SELECT TOP 1 trustItem.States FROM dbo.HJGL_CH_TrustItem trustItem
|
|
LEFT JOIN dbo.HJGL_CH_Trust trust ON trust.CH_TrustID=trustItem.CH_TrustID
|
|
LEFT JOIN dbo.HJGL_BS_NDTType n ON n.NDT_ID=trust.CH_NDTMethod
|
|
WHERE trustItem.BatchDetailId=BatchDetail.BatchDetailId AND n.NDT_Code LIKE '%MT%')
|
|
AS MTTestResult,--MT检测结果
|
|
|
|
(CASE WHEN BatchDetail.NDT LIKE '%'+(SELECT TOP 1 pt.NDT_ID FROM dbo.HJGL_BS_NDTType pt WHERE pt.NDT_Code LIKE '%PT%')+'%'
|
|
THEN '√' ELSE '' END) AS PTTrust,--PT委托
|
|
(SELECT TOP 1 trustItem.States FROM dbo.HJGL_CH_TrustItem trustItem
|
|
LEFT JOIN dbo.HJGL_CH_Trust trust ON trust.CH_TrustID=trustItem.CH_TrustID
|
|
LEFT JOIN dbo.HJGL_BS_NDTType n ON n.NDT_ID=trust.CH_NDTMethod
|
|
WHERE trustItem.BatchDetailId=BatchDetail.BatchDetailId AND n.NDT_Code LIKE '%PT%')
|
|
AS PTTestResult,--PT检测结果
|
|
|
|
'' AS HotProessResult,--热处理委托
|
|
'' AS HotProessTrustDate,--热处理委托时间
|
|
'' AS HotProessResultOK, --热处理合格
|
|
--热处理反馈时间
|
|
'' AS HotProessResultNotOK,--热处理不合格
|
|
'' AS HardTestResult,--硬度委托
|
|
'' AS HardTrustDate,--硬度委托时间
|
|
'' AS HardTestResultOK,--硬度合格
|
|
--硬度反馈时间
|
|
'' AS HardTestResultNotOK --硬度不合格
|
|
|
|
FROM dbo.HJGL_BO_BatchDetail AS BatchDetail
|
|
LEFT JOIN DBO.HJGL_PW_IsoInfo AS IsoInfo ON IsoInfo.ISO_ID = BatchDetail.ISO_ID
|
|
LEFT JOIN DBO.HJGL_PW_JointInfo AS JointInfo ON JointInfo.JOT_ID = BatchDetail.JOT_ID
|
|
LEFT JOIN DBO.HJGL_BO_Batch AS Batch ON Batch.BatchId = BatchDetail.BatchId
|
|
LEFT JOIN dbo.Base_Project project ON project.ProjectId=Batch.ProjectId
|
|
LEFT JOIN dbo.HJGL_BS_Steel AS Steel ON Steel.STE_ID = Batch.STE_ID
|
|
LEFT JOIN DBO.HJGL_BS_Welder AS Welder ON Welder.WED_ID = JointInfo.JOT_CellWelder
|
|
LEFT JOIN DBO.HJGL_BO_WeldReportMain AS WeldReportMain ON WeldReportMain.DReportID = JointInfo.DReportID
|
|
LEFT JOIN dbo.HJGL_BS_NDTRate AS NDTRate ON NDTRate.NDTR_ID = JointInfo.NDTR_ID
|
|
|
|
--LEFT JOIN DBO.HJGL_CH_HotProessTrustItem AS HotProessTrustItem ON HotProessTrustItem.JOT_ID= BatchDetail.
|
|
--LEFT JOIN DBO.HJGL_CH_HotProessResult AS HotProessResult ON HotProessResult.JOT_ID = BatchDetail.JOT_ID
|
|
--LEFT JOIN DBO.HJGL_CH_HardTestResult AS HardTestResult ON HardTestResult.JOT_ID = BatchDetail.JOT_ID
|
|
--LEFT JOIN DBO.HJGL_CH_HardTestReport AS HardTestReport ON HardTestReport.HardTestReportId = HardTestResult.HardTestReportId
|
|
|
|
WHERE (Batch.ProjectId=@projectId OR @projectId IS NULL)
|
|
AND (IsoInfo.ISO_IsoNo =@iso_isoNo OR @iso_isoNo IS NULL)
|
|
ORDER BY project.ProjectCode,IsoInfo.ISO_IsoNo,JointInfo.JOT_JointNo
|
|
|
|
|
|
GO
|