public Replica GetByID(int ID)
{
DataTable dt = new DataTable();
DBEntity db = new DBEntity();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = String.Format(@"SELECT *
FROM repliche WHERE id_replica={0}", ID);
Replica R = null;
dt = db.eseguiQuery(cmd);
if (dt.Rows.Count > 0)
{
R = new Replica();
R.ID = (int)dt.Rows[0]["id_replica"];
R.Data = (Int64)dt.Rows[0]["data"];
R.Annullato = (bool)dt.Rows[0]["annullato"];
R.IDEvento = (int)dt.Rows[0]["fk_evento"];
R.Evento = new daoEvento().GetByID(R.IDEvento);
R.PostiOccupati = this.GetPostiOccupati(R.ID);
}
return(R);
}
public Evento GetByID(int ID)
{
DataTable dt = new DataTable();
DBEntity db = new DBEntity();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = String.Format(@"SELECT *
FROM eventi WHERE id_evento={0}", ID);
Evento E = null;
dt = db.eseguiQuery(cmd);
if (dt.Rows.Count > 0)
{
E = new Evento();
E.ID = (int)dt.Rows[0]["id_evento"];
E.Titolo = (string)dt.Rows[0]["titolo"];
E.ImagePath = (string)dt.Rows[0]["image"];
E.IDLocale = (int)dt.Rows[0]["fk_locale"];
}
E.Locale = new daoLocale().GetByID(E.IDLocale);
return(E);
}
public List <Replica> GetByTime()
{
DataTable dt = new DataTable();
DBEntity db = new DBEntity();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = String.Format(@"SELECT * FROM repliche WHERE data > '{0}' ORDER BY data DESC",
Convert.ToInt64(DateTime.UtcNow.Subtract(new DateTime(1970, 1, 1)).TotalSeconds));
List <Replica> repliche = null;
dt = db.eseguiQuery(cmd);
if (dt.Rows.Count > 0)
{
repliche = new List <Replica>();
foreach (DataRow dr in dt.Rows)
{
Replica R = new Replica();
R.ID = (int)dr["id_replica"];
R.Data = (Int64)dr["data"];
R.Annullato = (bool)dr["annullato"];
R.IDEvento = (int)dr["fk_evento"];
R.Evento = new daoEvento().GetByID(R.IDEvento);
R.PostiOccupati = this.GetPostiOccupati(R.ID);
repliche.Add(R);
}
}
return(repliche);
}
public Prenotazione GetByID(int ID)
{
DataTable dt = new DataTable();
DBEntity db = new DBEntity();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = String.Format(@"SELECT *
FROM prenotazioni WHERE id_pren={0}", ID);
Prenotazione P = null;
dt = db.eseguiQuery(cmd);
if (dt.Rows.Count > 0)
{
P = new Prenotazione();
P.ID = (int)dt.Rows[0]["id_pren"];
P.Biglietti = (int)dt.Rows[0]["qta"];
P.Data = (Int64)dt.Rows[0]["data"];
P.IDUtente = (int)dt.Rows[0]["fk_utente"];
P.IDReplica = (int)dt.Rows[0]["fk_replica"];
}
P.Replica = new daoReplica().GetByID(P.IDReplica);
return(P);
}
public List <Prenotazione> GetByReplica(int RID)
{
DataTable dt = new DataTable();
DBEntity db = new DBEntity();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = String.Format(@"SELECT *
FROM prenotazioni WHERE fk_replica={0}", RID);
List <Prenotazione> prenotazioni = null;
dt = db.eseguiQuery(cmd);
if (dt.Rows.Count > 0)
{
prenotazioni = new List <Prenotazione>();
foreach (DataRow dr in dt.Rows)
{
Prenotazione P = new Prenotazione();
P.ID = (int)dr["id_pren"];
P.Biglietti = (int)dr["qta"];
P.Data = (Int64)dr["data"];
P.IDUtente = (int)dr["fk_utente"];
P.IDReplica = (int)dr["fk_replica"];
P.Replica = new daoReplica().GetByID(P.IDReplica);
prenotazioni.Add(P);
}
}
return(prenotazioni);
}
public Utente Login(Utente U)
{
DataTable dt = new DataTable();
DBEntity db = new DBEntity();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = String.Format(@"SELECT * FROM utenti WHERE email='{0}' AND password='******'", U.Email, U.Password);
dt = db.eseguiQuery(cmd);
if (dt.Rows.Count == 1)
{
U.ID = (int)dt.Rows[0]["id_utente"];
U.Cognome = (string)dt.Rows[0]["cognome"];
U.Nome = (string)dt.Rows[0]["nome"];
U.Telefono = (string)dt.Rows[0]["telefono"];
U.Email = (string)dt.Rows[0]["email"];
U.Password = (string)dt.Rows[0]["password"];
}
else
{
return(null);
}
//U.Prenotazioni = new daoPrenotazioni().GetByUtente(U.ID);
return(U);
}
public int GetPostiOccupati(int ID)
{
DataTable dt = new DataTable();
DBEntity db = new DBEntity();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = String.Format(@"SELECT SUM(qta) AS postioccupati
FROM prenotazioni WHERE fk_replica={0}", ID);
int postiOccupati = 0;
dt = db.eseguiQuery(cmd);
if (dt.Rows.Count > 0)
{
postiOccupati = dt.Rows[0].IsNull("postioccupati") ? 0 : (int)dt.Rows[0]["postioccupati"];
}
return(postiOccupati);
}
public Locale GetByID(int ID)
{
DataTable dt = new DataTable();
DBEntity db = new DBEntity();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = String.Format(@"SELECT *
FROM locali WHERE id_locale={0}", ID);
Locale L = null;
dt = db.eseguiQuery(cmd);
if (dt.Rows.Count > 0)
{
L = new Locale();
L.ID = (int)dt.Rows[0]["id_locale"];
L.Nome = (string)dt.Rows[0]["nome"];
L.Luogo = (string)dt.Rows[0]["luogo"];
L.Posti = (int)dt.Rows[0]["posti"];
}
return(L);
}
