private bool checkZeroAmt(String crm_custcode) { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT crm_balance FROM crm_customer "; query += "WHERE crm_balance = 0"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { foundZero = true; } } finally { con.Close(); } return(foundZero); }
// public String katsSupplierCode(String supplier_name) { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT supplier_code AS glennisgay FROM inventory_supplier "; query += "WHERE supplier_name = ?supplier_name"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?supplier_name", supplier_name); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { supplier_code = rdr["glennisgay"].ToString(); sendSupplierCode(); } } finally { con.Close(); } return(supplier_code); }
public String getIndentifier(String physicalcode) { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT identify AS a FROM system_terminal "; query += "WHERE physicalcode = ?physicalcode"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?physicalcode", physicalcode); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { terminal = rdr["a"].ToString(); } else { terminal = null; } } finally { con.Close(); } return(terminal); }
public void UpdatePO(Int32 po_no, String supplier_code, String po_warehouse, String po_carrier, String po_remarks, String user_name) { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "UPDATE po_order SET po_warehouse = ?po_warehouse, po_carrier = ?po_carrier, po_remarks = ?po_remarks, user_name = ?user_name "; query += "WHERE po_no = ?po_no AND supplier_code = ?supplier_code"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?po_warehouse", po_warehouse); cmd.Parameters.AddWithValue("?po_carrier", po_carrier); cmd.Parameters.AddWithValue("?po_remarks", po_remarks); cmd.Parameters.AddWithValue("?user_name", user_name); cmd.Parameters.AddWithValue("?po_no", po_no); cmd.Parameters.AddWithValue("?supplier_code", supplier_code); cmd.ExecuteNonQuery(); cmd.Dispose(); } finally { con.Close(); } }
public void OrderPO(Int32 po_no, Int32 order_quantity, String order_uom, String order_suppliers_itemno, String order_description, Double order_unitcost, Double order_amount) { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "INSERT INTO po_order_list VALUES"; query += "(?po_no, ?order_quantity, ?order_uom, ?order_suppliers_itemno, ?order_description, ?order_unitcost, ?order_amount)"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?po_no", po_no); cmd.Parameters.AddWithValue("?order_quantity", order_quantity); cmd.Parameters.AddWithValue("?order_uom", order_uom); cmd.Parameters.AddWithValue("?order_suppliers_itemno", order_suppliers_itemno); cmd.Parameters.AddWithValue("?order_description", order_description); cmd.Parameters.AddWithValue("?order_unitcost", order_unitcost); cmd.Parameters.AddWithValue("?order_amount", order_amount); cmd.ExecuteNonQuery(); cmd.Dispose(); } finally { con.Close(); } }
public Int32 catchUserIDFromUserName(String user_name) { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT user_id FROM user_account "; query += "WHERE user_name = ?user_name"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?user_name", user_name); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { catchUserID = Convert.ToInt32(rdr["user_id"]); sendCatchUserID(); } else { catchUserID = 0; sendCatchUserID(); } } finally { con.Close(); } return(catchUserID); }
public Int32 postPONumber() { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT COUNT(*) AS gayminerva FROM po_order;"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { PONumber = Convert.ToInt32(rdr["gayminerva"]); sendPONumber(); } else { PONumber = 0; sendPONumber(); } } finally { con.Close(); } return(PONumber); }
// public String[] ReadData(String crm_custcode, String crm_companyname) { String[] cabilat = new String[2]; con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT crm_balance AS a, crm_creditlimit AS b "; query += "FROM crm_customer "; query += "WHERE crm_custcode = ?crm_custcode AND crm_companyname = ?crm_companyname"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?crm_custcode", crm_custcode); cmd.Parameters.AddWithValue("?crm_companyname", crm_companyname); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { cabilat[0] = rdr["a"].ToString(); cabilat[1] = rdr["b"].ToString(); } } catch (Exception ex) { Console.WriteLine(ex); } finally { con.Close(); } return(cabilat); }
public Int32 PositionCountSearch(String customer) { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String sql = "SELECT COUNT(*) AS a "; sql += "FROM crm_customer "; sql += "WHERE crm_companyname LIKE ?customer ORDER BY crm_companyname"; Int32 count = 0; try { con.Open(); MySqlCommand cmd = new MySqlCommand(sql, con); cmd.Parameters.AddWithValue("?customer", customer + "%"); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { count = Convert.ToInt32(rdr["a"].ToString()); } } catch (Exception ex) { Console.WriteLine(" Error :: ERROR " + ex); } finally { con.Close(); } return(count); }
public bool checkCustcode(String custcode) { bool found = false; con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT crm_custcode FROM crm_customer "; query += "WHERE crm_custcode = ?a"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?a", custcode); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { found = true; } } finally { con.Close(); } return(found); }
// public Int32 countSummary() { Int32 ihap = 0; con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT COUNT(*) AS a FROM crm_customer "; query += "WHERE has_summary = 1 ORDER BY crm_companyname"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { ihap = Convert.ToInt32(rdr["a"]); } } finally { con.Close(); } return(ihap); }
public String lname() { String a = ""; con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT last_name AS a FROM user_information "; query += "WHERE user_id = ?user_id"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?user_id", this.getUserIdFromDB()); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { a = rdr["a"].ToString(); } } finally { con.Close(); } return(a); }
public Int32 getUserIdFromDB() { String user = frmLogin.User.user_name; Int32 no = 0; con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT user_id AS a FROM user_account "; query += "WHERE user_name = ?user_name"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?user_name", user); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { no = Convert.ToInt32(rdr["a"]); } } finally { con.Close(); } return(no); }
public Boolean checkVat() { Boolean vat = false; con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT tax_type 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") { vat = true; } else { vat = false; } } } finally { con.Close(); } return(vat); }
public void Update(Int32 user_id, String user_name, String first_name, String middle_name, String last_name) { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query1 = "UPDATE user_account SET user_name = ?user_name "; query1 += "WHERE user_id = ?user_id"; String query2 = "UPDATE user_information SET first_name = ?first_name, middle_name = ?middle_name, last_name = ?last_name "; query2 += "WHERE user_id = ?user_id"; try { con.Open(); MySqlCommand cmd1 = new MySqlCommand(query1, con); MySqlCommand cmd2 = new MySqlCommand(query2, con); cmd1.Parameters.AddWithValue("?user_name", user_name); cmd1.Parameters.AddWithValue("?user_id", user_id); cmd2.Parameters.AddWithValue("?first_name", first_name); cmd2.Parameters.AddWithValue("?middle_name", middle_name); cmd2.Parameters.AddWithValue("?last_name", last_name); cmd2.Parameters.AddWithValue("?user_id", user_id); cmd1.ExecuteNonQuery(); cmd1.Dispose(); cmd2.ExecuteNonQuery(); cmd2.Dispose(); } finally { con.Close(); } }
public Int32 PositionCountCRMAll(String crm_custcode) { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String sql = "SELECT COUNT(*) AS a FROM crm_basic WHERE (crm_custcode = ?a) ORDER BY basic_id"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(sql, con); cmd.Parameters.AddWithValue("?a", crm_custcode); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { count3 = Convert.ToInt32(rdr["a"].ToString()); } } catch (Exception ex) { Console.WriteLine(" Error :: ERROR " + ex); } finally { con.Close(); } return(count3); }
public void UpdateRestrictions(Int32 can_access, Int32 has_sales, Int32 has_order, Int32 has_customers, Int32 has_inventory, Int32 has_reports, Int32 has_gc, Int32 has_user_accounts, Int32 has_conf, Int32 user_id) { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "UPDATE user_access_restrictions SET "; query += "can_access = ?can_access, has_sales = ?has_sales, has_order = ?has_order, has_customers = ?has_customers, has_inventory = ?has_inventory, has_reports = ?has_reports, has_gc = ?has_gc, has_user_accounts = ?has_user_accounts, has_conf = ?has_conf "; query += "WHERE user_id = ?user_id"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?can_access", can_access); cmd.Parameters.AddWithValue("?has_sales", has_sales); cmd.Parameters.AddWithValue("?has_order", has_order); cmd.Parameters.AddWithValue("?has_customers", has_customers); cmd.Parameters.AddWithValue("?has_inventory", has_inventory); cmd.Parameters.AddWithValue("?has_reports", has_reports); cmd.Parameters.AddWithValue("?has_gc", has_gc); cmd.Parameters.AddWithValue("?has_user_accounts", has_user_accounts); cmd.Parameters.AddWithValue("?has_conf", has_conf); cmd.Parameters.AddWithValue("?user_id", user_id); cmd.ExecuteNonQuery(); cmd.Dispose(); } finally { con.Close(); } }
public String DisplayCustC(String crm_companyname) { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT crm_custcode FROM crm_customer WHERE crm_companyname = ?a AND is_suspended = 0"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?a", crm_companyname); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { custC = rdr["crm_custcode"].ToString(); } } finally { con.Close(); } return(custC); }
public Double katsStockPriceN(String stock_name, String supplier_code) { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT stock_cost_price AS glennisgay FROM inventory_stocks "; query += "WHERE stock_code = ?stock_name AND supplier_code = ?supplier_code"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?stock_name", stock_name); cmd.Parameters.AddWithValue("?supplier_code", supplier_code); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { stock_cost_price = Convert.ToDouble(rdr["glennisgay"]); sendStockName(); } } finally { con.Close(); } return(stock_cost_price); }
public bool correct(String crm_custcode, String crm_companyname) { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT * FROM crm_customer "; query += "WHERE (crm_custcode = ?a) AND (crm_companyname = ?b)"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?a", crm_custcode); cmd.Parameters.AddWithValue("?b", crm_companyname); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { korek = true; } } finally { con.Close(); } return(korek); }
public void IssuePO(Int32 po_no, String po_date, String po_time, String supplier_code, String po_remarks, String user_name) { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "INSERT INTO po_order (po_no, po_date, po_time, supplier_code, po_remarks, user_name) VALUES"; query += "(?po_no, ?po_date, ?po_time, ?supplier_code, ?po_remarks, ?user_name)"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?po_no", po_no); cmd.Parameters.AddWithValue("?po_date", po_date); cmd.Parameters.AddWithValue("?po_time", po_time); cmd.Parameters.AddWithValue("?po_remarks", po_remarks); cmd.Parameters.AddWithValue("?user_name", user_name); cmd.Parameters.AddWithValue("?supplier_code", supplier_code); cmd.ExecuteNonQuery(); cmd.Dispose(); } finally { con.Close(); } }
// public Double grabInterestRate(String custcode) { Double IR = 0; con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT crm_interest AS a FROM crm_customer "; query += "WHERE crm_custcode = ?crm_custcode"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?crm_custcode", custcode); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { IR = Convert.ToDouble(rdr["a"]); } } catch (Exception ex) { Console.WriteLine(ex.ToString()); } finally { con.Close(); } return(IR); }
public void DeletePO(Int32 po_no, String supplier_code) { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "DELETE FROM po_order WHERE po_no = ?po_no AND supplier_code = ?supplier_code"; String query1 = "DELETE FROM po_order_list WHERE po_no = ?po_no"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); MySqlCommand cmd1 = new MySqlCommand(query1, con); cmd.Parameters.AddWithValue("?po_no", po_no); cmd.Parameters.AddWithValue("?supplier_code", supplier_code); cmd1.Parameters.AddWithValue("?po_no", po_no); cmd.ExecuteNonQuery(); cmd1.ExecuteNonQuery(); cmd.Dispose(); cmd1.Dispose(); } finally { con.Close(); } }
public void PingIR(Double crm_balance, String custcode) { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "UPDATE crm_customer SET crm_balance = ?a "; query += "WHERE crm_custcode = ?crm_custcode"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?a", crm_balance); cmd.Parameters.AddWithValue("?crm_custcode", custcode); cmd.ExecuteNonQuery(); cmd.Dispose(); } catch (Exception ex) { Console.WriteLine(ex); } finally { con.Close(); } }
// public void UpdateOrderPO(Int32 po_no, Int32 order_quantity, String order_uom, String order_suppliers_itemno, String old_stock_code, String order_description, Double order_unitcost, Double order_amount) { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "UPDATE po_order_list SET "; query += "order_quantity = ?order_quantity, order_uom = ?order_uom, order_suppliers_itemno = ?order_suppliers_itemno, order_description = ?order_description, order_unitcost = ?order_unitcost, order_amount = ?order_amount "; query += "WHERE po_no = ?po_no AND order_suppliers_itemno = ?old_stock_code"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?po_no", po_no); cmd.Parameters.AddWithValue("?order_quantity", order_quantity); cmd.Parameters.AddWithValue("?order_uom", order_uom); cmd.Parameters.AddWithValue("?order_suppliers_itemno", order_suppliers_itemno); cmd.Parameters.AddWithValue("?order_description", order_description); cmd.Parameters.AddWithValue("?order_unitcost", order_unitcost); cmd.Parameters.AddWithValue("?order_amount", order_amount); cmd.Parameters.AddWithValue("?old_stock_code", old_stock_code); cmd.ExecuteNonQuery(); cmd.Dispose(); } finally { con.Close(); } }
public Int32 PositionCountFilter(Double crm_balance) { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String sql = "SELECT COUNT(*) AS a FROM crm_customer WHERE crm_balance = ?a ORDER BY crm_id"; Int32 count = 0; try { con.Open(); MySqlCommand cmd = new MySqlCommand(sql, con); cmd.Parameters.AddWithValue("?a", crm_balance); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { count = Convert.ToInt32(rdr["a"].ToString()); } } catch (Exception ex) { Console.WriteLine(" Error :: ERROR " + ex); } finally { con.Close(); } return(count); }
public bool hasUserConf() { con = new MySqlConnection(); dbcon = new Conf.dbs(); bool hasConf = true; String getUserID = getUserIDfromDB(); con.Close(); con.ConnectionString = dbcon.getConnectionString(); String sql = "SELECT has_conf FROM user_access_restrictions WHERE user_id = ?user_id"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(sql, con); cmd.Parameters.Add("?user_id", MySqlDbType.Int32, 3).Value = getUserIDfromDB(); cmd.CommandType = CommandType.Text; MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { if (rdr["has_conf"].ToString() == "0") { hasConf = false; } } } finally { con.Close(); con.Dispose(); } return(hasConf); }
public Int32 postUserID() { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT COUNT(*) AS get FROM user_account;"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { UserID = Convert.ToInt32(rdr["get"]); sendUserID(); } else { UserID = 0; sendUserID(); } } finally { con.Close(); } return(UserID); }
private String getUserIDfromDB() { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String sql = "SELECT user_id FROM user_account WHERE user_name = ?user_name"; String UserID = ""; try { con.Open(); MySqlCommand cmd = new MySqlCommand(sql, con); cmd.Parameters.Add("?user_name", MySqlDbType.VarChar, 30).Value = user_name; cmd.CommandType = CommandType.Text; MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { UserID = rdr["user_id"].ToString(); } } finally { con.Close(); con.Dispose(); } return(UserID); }
private void triggerAddDD(String crm_custcode) { Int32 terms = this.getTerms(crm_custcode); String DueDate = this.getDD(crm_custcode); String finale = ""; con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "UPDATE crm_customer SET crm_duedate = ?duedate "; query += "WHERE crm_custcode = ?crm_custcode"; finale = Convert.ToDateTime(DueDate).AddDays(terms).ToString("yyyy-MM-dd"); try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?duedate", finale); cmd.Parameters.AddWithValue("?crm_custcode", crm_custcode); cmd.ExecuteNonQuery(); cmd.Dispose(); } finally { con.Close(); } }