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

-- SPWEB_RANDEVU_KAYIT 412, '2022-01-23 15:15:00',0,'ERHAN','NACIR','','A97GFTGB','5075011488','','1981-08-11','E',3
CREATE PROCEDURE [dbo].[SPWEB_RANDEVUKAYIT] 
@DoctorId int,
@AppointmentDate nvarchar(30),
@PatientId nvarchar(30),
@PatientName nvarchar(30),
@PatientSurname nvarchar(30),
@TcNo nvarchar(20),
@PassportNo nvarchar(20), 
@Phone nvarchar(20), 
@Email nvarchar(30),
@BirthDate nvarchar(30),
@Gender nvarchar(5),
@AppointmentType int,
@ConfirmCode nvarchar(20),
@IpNo nvarchar(40)
AS
BEGIN
	DECLARE @RANDEVUKONTROL TABLE (	slotdate NVARCHAR(100),
									slotenddate NVARCHAR(100),
									result INT,
									resultdesc NVARCHAR(200))

	DECLARE
		@RANDEVUTARIHI DATETIME,
		@RANDEVUTURU NVARCHAR(20),
		@GONDERIMZAMANI DATETIME,
		@BOLUMID INT,
		@SUBEID INT,
		@RANDEVUDAKIKA INT,
		@RANDTARIH NVARCHAR(20),
		@BULUNANHKIMLIKID INT,
		@YENIHKIMLIKID INT,
		@PATIENTNO NVARCHAR(30)='',
		@RANDEVUSURESI INT,
		@RANDEVUID INT,
		@HWEBSIFRE NVARCHAR(20),
		@CRMSMSBLID INT,
		@BOLUM NVARCHAR(100),
		@DOKTOR NVARCHAR(100),
		@HKIMLIKID INT=0,
		@RANDEVUYUSMSAT INT=0,
		@WEBSIFRE NVARCHAR(30)='',
		@RANDEVUSMSMESAJ NVARCHAR(1000),
		@ALTYASSINIRI INT,
		@USTYASSINIRI INT,
		@KONTROLLLERIRANDEVUSUZALIR INT


	SET @Phone = REPLACE(@Phone,' ','')
	SET @TcNo = REPLACE(@TcNo,' ','')
	SET @PassportNo = REPLACE(@PassportNo,' ','')
	SET @Email = REPLACE(@Email,' ','')
	SET @PatientName = UPPER(@PatientName)
	SET @PatientSurname = UPPER(@PatientSurname)

	IF @PatientId<>'' AND @PatientId<>'0'
	BEGIN
		SET @HKIMLIKID = DBO.FN_BASIT_ID_SIFRE_AC(@PatientId)
	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 appointmentid, '' AS patientid, '' AS phone, '' AS smsmessage, '' AS passwrd, 4 AS result, 'Kimlik numaras'+CHAR(253)+' ge'+CHAR(231)+'erli de'+CHAR(240)+'il' AS resultdesc			
				RETURN
			END
		END
	END
	
	IF (@ConfirmCode<>'854091')
	BEGIN
		IF @HKIMLIKID>0
		BEGIN
			IF (SELECT COUNT(*) FROM HWEBONAYKOD WITH(NOLOCK) WHERE (	(RIGHT(HWEBONAYKOD.HCEP,9) = RIGHT(REPLACE(ISNULL((SELECT HKIMLIK.HCEP FROM HKIMLIK WITH(NOLOCK) WHERE HKIMLIK.HKIMLIKID=@HKIMLIKID),'999'),' ',''),9))
																		OR 
																			(HWEBONAYKOD.HEMAIL = ISNULL((SELECT HKIMLIK.HEMAIL FROM HKIMLIK WITH(NOLOCK) WHERE HKIMLIK.HKIMLIKID=@HKIMLIKID),'999'))
																	)
																	AND ONAYKODU=@ConfirmCode AND KAYITTARIHI>DATEADD(MINUTE,-30,GETDATE()))=0
			BEGIN
				SELECT 0 AS appointmentid, '' AS patientid, '' AS phone, '' AS smsmessage, '' AS passwrd, 7 AS result, 'Sms do'+CHAR(240)+'rulama kodunuz yanl'+CHAR(253)+''+CHAR(254)+' veya s'+CHAR(252)+'resi dolmu'+CHAR(254)+'' AS resultdesc
				RETURN
			END
		END
		ELSE
		BEGIN
			IF (SELECT COUNT(*) FROM HWEBONAYKOD WITH(NOLOCK) WHERE ((@Phone<>'' AND RIGHT(HCEP,9)=RIGHT(@Phone,9)) OR (@Email<>'' AND HEMAIL=@Email)) AND ONAYKODU=@ConfirmCode AND KAYITTARIHI>DATEADD(MINUTE,-30,GETDATE()))=0
			BEGIN
				SELECT 0 AS appointmentid, '' AS patientid, '' AS phone, '' AS smsmessage, '' AS passwrd, 7 AS result, 'Sms do'+CHAR(240)+'rulama kodunuz yanl'+CHAR(253)+''+CHAR(254)+' veya s'+CHAR(252)+'resi dolmu'+CHAR(254)+'' AS resultdesc
				RETURN
			END
		END
	END

	IF @AppointmentType = 3 
		SET @RANDEVUTURU = 'ONLINEMUAYENE'
	ELSE
		SET @RANDEVUTURU = 'MEDWEB'


	IF ISDATE(@AppointmentDate)=1
	BEGIN
		SET @RANDEVUTARIHI = CAST(CAST(@AppointmentDate AS SMALLDATETIME) AS DATETIME)
	END
	ELSE
	BEGIN
		SELECT 0 AS appointmentid, '' AS patientid, '' AS phone, '' AS smsmessage, '' AS passwrd, 8 AS result, 'Randevu tarihi ge'+CHAR(231)+'ersiz' AS resultdesc
		RETURN
	END

	IF @RANDEVUTARIHI <= GETDATE()
	BEGIN
		SELECT 0 AS appointmentid, '' AS patientid, '' AS phone, '' AS smsmessage, '' AS passwrd, 9 AS result, 'Ge'+CHAR(231)+'mi'+CHAR(254)+' tarihli randevu verilemez' AS resultdesc
	    RETURN
	END


	SELECT TOP 1 @BOLUMID=ISNULL(BOLUMID,0),
			@SUBEID = ISNULL(SUBEID,1), 
			@RANDEVUSURESI=ISNULL(RANDEVUDAKIKA,5),
			@ALTYASSINIRI=ISNULL(ALTYASSINIRI,-1),
			@USTYASSINIRI=ISNULL(USTYASSINIRI,200),
			@KONTROLLLERIRANDEVUSUZALIR=ISNULL(KONTROLLLERIRANDEVUSUZALIR,0),
			@BOLUM = ISNULL((SELECT BOLUMADI FROM TBOLUM WITH(NOLOCK) WHERE TBOLUM.BOLUMID=TDRKIMLIK.BOLUMID),''),
			@DOKTOR = ISNULL(DRADI,'')
	FROM TDRKIMLIK WITH(NOLOCK)
	WHERE TDRKIMLIK.DRID=@DoctorId

	IF @SUBEID IS NULL
	BEGIN
		SELECT 0 AS appointmentid, '' AS patientid, '' AS phone, '' AS smsmessage, '' AS passwrd, 10 AS result, 'Doktor bulunamad'+CHAR(253)+'' AS resultdesc
		RETURN
	END

	IF (NOT (@HKIMLIKID>0 OR LEN(@TcNo)>5 OR LEN(@PassportNo)>5))
	BEGIN
		SELECT 0 AS appointmentid, '' AS patientid, '' AS phone, '' AS smsmessage, '' AS passwrd, 11 AS result, 'Hasta numaras'+CHAR(253)+', kimlik numaras'+CHAR(253)+' veya pasaport numaras'+CHAR(253)+' zorunludur' AS resultdesc
		RETURN
	END

	IF @HKIMLIKID=0 AND LEN(@Phone)<9
	BEGIN
		SELECT 0 AS appointmentid, '' AS patientid, '' AS phone, '' AS smsmessage, '' AS passwrd, 12 AS result, 'En az 9 haneli telefon numaras'+CHAR(253)+' zorunludur' AS resultdesc
		RETURN
	END

	IF @HKIMLIKID=0 AND (@BirthDate='' OR (NOT (ISDATE(@BirthDate)=1)))
	BEGIN
		SELECT 0 AS appointmentid, '' AS patientid, '' AS phone, '' AS smsmessage, '' AS passwrd, 13 AS result, 'Do'+CHAR(240)+'um tarihi zorunludur' AS resultdesc
		RETURN
	END

	SET @RANDTARIH = (CONVERT(VARCHAR,DATEPART(YEAR,@RANDEVUTARIHI))+'-'+RIGHT('0'+CONVERT(VARCHAR,DATEPART(MONTH,@RANDEVUTARIHI)),2)+'-'+RIGHT('0'+CONVERT(VARCHAR,DATEPART(DAY,@RANDEVUTARIHI)),2))
	INSERT INTO @RANDEVUKONTROL (slotdate,slotenddate,result,resultdesc)
	EXEC SPWEB_DOKTORSLOT @DoctorId,@RANDTARIH,@RANDTARIH

	IF ((@RANDEVUTURU = 'ONLINEMUAYENE') AND @RANDEVUTARIHI NOT IN (SELECT slotdate FROM @RANDEVUKONTROL WHERE result IN (1,3)))
	BEGIN
		SELECT 0 AS appointmentid, '' AS patientid, '' AS phone, '' AS smsmessage, '' AS passwrd, 14 AS result, 'Randevu tarihi dolu veya doktor randevu s'+CHAR(252)+'releri ile uyu'+CHAR(254)+'muyor ' AS resultdesc
		RETURN
	END
	
	IF ((@RANDEVUTURU = 'MEDWEB') AND @RANDEVUTARIHI NOT IN (SELECT slotdate FROM @RANDEVUKONTROL WHERE result IN (1,2)))
	BEGIN
		SELECT 0 AS appointmentid, '' AS patientid, '' AS phone, '' AS smsmessage, '' AS passwrd, 14 AS result, 'Randevu tarihi dolu veya doktor randevu s'+CHAR(252)+'releri ile uyu'+CHAR(254)+'muyor ' AS resultdesc
		RETURN
	END

	IF @HKIMLIKID>0 AND (SELECT COUNT(HKIMLIKID) FROM HKIMLIK WITH(NOLOCK) WHERE HKIMLIKID=@HKIMLIKID)=0
	BEGIN
		SELECT 0 AS appointmentid, '' AS patientid, '' AS phone, '' AS smsmessage, '' AS passwrd, 6 AS result, 'Hasta bulunamad'+CHAR(253)+'' AS resultdesc
		RETURN
	END


	SET @GONDERIMZAMANI= @RANDEVUTARIHI - CONVERT(DATETIME,ISNULL((SELECT TOP 1 VALUESTR FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='Randevu' AND KEYNAME='Randevu SMS Kaydi Saat Farki'),'02:00:00'))
	


	IF @HKIMLIKID=0 AND (@TcNo<>'' OR @PassportNo<>'')
	BEGIN
		SELECT TOP 1 @BULUNANHKIMLIKID=HKIMLIKID 
		FROM HKIMLIK WITH(NOLOCK) 
		WHERE	(		(@TcNo<>'' AND HTCKIMLIKNO=@TcNo) 
					OR	(@PassportNo<>'' AND @BirthDate<>'' AND HPASAPORT=@PassportNo AND HDTARIHI = CONVERT(DATETIME,@BirthDate) )
				)
		ORDER BY (CASE WHEN @BirthDate<>'' AND HDTARIHI = CONVERT(DATETIME,@BirthDate) THEN 0 ELSE 1 END) ASC,
				 (CASE WHEN @Phone<>'' AND RIGHT(@Phone,9)=RIGHT(HCEP,9) THEN 0 ELSE 1 END) ASC,
				 (CASE WHEN @PatientName<>'' AND @PatientName=HADI THEN 0 ELSE 1 END) ASC,
				 (CASE WHEN @PatientSurname<>'' AND @PatientSurname=HSOYADI THEN 0 ELSE 1 END) ASC		
	END

	IF ISNULL(@BULUNANHKIMLIKID,0)=0 SET @BULUNANHKIMLIKID=0
	IF @HKIMLIKID>0 SET @BULUNANHKIMLIKID=@HKIMLIKID

	
	
	IF @BULUNANHKIMLIKID>0
	BEGIN
		IF (	SELECT COUNT(RANDEVUID) 
				FROM HRANDEVU WITH(NOLOCK) 
				WHERE	KIMLIKID = @BULUNANHKIMLIKID 
					AND RTARIHSAAT>=GETDATE() 
					AND BOLUMID=@BOLUMID 
					AND SUBEID=@SUBEID 
					AND RANDDURUMID NOT IN (5,6)
			)>0
		BEGIN
			SELECT 0 AS appointmentid, '' AS patientid, '' AS phone, '' AS smsmessage, '' AS passwrd, 15 AS result, 'Hastan'+CHAR(253)+'n ayn'+CHAR(253)+' b'+CHAR(246)+'l'+CHAR(252)+'me randevusu var' AS resultdesc
			RETURN
		END
		
		IF (	SELECT COUNT(RANDEVUID) 
				FROM HRANDEVU WITH(NOLOCK) 
				WHERE	KIMLIKID = @BULUNANHKIMLIKID 
					AND RTARIHSAAT=@RANDEVUTARIHI 
					AND RANDDURUMID NOT IN (5,6)
			)>0
		BEGIN		
			SELECT 0 AS appointmentid, '' AS patientid, '' AS phone, '' AS smsmessage, '' AS passwrd, 16 AS result, 'Hastan'+CHAR(253)+'n ayn'+CHAR(253)+' saate randevusu var' AS resultdesc
			RETURN
		END
		
		IF (	SELECT COUNT(RANDEVUID) 
				FROM HRANDEVU WITH(NOLOCK) 
				WHERE	KIMLIKID = @BULUNANHKIMLIKID 
					AND RTARIHSAAT>GETDATE()
					AND RANDDURUMID NOT IN (5,6)
			)>2
		BEGIN		
			SELECT 0 AS appointmentid, '' AS patientid, '' AS phone, '' AS smsmessage, '' AS passwrd, 17 AS result, ''+CHAR(252)+''+CHAR(231)+'den fazla aktif randevu olu'+CHAR(254)+'turamazs'+CHAR(253)+'n'+CHAR(253)+'z' AS resultdesc
			RETURN
		END	
	END
	ELSE
	BEGIN
		IF @TcNo<>'' OR @PassportNo<>''
		IF (	SELECT COUNT(RANDEVUID) 
				FROM HRANDEVU WITH(NOLOCK) 
				WHERE	(
								(@TcNo<>'' AND RTCKIMLIKNO = @TcNo) 
							OR (@PassportNo<>'' AND PASAPORTNO=@PassportNo) 
							OR (ADISOYADI=UPPER(@PatientName+' '+@PatientSurname) )
						) 
					AND RTARIHSAAT>=GETDATE() 
					AND BOLUMID=@BOLUMID 
					AND SUBEID=@SUBEID AND RANDDURUMID NOT IN (5,6)
			)>0
		BEGIN
			SELECT 0 AS appointmentid, '' AS patientid, '' AS phone, '' AS smsmessage, '' AS passwrd, 15 AS result, 'Hastan'+CHAR(253)+'n ayn'+CHAR(253)+' b'+CHAR(246)+'l'+CHAR(252)+'me randevusu var' AS resultdesc
			RETURN
		END

		IF @TcNo<>'' OR @PassportNo<>''
		IF (	SELECT COUNT(RANDEVUID) 
				FROM HRANDEVU WITH(NOLOCK) 
				WHERE	(
								(@TcNo<>'' AND RTCKIMLIKNO = @TcNo) 
							OR	(@PassportNo<>'' AND PASAPORTNO=@PassportNo)  
							OR	(ADISOYADI=UPPER(@PatientName+' '+@PatientSurname))
						) 
					AND RTARIHSAAT=@RANDEVUTARIHI 
					AND RANDDURUMID NOT IN (5,6)
			)>0
		BEGIN		
			SELECT 0 AS appointmentid, '' AS patientid, '' AS phone, '' AS smsmessage, '' AS passwrd, 16 AS result, 'Hastan'+CHAR(253)+'n ayn'+CHAR(253)+' saate randevusu var' AS resultdesc
			RETURN
		END


		IF @TcNo<>'' OR @PassportNo<>''
		IF (	SELECT COUNT(RANDEVUID) 
				FROM HRANDEVU WITH(NOLOCK) 
				WHERE	(
								(@TcNo<>'' AND RTCKIMLIKNO = @TcNo) 
							OR	(@PassportNo<>'' AND PASAPORTNO=@PassportNo)  
							OR	(ADISOYADI=UPPER(@PatientName+' '+@PatientSurname))
						) 
					AND RTARIHSAAT>GETDATE()
					AND RANDDURUMID NOT IN (5,6)
			)>2
		BEGIN		
			SELECT 0 AS appointmentid, '' AS patientid, '' AS phone, '' AS smsmessage, '' AS passwrd, 17 AS result, ''+CHAR(252)+''+CHAR(231)+'den fazla aktif randevu olu'+CHAR(254)+'turamazs'+CHAR(253)+'n'+CHAR(253)+'z' AS resultdesc
			RETURN
		END
	END

	IF @HKIMLIKID>0 
	BEGIN
		SELECT	@PatientName= (CASE WHEN @PatientName='' THEN ISNULL(HADI,'') ELSE @PatientName END),
				@PatientSurname= (CASE WHEN @PatientSurname='' THEN ISNULL(HSOYADI,'') ELSE @PatientSurname END),
				@TcNo= (CASE WHEN @TcNo='' THEN ISNULL(HTCKIMLIKNO,'') ELSE @TcNo END),
				@PassportNo= (CASE WHEN @PassportNo='' THEN ISNULL(HPASAPORT,'') ELSE @TcNo END),
				@Phone= (CASE WHEN @Phone='' THEN ISNULL(HCEP,'') ELSE @Phone END),
				@Email= (CASE WHEN @Email='' THEN ISNULL(HEMAIL,'') ELSE @Email END),
				@BirthDate= (CASE WHEN @BirthDate='' THEN (CASE WHEN HDTARIHI IS NULL THEN '' ELSE (CONVERT(VARCHAR,DATEPART(YEAR,HDTARIHI))+'-'+RIGHT('0'+CONVERT(VARCHAR,DATEPART(MONTH,HDTARIHI)),2)+'-'+RIGHT('0'+CONVERT(VARCHAR,DATEPART(DAY,HDTARIHI)),2)) END) ELSE @BirthDate END),
				@Gender= (CASE WHEN @Gender='' THEN ISNULL(HCINSIYETI,'') ELSE @Gender END)		
		FROM	HKIMLIK WITH(NOLOCK) 
		WHERE	HKIMLIKID=@HKIMLIKID
	END

	IF @USTYASSINIRI=0 SET @USTYASSINIRI=200
	IF (@ALTYASSINIRI<>-1 AND DATEDIFF(DAY,CONVERT(DATETIME,@BirthDate),GETDATE())< @ALTYASSINIRI*365)
	BEGIN
		SELECT 0 AS appointmentid, '' AS patientid, '' AS phone, '' AS smsmessage, '' AS passwrd, 18 AS result, 'Doktor '+CONVERT(VARCHAR,@ALTYASSINIRI)+' ya'+CHAR(254)+''+CHAR(253)+'ndan b'+CHAR(252)+'y'+CHAR(252)+'k hastalar'+CHAR(253)+' muayene ediyor' AS resultdesc
			RETURN
	END

	IF (@USTYASSINIRI<>200 AND DATEDIFF(DAY,CONVERT(DATETIME,@BirthDate),GETDATE())> @USTYASSINIRI*365)
	BEGIN
		SELECT 0 AS appointmentid, '' AS patientid, '' AS phone, '' AS smsmessage, '' AS passwrd, 18 AS result, 'Doktor '+CONVERT(VARCHAR,@USTYASSINIRI)+' ya'+CHAR(254)+''+CHAR(253)+'ndan k'+CHAR(252)+''+CHAR(231)+''+CHAR(252)+'k hastalar'+CHAR(253)+' muayene ediyor' AS resultdesc
			RETURN
	END

	IF (@KONTROLLLERIRANDEVUSUZALIR=1 AND @BULUNANHKIMLIKID>0)
	BEGIN
		IF (SELECT COUNT(HMUAYENEID) FROM VWMUAYENE WITH(NOLOCK) WHERE HKIMLIKID=@BULUNANHKIMLIKID  AND HMDRID=@DoctorId AND HMTARIH>=DATEADD(DAY,-10,CONVERT(DATETIME,@RANDEVUTARIHI))) > 0 
		BEGIN
			SELECT 0 AS appointmentid, '' AS patientid, '' AS phone, '' AS smsmessage, '' AS passwrd, 19 AS result, 'Doktor kontrol muayenelerini randevusuz almaktad'+CHAR(253)+'r.' AS resultdesc
			RETURN
		END
	END

	IF @PatientName='' AND @RANDEVUTURU = 'ONLINEMUAYENE'
	BEGIN
		SET @PatientName = 'ONLINE'
		SET @PatientSurname = 'MUAYENE'
	END

	IF @PatientName='' AND @RANDEVUTURU = 'MEDWEB'
	BEGIN
		SET @PatientName = 'WEB'
		SET @PatientSurname = 'RANDEVU'
	END

	IF @BULUNANHKIMLIKID=0 AND ISNULL((SELECT TOP 1 VALUESTR FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='MediWEB' AND KEYNAME='Online randevu olusturulurken hasta kimlik yoksa olusturulsun'),'1')='1'
	BEGIN
		EXEC SPREADID2 N'HKIMLIK','', @YENIHKIMLIKID OUTPUT
		SET @HWEBSIFRE =  CHAR(65+CONVERT(INT,RAND()*26)) + CHAR(97+CONVERT(INT,RAND()*26)) + CHAR(48+CONVERT(INT,RAND()*10)) + CHAR(48+CONVERT(INT,RAND()*10)) + CHAR(48+CONVERT(INT,RAND()*10)) + 
		CHAR(48+CONVERT(INT,RAND()*10)) + CHAR(48+CONVERT(INT,RAND()*10)) + CHAR(48+CONVERT(INT,RAND()*10))
	       
		INSERT INTO HKIMLIK (HKIMLIKID,HKARTNO,HADI,HSOYADI,HTCKIMLIKNO,HPASAPORT,HDTARIHI,HCINSIYETI,HKCARIID,HCEP,HEMAIL,HWEBSIFRE,HWEBKULLAN,HWEBAKTIF,HWEBKAYIT,UYRUKKODU)
		VALUES (@YENIHKIMLIKID,@YENIHKIMLIKID,@PatientName,@PatientSurname,@TcNo,@PassportNo,@BirthDate,@Gender,-1,@Phone,@Email,@HWEBSIFRE,1,1,1,(CASE WHEN @PassportNo='' THEN 'TC' ELSE '' END))
	END
	SET @YENIHKIMLIKID = ISNULL(@YENIHKIMLIKID,0)
	
	
	--E'+CHAR(254)+'le'+CHAR(254)+'me sonucu randevu ile kimli'+CHAR(240)+'i ba'+CHAR(240)+'lamak istemiyorsak bu sat'+CHAR(253)+'r a'+CHAR(231)+''+CHAR(253)+'l'+CHAR(253)+'p i'+CHAR(254)+'ler @HKIMLIKID  ile ilerlemeli ?
	--IF @YENIHKIMLIKID>0 SET @HKIMLIKID=@YENIHKIMLIKID
	IF @YENIHKIMLIKID>0 SET @BULUNANHKIMLIKID=@YENIHKIMLIKID


    	     
	EXEC SPREADID2 N'HRANDEVU','', @RANDEVUID 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, IPNO) 
	VALUES (				@RANDEVUID, @BULUNANHKIMLIKID ,@DoctorId, @BOLUMID, 1, convert(date,@RANDEVUTARIHI,104), dateadd(day,-2,convert(nvarchar(5), @RANDEVUTARIHI ,14)) , @RANDEVUSURESI, 
							DATEADD(MINUTE,@RANDEVUSURESI,@RANDEVUTARIHI), 1, @PatientName+' '+@PatientSurname, @Phone, @RANDEVUTARIHI, GETDATE(), GETDATE(), 
							@RANDEVUTURU, @RANDEVUTURU, @SUBEID, 1, @TcNo, @BirthDate, @PatientName, @PatientSurname,@IpNo)
          
	IF @@ERROR = 0 and @RANDEVUID>0
	BEGIN
		SET @RANDEVUSMSMESAJ=(CASE WHEN @PatientName='ONLINE' OR @PatientName='WEB' THEN '' ELSE 'SAYIN '+@PatientName+' '+@PatientSurname+' ' END)+@BOLUM+' BOLUMUNDE '+@DOKTOR+' ILE RANDEVU SAATINIZ '+LEFT(CONVERT(VARCHAR, @RANDEVUTARIHI, 120),10) + ' ' +CONVERT(VARCHAR(5),@RANDEVUTARIHI,108)

		IF (SELECT COUNT(*) FROM SUBELER WITH(NOLOCK) WHERE	MEDWEBKULLAN = 1 AND EXISTS(SELECT SUBEID FROM SUBEBOLUM WITH(NOLOCK) INNER JOIN TBOLUM WITH(NOLOCK) ON SUBEBOLUM.BOLUMID = TBOLUM.BOLUMID WHERE SUBEID = SUBELER.SUBEID AND WEBRANDEVUVERILIR=1))>1
			SET @RANDEVUSMSMESAJ=@RANDEVUSMSMESAJ+' '+(SELECT SUBEADI FROM SUBELER WHERE SUBEID=@SUBEID)

		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 (ISNULL((SELECT VALUESTR FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='MediWEB' AND KEYNAME='Online randevu suresi yaklasinca hatirlatma smsi iletilsin'),'1')='1')
		BEGIN
								   
			EXEC SPREADID2 N'CRMSMSSBL','',@CRMSMSBLID OUTPUT
			INSERT INTO CRMSMSSBL (SMSSBLID,FROMSTR,GSMNO,CONTENTSTR,DATESEND,SMSSBLADI,OTOKAYITNO,TUR)
				VALUES (
				@CRMSMSBLID,
				ISNULL((SELECT TOP 1 VALUESTR FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='CRMSMS' AND KEYNAME='Standart G'+CHAR(246)+'nderen Ad'+CHAR(253)+''),''),
				ISNULL((SELECT TOP 1 VALUESTR FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='CRMSMS' AND KEYNAME='Standart G'+CHAR(246)+'nderen GSMNO'),''),
				(CASE WHEN @PatientName='ONLINE' OR @PatientName='WEB' THEN '' ELSE 'SAYIN '+@PatientName+' '+@PatientSurname+' ' END)+@BOLUM+' BOLUMUNDE '+@DOKTOR+' ILE RANDEVU SAATINIZ '+LEFT(CONVERT(VARCHAR, @RANDEVUTARIHI, 120),10) + ' ' +CONVERT(VARCHAR(5),@RANDEVUTARIHI,108) , 
				@GONDERIMZAMANI,
				'Muayene Hat'+CHAR(253)+'rlatma Mesaj'+CHAR(253)+' '+@PatientName+' '+@PatientSurname,
				'HRANDEVU-'+CONVERT(VARCHAR,@RANDEVUID),
				'Muayene')

			INSERT INTO CRMSMS (SMSSBLID,HKIMLIKID,TOGSMNO,SENTRESULT,SENDDATE,HRANDEVUID,HMUAYENEID)
			VALUES (@CRMSMSBLID,(CASE WHEN @BULUNANHKIMLIKID=0 THEN NULL ELSE @BULUNANHKIMLIKID END),@Phone,0,@GONDERIMZAMANI,@RANDEVUID,NULL)
		END

		--Randevu mesaj'+CHAR(253)+' doluysa sms olarak iletilir
		IF (NOT (@RANDEVUTURU='MEDWEB' AND (ISNULL((SELECT VALUESTR FROM CONFIG WITH(NOLOCK) WHERE SECTIONSTR='MediWEB' AND KEYNAME='Online randevu alindigi anda hastaya sms olarak iletilsin'),'1')='1')))
		BEGIN
			SET @RANDEVUSMSMESAJ=''		
		END


		--Bir kereye mahsus web '+CHAR(254)+'ifresini yolla
		IF @RANDEVUTURU='MEDWEB' AND @BULUNANHKIMLIKID>0 AND (SELECT TOP 1 HKIMLIKID FROM HKIMLIK WHERE HKIMLIKID=@BULUNANHKIMLIKID AND HWEBSIFRETARIHI IS NULL)>0
		BEGIN
			UPDATE HKIMLIK SET HWEBSIFRETARIHI=DATEADD(MONTH,-5,GETDATE()) WHERE HKIMLIKID=@BULUNANHKIMLIKID
			SET @WEBSIFRE = ISNULL((SELECT HWEBSIFRE FROM HKIMLIK WHERE HKIMLIKID=@BULUNANHKIMLIKID),'')
		END

		IF @BULUNANHKIMLIKID>0 SET @PATIENTNO=DBO.FN_BASIT_ID_SIFRELE(@BULUNANHKIMLIKID)

		SELECT @RANDEVUID as appointmentid, @PATIENTNO AS patientid, @Phone as phone, @RANDEVUSMSMESAJ AS smsmessage, @WEBSIFRE AS passwrd , 1 AS result, 'Randevu ba'+CHAR(254)+'ar'+CHAR(253)+'yla olu'+CHAR(254)+'turuldu' AS resultdesc
		RETURN
	END
	ELSE
	BEGIN
		SELECT 0 AS appointmentid, '' AS patientid, '' AS phone, '' AS smsmessage, '' AS passwrd, 20 AS result, 'Randevu kayd'+CHAR(253)+' olu'+CHAR(254)+'turulamad'+CHAR(253)+'' AS resultdesc
		RETURN
	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_RANDEVUKAYIT] TO [mediwebuser]
	END TRY
	BEGIN CATCH
	END CATCH
END
GO
