function processCallback()
{
$this->consumer = new Zend_OAuth_Consumer($this->configuration);
if (!empty($_GET) && isset($_SESSION['EPICOLLECT_TOKEN'])) {
$token = $this->consumer->getAccessToken($_GET, unserialize($_SESSION['EPICOLLECT_TOKEN']));
$_SESSION['EPICOLLECT_ACCESS_TOKEN'] = serialize($token);
$_SESSION['EPICOLLECT_TOKEN'] = null;
$db = new dbConnection();
$res = $db->do_query("CALL setOAuthLoginDetails('{$this->provider}', '{$token->user_id}', '{$token->screen_name}', '{$_SESSION['EPICOLLECT_TOKEN']}', '{$_SESSION['EPICOLLECT_ACCESS_TOKEN']}', '" . session_id() . "')");
if ($res === true) {
if ($arr = $db->get_row_array()) {
$_SESSION["EcUserId"] = $arr["EcUserId"];
$_SESSION["newUser"] = $arr["newUser"];
}
} else {
throw new OAuthException(500, $res);
}
} else {
exit('Invalid callback request. Oops. Sorry.');
}
}
public function addToDb()
{
global $db;
if (!$db) {
$db = new dbConnection();
}
$qry = "SELECT idFieldType FROM FieldType where name = '{$this->type}'";
$db->do_query($qry);
while ($arr = $db->get_row_array()) {
$fieldType = $arr["idFieldType"];
}
$lbl = mysql_escape_string($this->label);
$qry = "INSERT INTO Field (form, projectName, formName, type, name, label, language, regex, title, `key`, isinteger, isdouble, active, doubleentry, jump, required, search, group_form, branch_form, display, genkey, date, time, setdate, settime, position) VALUES\n\t\t\t\t\t\t\t\t ({$this->form->id}, '{$this->form->survey->name}', '{$this->form->name}', {$fieldType}, '{$this->name}','{$lbl}', '{$this->language}',";
$qry .= $this->regex != "" ? "'{$this->regex}'," : "NULL,";
$qry .= $this->title ? "1," : "0,";
$qry .= $this->key ? "1," : "0,";
$qry .= $this->isInt ? "1," : "0,";
$qry .= $this->isDouble ? "1," : "0,";
$qry .= "1,";
$qry .= $this->doubleEntry ? "1," : "0,";
$qry .= $this->jump ? "'{$this->jump}'," : "NULL,";
$qry .= $this->required ? "1," : "0,";
$qry .= $this->search ? "1," : "0,";
$qry .= $this->group_form ? "'{$this->group_form}'," : "NULL,";
$qry .= $this->branch_form ? "'{$this->branch_form}'," : "NULL,";
$qry .= $this->display ? "1," : "0,";
$qry .= $this->genkey ? "1," : "0,";
$qry .= $this->date ? "'{$this->date}'," : "NULL,";
$qry .= $this->time ? "'{$this->time}'," : "NULL,";
$qry .= $this->setDate ? "'{$this->setDate}'," : "NULL,";
$qry .= $this->setTime ? "'{$this->setTime}'," : "NULL,";
$qry .= "{$this->position})";
$res = $db->do_query($qry);
if ($res === true) {
foreach ($this->options as $opt) {
$res = $db->exec_sp("addOption", array($this->form->survey->name, $this->form->name, $this->name, $opt->idx, $opt->label, $opt->value));
if ($res !== true) {
return $res;
}
}
}
return $res;
}
public function fetch()
{
$db = new dbConnection();
//global $db;
$this->titleFields = array();
$qry = "SELECT * from form WHERE";
if (is_numeric($this->id)) {
$qry = "{$qry} idForm = {$this->id}";
} else {
$qry = "{$qry} projectName = '{$this->survey->name}' AND name = '{$this->name}'";
}
$res = $db->do_query($qry);
if ($res === true) {
while ($arr = $db->get_row_array()) {
$this->id = $arr["idForm"];
$this->key = $arr["keyField"];
$this->name = $arr["name"];
$this->number = $arr["table_num"];
$this->version = $arr["version"];
$this->group = $arr["group"];
$this->isMain = $arr["isMain"] == "1";
}
}
$qry = "SELECT f.idField as idField, f.key, f.name, f.label, ft.name as type, f.required, f.jump, f.isinteger as isInt, f.isDouble, f.title, f.regex, f.doubleEntry, f.search, f.group_form, f.branch_form, f.display, f.genkey, f.date, f.time, f.setDate, f.setTime, f.min, f.max, f.crumb, f.`match`, f.active, f.defaultValue, f.otherFieldProperties, f.upperCase, f.position FROM field f LEFT JOIN fieldtype ft on ft.idFieldType = f.type WHERE ";
if (is_numeric($this->id)) {
$qry = "{$qry} f.form = {$this->id} ORDER BY f.position";
} else {
$qry = "{$qry} f.projectName = '{$this->survey->name}' AND f.formname = '{$this->name}' ORDER BY f.position";
}
$res = $db->do_query($qry);
if ($res === true) {
while ($arr = $db->get_row_array()) {
if (!array_key_exists($arr["name"], $this->fields)) {
$fld = new EcField();
} else {
$fld = $this->fields[$arr["name"]];
}
$fld->form = $this;
$fld->fromArray($arr);
$fld->name = preg_replace('/[^0-9a-z]/i', '_', $fld->name);
$fld->otherAttributes = json_decode($arr['otherFieldProperties']);
$this->fields[$fld->name] = $fld;
if ($fld->key) {
$this->key = $fld->name;
}
if ($fld->title && $fld->active) {
array_push($this->titleFields, $fld->name);
}
}
foreach ($this->fields as $fld) {
//$db = new dbConnection();
if (!$fld->idField) {
continue;
}
$res = $db->do_query("SELECT `index`, `label`, `value` FROM `option` WHERE `field` = {$fld->idField}");
//$db2->exec_sp("getOptions", array($fld->idField));
if ($res !== true) {
die($res);
}
while ($arr = $db->get_row_array()) {
$opt = new EcOption();
$opt->fromArray($arr);
$opt->idx = $arr["index"];
array_push($fld->options, $opt);
}
if ($fld->type == "branch") {
array_push($this->branches, $fld->branch_form);
array_push($this->branchfields, $fld->name);
}
}
return true;
} else {
return $res;
}
}
<th scope="col">Item Tested</th>
<th scope="col">Result</th>
</tr>
<?php
//SQL battery
echo "<tr><th colspan=\"2\">SQL Tests</th></tr>\n\t<tr><td>Connect to db</td><td>";
try {
$db = new dbConnection();
echo "<span class=\"success\">OK</span>";
} catch (Exception $e) {
echo "<span class=\"failure\">{$e->message}</span>";
}
echo "</td></tr>";
$spPass = true;
$res = $db->do_query("CALL getOAuthProvider('twitter');");
while ($arr = $db->get_row_array()) {
}
if ($res === true) {
} else {
$spPass = false;
echo "<tr><td>Get OAuth Provider (twitter)</td><td>";
echo "<span class=\"failure\">{$res}</span>";
}
$db = new dbConnection();
$res = $db->do_query("CALL setOAuthLoginDetails('twitter', '12345', 'test', 'testToken', 'testToken', 'abcdefghij');");
if ($res === true) {
} else {
$spPass = false;
echo "<tr><td>Set OAuth Login details</td><td>";
echo "<span class=\"failure\">{$res}</span>";
}
function getUsage($res = "month", $from = NULL, $to = NULL)
{
if (!$from || !is_object($from) || !get_class($from) == "DateTime") {
$from = new DateTime('now', new DateTimeZone('UTC'));
$from->sub(new DateInterval("P12M"));
}
if (!$to || !is_object($to) || !get_class($to) == "DateTime") {
$to = new DateTime();
//$to->add(new DateInterval("P6M"));
}
$formats = array("hour" => array("%H %d/%m/%Y", "PT1H", "H d/m/Y"), "day" => array("%d/%m/%Y", "P1D", "d/m/Y"), "week" => array("%u %Y", "P1W", "W Y"), "month" => array("%m/%Y", "P1M", "m/Y"), "year" => array("%Y", "P1Y", "Y"));
$sql = " LEFT JOIN (SELECT count(distinct user) as userTotal, count(1) as entryTotal, DATE_FORMAT(FROM_UNIXTIME(created / 1000), '{$formats[$res][0]}') as Date From entry WHERE projectName = '{$this->name}' GROUP BY Date) b ON a.date = b.Date";
$periods = array();
for ($dat = $from; $dat <= $to; $dat = $dat->add(new DateInterval($formats[$res][1]))) {
array_push($periods, $dat->format($formats[$res][2]));
}
$sql = "SELECT a.date as `date`, IFNULL(b.userTotal, 0) as users, IFNULL(b.entryTotal, 0) as entries FROM (SELECT '" . implode("' as date UNION SELECT '", $periods) . "') a {$sql}";
$db = new dbConnection();
$res = $db->do_query($sql);
if ($res === true) {
$resArr = array();
while ($arr = $db->get_row_array()) {
$arr["users"] = (int) $arr["users"];
$arr["entries"] = (int) $arr["entries"];
array_push($resArr, $arr);
}
return json_encode($resArr);
} else {
return $res;
}
}
function siteTest()
{
$res = array();
global $cfg, $db;
$template = 'testResults.html';
$doit = true;
if (!array_key_exists("database", $cfg->settings) || !array_key_exists("server", $cfg->settings["database"]) || trim($cfg->settings["database"]["server"]) == "") {
$res["dbStatus"] = "fail";
$res["dbResult"] = "No database server specified, please amend the file ec/settings.php and so that \$DBSERVER equals the name of the MySQL server";
$doit = false;
} else {
if (!array_key_exists("user", $cfg->settings["database"]) || trim($cfg->settings["database"]["user"]) == "") {
$res["dbStatus"] = "fail";
$res["dbResult"] = "No database user specified, please amend the file ec/settings.php so that \$DBUSER and \$DBPASS equal the credentials for MySQL server";
$doit = false;
} else {
if (!array_key_exists("database", $cfg->settings["database"]) || trim($cfg->settings["database"]["database"]) == "") {
$res["dbStatus"] = "fail";
$res["dbResult"] = "No database name specified, please amend the file ec/settings.php so that \$DBNAME equals the name of the MySQL database";
$doit = false;
}
}
}
if ($doit && !(array_key_exists("edit", $_GET) && $_GET["edit"] === "true")) {
if (array_key_exists("redir", $_GET) && $_GET["redir"] === "true") {
$res["redirMsg"] = "\t<p class=\"message\">You have been brought to this page because of a fatal error opening the home page</p>";
}
if (array_key_exists("redir", $_GET) && $_GET["redir"] === "pwd") {
$res["redirMsg"] = "\t<p class=\"message\">The username and password you entered were incorrect, please try again.</p>";
}
if (!$db) {
$db = new dbConnection();
}
if ($db->connected) {
$res["dbStatus"] = "succeed";
$res["dbResult"] = "Connected";
} else {
$ex = $db->errorCode;
if ($ex == 1045) {
$res["dbStatus"] = "fail";
$res["dbResult"] = "DB Server found, but the combination of the username and password invalid. <a href=\"./test?edit=true\">Edit Settings</a>";
} elseif ($ex == 1044) {
$res["dbStatus"] = "fail";
$res["dbResult"] = "DB Server found, but the database specified does not exist or the user specified does not have access to the database. <a href=\"./test?edit=true\">Edit Settings</a>";
} else {
$res["dbStatus"] = "fail";
$res["dbResult"] = "Could not find the DB Server ";
}
}
if ($db->connected) {
$dbNameRes = $db->do_query("SHOW DATABASES");
if ($dbNameRes !== true) {
echo $dbNameRes;
return;
}
while ($arr = $db->get_row_array()) {
if ($arr['Database'] == $cfg->settings["database"]["database"]) {
$res["dbStatus"] = "succeed";
$res["dbResult"] = "";
break;
} else {
$res["dbStatus"] = "fail";
$res["dbResult"] = "DB Server found, but the database '{$cfg->settings["database"]["database"]}' does not exist.<br />";
}
}
$res["dbPermStatus"] = "fail";
$res["dbPermResults"] = "";
$res["dbTableStatus"] = "fail";
if ($res["dbStatus"] == "succeed") {
$dbres = $db->do_query("SHOW GRANTS FOR {$cfg->settings["database"]["user"]};");
if ($dbres !== true) {
$res["dbPermResults"] = $res;
} else {
$perms = array("SELECT", "INSERT", "UPDATE", "DELETE", "EXECUTE");
$res["dbPermResults"] = "Permssions not set, the user {$cfg->settings["database"]["user"]} requires SELECT, UPDATE, INSERT, DELETE and EXECUTE permissions on the database {$cfg->settings["database"]["database"]}";
while ($arr = $db->get_row_array()) {
$_g = implode(" -- ", $arr) . "<br />";
if (preg_match("/ON (`?{$cfg->settings["database"]["database"]}`?|\\*\\.\\*)/", $_g)) {
if (preg_match("/ALL PERMISSIONS/i", $_g)) {
$res["dbPermStatus"] = "fail";
$res["dbPermResults"] = "The user account {$cfg->settings["database"]["user"]} by the website should only have SELECT, INSERT, UPDATE, DELETE and EXECUTE priviliges on {$cfg->settings["database"]["database"]}";
break;
}
for ($_p = 0; $_p < count($perms); $_p++) {
if (preg_match("/{$perms[$_p]}/i", $_g)) {
unset($perms[$_p]);
$perms = array_values($perms);
$_p--;
}
}
}
}
if (count($perms) == 0) {
$res["dbPermStatus"] = "succeed";
$res["dbPermResults"] = "Permssions Correct";
} else {
$res["dbPermResults"] = "Permssions not set, the user {$cfg->settings["database"]["user"]} is missing " . implode(", ", $perms) . " permissions on the database {$cfg->settings["database"]["database"]}";
}
}
}
}
if ($db->connected && $res["dbPermStatus"] == "succeed") {
$tblTemplate = array("device" => false, "deviceuser" => false, "enterprise" => false, "entry" => false, "entryvalue" => false, "entryvaluehistory" => false, "field" => false, "fieldtype" => false, "form" => false, "option" => false, "project" => false, "role" => false, "user" => false, "userprojectpermission" => false);
$dres = $db->do_query("SHOW TABLES");
if ($dres !== true) {
$res["dbTableStatus"] = "fail";
$res["dbTableResult"] = "EpiCollect Database is not set up correctly";
} else {
$i = 0;
while ($arr = $db->get_row_array()) {
$tblTemplate[$arr["Tables_in_{$cfg->settings["database"]["database"]}"]] = true;
$i++;
}
if ($i == 0) {
$template = 'dbSetup.html';
$res["dbTableStatus"] = "fail";
$res["dbTableResult"] = "<p>Database is blank, enter an <b>administrator</b> username and password for the database to create the database tables.</p>\n\t\t\t\t<form method=\"post\" action=\"createDB\">\n\t\t\t\t\t<b>Username : </b><input name=\"un\" type=\"text\" /> <b>Password : </b><input name=\"pwd\" type=\"password\" /> <input type=\"hidden\" name=\"create\" value=\"true\" /><input type=\"submit\" value=\"Create Database\" name=\"Submit\" />\n\t\t\t\t</form>";
} else {
$done = true;
foreach ($tblTemplate as $key => $val) {
$done &= $val;
}
if ($done) {
$res["dbTableStatus"] = "succeed";
$res["dbTableResult"] = "EpiCollect Database ready";
} else {
$res["dbTableStatus"] = "fail";
$res["dbTableResult"] = "EpiCollect Database is not set up correctly";
}
}
}
}
$res["endStatus"] = array_key_exists("dbTableStatus", $res) ? $res["dbTableStatus"] == "fail" ? "fail" : "" : "fail";
$res["endMsg"] = $res["endStatus"] == "fail" ? "The MySQL database is not ready, please correct the errors in red above and refresh this page. <a href = \"./test?edit=true\">Configuration tool</a>" : "You are now ready to create EpiCollect projects, place xml project definitions in {$_SERVER["PHP_SELF"]}/xml and visit the <a href=\"createProject.html\">create project</a> page";
echo applyTemplate("base.html", $template, $res);
} else {
$arr = "{";
foreach ($cfg->settings as $k => $v) {
foreach ($v as $sk => $sv) {
$arr .= "\"{$k}\\\\{$sk}\" : \"{$sv}\",";
}
}
$arr = trim($arr, ",") . "}";
echo applyTemplate("base.html", "setup.html", array("vals" => $arr));
}
}
private function populateSesssionInfo()
{
$db = new dbConnection();
$qry = "SELECT idUsers as userId, FirstName, LastName, Email, language FROM user WHERE openId = '{$_SESSION['openid']}'";
$err = $db->do_query($qry);
if ($err === true) {
if ($arr = $db->get_row_array()) {
foreach (array_keys($arr) as $key) {
$_SESSION[$key] = $arr[$key];
}
}
}
}
public function addToDb()
{
global $db;
if (!$db) {
$db = new dbConnection();
}
$qry = "SELECT idFieldType FROM fieldtype where name = '{$this->type}'";
$db->do_query($qry);
while ($arr = $db->get_row_array()) {
$fieldType = $arr["idFieldType"];
}
$lbl = $db->escapeArg($this->label);
$qry = "INSERT INTO field (form, projectName, formName, type, name, label, language, regex, title, `key`, isinteger, isdouble, active, doubleentry, jump, required, search, group_form, branch_form, display, genkey, upperCase, date, time, setdate, settime, `min`, `max`, `match`, crumb, defaultValue, position, otherFieldProperties) VALUES\n\t\t\t\t\t\t\t\t ({$this->form->id}, '{$this->form->survey->name}', '{$this->form->name}', {$fieldType}, '{$this->name}','{$lbl}', '{$this->language}',";
$qry .= $this->regex != "" ? $db->stringVal($this->regex) . "," : "NULL,";
$qry .= $this->title ? "1," : "0,";
$qry .= $this->key ? "1," : "0,";
$qry .= $this->isInt ? "1," : "0,";
$qry .= $this->isDouble ? "1," : "0,";
$qry .= "1,";
$qry .= $this->doubleEntry ? "1," : "0,";
$qry .= $this->jump ? "'{$this->jump}'," : "NULL,";
$qry .= $this->required ? "1," : "0,";
$qry .= $this->search ? "1," : "0,";
$qry .= $this->group_form ? "'{$this->group_form}'," : "NULL,";
$qry .= $this->branch_form ? "'{$this->branch_form}'," : "NULL,";
$qry .= $this->display ? "1," : "0,";
$qry .= $this->genkey ? "1," : "0,";
$qry .= $this->upperCase ? "1," : "0,";
$qry .= $this->date ? "'{$this->date}'," : "NULL,";
$qry .= $this->time ? "'{$this->time}'," : "NULL,";
$qry .= $this->setDate ? "'{$this->setDate}'," : "NULL,";
$qry .= $this->setTime ? "'{$this->setTime}'," : "NULL,";
$qry .= $this->min || $this->min === '0' ? "{$this->min}," : "NULL,";
$qry .= $this->max || $this->max === '0' ? "{$this->max}," : "NULL,";
$qry .= $this->match ? $db->stringVal($this->match) . ',' : "NULL,";
$qry .= $this->crumb ? "'{$this->crumb}'," : "NULL,";
$qry .= $this->defaultValue || $this->defaultValue === '0' ? $db->stringVal($this->defaultValue) . "," : "NULL,";
$qry .= "{$this->position},";
$qry .= $db->stringVal(json_encode($this->otherAttributes)) . ")";
$res = $db->do_query($qry);
if ($res === true) {
$this->idField = $db->last_id();
$optcount = count($this->options);
if ($optcount > 0) {
$optqry = 'INSERT INTO `option` (`index`, `label`, `value`, `field`) VALUES';
//print_r($this->options);
for ($x = 0; $x < $optcount; ++$x) {
$lab = $db->stringVal($this->options[$x]->label);
$val = $db->stringVal($this->options[$x]->value);
if ($lab == 'NULL') {
throw new Exception(sprintf('The label for option %d of field %s cannot be null.', $x, $this->name));
}
if ($val == 'NULL') {
throw new Exception(sprintf('The value of option %d of field %s cannot be null.', $x, $this->name));
}
$optqry = sprintf('%s%s (%s, %s, %s, %s)', $optqry, $x > 0 ? ',' : '', intval($this->options[$x]->idx), $lab, $val, intval($this->idField));
//$res = $db->exec_sp("addOption", array(
// $this->form->survey->name,
// $this->form->name,
// $this->name,
// $opt->idx,
// $opt->label,
// $opt->value
//));
//if($res !== true) return $res;
}
$res = $db->do_query($optqry);
if ($res !== true) {
return $res;
}
}
}
//echo "$qry\n";
return $res;
}
