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);
}
}
//UPDATE 7.3
public void Update(Hodnosti hodnosti)
{
using (db.GetConnection())
{
db.Connect();
OracleCommand command = db.CreateCommand("UPDATE Hodnosti SET Nazev = :Nazev, minimalni_vek = :minimalni_vek WHERE hid = :hid");
command.Parameters.AddWithValue(":hid", hodnosti.Hid);
command.Parameters.AddWithValue(":Nazev", hodnosti.Nazev);
command.Parameters.AddWithValue(":minimalni_vek", hodnosti.Minimalni_vek);
command.ExecuteNonQuery();
}
}
//INSERT 7.1
public void Insert(Hodnosti hodnosti)
{
using (db.GetConnection())
{
db.Connect();
OracleCommand command = db.CreateCommand("INSERT INTO Hodnosti (hid, Nazev, minimalni_vek) VALUES (:hid, :Nazev, :minimalni_vek)");
command.Parameters.AddWithValue(":hid", hodnosti.Hid);
command.Parameters.AddWithValue(":Nazev", hodnosti.Nazev);
command.Parameters.AddWithValue(":minimalni_vek", hodnosti.Minimalni_vek);
command.ExecuteNonQuery();
}
}
//DELETE NOT USED! 7.2
public void ExportToCSV(string path)
{
using (db.GetConnection())
{
db.Connect();
using (var w = new StreamWriter(path))
{
List <Hodnosti> toCSV = SelectAll();
for (int i = 0; i < toCSV.Count; i++)
{
Hodnosti v = toCSV[i];
string line = v.Hid + ", " + v.Nazev + ", " + v.Minimalni_vek;
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 Hodnosti SelectById(int hid)
{
using (db.GetConnection())
{
db.Connect();
OracleCommand command = db.CreateCommand("SELECT hid, nazev, minimalni_vek FROM Hodnosti WHERE hid = :hid AND rownum = 1");
command.Parameters.AddWithValue(":hid", hid);
Hodnosti data = null;
var reader = command.ExecuteReader();
while (reader.Read())
{
data = new Hodnosti(reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2));
}
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);
}
}
static void Main(string[] args)
{
Database db = new Database();
db.Connect();
AkceDataMapper adm = new AkceDataMapper();
AkceDetiDataMapper addm = new AkceDetiDataMapper();
DetiDataMapper ddm = new DetiDataMapper();
FunkceDataMapper fdm = new FunkceDataMapper();
HodnostiDataMapper hdm = new HodnostiDataMapper();
RodicDataMapper rdm = new RodicDataMapper();
SchuzkyDataMapper sdm = new SchuzkyDataMapper();
VedouciDataMapper vdm = new VedouciDataMapper();
LogDataMapper ldm = new LogDataMapper();
DateTime datum = DateTime.Today;
//Pridani nove akce 1.1, Pokud akce s danym id v databazi existuje tak se jen updatne 1.3
List <Akce> SelectAkce = adm.SelectAll();
//Akce novaAkce = new Akce(SelectAkce.Count(), "Nova Akce z Csharp", datum, 0, vdm.SelectById(1), hdm.SelectById(1), 40);
//Akce pridanaAkce = adm.SelectById(SelectAkce.Count());
//Console.WriteLine("Pridana akce do databaze:" + pridanaAkce.Aid.ToString() + "|" + pridanaAkce.Nazev.ToString() + "|" + pridanaAkce.Datum_konani.ToString() + "|" + pridanaAkce.Cena.ToString(), pridanaAkce.Hodnosti_hid.Nazev);
//1.1
//adm.Insert(novaAkce);
//1.3
//adm.Update(novaAkce);
//Select vsech akci 1.4
Console.WriteLine();
Console.WriteLine("1.4");
Console.WriteLine("Aid|Nazev|Datum_konani|Cena|Max_pocet_deti|Vedouci_vid|Hodnosti_hid");
for (int i = 0; i < SelectAkce.Count(); i++)
{
Console.WriteLine(SelectAkce[i].Aid + "|" + SelectAkce[i].Nazev + "|" + SelectAkce[i].Datum_konani + "|" + SelectAkce[i].Cena + "|" + SelectAkce[i].Max_pocet_deti + "|" + SelectAkce[i].Vedouci_vid.Vid + "|" + SelectAkce[i].Hodnosti_hid.Hid);
}
//Pridani noveho vedouciho 2.1, pokud vedouci s danym id v databazi neexistuje tak se provede update nad danym id 2.3
List <Rodic> SelectRodic = rdm.SelectAll();
//Rodic novyRodic = new Rodic(SelectRodic.Count()+1, "Novy rodic z Csharp", "CSHARPLOGIN", "CSHARPPW", "*****@*****.**");
//rdm.Insert(novyRodic);
Rodic pridanyRodic = rdm.SelectById(SelectRodic.Count());
//Console.WriteLine("Pridany rodic do databaze:" + pridanyRodic.Rid + "|" + pridanyRodic.Jmeno + "|" + pridanyRodic.Login + "|" + pridanyRodic.Heslo + "|" + pridanyRodic.Kontakt);
//Odebrani rodice 2.2
//rdm.Delete(pridanyRodic);
//2.3
//rdm.Update(novyRodic);
//Select vsech vedoucich 2.4
Console.WriteLine();
Console.WriteLine("2.4");
Console.WriteLine("rid|jmeno|login|heslo|kontakt");
for (int i = 0; i < SelectRodic.Count(); i++)
{
Console.WriteLine(SelectRodic[i].Rid + "|" + SelectRodic[i].Jmeno + "|" + SelectRodic[i].Login + "|" + SelectRodic[i].Heslo + "|" + SelectRodic[i].Kontakt);
}
//Pridani noveho ditete 3.1
List <Deti> SelectDeti = ddm.SelectAll();
//Deti noveDite = new Deti(SelectDeti.Count()+1,"Nove dite", "NvDt", "NvDtPw", datum, 0, hdm.SelectById(1),sdm.SelectById(1),0, rdm.SelectById(1));
//ddm.Insert(noveDite);
//3.2
//ddm.Delete(noveDite);
//3.3
//ddm.Update(noveDite);
//3.4
Console.WriteLine();
Console.WriteLine("3.4");
Console.WriteLine("did|jmeno|nickname|heslo|datum narozeni|stav|hodnost|schuzky|registrovanych akci|rodic");
for (int i = 0; i < SelectDeti.Count(); i++)
{
Console.WriteLine(SelectDeti[i].Did + "|" + SelectDeti[i].Jmeno + "|" + SelectDeti[i].Nickname + "|" + SelectDeti[i].Heslo + "|" + SelectDeti[i].Datum_narozeni + "|" + SelectDeti[i].Stav + "|" + SelectDeti[i].Hodnosti_hid.Hid + "|" + SelectDeti[i].Schuzky_sid.Sid + "|" + SelectDeti[i].Reg_akci + "|" + SelectDeti[i].Rodic_rid.Rid);
}
//3.5 - Netrivialni select
Console.WriteLine();
Console.WriteLine("3.5");
List <Tuple <int, string, int, int> > ret = ddm.NejpocetnejsiAkce();
for (int i = 0; i < ret.Count(); i++)
{
Console.WriteLine(ret[i].Item1 + "|" + ret[i].Item2 + "|" + ret[i].Item3 + "|" + ret[i].Item4);
}
//4.1 - funkce (stored procedure)
//fdm.NovaFunkce("random funkce", random povinnosti");
//4.4
Console.WriteLine();
Console.WriteLine("4.4");
List <Funkce> SelectFunkce = fdm.SelectAll();
Console.WriteLine("fid|nazev|povinnosti");
for (int i = 0; i < SelectFunkce.Count(); i++)
{
Console.WriteLine(SelectFunkce[i].Fid + "|" + SelectFunkce[i].Nazev + "|" + SelectFunkce[i].Povinnosti);
}
//4.3
/*Funkce f = SelectFunkce[SelectFunkce.Count() - 1];
* f.Povinnosti = "zmena povinnosti";
* fdm.Update(f);
*/
//5.1 - schuzky
List <Schuzky> SelectSchuzky = sdm.SelectAll();
//Schuzky novaSchuzka = new Schuzky(SelectAkce.Count(), "Nova schuzka", 12, 4, vdm.SelectById(1));
//sdm.Insert(novaSchuzka);
//5.3
//sdm.Update(novaSchuzka);
//5.4
Console.WriteLine();
Console.WriteLine("5.4");
Console.WriteLine("sid|nazev_druziny|pocet_deti|datum_konani|vedouci_vid");
for (int i = 0; i < SelectSchuzky.Count(); i++)
{
Console.WriteLine(SelectSchuzky[i].Sid + "|" + SelectSchuzky[i].Nazev + "|" + SelectSchuzky[i].Pocet_Deti + "|" + SelectSchuzky[i].Datum_konani + "|" + SelectSchuzky[i].Vedouci_vid.Vid);
}
Console.WriteLine();
Console.WriteLine("5.5");
//5.5
List <Tuple <Deti, Schuzky> > DetiSchuzky = sdm.SelectWithDeti();
for (int i = 0; i < DetiSchuzky.Count(); i++)
{
Console.WriteLine(DetiSchuzky[i].Item2.Nazev + "|" + DetiSchuzky[i].Item1.Jmeno);
}
//6.1 - vedouci
List <Vedouci> SelectVedouci = vdm.SelectAll();
//Vedouci novyVedouci = new Vedouci(SelectVedouci.Count()+1, "Novy Vedouci", "NewPwVedouci", datum, "mejlnovehovedouciho@mejl", null);
//vdm.Insert(novyVedouci);
//6.2
//vdm.Delete(novyVedouci);
//6.3
//vdm.Update(novyVedouci);
//6.4
Console.WriteLine();
Console.WriteLine("6.4");
Console.WriteLine("vid|jmeno|heslo|datum_narozeni|kontakt|funkce_fid");
for (int i = 0; i < SelectVedouci.Count(); i++)
{
Console.WriteLine(SelectVedouci[i].Vid + "|" + SelectVedouci[i].Jmeno + "|" + SelectVedouci[i].Heslo + "|" + SelectVedouci[i].Datum_narozeni + "|" + SelectVedouci[i].Kontakt + "|" + SelectVedouci[i].Funkce_fid.Fid);
}
//6.5 - StoredProcedure
//vdm.DiteBecomeVedouci(1);
//6.6 - netrivialni select
Console.WriteLine();
Console.WriteLine("6.6");
var test = vdm.VedouciSchuzkaDite(5);
Console.WriteLine(test.Item1 + "|" + test.Item2 + "|" + test.Item3);
//7.1 - Hodnost
List <Hodnosti> SelectHodnosti = hdm.SelectAll();
Hodnosti NovaHodnost = new Hodnosti(SelectHodnosti.Count(), "Nova Hodnost", 8);
//hdm.Insert(NovaHodnost);
//7.3
//hdm.Update(NovaHodnost);
//7.4
Console.WriteLine();
Console.WriteLine("7.4");
for (int i = 0; i < SelectHodnosti.Count(); i++)
{
Console.WriteLine(SelectHodnosti[i].Hid + "|" + SelectHodnosti[i].Nazev + "|" + SelectHodnosti[i].Minimalni_vek);
}
//8.1 - log
//ldm.MakeLog(1);
//8.3
List <Log> SelectLog = ldm.SelectAll();
//Log NovyLog = new Log(SelectLog.Count(), SelectDeti.Count(), SelectVedouci.Count(), datum, vdm.SelectById(1));
//ldm.Update(NovyLog);
//8.4
Console.WriteLine();
Console.WriteLine("8.4");
Console.WriteLine("lid|pocet_deti|pocet_vedoucich|datum_zalohy|vedouci_vid");
for (int i = 0; i < SelectLog.Count(); i++)
{
Console.WriteLine(SelectLog[i].Lid + "|" + SelectLog[i].Pocet_deti + "|" + SelectLog[i].Pocet_vedoucich + "|" + SelectLog[i].Datum_zalohy + "|" + SelectLog[i].Vedouci_vid.Vid);
}
//9.1 - akcedeti (Stored Procedure)
//addm.AddAkceToDite(1, 4);
List <AkceDeti> SelectAkceDeti = addm.SelectAll();
AkceDeti NoveAkceDeti = new AkceDeti(1, 4);
//9.2
//addm.Delete(NoveAkceDeti);
//9.4
Console.WriteLine();
Console.WriteLine("9.4");
Console.WriteLine("akce_aid|deti_did");
for (int i = 0; i < SelectAkceDeti.Count(); i++)
{
Console.WriteLine(SelectAkceDeti[i].Akce_aid + "|" + SelectAkceDeti[i].Deti_did);
}
Console.WriteLine('\n');
Console.ReadLine();
}