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();
}
}
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);
}
}
//--------------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();
}
}