--关闭并结束所有批次（组批条件变化） 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