/*
* @param storeID to check
* @param quantity threshold to get items below
* @returns table of productID, product name, and stocklevel below that threshold in that store
*/
public List <List <String> > GetStockBelowThreshold(int id, int threshold)
{
if (threshold < 1)
{
throw new Exception("Threshold can not be 0 or lower!");
}
var sql = new SQLController();
var content = new List <List <string> >();
var data = sql.ExecuteDataRequest($@"SELECT StoreInventory.StoreID, StoreInventory.ProductID as ID, Product.Name as Product, StoreInventory.StockLevel as StockLevel
FROM StoreInventory
JOIN Product ON Product.ProductID = StoreInventory.ProductID
WHERE StoreInventory.StoreID = {id} AND StockLevel < { threshold }");
int i = 0;
foreach (var x in data.Select())
{
content.Add(new List <string> {
x["ID"].ToString(), x["Product"].ToString(), x["StockLevel"].ToString()
});
i++;
}
if (i < 1)
{
throw new Exception("No products are below the threshold.");
}
return(content);
}
/*
* @param storeID
* @param productID
* Creates a request of 1 quantity of the product with id productID, the request is from storeid. This method checks first that this store does not have that item.
*/
public void AddNewInventoryItem(int storeID, int productID)
{
try
{
// this will throw an exception if productID is not valid
GetProductNameByID(productID);
}
catch (Exception)
{
throw new Exception($"There is no product with an id of {productID}");
}
var sql = new SQLController();
var data = sql.ExecuteDataRequest($@" SELECT OwnerInventory.ProductID
FROM OwnerInventory
WHERE OwnerInventory.ProductID IN
(SELECT ProductID
FROM Product
EXCEPT
SELECT ProductID FROM StoreInventory WHERE StoreInventory.StoreID = {storeID})");
foreach (var x in data.Select())
{
if (productID == (int)x["ProductID"])
{
CreateRequest(storeID, productID, 1);
return;
}
}
throw new Exception("Failed to create a stock request - This store already has that item in stock.");
}
private void AddTable(string startDatum, string slutDatum)
{
var tabel = SQLController.GetDataTable(Query.GetPersonalQuery(startDatum, slutDatum, (int)Common.personalTypes.type2, Common.columnNamesPersonal));
Calculate.AddCalculateColumns(tabel, Common.columnsToAdd);
Calculate.GetErastingValues(startDatum, slutDatum);
this.Page.Controls.Add(HtmlTable.CreateStatisticTable(Common.statistikTabel));
}
public SQLView()
{
InitializeComponent(); if (DesignMode || (Site != null && Site.DesignMode))
{
return;
}
controller = new SQLController();
}
/*
* @param productID to get name from
* @returns string of product name
*/
public string GetProductNameByID(int id)
{
var sql = new SQLController();
var data = sql.ExecuteDataRequest($@"SELECT * FROM Product
WHERE ProductID = {id}");
foreach (var x in data.Select())
{
return(x["Name"].ToString());
}
throw new Exception($"A product of the ID {id} does not exist!");
}
/*
* @param id StoreID to get name from
* @returns name of Store with the same id
*/
public String SelectStore(int id)
{
var sql = new SQLController();
var data = sql.ExecuteDataRequest($"SELECT Name FROM Store WHERE StoreID={id}");
if (data.Rows.Count > 0)
{
DataRow row = data.Rows[0];
return(row["Name"].ToString());
}
throw new Exception("Not a valid ID.");
}
/*
* @returns a table of storeIds and store names
*/
public List <List <String> > GetStores()
{
var sql = new SQLController();
var content = new List <List <string> >();
var data = sql.ExecuteDataRequest(@"SELECT * FROM Store");
foreach (var x in data.Select())
{
content.Add(new List <string> {
x["StoreID"].ToString(), x["Name"].ToString()
});
}
return(content);
}
/*
* @param storeID to create it for
* @param productId to create it for
* @param quantity to request
*/
public void CreateRequest(int storeID, int productID, int quantity)
{
if (quantity < 1)
{
throw new Exception("Quantity can not be be 0 or lower!");
}
var sql = new SQLController();
var rowsAffected = sql.ExecuteNonQueryRequest($@"INSERT INTO StockRequest VALUES({storeID},
{productID},
{quantity})");
if (rowsAffected != 1)
{
throw new Exception("Invalid product ID.");
}
}
/*
* @param id productID in OwnerInventory to be reset to 20
*/
public bool ResetInventoryStock(int id)
{
var sql = new SQLController();
var linesAffected = sql.ExecuteNonQueryRequest($@"UPDATE OwnerInventory
SET StockLevel = 20
WHERE ProductID = {id} AND StockLevel < 20"); // safe because it is an int
if (linesAffected == 1)
{
return(true);
}
else
{
return(false);
}
}
/*
* @returns a table of ProductIDs, Product Names, and StockLevels from OwnerInventory
*/
public List <List <string> > GetOwnerInventory()
{
var sql = new SQLController();
var content = new List <List <string> >();
var data = sql.ExecuteDataRequest(@"SELECT OwnerInventory.ProductID as ID, Product.Name as Product, OwnerInventory.StockLevel
FROM OwnerInventory
JOIN Product ON Product.ProductID = OwnerInventory.ProductID");
foreach (var x in data.Select())
{
content.Add(new List <string> {
x["ID"].ToString(), x["Product"].ToString(), x["StockLevel"].ToString()
});
}
return(content);
}
/*
* @param id StoreID to retrieve from
* @return Table of ProductID, Product Name, and Stocklevel from a specific store
*/
public List <List <String> > GetStoreInventory(int id)
{
var sql = new SQLController();
var content = new List <List <string> >();
var data = sql.ExecuteDataRequest($@"SELECT StoreInventory.StoreID, StoreInventory.ProductID as ID, Product.Name as Product, StoreInventory.StockLevel as StockLevel
FROM StoreInventory
JOIN Product ON Product.ProductID = StoreInventory.ProductID
WHERE StoreInventory.StoreID = {id}");
foreach (var x in data.Select())
{
content.Add(new List <string> {
x["ID"].ToString(), x["Product"].ToString(), x["StockLevel"].ToString()
});
}
return(content);
}
/*
* @param storeid to buy from
* @param productID that is being bought
* @param quantity to buy of that productid
*
*/
public void BuyProduct(int store, int productID, int quantity)
{
if (quantity < 1)
{
throw new Exception("Quantity can not be 0 or lower. That wouldn't make sense!");
}
var sql = new SQLController();
var content = new List <List <string> >();
var rowsAffected = sql.ExecuteNonQueryRequest($@"UPDATE StoreInventory
SET StockLevel = StockLevel - {quantity}
WHERE ProductID = {productID} AND StoreID = {store} AND StockLevel >= {quantity}");
if (rowsAffected != 1)
{
throw new Exception($"There is not enough stock for this quantity of {GetProductNameByID(productID)}! Sorry about that!");
}
}
/*
* @returns table of StockRequestID, Store Name, Product Name, Quantity and StockLevel
*/
public List <List <string> > GetStockRequests()
{
var sql = new SQLController();
var content = new List <List <string> >();
var data = sql.ExecuteDataRequest(@"SELECT StockRequest.StockRequestID as ID, Store.Name as Store, Product.Name as Product, StockRequest.Quantity as Quantity, OwnerInventory.StockLevel as StockLevel
FROM StockRequest
JOIN Store ON StockRequest.StoreID = Store.StoreID
JOIN Product ON StockRequest.ProductID = Product.ProductID
JOIN OwnerInventory ON StockRequest.ProductID = OwnerInventory.ProductID; ");
foreach (var x in data.Select())
{
content.Add(new List <string> {
x["ID"].ToString(), x["Store"].ToString(), x["Product"].ToString(), x["Quantity"].ToString(), x["StockLevel"].ToString()
});
}
return(content);
}
/*
* @param id Store to retrieve items not in stock from
* @returns table of products that are not stocked by the store
*/
public List <List <String> > GetItemsNotInStock(int id)
{
var sql = new SQLController();
var content = new List <List <string> >();
var data = sql.ExecuteDataRequest($@" SELECT OwnerInventory.ProductID, Product.Name, OwnerInventory.StockLevel
FROM OwnerInventory
INNER JOIN Product ON OwnerInventory.ProductID = Product.ProductID
WHERE OwnerInventory.ProductID IN
(SELECT ProductID
FROM Product
EXCEPT
SELECT ProductID FROM StoreInventory WHERE StoreInventory.StoreID = {id}) ");
foreach (var x in data.Select())
{
content.Add(new List <string> {
x["ProductID"].ToString(), x["Name"].ToString(), x["StockLevel"].ToString()
});
}
return(content);
}
public void hulIgennemTilIdentityDB()
{
string before = "selectScript() er kørt, følgende fundet:\n" + GetTimestamp(DateTime.Now) + "\n";
SQLController controller = new SQLController();
SQLmodel sqlModel = new SQLmodel();
sqlModel.sqlId = 42;
sqlModel.sqlResult = "";
sqlModel.sqlScript = "select current_timestamp";
ViewResult result = controller.Index(sqlModel) as ViewResult;
Assert.IsNotNull(result);
string res = ((SQLmodel)(result.Model)).sqlResult;
#if DEBUG
string after = "selectScript() er kørt, følgende fundet:\n" + GetTimestamp(DateTime.Now) + "\n";
int c0 = before.CompareTo(res);
int c1 = res.CompareTo(after);
Assert.IsTrue(c0 <= 0, "c0 fejl " + c0 + "" + before + " " + after);
Assert.IsTrue(c1 <= 0, "c1 fejl " + c1 + " " + res + " " + after);
#else
Assert.IsTrue(res.Length == 0, "Not debug. Result>>" + result + "<<");
#endif
}
public void CreateNewObject()
{
sqlController = new SQLController();
date1 = new DateTime(2008, 12, 1, 18, 30, 52);
date2 = DateTime.Now;
}
public MainService()
{
this.repository = new SQLController();
}
public bool ProcessStockRequest(int id)
{
var sql = new SQLController();
int quantity = -1, stocklevel = -1, productID = -1, storeID = -1, stockrequestID = -1;
using (var data = sql.ExecuteDataRequest($@"SELECT StockRequest.StockRequestID as ID, Store.StoreID as StoreID, Product.ProductID as ProductID, StockRequest.Quantity as Quantity, OwnerInventory.StockLevel as StockLevel
FROM StockRequest
JOIN Store ON StockRequest.StoreID = Store.StoreID
JOIN Product ON StockRequest.ProductID = Product.ProductID
JOIN OwnerInventory ON StockRequest.ProductID = OwnerInventory.ProductID
WHERE StockRequestID = {id}; ")) // safe because it has to be an int
{
try
{
if (data.Rows.Count > 0)
{
DataRow row = data.Rows[0];
quantity = (int)row["Quantity"];
stocklevel = (int)row["StockLevel"];
productID = (int)row["ProductID"];
stockrequestID = (int)row["ID"];
storeID = (int)row["StoreID"];
}
if (quantity == -1)
{
// checks if everything was found
throw new Exception("Something went wrong when reading the data from SQL.");
}
if (quantity > stocklevel) // not enough stock
{
return(false);
}
var newOwnerStock = (int)stocklevel - (int)quantity;
// change stock level in ownerinventory
sql.ExecuteNonQueryRequest($@"UPDATE OwnerInventory
SET OwnerInventory.StockLevel = OwnerInventory.StockLevel - {quantity}
WHERE OwnerInventory.ProductID = {productID}
");
// change stock level in store inventory (if theres 0 stock it creates a new record instead)
sql.ExecuteNonQueryRequest($@"IF EXISTS (SELECT * FROM StoreInventory
WHERE (StoreInventory.ProductID = {productID} AND StoreInventory.StoreID = {storeID}) )
UPDATE StoreInventory
SET StoreInventory.StockLevel = StoreInventory.StockLevel + {quantity}
WHERE (StoreInventory.ProductID = {productID} AND StoreInventory.StoreID = {storeID})
ELSE
INSERT INTO StoreInventory VALUES({storeID}, {productID}, {quantity})");
// removes stockrequest once complete
sql.ExecuteNonQueryRequest($@"DELETE FROM StockRequest
WHERE StockRequest.StockRequestID = {stockrequestID}");
return(true);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(false);
}
}
}
