Пример #1
0
        /// <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);
        }