IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SPWEB_RANDEVUIPTAL]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE SPWEB_RANDEVUIPTAL
GO
-- SPWEB_RANDEVUIPTAL 726474,'sp45iffllk2l',81,'','','','',''
CREATE PROCEDURE [dbo].[SPWEB_RANDEVUIPTAL]
	@AppointmentId int,
	@PatientId nvarchar(30),
	@DoctorId int,
	@AppointmentDate nvarchar(30),	
	@TcNo nvarchar(20),
	@PassportNo nvarchar(20),
	@BirthDate nvarchar(30),
	@Phone nvarchar(20),
	@IpNo nvarchar(40)
AS
BEGIN
	DECLARE 
	  @RTARIHSAAT DATETIME,
	  @RANDDURUMID INT,
	  @HMUAYENEISLID INT,
	  @RANDEVUID INT,
	  @HKIMLIKID INT=0

	IF @PatientId<>'' AND @PatientId<>'0'
	BEGIN
		SET @HKIMLIKID = DBO.FN_BASIT_ID_SIFRE_AC(@PatientId)
	END
	
	DECLARE @LOG NVARCHAR(500) = 'AppointmentId:'+convert(varchar,@AppointmentId)+', PatientId:'+@PatientId+', DoctorId:'+convert(varchar,@DoctorId)+', AppointmentDate:'+@AppointmentDate+', TcNo:'+@TcNo+', PassportNo:'+@PassportNo+', BirthDate:'+@BirthDate+', Phone:'+@Phone
	EXEC SPWEB_LOGLA 'randevuiptal',@HKIMLIKID,@LOG,@IpNo

	--AppointmentId,PatientNumber,DoctorId
	SET @RANDEVUID = ISNULL(	(	SELECT TOP 1 RANDEVUID
									FROM HRANDEVU 
									WHERE (@AppointmentId>0 AND @HKIMLIKID>0 AND @DoctorId>0 AND KIMLIKID = @HKIMLIKID AND RANDEVUID = @AppointmentId AND DRID=@DoctorId)
									ORDER BY RANDEVUID DESC
								),0
							)

	--AppointmentId,DoctorId,TcNo
	IF @RANDEVUID = 0
	SET @RANDEVUID = ISNULL(	(	SELECT TOP 1 RANDEVUID
									FROM HRANDEVU 
									WHERE (@AppointmentId>0 AND @DoctorId>0 AND @TcNo<>'' AND RTCKIMLIKNO = @TcNo AND RANDEVUID = @AppointmentId AND DRID=@DoctorId)
									ORDER BY RANDEVUID DESC
								),0
							)

	--AppointmentId,DoctorId,PassportNo
	IF @RANDEVUID = 0
	SET @RANDEVUID = ISNULL(	(	SELECT TOP 1 RANDEVUID
									FROM HRANDEVU 
									WHERE (@AppointmentId>0 AND @DoctorId>0 AND @PassportNo<>'' AND PASAPORTNO = @PassportNo AND RANDEVUID = @AppointmentId AND DRID=@DoctorId)
									ORDER BY RANDEVUID DESC
								),0
							)

	--AppointmentDate,DoctorId,TcNo
	IF @RANDEVUID = 0
	SET @RANDEVUID = ISNULL(	(	SELECT TOP 1 RANDEVUID
									FROM HRANDEVU 
									WHERE (@DoctorId>0 AND @AppointmentDate<>'' AND @TcNo<>'' AND RTCKIMLIKNO = @TcNo AND RTARIHSAAT = CONVERT(DATETIME,@AppointmentDate) AND DRID=@DoctorId)
									ORDER BY RANDEVUID DESC
								),0
							)

	--AppointmentDate,DoctorId,PassportNo
	IF @RANDEVUID = 0
	SET @RANDEVUID = ISNULL(	(	SELECT TOP 1 RANDEVUID
									FROM HRANDEVU 
									WHERE (@DoctorId>0 AND @AppointmentDate<>'' AND @PassportNo<>'' AND PASAPORTNO = @PassportNo AND RTARIHSAAT = CONVERT(DATETIME,@AppointmentDate) AND DRID=@DoctorId)
									ORDER BY RANDEVUID DESC
								),0
							)


	IF ISNULL(@RANDEVUID,0) =0 
	BEGIN
		SELECT 6 AS result, 'Randevu bulunamadı' AS resultdesc
		RETURN
	END

	SELECT @RTARIHSAAT=RTARIHSAAT, @RANDDURUMID=RANDDURUMID, @HMUAYENEISLID=HMUAYENEISLID 
	FROM HRANDEVU 
	WHERE RANDEVUID = @RANDEVUID 

	
	IF @RTARIHSAAT < GETDATE()
	BEGIN
		SELECT 9 AS result, 'Randevu geçmiş tarihli' AS resultdesc
		RETURN
	END
	ELSE IF NOT @RANDDURUMID IN (1,7)
	BEGIN
		SELECT 10 AS result, 'Randevu tamamlanmış' AS resultdesc
		RETURN
	END
	ELSE IF @HMUAYENEISLID IS NOT NULL
	BEGIN
		SELECT 11 AS result, 'Muayene kaydı oluşmuş' AS resultdesc
		RETURN
	END
	ELSE
	BEGIN
		UPDATE HRANDEVU SET RANDDURUMID = 6, SGTARIH = GETDATE()
		WHERE RANDEVUID = @RANDEVUID
		SELECT 1 AS result, 'Randevu iptal edildi' AS resultdesc
	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_RANDEVUIPTAL] TO [mediwebuser]
	END TRY
	BEGIN CATCH
	END CATCH
END
GO

