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


CREATE FUNCTION [dbo].[RANDEVUHAFTAAMELIYAT] (@salonid int, @gun datetime)  
RETURNS table AS  
return
SELECT G1.SAAT,G1.HASTADR AS G1, G2.HASTADR AS G2, G3.HASTADR AS G3, G4.HASTADR AS G4, G5.HASTADR AS G5, G6.HASTADR AS G6, G7.HASTADR AS G7, 
              G1.HAMELIYATID AS GM1, G2.HAMELIYATID AS GM2, G3.HAMELIYATID AS GM3, G4.HAMELIYATID AS GM4, G5.HAMELIYATID AS GM5, G6.HAMELIYATID AS GM6, 

G7.HAMELIYATID AS GM7,                
              G1.RANDDURUMID AS G1RANDDURUMID, G2.RANDDURUMID AS G2RANDDURUMID, G3.RANDDURUMID AS G3RANDDURUMID, G4.RANDDURUMID AS G4RANDDURUMID, 

G5.RANDDURUMID AS G5RANDDURUMID, G6.RANDDURUMID AS G6RANDDURUMID, G7.RANDDURUMID AS G7RANDDURUMID,
              G1.KIMLIKID AS G1KIMLIKID, G2.KIMLIKID AS G2KIMLIKID, G3.KIMLIKID AS G3KIMLIKID, G4.KIMLIKID AS G4KIMLIKID, G5.KIMLIKID AS G5KIMLIKID, 

G6.KIMLIKID AS G6KIMLIKID, G7.KIMLIKID AS G7KIMLIKID,
	      G1.HASTA AS GH1, G2.HASTA AS GH2, G3.HASTA AS GH3, G4.HASTA AS GH4, G5.HASTA AS GH5, G6.HASTA AS GH6, G7.HASTA AS GH7,
	      G1.HYAS AS GHYAS1, G2.HYAS AS GHYAS2, G3.HYAS AS GHYAS3, G4.HYAS AS GHYAS4, G5.HYAS AS GHYAS5, G6.HYAS AS GHYAS6, G7.HYAS AS GHYAS7, 
	      G1.HCINS AS GHCINS1, G2.HCINS AS GHCINS2, G3.HCINS AS GHCINS3, G4.HCINS AS GHCINS4, G5.HCINS AS GHCINS5, G6.HCINS AS GHCINS6, G7.HCINS AS GHCINS7,
	      G1.ISLEM AS G1ISLEM, G2.ISLEM AS G2ISLEM, G3.ISLEM AS G3ISLEM, G4.ISLEM AS G4ISLEM, G5.ISLEM AS G5ISLEM, G6.ISLEM AS G6ISLEM, G7.ISLEM AS G7ISLEM,
		  G1.RANDEVUID GRID1,G2.RANDEVUID GRID2,G3.RANDEVUID GRID3,G4.RANDEVUID GRID4,G5.RANDEVUID GRID5,G6.RANDEVUID GRID6,G7.RANDEVUID GRID7
              
FROM 

