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