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,
	  @RANDEVUSMSMESAJ VARCHAR(1000),
	  @TEL VARCHAR(50)

	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, '' as phone, '' as smsmessage
		RETURN
	END

	SELECT @RTARIHSAAT=RTARIHSAAT, @RANDDURUMID=RANDDURUMID, @HMUAYENEISLID=HMUAYENEISLID, @TEL=TEL, 
	@RANDEVUSMSMESAJ = CONVERT(nvarchar(20), DATEPART(DAY, RTARIHSAAT))+'.'+CONVERT(nvarchar(20), DATEPART(MONTH, RTARIHSAAT))+'.'+CONVERT(nvarchar(20), DATEPART(YEAR, RTARIHSAAT))+
	+' '+CONVERT(nvarchar(20), DATEPART(HOUR, RTARIHSAAT))+':'+CONVERT(nvarchar(20), DATEPART(MINUTE, RTARIHSAAT))
	+' '+DRUNVAN+' '+DRADI+' için aldığınız randevu iptal edildi'
	FROM HRANDEVU
	LEFT JOIN TDRKIMLIK ON TDRKIMLIK.DRID=HRANDEVU.DRID
	WHERE RANDEVUID = @RANDEVUID 
	
	SET @RANDEVUSMSMESAJ=UPPER(@RANDEVUSMSMESAJ)
		SET @RANDEVUSMSMESAJ=REPLACE(@RANDEVUSMSMESAJ,char(221),'I')
		SET @RANDEVUSMSMESAJ=REPLACE(@RANDEVUSMSMESAJ,char(208),'G')
		SET @RANDEVUSMSMESAJ=REPLACE(@RANDEVUSMSMESAJ,char(220),'U')
		SET @RANDEVUSMSMESAJ=REPLACE(@RANDEVUSMSMESAJ,char(222),'S')
		SET @RANDEVUSMSMESAJ=REPLACE(@RANDEVUSMSMESAJ,char(214),'O')
		SET @RANDEVUSMSMESAJ=REPLACE(@RANDEVUSMSMESAJ,char(199),'C')
	
	IF @RTARIHSAAT < GETDATE()
	BEGIN
		SELECT 9 AS result, 'Randevu geçmiş tarihli' AS resultdesc, '' as phone, '' as smsmessage
		RETURN
	END
	ELSE IF NOT @RANDDURUMID IN (1,7)
	BEGIN
		SELECT 10 AS result, 'Randevu tamamlanmış' AS resultdesc, '' as phone, '' as smsmessage
		RETURN
	END
	ELSE IF @HMUAYENEISLID IS NOT NULL
	BEGIN
		SELECT 11 AS result, 'Muayene kaydı oluşmuş' AS resultdesc, '' as phone, '' as smsmessage
		RETURN
	END
	ELSE
	BEGIN
	   IF (NOT (ISNULL((SELECT VALUESTR FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='MediWEB' AND KEYNAME='Online randevu iptalinde hastaya sms olarak iletilsin'),'1')='1'))
		BEGIN
			SET @RANDEVUSMSMESAJ=''		
		END

		UPDATE HRANDEVU SET RANDDURUMID = 6, SGTARIH = GETDATE()
		WHERE RANDEVUID = @RANDEVUID
		SELECT 1 AS result, 'Randevu iptal edildi' AS resultdesc, @TEL as phone, @RANDEVUSMSMESAJ as smsmessage
	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

