C#_Dotnet core計算カラムデータの総数

10310 ワード

FastMemberでデータをDataTableにする
次のコードにはカラム名変換があります
許可をnullに設定
カスタム属性の追加などの方法
最終データフォーマットでは、DataTableは自動的にこのようなjson配列に変換されます.
[
      {
        "deviceModel": "QTZ63(5610),QTZ40",
        "deviceNumber": "1729-001,9999",
        "extraReward": 129,
        "id": 242,
        "projectName": "      ",
        "rewardAmount": 300,
        "settlementWorkerDetail": [
          {
            "reward": 229,
            "worker": {
              "userName": "",
              "name": "   ",
              "surname": "   ",
              "emailAddress": "   @dingding.com",
              "isActive": true,
              "fullName": "       ",
              "lastLoginTime": null,
              "creationTime": "2020-05-26T17:45:26.8492917",
              "dingTalkUserId": "",
              "roleNames": null,
              "organizationUnitIds": null,
              "organizationUnits": null,
              "phoneNumber": "",
              "id": 21
            },
            "workerId": 21
          }
        ],
        "subTotalRewardAmount": 429,
        "totalRewardAmount": 429,
        "workContent": "       ",
        "workOrderSettlementId": 0,
        "u_21": 229,
        "editable": false
      }]

        public WorkOrderStatement ConvertGenericListToDataTable(List inputList, DateTime startDate, DateTime endDate)
        {
            WorkOrderStatement workOrderStatement = new WorkOrderStatement();
            var userList = _workOrderWorkerManager.GetWorkerDetailList(startDate, endDate);

            var dt = new DataTable("DataTable");

            var newInputList = ObjectMapper.Map>(inputList);


            using (var reader = ObjectReader.Create(newInputList))
            {
                dt.Load(reader);
            }
            #region       

            //foreach (DataColumn dataColumn in dt.Columns)
            //{
            //    switch (dataColumn.ColumnName)
            //    {
            //        case "DeviceModel":
            //            dataColumn.ColumnName = "    ";
            //            break;
            //        case "ProjectName":
            //            dataColumn.ColumnName = "    ";
            //            break;
            //        case "WorkContent":
            //            dataColumn.ColumnName = "      ";
            //            break;
            //        case "RewardAmount":
            //            dataColumn.ColumnName = "      ";
            //            break;
            //        case "ExtraReward":
            //            dataColumn.ColumnName = "      ";
            //            break;
            //        case "DeviceNumber":
            //            dataColumn.ColumnName = "    ";
            //            break;
            //        case "TotalRewardAmount":
            //            dataColumn.ColumnName = "      ";
            //            break;
            //        case "SubTotalRewardAmount":
            //            dataColumn.ColumnName = "    ";
            //            break;
            //        default:
            //            break;
            //    }
            //}
            #endregion

            #region      null
            foreach (DataColumn col in dt.Columns) col.AllowDBNull = true;
            #endregion

            #region     
            List tableHeaders = new List();
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                var thead = new TableHeader
                {
                    Index = i,
                    ColumnName = dt.Columns[i].ColumnName
                };
                tableHeaders.Add(thead);
            }
            #endregion
            foreach (var user in userList)
            {
                dt.Columns.Add("u_" + user.Id, typeof(int));
                int currentIndex = tableHeaders.Count();
                var thead = new TableHeader
                {
                    Index = currentIndex++,
                    ColumnName = user.Name,
                    UserId = user.Id
                };
                tableHeaders.Add(thead);
            }
            dt.Columns.Add("Editable", typeof(bool));
            workOrderStatement.TableHeaders = tableHeaders;
            #region   
            foreach (DataRow rowItem in dt.Rows)
            {
                var newRows = ObjectMapper.Map>(rowItem.ItemArray[6]);
                if (newRows.Count > 0)
                {
                    foreach (var newRowItemVal in newRows)
                    {
                        rowItem["u_" + newRowItemVal.Worker.Id] = newRowItemVal.Reward;
                    }
                }
                rowItem["Editable"] = rowItem["WorkOrderSettlementId"] != null && Convert.ToInt32(rowItem["WorkOrderSettlementId"]) != 0;
            }
            #endregion
            #region   
            var totalRewardAmount = dt.Compute("sum(TotalRewardAmount)", "True");
            var subTotalRewardAmount = dt.Compute("sum(SubTotalRewardAmount)", "True");
            DataRow rewardRow = dt.NewRow();
            rewardRow["ProjectName"] = "  ";
            rewardRow["TotalRewardAmount"] = totalRewardAmount;
            rewardRow["SubTotalRewardAmount"] = subTotalRewardAmount;
            foreach (var user in userList)
            {
                rewardRow["u_" + user.Id] = dt.Compute($"sum({"u_" + user.Id})", "True");
            }
            dt.Rows.Add(rewardRow);
            #endregion
            workOrderStatement.UserDT = dt;

            return workOrderStatement;
        }
