function __construct()
{
// Default constructor.
global $_AUTH;
// SQL code for preparing load entry query.
// Increase DB limits to allow concatenation of large number of gene IDs.
$this->sSQLPreLoadEntry = 'SET group_concat_max_len = 200000';
// SQL code for loading an entry for an edit form.
$this->sSQLLoadEntry = 'SELECT d.*, ' . 'd.tissues AS _tissues, ' . 'GROUP_CONCAT(g2d.geneid ORDER BY g2d.geneid SEPARATOR ";") AS _genes ' . 'FROM ' . TABLE_DISEASES . ' AS d ' . 'LEFT OUTER JOIN ' . TABLE_GEN2DIS . ' AS g2d ON (d.id = g2d.diseaseid) ' . 'WHERE d.id = ? ' . 'GROUP BY d.id';
// SQL code for preparing view entry query.
// Increase DB limits to allow concatenation of large number of gene IDs.
$this->sSQLPreViewEntry = 'SET group_concat_max_len = 200000';
// SQL code for viewing an entry.
$this->aSQLViewEntry['SELECT'] = 'd.*, ' . '(SELECT COUNT(*) FROM ' . TABLE_INDIVIDUALS . ' AS i INNER JOIN ' . TABLE_IND2DIS . ' AS i2d ON (i.id = i2d.individualid) WHERE i2d.diseaseid = d.id' . ($_AUTH['level'] >= LEVEL_COLLABORATOR ? '' : ' AND i.statusid >= ' . STATUS_MARKED) . ') AS individuals, ' . '(SELECT COUNT(*) FROM ' . TABLE_PHENOTYPES . ' AS p WHERE p.diseaseid = d.id' . ($_AUTH['level'] >= LEVEL_COLLABORATOR ? '' : ' AND p.statusid >= ' . STATUS_MARKED) . ') AS phenotypes, ' . 'GROUP_CONCAT(DISTINCT g2d.geneid ORDER BY g2d.geneid SEPARATOR ";") AS _genes, ' . 'uc.name AS created_by_, ' . 'ue.name AS edited_by_';
$this->aSQLViewEntry['FROM'] = TABLE_DISEASES . ' AS d ' . 'LEFT OUTER JOIN ' . TABLE_GEN2DIS . ' AS g2d ON (d.id = g2d.diseaseid) ' . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS uc ON (d.created_by = uc.id) ' . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS ue ON (d.edited_by = ue.id)';
$this->aSQLViewEntry['GROUP_BY'] = 'd.id';
// SQL code for viewing a list of entries.
$this->aSQLViewList['SELECT'] = 'd.*, d.id AS diseaseid, ' . '(SELECT COUNT(DISTINCT i.id) FROM ' . TABLE_IND2DIS . ' AS i2d LEFT OUTER JOIN ' . TABLE_INDIVIDUALS . ' AS i ON (i2d.individualid = i.id' . ($_AUTH['level'] >= LEVEL_COLLABORATOR ? '' : ' AND i.statusid >= ' . STATUS_MARKED) . ') WHERE i2d.diseaseid = d.id) AS individuals, ' . '(SELECT COUNT(*) FROM ' . TABLE_PHENOTYPES . ' AS p WHERE p.diseaseid = d.id' . ($_AUTH['level'] >= LEVEL_COLLABORATOR ? '' : ' AND p.statusid >= ' . STATUS_MARKED) . ') AS phenotypes, ' . 'COUNT(g2d.geneid) AS gene_count, ' . 'GROUP_CONCAT(DISTINCT g2d.geneid ORDER BY g2d.geneid SEPARATOR ";") AS _genes';
$this->aSQLViewList['FROM'] = TABLE_DISEASES . ' AS d ' . 'LEFT OUTER JOIN ' . TABLE_GEN2DIS . ' AS g2d ON (d.id = g2d.diseaseid)';
$this->aSQLViewList['WHERE'] = 'd.id > 0';
$this->aSQLViewList['GROUP_BY'] = 'd.id';
// List of columns and (default?) order for viewing an entry.
$this->aColumnsViewEntry = array('symbol' => 'Official abbreviation', 'name' => 'Name', 'id_omim' => 'OMIM ID', 'individuals' => 'Individuals reported having this disease', 'phenotypes_' => 'Phenotype entries for this disease', 'genes_' => 'Associated with', 'tissues' => 'Associated tissues', 'features' => 'Disease features', 'remarks' => 'Remarks', 'created_by_' => array('Created by', LEVEL_COLLABORATOR), 'created_date_' => array('Date created', LEVEL_COLLABORATOR), 'edited_by_' => array('Last edited by', LEVEL_COLLABORATOR), 'edited_date_' => array('Date last edited', LEVEL_COLLABORATOR));
// List of columns and (default?) order for viewing a list of entries.
$this->aColumnsViewList = array('diseaseid' => array('view' => array('ID', 45, 'style="text-align : right;"'), 'db' => array('d.id', 'ASC', true)), 'symbol' => array('view' => array('Abbreviation', 110), 'db' => array('d.symbol', 'ASC', true)), 'name' => array('view' => array('Name', 300), 'db' => array('d.name', 'ASC', true)), 'id_omim' => array('view' => array('OMIM ID', 75, 'style="text-align : right;"'), 'db' => array('d.id_omim', 'ASC', true)), 'individuals' => array('view' => array('Individuals', 80, 'style="text-align : right;"'), 'db' => array('individuals', 'DESC', 'INT_UNSIGNED')), 'phenotypes' => array('view' => array('Phenotypes', 80, 'style="text-align : right;"'), 'db' => array('phenotypes', 'DESC', 'INT_UNSIGNED')), 'genes_' => array('view' => array('Associated with genes', 200), 'db' => array('_genes', false, 'TEXT')), 'tissues' => array('view' => array('Associated tissues', 160), 'db' => array('d.tissues', false, true)), 'features' => array('view' => array('Disease features', 200), 'db' => array('d.features', false, true)));
$this->sSortDefault = 'symbol';
// Because the disease information is publicly available, remove some columns for the public.
$this->unsetColsByAuthLevel();
parent::__construct();
}
function __construct()
{
// Default constructor.
// SQL code for viewing a list of entries.
$this->aSQLViewList['SELECT'] = 'l.*, CONCAT_WS(",", l.name, l.date, l.mtime) AS row_id, "" AS row_link, CONCAT(l.date, " ", l.mtime) AS timestamp, u.name AS user';
$this->aSQLViewList['FROM'] = TABLE_LOGS . ' AS l LEFT JOIN ' . TABLE_USERS . ' AS u ON (l.userid = u.id)';
$this->aSQLViewList['ORDER_BY'] = 'timestamp DESC';
// List of columns and (default?) order for viewing a list of entries.
$this->aColumnsViewList = array('name' => array('view' => array('Log', 50), 'db' => array('l.name', 'ASC', true)), 'date' => array('view' => array('Date', 130), 'db' => array('l.date', 'DESC', 'DATETIME')), 'userid' => array('view' => false, 'db' => array('l.userid', 'ASC', true)), 'user_' => array('view' => array('User', 160), 'db' => array('u.name', 'ASC', true)), 'event' => array('view' => array('Event', 100), 'db' => array('l.event', 'ASC', true)), 'del' => array('view' => array(' ', 14, 'style="text-align : center;"')), 'entry' => array('view' => array('Entry', 700), 'db' => array('l.log', false, true)));
$this->sSortDefault = 'date';
parent::__construct();
}
function __construct()
{
// Default constructor.
// SQL code for viewing an entry.
$this->aSQLViewEntry['SELECT'] = 'a.*, uc.name AS created_by_, ue.name AS edited_by_';
$this->aSQLViewEntry['FROM'] = TABLE_ANNOUNCEMENTS . ' AS a LEFT JOIN ' . TABLE_USERS . ' AS uc ON (a.created_by = uc.id) LEFT JOIN ' . TABLE_USERS . ' AS ue ON (a.edited_by = ue.id)';
$this->aSQLViewEntry['GROUP_BY'] = 'a.id';
// SQL code for viewing a list of entries.
$this->aSQLViewList['SELECT'] = 'a.*, LEFT(a.start_date, 10) AS start_date_, LEFT(a.end_date, 10) AS end_date_';
$this->aSQLViewList['FROM'] = TABLE_ANNOUNCEMENTS . ' AS a';
$this->aSQLViewList['ORDER_BY'] = 'a.start_date ASC, a.end_date ASC, id ASC';
// List of columns and (default?) order for viewing an entry.
$this->aColumnsViewEntry = array('id' => 'Announcement ID', 'type' => 'Type', 'announcement' => 'Announcement text', 'start_date' => 'Start date', 'end_date' => 'End date', 'lovd_read_only_' => 'Make LOVD read-only?', 'created_by_' => 'Created by', 'created_date' => 'Date created', 'edited_by_' => 'Last edited by', 'edited_date' => 'Date last edited');
// List of columns and (default?) order for viewing a list of entries.
$this->aColumnsViewList = array('id' => array('view' => array('ID', 45), 'db' => array('a.id', 'ASC', true)), 'type_' => array('view' => array('Type', 50, 'style="text-align : center;"'), 'db' => array('a.type', 'ASC', true)), 'announcement' => array('view' => array('Announcement text', 600), 'db' => array('a.announcement', 'ASC', true)), 'start_date_' => array('view' => array('Start date', 80), 'db' => array('a.start_date', 'ASC', true)), 'end_date_' => array('view' => array('End date', 80), 'db' => array('a.end_date', 'ASC', true)), 'lovd_read_only_' => array('view' => array('Read only?', 60, 'style="text-align : center;"'), 'db' => array('lovd_read_only', 'DESC', true)));
$this->sSortDefault = 'start_date_';
parent::__construct();
}
function __construct($sObjectID = '', $nID = '')
{
// Default constructor.
// $nID is not really correct here, it's always $sID.
global $_DB;
if (!$sObjectID && !$nID) {
lovd_displayError('ObjectError', 'SharedColumn::__construct() not called with valid Parent or Column ID.');
}
$this->sObjectID = $sObjectID;
// ID of parent gene or disease.
$this->nID = $nID;
// ID of the column itself.
if ($nID) {
$sCategory = substr($nID, 0, strpos($nID . '/', '/'));
// Isolate the category from the ID.
} else {
$sCategory = ctype_digit($sObjectID) ? 'Phenotype' : 'VariantOnTranscript';
}
$this->aTableInfo = lovd_getTableInfoByCategory($sCategory);
// Gather info on the type of column.
// SQL code for loading an entry for an edit form.
$this->sSQLLoadEntry = 'SELECT sc.*, c.form_type ' . 'FROM ' . TABLE_SHARED_COLS . ' AS sc ' . 'INNER JOIN ' . TABLE_COLS . ' AS c ON (sc.colid = c.id) ' . 'WHERE sc.colid = ? AND sc.' . $this->aTableInfo['unit'] . 'id = "' . $sObjectID . '"';
// Variable has been checked elsewhere, before this query is run.
// SQL code for viewing an entry.
$this->aSQLViewEntry['SELECT'] = 'sc.*, ' . 'c.hgvs, ' . 'c.form_type, ' . 'uc.name AS created_by_, ' . 'ue.name AS edited_by_';
$this->aSQLViewEntry['FROM'] = TABLE_SHARED_COLS . ' AS sc ' . 'INNER JOIN ' . TABLE_COLS . ' AS c ON (sc.colid = c.id) ' . 'LEFT JOIN ' . TABLE_USERS . ' AS uc ON (sc.created_by = uc.id) ' . 'LEFT JOIN ' . TABLE_USERS . ' AS ue ON (sc.edited_by = ue.id)';
$this->aSQLViewEntry['WHERE'] = 'sc.' . $this->aTableInfo['unit'] . 'id = "' . $sObjectID . '"';
// Variable has been checked elsewhere, before this query is run.
// SQL code for viewing a list of entries.
$this->aSQLViewList['SELECT'] = 'sc.*, ' . 'SUBSTRING(sc.colid, LOCATE("/", sc.colid)+1) AS colid, ' . 'c.id, ' . 'c.head_column, ' . 'c.form_type, ' . 'u.name AS created_by_';
$this->aSQLViewList['FROM'] = TABLE_SHARED_COLS . ' AS sc ' . 'INNER JOIN ' . TABLE_COLS . ' AS c ON (sc.colid = c.id) ' . 'LEFT JOIN ' . TABLE_USERS . ' AS u ON (sc.created_by = u.id)';
// Now restrict viewList to only these related to this gene/disease.
$this->aSQLViewList['WHERE'] = 'sc.' . $this->aTableInfo['unit'] . 'id = ' . $_DB->quote($sObjectID);
$this->aSQLViewList['ORDER_BY'] = 'col_order, colid';
// List of columns and (default?) order for viewing an entry.
$this->aColumnsViewEntry = array('colid' => 'Column ID', 'width' => 'Displayed width in pixels', 'mandatory_' => 'Mandatory', 'description_form' => 'Description on form', 'description_legend_short' => 'Description on short legend', 'description_legend_full' => 'Description on full legend', 'select_options' => 'Select options', 'public_view_' => 'Show to public', 'public_add_' => 'Show on submission form', 'created_by_' => 'Created by', 'created_date' => 'Date created', 'edited_by_' => 'Last edited by', 'edited_date' => 'Date last edited');
// List of columns and (default?) order for viewing a list of entries.
$this->aColumnsViewList = array('id' => array('view' => false, 'db' => array('sc.colid', 'ASC', true)), 'colid_' => array('view' => array('ID', 175), 'db' => array('SUBSTRING(sc.colid, LOCATE("/", sc.colid)+1)', 'ASC', true)), 'head_column' => array('view' => array('Heading', 150), 'db' => array('c.head_column', 'ASC', true)), 'width' => array('view' => array('Width in px', 100), 'db' => array('sc.width', 'ASC', true)), 'mandatory_' => array('view' => array('Mandatory', 60, 'style="text-align : center;"'), 'db' => array('sc.mandatory', 'DESC', true)), 'public_view_' => array('view' => array('Public', 60, 'style="text-align : center;"'), 'db' => array('sc.public_view', 'DESC', true)), 'col_order' => array('view' => array('Order ', 60, 'style="text-align : right;"'), 'db' => array('sc.col_order', 'ASC')), 'form_type_' => array('view' => array('Form type', 200)), 'created_by_' => array('view' => array('Created by', 160), 'db' => array('u.name', 'DESC', true)));
$this->sSortDefault = 'col_order';
parent::__construct();
}
function __construct()
{
// Default constructor.
global $_SETT;
// SQL code for loading an entry for an edit form.
$this->sSQLLoadEntry = 'SELECT *, (login_attempts >= 3) AS locked ' . 'FROM ' . TABLE_USERS . ' ' . 'WHERE id = ? AND id > 0';
// SQL code to insert the level names into the database output, so it can be searched on.
$sLevelQuery = '';
$aLevels = $_SETT['user_levels'];
unset($aLevels[LEVEL_OWNER]);
foreach ($_SETT['user_levels'] as $nLevel => $sLevel) {
$sLevelQuery .= ' WHEN "' . $nLevel . '" THEN "' . $nLevel . $sLevel . '"';
}
// SQL code for preparing view entry query.
// Increase DB limits to allow concatenation of large number of gene IDs.
$this->sSQLPreViewEntry = 'SET group_concat_max_len = 200000';
// SQL code for viewing an entry.
$this->aSQLViewEntry['SELECT'] = 'u.*, ' . '(u.login_attempts >= 3) AS locked, ' . 'GROUP_CONCAT(DISTINCT CASE u2g.allow_edit WHEN "1" THEN u2g.geneid END ORDER BY u2g.geneid SEPARATOR ";") AS _curates, ' . 'GROUP_CONCAT(DISTINCT CASE u2g.allow_edit WHEN "0" THEN u2g.geneid END ORDER BY u2g.geneid SEPARATOR ";") AS _collaborates, ' . 'GROUP_CONCAT(DISTINCT col.userid_to, ";", ucol.name SEPARATOR ";;") AS __colleagues,' . 'c.name AS country_, ' . 'uc.name AS created_by_, ' . 'ue.name AS edited_by_, ' . 'GREATEST(u.level, IFNULL(CASE MAX(u2g.allow_edit) WHEN 1 THEN ' . LEVEL_CURATOR . ' WHEN 0 THEN ' . LEVEL_COLLABORATOR . ' END, ' . LEVEL_SUBMITTER . ')) AS level';
$this->aSQLViewEntry['FROM'] = TABLE_USERS . ' AS u ' . 'LEFT OUTER JOIN ' . TABLE_CURATES . ' AS u2g ON (u.id = u2g.userid) ' . 'LEFT OUTER JOIN ' . TABLE_COUNTRIES . ' AS c ON (u.countryid = c.id) ' . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS uc ON (u.created_by = uc.id) ' . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS ue ON (u.edited_by = ue.id) ' . 'LEFT OUTER JOIN ' . TABLE_COLLEAGUES . ' AS col ON (u.id = col.userid_from) ' . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS ucol ON (col.userid_to = ucol.id)';
$this->aSQLViewEntry['GROUP_BY'] = 'u.id';
// SQL code for viewing a list of users.
$this->aSQLViewList['SELECT'] = 'u.*, (u.login_attempts >= 3) AS locked, ' . 'COUNT(CASE u2g.allow_edit WHEN 1 THEN u2g.geneid END) AS curates, ' . 'c.name AS country_, ' . 'GREATEST(u.level, IFNULL(CASE MAX(u2g.allow_edit) WHEN 1 THEN ' . LEVEL_CURATOR . ' WHEN 0 THEN ' . LEVEL_COLLABORATOR . ' END, ' . LEVEL_SUBMITTER . ')) AS level, ' . 'CASE GREATEST(u.level, IFNULL(CASE MAX(u2g.allow_edit) WHEN 1 THEN ' . LEVEL_CURATOR . ' WHEN 0 THEN ' . LEVEL_COLLABORATOR . ' END, ' . LEVEL_SUBMITTER . '))' . $sLevelQuery . ' END AS level_';
$this->aSQLViewList['FROM'] = TABLE_USERS . ' AS u ' . 'LEFT OUTER JOIN ' . TABLE_CURATES . ' AS u2g ON (u.id = u2g.userid) ' . 'LEFT OUTER JOIN ' . TABLE_COUNTRIES . ' AS c ON (u.countryid = c.id)';
$this->aSQLViewList['WHERE'] = 'u.id > 0';
$this->aSQLViewList['GROUP_BY'] = 'u.id';
$this->aSQLViewList['ORDER_BY'] = 'level DESC, u.name ASC';
// List of columns and (default?) order for viewing an entry.
$this->aColumnsViewEntry = array('id' => 'User ID', 'orcid_id_' => 'ORCID ID', 'name' => 'Name', 'institute' => 'Institute', 'department' => 'Department', 'telephone' => array('Telephone', LEVEL_CURATOR), 'address' => array('Address', LEVEL_CURATOR), 'city' => 'City', 'country_' => 'Country', 'email' => array('Email address', LEVEL_CURATOR), 'reference' => 'Reference', 'username' => array('Username', LEVEL_MANAGER), 'password_force_change_' => array('Force change password', LEVEL_MANAGER), 'phpsessid' => array('Session ID', LEVEL_MANAGER), 'saved_work_' => array('Saved work', LEVEL_MANAGER), 'curates_' => 'Curator for', 'collaborates_' => array('Collaborator for', LEVEL_CURATOR), 'ownes_' => 'Data owner for', 'colleagues_' => '', 'level_' => array('User level', LEVEL_CURATOR), 'allowed_ip_' => array('Allowed IP address list', LEVEL_MANAGER), 'status_' => array('Status', LEVEL_MANAGER), 'locked_' => array('Locked', LEVEL_MANAGER), 'last_login' => array('Last login', LEVEL_MANAGER), 'created_by_' => array('Created by', LEVEL_CURATOR), 'created_date' => array('Date created', LEVEL_CURATOR), 'edited_by_' => array('Last edited by', LEVEL_MANAGER), 'edited_date_' => array('Date last edited', LEVEL_MANAGER));
// List of columns and (default?) order for viewing a list of entries.
$this->aColumnsViewList = array('userid' => array('view' => false, 'db' => array('u.id', 'ASC', true)), 'id' => array('view' => array('ID', 45), 'db' => array('u.id', 'ASC', true)), 'orcid_id_' => array('view' => array('ORCiD', 60, 'style="text-align : center;"'), 'db' => array('(u.orcid_id is not null)', 'DESC', true), 'legend' => array('The user\'s ORCID ID, a unique digital identifier.', 'The user\'s ORCID ID, a persistent digital identifier that distinguishes you from every other researcher and supports automated linkages between you and your professional activities ensuring that your work is recognized.')), 'name' => array('view' => array('Name', 160), 'db' => array('u.name', 'ASC', true)), 'username' => array('view' => array('Username', 80), 'db' => array('u.username', 'ASC', true)), 'institute' => array('view' => array('Institute', 225), 'db' => array('u.institute', 'ASC', true)), 'country_' => array('view' => array('Country', 200), 'db' => array('c.name', 'ASC', true)), 'curates' => array('view' => array('Curated DBs', 100, 'style="text-align : right;"'), 'db' => array('curates', 'DESC', 'INT_UNSIGNED'), 'legend' => array('Shows how many gene databases have this user assigned as curator.')), 'status_' => array('view' => array('Status', 50, 'style="text-align : center;"'), 'legend' => array('Shows whether this user is online (computer screen icon), locked (forbidden entry icon), or offline (no icon).')), 'last_login_' => array('view' => array('Last login', 80), 'db' => array('u.last_login', 'DESC', true)), 'created_date_' => array('view' => array('Started', 80), 'db' => array('u.created_date', 'ASC', true)), 'level_' => array('view' => array('Level', 150), 'db' => array('level_', 'DESC', 'TEXT')));
// In safe mode, the status check doesn't work anyway, because we're not allowed to access the session file.
if (ini_get('safe_mode')) {
unset($this->aColumnsViewEntry['status_']);
}
$this->sSortDefault = 'level_';
// Because the user information is publicly available, remove some columns for the public.
// FIXME; Dit moet eigenlijk per user anders; curatoren mogen deze info wel van submitters zien.
// Dus eigenlijk if ($_AUTH['level'] <= $zData['level']) maar we hebben hier geen $zData...
$this->unsetColsByAuthLevel();
parent::__construct();
}
function __construct()
{
// Default constructor.
// SQL code for loading an entry for an edit form.
$this->sSQLLoadEntry = 'SELECT l.*, GROUP_CONCAT(c2l.colid ORDER BY c2l.colid SEPARATOR ";") AS active_columns_ FROM ' . TABLE_LINKS . ' AS l LEFT JOIN ' . TABLE_COLS2LINKS . ' AS c2l ON (l.id = c2l.linkid) WHERE l.id = ? GROUP BY l.id';
// SQL code for viewing an entry.
$this->aSQLViewEntry['SELECT'] = 'l.*, COUNT(c2l.colid) AS active_columns, GROUP_CONCAT(c2l.colid ORDER BY c2l.colid SEPARATOR ", ") AS active_columns_, uc.name AS created_by_, ue.name AS edited_by_';
$this->aSQLViewEntry['FROM'] = TABLE_LINKS . ' AS l LEFT JOIN ' . TABLE_COLS2LINKS . ' AS c2l ON (l.id = c2l.linkid) LEFT JOIN ' . TABLE_USERS . ' AS uc ON (l.created_by = uc.id) LEFT JOIN ' . TABLE_USERS . ' AS ue ON (l.edited_by = ue.id)';
$this->aSQLViewEntry['GROUP_BY'] = 'l.id';
// SQL code for viewing a list of entries.
$this->aSQLViewList['SELECT'] = 'l.*, COUNT(c2l.colid) AS active_columns';
$this->aSQLViewList['FROM'] = TABLE_LINKS . ' AS l LEFT OUTER JOIN ' . TABLE_COLS2LINKS . ' AS c2l ON (l.id = c2l.linkid)';
$this->aSQLViewList['GROUP_BY'] = 'l.id';
$this->aSQLViewList['ORDER_BY'] = 'l.name ASC';
// List of columns and (default?) order for viewing an entry.
$this->aColumnsViewEntry = array('id' => 'Link ID', 'name' => 'Link name', 'pattern_text' => 'Pattern text', 'replace_text' => 'Replace text', 'description' => 'Description', 'active_columns' => '# active columns', 'active_columns_' => 'Active columns', 'created_by_' => 'Created by', 'created_date' => 'Date created', 'edited_by_' => 'Last edited by', 'edited_date' => 'Date last edited');
// List of columns and (default?) order for viewing a list of entries.
$this->aColumnsViewList = array('id' => array('view' => array('ID', 45), 'db' => array('l.id', 'ASC', true)), 'name' => array('view' => array('Name', 100), 'db' => array('l.name', 'ASC', true)), 'pattern_text' => array('view' => array('Pattern', 130), 'db' => array('l.pattern_text', 'ASC', true)), 'replace_text' => array('view' => array('Replacement', 630), 'db' => array('l.replace_text', 'ASC', true)), 'active_columns' => array('view' => array('# cols', 60, 'style="text-align : right;"'), 'db' => array('active_columns', 'DESC', 'INT_UNSIGNED')));
$this->sSortDefault = 'name';
parent::__construct();
}
function __construct()
{
// Default constructor.
// SQL code for loading an entry for an edit form.
$this->sSQLLoadEntry = 'SELECT c.*, ' . 'SUBSTRING_INDEX(c.id, "/", 1) AS category, ' . 'SUBSTRING(c.id, LOCATE("/", c.id)+1) AS colid, ' . '(a.created_by > 0) AS active, ' . 'GROUP_CONCAT(c2l.linkid SEPARATOR ";") AS _active_links ' . 'FROM ' . TABLE_COLS . ' AS c ' . 'LEFT JOIN ' . TABLE_ACTIVE_COLS . ' AS a ON (c.id = a.colid) ' . 'LEFT OUTER JOIN ' . TABLE_COLS2LINKS . ' AS c2l ON (c.id = c2l.colid) ' . 'WHERE c.id = ? ' . 'GROUP BY c.id';
// SQL code for preparing view entry query.
// Increase DB limits to allow concatenation of large number of gene/disease IDs.
$this->sSQLPreViewEntry = 'SET group_concat_max_len = 200000';
// SQL code for viewing an entry.
$this->aSQLViewEntry['SELECT'] = 'c.*, ' . 'SUBSTRING_INDEX(c.id, "/", 1) AS category, ' . 'SUBSTRING(c.id, LOCATE("/", c.id)+1) AS colid, ' . '(a.colid IS NOT NULL) AS active, ' . 'uc.name AS created_by_, ' . 'ue.name AS edited_by_, ' . 'GROUP_CONCAT(sc.geneid ORDER BY sc.geneid SEPARATOR ";") AS _genes, ' . 'GROUP_CONCAT(DISTINCT d.id, ";", IF(CASE d.symbol WHEN "-" THEN "" ELSE d.symbol END = "", d.name, d.symbol) ORDER BY (d.symbol != "" AND d.symbol != "-") DESC, d.symbol, d.name SEPARATOR ";;") AS __diseases';
$this->aSQLViewEntry['FROM'] = TABLE_COLS . ' AS c ' . 'LEFT OUTER JOIN ' . TABLE_ACTIVE_COLS . ' AS a ON (c.id = a.colid) ' . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS uc ON (c.created_by = uc.id) ' . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS ue ON (c.edited_by = ue.id) ' . 'LEFT OUTER JOIN ' . TABLE_SHARED_COLS . ' AS sc ON (c.id = sc.colid) ' . 'LEFT OUTER JOIN ' . TABLE_DISEASES . ' AS d ON (sc.diseaseid = d.id)';
$this->aSQLViewEntry['GROUP_BY'] = 'c.id';
// SQL code for viewing a list of entries.
$this->aSQLViewList['SELECT'] = 'c.*, ' . 'SUBSTRING_INDEX(c.id, "/", 1) AS category, ' . 'SUBSTRING(c.id, LOCATE("/", c.id)+1) AS colid, ' . 'IF(a.colid IS NULL, 0, 1) AS active, ' . 'u.name AS created_by_';
$this->aSQLViewList['FROM'] = TABLE_COLS . ' AS c ' . 'LEFT JOIN ' . TABLE_ACTIVE_COLS . ' AS a ON (c.id = a.colid) ' . 'LEFT JOIN ' . TABLE_USERS . ' AS u ON (c.created_by = u.id)';
$this->aSQLViewList['ORDER_BY'] = 'category, col_order, colid';
// List of columns and (default?) order for viewing an entry.
$this->aColumnsViewEntry = array('category' => 'Data category', 'colid' => 'Column ID', 'head_column' => 'Column heading', 'active_' => 'Active in LOVD?', 'hgvs_' => 'HGVS required column', 'standard_' => 'Standard/Enabled by default', 'mandatory_' => 'Mandatory', 'description_form' => 'Description on form', 'description_legend_short' => 'Description on short legend', 'description_legend_full' => 'Description on full legend', 'mysql_type' => 'Database type', 'form_type_' => 'Form type', 'select_options' => 'Select options', 'preg_pattern' => 'Regular expression pattern', 'public_view_' => 'Show to public', 'public_add_' => 'Show on submission form', 'allow_count_all_' => 'Include in search form', 'parent_objects' => 'Column activated for', 'created_by_' => 'Created by', 'created_date' => 'Date created', 'edited_by_' => 'Last edited by', 'edited_date' => 'Date last edited');
// List of columns and (default?) order for viewing a list of entries.
$this->aColumnsViewList = array('category' => array('view' => array('Category', 120), 'db' => array('SUBSTRING_INDEX(c.id, "/", 1)', 'ASC', true)), 'colid_' => array('view' => array('ID', 175), 'db' => array('SUBSTRING(c.id, LOCATE("/", c.id)+1)', 'ASC', true)), 'head_column' => array('view' => array('Heading', 150), 'db' => array('c.head_column', 'ASC', true), 'legend' => array('The header of this column in data listings.')), 'active_' => array('view' => array('Active', 60, 'style="text-align : center;"'), 'db' => array('IFNULL((a.colid IS NOT NULL), 0)', 'DESC', 'INT'), 'legend' => array('Whether this column has been activated in LOVD. For shared columns (Phenotype or VariantOnTranscript columns) this does not mean this column is activated in all diseases or genes, respectively.')), 'hgvs_' => array('view' => array('HGVS', 50, 'style="text-align : center;"'), 'db' => array('c.hgvs', 'DESC', true), 'legend' => array('Whether this column is HGVS standard or not. HGVS standard columns can not be removed or disabled.')), 'standard_' => array('view' => array('Standard', 80, 'style="text-align : center;"'), 'db' => array('c.standard', 'DESC', true), 'legend' => array('Whether this column is activated by default. For shared columns (Phenotype or VariantOnTranscript columns) this means newly created diseases or genes, include this column by default.')), 'public_view_' => array('view' => array('Public', 60, 'style="text-align : center;"'), 'db' => array('c.public_view', 'DESC', true), 'legend' => array('Whether the public can see this column\'s contents or not.')), 'col_order' => array('view' => array('Order ', 60, 'style="text-align : right;"'), 'db' => array('SUBSTRING_INDEX(c.id, "/", 1), col_order', 'ASC')), 'form_type_' => array('view' => array('Form type', 200)), 'created_by_' => array('view' => array('Created by', 160), 'db' => array('u.name', 'DESC', true)));
$this->sSortDefault = 'category';
parent::__construct();
}
function __construct()
{
global $_AUTH;
// Default constructor.
// SQL code for loading an entry for an edit form.
$this->sSQLLoadEntry = 'SELECT t.* ' . 'FROM ' . TABLE_TRANSCRIPTS . ' AS t ' . 'WHERE id = ?';
// SQL code for viewing an entry.
$this->aSQLViewEntry['SELECT'] = 't.*, ' . 'g.name AS gene_name, ' . 'g.chromosome, ' . 'uc.name AS created_by_, ' . 'ue.name AS edited_by_, ' . 'COUNT(DISTINCT vot.id) AS variants';
$this->aSQLViewEntry['FROM'] = TABLE_TRANSCRIPTS . ' AS t ' . 'LEFT OUTER JOIN ' . TABLE_GENES . ' AS g ON (t.geneid = g.id) ' . 'LEFT OUTER JOIN ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' AS vot ON (t.id = vot.transcriptid) ' . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS uc ON (t.created_by = uc.id) ' . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS ue ON (t.edited_by = ue.id)';
$this->aSQLViewEntry['GROUP_BY'] = 't.id';
// SQL code for viewing the list of transcripts
$this->aSQLViewList['SELECT'] = 't.*, ' . 'g.chromosome, ' . 'COUNT(DISTINCT ' . ($_AUTH['level'] >= LEVEL_COLLABORATOR ? 'vot.id' : 'vog.id') . ') AS variants';
$this->aSQLViewList['FROM'] = TABLE_TRANSCRIPTS . ' AS t ' . 'LEFT OUTER JOIN ' . TABLE_GENES . ' AS g ON (t.geneid = g.id) ' . 'LEFT OUTER JOIN ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' AS vot ON (t.id = vot.transcriptid)' . ($_AUTH['level'] >= LEVEL_COLLABORATOR ? '' : 'LEFT OUTER JOIN ' . TABLE_VARIANTS . ' AS vog ON ' . '(vot.id = vog.id AND (vog.statusid >= ' . STATUS_MARKED . (!$_AUTH ? '' : ' OR vog.created_by = "' . $_AUTH['id'] . '" OR ' . 'vog.owned_by = "' . $_AUTH['id'] . '"') . ')) ');
$this->aSQLViewList['GROUP_BY'] = 't.id';
// List of columns and (default?) order for viewing an entry.
$this->aColumnsViewEntry = array('name' => 'Transcript name', 'gene_name_' => 'Gene name', 'chromosome' => 'Chromosome', 'id_ncbi_' => 'Transcript - NCBI ID', 'id_ensembl' => 'Transcript - Ensembl ID', 'id_protein_ncbi' => 'Protein - NCBI ID', 'id_protein_ensembl' => 'Protein - Ensembl ID', 'id_protein_uniprot' => 'Protein - Uniprot ID', 'exon_table' => 'Exon/intron information', 'remarks' => 'Remarks', 'created_by_' => array('Created by', LEVEL_COLLABORATOR), 'created_date_' => array('Date created', LEVEL_COLLABORATOR), 'edited_by_' => array('Last edited by', LEVEL_COLLABORATOR), 'edited_date_' => array('Date last edited', LEVEL_COLLABORATOR));
// List of columns and (default?) order for viewing a list of entries.
$this->aColumnsViewList = array('id_' => array('view' => array('ID', 70, 'style="text-align : right;"'), 'db' => array('t.id', 'ASC', true)), 'chromosome' => array('view' => array('Chr', 40), 'db' => array('g.chromosome', 'ASC', true)), 'geneid' => array('view' => array('Gene ID', 100), 'db' => array('t.geneid', 'ASC', true)), 'name' => array('view' => array('Name', 300), 'db' => array('t.name', 'ASC', true)), 'id_ncbi' => array('view' => array('NCBI ID', 120), 'db' => array('t.id_ncbi', 'ASC', true)), 'id_protein_ncbi' => array('view' => array('NCBI Protein ID', 120), 'db' => array('t.id_protein_ncbi', 'ASC', true)), 'variants' => array('view' => array('Variants', 70, 'style="text-align : right;"'), 'db' => array('variants', 'DESC', 'INT_UNSIGNED')));
$this->sSortDefault = 'geneid';
// Because the disease information is publicly available, remove some columns for the public.
$this->unsetColsByAuthLevel();
parent::__construct();
}
function __construct()
{
// Default constructor.
global $_AUTH;
// SQL code for loading an entry for an edit form.
$this->sSQLLoadEntry = 'SELECT g.*, ' . 'GROUP_CONCAT(DISTINCT g2d.diseaseid ORDER BY g2d.diseaseid SEPARATOR ";") AS _active_diseases ' . 'FROM ' . TABLE_GENES . ' AS g ' . 'LEFT OUTER JOIN ' . TABLE_GEN2DIS . ' AS g2d ON (g.id = g2d.geneid) ' . 'WHERE g.id = ? ' . 'GROUP BY g.id';
// SQL code for viewing an entry.
$this->aSQLViewEntry['SELECT'] = 'g.*, g.id_entrez AS id_pubmed_gene, ' . 'GROUP_CONCAT(DISTINCT d.id, ";", IFNULL(d.id_omim, 0), ";", IF(CASE d.symbol WHEN "-" THEN "" ELSE d.symbol END = "", d.name, d.symbol), ";", d.name ORDER BY (d.symbol != "" AND d.symbol != "-") DESC, d.symbol, d.name SEPARATOR ";;") AS __diseases, ' . 'GROUP_CONCAT(DISTINCT t.id, ";", t.id_ncbi ORDER BY t.id_ncbi SEPARATOR ";;") AS __transcripts, ' . '(t.position_g_mrna_start < t.position_g_mrna_end) AS sense, ' . 'LEAST(MIN(t.position_g_mrna_start), MIN(t.position_g_mrna_end)) AS position_g_mrna_start, ' . 'GREATEST(MAX(t.position_g_mrna_start), MAX(t.position_g_mrna_end)) AS position_g_mrna_end, ' . 'GROUP_CONCAT(DISTINCT u2g.userid, ";", ua.name, ";", u2g.allow_edit, ";", show_order ORDER BY (u2g.show_order > 0) DESC, u2g.show_order SEPARATOR ";;") AS __curators, ' . 'uc.name AS created_by_, ' . 'ue.name AS edited_by_, ' . 'uu.name AS updated_by_, ' . '(SELECT COUNT(DISTINCT vog.id) FROM ' . TABLE_VARIANTS . ' AS vog INNER JOIN ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' AS vot USING (id) INNER JOIN ' . TABLE_TRANSCRIPTS . ' AS t ON (vot.transcriptid = t.id) WHERE t.geneid = g.id AND vog.statusid >= ' . STATUS_MARKED . ') AS variants, ' . '(SELECT COUNT(DISTINCT vog.`VariantOnGenome/DBID`) FROM ' . TABLE_VARIANTS . ' AS vog INNER JOIN ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' AS vot USING (id) INNER JOIN ' . TABLE_TRANSCRIPTS . ' AS t ON (vot.transcriptid = t.id) WHERE t.geneid = g.id AND vog.statusid >= ' . STATUS_MARKED . ') AS uniq_variants, ' . '"" AS count_individuals, ' . '(SELECT COUNT(*) FROM ' . TABLE_VARIANTS . ' AS hidden_vog INNER JOIN ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' AS hidden_vot ON (hidden_vog.id = hidden_vot.id) INNER JOIN ' . TABLE_TRANSCRIPTS . ' AS t ON (hidden_vot.transcriptid = t.id) WHERE t.geneid = g.id AND hidden_vog.statusid < ' . STATUS_MARKED . ') AS hidden_variants';
$this->aSQLViewEntry['FROM'] = TABLE_GENES . ' AS g ' . 'LEFT OUTER JOIN ' . TABLE_GEN2DIS . ' AS g2d ON (g.id = g2d.geneid) ' . 'LEFT OUTER JOIN ' . TABLE_DISEASES . ' AS d ON (g2d.diseaseid = d.id) ' . 'LEFT OUTER JOIN ' . TABLE_CURATES . ' AS u2g ON (g.id = u2g.geneid) ' . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS ua ON (u2g.userid = ua.id' . ($_AUTH['level'] >= LEVEL_COLLABORATOR ? '' : ' AND u2g.show_order > 0') . ') ' . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS uc ON (g.created_by = uc.id) ' . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS ue ON (g.edited_by = ue.id) ' . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS uu ON (g.updated_by = uu.id) ' . 'LEFT OUTER JOIN ' . TABLE_TRANSCRIPTS . ' AS t ON (g.id = t.geneid) ';
$this->aSQLViewEntry['GROUP_BY'] = 'g.id';
// SQL code for viewing the list of genes
$this->aSQLViewList['SELECT'] = 'g.*, ' . 'g.id AS geneid, ' . 'GROUP_CONCAT(DISTINCT IF(CASE d.symbol WHEN "-" THEN "" ELSE d.symbol END = "", d.name, d.symbol) ORDER BY (d.symbol != "" AND d.symbol != "-") DESC, d.symbol, d.name SEPARATOR ", ") AS diseases_, ' . 'COUNT(DISTINCT t.id) AS transcripts, ' . 'COUNT(DISTINCT vog.id) AS variants, ' . 'COUNT(DISTINCT vog.`VariantOnGenome/DBID`) AS uniq_variants';
$this->aSQLViewList['FROM'] = TABLE_GENES . ' AS g ' . 'LEFT OUTER JOIN ' . TABLE_GEN2DIS . ' AS g2d ON (g.id = g2d.geneid) ' . 'LEFT OUTER JOIN ' . TABLE_TRANSCRIPTS . ' AS t ON (g.id = t.geneid) ' . 'LEFT OUTER JOIN ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' AS vot ON (t.id = vot.transcriptid) ' . 'LEFT OUTER JOIN ' . TABLE_VARIANTS . ' AS vog ON (vot.id = vog.id' . ($_AUTH['level'] >= LEVEL_COLLABORATOR ? '' : ' AND vog.statusid >= ' . STATUS_MARKED) . ') ' . 'LEFT OUTER JOIN ' . TABLE_DISEASES . ' AS d ON (g2d.diseaseid = d.id)';
$this->aSQLViewList['GROUP_BY'] = 'g.id';
// List of columns and (default?) order for viewing an entry.
$this->aColumnsViewEntry = array('TableHeader_General' => 'General information', 'id' => 'Gene symbol', 'name' => 'Gene name', 'chromosome' => 'Chromosome', 'chrom_band' => 'Chromosomal band', 'imprinting_' => 'Imprinted', 'refseq_genomic_' => 'Genomic reference', 'refseq_UD_' => array('Mutalyzer genomic reference', LEVEL_ADMIN), 'refseq_transcript_' => 'Transcript reference', 'exon_tables' => 'Exon/intron information', 'diseases_' => 'Associated with diseases', 'reference' => 'Citation reference(s)', 'allow_download_' => array('Allow public to download all variant entries', LEVEL_COLLABORATOR), 'allow_index_wiki_' => array('Allow data to be indexed by WikiProfessional', LEVEL_COLLABORATOR), 'refseq_url_' => 'Refseq URL', 'curators_' => 'Curators', 'collaborators_' => array('Collaborators', LEVEL_COLLABORATOR), 'variants_' => 'Total number of public variants reported', 'uniq_variants_' => 'Unique public DNA variants reported', 'count_individuals' => 'Individuals with public variants', 'hidden_variants_' => 'Hidden variants', 'note_index' => 'Notes', 'created_by_' => array('Created by', LEVEL_COLLABORATOR), 'created_date_' => 'Date created', 'edited_by_' => array('Last edited by', LEVEL_COLLABORATOR), 'edited_date_' => array('Date last edited', LEVEL_COLLABORATOR), 'updated_by_' => array('Last updated by', LEVEL_COLLABORATOR), 'updated_date_' => 'Date last updated', 'version_' => 'Version', 'TableEnd_General' => '', 'HR_1' => '', 'TableStart_Graphs' => '', 'TableHeader_Graphs' => 'Graphical displays and utilities', 'graphs' => 'Graphs', 'ucsc' => 'UCSC Genome Browser', 'ensembl' => 'Ensembl Genome Browser', 'ncbi' => 'NCBI Sequence Viewer', 'TableEnd_Graphs' => '', 'HR_2' => '', 'TableStart_Links' => '', 'TableHeader_Links' => 'Links to other resources', 'url_homepage_' => 'Homepage URL', 'url_external_' => 'External URL', 'id_hgnc_' => 'HGNC', 'id_entrez_' => 'Entrez Gene', 'id_pubmed_gene_' => 'PubMed articles', 'id_omim_' => 'OMIM - Gene', 'disease_omim_' => 'OMIM - Diseases', 'show_hgmd_' => 'HGMD', 'show_genecards_' => 'GeneCards', 'show_genetests_' => 'GeneTests');
// List of columns and (default?) order for viewing a list of entries.
$this->aColumnsViewList = array('geneid' => array('view' => false, 'db' => array('g.id', 'ASC', true)), 'id_' => array('view' => array('Symbol', 100), 'db' => array('g.id', 'ASC', true)), 'name' => array('view' => array('Gene', 300), 'db' => array('g.name', 'ASC', true)), 'chromosome' => array('view' => array('Chr', 50), 'db' => array('g.chromosome', 'ASC', true)), 'chrom_band' => array('view' => array('Band', 70), 'db' => array('g.chrom_band', false, true)), 'transcripts' => array('view' => array('Transcripts', 90, 'style="text-align : right;"'), 'db' => array('transcripts', 'DESC', 'INT_UNSIGNED')), 'variants' => array('view' => array('Variants', 70, 'style="text-align : right;"'), 'db' => array('variants', 'DESC', 'INT_UNSIGNED')), 'uniq_variants' => array('view' => array('Unique variants', 70, 'style="text-align : right;"'), 'db' => array('uniq_variants', 'DESC', 'INT_UNSIGNED')), 'updated_date_' => array('view' => array('Last updated', 110), 'db' => array('g.updated_date', 'DESC', true)), 'diseases_' => array('view' => array('Associated with diseases', 200), 'db' => array('diseases_', false, 'TEXT')));
$this->sSortDefault = 'id_';
// Because the gene information is publicly available, remove some columns for the public.
$this->unsetColsByAuthLevel();
parent::__construct();
}
function __construct($sTable)
{
// Construct an object from a given database table name.
$this->sTable = $sTable;
parent::__construct();
}
function __construct()
{
// Default constructor.
global $_AUTH, $_DB;
$aArgs = array();
$this->sCategory = empty($this->sCategory) ? $this->sObject : $this->sCategory;
if (!$this->bShared) {
// "Simple", non-shared, data types (individuals, genomic variants, screenings).
$sSQL = 'SELECT c.*, ac.* ' . 'FROM ' . TABLE_ACTIVE_COLS . ' AS ac ' . 'LEFT OUTER JOIN ' . TABLE_COLS . ' AS c ON (c.id = ac.colid) ' . 'WHERE c.id LIKE "' . $this->sCategory . '/%" ' . 'ORDER BY c.col_order';
} else {
// Shared data type (variants on transcripts, phenotypes).
if ($this->sObjectID) {
// Parent object given (a gene for variants, a disease for phenotypes).
if ($this->sObject == 'Phenotype') {
$sSQL = 'SELECT c.*, sc.* ' . 'FROM ' . TABLE_COLS . ' AS c ' . 'INNER JOIN ' . TABLE_SHARED_COLS . ' AS sc ON (sc.colid = c.id) ' . 'WHERE c.id LIKE "' . $this->sCategory . '/%" ' . 'AND sc.diseaseid = ? ' . 'ORDER BY sc.col_order, sc.colid';
$aArgs[] = $this->sObjectID;
} elseif ($this->sObject == 'Transcript_Variant') {
$aArgs = explode(',', $this->sObjectID);
$sSQL = 'SELECT c.*, sc.* ' . 'FROM ' . TABLE_COLS . ' AS c ' . 'INNER JOIN ' . TABLE_SHARED_COLS . ' AS sc ON (sc.colid = c.id) ' . 'WHERE c.id LIKE "' . $this->sCategory . '/%" ' . 'AND sc.geneid IN (?' . str_repeat(', ?', count($aArgs) - 1) . ') ' . 'ORDER BY sc.col_order, sc.colid';
}
} elseif ($this->nID) {
// FIXME; kan er niet wat specifieke info in de objects (e.g. object_phenotypes) worden opgehaald, zodat dit stukje hier niet nodig is?
if ($this->sObject == 'Phenotype') {
$sSQL = 'SELECT c.*, sc.*, p.id AS phenotypeid ' . 'FROM ' . TABLE_COLS . ' AS c ' . 'INNER JOIN ' . TABLE_SHARED_COLS . ' AS sc ON (sc.colid = c.id) ' . 'INNER JOIN ' . TABLE_PHENOTYPES . ' AS p USING (diseaseid) ' . 'WHERE c.id LIKE "' . $this->sCategory . '/%" ' . 'AND p.id = ? ' . 'ORDER BY sc.col_order';
} elseif ($this->sObject == 'Transcript_Variant') {
$sSQL = 'SELECT c.*, sc.*, vot.id AS variantid ' . 'FROM ' . TABLE_COLS . ' AS c ' . 'INNER JOIN ' . TABLE_SHARED_COLS . ' AS sc ON (sc.colid = c.id) ' . 'INNER JOIN ' . TABLE_TRANSCRIPTS . ' AS t USING (geneid) ' . 'INNER JOIN ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' AS vot ON (t.id = vot.transcriptid) ' . 'WHERE c.id LIKE "' . $this->sCategory . '/%" ' . 'AND vot.id = ? ' . 'ORDER BY sc.col_order';
}
$aArgs[] = $this->nID;
} else {
$sSQL = 'SELECT c.*, c.id AS colid ' . 'FROM ' . TABLE_COLS . ' AS c ' . 'WHERE c.id LIKE "' . $this->sCategory . '/%" ' . 'ORDER BY c.col_order';
}
}
$q = $_DB->query($sSQL, $aArgs);
while ($z = $q->fetchAssoc()) {
$z['custom_links'] = array();
$z['form_type'] = explode('|', $z['form_type']);
// Modify form_type to include full legend text in second index of form_type.
if (!empty($z['description_legend_full'])) {
$z['form_type'][1] .= (empty($z['form_type'][1]) ? '' : '<BR>') . '<B>Legend: </B>' . str_replace(array("\r", "\n"), '', $z['description_legend_full']);
}
$z['select_options'] = explode("\r\n", $z['select_options']);
$this->aColumns[$z['id']] = $z;
}
// Gather the custom link information.
// 2015-01-23; 3.0-13; But not when importing, then we don't need this at all.
if (lovd_getProjectFile() != '/import.php') {
$aLinks = $_DB->query('SELECT l.*, GROUP_CONCAT(c2l.colid SEPARATOR ";") AS colids FROM ' . TABLE_LINKS . ' AS l INNER JOIN ' . TABLE_COLS2LINKS . ' AS c2l ON (l.id = c2l.linkid) WHERE c2l.colid LIKE ? GROUP BY l.id', array($this->sCategory . '/%'))->fetchAllAssoc();
foreach ($aLinks as $aLink) {
$aLink['regexp_pattern'] = '/' . str_replace(array('{', '}'), array('\\{', '\\}'), preg_replace('/\\[\\d\\]/', '(.*)', $aLink['pattern_text'])) . '/';
$aLink['replace_text'] = preg_replace('/\\[(\\d)\\]/', '\\$$1', $aLink['replace_text']);
$aCols = explode(';', $aLink['colids']);
foreach ($aCols as $sColID) {
if (isset($this->aColumns[$sColID])) {
$this->aColumns[$sColID]['custom_links'][] = $aLink['id'];
}
}
$this->aCustomLinks[$aLink['id']] = $aLink;
}
}
parent::__construct();
// Hide entries that are not marked or public.
if ($_AUTH['level'] < LEVEL_COLLABORATOR) {
// This check assumes lovd_isAuthorized() has already been called for gene-specific overviews.
if (in_array($this->sCategory, array('VariantOnGenome', 'VariantOnTranscript'))) {
$sAlias = 'vog';
} else {
$sAlias = strtolower($this->sCategory[0]);
}
// Construct list of user IDs for current user and users who share access with him.
$aOwnerIDs = array_merge(array($_AUTH['id']), lovd_getColleagues(COLLEAGUE_ALL));
$sOwnerIDsSQL = join(', ', $aOwnerIDs);
$this->aSQLViewList['WHERE'] .= (!empty($this->aSQLViewList['WHERE']) ? ' AND ' : '') . '(' . ($this->sObject == 'Screening' ? 'i' : $sAlias) . '.statusid >= ' . STATUS_MARKED . (!$_AUTH ? '' : ' OR (' . $sAlias . '.created_by = "' . $_AUTH['id'] . '" OR ' . $sAlias . '.owned_by IN (' . $sOwnerIDsSQL . '))') . '';
$this->aSQLViewEntry['WHERE'] .= (!empty($this->aSQLViewEntry['WHERE']) ? ' AND ' : '') . '(' . ($this->sObject == 'Screening' ? 'i' : $sAlias) . '.statusid >= ' . STATUS_MARKED . (!$_AUTH ? '' : ' OR (' . $sAlias . '.created_by = "' . $_AUTH['id'] . '" OR ' . $sAlias . '.owned_by IN (' . $sOwnerIDsSQL . '))') . ')';
if ($this->sCategory == 'VariantOnGenome' && $_AUTH && (count($_AUTH['curates']) || count($_AUTH['collaborates']))) {
// Added so that Curators and Collaborators can view the variants for which they have viewing rights in the genomic variant viewlist.
$this->aSQLViewList['WHERE'] .= ' OR t.geneid IN ("' . implode('", "', array_merge($_AUTH['curates'], $_AUTH['collaborates'])) . '"))';
} else {
$this->aSQLViewList['WHERE'] .= ')';
}
}
}
