public static UserInfo GetUserInfoByCard(string card)
{
string sql = string.Format("select * from Card_User where Card_User ='******'", card);
OleDbDataReader dr = Dbhelper.ExecuteReader(sql);
//List<UserInfo> list = new List<UserInfo>();
if (dr.Read())
{
UserInfo user = new UserInfo();
user.Cardid = int.Parse(dr[0].ToString());
user.Carduser = dr[1].ToString();
user.Cardpwd = dr[2].ToString();
user.Username = dr[3].ToString();
user.Userjob = dr[4].ToString();
user.Usertel = dr[5].ToString();
user.Useraddr = dr[6].ToString();
user.Userlove = dr[7].ToString();
user.Userphone = dr[8].ToString();
user.Usersex = dr[9].ToString();
user.Userbirth = dr[10].ToString();
user.Cardtime = dr[11].ToString();
user.Cardbak = dr[12].ToString();
user.Cardstata = dr[13].ToString();
user.Price = float.Parse(dr[14].ToString());
// list.Add(user);
return(user);
}
return(null);
}
public static List <employeeInfo> getallpd()
{
string sql = "select * from EmployeeInfo";
OleDbDataReader dr = Dbhelper.ExecuteReader(sql);
List <employeeInfo> list = new List <employeeInfo>();
if (dr != null)
{
while (dr.Read())
{
employeeInfo pd = new employeeInfo();
pd.Name = dr[1].ToString();
pd.Id = uint.Parse(dr[0].ToString());
pd.CreateTime = DateTime.Parse(dr[3].ToString());
pd.Type = dr[4].ToString();
if (pd.Id > 9)
{
list.Add(pd);
}
}
dr.Close();
}
return(list);
}
public static int AddPd(Pd_info pd)
{
string sql = string.Format("insert into pd_info (pd_name) values('{0}')", pd.Pdname);
int ret = Dbhelper.ExecuteNonQuery(sql);
return(ret);
}
public static List <UserInfo> GetAllInfo()
{
string sql = string.Format("select * from Card_User");
OleDbDataReader dr = Dbhelper.ExecuteReader(sql);
List <UserInfo> list = new List <UserInfo>();
while (dr.Read())
{
UserInfo user = new UserInfo();
user.Cardid = int.Parse(dr[0].ToString());
user.Carduser = dr[1].ToString();
user.Cardpwd = dr[2].ToString();
user.Username = dr[3].ToString();
user.Userjob = dr[4].ToString();
user.Usertel = dr[5].ToString();
user.Useraddr = dr[6].ToString();
user.Userlove = dr[7].ToString();
user.Userphone = dr[8].ToString();
user.Usersex = dr[9].ToString();
user.Userbirth = dr[10].ToString();
user.Cardtime = dr[11].ToString();
user.Cardbak = dr[12].ToString();
user.Cardstata = dr[13].ToString();
user.Price = float.Parse(dr[14].ToString());
list.Add(user);
}
return(list);
}
public static List <ProductInfo> GetAllProduct()
{
string sql = "select * from product_info";
OleDbDataReader dr = Dbhelper.ExecuteReader(sql);
List <ProductInfo> list = new List <ProductInfo>();
if (dr != null)
{
while (dr.Read())
{
ProductInfo product = new ProductInfo();
product.Productid = int.Parse(dr[0].ToString());
product.Productname = dr[1].ToString();
product.Productprice = float.Parse(dr[2].ToString());
product.Productnum = dr[3].ToString();
product.Producttype = dr[4].ToString();
product.Productpprice = float.Parse(dr[5].ToString());
product.Productaddr = dr[6].ToString();
product.Productpd = dr[7].ToString();
product.Productstata = dr[8].ToString();
product.Productpristata = dr[9].ToString();
product.Productdru = dr[10].ToString();
list.Add(product);
}
}
return(list);
}
public static void test()
{
string sql = string.Format("select * from admin_info");
OleDbDataReader dr = Dbhelper.ExecuteReader(sql);
while (dr.Read())
{
}
}
/// <summary>
/// 管理员登陆
/// </summary>
/// <param name="username">管理员用户名</param>
/// <param name="userpwd">管理员密码</param>
/// <returns>登陆管理员用户信</returns>
public static AdminInfo LoginAdmin(string username, string userpwd)
{
string sql = string.Format("select * from admin_info where admin_user='******' and admin_pwd='{1}'", username, userpwd);
OleDbDataReader dr = Dbhelper.ExecuteReader(sql);
AdminInfo admin = new AdminInfo();
if (dr.Read())
{
admin.AdminId = int.Parse(dr[0].ToString());
admin.Adminname = dr[1].ToString();
admin.Username = username;
admin.Userpwd = userpwd;
}
return(admin);
}
public static List <ScoInfo1> GetAllSco()
{
string sql = "select * form card_sco";
OleDbDataReader dr = Dbhelper.ExecuteReader(sql);
List <ScoInfo1> list = new List <ScoInfo1>();
while (dr.Read())
{
ScoInfo1 sco = new ScoInfo1();
sco.Id = int.Parse(dr[0].ToString());
sco.User = dr[1].ToString();
sco.Sco = dr[2].ToString();
sco.ScoHis = dr[3].ToString();
list.Add(sco);
}
return(list);
}
public static float GetAllBuyBkByUser(string username)
{
string sql = string.Format("select sum(buy_price) from buy_bk where buy_from='{0}'", username);
OleDbDataReader dr = Dbhelper.ExecuteReader(sql);
if (dr.Read())
{
try
{
return(float.Parse(dr[0].ToString()));
}
catch
{
return(0f);
}
}
dr.Close();
return(0f);
}
public static List <AdminInfo> GetALLAdmin()
{
string sql = "select * from admin_info";
OleDbDataReader dr = Dbhelper.ExecuteReader(sql);
List <AdminInfo> list = new List <AdminInfo>();
while (dr.Read())
{
AdminInfo admin = new AdminInfo();
admin.AdminId = int.Parse(dr[0].ToString());
admin.Adminname = dr[1].ToString();
admin.Username = dr[2].ToString();
admin.Userpwd = dr[3].ToString();
list.Add(admin);
}
dr.Close();
return(list);
}
public static List <BuyBk> GetAllBuyBkByTime(string begintime, string endtime)
{
string sql = string.Format("select * from buy_bk where buy_time between #{0}# and #{1}#", begintime, endtime);
OleDbDataReader dr = Dbhelper.ExecuteReader(sql);
List <BuyBk> list = new List <BuyBk>();
while (dr.Read())
{
BuyBk by = new BuyBk();
by.Buyid = int.Parse(dr[0].ToString());
by.Buyproduct = dr[1].ToString();
by.Buyprice = dr[2].ToString();
by.Buytime = dr[3].ToString();
by.Count = int.Parse(dr[4].ToString());
by.BuyFrom = dr[5].ToString();
list.Add(by);
}
dr.Close();
return(list);
}
public static List <Pd_info> getallpd()
{
string sql = "select * from pd_info";
OleDbDataReader dr = Dbhelper.ExecuteReader(sql);
List <Pd_info> list = new List <Pd_info>();
if (dr != null)
{
while (dr.Read())
{
Pd_info pd = new Pd_info();
pd.Pdname = dr[1].ToString();
pd.Pdid = int.Parse(dr[0].ToString());
list.Add(pd);
}
dr.Close();
}
return(list);
}
public static List <BuyBk> GetAllBuyBk()
{
string sql = "select * from buy_bk";
OleDbDataReader dr = Dbhelper.ExecuteReader(sql);
List <BuyBk> list = new List <BuyBk>();
while (dr.Read())
{
BuyBk by = new BuyBk();
by.Buyid = int.Parse(dr[0].ToString());
by.Buyproduct = dr[1].ToString();
by.Buyprice = dr[2].ToString();
by.Buytime = dr[3].ToString();
by.Count = int.Parse(dr[4].ToString());
by.BuyFrom = dr[5].ToString();
list.Add(by);
}
dr.Close();
return(list);
}
public static ScoInfo1 GetScorByUser(string username)
{
string sql = string.Format("select * from card_sco where sco_user = '******'", username);
OleDbDataReader dr = Dbhelper.ExecuteReader(sql);
if (dr.Read())
{
ScoInfo1 sco = new ScoInfo1();
sco.Id = int.Parse(dr[0].ToString());
sco.User = dr[1].ToString();
sco.ScoHis = dr[3].ToString();
sco.Sco = dr[2].ToString();
return(sco);
}
ScoInfo1 sco1 = new ScoInfo1();
sco1.User = username;
sco1.ScoHis = "0";
sco1.Sco = "0";
AddScor(sco1);
return(sco1);
}
public static List <ProductInfo> GetProductByNum(string productnum)
{
string sql = string.Format("select * from product_info where Product_num = '{0}'", productnum);
OleDbDataReader dr = Dbhelper.ExecuteReader(sql);
List <ProductInfo> list = new List <ProductInfo>();
while (dr.Read())
{
ProductInfo product = new ProductInfo();
product.Productid = int.Parse(dr[0].ToString());
product.Productname = dr[1].ToString();
product.Productprice = float.Parse(dr[2].ToString());
product.Productnum = dr[3].ToString();
product.Producttype = dr[4].ToString();
product.Productpprice = float.Parse(dr[5].ToString());
product.Productaddr = dr[6].ToString();
product.Productpd = dr[7].ToString();
product.Productstata = dr[8].ToString();
product.Productpristata = dr[9].ToString();
product.Productdru = dr[10].ToString();
list.Add(product);
}
return(list);
}
public static int DelAdmin(AdminInfo admin)
{
string sql = string.Format("delete from admin_info where admin_id = {0}", admin.AdminId);
return(Dbhelper.ExecuteNonQuery(sql));
}
public static int DelSco(ScoInfo1 sco)
{
string sql = string.Format("DELETE sci where sco_id = {0}", sco.Id);
return(Dbhelper.ExecuteNonQuery(sql));
}
/// <summary>
/// 修改密码
/// </summary>
/// <param name="admin">管理员信息</param>
/// <returns></returns>
public static int UpdataAdmin(AdminInfo admin)
{
string sql = string.Format("update admin_info set admin_pwd='{0}' where admin_id={1}", admin.Userpwd, admin.AdminId);
return(Dbhelper.ExecuteNonQuery(sql));
}
/// <summary>
/// 管理员登记
/// </summary>
/// <param name="admin">管理员信息</param>
/// <returns>操作</returns>
public static int AddAdmin(AdminInfo admin)
{
string sql = string.Format("insert into admin_info (admin_name,admin_user,admin_pwd) values('{0}','{1}','{2}')", admin.Adminname, admin.Username, admin.Userpwd);
return(Dbhelper.ExecuteNonQuery(sql));
}
public static int DlePd(Pd_info pd)
{
string sql = string.Format("delete from pd_info where pd_id={0}", pd.Pdid);
return(Dbhelper.ExecuteNonQuery(sql));
}
public static int Pay_pro(float price, string card)
{
string sql = string.Format("update card_user set card_price='{0}' where card_user = '******'", price, card);
return(Dbhelper.ExecuteNonQuery(sql));
}
public static int AddByBk(BuyBk bk)
{
string sql = string.Format("insert into buy_bk(Buy_product,Buy_Price,Buy_Time,Buy_Count,buy_from) values('{0}','{1}','{2}','{3}','{4}')", bk.Buyproduct, bk.Buyprice, bk.Buytime, bk.Count, bk.BuyFrom);
return(Dbhelper.ExecuteNonQuery(sql));
}
public static int UpdataSco(ScoInfo1 sco)
{
string sql = string.Format("update card_sco set sco_sco='{0}', sco_his='{1}' where sco_user='******'", sco.Sco, sco.ScoHis, sco.User);
return(Dbhelper.ExecuteNonQuery(sql));
}
public static int DelProduct(int productid)
{
string sql = string.Format("delete from product_info where product_id = {0}", productid);
return(Dbhelper.ExecuteNonQuery(sql));
}
public static int AddUser(UserInfo user)
{
string sql = string.Format("insert into card_user (Card_User,Card_Pwd,User_Name,User_Job,User_Tel,User_Addr,User_Love,User_Phone,User_Sex,User_birth,Card_time,Card_bak,Card_Stata) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')", user.Carduser, user.Cardpwd, user.Username, user.Userjob, user.Usertel, user.Useraddr, user.Userlove, user.Userphone, user.Usersex, user.Userbirth, user.Cardtime, user.Cardbak, user.Cardstata);
return(Dbhelper.ExecuteNonQuery(sql));
}
public static int UpdatePd(Pd_info pd)
{
string sql = string.Format("update pd_info set pd_name='{0}' where pd_id={1}", pd.Pdname, pd.Pdid);
return(Dbhelper.ExecuteNonQuery(sql));
}
public static int DelUser(int id)
{
string sql = string.Format("delete from card_user where card_id={0}", id);
return(Dbhelper.ExecuteNonQuery(sql));
}
public static int DlePd(employeeInfo pd)
{
string sql = string.Format("delete * from EmployeeInfo where Id={0}", pd.Id);
return(Dbhelper.ExecuteNonQuery(sql));
}
/// <summary>
/// 添加销售记录
/// </summary>
/// <param name="product"></param>
/// <returns></returns>
public static int AddProduct(BuyProduct product)
{
string sql = string.Format("insert into product_info (Product_name,Product_price,Product_num,Product_type,Product_pprice,Product_addr,Product_pd,Product_stata,Product_pristata,Product_dru,buy_time,buy_from) values('{0}',{1},'{2}','{3}',{4},'{5}','{6}','{7}','{8}','{9}','{10}','{11}')", product.Productname, product.Productprice, product.Productnum, product.Producttype, product.Productpprice, product.Productaddr, product.Productaddr, product.Productpd, product.Productstata, product.Productpristata, product.Productdru, product.BuyTime, product.BuyForm);
return(Dbhelper.ExecuteNonQuery(sql));
}
public static int AddScor(ScoInfo1 sco)
{
string sql = string.Format("insert into card_sco (sco_user,sco_sco,sco_his) values('{0}','{1}','{2}')", sco.User, sco.Sco, sco.ScoHis);
return(Dbhelper.ExecuteNonQuery(sql));
}