public static bool AddTimeSliceToQuery(int queryId, TimeSliceModel timeSlice) { var extrPeriodsString = String.Join(",", timeSlice.ExtractedPeriods); var selectedDaysString = timeSlice.SelectedDays.Aggregate("", (current, pair) => current + (pair.Key + "-" + pair.Value + ",")); selectedDaysString = selectedDaysString.Remove(selectedDaysString.Length - 1); var sql = "INSERT IGNORE INTO " + TblTimeSlices; sql += " (`QueryID`, `ExtrPeriods`, `SelectedDays`) VALUES "; sql += "('" + queryId + "',"; sql += " '" + extrPeriodsString + "',"; sql += " '" + selectedDaysString + "');COMMIT;"; if (DoSql(sql)) { var tsId = GetTimeSliceForQuery(queryId).TimeSliceId; AddFormulaToTimeSlice(tsId, timeSlice.Formulas); return true; } return false; }
public static bool EditTimeSliceForQuery(int queryId, TimeSliceModel timeSlice) { var extrPeriodsString = String.Join(",", timeSlice.ExtractedPeriods); var selectedDaysString = timeSlice.SelectedDays.Aggregate("", (current, pair) => current + (pair.Key + "-" + pair.Value + ",")); selectedDaysString = selectedDaysString.Remove(selectedDaysString.Length - 1); var sql = "UPDATE " + TblTimeSlices; sql += " SET `ExtrPeriods` = '" + extrPeriodsString + "',"; sql += " `SelectedDays` = '" + selectedDaysString + "' WHERE `QueryID` = '" + queryId + "';COMMIT;"; if (DoSql(sql)) { var tsId = GetTimeSliceForQuery(queryId).TimeSliceId; var formulaList = GetFormulaForTimeSlice(tsId); foreach (var simpleFormulaModel in formulaList) { if (!timeSlice.Formulas.Exists(a => a == simpleFormulaModel)) { DoSql("DELETE FROM `" + TblFormulas + "` WHERE ID = " + simpleFormulaModel.FormulaId + ";COMMIT;"); DoSql("DELETE FROM `" + TblFormulaTsRelations + "` WHERE FormulaID = " + simpleFormulaModel.FormulaId + ";COMMIT;"); } else { timeSlice.Formulas.Remove(simpleFormulaModel); } } AddFormulaToTimeSlice(tsId, timeSlice.Formulas); return true; } return false; }
public static TimeSliceModel GetTimeSliceForQuery(int queryId) { var timeSlice = new TimeSliceModel { ExtractedPeriods = new List<string>(), SelectedDays = new Dictionary<string, bool>() }; var sql = "SELECT * FROM " + TblTimeSlices + " WHERE `QueryID` = '" + queryId + "';"; var reader = GetReader(sql); if (reader != null) { try { while (reader.Read()) { timeSlice = new TimeSliceModel { TimeSliceId = reader.GetInt32(0), QueryId = reader.GetInt32(1), ExtractedPeriods = new List<string>(reader.GetString(2).Split(',')), }; if (timeSlice.ExtractedPeriods.Count == 1 && timeSlice.ExtractedPeriods[0] == String.Empty) timeSlice.ExtractedPeriods.Clear(); var selectedDays = reader.GetString(3).Split(','); var selectedDaysDictionary = selectedDays.Select(day => day.Split('-')).ToDictionary(pair => pair[0], pair => Convert.ToBoolean(pair[1])); timeSlice.SelectedDays = selectedDaysDictionary; } } finally { reader.Close(); } timeSlice.Formulas = GetFormulaForTimeSlice(timeSlice.TimeSliceId); } return timeSlice; }