/// <summary> /// ����������һ������ /// </summary> public void Add(AutoBwsModel.Credit.package model) { using (con) { MySqlTransaction ts = con.BeginTransaction(); try { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into package("); strSql.Append("name,remark,create_date,user_id,type_id,level,level_for_next_period)"); strSql.Append(" values ("); strSql.Append("@name,@remark,@create_date,@user_id,@type_id,@level,@level_for_next_period)"); MySqlParameter[] parameters = { new MySqlParameter("@name", MySqlDbType.VarChar, 100), new MySqlParameter("@remark", MySqlDbType.VarChar, 200), new MySqlParameter("@create_date", MySqlDbType.DateTime), // new MySqlParameter("@expired_date", MySqlDbType.DateTime), // new MySqlParameter("@start_date", MySqlDbType.DateTime), new MySqlParameter("@user_id", MySqlDbType.Int32, 4), // new MySqlParameter("@state", MySqlDbType.Int32, 3), new MySqlParameter("@type_id", MySqlDbType.Int32, 10), new MySqlParameter("@level", MySqlDbType.Int32, 10), new MySqlParameter("@level_for_next_period", MySqlDbType.Int32, 10) }; parameters[0].Value = model.name; parameters[1].Value = model.remark; parameters[2].Value = model.create_date; //parameters[3].Value = model.expired_date; //parameters[4].Value = model.start_date; parameters[3].Value = model.user_id; //parameters[6].Value = model.state; parameters[4].Value = model.type_id; parameters[5].Value = model.level; parameters[6].Value = model.levelForNextPeriod; DBHelper.ExecuteNonQuery(ts, CommandType.Text, strSql.ToString(), parameters); ts.Commit(); } catch (Exception ex) { ts.Commit(); throw ex; } } }
/// <summary> /// ����һ������ /// </summary> public void Add(AutoBwsModel.Credit.creadit model) { StringBuilder strSql=new StringBuilder(); strSql.Append("insert into creadit("); strSql.Append("admin_id,user_id,state,create_date)"); strSql.Append(" values ("); strSql.Append("@admin_id,@user_id,@state,@create_date)"); MySqlParameter[] parameters = { new MySqlParameter("@admin_id", MySqlDbType.Int32,4), new MySqlParameter("@user_id", MySqlDbType.Int32,4), new MySqlParameter("@state", MySqlDbType.Int32,4), new MySqlParameter("@create_date", MySqlDbType.DateTime)}; parameters[0].Value = model.admin_id; parameters[1].Value = model.user_id; parameters[2].Value = model.state; parameters[3].Value = model.create_date; DBHelper.ExecuteNonQuery(con, strSql.ToString(), parameters); }
/// <summary> /// ����һ������ /// </summary> public void Add(AutoBwsModel.Credit.package_type model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into package_type("); strSql.Append("remark,name,credit_count,level)"); strSql.Append(" values ("); strSql.Append("@remark,@name,@level)"); MySqlParameter[] parameters = { new MySqlParameter("@remark", MySqlDbType.VarChar, 500), new MySqlParameter("@name", MySqlDbType.VarChar, 200), new MySqlParameter("@credit_count", MySqlDbType.Int32, 4), new MySqlParameter("@level", MySqlDbType.Int32, 4) }; parameters[0].Value = model.remark; parameters[1].Value = model.name; parameters[2].Value = model.credit_count; parameters[3].Value = model.level; DBHelper.ExecuteNonQuery(con, CommandType.Text, strSql.ToString(), parameters); }
public void AddCreditForAdmin(AutoBwsModel.Credit.UserCredit creditInfo) { StringBuilder sb = new StringBuilder(); sb.Append("Insert into tab_UserCredit").Append("(") .Append("UserId,") .Append("ExpiredTime,") .Append("Credit,") .Append("BeginDateTime,") .Append("EndDateTime,") .Append("State,") .Append("TrueUserId)") .Append("values") .Append("(") .Append("@UserId,") .Append("@ExpiredTime,") .Append("@Credit,") .Append("@BeginDateTime,") .Append("@EndDateTime,") .Append("@State,") .Append("@TrueUserId)"); MySqlParameter[] paraList = new MySqlParameter[] { new MySqlParameter("@UserId", MySqlDbType.Int32, 4), new MySqlParameter("@ExpiredTime", MySqlDbType.DateTime, 8), new MySqlParameter("@Credit", MySqlDbType.Int32, 4), new MySqlParameter("@BeginDateTime", MySqlDbType.DateTime, 8), new MySqlParameter("@EndDateTime", MySqlDbType.DateTime, 8), new MySqlParameter("@State", MySqlDbType.Int32, 4), new MySqlParameter("@TrueUserId", MySqlDbType.Int32, 4), }; paraList[0].Value = creditInfo.UserId; paraList[1].Value = creditInfo.ExpiredTime; paraList[2].Value = creditInfo.Credit; paraList[3].Value = creditInfo.BeginDateTime; paraList[4].Value = creditInfo.EndDateTime; paraList[5].Value = creditInfo.State; paraList[6].Value = creditInfo.TrueUserId; DBHelper.ExecuteNonQuery(con, CommandType.Text, sb.ToString(), paraList); }
/// <summary> /// ����һ������ /// </summary> public void Update(AutoBwsModel.Credit.creadit model) { StringBuilder strSql=new StringBuilder(); strSql.Append("update creadit set "); strSql.Append("admin_id=@admin_id,"); strSql.Append("user_id=@user_id,"); strSql.Append("state=@state,"); strSql.Append("create_date=@create_date"); strSql.Append(" where id=@id "); MySqlParameter[] parameters = { new MySqlParameter("@id", MySqlDbType.Int32,4), new MySqlParameter("@admin_id", MySqlDbType.Int32,4), new MySqlParameter("@user_id", MySqlDbType.Int32,4), new MySqlParameter("@state", MySqlDbType.Int32,4), new MySqlParameter("@create_date", MySqlDbType.DateTime)}; parameters[0].Value = model.id; parameters[1].Value = model.admin_id; parameters[2].Value = model.user_id; parameters[3].Value = model.state; parameters[4].Value = model.create_date; DBHelper.ExecuteNonQuery(con,strSql.ToString(),parameters); }
/// <summary> /// ����һ������ /// </summary> public void Update(AutoBwsModel.Credit.package_type model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update package_type set "); strSql.Append("remark=@remark,"); strSql.Append("name=@name, "); strSql.Append("credit_count=@credit_count"); strSql.Append(" where id=@id "); MySqlParameter[] parameters = { new MySqlParameter("@id", MySqlDbType.Int32, 4), new MySqlParameter("@remark", MySqlDbType.VarChar, 500), new MySqlParameter("@name", MySqlDbType.VarChar, 200), new MySqlParameter("@credit_count", MySqlDbType.Int32, 4) }; parameters[0].Value = model.id; parameters[1].Value = model.remark; parameters[2].Value = model.name; parameters[3].Value = model.credit_count; DBHelper.ExecuteNonQuery(con, CommandType.Text, strSql.ToString(), parameters); }
public void SetModel(DataRow r, ref AutoBwsModel.Credit.package_type model) { model.id = int.Parse(r["id"].ToString()); model.remark = r["remark"].ToString(); model.name = r["name"].ToString(); model.credit_count = Convert.ToInt32(r["credit_count"].ToString()); model.level = Convert.ToInt32(r["level"].ToString()); }
/// <summary> /// ���ӻ����һ������ ���Ӻ���״̬�û���Ӧ�û���״̬ /// ע�����LEVEL��ʾ��ǰLEVEL��levelForNextPeriodҪ��ͬ /// </summary> public void AddOrUpdate(AutoBwsModel.Credit.package model, AutoBwsModel.UserInfo uinfo, out string msg) { using (con) { MySqlTransaction ts = con.BeginTransaction(); try { StringBuilder strSql = new StringBuilder(); int count = Convert.ToInt32(DBHelper.ExecuteScalar(ts, CommandType.Text, string.Format("SELECT count(0) FROM package where user_id={0} and type_id={1}", model.user_id , model.type_id))); if (count < 1) { strSql.Append("insert into package("); strSql.Append(@"name,remark,create_date, user_id, type_id,level,level_for_next_period)"); strSql.Append(" values ("); strSql.Append("@name,@remark,@create_date,@user_id,@type_id,@level,@level_for_next_period)"); } else { strSql.Append("update package set name=@name,") .Append("remark=@remark,") .Append("create_date=@create_date,") .Append("user_id=@user_id,") .Append("type_id=@type_id,") .Append("level=@level,") .Append("level_for_next_period=@level_for_next_period"); } MySqlParameter[] parameters = { new MySqlParameter("@name", MySqlDbType.VarChar, 100), new MySqlParameter("@remark", MySqlDbType.VarChar, 200), new MySqlParameter("@create_date", MySqlDbType.DateTime), new MySqlParameter("@user_id", MySqlDbType.Int32, 4), new MySqlParameter("@type_id", MySqlDbType.Int32, 10), new MySqlParameter("@level", MySqlDbType.Int32, 10), new MySqlParameter("@level_for_next_period", MySqlDbType.Int32, 10) }; parameters[0].Value = model.name; parameters[1].Value = model.remark; parameters[2].Value = model.create_date; // parameters[3].Value = model.expired_date; // parameters[4].Value = model.start_date; parameters[3].Value = model.user_id; // parameters[6].Value = model.state; parameters[4].Value = model.type_id; parameters[5].Value = model.level; parameters[6].Value = model.levelForNextPeriod; int currentLevel = Convert.ToInt32(DBHelper.ExecuteScalar(ts, CommandType.Text, string.Format("SELECT level from package where user_id={0} and type_id={1}", model.user_id, model.type_id))); //0��ȣ�1������2���� int upOrdown = currentLevel == model.levelForNextPeriod ? 0 : (currentLevel > model.levelForNextPeriod ? 1 : 2); decimal baseCredit = Convert.ToDecimal(DBHelper.ExecuteScalar(ts, CommandType.Text, "SELECT credit_count from package_type where id=" + model.type_id).ToString()); decimal Multiple = 0;//ʣ����������������� DateTime currentTime = DateTime.Now; DateTime expiredTime = uinfo.ExpiryDate; decimal needCredit = 0; // double leftMillisecond = (expiredTime - currentTime).TotalMilliseconds;//ʣ������ double piredMillsecond = Convert.ToDouble(System.DateTime.DaysInMonth(currentTime.Year, currentTime.Month)) * 24d * 60d * 60d * 1000d;//Ĭ������Ϊ����1�ŵ�β�Ž������ͣ� double nowToendMillsencond = (Convert.ToDateTime(currentTime.Year.ToString() + "-" + currentTime.Month.ToString() + "-" + System.DateTime.DaysInMonth(currentTime.Year, currentTime.Month).ToString()) - currentTime).TotalMilliseconds;//Ŀǰ���������ڵĺ���� // Multiple=Convert.ToDecimal(nowToendMillsencond/piredMillsecond); Multiple = Convert.ToDecimal(nowToendMillsencond / piredMillsecond); needCredit = baseCredit * (model.levelForNextPeriod - currentLevel); needCredit = Math.Ceiling(needCredit * Multiple * 4) / 4;//��ֵ //����USERINFO ����ֵ����CRIDITAVAILABLE&&CREDITUSED if (count < 1) //�����ǵ�һ������PACKAGE { // needCredit = model.levelForNextPeriod * baseCredit; parameters[6].Value = model.level = model.levelForNextPeriod; msg = ""; } decimal creditAvailable = Convert.ToDecimal(DBHelper.ExecuteScalar(ts, CommandType.Text, "SELECT creditAvailable from userinfo where id=" + model.user_id)); if (creditAvailable > needCredit) { msg = ""; switch (upOrdown) { case 2: DBHelper.ExecuteNonQuery(ts, CommandType.Text, string.Format("Update Userinfo set creditAvailable=creditAvailable-{0},creditUsed=creditUsed+{0} where id ={1}", needCredit, model.user_id)); DBHelper.ExecuteNonQuery(ts, CommandType.Text, strSql.ToString(), parameters); ts.Commit(); msg = "up success"; break; case 1: parameters[5].Value = currentLevel; parameters[6].Value = model.levelForNextPeriod; DBHelper.ExecuteNonQuery(ts, CommandType.Text, strSql.ToString(), parameters); msg = "down success"; ts.Commit(); break; default: msg = "Invalid operation"; ts.Rollback(); break; } } else { msg = "not enouth credit"; ts.Rollback(); } } catch (Exception ex) { msg = "error"; ts.Commit(); throw ex; } } }
private void setModel_Michael(DataRow r, ref AutoBwsModel.Credit.package model) { if (r["id"].ToString() != "") { model.id = int.Parse(r["id"].ToString()); } model.name = r["name"].ToString(); model.remark = r["remark"].ToString(); if (r["create_date"].ToString() != "") { model.create_date = DateTime.Parse(r["create_date"].ToString()); } //if (r["expired_date"].ToString() != "") //{ // model.expired_date = DateTime.Parse(r["expired_date"].ToString()); //} //if (r["start_date"].ToString() != "") //{ // model.start_date = DateTime.Parse(r["start_date"].ToString()); //} if (r["user_id"].ToString() != "") { model.user_id = int.Parse(r["user_id"].ToString()); } //if (r["state"].ToString() != "") //{ // model.state = int.Parse(r["state"].ToString()); //} if (r["type_id"].ToString() != "") { model.type_id = int.Parse(r["type_id"].ToString()); } if (r["level"].ToString() != "") { model.level = Convert.ToInt32(r["level"]); } if (r["level_for_next_period"].ToString() != "") { model.levelForNextPeriod = Convert.ToInt32(r["level_for_next_period"]); } //if (r["name"].ToString() != "") //{ // model.typeName = r["name"].ToString(); //} }
/// <summary> /// ����һ������ /// </summary> public void Update(AutoBwsModel.Credit.package model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update package set "); strSql.Append("name=@name,"); strSql.Append("remark=@remark,"); strSql.Append("create_date=@create_date,"); // strSql.Append("expired_date=@"); // strSql.Append("start_date=@"); strSql.Append("user_id=@user_id,"); // strSql.Append("state=@"); strSql.Append("type_id=@type_id,"); strSql.Append("level=@level,"); strSql.Append("level=@levelForNextPeriod"); strSql.Append(" where id=@id "); MySqlParameter[] parameters = { new MySqlParameter("@id", MySqlDbType.Int32, 4), new MySqlParameter("@name", MySqlDbType.VarChar, 100), new MySqlParameter("@remark", MySqlDbType.VarChar, 200), new MySqlParameter("@create_date", MySqlDbType.DateTime), new MySqlParameter("@user_id", MySqlDbType.Int32, 4), new MySqlParameter("@type_id", MySqlDbType.Int32, 10), new MySqlParameter("@level", MySqlDbType.Int32, 10), new MySqlParameter("@levelForNextPeriod", MySqlDbType.Int32, 4) }; parameters[0].Value = model.id; parameters[1].Value = model.name; parameters[2].Value = model.remark; parameters[3].Value = model.create_date; //parameters[4].Value = model.expired_date; // parameters[5].Value = model.start_date; parameters[4].Value = model.user_id; // parameters[7].Value = model.state; parameters[5].Value = model.type_id; parameters[6].Value = model.level; parameters[7].Value = model.levelForNextPeriod; DBHelper.ExecuteNonQuery(con, CommandType.Text, strSql.ToString(), parameters); }
/// <summary> /// 根据用户获取改用户下的player /// </summary> /// <param name="uinfo"></param> /// <returns></returns> public IList<Player> getPlayerList(AutoBwsModel.UserInfo uinfo) { IList<Player> pList = new List<Player>(); DataTable dt = null; switch (uinfo.AccountType) { case "superadmin": dt = DBHelper.ExecuteDataset(connectStr, CommandType.Text, "SELECT A.* ,G.ghostName FROM (SELECT Player.*,UserInfo.name as belongName FROM Player INNER JOIN UserInfo on UserInfo.Id =" + uinfo.Id + " and Player.UserId=UserInfo.Id) AS A LEFT JOIN Ghost G ON A.attachment2Ghost=G.id"). Tables[0]; break; case "admin": using (MySql.Data.MySqlClient.MySqlConnection con = new MySqlConnection(connectStr)) { if (con.State == ConnectionState.Closed) { con.Open(); } MySqlTransaction mts = con.BeginTransaction(); try { DataTable dtIDs = DBHelper.ExecuteDataset(mts, CommandType.Text, "SELECT * FROM UserInfo Where ParentId=" + uinfo.Id).Tables[0]; StringBuilder sb = new StringBuilder(); sb.Append("(-1,"); foreach (DataRow it in dtIDs.Rows) { sb.Append(it["Id"].ToString()).Append(","); } sb = sb.Remove(sb.Length - 1, 1); sb.Append(")"); dt = DBHelper.ExecuteDataset(mts, CommandType.Text, "SELECT A.* ,G.ghostName FROM (SELECT Player.*,UserInfo.name as belongName FROM Player INNER JOIN UserInfo on UserInfo.Id in " + sb.ToString() + " and Player.UserId=UserInfo.Id) AS A LEFT JOIN Ghost G ON A.attachment2Ghost=G.id"). Tables[0]; mts.Commit(); } catch { mts.Rollback(); } } break; default: dt = DBHelper.ExecuteDataset(connectStr, CommandType.Text, "SELECT A.* ,G.ghostName FROM (SELECT Player.*,UserInfo.name as belongName FROM Player INNER JOIN UserInfo on UserInfo.Id =" + uinfo.Id + " and Player.UserId=UserInfo.Id) AS A LEFT JOIN Ghost G ON A.attachment2Ghost=G.id"). Tables[0]; break; // break; } foreach (DataRow r in dt.Rows) { pList.Add(SetModel(r)); } return pList; }
/// <summary> /// 根据用户获取当前用户下的PLAYER /// </summary> /// <param name="UserInfo"></param> /// <returns></returns> public IList<Player> GetPlayerList(AutoBwsModel.UserInfo UserInfo) { return pService.getPlayerList(UserInfo); }