/**
* Common method to handle POST method
*
* @param string $r_resource_cmd URL
* @param array $r_resource_vars Array generated from URL
* @param array $r_resource_filters Array generated from URL query string
* @param array $r_post Post data
*
* @return mixed
*/
function r_post($r_resource_cmd, $r_resource_vars, $r_resource_filters, $r_post)
{
global $r_debug, $db_lnk, $authUser, $thumbsizes, $_server_domain_url;
$emailFindReplace = $response = array();
$fields = 'created, modified';
$values = 'now(), now()';
$json = $sql = $is_return_vlaue = false;
$uuid = '';
if (isset($r_post['uuid'])) {
$uuid = $r_post['uuid'];
}
unset($r_post['temp_id']);
unset($r_post['uuid']);
unset($r_post['id']);
switch ($r_resource_cmd) {
case '/users/bulk_action':
$user_ids = array();
$r_post = json_encode($r_post[0]);
$post_arr = json_decode($r_post, true);
$action_id = $post_arr['action_id']['action_id'];
unset($post_arr['action_id']);
$user_ids = $post_arr;
if ($action_id == 1) {
foreach ($user_ids as $user_id) {
$data = array(0, $user_id['user_id']);
pg_query_params($db_lnk, 'UPDATE users SET is_active = $1 WHERE id = $2', $data);
}
$response = array('success' => 'Checked users are blocked successfully.');
} else {
if ($action_id == 2) {
foreach ($user_ids as $user_id) {
$data = array(1, $user_id['user_id']);
pg_query_params($db_lnk, 'UPDATE users SET is_active = $1 WHERE id = $2', $data);
}
$response = array('success' => 'Checked users are unblocked successfully.');
} else {
if ($action_id == 3) {
foreach ($user_ids as $user_id) {
$conditions = array($user_id['user_id']);
pg_query_params($db_lnk, 'DELETE FROM users WHERE id= $1', $conditions);
}
$response = array('success' => 'Checked users are deleted successfully.');
}
}
}
break;
case '/boards/bulk_action':
$board_ids = array();
$r_post = json_encode($r_post[0]);
$post_arr = json_decode($r_post, true);
$action_id = $post_arr['action_id']['action_id'];
unset($post_arr['action_id']);
$board_ids = $post_arr;
if ($action_id == 1) {
foreach ($board_ids as $board_id) {
$data = array(1, $board_id['board_id']);
pg_query_params($db_lnk, 'UPDATE boards SET is_closed = $1 WHERE id = $2', $data);
}
$response = array('success' => 'Checked boards are closed successfully.');
} else {
if ($action_id == 2) {
foreach ($board_ids as $board_id) {
$data = array(0, $board_id['board_id']);
pg_query_params($db_lnk, 'UPDATE boards SET is_closed = $1 WHERE id = $2', $data);
}
$response = array('success' => 'Checked boards are reopened successfully.');
} else {
if ($action_id == 3) {
foreach ($board_ids as $board_id) {
$conditions = array($board_id['board_id']);
pg_query_params($db_lnk, 'DELETE FROM boards WHERE id= $1', $conditions);
}
$response = array('success' => 'Checked boards are deleted successfully.');
}
}
}
break;
case '/users/forgotpassword':
//users forgot password
$val_arr = array($r_post['email']);
$user = executeQuery('SELECT * FROM users WHERE email = $1 AND is_active = true', $val_arr);
if ($user) {
$password = uniqid();
$val_arr = array(getCryptHash($password), $user['id']);
pg_query_params($db_lnk, 'UPDATE users SET (password) = ($1) WHERE id = $2', $val_arr);
$emailFindReplace = array('##NAME##' => $user['full_name'], '##PASSWORD##' => $password);
$response = array('success' => 'An email has been sent with your new password.');
sendMail('forgetpassword', $emailFindReplace, $user['email']);
} else {
$response = array('error' => 'No matching email id is found in the database.');
}
break;
case '/users':
//Admin user add
$table_name = 'users';
$val_arr = array($r_post['username'], $r_post['email']);
$user = executeQuery('SELECT * FROM users WHERE username = $1 OR email = $2', $val_arr);
if (!$user) {
$sql = true;
$table_name = 'users';
$r_post['password'] = getCryptHash($r_post['password']);
$r_post['role_id'] = 2;
// user
$r_post['is_active'] = true;
$r_post['is_email_confirmed'] = true;
$r_post['role_id'] = 2;
// user
$r_post['initials'] = strtoupper(substr($r_post['username'], 0, 1));
$r_post['ip_id'] = saveIp();
$r_post['full_name'] = email2name($r_post['email']);
} else {
$msg = '';
if ($user['email'] == $r_post['email']) {
$msg = 1;
} else {
if ($user['username'] == $r_post['username']) {
$msg = 2;
}
}
$response = array('error' => $msg);
}
break;
case '/users/register':
//users register
$table_name = 'users';
$val_arr = array($r_post['username'], $r_post['email']);
$user = executeQuery('SELECT * FROM users WHERE (username = $1 AND username<>\'\') OR (email = $2 AND email<>\'\')', $val_arr);
if (!$user) {
$sql = true;
$table_name = 'users';
$r_post['password'] = getCryptHash($r_post['password']);
$r_post['role_id'] = 2;
// user
$r_post['initials'] = strtoupper(substr($r_post['username'], 0, 1));
$r_post['ip_id'] = saveIp();
$r_post['full_name'] = $r_post['email'] == '' ? $r_post['username'] : email2name($r_post['email']);
} else {
$msg = '';
if ($user['email'] == $r_post['email']) {
$msg = 1;
} else {
if ($user['username'] == $r_post['username']) {
$msg = 2;
}
}
$response = array('error' => $msg);
}
break;
case '/users/login':
//users login
$table_name = 'users';
$val_arr = array($r_post['email']);
$log_user = executeQuery('SELECT id, role_id, password, is_ldap::boolean::int FROM users WHERE email = $1 or username = $1', $val_arr);
if (LDAP_LOGIN_ENABLED && (empty($log_user) || !empty($log_user) && $log_user['is_ldap'] == 1)) {
$check_user = ldapAuthenticate($r_post['email'], $r_post['password']);
if (is_array($check_user) && !empty($check_user['User']) && $check_user['User']['is_username_exits'] && $check_user['User']['is_password_matched'] && isset($check_user['User']['email']) && !empty($check_user['User']['email'])) {
$val_arr = array($check_user['User']['email']);
$user = executeQuery('SELECT * FROM users_listing WHERE email = $1', $val_arr);
if (!$user) {
$r_post['password'] = getCryptHash($r_post['password']);
$r_post['role_id'] = 2;
// user
preg_match_all('/\\b\\w/', $check_user['User']['first_name'], $match);
$val_arr = array($r_post['email'], $check_user['User']['email'], $r_post['password'], $check_user['User']['first_name'], strtoupper(implode($match[0])));
$result = pg_query_params($db_lnk, 'INSERT INTO ' . $table_name . ' (created, modified, role_id, username, email, password, full_name, initials, is_active, is_email_confirmed, is_ldap) VALUES (now(), now(), 2, $1, $2, $3, $4, $5, true, true, true) RETURNING * ', $val_arr);
$user = pg_fetch_assoc($result);
$val_arr = array($user['id']);
$user = executeQuery('SELECT * FROM users_listing WHERE id = $1', $val_arr);
}
} else {
$ldap_error = $check_user;
}
} else {
if (STANDARD_LOGIN_ENABLED && !empty($log_user) && $log_user['is_ldap'] == 0) {
$r_post['password'] = crypt($r_post['password'], $log_user['password']);
$val_arr = array($r_post['email'], $r_post['password'], 1);
$user = executeQuery('SELECT * FROM users_listing WHERE (email = $1 or username = $1) AND password = $2 AND is_active = $3', $val_arr);
}
}
if (!empty($user)) {
if (LDAP_LOGIN_ENABLED) {
$login_type_id = 1;
} else {
$login_type_id = 2;
}
$last_login_ip_id = saveIp();
$val_arr = array($login_type_id, $last_login_ip_id, $user['id']);
pg_query_params($db_lnk, 'UPDATE users SET last_login_date = now(), login_type_id = $1, last_login_ip_id = $2 WHERE id = $3', $val_arr);
unset($user['password']);
$user_agent = !empty($_SERVER['HTTP_USER_AGENT']) ? $_SERVER['HTTP_USER_AGENT'] : '';
$val_arr = array($user['id'], $last_login_ip_id, $user_agent);
pg_query_params($db_lnk, 'INSERT INTO user_logins (created, modified, user_id, ip_id, user_agent) VALUES (now(), now(), $1, $2, $3)', $val_arr);
$role_val_arr = array($user['role_id']);
$role_links = executeQuery('SELECT * FROM role_links_listing WHERE id = $1', $role_val_arr);
$post_val = array('grant_type' => 'password', 'username' => $user['username'], 'password' => $r_post['password'], 'client_id' => OAUTH_CLIENTID, 'client_secret' => OAUTH_CLIENT_SECRET, 'scope' => 'read write');
$response = getToken($post_val);
$response = array_merge($role_links, $response);
$board_ids = array();
if (!empty($user['boards_users'])) {
$boards_users = json_decode($user['boards_users'], true);
foreach ($boards_users as $boards_user) {
$board_ids[] = $boards_user['board_id'];
}
}
$notify_val_arr = array($user['last_activity_id'], '{' . implode(',', $board_ids) . '}');
$notify_count = executeQuery('SELECT max(id) AS last_activity_id, count(a.*) AS notify_count FROM activities a WHERE a.id > $1 AND board_id = ANY ($2) ', $notify_val_arr);
$notify_count['last_activity_id'] = !empty($notify_count['last_activity_id']) ? $notify_count['last_activity_id'] : $user['last_activity_id'];
$user = array_merge($user, $notify_count);
$response['user'] = $user;
$response['user']['organizations'] = json_decode($user['organizations'], true);
} else {
if (!empty($ldap_error)) {
$response = array('code' => 'LDAP', 'error' => $ldap_error);
} else {
$response = array('code' => 'email', 'error' => 'Sorry, login failed. Either your username or password are incorrect or admin deactivated your account.');
}
}
break;
case '/users/?/changepassword':
$qry_val_array = array($r_resource_vars['users']);
if ($r_post['confirm_password'] == $r_post['password']) {
$user = executeQuery('SELECT * FROM users WHERE id = $1', $qry_val_array);
if ($user) {
$cry_old_pass = crypt($r_post['old_password'], $user['password']);
if ($authUser['role_id'] == 2 && $user['password'] == $cry_old_pass || $authUser['role_id'] == 1) {
$res_val_arr = array(getCryptHash($r_post['password']), $r_resource_vars['users']);
$result = pg_query_params($db_lnk, 'UPDATE users SET (password) = ($1) WHERE id = $2', $res_val_arr);
$conditions = array($authUser['username']);
pg_query_params($db_lnk, 'DELETE FROM oauth_access_tokens WHERE user_id= $1', $conditions);
pg_query_params($db_lnk, 'DELETE FROM oauth_refresh_tokens WHERE user_id= $1', $conditions);
if ($authUser['role_id'] == 1) {
$emailFindReplace = array('##PASSWORD##' => $r_post['password']);
sendMail('changepassword', $emailFindReplace, $user['email']);
$response = array('success' => 'Password change successfully. Please login.');
}
} else {
$response = array('error' => 1);
}
} else {
$response = array('error' => 2);
}
} else {
$response = array('error' => 3);
}
break;
case '/users/?':
$is_return_vlaue = true;
$profile_picture_path = 'null';
$no_error = true;
if (!empty($_FILES['attachment']['name']) && $_FILES['attachment']['error'] == 0) {
$allowed_ext = array('gif', 'png', 'jpg', 'jpeg', 'bmp');
$filename = $_FILES['attachment']['name'];
$file_ext = pathinfo($filename, PATHINFO_EXTENSION);
if (in_array($file_ext, $allowed_ext)) {
$mediadir = APP_PATH . DIRECTORY_SEPARATOR . 'media' . DIRECTORY_SEPARATOR . 'User' . DIRECTORY_SEPARATOR . $r_resource_vars['users'];
$save_path = 'media' . DIRECTORY_SEPARATOR . 'User' . DIRECTORY_SEPARATOR . $r_resource_vars['users'];
if (!file_exists($mediadir)) {
mkdir($mediadir, 0777, true);
}
$file = $_FILES['attachment'];
$file['name'] = preg_replace('/[^A-Za-z0-9\\-.]/', '', $file['name']);
if (is_uploaded_file($file['tmp_name']) && move_uploaded_file($file['tmp_name'], $mediadir . DIRECTORY_SEPARATOR . $file['name'])) {
$profile_picture_path = $save_path . DIRECTORY_SEPARATOR . $file['name'];
foreach ($thumbsizes['User'] as $key => $value) {
$mediadir = APP_PATH . '/client/img/' . $key . '/User/' . $r_resource_vars['users'];
$list = glob($mediadir . '.*');
@unlink($list[0]);
}
$authUser['profile_picture_path'] = $profile_picture_path;
$response['profile_picture_path'] = $profile_picture_path;
$comment = '##USER_NAME## updated the profile image';
$foreign_ids['user_id'] = $r_resource_vars['users'];
$response['activity'] = insertActivity($r_resource_vars['users'], $comment, 'update_profile_attachment', $foreign_ids);
}
$qry_val_arr = array($profile_picture_path, $r_resource_vars['users']);
pg_query_params($db_lnk, 'UPDATE users SET profile_picture_path = $1 WHERE id = $2', $qry_val_arr);
} else {
$no_error = false;
$msg = 1;
}
} else {
if (!empty($_POST['email'])) {
$usr_val_arr = array($_POST['email']);
$user = executeQuery('SELECT * FROM users WHERE email = $1', $usr_val_arr);
if ($user['id'] != $r_resource_vars['users'] && $user['email'] == $_POST['email']) {
$no_error = false;
$msg = 2;
}
}
if ($no_error) {
$_POST['initials'] = strtoupper($_POST['initials']);
$qry_val_arr = array($_POST['full_name'], $_POST['about_me'], $_POST['initials'], $_POST['is_send_newsletter'], $r_resource_vars['users']);
$comment = '##USER_NAME## updated the profile.';
$foreign_ids['user_id'] = $authUser['id'];
$table_name = 'users';
$id = $r_resource_vars['users'];
if (!empty($table_name) && !empty($id)) {
$put = getbindValues($table_name, $_POST);
if ($table_name == 'users') {
unset($put['ip_id']);
}
$sfields = '';
foreach ($put as $key => $value) {
if ($key != 'id') {
$fields .= ', ' . $key;
}
if ($key != 'id' && $key != 'position') {
$sfields .= empty($sfields) ? $key : ", " . $key;
}
}
if (!empty($comment)) {
$revision = '';
$qry_va_arr = array($id);
$revisions['old_value'] = executeQuery('SELECT ' . $sfields . ' FROM ' . $table_name . ' WHERE id = $1', $qry_va_arr);
unset($revisions['old_value']['is_send_newsletter']);
unset($_POST['is_send_newsletter']);
$temp_revisions = array_diff($revisions['old_value'], $_POST);
foreach ($temp_revisions as $key => $value) {
$revisions['new_value'][$key] = isset($_POST[$key]) ? $_POST[$key] : '';
}
$revision = serialize($revisions);
$foreign_id = $id;
if (!empty($temp_revisions)) {
$response['activity'] = insertActivity($authUser['id'], $comment, 'update_profile', $foreign_ids, $revision, $foreign_id);
} else {
$response['activity'] = '';
}
if (!empty($response['activity']['revisions']) && trim($response['activity']['revisions']) != '') {
$revisions = unserialize($response['activity']['revisions']);
}
if (!empty($revisions)) {
if (!empty($revisions['new_value'])) {
foreach ($revisions['new_value'] as $key => $value) {
$old_val = isset($revisions['old_value'][$key]) ? $revisions['old_value'][$key] : '';
$new_val = isset($revisions['new_value'][$key]) ? $revisions['new_value'][$key] : '';
$dif[] = nl2br(getRevisiondifference($old_val, $new_val));
}
}
}
if (isset($dif)) {
$response['activity']['difference'] = $dif;
}
}
}
pg_query_params($db_lnk, 'UPDATE users SET full_name = $1, about_me = $2, initials = $3, is_send_newsletter = $4 WHERE id = $5', $qry_val_arr);
if (!empty($_POST['email'])) {
$qry_val_arr = array($_POST['email'], $r_resource_vars['users']);
pg_query_params($db_lnk, 'UPDATE users SET email= $1 WHERE id = $2', $qry_val_arr);
}
}
}
if ($no_error) {
$response['success'] = 'User Profile has been updated.';
} else {
$response['error'] = $msg;
}
break;
case '/settings':
//settings update
foreach ($r_post as $key => $value) {
$qry_val_arr = array($value, trim($key));
pg_query_params($db_lnk, 'UPDATE settings SET value = $1 WHERE name = $2', $qry_val_arr);
}
$response = array('success' => 'Settings updated successfully.');
break;
case '/boards':
//boards add
$is_import_board = false;
if (!empty($_FILES['board_import'])) {
if ($_FILES['board_import']['error'] == 0) {
$get_files = file_get_contents($_FILES['board_import']['tmp_name']);
$utf8_encoded_content = utf8_encode($get_files);
$imported_board = json_decode($utf8_encoded_content, true);
if (!empty($imported_board) && !empty($imported_board['prefs'])) {
$board = importTrelloBoard($imported_board);
$response['id'] = $board['id'];
} else {
$response['error'] = 'Unable to import. please try again.';
}
} else {
$response['error'] = 'Unable to import. please try again.';
}
} else {
$table_name = 'boards';
$qry_val_arr = array($r_post['name']);
$board = executeQuery('SELECT id, name FROM ' . $table_name . ' WHERE name = $1', $qry_val_arr);
if (isset($r_post['template']) && !empty($r_post['template'])) {
$lists = explode(',', $r_post['template']);
}
unset($r_post['template']);
$sql = true;
$r_post['user_id'] = !empty($authUser['id']) ? $authUser['id'] : 1;
}
break;
case '/boards/?/boards_stars':
//stars add
$table_name = 'board_stars';
$qry_val_arr = array($r_resource_vars['boards'], $authUser['id']);
$subcriber = executeQuery('SELECT id, is_starred FROM ' . $table_name . ' WHERE board_id = $1 and user_id = $2', $qry_val_arr);
if (!$subcriber) {
$qry_val_arr = array($r_resource_vars['boards'], $authUser['id']);
$result = pg_query_params($db_lnk, 'INSERT INTO ' . $table_name . ' (created, modified, board_id, user_id, is_starred) VALUES (now(), now(), $1, $2, true) RETURNING id', $qry_val_arr);
} else {
$subcriber = convertBooleanValues($table_name, $subcriber);
if ($subcriber['is_starred'] == 1) {
$qry_val_arr = array(0, $r_resource_vars['boards'], $authUser['id']);
$result = pg_query_params($db_lnk, 'UPDATE ' . $table_name . ' SET is_starred = $1 Where board_id = $2 and user_id = $3 RETURNING id', $qry_val_arr);
} else {
$qry_val_arr = array(1, $r_resource_vars['boards'], $authUser['id']);
$result = pg_query_params($db_lnk, 'UPDATE ' . $table_name . ' SET is_starred = $1 Where board_id = $2 and user_id = $3 RETURNING id', $qry_val_arr);
}
}
$star = pg_fetch_assoc($result);
$response['id'] = $star['id'];
break;
case '/boards/?/board_subscribers':
//subscriber add
$table_name = 'board_subscribers';
$qry_val_arr = array($r_resource_vars['boards'], $authUser['id']);
$subcriber = executeQuery('SELECT id, is_subscribed FROM ' . $table_name . ' WHERE board_id = $1 and user_id = $2', $qry_val_arr);
if (!$subcriber) {
$qry_val_arr = array($r_resource_vars['boards'], $authUser['id']);
$result = pg_query_params($db_lnk, 'INSERT INTO ' . $table_name . ' (created, modified, board_id, user_id, is_subscribed) VALUES (now(), now(), $1, $2, true) RETURNING *', $qry_val_arr);
} else {
if ($subcriber['is_subscribed'] == 1) {
$qry_val_arr = array($r_resource_vars['boards'], $authUser['id']);
$result = pg_query_params($db_lnk, 'UPDATE ' . $table_name . ' SET is_subscribed = false Where board_id = $1 and user_id = $2 RETURNING *', $qry_val_arr);
} else {
$qry_val_arr = array($r_resource_vars['boards'], $authUser['id']);
$result = pg_query_params($db_lnk, 'UPDATE ' . $table_name . ' SET is_subscribed = True Where board_id = $1 and user_id = $2 RETURNING *', $qry_val_arr);
}
}
$_response = pg_fetch_assoc($result);
$response = convertBooleanValues($table_name, $_response);
break;
case '/boards/?/copy':
//boards copy
$table_name = 'boards';
$sql = true;
$copied_board_id = $r_resource_vars['boards'];
$board_visibility = $r_post['board_visibility'];
if (!empty($r_post['organization_id'])) {
$organization_id = $r_post['organization_id'];
}
$keepcards = false;
if (!empty($r_post['keepCards'])) {
$keepcards = true;
unset($r_post['keepCards']);
}
$qry_val_arr = array($copied_board_id);
$sresult = pg_query_params($db_lnk, 'SELECT * FROM boards WHERE id = $1', $qry_val_arr);
$srow = pg_fetch_assoc($sresult);
unset($srow['id']);
unset($srow['created']);
unset($srow['modified']);
unset($srow['user_id']);
unset($srow['name']);
if ($srow['commenting_permissions'] === null) {
$srow['commenting_permissions'] = 0;
}
if ($srow['voting_permissions'] === null) {
$srow['voting_permissions'] = 0;
}
if ($srow['inivitation_permissions'] === null) {
$srow['inivitation_permissions'] = 0;
}
$r_post = array_merge($r_post, $srow);
$r_post['board_visibility'] = $board_visibility;
if (!empty($organization_id)) {
$r_post['organization_id'] = $organization_id;
}
break;
case '/boards/?/custom_backgrounds':
$is_return_vlaue = true;
if (!empty($_FILES['attachment']) && $_FILES['attachment']['error'] == 0) {
$allowed_ext = array('gif', 'png', 'jpg', 'jpeg', 'bmp');
$filename = $_FILES['attachment']['name'];
$file_ext = pathinfo($filename, PATHINFO_EXTENSION);
if (in_array($file_ext, $allowed_ext)) {
$mediadir = APP_PATH . DIRECTORY_SEPARATOR . 'media' . DIRECTORY_SEPARATOR . 'Board' . DIRECTORY_SEPARATOR . $r_resource_vars['boards'];
$save_path = 'media' . DIRECTORY_SEPARATOR . 'Board' . DIRECTORY_SEPARATOR . $r_resource_vars['boards'];
if (!file_exists($mediadir)) {
mkdir($mediadir, 0777, true);
}
$file = $_FILES['attachment'];
$file['name'] = preg_replace('/[^A-Za-z0-9\\-.]/', '', $file['name']);
if (is_uploaded_file($file['tmp_name']) && move_uploaded_file($file['tmp_name'], $mediadir . DIRECTORY_SEPARATOR . $file['name'])) {
$r_post['name'] = $file['name'];
foreach ($thumbsizes['Board'] as $key => $value) {
$mediadir = APP_PATH . DIRECTORY_SEPARATOR . 'client' . DIRECTORY_SEPARATOR . 'img' . DIRECTORY_SEPARATOR . $key . DIRECTORY_SEPARATOR . 'Board' . DIRECTORY_SEPARATOR . $r_resource_vars['boards'];
$list = glob($mediadir . '.*');
@unlink($list[0]);
}
$hash = md5(SECURITYSALT . 'Board' . $r_resource_vars['boards'] . 'jpg' . 'extra_large_thumb');
$background_picture_url = $_server_domain_url . '/img/extra_large_thumb/Board/' . $r_resource_vars['boards'] . '.' . $hash . '.jpg';
$r_post['background_picture_path'] = $save_path . DIRECTORY_SEPARATOR . $file['name'];
$r_post['path'] = $background_picture_url;
$response['background_picture_url'] = $background_picture_url;
}
$qry_val_array = array($r_post['path'], $r_post['background_picture_path'], $r_resource_vars['boards']);
pg_query_params($db_lnk, 'UPDATE boards SET background_picture_url = $1,background_picture_path = $2 WHERE id = $3', $qry_val_array);
} else {
$response['error'] = 'File extension not supported. It supports only jpg, png, bmp and gif.';
}
}
break;
case '/boards/?/users':
$is_return_vlaue = true;
$table_name = 'boards_users';
$r_post['board_id'] = $r_resource_vars['boards'];
$qry_val_arr = array($r_resource_vars['boards'], $r_post['user_id']);
$boards_user = executeQuery('SELECT * FROM boards_users WHERE board_id = $1 AND user_id = $2', $qry_val_arr);
if (empty($boards_user)) {
$sql = true;
}
break;
case '/boards/?/lists':
$table_name = 'lists';
$r_post['board_id'] = $r_resource_vars['boards'];
$r_post['user_id'] = $authUser['id'];
$sql = true;
if (isset($r_post['clone_list_id'])) {
$clone_list_id = $r_post['clone_list_id'];
unset($r_post['clone_list_id']);
unset($r_post['list_cards']);
}
break;
case '/boards/?/lists/?/list_subscribers':
$table_name = 'list_subscribers';
$r_post['user_id'] = $authUser['id'];
$qry_val_arr = array($r_resource_vars['lists'], $r_post['user_id']);
$s_result = pg_query_params($db_lnk, 'SELECT is_subscribed FROM list_subscribers WHERE list_id = $1 and user_id = $2', $qry_val_arr);
$check_subscribed = pg_fetch_assoc($s_result);
if (!empty($check_subscribed)) {
$is_subscribed = $r_post['is_subscribed'] ? true : false;
$qry_val_arr = array($is_subscribed, $r_resource_vars['lists'], $r_post['user_id']);
$s_result = pg_query_params($db_lnk, 'UPDATE list_subscribers SET is_subscribed = $1 WHERE list_id = $2 and user_id = $3', $qry_val_arr);
} else {
$r_post['list_id'] = $r_resource_vars['lists'];
$sql = true;
}
break;
case '/boards/?/lists/?/cards':
$table_name = 'cards';
$r_post['user_id'] = $authUser['id'];
$qry_val_arr = array($r_post['board_id'], $r_post['list_id']);
$pos_res = pg_query_params($db_lnk, 'SELECT position FROM cards WHERE board_id = $1 AND list_id = $2 ORDER BY position DESC LIMIT 1', $qry_val_arr);
$position = pg_fetch_array($pos_res);
if (empty($r_post['due_date'])) {
unset($r_post['due_date']);
}
if (!empty($r_post['user_ids'])) {
$r_post['members'] = explode(',', $r_post['user_ids']);
}
if (!isset($r_post['position'])) {
$r_post['position'] = $position[0] + 1;
}
$sql = true;
break;
case '/boards/?/lists/?/cards/?/comments':
$is_return_vlaue = true;
$table_name = 'activities';
$sql = true;
$prev_message = array();
if (isset($r_post['root']) && !empty($r_post['root'])) {
$qry_val_arr = array($r_post['root']);
$prev_message = executeQuery('SELECT ac.*, u,username, u.profile_picture_path, u.initials, u.full_name FROM activities ac LEFT JOIN users u ON ac.user_id = u.id WHERE ac.id = $1 order by created DESC', $qry_val_arr);
}
$r_post['freshness_ts'] = date('Y-m-d h:i:s');
$r_post['type'] = 'add_comment';
if (empty($r_post['user_id'])) {
$r_post['user_id'] = $authUser['id'];
}
break;
case '/boards/?/lists/?/cards/?/card_subscribers':
$table_name = 'card_subscribers';
$json = true;
$r_post['user_id'] = $authUser['id'];
unset($r_post['list_id']);
unset($r_post['board_id']);
$qry_val_arr = array($r_resource_vars['cards'], $r_post['user_id']);
$s_result = pg_query_params($db_lnk, 'SELECT is_subscribed FROM card_subscribers WHERE card_id = $1 and user_id = $2', $qry_val_arr);
$check_subscribed = pg_fetch_assoc($s_result);
if (!empty($check_subscribed)) {
$is_subscribed = $r_post['is_subscribed'] ? true : false;
$qry_val_arr = array($is_subscribed, $r_resource_vars['cards'], $r_post['user_id']);
$s_result = pg_query_params($db_lnk, 'UPDATE card_subscribers SET is_subscribed = $1 WHERE card_id = $2 and user_id = $3 RETURNING id', $qry_val_arr);
$subscribe = pg_fetch_assoc($s_result);
$response['id'] = $subscribe['id'];
} else {
$r_post['card_id'] = $r_resource_vars['cards'];
$r_post['user_id'] = $r_post['user_id'];
$sql = true;
}
break;
case '/boards/?/lists/?/cards/?/card_voters':
$table_name = 'card_voters';
$r_post['card_id'] = $r_resource_vars['cards'];
$r_post['user_id'] = $authUser['id'];
$sql = true;
break;
case '/boards/?/lists/?/cards/?/attachments':
$is_return_vlaue = true;
$table_name = 'card_attachments';
$r_post['card_id'] = $r_resource_vars['cards'];
$r_post['list_id'] = $r_resource_vars['lists'];
$r_post['board_id'] = $r_resource_vars['boards'];
$mediadir = APP_PATH . DIRECTORY_SEPARATOR . 'media' . DIRECTORY_SEPARATOR . 'Card' . DIRECTORY_SEPARATOR . $r_resource_vars['cards'];
$save_path = 'media' . DIRECTORY_SEPARATOR . 'Card' . DIRECTORY_SEPARATOR . $r_resource_vars['cards'];
$save_path = str_replace('\\', '/', $save_path);
if (!empty($_FILES['attachment']) && $_FILES['attachment']['error'] == 0) {
if (!file_exists($mediadir)) {
mkdir($mediadir, 0777, true);
}
$file = $_FILES['attachment'];
if (is_uploaded_file($file['tmp_name']) && move_uploaded_file($file['tmp_name'], $mediadir . DIRECTORY_SEPARATOR . $file['name'])) {
$r_post['path'] = $save_path . '/' . $file['name'];
$r_post['name'] = $file['name'];
$r_post['mimetype'] = $file['type'];
$qry_val_arr = array($r_post['card_id'], $r_post['name'], $r_post['path'], $r_post['list_id'], $r_post['board_id'], $r_post['mimetype']);
$s_result = pg_query_params($db_lnk, 'INSERT INTO card_attachments (created, modified, card_id, name, path, list_id, board_id, mimetype) VALUES (now(), now(), $1, $2, $3, $4, $5, $6) RETURNING *', $qry_val_arr);
$response['card_attachments'][] = pg_fetch_assoc($s_result);
}
foreach ($thumbsizes['CardAttachment'] as $key => $value) {
$mediadir = APP_PATH . '/client/img/' . $key . '/CardAttachment/' . $response['card_attachments'][0]['id'];
$list = glob($mediadir . '.*');
@unlink($list[0]);
}
$foreign_ids['board_id'] = $r_resource_vars['boards'];
$foreign_ids['list_id'] = $r_resource_vars['lists'];
$foreign_ids['card_id'] = $r_resource_vars['cards'];
$comment = '##USER_NAME## added attachment to this card ##CARD_LINK##';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_card_attachment', $foreign_ids, null, $response['card_attachments'][0]['id']);
} else {
if (!empty($_FILES['attachment']) && is_array($_FILES['attachment']['name']) && $_FILES['attachment']['error'][0] == 0) {
$file = $_FILES['attachment'];
for ($i = 0; $i < count($file['name']); $i++) {
if ($file['name'][$i] != 'undefined') {
if (!file_exists($mediadir)) {
mkdir($mediadir, 0777, true);
}
if (is_uploaded_file($file['tmp_name'][$i]) && move_uploaded_file($file['tmp_name'][$i], $mediadir . DIRECTORY_SEPARATOR . $file['name'][$i])) {
$r_post[$i]['path'] = $save_path . DIRECTORY_SEPARATOR . $file['name'][$i];
$r_post[$i]['name'] = $file['name'][$i];
$r_post[$i]['mimetype'] = $file['type'][$i];
$qry_val_arr = array($r_post['card_id'], $r_post[$i]['name'], $r_post[$i]['path'], $r_post['list_id'], $r_post['board_id'], $r_post[$i]['mimetype']);
$s_result = pg_query_params($db_lnk, 'INSERT INTO card_attachments (created, modified, card_id, name, path, list_id, board_id, mimetype) VALUES (now(), now(), $1, $2, $3, $4, $5, $6) RETURNING *', $qry_val_arr);
$response['card_attachments'][] = pg_fetch_assoc($s_result);
$foreign_ids['board_id'] = $r_resource_vars['boards'];
$foreign_ids['list_id'] = $r_resource_vars['lists'];
$foreign_ids['card_id'] = $r_resource_vars['cards'];
$comment = '##USER_NAME## added attachment to this card ##CARD_LINK##';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_card_attachment', $foreign_ids, null, $response['card_attachments'][$i]['id']);
foreach ($thumbsizes['CardAttachment'] as $key => $value) {
$imgdir = APP_PATH . '/client/img/' . $key . '/CardAttachment/' . $response['card_attachments'][$i]['id'];
$list = glob($imgdir . '.*');
@unlink($list[0]);
}
}
}
}
} else {
if (isset($r_post['image_link']) && !empty($r_post['image_link'])) {
if (!empty($r_post['image_link']) && is_array($r_post['image_link'])) {
$i = 0;
foreach ($r_post['image_link'] as $image_link) {
$attachment_url_host = parse_url($image_link, PHP_URL_HOST);
$r_post['name'] = $r_post['link'] = $image_link;
$qry_val_arr = array($r_post['card_id'], $r_post['name'], 'NULL', $r_post['list_id'], $r_post['board_id'], 'NULL', $r_post['link']);
$s_result = pg_query_params($db_lnk, 'INSERT INTO card_attachments (created, modified, card_id, name, path, list_id, board_id, mimetype, link) VALUES (now(), now(), $1, $2, $3, $4, $5, $6, $7) RETURNING *', $qry_val_arr);
$response['card_attachments'][] = pg_fetch_assoc($s_result);
$foreign_ids['board_id'] = $r_resource_vars['boards'];
$foreign_ids['list_id'] = $r_resource_vars['lists'];
$foreign_ids['card_id'] = $r_resource_vars['cards'];
$comment = '##USER_NAME## added attachment to this card ##CARD_LINK##';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_card_attachment', $foreign_ids, null, $response['card_attachments'][$i]['id']);
$i++;
}
} else {
$sql = true;
$attachment_url_host = parse_url($r_post['image_link'], PHP_URL_HOST);
$url_hosts = array('docs.google.com', 'www.dropbox.com', 'github.com');
if (in_array($attachment_url_host, $url_hosts)) {
$r_post['name'] = $r_post['link'] = $r_post['image_link'];
} else {
$filename = curlExecute($r_post['image_link'], 'get', $mediadir, 'image');
$r_post['name'] = $filename['file_name'];
$r_post['link'] = $r_post['image_link'];
}
unset($r_post['image_link']);
$r_post['path'] = $save_path . '/' . $filename['file_name'];
}
}
}
}
break;
case '/boards/?/lists/?/cards/?/labels':
$is_return_vlaue = true;
$table_name = 'cards_labels';
$r_post['card_id'] = $r_resource_vars['cards'];
$r_post['list_id'] = $r_resource_vars['lists'];
$r_post['board_id'] = $r_resource_vars['boards'];
$qry_val_arr = array($r_resource_vars['cards']);
$delete_labels = pg_query_params($db_lnk, 'DELETE FROM ' . $table_name . ' WHERE card_id = $1 RETURNING label_id', $qry_val_arr);
$delete_label = pg_fetch_assoc($delete_labels);
$delete_labels_count = pg_affected_rows($delete_labels);
if (!empty($r_post['name'])) {
$label_names = explode(',', $r_post['name']);
unset($r_post['name']);
foreach ($label_names as $label_name) {
$qry_val_arr = array($label_name);
$s_result = pg_query_params($db_lnk, 'SELECT id FROM labels WHERE name = $1', $qry_val_arr);
$label = pg_fetch_assoc($s_result);
if (empty($label)) {
$qry_val_arr = array($label_name);
$s_result = pg_query_params($db_lnk, 'INSERT INTO labels (created, modified, name) VALUES (now(), now(), $1) RETURNING id', $qry_val_arr);
$label = pg_fetch_assoc($s_result);
}
$r_post['label_id'] = $label['id'];
$qry_val_arr = array($r_post['card_id'], $r_post['label_id'], $r_post['board_id'], $r_post['list_id']);
pg_query_params($db_lnk, 'INSERT INTO ' . $table_name . ' (created, modified, card_id, label_id, board_id, list_id) VALUES (now(), now(), $1, $2, $3, $4) RETURNING *', $qry_val_arr);
}
$qry_val_arr = array($r_post['card_id']);
$s_result = pg_query_params($db_lnk, 'SELECT * FROM cards_labels_listing WHERE card_id = $1', $qry_val_arr);
$cards_labels = pg_fetch_all($s_result);
$response['cards_labels'] = $cards_labels;
$comment = '##USER_NAME## added label(s) to this card ##CARD_LINK## - ##LABEL_NAME##';
} else {
$response['cards_labels'] = array();
$comment = '##USER_NAME## removed label(s) in this card ##CARD_LINK## - ##LABEL_NAME##';
$foreign_ids['foreign_id'] = $delete_label['label_id'];
}
$foreign_ids['board_id'] = $r_post['board_id'];
$foreign_ids['list_id'] = $r_post['list_id'];
$foreign_ids['card_id'] = $r_post['card_id'];
if (!empty($delete_labels_count)) {
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_card_label', $foreign_ids, null, $r_post['label_id']);
}
break;
case '/boards/?/lists/?/cards/?/checklists':
$sql = true;
$table_name = 'checklists';
$r_post['user_id'] = $authUser['id'];
$r_post['card_id'] = $r_resource_vars['cards'];
if (isset($r_post['checklist_id'])) {
$checklist_id = $r_post['checklist_id'];
unset($r_post['checklist_id']);
}
break;
case '/boards/?/lists/?/cards/?/checklists/?/items':
$table_name = 'checklist_items';
$is_return_vlaue = true;
$r_post['user_id'] = $authUser['id'];
$r_post['card_id'] = $r_resource_vars['cards'];
$r_post['checklist_id'] = $r_resource_vars['checklists'];
unset($r_post['created']);
unset($r_post['modified']);
unset($r_post['is_offline']);
unset($r_post['list_id']);
unset($r_post['board_id']);
$names = explode("\n", $r_post['name']);
foreach ($names as $name) {
$r_post['name'] = trim($name);
if (!empty($r_post['name'])) {
$qry_val_arr = array($r_post['checklist_id']);
$position = executeQuery('SELECT max(position) as position FROM checklist_items WHERE checklist_id = $1', $qry_val_arr);
$r_post['position'] = $position['position'];
if (empty($r_post['position'])) {
$r_post['position'] = 0;
}
$r_post['position'] += 1;
$result = pg_execute_insert($table_name, $r_post);
$item = pg_fetch_assoc($result);
$response[$table_name][] = $item;
$foreign_ids['board_id'] = $r_resource_vars['boards'];
$foreign_ids['list_id'] = $r_resource_vars['lists'];
$foreign_ids['card_id'] = $r_post['card_id'];
$comment = '##USER_NAME## added item ##CHECKLIST_ITEM_NAME## in checklist ##CHECKLIST_ITEM_PARENT_NAME## of card ##CARD_LINK##';
$response['activities'][] = insertActivity($authUser['id'], $comment, 'add_checklist_item', $foreign_ids, '', $item['id']);
}
}
break;
case '/boards/?/lists/?/cards/?/checklists/?/items/?/convert_to_card':
$is_return_vlaue = true;
$table_name = 'cards';
$qry_val_arr = array($r_resource_vars['items']);
$result = pg_query_params($db_lnk, 'SELECT name FROM checklist_items WHERE id = $1', $qry_val_arr);
$row = pg_fetch_assoc($result);
$r_post['board_id'] = $r_resource_vars['boards'];
$r_post['list_id'] = $r_resource_vars['lists'];
$r_post['name'] = $row['name'];
$qry_val_arr = array($r_post['list_id']);
$sresult = pg_query_params($db_lnk, 'SELECT max(position) as position FROM cards WHERE list_id = $1', $qry_val_arr);
$srow = pg_fetch_assoc($sresult);
$r_post['position'] = $srow['position'];
$r_post['user_id'] = $authUser['id'];
$sql = true;
break;
case '/boards/?/lists/?/cards/?/users/?':
$is_return_vlaue = true;
$table_name = 'cards_users';
unset($r_post['board_id']);
unset($r_post['list_id']);
unset($r_post['is_offline']);
unset($r_post['profile_picture_path']);
unset($r_post['username']);
unset($r_post['initials']);
$qry_val_arr = array($r_resource_vars['cards'], $r_resource_vars['users']);
$check_already_added = executeQuery('SELECT * FROM cards_users WHERE card_id = $1 AND user_id = $2', $qry_val_arr);
if (!empty($check_already_added)) {
$response['id'] = $check_already_added['id'];
$response['cards_users'] = $check_already_added;
} else {
$sql = true;
}
break;
case '/boards/?/lists/?/cards/?/copy':
$is_return_vlaue = true;
$r_post['user_id'] = $authUser['id'];
$table_name = 'cards';
$is_keep_attachment = $is_keep_user = $is_keep_label = $is_keep_activity = $is_keep_checklist = 0;
if (isset($r_post['keep_attachments'])) {
$is_keep_attachment = $r_post['keep_attachments'];
unset($r_post['keep_attachments']);
}
if (isset($r_post['keep_activities'])) {
$is_keep_activity = $r_post['keep_activities'];
unset($r_post['keep_activities']);
}
if (isset($r_post['keep_labels'])) {
$is_keep_label = $r_post['keep_labels'];
unset($r_post['keep_labels']);
}
if (isset($r_post['keep_users'])) {
$is_keep_user = $r_post['keep_users'];
unset($r_post['keep_users']);
}
if (isset($r_post['keep_checklists'])) {
$is_keep_checklist = $r_post['keep_checklists'];
unset($r_post['keep_checklists']);
}
$copied_card_id = $r_resource_vars['cards'];
unset($r_post['copied_card_id']);
$qry_val_arr = array($copied_card_id);
$sresult = pg_query_params($db_lnk, 'SELECT * FROM cards WHERE id = $1', $qry_val_arr);
$srow = pg_fetch_assoc($sresult);
unset($srow['id']);
$card_name = $r_post['name'];
$r_post = array_merge($srow, $r_post);
$r_post['name'] = $card_name;
$conditions = array($r_post['list_id'], 'false');
$list_card_objs = pg_query_params($db_lnk, 'SELECT * FROM cards_listing WHERE list_id = $1 AND is_archived = $2 ORDER BY position ASC', $conditions);
$list_cards = array();
$h = 1;
while ($card = pg_fetch_assoc($list_card_objs)) {
$list_cards[$h] = $card;
$h++;
}
if (isset($list_cards[$r_post['position']]) && isset($list_cards[$r_post['position'] - 1])) {
$r_post['position'] = ($list_cards[$r_post['position']]['position'] + $list_cards[$r_post['position'] - 1]['position']) / 2;
} else {
if (!isset($list_cards[$r_post['position']]) && isset($list_cards[$r_post['position'] - 1])) {
$r_post['position'] = $list_cards[$r_post['position'] - 1]['position'] + 1;
} else {
if (isset($list_cards[$r_post['position']]) && !isset($list_cards[$r_post['position'] - 1])) {
$r_post['position'] = $list_cards[$r_post['position']]['position'] / 2;
} else {
if (!isset($list_cards[$r_post['position']]) && !isset($list_cards[$r_post['position'] - 1])) {
$r_post['position'] = 1;
}
}
}
}
$sql = true;
break;
case '/organizations/?/users/?':
//organization users add
$table_name = 'organizations_users';
$sql = true;
$is_return_vlaue = true;
$r_post['organization_id'] = $r_resource_vars['organizations'];
$r_post['user_id'] = $r_resource_vars['users'];
break;
case '/organizations':
//organizations add
$sql = true;
$table_name = 'organizations';
$r_post['user_id'] = !empty($authUser['id']) ? $authUser['id'] : 1;
$r_post['organization_visibility'] = 2;
break;
case '/organizations/?/upload_logo':
// organizations logo upload
$sql = false;
$json = true;
$organization_id = $r_resource_vars['organizations'];
if (!empty($_FILES['file'])) {
$_FILES['attachment'] = $_FILES['file'];
}
if (!empty($_FILES['attachment']) && $_FILES['attachment']['error'] == 0) {
$allowed_ext = array('gif', 'png', 'jpg', 'jpeg', 'bmp');
$filename = $_FILES['attachment']['name'];
$file_ext = pathinfo($filename, PATHINFO_EXTENSION);
if (in_array($file_ext, $allowed_ext)) {
$mediadir = APP_PATH . DIRECTORY_SEPARATOR . 'media' . DIRECTORY_SEPARATOR . 'Organization' . DIRECTORY_SEPARATOR . $r_resource_vars['organizations'];
$save_path = 'media' . DIRECTORY_SEPARATOR . 'Organization' . DIRECTORY_SEPARATOR . $r_resource_vars['organizations'];
if (!file_exists($mediadir)) {
mkdir($mediadir, 0777, true);
}
$file = $_FILES['attachment'];
$file['name'] = preg_replace('/[^A-Za-z0-9\\-.]/', '', $file['name']);
if (is_uploaded_file($file['tmp_name']) && move_uploaded_file($file['tmp_name'], $mediadir . DIRECTORY_SEPARATOR . $file['name'])) {
$logo_url = $save_path . DIRECTORY_SEPARATOR . $file['name'];
foreach ($thumbsizes['Organization'] as $key => $value) {
$list = glob(APP_PATH . DIRECTORY_SEPARATOR . 'img' . DIRECTORY_SEPARATOR . $key . DIRECTORY_SEPARATOR . 'Organization' . DIRECTORY_SEPARATOR . $r_resource_vars['organizations'] . '.*');
@unlink($list[0]);
}
foreach ($thumbsizes['Organization'] as $key => $value) {
$mediadir = APP_PATH . '/client/img/' . $key . '/Organization/' . $r_resource_vars['organizations'];
$list = glob($mediadir . '.*');
@unlink($list[0]);
}
$qry_val_arr = array($logo_url, $r_resource_vars['organizations']);
pg_query_params($db_lnk, 'UPDATE organizations SET logo_url = $1 WHERE id = $2', $qry_val_arr);
$response['logo_url'] = $logo_url;
$foreign_ids['organization_id'] = $r_resource_vars['organizations'];
$comment = (!empty($authUser['full_name']) ? $authUser['full_name'] : $authUser['username']) . ' added attachment to this organization ##ORGANIZATION_LINK##';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_organization_attachment', $foreign_ids);
}
} else {
$response['error'] = 1;
}
}
break;
case '/acl_links':
$table_name = $r_post['table'];
$colmns = array('acl_links_roles' => array('acl_link_id', 'role_id'), 'acl_board_links_boards_user_roles' => array('acl_board_link_id', 'board_user_role_id'), 'acl_organization_links_organizations_user_roles' => array('acl_organization_link_id', 'organization_user_role_id'));
$qry_val_arr = array($r_post['acl_link_id'], $r_post['role_id']);
$acl = executeQuery('SELECT * FROM ' . $table_name . ' WHERE ' . $colmns[$table_name][0] . ' = $1 AND ' . $colmns[$table_name][1] . ' = $2', $qry_val_arr);
if ($acl) {
$qry_val_arr = array($r_post['acl_link_id'], $r_post['role_id']);
pg_query_params($db_lnk, 'DELETE FROM ' . $table_name . ' WHERE ' . $colmns[$table_name][0] . ' = $1 AND ' . $colmns[$table_name][1] . ' = $2', $qry_val_arr);
} else {
$qry_val_arr = array($r_post['acl_link_id'], $r_post['role_id']);
pg_query_params($db_lnk, 'INSERT INTO ' . $table_name . ' (created, modified, ' . $colmns[$table_name][0] . ', ' . $colmns[$table_name][1] . ') VALUES(now(), now(), $1, $2)', $qry_val_arr);
}
break;
case '/apps/settings':
$folder_name = $r_post['folder'];
unset($r_post['folder']);
$content = file_get_contents(APP_PATH . '/client/apps/' . $folder_name . '/app.json');
$app = json_decode($content, true);
if (isset($r_post['enable'])) {
$app['enabled'] = $r_post['enable'];
} else {
foreach ($r_post as $key => $val) {
$app['settings'][$key]['value'] = $val;
}
}
$fh = fopen(APP_PATH . '/client/apps/' . $folder_name . '/app.json', 'w');
fwrite($fh, json_encode($app, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES));
fclose($fh);
$response['success'] = 'App updated successfully';
break;
case '/oauth/token':
$post_val = array('grant_type' => 'authorization_code', 'code' => $r_post['code'], 'redirect_uri' => $r_post['redirect_uri'], 'client_id' => OAUTH_CLIENTID, 'client_secret' => OAUTH_CLIENT_SECRET);
$response = getToken($post_val);
break;
case '/oauth/clients':
$sql = true;
$table_name = 'oauth_clients';
$r_post['client_id'] = isClientIdAvailable();
$r_post['client_secret'] = isClientSecretAvailable();
$r_post['grant_types'] = 'client_credentials refresh_token authorization_code';
break;
case '/webhooks':
$sql = true;
$table_name = 'webhooks';
break;
case '/users/import':
$t_ldap_server = LDAP_IS_SSL == 'true' ? 'ldaps://' : 'ldap://';
$t_ds = $ldap_connection = ldap_connect($t_ldap_server . LDAP_SERVER, LDAP_PORT);
if ($t_ds > 0) {
ldap_set_option($ldap_connection, LDAP_OPT_PROTOCOL_VERSION, LDAP_PROTOCOL_VERSION) or die('Unable to set LDAP protocol version');
ldap_set_option($ldap_connection, LDAP_OPT_REFERRALS, 0);
if (true === ldap_bind($ldap_connection, LDAP_BIND_DN, LDAP_BIND_PASSWD)) {
$search_filter = '(&(objectCategory=person)(' . LDAP_UID_FIELD . '=*))';
$attributes = array('samaccountname', 'mail', 'name', 'memberof', 'admincount');
$result = ldap_search($ldap_connection, LDAP_ROOT_DN, $search_filter, $attributes);
if (false !== $result) {
$entries = ldap_get_entries($ldap_connection, $result);
for ($x = 0; $x < $entries['count']; $x++) {
if ($_POST['is_import_organizations'] != 'true') {
$users[] = array('username' => !empty($entries[$x]['samaccountname'][0]) ? trim($entries[$x]['samaccountname'][0]) : '', 'email' => !empty($entries[$x]['mail'][0]) ? trim($entries[$x]['mail'][0]) : '', 'name' => !empty($entries[$x]['name'][0]) ? trim($entries[$x]['name'][0]) : '', 'admincount' => !empty($entries[$x]['admincount']['count']) ? trim($entries[$x]['admincount']['count']) : '');
} else {
if (!empty($entries[$x]['memberof'][0])) {
$users[trim($entries[$x]['memberof'][0])][] = array('username' => !empty($entries[$x]['samaccountname'][0]) ? trim($entries[$x]['samaccountname'][0]) : '', 'email' => !empty($entries[$x]['mail'][0]) ? trim($entries[$x]['mail'][0]) : '', 'name' => !empty($entries[$x]['name'][0]) ? trim($entries[$x]['name'][0]) : '', 'admincount' => !empty($entries[$x]['admincount']['count']) ? trim($entries[$x]['admincount']['count']) : '');
} else {
$no_organization_users[] = array('username' => !empty($entries[$x]['samaccountname'][0]) ? trim($entries[$x]['samaccountname'][0]) : '', 'email' => !empty($entries[$x]['mail'][0]) ? trim($entries[$x]['mail'][0]) : '', 'name' => !empty($entries[$x]['name'][0]) ? trim($entries[$x]['name'][0]) : '', 'admincount' => !empty($entries[$x]['admincount']['count']) ? trim($entries[$x]['admincount']['count']) : '');
}
}
}
}
ldap_unbind($ldap_connection);
}
if (!empty($users)) {
if ($_POST['is_import_organizations'] != 'true') {
foreach ($users as $keys => $values) {
$condition = array($values['username']);
$is_user_exist = executeQuery('SELECT id FROM users WHERE username = $1', $condition);
if (empty($is_user_exist)) {
$password = getCryptHash($values['username']);
preg_match_all('/\\b\\w/', $values['name'], $match);
$data = array($values['username'], $values['email'], $password, $values['name'], strtoupper(implode($match[0])));
pg_query_params($db_lnk, 'INSERT INTO users(created, modified, role_id, username, email, password, full_name, initials, is_active, is_email_confirmed, is_ldap) VALUES (now(), now(), 2, $1, $2, $3, $4, $5, true, true, true) RETURNING id ', $data);
if ($_POST['is_send_welcome_mail'] == 'true') {
$emailFindReplace = array('##NAME##' => $values['name']);
sendMail('welcome', $emailFindReplace, $values['email']);
}
}
}
} else {
foreach ($users as $key => $value) {
$org = explode(",", $key);
$organization_name = substr($org[0], 3);
$condition = array($organization_name);
$is_organization_exist = executeQuery('SELECT id FROM organizations WHERE name = $1', $condition);
if (empty($is_organization_exist)) {
$data = array($authUser['id'], $organization_name, 0);
$result = pg_query_params($db_lnk, 'INSERT INTO organizations(created, modified, user_id, name, organization_visibility) VALUES (now(), now(), $1, $2, $3) RETURNING id', $data);
$organization = pg_fetch_assoc($result);
$organization_id = $organization['id'];
} else {
$organization_id = $is_organization_exist['id'];
}
foreach ($value as $keys => $values) {
$condition = array($values['username']);
$is_user_exist = executeQuery('SELECT id FROM users WHERE username = $1', $condition);
if (empty($is_user_exist)) {
$password = getCryptHash($values['username']);
preg_match_all('/\\b\\w/', $values['name'], $match);
$data = array($values['username'], $values['email'], $password, $values['name'], strtoupper(implode($match[0])));
$result1 = pg_query_params($db_lnk, 'INSERT INTO users(created, modified, role_id, username, email, password, full_name, initials, is_active, is_email_confirmed, is_ldap) VALUES (now(), now(), 2, $1, $2, $3, $4, $5, true, true, true) RETURNING id ', $data);
$user = pg_fetch_assoc($result1);
$user_id = $user['id'];
if ($_POST['is_send_welcome_mail'] == 'true') {
$emailFindReplace = array('##NAME##' => $values['name']);
sendMail('welcome', $emailFindReplace, $values['email']);
}
} else {
$user_id = $is_user_exist['id'];
}
if (empty($is_organization_exist)) {
$organization_user_role_id = 2;
if (!empty($values['admincount'])) {
$organization_user_role_id = 1;
}
$data = array($organization_id, $user_id, $organization_user_role_id);
$condition = array($user_id);
$is_organization_user_exist = executeQuery('SELECT id FROM organizations_users WHERE user_id = $1', $condition);
if (empty($is_organization_user_exist)) {
pg_query_params($db_lnk, 'INSERT INTO organizations_users (created, modified, organization_id, user_id, organization_user_role_id) VALUES (now(), now(), $1, $2, $3)', $data);
}
}
}
}
}
$response['success'] = 'import_success';
} else {
$response['error'] = 'user_not_found';
}
} else {
$response['error'] = 'connection_failed';
}
break;
default:
header($_SERVER['SERVER_PROTOCOL'] . ' 501 Not Implemented', true, 501);
break;
}
if (!empty($sql)) {
$post = getbindValues($table_name, $r_post);
$result = pg_execute_insert($table_name, $post);
if ($result) {
$row = pg_fetch_assoc($result);
$response['id'] = $row['id'];
if ($is_return_vlaue) {
$row = convertBooleanValues($table_name, $row);
$response[$table_name] = $row;
}
if (!empty($uuid)) {
$response['uuid'] = $uuid;
}
if ($r_resource_cmd == '/users/register') {
$emailFindReplace['##NAME##'] = $r_post['full_name'];
$emailFindReplace['##ACTIVATION_URL##'] = 'http://' . $_SERVER['HTTP_HOST'] . '/#/users/activation/' . $row['id'] . '/' . md5($r_post['username']);
sendMail('activation', $emailFindReplace, $r_post['email']);
} else {
if ($r_resource_cmd == '/boards') {
if (!$is_import_board) {
$foreign_id['board_id'] = $response['id'];
$comment = '##USER_NAME## created board';
$qry_val_arr = array($row['id'], $r_post['user_id']);
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_board', $foreign_id);
$result = pg_query_params($db_lnk, 'INSERT INTO boards_users (created, modified, board_id , user_id, board_user_role_id) VALUES (now(), now(), $1, $2, 1)', $qry_val_arr);
if (!empty($row['board_visibility']) && $row['board_visibility'] == 1 && !empty($r_post['organization_id'])) {
$qry_val_arr = array($r_post['organization_id']);
$organization_users = pg_query_params($db_lnk, 'SELECT * FROM organizations_users WHERE organization_id = $1', $qry_val_arr);
while ($organization_user = pg_fetch_assoc($organization_users)) {
if (!empty($organization_user)) {
if ($organization_user['user_id'] != $row['user_id']) {
$qry_val_arr = array($row['id'], $organization_user['user_id']);
pg_query_params($db_lnk, 'INSERT INTO boards_users (created, modified, board_id , user_id, board_user_role_id) VALUES (now(), now(), $1, $2, 2)', $qry_val_arr);
}
}
}
}
if (isset($lists) && !empty($lists)) {
$position = 1;
$total_list = count($lists);
$s_sql = 'INSERT INTO lists (created, modified, board_id, name, user_id, position) VALUES';
foreach ($lists as $list) {
$qry_val_arr = array($response['id'], $list, $authUser['id'], $position);
$s_sql = 'INSERT INTO lists (created, modified, board_id, name, user_id, position) VALUES';
$s_sql .= '(now(), now(), $1, $2, $3, $4)';
pg_query_params($db_lnk, $s_sql, $qry_val_arr);
$position++;
}
}
$qry_val_arr = array($row['id']);
$response['simple_board'] = executeQuery('SELECT row_to_json(d) FROM (SELECT * FROM simple_board_listing sbl WHERE id = $1 ORDER BY id ASC) as d', $qry_val_arr);
$response['simple_board'] = json_decode($response['simple_board']['row_to_json'], true);
}
} else {
if ($r_resource_cmd == '/organizations') {
$qry_val_arr = array($row['id'], $r_post['user_id'], 1);
$result = pg_query_params($db_lnk, 'INSERT INTO organizations_users (created, modified, organization_id , user_id, organization_user_role_id) VALUES (now(), now(), $1, $2, $3)', $qry_val_arr);
$foreign_id['organization_id'] = $row['id'];
$comment = '##USER_NAME## created organization "##ORGANIZATION_LINK##"';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_organization', $foreign_id);
} else {
if ($r_resource_cmd == '/boards/?/lists') {
$foreign_ids['board_id'] = $r_post['board_id'];
$foreign_ids['list_id'] = $response['id'];
$comment = '##USER_NAME## added list "' . $r_post['name'] . '".';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_list', $foreign_ids);
$copy_checklists = array();
$copy_checklists_items = array();
if (!empty($clone_list_id)) {
$qry_val_arr = array($clone_list_id);
$s_result = pg_query_params($db_lnk, 'SELECT name, board_id, position FROM lists WHERE id = $1', $qry_val_arr);
$previous_list = pg_fetch_assoc($s_result);
$new_list_id = $response['id'];
// Copy cards
$card_fields = 'board_id, name, description, position, due_date, is_archived, attachment_count, checklist_count, checklist_item_count, checklist_item_completed_count, label_count, cards_user_count, cards_subscriber_count, card_voter_count, activity_count, user_id, comment_count';
$card_fields = 'list_id, ' . $card_fields;
$qry_val_arr = array($clone_list_id);
$cards = pg_query_params($db_lnk, 'SELECT id, ' . $card_fields . ' FROM cards WHERE list_id = $1 ORDER BY id', $qry_val_arr);
if (pg_num_rows($cards)) {
copyCards($card_fields, $cards, $new_list_id, $post['name'], $foreign_ids['board_id']);
}
}
$qry_val_arr = array($foreign_ids['list_id']);
$s_result = pg_query_params($db_lnk, 'SELECT * FROM lists_listing WHERE id = $1', $qry_val_arr);
$list = pg_fetch_assoc($s_result);
$response['list'] = $list;
$qry_val_arr = array($foreign_ids['list_id']);
$attachments = pg_query_params($db_lnk, 'SELECT * FROM card_attachments WHERE list_id = $1 order by created DESC', $qry_val_arr);
while ($attachment = pg_fetch_assoc($attachments)) {
$response['list']['attachments'][] = $attachment;
}
$qry_val_arr = array($foreign_ids['list_id']);
$activities = pg_query_params($db_lnk, 'SELECT * FROM activities_listing WHERE list_id = $1', $qry_val_arr);
while ($activity = pg_fetch_assoc($activities)) {
$response['list']['activities'][] = $activity;
}
$condition = array($foreign_ids['list_id']);
$cards = pg_query_params($db_lnk, 'select * from cards where list_id = $1', $condition);
while ($card = pg_fetch_assoc($cards)) {
$response['list']['checklists'] = $response['list']['checklists_items'] = array();
if (!empty($card)) {
$condition = array($card['id']);
$checklists = pg_query_params($db_lnk, 'select * from checklists where card_id = $1', $condition);
while ($checklist = pg_fetch_assoc($checklists)) {
if (!empty($checklist)) {
$response['list']['checklists'][] = $checklist;
$condition = array($card['id'], $checklist['id']);
$checklist_items = pg_query_params($db_lnk, 'select * from checklist_items where card_id = $1 AND checklist_id = $2', $condition);
while ($checklist_item = pg_fetch_assoc($checklist_items)) {
if (!empty($checklist_item)) {
$response['list']['checklists_items'][] = $checklist_item;
}
}
}
}
}
}
$qry_val_arr = array($foreign_ids['list_id']);
$labels = pg_query_params($db_lnk, 'SELECT * FROM cards_labels_listing WHERE list_id = $1', $qry_val_arr);
while ($label = pg_fetch_assoc($labels)) {
$response['list']['labels'][] = $label;
}
$response['list']['cards'] = json_decode($response['list']['cards'], true);
$response['list']['lists_subscribers'] = json_decode($response['list']['lists_subscribers'], true);
$qry_val_arr = array($r_post['board_id']);
$list_count = executeQuery('SELECT count(*) as count FROM lists WHERE board_id = $1', $qry_val_arr);
if ($list_count['count'] == 1) {
$qry_val_arr = array($r_post['board_id'], $response['id']);
$board_query = pg_query_params($db_lnk, 'UPDATE boards SET default_email_list_id = $2 WHERE id = $1', $qry_val_arr);
}
} else {
if ($r_resource_cmd == '/boards/?/lists/?/cards' || $r_resource_cmd == '/boards/?/lists/?/cards/?/checklists/?/items/?/convert_to_card') {
$qry_val_arr = array($r_post['list_id']);
$s_result = pg_query_params($db_lnk, 'SELECT name FROM lists WHERE id = $1', $qry_val_arr);
$list = pg_fetch_assoc($s_result);
$foreign_ids['board_id'] = $r_post['board_id'];
$foreign_ids['card_id'] = $response['id'];
$foreign_ids['list_id'] = $r_post['list_id'];
$comment = '##USER_NAME## added card ##CARD_LINK## to list "' . $list['name'] . '".';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_card', $foreign_ids);
if (!empty($r_post['members'])) {
$s_usql = '';
foreach ($r_post['members'] as $member) {
$s_usql = 'INSERT INTO cards_users (created, modified, card_id, user_id) VALUES(now(), now(), ' . $response['id'] . ', ' . $member . ') RETURNING id';
$s_result = pg_query_params($db_lnk, $s_usql, array());
$card_user = pg_fetch_assoc($s_result);
$qry_val_arr = array($member);
$_user = executeQuery('SELECT username FROM users WHERE id = $1', $qry_val_arr);
$comment = '##USER_NAME## added "' . $_user['username'] . '" as member to this card ##CARD_LINK##';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_card_user', $foreign_ids, '', $card_user['id']);
}
}
$qry_val_arr = array($response['id']);
$cards_users = pg_query_params($db_lnk, 'SELECT * FROM cards_users_listing WHERE card_id = $1', $qry_val_arr);
while ($cards_user = pg_fetch_assoc($cards_users)) {
$response['cards_users'][] = $cards_user;
}
if (!empty($r_post['labels'])) {
$r_post['card_labels'] = $r_post['labels'];
}
if (!empty($r_post['card_labels'])) {
$label_names = explode(',', $r_post['card_labels']);
foreach ($label_names as $label_name) {
$qry_val_arr = array($label_name);
$s_result = pg_query_params($db_lnk, 'SELECT id FROM labels WHERE name = $1', $qry_val_arr);
$label = pg_fetch_assoc($s_result);
if (empty($label)) {
$qry_val_arr = array($label_name);
$s_result = pg_query_params($db_lnk, $s_sql = 'INSERT INTO labels (created, modified, name) VALUES (now(), now(), $1) RETURNING id', $qry_val_arr);
$label = pg_fetch_assoc($s_result);
}
$r_post['label_id'] = $label['id'];
$r_post['card_id'] = $row['id'];
$r_post['list_id'] = $row['list_id'];
$r_post['board_id'] = $row['board_id'];
$qry_val_arr = array($r_post['card_id'], $r_post['label_id'], $r_post['board_id'], $r_post['list_id']);
pg_query_params($db_lnk, 'INSERT INTO cards_labels (created, modified, card_id, label_id, board_id, list_id) VALUES (now(), now(), $1, $2, $3, $4) RETURNING *', $qry_val_arr);
}
$comment = '##USER_NAME## added label(s) to this card ##CARD_LINK## - ##LABEL_NAME##';
insertActivity($authUser['id'], $comment, 'add_card_label', $foreign_ids, null, $r_post['label_id']);
}
$qry_val_arr = array($response['id']);
$cards_labels = pg_query_params($db_lnk, 'SELECT * FROM cards_labels_listing WHERE card_id = $1', $qry_val_arr);
while ($cards_label = pg_fetch_assoc($cards_labels)) {
$response['cards_labels'][] = $cards_label;
}
if (!empty($clone_card_id)) {
$qry_val_arr = array($response['id'], $clone_card_id);
pg_query_params($db_lnk, 'INSERT INTO card_attachments (created, modified, card_id, name, path, mimetype) SELECT created, modified, $1, name, path, mimetype FROM card_attachments WHERE card_id = $2', $qry_val_arr);
$qry_val_arr = array($clone_card_id);
$s_result = pg_query_params($db_lnk, 'SELECT name, list_id, board_id, position FROM lists WHERE id = $1', $qry_val_arr);
$previous_value = pg_fetch_assoc($s_result);
$comment = '##USER_NAME## copied card "' . $r_post['name'] . '". from "' . $previous_value['name'] . '"';
$response['activity'] = insertActivity($authUser['id'], $comment, 'copy_card', $foreign_id);
}
} else {
if ($r_resource_cmd == '/boards/?/copy') {
$new_board_id = $row['id'];
//Copy board users
$boards_user_fields = 'user_id, board_user_role_id';
$qry_val_arr = array($r_resource_vars['boards']);
$boards_users = pg_query_params($db_lnk, 'SELECT id, ' . $boards_user_fields . ' FROM boards_users WHERE board_id = $1', $qry_val_arr);
if ($boards_users && pg_num_rows($boards_users)) {
$boards_user_fields = 'created, modified, board_id, ' . $boards_user_fields;
while ($boards_user = pg_fetch_object($boards_users)) {
$boards_user_values = array();
array_push($boards_user_values, 'now()', 'now()', $new_board_id);
foreach ($boards_user as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($boards_user_values, 'false');
} else {
if ($value === null) {
array_push($boards_user_values, null);
} else {
array_push($boards_user_values, $value);
}
}
}
}
$boards_user_val = '';
for ($i = 1, $len = count($boards_user_values); $i <= $len; $i++) {
$boards_user_val .= '$' . $i;
$boards_user_val .= $i != $len ? ', ' : '';
}
$boards_user_result = pg_query_params($db_lnk, 'INSERT INTO boards_users (' . $boards_user_fields . ') VALUES (' . $boards_user_val . ') RETURNING id', $boards_user_values);
}
}
//Copy board subscribers
$boards_subscriber_fields = 'user_id, is_subscribed';
$qry_val_arr = array($r_resource_vars['boards']);
$boards_subscribers = pg_query_params($db_lnk, 'SELECT id, ' . $boards_subscriber_fields . ' FROM board_subscribers WHERE board_id = $1', $qry_val_arr);
if ($boards_subscribers && pg_num_rows($boards_subscribers)) {
$boards_subscriber_fields = 'created, modified, board_id, ' . $boards_subscriber_fields;
while ($boards_subscriber = pg_fetch_object($boards_subscribers)) {
$boards_subscriber_values = array();
array_push($boards_subscriber_values, 'now()', 'now()', $new_board_id);
foreach ($boards_subscriber as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($boards_subscriber_values, 'false');
} else {
if ($value === null) {
array_push($boards_subscriber_values, null);
} else {
array_push($boards_subscriber_values, $value);
}
}
}
}
$boards_subscriber_val = '';
for ($i = 1, $len = count($boards_subscriber_values); $i <= $len; $i++) {
$boards_subscriber_val .= '$' . $i;
$boards_subscriber_val .= $i != $len ? ', ' : '';
}
$boards_subscriber_result = pg_query_params($db_lnk, 'INSERT INTO board_subscribers (' . $boards_subscriber_fields . ') VALUES (' . $boards_subscriber_val . ') RETURNING id', $boards_subscriber_values);
}
}
//Copy board star
$boards_star_fields = 'user_id, is_starred';
$qry_val_arr = array($r_resource_vars['boards']);
$boards_stars = pg_query_params($db_lnk, 'SELECT id, ' . $boards_star_fields . ' FROM board_stars WHERE board_id = $1', $qry_val_arr);
if ($boards_stars && pg_num_rows($boards_stars)) {
$boards_star_fields = 'created, modified, board_id, ' . $boards_star_fields;
while ($boards_star = pg_fetch_object($boards_stars)) {
$boards_star_values = array();
array_push($boards_star_values, 'now()', 'now()', $new_board_id);
foreach ($boards_star as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($boards_star_values, 'false');
} else {
if ($value === null) {
array_push($boards_star_values, null);
} else {
array_push($boards_star_values, $value);
}
}
}
}
$boards_star_val = '';
for ($i = 1, $len = count($boards_star_values); $i <= $len; $i++) {
$boards_star_val .= '$' . $i;
$boards_star_val .= $i != $len ? ', ' : '';
}
$boards_star_result = pg_query_params($db_lnk, 'INSERT INTO board_stars (' . $boards_star_fields . ') VALUES (' . $boards_star_val . ') RETURNING id', $boards_star_values);
}
}
if ($keepcards) {
$qry_val_arr = array($r_resource_vars['boards']);
$lists = pg_query_params($db_lnk, 'SELECT id, name, position, is_archived, card_count, lists_subscriber_count FROM lists WHERE board_id = $1', $qry_val_arr);
} else {
$qry_val_arr = array($r_resource_vars['boards']);
$lists = pg_query_params($db_lnk, 'SELECT id, name, position, is_archived, lists_subscriber_count FROM lists WHERE board_id = $1', $qry_val_arr);
}
if ($lists) {
// Copy lists
while ($list = pg_fetch_object($lists)) {
$list_id = $list->id;
$list_fields = 'created, modified, board_id, user_id';
$list_values = array();
array_push($list_values, 'now()', 'now()', $new_board_id, $authUser['id']);
foreach ($list as $key => $value) {
if ($key != 'id') {
$list_fields .= ', ' . $key;
if ($value === false) {
array_push($list_values, 'false');
} else {
array_push($list_values, $value);
}
}
}
$list_val = '';
for ($i = 1, $len = count($list_values); $i <= $len; $i++) {
$list_val .= '$' . $i;
$list_val .= $i != $len ? ', ' : '';
}
$lists_result = pg_query_params($db_lnk, 'INSERT INTO lists (' . $list_fields . ') VALUES (' . $list_val . ') RETURNING id', $list_values);
if ($lists_result) {
$list_result = pg_fetch_assoc($lists_result);
$new_list_id = $list_result['id'];
//Copy list subscribers
$lists_subscriber_fields = 'user_id, is_subscribed';
$qry_val_arr = array($list_id);
$lists_subscribers = pg_query_params($db_lnk, 'SELECT id, ' . $lists_subscriber_fields . ' FROM list_subscribers WHERE list_id = $1', $qry_val_arr);
if ($lists_subscribers && pg_num_rows($lists_subscribers)) {
$lists_subscriber_fields = 'created, modified, list_id, ' . $lists_subscriber_fields;
while ($lists_subscriber = pg_fetch_object($lists_subscribers)) {
$lists_subscriber_values = array();
array_push($lists_subscriber_values, 'now()', 'now()', $new_list_id);
foreach ($lists_subscriber as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($lists_subscriber_values, 'false');
} else {
if ($value === null) {
array_push($lists_subscriber_values, null);
} else {
array_push($lists_subscriber_values, $value);
}
}
}
}
$lists_subscriber_val = '';
for ($i = 1, $len = count($lists_subscriber_values); $i <= $len; $i++) {
$lists_subscriber_val .= '$' . $i;
$lists_subscriber_val .= $i != $len ? ', ' : '';
}
$lists_subscriber_result = pg_query_params($db_lnk, 'INSERT INTO list_subscribers (' . $lists_subscriber_fields . ') VALUES (' . $lists_subscriber_val . ') RETURNING id', $lists_subscriber_values);
}
}
// Copy cards
$card_fields = 'name, description, due_date, position, is_archived, attachment_count, checklist_count, checklist_item_count, checklist_item_completed_count, label_count, cards_user_count, cards_subscriber_count, card_voter_count, activity_count, user_id, comment_count';
if ($keepcards) {
$qry_val_arr = array($list_id);
$cards = pg_query_params($db_lnk, 'SELECT id, ' . $card_fields . ' FROM cards WHERE list_id = $1', $qry_val_arr);
}
if ($keepcards && pg_num_rows($cards)) {
$card_fields = 'created, modified, board_id, list_id, ' . $card_fields;
while ($card = pg_fetch_object($cards)) {
$card_id = $card->id;
$card_values = array();
array_push($card_values, 'now()', 'now()', $new_board_id, $new_list_id);
foreach ($card as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($card_values, 'false');
} else {
if ($value === null) {
array_push($card_values, null);
} else {
array_push($card_values, $value);
}
}
}
}
$card_val = '';
for ($i = 1, $len = count($card_values); $i <= $len; $i++) {
$card_val .= '$' . $i;
$card_val .= $i != $len ? ', ' : '';
}
$card_result = pg_query_params($db_lnk, 'INSERT INTO cards (' . $card_fields . ') VALUES (' . $card_val . ') RETURNING id', $card_values);
if ($card_result) {
$card_result = pg_fetch_assoc($card_result);
$new_card_id = $card_result['id'];
//Copy card attachments
$attachment_fields = 'name, path, mimetype';
$qry_val_arr = array($card_id);
$attachments = pg_query_params($db_lnk, 'SELECT id, ' . $attachment_fields . ' FROM card_attachments WHERE card_id = $1', $qry_val_arr);
if ($attachments && pg_num_rows($attachments)) {
$attachment_fields = 'created, modified, board_id, list_id, card_id, ' . $attachment_fields;
while ($attachment = pg_fetch_object($attachments)) {
$attachment_values = array();
array_push($attachment_values, 'now()', 'now()', $new_board_id, $new_list_id, $new_card_id);
foreach ($attachment as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($attachment_values, 'false');
} else {
if ($value === null) {
array_push($attachment_values, null);
} else {
array_push($attachment_values, $value);
}
}
}
}
$attachment_val = '';
for ($i = 1, $len = count($attachment_values); $i <= $len; $i++) {
$attachment_val .= '$' . $i;
$attachment_val .= $i != $len ? ', ' : '';
}
$card_result = pg_query_params($db_lnk, 'INSERT INTO card_attachments (' . $attachment_fields . ') VALUES (' . $attachment_val . ') RETURNING id', $attachment_values);
}
}
//Copy checklists
$checklist_fields = 'user_id, name, checklist_item_count, checklist_item_completed_count, position';
$qry_val_arr = array($card_id);
$checklists = pg_query_params($db_lnk, 'SELECT id, ' . $checklist_fields . ' FROM checklists WHERE card_id = $1', $qry_val_arr);
if ($checklists && pg_num_rows($checklists)) {
$checklist_fields = 'created, modified, card_id, ' . $checklist_fields;
while ($checklist = pg_fetch_object($checklists)) {
$checklist_values = array();
array_push($checklist_values, 'now()', 'now()', $new_card_id);
$checklist_id = $checklist->id;
foreach ($checklist as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($checklist_values, 'false');
} else {
if ($value === null) {
array_push($checklist_values, null);
} else {
array_push($checklist_values, $value);
}
}
}
}
$checklist_val = '';
for ($i = 1, $len = count($checklist_values); $i <= $len; $i++) {
$checklist_val .= '$' . $i;
$checklist_val .= $i != $len ? ', ' : '';
}
$checklist_result = pg_query_params($db_lnk, 'INSERT INTO checklists (' . $checklist_fields . ') VALUES (' . $checklist_val . ') RETURNING id', $checklist_values);
if ($checklist_result) {
$checklist_result = pg_fetch_assoc($checklist_result);
$new_checklist_id = $checklist_result['id'];
//Copy checklist items
$checklist_item_fields = 'user_id, name, position';
$qry_val_array = array($checklist_id);
$checklist_items = pg_query_params($db_lnk, 'SELECT id, ' . $checklist_item_fields . ' FROM checklist_items WHERE checklist_id = $1', $qry_val_array);
if ($checklist_items && pg_num_rows($checklist_items)) {
$checklist_item_fields = 'created, modified, card_id, checklist_id, ' . $checklist_item_fields;
while ($checklist_item = pg_fetch_object($checklist_items)) {
$checklist_item_values = array();
array_push($checklist_item_values, 'now()', 'now()', $new_card_id, $new_checklist_id);
foreach ($checklist_item as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($checklist_item_values, 'false');
} else {
if ($value === null) {
array_push($checklist_item_values, null);
} else {
array_push($checklist_item_values, $value);
}
}
}
}
$checklist_item_val = '';
for ($i = 1, $len = count($checklist_item_values); $i <= $len; $i++) {
$checklist_item_val .= '$' . $i;
$checklist_item_val .= $i != $len ? ', ' : '';
}
$checklist_item_result = pg_query_params($db_lnk, 'INSERT INTO checklist_items (' . $checklist_item_fields . ') VALUES (' . $checklist_item_val . ') RETURNING id', $checklist_item_values);
}
}
}
}
}
//Copy card voters
$card_voter_fields = 'user_id';
$qry_val_arr = array($card_id);
$card_voters = pg_query_params($db_lnk, 'SELECT id, ' . $card_voter_fields . ' FROM card_voters WHERE card_id = $1', $qry_val_arr);
if ($card_voters && pg_num_rows($card_voters)) {
$card_voter_fields = 'created, modified, card_id, ' . $card_voter_fields;
while ($card_voter = pg_fetch_object($card_voters)) {
$card_voter_values = array();
array_push($card_voter_values, 'now()', 'now()', $new_card_id);
foreach ($card_voter as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($card_voter_values, 'false');
} else {
if ($value === null) {
array_push($card_voter_values, null);
} else {
array_push($card_voter_values, $value);
}
}
}
}
$card_voter_val = '';
for ($i = 1, $len = count($card_voter_values); $i <= $len; $i++) {
$card_voter_val .= '$' . $i;
$card_voter_val .= $i != $len ? ', ' : '';
}
$card_voter_result = pg_query_params($db_lnk, 'INSERT INTO card_voters (' . $card_voter_fields . ') VALUES (' . $card_voter_val . ') RETURNING id', $card_voter_values);
}
}
//Copy card labels
$cards_label_fields = 'label_id';
$qry_val_arr = array($card_id);
$cards_labels = pg_query_params($db_lnk, 'SELECT id, ' . $cards_label_fields . ' FROM cards_labels WHERE card_id = $1', $qry_val_arr);
if ($cards_labels && pg_num_rows($cards_labels)) {
$cards_label_fields = 'created, modified, board_id, list_id, card_id, ' . $cards_label_fields;
while ($cards_label = pg_fetch_object($cards_labels)) {
$cards_label_values = array();
array_push($cards_label_values, 'now()', 'now()', $new_board_id, $new_list_id, $new_card_id);
foreach ($cards_label as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($cards_label_values, 'false');
} else {
if ($value === null) {
array_push($cards_label_values, null);
} else {
array_push($cards_label_values, $value);
}
}
}
}
$cards_label_val = '';
for ($i = 1, $len = count($cards_label_values); $i <= $len; $i++) {
$cards_label_val .= '$' . $i;
$cards_label_val .= $i != $len ? ', ' : '';
}
$cards_label_result = pg_query_params($db_lnk, 'INSERT INTO cards_labels (' . $cards_label_fields . ') VALUES (' . $cards_label_val . ') RETURNING id', $cards_label_values);
}
}
//Copy card subscribers
$cards_subscriber_fields = 'user_id, is_subscribed';
$qry_val_arr = array($card_id);
$cards_subscribers = pg_query_params($db_lnk, 'SELECT id, ' . $cards_subscriber_fields . ' FROM card_subscribers WHERE card_id = $1', $qry_val_arr);
if ($cards_subscribers && pg_num_rows($cards_subscribers)) {
$cards_subscriber_fields = 'created, modified, card_id, ' . $cards_subscriber_fields;
while ($cards_subscriber = pg_fetch_object($cards_subscribers)) {
$cards_subscriber_values = array();
array_push($cards_subscriber_values, 'now()', 'now()', $new_card_id);
foreach ($cards_subscriber as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($cards_subscriber_values, 'false');
} else {
if ($value === null) {
array_push($cards_subscriber_values, null);
} else {
array_push($cards_subscriber_values, $value);
}
}
}
}
$cards_subscriber_val = '';
for ($i = 1, $len = count($cards_subscriber_values); $i <= $len; $i++) {
$cards_subscriber_val .= '$' . $i;
$cards_subscriber_val .= $i != $len ? ', ' : '';
}
$cards_subscriber_result = pg_query_params($db_lnk, 'INSERT INTO card_subscribers (' . $cards_subscriber_fields . ') VALUES (' . $cards_subscriber_val . ') RETURNING id', $cards_subscriber_values);
}
}
//Copy card users
$cards_user_fields = 'user_id';
$qry_val_arr = array($card_id);
$cards_users = pg_query_params($db_lnk, 'SELECT id, ' . $cards_user_fields . ' FROM cards_users WHERE card_id = $1', $qry_val_arr);
if ($cards_users && pg_num_rows($cards_users)) {
$cards_user_fields = 'created, modified, card_id, ' . $cards_user_fields;
while ($cards_user = pg_fetch_object($cards_users)) {
$cards_user_values = array();
array_push($cards_user_values, 'now()', 'now()', $new_card_id);
foreach ($cards_user as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($cards_user_values, 'false');
} else {
if ($value === null) {
array_push($cards_user_values, null);
} else {
array_push($cards_user_values, $value);
}
}
}
}
$cards_user_val = '';
for ($i = 1, $len = count($cards_user_values); $i <= $len; $i++) {
$cards_user_val .= '$' . $i;
$cards_user_val .= $i != $len ? ', ' : '';
}
$cards_user_result = pg_query_params($db_lnk, 'INSERT INTO cards_users (' . $cards_user_fields . ') VALUES (' . $cards_user_val . ') RETURNING id', $cards_user_values);
}
}
}
}
}
}
}
}
$qry_val_arr = array($r_resource_vars['boards']);
$sresult = pg_query_params($db_lnk, 'SELECT name FROM boards WHERE id = $1', $qry_val_arr);
$srow = pg_fetch_assoc($sresult);
$foreign_ids['board_id'] = $new_board_id;
$comment = '##USER_NAME## copied this board from ' . $srow['name'];
$response['activity'] = insertActivity($authUser['id'], $comment, 'copy_board', $foreign_ids, null, $r_resource_vars['boards']);
} else {
if ($r_resource_cmd == '/boards/?/lists/?/cards/?/checklists') {
if (isset($checklist_id) && !empty($checklist_id)) {
$qry_val_arr = array($r_post['user_id'], $response['id'], $checklist_id);
pg_query_params($db_lnk, 'INSERT INTO checklist_items (created, modified, user_id, card_id, checklist_id, name, is_completed, position) SELECT created, modified, $1, card_id, $2, name, false, position FROM checklist_items WHERE checklist_id = $3', $qry_val_arr);
}
$qry_val_arr = array($response['id']);
$result = pg_query_params($db_lnk, 'SELECT * FROM checklists_listing WHERE id = $1', $qry_val_arr);
$response['checklist'] = pg_fetch_assoc($result);
$response['checklist']['checklists_items'] = json_decode($response['checklist']['checklists_items'], true);
$foreign_ids['board_id'] = $r_resource_vars['boards'];
$foreign_ids['list_id'] = $r_resource_vars['lists'];
$foreign_ids['card_id'] = $r_resource_vars['cards'];
$comment = '##USER_NAME## added checklist ' . $response['checklist']['name'] . ' to this card ##CARD_LINK##';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_card_checklist', $foreign_ids, '', $response['id']);
} else {
if ($r_resource_cmd == '/boards/?/lists/?/cards/?/comments') {
$id_converted = base_convert($response['id'], 10, 36);
$materialized_path = sprintf("%08s", $id_converted);
if (!empty($prev_message['materialized_path'])) {
$materialized_path = $prev_message['materialized_path'] . '-' . $materialized_path;
}
if (!empty($prev_message['path'])) {
$path = $prev_message['path'] . '.P' . $response['id'];
$depth = $prev_message['depth'] + 1;
$root = $prev_message['root'];
$response['activities']['depth'] = $depth;
} else {
$path = 'P' . $response['id'];
$depth = 0;
$root = $response['id'];
}
$qry_val_arr = array($materialized_path, $path, $depth, $root, $response['id']);
pg_query_params($db_lnk, 'UPDATE activities SET materialized_path = $1, path = $2, depth = $3, root = $4 WHERE id = $5', $qry_val_arr);
$qry_val_arr = array($r_post['freshness_ts'], $root);
pg_query_params($db_lnk, 'UPDATE activities SET freshness_ts = $1 WHERE root = $2', $qry_val_arr);
$qry_val_arr = array($root);
$act_res = pg_query_params($db_lnk, 'SELECT * FROM activities WHERE root = $1', $qry_val_arr);
$response['activity'] = pg_fetch_assoc($act_res);
} else {
if ($r_resource_cmd == '/boards/?/lists/?/cards/?/copy') {
if ($is_keep_attachment) {
$qry_val_arr = array($response['id'], $r_post['list_id'], $r_post['board_id'], $copied_card_id);
pg_query_params($db_lnk, 'INSERT INTO card_attachments (created, modified, card_id, name, path, mimetype, list_id, board_id) SELECT created, modified, $1, name, path, mimetype, $2, $3 FROM card_attachments WHERE card_id = $4 ORDER BY id', $qry_val_arr);
}
if ($is_keep_user) {
$qry_val_arr = array($response['id'], $copied_card_id);
pg_query_params($db_lnk, 'INSERT INTO cards_users (created, modified, card_id, user_id) SELECT created, modified, $1, user_id FROM cards_users WHERE card_id = $2 ORDER BY id', $qry_val_arr);
}
if ($is_keep_label) {
$qry_val_arr = array($response['id'], $r_post['list_id'], $r_post['board_id'], $copied_card_id);
pg_query_params($db_lnk, 'INSERT INTO cards_labels (created, modified, card_id, label_id, list_id, board_id) SELECT created, modified, $1, label_id, $2, $3 FROM cards_labels WHERE card_id = $4 ORDER BY id', $qry_val_arr);
}
if ($is_keep_activity) {
$qry_val_arr = array($response['id'], $r_post['user_id'], $r_post['list_id'], $r_post['board_id'], $copied_card_id);
pg_query_params($db_lnk, 'INSERT INTO activities (created, modified, card_id, user_id, list_id, board_id, foreign_id, type, comment, revisions, root, freshness_ts, depth, path, materialized_path) SELECT created, modified, $1, $2, $3, $4, foreign_id, type, comment, revisions, root, freshness_ts, depth, path, materialized_path FROM activities WHERE type = \'add_comment\' AND card_id = $5 ORDER BY id', $qry_val_arr);
}
if ($is_keep_checklist) {
$qry_val_arr = array($response['id'], $copied_card_id);
pg_query_params($db_lnk, 'INSERT INTO checklists (created, modified, user_id, card_id, name, checklist_item_count, checklist_item_completed_count, position) SELECT created, modified, user_id, $1, name, checklist_item_count, checklist_item_completed_count, position FROM checklists WHERE card_id = $2 ORDER BY id', $qry_val_arr);
$qry_val_arr = array($response['id']);
$checklists = pg_query_params($db_lnk, 'SELECT id FROM checklists WHERE card_id = $1', $qry_val_arr);
$qry_val_arr = array($copied_card_id);
$prev_checklists = pg_query_params($db_lnk, 'SELECT id FROM checklists WHERE card_id = $1', $qry_val_arr);
$prev_checklist_ids = array();
while ($prev_checklist_id = pg_fetch_assoc($prev_checklists)) {
$prev_checklist_ids[] = $prev_checklist_id['id'];
}
$i = 0;
while ($checklist_id = pg_fetch_assoc($checklists)) {
$qry_val_arr = array($response['id'], $checklist_id['id'], $prev_checklist_ids[$i]);
pg_query_params($db_lnk, 'INSERT INTO checklist_items (created, modified, user_id, card_id, name, checklist_id, is_completed, position) SELECT created, modified, user_id, $1, name , $2, is_completed, position FROM checklist_items WHERE checklist_id = $3 ORDER BY id', $qry_val_arr);
$i++;
}
}
$foreign_ids['board_id'] = $r_post['board_id'];
$foreign_ids['list_id'] = $r_post['list_id'];
$foreign_ids['card_id'] = $response['id'];
$comment = '##USER_NAME## copied this card "' . $srow['name'] . '" to ##CARD_NAME##';
$response['activity'] = insertActivity($authUser['id'], $comment, 'copy_card', $foreign_ids, null, $response['id']);
$qry_val_arr = array($response['id']);
$response['cards'] = executeQuery('SELECT * FROM cards_listing WHERE id = $1', $qry_val_arr);
if (!empty($response['cards']['cards_checklists'])) {
$response['cards']['cards_checklists'] = json_decode($response['cards']['cards_checklists'], true);
}
if (!empty($response['cards']['cards_users'])) {
$response['cards']['cards_users'] = json_decode($response['cards']['cards_users'], true);
}
if (!empty($response['cards']['cards_voters'])) {
$response['cards']['cards_voters'] = json_decode($response['cards']['cards_voters'], true);
}
if (!empty($response['cards']['cards_subscribers'])) {
$response['cards']['cards_subscribers'] = json_decode($response['cards']['cards_subscribers'], true);
}
if (!empty($response['cards']['cards_labels'])) {
$response['cards']['cards_labels'] = json_decode($response['cards']['cards_labels'], true);
}
$qry_val_arr = array($response['id']);
$activities = executeQuery('SELECT ( SELECT array_to_json(array_agg(row_to_json(cl.*))) AS array_to_json FROM ( SELECT activities_listing.* FROM activities_listing activities_listing WHERE (activities_listing.card_id = cards.id) ORDER BY activities_listing.id DESC) cl) AS activities FROM cards cards WHERE id = $1', $qry_val_arr);
if (!empty($activities)) {
$response['cards']['activities'] = json_decode($activities['activities'], true);
}
$qry_val_arr = array($response['id']);
$attachments = pg_query_params($db_lnk, 'SELECT * FROM card_attachments WHERE card_id = $1', $qry_val_arr);
while ($attachment = pg_fetch_assoc($attachments)) {
$response['cards']['attachments'][] = $attachment;
}
} else {
if ($r_resource_cmd == '/boards/?/lists/?/cards/?/users/?') {
$qry_val_arr = array($r_post['card_id'], $r_post['user_id']);
$sel_query = 'SELECT cu.card_id, cu.user_id, users.username, c.board_id, c.list_id, b.name as board_name FROM cards_users cu LEFT JOIN cards c ON cu.card_id = c.id LEFT JOIN users ON cu.user_id = users.id LEFT JOIN boards b ON c.board_id = b.id WHERE cu.card_id = $1 AND cu.user_id = $2';
$get_details = pg_query_params($db_lnk, $sel_query, $qry_val_arr);
$sel_details = pg_fetch_assoc($get_details);
$foreign_ids['board_id'] = $sel_details['board_id'];
$foreign_ids['list_id'] = $sel_details['list_id'];
$foreign_ids['card_id'] = $r_post['card_id'];
$comment = '##USER_NAME## added "' . $sel_details['username'] . '" as member to this card ##CARD_LINK##';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_card_user', $foreign_ids, '', $response['id']);
} else {
if ($r_resource_cmd == '/boards/?/lists/?/cards/?/attachments') {
$foreign_ids['board_id'] = $r_post['board_id'];
$foreign_ids['list_id'] = $r_post['list_id'];
$foreign_ids['card_id'] = $r_post['card_id'];
$comment = '##USER_NAME## added attachment to this card ##CARD_LINK##';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_card_attachment', $foreign_ids, null, $response['id']);
foreach ($thumbsizes['CardAttachment'] as $key => $value) {
$mediadir = APP_PATH . '/client/img/' . $key . '/CardAttachment/' . $response['id'];
$list = glob($mediadir . '.*');
@unlink($list[0]);
}
} else {
if ($r_resource_cmd == '/boards/?/lists/?/cards/?/card_voters') {
$qry_val_arr = array($r_resource_vars['cards']);
$previous_value = executeQuery('SELECT name FROM cards WHERE id = $1', $qry_val_arr);
$foreign_ids['board_id'] = $r_resource_vars['boards'];
$foreign_ids['list_id'] = $r_resource_vars['lists'];
$foreign_ids['card_id'] = $r_post['card_id'];
$comment = '##USER_NAME## voted on ##CARD_LINK##';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_card_voter', $foreign_ids, '', $response['id']);
$qry_val_arr = array($response['id']);
$s_result = pg_query_params($db_lnk, 'SELECT * FROM card_voters_listing WHERE id = $1', $qry_val_arr);
$user = pg_fetch_assoc($s_result);
$response['card_voters'] = $user;
} else {
if ($r_resource_cmd == '/boards/?/users') {
$qry_val_arr = array($r_post['board_id']);
$s_result = pg_query_params($db_lnk, 'SELECT name FROM boards WHERE id = $1', $qry_val_arr);
$previous_value = pg_fetch_assoc($s_result);
$foreign_ids['board_id'] = $r_resource_vars['boards'];
$foreign_ids['board_id'] = $r_post['board_id'];
$qry_val_arr = array($r_post['user_id']);
$user = executeQuery('SELECT * FROM users WHERE id = $1', $qry_val_arr);
if ($user) {
$emailFindReplace = array('##NAME##' => $user['full_name'], '##CURRENT_USER##' => $authUser['full_name'], '##BOARD_NAME##' => $previous_value['name'], '##BOARD_URL##' => 'http://' . $_SERVER['HTTP_HOST'] . '/#/board/' . $r_post['board_id']);
sendMail('newprojectuser', $emailFindReplace, $user['email']);
}
$comment = '##USER_NAME## added member to board';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_board_user', $foreign_ids, '', $response['id']);
} else {
if ($r_resource_cmd == '/organizations/?/users/?') {
$qry_val_arr = array($response['id']);
$foreign_ids['organization_id'] = $r_post['organization_id'];
$foreign_id = $response['id'];
$comment = '##USER_NAME## added member to organization';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_organization_user', $foreign_ids, null, $foreign_id);
$response['organizations_users'] = executeQuery('SELECT * FROM organizations_users_listing WHERE id = $1', $qry_val_arr);
$response['organizations_users']['boards_users'] = json_decode($response['organizations_users']['boards_users'], true);
$qry_val_arr = array($r_post['organization_id']);
$boards = pg_query_params($db_lnk, 'SELECT * FROM boards WHERE organization_id = $1', $qry_val_arr);
while ($board = pg_fetch_assoc($boards)) {
if (!empty($board)) {
$qry_val_arr = array($board['id'], $r_post['user_id']);
$boards_users = pg_query_params($db_lnk, 'SELECT * FROM boards_users WHERE board_id = $1 AND user_id = $2', $qry_val_arr);
$boards_users = pg_fetch_assoc($boards_users);
if (empty($boards_users)) {
$qry_val_arr = array($board['id'], $r_post['user_id'], 2);
pg_query_params($db_lnk, 'INSERT INTO boards_users (created, modified, board_id , user_id, board_user_role_id) VALUES (now(), now(), $1, $2, $3)', $qry_val_arr);
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
// todo: $sql set as true query not execute, so add condition ($sql !== true)
if ($sql && $sql !== true && !empty($json) && !empty($response['id'])) {
if ($result = pg_query_params($db_lnk, $sql, array())) {
$data = array();
$count = pg_num_rows($result);
$i = 0;
while ($row = pg_fetch_row($result)) {
if ($i == 0 && $count > 1) {
echo '[';
}
echo $row[0];
$i++;
if ($i < $count) {
echo ',';
} else {
if ($count > 1) {
echo ']';
}
}
}
pg_free_result($result);
}
} else {
echo json_encode($response);
}
}
/**
* Copy Card
*
* @param array $card_fields Fields of the card
* @param array $cards Card record array
* @param integer $new_list_id List id of the new card
* @param string $name Card name
* @param integer $new_board_id Board id of the new card
*
* @return void
*/
function copyCards($card_fields, $cards, $new_list_id, $name, $new_board_id = '')
{
global $db_lnk, $authUser;
while ($card = pg_fetch_object($cards)) {
$card->list_id = $new_list_id;
$card_id = $card->id;
if ($card->due_date === null) {
unset($card->due_date);
}
$card_result = pg_execute_insert('cards', $card);
if ($card_result) {
$card_result = pg_fetch_assoc($card_result);
$new_card_id = $card_result['id'];
$foreign_ids['card_id'] = $new_card_id;
$foreign_ids['board_id'] = $new_board_id;
$foreign_ids['list_id'] = $new_list_id;
$comment = '##USER_NAME## added ' . $card_result['name'] . ' card to ' . $name . '.';
insertActivity($authUser['id'], $comment, 'add_card', $foreign_ids);
//Copy card attachments
$attachment_fields = 'list_id, card_id, name, path, mimetype';
if (!empty($new_board_id)) {
$attachment_fields = 'board_id, list_id, card_id, name, path, mimetype';
}
$qry_val_arr = array($card_id);
$attachments = pg_query_params($db_lnk, 'SELECT id, ' . $attachment_fields . ' FROM card_attachments WHERE card_id = $1 ORDER BY id', $qry_val_arr);
if ($attachments && pg_num_rows($attachments)) {
while ($attachment = pg_fetch_object($attachments)) {
$attachment->board_id = $new_board_id;
$attachment->list_id = $new_list_id;
$attachment->card_id = $new_card_id;
$attachment_result = pg_execute_insert('card_attachments', $attachment);
$attachment_result = pg_fetch_assoc($attachment_result);
$comment = '##USER_NAME## added attachment to this card ##CARD_LINK##';
insertActivity($authUser['id'], $comment, 'add_card_attachment', $foreign_ids, null, $attachment_result['id']);
}
}
//Copy card comments
$comment_fields = 'list_id, card_id, board_id, user_id, type, comment, root, freshness_ts, depth, path, materialized_path';
$qry_val_arr = array($card_id, 'add_comment');
$comments = pg_query_params($db_lnk, 'SELECT id, ' . $comment_fields . ' FROM activities WHERE card_id = $1 AND type = $2 ORDER BY id', $qry_val_arr);
if ($comments && pg_num_rows($comments)) {
while ($comment = pg_fetch_object($comments)) {
$comment->board_id = $new_board_id;
$comment->list_id = $new_list_id;
$comment->card_id = $new_card_id;
$card_result = pg_execute_insert('activities', $comment);
}
}
//Copy checklists
$checklist_fields = 'card_id, user_id, name, checklist_item_count, checklist_item_completed_count, position';
$qry_val_arr = array($card_id);
$checklists = pg_query_params($db_lnk, 'SELECT id, ' . $checklist_fields . ' FROM checklists WHERE card_id = $1 ORDER BY id', $qry_val_arr);
if ($checklists && pg_num_rows($checklists)) {
while ($checklist = pg_fetch_object($checklists)) {
$checklist_id = $checklist->id;
$checklist->card_id = $new_card_id;
$checklist_result = pg_execute_insert('checklists', $checklist);
if ($checklist_result) {
$checklist_result = pg_fetch_assoc($checklist_result);
$new_checklist_id = $checklist_result['id'];
$comment = '##USER_NAME## added checklist to this card ##CARD_LINK##';
insertActivity($authUser['id'], $comment, 'add_card_checklist', $foreign_ids, '', $new_checklist_id);
$copy_checklists[] = $checklist_result;
//Copy checklist items
$checklist_item_fields = 'card_id, checklist_id, user_id, name, position';
$qry_val_arr = array($checklist_id);
$checklist_items = pg_query_params($db_lnk, 'SELECT id, ' . $checklist_item_fields . ' FROM checklist_items WHERE checklist_id = $1 ORDER BY id', $qry_val_arr);
if ($checklist_items && pg_num_rows($checklist_items)) {
while ($checklist_item = pg_fetch_object($checklist_items)) {
$checklist_item->card_id = $new_card_id;
$checklist_item->checklist_id = $new_checklist_id;
$checklist_item_result = pg_execute_insert('checklist_items', $checklist_item);
$checklist_item_result = pg_fetch_assoc($checklist_item_result);
$copy_checklists_items[] = $checklist_item_result;
$comment = '##USER_NAME## added checklist item to this card ##CARD_LINK##';
insertActivity($authUser['id'], $comment, 'add_checklist_item', $foreign_ids, '', $checklist_item_result['id']);
}
}
}
}
}
//Copy card labels
$cards_label_fields = 'list_id, card_id, board_id, label_id';
if (!empty($new_board_id)) {
$cards_label_fields = 'board_id, list_id, card_id, label_id';
}
$qry_val_arr = array($card_id);
$cards_labels = pg_query_params($db_lnk, 'SELECT id, ' . $cards_label_fields . ' FROM cards_labels WHERE card_id = $1 ORDER BY id', $qry_val_arr);
if ($cards_labels && pg_num_rows($cards_labels)) {
while ($cards_label = pg_fetch_object($cards_labels)) {
if (!empty($new_board_id)) {
$cards_label->board_id = $new_board_id;
$cards_label->list_id = $new_list_id;
$cards_label->card_id = $new_card_id;
$cards_label_values = $new_board_id . ', ' . $new_list_id . ', ' . $new_card_id;
} else {
$cards_label_values = $new_list_id . ', ' . $new_card_id;
}
$cards_label_result = pg_execute_insert('cards_labels', $cards_label);
$cards_label_result = pg_fetch_assoc($cards_label_result);
$comment = '##USER_NAME## added label(s) to this card ##CARD_LINK## - ##LABEL_NAME##';
insertActivity($authUser['id'], $comment, 'add_card_label', $foreign_ids);
}
}
//Copy card users
$cards_user_fields = 'card_id, user_id';
$qry_val_arr = array($card_id);
$cards_users = pg_query_params($db_lnk, 'SELECT id, ' . $cards_user_fields . ' FROM cards_users WHERE card_id = $1 ORDER BY id', $qry_val_arr);
if ($cards_users && pg_num_rows($cards_users)) {
while ($cards_user = pg_fetch_object($cards_users)) {
$cards_user->card_id = $new_card_id;
$cards_user_result = pg_execute_insert('cards_users', $cards_user);
$cards_user_result = pg_fetch_assoc($cards_user_result);
$qry_val_arr = array($cards_user->user_id);
$_user = executeQuery('SELECT username FROM users WHERE id = $1', $qry_val_arr);
$comment = '##USER_NAME## added ' . $_user['username'] . ' as member to this card ##CARD_LINK##';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_card_user', $foreign_ids, '', $cards_user_result['id']);
}
}
}
}
}
/**
* Common method to handle POST method
*
* @param string $r_resource_cmd URL
* @param array $r_resource_vars Array generated from URL
* @param array $r_resource_filters Array generated from URL query string
* @param array $r_post Post data
*
* @return mixed
*/
function r_post($r_resource_cmd, $r_resource_vars, $r_resource_filters, $r_post)
{
global $r_debug, $db_lnk, $authUser, $thumbsizes, $_server_domain_url;
$emailFindReplace = $response = array();
$fields = 'created, modified';
$values = 'now(), now()';
$json = $sql = $is_return_vlaue = false;
$uuid = '';
if (isset($r_post['uuid'])) {
$uuid = $r_post['uuid'];
}
unset($r_post['temp_id']);
unset($r_post['uuid']);
unset($r_post['id']);
switch ($r_resource_cmd) {
case '/users/forgotpassword':
//users forgot password
$val_arr = array($r_post['email']);
$user = executeQuery('SELECT * FROM users WHERE email = $1', $val_arr);
if ($user) {
$password = uniqid();
$val_arr = array(getCryptHash($password), $user['id']);
pg_query_params($db_lnk, 'UPDATE users SET (password) = ($1) WHERE id = $2', $val_arr);
$emailFindReplace = array('mail' => 'forgetpassword', '##USERNAME##' => $user['username'], '##PASSWORD##' => $password, 'to' => $user['email']);
$response = array('success' => 'An email has been sent with your new password.');
sendMail($emailFindReplace);
} else {
$response = array('error' => 'Please enter valid email id.');
}
break;
case '/users':
//Admin user add
$table_name = 'users';
$val_arr = array($r_post['username'], $r_post['email']);
$user = executeQuery('SELECT * FROM users WHERE username = $1 OR email = $2', $val_arr);
if (!$user) {
$sql = true;
$table_name = 'users';
$r_post['password'] = getCryptHash($r_post['password']);
$r_post['role_id'] = 2;
// user
$r_post['is_active'] = true;
$r_post['is_email_confirmed'] = true;
$r_post['role_id'] = 2;
// user
$r_post['initials'] = strtoupper(substr($r_post['username'], 0, 1));
$r_post['ip_id'] = saveIp();
} else {
$msg = '';
if ($user['email'] == $r_post['email']) {
$msg = 'Email address already exists. Your registration process is not completed. Please, try again.';
} else {
if ($user['username'] == $r_post['username']) {
$msg = 'Username already exists. Your registration process is not completed. Please, try again.';
}
}
$response = array('error' => $msg);
}
break;
case '/users/register':
//users register
$table_name = 'users';
$val_arr = array($r_post['username'], $r_post['email']);
$user = executeQuery('SELECT * FROM users WHERE username = $1 OR email = $2', $val_arr);
if (!$user) {
$sql = true;
$table_name = 'users';
$r_post['password'] = getCryptHash($r_post['password']);
$r_post['role_id'] = 2;
// user
$r_post['initials'] = strtoupper(substr($r_post['username'], 0, 1));
$r_post['ip_id'] = saveIp();
} else {
$msg = '';
if ($user['email'] == $r_post['email']) {
$msg = 'Email address is already exist. Your registration process is not completed. Please, try again.';
} else {
if ($user['username'] == $r_post['username']) {
$msg = 'Username address is already exist. Your registration process is not completed. Please, try again.';
}
}
$response = array('error' => $msg);
}
break;
case '/users/login':
//users login
$is_login = false;
$user = array();
$table_name = 'users';
$val_arr = array($r_post['email']);
$log_user = executeQuery('SELECT * FROM users WHERE email = $1 or username = $1', $val_arr);
if (LDAP_LOGIN_ENABLED && (empty($log_user) || !empty($log_user) && $log_user['role_id'] != 1 && $log_user['is_ldap'] == 't')) {
$check_user = ldapAuthenticate($r_post['email'], $r_post['password']);
if (!empty($check_user['User']) && $check_user['User']['is_username_exits'] && $check_user['User']['is_password_matched'] && isset($check_user['User']['email']) && !empty($check_user['User']['email'])) {
$val_arr = array($check_user['User']['email']);
$user = executeQuery('SELECT * FROM users_listing WHERE email = $1', $val_arr);
if (!$user) {
$r_post['password'] = getCryptHash($r_post['password']);
$r_post['role_id'] = 2;
// user
$val_arr = array($r_post['email'], $check_user['User']['email'], $r_post['password'], strtoupper(substr($r_post['email'], 0, 1)));
$result = pg_query_params($db_lnk, 'INSERT INTO ' . $table_name . ' (created, modified, role_id, username, email, password, initials, is_active, is_email_confirmed, is_ldap) VALUES (now(), now(), 2, $1, $2, $3, $4, true, true, true) RETURNING * ', $val_arr);
$user = pg_fetch_assoc($result);
$val_arr = array($user['id']);
$user = executeQuery('SELECT * FROM users_listing WHERE id = $1', $val_arr);
}
}
} else {
if ($log_user && $log_user['is_ldap'] == 'f' && STANDARD_LOGIN_ENABLED) {
$r_post['password'] = crypt($r_post['password'], $log_user['password']);
$val_arr = array($r_post['email'], $r_post['password'], true);
$user = executeQuery('SELECT * FROM users_listing WHERE (email = $1 or username = $1) AND password = $2 AND is_active = $3', $val_arr);
}
}
if (!empty($user)) {
if (LDAP_LOGIN_ENABLED) {
$login_type_id = 1;
} else {
$login_type_id = 2;
}
$last_login_ip_id = saveIp();
$val_arr = array($login_type_id, $last_login_ip_id, $user['id']);
pg_query_params($db_lnk, 'UPDATE users SET last_login_date = now(), login_type_id = $1, last_login_ip_id = $2 WHERE id = $3', $val_arr);
unset($user['password']);
$user_agent = !empty($_SERVER['HTTP_USER_AGENT']) ? $_SERVER['HTTP_USER_AGENT'] : '';
$val_arr = array($user['id'], $last_login_ip_id, $user_agent);
pg_query_params($db_lnk, 'INSERT INTO user_logins (created, modified, user_id, ip_id, user_agent) VALUES (now(), now(), $1, $2, $3)', $val_arr);
$role_val_arr = array($user['role_id']);
$role_links = executeQuery('SELECT * FROM role_links_listing WHERE id = $1', $role_val_arr);
$post_url = $_server_domain_url . str_replace('r.php', 'token.php', $_SERVER['PHP_SELF']);
$post_arr = array('grant_type' => 'password', 'username' => $user['username'], 'password' => $r_post['password'], 'client_id' => OAUTH_CLIENTID, 'client_secret' => OAUTH_CLIENT_SECRET);
$response = doPost($post_url, $post_arr);
$response = array_merge($role_links, $response);
$board_ids = array();
if (!empty($user['boards_users'])) {
$boards_users = json_decode($user['boards_users'], true);
foreach ($boards_users as $boards_user) {
$board_ids[] = $boards_user['board_id'];
}
}
$notify_val_arr = array($user['last_activity_id'], '{' . implode(',', $board_ids) . '}');
$notify_count = executeQuery('SELECT count(a.*) AS notify_count FROM activities a WHERE a.id > $1 AND board_id = ANY ($2) ', $notify_val_arr);
$user = array_merge($user, $notify_count);
$response['user'] = $user;
$response['user']['organizations'] = json_decode($user['organizations'], true);
} else {
$response = array('error' => 'Sorry, login failed. Either your username or password are incorrect or admin deactivated your account.');
}
break;
case '/users/?/changepassword':
$qry_val_array = array($r_resource_vars['users']);
$user = executeQuery('SELECT * FROM users WHERE id = $1', $qry_val_array);
if ($user) {
$cry_old_pass = crypt($r_post['old_password'], $user['password']);
if ($authUser['role_id'] == 2 && $user['password'] == $cry_old_pass || $authUser['role_id'] == 1) {
$res_val_arr = array(getCryptHash($r_post['password']), $r_resource_vars['users']);
$result = pg_query_params($db_lnk, 'UPDATE users SET (password) = ($1) WHERE id = $2', $res_val_arr);
if ($authUser['role_id'] == 1) {
$emailFindReplace = array('to' => $user['email'], 'mail' => 'changepassword', '##PASSWORD##' => $r_post['password']);
sendMail($emailFindReplace);
$response = array('success' => 'Password change successfully. Please login.');
}
} else {
$response = array('error' => 'Invalid old password.');
}
} else {
$response = array('error' => 'Unable to change password. Please try again.');
}
break;
case '/users/?':
$is_return_vlaue = true;
$profile_picture_path = 'null';
$no_error = true;
if (!empty($_FILES['attachment']['name']) && $_FILES['attachment']['error'] == 0) {
$mediadir = APP_PATH . DIRECTORY_SEPARATOR . 'media' . DIRECTORY_SEPARATOR . 'User' . DIRECTORY_SEPARATOR . $r_resource_vars['users'];
$save_path = 'media' . DIRECTORY_SEPARATOR . 'User' . DIRECTORY_SEPARATOR . $r_resource_vars['users'];
if (!file_exists($mediadir)) {
mkdir($mediadir, 0777, true);
}
$file = $_FILES['attachment'];
$file['name'] = preg_replace('/[^A-Za-z0-9\\-.]/', '', $file['name']);
if (move_uploaded_file($file['tmp_name'], $mediadir . DIRECTORY_SEPARATOR . $file['name'])) {
$profile_picture_path = $save_path . DIRECTORY_SEPARATOR . $file['name'];
foreach ($thumbsizes['User'] as $key => $value) {
$mediadir = APP_PATH . '/client/img/' . $key . '/User/' . $r_resource_vars['users'];
$list = glob($mediadir . '.*');
@unlink($list[0]);
}
$authUser['profile_picture_path'] = $profile_picture_path;
$response['profile_picture_path'] = $profile_picture_path;
$comment = '##USER_NAME## update profile image';
$foreign_ids['user_id'] = $authUser['id'];
$response['activity'] = insertActivity($authUser['id'], $comment, 'update_profile_attachment', $foreign_ids);
}
$qry_val_arr = array($profile_picture_path, $r_resource_vars['users']);
pg_query_params($db_lnk, 'UPDATE users SET profile_picture_path = $1 WHERE id = $2', $qry_val_arr);
} else {
if (!empty($_POST['email'])) {
$usr_val_arr = array($_POST['email']);
$user = executeQuery('SELECT * FROM users WHERE email = $1', $usr_val_arr);
if ($user['id'] != $r_resource_vars['users'] && $user['email'] == $_POST['email']) {
$no_error = false;
$msg = 'Email address is already exist. User Profile could not be updated. Please, try again.';
}
}
if ($no_error) {
$_POST['initials'] = strtoupper($_POST['initials']);
$qry_val_arr = array($_POST['full_name'], $_POST['about_me'], $_POST['initials'], $r_resource_vars['users']);
$comment = '##USER_NAME## update profile.';
$foreign_ids['user_id'] = $authUser['id'];
$response['activity'] = insertActivity($authUser['id'], $comment, 'update_profile', $foreign_ids);
pg_query_params($db_lnk, 'UPDATE users SET full_name = $1, about_me = $2, initials = $3 WHERE id = $4', $qry_val_arr);
if (!empty($_POST['email'])) {
$qry_val_arr = array($_POST['email'], $r_resource_vars['users']);
pg_query_params($db_lnk, 'UPDATE users SET email= $1 WHERE id = $2', $qry_val_arr);
}
}
}
if ($no_error) {
$response['success'] = 'User Profile has been updated.';
} else {
$response['error'] = $msg;
}
break;
case '/settings':
//settings update
foreach ($r_post as $key => $value) {
$qry_val_arr = array($value, trim($key));
pg_query_params($db_lnk, 'UPDATE settings SET value = $1 WHERE name = $2', $qry_val_arr);
}
$response = array('success' => 'Settings updated successfully.');
break;
case '/boards':
//boards add
$is_import_board = false;
if (!empty($_FILES['board_import'])) {
if ($_FILES['board_import']['error'] == 0) {
$get_files = file_get_contents($_FILES['board_import']['tmp_name']);
$imported_board = json_decode($get_files, true);
if (!empty($imported_board)) {
$board = importTrelloBoard($imported_board);
$response['id'] = $board['id'];
} else {
$response['error'] = 'Unable to import. please try again.';
}
} else {
$response['error'] = 'Unable to import. please try again.';
}
} else {
$table_name = 'boards';
$qry_val_arr = array($r_post['name']);
$board = executeQuery('SELECT id, name FROM ' . $table_name . ' WHERE name = $1', $qry_val_arr);
if (isset($r_post['template']) && !empty($r_post['template'])) {
$lists = explode(',', $r_post['template']);
}
unset($r_post['template']);
$sql = true;
$r_post['user_id'] = !empty($authUser['id']) ? $authUser['id'] : 1;
}
break;
case '/boards/?/boards_stars':
//stars add
$table_name = 'board_stars';
$qry_val_arr = array($r_resource_vars['boards'], $authUser['id']);
$subcriber = executeQuery('SELECT id, is_starred FROM ' . $table_name . ' WHERE board_id = $1 and user_id = $2', $qry_val_arr);
if (!$subcriber) {
$qry_val_arr = array($r_resource_vars['boards'], $authUser['id']);
$result = pg_query_params($db_lnk, 'INSERT INTO ' . $table_name . ' (created, modified, board_id, user_id, is_starred) VALUES (now(), now(), $1, $2, true) RETURNING id', $qry_val_arr);
} else {
if ($subcriber['is_starred'] == 't') {
$qry_val_arr = array($r_resource_vars['boards'], $authUser['id']);
$result = pg_query_params($db_lnk, 'UPDATE ' . $table_name . ' SET is_starred = false Where board_id = $1 and user_id = $2 RETURNING id', $qry_val_arr);
} else {
$qry_val_arr = array($r_resource_vars['boards'], $authUser['id']);
$result = pg_query_params($db_lnk, 'UPDATE ' . $table_name . ' SET is_starred = True Where board_id = $1 and user_id = $2 RETURNING id', $qry_val_arr);
}
}
$star = pg_fetch_assoc($result);
$response['id'] = $star['id'];
break;
case '/boards/?/board_subscribers':
//subscriber add
$table_name = 'board_subscribers';
$qry_val_arr = array($r_resource_vars['boards'], $authUser['id']);
$subcriber = executeQuery('SELECT id, is_subscribed FROM ' . $table_name . ' WHERE board_id = $1 and user_id = $2', $qry_val_arr);
if (!$subcriber) {
$qry_val_arr = array($r_resource_vars['boards'], $authUser['id']);
$result = pg_query_params($db_lnk, 'INSERT INTO ' . $table_name . ' (created, modified, board_id, user_id, is_subscribed) VALUES (now(), now(), $1, $2, true) RETURNING *', $qry_val_arr);
} else {
if ($subcriber['is_subscribed'] == 't') {
$qry_val_arr = array($r_resource_vars['boards'], $authUser['id']);
$result = pg_query_params($db_lnk, 'UPDATE ' . $table_name . ' SET is_subscribed = false Where board_id = $1 and user_id = $2 RETURNING *', $qry_val_arr);
} else {
$qry_val_arr = array($r_resource_vars['boards'], $authUser['id']);
$result = pg_query_params($db_lnk, 'UPDATE ' . $table_name . ' SET is_subscribed = True Where board_id = $1 and user_id = $2 RETURNING *', $qry_val_arr);
}
}
$response = pg_fetch_assoc($result);
break;
case '/boards/?/copy':
//boards copy
$table_name = 'boards';
$sql = true;
$copied_board_id = $r_resource_vars['boards'];
$board_visibility = $r_post['board_visibility'];
if (!empty($r_post['organization_id'])) {
$organization_id = $r_post['organization_id'];
}
$keepcards = false;
if (!empty($r_post['keepCards'])) {
$keepcards = true;
unset($r_post['keepCards']);
}
$qry_val_arr = array($copied_board_id);
$sresult = pg_query_params($db_lnk, 'SELECT * FROM boards WHERE id = $1', $qry_val_arr);
$srow = pg_fetch_assoc($sresult);
unset($srow['id']);
unset($srow['created']);
unset($srow['modified']);
unset($srow['user_id']);
unset($srow['name']);
if ($srow['commenting_permissions'] === null) {
$srow['commenting_permissions'] = 0;
}
if ($srow['voting_permissions'] === null) {
$srow['voting_permissions'] = 0;
}
if ($srow['inivitation_permissions'] === null) {
$srow['inivitation_permissions'] = 0;
}
$r_post = array_merge($r_post, $srow);
$r_post['board_visibility'] = $board_visibility;
if (!empty($organization_id)) {
$r_post['organization_id'] = $organization_id;
}
break;
case '/boards/?/custom_backgrounds':
$is_return_vlaue = true;
if (!empty($_FILES['attachment']) && $_FILES['attachment']['error'] == 0) {
$mediadir = APP_PATH . DIRECTORY_SEPARATOR . 'media' . DIRECTORY_SEPARATOR . 'Board' . DIRECTORY_SEPARATOR . $r_resource_vars['boards'];
$save_path = 'media' . DIRECTORY_SEPARATOR . 'Board' . DIRECTORY_SEPARATOR . $r_resource_vars['boards'];
if (!file_exists($mediadir)) {
mkdir($mediadir, 0777, true);
}
$file = $_FILES['attachment'];
$file['name'] = preg_replace('/[^A-Za-z0-9\\-.]/', '', $file['name']);
if (move_uploaded_file($file['tmp_name'], $mediadir . DIRECTORY_SEPARATOR . $file['name'])) {
$r_post['name'] = $file['name'];
foreach ($thumbsizes['Board'] as $key => $value) {
$mediadir = APP_PATH . DIRECTORY_SEPARATOR . 'client' . DIRECTORY_SEPARATOR . 'img' . DIRECTORY_SEPARATOR . $key . DIRECTORY_SEPARATOR . 'Board' . DIRECTORY_SEPARATOR . $r_resource_vars['boards'];
$list = glob($mediadir . '.*');
@unlink($list[0]);
}
$hash = md5(SECURITYSALT . 'Board' . $r_resource_vars['boards'] . 'jpg' . 'extra_large_thumb' . SITE_NAME);
$background_picture_url = $_server_domain_url . '/img/extra_large_thumb/Board/' . $r_resource_vars['boards'] . '.' . $hash . '.jpg';
$r_post['background_picture_path'] = $save_path . DIRECTORY_SEPARATOR . $file['name'];
$r_post['path'] = $background_picture_url;
$response['background_picture_url'] = $background_picture_url;
}
$qry_val_array = array($r_post['path'], $r_post['background_picture_path'], $r_resource_vars['boards']);
pg_query_params($db_lnk, 'UPDATE boards SET background_picture_url = $1,background_picture_path = $2 WHERE id = $3', $qry_val_array);
}
break;
case '/boards/?/users':
$is_return_vlaue = true;
$table_name = 'boards_users';
$qry_val_arr = array($r_resource_vars['boards'], $r_post['user_id']);
$boards_user = executeQuery('SELECT * FROM boards_users WHERE board_id = $1 AND user_id = $2', $qry_val_arr);
if (empty($boards_user)) {
$sql = true;
}
break;
case '/boards/?/lists':
$table_name = 'lists';
$r_post['board_id'] = $r_resource_vars['boards'];
$r_post['user_id'] = $authUser['id'];
$sql = true;
if (isset($r_post['clone_list_id'])) {
$clone_list_id = $r_post['clone_list_id'];
unset($r_post['clone_list_id']);
unset($r_post['list_cards']);
}
break;
case '/boards/?/lists/?/list_subscribers':
$table_name = 'list_subscribers';
$r_post['user_id'] = $authUser['id'];
$qry_val_arr = array($r_resource_vars['lists'], $r_post['user_id']);
$s_result = pg_query_params($db_lnk, 'SELECT is_subscribed FROM list_subscribers WHERE list_id = $1 and user_id = $2', $qry_val_arr);
$check_subscribed = pg_fetch_assoc($s_result);
if (!empty($check_subscribed)) {
$is_subscribed = $r_post['is_subscribed'] ? true : false;
$qry_val_arr = array($is_subscribed, $r_resource_vars['lists'], $r_post['user_id']);
$s_result = pg_query_params($db_lnk, 'UPDATE list_subscribers SET is_subscribed = $1 WHERE list_id = $2 and user_id = $3', $qry_val_arr);
} else {
$r_post['list_id'] = $r_resource_vars['lists'];
$sql = true;
}
break;
case '/boards/?/lists/?/cards':
$table_name = 'cards';
$r_post['user_id'] = $authUser['id'];
$qry_val_arr = array($r_post['board_id'], $r_post['list_id']);
$pos_res = pg_query_params($db_lnk, 'SELECT position FROM cards WHERE board_id = $1 AND list_id = $2 ORDER BY position DESC LIMIT 1', $qry_val_arr);
$position = pg_fetch_array($pos_res);
if (empty($r_post['due_date'])) {
unset($r_post['due_date']);
}
if (!empty($r_post['user_ids'])) {
$r_post['members'] = explode(',', $r_post['user_ids']);
}
if (!isset($r_post['position'])) {
$r_post['position'] = $position[0] + 1;
}
$sql = true;
break;
case '/boards/?/lists/?/cards/?/comments':
$is_return_vlaue = true;
$table_name = 'activities';
$sql = true;
$prev_message = array();
if (isset($r_post['root']) && !empty($r_post['root'])) {
$qry_val_arr = array($r_post['root']);
$prev_message = executeQuery('SELECT ac.*, u,username, u.profile_picture_path, u.initials FROM activities ac LEFT JOIN users u ON ac.user_id = u.id WHERE ac.id = $1', $qry_val_arr);
}
$r_post['freshness_ts'] = date('Y-m-d h:i:s');
$r_post['type'] = 'add_comment';
break;
case '/boards/?/lists/?/cards/?/card_subscribers':
$table_name = 'card_subscribers';
$json = true;
$r_post['user_id'] = $authUser['id'];
unset($r_post['list_id']);
unset($r_post['board_id']);
$qry_val_arr = array($r_resource_vars['cards'], $r_post['user_id']);
$s_result = pg_query_params($db_lnk, 'SELECT is_subscribed FROM card_subscribers WHERE card_id = $1 and user_id = $2', $qry_val_arr);
$check_subscribed = pg_fetch_assoc($s_result);
if (!empty($check_subscribed)) {
$is_subscribed = $r_post['is_subscribed'] ? true : false;
$qry_val_arr = array($is_subscribed, $r_resource_vars['cards'], $r_post['user_id']);
$s_result = pg_query_params($db_lnk, 'UPDATE card_subscribers SET is_subscribed = $1 WHERE card_id = $2 and user_id = $3 RETURNING id', $qry_val_arr);
$subscribe = pg_fetch_assoc($s_result);
$response['id'] = $subscribe['id'];
} else {
$r_post['card_id'] = $r_resource_vars['cards'];
$r_post['user_id'] = $r_post['user_id'];
$sql = true;
}
break;
case '/boards/?/lists/?/cards/?/card_voters':
$table_name = 'card_voters';
$r_post['card_id'] = $r_resource_vars['cards'];
$r_post['user_id'] = $authUser['id'];
$sql = true;
break;
case '/boards/?/lists/?/cards/?/attachments':
$is_return_vlaue = true;
$table_name = 'card_attachments';
$r_post['card_id'] = $r_resource_vars['cards'];
$r_post['list_id'] = $r_resource_vars['lists'];
$r_post['board_id'] = $r_resource_vars['boards'];
$mediadir = APP_PATH . DIRECTORY_SEPARATOR . 'media' . DIRECTORY_SEPARATOR . 'Card' . DIRECTORY_SEPARATOR . $r_resource_vars['cards'];
$save_path = 'media' . DIRECTORY_SEPARATOR . 'Card' . DIRECTORY_SEPARATOR . $r_resource_vars['cards'];
$save_path = str_replace('\\', '/', $save_path);
if (!empty($_FILES['attachment']) && $_FILES['attachment']['error'] == 0) {
if (!file_exists($mediadir)) {
mkdir($mediadir, 0777, true);
}
$file = $_FILES['attachment'];
if (move_uploaded_file($file['tmp_name'], $mediadir . DIRECTORY_SEPARATOR . $file['name'])) {
$r_post['path'] = $save_path . '/' . $file['name'];
$r_post['name'] = $file['name'];
$r_post['mimetype'] = $file['type'];
$qry_val_arr = array($r_post['card_id'], $r_post['name'], $r_post['path'], $r_post['list_id'], $r_post['board_id'], $r_post['mimetype']);
$s_result = pg_query_params($db_lnk, 'INSERT INTO card_attachments (created, modified, card_id, name, path, list_id, board_id, mimetype) VALUES (now(), now(), $1, $2, $3, $4, $5, $6) RETURNING *', $qry_val_arr);
$response['card_attachments'][] = pg_fetch_assoc($s_result);
}
foreach ($thumbsizes['CardAttachment'] as $key => $value) {
$mediadir = APP_PATH . '/client/img/' . $key . '/CardAttachment/' . $response['card_attachments'][0]['id'];
$list = glob($mediadir . '.*');
@unlink($list[0]);
}
$foreign_ids['board_id'] = $r_resource_vars['boards'];
$foreign_ids['list_id'] = $r_resource_vars['lists'];
$foreign_ids['card_id'] = $r_resource_vars['cards'];
$comment = '##USER_NAME## added attachment to this card ##CARD_LINK##';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_card_attachment', $foreign_ids, null, $response['card_attachments'][0]['id']);
} else {
if (!empty($_FILES['attachment']) && is_array($_FILES['attachment']['name']) && $_FILES['attachment']['error'][0] == 0) {
$file = $_FILES['attachment'];
for ($i = 0; $i < count($file['name']); $i++) {
if (!file_exists($mediadir)) {
mkdir($mediadir, 0777, true);
}
if (move_uploaded_file($file['tmp_name'][$i], $mediadir . DIRECTORY_SEPARATOR . $file['name'][$i])) {
$r_post[$i]['path'] = $save_path . DIRECTORY_SEPARATOR . $file['name'][$i];
$r_post[$i]['name'] = $file['name'][$i];
$r_post[$i]['mimetype'] = $file['type'][$i];
$qry_val_arr = array($r_post['card_id'], $r_post[$i]['name'], $r_post[$i]['path'], $r_post['list_id'], $r_post['board_id'], $r_post[$i]['mimetype']);
$s_result = pg_query_params($db_lnk, 'INSERT INTO card_attachments (created, modified, card_id, name, path, list_id, board_id, mimetype) VALUES (now(), now(), $1, $2, $3, $4, $5, $6) RETURNING *', $qry_val_arr);
$response['card_attachments'][] = pg_fetch_assoc($s_result);
$foreign_ids['board_id'] = $r_resource_vars['boards'];
$foreign_ids['list_id'] = $r_resource_vars['lists'];
$foreign_ids['card_id'] = $r_resource_vars['cards'];
$comment = '##USER_NAME## added attachment to this card ##CARD_LINK##';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_card_attachment', $foreign_ids, null, $response['card_attachments'][$i]['id']);
foreach ($thumbsizes['CardAttachment'] as $key => $value) {
$mediadir = APP_PATH . '/client/img/' . $key . '/CardAttachment/' . $response['card_attachments'][$i]['id'];
$list = glob($mediadir . '.*');
@unlink($list[0]);
}
}
}
} else {
if (isset($r_post['image_link']) && !empty($r_post['image_link'])) {
$filename = curlExecute($r_post['image_link'], 'get', $mediadir, 'image');
$sql = true;
unset($r_post['image_link']);
$r_post['path'] = $save_path . '/' . $filename;
$r_post['name'] = $filename;
}
}
}
break;
case '/boards/?/lists/?/cards/?/labels':
$is_return_vlaue = true;
$table_name = 'cards_labels';
$r_post['card_id'] = $r_resource_vars['cards'];
$r_post['list_id'] = $r_resource_vars['lists'];
$r_post['board_id'] = $r_resource_vars['boards'];
$qry_val_arr = array($r_resource_vars['cards']);
$delete_labels = pg_query_params($db_lnk, 'DELETE FROM ' . $table_name . ' WHERE card_id = $1', $qry_val_arr);
$delete_labels_count = pg_affected_rows($delete_labels);
if (!empty($r_post['name'])) {
$label_names = explode(',', $r_post['name']);
unset($r_post['name']);
foreach ($label_names as $label_name) {
$qry_val_arr = array($label_name);
$s_result = pg_query_params($db_lnk, 'SELECT id FROM labels WHERE name = $1', $qry_val_arr);
$label = pg_fetch_assoc($s_result);
if (empty($label)) {
$qry_val_arr = array($label_name);
$s_result = pg_query_params($db_lnk, 'INSERT INTO labels (created, modified, name) VALUES (now(), now(), $1) RETURNING id', $qry_val_arr);
$label = pg_fetch_assoc($s_result);
}
$r_post['label_id'] = $label['id'];
$qry_val_arr = array($r_post['card_id'], $r_post['label_id'], $r_post['board_id'], $r_post['list_id']);
pg_query_params($db_lnk, 'INSERT INTO ' . $table_name . ' (created, modified, card_id, label_id, board_id, list_id) VALUES (now(), now(), $1, $2, $3, $4) RETURNING *', $qry_val_arr);
}
$qry_val_arr = array($r_post['card_id']);
$s_result = pg_query_params($db_lnk, 'SELECT * FROM cards_labels_listing WHERE card_id = $1', $qry_val_arr);
$cards_labels = pg_fetch_all($s_result);
$response['cards_labels'] = $cards_labels;
$comment = '##USER_NAME## added label(s) to this card ##CARD_LINK## - ##LABEL_NAME##';
} else {
$response['cards_labels'] = array();
$comment = '##USER_NAME## removed label(s) in this card ##CARD_LINK## - ##LABEL_NAME##';
}
$foreign_ids['board_id'] = $r_post['board_id'];
$foreign_ids['list_id'] = $r_post['list_id'];
$foreign_ids['card_id'] = $r_post['card_id'];
if (!empty($delete_labels_count)) {
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_card_label', $foreign_ids, null, $r_post['label_id']);
}
break;
case '/boards/?/lists/?/cards/?/checklists':
$sql = true;
$table_name = 'checklists';
$r_post['user_id'] = $authUser['id'];
$r_post['card_id'] = $r_resource_vars['cards'];
if (isset($r_post['checklist_id'])) {
$checklist_id = $r_post['checklist_id'];
unset($r_post['checklist_id']);
}
break;
case '/boards/?/lists/?/cards/?/checklists/?/items':
$table_name = 'checklist_items';
$is_return_vlaue = true;
$r_post['user_id'] = $authUser['id'];
$r_post['card_id'] = $r_resource_vars['cards'];
$r_post['checklist_id'] = $r_resource_vars['checklists'];
unset($r_post['created']);
unset($r_post['modified']);
unset($r_post['is_offline']);
unset($r_post['list_id']);
unset($r_post['board_id']);
$names = explode("\n", $r_post['name']);
foreach ($names as $name) {
$r_post['name'] = trim($name);
if (!empty($r_post['name'])) {
$qry_val_arr = array($r_post['checklist_id']);
$position = executeQuery('SELECT max(position) as position FROM checklist_items WHERE checklist_id = $1', $qry_val_arr);
$r_post['position'] = $position['position'];
if (empty($r_post['position'])) {
$r_post['position'] = 0;
}
$r_post['position'] += 1;
$result = pg_execute_insert($table_name, $r_post);
$item = pg_fetch_assoc($result);
$response[$table_name][] = $item;
$foreign_ids['board_id'] = $r_resource_vars['boards'];
$foreign_ids['list_id'] = $r_resource_vars['lists'];
$foreign_ids['card_id'] = $r_post['card_id'];
$comment = '##USER_NAME## added item ##CHECKLIST_ITEM_NAME## in checklist ##CHECKLIST_ITEM_PARENT_NAME## of card ##CARD_LINK##';
$response['activities'][] = insertActivity($authUser['id'], $comment, 'add_checklist_item', $foreign_ids, '', $item['id']);
}
}
break;
case '/boards/?/lists/?/cards/?/checklists/?/items/?/convert_to_card':
$is_return_vlaue = true;
$table_name = 'cards';
$qry_val_arr = array($r_resource_vars['items']);
$result = pg_query_params($db_lnk, 'SELECT name FROM checklist_items WHERE id = $1', $qry_val_arr);
$row = pg_fetch_assoc($result);
$r_post['board_id'] = $r_resource_vars['boards'];
$r_post['list_id'] = $r_resource_vars['lists'];
$r_post['name'] = $row['name'];
$qry_val_arr = array($r_post['list_id']);
$sresult = pg_query_params($db_lnk, 'SELECT max(position) as position FROM cards WHERE list_id = $1', $qry_val_arr);
$srow = pg_fetch_assoc($sresult);
$r_post['position'] = $srow['position'];
$r_post['user_id'] = $authUser['id'];
$sql = true;
break;
case '/boards/?/lists/?/cards/?/users/?':
$is_return_vlaue = true;
$table_name = 'cards_users';
unset($r_post['board_id']);
unset($r_post['list_id']);
unset($r_post['is_offline']);
unset($r_post['profile_picture_path']);
unset($r_post['username']);
unset($r_post['initials']);
$qry_val_arr = array($r_resource_vars['cards'], $r_resource_vars['users']);
$check_already_added = executeQuery('SELECT * FROM cards_users WHERE card_id = $1 AND user_id = $2', $qry_val_arr);
if (!empty($check_already_added)) {
$response['id'] = $check_already_added['id'];
$response['cards_users'] = $check_already_added;
} else {
$sql = true;
}
break;
case '/boards/?/lists/?/cards/?/copy':
$is_return_vlaue = true;
$r_post['user_id'] = $authUser['id'];
$table_name = 'cards';
$is_keep_attachment = $is_keep_user = $is_keep_label = $is_keep_activity = $is_keep_checklist = 0;
if (isset($r_post['keep_attachments'])) {
$is_keep_attachment = $r_post['keep_attachments'];
unset($r_post['keep_attachments']);
}
if (isset($r_post['keep_activities'])) {
$is_keep_activity = $r_post['keep_activities'];
unset($r_post['keep_activities']);
}
if (isset($r_post['keep_labels'])) {
$is_keep_label = $r_post['keep_labels'];
unset($r_post['keep_labels']);
}
if (isset($r_post['keep_users'])) {
$is_keep_user = $r_post['keep_users'];
unset($r_post['keep_users']);
}
if (isset($r_post['keep_checklists'])) {
$is_keep_checklist = $r_post['keep_checklists'];
unset($r_post['keep_checklists']);
}
$copied_card_id = $r_resource_vars['cards'];
unset($r_post['copied_card_id']);
$qry_val_arr = array($copied_card_id);
$sresult = pg_query_params($db_lnk, 'SELECT * FROM cards WHERE id = $1', $qry_val_arr);
$srow = pg_fetch_assoc($sresult);
unset($srow['id']);
$card_name = $r_post['name'];
$r_post = array_merge($srow, $r_post);
$r_post['name'] = $card_name;
$list_card_objs = pg_query_params($db_lnk, 'SELECT * FROM cards_listing WHERE list_id = $1 AND is_archived = $2 ORDER BY position ASC', array($r_post['list_id'], 'false'));
$list_cards = array();
$h = 1;
while ($card = pg_fetch_assoc($list_card_objs)) {
$list_cards[$h] = $card;
$h++;
}
if (isset($list_cards[$r_post['position']]) && isset($list_cards[$r_post['position'] - 1])) {
$r_post['position'] = ($list_cards[$r_post['position']]['position'] + $list_cards[$r_post['position'] - 1]['position']) / 2;
} else {
if (!isset($list_cards[$r_post['position']]) && isset($list_cards[$r_post['position'] - 1])) {
$r_post['position'] = $list_cards[$r_post['position'] - 1]['position'] + 1;
} else {
if (isset($list_cards[$r_post['position']]) && !isset($list_cards[$r_post['position'] - 1])) {
$r_post['position'] = $list_cards[$r_post['position']]['position'] / 2;
} else {
if (!isset($list_cards[$r_post['position']]) && !isset($list_cards[$r_post['position'] - 1])) {
$r_post['position'] = 1;
}
}
}
}
$sql = true;
break;
case '/organizations/?/users/?':
//organization users add
$table_name = 'organizations_users';
$sql = true;
$is_return_vlaue = true;
break;
case '/organizations':
//organizations add
$sql = true;
$table_name = 'organizations';
$r_post['user_id'] = !empty($authUser['id']) ? $authUser['id'] : 1;
$r_post['organization_visibility'] = 2;
break;
case '/organizations/?/upload_logo':
// organizations logo upload
$sql = false;
$json = true;
$organization_id = $r_resource_vars['organizations'];
if (!empty($_FILES['attachment']) && $_FILES['attachment']['error'] == 0) {
$mediadir = APP_PATH . DIRECTORY_SEPARATOR . 'media' . DIRECTORY_SEPARATOR . 'Organization' . DIRECTORY_SEPARATOR . $r_resource_vars['organizations'];
$save_path = 'media' . DIRECTORY_SEPARATOR . 'Organization' . DIRECTORY_SEPARATOR . $r_resource_vars['organizations'];
if (!file_exists($mediadir)) {
mkdir($mediadir, 0777, true);
}
$file = $_FILES['attachment'];
$file['name'] = preg_replace('/[^A-Za-z0-9\\-.]/', '', $file['name']);
if (move_uploaded_file($file['tmp_name'], $mediadir . DIRECTORY_SEPARATOR . $file['name'])) {
$logo_url = $save_path . DIRECTORY_SEPARATOR . $file['name'];
foreach ($thumbsizes['Organization'] as $key => $value) {
$list = glob(APP_PATH . DIRECTORY_SEPARATOR . 'img' . DIRECTORY_SEPARATOR . $key . DIRECTORY_SEPARATOR . 'Organization' . DIRECTORY_SEPARATOR . $r_resource_vars['organizations'] . '.*');
@unlink($list[0]);
}
foreach ($thumbsizes['Organization'] as $key => $value) {
$mediadir = APP_PATH . '/client/img/' . $key . '/Organization/' . $r_resource_vars['organizations'];
$list = glob($mediadir . '.*');
@unlink($list[0]);
}
$qry_val_arr = array($logo_url, $r_resource_vars['organizations']);
pg_query_params($db_lnk, 'UPDATE organizations SET logo_url = $1 WHERE id = $2', $qry_val_arr);
$response['logo_url'] = $logo_url;
$foreign_ids['organization_id'] = $r_resource_vars['organizations'];
$comment = (!empty($authUser['full_name']) ? $authUser['full_name'] : $authUser['username']) . ' added attachment to this organization ##ORGANIZATION_LINK##';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_organization_attachment', $foreign_ids);
}
}
break;
case '/acl_links':
$table_name = 'acl_links_roles';
$qry_val_arr = array($r_post['acl_link_id'], $r_post['role_id']);
$acl = executeQuery('SELECT * FROM ' . $table_name . ' WHERE acl_link_id = $1 AND role_id = $2', $qry_val_arr);
if ($acl) {
$qry_val_arr = array($r_post['acl_link_id'], $r_post['role_id']);
pg_query_params($db_lnk, 'DELETE FROM ' . $table_name . ' WHERE acl_link_id = $1 AND role_id = $2', $qry_val_arr);
} else {
$qry_val_arr = array($r_post['acl_link_id'], $r_post['role_id']);
pg_query_params($db_lnk, 'INSERT INTO ' . $table_name . ' (created, modified, acl_link_id, role_id) VALUES(now(), now(), $1, $2)', $qry_val_arr);
}
break;
default:
header($_SERVER['SERVER_PROTOCOL'] . ' 501 Not Implemented', true, 501);
break;
}
if (!empty($sql)) {
$post = getbindValues($table_name, $r_post);
$result = pg_execute_insert($table_name, $post);
if ($result) {
$row = pg_fetch_assoc($result);
$response['id'] = $row['id'];
if ($is_return_vlaue) {
$response[$table_name] = $row;
}
if (!empty($uuid)) {
$response['uuid'] = $uuid;
}
if ($r_resource_cmd == '/users/register') {
$emailFindReplace['##USERNAME##'] = $r_post['username'];
$emailFindReplace['##ACTIVATION_URL##'] = 'http://' . $_SERVER['HTTP_HOST'] . '/#/users/activation/' . $row['id'] . '/' . md5($r_post['username']);
$emailFindReplace['to'] = $r_post['email'];
$emailFindReplace['mail'] = 'activation';
sendMail($emailFindReplace);
} else {
if ($r_resource_cmd == '/boards') {
if (!$is_import_board) {
$foreign_id['board_id'] = $response['id'];
$comment = '##USER_NAME## created board';
$qry_val_arr = array($row['id'], $r_post['user_id']);
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_board', $foreign_id);
$result = pg_query_params($db_lnk, 'INSERT INTO boards_users (created, modified, board_id , user_id, is_admin) VALUES (now(), now(), $1, $2, true)', $qry_val_arr);
if (isset($lists) && !empty($lists)) {
$position = 1;
$total_list = count($lists);
$s_sql = 'INSERT INTO lists (created, modified, board_id, name, user_id, position) VALUES';
foreach ($lists as $list) {
$qry_val_arr = array($response['id'], $list, $authUser['id'], $position);
$s_sql = 'INSERT INTO lists (created, modified, board_id, name, user_id, position) VALUES';
$s_sql .= '(now(), now(), $1, $2, $3, $4)';
pg_query_params($db_lnk, $s_sql, $qry_val_arr);
$position++;
}
}
$qry_val_arr = array($row['id']);
$response['simple_board'] = executeQuery('SELECT row_to_json(d) FROM (SELECT * FROM simple_board_listing sbl WHERE id = $1 ORDER BY id ASC) as d', $qry_val_arr);
$response['simple_board'] = json_decode($response['simple_board']['row_to_json'], true);
}
} else {
if ($r_resource_cmd == '/organizations') {
$qry_val_arr = array($row['id'], $r_post['user_id']);
$result = pg_query_params($db_lnk, 'INSERT INTO organizations_users (created, modified, organization_id , user_id, is_admin) VALUES (now(), now(), $1, $2, true)', $qry_val_arr);
$foreign_id['organization_id'] = $row['id'];
$comment = '##USER_NAME## created organization "##ORGANIZATION_LINK##"';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_organization', $foreign_id);
} else {
if ($r_resource_cmd == '/boards/?/lists') {
$foreign_ids['board_id'] = $r_post['board_id'];
$foreign_ids['list_id'] = $response['id'];
$comment = '##USER_NAME## added list "' . $r_post['name'] . '".';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_list', $foreign_ids);
$copy_checklists = array();
$copy_checklists_items = array();
if (!empty($clone_list_id)) {
$qry_val_arr = array($clone_list_id);
$s_result = pg_query_params($db_lnk, 'SELECT name, board_id, position FROM lists WHERE id = $1', $qry_val_arr);
$previous_list = pg_fetch_assoc($s_result);
$new_list_id = $response['id'];
// Copy cards
$card_fields = 'board_id, name, description, position, due_date, is_archived, attachment_count, checklist_count, checklist_item_count, checklist_item_completed_count, label_count, cards_user_count, cards_subscriber_count, card_voter_count, activity_count, user_id, comment_count';
$card_fields = 'list_id, ' . $card_fields;
$qry_val_arr = array($clone_list_id);
$cards = pg_query_params($db_lnk, 'SELECT id, ' . $card_fields . ' FROM cards WHERE list_id = $1 ORDER BY id', $qry_val_arr);
if (pg_num_rows($cards)) {
copyCards($card_fields, $cards, $new_list_id, $post['name'], $foreign_ids['board_id']);
}
}
$qry_val_arr = array($foreign_ids['list_id']);
$s_result = pg_query_params($db_lnk, 'SELECT * FROM lists_listing WHERE id = $1', $qry_val_arr);
$list = pg_fetch_assoc($s_result);
$response['list'] = $list;
$qry_val_arr = array($foreign_ids['list_id']);
$attachments = pg_query_params($db_lnk, 'SELECT * FROM card_attachments WHERE list_id = $1', $qry_val_arr);
while ($attachment = pg_fetch_assoc($attachments)) {
$response['list']['attachments'][] = $attachment;
}
$qry_val_arr = array($foreign_ids['list_id']);
$activities = pg_query_params($db_lnk, 'SELECT * FROM activities_listing WHERE list_id = $1', $qry_val_arr);
while ($activity = pg_fetch_assoc($activities)) {
$response['list']['activities'][] = $activity;
}
$response['list']['checklists'] = $copy_checklists;
$response['list']['checklists_items'] = $copy_checklists_items;
$qry_val_arr = array($foreign_ids['list_id']);
$labels = pg_query_params($db_lnk, 'SELECT * FROM cards_labels_listing WHERE list_id = $1', $qry_val_arr);
while ($label = pg_fetch_assoc($labels)) {
$response['list']['labels'][] = $label;
}
$response['list']['cards'] = json_decode($response['list']['cards'], true);
$response['list']['lists_subscribers'] = json_decode($response['list']['lists_subscribers'], true);
} else {
if ($r_resource_cmd == '/boards/?/lists/?/cards' || $r_resource_cmd == '/boards/?/lists/?/cards/?/checklists/?/items/?/convert_to_card') {
$qry_val_arr = array($r_post['list_id']);
$s_result = pg_query_params($db_lnk, 'SELECT name FROM lists WHERE id = $1', $qry_val_arr);
$list = pg_fetch_assoc($s_result);
$foreign_ids['board_id'] = $r_post['board_id'];
$foreign_ids['card_id'] = $response['id'];
$foreign_ids['list_id'] = $r_post['list_id'];
$comment = '##USER_NAME## added card ##CARD_LINK## to list "' . $list['name'] . '".';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_card', $foreign_ids);
if (!empty($r_post['members'])) {
$s_usql = '';
foreach ($r_post['members'] as $member) {
$s_usql = 'INSERT INTO cards_users (created, modified, card_id, user_id) VALUES(now(), now(), ' . $response['id'] . ', ' . $member . ') RETURNING id';
$s_result = pg_query_params($db_lnk, $s_usql, array());
$card_user = pg_fetch_assoc($s_result);
$qry_val_arr = array($member);
$_user = executeQuery('SELECT username FROM users WHERE id = $1', $qry_val_arr);
$comment = '##USER_NAME## added "' . $_user['username'] . '" as member to this card ##CARD_LINK##';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_card_user', $foreign_ids, '', $card_user['id']);
}
}
$qry_val_arr = array($response['id']);
$cards_users = pg_query_params($db_lnk, 'SELECT * FROM cards_users_listing WHERE card_id = $1', $qry_val_arr);
while ($cards_user = pg_fetch_assoc($cards_users)) {
$response['cards_users'][] = $cards_user;
}
if (!empty($r_post['labels'])) {
$r_post['card_labels'] = $r_post['labels'];
}
if (!empty($r_post['card_labels'])) {
$label_names = explode(',', $r_post['card_labels']);
foreach ($label_names as $label_name) {
$qry_val_arr = array($label_name);
$s_result = pg_query_params($db_lnk, 'SELECT id FROM labels WHERE name = $1', $qry_val_arr);
$label = pg_fetch_assoc($s_result);
if (empty($label)) {
$qry_val_arr = array($label_name);
$s_result = pg_query_params($db_lnk, $s_sql = 'INSERT INTO labels (created, modified, name) VALUES (now(), now(), $1) RETURNING id', $qry_val_arr);
$label = pg_fetch_assoc($s_result);
}
$r_post['label_id'] = $label['id'];
$r_post['card_id'] = $row['id'];
$r_post['list_id'] = $row['list_id'];
$r_post['board_id'] = $row['board_id'];
$qry_val_arr = array($r_post['card_id'], $r_post['label_id'], $r_post['board_id'], $r_post['list_id']);
pg_query_params($db_lnk, 'INSERT INTO cards_labels (created, modified, card_id, label_id, board_id, list_id) VALUES (now(), now(), $1, $2, $3, $4) RETURNING *', $qry_val_arr);
}
$comment = '##USER_NAME## added label(s) to this card ##CARD_LINK## - ##LABEL_NAME##';
insertActivity($authUser['id'], $comment, 'add_card_label', $foreign_ids);
}
$qry_val_arr = array($response['id']);
$cards_labels = pg_query_params($db_lnk, 'SELECT * FROM cards_labels_listing WHERE card_id = $1', $qry_val_arr);
while ($cards_label = pg_fetch_assoc($cards_labels)) {
$response['cards_labels'][] = $cards_label;
}
if (!empty($clone_card_id)) {
$qry_val_arr = array($response['id'], $clone_card_id);
pg_query_params($db_lnk, 'INSERT INTO card_attachments (created, modified, card_id, name, path, mimetype) SELECT created, modified, $1, name, path, mimetype FROM card_attachments WHERE card_id = $2', $qry_val_arr);
$qry_val_arr = array($clone_card_id);
$s_result = pg_query_params($db_lnk, 'SELECT name, list_id, board_id, position FROM lists WHERE id = $1', $qry_val_arr);
$previous_value = pg_fetch_assoc($s_result);
$comment = '##USER_NAME## copied card "' . $r_post['name'] . '". from "' . $previous_value['name'] . '"';
$response['activity'] = insertActivity($authUser['id'], $comment, 'copy_card', $foreign_id);
}
} else {
if ($r_resource_cmd == '/boards/?/copy') {
$new_board_id = $row['id'];
//Copy board users
$boards_user_fields = 'user_id, is_admin';
$qry_val_arr = array($r_resource_vars['boards']);
$boards_users = pg_query_params($db_lnk, 'SELECT id, ' . $boards_user_fields . ' FROM boards_users WHERE board_id = $1', $qry_val_arr);
if ($boards_users && pg_num_rows($boards_users)) {
$boards_user_fields = 'created, modified, board_id, ' . $boards_user_fields;
while ($boards_user = pg_fetch_object($boards_users)) {
$boards_user_values = array();
array_push($boards_user_values, 'now()', 'now()', $new_board_id);
foreach ($boards_user as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($boards_user_values, 'false');
} else {
if ($value === null) {
array_push($boards_user_values, null);
} else {
array_push($boards_user_values, $value);
}
}
}
}
$boards_user_val = '';
for ($i = 1, $len = count($boards_user_values); $i <= $len; $i++) {
$boards_user_val .= '$' . $i;
$boards_user_val .= $i != $len ? ', ' : '';
}
$boards_user_result = pg_query_params($db_lnk, 'INSERT INTO boards_users (' . $boards_user_fields . ') VALUES (' . $boards_user_val . ') RETURNING id', $boards_user_values);
}
}
//Copy board subscribers
$boards_subscriber_fields = 'user_id, is_subscribed';
$qry_val_arr = array($r_resource_vars['boards']);
$boards_subscribers = pg_query_params($db_lnk, 'SELECT id, ' . $boards_subscriber_fields . ' FROM board_subscribers WHERE board_id = $1', $qry_val_arr);
if ($boards_subscribers && pg_num_rows($boards_subscribers)) {
$boards_subscriber_fields = 'created, modified, board_id, ' . $boards_subscriber_fields;
while ($boards_subscriber = pg_fetch_object($boards_subscribers)) {
$boards_subscriber_values = array();
array_push($boards_subscriber_values, 'now()', 'now()', $new_board_id);
foreach ($boards_subscriber as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($boards_subscriber_values, 'false');
} else {
if ($value === null) {
array_push($boards_subscriber_values, null);
} else {
array_push($boards_subscriber_values, $value);
}
}
}
}
$boards_subscriber_val = '';
for ($i = 1, $len = count($boards_subscriber_values); $i <= $len; $i++) {
$boards_subscriber_val .= '$' . $i;
$boards_subscriber_val .= $i != $len ? ', ' : '';
}
$boards_subscriber_result = pg_query_params($db_lnk, 'INSERT INTO board_subscribers (' . $boards_subscriber_fields . ') VALUES (' . $boards_subscriber_val . ') RETURNING id', $boards_subscriber_values);
}
}
//Copy board star
$boards_star_fields = 'user_id, is_starred';
$qry_val_arr = array($r_resource_vars['boards']);
$boards_stars = pg_query_params($db_lnk, 'SELECT id, ' . $boards_star_fields . ' FROM board_stars WHERE board_id = $1', $qry_val_arr);
if ($boards_stars && pg_num_rows($boards_stars)) {
$boards_star_fields = 'created, modified, board_id, ' . $boards_star_fields;
while ($boards_star = pg_fetch_object($boards_stars)) {
$boards_star_values = array();
array_push($boards_star_values, 'now()', 'now()', $new_board_id);
foreach ($boards_star as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($boards_star_values, 'false');
} else {
if ($value === null) {
array_push($boards_star_values, null);
} else {
array_push($boards_star_values, $value);
}
}
}
}
$boards_star_val = '';
for ($i = 1, $len = count($boards_star_values); $i <= $len; $i++) {
$boards_star_val .= '$' . $i;
$boards_star_val .= $i != $len ? ', ' : '';
}
$boards_star_result = pg_query_params($db_lnk, 'INSERT INTO board_stars (' . $boards_star_fields . ') VALUES (' . $boards_star_val . ') RETURNING id', $boards_star_values);
}
}
if ($keepcards) {
$qry_val_arr = array($r_resource_vars['boards']);
$lists = pg_query_params($db_lnk, 'SELECT id, name, position, is_archived, card_count, lists_subscriber_count FROM lists WHERE board_id = $1', $qry_val_arr);
} else {
$qry_val_arr = array($r_resource_vars['boards']);
$lists = pg_query_params($db_lnk, 'SELECT id, name, position, is_archived, lists_subscriber_count FROM lists WHERE board_id = $1', $qry_val_arr);
}
if ($lists) {
// Copy lists
while ($list = pg_fetch_object($lists)) {
$list_id = $list->id;
$list_fields = 'created, modified, board_id, user_id';
$list_values = array();
array_push($list_values, 'now()', 'now()', $new_board_id, $authUser['id']);
foreach ($list as $key => $value) {
if ($key != 'id') {
$list_fields .= ', ' . $key;
if ($value === false) {
array_push($list_values, 'false');
} else {
array_push($list_values, $value);
}
}
}
$list_val = '';
for ($i = 1, $len = count($list_values); $i <= $len; $i++) {
$list_val .= '$' . $i;
$list_val .= $i != $len ? ', ' : '';
}
$lists_result = pg_query_params($db_lnk, 'INSERT INTO lists (' . $list_fields . ') VALUES (' . $list_val . ') RETURNING id', $list_values);
if ($lists_result) {
$list_result = pg_fetch_assoc($lists_result);
$new_list_id = $list_result['id'];
//Copy list subscribers
$lists_subscriber_fields = 'user_id, is_subscribed';
$qry_val_arr = array($list_id);
$lists_subscribers = pg_query_params($db_lnk, 'SELECT id, ' . $lists_subscriber_fields . ' FROM list_subscribers WHERE list_id = $1', $qry_val_arr);
if ($lists_subscribers && pg_num_rows($lists_subscribers)) {
$lists_subscriber_fields = 'created, modified, list_id, ' . $lists_subscriber_fields;
while ($lists_subscriber = pg_fetch_object($lists_subscribers)) {
$lists_subscriber_values = array();
array_push($lists_subscriber_values, 'now()', 'now()', $new_list_id);
foreach ($lists_subscriber as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($lists_subscriber_values, 'false');
} else {
if ($value === null) {
array_push($lists_subscriber_values, null);
} else {
array_push($lists_subscriber_values, $value);
}
}
}
}
$lists_subscriber_val = '';
for ($i = 1, $len = count($lists_subscriber_values); $i <= $len; $i++) {
$lists_subscriber_val .= '$' . $i;
$lists_subscriber_val .= $i != $len ? ', ' : '';
}
$lists_subscriber_result = pg_query_params($db_lnk, 'INSERT INTO list_subscribers (' . $lists_subscriber_fields . ') VALUES (' . $lists_subscriber_val . ') RETURNING id', $lists_subscriber_values);
}
}
// Copy cards
$card_fields = 'name, description, due_date, position, is_archived, attachment_count, checklist_count, checklist_item_count, checklist_item_completed_count, label_count, cards_user_count, cards_subscriber_count, card_voter_count, activity_count, user_id, comment_count';
if ($keepcards) {
$qry_val_arr = array($list_id);
$cards = pg_query_params($db_lnk, 'SELECT id, ' . $card_fields . ' FROM cards WHERE list_id = $1', $qry_val_arr);
}
if ($keepcards && pg_num_rows($cards)) {
$card_fields = 'created, modified, board_id, list_id, ' . $card_fields;
while ($card = pg_fetch_object($cards)) {
$card_id = $card->id;
$card_values = array();
array_push($card_values, 'now()', 'now()', $new_board_id, $new_list_id);
foreach ($card as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($card_values, 'false');
} else {
if ($value === null) {
array_push($card_values, null);
} else {
array_push($card_values, $value);
}
}
}
}
$card_val = '';
for ($i = 1, $len = count($card_values); $i <= $len; $i++) {
$card_val .= '$' . $i;
$card_val .= $i != $len ? ', ' : '';
}
$card_result = pg_query_params($db_lnk, 'INSERT INTO cards (' . $card_fields . ') VALUES (' . $card_val . ') RETURNING id', $card_values);
if ($card_result) {
$card_result = pg_fetch_assoc($card_result);
$new_card_id = $card_result['id'];
//Copy card attachments
$attachment_fields = 'name, path, mimetype';
$qry_val_arr = array($card_id);
$attachments = pg_query_params($db_lnk, 'SELECT id, ' . $attachment_fields . ' FROM card_attachments WHERE card_id = $1', $qry_val_arr);
if ($attachments && pg_num_rows($attachments)) {
$attachment_fields = 'created, modified, board_id, list_id, card_id, ' . $attachment_fields;
while ($attachment = pg_fetch_object($attachments)) {
$attachment_values = array();
array_push($attachment_values, 'now()', 'now()', $new_board_id, $new_list_id, $new_card_id);
foreach ($attachment as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($attachment_values, 'false');
} else {
if ($value === null) {
array_push($attachment_values, null);
} else {
array_push($attachment_values, $value);
}
}
}
}
$attachment_val = '';
for ($i = 1, $len = count($attachment_values); $i <= $len; $i++) {
$attachment_val .= '$' . $i;
$attachment_val .= $i != $len ? ', ' : '';
}
$card_result = pg_query_params($db_lnk, 'INSERT INTO card_attachments (' . $attachment_fields . ') VALUES (' . $attachment_val . ') RETURNING id', $attachment_values);
}
}
//Copy checklists
$checklist_fields = 'user_id, name, checklist_item_count, checklist_item_completed_count, position';
$qry_val_arr = array($card_id);
$checklists = pg_query_params($db_lnk, 'SELECT id, ' . $checklist_fields . ' FROM checklists WHERE card_id = $1', $qry_val_arr);
if ($checklists && pg_num_rows($checklists)) {
$checklist_fields = 'created, modified, card_id, ' . $checklist_fields;
while ($checklist = pg_fetch_object($checklists)) {
$checklist_values = array();
array_push($checklist_values, 'now()', 'now()', $new_card_id);
$checklist_id = $checklist->id;
foreach ($checklist as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($checklist_values, 'false');
} else {
if ($value === null) {
array_push($checklist_values, null);
} else {
array_push($checklist_values, $value);
}
}
}
}
$checklist_val = '';
for ($i = 1, $len = count($checklist_values); $i <= $len; $i++) {
$checklist_val .= '$' . $i;
$checklist_val .= $i != $len ? ', ' : '';
}
$checklist_result = pg_query_params($db_lnk, 'INSERT INTO checklists (' . $checklist_fields . ') VALUES (' . $checklist_val . ') RETURNING id', $checklist_values);
if ($checklist_result) {
$checklist_result = pg_fetch_assoc($checklist_result);
$new_checklist_id = $checklist_result['id'];
//Copy checklist items
$checklist_item_fields = 'user_id, name, position';
$qry_val_array = array($checklist_id);
$checklist_items = pg_query_params($db_lnk, 'SELECT id, ' . $checklist_item_fields . ' FROM checklist_items WHERE checklist_id = $1', $qry_val_array);
if ($checklist_items && pg_num_rows($checklist_items)) {
$checklist_item_fields = 'created, modified, card_id, checklist_id, ' . $checklist_item_fields;
while ($checklist_item = pg_fetch_object($checklist_items)) {
$checklist_item_values = array();
array_push($checklist_item_values, 'now()', 'now()', $new_card_id, $new_checklist_id);
foreach ($checklist_item as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($checklist_item_values, 'false');
} else {
if ($value === null) {
array_push($checklist_item_values, null);
} else {
array_push($checklist_item_values, $value);
}
}
}
}
$checklist_item_val = '';
for ($i = 1, $len = count($checklist_item_values); $i <= $len; $i++) {
$checklist_item_val .= '$' . $i;
$checklist_item_val .= $i != $len ? ', ' : '';
}
$checklist_item_result = pg_query_params($db_lnk, 'INSERT INTO checklist_items (' . $checklist_item_fields . ') VALUES (' . $checklist_item_val . ') RETURNING id', $checklist_item_values);
}
}
}
}
}
//Copy card voters
$card_voter_fields = 'user_id';
$qry_val_arr = array($card_id);
$card_voters = pg_query_params($db_lnk, 'SELECT id, ' . $card_voter_fields . ' FROM card_voters WHERE card_id = $1', $qry_val_arr);
if ($card_voters && pg_num_rows($card_voters)) {
$card_voter_fields = 'created, modified, card_id, ' . $card_voter_fields;
while ($card_voter = pg_fetch_object($card_voters)) {
$card_voter_values = array();
array_push($card_voter_values, 'now()', 'now()', $new_card_id);
foreach ($card_voter as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($card_voter_values, 'false');
} else {
if ($value === null) {
array_push($card_voter_values, null);
} else {
array_push($card_voter_values, $value);
}
}
}
}
$card_voter_val = '';
for ($i = 1, $len = count($card_voter_values); $i <= $len; $i++) {
$card_voter_val .= '$' . $i;
$card_voter_val .= $i != $len ? ', ' : '';
}
$card_voter_result = pg_query_params($db_lnk, 'INSERT INTO card_voters (' . $card_voter_fields . ') VALUES (' . $card_voter_val . ') RETURNING id', $card_voter_values);
}
}
//Copy card labels
$cards_label_fields = 'label_id';
$qry_val_arr = array($card_id);
$cards_labels = pg_query_params($db_lnk, 'SELECT id, ' . $cards_label_fields . ' FROM cards_labels WHERE card_id = $1', $qry_val_arr);
if ($cards_labels && pg_num_rows($cards_labels)) {
$cards_label_fields = 'created, modified, board_id, list_id, card_id, ' . $cards_label_fields;
while ($cards_label = pg_fetch_object($cards_labels)) {
$cards_label_values = array();
array_push($cards_label_values, 'now()', 'now()', $new_board_id, $new_list_id, $new_card_id);
foreach ($cards_label as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($cards_label_values, 'false');
} else {
if ($value === null) {
array_push($cards_label_values, null);
} else {
array_push($cards_label_values, $value);
}
}
}
}
$cards_label_val = '';
for ($i = 1, $len = count($cards_label_values); $i <= $len; $i++) {
$cards_label_val .= '$' . $i;
$cards_label_val .= $i != $len ? ', ' : '';
}
$cards_label_result = pg_query_params($db_lnk, 'INSERT INTO cards_labels (' . $cards_label_fields . ') VALUES (' . $cards_label_val . ') RETURNING id', $cards_label_values);
}
}
//Copy card subscribers
$cards_subscriber_fields = 'user_id, is_subscribed';
$qry_val_arr = array($card_id);
$cards_subscribers = pg_query_params($db_lnk, 'SELECT id, ' . $cards_subscriber_fields . ' FROM card_subscribers WHERE card_id = $1', $qry_val_arr);
if ($cards_subscribers && pg_num_rows($cards_subscribers)) {
$cards_subscriber_fields = 'created, modified, card_id, ' . $cards_subscriber_fields;
while ($cards_subscriber = pg_fetch_object($cards_subscribers)) {
$cards_subscriber_values = array();
array_push($cards_subscriber_values, 'now()', 'now()', $new_card_id);
foreach ($cards_subscriber as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($cards_subscriber_values, 'false');
} else {
if ($value === null) {
array_push($cards_subscriber_values, null);
} else {
array_push($cards_subscriber_values, $value);
}
}
}
}
$cards_subscriber_val = '';
for ($i = 1, $len = count($cards_subscriber_values); $i <= $len; $i++) {
$cards_subscriber_val .= '$' . $i;
$cards_subscriber_val .= $i != $len ? ', ' : '';
}
$cards_subscriber_result = pg_query_params($db_lnk, 'INSERT INTO card_subscribers (' . $cards_subscriber_fields . ') VALUES (' . $cards_subscriber_val . ') RETURNING id', $cards_subscriber_values);
}
}
//Copy card users
$cards_user_fields = 'user_id';
$qry_val_arr = array($card_id);
$cards_users = pg_query_params($db_lnk, 'SELECT id, ' . $cards_user_fields . ' FROM cards_users WHERE card_id = $1', $qry_val_arr);
if ($cards_users && pg_num_rows($cards_users)) {
$cards_user_fields = 'created, modified, card_id, ' . $cards_user_fields;
while ($cards_user = pg_fetch_object($cards_users)) {
$cards_user_values = array();
array_push($cards_user_values, 'now()', 'now()', $new_card_id);
foreach ($cards_user as $key => $value) {
if ($key != 'id') {
if ($value === false) {
array_push($cards_user_values, 'false');
} else {
if ($value === null) {
array_push($cards_user_values, null);
} else {
array_push($cards_user_values, $value);
}
}
}
}
$cards_user_val = '';
for ($i = 1, $len = count($cards_user_values); $i <= $len; $i++) {
$cards_user_val .= '$' . $i;
$cards_user_val .= $i != $len ? ', ' : '';
}
$cards_user_result = pg_query_params($db_lnk, 'INSERT INTO cards_users (' . $cards_user_fields . ') VALUES (' . $cards_user_val . ') RETURNING id', $cards_user_values);
}
}
}
}
}
}
}
}
} else {
if ($r_resource_cmd == '/boards/?/lists/?/cards/?/checklists') {
if (isset($checklist_id) && !empty($checklist_id)) {
$qry_val_arr = array($r_post['user_id'], $response['id'], $checklist_id);
pg_query_params($db_lnk, 'INSERT INTO checklist_items (created, modified, user_id, card_id, checklist_id, name, is_completed, position) SELECT created, modified, $1, card_id, $2, name, false, position FROM checklist_items WHERE checklist_id = $3', $qry_val_arr);
}
$qry_val_arr = array($response['id']);
$result = pg_query_params($db_lnk, 'SELECT * FROM checklists_listing WHERE id = $1', $qry_val_arr);
$response['checklist'] = pg_fetch_assoc($result);
$response['checklist']['checklists_items'] = json_decode($response['checklist']['checklists_items'], true);
$foreign_ids['board_id'] = $r_resource_vars['boards'];
$foreign_ids['list_id'] = $r_resource_vars['lists'];
$foreign_ids['card_id'] = $r_resource_vars['cards'];
$comment = '##USER_NAME## added checklist ##CHECKLIST_NAME## to this card ##CARD_LINK##';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_card_checklist', $foreign_ids, '', $response['id']);
} else {
if ($r_resource_cmd == '/boards/?/lists/?/cards/?/comments') {
$id_converted = base_convert($response['id'], 10, 36);
$materialized_path = sprintf("%08s", $id_converted);
if (!empty($prev_message['materialized_path'])) {
$materialized_path = $prev_message['materialized_path'] . '-' . $materialized_path;
}
if (!empty($prev_message['path'])) {
$path = $prev_message['path'] . '.P' . $response['id'];
$depth = $prev_message['depth'] + 1;
$root = $prev_message['root'];
$response['activities']['depth'] = $depth;
} else {
$path = 'P' . $response['id'];
$depth = 0;
$root = $response['id'];
}
$qry_val_arr = array($materialized_path, $path, $depth, $root, $response['id']);
pg_query_params($db_lnk, 'UPDATE activities SET materialized_path = $1, path = $2, depth = $3, root = $4 WHERE id = $5', $qry_val_arr);
$qry_val_arr = array($r_post['freshness_ts'], $root);
pg_query_params($db_lnk, 'UPDATE activities SET freshness_ts = $1 WHERE root = $2', $qry_val_arr);
$qry_val_arr = array($root);
$act_res = pg_query_params($db_lnk, 'SELECT * FROM activities WHERE root = $1', $qry_val_arr);
$response['activity'] = pg_fetch_assoc($act_res);
} else {
if ($r_resource_cmd == '/boards/?/lists/?/cards/?/copy') {
if ($is_keep_attachment) {
$qry_val_arr = array($response['id'], $r_post['list_id'], $r_post['board_id'], $copied_card_id);
pg_query_params($db_lnk, 'INSERT INTO card_attachments (created, modified, card_id, name, path, mimetype, list_id, board_id) SELECT created, modified, $1, name, path, mimetype, $2, $3 FROM card_attachments WHERE card_id = $4 ORDER BY id', $qry_val_arr);
}
if ($is_keep_user) {
$qry_val_arr = array($response['id'], $copied_card_id);
pg_query_params($db_lnk, 'INSERT INTO cards_users (created, modified, card_id, user_id) SELECT created, modified, $1, user_id FROM cards_users WHERE card_id = $2 ORDER BY id', $qry_val_arr);
}
if ($is_keep_label) {
$qry_val_arr = array($response['id'], $r_post['list_id'], $r_post['board_id'], $copied_card_id);
pg_query_params($db_lnk, 'INSERT INTO cards_labels (created, modified, card_id, label_id, list_id, board_id) SELECT created, modified, $1, label_id, $2, $3 FROM cards_labels WHERE card_id = $4 ORDER BY id', $qry_val_arr);
}
if ($is_keep_activity) {
$qry_val_arr = array($response['id'], $r_post['user_id'], $r_post['list_id'], $r_post['board_id'], $copied_card_id);
pg_query_params($db_lnk, 'INSERT INTO activities (created, modified, card_id, user_id, list_id, board_id, foreign_id, type, comment, revisions, root, freshness_ts, depth, path, materialized_path) SELECT created, modified, $1, $2, $3, $4, foreign_id, type, comment, revisions, root, freshness_ts, depth, path, materialized_path FROM activities WHERE type = \'add_comment\' AND card_id = $5 ORDER BY id', $qry_val_arr);
}
if ($is_keep_checklist) {
$qry_val_arr = array($response['id'], $copied_card_id);
pg_query_params($db_lnk, 'INSERT INTO checklists (created, modified, user_id, card_id, name, checklist_item_count, checklist_item_completed_count, position) SELECT created, modified, user_id, $1, name, checklist_item_count, checklist_item_completed_count, position FROM checklists WHERE card_id = $2 ORDER BY id', $qry_val_arr);
$qry_val_arr = array($response['id']);
$checklists = pg_query_params($db_lnk, 'SELECT id FROM checklists WHERE card_id = $1', $qry_val_arr);
$qry_val_arr = array($copied_card_id);
$prev_checklists = pg_query_params($db_lnk, 'SELECT id FROM checklists WHERE card_id = $1', $qry_val_arr);
$prev_checklist_ids = array();
while ($prev_checklist_id = pg_fetch_assoc($prev_checklists)) {
$prev_checklist_ids[] = $prev_checklist_id['id'];
}
$i = 0;
while ($checklist_id = pg_fetch_assoc($checklists)) {
$qry_val_arr = array($response['id'], $checklist_id['id'], $prev_checklist_ids[$i]);
pg_query_params($db_lnk, 'INSERT INTO checklist_items (created, modified, user_id, card_id, name, checklist_id, is_completed, position) SELECT created, modified, user_id, $1, name , $2, is_completed, position FROM checklist_items WHERE checklist_id = $3 ORDER BY id', $qry_val_arr);
$i++;
}
}
$foreign_ids['board_id'] = $r_post['board_id'];
$foreign_ids['list_id'] = $r_post['list_id'];
$foreign_ids['card_id'] = $response['id'];
$comment = '##USER_NAME## copied this card "' . $srow['name'] . '" to ##CARD_NAME##';
$response['activity'] = insertActivity($authUser['id'], $comment, 'copy_card', $foreign_ids, null, $response['id']);
$qry_val_arr = array($response['id']);
$response['cards'] = executeQuery('SELECT * FROM cards_listing WHERE id = $1', $qry_val_arr);
if (!empty($response['cards']['cards_checklists'])) {
$response['cards']['cards_checklists'] = json_decode($response['cards']['cards_checklists'], true);
}
if (!empty($response['cards']['cards_users'])) {
$response['cards']['cards_users'] = json_decode($response['cards']['cards_users'], true);
}
if (!empty($response['cards']['cards_voters'])) {
$response['cards']['cards_voters'] = json_decode($response['cards']['cards_voters'], true);
}
if (!empty($response['cards']['cards_subscribers'])) {
$response['cards']['cards_subscribers'] = json_decode($response['cards']['cards_subscribers'], true);
}
if (!empty($response['cards']['cards_labels'])) {
$response['cards']['cards_labels'] = json_decode($response['cards']['cards_labels'], true);
}
$qry_val_arr = array($response['id']);
$activities = executeQuery('SELECT ( SELECT array_to_json(array_agg(row_to_json(cl.*))) AS array_to_json FROM ( SELECT activities_listing.* FROM activities_listing activities_listing WHERE (activities_listing.card_id = cards.id) ORDER BY activities_listing.id DESC) cl) AS activities FROM cards cards WHERE id = $1', $qry_val_arr);
if (!empty($activities)) {
$response['cards']['activities'] = json_decode($activities['activities'], true);
}
$qry_val_arr = array($response['id']);
$attachments = pg_query_params($db_lnk, 'SELECT * FROM card_attachments WHERE card_id = $1', $qry_val_arr);
while ($attachment = pg_fetch_assoc($attachments)) {
$response['cards']['attachments'][] = $attachment;
}
} else {
if ($r_resource_cmd == '/boards/?/lists/?/cards/?/users/?') {
$qry_val_arr = array($r_post['card_id'], $r_post['user_id']);
$sel_query = 'SELECT cu.card_id, cu.user_id, users.username, c.board_id, c.list_id, b.name as board_name FROM cards_users cu LEFT JOIN cards c ON cu.card_id = c.id LEFT JOIN users ON cu.user_id = users.id LEFT JOIN boards b ON c.board_id = b.id WHERE cu.card_id = $1 AND cu.user_id = $2';
$get_details = pg_query_params($db_lnk, $sel_query, $qry_val_arr);
$sel_details = pg_fetch_assoc($get_details);
$foreign_ids['board_id'] = $sel_details['board_id'];
$foreign_ids['list_id'] = $sel_details['list_id'];
$foreign_ids['card_id'] = $r_post['card_id'];
$qry_val_arr = array($r_post['user_id']);
$user = executeQuery('SELECT * FROM users WHERE id = $1', $qry_val_arr);
if ($user) {
$emailFindReplace = array('mail' => 'newprojectuser', '##USERNAME##' => $user['username'], '##CURRENT_USER##' => $authUser['username'], '##BOARD_NAME##' => $sel_details['board_name'], '##BOARD_URL##' => 'http://' . $_SERVER['HTTP_HOST'] . '/#/board/' . $foreign_ids['board_id'] . '/card/' . $foreign_ids['card_id'], 'to' => $user['email']);
sendMail($emailFindReplace);
}
$comment = '##USER_NAME## added "' . $sel_details['username'] . '" as member to this card ##CARD_LINK##';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_card_user', $foreign_ids, '', $response['id']);
} else {
if ($r_resource_cmd == '/boards/?/lists/?/cards/?/attachments') {
$foreign_ids['board_id'] = $r_post['board_id'];
$foreign_ids['list_id'] = $r_post['list_id'];
$foreign_ids['card_id'] = $r_post['card_id'];
$comment = '##USER_NAME## added attachment to this card ##CARD_LINK##';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_card_attachment', $foreign_ids, null, $response['id']);
foreach ($thumbsizes['CardAttachment'] as $key => $value) {
$mediadir = APP_PATH . '/client/img/' . $key . '/CardAttachment/' . $response['id'];
$list = glob($mediadir . '.*');
@unlink($list[0]);
}
} else {
if ($r_resource_cmd == '/boards/?/lists/?/cards/?/card_voters') {
$qry_val_arr = array($r_resource_vars['cards']);
$previous_value = executeQuery('SELECT name FROM cards WHERE id = $1', $qry_val_arr);
$foreign_ids['board_id'] = $r_resource_vars['boards'];
$foreign_ids['list_id'] = $r_resource_vars['lists'];
$foreign_ids['card_id'] = $r_post['card_id'];
$comment = '##USER_NAME## voted on ##CARD_LINK##';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_card_voter', $foreign_ids, '', $response['id']);
$qry_val_arr = array($response['id']);
$s_result = pg_query_params($db_lnk, 'SELECT * FROM card_voters_listing WHERE id = $1', $qry_val_arr);
$user = pg_fetch_assoc($s_result);
$response['card_voters'] = $user;
} else {
if ($r_resource_cmd == '/boards/?/users') {
$qry_val_arr = array($r_post['board_id']);
$s_result = pg_query_params($db_lnk, 'SELECT name FROM boards WHERE id = $1', $qry_val_arr);
$previous_value = pg_fetch_assoc($s_result);
$foreign_ids['board_id'] = $r_resource_vars['boards'];
$foreign_ids['board_id'] = $r_post['board_id'];
$qry_val_arr = array($r_post['user_id']);
$user = executeQuery('SELECT * FROM users WHERE id = $1', $qry_val_arr);
if ($user) {
$emailFindReplace = array('mail' => 'newprojectuser', '##USERNAME##' => $user['username'], '##CURRENT_USER##' => $authUser['username'], '##BOARD_NAME##' => $previous_value['name'], '##BOARD_URL##' => 'http://' . $_SERVER['HTTP_HOST'] . '/#/board/' . $r_post['board_id'], 'to' => $user['email']);
sendMail($emailFindReplace);
}
$comment = '##USER_NAME## added member to board';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_board_user', $foreign_ids, '', $response['id']);
} else {
if ($r_resource_cmd == '/organizations/?/users/?') {
$qry_val_arr = array($response['id']);
$foreign_ids['organization_id'] = $r_post['organization_id'];
$foreign_id = $response['id'];
$comment = '##USER_NAME## added member to organization';
$response['activity'] = insertActivity($authUser['id'], $comment, 'add_organization_user', $foreign_ids, null, $foreign_id);
$response['organizations_users'] = executeQuery('SELECT * FROM organizations_users_listing WHERE id = $1', $qry_val_arr);
$response['organizations_users']['boards_users'] = json_decode($response['organizations_users']['boards_users'], true);
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
// todo: $sql set as true query not execute, so add condition ($sql !== true)
if ($sql && $sql !== true && !empty($json) && !empty($response['id'])) {
if ($result = pg_query_params($db_lnk, $sql, array())) {
$data = array();
$count = pg_num_rows($result);
$i = 0;
while ($row = pg_fetch_row($result)) {
if ($i == 0 && $count > 1) {
echo '[';
}
echo $row[0];
$i++;
if ($i < $count) {
echo ',';
} else {
if ($count > 1) {
echo ']';
}
}
}
pg_free_result($result);
}
} else {
echo json_encode($response);
}
}