private void autoCompleteStockCode()
{
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String sql = "SELECT stock_code FROM inventory_stocks WHERE supplier_code = ?supplier_code ORDER BY stock_code ASC";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("?supplier_code", txtBoxSupplierCode.Text);
cmd.CommandType = CommandType.Text;
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows == true)
{
while (rdr.Read())
{
collect2.Add(rdr["stock_code"].ToString());
}
}
rdr.Close();
txtBoxStockCode.AutoCompleteMode = AutoCompleteMode.Suggest;
txtBoxStockCode.AutoCompleteSource = AutoCompleteSource.CustomSource;
txtBoxStockCode.AutoCompleteCustomSource = collect2;
con.Close();
}
catch (Exception)
{
MessageBox.Show("Please Check your Database Server Connection", "Database Server Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.ExitThread();
}
}
private void getDataTable()
{
dbcon = new Conf.dbs();
dataGridView1.DataSource = null;
dataGridView1.Rows.Clear();
dataGridView1.Refresh();
String connectionString = dbcon.getConnectionString();
String query = "SELECT order_quantity AS a, order_suppliers_itemno AS b, order_uom AS c, order_description AS d, ";
query += "order_unitcost AS e, order_amount AS f FROM po_order_list ";
query += "WHERE po_no = ?po_no";
using (MySqlConnection con = new MySqlConnection(connectionString))
{
using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, con))
{
try
{
DataTable dataTable = new DataTable();
adapter.SelectCommand.Parameters.AddWithValue("?po_no", rdPOno.Text);
adapter.Fill(dataTable);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
dataGridView1.Rows.Add(dataTable.Rows[i][0], dataTable.Rows[i][1], dataTable.Rows[i][2], dataTable.Rows[i][3], dataTable.Rows[i][4], dataTable.Rows[i][5]);
}
}
catch (MySqlException ex)
{
MessageBox.Show("Error" + ex);
}
}
} // end using
}
private void btnProceed_Click(object sender, EventArgs e)
{
Convert.ToInt32(lblON.Text);
String name = txtBoxName.Text;
String address = txtBoxAdd.Text;
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String query = "UPDATE order_store SET order_customer = ?customer_name, order_address = ?customer_address ";
query += "WHERE order_no ='" + lblON.Text + "'";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?customer_name", name);
cmd.Parameters.AddWithValue("?customer_address", address);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
finally
{
con.Close();
}
}
public void toAddress()
{
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT supplier_address FROM inventory_supplier ";
query += "WHERE supplier_code = ?supplier_code";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?supplier_code", txtBoxSupplierCode.Text);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
lAddress.Text = rdr["supplier_address"].ToString();
}
else
{
lAddress.Text = "";
}
}
catch (Exception)
{
lAddress.Text = "";
}
}
private void getDataTableItems()
{
dbcon = new Conf.dbs();
dataGridView2.DataSource = null;
dataGridView2.Rows.Clear();
dataGridView2.Refresh();
String connectionString = dbcon.getConnectionString();
String query = "SELECT inventory_stocks.stock_code AS a, inventory_items.item_ean AS b, inventory_stocks.stock_name AS c, ";
query += "inventory_items.item_retail_price AS d, inventory_category.cat_description AS e ";
query += "FROM inventory_items ";
query += "INNER JOIN inventory_stocks ON inventory_items.stock_code = inventory_stocks.stock_code ";
query += "INNER JOIN inventory_category ON inventory_stocks.stock_cat_code = inventory_category.cat_code ";
query += "WHERE (inventory_items.is_kit = 0)";
using (MySqlConnection con = new MySqlConnection(connectionString))
{
using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, con))
{
try
{
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
dataGridView2.Rows.Add(dataTable.Rows[i][0], dataTable.Rows[i][1], dataTable.Rows[i][2], dataTable.Rows[i][3], dataTable.Rows[i][4]);
}
}
catch (MySqlException ex)
{
MessageBox.Show("Error" + ex);
}
}
} // end using
}
private void supplier()
{
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT supplier_code AS a FROM inventory_supplier ";
query += "WHERE supplier_name = ?supplier_name";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?supplier_name", cBoxSupplier.Text);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
sup = rdr["a"].ToString();
}
}
catch (Exception)
{
Application.ExitThread();
}
finally
{
con.Close();
}
}
private void fetchUnitAndSelling()
{
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT stock_uom AS a, stock_name AS b FROM inventory_stocks ";
query += "WHERE stock_code = ?stock_code";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?stock_code", dataGridView2.SelectedRows[0].Cells[0].Value.ToString());
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
uom = rdr["a"].ToString();
description = rdr["b"].ToString();
}
con.Close();
}
catch (Exception)
{
MessageBox.Show("Check Database Server", "Database Server Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void getDataTableKits()
{
dbcon = new Conf.dbs();
dataGridView1.DataSource = null;
dataGridView1.Rows.Clear();
dataGridView1.Refresh();
String connectionString = dbcon.getConnectionString();
String query = "SELECT inventory_items_kit.kit_qty AS aa, inventory_stocks.stock_code AS a, inventory_stocks.stock_name AS b, inventory_stocks.stock_uom AS c, inventory_stocks.stock_selling_price AS d ";
query += "FROM inventory_items_kit INNER JOIN inventory_stocks ON inventory_items_kit.stock_code = inventory_stocks.stock_code INNER JOIN inventory_items ON inventory_items_kit.item_ean = inventory_items.item_ean ";
query += "WHERE (inventory_items_kit.item_ean = ?ean)";
using (MySqlConnection con = new MySqlConnection(connectionString))
{
using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, con))
{
try
{
DataTable dataTable = new DataTable();
adapter.SelectCommand.Parameters.AddWithValue("?ean", Ean);
adapter.Fill(dataTable);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
dataGridView1.Rows.Add(dataTable.Rows[i][0], dataTable.Rows[i][1], dataTable.Rows[i][2], dataTable.Rows[i][3], dataTable.Rows[i][4]);
}
}
catch (MySqlException ex)
{
MessageBox.Show("Error" + ex);
}
}
} // end using
}
private void getDataTable()
{
dbcon = new Conf.dbs();
dataGridView1.DataSource = null;
dataGridView1.Rows.Clear();
dataGridView1.Refresh();
String connectionString = dbcon.getConnectionString();
String query = "SELECT user_account.user_id AS a, user_account.user_name AS b, user_information.first_name AS c, user_information.middle_name AS d, user_information.last_name AS e, user_account.last_login AS f, user_account.date_created AS g ";
query += "FROM user_account ";
query += "INNER JOIN user_information ON user_account.user_id = user_information.user_id";
using (MySqlConnection con = new MySqlConnection(connectionString))
{
using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, con))
{
try
{
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
dataGridView1.Rows.Add(dataTable.Rows[i][0], dataTable.Rows[i][1], dataTable.Rows[i][2], dataTable.Rows[i][3], dataTable.Rows[i][4], dataTable.Rows[i][5], dataTable.Rows[i][6]);
}
}
catch (Exception ex)
{
MessageBox.Show("Error" + ex);
}
}
} // end using
}
private void loadUsername()
{
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT DISTINCT user_name FROM user_account ORDER BY user_name ASC";
try
{
cBoxUserName.Items.Clear();
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
cBoxUserName.BeginUpdate();
cBoxUserName.Items.Add(rdr["user_name"].ToString());
cBoxUserName.EndUpdate();
}
con.Close();
}
catch (Exception)
{
MessageBox.Show("Please Check your Database Server Connection", "Database Server Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.ExitThread();
}
}
private void autoComplete()
{
con.ConnectionString = dbcon.getConnectionString();
String sql = "SELECT DISTINCT user_name FROM user_account ORDER BY user_name ASC";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(sql, con);
cmd.CommandType = CommandType.Text;
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows == true)
{
while (rdr.Read())
{
collect.Add(rdr["user_name"].ToString());
}
}
rdr.Close();
txtBoxUsername.AutoCompleteMode = AutoCompleteMode.Suggest;
txtBoxUsername.AutoCompleteSource = AutoCompleteSource.CustomSource;
txtBoxUsername.AutoCompleteCustomSource = collect;
con.Close();
}
catch (Exception)
{
MessageBox.Show("Please Check your Database Server Connection", "Database Server Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.ExitThread();
}
}
private void CheckIfStockCodePrevent()
{
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT DISTINCT order_suppliers_itemno FROM po_order_list WHERE order_suppliers_itemno = ?a AND po_no = ?b";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?a", txtBoxStockCode.Text);
cmd.Parameters.AddWithValue("?b", rdPOno.Text);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
supplier_code = rdr["order_suppliers_itemno"].ToString();
}
else
{
supplier_code = "";
}
con.Close();
}
catch (Exception)
{
MessageBox.Show("Please Check your Database Server Connection", "Database Server Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.ExitThread();
}
}
public void checkifTheSame()
{
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT * FROM inventory_supplier ";
query += "WHERE supplier_code = ?supplier_code AND supplier_name = ?supplier_name";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?supplier_name", txtBoxSupplierName.Text);
cmd.Parameters.AddWithValue("?supplier_code", txtBoxSupplierCode.Text);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
btnProceed.Enabled = true;
}
else
{
btnProceed.Enabled = false;
txtBoxQty.Clear();
}
}
catch (Exception)
{
btnProceed.Enabled = false;
txtBoxQty.Clear();
}
}
private void ConfigCheck()
{
frmLogin fl = new frmLogin();
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT * FROM system_config";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
if (rdr["tax_type"].ToString() == "V")
{
taxP = Convert.ToDouble("." + rdr["vat_rate"]);
taxDisplay = rdr["vat_rate"].ToString() + "%";
compName = rdr["company_name"].ToString();
address1 = rdr["company_address"].ToString();
address2 = rdr["company_address2"].ToString();
contact = rdr["company_contact"].ToString();
store_op = rdr["company_operator"].ToString();
permit_no = rdr["permit_no"].ToString();
TIN = rdr["tin_number"].ToString();
TaxT = rdr["tax_type"].ToString();
machine_no = rdr["machine_no"].ToString() + fl.tN;
all_items_tax = Convert.ToInt16(rdr["all_items_tax"]);
}
else
{
taxP = 0;
taxDisplay = "0%";
compName = rdr["company_name"].ToString();
address1 = rdr["company_address"].ToString();
address2 = rdr["company_address2"].ToString();
contact = rdr["company_contact"].ToString();
store_op = rdr["company_operator"].ToString();
permit_no = rdr["permit_no"].ToString();
TIN = rdr["tin_number"].ToString();
TaxT = rdr["tax_type"].ToString();
machine_no = rdr["machine_no"].ToString() + fl.tN;
all_items_tax = Convert.ToInt16(rdr["all_items_tax"]);
}
}
con.Close();
}
catch (Exception)
{
}
}
private void getDataTable()
{
frmLogin fs = new frmLogin();
dbcon = new Conf.dbs();
dataGridView1.DataSource = null;
dataGridView1.Rows.Clear();
dataGridView1.Refresh();
String connectionString = dbcon.getConnectionString();
String query = "SELECT pos_orno AS a, pos_tax_perc AS b, pos_tax_amt AS c, ";
query += "pos_total_amt AS d, pos_date AS e, pos_time AS f, pos_user AS g, pos_iswholesale AS h ";
query += "FROM pos_store ";
query += "WHERE pos_terminal = ?pos_terminal AND (pos_park = 1) AND (is_cancel = 0)";
using (MySqlConnection con = new MySqlConnection(connectionString))
{
using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, con))
{
try
{
DataTable dataTable = new DataTable();
adapter.SelectCommand.Parameters.AddWithValue("?pos_terminal", fs.tN);
adapter.Fill(dataTable);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
if (dataTable.Rows[i][7].ToString() == "1")
{
types = "Wholesale";
}
else
{
types = "Retail";
}
DateTime dates = DateTime.Parse(dataTable.Rows[i][5].ToString());
dataGridView1.Rows.Add(dataTable.Rows[i][0], dataTable.Rows[i][1], dataTable.Rows[i][2], dataTable.Rows[i][3], dataTable.Rows[i][4], dates.ToString("hh:mm:ss tt"), dataTable.Rows[i][6], types);
}
if (dataGridView1.Rows.Count != 0)
{
dataGridView1.FirstDisplayedScrollingRowIndex = dataGridView1.Rows.Count - 1;
}
}
catch (MySqlException ex)
{
MessageBox.Show("Error" + ex);
}
}
} // end using
}
public void checkifTheSameStockToQty()
{
Double stock_cost_price;
String UOM;
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT * FROM inventory_stocks ";
query += "WHERE stock_code = ?stock_code AND stock_name = ?stock_name";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?stock_code", txtBoxStockCode.Text);
cmd.Parameters.AddWithValue("?stock_name", txtBoxParticulars.Text);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
stock_cost_price = Convert.ToDouble(rdr["stock_cost_price"]);
UOM = rdr["stock_uom"].ToString();
txtBoxUnitPrice.Text = stock_cost_price.ToString("#,###,##0.00");
txtBoxUOM.Text = UOM;
txtBoxQty.ReadOnly = false;
}
else
{
txtBoxQty.ReadOnly = true;
txtBoxQty.Text = "0";
txtBoxUnitPrice.Text = "0.00";
txtBoxUOM.Clear();
}
con.Close();
}
catch (Exception)
{
txtBoxQty.ReadOnly = true;
txtBoxQty.Text = "0";
txtBoxUnitPrice.Text = "0.00";
txtBoxUOM.Clear();
}
}
private void ConfigCheck()
{
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT * FROM system_config";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
machine_no = rdr["machine_no"].ToString();
}
con.Close();
}
catch (Exception)
{
label11.Text = "Error!";
}
}
private void getSecurityFlags()
{
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
try
{
con.Open();
String query = "SELECT can_access AS a, has_sales AS b, has_customers AS c, has_inventory AS d, has_reports AS e, has_gc AS f, has_user_accounts AS g, has_conf AS h ";
query += "FROM user_access_restrictions ";
query += "WHERE user_id = ?grab";
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?grab", grabID);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
if (rdr["a"].ToString() == "1")
{
chkSystemAccess.Checked = true;
}
else
{
chkSystemAccess.Checked = false;
}
if (rdr["b"].ToString() == "1")
{
chkSales.Checked = true;
}
else
{
chkSales.Checked = false;
}
if (rdr["c"].ToString() == "1")
{
chkCustomers.Checked = true;
}
else
{
chkCustomers.Checked = false;
}
if (rdr["d"].ToString() == "1")
{
chkInventory.Checked = true;
}
else
{
chkInventory.Checked = false;
}
if (rdr["e"].ToString() == "1")
{
chkReports.Checked = true;
}
else
{
chkReports.Checked = false;
}
if (rdr["f"].ToString() == "1")
{
chkGiftCards.Checked = true;
}
else
{
chkGiftCards.Checked = false;
}
if (rdr["g"].ToString() == "1")
{
chkUserAccounts.Checked = true;
}
else
{
chkUserAccounts.Checked = false;
}
if (rdr["h"].ToString() == "1")
{
chkConfiguration.Checked = true;
}
else
{
chkConfiguration.Checked = false;
}
con.Close();
}
catch (Exception)
{
MessageBox.Show("Please Check your Database Server Connection", "Database Server Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.ExitThread();
}
}