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,
		CASE WHEN TDRKIMLIK.WEBRANDEVUVERILIR=1 THEN 1 ELSE 0 END AS webrandevualir,
		CASE WHEN TDRKIMLIK.ONLINEMUAYENEALIR=1 THEN 1 ELSE 0 END AS onlinemuayeneyapar,
		CASE WHEN LEN(ISNULL(TDRKIMLIK.DRTCKIMLIKNO,''))=11 THEN
			CAST(TDRKIMLIK.DRTCKIMLIKNO AS VARBINARY(MAX))			
		ELSE
			NULL
		END AS TCKimlikNo_Binary,
		'1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111' AS TCKimlikNo_Base64,		
		((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 OR TDRKIMLIK.WEBDEGORUNUR=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())
										ISNULL((SELECT TOP 1 HDTARIH FROM HDOSYA WITH(NOLOCK) WHERE DRID=TD.id AND HDTARIH<DATEADD(DAY,-1*@YENIDOKTORONDE,GETDATE())),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

	UPDATE #tumDoktorlar SET TCKimlikNo_Base64 = (CASE WHEN TCKimlikNo_Binary IS NULL THEN '' ELSE CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("TCKimlikNo_Binary")))', 'VARCHAR(MAX)') END )

	SELECT id,unvan,doktor,subeid,sube,il,bolumid,bolum,webrandevualir,onlinemuayeneyapar,webrandevuverilir,
		CASE WHEN TCKimlikNo_Base64<>'' THEN
		SUBSTRING(TCKimlikNo_Base64,1,1) +
		'S'+
		SUBSTRING(TCKimlikNo_Base64,2,2) +
		'N'+
		SUBSTRING(TCKimlikNo_Base64,4,4) +
		'A' +
		SUBSTRING(TCKimlikNo_Base64,8,LEN(TCKimlikNo_Base64)-10) +
		'M'+
		RIGHT(TCKimlikNo_Base64,3)
		ELSE
		''
		END
	AS	krmchknl
	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



