IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DashBoard_Hizmet_Analiz]') AND xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[DashBoard_Hizmet_Analiz]
GO

CREATE FUNCTION [dbo].[DashBoard_Hizmet_Analiz]
(
    @BasTar datetime,
    @BitTar datetime,
    @SubeId int
)
RETURNS TABLE
AS
RETURN
(

	WITH HesapIslem AS
	(
	   SELECT ADET, ITEMKODU2_LOINC, ITEMKODU2 
	   FROM VWMDS_HESAPISL WITH(NOLOCK) 
	   INNER JOIN TITEM ON TITEM.ITEMID = VWMDS_HESAPISL.ITEMID
	   WHERE IPTAL = 0 
		 AND VWMDS_HESAPISL.SUBEID = @SubeId
		 AND ITARIH >= @BasTar 
		 AND ITARIH < DateAdd(d, 1, @BitTar)
	),
	MuayeneIslem AS
	(
	   SELECT MUAYENETIPIID, ADET 
	   FROM VWMUAYENEISL WITH(NOLOCK) 
	   WHERE IPTAL = 0 
		 AND VWMUAYENEISL.SUBEID = @SubeId
		 AND ITARIH >= @BasTar 
		 AND ITARIH < DateAdd(d, 1, @BitTar)
	),
	AmeliyatIslem AS
	(
	   SELECT ITEMKODU2, ITARIH, ADET 
	   FROM VWAMELIYAT WITH(NOLOCK) 
	   WHERE VWAMELIYAT.SUBEID = @SubeId
		 AND ITARIH >= @BasTar 
		 AND ITARIH < DateAdd(d, 1, @BitTar)
	)

	SELECT 
		SUM(BT_Sayisi) AS BT_Sayisi,
		SUM(MR_Sayisi) AS MR_Sayisi,
		SUM(Anjio_Sayisi) AS Anjio_Sayisi,
		SUM(Gastro_Sayisi) AS Gastro_Sayisi,
		SUM(Kontrol_Muayene_Sayisi) AS Kontrol_Muayene_Sayisi,
		SUM(Muayene_Sayisi) AS Muayene_Sayisi,
		SUM(Acil_Sayisi) AS Acil_Sayisi,
		SUM(Ameliyat_Sayisi) AS Ameliyat_Sayisi,
		SUM(Dogum_Sayisi) AS Dogum_Sayisi
	FROM 
	(
		SELECT 
			CASE WHEN ITEMKODU2_LOINC >= 'R100000' AND ITEMKODU2_LOINC <= 'R102290' THEN ADET ELSE 0 END AS BT_Sayisi,
			CASE WHEN ITEMKODU2_LOINC >= 'R103000' AND ITEMKODU2_LOINC <= 'R104870' THEN ADET ELSE 0 END AS MR_Sayisi,
			CASE WHEN ITEMKODU2 IN ('700810', 'P700810','700880', '700890','700900', '700910','700920', '700921','700922', 'P700880', 'P700890','P700900', 'P700910','P700920', 'P700921','P700922') THEN ADET ELSE 0 END AS Anjio_Sayisi,
			CASE WHEN ITEMKODU2 IN ('701390','701400','701540','701550','701450','701460','P701410') THEN ADET ELSE 0 END AS Gastro_Sayisi,
			CASE WHEN ITEMKODU2 IN ('619922','P619923','P619922','619910','P619910','P619912','P619911','P619913','619920','P619920','P619926','619921','P619921','P619927','P619930','619929','P619929','P619925') THEN ADET ELSE 0 END AS Dogum_Sayisi,
			0 AS Kontrol_Muayene_Sayisi,
			0 AS Muayene_Sayisi,
			0 AS Acil_Sayisi,
			0 AS Ameliyat_Sayisi
		FROM HesapIslem WITH(NOLOCK)

		UNION ALL

		SELECT 
			0 AS BT_Sayisi,
			0 AS MR_Sayisi,
			0 AS Anjio_Sayisi,
			0 AS Gastro_Sayisi,
			0 AS Dogum_Sayisi,
			CASE WHEN MUAYENETIPIID = 2 THEN ADET ELSE 0 END AS Kontrol_Muayene_Sayisi,
			CASE WHEN MUAYENETIPIID NOT IN (2,5) THEN ADET ELSE 0 END AS Muayene_Sayisi,
			CASE WHEN MUAYENETIPIID = 5 THEN ADET ELSE 0 END AS Acil_Sayisi,
			0 AS Ameliyat_Sayisi
		FROM MuayeneIslem WITH(NOLOCK)

		UNION ALL

		SELECT 
			0 AS BT_Sayisi,
			0 AS MR_Sayisi,
			0 AS Anjio_Sayisi,
			0 AS Gastro_Sayisi,
			0 AS Dogum_Sayisi,
			0 AS Kontrol_Muayene_Sayisi,
			0 AS Muayene_Sayisi,
			0 AS Acil_Sayisi,
			CASE WHEN ITEMKODU2 NOT IN ('700810', 'P700810','700880', '700890','700900' , '700910','700920' ,'700921','700922', 'P700880' ,'P700890','P700900' ,'P700910','P700920', 'P700921','P700922' /*ANJİO*/
										 ,'701390','701400','701540','701550','701450','701460','P701410' /*gastro*/
										 ,'619922','P619923','P619922','619910','P619910','P619912','P619911','P619913','619920','P619920','P619926','619921','P619921','P619927','P619930','619929','P619929','P619925' /*DOĞUMLAR*/
										 )  THEN ADET ELSE 0 END AS Ameliyat_Sayisi
		FROM AmeliyatIslem WITH(NOLOCK)
	) AS T
)