public static int StaffWorkUp(string staffId) { if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } StringBuilder sb = new StringBuilder(); sb.Append("UPDATE StaffWork SET StaffStatus= @StaffStatus,RoomId= @RoomId, RoomName= @RoomName "); //筛选条件 sb.Append("WHERE StaffID = @StaffID "); sb.Append(ANDCOMPANYID); List <MySqlParameter> parameters = new List <MySqlParameter>() { new MySqlParameter("@StaffID", MySqlDbType.String), new MySqlParameter("@StaffStatus", MySqlDbType.String), new MySqlParameter("@RoomId", MySqlDbType.Int32), new MySqlParameter("@RoomName", MySqlDbType.String) }; parameters[0].Value = staffId; parameters[1].Value = "空闲"; parameters[2].Value = DBNull.Value; parameters[3].Value = DBNull.Value; return(mySqlclient.ExecuteNonQuery(sb.ToString(), parameters, CommandType.Text)); }
public static DataTable GetPermission(string userName) { if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } string sql = "SELECT Role FROM UserRole WHERE Name=@Name" + ANDCOMPANYID; List <MySqlParameter> parameters = new List <MySqlParameter>() { new MySqlParameter("@Name", MySqlDbType.String) }; parameters[0].Value = userName; DataSet ds = mySqlclient.GetDataSet(sql, parameters, CommandType.Text); string role = ds.Tables[0].Rows[0][0].ToString(); ds.Clear(); parameters.Clear(); sql = "SELECT ModeName FROM Permission WHERE Name=@Name" + ANDCOMPANYID; parameters = new List <MySqlParameter>() { new MySqlParameter("@Name", MySqlDbType.String) }; parameters[0].Value = role; ds = mySqlclient.GetDataSet(sql, parameters, CommandType.Text); return(ds.Tables[0]); }
//会员注册 public static int MemberRegister(List <object> infoList) { if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } StringBuilder sb = new StringBuilder(); sb.Append("memberRegister ");//存储过程名称 List <MySqlParameter> parameters = new List <MySqlParameter>() { new MySqlParameter("@v_mid", MySqlDbType.String), new MySqlParameter("@v_mname", MySqlDbType.String), new MySqlParameter("@v_cardName", MySqlDbType.String), new MySqlParameter("@v_phone", MySqlDbType.String), new MySqlParameter("@v_status", MySqlDbType.String), new MySqlParameter("@v_balance", MySqlDbType.Double), new MySqlParameter("@v_companyId", MySqlDbType.Int32) }; parameters[0].Value = infoList[0]; parameters[1].Value = infoList[1]; parameters[2].Value = infoList[2]; parameters[3].Value = infoList[3]; parameters[4].Value = infoList[4]; parameters[5].Value = infoList[5]; parameters[6].Value = infoList[6]; return(mySqlclient.ExecuteNonQuery(sb.ToString(), parameters, CommandType.StoredProcedure)); }
public static int DeleteByID(object id,Type type) { if (mySqlclient == null) mySqlclient = MySqlClient.GetMySqlClient(); DataAttr dataAttr = (DataAttr)type.GetCustomAttribute(typeof(DataAttr), false); StringBuilder sb = new StringBuilder(); string key = ""; Type keyType = null; sb.Append("DELETE FROM "); sb.Append(dataAttr.TableName); sb.Append(" WHERE "); PropertyInfo[] propertyInfos = type.GetProperties(); foreach (PropertyInfo info in propertyInfos) { DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false); if (infoAttr == null) continue; if (infoAttr.Key) { key = info.Name; keyType = info.PropertyType; sb.Append(key + " =@"+ key); break; } } sb.Append(ANDCOMPANYID); List<MySqlParameter> parameters = new List<MySqlParameter>(); MySqlParameter parameter = new MySqlParameter("@"+key, mySqlclient.ConvertDBType(keyType)); parameter.Value = id; parameters.Add(parameter); return mySqlclient.ExecuteNonQuery(sb.ToString(), parameters, CommandType.Text); }
public static int DeleteStaffQueue() { if (mySqlclient == null) mySqlclient = MySqlClient.GetMySqlClient(); string sql="DELETE FROM StaffQueue"+ WHERECOMPANYID; return mySqlclient.ExecuteNonQuery(sql, CommandType.Text); }
public static double[] GetSkillPrice(string skillName, string strType) { if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } string sql = "SELECT PriceA,PriceB,PriceC FROM SkillPrice WHERE SkillName=@SkillName and PriceType=@PriceType" + ANDCOMPANYID; List <MySqlParameter> parameters = new List <MySqlParameter>() { new MySqlParameter("@SkillName", MySqlDbType.String), new MySqlParameter("@PriceType", MySqlDbType.String) }; parameters[0].Value = skillName; parameters[1].Value = strType; DataSet ds = mySqlclient.GetDataSet(sql, parameters, CommandType.Text); DataTable dt = ds.Tables[0]; double[] result = new double[3]; if (dt.Rows.Count > 0) { result[0] = Convert.ToDouble(dt.Rows[0][0]); result[1] = Convert.ToDouble(dt.Rows[0][1]); result[2] = Convert.ToDouble(dt.Rows[0][2]); } return(result); }
public static string GetStaffSexByID(string staffId) { if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } StringBuilder sb = new StringBuilder(); string sql = @"SELECT StaffSex FROM StaffInfo WHERE StaffId=@StaffId " + ANDCOMPANYID; List <MySqlParameter> parameters = new List <MySqlParameter>() { new MySqlParameter("@StaffId", MySqlDbType.String) }; parameters[0].Value = staffId; DataSet ds = mySqlclient.GetDataSet(sql, parameters, CommandType.Text); DataTable dt = ds.Tables[0]; if (dt.Rows.Count < 0) { return(""); } else { return(dt.Rows[0][0].ToString()); } }
public static T GetWorkStaffInfoByRoomId <T>(int roomId) { Type type = typeof(T); if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } string strsql = "SELECT * FROM StaffWork WHERE RoomId =@RoomId " + ANDCOMPANYID; List <MySqlParameter> parameters = new List <MySqlParameter>() { new MySqlParameter("@RoomId", MySqlDbType.Int32) }; parameters[0].Value = roomId; DataSet ds = mySqlclient.GetDataSet(strsql, parameters, CommandType.Text); DataTable dt = ds.Tables[0]; if (dt == null || dt.Rows.Count == 0) { return(default(T)); } return(ConvertDataRowToT <T>(dt.Rows[0], dt.Columns.Count)); }
/// <summary> /// 根据Type获取DataTable /// </summary> /// <param name="type"></param> /// <returns></returns> public static DataTable SelectData(Type type) { if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } DataAttr dataAttr = (DataAttr)type.GetCustomAttribute(typeof(DataAttr), false); StringBuilder sb = new StringBuilder(); sb.Append("SELECT "); PropertyInfo[] propertyInfos = type.GetProperties(); foreach (PropertyInfo info in propertyInfos) { DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false); if (infoAttr == null) { continue; } sb.Append(info.Name + ","); } sb.Remove(sb.Length - 1, 1);//移除 多余的 "," sb.Append(" FROM " + dataAttr.TableName + " WHERE CompanyId= "); sb.Append(SystemConst.companyId); DataSet ds = mySqlclient.GetDataSet(sb.ToString(), CommandType.Text); return(ds.Tables[0]); }
/// <summary> /// 结账的时候根据房间号删除临时订单 /// </summary> /// <param name="roomId"></param> /// <returns></returns> public static int DeleteTempOrderByRoomId(int roomId) { if (mySqlclient == null) mySqlclient = MySqlClient.GetMySqlClient(); string sql = @"DELETE FROM temporder where RoomId=" + roomId+ ANDCOMPANYID; return mySqlclient.ExecuteNonQuery(sql, CommandType.Text); }
public static int StaffRegister(object[] infoArry) { if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } StringBuilder sb = new StringBuilder(); sb.Append("staffRegister ");//存储过程名称 List <MySqlParameter> parameters = new List <MySqlParameter>() { new MySqlParameter("@v_staffId", MySqlDbType.String), new MySqlParameter("@v_staffName", MySqlDbType.String), new MySqlParameter("@v_staffSex", MySqlDbType.String), new MySqlParameter("@v_staffPlace", MySqlDbType.String), new MySqlParameter("@v_staffLevel", MySqlDbType.String), new MySqlParameter("@v_department", MySqlDbType.String), new MySqlParameter("@v_idNumber", MySqlDbType.String), new MySqlParameter("@v_basicSalary", MySqlDbType.String), new MySqlParameter("@v_commision", MySqlDbType.Byte), new MySqlParameter("@v_companyId", MySqlDbType.Int32) }; parameters[0].Value = infoArry[0]; parameters[1].Value = infoArry[1]; parameters[2].Value = infoArry[2]; parameters[3].Value = infoArry[3]; parameters[4].Value = infoArry[4]; parameters[5].Value = infoArry[5]; parameters[6].Value = infoArry[6]; parameters[7].Value = infoArry[7]; parameters[8].Value = infoArry[8]; parameters[9].Value = SystemConst.companyId; return(mySqlclient.ExecuteNonQuery(sb.ToString(), parameters, CommandType.StoredProcedure)); }
public static List <T> GetTempOrderByRoomID <T>(int roomID) { List <T> tList = new List <T>(); if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } Type type = typeof(T); DataAttr dataAttr = (DataAttr)type.GetCustomAttribute(typeof(DataAttr), false); StringBuilder sb = new StringBuilder(); string sql = @"SELECT * FROM TempOrder WHERE RoomID = @RoomID" + ANDCOMPANYID; List <MySqlParameter> parameters = new List <MySqlParameter>() { new MySqlParameter("@RoomID", MySqlDbType.Int32) }; parameters[0].Value = roomID; DataSet ds = mySqlclient.GetDataSet(sql, parameters, CommandType.Text); DataTable dt = ds.Tables[0]; PropertyInfo[] propertyInfos = type.GetProperties(); for (int i = 0; i < dt.Rows.Count; ++i) { T t = (T)Activator.CreateInstance(type); //object objPacked = t; for (int j = 0; j < ds.Tables[0].Columns.Count && j < propertyInfos.Length; ++j) { PropertyInfo info = propertyInfos[j]; DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false); object value = dt.Rows[i][j]; if (infoAttr == null) { continue; } if (info.PropertyType.Name.Equals("String") && value == DBNull.Value) { info.SetValue(t, ""); } else { if (value == DBNull.Value) { info.SetValue(t, null); } else { info.SetValue(t, value); } } } //t = (T)objPacked; tList.Add(t); } return(tList); }
public static string GetStaffNameByRoomId(int roomId) { if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } string sql = "select staffName from staffwork where roomid=" + roomId + ANDCOMPANYID; return(mySqlclient.ExecuteScalar(sql, null) as string); }
public static DataTable GetPermission() { if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } string sql = "SELECT DISTINCT Name FROM Permission" + ANDCOMPANYID; DataSet ds = mySqlclient.GetDataSet(sql, CommandType.Text); return(ds.Tables[0]); }
/// <summary> /// 获取会员卡级别 /// </summary> /// <returns></returns> public static DataTable GetCardName() { if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } string sql = "SELECT cardName from card " + WHERECOMPANYID; DataSet ds = mySqlclient.GetDataSet(sql, CommandType.Text); return(ds.Tables[0]); }
/// <summary> ///根据名字删除权限 /// </summary> /// <param name="name"></param> /// <returns></returns> public static int DelPermissionByName(string name) { if (mySqlclient == null) mySqlclient = MySqlClient.GetMySqlClient(); string sql = "DELETE FROM Permission WHERE Name = @Name" + ANDCOMPANYID; List<MySqlParameter> parameters = new List<MySqlParameter>() { new MySqlParameter("@Name", MySqlDbType.String) }; parameters[0].Value = name; return mySqlclient.ExecuteNonQuery(sql, parameters, CommandType.Text); }
/// <summary> /// 根据名字找去员工ID /// </summary> /// <param name="staffName"></param> /// <returns></returns> public static string SelectSatffIDByName(string staffName) { if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } string sql = @"SELECT StaffId FROM StaffInfo WHERE StaffName=@StaffName " + ANDCOMPANYID; List <MySqlParameter> parameters = new List <MySqlParameter>() { new MySqlParameter("@StaffName", MySqlDbType.String) }; parameters[0].Value = staffName; return(mySqlclient.ExecuteScalar(sql, parameters) as string); }
public static int DelRoomByID(int roomId) { if (mySqlclient == null) mySqlclient = MySqlClient.GetMySqlClient(); StringBuilder sb = new StringBuilder(); sb.Append("DELETE FROM Room "); //筛选条件 sb.Append("WHERE RoomId = @RoomId "); List<MySqlParameter> parameters = new List<MySqlParameter>() { new MySqlParameter("@RoomId", MySqlDbType.Int32) }; sb.Append(ANDCOMPANYID); parameters[0].Value = roomId; return mySqlclient.ExecuteNonQuery(sb.ToString(), parameters, CommandType.Text); }
public static int DelStaffInfoByID(string staffId) { if (mySqlclient == null) mySqlclient = MySqlClient.GetMySqlClient(); StringBuilder sb = new StringBuilder(); sb.Append("DELETE FROM StaffInfo "); //筛选条件 sb.Append("WHERE StaffId = @StaffId "); sb.Append(ANDCOMPANYID); List<MySqlParameter> parameters = new List<MySqlParameter>() { new MySqlParameter("@StaffId", MySqlDbType.String) }; parameters[0].Value = staffId; return mySqlclient.ExecuteNonQuery(sb.ToString(), parameters, CommandType.Text); }
/// <summary> /// 获取临时订单的该结束的时间 /// </summary> /// <param name="skillId"></param> /// <param name="startTime"></param> /// <returns></returns> public static DateTime GetTempOrderEndTime(int skillId, DateTime startTime) { DateTime endTime = new DateTime(); if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } string sql = "select ServerTime from skill where SkillId=" + skillId + ANDCOMPANYID; string strTime = mySqlclient.ExecuteScalar(sql, null) as string; //"30分钟", "60分钟", "90分钟", "120分钟", "150分钟", "180分钟" endTime = TimeUtil.AddMinute(startTime, strTime); return(endTime); }
/// <summary> /// 根据房间号取房间 /// </summary> /// <param name="roomId"></param> /// <returns></returns> public static T GetRoomByRoomId <T>(int roomId) { if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } string sql = "select * from room where roomid=" + roomId + ANDCOMPANYID; DataSet ds = mySqlclient.GetDataSet(sql); DataTable dt = ds.Tables[0]; if (dt == null || dt.Rows.Count == 0) { return(default(T)); } return(ConvertDataRowToT <T>(dt.Rows[0], dt.Columns.Count)); }
public static bool IsServerNameExist(string skillName) { if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } string sql = "SELECT count(*) FROM Skill WHERE SkillName=@SkillName " + ANDCOMPANYID; List <MySqlParameter> parameters = new List <MySqlParameter>() { new MySqlParameter("@skillName", MySqlDbType.String) }; parameters[0].Value = skillName; DataSet ds = mySqlclient.GetDataSet(sql, parameters, CommandType.Text); return(Convert.ToInt32(ds.Tables[0].Rows[0][0]) > 0); }
public static DataTable GetPermissionByName(string name) { if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } string sql = @"SELECT ModeName FROM Permission WHERE Name=@Name " + ANDCOMPANYID; List <MySqlParameter> parameters = new List <MySqlParameter>() { new MySqlParameter("@Name", MySqlDbType.String) }; parameters[0].Value = name; DataSet ds = mySqlclient.GetDataSet(sql, parameters, CommandType.Text); return(ds.Tables[0]); }
public static double GetMemberBalance(string mId) { if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } string sql = "SELECT MBalance FROM member WHERE MId = @MId" + ANDCOMPANYID; List <MySqlParameter> parameters = new List <MySqlParameter>() { new MySqlParameter("@MId", MySqlDbType.String) }; parameters[0].Value = mId; DataSet ds = mySqlclient.GetDataSet(sql, parameters, CommandType.Text); return(Convert.ToDouble(ds.Tables[0].Rows[0][0])); }
public static DataTable GetMemberRechargeByName(string name) { if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } string sql = "SELECT Id,MId,MName,Amount,UpdateTime,CompanyId FROM memberRecharge WHERE MName=@MName" + ANDCOMPANYID; List <MySqlParameter> parameters = new List <MySqlParameter>() { new MySqlParameter("@MName", MySqlDbType.String) }; parameters[0].Value = name; DataSet ds = mySqlclient.GetDataSet(sql, parameters, CommandType.Text); return(ds.Tables[0]); }
public static string GetSkillTime(int skillId) { if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } string sql = "SELECT ServerTime FROM Skill WHERE SkillId=@SkillId" + ANDCOMPANYID; List <MySqlParameter> parameters = new List <MySqlParameter>() { new MySqlParameter("@SkillId", MySqlDbType.Int32) }; parameters[0].Value = skillId; DataSet ds = mySqlclient.GetDataSet(sql, parameters, CommandType.Text); return(ds.Tables[0].Rows[0][0].ToString()); }
public static void GetSkillCommisionByName <T>(string skillName, ref List <T> tList) { if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } Type type = typeof(T); DataAttr dataAttr = (DataAttr)type.GetCustomAttribute(typeof(DataAttr), false); string sql = @"SELECT Id,SkillName,StaffLevel,PriceA,PriceB,PriceC,Remark FROM SkillCommision WHERE SkillName = @SkillName" + ANDCOMPANYID; List <MySqlParameter> parameters = new List <MySqlParameter>() { new MySqlParameter("@SkillName", MySqlDbType.String) }; parameters[0].Value = skillName; DataSet ds = mySqlclient.GetDataSet(sql, parameters, CommandType.Text); DataTable dt = ds.Tables[0]; PropertyInfo[] propertyInfos = type.GetProperties(); for (int i = 0; i < dt.Rows.Count; ++i) { T t = (T)Activator.CreateInstance(type); object objPacked = t; for (int j = 0; j < ds.Tables[0].Columns.Count && j < propertyInfos.Length; ++j) { PropertyInfo info = propertyInfos[j]; DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false); if (infoAttr == null) { continue; } if (dt.Rows[i][j] != null) { info.SetValue(t, dt.Rows[i][j]); } else { info.SetValue(t, ""); } } t = (T)objPacked; tList.Add(t); } }
/// <summary> /// 根据性别找出员工 /// </summary> /// <param name="sex"></param> /// <returns></returns> public static DataTable SelectStaffByGender(string sex) { if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } string sql = "SELECT * FROM StaffInfo WHERE StaffSex=@StaffSex " + ANDCOMPANYID; List <MySqlParameter> parameters = new List <MySqlParameter>() { new MySqlParameter("@StaffSex", MySqlDbType.String) }; parameters[0].Value = sex; DataSet ds = mySqlclient.GetDataSet(sql, parameters, CommandType.Text); return(ds.Tables[0]); }
public static bool CheckRoomExist(int roomId) { if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } string sql = "SELECT count(*) FROM Room WHERE RoomId=@RoomId " + ANDCOMPANYID; List <MySqlParameter> parameters = new List <MySqlParameter>() { new MySqlParameter("@RoomId", MySqlDbType.String) }; parameters[0].Value = roomId; DataSet ds = mySqlclient.GetDataSet(sql, parameters, CommandType.Text); return(Convert.ToInt32(ds.Tables[0].Rows[0][0]) > 0); }
/// <summary> /// 获取根据卡的类别获取会员 /// </summary> /// <param name="level"></param> /// <returns></returns> public static DataTable GetMemberGroupByLevel(string level) { if (mySqlclient == null) { mySqlclient = MySqlClient.GetMySqlClient(); } string sql = "SELECT * FROM member WHERE CardName=@CardName " + ANDCOMPANYID; List <MySqlParameter> parameters = new List <MySqlParameter>() { new MySqlParameter("@CardName", MySqlDbType.String) }; parameters[0].Value = level; DataSet ds = mySqlclient.GetDataSet(sql, parameters, CommandType.Text); return(ds.Tables[0]); }