public DeaCusBLL SearchDealerCustomerForTransaction(string keyword)
{
SqlConnection conn = new SqlConnection(myconnstrng);
DeaCusBLL dc = new DeaCusBLL();
DataTable dt = new DataTable();
try
{
String sql = "SELECT name,email,contact,address FROM tbl_delr_cust WHERE id LIKE '%" + keyword + "%' OR name LIKE '%" + keyword + "%' ";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
conn.Open();
adapter.Fill(dt);
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 DeaCusBLL GetDeaCusIDFromName(string Name)
{
DeaCusBLL dc = new DeaCusBLL();
SqlConnection conn = new SqlConnection(myconnstrng);
DataTable dt = new DataTable();
try
{
String sql = "SELECT id FROM tbl_delr_cust WHERE name = '" + Name + "'";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
conn.Open();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
dc.id = int.Parse(dt.Rows[0]["id"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(dc);
}
public bool Delete(DeaCusBLL d)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
String sql = "DELETE FROM tbl_delr_cust WHERE id=@id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@id", d.id);
conn.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Update(DeaCusBLL c)
{
//sql Connection for database connection
SqlConnection conn = new SqlConnection(myconnstrng);
//Create a bool variable and set the default to false
bool isSucccess = false;
try
{
string sql = "UPDATE dbo.customers SET type=@type,name=@name,emailAddress=@emailAddress,contact=@contact,address=@address,addedDate=@addedDate, addedBy=@addedBy WHERE id = @id";
//SqlCommand to pass values
SqlCommand cmd = new SqlCommand(sql, conn);
// Pass the values through params
cmd.Parameters.AddWithValue("@type", c.type);
cmd.Parameters.AddWithValue("@name", c.name);
cmd.Parameters.AddWithValue("@emailAddress", c.emailAddress);
cmd.Parameters.AddWithValue("@contact", c.contact);
cmd.Parameters.AddWithValue("@address", c.address);
cmd.Parameters.AddWithValue("@addedDate", c.addedDate);
cmd.Parameters.AddWithValue("@addedBy", c.addedBy);
cmd.Parameters.AddWithValue("@id", c.id);
conn.Open();
int rows = cmd.ExecuteNonQuery();
//If the query is executed Successfully then the value to rows will be greater than zero else it will be less than zero.
if (rows > 0)
{
//Query executed successfully
isSucccess = true;
}
else
{
isSucccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSucccess);
}
private void txtPSDeaCusSearch_TextChanged(object sender, EventArgs e)
{
string keyword = txtPSDeaCusSearch.Text;
if (keyword == "")
{
txtPSDeaCusName.Text = "";
txtPSDeaCusEmail.Text = "";
txtPSDeaCusContact.Text = "";
txtPSDeaCusAddress.Text = "";
return;
}
DeaCusBLL dc = dcdal.SearchDealerCustomerForTransaction(keyword);
txtPSDeaCusName.Text = dc.name;
txtPSDeaCusEmail.Text = dc.email;
txtPSDeaCusContact.Text = dc.contact;
txtPSDeaCusAddress.Text = dc.address;
}
public DeaCusBLL SearchDealerCustomerForTransaction(string keyword)
{
//Create an object for DeaCust Class
DeaCusBLL dc = new DeaCusBLL();
//Method to connect to database
SqlConnection conn = new SqlConnection(myconnstrng);
//This is used to hold data from the database
DataTable dt = new DataTable();
try
{
//Query to get the data from the database
String sql = "SELECT name, emailAddress, contact, address FROM dbo.customers WHERE id LIKE '%" + keyword + "%' OR name LIKE '%" + keyword + "%'";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//Open database connection
conn.Open();
//fill data in the datatable
adapter.Fill(dt);
// save available data in customerBLL
if (dt.Rows.Count > 0)
{
dc.name = dt.Rows[0]["name"].ToString();
dc.emailAddress = dt.Rows[0]["emailAddress"].ToString();
dc.contact = dt.Rows[0]["contact"].ToString();
dc.address = dt.Rows[0]["address"].ToString();
}
}
catch (Exception ex)
{
//Show error message for any messages that might occur
MessageBox.Show(ex.Message);
}
finally
{
//Close Connection
conn.Close();
}
return(dc);
}
private void TbSearch_TextChanged(object sender, EventArgs e)
{
//get the keyword fro the textbox
string keywords = tbSearch.Text;
if (keywords == "")
{
//Clear all the textBoxes
Clear();
return;
}
DeaCusBLL dc = dcDAL.SearchDealerCustomerForTransaction(keywords);
//Set the value the textboxes
tbName.Text = dc.name;
tbEmail.Text = dc.emailAddress;
tbContact.Text = dc.contact;
tbAddress.Text = dc.address;
}
public bool Insert(DeaCusBLL c)
{
bool isSucccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
string sql = "INSERT into dbo.customers(type,name,emailAddress,contact,address,addedDate,addedBy) Values(@type,@name,@emailAddress,@contact,@address,@addedDate,@addedBy)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@type", c.type);
cmd.Parameters.AddWithValue("@name", c.name);
cmd.Parameters.AddWithValue("@emailAddress", c.emailAddress);
cmd.Parameters.AddWithValue("@contact", c.contact);
cmd.Parameters.AddWithValue("@address", c.address);
cmd.Parameters.AddWithValue("@addedDate", c.addedDate);
cmd.Parameters.AddWithValue("@addedBy", c.addedBy);
conn.Open();
int rows = cmd.ExecuteNonQuery();
//If the query is executed Successfully then the value to rows will be greater than zero else it will be less than zero.
if (rows > 0)
{
isSucccess = true;
}
else
{
isSucccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSucccess);
}
public bool Update(DeaCusBLL d)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
String sql = "UPDATE tbl_delr_cust SET type=@type, name=@name,email=@email,contact=@contact,address=@address,added_date=@added_date,added_by=@added_by WHERE id=@id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@type", d.type);
cmd.Parameters.AddWithValue("@name", d.name);
cmd.Parameters.AddWithValue("@email", d.email);
cmd.Parameters.AddWithValue("@contact", d.contact);
cmd.Parameters.AddWithValue("@address", d.address);
cmd.Parameters.AddWithValue("@added_date", d.added_date);
cmd.Parameters.AddWithValue("@added_by", d.added_by);
cmd.Parameters.AddWithValue("@id", d.id);
conn.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Insert(DeaCusBLL d)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
String sql = "INSERT INTO tbl_delr_cust (type,name,email,contact,address,added_date,added_by) VALUES (@type,@name,@email,@contact,@address,@added_date,@added_by)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@type", d.type);
cmd.Parameters.AddWithValue("@name", d.name);
cmd.Parameters.AddWithValue("@email", d.email);
cmd.Parameters.AddWithValue("@contact", d.contact);
cmd.Parameters.AddWithValue("@address", d.address);
cmd.Parameters.AddWithValue("@added_date", d.added_date);
cmd.Parameters.AddWithValue("@added_by", d.added_by);
conn.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public DeaCusBLL GetDeaCusIdFromName(string name)
{
//Create an object of Decust BLL
DeaCusBLL dc = new DeaCusBLL();
//Method to connect to database
SqlConnection conn = new SqlConnection(myconnstrng);
//This is used to hold data from the database
DataTable dt = new DataTable();
try
{
//Get Id based on name
string sql = "SELECT id FROM dbo.customers WHERE name ='" + name + "'";
//Create the data adapter to execute the query
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
conn.Open();
//Pass values to datatable
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
//pass the value from dt to DeaCusBLL dc
dc.id = int.Parse(dt.Rows[0]["id"].ToString());
;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(dc);
}
private void btnSaveAll_Click(object sender, EventArgs e)
{
TransactionBLL transaction = new TransactionBLL();
transaction.type = lblTopPurcSale.Text;
string deaCusName = txtPSDeaCusName.Text;
DeaCusBLL dc = dcdal.GetDeaCusIDFromName(deaCusName);
transaction.delr_cust_id = dc.id;
transaction.grandTotal = Math.Round(decimal.Parse(txtPSCDGrandTotal.Text), 2);
transaction.transaction_date = DateTime.Now;
transaction.tax = decimal.Parse(txtPSCDVat.Text);
transaction.discount = decimal.Parse(txtPSCDDiscount.Text);
string loggeduser = frmLogin.loggedIn;
UserBLL user = udal.GetIdFromUsername(loggeduser);
transaction.added_by = user.id;
transaction.transactionDetails = transactionDT;
bool Success = false;
using (TransactionScope scope = new TransactionScope())
{
int transactionID = -1;
bool X = tdal.Insert_Transaction(transaction, out transactionID);
for (int i = 0; i < transactionDT.Rows.Count; i++)
{
TransactionDetailsBLL td = new TransactionDetailsBLL();
String ProductName = transactionDT.Rows[i][0].ToString();
ProductsBLL p = pdal.GetProductIDFromName(ProductName);
td.product_id = p.id;
td.rate = decimal.Parse(transactionDT.Rows[i][1].ToString());
td.qty = decimal.Parse(transactionDT.Rows[i][2].ToString());
td.total = Math.Round(decimal.Parse(transactionDT.Rows[i][3].ToString()), 2);
td.delr_cust_id = dc.id;
td.added_date = DateTime.Now;
td.added_by = user.id;
string transactionType = lblTopPurcSale.Text;
bool Y = false;
if (transactionType == "PURCHASE")
{
Y = pdal.IncreaseProduct(td.product_id, td.qty);
}
else if (transactionType == "SALES")
{
Y = pdal.DecreaseProduct(td.product_id, td.qty);
}
bool Z = tdDal.Insert_TransactionDetails(td);
Success = X && Y && Z;
}
if (Success == true)
{
scope.Complete();
// CODE TO PRINT BILL
DGVPrinter printer = new DGVPrinter();
printer.Title = "\r\n\r\nEVERYTHING PVT. LTD.\r\n";
printer.SubTitle = "\r\n MAIN ROAD NEAR RAILWAY STATION ,P.O:GHATSILA,\r\nDIST:EAST SINGHBHUM ,JHARKHAND\r\n Mob: 9583340426 \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:" + txtPSCDDiscount.Text + "%\r\n" + "TAX:" + txtPSCDVat.Text + "%\r\n" + "Grand-Total:RS" + txtPSCDGrandTotal.Text + "\r\n\r\n" + "THANK YOU FOR BUSINESS WITH US";
printer.FooterSpacing = 15;
printer.PrintDataGridView(dgvPSAddedProduct);
//----------------------------------------------------------------------------------------------------------------------------
MessageBox.Show("Transaction Successfully Done.");
dgvPSAddedProduct.DataSource = null;
dgvPSAddedProduct.Rows.Clear();
txtPSDeaCusSearch.Text = "";
txtPSDeaCusName.Text = "";
txtPSDeaCusEmail.Text = "";
txtPSDeaCusContact.Text = "";
txtPSDeaCusAddress.Text = "";
txtPSPDSearch.Text = "";
txtPSPDName.Text = "";
txtPSPDInventory.Text = "0";
txtPSPDRate.Text = "0";
txtPSPDQty.Text = "";
txtPSCDSubtotal.Text = "0";
txtPSCDDiscount.Text = "0";
txtPSCDVat.Text = "0";
txtPSCDGrandTotal.Text = "0";
txtPSCDpaidamount.Text = "0";
txtPSCDRetAmut.Text = "0";
}
else
{
MessageBox.Show("Failed Transaction,Retry");
}
}
}
private void BtnCalcSave_Click(object sender, EventArgs e)
{
try
{
//Get the values from PurchaseSales Form
transactionsBLL transaction = new transactionsBLL();
transaction.type = lblPurchasesAndSales.Text;
//Get the id and name of dealer or customer
string deaCustName = tbName.Text;
DeaCusBLL dc = dcDAL.GetDeaCusIdFromName(deaCustName);
transaction.deal_cust_id = dc.id;
transaction.grandTotal = Math.Round(decimal.Parse(tbGrandTotal.Text), 2);
transaction.transactionDate = DateTime.Now;
transaction.tax = decimal.Parse(tbVat.Text);
transaction.discount = decimal.Parse(tbDiscount.Text);
//get the username of logged in user
string username = Login.loggedIn;
userBLL u = uDAL.GetIDFromUsername(username);
transaction.addedBy = u.id;
transaction.transactionDetails = transactionDT;
//create a boolean and set value
bool success = false;
//Insert transaction in table
using (TransactionScope scope = new TransactionScope())
{
int transactionID = -1;
//create a bool value and insert transaction
bool w = tDAL.Insert_Transaction(transaction, out transactionID);
for (int i = 0; i < transactionDT.Rows.Count; i++)
{
//Get all the details of the subscription
transactionDetailBLL transactionDetail = new transactionDetailBLL();
//convert subscription Name to id
string name = transactionDT.Rows[i][0].ToString();
subscriptionsBLL p = pDAL.GetSubscriptionIDFromName(name);
transactionDetail.subscriptionid = p.id;
transactionDetail.price = decimal.Parse(transactionDT.Rows[i][1].ToString());
transactionDetail.quantity = decimal.Parse(transactionDT.Rows[i][2].ToString());
transactionDetail.total = Math.Round(decimal.Parse(transactionDT.Rows[i][3].ToString()), 2);
transactionDetail.dealer_cust_id = dc.id;
transactionDetail.addedDate = DateTime.Now;
transactionDetail.addedBy = u.id;
//change quantity based on sales or purchases
string transactionType = lblPurchasesAndSales.Text;
//Create a bool and check for type of transaction
bool x = false;
if (transactionType == "Purchase")
{
x = pDAL.IncreaseProduct(transactionDetail.subscriptionid, transactionDetail.quantity);
}
else if (transactionType == "Sales")
{
//Decrease the Product Quantity
x = pDAL.DecreaseProduct(transactionDetail.subscriptionid, transactionDetail.quantity);
}
//Insert transaction Details
bool y = tdDAL.Insert_TransactionDetail(transactionDetail);
success = w && x && y;
}
if (success == true)
{
scope.Complete();
//Print Bill
DGVPrinter printer = new DGVPrinter();
printer.Title = "\r\n\r\n\r\n Flava Solutions Gymn MGMT. LTD. \r\n\r\n";
printer.SubTitle = "Angels, Spanish Town \r\n Phone: 876-619-1097 - 99 \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: " + tbDiscount.Text + "% \r\n" + "VAT: " + tbVat.Text + "% \r\n" + "Grand Total: " + tbGrandTotal.Text + "\r\n\r\n" + "Thank you for doing business with us.";
printer.FooterSpacing = 15;
printer.PrintDataGridView(dgvPurchases);
MessageBox.Show("Transaction Completed Successfully");
//Clear datagrid view
dgvPurchases.DataSource = null;
dgvPurchases.Rows.Clear();
Clear1();
}
else
{
//transaction Failed
MessageBox.Show("Transaction Failed");
}
}
}
finally
{
}
}