function get_results($sql, &$dd) { $link = isset($dd['db']) ? get_db($dd['db']) : get_db(); $res['data'] = array(); $res['total'] = 0; $res['found'] = 0; $res['sql'] = ''; $res['sql'] = $sql; if ($result = mysql_query($sql)) { $res['data'] = $result; $found = mysql_query('SELECT FOUND_ROWS() AS found'); $res['found'] = $found ? mysql_fetch_assoc($found) : 0; $res['found'] = $res['found']['found']; if (isset($dd['total_records'])) { $res['total'] = $dd['total_records']; } else { $total = mysql_query(query_gen_total($dd)); $res['total'] = $total ? mysql_fetch_assoc($total) : 0; $res['total'] = $res['total']['total']; } } return $res; }
function get_dd($db_id, $dv_id = false, $version = false) { global $dv_conf; $dd = false; $db = App::get('db'); if (!$dv_id) { $dv_id = Request::getVar('dv'); } if (!$version) { $version = Request::getInt('v', false); } $name = $dv_id; // Curators $curator = ''; $curator_groups = array(); if (!$version) { $sql = 'SELECT data_definition FROM #__project_databases WHERE `database_name` = ' . $db->quote($name); $db->setQuery($sql); $database = $db->loadAssoc(); $dd = json_decode($database['data_definition'], true); } else { $sql = 'SELECT data_definition FROM #__project_database_versions WHERE database_name=' . $db->quote($name) . ' AND version=' . $db->quote($version); $db->setQuery($sql); $ver = $db->loadAssoc(); $dd = json_decode($ver['data_definition'], true); // Check publication state $sql = 'SELECT state, curator FROM #__publication_versions ' . 'LEFT JOIN #__publication_attachments ON ' . '(#__publication_versions.publication_id=#__publication_attachments.publication_id ' . 'AND #__publication_versions.id=#__publication_attachments.publication_version_id) ' . 'WHERE object_name=' . $db->quote($name) . 'AND object_revision=' . $db->quote($version); $db->setQuery($sql); $pub_version = $db->loadAssoc(); $state = $pub_version['state']; $dd['version'] = $version; $dd['publication_state'] = $state; if ($state != 1) { // curator groups $curation_enabled = Component::params('com_publications')->get('curation'); $curator_group = trim(Component::params('com_publications')->get('curatorgroup')); if ($curation_enabled && $curator_group != '') { $curator_groups[] = $curator_group; } $sql = "SELECT cn FROM #__xgroups g LEFT JOIN #__publication_master_types t ON (g.gidNumber = t.curatorgroup) WHERE t.type = 'Databases'"; $db = App::get('db'); $db->setQuery($sql); $dsl_curators = $db->loadResult(); if ($curation_enabled && $dsl_curators != '') { $curator_groups[] = $dsl_curators; } if ($curation_enabled && $curator != '') { $curator = $pub_version['curator']; $curator = User::getInstance($curator)->get('username'); } } } // Access control if (!isset($dd['publication_state']) || $dd['publication_state'] != 1) { // Project owners $sql = "SELECT username FROM #__project_owners po JOIN #__users u ON (u.id = po.userid) WHERE projectid = {$dd['project']}"; $db = App::get('db'); $db->setQuery($sql); $dd['acl']['allowed_users'] = $db->loadColumn(); // Curators if (isset($dd['publication_state'])) { $dd['acl']['allowed_groups'] = $curator_groups; if (isset($dd['acl']['allowed_users']) && is_array($dd['acl']['allowed_users'])) { $dd['acl']['allowed_users'][] = $curator; } } } elseif (isset($dd['publication_state']) && $dd['publication_state'] == 1) { $dd['acl']['allowed_users'] = false; $dd['acl']['allowed_groups'] = false; $dd['acl']['public'] = true; } $dv_conf['db']['database'] = $dd['database']; $dd['db_id'] = $db_id; $dd['dv_id'] = $dv_id; _dd_post($dd); /* Dynamically set processing mode */ $link = get_db($dv_conf['db']); $cell_count_threshold = (isset($dv_conf['proc_switch_threshold']) && $dv_conf['proc_switch_threshold']) != 0 ? $dv_conf['proc_switch_threshold'] : 20000; mysql_query(query_gen_total($dd), $link); $total = mysql_query('SELECT FOUND_ROWS() AS total', $link); $total = mysql_fetch_assoc($total); $total = isset($total['total']) ? $total['total'] : 0; $dd['total_records'] = $total; $vis_col_count = count(array_filter($dd['cols'], function ($col) { return !isset($col['hide']); })); if ($cell_count_threshold < $total * $vis_col_count) { $dd['serverside'] = true; } return $dd; }
function get_dd($db_id) { global $dv_conf; $dd = false; $dv_id = Request::getVar('dv'); $db_name = $db_id['name']; $dv_conf['dd_json'] = "{$dv_conf['db_base_dir']}/{$db_name}/applications/dataviewer/datadefinitions"; $dd_json_file = isset($dv_conf['dd_json']) && file_exists($dv_conf['dd_json'] . DS . $dv_id . '.json') ? $dv_conf['dd_json'] . DS . $dv_id . '.json' : false; if (isset($db_id['extra']) && $db_id['extra'] == 'table') { $dd['title'] = 'Table : ' . $dv_id; $dd['table'] = $dv_id; if (!User::isGuest() && isset($dv_conf['_managers']) && $dv_conf['_managers'] !== false) { $dd['acl']['allowed_groups'] = $dv_conf['_managers']; } elseif (!User::isGuest() && User::authorise('login', 'administrator')) { // Remove access restrictions for managers $dd['acl']['allowed_users'] = false; $dd['acl']['allowed_groups'] = false; } } else { if ($dd_json_file) { $dd = json_decode(file_get_contents($dd_json_file), true); } elseif ($dd_php_file) { require_once $dd_php_file; $dd_func = 'get_' . $dv_id; if (function_exists($dd_func)) { $dd = $dd_func(); } } else { App::abort(404, 'Invalid or Missing Dataview', 'Invalid or Missing Dataview'); exit; } $dd['conf'] = isset($dd['conf']) ? $dd['conf'] : array(); if (isset($dd['conf']['proc_mode_switch'])) { $dv_conf['proc_mode_switch'] = $dd['conf']['proc_mode_switch']; } if (isset($dd['conf']['proc_switch_threshold'])) { $dv_conf['proc_switch_threshold'] = $dd['conf']['proc_switch_threshold']; } // Database override form dd if (isset($dd['db']) && is_array($dd['db'])) { $dv_conf['db'] = array_merge($dv_conf['db'], $dd['db']); } $dd = _dd_post($dd); } /* Dynamically set processing mode */ if (isset($dv_conf['proc_mode_switch']) && $dv_conf['proc_mode_switch']) { $link = get_db(); mysql_query(query_gen_total($dd), $link); $total = mysql_query('SELECT FOUND_ROWS() AS total', $link); $total = mysql_fetch_assoc($total); $total = isset($total['total']) ? $total['total'] : 0; $dd['total_records'] = $total; $vis_col_count = 0; if (isset($dd['cols'])) { $vis_col_count = count(array_filter($dd['cols'], function ($col) { return !isset($col['hide']); })); } elseif (isset($db_id['extra']) && $db_id['extra'] == 'table') { $sql = "SELECT COUNT(*) AS cols FROM information_schema.columns WHERE table_name = '{$dd['table']}'"; $cols = mysql_fetch_assoc(mysql_query($sql, $link)); $vis_col_count = $cols['cols']; } if ($dv_conf['proc_switch_threshold'] < $total * $vis_col_count) { $dd['serverside'] = true; } } $dd['db_id'] = $db_id; $dd['dv_id'] = $dv_id; return $dd; }
function get_dd($db_id) { global $dv_conf; $dd = false; $db = App::get('db'); $dv_id = Request::getVar('dv'); if ($db_id['extra']) { $sql = "SELECT * FROM #__datastore_tables WHERE datastore_id = " . $db_id['name'] . " AND id = " . $db->quote($dv_id); $db->setQuery($sql); $r = $db->loadAssoc(); $td = json_decode($r['table_definition'], true); $dd['db'] = $dv_conf['db']; $dd['db']['name'] = 'ds_' . $r['datastore_id']; $dd['table'] = $td['name']; $dd['title'] = $r['name']; if (isset($db_id['extra']) && ($db_id['extra'] == 'table' || $db_id['extra'] == 'update')) { if ($db_id['extra'] == 'update') { $update_link = '/datastores/' . $db_id['name'] . '/table/data_record_update/?table=' . $dv_id . '&__ds_rec_id='; $dd['cols'][$td['name'] . '.__ds_rec_id'] = array('label' => 'Select <br />Record', 'raw' => "CONCAT('{$update_link}', __ds_rec_id)", 'type' => 'link', 'relative' => 'true', 'link_label' => 'Edit', 'link_title' => 'Click here to update or remove this record', 'popup' => array('window' => 'Edit_Record', 'features' => 'width=1175px,resizable,scrollbars,status')); } foreach ($td['columns'] as $col) { if ($col['name'] != '__ds_rec_id') { if ($col['type'] == 'file') { $dd['cols'][$td['name'] . '.' . $col['name']]['type'] = 'file'; $dd['cols'][$td['name'] . '.' . $col['name']]['type_extra'] = $col['type_extra']; $dd['cols'][$td['name'] . '.' . $col['name']]['ds-repo-path'] = "/file_repo/{$td['name']}/{$col['name']}"; $dd['cols'][$td['name'] . '.' . $col['name']]['file-verify'] = true; } if ($col['type'] == 'url') { $dd['cols'][$td['name'] . '.' . $col['name']]['type'] = 'url'; $dd['cols'][$td['name'] . '.' . $col['name']]['url-display'] = 'full_link'; } if ($col['type'] == 'txt' && ($col['type_extra'] == 'medium' || $col['type_extra'] == 'large')) { $dd['cols'][$td['name'] . '.' . $col['name']]['width'] = '150'; $dd['cols'][$td['name'] . '.' . $col['name']]['truncate'] = 'truncate'; } $dd['cols'][$td['name'] . '.' . $col['name']]['label'] = $col['label']; } } } } else { $dsid = $db_id['name']; $path = "{$dv_conf['base_path']}/datadefinitions"; $dd_file = "{$dv_id}.json"; if (file_exists("{$path}/{$dd_file}")) { $dd = json_decode(file_get_contents("{$path}/{$dd_file}"), true); } else { return false; } } $dd['db_id'] = $db_id; $dd['dv_id'] = $dv_id; $dd = _dd_post($dd); $dd['conf'] = isset($dd['conf']) ? $dd['conf'] : array(); if (isset($dd['conf']['proc_mode_switch'])) { $dv_conf['proc_mode_switch'] = $dd['conf']['proc_mode_switch']; } if (isset($dd['conf']['proc_switch_threshold'])) { $dv_conf['proc_switch_threshold'] = $dd['conf']['proc_switch_threshold']; } /* Dynamically set processing mode */ if (isset($dv_conf['proc_mode_switch']) && $dv_conf['proc_mode_switch']) { $link = get_db(); mysql_query(query_gen_total($dd), $link); $total = mysql_query('SELECT FOUND_ROWS() AS total', $link); if ($total) { $total = mysql_fetch_assoc($total); $total = isset($total['total']) ? $total['total'] : 0; $dd['total_records'] = $total; $vis_col_count = 0; if (isset($dd['cols'])) { $vis_col_count = count(array_filter($dd['cols'], function ($col) { return !isset($col['hide']); })); } if ($dv_conf['proc_switch_threshold'] < $total * $vis_col_count) { $dd['serverside'] = true; } } } // Record Filters if (isset($dd['record_filters']) && is_array($dd['record_filters'])) { foreach ($dd['record_filters'] as $f) { switch ($f['type']) { case 'E': $dd['where'][] = array('raw' => $f['col'] . " = '" . $f['val'] . "'"); break; case 'NE': $dd['where'][] = array('raw' => $f['col'] . " <> '" . $f['val'] . "'"); break; case 'LT': $dd['where'][] = array('raw' => $f['col'] . " < '" . $f['val'] . "'"); break; case 'GT': $dd['where'][] = array('raw' => $f['col'] . " > '" . $f['val'] . "'"); break; case 'LK': $dd['where'][] = array('raw' => $f['col'] . " LIKE '%" . $f['val'] . "%'"); break; case 'NLK': $dd['where'][] = array('raw' => $f['col'] . " NOT LIKE '%" . $f['val'] . "%'"); break; case 'NULL': $dd['where'][] = array('raw' => $f['col'] . " IS NULL"); break; case 'NNULL': $dd['where'][] = array('raw' => $f['col'] . " IS NOT NULL"); break; } } } /* ACL */ // Dataviews attached to resources & publised $sql = "SELECT r.id, r.published, r.access, r.group_owner, r.group_access, dv.path\n\t\tFROM jos_datastore_resources AS dr\n\t\t\tLEFT JOIN (jos_resources AS r, jos_resource_assoc ra, jos_resources AS dv) ON (r.id = dr.resource_id AND ra.parent_id = r.id AND ra.child_id = dv.id)\n\t\tWHERE r.id IS NOT NULL\n\t\t\tAND r.published = 1\n\t\t\tAND dr.datastore_id = {$db_id['name']}\n\t\t\tAND dv.path = '/dataviewer/view/{$db_id['name']}:ds/{$dv_id}/'"; $db->setQuery($sql); $res = $db->loadAssoc(); if (isset($res['id'])) { $dd['acl'] = array(); // Public if ($res['access'] == 0) { $dd['acl']['public'] = true; } } //$sql = 'SELECT username FROM #__datastores ds LEFT JOIN #__users u ON (u.id = ds.created_by)'; $sql = "SELECT username FROM #__datastore_users ds LEFT JOIN #__users u ON (u.id = ds.value AND ds.type='user') WHERE ds.id = " . $db_id['name']; $db->setQuery($sql); $managers = $db->loadColumn(); if (!isset($dd['acl'])) { $dd['acl']['allowed_users'] = $managers; } elseif (!isset($dd['acl']['registered']) || !isset($dd['acl']['public'])) { $dd['acl']['allowed_users'] = isset($dd['acl']['allowed_users']) ? $dd['acl']['allowed_users'] : array(); $dd['acl']['allowed_users'] = array_merge($dd['acl']['allowed_users'], $managers); } // Giving Hub admins full access to the DataStore dataviews if (JAccess::check(User::get('id'), 'core.admin')) { $dd['acl']['allowed_users'] = isset($dd['acl']['allowed_users']) ? $dd['acl']['allowed_users'] : array(); $dd['acl']['allowed_users'][] = User::get('username'); } return $dd; }