public JsonResult TreeDatalist(JqGridPostData postDataModel)
{
Guard.Against.Null(postDataModel, nameof(postDataModel));
var result = _gridFormService.QueryPageDataResultView(postDataModel);
return(Json(result.GetJqGridTreeJsonData()));
}
/// <summary>
/// 导出excel数据
/// </summary>
/// <param name="model"></param>
/// <returns>filename</returns>
public string ExportExcelData(JqGridPostData model)
{
string tableName = model.QuerySet.TableName;
FapTable ftb = _dbContext.Table(tableName);
string fileName = $"{ftb.TableComment}_{UUIDUtils.Fid}.xlsx";
string filePath = Path.Combine(Environment.CurrentDirectory, FapPlatformConstants.TemporaryFolder, fileName);
Pageable pageable = AnalysisPostData(model);
pageable.QueryCols = model.QuerySet.ExportCols;
string sql = pageable.Wraper.MakeExportSql();
if (pageable.Parameters != null && pageable.Parameters.Count > 0)
{
foreach (var param in pageable.Parameters)
{
sql = sql.Replace("@" + param.Key, "'" + param.Value + "'");
}
}
ExportModel em = new ExportModel()
{
DataSql = sql, FileName = filePath, TableName = tableName, ExportCols = pageable.QueryCols
};
bool result = _officeService.ExportExcel(em);
return(result ? fileName : "");
}
public JsonResult ExportExcelTemplateAndData(JqGridPostData model)
{
string fileName = _gridFormService.ExportExcelData(model);
return(Json(new ResponseViewModel {
success = true, data = $"{FapPlatformConstants.TemporaryFolder}/{fileName}"
}));
}
public JsonResult EChart(ChartViewModel chartViewModel, JqGridPostData jqGridPostData)
{
var charResult = _gridFormService.EChart(chartViewModel, jqGridPostData);
return(Json(new ResponseViewModel()
{
success = true, data = charResult
}));
}
public void JqgridQueryOption()
{
JqGridPostData jqGridPostData = new JqGridPostData()
{
QuerySet = new Fap.Core.Infrastructure.Query.QuerySet {
TableName = "Employee", QueryCols = "*", Statsetlist = new List <Fap.Core.Infrastructure.Query.StatSet> {
new Fap.Core.Infrastructure.Query.StatSet {
Field = "Age", StatSymbol = Fap.Core.Infrastructure.Query.StatSymbolEnum.MAX
}
}
}
, Rows = 10, Page = 2, Sidx = "Id", Sord = "asc"
};
var rv = _gridFormService.QueryPageDataResultView(jqGridPostData);
Assert.Equal(10, rv.Records);
}
public PageDataResultView QueryPageDataResultView(JqGridPostData jqGridPostData)
{
Pageable pageable = AnalysisPostData(jqGridPostData);
//queryOption.Where = AnalysisWhere(queryOption.Where);
PageDataResultView result = QueryPagedDynamicData();
return(result);
PageDataResultView QueryPagedDynamicData()
{
try
{
IEnumerable <FapColumn> fapColumns = _dbContext.Columns(jqGridPostData.QuerySet.TableName);
PageInfo <dynamic> pi = _dbContext.QueryPage(pageable);
//组装成DataResultView对象
PageDataResultView dataResultView = new PageDataResultView();
dataResultView.Data = pi.Items.ToFapDynamicObjectList(fapColumns);
//当未获取数据的时候才获取默认值
//if (!dataObject.Data.Any())
//{
//wyf表单应用，表格暂时不用取默认值
//dataResultView.DefaultData = queryOption.Wraper.GetDefaultData();
//}
dataResultView.DataJson = JsonConvert.SerializeObject(pi.Items);
dataResultView.TotalCount = pi.TotalCount;
dataResultView.CurrentPage = pi.CurrentPage;
dataResultView.OrginData = pi.Items;
dataResultView.DataListForJqGrid = pi.Items;// as IEnumerable<IDictionary<string, object>>;
dataResultView.PageSize = pi.PageSize;
//统计字段
dataResultView.StatFieldData = pi.StatFieldData;
dataResultView.StatFieldDataJson = JsonConvert.SerializeObject(pi.StatFieldData);
return(dataResultView);
}
catch (Exception)
{
throw;
}
}
}
public ActionResult GetUsers(JqGridPostData jqGridPostData)
{
try
{
JqGridSearchModel search = new JqGridSearchModel();
search.Search = jqGridPostData.Search;
search.Filters = jqGridPostData.GetFilter();
search.PageSize = jqGridPostData.PageSize;
search.SortColumn = jqGridPostData.SortColumn;
search.SortOrder = jqGridPostData.SortOrder;
search.Page = jqGridPostData.Page;
var result = service.GetUsers(search);
return(Json(result, JsonRequestBehavior.AllowGet));
}
catch (Exception e)
{
throw e;
}
}
public PageDataResultView QueryPageDataResultView(JqGridPostData jqGridPostData)
{
Pageable pageable = AnalysisPostData(jqGridPostData);
//queryOption.Where = AnalysisWhere(queryOption.Where);
PageDataResultView result = QueryPagedDynamicData();
return(result);
PageDataResultView QueryPagedDynamicData()
{
try
{
IEnumerable <FapColumn> fapColumns = _dbContext.Columns(jqGridPostData.QuerySet.TableName);
PageInfo <dynamic> pi = _dbContext.QueryPage(pageable);
//组装成DataResultView对象
PageDataResultView dataResultView = new PageDataResultView();
dataResultView.Data = pi.Items.ToFapDynamicObjectList(fapColumns);
dataResultView.DataJson = JsonConvert.SerializeObject(pi.Items);
dataResultView.TotalCount = pi.TotalCount;
dataResultView.CurrentPage = pi.CurrentPage;
dataResultView.OrginData = pi.Items;
dataResultView.DataListForJqGrid = pi.Items;//暂不启用加密设置 GetEncryptData(jqGridPostData.QuerySet.TableName, pi.Items as IEnumerable<IDictionary<string, object>>);
dataResultView.PageSize = pi.PageSize;
//统计字段
dataResultView.StatFieldData = pi.StatFieldData;
dataResultView.StatFieldDataJson = JsonConvert.SerializeObject(pi.StatFieldData);
return(dataResultView);
}
catch (Exception)
{
throw;
}
}
}
public ChartResult EChart(ChartViewModel chartViewModel, JqGridPostData jqGridPostData)
{
Guard.Against.NullOrEmpty(chartViewModel.Groups, nameof(chartViewModel.Groups));
string tableName = jqGridPostData.QuerySet.TableName;
//页面级条件
JsonFilterToSql jfs = new JsonFilterToSql(_dbContext);
List <string> lwhere = new List <string>();
if (jqGridPostData.PageCondition.IsPresent())
{
lwhere.Add(jfs.BuilderFilter(tableName, jqGridPostData.PageCondition));
}
//构造jqgrid过滤条件
if (jqGridPostData.Filters.IsPresent())
{
var filters = jfs.BuilderFilter(tableName, jqGridPostData.Filters);
if (filters.IsPresent())
{
lwhere.Add(filters);
}
}
if (jqGridPostData.QuerySet.GlobalWhere.IsPresent())
{
lwhere.Add(jqGridPostData.QuerySet.GlobalWhere);
}
string where = $" where {tableName}.EnableDate<='{DateTimeUtils.CurrentDateTimeStr}' and {tableName}.DisableDate>='{DateTimeUtils.CurrentDateTimeStr}' and {tableName}.Dr=0 ";
if (lwhere.Count > 0)
{
where += " and " + string.Join(" and ", lwhere);
}
List <string> groupByList = new List <string>();
List <string> colNameList = new List <string>();
BuildGroupBy(chartViewModel.Groups, groupByList, colNameList);
string colName = string.Join(',', colNameList);
string groupBy = $" group by {string.Join(',', groupByList)}";
//统计列
List <string> aggCols = new List <string>();
//存放ccount得sql
Dictionary <string, string> ccSqlDics = new Dictionary <string, string>();
//统计项
List <Aggregate> agglist = new List <Aggregate>();
if (chartViewModel.Aggregates != null)
{
foreach (var aggregate in chartViewModel.Aggregates)
{
if (aggregate.AggType == AggregateEnum.CCOUNT)
{
//特殊处理CCount
ccSqlDics.Add(aggregate.Field, $"select {colName},{aggregate.Field},COUNT({aggregate.Field}) as C from {tableName} {where} {groupBy},{aggregate.Field}");
}
else
{
aggCols.Add($"{aggregate.AggType}({aggregate.Field}) as '{aggregate.Alias}'");
}
agglist.Add(aggregate);
}
}
string sql = $"select {colName} from {tableName} {where} {groupBy}";
if (aggCols.Count > 0)
{
sql = $"select {colName},{string.Join(',', aggCols)} from {tableName} {where} {groupBy}";
}
DynamicParameters param = new DynamicParameters();
foreach (var p in jqGridPostData.QuerySet.Parameters)
{
param.Add(p.ParamKey, p.ParamValue);
}
sql = sql.ReplaceIgnoreCase("query", "select ");
var dataList = _dbContext.QueryOriSql(sql, param);
DataProcessed(ccSqlDics, tableName, chartViewModel.Groups, dataList, agglist);
return(new ChartResult {
Aggregates = agglist, DataSet = dataList
});;
}
public Pageable AnalysisPostData(JqGridPostData jqGridPostData)
{
IEnumerable <FapColumn> fapColumns = _dbContext.Columns(jqGridPostData.QuerySet.TableName);
jqGridPostData.Filters = jqGridPostData.Filters.IsPresent() ? jqGridPostData.Filters.Replace("query ", "select ") : "";
//矫正当前页为0的情况
if (jqGridPostData.Page < 0)
{
jqGridPostData.Page = 1;
}
QuerySet qs = jqGridPostData.QuerySet;
Pageable pageable = new Pageable(_dbContext)
{
TableName = qs.TableName, QueryCols = qs.QueryCols, HistoryTimePoint = jqGridPostData.TimePoint
};
//设置统计
if (qs.Statsetlist != null && qs.Statsetlist.Any())
{
pageable.AddStatField(qs.Statsetlist);
}
if (qs.Parameters != null && qs.Parameters.Count > 0)
{
foreach (var param in qs.Parameters)
{
pageable.AddParameter(param.ParamKey, param.ParamValue);
}
}
//优先级高
if (jqGridPostData.Sidx.IsPresent())
{
var sidxs = jqGridPostData.Sidx.SplitComma();
foreach (var sidx in sidxs)
{
if (sidx.IsPresent())
{
string[] odx = sidx.Trim().Split(' ');
if (odx != null)
{
var col = fapColumns.First(f => f.ColName == odx[0]);
string colName = col.ColName;
if (col.CtrlType == FapColumn.CTRL_TYPE_REFERENCE)
{
colName += "MC";
}
if (odx.Length > 1)
{
pageable.OrderBy.AddOrderByCondtion(colName, odx[1]);
}
else
{
pageable.OrderBy.AddOrderByCondtion(colName, jqGridPostData.Sord);
}
}
}
}
}
if (qs.OrderByList != null && qs.OrderByList.Count > 0)
{
foreach (var orderby in qs.OrderByList)
{
pageable.OrderBy.AddOrderByCondtion(orderby.Field, orderby.Direction);
}
}
//构造初始化条件,如果没有过滤条件，又设置了初始化条件则设置初始化条件。或者设置了过滤条件且初始化条件为全局条件则同样设置where条件
if (qs.GlobalWhere.IsPresent())
{
pageable.AddWhere(qs.GlobalWhere);
}
if (jqGridPostData.Filters.IsMissing() && qs.InitWhere.IsPresent())
{
pageable.AddWhere(qs.InitWhere);
}
//页面级条件
JsonFilterToSql jfs = new JsonFilterToSql(_dbContext);
if (jqGridPostData.PageCondition.IsPresent())
{
pageable.AddWhere(jfs.BuilderFilter(pageable.TableName, jqGridPostData.PageCondition), QuerySymbolEnum.AND);
}
//构造jqgrid过滤条件
if (jqGridPostData.Filters.IsPresent())
{
pageable.AddWhere(jfs.BuilderFilter(pageable.TableName, jqGridPostData.Filters), QuerySymbolEnum.AND);
//string filterWhere = JsonFilterToSql.BuildFilterCondition(fapColumns, jqGridPostData.Filters);
}
//事件处理
//actionSimplepageable?.Invoke(pageable);
pageable.CurrentPage = jqGridPostData.Page;
pageable.PageSize = jqGridPostData.Rows;
//数据权限
string dataWhere = DataWhere();
if (dataWhere.IsPresent())
{
pageable.AddWhere(dataWhere);
}
//解析条件
pageable.Where = AnalysisWhere(pageable.Where);
return(pageable);
string AnalysisWhere(string where)
{
if (where.IsMissing())
{
return("");
}
//获得安全sql
where = where.FilterDangerSql();
//替换部门权限占位符
if (where.IndexOf(FapPlatformConstants.DepartmentAuthority) > -1)
{
where = where.Replace(FapPlatformConstants.DepartmentAuthority, DeptWhere());
}
return(where.ReplaceIgnoreCase("query", "select "));
}
string DeptWhere()
{
var roledepts = _rbacService.GetRoleDeptList(_applicationContext.CurrentRoleUid);
if (roledepts.Any())
{
return(string.Join(",", roledepts.Select(d => "'" + d.DeptUid + "'")));
}
else
{
return("'meiyou'");
}
}
//数据权限
string DataWhere()
{
string where = string.Empty;
var roleDatas = _rbacService.GetRoleDataList(_applicationContext.CurrentRoleUid);
if (roleDatas != null && roleDatas.Any())
{
var rd = roleDatas.FirstOrDefault <FapRoleData>(r => r.TableUid == qs.TableName);
if (rd != null)
{
where = rd.SqlCondition;
string pattern = FapPlatformConstants.VariablePattern;
Regex reg = new Regex(pattern);
MatchCollection matchs = reg.Matches(where);
foreach (var mtch in matchs)
{
int length = mtch.ToString().Length - 3;
string colName = mtch.ToString().Substring(2, length);
if (colName.EqualsWithIgnoreCase("DeptUid"))
{
where = where.Replace(mtch.ToString(), _applicationContext.DeptUid);
}
else if (colName.EqualsWithIgnoreCase("CurrentRoleUid"))
{
where = where.Replace(mtch.ToString(), _applicationContext.CurrentRoleUid);
}
else if (colName.EqualsWithIgnoreCase("EmpUid"))
{
where = where.Replace(mtch.ToString(), _applicationContext.EmpUid);
}
else if (colName.EqualsWithIgnoreCase("DeptCode"))
{
string deptCode = _applicationContext.DeptCode;
if (deptCode.IsMissing())
{
OrgDept dept = _dbContext.Get <OrgDept>(_applicationContext.DeptUid);
deptCode = dept.DeptCode;
}
where = where.Replace(mtch.ToString(), deptCode);
}
}
}
}
return(where);
}
}
