/// <summary>
/// 获取薪酬项目对照表
/// </summary>
/// <param name="salaryItemIdList">列里包含的里包含的薪酬项目id集合</param>
/// <returns></returns>
private List <SalaryItemMapper> GetSalaryMapper(string mapperPath)
{
var mapper = new List <SalaryItemMapper>();
salaryItemIdList = new List <int>();
salaryColumnNameList = new List <string>();
string sourceType = cmbDataSource.SelectedItem.ToString();
string table = string.Empty;
if (sourceType.Trim() == "辅助核算发放表")
{
table = "top_PA_AuxSum";
}
if (sourceType.Trim() == "员工工资发放表")
{
table = "top_PA_AcctBill_Pay";
}
string colnumSQL = $" SELECT TOP 1 * FROM {table}Entry WHERE 1=@filter";
var colDataTable = SQLHepler.ExecuteDataTable(colnumSQL, new SqlParameter("@filter", 1));
foreach (DataColumn col in colDataTable.Columns)
{
if (col.ColumnName.Contains("FFIELD_") && int.TryParse(col.ColumnName.Substring(7, 6).ToString(), out int result))
{
salaryColumnNameList.Add(col.ColumnName);
salaryItemIdList.Add(result);
}
}
ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
using (ExcelPackage pck = new ExcelPackage(new FileStream(mapperPath, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite)))
{
var worksheet = pck.Workbook.Worksheets.First();
if (worksheet != null)
{
mapper = worksheet.ConvertSheetToObjects <SalaryItemMapper>().ToList();
}
}
string salaryItemsSQL = $@"SELECT FID,FNUMBER,FDATATYPE FROM top_pa_payitem WHERE FID IN ({string.Join(",", salaryItemIdList)})";
var salaryItems = SQLHepler.ExecuteDataTable(salaryItemsSQL, new SqlParameter("@ids", ""));
foreach (DataRow row in salaryItems.Rows)
{
var matchList = mapper.Where(c => c.TQSalaryNumber == row["FNUMBER"].ToString()).ToList();
foreach (var item in matchList)
{
item.TQSalaryId = Convert.ToInt32(row["FID"]);
item.DataType = (DataTypeEnum)Convert.ToInt32(row["FDATATYPE"]);
item.Plan = string.IsNullOrWhiteSpace(item.Plan) ? string.Join(",", planMapper.Keys.ToList()) : item.Plan;
}
}
return(mapper);
}
/// <summary>
/// 获取需要迁移的数据源
/// </summary>
/// <param name="year"></param>
/// <param name="month"></param>
/// <returns></returns>
private DataTable GetMigrationSource(string startYear, string startMonth, string endYear, string endMonth, string sourceType)
{
string table = string.Empty;
if (sourceType.Trim() == "辅助核算发放表")
{
table = "top_PA_AuxSum";
}
if (sourceType.Trim() == "员工工资发放表")
{
table = "top_PA_AcctBill_Pay";
}
string sourceSQL = $@"Select t3.FYEAR, t3.FPERIOD,t4.FNUMBER as StaffNum,t4_L.FNAME as StaffName,
t5.FNUMBER as DeptNumber, t5_L.FNAME as DeptName,
t6.FNUMBER as PostNum, t6_L.FNAME as PostName,
t1.FPAYPLANID, t7.FNUMBER as PlanNum, t7_L.FNAME as PlanName,
{string.Join(",", salaryColumnNameList)}
FROM {table} t1
INNER JOIN {table}Entry t2 ON t1.FID=t2.FID
INNER JOIN top_PA_Period t3 ON t1.FPERIODID=t3.FENTRYID
AND (t3.FYEAR BETWEEN @StartYear AND @EndYear)
AND (t3.FPERIOD BETWEEN @StartMonth AND @EndMonth)
INNER JOIN T_HR_EMPINFO t4 ON t2.FCLOUDEMPID=t4.FID
INNER JOIN T_HR_EMPINFO_L t4_L ON t2.FCLOUDEMPID=t4_L.FID
INNER JOIN T_BD_DEPARTMENT t5 ON t2.FPERSONDEPTID=t5.FDEPTID
INNER JOIN T_BD_DEPARTMENT_L t5_L ON t2.FPERSONDEPTID=t5_L.FDEPTID
INNER JOIN T_ORG_POST t6 ON t2.FPERSONPOSTID=t6.FPOSTID
INNER JOIN T_ORG_POST_L t6_L ON t2.FPERSONPOSTID=t6_L.FPOSTID
INNER JOIN Top_PA_PayPlan t7 ON t1.FPAYPLANID=t7.FID
INNER JOIN Top_PA_PayPlan_L t7_L ON t1.FPAYPLANID=t7_L.FID
ORDER BY t4.FNUMBER ASC";
var sqlParameters = new SqlParameter[] {
new SqlParameter("@StartYear", startYear),
new SqlParameter("@EndYear", endYear),
new SqlParameter("@StartMonth", startMonth),
new SqlParameter("@EndMonth", endMonth)
};
var sourcesData = SQLHepler.ExecuteDataTable(sourceSQL, sqlParameters);
return(sourcesData);
}
