如何统计AJAX获取的JSON时序数据trolleys总和并填充表格Total行

这是我此前提出的「通过jQuery向动态新增td绑定JSON对象数据」问题的延续。

我正使用jQuery、HTML/CSS和AJAX开发时间表/资源时间线，数据从REST API获取。我已完成所有部门行的展示，但卡在了Total部分的开发：在最后一行Total中，我需要向该行追加td，展示基于AJAX返回的JSON数据、按时段统计的所有部门的trolleys总和。

![TimeTable]

HTML代码 #

<div style="overflow-x:auto;">
  <table id="time_table" class="table-bordered table-condensed">
    <tr>
      <th class="first-column">Department</th>
      <th>7am</th>
      <th>730am</th>
      <th>8am</th>
      <th>830am</th>
      <th>9am</th>
      <th>930am</th>
      <th>10am</th>
      <th>1030am</th>
      <th>11am</th>
      <th>1130am</th>
      <th>12pm</th>
      <th>1230pm</th>
      <th>1pm</th>
      <th>130pm</th>
      <th>2pm</th>
      <th>230pm</th>
      <th>3pm</th>
      <th>330pm</th>
      <th>4pm</th>
      <th>430pm</th>
      <th>5pm</th>
      <th>530pm</th>
      <th>6pm</th>
      <th>630pm</th>
      <th>7pm</th>
      <th>730pm</th>
      <th>8pm</th>
      <th>830pm</th>
      <th>9pm</th>
      <th>930pm</th>
      <th>10pm</th>
      <th>1030pm</th>
      <th>11pm</th>
    </tr>
    <tr id="department01_row">
      <td class="first-column">Department01</td>
      <td>Department01</td>
    </tr>
    <tr id="depatment02_row">
      <td class="first-column">Depatment02</td>
    </tr>
    <tr id="department03_row">
      <td class="first-column">Department03</td>
    </tr>
    <tr id="department04_row">
      <td class="first-column">Department04</td>
    </tr>
    <tr id="department05_row">
      <td class="first-column">Department05</td>
    </tr>
    <tr><td class="first-column"></td></tr>
    <tr id="total_row">
      <td class="first-column">Total</td>
    </tr>
  </table>
</div>

通过AJAX获取的JSON数据结构如下：

{
  "is_active": true,
  "timeslots": [
    {
      "department": "department05",
      "end_time": "13:00:00",
      "id": 3,
      "start_time": "09:00:00",
      "trolleys": 10
    },
    {
      "department": "department03",
      "end_time": "13:00:00",
      "id": 7,
      "start_time": "12:30:00",
      "trolleys": 1
    },
    {
      "department": "department01",
      "end_time": "10:30:00",
      "id": 14,
      "start_time": "09:00:00",
      "trolleys": 4
    },
    {
      "department": "department01",
      "end_time": "13:00:00",
      "id": 20,
      "start_time": "12:00:00",
      "trolleys": 1
    },
    {
      "department": "department04",
      "end_time": "10:30:00",
      "id": 22,
      "start_time": "10:00:00",
      "trolleys": 3
    },
    {
      "department": "department03",
      "end_time": "12:30:00",
      "id": 26,
      "start_time": "12:00:00",
      "trolleys": 7
    }
  ]
}

原有jQuery/JavaScript代码 #

//The code below is extracted from a function
//timeSlotData is data['timeslots'] which I retrieved via AJAX.

let sortedTimeSlotData = timeSlotData.sort(function(a, b) {
  return parseFloat(a.start_time) - parseFloat(b.start_time)
})

//The times are fixed, i.e. 7am up to 1130pm which is 33 tds 
let timeArray = [7.00, 7.30, 8.00, 8.30,
    9.00, 9.30, 10.00, 10.30,
    11.00, 11.30, 12.00, 12.30,
    13.00, 13.30, 14.00, 14.30,
    15.00, 15.30, 16.00, 16.30,
    17.00, 17.30, 18.00, 18.30,
    19.00, 19.30, 20.00, 20.30,
    21.00, 21.30, 22.00, 22.30,
    23.00]
let $totalRowHTML = Array(33).fill('<td></td>')

