private bool updateAddress(string id, Address addr)
{
if (!AdditionalFunctions.isEmpty(id) && int.Parse(id) >= 0)
{
string updateCmd = $"UPDATE Addresses SET street = '{ addr.street }', house_num = '{ addr.house_num }', city = '{ addr.city }', zip_code = '{ addr.zip_code }', lat = { addr.lat.ToString() }, lng = { addr.lng.ToString() } WHERE id = { id }";
MySqlCommand cmd = new MySqlCommand(updateCmd);
cmd.CommandType = CommandType.Text;
if (conn != null)
{
cmd.Connection = conn;
conn.Open();
try {
cmd.ExecuteNonQuery();
conn.Close();
return(true);
} catch (MySqlException ex) {
conn.Close();
throw ex;
}
}
return(false);
}
else
{
throw new Exception("Address's id must be defined correctly");
}
}
public bool updateATM(ATM atm, Address originalAddr, string atmID, string addressID)
{
if (!AdditionalFunctions.isEmpty(atmID))
{
string updateCmd = $"UPDATE Atms SET capacity = '{ atm.capacity }', atm_size = '{ atm.size }', brand = { atm.brand }, Addresses_id = '{ addressID }', live_money_available = { atm.live_money_avilable } WHERE id = { atmID }";
MySqlCommand cmd = new MySqlCommand(updateCmd);
cmd.CommandType = CommandType.Text;
if (!atm.address.Equals(originalAddr))
{
Console.WriteLine("Updating address");
try {
// Needs to update the address
bool res = updateAddress(addressID, atm.address);
if (!res)
{
return(false);
}
} catch (Exception ex) {
Console.WriteLine("Problem with address update");
throw ex;
}
}
if (conn != null)
{
cmd.Connection = conn;
conn.Open();
try {
cmd.ExecuteNonQuery();
conn.Close();
return(true);
} catch (MySqlException ex) {
Console.WriteLine(ex.ToString());
conn.Close();
return(false);
}
}
return(false);
}
else
{
throw new Exception("Employee's id must be defined correctly");
}
}
public bool updateEmployee(Employee emp, Address originalAdrr, string addressID)
{
if (!AdditionalFunctions.isEmpty(emp.id) && emp.id.Length == 9)
{
string hashedPass = AdditionalFunctions.MD5(AdditionalFunctions.MD5(emp.password));
string updateCmd = $"UPDATE Employees SET id = { emp.id }, name = '{ emp.name }', birth_date = '{ emp.birthDate.ToString() }', role = { emp.role }, password = '******', phone_number = { emp.phone_number }, email = { emp.email }, gender = { emp.gender }, Addresses_id = { addressID } WHERE id = { emp.id }";
MySqlCommand cmd = new MySqlCommand(updateCmd);
cmd.CommandType = CommandType.Text;
if (!emp.address.Equals(originalAdrr))
{
try {
// Needs to update the address
bool res = updateAddress(addressID, emp.address);
if (!res)
{
return(false);
}
} catch (Exception ex) {
throw ex;
}
}
if (conn != null)
{
cmd.Connection = conn;
conn.Open();
try {
cmd.ExecuteNonQuery();
conn.Close();
return(true);
} catch (MySqlException ex) {
Console.WriteLine(ex.ToString());
conn.Close();
return(false);
}
}
return(false);
}
else
{
throw new Exception("Employee's id must be defined correctly");
}
}
public int getAvgWithdrawalsOfDay(string atmID, int day)
{
if (day >= 1 && day <= 7)
{
connect();
// Get avg sum of day between sunday and saturday of atm's withdrawls
string query = $"SELECT SUM(WithdrawalsSum) as SumOfAll, COUNT(*) AS NumOfRows FROM ( SELECT *, SUM(`amount`) as 'WithdrawalsSum' FROM Withdrawals WHERE date != CURDATE() and Atms_id = { atmID } and DAYOFWEEK(date) = { day } GROUP BY date ) as anotherAlias;";
MySqlCommand checkAvgCmd = new MySqlCommand(query, conn);
checkAvgCmd.CommandType = CommandType.Text;
if (conn != null)
{
try {
conn.Open();
MySqlDataReader dataReader = checkAvgCmd.ExecuteReader();
int sumOfAll = 0;
int numOfRows = 1;
while (dataReader.Read())
{
Console.WriteLine(dataReader.FieldCount);
string sumOfAllStr = dataReader["SumOfAll"].ToString();
string numOfRowsStr = dataReader["NumOfRows"].ToString();
if (!AdditionalFunctions.isEmpty(sumOfAllStr) && !AdditionalFunctions.isEmpty(numOfRowsStr))
{
sumOfAll = int.Parse(sumOfAllStr);
numOfRows = int.Parse(numOfRowsStr);
}
}
conn.Close();
return(sumOfAll / numOfRows);
} catch (MySqlException ex) {
conn.Close();
throw ex;
}
}
throw new Exception("Connection with database didn't established, Please try again");
}
throw new Exception("Inserted incorrect day, Day must be between 1 (internal) and 7 (internal)");
}
