DROP PROCEDURE sp_PDAP7_5_FMEA_FehlerAuftretenskorrelation
GO
-- =============================================
-- Author:		Mark Hausmann
-- Create date: 08.08.2019
--              30.06.2020 bei den Fehlersammelkarten den Bezug auf den Fehler eingebaut
--   
-- Description: Auswertung welche Fehler ohne Auftretenswarscheinlichkeit doch aufgetreten sind in der FMEA
-- 
-- =============================================
-- DROP PROCEDURE sp_PDAP7_5_FMEA_FehlerAuftretenskorrelation @A = '', @Datum_von = '2008',  @Datum_bis = '2019'
CREATE PROCEDURE sp_PDAP7_5_FMEA_FehlerAuftretenskorrelation(
@A varchar(10) = NULL,  -- Grenzwert für die Auftretenswahrscheinlichkeit 
--@Bereich smallint = NULL, -- zu betrachtender Bereich, SPC (1) oder REK (2) oder übergreifend (0)
@Datum_von varchar(10)= NULL,
@Datum_bis varchar(10) = NULL -- 12.08.2019
) AS
BEGIN

-- Test
DECLARE @A_ smallint = NULLIF(@A,'') -- 5  -- Grenzwert für die Auftretenswahrscheinlichkeit 

DECLARE @Datum_von_ date = TRY_CONVERT(date, @Datum_von, 104 )
DECLARE @Datum_bis_ date = TRY_CONVERT(date, @Datum_bis, 104 )

--
--RAISERROR(@Datum_bis,16,1)
----DECLARE @Bereich_ smallint =  @Bereich -- 1 -- zu betrachtender Bereich, SPC (1) oder REK (2) oder übergreifend (0)
--DECLARE @Datum_von_ date = NULLIF(LTRIM(RTRIM(@Datum_von)),'')
--DECLARE @Datum_bis_ date = NULLIF(LTRIM(RTRIM(@Datum_bis)),'')
 
IF ISNULL(@Datum_von,'') = ''
SET @Datum_von_ = CONVERT(date, '01.01.' + CAST(YEAR(GETDATE()) -10 AS varchar(4)), 104 )  
IF ISNULL(@Datum_bis,'') = ''
SET @Datum_bis_ =   CONVERT(date, '31.12.' + CAST(YEAR(GETDATE()) AS varchar(4)), 104 ) 

--SET @Datum_von_ = ISNULL( @Datum_von_, CONVERT(date, '01.01.' + CAST(YEAR(GETDATE()) -10 AS varchar(4)), 104 ) )
--SET @Datum_bis_ = ISNULL( @Datum_bis_, CONVERT(date, '31.12.' + CAST(YEAR(GETDATE()) AS varchar(4)), 104 ) ) 


--DECLARE @msg varchar(50) = ISNULL(@Datum_bis_,'--')
--RAISERROR( 'test',16,1)
--SELECT @Datum_von_, @Datum_bis_  

