/// <summary>
/// Updates a customer and writes record to the mySqlDatabase
/// </summary>
/// <param name="model"> The customer information </param>
/// <returns> Returns the customer information and updates the record in the database </returns>
public CustomerModel UpdateCustomer(CustomerModel model)
{
//Method-wide variable to set the active user
string user = DataBaseHandler.GetCurrentUserName();
//Updates country record
using (MySqlConnection con = new MySqlConnection(GlobalConfig.CS))
{
con.Open();
MySqlTransaction transaction = con.BeginTransaction();
var query = $"UPDATE country SET country = '{model.Country}', lastUpdate = CURRENT_TIMESTAMP, lastUpdateBy = '{user}' WHERE countryId = '{model.CountryId}'";
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
transaction.Commit();
con.Close();
}
//Updates city record based on model.CountryId value from above
using (MySqlConnection con = new MySqlConnection(GlobalConfig.CS))
{
con.Open();
MySqlTransaction transaction = con.BeginTransaction();
var query = $"UPDATE city SET city = '{model.City}', countryId = '{model.CountryId}', lastUpdate = CURRENT_TIMESTAMP, lastUpdateBy = '{user}' WHERE cityId = '{model.CityId}'";
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
transaction.Commit();
con.Close();
}
//Updates address record based on the cityId value from above
using (MySqlConnection con = new MySqlConnection(GlobalConfig.CS))
{
con.Open();
MySqlTransaction transaction = con.BeginTransaction();
var query = $"UPDATE address SET address = '{model.Address}', cityId = '{model.CityId}', postalCode = '{model.Zipcode}', phone = '{model.PhoneNumber}', lastUpdate = CURRENT_TIMESTAMP, lastUpdateBy = '{user}' WHERE addressId = '{model.AddressId}'";
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
transaction.Commit();
con.Close();
}
//Updates customer record based on the addressId value from above
using (MySqlConnection con = new MySqlConnection(GlobalConfig.CS))
{
con.Open();
MySqlTransaction transaction = con.BeginTransaction();
var query = $"UPDATE customer SET customerName = '{model.Name}', addressId = '{model.AddressId}', active = '{model.Active}', lastUpdate = CURRENT_TIMESTAMP, lastUpdateBy = '{user}' WHERE customerId = '{model.Id}'";
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
transaction.Commit();
con.Close();
}
return(model);
}
/// <summary>
/// Querys the mySqlDatabase to populate the update form's text boxes with current info.
/// </summary>
/// <param name="model"> The customer information </param>
/// <returns> Returns the customer information and populates update form's text boxes </returns>
public CustomerModel QueryCustomerInfo(CustomerModel model)
{
//Method-wide variable to set the active user
string user = DataBaseHandler.GetCurrentUserName();
using (MySqlConnection con = new MySqlConnection(GlobalConfig.CS))
{
MySqlCommand cmd = new MySqlCommand($"SELECT customerName, addressId, active FROM customer WHERE customerId = {model.Id}", con);
con.Open();
MySqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
model.Name = rdr.GetString("customerName");
model.AddressId = rdr.GetInt32(rdr.GetOrdinal("addressId"));
model.Active = rdr.GetInt32(rdr.GetOrdinal("active"));
if (model.Active == 0)
{
model.Inactive = 1;
}
}
using (MySqlConnection con = new MySqlConnection(GlobalConfig.CS))
{
MySqlCommand cmd = new MySqlCommand($"SELECT address, address2, cityId, postalCode, phone FROM address WHERE addressId = {model.AddressId}", con);
con.Open();
MySqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
model.Address = rdr.GetString("address");
model.CityId = rdr.GetInt32(rdr.GetOrdinal("cityId"));
model.Zipcode = rdr.GetString("postalCode");
model.PhoneNumber = rdr.GetString("phone");
}
using (MySqlConnection con = new MySqlConnection(GlobalConfig.CS))
{
MySqlCommand cmd = new MySqlCommand($"SELECT city, countryId FROM city WHERE cityId = {model.CityId}", con);
con.Open();
MySqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
model.City = rdr.GetString("city");
model.CountryId = rdr.GetInt32(rdr.GetOrdinal("countryId"));
}
using (MySqlConnection con = new MySqlConnection(GlobalConfig.CS))
{
MySqlCommand cmd = new MySqlCommand($"SELECT country FROM country WHERE countryId = {model.CountryId}", con);
con.Open();
MySqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
model.Country = rdr.GetString("country");
}
return(model);
}
public SignInForm()
{
InitializeComponent();
currentDate = DateTime.Now;
DetermineUsersLocation(CultureInfo.CurrentUICulture.LCID);
DataBaseHandler.PopulateUserTable();
DataBaseHandler.PopulateCountryTable();
DataBaseHandler.PopulateCityTable();
DataBaseHandler.PopulateAddressTable();
DataBaseHandler.PopulateCustomerTable();
DataBaseHandler.PopulateAppointmentTable();
}
private void signInBtn_Click(object sender, EventArgs e)
{
if (DataBaseHandler.UserExists(usernameTextBox.Text, passwordTextBox.Text) == 1)
{
//log user to file
Log.LogUser();
this.Hide();
Reminder();
//open main form
MainForm Main = new MainForm();
Main.Show();
}
else
{
MessageBox.Show(invalidCredentials);
}
}
/// <summary>
/// Updates an appointment and writes record to the mySqlDatabase
/// </summary>
/// <param name="model"> The appointment's information </param>
/// <returns> Returns the appointment information and updates the record in the database </returns>
public AppointmentModel UpdateAppointment(AppointmentModel model)
{
string user = DataBaseHandler.GetCurrentUserName();
//Updates appointment record
using (MySqlConnection con = new MySqlConnection(GlobalConfig.CS))
{
con.Open();
MySqlTransaction transaction = con.BeginTransaction();
var query = $"UPDATE appointment SET customerId = '{model.CustomerId}', appointmentId = '{model.AppointmentId}', type = '{model.AppointmentType}', start = STR_TO_DATE('{model.StartTime}', '%m/%d/%Y %h:%i:%s %p'), end = STR_TO_DATE('{model.EndTime}', '%m/%d/%Y %h:%i:%s %p'), lastUpdate = CURRENT_TIMESTAMP, lastUpdateBy = '{user}' WHERE appointmentId = '{model.AppointmentId}'";
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
transaction.Commit();
con.Close();
}
return(model);
}
private void handleWeek()
{
int userId = DataBaseHandler.GetCurrentUserId();
mainFormCalendar.RemoveAllBoldedDates();
dt.Clear();
int dow = (int)currentDate.DayOfWeek;
string startDate = currentDate.AddDays(-dow).ToString();
DateTime tempDate = Convert.ToDateTime(startDate);
for (int i = 0; i < 7; i++)
{
mainFormCalendar.AddBoldedDate(tempDate.AddDays(i));
}
mainFormCalendar.UpdateBoldedDates();
string endDate = currentDate.AddDays(7 - dow).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();
}
}
}
/// <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);
}
/// <summary>
/// Saves a new customer record to the database from
/// values entered into text boxes on add customer form.
/// </summary>
/// <param name="model"> The customer information </param>
/// <returns> The customer information, including the unique identifier </returns>
public CustomerModel CreateCustomer(CustomerModel model)
{
//Method-wide variable to set the active user
string user = DataBaseHandler.GetCurrentUserName();
//Creates new country record
using (MySqlConnection con = new MySqlConnection(GlobalConfig.CS))
{
con.Open();
MySqlTransaction transaction = con.BeginTransaction();
var query = "INSERT into country (country, createDate, createdBy, lastUpdate, lastUpdateBy) " +
$"VALUES ('{model.Country}', CURRENT_DATE,'{user}', CURRENT_TIMESTAMP, '{user}')";
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
transaction.Commit();
con.Close();
}
//Finds the countryId assigned above and sets model.CountryId to that
//value, then it is used to link the following create record action below
using (MySqlConnection con = new MySqlConnection(GlobalConfig.CS))
{
MySqlCommand cmd = new MySqlCommand("SELECT countryId FROM country ORDER BY countryId DESC LIMIT 1", con);
con.Open();
MySqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
model.CountryId = rdr.GetInt32(rdr.GetOrdinal("countryId"));
}
//Creates new city record based on model.CountryId value from above
using (MySqlConnection con = new MySqlConnection(GlobalConfig.CS))
{
con.Open();
MySqlTransaction transaction = con.BeginTransaction();
var query = "INSERT into city (city, countryId, createDate, createdBy, lastUpdate, lastUpdateBy) " +
$"VALUES ('{model.City}', '{model.CountryId}', CURRENT_DATE,'{user}', CURRENT_TIMESTAMP, '{user}')";
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
transaction.Commit();
con.Close();
}
//Finds the cityId assigned above and sets model.CityId to that
//value, then it is used to link the following create record action below
using (MySqlConnection con = new MySqlConnection(GlobalConfig.CS))
{
MySqlCommand cmd = new MySqlCommand("SELECT cityId FROM city ORDER BY cityId DESC LIMIT 1", con);
con.Open();
MySqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
model.CityId = rdr.GetInt32(rdr.GetOrdinal("cityId"));
}
//Creates new address record based on the cityId value from above
using (MySqlConnection con = new MySqlConnection(GlobalConfig.CS))
{
con.Open();
MySqlTransaction transaction = con.BeginTransaction();
var query = "INSERT into address (address, address2, cityId, postalCode, phone, createDate, createdBy, lastUpdate, lastUpdateBy) " +
$"VALUES ('{model.Address}', '', '{model.CityId}', '{model.Zipcode}', '{model.PhoneNumber}', CURRENT_DATE,'{user}', CURRENT_TIMESTAMP, '{user}')";
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
transaction.Commit();
con.Close();
}
//Finds the addressId assigned above and sets model.AddressId to that
//value, then it is used to link the following create record action below
using (MySqlConnection con = new MySqlConnection(GlobalConfig.CS))
{
MySqlCommand cmd = new MySqlCommand("SELECT addressId FROM address ORDER BY addressId DESC LIMIT 1", con);
con.Open();
MySqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
model.AddressId = rdr.GetInt32(rdr.GetOrdinal("addressId"));
}
//Creates new customer record based on the addressId value from above
using (MySqlConnection con = new MySqlConnection(GlobalConfig.CS))
{
con.Open();
MySqlTransaction transaction = con.BeginTransaction();
var query = "INSERT into customer (customerName, addressId, active, createDate, createdBy, lastUpdate, lastUpdateBy) " +
$"VALUES ('{model.Name}', '{model.AddressId}', '{model.Active}', 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.Id (CustomerModel object)
model.Id = 1;
//Returns the CustomerModel 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
}
}
}
}
public static void LogUser()
{
using (StreamWriter w = File.AppendText("log.txt"))
{
LogEntry($"UserId:\t{DataBaseHandler.GetCurrentUserId()}\n :Username:\t{DataBaseHandler.GetCurrentUserName()}", w);
}
}
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();
}
}
}
