public static void delete(string s)
{
connect constring = new connect();
string strcon = constring.Stringconnect;
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
SqlCommand cmd = new SqlCommand("DELETE FROM employee where ID='"+s+"' ", sqlcon);
cmd.ExecuteReader();
sqlcon.Close();
}
public static void update(string s1,string s2)
{
connect constring = new connect();
string strcon = constring.Stringconnect;
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
SqlCommand cmd = new SqlCommand("UPDATE goods SET name='" + s2.Split(',')[0] + "', price='" + s2.Split(',')[1] + "', barcode='" + s2.Split(',')[2] + "' WHERE ID='" + s1 + "'", sqlcon);
cmd.ExecuteReader();
sqlcon.Close();
}
public static void insert(string s)
{
connect constring = new connect();
string strcon = constring.Stringconnect;
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
SqlCommand cmd = new SqlCommand("insert into Sale values ('" + s.Split(',')[0] + "','" + s.Split(',')[1] + "','" + s.Split(',')[2] + "','" + s.Split(',')[3] + "','" + s.Split(',')[4] + "','" + s.Split(',')[5] + "','" + s.Split(',')[6] + "','" + s.Split(',')[7] + "','" + s.Split(',')[8] + "')", sqlcon);
cmd.ExecuteNonQuery();
sqlcon.Close();
}
public static void insert(string s)
{
connect constring = new connect();
string strcon = constring.Stringconnect;
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
SqlCommand cmd = new SqlCommand("insert into goods(name,price,barcode) values ('" + s.Split(',')[0] + "','" + s.Split(',')[1] + "','" + s.Split(',')[2] + "')", sqlcon);
cmd.ExecuteNonQuery();
sqlcon.Close();
}
public static void deletegoodstodb(string s,string ss)
{
connect constring = new connect();
string strcon = constring.Stringconnect;
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
string check = searchBalance(s.Split(',')[0]);
Database.sale.insert(ss+","+(Convert.ToInt32(check.Split(',')[1]) - Convert.ToInt32(s.Split(',')[1])).ToString());
SqlCommand cmd = new SqlCommand("UPDATE addgoods SET amount='" + (Convert.ToInt32(check.Split(',')[1]) - Convert.ToInt32(s.Split(',')[1])).ToString() + "' WHERE ID='" + check.Split(',')[0] + "'", sqlcon);
cmd.ExecuteNonQuery();
sqlcon.Close();
}
public static void update(string s,string id)
{
ArrayList str = new ArrayList();
for (int i = 0; i < 8; i++)
{
str.Add(s.Split(',')[i]);
}
connect constring = new connect();
string strcon = constring.Stringconnect;
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
SqlCommand cmd = new SqlCommand("UPDATE employee SET username='******', idcard='" + str[1] + "', name='" + str[2] + "', lname='" + str[3] + "', age='" + str[4] + "', sex='" + str[5] + "', address='" + str[6] + "', tell='" + str[7] + "'" + " WHERE Id='" + id + "'", sqlcon);
cmd.ExecuteReader();
sqlcon.Close();
}
public static void insert(string s)
{
ArrayList str = new ArrayList();
for (int i = 0; i < 9; i++)
{
str.Add(s.Split(',')[i]);
}
connect constring = new connect();
string strcon = constring.Stringconnect;
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
SqlCommand cmd = new SqlCommand("insert into employee(username,idcard,name,lname,age,sex,address,tell,password) values ('" + str[0] + "','" + str[1] + "','" + str[2] + "','" + str[3] + "','" + str[4] + "','" + str[5] + "','" + str[6] + "','" + str[7] + "','" + str[8] + "')",sqlcon);
cmd.ExecuteNonQuery();
sqlcon.Close();
}
public static ArrayList search(string s)
{
ArrayList ret = new ArrayList();
connect constring = new connect();
string strcon = constring.Stringconnect;
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
SqlCommand myCommand = new SqlCommand("select * from goods " + s , sqlcon);
SqlDataReader read = myCommand.ExecuteReader();
while (read.Read())
{
ret.Add(read["ID"].ToString() + "," + read["name"].ToString() + "," + read["price"].ToString() + "," + read["barcode"].ToString());
}
sqlcon.Close();
return ret;
}
public ArrayList GetUser()
{
ArrayList ret = new ArrayList();
connect constring = new connect();
string strcon = constring.Stringconnect;
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
SqlCommand myCommand = new SqlCommand("select * from employee",sqlcon);
SqlDataReader read = myCommand.ExecuteReader();
while (read.Read())
{
ret.Add(read["username"].ToString() + "," + read["password"].ToString());
}
sqlcon.Close();
return ret;
}
//standard data query
//username idcard name lname age sex address tell lname password\
public static ArrayList SearchUser(string select)
{
ArrayList ret = new ArrayList();
connect constring = new connect();
string strcon = constring.Stringconnect;
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
SqlCommand myCommand = new SqlCommand("select * from employee "+select, sqlcon);
SqlDataReader read = myCommand.ExecuteReader();
while (read.Read())
{
ret.Add(read["username"].ToString() + "," + read["idcard"].ToString() + "," + read["name"].ToString() + "," + read["lname"].ToString() + "," + read["age"].ToString() + "," + read["sex"].ToString() + "," + read["address"].ToString() + "," + read["tell"].ToString() + "," + read["ID"].ToString());
}
sqlcon.Close();
return ret;
}
public static ArrayList Sale(string select, string where)
{
ArrayList ret = new ArrayList();
connect constring = new connect();
string strcon = constring.Stringconnect;
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
SqlCommand myCommand = new SqlCommand(select, sqlcon);
SqlDataReader read = myCommand.ExecuteReader();
while (read.Read())
{
if (Convert.ToInt32(where.Split(',')[0]) == Convert.ToInt32(read["date"].ToString().Split('/')[1]) && Convert.ToInt32(where.Split(',')[1]) == Convert.ToInt32(read["date"].ToString().Split('/')[2]))
{
ret.Add(read["date"] + "," + read["name"] + "," + read["amout"] + "," + read["price"] + "," + read["balance"]);
}
}
sqlcon.Close();
return ret;
}
public static string searchBalance(string barcode)
{
string ret = "nohave";
connect constring = new connect();
string strcon = constring.Stringconnect;
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
SqlCommand myCommand = new SqlCommand("select * from addgoods ", sqlcon);
SqlDataReader read = myCommand.ExecuteReader();
while (read.Read())
{
if (read["barcode"].ToString().Equals(barcode))
{
ret = read["ID"].ToString();
ret += "," + read["amount"].ToString();
}
}
sqlcon.Close();
return ret;
}
public static void addgoodstodb(string s)
{
connect constring = new connect();
string strcon = constring.Stringconnect;
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
string check=searchBalance(s.Split(',')[1]);
SqlCommand cmd0 = new SqlCommand("insert into addorder values ('" + s.Split(',')[0] + "','" + s.Split(',')[1] + "','" + s.Split(',')[2] + "','" + s.Split(',')[3] + "','" + s.Split(',')[4] + "')", sqlcon);
cmd0.ExecuteNonQuery();
if (check.Equals("nohave"))
{
SqlCommand cmd = new SqlCommand("insert into addgoods values ('" + s.Split(',')[0] + "','" + s.Split(',')[1] + "','" + s.Split(',')[2] + "','" + s.Split(',')[3] + "','" + s.Split(',')[4] + "')", sqlcon);
cmd.ExecuteNonQuery();
}
else
{
SqlCommand cmd = new SqlCommand("UPDATE addgoods SET amount='" +(Convert.ToInt32(s.Split(',')[2]) + Convert.ToInt32(check.Split(',')[1])).ToString() + "' WHERE ID='" + check.Split(',')[0] + "'", sqlcon);
cmd.ExecuteNonQuery();
}
sqlcon.Close();
}
public static void addgoodstodb(string s)
{
connect constring = new connect();
string strcon = constring.Stringconnect;
SqlConnection sqlcon = new SqlConnection(strcon);
sqlcon.Open();
string check = searchBalance(s.Split(',')[1]);
SqlCommand cmd0 = new SqlCommand("insert into addorder values ('" + s.Split(',')[0] + "','" + s.Split(',')[1] + "','" + s.Split(',')[2] + "','" + s.Split(',')[3] + "','" + s.Split(',')[4] + "')", sqlcon);
cmd0.ExecuteNonQuery();
if (check.Equals("nohave"))
{
SqlCommand cmd = new SqlCommand("insert into addgoods values ('" + s.Split(',')[0] + "','" + s.Split(',')[1] + "','" + s.Split(',')[2] + "','" + s.Split(',')[3] + "','" + s.Split(',')[4] + "')", sqlcon);
cmd.ExecuteNonQuery();
}
else
{
SqlCommand cmd = new SqlCommand("UPDATE addgoods SET amount='" + (Convert.ToInt32(s.Split(',')[2]) + Convert.ToInt32(check.Split(',')[1])).ToString() + "' WHERE ID='" + check.Split(',')[0] + "'", sqlcon);
cmd.ExecuteNonQuery();
}
sqlcon.Close();
}