public static DataTable GetStudentCountReport(int credentialID, int levelID, string selectedViewBy, int selectedSchoolID, int selectedTeacherId, int selectedClassID,
List<Demographics.ValueObject> selectedDemographics, int selectedGroupID, int selectedStudentID, string selectedAlignments, string selectedYear = null)
{
DataTable studentCount = new DataTable();
using (SqlConnection conn = new SqlConnection(AppSettings.ConnectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand
{
CommandType = CommandType.StoredProcedure,
CommandText = Thinkgate.Base.Classes.Data.StoredProcedures.E3_CREDENTIALS_TRACKINGREPORTSTUDENT_GET,
Connection = conn
};
cmd.Parameters.Add(credentialID == null
? new SqlParameter { ParameterName = "CredentialID", Value = DBNull.Value }
: new SqlParameter { ParameterName = "CredentialID", Value = credentialID });
cmd.Parameters.Add(levelID == null
? new SqlParameter { ParameterName = "LevelID", Value = DBNull.Value }
: new SqlParameter { ParameterName = "LevelID", Value = levelID });
cmd.Parameters.Add(selectedViewBy == null
? new SqlParameter { ParameterName = "ViewBy", Value = DBNull.Value }
: new SqlParameter { ParameterName = "ViewBy", Value = selectedViewBy });
cmd.Parameters.Add((selectedSchoolID == 0)
? new SqlParameter { ParameterName = "SchoolId", Value = DBNull.Value }
: new SqlParameter { ParameterName = "SchoolId", Value = selectedSchoolID });
cmd.Parameters.Add((selectedTeacherId == 0)
? new SqlParameter { ParameterName = "TeacherId", Value = DBNull.Value }
: new SqlParameter { ParameterName = "TeacherId", Value = selectedTeacherId });
cmd.Parameters.Add((selectedClassID == 0)
? new SqlParameter { ParameterName = "ClassId", Value = DBNull.Value }
: new SqlParameter { ParameterName = "ClassId", Value = selectedClassID });
cmd.Parameters.Add((selectedStudentID == 0)
? new SqlParameter { ParameterName = "StudentID", Value = DBNull.Value }
: new SqlParameter { ParameterName = "StudentID", Value = selectedStudentID });
cmd.Parameters.Add((selectedGroupID == 0)
? new SqlParameter { ParameterName = "GroupID", Value = DBNull.Value }
: new SqlParameter { ParameterName = "GroupID", Value = selectedGroupID });
drGeneric_Int drAlignments = new drGeneric_Int();
if (!string.IsNullOrEmpty(selectedAlignments))
{
string[] arAlignments = (selectedAlignments).Split(',');
int alignment = 0;
foreach (string strAlign in arAlignments)
if (Int32.TryParse(strAlign, out alignment))
drAlignments.Add(alignment);
}
//cmd.Parameters.Add(new SqlParameter { ParameterName = "Alignments", Value = drAlignments.ToSql() });
cmd.Parameters.Add((selectedYear == null || selectedYear == "")
? new SqlParameter { ParameterName = "Year", Value = DBNull.Value }
: new SqlParameter { ParameterName = "Year", Value = selectedYear });
if (selectedDemographics != null)
{
foreach (var selectedDemographic in selectedDemographics)
{
switch (selectedDemographic.DemoLabel)
{
case "Race":
cmd.Parameters.Add(selectedDemographic.DemoValue != null
? new SqlParameter { ParameterName = "Race", Value = selectedDemographic.DemoValue }
: new SqlParameter { ParameterName = "Race", Value = DBNull.Value });
break;
case "Students With Disabilities":
cmd.Parameters.Add(selectedDemographic.DemoValue != null
? new SqlParameter { ParameterName = "Disability", Value = selectedDemographic.DemoValue }
: new SqlParameter { ParameterName = "Disability", Value = DBNull.Value });
break;
case "English Language Learner":
cmd.Parameters.Add(selectedDemographic.DemoValue != null
? new SqlParameter { ParameterName = "EnglishLearner", Value = selectedDemographic.DemoValue }
: new SqlParameter { ParameterName = "EnglishLearner", Value = DBNull.Value });
break;
case "Economically Disadvantaged":
cmd.Parameters.Add(selectedDemographic.DemoValue != null
? new SqlParameter { ParameterName = "EconomicDisAdv", Value = selectedDemographic.DemoValue }
: new SqlParameter { ParameterName = "EconomicDisAdv", Value = DBNull.Value });
break;
case "Early Intervention Program":
cmd.Parameters.Add(selectedDemographic.DemoValue != null
? new SqlParameter { ParameterName = "EarlyInt", Value = selectedDemographic.DemoValue }
: new SqlParameter { ParameterName = "EarlyInt", Value = DBNull.Value });
break;
case "Gender":
cmd.Parameters.Add(selectedDemographic.DemoValue != null
? new SqlParameter { ParameterName = "Gender", Value = selectedDemographic.DemoValue }
: new SqlParameter { ParameterName = "Gender", Value = DBNull.Value });
break;
case "Gifted":
cmd.Parameters.Add(selectedDemographic.DemoValue != null
? new SqlParameter { ParameterName = "Gifted", Value = selectedDemographic.DemoValue }
: new SqlParameter { ParameterName = "Gifted", Value = DBNull.Value });
break;
}
}
if (!cmd.Parameters.Contains("Gender"))
{
cmd.Parameters.Add(new SqlParameter { ParameterName = "Gender", Value = DBNull.Value });
}
if (!cmd.Parameters.Contains("Race"))
{
cmd.Parameters.Add(new SqlParameter { ParameterName = "Race", Value = DBNull.Value });
}
if (!cmd.Parameters.Contains("EnglishLearner"))
{
cmd.Parameters.Add(new SqlParameter { ParameterName = "EnglishLearner", Value = DBNull.Value });
}
if (!cmd.Parameters.Contains("EconomicDisAdv"))
{
cmd.Parameters.Add(new SqlParameter { ParameterName = "EconomicDisAdv", Value = DBNull.Value });
}
if (!cmd.Parameters.Contains("Gifted"))
{
cmd.Parameters.Add(new SqlParameter { ParameterName = "Gifted", Value = DBNull.Value });
}
if (!cmd.Parameters.Contains("Disability"))
{
cmd.Parameters.Add(new SqlParameter { ParameterName = "Disability", Value = DBNull.Value });
}
if (!cmd.Parameters.Contains("EarlyInt"))
{
cmd.Parameters.Add(new SqlParameter { ParameterName = "EarlyInt", Value = DBNull.Value });
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(studentCount);
}
return studentCount;
}
}
private void DoSearch(bool excelExport=false)
{
var criteriaController = Master.CurrentCriteria(); // just going to go ahead and pull this from master instead of from search handler so it will work on tree updates
/* Courses */
var selectedGrades = criteriaController.ParseCriteria<E3Criteria.CheckBoxList.ValueObject>("Grade").Select(x => x.Text).ToList();
var selectedSubjects = criteriaController.ParseCriteria<E3Criteria.CheckBoxList.ValueObject>("Subject").Select(x => x.Text).ToList();
var selectedCourses = criteriaController.ParseCriteria<E3Criteria.CheckBoxList.ValueObject>("Course").Select(x => x.Text).ToList();
var selectedStandardSets = new drGeneric_String(criteriaController.ParseCriteria<E3Criteria.CheckBoxList.ValueObject>("StandardSet").Select(x => x.Text)); // take straight to drGeneric_String because it's going to SQL
var filteredCourses = CourseMasterList.GetStandardCoursesForUser(SessionObject.LoggedInUser).FilterByGradesSubjectsStandardSetsAndCourse(selectedGrades, selectedSubjects, selectedStandardSets, selectedCourses);
List<DropDownList.ValueObject> selectedLevels = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("Level");
_standardLevel = selectedLevels.Count > 0 ? selectedLevels[0].Text : "";
/* Text Search */
string searchText = string.Empty;
string searchOption = string.Empty;
var txtSearchList = criteriaController.ParseCriteria<TextWithDropdown.ValueObject>("TextSearch");
if (txtSearchList.Count > 0)
{
// we ensure that the value the user gave us for text search type is a valid option
var confirmedOption = TextSearchDropdownValues().Find(x => x.Name == txtSearchList[0].Option) ?? TextSearchDropdownValues().First();
if (!String.IsNullOrEmpty(txtSearchList[0].Text))
{
searchText = txtSearchList[0].Text;
searchOption = confirmedOption.Value;
}
}
/* Standard Filters */
var standardFilters = new drGeneric_Int();
var selectedStandardFilter = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("StandardFilter");
if (selectedStandardFilter != null && selectedStandardFilter.Count >= 1 && SessionObject.LoggedInUser.StandardFilters.ContainsKey(selectedStandardFilter[0].Text))
{
IEnumerable<int> ids = SessionObject.LoggedInUser.StandardFilters[selectedStandardFilter[0].Text].Split(',').Select(DataIntegrity.ConvertToInt);
standardFilters.AddRange(ids);
}
dtItemBank itemBanks = ItemBankMasterList.GetItemBanksForUser(SessionObject.LoggedInUser, ThinkgatePermission.PermissionLevelValues.ItemBankEdit, "Search");
DtGrid = Thinkgate.Base.Classes.Standards.SearchStandards( itemReservation: UserHasPermission(Permission.Access_ItemReservation),
textSearch:searchText,
textSearchVal:searchOption,
itemBanks:itemBanks,
standardCourses:filteredCourses,
filterList:standardFilters,
standardSetList:selectedStandardSets,
standardLevel: _standardLevel);
// Add columns to the data table.
DataColumn displayTextCol = DtGrid.Columns.Add("NameDisplayText", typeof(String));
List<Int32> allStandardIds = (from s in DtGrid.AsEnumerable() select s.Field<Int32>("StandardID")).Distinct().ToList();
String currRoot = "";
foreach (DataRow row in DtGrid.Rows)
{
currRoot = (String)((String)row["StandardName"]).Clone();
row[displayTextCol] = currRoot;
}
if (excelExport)
return;
DtGrid = Standpoint.Core.Classes.Encryption.EncryptDataTableColumn(DtGrid, "StandardID", "LinkID");
if (_isStandardEncrypted)
{
DtGrid = Standpoint.Core.Classes.Encryption.EncryptDataTableColumn(DtGrid, "StandardID", "EncryptedID");
}
else
{
DtGrid.Columns.Add("EncryptedID");
foreach (DataRow dr in DtGrid.Rows)
{
dr["EncryptedID"] = dr["StandardID"];
}
}
foreach (DataRow row in DtGrid.Rows)
{
if (row["ParentID"].Equals(0) || !string.IsNullOrEmpty(searchText))
{
row["ParentID"] = System.DBNull.Value;
}
}
if (DtGrid != null)
{
if (DtGrid.Rows.Count > 5000)
{
var windowManager = Master.FindControl("RadWindowManager1");
if (windowManager != null)
{
RadWindowManager radWindowManager = (RadWindowManager)windowManager;
radWindowManager.RadAlert(LargeCountMessage, 330, 180, "Message from webpage", string.Empty);
}
DataTable dt = DtGrid.Rows.Cast<System.Data.DataRow>().Take(5000).CopyToDataTable();
DtGrid.Clear();
DtGrid = dt;
}
DataView dv = new DataView(DtGrid, "", "", DataViewRowState.CurrentRows);
radTreeResults.DataSource = dv;
}
}
public static DataTable GetCredentialReport(string selectedViewBy, int selectedSchoolID, int selectedTeacherId, int selectedClassID,
List<Demographics.ValueObject> selectedDemographics, int selectedGroupID, int selectedStudentID, string selectedAlignments, string selectedYear = null, int credentialID = 0, int levelID = 0)
{
DataTable credentialReport = new DataTable();
drGeneric_Int drAlignments = new drGeneric_Int();
if (!string.IsNullOrEmpty(selectedAlignments))
{
string[] arAlignments = (selectedAlignments).Split(',');
int alignment = 0;
foreach (string strAlign in arAlignments)
if (Int32.TryParse(strAlign, out alignment))
drAlignments.Add(alignment);
}
var objparams = new object[] {
selectedViewBy ?? "",
selectedSchoolID,
selectedTeacherId,
selectedClassID,
selectedStudentID,
selectedGroupID,
drAlignments.ToSql(),
selectedYear??"",
selectedDemographics.Any(x=>x.DemoLabel=="Race")?selectedDemographics.Where(x=>x.DemoLabel=="Race").Select(x=>x.DemoValue).First():"",
selectedDemographics.Any(x=>x.DemoLabel=="Students With Disabilities")?selectedDemographics.Where(x=>x.DemoLabel=="Students With Disabilities").Select(x=>x.DemoValue).First():"",
selectedDemographics.Any(x=>x.DemoLabel=="English Language Learner")?selectedDemographics.Where(x=>x.DemoLabel=="English Language Learner").Select(x=>x.DemoValue).First():"",
selectedDemographics.Any(x=>x.DemoLabel=="Economically Disadvantaged")?selectedDemographics.Where(x=>x.DemoLabel=="Economically Disadvantaged").Select(x=>x.DemoValue).First():"",
selectedDemographics.Any(x=>x.DemoLabel=="Early Intervention Program")?selectedDemographics.Where(x=>x.DemoLabel=="Early Intervention Program").Select(x=>x.DemoValue).First():"",
selectedDemographics.Any(x=>x.DemoLabel=="Gender")?selectedDemographics.Where(x=>x.DemoLabel=="Gender").Select(x=>x.DemoValue).First():"",
selectedDemographics.Any(x=>x.DemoLabel=="Gifted")?selectedDemographics.Where(x=>x.DemoLabel=="Gifted").Select(x=>x.DemoValue).First():"",
credentialID,
levelID
};
DataSet dsCredentialReport = ThinkgateDataAccess.FetchDataSet(Thinkgate.Base.Classes.Data.StoredProcedures.E3_CREDENTIALS_TRACKINGREPORT_GET, objparams);
return dsCredentialReport.Tables[0];
}
public static DataSet GetCredentialReport(string selectedCriteria)
{
DataTable credentialReport = new DataTable();
var Tempobjparams = selectedCriteria.Split(';');
string selectedViewBy = Tempobjparams[0];
int selectedSchoolID =Tempobjparams[1]==""?0: Convert.ToInt32(Tempobjparams[1]);
int selectedTeacherId = Tempobjparams[2] == "" ? 0 : Convert.ToInt32(Tempobjparams[2]);
int selectedClassID = Tempobjparams[3] == "" ? 0 : Convert.ToInt32(Tempobjparams[3]);
int selectedGroupID = Tempobjparams[4] == "" ? 0 : Convert.ToInt32(Tempobjparams[4]);
int selectedStudentID = Tempobjparams[5] == "" ? 0 : Convert.ToInt32(Tempobjparams[5]);
string selectedAlignments = Tempobjparams[6];
string selectedYear = Tempobjparams[7];
string Race = Tempobjparams[8];
string Students_With_Disabilities = Tempobjparams[9];
string English_Language_Learner = Tempobjparams[10];
string Economically_Disadvantaged = Tempobjparams[11];
string Early_Intervention_Program = Tempobjparams[12];
string Gender = Tempobjparams[13];
string Gifted = Tempobjparams[14];
int credentialID = Tempobjparams[15] == "" ? 0 : Convert.ToInt32(Tempobjparams[15]);
int levelID = Tempobjparams[16] == "" ? 0 : Convert.ToInt32(Tempobjparams[16]);
drGeneric_Int drAlignments = new drGeneric_Int();
if (!string.IsNullOrEmpty(selectedAlignments))
{
string[] arAlignments = (selectedAlignments).Split(',');
int alignment = 0;
foreach (string strAlign in arAlignments)
if (Int32.TryParse(strAlign, out alignment))
drAlignments.Add(alignment);
}
var objparams = new object[] {
selectedViewBy ?? "",
selectedSchoolID,
selectedTeacherId,
selectedClassID,
selectedStudentID,
selectedGroupID,
drAlignments.ToSql(),
selectedYear??"",
Race,
Students_With_Disabilities,
English_Language_Learner,
Economically_Disadvantaged,
Early_Intervention_Program,
Gender,
Gifted,
credentialID,
levelID
};
DataSet dsCredentialReport = ThinkgateDataAccess.FetchDataSet(Thinkgate.Base.Classes.Data.StoredProcedures.E3_CREDENTIALS_TRACKINGREPORT_GET, objparams);
return dsCredentialReport;
}