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(); }