102 lines
4.2 KiB
MySQL
102 lines
4.2 KiB
MySQL
|
|
||
|
|
|
||
|
|
ALTER VIEW [dbo].[View_Batch_BatchTrustItem]
|
||
|
|
AS
|
||
|
|
/********无损委托********/
|
||
|
|
SELECT
|
||
|
|
ROW_NUMBER() OVER(ORDER BY WeldJointCode) AS Number,
|
||
|
|
BatchTrustItem.TrustBatchItemId,
|
||
|
|
BatchTrustItem.TrustBatchId,
|
||
|
|
BatchTrustItem.PointBatchItemId,
|
||
|
|
BatchTrustItem.WeldJointId,
|
||
|
|
BatchTrustItem.CreateDate,
|
||
|
|
BatchTrustItem.TrustNum,
|
||
|
|
BatchTrust.TrustType,
|
||
|
|
BatchTrustItem.RepairNum,
|
||
|
|
BatchTrust.TrustBatchCode, --委托单号
|
||
|
|
BatchTrust.ProjectId,
|
||
|
|
UnitWork.UnitWorkCode, --单位工程
|
||
|
|
pipe.PipelineCode, --管线号
|
||
|
|
pipingClass.PipingClassCode, --管线等级
|
||
|
|
jot.WeldJointCode+isnull((select top 1 RepairMark from [dbo].[HJGL_RepairRecord] where [WeldJointId]=BatchTrustItem.WeldJointId and RepairRecordId=BatchTrustItem.RepairRecordId),'')+(case when PointBatchItem.PointState='2' then 'K' else '' end) as WeldJointCode, --焊口号
|
||
|
|
mat.MaterialCode, --材质
|
||
|
|
jot.JointArea, --焊接区域
|
||
|
|
welder.WelderCode AS WelderCode, --焊工号
|
||
|
|
weldType.WeldTypeCode, --焊缝类型
|
||
|
|
jot.Dia, --外径
|
||
|
|
jot.Size, --寸径
|
||
|
|
jot.Thickness, --壁厚
|
||
|
|
method.WeldingMethodCode, --焊接方法
|
||
|
|
rate.DetectionRateCode,
|
||
|
|
null as CheckDefects,
|
||
|
|
PointBatchItem.PointDate --点口日期
|
||
|
|
|
||
|
|
|
||
|
|
FROM dbo.HJGL_Batch_BatchTrustItem AS BatchTrustItem
|
||
|
|
LEFT JOIN dbo.HJGL_Batch_BatchTrust AS BatchTrust ON BatchTrust.TrustBatchId=BatchTrustItem.TrustBatchId
|
||
|
|
LEFT JOIN dbo.HJGL_Batch_PointBatchItem AS PointBatchItem ON PointBatchItem.PointBatchItemId=BatchTrustItem.PointBatchItemId
|
||
|
|
LEFT JOIN dbo.HJGL_Batch_PointBatch point ON point.PointBatchId = PointBatchItem.PointBatchId
|
||
|
|
LEFT JOIN dbo.Base_DetectionRate rate ON rate.DetectionRateId = point.DetectionRateId
|
||
|
|
LEFT JOIN dbo.HJGL_WeldJoint jot ON jot.WeldJointId = BatchTrustItem.WeldJointId
|
||
|
|
LEFT JOIN dbo.HJGL_Pipeline pipe ON pipe.PipelineId = jot.PipelineId
|
||
|
|
LEFT JOIN Base_PipingClass AS pipingClass ON PipingClass.PipingClassId=pipe.PipingClassId
|
||
|
|
LEFT JOIN dbo.WBS_UnitWork UnitWork ON UnitWork.UnitWorkId = BatchTrust.UnitWorkId
|
||
|
|
LEFT JOIN dbo.Base_WeldType weldType ON weldType.WeldTypeId = jot.WeldTypeId
|
||
|
|
LEFT JOIN dbo.SitePerson_Person welder ON welder.PersonId = jot.BackingWelderId and welder.ProjectId=BatchTrust.ProjectId
|
||
|
|
LEFT JOIN dbo.Base_Material mat ON mat.MaterialId = jot.Material1Id
|
||
|
|
LEFT JOIN dbo.Base_WeldingMethod method ON method.WeldingMethodId = jot.WeldingMethodId
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
GO
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
ALTER VIEW [dbo].[View_GenerateTrustItem]
|
||
|
|
AS
|
||
|
|
SELECT point.ProjectId,point.UnitWorkId ,point.UnitId,point.DetectionTypeId,point.DetectionRateId,
|
||
|
|
--上面是条件
|
||
|
|
pipe.PipelineCode, --管线号
|
||
|
|
pipingClass.PipingClassCode, --管线等级
|
||
|
|
jot.WeldJointCode+isnull((select RepairMark from [dbo].[HJGL_RepairRecord] where [WeldJointId]=trustItem.WeldJointId),'')+(case when pointItem.PointState='2' then 'K' else '' end) as WeldJointCode, --焊口号
|
||
|
|
mat.MaterialCode, --材质
|
||
|
|
jot.JointArea, --焊接区域
|
||
|
|
welder.WelderCode AS WelderCode, --焊工号
|
||
|
|
weldType.WeldTypeCode, --焊缝类型
|
||
|
|
jot.Dia, --外径
|
||
|
|
jot.Size, --寸径
|
||
|
|
jot.Thickness, --壁厚
|
||
|
|
method.WeldingMethodCode, --焊接方法
|
||
|
|
rate.DetectionRateCode,
|
||
|
|
pointItem.PointDate, --点口日期
|
||
|
|
pointItem.PointBatchItemId,pointItem.PointBatchId,pointItem.WeldJointId
|
||
|
|
FROM dbo.HJGL_Batch_PointBatchItem pointItem
|
||
|
|
LEFT JOIN dbo.HJGL_Batch_PointBatch point ON point.PointBatchId = pointItem.PointBatchId
|
||
|
|
LEFT JOIN dbo.HJGL_WeldJoint jot ON jot.WeldJointId = pointItem.WeldJointId
|
||
|
|
LEFT JOIN dbo.HJGL_Pipeline pipe ON pipe.PipelineId = jot.PipelineId
|
||
|
|
LEFT JOIN Base_PipingClass AS pipingClass ON PipingClass.PipingClassId=pipe.PipingClassId
|
||
|
|
LEFT JOIN dbo.HJGL_Batch_BatchTrustItem trustItem ON trustItem.PointBatchItemId = pointItem.PointBatchItemId
|
||
|
|
LEFT JOIN dbo.Base_Material mat ON mat.MaterialId = jot.Material1Id
|
||
|
|
LEFT JOIN dbo.Base_WeldType weldType ON weldType.WeldTypeId = jot.WeldTypeId
|
||
|
|
LEFT JOIN dbo.SitePerson_Person welder ON welder.PersonId = jot.BackingWelderId and welder.ProjectId=point.ProjectId
|
||
|
|
LEFT JOIN dbo.Base_WeldingMethod method ON method.WeldingMethodId = jot.WeldingMethodId
|
||
|
|
LEFT JOIN dbo.Base_DetectionRate rate ON rate.DetectionRateId = point.DetectionRateId
|
||
|
|
WHERE pointItem.PointState IS NOT NULL AND pointItem.CutDate IS NULL
|
||
|
|
AND trustItem.TrustBatchItemId IS NULL
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
GO
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|