public void RemoveDepartmentToProduct(ProductDepartment productDepartment)
{
try
{
var productDepartmentToRemove = _productContext.ProductDepartment
.Where(pd => pd.DepartmentId == productDepartment.DepartmentId && pd.ProductId == productDepartment.ProductId).FirstOrDefault();
if (productDepartmentToRemove == null)
{
throw new Exception("ProductDepartment não encontrado.");
}
_productContext.ProductDepartment.Remove(productDepartmentToRemove);
_productContext.SaveChanges();
}
catch (Exception ex)
{
if (ex.InnerException != null)
{
throw ex.InnerException;
}
else
{
throw ex;
}
}
}
public IEnumerable <Product> GetProductsByCategory(string category, ProductDepartment department, int skip, int take)
{
using (var context = new TBStockDBContext())
{
return(context.Products.Where(x => x.Category == category && x.DepartmentId == (int)department).OrderByDescending(x => x.Id).Skip(skip).Take(take).ToList());
}
}
public void GetNonExistingDepartmentTest()
{
DepartmentController dc = new DepartmentController();
ProductDepartment dept = dc.GetDepartment("Not existing");
Assert.IsNull(dept);
}
public void CreateProductDepartment()
{
ProductDepartment dept = new ProductDepartment(12, "Test department");
Assert.IsNotNull(dept);
Assert.AreEqual(12, dept.Id);
Assert.AreEqual("Test department", dept.DptName);
}
public void GetStocksTest()
{
StockController sc = new StockController();
try
{
string sql = "SELECT * FROM test_products;";
MySqlCommand cmd = new MySqlCommand(sql, sc.connection);
sc.connection.Open();
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int id = Convert.ToInt32(reader["id"]);
string name = Convert.ToString(reader["name"]);
string description = Convert.ToString(reader["description"]);
double price = Convert.ToDouble(reader["price"]);
double supplyCost = Convert.ToDouble(reader["supply_cost"]);
DateTime dateOfArrival = Convert.ToDateTime(reader["date_of_arrival"]);
int quantity = Convert.ToInt32(reader["quantity"]);
int restockQuantity = Convert.ToInt32(reader["restock_quantity"]);
int timesSold = Convert.ToInt32(reader["times_sold"]);
bool requested = Convert.ToBoolean(reader["reqested"]);
ProductDepartment dep = null;
foreach (ProductDepartment d in dc.GetDepartments())
{
if (d.Id == Convert.ToInt32(reader["department"]))
{
dep = d;
}
}
sc.Stocks.Add(new Stock(id, name, description, price, supplyCost, dateOfArrival, quantity, timesSold, restockQuantity, requested, dep));
Stock stock = new Stock(testId, testName, testDescription, testPrice, testSupplyCost, testDateOfArrival, testQuantity, testTimesSold, testRestockQuantity, testRequested, testDepartment);
Stock stock2 = new Stock(2, testName, testDescription, testPrice, testSupplyCost, testDateOfArrival, testQuantity, testTimesSold, testRestockQuantity, testRequested, testDepartment);
CollectionAssert.AreEquivalent(new List <Stock> {
stock, stock2
}, sc.Stocks);
}
}
catch (MySqlException)
{
throw new Exception("Fail");
}
catch (Exception)
{
}
finally
{
if (sc.connection != null)
{
sc.connection.Close();
}
}
}
public StockRequest(int id, string name, ProductDepartment department, DateTime dateofrequest, int restockquantity, bool completed)
{
this.Id = id;
this.Completed = completed;
this.DateOfRequest = dateofrequest;
base.Name = name;
base.Department = department;
base.RestockQuantity = restockquantity;
}
public IActionResult AddDepartmentToProduct([FromBody] ProductDepartment productDeparment)
{
try
{
_productData.AddDepartmentInProduct(productDeparment);
return(Ok());
}
catch (Exception ex)
{
return(Problem(ex.Message, nameof(AddDepartmentToProduct), StatusCodes.Status500InternalServerError, "Fail adding the department in product."));
}
}
public bool AddStock(string name, string description, double price, double supplyCost, DateTime dateOfArrival,
int quantity, int timesSold, int restockQuantity,
bool requested, ProductDepartment department)
{
if (Stocks.Any(s => s.Name.Equals(name)))
{
return(false);
}
try
{
using (connection)
{
base.connection.Open();
string sql = "INSERT INTO products (name, description, supply_cost, price, department, date_of_arrival, quantity, restock_quantity, times_sold, reqested)" +
"VALUES (@name, @description, @supplyCost, @price, @department, @dateOfArrival, @quantity, @restockQuantity, @timesSold, @requested)";
using (MySqlCommand cmd = new MySqlCommand(sql, base.connection))
{
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@description", description);
cmd.Parameters.AddWithValue("@supplyCost", supplyCost);
cmd.Parameters.AddWithValue("@price", price);
cmd.Parameters.AddWithValue("@department", department.Id);
cmd.Parameters.AddWithValue("@dateOfArrival", dateOfArrival);
cmd.Parameters.AddWithValue("@quantity", quantity);
cmd.Parameters.AddWithValue("@restockQuantity", restockQuantity);
cmd.Parameters.AddWithValue("@timesSold", timesSold);
cmd.Parameters.AddWithValue("@requested", requested);
cmd.ExecuteNonQuery();
return(true);
}
}
}
catch (MySqlException)
{
throw new Exception("Could not connect to the database properly!");
}
catch (Exception)
{
return(false);
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
public void GetStocks()
{
try
{
string sql = "SELECT * FROM products;";
MySqlCommand cmd = new MySqlCommand(sql, base.connection);
base.connection.Open();
MySqlDataReader reader = cmd.ExecuteReader();
Stocks.Clear();
while (reader.Read())
{
int id = Convert.ToInt32(reader["id"]);
string name = Convert.ToString(reader["name"]);
string description = Convert.ToString(reader["description"]);
double price = Convert.ToDouble(reader["price"]);
double supplyCost = Convert.ToDouble(reader["supply_cost"]);
DateTime dateOfArrival = Convert.ToDateTime(reader["date_of_arrival"]);
int quantity = Convert.ToInt32(reader["quantity"]);
int restockQuantity = Convert.ToInt32(reader["restock_quantity"]);
int timesSold = Convert.ToInt32(reader["times_sold"]);
bool requested = Convert.ToBoolean(reader["reqested"]);
ProductDepartment dep = null;
foreach (ProductDepartment d in dc.GetDepartments())
{
if (d.Id == Convert.ToInt32(reader["department"]))
{
dep = d;
}
}
Stocks.Add(new Stock(id, name, description, price, supplyCost, dateOfArrival, quantity, timesSold, restockQuantity, requested, dep));
}
}
catch (MySqlException)
{
throw new Exception("Fail");
}
catch (Exception)
{
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
//Get id and name od department
public bool EditDepartment(string deptName, string newname)
{
ProductDepartment dept = this.GetDepartment(deptName);
if (dept != null)
{
if (EditDepartment(dept.Id, newname))
{
dept.DptName = newname;
return(true);
}
}
return(false);
}
//Get id from department name
public bool DeleteDepartment(string deptName)
{
ProductDepartment dept = this.GetDepartment(deptName);
if (dept != null)
{
if (DeleteDepartment(dept.Id))
{
departments.Remove(dept);
return(true);
}
}
return(false);
}
public void TestInitialize()
{
dc = new DepartmentController();
testId = 1;
testName = "Test";
testDescription = "Test";
testPrice = 10;
testSupplyCost = 10;
testDateOfArrival = new DateTime(2020, 1, 1);
testQuantity = 10;
testRestockQuantity = 10;
testTimesSold = 10;
testRequested = false;
testDepartment = new ProductDepartment(1, "TVs");
}
public Stock(int id, string name, string description, double price, double supplyCost, DateTime dateOfArrival,
int quantity, int timesSold, int restockQuantity,
bool requested, ProductDepartment department)
{
this.id = id;
this.name = name;
this.description = description;
this.price = price;
this.supplyCost = supplyCost;
this.dateOfArrival = dateOfArrival;
this.quantity = quantity;
this.timesSold = timesSold;
this.restockQuantity = restockQuantity;
this.requested = requested;
this.dep = department;
}
public bool EditStock(int id, string name, string description, double price, double supplyCost, DateTime dateOfArrival,
int quantity, int timesSold, int restockQuantity,
bool requested, ProductDepartment department)
{
try
{
using (connection)
{
string sql = "UPDATE products SET name = @name, description = @description, supply_cost = @supplyCost, price = @price, date_of_arrival = @dateOfArrival, " +
"quantity = @quantity, times_sold = @timesSold, restock_quantity = @restockQuantity, reqested = @requested, department = @department WHERE id = @id;";
using (MySqlCommand cmd = new MySqlCommand(sql, base.connection))
{
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@description", description);
cmd.Parameters.AddWithValue("@supplyCost", supplyCost);
cmd.Parameters.AddWithValue("@price", price);
cmd.Parameters.AddWithValue("@dateOfArrival", dateOfArrival);
cmd.Parameters.AddWithValue("@quantity", quantity);
cmd.Parameters.AddWithValue("@timesSold", timesSold);
cmd.Parameters.AddWithValue("@restockQuantity", restockQuantity);
cmd.Parameters.AddWithValue("@requested", requested);
cmd.Parameters.AddWithValue("@department", department.Id);
base.connection.Open();
cmd.ExecuteNonQuery();
return(true);
}
}
}
catch (MySqlException)
{
throw new Exception("Could not connect to the database properly!");
}
catch (Exception)
{
return(false);
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
private void deleteProduct_Click(object sender, RoutedEventArgs e)
{
ProductDepartment currentProduct = ((Button)(sender)).DataContext as ProductDepartment;
if (currentProduct != null)
{
productsSupplier.Remove(currentProduct);
productsDepartment.Add(currentProduct);
ProductosProveedor prodSupplierToRemove = (from producto in _supplierToSave.productos where producto.idProducto.Equals(currentProduct.idProducto) select producto).FirstOrDefault();
if (prodSupplierToRemove != null && prodSupplierToRemove.idProductoProveedor != 0)
{
_orderPurchasePresenter.DeleteProductoProveedor(prodSupplierToRemove);
}
_supplierToSave.productos.Remove(prodSupplierToRemove);
_supplierToSave.productosWithDepartment = productsSupplier.ToList();
}
}
private void assignBtn_MouseUp(object sender, MouseButtonEventArgs e)
{
ProductDepartment currentProduct = productsDepartmentGrid.SelectedItem as ProductDepartment;
if (currentProduct != null)
{
productsDepartment.Remove(currentProduct);
productsSupplier.Add(currentProduct);
ProductosProveedor prodSupplierToAdd = new ProductosProveedor {
idProducto = currentProduct.idProducto, idProveedor = _supplierToSave.idProveedor, ultimoCosto = 0
};
_supplierToSave.productos.Add(prodSupplierToAdd);
List <ProductDepartment> listProductsSupplier = productsSupplier.ToList();
listProductsSupplier.AddRange(_supplierToSave.productosWithDepartment);
_supplierToSave.productosWithDepartment = listProductsSupplier.ToList();
}
}
public void AddDepartmentInProduct(ProductDepartment productDepartment)
{
try
{
_productContext.ProductDepartment.Add(productDepartment);
_productContext.SaveChanges();
}
catch (Exception ex)
{
if (ex.InnerException != null)
{
throw ex.InnerException;
}
else
{
throw ex;
}
}
}
public void GetStockRequests()
{
try
{
StockRequests.Clear();
string sql = "SELECT pr.id AS pr_id, pr.restock_quantity AS pr_restock_quantity, pr.date_of_request AS pr_date_of_request, pr.complete AS pr_complete, p.name AS p_name, p.department AS p_department FROM product_requests AS pr INNER JOIN products AS p ON pr.stock_id = p.id;";
MySqlCommand cmd = new MySqlCommand(sql, base.connection);
base.connection.Open();
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int id = Convert.ToInt32(reader["pr_id"]);
int restockquantity = Convert.ToInt32(reader["pr_restock_quantity"]);
DateTime dateofrequest = Convert.ToDateTime(reader["pr_date_of_request"]);
bool completed = Convert.ToBoolean(reader["pr_complete"]);
string name = Convert.ToString(reader["p_name"]);
ProductDepartment dep = null;
foreach (ProductDepartment d in dc.GetDepartments())
{
if (d.Id == Convert.ToInt32(reader["p_department"]))
{
dep = d;
}
}
StockRequests.Add(new StockRequest(id, name, dep, dateofrequest, restockquantity, completed));
}
}
catch (Exception)
{
throw new Exception();
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
public void GetDepartmentTest()
{
DepartmentController dc = new DepartmentController();
MySqlConnection connection = CreateConnection();
bool add = dc.AddDepartment("Test department");
ProductDepartment dept = dc.GetDepartment("Test department");
using (connection)
{
connection.Open();
string sql = "DELETE FROM departments WHERE name = @name;";
using (MySqlCommand cmd = new MySqlCommand(sql, connection))
{
cmd.Parameters.AddWithValue("@name", "Test department");
cmd.ExecuteNonQuery();
}
}
Assert.AreEqual("Test department", dept.DptName);
}
private void SearchStock_OnTextChange_1(object sender, EventArgs e)
{
sc.GetStocks();
lvStock.Items.Clear();
ProductDepartment selectedDepartment = dc.GetDepartmentId(cmxCategoryStock.selectedIndex);
foreach (var stock in sc.Stocks)
{
if (stock.Name.ToLower().Contains(SearchStock.text.ToLower()) || SearchStock.text == "Search by name")
{
if (stock.Department == selectedDepartment)
{
string[] row = { stock.Id.ToString(), stock.Name, stock.Department.DptName.ToString(), stock.SupplyCost.ToString(), stock.Price.ToString(), stock.Quantity.ToString() };
lvStock.Items.Add(new ListViewItem(row));
}
else if (cmxCategoryStock.selectedIndex == -1)
{
string[] row = { stock.Id.ToString(), stock.Name, stock.Department.DptName.ToString(), stock.SupplyCost.ToString(), stock.Price.ToString(), stock.Quantity.ToString() };
lvStock.Items.Add(new ListViewItem(row));
}
}
}
}
public IEnumerable <Product> GetProductsByCategory(string category, ProductDepartment department, [FromUri] int skip = 0, [FromUri] int take = 20)
{
return(_ProductsRepository.GetProductsByCategory(category, department, skip, take));
}
public ProductDepartmentModel(ProductDepartment productDepartment)
{
_productDepartment = productDepartment;
}
//TODO: Fix Exceptions
//Extract all listed people in the data base and add them to a list depending on their role & in one mutual list for all users
public void ExtractAllUsers()
{
try
{
string sql = "SELECT * FROM users;"; //SQL command to collect all information for all users
MySqlCommand cmd = new MySqlCommand(sql, base.connection);
base.connection.Open();
MySqlDataReader reader = cmd.ExecuteReader(); //reads row by row
AllUsers.Clear();
Employees.Clear();
Managers.Clear();
Administrators.Clear();
while (reader.Read())
{
//Get all the necessary info from the database to complete a User class object:
int userID = Convert.ToInt32(reader["id"]);
string first_name = Convert.ToString(reader["first_name"]);
string last_name = Convert.ToString(reader["last_name"]);
DateTime dateOfBirth = Convert.ToDateTime(reader["date_of_birth"]);
string address = Convert.ToString(reader["address"]);
Gender gender;
Enum.TryParse <Gender>(reader["gender"].ToString(), out gender);
string phone = Convert.ToString(reader["phone"]);
DateTime dateOfStart = Convert.ToDateTime(reader["date_of_start"]);
string username = Convert.ToString(reader["username"]);
string password = Convert.ToString(reader["password"]);
string email = Convert.ToString(reader["email"]);
double salary = Convert.ToDouble(reader["salary"]);
Role role;
Enum.TryParse <Role>(reader["role"].ToString(), out role);
bool isEmpl = Convert.ToBoolean(reader["employeed"]);
Department department = (Department)(Convert.ToInt32(reader["department"]) - 1);
ProductDepartment dep = null;
foreach (ProductDepartment d in dc.GetDepartments())
{
if (d.Id == Convert.ToInt32(reader["department"]))
{
dep = d;
}
}
User user;
if (role == Role.EMPLOYEE)
{
//comment old code and try only this function with the new code for department
//user = new Employee(userID, first_name, last_name, dateOfBirth, address, gender, phone, dateOfStart, username, password, email, salary, role, isEmpl, department);
user = new Employee(userID, first_name, last_name, dateOfBirth, address, gender, phone, dateOfStart, username, password, email, salary, role, isEmpl, dep);
Employees.Add((Employee)user);
}
else if (role == Role.MANAGER)
{
//user = new Manager(userID, first_name, last_name, dateOfBirth, address, gender, phone, dateOfStart, username, password, email, salary, role, isEmpl, department);
user = new Manager(userID, first_name, last_name, dateOfBirth, address, gender, phone, dateOfStart, username, password, email, salary, role, isEmpl, dep);
Managers.Add((Manager)user);
}
else
{
user = new Admin(userID, first_name, last_name, dateOfBirth, address, gender, phone, dateOfStart, username, password, email, salary, role, isEmpl);
Administrators.Add((Admin)user);
}
AllUsers.Add(user);
}
}
catch (MySqlException)
{
throw new Exception("Fail to connect to the database! Try again later!");
}
catch (Exception)
{
throw new Exception("Sorry, something went wrong! Try again!");
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
public Employee(int id, string firstName, string lastName, DateTime DoB, string address, Gender gender, string phone, DateTime DoS, string username, string password, string email, double salary, Role role, bool isEmp, ProductDepartment dep)
: base(id, firstName, lastName, DoB, address, gender, phone, DoS, username, password, email, salary, role, isEmp)
{
this.dep = dep;
}
public void EditStockTest()
{
StockController sc = new StockController();
Stock actualStock = null;
#region
try
{
string sql = "UPDATE test_products SET department = @department where id = @id;";
MySqlCommand cmd = new MySqlCommand(sql, sc.connection);
cmd.Parameters.AddWithValue("@id", 1);
cmd.Parameters.AddWithValue("@department", 2);
sc.connection.Open();
cmd.ExecuteNonQuery();
}
catch (MySqlException)
{
throw new Exception("Could not connect to the database properly!");
}
catch (Exception)
{
}
finally
{
if (sc.connection != null)
{
sc.connection.Close();
}
}
#endregion
#region
try
{
string sql = "SELECT * FROM test_products;";
MySqlCommand cmd = new MySqlCommand(sql, sc.connection);
sc.connection.Open();
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int id = Convert.ToInt32(reader["id"]);
string name = Convert.ToString(reader["name"]);
string description = Convert.ToString(reader["description"]);
double price = Convert.ToDouble(reader["price"]);
double supplyCost = Convert.ToDouble(reader["supply_cost"]);
DateTime dateOfArrival = Convert.ToDateTime(reader["date_of_arrival"]);
int quantity = Convert.ToInt32(reader["quantity"]);
int restockQuantity = Convert.ToInt32(reader["restock_quantity"]);
int timesSold = Convert.ToInt32(reader["times_sold"]);
bool requested = Convert.ToBoolean(reader["reqested"]);
ProductDepartment dep = null;
foreach (ProductDepartment d in dc.GetDepartments())
{
if (d.Id == Convert.ToInt32(reader["department"]))
{
dep = d;
}
}
actualStock = new Stock(id, name, description, price, supplyCost, dateOfArrival, quantity, timesSold, restockQuantity, requested, dep);
Stock expectedStock = new Stock(testId, testName, testDescription, testPrice, testSupplyCost, testDateOfArrival, testQuantity, testTimesSold, testRestockQuantity, testRequested, testDepartment);
Assert.AreEqual(expectedStock, actualStock);
}
}
catch (Exception)
{
}
finally
{
if (sc.connection != null)
{
sc.connection.Close();
}
}
#endregion
#region
try
{
string sql = "UPDATE test_products SET department = @department where id = @id;";
MySqlCommand cmd = new MySqlCommand(sql, sc.connection);
cmd.Parameters.AddWithValue("@id", 1);
cmd.Parameters.AddWithValue("@department", 1);
sc.connection.Open();
cmd.ExecuteNonQuery();
}
catch (MySqlException)
{
throw new Exception("Could not connect to the database properly!");
}
catch (Exception)
{
}
finally
{
if (sc.connection != null)
{
sc.connection.Close();
}
}
#endregion
}
