public void viewAllProduct()
{
search = txtSearch.Text;
try
{
productBLL prod = new productBLL();
DataTable dt = prod.viewProduct(search, "pr_name", "asc");
int i = dt.Rows.Count;
listView1.Items.Clear();
for (int j = 0; j < i; j++)
{
ListView item = new ListView();
String id = dt.Rows[j][0].ToString();
String name = dt.Rows[j][1].ToString();
String u_type = dt.Rows[j][2].ToString();
String u_price = dt.Rows[j][3].ToString();
String u_quantity = dt.Rows[j][4].ToString();
listView1.Items.Add(id);
listView1.Items[listView1.Items.Count - 1].SubItems.Add(name);
listView1.Items[listView1.Items.Count - 1].SubItems.Add(u_type);
listView1.Items[listView1.Items.Count - 1].SubItems.Add(u_price);
listView1.Items[listView1.Items.Count - 1].SubItems.Add(u_quantity);
//btnDetails.Enabled = false;
}
}
catch (Exception e)
{
//MessageBox.Show("error in data base");
}
}
public productBLL GetProductsForTransaction(string keyword)
{
productBLL p = new productBLL();
SqlConnection conn = new SqlConnection(myconnstrng);
DataTable dt = new DataTable();
try
{
string sql = "SELECT Product_Name, Purchase_Price FROM Product_Master WHERE Product_ID LIKE '%" + keyword + "%' OR Product_Name LIKE '%" + keyword + "%'";
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
conn.Open();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
p.name = dt.Rows[0]["Product_Name"].ToString();
p.rate = decimal.Parse(dt.Rows[0]["Purchase_Price"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(p);
}
public productBLL GetProductIDFromName(string ProductName)
{
productBLL p = new productBLL();
SqlConnection con = new SqlConnection(myconnstrng);
DataTable dt = new DataTable();
try
{
// GETID FROM PRODUCT TABLE
string sql = "SELECT Product_ID FROM Product_Master WHERE Product_Name='" + ProductName + "'";
SqlDataAdapter adapter = new SqlDataAdapter(sql, con);
con.Open();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
p.id = int.Parse(dt.Rows[0]["Product_ID"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
return(p);
}
public productBLL GetIDFromProductName(string productName)
{
productBLL productBLL = new productBLL();
SqlConnection connection = new SqlConnection(myconnectingstring);
DataTable dataTable = new DataTable();
try
{
string sql = "SELECT id FROM tbl_products Where name='" + productName + "'";
SqlCommand command = new SqlCommand(sql, connection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
connection.Open();
adapter.Fill(dataTable);
if (dataTable.Rows.Count > 0)
{
productBLL.id = Convert.ToInt32(dataTable.Rows[0]["id"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
return(productBLL);
}
private void txtQuntity_TextChanged(object sender, EventArgs e)
{
string pname = txtSearchProduct.Text;
productBLL p = pDAL.GetProductsForTransaction(pname);
decimal inv;
decimal.TryParse(txtQuntity.Text, out inv);
decimal qunt = p.qty - inv;
if (qunt < 0)
{
MessageBox.Show("No Inventory Please add Inventory First");
}
}
public bool Insert(productBLL product)
{
bool isSuccess = false;
SqlConnection connection = new SqlConnection(myconnectingstring);
try
{
string sql = "INSERT INTO tbl_products (name, category, description, size, rate, qty, added_date, added_by) VALUES " +
"(@name, @category, @description, @size, @rate, @qty, @added_date, @added_by)";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@name", product.name);
command.Parameters.AddWithValue("@category", product.category);
command.Parameters.AddWithValue("@description", product.description);
command.Parameters.AddWithValue("@size", product.size);
command.Parameters.AddWithValue("@rate", product.rate);
command.Parameters.AddWithValue("@qty", product.qty);
command.Parameters.AddWithValue("@added_date", product.added_date);
command.Parameters.AddWithValue("@added_by", product.added_by);
connection.Open();
int rows = command.ExecuteNonQuery();
// if the query is executed successfully then the value of rows will be greater than 0 else it will be less than 0
if (rows > 0)
{
//query is successfull
isSuccess = true;
}
else
{
//query is failed
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
return(isSuccess);
}
public void viewAllProduct()
{
search = txtSearch.Text;
if (cmbOrder.Text == "NAME")
{
orderby = "pr_name";
}
else if (cmbOrder.Text == "ID")
{
orderby = "pr_id";
}
else if (cmbOrder.Text == "UNIT_TYPE")
{
orderby = "unit_type";
}
asc = cmbAsc.Text;
try
{
productBLL prod = new productBLL();
DataTable dt = prod.viewProduct(search, orderby, asc);
int i = dt.Rows.Count;
listView1.Items.Clear();
for (int j = 0; j < i; j++)
{
ListView item = new ListView();
String id = dt.Rows[j][0].ToString();
String name = dt.Rows[j][1].ToString();
String u_type = dt.Rows[j][2].ToString();
String u_price = dt.Rows[j][3].ToString();
String u_quantity = dt.Rows[j][4].ToString();
listView1.Items.Add(id);
listView1.Items[listView1.Items.Count - 1].SubItems.Add(name);
listView1.Items[listView1.Items.Count - 1].SubItems.Add(u_type);
listView1.Items[listView1.Items.Count - 1].SubItems.Add(u_price);
listView1.Items[listView1.Items.Count - 1].SubItems.Add(u_quantity);
//btnDetails.Enabled = false;
}
}
catch (Exception e)
{
//MessageBox.Show("error in data base");
}
}
public bool Update(productBLL product)
{
bool isSuccess = false;
SqlConnection connection = new SqlConnection(myconnectingstring);
try
{
string sql = "UPDATE tbl_products SET name = @name, category = @category, description = @description,size=@size, rate = @rate, qty= @qty, added_date = @added_date, added_by = @added_by where id = @id";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@name", product.name);
command.Parameters.AddWithValue("@category", product.category);
command.Parameters.AddWithValue("@description", product.description);
command.Parameters.AddWithValue("@size", product.size);
command.Parameters.AddWithValue("@rate", product.rate);
command.Parameters.AddWithValue("@qty", product.qty);
command.Parameters.AddWithValue("@added_date", product.added_date);
command.Parameters.AddWithValue("@added_by", product.added_by);
command.Parameters.AddWithValue("@id", product.id);
connection.Open();
int rows = command.ExecuteNonQuery();
if (rows > 0)
{
//Query is successfull
isSuccess = true;
}
else
{
//Query is failed
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
return(isSuccess);
}
public productBLL GetProductsForTransaction(string keywords)
{
//Create an object for customerBLL class
productBLL productBLL = new productBLL();
// Create a database connection
SqlConnection connection = new SqlConnection(myconnectingstring);
//Create a data table to hold the value temporarily
DataTable dataTable = new DataTable();
try
{
//Write a SQL query to search dealer or customer based on keywords
string sql = "SELECT name, rate, qty from tbl_products where name LIKE '%" + keywords + "%'";
//Create a SQL data adapter to execute the query
SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
//Open the database connection
connection.Open();
//Transfer the data from SqlData Adapter to data table
adapter.Fill(dataTable);
// if we have values on dataTable we need to save it in dealer Customer BLL
if (dataTable.Rows.Count > 0)
{
productBLL.name = dataTable.Rows[0]["name"].ToString();
productBLL.rate = Convert.ToInt32(dataTable.Rows[0]["rate"].ToString());
productBLL.qty = Convert.ToInt32(dataTable.Rows[0]["qty"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
return(productBLL);
}
private void txtSearchProduct_TextChanged(object sender, EventArgs e)
{
string keyword = txtSearchProduct.Text;
if (keyword == "")
{
txtProductName.Text = "";
txtInventory.Text = "0";
txtRate.Text = "0";
txtQuntity.Text = "0";
return;
}
productBLL p = pDAL.GetProductsForTransaction(keyword);
txtProductName.Text = p.name;
txtInventory.Text = p.qty.ToString();
txtRate.Text = p.rate.ToString();
}
private int addProductNow()
{
productBLL product = new productBLL();
try
{
//long pr_id, string pr_name, string unit_type, long unit_price, long quantity
long i = product.addProduct(txtProductName.Text, cmbUnitType.Text, Convert.ToInt64(txtProductPrice.Text), Convert.ToInt64(txtProductQuantity.Text));
if (i != 0)
{
//login(i);
return(1);
}
else
{
return(0);
}
}
catch (Exception e)
{
return(0);
}
}
public bool Delete(productBLL product)
{
bool isSuccess = false;
SqlConnection connection = new SqlConnection(myconnectingstring);
try
{
string sql = "DELETE from tbl_products where id = @id";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@id", product.id);
connection.Open();
int rows = command.ExecuteNonQuery();
if (rows > 0)
{
//Query is successfull
isSuccess = true;
}
else
{
//Query is failed
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
return(isSuccess);
}
public void save()
{
salesBLL sales = new salesBLL();
string cname = txtName.Text;
if (cname != "")
{
customerBLL c = cDAL.getCustomerIdFromName(cname);
sales.salesdate = dtpBillDate.Value;
sales.custid = c.id;
sales.grandtotal = decimal.Parse(txtGrandTotal.Text);
sales.gst = decimal.Parse(txtGst.Text);
sales.discount = decimal.Parse(txtDiscount.Text);
sales.salesdetails = salesdt;
bool isSuccess = false;
// using (TransactionScope scope = new TransactionScope())
{
int salesid = -1;
bool b = s.insertsales(sales, out salesid);
for (int i = 0; i < salesdt.Rows.Count; i++)
{
salesdetailsBLL sdb = new salesdetailsBLL();
string productName = salesdt.Rows[i][1].ToString();
productBLL p = pDAL.GetProductIDFromName(productName);
sdb.productid = p.id;
sdb.rate = decimal.Parse(salesdt.Rows[i][2].ToString());
sdb.qty = decimal.Parse(salesdt.Rows[i][3].ToString());
sdb.total = Math.Round(decimal.Parse(salesdt.Rows[i][4].ToString()), 2);
sdb.custid = c.id;
sdb.addeddate = dtpBillDate.Value;
if (b == true)
{
bool x = pDAL.DecreaseProduct(sdb.productid, sdb.qty);
}
bool y = sd.insertsalesdetails(sdb);
isSuccess = b && y;
}
if (isSuccess == true)
{
//scope.Complete();
MessageBox.Show("Transaction Completed");
clear();
}
else
{
MessageBox.Show("Transaction Failed");
}
}
}
else
{
MessageBox.Show("Please Select Customer Details");
}
}
private void button1_Click(object sender, EventArgs e)
{
if (cmbBoxTransactionType.Text == "Giao hàng" && txtBoxCustomerAddress.Text == "")
{
MessageBox.Show("Bạn chưa điền địa chỉ giao hàng của khách!");
}
else if (txtBoxCustomerName.Text == "")
{
MessageBox.Show("Bạn quên điền thông tin khách hàng");
}
else if (Convert.ToInt32(txtBoxSubTotal.Text.Replace(",", "")) == 0)
{
MessageBox.Show("Bạn chưa thêm món ăn vào đơn ");
}
else
{
int totalRows = dgvAddedProducts.Rows.Count;
int verticalLength = (totalRows * 3 + 23) * 13;
printPreviewDialog1.Document = printDocument1;
printDocument1.DefaultPageSettings.PaperSize = new System.Drawing.Printing.PaperSize("pprnm", 300, verticalLength);
printPreviewDialog1.ShowDialog();
//Get the value from Purchase sales
transactionBLL transactionBLL = new transactionBLL();
//Get customer details
transactionBLL.customer_name = txtBoxCustomerName.Text;
transactionBLL.customer_contact = txtBoxCustomerContact.Text;
transactionBLL.type = cmbBoxTransactionType.Text;
transactionBLL.grandTotal = Convert.ToInt32(txtBoxGrandTotal.Text.Replace(",", ""));
transactionBLL.transaction_date = DateTime.Now;
transactionBLL.tax = 0;
transactionBLL.discount = Convert.ToInt32(txtBoxDiscount.Text);
transactionBLL.billText = receipt;
string username = fLogin.logged_in;
transactionBLL.added_by = username;
transactionBLL.transactionDetails = transactionDT;
//Lets create a boolean var and set its value to false
bool success = false;
int transactionID = -1;
//Create a boolean value and insert transaction
bool transactionInsert = transactionDAL.Insert_Transaction(transactionBLL);
//Use for loop to insert transaction details
for (int i = 0; i < transactionDT.Rows.Count; i++)
{
//Get all the details of the product
transactionDetailBLL transactionDetailBLL = new transactionDetailBLL();
//Get the product name and convert it to ID
string ProductName = transactionDT.Rows[i][0].ToString();
productBLL productBLL = productDAl.GetIDFromProductName(ProductName);
transactionDetailBLL.product_id = productBLL.id;
transactionDetailBLL.product_note = transactionDT.Rows[i][1].ToString();
transactionDetailBLL.rate = Convert.ToInt32(transactionDT.Rows[i][2].ToString().Replace(",", ""));
transactionDetailBLL.qty = Convert.ToInt32(transactionDT.Rows[i][3].ToString());
transactionDetailBLL.total = Convert.ToInt32(transactionDT.Rows[i][4].ToString().Replace(",", ""));
transactionDetailBLL.customer_name = txtBoxCustomerName.Text;
transactionDetailBLL.customer_contact = txtBoxCustomerContact.Text;
transactionDetailBLL.added_date = DateTime.Now;
transactionDetailBLL.added_by = username;
//Insert transaction details inside the database
bool transactionDetailInsert = transactionDetailDAL.InsertTransactionDetail(transactionDetailBLL);
success = transactionInsert && transactionDetailInsert;
}
if (success = true)
{
MessageBox.Show("Hóa đơn hoàn tất");
//Print bill
receipt = "-----------------------------------------------------------------------------" + Environment.NewLine;
clear();
}
else
{
//transaction failed
MessageBox.Show("Hóa đơn thất bại");
}
}
}