public async static Task <ServiceType> LoadByDescription(string description) { try { ServiceType serviceType = new ServiceType(); await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { tblServiceType tblServicetype = dc.tblServiceTypes.FirstOrDefault(s => s.Description == description); if (tblServicetype != null) { serviceType.Id = tblServicetype.Id; serviceType.Description = tblServicetype.Description; serviceType.CostPerSQFT = tblServicetype.CostPerSqFt; } else { throw new Exception("Service type not found"); } } }); return(serviceType); } catch (Exception) { throw; } }
public async static Task <List <User> > Load() { try { List <User> users = new List <User>(); await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { dc.tblUsers .ToList() .ForEach(u => users .Add(new User { Id = u.Id, FirstName = u.FirstName, LastName = u.LastName, UserName = u.UserName, UserPass = u.UserPass })); } }); return(users); } catch (Exception) { throw; } }
public async static Task <List <ServiceType> > Load() { try { List <ServiceType> serviceTypes = new List <ServiceType>(); await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { dc.tblServiceTypes .ToList() .ForEach(s => serviceTypes .Add(new ServiceType { Id = s.Id, Description = s.Description, CostPerSQFT = s.CostPerSqFt })); } }); return(serviceTypes.OrderBy(s => s.Description).ToList()); } catch (Exception) { throw; } }
public static async Task <decimal> GetBalance(Guid customerId) { try { decimal balance = 0; await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { var parameters = new SqlParameter { ParameterName = "customerId", SqlDbType = System.Data.SqlDbType.UniqueIdentifier, Value = customerId }; var results = dc.Set <spGetCustomerBalanceResult>().FromSqlRaw("exec spGetCustomerBalance @customerId", parameters); foreach (var r in results) { balance = r.Balance; } } }); return(balance); } catch (Exception ex) { throw new Exception(ex.Message); } }
public async static Task <List <Appointment> > LoadByStatus(string status) { try { List <Appointment> appointments = new List <Appointment>(); await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { dc.tblAppointments .Where(a => a.Status == status) .ToList() .ForEach(a => appointments .Add(new Appointment { Id = a.Id, CustomerId = a.CustomerId, EmployeeId = a.EmployeeId, StartDateTime = a.StartDateTime, EndDateTime = a.EndDateTime, ServiceId = a.ServiceId, Status = a.Status })); } }); return(appointments.OrderBy(a => a.StartDateTime).ToList()); } catch (Exception) { throw; } }
public async static Task <User> LoadById(Guid id) { try { User user = new User(); await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { user = (from u in dc.tblUsers where u.Id == id select new User { Id = u.Id, FirstName = u.FirstName, LastName = u.LastName, UserName = u.UserName, UserPass = u.UserPass }).FirstOrDefault(); } }); return(user); } catch (Exception) { throw; } }
public async static Task <int> Delete(Guid id, bool rollback = false) { try { int results = 0; await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { // Check if user is associated with an exisiting customers or employees = do not allow delete .... bool inuse = dc.tblEmployees.Any(e => e.UserId == id); if (!inuse) { inuse = dc.tblCustomers.Any(c => c.UserId == id); } if (inuse) { throw new Exception("This user is associated with an existing Customers or Employee and therefore cannot be deleted."); } else { IDbContextTransaction transaction = null; if (rollback) { transaction = dc.Database.BeginTransaction(); } tblUser deleteRow = dc.tblUsers.FirstOrDefault(r => r.Id == id); if (deleteRow != null) { dc.tblUsers.Remove(deleteRow); // Commit the changes and get the number of rows affected results = dc.SaveChanges(); if (rollback) { transaction.Rollback(); } } else { throw new Exception("User was not found."); } } } }); return(results); } catch (Exception) { throw; } }
public async static Task <bool> Insert(User user, bool rollback = false) { try { int result = 0; if (user.Id.Equals(user.Id.ToString())) { user.Id = (Guid)user.Id; } await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { // Check if username already exists - do not allow .... bool inuse = dc.tblUsers.Any(u => u.UserName.Trim().ToUpper() == user.UserName.Trim().ToUpper()); if (inuse) { throw new Exception("This User Name already exists."); } else { IDbContextTransaction transaction = null; if (rollback) { transaction = dc.Database.BeginTransaction(); } tblUser newUser = new tblUser(); newUser.Id = Guid.NewGuid(); newUser.FirstName = user.FirstName.Trim(); newUser.LastName = user.LastName.Trim(); newUser.UserName = user.UserName.Trim(); newUser.UserPass = GetHash(user.UserPass.Trim()); user.Id = newUser.Id; dc.tblUsers.Add(newUser); result = dc.SaveChanges(); if (rollback) { transaction.Rollback(); } } } }); return(result == 1); } catch (Exception) { throw; } }
public async static Task <int> Delete(Guid id, bool rollback = false) { try { int results = 0; await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { // Check if user is associated with an exisiting appointment or invoice = do not allow delete .... bool inuse = dc.tblAppointments.Any(e => e.CustomerId == id); if (!inuse) { inuse = dc.tblInvoices.Any(c => c.CustomerId == id); } if (inuse && rollback == false) { throw new Exception("This customer is associated with an existing appointment or invoice and therefore cannot be deleted."); } else { IDbContextTransaction transaction = null; if (rollback) { transaction = dc.Database.BeginTransaction(); } tblCustomer deleteRow = dc.tblCustomers.FirstOrDefault(r => r.Id == id); if (deleteRow != null) { dc.tblCustomers.Remove(deleteRow); results = dc.SaveChanges(); if (rollback) { transaction.Rollback(); } } else { throw new Exception("Customer was not found."); } } } }); return(results); } catch (Exception) { throw; } }
public async static Task <int> Update(Appointment appointment, bool rollback = false) { try { int results = 0; await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { IDbContextTransaction transaction = null; if (rollback) { transaction = dc.Database.BeginTransaction(); } tblAppointment updateRow = dc.tblAppointments.FirstOrDefault(r => r.Id == appointment.Id); if (updateRow != null) { updateRow.CustomerId = appointment.CustomerId; updateRow.EmployeeId = appointment.EmployeeId; updateRow.StartDateTime = appointment.StartDateTime; updateRow.EndDateTime = appointment.EndDateTime; updateRow.ServiceId = appointment.ServiceId; updateRow.Status = appointment.Status; dc.tblAppointments.Update(updateRow); // Commit the changes and get the number of rows affected results = dc.SaveChanges(); if (appointment.Status == AppointmentStatus.Completed.ToString()) { GenerateInvoice(appointment); } if (rollback) { transaction.Rollback(); } } else { throw new Exception("Appointment was not found."); } } }); return(results); } catch (Exception) { throw; } }
public async static Task <int> Update(Customer customer, bool rollback = false) { try { int results = 0; await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { IDbContextTransaction transaction = null; if (rollback) { transaction = dc.Database.BeginTransaction(); } tblCustomer upDateRow = dc.tblCustomers.FirstOrDefault(r => r.Id == customer.Id); if (upDateRow != null) { upDateRow.FirstName = customer.FirstName; upDateRow.LastName = customer.LastName; upDateRow.StreetAddress = customer.StreetAddress; upDateRow.City = customer.City; upDateRow.State = customer.State; upDateRow.ZipCode = customer.ZipCode; upDateRow.Email = customer.Email; upDateRow.Phone = customer.Phone; upDateRow.PropertySqFt = customer.PropertySqFeet; upDateRow.UserId = customer.UserId; dc.tblCustomers.Update(upDateRow); // Commit the changes and get the number of rows affected results = dc.SaveChanges(); if (rollback) { transaction.Rollback(); } } else { throw new Exception("Customer was not found."); } } }); return(results); } catch (Exception) { throw; } }
public async static Task <User> Login(User user) { try { await Task.Run(() => { if (!string.IsNullOrEmpty(user.UserName)) { if (!string.IsNullOrEmpty(user.UserPass)) { using (LawnProEntities dc = new LawnProEntities()) { tblUser userrow = dc.tblUsers.FirstOrDefault(u => u.UserName == user.UserName); if (userrow != null) { // check the password if (userrow.UserPass == GetHash(user.UserPass)) { // Login was successfull user.Id = userrow.Id; user.FirstName = userrow.FirstName; user.LastName = userrow.LastName; user.UserName = userrow.UserName; user.UserPass = userrow.UserPass; } else { throw new Exception("Invalid credentials."); } } else { throw new Exception("User not found."); } } } else { throw new Exception("Password is required."); } } else { throw new Exception("Username is required."); } }); return(user); } catch (Exception) { throw; } }
public async static Task <bool> Insert(Customer customer, bool rollback = false) { try { int result = 0; await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { IDbContextTransaction transaction = null; if (rollback) { transaction = dc.Database.BeginTransaction(); } tblCustomer newRow = new tblCustomer(); // Teranary operator newRow.Id = Guid.NewGuid(); newRow.FirstName = customer.FirstName; newRow.LastName = customer.LastName; newRow.StreetAddress = customer.StreetAddress; newRow.City = customer.City; newRow.State = customer.State; newRow.ZipCode = customer.ZipCode; newRow.Email = customer.Email; newRow.Phone = customer.Phone; newRow.PropertySqFt = customer.PropertySqFeet; newRow.UserId = customer.UserId; // Backfill the id on the input parameter customer customer.Id = newRow.Id; // Insert the row dc.tblCustomers.Add(newRow); // Commit the changes and get the number of rows affected result = dc.SaveChanges(); if (rollback) { transaction.Rollback(); } } }); return(result == 1); } catch (Exception) { throw; } }
public async static Task <int> Delete(Guid id, bool rollback = false) { try { int results = 0; await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { // Check if appointment is in progress or completed .... bool inuse = dc.tblAppointments.Any(a => a.Id == id && a.Status == "InProgress" || a.Status == "Complete"); if (inuse && rollback == false) { throw new Exception("This appointment is currently in progress or completed and therefore cannot be deleted."); } else { IDbContextTransaction transaction = null; if (rollback) { transaction = dc.Database.BeginTransaction(); } tblAppointment deleteRow = dc.tblAppointments.FirstOrDefault(r => r.Id == id); if (deleteRow != null) { dc.tblAppointments.Remove(deleteRow); results = dc.SaveChanges(); if (rollback) { transaction.Rollback(); } } else { throw new Exception("Appointment was not found."); } } } }); return(results); } catch (Exception) { throw; } }
public async static Task <int> Update(Invoice invoice, bool rollback = false) { try { int results = 0; await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { IDbContextTransaction transaction = null; if (rollback) { transaction = dc.Database.BeginTransaction(); } tblInvoice updateRow = dc.tblInvoices.FirstOrDefault(s => s.Id == invoice.Id); if (updateRow != null) { updateRow.EmployeeName = invoice.EmployeeFullName; updateRow.ServiceDate = invoice.ServiceDate; updateRow.ServiceName = invoice.ServiceType; updateRow.ServiceRate = invoice.ServiceRate; updateRow.Status = invoice.Status; dc.tblInvoices.Update(updateRow); results = dc.SaveChanges(); if (rollback) { transaction.Rollback(); } } else { throw new Exception("Invoice not found"); } } }); return(results); } catch (Exception) { throw; } }
public async static Task <bool> Insert(Appointment appointment, bool rollback = false) { try { int result = 0; await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { IDbContextTransaction transaction = null; if (rollback) { transaction = dc.Database.BeginTransaction(); } tblAppointment newRow = new tblAppointment(); newRow.Id = Guid.NewGuid(); newRow.CustomerId = appointment.CustomerId; newRow.EmployeeId = appointment.EmployeeId; newRow.StartDateTime = appointment.StartDateTime; newRow.EndDateTime = appointment.EndDateTime; newRow.ServiceId = appointment.ServiceId; newRow.Status = appointment.Status; // Backfill the id on the input parameter appointment appointment.Id = newRow.Id; // Insert the row dc.tblAppointments.Add(newRow); // Commit the changes and get the number of rows affected result = dc.SaveChanges(); if (rollback) { transaction.Rollback(); } } }); return(result == 1); } catch (Exception) { throw; } }
public async static Task <bool> Insert(Invoice invoice, bool rollback = false) { try { int result = 0; await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { IDbContextTransaction transaction = null; if (rollback) { transaction = dc.Database.BeginTransaction(); } tblInvoice newrow = new tblInvoice(); newrow.Id = Guid.NewGuid(); newrow.CustomerId = invoice.CustomerId; newrow.EmployeeName = invoice.EmployeeFullName; newrow.ServiceDate = invoice.ServiceDate; newrow.ServiceName = invoice.ServiceType; newrow.ServiceRate = invoice.ServiceRate; newrow.Status = invoice.Status; invoice.Id = newrow.Id; dc.tblInvoices.Add(newrow); result = dc.SaveChanges(); if (rollback) { transaction.Rollback(); } } }); return(result == 1); } catch (Exception) { throw; } }
public async static Task <int> Delete(Guid id, bool rollback = false) { try { int results = 0; await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { bool inuse = dc.tblAppointments.Any(e => e.ServiceId == id); if (inuse && rollback == false) { throw new Exception("This service type is associated with an existing appointment and cannot be deleted"); } else { IDbContextTransaction transaction = null; if (rollback) { transaction = dc.Database.BeginTransaction(); } tblServiceType deleteRow = dc.tblServiceTypes.FirstOrDefault(s => s.Id == id); if (deleteRow != null) { dc.tblServiceTypes.Remove(deleteRow); results = dc.SaveChanges(); } else { throw new Exception("Service type not found"); } } } }); return(results); } catch (Exception) { throw; } }
public async static Task <int> Update(ServiceType serviceType, bool rollback = false) { try { int results = 0; await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { IDbContextTransaction transaction = null; if (rollback) { transaction = dc.Database.BeginTransaction(); } tblServiceType updateRow = dc.tblServiceTypes.FirstOrDefault(s => s.Id == serviceType.Id); if (updateRow != null) { updateRow.Description = serviceType.Description; updateRow.CostPerSqFt = serviceType.CostPerSQFT; dc.tblServiceTypes.Update(updateRow); results = dc.SaveChanges(); if (rollback) { transaction.Rollback(); } } else { throw new Exception("Service type not found"); } } }); return(results); } catch (Exception) { throw; } }
public async static Task <int> Delete(Guid id, bool rollback = false) { try { int results = 0; await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { bool inuse = dc.tblInvoices.Any(e => e.Id == id && e.Status != "Paid"); if (inuse && rollback == false) { throw new Exception("This Invoice has not been paid and cannot be deleted"); } else { IDbContextTransaction transaction = null; if (rollback) { transaction = dc.Database.BeginTransaction(); } tblInvoice deleteRow = dc.tblInvoices.FirstOrDefault(s => s.Id == id); if (deleteRow != null) { dc.tblInvoices.Remove(deleteRow); results = dc.SaveChanges(); } else { throw new Exception("Invoice not found"); } } } }); return(results); } catch (Exception) { throw; } }
public async static Task <Invoice> LoadById(Guid id) { try { Invoice invoice = new Invoice(); await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { invoice = (from i in dc.tblInvoices join c in dc.tblCustomers on i.CustomerId equals c.Id where i.Id == id select new Invoice { Id = i.Id, CustomerId = i.CustomerId, CustomerFirstName = c.FirstName, CustomerLastName = c.LastName, CustomerStreetAddress = c.StreetAddress, CustomerCity = c.City, CustomerState = c.State, CustomerZip = c.ZipCode, CustomerEmail = c.Email, EmployeeFullName = i.EmployeeName, PropertySqFt = c.PropertySqFt, ServiceDate = i.ServiceDate, ServiceType = i.ServiceName, ServiceRate = i.ServiceRate, Status = i.Status }).FirstOrDefault(); if (invoice == null) { throw new Exception("Invoice not found"); } } }); return(invoice); } catch (Exception) { throw; } }
public async static Task <bool> Insert(ServiceType serviceType, bool rollback = false) { try { int result = 0; await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { IDbContextTransaction transaction = null; if (rollback) { transaction = dc.Database.BeginTransaction(); } tblServiceType newrow = new tblServiceType(); newrow.Id = Guid.NewGuid(); newrow.Description = serviceType.Description; newrow.CostPerSqFt = serviceType.CostPerSQFT; serviceType.Id = newrow.Id; dc.tblServiceTypes.Add(newrow); result = dc.SaveChanges(); if (rollback) { transaction.Rollback(); } } }); return(result == 1); } catch (Exception) { throw; } }
public static async Task <bool> Seed() { List <User> users = new List <User>(); await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { dc.tblUsers .ToList() .ForEach(u => users .Add(new User { Id = u.Id, FirstName = u.FirstName, LastName = u.LastName, UserName = u.UserName, UserPass = u.UserPass })); foreach (User user in users) { if (user.UserPass.Length != 28) { tblUser upDateRow = dc.tblUsers.FirstOrDefault(r => r.Id == user.Id); if (upDateRow != null) { upDateRow.UserPass = GetHash(user.UserPass.Trim()); } dc.tblUsers.Update(upDateRow); // Commit the changes and get the number of rows affected dc.SaveChanges(); } } } }); return(true); }
public async static Task <Customer> LoadByUserId(Guid userId) { try { Customer customer = new Customer(); await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { tblCustomer row = dc.tblCustomers.FirstOrDefault(r => r.UserId == userId); if (row != null) { customer.Id = row.Id; customer.FirstName = row.FirstName; customer.LastName = row.LastName; customer.StreetAddress = row.StreetAddress; customer.City = row.City; customer.State = row.State; customer.ZipCode = row.ZipCode; customer.Email = row.Email; customer.Phone = row.Phone; customer.PropertySqFeet = row.PropertySqFt; customer.UserId = row.UserId; } else { throw new Exception("Customer not found"); } } }); return(customer); } catch (Exception) { throw; } }
public async static Task <Employee> LoadByUserId(Guid userId) { try { Employee employee = new Employee(); await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { tblEmployee row = dc.tblEmployees.FirstOrDefault(r => r.UserId == userId); if (row != null) { employee.Id = row.Id; employee.FirstName = row.FirstName; employee.LastName = row.LastName; employee.StreetAddress = row.StreetAddress; employee.City = row.City; employee.State = row.State; employee.ZipCode = row.ZipCode; employee.Email = row.Email; employee.Phone = row.Phone; employee.UserId = row.UserId; } else { throw new Exception("Employee not found"); } } }); return(employee); } catch (Exception) { throw; } }
public async static Task <List <Customer> > Load() { try { List <Customer> customers = new List <Customer>(); await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { dc.tblCustomers .ToList() .ForEach(r => customers .Add(new Customer { Id = r.Id, FirstName = r.FirstName, LastName = r.LastName, StreetAddress = r.StreetAddress, City = r.City, State = r.State, ZipCode = r.ZipCode, Email = r.Email, Phone = r.Phone, PropertySqFeet = r.PropertySqFt, UserId = r.UserId })); } }); return(customers); } catch (Exception) { throw; } }
public async static Task <List <Employee> > Load() { try { List <Employee> employees = new List <Employee>(); await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { dc.tblEmployees .ToList() .ForEach(r => employees .Add(new Employee { Id = r.Id, FirstName = r.FirstName, LastName = r.LastName, StreetAddress = r.StreetAddress, City = r.City, State = r.State, ZipCode = r.ZipCode, Email = r.Email, Phone = r.Phone, UserId = r.UserId })); } }); return(employees); } catch (Exception) { throw; } }
public void TestCleanup() { transaction.Rollback(); transaction.Dispose(); dc = null; }
public void TestInitialize() { dc = new LawnProEntities(); transaction = dc.Database.BeginTransaction(); }
public async static Task <Appointment> LoadById(Guid id) { Appointment result = new Appointment(); try { if (id != Guid.Empty) { await Task.Run(() => { using (LawnProEntities dc = new LawnProEntities()) { var appointment = (from a in dc.tblAppointments join c in dc.tblCustomers on a.CustomerId equals c.Id join e in dc.tblEmployees on a.EmployeeId equals e.Id into es from e in es.DefaultIfEmpty() join st in dc.tblServiceTypes on a.ServiceId equals st.Id where a.Id == id select new { a.Id, a.CustomerId, a.EmployeeId, a.StartDateTime, a.EndDateTime, a.ServiceId, a.Status, CustomerFirstName = c.FirstName, CustomerLastName = c.LastName, EmployeeFirstName = e.FirstName, EmployeeLastName = e.LastName, StreetAddress = c.StreetAddress, City = c.City, State = c.State, ZipCode = c.ZipCode, Email = c.Email, Phone = c.Phone, PropertySqFeet = c.PropertySqFt, ServiceType = st.Description, ServiceRate = st.CostPerSqFt }).FirstOrDefault(); if (appointment != null) { result = new Appointment { Id = appointment.Id, CustomerId = appointment.CustomerId, EmployeeId = appointment.EmployeeId, StartDateTime = appointment.StartDateTime, EndDateTime = appointment.EndDateTime, ServiceId = appointment.ServiceId, Status = appointment.Status, CustomerFirstName = appointment.CustomerFirstName, CustomerLastName = appointment.CustomerLastName, EmployeeFirstName = appointment.EmployeeFirstName, EmployeeLastName = appointment.EmployeeLastName, StreetAddress = appointment.StreetAddress, City = appointment.City, State = appointment.State, ZipCode = appointment.ZipCode, Email = appointment.Email, Phone = appointment.Phone, PropertySqFeet = appointment.PropertySqFeet, ServiceType = appointment.ServiceType, ServiceRate = appointment.ServiceRate }; } else { throw new Exception("Appointment was not found"); } } }); return(result); } else { throw new Exception("Please provide an id"); } } catch (Exception) { throw; } }