//책 반납. 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); } }