//Warehouse Code
public String grabWarehouseCode(String warehouse_name)
{
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT warehouse_code FROM inventory_warehouse ";
query += "WHERE warehouse_name = ?warehouse_name";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?warehouse_name", warehouse_name);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
WarehouseCode = rdr["warehouse_code"].ToString();
sendWarehouseCode();
}
else
{
WarehouseCode = "";
sendWarehouseCode();
}
}
finally
{
con.Close();
}
return(SupplierCode);
}
//
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 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 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 Int32 PositionCount()
{
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String sql = "SELECT COUNT(*) AS a FROM crm_customer ORDER BY crm_id";
Int32 count = 0;
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(sql, con);
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);
}
private String getDD(String crm_custcode)
{
String duedate = "";
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT crm_duedate 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", crm_custcode);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
duedate = rdr["a"].ToString();
}
}
finally
{
con.Close();
}
return(duedate);
}
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 pushEan(String kit_name)
{
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT item_ean AS ean FROM inventory_items ";
query += "WHERE kit_name = ?kit_name";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?kit_name", kit_name);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
itemEAN = rdr["ean"].ToString();
}
}
finally
{
con.Close();
}
return(itemEAN);
}
public Int32 askQuantity(String ean)
{
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT item_quantity AS a FROM inventory_items ";
query += "WHERE item_ean = ?item_ean";
try
{
qty = 0;
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?item_ean", ean);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
qty = Convert.ToInt32(rdr["a"]);
}
}
finally
{
con.Close();
}
return(qty);
}
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 String pushKitName(String item_ean)
{
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT kit_name AS a FROM inventory_items ";
query += "WHERE item_ean = ?item_ean";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?item_ean", item_ean);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
kitName = rdr["a"].ToString();
}
}
finally
{
con.Close();
}
return(kitName);
}
public void UpdateRestrictions(Int32 can_access, Int32 has_sales, 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_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_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 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 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);
}
public Double askTax()
{
Double comp = 0;
Double tax = 0;
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT vat_rate FROM system_config";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
comp = Convert.ToDouble(rdr["vat_rate"]);
tax = comp / 100;
}
}
finally
{
con.Close();
}
return(tax);
}
public Int32 ItemCount()
{
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String sql = "SELECT COUNT(*) AS a FROM inventory_items INNER JOIN inventory_stocks ON inventory_items.stock_code = inventory_stocks.stock_code ";
sql += "WHERE (inventory_items.is_kit = 0)";
Int32 count = 0;
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(sql, con);
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);
}
private Int32 getTerms(String crm_custcode)
{
Int32 days = 0;
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT crm_netdays AS x FROM crm_customer ";
query += "WHERE crm_custcode = ?crm_custcode";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?crm_custcode", crm_custcode);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
days = Convert.ToInt32(rdr["x"]);
}
}
finally
{
con.Close();
}
return(days);
}
private void ComputeTotalAmtStocks(Double retail_price, String stock_code)
{
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT stock_quantity AS gay FROM inventory_stocks ";
query += "WHERE stock_code = ?stock_code";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?stock_code", stock_code);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
stockQTY = Convert.ToInt32(rdr["gay"]);
}
stocktotalAmt = retail_price * stockQTY;
}
finally
{
con.Close();
}
}
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();
}
}
private void ComputeTotalAmtQty(String stock_code)
{
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT stock_quantity AS a, stock_selling_price 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", stock_code);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
qty = Convert.ToInt32(rdr["a"]);
price = Convert.ToDouble(rdr["b"]);
}
finale = qty * price;
}
finally
{
con.Close();
}
}
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 String catchUserID()
{
String userName = frmLogin.User.user_name;
dbcon = new Conf.dbs();
con = new MySqlConnection(dbcon.getConnectionString());
String query = "SELECT user_id FROM user_account ";
query += "WHERE user_name = ?user_name";
String catchID = "";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.Add("?user_name", MySqlDbType.VarChar, 48).Value = userName;
cmd.CommandType = CommandType.Text;
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
catchID = rdr["user_id"].ToString();
}
}
finally
{
con.Close();
}
return(catchID);
}
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 bool expiry(String gc_cardno)
{
bool found = false;
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT * FROM gc_core ";
query += "WHERE (gc_cardno = ?gc_cardno) AND (gc_validuntil <= NOW())";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?gc_cardno", gc_cardno);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
found = true;
}
else
{
found = false;
}
}
finally
{
con.Close();
}
return(found);
}
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 Double catchA(String gc_cardno)
{
Double amount = 0;
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT gc_amount AS a FROM gc_core ";
query += "WHERE gc_cardno = ?gc_cardno";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?gc_cardno", gc_cardno);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
amount = Convert.ToDouble(rdr["a"]);
}
}
finally
{
con.Close();
}
return(amount);
}
//
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 String[,] Read()
{
Int32 count = this.PositionCount();
String[,] xxx = new String[4, count];
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT * FROM gc_core";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataReader rdr = cmd.ExecuteReader();
int counts = 0;
while (rdr.Read())
{
xxx[0, counts] = rdr["gc_cardno"].ToString();
xxx[1, counts] = rdr["gc_amount"].ToString();
xxx[2, counts] = rdr["gc_holder"].ToString();
xxx[3, counts] = rdr["gc_validuntil"].ToString();
counts++;
}
}
catch (Exception ex)
{
Console.WriteLine(" Err :: ERROR " + ex);
}
finally
{
con.Close();
}
return(xxx);
}
public Int32 PositionCountCRM(String crm_custcode, DateTime filterToday)
{
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String sql = "SELECT COUNT(*) AS a FROM crm_basic WHERE crm_custcode = ?a AND crm_paydate = ?b ORDER BY basic_id";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("?a", crm_custcode);
cmd.Parameters.AddWithValue("?b", filterToday);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
count1 = Convert.ToInt32(rdr["a"].ToString());
}
}
catch (Exception ex)
{
Console.WriteLine(" Error :: ERROR " + ex);
}
finally
{
con.Close();
}
return(count1);
}
//Cat
public String grabCategoryCode(String cat_description)
{
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT cat_code FROM inventory_category ";
query += "WHERE cat_description = ?cat_description";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?cat_description", cat_description);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
CategoryCode = rdr["cat_code"].ToString();
sendCategoryCode();
}
else
{
CategoryCode = "";
sendCategoryCode();
}
}
finally
{
con.Close();
}
return(SupplierCode);
}