/// <summary>
/// 数据转查询实体
/// </summary>
public static UserQueryInfo DataToModel(SqlDataReader rdr)
{
UserQueryInfo query = new UserQueryInfo();
if (!rdr.IsDBNull(0))
{
query.UserQueryID = rdr.GetInt32(0);
}
if (!rdr.IsDBNull(1))
{
query.UserQueryName = rdr.GetString(1);
}
if (!rdr.IsDBNull(2))
{
query.UserQueryURL = rdr.GetString(2);
}
if (!rdr.IsDBNull(3))
{
query.UserQueryValue = rdr.GetString(3);
}
if (!rdr.IsDBNull(4))
{
query.UserQueryLive = rdr.GetByte(4);
}
if (!rdr.IsDBNull(5))
{
query.UserID = rdr.GetInt32(5);
}
if (!rdr.IsDBNull(6))
{
query.ModifyDate = rdr.GetDateTime(6);
}
return(query);
}
//获取查询信息
private void GetQueryInfo()
{
string param = Request.Url.Query;
this.titleHid.Value = "你可以保存这个查询";
if (param != "")
{
string url = Request.Url.PathAndQuery;
string value = url.Substring(url.IndexOf('&'));
UserQueryInfo query = query_bll.GetUserQueryByValue(userId, value);
if (query.UserQueryID > 0)
{
this.urlHid.Value = query.UserQueryURL;
this.titleHid.Value = query.UserQueryName;
Page.Title = query.UserQueryName;
}
}
else
{
this.titleHid.Value = "消费明细";
}
this.QueryTitle.Text = this.titleHid.Value;
}
public UserQueryInfoCollection QueryAgentLowerUserList(string agentId, int pageIndex, int pageSize)
{
Session.Clear();
UserQueryInfoCollection collection = new UserQueryInfoCollection();
collection.TotalCount = 0;
//string strSql = "select u.UserId,u.DisplayName,isnull(r.RealName,'')RealName,isnull(m.Mobile,'')Mobile,isnull(u.RegisterIp,'')RegisterIp,u.CreateTime,isnull(u.AgentId,'')AgentId,isnull(b.CommissionBalance,0)CommissionBalance,isnull(b.BonusBalance,0)BonusBalance,isnull(b.FreezeBalance,0)FreezeBalance from C_User_Register u left join E_Authentication_RealName r on u.UserId=r.UserId left join E_Authentication_Mobile m on r.UserId=m.UserId left join C_User_Balance b on u.UserId=b.UserId where u.ParentPath like '%" + agentId + "%'";
string strSql = "select u.UserId,u.DisplayName,isnull(r.RealName,'')RealName,isnull(m.Mobile,'')Mobile,isnull(u.RegisterIp,'')RegisterIp,u.CreateTime,isnull(u.AgentId,'')AgentId,isnull(b.CommissionBalance,0)CommissionBalance,isnull(b.BonusBalance,0)BonusBalance,isnull(b.FreezeBalance,0)FreezeBalance,isnull(b.FillMoneyBalance,0)FillMoneyBalance from C_User_Register u left join E_Authentication_RealName r on u.UserId=r.UserId left join E_Authentication_Mobile m on r.UserId=m.UserId left join C_User_Balance b on u.UserId=b.UserId where u.AgentId=:agentId";
var result = Session.CreateSQLQuery(strSql).SetString("agentId", agentId).List();
if (result != null)
{
collection.TotalCount = result.Count;
foreach (var item in result)
{
var array = item as object[];
UserQueryInfo info = new UserQueryInfo();
info.UserId = array[0].ToString();
info.DisplayName = array[1].ToString();
info.RealName = array[2].ToString();
info.Mobile = array[3].ToString();
info.RegisterIp = array[4].ToString();
info.RegTime = Convert.ToDateTime(array[5]);
info.AgentId = array[6].ToString();
info.CommissionBalance = Convert.ToDecimal(array[7]) + Convert.ToDecimal(array[10]);
info.BonusBalance = Convert.ToDecimal(array[8]);
info.FreezeBalance = Convert.ToDecimal(array[9]);
collection.UserList.Add(info);
}
}
collection.UserList = collection.UserList.Skip(pageIndex * pageSize).Take(pageSize).ToList();
return(collection);
}
/// <summary>
/// 修改查询
/// </summary>
public bool UpdateUserQuery(UserQueryInfo query)
{
SqlParameter[] parms = ModelToParms(query);
int result = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.StoredProcedure, SQL_UPDATE_USER_QUERY, parms);
return(result > 0);
}
//查询保存
protected void ButtonSave_Click(object sender, EventArgs e)
{
if (!this.QueryTitleBox.Visible || this.QueryTitleBox.Text.Trim() == "消费明细")
{
Utility.Alert(this, "请重命名查询名称再保存。");
return;
}
string queryURL = QueryHelper.GetQueryURL(Request.Url.PathAndQuery);
string queryValue = queryURL.Substring(queryURL.IndexOf('&'));
if (queryURL == "")
{
Utility.Alert(this, "没有要保存的查询。");
return;
}
if (this.QueryTitleBox.Visible && this.QueryTitleBox.Text.Trim() == "")
{
Utility.Alert(this, "查询名称不能为空。");
return;
}
UserQueryInfo info = query_bll.GetUserQueryByName(userId, this.QueryTitleBox.Text.Trim());
if (info.UserQueryID <= 0)
{
info = query_bll.GetUserQueryByValue(userId, queryValue);
}
info.UserQueryName = this.QueryTitleBox.Text.Trim();
info.UserQueryURL = queryURL;
info.UserQueryValue = queryValue;
info.UserQueryLive = 1;
info.ModifyDate = DateTime.Now;
info.UserID = userId;
bool success = false;
if (info.UserQueryID > 0)
{
success = query_bll.UpdateUserQuery(info);
}
else
{
success = query_bll.InsertUserQuery(info);
}
if (success)
{
Utility.Alert(this, "查询保存成功。", "Default.aspx");
}
else
{
Utility.Alert(this, "查询保存失败！");
}
}
public JsonResult ExportExcel(List <ColumnModel> gridColumns, UserQueryInfo queryInfo)
{
var user = AuthenticationService.GetCurrentUser().User;
if (user != null)
{
queryInfo.CurrentUserId = user.Id;
}
return(base.ExportExcelMasterfile(gridColumns, queryInfo));
}
public JsonResult GetDataForGrid(UserQueryInfo queryInfo)
{
var user = AuthenticationService.GetCurrentUser().User;
if (user != null)
{
queryInfo.CurrentUserId = user.Id;
}
var queryData = _userService.GetDataForGridMasterfile(queryInfo);
var clientsJson = Json(queryData, JsonRequestBehavior.AllowGet);
return(clientsJson);
}
private void RefreshTable(PowerEntity entity)
{
if (entity.UserQueryInfo == null)
{
return;
}
for (int i = 0; i < entity.UserQueryInfo.Count; i++)
{
UserQueryInfo info = entity.UserQueryInfo[i];
GetElementById("index" + i).InnerText = info.GetCorrectValue(UserQueryInfo.QueryValueType.Index);
GetElementById("month" + i).InnerText = info.GetCorrectValue(UserQueryInfo.QueryValueType.Date);
GetElementById("pay" + i).InnerText = info.GetCorrectValue(UserQueryInfo.QueryValueType.Pay);
GetElementById("dedit" + i).InnerText = info.GetCorrectValue(UserQueryInfo.QueryValueType.Dedit);
GetElementById("message" + i).InnerText = info.GetCorrectValue(UserQueryInfo.QueryValueType.Message) + " ";
GetElementById("r" + i).Style = "display:block;";
}
}
/// <summary>
/// 根据Value取查询，返回实体
/// </summary>
public UserQueryInfo GetUserQueryByValue(int userId, string value)
{
UserQueryInfo query = new UserQueryInfo();
SqlParameter[] parms =
{
new SqlParameter(PARM_USER_ID, SqlDbType.Int),
new SqlParameter(PARM_VALUE, SqlDbType.NVarChar, 200)
};
parms[0].Value = userId;
parms[1].Value = value;
using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_USER_QUERY_BY_VALUE, parms))
{
while (rdr.Read())
{
query = DataToModel(rdr);
}
}
return(query);
}
/// <summary>
/// 查询实体转参数
/// </summary>
public static SqlParameter[] ModelToParms(UserQueryInfo query)
{
SqlParameter[] parms =
{
new SqlParameter("@UserQueryID", SqlDbType.Int),
new SqlParameter("@UserQueryName", SqlDbType.NVarChar, 20),
new SqlParameter("@UserQueryURL", SqlDbType.NVarChar, 200),
new SqlParameter("@UserQueryValue", SqlDbType.NVarChar, 200),
new SqlParameter("@UserQueryLive", SqlDbType.TinyInt),
new SqlParameter("@UserID", SqlDbType.Int),
new SqlParameter("@ModifyDate", SqlDbType.DateTime)
};
parms[0].Value = query.UserQueryID;
parms[1].Value = query.UserQueryName;
parms[2].Value = query.UserQueryURL;
parms[3].Value = query.UserQueryValue;
parms[4].Value = query.UserQueryLive;
parms[5].Value = query.UserID;
parms[6].Value = query.ModifyDate;
return(parms);
}
//查询删除
protected void ButtonDelete_Click(object sender, EventArgs e)
{
UserQueryInfo info = query_bll.GetUserQueryByName(userId, this.titleHid.Value);
info.UserQueryLive = 0;
info.ModifyDate = DateTime.Now;
bool success = false;
if (info.UserQueryID > 0)
{
success = query_bll.UpdateUserQuery(info);
}
if (success)
{
Utility.Alert(this, "查询删除成功。", "Default.aspx");
}
else
{
Utility.Alert(this, "查询删除失败！");
}
}
/// <summary>
/// 修改专题
/// </summary>
public bool UpdateUserQuery(UserQueryInfo query)
{
return(dal.UpdateUserQuery(query));
}
/// <summary>
/// 插入专题
/// </summary>
public bool InsertUserQuery(UserQueryInfo query)
{
return(dal.InsertUserQuery(query));
}