-------------------------GÜN  1 ------------------------------
(SELECT convert(nvarchar(5), TGUNSAATAMELIYAT.Saat ,14) as Saat, RANDEVUID, HAMELIYATID AS HAMELIYATID,  RANDDURUMID, KIMLIKID, 
       CASE WHEN RANDEVUID IS NOT NULL AND HRANDEVU.DRID IS NOT NULL  AND TGUNSAATAMELIYAT.Saat=RSAAT THEN DRADI
			when RANDEVUID IS NOT NULL AND HRANDEVU.DRID IS NOT NULL  and TGUNSAATAMELIYAT.Saat<>RSAAT THEN '"' 
       ELSE 
       CASE WHEN RANDEVUID IS NOT NULL AND HRANDEVU.DRID IS NULL THEN ADISOYADI
             ELSE 
                CASE 
		       WHEN ((TGUNSAATAMELIYAT.Saat>=SBASSAAT and TGUNSAATAMELIYAT.Saat<SBITSAAT) OR (TGUNSAATAMELIYAT.Saat>=OSBASSAAT and 

TGUNSAATAMELIYAT.Saat<OSBITSAAT))  THEN NULL 
               ELSE '---' END  END
       END AS HASTADR,
       CASE WHEN RANDEVUID IS NOT NULL THEN ADISOYADI ELSE '' END AS HASTA,
       CASE WHEN HRANDEVU.ITEMID IS NOT NULL THEN TITEM.ITEMADI ELSE '' END AS ISLEM,
       CASE WHEN HRANDEVU.KIMLIKID IS NULL THEN '' ELSE CASE WHEN dbo.HKIMLIK.HDTARIHI IS NULL THEN 0 ELSE DATEDIFF(yy, dbo.HKIMLIK.HDTARIHI, GETDATE()) 
                      + CASE WHEN RIGHT(CONVERT(NVARCHAR(6), dbo.HKIMLIK.HDTARIHI, 12), 4) > RIGHT(CONVERT(NVARCHAR(6), CAST(GETDATE() AS datetime), 12), 4) 
                      THEN - 1 ELSE 0 END END END AS HYAS,
       CASE WHEN HRANDEVU.KIMLIKID IS NULL THEN '' ELSE CASE WHEN HKIMLIK.HCINSIYETI='E' THEN 'Erkek' WHEN HKIMLIK.HCINSIYETI='K' THEN 'Kadın' END END AS HCINS 
FROM TGUNSAATAMELIYAT WITH(NOLOCK) INNER JOIN 
     TASALON WITH(NOLOCK) ON TASALON.SALONID = @salonid LEFT JOIN 
     TASALONCALSAAT WITH(NOLOCK) ON TASALONCALSAAT.SALONID=@salonid and HAFTAGUNU=DATEPART(dw,@gun) LEFT OUTER JOIN
     HRANDEVU WITH(NOLOCK) ON HRANDEVU.SALONID = @salonid AND RTARIH =@gun AND TGUNSAATAMELIYAT.Saat>=RSAAT and TGUNSAATAMELIYAT.Saat<dateadd(n,RSURE,RSAAT) AND 

RANDDURUMID NOT IN (5,6) LEFT  JOIN
     TDRKIMLIK WITH(NOLOCK) ON HRANDEVU.DRID=TDRKIMLIK.DRID LEFT  JOIN
     TITEM WITH(NOLOCK) ON TITEM.ITEMID=HRANDEVU.ITEMID AND ITEMTURUID=7 AND IIPTAL=0 LEFT JOIN
     HKIMLIK WITH(NOLOCK) ON HKIMLIK.HKIMLIKID = HRANDEVU.KIMLIKID 
WHERE CAST(ROUND(CAST(SAAT-dbo.HaftalikIlkCalSaatSalon(TASALON.SALONID) AS FLOAT) * 24*60,0)AS INT)%ISNULL(TASALON.RANDEVUDAKIKA,5) = 0) G1 
INNER JOIN
-------------------------GÜN  2 ------------------------------
(SELECT convert(nvarchar(5), TGUNSAATAMELIYAT.Saat ,14) as Saat, RANDEVUID, HAMELIYATID AS HAMELIYATID,  RANDDURUMID, KIMLIKID, 
       CASE WHEN RANDEVUID IS NOT NULL AND HRANDEVU.DRID IS NOT NULL AND TGUNSAATAMELIYAT.Saat=RSAAT THEN DRADI
			when RANDEVUID IS NOT NULL AND HRANDEVU.DRID IS NOT NULL  and TGUNSAATAMELIYAT.Saat<>RSAAT THEN '"' 
       ELSE 
       CASE WHEN RANDEVUID IS NOT NULL AND HRANDEVU.DRID IS NULL THEN ADISOYADI
             ELSE 
                CASE 
		       WHEN ((TGUNSAATAMELIYAT.Saat>=SBASSAAT and TGUNSAATAMELIYAT.Saat<SBITSAAT) OR (TGUNSAATAMELIYAT.Saat>=OSBASSAAT and 

TGUNSAATAMELIYAT.Saat<OSBITSAAT))  THEN NULL 
               ELSE '---' END  END
       END AS HASTADR,
       CASE WHEN RANDEVUID IS NOT NULL THEN ADISOYADI ELSE '' END AS HASTA,
       CASE WHEN HRANDEVU.ITEMID IS NOT NULL THEN TITEM.ITEMADI ELSE '' END AS ISLEM,
       CASE WHEN HRANDEVU.KIMLIKID IS NULL THEN '' ELSE CASE WHEN dbo.HKIMLIK.HDTARIHI IS NULL THEN 0 ELSE DATEDIFF(yy, dbo.HKIMLIK.HDTARIHI, GETDATE()) 
                      + CASE WHEN RIGHT(CONVERT(NVARCHAR(6), dbo.HKIMLIK.HDTARIHI, 12), 4) > RIGHT(CONVERT(NVARCHAR(6), CAST(GETDATE() AS datetime), 12), 4) 
                      THEN - 1 ELSE 0 END END END AS HYAS,
       CASE WHEN HRANDEVU.KIMLIKID IS NULL THEN '' ELSE CASE WHEN HKIMLIK.HCINSIYETI='E' THEN 'Erkek' WHEN HKIMLIK.HCINSIYETI='K' THEN 'Kadın' END END AS HCINS 
FROM TGUNSAATAMELIYAT WITH(NOLOCK) INNER JOIN 
     TASALON WITH(NOLOCK) ON TASALON.SALONID = @salonid LEFT JOIN 
     TASALONCALSAAT WITH(NOLOCK) ON TASALONCALSAAT.SALONID=@salonid and HAFTAGUNU=DATEPART(dw,DATEADD(d,1,@gun)) LEFT OUTER JOIN
     HRANDEVU WITH(NOLOCK) ON HRANDEVU.SALONID = @salonid AND RTARIH =DATEADD(d,1,@gun) AND TGUNSAATAMELIYAT.Saat>=RSAAT and TGUNSAATAMELIYAT.Saat<dateadd(n,RSURE,RSAAT) 

AND RANDDURUMID NOT IN (5,6) LEFT  JOIN
     TDRKIMLIK WITH(NOLOCK) ON HRANDEVU.DRID=TDRKIMLIK.DRID LEFT  JOIN
     TITEM WITH(NOLOCK) ON TITEM.ITEMID=HRANDEVU.ITEMID AND ITEMTURUID=7 AND IIPTAL=0 LEFT JOIN
     HKIMLIK WITH(NOLOCK) ON HKIMLIK.HKIMLIKID = HRANDEVU.KIMLIKID
WHERE CAST(ROUND(CAST(SAAT-dbo.HaftalikIlkCalSaatSalon(TASALON.SALONID) AS FLOAT) * 24*60,0)AS INT)%ISNULL(TASALON.RANDEVUDAKIKA,5) = 0) G2 
ON G1.SAAT=G2.SAAT INNER JOIN
-------------------------GÜN  3 ------------------------------
(SELECT convert(nvarchar(5), TGUNSAATAMELIYAT.Saat ,14) as Saat, RANDEVUID, HAMELIYATID AS HAMELIYATID,  RANDDURUMID, KIMLIKID, 
       CASE WHEN RANDEVUID IS NOT NULL AND HRANDEVU.DRID IS NOT NULL  AND TGUNSAATAMELIYAT.Saat=RSAAT THEN DRADI
			when RANDEVUID IS NOT NULL AND HRANDEVU.DRID IS NOT NULL  and TGUNSAATAMELIYAT.Saat<>RSAAT THEN '"' 
       ELSE 
       CASE WHEN RANDEVUID IS NOT NULL AND HRANDEVU.DRID IS NULL THEN ADISOYADI
             ELSE 
                CASE 
		       WHEN ((TGUNSAATAMELIYAT.Saat>=SBASSAAT and TGUNSAATAMELIYAT.Saat<SBITSAAT) OR (TGUNSAATAMELIYAT.Saat>=OSBASSAAT and 

TGUNSAATAMELIYAT.Saat<OSBITSAAT))  THEN NULL 
               ELSE '---' END  END
       END AS HASTADR,
       CASE WHEN RANDEVUID IS NOT NULL THEN ADISOYADI ELSE '' END AS HASTA,
       CASE WHEN HRANDEVU.ITEMID IS NOT NULL THEN TITEM.ITEMADI ELSE '' END AS ISLEM,
       CASE WHEN HRANDEVU.KIMLIKID IS NULL THEN '' ELSE CASE WHEN dbo.HKIMLIK.HDTARIHI IS NULL THEN 0 ELSE DATEDIFF(yy, dbo.HKIMLIK.HDTARIHI, GETDATE()) 
                      + CASE WHEN RIGHT(CONVERT(NVARCHAR(6), dbo.HKIMLIK.HDTARIHI, 12), 4) > RIGHT(CONVERT(NVARCHAR(6), CAST(GETDATE() AS datetime), 12), 4) 
                      THEN - 1 ELSE 0 END END END AS HYAS,
       CASE WHEN HRANDEVU.KIMLIKID IS NULL THEN '' ELSE CASE WHEN HKIMLIK.HCINSIYETI='E' THEN 'Erkek' WHEN HKIMLIK.HCINSIYETI='K' THEN 'Kadın' END END AS HCINS 
FROM TGUNSAATAMELIYAT WITH(NOLOCK) INNER JOIN 
     TASALON WITH(NOLOCK) ON TASALON.SALONID = @salonid LEFT JOIN 
     TASALONCALSAAT WITH(NOLOCK) ON TASALONCALSAAT.SALONID=@salonid and HAFTAGUNU=DATEPART(dw,DATEADD(d,2,@gun)) LEFT OUTER JOIN
     HRANDEVU WITH(NOLOCK) ON HRANDEVU.SALONID = @salonid AND RTARIH =DATEADD(d,2,@gun) AND TGUNSAATAMELIYAT.Saat>=RSAAT and TGUNSAATAMELIYAT.Saat<dateadd(n,RSURE,RSAAT) 

AND RANDDURUMID NOT IN (5,6)  LEFT JOIN
     TDRKIMLIK WITH(NOLOCK) ON HRANDEVU.DRID=TDRKIMLIK.DRID LEFT  JOIN
     TITEM WITH(NOLOCK) ON TITEM.ITEMID=HRANDEVU.ITEMID AND ITEMTURUID=7 AND IIPTAL=0 LEFT JOIN
     HKIMLIK WITH(NOLOCK) ON HKIMLIK.HKIMLIKID = HRANDEVU.KIMLIKID
WHERE CAST(ROUND(CAST(SAAT-dbo.HaftalikIlkCalSaatSalon(TASALON.SALONID) AS FLOAT) * 24*60,0)AS INT)%ISNULL(TASALON.RANDEVUDAKIKA,5) = 0) G3 
ON G1.SAAT=G3.SAAT INNER JOIN
-------------------------GÜN  4 ------------------------------
(SELECT convert(nvarchar(5), TGUNSAATAMELIYAT.Saat ,14) as Saat, RANDEVUID, HAMELIYATID AS HAMELIYATID, RANDDURUMID, KIMLIKID, 
       CASE WHEN RANDEVUID IS NOT NULL AND HRANDEVU.DRID IS NOT NULL  AND TGUNSAATAMELIYAT.Saat=RSAAT THEN DRADI
			when RANDEVUID IS NOT NULL AND HRANDEVU.DRID IS NOT NULL  and TGUNSAATAMELIYAT.Saat<>RSAAT THEN '"' 
       ELSE 
       CASE WHEN RANDEVUID IS NOT NULL AND HRANDEVU.DRID IS NULL THEN ADISOYADI
             ELSE 
                CASE 
		       WHEN ((TGUNSAATAMELIYAT.Saat>=SBASSAAT and TGUNSAATAMELIYAT.Saat<SBITSAAT) OR (TGUNSAATAMELIYAT.Saat>=OSBASSAAT and 

TGUNSAATAMELIYAT.Saat<OSBITSAAT))  THEN NULL 
               ELSE '---' END  END
       END AS HASTADR,
       CASE WHEN RANDEVUID IS NOT NULL THEN ADISOYADI ELSE '' END AS HASTA,
       CASE WHEN HRANDEVU.ITEMID IS NOT NULL THEN TITEM.ITEMADI ELSE '' END AS ISLEM,
       CASE WHEN HRANDEVU.KIMLIKID IS NULL THEN '' ELSE CASE WHEN dbo.HKIMLIK.HDTARIHI IS NULL THEN 0 ELSE DATEDIFF(yy, dbo.HKIMLIK.HDTARIHI, GETDATE()) 
                      + CASE WHEN RIGHT(CONVERT(NVARCHAR(6), dbo.HKIMLIK.HDTARIHI, 12), 4) > RIGHT(CONVERT(NVARCHAR(6), CAST(GETDATE() AS datetime), 12), 4) 
                      THEN - 1 ELSE 0 END END END AS HYAS,
       CASE WHEN HRANDEVU.KIMLIKID IS NULL THEN '' ELSE CASE WHEN HKIMLIK.HCINSIYETI='E' THEN 'Erkek' WHEN HKIMLIK.HCINSIYETI='K' THEN 'Kadın' END END AS HCINS 
FROM TGUNSAATAMELIYAT WITH(NOLOCK) INNER JOIN 
     TASALON WITH(NOLOCK) ON TASALON.SALONID = @salonid LEFT JOIN 
     TASALONCALSAAT WITH(NOLOCK) ON TASALONCALSAAT.SALONID=@salonid and HAFTAGUNU=DATEPART(dw,DATEADD(d,3,@gun)) LEFT OUTER JOIN
     HRANDEVU WITH(NOLOCK) ON HRANDEVU.SALONID = @salonid AND RTARIH =DATEADD(d,3,@gun) AND TGUNSAATAMELIYAT.Saat>=RSAAT and TGUNSAATAMELIYAT.Saat<dateadd(n,RSURE,RSAAT) 

AND RANDDURUMID NOT IN (5,6)  LEFT JOIN
     TDRKIMLIK WITH(NOLOCK) ON HRANDEVU.DRID=TDRKIMLIK.DRID LEFT  JOIN
     TITEM WITH(NOLOCK) ON TITEM.ITEMID=HRANDEVU.ITEMID AND ITEMTURUID=7 AND IIPTAL=0 LEFT JOIN
     HKIMLIK WITH(NOLOCK) ON HKIMLIK.HKIMLIKID = HRANDEVU.KIMLIKID
WHERE CAST(ROUND(CAST(SAAT-dbo.HaftalikIlkCalSaatSalon(TASALON.SALONID) AS FLOAT) * 24*60,0)AS INT)%ISNULL(TASALON.RANDEVUDAKIKA,5) = 0) G4 
ON G1.SAAT=G4.SAAT INNER JOIN
-------------------------GÜN  5 ------------------------------
(SELECT convert(nvarchar(5), TGUNSAATAMELIYAT.Saat ,14) as Saat, RANDEVUID, HAMELIYATID AS HAMELIYATID, RANDDURUMID, KIMLIKID, 
       CASE WHEN RANDEVUID IS NOT NULL AND HRANDEVU.DRID IS NOT NULL  AND TGUNSAATAMELIYAT.Saat=RSAAT THEN DRADI
			when RANDEVUID IS NOT NULL AND HRANDEVU.DRID IS NOT NULL  and TGUNSAATAMELIYAT.Saat<>RSAAT THEN '"' 
       ELSE 
       CASE WHEN RANDEVUID IS NOT NULL AND HRANDEVU.DRID IS NULL THEN ADISOYADI
             ELSE 
                CASE 
		       WHEN ((TGUNSAATAMELIYAT.Saat>=SBASSAAT and TGUNSAATAMELIYAT.Saat<SBITSAAT) OR (TGUNSAATAMELIYAT.Saat>=OSBASSAAT and 

TGUNSAATAMELIYAT.Saat<OSBITSAAT))  THEN NULL 
               ELSE '---' END  END
       END AS HASTADR,
       CASE WHEN RANDEVUID IS NOT NULL THEN ADISOYADI ELSE '' END AS HASTA,
       CASE WHEN HRANDEVU.ITEMID IS NOT NULL THEN TITEM.ITEMADI ELSE '' END AS ISLEM,
       CASE WHEN HRANDEVU.KIMLIKID IS NULL THEN '' ELSE CASE WHEN dbo.HKIMLIK.HDTARIHI IS NULL THEN 0 ELSE DATEDIFF(yy, dbo.HKIMLIK.HDTARIHI, GETDATE()) 
                      + CASE WHEN RIGHT(CONVERT(NVARCHAR(6), dbo.HKIMLIK.HDTARIHI, 12), 4) > RIGHT(CONVERT(NVARCHAR(6), CAST(GETDATE() AS datetime), 12), 4) 
                      THEN - 1 ELSE 0 END END END AS HYAS,
       CASE WHEN HRANDEVU.KIMLIKID IS NULL THEN '' ELSE CASE WHEN HKIMLIK.HCINSIYETI='E' THEN 'Erkek' WHEN HKIMLIK.HCINSIYETI='K' THEN 'Kadın' END END AS HCINS 
FROM TGUNSAATAMELIYAT WITH(NOLOCK) INNER JOIN 
     TASALON WITH(NOLOCK) ON TASALON.SALONID = @salonid LEFT JOIN 
     TASALONCALSAAT WITH(NOLOCK) ON TASALONCALSAAT.SALONID=@salonid and HAFTAGUNU=DATEPART(dw,DATEADD(d,4,@gun)) LEFT OUTER JOIN
     HRANDEVU WITH(NOLOCK) ON HRANDEVU.SALONID = @salonid AND RTARIH =DATEADD(d,4,@gun) AND TGUNSAATAMELIYAT.Saat>=RSAAT and TGUNSAATAMELIYAT.Saat<dateadd(n,RSURE,RSAAT) 

AND RANDDURUMID NOT IN (5,6) LEFT  JOIN
     TDRKIMLIK WITH(NOLOCK) ON HRANDEVU.DRID=TDRKIMLIK.DRID LEFT  JOIN
     TITEM WITH(NOLOCK) ON TITEM.ITEMID=HRANDEVU.ITEMID AND ITEMTURUID=7 AND IIPTAL=0 LEFT JOIN
     HKIMLIK WITH(NOLOCK) ON HKIMLIK.HKIMLIKID = HRANDEVU.KIMLIKID
WHERE CAST(ROUND(CAST(SAAT-dbo.HaftalikIlkCalSaatSalon(TASALON.SALONID) AS FLOAT) * 24*60,0)AS INT)%ISNULL(TASALON.RANDEVUDAKIKA,5) = 0) G5 
ON G1.SAAT=G5.SAAT INNER JOIN
-------------------------GÜN  6 ------------------------------
(SELECT convert(nvarchar(5), TGUNSAATAMELIYAT.Saat ,14) as Saat, RANDEVUID, HAMELIYATID AS HAMELIYATID, RANDDURUMID, KIMLIKID, 
       CASE WHEN RANDEVUID IS NOT NULL AND HRANDEVU.DRID IS NOT NULL  AND TGUNSAATAMELIYAT.Saat=RSAAT THEN DRADI
			when RANDEVUID IS NOT NULL AND HRANDEVU.DRID IS NOT NULL  and TGUNSAATAMELIYAT.Saat<>RSAAT THEN '"' 
       ELSE 
       CASE WHEN RANDEVUID IS NOT NULL AND HRANDEVU.DRID IS NULL THEN ADISOYADI
             ELSE 
                CASE 
		       WHEN ((TGUNSAATAMELIYAT.Saat>=SBASSAAT and TGUNSAATAMELIYAT.Saat<SBITSAAT) OR (TGUNSAATAMELIYAT.Saat>=OSBASSAAT and 

TGUNSAATAMELIYAT.Saat<OSBITSAAT))  THEN NULL 
               ELSE '---' END  END
       END AS HASTADR,
       CASE WHEN RANDEVUID IS NOT NULL THEN ADISOYADI ELSE '' END AS HASTA,
       CASE WHEN HRANDEVU.ITEMID IS NOT NULL THEN TITEM.ITEMADI ELSE '' END AS ISLEM,
       CASE WHEN HRANDEVU.KIMLIKID IS NULL THEN '' ELSE CASE WHEN dbo.HKIMLIK.HDTARIHI IS NULL THEN 0 ELSE DATEDIFF(yy, dbo.HKIMLIK.HDTARIHI, GETDATE()) 
                      + CASE WHEN RIGHT(CONVERT(NVARCHAR(6), dbo.HKIMLIK.HDTARIHI, 12), 4) > RIGHT(CONVERT(NVARCHAR(6), CAST(GETDATE() AS datetime), 12), 4) 
                      THEN - 1 ELSE 0 END END END AS HYAS,
       CASE WHEN HRANDEVU.KIMLIKID IS NULL THEN '' ELSE CASE WHEN HKIMLIK.HCINSIYETI='E' THEN 'Erkek' WHEN HKIMLIK.HCINSIYETI='K' THEN 'Kadın' END END AS HCINS 
FROM TGUNSAATAMELIYAT WITH(NOLOCK) INNER JOIN 
     TASALON WITH(NOLOCK) ON TASALON.SALONID = @salonid LEFT JOIN 
     TASALONCALSAAT WITH(NOLOCK) ON TASALONCALSAAT.SALONID=@salonid and HAFTAGUNU=DATEPART(dw,DATEADD(d,5,@gun)) LEFT OUTER JOIN
     HRANDEVU ON HRANDEVU.SALONID = @salonid AND RTARIH =DATEADD(d,5,@gun) AND TGUNSAATAMELIYAT.Saat>=RSAAT and TGUNSAATAMELIYAT.Saat<dateadd(n,RSURE,RSAAT) 

AND RANDDURUMID NOT IN (5,6)  LEFT JOIN
     TDRKIMLIK WITH(NOLOCK) ON HRANDEVU.DRID=TDRKIMLIK.DRID LEFT  JOIN
     TITEM WITH(NOLOCK) ON TITEM.ITEMID=HRANDEVU.ITEMID AND ITEMTURUID=7 AND IIPTAL=0 LEFT JOIN
     HKIMLIK WITH(NOLOCK) ON HKIMLIK.HKIMLIKID = HRANDEVU.KIMLIKID
WHERE CAST(ROUND(CAST(SAAT-dbo.HaftalikIlkCalSaatSalon(TASALON.SALONID) AS FLOAT) * 24*60,0)AS INT)%ISNULL(TASALON.RANDEVUDAKIKA,5) = 0) G6 
ON G1.SAAT=G6.SAAT INNER JOIN
-------------------------GÜN  7 ------------------------------
(SELECT convert(nvarchar(5), TGUNSAATAMELIYAT.Saat ,14) as Saat, RANDEVUID, HAMELIYATID AS HAMELIYATID, RANDDURUMID, KIMLIKID, 
       CASE WHEN RANDEVUID IS NOT NULL AND HRANDEVU.DRID IS NOT NULL  AND TGUNSAATAMELIYAT.Saat=RSAAT THEN DRADI
			when RANDEVUID IS NOT NULL AND HRANDEVU.DRID IS NOT NULL  and TGUNSAATAMELIYAT.Saat<>RSAAT THEN '"' 
       ELSE 
       CASE WHEN RANDEVUID IS NOT NULL AND HRANDEVU.DRID IS NULL THEN ADISOYADI
             ELSE 
                CASE 
		       WHEN ((TGUNSAATAMELIYAT.Saat>=SBASSAAT and TGUNSAATAMELIYAT.Saat<SBITSAAT) OR (TGUNSAATAMELIYAT.Saat>=OSBASSAAT and 

TGUNSAATAMELIYAT.Saat<OSBITSAAT))  THEN NULL 
               ELSE '---' END  END
       END AS HASTADR,
       CASE WHEN RANDEVUID IS NOT NULL THEN ADISOYADI ELSE '' END AS HASTA,
       CASE WHEN HRANDEVU.ITEMID IS NOT NULL THEN TITEM.ITEMADI ELSE '' END AS ISLEM,
       CASE WHEN HRANDEVU.KIMLIKID IS NULL THEN '' ELSE CASE WHEN dbo.HKIMLIK.HDTARIHI IS NULL THEN 0 ELSE DATEDIFF(yy, dbo.HKIMLIK.HDTARIHI, GETDATE()) 
                      + CASE WHEN RIGHT(CONVERT(NVARCHAR(6), dbo.HKIMLIK.HDTARIHI, 12), 4) > RIGHT(CONVERT(NVARCHAR(6), CAST(GETDATE() AS datetime), 12), 4) 
                      THEN - 1 ELSE 0 END END END AS HYAS,
       CASE WHEN HRANDEVU.KIMLIKID IS NULL THEN '' ELSE CASE WHEN HKIMLIK.HCINSIYETI='E' THEN 'Erkek' WHEN HKIMLIK.HCINSIYETI='K' THEN 'Kadın' END END AS HCINS 
FROM TGUNSAATAMELIYAT WITH(NOLOCK) INNER JOIN 
     TASALON WITH(NOLOCK) ON TASALON.SALONID = @salonid LEFT JOIN 
     TASALONCALSAAT WITH(NOLOCK) ON TASALONCALSAAT.SALONID=@salonid and HAFTAGUNU=DATEPART(dw,DATEADD(d,6,@gun)) LEFT OUTER JOIN
     HRANDEVU WITH(NOLOCK) ON HRANDEVU.SALONID = @salonid AND RTARIH =DATEADD(d,6,@gun) AND TGUNSAATAMELIYAT.Saat>=RSAAT and TGUNSAATAMELIYAT.Saat<dateadd(n,RSURE,RSAAT) 

AND RANDDURUMID NOT IN (5,6) LEFT  JOIN
     TDRKIMLIK WITH(NOLOCK) ON HRANDEVU.DRID=TDRKIMLIK.DRID LEFT  JOIN
     TITEM WITH(NOLOCK) ON TITEM.ITEMID=HRANDEVU.ITEMID AND ITEMTURUID=7 AND IIPTAL=0 LEFT JOIN
     HKIMLIK WITH(NOLOCK) ON HKIMLIK.HKIMLIKID = HRANDEVU.KIMLIKID
WHERE CAST(ROUND(CAST(SAAT-dbo.HaftalikIlkCalSaatSalon(TASALON.SALONID) AS FLOAT) * 24*60,0)AS INT)%ISNULL(TASALON.RANDEVUDAKIKA,5) = 0) G7 
ON G1.SAAT=G7.SAAT
WHERE
    (G1.SAAT >= (SELECT MIN(SBASSAAT)+2 FROM TASALONCALSAAT WHERE SALONID=@salonid) OR 
     G1.SAAT >= (SELECT MIN(RSAAT)+2 FROM HRANDEVU WHERE SALONID = @salonid AND RTARIH >= @GUN AND RTARIH <= @GUN+6)) AND 
    (G1.SAAT <  (SELECT MAX(OSBITSAAT)+2 FROM TASALONCALSAAT WHERE SALONID=@salonid) OR 
     G1.SAAT < (SELECT MAX(DATEADD(n,RSURE,RSAAT))+2 FROM HAMELIYATRANDEVU WHERE SALONID = @salonid AND RTARIH >= @GUN AND RTARIH <= @GUN+6))
          
GO

IF EXISTS (SELECT [name] FROM [sys].[database_principals] WHERE [type] = N'S' AND [name] = N'mediwebuser')
BEGIN
	BEGIN TRY
		GRANT SELECT ON [dbo].[RANDEVUHAFTAAMELIYAT] TO [mediwebuser]
	END TRY
	BEGIN CATCH
	END CATCH
END
GO


