public deaCustBLL GetDeaCustIDFromName(string Name)
{
//First create an object of deacustBLL and return it
deaCustBLL dc = new deaCustBLL();
//sql conn
SqlConnection conn = new SqlConnection(myconnstring);
//data table to hold data temporariliy
DataTable dt = new DataTable();
try
{
// sql query to get id based on name
string sql = "SELECT id FROM tbl_dea_cust WHERE name='" + Name + "'";
//create the sql data adapter
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
conn.Open();
//passing the value from adapter to data table
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
//pass the value from dt to deacustBLLdc
dc.id = int.Parse(dt.Rows[0]["id"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(dc);
}
public bool Insert(deaCustBLL dc)
{
//creating sql connection
SqlConnection conn = new SqlConnection(myconnstring);
//create a boolean value and set its default to false
bool isSuccess = false;
try
{
//sql query to insert deatils of dealer or customer
string sql = "INSERT INTO tbl_dea_cust (type, name, email, contact, address, added_date, added_by) VALUES (@type, @name, @email, @contact, @address, @added_date, @added_by)";
//sql command to pass the values to execute the query
SqlCommand cmd = new SqlCommand(sql, conn);
//passing the values usiing parameters
cmd.Parameters.AddWithValue("@type", dc.type);
cmd.Parameters.AddWithValue("@name", dc.name);
cmd.Parameters.AddWithValue("@email", dc.email);
cmd.Parameters.AddWithValue("@contact", dc.contact);
cmd.Parameters.AddWithValue("@address", dc.address);
cmd.Parameters.AddWithValue("@added_date", dc.added_date);
cmd.Parameters.AddWithValue("@added_by", dc.added_by);
//open the db conn
conn.Open();
//creating int variable to check wheather the query the executed or not
int rows = cmd.ExecuteNonQuery();
//if the query is executed successfully then the value of the rows will be >0 else <0
if (rows > 0)
{
//query executed successfully
isSuccess = true;
}
else
{
//failed to execute the query
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Update(deaCustBLL dc)
{
//sql conn for db conn
SqlConnection conn = new SqlConnection(myconnstring);
//create a boolean variable and set its deafult value to false
bool isSuccess = false;
try
{
//sql query to update in db
string sql = "UPDATE tbl_dea_cust SET type=@type, name=@name, email=@email, contact=@contact, address=@address, added_date=@added_date, added_by=@added_by WHERE id=@id";
//create sql command to pass the values
SqlCommand cmd = new SqlCommand(sql, conn);
//passing the values through parameters
cmd.Parameters.AddWithValue("@type", dc.type);
cmd.Parameters.AddWithValue("@name", dc.name);
cmd.Parameters.AddWithValue("@email", dc.email);
cmd.Parameters.AddWithValue("@contact", dc.contact);
cmd.Parameters.AddWithValue("@address", dc.address);
cmd.Parameters.AddWithValue("@added_date", dc.added_date);
cmd.Parameters.AddWithValue("@added_by", dc.added_by);
cmd.Parameters.AddWithValue("@id", dc.id);
conn.Open();
//int variable to check if the query is successfully executed or !
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
//Query executed successfully
isSuccess = true;
}
else
{
//Failed to execute the query
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public deaCustBLL searchDealerCustomerforTransaction(string keyword)
{
//Creating object for dea cust bll class
deaCustBLL dc = new deaCustBLL();
//creating a database connection
SqlConnection conn = new SqlConnection(myconnstring);
//creating a data table to hold the value temporarily
DataTable dt = new DataTable();
try
{
//writing a sql query to search dealer or customer based on keywords
string sql = "SELECT name, email, contact, address from tbl_dea_cust WHERE id LIKE '%" + keyword + "%' OR name LIKE '%" + keyword + "%'";
//creating a sql query data adapater to execute the query
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
conn.Open();
//transfer the data from sqldata adapter to data table
adapter.Fill(dt);
//if we have values on dt we need to save in dealercustomer Bll
if (dt.Rows.Count > 0)
{
dc.name = dt.Rows[0]["name"].ToString();
dc.email = dt.Rows[0]["email"].ToString();
dc.contact = dt.Rows[0]["contact"].ToString();
dc.address = dt.Rows[0]["address"].ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(dc);
}
public bool Delete(deaCustBLL dc)
{
//creating sql connection
SqlConnection conn = new SqlConnection(myconnstring);
//create a boolean value and set its default to false
bool isSuccess = false;
try
{
//sql query to delet data from db
string sql = "DELETE FROM tbl_dea_cust WHERE id=@id";
//sql command to pass the value
SqlCommand cmd = new SqlCommand(sql, conn);
//passing the valuue
cmd.Parameters.AddWithValue("@id", dc.id);
//open the db conn
conn.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
//Qury executed successfully
isSuccess = true;
}
else
{
//Failed to execute the query
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
private void txtDandCSearch_TextChanged(object sender, EventArgs e)
{
//get the keyword from the text box
string keywords = txtDandCSearch.Text;
if (keywords == "")
{
//clear all the text boxes
txtDandCName.Text = "";
txtDandCEmail.Text = "";
txtDandCContact.Text = "";
txtDandCAddress.Text = "";
return;
}
//write the code to get details and set the value on text boxes
deaCustBLL dc = dcDAL.searchDealerCustomerforTransaction(keywords);
//Now transfer or set the value from deacustBLL to textboxes
txtDandCName.Text = dc.name;
txtDandCEmail.Text = dc.email;
txtDandCContact.Text = dc.contact;
txtDandCAddress.Text = dc.address;
}
private void btnSave_Click(object sender, EventArgs e)
{
//get the details from the form purchase sales form first
transactionsBLL transaction = new transactionsBLL();
transaction.type = lblTop.Text;
//get the id of dealer or customer
//lets get name of the delar or customer first
string deaCustName = txtDandCName.Text;
deaCustBLL dc = dcDAL.GetDeaCustIDFromName(deaCustName);
transaction.dea_cust_id = dc.id;
transaction.grandTotal = Math.Round(decimal.Parse(txtGrandTotal.Text), 2);
transaction.transaction_date = DateTime.Now;
transaction.gst = decimal.Parse(txtGST.Text);
transaction.discount = decimal.Parse(txtDiscount.Text);
//get the user name of logged in user
string username = frmLogin.loggedIn;
userBLL u = uDAL.GetIDFromUsername(username);
transaction.added_by = u.id;
transaction.transactionDetails = transactiondt;
//boolean variable and set its value to FALSE
bool success = false;
//actual code to insert transaction and transaction details
using (TransactionScope scope = new TransactionScope())
{
int transactionID = -1;
//create bool value and insert transaction
bool w = tDAL.Insert_transaction(transaction, out transactionID);
//use for loop to insert transaction details
for (int i = 0; i < transactiondt.Rows.Count; i++)
{
//get all the details of the product
transactionDetailBLL transactionDetail = new transactionDetailBLL();
//get the product name and converse into id
string ProductName = transactiondt.Rows[i][0].ToString();
productsBLL p = pDAL.GetProductsIDFromName(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;
//Increase or Decrease Product Qty based on Purchase Or Sales
string transactionType = lblTop.Text;
//check whether we r on purchase or sales
bool x = false;
if (transactionType == "PURCHASE")
{
//Increase the Product
x = pDAL.IncreaseProduct(transactionDetail.product_id, transactionDetail.qty);
}
else if (transactionType == "SALES")
{
//Decrease the Product
x = pDAL.DecreaseProduct(transactionDetail.product_id, transactionDetail.qty);
}
//Insert transactionsDetails inside the db
bool y = tdDAL.InsertTransactionDetail(transactionDetail);
success = w && x && y;
}
if (success == true)
{
//transaction is Compelete
scope.Complete();
//scope to print the bill
DGVPrinter printer = new DGVPrinter();
printer.Title = "\r\n\r\n\r\nRISHI SOFTWARE PVT.LTD\r\n\r\n";
printer.SubTitle = "Kenduadihi, Bankura \r\n Phone:-89720635\r\n\r\n";
printer.SubTitleFormatFlags = StringFormatFlags.LineLimit | StringFormatFlags.NoClip;
printer.PageNumbers = true;
printer.PageNumberInHeader = false;
printer.PorportionalColumns = true;
printer.HeaderCellAlignment = StringAlignment.Near;
//footer for our bill
printer.Footer = "Discount:- " + txtDiscount.Text + "% \r\n" + "GST:- " + txtGST.Text + "%\r\n" + "Grand Total:- " + txtGrandTotal.Text + "\r\n\r\n" + "Thank You For Shopping With Us";
printer.FooterSpacing = 15;
printer.PrintDataGridView(dgvAddedProducts);
MessageBox.Show("Transaction Completed Successfully");
//clear the data grid view and clear all the textboxes
dgvAddedProducts.DataSource = null;
dgvAddedProducts.Rows.Clear();
txtDandCSearch.Text = "";
txtDandCName.Text = "";
txtDandCEmail.Text = "";
txtDandCContact.Text = "";
txtDandCAddress.Text = "";
txtPDSearch.Text = "";
txtPDName.Text = "";
txtPDInventory.Text = "0";
txtPDRate.Text = "0";
txtPDQty.Text = "0";
txtSubTotal.Text = "0";
txtDiscount.Text = "0";
txtGST.Text = "0";
txtGrandTotal.Text = "0";
txtPaidAmount.Text = "0";
txtReturnAmount.Text = "0";
}
else
{
//Transaction Failed
MessageBox.Show("Transaction Failed!");
}
}
}
