/// <summary> /// 取消发放 /// </summary> /// <param name="caseUid"></param> /// <returns></returns> public void PayrollOffCancel(string caseUid) { PayCase pc = _dbContext.Get <PayCase>(caseUid); if (pc.PayFlag == 0) { throw new FapException("薪资未发放,不用取消"); } pc.PayFlag = 0; if (pc.InitYM == pc.PayYM && pc.PayCount == 1) { pc.Unchanged = 0; } _dbContext.Update(pc); //获取发放记录 DynamicParameters paramR = new DynamicParameters(); paramR.Add("PayYM", pc.PayYM); paramR.Add("PayCount", pc.PayCount); paramR.Add("CaseUid", pc.Fid); PayRecord pr = _dbContext.QueryFirstOrDefaultWhere <PayRecord>("PayYM=@PayYM and PayCount=@PayCount and CaseUid=@CaseUid and PayFlag=1", paramR); if (pr != null) { pr.PayFlag = 0; pr.PayDate = ""; pr.PayEmpUid = ""; _dbContext.Update(pr); } string deleteSql = "delete from PayCenter where PayCaseUid=@CaseUid and PaymentTimes=@PayTimes and PayYM=@PayYM"; string updateSql = string.Format("update {0} set PayConfirm=0", pc.TableName); _dbContext.Execute(deleteSql, new DynamicParameters(new { CaseUid = pc.Fid, PayTimes = pc.PayCount, PayYM = pc.PayYM })); _dbContext.Execute(updateSql); }
public void InitEmployeeToPayCase(PayCase payCase, string empWhere) { if (payCase.Unchanged == 1) { throw new FapException("已存在薪资方法记录,不能再初始化员工"); } if (!empWhere.Contains("IsMainJob", StringComparison.OrdinalIgnoreCase)) { empWhere = empWhere.IsMissing() ? " IsMainJob=1" : empWhere + " and IsMainJob=1"; } var employees = _dbContext.QueryWhere <Employee>(empWhere); IList <FapDynamicObject> listCase = new List <FapDynamicObject>(); foreach (var emp in employees) { FapDynamicObject fdo = new FapDynamicObject(_dbContext.Columns(payCase.TableName)); fdo.SetValue("EmpUid", emp.Fid); fdo.SetValue("EmpCode", emp.EmpCode); fdo.SetValue("EmpCategory", emp.EmpCategory); fdo.SetValue("DeptUid", emp.DeptUid); fdo.SetValue("PayCaseUid", payCase.Fid); fdo.SetValue("PayYM", payCase.PayYM.IsPresent() ? payCase.PayYM : payCase.InitYM); fdo.SetValue("PaymentTimes", 1); fdo.SetValue("PayConfirm", 0); listCase.Add(fdo); } _dbContext.ExecuteOriginal($"truncate table {payCase.TableName}"); _dbContext.InsertDynamicDataBatchSql(listCase); //添加发放记录 var records = _dbContext.DeleteExec(nameof(PayRecord), "CaseUid=@CaseUid" , new DynamicParameters(new { CaseUid = payCase.Fid })); PayRecord pr = new PayRecord(); pr.CaseUid = payCase.Fid; pr.PayCount = 1; pr.PayYM = payCase.InitYM; pr.PayFlag = 0; _dbContext.Insert <PayRecord>(pr); //更新薪资套本次发放内容 payCase.PayYM = payCase.InitYM; payCase.PayCount = 1; payCase.PayFlag = 0; _dbContext.Update(payCase); }
public GapEmployee PayGapAnalysis(string recordUid) { PayRecord payRecord = _dbContext.Get <PayRecord>(recordUid); PayCase payCase = _dbContext.Get <PayCase>(payRecord.CaseUid); //当前月有 历史数据没有的语句(入职的) string sql1 = string.Format($"select EmpUid from {payCase.TableName} where PaymentTimes={payCase.PayCount} and EmpUid NOT IN(select EmpUid from {PAYROLLCENTER} where PayYM='{payRecord.PayYM}' and PayCaseUid='{payRecord.CaseUid}' and PaymentTimes={payRecord.PayCount})"); //当前月没有 历史数据有的(离职的) string sql2 = string.Format($"select EmpUid from {PAYROLLCENTER} where PayYM='{payRecord.PayYM}' and PayCaseUid='{payRecord.CaseUid}' and PaymentTimes={payRecord.PayCount} and EmpUid NOT IN(select EmpUid from {payCase.TableName} where PaymentTimes={payCase.PayCount})"); var list1 = _dbContext.QueryOriSql(sql1); var list2 = _dbContext.QueryOriSql(sql2); GapEmployee emps = new GapEmployee(); if (list1.Any()) { emps.AddedList = _dbContext.QueryWhere <Employee>("Fid in @Fids", new DynamicParameters(new { Fids = list1.Select(l => l.EmpUid) })); } if (list2.Any()) { emps.RemovedList = _dbContext.QueryWhere <Employee>("Fid in @Fids", new DynamicParameters(new { Fids = list2.Select(l => l.EmpUid) })); } return(emps); }
public void PayrollOff(string caseUid) { PayCase payCase = _dbContext.Get <PayCase>(caseUid); if (payCase.PayFlag == 1) { throw new FapException("薪资已经发放,请不要重复发放"); } payCase.PayFlag = 1; //标记已存在发放,不能再设置薪资套 payCase.Unchanged = 1; _dbContext.Update(payCase); //获取发放记录 DynamicParameters paramR = new DynamicParameters(); paramR.Add("PayYM", payCase.PayYM); paramR.Add("PayCount", payCase.PayCount); paramR.Add("CaseUid", payCase.Fid); PayRecord payRecord = _dbContext.QueryFirstOrDefaultWhere <PayRecord>("PayYM=@PayYM and PayCount=@PayCount and CaseUid=@CaseUid and PayFlag=0", paramR); if (payRecord != null) { payRecord.PayFlag = 1; payRecord.PayEmpUid = _applicationContext.EmpUid; payRecord.PayDate = DateTimeUtils.CurrentDateTimeStr; _dbContext.Update(payRecord); } var caseCols = _dbContext.Columns(payCase.TableName) .Where(c => !c.ColName.EqualsWithIgnoreCase("Id")) .Select(c => c.ColName); string cols = string.Join(",", caseCols); string insertSql = string.Format("insert into {0}({1}) (select {1} from {2})", "PayCenter", cols, payCase.TableName); _dbContext.ExecuteOriginal(insertSql); string updateSql = string.Format("update {0} set PayConfirm=1", payCase.TableName); _dbContext.ExecuteOriginal(updateSql); }
public void InitPayrollData(InitDataViewModel payrollInitData) { PayCase payCase = _dbContext.Get <PayCase>(payrollInitData.CaseUid); PayRecord pRecord = _dbContext.Get <PayRecord>(payrollInitData.RecordUid); string where = " PayYM='" + pRecord.PayYM + "' and PayCaseUid='" + pRecord.CaseUid + "' and PaymentTimes=" + pRecord.PayCount; var colList = _dbContext.Columns(payCase.TableName); //基础列和 特性列 List <FapColumn> baseCols = colList.Where(c => (c.IsDefaultCol == 1 || c.ColProperty == "3") && c.ColName != "Id" && c.ColName != "Fid").ToList(); string pCols = string.Join(",", baseCols.Select(c => c.ColName)); if (payrollInitData.ReservedItems.IsPresent()) { pCols += "," + payrollInitData.ReservedItems; } pCols = pCols.ReplaceIgnoreCase("PayYM", "'" + payrollInitData.InitYm + "' as PayYM"); //检查当月是否有发送记录 DynamicParameters param = new DynamicParameters(); param.Add("PayYM", payrollInitData.InitYm); param.Add("CaseUid", payrollInitData.CaseUid); var records = _dbContext.QueryWhere <PayRecord>(" PayYM=@PayYM and CaseUid=@CaseUid", param); int pcount = 1; if (records.Any()) { var existRecord = records.FirstOrDefault(r => r.PayFlag == 0); if (existRecord == null) { pcount = records.Max(r => r.PayCount) + 1; //添加发放记录 PayRecord newRecord = new PayRecord(); newRecord.CaseUid = payCase.Fid; newRecord.PayCount = pcount; newRecord.PayYM = payrollInitData.InitYm; newRecord.PayFlag = 0; _dbContext.Insert(newRecord); } else { pcount = existRecord.PayCount; } } else { //添加发放记录 PayRecord newRecord = new PayRecord(); newRecord.CaseUid = payCase.Fid; newRecord.PayCount = pcount; newRecord.PayYM = payrollInitData.InitYm; newRecord.PayFlag = 0; _dbContext.Insert(newRecord); } pCols = pCols.ReplaceIgnoreCase("PaymentTimes", pcount.ToString() + " as PaymentTimes"); pCols = pCols.ReplaceIgnoreCase("PayConfirm", "0 as PayConfirm"); string sql = $"select {pCols} from PayCenter where {where}"; var pastData = _dbContext.QueryOriSql(sql); IList <FapDynamicObject> listCase = new List <FapDynamicObject>(); foreach (var pd in pastData) { var dicPd = pd as IDictionary <string, object>; FapDynamicObject fdo = new FapDynamicObject(_dbContext.Columns(payCase.TableName)); foreach (var key in dicPd.Keys) { fdo.SetValue(key, dicPd[key]); } listCase.Add(fdo); } _dbContext.ExecuteOriginal($"truncate table {payCase.TableName}"); _dbContext.InsertDynamicDataBatchSql(listCase); //更新工资套 payCase.PayYM = payrollInitData.InitYm; payCase.PayCount = pcount; payCase.PayFlag = 0; _dbContext.Update(payCase); }