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();
            }
        }