public Tuple <int, string, string> /*<id,nick,role>*/ LoginAs(string username, string pw)
{
Deti d = null;
int role;
d = detiDataMapper.TryLogin(username, pw);
if (d != null)
{
if (d.Nickname == username)
{
role = 1;
}
else
{
role = 0;
}
if (role == 0)
{
return(new Tuple <int, string, string>(d.Did, d.Jmeno, "Child"));
}
else if (role == 1)
{
return(new Tuple <int, string, string>(d.Did, d.Jmeno, "Parent"));
}
}
return(null);
}
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 FROM - UPDATE "stav" to -1 3.2
public void Delete(Deti deti)
{
using (db.GetConnection())
{
db.Connect();
OracleCommand command = db.CreateCommand("UPDATE Deti SET Stav = -1 WHERE ID = :ID");
command.Parameters.AddWithValue(":ID", deti.Did);
command.ExecuteNonQuery();
}
}
public void ExportToCSV(string path)
{
using (db.GetConnection())
{
db.Connect();
using (var w = new StreamWriter(path))
{
List <Deti> toCSV = SelectAll();
for (int i = 0; i < toCSV.Count; i++)
{
Deti v = toCSV[i];
string line = v.Did + ", " + v.Jmeno + ", " + v.Nickname + ", " + v.Heslo + ", " + v.Datum_narozeni + ", " + v.Stav + ", " + v.Hodnosti_hid.Hid + ", " + v.Schuzky_sid.Sid;
w.WriteLine(line);
w.Flush();
}
}
}
}
//UPDATE 3.3
public void Update(Deti deti)
{
using (db.GetConnection())
{
db.Connect();
OracleCommand commandUpdate = db.CreateCommand("UPDATE Deti SET Jmeno = :Jmeno, Nickname = :Nickname, Heslo = :Heslo, datum_narozeni = :datum_narozeni, Stav = :Stav, hodnosti_hid = :hodnosti_hid, schuzky_sid = :schuzky_sid, rodic_rid = :rodic_rid WHERE did = :did"); commandUpdate.Parameters.AddWithValue(":Jmeno", deti.Jmeno);
commandUpdate.Parameters.AddWithValue(":Nickname", deti.Nickname);
commandUpdate.Parameters.AddWithValue(":Heslo", deti.Heslo);
commandUpdate.Parameters.AddWithValue(":datum_narozeni", deti.Datum_narozeni);
commandUpdate.Parameters.AddWithValue(":Stav", deti.Stav);
commandUpdate.Parameters.AddWithValue(":hodnosti_hid", deti.Hodnosti_hid.Hid);
commandUpdate.Parameters.AddWithValue(":schuzky_sid", deti.Schuzky_sid.Sid);
commandUpdate.Parameters.AddWithValue(":reg_akci", deti.Reg_akci);
commandUpdate.Parameters.AddWithValue(":rodic_rid", deti.Rodic_rid.Rid);
commandUpdate.ExecuteNonQuery();
}
}
//INSERT 3.1
public void Insert(Deti deti)
{
using (db.GetConnection())
{
db.Connect();
OracleCommand commandInsert = db.CreateCommand("INSERT INTO Deti (did, Jmeno, Nickname,Heslo,datum_narozeni,Stav, hodnosti_hid,schuzky_sid, reg_akci, rodic_rid) VALUES (:did, :Jmeno, :Nickname,:Heslo,:datum_narozeni,:Stav, :hodnosti_hid,:schuzky_sid, :reg_akci, :rodic_rid)");
commandInsert.Parameters.AddWithValue(":did", deti.Did);
commandInsert.Parameters.AddWithValue(":Jmeno", deti.Jmeno);
commandInsert.Parameters.AddWithValue(":Nickname", deti.Nickname);
commandInsert.Parameters.AddWithValue(":Heslo", deti.Heslo);
commandInsert.Parameters.AddWithValue(":datum_narozeni", deti.Datum_narozeni);
commandInsert.Parameters.AddWithValue(":Stav", deti.Stav);
commandInsert.Parameters.AddWithValue(":hodnosti_hid", deti.Hodnosti_hid.Hid);
commandInsert.Parameters.AddWithValue(":schuzky_sid", deti.Schuzky_sid.Sid);
commandInsert.Parameters.AddWithValue(":reg_akci", deti.Reg_akci);
commandInsert.Parameters.AddWithValue(":rodic_rid", deti.Rodic_rid.Rid);
commandInsert.ExecuteNonQuery();
}
}
