public void editCycle(StockCycleInfo info)
{
if (info == null || !connect())
return;
try
{
begin();
String query;
NpgsqlCommand command;
query = "UPDATE STOCK_CYCLE SET START_TIME = :s, BORDER1_TIME = :b2, BORDER2_TIME = :b2, FINISH_TIME = :f WHERE ID = :cid";
command = new NpgsqlCommand(query, connection);
command.Parameters.Add(new NpgsqlParameter("s", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters["s"].Value = info.start;
command.Parameters.Add(new NpgsqlParameter("b1", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters["b1"].Value = info.border1;
command.Parameters.Add(new NpgsqlParameter("b2", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters["b2"].Value = info.border2;
command.Parameters.Add(new NpgsqlParameter("f", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters["f"].Value = info.finish;
command.Parameters.Add(new NpgsqlParameter("cid", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters["cid"].Value = info.id;
command.ExecuteNonQuery();
foreach (DataRow row in info.quotes.Rows)
{
String ticker = Convert.ToString(row["TICKER"]);
UInt64 quote = Convert.ToUInt64(row["QUOTE"]);
UInt64 tl = Convert.ToUInt64(row["TRADE_LIMIT"]);
UInt64 npc = Convert.ToUInt64(row["NPCS_BUY"]);
query = "SELECT PRICE FROM STOCK_QUOTE WHERE CYCLE_ID = :cid AND COMPANY_KEY = :ticker";
command = new NpgsqlCommand(query, connection);
command.Parameters.Add(new NpgsqlParameter("cid", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters["cid"].Value = info.id;
command.Parameters.Add(new NpgsqlParameter("ticker", NpgsqlTypes.NpgsqlDbType.Varchar));
command.Parameters["ticker"].Value = ticker;
NpgsqlDataReader rd = command.ExecuteReader();
bool isNew = !rd.HasRows;
rd.Close();
if (isNew)
{
query = "INSERT INTO STOCK_QUOTE (CYCLE_ID, COMPANY_KEY, PRICE, TRADE_LIMIT, NPCS_BUY) VALUES (:cid, :ticker, :quote, :tl, :npc)";
command = new NpgsqlCommand(query, connection);
command.Parameters.Add(new NpgsqlParameter("cid", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters["cid"].Value = info.id;
command.Parameters.Add(new NpgsqlParameter("ticker", NpgsqlTypes.NpgsqlDbType.Varchar));
command.Parameters["ticker"].Value = ticker;
command.Parameters.Add(new NpgsqlParameter("quote", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters["quote"].Value = quote;
command.Parameters.Add(new NpgsqlParameter("tl", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters["tl"].Value = tl;
command.Parameters.Add(new NpgsqlParameter("npc", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters["npc"].Value = npc;
command.ExecuteNonQuery();
}
else
{
query = "UPDATE STOCK_QUOTE SET PRICE = :quote, TRADE_LIMIT = :tl, NPCS_BUY = :npc WHERE CYCLE_ID = :cid AND COMPANY_KEY = :ticker";
command = new NpgsqlCommand(query, connection);
command.Parameters.Add(new NpgsqlParameter("cid", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters["cid"].Value = info.id;
command.Parameters.Add(new NpgsqlParameter("ticker", NpgsqlTypes.NpgsqlDbType.Varchar));
command.Parameters["ticker"].Value = ticker;
command.Parameters.Add(new NpgsqlParameter("quote", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters["quote"].Value = quote;
command.Parameters.Add(new NpgsqlParameter("tl", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters["tl"].Value = tl;
command.Parameters.Add(new NpgsqlParameter("npc", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters["npc"].Value = npc;
command.ExecuteNonQuery();
}
}
commit();
}
catch (Exception ex)
{
HandleException(ex);
}
finally
{
disconnect();
}
}
public UInt64 newCycle(StockCycleInfo info)
{
if (info == null || !connect())
return 0;
UInt64 cycleId = 0;
try
{
begin();
String query;
NpgsqlCommand command;
query = "INSERT INTO STOCK_CYCLE (START_TIME, BORDER1_TIME, BORDER2_TIME, FINISH_TIME) VALUES (:s, :b1, :b2, :f) RETURNING ID";
command = new NpgsqlCommand(query, connection);
command.Parameters.Add(new NpgsqlParameter("s", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters["s"].Value = info.start;
command.Parameters.Add(new NpgsqlParameter("b1", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters["b1"].Value = info.border1;
command.Parameters.Add(new NpgsqlParameter("b2", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters["b2"].Value = info.border2;
command.Parameters.Add(new NpgsqlParameter("f", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters["f"].Value = info.finish;
NpgsqlDataReader rd = command.ExecuteReader();
while (rd.Read())
{
cycleId = Convert.ToUInt64(rd[0]);
break;
}
rd.Close();
query = "INSERT INTO STOCK_QUOTE (CYCLE_ID, COMPANY_KEY, PRICE, TRADE_LIMIT, NPCS_BUY) VALUES (:cid, :ticker, :quote, :tl, :npc)";
foreach (DataRow row in info.quotes.Rows)
{
String ticker = Convert.ToString(row["TICKER"]);
UInt64 quote = Convert.ToUInt64(row["QUOTE"]);
UInt64 tl = Convert.ToUInt64(row["TRADE_LIMIT"]);
UInt64 npc = Convert.ToUInt64(row["NPCS_BUY"]);
command = new NpgsqlCommand(query, connection);
command.Parameters.Add(new NpgsqlParameter("cid", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters["cid"].Value = cycleId;
command.Parameters.Add(new NpgsqlParameter("ticker", NpgsqlTypes.NpgsqlDbType.Varchar));
command.Parameters["ticker"].Value = ticker;
command.Parameters.Add(new NpgsqlParameter("quote", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters["quote"].Value = quote;
command.Parameters.Add(new NpgsqlParameter("tl", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters["tl"].Value = tl;
command.Parameters.Add(new NpgsqlParameter("npc", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters["npc"].Value = npc;
command.ExecuteNonQuery();
}
// Moving all "waiting" requests to this very cycle
query = "UPDATE STOCK_REQUEST SET CYCLE_ID = :cid, STATUS = 'A' WHERE STATUS = 'W'";
command = new NpgsqlCommand(query, connection);
command.Parameters.Add(new NpgsqlParameter("cid", NpgsqlTypes.NpgsqlDbType.Numeric));
command.Parameters["cid"].Value = cycleId;
command.ExecuteNonQuery();
commit();
}
catch (Exception ex)
{
cycleId = 0;
HandleException(ex);
}
finally
{
disconnect();
}
return cycleId;
}