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

CREATE FUNCTION [dbo].[RANDEVUHAFTA] (@dr int, @gun datetime, @AmeliyatRandevugoster BIT=1)    
RETURNS table AS    
return
(
	   WITH HRANDEVUTEMP AS (
		  SELECT KIMLIKID,RNEDENID,RSURE,RANDDURUMID,RACIKLAMA,ADISOYADI,TEL,HMUAYENEISLID,SIRANO,SGKULLANICI,WEB,RTARIH,RANDEVUID,RSAAT
		  FROM HRANDEVU WITH(NOLOCK)
		  WHERE  HRANDEVU.DRID = @dr 
			 AND RANDDURUMID NOT IN (5,6)
			 AND RTARIH >= @gun
			 AND RTARIH <  DATEADD(DAY,7,@gun)
			 AND (@AmeliyatRandevugoster = 1 OR HRANDEVU.SALONID IS NULL) 
	   )

	   SELECT G1.SAAT,G1.HASTA AS G1, G2.HASTA AS G2, G3.HASTA AS G3, G4.HASTA AS G4, G5.HASTA AS G5, G6.HASTA AS G6, G7.HASTA AS G7,   
				  G1.HMUAYENEISLID AS GM1, G2.HMUAYENEISLID AS GM2, G3.HMUAYENEISLID AS GM3, G4.HMUAYENEISLID AS GM4, G5.HMUAYENEISLID AS GM5, G6.HMUAYENEISLID AS GM6, G7.HMUAYENEISLID AS GM7,    
				  G1.RNEDENID AS G1RNEDENID, G2.RNEDENID AS G2RNEDENID, G3.RNEDENID AS G3RNEDENID, G4.RNEDENID AS G4RNEDENID, G5.RNEDENID AS G5RNEDENID, G6.RNEDENID AS G6RNEDENID, G7.RNEDENID AS G7RNEDENID,  
				  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.WEB AS G1WEB, G2.WEB AS G2WEB, G3.WEB AS G3WEB, G4.WEB AS G4WEB, G5.WEB AS G5WEB, G6.WEB AS G6WEB, G7.WEB AS G7WEB,  
				  G1.SIRANO+' '+G1.HASTA AS GS1, G2.SIRANO+' '+G2.HASTA AS GS2, G3.SIRANO+' '+G3.HASTA AS GS3,   
				  G4.SIRANO+' '+G4.HASTA AS GS4, G5.SIRANO+' '+G5.HASTA AS GS5, G6.SIRANO+' '+G6.HASTA AS GS6, G7.SIRANO+' '+G7.HASTA AS GS7,  
				  G1.RNEDENI AS GHRNEDEN1, G2.RNEDENI AS GHRNEDEN2, G3.RNEDENI AS GHRNEDEN3, G4.RNEDENI AS GHRNEDEN4, G5.RNEDENI AS GHRNEDEN5, G6.RNEDENI AS GHRNEDEN6, G7.RNEDENI AS GHRNEDEN7,  
			    G1.TEL AS GHRTEL1, G2.TEL AS GHRTEL2, G3.TEL AS GHRTEL3, G4.TEL AS GHRTEL4, G5.TEL AS GHRTEL5, G6.TEL AS GHRTEL6, G7.TEL AS GHRTEL7,   
				  G1.RACIKLAMA AS GRACIK1, G2.RACIKLAMA AS GRACIK2, G3.RACIKLAMA AS GRACIK3, G4.RACIKLAMA AS GRACIK4, G5.RACIKLAMA AS GRACIK5, G6.RACIKLAMA AS GRACIK6, G7.RACIKLAMA AS GRACIK7,   
				  G1.SGKULLANICI AS GSGKUL1, G2.SGKULLANICI AS GSGKUL2, G3.SGKULLANICI AS GSGKUL3, G4.SGKULLANICI AS GSGKUL4, G5.SGKULLANICI AS GSGKUL5, G6.SGKULLANICI AS GSGKUL6, G7.SGKULLANICI AS GSGKUL7, 
				  G1.RANDEVUID RANDEVUID1, G2.RANDEVUID RANDEVUID2, G3.RANDEVUID RANDEVUID3, G4.RANDEVUID RANDEVUID4, G5.RANDEVUID RANDEVUID5, G6.RANDEVUID RANDEVUID6, G7.RANDEVUID RANDEVUID7,
				  G1.HASTAGENEL HASTAGENEL1 ,G2.HASTAGENEL HASTAGENEL2,G3.HASTAGENEL HASTAGENEL3,G4.HASTAGENEL HASTAGENEL4,G5.HASTAGENEL HASTAGENEL5,G6.HASTAGENEL HASTAGENEL6,G7.HASTAGENEL HASTAGENEL7,
				  RD1,RD2,RD3,RD4,RD5,RD6,RD7
	   FROM   
  
	   -------------------------GÜN  1 ------------------------------  
	   (
		   SELECT	convert(nvarchar(5), TGUNSAAT.Saat, 14) AS Saat, RANDEVUID, HMUAYENEISLID AS HMUAYENEISLID, HRANDEVU.RNEDENID AS RNEDENID, RANDDURUMID, KIMLIKID, WEB,
					CASE
              WHEN RANDEVUID IS NOT NULL AND TGUNSAAT.Saat=RSAAT THEN ADISOYADI
              WHEN RANDEVUID IS NOT NULL AND TGUNSAAT.Saat<>RSAAT THEN '"'
              ELSE CASE
                       WHEN (ACIKLAMA IS NOT NULL) THEN '---'
                       WHEN (DETAYLICALSAATAKTIF=0)
                            AND ((TGUNSAAT.Saat>=TDRCALSAAT.SBASSAAT
                                  AND TGUNSAAT.Saat<TDRCALSAAT.SBITSAAT)
                                 OR (TGUNSAAT.Saat>=TDRCALSAAT.OSBASSAAT
                                     AND TGUNSAAT.Saat<TDRCALSAAT.OSBITSAAT))
                            AND (ACIKLAMA IS NULL) THEN NULL
                       WHEN (DETAYLICALSAATAKTIF=1)
                            AND ((TGUNSAAT.Saat>=TD.SBASSAAT
                                  AND TGUNSAAT.Saat<TD.SBITSAAT)
                                 OR (TGUNSAAT.Saat>=TD.OSBASSAAT
                                     AND TGUNSAAT.Saat<TD.OSBITSAAT))
                            AND (ACIKLAMA IS NULL) THEN NULL
                       ELSE '---'
                   END
          END AS HASTA,
		  CASE
			   WHEN RANDEVUID IS NOT NULL THEN ADISOYADI
			   ELSE CASE
						WHEN (ACIKLAMA IS NOT NULL) THEN '---'
						/*WHEN ((TGUNSAAT.Saat>=SBASSAAT
							   AND TGUNSAAT.Saat<SBITSAAT)
							  OR (TGUNSAAT.Saat>=OSBASSAAT
								  AND TGUNSAAT.Saat<OSBITSAAT))
							 AND (ACIKLAMA IS NULL) THEN NULL*/
						
						WHEN (DETAYLICALSAATAKTIF=0) AND ((TGUNSAAT.Saat>=TDRCALSAAT.SBASSAAT
							   AND TGUNSAAT.Saat<TDRCALSAAT.SBITSAAT)
							  OR (TGUNSAAT.Saat>=TDRCALSAAT.OSBASSAAT
								  AND TGUNSAAT.Saat<TDRCALSAAT.OSBITSAAT))
							 AND (ACIKLAMA IS NULL) THEN NULL

						WHEN (DETAYLICALSAATAKTIF=1) AND ((TGUNSAAT.Saat>=TD.SBASSAAT
							   AND TGUNSAAT.Saat<TD.SBITSAAT)
							  OR (TGUNSAAT.Saat>=TD.OSBASSAAT
								  AND TGUNSAAT.Saat<TD.OSBITSAAT))
							 AND (ACIKLAMA IS NULL) THEN NULL

						ELSE '---'
					END
		   END AS HASTAGENEL,
		 
					ISNULL(SIRANO, '') AS SIRANO, HRANDNEDEN.RNEDENI, HRANDEVU.TEL, HRANDEVU.RACIKLAMA, HRANDEVU.SGKULLANICI,RD1
			FROM TGUNSAAT WITH(NOLOCK)
			INNER JOIN TDRKIMLIK WITH(NOLOCK) ON TDRKIMLIK.DRID = @dr
			LEFT JOIN TDRCALSAAT WITH(NOLOCK) ON TDRCALSAAT.DRID=@dr AND HAFTAGUNU=DATEPART(dw, @gun)   
			LEFT JOIN TDRCALSAATDETAYLI TD WITH(NOLOCK) ON TD.DRID=@dr AND TD.TARIH=@gun
			LEFT OUTER JOIN HRANDEVUTEMP HRANDEVU WITH(NOLOCK) ON RTARIH =@gun 	AND TGUNSAAT.Saat>=RSAAT AND TGUNSAAT.Saat<dateadd(n, RSURE, RSAAT)
			LEFT OUTER JOIN TDRIZINLERI WITH(NOLOCK) ON TDRIZINLERI.DRID=@dr AND (TDRIZINLERI.BASTARIHI<=@gun) AND (@gun<TDRIZINLERI.BITISTARIHI)
			LEFT JOIN HRANDNEDEN WITH(NOLOCK) ON HRANDNEDEN.RNEDENID = HRANDEVU.RNEDENID
			LEFT JOIN (SELECT  RANDEVUID RD1   FROM HRANDEVU WITH(NOLOCK) WHERE HRANDEVU.RNEDENIDDIGER NOT IN (5,6) AND  (ISNULL(RADIDIGER,'')<>'' OR ISNULL(RSOYADDIGER,'')<>'') ) RDD1 ON  RD1=RANDEVUID
			WHERE CAST(
						ROUND(	CASE 
								WHEN SAAT < dbo.HaftalikOSIlkCalSaat(TDRKIMLIK.DRID) 
								THEN CAST(SAAT - dbo.HaftalikIlkCalSaat(TDRKIMLIK.DRID) AS FLOAT)
								ELSE CAST(SAAT - dbo.HaftalikOSIlkCalSaat(TDRKIMLIK.DRID) AS FLOAT)
								END * 24 * 60, 0
							)AS INT
					  ) % ISNULL(RANDEVUDAKIKA, 5) = 0
		) G1
	     
	   -------------------------GÜN  2 ------------------------------
	   INNER JOIN
	   (
			SELECT	convert(nvarchar(5), TGUNSAAT.Saat ,14) as Saat, RANDEVUID, HMUAYENEISLID AS HMUAYENEISLID, HRANDEVU.RNEDENID AS RNEDENID, RANDDURUMID, KIMLIKID, WEB,  
					CASE
              WHEN RANDEVUID IS NOT NULL AND TGUNSAAT.Saat=RSAAT THEN ADISOYADI
              WHEN RANDEVUID IS NOT NULL AND TGUNSAAT.Saat<>RSAAT THEN '"'
              ELSE CASE
                       WHEN (ACIKLAMA IS NOT NULL) THEN '---'
                       WHEN (DETAYLICALSAATAKTIF=0)
                            AND ((TGUNSAAT.Saat>=TDRCALSAAT.SBASSAAT
                                  AND TGUNSAAT.Saat<TDRCALSAAT.SBITSAAT)
                                 OR (TGUNSAAT.Saat>=TDRCALSAAT.OSBASSAAT
                                     AND TGUNSAAT.Saat<TDRCALSAAT.OSBITSAAT))
                            AND (ACIKLAMA IS NULL) THEN NULL
                       WHEN (DETAYLICALSAATAKTIF=1)
                            AND ((TGUNSAAT.Saat>=TD.SBASSAAT
                                  AND TGUNSAAT.Saat<TD.SBITSAAT)
                                 OR (TGUNSAAT.Saat>=TD.OSBASSAAT
                                     AND TGUNSAAT.Saat<TD.OSBITSAAT))
                            AND (ACIKLAMA IS NULL) THEN NULL
                       ELSE '---'
                   END
          END AS HASTA, 
		  CASE
			   WHEN RANDEVUID IS NOT NULL THEN ADISOYADI
			   ELSE CASE
						WHEN (ACIKLAMA IS NOT NULL) THEN '---'
						/*WHEN ((TGUNSAAT.Saat>=SBASSAAT
							   AND TGUNSAAT.Saat<SBITSAAT)
							  OR (TGUNSAAT.Saat>=OSBASSAAT
								  AND TGUNSAAT.Saat<OSBITSAAT))
							 AND (ACIKLAMA IS NULL) THEN NULL*/
						
						WHEN (DETAYLICALSAATAKTIF=0) AND ((TGUNSAAT.Saat>=TDRCALSAAT.SBASSAAT
							   AND TGUNSAAT.Saat<TDRCALSAAT.SBITSAAT)
							  OR (TGUNSAAT.Saat>=TDRCALSAAT.OSBASSAAT
								  AND TGUNSAAT.Saat<TDRCALSAAT.OSBITSAAT))
							 AND (ACIKLAMA IS NULL) THEN NULL

						WHEN (DETAYLICALSAATAKTIF=1) AND ((TGUNSAAT.Saat>=TD.SBASSAAT
							   AND TGUNSAAT.Saat<TD.SBITSAAT)
							  OR (TGUNSAAT.Saat>=TD.OSBASSAAT
								  AND TGUNSAAT.Saat<TD.OSBITSAAT))
							 AND (ACIKLAMA IS NULL) THEN NULL

						ELSE '---'
					END
		   END AS HASTAGENEL,
					ISNULL(SIRANO,'') AS SIRANO,HRANDNEDEN.RNEDENI,HRANDEVU.TEL,HRANDEVU.RACIKLAMA,HRANDEVU.SGKULLANICI,RD2  
			FROM TGUNSAAT WITH(NOLOCK) 
			INNER JOIN TDRKIMLIK WITH(NOLOCK) ON TDRKIMLIK.DRID = @dr 
			LEFT JOIN TDRCALSAAT WITH(NOLOCK) ON TDRCALSAAT.DRID=@dr and HAFTAGUNU=DATEPART(dw,DATEADD(d,1,@gun)) 
			LEFT JOIN TDRCALSAATDETAYLI TD WITH(NOLOCK) ON TD.DRID=@dr AND TD.TARIH=DATEADD(d,1,@gun)
			LEFT OUTER JOIN HRANDEVUTEMP HRANDEVU WITH(NOLOCK) ON RTARIH =DATEADD(d,1,@gun) AND TGUNSAAT.Saat>=RSAAT and TGUNSAAT.Saat<dateadd(n,RSURE,RSAAT) 
			LEFT OUTER JOIN TDRIZINLERI WITH(NOLOCK) ON TDRIZINLERI.DRID=@dr and (TDRIZINLERI.BASTARIHI<=DATEADD(d,1,@gun)) and (DATEADD(d,1,@gun)<TDRIZINLERI.BITISTARIHI) 
			LEFT JOIN HRANDNEDEN WITH(NOLOCK) ON HRANDNEDEN.RNEDENID = HRANDEVU.RNEDENID  
			LEFT JOIN (SELECT  RANDEVUID RD2 FROM HRANDEVU WITH(NOLOCK) WHERE HRANDEVU.RNEDENIDDIGER NOT IN (5,6) AND  (ISNULL(RADIDIGER,'')<>'' OR ISNULL(RSOYADDIGER,'')<>'') ) RDD2 ON  RD2=RANDEVUID

			WHERE CAST(
						ROUND(	CASE 
								WHEN SAAT < dbo.HaftalikOSIlkCalSaat(TDRKIMLIK.DRID) 
								THEN CAST(SAAT - dbo.HaftalikIlkCalSaat(TDRKIMLIK.DRID) AS FLOAT)   
								ELSE CAST(SAAT - dbo.HaftalikOSIlkCalSaat(TDRKIMLIK.DRID) AS FLOAT)   
								END * 24 * 60, 0
							)AS INT
					) % ISNULL(RANDEVUDAKIKA,5) = 0
		) G2   
	   ON G1.SAAT=G2.SAAT
	   
	   -------------------------GÜN  3 ------------------------------
	   INNER JOIN
	   (
			SELECT	convert(nvarchar(5), TGUNSAAT.Saat ,14) as Saat, RANDEVUID, HMUAYENEISLID AS HMUAYENEISLID, HRANDEVU.RNEDENID AS RNEDENID, RANDDURUMID, KIMLIKID, WEB,  
					CASE
              WHEN RANDEVUID IS NOT NULL AND TGUNSAAT.Saat=RSAAT THEN ADISOYADI
              WHEN RANDEVUID IS NOT NULL AND TGUNSAAT.Saat<>RSAAT THEN '"'
              ELSE CASE
                       WHEN (ACIKLAMA IS NOT NULL) THEN '---'
                       WHEN (DETAYLICALSAATAKTIF=0)
                            AND ((TGUNSAAT.Saat>=TDRCALSAAT.SBASSAAT
                                  AND TGUNSAAT.Saat<TDRCALSAAT.SBITSAAT)
                                 OR (TGUNSAAT.Saat>=TDRCALSAAT.OSBASSAAT
                                     AND TGUNSAAT.Saat<TDRCALSAAT.OSBITSAAT))
                            AND (ACIKLAMA IS NULL) THEN NULL
                       WHEN (DETAYLICALSAATAKTIF=1)
                            AND ((TGUNSAAT.Saat>=TD.SBASSAAT
                                  AND TGUNSAAT.Saat<TD.SBITSAAT)
                                 OR (TGUNSAAT.Saat>=TD.OSBASSAAT
                                     AND TGUNSAAT.Saat<TD.OSBITSAAT))
                            AND (ACIKLAMA IS NULL) THEN NULL
                       ELSE '---'
                   END
          END AS HASTA,
          CASE
			   WHEN RANDEVUID IS NOT NULL THEN ADISOYADI
			   ELSE CASE
						WHEN (ACIKLAMA IS NOT NULL) THEN '---'
						/*WHEN ((TGUNSAAT.Saat>=SBASSAAT
							   AND TGUNSAAT.Saat<SBITSAAT)
							  OR (TGUNSAAT.Saat>=OSBASSAAT
								  AND TGUNSAAT.Saat<OSBITSAAT))
							 AND (ACIKLAMA IS NULL) THEN NULL*/
						
						WHEN (DETAYLICALSAATAKTIF=0) AND ((TGUNSAAT.Saat>=TDRCALSAAT.SBASSAAT
							   AND TGUNSAAT.Saat<TDRCALSAAT.SBITSAAT)
							  OR (TGUNSAAT.Saat>=TDRCALSAAT.OSBASSAAT
								  AND TGUNSAAT.Saat<TDRCALSAAT.OSBITSAAT))
							 AND (ACIKLAMA IS NULL) THEN NULL

						WHEN (DETAYLICALSAATAKTIF=1) AND ((TGUNSAAT.Saat>=TD.SBASSAAT
							   AND TGUNSAAT.Saat<TD.SBITSAAT)
							  OR (TGUNSAAT.Saat>=TD.OSBASSAAT
								  AND TGUNSAAT.Saat<TD.OSBITSAAT))
							 AND (ACIKLAMA IS NULL) THEN NULL

						ELSE '---'
					END
		   END AS HASTAGENEL,
					ISNULL(SIRANO,'') AS SIRANO,HRANDNEDEN.RNEDENI,HRANDEVU.TEL,HRANDEVU.RACIKLAMA,HRANDEVU.SGKULLANICI ,RD3 
			FROM TGUNSAAT WITH(NOLOCK) 
			INNER JOIN TDRKIMLIK WITH(NOLOCK) ON TDRKIMLIK.DRID = @dr 
			LEFT JOIN TDRCALSAAT WITH(NOLOCK) ON TDRCALSAAT.DRID=@dr and HAFTAGUNU=DATEPART(dw,DATEADD(d,2,@gun)) 
			LEFT JOIN TDRCALSAATDETAYLI TD WITH(NOLOCK) ON TD.DRID=@dr AND TD.TARIH=DATEADD(d,2,@gun)
			LEFT OUTER JOIN HRANDEVUTEMP HRANDEVU WITH(NOLOCK) ON RTARIH =DATEADD(d,2,@gun) AND TGUNSAAT.Saat>=RSAAT and TGUNSAAT.Saat<dateadd(n,RSURE,RSAAT) 
			LEFT OUTER JOIN TDRIZINLERI WITH(NOLOCK) ON TDRIZINLERI.DRID=@dr and (TDRIZINLERI.BASTARIHI<=DATEADD(d,2,@gun)) and (DATEADD(d,2,@gun)<TDRIZINLERI.BITISTARIHI) 
			LEFT JOIN HRANDNEDEN WITH(NOLOCK) ON HRANDNEDEN.RNEDENID = HRANDEVU.RNEDENID  
			LEFT JOIN (SELECT  RANDEVUID RD3 FROM HRANDEVU WITH(NOLOCK) WHERE HRANDEVU.RNEDENIDDIGER NOT IN (5,6) AND  (ISNULL(RADIDIGER,'')<>'' OR ISNULL(RSOYADDIGER,'')<>'') ) RDD3 ON  RD3=RANDEVUID

			WHERE CAST(
						ROUND(	CASE 
								WHEN SAAT < dbo.HaftalikOSIlkCalSaat(TDRKIMLIK.DRID) 
								THEN CAST(SAAT - dbo.HaftalikIlkCalSaat(TDRKIMLIK.DRID) AS FLOAT)   
								ELSE CAST(SAAT - dbo.HaftalikOSIlkCalSaat(TDRKIMLIK.DRID) AS FLOAT)   
								END * 24 * 60, 0
							)AS INT
					) % ISNULL(RANDEVUDAKIKA,5) = 0
		) G3   
	   ON G1.SAAT=G3.SAAT
	   
	   -------------------------GÜN  4 ------------------------------
	   INNER JOIN
	   (	
			SELECT	convert(nvarchar(5), TGUNSAAT.Saat ,14) as Saat, RANDEVUID, HMUAYENEISLID AS HMUAYENEISLID, HRANDEVU.RNEDENID AS RNEDENID, RANDDURUMID, KIMLIKID, WEB,  
					CASE
              WHEN RANDEVUID IS NOT NULL AND TGUNSAAT.Saat=RSAAT THEN ADISOYADI
              WHEN RANDEVUID IS NOT NULL AND TGUNSAAT.Saat<>RSAAT THEN '"'
              ELSE CASE
                       WHEN (ACIKLAMA IS NOT NULL) THEN '---'
                       WHEN (DETAYLICALSAATAKTIF=0)
                            AND ((TGUNSAAT.Saat>=TDRCALSAAT.SBASSAAT
                                  AND TGUNSAAT.Saat<TDRCALSAAT.SBITSAAT)
                                 OR (TGUNSAAT.Saat>=TDRCALSAAT.OSBASSAAT
                                     AND TGUNSAAT.Saat<TDRCALSAAT.OSBITSAAT))
                            AND (ACIKLAMA IS NULL) THEN NULL
                       WHEN (DETAYLICALSAATAKTIF=1)
                            AND ((TGUNSAAT.Saat>=TD.SBASSAAT
                                  AND TGUNSAAT.Saat<TD.SBITSAAT)
                                 OR (TGUNSAAT.Saat>=TD.OSBASSAAT
                                     AND TGUNSAAT.Saat<TD.OSBITSAAT))
                            AND (ACIKLAMA IS NULL) THEN NULL
                       ELSE '---'
                   END
          END AS HASTA,
		  CASE
			   WHEN RANDEVUID IS NOT NULL THEN ADISOYADI
			   ELSE CASE
						WHEN (ACIKLAMA IS NOT NULL) THEN '---'
						/*WHEN ((TGUNSAAT.Saat>=SBASSAAT
							   AND TGUNSAAT.Saat<SBITSAAT)
							  OR (TGUNSAAT.Saat>=OSBASSAAT
								  AND TGUNSAAT.Saat<OSBITSAAT))
							 AND (ACIKLAMA IS NULL) THEN NULL*/
						
						WHEN (DETAYLICALSAATAKTIF=0) AND ((TGUNSAAT.Saat>=TDRCALSAAT.SBASSAAT
							   AND TGUNSAAT.Saat<TDRCALSAAT.SBITSAAT)
							  OR (TGUNSAAT.Saat>=TDRCALSAAT.OSBASSAAT
								  AND TGUNSAAT.Saat<TDRCALSAAT.OSBITSAAT))
							 AND (ACIKLAMA IS NULL) THEN NULL

						WHEN (DETAYLICALSAATAKTIF=1) AND ((TGUNSAAT.Saat>=TD.SBASSAAT
							   AND TGUNSAAT.Saat<TD.SBITSAAT)
							  OR (TGUNSAAT.Saat>=TD.OSBASSAAT
								  AND TGUNSAAT.Saat<TD.OSBITSAAT))
							 AND (ACIKLAMA IS NULL) THEN NULL

						ELSE '---'
					END
		   END AS HASTAGENEL,
					ISNULL(SIRANO,'') AS SIRANO, HRANDNEDEN.RNEDENI,HRANDEVU.TEL,HRANDEVU.RACIKLAMA,HRANDEVU.SGKULLANICI ,RD4 
			FROM TGUNSAAT WITH(NOLOCK) 
			INNER JOIN TDRKIMLIK WITH(NOLOCK) ON TDRKIMLIK.DRID = @dr 
			LEFT JOIN TDRCALSAAT WITH(NOLOCK) ON TDRCALSAAT.DRID=@dr and HAFTAGUNU=DATEPART(dw,DATEADD(d,3,@gun)) 
			LEFT JOIN TDRCALSAATDETAYLI TD WITH(NOLOCK) ON TD.DRID=@dr AND TD.TARIH=DATEADD(d,3,@gun)
			LEFT OUTER JOIN HRANDEVUTEMP HRANDEVU WITH(NOLOCK) ON RTARIH =DATEADD(d,3,@gun) AND TGUNSAAT.Saat>=RSAAT and TGUNSAAT.Saat<dateadd(n,RSURE,RSAAT) 
			LEFT OUTER JOIN TDRIZINLERI WITH(NOLOCK) ON TDRIZINLERI.DRID=@dr and (TDRIZINLERI.BASTARIHI<=DATEADD(d,3,@gun)) and (DATEADD(d,3,@gun)<TDRIZINLERI.BITISTARIHI) 
			LEFT JOIN HRANDNEDEN WITH(NOLOCK) ON HRANDNEDEN.RNEDENID = HRANDEVU.RNEDENID  
			LEFT JOIN (SELECT  RANDEVUID RD4 FROM HRANDEVU WITH(NOLOCK) WHERE HRANDEVU.RNEDENIDDIGER NOT IN (5,6) AND  (ISNULL(RADIDIGER,'')<>'' OR ISNULL(RSOYADDIGER,'')<>'') ) RDD4 ON  RD4=RANDEVUID
			WHERE	CAST(	
							ROUND(	CASE 
									WHEN SAAT < dbo.HaftalikOSIlkCalSaat(TDRKIMLIK.DRID) 
									THEN CAST(SAAT - dbo.HaftalikIlkCalSaat(TDRKIMLIK.DRID) AS FLOAT)   
									ELSE CAST(SAAT - dbo.HaftalikOSIlkCalSaat(TDRKIMLIK.DRID) AS FLOAT)   
									END * 24 * 60, 0
								)AS INT
						) % ISNULL(RANDEVUDAKIKA,5) = 0
		) G4   
	   ON G1.SAAT=G4.SAAT
	   
	   -------------------------GÜN  5 ------------------------------
	   INNER JOIN
	   (	
			SELECT	convert(nvarchar(5), TGUNSAAT.Saat ,14) as Saat, RANDEVUID, HMUAYENEISLID AS HMUAYENEISLID, HRANDEVU.RNEDENID AS RNEDENID, RANDDURUMID, KIMLIKID, WEB,  
					CASE
              WHEN RANDEVUID IS NOT NULL AND TGUNSAAT.Saat=RSAAT THEN ADISOYADI
              WHEN RANDEVUID IS NOT NULL AND TGUNSAAT.Saat<>RSAAT THEN '"'
              ELSE CASE
                       WHEN (ACIKLAMA IS NOT NULL) THEN '---'
                       WHEN (DETAYLICALSAATAKTIF=0)
                            AND ((TGUNSAAT.Saat>=TDRCALSAAT.SBASSAAT
                                  AND TGUNSAAT.Saat<TDRCALSAAT.SBITSAAT)
                                 OR (TGUNSAAT.Saat>=TDRCALSAAT.OSBASSAAT
                                     AND TGUNSAAT.Saat<TDRCALSAAT.OSBITSAAT))
                            AND (ACIKLAMA IS NULL) THEN NULL
                       WHEN (DETAYLICALSAATAKTIF=1)
                            AND ((TGUNSAAT.Saat>=TD.SBASSAAT
                                  AND TGUNSAAT.Saat<TD.SBITSAAT)
                                 OR (TGUNSAAT.Saat>=TD.OSBASSAAT
                                     AND TGUNSAAT.Saat<TD.OSBITSAAT))
                            AND (ACIKLAMA IS NULL) THEN NULL
                       ELSE '---'
                   END
          END AS HASTA,
		  CASE
			   WHEN RANDEVUID IS NOT NULL THEN ADISOYADI
			   ELSE CASE
						WHEN (ACIKLAMA IS NOT NULL) THEN '---'
						/*WHEN ((TGUNSAAT.Saat>=SBASSAAT
							   AND TGUNSAAT.Saat<SBITSAAT)
							  OR (TGUNSAAT.Saat>=OSBASSAAT
								  AND TGUNSAAT.Saat<OSBITSAAT))
							 AND (ACIKLAMA IS NULL) THEN NULL*/
						
						WHEN (DETAYLICALSAATAKTIF=0) AND ((TGUNSAAT.Saat>=TDRCALSAAT.SBASSAAT
							   AND TGUNSAAT.Saat<TDRCALSAAT.SBITSAAT)
							  OR (TGUNSAAT.Saat>=TDRCALSAAT.OSBASSAAT
								  AND TGUNSAAT.Saat<TDRCALSAAT.OSBITSAAT))
							 AND (ACIKLAMA IS NULL) THEN NULL

						WHEN (DETAYLICALSAATAKTIF=1) AND ((TGUNSAAT.Saat>=TD.SBASSAAT
							   AND TGUNSAAT.Saat<TD.SBITSAAT)
							  OR (TGUNSAAT.Saat>=TD.OSBASSAAT
								  AND TGUNSAAT.Saat<TD.OSBITSAAT))
							 AND (ACIKLAMA IS NULL) THEN NULL

						ELSE '---'
					END
		   END AS HASTAGENEL,
					ISNULL(SIRANO,'') AS SIRANO, HRANDNEDEN.RNEDENI,HRANDEVU.TEL,HRANDEVU.RACIKLAMA,HRANDEVU.SGKULLANICI,RD5  
			FROM TGUNSAAT WITH(NOLOCK) 
			INNER JOIN TDRKIMLIK WITH(NOLOCK) ON TDRKIMLIK.DRID = @dr 
			LEFT JOIN TDRCALSAAT WITH(NOLOCK) ON TDRCALSAAT.DRID=@dr and HAFTAGUNU=DATEPART(dw,DATEADD(d,4,@gun)) 
			LEFT JOIN TDRCALSAATDETAYLI TD WITH(NOLOCK) ON TD.DRID=@dr AND TD.TARIH=DATEADD(d,4,@gun)	   
			LEFT OUTER JOIN HRANDEVUTEMP HRANDEVU WITH(NOLOCK) ON RTARIH =DATEADD(d,4,@gun) AND TGUNSAAT.Saat>=RSAAT and TGUNSAAT.Saat<dateadd(n,RSURE,RSAAT) 
			LEFT OUTER JOIN TDRIZINLERI WITH(NOLOCK) ON TDRIZINLERI.DRID=@dr and (TDRIZINLERI.BASTARIHI<=DATEADD(d,4,@gun)) and (DATEADD(d,4,@gun)<TDRIZINLERI.BITISTARIHI) 
			LEFT JOIN HRANDNEDEN WITH(NOLOCK) ON HRANDNEDEN.RNEDENID = HRANDEVU.RNEDENID 
			LEFT JOIN (SELECT  RANDEVUID RD5 FROM HRANDEVU WITH(NOLOCK) WHERE HRANDEVU.RNEDENIDDIGER NOT IN (5,6) AND  (ISNULL(RADIDIGER,'')<>'' OR ISNULL(RSOYADDIGER,'')<>'') ) RDD5 ON  RD5=RANDEVUID 
			WHERE	CAST(
							ROUND(	CASE 
									WHEN SAAT < dbo.HaftalikOSIlkCalSaat(TDRKIMLIK.DRID) 
									THEN CAST(SAAT - dbo.HaftalikIlkCalSaat(TDRKIMLIK.DRID) AS FLOAT)   
									ELSE CAST(SAAT - dbo.HaftalikOSIlkCalSaat(TDRKIMLIK.DRID) AS FLOAT)   
									END * 24 * 60, 0
								)AS INT
						) % ISNULL(RANDEVUDAKIKA,5) = 0
		) G5   
	   ON G1.SAAT=G5.SAAT 
	   
	   -------------------------GÜN  6 ------------------------------
	   INNER JOIN
	   (
			SELECT	convert(nvarchar(5), TGUNSAAT.Saat ,14) as Saat, RANDEVUID, HMUAYENEISLID AS HMUAYENEISLID, HRANDEVU.RNEDENID AS RNEDENID, RANDDURUMID, KIMLIKID, WEB,  
					CASE
              WHEN RANDEVUID IS NOT NULL AND TGUNSAAT.Saat=RSAAT THEN ADISOYADI
              WHEN RANDEVUID IS NOT NULL AND TGUNSAAT.Saat<>RSAAT THEN '"'
              ELSE CASE
                       WHEN (ACIKLAMA IS NOT NULL) THEN '---'
                       WHEN (DETAYLICALSAATAKTIF=0)
                            AND ((TGUNSAAT.Saat>=TDRCALSAAT.SBASSAAT
                                  AND TGUNSAAT.Saat<TDRCALSAAT.SBITSAAT)
                                 OR (TGUNSAAT.Saat>=TDRCALSAAT.OSBASSAAT
                                     AND TGUNSAAT.Saat<TDRCALSAAT.OSBITSAAT))
                            AND (ACIKLAMA IS NULL) THEN NULL
                       WHEN (DETAYLICALSAATAKTIF=1)
                            AND ((TGUNSAAT.Saat>=TD.SBASSAAT
                                  AND TGUNSAAT.Saat<TD.SBITSAAT)
                                 OR (TGUNSAAT.Saat>=TD.OSBASSAAT
                                     AND TGUNSAAT.Saat<TD.OSBITSAAT))
                            AND (ACIKLAMA IS NULL) THEN NULL
                       ELSE '---'
                   END
          END AS HASTA,
		  CASE
			   WHEN RANDEVUID IS NOT NULL THEN ADISOYADI
			   ELSE CASE
						WHEN (ACIKLAMA IS NOT NULL) THEN '---'
						/*WHEN ((TGUNSAAT.Saat>=SBASSAAT
							   AND TGUNSAAT.Saat<SBITSAAT)
							  OR (TGUNSAAT.Saat>=OSBASSAAT
								  AND TGUNSAAT.Saat<OSBITSAAT))
							 AND (ACIKLAMA IS NULL) THEN NULL*/
						
						WHEN (DETAYLICALSAATAKTIF=0) AND ((TGUNSAAT.Saat>=TDRCALSAAT.SBASSAAT
							   AND TGUNSAAT.Saat<TDRCALSAAT.SBITSAAT)
							  OR (TGUNSAAT.Saat>=TDRCALSAAT.OSBASSAAT
								  AND TGUNSAAT.Saat<TDRCALSAAT.OSBITSAAT))
							 AND (ACIKLAMA IS NULL) THEN NULL

						WHEN (DETAYLICALSAATAKTIF=1) AND ((TGUNSAAT.Saat>=TD.SBASSAAT
							   AND TGUNSAAT.Saat<TD.SBITSAAT)
							  OR (TGUNSAAT.Saat>=TD.OSBASSAAT
								  AND TGUNSAAT.Saat<TD.OSBITSAAT))
							 AND (ACIKLAMA IS NULL) THEN NULL

						ELSE '---'
					END
		   END AS HASTAGENEL,
					ISNULL(SIRANO,'') AS SIRANO, HRANDNEDEN.RNEDENI,HRANDEVU.TEL,HRANDEVU.RACIKLAMA,HRANDEVU.SGKULLANICI,RD6  
			FROM TGUNSAAT WITH(NOLOCK) 
			INNER JOIN TDRKIMLIK WITH(NOLOCK) ON TDRKIMLIK.DRID = @dr 
			LEFT JOIN TDRCALSAAT WITH(NOLOCK) ON TDRCALSAAT.DRID=@dr and HAFTAGUNU=DATEPART(dw,DATEADD(d,5,@gun)) 
			LEFT JOIN TDRCALSAATDETAYLI TD WITH(NOLOCK) ON TD.DRID=@dr AND TD.TARIH=DATEADD(d,5,@gun)
			LEFT OUTER JOIN HRANDEVUTEMP HRANDEVU WITH(NOLOCK) ON RTARIH =DATEADD(d,5,@gun) AND TGUNSAAT.Saat>=RSAAT and TGUNSAAT.Saat<dateadd(n,RSURE,RSAAT) 
			LEFT OUTER JOIN TDRIZINLERI WITH(NOLOCK) ON TDRIZINLERI.DRID=@dr and (TDRIZINLERI.BASTARIHI<=DATEADD(d,5,@gun)) and (DATEADD(d,5,@gun)<TDRIZINLERI.BITISTARIHI)
			LEFT JOIN HRANDNEDEN WITH(NOLOCK) ON HRANDNEDEN.RNEDENID = HRANDEVU.RNEDENID  
			LEFT JOIN (SELECT  RANDEVUID RD6 FROM HRANDEVU WITH(NOLOCK) WHERE HRANDEVU.RNEDENIDDIGER NOT IN (5,6) AND  (ISNULL(RADIDIGER,'')<>'' OR ISNULL(RSOYADDIGER,'')<>'') ) RDD6 ON  RD6=RANDEVUID
			WHERE	CAST(
							ROUND(	CASE 
									WHEN SAAT < dbo.HaftalikOSIlkCalSaat(TDRKIMLIK.DRID) 
									THEN CAST(SAAT - dbo.HaftalikIlkCalSaat(TDRKIMLIK.DRID) AS FLOAT)   
									ELSE CAST(SAAT - dbo.HaftalikOSIlkCalSaat(TDRKIMLIK.DRID) AS FLOAT)   
									END * 24 * 60, 0
								)AS INT
						) % ISNULL(RANDEVUDAKIKA,5) = 0
		) G6   
	   ON G1.SAAT=G6.SAAT 
	   
	   -------------------------GÜN  7 ------------------------------
	   INNER JOIN
	   (
			SELECT	convert(nvarchar(5), TGUNSAAT.Saat ,14) as Saat, RANDEVUID, HMUAYENEISLID AS HMUAYENEISLID, HRANDEVU.RNEDENID AS RNEDENID, RANDDURUMID, KIMLIKID, WEB,  
					CASE
              WHEN RANDEVUID IS NOT NULL AND TGUNSAAT.Saat=RSAAT THEN ADISOYADI
              WHEN RANDEVUID IS NOT NULL AND TGUNSAAT.Saat<>RSAAT THEN '"'
              ELSE CASE
                       WHEN (ACIKLAMA IS NOT NULL) THEN '---'
                       WHEN (DETAYLICALSAATAKTIF=0)
                            AND ((TGUNSAAT.Saat>=TDRCALSAAT.SBASSAAT
                                  AND TGUNSAAT.Saat<TDRCALSAAT.SBITSAAT)
                                 OR (TGUNSAAT.Saat>=TDRCALSAAT.OSBASSAAT
                                     AND TGUNSAAT.Saat<TDRCALSAAT.OSBITSAAT))
                            AND (ACIKLAMA IS NULL) THEN NULL
                       WHEN (DETAYLICALSAATAKTIF=1)
                            AND ((TGUNSAAT.Saat>=TD.SBASSAAT
                                  AND TGUNSAAT.Saat<TD.SBITSAAT)
                                 OR (TGUNSAAT.Saat>=TD.OSBASSAAT
                                     AND TGUNSAAT.Saat<TD.OSBITSAAT))
                            AND (ACIKLAMA IS NULL) THEN NULL
                       ELSE '---'
                   END
          END AS HASTA,
		  CASE
			   WHEN RANDEVUID IS NOT NULL THEN ADISOYADI
			   ELSE CASE
						WHEN (ACIKLAMA IS NOT NULL) THEN '---'
						/*WHEN ((TGUNSAAT.Saat>=SBASSAAT
							   AND TGUNSAAT.Saat<SBITSAAT)
							  OR (TGUNSAAT.Saat>=OSBASSAAT
								  AND TGUNSAAT.Saat<OSBITSAAT))
							 AND (ACIKLAMA IS NULL) THEN NULL*/
						
						WHEN (DETAYLICALSAATAKTIF=0) AND ((TGUNSAAT.Saat>=TDRCALSAAT.SBASSAAT
							   AND TGUNSAAT.Saat<TDRCALSAAT.SBITSAAT)
							  OR (TGUNSAAT.Saat>=TDRCALSAAT.OSBASSAAT
								  AND TGUNSAAT.Saat<TDRCALSAAT.OSBITSAAT))
							 AND (ACIKLAMA IS NULL) THEN NULL

						WHEN (DETAYLICALSAATAKTIF=1) AND ((TGUNSAAT.Saat>=TD.SBASSAAT
							   AND TGUNSAAT.Saat<TD.SBITSAAT)
							  OR (TGUNSAAT.Saat>=TD.OSBASSAAT
								  AND TGUNSAAT.Saat<TD.OSBITSAAT))
							 AND (ACIKLAMA IS NULL) THEN NULL

						ELSE '---'
					END
		   END AS HASTAGENEL,
					ISNULL(SIRANO,'') AS SIRANO,HRANDNEDEN.RNEDENI,HRANDEVU.TEL,HRANDEVU.RACIKLAMA,HRANDEVU.SGKULLANICI ,RD7 
			FROM TGUNSAAT WITH(NOLOCK) 
			INNER JOIN TDRKIMLIK WITH(NOLOCK) ON TDRKIMLIK.DRID = @dr 
			LEFT JOIN TDRCALSAAT WITH(NOLOCK) ON TDRCALSAAT.DRID=@dr and HAFTAGUNU=DATEPART(dw,DATEADD(d,6,@gun)) 
			LEFT JOIN TDRCALSAATDETAYLI TD WITH(NOLOCK) ON TD.DRID=@dr AND TD.TARIH=DATEADD(d,6,@gun)
			LEFT OUTER JOIN HRANDEVUTEMP HRANDEVU WITH(NOLOCK) ON RTARIH =DATEADD(d,6,@gun) AND TGUNSAAT.Saat>=RSAAT and TGUNSAAT.Saat<dateadd(n,RSURE,RSAAT) 
			LEFT OUTER JOIN TDRIZINLERI WITH(NOLOCK) ON TDRIZINLERI.DRID=@dr and (TDRIZINLERI.BASTARIHI<=DATEADD(d,6,@gun)) and (DATEADD(d,6,@gun)<TDRIZINLERI.BITISTARIHI) 
			LEFT JOIN HRANDNEDEN WITH(NOLOCK) ON HRANDNEDEN.RNEDENID = HRANDEVU.RNEDENID  
			LEFT JOIN (SELECT  RANDEVUID RD7 FROM HRANDEVU WITH(NOLOCK) WHERE HRANDEVU.RNEDENIDDIGER NOT IN (5,6) AND  (ISNULL(RADIDIGER,'')<>'' OR ISNULL(RSOYADDIGER,'')<>'') ) RDD7 ON  RD7=RANDEVUID
			WHERE	CAST(
							ROUND(	CASE 
									WHEN SAAT < dbo.HaftalikOSIlkCalSaat(TDRKIMLIK.DRID) 
									THEN CAST(SAAT - dbo.HaftalikIlkCalSaat(TDRKIMLIK.DRID) AS FLOAT)   
									ELSE CAST(SAAT - dbo.HaftalikOSIlkCalSaat(TDRKIMLIK.DRID) AS FLOAT)   
									END * 24 * 60, 0
								)AS INT
						) % ISNULL(RANDEVUDAKIKA,5) = 0
		) G7   
	   ON G1.SAAT=G7.SAAT  
	   WHERE  
		 
		  ((G1.SAAT >= (SELECT MIN(SBASSAAT)+2 FROM TDRCALSAAT WHERE DRID=@dr) OR   
		   G1.SAAT >= (SELECT MIN(RSAAT)+2 FROM HRANDEVU WHERE DRID = @DR AND RTARIH >= @GUN AND RTARIH <= @GUN+6)) AND   
		  (G1.SAAT <  (SELECT MAX(OSBITSAAT)+2 FROM TDRCALSAAT WHERE DRID=@dr) OR   
		   G1.SAAT < (SELECT MAX(DATEADD(n,RSURE,RSAAT))+2 FROM HRANDEVU WHERE DRID = @DR AND RTARIH >= @GUN AND RTARIH <= @GUN+6))  )

		   or
		 ((G1.SAAT >= (SELECT MIN(SBASSAAT)+2 FROM TDRCALSAATDETAYLI WHERE DRID=@dr) OR   
		   G1.SAAT >= (SELECT MIN(RSAAT)+2 FROM HRANDEVU WHERE DRID = @DR AND RTARIH >= @GUN AND RTARIH <= @GUN+6)) AND   
		  (G1.SAAT <  (SELECT MAX(OSBITSAAT)+2 FROM TDRCALSAATDETAYLI WHERE DRID=@dr) OR   
		   G1.SAAT < (SELECT MAX(DATEADD(n,RSURE,RSAAT))+2 FROM HRANDEVU WHERE DRID = @DR 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].[RANDEVUHAFTA] TO [mediwebuser]
	END TRY
	BEGIN CATCH
	END CATCH
END
GO


