DataAnalysis-Tmp/analysis/index.js

const Student = require("../models/student");
const Database = require('../config/database');
const Mysql = require('../config/mysql');
const CsvReader = require("../config/csvReader");
const StudentService = require("../services/studentService");
const GitlabApi = require("../apis/gitlabClient");
const JsonHandler = require("../config/jsonHandler");
const StudentConstructor = require("../models/constructors/StudentConstructor");
const GroupConstructor = require("../models/constructors/GroupConstructor");
const GroupService = require("../services/groupService");

const userSchema = {
  'id': Number,
  'created_time': String,
  'email': String,
  'is_valid': String,
  'name': String,
  'password': String,
  'phone': String,
  'photo': Uint32Array,
  'role': String,
  'signature': String,
  'username': String
}

const userSchema2 = {
  '组号': Number,
  '姓名': String,
  '学号': String,
  '组长手机号': String
}

class Analysis {
  async run() {
    try {

      /* // mysql 连接
      const mysql = await Mysql.connect();
      let [rows, fields] = await mysql.execute("select * from user limit 10");
      console.log(rows);
      mysql.end(); 
      */

      /* // mongo 连接
      await Database.asyncConnect();
      let data = await StudentService.getAll();
      console.log(data); 
      Database.close();
      */
      this.fromJsonToDatabase()
      // await this.getInterfaceTestStatistics()
    } catch (errors) {
      console.error(errors);
    }
  }

