public static lockerInfo getLockInfo(int LockNumber)
{
DataSet a = new DataSet();
string sql = "select distinct * from LockBox_table where LockNumber = '" + LockNumber + "'";
a = Datameans.getDataSet(sql);
int Locknumber = System.Convert.ToInt32(a.Tables[0].Rows[0][0].ToString());
string memberID = a.Tables[0].Rows[0][1].ToString();
int islock = System.Convert.ToInt32(a.Tables[0].Rows[0][2].ToString());
//查看相应箱柜号使用者，返回memID
if (islock == 0)
{
lockerInfo temp = new lockerInfo();
temp.isLocked = false;
temp.LockNumber = Locknumber;
temp.userID = memberID;
return(temp);
}
else
{
lockerInfo temp = new lockerInfo();
temp.isLocked = true;
temp.LockNumber = Locknumber;
temp.userID = memberID;
return(temp);
}
}
// 对ComboBox控件进行数据绑定
public void cboxBind(string M_str_sqlstr, string M_str_table, string M_str_tbMember, ComboBox cbox)
{
DataSet myds = Datameans.getDataSet(M_str_sqlstr); // 根据SQL语句与表，获取Dataset对象
cbox.DataSource = myds.Tables[M_str_table]; // 动态的绑定数据
cbox.DisplayMember = M_str_tbMember; // 设置要显示的列
}
public static void PanDian(MODEL.Inventory inventory)
{
string sql = "update stockgoods_table set GoodNum = '" + inventory.Amount + "' where GoodsID = '" + inventory.GoodID + "'";
Datameans.getsqlcom(sql);
//根据inventory.GoodID改变inventory.Amount
}
public static void InputMember(Member m)
{ // id card 改为 phone number
String sql = "insert into member_table(MemberID,Name,Sex,Password,email,identitycard,balance,enrolldate,operateStaff) values('"
+ m.MemCardID
+ "','"
+ m.MemName
+ "','"
+ m.MemSex
+ "','"
+ m.MemPd
+ "','"
+ m.Memtel
+ "','"
+ m.IDCard
+ "','"
+ m.Memacount
+ "','"
+ m.Registtime
+ "','"
+ m.operateStaff + "')";
Datameans.getsqlcom(sql);
//插入m的八项内容
//m.MemCardID;
//m.MemName
// m.Memtel
//m.MemSex
//m.Memacount
//m.IDCard
//m.Registtime
//m.operateStaff
}
public static void Input(string MemID, string RFID)
{
string sql = "update MemberRFID_table set MemberID = '" + MemID + "' where RFIDID = '" + RFID + "'";
Datameans.getsqlcom(sql);
//在RFID表中输入会员号和rfid号
}
public static void ChangeNum(MODEL.InStockdetail instockdetail)
{
string sql = "select distinct GoodsID from stockgoods_table where GoodsID='"
+ instockdetail.GoodID + "'";
DataSet a = Datameans.getDataSet(sql);
if (a.Tables[0].Rows.Count == 0)
{
string sql1 = "insert into stockgoods_table(GoodsID,Storename,GoodNum) values('"
+ instockdetail.GoodID
+ "','"
+ instockdetail.Inventory
+ "','"
+ instockdetail.Amount + "')";
Datameans.getsqlcom(sql1);
}
//如果表中有ID，根据入库单商品ID在库存表里增加相应数量Amount
else
{
string sql2 = "update stockgoods_table set GoodNum = GoodNum + '" + instockdetail.Amount + "' where GoodsID='"
+ instockdetail.GoodID + "'";
Datameans.getsqlcom(sql2);
}
//如果表中没有该商品ID插入以下信息
//Datameans.getsqlcom(sql1);
}
public static void DeleteMem(String MemId)
{
String sql = "update member_table set is_use='停用' where MemberID='" + MemId + "'";
Datameans.getsqlcom(sql);
//依据MemId在数据库中删除会员信息
}
public static int StaffLogin(Staff s)
{
String sql = "select distinct * from staff_table where Username ='******'";
DataSet a = Datameans.getDataSet(sql);
if (a.Tables[0].Rows.Count == 0)
{
return(-1);
}
else
{
string pwd = a.Tables[0].Rows[0][2].ToString();
if (pwd == s.pd)
{
return(1);
}
else
{
return(0);
}
}
//在员工、管理员表中通过ID查找密码
//if不存在
//return -1;
//if存在但数据库无
//return 0;
//if存在且数据库中有
}
public static bool updateMemBalance(string memID, double bal)
{
String sql = "update member_table set balance = '" + bal + "' where MemberID='" + memID + "'";
Datameans.getsqlcom(sql);
return(true);
//更新会员卡内余额，通过memID进行，bal为新会员卡余额，成功返回true，失败返回false
}
public static DataSet QuerybyDate(string Date)
{
DataSet a = new DataSet();
String sql = "select distinct Goodname , GoodsID , amount ,Date from selltotal_table where Date = '" + Date + "'";
a = Datameans.getDataSet(sql);
return(a);
}
public static DataSet QuerybyOperator(string qaoperator)
{
String sql = "select distinct qualitymain_table.qualityID, qualitydetail_table.GoodsID, qualitydetail_table.Goodsname, qualitydetail_table.GoodsNum, qualitymain_table.Suppliername, qualitymain_table.checkdate, qualitymain_table.StaffID from qualitydetail_table ,qualitymain_table where qualitymain_table.StaffID ='" + qaoperator + "' and qualitymain_table.qualityID= qualitydetail_table.qualityID";
DataSet a = new DataSet();
//依据质检员ID从主、从表查找质检单号、商品号、商品名称、数量、供应商、时间、质检员
a = Datameans.getDataSet(sql);
return(a);
}
public static DataSet QueryMembyDate(String s)
{
DataSet a = new DataSet();
String sql = "select distinct * from member_table where enrolldate ='" + s + "'";
//依据日期查找会员信息
a = Datameans.getDataSet(sql);
return(a);
}
public static DataSet QuerybyOperator(string ID)
{
DataSet a = new DataSet();
String sql = "select distinct stockmain_table.GoodinID, stockdetail_table.GoodsID, stockdetail_table.Goodsname, stockdetail_table.GoodsinNum, stockdetail_table.Suppliername, stockmain_table.Indate, stockmain_table.Handlepeople from stockdetail_table,stockmain_table where stockmain_table.Handlepeople ='" + ID + "'and stockdetail_table.GoodinID = stockmain_table.GoodinID";
//通过入库员查询入库单号、商品号、商品名称、商品数量、供应商、日期、入库员
a = Datameans.getDataSet(sql);
return(a);
}
public static DataSet QuerybyGoodName(string goodName)
{
DataSet a = new DataSet();
String sql = "select distinct qualitymain_table.qualityID, qualitydetail_table.GoodsID, qualitydetail_table.Goodsname, qualitydetail_table.GoodsNum, qualitymain_table.Suppliername, qualitymain_table.checkdate, qualitymain_table.StaffID from qualitydetail_table ,qualitymain_table where qualitydetail_table.Goodsname ='" + goodName + "' and qualitymain_table.qualityID= qualitydetail_table.qualityID";
//依据商品名称从主、从表查找质检单号、商品号、商品名称、数量、供应商、时间、质检员
a = Datameans.getDataSet(sql);
return(a);
}
//会员消费记录系列
public static DataSet QueryCustomRecordbyId(string MemID)
{
DataSet a = new DataSet();
String sql = "select distinct selldetail_table.MemberID, member_table.Name, selldetail_table.Goodsname, selldetail_table.Goodsnum, selldetail_table.Sellprice, selldetail_table.Totalprice, selldetail_table.Selltime from selldetail_table, member_table where selldetail_table.GoodsName ='" + MemID + "'and selldetail_table.MemberID = member_table.MemberID";
//通过会员卡号查找会员卡号、姓名、商品类型、商品数量、单价、总金额、购买日期
a = Datameans.getDataSet(sql);
return(a);
}
public static DataSet QueryRechargebydate(string Date)
{
DataSet a = new DataSet();
String sql = "select distinct Payrecord_table.MemberID, member_table.Name, Payrecord_table.amount, Payrecord_table.Paydate, Payrecord_table.operateStaff from payrecord_table ,member_table where Payrecord_table.Paydate ='" + Date + "' and Payrecord_table.MemberID = member_table.MemberID";;
//在充值记录表中通过日期查找会员卡号、姓名、充值金额、充值前后金额、充值时间、操作员信息
a = Datameans.getDataSet(sql);
return(a);
}
public static DataSet QueryMembyId(String s)
{
DataSet a = new DataSet();
String sql = "select distinct * from member_table where identitycard ='" + s + "'";
//依据身份证号查找会员信息
a = Datameans.getDataSet(sql);
return(a);
}
public static DataSet QueryMembyTel(String s)
{
DataSet a = new DataSet();
String sql = "select distinct * from member_table where email ='" + s + "'";
//依据电话号查找会员信息
a = Datameans.getDataSet(sql);
return(a);
}
public static DataSet QueryMembyMemId(String s)
{
DataSet a = new DataSet();
String sql = "select distinct * from member_table where MemberID ='" + s + "'";
//依据会员卡号查找信息
a = Datameans.getDataSet(sql);
return(a);
}
public static DataSet QueryMembyName(String s)
{
DataSet a = new DataSet();
//"//依据姓名（即String s）查找顾客的会员卡号、姓名、电话号、性别、账户余额、身份证号、注册时间和注册人员");
String sql = "select distinct * from member_table where Name ='" + s + "'";
a = Datameans.getDataSet(sql);
return(a);
}
public static DataSet QuerybyGoodName(string GoodName)
{
DataSet a = new DataSet();
String sql = "select distinct Goodname , GoodsID , amount ,Date from selltotal_table where Goodname = '" + GoodName + "'";
a = Datameans.getDataSet(sql);
//根据商品名称查找售出产品的名称、数量、编号和日期；
return(a);
}
public static DataSet QuerybuSupplier(string supplier, string date)
{
DataSet a = new DataSet();
String sql = "select distinct Goodname , GoodsID , amount ,Date from selltotal_table where suppliername = '" + supplier + "' and Date = '" + date + "'";
a = Datameans.getDataSet(sql);
//根据supplier和date查找当天售出产品的编号、名称、日期和数量
return(a);
}
public static DataSet allQAID()
{
DataSet a = new DataSet();
string sql = "select distinct qualityID from qualitydetail_table ";
a = Datameans.getDataSet(sql);
//获取最近的质检单号
return(a);
}
public static DataSet QuerybyGoodID(string ID)
{
DataSet d = new DataSet();
string sql = "select distinct stockdetail_table.GoodsID, stockdetail_table.Goodsname, stockgoods_table.GoodNum from stockgoods_table, stockdetail_table where stockdetail_table.GoodsID = '" + ID + "' and stockdetail_table.GoodsID = stockgoods_table.GoodsID";
//依据商品编号查找商品的ID、名称和库存数量
d = Datameans.getDataSet(sql);
return(d);
}
public static void refreshRFIDInfo(string RFIDID, string goodID, int isSold) // 更新RFID表
{
string sql = "update GoodsRFID set IS_SOLD = 1 where RFID_ID ='" + RFIDID + "'";
Datameans.getsqlcom(sql);
string sql1 = "update stockgoods set GoodNum = GoodNum -1";
Datameans.getsqlcom(sql1);
//更新RFID表
}
public static void InPutGoodsRFID(string goodID, string RFID)
{
String sql = "insert into GoodsRFID(RFID_ID,Good_ID,IS_SOLD) values('"
+ RFID
+ "','"
+ goodID + "','" + "0')";
Datameans.getsqlcom(sql);
//插入goodsrfid表
}
public static DataSet Convert(string QAID)
{
DataSet a = new DataSet();
String sql = "select distinct qualitydetail_table.GoodsID , qualitydetail_table.Goodsname, qualitydetail_table.GoodsNum, qualitymain_table.Suppliername from qualitydetail_table,qualitymain_table where qualitydetail_table.qualityID = '" + QAID + "' and qualitydetail_table.qualityID = qualitymain_table.qualityID";
a = Datameans.getDataSet(sql);
return(a);
//根据QAID选择商品ID、商品名、商品数量、供应商
}
public static string StaffAuthority(string staffID)
{
String sql = "select distinct Postname from staffpost_table where Username ='******'"; //通过ID返回员工类型
DataSet a = Datameans.getDataSet(sql);
string post = a.Tables[0].Rows[0][0].ToString();
System.Console.WriteLine("y");
//string post = "信息员";
return(post);
}
public static int isSold(string RFIDID)
{
DataSet a = new DataSet();
string sql = "select distinct IS_SOLD from GoodsRFID where RFID_ID = '" + RFIDID + "'";
a = Datameans.getDataSet(sql);
int issold = System.Convert.ToInt32(a.Tables[0].Rows[0][0].ToString());
return(issold);
}
public static bool updateLockInfo(lockerInfo locker)
{
string sql = "update LockBox_table set MemberID = '" + locker.userID + "', is_locked='"
+ (locker.isLocked == true ? 1 : 0) + "' where LockNumber='"
+ locker.LockNumber + "'";
Datameans.getsqlcom(sql);
//更新箱柜信息
return(true);
}