Beispiel #1
0
        /// <summary>
        /// 生成SQL语句
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="dicInputValues">输入项的值</param>
        /// <param name="paramList">参数列表</param>
        /// <returns></returns>
        public static string GenerateSQL(Entity entity, Dictionary <string, string> dicInputValues, out List <object> paramList)
        {
            paramList = new List <object>();
            if (entity == null || string.IsNullOrWhiteSpace(entity.SQL_CODE))
            {
                return(string.Empty);
            }

            List <BF_CHART_REPORT_FILTER.Entity> filterList = new List <BF_CHART_REPORT_FILTER.Entity>();

            if (dicInputValues != null && dicInputValues.Count > 0)
            {
                filterList = BF_CHART_REPORT_FILTER.Instance.GetFilterList(entity.ID);
            }

            //执行函数
            string sql = Common.FW.Functions.Instance.ExecuteFunction(entity.SQL_CODE);

            //替换SESSION变量
            sql = SystemSession.TransParams(sql);

            //替换输入项
            Regex regex = new Regex(@"@\((?<name>[a-z_0-9\-]+)\)", RegexOptions.IgnoreCase);
            Match match = regex.Match(sql);

            while (match != null && match.Success)
            {
                string name = match.Result("${name}");
                if (dicInputValues != null && dicInputValues.ContainsKey(name))
                {
                    sql = sql.Replace("@(" + name + ")", dicInputValues[name]);
                }
                else
                {
                    sql = sql.Replace("@(" + name + ")", "0");
                }

                match = match.NextMatch();
            }

            //替换筛选项
            StringBuilder sbWhere = new StringBuilder();

            sbWhere.AppendLine("WHERE 1=1");
            int fc = 0;

            foreach (BF_CHART_REPORT_FILTER.Entity fe in filterList)
            {
                if (dicInputValues.ContainsKey(fe.FIELD_NAME) == false)
                {
                    continue;
                }
                if (string.IsNullOrWhiteSpace(dicInputValues[fe.FIELD_NAME]))
                {
                    continue;
                }
                fc++;

                string v1 = dicInputValues[fe.FIELD_NAME].Trim();
                string v2 = string.Empty;

                Enums.FilterOperator oper = (Enums.FilterOperator)fe.FILTER_OPERATOR;
                if (oper == Enums.FilterOperator.介于)
                {
                    string[] vs = v1.Split(new string[] { " - " }, StringSplitOptions.RemoveEmptyEntries);
                    v1 = vs[0].Trim();
                    if (vs.Length > 1)
                    {
                        v2 = vs[1].Trim();
                    }
                    else
                    {
                        v2 = v1;
                    }
                }
                else
                {
                    v2 = v1;
                }

                Enums.FieldDataType dataType = (Enums.FieldDataType)fe.FIELD_DATA_TYPE;
                switch (dataType)
                {
                case Enums.FieldDataType.数值:
                    if (oper == Enums.FilterOperator.介于)
                    {
                        sbWhere.AppendLine("AND " + fe.FIELD_NAME + ">=?");
                        sbWhere.AppendLine("AND " + fe.FIELD_NAME + "<=?");
                        paramList.Add(Convert.ToDecimal(v1));
                        paramList.Add(Convert.ToDecimal(v2));
                    }
                    else
                    {
                        sbWhere.AppendLine("AND " + fe.FIELD_NAME + "=?");
                        paramList.Add(Convert.ToDecimal(v1));
                    }
                    break;

                case Enums.FieldDataType.日期:
                    sbWhere.AppendLine("AND " + fe.FIELD_NAME + ">=?");
                    sbWhere.AppendLine("AND " + fe.FIELD_NAME + "<?");
                    paramList.Add(Convert.ToDateTime(v1).Date);
                    paramList.Add(Convert.ToDateTime(v2).AddDays(1).Date);
                    break;

                case Enums.FieldDataType.文本:
                    if (oper == Enums.FilterOperator.包含)
                    {
                        sbWhere.AppendLine("AND " + fe.FIELD_NAME + " LIKE '%" + v1.Replace('\'', '"') + "%'");
                    }
                    else
                    {
                        sbWhere.AppendLine("AND " + fe.FIELD_NAME + "=?");
                        paramList.Add(v1);
                    }
                    break;
                }
            }

            if (fc > 0)
            {
                sql = "SELECT * FROM (\r\n" + sql + "\r\n)\r\n" + sbWhere.ToString();
            }

            return(sql);
        }
