CREATE PROCEDURE SPNetGetGasDensity
	@SPNetHandle int,
	@RequestID int,
	@Device int,
	@Channel int,
	@Array int,
	@ArrayIndex int,
	@DateTime datetime
AS

declare @rc as int

-- Временная таблица результатов запросов к СПСети
create table #GetGasDensityResultTMP (
	Device int,
	Channel int,
	Array int,
	ArrayIndex int,
	Value varchar(32),
	Unit varchar(32),
	DateTime varchar(32)
)


insert into #GetGasDensityResultTMP exec @rc = master..xp_SPNet_get_array @SPNetHandle, 7, 1, 125, 9, 1

-- Запрос удачен?
if @rc = 0  and @@rowcount != 0 begin
	-- Данные числовые?
	declare @Value as varchar(32)
	select @Value = Value from #GetGasDensityResultTMP

	if isnumeric(@Value) = 1 begin
		declare @SaveValueResult as int

		-- Помещение результата запроса в архив c избежанием дублирования 
		update SPNetArchive set Value = cast(Value as float), StoredInOracle = 0 where RequestID = @RequestID and [DateTime] = @DateTime
		set @SaveValueResult = @@rowcount

		if @SaveValueResult = 0 begin
			insert SPNetArchive select @RequestID as RequestID, @DateTime, cast(Value as float), Unit, 0 as StoredInOracle from #GetGasDensityResultTMP
			set @SaveValueResult = @@rowcount
		end

		-- Удачная попытка добавиления?
		if @SaveValueResult != 0 begin
			-- Удаление запроса из очереди
			delete from SPNetRequestsQueue where RequestID = @RequestID and DateTime = @DateTime
			set @rc = 0
		end
		else begin
			set @rc = 1
		end
	end
	else begin
		set @rc = 1
	end
end

-- Очистка результата запроса
delete #GetGasDensityResultTMP

return @rc
GO

exec SPNetGetGasDensity 1, 226, 7, 1, 125, 9, "20180303"

CREATE PROCEDURE SPNetGetGasDensity
	--@Handle int,
	--@RequestID int,
	--@Device int,
	--@Channel int,
	--@Array int,
	--@ArrayIndex int,
	--@DateTime datetime
            --set  @DateTime = getdate
AS
--SET NOCOUNT ON
create table #SPNetMain (
	Port varchar(16),
	Baud int,
	Timeout int,
	ConnDevAddr int,
	RequestDelay int
)

insert #SPNetMain select Port, Baud, Timeout, ConnDevAddr, RequestDelay from SPNetworks where id = 1

declare @Port as varchar(16)
declare @Baud as int
declare @Timeout as int
declare @ConnDevAddr as int
declare @RequestDelay as int

select @Port = Port from #SPNetMain
select @Baud = Baud from #SPNetMain
select @Timeout = Timeout from #SPNetMain
select @ConnDevAddr = ConnDevAddr from #SPNetMain
select @RequestDelay = RequestDelay from #SPNetMain

drop table #SPNetMain

declare @Handle int
declare @rc int

exec @rc = master..xp_SPNet_connect @Port, @Baud, @Timeout, @ConnDevAddr, @Handle OUTPUT
--declare @rc as int

-- Временная таблица результатов запросов к СПСети
create table #GetGasDensityResultTMP (
	Device int,
	Channel int,
	Array int,
	ArrayIndex int,
	Value varchar(32),
	Unit varchar(32),
	DateTime varchar(32)
)

--set  @DateTime = GETDATE()
insert into #GetGasDensityResultTMP exec @rc = master..xp_SPNet_get_array @Handle, 7, 1, 125, 9, 1

-- Запрос удачен?
if @rc = 0  and @@rowcount != 0 begin
	-- Данные числовые?
	declare @Value as varchar(32)
	select @Value = Value from #GetGasDensityResultTMP

	if isnumeric(@Value) = 1 begin
		declare @SaveValueResult as int

		-- Помещение результата запроса в архив c избежанием дублирования 
		update SPNetArchive set Value = cast(Value as float), StoredInOracle = 0 where RequestID = 226 and [DateTime] = getdate()
		set @SaveValueResult = @@rowcount

		if @SaveValueResult = 0 begin
			insert SPNetArchive select 226 as RequestID, getdate(), cast(Value as float), Unit, 0 as StoredInOracle from #GetGasDensityResultTMP
			set @SaveValueResult = @@rowcount
		end

		-- Удачная попытка добавления?
		if @SaveValueResult != 0 begin
			-- Удаление запроса из очереди
			delete from SPNetRequestsQueue where RequestID = 226 and DateTime = getdate()
			set @rc = 0
		end
		else begin
			set @rc = 1
		end
	end
	else begin
		set @rc = 1
	end
end
exec master..xp_SPNet_disconnect @Handle
-- Очистка результата запроса
delete #GetGasDensityResultTMP

return @rc
GO