  async getUserInfo() { // 集成所有用户信息
    let portal_user = await CsvReader.readFile(__dirname + '/portal_user.csv', data => {
      let { id, email, username, name, phone } = data;
      let emailReg = /^[0-9]+@s.ail\.nju\.edu\.cn$/;
      if (emailReg.test(email)) {
        username = email.split("@")[0];
      }
      return {
        userId: id, email, studentId: username, name, phone
      }
    });
    let devcloud_user = await CsvReader.readFile(__dirname + '/软工II小组名单.csv', data => {
      let { 组号, 姓名, 学号 } = data;
      return {
        groupId: 组号,
        name: 姓名,
        studentId: 学号
      }
    });
    portal_user.sort((a, b) => {
      if (a.studentId < b.studentId) return -1;
      else if (a.studentId == b.studentId) return 0;
      else return 1;
    });
    if (devcloud_user instanceof Array)
      devcloud_user.forEach((val, idx, arr) => {
        let info = this.findInfo(val, portal_user);
        if (info) {
          arr[idx].userId = info.userId;
          arr[idx].email = info.email;
          arr[idx].phone = info.phone;
        } else {
          console.log('cannot find', val);
        }
      });
    // await JsonHandler.writeSync(devcloud_user, __dirname + '/user_info.json');
  }
  async transformUserInfo() { // 改为小组形式展示用户
    let user_info = await JsonHandler.readSync(__dirname+'/user_info.json');
    let group_user_map = {};
    for(let user of user_info) {
      if(!group_user_map[user.groupId]) group_user_map[user.groupId] = [];
      group_user_map[user.groupId].push(user);
    }
    await JsonHandler.writeSync(group_user_map, __dirname+'/group_user_map.json');
  }
  async getProjectsByGroup() { // 按小组拿到项目
    let devcloud_user = await JsonHandler.readSync(__dirname + '/user_info.json');
    let group_project_info_map = {};
    for(let i=0;i<devcloud_user.length;i++) {
    // for (let i = 0; i < 5; i++) {
      if (!group_project_info_map[devcloud_user[i].groupId]) {
        group_project_info_map[devcloud_user[i].groupId] = [];
      }
      let proj_list = group_project_info_map[devcloud_user[i].groupId];
      let user_projects = await GitlabApi.getAllProjetsByUserId(devcloud_user[i].userId);
      if (proj_list.length === 0) proj_list = user_projects.res;
      else proj_list = this.getIntersection(proj_list, user_projects.res, "projectId");
      group_project_info_map[devcloud_user[i].groupId]=proj_list;
      console.log(i, " -- finish")
    }
    // console.log(group_project_info_map);
    // await JsonHandler.writeSync(group_project_info_map, __dirname + '/group_project_info_map.json');
  }
  async getCommitsByGroup() { // 按照小组拿到commit 记录
    let group_project_info_map = await JsonHandler.readSync(__dirname + '/group_project_info_map.json');
    let group_commits_map = {};
    for(let k of Object.keys(group_project_info_map)) {
      console.log("group -- ",k);
      if(!group_commits_map[k]) group_commits_map[k] = [];
      for(let project of group_project_info_map[k]) {
        console.log("project -- ",project.projectId);
        let commits = await GitlabApi.getCommitsByGitlabProjectId(project.projectId);
        group_commits_map[k].push(...commits.map((commit) => { 
          let {id, message, committer_name, committer_email} = commit;
          return {id, message, committer_name, committer_email, projectId: project.projectId};
        }));
      }
    }
    // JsonHandler.writeSync(group_commits_map, __dirname + '/group_commits_map.json');
    // console.log(group_commits_map)
  }
  async getCommitsStatistics() { // 按小组生成最终的commit报告
    let group_commits_map = await JsonHandler.readSync(__dirname+'/group_commits_map.json');
    let group_user_map = await JsonHandler.readSync(__dirname+'/group_user_map.json')
    let group_user_commits_map = {};
    for(let k of Object.keys(group_commits_map)) {
      if(!group_user_commits_map[k]) group_user_commits_map[k] = {};
      let tmpMap = {};
      for(let commit of group_commits_map[k]) {
        if(!tmpMap[commit.committer_name]) tmpMap[commit.committer_name] = { 
          count: 0,
          committer_email: commit.committer_email,
          commit_messages: []
        };
        tmpMap[commit.committer_name].count += 1;
        tmpMap[commit.committer_name].commit_messages.push(commit.message);
      }
      for(let committer_name of Object.keys(tmpMap)) {
        let user = this.findUserByCommitterName(committer_name, group_user_map[k]);
        if(user == null) user = this.findUserByCommitterEmail(tmpMap[committer_name].committer_email, group_user_map[k]);
        let user_identity = committer_name;
        if(user) {
          user_identity = user.studentId
        }
        // 根据 committer_name 从数据库拿到 "valid-commit-times","buglist-commit","feature-commit"
        const mysql = await Mysql.connect();
        let bug = await mysql.execute("select count(*) as count from `commit` where `gitlab_username` = ? and `related_type` = 'BUG_ID'", [committer_name]);
        let tree = await mysql.execute("select count(*) as count from `commit` where `gitlab_username` = ? and `related_type` = 'TREE_ID'", [committer_name]);
        mysql.end(); 
        group_user_commits_map[k][user_identity] = {
          'commit-times': tmpMap[committer_name].count,
          'commit-msg-overview': tmpMap[committer_name].commit_messages,
          'buglist-commit': bug[0][0]['count'],
          'feature-commit': tree[0][0]['count'],
          'valid-commit-times': bug[0][0]['count'] + tree[0][0]['count']
        }
      }
      console.log(group_user_commits_map)
      await JsonHandler.writeSync(group_user_commits_map, __dirname+'/group_user_commits_map.json');
    }
  }
  async getPipelineStatistics() { // 按小组生成Pipeline报告
    // 警告: 理论上应该从项目出发统计Pipeline触发数,但是数据库中存在很多项目Pipeline被删除的情况,
    // 因此我直接统计一段时间内所有用户的Pipeline触发次数, 非本次项目的也可能会统计在内
    // (宁愿多取一点数据)
    // "pipeline": {
    //   "pipeline-deploy":0,
    //   "pipeline-pass":0,
    //   "pipeline-failure":0
    // },
    let group_user_map = await JsonHandler.readSync(__dirname+'/group_user_map.json');
    let group_user_pipeline_map = {};
    let startTime = '2022-02-13 20:13:10';
    const mysql = await Mysql.connect();
    for(let k of Object.keys(group_user_map)) {
      if(!group_user_pipeline_map[k]) group_user_pipeline_map[k] = {};
      let user_list = group_user_map[k];
      for(let user of user_list) {
        group_user_pipeline_map[k][user.studentId] = {
          "pipeline-deploy":0,
          "pipeline-pass":0,
          "pipeline-failure":0
        };
        let [row] = await mysql.execute("select result,count(*) as count from pipeline_record where start_time > ? and user_id = ? group by result;", [startTime, user.userId]);
        for(let res of row) {
          if(res.result === '部署失败') {
            group_user_pipeline_map[k][user.studentId]["pipeline-failure"] += res.count;
          } else if(res.result === '部署成功') {
            group_user_pipeline_map[k][user.studentId]["pipeline-pass"] += res.count;
          }
        }
        group_user_pipeline_map[k][user.studentId]["pipeline-deploy"] = 
        group_user_pipeline_map[k][user.studentId]["pipeline-pass"] + 
        group_user_pipeline_map[k][user.studentId]["pipeline-failure"];
      }
    }
    mysql.end(); 
    // await JsonHandler.writeSync(group_user_pipeline_map, __dirname+'/group_user_pipeline_map.json');
  }
  async getInterfaceTestStatistics() { // 按小组生成接口测试报告
    // 由于依赖Pipeline, 所以也有上述问题
    let group_user_map = await JsonHandler.readSync(__dirname+'/group_user_map.json');
    let group_user_interface_test_map = {};
    let startTime = '2022-02-13 20:13:10';
    const mysql = await Mysql.connect();
    for(let k of Object.keys(group_user_map)) {
      if(!group_user_interface_test_map[k]) group_user_interface_test_map[k] = {};
      let user_list = group_user_map[k];
      for(let user of user_list) {
        let [row] = await mysql.execute("select count(*) as times, COALESCE(sum(success),0) as success, COALESCE(sum(failure),0) as failure from test_result where test_result.pipeline_record_id in (select id as pipe_record_id from pipeline_record where start_time > ? and user_id = ? ) ;", [startTime, user.userId]);
        // console.log(row[0]); // { times: 0, success: null, failure: null }
        row[0]['times'] = parseInt(row[0]['times']);
        row[0]['failure'] = parseInt(row[0]['failure']);
        row[0]['success'] = parseInt(row[0]['success']);
        group_user_interface_test_map[k][user.studentId] =  {
          "interface-test-times": row[0]['times'],
          "interface-test-concurrency-count": row[0]['failure'] + row[0]['success'],
          "interface-test-concurrency-failure": row[0]['failure'],
          "interface-test-concurrency-pass": row[0]['success']
        };
      }
    }
    mysql.end(); 
    await JsonHandler.writeSync(group_user_interface_test_map, __dirname+'/group_user_interface_test_map.json');
  }
  async fromJsonToDatabase() { // 将上面生成的一系列json文件组合导入数据库
    // TODO
    const group_user_map = await JsonHandler.readSync(__dirname+'/group_user_map.json');
    const group_project_info_map = await JsonHandler.readSync(__dirname+'/group_project_info_map.json');
    const group_user_commits_map = await JsonHandler.readSync(__dirname+'/group_user_commits_map.json');
    const group_user_pipeline_map = await JsonHandler.readSync(__dirname+'/group_user_pipeline_map.json');
    const group_user_interface_test_map = await JsonHandler.readSync(__dirname+'/group_user_interface_test_map.json');
     // mongo 连接
    await Database.asyncConnect();

    const generateGroupMembers = function(g) {
      return group_user_map[g].map(item => {
        return {
          "userId": item.userId,
          "studentId":item.studentId, 
          "name":item.name
        };
      });
    }
    const generateGroupCommit = function(g) {
      let raw = Object.entries(group_user_commits_map[g])
      .map(v => {
        v[1].identity = v[0];
        return v[1];
      });
      let res = raw.reduce((prev, cur) => {
        prev["commit-times"] += cur["commit-times"];
        prev["valid-commit-times"] += cur["valid-commit-times"];
        prev["buglist-commit"] += cur["buglist-commit"];
        prev["feature-commit"] += cur["feature-commit"];
        prev["commit-msg-overview"].push(...cur[["commit-msg-overview"]]);
        return prev;
      },{
        "commit-times": 0,
        "valid-commit-times": 0,
        "buglist-commit": 0,
        "feature-commit": 0,
        "commit-msg-overview": []
      });
      res["commit-contribution"] = raw.map(item => {
        return {
          "name": item.identity, 
          "times": item["commit-times"], 
          "proportion": res["commit-times"] === 0 ? 0 : (item["commit-times"] / res["commit-times"] * 100).toFixed(2)
        }
      });
      return res;
    }
    const generateGroupInterfaceTest = function(g) {
      let raw = Object.entries(group_user_interface_test_map[g])
      .map(v => {
        v[1].identity = v[0];
        return v[1];
      });
      let res = raw.reduce((prev, cur) => {
        prev["interface-test-times"] += cur["interface-test-times"];
        prev["interface-test-concurrency-count"] += cur["interface-test-concurrency-count"];
        prev["interface-test-concurrency-failure"] += cur["interface-test-concurrency-failure"];
        prev["interface-test-concurrency-pass"] += cur["interface-test-concurrency-pass"];
        return prev;
      },{
        "interface-test-times": 0,
        "interface-test-concurrency-count": 0,
        "interface-test-concurrency-failure": 0,
        "interface-test-concurrency-pass": 0
      });
      res["interface-test-contribution"] = raw.map(item => {
        return {
          "name": item.identity, 
          "times": item["interface-test-times"], 
          "proportion": res["interface-test-times"] === 0 ? 0 : (item["interface-test-times"] / res["interface-test-times"] * 100).toFixed(2)
        }
      });
      return res;
    }
    const generateGroupPipeline = function(g) {
      let raw = Object.entries(group_user_pipeline_map[g])
      .map(v => {
        v[1].identity = v[0];
        return v[1];
      });
      let res = raw.reduce((prev, cur) => {
        prev["pipeline-deploy"] += cur["pipeline-deploy"];
        prev["pipeline-pass"] += cur["pipeline-pass"];
        prev["pipeline-failure"] += cur["pipeline-failure"];
        return prev;
      },{
        "pipeline-deploy": 0,
        "pipeline-pass": 0,
        "pipeline-failure": 0,
      });
      res["pipeline-contribution"] = raw.map(item => {
        return {
          "name": item.identity, 
          "times": item["pipeline-deploy"], 
          "proportion": res["pipeline-deploy"] === 0 ? 0 : (item["pipeline-deploy"] / res["pipeline-deploy"] * 100).toFixed(2)
        }
      });
      return res;
    }

    for(let group of Object.keys(group_user_map)) {
      // 生成小组报告, 入库
      let projects = group_project_info_map[group];
      let members = generateGroupMembers(group);
      let group_commit = generateGroupCommit(group);
      let group_interface_test = generateGroupInterfaceTest(group);
      let group_pipeline = generateGroupPipeline(group);

      let groupInfo = new GroupConstructor(
        group,
        projects,
        members,
        group_commit,
        group_interface_test,
        group_pipeline
      );
      // console.log(groupInfo);
      GroupService.create(groupInfo);
      // for(let user of group_user_map[group]) { // 生成个人报告, 入库
      //   let commit = group_user_commits_map[group][user.studentId];
      //   if(commit == null) commit = {
      //     "commit-times": 0,
      //     "valid-commit-times": 0,
      //     "buglist-commit": 0,
      //     "feature-commit": 0,
      //     "commit-msg-overview": []
      //   };
      //   let interface_test = group_user_interface_test_map[group][user.studentId];
      //   let pipeline = group_user_pipeline_map_map[group][user.studentId];
      //   let studentInfo = new StudentConstructor(
      //     user.name,
      //     user.userId,
      //     user.studentId,
      //     group,
      //     user.email,
      //     commit,
      //     projects,
      //     interface_test,
      //     pipeline
      //   );
      //   // console.log(studentInfo)
      //   StudentService.create(studentInfo);
      // }
    }
  }
  findUserByCommitterEmail(committer_email, user_list) { // 通过committer email找用户
    for(let user of user_list) {
      if(committer_email === user.email) return user;
    }
    return null;
  }
  findUserByCommitterName(committer_name, user_list) { // 通过committer name找用户
    for(let user of user_list) {
      if(committer_name === user.name || committer_name === user.studentId || committer_name === user.phone) return user;
    }
    return null;
  }
  findInfo(devUser, portalUsers) { // 在portalUser中找到devUser所对应的信息, 二分查找
    let l = 0, r = portalUsers.length - 1;
    while (l <= r) {
      let m = Math.floor((r - l) / 2) + l;
      if (portalUsers[m].studentId === devUser.studentId) {
        return portalUsers[m];
      } else if (portalUsers[m].studentId < devUser.studentId) {
        l = m + 1;
      } else {
        r = m - 1;
      }
    }
    return null;
  }
  getIntersection(arr_a, arr_b, key) { // 求两个数组的交集
    let res = [];
    arr_a.forEach(a => {
      arr_b.forEach(b => {
        if (a[key] === b[key]) {
          res.push(a);
        }
      })
    })
    return res;
  }
}

(new Analysis()).run().then(_ => {
  // process.exit(0);
}).catch(err => {
  console.error(err);
})