public List<Store> GetAllStore()
{
string cs = CocBook.Properties.Settings.Default.connectionString;
SqlConnection con = new SqlConnection(cs);
SqlCommand cmd = new SqlCommand("Select * from BookStore", con);
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
List<Store> list = new List<Store>();
while (sdr.Read())
{
Store store = new Store();
store.ISBNBook = sdr["ISBN"].ToString();
string ISBN = store.ISBNBook;
store.Quantity = (int)sdr["Quantity"];
Book book = new Book();
BookDAL bookDAL = new BookDAL();
book = bookDAL.GetBookbyISBN(ISBN);
store.BookName = book.BookName;
store.Publisher = book.PublisherName;
store.Unit = book.Unit;
store.Price = book.Price;
list.Add(store);
}
con.Close();
return list;
}
public void LoadAllData()
{
BookDAL BookDAL = new BookDAL();
List<Book> list = BookDAL.GetAllBook();
BookGridView.DataSource = list;
}
private void SaveToDB()
{
Book book = new Book();
BookDAL bookDAL = new BookDAL();
try
{
book.ISBNBook = txtISBN.Text;
book.BookName = txtBookName.Text;
book.PublisherName = txtPublisher.Text;
book.Unit = txtUnit.Text;
bool priceChange = (book.Price == int.Parse(txtPrice.Text));
book.Price = int.Parse(txtPrice.Text);
bool rs;
if (isAdd)
{
rs = bookDAL.CreateBook(book);
}
else
{
rs = bookDAL.UpdateBook(book);
if (priceChange == false)
{
List<IEDetail> ieDetailList = new List<IEDetail>();
IEDetailDAL ieDetailDAL = new IEDetailDAL();
ieDetailList = ieDetailDAL.GetIEDetailByISBN(book.ISBNBook);
if (ieDetailList != null)
{
foreach (var ieDetail in ieDetailList)
{
ieDetail.Value=(book.Price * ieDetail.Quantity * (100 - ieDetail.Discount)) / 100;
ieDetailDAL.UpdateIEDetail(ieDetail);
}
}
}
}
if (rs)
{
MessageBox.Show("Đã lưu !");
LoadAllData();
makeAllBlank();
}
else
{
MessageBox.Show("Không thể lưu !");
}
}
catch (Exception ex)
{
logger.MyLogFile(DateTime.Now.ToString(), "' Error '" + ex.Message + "'"); ;
}
}
private void btnImportFromExcel_Click(object sender, EventArgs e)
{
try
{
openFileDialog1.ShowDialog();
string filename = openFileDialog1.FileName;
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkBook = xlApp.Workbooks.Open(filename, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 2;
BookDAL bookDAL = new BookDAL();
while (xlWorkSheet.get_Range("A" + i, "A" + i).Value2 != null)
{
Book book = new Book();
book.BookName = xlWorkSheet.get_Range("A" + i, "A" + i).Value2.ToString();
book.PublisherName = xlWorkSheet.get_Range("B" + i, "B" + i).Value2.ToString();
book.Unit = xlWorkSheet.get_Range("C" + i, "C" + i).Value2.ToString();
book.Price = int.Parse(xlWorkSheet.get_Range("D" + i, "D" + i).Value2.ToString());
book.ISBNBook = xlWorkSheet.get_Range("E" + i, "E" + i).Value2.ToString();
i++;
Book book1 = bookDAL.GetBookbyISBN(book.ISBNBook);
if (book1 == null)
{
bookDAL.CreateBook(book);
}
else if (book1 != book)
{
if (MessageBox.Show("Bạn có cập nhật " + book1.BookName + " - " + book1.PublisherName + " - " + book1.Price + " thành " + book.BookName + " - " + book.PublisherName + " - " + book.Price, "Xác nhận", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
bookDAL.UpdateBook(book1);
}
}
}
MessageBox.Show("Thêm sách thành công");
LoadAllData();
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
catch (Exception ex)
{
logger.MyLogFile(DateTime.Now.ToString(), "' Error '" + ex.Message + "'");
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
if (CanDeleted)
{
try
{
if (MessageBox.Show("Bạn có muốn xóa ?", "Xác nhận", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
string isbn = BookGridView.SelectedRows[0].Cells[0].Value.ToString();
BookDAL bookDAL = new BookDAL();
bookDAL.DeleteBook(isbn);
}
makeAllBlank();
LoadAllData();
}
catch (SqlException sqlEx)
{
MessageBox.Show("Không thể xóa vì sách đã có trong phiếu !");
logger.MyLogFile(DateTime.Now.ToString(), "' Error '" + sqlEx.Message + "'");
}
catch (Exception ex)
{
logger.MyLogFile(DateTime.Now.ToString(), "' Error '" + ex.Message + "'");
}
}
else
{
try
{
if (book.ISBNBook != null)
{
Close();
if (chooseEvent != null)
{
chooseEvent();
}
}
else
{
MessageBox.Show("Vui lòng chọn sách !");
}
}
catch (Exception ex)
{
logger.MyLogFile(DateTime.Now.ToString(), "' Error '" + ex.Message + "'");
}
}
}
//Use in ViewStore
public Store GetBookStorebyISBN(string ISBNBookStore)
{
string cs = CocBook.Properties.Settings.Default.connectionString;
SqlConnection con = new SqlConnection(cs);
SqlCommand cmd = new SqlCommand("Select * from BookStore where ISBN = @ISBN", con);
cmd.Parameters.AddWithValue("ISBN", ISBNBookStore);
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
Store store = new Store();
if (sdr.HasRows)
{
sdr.Read();
store.ISBNBook = sdr["ISBN"].ToString();
string ISBN = store.ISBNBook;
store.Quantity = (int)sdr["Quantity"];
BookDAL bookDAL = new BookDAL();
Book book = new Book();
book = bookDAL.GetBookbyISBN(ISBN);
store.BookName = book.BookName;
store.Publisher = book.PublisherName;
store.Unit = book.Unit;
store.Price = book.Price;
return store;
}
con.Close();
return null;
}
public List<Store> GetBookStorebyPublisher(string publisher)
{
List<Store> list = new List<Store>();
List<Book> listBook = new List<Book>();
BookDAL bookDAL = new BookDAL();
listBook = bookDAL.GetBookbyPublisherName(publisher);
foreach (var book in listBook)
{
Store store = new Store();
store.ISBNBook = book.ISBNBook;
store.BookName = book.BookName;
store.Publisher = book.PublisherName;
store.Unit = book.Unit;
store.Price = book.Price;
string ISBN = book.ISBNBook;
string cs = CocBook.Properties.Settings.Default.connectionString;
SqlConnection con = new SqlConnection(cs);
SqlCommand cmd = new SqlCommand("Select * from BookStore where ISBN = @ISBN", con);
cmd.Parameters.AddWithValue("ISBN", ISBN);
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
if (sdr.HasRows)
{
sdr.Read();
store.Quantity = (int)sdr["Quantity"];
}
con.Close();
list.Add(store);
}
return list;
}
