/// <summary>
/// Returns an SQL query as a string, where this query only returns the data which the user has permissions to view.
/// </summary>
/// <param name="fields"></param>
/// <param name="permissions"></param>
/// <returns></returns>
public string BuildQueryFromPermissions(List <GetPortalPortalDataViewResult> fields, List <GetFilteredDataResult> permissions)
{
var groupedFieldsByTable = fields.OrderBy(f => f.TableOrder).GroupBy(x => x.TableOrder);
using (var context = new OrgSys2017DataContext())
{
var userId = context.GetUserIDSession(Token).SingleOrDefault()?.UserID;
foreach (var group in groupedFieldsByTable)
{
foreach (var field in group)
{
if (!field.IsPresented)
{
continue; //skips fields that are not for display
}
if (field.IsEncrypted)
{
SelectColumnList.Add($"fn_DecryptString({field.TableName}.{field.ColumnName}) AS {field.ColumnAlias}");
}
else
{
SelectColumnList.Add($"{field.TableName}.{field.ColumnName} AS {field.ColumnAlias}");
}
}
var item = group.First();
if (item.TableName != "Claims" && item.TableName != "User_Profiles" && item.TableName != "Claim_Documents")
{
//PKName and PKTable are coming from Table_Order table
JoinTableList.Add($"LEFT JOIN {item.TableName} ON {item.PKTable}.{item.PKName} = {item.TableName}.{item.FKName}");
}
}
foreach (var filter in permissions)
{
object value = ResolveFilterValue(filter, context, Token);
WhereClausePermissionList.Add($"{filter.TableName}.{filter.ColumnName} {filter.Operator} {value}");
}
}
var query = $" SELECT DISTINCT {string.Join(", ", SelectColumnList)} FROM {TableName} {string.Join(" ", JoinTableList)} WHERE ";
if (WhereClauseQueryList.Count > 0 && permissions.Count > 0)
{
query += string.Join(" AND ", WhereClauseQueryList) + " AND "; //add WHEREs that are part of the query itself, not permissions
}
else if (WhereClauseQueryList.Count > 0)
{
query += $" {string.Join(" AND ", WhereClauseQueryList)} ;";
}
if (permissions.Count > 0)
{
query += $" ({string.Join(" OR ", WhereClausePermissionList)}) ;";
}
return(query);
}
/// <summary>
/// Returns an SQL query as a string, where this query only returns the data which the user has permissions to view.
/// </summary>
/// <param name="fields"></param>
/// <param name="permissions"></param>
/// <returns></returns>
public string BuildQuery(List <GetPortalPortalDataViewResult> fields, List <GetFilteredDataResult> permissions)
{
var groupedFieldsByTable = fields.OrderBy(f => f.TableOrder).GroupBy(x => x.TableOrder);
int?userId;
using (var context = new OrgSys2017DataContext())
{
userId = context.GetUserIDSession(Token).SingleOrDefault().UserID;
}
//these JOIN or WHERE statements need the values passed to the, not to be done through db
if (TableName.StartsWith("OSI_New"))
{
SelectColumnList.Add(" OSI_New.os_claims.id AS ClaimID ");
SelectColumnList.Add(" OSI_New.os_claims.ClaimType AS Description ");
WhereClauseQueryList.Add($" OSI_New.os_employees.CompanyID = {ImportID} ");
}
else
{
//this portion is only for creating document views at the moment
JoinTableList.Add($" LEFT JOIN [Session] ON [Session].SessionToken = '{Token}' ");
JoinTableList.Add($" LEFT JOIN [User_Profiles] ON [Claim_Documents].UserID = [User_Profiles].UserID ");
JoinTableList.Add($" LEFT JOIN [Client] ON [User_Profiles].ClientID = [Client].ClientID ");
WhereClauseQueryList.Add($" Client.ClientID = [Session].ClientID ");
}
foreach (var group in groupedFieldsByTable)
{
foreach (var field in group)
{
if (!field.IsPresented)
{
continue; //skips fields that are not for display
}
if (field.IsEncrypted)
{
SelectColumnList.Add($"fn_DecryptString({field.TableName}.{field.ColumnName}) AS {field.ColumnAlias}");
}
else
{
SelectColumnList.Add($"{field.TableName}.{field.ColumnName} AS {field.ColumnAlias}");
}
}
var item = group.First();
if (item.TableName != "OSI_New.os_employees" && item.TableName != "User_Profiles" && item.TableName != "Claim_Documents")
{
//PKName and PKTable are coming from Table_Order table
JoinTableList.Add($"LEFT JOIN {item.TableName} ON {item.PKTable}.{item.PKName} = {item.TableName}.{item.FKName}");
}
}
foreach (var filter in permissions)
{
object value;
switch (filter.FilterValue) //substitutes current user id when needed, allows for dynamic query
{
case "UserID":
value = userId;
break;
case "OrgsysUserID":
value = OrgsysEmployeeID;
break;
default:
value = filter.FilterValue;
break;
}
bool isFilterColumn = filter.isFilterValueColumn.Value;
if (isFilterColumn)//If the filter is an actual column in the built query
{
WhereClausePermissionList.Add($"{filter.TableName}.{filter.ColumnName} {filter.Operator} {value}");
}
else
{
WhereClausePermissionList.Add($"{filter.TableName}.{filter.ColumnName} {filter.Operator} '{value}'");
}
}
var query = $" SELECT {string.Join(", ", SelectColumnList)} FROM {TableName} {string.Join(" ", JoinTableList)} WHERE ";
if (WhereClauseQueryList.Count > 0 && permissions.Count > 0)
{
query += string.Join(" AND ", WhereClauseQueryList) + " AND "; //add WHEREs that are part of the query itself, not permissions
}
else if (WhereClauseQueryList.Count > 0)
{
query += $" {string.Join(" AND ", WhereClauseQueryList)} ;";
}
if (permissions.Count > 0)
{
query += $" ({string.Join(" OR ", WhereClausePermissionList)}) ;";
}
return(query);
}