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