public int GetNextEmployeeNr()
{
string sql = "select coalesce(max(empNr), 199) from employee"; // EmpNr starts from 200
try
{
MyDataHelper.OpenConnection();
return(Convert.ToInt32(myDataHelper.ExecuteScalar(sql)));
}
catch
{
return(-1);
}
finally
{
MyDataHelper.CloseConnection();
}
}
public int GetNextVisitorNr()
{
string sql = "select coalesce(max(participantNr), 499) from participant"; // visitorNr starts from 499
try
{
MyDataHelper.OpenConnection();
return(Convert.ToInt32(myDataHelper.ExecuteScalar(sql)));
}
catch
{
return(-1);
}
finally
{
MyDataHelper.CloseConnection();
}
}
public int GetNextOrderNumber()
{
string sql = "select coalesce(max(orderNr), 99) from `order`"; // orderNr starts from 100
try
{
MyDataHelper.OpenConnection();
return(Convert.ToInt32(myDataHelper.ExecuteScalar(sql)));
}
catch
{
return(-1);
}
finally
{
MyDataHelper.CloseConnection();
}
}
public int GetNextLogFileId()
{
string sql = "select coalesce(max(logNr), 0) from atm_log_file"; // logNr starts from 1
try
{
MyDataHelper.OpenConnection();
return(Convert.ToInt32(myDataHelper.ExecuteScalar(sql)));
}
catch
{
return(-1);
}
finally
{
MyDataHelper.CloseConnection();
}
}
public int GetQuantitySoldOrTimesRented(EventItem item)
{
string sql = $"select coalesce(sum(quantity), 0) from order_line_item group by sku having sku = {item.Sku}; ";
try
{
MyDataHelper.OpenConnection();
return(Convert.ToInt32(myDataHelper.ExecuteScalar(sql)));
}
catch
{
return(-1);
}
finally
{
MyDataHelper.CloseConnection();
}
}
public double CalculateTotalFine(Visitor v)
{
string sql = $"select coalesce(sum(paidFine), 0) from `returned_item` group by ticketNr having ticketNr = {v.TicketNr}; ";
try
{
MyDataHelper.OpenConnection();
return(Convert.ToDouble(myDataHelper.ExecuteScalar(sql)));
}
catch
{
return(-1);
}
finally
{
MyDataHelper.CloseConnection();
}
}
public double CalculateTotalBalance()
{
string sql = $"select coalesce(sum(balance), 0) from participant_event group by eventNr having eventNr = {myEvent.Id}; ";
try
{
MyDataHelper.OpenConnection();
return(Convert.ToDouble(myDataHelper.ExecuteScalar(sql)));
}
catch
{
return(-1);
}
finally
{
MyDataHelper.CloseConnection();
}
}
public double CalculateTotalBuyingRenting(Visitor v)
{
string sql = $"select coalesce(sum(totalToPay), 0) from `order` group by ticketNr having ticketNr = {v.TicketNr}; ";
try
{
MyDataHelper.OpenConnection();
return(Convert.ToDouble(myDataHelper.ExecuteScalar(sql)));
}
catch
{
return(-1);
}
finally
{
MyDataHelper.CloseConnection();
}
}
public double CalculateTotalTopUp()
{
string sql = $"select coalesce(sum(amount), 0) from transaction; ";
try
{
MyDataHelper.OpenConnection();
return(Convert.ToDouble(myDataHelper.ExecuteScalar(sql)));
}
catch
{
return(-1);
}
finally
{
MyDataHelper.CloseConnection();
}
}
public double CalculateRevenueFromFine()
{
string sql = $"select coalesce(sum(paidFine), 0) from returned_item; ";
try
{
MyDataHelper.OpenConnection();
return(Convert.ToDouble(myDataHelper.ExecuteScalar(sql)));
}
catch
{
return(-1);
}
finally
{
MyDataHelper.CloseConnection();
}
}
public double CalculateRevenueFromVendingMachines()
{
string sql = $"select coalesce(sum(totalToPay), 0) from `order` join service_point on `order`.shopId = service_point.id group by type having type = 'vending_machine'; ";
try
{
MyDataHelper.OpenConnection();
return(Convert.ToDouble(myDataHelper.ExecuteScalar(sql)));
}
catch
{
return(-1);
}
finally
{
MyDataHelper.CloseConnection();
}
}
public double CalculateRevenueFromAServicePoint(ServicePoint point)
{
string sql = $"select coalesce(sum(totalToPay), 0) from `order` group by shopId having shopId = {point.PointId}";
try
{
MyDataHelper.OpenConnection();
return(Convert.ToDouble(myDataHelper.ExecuteScalar(sql)));
}
catch
{
return(-1);
}
finally
{
MyDataHelper.CloseConnection();
}
}
private List <Person> GetEmployees()
{
string sql = "SELECT * FROM employee";
List <Person> temp = new List <Person>();
try
{
MyDataHelper.OpenConnection();
MySqlDataReader reader = myDataHelper.DataReader(sql);
while (reader.Read())
{
Gender g;
Person emp;
if (reader["gender"].ToString() == "F")
{
g = Gender.Female;
}
else
{
g = Gender.Male;
}
if (reader["level"].ToString() == "level_3")
{
WorkPlaceForLevel3 place;
if (reader["work_place"].ToString() == "shop")
{
place = WorkPlaceForLevel3.Shop;
}
else if (reader["work_place"].ToString() == "entrance check")
{
place = WorkPlaceForLevel3.EntranceCheck;
}
else
{
place = WorkPlaceForLevel3.Other;
}
emp = new Employee(Convert.ToInt32(reader["empNr"]), reader["first_name"].ToString(), reader["last_name"].ToString(),
Convert.ToDateTime(reader["dob"]), g, reader["address"].ToString(), reader["email"].ToString(), reader["userName"].ToString(),
reader["password"].ToString(), place);
}
else
{
EmpLevel lvl;
if (reader["level"].ToString() == "level_2")
{
lvl = EmpLevel.Level2;
}
else
{
lvl = EmpLevel.Level1;
}
emp = new Employee(Convert.ToInt32(reader["empNr"]), reader["first_name"].ToString(), reader["last_name"].ToString(),
Convert.ToDateTime(reader["dob"]), g, reader["address"].ToString(), reader["email"].ToString(), reader["userName"].ToString(),
reader["password"].ToString(), lvl);
}
temp.Add(emp);
}
return(temp);
}
catch
{
return(null);
}
finally
{
MyDataHelper.CloseConnection();
}
}
