function mastdataservice()
{
$conn = mysqlconnectionservice();
$mast_stmt = $conn->prepare("SELECT mastimg, quote, credit FROM mastcontent");
$mast_stmt->execute();
$mast_stmt->bind_result($img, $quote, $credit);
$content = array();
while ($mast_stmt->fetch()) {
$content[] = array("img" => $img, "quote" => $quote, "credit" => $credit);
}
return $content;
}
function userschemaupdateservice($keys, $table, $attribute, $value)
{
$typestring = resolveType($value);
if ($typestring == "" || !isset($_COOKIE['userid'])) {
return false;
}
if (gettype($value) == "boolean") {
$value = intval($value);
}
$conn = mysqlconnectionservice();
$qstring = "UPDATE " . $table . " SET " . $attribute . "=?" . " WHERE userid=? " . resolveKeys($keys);
$stmt = $conn->prepare($qstring);
$stmt->bind_param($typestring . "s", $value, $_COOKIE['userid']);
return $stmt->execute();
}
function userschemaaddservice($table, $rows)
{
if (count($rows) == 0) {
return false;
}
// empty rows is treated as a failed request.
//if (!isset($_COOKIE['userid'])) return false; // failed because of unauthentication
$columns = array_keys($rows[0]);
sort($columns);
$column_count = count($columns);
if ($column_count == 0) {
return false;
}
$allvalues = array('');
foreach ($rows as $i => $row) {
if (count($row) != $column_count) {
return false;
}
// every column should be the same number of rows
ksort($rows[$i]);
// garauntee strict ordering of keys, which is important for inserts
$allvalues[0] = $allvalues[0] . get_typesdescriptor($rows[$i]);
$allvalues = array_merge($allvalues, array_values($rows[$i]));
}
$columns[] = 'userid';
$qstring = 'INSERT INTO ' . $table . ' (' . join(',', $columns) . ') VALUES ';
array_pop($columns);
// remove that userid we just added
$qmarks = array_fill(0, $column_count, '?');
array_push($qmarks, '"' . "gapoorva" . '"');
$insertion_list = array_fill(0, count($rows), join(',', $qmarks));
$qstring .= '(' . join('),(', $insertion_list) . ')';
// create a param_list as a set of references
$param_list = array();
for ($i = 0; $i < count($allvalues); $i++) {
$param_list[] =& $allvalues[$i];
}
$conn = mysqlconnectionservice();
$stmt = $conn->prepare($qstring);
call_user_func_array(array($stmt, 'bind_param'), $param_list);
return $stmt->execute();
}
function loginservice($userid, $pw)
{
$conn = mysqlconnectionservice();
$login_stmt = $conn->prepare("UPDATE auth SET token=? WHERE userid=? AND pw=?");
$now = time();
$pw = salted_pw($userid, $pw);
$token = hash("sha256", $pw . strval($now));
$login_stmt->bind_param("sss", $token, $userid, $pw);
$login_stmt->execute();
$auths = $conn->affected_rows;
if ($auths != 1) {
return false;
} else {
// set cookie before returning true
setcookie("token", $token, $now + 12 * 3600);
// Login is good for 12 hrs.
setcookie("userid", $userid, $now + 12 * 3600);
// Login is good for 12 hrs.
return true;
}
}
function userschemaservice()
{
// if (!isset($_COOKIE['userid'])) {
// // user is unauthenticated, return empty and trust caller to check & handle
// return array();
// }
$conn = mysqlconnectionservice();
// TODO: Replace this static variable with a efficient, dynamically loaded solution from SQL. I'm doing this now, because making requests to msql to desc each table would just add a bunch of latency to this service. For the current small list of data points, it's not too bad to just list them statically in a file.
$colnames = array('users' => array('firstname', 'lastname', 'roll', 'verified', 'email', 'img'), 'profile' => array('major', 'city', 'state', 'grad_year', 'grad_sem', 'pledge_class', 'nickname', 'gender', 'phone', 'biguserid'), 'jobs' => array('title', 'company', 'description', 'startT', 'endT', 'link'), 'projects' => array('projectname', 'description', 'startT', 'endT', 'link'), 'hobbies' => array('hobby'), 'skills' => array('skill'), 'thetataucareer' => array('roleid', 'year', 'semester'), 'social_profile' => array('profiletype', 'link'), 'userroles' => array('roleid'));
$schemaData = array();
foreach ($colnames as $tbl => $cols) {
$qstring = 'SELECT ' . join(',', $cols) . ' FROM ' . $tbl . ' WHERE userid=?';
$stmt = $conn->prepare('SELECT ' . join(',', $cols) . ' FROM ' . $tbl . ' WHERE userid=?');
$userid = "gapoorva";
$stmt->bind_param('s', $userid);
$stmt->execute();
$schemaData[$tbl] = get_result($stmt);
$stmt->close();
}
return $schemaData;
}
function tokenauthservice()
{
$conn = mysqlconnectionservice();
$token_auth_stmt = $conn->prepare("SELECT COUNT(*) FROM auth WHERE userid=? AND token=?");
// no cookies? no auth
if (!isset($_COOKIE['token']) || !isset($_COOKIE['userid'])) {
return false;
}
$token_auth_stmt->bind_param("ss", $_COOKIE['userid'], $_COOKIE['token']);
$token_auth_stmt->execute();
$token_auth_stmt->bind_result($auths);
if ($token_auth_stmt->fetch()) {
if ($auths == 1) {
// should only be matching one...
return true;
} else {
return false;
}
}
return false;
// just cause
}
function statsservice($stats)
{
if (count($stats) == 0) {
return $stats;
}
$conn = mysqlconnectionservice();
$answers = array();
foreach ($stats as $stat) {
$querystring = null;
// create a query string if appropriate and evaluate
switch ($stat) {
case 'count_actives':
$querystring = 'SELECT COUNT(*) AS `number` FROM userroles WHERE roleid="active"';
break;
case 'count_actives_majors':
$querystring = 'SELECT COUNT(DISTINCT(P.major)) AS `number` FROM profile AS P, userroles AS U WHERE P.userid = U.userid AND U.roleid="active"';
break;
case 'percent_female_active_brothers':
// technically this can only be evaluated by mysql query BUT
// hack for this one cause a single query is too complicated
$answers[$stat] = getGirlPercentage($conn, $answers['count_actives']);
break;
case 'count_alumni':
$querystring = 'SELECT COUNT(*) AS `number` FROM userroles WHERE roleid="alumni"';
break;
case 'count_alumni_companies':
$querystring = 'SELECT COUNT(DISTINCT(J.company)) AS `number` FROM profile AS P, userroles AS U, jobs as J WHERE P.userid = U.userid AND P.userid=J.userid AND U.roleid="alumni"';
break;
case 'count_alumni_cities':
$querystring = 'SELECT COUNT(DISTINCT(P.city)) AS `number` FROM profile AS P, userroles AS U WHERE P.userid = U.userid AND U.roleid="alumni"';
break;
}
if ($querystring) {
if ($result = $conn->query($querystring)) {
$answers[$stat] = $result->fetch_object()->number;
}
} else {
// not suitable for query, so it must be obtained by a custom function
switch ($stat) {
case 'chapter_age':
$answers[$stat] = floor(abs(TimeOffsetFromNow(1999, 4, 17, time())) / (60 * 60 * 24 * 365));
break;
case 'chapter_number':
$answers[$stat] = 51;
break;
case 'events_this_semester':
$thismonth = m();
$events = array();
$now = time();
if ($thismonth >= 1 && $thismonth <= 4) {
// winter
$events = calendarservice($now, y(), 1, 1, y(), 4, 30);
} else {
if ($thismonth >= 5 && $thismonth <= 7) {
// summer
$events = calendarservice($now, y(), 5, 1, y(), 7, 31);
} else {
// fall
$events = calendarservice($now, y(), 8, 1, y(), 12, 31);
}
}
$answers[$stat] = count($events);
break;
}
}
}
// END FOREACH (if stat was a known stat, it was set in answers)
return $answers;
}
