Ejemplo n.º 1
0
        /// <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);
        }
Ejemplo n.º 2
0
        /// <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());
        }