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



CREATE   FUNCTION [dbo].[RANDEVUHAFTAFTR] (@FTRYERIODAID int, @GUN datetime)  
RETURNS table AS  
return
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.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.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.RANDEVUID AS G1RANDEVUID, G2.RANDEVUID AS G2RANDEVUID, G3.RANDEVUID AS G3RANDEVUID, G4.RANDEVUID AS G4RANDEVUID, G5.RANDEVUID AS G5RANDEVUID, G6.RANDEVUID AS G6RANDEVUID, G7.RANDEVUID AS G7RANDEVUID,
			  G1.HRAPORID AS G1HRAPORID, G2.HRAPORID AS G2HRAPORID, G3.HRAPORID AS G3HRAPORID, G4.HRAPORID AS G4HRAPORID, G5.HRAPORID AS G5HRAPORID, G6.HRAPORID AS G6HRAPORID, G7.HRAPORID AS G7HRAPORID,
              G1.HASTA AS GS1, G2.HASTA AS GS2, G3.HASTA AS GS3, G4.HASTA AS GS4, G5.HASTA AS GS5, G6.HASTA AS GS6, G7.HASTA AS GS7,
              G1.DRADI AS G1DRADI,G2.DRADI AS G2DRADI,G3.DRADI AS G3DRADI,G4.DRADI AS G4DRADI,G5.DRADI AS G5DRADI,G6.DRADI AS G6DRADI,G7.DRADI AS G7DRADI,
              G1.SPADI AS G1SPADI,G2.SPADI AS G2SPADI,G3.SPADI AS G3SPADI,G4.SPADI AS G4SPADI,G5.SPADI AS G5SPADI,G6.SPADI AS G6SPADI,G7.SPADI AS G7SPADI,
              G1.ODA AS G1ODA,G2.ODA AS G2ODA,G3.ODA AS G3ODA,G4.ODA AS G4ODA,G5.ODA AS G5ODA,G6.ODA AS G6ODA,G7.ODA AS G7ODA
              
FROM 

