UPDATE dbo.Batch_PointBatchItem SET PointState=NULL,PointDate=NULL,
 JLAudit=NULL,GLGSAudit=NULL,QTAudit=NULL
 WHERE PointState='1' AND 
 PointBatchItemId NOT IN(SELECT PointBatchItemId FROM dbo.Batch_BatchTrustItem)
 GO

 UPDATE dbo.Batch_PointBatchItem SET IsCompletedPoint=1
 GO

alter table PTP_PipelineList add WorkAreaId nvarchar(50)
alter table PTP_PipelineList add isAll bit
alter table PTP_PipelineList add WeldJonintCode nvarchar(max)
GO


ALTER VIEW [dbo].[View_Batch_PointBatchItem]
AS
/*************点口明细表*************/
SELECT PointBatchItem.PointBatchItemId,
PointBatchItem.PointBatchId,
PointBatch.ProjectId,
PointBatch.InstallationId,
PointBatch.UnitId,
PointBatch.PointBatchCode,
PointBatch.DetectionTypeId,
Pipeline.WorkAreaId,
PointBatchItem.WeldJointId,
PointBatchItem.PointState AS PState,
(CASE PointBatchItem.PointState WHEN '1' THEN '点口' WHEN '2' THEN '扩透' END) AS PointState,
--(CASE PointBatchItem.IsAudit WHEN 1 THEN '是' ELSE '否' END) AS PointIsAudit,
--(CASE WHEN PointBatchItem.IsAudit=1 THEN '是' 
--      WHEN (PointBatchItem.IsAudit IS NULL OR PointBatchItem.IsAudit=0) AND PointBatchItem.PointState IS NOT NULL THEN '否'
--      WHEN (PointBatchItem.IsAudit IS NULL OR PointBatchItem.IsAudit=0) AND PointBatchItem.PointState IS  NULL THEN '' END) AS PointIsAudit,
PointBatchItem.PointDate,--点口日期
PointBatchItem.RepairDate,--返修日期
PointBatchItem.CutDate,--切除日期
PointBatchItem.RepairRecordId,
PointBatchItem.IsBuildTrust,--是否委托
WorkArea.WorkAreaCode,--工区号
jot.WeldJointCode,--焊口号
jot.BackingWelderId AS WelderId, --焊工ID
jot.JointArea,--焊接区域
jot.Dia,
jot.Size,--实际寸径
WeldingDaily.WeldingDate,--焊接日期
jot.PipelineId, --管线ID
jot.Material1Id AS Mat, --材质
jot.Specification,  --规格
jot.JointAttribute,
weldType.Flag,     --焊缝类型
Pipeline.PipelineCode, --管线号
PipingClass.PipingClassName, --管道等级
PointBatchItem.IsWelderFirst,
PointBatchItem.IsCompletedPoint,
trust.TrustBatchItemId,
(CASE WHEN PointBatchItem.JLAudit IS NOT NULL THEN userJL.UserName ELSE (CASE WHEN PointBatchItem.PointState IS NOT NULL THEN '未审核' ELSE '' END)  END) AS JLAudit,
(CASE WHEN PointBatchItem.GLGSAudit IS NOT NULL THEN userGLGS.UserName ELSE (CASE WHEN PointBatchItem.PointState IS NOT NULL THEN '未审核' ELSE '' END)  END) AS GLGSAudit,
(CASE WHEN PointBatchItem.QTAudit IS NOT NULL THEN userQT.UserName ELSE (CASE WHEN PointBatchItem.PointState IS NOT NULL THEN '未审核' ELSE '' END)  END) AS QTAudit,
(CASE WHEN jot.BackingWelderId!=jot.CoverWelderId THEN backingWelder.WelderCode+'/'+coverWelder.WelderCode ELSE backingWelder.WelderCode END) AS WelderCode, --焊工号
(CASE WHEN PointBatchItem.JLAudit IS NOT NULL AND PointBatchItem.GLGSAudit IS NOT NULL THEN 1 ELSE 0 END) AS IsPointAudit
FROM Batch_PointBatchItem AS PointBatchItem
LEFT JOIN Batch_PointBatch AS PointBatch ON PointBatch.PointBatchId=PointBatchItem.PointBatchId
LEFT JOIN dbo.Batch_BatchTrustItem trust ON trust.PointBatchItemId = PointBatchItem.PointBatchItemId
LEFT JOIN Pipeline_WeldJoint AS jot ON jot.WeldJointId=PointBatchItem.WeldJointId
LEFT JOIN Pipeline_Pipeline AS Pipeline ON Pipeline.PipelineId=jot.PipelineId
LEFT JOIN dbo.Base_WeldType weldType ON weldType.WeldTypeId = jot.WeldTypeId
LEFT JOIN dbo.Welder_Welder backingWelder ON backingWelder.WelderId = jot.BackingWelderId
LEFT JOIN dbo.Welder_Welder coverWelder ON coverWelder.WelderId = jot.CoverWelderId
LEFT JOIN Project_WorkArea AS WorkArea ON WorkArea.WorkAreaId=Pipeline.WorkAreaId
LEFT JOIN Pipeline_WeldingDaily AS WeldingDaily ON WeldingDaily.WeldingDailyId=jot.WeldingDailyId
LEFT JOIN Base_PipingClass AS PipingClass ON PipingClass.PipingClassId=Pipeline.PipingClassId
LEFT JOIN dbo.Sys_User userJL ON userJL.UserId = PointBatchItem.JLAudit
LEFT JOIN dbo.Sys_User userGLGS ON userGLGS.UserId = PointBatchItem.GLGSAudit
LEFT JOIN dbo.Sys_User userQT ON userQT.UserId = PointBatchItem.QTAudit


GO




CREATE VIEW [dbo].[View_TrustBathcIn] 
AS
/*用于导入数据时，查询焊口信息*/
SELECT 
	pitem.PointBatchItemId,pitem.PointBatchId,jot.ProjectId
	,pipeline.InstallationId,ins.InstallationCode
	,pipeline.WorkAreaId,workArea.WorkAreaCode
	,jot.PipelineId,pipeline.PipelineCode
	,pitem.WeldJointId,jot.WeldJointCode
	,point.DetectionTypeId,nde.DetectionTypeCode
	,pitem.IsWelderFirst,trust.TrustBatchItemId
FROM dbo.Batch_PointBatchItem AS pitem
LEFT JOIN dbo.Batch_PointBatch point ON point.PointBatchId = pitem.PointBatchId
LEFT JOIN Pipeline_WeldJoint jot ON jot.WeldJointId = pitem.WeldJointId
LEFT JOIN Pipeline_Pipeline AS pipeline ON jot.PipelineId = pipeline.PipelineId
LEFT JOIN dbo.Project_WorkArea AS workArea ON workArea.WorkAreaId=pipeline.WorkAreaId
LEFT JOIN dbo.Project_Installation ins ON ins.InstallationId = workArea.InstallationId
LEFT JOIN dbo.Base_DetectionType nde ON nde.DetectionTypeId = point.DetectionTypeId
LEFT JOIN dbo.Batch_BatchTrustItem trust ON trust.PointBatchItemId = pitem.PointBatchItemId



GO