/// <summary>
///
/// </summary>
/// <param name="keyword"></param>
/// <param name="payrollId"></param>
/// <param name="order"></param>
/// <param name="limit"></param>
/// <returns></returns>
public static DataTable GetPayrollDetail(string keyword, int payrollId, string order, int?limit)
{
// get payroll
var payroll = GetById(payrollId);
if (payroll == null)
{
return(null);
}
// get salary board info
var salaryBoardInfos = SalaryBoardInfoController.GetAll(keyword, payrollId.ToString(), order, limit);
if (payroll.Status == PayrollStatus.Locked)
{
return(JsonConvert.DeserializeObject <DataTable>(payroll.Data));
}
// get salary board config
var salaryBoardConfigs = SalaryBoardConfigController.GetAll(payroll.ConfigId, true, null, null, null, null, null);
// get payroll dynamic data
var salaryBoardDynamicColumns = SalaryBoardDynamicColumnController.GetAll(null, null, payroll.Id, null, true, null, null);
var workbook = new WorkBook {
AutoCalc = false
};
// generate default column
workbook.setText("A1", "Id");
workbook.setText("B1", "RecordId");
workbook.setText("C1", "FullName");
workbook.setText("D1", "EmployeeCode");
workbook.setText("E1", "PositionName");
// generate other column
foreach (var config in salaryBoardConfigs)
{
// set column name as Property name
workbook.setText(config.ColumnExcel + 1, config.ColumnCode);
}
// fill data into table
for (var i = 0; i < salaryBoardInfos.Count; i++)
{
workbook.setNumber("A{0}".FormatWith(i + 2), salaryBoardInfos[i].Id);
workbook.setNumber("B{0}".FormatWith(i + 2), salaryBoardInfos[i].RecordId);
workbook.setText("C{0}".FormatWith(i + 2), salaryBoardInfos[i].FullName);
workbook.setText("D{0}".FormatWith(i + 2), salaryBoardInfos[i].EmployeeCode);
workbook.setText("E{0}".FormatWith(i + 2), salaryBoardInfos[i].PositionName);
// add value for dynamic column
foreach (var config in salaryBoardConfigs)
{
var columnExcel = config.ColumnExcel + (i + 2);
switch (config.DataType)
{
case SalaryConfigDataType.FieldValue:
var type = salaryBoardInfos[i].GetType();
var propInfo = type.GetProperty(config.ColumnCode);
if (propInfo == null)
{
break;
}
var value = Convert.ToString(propInfo.GetValue(salaryBoardInfos[i], BindingFlags.GetProperty, null, null, CultureInfo.InvariantCulture), CultureInfo.InvariantCulture);
// convert to double
if (double.TryParse(value, NumberStyles.Float, CultureInfo.InvariantCulture, out var result))
{
workbook.setNumber(columnExcel, result);
}
else
{
workbook.setText(columnExcel, value);
}
break;
case SalaryConfigDataType.FixedValue:
case SalaryConfigDataType.FieldFormula:
case SalaryConfigDataType.Formula:
workbook.setFormula(columnExcel, config.Formula.FormatWith(i + 2).TrimStart('='));
break;
case SalaryConfigDataType.DynamicValue:
var findObj = salaryBoardDynamicColumns.Find(o =>
o.RecordId == salaryBoardInfos[i].RecordId && o.ColumnCode == config.ColumnCode);
if (findObj != null)
{
// convert to double
if (double.TryParse(findObj.Value, NumberStyles.Float, CultureInfo.InvariantCulture, out var result2))
{
workbook.setNumber(columnExcel, result2);
}
else
{
workbook.setText(columnExcel, findObj.Value);
}
}
else
{
workbook.setText(columnExcel, string.Empty);
}
break;
default:
workbook.setText(columnExcel, string.Empty);
break;
}
}
}
workbook.recalc();
var table = workbook.ExportDataTable(0, 0, workbook.LastRow + 1, workbook.LastCol + 1);
return(workbook.ExportDataTable(0, 0, workbook.LastRow + 1, workbook.LastCol + 1));
}
public static DataTable GetTable(string keyword, int payrollId, string order, int start, int limit)
{
var dataTable = new DataTable();
// get payroll
var payroll = PayrollController.GetById(payrollId);
if (payroll == null)
{
return(null);
}
// get payroll config
var salaryBoardConfigs = SalaryBoardConfigController.GetAll(payroll.ConfigId, true, null, null,
SalaryConfigDataType.DynamicValue, null, null);
// get dynamic column value
var salaryBoardDynamicColumns = GetAll(null, null, payrollId, null, true, null, null);
// get salary board info
var salaryInfos = SalaryBoardInfoController.GetPaging(keyword, payrollId.ToString(), order, start, limit);
// generate fixed column
var fixedColumn = new[] {
new DataColumn("Id"),
new DataColumn("RecordId"),
new DataColumn("FullName"),
new DataColumn("EmployeeCode"),
new DataColumn("DepartmentName")
};
dataTable.Columns.AddRange(fixedColumn);
// generate dynamic column
if (salaryBoardConfigs != null && salaryBoardConfigs.Count > 0)
{
foreach (var salaryBoardConfig in salaryBoardConfigs)
{
dataTable.Columns.Add(new DataColumn(salaryBoardConfig.ColumnCode));
}
}
// fill data to table
foreach (var info in salaryInfos.Data)
{
// init row
var row = dataTable.NewRow();
// fill data to row
row["Id"] = info.Id;
row["RecordId"] = info.RecordId;
row["FullName"] = info.FullName;
row["EmployeeCode"] = info.EmployeeCode;
row["DepartmentName"] = info.DepartmentName;
// fill dynamic value
foreach (var salaryBoardDynamicColumn in salaryBoardDynamicColumns.Where(s => s.RecordId == info.RecordId))
{
if (row.Table.Columns.Contains(salaryBoardDynamicColumn.ColumnCode))
{
row[salaryBoardDynamicColumn.ColumnCode] = salaryBoardDynamicColumn.Value;
}
}
// add row to data table
dataTable.Rows.Add(row);
}
return(dataTable);
}
