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