question.js

var express = require('express')
, _ = require('underscore')
, async = require("async")
, sqlHelper = require("../lib/sqlHelper");
 
exports.create = function(req,res) {
  res.set('Content-Type', 'text/json');
  if(sqlHelper.requestHas(req.body,["question","answers"]) && req.body.answers.length > 0) {
    clientPool.acquire(function(err,mysql) {
      if(err){
        throw new Error("can't get mysql con",err);
        res.send(500);
        return false;
      }
      sqlHelper.insert(
        mysql,
        "question",
        {
          userID:req.session.userID,
          question:req.body.question,
          numAnswers:req.body.answers.length
        },
        function(err, results) {
          if(err) {
            throw new Error("error inserting into mysql",err);
            res.send(500);
            clientPool.releaseConnection(mysql);
            return false;
          }
          var answerQueries = [];
          _.each(req.body.answers,function(answer) {
            answerQueries.push(function(cb) {
              mysql.query("INSERT INTO `answer` (`questionID`,`answer`) VALUES ('" + results.questionID + "'," + mysql.escape(answer) + ") ;",cb);
            });
          });
          async.series(answerQueries,function(errs,results) {
            if(errs) {
              clientPool.releaseConnection(mysql);
              res.send(500);
              throw new Error("error inserting answers",errs);
              return false;
            }
            mysql.query("UPDATE LOW_PRIORITY `user` SET `numAsked` = `numAsked` + 1, `numNeeded` = `numNeeded` - 1 WHERE `userID` = ?",[req.session.userID],function(err) {
              if(err) {
                clientPool.releaseConnection(mysql);
                res.send(500);
                console.log("error updating user num asked",err);
                return false;
              }
              clientPool.releaseConnection(mysql);
              req.session.numNeeded--;
              res.send({status:'success',question:results,numNeeded:req.session.numNeeded});
            });
          })  
        });
    });
  }else {
    res.send({status:"failed",message:"Something's wrong with the POST request. It needs to contain question and answers"});
  } 
};

exports.stats = function(req,res) {
  req.route.params.questionID = parseInt(req.route.params.questionID);
  if(!isNaN(req.route.params.questionID)) {
    clientPool.acquire(function(err,mysql) {
      mysql.query("SELECT * FROM `question` WHERE `questionID` = ?",[req.route.params.questionID],function(err,questions) {
        mysql.query("SELECT * FROM `answer` WHERE `questionID` = ? LIMIT ?",[req.route.params.questionID,questions[0].numAnswers],function(err,answers) {

          clientPool.releaseConnection(mysql);
          if(err)
            console.log(err);
          _.map(answers,function(answer) {
            answer.percentAnswered = 100 * answer.numResponses / questions[0].numResponses;
            answer.percentAccept = 100 * answer.numAccept / questions[0].numResponses;
            return answer;
          });
          console.log(answers);
          res.render("questionStats",{
            question:questions[0],
            answers:answers,
            _:_
          });
        });
      });
    });
  }
}

exports.index = function(req,res) {
  clientPool.acquire(function(err,mysql) {
    mysql.query("SELECT * FROM `question` WHERE `approved` = 1 ;",function(err,questions) {
      clientPool.releaseConnection(mysql);
      res.render("questionIndex",{questions:questions,_:_});
    });
  });
};

exports.view = function(req,res) {
  res.set('Content-Type', 'text/json');
  clientPool.acquire(function(err,mysql) {
    if(err){
      throw new Error("can't get mysql con",err);
      res.send(500);
      return false;
    }
    sqlHelper.find(mysql,"questionResponse",{userID:req.session.userID},function(err,questionresponses) {
      if(err){
        throw new Error("can't make selection",err);
        res.send(500);
        return false;
      }       

      var query = "SELECT * FROM `question` WHERE `numAnswers` > 0 AND `approved` = 1 ";
      if(questionresponses.length > 0){
        _.each(questionresponses,function(questionresponse,i) {
          query += " AND ";
          query += " questionID != " + mysql.escape(questionresponse.questionID);
        });
      }
      query += " ORDER BY RAND() LIMIT 0,1;";
      mysql.query(query,function(err,questions) {
        if(!questions || questions.length === 0) {
          clientPool.releaseConnection(mysql);
          res.send({status:"failed",numNeeded:req.session.numNeeded,remaining:0,message:"Wow, you've answered ALL the questions. This is great. Check back soon for more!"});
        } else {
          mysql.query("SELECT COUNT(*) as `numQuestions` FROM `question` WHERE `approved` = 1 ;",function(err,numQ) {
            var question = questions[0];
            sqlHelper.find(mysql,"user",{userID:question.userID},function(err,user) {
              if(err) {
                console.log(err);
                throw new Error("can't get user",err);
                res.send(500);
                return false;
              }
              user = user[0];
              sqlHelper.find(mysql,"answer",{questionID:question.questionID},function(err,answers) {
                clientPool.releaseConnection(mysql);
                res.send({
                  status:"success",
                  numNeeded:req.session.numNeeded,
                  remaining:numQ[0].numQuestions-req.session.numResponses,
                  question:question,
                  answers:answers,
                  user:_.omit(user,"password")
                });
              });
            }); 
          }); 
        }   
      });       
    });
  });
}