function find_by_account($account, $limit = null)
{
$db =& DBUtils::connect();
$sql = 'SELECT f.id, f.url AS url FROM foaf f
JOIN member m ON f.member_id = m.id
WHERE m.account = ?';
$result = $db->getAll($sql, array($account));
if (DB::isError($result)) {
trigger_error(__CLASS__ . '::' . __FUNCTION__ . '(): ' . $result->toString(), E_USER_ERROR);
return null;
}
$parser =& new FOAFParser(true, CACHE_LITE_DIR, 60 * 60 * 24 * 1);
$foafs = array();
foreach ($result as $foaf) {
if (@$parser->parse($foaf['url']) === false) {
continue;
}
$people = $parser->getKnowsPerson();
foreach ($people as $index => $person) {
$people[$index]['foaf_id'] = $foaf['foaf_id'];
$people[$index]['foaf_url'] = $foaf['url'];
$p =& new FOAFParser(true, CACHE_LITE_DIR, 60 * 60 * 24 * 1);
if ($p->parse($person['seeAlso'])) {
$person['img'] = $p->getImg();
}
$foafs[$person['seeAlso']] = $person;
}
}
$res = array();
foreach ($foafs as $foaf) {
$res[] = $foaf;
}
return $res;
}
function find_with_content_category_by_account($account)
{
$db =& DBUtils::connect();
$sql = 'SELECT cc.id AS category_id, cc.name AS category_name, mcf.feed_id
FROM member_to_content_category_to_feed mcf
JOIN member m ON mcf.member_id = m.id
JOIN content_category cc ON mcf.content_category_id = cc.id
WHERE m.account = ?';
$result = $db->getAll($sql, array($account));
if (DB::isError($result)) {
trigger_error(__CLASS__ . '::' . __FUNCTION__ . '(): ' . $result->toString(), E_USER_ERROR);
return false;
}
$mcfs = $result;
$other = ContentCategoryUtils::get(PLNET_OTHER_CATEGORY_ID);
$feeds = FeedUtils::get_feeds_by_account($account);
$feeds_with_category = array();
foreach ($feeds as $key => $feed) {
foreach ($mcfs as $i => $mcf) {
if ($feed['id'] == $mcf['feed_id']) {
$feed['category_id'] = $mcf['category_id'];
$feed['category_name'] = $mcf['category_name'];
}
}
if (!isset($feed['category_id'])) {
$feed['category_id'] = $other['id'];
$feed['category_name'] = $other['name'];
}
$feeds_with_category[] = $feed;
}
return $feeds_with_category;
}
function get_tagid_by_tagname($tagid)
{
$db =& DBUtils::connect();
$sql = "SELECT id FROM tag WHERE name = ?";
$result =& $db->getOne($sql, array($tagid));
if (DB::isError($result)) {
trigger_error('PlnetUtils::get_tagid_from_tagname(): fetch error.' . $result->toString(), E_USER_ERROR);
return false;
}
return $result;
}
function get($content_category_id)
{
$db =& DBUtils::connect();
$sql = 'SELECT * FROM content_category WHERE id = ?';
$result = $db->getRow($sql, array($content_category_id));
if (DB::isError($result)) {
trigger_error(__CLASS__ . '::' . __FUNCTION__ . '(): ' . $result->toString(), E_USER_ERROR);
return false;
}
return $result;
}
function get_sources_by_account($account)
{
$db =& DBUtils::connect();
$sql = "SELECT s.id, s.name, s.uri, s.link, s.icon \n FROM member m \n JOIN source s ON m.id = s.member_id \n WHERE m.account = ? \n ORDER BY s.id";
$result = $db->getAll($sql, array($account));
if (DB::isError($result)) {
trigger_error('PlnetUtils::get_sources_by_account(): ' . $result->toString(), E_USER_ERROR);
return false;
}
return $result;
}
function get_hot_tags($limit = null)
{
$db =& DBUtils::connect();
$sql = "SELECT t.name, COUNT(t.id) AS cnt\n FROM entry e\n JOIN entry_to_tag e2t ON e.id = e2t.entry_id\n JOIN tag t ON e2t.tag_id = t.id\n WHERE ? < e.date\n GROUP BY t.id\n ORDER BY cnt desc";
$sql .= $limit != null ? " LIMIT " . $limit : "";
$tags = $db->getAll($sql, array(date("Y-m-d", time() - 172800)));
if (DB::isError($tags)) {
trigger_error('fetch error.' . $tags->toString(), E_USER_ERROR);
return false;
}
return $tags;
}
function get_list($limit)
{
$db =& DBUtils::connect();
$sql = 'SELECT c.id, c.name, cc.id AS category_id, cc.name AS category_name,
c.uri, c.icon, c.format, c.target, c.foaf_format, c.description
FROM content c
JOIN content_category cc ON c.content_category_id = cc.id
ORDER BY cc.id, c.id
LIMIT ?';
$result = $db->getAll($sql, array($limit));
if (DB::isError($result)) {
trigger_error('ContentUtils::get_list(): ' . $result->toString(), E_USER_ERROR);
return false;
}
return $result;
}
function is_exists_by_member_id_and_feed_id($member_id, $feed_id)
{
$db =& DBUtils::connect();
$sql = 'SELECT content_category_id FROM member_to_content_category_to_feed
WHERE member_id = ?
AND feed_id = ?';
$result = $db->getOne($sql, array($member_id, $feed_id));
if (DB::isError($result)) {
trigger_error(__CLASS__ . '::' . __FUNCTION__ . '(): ' . $result->toString(), E_USER_ERROR);
return false;
}
if ($result) {
return true;
} else {
return false;
}
}
function is_exists_by_account_and_url($account, $url)
{
$db =& DBUtils::connect();
$sql = 'SELECT f.id FROM foaf f
JOIN member m ON f.member_id = m.id
WHERE m.account = ?
AND f.url = ?';
$result = $db->getOne($sql, array($account, $url));
if (DB::isError($result)) {
trigger_error(__CLASS__ . '::' . __FUNCTION__ . '(): ' . $result->toString(), E_USER_ERROR);
return null;
}
if ($result > 0) {
return true;
} else {
return false;
}
}
function is_exist_today($owner_id, $visitor_id)
{
$one_day_before = date("Y-m-d H:i:s", time() - 86400);
$db =& DBUtils::connect();
$sql = 'SELECT id FROM footprint fp
WHERE owner_id = ?
AND visitor_id = ?
AND created_on > ?';
$result = $db->getOne($sql, array($owner_id, $visitor_id, $one_day_before));
if (DB::isError($result)) {
trigger_error('FootprintUtils::is_exist_tody(): ' . $result->toString(), E_USER_ERROR);
return false;
}
if ($result) {
return true;
} else {
return false;
}
}
function get_list_by_tag_name($name, $limit = null)
{
$db =& DBUtils::connect();
$sql = 'SELECT m.id, m.account, m.photo, count(t.id) AS cnt
FROM member m
JOIN member_to_feed m2f ON m.id = m2f.member_id
JOIN feed f ON f.id = m2f.feed_id
JOIN entry e ON f.id = e.feed_id
JOIN entry_to_tag e2t ON e.id = e2t.entry_id
JOIN tag t ON t.id = e2t.tag_id
JOIN site s ON s.member_id = m.id
WHERE t.name = ?
GROUP BY m.id
ORDER BY cnt DESC';
if ($limit) {
$sql .= " LIMIT {$limit}";
}
$result = $db->getAll($sql, array($name));
if (DB::isError($result)) {
trigger_error(__CLASS__ . '::' . __FUNCTION__ . '(): ' . $result->toString(), E_USER_ERROR);
return false;
}
return $result;
}
function get_entries_count_by_account_and_tagname($account, $tagname)
{
$member_id = MemberUtils::get_id_by_account($account);
$db =& DBUtils::connect();
$sql = "SELECT COUNT(e.id) AS CNT\n FROM member_to_feed m2f\n JOIN feed f ON m2f.feed_id = f.id\n JOIN entry e ON f.id = e.feed_id\n JOIN entry_to_tag e2t ON e.id = e2t.entry_id\n JOIN tag t ON e2t.tag_id = t.id\n WHERE m2f.member_id = ?\n AND t.name = ?";
$result =& $db->getOne($sql, array($member_id, $tagname));
if (DB::isError($result)) {
trigger_error(__CLASS__ . '::' . __FUNCTION__ . '(): ' . $result->toString(), E_USER_ERROR);
return false;
}
return $result;
}
function Crawler()
{
$this->db = DBUtils::connect(false);
}
function execute(&$controller, &$request, &$user)
{
$uri = $request->getParameter('uri');
$member = $user->getAttribute('member', GLU_NS);
$redirect = $request->hasParameter('redirect') ? $request->getParameter('redirect') : null;
$cc_id = $this->getContentCategoryIdByUri($uri) ? $this->getContentCategoryIdByUri($uri) : 8;
// get feed
$f = new FeedParser($uri);
$parse_result = $f->parse();
if ($parse_result === false) {
trigger_error(__CLASS__ . '::' . __FUNCTION__ . '(): ' . "Failed to parse feed uri: {$uri}", E_USER_NOTICE);
header('Content-Type: text/plain; charset=utf-8');
echo 'false';
return VIEW_NONE;
}
$name = $f->getTitle();
$favicon = $f->getFavicon() ? $f->getFavicon() : SCRIPT_PATH . 'images/favicon.ico';
$db =& DBUtils::connect(false);
$db->query('BEGIN');
// get feedId
$feedId = $this->getFeedIdByUri($uri);
if ($feedId === false) {
// add feed
$fields = array('uri' => $uri, 'link' => $f->getLink(), 'title' => $f->getTitle(), 'description' => $f->getDescription(), 'favicon' => $favicon, 'lastupdatedtime' => date("Y-m-d H:i:s"));
$res = $db->autoExecute('feed', $fields, DB_AUTOQUERY_INSERT);
if (DB::isError($res)) {
$db->rollback();
trigger_error(__CLASS__ . '::' . __FUNCTION__ . '(): ' . "Failed to insert. " . $res->toString(), E_USER_ERROR);
header('Content-Type: text/plain; charset=utf-8');
echo 'false';
return VIEW_NONE;
}
// specific mysql
$feedId = $db->getOne('SELECT LAST_INSERT_ID()');
}
// add member_to_feed
$m2f_fields = array('member_id' => MemberUtils::get_id_by_account($member->account), 'feed_id' => $feedId);
if (DBUtils::get('member_to_feed', $m2f_fields)) {
echo "'already exists'";
// already exists
return VIEW_NONE;
}
$res = $db->autoExecute('member_to_feed', $m2f_fields, DB_AUTOQUERY_INSERT);
if (DB::isError($res)) {
$db->rollback();
trigger_error(__CLASS__ . '::' . __FUNCTION__ . '(): ' . "Failed to insert m2f. " . $res->toString(), E_USER_WARNING);
header('Content-Type: text/plain; charset=utf-8');
echo 'false';
return VIEW_NONE;
}
// add member_to_content_category_to_feed
$m2cc2f_fields = array('member_id' => $member->id, 'content_category_id' => $cc_id, 'feed_id' => $feedId);
$sql = "SELECT count(*)\n FROM member_to_content_category_to_feed\n WHERE member_id = ?\n AND content_category_id = ?\n AND feed_id = ? ";
if ($db->getOne($sql, array_values($m2cc2f_fields)) == 0) {
$res = $db->autoExecute('member_to_content_category_to_feed', $m2cc2f_fields, DB_AUTOQUERY_INSERT);
if (DB::isError($res)) {
$db->rollback();
trigger_error(__CLASS__ . '::' . __FUNCTION__ . '(): ' . 'Failed to insert m2cc2f. ' . $res->toString(), E_USER_WARNING);
header('Content-Type: text/plain; charset=utf-8');
echo 'false';
return VIEW_NONE;
}
}
// try to crawl
$crawler =& new Crawler();
$crawl_result = $crawler->crawl($uri);
if ($crawl_result === false) {
$db->rollback();
trigger_error("AddFeedAction::execute(): Failed to crawl: {$uri}", E_USER_NOTICE);
header('Content-Type: text/plain; charset=utf-8');
echo 'false';
return VIEW_NONE;
}
$db->commit();
if ($redirect) {
Controller::redirect(SCRIPT_PATH . 'setting/feed');
} else {
header('Content-Type: text/plain; charset=utf-8');
echo 'true';
}
return VIEW_NONE;
}
<?php
require_once dirname(dirname(__FILE__)) . '/webapp/config.php';
$db =& DBUtils::connect();
$members = $db->getAll('SELECT * FROM member LIMIT 5');
print_r($members);
