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


	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,0) AS doktor,
		ISNULL(HRANDEVU.BOLUMID,0) AS bolumid,
		ISNULL(TBOLUM.BOLUMADI,0) 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
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

