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

CREATE PROCEDURE [dbo].[SPWEB_RANDEVUSORGULA]
	@TcNo nvarchar(20),
	@PassportNo nvarchar(20),
	@BirthDate nvarchar(30),
	@Phone nvarchar(20),
	@Email nvarchar(100),
	@IpNo nvarchar(100)
AS
BEGIN
	DECLARE 
	  @BULUNANHKIMLIKID INT=0,
	  @CNTSAYI INT=0

	IF @TcNo='' AND @PassportNo=''
	BEGIN
		SELECT 0 AS patientid, '' AS code, '' AS phone, '' AS email, '' AS patientname, '' AS patientsurname,
				0 As randevuid,0 AS doktorid, '' AS doktor, 0 AS bolumid, '' AS bolum, 0 AS randevusuresi, '' AS randevutarihi, 
				 '' AS randevubitistarihi,  '' AS kayittarihi, 4 as result, 'Kimlik numarası geçerli değil' as resultdesc
		RETURN
	END

	IF (LEN(ISNULL(@TcNo,''))>0)
	BEGIN
		IF ISNULL((SELECT ISNULL(VALUESTR,'') FROM CONFIG WHERE PROGNAME='MEDISOFT' AND SECTIONSTR='Sistem' AND KEYNAME = 'Uluslar arası kullanım aktif'),'')<>'1'
		BEGIN
			IF (dbo.TcKimlikNoGecerli(@TcNo)=0)
			BEGIN
				SELECT 0 AS patientid, '' AS code, '' AS phone, '' AS email, '' AS patientname, '' AS patientsurname,
				0 As randevuid,0 AS doktorid, '' AS doktor, 0 AS bolumid, '' AS bolum, 0 AS randevusuresi, '' AS randevutarihi, 
				 '' AS randevubitistarihi,  '' AS kayittarihi, 4 as result, 'Kimlik numarası geçerli değil' as resultdesc
				RETURN
			END
		END
	END

	IF (
		SELECT 
		COUNT(*)
	FROM HRANDEVU WITH(NOLOCK) 
		LEFT JOIN HKIMLIK WITH(NOLOCK) ON HKIMLIK.HKIMLIKID=HRANDEVU.KIMLIKID
	WHERE	RTARIHSAAT>=DATEADD(HOUR,-1,GETDATE())
		AND RANDDURUMID NOT IN (5,6)
		AND (@TcNo='' OR (@TcNo<>'' AND (HRANDEVU.RTCKIMLIKNO=@TcNo OR HKIMLIK.HTCKIMLIKNO=@TcNo)))
		AND (@PassportNo='' OR (@TcNo<>'' AND (HRANDEVU.RTCKIMLIKNO=@TcNo OR HKIMLIK.HPASAPORT=@PassportNo)))
		AND (@BirthDate='' OR (@BirthDate<>'' AND (HRANDEVU.DOGUMTARIHI = CONVERT(DATETIME,@BirthDate) OR HKIMLIK.HDTARIHI = CONVERT(DATETIME,@BirthDate))))
	) = 0
	BEGIN
		SELECT 
			DBO.FN_BASIT_ID_SIFRELE(HKIMLIK.HKIMLIKID)  AS patientid, 
			'' AS code,		
			ISNULL(HKIMLIK.HCEP,'') AS phone,
			ISNULL(HKIMLIK.HEMAIL,'') AS email,
			ISNULL(HKIMLIK.HADI,'') AS patientname,
			ISNULL(HKIMLIK.HSOYADI,'') AS patientsurname,
			0 AS randevuid,
			0 AS doktorid,
			'' AS doktor,
			0 AS bolumid,
			'' AS bolum,
			0 AS randevusuresi,
			CONVERT(DATETIME,'2000-01-01 00:00') AS randevutarihi,
			CONVERT(DATETIME,'2000-01-01 00:00')  AS randevubitistarihi,
			CONVERT(DATETIME,'2000-01-01 00:00')  AS kayittarihi,
			2 AS result,
			'Aktif randevu bulunamadı' AS resultdesc
		FROM HKIMLIK WITH(NOLOCK)
		WHERE	(@TcNo='' OR (@TcNo<>'' AND (HKIMLIK.HTCKIMLIKNO=@TcNo)))
			AND (@PassportNo='' OR (@TcNo<>'' AND (HKIMLIK.HPASAPORT=@PassportNo)))
			AND (@BirthDate='' OR (@BirthDate<>'' AND (HKIMLIK.HDTARIHI = CONVERT(DATETIME,@BirthDate))))
	END
	ELSE
	BEGIN


		SELECT 
			DBO.FN_BASIT_ID_SIFRELE(HKIMLIK.HKIMLIKID)  AS patientid, 
			'' AS code,		
			ISNULL(HRANDEVU.TEL,HKIMLIK.HCEP) AS phone,
			ISNULL(HKIMLIK.HEMAIL,'') AS email,
			ISNULL(HRANDEVU.RADI,HKIMLIK.HADI) AS patientname,
			ISNULL(HRANDEVU.RSOYAD,HKIMLIK.HSOYADI) AS patientsurname,
			RANDEVUID AS randevuid,
			ISNULL(HRANDEVU.DRID,0) AS doktorid,
			ISNULL(TDRKIMLIK.DRADI,'') AS doktor,
			ISNULL(HRANDEVU.BOLUMID,0) AS bolumid,
			ISNULL(TBOLUM.BOLUMADI,'') AS bolum,
			ISNULL(HRANDEVU.RSURE,0) AS randevusuresi,
			CONVERT(VARCHAR(19), HRANDEVU.RTARIHSAAT, 120) AS randevutarihi,
			CONVERT(VARCHAR(19), HRANDEVU.RBTARIH, 120)  AS randevubitistarihi,
			CONVERT(VARCHAR(19), HRANDEVU.KYTARIH, 120)  AS kayittarihi,
			1 AS result,
			'' AS resultdesc
		FROM HRANDEVU WITH(NOLOCK) 
			LEFT JOIN HKIMLIK WITH(NOLOCK) ON HKIMLIK.HKIMLIKID=HRANDEVU.KIMLIKID
			LEFT JOIN TDRKIMLIK WITH(NOLOCK) ON TDRKIMLIK.DRID=HRANDEVU.DRID
			LEFT JOIN TBOLUM WITH(NOLOCK) ON TBOLUM.BOLUMID=HRANDEVU.BOLUMID
		WHERE	RTARIHSAAT>=DATEADD(HOUR,-1,GETDATE())
			AND RANDDURUMID NOT IN (5,6)
			AND (@TcNo='' OR (@TcNo<>'' AND (HRANDEVU.RTCKIMLIKNO=@TcNo OR HKIMLIK.HTCKIMLIKNO=@TcNo)))
			AND (@PassportNo='' OR (@TcNo<>'' AND (HRANDEVU.RTCKIMLIKNO=@TcNo OR HKIMLIK.HPASAPORT=@PassportNo)))
			AND (@BirthDate='' OR (@BirthDate<>'' AND (HRANDEVU.DOGUMTARIHI = CONVERT(DATETIME,@BirthDate) OR HKIMLIK.HDTARIHI = CONVERT(DATETIME,@BirthDate))))
		ORDER BY HRANDEVU.RANDEVUID DESC
	END


 
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_RANDEVUSORGULA] TO [mediwebuser]
	END TRY
	BEGIN CATCH
	END CATCH
END
GO
