object_custom_viewlists.php 57.8 KB
Newer Older
1 2 3 4 5 6
<?php
/*******************************************************************************
 *
 * LEIDEN OPEN VARIATION DATABASE (LOVD)
 *
 * Created     : 2011-08-15
7
 * Modified    : 2019-10-01
8
 * For LOVD    : 3.0-22
9
 *
10
 * Copyright   : 2004-2019 Leiden University Medical Center; http://www.LUMC.nl/
11 12 13
 * Programmers : Ivo F.A.C. Fokkema <I.F.A.C.Fokkema@LUMC.nl>
 *               Ivar C. Lugtenburg <I.C.Lugtenburg@LUMC.nl>
 *               Daan Asscheman <D.Asscheman@LUMC.nl>
14
 *               M. Kroon <m.kroon@lumc.nl>
15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
 *
 *
 * This file is part of LOVD.
 *
 * LOVD is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * LOVD is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with LOVD.  If not, see <http://www.gnu.org/licenses/>.
 *
 *************/

// Don't allow direct access.
if (!defined('ROOT_PATH')) {
    exit;
}
// Require parent class definition.
require_once ROOT_PATH . 'class/objects.php';





class LOVD_CustomViewList extends LOVD_Object {
    // This class extends the basic Object class and it handles pre-configured custom viewLists.
    var $sObject = 'Custom_ViewList';
48
    var $nOtherID = 0; // Some objects (like DistanceToVar) need an additional ID.
49
    var $aColumns = array();
50 51 52 53 54 55 56
    var $aCustomLinks = array();
    var $nCount = 0; // Necessary for tricking Objects::getCount() that is run in viewList().





57
    function __construct ($aObjects = array(), $sOtherID = '')
58 59
    {
        // Default constructor.
60
        global $_AUTH, $_CONF, $_DB, $_SETT;
61 62 63 64 65

        if (!is_array($aObjects)) {
            $aObjects = explode(',', $aObjects);
        }
        $this->sObjectID = implode(',', $aObjects);
66 67 68 69 70 71 72
        // Receive OtherID or Gene.
        if (ctype_digit($sOtherID)) {
            $sGene = '';
            $this->nOtherID = $sOtherID;
        } else {
            $sGene = $sOtherID;
        }
73

Ivo Fokkema's avatar
Ivo Fokkema committed
74

75
        // FIXME: Disable this part when not using any of the custom column data types...
Ivo Fokkema's avatar
Ivo Fokkema committed
76
        // Collect custom column information, all active columns (possibly restricted per gene).
77
        // 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?
78
        //   Also, the select of public_view makes no sense of VOTs are restricted by gene.
79
        // Note: objects inheriting LOVD_custom implement selection of
80
        //  viewable columns in buildViewList().
81
        $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) ' .
82
                    'WHERE ' . ($_AUTH['level'] >= ($sGene? $_SETT['user_level_settings']['see_nonpublic_data'] : 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) . ') ' .
83
                    (!$sGene? 'GROUP BY c.id ' :
Ivo Fokkema's avatar
Ivo Fokkema committed
84
                      // If gene is given, only shown VOT columns active in the given gene! We'll use an UNION for that, so that we'll get the correct width and order also.
85
                      'AND c.id NOT LIKE "VariantOnTranscript/%" GROUP BY c.id ' . // Exclude the VOT columns from the normal set, we'll load them below.
Ivo Fokkema's avatar
Ivo Fokkema committed
86
                      'UNION ' .
87
                      '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 = ? ' .
88
                      ($_AUTH['level'] >= $_SETT['user_level_settings']['see_nonpublic_data']? '' : 'AND sc.public_view = 1 ')) .
Ivo Fokkema's avatar
Ivo Fokkema committed
89
                    'ORDER BY col_order';
90 91 92 93 94 95 96 97 98 99
        if (LOVD_plus) {
            // In LOVD_plus, the shared cols table is empty and the public_view field is used to set if a custom column will be displayed in a VL or not.
            // So, in LOVD_plus we need to check for ALL USERS if a custom column has public_view flag turned on or not.
            $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, c.public_view FROM ' . TABLE_ACTIVE_COLS . ' AS ac INNER JOIN ' . TABLE_COLS . ' AS c ON (c.id = ac.colid) ' .
                    'WHERE c.public_view = 1 AND (c.id LIKE ?' . str_repeat(' OR c.id LIKE ?', count($aObjects)-1) . ') ' .
                    'GROUP BY c.id ' .
                    'ORDER BY col_order';
            // And then we don't need this.
            $sGene = '';
        }
Ivo Fokkema's avatar
Ivo Fokkema committed
100 101 102 103 104 105
        $aSQL = array();
        foreach ($aObjects as $sObject) {
            $aSQL[] = $sObject . '/%';
        }
        if ($sGene) {
            $aSQL[] = $sGene;
106 107 108
        }
        if ($sOtherID) {
            $this->nID = $sOtherID; // We need the AJAX script to have the same restrictions!!!
Ivo Fokkema's avatar
Ivo Fokkema committed
109
        }
110

Ivo Fokkema's avatar
Ivo Fokkema committed
111
        // 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).
112
        $_DB->query('SET group_concat_max_len = 200000');
Ivo Fokkema's avatar
Ivo Fokkema committed
113 114 115
        $q = $_DB->query($sSQL, $aSQL);
        while ($z = $q->fetchAssoc()) {
            $z['custom_links'] = array();
116
            $z['form_type'] = explode('|', $z['form_type']);
117
            $z['select_options'] = explode("\r\n", $z['select_options']); // What do we use this for?
118 119 120 121 122 123
            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();
            }
124
            $this->aColumns[$z['id']] = $z;
Ivo Fokkema's avatar
Ivo Fokkema committed
125
        }
126 127 128
        if ($_AUTH) {
            $_AUTH['allowed_to_view'] = array_merge($_AUTH['curates'], $_AUTH['collaborates']);
        }
Ivo Fokkema's avatar
Ivo Fokkema committed
129

130 131 132
        // Boolean indicating whether row_id is already in SELECT statement somewhere.
        $bSetRowID = false;
        $aSQL = $this->aSQLViewList;
Ivo Fokkema's avatar
Ivo Fokkema committed
133

134 135 136 137
        // 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))) {
Ivo Fokkema's avatar
Ivo Fokkema committed
138
            // Use "vog.id:vot.transcriptid" as row_id, fall back to "vog.id" if there is no VOT entry.
139
            $aSQL['SELECT'] = 'CONCAT(CAST(MIN(vog.id) AS UNSIGNED), IFNULL(CONCAT(":", CAST(MIN(vot.transcriptid) AS UNSIGNED)), "")) AS row_id';
140
            $bSetRowID = true;
141
        } elseif (in_array('Transcript', $aObjects)) {
142
            $aSQL['SELECT'] = 't.id AS row_id';
143 144
            $bSetRowID = true;
        }
