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