add: import board/cards/lists using CSV/TSV

9 files changed, 413 insertions, 14 deletions

diff --git a/client/components/import/csvMembersMapper.js b/client/components/import/csvMembersMapper.js
new file mode 100644
index 00000000..cf8d5837
--- /dev/null
+++ b/client/components/import/csvMembersMapper.js
@@ -0,0 +1,37 @@
+export function getMembersToMap(data) {
+  // we will work on the list itself (an ordered array of objects) when a
+  // mapping is done, we add a 'wekan' field to the object representing the
+  // imported member
+
+  const membersToMap = [];
+  const importedMembers = [];
+  let membersIndex;
+
+  for (let i = 0; i < data[0].length; i++) {
+    if (data[0][i].toLowerCase() === 'members') {
+      membersIndex = i;
+    }
+  }
+
+  for (let i = 1; i < data.length; i++) {
+    if (data[i][membersIndex]) {
+      for (const importedMember of data[i][membersIndex].split(' ')) {
+        if (importedMember && importedMembers.indexOf(importedMember) === -1) {
+          importedMembers.push(importedMember);
+        }
+      }
+    }
+  }
+
+  for (let importedMember of importedMembers) {
+    importedMember = {
+      username: importedMember,
+      id: importedMember,
+    };
+    const wekanUser = Users.findOne({ username: importedMember.username });
+    if (wekanUser) importedMember.wekanId = wekanUser._id;
+    membersToMap.push(importedMember);
+  }
+
+  return membersToMap;
+}
diff --git a/client/components/import/import.jade b/client/components/import/import.jade
index 1551a7dd..2bea24ae 100644
--- a/client/components/import/import.jade
+++ b/client/components/import/import.jade
@@ -13,7 +13,7 @@ template(name="import")
 template(name="importTextarea")
   form
     p: label(for='import-textarea') {{_ instruction}} {{_ 'import-board-instruction-about-errors'}}
-    textarea.js-import-json(placeholder="{{_ 'import-json-placeholder'}}" autofocus)
+    textarea.js-import-json(placeholder="{{_ importPlaceHolder}}" autofocus)
       | {{jsonText}}
     input.primary.wide(type="submit" value="{{_ 'import'}}")
 
diff --git a/client/components/import/import.js b/client/components/import/import.js
index 6368885b..673900fd 100644
--- a/client/components/import/import.js
+++ b/client/components/import/import.js
@@ -1,5 +1,8 @@
 import trelloMembersMapper from './trelloMembersMapper';
 import wekanMembersMapper from './wekanMembersMapper';
