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); }