IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SPWEB_DOKTORLAR]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE SPWEB_DOKTORLAR
GO

CREATE PROCEDURE [dbo].[SPWEB_DOKTORLAR]
AS
BEGIN

	

	DECLARE  @SUBESIRALAMA NVARCHAR(20),@BOLUMSIRALAMA NVARCHAR(20),@DOKTORSIRALAMA NVARCHAR(20),@YENIDOKTORONDE NVARCHAR(20),@HERBOLUMDENBIRER NVARCHAR(20)
	SELECT @SUBESIRALAMA=SUBESIRALAMA,@BOLUMSIRALAMA=BOLUMSIRALAMA,@DOKTORSIRALAMA=DOKTORSIRALAMA,@YENIDOKTORONDE=YENIDOKTORONDE,@HERBOLUMDENBIRER=HERBOLUMDENBIRER FROM MEDIWEBAYARLAR
	IF ISNULL(@SUBESIRALAMA,'')='' SET @SUBESIRALAMA='siralanmaz'
	IF ISNULL(@BOLUMSIRALAMA,'')='' SET @BOLUMSIRALAMA='once_yogun'
	IF ISNULL(@DOKTORSIRALAMA,'')='' SET @DOKTORSIRALAMA='once_yogun'
	IF @YENIDOKTORONDE IS NULL SET @YENIDOKTORONDE=30
	IF @HERBOLUMDENBIRER IS NULL SET @HERBOLUMDENBIRER=2

	


	IF @YENIDOKTORONDE>0
	BEGIN
		IF(OBJECT_ID('tempdb..#tumOnde') IS NULL)
		BEGIN
			CREATE TABLE #tumOnde (id INT NULL,trh DATETIME NULL,gun INT NULL)
		END
		DELETE FROM #tumOnde
		IF(OBJECT_ID('tempdb..#tumDoktorlar') IS NOT NULL) INSERT INTO #tumOnde (id,trh,gun) SELECT id,trh,gun FROM #tumDoktorlar
	END

	IF(OBJECT_ID('tempdb..#tumDoktorlar') IS NOT NULL) DROP TABLE #tumDoktorlar

	SELECT TDRKIMLIK.DRID AS id,
		ISNULL(TDRKIMLIK.DRUNVAN,'') AS unvan,
		TDRKIMLIK.DRADI AS doktor, 
		SUBELER.SUBEID AS subeid,
		SUBELER.SUBEADI AS sube,
		ISNULL(SUBELER.IL,'') AS il,
		TBOLUM.BOLUMID AS bolumid,
		TBOLUM.BOLUMADI AS bolum,
		ISNULL(TDRKIMLIK.WEBRANDEVUVERILIR,0) AS webrandevualir,
		ISNULL(TDRKIMLIK.ONLINEMUAYENEALIR,0) AS onlinemuayeneyapar,								
		((SELECT COUNT(HDOSYAID) FROM HDOSYA WITH(NOLOCK) WHERE HDIPTAL=0 AND HDYATIYOR=0 AND HDTEKLIF=0 AND DRID=TDRKIMLIK.DRID AND HDTARIH>DATEADD(DAY,-3,GETDATE()))*10.0/
			(ISNULL((SELECT  MAX(CALISMASAATI) FROM TDRCALSAAT WHERE DRID=TDRKIMLIK.DRID),8)*3*60/(CASE WHEN ISNULL(TDRKIMLIK.RANDEVUDAKIKA,0)=0 THEN 1 ELSE TDRKIMLIK.RANDEVUDAKIKA END))
		)*(CASE TBOLUM.GSSKOD WHEN '4400' THEN 0.2 WHEN '3000' THEN 1.2 WHEN '1500' THEN 1.2 ELSE 1 END) as dosyasayi  INTO #tumDoktorlar
	FROM TDRKIMLIK WITH(NOLOCK)
	JOIN SUBELER WITH(NOLOCK) ON SUBELER.SUBEID=TDRKIMLIK.SUBEID
	JOIN TBOLUM WITH(NOLOCK) ON TBOLUM.BOLUMID=TDRKIMLIK.BOLUMID
	WHERE	STATUS = 'A' 
		AND TDRKIMLIK.WEBRANDEVUVERILIR = 1
		AND TDRKIMLIK.DRID IN (SELECT U.DRID FROM [USER] U WITH(NOLOCK) WHERE U.PASIF=0 AND U.DRID IS NOT NULL)


	ALTER TABLE #tumDoktorlar ADD puan FLOAT NULL
	ALTER TABLE #tumDoktorlar ADD trh DATETIME NULL
	ALTER TABLE #tumDoktorlar ADD gun INT NULL

    IF @SUBESIRALAMA='alfabetik' 
	BEGIN
		UPDATE TD 
		SET puan =  ISNULL(puan,0)+puanekle*10000000.0
		FROM #tumDoktorlar TD
		JOIN (SELECT SUBEID,ROW_NUMBER() OVER (ORDER BY SUBEADI DESC) AS puanekle FROM SUBELER WITH(NOLOCK)) AS TBL ON TBL.SUBEID=TD.subeid		
	END

	IF @SUBESIRALAMA='once_yuksekid' 
	BEGIN
		UPDATE TD 
		SET puan =  ISNULL(puan,0)+subeid*10000000.0
		FROM #tumDoktorlar TD		
	END

	IF @SUBESIRALAMA='once_dusukid' 
	BEGIN
		UPDATE TD 
		SET puan =  ISNULL(puan,0)+90000000.0/subeid
		FROM #tumDoktorlar TD		
	END

	IF @BOLUMSIRALAMA='alfabetik' 
	BEGIN
		UPDATE TD 
		SET puan =  ISNULL(puan,0)+puanekle*100000.0
		FROM #tumDoktorlar TD
		JOIN (SELECT BOLUMID,ROW_NUMBER() OVER (ORDER BY BOLUMADI DESC) AS puanekle FROM TBOLUM WITH(NOLOCK)) AS TBL ON TBL.BOLUMID=TD.bolumid	
	END


	IF @BOLUMSIRALAMA='once_yogun' 
	BEGIN
		UPDATE TD 
		SET puan =  ISNULL(puan,0)+sayi*300.0
		FROM #tumDoktorlar TD
		JOIN (SELECT bolumid,subeid,sum(dosyasayi) AS sayi FROM #tumDoktorlar WITH(NOLOCK) GROUP BY bolumid,subeid) AS TBL ON TBL.bolumid=TD.bolumid AND TBL.subeid=TD.subeid
	END

	IF @BOLUMSIRALAMA='once_azyogun' 
	BEGIN
		UPDATE TD 
		SET puan =  ISNULL(puan,0)+900000.0/(sayi+1)
		FROM #tumDoktorlar TD
		JOIN (SELECT bolumid,subeid,sum(dosyasayi) AS sayi FROM #tumDoktorlar WITH(NOLOCK) GROUP BY bolumid,subeid) AS TBL ON TBL.bolumid=TD.bolumid AND TBL.subeid=TD.subeid
	END

	IF @DOKTORSIRALAMA='alfabetik' 
	BEGIN
		UPDATE TD 
		SET puan =  ISNULL(puan,0)+puanekle*1.0
		FROM #tumDoktorlar TD
		JOIN (SELECT id,ROW_NUMBER() OVER (ORDER BY doktor DESC) AS puanekle FROM #tumDoktorlar WITH(NOLOCK)) AS TBL ON TBL.id=TD.id	
	END


	IF @DOKTORSIRALAMA='once_yogun' 
	BEGIN
		UPDATE TD 
		SET puan =  ISNULL(puan,0)+dosyasayi*1.0
		FROM #tumDoktorlar TD		
	END

	IF @DOKTORSIRALAMA='once_azyogun' 
	BEGIN
		UPDATE TD 
		SET puan =  ISNULL(puan,0)+900.0/(dosyasayi+1)
		FROM #tumDoktorlar TD		
	END

	IF @YENIDOKTORONDE>0
	BEGIN		
		IF ISNULL((SELECT MAX(gun) FROM #tumOnde),-50)<>@YENIDOKTORONDE DELETE FROM #tumOnde
				
		UPDATE TD SET trh=ISNULL(
										(SELECT MAX(tonde.trh) FROM #tumOnde tonde WHERE tonde.id=TD.id),
										ISNULL((SELECT MIN(HDTARIH) FROM HDOSYA WHERE DRID=TD.id),GETDATE())
								),			
		GUN=@YENIDOKTORONDE FROM #tumDoktorlar AS TD

		UPDATE TD 
		SET puan =  ISNULL(puan,0)+9999999999
		FROM #tumDoktorlar TD
		WHERE TD.trh>=DATEADD(DAY,-1*@YENIDOKTORONDE,GETDATE())
		
	END

	IF @HERBOLUMDENBIRER=1
	BEGIN
		UPDATE TD 
		SET puan =  ISNULL(puan,0)+(CASE WHEN TD.puan=(SELECT max(TD2.puan) FROM #tumDoktorlar TD2 WITH(NOLOCK) WHERE TD2.bolumid=TD.bolumid) THEN 999999999 ELSE 0 END)
		FROM #tumDoktorlar TD		
	END

	IF @HERBOLUMDENBIRER=2
	BEGIN
		UPDATE TD 
		SET puan =  ISNULL(puan,0)+(CASE WHEN TD.puan=(SELECT max(TD2.puan) FROM #tumDoktorlar TD2 WITH(NOLOCK) WHERE TD2.bolumid=TD.bolumid AND TD2.subeid=TD.subeid) THEN 999999999 ELSE 0 END)
		FROM #tumDoktorlar TD		
	END

	SELECT id,unvan,doktor,subeid,sube,il,bolumid,bolum,webrandevualir,onlinemuayeneyapar 
	FROM #tumDoktorlar ORDER BY puan DESC,id
	

END
GO

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



