public List <CatalogueModel> ListCatalogues()
{
StationeryStoreEntities entities = new StationeryStoreEntities();
List <CatalogueModel> employees = new List <CatalogueModel>();
try
{
employees = entities.CatalogueInventories.Select <CatalogueInventory, CatalogueModel>
(c => new CatalogueModel()
{
ItemID = c.ItemID,
CatID = c.CatID,
Description = c.Item_Description,
UnitCost = c.UnitCost,
ActualQty = c.ActualQty
}).ToList <CatalogueModel>();
}
catch (NullReferenceException)
{
return(null);
}
catch (Exception e)
{
return(null);
}
return(employees);
}
// use this method when clerk clicks on raise request button
public static void CreateVoucher(int employeeId)
{
using (StationeryStoreEntities _entities = new StationeryStoreEntities())
{
Voucher voucher = new Voucher
{
SubmissionDate = DateTime.Now,
ApprovalDate = null,
EmployeeID = employeeId, // employee id should be shifted to voucher details or not? // employee should be able to see only his own request that he raised?
Status = "Pending Supervisor Approval"
};
var updateVoucherDetailList = _entities.VoucherDetail
.Where(v => v.VoucherID == null && v.EmployeeID == employeeId)
.ToList();
foreach (VoucherDetail v in updateVoucherDetailList)
{
v.VoucherID = voucher.VoucherID;
}
_entities.Voucher.Add(voucher);
_entities.SaveChanges();
}
}
public static List <Supplier> ListSupplier()
{
using (StationeryStoreEntities context = new StationeryStoreEntities())
{
return(context.Supplier.ToList <Supplier>());
}
}
public static void CreateDelegation(int EmpID, DateTime start, DateTime end, int DepID)
{
using (StationeryStoreEntities context = new StationeryStoreEntities())
{
Delegation s = new Delegation
{
EmployeeID = EmpID,
StartDate = start,
EndDate = end,
DepartmentID = DepID,
};
context.Delegation.Add(s);
context.SaveChanges();
Employee e = context.Employee.Where(x => x.EmployeeID == EmpID).FirstOrDefault();
String from = "*****@*****.**";
String to = e.Email;
String subject = "[Auto Notification] Delegation Status";
String body = String.Format("You are Delegated from " + start + " to " + end +
"\n\nNote: This is an auto-generated email. Please do not reply to this email." +
"\n\nThis email is confidential and may be privileged.If you are not the intended recipient, " +
"please delete it and notify us immediately; you should not copy or use it for any purpose, " +
"nor disclose its contents to any other person.\n\nThank you.");
MailBizLogic.sendMail(from, to, subject, body);
}
}
public List <ViewRequest> ViewPendingRequest(int id)
{
using (StationeryStoreEntities context = new StationeryStoreEntities())
{
List <Request> r = context.Requests.Where(x => x.Employee.DepartmentID == id && x.Status == "Pending").ToList(); //RequestID is null when request is not submitted
List <ViewRequest> req = new List <ViewRequest>();
for (int i = 0; i < r.Count; i++)
{
decimal?[] amt = new decimal?[r.Count];
amt[i] = 0;
int rid = r[i].RequestID;
List <RequestDetail> l = context.RequestDetails.Where(x => x.RequestID == rid).ToList();
for (int j = 0; j < l.Count; j++)
{
amt[i] += l[j].CatalogueInventory.UnitCost * l[j].Qty;
}
// foreach (RequestDetail rd in l)
//{
// amt[i] += rd.CatalogueInventory.UnitCost * rd.Qty;
//}
//decimal? d = (decimal)20.00;
ViewRequest rc = new ViewRequest(r[i].RequestID, r[i].Employee.Name, r[i].SubmissionDate, r[i].Status, r[i].Remarks, amt[i]);
req.Add(rc);
}
return(req);
}
}
//9. View approved list of request
//8. View delivering list of request
public static List <ViewRequest> ViewPendingRequest(int id)
{
using (StationeryStoreEntities context = new StationeryStoreEntities())
{
List <Request> r = context.Request.Where(x => x.EmployeeID == id && x.Status == "Pending").ToList(); //RequestID is null when request is not submitted
List <ViewRequest> req = new List <ViewRequest>();
for (int i = 0; i < r.Count; i++)
{
decimal?[] amt = new decimal?[r.Count];
amt[i] = 0;
HashSet <RequestDetail> l = (HashSet <RequestDetail>)r[i].RequestDetails;
foreach (RequestDetail rd in l)
{
amt[i] += rd.CatalogueInventory.UnitCost * rd.Qty;
}
ViewRequest rc = new ViewRequest(r[i].RequestID, null, String.Format("{0:ddd, MMM d, yyyy}", r[i].SubmissionDate), null, amt[i], null, null);
req.Add(rc);
}
return(req);
}
}
public List <RequestModel> ListRequests()
{
StationeryStoreEntities entities = new StationeryStoreEntities();
List <RequestModel> requests = new List <RequestModel>();
try
{
requests = entities.Requests
.Select <Request, RequestModel>
(c => new RequestModel()
{
EmployeeID = c.EmployeeID
}).ToList <RequestModel>();
}
catch (NullReferenceException)
{
return(null);
}
catch (Exception e)
{
return(null);
}
return(requests);
}
public UserRepCollection ListUserRepByDeptID(int Did)
{
StationeryStoreEntities entities = new StationeryStoreEntities();
List <UserRepCollection> userrepcoll = new List <UserRepCollection>();
try
{
userrepcoll = entities.UserRepCollections.Where(a => a.DepartmentID == Did)
.ToList <UserRepCollection>();
}
catch (NullReferenceException)
{
return(null);
}
catch (Exception e)
{
return(null);
}
if (userrepcoll.Count > 0)
{
return(userrepcoll[0]);
}
else
{
return(null);
}
}
public IHttpActionResult GetOrderById(string moduleid)
{
StationeryStoreEntities context = new StationeryStoreEntities();
var order = context.Orders.Where(s => s.OrderID == moduleid).FirstOrDefault <Order>();
return(Ok(order));
}
public AspNetUser FindUserByEmail(String Email)
{
StationeryStoreEntities entities = new StationeryStoreEntities();
List <AspNetUser> users = new List <AspNetUser>();
try
{
users = entities.AspNetUsers.Where(a => a.Email == Email).ToList();
}
catch (NullReferenceException)
{
return(null);
}
catch (Exception e)
{
return(null);
}
if (users.Count > 0)
{
return(users[0]);
}
else
{
return(null);
}
}
public static void UpdateStatus(int voucherId, int count)
{
string to;
using (StationeryStoreEntities context = new StationeryStoreEntities())
{
var item = context.Voucher.Where(c => c.VoucherID == voucherId).Single();
item.Status = "Pending Manager Approval";
context.SaveChanges();
to = item.Employee.Email;
}
if (count == 0)
{
//mail to employee who raised request
String from = "*****@*****.**";
String subject = "[Auto Notification] Voucher Status";
String body = String.Format("Your voucher {0} has been moved to Manager notice, as the amount of item is more than $250." +
"\n\nNote: This is an auto-generated email. Please do not reply to this email." +
"\n\nThis email is confidential and may be privileged.If you are not the intended recipient, " +
"please delete it and notify us immediately; you should not copy or use it for any purpose, " +
"nor disclose its contents to any other person.\n\nThank you.", voucherId);
MailBizLogic.sendMail(from, to, subject, body);
}
}
public static void RejectVoucher(int voucherId, DateTime datetime, string remarks, int count)
{
string to;
using (StationeryStoreEntities context = new StationeryStoreEntities())
{
var item = context.Voucher.Where(c => c.VoucherID == voucherId).Single();
item.Status = "Rejected";
item.ApprovalDate = datetime;
item.Remarks = remarks;
context.SaveChanges();
to = item.Employee.Email;
}
if (count == 0)
{
//mail to employee who raised request
String from = "*****@*****.**";
String subject = "[Auto Notification] Voucher Status";
String body = String.Format("Sorry! Your Voucher {0} has been rejected." +
"\n\nNote: This is an auto-generated email. Please do not reply to this email." +
"\n\nThis email is confidential and may be privileged.If you are not the intended recipient, " +
"please delete it and notify us immediately; you should not copy or use it for any purpose, " +
"nor disclose its contents to any other person.\n\nThank you.", voucherId);
MailBizLogic.sendMail(from, to, subject, body);
}
}
public Employee ListEmployeeByUserID1(String UserID)
{
StationeryStoreEntities entities = new StationeryStoreEntities();
List <Employee> employees = new List <Employee>();
try
{
int id = Convert.ToInt32(UserID);
employees = entities.Employees.Where(a => a.EmployeeID == id).ToList();
}
catch (NullReferenceException)
{
return(null);
}
catch (Exception e)
{
return(null);
}
if (employees.Count > 0)
{
return(employees[0]);
}
else
{
return(null);
}
}
public List <DelegationModel> ListDelegations()
{
StationeryStoreEntities entities = new StationeryStoreEntities();
List <DelegationModel> requests = new List <DelegationModel>();
try
{
requests = entities.Delegations.Select <Delegation, DelegationModel>
(c => new DelegationModel()
{
EmployeeID = c.EmployeeID,
DelegationID = c.DelegationID,
DepartmentID = c.DepartmentID,
StartDate = c.StartDate,
EndDate = c.EndDate
}).ToList <DelegationModel>();
}
catch (NullReferenceException)
{
return(null);
}
catch (Exception e)
{
return(null);
}
return(requests);
}
//7. View rejected list of request
public static List <ViewRequest> ViewRejectedRequest(int id)
{
using (StationeryStoreEntities context = new StationeryStoreEntities())
{
List <Request> r = context.Request.Where(x => x.EmployeeID == id && x.Status == "Rejected").ToList(); //RequestID is null when request is not submitted
List <ViewRequest> req = new List <ViewRequest>();
HashSet <RequestDetail> l = new HashSet <RequestDetail>();
for (int i = 0; i < r.Count; i++)
{
decimal?[] qty = new decimal?[r.Count];
qty[i] = 0;
string[] desc = new string[r.Count];
desc[i] = null;
l = (HashSet <RequestDetail>)r[i].RequestDetails;
foreach (RequestDetail rd in l)
{
desc[i] = rd.CatalogueInventory.Item_Description;
qty[i] = rd.Qty;
}
ViewRequest rc = new ViewRequest(r[i].RequestID, desc[i], String.Format("{0:ddd, MMM d, yyyy}", r[i].SubmissionDate), null, qty[i], null, r[i].Remarks);
req.Add(rc);
}
return(req);
}
}
public Employee ListEmployeeByUserID(string UserID)
{
StationeryStoreEntities entities = new StationeryStoreEntities();
List <Employee> employees = new List <Employee>();
try
{
employees = entities.Employees.Where(a => a.Id == UserID).ToList();
}
catch (NullReferenceException)
{
return(null);
}
catch (Exception e)
{
return(null);
}
if (employees.Count > 0)
{
return(employees[0]);
}
else
{
return(null);
}
}
public static List <Disbursement> GenerateDisbursement()
{
using (StationeryStoreEntities context = new StationeryStoreEntities())
{
DateTime today = DateTime.Now;
var requestdetaillist = context.RequestDetails.Where(x => x.Request.Status == "Approved" && x.Request.ApprovalDate < today).ToList();
var list = requestdetaillist.GroupBy(h => new { h.Request.Employee.DepartmentID, h.ItemID })
.Select(group => new
{
DepartmentID = group.Key.DepartmentID,
ItemID = group.Key.ItemID,
DisbursedQty = group.Sum(s => s.Qty),
});
List <Disbursement> disburse = new List <Disbursement>();
foreach (var q in list)
{
Disbursement d = new Disbursement();
d.DepartmentID = q.DepartmentID;
d.DisbursedQty = q.DisbursedQty;
d.ItemID = q.ItemID;
disburse.Add(d);
context.Disbursements.Add(d);
}
var requestids = requestdetaillist.Select(x => x.RequestID).ToList().Distinct();
foreach (var q in requestids)
{
Request r = context.Requests.Where(x => x.RequestID == q).FirstOrDefault();
r.Status = "Processed";
}
context.SaveChanges();
return(disburse);
}
}
// Use this when clerk clicks on add item button
public static void CreateVoucherDetail(string itemCode, int adjustedQty, string remarks, int employeeId)
{
using (StationeryStoreEntities _entities = new StationeryStoreEntities()) // need using... as cannot create a _entities reference in static class directly
{
var chosenItem = _entities.CatalogueInventory.Where(c => c.ItemID == itemCode).Single();
var chosenItemUnitCost = chosenItem.UnitCost;
VoucherDetail voucherDetail = new VoucherDetail
{
ItemID = itemCode,
AdjustedQty = adjustedQty,
AdjustedAmt = adjustedQty * chosenItemUnitCost,
VoucherID = null,
Remarks = remarks,
EmployeeID = employeeId
};
_entities.VoucherDetail.Add(voucherDetail);
_entities.SaveChanges();
// for an employee with unsubmitted voucher details,
// can store and retrieve them later using employee id
// once voucher raised, set their voucher id column to the voucher id that
// was newly generated
}
}
public List <EmployeeModel> ListEmployeeByDepartmentID2(int DepID)
{
StationeryStoreEntities entities = new StationeryStoreEntities();
List <EmployeeModel> employees = new List <EmployeeModel>();
try
{
employees = entities.Employees.Where(a => a.DepartmentID == DepID)
.Select <Employee, EmployeeModel>
(c => new EmployeeModel()
{
EmployeeID = c.EmployeeID,
Name = c.Name,
Address = c.Address,
Email = c.Email,
Phone = c.Phone,
DepartmentID = c.DepartmentID
}).ToList <EmployeeModel>();
}
catch (NullReferenceException)
{
return(null);
}
catch (Exception e)
{
return(null);
}
return(employees);
}
public static List <CatalogueInventoryViewModel> ListCatalogue()
{
using (StationeryStoreEntities _entities = new StationeryStoreEntities())
{
var catalogueList = _entities.CatalogueInventory
.Select(c => new CatalogueInventoryViewModel
{
ItemID = c.ItemID,
CategoryDescription = c.Category.Category_Description,
ItemDescription = c.Item_Description,
ReorderLevel = c.ReorderLevel,
ReorderQty = c.ReorderQty,
UnitOfMeasure = c.UnitOfMeasure,
UnitCost = c.UnitCost,
ActualQty = c.ActualQty
}).ToList();
for (int i = 0; i < catalogueList.Count; i++)
{
catalogueList.ElementAt(i).SerialNo = i + 1;
}
return(catalogueList);
}
}
//2. Add Item to not submitted cart
public static void AddRequestDetail(string id, int empid, int qty) //change to catalogue-inventory
{
using (StationeryStoreEntities context = new StationeryStoreEntities())
{
bool flag = false;
List <RequestDetail> reqList = context.RequestDetail.Where(x => x.EmployeeID == empid && x.RequestID == null).ToList();
foreach (RequestDetail r in reqList)
{
if (r.CatalogueInventory.ItemID == id)
{
r.Qty += qty;
flag = true;
context.SaveChanges();
}
}
if (flag == false)
{
RequestDetail obj = new RequestDetail();
obj.ItemID = id;
obj.Qty = qty;
obj.RequestID = null;
obj.EmployeeID = empid;
context.RequestDetail.Add(obj); //save change to request detail table
context.SaveChanges();
}
};
}
public static List <CatalogueInventory> ViewAll()
{
using (StationeryStoreEntities context = new StationeryStoreEntities())
{
List <CatalogueInventory> list = context.CatalogueInventory.ToList();
return(list);
}
}
//11.Search Catalogue Inventory
public static List <CatalogueInventory> SearchCatalogue(string name)
{
using (StationeryStoreEntities context = new StationeryStoreEntities())
{
List <CatalogueInventory> list = context.CatalogueInventory.Where(x => x.Item_Description.Contains(name)).ToList();
return(list);
}
}
public static List <String> ListAllItem()
{
using (StationeryStoreEntities context = new StationeryStoreEntities())
{
return(context.CatalogueInventory.Select <CatalogueInventory, String>
(c => c.ItemID).ToList <String>());
}
}
public static void DeleteSupplier(int SupplierID)
{
using (StationeryStoreEntities context = new StationeryStoreEntities())
{
Supplier s = context.Supplier.Where(p => p.SupplierID == SupplierID).First <Supplier>();
context.Supplier.Remove(s);
context.SaveChanges();
}
}
//5. View name for Request--tested
public static string ViewEmpName(int id)
{
using (StationeryStoreEntities context = new StationeryStoreEntities())
{
string name;
Request r = context.Request.Where(x => x.RequestID == id).First(); //View List of RequestDetail
return(name = r.Employee.Name);
};
}
{//Hardcoding RequestID Not submitted- 9010 (line 22,44)
//Hardcoding RequestStatus - Pending (line 82)
//User Functions
//1.Update qty for RequestDetail --tested
public static void UpdateRequestDetail(int id, int qty)
{
using (StationeryStoreEntities context = new StationeryStoreEntities())
{
var q = context.RequestDetail.Where(x => x.RequestDetailID == id).First();
q.Qty = qty; //save change to request detail table
context.SaveChanges();
};
}
//3. Delete item from not submitted cart --tested
public static void DeleteRequestDetail(int id)
{
using (StationeryStoreEntities context = new StationeryStoreEntities())
{
var q = context.RequestDetail.Where(x => x.RequestDetailID == id).First();
context.RequestDetail.Remove(q); //save change to request detail table
context.SaveChanges();
};
}
public static void UpdateOldURinEmptable(int EmpID)
{
using (StationeryStoreEntities context = new StationeryStoreEntities())
{
Employee s = context.Employee.Where(p => p.EmployeeID == EmpID).First <Employee>();
s.isUserRep = 0;
context.SaveChanges();
}
}
public static void UpdateDelegation(int DelegationID, DateTime?end)
{
using (StationeryStoreEntities context = new StationeryStoreEntities())
{
Delegation s = context.Delegation.Where(p => p.DelegationID == DelegationID).First <Delegation>();
s.EndDate = end;
context.SaveChanges();
}
}