145

146 147 148
        // Loop requested data types, and keep columns in order indicated by request.
        foreach ($aObjects as $nKey => $sObject) {
            switch ($sObject) {
149
                case 'Gene':
150
                    if (!$bSetRowID) {
151
                        $aSQL['SELECT'] .= (!$aSQL['SELECT']? '' : ', ') . 'g.id AS row_id';
152 153
                        $bSetRowID = true;
                    }
154 155 156 157 158 159 160
                    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;

161
                case 'Transcript':
162
                    $aSQL['SELECT'] .= (!$aSQL['SELECT']? '' : ', ') . 't.id AS tid, ' .
163
                        't.geneid, t.name, t.id_ncbi, t.id_protein_ncbi';
164
                    if (!$bSetRowID) {
165
                        $aSQL['SELECT'] .= ', t.id AS row_id';
166 167
                        $bSetRowID = true;
                    }
168 169 170
                    if (!$aSQL['FROM']) {
                        // First data table in query.
                        $aSQL['FROM'] = TABLE_TRANSCRIPTS . ' AS t';
171 172 173
                        // MAX(id) gets nowhere nearly the correct number of results when data has been removed or archived, but this number
                        //  anyway doesn't need to be accurate at all, and a COUNT(*) can be too slow on large systems running InnoDB.
                        $this->nCount = $_DB->query('SELECT MAX(id) FROM ' . TABLE_TRANSCRIPTS)->fetchColumn();
174
                    } else {
175 176
                        $aSQL['FROM'] .= ' INNER JOIN ' . TABLE_TRANSCRIPTS . ' AS t ON (';
                        $nKeyG   = array_search('Gene', $aObjects);
177
                        $nKeyVOT = array_search('VariantOnTranscript', $aObjects);
178 179 180
                        if ($nKeyG !== false && $nKeyG < $nKey) {
                            // Earlier, Gene was used, join to that.
                            $aSQL['FROM'] .= 'g.id = t.geneid)';
181 182
                            // A view with gene info and transcript info will be grouped on the transcripts.
                            $aSQL['GROUP_BY'] = 't.id';
183
                        } elseif ($nKeyVOT !== false && $nKeyVOT < $nKey) {
184 185 186
                            // Earlier, VOT was used, join to that.
                            $aSQL['FROM'] .= 'vot.transcriptid = t.id)';
                        }
Ivo Fokkema's avatar
Ivo Fokkema committed
187
                        // We have no fallback, so we'll easily detect an error if we messed up somewhere.
188 189 190
                    }
                    break;

191 192 193 194 195 196 197
                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.
198
                        if ($nPosStart && $nPosEnd) {
199
                            // 2014-08-11; 3.0-12; Transcripts on the reverse strand did not display the correctly calculated distance.
200
                            $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';
201 202 203
                        } else {
                            $aSQL['SELECT'] .= (!$aSQL['SELECT']? '' : ', ') . '"?" AS distance_to_var';
                        }
204 205 206
                    }
                    break;

207
                case 'VariantOnGenome':
208
                    $bLoadVOGEffect = (LOVD_plus || !$nKey); // LOVD+ always needs the VOG effect. For LOVD, show vog_effect when it's the first table in the list of objects.
209
                    $nKeyVOTUnique = array_search('VariantOnTranscriptUnique', $aObjects);
210 211 212 213
                    if ($nKeyVOTUnique === false) {
                        // Not viewing the unique variants view.
                        $aSQL['SELECT'] .= (!$aSQL['SELECT']? '' : ', ') .
                            'vog.id AS vogid, vog.chromosome, a.name AS allele_' .
214
                            (!$bLoadVOGEffect? '' : ', eg.name AS vog_effect') .
215 216 217
                            (in_array('Individual', $aObjects)? '' : ', uo.name AS owned_by_, CONCAT_WS(";", uo.id, uo.name, uo.email, uo.institute, uo.department, IFNULL(uo.countryid, "")) AS _owner') .
                            ', dsg.id AS var_statusid, dsg.name AS var_status';
                    }
218
                    if (!$bSetRowID) {
219
                        $aSQL['SELECT'] .= ', vog.id AS row_id';
220 221
                        $bSetRowID = true;
                    }
222 223 224
                    if (!$aSQL['FROM']) {
                        // First data table in query.
                        $aSQL['FROM'] = TABLE_VARIANTS . ' AS vog';
225 226 227
                        // MAX(id) gets nowhere nearly the correct number of results when data has been removed or archived, but this number
                        //  anyway doesn't need to be accurate at all, and a COUNT(*) can be too slow on large systems running InnoDB.
                        $this->nCount = $_DB->query('SELECT MAX(id) FROM ' . TABLE_VARIANTS)->fetchColumn();
Ivo Fokkema's avatar
Ivo Fokkema committed
228
                        $aSQL['GROUP_BY'] = 'vog.id'; // Necessary for GROUP_CONCAT(), such as in Screening.
229
                        $aSQL['ORDER_BY'] = 'vog.chromosome ASC, vog.position_g_start';
230
                    } elseif ($nKeyVOTUnique !== false && $nKeyVOTUnique < $nKey) {
Ivo Fokkema's avatar
Ivo Fokkema committed
231
                        // For the unique variant view a GROUP_CONCAT must be done for the variantOnGenome fields.
232
                        foreach ($this->getCustomColsForCategory('VariantOnGenome') as $sCol => $aCol) {
233 234 235 236
                            // 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).
237
                            $aSQL['SELECT'] .= ', GROUP_CONCAT(DISTINCT NULLIF(`' . $sCol . '`, "") SEPARATOR ";;") AS `' . $sCol . '`';
238
                        }
239
                        $aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_VARIANTS . ' AS vog ON (vot.id = vog.id)';
240
                    } else {
241 242
                        // 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.
243
                        $aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_VARIANTS . ' AS vog FORCE INDEX FOR JOIN (PRIMARY) ON (';
244 245 246 247 248
                        $nKeyVOT = array_search('VariantOnTranscript', $aObjects);
                        if ($nKeyVOT !== false && $nKeyVOT < $nKey) {
                            // Earlier, VOT was used, join to that.
                            $aSQL['FROM'] .= 'vot.id = vog.id)';
                        }
Ivo Fokkema's avatar
Ivo Fokkema committed
249 250
                        // We have no fallback, so we'll easily detect an error if we messed up somewhere.
                    }
mkroon's avatar
mkroon committed
251 252 253 254 255

                    // Add any missing custom cols.
                    if (($sCustomCols = $this->getCustomColQuery($sObject, $aSQL['SELECT'])) != '') {
                        $aSQL['SELECT'] .= ', ' . $sCustomCols;
                    }
256
                    $aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_ALLELES . ' AS a ON (vog.allele = a.id)';
257
                    if ($bLoadVOGEffect) {
258 259
                        $aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_EFFECT . ' AS eg ON (vog.effectid = eg.id)';
                    }
260 261 262
                    if (!in_array('Individual', $aObjects)) {
                        $aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_USERS . ' AS uo ON (vog.owned_by = uo.id)';
                    }
263
                    $aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_DATA_STATUS . ' AS dsg ON (vog.statusid = dsg.id)';
264 265
                    // If user level not high enough, hide lines with hidden variants!
                    if ($_AUTH['level'] < $_SETT['user_level_settings']['see_nonpublic_data']) {
266
                        // Construct list of user IDs for current user and users who share access with him.
mkroon's avatar
mkroon committed
267
                        $aOwnerIDs = array_merge(array($_AUTH['id']), lovd_getColleagues(COLLEAGUE_ALL));
268 269 270
                        $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 . ')') . ')';
271 272 273 274
                    }
                    break;

                case 'VariantOnTranscript':
