public List<InverterInfo> GetInverterList(String managerType, int instanceNo)
{
List<InverterInfo> InverterList = new List<InverterInfo>();
GenDatabase db = null;
GenConnection con = null;
GenCommand cmd = null;
GenDataReader dataReader = null;
try
{
db = GetDatabase();
con = db.NewConnection();
String getInverters =
"select i.Id, i.SerialNumber, i.SiteId, it.Manufacturer, it.Model, im.ManagerType " +
"from inverter i, invertertype it, invertermanager im " +
"where im.ManagerType = @ManagerType " +
"and im.InstanceNo = @InstanceNo " +
"and i.InverterManager_Id = im.Id " +
"and i.InverterType_Id = it.Id " +
"order by SerialNumber ";
cmd = new GenCommand(getInverters, con);
cmd.AddParameterWithValue("@ManagerType", managerType);
cmd.AddParameterWithValue("@InstanceNo", instanceNo);
dataReader = (GenDataReader)cmd.ExecuteReader();
while (dataReader.Read())
{
InverterInfo info = new InverterInfo();
info.Id = dataReader.GetInt32(0);
info.SerialNumber = dataReader.GetString(1);
info.Manufacturer = dataReader.GetString(3);
info.Model = dataReader.GetString(4);
InverterList.Add(info);
}
}
catch (Exception)
{
}
finally
{
if (dataReader != null)
{
dataReader.Close();
dataReader.Dispose();
}
if (cmd != null)
cmd.Dispose();
if (con != null)
{
con.Close();
con.Dispose();
}
}
return InverterList;
}
public void LoadDeviceList()
{
DeviceList.Clear();
GenDatabase db = null;
GenConnection con = null;
GenCommand cmd = null;
GenDataReader dataReader = null;
try
{
db = GetDatabase();
con = db.NewConnection();
String getDevices =
"select i.Id, i.SerialNumber, itp.DeviceType, itp.Manufacturer, itp.Model, ift.FeatureType, ift.FeatureId, ift.MeasureType " +
"from device i, devicetype itp, devicefeature ift " +
"where i.DeviceType_Id = itp.Id and i.Id = ift.Device_Id " +
"order by itp.DeviceType, i.SerialNumber ";
cmd = new GenCommand(getDevices, con);
dataReader = (GenDataReader)cmd.ExecuteReader();
while (dataReader.Read())
{
DeviceDisplayInfo info = new DeviceDisplayInfo(Settings);
info.Id = dataReader.GetInt32(0);
info.SerialNumber = dataReader.GetString(1);
info.DeviceType = dataReader.GetString(2);
info.Manufacturer = dataReader.GetString(3);
info.Model = dataReader.GetString(4);
info.FeatureType = ((FeatureType)dataReader.GetInt16(5)).ToString();
info.FeatureId = dataReader.GetInt16(6);
info.Updated = false;
DeviceList.Add(info);
}
}
catch (Exception)
{
}
finally
{
if (dataReader != null)
{
dataReader.Close();
dataReader.Dispose();
}
if (cmd != null)
cmd.Dispose();
if (con != null)
{
con.Close();
con.Dispose();
}
}
}
public String RunDatabaseTest(ref String outStage, ref Exception outException)
{
String stage = "Initialise";
GenDatabase db = null;
GenConnection con = null;
GenCommand cmd = null;
GenDataReader dataReader = null;
try
{
stage = "Get Database from settings";
db = GetDatabase();
stage = "Get Database connection";
con = db.NewConnection();
VersionManager vm = new VersionManager();
vm.PopulateDatabaseIfEmpty(con);
//con.GetSchemaTable("Fred");
String cmd1 = "select count(*) from pvoutputlog ";
stage = "Creating select command";
cmd = new GenCommand(cmd1, con);
stage = "Executing data reader";
dataReader = (GenDataReader)cmd.ExecuteReader();
stage = "Calling DataReader.Read()";
bool res = dataReader.Read();
}
catch (Exception e)
{
outStage = stage;
outException = e;
return "Database Test - Stage: " + stage + " - Exception: " + e.Message;
}
finally
{
if (dataReader != null)
{
dataReader.Close();
dataReader.Dispose();
}
if (cmd != null)
cmd.Dispose();
if (con != null)
{
con.Close();
con.Dispose();
}
}
outStage = "Complete";
outException = null;
return "Success";
}
private bool RelationExists(GenConnection con, string relationName)
{
GenCommand cmd = null;
try
{
cmd = new GenCommand("Select * from " + relationName + " where 0 = 1 ", con);
GenDataReader dr;
dr = (GenDataReader)cmd.ExecuteReader();
dr.Close();
return true;
}
catch (Exception e)
{
if (GlobalSettings.SystemServices != null) // true when service is running
GlobalSettings.LogMessage("RelationExists", "Exception: " + e.Message);
return false;
}
}
private ObservableCollection<ApplianceInfo> GetApplianceList()
{
ObservableCollection<ApplianceInfo> list = new ObservableCollection<ApplianceInfo>();
GenDatabase olwDb = GetDatabase();
if (OwlDb != null)
{
GenConnection con = null;
GenCommand cmd = null;
GenDataReader reader = null;
String selCmd =
"select addr, name, model " +
"from energy_sensor " +
"where addr is not null " +
"order by name ";
try
{
con = OwlDb.NewConnection();
cmd = new GenCommand(selCmd, con);
reader = (GenDataReader)cmd.ExecuteReader();
while (reader.Read())
{
ApplianceInfo info = new ApplianceInfo();
info.ApplianceNo = reader.GetInt32(0);
string name = reader.IsDBNull(1) ? "" : reader.GetString(1);
string model = reader.IsDBNull(2) ? "" : reader.GetInt32(2).ToString();
if (name == "")
if (model == "")
info.Description = info.ApplianceNo.ToString();
else
info.Description = model + ": " + info.ApplianceNo.ToString();
else if (model == "")
info.Description = name + ": " + info.ApplianceNo.ToString();
else
info.Description = name + " / " + model + ": " + info.ApplianceNo.ToString();
list.Add(info);
}
}
catch (Exception)
{
}
finally
{
if (con != null)
{
con.Close();
con.Dispose();
}
if (cmd != null)
cmd.Dispose();
if (reader != null)
{
reader.Close();
reader.Dispose();
}
}
}
return list;
}
private bool LoadPVOutputBatch()
{
GenDataReader dr = null;
bool logRequired = false;
int messageStatusCount = 0;
String postData = "";
ErrorReported = false;
DateTime lastTime = DateTime.Now;
int messageLimit;
int availRequests;
if (Settings.APIVersion == "r1")
messageLimit = PVOutputr1Size;
else
messageLimit = PVOutputr2Size;
GenCommand cmdLoadSel = null;
GenConnection con = null;
bool complete = true;
int sentCount = 0;
try
{
if (Settings.APIVersion == "r1")
availRequests = (PVOutputHourLimit - RequestCount) * PVOutputr1Multiple;
else
availRequests = (PVOutputHourLimit - RequestCount) * PVOutputr2Multiple;
con = GlobalSettings.TheDB.NewConnection();
cmdLoadSel = new GenCommand(CmdLoadSelect, con);
cmdLoadSel.AddParameterWithValue("@SiteId", SystemId);
cmdLoadSel.AddParameterWithValue("@FirstDay", PVDateLimit);
dr = (GenDataReader)cmdLoadSel.ExecuteReader();
DateTime prevDate = DateTime.Today;
while (dr.Read() && ManagerManager.RunMonitors)
{
DateTime date = dr.GetDateTime(1).Date;
if (messageStatusCount == messageLimit
|| (messageStatusCount > 0 && date != prevDate) // force new batch at date change - pvoutput day total update requirement
|| (messageStatusCount >= availRequests && messageStatusCount > 0))
{
// pvoutput enforces 1 per second now
int sleep = PVOutputDelay - (int)((DateTime.Now - lastTime).TotalMilliseconds);
if (sleep > 0)
Thread.Sleep(sleep);
if (SendPVOutputBatch(postData, messageStatusCount, availRequests))
logRequired = true;
else
{
// error encountered exit with incomplete status
messageStatusCount = 0;
complete = false;
break;
}
lastTime = DateTime.Now;
availRequests -= messageStatusCount;
sentCount += messageStatusCount;
messageStatusCount = 0;
postData = "";
}
prevDate = date;
if (RequestCount >= PVOutputHourLimit)
{
// hour quota exhausted - exit with incomplete status
if (!PVOutputLimitReported)
{
LogMessage("LoadPVOutputBatch", "Reached pvoutput request limit - pending updates delayed", LogEntryType.Information);
PVOutputLimitReported = true;
}
complete = false;
break;
}
int hourUpdatesRequired = (60 - (int)DateTime.Now.Minute) / 5;
if (RequestCount >= (PVOutputHourLimit - hourUpdatesRequired))
{
// approaching hour quota - only process data for today
if (date != DateTime.Today)
{
if (!PVOutputCurrentDayLimitReported)
{
LogMessage("LoadPVOutputBatch", "Reached pvoutput request limit - pending updates delayed", LogEntryType.Information);
PVOutputCurrentDayLimitReported = true;
}
complete = false;
continue;
}
}
//if (messageStatusCount > 0)
// postData += ";";
{
int time = dr.GetInt32(2);
if (time < (24 * 3600))
{
if (messageStatusCount > 0)
postData += ";";
postData += dr.GetDateTime(1).ToString("yyyyMMdd") +
"," + TimeSpan.FromSeconds(time).ToString(@"hh\:mm");
}
else
{
continue; // skip 24:00 being rejected at PVOutout as invalid time
//if (messageStatusCount > 0)
// postData += ";";
//postData += dr.GetDateTime(1).ToString("yyyyMMdd") + ",24:00"; // ToString results in 00:00, PVOutput needs 24:00
}
}
if (Settings.UploadYield)
if (dr.IsDBNull(3)) // is energy generated null
postData += ",,";
else
postData += "," + ((Int32)dr.GetDouble(3)).ToString() + "," + ((Int32)dr.GetDouble(4)).ToString();
else
postData += ",-1,-1"; // causes pvoutput to ignore yield (no overwrite)
if (Settings.UploadConsumption)
if (dr.IsDBNull(5)) // is energy consumed null
postData += ",,";
else
postData +=
"," + ((Int32)dr.GetDouble(5)).ToString() + "," + ((Int32)dr.GetDouble(6)).ToString();
else
postData += ",-1,-1"; // causes pvoutput to ignore consumption (no overwrite)
if (Settings.APIVersion != "r1")
if (!dr.IsDBNull(7)) // is temperature imported null
postData +=
"," + (dr.GetDouble(7)).ToString("F");
messageStatusCount++;
}
dr.Close();
if (messageStatusCount > 0)
{
// pvoutput enforces 1 per second now
int sleep = PVOutputDelay - (int)((DateTime.Now - lastTime).TotalMilliseconds);
if (sleep > 0)
Thread.Sleep(sleep);
if (SendPVOutputBatch(postData, messageStatusCount, availRequests))
logRequired = true;
sentCount += messageStatusCount;
}
}
catch (GenException e)
{
throw new Exception("LoadPVOutputBatch: " + e.Message);
}
catch (Exception e)
{
throw new Exception("LoadPVOutputBatch: " + e.Message, e);
}
finally
{
if (dr != null)
dr.Dispose();
if (cmdLoadSel != null)
cmdLoadSel.Dispose();
if (con != null)
{
con.Close();
con.Dispose();
}
}
if (logRequired)
{
LogMessage("LoadPVOutputBatch", "pvoutput.org batch updated - DataPoints: " + sentCount +
" - Hour Total: " + RequestCount + " - Limit: " + PVOutputHourLimit, LogEntryType.Information);
}
return complete;
}
private DateTime? GetOldestDay()
{
GenDataReader dr = null;
DateTime? oldestDay = null;
GenCommand cmdSelOldestDay = null;
GenConnection con = null;
try
{
con = GlobalSettings.TheDB.NewConnection();
cmdSelOldestDay = new GenCommand(CmdSelectOldestDay, con);
dr = (GenDataReader)cmdSelOldestDay.ExecuteReader();
if (dr.Read())
{
oldestDay = dr.IsDBNull(0) ? (DateTime?)null : dr.GetDateTime(0);
}
dr.Close();
}
catch (GenException e)
{
throw new Exception("GetOldestDay - Database exception: " + e.Message, e);
}
catch (Exception e)
{
throw new Exception("GetOldestDay: " + e.Message, e);
}
finally
{
if (dr != null)
dr.Dispose();
if (cmdSelOldestDay != null)
cmdSelOldestDay.Dispose();
if (con != null)
{
con.Close();
con.Dispose();
}
}
return oldestDay;
}
private void RecordYield(DateTime readingTime, long energy, long power, bool intervalHasEnergy, Double? temperature)
{
// Check for an existing record in the pvoutputlog table.
// If it exists update it if the yield energy or power values have changed.
// If it does not exist, add the record if the yield energy is not zero
GenCommand cmdCheck = null;
GenConnection con = null;
GenDataReader drCheck = null;
int timeVal = 0;
DateTime date = DateTime.MinValue;
try
{
date = readingTime.Date;
timeVal = (int)readingTime.TimeOfDay.TotalSeconds;
if (timeVal == 0)
{
date = date.AddDays(-1.0);
timeVal = 24 * 3600; // 24:00 - This is required by PVOutput for the end of day reading
}
con = GlobalSettings.TheDB.NewConnection();
cmdCheck = new GenCommand(CmdCheckStr, con);
cmdCheck.AddParameterWithValue("@SiteId", SystemId);
cmdCheck.AddParameterWithValue("@OutputDay", readingTime.Date);
cmdCheck.AddParameterWithValue("@OutputTime", timeVal);
drCheck = (GenDataReader)cmdCheck.ExecuteReader();
bool update = false;
bool insert = false;
if (drCheck.Read())
{
if (drCheck.IsDBNull(0)
|| (((long)Math.Round(drCheck.GetDouble(0))) != energy)
|| (((long)Math.Round(drCheck.GetDouble(1))) != power))
{
if (!drCheck.IsDBNull(0))
LogMessage("RecordYield", "Update - Time: " + readingTime + " - Date: " + date + " - timeVal: " + timeVal + " - Energy: " + (long)Math.Round(drCheck.GetDouble(0)) + " - " + energy +
" - Percent: " + ((energy - drCheck.GetDouble(0)) / energy).ToString("P", CultureInfo.InvariantCulture) +
" - Power: " + (long)Math.Round(drCheck.GetDouble(1)) + " - " + power, LogEntryType.DetailTrace);
else
LogMessage("RecordYield", "Update - Time: " + readingTime + " - Date: " + date + " - timeVal: " + timeVal + " - Energy: null - " + (int)(energy),
LogEntryType.DetailTrace);
update = true;
}
}
else if (intervalHasEnergy) // only add new records if energy > 0
{
LogMessage("RecordYield", "Record not found - Time: " + readingTime + " - Date: " + date + " - timeVal: " + timeVal + " - Energy: " + energy + " - Power: " + power, LogEntryType.DetailTrace);
insert = true;
}
drCheck.Close();
drCheck.Dispose();
drCheck = null;
con.Close();
con.Dispose();
con = null;
if (insert)
InsertPVOutputLog(date, timeVal, energy, power, temperature);
else if (update)
UpdatePVOutputLog(date, timeVal, energy, power, temperature);
}
catch (Exception e)
{
LogMessage("RecordYield", "Time: " + readingTime + " - Date: " + date + " - timeVal: " + timeVal + " - Exception: " + e.Message, LogEntryType.ErrorMessage);
}
finally
{
if (drCheck != null)
{
drCheck.Close();
drCheck.Dispose();
}
if (con != null)
{
con.Close();
con.Dispose();
}
}
}