public static Employee GetEmployeeByEmail(string email)
{
using (StationeryEntities context = new StationeryEntities())
{
return(context.Employees.Where(x => x.Email == email).First());
}
}
public static List <int> GetRequisitionIDListbyDisbID(int disbID)
{
using (StationeryEntities context = new StationeryEntities())
{
return(context.Requisitions.Where(x => x.DisbursementID == disbID).Select(x => x.RequisitionID).ToList());
}
}
public static void AddPurchaseOrder(Dictionary <PurchaseOrder, List <Item_PurchaseOrder> > orderItems)
{
using (TransactionScope ts = new TransactionScope())
{
StationeryEntities entities = new StationeryEntities();
decimal? totAmount = 0;
foreach (var order in orderItems)
{
entities.PurchaseOrders.Add(order.Key);
entities.SaveChanges();
foreach (var items in order.Value)
{
totAmount += items.Amount;
items.PurchaseOrderID = order.Key.PurchaseOrderID;
entities.Item_PurchaseOrder.Add(items);
}
PurchaseOrder porder = entities.PurchaseOrders.Where(x => x.PurchaseOrderID == order.Key.PurchaseOrderID).FirstOrDefault();
porder.TotalAmount = totAmount;
totAmount = 0;
entities.SaveChanges();
}
ts.Complete();
}
}
public static Requisition GetRequisitionByDisbID(int disbId)
{
using (StationeryEntities context = new StationeryEntities())
{
return(context.Requisitions.OrderByDescending(x => x.RequestDate).Where(x => x.DisbursementID == disbId).FirstOrDefault());
}
}
public static int CountPending(string deptCode)
{
using (StationeryEntities context = new StationeryEntities())
{
return(context.Requisitions.Where(x => x.DeptCode.Equals(deptCode) && x.Status.Equals("Pending")).Count());
}
}
public static int?GetReordersForGivenMonth(DateTime startDate, DateTime endDate, string supplier, string cat)
{
using (TransactionScope ts = new TransactionScope())
{
StationeryEntities SE = new StationeryEntities();
var totalR = from ip in SE.Item_PurchaseOrder
from po in SE.PurchaseOrders
from c in SE.Categories
from i in SE.Items
from s in SE.Suppliers
where ip.PurchaseOrderID == po.PurchaseOrderID
where ip.ItemCode == i.ItemCode
where po.OrderDate >= startDate
where po.OrderDate <= endDate
where po.SupplierCode == s.SupplierCode
where i.CategoryID == c.CategoryID
where c.CategoryName == cat
where s.SupplierName == supplier
select ip.OrderQty;
int?returnValue = totalR.Sum();
ts.Complete();
return(returnValue);
}
}
//AUTHOR : KHIN MO MO ZIN
public static int GetDeptRepEmpIDByDeptCode(string depName)
{
using (StationeryEntities smodel = new StationeryEntities())
{
return(smodel.Employees.Where(x => x.Department.DeptName.Equals(depName) && x.Role.Equals("Representative")).Select(x => x.EmpID).First());
}
}
public static void AddNewRequisition(List <RequestedItem> item, DateTime date, string status, int empID, string DeptCode)
{
using (TransactionScope ts = new TransactionScope())
{
StationeryEntities context = new StationeryEntities();
Requisition r = new Requisition();
List <Requisition_Item> list = new List <Requisition_Item>();
//to pass from previous form
r.RequestDate = date;
r.Status = status;
r.RequestedBy = empID;
r.DeptCode = DeptCode;
context.Requisitions.Add(r);
context.SaveChanges();
foreach (RequestedItem i in item)
{
int qty = i.Quantity;
string code = i.Code;
Requisition_Item ri = new Requisition_Item();
ri.RequisitionID = r.RequisitionID;
ri.ItemCode = code;
ri.RequestedQty = qty;
list.Add(ri);
}
context.Requisition_Item.AddRange(list);
context.SaveChanges();
ts.Complete();
}
}
public static List <Requisition> SearchForCollectionList(string deptCode)
{
using (StationeryEntities context = new StationeryEntities())
{
return(context.Requisitions.OrderByDescending(x => x.RequestDate).Where(x => x.DeptCode.Equals(deptCode) && x.Status.Equals("InProgress")).ToList());
}
}
public static List <Retrieval> GetAllRetrievalList()
{
using (StationeryEntities context = new StationeryEntities())
{
return(context.Retrievals.Include("Employee").ToList());
}
}
public static List <Item> GetActiveItemDescriptionList()
{
using (StationeryEntities context = new StationeryEntities())
{
return(context.Items.Where(i => i.ActiveStatus.Equals("Y")).ToList());
}
}
public static int GetRetrievedQtyByRequisitionIdNItemCode(int requisitionId, string itemCode)
{
using (StationeryEntities context = new StationeryEntities())
{
return((int)context.Disbursement_Item.Include("Disbursement").Where(x => x.Disbursement.RetrievalID == requisitionId && x.ItemCode.Equals(itemCode)).Select(x => x.ActualQty).First());
}
}
public static string GetRetrievalStatusByRequisitionId(int requisitionId)
{
using (StationeryEntities context = new StationeryEntities())
{
return(context.Retrievals.Where(x => x.RetrievalID == requisitionId).Select(x => x.RetrievalStatus).First().ToString());
}
}
//EMPLOYEE NAME
public static string getEmployee(int id)
{
using (StationeryEntities context = new StationeryEntities())
{
return(context.Employees.Where(e => e.EmpID.Equals(id)).Select(e => e.EmpName).FirstOrDefault());
}
}
public static List <ShortfallItems> GenerateReorderReportForPurchasedItems(DateTime startDate, DateTime endDate)
{
StationeryEntities entities = new StationeryEntities();
//var itemList = (from item in entities.Items
// join pItems in entities.Item_PurchaseOrder on item.ItemCode equals pItems.ItemCode
// where item.ReorderLevel >= item.BalanceQty && (pItems.PurchaseOrder.OrderDate >= startDate && endDate >= pItems.PurchaseOrder.OrderDate)
var itemList = (from porder in entities.Item_PurchaseOrder
group porder by porder.ItemCode into p
join item in entities.Items on p.Key equals item.ItemCode
where item.ReorderLevel >= item.BalanceQty && (p.OrderByDescending(x => x.PurchaseOrderID).FirstOrDefault().PurchaseOrder.OrderDate >= startDate && endDate >= (p.OrderByDescending(x => x.PurchaseOrderID).FirstOrDefault().PurchaseOrder.OrderDate))
select new ShortfallItems
{
ItemCode = item.ItemCode,
Description = item.Description,
ReorderQuantity = item.ReorderQty,
ReorderLevel = item.ReorderLevel,
UnitOfMeasure = item.UnitOfMeasure,
Balance = item.BalanceQty,
PurchaseOrderNo = p.OrderByDescending(x => x.PurchaseOrderID).FirstOrDefault().PurchaseOrderID,
ExpectedDate = p.OrderByDescending(x => x.PurchaseOrderID).FirstOrDefault().PurchaseOrder.ExpectedDate
}).ToList <ShortfallItems>();
return(itemList);
}
public static List <Requisition> SearchForReqHeadWithStatus(string deptCode, string status)
{
using (StationeryEntities context = new StationeryEntities())
{
return(context.Requisitions.OrderByDescending(x => x.RequestDate).Where(x => x.DeptCode.Equals(deptCode) && x.Status.Equals(status)).ToList());
}
}
public static int?GetRequisitionsForGivenMonth(DateTime startDate, DateTime endDate, string dept, string cat)
{
using (TransactionScope ts = new TransactionScope())
{
StationeryEntities SE = new StationeryEntities();
var totalR = from ri in SE.Requisition_Item
from r in SE.Requisitions
from i in SE.Items
from d in SE.Departments
from e in SE.Employees
from c in SE.Categories
where ri.ItemCode == i.ItemCode
where ri.RequisitionID == r.RequisitionID
where r.ApprovedBy == e.EmpID
where e.DeptCode == d.DeptCode
where d.DeptName == dept
where i.CategoryID == c.CategoryID
where c.CategoryName == cat
where r.RequestDate >= startDate
where r.RequestDate <= endDate
select ri.RequestedQty;
int?returnValue = totalR.Sum();
ts.Complete();
return(returnValue);
}
}
public static List <Requisition> DisplayReqByDeptCodeEmpID(string deptCode, int empID)
{
using (StationeryEntities context = new StationeryEntities())
{
return(context.Requisitions.OrderByDescending(x => x.RequestDate).Where(x => x.DeptCode.Equals(deptCode) && x.RequestedBy == (empID)).ToList());
}
}
public static Employee GetDHeadByDeptCode(string depCode)
{
using (StationeryEntities smodel = new StationeryEntities())
{
return(smodel.Employees.Where(x => x.DeptCode == depCode && x.Role == "DepartmentHead").First());
}
}
public static void ClosePurchaseOrder(PurchaseOrder pOrder)
{
using (TransactionScope ts = new TransactionScope())
{
StationeryEntities entities = new StationeryEntities();
PurchaseOrder order = entities.PurchaseOrders.Where(x => x.PurchaseOrderID == pOrder.PurchaseOrderID).First();
order.DONumber = pOrder.DONumber;
order.Status = pOrder.Status;
order.ExpectedDate = DateTime.Now.Date;
List <Item_PurchaseOrder> itemList = entities.Item_PurchaseOrder.Where(x => x.PurchaseOrderID == order.PurchaseOrderID).ToList();
foreach (Item_PurchaseOrder item in itemList)
{
//StockCard itemStockCard = (from stck in entities.StockCards.OrderBy(x => x.Balance)
// where stck.ItemCode == item.ItemCode
// select stck).FirstOrDefault();
StockCard itemStockCard = new StockCard();
itemStockCard.ItemCode = item.ItemCode;
itemStockCard.Qty = item.OrderQty;
itemStockCard.TransactionType = "Purchase";
itemStockCard.TransactionDetailID = item.PurchaseOrderID;
Item itm = entities.Items.Where(x => x.ItemCode == item.ItemCode).FirstOrDefault();
itemStockCard.Balance = itm.BalanceQty + itemStockCard.Qty;
itm.BalanceQty = itemStockCard.Balance;
entities.StockCards.Add(itemStockCard);
}
entities.SaveChanges();
ts.Complete();
}
return;
}
public static Department GetDepartByDepCode(string depCode)
{
using (StationeryEntities smodel = new StationeryEntities())
{
return(smodel.Departments.Where(x => x.DeptCode == depCode).First());
}
}
public static List <Employee> GetEmployeeList()
{
using (StationeryEntities smodel = new StationeryEntities())
{
return(smodel.Employees.ToList <Employee>());
}
}
public static List <String> getAllClerkMails()
{
using (StationeryEntities context = new StationeryEntities())
{
return(context.Employees.Where(e => e.Role.Equals("Store Clerk")).Select(e => e.Email).ToList());
}
}
public static void UpdateActingDHead(string depcode, int empid, string sdate, string edate)
{
using (StationeryEntities smodel = new StationeryEntities())
{
try
{
if (EFBroker_DeptEmployee.GetEmployeeListForActingDHeadSelectedCount(depcode) <= 0)
{
Employee emp = smodel.Employees.Where(p => p.DeptCode == depcode && p.EmpID == empid).First <Employee>();
emp.IsTempHead = "Y";
emp.StartDate = Convert.ToDateTime(sdate);
emp.EndDate = Convert.ToDateTime(edate);
smodel.SaveChanges();
}
else
{
Employee orgemp = smodel.Employees.Where(q => q.DeptCode == depcode && q.IsTempHead == "Y").First <Employee>();
orgemp.IsTempHead = "N";
orgemp.StartDate = null;
orgemp.EndDate = null;
Employee emp = smodel.Employees.Where(p => p.DeptCode == depcode && p.EmpID == empid).First <Employee>();
emp.IsTempHead = "Y";
emp.StartDate = Convert.ToDateTime(sdate);
emp.EndDate = Convert.ToDateTime(edate);
smodel.SaveChanges();
}
}
catch (Exception e)
{
}
}
}
//AUTHOR : KHIN MO MO ZIN
public static string GetDRepresentativeNameByDeptCode(string depCode)
{
using (StationeryEntities smodel = new StationeryEntities())
{
return(smodel.Employees.Where(x => x.DeptCode == depCode && x.Role == "Representative").Select(x => x.EmpName).First());
}
}
public static Employee GetEmployeeEmailByEid(int eid)
{
using (StationeryEntities context = new StationeryEntities())
{
return(context.Employees.Where(x => x.EmpID == eid).First());
}
}
public static List <CollectionPoint> GetCollectionPointList()
{
using (StationeryEntities smodel = new StationeryEntities())
{
return(smodel.CollectionPoints.ToList <CollectionPoint>());
}
}
//
// TODO: Add constructor logic here
//
public static List <Department> GetDepartList()
{
using (StationeryEntities smodel = new StationeryEntities())
{
return(smodel.Departments.ToList <Department>());
}
}
public static List <Employee> GetEmployeeListForDRep(string deptcode, int a)
{
using (StationeryEntities smodel = new StationeryEntities())
{
return(smodel.Employees.Where
(p => p.DeptCode == deptcode && p.IsTempHead == "N" && p.Role != "DepartmentHead" && p.EmpID != a).ToList <Employee>());
}
}
public static int GetEmployeeListForActingDHeadSelectedCount(string deptcode)
{
using (StationeryEntities smodel = new StationeryEntities())
{
return(smodel.Employees.Where
(p => p.DeptCode == deptcode && p.IsTempHead == "Y").Count());
}
}
