<?php require_once '../src/utils.inc.php'; if ($_SERVER['REQUEST_METHOD'] == 'GET') { doGet(); } else { doPost(); } function doGet() { $storeName = $_GET['storeName']; $token = @$_GET['token']; $from = @$_GET['from']; $to = @$_GET['to']; $type = @$_GET['submit']; if (empty($token)) { $token = null; } if (empty($from)) { $from = date('Y-m-d'); } if (empty($to)) { $fromTime = strtotime($from); $to = date('Y-m-d', $fromTime + 24 * 60 * 60); } if (empty($type)) { $type = null; } render($storeName, $token, $from, $to, $type); } function doPost()
function lookForCustomer($email) { $result = doGet('/api/v2/customers/search?email=' . urlencode($email)); return $result->_embedded->entries; }
<?php $action = @$_GET['a']; $email = @$_POST['email']; $data = array(); $data['error'] = 1; switch ($action) { case "getCountry": $ip = getip(); $jsonStr = doGet('http://www.geoplugin.net/json.gp?ip=' . $ip); $obj = json_decode($jsonStr); $data['error'] = 0; $data['ip'] = $ip; $data['country'] = $obj->geoplugin_countryName; break; case "bingEmail": $data['error'] = 1; $data['message'] = "绑定失败,服务器未响应您的请求。"; break; case "loli": setcookie('openSesame', 'loli.help', time() + 3600); $data['error'] = 0; $data['message'] = "cookie set ok!"; break; case "openSesame": $dcookie = $_COOKIE["openSesame"]; if ($dcookie == null) { $data['error'] = 1; } elseif ($dcookie == "loli.help") { $data['error'] = 0; }
/** * Common method to handle GET 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 * * @return mixed */ function r_get($r_resource_cmd, $r_resource_vars, $r_resource_filters) { global $r_debug, $db_lnk, $authUser, $_server_domain_url; // switch case.. if taking more length, then associative array... $sql = false; $response = array(); $pg_params = array(); switch ($r_resource_cmd) { case '/users': $response['users'] = array(); $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM users_listing ul ORDER BY id DESC) as d '; break; case '/users/logout': $response['user'] = array(); $authUser = array(); break; case '/users/?/activities': $condition = ''; $condition1 = ''; if (isset($r_resource_filters['last_activity_id']) && $r_resource_filters['last_activity_id'] > 0) { $condition = ' AND al.id > $2'; $condition1 = ' AND al.id > $3'; if (!empty($r_resource_filters['type']) && $r_resource_filters['type'] == 'profile') { $condition = ' AND al.id < $2'; $condition1 = ' AND al.id < $3'; } } $val_array = array($r_resource_vars['users']); $user = executeQuery('SELECT boards_users FROM users_listing WHERE id = $1', $val_array); $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']; } } $org_users = pg_query_params($db_lnk, 'SELECT organization_id FROM organizations_users WHERE user_id = $1', $val_array); $org_ids = array(); while ($row = pg_fetch_assoc($org_users)) { $org_ids[] = $row['organization_id']; } if (!empty($authUser) && $authUser['role_id'] == 1 && $authUser['id'] == $r_resource_vars['users']) { $condition = !empty($r_resource_filters['last_activity_id']) ? ' WHERE al.id > $1' : ""; $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM activities_listing al ' . $condition . ' ORDER BY id DESC LIMIT ' . PAGING_COUNT . ') as d'; } else { if (!empty($r_resource_filters['type']) && $r_resource_filters['type'] == 'profile') { $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM activities_listing al WHERE user_id = $1 ' . $condition . ' ORDER BY id DESC LIMIT ' . PAGING_COUNT . ') as d'; array_push($pg_params, $r_resource_vars['users']); } else { if (!empty($r_resource_filters['organization_id'])) { if (isset($r_resource_filters['last_activity_id']) && $r_resource_filters['last_activity_id'] > 0) { $condition1 = ' AND al.id > $4'; } $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM activities_listing al WHERE ((user_id = $1 AND board_id IN (SELECT id FROM boards WHERE organization_id = $2)) OR organization_id = ANY ( $3 )) ' . $condition1 . ' ORDER BY id DESC LIMIT ' . PAGING_COUNT . ') as d'; array_push($pg_params, $r_resource_vars['users'], $r_resource_filters['organization_id'], $r_resource_filters['organization_id']); } else { if (!empty($r_resource_filters['type']) && ($r_resource_filters['type'] = 'all')) { $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM activities_listing al WHERE (board_id = ANY ( $1 ) OR organization_id = ANY ( $2 ))' . $condition1 . ' ORDER BY id DESC LIMIT ' . PAGING_COUNT . ') as d'; array_push($pg_params, '{' . implode(',', $board_ids) . '}', '{' . implode(',', $org_ids) . '}'); } else { if (!empty($r_resource_filters['board_id']) && $r_resource_filters['board_id']) { $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM activities_listing al WHERE user_id = $1 AND board_id = $2' . $condition1 . ' ORDER BY freshness_ts DESC, materialized_path ASC LIMIT ' . PAGING_COUNT . ') as d'; array_push($pg_params, $r_resource_vars['users'], $r_resource_filters['board_id']); } else { $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM activities_listing al WHERE ( board_id = ANY( $1 ) OR organization_id = ANY ( $2 ) )' . $condition1 . ' ORDER BY id DESC LIMIT ' . PAGING_COUNT . ') as d'; array_push($pg_params, '{' . implode(',', $board_ids) . '}', '{' . implode(',', $org_ids) . '}'); } } } } } if (!empty($condition) || !empty($condition1)) { array_push($pg_params, $r_resource_filters['last_activity_id']); } break; case '/users/search': if (!empty($r_resource_filters['organizations'])) { $sql = 'SELECT row_to_json(d) FROM (SELECT u.id, u.username, u.profile_picture_path,u.initials FROM users u LEFT JOIN organizations_users ou ON ou.user_id = u.id WHERE u.is_active = true AND u.is_email_confirmed = true AND '; $sql .= '(ou.organization_id != $1 OR ou.user_id IS null) AND'; array_push($pg_params, $r_resource_filters['organizations']); } else { if (!empty($r_resource_filters['board_id'])) { $sql = 'SELECT row_to_json(d) FROM (SELECT u.id, u.username, u.profile_picture_path,u.initials FROM users u JOIN boards_users bu ON bu.user_id = u.id WHERE u.is_active = true AND u.is_email_confirmed = true AND '; $sql .= 'bu.board_id = $1 AND'; array_push($pg_params, $r_resource_filters['board_id']); } else { $sql = 'SELECT row_to_json(d) FROM (SELECT u.id, u.username, u.profile_picture_path,u.initials FROM users u WHERE u.is_active = true AND u.is_email_confirmed = true AND '; } } if (empty($pg_params)) { $sql .= '(LOWER(u.username) LIKE LOWER($1) OR LOWER(u.email) LIKE LOWER($2))) as d '; } else { $sql .= '(LOWER(u.username) LIKE LOWER($2) OR LOWER(u.email) LIKE LOWER($3))) as d '; } array_push($pg_params, $r_resource_filters['q'] . '%', $r_resource_filters['q'] . '%'); if (empty($r_resource_filters['q'])) { $sql = false; $response = array(); $pg_params = array(); } $table = 'users'; break; case '/users/?': $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM users ul WHERE id = $1) as d '; array_push($pg_params, $r_resource_vars['users']); break; case '/users/?/boards': if (!empty($authUser)) { $val_array = array($authUser['id']); $s_result = pg_query_params($db_lnk, 'SELECT board_id FROM board_stars WHERE is_starred = true AND user_id = $1', $val_array); $response['starred_boards'] = array(); while ($row = pg_fetch_assoc($s_result)) { $response['starred_boards'][] = $row['board_id']; } $val_array = array($authUser['id']); $s_result = pg_query_params($db_lnk, 'SELECT o.id as organization_id, o.name as organization_name, bu.board_id FROM boards_users bu LEFT JOIN boards b ON b.id = bu.board_id LEFT JOIN organizations o ON o.id = b.organization_id WHERE bu.user_id = $1', $val_array); $response['user_boards'] = array(); $user_boards = array(); while ($row = pg_fetch_assoc($s_result)) { $response['user_boards'][] = $row; } } break; case '/users/?/cards': $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM users_cards_listing ucl WHERE user_id = $1 ORDER BY board_id ASC) as d '; array_push($pg_params, $r_resource_vars['users']); break; case '/boards': if (!empty($r_resource_filters['type']) && $r_resource_filters['type'] == 'simple') { $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM simple_board_listing ul '; if (!empty($authUser) && $authUser['role_id'] != 1) { $val_array = array($authUser['id']); $s_result = pg_query_params($db_lnk, 'SELECT board_id FROM board_stars WHERE user_id = $1', $val_array); $response['starred_boards'] = array(); while ($row = pg_fetch_assoc($s_result)) { $response['starred_boards'][] = $row['board_id']; } $s_result = pg_query_params($db_lnk, 'SELECT board_id FROM boards_users WHERE user_id = $1', $val_array); $response['user_boards'] = array(); while ($row = pg_fetch_assoc($s_result)) { $response['user_boards'][] = $row['board_id']; } $board_ids = array_merge($response['starred_boards'], $response['user_boards']); $ids = 0; if (!empty($board_ids)) { $board_ids = array_unique($board_ids); $ids = '{' . implode($board_ids, ',') . '}'; } $sql .= 'WHERE ul.id =ANY($1)'; array_push($pg_params, $ids); } $sql .= ' ORDER BY name ASC) as d '; if ($authUser['role_id'] != 1 && empty($board_ids)) { $sql = false; } } else { $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM boards_listing ul '; if (!empty($authUser) && $authUser['role_id'] != 1) { $val_array = array($authUser['id']); $s_result = pg_query_params($db_lnk, 'SELECT board_id FROM board_subscribers WHERE user_id = $1', $val_array); $response['starred_boards'] = array(); while ($row = pg_fetch_assoc($s_result)) { $response['starred_boards'][] = $row['board_id']; } $s_result = pg_query_params($db_lnk, 'SELECT board_id FROM boards_users WHERE user_id = $1', $val_array); $response['user_boards'] = array(); while ($row = pg_fetch_assoc($s_result)) { $response['user_boards'][] = $row['board_id']; } $board_ids = array_merge($response['starred_boards'], $response['user_boards']); $ids = 0; if (!empty($board_ids)) { $board_ids = array_unique($board_ids); $ids = '{' . implode($board_ids, ',') . '}'; } $sql .= 'WHERE ul.id = ANY ($1)'; array_push($pg_params, $ids); } $sql .= ' ORDER BY name ASC) as d '; if ($authUser['role_id'] != 1 && empty($board_ids)) { $sql = false; } } break; case '/settings/?': $response = array(); $sql = false; $s_sql = 'SELECT id, name, parent_id FROM setting_categories WHERE parent_id IS null ORDER BY "order" ASC'; $s_result = pg_query_params($db_lnk, $s_sql, array()); while ($row = pg_fetch_assoc($s_result)) { if ($row['id'] == $r_resource_vars['settings'] || $row['parent_id'] == $r_resource_vars['settings']) { $s_sql = 'SELECT s.*, sc.name as category_name FROM settings s LEFT JOIN setting_categories sc ON sc.id = s.setting_category_id WHERE setting_category_id = $1 OR setting_category_parent_id = $2 ORDER BY "order" ASC'; $s_val = array($row['id'], $row['id']); $ss_result = pg_query_params($db_lnk, $s_sql, $s_val); while ($srow = pg_fetch_assoc($ss_result)) { $row['settings'][] = $srow; } } $response[] = $row; } break; case '/email_templates/?': $response = array(); $sql = false; $s_sql = 'SELECT id, display_name FROM email_templates ORDER BY id ASC'; $s_result = pg_query_params($db_lnk, $s_sql, array()); while ($row = pg_fetch_assoc($s_result)) { if ($row['id'] == $r_resource_vars['email_templates']) { $s_sql = 'SELECT from_email, reply_to_email, name, description, subject, email_text_content, email_variables, display_name FROM email_templates WHERE id = $1'; $s_val = array($row['id']); $ss_result = pg_query_params($db_lnk, $s_sql, $s_val); while ($srow = pg_fetch_assoc($ss_result)) { $row['template'] = $srow; } } $response[] = $row; } break; case '/boards/?': $s_sql = 'SELECT b.board_visibility, bu.user_id FROM boards AS b LEFT JOIN boards_users AS bu ON bu.board_id = b.id WHERE b.id = $1'; $arr[] = $r_resource_vars['boards']; if (!empty($authUser) && $authUser['role_id'] != 1) { $s_sql .= ' AND (b.board_visibility = 2 OR bu.user_id = $2)'; $arr[] = $authUser['id']; } else { if (empty($authUser)) { $s_sql .= ' AND b.board_visibility = 2 '; } } $check_visibility = executeQuery($s_sql, $arr); if (!empty($check_visibility)) { $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM boards_listing ul WHERE id = $1 ORDER BY id DESC) as d '; array_push($pg_params, $r_resource_vars['boards']); } else { $response['error']['type'] = 'visibility'; $response['error']['message'] = 'Unauthorized'; } break; case '/organizations': $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM organizations_listing'; if (!empty($authUser) && $authUser['role_id'] != 1) { $sql .= ' WHERE user_id = $1'; array_push($pg_params, $authUser['id']); } $sql .= ' ORDER BY id ASC) as d '; break; case '/organizations/?': $s_sql = 'SELECT o.organization_visibility, ou.user_id FROM organizations AS o LEFT JOIN organizations_users AS ou ON ou.organization_id = o.id WHERE o.id = $1'; $arr[] = $r_resource_vars['organizations']; if (!empty($authUser) && $authUser['role_id'] != 1) { $s_sql .= ' AND (o.organization_visibility = 1 OR ou.user_id = $2)'; $arr[] = $authUser['id']; } else { if (empty($authUser)) { $s_sql .= ' AND o.organization_visibility = 1 '; } } $check_visibility = executeQuery($s_sql, $arr); if (!empty($check_visibility)) { $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM organizations_listing ul WHERE id = $1 ORDER BY id DESC) as d '; array_push($pg_params, $r_resource_vars['organizations']); } else { $response['error']['type'] = 'visibility'; $response['error']['message'] = 'Unauthorized'; } break; case '/boards/?/activities': $condition = ''; if (isset($r_resource_filters['last_activity_id']) && $r_resource_filters['last_activity_id'] > 0) { if (!empty($r_resource_filters['type']) && $r_resource_filters['type'] == 'all') { $condition = ' AND al.id < $2'; } else { $condition = ' AND al.id > $2'; } } $sql = 'SELECT row_to_json(d) FROM (SELECT al.*, c.name as card_name FROM activities_listing al left join cards c on al.card_id = c.id WHERE al.board_id = $1' . $condition . ' ORDER BY al.id DESC LIMIT ' . PAGING_COUNT . ') as d '; array_push($pg_params, $r_resource_vars['boards']); if (!empty($condition)) { array_push($pg_params, $r_resource_filters['last_activity_id']); } break; case '/boards/?/boards_stars': $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM board_stars bs WHERE board_id = $1'; array_push($pg_params, $r_resource_vars['boards']); if (!empty($authUser) && $authUser['role_id'] != 1) { $sql .= ' and user_id = $2'; array_push($pg_params, $authUser['id']); } $sql .= ' ORDER BY id DESC) as d '; break; case '/boards/?/board_subscribers': $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM board_subscribers ul WHERE board_id = $1'; array_push($pg_params, $r_resource_vars['boards']); if (!empty($authUser) && $authUser['role_id'] != 1) { $sql .= ' and user_id = $2'; array_push($pg_params, $authUser['id']); } $sql .= ' ORDER BY id DESC) as d '; break; case '/boards/search': $sql = 'SELECT row_to_json(d) FROM (SELECT id, name, background_color FROM boards ul WHERE name ILIKE $1 ORDER BY id DESC) as d '; array_push($pg_params, '%' . $r_resource_filters['q'] . '%'); break; case '/boards/?/lists/?/cards/?': $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM cards_listing cll WHERE id = $1) as d '; array_push($pg_params, $r_resource_vars['cards']); break; case '/boards/?/lists/?/cards/?/activities': $sql = 'SELECT row_to_json(d) FROM (SELECT al.*, u.username, u.profile_picture_path, u.initials, c.description, c.name as card_name FROM activities_listing al LEFT JOIN users u ON al.user_id = u.id LEFT JOIN cards c ON al.card_id = c.id WHERE card_id = $1 ORDER BY freshness_ts DESC, materialized_path ASC) as d '; array_push($pg_params, $r_resource_vars['cards']); break; case '/activities': $condition = ''; if (isset($r_resource_filters['last_activity_id'])) { $condition = ' WHERE al.id < $1'; } $sql = 'SELECT row_to_json(d) FROM (SELECT al.*, u.username, u.profile_picture_path, u.initials, c.description FROM activities_listing al LEFT JOIN users u ON al.user_id = u.id LEFT JOIN cards c ON al.card_id = c.id ' . $condition . ' ORDER BY id DESC limit ' . PAGING_COUNT . ') as d '; if (!empty($condition)) { array_push($pg_params, $r_resource_filters['last_activity_id']); } break; case '/boards/?/lists/?/cards/?/checklists': $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM checklist_add_listing al WHERE board_id = $1) as d '; array_push($pg_params, $r_resource_vars['boards']); break; case '/boards/?/visibility': $sql = 'SELECT board_visibility FROM boards bl WHERE bl.id = $1'; array_push($pg_params, $r_resource_vars['boards']); break; case '/workflow_templates': $files = glob(APP_PATH . '/client/js/workflow_templates/*.json', GLOB_BRACE); $i = 0; foreach ($files as $file) { $file_name = basename($file, '.json'); $data = file_get_contents($file); $json = json_decode($data, true); $response[] = array('name' => $json['name'], 'value' => implode($json['lists'], ', ')); } break; case '/search': if (isset($_GET['q'])) { $q_string = $_GET['q']; preg_match_all('/(?P<name>\\w+):(?P<search>\\w+)/', $q_string, $search); if (!empty($search['name'])) { foreach ($search['name'] as $key => $name) { $filter['term'][$name . '_name'] = $search['search'][$key]; $filter_query['match'][$name . '_name'] = $search['search'][$key]; } } preg_match_all('/(.*)@(?P<search>\\w+)/', $q_string, $user_search); if (!empty($user_search['search'])) { foreach ($user_search['search'] as $value) { $filter['term']['user_name'] = $value; $filter_query['match']['user_name'] = $value; } } preg_match_all('/(.*)#(?P<search>\\w+)/', $q_string, $label_search); if (!empty($label_search['search'])) { foreach ($user_search['search'] as $value) { $filter['term']['label_name'] = $value; $filter_query['match']['label_name'] = $value; } } $response = array(); if (!empty($r_resource_filters['q'])) { $elasticsearch_url = ELASTICSEARCH_URL . ELASTICSEARCH_INDEX . '/cards/_search?q=*' . $r_resource_filters['q'] . '*'; $search_response = doGet($elasticsearch_url); $response['result'] = array(); if (!empty($search_response['hits']['hits'])) { foreach ($search_response['hits']['hits'] as $result) { $s_val = array($result['_source']['board_id']); $s_result = executeQuery('SELECT board_visibility,user_id FROM boards WHERE id = $1', $s_val); if ($s_result['board_visibility'] == '2' || $s_result['user_id'] == $authUser['id'] || $authUser['role_id'] == 1) { $card['name'] = $result['_source']['card_name']; $card['id'] = $result['_id']; $card['list_name'] = $result['_source']['list_name']; $card['list_id'] = $result['_source']['list_id']; $card['board_name'] = $result['_source']['board_name']; $card['board_id'] = $result['_source']['board_id']; $card['type'] = $result['_type']; $response['result'][] = $card; } } } $elasticsearch_params['suggest']['text'] = $r_resource_filters['q']; $elasticsearch_params['suggest']['card-name-suggest']['term']['size'] = 5; $elasticsearch_params['suggest']['card-name-suggest']['term']['field'] = 'card_name'; $elasticsearch_params['suggest']['card-description-suggest']['term']['size'] = 5; $elasticsearch_params['suggest']['card-description-suggest']['term']['field'] = 'card_description'; $elasticsearch_url = ELASTICSEARCH_URL . ELASTICSEARCH_INDEX . '/_search'; $result_arr = doPost($elasticsearch_url, $elasticsearch_params, 'json'); $words = $r_resource_filters['q']; $word_count = str_word_count($words); $word_arr = explode(' ', $words); $tmp_suggested_arr = array(); $max_suggested_count = 0; if (!empty($result_arr['suggest']['card-name-suggest'])) { for ($i = 0; $i < count($result_arr['suggest']['card-name-suggest']); $i++) { for ($j = 0; $j <= 2; $j++) { if (!empty($result_arr['suggest']['card-name-suggest'][$i]['options'][$j]['text'])) { $tmp_suggested_arr[$i][] = $result_arr['suggest']['card-name-suggest'][$i]['options'][$j]['text']; } if (!empty($result_arr['suggest']['card-description-suggest'][$i]['options'][$j]['text'])) { $tmp_suggested_arr[$i][] = $result_arr['suggest']['card-description-suggest'][$i]['options'][$j]['text']; } } if (!empty($tmp_suggested_arr[$i])) { $tmp_suggested_arr[$i] = array_unique($tmp_suggested_arr[$i]); if (count($tmp_suggested_arr[$i]) > $max_suggested_count) { $max_suggested_count = count($tmp_suggested_arr[$i]); } } } } $response['suggestion'] = array(); if (!empty($tmp_suggested_arr)) { for ($i = 0; $i < $max_suggested_count; $i++) { $response['suggestion'][$i] = ''; for ($j = 0; $j < $word_count; $j++) { if (isset($response[$i])) { $response[$i] .= ' '; } $response['suggestion'][$i] .= !empty($tmp_suggested_arr[$j][$i]) ? $tmp_suggested_arr[$j][$i] : (!empty($tmp_suggested_arr[$j][0]) ? $tmp_suggested_arr[$j][0] : $word_arr[$j]); } } } $response['suggestion'] = array_unique($response['suggestion']); } } break; case '/boards/?/lists/?/cards/?/search': $sql = 'SELECT row_to_json(d) FROM (SELECT bul.id, bul.user_id, bul.username, bul.profile_picture_path,bul.initials FROM boards_users_listing bul WHERE'; $sql .= '(bul.username LIKE $1 OR bul.email LIKE $2) AND bul.board_id = $3) as d '; array_push($pg_params, '%' . $r_resource_filters['q'] . '%', '%' . $r_resource_filters['q'] . '%', $r_resource_vars['boards']); if (empty($r_resource_filters['q'])) { $sql = false; $response = array(); $pg_params = array(); } $table = 'users'; break; case '/cards/search': $user_id = !empty($authUser['id']) ? $authUser['id'] : 0; $sql = 'SELECT row_to_json(d) FROM (SELECT DISTINCT c.id, c.name, bu.board_id FROM boards_users bu join cards c on c.board_id = bu.board_id WHERE bu.board_id IN (SELECT board_id FROM boards_users WHERE user_id = $1) AND c.name LIKE $2 ORDER BY id ASC) as d'; array_push($pg_params, $user_id, '%' . $r_resource_filters['q'] . '%'); if (empty($r_resource_filters['q'])) { $sql = false; $response = array(); $pg_params = array(); } break; case '/acl_links': $sql = false; $s_sql = 'SELECT row_to_json(d) FROM (SELECT acl_links.id, acl_links.name, acl_links.group_id, ( SELECT array_to_json(array_agg(row_to_json(alr.*))) AS array_to_json FROM ( SELECT acl_links_roles.role_id FROM acl_links_roles acl_links_roles WHERE acl_links_roles.acl_link_id = acl_links.id ORDER BY acl_links_roles.role_id) alr) AS acl_links_roles, acl_links.is_allow_only_to_admin, acl_links.is_allow_only_to_user FROM acl_links acl_links ORDER BY group_id ASC, id ASC) as d'; $s_result = pg_query_params($db_lnk, $s_sql, array()); $response['acl_links'] = array(); while ($row = pg_fetch_assoc($s_result)) { $response['acl_links'][] = json_decode($row['row_to_json'], true); } $s_sql = 'SELECT id, name FROM roles'; $s_result = pg_query_params($db_lnk, $s_sql, array()); $response['roles'] = array(); while ($row = pg_fetch_assoc($s_result)) { $response['roles'][] = $row; } break; case '/settings': $role_id = empty($user['role_id']) ? 3 : $user['role_id']; $s_sql = pg_query_params($db_lnk, 'SELECT name, value FROM settings WHERE name = \'SITE_NAME\' OR name = \'SITE_TIMEZONE\' OR name = \'DROPBOX_APPKEY\' OR name = \'LABEL_ICON\' OR name = \'FLICKR_API_KEY\' or name = \'LDAP_LOGIN_ENABLED\' or name = \'STANDARD_LOGIN_ENABLED\'', array()); while ($row = pg_fetch_assoc($s_sql)) { $response[$row['name']] = $row['value']; } break; default: header($_SERVER['SERVER_PROTOCOL'] . ' 501 Not Implemented', true, 501); } if (!empty($sql)) { $arrayResponse = array('/users/?/cards', '/users/?/activities', '/users/search', '/boards', '/boards/?/activities', '/boards/?/activities', '/boards/?/lists/?/cards/?/activities', '/boards/?/lists/?/cards/?/search', '/cards/search', '/organizations', '/activities'); if ($result = pg_query_params($db_lnk, $sql, $pg_params)) { $data = array(); $count = pg_num_rows($result); $i = 0; if (in_array($r_resource_cmd, $arrayResponse) && ($count == 1 || $count == 0)) { echo '['; } while ($row = pg_fetch_row($result)) { $obj = json_decode($row[0], true); if (isset($obj['board_activities']) && !empty($obj['board_activities'])) { for ($k = 0; $k < count($obj['board_activities']); $k++) { if (!empty($obj['board_activities'][$k]['revisions']) && trim($obj['board_activities'][$k]['revisions']) != '') { $revisions = unserialize($obj['board_activities'][$k]['revisions']); unset($dif); if (!empty($revisions['new_value'])) { foreach ($revisions['new_value'] as $key => $value) { if ($key != 'is_archived' && $key != 'is_deleted' && $key != 'created' && $key != 'modified' && $obj['type'] != 'moved_card_checklist_item' && $obj['type'] != 'add_card_desc' && $obj['type'] != 'add_card_duedate' && $obj['type'] != 'delete_card_duedate' && $obj['type'] != 'change_visibility' && $obj['type'] != 'add_background' && $obj['type'] != 'change_background') { $old_val = $revisions['old_value'][$key] != null && $revisions['old_value'][$key] != 'null' ? $revisions['old_value'][$key] : ''; $new_val = $revisions['new_value'][$key] != null && $revisions['new_value'][$key] != 'null' ? $revisions['new_value'][$key] : ''; $dif[] = nl2br(getRevisiondifference($old_val, $old_val)); } if ($obj['type'] == 'add_card_desc' || $obj['type'] == 'add_card_desc' || $obj['type'] == ' edit_card_duedate' || $obj['type'] == 'change_visibility' || $obj['type'] == 'add_background' || $obj['type'] == 'change_background') { $dif[] = $revisions['new_value'][$key]; } } if (isset($dif)) { $obj['board_activities'][$k]['difference'] = $dif; } } else { if (!empty($revisions['old_value']) && isset($obj['type']) && $obj['type'] == 'delete_card_comment') { $obj['board_activities'][$k]['difference'] = nl2br(getRevisiondifference($revisions['old_value'], '')); } } } } $row[0] = json_encode($obj); if ($r_resource_cmd == '/boards/?') { $obj = json_decode($row[0], true); global $_server_domain_url; $md5_hash = md5(SECURITYSALT . $r_resource_vars['boards']); $obj['google_syn_url'] = $_server_domain_url . '/ical/' . $r_resource_vars['boards'] . '/' . $md5_hash . '.ics'; $row[0] = json_encode($obj); } } else { if ($r_resource_cmd == '/boards/?/lists/?/cards/?/activities' || $r_resource_cmd == '/users/?/activities' || $r_resource_cmd == '/users/?/notify_count' || $r_resource_cmd == '/boards/?/activities') { if (!empty($obj['revisions']) && trim($obj['revisions']) !== '') { $revisions = unserialize($obj['revisions']); $obj['revisions'] = $revisions; unset($dif); if (!empty($revisions['new_value'])) { foreach ($revisions['new_value'] as $key => $value) { if ($key != 'is_archived' && $key != 'is_deleted' && $key != 'created' && $key != 'modified' && $key != 'is_offline' && $key != 'uuid' && $key != 'to_date' && $key != 'temp_id' && $obj['type'] != 'moved_card_checklist_item' && $obj['type'] != 'add_card_desc' && $obj['type'] != 'add_card_duedate' && $obj['type'] != 'delete_card_duedate' && $obj['type'] != 'add_background' && $obj['type'] != 'change_background' && $obj['type'] != 'change_visibility') { $old_val = isset($revisions['old_value'][$key]) && $revisions['old_value'][$key] != null && $revisions['old_value'][$key] != 'null' ? $revisions['old_value'][$key] : ''; $new_val = isset($revisions['new_value'][$key]) && $revisions['new_value'][$key] != null && $revisions['new_value'][$key] != 'null' ? $revisions['new_value'][$key] : ''; $dif[] = nl2br(getRevisiondifference($old_val, $new_val)); } if ($obj['type'] == 'add_card_desc' || $obj['type'] == 'add_card_desc' || $obj['type'] == ' edit_card_duedate' || $obj['type'] == 'add_background' || $obj['type'] == 'change_background' || $obj['type'] == 'change_visibility') { $dif[] = $revisions['new_value'][$key]; } } } else { if (!empty($revisions['old_value']) && isset($obj['type']) && $obj['type'] == 'delete_card_comment') { $dif[] = nl2br(getRevisiondifference($revisions['old_value'], '')); } } if (isset($dif)) { $obj['difference'] = $dif; } } if ($obj['type'] === 'add_board_user') { $obj_val_arr = array($obj['foreign_id']); $obj['board_user'] = executeQuery('SELECT * FROM boards_users_listing WHERE id = $1', $obj_val_arr); } else { if ($obj['type'] === 'add_list') { $obj_val_arr = array($obj['list_id']); $obj['list'] = executeQuery('SELECT * FROM lists WHERE id = $1', $obj_val_arr); } else { if ($obj['type'] === 'change_list_position') { $obj_val_arr = array($obj['list_id']); $obj['list'] = executeQuery('SELECT position, board_id FROM lists WHERE id = $1', $obj_val_arr); } else { if ($obj['type'] === 'add_card') { $obj_val_arr = array($obj['card_id']); $obj['card'] = executeQuery('SELECT * FROM cards WHERE id = $1', $obj_val_arr); } else { if ($obj['type'] === 'copy_card') { $obj_val_arr = array($obj['foreign_id']); $obj['card'] = executeQuery('SELECT * FROM cards WHERE id = $1', $obj_val_arr); } else { if ($obj['type'] === 'add_card_checklist') { $obj_val_arr = array($obj['foreign_id']); $obj['checklist'] = executeQuery('SELECT * FROM checklists_listing WHERE id = $1', $obj_val_arr); $obj['checklist']['checklists_items'] = json_decode($obj['checklist']['checklists_items'], true); } else { if ($obj['type'] === 'add_card_label') { $obj_val_arr = array($obj['card_id']); $s_result = pg_query_params($db_lnk, 'SELECT * FROM cards_labels_listing WHERE card_id = $1', $obj_val_arr); while ($row = pg_fetch_assoc($s_result)) { $obj['labels'][] = $row; } } else { if ($obj['type'] === 'add_card_voter') { $obj_val_arr = array($obj['foreign_id']); $obj['voter'] = executeQuery('SELECT * FROM card_voters_listing WHERE id = $1', $obj_val_arr); } else { if ($obj['type'] === 'add_card_user') { $obj_val_arr = array($obj['foreign_id']); $obj['user'] = executeQuery('SELECT * FROM cards_users_listing WHERE id = $1', $obj_val_arr); } else { if ($obj['type'] === 'update_card_checklist') { $obj_val_arr = array($obj['foreign_id']); $obj['checklist'] = executeQuery('SELECT * FROM checklists WHERE id = $1', $obj_val_arr); } else { if ($obj['type'] === 'add_checklist_item' || $obj['type'] === 'update_card_checklist_item' || $obj['type'] === 'moved_card_checklist_item') { $obj_val_arr = array($obj['foreign_id']); $obj['item'] = executeQuery('SELECT * FROM checklist_items WHERE id = $1', $obj_val_arr); } else { if ($obj['type'] === 'add_card_attachment') { $obj_val_arr = array($obj['foreign_id']); $obj['attachment'] = executeQuery('SELECT * FROM card_attachments WHERE id = $1', $obj_val_arr); } else { if ($obj['type'] === 'change_card_position') { $obj_val_arr = array($obj['card_id']); $obj['card'] = executeQuery('SELECT position FROM cards WHERE id = $1', $obj_val_arr); } } } } } } } } } } } } } $row[0] = json_encode($obj); } else { if ($r_resource_cmd == '/boards/?') { $obj = json_decode($row[0], true); global $_server_domain_url; $md5_hash = md5(SECURITYSALT . $r_resource_vars['boards']); $obj['google_syn_url'] = $_server_domain_url . '/ical/' . $r_resource_vars['boards'] . '/' . $md5_hash . '.ics'; $row[0] = json_encode($obj); } } } if ($i == 0 && $count > 1) { echo '['; } echo $row[0]; $i++; if ($i < $count) { echo ','; } else { if ($count > 1) { echo ']'; } } } if (in_array($r_resource_cmd, $arrayResponse) && ($count == 1 || $count == 0)) { echo ']'; } pg_free_result($result); } else { $r_debug .= __LINE__ . ': ' . pg_last_error($db_lnk) . '\\n'; } } else { echo json_encode($response); } }
/** * Common method to handle GET 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 * * @return mixed */ function r_get($r_resource_cmd, $r_resource_vars, $r_resource_filters) { global $r_debug, $db_lnk, $authUser, $_server_domain_url; // switch case.. if taking more length, then associative array... $sql = false; $response = array(); $pg_params = array(); switch ($r_resource_cmd) { case '/users/me': $role_val_arr = array($authUser['role_id']); $role_links = executeQuery('SELECT * FROM role_links_listing WHERE id = $1', $role_val_arr); $val_arr = array($authUser['id']); $user = executeQuery('SELECT * FROM users_listing WHERE id = $1', $val_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 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); unset($user['user']['password']); $response['user'] = $user; $response['user']['organizations'] = json_decode($user['organizations'], true); break; case '/users': $response['users'] = array(); $order_by = 'id'; $direction = 'desc'; $filter_condition = ''; if (!empty($r_resource_filters['sort'])) { $order_by = $r_resource_filters['sort']; $direction = $r_resource_filters['direction']; } else { if (!empty($r_resource_filters['filter'])) { $filter_condition = 'WHERE '; if ($r_resource_filters['filter'] == 'active') { $filter_condition .= 'is_active = 1'; } else { if ($r_resource_filters['filter'] == 'inactive') { $filter_condition .= 'is_active = 0'; } else { if ($r_resource_filters['filter'] == 'ldap') { $filter_condition .= 'is_ldap = 1'; } else { $filter_condition .= 'role_id = ' . $r_resource_filters['filter']; } } } } } $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM users_listing ul ' . $filter_condition . ' ORDER BY ' . $order_by . ' ' . $direction . ') as d '; $c_sql = 'SELECT COUNT(*) FROM users_listing ul'; break; case '/users/logout': $response['user'] = array(); $conditions = array($_GET['token']); pg_query_params($db_lnk, 'DELETE FROM oauth_access_tokens WHERE access_token= $1', $conditions); $authUser = array(); break; case '/users/?/activities': $condition = $condition1 = ''; if (isset($r_resource_filters['last_activity_id']) && $r_resource_filters['last_activity_id'] > 0) { $condition = ' AND al.id > $2'; $condition1 = ' AND al.id > $3'; if (!empty($r_resource_filters['type']) && $r_resource_filters['type'] == 'profile') { $condition = ' AND al.id < $2'; $condition1 = ' AND al.id < $3'; } } if (!empty($authUser) && $authUser['id'] != $r_resource_vars['users']) { $val_array = array($authUser['id']); $logged_user = executeQuery('SELECT boards_users FROM users_listing WHERE id = $1', $val_array); $logged_user_board_ids = array(); if (!empty($logged_user['boards_users'])) { $logged_boards_users = json_decode($logged_user['boards_users'], true); foreach ($logged_boards_users as $logged_boards_user) { $logged_user_board_ids[] = $logged_boards_user['board_id']; } } } $val_array = array($r_resource_vars['users']); $user = executeQuery('SELECT boards_users FROM users_listing WHERE id = $1', $val_array); $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']; } } if (!empty($logged_user_board_ids)) { $board_ids = array_intersect($logged_user_board_ids, $board_ids); } $org_users = pg_query_params($db_lnk, 'SELECT organization_id FROM organizations_users WHERE user_id = $1', $val_array); $org_ids = array(); while ($row = pg_fetch_assoc($org_users)) { $org_ids[] = $row['organization_id']; } if (!empty($authUser) && $authUser['role_id'] == 1 && $authUser['id'] == $r_resource_vars['users'] && empty($r_resource_filters['board_id'])) { if (!empty($r_resource_filters['type']) && $r_resource_filters['type'] == 'profile') { $condition = !empty($r_resource_filters['last_activity_id']) ? ' WHERE al.id < $1' : ""; } else { $condition = !empty($r_resource_filters['last_activity_id']) ? ' WHERE al.id > $1' : ""; } $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM activities_listing al ' . $condition . ' ORDER BY id DESC LIMIT ' . PAGING_COUNT . ') as d'; $c_sql = 'SELECT COUNT(*) FROM activities_listing al' . $condition; } else { if (!empty($r_resource_filters['type']) && $r_resource_filters['type'] == 'profile') { $str = ''; $i = 1; if (!empty($logged_user_board_ids)) { $str .= 'board_id = ANY ( $' . $i . ' ) AND'; $i++; array_push($pg_params, '{' . implode(',', $board_ids) . '}'); } $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM activities_listing al WHERE ' . $str . ' user_id = $' . $i . $condition . ' ORDER BY id DESC LIMIT ' . PAGING_COUNT . ') as d'; $c_sql = 'SELECT COUNT(*) FROM activities_listing al WHERE ' . $str . ' user_id = $' . $i . $condition; array_push($pg_params, $r_resource_vars['users']); } else { if (!empty($r_resource_filters['organization_id'])) { if (isset($r_resource_filters['last_activity_id']) && $r_resource_filters['last_activity_id'] > 0) { $condition1 = ' AND al.id > $4'; } $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM activities_listing al WHERE ((user_id = $1 AND board_id IN (SELECT id FROM boards WHERE organization_id = $2)) OR organization_id = ANY ( $3 )) ' . $condition1 . ' ORDER BY id DESC LIMIT ' . PAGING_COUNT . ') as d'; $c_sql = 'SELECT COUNT(*) FROM activities_listing al WHERE ((user_id = $1 AND board_id IN (SELECT id FROM boards WHERE organization_id = $2)) OR organization_id = ANY ( $3 )) ' . $condition1; array_push($pg_params, $r_resource_vars['users'], $r_resource_filters['organization_id'], '{' . $r_resource_filters['organization_id'] . '}'); } else { if (!empty($r_resource_filters['type']) && ($r_resource_filters['type'] = 'all')) { $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM activities_listing al WHERE (board_id = ANY ( $1 ) OR organization_id = ANY ( $2 ))' . $condition1 . ' ORDER BY id DESC LIMIT ' . PAGING_COUNT . ') as d'; $c_sql = 'SELECT COUNT(*) FROM activities_listing al WHERE (board_id = ANY ( $1 ) OR organization_id = ANY ( $2 ))' . $condition1; array_push($pg_params, '{' . implode(',', $board_ids) . '}', '{' . implode(',', $org_ids) . '}'); } else { if (!empty($r_resource_filters['board_id']) && $r_resource_filters['board_id']) { $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM activities_listing al WHERE user_id = $1 AND board_id = $2' . $condition1 . ' ORDER BY freshness_ts DESC, materialized_path ASC LIMIT ' . PAGING_COUNT . ') as d'; $c_sql = 'SELECT COUNT(*) FROM activities_listing al WHERE user_id = $1 AND board_id = $2' . $condition1; array_push($pg_params, $r_resource_vars['users'], $r_resource_filters['board_id']); } else { $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM activities_listing al WHERE ( board_id = ANY( $1 ) OR organization_id = ANY ( $2 ) )' . $condition1 . ' ORDER BY id DESC LIMIT ' . PAGING_COUNT . ') as d'; $c_sql = 'SELECT COUNT(*) FROM activities_listing al WHERE ( board_id = ANY( $1 ) OR organization_id = ANY ( $2 ) )' . $condition1; array_push($pg_params, '{' . implode(',', $board_ids) . '}', '{' . implode(',', $org_ids) . '}'); } } } } } if (!empty($condition) || !empty($condition1)) { array_push($pg_params, $r_resource_filters['last_activity_id']); } break; case '/users/search': if (!empty($r_resource_filters['organizations'])) { $sql = 'SELECT row_to_json(d) FROM (SELECT u.id, u.username, u.profile_picture_path,u.initials, u.full_name FROM users u LEFT JOIN organizations_users ou ON ou.user_id = u.id WHERE u.is_active = true AND u.is_email_confirmed = true AND '; $sql .= '(ou.organization_id != $1 OR ou.user_id IS null) AND'; array_push($pg_params, $r_resource_filters['organizations']); } else { if (!empty($r_resource_filters['board_id'])) { $sql = 'SELECT row_to_json(d) FROM (SELECT u.id, u.username, u.profile_picture_path,u.initials, u.full_name FROM users u JOIN boards_users bu ON bu.user_id = u.id WHERE u.is_active = true AND u.is_email_confirmed = true AND '; $sql .= 'bu.board_id = $1 AND'; array_push($pg_params, $r_resource_filters['board_id']); } else { if (!empty($r_resource_filters['filter'])) { $sql = 'SELECT row_to_json(d) FROM (SELECT u.id, u.username, u.profile_picture_path,u.initials, u.full_name FROM users u WHERE '; } else { $sql = 'SELECT row_to_json(d) FROM (SELECT u.id, u.username, u.profile_picture_path,u.initials, u.full_name FROM users u WHERE u.is_active = true AND u.is_email_confirmed = true AND '; } } } if (empty($pg_params)) { $sql .= '(LOWER(u.username) LIKE LOWER($1) OR LOWER(u.email) LIKE LOWER($2))) as d '; } else { $sql .= '(LOWER(u.username) LIKE LOWER($2) OR LOWER(u.email) LIKE LOWER($3))) as d '; } array_push($pg_params, $r_resource_filters['q'] . '%', $r_resource_filters['q'] . '%'); if (empty($r_resource_filters['q'])) { $sql = false; $response = array(); $pg_params = array(); } $table = 'users'; break; case '/users/?': $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM users ul WHERE id = $1) as d '; array_push($pg_params, $r_resource_vars['users']); break; case '/users/?/boards': if (!empty($authUser)) { $val_array = array($authUser['id']); $s_result = pg_query_params($db_lnk, 'SELECT board_id FROM board_stars WHERE is_starred = true AND user_id = $1', $val_array); $response['starred_boards'] = array(); while ($row = pg_fetch_assoc($s_result)) { $response['starred_boards'][] = $row['board_id']; } $val_array = array($authUser['id']); $s_result = pg_query_params($db_lnk, 'SELECT o.id as organization_id, o.name as organization_name, bu.board_id FROM boards_users bu LEFT JOIN boards b ON b.id = bu.board_id LEFT JOIN organizations o ON o.id = b.organization_id WHERE bu.user_id = $1', $val_array); $response['user_boards'] = array(); $user_boards = array(); while ($row = pg_fetch_assoc($s_result)) { $response['user_boards'][] = $row; } } break; case '/users/?/cards': if (!empty($authUser) && $authUser['id'] != $r_resource_vars['users']) { $val_array = array($authUser['id']); $logged_user = executeQuery('SELECT boards_users FROM users_listing WHERE id = $1', $val_array); $logged_user_board_ids = array(); if (!empty($logged_user['boards_users'])) { $logged_boards_users = json_decode($logged_user['boards_users'], true); foreach ($logged_boards_users as $logged_boards_user) { $logged_user_board_ids[] = $logged_boards_user['board_id']; } } } $str = ''; $i = 1; if (!empty($logged_user_board_ids)) { $str .= 'board_id = ANY ( $' . $i . ' ) AND'; $i++; array_push($pg_params, '{' . implode(',', $board_ids) . '}'); } $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM users_cards_listing ucl WHERE ' . $str . ' user_id = $' . $i . ' ORDER BY board_id ASC) as d '; array_push($pg_params, $r_resource_vars['users']); break; case '/boards/list': if (!empty($r_resource_filters['type']) && $r_resource_filters['type'] == 'simple') { $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM simple_board_listing ul '; if (!empty($authUser) && $authUser['role_id'] != 1) { $val_array = array($authUser['id']); $s_result = pg_query_params($db_lnk, 'SELECT board_id FROM board_stars WHERE user_id = $1', $val_array); $response['starred_boards'] = array(); while ($row = pg_fetch_assoc($s_result)) { $response['starred_boards'][] = $row['board_id']; } $s_result = pg_query_params($db_lnk, 'SELECT board_id FROM boards_users WHERE user_id = $1', $val_array); $response['user_boards'] = array(); while ($row = pg_fetch_assoc($s_result)) { $response['user_boards'][] = $row['board_id']; } $board_ids = array_merge($response['starred_boards'], $response['user_boards']); $ids = 0; if (!empty($board_ids)) { $board_ids = array_unique($board_ids); $ids = '{' . implode($board_ids, ',') . '}'; } $sql .= 'WHERE ul.id =ANY($1)'; array_push($pg_params, $ids); } $sql .= ' ORDER BY name ASC) as d '; if ($authUser['role_id'] != 1 && empty($board_ids)) { $sql = false; } } else { $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM boards_listing ul '; if (!empty($authUser) && $authUser['role_id'] != 1) { $val_array = array($authUser['id']); $s_result = pg_query_params($db_lnk, 'SELECT board_id FROM board_subscribers WHERE user_id = $1', $val_array); $response['starred_boards'] = array(); while ($row = pg_fetch_assoc($s_result)) { $response['starred_boards'][] = $row['board_id']; } $s_result = pg_query_params($db_lnk, 'SELECT board_id FROM boards_users WHERE user_id = $1', $val_array); $response['user_boards'] = array(); while ($row = pg_fetch_assoc($s_result)) { $response['user_boards'][] = $row['board_id']; } $board_ids = array_merge($response['starred_boards'], $response['user_boards']); $ids = 0; if (!empty($board_ids)) { $board_ids = array_unique($board_ids); $ids = '{' . implode($board_ids, ',') . '}'; } $sql .= 'WHERE ul.id = ANY ($1)'; array_push($pg_params, $ids); } $sql .= ' ORDER BY name ASC) as d '; if ($authUser['role_id'] != 1 && empty($board_ids)) { $sql = false; } } $c_sql = 'SELECT COUNT(*) FROM boards_listing bl'; break; case '/boards': if (!empty($r_resource_filters['type']) && $r_resource_filters['type'] == 'simple') { $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM simple_board_listing ul '; if (!empty($authUser) && $authUser['role_id'] != 1) { $val_array = array($authUser['id']); $s_result = pg_query_params($db_lnk, 'SELECT board_id FROM board_stars WHERE user_id = $1', $val_array); $response['starred_boards'] = array(); while ($row = pg_fetch_assoc($s_result)) { $response['starred_boards'][] = $row['board_id']; } $s_result = pg_query_params($db_lnk, 'SELECT board_id FROM boards_users WHERE user_id = $1', $val_array); $response['user_boards'] = array(); while ($row = pg_fetch_assoc($s_result)) { $response['user_boards'][] = $row['board_id']; } $board_ids = array_merge($response['starred_boards'], $response['user_boards']); $ids = 0; if (!empty($board_ids)) { $board_ids = array_unique($board_ids); $ids = '{' . implode($board_ids, ',') . '}'; } $sql .= 'WHERE ul.id =ANY($1)'; array_push($pg_params, $ids); } $sql .= ' ORDER BY name ASC) as d '; if ($authUser['role_id'] != 1 && empty($board_ids)) { $sql = false; } } else { $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM boards_listing ul '; if (!empty($authUser) && $authUser['role_id'] != 1) { $val_array = array($authUser['id']); $s_result = pg_query_params($db_lnk, 'SELECT board_id FROM board_subscribers WHERE user_id = $1', $val_array); $response['starred_boards'] = array(); while ($row = pg_fetch_assoc($s_result)) { $response['starred_boards'][] = $row['board_id']; } $s_result = pg_query_params($db_lnk, 'SELECT board_id FROM boards_users WHERE user_id = $1', $val_array); $response['user_boards'] = array(); while ($row = pg_fetch_assoc($s_result)) { $response['user_boards'][] = $row['board_id']; } $board_ids = array_merge($response['starred_boards'], $response['user_boards']); $ids = 0; if (!empty($board_ids)) { $board_ids = array_unique($board_ids); $ids = '{' . implode($board_ids, ',') . '}'; } $sql .= 'WHERE ul.id = ANY ($1)'; array_push($pg_params, $ids); } $order_by = 'name'; $direction = 'asc'; $filter_condition = ''; if (!empty($r_resource_filters['sort'])) { $order_by = $r_resource_filters['sort']; $direction = $r_resource_filters['direction']; } else { if (!empty($r_resource_filters['filter'])) { $filter_condition = 'WHERE '; if ($r_resource_filters['filter'] == 'open') { $filter_condition .= 'is_closed = 0'; } else { if ($r_resource_filters['filter'] == 'closed') { $filter_condition .= 'is_closed = 1'; } else { if ($r_resource_filters['filter'] == 'private') { $filter_condition .= 'board_visibility = 0'; } else { if ($r_resource_filters['filter'] == 'public') { $filter_condition .= 'board_visibility = 2'; } else { if ($r_resource_filters['filter'] == 'organization') { $filter_condition .= 'board_visibility = 1'; } } } } } $sql .= $filter_condition; } } $sql .= ' ORDER BY ' . $order_by . ' ' . $direction . ') as d '; if ($authUser['role_id'] != 1 && empty($board_ids)) { $sql = false; } } if (isset($r_resource_filters['page'])) { $c_sql = 'SELECT COUNT(*) FROM boards_listing bl ' . $filter_condition; } break; case '/settings/?': $response = array(); $sql = false; $s_sql = 'SELECT id, name, parent_id FROM setting_categories WHERE parent_id IS null ORDER BY "order" ASC'; $s_result = pg_query_params($db_lnk, $s_sql, array()); while ($row = pg_fetch_assoc($s_result)) { if ($row['id'] == $r_resource_vars['settings'] || $row['parent_id'] == $r_resource_vars['settings']) { $s_sql = 'SELECT s.*, sc.name as category_name FROM settings s LEFT JOIN setting_categories sc ON sc.id = s.setting_category_id WHERE setting_category_id = $1 OR setting_category_parent_id = $2 ORDER BY "order" ASC'; $s_val = array($row['id'], $row['id']); $ss_result = pg_query_params($db_lnk, $s_sql, $s_val); while ($srow = pg_fetch_assoc($ss_result)) { $row['settings'][] = $srow; } } $response[] = $row; } break; case '/email_templates/?': $response = array(); $sql = false; $s_sql = 'SELECT id, display_name FROM email_templates ORDER BY id ASC'; $s_result = pg_query_params($db_lnk, $s_sql, array()); while ($row = pg_fetch_assoc($s_result)) { if ($row['id'] == $r_resource_vars['email_templates']) { $s_sql = 'SELECT from_email, reply_to_email, name, description, subject, email_text_content, email_variables, display_name FROM email_templates WHERE id = $1'; $s_val = array($row['id']); $ss_result = pg_query_params($db_lnk, $s_sql, $s_val); while ($srow = pg_fetch_assoc($ss_result)) { $row['template'] = $srow; } } $response[] = $row; } break; case '/boards/?': $s_sql = 'SELECT id FROM boards WHERE id = $1'; $board[] = $r_resource_vars['boards']; $check_board = executeQuery($s_sql, $board); if (!empty($check_board)) { $s_sql = 'SELECT b.board_visibility, bu.user_id FROM boards AS b LEFT JOIN boards_users AS bu ON bu.board_id = b.id WHERE b.id = $1'; $arr[] = $r_resource_vars['boards']; if (!empty($authUser) && $authUser['role_id'] != 1) { $s_sql .= ' AND (b.board_visibility = 2 OR bu.user_id = $2)'; $arr[] = $authUser['id']; } else { if (empty($authUser)) { $s_sql .= ' AND b.board_visibility = 2 '; } } $check_visibility = executeQuery($s_sql, $arr); if (!empty($check_visibility)) { $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM boards_listing ul WHERE id = $1 ORDER BY id DESC) as d '; array_push($pg_params, $r_resource_vars['boards']); } else { $response['error']['type'] = 'visibility'; $response['error']['message'] = 'Unauthorized'; header($_SERVER['SERVER_PROTOCOL'] . ' 401 Unauthorized', true, 401); } } else { $response['error']['type'] = 'board'; $response['error']['message'] = 'Bad Request'; header($_SERVER['SERVER_PROTOCOL'] . ' 400 Bad Request', true, 400); } break; case '/organizations': $organization_ids = array(); $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM organizations_listing'; if (!empty($authUser) && $authUser['role_id'] != 1) { $s_sql = 'SELECT b.organization_id FROM boards_users AS bu LEFT JOIN boards AS b ON b.id = bu.board_id WHERE bu.user_id = $1'; $conditions = array($authUser['id']); $s_result = pg_query_params($db_lnk, $s_sql, $conditions); while ($row = pg_fetch_assoc($s_result)) { if ($row['organization_id'] != 0) { array_push($organization_ids, $row['organization_id']); } } $s_sql = 'SELECT id FROM organizations WHERE user_id = $1'; $conditions = array($authUser['id']); $s_result = pg_query_params($db_lnk, $s_sql, $conditions); while ($row = pg_fetch_assoc($s_result)) { array_push($organization_ids, $row['id']); } $s_sql = 'SELECT organization_id FROM organizations_users WHERE user_id = $1'; $conditions = array($authUser['id']); $s_result = pg_query_params($db_lnk, $s_sql, $conditions); while ($row = pg_fetch_assoc($s_result)) { array_push($organization_ids, $row['organization_id']); } if (!empty($organization_ids)) { $sql .= ' WHERE id IN (' . implode(",", array_unique($organization_ids)) . ')'; } else { $sql .= ' WHERE user_id = ' . $authUser['id']; } } $sql .= ' ORDER BY id ASC) as d '; break; case '/organizations/?': $s_sql = 'SELECT o.organization_visibility, ou.user_id FROM organizations AS o LEFT JOIN organizations_users AS ou ON ou.organization_id = o.id WHERE o.id = $1'; $arr[] = $r_resource_vars['organizations']; if (!empty($authUser) && $authUser['role_id'] != 1) { $s_sql .= ' AND (o.organization_visibility = 1 OR ou.user_id = $2)'; $arr[] = $authUser['id']; } else { if (empty($authUser)) { $s_sql .= ' AND o.organization_visibility = 1 '; } } $check_visibility = executeQuery($s_sql, $arr); if (!empty($check_visibility)) { $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM organizations_listing ul WHERE id = $1 ORDER BY id DESC) as d '; array_push($pg_params, $r_resource_vars['organizations']); } else { $response['error']['type'] = 'visibility'; $response['error']['message'] = 'Unauthorized'; } break; case '/boards/?/lists/?/cards/?/activities': case '/boards/?/lists/?/activities': case '/boards/?/activities': $val_array = array($r_resource_vars['boards']); $board = executeQuery('SELECT board_visibility FROM boards_listing WHERE id = $1', $val_array); $val_array = array($r_resource_vars['boards'], $authUser['id']); $boards_user = executeQuery('SELECT * FROM boards_users WHERE board_id = $1 AND user_id = $2', $val_array); if (!empty($authUser) && $authUser['role_id'] == 1 || $board['board_visibility'] == 2 || !empty($boards_user)) { $condition = ''; array_push($pg_params, $r_resource_vars['boards']); $i = 2; if (isset($r_resource_filters['last_activity_id']) && $r_resource_filters['last_activity_id'] > 0) { if (!empty($r_resource_filters['type']) && $r_resource_filters['type'] == 'all') { $condition = ' AND al.id < $' . $i; } else { $condition = ' AND al.id > $' . $i; } array_push($pg_params, $r_resource_filters['last_activity_id']); $i++; } if (!empty($r_resource_vars['lists'])) { $condition .= ' AND al.list_id = $' . $i; array_push($pg_params, $r_resource_vars['lists']); $i++; } if (!empty($r_resource_vars['cards'])) { $condition .= ' AND al.card_id = $' . $i; array_push($pg_params, $r_resource_vars['cards']); } if (!empty($r_resource_filters['filter'])) { $condition .= ' AND al.type = $' . $i; array_push($pg_params, $r_resource_filters['filter']); $i++; } $limit = PAGING_COUNT; if (!empty($r_resource_filters['limit'])) { $limit = $r_resource_filters['limit']; } $sql = 'SELECT row_to_json(d) FROM (SELECT al.*, u.username, u.profile_picture_path, u.initials, u.full_name, c.description, c.name as card_name FROM activities_listing al LEFT JOIN users u ON al.user_id = u.id LEFT JOIN cards c on al.card_id = c.id WHERE al.board_id = $1' . $condition . ' ORDER BY al.id DESC LIMIT ' . $limit . ') as d '; if (empty($r_resource_filters['from']) || !empty($r_resource_filters['from']) && $r_resource_filters['from'] != 'app') { $c_sql = 'SELECT COUNT(*) FROM activities_listing al WHERE al.board_id = $1' . $condition; } } break; case '/boards/?/boards_stars': $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM board_stars bs WHERE board_id = $1'; array_push($pg_params, $r_resource_vars['boards']); if (!empty($authUser) && $authUser['role_id'] != 1) { $sql .= ' and user_id = $2'; array_push($pg_params, $authUser['id']); } $sql .= ' ORDER BY id DESC) as d '; break; case '/boards/?/board_subscribers': $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM board_subscribers ul WHERE board_id = $1'; array_push($pg_params, $r_resource_vars['boards']); if (!empty($authUser) && $authUser['role_id'] != 1) { $sql .= ' and user_id = $2'; array_push($pg_params, $authUser['id']); } $sql .= ' ORDER BY id DESC) as d '; break; case '/boards/search': $sql = 'SELECT row_to_json(d) FROM (SELECT id, name, background_color FROM boards ul WHERE name ILIKE $1 ORDER BY id DESC) as d '; array_push($pg_params, '%' . $r_resource_filters['q'] . '%'); break; case '/boards/?/lists/?/cards/?': $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM cards_listing cll WHERE id = $1) as d '; array_push($pg_params, $r_resource_vars['cards']); break; case '/boards/?/lists': $fields = !empty($r_resource_filters['fields']) ? $r_resource_filters['fields'] : '*'; $sql = 'SELECT row_to_json(d) FROM (SELECT ' . $fields . ' FROM lists_listing cll WHERE board_id = $1) as d '; array_push($pg_params, $r_resource_vars['boards']); if (empty($r_resource_filters['from']) || !empty($r_resource_filters['from']) && $r_resource_filters['from'] != 'app') { $c_sql = 'SELECT COUNT(*) FROM lists_listing cll'; } break; case '/boards/?/lists/?/cards': $fields = !empty($r_resource_filters['fields']) ? $r_resource_filters['fields'] : '*'; $sql = 'SELECT row_to_json(d) FROM (SELECT ' . $fields . ' FROM cards_listing cll WHERE board_id = $1 AND list_id = $2) as d '; array_push($pg_params, $r_resource_vars['boards']); array_push($pg_params, $r_resource_vars['lists']); if (empty($r_resource_filters['from']) || !empty($r_resource_filters['from']) && $r_resource_filters['from'] != 'app') { $c_sql = 'SELECT COUNT(*) FROM cards_listing cll'; } break; case '/activities': $condition = ''; $i = 1; if (isset($r_resource_filters['last_activity_id'])) { $condition = ' WHERE al.id < $' . $i; array_push($pg_params, $r_resource_filters['last_activity_id']); $i++; } if (!empty($r_resource_filters['filter'])) { $condition .= ' AND al.type = $' . $i; array_push($pg_params, $r_resource_filters['filter']); $i++; } $limit = PAGING_COUNT; if (!empty($r_resource_filters['limit'])) { $limit = $r_resource_filters['limit']; } $sql = 'SELECT row_to_json(d) FROM (SELECT al.*, u.username, u.profile_picture_path, u.initials, u.full_name, c.description FROM activities_listing al LEFT JOIN users u ON al.user_id = u.id LEFT JOIN cards c ON al.card_id = c.id ' . $condition . ' ORDER BY id DESC limit ' . $limit . ') as d '; if (empty($r_resource_filters['from']) || !empty($r_resource_filters['from']) && $r_resource_filters['from'] != 'app') { $c_sql = 'SELECT COUNT(*) FROM activities_listing al' . $condition; } break; case '/boards/?/lists/?/cards/?/checklists': $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM checklist_add_listing al WHERE board_id = $1) as d '; array_push($pg_params, $r_resource_vars['boards']); break; case '/boards/?/visibility': $sql = 'SELECT board_visibility FROM boards bl WHERE bl.id = $1'; array_push($pg_params, $r_resource_vars['boards']); break; case '/workflow_templates': $files = glob(APP_PATH . '/client/js/workflow_templates/*.json', GLOB_BRACE); $i = 0; foreach ($files as $file) { $file_name = basename($file, '.json'); $data = file_get_contents($file); $json = json_decode($data, true); $response[] = array('name' => $json['name'], 'value' => implode($json['lists'], ', ')); } break; case '/search': if (!empty($r_resource_filters['q'])) { $response = array(); if (!empty($r_resource_filters['q'])) { if (preg_match('/^\\".*\\"$/', $r_resource_filters['q'])) { $q = $r_resource_filters['q']; } else { $q = '*' . $r_resource_filters['q'] . '*'; } if ($authUser['role_id'] != 1) { $q .= ' AND board_users.user_id:' . $authUser['id']; } $elasticsearch_url = ELASTICSEARCH_URL . ELASTICSEARCH_INDEX . '/cards/_search?q=' . urlencode($q); $search_response = doGet($elasticsearch_url); $response['result'] = array(); if (!empty($search_response['hits']['hits'])) { foreach ($search_response['hits']['hits'] as $result) { $card = array('id' => $result['_source']['id'], 'name' => $result['_source']['name'], 'list_id' => $result['_source']['list_id'], 'list_name' => $result['_source']['list'], 'board_id' => $result['_source']['board_id'], 'board_name' => $result['_source']['board'], 'name' => $result['_source']['name'], 'type' => 'cards'); $response['result'][] = $card; } } } } break; case '/boards/?/lists/?/cards/?/search': $sql = 'SELECT row_to_json(d) FROM (SELECT bul.id, bul.user_id, bul.username, bul.profile_picture_path, bul.full_name, bul.initials FROM boards_users_listing bul WHERE'; $sql .= '(bul.username LIKE $1 OR bul.email LIKE $2) AND bul.board_id = $3) as d '; array_push($pg_params, '%' . $r_resource_filters['q'] . '%', '%' . $r_resource_filters['q'] . '%', $r_resource_vars['boards']); if (empty($r_resource_filters['q'])) { $sql = false; $response = array(); $pg_params = array(); } $table = 'users'; break; case '/boards/?/cards/search': $user_id = !empty($authUser['id']) ? $authUser['id'] : 0; $sql = 'SELECT row_to_json(d) FROM (SELECT DISTINCT c.id, c.name, bu.board_id FROM boards_users bu join cards c on c.board_id = bu.board_id WHERE bu.board_id IN (SELECT board_id FROM boards_users WHERE user_id = $1) AND c.name LIKE $2 ORDER BY id ASC) as d'; array_push($pg_params, $user_id, '%' . $r_resource_filters['q'] . '%'); if (empty($r_resource_filters['q'])) { $sql = false; $response = array(); $pg_params = array(); } break; case '/acl_links': $sql = false; $acl_links_sql = 'SELECT row_to_json(d) FROM (SELECT acl_links.id, acl_links.name, acl_links.group_id, ( SELECT array_to_json(array_agg(row_to_json(alr.*))) AS array_to_json FROM ( SELECT acl_links_roles.role_id FROM acl_links_roles acl_links_roles WHERE acl_links_roles.acl_link_id = acl_links.id ORDER BY acl_links_roles.role_id) alr) AS acl_links_roles, acl_links.is_guest_action, acl_links.is_user_action, acl_links.is_admin_action, acl_links.is_hide FROM acl_links acl_links ORDER BY group_id ASC, id ASC) as d'; $acl_links_result = pg_query_params($db_lnk, $acl_links_sql, array()); $response['acl_links'] = array(); while ($row = pg_fetch_assoc($acl_links_result)) { $response['acl_links'][] = json_decode($row['row_to_json'], true); } $roles_sql = 'SELECT id, name FROM roles'; $roles_result = pg_query_params($db_lnk, $roles_sql, array()); $response['roles'] = array(); while ($row = pg_fetch_assoc($roles_result)) { $response['roles'][] = $row; } $acl_board_links_sql = 'SELECT row_to_json(d) FROM (SELECT acl_board_links.id, acl_board_links.name, acl_board_links.group_id, ( SELECT array_to_json(array_agg(row_to_json(alr.*))) AS array_to_json FROM ( SELECT acl_board_links_boards_user_roles.board_user_role_id FROM acl_board_links_boards_user_roles acl_board_links_boards_user_roles WHERE acl_board_links_boards_user_roles.acl_board_link_id = acl_board_links.id ORDER BY acl_board_links_boards_user_roles.board_user_role_id) alr) AS acl_board_links_boards_user_roles, acl_board_links.is_hide FROM acl_board_links acl_board_links ORDER BY group_id ASC, id ASC) as d'; $acl_board_links_result = pg_query_params($db_lnk, $acl_board_links_sql, array()); $response['acl_board_links'] = array(); while ($row = pg_fetch_assoc($acl_board_links_result)) { $response['acl_board_links'][] = json_decode($row['row_to_json'], true); } $board_user_roles_sql = 'SELECT id, name FROM board_user_roles'; $board_user_roles_result = pg_query_params($db_lnk, $board_user_roles_sql, array()); $response['board_user_roles'] = array(); while ($row = pg_fetch_assoc($board_user_roles_result)) { $response['board_user_roles'][] = $row; } $acl_organization_links_sql = 'SELECT row_to_json(d) FROM (SELECT acl_organization_links.id, acl_organization_links.name, acl_organization_links.group_id, ( SELECT array_to_json(array_agg(row_to_json(alr.*))) AS array_to_json FROM ( SELECT acl_organization_links_organizations_user_roles.organization_user_role_id FROM acl_organization_links_organizations_user_roles acl_organization_links_organizations_user_roles WHERE acl_organization_links_organizations_user_roles.acl_organization_link_id = acl_organization_links.id ORDER BY acl_organization_links_organizations_user_roles.organization_user_role_id) alr) AS acl_organization_links_organizations_user_roles FROM acl_organization_links acl_organization_links ORDER BY group_id ASC, id ASC) as d'; $acl_organization_links_result = pg_query_params($db_lnk, $acl_organization_links_sql, array()); $response['acl_organization_links'] = array(); while ($row = pg_fetch_assoc($acl_organization_links_result)) { $response['acl_organization_links'][] = json_decode($row['row_to_json'], true); } $organization_user_roles_sql = 'SELECT id, name FROM organization_user_roles'; $organization_user_roles_result = pg_query_params($db_lnk, $organization_user_roles_sql, array()); $response['organization_user_roles'] = array(); while ($row = pg_fetch_assoc($organization_user_roles_result)) { $response['organization_user_roles'][] = $row; } break; case '/settings': $role_id = empty($user['role_id']) ? 3 : $user['role_id']; $s_sql = pg_query_params($db_lnk, 'SELECT name, value FROM settings WHERE name = \'SITE_NAME\' OR name = \'SITE_TIMEZONE\' OR name = \'DROPBOX_APPKEY\' OR name = \'LABEL_ICON\' OR name = \'FLICKR_API_KEY\' or name = \'LDAP_LOGIN_ENABLED\' OR name = \'DEFAULT_LANGUAGE\' OR name = \'IMAP_EMAIL\' OR name = \'STANDARD_LOGIN_ENABLED\'', array()); while ($row = pg_fetch_assoc($s_sql)) { $response[$row['name']] = $row['value']; } $files = glob(APP_PATH . '/client/apps/*/app.json', GLOB_BRACE); foreach ($files as $file) { $content = file_get_contents($file); $data = json_decode($content, true); if ($data['enabled'] === true) { if (!empty($data['settings'])) { foreach ($data['settings'] as $key => $value) { if ($value['is_public']) { $value['name'] = $key; $response['apps']['settings'][] = $value; } } } foreach ($data['assets']['js'] as $jsfiles) { $response['apps']['js'][] = $jsfiles; } foreach ($data['assets']['css'] as $cssfiles) { $response['apps']['css'][] = $cssfiles; } } } break; case '/apps': $files = glob(APP_PATH . '/client/apps/*/app.json', GLOB_BRACE); foreach ($files as $file) { $folder = explode('/', $file); $content = file_get_contents($file); $data = json_decode($content, true); $data['folder'] = $folder[count($folder) - 2]; $response[] = $data; } break; case '/apps/settings': $content = file_get_contents(APP_PATH . '/client/apps/' . $r_resource_filters['app'] . '/app.json'); $data = json_decode($content, true); if (file_exists(APP_PATH . '/tmp/cache/site_url_for_shell.php')) { include_once APP_PATH . '/tmp/cache/site_url_for_shell.php'; } if (!empty($data['settings'])) { foreach ($data['settings'] as $key => $value) { $value['name'] = $key; $value['folder'] = $r_resource_filters['app']; $value['app_name'] = $data['name']; $replaceContent = array('##SITE_NAME##' => SITE_NAME, '##SITE_URL##' => $_server_domain_url); $value['settings_description'] = strtr($data['settings_description'], $replaceContent); $response[] = $value; } } break; case '/oauth/clients': $response['oauth_clients'] = array(); $condition = ''; if (!empty($_GET['id'])) { $condition = 'WHERE id = $1'; $condition_param = $_GET['id']; } if (!empty($condition_param)) { array_push($pg_params, $condition_param); } $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM oauth_clients c ' . $condition . ') as d '; $c_sql = 'SELECT COUNT(*) FROM oauth_clients c'; break; case '/oauth/applications': $response['applications'] = array(); $sql = 'SELECT row_to_json(d) FROM (SELECT DISTINCT ON (ort.client_id) ort.client_id, oc.client_name FROM oauth_refresh_tokens ort LEFT JOIN oauth_clients oc ON ort.client_id = oc.client_id WHERE ort.user_id = $1 AND ort.client_id != $2) as d '; array_push($pg_params, $authUser['username'], '7742632501382313'); $c_sql = 'SELECT COUNT(*) FROM oauth_clients oc'; break; case '/webhooks': $response['webhooks'] = array(); $sql = 'SELECT row_to_json(d) FROM (SELECT * FROM webhooks w ORDER BY id ASC) as d '; $c_sql = 'SELECT COUNT(*) FROM webhooks w'; break; default: header($_SERVER['SERVER_PROTOCOL'] . ' 501 Not Implemented', true, 501); } if (!empty($sql)) { $_metadata = array(); if (!empty($c_sql)) { $c_result = pg_query_params($db_lnk, $c_sql, $pg_params); $c_data = pg_fetch_object($c_result, 0); $page = isset($r_resource_filters['page']) && $r_resource_filters['page'] ? $r_resource_filters['page'] : 1; $start = ($page - 1) * PAGING_COUNT; $total_page = ceil($c_data->count / PAGING_COUNT); $showing = $start + PAGING_COUNT > $c_data->count ? $c_data->count - $start : PAGING_COUNT; $_metadata = array('noOfPages' => $total_page, 'total_records' => $c_data->count, 'limit' => PAGING_COUNT, 'offset' => $start, 'showing' => $showing, 'maxSize' => 5); $sql .= ' LIMIT ' . PAGING_COUNT . ' OFFSET ' . $start; } if ($r_resource_cmd == '/users') { $filter_count = array(); $val_array = array(true); $active_count = executeQuery('SELECT count(*) FROM users WHERE is_active = $1', $val_array); $filter_count['active'] = $active_count['count']; $val_array = array(0); $inactive_count = executeQuery('SELECT count(*) FROM users WHERE is_active = $1', $val_array); $filter_count['inactive'] = $inactive_count['count']; $val_array = array(true); $ldap_count = executeQuery('SELECT count(*) FROM users WHERE is_ldap = $1', $val_array); $filter_count['ldap'] = $ldap_count['count']; $val_array = array(3); $s_result = pg_query_params($db_lnk, 'SELECT * FROM roles WHERE id != $1', $val_array); $roles = array(); $i = 0; while ($row = pg_fetch_assoc($s_result)) { $roles[$i]['id'] = $row['id']; $roles[$i]['name'] = ucfirst($row['name']); $val_array = array($row['id']); $user_count = executeQuery('SELECT count(*) FROM users WHERE role_id = $1', $val_array); $roles[$i]['count'] = $user_count['count']; $i++; } } if ($r_resource_cmd == '/boards') { $filter_count = array(); $val_array = array(true); $closed_count = executeQuery('SELECT count(*) FROM boards WHERE is_closed = $1', $val_array); $filter_count['closed'] = $closed_count['count']; $val_array = array(0); $open_count = executeQuery('SELECT count(*) FROM boards WHERE is_closed = $1', $val_array); $filter_count['open'] = $open_count['count']; $val_array = array(0); $private_count = executeQuery('SELECT count(*) FROM boards WHERE board_visibility = $1', $val_array); $filter_count['private'] = $private_count['count']; $val_array = array(2); $public_count = executeQuery('SELECT count(*) FROM boards WHERE board_visibility = $1', $val_array); $filter_count['public'] = $public_count['count']; $val_array = array(1); $organization_count = executeQuery('SELECT count(*) FROM boards WHERE board_visibility = $1', $val_array); $filter_count['organization'] = $organization_count['count']; $board_user_roles_result = pg_query_params($db_lnk, 'SELECT id, name FROM board_user_roles', array()); $board_user_roles = array(); while ($board_user = pg_fetch_assoc($board_user_roles_result)) { $board_user_roles[] = $board_user; } } $arrayResponse = array('/users/?/cards', '/users/?/activities', '/users/search', '/boards', '/boards/?/lists', '/boards/?/lists/?/cards', '/boards/?/activities', '/boards/?/lists/?/activities', '/boards/?/lists/?/cards/?/activities', '/boards/?/lists/?/cards/?/search', '/cards/search', '/organizations', '/activities', '/oauth/clients', '/oauth/applications', '/webhooks'); if ($result = pg_query_params($db_lnk, $sql, $pg_params)) { $data = array(); $count = pg_num_rows($result); while ($row = pg_fetch_row($result)) { $obj = json_decode($row[0], true); if (isset($obj['board_activities']) && !empty($obj['board_activities'])) { for ($k = 0; $k < count($obj['board_activities']); $k++) { if (!empty($obj['board_activities'][$k]['revisions']) && trim($obj['board_activities'][$k]['revisions']) != '') { $revisions = unserialize($obj['board_activities'][$k]['revisions']); unset($dif); if (!empty($revisions['new_value'])) { foreach ($revisions['new_value'] as $key => $value) { if ($key != 'is_archived' && $key != 'is_deleted' && $key != 'created' && $key != 'modified' && $obj['type'] != 'moved_card_checklist_item' && $obj['type'] != 'add_card_desc' && $obj['type'] != 'add_card_duedate' && $obj['type'] != 'delete_card_duedate' && $obj['type'] != 'change_visibility' && $obj['type'] != 'add_background' && $obj['type'] != 'change_background') { $old_val = $revisions['old_value'][$key] != null && $revisions['old_value'][$key] != 'null' ? $revisions['old_value'][$key] : ''; $new_val = $revisions['new_value'][$key] != null && $revisions['new_value'][$key] != 'null' ? $revisions['new_value'][$key] : ''; $dif[] = nl2br(getRevisiondifference($old_val, $old_val)); } if ($obj['type'] == 'add_card_desc' || $obj['type'] == 'add_card_desc' || $obj['type'] == ' edit_card_duedate' || $obj['type'] == 'change_visibility' || $obj['type'] == 'add_background' || $obj['type'] == 'change_background') { $dif[] = $revisions['new_value'][$key]; } } if (isset($dif)) { $obj['board_activities'][$k]['difference'] = $dif; } } else { if (!empty($revisions['old_value']) && isset($obj['type']) && $obj['type'] == 'delete_card_comment') { $obj['board_activities'][$k]['difference'] = nl2br(getRevisiondifference($revisions['old_value'], '')); } } } } if ($r_resource_cmd == '/boards/?') { global $_server_domain_url; $md5_hash = md5(SECURITYSALT . $r_resource_vars['boards']); $obj['google_syn_url'] = $_server_domain_url . '/ical/' . $r_resource_vars['boards'] . '/' . $md5_hash . '.ics'; } } else { if ($r_resource_cmd == '/boards/?/lists/?/cards/?/activities' || $r_resource_cmd == '/users/?/activities' || $r_resource_cmd == '/users/?/notify_count' || $r_resource_cmd == '/boards/?/activities') { if (!empty($obj['revisions']) && trim($obj['revisions']) !== '') { $revisions = unserialize($obj['revisions']); $obj['revisions'] = $revisions; unset($dif); if (!empty($revisions['new_value'])) { foreach ($revisions['new_value'] as $key => $value) { if ($key != 'is_archived' && $key != 'is_deleted' && $key != 'created' && $key != 'modified' && $key != 'is_offline' && $key != 'uuid' && $key != 'to_date' && $key != 'temp_id' && $obj['type'] != 'moved_card_checklist_item' && $obj['type'] != 'add_card_desc' && $obj['type'] != 'add_card_duedate' && $obj['type'] != 'delete_card_duedate' && $obj['type'] != 'add_background' && $obj['type'] != 'change_background' && $obj['type'] != 'change_visibility') { $old_val = isset($revisions['old_value'][$key]) && $revisions['old_value'][$key] != null && $revisions['old_value'][$key] != 'null' ? $revisions['old_value'][$key] : ''; $new_val = isset($revisions['new_value'][$key]) && $revisions['new_value'][$key] != null && $revisions['new_value'][$key] != 'null' ? $revisions['new_value'][$key] : ''; $dif[] = nl2br(getRevisiondifference($old_val, $new_val)); } if ($obj['type'] == 'add_card_desc' || $obj['type'] == 'add_card_desc' || $obj['type'] == ' edit_card_duedate' || $obj['type'] == 'add_background' || $obj['type'] == 'change_background' || $obj['type'] == 'change_visibility') { $dif[] = $revisions['new_value'][$key]; } } } else { if (!empty($revisions['old_value']) && isset($obj['type']) && $obj['type'] == 'delete_card_comment') { $dif[] = nl2br(getRevisiondifference($revisions['old_value'], '')); } } if (isset($dif)) { $obj['difference'] = $dif; } } if ($obj['type'] === 'add_board_user') { $obj_val_arr = array($obj['foreign_id']); $obj['board_user'] = executeQuery('SELECT * FROM boards_users_listing WHERE id = $1', $obj_val_arr); } else { if ($obj['type'] === 'add_list') { $obj_val_arr = array($obj['list_id']); $obj['list'] = executeQuery('SELECT * FROM lists WHERE id = $1', $obj_val_arr); } else { if ($obj['type'] === 'change_list_position') { $obj_val_arr = array($obj['list_id']); $obj['list'] = executeQuery('SELECT position, board_id FROM lists WHERE id = $1', $obj_val_arr); } else { if ($obj['type'] === 'add_card') { $obj_val_arr = array($obj['card_id']); $obj['card'] = executeQuery('SELECT * FROM cards WHERE id = $1', $obj_val_arr); } else { if ($obj['type'] === 'copy_card') { $obj_val_arr = array($obj['foreign_id']); $obj['card'] = executeQuery('SELECT * FROM cards WHERE id = $1', $obj_val_arr); } else { if ($obj['type'] === 'add_card_checklist') { $obj_val_arr = array($obj['foreign_id']); $obj['checklist'] = executeQuery('SELECT * FROM checklists_listing WHERE id = $1', $obj_val_arr); $obj['checklist']['checklists_items'] = json_decode($obj['checklist']['checklists_items'], true); } else { if ($obj['type'] === 'add_card_label') { $obj_val_arr = array($obj['card_id']); $s_result = pg_query_params($db_lnk, 'SELECT * FROM cards_labels_listing WHERE card_id = $1', $obj_val_arr); while ($row = pg_fetch_assoc($s_result)) { $obj['labels'][] = $row; } } else { if ($obj['type'] === 'add_card_voter') { $obj_val_arr = array($obj['foreign_id']); $obj['voter'] = executeQuery('SELECT * FROM card_voters_listing WHERE id = $1', $obj_val_arr); } else { if ($obj['type'] === 'add_card_user') { $obj_val_arr = array($obj['foreign_id']); $obj['user'] = executeQuery('SELECT * FROM cards_users_listing WHERE id = $1', $obj_val_arr); } else { if ($obj['type'] === 'update_card_checklist') { $obj_val_arr = array($obj['foreign_id']); $obj['checklist'] = executeQuery('SELECT * FROM checklists WHERE id = $1', $obj_val_arr); } else { if ($obj['type'] === 'add_checklist_item' || $obj['type'] === 'update_card_checklist_item' || $obj['type'] === 'moved_card_checklist_item') { $obj_val_arr = array($obj['foreign_id']); $obj['item'] = executeQuery('SELECT * FROM checklist_items WHERE id = $1', $obj_val_arr); } else { if ($obj['type'] === 'add_card_attachment') { $obj_val_arr = array($obj['foreign_id']); $obj['attachment'] = executeQuery('SELECT * FROM card_attachments WHERE id = $1', $obj_val_arr); } else { if ($obj['type'] === 'change_card_position') { $obj_val_arr = array($obj['card_id']); $obj['card'] = executeQuery('SELECT position FROM cards WHERE id = $1', $obj_val_arr); } } } } } } } } } } } } } } else { if ($r_resource_cmd == '/boards/?') { global $_server_domain_url; $md5_hash = md5(SECURITYSALT . $r_resource_vars['boards']); $obj['google_syn_url'] = $_server_domain_url . '/ical/' . $r_resource_vars['boards'] . '/' . $md5_hash . '.ics'; $acl_links_sql = 'SELECT row_to_json(d) FROM (SELECT * FROM acl_board_links_listing) as d'; $acl_links_result = pg_query_params($db_lnk, $acl_links_sql, array()); $obj['acl_links'] = array(); while ($row = pg_fetch_assoc($acl_links_result)) { $obj['acl_links'][] = json_decode($row['row_to_json'], true); } $board_user_roles_sql = 'SELECT row_to_json(d) FROM (SELECT * FROM board_user_roles) as d'; $board_user_roles_result = pg_query_params($db_lnk, $board_user_roles_sql, array()); $obj['board_user_roles'] = array(); while ($row = pg_fetch_assoc($board_user_roles_result)) { $obj['board_user_roles'][] = json_decode($row['row_to_json'], true); } } else { if ($r_resource_cmd == '/activities') { if (!empty($obj['revisions']) && trim($obj['revisions']) != '') { $revisions = unserialize($obj['revisions']); $obj['revisions'] = $revisions; unset($dif); if (!empty($revisions['new_value'])) { foreach ($revisions['new_value'] as $key => $value) { if ($key != 'is_archived' && $key != 'is_deleted' && $key != 'created' && $key != 'modified' && $key != 'is_offline' && $key != 'uuid' && $key != 'to_date' && $key != 'temp_id' && $obj['type'] != 'moved_card_checklist_item' && $obj['type'] != 'add_card_desc' && $obj['type'] != 'add_card_duedate' && $obj['type'] != 'delete_card_duedate' && $obj['type'] != 'add_background' && $obj['type'] != 'change_background' && $obj['type'] != 'change_visibility') { $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 ($obj['type'] == 'add_card_desc' || $obj['type'] == 'edit_card_duedate' || $obj['type'] == 'add_background' || $obj['type'] == 'change_background' || $obj['type'] == 'change_visibility') { $dif[] = $revisions['new_value'][$key]; } } } else { if (!empty($revisions['old_value']) && isset($obj['type']) && $obj['type'] == 'delete_card_comment') { $dif[] = nl2br(getRevisiondifference($revisions['old_value'], '')); } } if (isset($dif)) { $obj['difference'] = $dif; } } } else { if ($r_resource_cmd == '/organizations/?') { $acl_links_sql = 'SELECT row_to_json(d) FROM (SELECT * FROM acl_organization_links_listing) as d'; $acl_links_result = pg_query_params($db_lnk, $acl_links_sql, array()); $obj['acl_links'] = array(); while ($row = pg_fetch_assoc($acl_links_result)) { $obj['acl_links'][] = json_decode($row['row_to_json'], true); } $organization_user_roles_sql = 'SELECT row_to_json(d) FROM (SELECT * FROM organization_user_roles) as d'; $organization_user_roles_result = pg_query_params($db_lnk, $organization_user_roles_sql, array()); $obj['organization_user_roles'] = array(); while ($row = pg_fetch_assoc($organization_user_roles_result)) { $obj['organization_user_roles'][] = json_decode($row['row_to_json'], true); } } } } } } if (!empty($_metadata)) { $data['data'][] = $obj; } elseif (in_array($r_resource_cmd, $arrayResponse)) { $data[] = $obj; } else { $data = $obj; } } if (!empty($_metadata)) { $data['_metadata'] = $_metadata; } if (!empty($_metadata) && !empty($filter_count)) { $data['filter_count'] = $filter_count; } if (!empty($_metadata) && !empty($board_user_roles)) { $data['board_user_roles'] = $board_user_roles; } if (!empty($roles)) { $data['roles'] = $roles; } echo json_encode($data); pg_free_result($result); } else { $r_debug .= __LINE__ . ': ' . pg_last_error($db_lnk) . '\\n'; } } else { echo json_encode($response); } }
} return $result; } else { return "Invalid schedule"; } } $deviceName = filter_input(INPUT_GET, "deviceName", FILTER_SANITIZE_STRING); $deviceId = getDeviceId($deviceName); if ($deviceId === false) { $result = "Invalid device {$deviceName}"; } else { $cmd = filter_input(INPUT_GET, "cmd", FILTER_SANITIZE_STRING); switch ($cmd) { case "status": case "schedule": case "config": $result = doGet($deviceId, $cmd); break; case "receiveSch": $sch = filter_input(INPUT_POST, "params", FILTER_SANITIZE_STRING); $result = doSchedule($deviceId, $sch); break; case "sendMsg": $params = filter_input(INPUT_POST, "params", FILTER_SANITIZE_STRING); $result = doPost($deviceId, $cmd, $params); break; default: $result = "Bad command"; } } echo $result;
function deleteItem($itemid) { # parameters $params = array('method' => 'delete_item', 'itemid' => $itemid); # response $response = doGet($params); if (isset($response['error'])) { return $response; } return $response; }
header('Content-Type: text/plain'); //echo "API endpoint\n"; $request = (object) NULL; $headers = array('HTTP_HOST', 'HTTP_USER_AGENT', 'HTTP_ACCEPT', 'HTTP_ACCEPT_LANGUAGE', 'HTTP_ACCEPT_ENCODING', 'HTTP_ACCEPT_CHARSET', 'HTTP_KEEP_ALIVE', 'HTTP_CONNECTION', 'HTTP_CACHE_CONTROL'); $request->method = $_SERVER['REQUEST_METHOD']; $request->url = $_SERVER['REQUEST_URI']; $request->host = $_SERVER['HTTP_HOST']; $request->protocol = $_SERVER['SERVER_PROTOCOL']; //$request->query = $_SERVER['QUERY_STRING']; $request->query = $_GET; //$request->headers = processHeaders($headers); $request->headers = getallheaders(); switch ($request->method) { case 'GET': doGet($request); break; case 'POST': $request->body = file_get_contents('php://input'); doPost($request); break; case 'PUT': $request->body = file_get_contents('php://input'); doPut($request); break; case 'DELETE': doDelete($request); break; default: echo $request->method, " not supported.\n"; break;
$pwd = $_G['gp_pwd']; $submode = $_G['gp_submode']; $submode = isset($submode) ? $submode : 0; if (empty($user)) { showmessage(lang('plugin/bshare', 'message1'), HTTP_REFERER); exit; } if (empty($pwd)) { showmessage(lang('plugin/bshare', 'message2'), HTTP_REFERER); exit; } $openUrl = "http://api.bshare.cn/analytics/reguuid.json?email={$user}&password={$pwd}&domain={$_SERVER['HTTP_HOST']}&source=discuz"; if (!function_exists('curl_init')) { cpmsg(lang('plugin/bshare', 'message3'), "action=plugins&operation=config&do={$pluginid}"); } $result = doGet($openUrl); $json = json_decode($result['response'], true); $uuid = $json['uuid']; $sk = $json['secret']; if (!isset($json) || $json == '') { // Error processing if ($result['code'] == 400) { cpmsg(lang('plugin/bshare', 'message4'), "action=plugins&operation=config&do={$pluginid}"); exit; } else { if ($result['code'] == 401) { if ($submode == 0) { cpmsg(lang('plugin/bshare', 'message5'), "action=plugins&operation=config&do={$pluginid}"); } cpmsg(lang('plugin/bshare', 'message6'), "action=plugins&operation=config&do={$pluginid}"); exit;
} return $mod_id . '-' . $type . '-' . $prop_id; } function buildKey($prop_id, $type) { $config['hz_property_servers'] = array('rest_api' => 'http://10.20.3.82:8080', 'table_name' => 'prop:hz_property', 'column' => 'info'); $key = getPropertyHBaseKey($prop_id, $type); $hBase_config = $config['hz_property_servers']; $api = $hBase_config['rest_api'] . '/' . urlencode($hBase_config['table_name']) . '/' . urlencode($key); if ($hBase_config['column']) { $api .= "/" . urlencode($hBase_config['column']); } return $api; } function doGet($api, $header = array('Accept' => 'application/json')) { $curl = curl_init(); curl_setopt($curl, CURLOPT_URL, $api); curl_setopt($curl, CURLOPT_POST, 0); curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1); curl_setopt($curl, CURLOPT_NOSIGNAL, 1); curl_setopt($curl, CURLOPT_HTTPHEADER, $header); $rs = curl_exec($curl); echo '<pre>'; var_dump($rs); exit; } //15824507 2 $api = buildKey($argv[1], $argv[2]); doGet($api);