public TableRowsApiResultModel Get([BindRequired, FromQuery] string label, [BindRequired, FromQuery] int from_time, [BindRequired, FromQuery] int to_time)
{
PspDbHelper helper = new PspDbHelper {
ConnStr = Configuration["dbinfo:ConnectionString"]
};
//return helper.GetDbTableRows("select * from DIM_CONDUCTOR");
try
{
PspDbMeasurement pspDbMeasurement = _AppDbContext.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 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 async Task <ActionResult <List <PspLabelApiItem> > > GetData()
{
PspDbHelper helper = new PspDbHelper {
ConnStr = Configuration["pspdbinfo:ConnectionString"]
};
List <PspLabelApiItem> pspDbMeasurements = new List <PspLabelApiItem>();
try
{
List <PspMeasurement> measurements = await _context.PspDbMeasurements.ToListAsync();
for (int measIter = 0; measIter < measurements.Count; measIter++)
{
pspDbMeasurements.Add(new PspLabelApiItem {
Label = measurements[measIter].Label, Id = measurements[measIter].MeasId
});
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(pspDbMeasurements);
}
public static List <LabelCheckResult> GetLabelCheckResults(string connStr, LabelCheck labelCheck, DateTime fromTime, DateTime toTime)
{
// get measurement fetch helper
PspDbHelper helper = new PspDbHelper()
{
ConnStr = connStr
};
// fetch the measurement values
List <PspTimeValTuple> tuples = helper.GetPSPMeasVals(labelCheck.PspMeasurement, ConvertDateTimeToInt(fromTime), ConvertDateTimeToInt(toTime));
// do the checking algorithm on each tuple to produce check results
List <LabelCheckResult> labelCheckResults = new List <LabelCheckResult>();
// initialize a checkresult for each date with remark, data not found for processing
for (int dayIter = 0; dayIter <= (toTime - fromTime).Days; dayIter++)
{
labelCheckResults.Add(new LabelCheckResult {
IsSuccessful = false, CheckProcessStartTime = fromTime.AddDays(dayIter), CheckProcessEndTime = fromTime.AddDays(dayIter), LabelCheckId = labelCheck.Id, Remarks = "data not processed"
});
}
// scan all the tuples to process the checks and replace with initial values
for (int tupleIter = 0; tupleIter < tuples.Count; tupleIter++)
{
int targetResultInd;
LabelCheckResult targetResult;
PspTimeValTuple tuple = tuples[tupleIter];
// find the tuple to be modified from the list of inititalized results
targetResultInd = labelCheckResults.FindIndex(res =>
(res.CheckProcessStartTime.Date == ConvertIntToDateTime(tuple.TimeInt).Date&&
res.CheckProcessEndTime.Date == ConvertIntToDateTime(tuple.TimeInt).Date));
if (targetResultInd == -1)
{
continue;
}
if (labelCheck.CheckType == CheckTypeNotNull)
{
targetResult = labelCheckResults.ElementAt(targetResultInd);
if (tuple.Val == null)
{
targetResult.CheckProcessEndTime = ConvertIntToDateTime(tuple.TimeInt);
targetResult.CheckProcessStartTime = ConvertIntToDateTime(tuple.TimeInt);
targetResult.IsSuccessful = false;
targetResult.Remarks = "null value found";
labelCheckResults[targetResultInd] = targetResult;
}
else
{
targetResult.CheckProcessEndTime = ConvertIntToDateTime(tuple.TimeInt);
targetResult.CheckProcessStartTime = ConvertIntToDateTime(tuple.TimeInt);
targetResult.IsSuccessful = true;
targetResult.Remarks = $"passed, value={tuple.Val}";
labelCheckResults[targetResultInd] = targetResult;
}
}
else if (labelCheck.CheckType == CheckTypeLimit)
{
targetResult = labelCheckResults.ElementAt(targetResultInd);
if (tuple.Val >= labelCheck.Num1 && tuple.Val <= labelCheck.Num2)
{
targetResult.CheckProcessEndTime = ConvertIntToDateTime(tuple.TimeInt);
targetResult.CheckProcessStartTime = ConvertIntToDateTime(tuple.TimeInt);
targetResult.IsSuccessful = true;
targetResult.Remarks = $"passed, value={tuple.Val}";
labelCheckResults[targetResultInd] = targetResult;
}
else if (tuple.Val == null)
{
targetResult.CheckProcessEndTime = ConvertIntToDateTime(tuple.TimeInt);
targetResult.CheckProcessStartTime = ConvertIntToDateTime(tuple.TimeInt);
targetResult.IsSuccessful = false;
targetResult.Remarks = "null value found";
labelCheckResults[targetResultInd] = targetResult;
}
else
{
targetResult.CheckProcessEndTime = ConvertIntToDateTime(tuple.TimeInt);
targetResult.CheckProcessStartTime = ConvertIntToDateTime(tuple.TimeInt);
targetResult.IsSuccessful = false;
targetResult.Remarks = $"limits violated, value={tuple.Val}";;
labelCheckResults[targetResultInd] = targetResult;
}
}
}
return(labelCheckResults);
}