DECLARE @Table_Var TABLE
(
id integer identity(1,1) PRIMARY KEY,
FilterLabel varchar(250),
Fehleranzahl_SPC_mess_ein int,
Fehleranzahl_SPC_mess_reg int,
Fehleranzahl_SPC_mess_mfu int,
Fehleranzahl_SPC_mess int,
Fehleranzahl_SPC_att int,
Fehleranzahl_REK int,
Fehleranzahl int,
FMEA_ID int,
FMEA_NR varchar(200), 
FFNK_NR varchar(100),
FMEA_DATUM varchar(200),
FMEA_NR_DATUM varchar(200), 
FFNK_NR_BEZ varchar(200),
EZ_NR varchar(100), 
FE_NR varchar(100),
FE_NR_NAME varchar(200),
EZ_NR_NAME varchar(200),
FFLG_BEZ varchar(250),
FFE_B int,
FUR_BEZ varchar(250),
FUR_A int,
FUR_E int,
FAMASS_MASS_BEZ varchar(2000),
FMASS_ERL_TERMIN varchar(10), -- 01.10.2019
FMASS_ERL_MA varchar(30),
MA_ID int,
EZ_ID int,
FE_ID int
)
INSERT INTO @Table_Var(FMEA_ID, FMEA_NR, FFNK_NR, FMEA_DATUM, FMEA_NR_DATUM, FFNK_NR_BEZ, EZ_NR, FE_NR, FE_NR_NAME, EZ_NR_NAME, FFLG_BEZ, FFE_B, FUR_BEZ, FUR_A, FUR_E, 
FAMASS_MASS_BEZ, FMASS_ERL_TERMIN, FMASS_ERL_MA, MA_ID, EZ_ID, FE_ID )
SELECT 
FMEA_ID,
FMEA_NR = FMEA_NR + ISNULL('-'+CAST(FMEA_REV AS varchar(10)),''),
--FMEA_NR_REV = FMEA_NR + ISNULL('-'+CAST(FMEA_REV AS varchar(10)),''),
FFNK_NR,
FMEA_DATUM = CONVERT(varchar(10),FMEA_GEPL_AB_DATUM,104),	 
FMEA_NR_DATUM = FMEA_NR + ISNULL(' (' + CONVERT(varchar(10),FMEA_GEPL_AB_DATUM,104)+')',''), 
FFNK_NR_BEZ = FFNK_NR + ISNULL('-' + FFNK_BEZ,''), 
EZ_NR,
FE_NR = ISNULL( (SELECT TOP 1 FE_NR FROM FE_STM WHERE FE_ID = FFE_FE_ID),FFE_FE_BEZ),
FE_NR_NAME = ISNULL((SELECT TOP 1 FE_NR FROM FE_STM WHERE FE_ID = FFE_FE_ID),FFE_FE_BEZ) + ISNULL('-' + (SELECT TOP 1 FE_NAME FROM FE_STM WHERE FE_ID = FFE_FE_ID),''),
EZ_NR_NAME = EZ_NR + ISNULL('-' + EZ_NAME,''),
FFLG_BEZ,
FFE_B,
FUR_BEZ,
FUR_A,
FUR_E,
FAMASS_MASS_BEZ,
FMASS_ERL_TERMIN, -- = CONVERT(varchar(10),FMASS_ERL_TERMIN,104), -- 01.10.2019
FMASS_ERL_MA = MA_NAME,
MA_ID = FMASS_ERL_MA_ID,
EZ_ID,
FE_ID
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 FUR_A <= ISNULL(@A_,FUR_A)
AND CAST( FMEA_ANLEGE_DATUM AS date) BETWEEN @Datum_von_ AND @Datum_bis_ 
--AND NULLIF(FMEA_STATUS,'0') IS NOT NULL


--Mögliche Duplikate entfernen (hier keine gefunden)
;WITH  numbered 
AS ( SELECT   *
    , row_number() OVER ( PARTITION BY EZ_NR, FMEA_NR, FFNK_NR_BEZ, FE_NR  ORDER BY   id    ) AS nr
FROM     @Table_Var
)
--SELECT * FROM numbered
DELETE FROM  numbered
WHERE   nr > 1


IF NOT EXISTS (SELECT * FROM @Table_Var ) INSERT INTO @Table_Var DEFAULT VALUES 

UPDATE @Table_Var SET FilterLabel = ISNULL( 'Auftretenswahrscheinlichkeit <=' + CAST(@A_ AS varchar(10)) + ' ', '' )
-- + ISNULL( '; Bereich: ' + CASE @Bereich_ WHEN 1 THEN 'SPC' WHEN 2 THEN 'REK' END, '' )  
+ ISNULL( 'Zeitraum von ' + CONVERT(varchar(10), @Datum_von_, 104) + ' bis ' + CONVERT(varchar(10), @Datum_bis_, 104), '')


