/// <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>
/// 生成查询参数sql字符串
/// </summary>
/// <param name="userId"></param>
/// <param name="para"></param>
/// <returns></returns>
private string GetPara(long userId, QueryParaDto para)
{
if (para == null || para.query_params == null)
{
return("");
}
StringBuilder queryPara = new StringBuilder();
queryPara.Append("'{");
// 组合查询条件
d_query_para_dal paraDal = new d_query_para_dal();
para.query_params = para.query_params.OrderBy(_ => _.id).ToList();
foreach (var p in para.query_params)
{
if (p.value == null && p.value2 == null)
{
continue;
}
var param = paraDal.FindSignleBySql <d_query_para>($"SELECT * FROM d_query_para WHERE id={p.id} AND query_type_id={para.query_type_id}");
if (param == null)
{
continue;
}
if (param.data_type_id == (int)DicEnum.QUERY_PARA_TYPE.DATE ||
param.data_type_id == (int)DicEnum.QUERY_PARA_TYPE.DATETIME ||
param.data_type_id == (int)DicEnum.QUERY_PARA_TYPE.NUMBER ||
param.data_type_id == (int)DicEnum.QUERY_PARA_TYPE.TIMESPAN) // 数值和日期类型
{
if (param.data_type_id == (int)DicEnum.QUERY_PARA_TYPE.TIMESPAN)
{
DateTime t1 = DateTime.MinValue, t2 = DateTime.MinValue;
if ((p.value != null && !DateTime.TryParse(p.value, out t1)) ||
(p.value2 != null && !DateTime.TryParse(p.value2, out t2)))
{
continue;
}
if (p.value != null)
{
p.value = Tools.Date.DateHelper.ToUniversalTimeStamp(t1).ToString();
}
if (p.value2 != null)
{
p.value2 = Tools.Date.DateHelper.ToUniversalTimeStamp(t2).ToString();
}
}
queryPara.Append('"').Append(param.col_name).Append("\":");
if (p.value != null)
{
queryPara.Append('"').Append(param.col_name).Append(" >= '").Append(p.value).Append("'");
if (p.value2 != null)
{
queryPara.Append(" and ").Append(param.col_name).Append(" <= '").Append(p.value2).Append("'\"");
}
else
{
queryPara.Append('"');
}
}
else
{
queryPara.Append('"').Append(param.col_name).Append(" <= '").Append(p.value2).Append("'\"");
}
}
else
{
if (p.value == null)
{
continue;
}
queryPara.Append('"').Append(param.col_name).Append("\":\"").Append(p.value).Append('"');
}
queryPara.Append(",");
}
// 移除最后条件末尾的,
if (queryPara.Length > 2)
{
queryPara = queryPara.Remove(queryPara.Length - 1, 1);
}
queryPara.Append("}'");
return(queryPara.ToString());
}