/****** Object:  View [dbo].[View_IsoinfoView]    Script Date: 2021/9/6 19:32:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



/*==============================================================*/
/* VIEW: V_IsoinfoView  管线综合信息视图                           */
/*==============================================================*/
ALTER VIEW [dbo].[View_IsoinfoView] AS
SELECT  pw_isoinfo.ISO_ID,
        pw_isoinfo.ProjectId,
		PW_IsoInfo.Is_Standard,
		Base_Unit.UnitName,
		pw_isoinfo.iso_isono,
		jot_count,
		TotalDin.ISO_TotalDin,	
		testMedium.MediumName,
		detectionRate.DetectionRate,
		detectionType.DetectionTypeName,
		pw_isoinfo.ISO_NDTClass,
		material.MaterialType,
		pw_isoinfo.ISO_Specification,
		pw_isoinfo.ISO_DesignPress,
		pw_isoinfo.ISO_DesignTemperature,
		pw_isoinfo.ISO_TestPress,
		pw_isoinfo.ISO_TestTemperature,
		workArea.WorkAreaCode,
		workArea.WorkAreaId,
		pw_isoinfo.ISO_SysNo,
		pw_isoinfo.ISO_SubSysNo,
		pw_isoinfo.ISO_CwpNo,
		pw_isoinfo.ISO_IsoNumber,
		workArea.SupervisorUnitId,
		--(SELECT TOP 1 IS_Proess FROM pw_jointinfo WHERE iso_id=pw_isoinfo.iso_id) AS is_proess,
		(case when IS_Proess.IS_Proess='True' THEN '是' ELSE '否' END)as is_proess,		
		pack.PTP_TestPackageNo,
	    CONVERT(VARCHAR(10),pack.PTP_TableDate,23) AS PTP_TableDate
FROM pw_isoinfo
	LEFT JOIN Base_Unit ON Base_Unit.UnitId = pw_isoinfo.UnitId
	LEFT JOIN Base_TestMedium as testMedium ON testMedium.TestMediumId=pw_isoinfo.TestMediumId
	LEFT JOIN Base_DetectionRate as detectionRate ON detectionRate.DetectionRateId=pw_isoinfo.DetectionRateId
	LEFT JOIN Base_DetectionType as detectionType ON detectionType.DetectionTypeId=pw_isoinfo.DetectionTypeId
	LEFT JOIN Base_Material as material ON material.MaterialId = pw_isoinfo.MaterialId
	LEFT JOIN ProjectData_WorkArea as workArea ON workArea.WorkAreaId = pw_isoinfo.WorkAreaId	
    LEFT JOIN dbo.TP_IsoList tp ON tp.ISO_ID=pw_isoinfo.ISO_ID
    LEFT JOIN dbo.TP_TestPackage pack ON pack.PTP_ID=tp.PTP_ID
	LEFT JOIN (SELECT COUNT(*) AS jot_count,iso_id FROM dbo.PW_JointInfo GROUP BY ISO_ID) AS jointCount ON jointCount.ISO_ID = pw_isoinfo.ISO_ID
	LEFT JOIN (SELECT CAST(SUM(JOT_Size)AS DECIMAL(19,2)) AS ISO_TotalDin,ISO_ID FROM dbo.PW_JointInfo GROUP BY ISO_ID) AS TotalDin ON TotalDin.ISO_ID = pw_isoinfo.ISO_ID 
	LEFT JOIN (SELECT top 1 IS_Proess,ISO_ID FROM dbo.PW_JointInfo GROUP BY ISO_ID,IS_Proess) AS IS_Proess ON IS_Proess.ISO_ID = pw_isoinfo.ISO_ID


GO