public void CreateQueryUserSpotDealsStoredProcedure(OptionDbCtx db)
{
string sql = @"if exists (select * from sys.objects where object_id=OBJECT_ID(N'QueryUserSpotDeals') and type='P')
drop procedure QueryUserSpotDeals
execute('create procedure QueryUserSpotDeals
@name nvarchar(100),
@take int,
@skip int
as
begin
select top (@take) *
from(
select
ROW_NUMBER() over(order by (a.Id) desc ) as Rid,
a.MainId as MainOrderId,
b.ReportCount as ReportCount,
b.Direction as Direction,
b.CoinId as OrderPolicy,
b.OrderTime as OrderTime,
b.Count as Count,
b.Price as Price,
b.[State] as [State],
a.[When] as DealTime,
a.[Count] as DealCount,
a.Price as DealPrice
from SpotDeals a left join SpotOrders b on a.MainId=b.Id
where a.MainTraderName=@name
union
select
ROW_NUMBER() over(order by (a.Id) desc ) as Rid,
a.SlaveId as MainOrderId,
c.ReportCount as ReportCount,
c.Direction as Direction,
c.CoinId as OrderPolicy,
c.OrderTime as OrderTime,
c.Count as Count,
c.Price as Price,
c.[State] as [State],
a.[When] as DealTime,
a.[Count] as DealCount,
a.Price as DealPrice
from SpotDeals a
left join SpotOrders c on a.SlaveId =c.Id
where a.SlaveTraderId=@name
) d
where d.Rid>@skip
end')";
db.Database.ExecuteSqlCommand(sql);
}
public void CreateTempSpotOrder(OptionDbCtx context)
{
var conn = context.Database.Connection.Database;
var sql1 = "use [" + conn + "] ";
var sql2 = @"
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
if not exists (select * from dbo.sysobjects where id = object_id(N'TempSpotOrders') and type='U') begin
CREATE TABLE [dbo].[TempSpotOrders](
[Id] [int] NOT NULL,
[TraderId] [int] NOT NULL,
[CoinId] [int] NOT NULL,
[Direction] [int] NOT NULL,
[ReportCount] [decimal](18, 2) NOT NULL,
[Price] [decimal](18, 2) NOT NULL,
[Count] [decimal](18, 2) NOT NULL,
[DoneCount] [decimal](18, 2) NOT NULL,
[TotalDoneCount] [decimal](18, 2) NOT NULL,
[OrderTime] [datetime] NOT NULL,
[State] [int] NOT NULL,
[RequestStatus] [int] NOT NULL,
[DonePrice] [decimal](18, 2) NOT NULL,
[IsBySystem] [bit] NOT NULL,
[TotalDoneSum] [decimal](18, 2) NOT NULL,
[Detail] [nvarchar](max) NULL,
[OrderPolicy] [int] NOT NULL,
CONSTRAINT [PK_dbo.TempSpotOrders] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[TempSpotOrders] WITH CHECK ADD CONSTRAINT [FK_dbo.TempSpotOrders.Coins_CoinId] FOREIGN KEY([CoinId])
REFERENCES [dbo].[Coins] ([Id])
ON DELETE CASCADE
ALTER TABLE [dbo].[TempSpotOrders] CHECK CONSTRAINT [FK_dbo.TempSpotOrders.Coins_CoinId]
ALTER TABLE [dbo].[TempSpotOrders] WITH CHECK ADD CONSTRAINT [FK_dbo.TempSpotOrders.Traders_TraderId] FOREIGN KEY([TraderId])
REFERENCES [dbo].[Traders] ([Id])
ON DELETE CASCADE
ALTER TABLE [dbo].[TempSpotOrders] CHECK CONSTRAINT [FK_dbo.TempSpotOrders.Traders_TraderId]
end
";
var sql = sql1 + sql2;
context.Database.ExecuteSqlCommand(sql);
}
public void CreateQueryUserOrderDealsStoredProcedure(OptionDbCtx db)
{
String sql = @"if exists (select * from sys.objects where object_id=OBJECT_ID(N'QueryUserOrderDeals') and type='P')
drop procedure QueryUserOrderDeals
execute('create procedure QueryUserOrderDeals
@name nvarchar(100),
@take int,
@skip int
as
begin
select top (@take) *
from(
select row_number() over(order by d.DealTime desc) as Rid,* from(
select
a.MainOrderId as MainOrderId,
b.ReportCount as ReportCount,
b.Direction as Direction,
b.OrderPolicy as OrderPolicy,
b.OrderTime as OrderTime,
b.Count as Count,
b.Price as Price,
b.[State]as [State],
b.OrderType as [OrderType],
a.[When] as DealTime,
a.[Count] as DealCount,
a.Price as DealPrice,
con.Code as Code,
con.Name as Name
from Deals a left join Orders b on a.MainOrderId=b.Id
left join Contracts con on a.ContractId =con.Id
where a.MainName=@name
union
select
a.SlaveOrderId as MainOrderId,
c.ReportCount as ReportCount,
c.Direction as Direction,
c.OrderPolicy as OrderPolicy,
c.OrderTime as OrderTime,
c.Count as Count,
c.Price as Price,
c.[State] as [State],
c.OrderType as [OrderType],
a.[When] as DealTime,
a.[Count] as DealCount,
a.Price as DealPrice,
con.Code as Code,
con.Name as Name
from Deals a
left join Orders c on a.SlaveOrderId =c.Id
left join Contracts con on a.ContractId =con.Id
where a.SlaveName=@name
) d
) e
where e.Rid>@skip
end')";
db.Database.ExecuteSqlCommand(sql);
}