private void spasi_Click(object sender, EventArgs e)
{
try
{
int zakupacId;
string dioNekretnineSifra;
if (fizickoPravnoTabControl.SelectedIndex == 0) // Fizičko lice
{
DAO dao = new DAO("localhost", "ikzavrsni", "root", "root");
if (studentOstaliTabControl.SelectedIndex == 0) // Student
{
s = new Student(brojTelefonaMaskedTextBox.Text, emailTextBox.Text, adresaTextBox.Text, gradTextBox.Text, biljeskeRichTextBox.Text, imeTextBox.Text, prezimeTextBox.Text, licnaKartaTextBox.Text, jmbgTextBox.Text, fakultetComboBox.SelectedItem.ToString(), Convert.ToInt32(godinaStudijaNumericUpDown.Value), kucniTelefonMaskedTextBox.Text, roditeljTextBox.Text);
dao.UnesiStudenta(s);
zakupacId = dao.VratiIdZakupca(brojTelefonaMaskedTextBox.Text);
if (zakupacId != -1)
{
dioNekretnineSifra = dao.VratiSifruDijelaNekretnine(nazivDijelaZaIznajmljivanjeComboBox.SelectedItem.ToString());
i = new Iznajmljivanje(zakupacId, dioNekretnineSifra, Convert.ToDateTime(pocinjeOdDateTimePicker.Text), Convert.ToDateTime(zavrsavaDoDateTimePicker.Text));
dao.Iznajmi(i);
// postavi status na Zauzeto
// ukloni iz comboboxa
//statusStrip1.BackColor = Color.White;
//toolStripStatusLabel1.ForeColor = Color.Green;
toolStripStatusLabel1.Text = "Podaci su spašeni.";
}
else
throw new Exception("Podaci nisu spašeni!");
}
else // Ostali
{
o = new Ostali(brojTelefonaMaskedTextBox.Text, emailTextBox.Text, adresaTextBox.Text, gradTextBox.Text, biljeskeRichTextBox.Text, imeTextBox.Text, prezimeTextBox.Text, licnaKartaTextBox.Text, jmbgTextBox.Text, nazivFirmeTextBox.Text, adresaFirmeTextBox.Text, radnoMjestoTextBox.Text);
dao.UnesiOstalog(o);
}
}
else // Pravno lice
{
DAO dao = new DAO("localhost", "ikzavrsni", "root", "root");
pl = new PravnoLice(brojTelefonaMaskedTextBox.Text, emailTextBox.Text, adresaTextBox.Text, gradTextBox.Text, biljeskeRichTextBox.Text, pidTextBox.Text, nazivPravnogLicaTextBox.Text, ovlastenaOsobaTextBox.Text);
dao.UnesiPravnoLice(pl);
}
}
catch (Exception izuzetak)
{
//statusStrip1.BackColor = Color.White;
//toolStripStatusLabel1.ForeColor = Color.Red;
toolStripStatusLabel1.Text = izuzetak.Message;
}
}
public bool AzurirajStudenta(Student s, int studentID, int brojClanskeKarte)
{
try
{
MySqlCommand dataCommand = new MySqlCommand();
dataCommand.Connection = dataConnection;
dataCommand.CommandText = "UPDATE studenti SET brojIndexa = " + s.BrojIndexa + ", nazivFakulteta = '" + s.NazivFakulteta + "', tipClanarine = " + s.TipClanarine + " WHERE studentID = " + studentID + ";";
dataCommand.ExecuteNonQuery();
string datum = Convert.ToString(s.DatumUclanjivanja.Year) + "-" + Convert.ToString(s.DatumUclanjivanja.Month) + "-" + Convert.ToString(s.DatumUclanjivanja.Day);
MemoryStream m = new MemoryStream();
s.Slika.Save(m, System.Drawing.Imaging.ImageFormat.Jpeg);
byte[] bajtovi = m.ToArray();
MySqlCommand studenti = new MySqlCommand("Update clanovi_biblioteke SET brojLicneKarte=@brojLicneKarte, ime=@ime, prezime=@prezime, adresa=@adresa, email=@email, brojTelefona=@brojTelefona, datumUclanjivanja=@datumUclanjivanja, brojIznajmljenihKnjiga=@brojIznajmljenihKnjiga, zaduzen=@zaduzen, studenti_studentID=@studenti_studentID, slika=@slika WHERE brojClanskeKarte='" + brojClanskeKarte+ "'", dataConnection);
studenti.Parameters.Add("@brojLicneKarte", s.BrojLicneKarte);
studenti.Parameters.Add("@ime", s.Ime);
studenti.Parameters.Add("@prezime", s.Prezime);
studenti.Parameters.Add("@adresa", s.Adresa);
studenti.Parameters.Add("@email", s.Email);
studenti.Parameters.Add("@brojTelefona", s.BrojTelefona);
studenti.Parameters.Add("@datumUclanjivanja", datum);
studenti.Parameters.Add("@brojIznajmljenihKnjiga", s.BrojIznajmljenihKnjiga);
studenti.Parameters.Add("@zaduzen", s.Zaduzen);
studenti.Parameters.Add("@studenti_studentID", studentID);
MySqlParameter p = studenti.Parameters.Add("@slika", MySqlDbType.Blob);
p.Value = bajtovi;
studenti.ExecuteNonQuery();
return true;
}
catch (MySqlException izuzetak)
{
throw new Exception(izuzetak.Message);
}
}
// ZAKUPCI
public bool UnesiStudenta(Student s)
{
try
{
MySqlCommand dataCommand = new MySqlCommand();
dataCommand.Connection = dataConnection;
MySqlCommand student =
new MySqlCommand("INSERT INTO zakupci(tipZakupca, brojTelefona, email, adresa, grad, biljeske, ime, prezime, licnaKarta, jmbg, fakultet, godinaStudija, kucniTelefon, roditelj) "
+ "VALUES(@tipZakupca, @brojTelefona, @email, @adresa, @grad, @biljeske, @ime, @prezime, @licnaKarta, @jmbg, @fakultet, @godinaStudija, @kucniTelefon, @roditelj);", dataConnection);
student.Parameters.AddWithValue("@tipZakupca", (Object)"Student");
student.Parameters.AddWithValue("@brojTelefona", (Object)s.BrojTelefona);
student.Parameters.AddWithValue("@email", (Object)s.Email);
student.Parameters.AddWithValue("@adresa", (Object)s.Adresa);
student.Parameters.AddWithValue("@grad", (Object)s.Grad);
student.Parameters.AddWithValue("@biljeske", (Object)s.Biljeske);
student.Parameters.AddWithValue("@ime", (Object)s.Ime);
student.Parameters.AddWithValue("@prezime", (Object)s.Prezime);
student.Parameters.AddWithValue("@licnaKarta", (Object)s.LicnaKarta);
student.Parameters.AddWithValue("@jmbg", (Object)s.Jmbg);
student.Parameters.AddWithValue("@fakultet", (Object)s.Fakultet);
student.Parameters.AddWithValue("@godinaStudija", (Object)s.GodinaStudija);
student.Parameters.AddWithValue("@kucniTelefon", (Object)s.KucniTelefon);
student.Parameters.AddWithValue("@roditelj", (Object)s.Roditelj);
student.ExecuteNonQuery();
return true;
}
catch (MySqlException izuzetak)
{
throw new Exception(izuzetak.Message);
}
}
public List<Student> DajSveStudente()
{
try
{
List<Student> studenti = new List<Student>();
MySqlCommand dataCommand = new MySqlCommand();
dataCommand.Connection = dataConnection;
dataCommand.CommandText = "SELECT * FROM zakupci WHERE tipZakupca = 'Student';";
MySqlDataReader dataReader = dataCommand.ExecuteReader();
while (dataReader.Read())
{
Student s = new Student(dataReader.GetString(2), dataReader.GetString(3), dataReader.GetString(4), dataReader.GetString(5), dataReader.GetString(6), dataReader.GetString(7), dataReader.GetString(8), dataReader.GetString(9), dataReader.GetString(10), dataReader.GetString(11), dataReader.GetInt32(12), dataReader.GetString(13), dataReader.GetString(14));
s.Id = dataReader.GetInt32(0);
studenti.Add(s);
}
dataReader.Close();
return studenti;
}
catch (MySqlException izuzetak)
{
throw new Exception(izuzetak.Message);
}
}
public Student VratiStudenta(int brojClanskeKarte)
{
try
{
MySqlCommand dataCommand = new MySqlCommand();
dataCommand.Connection = dataConnection;
dataCommand.CommandText = "SELECT * FROM clanovi_biblioteke, studenti WHERE studenti_studentID = studentID AND brojClanskeKarte = " + brojClanskeKarte + ";";
MySqlDataReader dataReader = dataCommand.ExecuteReader();
dataReader.Read();
Student s = new Student(dataReader.GetString(1), dataReader.GetString(2), dataReader.GetString(3), dataReader.GetString(4), dataReader.GetString(5), dataReader.GetString(6), dataReader.GetDateTime(7), dataReader.GetInt32(13), dataReader.GetString(14), dataReader.GetInt32 (15));
s.BrojIznajmljenihKnjiga = dataReader.GetInt32(9);
s.Zaduzen = dataReader.GetBoolean(10);
dataReader.Close();
return s;
}
catch (MySqlException izuzetak)
{
throw new Exception(izuzetak.Message);
}
}
public ArrayList PretraziClanoveBiblioteke(string atribut, string uneseno)
{
try
{
ArrayList clanovi = new ArrayList();
string pretraga;
if (atribut == "Ime")
pretraga = "ime";
else if (atribut == "Prezime")
pretraga = "prezime";
else pretraga = "brojClanskeKarte";
MySqlCommand dataCommand = new MySqlCommand();
dataCommand.Connection = dataConnection;
dataCommand.CommandText = "SELECT * FROM clanovi_biblioteke, studenti WHERE studenti_studentID = studentID AND " + pretraga + " LIKE '%" + uneseno + "%';";
MySqlDataReader dataReader = dataCommand.ExecuteReader();
while (dataReader.Read())
{
Student s = new Student(dataReader.GetString(1), dataReader.GetString(2), dataReader.GetString(3), dataReader.GetString(4), dataReader.GetString(5), dataReader.GetString(6), dataReader.GetDateTime(7), dataReader.GetInt32(14), dataReader.GetString(15), dataReader.GetInt32(16));
s.BrojIznajmljenihKnjiga = dataReader.GetInt32(8);
s.Zaduzen = dataReader.GetBoolean(9);
s.BrojClanskeKarte = dataReader.GetInt32(0);
clanovi.Add(s);
}
dataReader.Close();
dataCommand.CommandText = "SELECT * FROM clanovi_biblioteke, uposlenici WHERE uposlenici_uposlenikID = uposlenikID AND " + pretraga + " LIKE '%" + uneseno + "%';";
dataReader = dataCommand.ExecuteReader();
while (dataReader.Read())
{
Uposlenik u = new Uposlenik(dataReader.GetString(1), dataReader.GetString(2), dataReader.GetString(3), dataReader.GetString(4), dataReader.GetString(5), dataReader.GetString(6), dataReader.GetDateTime(7), dataReader.GetString(14), dataReader.GetString(15));
u.BrojIznajmljenihKnjiga = dataReader.GetInt32(8);
u.Zaduzen = dataReader.GetBoolean(9);
u.BrojClanskeKarte = dataReader.GetInt32(0);
clanovi.Add(u);
}
return clanovi;
}
catch (MySqlException izuzetak)
{
throw new Exception(izuzetak.Message);
}
}
public bool DodajStudenta(Student s)
{
try
{
int studentID;
MySqlCommand dataCommand = new MySqlCommand();
dataCommand.Connection = dataConnection;
dataCommand.CommandText = "INSERT INTO studenti (brojIndexa, nazivFakulteta, tipClanarine) VALUES (" + s.BrojIndexa + ", '" + s.NazivFakulteta + "', " + s.TipClanarine + ");";
int r = dataCommand.ExecuteNonQuery();
dataCommand.CommandText = "SELECT studentID FROM studenti WHERE brojIndexa = " + s.BrojIndexa + " AND nazivFakulteta = '" + s.NazivFakulteta + "';";
MySqlDataReader dataReader = dataCommand.ExecuteReader();
dataReader.Read();
studentID = dataReader.GetInt16(0);
dataReader.Close();
string datum = Convert.ToString(s.DatumUclanjivanja.Year) + "-" + Convert.ToString(s.DatumUclanjivanja.Month) + "-" + Convert.ToString(s.DatumUclanjivanja.Day);
MemoryStream m = new MemoryStream();
s.Slika.Save(m, System.Drawing.Imaging.ImageFormat.Jpeg);
byte[] bajtovi = m.ToArray();
MySqlCommand studenti = new MySqlCommand("Insert into clanovi_biblioteke(brojLicneKarte, ime, prezime, adresa, email, brojTelefona,datumUclanjivanja, brojIznajmljenihKnjiga, zaduzen, studenti_studentID, slika) "
+ "values(@brojLicneKarte, @ime, @prezime, @adresa, @email, @brojTelefona, @datumUclanjivanja, @brojIznajmljenihKnjiga, @zaduzen, @studenti_studentID, @slika);", dataConnection);
studenti.Parameters.Add("@brojLicneKarte", (Object)s.BrojLicneKarte);
studenti.Parameters.Add("@ime", (Object)s.Ime);
studenti.Parameters.Add("@prezime", (Object)s.Prezime);
studenti.Parameters.Add("@adresa", (Object)s.Adresa);
studenti.Parameters.Add("@email", (Object)s.Email);
studenti.Parameters.Add("@brojTelefona", (Object)s.BrojTelefona);
studenti.Parameters.Add("@datumUclanjivanja", (Object)datum);
studenti.Parameters.Add("@brojIznajmljenihKnjiga", (Object)s.BrojIznajmljenihKnjiga);
studenti.Parameters.Add("@zaduzen", (Object)s.Zaduzen);
studenti.Parameters.Add("@studenti_studentID", (Object)studentID);
MySqlParameter p = studenti.Parameters.Add("@slika", MySqlDbType.Blob);
p.Value = bajtovi;
studenti.ExecuteNonQuery();
return true;
}
catch (MySqlException e)
{
throw new Exception(e.Message);
}
}
public ArrayList DajIstekle()
{
try
{
ArrayList clanovi = new ArrayList();
MySqlCommand dataCommand = new MySqlCommand();
dataCommand.Connection = dataConnection;
dataCommand.CommandText = "SELECT * FROM clanovi_biblioteke c, studenti s WHERE c.studenti_studentID = s.studentID AND PERIOD_DIFF(extract(year_month from sysdate()), extract(year_month from c.datumUclanjivanja)) > s.tipClanarine;";
MySqlDataReader dataReader = dataCommand.ExecuteReader();
while (dataReader.Read())
{
Student s = new Student(dataReader.GetString(1), dataReader.GetString(2), dataReader.GetString(3), dataReader.GetString(4), dataReader.GetString(5), dataReader.GetString(6), dataReader.GetDateTime(7), dataReader.GetInt32(14), dataReader.GetString(15), dataReader.GetInt32(16));
s.BrojIznajmljenihKnjiga = dataReader.GetInt32(8);
s.Zaduzen = dataReader.GetBoolean(9);
s.BrojClanskeKarte = dataReader.GetInt32(0);
clanovi.Add(s);
}
dataReader.Close();
return clanovi;
}
catch (MySqlException izuzetak)
{
throw new Exception(izuzetak.Message);
}
}
