public static List <VipsMDL> GetVips(int VipID)
{
string sql = "select * from Vips v left join dbo.VIPGrade g on v.GradeID=g.VGID where 1=1";
List <SqlParameter> sp = new List <SqlParameter>();
if (VipID != 0)
{
sql += " and VipID=@VipID";
sp.Add(new SqlParameter("@VipID", VipID));
}
SqlDataReader dr = DBhelper.MySqlDataReader(sql, sp.ToArray());
List <VipsMDL> list = new List <VipsMDL>();
while (dr.Read())
{
VipsMDL v = new VipsMDL();
v.VipID = Convert.ToInt32(dr[0]);
v.VipName = dr[1].ToString();
v.VipSex = dr[2].ToString();
v.GradeID = Convert.ToInt32(dr[3]);
v.VipTel = dr[4].ToString();
v.VipStartDate = dr[5].ToString();
v.VipEndDate = dr[6].ToString();
//v.VipType = Convert.ToInt32(dr[7]);
if (dr[8].ToString() == "")
{
v.VGName = "其他会员";
}
else
{
v.VGName = dr[8].ToString();
}
if (dr[9].ToString() == "")
{
v.VGDiscount = 1.0;
}
else
{
v.VGDiscount = Convert.ToDouble(dr[9]);
}
list.Add(v);
}
dr.Close();
return(list);
}
public static int GetMaxID()
{
string sql = "select max(VipID) from dbo.Vips";
object o = DBhelper.MyExecuteScalar(sql, null);
if (!(o is DBNull))
{
return(Convert.ToInt32(o));
}
else
{
return(0);
}
}
public static int AddVip(VipsMDL v)
{
string sql = "insert into Vips values(@VipName,@VipSex,@GradeID,@VipTel,GETDATE(),@VipEndDate)";
SqlParameter[] sp =
{
new SqlParameter("@VipName", v.VipName),
new SqlParameter("@VipSex", v.VipSex),
new SqlParameter("@GradeID", v.GradeID),
new SqlParameter("@VipTel", v.VipTel),
new SqlParameter("@VipEndDate", v.VipEndDate),
};
return(DBhelper.MyExecuteNonQuery(sql, sp));
}
public static int RemoveConsumerDetails(int PTID)
{
string sql = @"delete from ConsumerDetail
where ProdcutID=@ProdcutID
and CDID=(select MAX(CDID)
from dbo.ConsumerDetail
where ProdcutID=@ProdcutID)";
SqlParameter[] sp =
{
new SqlParameter("@ProdcutID", PTID)
};
return(DBhelper.MyExecuteNonQuery(sql, sp));
}
public static int UPCB(ConsumerBills c)
{
string sql = "update dbo.ConsumerBill set CBClose=1,VipID=@VipID,CBDiscount=@CBDiscount,CBEndDate=GETDATE(),AdminID=@AdminID,CBSale=@CBSale where CBID=@CBID";
SqlParameter[] sp =
{
new SqlParameter("@CBID", c.CBID),
new SqlParameter("@VipID", c.VipID),
new SqlParameter("@CBDiscount", c.CBDiscount),
new SqlParameter("@AdminID", c.AdminID),
new SqlParameter("@CBSale", c.CBSale),
};
return(DBhelper.MyExecuteNonQuery(sql, sp));
}
public bool update(string a, string b)
{
string s = "update test set sx ='" + a + "'where id ='" + b + "'";
int rows = DBhelper.ExecuteNonQuery(s);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool delete(int a)
{
string s2 = "delete from role_res where useId = '" + a + "'";
int rows = DBhelper.ExecuteNonQuery(s2);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool delete3(string str, string str2, string str3, string str4)
{
string s = "DELETE from test WHERE parentId2 = (select * from ((SELECT parentId2 FROM test where zId= '" + str + "' and sx = '" + str2 + "'and pId ='" + str3 + "' and bh='" + str4 + "')a))";
int rows = DBhelper.ExecuteNonQuery(s);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool delete4(string str)
{
string s = "DELETE from test WHERE parentId2 = (SELECT * from ((SELECT parentId2 FROM test where zId= 9 and sx = '" + str + "') a))";
int rows = DBhelper.ExecuteNonQuery(s);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool update112(string a, string b, string c)
{
string s = "update zl set iszl ='" + a + "'where lzId='" + b + "' and bh ='" + c + "'";
int rows = DBhelper.ExecuteNonQuery(s);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public Dictionary <DateTime, int> getDaySet(int year, int month)
{
Dictionary <DateTime, int> asetDic = new Dictionary <DateTime, int>();
string sql = " select * from AttendanceSetting where YEAR(Date) =" + year + " and MONTH(Date) =" + month;
DataTable dt = DBhelper.Select(sql);
foreach (DataRow dr in dt.Rows)
{
DateTime now = Convert.ToDateTime(dr["Date"]);
int statu = Convert.ToInt32(dr["Status"]);
asetDic[now] = statu;
}
return(asetDic);
}
public bool delete2(string str)
{
string s5 = "delete from processing where pId = '" + str + "'";
int rows = DBhelper.ExecuteNonQuery(s5);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool update2(string a, DateTime b, string c, string d)
{
string s7 = "UPDATE user set " + a + " ='" + b + "'where useId='" + c + "'and username='******'";
int rows = DBhelper.ExecuteNonQuery(s7);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool delete2(string a, string str, string str2, string str3)
{
string s = "DELETE from test WHERE parentId2 IN(select * from((SELECT DISTINCT parentId2 FROM test WHERE parentId = (SELECT id FROM test where zId= '" + a + "' and sx = '" + str + "' AND pId ='" + str2 + "' AND bh ='" + str3 + "'))a))";
int rows = DBhelper.ExecuteNonQuery(s);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool update2(string a, string b, string c)
{
string s7 = "update processing set " + a + "='" + b + "'where pId = '" + c + "'";
int rows = DBhelper.ExecuteNonQuery(s7);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool delete(string str, string str2, string str3)
{
string s = "delete from test WHERE parentId2 in (select * from ((select parentId2 from test where parentId in(select id from test where zId =12 and parentId=(select id from test where zId =3 and sx='" + str + "'and pId ='" + str2 + "' and bh='" + str3 + "' )))a));";
int rows = DBhelper.ExecuteNonQuery(s);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool update2(string a, string b, string c, string d)
{
string s = "update test set sx ='" + a + "'where pId='" + b + "' and zId ='" + c + "' and parentId2='" + d + "'";
int rows = DBhelper.ExecuteNonQuery(s);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool delete(string str)
{
string s = "DELETE from user WHERE useId='" + str + "'";
int rows = DBhelper.ExecuteNonQuery(s);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool delete(int a, DateTime b)
{
string s2 = "delete from attendance where useId='" + a + "' and date='" + b + "'";
int rows = DBhelper.ExecuteNonQuery(s2);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool update5(string a, string b, int c, DateTime d)
{
string s7 = "update attendance set " + a + "='" + b + "'where useId = '" + c + "'and date='" + d + "'";
int rows = DBhelper.ExecuteNonQuery(s7);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool insert5(DateTime b, DateTime c, DateTime d, DateTime e)
{
string s = "insert into aspunch(smw, sme,saw,sae) values('" + b + "','" + c + "','" + d + "','" + e + "')";
int rows = DBhelper.ExecuteNonQuery(s);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool insert(int a, string b, DateTime c, string d, string e, string f, DateTime g, string h, string i, DateTime j, string k, string l, string m, string n)
{
string s = "insert into user(useId,username,brithday,sex,Origin,marriage,employdate,historyrole,nowrole,workdate,telephone,mail,wangdian,card) values('" + a + "','" + b + "','" + c + "','" + d + "','" + e + "','" + f + "','" + g + "','" + h + "','" + i + "','" + j + "','" + k + "','" + l + "','" + m + "','" + n + "')";
int rows = DBhelper.ExecuteNonQuery(s);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool update(string a)
{
string s = "UPDATE test SET test.zId = (SELECT zd FROM ziduan WHERE ziduan.zId = test.zId ) where pc = '" + a + "'";
int rows = DBhelper.ExecuteNonQuery(s);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
//设置罚款进行罚款处理
public bool insert6(int a, int b, int c, int d)
{
string s = "insert into apmoney(p1,p2,p3,cb)values('" + a + "','" + b + "','" + c + "','" + d + "')";
int rows = DBhelper.ExecuteNonQuery(s);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool insert4(int a, string b, DateTime c, DateTime d)
{
string s = "insert into attendance(useId,username,date,afternoonend)values('" + a + "','" + b + "','" + c + "','" + d + "')";
int rows = DBhelper.ExecuteNonQuery(s);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool update4(int a, int b, string c, int d, DateTime e)
{
string s = "UPDATE attendance set mealsupport = '" + a + "',punish='" + b + "',allwork='" + c + "'WHERE useId='" + d + "' and date='" + e + "'";
int rows = DBhelper.ExecuteNonQuery(s);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool insert(string str, string b, string c)
{
string s = "insert into zl(lzId,bh,iszl) values('" + str + "','" + b + "','" + c + "')";
int rows = DBhelper.ExecuteNonQuery(s);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
//更新档次
public bool update3(string a, int b, int c)
{
string s = "UPDATE attendance set dc ='" + a + "' where id ='" + b + "' and useId = '" + c + "'";
int rows = DBhelper.ExecuteNonQuery(s);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool insert110(string a, string b, string c, string d, string e, string f, string g)
{
string s = "insert into test2(pId,zId,sx,parentId,parentId2,bh,lzId) values('" + a + "','" + b + "','" + c + "','" + d + "','" + e + "','" + f + "','" + g + "') ";
int rows = DBhelper.ExecuteNonQuery(s);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 添加商品
/// </summary>
/// <param name="p"></param>
/// <returns>几行受影响</returns>
public static int AddProducts(ProductsMDL p)
{
string sql = "insert into Products values(@ProductName,@PTID,@ProductJP,@ProductPrice,@ProductIntroduction,@ProductPic)";
SqlParameter[] sp =
{
new SqlParameter("@ProductName", p.ProductName),
new SqlParameter("@PTID", p.PTID),
new SqlParameter("@ProductJP", p.ProductJP),
new SqlParameter("@ProductPrice", p.ProductPrice),
new SqlParameter("@ProductIntroduction", p.ProductIntroduction),
new SqlParameter("@ProductPic", p.ProductPic)
};
return(DBhelper.MyExecuteNonQuery(sql, sp));
}