public void loadvisits(int studentID, DateTime minDate, DateTime maxDate)
{
min = minDate;
max = maxDate;
ID = studentID;
//clears the list box to enter new information
listBoxEditVisit.Items.Clear();
//TAB THIS
listBoxEditVisit.Items.Add("DATE".PadRight(15) + "\t" + "FIRST NAME".PadRight(30) + "\t" + "LAST NAME".PadRight(30) + "\t" + "ID".PadRight(12)+ "\t" + "TIME IN".PadRight(10)+"\t" + "TIME OUT".PadRight(10) + "\t" + "METHOD".PadRight(20) + "\t" + "TUTOR'S FIRST NAME".PadRight(30) + "TUTOR'S LAST NAME".PadRight(30)+ "\t" + "SUBJECT" + "\t" + "CATALOG" + "\t" + "SECTION");
//creates new dataconnection
DataConnection conn = new DataConnection();
SqlDataReader rd;
conn.Open();
//gets visits request
//just added STUDENT.FIRSTNAME, STUDENT.LASTNAME, STUDENT TABLE
rd = conn.joinQuery("SELECT VISIT.CLARION_ID, VISIT.DATE, VISIT.TIME_IN, VISIT.TIME_OUT, STUDENT.FIRSTNAME, STUDENT.LASTNAME, VISIT.METHOD, TUTOR.TUTOR_ID, SUBJECT, CATALOG, S_TUTOR.FIRSTNAME AS TUTORFIRSTNAME, S_TUTOR.LASTNAME AS TUTORLASTNAME, SECTION FROM VISIT INNER JOIN student on visit.clarion_id = student.clarion_id LEFT JOIN TUTOR ON VISIT.TUTOR_ID = TUTOR.TUTOR_ID LEFT JOIN STUDENT S_TUTOR ON TUTOR.CLARION_ID = S_TUTOR.CLARION_ID WHERE visit.DATE<='" + maxDate + "' AND visit.DATE>='" + minDate + (studentID == 0 ? "'":"' AND VISIT.CLARION_ID = '" + studentID + "'") + " ORDER BY DATE, TIME_IN");
if (rd.HasRows)
{
while (rd.Read())
{
DateTime thedate = DateTime.Parse(rd["DATE"].ToString());
string TIMEOUT = rd["TIME_OUT"].ToString().PadRight(10);
if (TIMEOUT.Replace(" ","").Length !=8)
TIMEOUT = " ".PadRight(18);
listBoxEditVisit.Items.Add(thedate.ToString("d").PadRight(15) + "\t" + rd["FIRSTNAME"].ToString().PadRight(30) + "\t" + rd["LASTNAME"].ToString().PadRight(30) + "\t" + (int.Parse(rd["CLARION_ID"].ToString())).ToString("D8").PadRight(12) + "\t" + rd["TIME_IN"].ToString().PadRight(10) + "\t" + TIMEOUT.PadRight(10) + "\t" + rd["METHOD"].ToString().PadRight(20) + "\t" + rd["TUTORFIRSTNAME"].ToString().PadRight(30) + " " + rd["TUTORLASTNAME"].ToString().PadRight(30) + "\t" + rd["SUBJECT"].ToString().PadRight(5) + "\t" + ((rd["CATALOG"]).ToString()).PadRight(5) + "\t" + ((rd["SECTION"]).ToString()).PadRight(4));
}
}
rd.Close();
//closes connection
conn.Close();
if (studentID == 0)
{
conn.Open();
//gets visits request
//just added STUDENT.FIRSTNAME, STUDENT.LASTNAME, STUDENT TABLE
// rd = conn.joinQuery("SELECT VISIT.CLARION_ID, VISIT.DATE, VISIT.TIME_IN, VISIT.TIME_OUT, STUDENT.FIRSTNAME, STUDENT.LASTNAME, VISIT.METHOD, TUTOR.TUTOR_ID, SUBJECT, CATALOG, S_TUTOR.FIRSTNAME AS TUTORFIRSTNAME, S_TUTOR.LASTNAME AS TUTORLASTNAME, SECTION FROM VISIT INNER JOIN student on visit.clarion_id = student.clarion_id LEFT JOIN TUTOR ON VISIT.TUTOR_ID = TUTOR.TUTOR_ID LEFT JOIN STUDENT S_TUTOR ON TUTOR.CLARION_ID = S_TUTOR.CLARION_ID WHERE visit.DATE<='" + maxDate + "' AND visit.DATE>='" + minDate + (studentID == 0 ? "'" : "' AND VISIT.CLARION_ID = '" + studentID + "'") + " ORDER BY DATE, TIME_IN");
rd = conn.joinQuery("select tutor_hour.tutor_id, tutor_hour.date, tutor_hour.time_out ,tutor_hour.time_difference, tutor_hour.time_in, student.lastname, student.firstname from tutor_hour inner join tutor on tutor_hour.tutor_id = tutor.tutor_id inner join student on tutor.clarion_id = student.clarion_id where tutor_hour.DATE<='" + maxDate + "' AND tutor_hour.DATE>='" + minDate+"' ");
if (rd.HasRows)
{
while (rd.Read())
{
DateTime thedate = DateTime.Parse(rd["DATE"].ToString());
string TIMEOUT = rd["TIME_OUT"].ToString().PadRight(10);
if (TIMEOUT.Replace(" ", "").Length != 8)
TIMEOUT = " ".PadRight(18);
listBoxEditVisit.Items.Add(thedate.ToString("d").PadRight(15) + "\t" + rd["FIRSTNAME"].ToString().PadRight(30) + "\t" + rd["LASTNAME"].ToString().PadRight(30) + "\t" + ("TUT" + int.Parse(rd["tutor_ID"].ToString()).ToString("D4").PadRight(10)) + "\t" + rd["TIME_IN"].ToString().PadRight(10) + "\t" + rd["TIME_OUT"].ToString().PadRight(10) + "\t" + "TUTOR");
}
}
rd.Close();
//closes connection
conn.Close();
}
}
public void loadlist()
{
listBoxEnableTutors.Items.Clear();
listBoxDisableTutors.Items.Clear();
listBoxLoggedIn.Items.Clear();
DataConnection conn = new DataConnection();
SqlDataReader rd;
conn.Open();
try
{
rd = conn.GetReader("STUDENT.FIRSTNAME, STUDENT.LASTNAME, STUDENT.clarion_id, tutor.status", "TUTOR INNER JOIN STUDENT ON TUTOR.CLARION_ID=STUDENT.CLARION_ID");
if (rd.HasRows)
{
while (rd.Read())
{
if (rd[3].ToString() == "active")
listBoxEnableTutors.Items.Add(rd[0].ToString() + " " + rd[1].ToString() + " " + rd[2]);
else
listBoxDisableTutors.Items.Add(rd[0].ToString() + " " + rd[1].ToString() + " " + rd[2]);
}
}
listBoxLoggedIn.Items.Add("DATE\t\tTIME IN\t\tID\t\t" + "LAST NAME".PadRight(30) + "\tFIRST NAME");
}
catch
{
MessageBox.Show("Cannot load tutors", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
conn.Close();
conn.Open();
try
{
rd = conn.GetReader("*", "VISIT", "student", "visit.clarion_id=student.clarion_id and time_out is null", 1);
if (rd.HasRows)
{
while (rd.Read())
{
DateTime jdate = DateTime.Parse(rd[1].ToString());
listBoxLoggedIn.Items.Add(jdate.ToString("MM/dd/yyyy") + "\t" + rd[2] + "\t" + int.Parse(rd[0].ToString()).ToString("D8").PadRight(10) + "\t" + rd[13].ToString().PadRight(30) + "\t" + rd[14].ToString().PadRight(30));
}
}
}
catch
{
MessageBox.Show("Cannot load currently logged in visits", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
conn.Close();
conn.Open();
try
{
rd = conn.joinQuery("select tutor_hour.tutor_id, tutor_hour.date ,tutor_hour.time_difference, tutor_hour.time_in, student.lastname, student.firstname from tutor_hour inner join tutor on tutor_hour.tutor_id = tutor.tutor_id inner join student on tutor.clarion_id = student.clarion_id where time_difference is null");
//rd = conn.GetReader("*", "tutor_hour", "student", "tutor_hour.clarion_id=student.clarion_id and time_out is null", 1);
if (rd.HasRows)
{
while (rd.Read())
{
DateTime jdate = DateTime.Parse(rd[1].ToString());
listBoxLoggedIn.Items.Add(jdate.ToString("MM/dd/yyyy") + "\t" + rd[3] + "\t" + ("TUT" + int.Parse(rd[0].ToString()).ToString("D5")).PadRight(10) + "\t" + rd[4].ToString().PadRight(30) + "\t" + rd[5].ToString().PadRight(30));
}
}
}
catch
{
MessageBox.Show("Cannot load currently logged in visits", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
conn.Close();
}
//Created by Sean: button1_Click inside the Reporting Tab
private void displayBtn_Click(object sender, EventArgs e)
{
//TODO: This should add a placeholder to the listbox that represents
// the data that will be placed into the excel file
string column, table, condition = "", row = "", first="", last="";
DataConnection conn = new DataConnection();
SqlDataReader rd;
int year = 0, term=0, count = 0;
bool y = int.TryParse(txtYear.Text, out year);
term = DataConnection.getTerm(year, comboTerm.SelectedItem.ToString());
conn.Open();
rd = conn.GetReader("*", "VISIT", "WHERE TERM = '" + term.ToString() + "'");
if (!(rd.HasRows))
{
conn.Close();
return;
}
conn.Close();
conn.Open();
/*
term += (int.Parse(year.ToString())/1000)*1000;
term += (int.Parse(year.ToString()) % 100) * 10;
if (comboTerm.SelectedItem.ToString() == "Winter")
term += 9;
else if (comboTerm.SelectedItem.ToString() == "Spring")
term += 1;
else if (comboTerm.SelectedItem.ToString() == "Summer")
term += 5;
else
term += 8;
*/
switch (comboCountCategory.SelectedItem.ToString())
{
case "Method":
column = "method, COUNT(DISTINCT CLARION_ID), term";
table = "VISIT";
//MessageBox.Show(comboFilter.SelectedItem.ToString());
condition = " where term = '" + term + "' GROUP BY METHOD, term";
listBoxReport.Items.Add( "Method".PadRight(30) + "\t" + "Number of Students");
if (comboFilter.SelectedItem.ToString() == "All")
{
// MessageBox.Show("wind");
rd = conn.GetReader(column, table, condition);
while (rd.Read())
{
// for (int i = 0; i < 2; i++)
{
row += rd[0].ToString().PadRight(30) + "\t" +rd[1];
// MessageBox.Show((80 - rd[0].ToString().Length).ToString());
}
//MessageBox.Show("1".PadLeft(80-(rd[0].ToString().Length*1)));
// MessageBox.Show(string.Format("{0,-50} {1,60}", rd[0].ToString(), rd[1].ToString()));h
listBoxReport.Items.Add(row);
//listBoxReport.Items.Add(new object[] { rd[0], rd[1] });
row = "";
//MessageBox.Show(row);
}
}
else
{
rd = conn.GetReader("method, COUNT(DISTINCT CLARION_ID), term", "visit", " where term = '" + term.ToString() + "' and method = '" + comboFilter.SelectedItem.ToString() + "' GROUP BY METHOD, term ");
if (rd.HasRows)
{
while (rd.Read())
{
for (int i = 0; i < 2; i++)
row += rd[i].ToString().PadRight(30) + "\t";
listBoxReport.Items.Add(row);
row = "";
}
}
else
listBoxReport.Items.Add(comboFilter.SelectedItem.ToString().PadRight(30) + "\t0");
}
listBoxReport.Items.Add("");
/*
//condition = " method " + " = "+ " '"+"other"+"' ";
filterColumn = "METHOD";
* */
break;
case "Student":
int newid = -1;
int nontradcount = 0;
int studentcount = 0;
count = 0;
TimeSpan newtime = new TimeSpan();
if (comboFilter.SelectedItem.ToString() == "All" || comboFilter.SelectedItem.ToString() == "Total Hours")
{
listBoxReport.Items.Add("Student Name".PadRight(20) + "\t" + "".PadRight(20) + "\t" + "Total Hours");
// rd = conn.GetReader(column, table, condition);
rd = conn.joinQuery("select visit.clarion_id, visit.time_difference, visit.term, student.lastname, student.firstname, student.age from visit inner join student on visit.clarion_id = student.clarion_id where time_difference is not null and visit.term = '" + term.ToString() + "'");
while (rd.Read())
{
if (newid == int.Parse(rd[0].ToString()))
{
newtime += TimeSpan.Parse(rd[1].ToString());
}
else
{
if (newid != -1)
{
listBoxReport.Items.Add(first.PadRight(20) + "\t" + last.PadRight(20) + "\t" + newtime);
studentcount++;
}
newid = int.Parse(rd[0].ToString());
newtime = TimeSpan.Parse(rd[1].ToString());
first = rd[4].ToString();
last = rd[3].ToString();
try
{
if (int.Parse(rd["student.age"].ToString()) >= 24)
{
first = "* " + rd[4].ToString();
nontradcount++;
}
}
catch
{
}
}
}
if (newid != -1)
{
studentcount++;
listBoxReport.Items.Add(first.PadRight(20 ) + "\t" + last.PadRight(20 ) + "\t" + newtime);
}
if (comboFilter.SelectedItem.ToString() == "All")
listBoxReport.Items.Add("");
}
if (comboFilter.SelectedItem.ToString() == "All" || comboFilter.SelectedItem.ToString() == "Visits")
{
newid = -1;
count = 0;
nontradcount = 0;
studentcount = 0;
listBoxReport.Items.Add("Student Name".PadRight(20) + "\t" + "".PadRight(20) + "\tNumber of Visits");
column = "CLARION_ID,count(distinct time_difference), term";
table = "VISIT";
condition = "where time_difference is not null and term = '"+term.ToString()+"' group by clarion_id, term";
// rd = conn.GetReader(column, table, condition);
rd = conn.joinQuery("select visit.clarion_id, visit.time_difference, visit.term, student.lastname, student.firstname, student.age from visit inner join student on visit.clarion_id = student.clarion_id where time_difference is not null and visit.term = '" + term.ToString() + "'");
while (rd.Read())
{
if (newid == int.Parse(rd[0].ToString()))
{
count++;
}
else
{
if (newid != -1)
{
studentcount++;
listBoxReport.Items.Add(first.PadRight(20 ) + "\t" + last.PadRight(20) + "\t" + count);
}
newid = int.Parse(rd[0].ToString());
count = 1;
first = rd[4].ToString();
last = rd[3].ToString();
try
{
if (int.Parse(rd[5].ToString()) >= 25)
{
first = "* " + rd[4].ToString();
nontradcount++;
}
}
catch
{
}
}
}
if (newid != -1)
{
studentcount++;
listBoxReport.Items.Add(first.PadRight(20 ) + "\t" + last.PadRight(20 ) + "\t" + count);
listBoxReport.Items.Add("Nontraditional Students".PadRight(30) + "\t" + nontradcount);
listBoxReport.Items.Add("Traditional Students".PadRight(30) + "\t" + (studentcount-nontradcount));
}
}
listBoxReport.Items.Add("");
break;
case "Tutor":
newid = -1;
count = 0;
newtime = new TimeSpan();
column = "tutor_ID, time_difference, term ";
table = "VISIT";
condition = "where time_difference is not null and tutor_id is not null and term = '" + term.ToString() + "' ORDER BY tutor_id";
if (comboFilter.SelectedItem.ToString() == "All" || comboFilter.SelectedItem.ToString() == "Hours Tutoring")
{
newid = -1;
count = 0;
listBoxReport.Items.Add("Tutor Name".PadRight(20) + "\t" + "".PadRight(20) + "\tTime Worked");
// rd = conn.GetReader(column, table, condition);
rd = conn.joinQuery("select visit.tutor_id, tutor.clarion_id, visit.time_difference, visit.term, student.lastname, student.firstname from visit inner join tutor on visit.tutor_id = tutor.tutor_id inner join student on tutor.clarion_id = student.clarion_id where time_difference is not null and visit.term = '" + term.ToString() + "' order by visit.tutor_id");
while (rd.Read())
{
if (newid == int.Parse(rd[0].ToString()))
{
newtime += TimeSpan.Parse(rd[2].ToString());
}
else
{
if (newid != -1)
listBoxReport.Items.Add(first.PadRight(20 ) + "\t" + last.PadRight(20 ) + "\t" + newtime);
newid = int.Parse(rd[0].ToString());
newtime = TimeSpan.Parse(rd[2].ToString());
first = rd[5].ToString();
last = rd[4].ToString();
}
}
if (newid != -1)
listBoxReport.Items.Add(first.PadRight(20 ) + "\t" + last.PadRight(20 ) + "\t" + newtime);
// listBoxReport.Items.Add(newid.ToString().PadRight(60 - newid.ToString().Length) + "\t" + newtime);
listBoxReport.Items.Add("");
}
if (comboFilter.SelectedItem.ToString() == "All" || comboFilter.SelectedItem.ToString() == "Students Tutored")
{
newid = -1;
count = 0;
column = "tutor_ID, count(distinct time_difference), term";
table = "VISIT";
condition = "where time_difference is not null and tutor_id is not null and term = '" + term.ToString() + "' group by tutor_id, term";
listBoxReport.Items.Add("Tutor Name".PadRight(20) + "\t" + "".PadRight(20) + "\tStudents Tutored");
rd = conn.joinQuery("select visit.tutor_id, tutor.clarion_id, visit.time_difference, visit.term, student.lastname, student.firstname from visit inner join tutor on visit.tutor_id = tutor.tutor_id inner join student on tutor.clarion_id = student.clarion_id where time_difference is not null and visit.term = '" + term.ToString() + "' order by visit.tutor_id");
while (rd.Read())
{
if (newid == int.Parse(rd[0].ToString()))
{
count++;
}
else
{
if (newid != -1)
listBoxReport.Items.Add(first.PadRight(20 ) + "\t" + last.PadRight(20 ) + "\t" + count);
newid = int.Parse(rd[0].ToString());
count = 1;
first = rd[5].ToString();
last = rd[4].ToString();
}
}
if (newid != -1)
listBoxReport.Items.Add(first.PadRight(20) + "\t" + last.PadRight(20) + "\t" + count);
}
listBoxReport.Items.Add("");
break;
case "Course":
column = "SUBJECT, catalog, COUNT(*), term";
table = "VISIT";
count = 0;
condition = "where term = '"+term.ToString()+"' GROUP BY SUBJECT, catalog, term";
if (comboFilter.SelectedItem.ToString() == "All")
{
listBoxReport.Items.Add("Subject" + "\t" + "Catalog" + "\t" + "Number of Visits");
rd = conn.GetReader(column, table, condition);
while (rd.Read())
{
for (int i = 0; i < 3; i++)
row += rd[i].ToString().PadRight(5) + "\t";
listBoxReport.Items.Add(row);
row = "";
}
}
else if (comboFilter.SelectedItem.ToString() == "Total Courses")
{
rd = conn.GetReader(column, table, condition);
while (rd.Read())
{
//for (int i = 0; i < rd.FieldCount; i++)
count++;
}
listBoxReport.Items.Add("Total Courses ".PadRight(30) + "\t" + count);
}
else
{
listBoxReport.Items.Add("Subject" + "\t" + "Catalog" + "\t" + "Number of Visits");
condition = "where term = '" + term.ToString() + "' and subject = '" + comboFilter.SelectedItem.ToString() + "' GROUP BY SUBJECT, catalog, term";
rd = conn.GetReader(column, table, condition);
if (rd.HasRows)
{
while (rd.Read())
{
for (int i = 0; i < 3; i++)
row += rd[i].ToString().PadRight(5) + "\t";
listBoxReport.Items.Add(row);
row = "";
}
}
}
listBoxReport.Items.Add("");
break;
default:
column = "-1";
table = "-1";
break;
}
/*
if (!((column == "-1") && (table == "-1")))
if (comboFilter.SelectedIndex == 0)
if (condition != "")
{
MessageBox.Show("sfgfdsgfg");
rd = conn.GetReader(column, table, condition);
}
else
rd = conn.GetReader(column, table);
else
if (condition != "")
{
rd = conn.GetReader(column, table, filterColumn, comboFilter.SelectedItem.ToString(), condition);
}
else
{
MessageBox.Show("");
rd = conn.GetReader(column, table);
}
else
return;
while (rd.Read())
{
for (int i = 0; i < rd.FieldCount; i++)
row += rd[i] + "\t";
listBoxReport.Items.Add(row);
listBoxReport.Items.Add("");
row = "";
}
// */
/*
if (comboCountCategory.SelectedItem.ToString() == "Method" && comboGroup.SelectedItem.ToString() == "Tutoring")
{
}
else if (comboCountCategory.SelectedItem.ToString() == "Total Tutors")
{
}
else if (comboCountCategory.SelectedItem.ToString() == "Total Tutor Hours")
{
selectString = "SELECT dateadd(second, SUM(DATEPART(SECOND, TIME_DIFFERENCE)), 108) FROM TUTOR_HOUR";
}
*/
}
//Adds Tutor to the list of tutors via Student ID and adds their information to the Tutors table
private void btnAddTutor_Click(object sender, EventArgs e)
{
/////////////// edit table so only clarion id, status and cnet_username are used
//bool valid = false;
//Gets the student id
string studentID = txtTutorStudentID.Text;
/*cn.Open();
cmd.CommandText = "select * from student where CLARION_ID=" + studentID;
rd = cmd.ExecuteReader();
*/
try
{
bool notInDB = frmMain.studentIDExists(int.Parse(studentID));
DataConnection conn = new DataConnection();
conn.Open();
SqlDataReader rd = conn.joinQuery("select clarion_id from tutor where clarion_id = " + studentID);
if (rd.HasRows)
{
notInDB = false;
}
conn.Close();
if (notInDB)
{
conn.Open();
conn.Query("insert into tutor(clarion_id,status) values ('" + studentID + "', '" + "active" + "')");
conn.Close();
}
else
MessageBox.Show("Tutor is already in database.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch
{
MessageBox.Show("Error while searching for student ID. Please check to see if it is valid.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
loadlist();
}