275
                    $bLoadVOTEffect = (!LOVD_plus); // LOVD+ never uses this.
276
                    $nKeyVOG = array_search('VariantOnGenome', $aObjects);
277
                    $nKeyT   = array_search('Transcript', $aObjects);
278
                    if ($nKeyVOG === false || $nKeyVOG > $nKey) {
279 280
                        $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' .
                            (!$bLoadVOTEffect? '' : ', et.name as vot_effect');
281
                    }
282
                    if (!$bSetRowID) {
283
                        $aSQL['SELECT'] .= ', vot.id AS row_id';
284 285
                        $bSetRowID = true;
                    }
286 287 288
                    if (!$aSQL['FROM']) {
                        // First data table in query.
                        $aSQL['FROM'] = TABLE_VARIANTS_ON_TRANSCRIPTS . ' AS vot';
289 290 291
                        // MAX(id) on the VOT table gets nowhere nearly the correct number of results, but this number
                        //  anyway doesn't need to be accurate at all, and a COUNT(*) can be too slow on large systems running InnoDB.
                        $this->nCount = $_DB->query('SELECT MAX(id) FROM ' . TABLE_VARIANTS_ON_TRANSCRIPTS)->fetchColumn();
Ivo Fokkema's avatar
Ivo Fokkema committed
292
                        $aSQL['GROUP_BY'] = 'vot.id'; // Necessary for GROUP_CONCAT(), such as in Screening.
293 294 295 296 297
                    } 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?
298
                        $sGCOrderBy = 't.geneid, t.id_ncbi';
299
                        foreach ($this->getCustomColsForCategory('VariantOnTranscript') as $sCol => $aCol) {
300
                            $aSQL['SELECT'] .= ', GROUP_CONCAT(DISTINCT ' . ($sCol != 'VariantOnTranscript/DNA'? '`' . $sCol . '`' : 'CONCAT(t.id_ncbi, ":", `' . $sCol . '`)') . ' ORDER BY ' . $sGCOrderBy . ' SEPARATOR ", ") AS `' . $sCol . '`';
301
                        }
302 303 304
                        // 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) {
305
                            $aSQL['SELECT'] .= ', GROUP_CONCAT(DISTINCT t.geneid ORDER BY ' . $sGCOrderBy . ' SEPARATOR ", ") AS genes';
306
                        }
307
                        // 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.
308
                        $aSQL['SELECT'] .= ', GROUP_CONCAT(DISTINCT t.geneid SEPARATOR ";") AS _geneid';
309
                        $aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' AS vot ON (';
310 311
                        // Earlier, VOG was used, join to that.
                        $aSQL['FROM'] .= 'vog.id = vot.id)';