+import csvMembersMapper from './csvMembersMapper';
+
+const Papa = require('papaparse');
 
 BlazeComponent.extendComponent({
   title() {
@@ -30,20 +33,30 @@ BlazeComponent.extendComponent({
     }
   },
 
-  importData(evt) {
+  importData(evt, dataSource) {
     evt.preventDefault();
-    const dataJson = this.find('.js-import-json').value;
-    try {
-      const dataObject = JSON.parse(dataJson);
-      this.setError('');
-      this.importedData.set(dataObject);
-      const membersToMap = this._prepareAdditionalData(dataObject);
-      // store members data and mapping in Session
-      // (we go deep and 2-way, so storing in data context is not a viable option)
+    const input = this.find('.js-import-json').value;
+    if (dataSource === 'csv') {
+      const csv = input.indexOf('\t') > 0 ? input.replace(/(\t)/g, ',') : input;
+      const ret = Papa.parse(csv);
+      if (ret && ret.data && ret.data.length) this.importedData.set(ret.data);
+      else throw new Meteor.Error('error-csv-schema');
+      const membersToMap = this._prepareAdditionalData(ret.data);
       this.membersToMap.set(membersToMap);
       this.nextStep();
-    } catch (e) {
-      this.setError('error-json-malformed');
+    } else {
+      try {
+        const dataObject = JSON.parse(input);
+        this.setError('');
+        this.importedData.set(dataObject);
+        const membersToMap = this._prepareAdditionalData(dataObject);
+        // store members data and mapping in Session
+        // (we go deep and 2-way, so storing in data context is not a viable option)
+        this.membersToMap.set(membersToMap);
+        this.nextStep();
+      } catch (e) {
+        this.setError('error-json-malformed');
+      }
     }
   },
 
@@ -91,6 +104,9 @@ BlazeComponent.extendComponent({
       case 'wekan':
         membersToMap = wekanMembersMapper.getMembersToMap(dataObject);
         break;
+      case 'csv':
+        membersToMap = csvMembersMapper.getMembersToMap(dataObject);
+        break;
     }
     return membersToMap;
   },
@@ -109,11 +125,23 @@ BlazeComponent.extendComponent({
     return `import-board-instruction-${Session.get('importSource')}`;
   },
 
+  importPlaceHolder() {
+    const importSource = Session.get('importSource');
+    if (importSource === 'csv') {
+      return 'import-csv-placeholder';
+    } else {
+      return 'import-json-placeholder';
+    }
+  },
+
   events() {
     return [
       {
         submit(evt) {
-          return this.parentComponent().importData(evt);
+          return this.parentComponent().importData(
+            evt,
+            Session.get('importSource'),
+          );
         },
       },
     ];
diff --git a/client/components/sidebar/sidebar.jade b/client/components/sidebar/sidebar.jade
index 6bfedc9c..89622ac1 100644
--- a/client/components/sidebar/sidebar.jade
+++ b/client/components/sidebar/sidebar.jade
@@ -230,6 +230,8 @@ template(name="chooseBoardSource")
       a(href="{{pathFor '/import/trello'}}") {{_ 'from-trello'}}
     li
       a(href="{{pathFor '/import/wekan'}}") {{_ 'from-wekan'}}
+    li
+      a(href="{{pathFor '/import/csv'}}") {{_ 'from-csv'}}
 
 template(name="archiveBoardPopup")
   p {{_ 'close-board-pop'}}
diff --git a/i18n/en.i18n.json b/i18n/en.i18n.json
index 11e7e2dd..a1bff774 100644
--- a/i18n/en.i18n.json
+++ b/i18n/en.i18n.json
@@ -307,6 +307,7 @@
   "error-board-notAMember": "You need to be a member of this board to do that",
   "error-json-malformed": "Your text is not valid JSON",
   "error-json-schema": "Your JSON data does not include the proper information in the correct format",
+  "error-csv-schema": "Your CSV(Comma Separated Values)/TSV (Tab Separated Values) does not include the proper information in the correct format ",
   "error-list-doesNotExist": "This list does not exist",
   "error-user-doesNotExist": "This user does not exist",
   "error-user-notAllowSelf": "You can not invite yourself",
@@ -349,12 +350,16 @@
   "import-board-c": "Import board",
   "import-board-title-trello": "Import board from Trello",
   "import-board-title-wekan": "Import board from previous export",
+  "import-board-title-csv": "Import board from CSV/TSV",
   "from-trello": "From Trello",
   "from-wekan": "From previous export",
+  "from-csv": "From CSV/TSV",
   "import-board-instruction-trello": "In your Trello board, go to 'Menu', then 'More', 'Print and Export', 'Export JSON', and copy the resulting text.",
+  "import-board-instruction-csv": "Paste in your Comma Separated Values(CSV)/ Tab Separated Values (TSV) .",
   "import-board-instruction-wekan": "In your board, go to 'Menu', then 'Export board', and copy the text in the downloaded file.",
   "import-board-instruction-about-errors": "If you get errors when importing board, sometimes importing still works, and board is at All Boards page.",
   "import-json-placeholder": "Paste your valid JSON data here",
+  "import-csv-placeholder": "Paste your valid CSV/TSV data here",
   "import-map-members": "Map members",
   "import-members-map": "Your imported board has some members. Please map the members you want to import to your users",
   "import-show-user-mapping": "Review members mapping",
@@ -387,6 +392,7 @@
   "swimlaneActionPopup-title": "Swimlane Actions",
   "swimlaneAddPopup-title": "Add a Swimlane below",
   "listImportCardPopup-title": "Import a Trello card",
+  "listImportCardsTsvPopup-title": "Import Excel CSV/TSV",
   "listMorePopup-title": "More",
   "link-list": "Link to this list",
   "list-delete-pop": "All actions will be removed from the activity feed and you won't be able to recover the list. There is no undo.",
diff --git a/models/csvCreator.js b/models/csvCreator.js
new file mode 100644
index 00000000..346d2201
--- /dev/null
+++ b/models/csvCreator.js
@@ -0,0 +1,314 @@
+import Boards from './boards';
+
+export class CsvCreator {
+  constructor(data) {
+    // date to be used for timestamps during import
+    this._nowDate = new Date();
+    // index to help keep track of what information a column stores
+    // each row represents a card
+    this.fieldIndex = {};
+    this.lists = {};
+    // Map of members using username => wekanid
+    this.members = data.membersMapping ? data.membersMapping : {};
+    this.swimlane = null;
+  }
+
+  /**
+   * If dateString is provided,
+   * return the Date it represents.
+   * If not, will return the date when it was first called.
+   * This is useful for us, as we want all import operations to
+   * have the exact same date for easier later retrieval.
+   *
+   * @param {String} dateString a properly formatted Date
+   */
+  _now(dateString) {
+    if (dateString) {
+      return new Date(dateString);
+    }
+    if (!this._nowDate) {
+      this._nowDate = new Date();
+    }
+    return this._nowDate;
+  }
+
+  _user(wekanUserId) {
+    if (wekanUserId && this.members[wekanUserId]) {
+      return this.members[wekanUserId];
+    }
+    return Meteor.userId();
+  }
+
+  /**
+   * Map the header row titles to an index to help assign proper values to the cards' fields
+   * Valid headers (name of card fields):
+   * title, description, status, owner, member, label, due date, start date, finish date, created at, updated at
+   * Some header aliases can also be accepted.
+   * Headers are NOT case-sensitive.
+   *
+   * @param {Array} headerRow array from row of headers of imported CSV/TSV for cards
+   */
+  mapHeadertoCardFieldIndex(headerRow) {
+    const index = {};
+    for (let i = 0; i < headerRow.length; i++) {
+      switch (headerRow[i].trim().toLowerCase()) {
+        case 'title':
+          index.title = i;
+          break;
+        case 'description':
+          index.description = i;
+          break;
+        case 'stage':
+        case 'status':
+        case 'state':
+          index.stage = i;
+          break;
+        case 'owner':
+          index.owner = i;
+          break;
+        case 'members':
+        case 'member':
+          index.members = i;
+          break;
+        case 'labels':
+        case 'label':
+          index.labels = i;
+          break;
+        case 'due date':
+        case 'deadline':
+        case 'due at':
+          index.dueAt = i;
+          break;
+        case 'start date':
+        case 'start at':
+          index.startAt = i;
+          break;
+        case 'finish date':
+        case 'end at':
+          index.endAt = i;
+          break;
+        case 'creation date':
+        case 'created at':
+          index.createdAt = i;
+          break;
+        case 'update date':
+        case 'updated at':
+        case 'modified at':
+        case 'modified on':
+          index.modifiedAt = i;
+          break;
+      }
+    }
+    this.fieldIndex = index;
+  }
+
+  createBoard(csvData) {
+    const boardToCreate = {
+      archived: false,
+      color: 'belize',
+      createdAt: this._now(),
+      labels: [],
+      members: [
+        {
+          userId: Meteor.userId(),
+          wekanId: Meteor.userId(),
+          isActive: true,
+          isAdmin: true,
+          isNoComments: false,
+          isCommentOnly: false,
+          swimlaneId: false,
+        },
+      ],
+      modifiedAt: this._now(),
+      //default is private, should inform user.
+      permission: 'private',
+      slug: 'board',
+      stars: 0,
+      title: `Imported Board ${this._now()}`,
+    };
+
+    // create labels
+    for (let i = 1; i < csvData.length; i++) {
+      //get the label column
+      if (csvData[i][this.fieldIndex.labels]) {
+        const labelsToCreate = new Set();
+        for (const importedLabel of csvData[i][this.fieldIndex.labels].split(
+          ' ',
+        )) {
+          if (importedLabel && importedLabel.length > 0) {
+            labelsToCreate.add(importedLabel);
+          }
+        }
+        for (const label of labelsToCreate) {
+          let labelName, labelColor;
+          if (label.indexOf('-') > -1) {
+            labelName = label.split('-')[0];
+            labelColor = label.split('-')[1];
+          } else {
+            labelName = label;
+          }
+          const labelToCreate = {
+            _id: Random.id(6),
+            color: labelColor ? labelColor : 'black',
+            name: labelName,
+          };
+          boardToCreate.labels.push(labelToCreate);
+        }
+      }
+    }
+
+    const boardId = Boards.direct.insert(boardToCreate);
+    Boards.direct.update(boardId, {
+      $set: {
+        modifiedAt: this._now(),
+      },
+    });
+    // log activity
+    Activities.direct.insert({
+      activityType: 'importBoard',
+      boardId,
+      createdAt: this._now(),
+      source: {
+        id: boardId,
+        system: 'CSV/TSV',
+      },
+      // We attribute the import to current user,
+      // not the author from the original object.
+      userId: this._user(),
+    });
+    return boardId;
+  }
+
+  createSwimlanes(boardId) {
+    const swimlaneToCreate = {
+      archived: false,
+      boardId,
+      createdAt: this._now(),
+      title: 'Default',
+      sort: 1,
+    };
+    const swimlaneId = Swimlanes.direct.insert(swimlaneToCreate);
+    Swimlanes.direct.update(swimlaneId, { $set: { updatedAt: this._now() } });
+    this.swimlane = swimlaneId;
+  }
+
+  createLists(csvData, boardId) {
+    let numOfCreatedLists = 0;
+    for (let i = 1; i < csvData.length; i++) {
+      const listToCreate = {
+        archived: false,
+        boardId,
+        createdAt: this._now(),
+      };
+      if (csvData[i][this.fieldIndex.stage]) {
+        const existingList = Lists.find({
+          title: csvData[i][this.fieldIndex.stage],
+          boardId,
+        }).fetch();
+        if (existingList.length > 0) {
+          continue;
+        } else {
+          listToCreate.title = csvData[i][this.fieldIndex.stage];
+        }
+      } else listToCreate.title = `Imported List ${this._now()}`;
+
+      const listId = Lists.direct.insert(listToCreate);
+      this.lists[csvData[i][this.fieldIndex.stage]] = listId;
+      numOfCreatedLists++;
+      Lists.direct.update(listId, {
+        $set: {
+          updatedAt: this._now(),
+          sort: numOfCreatedLists,
+        },
+      });
+    }
+  }
+
+  createCards(csvData, boardId) {
+    for (let i = 1; i < csvData.length; i++) {
+      const cardToCreate = {
+        archived: false,
+        boardId,
+        createdAt: csvData[i][this.fieldIndex.createdAt]
+          ? this._now(new Date(csvData[i][this.fieldIndex.createdAt]))
+          : null,
+        dateLastActivity: this._now(),
+        description: csvData[i][this.fieldIndex.description],
+        listId: this.lists[csvData[i][this.fieldIndex.stage]],
+        swimlaneId: this.swimlane,
+        sort: -1,
+        title: csvData[i][this.fieldIndex.title],
+        userId: this._user(),
+        startAt: csvData[i][this.fieldIndex.startAt]
+          ? this._now(new Date(csvData[i][this.fieldIndex.startAt]))
+          : null,
+        dueAt: csvData[i][this.fieldIndex.dueAt]
+          ? this._now(new Date(csvData[i][this.fieldIndex.dueAt]))
+          : null,
+        endAt: csvData[i][this.fieldIndex.endAt]
+          ? this._now(new Date(csvData[i][this.fieldIndex.endAt]))
+          : null,
+        spentTime: null,
+        labelIds: [],
+        modifiedAt: csvData[i][this.fieldIndex.modifiedAt]
+          ? this._now(new Date(csvData[i][this.fieldIndex.modifiedAt]))
+          : null,
+      };
+      // add the labels
+      if (csvData[i][this.fieldIndex.labels]) {
+        const board = Boards.findOne(boardId);
+        for (const importedLabel of csvData[i][this.fieldIndex.labels].split(
+          ' ',
+        )) {
+          if (importedLabel && importedLabel.length > 0) {
+            let labelToApply;
+            if (importedLabel.indexOf('-') === -1) {
+              labelToApply = board.getLabel(importedLabel, 'black');
+            } else {
+              labelToApply = board.getLabel(
+                importedLabel.split('-')[0],
+                importedLabel.split('-')[1],
+              );
+            }
+            cardToCreate.labelIds.push(labelToApply._id);
+          }
+        }
+      }
+      // add the members
+      if (csvData[i][this.fieldIndex.members]) {
+        const wekanMembers = [];
+        for (const importedMember of csvData[i][this.fieldIndex.members].split(
+          ' ',
+        )) {
+          if (this.members[importedMember]) {
+            const wekanId = this.members[importedMember];
+            if (!wekanMembers.find(wId => wId === wekanId)) {
+              wekanMembers.push(wekanId);
+            }
+          }
+        }
+        if (wekanMembers.length > 0) {
+          cardToCreate.members = wekanMembers;
+        }
+      }
+      Cards.direct.insert(cardToCreate);
+    }
+  }
+
+  create(board, currentBoardId) {
+    const isSandstorm =
+      Meteor.settings &&
+      Meteor.settings.public &&
+      Meteor.settings.public.sandstorm;
+    if (isSandstorm && currentBoardId) {
+      const currentBoard = Boards.findOne(currentBoardId);
+      currentBoard.archive();
+    }
+    this.mapHeadertoCardFieldIndex(board[0]);
+    const boardId = this.createBoard(board);
+    this.createLists(board, boardId);
+    this.createSwimlanes(boardId);
+    this.createCards(board, boardId);
+    return boardId;
+  }
+}
diff --git a/models/import.js b/models/import.js
index fbfb1483..ea18c14f 100644
--- a/models/import.js
+++ b/models/import.js
@@ -2,21 +2,27 @@ import { TrelloCreator } from './trelloCreator';
 import { WekanCreator } from './wekanCreator';
 import { Exporter } from './export';
 import wekanMembersMapper from './wekanmapper';
+import { CsvCreator } from './csvCreator';
 
 Meteor.methods({
   importBoard(board, data, importSource, currentBoard) {
-    check(board, Object);
     check(data, Object);
     check(importSource, String);
     check(currentBoard, Match.Maybe(String));
     let creator;
     switch (importSource) {
       case 'trello':
+        check(board, Object);
         creator = new TrelloCreator(data);
         break;
       case 'wekan':
+        check(board, Object);
         creator = new WekanCreator(data);
         break;
+      case 'csv':
+        check(board, Array);
+        creator = new CsvCreator(data);
+        break;
     }
 
     // 1. check all parameters are ok from a syntax point of view
diff --git a/package-lock.json b/package-lock.json
index 72e781a7..ced4a945 100644
--- a/package-lock.json
+++ b/package-lock.json
@@ -3725,6 +3725,11 @@
         "path-to-regexp": "~1.2.1"
       }
     },
+    "papaparse": {
+      "version": "5.2.0",
+      "resolved": "https://registry.npmjs.org/papaparse/-/papaparse-5.2.0.tgz",
+      "integrity": "sha512-ylq1wgUSnagU+MKQtNeVqrPhZuMYBvOSL00DHycFTCxownF95gpLAk1HiHdUW77N8yxRq1qHXLdlIPyBSG9NSA=="
+    },
     "parent-module": {
       "version": "1.0.1",
       "resolved": "https://registry.npmjs.org/parent-module/-/parent-module-1.0.1.tgz",
diff --git a/package.json b/package.json
index 85dc1f9b..871f1171 100644
--- a/package.json
+++ b/package.json
@@ -69,6 +69,7 @@
     "mongodb": "^3.5.0",
     "os": "^0.1.1",
     "page": "^1.11.5",
+    "papaparse": "^5.2.0",
     "qs": "^6.9.1",
     "source-map-support": "^0.5.16",
     "xss": "^1.0.6"