public static List <Supplier> GetSuppliers()
{
using (var ctx = new UniDBContext())
{
return(ctx.Suppliers.OrderBy(s => s.Priority).ToList());
}
}
public void SaveJobData()
{
if (txtStaffName.Text != "" &&
txtStaffRole.Text != "")
{
Staff NewStaff = new Staff();
NewStaff.StaffName = txtStaffName.Text.Trim();
NewStaff.StaffRole = txtStaffRole.Text.Trim();
NewStaff.JobDate = mtxtJobDate.Text.Trim();
using (var context = new UniDBContext())
{
var query = from b in context.ClientCompanys
orderby b.ClientID
select b;
query.ToList()[jobIndex].Jobs.ToList()[staffIndex].AssignedStaff.Add(NewStaff);
context.SaveChanges();
}
MessageBox.Show("Submitted Successfully");
}
else
{
MessageBox.Show("please enter all data!");
}
}
public static List <Adjustment> GetAllPendingAdjustments(int userId)
{
using (var ctx = new UniDBContext())
{
User user = ctx.Users.
Where(u => u.UserId == userId && (u.Rank == (int)UserRank.Manager || u.Rank == (int)UserRank.Supervisor))
.SingleOrDefault();
if (user != null)
{
IQueryable <Adjustment> adjustQuery = null;
List <Adjustment> adjustments = null;
switch (user.Rank)
{
case (int)UserRank.Manager:
adjustQuery = ctx.Adjustments.Include("Requestor")
.Where(ad => ad.Total >= 250 && ad.Status == (int)AdjustmentStatus.Raised);
break;
case (int)UserRank.Supervisor:
adjustQuery = ctx.Adjustments.Include("Requestor")
.Where(ad => ad.Total > 0 && ad.Total < 250 && ad.Status == (int)AdjustmentStatus.Raised);
break;
}
adjustments = adjustQuery.OrderByDescending(ad => ad.AdjustmentId).ToList();
return(adjustments);
}
return(null);
}
}
public static void InsertRequest(ICollection <Item> items, User u)
{
try
{
using (var ctx = new UniDBContext())
{
Request req = new Request();
req.Requestor = u;
req.Department = u.Department;
req.DisbursementStatus = (int)RequestRetrievalStatus.NotPrepared;
ICollection <RequestDetail> details = new List <RequestDetail>();
req.Date = DateTime.Now;
req.Status = (int)RequestStatus.Requested;
foreach (var i in items)
{
RequestDetail detail = new RequestDetail();
ctx.Items.Attach(i);
detail.Item = i;
detail.Quantity = i.Quantity;
details.Add(detail);
}
ctx.Users.Attach(u);
ctx.Departments.Attach(req.Department);
req.RequestDetails = details;
ctx.RequestDetails.AddRange(details);
ctx.Requests.Add(req);
ctx.SaveChanges();
}
}
catch (Exception e)
{
throw new Exception("Internal Server Error");
}
}
public void SaveClick()
{
Job NewJob = new Job();
NewJob.MachineDescription = jobViewerInstance.GetTxtMachineDescription();
NewJob.FaultDescription = jobViewerInstance.GetTxtFaultDescription();
NewJob.JobUrgency = Convert.ToInt32(jobViewerInstance.GetTxtJobUrgency());
NewJob.MachineComplexity = Convert.ToInt32(jobViewerInstance.GetTxtMachineComplexity());
Address NewFactoryLocation = new Address();
NewFactoryLocation.HouseNumber = jobViewerInstance.GetTxtHouseNumber();
NewFactoryLocation.Street = jobViewerInstance.GetTxtStreet();
NewFactoryLocation.Town = jobViewerInstance.GetTxtTown();
NewFactoryLocation.PostCode = jobViewerInstance.GetTxtPostcode();
NewJob.FactoryLocation.Add(NewFactoryLocation);
using (var context = new UniDBContext())
{
var query = from b in context.ClientCompanys
orderby b.ClientID
select b;
NewJob.FactoryLocation.Add(NewFactoryLocation);
query.ToList()[jobsIndex].Jobs.Add(NewJob);
context.SaveChanges();
}
}
//public static List<Request> GetUnFulfilledRequests()
//{
// using (var ctx = new UniDBContext())
// {
// List<Request> requests = ctx.Requests.Include("Department")
// .Where(r => r.Status == (int)RequestStatus.Approved || r.Status == (int)RequestStatus.PartiallyDelivered)
// .ToList();
// return requests;
// }
//}
//key for item description,
//another dict key for month and value for no of items requested
public static Dictionary <string, Dictionary <int, int> > GetRequestedItemNoByCategory(string category)
{
using (var ctx = new UniDBContext())
{
//description key ,
Dictionary <string, List <RequestDetail> > rDDict = ctx.RequestDetails.Include("Request").Include("Item")
.Where(rd => (rd.Request.Status == (int)RequestStatus.Approved || rd.Request.Status == (int)RequestStatus.PartiallyDelivered ||
rd.Request.Status == (int)RequestStatus.FullyDelivered) && rd.Request.Date.Year == DateTime.Now.Year && rd.Item.Category == category)
.GroupBy(rd => rd.Item.Description)
.ToDictionary(cl => cl.Key, cl => cl.ToList());
Dictionary <string, Dictionary <int, int> > itemInfo = new Dictionary <string, Dictionary <int, int> >();
foreach (KeyValuePair <string, List <RequestDetail> > kV in rDDict)
{
List <int> rdIds = kV.Value.Select(rd => rd.RequestDetailId).ToList();
Dictionary <int, int> monthAndItemAmount = ctx.RequestDetails.Include("Request")
.Where(rd => rdIds.Contains(rd.RequestDetailId))
.GroupBy(rd => rd.Request.Date.Month)
.ToDictionary(cl => cl.Key, cl => cl.Sum(rd => rd.Quantity));
itemInfo.Add(kV.Key, monthAndItemAmount);
}
return(itemInfo);
}
}
public static User GetUserProfile(User user)
{
using (var ctx = new UniDBContext())
{
User loggedInUser = ctx.Users.Include("Department").Where(u => u.UserId == user.UserId && u.Department.DepartmentId == user.Department.DepartmentId).SingleOrDefault();
return(loggedInUser);
}
}
public static List <Department> GetAllDepartments()
{
using (var ctx = new UniDBContext())
{
List <Department> departments = ctx.Departments.ToList();
return(departments);
}
}
public async static Task <List <PickUpPoint> > GetAllPickupPoints()
{
using (var ctx = new UniDBContext())
{
List <PickUpPoint> points = ctx.PickUpPoints.ToList();
return(points);
}
}
public static List <Item> GetAllItems()
{
using (var ctx = new UniDBContext())
{
List <Item> items = ctx.Items.OrderBy(i => i.Quantity).ToList();
return(items);
}
}
public static List <Item> GetAllItems()
{
using (var ctx = new UniDBContext())
{
List <Item> items = ctx.Items.ToList();
return(items);
}
}
public static List <string> GetCategoryNames()
{
using (var ctx = new UniDBContext())
{
List <string> categories = ctx.Items.Select(i => i.Category).Distinct().ToList();
return(categories);
}
}
public static List <Request> GetRequestDetailByDepartment()
{
using (var ctx = new UniDBContext())
{
List <Request> requests = ctx.Requests.Include("RequestDetails").Include("RequestDetails.Item").Include("Department").Where(r => r.Status == (int)RequestStatus.Approved).ToList();
return(requests);
}
}
public static List <object> GetOrderedItemsByMonth(int month, int supplierId)
{
try
{
using (var ctx = new UniDBContext())
{
List <Order> orders = ctx.Orders.Include("Supplier").Include("OrderDetails").Include("OrderDetails.Item")
.Where(o => o.Date.Year == DateTime.Now.Year && o.Date.Month == month && o.Status == (int)OrderStatus.Delivered)
.ToList();
if (supplierId != 0)
{
orders = orders.Where(o => o.Supplier.SupplierId == supplierId).ToList();
}
Dictionary <int, int> itemInfo = new Dictionary <int, int>();
foreach (var o in orders)
{
foreach (var oD in o.OrderDetails)
{
var requiredAmount = oD.DeliveredQuantity;
if (itemInfo.ContainsKey(oD.Item.ItemId))
{
requiredAmount += itemInfo[oD.Item.ItemId];
itemInfo[oD.Item.ItemId] = requiredAmount;
}
else
{
itemInfo.Add(oD.Item.ItemId, requiredAmount);
}
}
}
List <int> itemIds = itemInfo.Keys.ToList();
Dictionary <int, Item> stockDict = ctx.Items.Where(i => itemIds.Contains(i.ItemId)).ToDictionary(i => i.ItemId);
List <object> orderlist = new List <object>();
foreach (var itemId in itemIds)
{
int requestedNumber = itemInfo[itemId];
Item i = stockDict[itemId];
object orderitem = new
{
Description = i.Description,
Qty = requestedNumber,
ItemId = i.ItemId,
Price = i.Price,
};
orderlist.Add(orderitem);
}
return(orderlist);
}
}
catch (Exception e)
{
return(null);
}
}
public async static Task <List <User> > GetAllEmployeesFromDepartment(int departmentId)
{
using (var ctx = new UniDBContext())
{
List <User> users = await ctx.Users.Where(u => u.Department.DepartmentId == departmentId).ToListAsync();
return(users);
}
}
public async static Task <User> GetDepartmentHeadByDepartmentId(int departmentId)
{
using (var ctx = new UniDBContext())
{
User deptHead = ctx.Users.Include("Department").Where(u => u.Department.DepartmentId == departmentId && u.Rank == (int)UserRank.Head)
.SingleOrDefault();
return(deptHead);
}
}
public static Dictionary <int, DisbursementDetail> GetDisbursementDetailDictByDisbursementId(int disbursementId)
{
using (var ctx = new UniDBContext())
{
Dictionary <int, DisbursementDetail> dDict = ctx.DisbursementDetails.Include("Disbursement").Where(dd => dd.Disbursement.DisbursementId == disbursementId)
.ToDictionary(dd => dd.DisbursementDetailId);
return(dDict);
}
}
public static Adjustment GetAdjustmentDetailById(int adjustmentId)
{
using (var ctx = new UniDBContext())
{
Adjustment adj = ctx.Adjustments.Include("Requestor").Include("AdjustmentDetails").Include("AdjustmentDetails.Item")
.Where(ad => ad.AdjustmentId == adjustmentId).SingleOrDefault();
return(adj);
}
}
void PopulateDataGridView()
{
ClientGrid.AutoGenerateColumns = false;
using (var context = new UniDBContext())
{
ClientGrid.DataSource = context.ClientCompanys.ToList <ClientCompany>();
FactoryGrid.DataSource = context.Addresses.ToList <Address>();
}
}
public async static Task <PickUpPoint> GetPickupPointByDepartment(int departmentId)
{
using (var ctx = new UniDBContext())
{
PickUpPoint point = ctx.Departments.Include("PickupPoint").Where(d => d.DepartmentId == departmentId).Select(d => d.PickupPoint).SingleOrDefault();
return(point);
}
}
public static List <RetrievalItem> GetRequestedItemsByMonth(int deptId, int month)
{
try
{
using (var ctx = new UniDBContext())
{
List <Request> requestList = ctx.Requests.Include("Department").Include("RequestDetails").Include("RequestDetails.Item")
.Where(r => r.Date.Year == DateTime.Now.Year && r.Date.Month == month && r.Status != (int)RequestStatus.Requested && r.Status != (int)RequestStatus.Rejected && r.Status != (int)RequestStatus.Cancelled)
.ToList();
if (deptId != 0)
{
requestList = requestList.Where(r => r.Department.DepartmentId == deptId).ToList();
}
Dictionary <int, int> itemInfo = new Dictionary <int, int>();
foreach (var r in requestList)
{
foreach (var rD in r.RequestDetails)
{
var requiredAmount = rD.Quantity;
if (itemInfo.ContainsKey(rD.Item.ItemId))
{
requiredAmount += itemInfo[rD.Item.ItemId];
itemInfo[rD.Item.ItemId] = requiredAmount;
}
else
{
itemInfo.Add(rD.Item.ItemId, requiredAmount);
}
}
}
List <int> itemIds = itemInfo.Keys.ToList();
Dictionary <int, Item> stockDict = ctx.Items.Where(i => itemIds.Contains(i.ItemId)).ToDictionary(i => i.ItemId);
List <RetrievalItem> retrievalItems = new List <RetrievalItem>();
foreach (var itemId in itemIds)
{
int requestedNumber = itemInfo[itemId];
Item i = stockDict[itemId];
RetrievalItem retrieval = new RetrievalItem()
{
AllocatedQuantity = requestedNumber,
Description = i.Description,
ItemId = i.ItemId,
};
retrievalItems.Add(retrieval);
}
return(retrievalItems);
}
}
catch (Exception e)
{
return(null);
}
}
public static Item getItemById(int itemId)
{
Item item = null;
using (var ctx = new UniDBContext())
{
item = ctx.Items.Where(i => i.ItemId == itemId).FirstOrDefault();
}
return(item);
}
public static List <Order> GetAllOrdersByUserId(int userId)
{
using (var ctx = new UniDBContext())
{
List <Order> orders = ctx.Orders.Include("Requestor").Include("Supplier")
.Where(o => o.Requestor.UserId == userId)
.OrderByDescending(o => o.OrderId).ToList();
return(orders);
}
}
public static List <RequestDetail> getRequestDetailsByRequestId(int id, int departmentId)
{
List <RequestDetail> details = null;
using (var ctx = new UniDBContext())
{
details = ctx.RequestDetails.Include("Item").Where(d => d.Request.RequestId == id && d.Request.Department.DepartmentId == departmentId).ToList();
}
return(details);
}
public static Disbursement GetDisbursementDetailById(int disbursementId)
{
using (var ctx = new UniDBContext())
{
Disbursement dis = ctx.Disbursements.Include("Department").Include("DisbursementDetails").Include("DisbursementDetails.Item")
.Include("ApprovedBy")
.Where(d => d.DisbursementId == disbursementId).SingleOrDefault();
return(dis);
}
}
public async static Task <List <int> > GetDepartmentIdWithPreparedDisbursement()
{
using (var ctx = new UniDBContext())
{
List <int> deptIds = ctx.Disbursements.Include("Department").Where(dis => dis.Status == (int)DisbursementStatus.Prepared)
.Select(dis => dis.Department.DepartmentId).Distinct().ToList();
return(deptIds);
}
}
public static Disbursement GetDeliveredDisbursement(int id)
{
using (var ctx = new UniDBContext())
{
Disbursement disbursement = ctx.Disbursements.Include("Department").Include("Department.Representative").Include("DisbursementDetails").Include("DisbursementDetails.Item")
.Include("Request").Include("Department.PickupPoint")
.Where(d => d.Status == (int)DisbursementStatus.Delivered && d.DisbursementId == id).SingleOrDefault();
return(disbursement);
}
}
public async static Task <User> GetTemporaryHeadByDepartment(int departmentId)
{
using (var ctx = new UniDBContext())
{
User u = ctx.Users.Include("Department").Where(user => user.Department.DepartmentId == departmentId &&
user.Rank == (int)UserRank.TemporaryHead).SingleOrDefault();
return(u);
}
}
public static List <Item> GetCurrentStockInfoByIds(List <int> itemIds)
{
using (var ctx = new UniDBContext())
{
List <Item> itemStockInfo = ctx.Items.Where(i => itemIds.Contains(i.ItemId))
.ToList();
return(itemStockInfo);
}
}
public async static Task <User> GetRepresentative(int departmentId)
{
using (var ctx = new UniDBContext())
{
Department department = await ctx.Departments.Include("Representative").Where(d => d.DepartmentId == departmentId).FirstOrDefaultAsync <Department>();
User u = department.Representative;
return(u);
}
}
