186 lines
6.9 KiB
Transact-SQL
186 lines
6.9 KiB
Transact-SQL
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
|
|
|
|
|