public IHttpActionResult GetLast(string meter)
{
if (GetRoles == string.Empty || User.IsInRole(GetRoles))
{
try
{
using (AdoDataConnection connection = new AdoDataConnection(Connection))
{
IEnumerable <MiMDDailyStatistic> result = new TableOperations <MiMDDailyStatistic>(connection).QueryRecordsWhere("Meter = {0}", meter);
MiMDDailyStatistic statistic = null;
if (result.Any())
{
statistic = result.OrderBy(x => x.Date).Last();
}
return(Ok(statistic));
}
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
else
{
return(Unauthorized());
}
}
private IEnumerable <DisturbanceData> GetDisturbanceDatas(DateTime startDate, DateTime endDate)
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings")) {
IEnumerable <PQMarkCompanyMeter> meters = new TableOperations <PQMarkCompanyMeter>(connection).QueryRecordsWhere("Enabled = 1");
if (!meters.Any())
{
return(null);
}
DataTable table = connection.RetrieveData(
@"SELECT Event.MeterID, Disturbance.PerUnitMagnitude, Disturbance.StartTime, Disturbance.EndTime, Disturbance.DurationSeconds, Disturbance.DurationCycles
FROM Disturbance JOIN
Event ON Event.ID = Disturbance.EventID
WHERE Disturbance.PhaseID = (SELECT ID FROM Phase WHERE Name = 'Worst') AND
Event.MeterID IN (" + string.Join(",", meters.Select(x => x.MeterID)) + @") AND
Event.StartTime Between '" + startDate + @"' AND '" + endDate + @"'"
);
return(table.Select().Select(row => new TableOperations <DisturbanceData>(connection).LoadRecord(row)));
}
}
public IHttpActionResult Post(DataForPeriodForm form)
{
try
{
EventSet eventSet = new EventSet();
if (form.context == "day")
{
eventSet.StartDate = DateTime.Parse(form.targetDateFrom).ToUniversalTime();
eventSet.EndDate = eventSet.StartDate.AddDays(1).AddSeconds(-1);
}
else if (form.context == "hour")
{
eventSet.StartDate = DateTime.Parse(form.targetDateFrom).ToUniversalTime();
eventSet.EndDate = eventSet.StartDate.AddHours(1).AddSeconds(-1);
}
else if (form.context == "minute" || form.context == "second")
{
eventSet.StartDate = DateTime.Parse(form.targetDateFrom).ToUniversalTime();
eventSet.EndDate = eventSet.StartDate.AddMinutes(1).AddSeconds(-1);
}
else
{
eventSet.StartDate = DateTime.Parse(form.targetDateFrom).ToUniversalTime();
eventSet.EndDate = DateTime.Parse(form.targetDateTo).ToUniversalTime();
form.context = "DateRange";
}
using (AdoDataConnection XDAconnection = new AdoDataConnection("dbOpenXDA"))
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
DataTable table = XDAconnection.RetrieveData(Query, eventSet.StartDate, eventSet.EndDate, form.siteID, form.context);
IEnumerable <ValueList> chartSettings = new TableOperations <ValueList>(connection).QueryRecordsWhere("GroupID = ( SELECT ID FROM ValueListGroup WHERE Name = 'Chart." + Tab + "')");
int groupID = connection.ExecuteScalar <int>($"SELECT ID FROM ValueListGroup WHERE Name = 'Chart.{Tab}'");
// remove disabled columns from set and create settings for columns that do not have settings
foreach (DataColumn column in table.Columns)
{
if (column.ColumnName == "thedate")
{
continue;
}
if (!chartSettings.Any(x => x.Text == column.ColumnName))
{
Random r = new Random(DateTime.UtcNow.Millisecond);
new TableOperations <ValueList>(connection).AddNewRecord(new ValueList
{
Key = 0,
GroupID = groupID,
Text = column.ColumnName,
Flag = false,
AltText1 = "#" + r.Next(256).ToString("X2") + r.Next(256).ToString("X2") + r.Next(256).ToString("X2"),
IsDefault = false,
Hidden = false,
Enabled = true
});
chartSettings = new TableOperations <ValueList>(connection).QueryRecordsWhere("GroupID = ( SELECT ID FROM ValueListGroup WHERE Name = 'Chart." + Tab + "')");
}
ValueList setting = chartSettings.FirstOrDefault(x => x.Text == column.ColumnName);
if (setting != null && setting.Enabled == true)
{
if (eventSet.Types.All(x => x.Name != column.ColumnName))
{
EventSet.EventDetail ed = new EventSet.EventDetail();
ed.Name = column.ColumnName;
ed.Color = setting.AltText1;
eventSet.Types.Add(ed);
}
}
}
foreach (DataRow row in table.Rows)
{
foreach (DataColumn column in table.Columns)
{
if (column.ColumnName == "thedate")
{
continue;
}
if (chartSettings.First(x => x.Text == column.ColumnName).Enabled == true)
{
eventSet.Types[eventSet.Types.IndexOf(x => x.Name == column.ColumnName)].Data.Add(Tuple.Create(Convert.ToDateTime(row["thedate"]), Convert.ToInt32(row[column.ColumnName])));
}
}
}
return(Ok(eventSet));
}
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}