private bool Update_meterreading_1700(String databaseType, DDL ddl, GenConnection con)
{
bool ok = true;
if (databaseType == "SQLite")
{
try
{
GenCommand cmd = new GenCommand("alter table meterreading rename to meterreading_old ", con);
int res = cmd.ExecuteNonQuery();
ok = res >= 0;
}
catch (Exception e)
{
ok = false;
GlobalSettings.LogMessage("VersionManager", "Update_meterreading_1700: exception renaming existing: " + e.Message, LogEntryType.ErrorMessage);
}
if (!ok)
return false;
if (!CreateRelation(((SQLite_DDL)ddl).Table_meterreading_1700, con))
return false;
List<String> columns = new List<String>();
columns.Add("Meter_Id");
columns.Add("ReadingTime");
columns.Add("Appliance");
columns.Add("Duration");
columns.Add("Energy");
columns.Add("Temperature");
columns.Add("Calculated");
columns.Add("MinPower");
columns.Add("MaxPower");
if (!CopyToTable("meterreading_old", "meterreading", columns, con))
return false;
try
{
GenCommand cmd = new GenCommand("drop table meterreading_old ", con);
int res = cmd.ExecuteNonQuery();
return true;
}
catch (Exception e)
{
GlobalSettings.LogMessage("VersionManager", "Update_meterreading_1700: exception dropping old: " + e.Message, LogEntryType.ErrorMessage);
}
}
else
{
if (!AlterRelation(ddl.Alter_meterreading_dropPK_1700, con))
return false;
if (!AlterRelation(ddl.Alter_meterreading_createPK_1700, con))
return false;
return true;
}
return false;
}
private bool Update_pvoutput_v_1836(DDL ddl, GenConnection con)
{
try
{
GenCommand cmd = new GenCommand("drop view pvoutput_v", con);
int res = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
GlobalSettings.LogMessage("VersionManager", "Update_pvoutput_v_1500: exception dropping pvoutput_v: " + e.Message, LogEntryType.ErrorMessage);
}
try
{
GenCommand cmd = new GenCommand("drop view pvoutput5min_v", con);
int res = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
GlobalSettings.LogMessage("VersionManager", "Update_pvoutput_v_1500: exception dropping pvoutput5min_v: " + e.Message, LogEntryType.ErrorMessage);
}
try
{
GenCommand cmd = new GenCommand("drop view pvoutput_sub_v", con);
int res = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
GlobalSettings.LogMessage("VersionManager", "Update_pvoutput_v_1500: exception dropping pvoutput_sub_v: " + e.Message, LogEntryType.ErrorMessage);
}
try
{
if (CreateRelation(ddl.View_pvoutput_sub_v_1836, con))
if (CreateRelation(ddl.View_pvoutput_v_1836, con))
if (CreateRelation(ddl.View_pvoutput5min_v_1836, con))
return true;
}
catch (Exception e)
{
GlobalSettings.LogMessage("VersionManager", "Update_pvoutput_v_1836: exception creating views: " + e.Message, LogEntryType.ErrorMessage);
}
return false;
}
private bool SetMeterInstanceNo(GenConnection con)
{
try
{
GenCommand cmd = new GenCommand("update meter set InstanceNo = 1 ", con);
cmd.ExecuteNonQuery();
return true;
}
catch (Exception e)
{
GlobalSettings.LogMessage("SetMeterInstanceNo", "Error updating meter table - Exception: " + e.Message, LogEntryType.ErrorMessage);
return false;
}
}
private void UpdateVersion(String major, String minor, String release, String patch, GenConnection con)
{
try
{
GenCommand cmd = new GenCommand("delete from version", con);
cmd.ExecuteNonQuery();
}
catch
{
}
String cmdStr;
if (con.DBType == GenDBType.SQLServer)
cmdStr =
"insert into version (Major, Minor, Release, Patch) values (@Major, @Minor, @Release, @Patch)";
else
cmdStr =
"insert into version (Major, Minor, `Release`, Patch) values (@Major, @Minor, @Release, @Patch)";
try
{
GenCommand cmd = new GenCommand(cmdStr, con);
cmd.AddParameterWithValue("@Major", major);
cmd.AddParameterWithValue("@Minor", minor);
cmd.AddParameterWithValue("@Release", release);
cmd.AddParameterWithValue("@Patch", patch);
cmd.ExecuteNonQuery();
Version.major = major;
Version.minor = minor;
Version.release = release;
Version.patch = patch;
}
catch (Exception e)
{
throw new Exception("UpdateVersion: error updating version table: " + e.Message, e);
}
}
private bool CreateRelation(String tableCommand, GenConnection con)
{
try
{
GenCommand cmd = new GenCommand(tableCommand, con);
int res = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
GlobalSettings.LogMessage("CreateRelation", "Error creating relation " + tableCommand + " - error: " + e.Message, LogEntryType.ErrorMessage);
return false;
}
return true;
}
private bool CopyToTable(String fromRelation, String toTable, List<String> columns, GenConnection con)
{
if (columns == null || columns.Count == 0)
{
GlobalSettings.LogMessage("VersionManager", "CopyToTable - Empty column list", LogEntryType.ErrorMessage);
return false;
}
String columnList = "";
foreach (String column in columns)
{
if (columnList == "")
columnList = column;
else
columnList += ", " + column;
}
String fromSelect = "select " + columnList + " from " + fromRelation;
String toInsert = "insert into " + toTable + " ( " + columnList + " ) " + fromSelect;
GenCommand cmd = null;
bool ret = true;
try
{
cmd = new GenCommand(toInsert, con);
int res = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
ret = false;
GlobalSettings.LogMessage("VersionManager", "CopyToTable - Exception: " + e.Message, LogEntryType.ErrorMessage);
}
finally
{
if (cmd != null)
cmd.Dispose();
}
return ret;
}
private void DeleteOldLogEntries()
{
if (GlobalSettings.SystemServices.LogTrace)
LogMessage("DeleteOldLogEntries", "Deleting entries over 14 days old", LogEntryType.Trace);
GenCommand cmdDelLog = null;
GenConnection con = null;
try
{
con = GlobalSettings.TheDB.NewConnection();
cmdDelLog = new GenCommand(CmdDeleteLog, con);
cmdDelLog.AddParameterWithValue("@SiteId", SystemId);
cmdDelLog.AddParameterWithValue("@LimitDay", DateTime.Today.AddDays(-(PVLiveDays + 1)));
cmdDelLog.ExecuteNonQuery();
}
catch (GenException e)
{
throw new Exception("DeleteOldLogEntries - Database Error: " + e.Message, e);
}
catch (Exception e)
{
throw new Exception("DeleteOldLogEntries - Error : " + e.Message, e);
}
finally
{
if (cmdDelLog != null)
cmdDelLog.Dispose();
if (con != null)
{
con.Close();
con.Dispose();
}
}
}
private void UpdatePVOutputLogLoaded(DateTime outputDay, Int32 outputTime)
{
if (GlobalSettings.SystemServices.LogTrace)
LogMessage("UpdatePVOutputLogLoaded", "Updating: " + outputDay + " " + outputTime, LogEntryType.Trace);
GenCommand cmdLoadUpd = null;
GenConnection con = null;
try
{
con = GlobalSettings.TheDB.NewConnection();
cmdLoadUpd = new GenCommand(CmdLoadUpdate, con);
cmdLoadUpd.AddParameterWithValue("@SiteId", SystemId);
cmdLoadUpd.AddParameterWithValue("@OutputDay", outputDay);
cmdLoadUpd.AddParameterWithValue("@OutputTime", outputTime);
int rows = cmdLoadUpd.ExecuteNonQuery();
if (rows != 1)
LogMessage("UpdatePVOutputLogLoaded", "Update rows - expected: 1 - actual: " + rows +
" - Day: " + outputDay + " - Time: " + outputTime, LogEntryType.ErrorMessage);
}
catch (GenException e)
{
throw new Exception("UpdatePVOutputLogLoaded - Database Error: " + e.Message, e);
}
catch (Exception e)
{
throw new Exception("UpdatePVOutputLogLoaded - Error : " + e.Message, e);
}
finally
{
if (cmdLoadUpd != null)
cmdLoadUpd.Dispose();
if (con != null)
{
con.Close();
con.Dispose();
}
}
}
private void PVForceLiveLoad()
{
ObservableCollection<PVOutputDaySettings> list = Settings.PvOutputDayList;
GenCommand cmdFrcLoad = null;
GenConnection con = null;
try
{
con = GlobalSettings.TheDB.NewConnection();
cmdFrcLoad = new GenCommand(CmdForceLoad, con);
cmdFrcLoad.AddParameterWithValue("@SiteId", SystemId);
foreach (PVOutputDaySettings day in list)
{
if (day.ForceLoad)
{
DateTime? date = day.Day;
if (GlobalSettings.SystemServices.LogTrace)
LogMessage("PVForceLiveLoad", "Updating: " + date, LogEntryType.Information);
try
{
if (cmdFrcLoad.Parameters.Count < 2)
cmdFrcLoad.AddParameterWithValue("@OutputDay", date.Value);
else
cmdFrcLoad.Parameters["@OutputDay"].Value = date.Value;
cmdFrcLoad.ExecuteNonQuery();
}
catch (GenException e)
{
throw new Exception("PVForceLiveLoad - Database exception: " + e.Message, e);
}
catch (Exception e)
{
throw new Exception("PVForceLiveLoad: " + e.Message, e);
}
}
}
}
catch (Exception e)
{
throw e;
}
finally
{
if (cmdFrcLoad != null)
cmdFrcLoad.Dispose();
if (con != null)
{
con.Close();
con.Dispose();
}
}
}