public WorkModel GetDetailModel(string Details)
{
WorksheetEntities entities = new WorksheetEntities();
string[] Sheet = Details.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
int count = Sheet.Count();
string workid = Sheet[count - 2];
Timesheet sheet = (from ts in entities.Timesheets where (ts.Id_WorkAssignment.ToString() == workid) select ts).FirstOrDefault();
string cont = (from con in entities.Contractors where (con.Id_Contractor == sheet.Id_Contractor) select con.CompanyName).Single();
string cust = (from c in entities.Customers where (c.Id_Customer == sheet.Id_Customer) select c.CustomerName).Single();
string work = (from co in entities.WorkAssignments where (co.Id_WorkAssignment == sheet.Id_WorkAssignment) select co.Title).Single();
string emp = (from e in entities.Employees where (e.Id_Employee == sheet.Id_Employee) select e.FirstName + " " + e.LastName).Single();
double workTime = (sheet.StopTime.Value - sheet.StartTime.Value).TotalHours;
string comments = (from ct in entities.Timesheets where (ct.Id_Timesheet == sheet.Id_Timesheet) select ct.Comments).Single();
WorkModel details = new WorkModel()
{
CustomerName = cust,
ContractorName = cont,
FirstName = emp,
WorkTitle = work,
CountedHours = workTime,
Comments = comments
};
return(details);
}
public WorkModel GetProfileInfo(string userName)
{
WorksheetEntities entities = new WorksheetEntities();
try
{
Employee profile = (from p in entities.Employees where (p.Active == true) && (p.Username == userName) select p).FirstOrDefault();
string cont = (from c in entities.Contractors where (c.Id_Contractor == profile.Id_Contractor) select c.CompanyName).Single();
WorkModel loggedProfile = new WorkModel()
{
ContractorName = cont,
FirstName = profile.FirstName,
LastName = profile.LastName,
PhoneNumber = int.Parse(profile.PhoneNumber),
Email = profile.EmailAddress,
Picture = profile.EmployeePicture,
UserName = profile.Username
};
return(loggedProfile);
}
finally
{
entities.Dispose();
}
}
// GET: Getting model
public WorkModel GetContractorModel(string contractorName)
{
WorksheetEntities entities = new WorksheetEntities();
try
{
string chosenContractor = contractorName;
Contractor contractor = (from co in entities.Contractors where (co.Active == true) && (co.CompanyName == chosenContractor) select co).FirstOrDefault();
WorkModel chosenContractorModel = new WorkModel()
{
ContractorId = contractor.Id_Contractor,
ContractorName = contractor.CompanyName,
ContractorContactPerson = contractor.ContactPerson,
ContractorPhoneNumber = contractor.PhoneNumber,
ContractorEmail = contractor.EmailAddress,
VatId = contractor.VatId,
HourlyRate = contractor.HourlyRate.ToString(),
};
return(chosenContractorModel);
}
finally
{
entities.Dispose();
}
}
// GET: Getting model
public WorkModel GetModel(string employeeName)
{
WorksheetEntities entities = new WorksheetEntities();
try
{
string chosenEmployee = employeeName;
Employee employee = (from e in entities.Employees where (e.Active == true) && (e.Id_Employee.ToString() == chosenEmployee) select e).FirstOrDefault();
WorkModel chosenEmployeeModel = new WorkModel()
{
FirstName = employee.FirstName,
LastName = employee.LastName,
PhoneNumber = int.Parse(employee.PhoneNumber),
Email = employee.EmailAddress,
Picture = employee.EmployeePicture
};
return(chosenEmployeeModel);
}
finally
{
entities.Dispose();
}
}
public List <string> GetAll()
{
string[] employees = null;
WorksheetEntities entities = new WorksheetEntities();
List <string> empList = new List <string>();
employees = (from e in entities.Employees where (e.Active == true) select e.Id_Contractor + " " + e.Id_Employee).ToArray();
try
{
foreach (var item in employees)
{
string[] data = item.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
string contractor = data[0];
string contr = (from c in entities.Contractors where (c.Id_Contractor.ToString() == contractor) select c.CompanyName).Single();
string employee = data[1];
string emp = (from e in entities.Employees where (e.Id_Employee.ToString() == employee) select e.FirstName + " " + e.LastName).Single();
empList.Add("Contractor: " + contr + " | Name: " + emp + " ( " + employee + " )");
}
}
finally
{
entities.Dispose();
}
return(empList);
}
// GET: Trying to return model
public WorkModel GetModel(string workName)
{
WorksheetEntities entities = new WorksheetEntities();
try
{
string chosenWorkId = workName;
WorkAssignment chosenWorkData = (from cw in entities.WorkAssignments
where (cw.Active == true) && (cw.Id_WorkAssignment.ToString() == chosenWorkId)
select cw).FirstOrDefault();
WorkModel chosenWorkModel = new WorkModel()
{
WorkTitle = chosenWorkData.Title,
Description = chosenWorkData.Description,
Deadline = chosenWorkData.Deadline.Value
};
return(chosenWorkModel);
}
finally
{
entities.Dispose();
}
}
public List <string> TimeSheetList(int tsID)
{
WorksheetEntities entities = new WorksheetEntities();
List <string> realList = new List <string>();
tsID = 2;
string[] sheetId = (from ts in entities.Timesheets where (ts.WorkComplete == true) select ts.Id_Contractor.ToString() + " " + ts.Id_Employee.ToString() + " " + ts.Id_WorkAssignment.ToString()).ToArray();
try
{
foreach (var item in sheetId)
{
string[] data = item.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
string contractor = data[0];
string contr = (from c in entities.Contractors where (c.Id_Contractor.ToString() == contractor) select c.CompanyName).Single();
string employee = data[1];
string emp = (from e in entities.Employees where (e.Id_Employee.ToString() == employee) select e.FirstName + " " + e.LastName).Single();
string workid = data[2];
string work = (from w in entities.WorkAssignments where (w.Id_WorkAssignment.ToString() == workid) select w.Title).Single();
realList.Add(contr + " | " + emp + " | " + work + " " + "( " + workid + " )");
}
}
finally
{
entities.Dispose();
}
return(realList);
}
// GET: Getting model
public WorkModel GetCustomerModel(string customerName)
{
WorksheetEntities entities = new WorksheetEntities();
try
{
string chosenCustomer = customerName;
Customer customer = (from c in entities.Customers where (c.Active == true) && (c.CustomerName == chosenCustomer) select c).FirstOrDefault();
WorkModel chosenCustomerModel = new WorkModel()
{
CustomerName = customer.CustomerName,
ContactPerson = customer.ContactPerson,
CustomerPhoneNumber = customer.PhoneNumber,
CustomerEmail = customer.EmailAddress,
CustomerId = customer.Id_Customer
};
return(chosenCustomerModel);
}
finally
{
entities.Dispose();
}
}
// Gets all the data for the picker
public List <string[]> GetPickerData(int picker)
{
WorksheetEntities entities = new WorksheetEntities();
List <string[]> pickerData = new List <string[]>();
string[] emp = { "Employees:---------- " };
string[] work = { "Workassignments:---------- " };
string[] cont = { "Contractors:---------- " };
try
{
string[] workAssignments = (from wa in entities.WorkAssignments where (wa.Active == true) select wa.Title).ToArray();
string [] employees = (from e in entities.Employees where (e.Active == true) select e.FirstName + " " + e.LastName).ToArray();
string[] contractors = (from co in entities.Contractors where (co.Active == true) select co.CompanyName).ToArray();
pickerData.Add(work);
pickerData.Add(workAssignments);
pickerData.Add(emp);
pickerData.Add(employees);
pickerData.Add(cont);
pickerData.Add(contractors);
}
finally
{
entities.Dispose();
}
return(pickerData);
}
public bool PostChanges(WorkModel model)
{
WorksheetEntities entities = new WorksheetEntities();
try
{
Employee MyProfile = (from ce in entities.Employees where (ce.Active == true) && (ce.FirstName + " " + ce.LastName == model.FirstName + " " + model.LastName) select ce).FirstOrDefault();
if (MyProfile == null)
{
return(false);
}
int employeeId = MyProfile.Id_Employee;
Employee existing = (from e in entities.Employees where (e.Id_Employee == employeeId) && (e.Active == true) select e).FirstOrDefault();
if (existing != null && model.Picture != null && model.Operation == "Save")
{
existing.FirstName = model.FirstName;
existing.LastName = model.LastName;
existing.PhoneNumber = model.PhoneNumber.ToString();
existing.EmailAddress = model.Email;
existing.LastModifiedAt = DateTime.Now;
existing.EmployeePicture = model.Picture;
}
else if (existing != null && model.Picture == null && model.Operation == "Save")
{
existing.FirstName = model.FirstName;
existing.LastName = model.LastName;
existing.PhoneNumber = model.PhoneNumber.ToString();
existing.EmailAddress = model.Email;
existing.LastModifiedAt = DateTime.Now;
}
else if (existing != null && model.Operation == "SavePW")
{
existing.Password = model.Password;
}
entities.SaveChanges();
}
catch
{
return(false);
}
finally
{
entities.Dispose();
}
return(true);
}
public string[] GetAll()
{
string[] WorkAssignments = null;
WorksheetEntities entities = new WorksheetEntities();
try
{
WorkAssignments = (from wa in entities.WorkAssignments where (wa.Active == true) && (wa.InProgressAt == null) select wa.Title + " Deadline: " + wa.Deadline + " ( " + wa.Id_WorkAssignment + " )").ToArray();
}
finally
{
entities.Dispose();
}
return(WorkAssignments);
}
public string[] GetAll()
{
string[] customers = null;
WorksheetEntities entities = new WorksheetEntities();
try
{
customers = (from c in entities.Customers where (c.Active == true) select c.CustomerName).ToArray();
}
finally
{
entities.Dispose();
}
return(customers);
}
// GET: Get works in progress
public string[] GetWorksInProgress(int id)
{
WorksheetEntities entities = new WorksheetEntities();
id = 5;
try
{
string[] chosenWorkData = (from cw in entities.WorkAssignments
where (cw.Active == true) && (cw.InProgressAt != null) && (cw.Completed != true)
select cw.Title + " | Started at: " + cw.InProgressAt + " ( " + cw.Id_WorkAssignment + " )").ToArray();
return(chosenWorkData);
}
finally
{
entities.Dispose();
}
}
public bool GetLogin(string LogData)
{
WorksheetEntities entities = new WorksheetEntities();
string[] logDataParts = LogData.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
string usrname = logDataParts[0];
string pw = logDataParts[1];
SHA512 sha512 = SHA512.Create();
byte[] bytes = Encoding.UTF8.GetBytes(pw);
byte[] hash = sha512.ComputeHash(bytes);
Employee emp = (from e in entities.Employees where (e.Username == usrname) select e).FirstOrDefault();
StringBuilder dbPW = new StringBuilder();
for (int i = 0; i < emp.Password.Length; i++)
{
dbPW.Append(emp.Password[i].ToString("X2"));
}
StringBuilder logDataPW = new StringBuilder();
for (int i = 0; i < hash.Length; i++)
{
logDataPW.Append(hash[i].ToString("X2"));
}
string DBPass = dbPW.ToString();
string logDataPass = logDataPW.ToString();
if (emp.Username + DBPass == usrname + logDataPass)
{
return(true);
}
return(false);
}
public bool PostWork(WorkModel model)
{
WorksheetEntities entities = new WorksheetEntities();
Customer customer = (from cu in entities.Customers where (cu.Active == true) && (cu.CustomerName == model.CustomerName) select cu).FirstOrDefault();
try
{
// Save chosen work
if (model.Operation == "Save")
{
WorkAssignment newEntry = new WorkAssignment()
{
Id_Customer = customer.Id_Customer,
Title = model.WorkTitle,
Description = model.Description,
Deadline = model.Deadline,
InProgress = true,
CreatedAt = DateTime.Now,
Active = true
};
entities.WorkAssignments.Add(newEntry);
}
// Modify chosen work
else if (model.Operation == "Modify")
{
WorkAssignment existing = (from wa in entities.WorkAssignments where (wa.Id_WorkAssignment == model.WorkID) && (wa.Active == true) select wa).FirstOrDefault();
if (existing != null)
{
existing.Title = model.WorkTitle;
existing.Description = model.Description;
existing.Deadline = model.Deadline;
existing.LastModifiedAt = DateTime.Now;
}
else
{
return(false);
}
}
// Delete chosen work
else if (model.Operation == "Delete")
{
WorkAssignment existing = (from wa in entities.WorkAssignments where (wa.Id_WorkAssignment == model.WorkID) select wa).FirstOrDefault();
if (existing != null)
{
entities.WorkAssignments.Remove(existing);
}
else
{
return(false);
}
}
// Assign chosen work to an employee
else if (model.Operation == "Assign")
{
WorkAssignment assignment = (from wa in entities.WorkAssignments where (wa.Id_WorkAssignment == model.WorkID) && (wa.Active == true) && (wa.InProgress == true) select wa).FirstOrDefault();
if (assignment == null)
{
return(false);
}
Employee emp = (from e in entities.Employees where (e.Id_Employee == model.EmployeeId) select e).FirstOrDefault();
if (emp == null)
{
return(false);
}
int workId = assignment.Id_WorkAssignment;
int customerId = assignment.Id_Customer.Value;
assignment.InProgressAt = DateTime.Now;
Timesheet newEntry = new Timesheet()
{
Id_Customer = customerId,
Id_Contractor = emp.Id_Contractor,
Id_Employee = emp.Id_Employee,
Id_WorkAssignment = workId,
StartTime = DateTime.Now,
CreatedAt = DateTime.Now,
Active = true,
WorkComplete = false
};
entities.Timesheets.Add(newEntry);
}
else if (model.Operation == "MarkComplete")
{
WorkAssignment assignment = (from wa in entities.WorkAssignments where (wa.Id_WorkAssignment == model.WorkID) && (wa.Active == true) && (wa.InProgress == true) && (wa.InProgressAt != null) select wa).FirstOrDefault();
if (assignment == null)
{
return(false);
}
int workId = assignment.Id_WorkAssignment;
int customerId = assignment.Id_Customer.Value;
assignment.CompletedAt = DateTime.Now;
assignment.Completed = true;
assignment.InProgress = false;
Timesheet existing = (from e in entities.Timesheets where (e.Id_WorkAssignment == workId) && (e.Id_Customer == customerId) select e).FirstOrDefault();
if (existing != null)
{
existing.WorkComplete = true;
existing.StopTime = DateTime.Now;
existing.LastModifiedAt = DateTime.Now;
existing.Comments = "Work set to complete by Admin";
}
}
entities.SaveChanges();
}
catch
{
return(false);
}
finally
{
entities.Dispose();
}
return(true);
}
// Method to get same data as in timesheet mainpage, but for the chosen entity only
public List <string> GetChosenEntity(string Entity)
{
WorksheetEntities entities = new WorksheetEntities();
string[] sheet = null;
string[] workAssignments = (from wa in entities.WorkAssignments where (wa.Active == true) select wa.Title).ToArray();
string[] employees = (from e in entities.Employees where (e.Active == true) select e.FirstName + " " + e.LastName).ToArray();
string[] contractors = (from cont in entities.Contractors where (cont.Active == true) select cont.CompanyName).ToArray();
List <string[]> empData = new List <string[]>();
List <string[]> contData = new List <string[]>();
List <string[]> WorkData = new List <string[]>();
WorkData.Add(workAssignments);
empData.Add(employees);
contData.Add(contractors);
foreach (var item in empData)
{
foreach (var i in item)
{
if (i.ToString() == Entity)
{
string emplo = (from e in entities.Employees where (e.FirstName + " " + e.LastName == Entity) select e.Id_Employee.ToString()).Single();
sheet = (from ts in entities.Timesheets where (ts.Id_Employee.ToString() == emplo) select ts.Id_Contractor.ToString() + " " + ts.Id_Employee.ToString() + " " + ts.Id_WorkAssignment.ToString()).ToArray();
}
}
}
foreach (var itemm in WorkData)
{
foreach (var it in itemm)
{
if (it.ToString() == Entity)
{
string worka = (from w in entities.WorkAssignments where (w.Title == Entity) select w.Id_WorkAssignment.ToString()).Single();
sheet = (from ts in entities.Timesheets where (ts.Id_WorkAssignment.ToString() == worka) select ts.Id_Contractor.ToString() + " " + ts.Id_Employee.ToString() + " " + ts.Id_WorkAssignment.ToString()).ToArray();
}
}
}
foreach (var iten in contData)
{
foreach (var ite in iten)
{
if (ite.ToString() == Entity)
{
string contra = (from c in entities.Contractors where (c.CompanyName == Entity) select c.Id_Contractor.ToString()).Single();
sheet = (from ts in entities.Timesheets where (ts.Id_Contractor.ToString() == contra) select ts.Id_Contractor.ToString() + " " + ts.Id_Employee.ToString() + " " + ts.Id_WorkAssignment.ToString()).ToArray();
}
}
}
List <string> entityList = new List <string>();
try
{
foreach (var item in sheet)
{
string[] datas = item.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
string contractor = datas[0];
string contr = (from c in entities.Contractors where (c.Id_Contractor.ToString() == contractor) select c.CompanyName).Single();
string employee = datas[1];
string emp = (from e in entities.Employees where (e.Id_Employee.ToString() == employee) select e.FirstName + " " + e.LastName).Single();
string workid = datas[2];
string work = (from w in entities.WorkAssignments where (w.Id_WorkAssignment.ToString() == workid) select w.Title).Single();
entityList.Add(contr + " | " + emp + " | " + work + " " + "( " + workid + " )");
}
}
finally
{
entities.Dispose();
}
return(entityList);
}
// POST: Contractors
public bool PostContractor(WorkModel model)
{
WorksheetEntities entities = new WorksheetEntities();
try
{
if (model.ContOperation == "Save")
{
Contractor newEntry = new Contractor()
{
CompanyName = model.ContractorName,
ContactPerson = model.ContractorContactPerson,
PhoneNumber = model.ContractorPhoneNumber,
EmailAddress = model.ContractorEmail,
VatId = model.VatId,
HourlyRate = int.Parse(model.HourlyRate),
Active = true,
CreatedAt = DateTime.Now
};
entities.Contractors.Add(newEntry);
}
// Modify chosen contractor
else if (model.ContOperation == "Modify")
{
Contractor existing = (from co in entities.Contractors where (co.Id_Contractor == model.ContractorId) && (co.Active == true) select co).FirstOrDefault();
if (existing != null)
{
existing.CompanyName = model.ContractorName;
existing.ContactPerson = model.ContractorContactPerson;
existing.PhoneNumber = model.ContractorPhoneNumber;
existing.EmailAddress = model.ContractorEmail;
existing.VatId = model.VatId;
existing.HourlyRate = int.Parse(model.HourlyRate);
existing.LastModifiedAt = DateTime.Now;
}
else
{
return(false);
}
}
// Delete chosen contractor
else if (model.ContOperation == "Delete")
{
Contractor existing = (from e in entities.Contractors where (e.Id_Contractor == model.ContractorId) select e).FirstOrDefault();
if (existing != null)
{
entities.Contractors.Remove(existing);
}
else
{
return(false);
}
}
entities.SaveChanges();
}
catch
{
return(false);
}
finally
{
entities.Dispose();
}
return(true);
}
public bool PostEmployee(WorkModel model)
{
WorksheetEntities entities = new WorksheetEntities();
try
{
if (model.EmpOperation == "Save")
{
Contractor contractor = (from c in entities.Contractors where (c.CompanyName == model.ContractorName) select c).FirstOrDefault();
Employee newEntry = new Employee()
{
Id_Contractor = contractor.Id_Contractor,
Username = model.UserName,
Password = model.Password,
FirstName = model.FirstName,
LastName = model.LastName,
PhoneNumber = model.PhoneNumber.ToString(),
EmailAddress = model.Email,
CreatedAt = DateTime.Now,
Active = true,
};
entities.Employees.Add(newEntry);
}
// Modify chosen employee
else if (model.EmpOperation == "Modify")
{
Employee existing = (from e in entities.Employees where (e.Id_Employee == model.EmployeeId) && (e.Active == true) select e).FirstOrDefault();
if (existing != null)
{
existing.FirstName = model.FirstName;
existing.LastName = model.LastName;
existing.PhoneNumber = model.PhoneNumber.ToString();
existing.EmailAddress = model.Email;
existing.LastModifiedAt = DateTime.Now;
existing.EmployeePicture = model.Picture;
}
else
{
return(false);
}
}
// Delete chosen work
else if (model.EmpOperation == "Delete")
{
Employee existing = (from e in entities.Employees where (e.Id_Employee == model.EmployeeId) select e).FirstOrDefault();
if (existing != null)
{
entities.Employees.Remove(existing);
}
else
{
return(false);
}
}
entities.SaveChanges();
}
catch
{
return(false);
}
finally
{
entities.Dispose();
}
return(true);
}
// POST: Customers
public bool PostCustomer(WorkModel model)
{
WorksheetEntities entities = new WorksheetEntities();
try
{
if (model.CustOperation == "Save")
{
Customer newEntry = new Customer()
{
CustomerName = model.CustomerName,
ContactPerson = model.ContactPerson,
PhoneNumber = model.CustomerPhoneNumber,
EmailAddress = model.CustomerEmail,
CreatedAt = DateTime.Now,
Active = true
};
entities.Customers.Add(newEntry);
}
// Modify chosen customer
else if (model.CustOperation == "Modify")
{
Customer existing = (from c in entities.Customers where (c.Id_Customer == model.CustomerId) && (c.Active == true) select c).FirstOrDefault();
if (existing != null)
{
existing.CustomerName = model.CustomerName;
existing.ContactPerson = model.ContactPerson;
existing.PhoneNumber = model.CustomerPhoneNumber;
existing.EmailAddress = model.CustomerEmail;
existing.LastModifiedAt = DateTime.Now;
}
else
{
return(false);
}
}
// Delete chosen customer
else if (model.CustOperation == "Delete")
{
Customer chosenCustomer = (from cc in entities.Customers where (cc.CustomerName == model.CustomerName) select cc).FirstOrDefault();
if (chosenCustomer == null)
{
return(false);
}
int customerId = chosenCustomer.Id_Customer;
Customer existing = (from e in entities.Customers where (e.Id_Customer == customerId) select e).FirstOrDefault();
if (existing != null)
{
entities.Customers.Remove(existing);
}
else
{
return(false);
}
}
entities.SaveChanges();
}
catch
{
return(false);
}
finally
{
entities.Dispose();
}
return(true);
}