public List <StudentEntry> GetAllStudentsByClass(int classId)
{
query = "SELECT * FROM tbl_students WHERE class_id = '" + classId + "'";
connection.Open();
command = new SqlCommand(query, connection);
reader = command.ExecuteReader();
List <StudentEntry> allStudents = new List <StudentEntry>();
while (reader.Read())
{
StudentEntry aStudentEntry = new StudentEntry();
aStudentEntry.Id = Convert.ToInt32(reader["id"]);
aStudentEntry.ClassId = Convert.ToInt32(reader["class_id"]);
aStudentEntry.Roll = reader["roll"].ToString();
aStudentEntry.Name = reader["name"].ToString();
aStudentEntry.Phone = reader["phone"].ToString();
aStudentEntry.OrderBy = Convert.ToInt32(reader["order_by"]);
allStudents.Add(aStudentEntry);
}
reader.Close();
connection.Close();
return(allStudents);
}
public IHttpActionResult PutStudent(int id, StudentEntry student)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
var stud = db.Student.Find(id);
if (stud == null)
{
return(NotFound());
}
stud.ticketNumber = student.TicketNumber ?? stud.ticketNumber; // Changed this on 10/10. Added ?? stud.ticketNumber
stud.studentFirstName = student.StudentFirstName ?? stud.studentFirstName;
stud.studentLastName = student.StudentLastName ?? stud.studentLastName;
// stud.studentDOB = student.StudentDob; When are you ever going to update a students DOB?????
db.Entry(stud).State = EntityState.Modified;
try
{
db.SaveChanges();
}
catch (DbUpdateConcurrencyException)
{
if (!StudentExists(id))
{
return(NotFound());
}
throw;
}
return(Ok(stud));
}
public List <StudentEntry> GetAllStudents()
{
query = "SELECT tbl_students.*, tbl_classes.name AS class_name FROM tbl_students" +
" LEFT JOIN tbl_classes ON tbl_classes.id = tbl_students.class_id" +
" ORDER BY class_name ASC";
connection.Open();
command = new SqlCommand(query, connection);
reader = command.ExecuteReader();
List <StudentEntry> allStudents = new List <StudentEntry>();
while (reader.Read())
{
StudentEntry aStudentEntry = new StudentEntry();
aStudentEntry.Id = Convert.ToInt32(reader["id"]);
aStudentEntry.ClassId = Convert.ToInt32(reader["class_id"]);
aStudentEntry.Roll = reader["roll"].ToString();
aStudentEntry.Name = reader["name"].ToString();
aStudentEntry.Phone = reader["phone"].ToString();
aStudentEntry.OrderBy = Convert.ToInt32(reader["order_by"]);
aStudentEntry.ClassName = reader["class_name"].ToString();
allStudents.Add(aStudentEntry);
}
reader.Close();
connection.Close();
return(allStudents);
}
public StudentEntry GetStudentInfoById(int studentId)
{
query = "SELECT tbl_students.*, tbl_classes.name AS class_name FROM tbl_students " +
"LEFT JOIN tbl_classes ON tbl_classes.id = tbl_students.class_id " +
"WHERE tbl_students.id = '" + studentId + "'";
connection.Open();
command = new SqlCommand(query, connection);
reader = command.ExecuteReader();
StudentEntry studentInfo = new StudentEntry();
while (reader.Read())
{
studentInfo.Id = Convert.ToInt32(reader["Id"]);
studentInfo.Name = reader["name"].ToString();
studentInfo.Phone = reader["phone"].ToString();
studentInfo.Roll = reader["roll"].ToString();
studentInfo.ClassName = reader["class_name"].ToString();
}
reader.Close();
connection.Close();
return(studentInfo);
}
public ActionResult getStudentResponses(StudentEntry studentinfo)
{
Student student = new Student
{
FirstName = studentinfo.firstName,
LastName = studentinfo.lastname,
PhoneNumber = studentinfo.phonenumber,
Email = studentinfo.email,
StudentGrade = studentinfo.studentgrade,
FirstParentFirstName = studentinfo.parent1firstname,
FirstParentPhoneNumber = studentinfo.parent1phonenumber,
FirstParentEmail = studentinfo.parent1email,
SecondParentFirstName = studentinfo.parent2firstname,
SecondParentPhoneNumber = studentinfo.parent2phonenumber,
SecondParentEmail = studentinfo.parent2email,
Date = studentinfo.date
};
try
{
StudentClient tableStorage = new StudentClient();
tableStorage.Add(student);
return(Content("ok"));
}
catch (Exception ex)
{
return(new HttpStatusCodeResult(HttpStatusCode.NotFound, ex.Message));
}
}
private void deleteButton_Click(object sender, EventArgs e)
{
StudentEntry aStudentEntry = new StudentEntry();
aStudentEntry.Id = Convert.ToInt32(studentIdTextBox.Text);
string message = aStudentEntryManager.DeleteStudent(aStudentEntry.Id);
MessageBox.Show(message);
LoadStudentEntryListview();
}
public int SaveStudent(StudentEntry aStudentEntry)
{
query = "INSERT INTO tbl_students (class_id, roll, name, phone, order_by) VALUES ('" + aStudentEntry.ClassId + "','" + aStudentEntry.Roll + "','" + aStudentEntry.Name + "','" + aStudentEntry.Phone + "','" + aStudentEntry.OrderBy + "')";
connection.Open();
command = new SqlCommand(query, connection);
int rowAffected = command.ExecuteNonQuery();
connection.Close();
return(rowAffected);
}
public int UpdatedStudent(StudentEntry aStudentEntry)
{
query = "UPDATE tbl_students SET class_id = '" + aStudentEntry.ClassId + "', roll = '" + aStudentEntry.Roll + "', name = '" + aStudentEntry.Name + "', phone = '" + aStudentEntry.Phone + "', order_by = '" + aStudentEntry.OrderBy + "' WHERE id = '" + aStudentEntry.Id + "'";
connection.Open();
command = new SqlCommand(query, connection);
int rowAffected = command.ExecuteNonQuery();
connection.Close();
return(rowAffected);
}
public IHttpActionResult PostStudent(StudentEntry studentEntry)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
var ticketExists = (from t in db.Ticket
where t.ticketNumber == studentEntry.TicketNumber
select t).Any();
if (!ticketExists)
{
var ticketController = new TicketsController();
ticketController.PostTicket(new TicketEntry()
{
ticketNumber = studentEntry.TicketNumber // Provides no support for ticketIsValid or lessonsRemaining
});
}
int nextStudentId = (db.Student
.OrderByDescending(n => n.studentId)
.Select(n => n.studentId)
.First()) + 1;
var newStudent = new Student
{
studentId = nextStudentId,
studentFirstName = studentEntry.StudentFirstName,
studentLastName = studentEntry.StudentLastName,
studentDOB = studentEntry.StudentDob,
ticketNumber = studentEntry.TicketNumber
};
db.Student.Add(newStudent);
try
{
db.SaveChanges();
}
catch (DbUpdateException)
{
if (StudentExists(newStudent.studentId))
{
return(BadRequest("That student id already exists"));
}
throw;
}
return(Ok(newStudent.studentId));
}
public bool IsStudentExists(StudentEntry aStudentEntry)
{
query = "SELECT * FROM tbl_students WHERE class_id = '" + aStudentEntry.ClassId + "' AND roll = '" + aStudentEntry.Roll + "' AND id <> '" + aStudentEntry.Id + "'";
connection.Open();
command = new SqlCommand(query, connection);
reader = command.ExecuteReader();
bool isExists = reader.HasRows;
reader.Close();
connection.Close();
return(isExists);
}
private void studentComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
if (studentComboBox.SelectedValue.ToString() != null)
{
int studentId = Convert.ToInt32(studentComboBox.SelectedValue);
StudentEntry studentInfo = aResultEntryManager.GetStudentInfoById(studentId);
nameLabel.Text = studentInfo.Name;
classLabel.Text = studentInfo.ClassName;
phoneLabel.Text = studentInfo.Phone;
rollLabel.Text = studentInfo.Roll;
}
}
private void searchButton_Click(object sender, EventArgs e)
{
int studenId = Convert.ToInt32(studentComboBox.SelectedValue);
StudentEntry studentInfo = aIndividualResultManager.GetStudentInfoById(studenId);
float average = aIndividualResultManager.GetResultAverage(studenId);
studentNameLabel.Text = studentInfo.Name;
classNameLabel.Text = studentInfo.ClassName;
rollLabel.Text = studentInfo.Roll;
averageLabel.Text = average.ToString("0.00");
LoadAllSubjectsResultListView(studenId);
}
public string UpdateStudent(StudentEntry aStudentEntry)
{
if (aStudentEntryGateway.IsStudentExists(aStudentEntry))
{
return("This Student Already Exists");
}
else
{
int rowAffected = aStudentEntryGateway.UpdatedStudent(aStudentEntry);
if (rowAffected > 0)
{
return("Student Updated Successfully");
}
return("Saved Failed");
}
}
private void studentListView_DoubleClick(object sender, EventArgs e)
{
ListViewItem selectedItem = studentListView.SelectedItems[0];
StudentEntry aStudentEntry = (StudentEntry)selectedItem.Tag;
if (aStudentEntry != null)
{
nameTextBox.Text = aStudentEntry.Name;
phoneTextBox.Text = aStudentEntry.Phone;
classComboBox.SelectedIndex = classComboBox.FindStringExact(aStudentEntry.ClassName);
rollTextBox.Text = aStudentEntry.Roll;
orderByTextBox.Text = aStudentEntry.OrderBy.ToString();
studentIdTextBox.Text = aStudentEntry.Id.ToString();
saveButton.Text = "Update";
}
deleteButton.Enabled = true;
}
public async Task <List <StudentEntry> > GetAllStudents()
{
var students = await _studentDataAccess.GetInitialStudents(1);
var initialStudents = new List <StudentEntry>();
var lastStudentId = -1;
foreach (var student in students)
{
if (student.StudentId != lastStudentId)
{
var createdStudent = new StudentEntry()
{
StudentId = student.StudentId,
FirstName = student.FirstName,
LastName = student.LastName,
Grade = student.Grade,
Entries = new List <Entry>()
};
initialStudents.Add(createdStudent);
}
if (student.EntryStudentId > 0)
{
// gives me the last student that was added
var currentStudent = initialStudents[initialStudents.Count - 1];
var entry = new Entry()
{
Id = student.EntryId,
CreatedByUserId = student.EntryCreatedByUserId,
CreatedAtDateUTC = student.EntryCreatedUTC,
StudentId = student.EntryStudentId,
Note = student.Note
};
currentStudent.Entries.Add(entry);
}
lastStudentId = student.StudentId;
}
return(initialStudents);
}
private void saveButton_Click(object sender, EventArgs e)
{
StudentEntry aStudentEntry = new StudentEntry();
aStudentEntry.ClassId = Convert.ToInt32(this.classComboBox.SelectedValue);
aStudentEntry.Roll = rollTextBox.Text;
aStudentEntry.Name = nameTextBox.Text;
aStudentEntry.Phone = phoneTextBox.Text;
aStudentEntry.OrderBy = Convert.ToInt32(orderByTextBox.Text);
if (saveButton.Text == "Save")
{
string message = aStudentEntryManager.SaveStudent(aStudentEntry);
MessageBox.Show(message);
}
else
{
aStudentEntry.Id = Convert.ToInt32(studentIdTextBox.Text);
string message = aStudentEntryManager.UpdateStudent(aStudentEntry);
MessageBox.Show(message);
}
LoadStudentEntryListview();
}
