// Show booking grid with booking list from SQL Table
public void showBookings()
{
workingHours_Txt.Text = DatabaseControl.getHoursOfBookingDate(dateTimePicker1.Value.Date).ToString();
note_Txt.Text = DatabaseControl.getBookingNote(dateTimePicker1.Value.Date);
double workingHours = DatabaseControl.getHoursOfBookingDate(dateTimePicker1.Value.Date);
BindingSource bs = new BindingSource();
GData.bookings = DatabaseControl.getBookings();
GData.customers = DatabaseControl.getCustomers();
List <Booking> bookings = GData.bookings.Where(b => b.bookingDate.Date == dateTimePicker1.Value.Date).ToList();
foreach (Booking booking in bookings)
{
//booking.estimatedTime = booking.timeOut - booking.timeIn;
booking.timeRemaining = workingHours - booking.estimatedTime;
workingHours -= booking.estimatedTime;
}
bs.DataSource = bookings.Select(b => new FilteredBooking()
{
id = b.id,
jobNO = b.jobNO,
jobType = b.jobType,
customerName = GData.customers.Where(c => c.id == b.customerID).ToList()[0].name,
vehicleModel = b.vehicleModel,
regNo = b.regNo,
bookedBy = b.bookedBy,
loanCar = b.loanCar,
jobDescription = b.jobDescription,
timeRemaining = b.timeRemaining,
timeIn = b.timeIn,
timeOut = b.timeOut,
bookingDate = b.bookingDate
}).ToList();
dataGridView1.DataSource = bs;
}
private void nextDay_Btn_Click(object sender, EventArgs e)
{
bool firstMove = false;
List <Booking> bookings = GData.bookings.Where(b => b.bookingDate.Date == dateTimePicker1.Value.Date).ToList();
foreach (Booking booking in bookings)
{
if (booking.timeRemaining < 0)
{
//booking.estimatedTime = - booking.timeRemaining;
if (!firstMove)
{
DatabaseControl.moveBookingToNextDate(booking, -booking.timeRemaining);
booking.estimatedTime += booking.timeRemaining;
DatabaseControl.updateBooking(booking);
}
else
{
DatabaseControl.moveBookingToNextDate(booking, booking.estimatedTime);
booking.estimatedTime = 0;
DatabaseControl.updateBooking(booking);
}
firstMove = true;
}
}
dateTimePicker1.Value = dateTimePicker1.Value.AddDays(1);
}
private void save_Btn_Click(object sender, EventArgs e)
{
// Check if a technician name is not empty
if (name_Txt.Text == "")
{
MessageBox.Show("Name field can not be empty. Please input!");
name_Txt.Focus();
return;
}
// Check if a working hours is not empty
if (hoursOnMonFri_Txt.Text == "")
{
MessageBox.Show("Working Hours can not be empty. Please input!");
hoursOnMonFri_Txt.Focus();
return;
}
// Create a new technician
Technician newTechnician = new Technician
{
name = name_Txt.Text,
workingHours = Convert.ToDouble(hoursOnMonFri_Txt.Text),
date = DateTime.Now
};
if (DatabaseControl.addTechnician(newTechnician))
{
MessageBox.Show("New Technician has been added succesfully!");
UIControl.technicianGridForm.showTechnicians();
clearFields();
}
}
public void updateJobTypes()
{
GData.jobTypes = DatabaseControl.getJobTypes();
jobType_Cmb.Items.Clear();
foreach (JobType job in GData.jobTypes)
{
jobType_Cmb.Items.Add(job.typeName);
}
jobType_Cmb.Text = "";
}
public void showTechnicians()
{
BindingSource bs = new BindingSource();
GData.technicians = DatabaseControl.getTechnicians();
bs.DataSource = GData.technicians.Select(s => new FilteredTechnician()
{
id = s.id,
name = s.name,
workingHours = s.workingHours,
date = s.date
}).Where(s => s.date.Date == dateTimePicker1.Value.Date).ToList();
dataGridView1.AutoGenerateColumns = true;
dataGridView1.DataSource = bs;
}
public void showJobTypes()
{
BindingSource bs = new BindingSource();
GData.jobTypes = DatabaseControl.getJobTypes();
bs.DataSource = GData.jobTypes.Select(j => new FilteredJobType()
{
id = j.id,
typeName = j.typeName,
background = j.background
}).ToList();
dataGridView1.AutoGenerateColumns = true;
dataGridView1.DataSource = bs;
UIControl.newBookingForm.updateJobTypes();
UIControl.editBookingForm.updateJobTypes();
}
public void updateCustomers()
{
GData.customers = DatabaseControl.getCustomers();
existingCustomer_Cmb.Items.Clear();
existingCustomer_Cmb.Items.Add("(New)");
foreach (Customer customer in GData.customers)
{
existingCustomer_Cmb.Items.Add(customer.name);
}
if (customerName_Txt.Text != "")
{
existingCustomer_Cmb.Text = customerName_Txt.Text;
}
else
{
existingCustomer_Cmb.SelectedIndex = 0;
}
}
private void save_Btn_Click(object sender, EventArgs e)
{
// Check if a Job Type name is not empty
if (name_Txt.Text == "")
{
MessageBox.Show("Job Type Name can not be empty. Please input!");
name_Txt.Focus();
return;
}
// Check if a background color is not empty
if (background_Lbl.Text == "")
{
MessageBox.Show("Background Color field can not be empty. Please input!");
background_Lbl.Focus();
return;
}
//// Check if a working hours is numeric value
//double d;
//if (!double.TryParse(workingHours_Txt.Text, out d))
//{
// MessageBox.Show("Invalid Input! Working hours should be numeric value. Please input!");
// workingHours_Txt.Focus();
// return;
//}
// Create a new technician
JobType newJobType = new JobType
{
typeName = name_Txt.Text,
background = background_Lbl.Text
};
if (DatabaseControl.addJobType(newJobType))
{
MessageBox.Show("New Job Type has been added succesfully!");
UIControl.newBookingForm.updateJobTypes();
UIControl.editBookingForm.updateJobTypes();
UIControl.jobTypesGridForm.showJobTypes();
clearFields();
}
}
private void connect_Bt_Click(object sender, EventArgs e)
{
GData.hostAddress = hostAddress_Txt.Text;
if (GData.hostAddress == "")
{
MessageBox.Show("Please input Host Address");
hostAddress_Txt.Focus();
return;
}
GData.userID = user_Txt.Text;
if (GData.userID == "")
{
MessageBox.Show("Please input User ID");
user_Txt.Focus();
return;
}
GData.password = password_Txt.Text;
GData.port = port_Txt.Text;
GData.conStr = "Data Source=" + GData.hostAddress + "\\SQLEXPRESS," + GData.port +
";Network Library=DBMSSOCN;User ID=" + GData.userID + ";Password="******"Data Source=" + GData.hostAddress + "\\SQLEXPRESS," + GData.port +
";Network Library=DBMSSOCN;;Initial Catalog=HordernsDB;User ID=" + GData.userID + ";Password="******"Data Source=" + Info.hostAddress +
// ";Integrated Security=SSPI;Initial Catalog=" + Info.database + ";";
// Connect to Database and get data of Booking, JobType, and Technicians
if (DatabaseControl.CreateDBIfNotExists())
{
GData.bookings = DatabaseControl.getBookings();
GData.jobTypes = DatabaseControl.getJobTypes();
GData.technicians = DatabaseControl.getTechnicians();
GData.customers = DatabaseControl.getCustomers();
UIControl.mainForm.Show();
this.Hide();
}
}
private void workingHours_Txt_Leave(object sender, EventArgs e)
{
double d;
if (!double.TryParse(workingHours_Txt.Text, out d))
{
MessageBox.Show("Working hours should be numeric value. Please try again!");
workingHours_Txt.Text = "0";
workingHours_Txt.Focus();
return;
}
else if (Convert.ToDouble(workingHours_Txt.Text) < 0)
{
MessageBox.Show("Invalid working hours. Should be numeric value over 0. Please try again!");
workingHours_Txt.Text = "0";
workingHours_Txt.Focus();
return;
}
DatabaseControl.setBookingDates(dateTimePicker1.Value, Convert.ToDouble(workingHours_Txt.Text), note_Txt.Text);
showBookings();
}
private void del_Btn_Click(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count > 0)
{
int index = dataGridView1.SelectedRows[0].Index;
int id = (int)dataGridView1.Rows[index].Cells["ID"].Value;
var result = MessageBox.Show("Are you sure want to deleted this booking?", "Warning!", MessageBoxButtons.YesNo);
Booking booking = GData.bookings.Where(b => b.id == id).ToList()[0];
if (result == DialogResult.Yes)
{
if (DatabaseControl.deleteBooking(booking))
{
MessageBox.Show("Selected booking has been deleted!");
showBookings();
}
}
else
{
return;
}
}
}
private void save_Btn_Click(object sender, EventArgs e)
{
// Check if a technician name is not empty
if (name_Txt.Text == "")
{
MessageBox.Show("Name field can not be empty. Please input!");
name_Txt.Focus();
return;
}
// Check if a working hours is not empty
if (workingHours_Txt.Text == "")
{
MessageBox.Show("Working Hours can not be empty. Please input!");
workingHours_Txt.Focus();
return;
}
// Check if a working hours is numeric value
double d;
if (!double.TryParse(workingHours_Txt.Text, out d))
{
MessageBox.Show("Invalid Input! Working hours should be numeric value. Please input!");
workingHours_Txt.Focus();
return;
}
technicianToEdit.name = name_Txt.Text;
technicianToEdit.workingHours = Convert.ToDouble(workingHours_Txt.Text);
technicianToEdit.date = dateTimePicker1.Value;
if (DatabaseControl.updateTechnician(technicianToEdit))
{
MessageBox.Show("Technician data has been updated successfully!");
UIControl.technicianGridForm.showTechnicians();
UIControl.showForm(UIControl.technicianGridForm);
}
}
private void del_Btn_Click(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count > 0)
{
int index = dataGridView1.SelectedRows[0].Index;
int id = (int)dataGridView1.Rows[index].Cells["ID"].Value;
var result = MessageBox.Show("Are you sure want to deleted this techinician?", "Warning!", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
Technician technician = GData.technicians.Where(t => t.id == id).ToList()[0];
if (DatabaseControl.deleteTechnician(technician))
{
MessageBox.Show("Selected Technician has been deleted!");
showTechnicians();
}
}
else
{
return;
}
}
}
private void save_Btn_Click(object sender, EventArgs e)
{
// Check if a Job Type name is not empty
if (name_Txt.Text == "")
{
MessageBox.Show("Job Type Name can not be empty. Please input!");
name_Txt.Focus();
return;
}
jobTypeToEdit.typeName = name_Txt.Text;
jobTypeToEdit.background = background_Lbl.Text;
if (DatabaseControl.updateJobType(jobTypeToEdit))
{
MessageBox.Show("Job type data has been updated successfuly!");
UIControl.newBookingForm.updateJobTypes();
UIControl.editBookingForm.updateJobTypes();
UIControl.bookingGridForm.updateColumnColor();
UIControl.jobTypesGridForm.showJobTypes();
UIControl.showForm(UIControl.jobTypesGridForm);
}
}
private void del_Btn_Click(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count > 0)
{
int index = dataGridView1.SelectedRows[0].Index;
int id = (int)dataGridView1.Rows[index].Cells["ID"].Value;
var result = MessageBox.Show("Are you sure want to deleted this job type?", "Warning!", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
JobType jobType = GData.jobTypes.Where(j => j.id == id).ToList()[0];
if (DatabaseControl.deleteJobType(jobType))
{
MessageBox.Show("Selected Job Type has been deleted!");
showJobTypes();
}
}
else
{
return;
}
}
}
private void note_Txt_Leave(object sender, EventArgs e)
{
DatabaseControl.setBookingDates(dateTimePicker1.Value, Convert.ToDouble(workingHours_Txt.Text), note_Txt.Text);
showBookings();
}
private void save_Btn_Click(object sender, EventArgs e)
{
// Check if Job NO is not empty
if (jobNO_Txt.Text == "")
{
MessageBox.Show("Job No can not be empty. Please input!");
jobNO_Txt.Focus();
return;
}
// Check if a job No already exists in the database
GData.bookings = DatabaseControl.getBookings();
var list = GData.bookings.Where(b => b.jobNO == jobNO_Txt.Text).ToList();
if (list.Count > 0)
{
MessageBox.Show("The booking with Job No. " + jobNO_Txt.Text + " already exists! Please input another Job No.");
jobNO_Txt.Focus();
return;
}
// Check if Job NO is not empty
if (jobType_Cmb.Text == "")
{
MessageBox.Show("Job Type can not be empty. Please input!");
jobType_Cmb.Focus();
return;
}
// Check if a Customer filed is not empty
if (customerName_Txt.Text == "")
{
MessageBox.Show("Customer field can not be empty. Please input!");
customerName_Txt.Focus();
return;
}
// Check if an Address filed is not empty.
if (address1_Txt.Text == "")
{
MessageBox.Show("Address field can not be empty. Please input!");
address1_Txt.Focus();
return;
}
if (address2_Txt.Text == "")
{
MessageBox.Show("Address field can not be empty. Please input!");
address2_Txt.Focus();
return;
}
if (address3_Txt.Text == "")
{
MessageBox.Show("Address field can not be empty. Please input!");
address3_Txt.Focus();
return;
}
// Check if an Post Code filed is not empty.
if (postCode_Txt.Text == "")
{
MessageBox.Show("Post Code can not be empty. Please input!");
postCode_Txt.Focus();
return;
}
// Check if an email filed is not empty.
if (email_Txt.Text == "")
{
MessageBox.Show("Email field can not be empty. Please input!");
email_Txt.Focus();
return;
}
// Check if an Tel filed is not empty.
if (tel_Txt.Text == "")
{
MessageBox.Show("Tel field can not be empty. Please input!");
tel_Txt.Focus();
return;
}
// Check if an Vehicle Model filed is not empty.
if (vehicleModel_Txt.Text == "")
{
MessageBox.Show("Vehicle Model field can not be empty. Please input!");
vehicleModel_Txt.Focus();
return;
}
// Check if an Vehicle Reg.No filed is not empty.
if (vehicleRegNo_Txt.Text == "")
{
MessageBox.Show("Vehicle Reg.NO field can not be empty. Please input!");
vehicleRegNo_Txt.Focus();
return;
}
// Check if an Booked By filed is not empty.
if (bookedBy_Txt.Text == "")
{
MessageBox.Show("Booked By field can not be empty. Please input!");
bookedBy_Txt.Focus();
return;
}
// Check if an Job Description filed is not empty.
if (jobDescription_Txt.Text == "")
{
MessageBox.Show("Work Title field can not be empty. Please input!");
jobDescription_Txt.Focus();
return;
}
// Compare Time in and Time Out
if (timeIn_Dtp.Value.Hour * 60 + timeIn_Dtp.Value.Minute > timeOut_Dtp.Value.Hour * 60 + timeOut_Dtp.Value.Minute)
{
MessageBox.Show("Time in should be less than Time Out!");
timeIn_Dtp.Focus();
return;
}
int customerId = 0;
customer = new Customer
{
honor = honor_Cmb.Text,
name = customerName_Txt.Text,
address1 = address1_Txt.Text,
address2 = address2_Txt.Text,
address3 = address3_Txt.Text,
postCode = postCode_Txt.Text,
email = email_Txt.Text,
tel = tel_Txt.Text
};
if (existingCustomer_Cmb.SelectedIndex == 0)
{
customerId = DatabaseControl.addCustomer(customer);
updateCustomers();
UIControl.editBookingForm.updateCustomers();
}
else
{
customerId = GData.customers[existingCustomer_Cmb.SelectedIndex - 1].id;
DatabaseControl.updateCustomer(customer, customerId);
}
double estimatedTime = Convert.ToDouble(estimatedTime_Txt.Text);
double timeRemaining = DatabaseControl.getHoursOfBookingDate(DateTime.Now.Date) - estimatedTime;
if (timeRemaining < 0)
{
timeRemaining = 0;
}
var newBooking = new Booking
{
jobNO = jobNO_Txt.Text,
jobType = jobType_Cmb.Text,
customerID = customerId,
servicePlan = servicePlan_Cmb.Text,
vehicleMake = vehicleMake_Txt.Text,
vehicleModel = vehicleModel_Txt.Text,
regNo = vehicleRegNo_Txt.Text,
mileage = Convert.ToDouble(mileage_Txt.Text),
loanCar = loanCar_Cmb.Text,
timeIn = timeIn_Dtp.Value,
timeOut = timeOut_Dtp.Value,
bookedBy = bookedBy_Txt.Text,
estimatedTime = estimatedTime,
timeRemaining = timeRemaining,
insuranceRequired = insurance_Cmb.Text,
jobDescription = jobDescription_Txt.Text,
notes = notes_Txt.Text,
bookingDate = newBookingDate
};
//if (newBooking.timeRemaining > 0)
//{
// var newBookingToNextDay = new Booking
// {
// jobNO = jobNO_Txt.Text,
// jobType = jobType_Cmb.Text,
// customerID = customerId,
// servicePlan = servicePlan_Cmb.Text,
// vehicleMake = vehicleMake_Txt.Text,
// vehicleModel = vehicleModel_Txt.Text,
// regNo = vehicleRegNo_Txt.Text,
// mileage = Convert.ToDouble(mileage_Txt.Text),
// loanCar = loanCar_Cmb.Text,
// timeIn = timeIn_Dtp.Value,
// timeOut = timeOut_Dtp.Value,
// bookedBy = bookedBy_Txt.Text,
// estimatedTime = newBooking.timeRemaining,
// timeRemaining = DatabaseControl.getHoursOfBookingDate(DateTime.Now.Date) - newBooking.timeRemaining,
// insuranceRequired = insurance_Cmb.Text,
// jobDescription = jobDescription_Txt.Text,
// notes = notes_Txt.Text,
// bookingDate = newBookingDate.AddDays(1)
// };
// foreach (DateTime dt in GData.blackoutDates)
// {
// if (dt.Date == newBookingToNextDay.bookingDate.Date)
// {
// newBookingToNextDay.bookingDate.AddDays(1);
// break;
// }
// }
// if (newBookingToNextDay.bookingDate.DayOfWeek == DayOfWeek.Sunday)
// {
// newBookingToNextDay.bookingDate.AddDays(1);
// }
// if (DatabaseControl.addBooking(newBooking) && DatabaseControl.addBooking(newBookingToNextDay))
// {
// MessageBox.Show("New booking has been added succesfully!");
// UIControl.bookingGridForm.showBookings();
// clearFields();
// }
//}
//else
//{
if (DatabaseControl.addBooking(newBooking))
{
MessageBox.Show("New booking has been added succesfully!");
UIControl.bookingGridForm.showBookings();
clearFields();
}
//}
}
private void save_Btn_Click(object sender, EventArgs e)
{
// Check if Job NO is not empty
if (jobNO_Txt.Text == "")
{
MessageBox.Show("Job No can not be empty. Please input!");
jobNO_Txt.Focus();
return;
}
// Check if Job NO is not empty
if (jobType_Cmb.Text == "")
{
MessageBox.Show("Job Type can not be empty. Please input!");
jobType_Cmb.Focus();
return;
}
// Check if a Customer filed is not empty
if (customerName_Txt.Text == "")
{
MessageBox.Show("Customer field can not be empty. Please input!");
customerName_Txt.Focus();
return;
}
// Check if an Address filed is not empty.
if (address1_Txt.Text == "")
{
MessageBox.Show("Address field can not be empty. Please input!");
address1_Txt.Focus();
return;
}
if (address2_Txt.Text == "")
{
MessageBox.Show("Address field can not be empty. Please input!");
address2_Txt.Focus();
return;
}
if (address3_Txt.Text == "")
{
MessageBox.Show("Address field can not be empty. Please input!");
address3_Txt.Focus();
return;
}
// Check if an Post Code filed is not empty.
if (postCode_Txt.Text == "")
{
MessageBox.Show("Post Code can not be empty. Please input!");
postCode_Txt.Focus();
return;
}
// Check if an email filed is not empty.
if (email_Txt.Text == "")
{
MessageBox.Show("Email field can not be empty. Please input!");
email_Txt.Focus();
return;
}
// Check if an Tel filed is not empty.
if (tel_Txt.Text == "")
{
MessageBox.Show("Tel field can not be empty. Please input!");
tel_Txt.Focus();
return;
}
// Check if an Vehicle Model filed is not empty.
if (vehicleModel_Txt.Text == "")
{
MessageBox.Show("Vehicle Model field can not be empty. Please input!");
vehicleModel_Txt.Focus();
return;
}
// Check if an Vehicle Reg.No filed is not empty.
if (vehicleRegNo_Txt.Text == "")
{
MessageBox.Show("Vehicle Reg.NO field can not be empty. Please input!");
vehicleRegNo_Txt.Focus();
return;
}
// Check if an Booked By filed is not empty.
if (bookedBy_Cmb.Text == "")
{
MessageBox.Show("Booked By field can not be empty. Please input!");
bookedBy_Cmb.Focus();
return;
}
// Check if an Job Description filed is not empty.
if (jobDescription_Txt.Text == "")
{
MessageBox.Show("Work Title field can not be empty. Please input!");
jobDescription_Txt.Focus();
return;
}
// Compare Time in and Time Out
if (timeIn_Dtp.Value.Hour * 60 + timeIn_Dtp.Value.Minute > timeOut_Dtp.Value.Hour * 60 + timeOut_Dtp.Value.Minute)
{
MessageBox.Show("Time in should be less than Time Out!");
timeIn_Dtp.Focus();
return;
}
int customerId = 0;
Customer customer = new Customer
{
honor = honor_Cmb.Text,
name = customerName_Txt.Text,
address1 = address1_Txt.Text,
address2 = address2_Txt.Text,
address3 = address3_Txt.Text,
postCode = postCode_Txt.Text,
email = email_Txt.Text,
tel = tel_Txt.Text
};
if (existingCustomer_Cmb.SelectedIndex == 0)
{
customerId = DatabaseControl.addCustomer(customer);
}
else
{
customerId = GData.customers[existingCustomer_Cmb.SelectedIndex - 1].id;
DatabaseControl.updateCustomer(customer, customerId);
}
bookingToEdit.jobNO = jobNO_Txt.Text;
bookingToEdit.jobType = jobType_Cmb.Text;
bookingToEdit.customerID = customerId;
bookingToEdit.servicePlan = servicePlan_Cmb.Text;
bookingToEdit.vehicleMake = vehicleMake_Txt.Text;
bookingToEdit.vehicleModel = vehicleModel_Txt.Text;
bookingToEdit.regNo = vehicleRegNo_Txt.Text;
bookingToEdit.mileage = Convert.ToDouble(mileage_Txt.Text);
bookingToEdit.loanCar = loanCar_Cmb.Text;
bookingToEdit.timeIn = timeIn_Dtp.Value;
bookingToEdit.timeOut = timeOut_Dtp.Value;
bookingToEdit.bookedBy = bookedBy_Cmb.Text;
bookingToEdit.estimatedTime = Convert.ToDouble(estimatedTime_Txt.Text);
//bookingToEdit.timeRemaining = DatabaseControl.getBookingDates(bookingToEdit.bookingDate.Date) - bookingToEdit.estimatedTime;
//if (bookingToEdit.timeRemaining < 0)
// bookingToEdit.timeRemaining = 0;
bookingToEdit.insuranceRequired = insurance_Cmb.Text;
bookingToEdit.jobDescription = jobDescription_Txt.Text;
bookingToEdit.notes = notes_Txt.Text;
if (DatabaseControl.updateBooking(bookingToEdit))
{
MessageBox.Show("Booking data has been updated succesfully!");
UIControl.bookingGridForm.showBookings();
UIControl.showForm(UIControl.bookingGridForm);
updateCustomers();
UIControl.newBookingForm.updateCustomers();
}
}