ALTER VIEW [dbo].[View_Batch_BatchTrust]
AS
/********无损委托********/
SELECT BatchTrust.TrustBatchId, 
BatchTrust.TrustBatchCode, 
(CASE WHEN BatchTrust.TrustType='1' THEN BatchTrust.TrustBatchCode 
      WHEN BatchTrust.TrustType='2' THEN BatchTrust.TrustBatchCode+'K1'
	  WHEN BatchTrust.TrustType='3' THEN BatchTrust.TrustBatchCode+'R1' END) AS NewTrustBatchCode,
BatchTrust.TrustDate, 
BatchTrust.NDEUnit,
BatchTrust.ProjectId, 
project.ProjectCode,
project.ProjectName,
BatchTrust.UnitId, 
BatchTrust.UnitWorkId,
BatchTrust.TrustType,
BatchTrust.DetectionTypeId,
BatchTrust.DetectionRateId,
BatchTrust.IsCheck,
BatchTrust.IsAudit,
BatchTrust.TopointBatch,
BatchTrust.PointBatchId,
Unit.UnitCode,
Unit.UnitName,
UnitWork.UnitWorkCode,
UnitWork.UnitWorkName,
DetectionType.DetectionTypeCode,
detectionRate.DetectionRateValue,
ndtCheck.TrustBatchId AS CheckTrustBatchId
FROM dbo.HJGL_Batch_BatchTrust AS BatchTrust
LEFT JOIN Base_Unit AS Unit ON Unit.UnitId=BatchTrust.UnitId
LEFT JOIN WBS_UnitWork AS UnitWork ON UnitWork.UnitWorkId=BatchTrust.UnitWorkId
LEFT JOIN Base_DetectionType AS DetectionType ON DetectionType.DetectionTypeId=BatchTrust.DetectionTypeId
LEFT JOIN dbo.Base_Project project ON project.ProjectId = BatchTrust.ProjectId
LEFT JOIN dbo.HJGL_Batch_NDE ndtCheck ON ndtCheck.TrustBatchId = BatchTrust.TrustBatchId
left join Base_DetectionRate detectionRate on detectionRate.DetectionRateId = BatchTrust.DetectionRateId
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, 
BatchTrust.TrustBatchCode,  --委托单号
BatchTrust.ProjectId,  
BatchTrust.UnitWorkId,
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,     --点口日期
grooveType.GrooveTypeCode,--坡口形式
BatchTrust.DetectionTypeId
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
left join Base_GrooveType grooveType on grooveType.GrooveTypeId = jot.GrooveTypeId

GO