public IHttpActionResult GetExternalDB()
{
try
{
if (GetRoles == string.Empty || User.IsInRole(GetRoles))
{
string afTbl = TableOperations <AdditionalField> .GetTableName();
string afvTbl = TableOperations <AdditionalFieldValue> .GetTableName();
using (AdoDataConnection connection = new AdoDataConnection(Connection))
{
string query = $@"SELECT MIN(UpdatedOn) AS lastUpdate, {afTbl}.ExternalDB AS name
FROM
{afTbl} LEFT JOIN {afvTbl} ON {afTbl}.ID = {afvTbl}.AdditionalFieldID
WHERE
{afTbl}.ParentTable = 'DER'
AND {afTbl}.ExternalDB IS NOT NULL AND {afTbl}.ExternalDB <> ''
GROUP BY {afTbl}.ExternalDB";
DataTable table = connection.RetrieveData(query);
return(Ok(table));
}
}
else
{
return(Unauthorized());
}
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
public virtual IHttpActionResult GetOne(string id)
{
if (GetAuthCheck())
{
try
{
T result = null;
PropertyInfo primaryKey = typeof(T).GetProperty(PrimaryKeyField);
if (primaryKey.PropertyType == typeof(int))
{
result = QueryRecordWhere(PrimaryKeyField + " = {0}", int.Parse(id));
}
else if (primaryKey.PropertyType == typeof(Guid))
{
result = QueryRecordWhere(PrimaryKeyField + " = {0}", Guid.Parse(id));
}
else
{
result = QueryRecordWhere(PrimaryKeyField + " = {0}", id);
}
if (result == null)
{
string tableName = TableOperations <T> .GetTableName();
return(BadRequest(string.Format(PrimaryKeyField + " provided does not exist in '{0}'.", tableName)));
}
else
{
return(Ok(result));
}
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
else
{
return(Unauthorized());
}
}
public IHttpActionResult GetUsersForTSC(int tscid)
{
if (GetRoles != string.Empty && !User.IsInRole(GetRoles))
{
return(Unauthorized());
}
try
{
using (AdoDataConnection connection = new AdoDataConnection(Connection))
{
string uaTableName = TableOperations <UserAccount> .GetTableName();
string aufvTableName = TableOperations <AdditionalUserFieldValue> .GetTableName();
string aufTableName = TableOperations <AdditionalUserField> .GetTableName();
string sql = $@"
SELECT
UA.*
FROM
{uaTableName} as UA JOIN
{aufvTableName} as AUFV ON UA.ID = AUFV.UserAccountID JOIN
{aufTableName} as AUF ON AUFV.AdditionalUserFieldID = AUF.ID
WHERE
AUF.FieldName = 'TSC' AND AUFV.Value = {{0}}
";
DataTable table = connection.RetrieveData(sql, tscid);
return(Ok(table));
}
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
public IHttpActionResult GetAssetsForLocation(int locationID)
{
if (GetRoles == string.Empty || User.IsInRole(GetRoles))
{
using (AdoDataConnection connection = new AdoDataConnection(Connection))
{
string assetTableName = TableOperations <Asset> .GetTableName();
string assetTypeTableName = TableOperations <AssetTypes> .GetTableName();
string assetLocationTableName = TableOperations <AssetLocation> .GetTableName();
try
{
DataTable result = connection.RetrieveData($@"
SELECT
a.*,
at.Name as AssetType
FROM
{assetTableName} as a JOIN
{assetTypeTableName} as at ON a.AssetTypeID = at.ID JOIN
{assetLocationTableName} as al ON a.ID = al.AssetID
WHERE
al.LocationID = {{0}}", locationID);
return(Ok(result));
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
}
else
{
return(Unauthorized());
}
}
/// <summary>
/// Gets the <see cref="DataTable"/> with the SearchResults as specified in <see cref="PostData"/>.
/// </summary>
/// <returns>A <see cref="DataTable"/>.</returns>
protected virtual DataTable GetSearchResults(PostData postData)
{
string whereClause = BuildWhereClause(postData.Searches);
object[] param = new object[] { };
if (RootQueryRestriction != null)
{
if (whereClause == "")
{
whereClause = $" WHERE {RootQueryRestriction.FilterExpression}";
}
else
{
whereClause = whereClause + " AND " + RootQueryRestriction.FilterExpression;
}
param = RootQueryRestriction.Parameters.ToArray();
}
using (AdoDataConnection connection = new AdoDataConnection(Connection))
{
string tableName = TableOperations <T> .GetTableName();
string sql = "";
string limit;
if (Take == null)
{
limit = "";
}
else
{
limit = $"TOP {(int)Take}";
}
if (SearchSettings == null && CustomView == String.Empty)
{
sql = $@" SELECT {limit} * FROM {tableName} FullTbl {whereClause}
ORDER BY { postData.OrderBy} {(postData.Ascending ? "ASC" : "DESC")} ";
}
else if (SearchSettings == null)
{
sql = $@" SELECT {limit} * FROM({CustomView}) FullTbl
{whereClause}
ORDER BY {postData.OrderBy} {(postData.Ascending ? "ASC" : "DESC")}";
}
else
{
string pivotCollums = "(" + String.Join(",", postData.Searches.Where(item => item.isPivotColumn).Select(search => "'" + search.FieldName + "'")) + ")";
if (pivotCollums == "()")
{
pivotCollums = "('')";
}
string collumnCondition = SearchSettings.Condition;
if (collumnCondition != String.Empty)
{
collumnCondition = $"({collumnCondition}) AND ";
}
collumnCondition = collumnCondition + $"{SearchSettings.FieldKeyField} IN {pivotCollums}";
string searchSettingConditions = SearchSettings.Condition;
if (searchSettingConditions != String.Empty)
{
searchSettingConditions = "(" + searchSettingConditions + ")";
}
string joinCondition = $"af.FieldName IN {pivotCollums} AND ";
joinCondition = joinCondition + searchSettingConditions;
if (SearchSettings.Condition != String.Empty)
{
joinCondition = $"{joinCondition} AND ";
}
joinCondition = joinCondition + $"SRC.{PrimaryKeyField} = AF.{SearchSettings.PrimaryKeyField}";
string sqlPivotColumns = $@"
SELECT '[AFV_' + [Key] + ']'
FROM (Select DISTINCT {SearchSettings.FieldKeyField} AS [Key] FROM {SearchSettings.AdditionalFieldTable} AS AF WHERE {collumnCondition} ) AS [Fields]";
sqlPivotColumns = string.Join(",", connection.RetrieveData(sqlPivotColumns).Select().Select(r => r[0].ToString()));
string tblSelect = $@"
(SELECT
SRC.*,
'AFV_' + AF.{SearchSettings.FieldKeyField} AS AFFieldKey,
AF.{SearchSettings.ValueField} AS AFValue
FROM {(string.IsNullOrEmpty(CustomView) ? tableName : $"({CustomView})")} SRC LEFT JOIN
{SearchSettings.AdditionalFieldTable} AF ON {joinCondition}
public IHttpActionResult GetMetersUsingSearchableList([FromBody] PostData searches)
{
if (!AllowSearch || (GetRoles != string.Empty && !User.IsInRole(GetRoles)))
{
return(Unauthorized());
}
try
{
string whereClause = BuildWhereClause(searches.Searches);
using (AdoDataConnection connection = new AdoDataConnection(Connection))
{
string addtionalFieldTableName = TableOperations <AdditionalField> .GetTableName();
string addtionalFieldValueTableName = TableOperations <AdditionalFieldValue> .GetTableName();
string meterTableName = TableOperations <Meter> .GetTableName();
string locationTableName = TableOperations <Location> .GetTableName();
string assetTableName = TableOperations <Asset> .GetTableName();
string assetLocationTableName = TableOperations <AssetLocation> .GetTableName();
string view = $@"
SELECT
DISTINCT
l.ID,
l.LocationKey,
l.Name,
COUNT(DISTINCT m.ID) as Meters,
COUNT(DISTINCT al.AssetID) as Assets
FROM
{locationTableName} as l LEFT JOIN
{meterTableName} as m ON l.ID = m.LocationID LEFT JOIN
{assetLocationTableName} as al ON l.ID = al.LocationID LEFT JOIN
{assetTableName} as a ON al.AssetID = a.ID
GROUP BY
l.ID,
l.LocationKey,
l.Name
";
string sql = "";
sql = $@"
DECLARE @PivotColumns NVARCHAR(MAX) = N''
SELECT @PivotColumns = @PivotColumns + '[AFV_' + t.FieldName + '],'
FROM (Select DISTINCT FieldName FROM {addtionalFieldTableName} WHERE ParentTable = 'Location') AS t
DECLARE @SQLStatement NVARCHAR(MAX) = N''
IF @PivotColumns != ''
SET @SQLStatement = N'
SELECT * INTO #Tbl FROM (
SELECT
M.*,
(CONCAT(''AFV_'',af.FieldName)) AS FieldName,
afv.Value
FROM ({view.Replace("'", "''")}) M LEFT JOIN
{addtionalFieldTableName} af on af.ParentTable = ''Location'' LEFT JOIN
{addtionalFieldValueTableName} afv ON m.ID = afv.ParentTableID AND af.ID = afv.AdditionalFieldID
) as T PIVOT (
Max(T.Value) FOR T.FieldName IN ('+ SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')) AS PVT
{whereClause.Replace("'", "''")}
ORDER BY { searches.OrderBy} {(searches.Ascending ? "ASC" : "DESC")};
DECLARE @NoNPivotColumns NVARCHAR(MAX) = N''''
SELECT @NoNPivotColumns = @NoNPivotColumns + ''[''+ name + ''],''
FROM tempdb.sys.columns WHERE object_id = Object_id(''tempdb..#Tbl'') AND name NOT LIKE ''AFV%'';
DECLARE @CleanSQL NVARCHAR(MAX) = N''SELECT '' + SUBSTRING(@NoNPivotColumns,0, LEN(@NoNPivotColumns)) + ''FROM #Tbl''
exec sp_executesql @CleanSQL
'
ELSE
SET @SQLStatement = 'SELECT * FROM ({view.Replace("'", "''")}) T {whereClause.Replace("'", "''")} ORDER BY { searches.OrderBy} {(searches.Ascending ? "ASC" : "DESC")}'
exec sp_executesql @SQLStatement";
DataTable table = connection.RetrieveData(sql, "");
return(Ok(JsonConvert.SerializeObject(table)));
}
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}