//--------------endOftotal Function---------------------------------------------------------------------------------------------------------------------
//--------------startOfbillToDB Function---------------------------------------------------------------------------------------------------------------------
public void billToDB(object obj)
{
Billingform bf = (Billingform)obj;
DBConnection db = new DBConnection();
try
{
string inv_no = bf.textBox1.Text;
string user = bf.label16.Text;
string code = null;
string qty = null;
string discount = null;
string total = null;
DateTime date = Convert.ToDateTime(bf.label9.Text.Substring(0, bf.label9.Text.Length - 12));
string time = bf.label9.Text.Substring(10, 11);
string pay_Type = "ca";
if (db.OpenConnection() == true)
{
for (int i = 0; i < bf.dataGridView1.RowCount; i++)
{
code = bf.dataGridView1.Rows[i].Cells[1].Value.ToString();
qty = bf.dataGridView1.Rows[i].Cells[3].Value.ToString();
discount = bf.dataGridView1.Rows[i].Cells[4].Value.ToString();
total = bf.dataGridView1.Rows[i].Cells[6].Value.ToString();
string query = "INSERT INTO bills VALUES('" + inv_no + "','" + code + "','" + qty + "','" + total + "','" + discount + "')";
MySqlCommand cmd = new MySqlCommand(query, db.connection);
cmd.ExecuteNonQuery();
}
string query2 = "INSERT INTO bills_info VALUES('" + inv_no + "','" + user + "','" + date.Year + "-" + date.Month + "-" + date.Day + "','" + time + "','" + pay_Type + "')";
MySqlCommand cmdOne = new MySqlCommand(query2, db.connection);
cmdOne.ExecuteNonQuery();
bf.reload();
}
else
{
throw new Exception("DB Connection Error");
}
}
catch (Exception exc)
{
MessageBox.Show("Errror Occured, Please Try Again, " + exc.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
db.CloseConnection();
}
}
public void reader(object obj)
{
DBConnection db = new DBConnection();
try
{
string query = "SELECT * FROM items WHERE Item_Code='"+barCode+"'";
if (db.OpenConnection() == true)
{
//populate data gridview from result
MySqlCommand cmd = new MySqlCommand(query, db.connection);
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);
if(table.Rows.Count==0)
{
MessageBox.Show("Invalid BarCode", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
Billingform bf=(Billingform)obj;
bf.txtBoxDescription.Text=table.Rows[0].ItemArray[1].ToString();
bf.txtBoxDiscount.Text=table.Rows[0].ItemArray[4].ToString();
bf.textBox8.Text=table.Rows[0].ItemArray[2].ToString();
bf.txtBoxCode.ReadOnly = true;
bf.ActiveControl = bf.textBox2;
}
}
else
{
MessageBox.Show("DB Connection Error, Please Try Again", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception exc)
{
MessageBox.Show("Error Occured, Please Try Again, " + exc.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
bool a = db.CloseConnection();
}
}
public void reader(object obj)
{
DBConnection db = new DBConnection();
try
{
string query = "SELECT * FROM items WHERE Item_Code='" + barCode + "'";
if (db.OpenConnection() == true)
{
//populate data gridview from result
MySqlCommand cmd = new MySqlCommand(query, db.connection);
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);
if (table.Rows.Count == 0)
{
MessageBox.Show("Invalid BarCode", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
Billingform bf = (Billingform)obj;
bf.txtBoxDescription.Text = table.Rows[0].ItemArray[1].ToString();
bf.txtBoxDiscount.Text = table.Rows[0].ItemArray[6].ToString();
bf.textBox8.Text = table.Rows[0].ItemArray[4].ToString();
bf.txtBoxCode.ReadOnly = true;
bf.ActiveControl = bf.textBox2;
}
}
else
{
MessageBox.Show("DB Connection Error, Please Try Again", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception exc)
{
MessageBox.Show("Error Occured, Please Try Again, " + exc.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
bool a = db.CloseConnection();
}
}
//-------------------startOfreorderFunction---------------------------------------------------------------------------------------------------------------------------------------------
// RavishaHeshan([email protected])--2/21/2014
public void reorder(string code, int qty)
{
DBConnection db = new DBConnection();
try
{
if (db.OpenConnection() == true)
{
int quantity = 0;
int reorder_level = 0;
string query = "SELECT Quantity, Reorder_Level FROM items WHERE Item_Code=" + code + "";
MySqlCommand cmd = new MySqlCommand(query, db.connection);
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);
quantity = int.Parse(table.Rows[0].ItemArray[0].ToString());
reorder_level = int.Parse(table.Rows[0].ItemArray[1].ToString());
if (quantity <= reorder_level)
{
int result;
string query1 = "SELECT COUNT(Quantity) FROM alert WHERE Item_Code=" + code + "";
MySqlCommand cmd1 = new MySqlCommand(query1, db.connection);
result = int.Parse(cmd1.ExecuteScalar().ToString());
if (result == 0)
{
string query2 = "INSERT INTO alert(Item_Code,Quantity,Date,Time) VALUES('" + code + "','" + qty.ToString() + "','" + DateTime.UtcNow.ToString("yyyy-MM-dd") + "','" + DateTime.Now.ToString("HH:mm:ss tt") + "')";
MySqlCommand cmd2 = new MySqlCommand(query2, db.connection);
cmd2.ExecuteNonQuery();
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("Error Occured," + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
db.CloseConnection();
}
}
//--------------endOfbillToDB Function---------------------------------------------------------------------------------------------------------------------
//--------------startOfupdate_qty Function---------------------------------------------------------------------------------------------------------------------
public void update_qty(object obj, int qty, string code)
{
DBConnection db = new DBConnection();
try
{
if (db.OpenConnection() == true)
{
int quantity = 0;
string query1 = "SELECT Quantity FROM items WHERE Item_Code=" + code + "";
MySqlCommand cmd = new MySqlCommand(query1, db.connection);
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);
quantity = int.Parse(table.Rows[0].ItemArray[0].ToString());
quantity = quantity + int.Parse(qty.ToString());
string query2 = "UPDATE items SET Quantity=" + quantity + " WHERE Item_Code=" + code + "";
MySqlCommand cmd1 = new MySqlCommand(query2, db.connection);
cmd1.ExecuteNonQuery();
total(obj);
}
}
catch (Exception ex)
{
MessageBox.Show("Error Occured," + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
db.CloseConnection();
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
DBConnection db = new DBConnection();
//Admin item = new Admin();
try
{
string query;
if (db.OpenConnection() == true)
{
query = "SELECT * From users";
MySqlCommand cmd = new MySqlCommand(query, db.connection);
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
DataTable table = new DataTable();
adapter.Fill(table);
BindingSource bsource = new BindingSource();
bsource.DataSource = table;
dataGridView2.DataSource = table;
adapter.Update(table);
}
}
catch (Exception ex)
{
MessageBox.Show("Error Occured," + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
bool a = db.CloseConnection();
}
}
//--------------startOfBillNoGen Function---------------------------------------------------------------------------------------------------------------------------
//function is for generate billno for BillingForm
public int BillNoGen(object obj)
{
DBConnection db = new DBConnection();
int billno = 0; //initialize billno variable
try
{
string query = "SELECT COUNT(Quantity) FROM bills";
int count = 0; //initialize count variable
if (db.OpenConnection() == true)
{
MySqlCommand cmd = new MySqlCommand(query, db.connection);
count = int.Parse(cmd.ExecuteScalar().ToString()); //check for existing bills
if (count < 1)
billno = 000001; //first billno
else
{
string queryOne = "SELECT Invoice_No FROM bills ORDER BY Invoice_No DESC LIMIT 1";
MySqlCommand cmdOne = new MySqlCommand(queryOne, db.connection);
int result = int.Parse(cmdOne.ExecuteScalar().ToString()); //retrieve billno of last bill
billno = result + 1; //next billno
}
}
else
{
throw new Exception("DB Connection Error");
}
}
catch (Exception ex)
{
Billingform bf = (Billingform)obj;
DialogResult result = MessageBox.Show("Error " + ex.Message + " Do you need to Retry?", "Oops!", System.Windows.Forms.MessageBoxButtons.RetryCancel, System.Windows.Forms.MessageBoxIcon.Question);
if (result == DialogResult.Retry)
{
bf.reload();
}
else
{
bf.Close();
}
}
finally
{
bool a = db.CloseConnection();
}
return billno;
}
//--------------endOftotal Function---------------------------------------------------------------------------------------------------------------------
//--------------startOfbillToDB Function---------------------------------------------------------------------------------------------------------------------
public void billToDB(object obj)
{
Billingform bf = (Billingform)obj;
DBConnection db = new DBConnection();
try
{
string inv_no = bf.textBox1.Text;
string user = bf.label16.Text;
string code = null;
string qty = null;
string discount = null;
string total = null;
DateTime date = Convert.ToDateTime(bf.label9.Text.Substring(0, bf.label9.Text.Length - 12));
string time = bf.label9.Text.Substring(10, 11);
string pay_Type = "ca";
if (db.OpenConnection() == true)
{
for (int i = 0; i < bf.dataGridView1.RowCount; i++)
{
code = bf.dataGridView1.Rows[i].Cells[1].Value.ToString();
qty = bf.dataGridView1.Rows[i].Cells[3].Value.ToString();
discount = bf.dataGridView1.Rows[i].Cells[4].Value.ToString();
total = bf.dataGridView1.Rows[i].Cells[6].Value.ToString();
string query = "INSERT INTO bills VALUES('" + inv_no + "','" + code + "','" + qty + "','" + total + "','" + discount + "')";
MySqlCommand cmd = new MySqlCommand(query, db.connection);
cmd.ExecuteNonQuery();
}
string query2 = "INSERT INTO bills_info VALUES('" + inv_no + "','" + user + "','" + date.Year + "-" + date.Month + "-" + date.Day + "','" + time + "','" + pay_Type + "')";
MySqlCommand cmdOne = new MySqlCommand(query2, db.connection);
cmdOne.ExecuteNonQuery();
bf.reload();
}
else
{
throw new Exception("DB Connection Error");
}
}
catch (Exception exc)
{
MessageBox.Show("Errror Occured, Please Try Again, " + exc.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//--------------endOfEnter Function---------------------------------------------------------------------------------------------------------------------------
//--------------startOfManualBilling Function-------------------------------------------------------------------------------------------------------------------
public void manualBilling(string form, string searchKey, object obj = null, string tab = null)
{
DBConnection db = new DBConnection();
TextBox textbox = null;
DataGridView datagridview = null;
string querystring = null;
//for ManualBilling form
if (form == "mb")
{
ManualBilling mb = (ManualBilling)obj;
textbox = mb.txtBoxDescription;
datagridview = mb.dataGridView1;
}
if (form == "admin" && tab == "itm")
{
Admin frm = (Admin)obj;
textbox = frm.textBox6;
datagridview = frm.dataGridView1;
}
if (form == "admin" && tab == "qty")
{
Admin frm = (Admin)obj;
textbox = frm.textBox24;
datagridview = frm.dataGridView5;
}
if (form == "admin" && tab == "usrs")
{
Admin frm = (Admin)obj;
textbox = frm.txtBoxSearch;
datagridview = frm.dataGridView2;
}
if (form == "admin" && tab == "sup")
{
Admin frm = (Admin)obj;
textbox = frm.textBox18;
datagridview = frm.dataGridView4;
}
try
{
string query = null;
textbox.ReadOnly = false;
textbox.Text = textbox.Text + searchKey; //assign character of the pressed key into the end of the textbox
textbox.Select(textbox.Text.Length, 0); //move cursor into the end of text in the textbox
textbox.ReadOnly = true;
if (form == "mb")//for ManualBilling form
{
querystring = "SELECT * From items WHERE Description LIKE CONCAT('" + textbox.Text + "','%')";
}
if (form == "admin" && tab == "itm")
{
if (textbox.Text == "")
{
querystring = "SELECT * From items";
}
else
{
querystring = "SELECT * From items WHERE Description LIKE CONCAT('" + textbox.Text + "','%')";
}
}
if (form == "admin" && tab == "qty")
{
if (textbox.Text == "")
{
querystring = "SELECT Item_Code,Description,Quantity From items";
}
else
{
querystring = "SELECT Item_Code,Description,Quantity From items WHERE Description LIKE CONCAT('" + textbox.Text + "','%')";
}
}
if (form == "admin" && tab == "usrs")
{
if (textbox.Text == "")
{
querystring = "SELECT * From users";
}
else
{
querystring = "SELECT * From users WHERE User_Name LIKE CONCAT('" + textbox.Text + "','%')";
}
}
if (form == "admin" && tab == "sup")
{
if (textbox.Text == "")
{
querystring = "SELECT * From suppliers";
}
else
{
querystring = "SELECT * From suppliers WHERE Supplier_Name LIKE CONCAT('" + textbox.Text + "','%')";
}
}
query = querystring;
if (db.OpenConnection() == true)
{
//populate data gridview from result
MySqlCommand cmd = new MySqlCommand(query, db.connection);
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);
BindingSource bs = new BindingSource();
bs.DataSource = table;
datagridview.DataSource = bs;
datagridview.AutoResizeRows(DataGridViewAutoSizeRowsMode.AllCellsExceptHeaders);
if (datagridview.RowCount > 0)
{
datagridview.Rows[0].Selected = true; //auto select first record of datagridview if there is any record
}
}
else
{
MessageBox.Show("DB Connection Error, Please Try Again", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception exc)
{
MessageBox.Show("Error Occured, Please Try Again" + exc.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
bool a = db.CloseConnection();
}
}
//--------------endOfUpArrow Function---------------------------------------------------------------------------------------------------------------------------
//--------------startOfEnter Function---------------------------------------------------------------------------------------------------------------------------
public void enterButton(string form, string focus, object obj, object formobj = null)
{
DBConnection db = new DBConnection();
try
{
if (form == "mb" && focus == "dgv")
{
ManualBilling mb = (ManualBilling)obj;
Billingform bf = (Billingform)formobj;
int row = mb.dataGridView1.CurrentCell.RowIndex;
if (mb.dataGridView1.RowCount != 0)
{
int code = (int)mb.dataGridView1.Rows[row].Cells[0].Value;
string des = mb.dataGridView1.Rows[row].Cells[1].Value.ToString();
decimal price = (decimal)mb.dataGridView1.Rows[row].Cells[4].Value;
decimal l_price = (decimal)mb.dataGridView1.Rows[row].Cells[5].Value;
decimal disc = (decimal)mb.dataGridView1.Rows[row].Cells[6].Value;;
//string other = mb.dataGridView1.Rows[row].Cells[5].Value.ToString();
bf.txtBoxCode.Text = code.ToString();
bf.txtBoxDescription.Text = des;
bf.textBox8.Text = price.ToString();
bf.txtBoxDiscount.Text = disc.ToString();
mb.Close();
bf.ActiveControl = bf.textBox2;
}
}
if (form == "bf" && focus == "qty")
{
Billingform bf = (Billingform)obj;
string code = bf.txtBoxCode.Text;
string des = bf.txtBoxDescription.Text;
decimal price;
Decimal.TryParse(bf.textBox8.Text, out price);
decimal disc;
Decimal.TryParse(bf.txtBoxDiscount.Text, out disc);
decimal qty;
Decimal.TryParse(bf.textBox2.Text, out qty);
decimal tot = 0.00m;
if (qty == 0)
{
qty = 1;
}
if (disc == 0)
{
tot = (price * qty);
}
else
{
decimal newprice;
newprice = (price - ((price / 100) * disc));
tot = (newprice * qty);
}
Decimal.TryParse((tot.ToString().Substring(0, tot.ToString().Length)), out tot);
Decimal total;
Decimal.TryParse(bf.label7.Text, out total);
tot = Math.Round(tot, 2);
bf.dataGridView1.Rows.Add(bf.dataGridView1.RowCount + 1, code, des, qty, disc, price, tot);
//reduce qty
if (db.OpenConnection() == true)
{
int quantity = 0;
string query1 = "SELECT Quantity FROM items WHERE Item_Code=" + code + "";
MySqlCommand cmd = new MySqlCommand(query1, db.connection);
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);
quantity = int.Parse(table.Rows[0].ItemArray[0].ToString());
if (quantity > int.Parse(qty.ToString()))
{
quantity = quantity - int.Parse(qty.ToString());
string query2 = "UPDATE items SET Quantity=" + quantity + " WHERE Item_Code=" + code + "";
MySqlCommand cmd1 = new MySqlCommand(query2, db.connection);
cmd1.ExecuteNonQuery();
ItemDBConnection idc = new ItemDBConnection();
idc.reorder(code, quantity);
BillGeneration bg = new BillGeneration();
bg.total(bf);
}
else
{
MessageBox.Show("According to Database Stocks in hand is lesser than the Quantity that you going to purchuase", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
bf.dataGridView1.Rows.RemoveAt(bf.dataGridView1.RowCount - 1);
bf.txtBoxCode.Text = "";
bf.txtBoxDescription.Text = "";
bf.textBox8.Text = "";
bf.textBox2.Text = "";
bf.txtBoxDiscount.Text = "";
bf.ActiveControl = bf.txtBoxDescription;
}
}
}
if (form == "bf" && focus == "des")
{
Billingform bf = (Billingform)obj;
if (bf.dataGridView1.RowCount != 0)
{
bf.ActiveControl = bf.textBox3;
}
else
{
SystemSounds.Hand.Play();
}
}
if (form == "bf" && focus == "dgv")
{
Form6 newform = new Form6(obj);
newform.Show();
}
}
catch (Exception exc)
{
MessageBox.Show("Error Occured, Please Try Again, " + exc.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
db.CloseConnection();
}
}
//--------------startOfBillNoGen Function---------------------------------------------------------------------------------------------------------------------------
//function is for generate billno for BillingForm
public int BillNoGen(object obj)
{
DBConnection db = new DBConnection();
int billno = 0; //initialize billno variable
try
{
string query = "SELECT COUNT(Quantity) FROM bills";
int count = 0; //initialize count variable
if (db.OpenConnection() == true)
{
MySqlCommand cmd = new MySqlCommand(query, db.connection);
count = int.Parse(cmd.ExecuteScalar().ToString()); //check for existing bills
if (count < 1)
{
billno = 000001; //first billno
}
else
{
string queryOne = "SELECT Invoice_No FROM bills ORDER BY Invoice_No DESC LIMIT 1";
MySqlCommand cmdOne = new MySqlCommand(queryOne, db.connection);
int result = int.Parse(cmdOne.ExecuteScalar().ToString()); //retrieve billno of last bill
billno = result + 1; //next billno
}
}
else
{
throw new Exception("DB Connection Error");
}
}
catch (Exception ex)
{
Billingform bf = (Billingform)obj;
DialogResult result = MessageBox.Show("Error " + ex.Message + " Do you need to Retry?", "Oops!", System.Windows.Forms.MessageBoxButtons.RetryCancel, System.Windows.Forms.MessageBoxIcon.Question);
if (result == DialogResult.Retry)
{
bf.reload();
}
else
{
bf.Close();
}
}
finally
{
bool a = db.CloseConnection();
}
return(billno);
}
//--------------endOfEnter Function---------------------------------------------------------------------------------------------------------------------------
//--------------startOfManualBilling Function-------------------------------------------------------------------------------------------------------------------
public void manualBilling(string form, string searchKey, object obj = null)
{
DBConnection db = new DBConnection();
TextBox textbox = null;
DataGridView datagridview = null;
string querystring = null;
//for ManualBilling form
if (form == "mb")
{
ManualBilling mb = (ManualBilling)obj;
textbox = mb.txtBoxDescription;
datagridview = mb.dataGridView1;
}
if (form == "admin")
{
Admin frm = (Admin)obj;
textbox = frm.textBox6;
datagridview = frm.dataGridView1;
}
try
{
string query = null;
textbox.ReadOnly = false;
textbox.Text = textbox.Text + searchKey; //assign character of the pressed key into the end of the textbox
textbox.Select(textbox.Text.Length, 0); //move cursor into the end of text in the textbox
textbox.ReadOnly = true;
if (form == "mb")//for ManualBilling form
{
querystring = "SELECT * From items WHERE Description LIKE CONCAT('" + textbox.Text + "','%')";
}
if (form == "admin")
{
if (textbox.Text == "")
{
querystring = "SELECT * From items";
}
else
{
querystring = "SELECT * From items WHERE Description LIKE CONCAT('" + textbox.Text + "','%')";
}
}
query = querystring;
if (db.OpenConnection() == true)
{
//populate data gridview from result
MySqlCommand cmd = new MySqlCommand(query, db.connection);
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);
BindingSource bs = new BindingSource();
bs.DataSource = table;
datagridview.DataSource = bs;
datagridview.AutoResizeRows(DataGridViewAutoSizeRowsMode.AllCellsExceptHeaders);
if (datagridview.RowCount > 0)
{
datagridview.Rows[0].Selected = true; //auto select first record of datagridview if there is any record
}
}
else
{
MessageBox.Show("DB Connection Error, Please Try Again", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception exc)
{
MessageBox.Show("Error Occured, Please Try Again" + exc.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
bool a = db.CloseConnection();
}
}