public void MainForm_Load(bool week)
{
DateTime filter = week ? CalcDateFilter("week") : CalcDateFilter("month");
DataTable dtRecord = SqlUpdater.FirstCal(SqlUpdater.DateSQLFormat(filter), week);
AppointmentCalendar.DataSource = dtRecord;
}
private void ReportButton_Click(object sender, EventArgs e)
{
DataRowView drv = TypeCombo.SelectedItem as DataRowView;
try
{
string item = TypeCombo.SelectedItem.ToString();
IDictionary <string, object> dictionary = SqlUpdater.ReportAppoint(item);
janResult.Text = dictionary["Jan"].ToString();
febResult.Text = dictionary["Feb"].ToString();
marResult.Text = dictionary["Mar"].ToString();
aprResult.Text = dictionary["Apr"].ToString();
mayResult.Text = dictionary["May"].ToString();
junResult.Text = dictionary["Jun"].ToString();
julResult.Text = dictionary["Jul"].ToString();
augResult.Text = dictionary["Aug"].ToString();
sepResult.Text = dictionary["Sep"].ToString();
octResult.Text = dictionary["Oct"].ToString();
novResult.Text = dictionary["Nov"].ToString();
decResult.Text = dictionary["Dec"].ToString();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
private void customerCreateButton_Click(object sender, EventArgs e)
{
string timestamp = SqlUpdater.createTimestamp();
string userName = SqlUpdater.getCurrentUserName();
if (string.IsNullOrEmpty(customerNameText.Text) ||
string.IsNullOrEmpty(customerPhoneText.Text) ||
string.IsNullOrEmpty(customerCityText.Text) ||
string.IsNullOrEmpty(customerCountryText.Text) ||
string.IsNullOrEmpty(customerZipText.Text) ||
string.IsNullOrEmpty(customerAddressText.Text) ||
(activeYes.Checked == false && activeNo.Checked == false))
{
MessageBox.Show("Please complete all fields");
}
else
{
int countryId = SqlUpdater.createRecord(timestamp, userName, "country", $"'{customerCountryText.Text}'");
int cityId = SqlUpdater.createRecord(timestamp, userName, "city", $"'{customerCityText.Text}', '{countryId}'");
int addressId = SqlUpdater.createRecord(timestamp, userName, "address", $"'{customerAddressText.Text}', '', '{cityId}', '{customerZipText.Text}', '{customerPhoneText.Text}'");
SqlUpdater.createRecord(timestamp, userName, "customer", $"'{ customerNameText.Text}', '{addressId}', '{(activeYes.Checked ? 1 : 0)}'");
Close();
}
}
public static void writeUserLoginLog(int userId)
{
string path = "log.text";
string log = $"User with ID {userId} logged in at {SqlUpdater.createTimestamp()}" + Environment.NewLine;
File.AppendAllText(path, log);
}
private void searchButton_Click(object sender, EventArgs e)
{
int customerId = SqlUpdater.findCustomer(searchBar.Text);
if (customerId != 0)
{
cForm = SqlUpdater.getCustomerDetails(customerId);
customerNameBox.Text = cForm["customerName"];
customerPhoneBox.Text = cForm["phone"];
customerCityBox.Text = cForm["city"];
customerAddessBox.Text = cForm["address"];
customerZipBox.Text = cForm["zip"];
customerCountryBox.Text = cForm["country"];
if (cForm["active"] == "true")
{
activeYes.Checked = true;
}
else
{
activeNo.Checked = true;
}
}
else
{
MessageBox.Show("Unable to find customer");
}
}
private void searchButton_Click(object sender, EventArgs e)
{
int customerId = SqlUpdater.findCustomer(searchBar.Text);
if (customerId != 0)
{
customerDetails = SqlUpdater.getCustomerDetails(customerId);
nameLabel.Text = customerDetails["customerName"];
phoneLabel.Text = customerDetails["phone"];
addressLabel.Text = customerDetails["address"];
cityLabel.Text = customerDetails["city"];
zipLabel.Text = customerDetails["zip"];
countryLabel.Text = customerDetails["country"];
if (customerDetails["active"] == "true")
{
activeLabel.Text = "Active";
}
else
{
activeLabel.Text = "Inactive";
}
}
else
{
MessageBox.Show("Unable to find customer");
}
}
private void ReportButton_Click(object sender, EventArgs e)
{
DataRowView drv = comboBox1.SelectedItem as DataRowView;
int id = Convert.ToInt32(comboBox1.SelectedValue);
DataTable dtRecord = SqlUpdater.Schedule(id.ToString());
userReportView.DataSource = dtRecord;
}
private void SearchButton_Click_1(object sender, EventArgs e)
{
string appointmentId = SearchBar.Text;
form = SqlUpdater.GetAppointmentDetails(appointmentId);
CustomerIdBox.Text = form["customerId"];
TypeBox.Text = form["type"];
StartTimeBox.Text = SqlUpdater.ConvertToTimezone(form["start"]);
EndTimeBox.Text = (SqlUpdater.ConvertToTimezone(form["end"]));
}
private void searchButton_Click(object sender, EventArgs e)
{
string appointmentId = searchBar.Text;
aForm = SqlUpdater.getAppointmentDetails(appointmentId);
customerIdBox.Text = aForm["customerId"];
typeBox.Text = aForm["type"];
startTimeBox.Value = DateTime.Parse(SqlUpdater.convertToTimezone(aForm["start"]));
endTimeBox.Value = DateTime.Parse(SqlUpdater.convertToTimezone(aForm["end"]));
}
private void searchButton_Click(object sender, EventArgs e)
{
string appointmentId = appointmentIdBox.Text;
appointmentDetails = SqlUpdater.getAppointmentDetails(appointmentId);
customerIdLabel.Text = appointmentDetails["customerId"];
typeLabel.Text = appointmentDetails["type"];
startLabel.Text = appointmentDetails["start"];
endLabel.Text = appointmentDetails["end"];
}
public static bool AppHasConflict(DateTime startTime, DateTime endTime)
{
foreach (var app in SqlUpdater.GetAppointments().Values)
{
if (startTime < DateTime.Parse(app["end"].ToString()) && DateTime.Parse(app["start"].ToString()) < endTime)
{
return(true);
}
}
return(false);
}
public static Array getReport()
{
List <AppReport> appReports = new List <AppReport>();
List <Hashtable> appTypes = new List <Hashtable>();
SortedList months = new SortedList();
months.Add(1, "Janurary");
months.Add(2, "Feburary");
months.Add(3, "March");
months.Add(4, "April");
months.Add(5, "May");
months.Add(6, "June");
months.Add(7, "July");
months.Add(8, "August");
months.Add(9, "September");
months.Add(10, "October");
months.Add(11, "November");
months.Add(12, "December");
foreach (var app in SqlUpdater.getAppointments().Values)
{
int appMonth = DateTime.Parse(app["start"].ToString()).Month;
bool duplicate = false;
foreach (AppReport r in appReports)
{
if (r.month == months[appMonth].ToString() && r.appType == app["type"].ToString())
{
duplicate = true;
}
}
if (!duplicate)
{
AppReport appReport = new AppReport();
appReport.month = months[appMonth].ToString();
appReport.appType = app["type"].ToString();
appReport.quantity = SqlUpdater.getAppointments().Where(i => i.Value["type"].ToString() == app["type"].ToString() && DateTime.Parse(i.Value["start"].ToString()).Month == appMonth).Count();
appReports.Add(appReport);
}
}
var appointmentArray = from row in appReports
select new
{
Month = row.month,
Type = row.appType,
Quantity = row.quantity
};
return(appointmentArray.ToArray());
}
private void UpdateButton_Click_1(object sender, EventArgs e)
{
string timestamp = SqlUpdater.CreateTimestamp();
int userId = SqlUpdater.GetCurrentUserID();
string username = SqlUpdater.GetCurrentUserName();
int appointmentId = Convert.ToInt32(SearchBar.Text);
int customerId = Convert.ToInt32(CustomerIdBox.Text);
string type = TypeBox.Text;
DateTime startTime = DateTime.Parse(StartTimeBox.Text).ToUniversalTime();
DateTime endTime = DateTime.Parse(EndTimeBox.Text).ToUniversalTime();
String st = DateTime.Parse(StartTimeBox.Text).ToUniversalTime().ToString("u");
String et = DateTime.Parse(EndTimeBox.Text).ToUniversalTime().ToString("u");
bool pass = Validator();
if (pass)
{
try
{
if (AppHasConflict(startTime, endTime))
{
throw new AppointmentException();
}
else
{
try
{
if (OutsideBusinessHours(startTime, endTime))
{
throw new AppointmentException();
}
else
{
SqlUpdater.UpdateAppt(customerId, userId, st, et, type, timestamp, username, appointmentId);
mainFormObject.UpdateCalendar();
MessageBox.Show("Update Sucessfull.");
Close();
}
}
catch (AppointmentException ex) { ex.BusinessHours(); }
}
}
catch (AppointmentException ex) { ex.AppOverlap(); }
}
else
{
MessageBox.Show("Add Appointment Error.");
}
}
public static Array getReport()
{
Dictionary <int, Hashtable> userReport = SqlUpdater.getAppointments();
var appointmentArray = from row in userReport
select new
{
UserName = row.Value["username"],
Type = row.Value["type"],
StartTime = SqlUpdater.convertToTimezone(row.Value["start"].ToString()),
EndTime = SqlUpdater.convertToTimezone(row.Value["start"].ToString()),
Customer = row.Value["customerName"]
};
return(appointmentArray.ToArray());
}
private void loginButton_Click(object sender, EventArgs e)
{
if (FindUser(username.Text, password.Text) != 0)
{
this.Hide();
MainForm MainForm = new MainForm();
MainForm.loginForm = this;
Logger.writeUserLoginLog(SqlUpdater.getCurrentUserID());
MainForm.Show();
}
else
{
MessageBox.Show(errorMessage);
password.Text = "";
}
}
public bool updateCustomer(Dictionary <string, string> updatedForm)
{
MySqlConnection c = new MySqlConnection(SqlUpdater.conString);
c.Open();
//Customer table
string recUpdate = $"UPDATE customer" +
$" SET customerName = '{updatedForm["customerName"]}', active = '{updatedForm["active"]}', lastUpdate = '{SqlUpdater.createTimestamp()}', lastUpdateBy = '{SqlUpdater.getCurrentUserName()}'" +
$" WHERE customerName = '{cForm["customerName"]}'";
MySqlCommand cmd = new MySqlCommand(recUpdate, c);
int customerUpdated = cmd.ExecuteNonQuery();
//Address table
recUpdate = $"UPDATE address" +
$" SET address = '{updatedForm["address"]}', postalCode = '{updatedForm["zip"]}', phone = '{updatedForm["phone"]}', lastUpdate = '{SqlUpdater.createTimestamp()}', lastUpdateBy = '{SqlUpdater.getCurrentUserName()}'" +
$" WHERE address = '{cForm["address"]}'";
cmd = new MySqlCommand(recUpdate, c);
int addressUpdated = cmd.ExecuteNonQuery();
//City Table
recUpdate = $"UPDATE city" +
$" SET city = '{updatedForm["city"]}', lastUpdate = '{SqlUpdater.createTimestamp()}', lastUpdateBy = '{SqlUpdater.getCurrentUserName()}'" +
$" WHERE city = '{cForm["city"]}'";
cmd = new MySqlCommand(recUpdate, c);
int cityUpdated = cmd.ExecuteNonQuery();
//Country Table
recUpdate = $"UPDATE country" +
$" SET country = '{updatedForm["country"]}', lastUpdate = '{SqlUpdater.createTimestamp()}', lastUpdateBy = '{SqlUpdater.getCurrentUserName()}'" +
$" WHERE country = '{cForm["country"]}'";
cmd = new MySqlCommand(recUpdate, c);
int countryUpdated = cmd.ExecuteNonQuery();
c.Close();
if (customerUpdated != 0 && addressUpdated != 0 && cityUpdated != 0 && countryUpdated != 0)
{
return(true);
}
else
{
return(false);
}
}
static public int FindUser(string userName, string password)
{
MySqlConnection C = new MySqlConnection(SqlUpdater.conString);
C.Open();
MySqlCommand cmd = new MySqlCommand($"SELECT userId FROM user WHERE userName = '******' AND password = '******'", C);
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
rdr.Read();
SqlUpdater.setCurrentUserID(Convert.ToInt32(rdr[0]));
SqlUpdater.setCurrentUserName(userName);
rdr.Close();
return(SqlUpdater.getCurrentUserID());
}
return(0);
}
private void LoginButton_Click(object sender, EventArgs e)
{
if (FindUser(Username.Text, Password.Text) != 0)
{
this.Hide();
MainForm MainForm = new MainForm
{
loginForm = this
};
Logger.WriteUserLoginLog(SqlUpdater.GetCurrentUserName());
MainForm.Show();
}
else
{
MessageBox.Show(errorMessage);
Password.Text = "";
}
}
private void SearchButton_Click_1(object sender, EventArgs e)
{
{
int customerId = SqlUpdater.FindCustomer(SearchBar.Text);
if (customerId != 0)
{
cForm = SqlUpdater.GetCustomerDetails(customerId);
CustomerNameBox.Text = cForm["customerName"];
CustomerPhoneBox.Text = cForm["phone"];
CustomerCityBox.Text = cForm["city"];
CustomerAddressBox.Text = cForm["address"];
CustomerZipBox.Text = cForm["zip"];
CustomerCountryBox.Text = cForm["country"];
}
else
{
MessageBox.Show("Unable to find customer");
}
}
}
public static DataTable getReport()
{
Dictionary <int, Hashtable> appointments = SqlUpdater.getAppointments();
DataTable dt = new DataTable();
dt.Clear();
dt.Columns.Add("customer");
dt.Columns.Add("appointments");
IEnumerable <string> customers = appointments.Select(i => i.Value["customerName"].ToString()).Distinct();
foreach (string customer in customers)
{
DataRow row = dt.NewRow();
row["customer"] = customer;
row["appointments"] = appointments.Where(i => i.Value["customerName"].ToString() == customer.ToString()).Count().ToString();
dt.Rows.Add(row);
}
return(dt);
}
private void AddButton_Click(object sender, EventArgs e)
{
string timestamp = SqlUpdater.CreateTimestamp();
int userId = SqlUpdater.GetCurrentUserID();
string username = SqlUpdater.GetCurrentUserName();
DateTime startTime = DateTime.Parse(StartTimeBox.Text).ToUniversalTime();
DateTime endTime = DateTime.Parse(EndTimeBox.Text).ToUniversalTime();
bool pass = Validator();
if (pass)
{
try
{
if (AppHasConflict(startTime, endTime))
{
throw new AppointmentException();
}
else
{
try
{
if (OutsideBusinessHours(startTime, endTime))
{
throw new AppointmentException();
}
else
{
SqlUpdater.CreateRecord(timestamp, username, "appointment", $"'{CustomerIDBox.Text}', '{DateTime.Parse(StartTimeBox.Text).ToUniversalTime():u}', '{DateTime.Parse(EndTimeBox.Text).ToUniversalTime():u}', '{TypeBox.Text}'", userId);
mainFormObject.UpdateCalendar();
Close();
}
}
catch (AppointmentException ex) { ex.BusinessHours(); }
}
}
catch (AppointmentException ex) { ex.AppOverlap(); }
}
private void AllButton_Click(object sender, EventArgs e)
{
try
{
IDictionary <string, object> dictionary = SqlUpdater.ReportAll();
janResult.Text = dictionary["Jan"].ToString();
febResult.Text = dictionary["Feb"].ToString();
marResult.Text = dictionary["Mar"].ToString();
aprResult.Text = dictionary["Apr"].ToString();
mayResult.Text = dictionary["May"].ToString();
junResult.Text = dictionary["Jun"].ToString();
julResult.Text = dictionary["Jul"].ToString();
augResult.Text = dictionary["Aug"].ToString();
sepResult.Text = dictionary["Sep"].ToString();
octResult.Text = dictionary["Oct"].ToString();
novResult.Text = dictionary["Nov"].ToString();
decResult.Text = dictionary["Dec"].ToString();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
private void addButton_Click(object sender, EventArgs e)
{
string timestamp = SqlUpdater.createTimestamp();
int userId = SqlUpdater.getCurrentUserID();
string username = SqlUpdater.getCurrentUserName();
DateTime startTime = startTimeBox.Value.ToUniversalTime();
DateTime endTime = endTimeBox.Value.ToUniversalTime();
try
{
if (appHasConflict(startTime, endTime))
{
throw new appointmentException();
}
else
{
try
{
if (outsideBusinessHours(startTime, endTime))
{
throw new appointmentException();
}
else
{
SqlUpdater.createRecord(timestamp, username, "appointment", $"'{customerIdBox.Text}', '{startTimeBox.Value.ToUniversalTime().ToString("u")}', '{endTimeBox.Value.ToUniversalTime().ToString("u")}', '{typeBox.Text}'", userId);
mainFormObject.updateCalendar();
Close();
}
}
catch (appointmentException ex) { ex.businessHours(); }
}
}
catch (appointmentException ex) { ex.appOverlap(); }
}
public bool updateAppointment(Dictionary <string, string> updatedForm)
{
MySqlConnection c = new MySqlConnection(SqlUpdater.conString);
c.Open();
string recUpdate = $"UPDATE appointment" +
$" SET customerId = '{updatedForm["customerId"]}', start = '{updatedForm["start"]}', end = '{updatedForm["end"]}', type = '{updatedForm["type"]}', lastUpdate = '{SqlUpdater.createTimestamp()}', lastUpdateBy = '{SqlUpdater.getCurrentUserName()}'" +
$" WHERE appointmentId = '{aForm["appointmentId"]}'";
MySqlCommand cmd = new MySqlCommand(recUpdate, c);
int appointmentUpdated = cmd.ExecuteNonQuery();
c.Close();
if (appointmentUpdated != 0)
{
return(true);
}
else
{
return(false);
}
}
static public Array getCalendar(bool weekView)
{
MySqlConnection c = new MySqlConnection(SqlUpdater.conString);
c.Open();
string query = $"SELECT customerId, type, start, end, appointmentId, userId FROM appointment WHERE userid = '{SqlUpdater.getCurrentUserID()}'";
MySqlCommand cmd = new MySqlCommand(query, c);
MySqlDataReader rdr = cmd.ExecuteReader();
Dictionary <int, Hashtable> appointments = SqlUpdater.getAppointments();//new Dictionary<int, Hashtable>();
while (rdr.Read())
{
Hashtable appointment = new Hashtable();
appointment.Add("customerId", rdr[0]);
appointment.Add("type", rdr[1]);
appointment.Add("start", rdr[2]);
appointment.Add("end", rdr[3]);
appointment.Add("userId", rdr[5]);
appointment.Add(Convert.ToInt32(rdr[4]), appointment);
}
rdr.Close();
foreach (var app in appointments.Values)
{
query = $"SELECT userName FROM user WHERE userId = '{app["userId"]}'";
cmd = new MySqlCommand(query, c);
rdr = cmd.ExecuteReader();
rdr.Read();
app.Add("userName", rdr[0]);
rdr.Close();
}
foreach (var app in appointments.Values)
{
query = $"SELECT customerName FROM customer WHERE customerId = '{app["customerId"]}'";
cmd = new MySqlCommand(query, c);
rdr = cmd.ExecuteReader();
rdr.Read();
app.Add("customerName", rdr[0]);
rdr.Close();
}
Dictionary <int, Hashtable> parsedAppointments = new Dictionary <int, Hashtable>();
foreach (var app in appointments)
{
DateTime startTime = DateTime.Parse(app.Value["start"].ToString());
DateTime endTime = DateTime.Parse(app.Value["end"].ToString());
DateTime today = DateTime.UtcNow;
if (weekView)
{
DateTime sunday = today.AddDays(-(int)today.DayOfWeek);
DateTime saturday = today.AddDays(-(int)today.DayOfWeek + (int)DayOfWeek.Saturday);
if (startTime >= sunday && endTime < saturday)
{
parsedAppointments.Add(app.Key, app.Value);
}
}
else
{
DateTime firstDayOfMonth = new DateTime(today.Year, today.Month, 1);
DateTime lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1);
if (startTime >= firstDayOfMonth && endTime < lastDayOfMonth)
{
parsedAppointments.Add(app.Key, app.Value);
}
}
}
SqlUpdater.setAppointments(appointments);
var appointmentArray = from row in parsedAppointments select new {
ID = row.Key,
Type = row.Value["type"],
StartTime = SqlUpdater.convertToTimezone(row.Value["start"].ToString()),
EndTime = SqlUpdater.convertToTimezone(row.Value["end"].ToString()),
Customer = row.Value["customerName"]
};
c.Close();
return(appointmentArray.ToArray());
}
