public ActionResult GenerateReport(DynamicReportRequest request, int districtId) { var user = AppUserService.GetUser(); var filterByDistrict = user.AppRole.Name != PortalRoleNames.DataOwner; var report = DynamicReportingService.GetReportData(request, filterByDistrict ? districtId : (int?)null); var csvArray = Csv.WriteCsvToMemory(report); var memoryStream = new MemoryStream(csvArray); return(new FileStreamResult(memoryStream, "text/csv")); }
public IList <dynamic> GetReportData(DynamicReportRequest request, int?districtId) { var reportDefinition = GetReportDefinition(request.ReportDefinitionId); // todo: implement user ordering, this at least mimics the admin definition screen var selectedFields = reportDefinition.Fields .Where(x => request.SelectedFields.Contains(x.Id.ToString())) .OrderBy(x => x.Field.Id) .ToList(); var viewName = $"[{reportDefinition.RulesView.Schema}].[{reportDefinition.RulesView.Name}]"; var fieldNames = string.Join(", ", selectedFields.Select(x => $"[{x.Field.Name}]")); var report = new List <dynamic>(); using (var schoolYearContext = SchoolYearDbContextFactory.CreateWithParameter(reportDefinition.SchoolYear.EndYear)) { var connection = schoolYearContext.Database.Connection; connection.Open(); var queryCommand = connection.CreateCommand(); queryCommand.CommandTimeout = 500; queryCommand.CommandType = System.Data.CommandType.Text; queryCommand.CommandText = $"SELECT {fieldNames} FROM {viewName}"; if (!reportDefinition.IsOrgLevelReport || districtId.HasValue) // && selectedFields.Any(x => x.Field.Name.Equals("DistrictId", StringComparison.InvariantCultureIgnoreCase))) { queryCommand.CommandText += $" where [DistrictId] = {districtId}"; } LoggingService.LogInfoMessage($"Executing dynamic report SQL: {queryCommand.CommandText}"); using (var reader = queryCommand.ExecuteReader()) { while (reader.Read()) { var dynamicObject = new ExpandoObject() as IDictionary <string, object>; foreach (var field in selectedFields) { var fieldDescription = !string.IsNullOrWhiteSpace(field.Description) ? field.Description : field.Field.Name; dynamicObject.Add(fieldDescription, reader[field.Field.Name].ToString()); } report.Add(dynamicObject); } } } return(report); }