//create new outstanding for one department
public void createOutstandingList(string deptID, string storeStaffID)
{
Outstanding outstanding = new Outstanding();
outstanding.deptID = deptID;
outstanding.disburseDate = DateTime.Today.AddDays(7);//add to the next-week disbursementList
outstanding.storeStaffID = storeStaffID;
outstanding.status = "waiting for replenishment";
context.Outstandings.Add(outstanding);
context.SaveChanges();
}
//Can only allow to decrease Qty and can only change Qty one time.
public static bool UpdateDisbursement(string ItemID, int newQty)
{
//delete the temporary outstandings related to this ItemID
List <Outstanding> outslist = context.Outstanding.Where(x => x.Status == null && x.ItemID == ItemID).ToList <Outstanding>();
foreach (Outstanding o in outslist)
{
Disbursement d = context.Disbursement.Where(x => x.ItemID == o.ItemID && x.DepartmentID == o.DepartmentID && x.DeliveryID == null).ToList <Disbursement>().FirstOrDefault();
d.DisbursedQty += o.Qty;
context.Outstanding.Remove(o);
}
context.SaveChanges();
//restore the request qty
int outQty = RequestQty(ItemID) - newQty;
int count = 0;
//if the newQty >= the total request Qty, cannot accept
if (outQty < 0)
{
return(false);
}
else
{
while (outQty > 0)
{
int lastDep = LastDepartment(ItemID)[count];
Disbursement b = context.Disbursement.Where(x => x.ItemID == ItemID && x.DepartmentID == lastDep && x.DeliveryID == null).ToList <Disbursement>().First();
int newDisbursedQty = (int)b.DisbursedQty - outQty;
Outstanding o = new Outstanding();
o.DepartmentID = b.DepartmentID;
o.ItemID = b.ItemID;
if (newDisbursedQty >= 0)
{
b.DisbursedQty = newDisbursedQty;
o.Qty = outQty;
outQty = 0;
}
else
{
o.Qty = b.DisbursedQty;
b.DisbursedQty = 0;
outQty = -newDisbursedQty;
}
context.Outstanding.Add(o);
context.SaveChanges();
count++;
}
return(true);
}
}
//during delivery
public string UpdateDepDisbursement(int DisbursementID, int Qty)
{
using (StationeryStoreEntities context = new StationeryStoreEntities())
{
//select the request with last ApprovalDate
Disbursement b = context.Disbursements.Where(x => x.DisbursementID == DisbursementID).ToList <Disbursement>().First();
Outstanding outs = context.Outstandings.Where(x => x.Status == null && x.DepartmentID == b.DepartmentID && x.ItemID == b.ItemID).ToList <Outstanding>().FirstOrDefault();
if (Qty > RequestQty(b.ItemID, (int)b.DepartmentID))
{
return("notok");
}
else if (Qty < RequestQty(b.ItemID, (int)b.DepartmentID))
{
int outsQty = 0;
if (outs == null)
{
outsQty = (int)b.DisbursedQty - Qty;
Outstanding o = new Outstanding();
o.DepartmentID = b.DepartmentID;
o.ItemID = b.ItemID;
o.Qty = outsQty;
context.Outstandings.Add(o);
b.DisbursedQty = Qty;
context.SaveChanges();
return("ok");
}
else
{
outsQty = (int)outs.Qty + (int)b.DisbursedQty - Qty;
if (outsQty == 0)
{
context.Outstandings.Remove(outs);
}
else
{
outs.Qty = outsQty;
}
b.DisbursedQty = Qty;
context.SaveChanges();
return("ok");
}
}
else
{
b.DisbursedQty = RequestQty(b.ItemID, (int)b.DepartmentID);
context.Outstandings.Remove(outs);
context.SaveChanges();
return("ok");
}
}
}
private List <Outstanding> generateOutstandingList(List <Disbursement> dislist)
{
List <Outstanding> outlist = new List <Outstanding>();
bool IsNewDepartment = true;
foreach (Disbursement dept in dislist)// each department
{
foreach (DisbursementItem item in dept.DisbursementItems)
{
if (item.expectedQty > item.actualQty)// outstanding
{
foreach (Outstanding outsdept in outlist)
{
IsNewDepartment = true;
if (outsdept.deptID == dept.deptID)//not a new department in outstanding
{
IsNewDepartment = false;
OutstandingItem outitem = new OutstandingItem();
outitem.itemID = item.itemID;
outitem.expectedQty = item.expectedQty - item.actualQty;
outitem.actualQty = outitem.expectedQty;
outsdept.OutstandingItems.Add(outitem);
break;
}
}
if (IsNewDepartment)//add a new department in outstading
{
Outstanding outs = new Outstanding();
outs.deptID = dept.deptID;
outs.disburseDate = dept.disburseDate;
outs.storeStaffID = dept.storeStaffID;
outs.status = "Pending";
OutstandingItem outitem = new OutstandingItem();
outitem.itemID = item.itemID;
outitem.expectedQty = item.expectedQty - item.actualQty;
outitem.actualQty = outitem.expectedQty;
outs.OutstandingItems.Add(outitem);
outlist.Add(outs);
}
}
}
}
return(outlist);
}
public string ToJson()
{
return("{\"Time\":" +
new JavaScriptSerializer().Serialize(Time) +
",\"Id\":\"" +
Id.ToString() +
"\",\"Security\":\"" +
Security.ToString() +
"\",\"Side\":\"" +
Side.ToString() +
"\",\"Quantity\":" +
Quantity.ToString() +
",\"Outstanding\":" +
Outstanding.ToString() +
",\"Price\":" +
Price.ToString() +
"}");
}
public List <Outstanding> GetPendingOutstandingsListByItemId(string itemId)
{
List <Outstanding> outstandings = new List <Outstanding>();
SqlConnection conn = connection;
SqlDataReader reader = null;
try
{
conn.Open();
string sql = @"select (select name from Department where id = (select deptid from Employee where id = r.EmployeeID)) as dept,
(ir.NeededQty - ir.ActualQty) as remaining,
r.DateTime as ReqDate,
ir.ItemId,
o.id
from ItemRequest ir , Request r, Outstanding o
where ir.id in (select ItemRequestID from Outstanding where Status = 'Pending')
and ir.RequestID = r.ID
and o.ItemRequestID = ir.ID
and ir.ItemID = '" + itemId + "' order by ReqDate";
SqlCommand command = new SqlCommand(sql, conn);
reader = command.ExecuteReader();
while (reader.Read())
{
Outstanding o = new Outstanding();
o.Id = (int)reader["id"];
o.ItemId = (string)reader["ItemId"];
o.Dept = (string)reader["dept"];
o.RemainingQty = (int)reader["remaining"];
o.DateTime = (DateTime)reader["reqdate"];
outstandings.Add(o);
}
}
finally
{
if (reader != null)
{
reader.Close();
}
conn.Close();
}
return(outstandings);
}
