简单报表，过滤条件处理常用方法

场景：

        简单报表开发对过滤条件进行处理，对处理代码进行提炼。

1、调用示例代码

        /// <summary>
        /// 构造取数Sql，取数据填充到临时表：tableName
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="tableName"></param>
        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            StringBuilder whereSqlInside = new StringBuilder();
            whereSqlInside.Append("Where 1=1 ");

            DynamicObject customFilter = filter.FilterParameter.CustomFilter;

            //多选基础资料
            GetFilterFromMulBaseInfo(whereSqlInside, customFilter, "FMaterialId", "Name", "mH_L.FName");              //商品
            
            //单选基础资料
            GetFilterFromBaseInfo(whereSqlInside, customFilter, "FSupplyId", "Name", "supH_L.FName");           //供应商
            
            //日期
            GetFilterFromDateInfo(whereSqlInside, customFilter, "FStartTime", "retailH.FStartTime");      //销售日期
        
            ......（代码主体）
        
        }

2、单选基础资料

        /// <summary>
        /// 单选基础资料的过滤条件sql语句
        /// </summary>
        /// <param name="customFilter"></param>
        /// <param name="filterName"></param>
        /// <param name="valueFilterName"></param>
        /// <param name="sqlWhereFieldName"></param>
        /// <returns></returns>
        private static void GetFilterFromBaseInfo(StringBuilder whereSql, DynamicObject customFilter, string filterName, string valueFilterName, string sqlWhereFieldName)
        {

            if (valueFilterName == string.Empty)
            {
                valueFilterName = "Id";
            }

            List<string> ListFBaseInfoID = new List<string>();
            DynamicObject FBaseInfoID = customFilter[filterName] as DynamicObject;
            if (FBaseInfoID != null)
            {
                whereSql.AppendFormat(" and ({0})", string.Format("{0} = '{1}'", sqlWhereFieldName, Convert.ToString(FBaseInfoID[valueFilterName])));
            }

        }

3、多选基础资料

        /// <summary>
        /// 多选基础资料的过滤条件sql语句
        /// </summary>
        /// <param name="customFilter"></param>
        /// <param name="filterName"></param>
        /// <param name="valueFilterName"></param>
        /// <param name="sqlWhereFieldName"></param>
        /// <returns></returns>
        private static void GetFilterFromMulBaseInfo(StringBuilder whereSql, DynamicObject customFilter, string filterName, string valueFilterName, string sqlWhereFieldName)
        {

            if (valueFilterName == string.Empty)
            {
                valueFilterName = "Id";
            }

            List<string> ListFBaseInfoID = new List<string>();
            DynamicObjectCollection FBaseInfoIDList = customFilter[filterName] as DynamicObjectCollection;
            foreach (DynamicObject FBaseInfoID in FBaseInfoIDList)
            {
                DynamicObject baseInfo = FBaseInfoID[filterName] as DynamicObject;
                ListFBaseInfoID.Add(Convert.ToString(baseInfo[valueFilterName]));
            }
            if (ListFBaseInfoID.Count > 0)
            {
                whereSql.AppendFormat(" and ({0})", string.Format("{0} in('{1}')", sqlWhereFieldName, string.Join("','", ListFBaseInfoID)));
            }

        }

4、日期（字段命名固定使用 xxx1、xxx2）

        /// <summary>
        /// 组织日期的过滤条件sql语句
        /// </summary>
        /// <param name="whereSql"></param>
        /// <param name="customFilter"></param>
        /// <param name="filterName"></param>
        /// <param name="sqlWhereFieldName"></param>
        private static void GetFilterFromDateInfo(StringBuilder whereSql, DynamicObject customFilter, string filterName, string sqlWhereFieldName)
        {

            string filterName1 = filterName + "1";
            string filterName2 = filterName + "2";

            string whereFilterSql1 = string.Empty;
            string filterValue1 = Convert.ToString(customFilter[filterName1]);
            if (!(customFilter[filterName1] == null || string.IsNullOrWhiteSpace(filterValue1)))
            {
                whereFilterSql1 = string.Format("DATEDIFF(DAY, '{1}', {0})>=0", sqlWhereFieldName, filterValue1);
                whereSql.AppendFormat(" and ({0}) ", whereFilterSql1);
            }

            string whereFilterSql2 = string.Empty;
            string filterValue2 = Convert.ToString(customFilter[filterName2]);
            if (!(customFilter[filterName2] == null || string.IsNullOrWhiteSpace(filterValue2)))
            {
                whereFilterSql2 = string.Format("DATEDIFF(DAY, {0}, '{1}')>=0", sqlWhereFieldName, filterValue2);
                whereSql.AppendFormat(" and ({0}) ", whereFilterSql2);
            }

        }

5、文本（完全匹配）

        /// <summary>
        /// 文本的过滤条件sql语句
        /// </summary>
        /// <param name="whereSql"></param>
        /// <param name="customFilter"></param>
        /// <param name="filterName"></param>
        /// <param name="sqlWhereFieldName"></param>
        private static void GetFilterFromTest(StringBuilder whereSql, DynamicObject customFilter, string filterName, string sqlWhereFieldName)
        {
            if(customFilter[filterName] != null)
            {
                string filterValue = customFilter[filterName].ToString();
                if (string.IsNullOrWhiteSpace(filterValue) == false)
                {
                    filterValue = filterValue.Replace(" ", "");
                    whereSql.AppendFormat(" and ({0})", string.Format("{0} = '{1}'", sqlWhereFieldName, filterValue));
                }
            }
        }

6、字符串下拉列表

        /// <summary>
        /// 字符串下拉列表的过滤条件sql语句
        /// </summary>
        /// <param name="whereSql"></param>
        /// <param name="customFilter"></param>
        /// <param name="filterName"></param>
        /// <param name="sqlWhereFieldName"></param>
        private static void GetFilterFromStringList(StringBuilder whereSql, DynamicObject customFilter, string filterName, string sqlWhereFieldName)
        {
            if (customFilter[filterName] != null)
            {
                string filterValue = customFilter[filterName].ToString();
                if (string.IsNullOrWhiteSpace(filterValue) == false)
                {
                    filterValue = filterValue.Replace(" ", "");
                    filterValue = "'" + filterValue.Replace(",", "','") + "'";
                    whereSql.AppendFormat(" and ({0})", string.Format("{0} in({1})", sqlWhereFieldName, filterValue));
                }
            }
        }