gistfile1.js

var Q = require("q");

exports.get = function(request, response) {
	var userId = request.user.userId;
    var weekOf = request.query.weekForDate;
    
    // make a query to get the total points for the user for the week
    var weekDataPromise = getWeekFor(weekOf);

    weekDataPromise.then(function(result) {
        var weekInfo = result[0];
        console.log(weekInfo);
        
    	var totalPointsPromise = getTotalPoints(userId, weekInfo.weekId);
    	var totalPredictionsPromise = getTotalPredictions(userId, weekInfo.weekId);
    	var rankingPromise = getRanking(userId, weekInfo.weekId);
        
    	Q.allSettled([totalPointsPromise, totalPredictionsPromise, rankingPromise])
         .spread(function(points, predictions, rankings) {
    		console.log("r " + points);
            console.log("r " + predictions);
            console.log("r " + rankings);
            
    		request.respond(200, "Ok");
    	});
    });

	function getWeekFor(weekOf) {
		var defer = Q.defer();
		var sql = "select top 1 weekNumber, [year], id as weekId from scorepredict.weeks " +
				  "where cast(start as datetime2) <= ? order by cast([end] as datetime2) desc";

		request.service.mssql.queryRaw(sql, [weekOf], {
			success: function(result) {
				if (result.rows.length == 0) {
					defer.reject(new Error("No Week for WeekFor"));
				}
				else {
					defer.resolve({
						weekNumber: result.rows[0][0],
						year: result.rows[0][1],
						weekId: result.rows[0][2]
					});
				}
			}
		});

		return defer.promise;
	}

	function getTotalPoints(userId, weekId) {
		var defer = Q.defer();
		var sql = "select sumPointsAwarded from scorepredict.pointsByUserByWeek " +
				  "where weekId = ? and userId = ?";

		request.service.mssql.queryRaw(sql, [weekId, userId], {
			success: function(result) {
				if (result.rows.length == 0) {
					defer.resolve({
						totalPoints: 0,
						userId: userId,
						weekId: weekId
					});
				}
				else {
					defer.resolve({
						totalPoints: result.rows[0][0],
						userId: userId,
						weekId: weekId
					});
				}
			}
		});

		return defer.promise;
	}

	function getTotalPredictions(userId, weekId) {
		var defer = Q.defer();
		var sql = "select count(*) from scorepredict.predictions p " +
				  "   inner join scorepredict.games g on g.Id = p.GameId " +
				  "where g.WeekId = ? and p.userId = ?";

		request.service.mssql.queryRaw(sql, [weekId, userId], {
		  	success: function(result) {
		  		if (result.rows.length == 0) {
		  			defer.resolve({
		  				totalPredictions: 0,
		  				userId: userId,
		  				weekId: weekId
		  			});
		  		}
		  		else {
		  			defer.resolve({
		  				totalPredictions: result.rows[0][0],
		  				userId: userId,
		  				weekId: weekId
		  			});
		  		}
		  	}
		 });

		return defer.promise;
	}

	function getRanking(userId, weekId) {
		var defer = Q.defer();
		var sql = "select * from scorepredict.pointsByUserByWeek where WeekId = ? " +
				  "order by sumPointsAwarded desc";

		request.service.mssql.queryRaw(sql, [weekId], {
			success: function(result) {
				if (result.rows.length == 0) {
					defer.resolve({yourRank: 0, outOf: 0});
					return;
				}

				var res = {
					yourRank: 0,
					outOf: result.rows.length
				};

				for (var index=0; result.rows.length; index++) {
					if (result.rows[0][0] == userId) {
						res.yourRank = index + 1;
						break;
					}
				}

				defer.resolve(res);
			}
		});

		return defer.promise;
	}
};