equipe.controller.js

const mysql = require('mysql');

var secrets = require('../secrets.js');
var dbhost = secrets.dbhost;
var dbuser = secrets.dbuser;
var dbpassword = secrets.dbpassword;
var dbdatabase = secrets.dbdatabase;

const dbConfig = {
  host: dbhost,
  user: dbuser,
  password: dbpassword,
  database: dbdatabase
};

function getClassementEquipe(req, res) {
  var con = mysql.createConnection(dbConfig);
  /*var query = "SELECT Equipe.id as id,Equipe.name as name,SUM(score) AS score,COUNT(participantId) AS nbParticipant,COUNT(score) AS nbScores FROM Score JOIN Equipe ON Equipe.id=Score.equipeId WHERE Equipe.deleted = 0 AND Score.deleted = 0 GROUP BY equipeId ORDER BY score DESC;"*/
  var query = "SELECT Equipe.id as id,Equipe.name as name,score, participantId AS partId FROM Score JOIN Equipe ON Equipe.id=Score.equipeId WHERE Equipe.deleted = 0 AND Score.deleted = 0 ORDER BY score DESC;"
  con.connect();
  con.query(query, (err, result) => {
    if (err) {
      console.log(err)
      return res.send({ success: false })
    }
    let leader_team = {};
    for (const data of result) {
      if (data.id in leader_team) {
        if (leader_team[data.id].participants.indexOf(data.partId) == -1) {
          leader_team[data.id].participants.unshift(data.partId);
          if (data.partId != 'sprint') {
          leader_team[data.id].nbParticipant += 1;
        }
      }
        leader_team[data.id].score += data.score;
        leader_team[data.id].nbScores += 1;
      } else {
        leader_team[data.id] = {
          name: data.name,
          id: data.id,
          score: data.score,
          participants: [data.partId],
          nbParticipant: 1,
          nbScores: 1,
        }
      }
    }
    result = Object.keys(leader_team).map((key) => leader_team[key]);
    result.sort((a, b) => (a.score < b.score) ? 1 : -1);
    return res.send(result)
  });
  con.end();
}

function getSprint(req, res) {
  var con = mysql.createConnection(dbConfig);
  var query = "SELECT Equipe.name as name,score FROM Score JOIN Equipe ON Equipe.id=Score.equipeId WHERE (Equipe.deleted = 0 AND (Score.deleted = 0 AND Score.participantId = 'sprint')) ORDER BY score DESC;"
  con.connect();
  con.query(query, (err, result) => {
    if (err) {
      console.log(err)
      return res.send({ success: false })
    }
    return res.send(result)
  });
  con.end();
}

function getEquipes(req, res) {
  var con = mysql.createConnection(dbConfig);
  var query = "SELECT id,name FROM Equipe WHERE deleted = 0 ORDER BY name ASC;"
  con.connect();
  con.query(query, (err, result) => {
    if (err) {
      console.log(err)
      return res.send({ success: false })
    }
    return res.send(result)
  });
  con.end();
}

function getEquipesByUserId(req, res) {
  var con = mysql.createConnection(dbConfig);
  var query = "SELECT DISTINCT Equi.name AS equipe, Score.ParticipantId as Partid FROM Score LEFT JOIN (SELECT * FROM Participant WHERE deleted = 0 AND id IS NOT NULL) AS Part ON Part.id=Score.ParticipantId LEFT JOIN (SELECT * FROM Equipe WHERE deleted = 0 ) AS Equi ON Score.equipeId=Equi.id WHERE Score.deleted = 0 ORDER BY equipe ASC;"
  con.connect();
  con.query(query, (err, result) => {
    if (err) {
      console.log(err)
      return res.send({ success: false })
    }
    return res.send(result)
  });
  con.end();
}

function addNewEquipe(req, res) {
  var con = mysql.createConnection(dbConfig);
  var query = "INSERT INTO `Equipe` (`name`) VALUES (?);"
  var inserts = [req.body.name];
  con.connect();
  con.query(query, inserts, (err, result) => {
    if (err) {
      console.log(err)
      return res.send({ success: false })
    }
    return res.send(result)
  });
  con.end();
}

function updateEquipe(req, res) {
  var con = mysql.createConnection(dbConfig);
  var query = "UPDATE Equipe SET name = ? WHERE id=?;"
  var inserts = [req.body.name, req.body.id];
  con.connect();
  con.query(query, inserts, (err, result) => {
    if (err) {
      console.log(err)
      return res.send({ success: false })
    }
    return res.send(result)
  });
  con.end();
}

function deleteEquipe(req, res) {
  var con = mysql.createConnection(dbConfig);
  var query = "UPDATE Equipe SET deleted = 1 WHERE id=?;"
  var inserts = [req.body.id];
  con.connect();
  con.query(query, inserts, (err, result) => {
    if (err) {
      console.log(err)
      return res.send({ success: false })
    }
    return res.send(result)
  });
  con.end();
}


module.exports = { getClassementEquipe, getEquipes, addNewEquipe, updateEquipe, deleteEquipe, getEquipesByUserId, getSprint }