public override void SQLSave()
{
string sqlIns = "INSERT INTO trade_buckets (Time, High, Low, Volume, open, close, number_of_trades, bucket_group, Created_at, updated_at) VALUES (@Time, @High, @Low, @Volume, @Open, @Close, @NumTrades, @GroupBy, @Created_at,@Created_at)";
try
{
if (bool.Parse(ConfigurationManager.AppSettings["enableRDS"]))
{
using (MySqlConnection conn = new MySqlConnection(connectionStringRDS))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = sqlIns;
cmd.Prepare();
cmd.Parameters.AddWithValue("@Time", TimeUtil.FromUnixTime((long)Time));
cmd.Parameters.AddWithValue("@High", High);
cmd.Parameters.AddWithValue("@Low", Low);
cmd.Parameters.AddWithValue("@Volume", Volume);
cmd.Parameters.AddWithValue("@Open", Open);
cmd.Parameters.AddWithValue("@Close", Close);
cmd.Parameters.AddWithValue("@NumTrades", NumTrades);
cmd.Parameters.AddWithValue("@Created_at", DateTime.Now);
cmd.Parameters.AddWithValue("@GroupBy", Group.ToString());
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
}
using (MySqlConnection conn = new MySqlConnection(connectionStringLocal))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = sqlIns;
cmd.Prepare();
cmd.Parameters.AddWithValue("@Time", TimeUtil.FromUnixTime((long)Time));
cmd.Parameters.AddWithValue("@High", High);
cmd.Parameters.AddWithValue("@Low", Low);
cmd.Parameters.AddWithValue("@Volume", Volume);
cmd.Parameters.AddWithValue("@Open", Open);
cmd.Parameters.AddWithValue("@Close", Close);
cmd.Parameters.AddWithValue("@NumTrades", NumTrades);
cmd.Parameters.AddWithValue("@Created_at", DateTime.Now);
cmd.Parameters.AddWithValue("@GroupBy", Group.ToString());
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
}
catch (Exception ex)
{
throw new Exception(ex.ToString(), ex);
}
}
public XDocument ExportXml()
{
var icon = Icon?.Entity;
return(new XDocument(new XDeclaration("1.0", "utf-8", "yes"),
new XElement("ChartScript",
new XAttribute("GroupBy", GroupBy.ToString()),
new XElement("Columns",
Columns.Select(c => new XElement("Column",
new XAttribute("DisplayName", c.DisplayName),
new XAttribute("ColumnType", c.ColumnType.ToString()),
c.IsGroupKey ? new XAttribute("IsGroupKey", true) : null,
c.IsOptional ? new XAttribute("IsOptional", true) : null
))),
new XElement("Parameters",
Parameters.Select(p => new XElement("Parameter",
new XAttribute("Name", p.Name),
new XAttribute("Type", p.Type),
new XAttribute("ValueDefinition", p.ValueDefinition),
p.ColumnIndex == null ? null : new XAttribute("ColumnIndex", p.ColumnIndex))
)),
icon == null ? null :
new XElement("Icon",
new XAttribute("FileName", icon.FileName),
new XCData(Convert.ToBase64String(Icon.Entity.BinaryFile))),
new XElement("Script", new XCData(Script)))));
}
public sealed override StringBuilder ToString(StringBuilder sb, Dictionary <IQueryElement, IQueryElement> dic)
{
if (dic.ContainsKey(this))
{
return(sb.Append("..."));
}
dic.Add(this, this);
sb
.Append("(")
.Append(SourceID)
.Append(") ");
Select.ToString(sb, dic);
From.ToString(sb, dic);
Where.ToString(sb, dic);
GroupBy.ToString(sb, dic);
Having.ToString(sb, dic);
OrderBy.ToString(sb, dic);
if (HasUnion)
{
foreach (var u in Unions)
{
u.ToString(sb, dic);
}
}
dic.Remove(this);
return(sb);
}
public static GroupBy GroupBy(this From from, params string[] groupNames)
{
GroupBy groupby = new GroupBy(groupNames);
groupby.SqlString = from.SqlString + groupby.ToString();
return(groupby);
}
public static GroupBy GroupBy(this Where where, params string[] columnNames)
{
GroupBy groupby = new GroupBy(columnNames);
groupby.SqlString = where.SqlString + groupby.ToString();
return(groupby);
}
public static string GetStringValue(this GroupBy enumValue)
{
switch (enumValue)
{
case GroupBy.Nodes: return("nodes");
case GroupBy.Parents: return("parents");
}
throw new ArgumentException($"'{enumValue.ToString()}' is not a valid value for enum 'GroupBy'");
}
public void ToStringTest()
{
GroupBy groupBy = new GroupBy();
groupBy.By = ElementAttributeType.Access;
string str = groupBy.ToString();
Assert.AreEqual("Group by: Access", str, "Unexpected string representation.");
}
public static string GetLocalisedName(this GroupBy groupBy)
{
switch (groupBy)
{
case GroupBy.Genre:
return(AppResources.Genre);
case GroupBy.Name:
return(AppResources.NameLabel);
case GroupBy.ProductionYear:
return(AppResources.ProductionYear);
}
return(groupBy.ToString());
}
public override string ToString()
{
return
($"{Select?.ToString()} {From?.ToString()} {Where?.ToString()} {GroupBy?.ToString()} {OrderBy?.ToString()} {Skip?.ToString()} {Take?.ToString()}");
}
/// <summary>
/// Get a Join on TWO tables.
/// </summary>
/// <param name="tables"></param>
/// <param name="c"></param>
/// <param name="g"></param>
/// <param name="o"></param>
/// <returns></returns>
public Result Join(Joinable[] tables, Condition[] c, GroupBy g, OrderBy[] o)
{
string sql = "SELECT ";
List<string> fields = new List<string>();
foreach (Joinable j in tables)
{
sql += String.Join(",", j.GetFields(true)) + ", ";
foreach (string item in j.GetFields(true))
{
fields.Add(item);
}
}
sql = sql.Remove(sql.Length - 2);
sql += " FROM ";
foreach (Joinable j in tables)
{
sql += j.GetTableName() + ", ";
}
sql = sql.Remove(sql.Length - 2);
sql += " WHERE (";
sql += tables[0].GetJoinOn(tables[1]) + "=" + tables[1].GetJoinOn(tables[0]); //TODO: make this work for more tables...
sql += ") ";
if (c != null)
{
sql += " AND (";
foreach (Condition item in c)
{
sql += item.ToString() + " AND ";
}
sql = sql.Remove(sql.Length - 5);
sql += ") ";
}
if (g != null)
{
sql += g.ToString();
}
if (o != null)
{
sql += " ORDER BY ";
foreach (OrderBy item in o)
{
sql += item.ToShortString() + ", ";
}
sql = sql.Remove(sql.Length - 2);
}
this.cmd.CommandText = sql;
Result r = new Result(this.cmd.CommandText);
if (this.connection.State != System.Data.ConnectionState.Open) this.connection.Open();
SQLiteDataReader re = this.cmd.ExecuteReader();
while (re.Read())
{
Row row = new Row(re.StepCount);
foreach (string f in fields)
{
row.AddCell(f, re[f].ToString());
}
r.AddRow(row);
}
re.Close();
return r;
}
public Result getRow(Joinable j, string[] fields, Condition[] c = null, GroupBy g = null, OrderBy[] o = null, int limit = 0)
{
this.cmd.CommandText = "SELECT " + String.Join(",", fields) + " FROM " + j.GetTableName();
if (c != null)
{
this.cmd.CommandText += " WHERE (";
foreach (Condition item in c)
{
this.cmd.CommandText += item.ToString() + " AND ";
}
this.cmd.CommandText = this.cmd.CommandText.Remove(this.cmd.CommandText.Length - 5);
this.cmd.CommandText += ") ";
}
if (g != null)
{
this.cmd.CommandText += g.ToString();
}
if (o != null)
{
this.cmd.CommandText += " ORDER BY ";
foreach (OrderBy item in o)
{
this.cmd.CommandText += item.ToShortString() + ", ";
}
this.cmd.CommandText = this.cmd.CommandText.Remove(this.cmd.CommandText.Length - 2);
}
if (limit != 0)
{
this.cmd.CommandText += " LIMIT " + limit.ToString();
}
Result r = new Result(this.cmd.CommandText);
if (this.connection.State != System.Data.ConnectionState.Open) this.connection.Open();
SQLiteDataReader re = this.cmd.ExecuteReader();
while (re.Read())
{
Row row = new Row(re.StepCount);
foreach (string f in fields)
{
row.AddCell(f, re[f].ToString());
}
r.AddRow(row);
}
re.Close();
return r;
}
public string ToString(bool addExecutionTime)
{
StringBuilder sb = new StringBuilder();
string post = null;
sb.Append(Columns.ToString(ref post));
string str, where = Where.ToString();
if (!string.IsNullOrEmpty(where))
{
sb.Append(" ");
sb.Append(where);
}
str = OrderBy.ToString();
if (!string.IsNullOrEmpty(str))
{
sb.Append(str);
}
str = GroupBy.ToString();
if (!string.IsNullOrEmpty(str))
{
sb.Append(str);
}
if (Settings.LimitType == LimitType.Fetch &&
(Index != 0 || Count != 0))
{
sb.Append(" OFFSET ");
sb.Append(Index);
sb.Append(" ROWS");
sb.Append(" FETCH NEXT ");
sb.Append(Count);
sb.Append(" ROWS ONLY");
}
str = Having.ToString();
if (!string.IsNullOrEmpty(str))
{
sb.Append(str);
}
if (Settings.Type == DatabaseType.Oracle)
{
str = Where.LimitToString();
if (!string.IsNullOrEmpty(str))
{
string tmp = sb.ToString();
str = string.Format(str, tmp);
sb.Length = 0;
sb.Append(str);
}
}
if (Settings.Type != DatabaseType.Oracle)
{
str = Where.LimitToString();
if (!string.IsNullOrEmpty(str))
{
sb.Append(" ");
sb.Append(str);
}
}
if (post != null)
{
sb.Append(post);
}
query = sb.ToString();
if (addExecutionTime && ExecutionTime != 0)
{
sb.Clear();
sb.Append("Execution time: ");
sb.Append(ExecutionTime);
sb.Append(" ms. ");
sb.Append(Environment.NewLine);
sb.Append(query);
}
return(query);
}
public static double GetLastKnownTime(string algorithmName, GroupBy group)
{
DateTime last = DateTime.MinValue;
string query = string.Format(@"SELECT Time FROM advices WHERE algorithm_name = '{1}' AND bucket_group = '{0}' Order By Time Desc limit 1", group.ToString(), algorithmName);
DataTable dt = RunSELECT(query);
if (dt.Rows.Count > 0)
{
last = (DateTime)dt.Rows[0][0];
}
if (last == DateTime.MinValue)
{
return(0);
}
return((double)TimeUtil.ToUnixTime(last));
}
public static List <Advice> GetAllAdvicesInRange(string algorithmName, GroupBy group, double start, double end)
{
List <Advice> advices = new List <Advice>();
string query = string.Format(@"SELECT Time, Confidence, Price, Action, algorithm_name, bucket_group, Created_at, id FROM advices WHERE Time >= '{0}' AND Time <= '{3}' AND algorithm_name = '{1}' AND bucket_group = '{2}' Order By Time Asc", TimeUtil.FromUnixTime((long)start).ToString("yyyy-MM-dd HH:mm:ss"), algorithmName, group.ToString(), TimeUtil.FromUnixTime((long)end).ToString("yyyy-MM-dd HH:mm:ss"));
DataTable dt = RunSELECT(query);
foreach (DataRow dr in dt.Rows)
{
Advice advice = new Advice();
advice.Time = (int)TimeUtil.ToUnixTime((DateTime)dr[0]);
advice.Confidence = (decimal)dr[1];
advice.Price = (decimal)dr[2];
advice.Action = ActionUtil.GetTradeActionBy((string)dr[3]);
advice.AlgorithmName = ((string)dr[4]).Trim();
advice.Group = GroupUtil.GetGroupBy((string)dr[5]);
advice.Id = (int)dr[6];
advices.Add(advice);
}
return(advices);
}
public static List <TradeBucket> GetLastBuckets(GroupBy group, double time, int buckets)
{
DateTime last = DateTime.MinValue;
DateTime endTime = TimeUtil.FromUnixTime((long)time);
string query = string.Format(@"SELECT Time, High, Low, Volume, open, close, number_of_trades, bucket_group, ID FROM trade_buckets WHERE bucket_group = '{0}' AND TIME <= '{1}' Order By Time desc limit {2}", group.ToString(), endTime.ToString("yyyy-MM-dd HH:mm:ss"), buckets);
DataTable dt = RunSELECT(query);
List <TradeBucket> results = new List <TradeBucket>();
//need to reverse order
for (int i = dt.Rows.Count - 1; i >= 0; i--)
{
TradeBucket tradeBucket = new TradeBucket();
tradeBucket.Time = (int)TimeUtil.ToUnixTime((DateTime)dt.Rows[i][0]);
tradeBucket.High = (decimal)dt.Rows[i][1];
tradeBucket.Low = (decimal)dt.Rows[i][2];
tradeBucket.Volume = (decimal)dt.Rows[i][3];
tradeBucket.Open = (decimal)dt.Rows[i][4];
tradeBucket.Close = (decimal)dt.Rows[i][5];
tradeBucket.NumTrades = (int)dt.Rows[i][6];
tradeBucket.Group = GroupUtil.GetGroupBy((string)dt.Rows[i][7]);
tradeBucket.Id = (int)dt.Rows[i][8];
results.Add(tradeBucket);
}
return(results);
}
public static List <TradeBucket> GetAllBuckets(GroupBy group)
{
string query = string.Format(@"SELECT Time, High, Low, Volume, open, close, number_of_trades, bucket_group, ID FROM trade_buckets WHERE bucket_group = '{0}' Order By Time desc", group.ToString());
DataTable dt = RunSELECT(query);
List <TradeBucket> results = new List <TradeBucket>();
//need to reverse order
for (int i = dt.Rows.Count - 1; i >= 0; i--)
{
TradeBucket tradeBucket = new TradeBucket();
tradeBucket.Time = (int)TimeUtil.ToUnixTime((DateTime)dt.Rows[i][0]);
tradeBucket.High = (decimal)dt.Rows[i][1];
tradeBucket.Low = (decimal)dt.Rows[i][2];
tradeBucket.Volume = (decimal)dt.Rows[i][3];
tradeBucket.Open = (decimal)dt.Rows[i][4];
tradeBucket.Close = (decimal)dt.Rows[i][5];
tradeBucket.NumTrades = (int)dt.Rows[i][6];
tradeBucket.Group = GroupUtil.GetGroupBy((string)dt.Rows[i][7]);
tradeBucket.Id = (int)dt.Rows[i][8];
results.Add(tradeBucket);
}
return(results);
}