private int GetAdjustmentSum(Item i)
{
Discrepency dMonthly = EFBroker_Discrepancy.GetPendingMonthlyDiscrepancyByItemCode(i.ItemCode);
List <Discrepency> dList = EFBroker_Discrepancy.GetPendingDiscrepanciesByItemCode(i.ItemCode);
int adjustment = 0;
if (dMonthly == null)
{
foreach (Discrepency d in dList)
{
adjustment += (int)d.AdjustmentQty;
}
}
else
{
adjustment = (int)dMonthly.AdjustmentQty;
foreach (Discrepency d in dList)
{
if ((int)d.DiscrepencyID > dMonthly.DiscrepencyID)
{
adjustment += (int)d.AdjustmentQty;
}
}
}
return(adjustment);
}
public List <Discrepency> GetDiscrepencies()
{
List <Discrepency> discrepancies = new List <Discrepency>();
using (SqlConnection connection = new SqlConnection(DataLink.connectionString))
{
connection.Open();
string cmdquery = " select s.ItemID, d.DiscrepencyID,d.DiscrepancyQty,d.Reason,d.DiscrepancyStatus , s.ItemName , s.Price* d.DiscrepancyQty as Amount" +
" from Discrepancy d, Stationery s" +
" where d.ItemID = s.ItemID";
SqlCommand cmd = new SqlCommand(cmdquery, connection);
//Call Execute reader to get query results
SqlDataReader reader = cmd.ExecuteReader();
//Print out each record
while (reader.Read())
{
Discrepency s = new Discrepency();
s.ItemID = (string)reader["ItemID"];
s.DiscrepencyID = (int)reader["DiscrepencyID"];
s.DiscrepancyQty = (int)reader["DiscrepancyQty"];
s.Reason = (string)reader["Reason"];
s.DiscrepancyStatus = (string)reader["DiscrepancyStatus"];
s.ItemName = (string)reader["ItemName"];
s.Amount = (int)reader["Amount"];
discrepancies.Add(s);
}
return(discrepancies);
}
}
public List <Discrepency> GetAdjustmentDetails(int id)
{
List <Discrepency> adjust = new List <Discrepency>();
using (SqlConnection connection = new SqlConnection(DataLink.connectionString))
{
connection.Open();
string cmdquery = "select d.DiscrepancyStatus,d.DiscrepencyID,s.ItemName,d.Reason,d.DiscrepancyQty,s.Price*d.DiscrepancyQty as Amount from Stationery s,Discrepancy d, DisbursementDetails dd,DisbursementList dl where dd.DisbursementID = dl.DisbursementID and dd.ItemID = s.ItemID and d.DisbursementID = dl.DisbursementID and d.DiscrepencyID ='" + id + "' group by s.ItemName,d.Reason,d.DiscrepancyQty,s.Price,d.DiscrepancyStatus,d.DiscrepencyID";
SqlCommand cmd = new SqlCommand(cmdquery, connection);
//Call Execute reader to get query results
SqlDataReader reader = cmd.ExecuteReader();
//Print out each record
while (reader.Read())
{
Discrepency s = new Discrepency();
s.DiscrepancyStatus = (string)reader["DiscrepancyStatus"];
s.DiscrepencyID = (int)reader["DiscrepencyID"];
s.ItemName = (string)reader["ItemName"];
s.Reason = (string)reader["Reason"];
s.DiscrepancyQty = (int)reader["DiscrepancyQty"];
s.Amount = (int)reader["Amount"];
adjust.Add(s);
}
return(adjust);
}
}
public ActionResult UpdateStatus(Discrepency d)
{
int id = d.DiscrepencyID;
int qty = d.DiscrepancyQty;
AdjustmentService adjust = new AdjustmentService();
adjust.UpdateStatus(d);
return(RedirectToAction("UpdateAdjustmentStatus", "Adjustment"));
}
public static void ProcessDiscrepancy(int id, string action)
{
using (StationeryEntities context = new StationeryEntities())
{
Discrepency d = context.Discrepencies.Where(x => x.DiscrepencyID == id).First();
d.Status = action;
context.SaveChanges();
}
}
public static int GetDiscrepancyID(Discrepency d)
{
int id;
using (StationeryEntities context = new StationeryEntities())
{
id = context.Discrepencies.Where(x => x.ItemCode == d.ItemCode && x.RequestedBy == d.RequestedBy && x.Date == d.Date && x.AdjustmentQty == d.AdjustmentQty && x.Remarks == d.Remarks).Select(x => x.DiscrepencyID).FirstOrDefault();
}
return(id);
}
protected void BtnProcess_Click(object sender, EventArgs e)
{
try
{
foreach (GridViewRow row in gvActionSummary.Rows)
{
int discID = Int32.Parse((row.FindControl("lblDiscID") as Label).Text);
string action = (row.FindControl("lblAction") as Label).Text;
Discrepency d = EFBroker_Discrepancy.GetDiscrepancyById(discID);
if (d.Status == "Monthly")
{
if (action == "Approved")
{
List <Discrepency> dList = EFBroker_Discrepancy.GetPendingDiscrepanciesByItemCode(d.ItemCode);
foreach (Discrepency d2 in dList)
{
if (d2.DiscrepencyID < d.DiscrepencyID)
{ //Negating discrepancies reported before the monthly discrepancy after it is approved
EFBroker_Discrepancy.ProcessDiscrepancy(d2.DiscrepencyID, "Resolved");
}
}
}
}
EFBroker_Discrepancy.ProcessDiscrepancy(discID, action);
if (action == "Approved")
{ //only update stock card and item tables if discrepancy is approved
StockCard sc = new StockCard();
StockCard lastEntry = EFBroker_StockCard.GetStockCardsByItemCode(d.ItemCode).Last();
sc.ItemCode = d.ItemCode;
sc.TransactionType = "Adjustment";
sc.Qty = d.AdjustmentQty;
sc.Balance = lastEntry.Balance + d.AdjustmentQty;
sc.TransactionDetailID = d.DiscrepencyID;
EFBroker_StockCard.ResolveDiscrepancy(sc, sc.ItemCode, (int)sc.Balance);
}
}
Session["discrepancySummary"] = null;
Utility.AlertMessageThenRedirect("Adjustments processed", "StockAdjustment.aspx");
}
catch (Exception ex)
{
Session["discrepancySummary"] = null;
Utility.AlertMessageThenRedirect("Failed to process adjustments, please try again", "StockAdjustment.aspx");
}
}
public void UpdateStatus(Discrepency d)
{
Discrepency discrepancy = new Discrepency();
using (SqlConnection connection = new SqlConnection(DataLink.connectionString))
{
connection.Open();
string cmdquery = "update Discrepancy set DiscrepancyStatus='approved' where DiscrepencyID='" + d.DiscrepencyID + "'";
SqlCommand cmd = new SqlCommand(cmdquery, connection);
cmd.ExecuteNonQuery();
}
}
public int GetDiscrepancyID()
{
Discrepency reqInfo = new Discrepency();
using (SqlConnection conn = new SqlConnection(DataLink.connectionString))
{
conn.Open();
string cmdtext = @" select d.DiscrepencyID
from DisbursementDetails dd, Discrepancy d
where dd.DisbursementID = d.DisbursementID and dd.ItemID = d.ItemID
and d.DiscrepancyStatus='PendingForApproval'";
SqlCommand cmd = new SqlCommand(cmdtext, conn);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
reqInfo.DiscrepencyID = (int)reader["DiscrepencyID"];
}
}
int DiscrepencyID = reqInfo.DiscrepencyID;
return(DiscrepencyID);
}
private void ProcessApprovalAndRejections(GridView gdv, Dictionary <KeyValuePair <Discrepency, Item>, String> summary)
{
foreach (GridViewRow row in gdv.Rows)
{
RadioButtonList rbl = row.FindControl("rblAction") as RadioButtonList;
if (rbl.SelectedIndex == 0 || rbl.SelectedIndex == 1)
{
string itemCode = (row.FindControl("lblItemCode") as Label).Text;
int discID = Int32.Parse((row.FindControl("lblDiscID") as Label).Text);
Item i = EFBroker_Item.GetItembyItemCode(itemCode);
Discrepency d = EFBroker_Discrepancy.GetDiscrepancyById(discID);
KeyValuePair <Discrepency, Item> kvp = new KeyValuePair <Discrepency, Item>(d, i);
if (rbl.SelectedIndex == 0)
{
summary.Add(kvp, "Approved");
}
else if (rbl.SelectedIndex == 1)
{
summary.Add(kvp, "Rejected");
}
}
}
}
private void ShowAll()
{
List <Item> iList = new List <Item>();
iList = EFBroker_Item.GetActiveItemList();
Dictionary <Item, String> displayItems = new Dictionary <Item, String>();
foreach (Item i in iList)
{
//If a monthly inventory check discrepancy is not yet approved, the sum of only
//discrepancies starting from the monthly one will be displayed
Discrepency dMonthly = EFBroker_Discrepancy.GetPendingMonthlyDiscrepancyByItemCode(i.ItemCode);
List <Discrepency> dList = EFBroker_Discrepancy.GetPendingDiscrepanciesByItemCode(i.ItemCode);
if (dMonthly == null)
{
string adjStr = GetAdjustmentsString(dList);
displayItems.Add(i, adjStr);
}
else
{
string adjStr = GetPartialAdjustmentsString(dList, dMonthly);
displayItems.Add(i, adjStr);
}
}
gvItemList.DataSource = displayItems;
gvItemList.DataBind();
foreach (GridViewRow row in gvItemList.Rows)
{
HyperLink link = row.FindControl("hlkDesc") as HyperLink;
Label lbl = row.FindControl("lblItemCodeItem") as Label;
string itemCode = lbl.Text;
link.NavigateUrl = LoginController.AddItemDiscrepancyURI + "?itemCode=" + itemCode;
}
}
private string GetPartialAdjustmentsString(List <Discrepency> dList, Discrepency dMonthly)
{
int adj = (int)dMonthly.AdjustmentQty;
foreach (Discrepency d in dList)
{
if (d.DiscrepencyID > dMonthly.DiscrepencyID)
{
adj += (int)d.AdjustmentQty;;
}
}
string adjStr = "";
if (adj > 0)
{
adjStr = "+" + adj.ToString();
}
else
{
adjStr = adj.ToString();
}
return(adjStr);
}
protected void BtnSearch_Click(object sender, EventArgs e)
{ //Search button
Dictionary <Item, String> searchResults = new Dictionary <Item, String>();
string search = txtSearch.Text.ToLower();
List <Item> iList = EFBroker_Item.SearchItemsByItemCodeOrDesc(search);
foreach (Item i in iList)
{
//If a monthly inventory check discrepancy is not yet approved, the sum of only
//discrepancies starting from the monthly one will be displayed
Discrepency dMonthly = EFBroker_Discrepancy.GetPendingMonthlyDiscrepancyByItemCode(i.ItemCode);
List <Discrepency> dList = EFBroker_Discrepancy.GetPendingDiscrepanciesByItemCode(i.ItemCode);
if (dMonthly == null)
{
string adjStr = GetAdjustmentsString(dList);
searchResults.Add(i, adjStr);
}
else
{
string adjStr = GetPartialAdjustmentsString(dList, dMonthly);
searchResults.Add(i, adjStr);
}
}
gvItemList.DataSource = searchResults;
gvItemList.DataBind();
foreach (GridViewRow row in gvItemList.Rows)
{
HyperLink link = row.FindControl("hlkDesc") as HyperLink;
Label lbl = row.FindControl("lblItemCodeItem") as Label;
string itemCode = lbl.Text;
link.NavigateUrl = LoginController.AddItemDiscrepancyURI + "?itemCode=" + itemCode;
}
}
public bool SubmitDiscrepanciesWithItemUpdate(List <WCFDiscrepancy> wdList)
{
List <Discrepency> dList = new List <Discrepency>();
bool informSupervisor = false;
bool informManager = false;
foreach (WCFDiscrepancy wd in wdList)
{
Discrepency d = new Discrepency();
d.ItemCode = wd.ItemCode;
d.RequestedBy = Int32.Parse(wd.RequestedBy);
d.AdjustmentQty = Int32.Parse(wd.AdjustmentQty);
d.Remarks = wd.Remarks;
d.Status = wd.Status;
d.Date = DateTime.Now;
//Code to determine the discrepancy amount
List <PriceList> plHistory = EFBroker_PriceList.GetPriceListByItemCode(d.ItemCode);
List <PriceList> itemPrices = new List <PriceList>();
foreach (PriceList pl in plHistory)
{ //Get only currently active suppliers for an item
if (pl.TenderYear == DateTime.Now.Year.ToString())
{
itemPrices.Add(pl);
}
}
decimal totalPrice = 0;
foreach (PriceList pl in itemPrices)
{
totalPrice += (decimal)pl.Price;
}
decimal averageUnitPrice = totalPrice / itemPrices.Count;
d.TotalDiscrepencyAmount = d.AdjustmentQty * averageUnitPrice;
//Set the approver based on discrepancy amount, and email notify them
if (Math.Abs((decimal)d.TotalDiscrepencyAmount) < 250)
{
if (d.TotalDiscrepencyAmount < 250)
{
d.ApprovedBy = EFBroker_DeptEmployee.GetEmployeeListByRole("Store Supervisor")[0].EmpID;
informSupervisor = true;
}
else
{
d.ApprovedBy = EFBroker_DeptEmployee.GetEmployeeListByRole("Store Manager")[0].EmpID;
informManager = true;
}
}
dList.Add(d);
}
bool successfullySent = sendDiscrepanciesToDatabaseWithItemUpdates(dList);
if (successfullySent)
{
if (informSupervisor)
{
string supervisorEmail = EFBroker_DeptEmployee.GetEmployeeListByRole("Store Supervisor")[0].Email;
Utility.sendMail(supervisorEmail, "New Discrepancies Notification " + DateTime.Now.ToString(), "New item discrepancies have been submitted. Please log in to the system to review them. Thank you.");
}
if (informManager)
{
string managerEmail = EFBroker_DeptEmployee.GetEmployeeListByRole("Store Manager")[0].Email;
Utility.sendMail(managerEmail, "New Discrepancies Notification " + DateTime.Now.ToString(), "New item discrepancies (worth at least $250) have been submitted. Please log in to the system to review them. Thank you.");
}
return(true);
}
else
{
return(false);
}
}
protected void BtnSubmit_Click(object sender, EventArgs e)
{
List <Discrepency> dList = new List <Discrepency>();
bool complete = true;
bool monthly = false;
foreach (GridViewRow row in gvDiscrepancies.Rows)
{
string itemCode = (row.FindControl("lblItemCode") as Label).Text;
string stock = (row.FindControl("lblStock") as Label).Text;
string actual = (row.FindControl("lblActual") as Label).Text;
int adj = Int32.Parse(actual) - Int32.Parse(stock);
string remarks = (row.FindControl("txtRemarks") as TextBox).Text;
if (!ValidatorUtil.isEmpty(remarks))
{
if (remarks.Length <= maxChars)
{
//update item table if any adjustment at disubrsement point
if (Session["ItemToUpdate"] != null)
{
if ((bool)Session["ItemToUpdate"])
{
Item i = EFBroker_Item.GetItembyItemCode(itemCode);
i.BalanceQty = (adj * -1) + i.BalanceQty;
EFBroker_Item.UpdateItem(i);
}
}
List <PriceList> plHistory = EFBroker_PriceList.GetPriceListByItemCode(itemCode);
List <PriceList> itemPrices = new List <PriceList>();
foreach (PriceList pl in plHistory)
{ //Get only currently active suppliers for an item
if (pl.TenderYear == DateTime.Now.Year.ToString())
{
itemPrices.Add(pl);
}
}
decimal totalPrice = 0;
foreach (PriceList pl in itemPrices)
{
totalPrice += (decimal)pl.Price;
}
decimal averageUnitPrice = totalPrice / itemPrices.Count;
Discrepency d = new Discrepency();
d.ItemCode = itemCode;
if (Session["empID"] != null)
{
int empID = (int)Session["empID"];
d.RequestedBy = empID;
}
else
{
Utility.logout();
}
d.AdjustmentQty = adj;
d.Remarks = remarks;
d.Date = DateTime.Now;
if (Session["monthly"] != null)
{
if ((bool)Session["monthly"] == true)
{
d.Status = "Monthly";
monthly = true;
}
else
{
d.Status = "Pending";
monthly = false;
}
}
else
{
d.Status = "Pending";
}
d.TotalDiscrepencyAmount = adj * averageUnitPrice;
if (d.TotalDiscrepencyAmount < 250)
{
d.ApprovedBy = EFBroker_DeptEmployee.GetEmployeeListByRole("Store Supervisor")[0].EmpID;
}
else
{
d.ApprovedBy = EFBroker_DeptEmployee.GetEmployeeListByRole("Store Manager")[0].EmpID;
}
dList.Add(d);
}
else
{
lblErrorCharLimit.Text = "Make sure all remarks are under 100 characters long";
row.BackColor = Color.Yellow;
complete = false;
break;
}
}
else
{
lblRequired.Text = "Please state the cause of discrepancies for all items in Remarks";
complete = false;
break;
}
}
if (complete)
{
if (monthly)
{
EFBroker_Discrepancy.MonthlyInventoryCheck(dList);
}
else
{
EFBroker_Discrepancy.SaveDiscrepencies(dList);
}
//bool informSupervisor = false;
//bool informManager = false;
informSupervisor = false;
informManager = false;
foreach (Discrepency d in dList)
{
if (Math.Abs((decimal)d.TotalDiscrepencyAmount) < 250)
{
informSupervisor = true;
}
else
{
informManager = true;
}
}
Session["discrepancyList"] = null;
Session["discrepancyDisplay"] = null;
Session["RetrievalShortfallItemList"] = null;
Session["RetrievalID"] = null;
Session["monthly"] = null;
Session["ItemToUpdate"] = null;
ThreadStart emailThreadStart = new ThreadStart(DiscrepancyMailNotification);
Thread emailThread = new Thread(emailThreadStart);
emailThread.Start();
string destination = "";
if (Session["empRole"] != null)
{
string role = (string)Session["empRole"];
if (role == "Store Supervisor" || role == "Store Manager")
{
destination = "PurchaseOrderList.aspx";
}
else if (role == "Store Clerk")
{
destination = "RequisitionListClerk.aspx";
}
else
{
Utility.logout();
}
}
else
{
Utility.logout();
}
Utility.AlertMessageThenRedirect("Discrepancies successfully reported", destination);
}
}
protected void Page_Load(object sender, EventArgs e)
{
string itemCode = Request.QueryString["itemCode"];
string adjustment = "Adjustment"; //strings for stock card transaction type
string disbursement = "Disbursement";
string purchase = "Purchase";
if (!ValidatorUtil.isEmpty(itemCode))
{
Item item = EFBroker_Item.GetItembyItemCode(itemCode);
string currentYear = DateTime.Now.Year.ToString();
List <PriceList> plList = EFBroker_PriceList.GetPriceListByItemCode(itemCode).Where(c => c.TenderYear == currentYear).ToList();
if (item != null && plList.Count > 0)
{
lblItemCode.Text = item.ItemCode;
lblItemName.Text = item.Description;
lblBin.Text = item.Bin;
lblUom.Text = item.UnitOfMeasure;
foreach (PriceList pl in plList)
{
switch (pl.SupplierRank)
{
case 1:
lblSupp1.Text = pl.SupplierCode;
break;
case 2:
lblSupp2.Text = pl.SupplierCode;
break;
case 3:
lblSupp3.Text = pl.SupplierCode;
break;
}
}
if (plList.Count < 3) // N/A on supplier labels if there are less than 3 suppliers
{
lblSupp3.Text = "N/A";
if (plList.Count < 2)
{
lblSupp2.Text = "N/A";
}
}
List <StockCard> scList = EFBroker_StockCard.GetStockCardsByItemCode(itemCode);
List <StockCardDisplayRow> scDisplayList = new List <StockCardDisplayRow>();
foreach (StockCard sc in scList)
{
if (sc.TransactionType == adjustment || sc.TransactionType == disbursement || sc.TransactionType == purchase)
{
//Possible to display 3 types of stock card entries (each accessing different tables)
StockCardDisplayRow scdr = new StockCardDisplayRow();
if (sc.TransactionType == adjustment)
{
Discrepency d = EFBroker_Discrepancy.GetDiscrepancyById((int)sc.TransactionDetailID);
scdr.TransDate = ((DateTime)d.Date).ToShortDateString();
scdr.TransDetails = "Adjustment ID. " + sc.TransactionDetailID;
scdr.Quantity = "ADJ " + GetQuantityString((int)sc.Qty);
}
else if (sc.TransactionType == purchase)
{
PurchaseOrder po = EFBroker_PurchaseOrder.GetPurchaseOrderById((int)sc.TransactionDetailID);
scdr.TransDate = ((DateTime)po.ExpectedDate).ToShortDateString();
scdr.TransDetails = "Supplier - " + po.SupplierCode;
Item_PurchaseOrder ipo = EFBroker_PurchaseOrder.GetPurchaseOrderItem(po.PurchaseOrderID, itemCode);
scdr.Quantity = GetQuantityString((int)sc.Qty);
}
else if (sc.TransactionType == disbursement)
{
Disbursement db = EFBroker_Disbursement.GetDisbursmentbyDisbID((int)sc.TransactionDetailID);
scdr.TransDate = ((DateTime)db.CollectionDate).ToShortDateString();
scdr.TransDetails = EFBroker_DeptEmployee.GetDepartByDepCode(db.DeptCode).DeptName;
scdr.Quantity = GetQuantityString((int)sc.Qty);
}
scdr.Balance = (int)sc.Balance;
scDisplayList.Add(scdr);
}
}
gvTransactions.DataSource = scDisplayList;
gvTransactions.DataBind();
}
else //if item is not found or no entries found in price list table
{
Response.Redirect(LoginController.StationeryCatalogueURI);
}
}
else //if there is no itemCode in querystring
{
Response.Redirect(LoginController.StationeryCatalogueURI);
}
}
