/****** Object:  View [dbo].[HJGL_View_ElectrodeRecovery]    Script Date: 2021/9/11 21:45:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER VIEW [dbo].[HJGL_View_ElectrodeRecovery]
/*焊接耗材发放回收记录列表视图*/
AS
SELECT 
	Main.ElectrodeRecoveryId,
	Item.ElectrodeRecoveryItemID,
	Main.CompileDate,
	Main.ElectrodeRecoveryCode,
	Main.ElectrodeRecoveryDate,
	Item.WelderCode, 
	Welder.WED_Name,
	Unit.UnitName,
	Project.ProjectName,
	Installation.InstallationName,
	(CASE Item.CnProfession WHEN '1' THEN '管道安装工程' WHEN '2' THEN '钢结构安装工程' WHEN '3' THEN '仪表安装工程' END) AS CnProfession,
	(CASE Item.Equipment WHEN '1' THEN '管道' WHEN '2' THEN '钢结构' WHEN '3' THEN '仪表' END) AS Equipment,
	WeldMaterial.ConsumablesName as WMT_MatName,
	steel.STE_Name,
	(CASE WeldMaterial.ConsumablesType WHEN '1' THEN 'GTAW' WHEN '2' THEN 'SMAW' ELSE '' END) AS WMT_MatType,
	Item.Specifications,
	Item.RecipientsCount,
	Item.RecoveryCount,
	Main.AuditDate,
	RoleAuditManUser.UserName AS RoleAuditManName,
	AuditManUser.UserName AS AuditManName,
	Main.ProjectId,Main.AuditMan
FROM dbo.HJGL_ElectrodeRecovery AS Main
LEFT JOIN dbo.HJGL_ElectrodeRecoveryItem AS Item ON Item.ElectrodeRecoveryId = Main.ElectrodeRecoveryId
LEFT JOIN dbo.Sys_User AS AuditManUser ON Main.AuditMan = AuditManUser.UserId
LEFT JOIN dbo.Sys_User AS RoleAuditManUser ON Main.RoleAuditMan = RoleAuditManUser.UserId
LEFT JOIN BS_Welder AS Welder ON Welder.WED_Code = Item.WelderCode and Welder.ProjectId=Main.ProjectId
LEFT JOIN Base_Consumables AS WeldMaterial ON WeldMaterial.ConsumablesId = Item.WMT_ID
LEFT JOIN Base_Unit AS Unit ON Unit.UnitId = Welder.WED_Unit
LEFT JOIN dbo.Base_Project AS Project ON Project.ProjectId = Main.ProjectId 
LEFT JOIN dbo.Project_Installation AS Installation ON Installation.InstallationId = Item.InstallationId
LEFT JOIN dbo.BS_Steel steel ON steel.STE_ID=Item.WeldingMaterial


GO