scores.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 getAdminScores(req, res) {
  var con = mysql.createConnection(dbConfig);
  var query = "SELECT Score.id, score, Part.name AS participant, Equi.name AS equipe, createdAt as date ,Part.id as partid ,Equi.id as equid FROM Score LEFT JOIN (SELECT * FROM Participant WHERE deleted = 0) 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 date 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 getDistanceTotale(req, res) {
  var con = mysql.createConnection(dbConfig);
  var query = "SELECT SUM(score) as distanceTotale FROM Score WHERE deleted = 0;"
  con.connect();
  con.query(query, (err, result) => {
    if (err) {
      console.log(err)
      return res.send({ success: false })
    }
    return res.send(result)
  });
  con.end();
}


function addNewScore(req, res) {
  var con = mysql.createConnection(dbConfig);
  var query = "INSERT INTO `Score` (`participantId`, `equipeId`, `score`) VALUES (?, ?, ?);"
  var inserts = [req.body.participant, req.body.equipe, req.body.score];
  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 updateScore(req, res) {
  var con = mysql.createConnection(dbConfig);
  var query = "UPDATE Score SET participantId = ?, equipeId = ?, score = ? WHERE id=?;"
  var inserts = [req.body.participant, req.body.equipe, req.body.score, 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 updateScoreScore(req, res) {
  var con = mysql.createConnection(dbConfig);
  var query = "UPDATE Score SET score = ? WHERE id=?;"
  var inserts = [req.body.score, 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 updateScoreEquipe(req, res) {
  var con = mysql.createConnection(dbConfig);
  var query = "UPDATE Score SET equipeId = ? WHERE id=?;"
  var inserts = [req.body.equipe, 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 updateScoreParticipant(req, res) {
  var con = mysql.createConnection(dbConfig);
  var query = "UPDATE Score SET participantId = ? WHERE id=?;"
  var inserts = [req.body.participant, 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 deleteScore(req, res) {
  var con = mysql.createConnection(dbConfig);
  var query = "UPDATE Score 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 = { getAdminScores, addNewScore, updateScore, updateScoreScore, updateScoreEquipe, updateScoreParticipant, deleteScore, getDistanceTotale }