Beispiel #2
0
        /// <summary>
        /// 转换SQL语句
        /// </summary>
        /// <param name="sqlCode">原始SQL语句</param>
        /// <param name="queryString">URL中GET参数</param>
        /// <param name="inputList">自定义输入项(JSON串)</param>
        /// <param name="filterList">筛选项</param>
        /// <param name="paramList">参数列表(输出)</param>
        /// <returns>SQL语句</returns>
        private static void TransSQL(string sqlCode, string queryString, List <InputValueItem> inputList, List <QueryFilterItem> filterList, out string sql, out List <object> paramList, Order order = null)
        {
            sql       = string.Empty;
            paramList = new List <object>();
            if (string.IsNullOrWhiteSpace(sqlCode))
            {
                return;
            }

            //执行函数
            sql = Functions.Instance.ExecuteFunction(sqlCode);

            //替换SESSION变量
            sql = SystemSession.TransParams(sql);

            //自定义输入参数
            Dictionary <string, string> dicInputValues = new Dictionary <string, string>();

            if (inputList != null)
            {
                foreach (InputValueItem input in inputList)
                {
                    if (dicInputValues.ContainsKey(input.Name) == false)
                    {
                        dicInputValues.Add(input.Name, input.Value);
                    }
                }
            }

            //替换输入项
            Regex regex = new Regex(@"@\((?<name>[a-z_0-9\-]+)\)", RegexOptions.IgnoreCase);
            Match match = regex.Match(sql);

            while (match != null && match.Success)
            {
                string name = match.Result("${name}");

                if (dicInputValues.ContainsKey(name) == true)
                {
                    if (dicInputValues[name] == null)
                    {
                        sql = sql.Replace("@(" + name + ")", "");
                    }
                    else
                    {
                        sql = sql.Replace("@(" + name + ")", dicInputValues[name]);
                    }
                }
                else
                {
                    Regex reg = new Regex("&" + name + @"=(?<value>[^\?&=]*)", RegexOptions.IgnoreCase);
                    Match m   = reg.Match("&" + queryString);
                    if (m.Success == true)
                    {
                        sql = sql.Replace("@(" + name + ")", m.Result("${value}"));
                    }
                    else
                    {
                        throw new Exception("SQL语句中,@(" + name + ") 未找到定义");
                    }
                }

                match = match.NextMatch();
            }

            //替换筛选项
            if (filterList != null)
            {
                StringBuilder sbWhere = new StringBuilder();
                sbWhere.AppendLine("WHERE 1=1");
                int    filterCount = 0;
                string fieldName   = string.Empty;
                try
                {
                    foreach (QueryFilterItem filter in filterList)
                    {
                        fieldName = filter.Field;
                        if (string.IsNullOrWhiteSpace(filter.Value))
                        {
                            continue;
                        }
                        filterCount++;
                        Enums.FieldDataType  dataType = (Enums.FieldDataType)filter.DataType;
                        Enums.FilterOperator oper     = (Enums.FilterOperator)filter.Operator;
                        switch (dataType)
                        {
                        case Enums.FieldDataType.数值:
                            switch (oper)
                            {
                            case Enums.FilterOperator.小于:
                                sbWhere.AppendLine("AND " + filter.Field + "<?");
                                paramList.Add(Convert.ToDecimal(filter.Value));
                                break;

                            case Enums.FilterOperator.小于等于:
                                sbWhere.AppendLine("AND " + filter.Field + "<=?");
                                paramList.Add(Convert.ToDecimal(filter.Value));
                                break;

                            case Enums.FilterOperator.大于:
                                sbWhere.AppendLine("AND " + filter.Field + ">?");
                                paramList.Add(Convert.ToDecimal(filter.Value));
                                break;

                            case Enums.FilterOperator.大于等于:
                                sbWhere.AppendLine("AND " + filter.Field + ">=?");
                                paramList.Add(Convert.ToDecimal(filter.Value));
                                break;

                            case Enums.FilterOperator.等于:
                                sbWhere.AppendLine("AND " + filter.Field + ">=?");
                                paramList.Add(Convert.ToDecimal(filter.Value));
                                break;

                            case Enums.FilterOperator.介于:
                                sbWhere.AppendLine("AND " + filter.Field + ">? AND " + filter.Field + "<?");
                                string[] vdecs = filter.Value.Split(new string[] { " - " }, StringSplitOptions.RemoveEmptyEntries);
                                paramList.Add(Convert.ToDecimal(vdecs[0]));
                                paramList.Add(Convert.ToDecimal(vdecs[1]));
                                break;

                            case Enums.FilterOperator.集合:
                                string[] vdecl = filter.Value.Split(new char[] { ',', ',', '\r', '\n', ' ' }, StringSplitOptions.RemoveEmptyEntries);
                                foreach (string v in vdecl)
                                {
                                    Convert.ToDecimal(v);
                                }
                                sbWhere.AppendLine("AND " + filter.Field + " IN (" + string.Join(",", vdecl) + ")");
                                break;

                            default:
                                sbWhere.AppendLine("AND " + filter.Field + "=?");
                                paramList.Add(Convert.ToDecimal(filter.Value));
                                break;
                            }
                            break;

                        case Enums.FieldDataType.日期:
                            string[] vdates = filter.Value.Split(new string[] { " - " }, StringSplitOptions.RemoveEmptyEntries);
                            DateTime begin  = Convert.ToDateTime(vdates[0]);
                            DateTime end    = begin.AddDays(1);
                            if (vdates.Length > 1)
                            {
                                end = Convert.ToDateTime(vdates[1]).AddDays(1);
                            }
                            sbWhere.AppendLine("AND " + filter.Field + ">? AND " + filter.Field + "<?");
                            paramList.Add(begin);
                            paramList.Add(end);
                            break;

                        case Enums.FieldDataType.文本:
                            switch (oper)
                            {
                            case Enums.FilterOperator.包含:
                                sbWhere.AppendLine("AND " + filter.Field + " LIKE '%" + filter.Value.Replace('\'', '"') + "%'");
                                break;

                            case Enums.FilterOperator.集合:
                                string[]      vstrs = filter.Value.Split(new char[] { ',', ',', '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
                                List <string> vstrl = new List <string>();
                                foreach (var s in vstrs)
                                {
                                    vstrl.Add("'" + s.Replace('\'', '"') + "'");
                                }
                                sbWhere.AppendLine("AND " + filter.Field + " IN (" + string.Join(",", vstrl) + ")");
                                break;

                            default:
                                sbWhere.AppendLine("AND " + filter.Field + "=?");
                                paramList.Add(Convert.ToString(filter.Value));
                                break;
                            }
                            break;
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception("筛选项:" + fieldName + "出错:" + ex.ToString());
                }
                //if (filterCount > 0)  //为了性能,在没有筛选项的情况下直接添加排序子句,但原SQL中有ORDER BY的话会有BUG
                //{
                sql = "SELECT * FROM (\r\n" + sql + "\r\n)\r\n" + sbWhere.ToString();
                if (order != null && string.IsNullOrWhiteSpace(order.Field) == false)
                {
                    sql += " ORDER BY " + order.Field + (order.IsDesc ? " DESC" : " ASC");
                }
                //}
                //else
                //{
                //    if (order != null && string.IsNullOrWhiteSpace(order.Field) == false)
                //    {
                //        sql += "\r\nORDER BY " + order.Field + (order.IsDesc ? " DESC" : " ASC");
                //    }
                //}
            }
        }