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 static List<QueryModel> GetQueriesForProfile(int profileId) { var queryList = new List<QueryModel>(); string sql = "SELECT * FROM " + TblQueries + " WHERE `ProfileID` = '" + profileId + "';"; var reader = GetReader(sql); if (reader != null) { try { while (reader.Read()) { var query = new QueryModel { QueryId = reader.GetInt32(0), ProfileId = reader.GetInt32(1), QueryName = reader.GetString(2), SymbolName = reader.GetString(3), TimeFrame = reader.GetString(4), SelectedCols = new List<string>(reader.GetString(5).Split(',')), DateOrDaysBack = reader.GetBoolean(6), Start = reader.GetDateTime(7), End = reader.GetDateTime(8), MostRecent = reader.GetBoolean(9), DaysBackCount = reader.GetInt32(10), TimeSlice = new TimeSliceModel(), SnapShoot = new SnapShootModel() }; queryList.Add(query); } } finally { reader.Close(); } } for (int i = 0; i < queryList.Count; i++ ) { var ass = queryList[i]; ass.TimeSlice = GetTimeSliceForQuery(queryList[i].QueryId); ass.SnapShoot = GetSnapShootForQuery(queryList[i].QueryId); queryList[i] = ass; } return queryList; }
public static bool EditQuery(int queryId, QueryModel query) { var startDateStr = Convert.ToDateTime(query.Start).ToString("yyyy/MM/dd HH:mm:ss", CultureInfo.InvariantCulture); var endDateStr = Convert.ToDateTime(query.End).ToString("yyyy/MM/dd HH:mm:ss", CultureInfo.InvariantCulture); var selectedColsString = String.Join(",", query.SelectedCols); var sql = "UPDATE " + TblQueries; sql += " SET `QueryName` = '" + query.QueryName + "',"; sql += " `SymbolName` = '" + query.SymbolName + "',"; sql += " `TimeFrame` = '" + query.TimeFrame + "',"; sql += " `SelectedCols` = '" + selectedColsString + "',"; sql += " `DateORDaysBack` = " + query.DateOrDaysBack + ","; sql += " `StartDate` = '" + startDateStr + "',"; sql += " `EndDate` = '" + endDateStr + "',"; sql += " `MostRecent` = " + query.MostRecent + ","; sql += " `DaysCount` = '" + query.DaysBackCount + "' WHERE `ID` = '" + queryId + "';COMMIT;"; if (DoSql(sql)) { return EditTimeSliceForQuery(queryId, query.TimeSlice) && EditSnapShootForQuery(queryId, query.SnapShoot); } return false; }
public static bool AddQueryToProfile(int profileId, QueryModel query) { var startDateStr = Convert.ToDateTime(query.Start).ToString("yyyy/MM/dd HH:mm:ss", CultureInfo.InvariantCulture); var endDateStr = Convert.ToDateTime(query.End).ToString("yyyy/MM/dd HH:mm:ss", CultureInfo.InvariantCulture); var selectedColsString = String.Join(",", query.SelectedCols); var sql = "INSERT IGNORE INTO " + TblQueries; sql += " (`ProfileID`, `QueryName`, `SymbolName`, `TimeFrame`, `SelectedCols`,"; sql += " `DateORDaysBack`, `StartDate`, `EndDate`, `MostRecent`, `DaysCount`) VALUES "; sql += "('" + profileId + "',"; sql += " '" + query.QueryName + "',"; sql += " '" + query.SymbolName + "',"; sql += " '" + query.TimeFrame + "',"; sql += " '" + selectedColsString + "',"; sql += " " + query.DateOrDaysBack + ","; sql += " '" + startDateStr + "',"; sql += " '" + endDateStr + "',"; sql += " " + query.MostRecent + ","; sql += " '" + query.DaysBackCount + "');COMMIT;"; if (DoSql(sql)) { var queries = GetQueriesForProfile(profileId); var currentQueryId = queries.Find(a => a.QueryName == query.QueryName).QueryId; return AddTimeSliceToQuery(currentQueryId, query.TimeSlice) && AddSnapShootToQuery(currentQueryId, query.SnapShoot); } return false; }
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); } }