using Abp.Application.Services.Dto;
using Abp.AutoMapper;
using DRMS.Dictionaries.Dtos;
using DRMS.WorkOrders;
using DRMS.WorkOrders.Dtos;
using System;
using System.Collections.Generic;
using System.Data;

namespace DRMS.WorkOrder.Dtos
{
    [AutoMap(typeof(WorkOrderWorkerSettlement))]
    public class WorkOrderWorkerSettlementDto : EntityDto
    {
        /// 
        ///   
        /// 
        public WorkOrderDto WorkOrder { get; set; }

        /// 
        ///   ID
        /// 
        public Guid WorkOrderId { get; set; }

        /// 
        /// aa,bb,cc     
        /// 
        public string DeviceModel { get; set; }

        /// 
        /// aa,bb,cc     
        /// 
        public string DeviceNumber { get; set; }

        /// 
        ///     
        /// 
        public string ProjectName { get; set; }

        /// 
        ///       
        /// 
        public string WorkContent { get; set; }

        /// 
        ///       
        /// 
        public decimal RewardAmount { get; set; }

        /// 
        ///       
        /// 
        public decimal ExtraReward { get; set; }

        /// 
        ///       
        /// 
        public decimal TotalRewardAmount { get; set; }

        /// 
        ///     
        /// 
        public decimal SubTotalRewardAmount { get; set; }

        /// 
        ///        
        /// 
        public DateTime StartDate { get; set; }

        /// 
        ///        
        /// 
        public DateTime EndDate { get; set; }

        /// 
        ///        ID,     
        /// 
        public int WorkOrderSettlementId { get; set; }

        public DataDictionaryItemDto WorkOrderSettlement { get; set; }

        /// 
        ///       
        /// 
        // public ICollection WorkOrderWorkers { get; set; }
        public ICollection SettlementWorkerDetail { get; set; }
    }
    [AutoMap(typeof(WorkOrderWorkerSettlementDto))]
    public class ConvertGenericListToDataTable
    {
        public int Id { get; set; }

        /// 
        ///        ID,     
        /// 
        public int WorkOrderSettlementId { get; set; }

        /// 
        /// aa,bb,cc     
        /// 
        public string DeviceModel { get; set; }

        /// 
        ///     
        /// 
        public string ProjectName { get; set; }

        /// 
        ///       
        /// 
        public string WorkContent { get; set; }

        /// 
        ///       
        /// 
        public decimal RewardAmount { get; set; }

        /// 
        ///       
        /// 
        public decimal ExtraReward { get; set; }

        /// 
        /// aa,bb,cc     
        /// 
        public string DeviceNumber { get; set; }

        /// 
        ///       
        /// 
        public decimal TotalRewardAmount { get; set; }

        /// 
        ///     
        /// 
        public decimal SubTotalRewardAmount { get; set; }
        public ICollection SettlementWorkerDetail { get; set; }
    }
    public class TableHeader
    {
        public string ColumnName { get; set; }
        public int Index { get; set; }
        public long UserId { get; set; }
    }
    public class WorkOrderStatement
    {
        public List TableHeaders { get; set; }

        public DataTable UserDT { get; set; }
    }
}