public static Collection <Person> SelectTeammates(int pID, DatabaseT pDb = null)
{
DatabaseT db;
if (pDb == null)
{
db = new DatabaseT();
db.Connect();
}
else
{
db = (DatabaseT)pDb;
}
Person tmp = SelectOne(pID);
OracleCommand command = db.CreateCommand(SQL_SELECT_TEAMMATES);
command.Parameters.AddWithValue(":id", pID);
command.Parameters.AddWithValue(":tID", tmp.Team_Id.Id);
OracleDataReader reader = db.Select(command);
Collection <Person> Users = Read(reader);
reader.Close();
if (pDb == null)
{
db.Close();
}
return(Users);
}
public static Organisation SelectOne(int pID, DatabaseT pDb = null)
{
DatabaseT db;
if (pDb == null)
{
db = new DatabaseT();
db.Connect();
}
else
{
db = (DatabaseT)pDb;
}
OracleCommand command = db.CreateCommand(SQL_SELECT_ONE);
command.Parameters.AddWithValue(":id", pID);
OracleDataReader reader = db.Select(command);
Organisation Users = Read(reader).First();
reader.Close();
if (pDb == null)
{
db.Close();
}
return(Users);
}
public static Collection <Team> SelectByParam(string name = null, DatabaseT pDb = null)
{
DatabaseT db;
if (pDb == null)
{
db = new DatabaseT();
db.Connect();
}
else
{
db = (DatabaseT)pDb;
}
OracleCommand command = db.CreateCommand(SQL_SELECT_PARAM);
command.Parameters.AddWithValue(":name", name);
OracleDataReader reader = db.Select(command);
Collection <Team> g = Read(reader);
reader.Close();
if (pDb == null)
{
db.Close();
}
return(g);
}
public static int Update(Person p, DatabaseT pDb = null)
{
DatabaseT db = new DatabaseT();
db.Connect();
db.BeginTransaction();
Person prv = SelectOne(p.Id);
if (p.Role != "Coach" && prv.Role == "Coach")
{
Team ot = TeamTable.SelectOne(p.Team_Id.Id);
ot.Person_Id = null;
TeamTable.Update(ot);
}
OracleCommand command = db.CreateCommand(SQL_UPDATE);
PrepareCommand(command, p);
int ret = db.ExecuteNonQuery2(command);
db.EndTransaction();
db.Close();
return(ret);
}
public static Collection <Event> SelectAllFromOrganiser(string or, DatabaseT pDb = null)
{
DatabaseT db;
if (pDb == null)
{
db = new DatabaseT();
db.Connect();
}
else
{
db = (DatabaseT)pDb;
}
OracleCommand command = db.CreateCommand(SQL_SELECT_ONE_PAR);
command.Parameters.AddWithValue(":org", or);
command.Parameters.AddWithValue(":ed", DateTime.Now.Year.ToString());
OracleDataReader reader = db.Select(command);
Collection <Event> Users = Read(reader);
reader.Close();
if (pDb == null)
{
db.Close();
}
return(Users);
}
public static int SelectTeamCountGame(int oID, int gID, DatabaseT pDb = null)
{
DatabaseT db;
if (pDb == null)
{
db = new DatabaseT();
db.Connect();
}
else
{
db = (DatabaseT)pDb;
}
OracleCommand command = db.CreateCommand(SQL_SELECT_TEAM_COUNT_gamePAR);
command.Parameters.AddWithValue(":oID", oID);
command.Parameters.AddWithValue(":gID", gID);
OracleDataReader reader = db.Select(command);
int res = ReadCount(reader);
reader.Close();
if (pDb == null)
{
db.Close();
}
return(res);
}
public static int SelectLast(DatabaseT pDb = null)
{
DatabaseT db;
if (pDb == null)
{
db = new DatabaseT();
db.Connect();
}
else
{
db = (DatabaseT)pDb;
}
OracleCommand command = db.CreateCommand(SQL_SELECT_LAST);
OracleDataReader reader = db.Select(command);
int res = ReadLast(reader);
reader.Close();
if (pDb == null)
{
db.Close();
}
return(res);
}
public static Collection <Person> SelectPeople(int oID, DatabaseT pDb = null)
{
DatabaseT db;
if (pDb == null)
{
db = new DatabaseT();
db.Connect();
}
else
{
db = (DatabaseT)pDb;
}
OracleCommand command = db.CreateCommand(SQL_SELECT_P);
command.Parameters.AddWithValue(":id", oID);
OracleDataReader reader = db.Select(command);
Collection <Person> Users = ReadP(reader);
reader.Close();
if (pDb == null)
{
db.Close();
}
return(Users);
}
public static Collection <Game> Select(DatabaseT pDb = null)
{
DatabaseT db;
if (pDb == null)
{
db = new DatabaseT();
db.Connect();
}
else
{
db = (DatabaseT)pDb;
}
OracleCommand command = db.CreateCommand(SQL_SELECT);
OracleDataReader reader = db.Select(command);
Collection <Game> Users = Read(reader);
reader.Close();
if (pDb == null)
{
db.Close();
}
return(Users);
}
public static int UpdateOrg(Team t, int oID, DatabaseT pDb = null)
{
DatabaseT db = new DatabaseT();
db.Connect();
int count = 0;
try
{
count = OrganisationTable.SelectTeamCountGame(oID, t.Game_Id.Id);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(-1);
}
if (count > 0)
{
Console.WriteLine("Organizace uz ma tym pro tuto hru");
return(-1);
}
OracleCommand command = db.CreateCommand(SQL_UPDATE_ORG);
command.Parameters.AddWithValue(":id", t.Id);
command.Parameters.AddWithValue(":voID", oID);
int ret = db.ExecuteNonQuery(command);
db.Close();
return(ret);
}
public static Collection <Person> SelectByParameter(string attr = null, DatabaseT pDb = null)
{
DatabaseT db;
if (pDb == null)
{
db = new DatabaseT();
db.Connect();
}
else
{
db = (DatabaseT)pDb;
}
OracleCommand command = db.CreateCommand(SQL_SELECT_SEARCH);
attr = "%" + attr + "%";
command.Parameters.AddWithValue(":attr", attr);
OracleDataReader reader = db.Select(command);
Collection <Person> Users = Read(reader);
reader.Close();
if (pDb == null)
{
db.Close();
}
return(Users);
}
public static int Update(Organisation o, DatabaseT pDb = null)
{
DatabaseT db = new DatabaseT();
db.Connect();
OracleCommand command = db.CreateCommand(SQL_UPDATE);
PrepareCommand(command, o);
int ret = db.ExecuteNonQuery(command);
db.Close();
return(ret);
}
public static int Delete(int id, DatabaseT pDb = null)
{
DatabaseT db = new DatabaseT();
db.Connect();
OracleCommand command = db.CreateCommand(SQL_DELETE_ID);
command.Parameters.AddWithValue(":id", id);
int ret = db.ExecuteNonQuery(command);
db.Close();
return(ret);
}
public static int Insert(Game game, DatabaseT pDb = null)
{
DatabaseT db = new DatabaseT();
db.Connect();
OracleCommand command = db.CreateCommand(SQL_INSERT);
PrepareCommand(command, game);
int ret = db.ExecuteNonQuery(command);
db.Close();
return(ret);
}
public static int UpdateGame(int tID, int gID, DatabaseT pDb = null)
{
DatabaseT db = new DatabaseT();
db.Connect();
OracleCommand command = db.CreateCommand(SQL_UPDATE_GAME);
command.Parameters.AddWithValue(":team", gID);
command.Parameters.AddWithValue(":game", tID);
int ret = db.ExecuteNonQuery(command);
db.Close();
return(ret);
}
public static int PridatAkci(Event e, DatabaseT pDb = null)
{
DatabaseT db = new DatabaseT();
db.Connect();
int event_id = 0;
if (e.Prizepool == null)
{
e.Prizepool = 0;
}
if (SelectAllFromOrganiser(e.Organiser).Count > 500)
{
Console.WriteLine("Prekrocen maximalni pocet akci");
return(-1);
}
event_id = SelectLast();
e.Id = ++event_id;
OracleCommand command = db.CreateCommand(SQL_INSERT);
PrepareCommand(command, e);
int ret = 0;
try
{
ret = db.ExecuteNonQuery(command);
}
catch (Exception er)
{
Console.WriteLine(er.Message);
}
db.Close();
return(ret);
}
public static int Delete(int id, DatabaseT pDb = null)
{
DatabaseT db = new DatabaseT();
db.Connect();
OracleCommand command = db.CreateCommand(SQL_DELETE_ID);
Person p = PersonTable.SelectOne(id);
if (p.Role == "Coach")
{
Team ot = TeamTable.SelectOne(p.Team_Id.Id);
ot.Person_Id = null;
TeamTable.Update(ot);
}
command.Parameters.AddWithValue(":id", id);
int ret = db.ExecuteNonQuery(command);
db.Close();
return(ret);
}
public static int UpdateManager(Organisation o, int pID, bool is_manager, DatabaseT pDb = null)
{
DatabaseT db = new DatabaseT();
db.Connect();
db.BeginTransaction();
if (SelectOne(o.Id) == null)
{
Console.WriteLine("Organizace nenalezena");
db.Rollback();
return(-1);
}
if (PersonTable.SelectOne(pID) == null)
{
Console.WriteLine("Osoba nenalezena");
db.Rollback();
return(-1);
}
if (o.Person_Id.Id == pID)
{
Console.WriteLine("Osoba j*z je kontaktni osobou organizace");
db.Rollback();
return(-1);
}
try
{
Person p = new Person();
p = PersonTable.SelectOne(pID);
if (is_manager)
{
p.Role = "Manager";
}
else
{
p.Role = null;
}
PersonTable.Update(p);
o.Person_Id.Id = pID;
Update(o);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
db.Rollback();
return(-1);
}
OracleCommand command = db.CreateCommand(SQL_UPDATE_MAN);
PrepareCommand(command, o);
int ret = db.ExecuteNonQuery2(command);
db.EndTransaction();
db.Close();
return(ret);
}
public static int Prestup(int pID, int tID, DatabaseT pDb = null)
{
DatabaseT db = new DatabaseT();
db.Connect();
Person p = null;
Team t = null;
db.BeginTransaction();
try
{
p = SelectOne(pID);
t = TeamTable.SelectOne(tID);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(-1);
}
if (p.Game_Id.Id != t.Game_Id.Id)
{
p.Game_Id.Id = t.Game_Id.Id;
try
{
Update(p, db);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
db.Rollback();
return(-1);
}
}
if (p.Role == "Coach")
{
try
{
Team ot = TeamTable.SelectOne(p.Team_Id.Id);
ot.Person_Id = null;
TeamTable.Update(ot);
ot = TeamTable.SelectOne(tID);
ot.Person_Id = p;
TeamTable.Update(ot);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
db.Rollback();
return(-1);
}
}
OracleCommand command = db.CreateCommand(SQL_UPDATE_PRESTUP);
command.Parameters.AddWithValue(":team_id", tID);
command.Parameters.AddWithValue(":id", pID);
int ret = db.ExecuteNonQuery2(command);
command.Dispose();
db.EndTransaction();
if (pDb == null)
{
db.Close();
}
return(ret);
}
public static int ZmenitHru(Team t, int gID, DatabaseT pDb = null)
{
DatabaseT db;
if (pDb == null)
{
db = new DatabaseT();
db.Connect();
}
else
{
db = (DatabaseT)pDb;
}
db.BeginTransaction();
if (SelectOne(t.Id) == null)
{
db.Rollback();
return(-1);
}
if (GameTable.SelectOne(gID) == null)
{
db.Rollback();
return(-1);
}
try
{
PersonTable.UpdateGame(t.Id, gID);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
db.Rollback();
return(-1);
}
try
{
t.Game_Id = GameTable.SelectOne(gID);
Update(t);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
db.Rollback();
return(-1);
}
OracleCommand command = db.CreateCommand(SQL_SELECT_COUNT);
command.Parameters.AddWithValue(":gID", gID);
OracleDataReader reader = db.Select(command);
int res = ReadCount(reader);
reader.Close();
db.EndTransaction();
if (pDb == null)
{
db.Close();
}
return(res);
}
