public customerBLL getCustomerIdFromName(string Name)
{
customerBLL c = new customerBLL();
SqlConnection con = new SqlConnection(myconnstrng);
DataTable dt = new DataTable();
try
{
string sql = "SELECT id FROM Supplier_Master WHERE name='" + Name + "'";
SqlDataAdapter adapter = new SqlDataAdapter(sql, con);
con.Open();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
c.id = int.Parse(dt.Rows[0]["id"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
return(c);
}
public bool Delete(customerBLL c)
{
bool isSuccess = false;
SqlConnection con = new SqlConnection(myconnstrng);
try
{
string sql = "DELETE FROM Cust_Master WHERE Cust_Id =@id";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@id", c.id);
con.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
return(isSuccess);
}
public bool Insert(customerBLL customer)
{
bool isSuccess = false;
SqlConnection connection = new SqlConnection(myconnectingstring);
try
{
string sql = "INSERT INTO tbl_customers (name, email, contact, address, added_Date, added_by) VALUES " +
"(@name, @email, @contact, @address, @added_date, @added_by)";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@name", customer.name);
command.Parameters.AddWithValue("@email", customer.email);
command.Parameters.AddWithValue("@contact", customer.contact);
command.Parameters.AddWithValue("@address", customer.address);
command.Parameters.AddWithValue("@added_date", customer.added_date);
command.Parameters.AddWithValue("@added_by", customer.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);
}
protected void btulogin_Click(object sender, EventArgs e)
{
customer cu = new customer();
cu.Account = ACC.Text;
cu.Pssword = password.Text;
customer culist = new customerBLL().selcustomer(cu);
if (culist.ID < 0)
{
tip.Text = "账号或密码错误，请重新输入！";
}
else
{
Session["customer"] = culist;
Response.Redirect("index.aspx");
}
}
public customerBLL SearchCustomerForTransaction(string keywords)
{
//Create an object for customerBLL class
customerBLL customerBLL = new customerBLL();
// 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, address, contact from tbl_customers where name LIKE '%" + keywords + "%' OR contact 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)
{
customerBLL.name = dataTable.Rows[0]["name"].ToString();
customerBLL.email = dataTable.Rows[0]["addresss"].ToString();
customerBLL.contact = dataTable.Rows[0]["contact"].ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
return(customerBLL);
}
public bool Update(customerBLL customer)
{
bool isSuccess = false;
SqlConnection connection = new SqlConnection(myconnectingstring);
try
{
string sql = "UPDATE tbl_customers SET name = @name, email = @email, contact = @contact,address = @address, added_date = @added_date, added_by = @added_by where id = @id";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@name", customer.name);
command.Parameters.AddWithValue("@email", customer.email);
command.Parameters.AddWithValue("@contact", customer.contact);
command.Parameters.AddWithValue("@address", customer.address);
command.Parameters.AddWithValue("@added_date", customer.added_date);
command.Parameters.AddWithValue("@added_by", customer.added_by);
command.Parameters.AddWithValue("@id", customer.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);
}
private void txtSearch_TextChanged(object sender, EventArgs e)
{
string keyword = txtSearch.Text;
if (keyword == "")
{
txtName.Text = "";
txtAddress.Text = "";
txtContact.Text = "";
txtEmail.Text = "";
return;
}
customerBLL cb = cDAL.searchcustomerforsales(keyword);
txtName.Text = cb.name;
txtContact.Text = cb.contact;
txtEmail.Text = cb.email;
txtAddress.Text = cb.address;
}
protected void btusave_Click(object sender, EventArgs e)
{
customer cu = new customer();
cu.ID = ((customer)Session["customer"]).ID;
cu.Name = tetName.Text;
cu.Phone = txtPhone.Text;
cu.Email = txtEmail.Text;
cu.Adress = txtAdress.Text;
if (new customerBLL().updatecustomer(cu) > 0)
{
Session["customer"] = new customerBLL().query(cu);
Label divMaster = (Label)this.Master.FindControl("Lablogin");
divMaster.Text = ((customer)Session["customer"]).Name;
Response.Write("<script>alert('修改成功!');</script>");
}
else
{
Response.Write("<script>alert('修改失败!');</script>");
}
}
private void txtBoxSearch_TextChanged(object sender, EventArgs e)
{
//Get the keywords from the text box
string keywords = txtBoxCustomerSearch.Text;
if (keywords == "")
{
//Clear all the textboxes
txtBoxCustomerName.Text = "";
txtBoxCustomerAddress.Text = "";
txtBoxCustomerContact.Text = "";
}
else
{
customerBLL customerBLL = customerDAL.SearchCustomerForTransaction(keywords);
txtBoxCustomerName.Text = customerBLL.name;
txtBoxCustomerAddress.Text = customerBLL.address;
txtBoxCustomerContact.Text = customerBLL.contact;
}
}
private void textSearch_TextChanged(object sender, EventArgs e)
{
//get search keyword from search text box
string keyword = textSearch.Text;
if (keyword == "")//clear all textboex
{
textCust_Name.Text = "";
textAddress.Text = "";
textContact.Text = "";
textEmail.Text = "";
return;
}
customerBLL cBLL = cDAL.searchcustomerforsales(keyword);
textCust_Name.Text = cBLL.name;
textContact.Text = cBLL.contact;
textEmail.Text = cBLL.email;
textAddress.Text = cBLL.address;
}
public bool Update(customerBLL c)
{
bool isSuccess = false;
SqlConnection con = new SqlConnection(myconnstrng);
try
{
String sql = "UPDATE Cust_Master SET Cust_Name=@name, Cust_Contact=@contact, Cust_Email=@email, Cust_Address=@address WHERE Cust_Id = @id";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@name", c.name);
cmd.Parameters.AddWithValue("@contact", c.contact);
cmd.Parameters.AddWithValue("@email", c.email);
cmd.Parameters.AddWithValue("@address", c.address);
cmd.Parameters.AddWithValue("@id", c.id);
con.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
return(isSuccess);
}
public bool Insert(customerBLL c)
{
bool isSuccess = false;
SqlConnection con = new SqlConnection(myconnstrng);
try
{
String sql = "INSERT INTO Cust_Master (Cust_Name, Cust_Contact, Cust_Email, Cust_Address) VALUES(@name, @contact, @email, @address)";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@name", c.name);
cmd.Parameters.AddWithValue("@contact", c.contact);
cmd.Parameters.AddWithValue("@email", c.email);
cmd.Parameters.AddWithValue("@address", c.address);
con.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
return(isSuccess);
}
public bool Delete(customerBLL customer)
{
bool isSuccess = false;
SqlConnection connection = new SqlConnection(myconnectingstring);
try
{
string sql = "DELETE from tbl_customers where id = @id";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@id", customer.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 customerBLL searchcustomerforsales(string keyword)
{
customerBLL c = new customerBLL();
SqlConnection con = new SqlConnection(myconnstrng);
DataTable dt = new DataTable();
//
try
{
string sql = "SELECT Cust_Name, Cust_Contact, Cust_Email,Cust_Address from Cust_Master WHERE Cust_Id LIKE '%" + keyword + "%' OR Cust_Name LIKE '%" + keyword + "%'";
SqlDataAdapter adapter = new SqlDataAdapter(sql, con);
con.Open();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
c.name = dt.Rows[0]["Cust_Name"].ToString();
c.contact = dt.Rows[0]["Cust_Contact"].ToString();
c.email = dt.Rows[0]["Cust_Email"].ToString();
c.address = dt.Rows[0]["Cust_Address"].ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
}
return(c);
}
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");
}
}
public void save()
{
string sname = textCust_Name.Text;
if (comboTransactionType.Text != "")
{
if (sname != "")
{
if (dgvAddedProducts.Rows.Count != 0)
{
customerBLL c = cDAL.getCustomerIdFromName(sname);
decimal subTotal, totalDiscount, totalSgst, totalCgst, totalIgst, grandTotal;
string type = comboTransactionType.Text;
decimal.TryParse(textSubTotal.Text, out subTotal);
decimal.TryParse(textSubDiscount.Text, out totalDiscount);
decimal.TryParse(textSgst.Text, out totalSgst);
decimal.TryParse(textCgst.Text, out totalCgst);
decimal.TryParse(textIgst.Text, out totalIgst);
decimal.TryParse(textGrandTotal.Text, out grandTotal);
EstimateBLL.Transaction_Type = type;
EstimateBLL.Cust_ID = c.Cust_ID;
EstimateBLL.Sub_Total = subTotal;
EstimateBLL.TDiscount = totalDiscount;
EstimateBLL.TSGST = totalSgst;
EstimateBLL.TCGST = totalCgst;
EstimateBLL.TIGST = totalIgst;
EstimateBLL.Grand_Total = grandTotal;
EstimateBLL.SalesDetails = salesDT;
bool isSuccess = false;
// using (TransactionScope scope = new TransactionScope())
//alredy declared on top
// int Invoice_No = -1;
bool b = EstimateDAL.insertChallan(EstimateBLL, out Invoice_No);
for (int i = 0; i < salesDT.Rows.Count; i++)
{
EstimateDetailsBLL cdBLL = new EstimateDetailsBLL();
stockBLL stockBLL = new stockBLL();
string productName = salesDT.Rows[i][1].ToString();
ProductMasterBLL p = ProductMasterDAL.GetProductIDFromName(productName);
cdBLL.Product_ID = p.Product_ID;
cdBLL.Invoice_No = Invoice_No;
cdBLL.Cust_ID = c.Cust_ID;
cdBLL.Product_Name = salesDT.Rows[i][1].ToString();
cdBLL.Unit = salesDT.Rows[i][2].ToString();
cdBLL.Qty = Math.Round(decimal.Parse(salesDT.Rows[i][3].ToString()), 2);
cdBLL.Rate = Math.Round(decimal.Parse(salesDT.Rows[i][4].ToString()), 2);
cdBLL.Discount_Per = Math.Round(decimal.Parse(salesDT.Rows[i][6].ToString()), 2);
cdBLL.GST_Type = salesDT.Rows[i][7].ToString();
cdBLL.GST_Per = Math.Round(decimal.Parse(salesDT.Rows[i][8].ToString()), 2);
cdBLL.Total = Math.Round(decimal.Parse(salesDT.Rows[i][9].ToString()), 2);
int Product_id = p.Product_ID;
stockBLL.Product_Id = Product_id;
stockBLL.Quantity = Math.Round(decimal.Parse(salesDT.Rows[i][3].ToString()), 2);
stockBLL.Unit = salesDT.Rows[i][2].ToString();
bool y = EstimateDetailsDAL.insertchallandetails(cdBLL);
isSuccess = b && y;
isSuccess = true;
}
isSuccess = b;
if (isSuccess == true)
{
//scope.Complete();
MessageBox.Show("Transaction Completed");
//clear();
}
else
{
MessageBox.Show("Transaction Failed");
}
}
else
{
MessageBox.Show("Please Add product Details");
}
}
else
{
MessageBox.Show("Please Select Customer Details");
}
}
else
{
MessageBox.Show("Please Select Purchase Type GST OR NOGST");
}
}
