USE [SGGLDB_TCC] GO /****** Object: View [dbo].[View_JointInfo] Script Date: 2021/11/1 21:40:39 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [dbo].[View_JointInfo] AS /********焊口信息列表*********/ select jointInfo.JOT_ID, jointInfo.ProjectId, jointInfo.JOT_JointNo, jointInfo.DReportID, jointInfo.DetectionTypeId, jointInfo.DetectionRateId, is_hj= -----是否焊接 (case isnull(jointInfo.DReportID,'') when '' then '否' else '是' end), jointInfo.JOT_JointStatus, (case jointInfo.JOT_JointStatus when '100' then '正常' when '102' then '扩透' when '101' then '点口' when '104' then '已切除' end) as JointStatusName,---焊口状态 (case jointInfo.JOT_TrustFlag when '01' then '一次委托,未审核' when '02' then '一次委托,已审核' when '11' then '二次委托,未审核' when '12' then '二次委托,已审核' when '21' then '三次委托,未审核' when '22' then '三次委托,已审核' else '' end) as JOT_TrustFlagName,--委托情况 (case jointInfo.JOT_CheckFlag when '01' then '一次检测,未审核' when '02' then '一次检测,已审核' when '11' then '二次检测,未审核' when '12' then '二次检测,已审核' when '21' then '三次检测,未审核' when '22' then '三次检测,已审核' else '' end) as JOT_CheckFlagName,--探伤情况 jointInfo.ISO_ID, isoInfo.ISO_IsoNo, --管线号 isoInfo.ISO_IsoNumber, isoInfo.WorkAreaId, --工区 workArea.WorkAreaCode, --区域 weldReport.JOT_WeldDate, --焊接日期 weldReport.JOT_DailyReportNo, --日报告号 jointInfo.WLO_Code, --焊接区域 case jointInfo.WLO_Code when 'F' then '安装' when 'S' then '预制' else '' end as WLO_CodeStr, --焊接区域 Material.MaterialCode as STE_Name1,--材质1 Material1.MaterialCode as STE_Name2,--材质2 com1.ComponentsName as Component1,--组件1号 com2.ComponentsName as Component2,--组件2号 jointInfo.JOT_CellWelder, --盖面焊工Id cellWelder.WED_Code as WED_Code1, --盖面焊工代号 cellWelder.WED_Name as WED_Name1, --盖面焊工名称 jointInfo.JOT_FloorWelder, --打底焊工Id floorWelder.WED_Code as WED_Code2, --打底焊工代号 floorWelder.WED_Name as WED_Name2, --打底焊工名称 jointInfo.JOT_JointDesc,--焊口规格 jointInfo.JOT_Dia,--外径 jointInfo.JOT_Size,--寸径 jointInfo.JOT_Sch,--壁厚 jointInfo.JOT_FactSch,--实际壁厚 grooveType.GrooveTypeName, --坡口类型 jointInfo.JOTY_ID, weldType.WeldTypeName,--焊缝类型 jointInfo.WME_ID, weldingMethod.WeldingMethodName,--焊接方法 m2.ConsumablesCode as WeldSilk,--焊丝代号 m1.ConsumablesCode as WeldMat,--焊条代号 weldingLocation.WeldingLocationName, --焊接区域 jointInfo.JOT_DoneDin,--完成达因 jointInfo.JOT_PrepareTemp,--预热温度 jointInfo.ActualPrepareTemp,--实际预热温度 jointInfo.JOT_JointAttribute,--焊口属性 jointInfo.JOT_Location, --焊接位置 jointInfo.JOT_CellTemp,--层间温度 jointInfo.JOT_LastTemp,--后热温度 jointInfo.JOT_HeartNo1,--炉批号1 jointInfo.JOT_HeartNo2,--炉批号2 point.PW_PointDate as PointDate,--点口日期 point.PW_PointNo as PointNo,--点口报告号 '' AS CH_TrustCode,--委托编号 '' AS CH_TrustDate,--委托日期 --(select CH_TrustCode from dbo.CH_Trust where CH_TrustType='1' and CH_TrustID in (select CH_TrustID from dbo.CH_TrustItem where JOT_ID=PW_JointInfo.JOT_ID )) as CH_TrustCode, --(select CH_TrustDate from dbo.CH_Trust where CH_TrustType='1' and CH_TrustID in (select CH_TrustID from dbo.CH_TrustItem where JOT_ID=PW_JointInfo.JOT_ID )) as CH_TrustDate, jointInfo.JOT_FaceCheckResult,--外检结果 JOT_FaceCheckDate,--外检日期 jointInfo.JOT_FaceChecker,--外检人员 (case when jointInfo.IS_Proess='1' then '是' else '否' end) as IS_Proess,--是否热处理 jointInfo.JOT_BelongPipe,--所属管段 jointInfo.JOT_Electricity,--焊接电流 jointInfo.JOT_Voltage,--焊接电压 jointInfo.WeldingSpeed, --焊接速度 jointInfo.JOT_ProessDate,--热处理日期 jointInfo.JOT_HotRpt,--热处理报告号 jointInfo.PW_PointID,--点口Id --FloorWelderEDU.TeamGroupName as FloorGroup, --打底班组 --CellWelderEDU.TeamGroupName as CellGroup, --盖面班组 --IS_Compute, --jointInfo.JOT_NDTResult, --JOT_PHWTDate, --JOT_PHWTReportNo, --JOT_PHWTResult, --JOT_JointFlag, --JOT_BecauseJointNo, --JOT_RepairFlag, --JOT_CheckResult, --JOT_CellWeldRules, --JOT_FloorWeldRules, --FloorWeld1.WED_Name as FloorWeld1,--返修1打底工 --CellWelder1.WED_Name as CellWelder1,--返修1盖面工 --FloorWeld2.WED_Name as FloorWeld2,--返修2打底工 --CellWelder2.WED_Name as CellWelder2,--返修1盖面工 --Base_DetectionType.DetectionTypeName as NDT_Name,--探伤类型 --CH_Repair1.CH_RepairNo as RePairNo1, --返修1 --CH_Repair1.CH_RepairDate as RePairDate1, --返修日期1 --CH_Repair2.CH_RepairNo as RePairNo2, --返修2 --CH_Repair2.CH_RepairDate as RePairDate2, --返修日期2 --Fix1_date, --Fix2_date, Base_DetectionRate.DetectionRateValue as NDTR_Name, --探伤比例 --if_dk= --是否点口 -- (case isnull(jointInfo.PW_PointID,'') -- when '' then '0' -- else '1' -- end), jointInfo.Extend_Length, jointInfo.JOT_Remark from PW_JointInfo as jointInfo left join PW_IsoInfo as isoInfo on isoInfo.ISO_ID=jointInfo.ISO_ID left join BO_WeldReportMain as weldReport on weldReport.DReportID=jointInfo.DReportID left join ProjectData_WorkArea as workArea on isoInfo.WorkAreaId=workArea.WorkAreaId left join Base_Material as Material on Material.MaterialId=jointInfo.MaterialId left join Base_Material as Material1 on Material1.MaterialId=jointInfo.MaterialId2 left join BS_Welder as cellWelder on cellWelder.WED_ID= jointInfo.JOT_CellWelder left join BS_Welder as floorWelder on floorWelder.WED_ID=jointInfo.JOT_FloorWelder LEFT Join BS_Welder as floorWeld1 on floorWeld1.WED_ID= jointInfo.Fix1_FloorWeld LEFT Join BS_Welder as floorWeld2 on floorWeld2.WED_ID= jointInfo.Fix2_FloorWeld LEFT join BS_Welder as cellWelder1 on cellWelder1.WED_ID=jointInfo.Fix1_CellWelder LEFT join BS_Welder as cellWelder2 on cellWelder2.WED_ID=jointInfo.Fix2_CellWelder left join Base_WeldingLocation as weldingLocation on weldingLocation.WeldingLocationCode =jointInfo.WLO_Code --left join ProjectData_TeamGroup as FloorWelderEDU on FloorWelderEDU.TeamGroupId=jointInfo.JOT_FloorGroup --left join ProjectData_TeamGroup as CellWelderEDU on CellWelderEDU.TeamGroupId=jointInfo.JOT_CellGroup left join Base_Components as com1 on com1.ComponentsId=jointInfo.JOT_Component1 left join Base_Components as com2 on com2.ComponentsId=jointInfo.JOT_Component2 left join Base_Consumables m1 on m1.ConsumablesId=jointInfo.JOT_WeldMat left join Base_Consumables m2 on m2.ConsumablesId=jointInfo.JOT_WeldSilk left join Base_WeldType as weldType on weldType.WeldTypeId=jointInfo.JOTY_ID left join Base_WeldingMethod as weldingMethod on weldingMethod.WeldingMethodId=jointInfo.WME_ID left join Base_GrooveType as grooveType on grooveType.GrooveTypeId=jointInfo.JST_ID left join BO_Point as point on point.PW_PointID = jointInfo.PW_PointID --left join Base_DetectionType on Base_DetectionType.DetectionTypeId=jointInfo.NDT_ID --left join CH_Repair as CH_Repair1 on CH_Repair1.CH_RepairID = PW_JointInfo.CH_RepairID1 --left join CH_Repair as CH_Repair2 on CH_Repair2.CH_RepairID = PW_JointInfo.CH_RepairID2 left join Base_DetectionRate on Base_DetectionRate.DetectionRateId=isoInfo.DetectionRateId GO