//TODO: 现有逻辑循环处理数据时，无法汇总相同时段的trolleys数值
for (let singleData of sortedTimeSlotData) {
  let startTime = parseFloat(singleData.start_time.substr(0, 5))
  let endTime = parseFloat(singleData.end_time.substr(0, 5))

  let startTimeIndex = timeArray.indexOf(startTime)
  let endTimeIndex = timeArray.indexOf(endTime)
  let timeLength = endTimeIndex - startTimeIndex

  const $tdSlot = $('<td>').attr('colspan', timeLength)
  
  $tdSlot.text(singleData.trolleys);
  $tdSlot.addClass('total-booked')

  $totalRowHTML[startTimeIndex] = $tdSlot

  if (timeLength > 1) {
    for (let i = 1; i < timeLength; ++i) {
      $totalRowHTML[startTimeIndex+i] = ''
    }
  }
}

$('#total_row').append($totalRowHTML)

待解决问题 #

该如何调整现有代码，实现按时段汇总所有部门的trolleys数值，正确填充到Total行的对应单元格中？

解决方案 #

现有逻辑的核心问题是直接把单个部门的trolleys值覆盖到对应时段，没有做累计统计，同时直接拼接td元素的方式会导致同一时段的多个部门数据无法叠加。调整逻辑分为三步：

• 初始化一个和timeArray长度一致的空数组，按索引累计每个30分钟时段的trolleys总和
• 遍历所有timeslot数据，将每个条目覆盖范围内的每个30分钟索引对应的累计值，加上当前条目的trolleys数
• 根据累计的数值数组生成Total行的td元素，连续数值相同的相邻时段合并为一个带colspan的单元格，减少冗余DOM

调整后的完整代码：

// 固定时段数组保持原有定义
let timeArray = [7.00, 7.30, 8.00, 8.30,
    9.00, 9.30, 10.00, 10.30,
    11.00, 11.30, 12.00, 12.30,
    13.00, 13.30, 14.00, 14.30,
    15.00, 15.30, 16.00, 16.30,
    17.00, 17.30, 18.00, 18.30,
    19.00, 19.30, 20.00, 20.30,
    21.00, 21.30, 22.00, 22.30,
    23.00]

// 1. 初始化全时段累计数组，每个时段初始值为0
let slotTotals = Array(timeArray.length).fill(0)

// 2. 遍历所有时段数据，累计每个30分钟块的总和
timeSlotData.forEach(singleData => {
  let startTime = parseFloat(singleData.start_time.substr(0,5))
  let endTime = parseFloat(singleData.end_time.substr(0,5))
  let startIdx = timeArray.indexOf(startTime)
  let endIdx = timeArray.indexOf(endTime)
  // 容错处理，跳过无效的时间配置
  if (startIdx === -1 || endIdx === -1 || endIdx <= startIdx) return
  // 把当前条目的trolleys数，累加到覆盖的所有30分钟块中
  for (let i = startIdx; i < endIdx; i++) {
    slotTotals[i] += singleData.trolleys
  }
})

// 3. 生成Total行的td元素，合并连续相同值的相邻单元格
let totalCells = []
let i = 0
while (i < slotTotals.length) {
  let currentVal = slotTotals[i]
  let spanLen = 1
  // 向后查找连续相同值的块，计算colspan长度
  while (i + spanLen < slotTotals.length && slotTotals[i + spanLen] === currentVal) {
    spanLen++
  }
  // 生成对应td，值为0的块留空
  let $td = $('<td>').attr('colspan', spanLen)
  if (currentVal > 0) {
    $td.text(currentVal).addClass('total-booked')
  }
  totalCells.push($td)
  i += spanLen
}

// 清空total_row原有动态内容，保留第一列的"Total"文本，再追加生成的单元格
$('#total_row').find('td:not(.first-column)').remove()
$('#total_row').append(totalCells)

补充说明：

• 移除了原有按start_time排序的逻辑，因为累计统计不依赖数据的先后顺序，遍历效率不受影响
• 增加了无效时间索引的容错处理，避免接口返回异常时间值时导致数组索引越界
• 合并连续相同数值的单元格，和部门行的渲染逻辑保持一致，不会出现同一数值被拆成多个窄单元格的视觉问题
• 数值为0的时段不显示文本，保持表格视觉简洁

内容的提问来源于stack exchange，提问作者AlbertLeng