312 313 314 315 316 317 318 319
                        $aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_TRANSCRIPTS . ' AS t ON (vot.transcriptid = t.id)';
                    } elseif ($nKeyT !== false && $nKeyT < $nKey) {
                        // Earlier, T was used, join to that.
                        // If we're showing transcripts and VOTs in one viewList, we'd only want to see the transcripts that HAVE variants.
                        $aSQL['FROM'] .= ' INNER JOIN ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' AS vot ON (t.id = vot.transcriptid)';
                        // Then also make sure we group on the VOT's ID, unless we're already grouping on something.
                        if (!$aSQL['GROUP_BY']) {
                            $aSQL['GROUP_BY'] = 'vot.transcriptid, vot.id';
320
                        }
Ivo Fokkema's avatar
Ivo Fokkema committed
321
                    }
322
                    // We have no fallback, so it won't join if we messed up somewhere!
mkroon's avatar
mkroon committed
323 324 325 326 327

                    // Add any missing custom columns.
                    if (($sCustomCols = $this->getCustomColQuery($sObject, $aSQL['SELECT'])) != '') {
                        $aSQL['SELECT'] .= ', ' . $sCustomCols;
                    }
328 329 330
                    if ($bLoadVOTEffect) {
                        $aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_EFFECT . ' AS et ON (vot.effectid = et.id)';
                    }
Ivo Fokkema's avatar
Ivo Fokkema committed
331 332
                    break;

333
                case 'VariantOnTranscriptUnique':
334
                    $aSQL['SELECT'] .= (!$aSQL['SELECT']? '' : ', ') . 'MIN(vot.id) AS votid, vot.transcriptid, ' . // To ensure other table's id columns don't interfere.
335
                                      'vot.position_c_start, vot.position_c_start_intron, ' .
336
                                      'vot.position_c_end, vot.position_c_end_intron';
Ivo Fokkema's avatar
Ivo Fokkema committed
337 338
                    // 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.
339 340 341
                    $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_' .
342
                                       ', GROUP_CONCAT(DISTINCT CONCAT_WS(";", uo.id, uo.name, uo.email, uo.institute, IF(IFNULL(uo.department, "") = "", "-", uo.department), IF(IFNULL(uo.countryid, "") = "", "-", uo.countryid)) SEPARATOR ";;") AS __owner';
Ivo Fokkema's avatar
Ivo Fokkema committed
343 344
                    // 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.
345 346
                    $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';
347
                    if (!$bSetRowID) {
mkroon's avatar
mkroon committed
348
                        $aSQL['SELECT'] .= ', MIN(vot.id) AS row_id';
349 350
                        $bSetRowID = true;
                    }
351 352
                    $aSQL['FROM'] = TABLE_VARIANTS_ON_TRANSCRIPTS . ' AS vot';

353 354 355 356
                    // $this->nCount = $_DB->query('SELECT COUNT(DISTINCT `VariantOnTranscript/DNA`) FROM ' . TABLE_VARIANTS_ON_TRANSCRIPTS)->fetchColumn();
                    // No longer calculate the number of results; this number anyway doesn't need to be correct at all,
                    //  and this query is too slow on large systems running InnoDB.
                    $this->nCount = $_DB->query('SELECT 1 FROM ' . TABLE_VARIANTS_ON_TRANSCRIPTS . ' LIMIT 1')->fetchColumn();
357

Ivo Fokkema's avatar
Ivo Fokkema committed
358
                    $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.
359

360
                    foreach ($this->getCustomColsForCategory('VariantOnTranscript') as $sCol => $aCol) {
361 362 363 364
                        // 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).
365
                        $aSQL['SELECT'] .= ', GROUP_CONCAT(DISTINCT NULLIF(`' . $sCol . '`, "") SEPARATOR ";;") AS `' . $sCol . '`';
366 367 368 369
                    }
                    $aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_EFFECT . ' AS et ON (vot.effectid = et.id)';
                    break;

Ivo Fokkema's avatar
Ivo Fokkema committed
370
                case 'Screening':
371
                    if (!$bSetRowID) {
372
                        $aSQL['SELECT'] .= (!$aSQL['SELECT']? '' : ', ') . 's.id AS row_id';
373 374
                        $bSetRowID = true;
                    }
Ivo Fokkema's avatar
Ivo Fokkema committed
375 376
                    if (!$aSQL['FROM']) {
                        // First data table in query.
377
                        $aSQL['SELECT'] .= (!$aSQL['SELECT']? '' : ', ') . 's.id AS sid';
Ivo Fokkema's avatar
Ivo Fokkema committed
378
                        $aSQL['FROM'] = TABLE_SCREENINGS . ' AS s';
379 380 381
                        // MAX(id) gets nowhere nearly the correct number of results when data has been removed or archived, but this number
                        //  anyway doesn't need to be accurate at all, and a COUNT(*) can be too slow on large systems running InnoDB.
                        $this->nCount = $_DB->query('SELECT MAX(id) FROM ' . TABLE_SCREENINGS)->fetchColumn();
Ivo Fokkema's avatar
Ivo Fokkema committed
382 383 384
                        $aSQL['ORDER_BY'] = 's.id';
                    } else {
                        // SELECT will be different: we will GROUP_CONCAT the whole lot, per column.
385 386
                        $sGCOrderBy = (isset($this->aColumns['Screening/Date'])? '`Screening/Date`' : 'id');
                        foreach ($this->getCustomColsForCategory('Screening') as $sCol => $aCol) {
387
                            $aSQL['SELECT'] .= (!$aSQL['SELECT']? '' : ', ') . 'GROUP_CONCAT(DISTINCT `' . $sCol . '` ORDER BY s.' . $sGCOrderBy . ' SEPARATOR ";") AS `' . $sCol . '`';
Ivo Fokkema's avatar
Ivo Fokkema committed
388
                        }
mkroon's avatar
mkroon committed
389 390 391 392 393
                        // Add any missing custom columns.
                        if (($sCustomCols = $this->getCustomColQuery($sObject, $aSQL['SELECT'])) != '') {
                            $aSQL['SELECT'] .= ', ' . $sCustomCols;
                        }

Ivo Fokkema's avatar
Ivo Fokkema committed
394 395 396 397 398
                        $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.
399
                            $aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_SCR2VAR . ' AS s2v ON (vog.id = s2v.variantid) LEFT OUTER JOIN ' . TABLE_SCREENINGS . ' AS s ON (s2v.screeningid = s.id)';
Ivo Fokkema's avatar
Ivo Fokkema committed
400 401
                        } elseif ($nKeyVOT !== false && $nKeyVOT < $nKey) {
                            // Earlier, VOT was used, join to that.
402
                            $aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_SCR2VAR . ' AS s2v ON (vot.id = s2v.variantid) LEFT OUTER JOIN ' . TABLE_SCREENINGS . ' AS s ON (s2v.screeningid = s.id)';
Ivo Fokkema's avatar
Ivo Fokkema committed
403 404
                        } elseif ($nKeyI !== false && $nKeyI < $nKey) {
                            // Earlier, I was used, join to that.
405
                            $aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_SCREENINGS . ' AS s ON (i.id = s.individualid)';
Ivo Fokkema's avatar
Ivo Fokkema committed
406 407 408 409 410
                        }
                        // We have no fallback, so it won't join if we messed up somewhere!
                    }
                    break;

