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())); }
/// <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())); }
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); } }