public List<Tuple<Theme, bool>> GetAll(Guest guest)
 {
     try {
         List<string> columnNames = new List<string> ();
         columnNames.Add ("guestId");
         columnNames.Add ("themeId");
         columnNames.Add ("likeValue");
         StatementValue where = new StatementValue ()
     {
         Item1 = "guestId = @StatementValue0",
         Item2 = new List<string>(new string[]{guest.Id.ToString()})
     };
         List<List<string>> result = this.Select (columnNames, where);
         List<Tuple<Theme, bool>> objs = new List<Tuple<Theme, bool>> ();
         List<Theme> themes = (new ThemesTable (this.entity)).GetAll ();
         for (int i = 0; i < result[0].Count; i++) {
             Theme theme = themes.Find (thme => thme.Id == int.Parse (result [1] [i]));
             bool like = bool.Parse (result [2] [i]);
             objs.Add (new Tuple<Theme, bool> (theme, like));
         }
         return objs;
     } catch (MySqlException ex) {
         switch (ex.Number) {
         case 0:
             throw new DatabaseException ("Cannot connect to server.  Contact administrator", ex);
         case 1045:
             throw new DatabaseException ("Invalid username/password, please try again", ex);
         default:
             throw new DatabaseException (ex.Message, ex);
         }
     }
 }
        public GeneralItem Get(int id)
        {
            try {
                List<string> columnNames = new List<string> ();
                columnNames.Add ("id");
                columnNames.Add ("name");
                columnNames.Add ("iconId");

                StatementValue where = new StatementValue ();
                where.Item1 = "id = @StatementValue0";
                where.Item2 = new List<string> (new string[]{id.ToString ()});
                List<List<string>> result = this.Select (columnNames, where);
                List<GeneralItem> generalItems = new List<GeneralItem> ();
                for (int i = 0; i < result[0].Count; i++) {
                    generalItems.Add (new GeneralItem (int.Parse (result [0] [i]), result [1] [i], int.Parse (result [2] [i])));
                }
                return generalItems.FirstOrDefault ();
            } catch (MySqlException ex) {
                switch (ex.Number) {
                case 0:
                    throw new DatabaseException ("Cannot connect to server.  Contact administrator", ex);
                case 1045:
                    throw new DatabaseException ("Invalid username/password, please try again", ex);
                default:
                    throw new DatabaseException (ex.Message, ex);
                }
            }
        }
        public Question Get(int id)
        {
            try {
                List<string> columnNames = new List<string> ();
                columnNames.Add ("id");
                columnNames.Add ("question");

                StatementValue where = new StatementValue ()
            {
                Item1 = "id = @StatementValue0",
                Item2 = new List<string>()
            };
                where.Item2.Add (id.ToString ());
                List<List<string>> result = this.Select (columnNames, where);

                List<Question> questions = new List<Question> ();
                for (int i = 0; i < result[0].Count; i++) {
                    questions.Add (new Question (int.Parse (result [0] [i]), result [1] [i]));
                }
                return questions.FirstOrDefault ();
            } catch (MySqlException ex) {
                switch (ex.Number) {
                case 0:
                    throw new DatabaseException ("Cannot connect to server.  Contact administrator", ex);
                case 1045:
                    throw new DatabaseException ("Invalid username/password, please try again", ex);
                default:
                    throw new DatabaseException (ex.Message, ex);
                }
            }
        }
 public void Delete(Dto.Action action)
 {
     StatementValue where = new StatementValue ();
     where.Item1 = "id = @StatementValue0";
     where.Item2 = new List<string> ();
     where.Item2.Add (action.Id.ToString ());
     this.Delete (where);
 }
 public Dto.Action Get(int id)
 {
     StatementValue where = new StatementValue ()
     {
         Item1 = "id = @StatementValue0",
         Item2 = new List<string>()
     };
     where.Item2.Add (id.ToString ());
     List<Dto.Action> answers = this.GetAll (where);
     return answers.FirstOrDefault ();
 }
 public void Delete(GeneralItem generalItems)
 {
     try {
         StatementValue where = new StatementValue ();
         where.Item1 = "id = @StatementValue0";
         where.Item2 = new List<string> ();
         where.Item2.Add (generalItems.Id.ToString ());
         this.Delete (where);
     } catch (MySqlException ex) {
         switch (ex.Number) {
         case 0:
             throw new DatabaseException ("Cannot connect to server.  Contact administrator", ex);
         case 1045:
             throw new DatabaseException ("Invalid username/password, please try again", ex);
         default:
             throw new DatabaseException (ex.Message, ex);
         }
     }
 }
 public void Delete(Player player, SpecificItem item)
 {
     try {
         StatementValue where = new StatementValue ();
         where.Item1 = "playerId = @StatementValue0 AND specificItemId = @StatementValue1";
         where.Item2 = new List<string> ();
         where.Item2.Add (player.Id.ToString ());
         where.Item2.Add (item.Id.ToString ());
         this.Delete (where);
     } catch (MySqlException ex) {
         switch (ex.Number) {
         case 0:
             throw new DatabaseException ("Cannot connect to server.  Contact administrator", ex);
         case 1045:
             throw new DatabaseException ("Invalid username/password, please try again", ex);
         default:
             throw new DatabaseException (ex.Message, ex);
         }
     }
 }
        private List<Dto.Action> GetAll(StatementValue where)
        {
            List<string> columnNames = new List<string> ();
            columnNames.Add ("id");
            columnNames.Add ("gameId");
            columnNames.Add ("playerId");
            columnNames.Add ("time");
            columnNames.Add ("description");

            List<List<string>> result;
            if (where != null) {
                result = this.Select (columnNames, where);
            } else {
                result = this.Select (columnNames);
            }

            PlayersTable playersTable = new PlayersTable (entity);
            GamesTable gamesTable = new GamesTable (entity);

            List<Dto.Action> actions = new List<Dto.Action> ();
            for (int i = 0; i < result[0].Count; i++) {
                Game game = gamesTable.Get (result [1] [i]);
                Player player = null;
                if (!string.IsNullOrEmpty (result [2] [i]))
                    player = playersTable.Get (int.Parse (result [2] [i]));
                actions.Add (new Dto.Action (int.Parse (result [0] [i]), result [4] [i], game, player, DateTime.Parse (result [3] [i])));
            }
            return actions;
        }
 public List<Guest> GetAllShallow(Game game)
 {
     try {
         List<string> columnNames = new List<string> ();
         columnNames.Add ("id");
         columnNames.Add ("name");
         columnNames.Add ("avatarId");
         columnNames.Add ("gameId");
         StatementValue where = new StatementValue ()
     {
         Item1 = "gameId = @StatementValue0",
         Item2 = new List<string>(new string[] { game.Id.ToString() })
     };
         List<List<string>> result = this.Select (columnNames, where);
         List<Guest> objs = new List<Guest> ();
         for (int i = 0; i < result[0].Count; i++) {
             objs.Add (new Guest (int.Parse (result [0] [i]), result [1] [i], int.Parse (result [2] [i])));
         }
         return objs;
     } catch (MySqlException ex) {
         switch (ex.Number) {
         case 0:
             throw new DatabaseException ("Cannot connect to server.  Contact administrator", ex);
         case 1045:
             throw new DatabaseException ("Invalid username/password, please try again", ex);
         default:
             throw new DatabaseException (ex.Message, ex);
         }
     }
 }
 private List<Player> GetAllShallow(StatementValue where)
 {
     return GetAllShallow (where, null);
 }
        public void SaveShallow(Player player, Game game)
        {
            try {
                List<ColumnValue> columns = new List<ColumnValue> ();
                columns.Add (new ColumnValue () { Item1 = "gameId", Item2 = game.Id.ToString() });
                columns.Add (new ColumnValue () { Item1 = "guestId", Item2 = player.Guest.Id.ToString() });
                columns.Add (new ColumnValue () { Item1 = "budget", Item2 = player.Budget.ToString() });
                int inUseValue = player.InUse ? 1 : 0;
                columns.Add (new ColumnValue () { Item1 = "inUse", Item2 = inUseValue.ToString() });
                int wasUsedValue = player.WasUsed ? 1 : 0;
                columns.Add (new ColumnValue () { Item1 = "wasUsed", Item2 = wasUsedValue.ToString() });
                columns.Add (new ColumnValue () { Item1 = "colorId", Item2 = player.ColorId.ToString() });
                columns.Add (new ColumnValue () { Item1 = "time", Item2 = player.Time.ToString() });
                columns.Add (new ColumnValue () { Item1 = "itemsReturned", Item2 = player.ItemsReturned.ToString() });

                StatementValue where = new StatementValue ();
                where.Item1 = "id = @StatementValue0";
                where.Item2 = new List<string> ();
                where.Item2.Add (player.Id.ToString ());

                this.Update (columns, where);
            } catch (MySqlException ex) {
                switch (ex.Number) {
                case 0:
                    throw new DatabaseException ("Cannot connect to server.  Contact administrator", ex);
                case 1045:
                    throw new DatabaseException ("Invalid username/password, please try again", ex);
                default:
                    throw new DatabaseException (ex.Message, ex);
                }
            }
        }
        public Player GetShallow(int id)
        {
            StatementValue where = new StatementValue ()
            {
                Item1 = "id = @StatementValue0",
                Item2 = new List<string>(new string[] { id.ToString() })
            };

            return this.GetAllShallow (where).FirstOrDefault ();
        }
 public List<Player> GetAllShallow(Game game)
 {
     StatementValue where = new StatementValue ()
     {
         Item1 = "gameId = @StatementValue0",
         Item2 = new List<string>(new string[] { game.Id.ToString() })
     };
     List<Guest> guests = (new GuestsTable (this.entity)).GetAll (game);
     return GetAllShallow (where, guests);
 }
        /// <summary>
        /// Saves the specified guest to the database.
        /// </summary>
        /// <remarks>
        /// Also updates it's likes and dislikes (removing old ones and adding new ones)
        /// </remarks>
        /// <param name="guest">The guest.</param>
        public void Save(Guest guest, Game game)
        {
            try {
                if (guest.Id < 0) {
                    return;
                }

                List<ColumnValue> columns = new List<ColumnValue> ();
                columns.Add (new ColumnValue () { Item1 = "name", Item2 = guest.Name });
                columns.Add (new ColumnValue () { Item1 = "avatarId", Item2 = guest.AvatarId.ToString() });
                columns.Add (new ColumnValue () { Item1 = "gameId", Item2 = game.Id.ToString() });

                StatementValue where = new StatementValue ();
                where.Item1 = "id = @StatementValue0";
                where.Item2 = new List<string> ();
                where.Item2.Add (guest.Id.ToString ());

                this.Update (columns, where);

                LikesTable likesTable = new LikesTable (this.entity);
                List<Tuple<Theme, bool>> likes = likesTable.GetAll (guest);
                // Delete removed likes
                foreach (Tuple<Theme, bool> like in likes) {
                    if (!guest.Likes.Contains (like.Item1) && !guest.Dislikes.Contains (like.Item1)) {
                        likesTable.Delete (guest, like.Item1);
                    }
                }

                // Update likes and add new likes
                foreach (Theme theme in guest.Likes) {
                    Tuple<Theme, bool> findTheme = likes.Find (thmeLike => thmeLike.Item1.Equals (theme));
                    if (findTheme == null) {
                        likesTable.AddLike (guest, theme, true);
                    } else {
                        if (!findTheme.Item2)
                            likesTable.Save (guest, theme, true);
                    }
                }
                foreach (Theme theme in guest.Dislikes) {
                    Tuple<Theme, bool> findTheme = likes.Find (thmeLike => thmeLike.Item1.Equals (theme));
                    if (findTheme == null) {
                        likesTable.AddLike (guest, theme, false);
                    } else {
                        if (findTheme.Item2)
                            likesTable.Save (guest, theme, false);
                    }
                }
            } catch (MySqlException ex) {
                switch (ex.Number) {
                case 0:
                    throw new DatabaseException ("Cannot connect to server.  Contact administrator", ex);
                case 1045:
                    throw new DatabaseException ("Invalid username/password, please try again", ex);
                default:
                    throw new DatabaseException (ex.Message, ex);
                }
            }
        }
        public void Save(Dto.Action action)
        {
            if (action.Id < 0) {
                this.Add (action);
                return;
            }
            try {
                int tid = entity.BeginTransaction ();

                List<ColumnValue> columns = new List<ColumnValue> ();
                columns.Add (new ColumnValue () { Item1 = "gameId", Item2 = action.Game.Id.ToString() });
                columns.Add (new ColumnValue () { Item1 = "time", Item2 = action.Time.ToString("yyyy-MM-dd HH:mm:ss") });
                columns.Add (new ColumnValue () { Item1 = "description", Item2 = action.Description });
                if (action.Player != null) {
                    columns.Add (new ColumnValue () { Item1 = "playerId", Item2 = action.Player.Id.ToString() });
                } else {
                    columns.Add (new ColumnValue () { Item1 = "playerId", Item2 = null });
                }

                StatementValue where = new StatementValue ();
                where.Item1 = "id = @StatementValue0";
                where.Item2 = new List<string> ();
                where.Item2.Add (action.Id.ToString ());

                this.Update (columns, where);
                entity.Commit (tid);
            } catch (MySqlException ex) {
                entity.Rollback ();
                Debug.LogError ("Table " + this.Name + ": Failed to Save: " + ex.Message);
            }
        }
        public void Save(Question question)
        {
            if (question.Id < 0) {
                this.Add (question);
                return;
            }
            try {
                int tid = entity.BeginTransaction ();

                List<ColumnValue> columns = new List<ColumnValue> ();
                columns.Add (new ColumnValue () { Item1 = "question", Item2 = question.Value });

                StatementValue where = new StatementValue ();
                where.Item1 = "id = @StatementValue0";
                where.Item2 = new List<string> ();
                where.Item2.Add (question.Id.ToString ());

                this.Update (columns, where);

                entity.Commit (tid);

            } catch (MySqlException ex) {
                switch (ex.Number) {
                case 0:
                    throw new DatabaseException ("Cannot connect to server.  Contact administrator", ex);
                case 1045:
                    throw new DatabaseException ("Invalid username/password, please try again", ex);
                default:
                    throw new DatabaseException (ex.Message, ex);
                }
            }
        }
        private List<Player> GetAllShallow(StatementValue where, List<Guest> guests)
        {
            try {
                List<string> columnNames = new List<string> ();
                columnNames.Add ("id");
                columnNames.Add ("gameId");
                columnNames.Add ("guestId");
                columnNames.Add ("budget");
                columnNames.Add ("inUse");
                columnNames.Add ("colorId");
                columnNames.Add ("time");
                columnNames.Add ("wasUsed");
                columnNames.Add ("itemsReturned");
                List<List<string>> result = null;
                if (where == null) {
                    result = this.Select (columnNames);
                } else {
                    result = this.Select (columnNames, where);
                }

                GuestsTable guestsTable = new GuestsTable (this.entity);

                List<Player> players = new List<Player> ();
                for (int i = 0; i < result[0].Count; i++) {
                    int id = int.Parse (result [0] [i]);
                    Guest guest = null;
                    int guestId = int.Parse (result [2] [i]);
                    if (guests == null) {
                        guest = guestsTable.Get (guestId);
                    } else {
                        guest = guests.Find (gst => gst.Id == guestId);
                    }
                    float budget = float.Parse (result [3] [i]);
                    bool inUse = bool.Parse (result [4] [i]);
                    bool wasUsed = bool.Parse (result [7] [i]);
                    int colorId = int.Parse (result [5] [i]);
                    float time = float.Parse (result [6] [i]);
                    int itemsReturned = int.Parse (result [8] [i]);
                    players.Add (new Player (id, guest, budget, inUse, colorId, time, wasUsed, itemsReturned));
                }
                return players;
            } catch (MySqlException ex) {
                switch (ex.Number) {
                case 0:
                    throw new DatabaseException ("Cannot connect to server.  Contact administrator", ex);
                case 1045:
                    throw new DatabaseException ("Invalid username/password, please try again", ex);
                default:
                    throw new DatabaseException (ex.Message, ex);
                }
            }
        }
        public void Save(Guest obj, Theme theme, bool like)
        {
            try {
                List<ColumnValue> columns = new List<ColumnValue> ();
                int likeValue = like ? 1 : 0;
                columns.Add (new ColumnValue () { Item1 = "likeValue", Item2 = likeValue.ToString() });

                StatementValue where = new StatementValue ();
                where.Item1 = "guestId = @StatementValue0 AND themeId = @StatementValue1";
                where.Item2 = new List<string> ();
                where.Item2.Add (obj.Id.ToString ());
                where.Item2.Add (theme.Id.ToString ());

                this.Update (columns, where);
            } catch (MySqlException ex) {
                switch (ex.Number) {
                case 0:
                    throw new DatabaseException ("Cannot connect to server.  Contact administrator", ex);
                case 1045:
                    throw new DatabaseException ("Invalid username/password, please try again", ex);
                default:
                    throw new DatabaseException (ex.Message, ex);
                }
            }
        }
        public void Save(Player player, GeneralItem item, int quantity)
        {
            try {
                List<ColumnValue> columns = new List<ColumnValue> ();
                columns.Add (new ColumnValue () { Item1 = "quantity", Item2 = quantity.ToString() });

                StatementValue where = new StatementValue ();
                where.Item1 = "playerId = @StatementValue0 AND generalItemId = @StatementValue1";
                where.Item2 = new List<string> ();
                where.Item2.Add (player.Id.ToString ());
                where.Item2.Add (item.Id.ToString ());

                this.Update (columns, where);
            } catch (MySqlException ex) {
                switch (ex.Number) {
                case 0:
                    throw new DatabaseException ("Cannot connect to server.  Contact administrator", ex);
                case 1045:
                    throw new DatabaseException ("Invalid username/password, please try again", ex);
                default:
                    throw new DatabaseException (ex.Message, ex);
                }
            }
        }
        public void Save(GeneralItem generalItem)
        {
            try {
                ColumnValue name = new ColumnValue () { Item1 = "name", Item2 = generalItem.Name };
                ColumnValue iconId = new ColumnValue () { Item1 = "iconId", Item2 = generalItem.IconId.ToString() };
                List<ColumnValue> columns = new List<ColumnValue> ();
                columns.Add (name);
                columns.Add (iconId);

                StatementValue where = new StatementValue ();
                where.Item1 = "id = @StatementValue0";
                where.Item2 = new List<string> ();
                where.Item2.Add (generalItem.Id.ToString ());

                this.Update (columns, where);
            } catch (MySqlException ex) {
                switch (ex.Number) {
                case 0:
                    throw new DatabaseException ("Cannot connect to server.  Contact administrator", ex);
                case 1045:
                    throw new DatabaseException ("Invalid username/password, please try again", ex);
                default:
                    throw new DatabaseException (ex.Message, ex);
                }
            }
        }
        public List<Quantifier<GeneralItem>> GetAll(Player player)
        {
            try {
                List<string> columnNames = new List<string> ();
                columnNames.Add ("playerId");
                columnNames.Add ("generalItemId");
                columnNames.Add ("quantity");
                StatementValue where = new StatementValue ()
            {
                Item1 = "playerId = @StatementValue0",
                Item2 = new List<string>(new string[] { player.Id.ToString() })
            };
                List<List<string>> result = this.Select (columnNames, where);

                List<GeneralItem> generalItems = (new GeneralItemsTable (this.entity)).GetAll ();
                List<Quantifier<GeneralItem>> items = new List<Quantifier<GeneralItem>> ();
                for (int i = 0; i < result[0].Count; i++) {
                    GeneralItem item = generalItems.Find (itm => itm.Id == int.Parse (result [1] [i]));
                    items.Add (new Quantifier<GeneralItem> (int.Parse (result [2] [i]), item));
                }
                return items;
            } catch (MySqlException ex) {
                switch (ex.Number) {
                case 0:
                    throw new DatabaseException ("Cannot connect to server.  Contact administrator", ex);
                case 1045:
                    throw new DatabaseException ("Invalid username/password, please try again", ex);
                default:
                    throw new DatabaseException (ex.Message, ex);
                }
            }
        }
        public Answer Get(int id)
        {
            try {
                List<string> columnNames = new List<string> ();
                columnNames.Add ("id");
                columnNames.Add ("gameId");
                columnNames.Add ("playerId");
                columnNames.Add ("questionId");
                columnNames.Add ("answer");
                StatementValue where = new StatementValue ()
            {
                Item1 = "id = @StatementValue0",
                Item2 = new List<string>()
            };
                where.Item2.Add (id.ToString ());
                List<List<string>> result = this.Select (columnNames, where);

                GamesTable gamesTable = new GamesTable (entity);
                PlayersTable playersTable = new PlayersTable (entity);
                QuestionsTable questionsTable = new QuestionsTable (entity);

                List<Answer> answers = new List<Answer> ();
                for (int i = 0; i < result[0].Count; i++) {
                    Game game = gamesTable.GetShallow (int.Parse (result [1] [i]));
                    Player player = null;
                    if (!string.IsNullOrEmpty (result [4] [i]))
                        player = playersTable.Get (int.Parse (result [2] [i]));
                    Question question = questionsTable.Get (int.Parse (result [3] [i]));
                    answers.Add (new Answer (int.Parse (result [0] [i]), question, game, player, result [4] [i]));
                }
                return answers.FirstOrDefault ();
            } catch (MySqlException ex) {
                switch (ex.Number) {
                case 0:
                    throw new DatabaseException ("Cannot connect to server.  Contact administrator", ex);
                case 1045:
                    throw new DatabaseException ("Invalid username/password, please try again", ex);
                default:
                    throw new DatabaseException (ex.Message, ex);
                }
            }
        }
        public void Save(SpecificItem obj)
        {
            try {
                List<ColumnValue> columns = new List<ColumnValue> ();
                columns.Add (new ColumnValue () { Item1 = "name", Item2 = obj.Name });
                columns.Add (new ColumnValue () { Item1 = "iconId", Item2 = obj.IconId.ToString() });
                columns.Add (new ColumnValue () { Item1 = "price", Item2 = obj.Price.ToString() });
                if (obj.Theme != null) {
                    columns.Add (new ColumnValue () { Item1 = "themeId", Item2 = obj.Theme.Id.ToString() });
                }
                columns.Add (new ColumnValue () { Item1 = "generalItemId", Item2 = obj.GeneralItem.Id.ToString() });

                StatementValue where = new StatementValue ();
                where.Item1 = "id = @StatementValue0";
                where.Item2 = new List<string> ();
                where.Item2.Add (obj.Id.ToString ());

                this.Update (columns, where);
            } catch (MySqlException ex) {
                switch (ex.Number) {
                case 0:
                    throw new DatabaseException ("Cannot connect to server.  Contact administrator", ex);
                case 1045:
                    throw new DatabaseException ("Invalid username/password, please try again", ex);
                default:
                    throw new DatabaseException (ex.Message, ex);
                }
            }
        }