-------------------------GÜN  1 ------------------------------
(SELECT convert(nvarchar(5), TGUNSAATFTR.Saat ,14) as Saat, RANDEVUID, RANDDURUMID,KIMLIKID, HRAPORID,
       CASE WHEN HKIMLIKID IS NOT NULL THEN (HADI+' '+HSOYADI) 
            ELSE 
               CASE WHEN  (ACIKLAMA IS NOT NULL) THEN ACIKLAMA
		    WHEN ((TGUNSAATFTR.Saat>=SBASSAAT and TGUNSAATFTR.Saat<SBITSAAT) OR (TGUNSAATFTR.Saat>=OSBASSAAT and TGUNSAATFTR.Saat<OSBITSAAT)) AND (ACIKLAMA IS NULL) THEN NULL 
                ELSE '---' 
           END 
       END AS HASTA,TDRKIMLIK.DRADI,TSAGLIKP.SPADI,TANIM As ODA
FROM TGUNSAATFTR WITH(NOLOCK) INNER JOIN 
     FTRYERIODA WITH(NOLOCK) ON FTRYERIODA.FTRYERIODAID = @FTRYERIODAID LEFT JOIN 
     FTRYERIODACALSAAT WITH(NOLOCK) ON FTRYERIODACALSAAT.FTRYERIODAID=@FTRYERIODAID and FTRYERIODACALSAAT.BASTARIH<=@gun and FTRYERIODACALSAAT.BITTARIH>=@gun  and HAFTAGUNU=DATEPART(dw,@gun) LEFT OUTER JOIN
     HRANDEVUFTR WITH(NOLOCK) ON HRANDEVUFTR.FTRYERIODAID = @FTRYERIODAID AND RTARIH =@gun AND TGUNSAATFTR.Saat>=RSAAT and TGUNSAATFTR.Saat<dateadd(n,RSURE,RSAAT) AND RANDDURUMID<7 LEFT OUTER JOIN  
     HKIMLIK WITH(NOLOCK) ON HRANDEVUFTR.KIMLIKID=HKIMLIK.HKIMLIKID LEFT OUTER JOIN 
     TDRKIMLIK WITH(NOLOCK) ON HRANDEVUFTR.DRID=TDRKIMLIK.DRID LEFT OUTER JOIN 
     TSAGLIKP WITH(NOLOCK) ON HRANDEVUFTR.TERAPISTID=TSAGLIKP.SPID
WHERE CAST(ROUND(CAST(CASE WHEN FTRYERIODACALSAAT.FTRYERIODAID IS NULL THEN SAAT ELSE CASE WHEN SAAT < OSBASSAAT THEN SAAT-SBASSAAT ELSE SAAT - OSBASSAAT END END AS FLOAT)  * 24*60,0)AS INT)%ISNULL(FTRYERIODA.RANDEVUDAKIKA,60) = 0 ) G1 
INNER JOIN
-------------------------GÜN  2 ------------------------------
(SELECT convert(nvarchar(5), TGUNSAATFTR.Saat ,14) as Saat, RANDEVUID,RANDDURUMID,KIMLIKID, HRAPORID,
       CASE WHEN HKIMLIKID IS NOT NULL THEN (HADI+' '+HSOYADI) 
            ELSE 
               CASE WHEN  (ACIKLAMA IS NOT NULL) THEN ACIKLAMA
		    WHEN ((TGUNSAATFTR.Saat>=SBASSAAT and TGUNSAATFTR.Saat<SBITSAAT) OR (TGUNSAATFTR.Saat>=OSBASSAAT and TGUNSAATFTR.Saat<OSBITSAAT)) AND (ACIKLAMA IS NULL) THEN NULL 
                ELSE '---' 
           END 
       END AS HASTA,TDRKIMLIK.DRADI,TSAGLIKP.SPADI,TANIM As ODA
FROM TGUNSAATFTR WITH(NOLOCK) INNER JOIN 
     FTRYERIODA WITH(NOLOCK) ON FTRYERIODA.FTRYERIODAID = @FTRYERIODAID LEFT JOIN 
     FTRYERIODACALSAAT WITH(NOLOCK) ON FTRYERIODACALSAAT.FTRYERIODAID=@FTRYERIODAID and FTRYERIODACALSAAT.BASTARIH<=DATEADD(d,1,@gun) and FTRYERIODACALSAAT.BITTARIH>=DATEADD(d,1,@gun) and HAFTAGUNU=DATEPART(dw,DATEADD(d,1,@gun)) LEFT OUTER JOIN
     HRANDEVUFTR WITH(NOLOCK) ON HRANDEVUFTR.FTRYERIODAID = @FTRYERIODAID AND RTARIH =DATEADD(d,1,@gun) AND TGUNSAATFTR.Saat>=RSAAT and TGUNSAATFTR.Saat<dateadd(n,RSURE,RSAAT) AND RANDDURUMID<7 LEFT OUTER JOIN  
     HKIMLIK WITH(NOLOCK) ON HRANDEVUFTR.KIMLIKID=HKIMLIK.HKIMLIKID LEFT OUTER JOIN 
     TDRKIMLIK WITH(NOLOCK) ON HRANDEVUFTR.DRID=TDRKIMLIK.DRID LEFT OUTER JOIN 
     TSAGLIKP WITH(NOLOCK) ON HRANDEVUFTR.TERAPISTID=TSAGLIKP.SPID
WHERE CAST(ROUND(CAST(CASE WHEN FTRYERIODACALSAAT.FTRYERIODAID IS NULL THEN SAAT ELSE CASE WHEN SAAT < OSBASSAAT THEN SAAT-SBASSAAT ELSE SAAT - OSBASSAAT END END AS FLOAT)  * 24*60,0)AS INT)%ISNULL(FTRYERIODA.RANDEVUDAKIKA,60) = 0 ) G2 
ON G1.SAAT=G2.SAAT INNER JOIN
-------------------------GÜN  3 ------------------------------
(SELECT convert(nvarchar(5), TGUNSAATFTR.Saat ,14) as Saat, RANDEVUID, RANDDURUMID,KIMLIKID, HRAPORID,
       CASE WHEN HKIMLIKID IS NOT NULL THEN (HADI+' '+HSOYADI) 
            ELSE 
               CASE WHEN  (ACIKLAMA IS NOT NULL) THEN ACIKLAMA
		    WHEN ((TGUNSAATFTR.Saat>=SBASSAAT and TGUNSAATFTR.Saat<SBITSAAT) OR (TGUNSAATFTR.Saat>=OSBASSAAT and TGUNSAATFTR.Saat<OSBITSAAT)) AND (ACIKLAMA IS NULL) THEN NULL 
                ELSE '---' 
           END 
       END AS HASTA,TDRKIMLIK.DRADI,TSAGLIKP.SPADI,TANIM As ODA
FROM TGUNSAATFTR WITH(NOLOCK) INNER JOIN 
     FTRYERIODA WITH(NOLOCK) ON FTRYERIODA.FTRYERIODAID = @FTRYERIODAID LEFT JOIN 
     FTRYERIODACALSAAT WITH(NOLOCK) ON FTRYERIODACALSAAT.FTRYERIODAID=@FTRYERIODAID and FTRYERIODACALSAAT.BASTARIH<=DATEADD(d,2,@gun) and FTRYERIODACALSAAT.BITTARIH>=DATEADD(d,2,@gun) and HAFTAGUNU=DATEPART(dw,DATEADD(d,2,@gun)) LEFT OUTER JOIN
     HRANDEVUFTR WITH(NOLOCK) ON HRANDEVUFTR.FTRYERIODAID = @FTRYERIODAID AND RTARIH =DATEADD(d,2,@gun) AND TGUNSAATFTR.Saat>=RSAAT and TGUNSAATFTR.Saat<dateadd(n,RSURE,RSAAT) AND RANDDURUMID<7 LEFT OUTER JOIN  
     HKIMLIK WITH(NOLOCK) ON HRANDEVUFTR.KIMLIKID=HKIMLIK.HKIMLIKID LEFT OUTER JOIN 
     TDRKIMLIK WITH(NOLOCK) ON HRANDEVUFTR.DRID=TDRKIMLIK.DRID LEFT OUTER JOIN 
     TSAGLIKP WITH(NOLOCK) ON HRANDEVUFTR.TERAPISTID=TSAGLIKP.SPID
WHERE CAST(ROUND(CAST(CASE WHEN FTRYERIODACALSAAT.FTRYERIODAID IS NULL THEN SAAT ELSE CASE WHEN SAAT < OSBASSAAT THEN SAAT-SBASSAAT ELSE SAAT - OSBASSAAT END END AS FLOAT)  * 24*60,0)AS INT)%ISNULL(FTRYERIODA.RANDEVUDAKIKA,60) = 0 ) G3 
ON G1.SAAT=G3.SAAT INNER JOIN
-------------------------GÜN  4 ------------------------------
(SELECT convert(nvarchar(5), TGUNSAATFTR.Saat ,14) as Saat, RANDEVUID, RANDDURUMID,KIMLIKID, HRAPORID,
       CASE WHEN HKIMLIKID IS NOT NULL THEN (HADI+' '+HSOYADI) 
            ELSE 
               CASE WHEN  (ACIKLAMA IS NOT NULL) THEN ACIKLAMA
		    WHEN ((TGUNSAATFTR.Saat>=SBASSAAT and TGUNSAATFTR.Saat<SBITSAAT) OR (TGUNSAATFTR.Saat>=OSBASSAAT and TGUNSAATFTR.Saat<OSBITSAAT)) AND (ACIKLAMA IS NULL) THEN NULL 
                ELSE '---' 
           END 
       END AS HASTA,TDRKIMLIK.DRADI,TSAGLIKP.SPADI,TANIM As ODA
FROM TGUNSAATFTR WITH(NOLOCK) INNER JOIN 
     FTRYERIODA WITH(NOLOCK) ON FTRYERIODA.FTRYERIODAID = @FTRYERIODAID LEFT JOIN 
     FTRYERIODACALSAAT WITH(NOLOCK) ON FTRYERIODACALSAAT.FTRYERIODAID=@FTRYERIODAID and FTRYERIODACALSAAT.BASTARIH<=DATEADD(d,3,@gun) and FTRYERIODACALSAAT.BITTARIH>=DATEADD(d,3,@gun) and HAFTAGUNU=DATEPART(dw,DATEADD(d,3,@gun)) LEFT OUTER JOIN
     HRANDEVUFTR WITH(NOLOCK) ON HRANDEVUFTR.FTRYERIODAID = @FTRYERIODAID AND RTARIH =DATEADD(d,3,@gun) AND TGUNSAATFTR.Saat>=RSAAT and TGUNSAATFTR.Saat<dateadd(n,RSURE,RSAAT) AND RANDDURUMID<7 LEFT OUTER JOIN  
     HKIMLIK WITH(NOLOCK) ON HRANDEVUFTR.KIMLIKID=HKIMLIK.HKIMLIKID LEFT OUTER JOIN 
     TDRKIMLIK WITH(NOLOCK) ON HRANDEVUFTR.DRID=TDRKIMLIK.DRID LEFT OUTER JOIN 
     TSAGLIKP WITH(NOLOCK) ON HRANDEVUFTR.TERAPISTID=TSAGLIKP.SPID
WHERE CAST(ROUND(CAST(CASE WHEN FTRYERIODACALSAAT.FTRYERIODAID IS NULL THEN SAAT ELSE CASE WHEN SAAT < OSBASSAAT THEN SAAT-SBASSAAT ELSE SAAT - OSBASSAAT END END AS FLOAT)  * 24*60,0)AS INT)%ISNULL(FTRYERIODA.RANDEVUDAKIKA,60) = 0 ) G4 
ON G1.SAAT=G4.SAAT INNER JOIN
-------------------------GÜN  5 ------------------------------
(SELECT convert(nvarchar(5), TGUNSAATFTR.Saat ,14) as Saat, RANDEVUID, RANDDURUMID,KIMLIKID, HRAPORID,
       CASE WHEN HKIMLIKID IS NOT NULL THEN (HADI+' '+HSOYADI) 
            ELSE 
               CASE WHEN  (ACIKLAMA IS NOT NULL) THEN ACIKLAMA
		    WHEN ((TGUNSAATFTR.Saat>=SBASSAAT and TGUNSAATFTR.Saat<SBITSAAT) OR (TGUNSAATFTR.Saat>=OSBASSAAT and TGUNSAATFTR.Saat<OSBITSAAT)) AND (ACIKLAMA IS NULL) THEN NULL 
                ELSE '---' 
           END 
       END AS HASTA,TDRKIMLIK.DRADI,TSAGLIKP.SPADI,TANIM As ODA
FROM TGUNSAATFTR WITH(NOLOCK) INNER JOIN 
     FTRYERIODA WITH(NOLOCK) ON FTRYERIODA.FTRYERIODAID = @FTRYERIODAID LEFT JOIN 
     FTRYERIODACALSAAT WITH(NOLOCK) ON FTRYERIODACALSAAT.FTRYERIODAID=@FTRYERIODAID and FTRYERIODACALSAAT.BASTARIH<=DATEADD(d,4,@gun) and FTRYERIODACALSAAT.BITTARIH>=DATEADD(d,4,@gun) and HAFTAGUNU=DATEPART(dw,DATEADD(d,4,@gun)) LEFT OUTER JOIN
     HRANDEVUFTR WITH(NOLOCK) ON HRANDEVUFTR.FTRYERIODAID = @FTRYERIODAID AND RTARIH =DATEADD(d,4,@gun) AND TGUNSAATFTR.Saat>=RSAAT and TGUNSAATFTR.Saat<dateadd(n,RSURE,RSAAT) AND RANDDURUMID<7 LEFT OUTER JOIN 
     HKIMLIK WITH(NOLOCK) ON HRANDEVUFTR.KIMLIKID=HKIMLIK.HKIMLIKID LEFT OUTER JOIN 
     TDRKIMLIK WITH(NOLOCK) ON HRANDEVUFTR.DRID=TDRKIMLIK.DRID LEFT OUTER JOIN 
     TSAGLIKP WITH(NOLOCK) ON HRANDEVUFTR.TERAPISTID=TSAGLIKP.SPID
WHERE CAST(ROUND(CAST(CASE WHEN FTRYERIODACALSAAT.FTRYERIODAID IS NULL THEN SAAT ELSE CASE WHEN SAAT < OSBASSAAT THEN SAAT-SBASSAAT ELSE SAAT - OSBASSAAT END END AS FLOAT)  * 24*60,0)AS INT)%ISNULL(FTRYERIODA.RANDEVUDAKIKA,60) = 0 ) G5 
ON G1.SAAT=G5.SAAT INNER JOIN
-------------------------GÜN  6 ------------------------------
(SELECT convert(nvarchar(5), TGUNSAATFTR.Saat ,14) as Saat, RANDEVUID, RANDDURUMID,KIMLIKID, HRAPORID,
       CASE WHEN HKIMLIKID IS NOT NULL THEN (HADI+' '+HSOYADI) 
            ELSE 
               CASE WHEN  (ACIKLAMA IS NOT NULL) THEN ACIKLAMA
		    WHEN ((TGUNSAATFTR.Saat>=SBASSAAT and TGUNSAATFTR.Saat<SBITSAAT) OR (TGUNSAATFTR.Saat>=OSBASSAAT and TGUNSAATFTR.Saat<OSBITSAAT)) AND (ACIKLAMA IS NULL) THEN NULL 
                ELSE '---' 
           END 
       END AS HASTA,TDRKIMLIK.DRADI,TSAGLIKP.SPADI,TANIM As ODA
FROM TGUNSAATFTR WITH(NOLOCK) INNER JOIN 
     FTRYERIODA WITH(NOLOCK) ON FTRYERIODA.FTRYERIODAID = @FTRYERIODAID LEFT JOIN 
     FTRYERIODACALSAAT WITH(NOLOCK) ON FTRYERIODACALSAAT.FTRYERIODAID=@FTRYERIODAID and FTRYERIODACALSAAT.BASTARIH<=DATEADD(d,5,@gun) and FTRYERIODACALSAAT.BITTARIH>=DATEADD(d,5,@gun) and HAFTAGUNU=DATEPART(dw,DATEADD(d,5,@gun)) LEFT OUTER JOIN
     HRANDEVUFTR WITH(NOLOCK) ON HRANDEVUFTR.FTRYERIODAID = @FTRYERIODAID AND RTARIH =DATEADD(d,5,@gun) AND TGUNSAATFTR.Saat>=RSAAT and TGUNSAATFTR.Saat<dateadd(n,RSURE,RSAAT) AND RANDDURUMID<7 LEFT OUTER JOIN 
     HKIMLIK WITH(NOLOCK) ON HRANDEVUFTR.KIMLIKID=HKIMLIK.HKIMLIKID LEFT OUTER JOIN 
     TDRKIMLIK WITH(NOLOCK) ON HRANDEVUFTR.DRID=TDRKIMLIK.DRID LEFT OUTER JOIN 
     TSAGLIKP WITH(NOLOCK) ON HRANDEVUFTR.TERAPISTID=TSAGLIKP.SPID
WHERE CAST(ROUND(CAST(CASE WHEN FTRYERIODACALSAAT.FTRYERIODAID IS NULL THEN SAAT ELSE CASE WHEN SAAT < OSBASSAAT THEN SAAT-SBASSAAT ELSE SAAT - OSBASSAAT END END AS FLOAT)  * 24*60,0)AS INT)%ISNULL(FTRYERIODA.RANDEVUDAKIKA,60) = 0 ) G6 
ON G1.SAAT=G6.SAAT INNER JOIN
-------------------------GÜN  7 ------------------------------
(SELECT convert(nvarchar(5), TGUNSAATFTR.Saat ,14) as Saat, RANDEVUID, RANDDURUMID,KIMLIKID, HRAPORID,
       CASE WHEN HKIMLIKID IS NOT NULL THEN (HADI+' '+HSOYADI) 
            ELSE 
               CASE WHEN  (ACIKLAMA IS NOT NULL) THEN ACIKLAMA
		    WHEN ((TGUNSAATFTR.Saat>=SBASSAAT and TGUNSAATFTR.Saat<SBITSAAT) OR (TGUNSAATFTR.Saat>=OSBASSAAT and TGUNSAATFTR.Saat<OSBITSAAT)) AND (ACIKLAMA IS NULL) THEN NULL 
                ELSE '---' 
           END 
       END AS HASTA,TDRKIMLIK.DRADI,TSAGLIKP.SPADI,TANIM As ODA
FROM TGUNSAATFTR WITH(NOLOCK) INNER JOIN 
     FTRYERIODA WITH(NOLOCK) ON FTRYERIODA.FTRYERIODAID = @FTRYERIODAID LEFT JOIN 
     FTRYERIODACALSAAT WITH(NOLOCK) ON FTRYERIODACALSAAT.FTRYERIODAID=@FTRYERIODAID and FTRYERIODACALSAAT.BASTARIH<=DATEADD(d,6,@gun) and FTRYERIODACALSAAT.BITTARIH>=DATEADD(d,6,@gun) and HAFTAGUNU=DATEPART(dw,DATEADD(d,6,@gun)) LEFT OUTER JOIN
     HRANDEVUFTR WITH(NOLOCK) ON HRANDEVUFTR.FTRYERIODAID = @FTRYERIODAID AND RTARIH =DATEADD(d,6,@gun) AND TGUNSAATFTR.Saat>=RSAAT and TGUNSAATFTR.Saat<dateadd(n,RSURE,RSAAT) AND RANDDURUMID<7 LEFT OUTER JOIN  
     HKIMLIK WITH(NOLOCK) ON HRANDEVUFTR.KIMLIKID=HKIMLIK.HKIMLIKID LEFT OUTER JOIN 
     TDRKIMLIK WITH(NOLOCK) ON HRANDEVUFTR.DRID=TDRKIMLIK.DRID LEFT OUTER JOIN 
     TSAGLIKP WITH(NOLOCK) ON HRANDEVUFTR.TERAPISTID=TSAGLIKP.SPID
WHERE CAST(ROUND(CAST(CASE WHEN FTRYERIODACALSAAT.FTRYERIODAID IS NULL THEN SAAT ELSE CASE WHEN SAAT < OSBASSAAT THEN SAAT-SBASSAAT ELSE SAAT - OSBASSAAT END END AS FLOAT)  * 24*60,0)AS INT)%ISNULL(FTRYERIODA.RANDEVUDAKIKA,60) = 0 ) G7 
ON G1.SAAT=G7.SAAT
WHERE
    (G1.SAAT >= (SELECT MIN(SBASSAAT)+2 FROM FTRYERIODACALSAAT WHERE FTRYERIODAID=@FTRYERIODAID) OR 
     G1.SAAT >= (SELECT MIN(RSAAT)+2 FROM HRANDEVUFTR WHERE FTRYERIODAID = @FTRYERIODAID AND RTARIH >= @GUN AND RTARIH <= @GUN+6)) AND 
    (G1.SAAT <  (SELECT MAX(OSBITSAAT)+2 FROM FTRYERIODACALSAAT WHERE FTRYERIODAID=@FTRYERIODAID) OR 
     G1.SAAT < (SELECT MAX(DATEADD(n,RSURE,RSAAT))+2 FROM HRANDEVUFTR WHERE FTRYERIODAID = @FTRYERIODAID 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].[RANDEVUHAFTAFTR] TO [mediwebuser]
	END TRY
	BEGIN CATCH
	END CATCH
END
GO



