public Log SelectById(int lid)
{
using (db.GetConnection())
{
db.Connect();
OracleCommand command = db.CreateCommand("SELECT l.lid, l.pocet_vedoucich, l.pocet_deti, l.datum_zalohy, v.vid, v.jmeno, v.heslo, v.datum_narozeni, v.kontakt, f.fid, f.nazev, f.povinnosti FROM Log l LEFT JOIN vedouci v ON v.vid = l.vedouci_vid LEFT JOIN funkce f ON f.fid = v.funkce_fid WHERE lid = :lid");
command.Parameters.AddWithValue(":lid", lid);
Log data = null;
int?funkceID = -1;
var reader = command.ExecuteReader();
while (reader.Read())
{
if (!reader.IsDBNull(5))
{
funkceID = reader.GetInt32(5);
}
Funkce f = new Funkce(reader.GetInt32(9), reader.GetString(10), reader.GetString(11));
Vedouci v = new Vedouci(reader.GetInt32(4), reader.GetString(5), reader.GetString(6), reader.GetDateTime(7), reader.GetString(8), f);
data = new Log(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetDateTime(3), v);
}
reader.Close();
return(data);
}
}
public Deti TryLogin(string nick, string heslo)
{
using (db.GetConnection())
{
db.Connect();
OracleCommand command = db.CreateCommand("SELECT d.did, d.Jmeno, d.Nickname, d.Heslo, d.Datum_Narozeni, d.stav, d.reg_akci,h.hid, h.nazev, h.minimalni_vek, s.sid, s.nazev_druziny, s.pocet_deti, s.datum_konani,v.vid, v.jmeno, v.heslo, v.datum_narozeni, v.kontakt,f.fid, f.nazev, f.povinnosti,r.rid, r.jmeno, r.login, r.heslo, r.kontakt FROM Deti d LEFT JOIN hodnosti h ON h.hid = d.hodnosti_hid LEFT JOIN Schuzky s ON s.sid = d.schuzky_sid LEFT JOIN vedouci v ON v.vid = s.vedouci_vid LEFT JOIN funkce f ON f.fid = v.funkce_fid LEFT JOIN rodic r ON r.rid = d.rodic_rid WHERE d.Nickname = :nick AND d.Heslo = :heslo AND rownum = 1 AND d.stav = 0");
command.Parameters.AddWithValue(":nick", nick);
command.Parameters.AddWithValue(":heslo", heslo);
var reader = command.ExecuteReader();
Deti data = null;
while (reader.Read())
{
Hodnosti h = new Hodnosti(reader.GetInt32(7), reader.GetString(8), reader.GetInt32(9));
Funkce f = new Funkce(reader.GetInt32(19), reader.GetString(20), reader.GetString(21));
Vedouci v = new Vedouci(reader.GetInt32(14), reader.GetString(15), reader.GetString(16), reader.GetDateTime(17), reader.GetString(18), f);
Schuzky s = new Schuzky(reader.GetInt32(10), reader.GetString(11), reader.GetInt32(12), reader.GetInt32(13), v);
Rodic r = new Rodic(reader.GetInt32(22), reader.GetString(23), reader.GetString(24), reader.GetString(25), reader.GetString(26));
data = new Deti(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetDateTime(4), reader.GetInt32(5), h, s, reader.GetInt32(6), r);
}
reader.Close();
return(data);
}
}
//REMOVE 6.2
public void Delete(Vedouci vedouci)
{
using (db.GetConnection()){
db.Connect();
OracleCommand command = db.CreateCommand("DELETE FROM Vedouci WHERE vID = :ID");
command.Parameters.AddWithValue(":ID", vedouci.Vid);
command.ExecuteNonQuery();
}
}
public Tuple <int, string, string> /*<id,nick,role>*/ LoginAs(string username, string pw)
{
VedouciDataMapper vdm = new VedouciDataMapper();
Vedouci d = vdm.TryLogin(username, pw);
if (d != null)
{
return(new Tuple <int, string, string>(d.Vid, d.Jmeno, "Administrator"));
}
return(null);
}
//UPDATE 6.3
public void Update(Vedouci vedouci)
{
using (db.GetConnection())
{
db.Connect();
OracleCommand command = db.CreateCommand("UPDATE Vedouci SET Jmeno = :Jmeno, heslo = :heslo, Datum_narozeni = :Datum_narozeni, Kontakt = :Kontakt WHERE vid = :vid");
command.Parameters.AddWithValue(":vid", vedouci.Vid);
command.Parameters.AddWithValue(":Jmeno", vedouci.Jmeno);
command.Parameters.AddWithValue(":heslo", vedouci.Heslo);
command.Parameters.AddWithValue(":Datum_narozeni", vedouci.Datum_narozeni);
command.Parameters.AddWithValue(":Kontakt", vedouci.Kontakt);
command.ExecuteNonQuery();
}
}
//INSERT 6.1
public void Insert(Vedouci vedouci)
{
using (db.GetConnection())
{
db.Connect();
OracleCommand command = db.CreateCommand("INSERT INTO Vedouci (vid, jmeno, heslo, datum_narozeni, kontakt) VALUES (:vid, :Jmeno, :heslo, :Datum_narozeni, :Kontakt)");
command.Parameters.AddWithValue(":vid", vedouci.Vid);
command.Parameters.AddWithValue(":Jmeno", vedouci.Jmeno);
command.Parameters.AddWithValue(":heslo", vedouci.Heslo);
command.Parameters.AddWithValue(":Datum_narozeni", vedouci.Datum_narozeni);
command.Parameters.AddWithValue(":Kontakt", vedouci.Kontakt);
command.ExecuteNonQuery();
}
}
public void SpustVyzkum()
{
Vedouci vedouci = new Vedouci("Meno Fero");
LaboratorniPristroj1 = new Mikroskop();
LaboratorniPristroj2 = new Odstredivka();
LaboratorniPristroj1.Nazev = "Kukalor hn301";
LaboratorniPristroj2.Nazev = "Odstredka ods56";
IOvladani ovladani = (IOvladani)LaboratorniPristroj1;
ovladani.Zapni();
LaboratorniPristroj1.VypisDetailniInfo();
LaboratorniPristroj2.VypisDetailniInfo();
}
public void SpustVyzkum()
{
Vedouci vedouci = new Vedouci("Meno Fero");
LaboratorniPristroj1 = new Mikroskop();
LaboratorniPristroj2 = new odstredivka();
LaboratorniPristroj1.Nazev = "hn301";
LaboratorniPristroj2.Nazev = "ods56";
Ovladanie ovladani = (Ovladanie)LaboratorniPristroj1;
ovladani.Zapni();
LaboratorniPristroj1.VypisDetailniInfo();
LaboratorniPristroj2.VypisDetailniInfo();
}
public void ExportToCSV(string path)
{
using (db.GetConnection())
{
db.Connect();
using (var w = new StreamWriter(path))
{
List <Vedouci> toCSV = SelectAll();
for (int i = 0; i < toCSV.Count; i++)
{
Vedouci v = toCSV[i];
string line = v.Vid + ", " + v.Jmeno + ", " + v.Heslo + ", " + v.Datum_narozeni + ", " + v.Kontakt;
w.WriteLine(line);
w.Flush();
}
}
}
}
//SelectUpcoming 1.5
public List <Akce> SelectUpcoming()
{
using (db.GetConnection())
{
db.Connect();
OracleCommand command = db.CreateCommand("SELECT a.aid, a.Nazev, a.datum_konani, a.Cena, a.max_pocet_deti, v.vid, v.jmeno, v.heslo, v.datum_narozeni, v.kontakt, f.fid, f.nazev, f.povinnosti , h.hid, h.nazev, h.minimalni_vek FROM Akce a JOIN hodnosti h ON h.hid = a.hodnosti_hid JOIN vedouci v ON v.vid = a.vedouci_vid JOIN funkce f ON f.fid = v.funkce_fid WHERE a.datum_konani> CURRENT_DATE");
List <Akce> data = new List <Akce>();
var reader = command.ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(0);
int?cena;
int?max_pocet_deti;
if (!reader.IsDBNull(3))
{
cena = reader.GetInt32(3);
}
else
{
cena = null;
}
if (!reader.IsDBNull(4))
{
max_pocet_deti = reader.GetInt32(4);
}
else
{
max_pocet_deti = 300;
}
Hodnosti h = new Hodnosti(reader.GetInt32(13), reader.GetString(14), reader.GetInt32(15));
Vedouci v = new Vedouci(reader.GetInt32(5), reader.GetString(6), reader.GetString(7), reader.GetDateTime(8), reader.GetString(9), new Funkce(reader.GetInt32(10), reader.GetString(11), reader.GetString(12)));
data.Add(new Akce(id, reader.GetString(1), reader.GetDateTime(2), cena, v, h, max_pocet_deti));
}
reader.Close();
return(data);;
}
}
public Vedouci SelectById(int vid)
{
using (db.GetConnection())
{
db.Connect();
OracleCommand command = db.CreateCommand("SELECT v.vid, v.jmeno, v.heslo, v.datum_narozeni, v.kontakt, f.fid, f.nazev, f.povinnosti FROM vedouci v LEFT JOIN funkce f ON f.fid = v.funkce_fid WHERE v.vid = :vid");
command.Parameters.AddWithValue(":vid", vid);
Vedouci data = null;
var reader = command.ExecuteReader();
while (reader.Read())
{
Funkce f = new Funkce(reader.GetInt32(5), reader.GetString(6), reader.GetString(7));
data = new Vedouci(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetDateTime(3), reader.GetString(4), f);
}
reader.Close();
return(data);
}
}
//SelectAll 5.4
public List <Schuzky> SelectAll()
{
using (db.GetConnection())
{
db.Connect();
OracleCommand command = db.CreateCommand("SELECT s.sid,s.nazev_druziny, s.pocet_deti, s.datum_konani, v.vid, v.jmeno, v.heslo, v.datum_narozeni, v.kontakt, f.fid, f.nazev, f.povinnosti FROM Schuzky s LEFT JOIN vedouci v ON v.vid = s.vedouci_vid LEFT JOIN funkce f ON f.fid = v.funkce_fid");
List <Schuzky> data = new List <Schuzky>();
var reader = command.ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(0);
//nová instance vedoucího, upravit select
Funkce f = new Funkce(reader.GetInt32(9), reader.GetString(10), reader.GetString(11));
Vedouci v = new Vedouci(reader.GetInt32(4), reader.GetString(5), reader.GetString(6), reader.GetDateTime(7), reader.GetString(8), f);
data.Add(new Schuzky(id, reader.GetString(1), reader.GetInt32(2), reader.GetInt32(3), v));
}
reader.Close();
return(data);
}
}
//SelectWithDeti 5.5
public List <Tuple <Deti, Schuzky> > SelectWithDeti()
{
using (db.GetConnection())
{
db.Connect();
OracleCommand command = db.CreateCommand("SELECT d.did, d.Jmeno, d.Nickname, d.Heslo, d.Datum_Narozeni, d.stav, d.reg_akci,h.hid, h.nazev, h.minimalni_vek, s.sid, s.nazev_druziny, s.pocet_deti, s.datum_konani,v.vid, v.jmeno, v.heslo, v.datum_narozeni, v.kontakt,f.fid, f.nazev, f.povinnosti,r.rid, r.jmeno, r.login, r.heslo, r.kontakt FROM Deti d LEFT JOIN hodnosti h ON h.hid = d.hodnosti_hid LEFT JOIN Schuzky s ON s.sid = d.schuzky_sid LEFT JOIN vedouci v ON v.vid = s.vedouci_vid LEFT JOIN funkce f ON f.fid = v.funkce_fid LEFT JOIN rodic r ON r.rid = d.rodic_rid");
List <Tuple <Deti, Schuzky> > data = new List <Tuple <Deti, Schuzky> >();
var reader = command.ExecuteReader();
while (reader.Read())
{
Hodnosti h = new Hodnosti(reader.GetInt32(7), reader.GetString(8), reader.GetInt32(9));
Funkce f = new Funkce(reader.GetInt32(19), reader.GetString(20), reader.GetString(21));
Vedouci v = new Vedouci(reader.GetInt32(14), reader.GetString(15), reader.GetString(16), reader.GetDateTime(17), reader.GetString(18), f);
Schuzky s = new Schuzky(reader.GetInt32(10), reader.GetString(11), reader.GetInt32(12), reader.GetInt32(13), v);
Rodic r = new Rodic(reader.GetInt32(22), reader.GetString(23), reader.GetString(24), reader.GetString(25), reader.GetString(26));
data.Add(new Tuple <Deti, Schuzky>(new Deti(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetDateTime(4), reader.GetInt32(5), h, s, reader.GetInt32(6), r), s));
}
reader.Close();
return(data);
}
}
public Schuzky SelectById(int sid)
{
using (db.GetConnection())
{
db.Connect();
OracleCommand command = db.CreateCommand("SELECT s.sid,s.nazev_druziny, s.pocet_deti, s.datum_konani, v.vid, v.jmeno, v.heslo, v.datum_narozeni, v.kontakt, f.fid, f.nazev, f.povinnosti FROM Schuzky s LEFT JOIN vedouci v ON v.vid = s.vedouci_vid LEFT JOIN funkce f ON f.fid = v.funkce_fid WHERE sid = :sid");
command.Parameters.AddWithValue(":sid", sid);
Schuzky data = null;
var reader = command.ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(0);
Funkce f = new Funkce(reader.GetInt32(9), reader.GetString(10), reader.GetString(11));
Vedouci v = new Vedouci(reader.GetInt32(4), reader.GetString(5), reader.GetString(6), reader.GetDateTime(7), reader.GetString(8), f);
data = new Schuzky(id, reader.GetString(1), reader.GetInt32(2), reader.GetInt32(3), v);
}
reader.Close();
return(data);
}
}
