alter proc [dbo].[Security_UpdateById]
@Id as nvarchar(256),
@Name as nvarchar(512),
@Code as nvarchar(256),
@Class as nvarchar(256),
@ShortName as nvarchar(256),
@MinStepSize decimal(18,7),
@MinStepPrice decimal(18,7),
@MinLotSize real,
@Decimals real,
@OpenPrice decimal(18,7),
@ClosePrice decimal(18,7),
@LowPrice decimal(18,7),
@HighPrice decimal(18,7),
@State int,
@Type int,
@MinPrice decimal(18,7),
@MaxPrice decimal(18,7),
@MarginBuy decimal(18,7),
@MarginSell decimal(18,7),
@ExpiryDate datetime,
@SettlementDate datetime,
@ExtensionInfo nvarchar(max),
@LastTradeId bigint = null,
@LastTradeTime datetime2(7) = null,
@LastTradePrice decimal(18,7) = null,
@LastTradeVolume decimal(18,7) = null,
@LastTradeOrderDirection int = null,
@BestBidPrice decimal(18,7) = null,
@BestBidVolume decimal(18,7) = null,
@BestBidOrderDirection int = null,
@BestAskPrice decimal(18,7) = null,
@BestAskVolume decimal(18,7) = null,
@BestAskOrderDirection int = null,
@Exchange nvarchar(64),
@UnderlyingSecurityId nvarchar(256),
@Strike decimal(18,7),
@OptionType int,
@Volatility decimal(18,7),
@TheorPrice decimal(18,7)
as
begin transaction
declare @ExtensionInfoXml xml
set @ExtensionInfoXml = convert(xml, @ExtensionInfo, 1)
declare @FinamMarketId bigint
declare @FinamSecurityId bigint
declare @Source nvarchar(128)
declare @TradeSource nvarchar(128)
declare @DepthSource nvarchar(128)
declare @IsSelected bit
declare @TradeCount int
declare @DepthCount int
declare @LastUpdateTime datetime
set @FinamMarketId = @ExtensionInfoXml.value(N'(/IDictionaryBeginObjectAndObjectEnd/KeyValuePairBeginObjectAndObjectEnd[key/Value = "FinamMarketId"]/value/Value)[1]', 'bigint')
set @FinamSecurityId = @ExtensionInfoXml.value(N'(/IDictionaryBeginObjectAndObjectEnd/KeyValuePairBeginObjectAndObjectEnd[key/Value = "FinamSecurityId"]/value/Value)[1]', 'bigint')
set @Source = @ExtensionInfoXml.value(N'(/IDictionaryBeginObjectAndObjectEnd/KeyValuePairBeginObjectAndObjectEnd[key/Value = "Source"]/value/Value)[1]', 'nvarchar(128)')
set @TradeSource = @ExtensionInfoXml.value(N'(/IDictionaryBeginObjectAndObjectEnd/KeyValuePairBeginObjectAndObjectEnd[key/Value = "TradeSource"]/value/Value)[1]', 'nvarchar(128)')
set @DepthSource = @ExtensionInfoXml.value(N'(/IDictionaryBeginObjectAndObjectEnd/KeyValuePairBeginObjectAndObjectEnd[key/Value = "DepthSource"]/value/Value)[1]', 'nvarchar(128)')
set @IsSelected = IsNull(@ExtensionInfoXml.value(N'(/IDictionaryBeginObjectAndObjectEnd/KeyValuePairBeginObjectAndObjectEnd[key/Value = "IsSelected"]/value/Value)[1]', 'bit'), 0)
set @TradeCount = IsNull(@ExtensionInfoXml.value(N'(/IDictionaryBeginObjectAndObjectEnd/KeyValuePairBeginObjectAndObjectEnd[key/Value = "TradeCount"]/value/Value)[1]', 'int'), 0)
set @DepthCount = IsNull(@ExtensionInfoXml.value(N'(/IDictionaryBeginObjectAndObjectEnd/KeyValuePairBeginObjectAndObjectEnd[key/Value = "DepthCount"]/value/Value)[1]', 'int'), 0)
--set @LastUpdateTime = @ExtensionInfoXml.value(N'(/IDictionaryBeginObjectAndObjectEnd/KeyValuePairBeginObjectAndObjectEnd[key/Value = "LastUpdateTime"]/value/Value)[1]', 'datetime')
begin try
if (exists(select * from [Security] where Id = @Id))
begin
update [Security]
set
Name = @Name,
Code = @Code,
Class = @Class,
ShortName = @ShortName,
MinStepSize = @MinStepSize,
MinStepPrice = @MinStepPrice,
MinLotSize = @MinLotSize,
Decimals = @Decimals,
OpenPrice = @OpenPrice,
ClosePrice = @ClosePrice,
LowPrice = @LowPrice,
HighPrice = @HighPrice,
[State] = @State,
[Type] = @Type,
MinPrice = @MinPrice,
MaxPrice = @MaxPrice,
MarginBuy = @MarginBuy,
MarginSell = @MarginSell,
ExpiryDate = @ExpiryDate,
SettlementDate = @SettlementDate,
ExtensionInfo = @ExtensionInfo,
LastTradeId = @LastTradeId,
LastTradeTime = @LastTradeTime,
LastTradePrice = @LastTradePrice,
LastTradeVolume = @LastTradeVolume,
LastTradeOrderDirection = @LastTradeOrderDirection,
BestBidPrice = @BestBidPrice,
BestBidVolume = @BestBidVolume,
BestBidOrderDirection = @BestBidOrderDirection,
BestAskPrice = @BestAskPrice,
BestAskVolume = @BestAskVolume,
BestAskOrderDirection = @BestAskOrderDirection,
Exchange = @Exchange,
UnderlyingSecurityId = @UnderlyingSecurityId,
Strike = @Strike,
OptionType = @OptionType,
Volatility = @Volatility,
TheorPrice = @TheorPrice
where
Id = @Id
update HydraSecurityInfo
set
FinamMarketId = @FinamMarketId,
FinamSecurityId = @FinamSecurityId,
[Source] = @Source,
TradeSource = @TradeSource,
DepthSource = @DepthSource,
IsSelected = @IsSelected,
TradeCount = @TradeCount,
DepthCount = @DepthCount,
LastUpdateTime = @LastUpdateTime
where
[Security] = @Id
end
else
begin
insert into [Security]
(Id, Name, Code, Class, ShortName, MinStepSize, MinStepPrice, MinLotSize, Decimals,
OpenPrice, ClosePrice, LowPrice, HighPrice, [State], [Type], MinPrice, MaxPrice, MarginBuy, MarginSell, ExpiryDate,
SettlementDate, ExtensionInfo, LastTradeId, LastTradeTime, LastTradePrice, LastTradeVolume, LastTradeOrderDirection,
BestBidPrice, BestBidVolume, BestBidOrderDirection, BestAskPrice, BestAskVolume, BestAskOrderDirection, Exchange,
OptionType, Strike,UnderlyingSecurityId, Volatility, TheorPrice)
values
(@Id, @Name, @Code, @Class, @ShortName, @MinStepSize, @MinStepPrice, @MinLotSize, @Decimals,
@OpenPrice, @ClosePrice, @LowPrice, @HighPrice, @State, @Type, @MinPrice, @MaxPrice, @MarginBuy, @MarginSell, @ExpiryDate,
@SettlementDate, @ExtensionInfo, @LastTradeId, @LastTradeTime, @LastTradePrice, @LastTradeVolume, @LastTradeOrderDirection,
@BestBidPrice, @BestBidVolume, @BestBidOrderDirection, @BestAskPrice, @BestAskVolume, @BestAskOrderDirection, @Exchange,
@OptionType, @Strike, @UnderlyingSecurityId, @Volatility, @TheorPrice)
insert into HydraSecurityInfo
([Security], TradeSource, DepthSource, [Source], IsSelected, TradeCount, DepthCount, FinamMarketId, FinamSecurityId, LastUpdateTime)
values
(@Id, @TradeSource, @DepthSource, @Source, @IsSelected, @TradeCount, @DepthCount, @FinamMarketId, @FinamSecurityId, @LastUpdateTime)
end
end try
begin catch
if @@TRANCOUNT > 0
rollback transaction
print 'Error Number: ' + str(error_number())
print 'Line Number: ' + str(error_line())
print error_message()
exec usp_RethrowError
end catch
if @@TRANCOUNT > 0
commit transaction
GO
ALTER TABLE [dbo].[Security] ALTER COLUMN [Strike] DECIMAL (18,7) NOT NULL;
ALTER TABLE [dbo].[Security] ALTER COLUMN [TheorPrice] DECIMAL (18,7) NOT NULL;
ALTER TABLE [dbo].[Security] ALTER COLUMN [Volatility] DECIMAL (18,7) NOT NULL;
ALTER TABLE [dbo].[Security] ALTER COLUMN [BestAskOrderDirection] INT NULL;
ALTER TABLE [dbo].[Security] ALTER COLUMN [BestAskPrice] DECIMAL (18,7) NULL;
ALTER TABLE [dbo].[Security] ALTER COLUMN [BestAskVolume] DECIMAL (18,7) NULL;
ALTER TABLE [dbo].[Security] ALTER COLUMN [BestBidOrderDirection] INT NULL;
ALTER TABLE [dbo].[Security] ALTER COLUMN [BestBidPrice] DECIMAL (18,7) NULL;
ALTER TABLE [dbo].[Security] ALTER COLUMN [BestBidVolume] DECIMAL (18,7) NULL;
ALTER TABLE [dbo].[Security] ALTER COLUMN [LastTradeId] BIGINT NULL;
ALTER TABLE [dbo].[Security] ALTER COLUMN [LastTradePrice] DECIMAL (18,7) NULL;
ALTER TABLE [dbo].[Security] ALTER COLUMN [LastTradeTime] DATETIME2 (7) NULL;
ALTER TABLE [dbo].[Security] ALTER COLUMN [LastTradeVolume] DECIMAL (18,7) NULL;
ALTER TABLE [dbo].[MarketDataSourceSettings] add FilterBySecurities bit NULL;
go
update [dbo].[MarketDataSourceSettings]
set
[StorageFolder] = N''
where
[StorageFolder] is null
update [dbo].[MarketDataSourceSettings]
set
FilterBySecurities = 0
where
FilterBySecurities is null
go
ALTER TABLE [dbo].[MarketDataSourceSettings] ALTER COLUMN [StorageFolder] [nvarchar](2048) NOT NULL;
ALTER TABLE [dbo].[MarketDataSourceSettings] ALTER COLUMN FilterBySecurities bit NOT NULL;
go
alter proc [dbo].[MarketDataSourceSettings_UpdateBySourceId]
@SourceId as uniqueidentifier,
@IsEnabled as bit,
@WorkingFrom as time(7),
@WorkingTo as time(7),
@Interval as time(7),
@DumpFolder nvarchar(2048),
@StorageFolder nvarchar(2048),
@ExtensionInfo nvarchar(max),
@FilterBySecurities bit
as
if (exists(select * from MarketDataSourceSettings where SourceId = @SourceId))
update MarketDataSourceSettings
set
IsEnabled = @IsEnabled,
WorkingFrom = @WorkingFrom,
WorkingTo = @WorkingTo,
Interval = @Interval,
DumpFolder = @DumpFolder,
StorageFolder = @StorageFolder,
ExtensionInfo = @ExtensionInfo,
FilterBySecurities = @FilterBySecurities
where
SourceId = @SourceId
else
insert into MarketDataSourceSettings
(SourceId, IsEnabled, WorkingFrom, WorkingTo, Interval, DumpFolder, StorageFolder, ExtensionInfo, FilterBySecurities)
values
(@SourceId, @IsEnabled, @WorkingFrom, @WorkingTo, @Interval, @DumpFolder, @StorageFolder, @ExtensionInfo, @FilterBySecurities)