USE [SGGLDB_TCC]
GO

/****** Object:  View [dbo].[View_CH_TrustItem]    Script Date: 2022/3/9 12:47:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






--委托明细表查询列表
ALTER VIEW [dbo].[View_CH_TrustItem]
/*委托明细表查询列表*/
AS
SELECT TrustItem.CH_TrustItemID, --委托明细id
		TrustItem.CH_TrustID, -- 委托id
		JointInfo.JOT_ID, --焊口id
		IsoInfo.WorkAreaId, ---施工区域id
		WorkArea.InstallationId, --装置id
		Installation.InstallationName,
		Trust.CH_TrustType,
		Trust.CH_TrustDate,		
		IsoInfo.ISO_IsoNo,
		IsoInfo.ISO_IsoNumber,
		JointInfo.JOT_JointNo,	
		TrustItem.CH_RepairLocation,	
		TrustItem.CH_Remark,
		CAST(JointInfo.JOT_Dia AS decimal(18,2)) as JOT_Dia,
		JointInfo.JOT_Sch,
		JointInfo.WLO_Code,
		case JointInfo.WLO_Code when 'F' then '安装'
		                        when 'S' then '预制'
								else '' end as WLO_CodeStr,
		WeldMethod.WME_Name,
		JointInfo.ProjectId,
		JointInfo.WME_ID,
		Material.MaterialCode as STE_Name1,--材质1
		Material1.MaterialCode as STE_Name2,--材质2
		JointInfo.JOT_JointStatus,
		JointInfo.JOT_JointDesc,
		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 --打底焊工名称
FROM dbo.PW_JointInfo AS JointInfo 
LEFT JOIN dbo.CH_TrustItem AS TrustItem ON JointInfo.JOT_ID = TrustItem.JOT_ID
LEFT JOIN dbo.CH_Trust AS Trust ON Trust.CH_TrustID=TrustItem.CH_TrustID
LEFT JOIN dbo.PW_IsoInfo AS IsoInfo ON JointInfo.ISO_ID = IsoInfo.ISO_ID
LEFT JOIN dbo.ProjectData_WorkArea AS WorkArea ON WorkArea.WorkAreaId = IsoInfo.WorkAreaId
LEFT JOIN dbo.Project_Installation AS Installation ON WorkArea.InstallationId = Installation.InstallationId
LEFT JOIN dbo.BS_WeldMethod AS WeldMethod ON WeldMethod.WME_ID=JointInfo.WME_ID
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









GO