IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HDOSYA_ANTIBIYOTIK_LISTE]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION  [dbo].[HDOSYA_ANTIBIYOTIK_LISTE]
go

CREATE FUNCTION [dbo].[HDOSYA_ANTIBIYOTIK_LISTE] (@HDosyaId as Integer)  
RETURNS nvarchar(MAX) AS  
BEGIN 
declare @IlacKodListe as nvarchar(MAX),
	@Ilac as nvarchar(120)
SET @IlacKodListe = '' 
DECLARE HDOSYA_ILAC_CURSOR CURSOR
   FOR SELECT TARIHILAC+' ('+CAST(ADET AS nvarchar)+' Adet)' Ilac  FROM
		(
		SELECT  '['+CAST(DATEPART(DAY, ITARIH) AS nvarchar)+'.'+CAST(DATEPART(MONTH, ITARIH) AS nvarchar)+'.'+CAST(DATEPART(YEAR, ITARIH) AS nvarchar) +'] '+ISNULL(LEFT(ILAC.ITEMADI,100),'') TARIHILAC,SUM(VWHESAPISLDRM.ADET) ADET FROM VWHESAPISLDRM
		INNER JOIN (SELECT * FROM TITEM WHERE ANTIBIYOTIK=1 AND IIPTAL=0 AND ITEMTURUID=3 ) AS ILAC ON ILAC.ITEMID=VWHESAPISLDRM.ITEMID
		 WHERE IPTAL=0 AND HDOSYAID=@HDosyaId
		 GROUP BY  '['+CAST(DATEPART(DAY, ITARIH) AS nvarchar)+'.'+CAST(DATEPART(MONTH, ITARIH) AS nvarchar)+'.'+CAST(DATEPART(YEAR, ITARIH) AS nvarchar) +'] '+ISNULL(LEFT(ILAC.ITEMADI,100),'')
		 ) AS T

 
OPEN HDOSYA_ILAC_CURSOR
FETCH NEXT FROM HDOSYA_ILAC_CURSOR INTO @Ilac
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @IlacKodListe = @IlacKodListe + @Ilac + ', '
	FETCH NEXT FROM HDOSYA_ILAC_CURSOR INTO @Ilac
END
	IF CHARINDEX(',', ISNULL(@IlacKodListe,' ')) <> 0
		SET @IlacKodListe = LEFT(@IlacKodListe, LEN(@IlacKodListe)-1)
return @IlacKodListe
END
GO


