public function for_translation($project, $translation_set, $page, $filters = array(), $sort = array())
{
global $wpdb;
$locale = GP_Locales::by_slug($translation_set->locale);
$join_type = 'INNER';
$sort_bys = wp_list_pluck(gp_get_sort_by_fields(), 'sql_sort_by');
$default_sort = get_user_option('gp_default_sort');
if (!is_array($default_sort)) {
$default_sort = array('by' => 'priority', 'how' => 'desc');
}
$sort_by = gp_array_get($sort_bys, gp_array_get($sort, 'by'), gp_array_get($sort_bys, $default_sort['by']));
$sort_hows = array('asc' => 'ASC', 'desc' => 'DESC');
$sort_how = gp_array_get($sort_hows, gp_array_get($sort, 'how'), gp_array_get($sort_hows, $default_sort['how']));
$collation = 'yes' === gp_array_get($filters, 'case_sensitive') ? 'BINARY' : '';
$where = array();
if (gp_array_get($filters, 'term')) {
$like = "LIKE {$collation} '%" . esc_sql($wpdb->esc_like(gp_array_get($filters, 'term'))) . "%'";
$where[] = '(' . implode(' OR ', array_map(function ($x) use($like) {
return "({$x} {$like})";
}, array('o.singular', 't.translation_0', 'o.plural', 't.translation_1', 'o.context', 'o.references'))) . ')';
}
if (gp_array_get($filters, 'before_date_added')) {
$where[] = $wpdb->prepare('t.date_added > %s', gp_array_get($filters, 'before_date_added'));
}
if (gp_array_get($filters, 'translation_id')) {
$where[] = $wpdb->prepare('t.id = %d', gp_array_get($filters, 'translation_id'));
}
if (gp_array_get($filters, 'original_id')) {
$where[] = $wpdb->prepare('o.id = %d', gp_array_get($filters, 'original_id'));
}
if ('yes' == gp_array_get($filters, 'warnings')) {
$where[] = 't.warnings IS NOT NULL';
$where[] = 't.warnings != ""';
} elseif ('no' == gp_array_get($filters, 'warnings')) {
$where[] = 't.warnings IS NULL';
}
if ('yes' == gp_array_get($filters, 'with_context')) {
$where[] = 'o.context IS NOT NULL';
}
if ('yes' == gp_array_get($filters, 'with_comment')) {
$where[] = 'o.comment IS NOT NULL AND o.comment <> ""';
}
if (gp_array_get($filters, 'user_login')) {
$user = get_user_by('login', $filters['user_login']);
// do not return any entries if the user doesn't exist
$where[] = $wpdb->prepare('t.user_id = %d', $user && $user->ID ? $user->ID : -1);
}
if (!GP::$permission->current_user_can('write', 'project', $project->id)) {
$where[] = 'o.priority > -2';
}
$priorities = gp_array_get($filters, 'priority');
if ($priorities) {
$valid_priorities = array_keys(GP::$original->get_static('priorities'));
$priorities = array_filter(gp_array_get($filters, 'priority'), function ($p) use($valid_priorities) {
return in_array($p, $valid_priorities, true);
});
$priorities_where = array();
foreach ($priorities as $single_priority) {
$priorities_where[] = $wpdb->prepare('o.priority = %s', $single_priority);
}
if (!empty($priorities_where)) {
$priorities_where = '(' . implode(' OR ', $priorities_where) . ')';
$where[] = $priorities_where;
}
}
$join_where = array();
$status = gp_array_get($filters, 'status', 'current_or_waiting_or_fuzzy_or_untranslated');
$statuses = explode('_or_', $status);
if (in_array('untranslated', $statuses)) {
if ($statuses == array('untranslated')) {
$where[] = 't.translation_0 IS NULL';
}
$join_type = 'LEFT';
$join_where[] = 't.status != "rejected"';
$join_where[] = 't.status != "old"';
$statuses = array_filter($statuses, function ($x) {
return $x != 'untranslated';
});
}
$all_statuses = $this->get_static('statuses');
$statuses = array_filter($statuses, function ($s) use($all_statuses) {
return in_array($s, $all_statuses);
});
if (!empty($statuses)) {
$statuses_where = array();
foreach ($statuses as $single_status) {
$statuses_where[] = $wpdb->prepare('t.status = %s', $single_status);
}
$statuses_where = '(' . implode(' OR ', $statuses_where) . ')';
$join_where[] = $statuses_where;
}
/**
* Filter the SQL WHERE clause to get available translations.
*
* @since 1.0.0
*
* @param array $where An array of where conditions.
* @param GP_Translation_Set $translation_set Current translation set.
*/
$where = apply_filters('gp_for_translation_where', $where, $translation_set);
$where = implode(' AND ', $where);
if ($where) {
$where = 'AND ' . $where;
}
$join_where = implode(' AND ', $join_where);
if ($join_where) {
$join_where = 'AND ' . $join_where;
}
$sql_sort = sprintf($sort_by, $sort_how);
$limit = $this->sql_limit_for_paging($page, $this->per_page);
$sql_for_translations = "\n\t\t\tSELECT SQL_CALC_FOUND_ROWS t.*, o.*, t.id as id, o.id as original_id, t.status as translation_status, o.status as original_status, t.date_added as translation_added, o.date_added as original_added\n\t\t\tFROM {$wpdb->gp_originals} as o\n\t\t\t{$join_type} JOIN {$wpdb->gp_translations} AS t ON o.id = t.original_id AND t.translation_set_id = " . (int) $translation_set->id . " {$join_where}\n\t\t\tWHERE o.project_id = " . (int) $project->id . " AND o.status = '+active' {$where} ORDER BY {$sql_sort} {$limit}";
$rows = $this->many_no_map($sql_for_translations);
$this->found_rows = $this->found_rows();
$translations = array();
foreach ((array) $rows as $row) {
$row->user = $row->user_last_modified = null;
if ($row->user_id && 'no-limit' !== $this->per_page) {
$user = get_userdata($row->user_id);
if ($user) {
$row->user = (object) array('ID' => $user->ID, 'user_login' => $user->user_login, 'display_name' => $user->display_name, 'user_nicename' => $user->user_nicename);
}
}
if ($row->user_id_last_modified && 'no-limit' !== $this->per_page) {
$user = get_userdata($row->user_id_last_modified);
if ($user) {
$row->user_last_modified = (object) array('ID' => $user->ID, 'user_login' => $user->user_login, 'display_name' => $user->display_name, 'user_nicename' => $user->user_nicename);
}
}
$row->translations = array();
for ($i = 0; $i < $locale->nplurals; $i++) {
$row->translations[] = $row->{"translation_" . $i};
}
$row->references = preg_split('/\\s+/', $row->references, -1, PREG_SPLIT_NO_EMPTY);
$row->extracted_comments = $row->comment;
$row->warnings = $row->warnings ? maybe_unserialize($row->warnings) : null;
unset($row->comment);
// Reduce range by one since we're starting at 0, see GH#516.
foreach (range(0, $this->get_static('number_of_plural_translations') - 1) as $i) {
$member = "translation_{$i}";
unset($row->{$member});
}
$row->row_id = $row->original_id . ($row->id ? "-{$row->id}" : '');
$translations[] = new Translation_Entry((array) $row);
}
unset($rows);
return $translations;
}