private void BuildCriteriaSheet(string worksheetName, XLWorkbook workbook, SelectedCriteria selectedCriteria)
{
//You can find info on manipulating the Excel workbook here - https://closedxml.codeplex.com/documentation
var ws = workbook.Worksheets.Add(worksheetName);
ws.Range("B2:B4").Style.Font.Bold = true;
ws.Cell(2, 2).Value = CompetencyTrackingReport;
ws.Cell("B2").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //Center the cell
ws.Range("B2:C2").Row(1).Merge(); //Merge across cells
ws.Range("B2:C2").Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
ws.Cell(3, 2).Value = "Standard List";
ws.Cell(4, 2).Value = "Criteria";
ws.Cell("B4").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; //Center the cell
ws.Range("B4:B7").Column(1).Merge(); ; //Merge across columns
if (string.IsNullOrEmpty(selectedCriteria.SelectedWorksheet))
{
ws.Cell(3, 3).Value = selectedCriteria.StandardList;
}
else
{
ws.Cell(3, 3).Value = selectedCriteria.StandardList + ": \"" + selectedCriteria.SelectedWorksheet + "\"";
}
//View By, Demographics, Standard Level, and Data Range
ws.Cell(4, 3).Value = "View By = " + selectedCriteria.SelectedViewBy;
ws.Cell(5, 3).Value = "Demographics = " + selectedCriteria.SelectedDemographics.ToArray();
ws.Cell(6, 3).Value = "Standard Level = " + selectedCriteria.StandardLevel;
ws.Cell(7, 3).Value = "Date Range = (start:" + selectedCriteria.StartDate + " - end: " + selectedCriteria.EndDate + ")";
ws.Range("D2:D7").Style.Font.Bold = true;
var theDistrict = Base.Classes.District.GetDistrictByID(SessionObject.LoggedInUser.District);
ws.Cell(2, 4).Value = theDistrict.DistrictName + " (" + theDistrict.ClientID + ")";
ws.Cell("D2").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //Center the cell
ws.Range("D2:G2").Row(1).Merge(); //Merge across cells
ws.Range("D2:G2").Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
ws.Cell(3, 4).Value = "School";
ws.Cell(4, 4).Value = "Teacher";
ws.Cell(5, 4).Value = "Class";
ws.Cell(6, 4).Value = "Group";
ws.Cell(7, 4).Value = "Student";
ws.Cell(3, 5).Value = selectedCriteria.SelectedSchoolName;
ws.Cell(4, 5).Value = selectedCriteria.SelectedTeacher;
ws.Cell(5, 5).Value = selectedCriteria.SelectedClass;
ws.Cell(6, 5).Value = selectedCriteria.SelectedGroup;
ws.Cell(7, 5).Value = selectedCriteria.SelectedStudent;
ws.Range("F3:F6").Style.Font.Bold = true;
ws.Cell(3, 6).Value = "# Schools";
ws.Cell(4, 6).Value = "# Teachers";
ws.Cell(5, 6).Value = "# Classes";
ws.Cell(6, 6).Value = "# Groups";
ws.Cell(7, 6).Value = "# Students";
ws.Cell(3, 7).Value = "?";
ws.Cell(4, 7).Value = "?";
ws.Cell(5, 7).Value = "?";
ws.Cell(6, 7).Value = "?";
ws.Cell(7, 7).Value = "?";
ws.Columns().AdjustToContents();
}
private SelectedCriteria GetCriteriaControlValues()
{
var criteriaController = Master.CurrentCriteria();
SelectedCriteria selectedCriteria = new SelectedCriteria();
//ViewBy
selectedCriteria.SelectedViewBy = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("ViewBy").Select(x => x.Text).FirstOrDefault();
//School
selectedCriteria.SelectedSchoolId = DataIntegrity.ConvertToInt(criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("School").Select(x => x.Value).FirstOrDefault());
selectedCriteria.SelectedSchoolName = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("School").Select(x => x.Value).FirstOrDefault();
//Teacher
selectedCriteria.SelectedTeacher = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("TeacherName").Select(x => x.Value).FirstOrDefault();
//Class
selectedCriteria.SelectedClass = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("Class").Select(x => x.Value).FirstOrDefault();
//Student
selectedCriteria.SelectedStudent = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("Student").Select(x => x.Value).FirstOrDefault();
//StandardLevel
selectedCriteria.StandardLevel = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("StandardLevel").Select(x => x.Value).FirstOrDefault();
//Demographics
selectedCriteria.SelectedDemographics = criteriaController.ParseCriteria<E3Criteria.AutoCompleteCriteriaControls.ACDropDownList.ValueObject>("Demographic").Select(x => x.Value).FirstOrDefault();
//Group;
selectedCriteria.SelectedGroup = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("Group").Select(x => x.Value).FirstOrDefault();
//Worksheet List
string selectedListId = "0";
if (criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("ListSelection").Select(x => x.Value).FirstOrDefault() != null)
{
selectedListId = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("ListSelection").Select(x => x.Value).FirstOrDefault();
}
else
{
selectedListId = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("WorksheetSelection").Select(x => x.Value).FirstOrDefault();
}
selectedCriteria.SelectedWorksheet = selectedListId;
//StandardList
selectedCriteria.StandardList = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("StandardList").Select(x => x.Value).FirstOrDefault();
selectedCriteria.StandardSet = criteriaController.ParseCriteria<E3Criteria.AutoCompleteCriteriaControls.GetGradeSubjectCourseStandard.ValueObject>("StandardSet").Select(x => x.StandardSet).FirstOrDefault();
selectedCriteria.StandardId = criteriaController.ParseCriteria<E3Criteria.AutoCompleteCriteriaControls.GetGradeSubjectCourseStandard.ValueObject>("StandardSet").Select(x => x.StandardId).FirstOrDefault();
//if (criteriaController.CriteriaNodes[2].Values[0].Value != null && criteriaController.CriteriaNodes[2].Values[0].Value.ContainsKey("Grades"))
//{
selectedCriteria.Grade = criteriaController.ParseCriteria<E3Criteria.AutoCompleteCriteriaControls.GetGradeSubjectCourseStandard.ValueObject>("StandardSet").Select(x => x.Grades).FirstOrDefault();//criteriaController.ParseCriteria<E3Criteria.AutoCompleteCriteriaControls.GetGradeSubjectCourseStandard.ValueObject>("Grades").Select(x => x.Grades).FirstOrDefault();
//}
//if (criteriaController.CriteriaNodes[2].Values[0].Value != null && criteriaController.CriteriaNodes[2].Values[0].Value.ContainsKey("Subjects"))
//{
selectedCriteria.Subject = criteriaController.ParseCriteria<E3Criteria.AutoCompleteCriteriaControls.GetGradeSubjectCourseStandard.ValueObject>("StandardSet").Select(x => x.Subjects).FirstOrDefault(); //criteriaController.ParseCriteria<E3Criteria.AutoCompleteCriteriaControls.GetGradeSubjectCourseStandard.ValueObject>("Subjects").Select(x => x.Subjects).FirstOrDefault();
//}
//if (criteriaController.CriteriaNodes[2].Values[0].Value != null && criteriaController.CriteriaNodes[2].Values[0].Value.ContainsKey("Courses"))
//{
selectedCriteria.Course = criteriaController.ParseCriteria<E3Criteria.AutoCompleteCriteriaControls.GetGradeSubjectCourseStandard.ValueObject>("StandardSet").Select(x => x.Courses).FirstOrDefault();
//}
//TODO UNCOMMENT AND VERIFY BELOW CODE ONCE STANDARD LIST IS WORKING
//selectedCriteria.StandardList = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("ListSelection").Select(x => x.Value).FirstOrDefault();
selectedlist = Convert.ToInt32(criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("StandardList").Select(x => x.Value).FirstOrDefault());
if (selectedlist == 9)//for competency list option only
{
Base.DataAccess.dtGeneric_Int _standardids = new Base.DataAccess.dtGeneric_Int();
_standardids.Add(Convert.ToInt32(selectedCriteria.SelectedWorksheet));
DataTable dsstandard = CompetencyWorkSheet.GetCurrStabdardsById_Kentico(_standardids, true);
var stdListFromCompetencyList = "";
foreach (DataRow dr in dsstandard.Rows)
{
stdListFromCompetencyList += "_" + dr[0].ToString();
}
if (!string.IsNullOrEmpty(stdListFromCompetencyList))
{
selectedCriteria.StandardId = stdListFromCompetencyList.Substring(1);
}
selectedCriteria.SelectedWorksheet = null;
}
/* Created Date Range */
foreach (var val in criteriaController.ParseCriteria<DateRange.ValueObject>("DateRange"))
{
if (val.Type == "Start")
{
selectedCriteria.StartDate = val.Date;
}
else
{
selectedCriteria.EndDate = val.Date;
}
}
return selectedCriteria;
}
private void FormatColumnsForExcel(SelectedCriteria selectedCriteria)
{
string selectedViewBy = selectedCriteria.SelectedViewBy;
string standardLevel = selectedCriteria.StandardLevel;
DataTable localDataTable = GridDataTable.Copy();
localDataTable.Columns[selectedViewBy + "Name"].ColumnName = selectedViewBy + " (" + _distinctRowsCount + ")";
if (!string.IsNullOrEmpty(standardLevel))
{
localDataTable.Columns["StandardName"].ColumnName = standardLevel + " (" + _distinctObjectivesCount + ")";
}
else
{
localDataTable.Columns["StandardName"].ColumnName = "Competencies (" + _distinctObjectivesCount + ")";
}
//localDataTable.Columns["DateA"].ColumnName = "No Attempt";
//localDataTable.Columns["DateB"].ColumnName = "Beginning\r\n/Novice";
//localDataTable.Columns["DateC"].ColumnName = "Developing\r\n/Emerging";
//localDataTable.Columns["DateD"].ColumnName = "Accomplished\r\n/Proficient";
//localDataTable.Columns["DateE"].ColumnName = "Exemplary\r\n/Master";
ReportData theReportData = new ReportData
{
ReportDataTable = localDataTable,
ReportSelectedCriteria = selectedCriteria
};
SessionObject.CompetencyTracking_ReportData = theReportData;
}