411
                case 'Scr2Var':
412
                    if (!$bSetRowID) {
413
                        $aSQL['SELECT'] .= (!$aSQL['SELECT']? '' : ', ') . 's2v.id AS row_id';
414 415
                        $bSetRowID = true;
                    }
416 417 418 419 420 421
                    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.
422
                            $aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_SCR2VAR . ' AS s2v ON (vog.id = s2v.variantid)';
423 424 425 426
                            // 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().
427 428
                        } elseif ($nKeyVOT !== false && $nKeyVOT < $nKey) {
                            // Earlier, VOT was used, join to that.
429
                            $aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_SCR2VAR . ' AS s2v ON (vot.id = s2v.variantid)';
430 431 432 433 434
                        }
                        // We have no fallback, so it won't join if we messed up somewhere!
                    }
                    break;

Ivo Fokkema's avatar
Ivo Fokkema committed
435
                case 'Individual':
436
                    $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';
437
                    if (!$bSetRowID) {
438
                        $aSQL['SELECT'] .= ', i.id AS row_id';
439 440
                        $bSetRowID = true;
                    }
mkroon's avatar
mkroon committed
441 442 443 444
                    // Add any missing custom columns.
                    if (($sCustomCols = $this->getCustomColQuery($sObject, $aSQL['SELECT'])) != '') {
                        $aSQL['SELECT'] .= ', ' . $sCustomCols;
                    }
Ivo Fokkema's avatar
Ivo Fokkema committed
445 446 447
                    if (!$aSQL['FROM']) {
                        // First data table in query.
                        $aSQL['FROM'] = TABLE_INDIVIDUALS . ' AS i';
448 449 450
                        // MAX(id) gets nowhere nearly the correct number of results when data has been removed or archived, but this number
                        //  anyway doesn't need to be accurate at all, and a COUNT(*) can be too slow on large systems running InnoDB.
                        $this->nCount = $_DB->query('SELECT MAX(id) FROM ' . TABLE_INDIVIDUALS)->fetchColumn();
Ivo Fokkema's avatar
Ivo Fokkema committed
451 452 453 454 455 456 457 458 459 460 461 462 463
                        $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.
464
                                $aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_SCR2VAR . ' AS s2v ON (vog.id = s2v.variantid) LEFT OUTER JOIN ' . TABLE_SCREENINGS . ' AS s ON (s2v.screeningid = s.id)';
Ivo Fokkema's avatar
Ivo Fokkema committed
465 466
                            } elseif ($nKeyVOT !== false && $nKeyVOT < $nKey) {
                                // Earlier, VOT was used, join to that.
467
                                $aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_SCR2VAR . ' AS s2v ON (vot.id = s2v.variantid) LEFT OUTER JOIN ' . TABLE_SCREENINGS . ' AS s ON (s2v.screeningid = s.id)';
Ivo Fokkema's avatar
Ivo Fokkema committed
468 469 470
                            }
                            // We have no fallback, so it won't join if we messed up somewhere!
                        }
471
                        $aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_INDIVIDUALS . ' AS i ON (s.individualid = i.id';
472 473
                        // If user level not high enough, hide hidden individuals (from the join, don't hide the line)!
                        if ($_AUTH['level'] < $_SETT['user_level_settings']['see_nonpublic_data']) {
474
                            // Construct list of user IDs for current user and users who share access with him.
mkroon's avatar
mkroon committed
475
                            $aOwnerIDs = array_merge(array($_AUTH['id']), lovd_getColleagues(COLLEAGUE_ALL));
476 477 478
                            $sOwnerIDsSQL = join(', ', $aOwnerIDs);

                            $aSQL['FROM'] .= ' AND (i.statusid >= ' . STATUS_MARKED . (!$_AUTH? '' : ' OR i.created_by = "' . $_AUTH['id'] . '" OR i.owned_by IN (' . $sOwnerIDsSQL . ')') . ')';
Ivo Fokkema's avatar
Ivo Fokkema committed
479 480
                        }
                        $aSQL['FROM'] .= ')';
481
                    }
482 483 484
                    $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)';
Ivo Fokkema's avatar
Ivo Fokkema committed
485
                    $aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_USERS . ' AS uo ON (i.owned_by = uo.id)';
486
                    $aSQL['FROM'] .= ' LEFT OUTER JOIN ' . TABLE_DATA_STATUS . ' AS dsi ON (i.statusid = dsi.id)';
487 488
                    break;
            }
Ivo Fokkema's avatar
Ivo Fokkema committed
489
        }
490

491 492 493 494 495 496 497 498
        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;



499
        if ($this->sObjectID == 'Transcript,VariantOnTranscript,VariantOnGenome') {
500 501 502 503 504 505
            // 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');
        }



