-- =============================================
-- Author:		Evgeny Bilik
-- Create date: 2013-09-10
-- Description:	Calls web service to sign  XML files
-- =============================================
/*


DECLARE @Rslt NVARCHAR(MAX)
EXEC [dbo].[pr_SMS_WebService] '2','0508948644','0542266651','Test SMS  form Pocedure', @Rslt out 
SELECT @Rslt


*/

ALTER PROCEDURE [dbo].[pr_SMS_WebService] (
	@AUId varchar(100),
	@FromMobileNumber varchar(100),
	@toMobileNumber nvarchar(100),
	@SmsBody nvarchar(4000),
	@Rslt NVARCHAR(1000) OUTPUT
)
AS
BEGIN

	SET NOCOUNT ON;
	
	DECLARE
		 
		  @methodName varchar(50),
		  @requestBody varchar(8000),
		  @SoapAction varchar(255), 
		  @UserName nvarchar(100), -- Domain\UserName or UserName 
		  @Password nvarchar(100), 
		  @responseText nvarchar(max),
		  @URL_WebService nvarchar(4000)=''


      ---    Эта фукция чтения параметров из таблицы параметров ---------------------------        
    SELECT @URL_WebService = dbo.fun_LookupsParamGetValue('WebService','SMS')


	
	SET @methodName = 'POST' 
	SET @SoapAction = 'http://tempuri.org/SendSmsMessage'

	--PRINT N'@strXML: ' + @strXML
	RAISERROR ('' ,0,0) WITH NOWAIT

	
	SET @requestBody = '<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
	  <soap:Body>
		  <SendSmsMessage xmlns="http://tempuri.org/">
		  <AUId>'+@AUId+'</AUId>
		  <FromMobileNumber>'+@FromMobileNumber+'</FromMobileNumber>
		  <toMobileNumber>'+@toMobileNumber+'</toMobileNumber>
		  <SmsBody>'+@SmsBody+'</SmsBody>
		</SendSmsMessage>
	  </soap:Body>
	</soap:Envelope>'


 
	
	IF @methodName = ''
		BEGIN
			  select FailPoint = 'Method Name must be set'
			  return
		END

	SET @responseText = 'FAILED'

	DECLARE @objectID int
	DECLARE @hResult int
	DECLARE @source varchar(255), @desc varchar(255)
	DECLARE @t table (response nvarchar(max)) 

	EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT

	IF @hResult <> 0 
		BEGIN
			  EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
			  SELECT hResult = convert(varbinary(4), @hResult), 
				  source = @source, 
				  description = @desc, 
				  FailPoint = 'Create failed', 
				  MedthodName = @methodName 
			  GOTO DESTROY 
			  RETURN
		END

	-- open the destination URI with Specified method 
	EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URL_WebService, 'false', @UserName, @Password

	IF @hResult <> 0 
		BEGIN
			  EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
			  SELECT hResult = convert(varbinary(4), @hResult), 
					source = @source, 
					description = @desc, 
					FailPoint = 'Open failed', 
					MedthodName = @methodName 
			  GOTO DESTROY 
			  RETURN
		END

	-- set request headers 
	EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'text/xml;charset=UTF-8'
	--EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'text/xml;charset=windows-1255'

	IF @hResult <> 0 
		BEGIN
			  EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
			  SELECT hResult = convert(varbinary(4), @hResult), 
					source = @source, 
					description = @desc, 
					FailPoint = 'SetRequestHeader failed', 
					MedthodName = @methodName 
			  GOTO DESTROY 
			  RETURN
		END

	-- set soap action 
	EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'SOAPAction', @SoapAction 

	IF @hResult <> 0 
		BEGIN
			  EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
			  SELECT hResult = convert(varbinary(4), @hResult), 
					source = @source, 
					description = @desc, 
					FailPoint = 'SetRequestHeader failed', 
					MedthodName = @methodName 
			  GOTO DESTROY 
			  RETURN
		END

	DECLARE @len int
	SET @len = len(@requestBody) 

	EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len 

	IF @hResult <> 0 
		BEGIN
			  EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
			  SELECT hResult = convert(varbinary(4), @hResult), 
					source = @source, 
					description = @desc, 
					FailPoint = 'SetRequestHeader failed', 
					MedthodName = @methodName 
			  GOTO DESTROY 
			  RETURN
		END


	-- send the request 
	EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody 

	IF @hResult <> 0 
		BEGIN
			  EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
			  SELECT hResult = convert(varbinary(4), @hResult), 
					source = @source, 
					description = @desc, 
					FailPoint = 'Send failed', 
					MedthodName = @methodName 
					SET @Rslt ='Send failed'
			  GOTO DESTROY 
			  RETURN
		END
	-- Get status text 
	DECLARE @statusText varchar(1000), @status varchar(1000) --mld will be output, because it shows success/failure
	EXEC sp_OAGetProperty @objectID, 'StatusText', @statusText out
	EXEC sp_OAGetProperty @objectID, 'Status', @status out

	

	-- Get status text 

	--SELECT @status [status], @statusText statusText, @methodName methodName --, @responseText responseText --mld SELECT

	---- Get response text 
	--exec sp_OAGetProperty @objectID, 'responseText', @responseText out

	INSERT INTO @t (response) 
		 EXEC sp_OAGetProperty @objectID, 'responseText'

	--SELECT '@t', * FROM @t
	SELECT @responseText = response FROM @t
	--SELECT @responseText responseText --mld SELECT

	IF @hResult <> 0 
		BEGIN
			  EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
			  SELECT hResult = convert(varbinary(4), @hResult), 
					source = @source, 
					description = @desc, 
					FailPoint = 'ResponseText failed', 
					MedthodName = @methodName 
					SET @Rslt =''
			  GOTO DESTROY 
			  RETURN
		END

/***************************************************************************************************************/
--Parse Result XML to get SignedXML

	SELECT @responseText = replace(@responseText, 'encoding="utf-8"', 'encoding="utf-16"')

	DECLARE @xDoc as xml
	SET @xDoc = CAST(@responseText as xml)

	SELECT @status [status], @statusText statusText, @methodName methodName , @xDoc [@xDoc]
    SET @Rslt = @statusText
/***************************************************************************************************************/

	DESTROY: 
		  EXEC sp_OADestroy @objectID 


END