UPDATE @Table_Var SET Fehleranzahl_SPC_att = ISNULL(( SELECT SUM(FSKD_ANZAHL) FROM FSKK_DAT INNER JOIN FSKD_DAT ON FSKD_FSKK_ID = FSKK_ID WHERE  FSKD_FE_ID = FE_ID  AND     FSKK_EZ_ID = EZ_ID AND CAST( FSKK_DATUM_VON AS date) BETWEEN @Datum_von_ AND @Datum_bis_ ), 0 )
UPDATE @Table_Var SET Fehleranzahl_SPC_mess_ein = ISNULL(( SELECT COUNT(*) FROM MPK_DAT INNER JOIN MPD_EW ON MPD_MPK_ID = MPK_ID INNER JOIN MK_STM ON MK_ID = MPK_MK_ID WHERE MPK_EZ_ID = EZ_ID AND MK_NR = FE_NR AND MPD_WERT NOT BETWEEN MK_SPEZIF_UNTEN AND MK_SPEZIF_OBEN AND CAST( MPK_DATUM_VON AS date) BETWEEN @Datum_von_ AND @Datum_bis_ ), 0 ) 
UPDATE @Table_Var SET Fehleranzahl_SPC_mess_reg = ISNULL(( SELECT COUNT(*) FROM MPK_DAT INNER JOIN MPD_REG ON MPD_MPK_ID = MPK_ID INNER JOIN MK_STM ON MK_ID = MPK_MK_ID WHERE MPK_EZ_ID = EZ_ID AND MK_NR = FE_NR AND MPD_WERT NOT BETWEEN MK_SPEZIF_UNTEN AND MK_SPEZIF_OBEN AND CAST( MPK_DATUM_VON AS date) BETWEEN @Datum_von_ AND @Datum_bis_ ), 0 ) 
UPDATE @Table_Var SET Fehleranzahl_SPC_mess_mfu = ISNULL(( SELECT COUNT(*) FROM MPK_DAT INNER JOIN MPD_MFU ON MPD_MPK_ID = MPK_ID INNER JOIN MK_STM ON MK_ID = MPK_MK_ID WHERE MPK_EZ_ID = EZ_ID AND MK_NR = FE_NR AND MPD_WERT NOT BETWEEN MK_SPEZIF_UNTEN AND MK_SPEZIF_OBEN AND CAST( MPK_DATUM_VON AS date) BETWEEN @Datum_von_ AND @Datum_bis_ ), 0 ) 
UPDATE @Table_Var SET Fehleranzahl_REK = ISNULL((SELECT SUM(REKF_ANZ_AUSF_GESAMT) FROM REK_FEHL INNER JOIN REK_STM ON REK_ID = REKF_REK_ID WHERE REKF_FE_ID = FE_ID AND REK_EZ_ID = EZ_ID AND CAST( REK_REKL_DATUM AS date) BETWEEN @Datum_von_ AND @Datum_bis_ ), 0 )
UPDATE @Table_Var SET Fehleranzahl_SPC_mess = ISNULL(Fehleranzahl_SPC_mess_mfu,0) + ISNULL(Fehleranzahl_SPC_mess_reg,0) + ISNULL(Fehleranzahl_SPC_mess_ein,0)
UPDATE @Table_Var SET Fehleranzahl = ISNULL(Fehleranzahl_REK,0) + ISNULL(Fehleranzahl_SPC_mess_mfu,0) + ISNULL(Fehleranzahl_SPC_mess_reg,0) + ISNULL(Fehleranzahl_SPC_mess_ein,0) + ISNULL(Fehleranzahl_SPC_att,0)

SELECT 
Nr = ROW_NUMBER() OVER (Order by (SELECT 100)),
Erzeugnis = EZ_NR,
FMEA = FMEA_NR,
Funktion = FFNK_NR_BEZ,
Fehler = FE_NR,
*
FROM @Table_Var WHERE Fehleranzahl > 0 ORDER BY Fehleranzahl DESC --WHERE FMEA_NR = 'Z-ABX'
--FME  FE_NR, * FROM @Table_Var WHERE Fehleranzahl > 0 ORDER BY Fehleranzahl DESC --WHERE FMEA_NR = 'Z-ABX'
END
