(SELECT ROUND(EXTRACT(\'epoch\' FROM SUM("end" - "start"))) FROM status_history t ' . $group_query_join_where . ' t.status=true AND t."end" IS NOT NULL) "user_status_count_time",
(SELECT COUNT(1) FROM profilepicture_history t ' . $group_query_join . ') "profilepicture_count",
(SELECT COUNT(1) FROM statusmessage_history t ' . $group_query_join . ') "statusmessage_count",
(SELECT COUNT(1) FROM lastseen_privacy_history t ' . $group_query_join . ') "lastseen_privacy_count",
(SELECT COUNT(1) FROM profilepic_privacy_history t ' . $group_query_join . ') "profilepic_privacy_count",
(SELECT COUNT(1) FROM statusmessage_privacy_history t ' . $group_query_join . ') "statusmessage_privacy_count",
(SELECT COUNT(1) FROM accounts a ' . $group_query_acc_join_where . ' a.active = true AND a.verified = true AND a.lastseen_privacy = true) "account_lastseen_privacy_enabled",
(SELECT COUNT(1) FROM accounts a ' . $group_query_acc_join_where . ' a.active = true AND a.verified = true AND a.lastseen_privacy = false) "account_lastseen_privacy_disabled",
(SELECT COUNT(1) FROM accounts a ' . $group_query_acc_join_where . ' a.active = true AND a.verified = true AND a.statusmessage_privacy = true) "account_statusmessage_privacy_enabled",
(SELECT COUNT(1) FROM accounts a ' . $group_query_acc_join_where . ' a.active = true AND a.verified = true AND a.statusmessage_privacy = false) "account_statusmessage_privacy_disabled",
(SELECT COUNT(1) FROM accounts a ' . $group_query_acc_join_where . ' a.active = true AND a.verified = true AND a.profilepic_privacy = true) "account_profilepic_privacy_enabled",
(SELECT COUNT(1) FROM accounts a ' . $group_query_acc_join_where . ' a.active = true AND a.verified = true AND a.profilepic_privacy = false) "account_profilepic_privacy_disabled";');
$select_global->execute();
$result_global = $select_global->fetch(PDO::FETCH_ASSOC);
// Fix timezone
$result_global['first_tracker_session'] = fixTimezone($result_global['first_tracker_session']);
// Add group name
$result_global['shared_group_name'] = @$token_group['name'];
echo json_encode($result_global);
break;
case 'top_usage_users':
// Top 10 setup
$result_topusers = array();
// Add metadata: the amount of users (in this group)
$select_meta = $DBH->prepare('SELECT COUNT(1) FROM accounts a ' . $group_query_acc_join);
$select_meta->execute();
$result_topusers['meta'] = $select_meta->fetch(PDO::FETCH_ASSOC);
$top_limit = is_numeric($_GET['users']) ? round($_GET['users']) : 10;
$select = $DBH->prepare('SELECT ' . $select_top . ' a.name, ROUND(EXTRACT(\'epoch\' FROM SUM(sh."end" - sh."start"))) "online", COUNT(sh.status) "count"
FROM accounts a
LEFT JOIN status_history sh ON sh.number = a.id
array_push($result_status, $status);
}
$select = $DBH->prepare('SELECT hash, changed_at FROM profilepicture_history WHERE number = :number ORDER BY changed_at DESC');
$select->execute(array(':number' => $number));
$result_picture = array();
// Quick fix, need better solution
foreach ($select->fetchAll(PDO::FETCH_ASSOC) as $status) {
$status['changed_at'] = fixTimezone($status['changed_at']);
array_push($result_picture, $status);
}
$select = $DBH->prepare('SELECT status, changed_at FROM statusmessage_history WHERE number = :number ORDER BY changed_at DESC');
$select->execute(array(':number' => $number));
$result_statusmsg = array();
// Quick fix, need better solution
foreach ($select->fetchAll(PDO::FETCH_ASSOC) as $status) {
$status['changed_at'] = fixTimezone($status['changed_at']);
array_push($result_statusmsg, $status);
}
if (count($result_status) != 0) {
array_push($accounts, array('id' => $number, 'status' => $result_status, 'statusmessages' => $result_statusmsg, 'pictures' => $result_picture));
}
}
echo json_encode($accounts);
} else {
echo json_encode(['error' => 'No number supplied!', 'code' => 400]);
}
break;
case 'getAbout':
echo file_get_contents($whatsspyAboutQAUrl);
break;
default:
