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