/// <summary> /// 修改用户所选的查询结果显示列 /// </summary> /// <param name="userId"></param> /// <param name="groupId">查询条件组id</param> /// <param name="ids">修改的查询结果显示列字符串(如:2,3,5)</param> public void EditResultSelect(long userId, long queryTypeId, long groupId, string ids) { if (string.IsNullOrEmpty(ids)) { return; } // 检查输入参数是否合法 var resultAll = GetResultAll(queryTypeId); string[] idArr = ids.Split(','); foreach (string id in idArr) { // 字符串中有非法内容,直接返回 if (!resultAll.Exists(r => r.val.Equals(id))) { return; } } // 向查询结果列添加不可见列 string sql = $"SELECT * FROM d_query_result WHERE query_type_id={queryTypeId} AND display_type_id IN ({(int)DicEnum.QUERY_RESULT_DISPLAY_TYPE.ID},{(int)DicEnum.QUERY_RESULT_DISPLAY_TYPE.TOOLTIP},{(int)DicEnum.QUERY_RESULT_DISPLAY_TYPE.RETURN_VALUE})"; var paras = new d_query_result_dal().FindListBySql(sql); if (paras == null || paras.Count == 0) { throw new Exception($"query_type_id:{queryTypeId}-数据库未添加d_query_result记录:id"); } foreach (var p in paras) { ids += "," + p.id; } var dal = new sys_query_type_user_dal(); // 同时修改所有相同query_type_id的查询结果 sql = $"UPDATE sys_query_type_user SET query_result_ids='{ids}' WHERE user_id={userId} AND query_type_id={queryTypeId}"; dal.ExecuteSQL(sql); sys_query_type_user queryUser = dal.FindSignleBySql <sys_query_type_user>($"SELECT * FROM sys_query_type_user WHERE user_id={userId} AND query_para_group_id={groupId}"); // 用户第一次修改,新增记录 if (queryUser == null) { sys_query_type_user queryDefault = dal.FindSignleBySql <sys_query_type_user>($"SELECT * FROM sys_query_type_user WHERE user_id is null AND query_para_group_id={groupId}"); queryUser = new sys_query_type_user { id = dal.GetNextIdSys(), query_para_ids = queryDefault.query_para_ids, query_result_ids = ids, query_type_id = queryTypeId, query_para_group_id = groupId, user_id = userId }; dal.Insert(queryUser); } }
/// <summary> /// 获取用户已选择的查询结果列信息 /// </summary> /// <param name="userId"></param> /// <param name="groupId">查询条件组id</param> /// <returns></returns> public List <QueryResultParaDto> GetResultParaSelect(long userId, long groupId) { var result = new List <QueryResultParaDto>(); sys_query_type_user queryUser = new sys_query_type_user_dal().GetQueryTypeUser(userId, groupId); if (queryUser == null) // 用户未修改查询结果列,使用默认值 { queryUser = new sys_query_type_user_dal().GetQueryTypeUser(0, groupId); } if (queryUser == null) { return(result); } // 获取显示结果列信息并按顺序填充 var list = new d_query_result_dal().FindListBySql($"SELECT * FROM d_query_result WHERE id IN ({queryUser.query_result_ids}) AND query_type_id={queryUser.query_type_id} "); string[] ids = queryUser.query_result_ids.Split(','); for (int i = 0; i < ids.Length; ++i) { long id = long.Parse(ids[i]); var col = list.Find(c => c.id == id); if (col == null) { continue; } AuthUrlDto url = null; if (!string.IsNullOrEmpty(col.link_url)) { url = GetAuthUrl(col.link_url); } QueryResultParaDto para = new QueryResultParaDto { id = id, name = col.col_comment, length = col.col_length, type = col.display_type_id, visible = col.is_visible, url = url }; result.Add(para); } return(result); }
/// <summary> /// 获取一个搜索页面的查询语句 /// </summary> /// <param name="userId">用户id</param> /// <param name="para">搜索条件</param> /// <param name="count">查询结果总数</param> /// <returns>查询没有结果时,返回空字符串</returns> private string GetSql(long userId, QueryParaDto para, out int count) { count = 0; string sqlPara = GetPara(userId, para); if (sqlPara == "") { return(""); } count = new sys_query_type_user_dal().GetQueryCount(para.para_group_id, para.query_type_id, userId, sqlPara); if (count == 0) { return(""); } string sql = new sys_query_type_user_dal().GetQuerySql(para.para_group_id, para.query_type_id, userId, sqlPara, para.order_by); return(sql); }
/// <summary> /// 获取查询条件信息 /// </summary> /// <param name="userId"></param> /// <param name="groupId">查询条件组id</param> /// <param name="visiable">查询条件范围(1:可见的查询条件,0:全部)</param> /// <returns></returns> private List <QueryConditionParaDto> GetConditionParaType(long userId, long groupId, int visiable) { var result = new List <QueryConditionParaDto>(); sys_query_type_user queryUser = new sys_query_type_user_dal().GetQueryTypeUser(userId, groupId); if (queryUser == null) // 用户未修改查询结果列,使用默认值 { queryUser = new sys_query_type_user_dal().GetQueryTypeUser(0, groupId); } if (queryUser == null) { return(result); } if (string.IsNullOrEmpty(queryUser.query_para_ids)) { return(result); } // 获取查询条件列信息并按顺序填充 List <d_query_para> list; if (visiable == 1) { list = new d_query_para_dal().FindListBySql($"SELECT * FROM d_query_para WHERE id IN ({queryUser.query_para_ids}) AND query_para_group_id={groupId} AND is_visible=1"); } else { list = new d_query_para_dal().FindListBySql($"SELECT * FROM d_query_para WHERE id IN ({queryUser.query_para_ids}) AND query_para_group_id={groupId}"); } string[] ids = queryUser.query_para_ids.Split(','); for (int i = 0; i < ids.Length; ++i) { long id = long.Parse(ids[i]); var col = list.Find(c => c.id == id); if (col == null) { continue; } QueryConditionParaDto para = new QueryConditionParaDto { id = id, data_type = col.data_type_id, defaultValue = col.default_value, description = col.col_comment, ref_url = col.ref_url, is_not_null = col.isnot_null, }; if (para.defaultValue != null) { if (para.defaultValue.Contains("**login_user_id**")) { para.defaultValue = para.defaultValue.Replace("**login_user_id**", $"{userId}"); } if (para.defaultValue.Contains("select")) { try { var dt = new d_query_para_dal().ExecuteDataTable(para.defaultValue); if (dt != null && dt.Rows.Count > 0) { para.defaultValue = dt.Rows[0][0].ToString(); } } catch { } } } // 下拉框和多选下拉框,获取列表值 if (col.data_type_id == (int)DicEnum.QUERY_PARA_TYPE.DROPDOWN || col.data_type_id == (int)DicEnum.QUERY_PARA_TYPE.MULTI_DROPDOWN || col.data_type_id == (int)DicEnum.QUERY_PARA_TYPE.DYNAMIC) { if (col.id == 1244) // 费用报表 { col.ref_sql = col.ref_sql.Replace("##col1242##", $"{userId}"); } if (col.ref_sql.Contains("**login_user_id**")) { col.ref_sql = col.ref_sql.Replace("**login_user_id**", $"{userId}"); } var dt = new d_query_para_dal().ExecuteDataTable(col.ref_sql); if (dt != null) { para.values = new List <DictionaryEntryDto>(); foreach (System.Data.DataRow row in dt.Rows) { para.values.Add(new DictionaryEntryDto(row[0].ToString(), row[1].ToString())); } } } result.Add(para); } return(result); }
/// <summary> /// 获取小窗口钻取的查询结果 /// </summary> /// <param name="userId"></param> /// <param name="para"></param> /// <returns></returns> public QueryResultDto GetResultWidgetDrill(long userId, QueryWidgetDrillParaDto para) { QueryResultDto result = new QueryResultDto(); int count = 0; string sql = new DashboardBLL().GetWidgetDrillSql(para.widget_id, para.group1, para.group2, para.order_by, userId, out count); // 获取查询sql语句 result.count = count; result.query_type_id = para.query_type_id; result.para_group_id = para.para_group_id; if (count == 0) // 查询记录总数为0 { return(result); } // 计算分页信息 if (para.page_size == 0) { para.page_size = _pageSize; } int totalPage = count / para.page_size; if (count % para.page_size != 0) { ++totalPage; } if (para.page <= 0) { para.page = 1; } if (para.page > totalPage) { para.page = totalPage; } int offset = (para.page - 1) * para.page_size; // 获取查询结果 sql = sql + $" LIMIT {offset},{para.page_size}"; var table = new sys_query_type_user_dal().ExecuteDataTable(sql); List <Dictionary <string, object> > list = new List <Dictionary <string, object> >(); foreach (DataRow row in table.Rows) { Dictionary <string, object> column = new Dictionary <string, object>(); foreach (DataColumn col in table.Columns) { column.Add(col.ColumnName, row[col.ColumnName]); } list.Add(column); } result.order_by = para.order_by; result.page = para.page; result.page_count = totalPage; result.page_size = para.page_size; //result.query_id = queryId; result.query_id = ""; result.result = list; return(result); }
/// <summary> /// 根据查询条件查询数据 /// </summary> /// <param name="userId"></param> /// <param name="para"></param> /// <returns></returns> public QueryResultDto GetResult(long userId, QueryParaDto para) { QueryResultDto result = new QueryResultDto(); int count = 0; string sql = GetSql(userId, para, out count); // 根据查询条件获取查询sql语句 result.count = count; result.query_type_id = para.query_type_id; result.para_group_id = para.para_group_id; if (count == 0) // 查询记录总数为0 { return(result); } // 计算分页信息 if (para.page_size == 0) { para.page_size = _pageSize; } int totalPage = count / para.page_size; if (count % para.page_size != 0) { ++totalPage; } if (para.page <= 0) { para.page = 1; } if (para.page > totalPage) { para.page = totalPage; } int offset = (para.page - 1) * para.page_size; // 缓存查询sql语句 /* * CacheQuerySqlDto sqlCache = new CacheQuerySqlDto * { * query_page_name = para.query_page_name, * count = count, * page_size = para.page_size, * page_count = totalPage, * query_sql = sql * }; * string queryId = userId + new Random().Next(1000, 9999).ToString(); * CachedInfoBLL.SetQuerySql(queryId, sqlCache, _sqlExpireMins); */ // 获取查询结果 sql = sql + $" LIMIT {offset},{para.page_size}"; var table = new sys_query_type_user_dal().ExecuteDataTable(sql); List <Dictionary <string, object> > list = new List <Dictionary <string, object> >(); foreach (DataRow row in table.Rows) { Dictionary <string, object> column = new Dictionary <string, object>(); foreach (DataColumn col in table.Columns) { column.Add(col.ColumnName, row[col.ColumnName]); } list.Add(column); } result.order_by = para.order_by; result.page = para.page; result.page_count = totalPage; result.page_size = para.page_size; //result.query_id = queryId; result.query_id = ""; result.result = list; return(result); }