-- =============================================
-- Author:		Mark Hausmann
-- Create date: 17.01.2019
--              25.01.2019 Mit RP3 erweitert, gibt mit Priorität RPZ2 zurück und wenn nicht vorhanden dann RPZ1
--   
-- Description: FMEA Top 10 RPZ Pareto Auswertung
--
-- =============================================
--
DROP PROCEDURE sp_PDAP7_5_FMEA_RPZ_Pareto
GO
CREATE PROCEDURE sp_PDAP7_5_FMEA_RPZ_Pareto( @Top int = 10, @FMEA_ID int = NULL ) AS
BEGIN

DECLARE @Top_ int = @Top
DECLARE @FMEA_ID_ int = @FMEA_ID  -- 1450
---- Test
--DECLARE @Top_ int = 10
--DECLARE @FMEA_ID_ int = NULL -- 22 -- '1450'

DECLARE @APQP_FMEA_LIMIT_GREEN int = (SELECT TOP 1 SYS_PAR_VALUE FROM SYS_PARAM WHERE SYS_PAR_BEZ = 'APQP_FMEA_LIMIT_GREEN')
DECLARE @APQP_FMEA_LIMIT_YELLOW int = (SELECT TOP 1 SYS_PAR_VALUE FROM SYS_PARAM WHERE SYS_PAR_BEZ = 'APQP_FMEA_LIMIT_YELLOW')
DECLARE @SysInfFooter varchar(100) = (SELECT MIN(SYS_PAR_VALUE) FROM SYS_PARAM WHERE SYS_PAR_BEZ = 'SYS_INF_FORM_FUSSZEILE')

