public ActionResult PostCSV([FromBody] EventSearchPostData postData)
{
using (AdoDataConnection sCConnection = new AdoDataConnection(m_configuration["SystemCenter:ConnectionString"], m_configuration["SystemCenter:DataProviderString"]))
using (AdoDataConnection connection = new AdoDataConnection(m_configuration["OpenXDA:ConnectionString"], m_configuration["OpenXDA:DataProviderString"]))
{
string orgId = (User.Identity as ClaimsIdentity).Claims.FirstOrDefault(c => c.Type == "org_id")?.Value;
DataTable meters = sCConnection.RetrieveData(@"SELECT OpenXDAMeterID FROM CompanyMeter WHERE CompanyID = (SELECT ID FROM Company WHERE CompanyID = {0})", orgId);
if (meters.Rows.Count == 0)
{
return(Ok(new DataTable()));
}
DataTable table = connection.RetrieveData(SQLQuery(postData.Types, meters, postData.Ascending, postData.SortField), postData.StartDate, postData.EndDate, 10000000);
MemoryStream memoryStream = new MemoryStream();
TextWriter writer = new StreamWriter(memoryStream);
writer.WriteLine(string.Join(",", table.Columns.Cast <DataColumn>().Select(x => x.ColumnName)));
foreach (DataRow row in table.Rows)
{
writer.WriteLine(string.Join(",", table.Columns.Cast <DataColumn>().Select(x => row[x.ColumnName].ToString())));
}
return(Ok(new FileStreamResult(memoryStream, "text/csv")
{
FileDownloadName = "EventSearch.csv"
}));
}
}
public ActionResult Post([FromBody] EventSearchPostData postData)
{
using (AdoDataConnection sCConnection = new AdoDataConnection(m_configuration["SystemCenter:ConnectionString"], m_configuration["SystemCenter:DataProviderString"]))
using (AdoDataConnection connection = new AdoDataConnection(m_configuration["OpenXDA:ConnectionString"], m_configuration["OpenXDA:DataProviderString"]))
{
string orgId = (User.Identity as ClaimsIdentity).Claims.FirstOrDefault(c => c.Type == "org_id")?.Value;
DataTable meters = sCConnection.RetrieveData(@"SELECT OpenXDAMeterID FROM CompanyMeter WHERE CompanyID = (SELECT ID FROM Company WHERE CompanyID = {0})", orgId);
if (meters.Rows.Count == 0)
{
return(Ok(new DataTable()));
}
return(Ok(connection.RetrieveData(SQLQuery(postData.Types, meters, postData.Ascending, postData.SortField), postData.StartDate, postData.EndDate, postData.Count)));
}
}
public DataTable GetEventSearchData(EventSearchPostData postData)
{
using (AdoDataConnection connection = new AdoDataConnection(SettingsCategory))
{
DateTime dateTime = DateTime.ParseExact(postData.date + " " + postData.time, "MM/dd/yyyy HH:mm:ss.fff", new CultureInfo("en-US"));
string timeWindowUnits = ((TimeWindowUnits)postData.timeWindowUnits).GetDescription();
List <string> eventTypes = new List <string>();
if (postData.faults)
{
eventTypes.Add(" ((EventType.Name = 'Fault' AND (SELECT COUNT(*) FROM BreakerOperation WHERE BreakerOperation.EventID = Event.ID) = 0) OR (EventType.Name = 'RecloseIntoFault'))");
}
if (postData.breakerOps)
{
eventTypes.Add("((EventType.Name = 'Fault' AND (SELECT COUNT(*) FROM BreakerOperation WHERE BreakerOperation.EventID = Event.ID) > 0) OR (EventType.Name = 'BreakerOpen'))");
}
if (postData.sags)
{
eventTypes.Add("EventType.Name = 'Sag'");
}
if (postData.swells)
{
eventTypes.Add("EventType.Name = 'Swell'");
}
if (postData.interruptions)
{
eventTypes.Add("EventType.Name = 'Interruption'");
}
if (postData.transients)
{
eventTypes.Add("EventType.Name = 'Transient'");
}
if (postData.others)
{
eventTypes.Add("EventType.Name = 'Other'");
}
if (postData.relayTCE)
{
eventTypes.Add("(SELECT COUNT(RelayPerformance.ID) FROM RelayPerformance WHERE RelayPerformance.EventID = Event.ID) > 0");
}
if (!eventTypes.Any())
{
eventTypes.Add("EventType.Name = ''");
}
string eventTypeRestriction = $"({string.Join(" OR ", eventTypes)})";
List <string> voltageClasses = new List <string>();
if (postData.g200)
{
voltageClasses.Add(" Asset.VoltageKV > 200 ");
}
if (postData.one00to200)
{
voltageClasses.Add(" (Asset.VoltageKV > 100 AND Asset.VoltageKV <= 200) ");
}
if (postData.thirty5to100)
{
voltageClasses.Add(" (Asset.VoltageKV > 35 AND Asset.VoltageKV <= 100) ");
}
if (postData.oneTo35)
{
voltageClasses.Add(" (Asset.VoltageKV > 1 AND Asset.VoltageKV <= 35) ");
}
if (postData.l1)
{
voltageClasses.Add(" Asset.VoltageKV <= 1 ");
}
if (!voltageClasses.Any())
{
voltageClasses.Add(" Asset.VoltageKV = -1234567 ");
}
string voltageClassRestriction = $"({string.Join(" OR ", voltageClasses)})";
List <string> assetTypes = new List <string>();
if (postData.line)
{
assetTypes.Add(" AssetType.Name = 'Line'");
}
if (postData.bus)
{
assetTypes.Add(" AssetType.Name = 'Bus'");
}
if (postData.transformer)
{
assetTypes.Add(" AssetType.Name = 'Transformer'");
}
if (postData.breaker)
{
assetTypes.Add(" AssetType.Name = 'Breaker'");
}
if (postData.capacitorBank)
{
assetTypes.Add(" AssetType.Name = 'CapacitorBank'");
}
if (!assetTypes.Any())
{
assetTypes.Add(" AssetType.Name = 'None'");
}
string assetTypesRestriction = $"({string.Join(" OR ", assetTypes)})";
List <string> meterType = new List <string>();
if (postData.dfr)
{
meterType.Add(" (SELECT COUNT(AssetID) FROM MeterAsset as ml WHERE event.MeterID = ml.MeterID) > 1 ");
}
if (postData.pqMeter)
{
meterType.Add(" (SELECT COUNT(AssetID) FROM MeterAsset as ml WHERE event.MeterID = ml.MeterID) = 1 ");
}
if (!meterType.Any())
{
meterType.Add(" (SELECT COUNT(AssetID) FROM MeterAsset as ml WHERE event.MeterID = ml.MeterID) < 1 ");
}
string meterTypeRestriction = $"({string.Join(" OR ", meterType)})";
string meterMakeRestriction = $"";
if (postData.make != "All" && postData.model != "All")
{
meterMakeRestriction = $" AND Meter.Make = '{postData.make}' AND Meter.Model = '{postData.model}' ";
}
else if (postData.make != "All")
{
meterMakeRestriction = $" AND Meter.Make = '{postData.make}' ";
}
string query = @"
SELECT
TOP 100
Event.ID as EventID,
Asset.AssetKey as AssetName,
AssetType.Name as AssetType,
Asset.VoltageKV as VoltageClass,
EventType.Name as EventType,
Event.StartTime as FileStartTime,
(SELECT COUNT(*) FROM BreakerOperation WHERE BreakerOperation.EventID = Event.ID) as BreakerOperation,
(SELECT COUNT(Channel.ID) FROM Channel LEFT JOIN MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID WHERE MeasurementType.Name = 'TripCoilCurrent' AND Channel.AssetID = Asset.ID ) as TripCoilCount
FROM
Event JOIN
EventType ON Event.EventTypeID = EventType.ID JOIN
Asset ON Event.AssetID = Asset.ID JOIN
Meter ON Event.MeterID = Meter.ID JOIN
AssetType ON Asset.AssetTypeID = AssetType.ID
WHERE
Event.StartTime BETWEEN DATEADD(" + timeWindowUnits + @", " + (-1 * postData.windowSize).ToString() + @", {0}) AND
DATEADD(" + timeWindowUnits + @", " + (postData.windowSize).ToString() + @", {0}) AND
" + eventTypeRestriction + @" AND
" + voltageClassRestriction + @" AND
" + assetTypesRestriction + @" AND
" + meterTypeRestriction + meterMakeRestriction + @"
";
DataTable table = connection.RetrieveData(query, dateTime);
return(table);
}
}
