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;
}