public int QueryHourOfWeekLimitCount(int channelId, string filterText)
{
TableOperations <HourOfWeekLimit> table = DataContext.Table <HourOfWeekLimit>();
RecordRestriction restriction = table.GetSearchRestriction(filterText);
return(table.QueryRecordCount(new RecordRestriction("ChannelID = {0}", channelId) + restriction));
}
public int QueryCompanyCount(string filterText)
{
TableOperations <Company> tableOperations = DataContext.Table <Company>();
RecordRestriction restriction = tableOperations.GetSearchRestriction(filterText);
return(tableOperations.QueryRecordCount(restriction));
}
public IEnumerable <VendorDevice> QueryVendorDevices(string sortField, bool ascending, int page, int pageSize, string filterText)
{
TableOperations <VendorDevice> tableOperations = DataContext.Table <VendorDevice>();
RecordRestriction restriction = tableOperations.GetSearchRestriction(filterText);
return(tableOperations.QueryRecords(sortField, ascending, page, pageSize, restriction));
}
public void SendChannelsWithNormalLimitsToCSV(Stream returnStream, int meterID, string searchString, CompatibleCancellationToken cancellationToken, string sortField = "Name", bool ascending = true)
{
MetersWithNormalLimits meter;
IEnumerable <ChannelsWithNormalLimits> channels;
using (var connection = new AdoDataConnection("systemSettings"))
{
TableOperations <MetersWithNormalLimits> meterTable = new TableOperations <MetersWithNormalLimits>(connection);
TableOperations <ChannelsWithNormalLimits> channelTable = new TableOperations <ChannelsWithNormalLimits>(connection);
meter = meterTable.QueryRecordWhere("ID = {0}", meterID);
RecordRestriction searchRestriction = channelTable.GetSearchRestriction(searchString);
string sortExpression = sortField != null ? sortField + (ascending == true ? " ASC" : " DESC") : null;
channels = channelTable.QueryRecords(sortExpression, new RecordRestriction("MeterID = {0}", meterID) + searchRestriction).ToList();
}
string meterInfo = meter.ToCSV();
using (StreamWriter writer = new StreamWriter(returnStream))
foreach (ChannelsWithNormalLimits limit in channels)
{
writer.WriteLine(meterInfo + "," + limit.ToCSV());
}
}
public void SendChannelsWithHourlyLimitsToCSV(Stream returnStream, int meterID, string searchString, CompatibleCancellationToken cancellationToken, string sortField = "Name", bool ascending = true)
{
MetersWithHourlyLimits meter;
List <ChannelsWithHourlyLimits> channels;
using (var connection = new AdoDataConnection("systemSettings"))
{
TableOperations <MetersWithHourlyLimits> meterTable = new TableOperations <MetersWithHourlyLimits>(connection);
TableOperations <ChannelsWithHourlyLimits> channelTable = new TableOperations <ChannelsWithHourlyLimits>(connection);
meter = meterTable.QueryRecordWhere("ID = {0}", meterID);
string sortExpression = sortField != null ? sortField + (ascending == true ? " ASC" : " DESC") : null;
RecordRestriction searchRestriction = channelTable.GetSearchRestriction(searchString);
RecordRestriction meterRestriction = new RecordRestriction("MeterID = {0}", meterID);
RecordRestriction finalRestriction = searchRestriction + meterRestriction;
channels = channelTable.QueryRecords(sortExpression, finalRestriction).ToList();
}
foreach (var channel in channels)
{
if (cancellationToken.IsCancelled)
{
return;
}
SendHourOfWeekLimitTableToCSV(returnStream, channel.ID, "", cancellationToken);
}
}
public void SendHourOfWeekLimitTableToCSV(Stream returnStream, int channelID, string searchString, CompatibleCancellationToken cancellationToken, string sortField = "HourOfWeek", bool ascending = true)
{
MetersWithHourlyLimits meter;
ChannelsWithHourlyLimits channel;
List <HourOfWeekLimitView> hourlyLimits = new List <HourOfWeekLimitView>();
using (var connection = new AdoDataConnection("systemSettings"))
{
TableOperations <MetersWithHourlyLimits> meterTable = new TableOperations <MetersWithHourlyLimits>(connection);
TableOperations <ChannelsWithHourlyLimits> channelTable = new TableOperations <ChannelsWithHourlyLimits>(connection);
TableOperations <HourOfWeekLimitView> table = new TableOperations <HourOfWeekLimitView>(connection);
channel = channelTable.QueryRecordWhere("ID = {0}", channelID);
meter = meterTable.QueryRecordWhere("ID = {0}", channel.MeterID);
RecordRestriction searchRestriction = table.GetSearchRestriction(searchString);
RecordRestriction channelRestriction = new RecordRestriction("ChannelID = {0}", channel.ID);
RecordRestriction finalRestriction = searchRestriction + channelRestriction;
string sortExpression = sortField != null ? sortField + (ascending == true ? " ASC" : " DESC") : null;
hourlyLimits = table.QueryRecords(sortExpression, finalRestriction).ToList();
}
string meterInfo = meter.ToCSV();
string channelInfo = channel.ToCSV();
using (StreamWriter writer = new StreamWriter(returnStream))
foreach (HourOfWeekLimitView limit in hourlyLimits)
{
writer.WriteLine(meterInfo + "," + channelInfo + "," + limit.ToCSV());
}
}
public IEnumerable <ChannelsWithNormalLimits> QueryChannelsWithNormalLimits(int meterId, string sortField, bool ascending, int page, int pageSize, string filterText)
{
TableOperations <ChannelsWithNormalLimits> table = DataContext.Table <ChannelsWithNormalLimits>();
RecordRestriction restriction = table.GetSearchRestriction(filterText);
return(table.QueryRecords(sortField, ascending, page, pageSize, new RecordRestriction("MeterID = {0}", meterId) + restriction));
}
public int QueryChannelsWithNormalLimitsCount(int meterId, string filterText)
{
TableOperations <ChannelsWithNormalLimits> table = DataContext.Table <ChannelsWithNormalLimits>();
RecordRestriction restriction = table.GetSearchRestriction(filterText);
return(table.QueryRecordCount(new RecordRestriction("MeterID = {0}", meterId) + restriction));
}
public IEnumerable <HourOfWeekLimitView> QueryHourOfWeekLimit(int channelId, string sortField, bool ascending, int page, int pageSize, string filterText)
{
TableOperations <HourOfWeekLimitView> table = DataContext.Table <HourOfWeekLimitView>();
RecordRestriction restriction = table.GetSearchRestriction(filterText);
return(table.QueryRecords(sortField, ascending, page, pageSize, new RecordRestriction("ChannelID = {0}", channelId) + restriction));
}
public int QueryMeasurementCount(string filterText)
{
TableOperations <Measurement> tableOperations = DataContext.Table <Measurement>();
RecordRestriction restriction = tableOperations.GetSearchRestriction(filterText);
return(tableOperations.QueryRecordCount(restriction));
}
public int QueryVendorDeviceCount(string filterText)
{
TableOperations <VendorDevice> tableOperations = DataContext.Table <VendorDevice>();
RecordRestriction restriction = tableOperations.GetSearchRestriction(filterText);
return(tableOperations.QueryRecordCount(restriction));
}
public int QueryIncidentEventCycleDataViewCount(string date, string name, string levels, string limits, string timeContext, string filterString)
{
TableOperations <IncidentEventCycleDataView> table = DataContext.Table <IncidentEventCycleDataView>();
RecordRestriction filterRestriction = table.GetSearchRestriction(filterString);
DateTime startDate = DateTime.ParseExact(date, "yyyyMMddHH", CultureInfo.InvariantCulture);
DateTime endDate = (DateTime)typeof(DateTime).GetMethod("Add" + timeContext).Invoke(startDate, new object[] { 1 });
return(table.QueryRecordCount(filterRestriction + new RecordRestriction("StartTime BETWEEN {0} AND {1}", startDate, endDate) + new RecordRestriction(levels + " = {0}", name)));
}
public Task <DataTable> GetDataFiles([FromBody] GetDataFilesPostData postData, CancellationToken cancellationToken)
{
return(Task.Run(() =>
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
const string QueryFormat =
"WITH cte AS " +
"( " +
" SELECT * " +
" FROM DataFile " +
" WHERE {0} " +
") " +
"SELECT DataFile.* " +
"FROM " +
"( " +
" SELECT " +
" ROW_NUMBER() OVER(ORDER BY {2}) AS RowNumber, " +
" DataFile.* " +
" FROM (SELECT DISTINCT FileGroupID ID FROM cte) FileGroup CROSS APPLY " +
" ( " +
" SELECT TOP 1 * " +
" FROM cte DataFile " +
" WHERE DataFile.FileGroupID = FileGroup.ID " +
" ORDER BY FileSize DESC, FilePath " +
" ) DataFile " +
") DataFile " +
"WHERE {1} " +
"ORDER BY RowNumber";
TableOperations <DataFile> dataFileTable = new TableOperations <DataFile>(connection);
RecordRestriction searchRestriction = dataFileTable.GetSearchRestriction(postData.filterString)
?? new RecordRestriction("1=1");
string searchClause = searchRestriction.FilterExpression;
int paramIndex = searchRestriction.Parameters.Length;
string pageClause = $"RowNumber BETWEEN {{{paramIndex}}} AND {{{paramIndex + 1}}}";
string sortOrder = postData.ascending ? "ASC" : "DESC";
string orderByClause = $"{postData.sortField} {sortOrder}";
string query = string.Format(QueryFormat, searchClause, pageClause, orderByClause);
int pageStart = (postData.page - 1) * postData.pageSize + 1;
int pageEnd = pageStart + postData.pageSize - 1;
object[] parameters = searchRestriction.Parameters
.Concat(new object[] { pageStart, pageEnd })
.ToArray();
return connection
.RetrieveData(query, parameters);
}
}, cancellationToken));
}
public IEnumerable <Device> QueryDevices(Guid nodeID, string sortField, bool ascending, int page, int pageSize, string filterText)
{
TableOperations <Device> deviceTable = DataContext.Table <Device>();
RecordRestriction restriction =
new RecordRestriction("NodeID = {0}", nodeID) +
deviceTable.GetSearchRestriction(filterText);
return(deviceTable.QueryRecords(sortField, ascending, page, pageSize, restriction));
}
public int QueryDeviceCount(Guid nodeID, string filterText)
{
TableOperations <Device> deviceTable = DataContext.Table <Device>();
RecordRestriction restriction =
new RecordRestriction("NodeID = {0}", nodeID) +
deviceTable.GetSearchRestriction(filterText);
return(deviceTable.QueryRecordCount(restriction));
}
public IEnumerable <PhasorDetail> QueryPhasors(int deviceID, string sortField, bool ascending, int page, int pageSize, string filterText)
{
TableOperations <PhasorDetail> phasorDetailTable = DataContext.Table <PhasorDetail>();
RecordRestriction restriction = (deviceID > 0 ?
new RecordRestriction("DeviceID = {0}", deviceID) : null) +
phasorDetailTable.GetSearchRestriction(filterText);
return(DataContext.Table <PhasorDetail>().QueryRecords(sortField, ascending, page, pageSize, restriction));
}
public int QueryPhasorCount(int deviceID, string filterText)
{
TableOperations <PhasorDetail> phasorDetailTable = DataContext.Table <PhasorDetail>();
RecordRestriction restriction = (deviceID > 0 ?
new RecordRestriction("DeviceID = {0}", deviceID) : null) +
phasorDetailTable.GetSearchRestriction(filterText);
return(phasorDetailTable.QueryRecordCount(restriction));
}
public IEnumerable <DeviceGroup> QueryDeviceGroups(Guid nodeID, string sortField, bool ascending, int page, int pageSize, string filterText)
{
TableOperations <DeviceGroup> deviceGroupTable = DataContext.Table <DeviceGroup>();
RecordRestriction restriction =
new RecordRestriction("NodeID = {0} AND ProtocolID = {1} AND AccessID = {2}", nodeID, VirtualProtocolID, DeviceGroup.DefaultAccessID) +
deviceGroupTable.GetSearchRestriction(filterText);
return(deviceGroupTable.QueryRecords(sortField, ascending, page, pageSize, restriction));
}
public int QueryDeviceGroupCount(Guid nodeID, string filterText)
{
TableOperations <DeviceGroup> deviceGroupTable = DataContext.Table <DeviceGroup>();
RecordRestriction restriction =
new RecordRestriction("NodeID = {0} AND ProtocolID = {1} AND AccessID = {2}", nodeID, VirtualProtocolID, DeviceGroup.DefaultAccessID) +
deviceGroupTable.GetSearchRestriction(filterText);
return(deviceGroupTable.QueryRecordCount(restriction));
}
public IEnumerable <Device> QueryEnabledDevices(Guid nodeID, int limit, string filterText)
{
TableOperations <Device> deviceTable = DataContext.Table <Device>();
RecordRestriction restriction =
new RecordRestriction("NodeID = {0}", nodeID) +
new RecordRestriction("Enabled <> 0") +
deviceTable.GetSearchRestriction(filterText);
return(deviceTable.QueryRecords("Acronym", restriction, limit));
}
public IEnumerable <Device> QueryEnabledDevices(Guid nodeID, int limit, string filterText)
{
TableOperations <Device> deviceTable = DataContext.Table <Device>();
RecordRestriction restriction =
new RecordRestriction("NodeID = {0} AND NOT (ProtocolID = {1} AND AccessID = {2})", nodeID, VirtualProtocolID, DeviceGroup.DefaultAccessID) +
new RecordRestriction("Enabled <> 0") +
deviceTable.GetSearchRestriction(filterText);
return(deviceTable.QueryRecords("Acronym", restriction, limit));
}
public int QueryActiveMeasurementCount(string filterText)
{
TableOperations <ActiveMeasurement> tableOperations = DataContext.Table <ActiveMeasurement>();
RecordRestriction restriction = tableOperations.GetSearchRestriction(filterText);
if ((object)restriction == null)
{
restriction = new RecordRestriction("ID LIKE {0}", $"{GetSelectedInstanceName()}:%");
}
else
{
List <object> parameters = new List <object>(restriction.Parameters);
parameters.Add($"{GetSelectedInstanceName()}:%");
restriction = new RecordRestriction($"({restriction.FilterExpression}) AND ID LIKE {{{restriction.Parameters.Length}}}", parameters.ToArray());
}
return(tableOperations.QueryRecordCount(restriction));
}
public IEnumerable <ActiveMeasurement> QueryActiveMeasurements(string sortField, bool ascending, int page, int pageSize, string filterText)
{
TableOperations <ActiveMeasurement> tableOperations = DataContext.Table <ActiveMeasurement>();
RecordRestriction restriction = tableOperations.GetSearchRestriction(filterText);
if ((object)restriction == null)
{
restriction = new RecordRestriction("ID LIKE {0}", $"{GetSelectedInstanceName()}:%");
}
else
{
List <object> parameters = new List <object>(restriction.Parameters);
parameters.Add($"{GetSelectedInstanceName()}:%");
restriction = new RecordRestriction($"({restriction.FilterExpression}) AND ID LIKE {{{restriction.Parameters.Length}}}", parameters.ToArray());
}
return(DataContext.Table <ActiveMeasurement>().QueryRecords(sortField, ascending, page, pageSize, restriction));
}
public Task <int> GetCount([FromBody] GetDataFilesPostData postData, CancellationToken cancellationToken)
{
return(Task.Run(() =>
{
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
const string QueryFormat =
"SELECT COUNT(*) " +
"FROM " +
"( " +
" SELECT DISTINCT FileGroupID " +
" FROM DataFile " +
" WHERE {0} " +
") DataFile";
TableOperations <DataFile> dataFileTable = new TableOperations <DataFile>(connection);
RecordRestriction restriction = dataFileTable.GetSearchRestriction(postData.filterString);
string query = string.Format(QueryFormat, restriction?.FilterExpression ?? "1=1");
return connection.ExecuteScalar <int>(query, restriction?.Parameters ?? new object[0]);
}
}, cancellationToken));
}
public void SendMetersWithNormalLimitsToCSV(Stream returnStream, string searchString, CompatibleCancellationToken cancellationToken, string sortField = "Name", bool ascending = true)
{
List <MetersWithNormalLimits> meters;
using (var connection = new AdoDataConnection("systemSettings"))
{
TableOperations <MetersWithNormalLimits> meterTable = new TableOperations <MetersWithNormalLimits>(connection);
RecordRestriction restriction = meterTable.GetSearchRestriction(searchString);
string sortExpression = sortField != null ? sortField + (ascending == true ? " ASC" : " DESC") : null;
meters = meterTable.QueryRecords(sortExpression, restriction).ToList();
}
foreach (MetersWithNormalLimits meter in meters)
{
if (cancellationToken.IsCancelled)
{
return;
}
SendChannelsWithNormalLimitsToCSV(returnStream, meter.ID, "", cancellationToken);
}
}
public IEnumerable <Device> QueryEnabledDevices(int limit, string filterText)
{
TableOperations <Device> deviceTable = DataContext.Table <Device>();
return(deviceTable.QueryRecords("Acronym", "Enabled <> 0" + deviceTable.GetSearchRestriction(filterText), limit));
}
public IEnumerable <ConnectionProfileTask> QueryConnectionProfileTasks(int parentID, string sortField, bool ascending, int page, int pageSize, string filterText)
{
TableOperations <ConnectionProfileTask> connectionProfileTaskTable = DataContext.Table <ConnectionProfileTask>();
return(connectionProfileTaskTable.QueryRecords(sortField, ascending, page, pageSize, new RecordRestriction("ConnectionProfileID = {0}", parentID) + connectionProfileTaskTable.GetSearchRestriction(filterText)));
}
public int QueryConnectionProfileTaskCount(int parentID, string filterText)
{
TableOperations <ConnectionProfileTask> connectionProfileTaskTable = DataContext.Table <ConnectionProfileTask>();
return(connectionProfileTaskTable.QueryRecordCount(new RecordRestriction("ConnectionProfileID = {0}", parentID) + connectionProfileTaskTable.GetSearchRestriction(filterText)));
}
