public DataTable Search(string keyword)
{
//MEthod to connect Database
connclass c = new connclass();
SqlConnection conn = new SqlConnection(c.connection);
//TO hold the data from database
DataTable dt = new DataTable();
try
{
//SQL Query to search Data from DAtabase
string query = "SELECT* FROM tbl_users WHERE id Like '%" + keyword + "%' OR user_name like '%" + keyword + "%' OR user_type like '%" + keyword + "%' OR adress like '%" + keyword + "%' ";
//For Executing Command
SqlCommand cmd = new SqlCommand(query, conn);
//Getting DAta from dAtabase
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//Database Connection Open
conn.Open();
//Fill Data in our DataTable
adapter.Fill(dt);
}
catch (Exception ex)
{
//Throw Message if any error occurs
MessageBox.Show(ex.Message);
}
finally
{
//Closing Connection
conn.Close();
}
//Return the value in DataTable
return(dt);
}
public bool DecreaseProduct(int id, decimal quantity)
{
//Create Boolean Variable and SEt its Value to false
bool success = false;
//MEthod to connect Database
connclass c = new connclass();
SqlConnection conn = new SqlConnection(c.connection);
try
{
//Get the Current product Quantity
decimal currentQty = GetProductQty(id);
//Decrease the Product Quantity based on product sales
decimal NewQty = currentQty - quantity;
//Update Product in Database
success = UpdateQuantity(id, NewQty);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(success);
}
public DataTable MonthlyAndDailyData(string d1, string d2)
{
//MEthod to connect Database
connclass c = new connclass();
SqlConnection conn = new SqlConnection(c.connection);
//TO hold the data from database
DataTable dt = new DataTable();
//string d1 = DateTime.Now.ToString();
try
{
//SQL Query to insert Data in DAtabase
string query = "SELECT* FROM tbl_productdetails WHERE added_date BETWEEN '" + d2 + "' AND '" + d1 + "' ";
//For Executing Command
SqlCommand cmd = new SqlCommand(query, conn);
//Getting DAta from dAtabase
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//Database Connection Open
conn.Open();
//Fill Data in our DataTable
adapter.Fill(dt);
}
catch (Exception ex)
{
//Throw Message if any error occurs
MessageBox.Show(ex.Message);
}
finally
{
//Closing Connection
conn.Close();
}
return(dt);
}
public bool IncreaseProduct(int id, decimal IncreaseQty)
{
//Create a Boolean Variable and SEt its value to False
bool success = false;
//MEthod to connect Database
connclass c = new connclass();
SqlConnection conn = new SqlConnection(c.connection);
try
{
//Get the Current Qty From dAtabase based on id
decimal currentQty = GetProductQty(id);
//Increase the Current Quantity by the qty purchased from Dealer
decimal NewQty = currentQty + IncreaseQty;
//Update the Prudcty Quantity Now
success = UpdateQuantity(id, NewQty);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(success);
}
public DataTable select()
{
//MEthod to connect Database
connclass c = new connclass();
SqlConnection conn = new SqlConnection(c.connection);
//TO hold the data from database
DataTable dt = new DataTable();
try
{
//SQL Query to Get Data From DAtabase
string query = "select* from tbl_users";
//For Executing Command
SqlCommand cmd = new SqlCommand(query, conn);
//Getting DAta from dAtabase
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//Database Connection Open
conn.Open();
//Fill Data in our DataTable
adapter.Fill(dt);
}
catch (Exception ex)
{
//Throw Message if any error occurs
MessageBox.Show(ex.Message);
}
finally
{
//Closing Connection
conn.Close();
}
//Return the value in DataTable
return(dt);
}
public bool update(product_detailsbll 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 = "UPDATE tbl_productdetails set product_id=@product_id,product_name=@product_name,price=@price,quantity=@quantity,discount=@discount,total=@total,purchase_price=@purchase_price,inv_no=@inv_no,type=@type,added_by=@added_by,added_date=@added_date,code=@code WHERE id=@id";
//For Executing Command
SqlCommand cmd = new SqlCommand(query, conn);
//Passing Values to the Variables
cmd.Parameters.AddWithValue("@id", u.id);
cmd.Parameters.AddWithValue("@product_id", u.product_id);
cmd.Parameters.AddWithValue("@product_name", u.product_name);
cmd.Parameters.AddWithValue("@price", u.price);
cmd.Parameters.AddWithValue("@quantity", u.quantity);
cmd.Parameters.AddWithValue("@discount", u.discount);
cmd.Parameters.AddWithValue("@total", u.total);
cmd.Parameters.AddWithValue("@purchase_price", u.purchase_price);
cmd.Parameters.AddWithValue("@inv_no", u.inv_no);
cmd.Parameters.AddWithValue("@type", u.type);
cmd.Parameters.AddWithValue("@added_by", u.added_by);
cmd.Parameters.AddWithValue("@added_date", u.added_date);
cmd.Parameters.AddWithValue("@code", u.code);
//Database Connection Open
conn.Open();
//To execute non query
int rows = 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 (rows > 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 update(invoicebll 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 = "UPDATE tbl_invoice set inv_no=@inv_no, customer_name=@customer_name,total_payable=@total_payable,paid_amount=@paid_amount,discount=@discount,due_amount=@due_amount,change_amount=@change_amount,added_by=@added_by,sales_date=@sales_date WHERE id=@id";
//For Executing Command
SqlCommand cmd = new SqlCommand(query, conn);
//Passing Values to the Variables
cmd.Parameters.AddWithValue("@id", u.id);
cmd.Parameters.AddWithValue("@inv_no", u.inv_no);
cmd.Parameters.AddWithValue("@customer_name", u.customer_name);
cmd.Parameters.AddWithValue("@total_payable", u.total_payable);
cmd.Parameters.AddWithValue("@paid_amount", u.paid_amount);
cmd.Parameters.AddWithValue("@discount", u.discount);
cmd.Parameters.AddWithValue("@due_amount", u.due_amount);
cmd.Parameters.AddWithValue("@change_amount", u.change_amount);
cmd.Parameters.AddWithValue("@added_by", u.added_by);
cmd.Parameters.AddWithValue("@sales_date", u.sales_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 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 insert(usersbll 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_users(user_name,user_type,password,email,cnic,adress,phone_no,added_by,added_date)Values(@user_name,@user_type,@password,@email,@cnic,@adress,@phone_no,@added_by,@added_date)";
//For Executing Command
SqlCommand cmd = new SqlCommand(query, conn);
//Passing Values to the Variables
cmd.Parameters.AddWithValue("@user_name", u.user_name);
cmd.Parameters.AddWithValue("@user_type", u.user_type);
cmd.Parameters.AddWithValue("@password", u.password);
cmd.Parameters.AddWithValue("@email", u.email);
cmd.Parameters.AddWithValue("@cnic", u.cnic);
cmd.Parameters.AddWithValue("@adress", u.adress);
cmd.Parameters.AddWithValue("@phone_no", u.phone_no);
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 exist(usersbll 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_users where user_name=@user_name AND cnic=@cnic AND email=@email AND phone_no=@phone_no ";
//For Executing Command
SqlCommand cmd = new SqlCommand(query, conn);
//Passing Values to the Variables
cmd.Parameters.AddWithValue("@user_name", u.user_name);
cmd.Parameters.AddWithValue("@cnic", u.cnic);
cmd.Parameters.AddWithValue("@email", u.email);
cmd.Parameters.AddWithValue("@phone_no", u.phone_no);
//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 update(suppliersbll 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 update Data in DAtabase
string query = "UPDATE tbl_suppliers set user_name=@user_name,email=@email,company=@company,adress=@adress,phone_no=@phone_no WHERE id=@id";
//For Executing Command
SqlCommand cmd = new SqlCommand(query, conn);
//Passing Values to the Variables
cmd.Parameters.AddWithValue("@id", u.id);
cmd.Parameters.AddWithValue("@user_name", u.user_name);
cmd.Parameters.AddWithValue("@email", u.email);
cmd.Parameters.AddWithValue("@company", u.company);
cmd.Parameters.AddWithValue("@adress", u.adress);
cmd.Parameters.AddWithValue("@phone_no", u.phone_no);
//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 login(loginbll 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 check login
string query = "select * from tbl_users where user_name=@user_name AND password=@password AND user_type=@user_type";
//Creating SQL Command to pass value
SqlCommand cmd = new SqlCommand(query, conn);
//Passing Values to the Variables
cmd.Parameters.AddWithValue("@user_name", u.user_name);
cmd.Parameters.AddWithValue("@password", u.password);
cmd.Parameters.AddWithValue("@user_type", u.user_type);
//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)
{
//Login Sucessful
issuccess = true;
}
else
{
//Login Failed
issuccess = false;
}
}
catch (Exception ex)
{
//Throw Message if any error occurs
MessageBox.Show(ex.Message);
}
finally
{
//Closing Connection
conn.Close();
}
return(issuccess);
}
public bool UpdateQuantity(int id, decimal quantity)
{
//Create a Boolean Variable and Set its value to false
bool success = false;
//MEthod to connect Database
connclass c = new connclass();
SqlConnection conn = new SqlConnection(c.connection);
try
{
//Write the SQL Query to Update Qty
string sql = "UPDATE tbl_productdetails SET quantity=@quantity WHERE id=@id";
//Create SQL Command to Pass the calue into Queyr
SqlCommand cmd = new SqlCommand(sql, conn);
//Passing the VAlue trhough parameters
cmd.Parameters.AddWithValue("@quantity", quantity);
cmd.Parameters.AddWithValue("@id", id);
//Open Database Connection
conn.Open();
//Create Int Variable and Check whether the query is executed Successfully or not
int rows = cmd.ExecuteNonQuery();
//Lets check if the query is executed Successfully or not
if (rows > 0)
{
//Query Executed Successfully
success = true;
}
else
{
//Failed to Execute Query
success = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(success);
}
public decimal GetProductQty(int id)
{
//MEthod to connect Database
connclass c = new connclass();
SqlConnection conn = new SqlConnection(c.connection);
//Create a Decimal Variable and set its default value to 0
decimal quantity = 0;
//Create Data Table to save the data from database temporarily
DataTable dt = new DataTable();
try
{
//Write WQL Query to Get Quantity from Database
string sql = "SELECT quantity FROM tbl_productdetails WHERE id = " + id;
//Cerate A SqlCommand
SqlCommand cmd = new SqlCommand(sql, conn);
//Create a SQL Data Adapter to Execute the query
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//open DAtabase Connection
conn.Open();
//PAss the calue from Data Adapter to DataTable
adapter.Fill(dt);
//Lets check if the datatable has value or not
if (dt.Rows.Count > 0)
{
quantity = decimal.Parse(dt.Rows[0]["quantity"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
//Close Database Connection
conn.Close();
}
return(quantity);
}
public bool delete(usersbll 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_users 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 usersbll GetIDFromUsername(string username)
{
//Connecting user BLL for getting id
usersbll u = new usersbll();
//MEthod to connect Database
connclass c = new connclass();
SqlConnection conn = new SqlConnection(c.connection);
//TO hold the data from database
DataTable dt = new DataTable();
try
{
//SQL Query to get Data from DAtabase
string sql = "SELECT id FROM tbl_users WHERE user_name='" + username + "'";
//Getting DAta from dAtabase
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
//Database Connection Open
conn.Open();
//Fill Data in our DataTable
adapter.Fill(dt);
//Checking The rows in DataTable
if (dt.Rows.Count > 0)
{
//Getting id from DataTable
u.id = int.Parse(dt.Rows[0]["id"].ToString());
}
}
catch (Exception ex)
{
//Throw Message if any error occurs
MessageBox.Show(ex.Message);
}
finally
{
//Closing Connection
conn.Close();
}
return(u);
}
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);
}