/// <summary>
/// Restituisce l'ultimo timestamp scritto nel database sorgente
/// </summary>
/// <param name="connection"></param>
/// <param name="start_date">Data di inizio</param>
/// <param name="measure_coord"></param>
/// <returns></returns>
DateTime GetLastSourceSample(WetDBConn connection, DateTime start_date, MeasureDBCoord_Struct measure_coord)
{
DateTime ret = DateTime.MinValue;
try
{
DataTable dt = connection.ExecCustomQuery(GetBaseQueryStr(connection, measure_coord, start_date, DateTime.Now, WetDBConn.OrderTypes.DESC, 1));
if (dt.Rows.Count == 1)
ret = Convert.ToDateTime(dt.Rows[0][0]);
}
catch (Exception ex)
{
WetDebug.GestException(ex);
}
return ret;
}
/// <summary>
/// Corpo del job
/// </summary>
protected override void DoJob()
{
// Acquisisco le connessioni presenti nel database
DataTable connections = wet_db.ExecCustomQuery("SELECT * FROM connections");
connections.PrimaryKey = new DataColumn[] { connections.Columns["id_odbcdsn"] };
// Acquisisco le misure presenti nel database
DataTable measures = wet_db.ExecCustomQuery("SELECT * FROM measures");
// Ciclo per tutte le misure
foreach (DataRow measure in measures.Rows)
{
try
{
// Acquisisco l'ID univoco della misura
int id_measure = Convert.ToInt32(measure["id_measures"]);
int id_odbc_dsn = Convert.ToInt32(measure["connections_id_odbcdsn"]);
MeasureTypes mtype = (MeasureTypes)Convert.ToInt32(measure["type"]);
bool reliable = Convert.ToBoolean(measure["reliable"]);
DateTime start_date = Convert.ToDateTime(measure["update_timestamp"]);
double energy_specific_content = Convert.ToDouble(measure["energy_specific_content"]) * 3.6d; // KWh/mc -> KW/(l/s)
// Popolo le coordinate database per la misura
MeasureDBCoord_Struct measure_coord;
int dsn_id = Convert.ToInt32(measure["connections_id_odbcdsn"]);
measure_coord.odbc_connection = Convert.ToString(connections.Rows.Find(dsn_id)["odbc_dsn"]);
measure_coord.username = (connections.Rows.Find(dsn_id)["username"] == DBNull.Value ? null : Convert.ToString(connections.Rows.Find(dsn_id)["username"]));
measure_coord.password = (connections.Rows.Find(dsn_id)["password"] == DBNull.Value ? null : Convert.ToString(connections.Rows.Find(dsn_id)["password"]));
measure_coord.table_name = Convert.ToString(measure["table_name"]);
measure_coord.timestamp_column = Convert.ToString(measure["table_timestamp_column"]);
measure_coord.value_column = Convert.ToString(measure["table_value_column"]);
measure_coord.relational_id_column = Convert.ToString(measure["table_relational_id_column"]);
measure_coord.relational_id_value = Convert.ToString(measure["table_relational_id_value"]);
measure_coord.relational_id_type = (WetDBConn.PrimaryKeyColumnTypes)Convert.ToInt32(measure["table_relational_id_type"]);
// Istanzio la connessione al database sorgente
WetDBConn source_db = new WetDBConn(measure_coord.odbc_connection, measure_coord.username, measure_coord.password, false);
// Estraggo il timestamp dell'ultimo valore scritto nel database sorgente
DateTime last_source = GetLastSourceSample(source_db, start_date, measure_coord);
// Estraggo il timestamp dell'ultimo valore scritto nel database WetNet
DateTime last_dest = GetLastDestSample(id_measure);
if (last_dest == DateTime.MinValue)
last_dest = start_date;
// Controllo se ci sono campioni da acquisire
if (last_dest < last_source)
{
// Acquisisco tutti i campioni da scrivere
DataTable samples = source_db.ExecCustomQuery(GetBaseQueryStr(source_db, measure_coord, last_dest, DateTime.Now, WetDBConn.OrderTypes.ASC, MAX_RECORDS_IN_QUERY));
// Gestione dei contatori volumetrici
if (mtype == MeasureTypes.COUNTER)
{
// Acquisisco il campione precedente al primo della tabella samples
DataTable cnt_tbl = source_db.ExecCustomQuery(GetBaseQueryStr(source_db, measure_coord, WetDBConn.START_DATE, last_dest.Subtract(new TimeSpan(0, 0, 0, 1)), WetDBConn.OrderTypes.DESC, 1));
// Lo inserisco nella tabella samples
if (cnt_tbl.Rows.Count > 0)
{
samples.ImportRow(cnt_tbl.Rows[0]);
DataView dv = samples.DefaultView;
dv.Sort = "[" + cnt_tbl.Columns[0].ColumnName + "] ASC";
samples = dv.ToTable();
}
// Creo una tabella di appoggio temporanea
DataTable cnt_tbl_q = samples.Clone();
// Ciclo per tutti i campioni di samples
DateTime now_dt, prec_dt;
double now_v, prec_v, liters, flow, seconds;
for (int ii = 0; ii < samples.Rows.Count; ii++)
{
if (ii == 0)
continue; // Salta il primo record
// Acquisisco i valori attuali e precedenti
prec_dt = Convert.ToDateTime(samples.Rows[ii - 1][0]);
now_dt = Convert.ToDateTime(samples.Rows[ii][0]);
prec_v = Convert.ToDouble(samples.Rows[ii - 1][1]);
now_v = Convert.ToDouble(samples.Rows[ii][1]);
// Calcolo la differenza in litri
seconds = (now_dt - prec_dt).TotalSeconds;
liters = (now_v * 1000) - (prec_v * 1000);
// Calcolo la portata
flow = liters / seconds;
// Popolo la tabella
cnt_tbl_q.Rows.Add(now_dt, flow);
}
// Assegno la tabella temporanea a 'samples'
samples.Clear();
samples = cnt_tbl_q.Copy();
}
DataTable dest = new DataTable();
dest.Columns.Add("timestamp", typeof(DateTime));
dest.Columns.Add("reliable", typeof(bool));
dest.Columns.Add("value", typeof(double));
dest.Columns.Add("measures_id_measures", typeof(int));
dest.Columns.Add("measures_connections_id_odbcdsn", typeof(int));
/************************************************************/
/*** INIZIO PROCEDURA DI INTERPOLAZIONE LINEARE DEI PUNTI ***/
/************************************************************/
// Calcolo il timestamp del valore precedente
DateTime first = Convert.ToDateTime(samples.Rows[0][0]);
DateTime prec = new DateTime(first.Ticks % interpolation_time.Ticks == 0 ? first.Ticks - interpolation_time.Ticks : (first.Ticks / interpolation_time.Ticks) * interpolation_time.Ticks);
// Acquisisco, se presente, ultimo campione precedente a quelli acquisiti, se non esiste, il valore lo considero a zero
DataTable tmp = wet_db.ExecCustomQuery("SELECT `timestamp`, `value` FROM data_measures WHERE `timestamp` <= '" +
prec.ToString(WetDBConn.MYSQL_DATETIME_FORMAT) + "' AND `measures_id_measures` = " +
id_measure.ToString() + " ORDER BY `timestamp` DESC LIMIT 1");
DataRow new_row = samples.NewRow();
if (tmp.Rows.Count == 1)
{
new_row[0] = Convert.ToDateTime(tmp.Rows[0][0]);
new_row[1] = Convert.ToDouble(tmp.Rows[0][1]);
}
else
{
new_row[0] = prec;
new_row[1] = 0.0d;
}
samples.Rows.InsertAt(new_row, 0);
// Interpolazione lineare
Dictionary<DateTime, double> interpolated = WetMath.LinearInterpolation(interpolation_time,
WetMath.DataTable2Dictionary(samples, measure_coord.timestamp_column, measure_coord.value_column));
// Riconversione in tabella dati
for (int ii = 0; ii < interpolated.Count; ii++)
dest.Rows.Add(interpolated.ElementAt(ii).Key, 1, interpolated.ElementAt(ii).Value, id_measure, id_odbc_dsn);
/**********************************************************/
/*** FINE PROCEDURA DI INTERPOLAZIONE LINEARE DEI PUNTI ***/
/**********************************************************/
// Inserisco i valori ottenuti nella tabella dati
wet_db.TableInsert(dest, "data_measures");
/**********************************/
/*** Calcolo profilo energetico ***/
/**********************************/
// Creo la tabella di appoggio
DataTable measures_energy_profile = new DataTable();
measures_energy_profile.Columns.Add("timestamp", typeof(DateTime));
measures_energy_profile.Columns.Add("reliable", typeof(bool));
measures_energy_profile.Columns.Add("value", typeof(double));
measures_energy_profile.Columns.Add("measures_id_measures", typeof(int));
measures_energy_profile.Columns.Add("measures_connections_id_odbcdsn", typeof(int));
// Ciclo per tutti i campioni di portata
foreach (DataRow dr in dest.Rows)
{
// Creo un nuovo record vuoto
DataRow mep_r = measures_energy_profile.NewRow();
// Lo popolo calcolando la potenza associata
mep_r["timestamp"] = dr["timestamp"];
mep_r["reliable"] = dr["reliable"];
mep_r["value"] = Convert.ToDouble(dr["value"]) * energy_specific_content;
mep_r["measures_id_measures"] = dr["measures_id_measures"];
mep_r["measures_connections_id_odbcdsn"] = dr["measures_connections_id_odbcdsn"];
// Lo inserisco nella tabella temporanea
measures_energy_profile.Rows.Add(mep_r);
}
// Inserisco i dati sul DB
wet_db.TableInsert(measures_energy_profile, "measures_energy_profile");
}
}
catch (Exception ex)
{
WetDebug.GestException(ex);
}
// Passo il controllo al S.O. per l'attesa
if (cancellation_token_source.IsCancellationRequested)
return;
Sleep();
}
// Aggiorno cold_start_counter
if (WetEngine.cold_start_counter == 0)
WetEngine.cold_start_counter++;
}
/// <summary>
/// Restituisce l'ultimo allarme presente per la misura in questione
/// </summary>
/// <param name="wet_db">Connessione al database WetNet</param>
/// <param name="id_measure">ID della misura</param>
/// <param name="id_odbcdsn">ID della connessione</param>
/// <param name="date">Data da analizzare</param>
/// <returns>Ultimo allarme del giorno specificato</returns>
public static AlarmStruct ReadLastAlarmDay(WetDBConn wet_db, int id_measure, int id_odbcdsn, DateTime date)
{
// Leggo l'ultimo allarme presente
DataTable last_alarm_data = wet_db.ExecCustomQuery("SELECT * FROM measures_alarms WHERE `measures_id_measures` = " + id_measure.ToString() +
" AND `timestamp` < '" + date.Date.AddDays(1.0d).ToString(WetDBConn.MYSQL_DATE_FORMAT) + "' ORDER BY `timestamp` DESC LIMIT 1");
AlarmStruct last_alarm;
if (last_alarm_data.Rows.Count > 0)
{
last_alarm.timestamp = Convert.ToDateTime(last_alarm_data.Rows[0]["timestamp"]);
last_alarm.alarm_type = (AlarmTypes)Convert.ToInt32(last_alarm_data.Rows[0]["alarm_type"]);
last_alarm.event_type = (EventTypes)Convert.ToInt32(last_alarm_data.Rows[0]["event_type"]);
last_alarm.alarm_value = Convert.ToDouble(last_alarm_data.Rows[0]["alarm_value"]);
last_alarm.reference_value = Convert.ToDouble(last_alarm_data.Rows[0]["reference_value"]);
string[] strs = Convert.ToString(last_alarm_data.Rows[0]["duration"]).Split(new char[] { ':' });
last_alarm.duration = new TimeSpan(Convert.ToInt32(strs[0]), Convert.ToInt32(strs[1]), Convert.ToInt32(strs[2]));
last_alarm.id_measure = Convert.ToInt32(last_alarm_data.Rows[0]["measures_id_measures"]);
last_alarm.id_odbcdsn = Convert.ToInt32(last_alarm_data.Rows[0]["measures_connections_id_odbcdsn"]);
}
else
{
last_alarm.timestamp = DateTime.Now;
last_alarm.alarm_type = AlarmTypes.INVALID;
last_alarm.event_type = EventTypes.UNKNOWN;
last_alarm.alarm_value = 0.0d;
last_alarm.reference_value = 0.0d;
last_alarm.duration = new TimeSpan();
last_alarm.id_measure = id_measure;
last_alarm.id_odbcdsn = id_odbcdsn;
}
return last_alarm;
}