分享U9C存储过程客开报表支持排序

需求：实现U9C客开存储过程报表使用官方查询对话框里的排序功能

实现步骤

1.UBF添加查询条件+配置排序字段

• 例如查询条件名称：sorting

2.存储过程添加一参数排序字段

• 例如字段：@sorting

CREATE OR ALTER PROCEDURE dbo.proc_KKReport1057 
@org BIGINT = 1002403140002436
,@sorting   NVARCHAR(MAX) = NULL
AS
BEGIN

-- 查询结果插入临时表  #temp1 

    DECLARE @sql NVARCHAR(MAX) = N'' 
    SET @sql = N' SELECT * FROM #temp1  '+ISNULL(@sorting,'');
    EXEC (@sql);
    PRINT (@sql);


END

3.报表策略读取排序条件转为排序SQL

• 报表策略代码

 public override string VerifyParameters(Case usercase, CaseDefine caseDefine)
 {
     var logger = UFSoft.UBF.Util.Log.LoggerManager.GetLogger("KKReport1057@" + DateTime.Now.ToString("yyyyMMddHHmmss"));

     string note = base.VerifyParameters(usercase, caseDefine);
     if (note != null)
         return note;


     logger.Error("执行KKReport1057");

     var sorting = usercase.FilterValues.GetObjectByName("sorting");
     if (sorting == null) //查询对话框不显示这个查询条件，就会导致null
     {
         var t1 = new UFSoft.UBF.Report.Filter.FilterModel.FilterValue();
         t1.Name = "sorting";
         t1.FilterItemID = usercase.FilterValues.Count+1;
         t1.LogicOperator = enuOperatorListType.And;
         t1.PageType = enuPageOfInputFilterValueType.basicPage;
         t1.ValueType = enumFilterValueType.InputValue;
         t1.ReferenceType = enuReferenceType.nothing;
         t1.Values.Values = new List<string>();
         t1.Values.Values.Add("11");
         t1.Values.Labels= new List<string>();
         t1.Values.Labels.Add("排序");
         t1.Values.ReferenceValues = new List<ReferenceValue>();
         t1.Values.Type = enumFilterValueType.InputValue;

         usercase.FilterValues.Add(t1);
         sorting = usercase.FilterValues.GetObjectByName("sorting");
     }
    
     sorting.Values.Values[0] = "";
     if (usercase.Sorts != null && usercase.Sorts.Count > 0)
     {
         logger.Error("触发输出排序SQL");
         StringBuilder orderByBuilder = new StringBuilder(" ORDER BY ");
         foreach (var sort in usercase.Sorts)
         {
             if (!string.IsNullOrWhiteSpace(sort.Item))
             {
                 orderByBuilder.Append($"{sort.Item} {(sort.IsAsc ? "ASC" : "DESC")}, ");
             }
         }
         if (orderByBuilder.Length > 0)
         {
             orderByBuilder.Length -= 2;
         }

         logger.Error("输出排序SQL是："+orderByBuilder.ToString());
         sorting.Values.Values[0] = orderByBuilder.ToString();
     }

     return null;
 }

• 部署报表策略+重启IIS+更新存储过程，大功告成！