private void FormatColumnsForExcel(SelectedCriteriaCrd selectedCriteria)
        {
            string selectedViewBy = selectedCriteria.SelectedViewBy;
            DataTable localDataTable = GridDataTable.Copy();

            switch (selectedViewBy)
            {
                case "District":
                    localDataTable.Columns.Remove("LevelID");
                    localDataTable.Columns.Remove("LevelName");
                    localDataTable.Columns.Remove("CredentialID");
                    localDataTable.Columns.Remove("EarnedDate");
                    localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
                    localDataTable.Columns["StudentCount"].ColumnName = "Student Count";

                    break;
                case "School":
                    localDataTable.Columns.Remove("LevelID");
                    localDataTable.Columns["LevelName"].ColumnName = "School";
                    localDataTable.Columns.Remove("CredentialID");
                    localDataTable.Columns.Remove("EarnedDate");
                    localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
                    localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
                    break;
                case "Teacher":
                    localDataTable.Columns.Remove("LevelID");
                    localDataTable.Columns["LevelName"].ColumnName = "Teacher";
                    localDataTable.Columns.Remove("CredentialID");
                    localDataTable.Columns.Remove("EarnedDate");
                    localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
                    localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
                    break;
                case "Class":
                    localDataTable.Columns.Remove("LevelID");
                    localDataTable.Columns["LevelName"].ColumnName = "Class";
                    localDataTable.Columns.Remove("CredentialID");
                    localDataTable.Columns.Remove("EarnedDate");
                    localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
                    localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
                    break;
                case "Group":
                    localDataTable.Columns.Remove("LevelID");
                    localDataTable.Columns["LevelName"].ColumnName = "Group";
                    localDataTable.Columns.Remove("CredentialID");
                    localDataTable.Columns.Remove("EarnedDate");
                    localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
                    localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
                    break;
                case "Student":
                    localDataTable.Columns.Remove("LevelID");
                    localDataTable.Columns["LevelName"].ColumnName = "Student";
                    localDataTable.Columns.Remove("CredentialID");

                    // Need to modify for yes/no value.
                    //localDataTable.Columns["EarnedDate"].DataType = typeof(String);
                    DataColumn EarnedCol = localDataTable.Columns.Add("Earned", typeof(String));
                    foreach (DataRow dr in localDataTable.Rows)
                    {
                        string date = dr["EarnedDate"].ToString().Trim();
                        dr["Earned"] = String.IsNullOrEmpty(date) ? "No" : "Yes";
                    }
                    
                    localDataTable.Columns.Remove("EarnedDate");
                    localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
                    localDataTable.Columns.Remove("StudentCount");

                    break;
                case "Demographics":
                    localDataTable.Columns.Remove("LevelID");
                    localDataTable.Columns["LevelName"].ColumnName = "Demographics";
                    localDataTable.Columns.Remove("CredentialID");
                    localDataTable.Columns.Remove("EarnedDate");
                    localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
                    localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
                    break;
                case "Alignment":
                    localDataTable.Columns.Remove("LevelID");
                    localDataTable.Columns["LevelName"].ColumnName = "Alignment";
                    localDataTable.Columns.Remove("CredentialID");
                    localDataTable.Columns.Remove("EarnedDate");
                    localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
                    localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
                    break;

                case "Year":
                    localDataTable.Columns.Remove("LevelID");
                    localDataTable.Columns["LevelName"].ColumnName = "Year";
                    localDataTable.Columns.Remove("CredentialID");
                    localDataTable.Columns.Remove("EarnedDate");
                    localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
                    localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
                    break;

            }

            ReportDataCredential theReportData = new ReportDataCredential
            {
                ReportDataTable = localDataTable,
                ReportSelectedCriteria = selectedCriteria
            };
            SessionObject.CredentialTracking_ReportData = theReportData;
        }
        private void BuildCriteriaSheet(string worksheetName, XLWorkbook workbook, SelectedCriteriaCrd 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 = CredentialTrackingReport;
            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 SelectedCriteriaCrd GetCriteriaControlValues()
        {
            var criteriaController = Master.CurrentCriteria();

            SelectedCriteriaCrd selectedCriteria = new SelectedCriteriaCrd();

            //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.Text).FirstOrDefault();
            //Teacher
            selectedCriteria.SelectedTeacher = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("Teacher").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();
            //Demographics
            selectedCriteria.SelectedDemographics = criteriaController.ParseCriteria<Demographics.ValueObject>("Demographics");
            //Group;
            selectedCriteria.SelectedGroup = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("Group").Select(x => x.Value).FirstOrDefault();
            //Alignment
            selectedCriteria.SelectedAlignment = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("Alignment").Select(x => x.Value).FirstOrDefault();
            //Year
            selectedCriteria.SelectedYear = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("Year").Select(x => x.Value).FirstOrDefault();
            return selectedCriteria;
        }