public static bool EditSnapShootForQuery(int queryId, SnapShootModel snapShoot)
{
var extrTimesString = String.Join(",", snapShoot.ExtrTimes);
var selectedDaysString = snapShoot.SelectedDays.Aggregate("", (current, pair) => current + (pair.Key + "-" + pair.Value + ","));
selectedDaysString = selectedDaysString.Remove(selectedDaysString.Length - 1);
var sql = "UPDATE " + TblSnapShots;
sql += " SET `ExtrTimes` = '" + extrTimesString + "',";
sql += " `SelectedDays` = '" + selectedDaysString + "' WHERE `QueryID` = '" + queryId + "';COMMIT;";
if (DoSql(sql))
{
var ssId = GetSnapShootForQuery(queryId).SnapShootId;
var formulaList = GetFormulaForSnapShot(ssId);
foreach (var simpleFormulaModel in formulaList)
{
if (!snapShoot.Formulas.Exists(a => a == simpleFormulaModel))
{
DoSql("DELETE FROM `" + TblFormulas + "` WHERE ID = " + simpleFormulaModel.FormulaId + ";COMMIT;");
DoSql("DELETE FROM `" + TblFormulaSsRelations + "` WHERE FormulaID = " + simpleFormulaModel.FormulaId + ";COMMIT;");
}
else
{
snapShoot.Formulas.Remove(simpleFormulaModel);
}
}
AddFormulaToSnapShot(ssId, snapShoot.Formulas);
return true;
}
return false;
}
public static SnapShootModel GetSnapShootForQuery(int queryId)
{
var snapShoot = new SnapShootModel
{
ExtrTimes = new List<string>(),
SelectedDays = new Dictionary<string, bool>()
};
var sql = "SELECT * FROM " + TblSnapShots + " WHERE `QueryID` = '" + queryId + "';";
var reader = GetReader(sql);
if (reader != null)
{
try
{
while (reader.Read())
{
snapShoot = new SnapShootModel
{
SnapShootId = reader.GetInt32(0),
QueryId = reader.GetInt32(1),
ExtrTimes = new List<string>(reader.GetString(2).Split(',')),
};
if (snapShoot.ExtrTimes.Count == 1 && snapShoot.ExtrTimes[0] == String.Empty)
snapShoot.ExtrTimes.Clear();
var selectedDays = reader.GetString(3).Split(',');
var selectedDaysDictionary =
selectedDays.Select(day => day.Split('-')).ToDictionary(pair => pair[0],
pair => Convert.ToBoolean(pair[1]));
snapShoot.SelectedDays = selectedDaysDictionary;
}
}
finally
{
reader.Close();
}
snapShoot.Formulas = GetFormulaForSnapShot(snapShoot.SnapShootId);
}
return snapShoot;
}
public static bool AddSnapShootToQuery(int queryId, SnapShootModel snapShoot)
{
var extrTimeString = String.Join(",", snapShoot.ExtrTimes);
var selectedDaysString = snapShoot.SelectedDays.Aggregate("", (current, pair) => current + (pair.Key + "-" + pair.Value + ","));
selectedDaysString = selectedDaysString.Remove(selectedDaysString.Length - 1);
var sql = "INSERT IGNORE INTO " + TblSnapShots;
sql += " (`QueryID`, `ExtrTimes`, `SelectedDays`) VALUES ";
sql += "('" + queryId + "',";
sql += " '" + extrTimeString + "',";
sql += " '" + selectedDaysString + "');COMMIT;";
if (DoSql(sql))
{
var ssId = GetSnapShootForQuery(queryId).SnapShootId;
AddFormulaToSnapShot(ssId, snapShoot.Formulas);
return true;
}
return false;
}