javascript - 使用 Javascript 将数据组织到表中

Question

我有大约 5k 的文档需要整理和组织，从 mongodb 中提取，通过 express，然后输出到 ejs 模板。我已经能够成功地将文档转换为 ejs 模板，但我在如何处理项目的第二部分 - 组织数据方面遇到了困难。

下面是我的数据外观的示例。我的目标是在最左边的列中列出所有的缺陷定位(总共大约30个)，并根据每年和每个月统计缺陷定位出现的次数。我不反对使用框架或 jquery。我唯一能想到的是为每个单元格分配一个函数，该函数遍历数组以查看它是否符合该单元格的要求。 (但这似乎违背了编程的本意)。最后，我想添加图表，但在这一点上这似乎真的很牵强。要补充的一件事 - 这不是我将使用的唯一日期范围，它们可以追溯到 2012 年到 2017 年。

  [{
    "_id": "59cee5ce8ffdc0134854f0c1",
    "repairorder": 7192822,
    "month": 2,
    "year": 2015,
    "defectlocation": "MB"
  }, {
    "_id": "59cee5ce8ffdc0134854f0c2",
    "repairorder": 7192822,
    "month": 5,
    "year": 2015,
    "defectlocation": "COVER/HOUSING"
  }, {
    "_id": "59cee5ce8ffdc0134854f0c3",
    "repairorder": 7192822,
    "month": 2,
    "year": 2015,
    "defectlocation": "MB"
  }, {
    "_id": "59cee5ce8ffdc0134854f0c5",
    "repairorder": 7192822,
    "month": 3,
    "year": 2015,
    "defectlocation": "TOUCH PAD"
  }, {
    "_id": "59cee5ce8ffdc0134854f0c6",
    "repairorder": 7192822,
    "month": 4,
    "year": 2015,
    "defectlocation": "MB"
  }]

下面是我需要它显示的方式:

  -----------------------------------------------------------------------
  Defect Location | 01-2015 |  02-2015 |  03-2015 |  04-2015 |  05-2015 |
  -----------------------------------------------------------------------
  MB              |         |    2     |          |    1     |          |
  -----------------------------------------------------------------------
  Touch Pad       |         |          |     1    |          |          |
  -----------------------------------------------------------------------
  Cover/ Housing  |         |          |          |          |     1    |
  -----------------------------------------------------------------------
  TOTAL           |         |     2    |     1    |     1    |     1    |

Answer 1

听起来您的问题是如何组织数据以计算每个时隙内每个缺陷位置的实例。这会起作用。您可以缩短它，但我尝试使用易于阅读的 js。从终点开始，您可以使用任何表格库，例如 datatables.net 或手动创建一个 html 表格，如 Mikey 的回答所示。希望这会有所帮助。

*** 更新:我最初忽略了 TOTAL 行。我已经更新了我的答案以包含它(并且作为数组中的最后一行)。我还添加了先按年然后按月对列进行排序(因为您的期望最终结果示例以这种方式显示)并且我将代码分成两个函数以希望使其更具可读性。

var data = [{
    "_id": "59cee5ce8ffdc0134854f0c1",
    "repairorder": 7192822,
    "month": 2,
    "year": 2015,
    "defectlocation": "MB"
  }, {
    "_id": "59cee5ce8ffdc0134854f0c2",
    "repairorder": 7192822,
    "month": 5,
    "year": 2015,
    "defectlocation": "COVER/HOUSING"
  }, {
    "_id": "59cee5ce8ffdc0134854f0c3",
    "repairorder": 7192822,
    "month": 2,
    "year": 2015,
    "defectlocation": "MB"
  }, {
    "_id": "59cee5ce8ffdc0134854f0c5",
    "repairorder": 7192822,
    "month": 3,
    "year": 2015,
    "defectlocation": "TOUCH PAD"
  }, {
    "_id": "59cee5ce8ffdc0134854f0c6",
    "repairorder": 7192822,
    "month": 4,
    "year": 2015,
    "defectlocation": "MB"
  }];

  var tableData = {};
  var totalRow = {};
  var allCols = [];
  var asArray = [];

  tallyInstances();
  prepForTable();

  function tallyInstances () {
    var i;
    var monthDate;
    for (i = 0; i < data.length; i++) {
      monthDate = data[i].month.toString() + '-' + data[i].year.toString();
      allCols.indexOf(monthDate) < 0 ? allCols.push(monthDate) : null;
      if (!tableData[data[i].defectlocation]) {
        tableData[data[i].defectlocation] = {}; // if our tableData object doesn't have a property for this defect location yet then add it and make it an object
      }
      if (tableData[data[i].defectlocation][monthDate]) {
        tableData[data[i].defectlocation][monthDate] ++; // if this defect location object has a property for this year/month combination already then increment it's value by one
      } else {
        tableData[data[i].defectlocation][monthDate] = 1; // if this defect location object does not have a property for this year/month combination yet then create the property and give it a value of 1
      }
      totalRow[monthDate] ? totalRow[monthDate] ++ : totalRow[monthDate] = 1; // ternary operator saying if the totalRow object already has a property for this year/month combination then increment it's value by one, otherwise create it and give it a value of 1
    }
  }

  function prepForTable () {
    allCols.sort(function(a, b) {
      var aParts = a.split("-");
      var bParts = b.split("-");
      var x = {
        month : aParts[0],
        year  : aParts[1]
      };
      var y = {
        month : bParts[0],
        year  : bParts[1]
      };
      var n = x.year - y.year;
      if (n !== 0) {
        return n;
      }
      return x.month - y.month;
    });
    var keys = Object.keys(tableData);
    var e;
    var rowObj;
    for (e = 0; e < keys.length; e++) {
      rowObj = {};
      rowObj["Defect Location"] = keys[e];
      var a;
      for (a = 0; a < allCols.length; a++) {
        rowObj[allCols[a]] = tableData[keys[e]][allCols[a]] ? tableData[keys[e]][allCols[a]] : '';
      }
      asArray.push(rowObj);
    }
    rowObj = {};
    rowObj["Defect Location"] = "TOTAL";
    var o;
    for (o = 0; o < allCols.length; o++) {
      rowObj[allCols[o]] = totalRow[allCols[o]] ? totalRow[allCols[o]] : '';
    }
    asArray.push(rowObj);
  }

  console.log("tableRows: ", JSON.stringify(asArray, null, 4));
  /*
 tableRows:  [
    {
        "Defect Location": "MB",
        "2-2015": 2,
        "3-2015": "",
        "4-2015": 1,
        "5-2015": ""
    },
    {
        "Defect Location": "COVER/HOUSING",
        "2-2015": "",
        "3-2015": "",
        "4-2015": "",
        "5-2015": 1
    },
    {
        "Defect Location": "TOUCH PAD",
        "2-2015": "",
        "3-2015": 1,
        "4-2015": "",
        "5-2015": ""
    },
    {
        "Defect Location": "TOTAL",
        "2-2015": 2,
        "3-2015": 1,
        "4-2015": 1,
        "5-2015": 1
    }
]

*/