57 lines
3.2 KiB
MySQL
57 lines
3.2 KiB
MySQL
|
|
|||
|
|
ALTER VIEW [dbo].[View_Batch_PointBatchItem2]
|
|||
|
|
AS
|
|||
|
|
/*************点口明细表*************/
|
|||
|
|
SELECT PointBatchItem.PointBatchItemId,
|
|||
|
|
PointBatchItem.PointBatchId,
|
|||
|
|
PointBatchItem.WeldJointId,
|
|||
|
|
mat.MaterialCode,
|
|||
|
|
(CASE PointBatchItem.PointState WHEN '1' THEN '点口' WHEN '2' THEN '扩透' END) AS PointState,
|
|||
|
|
PointBatchItem.PointDate,--点口日期
|
|||
|
|
PointBatchItem.RepairDate,--返修日期
|
|||
|
|
PointBatchItem.CutDate,--切除日期
|
|||
|
|
WorkArea.WorkAreaCode,--工区号
|
|||
|
|
WeldJoint.WeldJointCode,--焊口号
|
|||
|
|
WeldJoint.JointArea,--焊接区域
|
|||
|
|
WeldJoint.Size,--实际寸径
|
|||
|
|
WeldJoint.IsCancel,--是否取消
|
|||
|
|
WeldingDaily.WeldingDate,--焊接日期
|
|||
|
|
Pipeline.PipelineCode, --管线号
|
|||
|
|
Pipeline.IsPressurePipe,--是否压力管道
|
|||
|
|
PipingClass.PipingClassName, --管道等级
|
|||
|
|
(select COUNT(1) from dbo.Batch_NDEItem where TrustBatchItemId in (select TrustBatchItemId from dbo.Batch_BatchTrustItem
|
|||
|
|
where PointBatchItemId=PointBatchItem.PointBatchItemId)) AS CheckNum, --检测数
|
|||
|
|
PointBatchItem.Remark
|
|||
|
|
FROM Batch_PointBatchItem AS PointBatchItem
|
|||
|
|
LEFT JOIN Batch_PointBatch AS PointBatch ON PointBatch.PointBatchId=PointBatchItem.PointBatchId
|
|||
|
|
LEFT JOIN Pipeline_WeldJoint AS WeldJoint ON WeldJoint.WeldJointId=PointBatchItem.WeldJointId
|
|||
|
|
LEFT JOIN Pipeline_Pipeline AS Pipeline ON Pipeline.PipelineId=WeldJoint.PipelineId
|
|||
|
|
LEFT JOIN Project_WorkArea AS WorkArea ON WorkArea.WorkAreaId=Pipeline.WorkAreaId
|
|||
|
|
LEFT JOIN Pipeline_WeldingDaily AS WeldingDaily ON WeldingDaily.WeldingDailyId=WeldJoint.WeldingDailyId
|
|||
|
|
LEFT JOIN Base_PipingClass AS PipingClass ON PipingClass.PipingClassId=Pipeline.PipingClassId
|
|||
|
|
LEFT JOIN dbo.Base_Material mat ON mat.MaterialId = WeldJoint.Material1Id
|
|||
|
|
|
|||
|
|
|
|||
|
|
GO
|
|||
|
|
|
|||
|
|
|
|||
|
|
CREATE VIEW [dbo].[View_Pre_PointWeldList]
|
|||
|
|
AS
|
|||
|
|
select trust.ProjectId, trust.TrustBatchCode,pipe.PipelineCode,jot.WeldJointCode,
|
|||
|
|
t.PipelineCode as PrePipelineCode,t.WeldJointCode as PreWeldJointCode
|
|||
|
|
from dbo.Batch_BatchTrustItem trustItem
|
|||
|
|
LEFT JOIN dbo.Batch_BatchTrust trust on trust.TrustBatchId = trustItem.TrustBatchId
|
|||
|
|
LEFT JOIN Batch_PointBatchItem pointItem ON pointItem.PointBatchItemId= trustItem.PointBatchItemId
|
|||
|
|
LEFT JOIN Batch_PointBatch point ON point.PointBatchId= pointItem.PointBatchId
|
|||
|
|
LEFT JOIN dbo.Pipeline_WeldJoint jot ON jot.WeldJointId = trustItem.WeldJointId
|
|||
|
|
LEFT JOIN dbo.Pipeline_Pipeline pipe ON pipe.PipelineId = jot.PipelineId
|
|||
|
|
LEFT JOIN
|
|||
|
|
(SELECT pipe.PipelineCode,jot.WeldJointCode,batchItem.PointBatchId
|
|||
|
|
FROM dbo.Batch_PointBatchItem batchItem
|
|||
|
|
LEFT JOIN dbo.Pipeline_WeldJoint jot ON jot.WeldJointId = batchItem.WeldJointId
|
|||
|
|
LEFT JOIN dbo.Pipeline_Pipeline pipe ON pipe.PipelineId = jot.PipelineId
|
|||
|
|
WHERE batchItem.PointState IS NULL
|
|||
|
|
AND (jot.IsCancel=0 OR jot.IsCancel is null)
|
|||
|
|
AND batchItem.CutDate IS NULL)t on t.PointBatchId=point.PointBatchId
|
|||
|
|
WHERE pointItem.CutDate IS NULL and t.PipelineCode IS NOT NULL and t.WeldJointCode IS NOT NULL
|
|||
|
|
GO
|