function get_page_tok_strange($newest = false)
{
check_permission(PERM_ADDER);
$res = sql_query("SELECT timestamp, param_value FROM stats_values WHERE param_id=7 ORDER BY timestamp DESC LIMIT 1");
$r = sql_fetch_array($res);
$out = array('timestamp' => $r['timestamp'], 'coeff' => $r['param_value'] / 1000, 'broken' => array(), 'items' => array());
$res = sql_fetchall(sql_query("SELECT param_value FROM stats_values WHERE param_id=28 ORDER BY param_value"));
$res1 = sql_prepare("SELECT tf_text, sent_id FROM tokens WHERE tf_id=? LIMIT 1");
foreach ($res as $r) {
sql_execute($res1, array($r['param_value']));
$r1 = sql_fetch_array($res1);
$out['broken'][] = array('token_text' => $r1['tf_text'], 'sent_id' => $r1['sent_id']);
}
$res1->closeCursor();
$comments = array();
$res = sql_query("SELECT ts.sent_id, ts.pos, ts.border, ts.coeff, s.source, p.book_id FROM tokenizer_strange ts LEFT JOIN sentences s ON (ts.sent_id=s.sent_id) LEFT JOIN paragraphs p ON (s.par_id=p.par_id) ORDER BY " . ($newest ? "ts.sent_id DESC" : "ts.coeff DESC"));
$res1 = sql_prepare("SELECT comment_id FROM sentence_comments WHERE sent_id=? LIMIT 1");
foreach (sql_fetchall($res) as $r) {
if (!isset($comments[$r['sent_id']])) {
sql_execute($res1, array($r['sent_id']));
$comments[$r['sent_id']] = sql_num_rows($res1) > 0 ? 1 : -1;
}
$out['items'][] = array('sent_id' => $r['sent_id'], 'book_id' => $r['book_id'], 'coeff' => $r['coeff'], 'border' => $r['border'], 'lcontext' => mb_substr($r['source'], max(0, $r['pos'] - 10), min(10, $r['pos'])), 'focus' => mb_substr($r['source'], $r['pos'], 1), 'rcontext' => mb_substr($r['source'], $r['pos'] + 1, 9), 'comments' => $comments[$r['sent_id']]);
}
return $out;
}
public function pull_stats()
{
global $config;
$res = sql_fetchall(sql_query("\n SELECT *\n FROM user_achievements\n JOIN users USING (user_id)\n ORDER BY achievement_id DESC, updated DESC\n "));
$out = array();
foreach ($res as $record) {
$a = $record['achievement_type'];
$haslevels = isset($this->objects[$a]->level);
if (empty($out[$a])) {
if ($haslevels) {
$out[$a] = array_fill_keys(range(1, $config['achievements']['max_level']), array());
} else {
$out[$a] = array();
}
}
if ($haslevels) {
array_push($out[$a][$record['level']], $record);
} else {
array_push($out[$a], $record);
}
}
$total = sql_fetch_array(sql_query("SELECT COUNT(*) FROM users"));
$out['total_users'] = $total[0];
return $out;
}
function update_annot_instance($id, $answer, $user_id = 0)
{
if (!$user_id) {
$user_id = $_SESSION['user_id'];
}
if (!$id || !$answer || !$user_id) {
throw new UnexpectedValueException();
}
global $config;
static $res = NULL;
if ($res == NULL) {
$res = sql_prepare("\n SELECT pool_id, p.status, i.user_id, answer\n FROM morph_annot_instances i\n LEFT JOIN morph_annot_samples\n USING (sample_id)\n LEFT JOIN morph_annot_pools p\n USING (pool_id)\n WHERE instance_id = ?\n LIMIT 1\n ");
}
sql_execute($res, array($id));
$inst = sql_fetchall($res);
if (!sizeof($inst)) {
throw new Exception("Instance not found");
}
$r = $inst[0];
// the pool should be editable
if ($r['status'] != MA_POOLS_STATUS_IN_PROGRESS) {
throw new Exception("Пул недоступен для разметки");
}
$pool_id = $r['pool_id'];
sql_begin();
static $res_rejected = NULL;
// does the instance really belong to this user?
$previous_answer = $r['answer'] > 0;
if ($r['user_id'] != $user_id) {
// if another user has taken it, no chance
if ($r['user_id'] > 0) {
throw new Exception();
}
// or, perhaps, this user has rejected it before but has changed his mind
if ($res_rejected == NULL) {
$res_rejected = sql_prepare("SELECT sample_id FROM morph_annot_rejected_samples WHERE user_id=? AND sample_id = (SELECT sample_id FROM morph_annot_instances WHERE instance_id=? LIMIT 1) LIMIT 1");
}
sql_execute($res_rejected, array($user_id, $id));
if (sql_num_rows($res_rejected) > 0) {
$r = sql_fetch_array($res_rejected);
sql_pe("DELETE FROM morph_annot_rejected_samples WHERE user_id=? AND sample_id=? LIMIT 1", array($user_id, $r['sample_id']));
sql_pe("UPDATE morph_annot_instances SET user_id=?, ts_finish=? WHERE instance_id=? LIMIT 1", array($user_id, time() + $config['misc']['morph_annot_timeout'], $id));
}
}
// a valid answer
static $update = NULL;
if ($update == NULL) {
$update = sql_prepare("UPDATE morph_annot_instances SET user_id=?, answer=? WHERE instance_id=? LIMIT 1");
}
if ($answer > 0) {
sql_execute($update, array($user_id, $answer, $id));
} elseif ($answer == -1) {
reject_annot_instance($id, $user_id);
}
sql_commit();
}
function merge_sentences($id1, $id2)
{
check_permission(PERM_ADDER);
if ($id1 < 1 || $id2 < 1) {
throw new UnexpectedValueException();
}
// check same paragraph and adjacency
$res = sql_pe("SELECT pos, par_id FROM sentences WHERE sent_id IN (?, ?) ORDER BY pos LIMIT 2", array($id1, $id2));
$r1 = $res[0];
$r2 = $res[1];
$res = sql_query("SELECT pos FROM sentences WHERE par_id = " . $r1['par_id'] . " AND pos > " . $r1['pos'] . " AND pos < " . $r2['pos'] . " LIMIT 1");
if ($r1['par_id'] != $r2['par_id'] || sql_num_rows($res) > 0) {
throw new Exception();
}
//moving tokens
sql_begin();
$res = sql_pe("SELECT MAX(pos) AS maxpos FROM tokens WHERE sent_id=?", array($id1));
sql_pe("UPDATE tokens SET sent_id=?, pos=pos+? WHERE sent_id=?", array($id1, $res[0]['maxpos'], $id2));
//merging source text
$res_src = sql_prepare("SELECT `source` FROM sentences WHERE sent_id=? LIMIT 1");
sql_execute($res_src, array($id1));
$r1 = sql_fetchall($res_src);
sql_execute($res_src, array($id2));
$r2 = sql_fetchall($res_src);
sql_pe("UPDATE sentences SET source=? WHERE sent_id=? LIMIT 1", array($r1[0]['source'] . ' ' . $r2[0]['source'], $id1));
//dropping status, moving comments
sql_pe("UPDATE sentences SET check_status=0 WHERE sent_id=? LIMIT 1", array($id1));
sql_pe("UPDATE sentence_comments SET sent_id=? WHERE sent_id=?", array($id1, $id2));
sql_pe("DELETE FROM sentence_check WHERE sent_id=? OR sent_id=?", array($id1, $id2));
// change syntax markup accordingly
sql_pe("UPDATE syntax_parses SET sent_id = ? WHERE sent_id = ?", array($id1, $id2));
//deleting sentence
sql_pe("DELETE FROM sentence_authors WHERE sent_id=? LIMIT 1", array($id2));
sql_pe("DELETE FROM sentences WHERE sent_id=? LIMIT 1", array($id2));
sql_commit();
}
function get_context_for_word($tf_id, $delta, $dir = 0, $include_self = 1)
{
// dir stands for direction (-1 => left, 1 => right, 0 => both)
// delta <= 0 stands for infinity
$t = array();
$tw = 0;
$left_c = -1;
//if there is left context to be added
$right_c = 0;
//same for right context
$mw_pos = 0;
static $query1 = NULL;
// prepare the 1st query
if ($query1 == NULL) {
$query1 = sql_prepare("\n SELECT MAX(tokens.pos) AS maxpos, MIN(tokens.pos) AS minpos, sent_id, source, book_id\n FROM tokens\n JOIN sentences USING (sent_id)\n JOIN paragraphs USING (par_id)\n WHERE sent_id = (\n SELECT sent_id\n FROM tokens\n WHERE tf_id=? LIMIT 1\n )\n ");
}
sql_execute($query1, array($tf_id));
$res = sql_fetchall($query1);
$r = $res[0];
$sent_id = $r['sent_id'];
$sentence_text = $r['source'];
$book_id = $r['book_id'];
$maxpos = $r['maxpos'];
$minpos = $r['minpos'];
// prepare the 2nd query
// this is really bad unreadable code, sorry
static $query2 = NULL;
if ($query2 == NULL) {
$q = "SELECT tf_id, tf_text, pos FROM tokens WHERE sent_id = ?";
if ($dir != 0 || $delta > 0) {
$q_left = $dir <= 0 ? $delta > 0 ? "(SELECT IF(pos > {$delta}, pos - {$delta}, 0) FROM tokens WHERE tf_id=? LIMIT 1)" : "0" : "(SELECT pos FROM tokens WHERE tf_id=? LIMIT 1)";
$q_right = $dir >= 0 ? $delta > 0 ? "(SELECT pos+{$delta} FROM tokens WHERE tf_id=? LIMIT 1)" : "1000" : "(SELECT pos FROM tokens WHERE tf_id=? LIMIT 1)";
$q .= " AND pos BETWEEN {$q_left} AND {$q_right}";
}
$q .= " ORDER BY pos";
$query2 = sql_prepare($q);
}
// how many values should we provide?
$bound = array($tf_id, $tf_id);
if ($delta <= 0) {
if ($dir == 0) {
$bound = array();
} else {
$bound = array($tf_id);
}
}
sql_execute($query2, array_merge(array($sent_id), $bound));
foreach (sql_fetchall($query2) as $r) {
if ($delta > 0) {
if ($left_c == -1) {
$left_c = $r['pos'] == $minpos ? 0 : $r['tf_id'];
}
if ($mw_pos) {
if ($r['pos'] > $mw_pos) {
$right_c = $r['tf_id'];
}
if ($right_c && $r['pos'] == $maxpos) {
$right_c = 0;
}
}
}
if ($include_self || $r['tf_id'] != $tf_id) {
$t[$r['tf_id']] = $r['tf_text'];
}
if ($include_self && $r['tf_id'] == $tf_id) {
$mw_pos = $r['pos'];
}
}
return array('context' => $t, 'mainword' => $tf_id, 'has_left_context' => $left_c, 'has_right_context' => $right_c, 'sentence_id' => $sent_id, 'sentence_text' => $sentence_text, 'book_id' => $book_id);
}
function get_simple_groups_by_complex($group_id)
{
$simple = array();
$frontier = array();
$get_children = "SELECT child_gid FROM anaphora_syntax_groups_complex WHERE parent_gid = ";
$res = sql_query($get_children . $group_id);
$frontier = array_map(function ($row) {
return $row['child_gid'];
}, sql_fetchall($res));
while (!empty($frontier)) {
$gid = array_pop($frontier);
$res = sql_query($get_children . $gid);
$r = sql_fetchall($res);
if ($r) {
foreach ($r as $row) {
$frontier[] = $row['child_gid'];
}
} else {
$simple[] = $gid;
}
}
return $simple;
}
function sql_pe($query, $params)
{
// prepares and executes query, closes cursor
// returns all the rows
$res = sql_prepare($query);
sql_execute($res, $params);
try {
return sql_fetchall($res);
} catch (PDOException $e) {
return array();
}
}
function find_ne_entity($annot_id, $e_id, $e_start_token, $e_length)
{
static $res = NULL;
if ($res == NULL) {
$res = sql_prepare("\n SELECT entity_id\n FROM ne_entities\n WHERE annot_id = ?\n AND start_token = ?\n AND length = ?\n ");
}
sql_execute($res, array($annot_id, $e_start_token, $e_length));
$rows = sql_fetchall($res);
if (sizeof($rows) == 0) {
return false;
}
// check tags
$found_id = $rows[0]['entity_id'];
$tags1 = get_ne_entity_tags($e_id, true);
$tags2 = get_ne_entity_tags($found_id, true);
if ($tags1 == $tags2) {
return $found_id;
}
return false;
}
