function get_translation_jobs($args = array())
{
global $wpdb, $sitepress, $wp_query;
// defaults
$args_default = array('translator_id' => 0, 'status' => false, 'include_unassigned' => false);
extract($args_default);
extract($args, EXTR_OVERWRITE);
$_exp = explode('-', $translator_id);
$service = isset($_exp[1]) ? $_exp[1] : 'local';
$translator_id = $_exp[0];
$where = " s.status > " . ICL_TM_NOT_TRANSLATED;
if ($status != '') {
$where .= " AND s.status=" . intval($status);
}
if (!empty($translator_id)) {
if ($include_unassigned) {
$where .= " AND (j.translator_id=" . intval($translator_id) . " OR j.translator_id=0) ";
} else {
$where .= " AND j.translator_id=" . intval($translator_id);
}
if (!empty($service)) {
$where .= " AND s.translation_service='{$service}'";
}
}
if (!empty($from)) {
$where .= " AND t.source_language_code='" . $wpdb->escape($from) . "'";
}
if (!empty($to)) {
$where .= " AND t.language_code='" . $wpdb->escape($to) . "'";
}
// ORDER BY
if ($include_unassigned) {
$orderby[] = 'j.translator_id DESC';
}
$orderby[] = ' j.job_id DESC ';
$orderby = join(', ', $orderby);
// LIMIT
if (!isset($_GET['paged'])) {
$_GET['paged'] = 1;
}
$offset = ($_GET['paged'] - 1) * $limit_no;
$limit = " " . $offset . ',' . $limit_no;
$jobs = $wpdb->get_results("SELECT SQL_CALC_FOUND_ROWS \n j.job_id, t.trid, t.language_code, t.source_language_code, \n s.translation_id, s.status, s.needs_update, s.translator_id, u.display_name AS translator_name, s.translation_service \n FROM {$wpdb->prefix}icl_translate_job j\n JOIN {$wpdb->prefix}icl_translation_status s ON j.rid = s.rid\n JOIN {$wpdb->prefix}icl_translations t ON s.translation_id = t.translation_id\n LEFT JOIN {$wpdb->users} u ON s.translator_id = u.ID\n WHERE {$where} AND revision IS NULL\n ORDER BY {$orderby}\n LIMIT {$limit}\n ");
$count = $wpdb->get_var("SELECT FOUND_ROWS()");
$wp_query->found_posts = $count;
$wp_query->query_vars['posts_per_page'] = $limit_no;
$wp_query->max_num_pages = ceil($wp_query->found_posts / $limit_no);
foreach ($jobs as $k => $row) {
//original
$post_id = $wpdb->get_var($wpdb->prepare("\n SELECT field_data\n FROM {$wpdb->prefix}icl_translate\n WHERE job_id=%d and field_type='original_id'", $row->job_id));
$parts = explode('_', $post_id);
if ($parts[0] == 'external') {
$jobs[$k]->original_doc_id = $post_id;
$jobs[$k]->post_title = base64_decode($wpdb->get_var($wpdb->prepare("\n SELECT field_data\n FROM {$wpdb->prefix}icl_translate\n WHERE job_id=%d and field_type='name'", $row->job_id)));
if ($jobs[$k]->post_title == "") {
// try the title field.
$jobs[$k]->post_title = base64_decode($wpdb->get_var($wpdb->prepare("\n SELECT field_data\n FROM {$wpdb->prefix}icl_translate\n WHERE job_id=%d and field_type='title'", $row->job_id)));
}
$jobs[$k]->edit_link = $this->tm_post_link($post_id);
$ldf = $sitepress->get_language_details($row->source_language_code);
} else {
$doc = $wpdb->get_row($wpdb->prepare("SELECT ID, post_title, post_type FROM {$wpdb->posts} WHERE ID=%d", $post_id));
if ($doc) {
$jobs[$k]->post_title = $doc->post_title;
$jobs[$k]->original_doc_id = $doc->ID;
$jobs[$k]->edit_link = get_edit_post_link($doc->ID);
$ldf = $sitepress->get_language_details($sitepress->get_element_language_details($post_id, 'post_' . $doc->post_type)->language_code);
} else {
$jobs[$k]->post_title = __("The original has been deleted!", "sitepress");
$jobs[$k]->original_doc_id = 0;
$jobs[$k]->edit_link = "";
$ldf['display_name'] = __("Deleted", "sitepress");
}
}
$ldt = $sitepress->get_language_details($row->language_code);
$jobs[$k]->lang_text = $ldf['display_name'] . ' » ' . $ldt['display_name'];
if ($row->translation_service == 'icanlocalize') {
$row->translator_name = ICL_Pro_Translation::get_translator_name($row->translator_id);
}
}
return $jobs;
}
function get_translation_jobs($args = array())
{
global $wpdb, $sitepress, $wp_query;
// defaults
/** @var $translator_id int */
/** @var $status int */
/** @var $include_unassigned bool */
/** @var $orderby bool|string */
/** @var $limit_no int */
$args_default = array('translator_id' => 0, 'status' => false, 'include_unassigned' => false);
extract($args_default);
extract($args, EXTR_OVERWRITE);
$language_pairs = array();
$_exp = explode('-', $translator_id);
$service = isset($_exp[1]) ? $_exp[1] : 'local';
$translator_id = $_exp[0];
$where = " s.status > " . ICL_TM_NOT_TRANSLATED;
if ($status != '') {
$where .= " AND s.status=" . intval($status);
}
if ($status != ICL_TM_DUPLICATE) {
$where .= " AND s.status <> " . ICL_TM_DUPLICATE;
}
if (!empty($translator_id)) {
if ($include_unassigned) {
$where .= " AND (j.translator_id=" . intval($translator_id) . " OR j.translator_id=0) ";
} else {
$where .= " AND j.translator_id=" . intval($translator_id);
}
if (!empty($service)) {
$where .= " AND s.translation_service='{$service}'";
}
$language_pairs = get_user_meta($translator_id, $wpdb->prefix . 'language_pairs', true);
}
// HANDLE FROM
if (!empty($from)) {
$where .= PHP_EOL . " AND t.source_language_code='" . esc_sql($from) . "'";
} else {
// only if we filter by translator, make sure to use just the 'from' languages that apply
// in no translator_id, ommit condition and all will be pulled
if ($translator_id) {
if (!empty($to)) {
// get 'from' languages corresdonding to $to (to $translator_id)
$from_languages = array();
foreach ($language_pairs as $fl => $tls) {
if (isset($tls[$to])) {
$from_languages[] = $fl;
}
}
if ($from_languages) {
$where .= PHP_EOL . sprintf(" AND t.source_language_code IN(%s)", "'" . join("','", $from_languages) . "'");
}
} else {
// all to all case
// get all possible combinations for $translator_id
$from_languages = array_keys($language_pairs);
$where_conditions = array();
foreach ($from_languages as $fl) {
$to_languages = "'" . join("','", array_keys($language_pairs[$fl])) . "'";
$where_conditions[] = sprintf(" (t.source_language_code='%s' AND t.language_code IN (%s)) ", $fl, $to_languages);
}
if (!empty($where_conditions)) {
$where .= PHP_EOL . ' AND ( ' . join(' OR ', $where_conditions) . ')';
}
}
}
}
// HANDLE TO
if (!empty($to)) {
$where .= PHP_EOL . " AND t.language_code='" . esc_sql($to) . "'";
} else {
// only if we filter by translator, make sure to use just the 'from' languages that apply
// in no translator_id, omit condition and all will be pulled
if ($translator_id) {
if (!empty($from)) {
// get languages the user can translate into from $from
$tos = isset($language_pairs[$from]) ? array_keys($language_pairs[$from]) : array();
if ($tos) {
$where .= PHP_EOL . sprintf(" AND t.language_code IN(%s)", "'" . join("','", $tos) . "'");
}
} else {
// covered by 'all to all case' above
}
}
}
// ORDER BY
if ($include_unassigned) {
$orderby[] = 'j.translator_id DESC';
}
$orderby[] = ' j.job_id DESC ';
$orderby = join(', ', $orderby);
// LIMIT
if (!isset($_GET['paged'])) {
$_GET['paged'] = 1;
}
$offset = ($_GET['paged'] - 1) * $limit_no;
$limit = " " . $offset . ',' . $limit_no;
$jobs = $wpdb->get_results("SELECT SQL_CALC_FOUND_ROWS\n\t\t\t\tj.job_id, t.trid, t.language_code, t.source_language_code,\n\t\t\t\ts.translation_id, s.status, s.needs_update, s.translator_id, u.display_name AS translator_name, s.translation_service\n\t\t\t\tFROM {$wpdb->prefix}icl_translate_job j\n\t\t\t\t\tJOIN {$wpdb->prefix}icl_translation_status s ON j.rid = s.rid\n\t\t\t\t\tJOIN {$wpdb->prefix}icl_translations t ON s.translation_id = t.translation_id\n\t\t\t\t\tLEFT JOIN {$wpdb->users} u ON s.translator_id = u.ID\n\t\t\t\tWHERE {$where} AND revision IS NULL\n\t\t\t\tORDER BY {$orderby}\n\t\t\t\tLIMIT {$limit}\n\t\t\t");
//echo '<pre>';
//print_r($wpdb->last_query);
//echo '</pre>';
$count = $wpdb->get_var("SELECT FOUND_ROWS()");
$wp_query->found_posts = $count;
$wp_query->query_vars['posts_per_page'] = $limit_no;
$wp_query->max_num_pages = ceil($wp_query->found_posts / $limit_no);
foreach ($jobs as $k => $row) {
//original
$post_id = $wpdb->get_var($wpdb->prepare("\n\t\t\t\t\t\t\t\t\t\t\t\t\t SELECT field_data\n\t\t\t\t\t\t\t\t\t\t\t\t\t FROM {$wpdb->prefix}icl_translate\n\t\t\t\t\t\t\t\t\t\t\t\t\t WHERE job_id=%d and field_type='original_id'", $row->job_id));
$parts = explode('_', $post_id);
if ($parts[0] == 'external') {
$jobs[$k]->original_doc_id = $post_id;
$jobs[$k]->post_title = base64_decode($wpdb->get_var($wpdb->prepare("\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t SELECT field_data\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t FROM {$wpdb->prefix}icl_translate\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t WHERE job_id=%d and field_type='name'", $row->job_id)));
if ($jobs[$k]->post_title == "") {
// try the title field.
$jobs[$k]->post_title = base64_decode($wpdb->get_var($wpdb->prepare("\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t SELECT field_data\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t FROM {$wpdb->prefix}icl_translate\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t WHERE job_id=%d and field_type='title'", $row->job_id)));
}
$jobs[$k]->post_title = apply_filters('WPML_translation_job_title', $jobs[$k]->post_title, $post_id);
$jobs[$k]->edit_link = self::tm_post_link($post_id);
$ldf = $sitepress->get_language_details($row->source_language_code);
} else {
$doc = $wpdb->get_row($wpdb->prepare("SELECT ID, post_title, post_type FROM {$wpdb->posts} WHERE ID=%d", $post_id));
if ($doc) {
$jobs[$k]->post_title = $doc->post_title;
$jobs[$k]->original_doc_id = $doc->ID;
$jobs[$k]->edit_link = get_edit_post_link($doc->ID);
$ldf = $sitepress->get_language_details($sitepress->get_element_language_details($post_id, 'post_' . $doc->post_type)->language_code);
} else {
$jobs[$k]->post_title = __("The original has been deleted!", "sitepress");
$jobs[$k]->original_doc_id = 0;
$jobs[$k]->edit_link = "";
$ldf['display_name'] = __("Deleted", "sitepress");
}
}
$ldt = $sitepress->get_language_details($row->language_code);
$jobs[$k]->lang_text = $ldf['display_name'] . ' » ' . $ldt['display_name'];
if ($row->translation_service == 'icanlocalize') {
$row->translator_name = ICL_Pro_Translation::get_translator_name($row->translator_id);
}
}
return $jobs;
}
function get_translation_jobs($args = array())
{
global $wpdb, $sitepress, $wp_query;
// defaults
$args_default = array('translator_id' => 0, 'status' => false, 'include_unassigned' => false);
extract($args_default);
extract($args, EXTR_OVERWRITE);
$_exp = explode('-', $translator_id);
$service = isset($_exp[1]) ? $_exp[1] : 'local';
$translator_id = $_exp[0];
$where = "1";
if ($status != '') {
$where .= " AND s.status=" . intval($status);
}
if (!empty($translator_id)) {
if ($include_unassigned) {
$where .= " AND (j.translator_id=" . intval($translator_id) . " OR j.translator_id=0) ";
} else {
$where .= " AND j.translator_id=" . intval($translator_id);
}
if (!empty($service)) {
$where .= " AND s.translation_service='{$service}'";
}
}
if (!empty($from)) {
$where .= " AND t.source_language_code='" . $wpdb->escape($from) . "'";
}
if (!empty($to)) {
$where .= " AND t.language_code='" . $wpdb->escape($to) . "'";
}
// ORDER BY
if ($include_unassigned) {
$orderby[] = 'j.translator_id DESC';
}
$orderby[] = ' j.job_id DESC ';
$orderby = join(', ', $orderby);
// LIMIT
if (!isset($_GET['paged'])) {
$_GET['paged'] = 1;
}
$offset = ($_GET['paged'] - 1) * $limit_no;
$limit = " " . $offset . ',' . $limit_no;
$jobs = $wpdb->get_results("SELECT SQL_CALC_FOUND_ROWS \r\n j.job_id, t.trid, t.language_code, t.source_language_code, s.status, s.needs_update, s.translator_id, u.display_name AS translator_name, s.translation_service \r\n FROM {$wpdb->prefix}icl_translate_job j\r\n JOIN {$wpdb->prefix}icl_translation_status s ON j.rid = s.rid\r\n JOIN {$wpdb->prefix}icl_translations t ON s.translation_id = t.translation_id\r\n LEFT JOIN {$wpdb->users} u ON s.translator_id = u.ID\r\n WHERE {$where} AND revision IS NULL\r\n ORDER BY {$orderby}\r\n LIMIT {$limit}\r\n ");
$count = $wpdb->get_var("SELECT FOUND_ROWS()");
$wp_query->found_posts = $count;
$wp_query->query_vars['posts_per_page'] = $limit_no;
$wp_query->max_num_pages = ceil($wp_query->found_posts / $limit_no);
foreach ($jobs as $k => $row) {
//original
$doc = $wpdb->get_row($wpdb->prepare("\r\n SELECT p.ID, p.post_title \r\n FROM {$wpdb->prefix}icl_translations t \r\n JOIN {$wpdb->posts} p ON p.ID = t.element_id\r\n WHERE t.trid = %d AND t.language_code = '%s'", $row->trid, $row->source_language_code));
$jobs[$k]->post_title = $doc->post_title;
$jobs[$k]->edit_link = get_edit_post_link($doc->ID);
$ldt = $sitepress->get_language_details($row->language_code);
$ldf = $sitepress->get_language_details($row->source_language_code);
$jobs[$k]->lang_text = $ldf['display_name'] . ' » ' . $ldt['display_name'];
if ($row->translation_service == 'icanlocalize') {
$row->translator_name = ICL_Pro_Translation::get_translator_name($row->translator_id);
}
}
return $jobs;
}