506 507 508 509 510 511
        // The effectid legend is often repeated.
        $aLegendVarEffect = array(
            'The variant\'s effect on the protein\'s function, in the format \'R/C\' where R is the value ' . (LOVD_plus? 'initially reported and C is the value finally concluded' : 'reported by the source and C is the value concluded by the curator') . '; values ranging from \'+\' (variant affects function) to \'-\' (does not affect function).',
            'The variant\'s effect on the protein\'s function, in the format \'R/C\' where R is the value ' . (LOVD_plus? 'initially reported and C is the value finally concluded' : 'reported by the source and C is the value concluded by the curator') . '; \'+\' indicating the variant affects function, \'+?\' probably affects function, \'+*\' affects function, not associated with individual\'s disease phenotype, \'#\' affects function, not associated with any known disease phenotype, \'-\' does not affect function, \'-?\' probably does not affect function, \'?\' effect unknown, \'.\' effect not classified.',
        );

512 513 514
        // Now build $this->aColumnsViewList, from the order given by $aObjects and TABLE_COLS.col_order.
        foreach ($aObjects as $nKey => $sObject) {
            switch ($sObject) {
515 516 517 518 519 520 521 522 523 524 525
                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;

526
                case 'Transcript':
Ivo Fokkema's avatar
Ivo Fokkema committed
527
                    $sPrefix = 't.';
528 529 530
                    // The fixed columns.
                    $this->aColumnsViewList = array_merge($this->aColumnsViewList,
                         array(
531
                             'tid' => array(
532 533 534 535 536 537 538 539 540 541 542 543 544 545
                                 '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)),
546 547 548 549 550
                              ));
                    if (!$this->sSortDefault) {
                        // First data table in view.
                        $this->sSortDefault = 'geneid';
                    }
551 552 553 554 555 556 557 558 559 560 561 562
                    // 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(
563
                                 'view' => array('Distance (bp)', 90, 'style="text-align : right;"'),
564 565 566 567
                                 'db'   => array('distance_to_var', 'ASC', false)),
                              ));
                    // Always force default sorting...
                    $this->sSortDefault = 'distance_to_var';
568 569 570
                    break;

                case 'VariantOnGenome':
Ivo Fokkema's avatar
Ivo Fokkema committed
571
                    $sPrefix = 'vog.';
572 573 574
                    // The fixed columns.
                    $this->aColumnsViewList = array_merge($this->aColumnsViewList,
                         array(
575
                                // NOTE: there are more columns defined a little further below.
576 577
                                'chromosome' => array(
                                        'view' => array('Chr', 50),
578
                                        'db'   => array('vog.chromosome', 'ASC', true)),
579 580
                                'allele_' => array(
                                        'view' => array('Allele', 120),
581 582
                                        'db'   => array('a.name', 'ASC', true),
                                        'legend' => array('On which allele is the variant located? Does not necessarily imply inheritance!',
Ivo Fokkema's avatar
Ivo Fokkema committed
583
                                                          '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.')),
584 585
                                'vog_effect' => array(
                                        'view' => array('Effect', 70),
586
                                        'db'   => array('eg.name', 'ASC', true),
587 588 589
                                        'legend' => array(
                                            str_replace('the protein', 'a protein', $aLegendVarEffect[0]),
                                            str_replace('the protein', 'a protein', $aLegendVarEffect[1]))),
590
                              ));
591
                    if (empty($bLoadVOGEffect)) {
592 593
                        unset($this->aColumnsViewList['vog_effect']);
                    }
594 595
                    if (!$this->sSortDefault) {
                        // First data table in view.
Ivo Fokkema's avatar
Ivo Fokkema committed
596
                        $this->sSortDefault = 'VariantOnGenome/DNA';
597
                    }
598
                    $this->sRowLink = 'variants/{{zData_vogid}}#{{zData_transcriptid}}';
599 600 601
                    break;

                case 'VariantOnTranscript':
Ivo Fokkema's avatar
Ivo Fokkema committed
602
                    $sPrefix = 'vot.';
603 604 605 606
                    // The fixed columns.
                    $this->aColumnsViewList = array_merge($this->aColumnsViewList,
                         array(
                                'transcriptid' => array(
607
                                        'view' => false,
608
                                        'db'   => array('vot.transcriptid', 'ASC', true)),
609 610 611 612 613 614 615 616 617 618 619 620 621 622
                                '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,
Ivo Fokkema's avatar
Ivo Fokkema committed
623
                                        'db'   => array('TRIM(BOTH "?" FROM TRIM(LEADING "c." FROM REPLACE(REPLACE(`VariantOnTranscript/DNA`, ")", ""), "(", "")))', 'ASC', 'TEXT')),
624 625 626
                                'genes' => array(
                                        'view' => array('Gene', 100),
                                        'db'   => array('t.geneid', 'ASC', true)),
627 628
                                'vot_effect' => array(
                                        'view' => array('Effect', 70),
629
                                        'db'   => array('et.name', 'ASC', true),
630
                                        'legend' => $aLegendVarEffect),
631
                              ));
632 633 634 635 636
                    // 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']);
                    }
637 638
                    if (!empty($bLoadVOGEffect) || empty($bLoadVOTEffect)) {
                        // Show vog_effect instead of vot_effect when requested.
639 640
                        unset($this->aColumnsViewList['vot_effect']);
                    }
641 642
                    if (!$this->sSortDefault) {
                        // First data table in view.
Ivo Fokkema's avatar
Ivo Fokkema committed
643 644 645 646
                        $this->sSortDefault = 'VariantOnTranscript/DNA';
                    }
                    break;

647 648 649 650 651 652 653 654 655 656 657
                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),
658
                                        'legend' => $aLegendVarEffect),
