public CService(string nId, string nName, float nPrice) { id = nId; name = nName; price = nPrice; employee = null; }
private void mainForm_Load(object sender, EventArgs e) { connection.openConnection(); // Screen 2 OleDbCommand getCustomers = new OleDbCommand(); getCustomers.Connection = connection.connection; getCustomers.CommandText = "SELECT * FROM Customers"; OleDbDataReader customerReader = getCustomers.ExecuteReader(); while (customerReader.Read()) { string id = customerReader[0].ToString(); string first_Name = customerReader[1].ToString(); string last_Name = customerReader[2].ToString(); string address = customerReader[3].ToString(); string phone = customerReader[4].ToString(); DateTime birthday = (DateTime)customerReader[5]; CCustomer tmpCustomer = new CCustomer(id, first_Name, last_Name, address, phone, birthday); c_CustomersList.Items.Add(tmpCustomer); } // Screen 3 OleDbCommand getServices = new OleDbCommand(); getServices.Connection = connection.connection; getServices.CommandText = "SELECT * FROM Services"; OleDbDataReader servicesReader = getServices.ExecuteReader(); while (servicesReader.Read()) { string id = servicesReader[0].ToString(); string name = servicesReader[1].ToString(); string price = servicesReader[2].ToString(); CService tmpService = new CService(id, name, float.Parse(price)); s_ServicesList.Items.Add(tmpService); } OleDbCommand getPackages = new OleDbCommand(); getPackages.Connection = connection.connection; getPackages.CommandText = "SELECT * FROM Packages"; OleDbDataReader packageReader = getPackages.ExecuteReader(); while (packageReader.Read()) { string id = packageReader[0].ToString(); string name = packageReader[1].ToString(); string price = packageReader[2].ToString(); OleDbCommand getPackageServices = new OleDbCommand(); getPackageServices.Connection = connection.connection; getPackageServices.CommandText = "SELECT Services.ID, Service_Name, Price FROM Services, Package_Services WHERE Package_Services.ID_Package = " + id + " AND Services.ID = Package_Services.ID_Service"; OleDbDataReader packageServicesReader = getPackageServices.ExecuteReader(); List<CService> services = new List<CService>(); while (packageServicesReader.Read()) { string serviceId = packageServicesReader[0].ToString(); string serviceName = packageServicesReader[1].ToString(); string servicePrice = packageServicesReader[2].ToString(); CService tmpSerice = new CService(serviceId, serviceName, float.Parse(servicePrice)); services.Add(tmpSerice); } CPackage tmpPackage = new CPackage(id, name, float.Parse(price), services); s_PackagesList.Items.Add(tmpPackage); } // Screen 4 OleDbCommand getEmployees = new OleDbCommand(); getEmployees.Connection = connection.connection; getEmployees.CommandText = "SELECT * FROM Employees"; OleDbDataReader employeesReader = getEmployees.ExecuteReader(); e_employees = new List<CEmployee>(); while (employeesReader.Read()) { string id = employeesReader[0].ToString(); string first_Name = employeesReader[1].ToString(); string last_Name = employeesReader[2].ToString(); string address = employeesReader[3].ToString(); string phone = employeesReader[4].ToString(); DateTime birthday = (DateTime)employeesReader[5]; DateTime employmend = (DateTime)employeesReader[6]; bool fired = (bool)employeesReader[7]; CEmployee tmpEmployee = new CEmployee(id, first_Name, last_Name, address, phone, birthday, employmend,fired); e_employees.Add(tmpEmployee); } connection.closeConnection(); if (c_CustomersList.Items.Count > 0) c_CustomersList.SelectedIndex = 0; else { c_EditBtn.Enabled = false; c_DeleteBtn.Enabled = false; c_LoadBtn.Enabled = false; c_ViewBilBtn.Enabled = false; } if (s_ServicesList.Items.Count > 0) s_ServicesList.SelectedIndex = 0; else { s_ServiceDelete.Enabled = false; s_ServiceEdit.Enabled = false; s_PackageAdd.Enabled = false; } if (s_PackagesList.Items.Count > 0) s_PackagesList.SelectedIndex = 0; else { s_PackageDelete.Enabled = false; s_PackageEdit.Enabled = false; } fillE_EmployeesList(); }
private void c_LoadBtn_Click(object sender, EventArgs e) { c_LoadBtn.Enabled = false; c_BillsList.Items.Clear(); CCustomer tmpCustomer = c_CustomersList.SelectedItem as CCustomer; connection.openConnection(); OleDbCommand billsCommand = new OleDbCommand(); billsCommand.Connection = connection.connection; billsCommand.CommandText = "SELECT * FROM Bills WHERE ID_Customer = " + tmpCustomer.id + " ORDER BY Bill_Date ASC"; OleDbDataReader billsReader = billsCommand.ExecuteReader(); while (billsReader.Read()) { string idBill = billsReader[0].ToString(); DateTime billDate = (DateTime)billsReader[1]; string billPrice = billsReader[2].ToString(); string billNote = billsReader[3].ToString(); OleDbCommand billServicesCommand = new OleDbCommand(); billServicesCommand.Connection = connection.connection; billServicesCommand.CommandText = "SELECT Services.* FROM Services, Bill_Services WHERE Bill_Services.ID_Service = Services.ID AND Bill_Services.ID_Bill = " + idBill; OleDbDataReader billServicesReader = billServicesCommand.ExecuteReader(); List<CService> tmpServices = new List<CService>(); while (billServicesReader.Read()) { string idService = billServicesReader[0].ToString(); string name = billServicesReader[1].ToString(); string servicePrice = billServicesReader[2].ToString(); CService tmpSerice = new CService(idService, name, float.Parse(servicePrice)); OleDbCommand billServiceEmployeeCommand = new OleDbCommand(); billServiceEmployeeCommand.Connection = connection.connection; billServiceEmployeeCommand.CommandText = "SELECT Employees.* FROM Employees, Bill_Services WHERE Bill_Services.ID_Bill = " + idBill + " AND Bill_Services.ID_Service = " + idService + "AND Bill_Services.ID_Employee = Employees.ID"; OleDbDataReader billServiceEmployeeReader = billServiceEmployeeCommand.ExecuteReader(); billServiceEmployeeReader.Read(); string idEmployee = billServiceEmployeeReader[0].ToString(); string first_name = billServiceEmployeeReader[1].ToString(); string last_name = billServiceEmployeeReader[2].ToString(); string address = billServiceEmployeeReader[3].ToString(); string phone = billServiceEmployeeReader[4].ToString(); DateTime birthday = (DateTime)billServiceEmployeeReader[5]; DateTime dayOfEmploymend = (DateTime)billServiceEmployeeReader[6]; bool fired = (bool)billServiceEmployeeReader[7]; CEmployee tmpEmployee = new CEmployee(idEmployee, first_name, last_name, address, phone, birthday, dayOfEmploymend, fired); tmpSerice.employee = tmpEmployee; tmpServices.Add(tmpSerice); } OleDbCommand billPackages = new OleDbCommand(); billPackages.Connection = connection.connection; billPackages.CommandText = "SELECT DISTINCT Packages.* FROM Packages, Bill_PServices WHERE Packages.ID = Bill_PServices.ID_Package AND Bill_PServices.ID_Bill = " + idBill; OleDbDataReader billPackagesReader = billPackages.ExecuteReader(); List<CPackage> tmpPackages = new List<CPackage>(); while (billPackagesReader.Read()) { string idPackage = billPackagesReader[0].ToString(); string packageName = billPackagesReader[1].ToString(); string packagePrice = billPackagesReader[2].ToString(); OleDbCommand billPackageServices = new OleDbCommand(); billPackageServices.Connection = connection.connection; billPackageServices.CommandText = "SELECT Services.* FROM Services, Bill_PServices WHERE Services.ID = Bill_PServices.ID_Service AND Bill_PServices.ID_Package = " + idPackage; OleDbDataReader billPackageServicesReader = billPackageServices.ExecuteReader(); List<CService> tmpPServices = new List<CService>(); while (billPackageServicesReader.Read()) { string idService = billPackageServicesReader[0].ToString(); string name = billPackageServicesReader[1].ToString(); string servicePrice = billPackageServicesReader[2].ToString(); CService tmpSerice = new CService(idService, name, float.Parse(servicePrice)); OleDbCommand billServiceEmployeeCommand = new OleDbCommand(); billServiceEmployeeCommand.Connection = connection.connection; billServiceEmployeeCommand.CommandText = "SELECT Employees.* FROM Employees, Bill_PServices WHERE Bill_PServices.ID_Bill = " + idBill + " AND Bill_PServices.ID_Service = " + idService + " AND Bill_PServices.ID_Employee = Employees.ID AND Bill_PServices.ID_Package = " + idPackage; OleDbDataReader billServiceEmployeeReader = billServiceEmployeeCommand.ExecuteReader(); billServiceEmployeeReader.Read(); string idEmployee = billServiceEmployeeReader[0].ToString(); string first_name = billServiceEmployeeReader[1].ToString(); string last_name = billServiceEmployeeReader[2].ToString(); string address = billServiceEmployeeReader[3].ToString(); string phone = billServiceEmployeeReader[4].ToString(); DateTime birthday = (DateTime)billServiceEmployeeReader[5]; DateTime dayOfEmploymend = (DateTime)billServiceEmployeeReader[6]; bool fired = (bool)billServiceEmployeeReader[7]; CEmployee tmpEmployee = new CEmployee(idEmployee, first_name, last_name, address, phone, birthday, dayOfEmploymend, fired); tmpSerice.employee = tmpEmployee; tmpPServices.Add(tmpSerice); } CPackage tmpPackage = new CPackage(idPackage, packageName, float.Parse(packagePrice), tmpPServices); tmpPackages.Add(tmpPackage); } CBill tmpBill = new CBill(idBill,billNote,float.Parse(billPrice),tmpPackages,tmpServices,billDate,tmpCustomer); c_BillsList.Items.Add(tmpBill); } if (c_BillsList.Items.Count > 0) { c_BillsList.SelectedIndex = 0; c_ViewBilBtn.Enabled = true; } else MessageBox.Show(tmpCustomer.name + " " + tmpCustomer.lastName + " doesn't have any bills yet!", "No bills found", MessageBoxButtons.OK, MessageBoxIcon.Information); connection.closeConnection(); }
private void button1_Click(object sender, EventArgs e) { refreshBtn.Enabled = false; allBillsList.Items.Clear(); ab_TotalBills = 0; ab_TotalPrice = 0.0f; connection.openConnection(); OleDbCommand billsCommand = new OleDbCommand(); billsCommand.Connection = connection.connection; billsCommand.CommandText = "SELECT * FROM Bills ORDER BY Bill_Date ASC"; OleDbDataReader billsReader = billsCommand.ExecuteReader(); while (billsReader.Read()) { string idBill = billsReader[0].ToString(); DateTime billDate = (DateTime)billsReader[1]; string billPrice = billsReader[2].ToString(); string billNote = billsReader[3].ToString(); string idCustomer = billsReader[4].ToString(); // FIlter by Date if (!(billDate.Date >= ab_From.Value.Date && billDate.Date <= ab_To.Value.Date)) continue; // Get Customer OleDbCommand customerComm = new OleDbCommand(); customerComm.Connection = connection.connection; customerComm.CommandText = "SELECT * FROM Customers WHERE ID = " + idCustomer; OleDbDataReader customerReader = customerComm.ExecuteReader(); customerReader.Read(); string cID = customerReader[0].ToString(); string CFN = customerReader[1].ToString(); string CLN = customerReader[2].ToString(); string CA = customerReader[3].ToString(); string CP = customerReader[4].ToString(); DateTime CB = (DateTime)customerReader[5]; CCustomer tmpCustomer = new CCustomer(cID, CFN, CLN, CA, CP, CB); OleDbCommand billServicesCommand = new OleDbCommand(); billServicesCommand.Connection = connection.connection; billServicesCommand.CommandText = "SELECT Services.* FROM Services, Bill_Services WHERE Bill_Services.ID_Service = Services.ID AND Bill_Services.ID_Bill = " + idBill; OleDbDataReader billServicesReader = billServicesCommand.ExecuteReader(); List<CService> tmpServices = new List<CService>(); while (billServicesReader.Read()) { string idService = billServicesReader[0].ToString(); string name = billServicesReader[1].ToString(); string servicePrice = billServicesReader[2].ToString(); CService tmpSerice = new CService(idService, name, float.Parse(servicePrice)); OleDbCommand billServiceEmployeeCommand = new OleDbCommand(); billServiceEmployeeCommand.Connection = connection.connection; billServiceEmployeeCommand.CommandText = "SELECT Employees.* FROM Employees, Bill_Services WHERE Bill_Services.ID_Bill = " + idBill + " AND Bill_Services.ID_Service = " + idService + "AND Bill_Services.ID_Employee = Employees.ID"; OleDbDataReader billServiceEmployeeReader = billServiceEmployeeCommand.ExecuteReader(); billServiceEmployeeReader.Read(); string idEmployee = billServiceEmployeeReader[0].ToString(); string first_name = billServiceEmployeeReader[1].ToString(); string last_name = billServiceEmployeeReader[2].ToString(); string address = billServiceEmployeeReader[3].ToString(); string phone = billServiceEmployeeReader[4].ToString(); DateTime birthday = (DateTime)billServiceEmployeeReader[5]; DateTime dayOfEmploymend = (DateTime)billServiceEmployeeReader[6]; bool fired = (bool)billServiceEmployeeReader[7]; CEmployee tmpEmployee = new CEmployee(idEmployee, first_name, last_name, address, phone, birthday, dayOfEmploymend, fired); tmpSerice.employee = tmpEmployee; tmpServices.Add(tmpSerice); } OleDbCommand billPackages = new OleDbCommand(); billPackages.Connection = connection.connection; billPackages.CommandText = "SELECT DISTINCT Packages.* FROM Packages, Bill_PServices WHERE Packages.ID = Bill_PServices.ID_Package AND Bill_PServices.ID_Bill = " + idBill; OleDbDataReader billPackagesReader = billPackages.ExecuteReader(); List<CPackage> tmpPackages = new List<CPackage>(); while (billPackagesReader.Read()) { string idPackage = billPackagesReader[0].ToString(); string packageName = billPackagesReader[1].ToString(); string packagePrice = billPackagesReader[2].ToString(); OleDbCommand billPackageServices = new OleDbCommand(); billPackageServices.Connection = connection.connection; billPackageServices.CommandText = "SELECT Services.* FROM Services, Bill_PServices WHERE Services.ID = Bill_PServices.ID_Service AND Bill_PServices.ID_Package = " + idPackage; OleDbDataReader billPackageServicesReader = billPackageServices.ExecuteReader(); List<CService> tmpPServices = new List<CService>(); while (billPackageServicesReader.Read()) { string idService = billPackageServicesReader[0].ToString(); string name = billPackageServicesReader[1].ToString(); string servicePrice = billPackageServicesReader[2].ToString(); CService tmpSerice = new CService(idService, name, float.Parse(servicePrice)); OleDbCommand billServiceEmployeeCommand = new OleDbCommand(); billServiceEmployeeCommand.Connection = connection.connection; billServiceEmployeeCommand.CommandText = "SELECT Employees.* FROM Employees, Bill_PServices WHERE Bill_PServices.ID_Bill = " + idBill + " AND Bill_PServices.ID_Service = " + idService + " AND Bill_PServices.ID_Employee = Employees.ID AND Bill_PServices.ID_Package = " + idPackage; OleDbDataReader billServiceEmployeeReader = billServiceEmployeeCommand.ExecuteReader(); billServiceEmployeeReader.Read(); string idEmployee = billServiceEmployeeReader[0].ToString(); string first_name = billServiceEmployeeReader[1].ToString(); string last_name = billServiceEmployeeReader[2].ToString(); string address = billServiceEmployeeReader[3].ToString(); string phone = billServiceEmployeeReader[4].ToString(); DateTime birthday = (DateTime)billServiceEmployeeReader[5]; DateTime dayOfEmploymend = (DateTime)billServiceEmployeeReader[6]; bool fired = (bool)billServiceEmployeeReader[7]; CEmployee tmpEmployee = new CEmployee(idEmployee, first_name, last_name, address, phone, birthday, dayOfEmploymend, fired); tmpSerice.employee = tmpEmployee; tmpPServices.Add(tmpSerice); } CPackage tmpPackage = new CPackage(idPackage, packageName, float.Parse(packagePrice), tmpPServices); tmpPackages.Add(tmpPackage); } CBill tmpBill = new CBill(idBill, billNote, float.Parse(billPrice), tmpPackages, tmpServices, billDate, tmpCustomer); tmpBill.inAll = true; allBillsList.Items.Add(tmpBill); ab_TotalBills += 1; ab_TotalPrice += tmpBill.price; } connection.closeConnection(); if (allBillsList.Items.Count == 0) { MessageBox.Show("There arent any bills in that period", "No bills found", MessageBoxButtons.OK, MessageBoxIcon.Information); button26.Enabled = false; ab_TotalBillTxt.Text = ""; ab_TotalIncomeTxt.Text = ""; } else { button26.Enabled = true; ab_TotalBillTxt.Text = ab_TotalBills.ToString(); ab_TotalIncomeTxt.Text = ab_TotalPrice.ToString(); allBillsList.SelectedIndex = 0; } }
private void selectPackage_Load(object sender, EventArgs e) { Connection connection = new Connection(); connection.openConnection(); OleDbCommand getPackages = new OleDbCommand(); getPackages.Connection = connection.connection; getPackages.CommandText = "SELECT * FROM Packages"; OleDbDataReader packageReader = getPackages.ExecuteReader(); while (packageReader.Read()) { string id = packageReader[0].ToString(); string name = packageReader[1].ToString(); string price = packageReader[2].ToString(); OleDbCommand getPackageServices = new OleDbCommand(); getPackageServices.Connection = connection.connection; getPackageServices.CommandText = "SELECT Services.ID, Service_Name, Price FROM Services, Package_Services WHERE Package_Services.ID_Package = " + id + " AND Services.ID = Package_Services.ID_Service"; OleDbDataReader packageServicesReader = getPackageServices.ExecuteReader(); List<CService> services = new List<CService>(); while (packageServicesReader.Read()) { string serviceId = packageServicesReader[0].ToString(); string serviceName = packageServicesReader[1].ToString(); string servicePrice = packageServicesReader[2].ToString(); CService tmpSerice = new CService(serviceId, serviceName, float.Parse(servicePrice)); services.Add(tmpSerice); } CPackage tmpPackage = new CPackage(id, name, float.Parse(price), services); packagesList.Items.Add(tmpPackage); } OleDbCommand getEmployees = new OleDbCommand(); getEmployees.Connection = connection.connection; getEmployees.CommandText = "SELECT * FROM Employees"; OleDbDataReader employeesReader = getEmployees.ExecuteReader(); while (employeesReader.Read()) { string id = employeesReader[0].ToString(); string first_name = employeesReader[1].ToString(); string last_name = employeesReader[2].ToString(); string address = employeesReader[3].ToString(); string phone = employeesReader[4].ToString(); DateTime birthday = (DateTime)employeesReader[5]; DateTime dayOfEmploymend = (DateTime)employeesReader[6]; bool fired = (bool)employeesReader[7]; CEmployee tmpEmployee = new CEmployee(id,first_name, last_name, address, phone, birthday, dayOfEmploymend,fired); if (!fired) employeeList.Items.Add(tmpEmployee); } connection.closeConnection(); if (packagesList.Items.Count == 0) { doneBtn.Enabled = false; employeeList.Enabled = false; } else packagesList.SelectedIndex = 0; }
private void addbtn_Click(object sender, EventArgs e) { if (nameTxt.Text.Length < 1 || lastNameTxt.Text.Length < 1 || addressTxt.Text.Length < 1 || phoneTxt.Text.Length < 1) { MessageBox.Show("All fields must be entered!", "Invalid Values", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (birthdayPicker.Value.AddYears(18) > DateTime.Today) { MessageBox.Show("Your employee must be atleast 18 years old!", "Invalid Birthday", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } Connection connection = new Connection(); connection.openConnection(); OleDbCommand employeeCommand = new OleDbCommand(); employeeCommand.Connection = connection.connection; string name = nameTxt.Text; string lastName = lastNameTxt.Text; string address = addressTxt.Text; string phone = phoneTxt.Text; string birthday = birthdayPicker.Value.ToShortDateString(); string employment = DateTime.Now.ToString(); if (edit) { employeeCommand.CommandText = "UPDATE Employees SET First_Name = '" + name + "', Last_Name = '" + lastName + "', Address = '" + address + "', Phone = '" + phone + "', Birthday = #" + birthday + "# WHERE ID = " + employee.id; employeeCommand.ExecuteNonQuery(); employee.name = name; employee.lastName = lastName; employee.address = address; employee.phone = phone; employee.birthday = birthdayPicker.Value.Date; } else { employeeCommand.CommandText = "INSERT INTO Employees (First_Name,Last_Name,Address,Phone,Birthday,Employmend,Fired) VALUES ('" + name + "', '" + lastName + "', '" + address + "', '" + phone + "', #" + birthday + "#, #" + employment + "#, false)"; employeeCommand.ExecuteNonQuery(); employeeCommand.CommandText = "Select @@Identity"; string idEmployee = employeeCommand.ExecuteScalar().ToString(); employee = new CEmployee(idEmployee, name, lastName, address, phone, birthdayPicker.Value.Date, DateTime.Now, false); } connection.closeConnection(); DialogResult = System.Windows.Forms.DialogResult.OK; Close(); }
private void selectService_Load(object sender, EventArgs e) { Connection connection = new Connection(); connection.openConnection(); OleDbCommand getServices = new OleDbCommand(); getServices.Connection = connection.connection; getServices.CommandText = "SELECT * FROM Services"; OleDbDataReader servicesReader = getServices.ExecuteReader(); while (servicesReader.Read()) { string id = servicesReader[0].ToString(); string name = servicesReader[1].ToString(); string price = servicesReader[2].ToString(); CService tmpSerice = new CService(id, name, float.Parse(price)); servicesList.Items.Add(tmpSerice); } OleDbCommand getEmployees = new OleDbCommand(); getEmployees.Connection = connection.connection; getEmployees.CommandText = "SELECT * FROM Employees"; OleDbDataReader employeesReader = getEmployees.ExecuteReader(); while (employeesReader.Read()) { string id = employeesReader[0].ToString(); string first_name = employeesReader[1].ToString(); string last_name = employeesReader[2].ToString(); string address = employeesReader[3].ToString(); string phone = employeesReader[4].ToString(); DateTime birthday = (DateTime)employeesReader[5]; DateTime dayOfEmploymend = (DateTime)employeesReader[6]; bool fired = (bool)employeesReader[7]; CEmployee tmpEmployee = new CEmployee(id, first_name, last_name, address, phone, birthday, dayOfEmploymend,fired); if (!fired) employeesList.Items.Add(tmpEmployee); } connection.closeConnection(); if (servicesList.Items.Count == 0) { doneBtn.Enabled = false; employeesList.Enabled = false; } else servicesList.SelectedIndex = 0; }