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