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)); } }