public static List <knjige> IzpisIzposojenih(int id_u)
{
List <knjige> knjige = new List <knjige>();
SQLiteConnection con = connect();
con.Open();
using (SQLiteCommand com = new SQLiteCommand(con))
{
com.CommandText = "SELECT b.title, b.author, b.year, s.name, b.id FROM books b INNER JOIN sections s ON s.id=b.section_id INNER JOIN rents r ON r.book_id=b.id WHERE (b.current_state=0) AND (r.user_id='" + id_u + "');";
SQLiteDataReader read = com.ExecuteReader();
while (read.Read())
{
string naslov = read.GetString(0);
string avtor = read.GetString(1);
int leto = read.GetInt32(2);
string oddelek = read.GetString(3);
int id = read.GetInt32(4);
knjige knjiga = new knjige(naslov, avtor, leto, oddelek, id);
knjige.Add(knjiga);
}
}
con.Close();
return(knjige);
}
public static List <knjige> IzpisKnjigNaVoljo(int status)
{
List <knjige> knjige = new List <knjige>();
SQLiteConnection con = connect();
con.Open();
using (SQLiteCommand com = new SQLiteCommand(con))
{
com.CommandText = "SELECT b.id,b.title,b.year,b.author,b.publisher,s.name,b.current_state FROM books b INNER JOIN sections s ON b.section_id = s.id WHERE current_state = " + status + " ORDER BY b.id;";
SQLiteDataReader read = com.ExecuteReader();
while (read.Read())
{
int id = read.GetInt32(0);
string naslov = read.GetString(1);
int leto = read.GetInt32(2);
string avtor = read.GetString(3);
string publisher = read.GetString(4);
string section = read.GetString(5);
int state = read.GetInt32(6);
knjige knjiga = new knjige(id, naslov, leto, avtor, publisher, section, state);
knjige.Add(knjiga);
}
com.Dispose();
con.Close();
return(knjige);
}
}
public static List <knjige> SearchKnjig(string src)
{
SQLiteConnection con = connect();
List <knjige> searchedbooks = new List <knjige>();
con.Open();
src = src.ToLower();
using (SQLiteCommand com = new SQLiteCommand(con))
{
com.CommandText = "SELECT b.id,b.title,b.year,b.author,b.publisher,s.name,b.current_state FROM books b INNER JOIN sections s ON b.section_id = s.id WHERE LOWER(b.title) LIKE '%" + src + "%' OR b.year LIKE '%" + src + "%' OR LOWER(b.author) LIKE '%" + src + "%' OR LOWER(b.publisher) LIKE '%" + src + "%' OR LOWER(s.name) LIKE '%" + src + "%' ORDER BY b.id;";
SQLiteDataReader read = com.ExecuteReader();
if (read.HasRows == true)
{
while (read.Read())
{
int id = read.GetInt32(0);
string naslov = read.GetString(1);
int leto = read.GetInt32(2);
string avtor = read.GetString(3);
string publisher = read.GetString(4);
string section = read.GetString(5);
int state = read.GetInt32(6);
knjige knjiga = new knjige(id, naslov, leto, avtor, publisher, section, state);
searchedbooks.Add(knjiga);
}
}
else
{
}
}
con.Dispose();
return(searchedbooks);
}
public static knjige PodatkiOknjigi(int idk)
{
knjige kn;
SQLiteConnection con = connect();
con.Open();
using (SQLiteCommand com = new SQLiteCommand(con))
{
com.CommandText = "SELECT k.id,k.title,k.shop,k.notes,s.name,k.year,k.author,k.publisher,k.lost,k.current_state FROM books k INNER JOIN sections s ON s.id = k.section_id WHERE k.id = " + idk + ";";
SQLiteDataReader read = com.ExecuteReader();
read.Read();
int it = read.GetInt32(0);
string naslov = read.GetString(1);
int shop = read.GetInt32(2);
string opombe = read.GetString(3);
string section = read.GetString(4);
int leto = read.GetInt32(5);
string avtor = read.GetString(6);
string publisher = read.GetString(7);
int lost = read.GetInt32(8);
int current_state = read.GetInt32(9);
string nacinpridobitve = "";
switch (shop)
{
case 0:
nacinpridobitve = "Nakup";
break;
case 1:
nacinpridobitve = "Darilo";
break;
case 2:
nacinpridobitve = "Ostalo";
break;
}
int st = 0;
kn = new knjige(current_state, nacinpridobitve, opombe, lost, it, naslov, leto, avtor, publisher, section, st);
com.Dispose();
con.Close();
}
return(kn);
}
private void dodajKnjigoButton_Click(object sender, EventArgs e)
{
string naslovKnjige = naslovKnjigeTextBox.Text;
string nacinPridobitve = pridobitevComboBox.SelectedIndex.ToString();
string oddelek = oddelekComboBox.SelectedItem.ToString();
int leto = Convert.ToInt32(letoTextbox.Text);
string avtor = avtorTextBox.Text;
string zalozba = zalozbaBox.Text;
string opombe = opombeTextBox.Text;
knjige knjiga = new knjige(naslovKnjige, nacinPridobitve, oddelek, leto, avtor, zalozba, opombe);
baza.DodajKnjigo(knjiga.Naslov, knjiga.Shop, knjiga.Section, knjiga.Leto, knjiga.Avtor, knjiga.Zalozba, knjiga.Opomba);
MessageBox.Show("Knjiga uspešno dodana!");
polnjenje();
}
private void polnjenje()
{
knjige kn = baza.PodatkiOknjigi(id);
invtext.Text = kn.inventarna_st.ToString();
nastext.Text = kn.Naslov;
avtext.Text = kn.Avtor;
zatext.Text = kn.Zalozba;
letext.Text = kn.Leto.ToString();
odtext.Text = kn.Section;
if (kn.Lost == 1)
{
ja_izgubljena.Checked = true;
}
else
{
ne_izgubljena.Checked = true;
}
nacintext.Text = kn.Shop;
optext.Text = kn.notes;
string stat = "";
if (kn.Status == 0)
{
stat = "Izposojeno";
vrnibutton.Visible = true;
spremeniStatusButton.Visible = false;
}
else if (kn.Status == 1)
{
stat = "Prosto";
vrnibutton.Visible = false;
spremeniStatusButton.Visible = true;
}
statlabel.Text = statlabel.Text + " " + stat;
}
