/// <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);
}