private void faculty_cmbx_SelectedIndexChanged(object sender, EventArgs e)
{
department_cmbx.Items.Clear();
string query, name;
int id, faculty_id, index;
index = faculty_cmbx.SelectedIndex;
faculty_id = list_faculty[index].Id;
query = "SELECT id,name FROM department WHERE faculty_id={0}";
query = string.Format(query, faculty_id);
try
{
DatabaseProcess.conn_open();
MySqlCommand cmd = new MySqlCommand(query, DatabaseProcess.myconn);
MySqlDataReader dt_reader = cmd.ExecuteReader();
while (dt_reader.Read())
{
name = dt_reader.GetString("name");
id = dt_reader.GetInt32("id");
department_cmbx.Items.Add(name);
list_department.Add(new department(id, name));
}
DatabaseProcess.conn_close();
}
catch
{ }
}
private void students_Load(object sender, EventArgs e)
{
update_btn.Enabled = false;
string query, name;
int id;
query = "SELECT id,name FROM faculty";
try
{
DatabaseProcess.conn_open();
MySqlCommand cmd = new MySqlCommand(query, DatabaseProcess.myconn);
MySqlDataReader dt_reader = cmd.ExecuteReader();
while (dt_reader.Read())
{
name = dt_reader.GetString("name");
id = dt_reader.GetInt32("id");
faculty_cmbx.Items.Add(name);
list_faculty.Add(new faculty(id, name));
}
DatabaseProcess.conn_close();
}
catch
{}
}
private void faculty_cmbx_SelectedIndexChanged(object sender, EventArgs e)
{
department_cmbx.Items.Clear();
int index, id;
string faculty_id, query, value, name;
index = faculty_cmbx.SelectedIndex;
faculty_id = Utils.faculty_list[index].Id.ToString();
query = "SELECT id, name FROM department WHERE faculty_id={0};";
query = string.Format(query, faculty_id);
DatabaseProcess.conn_open();
MySqlCommand cmd = new MySqlCommand(query, DatabaseProcess.myconn);
MySqlDataReader dt_reader = cmd.ExecuteReader();
while (dt_reader.Read())
{
id = dt_reader.GetInt32("id");
name = dt_reader.GetString("name");
value = id.ToString() + " - " + name;
department_cmbx.Items.Add(value);
list_department.Add(new department(id, name));
}
DatabaseProcess.conn_close();
}
private void student_result_lstbx_SelectedIndexChanged(object sender, EventArgs e)
{
string query = "", query2 = "", query3, book_name, code, waiting_recieve = "";
int student_id;
List <int> book_id = new List <int>();
if (student_result_lstbx.SelectedIndex > -1)
{
student_index = student_result_lstbx.SelectedIndex;
student_info_lbl.Text = student_result_lstbx.SelectedItem.ToString();
student_id = list_student[student_index].Id;
query = "SELECT book_id FROM lend_book WHERE student_id={0} && recieve_case=0";
query2 = "SELECT name,code FROM book WHERE id={0};";
query = string.Format(query, student_id.ToString());
}
try
{
DatabaseProcess.conn_open();
MySqlCommand cmd = new MySqlCommand(query, DatabaseProcess.myconn);
MySqlDataReader dt_reader;
dt_reader = cmd.ExecuteReader();
while (dt_reader.Read())
{
book_id.Add(dt_reader.GetInt32("book_id"));
}
DatabaseProcess.conn_close();
if (book_id.Count == 0)
{
waiting_recive_book_lbl.Text = "Ödünç aldığı kitap yok";
}
else
{
for (int i = 0; i < book_id.Count; i++)
{
DatabaseProcess.conn_open();
query3 = string.Format(query2, book_id[i].ToString());
cmd = new MySqlCommand(query3, DatabaseProcess.myconn);
dt_reader = cmd.ExecuteReader();
dt_reader.Read();
code = dt_reader.GetString("code");
book_name = dt_reader.GetString("name");
if (i > 0)
{
waiting_recieve += "\n" + book_name + " | " + code;
}
else
{
waiting_recieve = book_name + " | " + code;
}
waiting_recive_book_lbl.Text = waiting_recieve;
DatabaseProcess.conn_close();
}
}
}
catch (Exception)
{
}
}
private void student_search_txtbx_TextChanged(object sender, EventArgs e)
{
student_result_lstbx.Items.Clear();
string query = "SELECT * FROM student WHERE {0}";
string search_with_name = " concat_ws(' ',name, surname) like '%{0}%'";
string search_with_number = "number={0}";
if (student_search_txtbx.Text.Length > 2)
{
list_student.Clear();
string name, surname = "", string_number;
int number, id;
if (student_search_txtbx.Text.Any(char.IsLetter))
{
name = student_search_txtbx.Text;
query = string.Format(query, search_with_name);
query = string.Format(query, name);
}
else
{
string_number = student_search_txtbx.Text;
query = string.Format(query, search_with_number);
query = string.Format(query, string_number);
}
try
{
DatabaseProcess.conn_open();
MySqlCommand cmd = new MySqlCommand(query, DatabaseProcess.myconn);
MySqlDataReader dt_reader = cmd.ExecuteReader();
while (dt_reader.Read())
{
id = dt_reader.GetInt32("id");
name = dt_reader.GetString("name");
surname = dt_reader.GetString("surname");
number = dt_reader.GetInt32("number");
string_number = number.ToString();
list_student.Add(new student(id));
student_result_lstbx.Items.Add(name + " " + surname + " | " + string_number);
}
DatabaseProcess.conn_close();
}
catch { }
if (student_result_lstbx.Items.Count == 0)
{
student_result_lstbx.Items.Add("Eşleşme yok");
}
} //if
else
{
student_result_lstbx.Items.Add("Arama için en az 3 karakter girmelisiniz.");
}
}//student_search_txtbx_TextChanged
public static void list_faculty()
{
faculty_list.Clear();
int new_id;
string new_name;
string query = "SELECT id,name FROM faculty;";
DatabaseProcess.conn_open();
MySqlCommand cmd = new MySqlCommand(query, DatabaseProcess.myconn);
MySqlDataReader dt_reader = cmd.ExecuteReader();
while (dt_reader.Read())
{
new_id = dt_reader.GetInt32("id");
new_name = dt_reader.GetString("name");
faculty_list.Add(new faculty(new_id, new_name));
}
DatabaseProcess.conn_close();
}
private void giris_btn_Click(object sender, EventArgs e)
{
string email, password;
email = email_txtbx.Text;
password = psswrd_txtbx.Text;
string hash = Utils.get_hash(password);
string query = "SELECT * FROM User WHERE email='" + email + "' and password='******';";
try
{
DatabaseProcess.conn_open();
MySqlCommand cmd = new MySqlCommand(query, DatabaseProcess.myconn);
MySqlDataReader dt_reader = cmd.ExecuteReader();
if (dt_reader.Read())
{
Utils.User.id = dt_reader.GetInt32("id");
Utils.User.name = dt_reader.GetString("name");
Utils.User.surname = dt_reader.GetString("surname");
int id = dt_reader.GetInt32("id");
main_panel main_panel_form = new main_panel();
main_panel_form.show_singin = this;
this.Visible = false;
main_panel_form.Show();
}
else
{
msg_lbl.Text = "Email, parola combinasyonu bulunamadı. Bilgileri kontrol ediniz.";
}
DatabaseProcess.conn_close();
}
catch (Exception)
{
}
}
private void search_btn_Click(object sender, EventArgs e)
{
string query, query2, query3, code, student_id = "", book_id = "", name, surname, number, author, temp = "";
DateTime lend_date, expiry_date, today = DateTime.Today;
int remaining_time;
query = "SELECT id,student_id,lend_date,expiry_date FROM lend_book WHERE book_id={0} AND recieve_case=0;";
query2 = "SELECT name, surname, number FROM student WHERE id={0};";
query3 = "SELECT id,name, author FROM book WHERE code='{0}';";
code = search_txtbx.Text;
query3 = string.Format(query3, code);
try
{
DatabaseProcess.conn_open();
MySqlCommand cmd = new MySqlCommand(query3, DatabaseProcess.myconn);
MySqlDataReader dt_reader = cmd.ExecuteReader();
dt_reader.Read();
book_id = dt_reader.GetInt32("id").ToString();
name = dt_reader.GetString("name");
author = dt_reader.GetString("author");
book_info_lbl.Text = name + " " + author + " | " + code;
DatabaseProcess.conn_close();
}
catch
{}
if (book_id != "")
{
query = string.Format(query, book_id);
try
{
DatabaseProcess.conn_open();
MySqlCommand cmd = new MySqlCommand(query, DatabaseProcess.myconn);
MySqlDataReader dt_reader = cmd.ExecuteReader();
dt_reader.Read();
lend_book_id = dt_reader.GetInt32("id").ToString();
student_id = dt_reader.GetInt32("student_id").ToString();
lend_date = dt_reader.GetDateTime("lend_date");
expiry_date = dt_reader.GetDateTime("expiry_date");
lend_date_lbl.Text = lend_date.ToShortDateString();
remaining_time = (int)today.Subtract(expiry_date).TotalDays;
if (remaining_time <= 0)
{
remaining_time_lbl.Text = Math.Abs((int)remaining_time) + " gün kaldı";
}
else
{
remaining_time_lbl.Text = Math.Abs((int)remaining_time) + " gün geçti";
}
DatabaseProcess.conn_close();
}
catch
{
DatabaseProcess.conn_close();
}
if (student_id != "")
{
query2 = string.Format(query2, student_id);
try
{
DatabaseProcess.conn_open();
MySqlCommand cmd = new MySqlCommand(query2, DatabaseProcess.myconn);
MySqlDataReader dt_reader = cmd.ExecuteReader();
dt_reader.Read();
name = dt_reader.GetString("name");
surname = dt_reader.GetString("surname");
number = dt_reader.GetInt32("number").ToString();
student_info_lbl.Text = name + " " + surname + " | " + number;
DatabaseProcess.conn_close();
}
catch
{ }
groupBox1.Visible = true;
}//if(student_id != "")
else
{
query = "SELECT name FROM book WHERE code='{0}';";
query = string.Format(query, code);
MessageBox.Show(query);
try
{
DatabaseProcess.conn_open();
MySqlCommand cmd = new MySqlCommand(query, DatabaseProcess.myconn);
MySqlDataReader dt_reader = cmd.ExecuteReader();
while (dt_reader.Read())
{
temp = dt_reader.GetString("name");
}
DatabaseProcess.conn_close();
}
catch
{
}
if (temp != "")
{
MessageBox.Show("Bu kitap zaten rafta");
}
else
{
MessageBox.Show("Kitap kodu hatalı!");
}
}
}//if(book_id != "")
else
{
MessageBox.Show("Kitap kodu hatalı!");
}
}//student_search_txtbx_TextChanged
private void singup_Load(object sender, EventArgs e)
{
gsm_txtbx.Text = "0";
error_lbl.Text = "";
//// create database /////
//try
//{
Boolean result = DatabaseProcess.conn_open();
if (!result)
{
string connline = "server=127.0.0.1;port=3306;user id=root;password=1234";
string query = "CREATE DATABASE library";
MySqlConnection conn = new MySqlConnection(connline);
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = query;
cmd.Connection.Open();
try
{
cmd.ExecuteNonQuery();
query = "use library; CREATE TABLE book (id int(11) NOT NULL,code varchar(45) NOT NULL,name varchar(45) NOT NULL,category varchar(45) NOT NULL,published_year int(11) NOT NULL,ISBN_number varchar(45) NOT NULL,page_count int(11) NOT NULL,search_keys varchar(250) NOT NULL,author varchar(45) NOT NULL,PRIMARY KEY (id),UNIQUE KEY id_UNIQUE (id),UNIQUE KEY code_UNIQUE (code));";
DatabaseProcess.conn_open();
cmd = new MySqlCommand(query, DatabaseProcess.myconn);
cmd.ExecuteNonQuery();
DatabaseProcess.conn_close();
query = "CREATE TABLE faculty (" +
"id int(11) NOT NULL," +
"name varchar(100) NOT NULL," +
"create_time datetime NOT NULL," +
"PRIMARY KEY (id)," +
"UNIQUE KEY id_UNIQUE (id)," +
"UNIQUE KEY name_UNIQUE (name))";
cmd = new MySqlCommand(query, DatabaseProcess.myconn);
DatabaseProcess.conn_open();
cmd.ExecuteNonQuery();
DatabaseProcess.conn_close();
query = "CREATE TABLE department (" +
"id int(11) NOT NULL," +
"faculty_id int(11) NOT NULL," +
"name varchar(100) NOT NULL," +
"create_date datetime NOT NULL," +
"PRIMARY KEY (id)," +
"UNIQUE KEY id_UNIQUE (id)," +
"UNIQUE KEY name_UNIQUE (name)," +
"KEY fakulty_id_idx (faculty_id)," +
"CONSTRAINT fakulty_id FOREIGN KEY (faculty_id) " +
"REFERENCES faculty (id) ON UPDATE NO ACTION)";
cmd = new MySqlCommand(query, DatabaseProcess.myconn);
DatabaseProcess.conn_open();
cmd.ExecuteNonQuery();
DatabaseProcess.conn_close();
query = "CREATE TABLE student (" +
"id int(11) NOT NULL," +
"number int(11) NOT NULL," +
"name varchar(45) NOT NULL," +
"surname varchar(45) NOT NULL," +
"adress varchar(255) NOT NULL," +
"hometown_address varchar(255) NOT NULL," +
"gsm varchar(11) NOT NULL," +
"department_id int(11) NOT NULL," +
"PRIMARY KEY (id)," +
" UNIQUE KEY id_UNIQUE (id)," +
"UNIQUE KEY number_UNIQUE (number)," +
"UNIQUE KEY gsm_number_UNIQUE (gsm)," +
"KEY department_id_idx (department_id)," +
"CONSTRAINT department_id FOREIGN KEY (department_id) REFERENCES department (id) ON DELETE NO ACTION ON UPDATE NO ACTION)";
cmd = new MySqlCommand(query, DatabaseProcess.myconn);
DatabaseProcess.conn_open();
cmd.ExecuteNonQuery();
DatabaseProcess.conn_close();
query = "CREATE TABLE user (" +
"id int(11) NOT NULL," +
"name varchar(45) NOT NULL," +
"surname varchar(45) NOT NULL," +
"singup_date datetime NOT NULL," +
"gsm varchar(11) NOT NULL," +
"email varchar(45) NOT NULL," +
"password varchar(255) NOT NULL," +
"PRIMARY KEY (id)," +
" UNIQUE KEY id_UNIQUE (id)," +
" UNIQUE KEY tel_UNIQUE (gsm)," +
" UNIQUE KEY email_UNIQUE (email))";
cmd = new MySqlCommand(query, DatabaseProcess.myconn);
DatabaseProcess.conn_open();
cmd.ExecuteNonQuery();
DatabaseProcess.conn_close();
query = "CREATE TABLE lend_book (" +
"id int(11) NOT NULL," +
"student_id int(11) NOT NULL," +
"book_id int(11) NOT NULL," +
"recieve_by_id int(11) DEFAULT NULL," +
"lend_date datetime NOT NULL," +
"expiry_date datetime NOT NULL," +
"delivery_date datetime DEFAULT NULL," +
"recieve_case tinyint(1) DEFAULT 0," +
"PRIMARY KEY (id)," +
"UNIQUE KEY id_UNIQUE (id)," +
"KEY student_id_idx (student_id)," +
"KEY book_id_idx (book_id)," +
"KEY recieve_by_id_idx (recieve_by_id)," +
"CONSTRAINT book_id FOREIGN KEY (book_id) REFERENCES book (id) ON DELETE NO ACTION ON UPDATE NO ACTION," +
"CONSTRAINT recieve_by_id FOREIGN KEY (recieve_by_id) REFERENCES user (id) ON DELETE NO ACTION ON UPDATE NO ACTION," +
"CONSTRAINT student_id FOREIGN KEY (student_id) REFERENCES student (id) ON DELETE NO ACTION ON UPDATE NO ACTION) ";
cmd = new MySqlCommand(query, DatabaseProcess.myconn);
DatabaseProcess.conn_open();
cmd.ExecuteNonQuery();
DatabaseProcess.conn_close();
}
catch (Exception)
{
}
}
else
{
DatabaseProcess.conn_close();
}
//}
//catch
//{
// MessageBox.Show("Hata verdi");
//}
}
private void lend_btn_Click(object sender, EventArgs e)
{
Boolean result;
int user_id, student_id, book_id, expiry_date, recieve_case;
string query = "INSERT INTO lend_book(id,student_id,book_id,recieve_by_id,lend_date,expiry_date)" +
"values(new_id,{0},{1},{2},NOW()," +
"DATE_ADD(NOW(),INTERVAL {3} DAY));";
string query2 = "SELECT recieve_case FROM lend_book WHERE book_id={0}";
user_id = Utils.User.id;
student_id = list_student[student_index].Id;
book_id = list_book[book_index].Id;
query2 = string.Format(query2, book_id.ToString());
DatabaseProcess.conn_open();
MySqlCommand cmd = new MySqlCommand(query2, DatabaseProcess.myconn);
MySqlDataReader dt_reader = cmd.ExecuteReader();
if (dt_reader.Read())
{
recieve_case = dt_reader.GetByte("recieve_case");
}
else
{
recieve_case = 1;
}
DatabaseProcess.conn_close();
if (change_lend_time_rdbtn.Enabled == false)
{
expiry_date = Convert.ToInt32(lend_time_cmbx.Text);
}
else
{
expiry_date = 15;
}
if (student_index > -1 && book_index > -1 && recieve_case == 1)
{
query = string.Format(query, student_id, book_id, user_id, "15");
result = DatabaseProcess.insert(query, "lend_book");
if (result)
{
MessageBox.Show("Tamamlandı!");
}
else
{
error_msg_lbl.Text = DatabaseProcess.error_msg;
}
}
else
{
if (recieve_case == 0)
{
MessageBox.Show("Bu kitap şu an rafta değil!");
}
else
{
error_msg_lbl.Text = "Lütfen gerekli alanları doldurunuz.";
}
}
}
private void book_search_txtbx_TextChanged(object sender, EventArgs e)
{
// Keys ile arama çalışmıyo!!
book_result_lstbx.Items.Clear();
int recieve_case;
int id;
string search, name, author, code;
string query = "SELECT * FROM book WHERE {0}";
string search_with_name = "name LIKE '%{0}%' OR code LIKE '%{1}%' OR ISBN_number LIKE '%{2}%' OR author LIKE '%{3}%';";
string query2 = "SELECT recieve_case FROM lend_book WHERE book_id={0}" +
" AND lend_date=(SELECT MAX(lend_date) FROM lend_book WHERE book_id={0});";
string query3;
string[] temp;
if (book_search_txtbx.Text.Length > 2)
{
list_book.Clear();
search = book_search_txtbx.Text;
query = string.Format(query, search_with_name);
query = string.Format(query, search, search, search, search);
try
{
DatabaseProcess.conn_open();
MySqlCommand cmd = new MySqlCommand(query, DatabaseProcess.myconn);
MySqlDataReader dt_reader = cmd.ExecuteReader();
while (dt_reader.Read())
{
id = dt_reader.GetInt32("id");
name = dt_reader.GetString("name").ToUpper();
author = dt_reader.GetString("author");
code = dt_reader.GetString("code");
list_book.Add(new book(id));
book_result_lstbx.Items.Add(name + " | " + author + " | " + code);
}
DatabaseProcess.conn_close();
}
catch
{ }
if (book_result_lstbx.Items.Count <= 0)
{
book_result_lstbx.Items.Add("Eşleşen kayıt yok");
}
else if (book_search_txtbx.Text.Length > 2)
{
// Start with ">>>" substring which books on students
temp = new string[list_book.Count];
for (int i = 0; i < list_book.Count; i++)
{
temp[i] = book_result_lstbx.Items[i].ToString();
}
book_result_lstbx.Items.Clear();
for (int i = 0; i < list_book.Count; i++)
{
query3 = string.Format(query2, list_book[i].Id.ToString());
DatabaseProcess.conn_open();
MySqlCommand cmd2 = new MySqlCommand(query3, DatabaseProcess.myconn);
MySqlDataReader dt_reader2 = cmd2.ExecuteReader();
if (dt_reader2.Read())
{
recieve_case = dt_reader2.GetByte("recieve_case");
if (recieve_case == 0)
{
temp[i] = "<!> | " + temp[i];
}
}
book_result_lstbx.Items.Add(temp[i]);
DatabaseProcess.conn_close();
}
}
}//if(book_search_txtbx.Text.Length > 2)
else
{
book_result_lstbx.Items.Add("Arama için en az 3 karakter girmelisiniz.");
}
}
