//책 반납.
public static void ReturnBook(int rBookRN, int rMemberId)
{
try
{
string myConnection = DbConn.conn;
MySqlConnection myConn = new MySqlConnection(myConnection);
bool chkBook = false;
bool chkMember = false;
myConn.Open();
//이 책은 반납할 수 있는 책인가? - rent에서 일치 검색.
MySqlCommand cmd = new MySqlCommand(
SearchQueryManager.makeSearchQuery("rent", "BookRegisterNumber", rBookRN.ToString()), myConn);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
chkBook = true;
}
else
{
MessageBox.Show("대출중인 책이 아닙니다.");
}
reader.Close();
cmd = new MySqlCommand(
SearchQueryManager.makeSearchQuery("rent", "MemberId", rMemberId.ToString()), myConn);
reader = cmd.ExecuteReader();
if (reader.Read())
{
chkMember = true;
}
else
{
MessageBox.Show("error manager 190");
}
reader.Close();
if (chkBook && chkMember)
{
MySqlCommand cmdDel = new MySqlCommand(
"Delete from bookmanagement.rent where MemberId=" + rMemberId + " and BookRegisterNumber=" + rBookRN + " ;", myConn);
MySqlCommand cmdStat = new MySqlCommand(
"UPDATE bookmanagement.books SET BookState='대출가능' WHERE BookRegisterNumber=" + rBookRN + ";", myConn);
cmdDel.ExecuteNonQuery();
cmdStat.ExecuteNonQuery();
MessageBox.Show("도서 반납 성공");
}
myConn.Close();
}
catch (Exception e)
{
MessageBox.Show(e.StackTrace);
MessageBox.Show(e.Message);
}
}
//BookRegisterNumber / MemberId
public static void RentBook(int rBookRN, int rMemberId)
{
try
{
string myConnection = DbConn.conn;
//string myConnection = "datasource=localhost;port=3306;username=root;password=eoghks5953!";
MySqlConnection myConn = new MySqlConnection(myConnection);
bool chkBook = false;
bool chkMember = false;
bool rentAble = false;
myConn.Open();
MySqlCommand cmd = new MySqlCommand(
SearchQueryManager.makeSearchQuery("books", "BookRegisterNumber", rBookRN.ToString()), myConn);
MySqlDataReader reader_Book = cmd.ExecuteReader();
if (!reader_Book.Read())
{
chkBook = false;
MessageBox.Show("해당 책이 존재하지 않습니다.");
}
else if (((string)reader_Book["BookState"]).Equals("대출가능"))
{
//MessageBox.Show("111");
chkBook = true;
rentAble = true;
}
reader_Book.Close();
cmd = new MySqlCommand(
SearchQueryManager.makeSearchQuery("members", "MemberId", rMemberId.ToString()), myConn);
MySqlDataReader reader_Member = cmd.ExecuteReader();
if (reader_Member != null)
{
chkMember = true;
}
else
{
MessageBox.Show("존재하지 않는 회원번호 입니다.");
}
reader_Member.Close();
if (chkBook && chkMember && rentAble)
{
string date = DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day;
DateTime d = DateTime.Today.AddDays(7);
string date_last = d.Year + "-" + d.Month + "-" + d.Day;
MySqlCommand cmdr = new MySqlCommand(
"INSERT INTO bookmanagement.rent VALUES(" + rMemberId + ", " + rBookRN + ", '" + date + "', '" + date_last + "' );", myConn);
MySqlCommand cmdb = new MySqlCommand(
"UPDATE bookmanagement.books SET BookState='대출중' WHERE BookRegisterNumber=" + rBookRN + ";", myConn);
cmdr.ExecuteNonQuery();
cmdb.ExecuteNonQuery();
MessageBox.Show("도서 대출 성공");
}
else if (!rentAble)
{
MessageBox.Show("현재 대출이 불가능합니다.");
}
myConn.Close();
}
catch (Exception e)
{
MessageBox.Show("문제가 발생했습니다!!!!");
MessageBox.Show(e.StackTrace);
MessageBox.Show(e.Message);
}
}
private void button1_Click(object sender, EventArgs e)
{
dataGridView1.DataSource = "";
string selected = "";
if (comboBox1.SelectedItem != null)
{
selected = comboBox1.SelectedItem.ToString();
}
Console.WriteLine(selected);
string tableName = "books";
string search = "";
string keyword = textBox1.Text;
switch (selected)
{
case "등록번호":
search = "BookRegisterNumber";
break;
case "서명":
search = "BookName";
break;
case "총서명":
search = "BookFullName";
break;
case "저자":
search = "BookAuthor";
break;
default:
break;
}
DataTable table = new DataTable();
/*
* 순번 books
* 도서상태 books
* 등록번호 books
* 도서명 books
* 저자 books
* 청구기호 books
* 대출일자 ? - rent
* 반납일자 ? - rent
* 대출자 members
* 예약자 members - 미사용
* 소장위치 books
* 반입구분 books
* 등록일 books
*
*/
//book
table.Columns.Add("순번", typeof(int));
table.Columns.Add("도서상태", typeof(string));
table.Columns.Add("등록번호", typeof(int));
table.Columns.Add("도서명", typeof(string));
table.Columns.Add("저자", typeof(string));
// table.Columns.Add("청구기호", typeof(string));//미사용
//rent
table.Columns.Add("대출일자", typeof(string));
table.Columns.Add("반납일자", typeof(string));
table.Columns.Add("대출자", typeof(string));
// table.Columns.Add("예약자", typeof(string));//미사용
table.Columns.Add("소장위치", typeof(string));
// table.Columns.Add("반입구분", typeof(string));// 임시 해제
table.Columns.Add("등록일", typeof(string));
/*
* 수정 중
*/
foreach (var item in BookManager.Load(SearchQueryManager.makeSearchQuery(tableName, search, keyword)))
{
Rent r = null;
Member m = null;
if (RentManager.Load("select * from bookmanagement.rent where BookRegisterNumber=" + item.BookRegisterNumber + ";").Count > 0)
{
r = RentManager.Load("select * from bookmanagement.rent where BookRegisterNumber=" + item.BookRegisterNumber + ";")[0];
m = MemberManager.Load(SearchQueryManager.makeSearchQuery("members", "MemberId", r.MemberId.ToString()))[0];
}
if (r != null)
{
table.Rows.Add(item.Num, item.BookState, item.BookRegisterNumber, item.BookName, item.BookAuthor, r.Rent_Date, r.Rent_Date_Last, m.MemberName, item.BookLocation, item.BookPublishDate);
}
else
{
table.Rows.Add(item.Num, item.BookState, item.BookRegisterNumber, item.BookName, item.BookAuthor, "-", "-", "-", item.BookLocation, item.BookPublishDate);
}
}
/*
*
*/
dataGridView1.DataSource = table;
}
private void button1_Click(object sender, EventArgs e)
{
try
{
dataGridView1.DataSource = "";
string selected = "";
if (comboBox1.SelectedItem != null)
{
selected = comboBox1.SelectedItem.ToString();
}
Console.WriteLine(selected);
string tableName = "members";
string search = "";
string keyword = textBox1.Text;
switch (selected)
{
case "회원번호":
search = "MemberId";
break;
case "회원명":
search = "MemberName";
break;
case "회원상태":
search = "MemberState";
break;
case "주소":
search = "MemberAdress";
break;
case "전화번호":
search = "MemberPhoneNumber";
break;
case "이메일":
search = "MemberMail";
break;
default:
break;
}
Console.WriteLine("search = " + search);
DataTable table = new DataTable();
table.Columns.Add("회원번호", typeof(int));
table.Columns.Add("이름", typeof(string));
table.Columns.Add("상태", typeof(string));
table.Columns.Add("주소", typeof(string));
table.Columns.Add("전화번호", typeof(string));
table.Columns.Add("이메일", typeof(string));
table.Columns.Add("가입일", typeof(string));
foreach (var item in MemberManager.Load(SearchQueryManager.makeSearchQuery(tableName, search, keyword)))
{
table.Rows.Add(item.MemberId, item.MemberName, item.MemberState, item.MemberAdress, item.MemberPhoneNumber, item.MemberMail, item.MemberJoined);
}
dataGridView1.DataSource = table;
}
catch (Exception ex)
{
MessageBox.Show("잘못된 검색입니다");
//MessageBox.Show(ex.Message);
}
}