;WITH cFMEA AS (
SELECT
	FMEA_ID,
	FMEA_NR = FMEA_NR + ISNULL('-'+CAST(FMEA_REV AS varchar(10)),''),
	FMEA_DATUM = CONVERT(varchar(100),FMEA_GEPL_AB_DATUM,104),
	FFNK_NR,
	FFNK_BEZ,	
	FE_NR = ISNULL(FE_NR,FFE_FE_BEZ),
	FE_NAME = ISNULL(FE_NAME,FFE_FE_BEZ),
	FFLG_BEZ,
	FFE_B,
	FFE_FE_KLASSE,
	FFE_FE_KLASSE2 = CASE FFE_FE_KLASSE WHEN 1 THEN  'Key Control' WHEN 2 THEN  'Major' WHEN 3 THEN 'Significant' WHEN 4 THEN 'Critical' WHEN 5 THEN 'Pass Through' WHEN 6 THEN 'Process'  ELSE '' END,	 
	FUR_BEZ,
	FUR_A,
	FAMASS_MASS_BEZ,
	FUR_E,
	RPZ = CASE WHEN (FMASS_A2 * FMASS_B2 * FMASS_E2) > 0 THEN (FMASS_A2 * FMASS_B2 * FMASS_E2) ELSE (FFE_B * FUR_A * FUR_E) END,
	FMASS_EMPF_BEZ,
	MA_NAME,
	FMASS_ERL_TERMIN = CONVERT(varchar(100),FMASS_ERL_TERMIN,104) ,
	FMASS_ERL_TERMIN2 = FMASS_ERL_TERMIN,
	FMASS_GETR_BEZ,
	FMASS_A2,  
	FMASS_B2,  
	FMASS_E2,
	RPZ2 = FMASS_A2 * FMASS_B2 * FMASS_E2,
	FMASS_STAND,
	FMASS_STAND2 = CONVERT(varchar(100),FMASS_STAND,104),
	FMASS_STATUS,
	FMASS_STATUS2 = CASE FMASS_STATUS WHEN 1 THEN  'Unbearbeitet' WHEN 2 THEN  'In Entscheidung' WHEN 3 THEN 'In Umsetzung' WHEN 4 THEN 'Abgeschlossen' WHEN 5 THEN 'Verworfen'  ELSE '' END,
	EZ_NR,
	EZ_NR_INDEX
	--FMEA_NR_DATUM = FMEA_NR + ISNULL(' (' + CONVERT(varchar(100),FMEA_GEPL_AB_DATUM,104)+')',''), 
	--FFNK_NR_BEZ = FFNK_NR + ISNULL('-' + FFNK_BEZ,''), 
	--FE_NR_NAME = FE_NR + ISNULL('-' + FE_NAME,''),
	--EZ_NR_NAME = EZ_NR + ISNULL('-' + EZ_NAME,''),
	--FFLG_BEZ
	FROM FMEA_STM 
	INNER JOIN FMEA_FNK ON FFNK_FMEA_ID = FMEA_ID
	INNER JOIN FMEA_FE ON FFE_FFNK_ID = FFNK_ID AND FFE_FMEA_ID = FMEA_ID
	INNER JOIN EZ_STM ON EZ_ID = FMEA_EZ_ID
	INNER JOIN FMEA_FLG ON FFLG_FFE_ID = FFE_ID AND FFLG_FMEA_ID = FMEA_ID	
	LEFT OUTER JOIN FE_STM ON FE_ID = FFE_FE_ID 
	LEFT OUTER JOIN FMEA_UR ON FUR_FFE_ID = FFE_ID AND FUR_FMEA_ID = FMEA_ID
	LEFT OUTER JOIN FMEA_AKT_MASS ON FAMASS_FUR_ID = FUR_ID AND FAMASS_FMEA_ID = FMEA_ID
	LEFT OUTER JOIN FMEA_MASS ON FMASS_FUR_ID = FUR_ID AND FMASS_FMEA_ID = FMEA_ID
	LEFT OUTER JOIN MA ON MA_ID = FMASS_ERL_MA_ID
	WHERE FMEA_ID = ISNULL(@FMEA_ID_,FMEA_ID)
	AND FMEA_STATUS = '1'
), cFMEA2 AS (
SELECT TOP ( @Top_ ) * FROM cFMEA ORDER BY RPZ DESC 
)
-- SELECT * FROM cFMEA2
, cRPZ_Summe AS (
SELECT RPZ_Summe = CAST( ( SELECT SUM( RPZ ) FROM cFMEA2 ) AS float )
)
SELECT TOP ( @Top_ )
Nr = ROW_NUMBER() OVER (Order by (SELECT 100)),
FEMA = FMEA_NR,
Farbe = CASE WHEN RPZ >= @APQP_FMEA_LIMIT_YELLOW THEN 'red' WHEN RPZ >= @APQP_FMEA_LIMIT_GREEN THEN 'yellow' ELSE 'green' END,
Erzeugnis = EZ_NR + ISNULL('-'+CAST(EZ_NR_INDEX AS varchar(10)),''),
RPZ_Prozent = ( RPZ / ( SELECT RPZ_Summe FROM cRPZ_Summe ) ) * 100,
RPZ,
B = FFE_B,
A = FUR_A,
E = FUR_E,
Funktion = FFNK_BEZ,
Fehler = FE_NAME,
Klasse = FFE_FE_KLASSE2,
Ursache = FUR_BEZ,
Akt_Massnahme = FAMASS_MASS_BEZ,--       FMASS_GETR_BEZ, 
Getr_Massnahme = FMASS_EMPF_BEZ,
FFNK_NR,
RPZ_Summe = ( SELECT RPZ_Summe FROM cRPZ_Summe ),
Datum = GETDATE(),
SYS_INF_FORM_FUSSZEILE = @SysInfFooter
--* 
FROM cFMEA2 ORDER BY RPZ DESC
--	WHERE YEAR(ISNULL(FMEA_GEPL_AB_DATUM,FMEA_ANLEGE_DATUM)) = @FMEA_JAHR 
--	AND (      (FMEA_NR + ISNULL('-'+CAST(FMEA_REV AS varchar(10)),''))   = @FMEA_NR OR NULLIF(LTRIM(RTRIM(@FMEA_NR)),'') IS NULL)
	--ORDER BY CAST( FFNK_NR AS int)
 
END

