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 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));
}
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);
}
/// <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 DeleteStaffQueue()
{
if (mySqlclient == null)
mySqlclient = MySqlClient.GetMySqlClient();
string sql="DELETE FROM StaffQueue"+ WHERECOMPANYID;
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 MySqlClient GetMySqlClient()
{
if (instance == null)
{
instance = new MySqlClient();
}
return(instance);
}
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);
}
/// <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>
/// 获取会员卡级别
/// </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]);
}
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]);
}
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>
/// 根据名字找去员工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);
}
/// <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 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]);
}
