public bool InsertTransaction(transactionBLL t, out int transactionID)
{
//create Sql connection first
SqlConnection conn = new SqlConnection(myconnstrng);
//create a boolean Value and set it default Value to false
bool isSuccess = false;
//set the transactionID to negative 1.
transactionID = -1;
try
{
//Create SQL quary to Insert Detail of Dea_Cust table in database
string sql = "INSERT INTO tbl_transaction (type, dea_cust_id, grandTotal, transaction_date, tax, discount, added_by) VALUES (@type, @dea_cust_id, @grandTotal, @transaction_date, @tax, @discount, @added_by); SELECT @@IDENTITY;";
//create SQL Command to Execute the Quary
SqlCommand cmd = new SqlCommand(sql, conn);
//Passing the value using parameter
cmd.Parameters.AddWithValue("@type", t.type);
cmd.Parameters.AddWithValue("@dea_cust_id", t.dea_cus_id);
cmd.Parameters.AddWithValue("@grandTotal", t.grandTotal);
cmd.Parameters.AddWithValue("@transaction_date", t.transaction_date);
cmd.Parameters.AddWithValue("@tax", t.tax);
cmd.Parameters.AddWithValue("@discount", t.discount);
cmd.Parameters.AddWithValue("@added_by", t.added_by);
//opening datsbase connection
conn.Open();
//create Integer variable to check if the quary is executed or not
object o = cmd.ExecuteScalar();
//If the quary executed Successfully the value of rows will be greater 0 else it will less than 0.
if (o != null)
{
//Quary is Executed Successfully
isSuccess = true;
transactionID = int.Parse(o.ToString());
}
else
{
//failed to execute the Quary
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Insert_Transaction(transactionBLL transaction)
{
//Create a boolean value and set its default value to false
bool isSuccess = false;
//Set the out transactionID value to negative -1
//Create a sqlConnection first
SqlConnection connection = new SqlConnection(myconnectingstring);
try
{
//SQL Query to insert transactions
string sql = "INSERT INTO tbl_transactions (type, customer_name, customer_contact, grandTotal, transaction_date, tax, discount, added_by, billText)" +
"VALUES (@type, @customer_name, @customer_contact, @grandTotal, @transaction_date, @tax, @discount, @added_by, @billText);";
//SQL command to pass the value in the sql query
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@type", transaction.type);
cmd.Parameters.AddWithValue("@customer_name", transaction.customer_name);
cmd.Parameters.AddWithValue("@customer_contact", transaction.customer_contact);
cmd.Parameters.AddWithValue("@grandTotal", transaction.grandTotal);
cmd.Parameters.AddWithValue("@transaction_date", transaction.transaction_date);
cmd.Parameters.AddWithValue("@tax", transaction.tax);
cmd.Parameters.AddWithValue("@discount", transaction.discount);
cmd.Parameters.AddWithValue("@added_by", transaction.added_by);
cmd.Parameters.AddWithValue("@billText", transaction.billText);
//Open database connection
connection.Open();
int rows = cmd.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
{
//Close the connection
connection.Close();
}
return(isSuccess);
}
public bool Insert_Transaction(transactionBLL t, out int transactionID)
{
//Create Boolean value and set default value t false
bool isSucces = false;
//Set the transactionID value to negative 1 i.e -1
transactionID = -1;
//Create Sql Connection
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
//Sql query to insert transaction
string sql = "insert into tbl_transaction (type,dea_cust_id,grandTotal,transaction_date,tax,discount,added_by)values(@type,@dea_cust_id,@grandTotal,@transaction_date,@tax,@discount,@added_by);SELECT @@IDENTITY;";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@type", t.type);
cmd.Parameters.AddWithValue("@dea_cust_id", t.dea_cust_id);
cmd.Parameters.AddWithValue("@grandTotal", t.grandTotal);
cmd.Parameters.AddWithValue("@transaction_date", t.transaction_date);
cmd.Parameters.AddWithValue("@tax", t.tax);
cmd.Parameters.AddWithValue("@discount", t.discount);
cmd.Parameters.AddWithValue("@added_by", t.added_by);
//Open Connection
conn.Open();
object o = cmd.ExecuteScalar();
if (o != null)
{
transactionID = int.Parse(o.ToString());
isSucces = true;
}
else
{
isSucces = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSucces);
}
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");
}
}
}
private void btnSave_Click(object sender, EventArgs e)
{
//get the value from purchase form first
transactionBLL transaction = new transactionBLL();
transaction.type = lblTop.Text;
//get the ID of dealer or customer here.
//let get the name of dealer first
string DeaCustName = txtName.Text;
DeaCustBLL dc = dcDAL.GetDeaCustIDFromName(DeaCustName);
transaction.dea_cus_id = dc.id;
transaction.grandTotal = Math.Round(decimal.Parse(txtGrandTotal.Text), 2);
transaction.transaction_date = DateTime.Now;
transaction.tax = decimal.Parse(txtVat.Text);
transaction.discount = decimal.Parse(txtDiscount.Text);
//Get the username of logged in user
string username = frmLogin.loggedIn;
userBLL u = uDAL.GetIDFromUsername(username);
transaction.added_by = u.id;
transaction.transactionDetails = transactionDT;
//lets create boolean variable and set it value to false/
bool success = false;
//Actual Code to insert transaction and transaction detail
using (TransactionScope scope = new TransactionScope())
{
int transactionID = -1;
//create a boolean Value and insert transaction
bool w = tDAL.InsertTransaction(transaction, out transactionID);
//use for loop to insert transaction Detail
for (int i = 0; i < transactionDT.Rows.Count; i++)
{
//Get all the detail of products
transactionDetailBLL transactionDetail = new transactionDetailBLL();
//Get the products name and convert ID
string ProductName = transactionDT.Rows[i][0].ToString();
productsBLL p = pDAL.GetProductIDFromName(ProductName);
transactionDetail.product_id = p.id;
transactionDetail.rate = decimal.Parse(transactionDT.Rows[i][1].ToString());
transactionDetail.qty = decimal.Parse(transactionDT.Rows[i][2].ToString());
transactionDetail.total = Math.Round(decimal.Parse(transactionDT.Rows[i][3].ToString()), 2);
transactionDetail.dea_cust_id = dc.id;
transactionDetail.added_date = DateTime.Now;
transactionDetail.added_by = u.id;
//Here Increase or Decrease product Quantity based on Pyurchase or Sales
string transactionType = lblTop.Text;
//let check weather we are in purchase or sales
bool x = false;
if (transactionType == "PURCHASE")
{
x = pDAL.Increaseproduct(transactionDetail.product_id, transactionDetail.qty);
//Increase the product
}
else if (transactionType == "SALES")
{
//decrease the product
x = pDAL.DecreaseProduct(transactionDetail.product_id, transactionDetail.qty);
}
//Insert transaction Detail in to DataBase
bool y = tdDAL.InsertTransactionDetail(transactionDetail);
success = w && x && y;
}
if (success == true)
{
//Transaction Completed
scope.Complete();
//Code to Print the Bill of products
DGVPrinter printer = new DGVPrinter();
printer.Title = "\r\n\r\n\r\n ANYSTORE PVT.LTD. \r\n\r\n";
printer.SubTitle = "Highfrezh, Ajoke \r\n phone: 01-41XXXXX \r\n\r\n";
printer.SubTitleFormatFlags = StringFormatFlags.LineLimit | StringFormatFlags.NoClip;
printer.PageNumbers = true;
printer.PageNumberInHeader = false;
printer.PorportionalColumns = true;
printer.HeaderCellAlignment = StringAlignment.Near;
printer.Footer = "Discount: " + txtDiscount.Text + "% \r\n" + "VAT: " + txtVat.Text + "% \r\n" + "Grand Total: " + txtGrandTotal.Text + "\r\n\r\n" + "Thanks for doing business with us";
printer.FooterSpacing = 15;
printer.PrintDataGridView(dgvAddedProducts);
MessageBox.Show("Transaction Complete successfully");
//Clear datagrid veiw and all the TextBoxs
dgvAddedProducts.DataSource = null;
dgvAddedProducts.Rows.Clear();
txtSearch.Text = "";
txtName.Text = "";
txtEmail.Text = "";
txtContact.Text = "";
txtAddress.Text = "";
txtProductSearch.Text = "";
txtProductName.Text = "";
txtInventory.Text = "0";
txtRate.Text = "0";
txtQty.Text = "0";
txtSubTotal.Text = "0";
txtDiscount.Text = "0";
txtVat.Text = "0";
txtGrandTotal.Text = "0";
txtPaidAmount.Text = "0";
txtReturnAmount.Text = "0";
}
else
{
//Transaction Failed
MessageBox.Show("Transaction Failed");
}
}
}
private void BtnSave_Click(object sender, EventArgs e)
{
//Get the values from Purchase from frst
transactionBLL transaction = new transactionBLL();
transaction.type = lblTop.Text;
//Get the ID of dealer or customer her
//lets get name of the dealer or customer first
string deaCustName = txtName.Text;
DeaCustBLL dc = dcDAL.GetDeaCustIDFromName(deaCustName);
transaction.dea_cust_id = dc.id;
transaction.grandTotal = decimal.Parse(txtGrandTotal.Text);
transaction.transaction_date = DateTime.Now;
transaction.tax = decimal.Parse(txtVAT.Text);
transaction.discount = decimal.Parse(txtDiscount.Text);
//Get the username logged in user
string username = frmLogin.loggedIn;
userBLL u = uDal.GetIDFromUsername(username);
transaction.added_by = u.id;
transaction.transactionDetails = transactionDT;
bool success = false;
//Actual code to insert transaction and transaction details
using (TransactionScope scope = new TransactionScope())
{
int transactionID = -1;
///create boolean value and insert transaction
bool w = tDAL.Insert_Transaction(transaction, out transactionID);
//User for lopping transaction detaio
for (int i = 0; i < transactionDT.Rows.Count; i++)
{
//Get all the details of the product
transaction_detailBLL transactionDetail = new transaction_detailBLL();
//Get the Product name and convert it to id
string Productname = txtProductName.Text;
productsBLL p = pDAL.GetProductIDFromName(ProductName);
transactionDetail.product_id = p.id;
transactionDetail.rate = decimal.Parse(transactionDT.Rows[i][1].ToString());
transactionDetail.qty = decimal.Parse(transactionDT.Rows[i][2].ToString());
transactionDetail.total = Math.Round(decimal.Parse(transactionDT.Rows[i][3].ToString()), 3);
transactionDetail.dea_cust_id = dc.id;
transactionDetail.added_date = DateTime.Now;
transactionDetail.added_by = u.id;
//Here increase or Decrease Product Quantitiy based on Purchase or sales
string transactionType = lblTop.Text;
//Lets check wheather we are on purchase or sales
bool x = false;
if (transactionType == "Purchase")
{
x = pDAL.IncreaseProduct(transactionDetail.product_id, transactionDetail.qty);
}
else if (transactionType == "Sales")
{
//Decrease the Product Quantitiy
x = pDAL.DecreaseProduct(transactionDetail.product_id, transactionDetail.qty);
}
//Insert Transaction Detail inside the Databsae
bool y = tdDAL.InsertTransactionDetail(transactionDetail);
success = w && y;
}
if (success == true)
{
//Transaction Completed
scope.Complete();
//Code to print bill
DGVPrinter printer = new DGVPrinter();
printer.Title = "\r\n\r\n ANYSTORE PVT. LTD.";
printer.SubTitle = "Jepara, Suwawal \r\n Phone: 0895386989706";
printer.SubTitleFormatFlags = StringFormatFlags.LineLimit | StringFormatFlags.NoClip;
printer.PageNumbers = true;
printer.PageNumberInHeader = false;
printer.PorportionalColumns = true;
printer.HeaderCellAlignment = StringAlignment.Near;
printer.Footer = "Discount :" + txtDiscount.Text + "% \r\n " + "VAT:" + txtVAT.Text + "% \r\n" + "Grand Total:" + "r\n" + "Thank you for doing buisness with us ";
printer.FooterSpacing = 15;
printer.PrintDataGridView(dgvAddedProducts);
MessageBox.Show("Transaction Completed Successfully");
dgvAddedProducts.DataSource = null;
dgvAddedProducts.Rows.Clear();
txtSearch.Text = "";
txtName.Text = "";
txtEmail.Text = "";
txtContact.Text = "";
txtAddress.Text = "";
txtSearchProduct.Text = "";
txtProductName.Text = "";
txtInventory.Text = "";
txtRate.Text = "";
txtQty.Text = "0";
txtSubTotal.Text = "0";
txtDiscount.Text = "0";
txtVAT.Text = "0";
txtGrandTotal.Text = "0";
txtPaidAmount.Text = "0";
txtReturnAmount.Text = "0";
}
else
{
MessageBox.Show("Transaction Failed ");
}
}
}
