/**
* Relate articles (cron task function).
*
* @param vivvo_lite_site $sm
*/
function auto_relate($sm)
{
if (!VIVVO_ARTICLE_RELATED_CATEGORY and !VIVVO_ARTICLE_RELATED_TAGS and !VIVVO_ARTICLE_RELATED_TOPIC) {
$result = 'Nothing to do.';
} else {
$datetime = date('Y-m-d 23:59:00');
$db = $sm->get_db();
do {
$res = $db->query('SELECT COUNT(*) FROM ' . VIVVO_DB_PREFIX . "articles WHERE created < '{$datetime}' AND status > 0");
if (!PEAR::isError($res)) {
$count = $res->fetchOne();
$res->free();
} else {
$result = 'Failed to run.';
break;
}
$db->exec('TRUNCATE TABLE ' . VIVVO_DB_PREFIX . 'related');
$res = $db->query('SELECT id, category_id FROM ' . VIVVO_DB_PREFIX . "articles WHERE created <= '{$datetime}' AND status > 0");
if (PEAR::isError($res)) {
$result = 'Failed to select articles.';
break;
}
$max = VIVVO_ARTICLE_RELATED_CATEGORY + VIVVO_ARTICLE_RELATED_TOPIC + VIVVO_ARTICLE_RELATED_TAGS;
$rel_category = VIVVO_ARTICLE_RELATED_CATEGORY;
$rel_parent = VIVVO_ARTICLE_RELATED_CATEGORY / 2;
$categories = $sm->get_categories()->list;
while ($row = $res->fetchRow(MDB2_FETCHMODE_ASSOC)) {
$category_id = $row['category_id'];
$parent_id = $categories[$category_id]->get_parent_cat();
$sum = array();
$join = '';
if (VIVVO_ARTICLE_RELATED_CATEGORY >= 0) {
$sum[] = "IF(a.category_id = {$category_id}, {$rel_category}, IF(a.category_id = {$parent_id}, {$rel_parent}, 0))";
}
if (VIVVO_ARTICLE_RELATED_TOPIC >= 0) {
$topics_res = $db->query('SELECT DISTINCT tags_groups_id FROM ' . VIVVO_DB_PREFIX . "articles_tags WHERE article_id = {$row['id']}");
if (PEAR::isError($topics_res)) {
$topics = array();
} else {
$topics = $topics_res->fetchCol();
$topics_res->free();
}
if (!empty($topics)) {
$join .= ' INNER JOIN ' . VIVVO_DB_PREFIX . 'articles_tags AS at1 ON (at1.article_id = a.id AND at1.tags_group_id IN (' . implode(',', $topics) . '))';
$sum[] = 'COUNT(DISTINCT at1.tags_groups_id) * ' . VIVVO_ARTICLE_RELATED_TOPIC / count($topics);
}
}
if (VIVVO_ARTICLE_RELATED_TAGS >= 0) {
$tags_res = $db->query('SELECT DISTINCT tag_id FROM ' . VIVVO_DB_PREFIX . "articles_tags WHERE article_id = {$row['id']}");
if (PEAR::isError($tags_res)) {
$tags = array();
} else {
$tags = $tags_res->fetchCol();
$tags_res->free();
}
if (!empty($tags)) {
$join .= ' INNER JOIN ' . VIVVO_DB_PREFIX . 'articles_tags AS at2 ON (at2.article_id = a.id AND at2.tag_id IN (' . implode(',', $tags) . '))';
$sum[] = 'COUNT(DISTINCT at2.tag_id) * ' . VIVVO_ARTICLE_RELATED_TAGS / count($tags);
}
}
$sql = 'SELECT a.id, (' . implode('+', $sum) . ') AS score
FROM ' . VIVVO_DB_PREFIX . "articles AS a\r\n\t\t\t\t\t\t\t{$join}\r\n\t\t\t\t\t\t\tWHERE created <= '{$datetime}' AND status > 0 AND a.id != {$row['id']}\r\n\t\t\t\t\t\t\tGROUP BY a.id\r\n\t\t\t\t\t\t\tHAVING score > 0\r\n\t\t\t\t\t\t\tORDER BY score DESC\r\n\t\t\t\t\t\t\tLIMIT 5";
$related = $db->query($sql);
if (PEAR::isError($related)) {
$result = 'Failed to fetch related articles.';
break 2;
}
$values = array();
while ($related_row = $related->fetchRow(MDB2_FETCHMODE_ASSOC)) {
$relevance = floor($related_row['score'] / $max * 100);
$values[] = "({$row['id']},{$related_row['id']},{$relevance})";
}
$related->free();
$db->exec('INSERT INTO ' . VIVVO_DB_PREFIX . 'related VALUES ' . implode(',', $values));
}
$result = 'Executed successfully.';
} while (0);
}
if (defined('VIVVO_CRONJOB_MODE')) {
echo 'auto_relate: ' . $result . PHP_EOL;
} else {
admin_log('(Cron task: Auto Relate)', $result);
}
}
