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 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 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 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 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 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 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 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 allQAID()
{
DataSet a = new DataSet();
string sql = "select distinct qualityID from qualitydetail_table ";
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 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 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 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 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 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 string InID()
{
DataSet a = new DataSet();
string sql = "select distinct GoodinID from stockdetail_table ";
a = Datameans.getDataSet(sql);
string newestInID = a.Tables[0].Rows[a.Tables[0].Rows.Count - 1][0].ToString();
//获取最近的入库单号
return(newestInID);
}
public static string MemID()
{
DataSet a = new DataSet();
string sql = "select distinct MemberID from member_table ";
a = Datameans.getDataSet(sql);
string newestMemID = a.Tables[0].Rows[a.Tables[0].Rows.Count - 1][0].ToString();
//获取最近的会员卡号
return(newestMemID);
}
public static string QAID()
{
DataSet a = new DataSet();
string sql = "select distinct qualityID from qualitydetail_table ";
a = Datameans.getDataSet(sql);
string newestQAID = a.Tables[0].Rows[a.Tables[0].Rows.Count - 1][0].ToString();
//获取最近的质检单号
return(newestQAID);
}
public static bool updateMemCostDetail(costInfo cost) // 商品销售，添加销售信息 ， 商品销售表
{
DataSet a = new DataSet();
String sql = "select distinct Goodsname,Suppliername from stockdetail_table where GoodsID = '" + cost.goodID + "'";
a = Datameans.getDataSet(sql);
String Goodname = a.Tables[0].Rows[0][0].ToString();
String suppliername = a.Tables[0].Rows[0][1].ToString();
String sql1 = "insert into selldetail_table(SellId,GoodsName,MemberID,Goodsnum,Sellprice,Totalprice,Selltime) values('"
+ cost.billNumber
+ "','"
+ Goodname
+ "','"
+ cost.memID
+ "','"
+ cost.goodNum
+ "','"
+ cost.cost
+ "','"
+ cost.total
+ "','"
+ cost.date + "')";
Datameans.getsqlcom(sql1);
//更新销售汇总表
string sql2 = "select distinct GoodsID from selltotal_table where GoodsID='"
+ cost.goodID + "' and Date = '" + cost.date.Split(' ')[0] + "'";
DataSet b = Datameans.getDataSet(sql2);
if (b.Tables[0].Rows.Count == 0)
{
string sql3 = "insert into selltotal_table(GoodsID,Goodname,suppliername,amount,Date) values('"
+ cost.goodID
+ "','"
+ Goodname
+ "','"
+ suppliername
+ "','"
+ cost.goodNum
+ "','"
+ cost.date.Split(' ')[0] + "')";
Datameans.getsqlcom(sql3);
}
//如果表中有ID，根据入库单商品ID在库存表里增加相应数量Amount
else
{
string sql4 = "update selltotal_table set amount = amount + '" + cost.goodNum + "' where GoodsID='"
+ cost.goodID + "'";
Datameans.getsqlcom(sql4);
}
return(true);
}
public static string getSoldBillNumber() //获取小票代号
{
DataSet a = new DataSet();
String sql = "select distinct SellId from selldetail_table ";
a = Datameans.getDataSet(sql);
string sellID = "0";
if (a.Tables[0].Rows.Count != 0)
{
sellID = a.Tables[0].Rows[a.Tables[0].Rows.Count - 1][0].ToString();
}
return(sellID);
}
public static string getARFIDInfo(string memID)
{
DataSet a = new DataSet();
String sql = "select distinct RFIDID from MemberRFID_table where MemberID = '" + memID + "'";
a = Datameans.getDataSet(sql);
if (a.Tables[0].Rows.Count > 0)
{
string RFIDID = a.Tables[0].Rows[0][0].ToString();
return(RFIDID);
}
return("");
//通过会员ID获取到有源RFID的编号
}
public static UserInfo getMemDetailByID(string ID)
{
DataSet a = new DataSet();
String sql = "select distinct MemberID, Name, email,balance from member_table where member_table.MemberID='" + ID + "'";
//通过会员ID得到会员的详细信息，以UserInfo类型返回
a = Datameans.getDataSet(sql);
String MemID = a.Tables[0].Rows[0][0].ToString();
String name = a.Tables[0].Rows[0][1].ToString();
String phonenum = a.Tables[0].Rows[0][2].ToString();
String balance = a.Tables[0].Rows[0][3].ToString();
ArrayList temp = new ArrayList();
temp.Add(MemID);
temp.Add(name);
temp.Add(phonenum);
temp.Add(balance);
UserInfo temp2 = new UserInfo(temp);
return(temp2);
}
// 下面是RFID 部分用到的DAL
public static goodDetail getDetailByID(string ID)
{
DataSet a = new DataSet();
String sql = "select distinct GoodsRFID.Good_ID, stockdetail_table.Goodsname, stockdetail_table.Goodsinprice from GoodsRFID,stockdetail_table where GoodsRFID.RFID_ID ='" + ID + "' and stockdetail_table.GoodsID = GoodsRFID.Good_ID";
//通过商品的RFID扫描得到商品详细信息：商品ID（非RFID），商品名称，商品单价，以goodDetail类型返回
a = Datameans.getDataSet(sql);
if (a.Tables[0].Rows.Count != 0)
{
string name = a.Tables[0].Rows[0][1].ToString();
string GoodID = a.Tables[0].Rows[0][0].ToString();
string price = a.Tables[0].Rows[0][2].ToString();
List <string> temp = new List <string>();
temp.Add(name);
temp.Add(price);
temp.Add(GoodID);
goodDetail temp2 = new goodDetail(temp[2], temp[0], Convert.ToDouble(temp[1]));
return(temp2);
}
return(null);
}