public async Task <bool> AddOrUpdate(Schedule _schedule)
{
string sql = "INSERT INTO schedule (day) VALUES (@Day)";
bool success = false;
bool foundSlot = false;
using (MySqlConnection conn = PokeDB.NewConnection())
{
while (!foundSlot)
{
Int64 dayCount = await FindCount(_schedule.day);
if (dayCount >= 3)
{
break;
}
else
{
var affectedRows = await conn.ExecuteAsync(sql, _schedule);
success = (affectedRows > 0) ? true : false;
foundSlot = true;
}
}
}
return(success);
}
public async Task <bool> AddOrUpdate(Inventory _inventory)
{
string sqlUpdate = "UPDATE itemInventory SET itemName = @ItemName, itemQuantity = @ItemQuantity, where itemID = @ItemID";
string sql = "INSERT INTO itemInventory (itemName, itemQuantity) VALUES (@ItemName, @ItemQuantity )";
bool success = false;
Inventory inventory = new Inventory();
using (MySqlConnection conn = PokeDB.NewConnection())
{
try {
inventory = await Find(_inventory.ItemName);
if (inventory != null)
{
inventory.ItemQuantity += _inventory.ItemQuantity;
conn.Execute(sqlUpdate, inventory);
return(success = true);
}
}
catch
{
var affectedRows = await conn.ExecuteAsync(sql, _inventory);
success = (affectedRows > 0) ? true : false;
}
}
return(success);
}
public bool Receive()
{
string deleteOrderItems = "DELETE FROM `item_order` WHERE order_id = @Id";
string deleteOrder = "DELETE FROM `order` WHERE id = @Id";
string updateItems = "UPDATE inventory SET itemQuantity = itemQuantity + @Quantity WHERE id = @ItemId";
bool success = false;
var self = this;
using (MySqlConnection conn = PokeDB.NewConnection())
{
conn.Open();
using (var transactionScope = conn.BeginTransaction())
{
conn.Execute(deleteOrderItems, self);
conn.Execute(deleteOrder, self);
foreach (var item in self.ItemOrders)
{
conn.Execute(updateItems, item);
}
transactionScope.Commit();
success = true;
}
}
return(success);
}
//UPDATE
public bool Update(IEnumerable <Models.Orders.ItemOrder> items)
{
string sql = "REPLACE INTO `item_order` (order_id,item_id, quantity) VALUES (@OrderID, @ItemId, @Quantity)";
// string sql = "INSERT INTO `item_order` (order_id,item_id, quantity) VALUES (@OrderID, @ItemId, @Quantity) ON DUPLICATE KEY UPDATE item_order SET quantity = @Quantity WHERE item_id = @ItemId AND order_id = @OrderId";
string sql2 = "UPDATE `order` SET order_date = @OrderDate WHERE id = @Id";
bool success = false;
this.OrderDate = DateTime.Now;
this.ItemOrders = items;
var self = this;
using (MySqlConnection conn = PokeDB.NewConnection())
{
conn.Open();
using (var transactionScope = conn.BeginTransaction())
{
foreach (var item in ItemOrders)
{
conn.Execute(sql, new { Quantity = item.Quantity, OrderId = this.Id, ItemId = item.ItemId });
}
conn.Execute(sql2, self);
transactionScope.Commit();
success = true;
}
}
return(success);
}
// DELETE
public bool Delete()
{
string sql = "DELETE FROM customer WHERE customerID = @CustomerID";
bool success = false;
using (MySqlConnection conn = PokeDB.NewConnection())
{
MySqlCommand cmd = new MySqlCommand();
try
{
Console.WriteLine("Connecting to MySQL...");
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "CREATE PROCEDURE PROC_DELETE_USER (CustomerID INTEGER) BEGIN DELETE FROM customer WHERE customerID = CustomerID; END";
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
}
conn.Close();
Console.WriteLine("Connection closed.");
try
{
Console.WriteLine("Connecting to MySQL...");
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "PROC_DELETE_USER";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CustomerID", this.CustomerID);
if (cmd.ExecuteNonQuery() == 1)
{
success = true;
}
else
{
success = false;
};
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
success = false;
}
}
// using (MySqlConnection conn = PokeDB.NewConnection())
// {
// var affectedRows = conn.Execute(sql, this);
// success = (affectedRows > 0) ? true : false;
// }
return(success);
}
public async Task <Schedule> Find(string name)
{
string sql = "SELECT * FROM schedule WHERE name = @Name";
Schedule Schedule = null;
using (MySqlConnection conn = PokeDB.NewConnection()) {
Schedule = await conn.QueryFirstAsync <Schedule>(sql, new { name = name });
}
return(Schedule);
}
public async Task <Int64> FindCount(Int64 Day)
{
string sql = "SELECT COUNT(day) FROM schedule WHERE day = @Day";
Int64 count = 0;
using (MySqlConnection conn = PokeDB.NewConnection()) {
count = await conn.QueryFirstAsync <Int64>(sql, new { Day = Day });
}
return(count);
}
public async Task <IEnumerable <Schedule> > GetAll()
{
string sql = "SELECT * FROM schedule";
using (MySqlConnection conn = PokeDB.NewConnection()) {
var ScheduleCollection = await conn.QueryAsync <Schedule>(sql);
return(ScheduleCollection);
}
}
public async Task <Schedule> Find(Int64 Day)
{
string sql = "SELECT * FROM schedule WHERE day = @Day";
Schedule Schedule = null;
using (MySqlConnection conn = PokeDB.NewConnection()) {
Schedule = await conn.QueryFirstAsync <Schedule>(sql, new { Day = Day });
}
return(Schedule);
}
public async Task <IEnumerable <Inventory> > GetAll()
{
string sql = "SELECT * FROM itemInventory";
using (MySqlConnection conn = PokeDB.NewConnection())
{
var InventoryCollection = await conn.QueryAsync <Inventory>(sql);
return(InventoryCollection);
}
}
// READ
public async Task <Inventory> Find(string name)
{
string sql = "SELECT * FROM itemInventory WHERE itemName = @ItemName";
Inventory Inventory = null;
using (MySqlConnection conn = PokeDB.NewConnection())
{
Inventory = await conn.QueryFirstAsync <Inventory>(sql, new { name = name });
}
return(Inventory);
}
public static IEnumerable <User> GetAll(Int64 limit, Int64 offset)
{
string sql = "SELECT * FROM customer LIMIT @Limit OFFSET @Offset ";
IEnumerable <User> users = null;
using (MySqlConnection conn = PokeDB.NewConnection())
{
users = conn.Query <User>(sql, new { Offset = offset, Limit = limit });
}
return(users);
}
public bool UpdatePassword()
{
string sql = "UPDATE user SET password = @Password WHERE id = @Id";
bool success = false;
using (MySqlConnection conn = PokeDB.NewConnection())
{
var affectedRows = conn.Execute(sql, this);
success = (affectedRows > 0) ? true : false;
}
return(success);
}
public static Schedule[] scheduleSqlRunner(string sql)
{
using (MySqlConnection conn = PokeDB.NewConnection())
{
try {
Schedule[] schedule = conn.Query <Schedule>(sql).ToList().ToArray();
return(schedule);
}
catch {
return(null);
}
}
}
public static Inventory[] inventorySqlRunner(string sql)
{
using (MySqlConnection conn = PokeDB.NewConnection())
{
try {
Inventory[] treatmentItems = conn.Query <Inventory>(sql).ToList().ToArray();
return(treatmentItems);
}
catch {
return(null);
}
}
}
public static User GetByName(string customerName)
{
string sql = "SELECT * FROM customer WHERE customerName = @CustomerName";
User user = null;
using (MySqlConnection conn = PokeDB.NewConnection())
{
try
{
user = conn.QueryFirst <User>(sql, new { CustomerName = customerName });
}
catch (Exception err)
{
Console.WriteLine(err.Message);
return(null);
}
}
return(user);
}
public async Task <bool> Delete(string name)
{
string sqlDelete = "DELETE FROM schedule WHERE Id = @Id";
bool success = false;
Schedule schedule = new Schedule();
using (MySqlConnection conn = PokeDB.NewConnection()) {
try {
schedule = await Find(name);
var affectedRows = await conn.ExecuteAsync(sqlDelete, schedule);
success = (affectedRows > 0) ? true : false;
} catch {
return(success);
}
}
return(success);
}
public static IEnumerable <Order> GetAll(Int64 limit, Int64 offset)
{
string sql = "SELECT id, order_date AS orderDate FROM `order` LIMIT @Limit OFFSET @Offset ";
IEnumerable <Order> orders = null;
using (MySqlConnection conn = PokeDB.NewConnection())
{
try
{
orders = conn.Query <Order>(sql, new { Offset = offset, Limit = limit });
}
catch (Exception err)
{
Console.WriteLine(err.Message);
return(null);
}
}
return(orders);
}
// READ
public static User Get(Int64 customerID)
{
System.Console.WriteLine("Running");
string sql = "SELECT * FROM customer WHERE customerID = @CustomerID";
User user = null;
using (MySqlConnection conn = PokeDB.NewConnection())
{
try
{
user = conn.QueryFirst <User>(sql, new { CustomerID = customerID });
}
catch (Exception err)
{
Console.WriteLine(err.Message);
return(null);
}
}
return(user);
}
// DELETE
public bool Delete()
{
string sql = "DELETE FROM `item_order` WHERE order_id = @Id";
string sql2 = "DELETE FROM `order` WHERE id = @Id";
bool success = false;
var self = this;
using (MySqlConnection conn = PokeDB.NewConnection())
{
conn.Open();
using (var transactionScope = conn.BeginTransaction())
{
var affectedRows = conn.Execute(sql, self);
var affectedRows2 = conn.Execute(sql2, self);
transactionScope.Commit();
success = true;
}
}
return(success);
}
//GET ID
public static Order Get(Int64 id)
{
string sql = "SELECT id, order_date AS orderDate FROM `order` WHERE id = @Id";
string sql2 = "SELECT order_id AS OrderID, item_id AS ItemID, quantity AS Quantity FROM item_order WHERE order_id = @Id";
Order order = null;
using (MySqlConnection conn = PokeDB.NewConnection())
{
try
{
order = conn.QueryFirst <Order>(sql, new { Id = id });
var itemList = conn.Query <ItemOrder>(sql2, new { Id = id });
order.ItemOrders = itemList;
}
catch (Exception err)
{
Console.WriteLine(err.Message);
return(null);
}
}
return(order);
}
public async Task <bool> Delete(string name)
{
string sqlDelete = "DELETE FROM inventory WHERE Id = @Id";
bool success = false;
Inventory inventory = new Inventory();
using (MySqlConnection conn = PokeDB.NewConnection()){
try
{
inventory = await Find(name);
var affectedRows = await conn.ExecuteAsync(sqlDelete, inventory);
success = (affectedRows > 0) ? true : false;
}
catch
{
return(success);
}
}
return(success);
}
public bool Add(IEnumerable <Models.Requests.OrderAdd> orders)
{
bool success = false;
string createOrder = "INSERT INTO `order` (order_date) VALUES (@OrderDate); SELECT LAST_INSERT_ID() AS Id;";
string insertOrder = "INSERT INTO `item_order` (order_id,item_id, quantity) VALUES (@OrderID, @ItemId, @Quantity);";
this.OrderDate = DateTime.Now;
var self = this;
using (MySqlConnection conn = PokeDB.NewConnection())
{
conn.Open();
using (var transactionScope = conn.BeginTransaction())
{
try
{
//create order
self.Id = conn.ExecuteScalar <Int64>(createOrder, self);
//add to item_order
foreach (var order in orders)
{
order.OrderId = self.Id;
conn.Execute(insertOrder, order);
}
transactionScope.Commit();
success = true;
}
catch (Exception err)
{
Console.Write("SQL ADD ERR: " + err.Message);
success = false;
}
}
}
return(success);
}
// CREATE
public bool Add()
{
bool success = false;
this.CustomerRole = 1;
this.DateCreated = DateTime.Now;
MySqlConnection conn = PokeDB.NewConnection();
MySqlCommand cmd = new MySqlCommand();
// This is once off
try
{
Console.WriteLine("Connecting to MySQL...");
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "CREATE PROCEDURE EXEC_ADD_USER(" +
"IN CustomerName VARCHAR(250), IN CustomerEmail VARCHAR(250), IN CustomerPassword VARCHAR(250), IN DateCreated DATETIME, IN CustomerRole INTEGER)" +
"BEGIN INSERT INTO customer(customerName, customerEmail, customerPassword, dateCreated, customerRole) " +
"VALUES(CustomerName, CustomerEmail, CustomerPassword, DateCreated, CustomerRole); END";
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
}
conn.Close();
Console.WriteLine("Connection closed.");
try
{
Console.WriteLine("Connecting to MySQL...");
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "EXEC_ADD_USER";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CustomerName", this.CustomerName);
cmd.Parameters["@CustomerName"].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("@CustomerEmail", this.CustomerEmail);
cmd.Parameters["@CustomerEmail"].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("@CustomerPassword", this.CustomerPassword);
cmd.Parameters["@CustomerPassword"].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("@DateCreated", this.DateCreated);
cmd.Parameters["@DateCreated"].Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("@CustomerRole", this.CustomerRole);
cmd.Parameters["@CustomerRole"].Direction = ParameterDirection.Input;
if (cmd.ExecuteNonQuery() == 1)
{
success = true;
}
else
{
success = false;
};
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
success = false;
}
conn.Close();
Console.WriteLine("Done.");
return(success);
}