/**
* get the instance of this class
* @return object the instance
*/
public static function get_instance()
{
if (!self::$instance) {
self::$instance = new self();
}
return self::$instance;
}
static function get_all($sql = NULL)
{
$table_name = self::table_name();
$worked_table_name = Kanban_Task_Hour::table_name();
$sql = "SELECT tasks.*,\n\t\t\t\tCOALESCE(SUM(worked.hours), 0) 'hour_count'\n\t\t\t\tFROM {$table_name} tasks\n\t\t\t\tLEFT JOIN {$worked_table_name} worked\n\t\t\t\tON tasks.id = worked.task_id\n\t\t\t\tWHERE tasks.is_active = 1\n\t\t\t\tGROUP BY tasks.id\n\t\t;";
$sql = apply_filters('kanban_task_get_all_sql', $sql);
$records = parent::get_all($sql);
foreach ($records as $key => $record) {
$records[$key]->title = Kanban_Utils::str_for_frontend($records[$key]->title);
$records[$key]->description = Kanban_Utils::str_for_frontend($records[$key]->description);
}
return apply_filters('kanban_task_get_all_return', Kanban_Utils::build_array_with_id_keys($records, 'id'));
}
/**
* move users
* move terms (not deleted, as we need them for tasks)
* move projects
* move tasks
* move comments
* delete projects
* delete terms
* clean up
* @return [type] [description]
*/
static function ajax_migrate_records()
{
global $wpdb;
// build response
$response = array('posts_remaining' => Kanban::get_instance()->settings->records_to_move, 'continue' => FALSE, 'done' => FALSE);
// check for users to move
$is_users_moved = get_option('kanban_migrate_users_moved');
if (!$is_users_moved) {
$sql = "SELECT\n\t\t\t\t\t{$wpdb->prefix}options.option_value\n\t\t\t\t\tFROM {$wpdb->prefix}options\n\t\t\t\t\tWHERE `option_name` = 'kanban_user'\n\t\t\t;";
$users = $wpdb->get_var($sql);
if (!empty($users)) {
$users = unserialize($users);
$data = array('name' => 'allowed_users', 'value' => serialize($users['allowed_users']));
Kanban_Option::_replace($data);
delete_option('kanban_user');
}
update_option('kanban_migrate_users_moved', TRUE);
$response['posts_remaining'] = $response['posts_remaining'] - 1;
$response['continue'] = TRUE;
$response['message'] = 'Allowed users moved';
wp_send_json_success($response);
}
// check for terms to move
$is_terms_moved = get_option('kanban_migrate_terms_moved');
if (!$is_terms_moved) {
$sql = "SELECT\n\t\t\t\t\t{$wpdb->prefix}term_taxonomy.`taxonomy`\n\t\t\t\t\t, {$wpdb->prefix}term_taxonomy.`term_taxonomy_id`\n\t\t\t\t\t, {$wpdb->prefix}terms.`term_id`\n\t\t\t\t\t, {$wpdb->prefix}terms.`name`\n\t\t\t\t\t, {$wpdb->prefix}terms.`slug`\n\t\t\t\t\tFROM {$wpdb->prefix}terms\n\t\t\t\t\tJOIN {$wpdb->prefix}term_taxonomy\n\t\t\t\t\tON {$wpdb->prefix}terms.`term_id` = {$wpdb->prefix}term_taxonomy.`term_id`\n\t\t\t;";
$terms = $wpdb->get_results($sql);
// if we need to move terms
if (!empty($terms)) {
// get settings
$kanban_task_status_order = get_option('kanban_task_status_order');
$kanban_task_status_colors = get_option('kanban_task_status_colors');
$kanban_task_estimate_order = get_option('kanban_task_estimate_order');
// get statuses for matching
$status_table = Kanban_Status::table_name();
$sql = "SELECT * FROM {$status_table};";
$statuses = $wpdb->get_results($sql);
$status_arr = array();
foreach ($statuses as $status) {
$status_arr[$status->title] = $status->id;
}
// get estimates for matching
$estimates_table = Kanban_Estimate::table_name();
$sql = "SELECT * FROM {$estimates_table};";
$estimates = $wpdb->get_results($sql);
$estimate_arr = array();
foreach ($estimates as $estimate) {
$estimate_arr[$estimate->title] = $estimate->id;
}
// add each term
foreach ($terms as $term) {
switch ($term->taxonomy) {
case 'kanban_task_status':
if (isset($status_arr[$term->name])) {
continue;
}
$data = array('title' => $term->name, 'color_hex' => $kanban_task_status_colors[$term->term_id], 'position' => $kanban_task_status_order[$term->term_id]);
$success = Kanban_Status::replace($data);
break;
case 'kanban_task_estimate':
if (isset($estimate_arr[$term->name])) {
continue;
}
$data = array('title' => $term->name, 'hours' => $term->slug, 'position' => $kanban_task_estimate_order[$term->term_id]);
$success = Kanban_Estimate::replace($data);
break;
}
}
}
update_option('kanban_migrate_terms_moved', TRUE);
$response['posts_remaining'] = $response['posts_remaining'] - 2;
$response['continue'] = TRUE;
$response['message'] = 'statuses and estimates updated';
wp_send_json_success($response);
}
// is_terms_moved
// move projects
$sql = "SELECT posts.ID as id\n\t\t\t, posts.post_title as title\n\t\t\t, posts.post_modified_gmt as modified_dt_gmt\n\t\t\t, posts.post_date_gmt as created_dt_gmt\n\t\t\t, posts.post_author as user_id_author\n\n\t\t\tFROM {$wpdb->prefix}posts posts\n\n\t\t\tWHERE posts.`post_type` = 'kanban_project'\n\t\t\tAND posts.`post_status` = 'publish'\n\t\t\tLIMIT 1\n\t\t;";
$projects = $wpdb->get_results($sql);
if (!empty($projects)) {
foreach ($projects as $post) {
$data = array('title' => $post->title, 'created_dt_gmt' => $post->created_dt_gmt, 'modified_dt_gmt' => $post->modified_dt_gmt, 'user_id_author' => $post->user_id_author, 'is_active' => 1);
$success = Kanban_Project::replace($data);
if ($success) {
$wpdb->update("{$wpdb->prefix}posts", array('post_status' => 'trash'), array('ID' => $post->id));
}
}
// projects
$response['posts_remaining'] = $response['posts_remaining'] - 3;
$response['continue'] = TRUE;
$response['message'] = sprintf('project %s moved', $post->id);
wp_send_json_success($response);
}
// projects
// move tasks
$sql = "SELECT posts.ID as id\n\t\t\t, posts.post_title as title\n\t\t\t, posts.post_modified_gmt as modified_dt_gmt\n\t\t\t, posts.post_date_gmt as created_dt_gmt\n\t\t\t, posts.post_author as user_id_author\n\t\t\t, p_project_name.post_title as project_name\n\t\t\t, pm_user_id_assigned.meta_value as user_id_assigned\n\t\t\t, pm_work_hour_count.meta_value as work_hour_count\n\n\t\t\tFROM {$wpdb->prefix}posts posts\n\n\t\t\tJOIN {$wpdb->prefix}postmeta pm_project_id\n\t\t\t\tON pm_project_id.post_id = posts.ID\n\t\t\t\tAND pm_project_id.meta_key = 'kanban_task_project_id'\n\n\t\t\tJOIN {$wpdb->prefix}posts p_project_name\n\t\t\t\tON pm_project_id.meta_value = p_project_name.ID\n\n\t\t\tJOIN {$wpdb->prefix}postmeta pm_user_id_assigned\n\t\t\t\tON pm_user_id_assigned.post_id = posts.ID\n\t\t\t\tAND pm_user_id_assigned.meta_key = 'kanban_task_user_id_assigned'\n\n\t\t\tJOIN {$wpdb->prefix}postmeta pm_work_hour_count\n\t\t\t\tON pm_work_hour_count.post_id = posts.ID\n\t\t\t\tAND pm_work_hour_count.meta_key = 'kanban_task_work_hour_count'\n\n\t\t\tWHERE posts.`post_type` = 'kanban_task'\n\t\t\tAND posts.`post_status` = 'publish'\n\t\t\tLIMIT 1\n\t\t;";
$tasks = $wpdb->get_results($sql);
if (!empty($tasks)) {
// get statuses for matching
$status_table = Kanban_Status::table_name();
$sql = "SELECT * FROM {$status_table};";
$statuses = $wpdb->get_results($sql);
$status_arr = array();
foreach ($statuses as $status) {
$status_arr[$status->title] = $status->id;
}
// get estimates for matching
$estimates_table = Kanban_Estimate::table_name();
$sql = "SELECT * FROM {$estimates_table};";
$estimates = $wpdb->get_results($sql);
$estimate_arr = array();
foreach ($estimates as $estimate) {
$estimate_arr[$estimate->title] = $estimate->id;
}
// get projects for matching
$projects_table = Kanban_Project::table_name();
$sql = "SELECT * FROM {$projects_table};";
$projects = $wpdb->get_results($sql);
// build look up array by name
$projects_arr = array();
if (!empty($projects)) {
foreach ($projects as $project) {
$projects_arr[$project->title] = $project->id;
}
}
$estimate_arr = array();
foreach ($estimates as $estimate) {
$estimate_arr[$estimate->title] = $estimate->id;
}
foreach ($tasks as $post) {
// get terms for this task
$sql = "SELECT\n\t\t\t\t\t\t{$wpdb->prefix}terms.name\n\t\t\t\t\t\t, {$wpdb->prefix}term_taxonomy.`taxonomy`\n\t\t\t\t\t\tFROM {$wpdb->prefix}terms\n\t\t\t\t\t\tJOIN {$wpdb->prefix}term_taxonomy\n\t\t\t\t\t\tON {$wpdb->prefix}terms.term_id = {$wpdb->prefix}term_taxonomy.term_id\n\t\t\t\t\t\tJOIN {$wpdb->prefix}term_relationships\n\t\t\t\t\t\tON {$wpdb->prefix}term_taxonomy.term_taxonomy_id = {$wpdb->prefix}term_relationships.term_taxonomy_id\n\t\t\t\t\t\tWHERE {$wpdb->prefix}term_relationships.object_id = %s\n\t\t\t\t;";
$terms = $wpdb->get_results($wpdb->prepare($sql, $post->id));
$terms_arr = array();
foreach ($terms as $term_data) {
$terms_arr[$term_data->taxonomy] = $term_data->name;
}
if (isset($terms_arr['kanban_task_status']) && isset($status_arr[$terms_arr['kanban_task_status']])) {
$status_id = $status_arr[$terms_arr['kanban_task_status']];
} else {
$status_id = 0;
}
if (isset($terms_arr['kanban_task_estimate']) && isset($estimate_arr[$terms_arr['kanban_task_estimate']])) {
$estimate_id = $estimate_arr[$terms_arr['kanban_task_estimate']];
} else {
$estimate_id = 0;
}
if (isset($projects_arr[$post->project_name])) {
$project_id = $projects_arr[$post->project_name];
} else {
$project_id = 0;
}
// build task data to save
$data = array('title' => $post->title, 'created_dt_gmt' => $post->created_dt_gmt, 'modified_dt_gmt' => $post->modified_dt_gmt, 'user_id_author' => $post->user_id_author, 'user_id_assigned' => $post->user_id_assigned, 'status_id' => $status_id, 'project_id' => $project_id, 'estimate_id' => $estimate_id, 'is_active' => 1);
$success = Kanban_Task::replace($data);
$task_id = self::_insert_id();
if ($success) {
$wpdb->update("{$wpdb->prefix}posts", array('post_status' => 'trash'), array('ID' => $post->id));
}
$response['message'] = sprintf('task %s moved', $post->id);
// get comments for task
$sql = "SELECT *\n\t\t\t\t\t\tFROM {$wpdb->prefix}posts posts\n\t\t\t\t\t\tWHERE posts.`post_type` = 'kanban_task_comment'\n\t\t\t\t\t\tAND posts.`post_parent` = {$post->id}\n\t\t\t\t\t\tAND posts.`post_status` = 'publish'\n\t\t\t\t\t\t;";
$comments = $wpdb->get_results($sql);
if (count($comments) > 0) {
foreach ($comments as $comment) {
$data = array('description' => $comment->post_content, 'created_dt_gmt' => $comment->post_date_gmt, 'modified_dt_gmt' => $comment->post_modified_gmt, 'comment_type' => 'system', 'task_id' => $task_id, 'user_id_author' => $comment->post_author);
$success = Kanban_Comment::insert($data);
// mark as trash
if ($success) {
$wpdb->update("{$wpdb->prefix}posts", array('post_status' => 'trash'), array('ID' => $comment->ID));
}
// add task hour
if (strpos($comment->post_content, 'hour of work') !== FALSE) {
$data = array('task_id' => $task_id, 'created_dt_gmt' => $comment->post_date_gmt, 'hours' => 1, 'status_id' => $status_id, 'user_id_author' => $comment->post_author, 'user_id_worked' => $comment->post_author);
$success = Kanban_Task_Hour::insert($data);
}
}
$response['message'] .= sprintf('. %s comments moved', count($comments));
}
// $comments
}
// $tasks
$response['posts_remaining'] = Kanban::get_instance()->settings->records_to_move - 4;
$response['continue'] = TRUE;
wp_send_json_success($response);
}
// task
// $is_posts_deleted = get_option('kanban_migrate_posts_deleted');
// if ( !$is_posts_deleted )
// {
// // delete any orphaned posts
// $success = $wpdb->query("DELETE FROM $wpdb->posts
// WHERE post_type LIKE 'kanban%'
// OR post_type LIKE 'kbwp%'
// ;"
// );
// update_option('kanban_migrate_posts_deleted', TRUE);
// $response['posts_remaining'] = Kanban::get_instance()->settings->records_to_move;
// $response['continue'] = TRUE;
// $response['message'] = sprintf('%s posts cleaned up', count($success));
// wp_send_json_success($response);
// }
// // delete terms
// $sql = "SELECT
// {$wpdb->prefix}term_taxonomy.`taxonomy`
// , {$wpdb->prefix}term_taxonomy.`term_taxonomy_id`
// , {$wpdb->prefix}terms.`term_id`
// , {$wpdb->prefix}terms.`name`
// , {$wpdb->prefix}terms.`slug`
// FROM {$wpdb->prefix}terms
// JOIN {$wpdb->prefix}term_taxonomy
// ON {$wpdb->prefix}terms.`term_id` = {$wpdb->prefix}term_taxonomy.`term_id`
// ;";
// $terms = $wpdb->get_results($sql);
// // if we need to delete terms
// if ( !empty($terms) )
// {
// // add each term
// foreach ($terms as $term)
// {
// $wpdb->delete(
// sprintf('%sterms', $wpdb->prefix),
// array('term_id' => $term->term_id)
// );
// $wpdb->delete(
// sprintf('%sterm_relationships', $wpdb->prefix),
// array('term_taxonomy_id' => $term->term_taxonomy_id)
// );
// $wpdb->delete(
// sprintf('%sterm_taxonomy', $wpdb->prefix),
// array('term_taxonomy_id' => $term->term_taxonomy_id)
// );
// }
// $response['continue'] = TRUE;
// $response['message'] = 'statuses and estimates removed';
// wp_send_json_success($response);
// }
// cleanup records
$wpdb->update("{$wpdb->prefix}posts", array('post_status' => 'trash'), array('post_status' => 'publish', 'post_type' => 'kanban_task'));
$wpdb->update("{$wpdb->prefix}posts", array('post_status' => 'trash'), array('post_status' => 'publish', 'post_type' => 'kanban_project'));
$wpdb->update("{$wpdb->prefix}posts", array('post_status' => 'trash'), array('post_status' => 'publish', 'post_type' => 'kanban_task_comment'));
// clean up temp values
delete_option('kanban_migrate_users_moved');
delete_option('kanban_migrate_terms_moved');
delete_option('kanban_migrate_posts_deleted');
// clean up old data
// delete_option('kanban_task_status_order');
// delete_option('kanban_task_status_colors');
// delete_option('kanban_task_estimate_order');
Kanban_Db::add_defaults();
$response['done'] = TRUE;
wp_send_json_success($response);
}