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