public ActionResult Retrieval()
{
Session["StoreDisbursementTabIndex"] = "2";
using (SSISdbEntities m = new SSISdbEntities())
{
m.Configuration.ProxyCreationEnabled = false;
int id = m.StockRetrievalHeaders.Count();
string reqId = CommonLogic.SerialNo(id, "StoR");
Session["RetrievalId"] = reqId;
ViewBag.IdCount = reqId;
ViewBag.Disbursed = m.StockRetrievalHeaders.Where(x => x.ID == reqId).First().Disbursed;
//To check whether all items have been retrieved
List <StockRetrievalDetail> stockRetrievalDetailsList = m.StockRetrievalDetails.Where(x => x.Id == reqId).ToList <StockRetrievalDetail>();
bool allItemCollected = true;
foreach (StockRetrievalDetail srd in stockRetrievalDetailsList)
{
if (srd.Collected == 0)
{
allItemCollected = false;
}
}
StockRetrievalHeader stockRetrievalHeader = m.StockRetrievalHeaders.Where(x => x.ID == reqId).FirstOrDefault();
if (allItemCollected)
{
stockRetrievalHeader.AllItemsRetrieved = 1;
m.SaveChanges();
}
else
{
stockRetrievalHeader.AllItemsRetrieved = 0;
m.SaveChanges();
}
ViewBag.AllItemsRetrieved = stockRetrievalHeader.AllItemsRetrieved;
}
var tuple = new Tuple <StockRetrievalDetail, Item>(new StockRetrievalDetail(), new Item());
return(View(tuple));
}
public void UpdateStockRetrievalHeader(StockRetrievalHeader stockRetrievalHeader)
{
context.Entry(stockRetrievalHeader).State = EntityState.Modified;
}
public void DeleteStockRetrievalHeader(int id)
{
StockRetrievalHeader stockRetrievalHeader = context.StockRetrievalHeaders.Find(id);
context.StockRetrievalHeaders.Remove(stockRetrievalHeader);
}
public void InsertStockRetrievalHeader(StockRetrievalHeader stockRetrievalHeader)
{
context.StockRetrievalHeaders.Add(stockRetrievalHeader);
}
public Task Execute(IJobExecutionContext context)
{
using (SSISdbEntities m = new SSISdbEntities())
{
#region Variables
// List of StaffReq with Open & Approved
List <StaffRequisitionHeader> staffReqHeadList = m.StaffRequisitionHeaders
.Where(x => x.Status == "Open" && x.ApprovalStatus == "Approved").ToList();
// List of StaffReq with Outstanding
List <StaffRequisitionHeader> srhWithBO = m.StaffRequisitionHeaders.Where(x => x.Status == "Outstanding").ToList();
Dictionary <Item, int> itemAndQty = new Dictionary <Item, int>();
List <StaffRequisitionDetail> srdList = new List <StaffRequisitionDetail>();
List <StaffRequisitionDetail> srdWithBackOrders = new List <StaffRequisitionDetail>();
Dictionary <Item, List <string> > listOfItemsAndColAdded = new Dictionary <Item, List <string> >();
List <string> colpt = new List <string>();
List <StaffRequisitionDetail> allsrd = new List <StaffRequisitionDetail>();
#endregion
#region Prepare new StaffRequisitionDetails to add
// Search for all Outstanding StaffReqDetails
foreach (StaffRequisitionHeader srhBO in srhWithBO)
{
List <StaffRequisitionDetail> SRD = m.StaffRequisitionDetails
.Where(x => x.FormID == srhBO.FormID && x.QuantityBackOrdered > 0).ToList();
if (SRD != null)
{
foreach (StaffRequisitionDetail sr in SRD)
{
srdWithBackOrders.Add(sr);
}
}
}
// Search for all Open && Approved StaffReqDetails
foreach (StaffRequisitionHeader srhInWeek in staffReqHeadList)
{
srhInWeek.Status = "Outstanding";
m.SaveChanges();
// Find List of StaffReqDetails based on Header ID
List <StaffRequisitionDetail> SRD = m.StaffRequisitionDetails
.Where(x => x.FormID == srhInWeek.FormID).ToList();
if (SRD != null)
{
foreach (StaffRequisitionDetail sr in SRD)
{
srdList.Add(sr);
}
}
}
#endregion
// Check if there's any entries to collate
if (srdList.Count() > 0 || srdWithBackOrders.Count() > 0)
{
#region Create StockRetrievalHeader
// Create StockRetrievalHeader
StockRetrievalHeader newsrh = new StockRetrievalHeader();
int newSRH = m.StockRetrievalHeaders.Count() + 1;
string srhId = CommonLogic.SerialNo(newSRH, "StoR");
newsrh.ID = srhId;
newsrh.Date = DateTime.Now;
newsrh.Disbursed = 0;
newsrh.AllItemsRetrieved = 0;
m.StockRetrievalHeaders.Add(newsrh);
//m.SaveChanges();
#endregion
#region Confirming how many Items & quantity to collate
// Collate items based on Backorder SRDs (Total up items and its quantity to collect)
if (srdWithBackOrders.Count() > 0)
{
foreach (StaffRequisitionDetail srdlist in srdWithBackOrders)
{
Item item = m.Items.Where(x => x.ItemCode == srdlist.ItemCode).FirstOrDefault();
if (!itemAndQty.ContainsKey(item))
{
itemAndQty.Add(item, srdlist.QuantityBackOrdered);
}
else
{
itemAndQty[item] += srdlist.QuantityBackOrdered;
}
}
}
// Collate items based on new SRD (Total up items and its quantity to collect)
if (srdList.Count() > 0)
{
foreach (StaffRequisitionDetail srdlist in srdList)
{
Item item = m.Items.Where(x => x.ItemCode == srdlist.ItemCode).FirstOrDefault();
if (!itemAndQty.ContainsKey(item))
{
itemAndQty.Add(item, srdlist.QuantityOrdered);
}
else
{
itemAndQty[item] += srdlist.QuantityOrdered;
}
}
}
// Check if quantity is sufficient (If insufficient, can only collect based on Quantity on Hand)
foreach (Item itemToCollate in itemAndQty.Keys.ToList())
{
if (itemToCollate.Quantity < itemAndQty[itemToCollate])
{
itemAndQty[itemToCollate] = itemToCollate.Quantity;
}
}
#endregion
#region Fulfill Backorders
// Fulfill all backorders first
if (srdWithBackOrders.Count() > 0)
{
foreach (StaffRequisitionDetail retrievalListWithBO in srdWithBackOrders)
{
Item itemRetrieved = m.Items.Where(x => x.ItemCode == retrievalListWithBO.ItemCode).FirstOrDefault();
Bin bin = m.Bins.Where(x => x.ItemCode == retrievalListWithBO.ItemCode).FirstOrDefault();
StaffRequisitionHeader srhDeptCode = m.StaffRequisitionHeaders.Where(x => x.FormID == retrievalListWithBO.FormID).FirstOrDefault();
DepartmentDetail dd = m.DepartmentDetails.Where(x => x.DepartmentCode == srhDeptCode.DepartmentCode).FirstOrDefault();
StockRetrievalDetail newsrd = new StockRetrievalDetail();
int qtyAvailable = itemAndQty[itemRetrieved];
// Only create entries with qty available
if (qtyAvailable > 0)
{
// Create quantity requested that is really available for that item
int qtyToAdd = 0;
if (qtyAvailable > retrievalListWithBO.QuantityBackOrdered)
{
qtyToAdd = retrievalListWithBO.QuantityBackOrdered;
}
else
{
qtyToAdd = qtyAvailable;
}
if (listOfItemsAndColAdded.ContainsKey(itemRetrieved))
{
if (!listOfItemsAndColAdded[itemRetrieved].Contains(dd.CollectionPointID))
{
// If its an entirely new Item for the collection point of the SRD, create a new entry
newsrd.Id = srhId;
newsrd.Bin = bin.Number;
newsrd.ItemCode = retrievalListWithBO.ItemCode;
newsrd.QuantityRetrieved = qtyToAdd;
newsrd.CollectionPointID = dd.CollectionPointID;
newsrd.QuantityAdjusted = 0;
newsrd.Remarks = "";
newsrd.Collected = 0;
m.StockRetrievalDetails.Add(newsrd);
allsrd.Add(retrievalListWithBO);
// Prepare list for iteration of repeated entries (Indicate that there's exisiting record of Item & Collection point)
List <string> existingColpt = listOfItemsAndColAdded[itemRetrieved];
existingColpt.Add(dd.CollectionPointID);
listOfItemsAndColAdded[itemRetrieved] = existingColpt;
}
else
{
// If there's existing entry (Based on previous collection point / back orders created), add to entry
StockRetrievalDetail existingSRD = m.StockRetrievalDetails
.Where(x => x.Id == srhId && x.ItemCode == itemRetrieved.ItemCode &&
x.CollectionPointID == dd.CollectionPointID).FirstOrDefault();
existingSRD.QuantityRetrieved += qtyToAdd;
allsrd.Add(retrievalListWithBO);
}
}
else
{
// If its an entirely new Item for the collection point of the SRD, create a new entry
newsrd.Id = srhId;
newsrd.Bin = bin.Number;
newsrd.ItemCode = retrievalListWithBO.ItemCode;
newsrd.QuantityRetrieved = qtyToAdd;
newsrd.CollectionPointID = dd.CollectionPointID;
newsrd.QuantityAdjusted = 0;
newsrd.Remarks = "";
newsrd.Collected = 0;
m.StockRetrievalDetails.Add(newsrd);
allsrd.Add(retrievalListWithBO);
colpt = new List <string>();
colpt.Add(dd.CollectionPointID);
listOfItemsAndColAdded.Add(itemRetrieved, colpt);
}
//Remove quantity added from dictionary
itemAndQty[itemRetrieved] -= qtyToAdd;
m.SaveChanges();
}
}
}
#endregion
#region Fulfill new entries
// Create StockRetrievalDetails
if (srdList.Count() > 0)
{
foreach (StaffRequisitionDetail retrievalList in srdList)
{
Item itemRetrieved = m.Items.Where(x => x.ItemCode == retrievalList.ItemCode).FirstOrDefault();
Bin bin = m.Bins.Where(x => x.ItemCode == retrievalList.ItemCode).FirstOrDefault();
StaffRequisitionHeader srhDeptCode = m.StaffRequisitionHeaders
.Where(x => x.FormID == retrievalList.FormID).FirstOrDefault();
DepartmentDetail dd = m.DepartmentDetails
.Where(x => x.DepartmentCode == srhDeptCode.DepartmentCode).FirstOrDefault();
StockRetrievalDetail newsrd = new StockRetrievalDetail();
int qtyAvailable = itemAndQty[itemRetrieved];
// Only create entries with qty available
if (qtyAvailable > 0)
{
// Create quantity requested that is really available for that item
int qtyToAdd = 0;
if (qtyAvailable > retrievalList.QuantityOrdered)
{
qtyToAdd = retrievalList.QuantityOrdered;
}
else
{
qtyToAdd = qtyAvailable;
}
if (listOfItemsAndColAdded.ContainsKey(itemRetrieved))
{
if (!listOfItemsAndColAdded[itemRetrieved].Contains(dd.CollectionPointID))
{
// If its an entirely new Item for the collection point of the SRD, create a new entry
newsrd.Id = srhId;
newsrd.Bin = bin.Number;
newsrd.ItemCode = retrievalList.ItemCode;
newsrd.QuantityRetrieved = qtyToAdd;
newsrd.CollectionPointID = dd.CollectionPointID;
newsrd.QuantityAdjusted = 0;
newsrd.Remarks = "";
newsrd.Collected = 0;
m.StockRetrievalDetails.Add(newsrd);
allsrd.Add(retrievalList);
// Prepare list for iteration of repeated entries (Indicate that there's exisiting record of Item & Collection point)
List <string> existingColpt = listOfItemsAndColAdded[itemRetrieved];
existingColpt.Add(dd.CollectionPointID);
listOfItemsAndColAdded[itemRetrieved] = existingColpt;
}
else
{
// If there's existing entry (Based on previous collection point / back orders created), add to entry
StockRetrievalDetail existingSRD = m.StockRetrievalDetails
.Where(x => x.Id == srhId && x.ItemCode == itemRetrieved.ItemCode &&
x.CollectionPointID == dd.CollectionPointID).FirstOrDefault();
existingSRD.QuantityRetrieved += qtyToAdd;
allsrd.Add(retrievalList);
}
}
else
{
// If its an entirely new Item for the collection point of the SRD, create a new entry
newsrd.Id = srhId;
newsrd.Bin = bin.Number;
newsrd.ItemCode = retrievalList.ItemCode;
newsrd.QuantityRetrieved = qtyToAdd;
newsrd.CollectionPointID = dd.CollectionPointID;
newsrd.QuantityAdjusted = 0;
newsrd.Remarks = "";
newsrd.Collected = 0;
m.StockRetrievalDetails.Add(newsrd);
allsrd.Add(retrievalList);
colpt = new List <string>();
colpt.Add(dd.CollectionPointID);
listOfItemsAndColAdded.Add(itemRetrieved, colpt);
}
// Remove qty that is added
itemAndQty[itemRetrieved] -= qtyToAdd;
m.SaveChanges();
}
}
}
#endregion
#region Create StockRetrievalReqForm
List <String> allNewlyCreatedReqFormId = new List <String>();
foreach (StaffRequisitionDetail allNewlyCreated in allsrd)
{
allNewlyCreatedReqFormId.Add(allNewlyCreated.FormID);
}
//To remove duplicate req form id
allNewlyCreatedReqFormId = allNewlyCreatedReqFormId.Distinct().ToList();
foreach (String newReqFormId in allNewlyCreatedReqFormId)
{
// New StockRetrievalReqForm
StockRetrievalReqForm srrf = new StockRetrievalReqForm();
//srrf.ReqFormID = allNewlyCreated.FormID;
srrf.ReqFormID = newReqFormId;
srrf.StockRetrievalID = srhId;
m.StockRetrievalReqForms.Add(srrf);
m.SaveChanges();
}
#endregion
}
}
return(null);
}
public RedirectToRouteResult DisburseItems()
{
using (SSISdbEntities m = new SSISdbEntities())
{
m.Configuration.ProxyCreationEnabled = false;
string id = (string)Session["RetrievalId"];
StockRetrievalHeader srh = m.StockRetrievalHeaders.Where(x => x.ID == id).First();
srh.Disbursed = 1;
List <StockRetrievalDetail> itemsRetrieved = m.StockRetrievalDetails.Where(x => x.Id == id).ToList();
bool stockAdjustmentHeaderCreated = false;
foreach (StockRetrievalDetail srd in itemsRetrieved)
{
DateTime localDate = DateTime.Now;
if (srd.QuantityAdjusted > 0)
{
//To check whether there is stock adjustment header for the item
if (!stockAdjustmentHeaderCreated)
{
//To Create Stock Adjustment Header
stockAdjustmentHeaderCreated = true;
StockAdjustmentHeader sah = new StockAdjustmentHeader();
int stockAdjustmentHeaderId = m.StockAdjustmentHeaders.Count() + 1;
sah.RequestId = CommonLogic.SerialNo(stockAdjustmentHeaderId, "SA");
sah.DateRequested = localDate;
sah.Requestor = (string)Session["LoginEmployeeID"];
sah.TransactionType = "Stock Adjustment";
m.StockAdjustmentHeaders.Add(sah);
m.SaveChanges();
}
//To Create Stock Adjustment Details
int stockAdjustmentDetailId = m.StockAdjustmentHeaders.Count();
StockAdjustmentDetail sad = new StockAdjustmentDetail();
sad.RequestId = CommonLogic.SerialNo(stockAdjustmentDetailId, "SA");
sad.ItemCode = srd.ItemCode;
sad.ItemQuantity = srd.QuantityAdjusted;
float itemUnitCost = m.Items.Where(x => x.ItemCode == sad.ItemCode).Select(x => x.AvgUnitCost).FirstOrDefault();
sad.Amount = itemUnitCost * sad.ItemQuantity;
sad.Remarks = srd.Remarks;
sad.Status = "Pending";
m.StockAdjustmentDetails.Add(sad);
//To add the item transactions
ItemTransaction itemTransaction = new ItemTransaction();
itemTransaction.TransDateTime = localDate;
itemTransaction.DocumentRefNo = sad.RequestId;
itemTransaction.ItemCode = sad.ItemCode;
itemTransaction.TransactionType = "Stock Adjustment";
itemTransaction.Quantity = sad.ItemQuantity;
itemTransaction.UnitCost = itemUnitCost;
itemTransaction.Amount = sad.Amount;
m.ItemTransactions.Add(itemTransaction);
//To update the quantity of the item table
Item itemAdjusted = m.Items.Where(x => x.ItemCode == itemTransaction.ItemCode).FirstOrDefault();
itemAdjusted.Quantity -= itemTransaction.Quantity;
m.SaveChanges();
}
}
//Creating list of new disbursements
//To order by id so the earlier id will mean that the req form was submitted earlier
List <String> reqFormIDList = m.StockRetrievalReqForms.OrderBy(x => x.Id).Where(x => x.StockRetrievalID == id).Select(x => x.ReqFormID).ToList <String>();
//Create distinct disbursement headers
List <String> disbHeaderDeptCodes = new List <String>();
//Take from stock retrieval details
List <StockRetrievalDetail> stockRetrievalDetails = m.StockRetrievalDetails.Where(x => x.Id == id).ToList <StockRetrievalDetail>();
foreach (StockRetrievalDetail srd in stockRetrievalDetails)
{
//To take care that in case stock adjustment makes the quantity retrieved to be 0
if (srd.QuantityRetrieved > 0)
{
String deptCode = m.DepartmentDetails.Where(x => x.CollectionPointID == srd.CollectionPointID).FirstOrDefault().DepartmentCode;
disbHeaderDeptCodes.Add(deptCode);
}
}
//Make it distinct so that only one disbursement header is created
disbHeaderDeptCodes = disbHeaderDeptCodes.Distinct().ToList();
foreach (String deptCode in disbHeaderDeptCodes)
{
DisbursementHeader newDH = new DisbursementHeader();
int count = m.DisbursementHeaders.Count() + 1;
string disId = CommonLogic.SerialNo(count, "DH");
newDH.Id = disId;
newDH.Status = "Open";
newDH.Date = DateTime.Now;
newDH.Amount = 0; //Put 0 first and then to be calculated after all the disbursement details is done
newDH.StockRetrievalId = id;
newDH.DepartmentCode = deptCode;
newDH.CollectionPointID = m.DepartmentDetails.Where(x => x.DepartmentCode == deptCode).FirstOrDefault().CollectionPointID;
newDH.RepresentativeID = m.DepartmentDetails.Where(x => x.DepartmentCode == newDH.DepartmentCode).FirstOrDefault().RepresentativeID;
float totalAmount = 0f;
//Create disbursement details, since one collection point is for one dept, then the entire stock retrieved would be assigned to that dept
foreach (StockRetrievalDetail srd in stockRetrievalDetails)
{
String deptCodeDH = m.DepartmentDetails.Where(x => x.CollectionPointID == srd.CollectionPointID).Select(x => x.DepartmentCode).FirstOrDefault();
//Only disbursed if quantity retrieved is more than 0
if (srd.QuantityRetrieved > 0 && deptCodeDH.Equals(newDH.DepartmentCode))
{
DisbursementDetail newDD = new DisbursementDetail();
newDD.Id = disId;
newDD.ItemCode = srd.ItemCode;
int quantityOrdered = 0;
foreach (String reqF in reqFormIDList)
{
StaffRequisitionDetail staffReqDet = m.StaffRequisitionDetails.Where(x => x.FormID == reqF && x.ItemCode == newDD.ItemCode).FirstOrDefault();
StaffRequisitionHeader staffReqHeader = m.StaffRequisitionHeaders.Where(x => x.FormID == reqF).FirstOrDefault();
int reqQtyOrdered = 0;
if (staffReqDet != null && staffReqHeader != null)
{
if (staffReqHeader.DepartmentCode.Equals(newDH.DepartmentCode))
{
reqQtyOrdered = staffReqDet.QuantityOrdered;
}
}
quantityOrdered = quantityOrdered + reqQtyOrdered;
}
newDD.QuantityOrdered = quantityOrdered;
newDD.QuantityReceived = srd.QuantityRetrieved;
float itemUnitCost = m.Items.Where(x => x.ItemCode == newDD.ItemCode).Select(x => x.AvgUnitCost).FirstOrDefault();
newDD.UnitCost = itemUnitCost;
newDD.UoM = m.Items.Where(x => x.ItemCode == newDD.ItemCode).Select(x => x.UoM).FirstOrDefault();
newDD.QuantityAdjusted = 0;
newDD.TransactionType = "Disbursement";
float amount = itemUnitCost * newDD.QuantityReceived;
totalAmount += amount;
m.DisbursementDetails.Add(newDD);
//To add the item transactions
DateTime localDate = DateTime.Now;
ItemTransaction itemTransaction = new ItemTransaction();
itemTransaction.TransDateTime = localDate;
itemTransaction.DocumentRefNo = newDD.Id;
itemTransaction.ItemCode = newDD.ItemCode;
itemTransaction.TransactionType = "Disbursement";
itemTransaction.Quantity = newDD.QuantityReceived;
itemTransaction.UnitCost = itemUnitCost;
itemTransaction.Amount = newDD.QuantityReceived * itemUnitCost;
m.ItemTransactions.Add(itemTransaction);
//To update the quantity of the item table
Item itemDisbursed = m.Items.Where(x => x.ItemCode == itemTransaction.ItemCode).FirstOrDefault();
itemDisbursed.Quantity -= itemTransaction.Quantity;
}
}
newDH.Amount = totalAmount;
m.DisbursementHeaders.Add(newDH);
m.SaveChanges();
}
}
return(RedirectToAction("Disbursements", "Store"));
}
