protected void btnSelect_Click(object sender, EventArgs e)
{
DateTime startDate = DateTime.Today;
DateTime endDate = DateTime.Today;
#region Parse the selected school
foreach (School school in AllSchools)
{
if (lstSchoolList.SelectedItem.Value == school.getGovIDAsString())
{
selectedSchool = school;
}
}
#endregion
#region Parse the given date
int startYear = int.Parse(from_year.SelectedValue);
int startMonth = int.Parse(from_month.SelectedValue);
int startDay = int.Parse(from_day.SelectedValue);
if (startDay > DateTime.DaysInMonth(startYear, startMonth))
{
startDay = DateTime.DaysInMonth(startYear, startMonth);
}
int endYear = int.Parse(to_year.SelectedValue);
int endMonth = int.Parse(to_month.SelectedValue);
int endDay = int.Parse(to_day.SelectedValue);
if (endDay > DateTime.DaysInMonth(endYear, endMonth))
{
endDay = DateTime.DaysInMonth(endYear, endMonth);
}
startDate = new DateTime(startYear, startMonth, startDay);
endDate = new DateTime(endYear, endMonth, endDay);
#endregion
using (SqlConnection connection = new SqlConnection(dbConnectionString))
{
AllSchools = School.loadAllSchools(connection);
if (IsPostBack)
{
if (selectedSchool != null)
{
List <Student> DisplayedStudents = LSKY_INAC.loadStudentData(connection, selectedSchool, startDate, endDate);
lblCount.Text = "Found " + DisplayedStudents.Count + " students.";
if (DisplayedStudents.Count > 0)
{
tblResults.Visible = true;
lnkCSVDownload.Visible = true;
lnkCSVDownload.NavigateUrl = "INAC_CSV.aspx?schoolid=" + selectedSchool.getGovIDAsString() + "&from_year=" + startDate.Year + "&from_month=" + startDate.Month + "&from_day=" + startDate.Day + "&to_year=" + endDate.Year + "&to_month=" + endDate.Month + "&to_day=" + endDate.Day;
}
foreach (Student student in DisplayedStudents)
{
tblResults.Rows.Add(createStudentRow(student, startDate, endDate));
}
}
}
}
}
protected MemoryStream GenerateCSV(List <Student> students)
{
MemoryStream csvFile = new MemoryStream();
StreamWriter writer = new StreamWriter(csvFile, Encoding.UTF8);
// CSV Headings
StringBuilder headingLine = new StringBuilder();
headingLine.Append("Grade, StudentName, DateOfBirth, BandAffiliation, StatusNo, ReserveOfResidence, HouseNo, ParentOrGuardian, DaysAbsentUnexcused, BlocksAbsentUnexcused, DaysAbsentExcused, BlocksAbsentExcused, DaysAbsentTotal, BlocksAbsentTotal, InStatusDate");
writer.WriteLine(headingLine.ToString());
// CSV Data
foreach (Student student in students)
{
// Get explained / unexplained counts
int excused = 0;
int unexcused = 0;
foreach (Absence abs in student.absences)
{
if (abs.excused)
{
excused++;
}
else
{
unexcused++;
}
}
// Figure out days absent
float daysAbsent = LSKY_INAC.getDaysAbsent(student);
float daysAbsent_Explained = LSKY_INAC.getDaysAbsent_Explained(student);
float daysAbsent_Unexplained = LSKY_INAC.getDaysAbsent_Unexplained(student);
// Figure out guardians
List <Contact> guardiansList = LSKY_INAC.getINACGuardians(student.contacts);
StringBuilder guardians = new StringBuilder();
for (int x = 0; x < guardiansList.Count; x++)
{
guardians.Append(guardiansList[x].firstName + " " + guardiansList[x].lastName + " (" + guardiansList[x].relation + ")");
if (x < guardiansList.Count - 1)
{
guardians.Append("; ");
}
}
StringBuilder studentLine = new StringBuilder();
studentLine.Append(student.getGradeFormatted());
studentLine.Append(",");
studentLine.Append(student.getDisplayName());
studentLine.Append(",");
studentLine.Append(student.getDateOfBirth().Month + "/" + student.getDateOfBirth().Day + "/" + student.getDateOfBirth().Year);
studentLine.Append(",");
studentLine.Append(student.getBandName());
studentLine.Append(",");
studentLine.Append(student.getStatusNo());
studentLine.Append(",");
studentLine.Append(student.getReserveName());
studentLine.Append(",");
studentLine.Append(student.getReserveHouse());
studentLine.Append(",");
studentLine.Append(guardians.ToString());
studentLine.Append(",");
// Unexplained
studentLine.Append(Math.Round(daysAbsent_Unexplained, 2).ToString());
studentLine.Append(",");
studentLine.Append(unexcused.ToString());
studentLine.Append(",");
// Excused
studentLine.Append(Math.Round(daysAbsent_Explained, 2).ToString());
studentLine.Append(",");
studentLine.Append(excused.ToString());
studentLine.Append(",");
// Total
studentLine.Append(Math.Round(daysAbsent, 2).ToString());
studentLine.Append(",");
studentLine.Append(student.absences.Count.ToString());
studentLine.Append(",");
studentLine.Append(student.getEnrollDate().Month + "/" + student.getEnrollDate().Day + "/" + student.getEnrollDate().Year);
writer.WriteLine(studentLine.ToString());
}
writer.Flush();
csvFile.Flush();
return(csvFile);
}
private TableRow createStudentRow(Student student, DateTime startDate, DateTime endDate)
{
// Split absences into explained / unexplained
List <Absence> explainedAbsences = new List <Absence>();
List <Absence> unExplainedAbsences = new List <Absence>();
foreach (Absence abs in student.absences)
{
if (abs.excused)
{
explainedAbsences.Add(abs);
}
else
{
unExplainedAbsences.Add(abs);
}
}
string calculationExplaination_Total = string.Empty;
string calculationExplaination_Explained = string.Empty;
string calculationExplaination_Unexplained = string.Empty;
float daysAbsent_Total = LSKY_INAC.getDaysAbsent(student, out calculationExplaination_Total);
float daysAbsent_Explained = LSKY_INAC.getDaysAbsent_Explained(student, out calculationExplaination_Explained);
float daysAbsent_Unexplained = LSKY_INAC.getDaysAbsent_Unexplained(student, out calculationExplaination_Unexplained);
/* figure out guardian(s) */
List <Contact> guardiansList = LSKY_INAC.getINACGuardians(student.contacts);
StringBuilder guardians = new StringBuilder();
foreach (Contact contact in guardiansList)
{
guardians.Append(contact.firstName + " " + contact.lastName + " </i>(" + contact.relation + ")</i><br>");
}
TableRow newRow = new TableRow();
newRow.CssClass = "datatable_row";
TableCell gradeCell = new TableCell();
gradeCell.Text = student.getGradeFormatted();
gradeCell.VerticalAlign = VerticalAlign.Top;
newRow.Cells.Add(gradeCell);
TableCell nameCell = new TableCell();
nameCell.Text = student.getDisplayName();
nameCell.VerticalAlign = VerticalAlign.Top;
newRow.Cells.Add(nameCell);
TableCell birthdayCell = new TableCell();
birthdayCell.Text = student.getDateOfBirth().Month + "/" + student.getDateOfBirth().Day + "/" + student.getDateOfBirth().Year;
birthdayCell.VerticalAlign = VerticalAlign.Top;
birthdayCell.HorizontalAlign = HorizontalAlign.Right;
newRow.Cells.Add(birthdayCell);
TableCell bandCell = new TableCell();
bandCell.Text = student.getBandName();
bandCell.VerticalAlign = VerticalAlign.Top;
newRow.Cells.Add(bandCell);
TableCell statusNoCell = new TableCell();
statusNoCell.Text = student.getStatusNo();
statusNoCell.VerticalAlign = VerticalAlign.Top;
newRow.Cells.Add(statusNoCell);
TableCell reserveCell = new TableCell();
reserveCell.Text = student.getReserveName();
reserveCell.VerticalAlign = VerticalAlign.Top;
newRow.Cells.Add(reserveCell);
TableCell houseNoCell = new TableCell();
houseNoCell.Text = student.getReserveHouse();
houseNoCell.VerticalAlign = VerticalAlign.Top;
newRow.Cells.Add(houseNoCell);
TableCell guardianCell = new TableCell();
guardianCell.Text = guardians.ToString();
guardianCell.VerticalAlign = VerticalAlign.Top;
newRow.Cells.Add(guardianCell);
// Unexplained absences
TableCell daysAbsent_UnexplainedCell = new TableCell();
if (daysAbsent_Unexplained == 0)
{
daysAbsent_UnexplainedCell.Text = "<i style=\"color: #707070;\">No Absences</i>";
}
else if (daysAbsent_Unexplained >= 0)
{
daysAbsent_UnexplainedCell.Text = "<abbr title=\"" + calculationExplaination_Unexplained + "\">" + Math.Round(daysAbsent_Unexplained, 2) + " days</abbr>";
}
daysAbsent_UnexplainedCell.VerticalAlign = VerticalAlign.Top;
daysAbsent_UnexplainedCell.BorderColor = System.Drawing.ColorTranslator.FromHtml("#E8ADAA");
newRow.Cells.Add(daysAbsent_UnexplainedCell);
TableCell blocksAbsent_UnexplainedCell = new TableCell();
blocksAbsent_UnexplainedCell.Text = unExplainedAbsences.Count + " blocks";
blocksAbsent_UnexplainedCell.VerticalAlign = VerticalAlign.Top;
blocksAbsent_UnexplainedCell.BorderColor = System.Drawing.ColorTranslator.FromHtml("#E8ADAA");
newRow.Cells.Add(blocksAbsent_UnexplainedCell);
// Explained absences
/*
* TableCell daysAbsent_ExplainedCell = new TableCell();
* if (daysAbsent_Explained == 0)
* {
* daysAbsent_ExplainedCell.Text = "<i style=\"color: #707070;\">No Absences</i>";
* }
* else if (daysAbsent_Explained >= 0)
* {
* daysAbsent_ExplainedCell.Text = "<abbr title=\"" + calculationExplaination_Explained + "\">" + Math.Round(daysAbsent_Explained, 2) + " days</abbr>";
* }
* daysAbsent_ExplainedCell.VerticalAlign = VerticalAlign.Top;
* daysAbsent_ExplainedCell.BorderColor = System.Drawing.ColorTranslator.FromHtml("#C3FDB8");
* newRow.Cells.Add(daysAbsent_ExplainedCell);
*
* TableCell blocksAbsent_ExplainedCell = new TableCell();
* blocksAbsent_ExplainedCell.Text = explainedAbsences.Count + " blocks";
* blocksAbsent_ExplainedCell.VerticalAlign = VerticalAlign.Top;
* blocksAbsent_ExplainedCell.BorderColor = System.Drawing.ColorTranslator.FromHtml("#C3FDB8");
* newRow.Cells.Add(blocksAbsent_ExplainedCell);
*/
// total absences
TableCell daysAbsent_TotalCell = new TableCell();
if (daysAbsent_Total == 0)
{
daysAbsent_TotalCell.Text = "<i style=\"color: #707070;\">No Absences</i>";
}
else if (daysAbsent_Total >= 0)
{
daysAbsent_TotalCell.Text = "<abbr title=\"" + calculationExplaination_Total + "\">" + Math.Round(daysAbsent_Total, 2) + " days</abbr>";
}
daysAbsent_TotalCell.VerticalAlign = VerticalAlign.Top;
newRow.Cells.Add(daysAbsent_TotalCell);
TableCell blocksAbsent_TotalCell = new TableCell();
blocksAbsent_TotalCell.Text = student.absences.Count + " blocks";
blocksAbsent_TotalCell.VerticalAlign = VerticalAlign.Top;
newRow.Cells.Add(blocksAbsent_TotalCell);
TableCell dateRegisterCell = new TableCell();
dateRegisterCell.Text = student.getEnrollDate().Month + "/" + student.getEnrollDate().Day + "/" + student.getEnrollDate().Year;
dateRegisterCell.VerticalAlign = VerticalAlign.Top;
newRow.Cells.Add(dateRegisterCell);
TableCell absenceLinkCell = new TableCell();
absenceLinkCell.Text = "<a href=\"../Attendance/getAttendance.aspx?from_year=" + startDate.Year + "&from_month=" + startDate.Month + "&from_day=" + startDate.Day + "&to_year=" + endDate.Year + "&to_month=" + endDate.Month + "&to_day=" + endDate.Day + "&studentid=" + student.getStudentID() + "\" TARGET=\"_blank\">View absences</a>";
absenceLinkCell.VerticalAlign = VerticalAlign.Top;
newRow.Cells.Add(absenceLinkCell);
return(newRow);
}
protected void Page_Load(object sender, EventArgs e)
{
DateTime startDate = DateTime.Today;
DateTime endDate = DateTime.Today;
/* Get data from the query string */
if (!String.IsNullOrEmpty(Request.QueryString["schoolid"]))
{
if ((!String.IsNullOrEmpty(Request.QueryString["from_year"])) || (!String.IsNullOrEmpty(Request.QueryString["from_month"])) || (!String.IsNullOrEmpty(Request.QueryString["from_day"])))
{
if ((!String.IsNullOrEmpty(Request.QueryString["to_year"])) || (!String.IsNullOrEmpty(Request.QueryString["to_month"])) || (!String.IsNullOrEmpty(Request.QueryString["to_day"])))
{
// Parse school
int selectedSchoolID = -1;
if (int.TryParse(Request.QueryString["schoolid"], out selectedSchoolID))
{
// Parse from date
int startYear = -1;
int startMonth = -1;
int startDay = -1;
int.TryParse(Request.QueryString["from_year"], out startYear);
int.TryParse(Request.QueryString["from_month"], out startMonth);
int.TryParse(Request.QueryString["from_day"], out startDay);
// Parse to date
int endYear = -1;
int endMonth = -1;
int endDay = -1;
int.TryParse(Request.QueryString["to_year"], out endYear);
int.TryParse(Request.QueryString["to_month"], out endMonth);
int.TryParse(Request.QueryString["to_day"], out endDay);
if (
!(
(startYear == -1) ||
(startMonth == -1) ||
(startDay == -1) ||
(endYear == -1) ||
(endMonth == -1) ||
(endDay == -1)
)
)
{
if (startDay > DateTime.DaysInMonth(startYear, startMonth))
{
startDay = DateTime.DaysInMonth(startYear, startMonth);
}
if (endDay > DateTime.DaysInMonth(endYear, endMonth))
{
endDay = DateTime.DaysInMonth(endYear, endMonth);
}
startDate = new DateTime(startYear, startMonth, startDay);
endDate = new DateTime(endYear, endMonth, endDay);
// Grab data
using (SqlConnection connection = new SqlConnection(dbConnectionString))
{
School selectedSchool = School.loadThisSchool(connection, selectedSchoolID);
if (selectedSchool != null)
{
List <Student> DisplayedStudents = LSKY_INAC.loadStudentData(connection, selectedSchool, startDate, endDate);
// Output a CSV file
sendCSV(GenerateCSV(DisplayedStudents), "INAC_" + LSKYCommon.removeSpaces(selectedSchool.getName()) + "_" + startDate.Year + "-" + startDate.Month + "-" + startDate.Day + "_" + endDate.Year + "-" + endDate.Month + "-" + endDate.Day);
}
else
{
Response.Write("Invalid school specified");
}
}
}
else
{
Response.Write("Invalid date specified<br>");
Response.Write(" From Year: " + startYear + "<br>");
Response.Write(" From Month: " + startMonth + "<br>");
Response.Write(" From Day: " + startDay + "<br>");
Response.Write(" To Year: " + endYear + "<br>");
Response.Write(" To Month: " + endMonth + "<br>");
Response.Write(" To Day: " + endDay + "<br>");
}
}
else
{
Response.Write("Invalid school ID");
}
}
else
{
Response.Write("\"To\" data not valid");
}
}
else
{
Response.Write("\"From\" data not valid");
}
}
else
{
Response.Write("School ID not specified");
}
Response.End();
}
