public static DataTable GetDataTable(QueryModel queryModel)
{
var sql = CreateSql(queryModel);
_dataTable = CreateDataTable(queryModel.SelectedCols, queryModel.TimeFrame);
var reader = DataExportClientDataManager.GetReader(sql);
if (reader != null)
{
try
{
while (reader.Read())
{
var row = _dataTable.NewRow();
for (int i = 0; i < _dataTable.Columns.Count; i++)
{
row[i] = reader.GetValue(i);
}
_dataTable.Rows.Add(row);
}
}
finally
{
reader.Close();
}
}
if (!queryModel.DateOrDaysBack)
{
return (from rows in _dataTable.AsEnumerable()
where rows.Field<DateTime>("Time").Hour > queryModel.Start.Hour
&& rows.Field<DateTime>("Time").Hour < queryModel.End.Hour
select rows).CopyToDataTable();
}
return _dataTable;
}
public bool AddQuery(QueryModel newQuery)
{
if (!Queries.Exists(query => query.QueryName == newQuery.QueryName))
{
DataExportClientDataManager.AddQueryToProfile(Parameters.ProfileId, newQuery);
Queries = DataExportClientDataManager.GetQueriesForProfile(Parameters.ProfileId);
return true;
}
return false;
}
public static void Initialize(QueryModel query, EDataTable timeSliceTable, EDataTable snapShootTable, DataTable queryData)
{
_timeSliceFormulas = query.TimeSlice.Formulas.ToList();
_snapShootFormulas = query.SnapShoot.Formulas.ToList();
_timeSliceTable = timeSliceTable;
_snapShootTable = snapShootTable;
_queryData = queryData;
}
private static string GetWhereStatement(QueryModel queryModel)
{
if (queryModel.DateOrDaysBack)
{
var startDateStr = new DateTime(queryModel.Start.Year, queryModel.Start.Month, queryModel.Start.Day, 0, 0, 0).ToString("yyyy/MM/dd HH:mm:ss", CultureInfo.InvariantCulture);
var endDateStr = Convert.ToDateTime(queryModel.End).ToString("yyyy/MM/dd HH:mm:ss", CultureInfo.InvariantCulture);
if (queryModel.MostRecent)
{
endDateStr = Convert.ToDateTime(DateTime.Now).ToString("yyyy/MM/dd HH:mm:ss", CultureInfo.InvariantCulture);
}
var where = " WHERE `" + _dateColumnName + "` BETWEEN '" + startDateStr + "' AND '" + endDateStr + "';";
return where;
}
else
{
var startDateStr = new DateTime(DateTime.Now.AddDays(-queryModel.DaysBackCount).Year,
DateTime.Now.AddDays(-queryModel.DaysBackCount).Month,
DateTime.Now.AddDays(-queryModel.DaysBackCount).Day,
0, 0, 0).ToString("yyyy/MM/dd HH:mm:ss", CultureInfo.InvariantCulture);
var endDateStr = Convert.ToDateTime(DateTime.Now).ToString("yyyy/MM/dd HH:mm:ss", CultureInfo.InvariantCulture);
var where = " WHERE `" + _dateColumnName + "` BETWEEN '" + startDateStr + "' AND '" + endDateStr + "';";
return where;
}
}
private static string CreateSql(QueryModel queryModel)
{
_dateColumnName = GetDateTimeColumnName(queryModel.TimeFrame);
var tableName = queryModel.TimeFrame != "Tick" ? GetBarTableName(queryModel.SymbolName, queryModel.TimeFrame) : GetTickTableName(queryModel.SymbolName);
var selectedColumns = GetSelectedCols(queryModel.SelectedCols, queryModel.TimeFrame);
if (!selectedColumns.Contains(_dateColumnName))
selectedColumns += ", `" + _dateColumnName + "`";
if (!selectedColumns.Contains("Trade") && queryModel.TimeFrame == "Tick")
selectedColumns += ", `Trade`";
var sqlQuery = "SELECT " + selectedColumns + " FROM " + tableName;
var whereStatement = GetWhereStatement(queryModel);
sqlQuery += whereStatement;
return sqlQuery;
}
public void SetCurrentQuery(string name)
{
CurrentQuery = Queries.Find(query => query.QueryName == name);
}
public void EditCurrentQuery(QueryModel newQuery)
{
DataExportClientDataManager.EditQuery(CurrentQuery.QueryId, newQuery);
Queries = DataExportClientDataManager.GetQueriesForProfile(Parameters.ProfileId);
SetCurrentQuery(newQuery.QueryName);
}
private void ui_SaveQuery_button_Click(object sender, EventArgs e)
{
if (ProfilesManager.CurrentProfile == null)
{
ToastNotification.Show(ui_QueryOptions_expandPanel, @"Please select a profile", 2000, eToastPosition.TopCenter);
return;
}
if (ui_DaysBack_radioButton.Checked == false && ui_FullDT_radioButton.Checked == false)
{
ToastNotification.Show(ui_QueryOptions_expandPanel, @"Please select time period", 2000, eToastPosition.TopCenter);
return;
}
if (ui_QueryName_textBox.Text == String.Empty)
{
ToastNotification.Show(ui_QueryOptions_expandPanel, @"Please enter the name of query", 2000, eToastPosition.TopCenter);
return;
}
if (ProfilesManager.CurrentProfile.Queries.Exists(query => query.QueryName == ui_QueryName_textBox.Text) && (ProfilesManager.CurrentProfile.CurrentQuery.QueryName != ui_QueryName_textBox.Text))
{
ToastNotification.Show(ui_QueryOptions_expandPanel, @"Query with tis name already exist in current profile", 2000, eToastPosition.TopCenter);
return;
}
if (ui_SelectedColumns_chListBox.Items.Count == 0 || ui_SelectedColumns_chListBox.CheckedItems.Count == 0)
{
ToastNotification.Show(ui_QueryOptions_expandPanel, @"Please select some columns", 2000, eToastPosition.TopCenter);
return;
}
if (ui_BarTables_radioButton.Checked && ui_TimeFrames_comboBox.SelectedIndex < 0)
{
ToastNotification.Show(ui_QueryOptions_expandPanel, @"Please select TimeFrame", 2000, eToastPosition.TopCenter);
return;
}
if (ui_Symbols_comboBox.SelectedIndex < 0)
{
ToastNotification.Show(ui_QueryOptions_expandPanel, @"Please select Symbol", 2000, eToastPosition.TopCenter);
return;
}
try
{
//if(ProfilesManager.SnapShootFormulas == null)
// ProfilesManager.SnapShootFormulas = new List<SimpleFormulaModel>();
var newQuery = new QueryModel
{
ProfileId = ProfilesManager.CurrentProfile.Parameters.ProfileId,
QueryName = ui_QueryName_textBox.Text,
SymbolName = ui_Symbols_comboBox.SelectedItem.ToString(),
TimeFrame = ui_BarTables_radioButton.Checked
? ui_TimeFrames_comboBox.SelectedItem.ToString()
: "Tick",
DateOrDaysBack = ui_FullDT_radioButton.Checked,
MostRecent = ui_MostRecent_checkBox.Checked,
DaysBackCount = ui_DaysBackCount_integerInput.Value,
SelectedCols = new List<string>(),
TimeSlice = new TimeSliceModel
{
ExtractedPeriods = new List<string>(),
SelectedDays = new Dictionary<string, bool>(),
Formulas = new List<SimpleFormulaModel>(ProfilesManager.TimeSliceFormulas)
},
SnapShoot = new SnapShootModel
{
ExtrTimes = new List<string>(),
SelectedDays = new Dictionary<string, bool>(),
Formulas = new List<SimpleFormulaModel>(ProfilesManager.SnapShootFormulas)
}
};
foreach (var item in ui_SelectedColumns_chListBox.CheckedItems)
{
newQuery.SelectedCols.Add(item.ToString());
}
if (newQuery.DateOrDaysBack)
{
newQuery.Start = ui_FullDTStartDate_dTInput.Value;
newQuery.End = ui_FullDTEndDate_dTInput.Value;
}
else
{
newQuery.Start = ui_DaysBStartTime_dTInput.Value;
newQuery.End = ui_DaysBEndTime_dTInput.Value;
}
foreach (var item in ui_TimeSliceExtrPeriodsList_listBox.Items)
{
newQuery.TimeSlice.ExtractedPeriods.Add(item.ToString());
}
for (int i = 0; i < ui_TimeSliceSelDaysList_chListBox.Items.Count; i++)
{
newQuery.TimeSlice.SelectedDays.Add(ui_TimeSliceSelDaysList_chListBox.Items[i].ToString(),
ui_TimeSliceSelDaysList_chListBox.GetItemChecked(i));
}
foreach (var item in ui_SnapShootExtrTimesList_listBox.Items)
{
newQuery.SnapShoot.ExtrTimes.Add(item.ToString());
}
for (int i = 0; i < ui_SnapShootSelDaysList_chListBox.Items.Count; i++)
{
newQuery.SnapShoot.SelectedDays.Add(ui_SnapShootSelDaysList_chListBox.Items[i].ToString(),
ui_SnapShootSelDaysList_chListBox.GetItemChecked(i));
}
ProfilesManager.CurrentProfile.EditCurrentQuery(newQuery);
RefreshQueriesForCurrentProfile();
}
catch (NullReferenceException)
{
ToastNotification.Show(ui_QueryOptions_expandPanel, @"Please check all entered values", 2000, eToastPosition.TopCenter);
}
}