/// <summary>
/// This method generates the NCPMI Excel File Report and fills it with values from the database.
/// </summary>
/// <param name="programFKs">The program FKs to filter the report</param>
/// <param name="schoolYear">The school year DateTime to filter the report</param>
/// <returns>A byte array representation of the Excel file</returns>
public static byte[] GenerateNCPMIExcelFile(List <int> programFKs, DateTime schoolYear)
{
try
{
//To hold the necessary database values
List <rspBIRExcel_ProgramInfo_Result> allProgramInfo;
List <rspBIRExcel_ChildrenAndBIRs_Result> allBIRAndChildInfo;
List <string> programNames;
//Get the necessary values from the database
using (PyramidContext context = new PyramidContext())
{
//Get the program names
programNames = context.Program.AsNoTracking()
.Where(p => programFKs.Contains(p.ProgramPK))
.Select(p => p.ProgramName)
.ToList();
//Get the BIR and Child info for the Excel file
allBIRAndChildInfo = context.rspBIRExcel_ChildrenAndBIRs(string.Join(",", programFKs),
schoolYear).ToList();
//Get the program info for the Excel file
allProgramInfo = context.rspBIRExcel_ProgramInfo(string.Join(",", programFKs), schoolYear).ToList();
}
//Get the file info for the master Excel file
string excelFilePath = HttpContext.Current.Server.MapPath("~/Reports/PreBuiltReports/ExcelReports/BIR_Report.xlsm");
FileInfo fileInfo = new FileInfo(excelFilePath);
//Only continue if the master Excel file exists
if (fileInfo.Exists)
{
using (ExcelPackage excel = new ExcelPackage(fileInfo))
{
//Make sure that the XLSM file works properly
if (excel.Workbook.VbaProject == null)
{
excel.Workbook.CreateVBAProject();
}
//Get the necessary worksheets from the excel file
var programInfoWorksheet = excel.Workbook.Worksheets.Where(w => w.Name == "Program Information").FirstOrDefault();
var childWorksheet = excel.Workbook.Worksheets.Where(w => w.Name == "Child Enrollment").FirstOrDefault();
var BIRWorksheet = excel.Workbook.Worksheets.Where(w => w.Name == "BIR Data Entry").FirstOrDefault();
//------------------- PROGRAM INFO -----------------------------
//Set the basic program info and school year chosen
programInfoWorksheet.Cells[1, 2].Value = string.Join(", ", programNames);
programInfoWorksheet.Cells[2, 2].Value = schoolYear.ToString("yyyy");
//To hold the program info aggregates
int[] classroomNumRow = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
int[] ethnicityNumRow = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
int[] race1NumRow = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
int[] race2NumRow = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
int[] race3NumRow = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
int[] race4NumRow = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
int[] race5NumRow = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
int[] race6NumRow = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
int[] genderNumRow = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
int[] IEPNumRow = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
int[] DLLNumRow = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
//Calculate the aggregates by looping through the rows returned from the database
foreach (rspBIRExcel_ProgramInfo_Result programInfoRow in allProgramInfo)
{
//Get the classroom attendance numbers
classroomNumRow[programInfoRow.MonthNumOnSpreadsheet]++;
/* NOTE: The Excel sheet only asks for 1 row of Ethnicity, IEP, and DLL from
* us and calculates the other row automatically
*/
//Get the ethnicity numbers
if (!programInfoRow.Ethnicity.ToLower().Contains("not"))
{
ethnicityNumRow[programInfoRow.MonthNumOnSpreadsheet]++;
}
//Get the current race
string race = programInfoRow.Race.ToLower();
//Get the race numbers (American Indian and Alaskan native are combined in the stored procedure,
//so are Native Hawaiian and Pacific Islander)
if (race.Contains("american indian"))
{
race1NumRow[programInfoRow.MonthNumOnSpreadsheet]++;
}
else if (race == "asian")
{
race2NumRow[programInfoRow.MonthNumOnSpreadsheet]++;
}
else if (race.Contains("black"))
{
race3NumRow[programInfoRow.MonthNumOnSpreadsheet]++;
}
else if (race.Contains("native hawaiian"))
{
race4NumRow[programInfoRow.MonthNumOnSpreadsheet]++;
}
else if (race == "two or more races")
{
race5NumRow[programInfoRow.MonthNumOnSpreadsheet]++;
}
else if (race == "white")
{
race6NumRow[programInfoRow.MonthNumOnSpreadsheet]++;
}
//Get the gender numbers
if (programInfoRow.Gender.ToLower() == "female")
{
genderNumRow[programInfoRow.MonthNumOnSpreadsheet]++;
}
//Get the IEP numbers
if (programInfoRow.HasIEP)
{
IEPNumRow[programInfoRow.MonthNumOnSpreadsheet]++;
}
//Get the DLL numbers
if (programInfoRow.IsDLL)
{
DLLNumRow[programInfoRow.MonthNumOnSpreadsheet]++;
}
}
//Set the worksheet values
programInfoWorksheet.Cells[4, 2].LoadFromText("All Classrooms," + string.Join(",", classroomNumRow));
programInfoWorksheet.Cells[37, 3].LoadFromText(string.Join(",", ethnicityNumRow));
programInfoWorksheet.Cells[42, 3].LoadFromText(string.Join(",", race1NumRow));
programInfoWorksheet.Cells[43, 3].LoadFromText(string.Join(",", race2NumRow));
programInfoWorksheet.Cells[44, 3].LoadFromText(string.Join(",", race3NumRow));
programInfoWorksheet.Cells[45, 3].LoadFromText(string.Join(",", race4NumRow));
programInfoWorksheet.Cells[46, 3].LoadFromText(string.Join(",", race5NumRow));
programInfoWorksheet.Cells[47, 3].LoadFromText(string.Join(",", race6NumRow));
programInfoWorksheet.Cells[51, 3].LoadFromText(string.Join(",", genderNumRow));
programInfoWorksheet.Cells[56, 3].LoadFromText(string.Join(",", IEPNumRow));
programInfoWorksheet.Cells[61, 3].LoadFromText(string.Join(",", DLLNumRow));
//------------------- END PROGRAM INFO -----------------------------
//------------------- CHILDREN AND BIRs -----------------------------
//To hold the list of children PKs already added (to prevent duplicates)
List <int> childrenAdded = new List <int>();
//Start putting the children and BIRs in at the second row
int incidentIndex = 2;
int childIndex = 2;
//Loop through the stored procedure results
foreach (rspBIRExcel_ChildrenAndBIRs_Result childAndBIR in allBIRAndChildInfo)
{
//Don't insert duplicate child information into the worksheet
if (!childrenAdded.Contains(childAndBIR.ChildFK))
{
//Add the child to a stringbuilder
StringBuilder childrenToAdd = new StringBuilder();
childrenToAdd.Append(childAndBIR.FirstName + " " + childAndBIR.LastName + ",");
childrenToAdd.Append(childAndBIR.ProgramSpecificID + "_" + childAndBIR.ProgramFKChild.ToString() + ",");
childrenToAdd.Append(childAndBIR.Gender + ",");
childrenToAdd.Append((childAndBIR.IsDLL ? "DLL" : "Non-DLL") + ",");
childrenToAdd.Append((childAndBIR.HasIEP ? "Yes" : "No") + ",");
childrenToAdd.Append(childAndBIR.Ethnicity + ",");
childrenToAdd.Append(childAndBIR.Race + ",");
childrenToAdd.Append((childAndBIR.DischargeDate.HasValue ? "Disenrolled" : "Enrolled") + ",");
childrenToAdd.Append((childAndBIR.DischargeDate.HasValue ? "Disenrolled on " + childAndBIR.DischargeDate.Value.ToString("MM/dd/yyyy") + ". Reason: " + childAndBIR.DischargeReason + "," : ","));
//Add the child to the worksheet via the stringbuilder
childWorksheet.Cells[childIndex, 1].LoadFromText(childrenToAdd.ToString());
//Record the fact that the child was added
childrenAdded.Add(childAndBIR.ChildFK);
//Increment the child index
childIndex++;
}
//Add the BIR to a stringbuilder
StringBuilder incidentsToAdd = new StringBuilder();
incidentsToAdd.Append(childAndBIR.ClassroomID + "_" + childAndBIR.ProgramFKClassroom.ToString() + ",");
incidentsToAdd.Append(childAndBIR.ProgramSpecificID + "_" + childAndBIR.ProgramFKChild.ToString() + ",");
incidentsToAdd.Append(childAndBIR.IncidentDatetime.ToString("MMMM") + ",");
incidentsToAdd.Append(childAndBIR.IncidentDatetime.ToString("MM/dd/yy") + ",");
incidentsToAdd.Append(childAndBIR.IncidentDatetime.ToString("hh:mm tt") + ",");
incidentsToAdd.Append(childAndBIR.ProblemBehavior + ",");
incidentsToAdd.Append(childAndBIR.Activity + ",");
incidentsToAdd.Append(childAndBIR.OthersInvolved + ",");
incidentsToAdd.Append(childAndBIR.PossibleMotivation + ",");
incidentsToAdd.Append(childAndBIR.StrategyResponse + ",");
incidentsToAdd.Append(childAndBIR.AdminFollowUp + ",");
//Add the BIR to the worksheet via the stringbuilder
BIRWorksheet.Cells[incidentIndex, 1].LoadFromText(incidentsToAdd.ToString());
//Bug Fix - Excel doesn't like the string version of the time and requires a TimeSpan instead
//Set the time of the incident
BIRWorksheet.Cells[incidentIndex, 5].Value = childAndBIR.IncidentDatetime.TimeOfDay;
//Increment the incident index
incidentIndex++;
}
//------------------- END CHILDREN AND BIRs -----------------------------
//Return a byte array representation of the excel file
return(excel.GetAsByteArray());
}
}
else
{
//The file doesn't exist, return null
return(null);
}
}
catch (Exception ex)
{
//Log any exceptions and return null
LogException(ex);
return(null);
}
}