public JsonResult Objectives(string schemeUid, string objective, string filter)
{
JsonFilterToSql jfs = new JsonFilterToSql(_dbContext);
string filterWhere = jfs.BuilderFilter(objective, filter);
string sql = string.Empty;
if (objective.EqualsWithIgnoreCase("Employee"))
{
if (!filterWhere.Contains("IsMainJob", StringComparison.OrdinalIgnoreCase))
{
filterWhere = filterWhere.IsMissing() ? " IsMainJob=1" : filterWhere + " and IsMainJob=1";
}
sql = $"select Fid ObjUid,EmpCode as ObjCode,EmpName as ObjName,'{schemeUid}' ProgramUid from Employee where " + filterWhere;
}
else
{
sql = $"select Fid ObjUid,DeptCode as ObjCode,DeptName as ObjName,'{schemeUid}' ProgramUid from OrgDept where " + filterWhere;
}
if (sql.IsMissing())
{
Guard.Against.FapBusiness("不存在此类考核对象类型");
}
sql += " and Fid not in(select ObjUid from PerfObject where ProgramUid=@SchemeUid)";
var objectives = _dbContext.Query <PerfObject>(sql, new Dapper.DynamicParameters(new { SchemeUid = schemeUid }));
if (objectives.Any())
{
_dbContext.InsertBatchSql(objectives);
}
return(Json(ResponseViewModelUtils.Sueecss()));
}
public IActionResult Details(string fid)
{
var survey = _dbContext.Get <Survey>(fid, true);
var filter = _dbContext.QueryFirstOrDefaultWhere <SurFilter>("SurveyUid=@SurveyUid", new Dapper.DynamicParameters(new { SurveyUid = fid }));
string condition = filter.FilterCondition;
if (condition.IsMissing())
{
survey.FilterModelMC = "全部";
}
JsonFilterToSql d = new JsonFilterToSql(_dbContext);
IEnumerable <FilterDescModel> filterDesc = d.BuilderFilterDesc("Employee", condition);
ViewBag.SurFilter = filter;
ViewBag.Filters = filterDesc;
return(View(survey));
}
/// <summary>
/// 构造条件描述
/// </summary>
/// <param name="tableName"></param>
/// <param name="filter"></param>
/// <returns></returns>
public static IEnumerable <JqGridFilterDescViewModel> BuilderFilterDesc(string tableName, string filter, IDbContext dbContext)
{
if (filter.IsPresent())
{
JsonFilterToSql jfs = new JsonFilterToSql(dbContext);
var filterDescModel = jfs.BuilderFilterDesc(tableName, filter);
//Dictionary<string, List<JqGridFilterDescViewModel>> dicList = new Dictionary<string, List<JqGridFilterDescViewModel>>();
List <JqGridFilterDescViewModel> sqlBuilder = new List <JqGridFilterDescViewModel>();
foreach (var m in filterDescModel)
{
yield return(new JqGridFilterDescViewModel {
FilterDesc = m.FilterDesc, FilterOper = m.FilterOper, FilterResult = m.FilterResult, Group = m.Group
});
}
}
}
/// <summary>
/// 提醒到保险
/// </summary>
/// <param name="da"></param>
/// <param name="rule"></param>
/// <param name="billData">单据数据</param>
/// <param name="bizObject">业务数据</param>
private void NotifyInsurance(CfgBillWriteBackRule rule, FapDynamicObject billData, FapDynamicObject bizObject)
{
//影响工资同时影响保险,业务单据必须是Employee
if (rule.IsNotifyPayroll == 1 && rule.BizEntityUid == "Employee")
{
string insCaseSql = "select Fid,EmpCondition from InsCase where TableName!=''";
var insCases = _dbContext.Query(insCaseSql);
if (insCases == null || insCases.Count() < 1)
{
return;
}
bizObject.TryGetValue("Fid", out object empUid);
Employee empInfo = _dbContext.Get <Employee>(empUid.ToString());
List <InsToDo> insToDos = new List <InsToDo>();
foreach (var cs in insCases)
{
//解析保险组中的员工条件
string strWhere = "Fid=@Fid";
JsonFilterToSql jsont2sql = new JsonFilterToSql(_dbContext);
string insWhere = jsont2sql.BuilderFilter("Employee", cs.EmpCondition);
if (insWhere.IsPresent())
{
strWhere += " and " + insWhere;
}
DynamicParameters param = new DynamicParameters();
param.Add("Fid", empUid);
if (_dbContext.Count("Employee", strWhere, param) > 0)
{
string caseUid = cs.Fid;
var insToDo = BuilderInsToDo(rule, billData, empInfo, caseUid);
insToDos.Add(insToDo);
}
}
if (insToDos.Count > 0)
{
_dbContext.InsertBatch(insToDos);
}
else
{
var insToDo = BuilderInsToDo(rule, billData, empInfo, "");
_dbContext.Insert(insToDo);
}
}
}
/// <summary>
/// 提醒到工资
/// </summary>
/// <param name="da"></param>
/// <param name="rule"></param>
/// <param name="billData">单据数据</param>
/// <param name="bizObject">业务数据</param>
private void NotifyPayroll(CfgBillWriteBackRule rule, FapDynamicObject billData, FapDynamicObject bizObject)
{
//影响工资,业务单据必须是Employee
if (rule.IsNotifyPayroll == 1 && rule.BizEntityUid == "Employee")
{
string payCaseSql = "select Fid,EmpCondition from PayCase where TableName!=''";
var payCases = _dbContext.Query(payCaseSql);
if (payCases == null || payCases.Count() < 1)
{
return;
}
string empUid = bizObject.Get("Fid").ToString();
Employee empInfo = _dbContext.Get <Employee>(empUid);
List <PayToDo> payToDos = new List <PayToDo>();
foreach (var pc in payCases)
{
//解析工资套中的员工条件
string strWhere = "Fid=@Fid";
JsonFilterToSql jsont2sql = new JsonFilterToSql(_dbContext);
string payWhere = jsont2sql.BuilderFilter("Employee", pc.EmpCondition);
if (payWhere.IsPresent())
{
strWhere += " and " + payWhere;
}
DynamicParameters param = new DynamicParameters();
param.Add("Fid", empUid);
if (_dbContext.Count("Employee", strWhere, param) > 0)
{
string caseUid = pc.Fid;
var payToDo = BuilderPayToDo(rule, billData, empInfo, caseUid);
payToDos.Add(payToDo);
}
}
if (payToDos.Count > 0)
{
_dbContext.InsertBatch(payToDos);
}
else
{
var payToDo = BuilderPayToDo(rule, billData, empInfo, "");
_dbContext.Insert(payToDo);
}
}
}
public JsonResult InitInscaseEmployee(string caseUid)
{
var insCase = _dbContext.Get <InsCase>(caseUid);
if (insCase.TableName.IsMissing())
{
return(Json(ResponseViewModelUtils.Failure("请先生成保险项")));
}
if (insCase.EmpCondition.IsMissing())
{
return(Json(ResponseViewModelUtils.Failure("请先保存员工条件")));
}
if (insCase.Unchanged == 1)
{
return(Json(ResponseViewModelUtils.Failure("已经存在保险记录，不能再初始化员工了")));
}
JsonFilterToSql jfs = new JsonFilterToSql(_dbContext);
string filterWhere = jfs.BuilderFilter("Employee", insCase.EmpCondition);
_insuranceService.InitEmployeeToInsCase(insCase, filterWhere);
return(Json(ResponseViewModelUtils.Sueecss()));
}
public JsonResult InitPaycaseEmployee(string caseUid)
{
var payCase = _dbContext.Get <PayCase>(caseUid);
if (payCase.TableName.IsMissing())
{
return(Json(ResponseViewModelUtils.Failure("请先生成薪资项")));
}
if (payCase.EmpCondition.IsMissing())
{
return(Json(ResponseViewModelUtils.Failure("请先保存员工条件")));
}
if (payCase.Unchanged == 1)
{
return(Json(ResponseViewModelUtils.Failure("已经有发放记录，不能再初始化员工了")));
}
JsonFilterToSql jfs = new JsonFilterToSql(_dbContext);
string filterWhere = jfs.BuilderFilter("Employee", payCase.EmpCondition);
_payrollService.InitEmployeeToPayCase(payCase, filterWhere);
return(Json(ResponseViewModelUtils.Sueecss()));
}
/// <summary>
/// 构造条件描述
/// </summary>
/// <param name="tableName"></param>
/// <param name="filter"></param>
/// <returns></returns>
public static List <JqGridFilterDescViewModel> BuilderFilterDesc(string tableName, string filter, IDbContext dbContext)
{
if (string.IsNullOrWhiteSpace(filter))
{
return(null);
}
JsonFilterToSql jfs = new JsonFilterToSql(dbContext);
var filterDescModel = jfs.BuilderFilterDesc(tableName, filter);
//Dictionary<string, List<JqGridFilterDescViewModel>> dicList = new Dictionary<string, List<JqGridFilterDescViewModel>>();
List <JqGridFilterDescViewModel> sqlBuilder = new List <JqGridFilterDescViewModel>();
if (filterDescModel != null)
{
filterDescModel.ForEach((m) =>
{
sqlBuilder.Add(new JqGridFilterDescViewModel {
FilterDesc = m.FilterDesc, FilterOper = m.FilterOper, FilterResult = m.FilterResult, Group = m.Group
});
});
}
return(sqlBuilder);
}
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);
}
}
