update-excel-file.js

const moment = require('moment');
const _ = require('lodash');
const Excel = require('exceljs');
const striptags = require('striptags');

const backup = require('./backup_prod_2019-12-26.json');
const source = 'analytic_source.xlsm';

const fn = {
  cells: 0,
  updatedCells: 0,
  workbook: null,
  FARMER_ACCOUNT: '1',
  EXPERT_ACCOUNT: '2',
  TEMP_POST_FLAG: '0',
  PUBLIC_POST: '1',
  PRIVATE_POST: '2',
  IGNORE_ROWS_EXCEL: [1, 2],
  IGNORE_COLUMNS_EXCEL: [2, 3],
  COLUMNS_EXCEL_MAPPING_SHEET1: {
    deviceId: 1,
    displayName: 4,
    streamName: 5,
    nameSpaceId: 6,
    eventCount: 7,
    sessions: 8,
    revenue: 9,
    transactions: 10,
    conversions: 11,
    averageSessionDuration: 12,
    numberOfUniqeVisit: 13,
    numberOfLikePost: 14, //done
    numberOfLikeReply: 15, //done
    returningVisitFrequencyWeekly: 16,
    numberOfExpertConsultations: 17, //done
    numberOfAgronomistPost: 18, //done
    numberOfReadShareAgronomistPostComment: 19,
    numberOfLikeAgronomistPost: 20, //done
    numberOfLikeAgronomistComment: 21, //done
    bounceRate: 22,
    numberOfQuestionsPostedCommunityAndExperts: 23, //done
    numberOfReachedPosts: 24
  },
  COLUMNS_EXCELMAPPING_SHEET2: {
    postTopic: 1,
    userName: 2,
    postDate: 3,
    postTime: 4,
    tag: 5,
    detailOfPost: 6,
    farmersneedToKnowOtherCropsOrNot: 7,
    whatFarmerAskTopic: 8,
    numberOfRepeatedPostForEachUser: 9,
    leadTimeOfExpertToAnswerQuestion: 10
  },
  resetNumberCell: () => {
    fn.cells = 0, fn.updatedCells = 0;
  },
  increaseUpdatedCell(val) {
    if(val > 0) {
      fn.updatedCells++;
    }
  },
  updateCell: (row, columnIndex, val) => {
    row.getCell(columnIndex).value = val;
    fn.increaseUpdatedCell(val);
    fn.cells++;
  },
  parseBodyPreview: (body) => {
    let bodyPreview = '';
    let plainBody = striptags(body, [], ' ');
    plainBody = plainBody.trim().replace(/\s\s+/g, ' ');
    if (plainBody.length <= 100) {

      bodyPreview = plainBody
    } else {
      const words = plainBody.split(/\s+/) || [];
      let charCount = 0;
      const includedWords = [];
      for (let i = 0; i < words.length; i++) {
          if ((charCount + words[i].length) <= 100) {
              includedWords.push(words[i]);
              charCount += words[i].length + 1;
          } else {
              break;
          }
      }
      bodyPreview = includedWords.join(' ');
    }
    return bodyPreview;
  },
  findNumberOfLike: (row, data, userId, fromDate, toDate) => {
    const columns = fn.COLUMNS_EXCEL_MAPPING_SHEET1;
    let numberOfLikePost = 0;
    let numberOfLikeReply = 0;
    let numberOfLikeAgronomistPost = 0;
    let numberOfLikeAgronomistComment = 0;

    for (let likeId of Object.keys(data.like)) {
      const inRangeDate = data.like[likeId].updatedDatetime._seconds >= fromDate &&
        data.like[likeId].updatedDatetime._seconds < toDate;

      /* Number of “Like” – posts */
      let isOwner = data.like[likeId].actor.userId === userId;

      if (isOwner && inRangeDate) {
        numberOfLikePost++;
      }

      /* Like – Agronomist’s Post */
      isOwner = data.like[likeId].actor.userId === userId &&
        data.like[likeId].actor.accountType === fn.EXPERT_ACCOUNT;

      if (isOwner && inRangeDate) {
        numberOfLikeAgronomistPost++;
      }
    }

    for (let likeId of Object.keys(data.replyLike)) {
      const inRangeDate = data.replyLike[likeId].updatedDatetime._seconds >= fromDate &&
        data.replyLike[likeId].updatedDatetime._seconds <= toDate;
      /* Number of “Like” – comments */
      let isOwner = data.replyLike[likeId].actor.userId === userId;

      if (isOwner && inRangeDate) {
        numberOfLikeReply++;
      }

      /* Like – Agronomist’s Comment */
      isOwner = data.replyLike[likeId].actor.userId === userId &&
        data.replyLike[likeId].actor.accountType === fn.EXPERT_ACCOUNT;

      if (isOwner && inRangeDate) {
        numberOfLikeAgronomistComment++;
      }
    }
    fn.updateCell(row, columns.numberOfLikePost, numberOfLikePost);
    fn.updateCell(row, columns.numberOfLikeReply, numberOfLikeReply);
    fn.updateCell(row, columns.numberOfLikeAgronomistPost, numberOfLikeAgronomistPost);
    fn.updateCell(row, columns.numberOfLikeAgronomistComment, numberOfLikeAgronomistComment);
  },
  findNumberOfPost: (row, posts, userId, fromDate, toDate) => {
    const columns = fn.COLUMNS_EXCEL_MAPPING_SHEET1;
    let numOfQuestionPost = 0;
    let numberOfExpertConsultations = 0;
    let numberOfAgronomistPost = 0;

    for (let postId of Object.keys(posts)) {
      const inRangeDate = posts[postId].createdDatetime._seconds >= fromDate &&
        posts[postId].createdDatetime._seconds < toDate;

      /* Number of Questions Posted – Community & Experts */
      let isOwner = posts[postId].owner.userId === userId &&
        posts[postId].postType === fn.PUBLIC_POST &&
        posts[postId].tempFlag === fn.TEMP_POST_FLAG;
      
      if (isOwner && inRangeDate) {
        numOfQuestionPost++;
      }

      /* Number of 1-1 Expert Consultations */
      isOwner = posts[postId].owner.userId === userId &&
        posts[postId].postType === fn.PRIVATE_POST &&
        posts[postId].tempFlag === fn.TEMP_POST_FLAG;
      
      if (isOwner && inRangeDate) {
        numberOfExpertConsultations++;
      }

      /* Number of Agronomist’s Post */
      isOwner = posts[postId].owner.userId === userId &&
        posts[postId].postType === fn.PUBLIC_POST &&
        posts[postId].tempFlag === fn.TEMP_POST_FLAG &&
        posts[postId].owner.accountType === fn.EXPERT_ACCOUNT;

      if (isOwner && inRangeDate) {
        numberOfAgronomistPost++;
      }
    }
    fn.updateCell(row, columns.numberOfQuestionsPostedCommunityAndExperts, numOfQuestionPost);
    fn.updateCell(row, columns.numberOfExpertConsultations, numberOfExpertConsultations);
    fn.updateCell(row, columns.numberOfAgronomistPost, numberOfAgronomistPost);
  },
  addPostToSheetNumber: (workbook, posts, fromDate, toDate, sheetNumber = 1) => {
    const post = [];
    const startTime = new Date();
    const sheet = workbook.getWorksheet(sheetNumber);
    
    console.log(`\r\nActual Post Count(Sheet ${sheetNumber}): ${Object.keys(posts).length}`);
    console.log('Add post between dates (YYYY-MM-DD): ' + moment(fromDate * 1000).format('YYYY-MM-DD') + ' and ' +
      moment(toDate * 1000).subtract('1', 'days').format('YYYY-MM-DD'));

    for (let postId of Object.keys(posts)) {
      const inRangeDate = posts[postId].createdDatetime._seconds >= fromDate &&
        posts[postId].createdDatetime._seconds < toDate;

      if (inRangeDate) {
        const postDate = moment(posts[postId].createdDatetime._seconds * 1000).format('YYYY-MM-DD');
        const postTime = moment(posts[postId].createdDatetime._seconds * 1000).format('HH:MM:SS');
        const tag = Object.keys(posts[postId].tags || []).join(",");
        post.push([
          posts[postId].subject,
          posts[postId].owner.displayName,
          postDate,
          postTime,
          tag,
          fn.parseBodyPreview(posts[postId].body)
        ]);
      }
    }
    sheet.addRows(post);
    sheet.eachRow((row, rowNumber) => {
      row.eachCell({ includeEmpty: true }, function (cell, colNumber) {
        sheet.getColumn(colNumber).hidden = false;
        cell.border = {
          top: { style:'thin' },
          left: { style:'thin' },
          bottom: { style:'thin' },
          right: { style:'thin' }
        };
      });
      row.hidden = false;
      row.commit();
    });
    console.log(`Estimated Time(Add post in Sheet ${sheetNumber}): ${new Date().getTime() - startTime.getTime()}ms`);
  },
  updateAnalyticExcel: async (data, filename, fromDate, toDate) => {
    const startTime = new Date();
    fn.resetNumberCell();
    const workbook = new Excel.Workbook();
    const columns = fn.COLUMNS_EXCEL_MAPPING_SHEET1;

    await workbook.xlsx.readFile(filename);

    const sheet = workbook.getWorksheet(1);
    console.log(`Actual User Count(Sheet 1): ${sheet.actualRowCount - (_.max(fn.IGNORE_ROWS_EXCEL) || 0)}`);

    sheet.eachRow((row, rowNumber) => {
      if (!fn.IGNORE_ROWS_EXCEL.includes(rowNumber)) {
        let userId = row.getCell(columns.deviceId).value;

        fn.findNumberOfLike(row, data, userId, fromDate, toDate);
        fn.findNumberOfPost(row, data.post, userId, fromDate, toDate);
        
        row.eachCell({ includeEmpty: true }, function(cell, colNumber) {
          sheet.getColumn(colNumber).hidden = fn.IGNORE_COLUMNS_EXCEL.includes(colNumber);
        });
      }
      row.hidden = false;
      workbook.views = [
        {
          x: 0, y: 0, width: 10000, height: 20000,
          firstSheet: 0, activeTab: 0, visibility: 'visible'
        }
      ];
      row.commit();
    });

    console.log('Filter data between dates (YYYY-MM-DD): ' + moment(fromDate * 1000).format('YYYY-MM-DD') + ' and ' +
        moment(toDate * 1000).subtract('1', 'days').format('YYYY-MM-DD'));
    console.log(`Update Cell: ${fn.updatedCells} of ${fn.cells} (${((fn.updatedCells/fn.cells) * 100).toFixed(2)}%)`);
    console.log(`Estimated Time(Filter & Edit data in Sheet 1): ${new Date().getTime() - startTime.getTime()}ms`);

    fn.addPostToSheetNumber(workbook, data.post, fromDate, toDate, 2);

    try {
      const startWriteTime = new Date();
      console.log('\r\nRe-write file: ' + filename);

      await workbook.xlsx.writeFile(filename);

      console.log(`Estimated Time(Write file): ${new Date().getTime() - startWriteTime.getTime()}ms`);
    } catch (err) {
      console.error(err);
    }
    console.log(`\r\nEstimated All Time: ${new Date().getTime() - startTime.getTime()}ms`);
  }
};

const fromDate = moment('2019-11-16').unix(), toDate = moment('2019-12-18').add(1, 'days').unix();
fn.updateAnalyticExcel(backup, source, fromDate, toDate);