public function clearNotificationMatches($username, $personal, $team)
{
$db = $this->connect();
// Clean input.
$personal = Database_SQLiteInjection::no($personal);
$team = Database_SQLiteInjection::no($team);
// Select all identifiers of the personal change proposals.
$query = "SELECT identifier FROM notifications WHERE username = '******' AND category = '{$personal}';";
$personals = array();
$result = Database_SQLite::query($db, $query);
foreach ($result as $row) {
$personals[] = $row[0];
}
// Matches to be deleted.
$deletes = array();
// Go through each of the personal change proposals.
foreach ($personals as $personalID) {
$bible = $this->getNotificationBible($personalID);
$passage = $this->getNotificationPassage($personalID);
$book = $passage['book'];
$chapter = $passage['chapter'];
$verse = $passage['verse'];
$modification = $this->getNotificationModification($personalID);
$modification = Database_SQLiteInjection::no($modification);
// Get all matching identifiers from the team's change notifications.
$query = "SELECT identifier FROM notifications WHERE username = '******' AND category = '{$team}' AND bible = '{$bible}' AND book = {$book} AND chapter = {$chapter} AND verse = {$verse} AND modification = '{$modification}';";
$teamMatches = array();
$result = Database_SQLite::query($db, $query);
foreach ($result as $row) {
$teamMatches[] = $row[0];
}
// There should be exactly one candidate for the matches to be removed.
// If there are none, it means that the personal change didn't make it to the team's text.
// If there are two or more matching changes, then one could have undone the other, so should not be automatically removed.
if (count($teamMatches) == 1) {
foreach ($teamMatches as $teamMatches) {
// Check there are only two change notifications for this user / Bible / book / chapter / verse.
// If there are more, we can't be sure that the personal change proposal was not overwritten somehow.
$passageMatches = array();
$query = "SELECT identifier FROM notifications WHERE username = '******' AND bible = '{$bible}' AND book = {$book} AND chapter = {$chapter} AND verse = {$verse};";
$result = Database_SQLite::query($db, $query);
foreach ($result as $row) {
$passageMatches[] = $row[0];
}
if (count($passageMatches) == 2) {
// Store the personal change proposal to be deleted.
// Store the matching change notification to be deleted also.
foreach ($passageMatches as $passageMatche) {
$deletes[] = $passageMatche;
}
}
}
}
}
// Delete all stored identifiers to be deleted.
foreach ($deletes as $delete) {
$this->deleteNotification($delete, $db);
}
unset($db);
}
public function setValue($id, $key, $value)
{
$id = Database_SQLiteInjection::no($id);
$key = Database_SQLiteInjection::no($key);
$value = Database_SQLiteInjection::no($value);
$query = "DELETE FROM volatile WHERE id = {$id} AND key = '{$key}';";
Database_SQLite::exec($this->db, $query);
$query = "INSERT INTO volatile VALUES ({$id}, '{$key}', '{$value}');";
Database_SQLite::exec($this->db, $query);
}
public static function notesOptionalFulltextSearchStatement($search)
{
if ($search == "") {
return;
}
$search = str_replace(",", "", $search);
$search = Database_SQLiteInjection::no($search);
$query = " AND cleantext LIKE '%{$search}%' ";
return $query;
}
public function get($bible)
{
$bible = Database_SQLiteInjection::no($bible);
$commits = array();
$query = "SELECT sha1 FROM commits WHERE bible = '{$bible}';";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
$commits[] = $row[0];
}
return $commits;
}
public function flushCache()
{
$text = trim($this->currentText);
if ($text != "") {
$text = Database_SQLiteInjection::no($text);
$book = (int) $this->currentBook;
$chapter = (int) $this->currentChapter;
$verse = (int) $this->currentVerse;
$statement = "INSERT INTO Bible VALUES ({$book}, {$chapter}, {$verse}, '{$text}');";
$this->sql[] = $statement;
}
$this->currentText = "";
}
public function postpone($id)
{
$id = Database_SQLiteInjection::no($id);
$query = "UPDATE mail SET retry = retry + 1 WHERE rowid = {$id};";
Database_SQLite::exec($this->db, $query);
$query = "UPDATE mail SET timestamp = timestamp + retry * 900 WHERE rowid = {$id};";
Database_SQLite::exec($this->db, $query);
}
public function hasReadOnlyAccess2Bible($user, $bible)
{
$user = Database_SQLiteInjection::no($user);
$bible = Database_SQLiteInjection::no($bible);
$query = "SELECT readonly FROM teams WHERE username = '******' AND bible = '{$bible}';";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
return (bool) $row[0];
}
// Entry not found for user/bible: Default is not read-only.
return false;
}
public function searchStrong($strong)
{
$strong = Database_SQLiteInjection::no($strong);
$query = "SELECT DISTINCT book, chapter, verse FROM kjv WHERE strong = '{$strong}';";
$hits = array();
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
unset($row[0]);
unset($row[1]);
unset($row[2]);
$hits[] = $row;
}
return $hits;
}
public function getType($id)
{
$id = Database_SQLiteInjection::no($id);
$query = "SELECT type FROM books WHERE id = {$id};";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
return $row[0];
}
return "";
}
private function getNextId($user)
{
$user = Database_SQLiteInjection::no($user);
// Get the ID of the active entry for the user.
$id = 0;
$query = "SELECT rowid FROM navigation WHERE username = '******' AND active = 1;";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
$id = $row[0];
}
// If no active ID was found, return NULL.
if ($id == 0) {
return NULL;
}
// Get the ID of the entry just after the active entry.
$query = "SELECT rowid FROM navigation WHERE rowid > {$id} AND username = '******' ORDER BY rowid ASC LIMIT 1;";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
return $row[0];
}
// Nothing found.
return NULL;
}
public function getResult($id)
{
$id = Database_SQLiteInjection::no($id);
$query = "SELECT result FROM jobs WHERE id = {$id};";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
return $row['result'];
}
return false;
}
public function translate($input, $output, $book, $chapter, $verse)
{
// Care for situation that the input and output are the same.
if ($input == $output) {
$passage = array($book, $chapter, $verse);
return array($passage);
}
// Get the $input mapping for the passage from the database.
// This maps the $input to the Hebrew/Greek versification system.
// Skip this phase if the $input mapping is Hebrew / Greek.
$origpassage = array();
if ($input != $this->original()) {
$input = Database_SQLiteInjection::no($input);
$book = (int) $book;
$chapter = (int) $chapter;
$verse = (int) $verse;
$query = "SELECT origbook, origchapter, origverse FROM maps WHERE name = '{$input}' AND book = {$book} AND chapter = {$chapter} AND verse = {$verse};";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
unset($row['origbook']);
unset($row['origchapter']);
unset($row['origverse']);
$origpassage[] = $row;
}
}
// Check that the search yields a passage.
// If there is none, it means that the $input passage is the same as in Hebrew/Greek.
if (empty($origpassage)) {
$passage = array($book, $chapter, $verse);
$origpassage = array($passage);
}
// If the $output mapping is Hebrew/Greek, then we're done.
if ($output == $this->original()) {
return $origpassage;
}
// Get the $output mapping for the passage or two passages from the database.
// This is a translation from Hebrew/Greek to the $output system.
$targetpassage = array();
$output = Database_SQLiteInjection::no($output);
foreach ($origpassage as $passage) {
$origbook = $passage[0];
$origchapter = $passage[1];
$origverse = $passage[2];
$query = "SELECT book, chapter, verse FROM maps WHERE name = '{$output}' AND origbook = {$origbook} AND origchapter = {$origchapter} AND origverse = {$origverse};";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
unset($row['book']);
unset($row['chapter']);
unset($row['verse']);
$row[0] = (int) $row[0];
$row[1] = (int) $row[1];
$row[2] = (int) $row[2];
if (array_search($row, $targetpassage) === false) {
$targetpassage[] = $row;
}
}
}
// Check that the search yields a passage.
// If none, it means that the $output passage is the same as in Hebrew/Greek.
if (empty($targetpassage)) {
$targetpassage = $origpassage;
}
// Result.
return $targetpassage;
}
public function delete($bible, $book, $chapter)
{
$bible = Database_SQLiteInjection::no($bible);
$book = Database_SQLiteInjection::no($book);
$chapter = Database_SQLiteInjection::no($chapter);
$query = "DELETE FROM bibleactions WHERE bible = '{$bible}' AND book = {$book} AND chapter = {$chapter};";
Database_SQLite::exec($this->db, $query);
}
/**
* Remove a process' data from the database.
*/
public function removeProcess($name, $pid)
{
$name = Database_SQLiteInjection::no($name);
$pid = Database_SQLiteInjection::no($pid);
$query = "DELETE FROM shell WHERE name = '{$name}' AND pid = '{$pid}';";
Database_SQLite::exec($this->db, $query);
}
public function get($name, $book, $chapter, $verse)
{
$html = "";
$file = $this->databaseFile($name, $book);
if (file_exists($file)) {
$db = $this->connect($file);
$chapter = Database_SQLiteInjection::no($chapter);
$verse = Database_SQLiteInjection::no($verse);
$query = "SELECT html FROM offlineresources WHERE chapter = {$chapter} AND verse = {$verse};";
$result = Database_SQLite::query($db, $query);
foreach ($result as $row) {
$html = $row[0];
}
unset($db);
}
return $html;
}
public function searchHebrew($hebrew)
{
$hebrew = Database_SQLiteInjection::no($hebrew);
$query = "SELECT DISTINCT book, chapter, verse FROM morphhb WHERE hebrew = '{$hebrew}';";
$hits = array();
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
unset($row[0]);
unset($row[1]);
unset($row[2]);
$hits[] = $row;
}
return $hits;
}
public function authors($bibles)
{
$authors = array();
$bibleCondition = " WHERE ";
foreach ($bibles as $offset => $bible) {
if ($offset) {
$bibleCondition .= " OR ";
}
$bible = Database_SQLiteInjection::no($bible);
$bibleCondition .= " bible = '{$bible}' ";
}
$query = "SELECT author, COUNT(*) as count FROM history {$bibleCondition} GROUP BY author ORDER BY count DESC;";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
$authors[] = $row[0];
}
return $authors;
}
/**
* delete - Deletes $id from the table.
*/
public function delete($id)
{
$id = Database_SQLiteInjection::no($id);
$query = "DELETE FROM confirm WHERE id = {$id};";
Database_SQLite::exec($this->db, $query);
}
public function getVerses($name, $book, $chapter)
{
$id = $this->getID($name);
$book = Database_SQLiteInjection::no($book);
$chapter = Database_SQLiteInjection::no($chapter);
$query = "SELECT DISTINCT verse FROM data WHERE system = {$id} AND book = {$book} AND chapter = {$chapter} ORDER BY verse ASC;";
$result = Database_SQLite::query($this->db, $query);
$verses = array();
foreach ($result as $row) {
$verse = $row[0];
for ($i = 0; $i <= $verse; $i++) {
$verses[] = $i;
}
}
// Put verse 0 in chapter 0.
if ($chapter == 0) {
$verses[] = 0;
}
return $verses;
}
public function delete($rowid)
{
$rowid = Database_SQLiteInjection::no($rowid);
$query = "DELETE FROM noteactions where rowid = {$rowid};";
Database_SQLite::exec($this->db, $query);
}
public function revokeWriteAccess($user, $sheet)
{
$user = Database_SQLiteInjection::no($user);
$sheet = Database_SQLiteInjection::no($sheet);
if ($user != "") {
$query = "DELETE FROM users WHERE user = '******' AND sheet = '{$sheet}';";
} else {
$query = "DELETE FROM users WHERE sheet = '{$sheet}';";
}
Database_SQLite::exec($this->db, $query);
}
public function clearHistory($bible, $year, $month)
{
$bible = Database_SQLiteInjection::no($bible);
$year = Database_SQLiteInjection::no($year);
$month = Database_SQLiteInjection::no($month);
$query = "DELETE FROM sprinthistory WHERE bible = '{$bible}' AND year = {$year} AND month = {$month};";
Database_SQLite::exec($this->db, $query);
}
public function release($id)
{
$id = Database_SQLiteInjection::no($id);
$query = "DELETE FROM suppress WHERE rowid = {$id};";
Database_SQLite::exec($this->db, $query);
}
public function get($day)
{
$day = (int) $day;
//$firstsecond = strtotime ("today") - ($day * 86400);
// A day is considered not a natural day, but a period of 24 hours from now.
$day++;
$firstsecond = time() - $day * 86400;
$lastsecond = $firstsecond + 86400 - 1;
$firstfilename = $firstsecond . str_repeat("0", 8);
$lastfilename = $lastsecond . str_repeat("9", 8);
$lastentry = $firstfilename;
$entries = array();
// Read them from the database.
$db = self::connect();
$firsttimestamp = Database_SQLiteInjection::no($firstfilename);
$lasttimestamp = Database_SQLiteInjection::no($lastfilename);
$query = "SELECT entry FROM logs WHERE timestamp >= {$firsttimestamp} AND timestamp <= {$lasttimestamp} ORDER BY rowid ASC;";
$result = Database_SQLite::query($db, $query);
foreach ($result as $row) {
$entries[] = $row[0];
}
unset($db);
// Add them from the filesystem.
$files = scandir($this->folder());
$files = Filter_Folders::cleanup($files);
foreach ($files as $file) {
if ($file >= $firstfilename && $file <= $lastfilename) {
$lastentry = $file;
$entries[] = file_get_contents($this->folder() . "/{$file}");
}
}
return array($lastentry, $entries);
}
public function searchGreek($greek)
{
$greek = Database_SQLiteInjection::no($greek);
$query = "SELECT DISTINCT book, chapter, verse FROM sblgnt WHERE greek = '{$greek}';";
$hits = array();
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
unset($row[0]);
unset($row[1]);
unset($row[2]);
$hits[] = $row;
}
return $hits;
}
public function getNotesInRangeForBibles($lowId, $highId, $bibles)
{
$db = self::connect();
$identifiers = array();
$lowId = Database_SQLiteInjection::no($lowId);
$highId = Database_SQLiteInjection::no($highId);
$bibleSelector = $this->getBibleSelector($bibles);
$query = "SELECT identifier FROM notes WHERE identifier >= {$lowId} AND identifier <= {$highId} {$bibleSelector} ORDER BY identifier;";
$result = Database_SQLite::query($db, $query);
foreach ($result as $row) {
$identifiers[] = $row[0];
}
unset($db);
return $identifiers;
}
public function getVerseCount($bible)
{
$bible = Database_SQLiteInjection::no($bible);
$query = "SELECT count(*) FROM bibles WHERE bible = '{$bible}';";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
return $row[0];
}
return 0;
}
