function load_all($view = 'full', $params = false)
{
$where = array();
if (isset($_REQUEST['membersOnly'])) {
//$where[] = 'U.isMember = 1';
}
if (isset($_REQUEST['teamEligibleOnly'])) {
//$where[] = "U.optInStudy = 1 AND U.eligibility = 'team'";
}
if (isset($_REQUEST['startDate'])) {
$where[] = "Log.t > '" . $_REQUEST['startDate'] . " 00:00:00'";
$startLabel = date('l jS \\of F Y', strtotime($params['startDate']));
} else {
//$where[] = "Challenges.dateEnd > '".date("Y-m-d 00:00:00", time())."'";
$where[] = "Log.t > '" . date("2000-01-01 00:00:00", time()) . "'";
$startLabel = date('l jS \\of F Y', time());
}
if (isset($_REQUEST['endDate'])) {
$where[] = "Log.t < '" . $_REQUEST['endDate'] . " 23:59:59'";
$endLabel = date('l jS \\of F Y', strtotime($params['endDate']));
} else {
$endDate = false;
$endLabel = 'Now';
}
if (isset($_REQUEST['siteid']) && $_REQUEST['siteid'] != 0) {
$currSiteId = $_REQUEST['siteid'];
} else {
$currSiteId = false;
}
if (count($where)) {
$wherestr = ' ' . join(' AND ', $where);
} else {
$wherestr = '';
}
if ($view == 'pod') {
$usersql = "\n\t\t\t\tSELECT id, title, requires, type, dateEnd\n\t\t\t\tFROM Challenges {$wherestr}\n\t\t\t\tORDER BY dateEnd ASC\n\t\t\t";
} else {
$usersql = "\n\t\t\t\tSELECT id, title, requires, type, dateEnd, pointValue,\n\t\t\t\t\t(SELECT COUNT(1) FROM Log LEFT JOIN ChallengesCompleted ON Log.itemid = ChallengesCompleted.id WHERE {$wherestr} AND ChallengesCompleted.challengeid = Challenges.id AND action = 'completedChallenge') AS numCompletedChallenge,\n\t\t\t\t\t(SELECT count(distinct(userid)) from ChallengesCompleted WHERE ChallengesCompleted.challengeid = Challenges.id) as numParticipants\n\t\t\t\tFROM Challenges ORDER BY dateEnd ASC\n\t\t\t";
//FROM Content $wherestr
}
$sites = array();
$sites_res = $this->model->db->query("SELECT * FROM Sites");
while (($row = mysql_fetch_assoc($sites_res)) !== false) {
$sites[$row['name']] = array('dbname' => $row['dbname'], 'siteid' => $row['id']);
}
$jsonarr = array('Results' => array('replyCode' => '201', 'replyText' => 'Data Follows', 'data' => array()));
foreach ($sites as $name => $dbinfo) {
$dbname = $dbinfo['dbname'];
$siteid = $dbinfo['siteid'];
$this->model->db->selectdb($dbname);
if ($currSiteId && $currSiteId != $siteid) {
continue;
}
$results = $this->model->db->query($usersql);
$stories = array();
while (($row = mysql_fetch_assoc($results)) !== false) {
$stories[] = $row;
}
foreach ($stories as $story) {
//$baseurl = 'http://apps.facebook.com/hotdish/';
//$title = sprintf("<a href=\"%s?p=read&cid=%s\">%s</a>", $baseurl, $story['itemid'], $story['title']);
if ($view == 'pod') {
$tmp = array('title' => util_link_for($story['title'], 'street_team', 'challenge_detail_report', $story['id']), 'type' => $story['type'], 'requires' => $story['requires']);
} else {
if ($view == 'full') {
$tmp = array('title' => util_link_for($story['title'], 'street_team', 'challenge_detail_report', $story['id']), 'siteid' => $name, 'type' => $story['type'], 'requires' => $story['requires'], 'pointValue' => $story['pointValue'], 'numParticipants' => $story['numParticipants'], 'numCompletedChallenge' => $story['numCompletedChallenge'], 'dateEnd' => $story['dateEnd']);
} else {
if ($view == 'csv') {
$tmp = array('title' => $story['title'] . ' id: ' . $story['id'], 'siteid' => $name, 'type' => $story['type'], 'requires' => $story['requires'], 'pointValue' => $story['pointValue'], 'numParticipants' => $story['numParticipants'], 'numCompletedChallenge' => $story['numCompletedChallenge'], 'dateEnd' => $story['dateEnd']);
}
}
}
$jsonarr['Results']['data'][] = $tmp;
}
}
//$jsonarr['Results']['SQL'] = stripslashes(preg_replace(array('/\n/', '/\t/'), array(' ', ' '), $usersql));
if ($view != 'csv') {
return json_encode($jsonarr);
} else {
$fields = array_keys($jsonarr['Results']['data'][0]);
echo join(',', $fields) . "\n";
foreach ($jsonarr['Results']['data'] as $row) {
$str = '';
foreach ($fields as $field) {
$str .= '"' . str_replace('"', '\\"', $row[$field]) . '",';
}
$str = substr($str, 0, -1);
echo $str . "\n";
}
exit;
}
}
function load_all($view = 'full', $params = false)
{
$where = array();
if (isset($_REQUEST['membersOnly'])) {
$where[] = 'U.isMember = 1';
}
if (isset($_REQUEST['teamEligibleOnly'])) {
$where[] = "U.optInStudy = 1 AND U.eligibility = 'team'";
}
if (isset($_REQUEST['startDate'])) {
//$where[] = "L.t > '".$_REQUEST['startDate']." 00:00:00'";
$startLabel = date('l jS \\of F Y', strtotime($params['startDate']));
} else {
//$where[] = "L.t > '".date("Y-m-d 00:00:00", time())."'";
//$where[] = "L.t > '".date("2000-01-01 00:00:00", time())."'";
$startLabel = date('l jS \\of F Y', time());
}
if (isset($_REQUEST['endDate'])) {
//$where[] = "L.t < '".$_REQUEST['endDate']." 23:59:59'";
$endLabel = date('l jS \\of F Y', strtotime($params['endDate']));
} else {
$endDate = false;
$endLabel = 'Now';
}
if (isset($_REQUEST['siteid']) && $_REQUEST['siteid'] != 0) {
$where[] = 'U.siteid = "' . $_REQUEST['siteid'] . '"';
}
if ($view == 'csv') {
$survey_join = ' LEFT JOIN SurveyMonkeys as S ON S.userid = U.userid AND S.siteid = U.siteid';
$survey_select = ' S.*, ';
} else {
$survey_join = '';
$survey_select = '';
}
if (count($where)) {
$wherestr = ' AND ' . join(' AND ', $where);
} else {
$wherestr = '';
}
if ($this->studyEndDate !== false && $_REQUEST['siteid'] != 0 && isset($this->studyEndDate['siteid-' . $_REQUEST['siteid']])) {
$study_date = $this->studyEndDate['siteid-' . $_REQUEST['siteid']];
$study_sql = " AND end_session < '{$study_date}' ";
} else {
$study_sql = '';
}
$usersql = "\n\t\t\tSELECT U.siteid as user_siteid, U.userid as user_userid, U.email as user_email,U.dateRegistered,U.age, U.gender, U.state, U.country, U.researchImportance as interestLevel, U.cachedPointTotal,\n\t\t\t\tU.voteCount,U.postStoryCount, U.postCommentCount, U.postBlogCount, U.readStoryCount, U.completedChallengeCount, U.wonPrizeCount,\n\t\t\t\tU.chatStoryCount, U.inviteFriendsCount, U.shareStoryCount, U.tweetCount, U.bookmarkToolAdded, U.friendsSignUpCount,\n\t\t\t\t{$survey_select}\n\t\t\t\t(SELECT count(1) FROM SessionLengths WHERE SessionLengths.userid = U.userid AND SessionLengths.siteid = U.siteid {$study_sql}) as visitsCount,\n\t\t\t\t(SELECT count(DISTINCT(date_format(start_session, '%y-%m-%d'))) from SessionLengths where SessionLengths.userid = U.userid AND SessionLengths.siteid = U.siteid {$study_sql}) as daysOnSiteCount,\n\t\t\t\t(SELECT avg(total_actions) FROM SessionLengths WHERE SessionLengths.userid = U.userid AND SessionLengths.siteid = U.siteid {$study_sql}) as avgPageViewVisit,\n\t\t\t\t(SELECT avg(session_length) FROM SessionLengths WHERE SessionLengths.userid = U.userid AND SessionLengths.siteid = U.siteid {$study_sql}) as avgSessionTime,\n\t\t\t\t(SELECT (min(0 + session_length) + max(0 + session_length)) / 2 FROM SessionLengths WHERE SessionLengths.userid = U.userid AND SessionLengths.siteid = U.siteid AND session_length != 0 {$study_sql}) as medianSessionTime\n\t\t\tFROM UserCollectives as U\n\t\t\t{$survey_join}\n\t\t\tWHERE U.userid != 0 AND ((U.rxConsentForm=1 AND U.age<18) OR U.age>17) {$wherestr}\n\t\t";
//LIMIT 2000
$results = $this->model->db->query($usersql);
$users = array();
while (($row = mysql_fetch_assoc($results)) !== false) {
$users[] = $row;
}
$jsonarr = array('Results' => array('replyCode' => '201', 'replyText' => 'Data Follows', 'data' => array()));
foreach ($users as $user) {
//$baseurl = 'http://apps.facebook.com/hotdish/';
//$title = sprintf("<a href=\"%s?p=read&cid=%s\">%s</a>", $baseurl, $user['itemid'], $user['title']);
if ($view == 'pod') {
$tmp = array('userid' => util_link_for($user['user_userid'], 'members', 'view_member', $user['user_userid']), 'email' => $user['user_email'], 'age' => $user['age']);
} else {
if ($view == 'full') {
$tmp = array('userid' => util_link_for($user['user_userid'], 'members', 'view_member', $user['user_userid']), 'siteid' => $user['user_siteid'], 'email' => $user['user_email'], 'dateRegistered' => $user['dateRegistered'], 'age' => $user['age'], 'gender' => $user['gender'], 'state' => $user['state'], 'country' => $user['country'], 'interestLevel' => $user['interestLevel'], 'voteCount' => $user['voteCount'], 'postStoryCount' => $user['postStoryCount'], 'postCommentCount' => $user['postCommentCount'], 'postBlogCount' => $user['postBlogCount'], 'readStoryCount' => $user['readStoryCount'], 'completedChallengeCount' => $user['completedChallengeCount'], 'wonPrizeCount' => $user['wonPrizeCount'], 'chatStoryCount' => $user['chatStoryCount'], 'inviteFriendsCount' => $user['inviteFriendsCount'], 'shareStoryCount' => $user['shareStoryCount'], 'cachedPointTotal' => $user['cachedPointTotal'], 'tweetCount' => $user['tweetCount'], 'bookmarkToolAdded' => $user['bookmarkToolAdded'], 'friendsSignUpCount' => $user['friendsSignUpCount'], 'visitsCount' => $user['visitsCount'], 'daysOnSiteCount' => $user['daysOnSiteCount'], 'avgPageViewVisit' => $user['avgPageViewVisit'], 'avgSessionTime' => round($user['avgSessionTime'], 2), 'medianSessionTime' => round($user['medianSessionTime'], 2), 'usageClass' => $this->getUsageClass($user['avgSessionTime']));
} else {
if ($view == 'csv') {
$tmp = array('userid' => $user['user_userid'], 'siteid' => $user['user_siteid'], 'email' => $user['user_email'], 'dateRegistered' => $user['dateRegistered'], 'age' => $user['age'], 'gender' => $user['gender'], 'state' => $user['state'], 'country' => $user['country'], 'interestLevel' => $user['interestLevel'], 'voteCount' => $user['voteCount'], 'postStoryCount' => $user['postStoryCount'], 'postCommentCount' => $user['postCommentCount'], 'postBlogCount' => $user['postBlogCount'], 'readStoryCount' => $user['readStoryCount'], 'completedChallengeCount' => $user['completedChallengeCount'], 'wonPrizeCount' => $user['wonPrizeCount'], 'chatStoryCount' => $user['chatStoryCount'], 'inviteFriendsCount' => $user['inviteFriendsCount'], 'shareStoryCount' => $user['shareStoryCount'], 'cachedPointTotal' => $user['cachedPointTotal'], 'tweetCount' => $user['tweetCount'], 'bookmarkToolAdded' => $user['bookmarkToolAdded'], 'friendsSignUpCount' => $user['friendsSignUpCount'], 'visitsCount' => $user['visitsCount'], 'daysOnSiteCount' => $user['daysOnSiteCount'], 'avgPageViewVisit' => $user['avgPageViewVisit'], 'avgSessionTime' => round($user['avgSessionTime'], 2), 'medianSessionTime' => round($user['medianSessionTime'], 2), 'usageClass' => $this->getUsageClass($user['avgSessionTime']));
$tmp['q1a'] = $user['q1a'];
$tmp['q1b'] = $user['q1b'];
$tmp['q1c'] = $user['q1c'];
$tmp['q1d'] = $user['q1d'];
$tmp['q1e'] = $user['q1e'];
$tmp['q1f'] = $user['q1f'];
$tmp['q1g'] = $user['q1g'];
$tmp['q2a'] = $user['q2a'];
$tmp['q2b'] = $user['q2b'];
$tmp['q2c'] = $user['q2c'];
$tmp['q2d'] = $user['q2d'];
$tmp['q2e'] = $user['q2e'];
$tmp['q3a'] = $user['q3a'];
$tmp['q3b'] = $user['q3b'];
$tmp['q3c'] = $user['q3c'];
$tmp['q3d'] = $user['q3d'];
$tmp['q3e'] = $user['q3e'];
$tmp['q3f'] = $user['q3f'];
$tmp['q3g'] = $user['q3g'];
$tmp['q3h'] = $user['q3h'];
$tmp['q4a'] = $user['q4a'];
$tmp['q4b'] = $user['q4b'];
$tmp['q4c'] = $user['q4c'];
$tmp['q4d'] = $user['q4d'];
$tmp['q4e'] = $user['q4e'];
$tmp['q4f'] = $user['q4f'];
$tmp['q4g'] = $user['q4g'];
$tmp['q5a'] = $user['q5a'];
$tmp['q5b'] = $user['q5b'];
$tmp['q5c'] = $user['q5c'];
$tmp['q5d'] = $user['q5d'];
$tmp['q5e'] = $user['q5e'];
$tmp['q5f'] = $user['q5f'];
$tmp['q5g'] = $user['q5g'];
$tmp['q5h'] = $user['q5h'];
$tmp['q5i'] = $user['q5i'];
$tmp['q5j'] = $user['q5j'];
$tmp['q5k'] = $user['q5k'];
$tmp['q6'] = $user['q6'];
$tmp['q7'] = $user['q7'];
$tmp['q8a'] = $user['q8a'];
$tmp['q8b'] = $user['q8b'];
$tmp['q8c'] = $user['q8c'];
$tmp['q8d'] = $user['q8d'];
$tmp['q8e'] = $user['q8e'];
$tmp['q8f'] = $user['q8f'];
$tmp['q8g'] = $user['q8g'];
$tmp['q8h'] = $user['q8h'];
$tmp['q9a'] = $user['q9a'];
$tmp['q9b'] = $user['q9b'];
$tmp['q9c'] = $user['q9c'];
$tmp['q9d'] = $user['q9d'];
$tmp['q9e'] = $user['q9e'];
$tmp['q9f'] = $user['q9f'];
$tmp['q10a'] = $user['q10a'];
$tmp['q10b'] = $user['q10b'];
$tmp['q10c'] = $user['q10c'];
$tmp['q10d'] = $user['q10d'];
$tmp['q10e'] = $user['q10e'];
$tmp['q10f'] = $user['q10f'];
$tmp['q11a'] = $user['q11a'];
$tmp['q11b'] = $user['q11b'];
$tmp['q11c'] = $user['q11c'];
$tmp['q11d'] = $user['q11d'];
$tmp['q11e'] = $user['q11e'];
$tmp['q11f'] = $user['q11f'];
$tmp['q11g'] = $user['q11g'];
$tmp['q11h'] = $user['q11h'];
$tmp['q12a'] = $user['q12a'];
$tmp['q12b'] = $user['q12b'];
$tmp['q12c'] = $user['q12c'];
$tmp['q12d'] = $user['q12d'];
$tmp['q12e'] = $user['q12e'];
$tmp['q12f'] = $user['q12f'];
$tmp['q12g'] = $user['q12g'];
$tmp['q12h'] = $user['q12h'];
$tmp['q13a'] = $user['q13a'];
$tmp['q13b'] = $user['q13b'];
$tmp['q13c'] = $user['q13c'];
$tmp['q13d'] = $user['q13d'];
$tmp['q14a'] = $user['q14a'];
$tmp['q14b'] = $user['q14b'];
$tmp['q14c'] = $user['q14c'];
$tmp['q14d'] = $user['q14d'];
$tmp['q14e'] = $user['q14e'];
$tmp['q14f'] = $user['q14f'];
$tmp['q15'] = $user['q15'];
$tmp['q16'] = $user['q16'];
$tmp['q17a'] = $user['q17a'];
$tmp['q17b'] = $user['q17b'];
$tmp['q17c'] = $user['q17c'];
$tmp['q17d'] = $user['q17d'];
$tmp['q17e'] = $user['q17e'];
$tmp['q18'] = $user['q18'];
$tmp['q19'] = $user['q19'];
$tmp['q20'] = $user['q20'];
$tmp['q21'] = $user['q21'];
$tmp['q22'] = $user['q22'];
$tmp['q23'] = $user['q23'];
$tmp['q24'] = $user['q24'];
$tmp['q25'] = $user['q25'];
$tmp['q26'] = $user['q26'];
$tmp['q27'] = $user['q27'];
$tmp['q28a'] = $user['q28a'];
$tmp['q28b'] = $user['q28b'];
$tmp['q28c'] = $user['q28c'];
$tmp['q28d'] = $user['q28d'];
$tmp['q28e'] = $user['q28e'];
$tmp['q28f'] = $user['q28f'];
$tmp['q29'] = $user['q29'];
$tmp['q30'] = $user['q30'];
$tmp['q31'] = $user['q31'];
$tmp['q32'] = $user['q32'];
$tmp['q33'] = $user['q33'];
$tmp['q34'] = $user['q34'];
$tmp['q35'] = $user['q35'];
$tmp['q36'] = $user['q36'];
$tmp['q37'] = $user['q37'];
$tmp['q38a'] = $user['q38a'];
$tmp['q38b'] = $user['q38b'];
$tmp['q38c'] = $user['q38c'];
$tmp['q38d'] = $user['q38d'];
$tmp['q38e'] = $user['q38e'];
}
}
}
$jsonarr['Results']['data'][] = $tmp;
//$jsonarr['Results']['SQL'] = stripslashes(preg_replace(array('/\n/', '/\t/'), array(' ', ' '), $usersql));
}
if ($view != 'csv') {
return json_encode($jsonarr);
} else {
$fields = array_keys($jsonarr['Results']['data'][0]);
echo join(',', $fields) . "\n";
foreach ($jsonarr['Results']['data'] as $row) {
$str = '';
foreach ($fields as $field) {
$str .= '"' . str_replace('"', '\\"', $row[$field]) . '",';
}
$str = substr($str, 0, -1);
echo $str . "\n";
}
exit;
}
}
function load_all($view = 'full', $params = false)
{
$where = array();
if (isset($_REQUEST['membersOnly'])) {
//$where[] = 'U.isMember = 1';
}
if (isset($_REQUEST['teamEligibleOnly'])) {
//$where[] = "U.optInStudy = 1 AND U.eligibility = 'team'";
}
if (isset($_REQUEST['startDate'])) {
$where[] = "Content.date > '" . $_REQUEST['startDate'] . " 00:00:00'";
$startLabel = date('l jS \\of F Y', strtotime($params['startDate']));
} else {
//$where[] = "Content.date > '".date("Y-m-d 00:00:00", time())."'";
$where[] = "Content.date > '" . date("2000-01-01 00:00:00", time()) . "'";
$startLabel = date('l jS \\of F Y', time());
}
if (isset($_REQUEST['endDate'])) {
$where[] = "Content.date < '" . $_REQUEST['endDate'] . " 23:59:59'";
$endLabel = date('l jS \\of F Y', strtotime($params['endDate']));
} else {
$endDate = false;
$endLabel = 'Now';
}
if (isset($_REQUEST['siteid']) && $_REQUEST['siteid'] != 0) {
$currSiteId = $_REQUEST['siteid'];
} else {
$currSiteId = false;
}
if (count($where)) {
$wherestr = ' WHERE ' . join(' AND ', $where);
} else {
$wherestr = '';
}
$usersql = "\n\t\t\tSELECT siteContentId, title, url, numComments, score,\n\t\t\t\t(SELECT COUNT(1) FROM Log WHERE Log.itemid = Content.siteContentId AND action = 'readStory') AS numReadStory,\n\t\t\t\t(SELECT COUNT(1) FROM Log WHERE Log.itemid = Content.siteContentId AND action = 'shareStory') AS numSharedStory,\n\t\t\t\t(SELECT COUNT(1) FROM Log WHERE Log.itemid = Content.siteContentId AND action = 'vote') AS numVotes\n\t\t\tFROM Content {$wherestr}\n\t\t\tORDER BY siteContentId DESC\n\t\t";
//FROM Content $wherestr
$sites = array();
$sites_res = $this->model->db->query("SELECT * FROM Sites");
while (($row = mysql_fetch_assoc($sites_res)) !== false) {
$sites[$row['shortname']] = array('dbname' => $row['dbname'], 'siteid' => $row['id'], 'siteurl' => $row['url'], 'sitename' => $row['name']);
}
$jsonarr = array('Results' => array('replyCode' => '201', 'replyText' => 'Data Follows', 'data' => array()));
foreach ($sites as $name => $dbinfo) {
$dbname = $dbinfo['dbname'];
$siteid = $dbinfo['siteid'];
$sitename = $dbinfo['sitename'];
$siteurl = $dbinfo['siteurl'];
$this->model->db->selectdb($dbname);
if ($currSiteId && $currSiteId != $siteid) {
continue;
}
$results = $this->model->db->query($usersql);
$stories = array();
while (($row = mysql_fetch_assoc($results)) !== false) {
$stories[] = $row;
}
foreach ($stories as $story) {
$this->model->db->selectdb('research');
$ext_read_sql = "SELECT count(1) as fullReadCount FROM RawExtLinks WHERE siteid = {$siteid} AND action = 'read' AND itemid = {$story['siteContentId']}";
$read_results = $this->model->db->query($ext_read_sql);
$read_array = mysql_fetch_assoc($read_results);
$story['numFullReadStory'] = $read_array['fullReadCount'];
$title = sprintf("<a target=\"_cts\" href=\"%s?p=read&cid=%s\">%s</a>", $siteurl, $story['siteContentId'], $story['title']);
if ($view == 'pod') {
$tmp = array('title' => util_link_for($story['title'], 'stories', 'view_story', $story['siteContentId']), 'numComments' => $story['numComments'], 'score' => $story['score']);
} else {
if ($view == 'full') {
$tmp = array('siteContentId' => util_link_for($story['siteContentId'], 'stories', 'view_story', $story['siteContentId']), 'sitename' => $sitename, 'title' => util_link_for($story['title'], 'stories', 'view_story', $story['siteContentId']), 'numComments' => $story['numComments'], 'score' => $story['score'], 'numReadStory' => $story['numReadStory'], 'numFullReadStory' => $story['numFullReadStory'], 'numSharedStory' => $story['numSharedStory'], 'numVotes' => $story['numVotes'], 'storyUrl' => "<a target=\"_cts\" href=\"{$story['url']}\">{$story['url']}</a>");
} else {
if ($view == 'csv') {
$tmp = array('siteContentId' => $story['siteContentId'], 'sitename' => $sitename, 'title' => $story['title'], 'numComments' => $story['numComments'], 'score' => $story['score'], 'numReadStory' => $story['numReadStory'], 'numFullReadStory' => $story['numFullReadStory'], 'numSharedStory' => $story['numSharedStory'], 'numVotes' => $story['numVotes'], 'storyUrl' => $story['url']);
}
}
}
$jsonarr['Results']['data'][] = $tmp;
}
}
$jsonarr['Results']['SQL'] = stripslashes(preg_replace(array('/\\n/', '/\\t/'), array(' ', ' '), $usersql));
if ($view != 'csv') {
return json_encode($jsonarr);
} else {
$fields = array_keys($jsonarr['Results']['data'][0]);
echo join(',', $fields) . "\n";
foreach ($jsonarr['Results']['data'] as $row) {
$str = '';
foreach ($fields as $field) {
$str .= '"' . str_replace('"', '\\"', $row[$field]) . '",';
}
$str = substr($str, 0, -1);
echo $str . "\n";
}
exit;
}
}