//This action builds a collection of SelectList items based on the employees table in the DB
//The selected parameter is used to preselect an item in the SelectList. The selected item will be selected by default when the dropdown list on the view loads.
private SelectList GetEmployees(int selected)
{
using (HardwareDBEntities db = new HardwareDBEntities())
{
db.Configuration.ProxyCreationEnabled = false;
//Create a SelectListItem for each Employee record in the DB
//Value is set to the primary key of the record and Text is set to the Name of the Employee
var Emp = db.lgemployees.Select(x => new SelectListItem
{
Value = x.emp_num.ToString(),
Text = x.emp_fname
}).ToList();
//If selected pearameter has a value, configure the SelectList so that the apporiate item is preselected
if (selected == 0)
{
return(new SelectList(Emp, "Value", "Text"));
}
else
{
return(new SelectList(Emp, "Value", "Text", selected));
}
}
}
public SelectList GetCustomers(int selected)
{
using (HardwareDBEntities db = new HardwareDBEntities())
{
db.Configuration.ProxyCreationEnabled = false;
//Create a SelectListItem for each Vendor record in the DB
//Value is set to the primary key of the record and Text is set to the Name of the vendor
var Customer = db.lgcustomers.Select(x => new SelectListItem
{
Value = x.cust_code.ToString(),
Text = x.cust_fname + " " + x.cust_lname
}).ToList();
//If selected pearameter has a value, configure the SelectList so that the apporiate item is preselected
if (selected == 0)
{
return(new SelectList(Customer, "Value", "Text"));
}
else
{
return(new SelectList(Customer, "Value", "Text", selected));
}
}
}
public ActionResult Index(VMs vm)
{
using (HardwareDBEntities db = new HardwareDBEntities())
{
db.Configuration.ProxyCreationEnabled = false;
vm.Employees = GetEmployees(vm.SelectedEmployeeID);
vm.employee = db.lgemployees.Where(x => x.emp_num == vm.SelectedEmployeeID).FirstOrDefault();
var list = db.lginvoices.Where(x => x.employee_id == vm.SelectedEmployeeID && x.inv_DATETIME >= vm.DateFrom && x.inv_DATETIME <= vm.DateTo).ToList().Select(rr => new ReportRecord
{
OrderDate = Convert.ToDateTime(rr.inv_DATETIME).ToString("MMM. dd, yyyy"),
Amount = Convert.ToDouble(rr.inv_total),
CustomerID = Convert.ToInt32(rr.cust_code),
CustomerName = db.lgcustomers.Where(x => x.cust_code == rr.cust_code).FirstOrDefault().cust_fname,
CustomerSurname = db.lgcustomers.Where(x => x.cust_code == rr.cust_code).FirstOrDefault().cust_lname
}
);
vm.results = list.GroupBy(x => x.CustomerID).ToList();
vm.chartData = list.GroupBy(g => g.OrderDate.ToString()).ToDictionary(g => g.Key, g => g.Sum(v => v.Amount));
TempData["chartdata"] = vm.chartData;
TempData["records"] = list.ToList();
TempData["employee"] = vm.employee;
return(View(vm));
}
}
public ActionResult GetInfo(General GM)
{
//ViewBag.Message = "Your application description page.";
HardwareDBEntities db = new HardwareDBEntities();
//Retrieve a list of vendors so that it can be used to populate the dropdown on the View
//The ID of the currently selected item is passed through so that the returned list has that item preselected
GM.Customers = GetCustomers(GM.ID);
//Get the full details of the selected vendor so that it can be displayed on the view
GM.customer = db.lgcustomers.Where(zz => zz.cust_code == GM.ID).FirstOrDefault();
//Get all supplier orders that adheres to the entered criteria
//For each of the results, load data into a new ReportRecord object
var list = db.lginvoices.Where(zz => zz.cust_code == GM.customer.cust_code)
//Load the list of ReportRecords returned by the above query into a new list grouped by Shipment Method
//Load the list of ReportRecords returned by the above query into a new dictionary grouped by Employee
//This will be used to generate the chart on the View through the MicroSoft Charts helper
//Store the chartData dictionary in temporary data so that it can be accessed by the EmployeeOrdersChart action resonsible for generating the chart
TempData["chartData"] = vm.chartData;
TempData["records"] = list.ToList(); /***We use this list at different Action Result we can use
* tempdata to store the infromation and use somwhere else**/
TempData["vendor"] = vm.vendor; /**if we wanna be able to use this vendor details at different
* Action Result**/
return(View());
}
public ActionResult Advanced(int employee_id, string DateFrom, string DateTo)
{
AdvancedVM vm = new AdvancedVM {
employee_id = employee_id,
DateFrom = DateTime.Parse(DateFrom),
DateTo = DateTime.Parse(DateTo)
};
using (HardwareDBEntities db = new HardwareDBEntities())
{
db.Configuration.ProxyCreationEnabled = false;
vm.Employee = GetEmployee(vm.employee_id);
vm.employee = db.lgemployees.Where(x => x.emp_num == vm.employee_id).FirstOrDefault();
var test = db.lginvoices.Where(i => i.employee_id == vm.employee.emp_num).Where(i => i.inv_DATETIME <vm.DateTo && i.inv_DATETIME> vm.DateFrom).ToList().Select(r => new SalesRecord
{
OrderDate = (DateTime)r.inv_DATETIME,
Amount = Convert.ToDouble(r.inv_total),
Employee = vm.employee.emp_fname
}).ToList();
vm.results = test;
vm.chartData = test.GroupBy(g => g.Employee).ToDictionary(g => g.Key, g => g.Sum(v => v.Amount));
TempData["chartData"] = vm.chartData;
TempData["records"] = test.ToList();
TempData["employee"] = vm.employee;
return(View("ADVReporting", vm));
}
}
public List <dynamic> addEmployees([FromBody] List <lgemployee> employees)
{
HardwareDBEntities db = new HardwareDBEntities();
db.Configuration.ProxyCreationEnabled = false;
db.lgemployees.AddRange(employees);
db.SaveChanges();
return(getAllEmployees());
}
public List <dynamic> getAllEmployees()
{
HardwareDBEntities db = new HardwareDBEntities();
db.Configuration.ProxyCreationEnabled = false;
List <dynamic> empList = new List <dynamic>();
foreach (lgemployee employee in db.lgemployees)
{
dynamic employees = new ExpandoObject();
employees.emp_num = employee.emp_num;
employees.emp_fname = employee.emp_fname;
employees.emp_title = employee.emp_title;
empList.Add(employees);
}
return(empList);
}
public ActionResult Advanced(AdvancedVM vm)
{
using (HardwareDBEntities db = new HardwareDBEntities())
{
db.Configuration.ProxyCreationEnabled = false;
//to retrieve a list of the employees so as to populate the dropdown
vm.lgemployees = GetEmployees(vm.Selectedemp_num);
//to get the full details of the selected employee so that it can displayed on the view
vm.employee = db.lgemployees.Where(x => x.emp_num == vm.Selectedemp_num).FirstOrDefault();
//to get all the invoice details that adheres to the entered criteria
//For each of the results, load data into a new ReportRecord object
var temp = db.lginvoices.Include("lgcustomer").ToList();
var date = temp.Where(pp => pp.employee_id == vm.employee.emp_num &&
pp.inv_DATETIME >= vm.DateFrom &&
pp.inv_DATETIME <= vm.DateTo).ToList();
var list = date.ToList().Select(rr => new ReportRecord
{
INV_Date = rr.inv_DATETIME.ToString(),
Amount = Convert.ToDouble(rr.inv_total),
//Customer = db.lgcustomers.Where(pp => pp.cust_code == rr.cust_code).Select(x => x.cust_fname + " " + x.cust_lname).FirstOrDefault(),
Customer = rr.lgcustomer.cust_fname,
Employee_id = Convert.ToInt32(rr.employee_id)
});
vm.results = list.GroupBy(g => g.Customer).ToList();
//Load the list of ReportRecords returned by the above query into a new dictionary grouped by Employee
//This will be used to generate the chart on the View through the MicroSoft Charts helper
vm.chartData = list.GroupBy(g => g.Customer).ToDictionary(g => g.Key, g => g.Sum(v => v.Amount));
//Store the chartData dictionary in temporary data so that it can be accessed by the EmployeeOrdersChart action resonsible for generating the chart
TempData["chartData"] = vm.chartData;
TempData["records"] = list.ToList();
TempData["employee"] = vm.employee;
return(View(vm));
}
}
private SelectList GetEmployee(int selected)
{
using (HardwareDBEntities db = new HardwareDBEntities())
{
db.Configuration.ProxyCreationEnabled = false;
var employees = db.lgemployees.Select(x => new SelectListItem
{
Value = x.emp_num.ToString(),
Text = x.emp_fname
}).ToList();
if (selected == 0)
{
return(new SelectList(employees, "Value", "Text"));
}
else
{
return(new SelectList(employees, "Value", "Text", selected));
}
}
}
public ActionResult Report(ViewModelHardware vm)
{
using (HardwareDBEntities db = new HardwareDBEntities())
{
db.Configuration.ProxyCreationEnabled = false;
var list = db.lgproducts.Where(pp => pp.prod_qoh >= vm.lowerLimit && pp.prod_qoh <= vm.upperLimit).ToList().Select(rr => new ReportClass
{
prodDesc = rr.prod_descript,
prodType = rr.prod_type,
prodQOH = Convert.ToInt32(rr.prod_qoh),
});
vm.results = list.GroupBy(g => g.prodType).ToList();
vm.chartData = list.GroupBy(g => g.prodType).ToDictionary(g => g.Key, g => g.Sum(v => v.prodQOH));
TempData["chartData"] = vm.chartData;
return(View(vm));
}
}
private SelectList GetDepartments(int selected)
{
using (HardwareDBEntities db = new HardwareDBEntities())
{
db.Configuration.ProxyCreationEnabled = false;
var departments = db.lgdepartments.Select(x => new SelectListItem
{
Value = x.dept_num.ToString(),
Text = x.dept_name
}).ToList();
if (selected == 0)
{
return(new SelectList(departments, "Value", "Text"));
}
else
{
return(new SelectList(departments, "Value", "Text", selected));
}
}
}
public ActionResult ReportingTest(AdvancedViewModel vm)
{
using (HardwareDBEntities db = new HardwareDBEntities())
{
db.Configuration.ProxyCreationEnabled = false;
var list = db.lginvoices.Where(pp => pp.inv_DATETIME >= vm.DateFrom && pp.inv_DATETIME <= vm.DateTo).ToList().Select(xx => new ReportTest
{
inv_DATEIME = xx.inv_DATETIME.Value.ToString("yyyy-mm-dd"),
invoiceTotal = Convert.ToDouble(xx.inv_total),
custName = db.lgcustomers.Where(pp => pp.cust_code == xx.cust_code).Select(x => x.cust_fname + " " + x.cust_lname).FirstOrDefault(),
EmployeeName = db.lgemployees.Where(pp => pp.emp_num == xx.employee_id).Select(x => x.emp_fname).FirstOrDefault()
});
vm.pleaseWork = list.GroupBy(g => g.custName).ToList();
vm.chartData = list.GroupBy(g => g.EmployeeName).ToDictionary(g => g.Key, g => g.Sum(v => v.invoiceTotal));
TempData["chartData"] = vm.chartData;
return(View(vm));
}
}
public ActionResult DoReport(HardwareVM vm)
{
using (HardwareDBEntities db = new HardwareDBEntities())
{
db.Configuration.ProxyCreationEnabled = false;
vm.Employees = GetEmployees(vm.SelectedEmployeeID);
vm.employee = db.lgemployees.Where(x => x.emp_num == vm.SelectedEmployeeID).FirstOrDefault();
var list = db.lginvoices.Where(r => r.employee_id == vm.employee.emp_num && r.inv_DATETIME >= vm.DateFrom && r.inv_DATETIME <= vm.DateTo).ToList().Select(p => new ReportRecord
{
//OrderDate = p.inv_DATETIME.ToString("dd-MMM-yyyy"),
Total = Convert.ToDouble(p.inv_total),
Employee = db.lgemployees.Where(r => r.emp_num == p.employee_id).Select(x => x.emp_fname + "" + x.emp_lname).FirstOrDefault(),
});
}
//vm.chartData = list.GroupBy(g => g.Employee).ToDictionary(g => g.Key, g => g.Sum(v => v.Total));
TempData["chartData"] = vm.chartData;
//TempData["records"] = list.ToList();
TempData["vendor"] = vm.employee;
return(View(vm));
}
public ActionResult Advanced(AdView vm)
{
using (HardwareDBEntities db = new HardwareDBEntities())
{
db.Configuration.ProxyCreationEnabled = false;
//Retrieve a list of vendors so that it can be used to populate the dropdown on the View
//The ID of the currently selected item is passed through so that the returned list has that item preselected
vm.EmpNames = GetEmployees(vm.Empid);
//Get the full details of the selected vendor so that it can be displayed on the view
vm.empName = db.lgemployees.Where(zz => zz.emp_num == vm.Empid).FirstOrDefault();
//Get all supplier orders that adheres to the entered criteria
//For each of the results, load data into a new ReportRecord object
var list = db.lginvoices.Where(pp => pp.employee_id == vm.empName.emp_num && pp.inv_DATETIME >= vm.DateFrom && pp.inv_DATETIME <= vm.DateTo).ToList().Select(rr => new ReportRecord
{
OrderDate = rr.inv_DATETIME.ToString(),
Amount = Convert.ToDouble(rr.inv_total),
Employee = db.lgemployees.Where(pp => pp.emp_num == rr.employee_id).Select(x => x.emp_fname + " " + x.emp_lname).FirstOrDefault(),
Empid1 = Convert.ToInt32(rr.employee_id)
});
//Load the list of ReportRecords returned by the above query into a new list grouped by Shipment Method
vm.results = list.GroupBy(g => g.OrderDate).ToList();
//Load the list of ReportRecords returned by the above query into a new dictionary grouped by Employee
//This will be used to generate the chart on the View through the MicroSoft Charts helper
vm.chartData = list.GroupBy(g => g.OrderDate).ToDictionary(g => g.Key, g => g.Sum(v => v.Amount));
//Store the chartData dictionary in temporary data so that it can be accessed by the EmployeeOrdersChart action resonsible for generating the chart
TempData["chartData"] = vm.chartData;
TempData["records"] = list.ToList();
TempData["vendor"] = vm.empName;
return(View(vm));
}
}
public ActionResult Advanced(AdvancedVM vm)
{
using (HardwareDBEntities db = new HardwareDBEntities())
{
db.Configuration.ProxyCreationEnabled = false;
List <DepartmentEmployees> empList = new List <DepartmentEmployees>();
foreach (var employee in db.lgemployees.Where(emp => vm.SelectedDepartmentID == emp.dept_num && vm.DateFrom <= emp.emp_hireDATETIME && vm.DateTo >= emp.emp_hireDATETIME))
{
empList.Add(new DepartmentEmployees
{
EmployeeFName = employee.emp_fname,
EmployeeSName = employee.emp_lname,
EmployeeEmail = employee.emp_email,
EmployeeTitle = employee.emp_title,
EmplyeeID = employee.emp_num,
deptNum = employee.dept_num.Value
});
}
vm.Departments = GetDepartments(vm.SelectedDepartmentID);
vm.department = db.lgdepartments.Where(x => x.dept_num == vm.SelectedDepartmentID).FirstOrDefault();
//vm.department = db.dep.Where(x => x.BusinessEntityID == vm.SelectedVendorID).FirstOrDefault();
vm.depEmployess = empList;
vm.results = empList.GroupBy(g => g.EmployeeTitle).ToList();
empList.ToList();
vm.chartData = empList.GroupBy(g => g.EmployeeTitle).ToDictionary(g => g.Key, g => (double)g.Sum(v => v.deptNum));
vm.counter = empList.Count();
TempData["chartData"] = vm.chartData;
TempData["records"] = empList.ToList();
TempData["department"] = vm.department;
return(View(vm));
}
}
public ActionResult ReportingHistory(AdvancedViewModel vm)
{
using (HardwareDBEntities db = new HardwareDBEntities())
{
db.Configuration.ProxyCreationEnabled = false;
vm.empName = getEmployees(vm.SelectedEmpID);
vm.emps = db.lgemployees.Where(x => x.emp_num == vm.SelectedEmpID).FirstOrDefault();
var list = db.lginvoices.Where(pp => pp.employee_id == vm.emps.emp_num).ToList().Select(rr => new ReportRecord
{
empID = Convert.ToInt32(rr.employee_id),
invNum = (rr.inv_num).ToString(),
invTotal = Convert.ToDouble(rr.inv_total),
custName = db.lgcustomers.Where(pp => pp.cust_code == rr.cust_code).Select(x => x.cust_fname + " " + x.cust_lname).FirstOrDefault()
});
vm.results = list.GroupBy(g => g.empID.ToString()).ToList();
TempData["chartData"] = vm.chartData;
return(View(vm));
}
}
public List <dynamic> getTables()
{
HardwareDBEntities db = new HardwareDBEntities();
List <dynamic> tableList = new List <dynamic>();
foreach (lgemployee emp in db.lgemployees)
{
dynamic employees = new ExpandoObject();
employees.emp_num = emp.emp_num;
employees.emp_fname = emp.emp_fname;
//employees.dept_num = emp.dept_num;
tableList.Add(employees);
}
foreach (lgdepartment dept in db.lgdepartments)
{
dynamic department = new ExpandoObject();
department.dept_num = dept.dept_num;
department.dept_name = dept.dept_name;
department.emp_num = dept.emp_num;
tableList.Add(department);
}
return(tableList);
}
