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