IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SPSMS_SABLON]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE SPSMS_SABLON
GO
CREATE PROCEDURE [dbo].[SPSMS_SABLON]
AS
BEGIN
	DECLARE @tblSonuc TABLE (
		gonderimsekli NVARCHAR(50),
		adres NVARCHAR(500),
		soapaction NVARCHAR(500),
		sablon NVARCHAR(2000),
		basariliicerik NVARCHAR(50),
		telnasilbaslar NVARCHAR(1),
		sonuc INT,
		sonucmesaji NVARCHAR(200)
	)

	DECLARE @SmsGonderiFirmasiID INT = ISNULL((SELECT TOP 1 CONVERT(INT,VALUESTR) FROM CONFIG WHERE KEYNAME = 'CRM Sms Gonderi Firmasi ID' AND SECTIONSTR='CRMSMS'),0)
	DECLARE	@firma NVARCHAR(100)='',
			@kullanici NVARCHAR(100)='',
			@sifre NVARCHAR(100)='',
			@kanalkodu NVARCHAR(100)='',
			@originator NVARCHAR(100)='',
			@karalistekontrol NVARCHAR(100)=''

	IF @SmsGonderiFirmasiID=11
	BEGIN
		

		SELECT	@kullanici = ISNULL((SELECT VALUESTR FROM CONFIG WHERE KEYNAME = 'CRM SMS Username' AND SECTIONSTR='CRMSMS'),''),
				@sifre = ISNULL((SELECT VALUESTR FROM CONFIG WHERE KEYNAME = 'CRM SMS Password' AND SECTIONSTR='CRMSMS'),''),
				@kanalkodu = ISNULL((SELECT VALUESTR FROM CONFIG WHERE KEYNAME = 'CRM SMS Channel Code' AND SECTIONSTR='CRMSMS'),''),
				@firma = ISNULL((SELECT VALUESTR FROM CONFIG WHERE KEYNAME = 'CRM SMS Company' AND SECTIONSTR='CRMSMS'),''),
				@originator = ISNULL((SELECT VALUESTR FROM CONFIG WHERE KEYNAME = 'CRM SMS Originator' AND SECTIONSTR='CRMSMS'),''),
				@karalistekontrol = ISNULL((SELECT VALUESTR FROM CONFIG WHERE KEYNAME = 'CRM SMS BlackList Kontrol Et' AND SECTIONSTR='CRMSMS'),'0')

		IF @kullanici<>'' or @sifre<>'' 
		BEGIN

			INSERT INTO @tblSonuc 
				SELECT 	'soap' AS gonderimsekli,
						'https://webservice.asistiletisim.com.tr/SmsProxy.asmx?WSDL' AS adres,
						'https://webservice.asistiletisim.com.tr/SmsProxy/sendSms' AS soapaction,
						'<?xml version="1.0" encoding="UTF-8"?><soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns="https://webservice.asistiletisim.com.tr/SmsProxy">'+
						'<soapenv:Header/><soapenv:Body><sendSms><requestXml><![CDATA[<SendSms><Username>'+@kullanici+'</Username><Password>'+@sifre+'</Password><UserCode>'+@kanalkodu+'</UserCode>'+
						'<AccountId>'+@firma+'</AccountId><Originator>'+@originator+'</Originator><SendDate></SendDate><ValidityPeriod>60</ValidityPeriod><MessageText>#mesaj#</MessageText>'+
						'<IsCheckBlackList>0</IsCheckBlackList><ReceiverList><Receiver>#tel#</Receiver></ReceiverList></SendSms>]]></requestXml></sendSms></soapenv:Body></soapenv:Envelope>' AS sablon,
							'<ErrorCode>0' as basariliicerik,
							'5' as telnasilbaslar,
						1 as sonuc,
						'Bilgiler alındı' as sonucmesaji
		END
	END
	ELSE IF @SmsGonderiFirmasiID=9
	BEGIN
		
		select top 1 @kullanici=SMSKULLANICIKODU,@sifre=SMSKULLANICISIFRE,@kanalkodu=SMSKANALKODU,@originator=SMSORIGINATOR from KURUM WHERE SMSKULLANICIKODU IS NOT NULL

		IF @kullanici<>'' or @sifre<>'' 
		BEGIN

			INSERT INTO @tblSonuc 
				SELECT 	'xmlpost' AS gonderimsekli,
						'https://api.netgsm.com.tr/sms/send/xml' AS adres,
						'' AS soapaction,
						'<?xml version="1.0" encoding="UTF-8"?>
							<mainbody>
							   <header>
								<company dil="TR">Netgsm</company>				
								<usercode>'+@kullanici+'</usercode>
								<password>'+@sifre+'</password>
								<type>1:n</type>
								<msgheader>'+@originator+'</msgheader>
							   </header>
							   <body>
							   <msg>
							   <![CDATA[#mesaj#]]>
							   </msg>
							   <no>#tel#</no>
							   </body>
							</mainbody>' AS sablon,
							'00' as basariliicerik,
							'5' as telnasilbaslar,
						1 as sonuc,
						'Bilgiler alındı' as sonucmesaji
		END
	END
	ELSE IF @SmsGonderiFirmasiID=7
	BEGIN
		
		select top 1 @kullanici=SMSKULLANICIKODU,@sifre=SMSKULLANICISIFRE,@kanalkodu=SMSKANALKODU,@originator=SMSORIGINATOR from KURUM WHERE SMSKULLANICIKODU IS NOT NULL

		IF @kullanici<>'' or @sifre<>'' 
		BEGIN

			INSERT INTO @tblSonuc 
				SELECT 	'get' AS gonderimsekli,
						'http://gw.ekomesaj.com.tr/v1/xml/syncreply/Submit?Credential={Username:'+@kullanici+',Password:'+@sifre+'}&DataCoding=Default&Header={From:'+@originator+',ValidityPeriod:1440,Route:0}&Message=#mesaj#&To=#tel#}' AS adres,
						'' AS soapaction,
						'' AS sablon,
							'200' as basariliicerik,
						'9' as telnasilbaslar,
						1 as sonuc,
						'Bilgiler alındı' as sonucmesaji
		END
	END
	ELSE IF @SmsGonderiFirmasiID=2
	BEGIN
		
		select top 1 @kullanici=SMSKULLANICIKODU,@sifre=SMSKULLANICISIFRE,@kanalkodu=SMSKANALKODU,@originator=SMSORIGINATOR from KURUM WHERE SMSKULLANICIKODU IS NOT NULL

		IF @kullanici<>'' or @sifre<>'' 
		BEGIN

			INSERT INTO @tblSonuc 
				SELECT 	'xmlpost' AS gonderimsekli,
						'http://processor.smsorigin.com/xml/process.aspx' AS adres,
						'' AS soapaction,
						'<?xml version="1.0" encoding="utf-8" ?>
							<MainmsgBody>
							<Command>0</Command>
							<PlatformID>1</PlatformID>
							<ChannelCode>'+@kanalkodu+'</ChannelCode>
							<UserName>'+@kullanici+'</UserName>
							<PassWord>'+@sifre+'</PassWord>
							<Mesgbody>#mesaj#</Mesgbody>
							<Numbers>#tel#</Numbers>
							<Type>1</Type>
							<Originator>'+@originator+'</Originator>
							</MainmsgBody>' AS sablon,
							'ID' as basariliicerik,
							'5' as telnasilbaslar,
						1 as sonuc,
						'Bilgiler alındı' as sonucmesaji
		END
	END
	ELSE IF @SmsGonderiFirmasiID=99 OR @SmsGonderiFirmasiID=1324
	BEGIN
		
		DECLARE @SMSURL NVARCHAR(300)='',
				@SMSSABITALANADI NVARCHAR(300)='',
				@SMSSABITDEGER NVARCHAR(300)='',
				@SMSSIFREALANADI NVARCHAR(300)='',
				@SMSKULLANICIALANADI NVARCHAR(300)='',
				@SMSMESAJALANADI NVARCHAR(300)='',
				@SMSTELALANADI NVARCHAR(300)='',
				@SMSBASLIKALANADI NVARCHAR(300)='',
				@SMSKANALKODALANADI NVARCHAR(300)='',
				@SMSFIRMAKODALANADI NVARCHAR(300)='',
				@SMSKULLANICIKODU NVARCHAR(300)='',
				@SMSKULLANICISIFRE NVARCHAR(300)='',
				@SMSKANALKODU NVARCHAR(300)='',
				@SMSFIRMA NVARCHAR(300)='',
				@SMSORIGINATOR NVARCHAR(300)='',
				@SMSBLACKLISTCONTROL NVARCHAR(300)=''
		SELECT 
		@SMSURL=ISNULL((SELECT VALUESTR FROM CONFIG WHERE SECTIONSTR='Get Sms Tanim' AND KEYNAME = 'Get Yöntemi SMS API Adresi'),'http://api.smsvitrini.com/index.php'), 
		@SMSSABITALANADI=ISNULL((SELECT VALUESTR FROM CONFIG WHERE SECTIONSTR='Get Sms Tanim' AND KEYNAME = '<Sabit Parametre> alan adi'),'islem'), 
		@SMSSABITDEGER=ISNULL((SELECT VALUESTR FROM CONFIG WHERE SECTIONSTR='CRMSMS' AND KEYNAME = 'CRM SMS Sabit Parametre Değeri'),'1'), 
		@SMSSIFREALANADI=ISNULL((SELECT VALUESTR FROM CONFIG WHERE SECTIONSTR='Get Sms Tanim' AND KEYNAME = '<Sifre> alan adi'),'pass'), 
		@SMSKULLANICIALANADI=ISNULL((SELECT VALUESTR FROM CONFIG WHERE SECTIONSTR='Get Sms Tanim' AND KEYNAME = '<Kullanici Adi> alan adi'),'user'), 
		@SMSMESAJALANADI=ISNULL((SELECT VALUESTR FROM CONFIG WHERE SECTIONSTR='Get Sms Tanim' AND KEYNAME = '<Mesaj> alan adi'),'mesaj'), 
		@SMSTELALANADI=ISNULL((SELECT VALUESTR FROM CONFIG WHERE SECTIONSTR='Get Sms Tanim' AND KEYNAME = '<Gönderim Tel> alan adi'),'numaralar'), 
		@SMSBASLIKALANADI=ISNULL((SELECT VALUESTR FROM CONFIG WHERE SECTIONSTR='Get Sms Tanim' AND KEYNAME = '<Başlık> alan adi'),'baslik'), 
		@SMSKANALKODALANADI=ISNULL((SELECT VALUESTR FROM CONFIG WHERE SECTIONSTR='Get Sms Tanim' AND KEYNAME = '<Kanal Kodu> alan adi'),''), 
		@SMSFIRMAKODALANADI=ISNULL((SELECT VALUESTR FROM CONFIG WHERE SECTIONSTR='Get Sms Tanim' AND KEYNAME = '<Firma Kodu> alan adi'),''),
		@SMSKULLANICIKODU=ISNULL((SELECT VALUESTR FROM CONFIG WHERE KEYNAME = 'CRM SMS Username' AND SECTIONSTR='CRMSMS'),''), 
		@SMSKULLANICISIFRE=ISNULL((SELECT VALUESTR FROM CONFIG WHERE KEYNAME = 'CRM SMS Password' AND SECTIONSTR='CRMSMS'),''), 
		@SMSKANALKODU=ISNULL((SELECT VALUESTR FROM CONFIG WHERE KEYNAME = 'CRM SMS Channel Code' AND SECTIONSTR='CRMSMS'),''), 
		@SMSFIRMA=ISNULL((SELECT VALUESTR FROM CONFIG WHERE KEYNAME = 'CRM SMS Company' AND SECTIONSTR='CRMSMS'),''), 
		@SMSORIGINATOR=ISNULL((SELECT VALUESTR FROM CONFIG WHERE KEYNAME = 'CRM SMS Originator' AND SECTIONSTR='CRMSMS'),''), 
		@SMSBLACKLISTCONTROL=ISNULL((SELECT VALUESTR FROM CONFIG WHERE KEYNAME = 'CRM SMS BlackList Kontrol Et' AND SECTIONSTR='CRMSMS'),'0')

		SET @SMSURL = @SMSURL+'?'
		IF @SMSSABITALANADI<>'' AND @SMSSABITDEGER<>'' SET @SMSURL = @SMSURL+@SMSSABITALANADI+'='+@SMSSABITDEGER+'&'
		IF @SMSKULLANICIALANADI<>'' AND @SMSKULLANICIKODU<>'' SET @SMSURL = @SMSURL+@SMSKULLANICIALANADI+'='+@SMSKULLANICIKODU+'&'
		IF @SMSSIFREALANADI<>'' AND @SMSKULLANICISIFRE<>'' SET @SMSURL = @SMSURL+@SMSSIFREALANADI+'='+@SMSKULLANICISIFRE+'&'
		IF @SMSTELALANADI<>'' SET @SMSURL = @SMSURL+@SMSTELALANADI+'=#tel#&'
		IF @SMSBASLIKALANADI<>'' AND @SMSORIGINATOR<>'' SET @SMSURL = @SMSURL+@SMSBASLIKALANADI+'='+@SMSORIGINATOR+'&'
		IF @SMSKANALKODALANADI<>'' AND @SMSKANALKODU<>'' SET @SMSURL = @SMSURL+@SMSKANALKODALANADI+'='+@SMSKANALKODU+'&'
		IF @SMSFIRMAKODALANADI<>'' AND @SMSFIRMA<>'' SET @SMSURL = @SMSURL+@SMSFIRMAKODALANADI+'='+@SMSFIRMA+'&'
		IF @SMSMESAJALANADI<>'' SET @SMSURL = @SMSURL+@SMSMESAJALANADI+'=#mesaj#'

		

		

		IF @SMSKULLANICIKODU<>'' or @SMSKULLANICISIFRE<>'' 
		BEGIN

			INSERT INTO @tblSonuc 
				SELECT 	'get' AS gonderimsekli,
						@SMSURL AS adres,
						'' AS soapaction,
						'' AS sablon,
						'200' as basariliicerik,
						'9' as telnasilbaslar,
						1 as sonuc,
						'Bilgiler alındı' as sonucmesaji
		END
		
	END
	ELSE
	BEGIN
		select top 1 @kullanici=SMSKULLANICIKODU,@sifre=SMSKULLANICISIFRE,@kanalkodu=SMSKANALKODU,@originator=SMSORIGINATOR from KURUM WHERE SMSKULLANICIKODU IS NOT NULL

		IF @kullanici<>'' or @sifre<>'' 
		BEGIN

			INSERT INTO @tblSonuc 
				SELECT 	'get' AS gonderimsekli,
						'http://www.postaguvercini.com/api_http/sendsms.asp?user='+@kullanici+'&password='+@sifre+'&gsm=#tel#&text=#mesaj#' AS adres,
						'' AS soapaction,
						'' AS sablon,
							'200' as basariliicerik,
						'9' as telnasilbaslar,
						1 as sonuc,
						'Bilgiler alındı' as sonucmesaji
		END
		
	END

	IF (SELECT COUNT(*) FROM @tblSonuc)=0
		INSERT INTO @tblSonuc (gonderimsekli,adres,soapaction,sablon,basariliicerik,telnasilbaslar,sonuc,sonucmesaji) VALUES ('','','','','','',0,'Sms sağlayıcı bulumanadı')


	select gonderimsekli,adres,soapaction,sablon,sonuc,sonucmesaji from @tblSonuc
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].[SPSMS_SABLON] TO [mediwebuser]
	END TRY
	BEGIN CATCH
	END CATCH
END
GO


