//Get a single appointment by ID public userAppointment getAppointment(int dbID) { userAppointment userApp = new userAppointment(); if (this.OpenConnection() == true) { using (MySqlCommand dbCMD = new MySqlCommand()) { dbCMD.Connection = sqlConn; dbCMD.CommandText = $"SELECT `userID`,`appointmentDate`,`startTime`,`endTime`,`studentID`,`subject`,`specialProgram` FROM `appointmentLog` WHERE `id`={dbID}"; using (MySqlDataReader rdr = dbCMD.ExecuteReader()) { rdr.Read(); userApp.dbID = dbID; userApp.userID = Convert.ToInt32(rdr[0]); userApp.appointmentDate = Convert.ToDateTime(rdr[1]); userApp.startTime = Convert.ToDateTime(rdr[2].ToString()); userApp.endTime = Convert.ToDateTime(rdr[3].ToString()); userApp.studentID = (rdr[4] == DBNull.Value) ? 0 : Convert.ToInt32(rdr[4].ToString()); userApp.subject = (rdr[5] == DBNull.Value) ? "NO SUBJECT ENTERED" : Convert.ToString(rdr[5].ToString()); userApp.specialProgram = rdr[6].ToString(); } } } this.CloseConnect(); return(userApp); }
//----------------------------------------------------------------------------------- //ALL FUNCTIONS FOR EDITING, INSERTING, DELETEING AND UPDATING USERS AND APPOINTMENTS //Insert new Appointment from Admin program public bool InsertAppointmentAdmin(userAppointment userApp) { if (this.OpenConnection() == true) { using (MySqlCommand dbCMD = new MySqlCommand()) { dbCMD.Connection = sqlConn; dbCMD.CommandText = "INSERT INTO `appointmentLog`(`userID`, `appointmentDate`, `subject`, `studentID`, `startTime`, `endTime`, `specialProgram`, `adminInsert`) " + $"VALUES (\"{userApp.userID}\",\"{userApp.appointmentDate.ToString("yyyy-MM-dd")}\",\"{userApp.subject}\",\"{userApp.studentID}\"," + $"\"{userApp.startTime.ToString("HH:mm:ss")}\",\"{userApp.endTime.ToString("HH:mm:ss")}\",\"{userApp.specialProgram}\",1)"; dbCMD.ExecuteNonQuery(); } return(true); } return(false); }
//Update a single appointment by ID public bool updateAppointment(userAppointment app) { bool successfulInsert = false; if (this.OpenConnection() == true) { using (MySqlCommand dbCMD = new MySqlCommand()) { dbCMD.Connection = sqlConn; dbCMD.CommandText = $"UPDATE `appointmentLog` SET " + $"`appointmentDate`=\"{app.appointmentDate.Date.ToString("yyyy-MM-dd")}\", `startTime`=\"{app.startTime.ToString("HH:mm:ss")}\", `endTime`=\"{app.endTime.ToString("HH:mm:ss")}\"," + $"`studentID`=\"{app.studentID}\", `subject`=\"{app.subject}\", `specialProgram`=\"{app.specialProgram}\" " + $"WHERE `id`={app.dbID}"; dbCMD.ExecuteNonQuery(); return(true); } } return(successfulInsert); }
//Checking to see if current tutor being checked was the first one to have an appointment with a tutee //Maybe change from bool to something else cuz if their the only person to have appointment with tutee //But different times can't really show that on budget report correctly. //Returns a string with True/false: (and if they are second appoint in the day) //A true return means they get paid by EOPS. public string EOPSAppointmentCheck(userAppointment app, userInfo user) { List <userAppointment> checkHrLog = new List <userAppointment>(); if (this.OpenConnection() == true) { //Added DRC to this becuase DRC acts as EOPS now too. if (app.specialProgram.Contains("EOPS") || app.specialProgram.Contains("DRC")) { //Checking to see if tutee had only one appointment for a subject in a day MM/dd/yyyy using (MySqlCommand dbCMD = new MySqlCommand()) { string tempS = app.appointmentDate.ToString("yyyy-MM-dd"); dbCMD.Connection = sqlConn; dbCMD.CommandText = $"SELECT COUNT(*) FROM `appointmentLog` WHERE `studentID`={app.studentID}" + $" AND `subject`=\"{app.subject}\" AND `appointmentDate`=\"{app.appointmentDate.ToString("yyyy-MM-dd")}\"" + $" AND `specialProgram` LIKE \"%{app.specialProgram}%\""; if (Convert.ToInt32(dbCMD.ExecuteScalar()) >= 1) { dbCMD.Connection = sqlConn; dbCMD.CommandText = $"SELECT * FROM `appointmentLog` WHERE `studentID`={app.studentID}" + $" AND `subject`=\"{app.subject}\" AND `appointmentDate`=\"{app.appointmentDate.ToString("yyyy-MM-dd")}\"" + $" AND `specialProgram` LIKE \"%{app.specialProgram}%\""; using (MySqlDataReader rdr = dbCMD.ExecuteReader()) { while (rdr.Read()) { userAppointment tempRow = new userAppointment(); tempRow.userID = Convert.ToInt32(rdr[1]); tempRow.appointmentDate = new DateTime(Convert.ToDateTime(rdr[2].ToString()).Year, Convert.ToDateTime(rdr[2].ToString()).Month, Convert.ToDateTime(rdr[2].ToString()).Day, TimeSpan.Parse(rdr[5].ToString()).Hours, TimeSpan.Parse(rdr[5].ToString()).Minutes, TimeSpan.Parse(rdr[5].ToString()).Seconds); tempRow.subject = rdr[3].ToString(); tempRow.studentID = (rdr[4] == null) ? 0 : Convert.ToInt32(rdr[4]); tempRow.startTime = Convert.ToDateTime(rdr[5].ToString()); tempRow.endTime = Convert.ToDateTime(rdr[6].ToString()); tempRow.loggedTime = Convert.ToDateTime(rdr[7].ToString()); tempRow.specialProgram = rdr[8].ToString(); checkHrLog.Add(tempRow); } } checkHrLog.Sort((x, y) => DateTime.Compare(x.appointmentDate, y.appointmentDate)); //Logic the check who gets paid by EOPS /* * Return String meanings * 1ST2HR - First appointment for tutee of day was a 2hr appointment EOPS. * 2ND2HR - Second appointment for tutee of day was 2hr appointment with EOPS. * 2ND1HR - Tutor was the second appointment for tutee for the day. Gets paid by EOPS. * ERRORENT - Used if the tutor logged time in and didn't do a 2hr appoint and logged a 2hr as two different appointments. */ if (checkHrLog.Count >= 3) { if (checkHrLog[1].userID == user.userID) { this.CloseConnect(); return("2ND1HR"); } } else if (checkHrLog.Count == 2) { if ((checkHrLog[0].userID == checkHrLog[1].userID) && (checkHrLog[0].studentID == checkHrLog[1].studentID)) { this.CloseConnect(); return("ERRORENT"); } else if (checkHrLog[1].userID == user.userID) { this.CloseConnect(); return("2ND1HR"); } } } } } } this.CloseConnect(); return("FAILED TO CONNECT TO DB!"); }
//Gets user appointment info from supplyed Month. - Working again. public List <userAppointment> getUserLog(userInfo user, DateTime startOfMonth, DateTime endOfMonth) { List <userAppointment> appRows = new List <userAppointment>(); if (this.OpenConnection() == true) { using (MySqlCommand dbCMD = new MySqlCommand()) { dbCMD.Connection = sqlConn; dbCMD.CommandText = $"SELECT COUNT(*) FROM appointmentLog WHERE userID={user.userID} AND " + $"(appointmentDate BETWEEN '{startOfMonth.ToString("yyyy-MM-dd")}' AND '{endOfMonth.ToString("yyyy-MM-dd")}')"; if (Convert.ToInt32(dbCMD.ExecuteScalar().ToString()) >= 1) { dbCMD.Connection = sqlConn; dbCMD.CommandText = $"SELECT * FROM appointmentLog WHERE userID={user.userID} AND " + $"(appointmentDate BETWEEN '{startOfMonth.ToString("yyyy-MM-dd")}' AND '{endOfMonth.ToString("yyyy-MM-dd")}')"; using (MySqlDataReader rdr = dbCMD.ExecuteReader()) { while (rdr.Read()) { userAppointment tempRow = new userAppointment(); tempRow.dbID = Convert.ToInt32(rdr[0]); tempRow.userID = Convert.ToInt32(rdr[1]); tempRow.appointmentDate = new DateTime(Convert.ToDateTime(rdr[2].ToString()).Year, Convert.ToDateTime(rdr[2].ToString()).Month, Convert.ToDateTime(rdr[2].ToString()).Day, TimeSpan.Parse(rdr[5].ToString()).Hours, TimeSpan.Parse(rdr[5].ToString()).Minutes, TimeSpan.Parse(rdr[5].ToString()).Seconds); tempRow.subject = rdr[3].ToString(); tempRow.studentID = (rdr[4] == DBNull.Value) ? 0 : Convert.ToInt32(rdr[4]); //Crashing here~ tempRow.startTime = Convert.ToDateTime(rdr[5].ToString()); tempRow.endTime = (rdr[6].ToString() == "") ? Convert.ToDateTime(rdr[5].ToString()) : Convert.ToDateTime(rdr[6].ToString()); tempRow.loggedTime = Convert.ToDateTime(rdr[7].ToString()); tempRow.specialProgram = rdr[8].ToString(); appRows.Add(tempRow); } } this.CloseConnect(); return(appRows); } else { userAppointment tempRow = new userAppointment(); tempRow.subject = "No Appointments for the month"; appRows.Add(tempRow); this.CloseConnect(); return(appRows); } } } else { MessageBox.Show("Couldn't Connect to datebase!"); userAppointment tempRow = new userAppointment(); tempRow.subject = "Couldn't connect to Datebase."; appRows.Add(tempRow); this.CloseConnect(); return(appRows); } }