public OwnerRequest(StockRequest myRequest, string name, int currentStk, bool stockAvail)
{
request = myRequest;
productName = name;
currentOwnerStock = currentStk;
availability = stockAvail;
}
//Fetch all the stock request from db, and add them to the stockRequest list
//Notice:getStockRequestTable() must be called before displayOwnerRequest()
public static List <StockRequest> getStockRequestTable()
{
string query = "SELECT * FROM StockRequest;";
try
{
var requestTable = dm.fetchData(query, dm.ConnectionString);
foreach (DataRow row in requestTable.Rows)
{
var rID = row["StockRequestID"].ToString(); //throw exception: Column 'StockRequesetID' does not belong to table Table.
var sID = row["StoreID"].ToString();
var pID = row["ProductID"].ToString();
var pQuantity = row["Quantity"].ToString();
StockRequest item = new StockRequest(Int32.Parse(rID), Int32.Parse(sID), Int32.Parse(pID), Int32.Parse(pQuantity));
StockRequest.requestItems.Add(item);
/*
* Console.WriteLine("{0} {1} {2} {3}\n",
* row["StockRequestID"],
* row["StoreID"],
* row["ProductID"],
* row["Quantity"]);*/
}
return(StockRequest.requestItems);
}
catch (Exception e)
{
Console.WriteLine("Exception: {0}", e.Message);
return(null);
}
}
public static void updateStoreInventory(StockRequest newRequest)
{
if (newRequest.process == true)
{
if (StoreItems.Exists(x => x.ProductID == newRequest.prodID))
{
updateStockLevel(newRequest);
}
else if (!StoreItems.Exists(x => x.ProductID == newRequest.prodID))
{
addNewItem(newRequest);
}
}
}
/* !!!! */
//Exception: Violation of PRIMARY KEY constraint 'PK_StoreInventory'.
//Cannot insert duplicate key in object 'dbo.StoreInventory'. The duplicate key value is (5, 2).
//after confirming the request, insert new inventory item to the store's inventory
public static void addNewItem(StockRequest newRequest)
{
string query1 = "INSERT INTO StoreInventory (StoreID, ProductID, StockLevel) Values(@storeID, @productID, @stockLevel);";
try
{
SqlConnection conn = new SqlConnection(dm.ConnectionString);
conn.Open();
SqlCommand cmmd1 = new SqlCommand(query1, conn);
//Parametized SQL
cmmd1.CreateParameter();
cmmd1.Parameters.AddWithValue("storeID", newRequest.storeID);
cmmd1.Parameters.AddWithValue("productID", newRequest.prodID);
cmmd1.Parameters.AddWithValue("stockLevel", 1);
var affectedRow = dm.updateData(cmmd1);
Console.WriteLine("Successfully add new item. {0} row has been inserted", affectedRow);
/*
* foreach (Inventory item in optionalItems)
* {
* //check if the item already in the storeInventory
* if (newRequest.prodID == item.ProductID)
* {
* //after add the new item into the store inventory, delete it from the optionalList
* optionalItems.Remove(item);
* }
*
* }*/
//end of outter filter
conn.Close();
}
catch (Exception e)
{
Console.WriteLine("Exception: {0}", e.Message);
}
}
//The number of rows have been updated:1
public static void updateStockLevel(StockRequest newRequest)
{
var currentStock = 0;
//get the current stock ammount from StoreInventory
foreach (Inventory item in StoreItems)
{
if (item.ProductID == newRequest.prodID)
{
currentStock = item.StockLevel;
}
}
var updateAmount = AddStock(newRequest.requestQuantity, currentStock);
string query2 = "update StoreInventory set StockLevel = @updateStock where StoreID = @storeID AND ProductID = @productID;";
try{
SqlConnection conn = new SqlConnection(dm.ConnectionString);
conn.Open();
SqlCommand cmmd2 = new SqlCommand(query2, conn);
cmmd2.CreateParameter();
cmmd2.Parameters.AddWithValue("storeID", newRequest.storeID);
cmmd2.Parameters.AddWithValue("productID", newRequest.prodID);
cmmd2.Parameters.AddWithValue("updateStock", updateAmount);
var affectedRow = dm.updateData(cmmd2);
Console.WriteLine("Successfully update. {0} row has been changed", affectedRow);
}catch (Exception e)
{
Console.WriteLine("Exception: {0}", e.Message);
}
}