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


CREATE PROCEDURE [dbo].[SPWEB_RANDEVU_KAYIT] 
@DoctorID int,
@PatientNumber int,
@PatientName nvarchar(30),
@PatientSurname nvarchar(30),
@AppointmentDate nvarchar(30),
@TcNo nvarchar(20),
@PassportNo nvarchar(20), 
@Phone nvarchar(20), 
@Email nvarchar(30),
@BirthDate nvarchar(30),
@Gender nvarchar(5),
@OnlineMuayene int
AS
BEGIN
    SET NOCOUNT ON
	DECLARE 
	  @Sonuc INT =0,
	  @Appointment int,
	  @Division_Id int,
	  @RSURE int,
	  @RANDEVUID int,
	  @AppointmentID int,
	  @PatientSourceID nvarchar(20),
	  @Hasta nvarchar(20),
	  @RandWeb bit,
	  @Izinli bit,
	  @HastaRandevusuVar integer = 0,
	  @KIMLIKID INT

	SET @Division_Id=(SELECT BOLUMID AS Division_Id  FROM TDRKIMLIK WHERE TDRKIMLIK.DRID=@DoctorID)
	 
      
    IF @Date + @Start <= GETDATE()
	BEGIN
	    SELECT 1 AS Result,0 AS AppointmentID, 'Geçmiş tarihli randevu verilemez' AS ResultMessage

	    RETURN
	END


	IF  @NIN<>''
	BEGIN
		SELECT @AppointmentID = RANDEVUID FROM HRANDEVU WHERE RTCKIMLIKNO=@NIN AND RTARIH=@Date AND
		 (BOLUMID= ISNULL(CONVERT(INT,@Division_Id),0)) AND RANDDURUMID NOT IN (5,6)
		IF @@ROWCOUNT > 0 
			-- Hastanın aynı bölüme randevusu var	    
			SET @HastaRandevusuVar = 1
		ELSE
		BEGIN		
			SELECT @RANDEVUID = RANDEVUID FROM HRANDEVU WHERE RTCKIMLIKNO=@NIN AND RTARIH=@Date AND RSAAT = @Start - 2 AND RANDDURUMID NOT IN (5,6)
			IF @@ROWCOUNT > 0
				-- -- Hastanın aynı saate randevusu var	
				SET @HastaRandevusuVar = 2
	
		END
	END
	
	IF @HastaRandevusuVar=0
	BEGIN
		SELECT @AppointmentID = RANDEVUID FROM HRANDEVU WHERE ADISOYADI=@PatientName AND RTARIH=@Date AND
		 (BOLUMID= ISNULL(CONVERT(INT,@Division_Id),0)) AND RANDDURUMID NOT IN (5,6)
		IF @@ROWCOUNT > 0 -- Hastanın aynı bölüme randevusu var	    
			SET @HastaRandevusuVar = 1
		ELSE
		BEGIN		
			SELECT @RANDEVUID = RANDEVUID FROM HRANDEVU WHERE ADISOYADI=@PatientName AND RTARIH=@Date AND 
			RSAAT = @Start - 2 AND RANDDURUMID NOT IN (5,6)
			IF @@ROWCOUNT > 0 -- -- Hastanın aynı saate randevusu var	
				SET @HastaRandevusuVar = 2
		END
	END

	
		
	
	IF @HastaRandevusuVar > 0
	BEGIN
	    IF @HastaRandevusuVar = 1
		BEGIN
		    SELECT 5 AS Result,0 AS AppointmentID, 'Hastanın aynı bölüme randevusu var' AS ResultMessage
		END
		ELSE
		BEGIN
		    SELECT 6 AS Result,0 AS AppointmentID, 'Hastanın aynı saate randevusu var' AS ResultMessage
		END		
		  
	    RETURN
	END
	
	SELECT @RandevuID = RANDEVUID, @Hasta = HASTA, @RandWeb = WEB, @Izinli = IZINLI
	FROM dbo.RANDEVUGUN (ISNULL(CONVERT(INT,@DoctorID),0), @Date, default)
	WHERE CAST(SAAT AS datetime) = convert(nvarchar(5), @Start ,14)
	
	if @@ROWCOUNT > 0 
	BEGIN      
	    if @RandevuID IS NULL AND @Hasta IS NULL AND @Izinli = 0
	    BEGIN  




	        SELECT @RSURE = RANDEVUDAKIKA FROM TDRKIMLIK WHERE DRID = @DoctorID
    		
     
    	     
			EXEC SPREADID2 N'HRANDEVU','', @AppointmentID OUTPUT
		    INSERT INTO HRANDEVU (RANDEVUID,KIMLIKID,DRID, BOLUMID, RNEDENID, RTARIH, RSAAT, RSURE, RBTARIH, RANDDURUMID, ADISOYADI, TEL, 
							      RTARIHSAAT, KYTARIH, SGTARIH,KYKULLANICI, SGKULLANICI, SUBEID, WEB, RTCKIMLIKNO, DOGUMTARIHI, RADI, RSOYAD, ONLINEMUAYENE) 
								  VALUES (@AppointmentID,@PatientSourceID ,(ISNULL(CONVERT(INT,@DoctorID),0)),(ISNULL(CONVERT(INT,@Division_Id),0)),
								  1, convert(date,@Date,104),
								  dateadd(day,-2,convert(nvarchar(5), @Start ,14)) , DATEDIFF(MINUTE,@Start,@End), 
								  @End ,1,@PatientName,@Phone,@Start, GETDATE(), GETDATE(), 'ONLINEMUAYENE', 'ONLINEMUAYENE', 1, 1, @NIN, @BirthDate, LTRIM(@PatientName), RTRIM(@PatientName), @OnlineMuayene )
          
		    IF @@ERROR = 0
		      SELECT 0 AS Result,@AppointmentID AS AppointmentID, 'Randevu kaydı başarıyla oluşturuldu' AS ResultMessage			  	
		    ELSE
		      SELECT 4 AS Result,0 AS AppointmentID, 'Randevu kayıt aşamasında bir hata oluştu' AS ResultMessage
	    END
	    ELSE IF  @Izinli = 1
	        SELECT 0 AS Result,0 AS AppointmentID, 'İlgili tarihte doktor izinli' AS ResultMessage
	    ELSE IF @RandevuID IS NOT NULL AND @Hasta <> '----'  
		    SELECT 2 AS Result,0 AS AppointmentID, 'Randevu kayıt aşamasında bir hata oluştu' AS ResultMessage
	    ELSE
		    SELECT 3 AS Result,0 AS AppointmentID, 'Randevu kayıt aşamasında bir hata oluştu' AS ResultMessage
	END 
	ELSE	
	    -- randevu saati çalışma saati dışında
		SELECT 7 AS Result,0 AS AppointmentID, 'Randevu saati çalışma saatlerinin dışında' AS ResultMessage
	    
	RETURN
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_RANDEVU_KAYIT] TO [mediwebuser]
	END TRY
	BEGIN CATCH
	END CATCH
END
GO
