public bool CreateBook(Book book)
{
try
{
string cs = CocBook.Properties.Settings.Default.connectionString;
SqlConnection con = new SqlConnection(cs);
SqlCommand cmd = new SqlCommand("Insert into Book values (@ISBN, @Name, @PublisherName, @Unit, @Price)", con);
cmd.Parameters.AddWithValue("ISBN", book.ISBNBook);
cmd.Parameters.AddWithValue("Name", book.BookName);
cmd.Parameters.AddWithValue("PublisherName", book.PublisherName);
cmd.Parameters.AddWithValue("Unit", book.Unit);
cmd.Parameters.AddWithValue("Price", book.Price);
con.Open();
int count = cmd.ExecuteNonQuery();
con.Close();
return (count == 1);
}
catch (Exception)
{
return false;
}
}
public void OrderLoadData()
{
try
{
lbDetail.Text = "Phiếu " + importExport.ImEx + "\nKiểu " + importExport.Type + "\nSố " + importExport.CheckNo;
ieDetail.CheckNo = importExport.CheckNo;
customer = customerDAL.GetCustomerbyID(importExport.CustomerID);
List<GridViewRow> list = new List<GridViewRow>();
List<IEDetail> iedetail1 = new List<IEDetail>();
iedetail1 = ieDAL.GetIEDetailByCheckNo(importExport.CheckNo);
foreach (var item in iedetail1)
{
GridViewRow gridViewRow = new GridViewRow();
gridViewRow.ISBNBook = item.ISBNBook;
Book book1 = new Book();
book1 = bookDAL.GetBookbyISBN(item.ISBNBook);
gridViewRow.BookName = book1.BookName;
gridViewRow.Unit = book1.Unit;
gridViewRow.Quantity = item.Quantity;
gridViewRow.Price = book1.Price;
gridViewRow.Discount = item.Discount;
gridViewRow.Value = item.Value;
list.Add(gridViewRow);
}
dataGridView1.DataSource = list;
}
catch (Exception ex)
{
logger.MyLogFile(DateTime.Now.ToString(), "' Error '" + ex.Message + "'");
}
}
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 List<Book> GetAllBook()
{
string cs = CocBook.Properties.Settings.Default.connectionString;
SqlConnection con = new SqlConnection(cs);
SqlCommand cmd = new SqlCommand("Select * from Book", con);
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
List<Book> list = new List<Book>();
while (sdr.Read())
{
Book book = new Book();
book.ISBNBook = (string)sdr["ISBNBook"];
book.BookName = (string)sdr["BookName"];
book.PublisherName = (string)sdr["PublisherName"];
book.Unit = (string)sdr["Unit"];
book.Price = (int)sdr["Price"];
list.Add(book);
}
con.Close();
return list;
}
public Book GetBookbyISBN(string ISBNBook)
{
string cs = CocBook.Properties.Settings.Default.connectionString;
SqlConnection con = new SqlConnection(cs);
SqlCommand cmd = new SqlCommand("Select * from Book where ISBNBook = @ISBN", con);
cmd.Parameters.AddWithValue("ISBN", ISBNBook);
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
Book book = new Book();
if (sdr.HasRows)
{
sdr.Read();
book.ISBNBook = (string)sdr["ISBNBook"];
book.BookName = (string)sdr["BookName"];
book.PublisherName = (string)sdr["PublisherName"];
book.Unit = (string)sdr["Unit"];
book.Price = (int)sdr["Price"];
return book;
}
con.Close();
return null;
}
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 chooseBook_importEvent()
{
try
{
book = chooseBook.book;
ieDetail.ISBNBook = book.ISBNBook;
txtBookName.Text = book.BookName;
}
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;
}