/// <summary>
/// Returns a Signal Property set if signalID is set or a specific property if signalPropertyID is set.
/// An empty Signal Property will be returned if neither are set.
/// </summary>
/// <returns></returns>
public SignalPropertiesModels Get(string signalID = null, string signalPropertyID = null)
{
SignalPropertiesModels returnObjs = new SignalPropertiesModels();
string query = string.Empty;
if (!string.IsNullOrWhiteSpace(signalPropertyID))
{
query = "EXEC rGetSignalProperty @SignalPropertyID = '" + signalPropertyID + "'";
}
else if (!string.IsNullOrWhiteSpace(signalID))
{
query = "EXEC rGetSignalProperties @SignalID = '" + signalID + "'";
}
else
{
return(returnObjs);
}
using (DataTable tblData = sqlObject.QuerySQL(query, ref sqlStatus))
{
if (tblData != null)
{
foreach (DataRow item in tblData.Rows)
{
SignalPropertyModels newObj = new SignalPropertyModels();
newObj.DisplayName = item["DisplayName"].ToString();
newObj.PropertyName = item["PropertyName"].ToString();
newObj.PropertyValue = item["PropertyValue"].ToString();
newObj.SignalPropertyID = item["SignalPropertyID"].ToString();
}
}
}
return(returnObjs);
}
/// <summary>
/// Get properties the are associated with a specific object as a parent.
/// objectTypeID is optional.
/// </summary>
/// <param name="objectID"></param>
/// <param name="objectTypeID"></param>
/// <returns></returns>
public Object Get([FromUri] string objectID = null, [FromUri] string objectTypeID = null)
{
ObjectAssociatedPropertiesModels returnObj = new ObjectAssociatedPropertiesModels();
string propertyFilter = string.Empty;
if (!string.IsNullOrWhiteSpace(objectTypeID))
{
propertyFilter = ",@ObjectTypeID ='" + objectTypeID + "'";
}
string query = "EXEC rGetObjectAssociatedProperties @ObjectID ='" + objectID + "'" + propertyFilter;
using (DataTable tblData = sqlObject.QuerySQL(query, ref sqlStatus))
{
if (tblData != null)
{
foreach (DataRow item in tblData.Rows)
{
ObjectAssociatedPropertyModel newObj = new ObjectAssociatedPropertyModel();
newObj.DisplayName = item["DisplayName"].ToString();
newObj.ObjectID = item["ObjectID"].ToString();
newObj.ObjectTypeID = item["ObjectTypeID"].ToString();
newObj.ObjectTypeText = item["ObjectTypeText"].ToString();
newObj.ObjectTypeValue = item["ObjectTypeValue"].ToString();
returnObj.RadiusObjectAssociatedProperties.Add(newObj);
}
}
}
return(returnObj);
}
/// <summary>
/// Inserts a new Log. Excluding the timestamp will use the insert date.
/// </summary>
/// <param name="source"></param>
/// <param name="typeID"></param>
/// <param name="message"></param>
/// <param name="timeStamp"></param>
public HttpResponseMessage Put([FromUri] string source, [FromUri] string typeID, [FromUri] string message, [FromUri] string timeStamp = "")
{
string query;
if (string.IsNullOrWhiteSpace(timeStamp))
{
query = "INSERT INTO dataTblLogs (Source,Type,Message) VALUES ('" + source + "','" + typeID + "','" + message + "')";
}
else
{
query = "INSERT INTO dataTblLogs (Source,Type,Message,TimeStamp) VALUES ('" + source + "','" + typeID + "','" + message + "','" + timeStamp + "')";
}
SQL_Access sqlObject = new SQL_Access();
sqlObject.QuerySQL(query, ref sqlStatus);
HttpResponseMessage response;
if (sqlStatus == "Success")
{
response = Request.CreateResponse(HttpStatusCode.OK, sqlStatus);
}
else
{
response = Request.CreateResponse(HttpStatusCode.BadRequest, sqlStatus);
}
response.Content = new StringContent(sqlStatus, Encoding.Unicode);
response.Headers.CacheControl = new CacheControlHeaderValue()
{
MaxAge = TimeSpan.FromMinutes(20)
};
return(response);
}
/// <summary>
/// Gets the Values and Text representation for the Type specified.
/// </summary>
/// <param name="typeID">Type Specified</param>
/// <returns></returns>
public DataTable Get(string typeID)
{
string query = "SELECT ID,Value,Text FROM cfgTblIdentifiers WHERE ID_Type = '" + typeID + "' ORDER BY Value";
SQL_Access returnTable = new SQL_Access();
return(returnTable.QuerySQL(query, ref sqlStatus));
}
/// <summary>
/// Returns a list of available Objects
/// </summary>
/// <returns></returns>
public ObjectModels Get([FromUri] string objectTypeID = null)
{
string query = "EXEC rGetObjects";
if (objectTypeID != null)
{
query = "EXEC rGetObjects @ObjectTypeID='" + objectTypeID + "'";
}
ObjectModels returnObjs = new ObjectModels();
using (DataTable tblData = sqlObject.QuerySQL(query, ref sqlStatus))
{
if (tblData != null)
{
foreach (DataRow item in tblData.Rows)
{
ObjectModel newObj = new ObjectModel();
newObj.DisplayName = item["DisplayName"].ToString();
newObj.ObjectID = item["ObjectID"].ToString();
newObj.ObjectTypeID = item["ObjectTypeID"].ToString();
newObj.ObjectTypeText = item["ObjectTypeText"].ToString();
newObj.ObjectTypeValue = item["ObjectTypeValue"].ToString();
returnObjs.RadiusObjects.Add(newObj);
}
}
}
return(returnObjs);
}
/// <summary>
/// Returns Returns a list of properties if all parameters are null,
/// Properties associated with an Object,
/// Properties associated with the Property Name.
/// </summary>
/// <param name="objectID"></param>
/// <param name="objectPropertyID"></param>
/// <param name="propertyName"></param>
/// <returns></returns>
public Object Get([FromUri] string objectID = null, [FromUri] string objectPropertyID = null, [FromUri] string propertyName = null)
{
string query = "SELECT Property FROM cfgTblObjectProperties GROUP BY Property ORDER BY Property";
if (string.IsNullOrWhiteSpace(objectID) &&
string.IsNullOrWhiteSpace(objectPropertyID))
{
return(sqlObject.QuerySQL(query, ref sqlStatus));
}
ObjectPropertiesModels returnObj = new ObjectPropertiesModels();
string propertyFilter = string.Empty;
if (!string.IsNullOrWhiteSpace(objectPropertyID))
{
propertyFilter = ", @ObjectPropertyID='" + objectPropertyID + "'";
}
if (!string.IsNullOrWhiteSpace(propertyName))
{
propertyFilter = ", @PropertyName='" + propertyName + "'";
}
query = "EXEC rGetObjectProperties @ObjectID = '" + objectID + "'" + propertyFilter;
using (DataTable tblData = sqlObject.QuerySQL(query, ref sqlStatus))
{
if (tblData != null)
{
foreach (DataRow item in tblData.Rows)
{
ObjectPropertyModel newObj = new ObjectPropertyModel();
newObj.DataTypeID = item["DataTypeID"].ToString();
newObj.DataTypeText = item["DataTypeText"].ToString();
newObj.DataTypeValue = item["DataTypeValue"].ToString();
newObj.Value = item["Value"].ToString();
newObj.DisplayName = item["DisplayName"].ToString();
newObj.PropertyID = item["PropertyID"].ToString();
newObj.ObjectID = objectID;
newObj.PropertyName = item["PropertyName"].ToString();
newObj.WriteSecurityLevel = item["WriteSecurityLevel"].ToString();
returnObj.RadiusObjectProperties.Add(newObj);
}
}
}
return(returnObj);
}
/// <summary>
/// Writes a value to the signal
/// </summary>
/// <param name="writeValue"></param>
/// <returns></returns>
public HttpResponseMessage Put([FromUri] SignalWriteModel writeValue)
{
string comments = "NULL";
if (!string.IsNullOrWhiteSpace(writeValue.Comments))
{
comments = "'" + writeValue.Comments.Replace("'", "''") + "'";
}
string source = "NULL";
if (!string.IsNullOrWhiteSpace(writeValue.Source))
{
source = "'" + writeValue.Source + "'";
}
string query = "EXEC rSetOPC_SigWrite @UserID = '" + writeValue.UserName + "'" +
",@SignalID = '" + writeValue.SignalID + "'" +
",@Value = '" + writeValue.Value + "'" +
",@Comment = " + comments +
",@Source = " + source;
sqlObject.QuerySQL(query, ref sqlStatus);
HttpResponseMessage response;
if (sqlStatus == "Success")
{
response = Request.CreateResponse(HttpStatusCode.OK, sqlStatus);
}
else
{
response = Request.CreateResponse(HttpStatusCode.BadRequest, sqlStatus);
}
response.Content = new StringContent(sqlStatus, Encoding.Unicode);
response.Headers.CacheControl = new CacheControlHeaderValue()
{
MaxAge = TimeSpan.FromMinutes(20)
};
return(response);
}
/// <summary>
/// Inserts a new value.
/// </summary>
/// <param name="typeID">Type Specified</param>
/// <param name="value">Value to add</param>
/// <param name="text">Text representation of the Value</param>
public HttpResponseMessage Put([FromUri] string typeID, [FromUri] string value, [FromUri] string text)
{
string query = "INSERT INTO cfgTblIdentifiers (ID_Type,Value,Text) VALUES ('" + typeID +
"','" + value + "','" + text + "')";
SQL_Access sqlObject = new SQL_Access();
sqlObject.QuerySQL(query, ref sqlStatus);
HttpResponseMessage response;
if (sqlStatus == "Success")
{
response = Request.CreateResponse(HttpStatusCode.OK, sqlStatus);
}
else
{
response = Request.CreateResponse(HttpStatusCode.BadRequest, sqlStatus);
}
response.Content = new StringContent(sqlStatus, Encoding.Unicode);
response.Headers.CacheControl = new CacheControlHeaderValue()
{
MaxAge = TimeSpan.FromMinutes(20)
};
return(response);
}
/// <summary>
/// Deletes a Value and Text representation.
/// </summary>
/// <param name="typeID">Type Specified</param>
/// <param name="value">Value to delete</param>
public HttpResponseMessage Delete([FromUri] string typeID, [FromUri] string value)
{
string query = "DELETE FROM cfgTblIdentifiers WHERE ID_Type = '" + typeID +
"' AND Value = '" + value + "'";
SQL_Access sqlObject = new SQL_Access();
sqlObject.QuerySQL(query, ref sqlStatus);
HttpResponseMessage response;
if (sqlStatus == "Success")
{
response = Request.CreateResponse(HttpStatusCode.OK, sqlStatus);
}
else
{
response = Request.CreateResponse(HttpStatusCode.BadRequest, sqlStatus);
}
response.Content = new StringContent(sqlStatus, Encoding.Unicode);
response.Headers.CacheControl = new CacheControlHeaderValue()
{
MaxAge = TimeSpan.FromMinutes(20)
};
return(response);
}
/// <summary>
/// Returns the Views for the ID and/or group type.
/// TODO: Implement Username
/// </summary>
/// <param name="deviceID"></param>
/// <param name="groupType"></param>
/// <returns></returns>
public SignalViewModels Get([FromUri] string deviceID, [FromUri] string groupType = null)
{
SignalViewModels returnObjs = new SignalViewModels();
//returnObjs.UserName = RequestContext.Principal.Identity.Name;
string filter = string.Empty;
if (!string.IsNullOrWhiteSpace(groupType))
{
filter = ", @TypeIDByValue = '" + groupType + "'";
}
string query = "EXEC rGetSignalViewByDevice @DeviceID = '" + deviceID + "'" + filter;
using (DataTable tblData = sqlObject.QuerySQL(query, ref sqlStatus))
{
if (tblData != null && tblData.Rows.Count > 0)
{
foreach (DataRow item in tblData.Rows)
{
SignalViewModel newObj = new SignalViewModel();
newObj.SignalID = item["SignalID"].ToString();
newObj.InputSource = item["InputSource"].ToString();
newObj.OutputSource = item["OutputSource"].ToString();
newObj.TagName = item["TagName"].ToString();
newObj.DisplayName = item["DisplayName"].ToString();
newObj.ViewDisplayName = item["ViewDisplayName"].ToString();
newObj.TimeStamp = item["TimeStamp"].ToString();
newObj.RawValue = item["RawValue"].ToString();
newObj.Source = item["Source"].ToString();
newObj.Enabled = item["Enabled"].ToString();
newObj.ItemDataType = item["ItemDataType"].ToString();
newObj.CommChannel = item["CommChannel"].ToString();
newObj.EnableScaling = item["EnableScaling"].ToString();
newObj.EU_Min = item["EU_Min"].ToString();
newObj.EU_Max = item["EU_Max"].ToString();
newObj.RawMin = item["RawMin"].ToString();
newObj.RawMax = item["RawMax"].ToString();
newObj.MinValue = item["MinValue"].ToString();
newObj.MaxValue = item["MaxValue"].ToString();
newObj.Precision = item["Precision"].ToString();
newObj.Quality = item["Quality"].ToString();
newObj.Units = item["Units"].ToString();
newObj.WriteSecurityLevel = item["WriteSecurityLevel"].ToString();
newObj.EnumLookupValue = item["EnumLookupValue"].ToString();
if (!string.IsNullOrWhiteSpace(newObj.EnumLookupValue))
{
query = "SELECT Value, Text FROM cfgTblIdentifiers WHERE ID_Type = '" + newObj.EnumLookupValue + "'";
DataTable tblEnums = sqlObject.QuerySQL(query, ref sqlStatus);
if (tblEnums != null && tblEnums.Rows.Count > 0)
{
foreach (DataRow dRow in tblEnums.Rows)
{
newObj.EnumValues.Add(new EnumVal(dRow["Value"].ToString().ToLower(), dRow["Text"].ToString()));
}
}
}
returnObjs.SignalViews.Add(newObj);
}
}
}
return(returnObjs);
}
/// <summary>
/// Returns the Source and Type list if all parameters are blank or the logs according to source and/or type.
/// Excluded values indicates all.
/// </summary>
/// <param name="source"></param>
/// <param name="typeID"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="useTop"></param>
/// <returns></returns>
public object Get([FromUri] string source = "",
[FromUri] string typeID = "",
[FromUri] string startDate = "",
[FromUri] string endDate = "",
[FromUri] string useTop = "")
{
string query = string.Empty;
SQL_Access returnTable;
//Check if all are blank
if (string.IsNullOrWhiteSpace(source) &&
string.IsNullOrWhiteSpace(typeID) &&
string.IsNullOrWhiteSpace(startDate) &&
string.IsNullOrWhiteSpace(endDate) &&
string.IsNullOrWhiteSpace(useTop))
{
List <DataTable> tblData = new List <DataTable>();
query = "SELECT Source FROM dataTblLogs GROUP BY Source ORDER BY Source";
returnTable = new SQL_Access();
tblData.Add(returnTable.QuerySQL(query, ref sqlStatus));
query = "SELECT Type FROM dataTblLogs GROUP BY Type ORDER BY Type";
tblData.Add(returnTable.QuerySQL(query, ref sqlStatus));
return(tblData);
}
string filter = string.Empty;
// Check source filter
if (!string.IsNullOrWhiteSpace(source))
{
filter = " WHERE Source = '" + source + "'";
}
// Check typeID filter
if (!string.IsNullOrWhiteSpace(typeID))
{
if (string.IsNullOrWhiteSpace(filter))
{
filter = " WHERE Type = '" + typeID + "'";
}
else
{
filter += " AND Type = '" + typeID + "'";
}
}
// Check startDate filter
if (!string.IsNullOrWhiteSpace(startDate))
{
if (string.IsNullOrWhiteSpace(filter))
{
filter = " WHERE TimeStamp >= '" + startDate + "'";
}
else
{
filter += " AND TimeStamp >= '" + startDate + "'";
}
}
// Check endDate filter
if (!string.IsNullOrWhiteSpace(endDate))
{
if (string.IsNullOrWhiteSpace(filter))
{
filter = " WHERE TimeStamp <= '" + endDate + "'";
}
else
{
filter += " AND TimeStamp <= '" + endDate + "'";
}
}
if (int.TryParse(useTop, out int topResult) && topResult > 0)
{
query = "SELECT TOP " + topResult.ToString() + " Source,Type,Message FROM dataTblLogs" + filter + " ORDER BY TimeStamp DESC";
}
else
{
if (string.IsNullOrWhiteSpace(filter))
{
query = "SELECT 'Error in Request' AS 'Error'";
}
else
{
query = "SELECT Source,Type,Message FROM dataTblLogs" + filter + " ORDER BY TimeStamp DESC";
}
}
returnTable = new SQL_Access();
return(returnTable.QuerySQL(query, ref sqlStatus));
}
/// <summary>
/// Returns a list of available Signals
/// </summary>
/// <returns></returns>
public SignalModels Get()
{
string query = "EXEC rGetSignals";
SignalModels returnObjs = new SignalModels();
using (DataTable tblData = sqlObject.QuerySQL(query, ref sqlStatus))
{
if (tblData != null)
{
foreach (DataRow item in tblData.Rows)
{
SignalModel newObj = new SignalModel();
newObj.DataTypeID = item["DataTypeID"].ToString();
newObj.DeviceID = item["DeviceID"].ToString();
newObj.DataTypeText = item["DataTypeText"].ToString();
newObj.DataTypeValue = item["DataTypeValue"].ToString();
newObj.DisplayName = item["DisplayName"].ToString();
newObj.Enabled = item["Enabled"].ToString();
newObj.RawValue = item["RawValue"].ToString();
newObj.SignalID = item["SignalID"].ToString();
newObj.SourceID = item["SourceID"].ToString();
newObj.SourceText = item["SourceText"].ToString();
newObj.SourceValue = item["SourceValue"].ToString();
newObj.TagName = item["TagName"].ToString();
newObj.TimeStamp = item["TimeStamp"].ToString();
returnObjs.Signals.Add(newObj);
}
}
}
return(returnObjs);
}
/// <summary>
/// Returns the types of identifiers
/// </summary>
/// <returns></returns>
public DataTable Get()
{
string query = "SELECT ID_Type AS Type FROM cfgTblIdentifiers GROUP BY ID_Type ORDER BY ID_Type";
return(returnTable.QuerySQL(query, ref sqlStatus));
}
/// <summary>
/// Returns the requested history based on the parameters.
/// If nothing is passed, a list of available Tag Names is returned.
/// </summary>
/// <param name="historyRequest"></param>
/// <returns></returns>
public object Get([FromUri] SignalHistoryRequestModel historyRequest)
{
string query = string.Empty;
if (historyRequest == null)
{
query = "SELECT TagName FROM dataTblSignalHistory GROUP BY TagName";
return(sqlObject.QuerySQL(query, ref sqlStatus));
}
SignalHistoryModel returnObjs = new SignalHistoryModel();
string filter = string.Empty;
//Select statement
if (!string.IsNullOrWhiteSpace(historyRequest.SelectTop))
{
query = "SELECT TOP " + historyRequest.SelectTop + " ";
}
else
{
query = "SELECT ";
}
//Filters
if (!string.IsNullOrWhiteSpace(historyRequest.StartDate))
{
filter = " WHERE TimeStamp >= '" + historyRequest.StartDate + "'";
}
if (!string.IsNullOrWhiteSpace(historyRequest.EndDate))
{
if (string.IsNullOrWhiteSpace(filter))
{
filter = " WHERE TimeStamp <= '" + historyRequest.EndDate + "'";
}
else
{
filter += " AND TimeStamp <= '" + historyRequest.EndDate + "'";
}
}
if (!string.IsNullOrWhiteSpace(historyRequest.SignalID))
{
if (string.IsNullOrWhiteSpace(filter))
{
filter = " WHERE SignalID = '" + historyRequest.SignalID + "'";
}
else
{
filter += " AND SignalID = '" + historyRequest.SignalID + "'";
}
}
if (!string.IsNullOrWhiteSpace(historyRequest.TagName))
{
if (string.IsNullOrWhiteSpace(filter))
{
filter = " WHERE TagName = '" + historyRequest.TagName + "'";
}
else
{
filter += " AND TagName = '" + historyRequest.TagName + "'";
}
}
// If no filters applied return blank object
if (string.IsNullOrWhiteSpace(filter))
{
query = "SELECT TagName FROM dataTblSignalHistory GROUP BY TagName";
return(sqlObject.QuerySQL(query, ref sqlStatus));
}
query += "InsertTime,TimeStamp,SignalID,TagName,TimeStamp,Value FROM dataTblSignalHistory" + filter;
using (DataTable tblData = sqlObject.QuerySQL(query, ref sqlStatus))
{
if (tblData != null)
{
foreach (DataRow item in tblData.Rows)
{
SignalHistoryPointModel newObj = new SignalHistoryPointModel();
newObj.InsertTimeStamp = item["InsertTime"].ToString();
newObj.SignalID = item["SignalID"].ToString();
newObj.SignalTimeStamp = item["TimeStamp"].ToString();
newObj.TagName = item["TagName"].ToString();
newObj.Value = item["Value"].ToString();
returnObjs.SignalHistory.Add(newObj);
}
}
}
return(returnObjs);
}
