/// <summary> ///获得全部显示变量 /// </summary> /// <returns></returns> public List <string> GetBodyAllVariable() { d_query_result_dal a = new d_query_result_dal(); StringBuilder sql = new StringBuilder(); sql.Append(@"select b.col_comment FROM d_tmpl_cate_var a,d_query_result b,d_general c,"); sql.Append(@"(SELECT id from d_general where general_table_id=" + (int)GeneralTableEnum.NOTIFICATION_TEMPLATE_CATE_DATE_GROUP); sql.Append(" and parent_id = " + (int)DicEnum.NOTIFY_CATE.QUOTE_TEMPLATE_BODY + " ORDER BY id) d "); sql.Append(@" WHERE a.query_result_id = b.id "); sql.Append(@"AND c.parent_id = a.template_cate_id "); sql.Append(@" AND c.ext1 = template_data_group_id"); sql.Append(@" AND c.id=d.id"); List <d_query_result> all = a.FindListBySql(sql.ToString()); List <string> list = new List <string>(); if (all == null) { return(list); } foreach (var entry in all) { list.Add(entry.col_comment); } return(list); }
/// <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="queryTypeId"></param> /// <returns></returns> public List <QueryResultParaDto> GetResultParaAll(long queryTypeId) { List <QueryResultParaDto> result = new List <QueryResultParaDto>(); string sql = $"SELECT * FROM d_query_result WHERE query_type_id={queryTypeId} and (type_id is null or type_id=3) and is_visible = 1 ORDER BY col_order ASC"; var queryResultList = new d_query_result_dal().FindListBySql(sql); foreach (var qr in queryResultList) { result.Add(new QueryResultParaDto { id = qr.id, name = qr.col_comment, length = qr.col_length, type = qr.display_type_id }); } return(result); }
/// <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); }
public List <string> GetVariable(int id) { d_query_result_dal a = new d_query_result_dal(); StringBuilder sql = new StringBuilder(); sql.Append(@"select b.col_comment FROM d_tmpl_cate_var a,d_query_result b,d_general c "); sql.Append(@" WHERE a.query_result_id = b.id "); sql.Append(@"AND c.parent_id = a.template_cate_id "); sql.Append(@" AND c.ext1 = template_data_group_id"); sql.Append(@" AND c.id=" + id); List <d_query_result> all = a.FindListBySql(sql.ToString()); List <string> list = new List <string>(); if (all == null) { return(list); } foreach (var entry in all) { list.Add(entry.col_comment); } return(list); }