//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);
}
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);
}
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);
}
// 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(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 <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 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 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 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 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 Schedule[] scheduleSqlRunner(string sql)
{
using (MySqlConnection conn = PokeDB.NewConnection())
{
try {
Schedule[] schedule = conn.Query <Schedule>(sql).ToList().ToArray();
return(schedule);
}
catch {
return(null);
}
}
}
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 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 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);
}
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);
}
//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 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);
}
public Tests()
{
pokeDB = new PokeDB(@"..\..\..\..\PokeBackend\Data\pokemons.csv");
pokeCtrl = new PokeControl(8);
pokeCtrlManyRounds = new PokeControl(1000);
}
public static void Main()
{
pokeDB = new PokeDB(@"..\..\..\Data\pokemons.csv");
pokeCtrl = new PokeControl();
var factory = new ConnectionFactory()
{
HostName = "localhost"
};
using (var connection = factory.CreateConnection())
using (var channel = connection.CreateModel())
{
channel.QueueDeclare(queue: "SearchType", durable: false, exclusive: false, autoDelete: false, arguments: null);
channel.QueueDeclare(queue: "ListMultType", durable: false, exclusive: false, autoDelete: false, arguments: null);
channel.QueueDeclare(queue: "ListAllLegendary", durable: false, exclusive: false, autoDelete: false, arguments: null);
channel.QueueDeclare(queue: "SearchName", durable: false, exclusive: false, autoDelete: false, arguments: null);
channel.QueueDeclare(queue: "ListHeaders", durable: false, exclusive: false, autoDelete: false, arguments: null);
channel.QueueDeclare(queue: "SearchHeader", durable: false, exclusive: false, autoDelete: false, arguments: null);
channel.QueueDeclare(queue: "Battle", durable: false, exclusive: false, autoDelete: false, arguments: null);
channel.BasicQos(0, 1, false);
var consumer = new EventingBasicConsumer(channel);
channel.BasicConsume(queue: "SearchType", autoAck: false, consumer: consumer);
channel.BasicConsume(queue: "ListMultType", autoAck: false, consumer: consumer);
channel.BasicConsume(queue: "ListAllLegendary", autoAck: false, consumer: consumer);
channel.BasicConsume(queue: "SearchName", autoAck: false, consumer: consumer);
channel.BasicConsume(queue: "ListHeaders", autoAck: false, consumer: consumer);
channel.BasicConsume(queue: "SearchHeader", autoAck: false, consumer: consumer);
channel.BasicConsume(queue: "Battle", autoAck: false, consumer: consumer);
Console.WriteLine("Awaiting requests");
consumer.Received += (model, ea) =>
{
string response = null;
var body = ea.Body;
var props = ea.BasicProperties;
var replyProps = channel.CreateBasicProperties();
replyProps.CorrelationId = props.CorrelationId;
try
{
var message = Encoding.UTF8.GetString(body);
Console.WriteLine("In: {0} - {1}", ea.RoutingKey, message);
//Create a stream to serialize the object to.
MemoryStream ms = new MemoryStream();
Object resp = CreateResponse(ea.RoutingKey, message);
// Serializer the User object to the stream.
DataContractJsonSerializer ser = new DataContractJsonSerializer((resp.GetType()));
ser.WriteObject(ms, resp);
byte[] json = ms.ToArray();
ms.Close();
response = Encoding.UTF8.GetString(json, 0, json.Length);
}
catch (Exception e)
{
response = "Error: " + e.Message;
}
finally
{
Console.WriteLine("Out: {0}", response);
var responseBytes = Encoding.UTF8.GetBytes(response);
channel.BasicPublish(exchange: "", routingKey: props.ReplyTo, basicProperties: replyProps, body: responseBytes);
channel.BasicAck(deliveryTag: ea.DeliveryTag, multiple: false);
}
};
Console.WriteLine("Press [enter] to exit.");
Console.ReadLine();
}
}
public void Dispose()
{
pokeDB = null;
pokeCtrl = null;
}
// 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);
}
/// <summary>
/// Determines the winner of a pokemon battle
/// </summary>
/// <param name="PokemonAStr">Pokemon Figther A</param>
/// <param name="PokemonBStr">Pokemon Figther B</param>
/// <param name="pokeDB">The pokemon Database</param>
/// <returns>The winner of the pokemon battle</returns>
public string Battle(string PokemonAStr, string PokemonBStr, PokeDB pokeDB)
{
//This could be done by iterating each round through, runtime will be affected directly by amount of rounds
//But could also be done with a few calculations and some conditions
Pokemon PokemonA = pokeDB.SearchNameExplicit(PokemonAStr);
Pokemon PokemonB = pokeDB.SearchNameExplicit(PokemonBStr);
if (PokemonA == null && PokemonB == null)
{
return("No Contest");
}
if (PokemonA == null)
{
return(PokemonB.Name + " Wins - Enemy No Show");
}
if (PokemonB == null)
{
return(PokemonA.Name + " Wins - Enemy No Show");
}
int RoundsToWinPokemonA = RoundsToWinBattle(PokemonA.Attack, PokemonB.HP, PokemonB.Defense);
int RoundsToWinPokemonB = RoundsToWinBattle(PokemonB.Attack, PokemonA.HP, PokemonA.Defense);
//Nobody can win in defined rounds
if (RoundsToWinPokemonA > RoundsToFight && RoundsToWinPokemonB > RoundsToFight)
{
//Business decided the winner is highest HP
int EnemyHPPokemonA = EnemyHPAfterNRounds(PokemonA.Attack, PokemonB.HP, PokemonB.Defense, RoundsToFight);
int EnemyHPPokemonB = EnemyHPAfterNRounds(PokemonB.Attack, PokemonA.HP, PokemonA.Defense, RoundsToFight);
if (EnemyHPPokemonA < EnemyHPPokemonB)
{
return(PokemonA.Name + " Wins - Enemy HP " + EnemyHPPokemonA + " < " + EnemyHPPokemonB);
}
else if (EnemyHPPokemonB < EnemyHPPokemonA)
{
return(PokemonB.Name + " Wins - Enemy HP " + EnemyHPPokemonB + " < " + EnemyHPPokemonA);
}
else
{
//Business must decide
return("Tie after " + RoundsToFight + " rounds");
}
}
if (RoundsToWinPokemonA < RoundsToWinPokemonB)
{
return(PokemonA.Name + " Wins - Round " + RoundsToWinPokemonA);
}
else if (RoundsToWinPokemonB < RoundsToWinPokemonA)
{
return(PokemonB.Name + " Wins - Round " + RoundsToWinPokemonB);
}
else
{
//Fastest pokemon wins
if (PokemonA.Speed > PokemonB.Speed)
{
return(PokemonA.Name + " Wins - Speed " + PokemonA.Speed + ">" + PokemonB.Speed);
}
else if (PokemonB.Speed > PokemonA.Speed)
{
return(PokemonB.Name + " Wins - Speed " + PokemonB.Speed + ">" + PokemonA.Speed);
}
else
{
//Business must decide
return("Tie - Speed is same");
}
}
}
