public async Task <IActionResult> GetTable(FilterModel[] Filters, OrderDataModel OrderData, int pages)
{
#region Logging
if (Filters != null && Filters.Count() > 0)
{
string parameters = JsonConvert.SerializeObject(new { Filters, OrderData });
sql.LogAsync("Filter Applied", DateTime.Now, User.Identity.Name, parameters);
}
#endregion
StringBuilder WhereClause = new StringBuilder("WHERE ");
#region Initial Column Data
string ColumnNamesQuery = @"SELECT COLUMNS.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'arkiv' AND COLUMN_NAME NOT IN (
SELECT [Column] FROM ColumnBlacklist
)";
IEnumerable <ColumnNameModel> ColumnNames = await sql.SelectDataAsync <ColumnNameModel>(ColumnNamesQuery).ConfigureAwait(false);
List <SelectListItem> ColumnNamesSelectList = ColumnNames.Select(x => new SelectListItem()
{
Value = x.COLUMN_NAME, Text = x.COLUMN_NAME
}).ToList();
#endregion
#region Active Directory account checking
bool ClearWhereFlag = false;
IEnumerable <ActiveModel> ActiveDirectoryGroupModel = await sql.SelectDataAsync <ActiveModel>("SELECT * FROM active").ConfigureAwait(false);
IEnumerable <string> Groups = GetUserGroups();
IEnumerable <string> SortedGroups = (from model in ActiveDirectoryGroupModel where Groups.Any(g => g == model.Group) select model.DEVI);
List <(string, object)> ParamList = new List <(string, object)>();
if (SortedGroups.Count() > 0)
{
WhereClause.Append("(");
for (int i = 0; i < SortedGroups.Count(); i++)
{
if (i < SortedGroups.Count() && i > 0)
{
WhereClause.Append(" OR ");
}
WhereClause.Append("DEVI = @DEVI" + i);
ParamList.Add(("@DEVI" + i, SortedGroups.ElementAt(i)));
}
WhereClause.Append(")");
}
else
{
ClearWhereFlag = true;
}
#endregion
#region Filtering
if (Filters.Count() > 0)
{
if (SortedGroups.Count() != 0)
{
WhereClause.Append(" AND ");
}
(string, (string, object)[])items = await GetFiltersToQueriesAsync(Filters).ConfigureAwait(false);