public List<UserLevelLogQuery> GetUserLevelLogList(UserLevelLogQuery query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlFrom = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
try
{
totalCount = 0;
sql.Append(" select ull.rowID,ull.user_id,u.user_name,ull.create_date_time,ull.`year`,ull.`month`,ull.user_order_amount,u.user_email,ml.ml_name as ml_code_old,mll.ml_name as ml_code_new,ull.ml_code_change_type ");
sqlFrom.Append(" from user_level_log ull LEFT JOIN users u on ull.user_id=u.user_id left JOIN member_level ml on ml.ml_code=ull.ml_code_old LEFT JOIN member_level mll on mll.ml_code=ull.ml_code_new ");
sqlWhere.Append(" where 1=1 ");
if (query.user_id != 0)
{
sqlWhere.AppendFormat(" and ull.user_id ={0} ", query.user_id);
}
if (!string.IsNullOrEmpty(query.key))
{
if (query.searchStatus == 1)
{
sqlWhere.AppendFormat(" and ull.user_id ={0}", query.key);
}
else if (query.searchStatus == 2)
{
sqlWhere.AppendFormat(" and u.user_name like N'%{0}%' ", query.key);
}
else if (query.searchStatus == 3)
{
sqlWhere.AppendFormat(" and u.user_email like N'%{0}%' ", query.key);
}
}
if (!string.IsNullOrEmpty(query.leveltypeid))
{
if (query.leveltype==1)
{
sqlWhere.AppendFormat(" and ull.ml_code_old ='{0}' ",query.leveltypeid);
}
else if (query.leveltype == 2)
{
sqlWhere.AppendFormat(" and ull.ml_code_new ='{0}' ", query.leveltypeid);
}
}
if (query.IsPage)
{
DataTable _dt = _accessMySql.getDataTable("select count(ull.rowID) as 'totalCount' " + sqlFrom.ToString() + sqlWhere.ToString());
if (_dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0][0]);
}
sqlWhere.AppendFormat(" order by ull.create_date_time desc limit {0},{1}; ", query.Start, query.Limit);
}
sql.Append(sqlFrom.ToString() + sqlWhere.ToString());
return _accessMySql.getDataTableForObj<UserLevelLogQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("UserLevelLogDao-->GetUserLevelLogList-->" + sql.ToString() + ex.Message, ex);
}
}
public List<UserLevelLogQuery> GetUserLevelLogList(UserLevelLogQuery query, out int totalCount)
{
try
{
List<UserLevelLogQuery> store = new List<UserLevelLogQuery>();
store = _userLevelDao.GetUserLevelLogList(query, out totalCount);
if (query.isSecret)
{
foreach (var item in store)
{
if (!string.IsNullOrEmpty(item.user_name))
{
item.user_name = item.user_name.Substring(0, 1) + "**";
}
if (!string.IsNullOrEmpty(item.user_email))
{
item.user_email = item.user_email.Split('@')[0] + "@***";
}
}
}
return store;
}
catch (Exception ex)
{
throw new Exception("UserLevelLogMgr-->GetUserLevelLogList-->" + ex.Message, ex);
}
}
public HttpResponseBase GetUserLevelLogList()
{
string json = string.Empty;
int totalCount = 0;
UserLevelLogQuery query = new UserLevelLogQuery();
List<UserLevelLogQuery> store = new List<UserLevelLogQuery>();
try
{
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
if (!string.IsNullOrEmpty(Request.Params["searchStatus"]))
{
query.searchStatus = Convert.ToInt32(Request.Params["searchStatus"]);
}
if (!string.IsNullOrEmpty(Request.Params["key"]))
{
query.key = Request.Params["key"];
}
if (!string.IsNullOrEmpty(Request.Params["leveltype"]))
{
query.leveltype = Convert.ToInt32(Request.Params["leveltype"]);
}
if (!string.IsNullOrEmpty(Request.Params["levelid"]))
{
query.leveltypeid = Request.Params["levelid"];
}
_userLevelLog = new UserLevelLogMgr(mySqlConnectionString);
store = _userLevelLog.GetUserLevelLogList(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:true,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}