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