public List <Disbursement> GetDisbursements(string deptId)
{
using (db = new LogicEntities())
{
return(db.Disbursement.Where(x => x.DeptId == deptId).ToList());
}
}
public ActionResult AppointStaffApi(string staffname, string getStartDate, string getEndDate)
{
var result = db.DepartmentStaff.SingleOrDefault(b => b.StaffName == staffname);
var resultStaffId = result.StaffId;
string resultDeptId = result.DeptId;
DateTime fromDate = DateTime.ParseExact(getStartDate, "MM/dd/yyyy HH:mm:ss",
CultureInfo.InvariantCulture);
DateTime toDate = DateTime.ParseExact(getEndDate, "MM/dd/yyyy HH:mm:ss",
CultureInfo.InvariantCulture);
using (LogicEntities db = new LogicEntities())
{
var authorization = db.Set <logicProject.Models.EF.Authorization>();
authorization.Add(new logicProject.Models.EF.Authorization {
DeptId = resultDeptId, StaffId = resultStaffId, StartDate = fromDate, EndDate = toDate
});
db.SaveChanges();
}
List <string> email = new List <string>();
email.Add(result.StaffEmail);
string message = Utility.EmailBody.HeadBody + getStartDate + " and will expired on " + getEndDate;
Utility.EmailService.SendEmail(email, Utility.EmailBody.HeadSubject, message);
return(Json(new { isok = true, message = "Authorization Successful" }));
}
public static void AddRequest(string products, string qty, int id)
{
string[] productArr = products.Split(',').ToArray();
int[] qtyArr = qty.Split(',').Select(n => Convert.ToInt32(n)).ToArray();
DateTime reqTime = DateTime.Now;
using (LogicEntities db = new LogicEntities())
{
string DeptId = db.DepartmentStaff.Where(x => x.StaffId == id).Select(x => x.DeptId).FirstOrDefault();
string DeptName = db.Department.Where(x => x.DeptId == DeptId).Select(x => x.DeptName).FirstOrDefault();
Request request = new Request();
request.StaffId = id;
request.DeptId = DeptId;
//request.RequestFormId = DeptId + "/";
//request.FavRequest = (save == "yes") ? true : false;
request.ReqDate = reqTime;
request.Status = status.Pending.ToString();
db.Request.Add(request);
db.SaveChanges();
for (int i = 0; i < productArr.Length; i++)
{
string des = productArr[i];
string productId = db.Product.Where(x => x.Description == des).Select(x => x.ProductId).SingleOrDefault();
RequestDetail rd = new RequestDetail();
rd.ProductId = productId;
rd.ReqQty = qtyArr[i];
rd.RequestId = request.RequestId;
db.RequestDetail.Add(rd);
}
db.SaveChanges();
Request r = db.Request.Find(request.RequestId);
r.RequestFormId = DeptId + "/" + "R0" + r.RequestId + "-" + DeptName;
db.SaveChanges();
}
}
public CollectionPoint GetCollectionPoint(int id)
{
using (db = new LogicEntities())
{
return(db.CollectionPoint.Find(id));
}
}
public List <Department> GetDepartments()
{
using (db = new LogicEntities())
{
return(db.Department.ToList());
}
}
public int GetItemMaxQty(string id)
{
using (db = new LogicEntities())
{
return(db.Product.Find(id).Qty);
}
}
public DepartmentStaff getDeptRep(string deptId)
{
using (db = new LogicEntities())
{
return(db.DepartmentStaff.FirstOrDefault(x => x.DeptId == deptId && x.StaffType == "Rep"));
}
}
public int SaveNewPO(string supplierId, string[] productIds, string[] requiredQty, int id)
{
List <PurchaseOrderDetail> purchaseOrderDetails = new List <PurchaseOrderDetail>();
PurchaseOrder po = new PurchaseOrder
{
OrderDate = DateTime.Now,
ApprovedDate = null,
StaffId = id, //To get this information from the session object
Status = "Pending",
SupplierId = supplierId,
};
for (int x = 0; x < productIds.Length; x++)
{
PurchaseOrderDetail pod = new PurchaseOrderDetail
{
OrderId = po.OrderId,
ProductId = productIds[x],
ReqQty = Convert.ToInt32(requiredQty[x]),
};
purchaseOrderDetails.Add(pod);
}
using (db = new LogicEntities())
{
db.PurchaseOrder.Add(po);
db.PurchaseOrderDetail.AddRange(purchaseOrderDetails);
db.SaveChanges();
}
return(po.OrderId);
}
public ActionResult CreateRetrievalForm()
{
DateTime cutoff = DateTime.Now.Date;
List <RetrievalViewModel> retrievalList = new List <RetrievalViewModel>();
using (db = new LogicEntities())
{
var resultSet = from rd in db.RequestDetail
from r in db.Request
where (r.Status.Contains("New") || r.Status.Contains("Outstanding")) &&
r.RequestId == rd.RequestId && r.ReqDate < cutoff //Ensures only item requests before the day of order are withdrawn
group rd by rd.Product into g
select new RetrievalViewModel
{
Product = g.Key,
Quantity = g.Sum(x => x.ReqQty)
};
retrievalList = resultSet.ToList();
}
foreach (RetrievalViewModel rvm in retrievalList)
{
rvm.MaxQuantity = productDAO.GetItemMaxQty(rvm.Product.ProductId);
}
ViewData["Retrieval"] = retrievalList;
return(View());
}
public DepartmentStaff GetStaffById(int id)
{
using (db = new LogicEntities())
{
return(db.DepartmentStaff.Find(id));
}
}
public StoreStaff GetStoreStaffbyPONumber(int id)
{
using (db = new LogicEntities())
{
return(db.StoreStaff.FirstOrDefault(x => x.StaffId == id));
}
}
public ActionResult addnew(AdjustmentDetail form_detail_obj)
{
if (string.IsNullOrEmpty(form_detail_obj.ProductId))
{
return(RedirectToAction("addcart"));
}
using (LogicEntities db = new LogicEntities())
{
List <AdjustmentDetail> temp = db.AdjustmentDetail.ToList();
int form_details_num = (db.AdjustmentDetail.AsEnumerable().ToList().Count == 0) ? 1 : (db.AdjustmentDetail.AsEnumerable().Max(p => p.AdjustmentDetailId)) + 1;
AdjustmentDetail old_rec_to_update = (from f in db.AdjustmentDetail
where f.ProductId == form_detail_obj.ProductId && f.AdjustmentId == form_detail_obj.AdjustmentId
select f).FirstOrDefault();
if (old_rec_to_update != null)
{
old_rec_to_update.Qty += form_detail_obj.Qty;
}
else
{
//Debug.WriteLine("____________" + form_details_num + "##" + form_detail_obj.AdjustmentId);
db.AdjustmentDetail.Add(new AdjustmentDetail {
AdjustmentDetailId = form_details_num, AdjustmentId = form_detail_obj.AdjustmentId, ProductId = form_detail_obj.ProductId, Qty = Convert.ToInt32(form_detail_obj.Qty), UnitPrice = form_detail_obj.UnitPrice, TotalPrice = form_detail_obj.TotalPrice, reason = form_detail_obj.reason
});
}
db.SaveChanges();
}
return(RedirectToAction("addcart"));
}
public StoreStaff GetStoreStaffbyRole(string role)
{
using (db = new LogicEntities())
{
return(db.StoreStaff.FirstOrDefault(x => x.StaffType == role));
}
}
public void WithdrawManyItems(string[] itemCodes, string[] retrievedQtys)
{
List <StockTransaction> stockTransactions = new List <StockTransaction>();
using (db = new LogicEntities())
{
for (int x = 0; x < itemCodes.Length; x++)
{
Product p = db.Product.Find(itemCodes[x]);
if (-1 * Convert.ToInt32(retrievedQtys[x]) > p.Qty)
{
//Throw an error //Not used due to modification made to view validation
}
else
{
StockTransaction stockTransaction = new StockTransaction
{
ProductId = itemCodes[x],
Qty = -1 * Convert.ToInt32(retrievedQtys[x]),
TranDate = DateTime.Now,
Remarks = "Inventory Withdrawal, Date: " + DateTime.Now.Date.ToString("d"),
TotalBalance = p.Qty - Convert.ToInt32(retrievedQtys[x])
};
stockTransactions.Add(stockTransaction);
}
}
db.StockTransaction.AddRange(stockTransactions);
db.SaveChanges();
}
}
public void SaveDisbursement(int id, string status, string[] receivedQtys, string[] remarks, int storestaffId)
{
using (db = new LogicEntities())
{
var result = from d in db.Disbursement
where d.DisId == id
select d;
Disbursement dis = result.First();
dis.Status = status;
dis.StoreStaffId = storestaffId;
var resultSet = from dd in db.DisbursementDetail
where dd.DisId == id
select dd;
List <DisbursementDetail> disbursementDetails = resultSet.ToList();
int x = 0;
foreach (DisbursementDetail disd in disbursementDetails)
{
disd.ReceivedQty = Convert.ToInt32(receivedQtys[x]);
disd.Remarks = remarks[x];
x++;
}
dis.DisbursementDetails = disbursementDetails;
db.Entry(dis).State = EntityState.Modified;
db.SaveChanges();
}
}
public static void AddRequest(string products, string qty, int id, string nameId)
{
string[] productArr = products.Split(',').ToArray();
int[] qtyArr = qty.Split(',').Select(n => Convert.ToInt32(n)).ToArray();
using (LogicEntities db = new LogicEntities())
{
string DeptId = db.DepartmentStaff.Where(x => x.StaffId == id).Select(x => x.DeptId).FirstOrDefault();
string DeptName = db.Department.Where(x => x.DeptId == DeptId).Select(x => x.DeptName).FirstOrDefault();
FavOrder request = new FavOrder();
request.StaffId = id;
request.DeptId = DeptId;
request.FavOrderName = nameId;
db.FavOrder.Add(request);
db.SaveChanges();
for (int i = 0; i < productArr.Length; i++)
{
string des = productArr[i];
string productId = db.Product.Where(x => x.Description == des).Select(x => x.ProductId).FirstOrDefault();
FavOrderDetails rd = new FavOrderDetails();
rd.ProductId = productId;
rd.FavQty = qtyArr[i];
rd.FavOrderId = request.FavOrderId;
db.FavOrderDetails.Add(rd);
}
db.SaveChanges();
FavOrder r = db.FavOrder.Find(request.FavOrderId);
r.FavFormId = DeptId + "/" + "F0" + r.FavOrderId + "-" + DeptName;
db.SaveChanges();
}
}
public ActionResult AppointStaff(string staffname, DateTime getStartDate, DateTime getEndDate)
{
var result = db.DepartmentStaff.SingleOrDefault(b => b.StaffName == staffname);
var resultStaffId = result.StaffId;
string resultDeptId = result.DeptId;
var list = db.Authorization.Where(x => x.DeptId == resultDeptId && x.EndDate >= getStartDate && x.StartDate <= getEndDate).ToList();
if (list.Count > 0)
{
return(Json(new { isok = false, message = "Authorization Unsuccessful" }));
}
using (LogicEntities db = new LogicEntities())
{
var authorization = db.Set <logicProject.Models.EF.Authorization>();
authorization.Add(new logicProject.Models.EF.Authorization {
DeptId = resultDeptId, StaffId = resultStaffId, StartDate = getStartDate, EndDate = getEndDate
});
db.SaveChanges();
}
List <string> email = new List <string>();
email.Add(result.StaffEmail);
string message = Utility.EmailBody.HeadBody + getStartDate.ToString("dddd, dd MMMM yyyy") + " and will expired on " + getEndDate.ToString("dddd, dd MMMM yyyy");
Utility.EmailService.SendEmail(email, Utility.EmailBody.HeadSubject, message);
return(Json(new { isok = true, message = "Authorization Successful", redirect = "/DeptHead/AuthorizeStaff" }));
}
public ActionResult SetDisbursementList(int id, string status, string deptId)
{
string[] productIds = Request.Form.GetValues("productId");
string[] requestedQtys = Request.Form.GetValues("requestedQty");
string[] receivedQtys = Request.Form.GetValues("receivedQty");
string[] remarks = Request.Form.GetValues("remarks");
int storeStaffId = Convert.ToInt32(Request.Form.Get("creatorId"));
//Update the item quantities && Set Disburesment status to delivered
disbursementDAO.SaveDisbursement(id, status, receivedQtys, remarks, storeStaffId);
DepartmentStaff rep = departmentStaffDAO.getDeptRep(deptId);
//Set all the orders status to Delivered. Not DAO-ed
using (db = new LogicEntities())
{
var resultSet = from r in db.Request
where r.DeptId == deptId
select r;
List <Request> requests = resultSet.ToList();
foreach (Request req in requests)
{
req.Status = "Delivered";
db.Entry(req).State = EntityState.Modified;
db.SaveChanges();
}
//if Qty received < Qty Requested, Generate outstanding order in requests
Request outstanding = new Request
{
DeptId = deptId,
ReqDate = DateTime.Now,
Remark = "Outstanding Order, " + DateTime.Now.Date.ToString("d"),
Status = "Outstanding",
StaffId = rep.StaffId
};
List <RequestDetail> outstandingDetails = new List <RequestDetail>();
for (int x = 0; x < requestedQtys.Length; x++)
{
if (requestedQtys[x] != receivedQtys[x])
{
RequestDetail rd = new RequestDetail
{
ProductId = productIds[x],
RequestId = outstanding.RequestId,
ReqQty = Convert.ToInt32(requestedQtys[x]) - Convert.ToInt32(receivedQtys[x]),
ReceivedQty = 0, //Shouldn't be needed...
};
outstandingDetails.Add(rd);
}
}
if (outstandingDetails.Count != 0)
{
db.Request.Add(outstanding);
db.RequestDetail.AddRange(outstandingDetails);
db.SaveChanges();
}
}
return(RedirectToAction("FindDisbursementList"));
}
public Product FindProductById(string id)
{
using (db = new LogicEntities())
{
Product p = db.Product.Find(id);
return(p);
}
}
public void CreateDisbursements(List <Disbursement> d, List <DisbursementDetail> dd)
{
using (db = new LogicEntities())
{
db.Disbursement.AddRange(d);
db.DisbursementDetail.AddRange(dd);
db.SaveChanges();
}
}
public List <Disbursement> GetDisbursements(DateTime startDate, DateTime endDate)
{
using (db = new LogicEntities())
{
var resultSet = from d in db.Disbursement
where d.DisDate >= startDate && d.DisDate <= endDate
select d;
return(resultSet.ToList());
}
}
public void UpdateItemQty(string productId, int qty)
{
using (db = new LogicEntities())
{
Product p = db.Product.Find(productId);
p.Qty = p.Qty + qty;
db.Entry(p).State = EntityState.Modified;
db.SaveChanges();
}
}
//public ActionResult OutstandingDisbursementList()
//{
// DateTime cutoff = DateTime.Now.Date;
// List<Department> departments = new List<Department>();
// departments = departmentDAO.GetDepartmentsWithOutstandingOrders(cutoff);
// ViewData["departments"] = departments;
// return View();
//}
public ActionResult GenerateDisbursementLists()
{
DateTime cutoff = DateTime.Now.Date; //Ensures that orders only up to yesterday 2359 is taken into consideration
List <Department> departments = departmentDAO.GetDepartmentsWithOutstandingOrders(cutoff);
List <DisbursementDetailViewModel> consolidatedOrders = new List <DisbursementDetailViewModel>();
foreach (Department d in departments)
{
DepartmentStaff rep = departmentStaffDAO.getDeptRep(d.DeptId);
using (db = new LogicEntities())
{
var resultSet = from rd in db.RequestDetail
from r in db.Request
where (r.Status.Contains("New") || r.Status.Contains("Outstanding")) &&
r.RequestId == rd.RequestId && r.DeptId == d.DeptId &&
r.ReqDate < cutoff
group rd by new { rd.ProductId, r.DeptId } into g
select new DisbursementDetailViewModel //Note to self: Groupby only works with enums or primitive data types
{
ProductId = g.Key.ProductId,
DepartmentId = g.Key.DeptId,
Quantity = g.Sum(x => x.ReqQty)
};
consolidatedOrders = resultSet.ToList();
Disbursement dis = new Disbursement
{
DisDate = DateTime.Now,
Status = "Pending",
DeptId = d.DeptId,
StoreStaffId = null,
ReceiveStaffId = rep.StaffId,
CollectionPointId = d.CollectionPt
};
List <DisbursementDetail> disbursementdetails = new List <DisbursementDetail>();
foreach (DisbursementDetailViewModel p in consolidatedOrders)
{
DisbursementDetail dd = new DisbursementDetail
{
ProductId = p.ProductId,
RequiredQty = p.Quantity,
ReceivedQty = 0,
DisId = dis.DisId
};
disbursementdetails.Add(dd);
}
db.Disbursement.Add(dis);
db.DisbursementDetail.AddRange(disbursementdetails);
db.SaveChanges();
}
}
return(RedirectToAction("FindDisbursementList"));
}
//Code from Prasanth ends here
//Code from Udaya
public ActionResult ViewInventory()
{
List <Product> items;
using (LogicEntities db = new LogicEntities())
{
items = db.Product.ToList();
}
ViewData["items"] = items;
return(View());
}
public List <Department> GetDepartmentsWithOutstandingOrders(DateTime cutoff)
{
using (db = new LogicEntities())
{
var resultSet = from r in db.Request
where (r.Status.Contains("New") || r.Status.Contains("Outstanding")) &&
r.ReqDate < cutoff
select r.Department;
List <Department> departments = resultSet.Distinct().ToList();
return(departments);
}
}
public ActionResult ViewLowStock()
{
List <Product> items;
using (LogicEntities db = new LogicEntities())
{
items = db.Product.Where(item => item.Qty < item.ReorderLevel).ToList();
db.SaveChanges();
}
ViewData["items"] = items;
return(View());
}
public PurchaseOrder GetPurchaseOrder(int no)
{
PurchaseOrder purchaseOrder = new PurchaseOrder();
using (db = new LogicEntities())
{
var resultsSet = from o in db.PurchaseOrder
where o.OrderId == no
select o;
purchaseOrder = resultsSet.FirstOrDefault();
}
return(purchaseOrder);
}
public List <PurchaseOrder> GetPurchaseOrders(DateTime start, DateTime end)
{
List <PurchaseOrder> purchaseOrders = new List <PurchaseOrder>();
using (db = new LogicEntities())
{
var resultsSet = from o in db.PurchaseOrder
where o.OrderDate >= start && o.OrderDate <= end
select o;
purchaseOrders = resultsSet.ToList();
}
return(purchaseOrders);
}
public ActionResult ViewVoucherList()
{
List <Adjustment> cart_list;
using (LogicEntities db = new LogicEntities())
{
cart_list = db.Database.SqlQuery <Adjustment>("select distinct a.* from Adjustments a join AdjustmentDetails ad on a.AdjustmentId=ad.AdjustmentId where (select sum(x.TotalPrice) from AdjustmentDetails x where x.AdjustmentId=a.AdjustmentId group by x.AdjustmentId) > 250").ToList();
db.SaveChanges();
}
ViewData["cart_list"] = cart_list;
return(View());
}
public List <PurchaseOrder> GetPurchaseOrders(string status)
{
List <PurchaseOrder> purchaseOrders = new List <PurchaseOrder>();
using (db = new LogicEntities())
{
var resultsSet = from o in db.PurchaseOrder
where o.Status.Contains(status)
select o;
purchaseOrders = resultsSet.ToList();
}
return(purchaseOrders);
}
