function __construct()
{
// Default constructor.
global $_AUTH;
// SQL code for loading an entry for an edit form.
// FIXME; change owner to owned_by_ in the load entry query below.
$this->sSQLLoadEntry = 'SELECT s.*, ' . 'GROUP_CONCAT(DISTINCT s2g.geneid ORDER BY s2g.geneid SEPARATOR ";") AS _genes, ' . 'uo.name AS owner ' . 'FROM ' . TABLE_SCREENINGS . ' AS s ' . 'LEFT OUTER JOIN ' . TABLE_SCR2GENE . ' AS s2g ON (s.id = s2g.screeningid) ' . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS uo ON (s.owned_by = uo.id) ' . 'WHERE s.id = ? ' . 'GROUP BY s.id';
// SQL code for viewing an entry.
$this->aSQLViewEntry['SELECT'] = 's.*, ' . 'i.statusid AS individual_statusid, ' . 'GROUP_CONCAT(DISTINCT "=\\"", s2g.geneid, "\\"" SEPARATOR "|") AS search_geneid, ' . 'IF(s.variants_found = 1 AND COUNT(s2v.variantid) = 0, -1, COUNT(DISTINCT ' . ($_AUTH['level'] >= LEVEL_COLLABORATOR ? 's2v.variantid' : 'vog.id') . ')) AS variants_found_, ' . 'uo.name AS owned_by_, ' . 'uc.name AS created_by_, ' . 'ue.name AS edited_by_';
// 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->aSQLViewEntry['FROM'] = TABLE_SCREENINGS . ' AS s ' . 'LEFT OUTER JOIN ' . TABLE_SCR2GENE . ' AS s2g ON (s.id = s2g.screeningid) ' . 'LEFT OUTER JOIN ' . TABLE_SCR2VAR . ' AS s2v ON (s.id = s2v.screeningid) ' . ($_AUTH['level'] >= LEVEL_COLLABORATOR ? '' : 'LEFT OUTER JOIN ' . TABLE_VARIANTS . ' AS vog ON (s2v.variantid = vog.id AND (vog.statusid >= ' . STATUS_MARKED . (!$_AUTH ? '' : ' OR vog.created_by = "' . $_AUTH['id'] . '" OR vog.owned_by IN (' . $sOwnerIDsSQL . ')') . ')) ') . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS uo ON (s.owned_by = uo.id) ' . 'LEFT OUTER JOIN ' . TABLE_INDIVIDUALS . ' AS i ON (s.individualid = i.id) ' . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS uc ON (s.created_by = uc.id) ' . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS ue ON (s.edited_by = ue.id)';
$this->aSQLViewEntry['GROUP_BY'] = 's.id';
// SQL code for viewing the list of screenings
$this->aSQLViewList['SELECT'] = 's.*, ' . 's.id AS screeningid, ' . 'IF(s.variants_found = 1 AND COUNT(s2v.variantid) = 0, -1, COUNT(DISTINCT ' . ($_AUTH['level'] >= LEVEL_COLLABORATOR ? 's2v.variantid' : 'vog.id') . ')) AS variants_found_, ' . 'GROUP_CONCAT(DISTINCT s2g.geneid SEPARATOR ", ") AS genes, ' . ($_AUTH['level'] < LEVEL_COLLABORATOR ? '' : 'CASE i.statusid WHEN ' . STATUS_MARKED . ' THEN "marked" WHEN ' . STATUS_HIDDEN . ' THEN "del" END AS class_name, ') . 'uo.name AS owned_by_, ' . 'CONCAT_WS(";", uo.id, uo.name, uo.email, uo.institute, uo.department, IFNULL(uo.countryid, "")) AS _owner';
$this->aSQLViewList['FROM'] = TABLE_SCREENINGS . ' AS s ' . 'LEFT OUTER JOIN ' . TABLE_SCR2VAR . ' AS s2v ON (s.id = s2v.screeningid) ' . ($_AUTH['level'] >= LEVEL_COLLABORATOR ? '' : 'LEFT OUTER JOIN ' . TABLE_VARIANTS . ' AS vog ON (s2v.variantid = vog.id AND (vog.statusid >= ' . STATUS_MARKED . (!$_AUTH ? '' : ' OR vog.created_by = "' . $_AUTH['id'] . '" OR vog.owned_by IN (' . $sOwnerIDsSQL . ')') . ')) ') . 'LEFT OUTER JOIN ' . TABLE_SCR2GENE . ' AS s2g ON (s.id = s2g.screeningid) ' . 'LEFT OUTER JOIN ' . TABLE_INDIVIDUALS . ' AS i ON (s.individualid = i.id) ' . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS uo ON (s.owned_by = uo.id)';
$this->aSQLViewList['GROUP_BY'] = 's.id';
// Run parent constructor to find out about the custom columns.
parent::__construct();
// List of columns and (default?) order for viewing an entry.
$this->aColumnsViewEntry = array_merge(array('individualid_' => 'Individual ID'), $this->buildViewEntry(), array('variants_found_' => 'Variants found?', 'owned_by_' => 'Owner name', '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_merge(array('screeningid' => array('view' => false, 'db' => array('s.id', 'ASC', true)), 'id' => array('view' => array('Screening ID', 110, 'style="text-align : right;"'), 'db' => array('s.id', 'ASC', true)), 'individualid' => array('view' => array('Individual ID', 110, 'style="text-align : right;"'), 'db' => array('s.individualid', 'ASC', true))), $this->buildViewList(), array('genes' => array('view' => array('Genes screened', 20), 'db' => array('genes', 'ASC', 'TEXT')), 'variants_found_' => array('view' => array('Variants found', 100, 'style="text-align : right;"'), 'db' => array('variants_found_', 'ASC', 'INT_UNSIGNED')), 'owned_by_' => array('view' => array('Owner', 160), 'db' => array('uo.name', 'ASC', true)), 'created_date' => array('view' => array('Date created', 130), 'db' => array('s.created_date', 'ASC', true))));
$this->sSortDefault = 'id';
// Because the gene information is publicly available, remove some columns for the public.
$this->unsetColsByAuthLevel();
}
function __construct()
{
// Default constructor.
global $_AUTH;
// SQL code for loading an entry for an edit form.
// FIXME; change owner to owned_by_ in the load entry query below.
$this->sSQLLoadEntry = 'SELECT i.*, ' . 'uo.name AS owner, ' . 'GROUP_CONCAT(DISTINCT i2d.diseaseid ORDER BY i2d.diseaseid SEPARATOR ";") AS active_diseases_ ' . 'FROM ' . TABLE_INDIVIDUALS . ' AS i ' . 'LEFT OUTER JOIN ' . TABLE_IND2DIS . ' AS i2d ON (i.id = i2d.individualid) ' . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS uo ON (i.owned_by = uo.id) ' . 'WHERE i.id = ? ' . 'GROUP BY i.id';
// SQL code for viewing an entry.
$this->aSQLViewEntry['SELECT'] = 'i.*, ' . 'GROUP_CONCAT(DISTINCT d.id SEPARATOR ";") AS _diseaseids, ' . 'GROUP_CONCAT(DISTINCT d.id, ";", 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 p.diseaseid SEPARATOR ";") AS _phenotypes, ' . 'GROUP_CONCAT(DISTINCT s.id SEPARATOR ";") AS _screeningids, ' . 'uo.id AS owner, ' . 'uo.name AS owned_by_, ' . 'uc.name AS created_by_, ' . 'ue.name AS edited_by_';
$this->aSQLViewEntry['FROM'] = TABLE_INDIVIDUALS . ' AS i ' . 'LEFT OUTER JOIN ' . TABLE_SCREENINGS . ' AS s ON (i.id = s.individualid) ' . 'LEFT OUTER JOIN ' . TABLE_IND2DIS . ' AS i2d ON (i.id = i2d.individualid) ' . 'LEFT OUTER JOIN ' . TABLE_DISEASES . ' AS d ON (i2d.diseaseid = d.id) ' . 'LEFT OUTER JOIN ' . TABLE_PHENOTYPES . ' AS p ON (i.id = p.individualid) ' . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS uo ON (i.owned_by = uo.id) ' . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS uc ON (i.created_by = uc.id) ' . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS ue ON (i.edited_by = ue.id)';
$this->aSQLViewEntry['GROUP_BY'] = 'i.id';
// SQL code for viewing the list of individuals
$this->aSQLViewList['SELECT'] = 'i.*, ' . 'i.id AS individualid, ' . 'GROUP_CONCAT(DISTINCT d.id) AS diseaseids, ' . '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_, ' . 'GROUP_CONCAT(DISTINCT s2g.geneid ORDER BY s2g.geneid SEPARATOR ", ") AS genes_screened_, ' . 'GROUP_CONCAT(DISTINCT t.geneid ORDER BY t.geneid SEPARATOR ", ") AS variants_in_genes_, ' . 'COUNT(DISTINCT ' . ($_AUTH['level'] >= LEVEL_COLLABORATOR ? 's2v.variantid' : 'vog.id') . ') AS variants_, ' . 'uo.name AS owned_by_, ' . 'CONCAT_WS(";", uo.id, uo.name, uo.email, uo.institute, uo.department, IFNULL(uo.countryid, "")) AS _owner, ' . ($_AUTH['level'] < LEVEL_COLLABORATOR ? '' : 'CASE ds.id WHEN ' . STATUS_MARKED . ' THEN "marked" WHEN ' . STATUS_HIDDEN . ' THEN "del" WHEN ' . STATUS_PENDING . ' THEN "del" END AS class_name,') . 'ds.name AS status';
// 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['FROM'] = TABLE_INDIVIDUALS . ' AS i ' . 'LEFT OUTER JOIN ' . TABLE_IND2DIS . ' AS i2d ON (i.id = i2d.individualid) ' . 'LEFT OUTER JOIN ' . TABLE_DISEASES . ' AS d ON (i2d.diseaseid = d.id) ' . 'LEFT OUTER JOIN ' . TABLE_SCREENINGS . ' AS s ON (i.id = s.individualid) ' . 'LEFT OUTER JOIN ' . TABLE_SCR2VAR . ' AS s2v ON (s2v.screeningid = s.id) ' . ($_AUTH['level'] >= LEVEL_COLLABORATOR ? '' : 'LEFT OUTER JOIN ' . TABLE_VARIANTS . ' AS vog ON (s2v.variantid = vog.id AND (vog.statusid >= ' . STATUS_MARKED . (!$_AUTH ? '' : ' OR vog.created_by = "' . $_AUTH['id'] . '" OR vog.owned_by IN (' . $sOwnerIDsSQL . ')') . ')) ') . 'LEFT OUTER JOIN ' . TABLE_SCR2GENE . ' AS s2g ON (s.id = s2g.screeningid) ' . 'LEFT OUTER JOIN ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' AS vot ON (' . ($_AUTH['level'] >= LEVEL_COLLABORATOR ? 's2v.variantid' : 'vog.id') . ' = vot.id) ' . 'LEFT OUTER JOIN ' . TABLE_TRANSCRIPTS . ' AS t ON (t.id = vot.transcriptid) ' . 'LEFT OUTER JOIN ' . TABLE_USERS . ' AS uo ON (i.owned_by = uo.id) ' . 'LEFT OUTER JOIN ' . TABLE_DATA_STATUS . ' AS ds ON (i.statusid = ds.id)';
$this->aSQLViewList['GROUP_BY'] = 'i.id';
// Run parent constructor to find out about the custom columns.
parent::__construct();
// List of columns and (default?) order for viewing an entry.
$this->aColumnsViewEntry = array_merge($this->buildViewEntry(), array('panelid_' => 'Panel ID', 'panel_size' => 'Panel size', 'diseases_' => 'Diseases', 'parents_' => 'Parent(s)', 'owned_by_' => 'Owner name', 'status' => array('Individual data status', LEVEL_COLLABORATOR), '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_merge(array('individualid' => array('view' => false, 'db' => array('i.id', 'ASC', true)), 'id' => array('view' => array('Individual ID', 110, 'style="text-align : right;"'), 'db' => array('i.id', 'ASC', true)), 'panelid' => array('view' => array('Panel ID', 70, 'style="text-align : right;"'), 'db' => array('i.panelid', 'ASC', true))), $this->buildViewList(), array('diseaseids' => array('view' => array('Disease ID', 0), 'db' => array('diseaseids', false, true)), 'diseases_' => array('view' => array('Disease', 175), 'db' => array('diseases_', 'ASC', true)), 'genes_searched' => array('view' => false, 'db' => array('t.geneid', false, true)), 'genes_screened_' => array('view' => array('Genes screened', 175), 'db' => array('genes_screened_', false, true)), 'variants_in_genes_' => array('view' => array('Variants in genes', 175), 'db' => array('variants_in_genes_', false, true), 'legend' => array('The individual has variants for this gene.')), 'variants_' => array('view' => array('Variants', 75, 'style="text-align : right;"'), 'db' => array('variants_', 'DESC', 'INT_UNSIGNED')), 'panel_size' => array('view' => array('Panel size', 70, 'style="text-align : right;"'), 'db' => array('i.panel_size', 'DESC', true), 'legend' => array('How many individuals does this entry represent?')), 'owned_by_' => array('view' => array('Owner', 160), 'db' => array('uo.name', 'ASC', true)), 'owner_countryid' => array('view' => false, 'db' => array('uo.countryid', 'ASC', true)), 'status' => array('view' => array('Status', 70), 'db' => array('ds.name', false, true), 'auth' => LEVEL_COLLABORATOR)));
$this->sSortDefault = 'id';
// Because the information is publicly available, remove some columns for the public.
$this->unsetColsByAuthLevel();
}
function lovd_isColleagueOfOwner($sType, $Data, $bMustHaveEditPermission = true)
{
// Checks if the current user (specified by global $_AUTH) is owner of the
// data objects.
// Params:
// $sType Type of the data object to check (string)
// $Data ID of object (string) or array of IDs of multiple objects
// (array of strings). Returns true if user is a colleague of
// some owner for ALL of the objects.
// $bMustHaveEditPermission
// Flag, if true this function returns only true if the
// current user is a colleague of the owner of $Data with
// explicit edit permission as defined by field 'allow_edit'
// in TABLE_COLLEAGUES.
// Return: True if all of the objects of type $sType with an ID in $Data
// are owned or created by a colleague of the current user.
global $_DB;
if (!in_array($sType, array('individual', 'phenotype', 'screening', 'variant'))) {
// Unknown data type, return false by default.
return false;
}
if (!is_array($Data)) {
$Data = array($Data);
}
$colleagueTypeFlag = $bMustHaveEditPermission ? COLLEAGUE_CAN_EDIT : COLLEAGUE_ALL;
$aOwnerIDs = lovd_getColleagues($colleagueTypeFlag);
if (!$aOwnerIDs) {
// No colleagues that give this user the enough permissions.
return false;
}
$sColleaguePlaceholders = '(?' . str_repeat(', ?', count($aOwnerIDs) - 1) . ')';
$sDataPlaceholders = '(?' . str_repeat(', ?', count($Data) - 1) . ')';
$sQ = 'SELECT COUNT(*) FROM ' . constant('TABLE_' . strtoupper($sType) . 'S') . ' WHERE id IN ' . $sDataPlaceholders . ' AND (owned_by IN ' . $sColleaguePlaceholders . ')';
$q = $_DB->query($sQ, array_merge($Data, $aOwnerIDs));
return $q !== false && intval($q->fetchColumn()) == count($Data);
}
function __construct($aObjects = array(), $sOtherID = '')
{
// Default constructor.
global $_AUTH, $_CONF, $_DB;
if (!is_array($aObjects)) {
$aObjects = explode(',', $aObjects);
}
$this->sObjectID = implode(',', $aObjects);
// Receive OtherID or Gene.
if (ctype_digit($sOtherID)) {
$sGene = '';
$this->nOtherID = $sOtherID;
} else {
$sGene = $sOtherID;
}
// FIXME: Disable this part when not using any of the custom column data types...
// Collect custom column information, all active columns (possibly restricted per gene).
// FIXME; This join is not always needed (it's done for VOT columns, but sometimes they are excluded, or the join is not necessary because of the user level), exclude when not needed to speed up the query?
// Also, the select of public_view makes no sense of VOTs are restricted by gene.
// Note: objects inheriting LOVD_custom implement selection of
// viewable columns in buildViewList()
$sSQL = 'SELECT c.id, c.width, c.head_column, c.description_legend_short, c.description_legend_full, c.mysql_type, c.form_type, c.select_options, c.col_order, GROUP_CONCAT(sc.geneid, ":", sc.public_view SEPARATOR ";") AS public_view FROM ' . TABLE_ACTIVE_COLS . ' AS ac INNER JOIN ' . TABLE_COLS . ' AS c ON (c.id = ac.colid) LEFT OUTER JOIN ' . TABLE_SHARED_COLS . ' AS sc ON (sc.colid = ac.colid) ' . 'WHERE ' . ($_AUTH['level'] >= ($sGene ? LEVEL_COLLABORATOR : LEVEL_MANAGER) ? '' : '((c.id NOT LIKE "VariantOnTranscript/%" AND c.public_view = 1) OR sc.public_view = 1) AND ') . '(c.id LIKE ?' . str_repeat(' OR c.id LIKE ?', count($aObjects) - 1) . ') ' . (!$sGene ? 'GROUP BY c.id ' : 'AND c.id NOT LIKE "VariantOnTranscript/%" GROUP BY c.id ' . 'UNION ' . 'SELECT c.id, sc.width, c.head_column, c.description_legend_short, c.description_legend_full, c.mysql_type, c.form_type, c.select_options, sc.col_order, CONCAT(sc.geneid, ":", sc.public_view) AS public_view FROM ' . TABLE_COLS . ' AS c INNER JOIN ' . TABLE_SHARED_COLS . ' AS sc ON (c.id = sc.colid) WHERE sc.geneid = ? ' . ($_AUTH['level'] >= LEVEL_COLLABORATOR ? '' : 'AND sc.public_view = 1 ')) . 'ORDER BY col_order';
$aSQL = array();
foreach ($aObjects as $sObject) {
$aSQL[] = $sObject . '/%';
}
if ($sGene) {
$aSQL[] = $sGene;
}
if ($sOtherID) {
$this->nID = $sOtherID;
// We need the AJAX script to have the same restrictions!!!
}
// Increase the max GROUP_CONCAT() length, so that lists of many many genes still have all genes mentioned here (22.000 genes take 193.940 bytes here).
$_DB->query('SET group_concat_max_len = 200000');
$q = $_DB->query($sSQL, $aSQL);
while ($z = $q->fetchAssoc()) {
$z['custom_links'] = array();
$z['form_type'] = explode('|', $z['form_type']);
$z['select_options'] = explode("\r\n", $z['select_options']);
// What do we use this for?
if (substr($z['id'], 0, 19) == 'VariantOnTranscript') {
$z['public_view'] = explode(';', rtrim(preg_replace('/([A-Za-z0-9-]+:0;|:1)/', '', $z['public_view'] . ';'), ';'));
}
if (is_null($z['public_view'])) {
$z['public_view'] = array();
}
$this->aColumns[$z['id']] = $z;
}
if ($_AUTH) {
$_AUTH['allowed_to_view'] = array_merge($_AUTH['curates'], $_AUTH['collaborates']);
}
// Boolean indicating whether row_id is already in SELECT statement somewhere.
$bSetRowID = false;
$aSQL = $this->aSQLViewList;
// If possible, determine row_id SQL from combination of object types.
$sRowIDSQL = null;
if (in_array('VariantOnGenome', $aObjects) && (in_array('VariantOnTranscript', $aObjects) || in_array('VariantOnTranscriptUnique', $aObjects))) {
// Use "vog.id:vot.transcriptid" as row_id, fall back to "vog.id" if there is no VOT entry.
$aSQL['SELECT'] = 'CONCAT(vog.id, IFNULL(CONCAT(":", vot.transcriptid), "")) AS row_id';
$bSetRowID = true;
} elseif (in_array('Transcript', $aObjects)) {
$aSQL['SELECT'] = 't.id AS row_id';
$bSetRowID = true;
}
// Loop requested data types, and keep columns in order indicated by request.
foreach ($aObjects as $nKey => $sObject) {
// Generate custom column listing to be appended to SELECT SQL statement.
// We're not using SELECT * anymore because then we can't use the query as a subquery
// (which needs to have unique columns, and * can select `id` more than once).
// So, we need to select all columns that we need.
$sCustomCols = '';
$aCustomColNames = array_keys($this->getCustomColsForCategory($sObject));
if (count($aCustomColNames) > 0) {
$sCustomCols = ', `' . implode('`, `', $aCustomColNames) . '`';
}
switch ($sObject) {
case 'Gene':
if (!$bSetRowID) {
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . 'g.id AS row_id';
$bSetRowID = true;
}
if (!$aSQL['FROM']) {
// First data table in query.
$aSQL['FROM'] = TABLE_GENES . ' AS g';
$this->nCount = $_DB->query('SELECT COUNT(*) FROM ' . TABLE_GENES)->fetchColumn();
}
break;
case 'Transcript':
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . 't.id AS tid, ' . 't.geneid, t.name, t.id_ncbi, t.id_protein_ncbi';
if (!$bSetRowID) {
$aSQL['SELECT'] .= ', t.id AS row_id';
$bSetRowID = true;
}
if (!$aSQL['FROM']) {
// First data table in query.
$aSQL['FROM'] = TABLE_TRANSCRIPTS . ' AS t';
$this->nCount = $_DB->query('SELECT COUNT(*) FROM ' . TABLE_TRANSCRIPTS)->fetchColumn();
} else {
$aSQL['FROM'] .= ' INNER JOIN ' . TABLE_TRANSCRIPTS . ' AS t ON (';
$nKeyG = array_search('Gene', $aObjects);
$nKeyVOT = array_search('VariantOnTranscript', $aObjects);
if ($nKeyG !== false && $nKeyG < $nKey) {
// Earlier, Gene was used, join to that.
$aSQL['FROM'] .= 'g.id = t.geneid)';
} elseif ($nKeyVOT !== false && $nKeyVOT < $nKey) {
// Earlier, VOT was used, join to that.
$aSQL['FROM'] .= 'vot.transcriptid = t.id)';
}
// We have no fallback, so we'll easily detect an error if we messed up somewhere.
}
break;
case 'DistanceToVar':
$nKeyT = array_search('Transcript', $aObjects);
if ($nKeyT !== false && $nKeyT < $nKey && $this->nOtherID) {
// Earlier, Transcript was used, join to that.
// First, retrieve information of variant.
list($nPosStart, $nPosEnd) = $_DB->query('SELECT position_g_start, position_g_end FROM ' . TABLE_VARIANTS . ' WHERE id = ?', array($this->nOtherID))->fetchRow();
// Specific modifications for this overview; distance between variant and transcript in question.
if ($nPosStart && $nPosEnd) {
// 2014-08-11; 3.0-12; Transcripts on the reverse strand did not display the correctly calculated distance.
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . 'IF(t.position_g_mrna_start < t.position_g_mrna_end, IF(t.position_g_mrna_start > ' . $nPosEnd . ', t.position_g_mrna_start - ' . $nPosEnd . ', IF(t.position_g_mrna_end < ' . $nPosStart . ', ' . $nPosStart . ' - t.position_g_mrna_start, 0)), IF(t.position_g_mrna_end > ' . $nPosEnd . ', t.position_g_mrna_end - ' . $nPosEnd . ', IF(t.position_g_mrna_start < ' . $nPosStart . ', ' . $nPosStart . ' - t.position_g_mrna_end, 0))) AS distance_to_var';
} else {
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . '"?" AS distance_to_var';
}
}
break;
case 'VariantOnGenome':
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . 'vog.id AS vogid, vog.chromosome, a.name AS allele_' . (!in_array('VariantOnTranscript', $aObjects) ? ', eg.name AS vog_effect' : '') . (in_array('Individual', $aObjects) || in_array('VariantOnTranscriptUnique', $aObjects) ? '' : ', uo.name AS owned_by_, CONCAT_WS(";", uo.id, uo.name, uo.email, uo.institute, uo.department, IFNULL(uo.countryid, "")) AS _owner') . (in_array('VariantOnTranscriptUnique', $aObjects) ? '' : ', dsg.id AS var_statusid, dsg.name AS var_status') . $sCustomCols;
$nKeyVOTUnique = array_search('VariantOnTranscriptUnique', $aObjects);
if (!$bSetRowID) {
$aSQL['SELECT'] .= ', vog.id AS row_id';
$bSetRowID = true;
}
if (!$aSQL['FROM']) {
// First data table in query.
$aSQL['FROM'] = TABLE_VARIANTS . ' AS vog';
$this->nCount = $_DB->query('SELECT COUNT(*) FROM ' . TABLE_VARIANTS)->fetchColumn();
$aSQL['GROUP_BY'] = 'vog.id';
// Necessary for GROUP_CONCAT(), such as in Screening.
$aSQL['ORDER_BY'] = 'vog.chromosome ASC, vog.position_g_start';
} elseif ($nKeyVOTUnique !== false && $nKeyVOTUnique < $nKey) {
// For the unique variant view a GROUP_CONCAT must be done for the variantOnGenome fields.
foreach ($this->getCustomColsForCategory('VariantOnGenome') as $sCol => $aCol) {
// Here all VariantOnGenome columns are grouped with GROUP_CONCAT. In prepareData(),
// these fields are exploded and the elements are counted, limiting the grouped values
// to a certain length. To recognize the separate items, ;; is used as a separator.
// The NULLIF() is used to not show empty values. GROUP_CONCAT handles NULL values well (ignores them), but not empty values (includes them).
$aSQL['SELECT'] .= ', GROUP_CONCAT(DISTINCT NULLIF(`' . $sCol . '`, "") SEPARATOR ";;") AS `' . $sCol . '`';
}
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_VARIANTS . ' AS vog ON (vot.id = vog.id)';
} else {
// 2016-07-20; 3.0-17; Added FORCE INDEX because MySQL optimized the Full data view
// differently, resulting in immense temporary tables filling up the disk.
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_VARIANTS . ' AS vog FORCE INDEX FOR JOIN (PRIMARY) ON (';
$nKeyVOT = array_search('VariantOnTranscript', $aObjects);
if ($nKeyVOT !== false && $nKeyVOT < $nKey) {
// Earlier, VOT was used, join to that.
$aSQL['FROM'] .= 'vot.id = vog.id)';
}
// We have no fallback, so we'll easily detect an error if we messed up somewhere.
}
$aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_ALLELES . ' AS a ON (vog.allele = a.id)';
if (!in_array('VariantOnTranscript', $aObjects)) {
$aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_EFFECT . ' AS eg ON (vog.effectid = eg.id)';
}
if (!in_array('Individual', $aObjects)) {
$aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_USERS . ' AS uo ON (vog.owned_by = uo.id)';
}
$aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_DATA_STATUS . ' AS dsg ON (vog.statusid = dsg.id)';
// If no collaborator, hide lines with hidden variants!
if ($_AUTH['level'] < LEVEL_COLLABORATOR) {
// 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);
$aSQL['WHERE'] .= (!$aSQL['WHERE'] ? '' : ' AND ') . '(vog.statusid >= ' . STATUS_MARKED . (!$_AUTH ? '' : ' OR vog.created_by = "' . $_AUTH['id'] . '" OR vog.owned_by IN (' . $sOwnerIDsSQL . ')') . ')';
}
break;
case 'VariantOnTranscript':
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . 'vot.id AS votid, vot.transcriptid, vot.position_c_start, vot.position_c_start_intron, vot.position_c_end, vot.position_c_end_intron, et.name as vot_effect' . $sCustomCols;
$nKeyVOG = array_search('VariantOnGenome', $aObjects);
if (!$bSetRowID) {
$aSQL['SELECT'] .= ', vot.id AS row_id';
$bSetRowID = true;
}
if (!$aSQL['FROM']) {
// First data table in query.
$aSQL['FROM'] = TABLE_VARIANTS_ON_TRANSCRIPTS . ' AS vot';
$this->nCount = $_DB->query('SELECT COUNT(*) FROM ' . TABLE_VARIANTS_ON_TRANSCRIPTS)->fetchColumn();
$aSQL['GROUP_BY'] = 'vot.id';
// Necessary for GROUP_CONCAT(), such as in Screening.
} elseif ($nKeyVOG !== false && $nKeyVOG < $nKey) {
// Previously, VOG was used. We will join VOT with VOG, using GROUP_CONCAT.
// SELECT will be different: we will GROUP_CONCAT the whole lot, per column.
// Sort GROUP_CONCAT() based on transcript name. We'll have to join Transcripts for that.
// That will break if somebody wants to join transcripts themselves, but why would somebody want that?
$sGCOrderBy = 't.geneid, t.id_ncbi';
foreach ($this->getCustomColsForCategory('VariantOnTranscript') as $sCol => $aCol) {
$aSQL['SELECT'] .= ', GROUP_CONCAT(DISTINCT ' . ($sCol != 'VariantOnTranscript/DNA' ? '`' . $sCol . '`' : 'CONCAT(t.id_ncbi, ":", `' . $sCol . '`)') . ' ORDER BY ' . $sGCOrderBy . ' SEPARATOR ", ") AS `' . $sCol . '`';
}
// If we're joining to Scr2Var, we're showing the Individual- and Screening-specific views, and we want to show a gene as well.
// We can't use _geneid below, because LOVD will explode that into an array.
if (array_search('Scr2Var', $aObjects) !== false) {
$aSQL['SELECT'] .= ', GROUP_CONCAT(DISTINCT t.geneid ORDER BY ' . $sGCOrderBy . ' SEPARATOR ", ") AS genes';
}
// Security checks in this file's prepareData() need geneid to see if the column in question is set to non-public for one of the genes.
$aSQL['SELECT'] .= ', GROUP_CONCAT(DISTINCT t.geneid SEPARATOR ";") AS _geneid';
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' AS vot ON (';
// Earlier, VOG was used, join to that.
$aSQL['FROM'] .= 'vog.id = vot.id)';
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_TRANSCRIPTS . ' AS t ON (vot.transcriptid = t.id)';
} else {
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' AS vot ON (';
$nKeyT = array_search('Transcript', $aObjects);
if ($nKeyT !== false && $nKeyT < $nKey) {
// Earlier, T was used, join to that.
$aSQL['FROM'] .= 't.id = vot.transcriptid)';
// Nice, but if we're showing transcripts and variants on transcripts in one viewList, we'd only want to see the transcripts that HAVE variants.
$aSQL['WHERE'] .= (!$aSQL['WHERE'] ? '' : ' AND ') . 'vot.id IS NOT NULL';
}
// We have no fallback, so we'll easily detect an error if we messed up somewhere.
}
$aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_EFFECT . ' AS et ON (vot.effectid = et.id)';
break;
case 'VariantOnTranscriptUnique':
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . 'vot.id AS votid, vot.transcriptid, ' . 'vot.position_c_start, vot.position_c_start_intron, ' . 'vot.position_c_end, vot.position_c_end_intron';
// To group variants together that belong together (regardless of minor textual differences, we replace parentheses, remove the "c.", and trim for question marks.
// This notation will be used to group on, and search on when navigating from the unique variant view to the full variant view.
$aSQL['SELECT'] .= ', TRIM(BOTH "?" FROM TRIM(LEADING "c." FROM REPLACE(REPLACE(`VariantOnTranscript/DNA`, ")", ""), "(", ""))) AS vot_clean_dna_change' . ', GROUP_CONCAT(DISTINCT et.name SEPARATOR ", ") AS vot_effect' . ', GROUP_CONCAT(DISTINCT NULLIF(uo.name, "") SEPARATOR ", ") AS owned_by_' . ', GROUP_CONCAT(DISTINCT CONCAT_WS(";", uo.id, uo.name, uo.email, uo.institute, uo.department, IFNULL(uo.countryid, "")) SEPARATOR ";;") AS __owner';
// dsg.id GROUP_CONCAT is ascendingly ordered. This is done for the color marking.
// In prepareData() the lowest var_statusid is used to determine the coloring.
$aSQL['SELECT'] .= ', GROUP_CONCAT(DISTINCT NULLIF(dsg.id, "") ORDER BY dsg.id ASC SEPARATOR ", ") AS var_statusid, GROUP_CONCAT(DISTINCT NULLIF(dsg.name, "") SEPARATOR ", ") AS var_status' . ', COUNT(`VariantOnTranscript/DNA`) AS vot_reported';
if (!$bSetRowID) {
$aSQL['SELECT'] .= ', vot.id AS row_id';
$bSetRowID = true;
}
$aSQL['FROM'] = TABLE_VARIANTS_ON_TRANSCRIPTS . ' AS vot';
// FIXME: On large databases, we might want to skip this, since a COUNT(*) on InnoDB tables isn't fast at all, and nCount doesn't need to be specific at all.
$this->nCount = $_DB->query('SELECT COUNT(DISTINCT `VariantOnTranscript/DNA`) FROM ' . TABLE_VARIANTS_ON_TRANSCRIPTS)->fetchColumn();
$aSQL['GROUP_BY'] = '`position_c_start`, `position_c_start_intron`, `position_c_end`, `position_c_end_intron`, vot_clean_dna_change';
// Necessary for GROUP_CONCAT(), such as in Screening.
foreach ($this->getCustomColsForCategory('VariantOnTranscript') as $sCol => $aCol) {
// Here all VariantOnTranscript columns are grouped with GROUP_CONCAT. In prepareData(),
// these fields are exploded and the elements are counted, limiting the grouped values
// to a certain length. To recognize the separate items, ;; is used as a separator.
// The NULLIF() is used to not show empty values. GROUP_CONCAT handles NULL values well (ignores them), but not empty values (includes them).
$aSQL['SELECT'] .= ', GROUP_CONCAT(DISTINCT NULLIF(`' . $sCol . '`, "") SEPARATOR ";;") AS `' . $sCol . '`';
}
$aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_EFFECT . ' AS et ON (vot.effectid = et.id)';
break;
case 'Screening':
if (!$bSetRowID) {
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . 's.id AS row_id';
$bSetRowID = true;
}
if (!$aSQL['FROM']) {
// First data table in query.
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . 's.id as sid' . $sCustomCols;
$aSQL['FROM'] = TABLE_SCREENINGS . ' AS s';
$this->nCount = $_DB->query('SELECT COUNT(*) FROM ' . TABLE_SCREENINGS)->fetchColumn();
$aSQL['ORDER_BY'] = 's.id';
} else {
// SELECT will be different: we will GROUP_CONCAT the whole lot, per column.
$sGCOrderBy = isset($this->aColumns['Screening/Date']) ? '`Screening/Date`' : 'id';
foreach ($this->getCustomColsForCategory('Screening') as $sCol => $aCol) {
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . 'GROUP_CONCAT(DISTINCT `' . $sCol . '` ORDER BY s.' . $sGCOrderBy . ' SEPARATOR ";") AS `' . $sCol . '`';
}
$nKeyVOG = array_search('VariantOnGenome', $aObjects);
$nKeyVOT = array_search('VariantOnTranscript', $aObjects);
$nKeyI = array_search('Individual', $aObjects);
if ($nKeyVOG !== false && $nKeyVOG < $nKey) {
// Earlier, VOG was used, join to that.
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_SCR2VAR . ' AS s2v ON (vog.id = s2v.variantid) LEFT JOIN ' . TABLE_SCREENINGS . ' AS s ON (s2v.screeningid = s.id)';
} elseif ($nKeyVOT !== false && $nKeyVOT < $nKey) {
// Earlier, VOT was used, join to that.
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_SCR2VAR . ' AS s2v ON (vot.id = s2v.variantid) LEFT JOIN ' . TABLE_SCREENINGS . ' AS s ON (s2v.screeningid = s.id)';
} elseif ($nKeyI !== false && $nKeyI < $nKey) {
// Earlier, I was used, join to that.
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_SCREENINGS . ' AS s ON (i.id = s.individualid)';
}
// We have no fallback, so it won't join if we messed up somewhere!
}
break;
case 'Scr2Var':
if (!$bSetRowID) {
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . 's2v.id AS row_id';
$bSetRowID = true;
}
if ($aSQL['FROM']) {
// Not allowed to be the first data table in query, because entries are usually grouped by the first table.
$nKeyVOG = array_search('VariantOnGenome', $aObjects);
$nKeyVOT = array_search('VariantOnTranscript', $aObjects);
if ($nKeyVOG !== false && $nKeyVOG < $nKey) {
// Earlier, VOG was used, join to that.
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_SCR2VAR . ' AS s2v ON (vog.id = s2v.variantid)';
// This combination only happens when we're joining VOG to Scr2Var to VOT, to show variants in a screening or individual.
// Then grouping on the s2v's variant ID is faster, because we're searching on the s2v.screeningid and like this we keep
// the group by and the where in the same table, greatly increasing the speed of the query.
$aSQL['GROUP_BY'] = 's2v.variantid';
// Necessary for GROUP_CONCAT().
} elseif ($nKeyVOT !== false && $nKeyVOT < $nKey) {
// Earlier, VOT was used, join to that.
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_SCR2VAR . ' AS s2v ON (vot.id = s2v.variantid)';
}
// We have no fallback, so it won't join if we messed up somewhere!
}
break;
case 'Individual':
$aSQL['SELECT'] .= (!$aSQL['SELECT'] ? '' : ', ') . 'i.id AS iid, i.panel_size, i.owned_by, 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_, uo.name AS owned_by_, CONCAT_WS(";", uo.id, uo.name, uo.email, uo.institute, uo.department, IFNULL(uo.countryid, "")) AS _owner, dsi.id AS ind_statusid, dsi.name AS ind_status' . $sCustomCols;
if (!$bSetRowID) {
$aSQL['SELECT'] .= ', i.id AS row_id';
$bSetRowID = true;
}
if (!$aSQL['FROM']) {
// First data table in query.
$aSQL['FROM'] = TABLE_INDIVIDUALS . ' AS i';
$this->nCount = $_DB->query('SELECT COUNT(*) FROM ' . TABLE_INDIVIDUALS)->fetchColumn();
$aSQL['ORDER_BY'] = 'i.id';
// If no manager, hide lines with hidden individuals (not specific to a gene)!
if ($_AUTH['level'] < LEVEL_MANAGER) {
$aSQL['WHERE'] .= (!$aSQL['WHERE'] ? '' : ' AND ') . 'i.statusid >= ' . STATUS_MARKED;
}
} else {
$nKeyS = array_search('Screening', $aObjects);
$nKeyVOG = array_search('VariantOnGenome', $aObjects);
$nKeyVOT = array_search('VariantOnTranscript', $aObjects);
if ($nKeyS === false || $nKeyS > $nKey) {
// S was not used yet, join to something else first!
if ($nKeyVOG !== false && $nKeyVOG < $nKey) {
// Earlier, VOG was used, join to that.
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_SCR2VAR . ' AS s2v ON (vog.id = s2v.variantid) LEFT JOIN ' . TABLE_SCREENINGS . ' AS s ON (s2v.screeningid = s.id)';
} elseif ($nKeyVOT !== false && $nKeyVOT < $nKey) {
// Earlier, VOT was used, join to that.
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_SCR2VAR . ' AS s2v ON (vot.id = s2v.variantid) LEFT JOIN ' . TABLE_SCREENINGS . ' AS s ON (s2v.screeningid = s.id)';
}
// We have no fallback, so it won't join if we messed up somewhere!
}
$aSQL['FROM'] .= ' LEFT JOIN ' . TABLE_INDIVIDUALS . ' AS i ON (s.individualid = i.id';
// If no collaborator, hide hidden individuals (from the join, don't hide the line)!
if ($_AUTH['level'] < LEVEL_COLLABORATOR) {
// 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);
$aSQL['FROM'] .= ' AND (i.statusid >= ' . STATUS_MARKED . (!$_AUTH ? '' : ' OR i.created_by = "' . $_AUTH['id'] . '" OR i.owned_by IN (' . $sOwnerIDsSQL . ')') . ')';
}
$aSQL['FROM'] .= ')';
}
$aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_IND2DIS . ' AS i2d ON (i.id = i2d.individualid) LEFT OUTER JOIN ' . TABLE_DISEASES . ' AS d ON (i2d.diseaseid = d.id)';
$aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_USERS . ' AS uo ON (i.owned_by = uo.id)';
$aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_DATA_STATUS . ' AS dsi ON (i.statusid = dsi.id)';
break;
}
}
if (!$aSQL['SELECT'] || !$aSQL['FROM']) {
// Apparently, not implemented or no objects given.
lovd_displayError('ObjectError', 'CustomViewLists::__construct() requested with non-existing or missing object(s) \'' . htmlspecialchars(implode(',', $aObjects)) . '\'.');
}
$this->aSQLViewList = $aSQL;
if ($this->sObjectID == 'Transcript,VariantOnTranscript,VariantOnGenome') {
// The joining of the tables needed for this view are in this order, but I want a different order on display.
$aObjects = array('Transcript', 'VariantOnGenome', 'VariantOnTranscript');
}
// Now build $this->aColumnsViewList, from the order given by $aObjects and TABLE_COLS.col_order.
foreach ($aObjects as $nKey => $sObject) {
switch ($sObject) {
case 'Gene':
$sPrefix = 'g.';
// The fixed columns.
$this->aColumnsViewList = array_merge($this->aColumnsViewList, array('chromosome' => array('view' => false, 'db' => array('g.chromosome', 'ASC', true))));
break;
case 'Transcript':
$sPrefix = 't.';
// The fixed columns.
$this->aColumnsViewList = array_merge($this->aColumnsViewList, array('tid' => array('view' => false, 'db' => array('t.id', 'ASC', true)), 'geneid' => array('view' => array('Gene', 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))));
if (!$this->sSortDefault) {
// First data table in view.
$this->sSortDefault = 'geneid';
}
// The custom ViewList with transcripts and variants also names the id_ncbi field differently.
if ($nKey == 0 && in_array('VariantOnTranscript', $aObjects)) {
// Object [0] is Transcripts, [1] is VOT; this is the in_gene view.
$this->aColumnsViewList['id_ncbi']['view'][0] = 'Transcript';
}
break;
case 'DistanceToVar':
// The fixed columns.
$this->aColumnsViewList = array_merge($this->aColumnsViewList, array('distance_to_var' => array('view' => array('Distance (bp)', 90, 'style="text-align : right;"'), 'db' => array('distance_to_var', 'ASC', false))));
// Always force default sorting...
$this->sSortDefault = 'distance_to_var';
break;
case 'VariantOnGenome':
$sPrefix = 'vog.';
// The fixed columns.
$this->aColumnsViewList = array_merge($this->aColumnsViewList, array('chromosome' => array('view' => array('Chr', 50), 'db' => array('vog.chromosome', 'ASC', true)), 'allele_' => array('view' => array('Allele', 120), 'db' => array('a.name', 'ASC', true), 'legend' => array('On which allele is the variant located? Does not necessarily imply inheritance!', 'On which allele is the variant located? Does not necessarily imply inheritance! \'Paternal\' (confirmed or inferred), \'Maternal\' (confirmed or inferred), \'Parent #1\' or #2 for compound heterozygosity without having screened the parents, \'Unknown\' for heterozygosity without having screened the parents, \'Both\' for homozygozity.')), 'vog_effect' => array('view' => array('Effect', 70), 'db' => array('eg.name', 'ASC', true), 'legend' => array('The variant\'s effect on a protein\'s function, in the format Reported/Curator concluded; ranging from \'+\' (variant affects function) to \'-\' (does not affect function).', 'The variant\'s effect on a protein\'s function, in the format Reported/Curator concluded; \'+\' indicating the variant affects function, \'+?\' probably affects function, \'-\' does not affect function, \'-?\' probably does not affect function, \'?\' effect unknown, \'.\' effect not classified.'))));
if (in_array('VariantOnTranscript', $aObjects) || in_array('VariantOnTranscriptUnique', $aObjects)) {
unset($this->aColumnsViewList['vog_effect']);
}
if (!$this->sSortDefault) {
// First data table in view.
$this->sSortDefault = 'VariantOnGenome/DNA';
}
$this->sRowLink = 'variants/{{zData_vogid}}#{{zData_transcriptid}}';
break;
case 'VariantOnTranscript':
$sPrefix = 'vot.';
// The fixed columns.
$this->aColumnsViewList = array_merge($this->aColumnsViewList, array('transcriptid' => array('view' => false, 'db' => array('vot.transcriptid', 'ASC', true)), 'position_c_start' => array('view' => false, 'db' => array('vot.position_c_start', 'ASC', true)), 'position_c_start_intron' => array('view' => false, 'db' => array('vot.position_c_start_intron', 'ASC', true)), 'position_c_end' => array('view' => false, 'db' => array('vot.position_c_end', 'ASC', true)), 'position_c_end_intron' => array('view' => false, 'db' => array('vot.position_c_end_intron', 'ASC', true)), 'vot_clean_dna_change' => array('view' => false, 'db' => array('TRIM(BOTH "?" FROM TRIM(LEADING "c." FROM REPLACE(REPLACE(`VariantOnTranscript/DNA`, ")", ""), "(", "")))', 'ASC', 'TEXT')), 'genes' => array('view' => array('Gene', 100), 'db' => array('t.geneid', 'ASC', true)), 'vot_effect' => array('view' => array('Effect', 70), 'db' => array('et.name', 'ASC', true), 'legend' => array('The variant\'s effect on the protein\'s function, in the format Reported/Curator concluded; ranging from \'+\' (variant affects function) to \'-\' (does not affect function).', 'The variant\'s effect on the protein\'s function, in the format Reported/Curator concluded; \'+\' indicating the variant affects function, \'+?\' probably affects function, \'-\' does not affect function, \'-?\' probably does not affect function, \'?\' effect unknown, \'.\' effect not classified.'))));
// Only show the gene symbol when we have Scr2Var included, because these are the Individual- and Screening-specific views.
// FIXME: Perhaps it would be better to always show this column with VOT, but then hide it in all views that don't need it.
if (array_search('Scr2Var', $aObjects) === false) {
unset($this->aColumnsViewList['genes']);
}
if (!$this->sSortDefault) {
// First data table in view.
$this->sSortDefault = 'VariantOnTranscript/DNA';
}
break;
case 'VariantOnTranscriptUnique':
$sPrefix = 'vot.';
// The fixed columns.
$this->aColumnsViewList = array_merge($this->aColumnsViewList, array('transcriptid' => array('view' => false, 'db' => array('vot.transcriptid', 'ASC', true)), 'vot_effect' => array('view' => array('Effect', 70), 'db' => array('et.name', 'ASC', true), 'legend' => array('The variant\'s effect on the protein\'s function, in the format Reported/Curator concluded; ranging from \'+\' (variant affects function) to \'-\' (does not affect function).', 'The variant\'s effect on the protein\'s function, in the format Reported/Curator concluded; \'+\' indicating the variant affects function, \'+?\' probably affects function, \'-\' does not affect function, \'-?\' probably does not affect function, \'?\' effect unknown, \'.\' effect not classified.')), 'vot_reported' => array('view' => array('Reported', 70, 'style="text-align : right;"'), 'db' => array('vot_reported', 'ASC', 'INT_UNSIGNED'), 'legend' => array('The number of times this variant has been reported.', 'The number of times this variant has been reported in the database.'))));
if (!$this->sSortDefault) {
// First data table in view.
$this->sSortDefault = 'VariantOnTranscript/DNA';
}
break;
case 'Screening':
$sPrefix = 's.';
// No fixed columns.
if (!$this->sSortDefault) {
// First data table in view.
// The fixed columns, only when first table.
$this->aColumnsViewList = array_merge($this->aColumnsViewList, array('sid' => array('view' => array('Screening ID', 110, 'style="text-align : right;"'), 'db' => array('s.id', 'ASC', true))));
$this->sSortDefault = 'id';
}
break;
case 'Scr2Var':
$sPrefix = 's2v.';
// No fixed columns, is only used to filter variants based on screening ID.
$this->aColumnsViewList = array_merge($this->aColumnsViewList, array('screeningid' => array('view' => false, 'db' => array('s2v.screeningid', false, true))));
break;
case 'Individual':
$sPrefix = 'i.';
// The fixed columns.
$this->aColumnsViewList = array_merge($this->aColumnsViewList, array('diseases_' => array('view' => array('Disease', 175), 'db' => array('diseases_', 'ASC', true))));
if (!$this->sSortDefault) {
$this->sSortDefault = 'id';
}
break;
}
// The custom columns.
foreach ($this->aColumns as $sColID => $aCol) {
if (strpos($sColID, str_replace('Unique', '', $sObject) . '/') === 0) {
$bAlignRight = preg_match('/^(DEC|FLOAT|(TINY|SMALL|MEDIUM|BIG)?INT)/', $aCol['mysql_type']);
$this->aColumnsViewList[$sColID] = array('view' => array($aCol['head_column'], $aCol['width'], $bAlignRight ? ' align="right"' : ''), 'db' => array($sPrefix . '`' . $aCol['id'] . '`', 'ASC', lovd_getColumnType('', $aCol['mysql_type'])), 'legend' => array($aCol['description_legend_short'], $aCol['description_legend_full']), 'allowfnr' => true);
}
}
// Some fixed columns are supposed to be shown AFTER this objects's custom columns, so we'll need to go through the objects again.
switch ($sObject) {
case 'VariantOnGenome':
// More fixed columns.
$this->aColumnsViewList = array_merge($this->aColumnsViewList, array('owned_by_' => array('view' => array('Owner', 160), 'db' => array('uo.name', 'ASC', true)), 'owner_countryid' => array('view' => false, 'db' => array('uo.countryid', 'ASC', true)), 'var_status' => array('view' => array('Var. status', 70), 'db' => array('dsg.name', false, true))));
if (in_array('Individual', $aObjects)) {
unset($this->aColumnsViewList['owned_by_']);
}
if ($_AUTH['level'] < LEVEL_COLLABORATOR) {
// Unset status column for non-collaborators. We're assuming here, that lovd_isAuthorized() only gets called for gene-specific overviews.
unset($this->aColumnsViewList['var_status']);
}
// 2015-10-09; 3.0-14; Add genome build name to the VOG/DNA field.
$this->aColumnsViewList['VariantOnGenome/DNA']['view'][0] .= ' (' . $_CONF['refseq_build'] . ')';
break;
case 'Individual':
// More fixed columns.
$this->aColumnsViewList = array_merge($this->aColumnsViewList, array('panel_size' => array('view' => array('Panel size', 70, 'style="text-align : right;"'), 'db' => array('i.panel_size', 'DESC', true)), 'owned_by_' => array('view' => array('Owner', 160), 'db' => array('uo.name', 'ASC', true)), 'ind_status' => array('view' => array('Ind. status', 70), 'db' => array('dsi.name', false, true))));
if ($_AUTH['level'] < LEVEL_COLLABORATOR) {
// Unset status column for non-collaborators. We're assuming here, that lovd_isAuthorized() only gets called for gene-specific overviews.
unset($this->aColumnsViewList['ind_status']);
}
break;
}
}
// Gather the custom link information. It's just easier to load all custom links, instead of writing code that checks for the appropriate objects.
$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) GROUP BY l.id')->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;
}
// Not including parent constructor, because these table settings will make it freak out.
//parent::__construct();
// Therefore, row links need to be created by us (which is done above).
}
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'] .= ')';
}
}
}