659
                                'vot_reported' => array(
660
                                        'view' => array('Reported', 70, 'style="text-align : right;"'),
661
                                        'db'   => array('vot_reported', 'ASC', 'INT_UNSIGNED'),
Ivo Fokkema's avatar
Ivo Fokkema committed
662 663
                                        'legend' => array('The number of times this variant has been reported.',
                                                          'The number of times this variant has been reported in the database.')),
664 665 666 667 668 669 670
                                ));
                    if (!$this->sSortDefault) {
                        // First data table in view.
                        $this->sSortDefault = 'VariantOnTranscript/DNA';
                    }
                    break;

Ivo Fokkema's avatar
Ivo Fokkema committed
671 672 673 674 675 676 677 678
                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(
679
                                    'sid' => array(
680
                                            'view' => array('Screening ID', 110, 'style="text-align : right;"'),
681
                                            'db'   => array('s.id', 'ASC', true)),
Ivo Fokkema's avatar
Ivo Fokkema committed
682 683 684 685 686
                                  ));
                        $this->sSortDefault = 'id';
                    }
                    break;

687 688 689 690 691 692 693 694 695 696 697
                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;

Ivo Fokkema's avatar
Ivo Fokkema committed
698 699
                case 'Individual':
                    $sPrefix = 'i.';
700 701 702
                    // The fixed columns.
                    $this->aColumnsViewList = array_merge($this->aColumnsViewList,
                         array(
703 704
                             // NOTE: there are more columns defined a little further below.
                             'diseases_' => array(
705
                                            'view' => array('Disease', 175),
706
                                            'db'   => array('diseases_', 'ASC', true)),
707
                              ));
Ivo Fokkema's avatar
Ivo Fokkema committed
708 709
                    if (!$this->sSortDefault) {
                        $this->sSortDefault = 'id';
710 711 712 713
                    }
                    break;
            }

714 715


716
            // The custom columns.
717 718
            foreach ($this->aColumns as $sColID => $aCol) {
                if (strpos($sColID, str_replace('Unique', '', $sObject) . '/') === 0) {
719 720
                    $bAlignRight = preg_match('/^(DEC|FLOAT|(TINY|SMALL|MEDIUM|BIG)?INT)/', $aCol['mysql_type']);

721 722
                    $this->aColumnsViewList[$sColID] =
                         array(
723
                                'view' => array($aCol['head_column'], $aCol['width'], ($bAlignRight? ' align="right"' : '')),
Ivo Fokkema's avatar
Ivo Fokkema committed
724
                                'db'   => array($sPrefix . '`' . $aCol['id'] . '`', 'ASC', lovd_getColumnType('', $aCol['mysql_type'])),
725
                                'legend' => array($aCol['description_legend_short'], $aCol['description_legend_full']),
726
                                'allow_find_replace' => true,
727 728 729
                              );
                }
            }
730 731 732 733 734 735 736 737 738 739



            // 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(
                            // NOTE: there are more columns defined a little further up.
740 741 742
                            'owned_by_' => array(
                                'view' => array('Owner', 160),
                                'db'   => array('uo.name', 'ASC', true)),
743 744 745
                            'owner_countryid' => array(
                                'view' => false,
                                'db'   => array('uo.countryid', 'ASC', true)),
746 747 748 749
                            'var_status' => array(
                                'view' => array('Var. status', 70),
                                'db'   => array('dsg.name', false, true)),
                        ));
750 751 752
                    if (in_array('Individual', $aObjects)) {
                        unset($this->aColumnsViewList['owned_by_']);
                    }
753
                    if ($_AUTH['level'] < $_SETT['user_level_settings']['see_nonpublic_data']) {
754 755 756
                        // 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']);
                    }
757 758
                    // 2015-10-09; 3.0-14; Add genome build name to the VOG/DNA field.
                    $this->aColumnsViewList['VariantOnGenome/DNA']['view'][0] .= ' (' . $_CONF['refseq_build'] . ')';
759 760 761 762 763 764 765
                    break;

                case 'Individual':
                    // More fixed columns.
                    $this->aColumnsViewList = array_merge($this->aColumnsViewList,
                        array(
                            // NOTE: there are more columns defined a little further up.
766
                            'panel_size' => array(
767
                                'view' => array('Panel size', 70, 'style="text-align : right;"'),
768
                                'db'   => array('i.panel_size', 'DESC', true)),
769 770 771
                            'owned_by_' => array(
                                'view' => array('Owner', 160),
                                'db'   => array('uo.name', 'ASC', true)),
772 773 774 775
                            'ind_status' => array(
                                'view' => array('Ind. status', 70),
                                'db'   => array('dsi.name', false, true)),
                        ));
776
                    if ($_AUTH['level'] < $_SETT['user_level_settings']['see_nonpublic_data']) {
777 778 779 780 781
                        // 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;
            }
782 783 784 785
        }



786
        // Gather the custom link information. It's just easier to load all custom links, instead of writing code that checks for the appropriate objects.
787 788
        $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) {
789
            $aLink['regexp_pattern'] = '/' . str_replace(array('{', '}'), array('\{', '\}'), preg_replace('/\[\d\]/', '([^:]*)', $aLink['pattern_text'])) . '/';
790 791 792
            $aLink['replace_text'] = preg_replace('/\[(\d)\]/', '\$$1', $aLink['replace_text']);
            $aCols = explode(';', $aLink['colids']);
            foreach ($aCols as $sColID) {
793 794
                if (isset($this->aColumns[$sColID])) {
                    $this->aColumns[$sColID]['custom_links'][] = $aLink['id'];
795
                }
796
            }
797
            $this->aCustomLinks[$aLink['id']] = $aLink;
798 799 800
        }

        // Not including parent constructor, because these table settings will make it freak out.
801 802
        //parent::__construct();
        // Therefore, row links need to be created by us (which is done above).
803 804 805 806 807 808
    }





