public static function getAllAuthorsByFirstLetter()
{
list(, $result) = parent::executeQuery("select {0}\nfrom authors\ngroup by substr (upper (sort), 1, 1)\norder by substr (upper (sort), 1, 1)", "substr (upper (sort), 1, 1) as title, count(*) as count", "", array(), -1);
$entryArray = array();
while ($post = $result->fetchObject()) {
array_push($entryArray, new Entry($post->title, Author::getEntryIdByLetter($post->title), str_format(localize("authorword", $post->count), $post->count), "text", array(new LinkNavigation("?page=" . parent::PAGE_AUTHORS_FIRST_LETTER . "&id=" . rawurlencode($post->title))), "", $post->count));
}
return $entryArray;
}
public static function getAllTagsByQuery($query, $n, $database = NULL, $numberPerPage = NULL)
{
$columns = "tags.id as id, tags.name as name, (select count(*) from books_tags_link where tags.id = tag) as count";
$sql = 'select {0} from tags where upper (tags.name) like ? {1} order by tags.name';
list($totalNumber, $result) = parent::executeQuery($sql, $columns, "", array('%' . $query . '%'), $n, $database, $numberPerPage);
$entryArray = array();
while ($post = $result->fetchObject()) {
$tag = new Tag($post);
array_push($entryArray, new Entry($tag->name, $tag->getEntryId(), str_format(localize("bookword", $post->count), $post->count), "text", array(new LinkNavigation($tag->getUri()))));
}
return array($entryArray, $totalNumber);
}
public static function getEntryArray($query, $params)
{
list(, $result) = parent::executeQuery($query, self::RATING_COLUMNS, "", $params, -1);
$entryArray = array();
while ($post = $result->fetchObject()) {
$ratingObj = new Rating($post->id, $post->rating);
$rating = $post->rating / 2;
$rating = str_format(localize("ratingword", $rating), $rating);
array_push($entryArray, new Entry($rating, $ratingObj->getEntryId(), str_format(localize("bookword", $post->count), $post->count), "text", array(new LinkNavigation($ratingObj->getUri())), "", $post->count));
}
return $entryArray;
}
public static function getAllLanguages()
{
$result = parent::getDb()->query('select languages.id as id, languages.lang_code as lang_code, count(*) as count
from languages, books_languages_link
where languages.id = books_languages_link.lang_code
group by languages.id, books_languages_link.lang_code
order by languages.lang_code');
$entryArray = array();
while ($post = $result->fetchObject()) {
$language = new Language($post->id, $post->lang_code);
array_push($entryArray, new Entry(Language::getLanguageString($language->lang_code), $language->getEntryId(), str_format(localize("bookword", $post->count), $post->count), "text", array(new LinkNavigation($language->getUri())), "", $post->count));
}
return $entryArray;
}
function query($queryString, $params = NULL)
{
$queryString = isset($params) ? str_format($queryString, $params) : $queryString;
$queryResult = mysqli_query($this->connection, $queryString);
if ($queryResult) {
$outputArray = array();
while ($row = mysqli_fetch_assoc($queryResult)) {
$outputArray[] = $row;
}
mysqli_free_result($queryResult);
mysqli_next_result($this->connection);
return $outputArray;
}
return FALSE;
}
/**
* @param Book $book
* @return array
*/
public static function getBookContentArray($book)
{
global $config;
$i = 0;
$preferedData = array();
foreach ($config['cops_prefered_format'] as $format) {
if ($i == 2) {
break;
}
if ($data = $book->getDataFormat($format)) {
$i++;
array_push($preferedData, array("url" => $data->getHtmlLink(), "name" => $format));
}
}
$publisher = $book->getPublisher();
if (is_null($publisher)) {
$pn = "";
$pu = "";
} else {
$pn = $publisher->name;
$link = new LinkNavigation($publisher->getUri());
$pu = $link->hrefXhtml();
}
$serie = $book->getSerie();
if (is_null($serie)) {
$sn = "";
$scn = "";
$su = "";
} else {
$sn = $serie->name;
$scn = str_format(localize("content.series.data"), $book->seriesIndex, $serie->name);
$link = new LinkNavigation($serie->getUri());
$su = $link->hrefXhtml();
}
$cc = $book->getCustomColumnValues($config['cops_calibre_custom_column_list'], true);
return array("id" => $book->id, "hasCover" => $book->hasCover, "preferedData" => $preferedData, "rating" => $book->getRating(), "publisherName" => $pn, "publisherurl" => $pu, "pubDate" => $book->getPubDate(), "languagesName" => $book->getLanguages(), "authorsName" => $book->getAuthorsName(), "tagsName" => $book->getTagsName(), "seriesName" => $sn, "seriesIndex" => $book->seriesIndex, "seriesCompleteName" => $scn, "seriesurl" => $su, "customcolumns_list" => $cc);
}
public static function executeQuery($query, $columns, $filter, $params, $n, $database = NULL, $numberPerPage = NULL)
{
$totalResult = -1;
if (useNormAndUp()) {
$query = preg_replace("/upper/", "normAndUp", $query);
$columns = preg_replace("/upper/", "normAndUp", $columns);
}
if (is_null($numberPerPage)) {
$numberPerPage = getCurrentOption("max_item_per_page");
}
if ($numberPerPage != -1 && $n != -1) {
// First check total number of results
$result = self::getDb($database)->prepare(str_format($query, "count(*)", $filter));
$result->execute($params);
$totalResult = $result->fetchColumn();
// Next modify the query and params
$query .= " limit ?, ?";
array_push($params, ($n - 1) * $numberPerPage, $numberPerPage);
}
$result = self::getDb($database)->prepare(str_format($query, $columns, $filter));
$result->execute($params);
return array($totalResult, $result);
}
function mail_expense($description, $amount, $type, $timestamp, $booker_userid, $groupid, $members, $eventid, $deposit = 0)
{
if (!preg_match("/(,|\\.)/", $amount)) {
$amount .= ".00";
} elseif (preg_match("/,/", $amount)) {
$amount = str_replace(",", ".", $amount);
}
$userList = array();
foreach ($members as $id) {
$userList[$id] = get_user_profile($id);
}
$allgroupmembers = get_groupmembers($groupid, false, true);
$groupdetails = get_groupdetails($groupid);
$balancelist = array_sort(get_group_balance_list($groupdetails), 'unformatted_balance', SORT_DESC);
if ($deposit == 0) {
$p1 = date('l jS \\of F Y');
$p3 = number_format($amount, DECIMALS, DSEP, TSEP);
$p4 = '';
if (!empty($eventid)) {
$eventdetails = get_eventdetails($eventid);
$p4 = " for event \"{$eventdetails['eventname']}\"";
}
$p5 = $description;
$p7 = number_format($amount / count($members), DECIMALS, DSEP, TSEP);
$message = "On {1} {2} booked an expense of € {3}{4} with description \"{5}\".<br /><br />";
$message .= "You were listed as a participant, together with {6}.<br /><br />";
$message .= "The costs per person are € {7} making your balance € {8} which comes to position {9} in the group. ";
$message .= "The balance list is now: <br /><br />{10}";
$message .= "<br /><br /><a href=\"" . LOGIN_URL . "\">Going Dutch</a>";
$from = '*****@*****.**';
$from_name = 'Going Dutch';
$subject = "Going Dutch expense booked in group \"{$groupdetails['name']}\"";
$subject = addslashes($subject);
foreach ($userList as $user) {
// skip user if prefs are not set
if (!isset($user['name_format']) || !isset($user['email_notify'])) {
continue;
}
if ($user['user_id'] == $booker_userid) {
$p2 = 'you have';
} else {
$p2 = format_name($user['name_format'], $userList[$booker_userid]['username'], $userList[$booker_userid]['realname']) . ' has';
}
$others = '';
foreach ($members as $participant) {
if ($participant != $user['user_id']) {
$others .= ', ' . format_name($user['name_format'], $userList[$participant]['username'], $userList[$participant]['realname']);
}
}
//
$p6 = preg_replace('/(.*),/', '$1 and', trim($others, ', '));
//$text = preg_replace('/(.*),/','$1 and',$text)
$p8 = $balancelist[$user['user_id']]['balance'];
$i = 1;
foreach ($balancelist as $key => $val) {
if ($key == $user['user_id']) {
break;
}
$i++;
}
$p9 = $i;
$tablehtml = create_memberlist_html_table($balancelist, $allgroupmembers, $user['name_format'], SORT_DESC);
$p10 = $tablehtml;
$a = 1;
$body = str_format($message, $p1, $p2, $p3, $p4, $p5, $p6, $p7, $p8, $p9, $p10);
$pp = $amount / count($members);
$floatval = floatval($user['email_notify']);
if ($user['email_notify'] != '-1' && $pp >= $floatval) {
// exec($command, $output = array());
// smtpmailer($user['email'], $from, $from_name, $subject, $body, $replyto = '', $sendas='to');
$replyto = '';
$sendas = 'to';
$background_mailfile = dirname(__FILE__) . '/background_mailer.php';
//$output = '/var/log/test';
$output = '/dev/null';
$body = addslashes($body);
$cmd = "/usr/bin/php5 {$background_mailfile} {$user['email']} {$from} \"{$from_name}\" \"{$subject}\" \"{$body}\" \"{$replyto}\" \"{$sendas}\"";
//exec("/usr/bin/php {$background_mailfile} {$user['email']} {$from} {$from_name} {$subject} {$body} {$replyto} {$sendas} > {$ouput} &");
exec("{$cmd} > {$output} &");
//exec("/usr/bin/php {$background_mailfile} {$user['email']} {$from} \"{$from_name}\" \"{$subject}\" \"{$body}\" \"{$replyto}\" \"{$sendas}\" > {$output} &");
}
}
//
// foreach ($members as $key => $value) {
//
// }
//$mysqldate = date('Y-m-d H:i:s', $timestamp);
$sql = "INSERT INTO expenses (type, user_id, group_id, description, amount, expense_date, event_id, timestamp, currency) \n VALUES ('{$type}', '{$userid}', '{$groupid}', '{$description}', '{$amount}', FROM_UNIXTIME({$timestamp}), {$eventid}, CURRENT_TIMESTAMP , '1')";
// if (!$result = mysql_query($sql)) {
// return false;
// } else {
// $expenseid = mysql_insert_id();
// foreach ($members as $key => $value) {
// $sql = "INSERT INTO users_expenses (`user_id` , `expense_id`) VALUES ('$value', '$expenseid')";
// if (!$result = mysql_query($sql)) {
// $inserterror = true;
// }
// }
// if ($inserterror)
// return false;
// return true;
// }
} else {
// making a deposit
// for each person, make an expense with only deposit holder ($userid) as recipient
// first register deposit to get a deposit id
// $sql = "INSERT INTO deposits (holder, description) VALUES ($userid, '$description')";
// if (!$result = mysql_query($sql)) {
// return false;
// } else {
// $depositid = mysql_insert_id();
// }
//
// foreach ($members as $key => $value) {
// $sql = "INSERT INTO expenses (type, user_id, group_id, description, amount, expense_date, event_id, timestamp, currency, deposit_id)
// VALUES ('$type', '$value', '$groupid', '$description', '$amount', FROM_UNIXTIME($timestamp), $eventid, CURRENT_TIMESTAMP , '1', $depositid)";
// if (!$result = mysql_query($sql)) {
// return false;
// } else {
// $expenseid = mysql_insert_id();
// $sql = "INSERT INTO users_expenses (`user_id` , `expense_id`) VALUES ('$userid', '$expenseid')";
// if (!$result = mysql_query($sql)) {
// $inserterror = true;
// }
// }
// }
}
if ($inserterror) {
return false;
}
return true;
}
public static function getAllBooks()
{
list(, $result) = parent::executeQuery("select {0}\nfrom books\ngroup by substr (upper (sort), 1, 1)\norder by substr (upper (sort), 1, 1)", "substr (upper (sort), 1, 1) as title, count(*) as count", self::getFilterString(), array(), -1);
$entryArray = array();
while ($post = $result->fetchObject()) {
array_push($entryArray, new Entry($post->title, Book::getEntryIdByLetter($post->title), str_format(localize("bookword", $post->count), $post->count), "text", array(new LinkNavigation("?page=" . parent::PAGE_ALL_BOOKS_LETTER . "&id=" . rawurlencode($post->title))), "", $post->count));
}
return $entryArray;
}
/**
* Formats a hash/digest based on string length.
*
* @param string $hash Hash/digest string.
* @return string Formatted hash string.
*/
function hash_format($hash)
{
$string = str_replace(array('+', '-', ' '), '', $hash);
switch (strlen($string)) {
case 16:
$tmpl = '00000000-0000-0000';
break;
case 24:
$tmpl = '00000000-0000-0000-00000000';
break;
case 32:
$tmpl = '00000000-0000-0000-0000-000000000000';
break;
case 40:
$tmpl = '00000000-0000-0000-00000000-0000-0000-00000000';
break;
case 48:
$tmpl = '00000000-0000-00000000-0000-0000-00000000-0000-00000000';
break;
default:
return $string;
}
return str_format($string, $tmpl);
}
public function getCustomByBook($book)
{
$queryFormat = "SELECT {0}.id AS id, {0}.value AS value FROM {0} WHERE {0}.book = {1}";
$query = str_format($queryFormat, $this->getTableName(), $book->id);
$result = $this->getDb()->query($query);
if ($post = $result->fetchObject()) {
return new CustomColumn($post->id, $post->value, $this);
}
return new CustomColumn(NULL, localize("customcolumn.float.unknown"), $this);
}
public static function getAllCustoms($customId)
{
$result = parent::getDb()->query(str_format('select {0}.id as id, {0}.value as name, count(*) as count
from {0}, {1}
where {0}.id = {1}.{2}
group by {0}.id, {0}.value
order by {0}.value', self::getTableName($customId), self::getTableLinkName($customId), self::getTableLinkColumn($customId)));
$entryArray = array();
while ($post = $result->fetchObject()) {
$customColumn = new CustomColumn($post->id, $post->name, $customId);
array_push($entryArray, new Entry($customColumn->name, $customColumn->getEntryId(), str_format(localize("bookword", $post->count), $post->count), "text", array(new LinkNavigation($customColumn->getUri())), "", $post->count));
}
return $entryArray;
}
