public PspTimeValTuple GetPSPMeasVal(PspMeasurement pspMeasurement, int fromTime)
{
TableRowsApiResultModel fetchedData = GetLabelData(pspMeasurement, fromTime, fromTime);
List <List <object> > rows = fetchedData.TableRows;
List <string> colNames = fetchedData.TableColNames;
int timeInd = colNames.IndexOf(pspMeasurement.PspTimeCol);
int valInd = colNames.IndexOf(pspMeasurement.PspValCol);
if (timeInd == -1 || valInd == -1)
{
// desired result was not found
return(null);
}
if (rows.Count == 0)
{
// desired result was not found
return(null);
}
// todo check val types also
int timeInt = Convert.ToInt32((decimal)rows.ElementAt(0).ElementAt(timeInd));
decimal? val = (decimal)rows.ElementAt(0).ElementAt(valInd);
PspTimeValTuple result = new PspTimeValTuple {
TimeInt = timeInt, Val = val
};
return(result);
}
public static void ProcessLabelCheck(LabelChecksDbContext labelChecksDbContext, string connStr, LabelCheck labelCheck, DateTime fromTime, DateTime toTime)
{
PspMeasurement meas = labelChecksDbContext.PspDbMeasurements.Where(m => m.MeasId == labelCheck.PspMeasurementId).FirstOrDefault();
labelCheck.PspMeasurement = meas;
List <LabelCheckResult> labelCheckResults = GetLabelCheckResults(connStr, labelCheck, fromTime, toTime);
//iterate through each result, find if record already exists and update the database
for (int resIter = 0; resIter < labelCheckResults.Count; resIter++)
{
LabelCheckResult targetRes = labelCheckResults[resIter];
LabelCheckResult foundRes = labelChecksDbContext.LabelCheckResults.Where(res => res.LabelCheckId == targetRes.LabelCheckId &&
res.CheckProcessEndTime.Date == targetRes.CheckProcessEndTime.Date &&
res.CheckProcessStartTime.Date == targetRes.CheckProcessStartTime.Date).FirstOrDefault();
if (foundRes == null)
{
// the record doesnot exist, so insert into the db
labelChecksDbContext.LabelCheckResults.Add(targetRes);
}
else
{
//targetRes.Id = foundRes.Id;
// make found result as target result and update
foundRes.IsSuccessful = targetRes.IsSuccessful;
foundRes.Remarks = targetRes.Remarks;
foundRes.CheckProcessEndTime = targetRes.CheckProcessEndTime;
foundRes.CheckProcessStartTime = targetRes.CheckProcessStartTime;
labelChecksDbContext.LabelCheckResults.Update(foundRes);
}
}
labelChecksDbContext.SaveChanges();
}
public async Task <IActionResult> Edit(int id, [Bind("MeasId,Label,PspTable,PspValCol,PspTimeCol,EntityCol,EntityVal,SqlStr,QueryParams,QueryParamVals")] PspMeasurement pspMeasurement)
{
if (id != pspMeasurement.MeasId)
{
return(NotFound());
}
if (ModelState.IsValid)
{
try
{
_context.Update(pspMeasurement);
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!PspMeasurementExists(pspMeasurement.MeasId))
{
return(NotFound());
}
else
{
throw;
}
}
return(RedirectToAction(nameof(Index)));
}
return(View(pspMeasurement));
}
public async Task OnPostAsync()
{
ViewData["MeasurementId"] = new SelectList(_context.PspMeasurements, "Id", "Label");
PspMeasurement measurement = await _context.PspMeasurements.Where(m => m.Id == MeasurementId).FirstOrDefaultAsync();
ZscoresData = await _mediator.Send(new GetZScoreDataQuery()
{
StartTime = StartDate.Date, EndTime = EndDate.Date, Measurement = measurement, NumDays = NumDays
});
}
public async Task <IActionResult> Create([Bind("MeasId,Label,PspTable,PspValCol,PspTimeCol,EntityCol,EntityVal,SqlStr,QueryParams,QueryParamVals")] PspMeasurement pspMeasurement)
{
if (ModelState.IsValid)
{
_context.Add(pspMeasurement);
await _context.SaveChangesAsync();
return(RedirectToAction(nameof(Index)));
}
return(View(pspMeasurement));
}
public async Task <IActionResult> OnGetAsync(int?id)
{
if (id == null)
{
return(NotFound());
}
PspMeasurement = await _context.PspMeasurements.FirstOrDefaultAsync(m => m.Id == id);
if (PspMeasurement == null)
{
return(NotFound());
}
return(Page());
}
public async Task <IActionResult> OnPostAsync(int?id)
{
if (id == null)
{
return(NotFound());
}
PspMeasurement = await _context.PspMeasurements.FindAsync(id);
if (PspMeasurement != null)
{
_context.PspMeasurements.Remove(PspMeasurement);
await _context.SaveChangesAsync();
}
return(RedirectToPage("./Index"));
}
public TableRowsApiResultModel Get([BindRequired, FromQuery] string label, [BindRequired, FromQuery] int from_time, [BindRequired, FromQuery] int to_time)
{
PspDbHelper helper = new PspDbHelper {
ConnStr = Configuration["pspdbinfo:ConnectionString"]
};
//return helper.GetDbTableRows("select * from DIM_CONDUCTOR");
try
{
PspMeasurement pspDbMeasurement = _context.PspDbMeasurements.Single(m => m.Label == label);
return(helper.GetLabelData(pspDbMeasurement, from_time, to_time));
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(new TableRowsApiResultModel());
}
}
public TableRowsApiResultModel GetLabelData(PspMeasurement pspMeasurement, int fromTime, int toTime)
{
string sql = "";
List <OracleParameter> parameters = new List <OracleParameter>();
if (String.IsNullOrWhiteSpace(pspMeasurement.PspTimeCol))
{
return(new TableRowsApiResultModel());
}
if (!String.IsNullOrWhiteSpace(pspMeasurement.PspTable) &&
!String.IsNullOrWhiteSpace(pspMeasurement.PspValCol) &&
!String.IsNullOrWhiteSpace(pspMeasurement.EntityCol) &&
!String.IsNullOrWhiteSpace(pspMeasurement.EntityVal))
{
// get the label details from the sqlite context
sql = $"select {pspMeasurement.PspTimeCol}, {pspMeasurement.PspValCol} from {pspMeasurement.PspTable} where {pspMeasurement.EntityCol}='{pspMeasurement.EntityVal}' AND ({pspMeasurement.PspTimeCol} BETWEEN {fromTime} AND {toTime}) ORDER BY {pspMeasurement.PspTimeCol} ASC";
}
if ((String.IsNullOrWhiteSpace(pspMeasurement.EntityCol) || String.IsNullOrWhiteSpace(pspMeasurement.EntityVal)) && !String.IsNullOrWhiteSpace(pspMeasurement.SqlStr))
{
// check if enitity info is not complete and sql string is available for execution
sql = pspMeasurement.SqlStr;
if (!String.IsNullOrWhiteSpace(pspMeasurement.QueryParams) && !String.IsNullOrWhiteSpace(pspMeasurement.QueryParamVals))
{
// add params if query params and query param vals strings are not null
List <string> queryParams = new List <string>();
List <string> queryParamVals = new List <string>();
queryParams = pspMeasurement.QueryParams.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).Select(p => p.Trim()).ToList();
queryParamVals = pspMeasurement.QueryParamVals.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).Select(p => p.Trim()).ToList();
for (int paramIter = 0; paramIter < queryParams.Count; paramIter++)
{
parameters.Add(new OracleParameter(queryParams[paramIter], queryParamVals[paramIter]));
}
}
// add function input params, the param names are taken by convention
parameters.Add(new OracleParameter(fromTimeParamName, fromTime));
parameters.Add(new OracleParameter(toTimeParamName, toTime));
}
return(GetDbTableRows(sql, parameters));
}
public List <PspTimeValTuple> GetPSPMeasVals(PspMeasurement pspMeasurement, int fromTime, int toTime)
{
TableRowsApiResultModel fetchedData = GetLabelData(pspMeasurement, fromTime, toTime);
List <List <object> > rows = fetchedData.TableRows;
List <string> colNames = fetchedData.TableColNames;
int timeInd = colNames.IndexOf(pspMeasurement.PspTimeCol);
int valInd = colNames.IndexOf(pspMeasurement.PspValCol);
List <PspTimeValTuple> results = new List <PspTimeValTuple>();
if (timeInd == -1 || valInd == -1)
{
// desired result was not found
return(results);
}
for (int rowIter = 0; rowIter < rows.Count; rowIter++)
{
// todo check val types also and try to accommodate other types also
int timeInt = 0;
decimal?val;
try
{
timeInt = Convert.ToInt32((decimal)rows.ElementAt(rowIter).ElementAt(timeInd));
}
catch (Exception e)
{
// didnot find valid timestamp, so we will not record the value
continue;
}
try
{
val = (decimal)rows.ElementAt(rowIter).ElementAt(valInd);
}
catch (Exception e)
{
val = null;
}
results.Add(new PspTimeValTuple {
TimeInt = timeInt, Val = val
});
}
return(results);
}
public void PSPSQLMeasFetchTest()
{
string connStr = Configuration["pspdbinfo:ConnectionString"];
Assert.NotNull(connStr);
// get measurement fetch helper
PspDbHelper helper = new PspDbHelper()
{
ConnStr = connStr
};
// fetch the measurement values
DateTime fromTime = DateTime.Now.AddDays(-1);
DateTime toTime = DateTime.Now.AddDays(-1);
PspMeasurement meas = new PspMeasurement {
PspTimeCol = "DATE_KEY",
SqlStr = "select MAX(DATE_KEY) DATE_KEY, SUM(UI) UI from state_load_details where DATE_KEY BETWEEN :from_time AND :to_time group by DATE_KEY ORDER BY DATE_KEY ASC",
PspValCol = "UI"
};
List <PspTimeValTuple> tuples = helper.GetPSPMeasVals(meas, LabelCheckUtils.ConvertDateTimeToInt(fromTime), LabelCheckUtils.ConvertDateTimeToInt(toTime));
Assert.False(tuples.Count == 0);
}
public override object ReadJson(JsonReader reader,
Type objectType, object existingValue,
JsonSerializer serializer)
{
var jsonObject = JObject.Load(reader);
var measurement = default(IMeasurement);
string objectTypeName = jsonObject["TypeName"].Value <string>();
if (objectTypeName == typeof(RandomMeasurement).Name)
{
measurement = new RandomMeasurement();
}
else if (objectTypeName == typeof(RandomTimeSeriesMeasurement).Name)
{
measurement = new RandomTimeSeriesMeasurement();
}
else if (objectTypeName == typeof(PMUMeasurement).Name)
{
measurement = new PMUMeasurement();
}
else if (objectTypeName == typeof(ScadaMeasurement).Name)
{
measurement = new ScadaMeasurement();
}
else if (objectTypeName == typeof(PspMeasurement).Name)
{
measurement = new PspMeasurement();
}
if (measurement != null)
{
serializer.Populate(jsonObject.CreateReader(), measurement);
}
return(measurement);
}
private void PspSettings_Click(object sender, RoutedEventArgs e)
{
PspMeasurement.OpenSettingsWindow();
}