809 810 811 812 813 814 815 816 817 818 819 820 821 822 823
    private function getCustomColsForCategory ($sCategory)
    {
        $nCatLength = strlen($sCategory);
        $aOut = array();
        foreach ($this->aColumns as $sCol => $aCol) {
            if (substr($sCol, 0, $nCatLength) == $sCategory) {
                $aOut[$sCol] = $aCol;
            }
        }
        return $aOut;
    }




mkroon's avatar
mkroon committed
824 825 826 827 828 829
    private function getCustomColQuery ($sCategory, $sSelectQuery='')
    {
        // Return string with custom column names for object type $sCategory
        // that can be used in the SELECT listing of an SQL query.
        // $sSelectQuery can be given as an existing SELECT clause to avoid
        // duplication of column names are already listed.
830 831 832
        $sSelectFields = array();
        foreach (array_keys($this->getCustomColsForCategory($sCategory)) as $sName) {
            if (strpos($sSelectQuery, $sName) === false) {
833
                $sSelectFields[] = '`' . $sName . '`';
834
            }
mkroon's avatar
mkroon committed
835
        }
836
        return join(', ', $sSelectFields);
mkroon's avatar
mkroon committed
837 838 839 840 841
    }




842

843
    function prepareData ($zData = '', $sView = 'list', $sViewListID = '')
844 845
    {
        // Prepares the data by "enriching" the variable received with links, pictures, etc.
846
        global $_AUTH, $_SETT;
847 848 849 850

        // Makes sure it's an array and htmlspecialchars() all the values.
        $zData = parent::prepareData($zData, $sView);

851 852
        // Replace rs numbers with dbSNP links.
        if (!empty($zData['VariantOnGenome/dbSNP'])) {
853
            $zData['VariantOnGenome/dbSNP'] = preg_replace('/(rs\d+)/', '<SPAN' . ($sView != 'list'? '' : ' onclick="cancelParentEvent(event);"') . '><A href="https://www.ncbi.nlm.nih.gov/SNP/snp_ref.cgi?rs=' . "$1" . '" target="_blank">' . "$1" . '</A></SPAN>', $zData['VariantOnGenome/dbSNP']);
854
        }
Ivo Fokkema's avatar
Ivo Fokkema committed
855

856
        foreach ($this->aColumns as $sCol => $aCol) {
857
            if (!LOVD_plus && $_AUTH['level'] < LEVEL_MANAGER && !$this->nID && substr($sCol, 0, 19) == 'VariantOnTranscript') {
858
                // Not a special authorized person, no gene selected, VOT column.
859
                // Empty the field if the column is not actually active for the gene(s) of this entry.
860
                // A column that has been disabled for this gene, may still show its value to collaborators and higher.
861
                // For LOVD+, shared columns are no longer shared, and public_view is not an array, so this code won't work.
862 863
                if ((!$_AUTH || !in_array($zData['geneid'], $_AUTH['allowed_to_view'])) && ((is_array($zData['geneid']) && count(array_diff($zData['geneid'], $aCol['public_view']))) || (!is_array($zData['geneid']) && !in_array($zData['geneid'], $aCol['public_view'])))) {
                    $zData[$sCol] = '';
864 865
                }
            }
866 867 868 869 870 871
            if (!empty($aCol['custom_links'])) {
                foreach ($aCol['custom_links'] as $nLink) {
                    $sRegexpPattern = $this->aCustomLinks[$nLink]['regexp_pattern'];
                    $sReplaceText = $this->aCustomLinks[$nLink]['replace_text'];
                    if ($sView == 'list') {
                        $sReplaceText = '<SPAN class="custom_link" onmouseover="lovd_showToolTip(\'' . str_replace('"', '\\\'', $sReplaceText) . '\', this);">' . strip_tags($sReplaceText) . '</SPAN>';
872
                    }
873
                    $zData[$aCol['id']] = preg_replace($sRegexpPattern . 'U', $sReplaceText, $zData[$aCol['id']]);
874 875 876
                }
            }
        }
877

878 879 880 881 882 883 884
        if ($sView == 'list') {
            // "Clean" the GROUP_CONCAT columns for double values.
            foreach ($zData as $sCol => $sVal) {
                if (strpos($sCol, 'Screening/') === 0) {
                    $zData[$sCol] = implode(', ', array_unique(explode(';', $sVal)));
                }
                if (strpos($sViewListID, 'CustomVL_VOTunique') === 0 && (strpos($sCol, 'VariantOnGenome/') === 0 || strpos($sCol, 'VariantOnTranscript/') === 0)) {
Ivo Fokkema's avatar
Ivo Fokkema committed
885
                    // In the GROUP_CONCAT query a double semicolon (;;) is used as a separator, so it can be recognized here.
886 887 888 889 890 891 892
                    $aElements = explode(';;', $sVal);
                    $nElements = count($aElements);
                    $sNewElement = '';
                    $nCount = 0;

                    // VariantOnGenome and VariantOnTranscript columns with more then 200 characters are cut off.
                    // A string is added which states how many more unique items are available.
Ivo Fokkema's avatar
Ivo Fokkema committed
893 894 895
                    foreach ($aElements as $nKey => $sElement) {
                        if ((strlen(strip_tags($sNewElement)) + strlen(strip_tags($sElement))) <= $_SETT['unique_view_max_string_length']) {
                            $sNewElement .= ($sNewElement === ''? '' : ', ') . $sElement;
896 897 898
                            $nCount ++;
                        }
                    }
Ivo Fokkema's avatar
Ivo Fokkema committed
899
                    $nNotPrinted = $nElements - $nCount;
900
                    if ($nNotPrinted > 0) {
Ivo Fokkema's avatar
Ivo Fokkema committed
901
                        $sNewElement .= ($sNewElement === ''? '' : ', ') . '<I>' . $nNotPrinted . ' more item' . ($nNotPrinted == 1? '' : 's') . '</I>';
902 903 904 905 906 907
                    }
                    $zData[$sCol] = $sNewElement;
                }
            }
        }

908 909 910 911
        return $zData;
    }
}
?>