public bool insert(Manage_Productsbll u)
{
//Create a boolean variable and set its value to false and return it
bool issucess = false;
//MEthod to connect Database
connclass c = new connclass();
SqlConnection conn = new SqlConnection(c.connection);
try
{
//SQL Query to insert Data in DAtabase
string query = "Insert into tbl_stock(product_name,colour_code,supplier,catagory,purchase_price,retail_price,type,quantity,added_by,added_date)Values(@product_name,@colour_code,@supplier,@catagory,@purchase_price,@retail_price,@type,@quantity,@added_by,@added_date)";
//For Executing Command
SqlCommand cmd = new SqlCommand(query, conn);
//Passing Values to the Variables
cmd.Parameters.AddWithValue("@product_name", u.product_name);
cmd.Parameters.AddWithValue("@colour_code", u.colour_code);
cmd.Parameters.AddWithValue("@supplier", u.supplier);
cmd.Parameters.AddWithValue("@catagory", u.catagory);
cmd.Parameters.AddWithValue("@purchase_price", u.purchase_price);
cmd.Parameters.AddWithValue("@retail_price", u.retail_price);
cmd.Parameters.AddWithValue("@type", u.type);
cmd.Parameters.AddWithValue("@quantity", u.quantity);
cmd.Parameters.AddWithValue("@added_by", u.added_by);
cmd.Parameters.AddWithValue("@added_date", u.added_date);
//Database Connection Open
conn.Open();
//To execute non query
int row = cmd.ExecuteNonQuery();
//If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
if (row > 0)
{
//Query Sucessfull
issucess = true;
}
else
{
//Query Failed
issucess = false;
}
}
catch (Exception ex)
{
//Throw Message if any error occurs
MessageBox.Show(ex.Message);
}
finally
{
//Closing Connection
conn.Close();
}
return(issucess);
}
public bool exist(Manage_Productsbll u)
{
//Create a boolean variable and set its value to false and return it
bool issuccess = false;
//MEthod to connect Database
connclass c = new connclass();
SqlConnection conn = new SqlConnection(c.connection);
try
{
//SQL Query to selecte Data from DAtabase
string query = "select * from tbl_stock where product_name=@product_name AND supplier=@supplier AND catagory=@catagory AND colour_code=@colour_code AND type=@type";
//For Executing Command
SqlCommand cmd = new SqlCommand(query, conn);
//Passing Values to the Variables
cmd.Parameters.AddWithValue("@type", u.type);
cmd.Parameters.AddWithValue("@product_name", u.product_name);
cmd.Parameters.AddWithValue("@supplier", u.supplier);
cmd.Parameters.AddWithValue("@catagory", u.catagory);
cmd.Parameters.AddWithValue("@colour_code", u.colour_code);
//Getting DAta from dAtabase
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//TO hold the data from database
DataTable dt = new DataTable();
//Database Connection Open
conn.Open();
//Fill Data in our DataTable
adapter.Fill(dt);
//Checking The rows in DataTable
if (dt.Rows.Count > 0)
{
//Data Exist
issuccess = true;
}
else
{
//Data not exist
issuccess = false;
}
}
catch (Exception ex)
{
//Throw Message if any error occurs
MessageBox.Show(ex.Message);
}
finally
{
//Closing Connection
conn.Close();
}
return(issuccess);
}
public bool delete(Manage_Productsbll u)
{
//Create a boolean variable and set its value to false and return it
bool issucess = false;
//MEthod to connect Database
connclass c = new connclass();
SqlConnection conn = new SqlConnection(c.connection);
try
{
//SQL Query to delete Data in DAtabase
string query = "DELETE FROM tbl_stock WHERE id=@id";
//For Executing Command
SqlCommand cmd = new SqlCommand(query, conn);
//Passing Values to the Variables
cmd.Parameters.AddWithValue("@id", u.id);
//Database Connection Open
conn.Open();
//To execute non query
int row = cmd.ExecuteNonQuery();
//If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
if (row > 0)
{
//Query Sucessfull
issucess = true;
}
else
{
//Query Failed
issucess = false;
}
}
catch (Exception ex)
{
//Throw Message if any error occurs
MessageBox.Show(ex.Message);
}
finally
{
//Closing Connection
conn.Close();
}
return(issucess);
}
public Manage_Productsbll GetProductpriceFromId(string id)
{
//First Create an Object of BLL and REturn it
Manage_Productsbll p = new Manage_Productsbll();
//MEthod to connect Database
connclass c = new connclass();
SqlConnection conn = new SqlConnection(c.connection);
//Data TAble to Holdthe data temporarily
DataTable dt = new DataTable();
try
{
//SQL Query to Get id based on Name
string sql = "SELECT purchase_price FROM tbl_stock WHERE id='" + id + "'";
//Create the SQL Data Adapter to Execute the Query
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
conn.Open();
//Passing the VAlue from Adapter to DAtatable
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
//Pass the value from dt to variable
p.purchase_price = dt.Rows[0]["purchase_price"].ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(p);
}
public Manage_Productsbll GetProductIDFromName(string ProductName, string type, string code)
{
//First Create an Object of BLL and REturn it
Manage_Productsbll p = new Manage_Productsbll();
//MEthod to connect Database
connclass c = new connclass();
SqlConnection conn = new SqlConnection(c.connection);
//Data TAble to Holdthe data temporarily
DataTable dt = new DataTable();
try
{
//SQL Query to Get id based on Name
string sql = "SELECT id FROM tbl_stock WHERE product_name='" + ProductName + "' AND type = '" + type + "' AND colour_code = '" + code + "'";
//Create the SQL Data Adapter to Execute the Query
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
conn.Open();
//Passing the CAlue from Adapter to DAtatable
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
//Pass the value from dt to DeaCustBLL dc
p.id = int.Parse(dt.Rows[0]["id"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(p);
}
private void btnsave_Click(object sender, EventArgs e)
{
if (Validation1())
{
//printPreviewDialog1.Document = printDocument1;
//printPreviewDialog1.ShowDialog();
//Gettting Data FRom UI
inbll.inv_no = Convert.ToInt32(lblinvoiceno.Text);
inbll.customer_name = cmbcustomer.Text;
inbll.total_payable = txtsubtotal.Text;
inbll.paid_amount = txtpaidamount.Text;
inbll.discount = txtdiscount.Text;
inbll.due_amount = txtdueamount.Text;
inbll.change_amount = txtchangeamount.Text;
inbll.sales_date = DateTime.Now;
//Getting Username of the logged in user
string loggedUser = Login.loggedIn;
usersbll usr = user.GetIDFromUsername(loggedUser);
inbll.added_by = usr.id;
//Inserting Data into DAtabase
bool w = indal.insert(inbll);
bool sucess = false;
for (int i = 0; i < table.Rows.Count; i++)
{
bool x = false;
//Get the Product name and convert it to id
string ProductName = table.Rows[i][0].ToString();
string type1 = table.Rows[i][2].ToString();
string ccode = table.Rows[i][1].ToString();
Manage_Productsbll p = dal.GetProductIDFromName(ProductName, type1, ccode);
prbll.product_id = p.id;
prbll.product_name = table.Rows[i][0].ToString();
prbll.type = table.Rows[i][2].ToString();
prbll.price = table.Rows[i][3].ToString();
prbll.quantity = table.Rows[i][4].ToString();
prbll.code = Convert.ToInt32(table.Rows[i][1]);
prbll.discount = txtdiscount.Text;
//prbll.total = table.Rows[i][3].ToString();
//Getting total value by calculating discount
t1 = Convert.ToDouble(txtdiscount.Text);
t2 = Convert.ToDouble(table.Rows[i][5]);
prbll.total = (t2 - (t2 * t1) / 100).ToString();
//Method to get purchase price an multiply it with quantity
Manage_Productsbll pr = dal.GetProductpriceFromId(p.id.ToString());
purchaseprice = Convert.ToDouble(pr.purchase_price);
prbll.purchase_price = ((Convert.ToDouble(table.Rows[i][4])) * purchaseprice).ToString();
prbll.inv_no = lblinvoiceno.Text;
prbll.added_by = usr.id;
prbll.added_date = DateTime.Now;
quantity1 = Convert.ToDecimal(prbll.quantity);
// Decreasing product quantity in DAtabase
bool y = false;
y = dal.DecreaseProduct(prbll.product_id, quantity1);
// Inserting Data into DAtabase
x = prdal.insert(prbll);
sucess = w && x && y;
}
if (sucess == true)
{
MessageBox.Show("Transaction Completed Sucessfully");
//Celar the Data Grid View and Clear all the TExtboxes
dgvinvoice.DataSource = null;
dgvinvoice.Rows.Clear();
txttotal.Text = "";
txtdiscount.Text = "";
txtsubtotal.Text = "";
txtpaidamount.Text = "";
txtdueamount.Text = "";
txtchangeamount.Text = "";
cmbcustomer.Text = "";
txtcomment.Text = "0";
//To refresh stock in Data grid view
DataTable dt = dal.select();
dgvstock.DataSource = dt;
ToGetInvoiceID();
}
else
{
//Transaction Failed
MessageBox.Show("Transaction Failed");
}
}
}
private void btncomplete_Click(object sender, EventArgs e)
{
if (Validation1())
{
//printPreviewDialog1.Document = printDocument1;
//printPreviewDialog1.ShowDialog();
//Gettting Data FRom UI
inbll.inv_no = Convert.ToInt32(lblinvoiceno.Text);
inbll.customer_name = cmbcustomer.Text;
inbll.total_payable = txtsubtotal.Text;
inbll.paid_amount = txtpaidamount.Text;
inbll.discount = txtdiscount.Text;
inbll.due_amount = txtdueamount.Text;
inbll.change_amount = txtchangeamount.Text;
inbll.sales_date = DateTime.Now;
//Getting Username of the logged in user
string loggedUser = Login.loggedIn;
usersbll usr = user.GetIDFromUsername(loggedUser);
inbll.added_by = usr.id;
//Inserting Data into DAtabase
bool w = indal.insert(inbll);
bool sucess = false;
for (int i = 0; i < table.Rows.Count; i++)
{
bool x = false;
//Get the Product name and convert it to id
string ProductName = table.Rows[i][0].ToString();
string type1 = table.Rows[i][2].ToString();
string ccode = table.Rows[i][1].ToString();
Manage_Productsbll p = dal.GetProductIDFromName(ProductName, type1, ccode);
prbll.product_id = p.id;
prbll.product_name = table.Rows[i][0].ToString();
prbll.type = table.Rows[i][2].ToString();
prbll.price = table.Rows[i][3].ToString();
prbll.quantity = table.Rows[i][4].ToString();
prbll.code = Convert.ToInt32(table.Rows[i][1]);
prbll.discount = txtdiscount.Text;
//prbll.total = table.Rows[i][3].ToString();
//Getting total value by calculating discount
t1 = Convert.ToDouble(txtdiscount.Text);
t2 = Convert.ToDouble(table.Rows[i][5]);
prbll.total = (t2 - (t2 * t1) / 100).ToString();
//Method to get purchase price an multiply it with quantity
Manage_Productsbll pr = dal.GetProductpriceFromId(p.id.ToString());
purchaseprice = Convert.ToDouble(pr.purchase_price);
prbll.purchase_price = ((Convert.ToDouble(table.Rows[i][4])) * purchaseprice).ToString();
prbll.inv_no = lblinvoiceno.Text;
prbll.added_by = usr.id;
prbll.added_date = DateTime.Now;
quantity1 = Convert.ToDecimal(prbll.quantity);
// Decreasing product quantity in DAtabase
bool y = false;
y = dal.DecreaseProduct(prbll.product_id, quantity1);
// Inserting Data into DAtabase
x = prdal.insert(prbll);
sucess = w && x && y;
}
if (sucess == true)
{
//code to print Bill
DGVPrinter printer = new DGVPrinter();
printer.Title = "\r \r Time Paint & Hardware Store";
printer.SubTitle = "Khokhar Plaza, Near Alied Bank, Main Behria Enclave Road \r \n Phone: 031659007044 \r \r \r \r \r \r Invoice #:" + lblinvoiceno.Text + "\r";
printer.SubTitleFormatFlags = StringFormatFlags.LineLimit | StringFormatFlags.NoClip;
printer.PageNumbers = true;
printer.PageNumberInHeader = false;
printer.PorportionalColumns = true;
printer.HeaderCellAlignment = StringAlignment.Near;
printer.Footer = "Grand Total: " + txtsubtotal.Text + " \r \r \r \r \r \r \r \r \r \r \r \r" + "Total Paid:" + txtpaidamount.Text + " \r \n" + "Thank You, for doing business with us. \n";
//printer.PageText = "Developed By: \r \r \r Engr. Azhar Mir \r \r \r \r \r \r \r \r \r Mailing Contact: \r \r \r [email protected] \n";
printer.FooterSpacing = 15;
printer.PrintDataGridView(dgvinvoice);
MessageBox.Show("Transaction Completed Sucessfully");
//Celar the Data Grid View and Clear all the TExtboxes
dgvinvoice.DataSource = null;
dgvinvoice.Rows.Clear();
txttotal.Text = "";
txtdiscount.Text = "";
txtsubtotal.Text = "";
txtpaidamount.Text = "";
txtdueamount.Text = "";
txtchangeamount.Text = "";
cmbcustomer.Text = "";
txtcomment.Text = "0";
//To refresh stock in Data grid view
DataTable dt = dal.select();
dgvstock.DataSource = dt;
ToGetInvoiceID();
}
else
{
//Transaction Failed
MessageBox.Show("Transaction Failed");
}
}
}
private void btnsave_Click(object sender, EventArgs e)
{
if (Validation1())
{
string inv_no = txtsearch.Text;
DataTable dt1 = dal1.Search(inv_no);
//Show user based on keywords
DataTable dt = dal.Search(inv_no);
if (dgvinvoice.Rows.Count == 0)
{
for (int i = 0; i < dt1.Rows.Count; i++)
{
bll1.id = Convert.ToInt32(dt1.Rows[i][0]);
}
bool x = false;
x = dal1.delete(bll1);
if (x == true)
{
MessageBox.Show("Data Deleted Succesfully");
//Celar the Data Grid View and Clear all the TExtboxes
dgvinvoice.DataSource = null;
dgvinvoice.Rows.Clear();
txttotal.Text = "";
txtdiscount.Text = "";
txtsubtotal.Text = "";
txtpaidamount.Text = "";
txtdueamount.Text = "";
txtchangeamount.Text = "";
cmbcustomer.Text = "";
}
else
{
MessageBox.Show("Error in deleting data");
}
}
else
{
for (int i = 0; i < dt1.Rows.Count; i++)
{
bll1.id = Convert.ToInt32(dt1.Rows[i][0]);
bll1.added_by = Convert.ToInt32(dt1.Rows[i][8]);
bll1.inv_no = Convert.ToInt32(dt1.Rows[i][9]);
}
bll1.customer_name = cmbcustomer.Text;
bll1.total_payable = txtsubtotal.Text;
bll1.paid_amount = txtpaidamount.Text;
bll1.discount = txtdiscount.Text;
bll1.due_amount = txtdueamount.Text;
bll1.change_amount = txtchangeamount.Text;
bll1.sales_date = Convert.ToDateTime(dtsalesdate.Text);
//Inserting Data into DAtabase
bool w = dal1.update(bll1);
for (int i = 0; i < dt.Rows.Count; i++)
{
//Get the Product name and convert it to id
bll.id = Convert.ToInt32(dt.Rows[i][0]);
bll.product_id = Convert.ToInt32(dt.Rows[i][1]);
bll.product_name = dt.Rows[i][3].ToString();
bll.price = dt.Rows[i][4].ToString();
bll.quantity = dt.Rows[i][5].ToString();
bll.discount = txtdiscount.Text;
bll.inv_no = dt.Rows[i][2].ToString();
bll.type = dt.Rows[i][9].ToString();
quantity11 = Convert.ToDecimal(bll.quantity);
q = Convert.ToDouble(dt.Rows[i][5]);
//Method to get purchase price an multiply it with quantity
Manage_Productsbll price = dal2.GetProductpriceFromId((dt.Rows[i][1]).ToString());
p = Convert.ToDouble((Convert.ToDouble(price.purchase_price)) * q);
bll.purchase_price = p.ToString();
//Getting total value by calculating discount
t1 = Convert.ToDouble(txtdiscount.Text);
t2 = Convert.ToDouble(dt.Rows[i][4]) * q;
bll.total = (t2 - (t2 * t1) / 100).ToString();
bll.added_by = Convert.ToInt32(dt.Rows[i][10]);
bll.added_date = Convert.ToDateTime(dtsalesdate.Text);
// Inserting Data into DAtabase
x = dal.update(bll);
//sucess1 = w && x;
}
if (w == true && x == true)
{
MessageBox.Show("Data Updated Succesfullay");
//Celar the Data Grid View and Clear all the TExtboxes
dgvinvoice.DataSource = null;
dgvinvoice.Rows.Clear();
txttotal.Text = "";
txtdiscount.Text = "";
txtsubtotal.Text = "";
txtpaidamount.Text = "";
txtdueamount.Text = "";
txtchangeamount.Text = "";
cmbcustomer.Text = "";
}
else
{
//Transaction Failed
MessageBox.Show("Failed to update");
}
}
}
}
internal bool update(Manage_Productsbll u)
{
throw new NotImplementedException();
}
