public static Dictionary <string, List <DisItemTotalQtyModel_M> > getDisburselistByCollectionPoint(string collectionID)
{
using (InventorySysDBEntities cntx = new InventorySysDBEntities())
{
Dictionary <string, List <DisItemTotalQtyModel_M> > DpNameAndDisMap = new Dictionary <string, List <DisItemTotalQtyModel_M> >();
var q = from x in cntx.Departments where x.Collection_ID == collectionID select x.Dept_ID;
List <string> deptIDList = q.ToList <string>();
foreach (string deptId in deptIDList)
{
Console.WriteLine(deptId);
var p = from x in cntx.DisbursementDetails
where x.Disbursement.Dept_ID == deptId && x.Disbursement.Status == "Submitted"
group x by x.Disbursement_ID into g
select new DisItemTotalQtyModel_M
{
Disbursement_ID = g.Key,
TotalQty = g.Sum(i => i.Quantity)
};
Console.WriteLine(p.ToString());
DpNameAndDisMap.Add(getDeptNameByDeptId(deptId), p.ToList());
}
return(DpNameAndDisMap);
}
}
//Requistion list for clerk
public static List <RequisitionModel_M> GetRequistionObjects()
{
using (InventorySysDBEntities context = new InventorySysDBEntities())
{
var q = from x in context.Requisitions where x.Status == "Approved" orderby x.Update_Date select x;
List <Requisition> requiList = q.ToList <Requisition>();
List <RequisitionModel_M> requisitionDetailList = new List <RequisitionModel_M>();
foreach (Requisition requi in requiList)
{
var p = from x in context.Requisitions where x.Requisition_ID == requi.Requisition_ID select x.SubmissionStaff_ID;
string staffId = p.First();
var r = from x in context.Staffs where x.Staff_ID == staffId select x.Department.Dept_Name;
string deptName = r.First();
RequisitionModel_M singleRequsition = new RequisitionModel_M();
singleRequsition.ReuisitionId1 = requi.Requisition_ID;
singleRequsition.DepartmentName1 = deptName;
singleRequsition.RequisitionType1 = requi.RequisitonType;
requisitionDetailList.Add(singleRequsition);
}
return(requisitionDetailList);
}
}
public static Requisition getReqByID(string reqID)
{
using (InventorySysDBEntities cntx = new InventorySysDBEntities())
{
return((Requisition)cntx.Requisitions.Find(reqID));
}
}
public static void updateRequisition(List <RequisitionItem> reqItemList, string reqID, string status)
{
using (InventorySysDBEntities cntx = new InventorySysDBEntities())
{
Requisition req = cntx.Requisitions.Find(reqID);
req.Create_Date = DateTime.Now;
req.Status = status;
foreach (RequisitionItem reqi in reqItemList)
{
if (reqi.Required_Qty == 0)
{
RequisitionItem reqItem = cntx.RequisitionItems.Find(reqi.Requisition_ID, reqi.Item_ID);
cntx.RequisitionItems.Remove(reqItem);
}
else
{
RequisitionItem reqItem = cntx.RequisitionItems.Find(reqi.Requisition_ID, reqi.Item_ID);
reqItem.Required_Qty = reqi.Required_Qty;
}
}
cntx.SaveChanges();
}
}
public static void createRequisition(List <RequisitionItem> reqItemList, string submitStaff_id, string type, string status)
{
Requisition newReq = new Requisition();
Staff fromStaff = StaffController.getStaffById(submitStaff_id);
// use sample data to test
newReq.Requisition_ID = "REQ" + DateTime.Now.ToString("yyyyMMddHHmmss");
newReq.SubmissionStaff_ID = submitStaff_id;
// approve staff can be null , for test use sample data
//newReq.ApprovedByStaff_ID = "STF0002";
newReq.Create_Date = DateTime.Now;
newReq.RequisitonType = type;
newReq.Status = status;
using (InventorySysDBEntities cntx = new InventorySysDBEntities())
{
cntx.Requisitions.Add(newReq);
cntx.SaveChanges();
foreach (RequisitionItem reqItem in reqItemList)
{
reqItem.Requisition_ID = newReq.Requisition_ID;
cntx.RequisitionItems.Add(reqItem);
}
cntx.SaveChanges();
}
if (type != "Special")
{
SendNotificationController.SendNotificaition(fromStaff, newReq.Requisition_ID);
}
}
public static int checkDisbursmentID(string Dis_ID, string RepID)
{
int isDisbursmentIDCorrect = 0;
using (InventorySysDBEntities cntx = new InventorySysDBEntities())
{
Staff Rep = cntx.Staffs.Find(RepID);
// whether disbursment exist
Disbursement dis = cntx.Disbursements.Find(Dis_ID);
if (dis != null)
{
// Rep only can see its own disbursment
if (dis.Dept_ID == Rep.Dept_ID)
{
// only the submitted disbursment can be seen
switch (dis.Status)
{
case "Received":
isDisbursmentIDCorrect = 2;
break;
case "Submitted":
isDisbursmentIDCorrect = 1;
break;
}
}
}
return(isDisbursmentIDCorrect);
}
}
public static void ConfirmRecivedItems(string DI, List <DisbursementDetail> ddlist, string deptID)
{
Staff rep = new Staff();
using (InventorySysDBEntities cntx = new InventorySysDBEntities())
{
rep = StaffController.getRepByDepID(deptID);
//update disbursmentdetail
foreach (DisbursementDetail dd in ddlist)
{
updateDisbursmentDetail(cntx, dd);
updateItemTransReceived(cntx, deptID, dd);
}
//update disbursment
Disbursement disbursment = cntx.Disbursements.Find(DI);
disbursment.Receive_Date = DateTime.Now;
disbursment.Status = "Received";
// update Requisition
List <Requisition> reqList = getRequisitionsByDI(DI);
foreach (Requisition req in reqList)
{
Requisition reqData = cntx.Requisitions.Find(req.Requisition_ID);
reqData.Update_Date = DateTime.Now;
reqData.Status = "Received";
}
cntx.SaveChanges();
}
SendNotificationController.SendNotificaition(rep, DI);
}
//mobile
//mobile
public string getDeptIdByDeptName(string deptName)
{
using (InventorySysDBEntities context = new InventorySysDBEntities())
{
var q = from x in context.Departments where x.Dept_Name == deptName select x.Dept_ID;
return(q.First());
}
}
//mobile
public string getRepStaffId(string deptId)
{
using (InventorySysDBEntities context = new InventorySysDBEntities())
{
var q = from x in context.Staffs where x.Dept_ID == deptId && x.Role_ID == "RepStaff" select x.Staff_ID;
return(q.First());
}
}
//mobile
public List <RequisitionItem> GetreqItemListByReqId(string requisitionId)
{
using (InventorySysDBEntities context = new InventorySysDBEntities())
{
var q = from x in context.RequisitionItems where x.Requisition_ID == requisitionId select x;
return(q.ToList <RequisitionItem>());
}
}
//calling from create disburement method
//mobile
public string GetRequisitionTypeByReqId(string requisitionId)
{
using (InventorySysDBEntities context = new InventorySysDBEntities())
{
var q = from x in context.Requisitions where x.Requisition_ID == requisitionId select x.RequisitonType;
return(q.First());
}
}
//Disburement Search History
//listing
public static string getDeptNameByDeptId(string deptID)
{
using (InventorySysDBEntities cntx = new InventorySysDBEntities())
{
var q = from x in cntx.Departments where x.Dept_ID == deptID select x.Dept_Name;
return(q.First());
}
}
public static List <Item> getAllItem()
{
using (InventorySysDBEntities cntx = new InventorySysDBEntities())
{
var q = from x in cntx.Items select x;
return(q.ToList());
}
}
//mobile
public Item GetItemObjectById(string itemId)
{
using (InventorySysDBEntities context = new InventorySysDBEntities())
{
Item itemOb = context.Items.Single(u => u.Item_ID == itemId);
return(itemOb);
}
}
public Item getItemById(string id)
{
using (InventorySysDBEntities cntx = new InventorySysDBEntities())
{
Item item = cntx.Items.Find(id);
return(item);
}
}
//mobile
public List <RequisitionItem> GetSelectedreqItemList(List <string> reqIdList)
{
using (InventorySysDBEntities context = new InventorySysDBEntities())
{
var q = from x in context.RequisitionItems where reqIdList.Contains(x.Requisition_ID) select x;
return(q.ToList <RequisitionItem>());
}
}
public List <string> getAllItemDesList()
{
using (InventorySysDBEntities cntx = new InventorySysDBEntities())
{
var q = from c in cntx.Items select c.Item_Name;
return(q.ToList());
}
}
public List <string> getCategoryList()
{
using (InventorySysDBEntities cntx = new InventorySysDBEntities())
{
var q = from c in cntx.ItemCategories select c.Category_ID;
return(q.ToList());
}
}
public static void updateDisbursmentDetail(InventorySysDBEntities cntx, DisbursementDetail newDD)
{
// update disbursmentDetailRow
string DI = newDD.Disbursement_ID;
string itemID = newDD.Item_ID;
DisbursementDetail oldDD = cntx.DisbursementDetails.Find(DI, itemID);
oldDD.Quantity = newDD.Quantity;
}
public Item GetDefaultItem()
{
using (InventorySysDBEntities cntx = new InventorySysDBEntities())
{
var q = from x in cntx.Items where x.Item_ID == "C001" select x;
Item i = q.First <Item>();
return(i);
}
}
// approve staff may be many, AuthStaff and Manager
public static List <Staff> getApprovStaffListByDepId(string DepId)
{
using (InventorySysDBEntities cntx = new InventorySysDBEntities())
{
var q = from x in cntx.Staffs
where x.Dept_ID == DepId && (x.Role_ID == "Manager" || x.Role_ID == "AuthStaff")
select x;
return(q.ToList());
}
}
public static List <Staff> getStaffListById(string id)
{
using (InventorySysDBEntities cntx = new InventorySysDBEntities())
{
var q = from x in cntx.Staffs
where x.Staff_ID == id
select x;
return(q.ToList());
}
}
public static List <Staff> getAllClerk()
{
using (InventorySysDBEntities cntx = new InventorySysDBEntities())
{
var q = from x in cntx.Staffs
where x.Role_ID == "StoreClerk"
select x;
return(q.ToList());
}
}
public static List <Department> getAllDepartment()
{
using (InventorySysDBEntities cntx = new InventorySysDBEntities())
{
var q = from x in cntx.Departments
where x.Dept_ID != "STAT" && x.Dept_ID != "SUPP"
select x;
return(q.ToList());
}
}
public static Staff getRepByDepID(string depid)
{
using (InventorySysDBEntities cntx = new InventorySysDBEntities())
{
var q = from x in cntx.Staffs
where x.Dept_ID == depid && x.Role_ID == "RepStaff"
select x;
return(q.First());
}
}
public static List <Staff> getStaffListByDepID(string DepID)
{
using (InventorySysDBEntities cntx = new InventorySysDBEntities())
{
var q = from x in cntx.Staffs
where x.Dept_ID == DepID
select x;
return(q.ToList());
}
}
public static List <Staff> getSManagerByid(Staff stf)
{
using (InventorySysDBEntities cntx = new InventorySysDBEntities())
{
var q = from x in cntx.Staffs
where x.Dept_ID == stf.Dept_ID && x.Role_ID == "SManager"
select x;
return(q.ToList());
}
}
//search listing requisition for staff
public static List <Requisition> getReqByDate_Status(string stfid, string fromDate, string toDate, string Status)
{
DateTime fromD = new DateTime();
DateTime toD = new DateTime();
if (fromDate != "" && toDate != "")
{
fromD = Convert.ToDateTime(fromDate);
toD = Convert.ToDateTime(toDate);
}
else
{
if (fromDate == "" && toDate == "")
{
fromD = DateTime.MinValue;
toD = DateTime.MaxValue;
}
if (fromDate != "" && toDate == "")
{
fromD = Convert.ToDateTime(fromDate);
toD = DateTime.MaxValue;
}
if (fromDate == "" && toDate != "")
{
fromD = DateTime.MinValue;
toD = Convert.ToDateTime(toDate);
}
}
using (InventorySysDBEntities cntx = new InventorySysDBEntities())
{
if (Status == "All")
{
var q = from x in cntx.Requisitions
where EntityFunctions.TruncateTime(x.Create_Date) >= fromD.Date
where EntityFunctions.TruncateTime(x.Create_Date) <= toD.Date
where x.SubmissionStaff_ID == stfid
select x;
return(q.ToList());
}
else
{
var q = from x in cntx.Requisitions
where EntityFunctions.TruncateTime(x.Create_Date) >= fromD.Date
where EntityFunctions.TruncateTime(x.Create_Date) <= toD.Date
where x.Status == Status
where x.SubmissionStaff_ID == stfid
select x;
return(q.ToList());
}
}
}
public string getItemUOMByID(string id)
{
using (InventorySysDBEntities cntx = new InventorySysDBEntities())
{
var q = from c in cntx.Items
where c.Item_ID == id
select c.UOM;
return(q.First());
}
}
public static List <Requisition> getRequisitionsByDI(string DI)
{
using (InventorySysDBEntities cntx = new InventorySysDBEntities())
{
var q = from req in cntx.Requisitions
where req.Disbursement_ID == DI
select req;
return(q.ToList());
}
}
