224 lines
9.0 KiB
MySQL
224 lines
9.0 KiB
MySQL
|
--关闭并结束所有批次（组批条件变化）
|
|||
|
|
update Batch_PointBatch set EndDate=GETDATE() where EndDate is null
|
|||
|
|
GO
|
|||
|
|
update Batch_PointBatch set ClearDate=GETDATE() where ClearDate is null
|
|||
|
|
GO
|
|||
|
|
|
|||
|
|
INSERT INTO dbo.Sys_Menu VALUES
|
|||
|
|
( '9E3834DC-ECA6-41CF-AFDB-307AEDE69EBB', '焊口业务未处理清单', 'WeldInfo No Handle List','PublicInfo/ProcessControl/WeldNoHandle.aspx',80 ,'083CE783-E1E0-46F3-9DFB-54E17A16AFD3','2',NULL)
|
|||
|
|
|
|||
|
|
INSERT INTO dbo.Sys_ButtonToMenu VALUES(NEWID(),'9E3834DC-ECA6-41CF-AFDB-307AEDE69EBB','查看','See',5)
|
|||
|
|
GO
|
|||
|
|
|
|||
|
|
--delete from Sys_ButtonToMenu where MenuId='C0AF6083-0535-4D5F-99EB-CC51F4A45AC0'
|
|||
|
|
--delete from Sys_Menu where MenuId='C0AF6083-0535-4D5F-99EB-CC51F4A45AC0'
|
|||
|
|
--GO
|
|||
|
|
INSERT INTO dbo.Sys_Menu VALUES
|
|||
|
|
( 'C0AF6083-0535-4D5F-99EB-CC51F4A45AC0', 'NDE预点口统计表', 'Pro Point weld List','WeldingProcess/WeldingReport/ProPointWeldList.aspx',155 ,'F3B157B7-9BEE-4150-80CB-087828F1C51D','3',NULL)
|
|||
|
|
|
|||
|
|
INSERT INTO dbo.Sys_ButtonToMenu VALUES(NEWID(),'C0AF6083-0535-4D5F-99EB-CC51F4A45AC0','查看','See',5)
|
|||
|
|
GO
|
|||
|
|
|
|||
|
|
--是否压力管道
|
|||
|
|
ALTER TABLE dbo.Pipeline_Pipeline ADD IsPressurePipe BIT NULL
|
|||
|
|
GO
|
|||
|
|
|
|||
|
|
--组批增加按区域
|
|||
|
|
ALTER TABLE dbo.Batch_PointBatch ADD JointArea nvarchar(10) NULL
|
|||
|
|
GO
|
|||
|
|
|
|||
|
|
--项目类型代号
|
|||
|
|
ALTER TABLE dbo.Base_ProjectType ADD ProjectTypeCode nvarchar(50) NULL
|
|||
|
|
GO
|
|||
|
|
|
|||
|
|
ALTER VIEW [dbo].[View_Pipeline_Pipeline]
|
|||
|
|
AS
|
|||
|
|
/*************管线视图*****************/
|
|||
|
|
SELECT pipeline.PipelineId,
|
|||
|
|
pipeline.ProjectId,
|
|||
|
|
pipeline.InstallationId,
|
|||
|
|
pipeline.UnitId,
|
|||
|
|
pipeline.WorkAreaId,
|
|||
|
|
pipeline.PipelineCode,
|
|||
|
|
pipeline.SingleNumber,
|
|||
|
|
pipeline.PipingClassId,
|
|||
|
|
pipeline.MainMaterialId,
|
|||
|
|
pipeline.MediumId,
|
|||
|
|
pipeline.Specification,
|
|||
|
|
pipeline.SystemNumber,
|
|||
|
|
pipeline.SubSystemNumber,
|
|||
|
|
pipeline.WorkPackageCode,
|
|||
|
|
pipeline.TestPackageCode,
|
|||
|
|
pipeline.PipelineLength,
|
|||
|
|
pipeline.Sheet,
|
|||
|
|
pipeline.PipeSegment,
|
|||
|
|
pipeline.DrawingsNum,
|
|||
|
|
pipeline.PaintingCategory,
|
|||
|
|
pipeline.AdiabaticCategory,
|
|||
|
|
pipeline.DesignPressure,
|
|||
|
|
pipeline.DesignTemperature,
|
|||
|
|
pipeline.TestPressure,
|
|||
|
|
pipeline.TestTemperature,
|
|||
|
|
pipeline.ModifierId,
|
|||
|
|
pipeline.ModifyDate,
|
|||
|
|
pipeline.CreatorId,
|
|||
|
|
pipeline.CreateDate,
|
|||
|
|
pipeline.PenetrationRatio,
|
|||
|
|
pipeline.PenetrationClass,
|
|||
|
|
pipeline.IfPickling,
|
|||
|
|
pipeline.IfChasing,
|
|||
|
|
pipeline.Remark,
|
|||
|
|
pipeline.IsPressurePipe,
|
|||
|
|
pipingClass.PipingClassCode,
|
|||
|
|
case pipeline.IfPickling when 1 then '是' else '否' end as IfPicklingStr,
|
|||
|
|
CAST((select SUM(ISNULL(Size,0)) from dbo.Pipeline_WeldJoint where PipelineId=pipeline.PipelineId) AS DECIMAL(19,4)) AS TotalDin,
|
|||
|
|
(select Count(WeldJointId) from dbo.Pipeline_WeldJoint where PipelineId=pipeline.PipelineId) AS JointCount,
|
|||
|
|
unit.UnitName,
|
|||
|
|
workArea.WorkAreaCode,
|
|||
|
|
material.MaterialCode,
|
|||
|
|
medium.MediumCode
|
|||
|
|
FROM Pipeline_Pipeline AS pipeline
|
|||
|
|
LEFT JOIN Base_PipingClass AS pipingClass ON pipingClass.PipingClassId=pipeline.PipingClassId
|
|||
|
|
LEFT JOIN dbo.Base_Unit AS unit on unit.UnitId=pipeline.UnitId
|
|||
|
|
LEFT JOIN Project_WorkArea AS workArea on workArea.WorkAreaId=pipeline.WorkAreaId
|
|||
|
|
LEFT JOIN Base_Material AS material on material.MaterialId=pipeline.MainMaterialId
|
|||
|
|
LEFT JOIN Base_Medium AS medium on medium.MediumId=pipeline.MediumId
|
|||
|
|
|
|||
|
|
GO
|
|||
|
|
|
|||
|
|
|
|||
|
|
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,
|
|||
|
|
BatchTrustItem.IsCancelTrust,
|
|||
|
|
BatchTrustItem.CancelReason,
|
|||
|
|
BatchTrustItem.Remark,
|
|||
|
|
BatchTrust.TrustBatchCode, --委托单号
|
|||
|
|
BatchTrust.TrustDate,
|
|||
|
|
BatchTrust.ProjectId,
|
|||
|
|
ins.InstallationCode, --装置
|
|||
|
|
area.WorkAreaCode, --工区号
|
|||
|
|
pipe.PipelineCode, --管线号
|
|||
|
|
pipingClass.PipingClassCode, --管线等级
|
|||
|
|
jot.WeldJointCode, --焊口号
|
|||
|
|
(CASE WHEN jot.Material1Id!=jot.Material1Id THEN mat1.MaterialCode+'/'+mat2.MaterialCode ELSE mat1.MaterialCode END) AS MaterialCode, --材质
|
|||
|
|
(CASE WHEN jot.WeldingLocationId!='' AND jot.WeldingLocationId IS NOT NULL THEN jot.JointArea+'-'+loc.WeldingLocationCode
|
|||
|
|
ELSE jot.JointArea END) AS JointArea, --焊接区域
|
|||
|
|
(CASE WHEN jot.CoverWelderId!=jot.BackingWelderId THEN welderb.WelderCode+'/'+welderc.WelderCode ELSE welderb.WelderCode END) AS WelderCode, --焊工号
|
|||
|
|
--welder.WelderCode AS WelderCode, --焊工号
|
|||
|
|
weldType.WeldTypeCode, --焊缝类型
|
|||
|
|
jot.Dia, --外径
|
|||
|
|
jot.Size, --寸径
|
|||
|
|
jot.Thickness, --壁厚
|
|||
|
|
method.WeldingMethodCode, --焊接方法
|
|||
|
|
rate.DetectionRateCode+'%' AS DetectionRateCode,
|
|||
|
|
PointBatchItem.PointDate, --点口日期
|
|||
|
|
PointBatchItem.AcceptLevel, --合格等级
|
|||
|
|
pipe.Sheet, --页数
|
|||
|
|
PipingClass.PNO
|
|||
|
|
FROM Batch_BatchTrustItem AS BatchTrustItem
|
|||
|
|
LEFT JOIN dbo.Batch_BatchTrust AS BatchTrust ON BatchTrust.TrustBatchId=BatchTrustItem.TrustBatchId
|
|||
|
|
LEFT JOIN Batch_PointBatchItem AS PointBatchItem ON PointBatchItem.PointBatchItemId=BatchTrustItem.PointBatchItemId
|
|||
|
|
LEFT JOIN dbo.Batch_PointBatch point ON point.PointBatchId = PointBatchItem.PointBatchId
|
|||
|
|
LEFT JOIN dbo.Base_DetectionRate rate ON rate.DetectionRateId = point.DetectionRateId
|
|||
|
|
LEFT JOIN dbo.Pipeline_WeldJoint jot ON jot.WeldJointId = BatchTrustItem.WeldJointId
|
|||
|
|
LEFT JOIN dbo.Pipeline_Pipeline pipe ON pipe.PipelineId = jot.PipelineId
|
|||
|
|
LEFT JOIN Base_PipingClass AS pipingClass ON PipingClass.PipingClassId=pipe.PipingClassId
|
|||
|
|
LEFT JOIN dbo.Project_Installation ins ON ins.InstallationId = BatchTrust.InstallationId
|
|||
|
|
LEFT JOIN dbo.Project_WorkArea area ON pipe.WorkAreaId=area.WorkAreaId
|
|||
|
|
LEFT JOIN dbo.Base_WeldType weldType ON weldType.WeldTypeId = jot.WeldTypeId
|
|||
|
|
LEFT JOIN dbo.Welder_Welder welderb ON welderb.WelderId = jot.BackingWelderId
|
|||
|
|
LEFT JOIN dbo.Welder_Welder welderc ON welderc.WelderId = jot.CoverWelderId
|
|||
|
|
LEFT JOIN dbo.Base_Material mat1 ON mat1.MaterialId = jot.Material1Id
|
|||
|
|
LEFT JOIN dbo.Base_Material mat2 ON mat2.MaterialId = jot.Material2Id
|
|||
|
|
LEFT JOIN dbo.Base_WeldingMethod method ON method.WeldingMethodId = jot.WeldingMethodId
|
|||
|
|
LEFT JOIN dbo.Base_WeldingLocation loc ON loc.WeldingLocationId = jot.WeldingLocationId
|
|||
|
|
|
|||
|
|
|
|||
|
|
GO
|
|||
|
|
|
|||
|
|
|
|||
|
|
ALTER VIEW [dbo].[View_Batch_PointBatchItem]
|
|||
|
|
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, --管道等级
|
|||
|
|
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_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
|
|||
|
|
|
|||
|
|
|
|||
|
|
|