public static void LogUser()
{
using (StreamWriter w = File.AppendText("log.txt"))
{
LogEntry($"UserId:\t{DataBaseHandler.GetCurrentUserId()}\n :Username:\t{DataBaseHandler.GetCurrentUserName()}", w);
}
}
private void customersWithAppointmentsBtn_Click(object sender, EventArgs e)
{
int userId = DataBaseHandler.GetCurrentUserId();
txtReport.Text = "";
using (MySqlConnection con = new MySqlConnection(CS))
{
//txtReport.Text = txtReport.Text + "\r";
MySqlCommand cmd = new MySqlCommand("SELECT DISTINCT customer.customerName " +
"FROM appointment INNER JOIN " +
$"customer ON appointment.customerId = customer.customerId WHERE appointment.userId = {userId}", con);
MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
adp.Fill(dt);
foreach (DataRow row in dt.Rows)
{
txtReport.Text = txtReport.Text +
"\t" + row[0].ToString() + "\r";/* + ")\t" +
* "\"" + row[0].ToString() + "\"" + "\r";*/
}
txtReport.Select(0, 0);
}
}
private void appointmentsByMonthBtn_Click(object sender, EventArgs e)
{
int userId = DataBaseHandler.GetCurrentUserId();
txtReport.Text = "";
using (MySqlConnection con = new MySqlConnection(CS))
{
string[] Months = new string[] { "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" };
int temp = 1;
foreach (string month in Months)
{
txtReport.Text = txtReport.Text + month + "\r";
MySqlCommand cmd = new MySqlCommand($"SELECT appointment.type, COUNT(*) FROM appointment WHERE month(start) = {temp++} AND appointment.userId = {userId} GROUP BY appointment.type", con);
MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
adp.Fill(dt);
foreach (DataRow row in dt.Rows)
{
txtReport.Text = txtReport.Text +
"\t(" + row[1].ToString() + ")\t" +
"\"" + row[0].ToString() + "\"" + "\r";
}
txtReport.Select(0, 0);
}
}
}
private void handleDay()
{
int userId = DataBaseHandler.GetCurrentUserId();
mainFormCalendar.RemoveAllBoldedDates();
mainFormCalendar.AddBoldedDate(currentDate);
mainFormCalendar.UpdateBoldedDates();
dt.Clear();
using (MySqlConnection con = new MySqlConnection(CS))
{
MySqlCommand cmd = new MySqlCommand($"SELECT appointment.appointmentId, customer.customerName, appointment.type, appointment.start, appointment.end " +
"FROM appointment INNER JOIN " +
$"customer ON appointment.customerId = customer.customerId WHERE DATE(start) = STR_TO_DATE('{currentDate}', '%m/%d/%Y') AND appointment.userId = {userId} ORDER BY appointment.start", con);
con.Open();
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
adapter.Fill(dt);
appointmentDgv.DataSource = dt;
//Converts from UTC (DateTimes stored in database) to user's local time using TimeZoneInfo
for (int idx = 0; idx < dt.Rows.Count; idx++)
{
dt.Rows[idx]["start"] = TimeZoneInfo.ConvertTimeFromUtc((DateTime)dt.Rows[idx]["start"], TimeZoneInfo.Local).ToString();
}
for (int idx = 0; idx < dt.Rows.Count; idx++)
{
dt.Rows[idx]["end"] = TimeZoneInfo.ConvertTimeFromUtc((DateTime)dt.Rows[idx]["end"], TimeZoneInfo.Local).ToString();
}
}
}
/// <summary>
/// Saves a new appointment record to the database from
/// values entered into text boxes on add appointment form.
/// </summary>
/// <param name="model"> The appointment information </param>
/// <returns> The appointment information, including the unique identifier </returns>
public AppointmentModel CreateAppointment(AppointmentModel model)
{
//Method-wide variable to set the active user
string user = DataBaseHandler.GetCurrentUserName();
int userId = DataBaseHandler.GetCurrentUserId();
//Creates new appointment record
using (MySqlConnection con = new MySqlConnection(GlobalConfig.CS))
{
con.Open();
MySqlTransaction transaction = con.BeginTransaction();
var query = "INSERT into appointment (customerId, userId, title, description, location, contact, type, url, start, end, createDate, createdBy, lastUpdate, lastUpdateBy) " +
$"VALUES ('{model.CustomerId}', '{userId}', 'not needed', 'not needed', 'not needed', 'not needed', '{model.AppointmentType}', 'not needed', STR_TO_DATE('{model.StartTime}', '%m/%d/%Y %h:%i:%s %p'), STR_TO_DATE('{model.EndTime}', '%m/%d/%Y %h:%i:%s %p'), CURRENT_DATE, '{user}', CURRENT_TIMESTAMP, '{user}')";
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
transaction.Commit();
con.Close();
}
//Dummy initialization value for model.AppointmentId (AppointmentModel object)
model.AppointmentId = 1;
//Returns the AppointmentModel object entered into database
return(model);
}
private void ValidateAppointment(DateTime startDT, DateTime endDT)
{
//Checks for appointments made outside of business hours. Mon-Fri, 8am to 5pm
int validStartTime = 480; //8am
int validEndTime = 1020; //5pm
DayOfWeek validStartDay = DayOfWeek.Monday;
DayOfWeek validEndDay = DayOfWeek.Friday;
int selectedStartTime = startDT.Hour * 60 + startDT.Minute;
int selectedEndTime = endDT.Hour * 60 + endDT.Minute;
DayOfWeek selectedStartDay = startDT.DayOfWeek;
DayOfWeek selectedEndDay = endDT.DayOfWeek;
if (selectedStartDay >= DayOfWeek.Monday && selectedStartDay <= DayOfWeek.Friday &&
selectedEndDay >= DayOfWeek.Monday && selectedEndDay <= DayOfWeek.Friday)
{
if (selectedStartTime >= validStartTime && selectedStartTime < validEndTime &&
selectedEndTime > validStartTime && selectedEndTime <= validEndTime)
{
//Appointment is valid, do nothing
}
else
{
throw new AppointmentOutsideBusinessHrsException();
}
}
else
{
throw new AppointmentOutsideBusinessHrsException();
}
//Checks for overlapping appointments.
using (MySqlConnection con = new MySqlConnection(CS))
{
int userId = DataBaseHandler.GetCurrentUserId();
DateTime dt1Start = startDT;
DateTime dt1End = endDT;
DateTime dt2Start;
DateTime dt2End;
MySqlCommand cmd = new MySqlCommand($"SELECT appointment.start, appointment.end FROM appointment WHERE appointment.userId = {userId}", con);
MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
adp.Fill(dt);
//Converts from UTC (DateTimes stored in database) to user's local time using TimeZoneInfo
for (int idx = 0; idx < dt.Rows.Count; idx++)
{
dt.Rows[idx]["start"] = TimeZoneInfo.ConvertTimeFromUtc((DateTime)dt.Rows[idx]["start"], TimeZoneInfo.Local).ToString();
}
for (int idx = 0; idx < dt.Rows.Count; idx++)
{
dt.Rows[idx]["end"] = TimeZoneInfo.ConvertTimeFromUtc((DateTime)dt.Rows[idx]["end"], TimeZoneInfo.Local).ToString();
}
foreach (DataRow row in dt.Rows)
{
dt2Start = (DateTime)row[0];
dt2End = (DateTime)row[1];
if ((dt1Start <= dt2Start && dt2Start < dt1End) || (dt2Start <= dt1Start && dt1Start < dt2End))
{
throw new ConflictingAppointmentException();
}
else
{
//Appointment is valid, do nothing
}
}
}
}
private void handleMonth()
{
int userId = DataBaseHandler.GetCurrentUserId();
mainFormCalendar.RemoveAllBoldedDates();
dt.Clear();
int mo = currentDate.Month;
int yr = currentDate.Year;
int d = 0;
string startDate = mo.ToString() + "/01/" + yr.ToString();
DateTime tempDate = Convert.ToDateTime(startDate);
switch (mo)
{
case 1:
case 3:
case 5:
case 7:
case 8:
case 10:
d = 31;
break;
case 4:
case 6:
case 9:
case 11:
d = 30;
break;
default:
d = 29;
break;
}
for (int i = 0; i < d; i++)
{
mainFormCalendar.AddBoldedDate(tempDate.AddDays(i));
}
mainFormCalendar.UpdateBoldedDates();
string endDate = mo.ToString() + "/" + d.ToString() + "/" + yr.ToString();
using (MySqlConnection con = new MySqlConnection(CS))
{
MySqlCommand cmd = new MySqlCommand("SELECT appointment.appointmentId, customer.customerName, appointment.type, appointment.start, appointment.end " +
"FROM appointment INNER JOIN " +
$"customer ON appointment.customerId = customer.customerId WHERE DATE(start) AND DATE(end) BETWEEN STR_TO_DATE('{startDate}', '%m/%d/%Y') AND STR_TO_DATE('{endDate}', '%m/%d/%Y') AND appointment.userId = {userId} ORDER BY appointment.start", con);
con.Open();
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
adapter.Fill(dt);
appointmentDgv.DataSource = dt;
//Converts from UTC (DateTimes stored in database) to user's local time using TimeZoneInfo
for (int idx = 0; idx < dt.Rows.Count; idx++)
{
dt.Rows[idx]["start"] = TimeZoneInfo.ConvertTimeFromUtc((DateTime)dt.Rows[idx]["start"], TimeZoneInfo.Local).ToString();
}
for (int idx = 0; idx < dt.Rows.Count; idx++)
{
dt.Rows[idx]["end"] = TimeZoneInfo.ConvertTimeFromUtc((DateTime)dt.Rows[idx]["end"], TimeZoneInfo.Local).ToString();
}
}
}