1:   2:   3:   4:   5:   6:   7:   8:   9:  10:  11:  12:  13:  14:  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:  48:  49:  50:  51:  52:  53:  54:  55:  56:  57:  58:  59:  60:  61:  62:  63:  64:  65:  66:  67:  68:  69:  70:  71:  72:  73:  74:  75:  76:  77:  78:  79:  80:  81:  82:  83:  84:  85:  86:  87:  88:  89:  90:  91:  92:  93:  94:  95:  96:  97:  98:  99: 100: 101: 102: 103: 104: 105: 106: 107: 108: 109: 110: 111: 112: 113: 114: 115: 116: 117: 118: 119: 120: 121: 122: 123: 124: 125: 126: 127: 128: 129: 130: 131: 132: 133: 134: 135: 136: 137: 138: 139: 140: 141: 142: 143: 144: 145: 146: 147: 148: 149: 150: 151: 152: 153: 154: 155: 156: 157: 158: 159: 160: 161: 162: 163: 164: 165: 166: 167: 168: 169: 170: 171: 172: 173: 174: 175: 176: 177: 178: 179: 180: 181: 182: 183: 184: 185: 186: 187: 188: 189: 190: 191: 192: 193: 194: 195: 196: 197: 198: 199: 200: 201: 202: 203: 204: 205: 206: 207: 208: 209: 210: 211: 212: 213: 214: 215: 216: 217: 218: 219: 220: 221: 222: 223: 224: 225: 226: 227: 228: 229: 230: 231: 232: 233: 234: 235: 236: 237: 238: 239: 240: 241: 242: 243: 244: 245: 246: 247: 248: 249: 250: 251: 252: 253: 254: 255: 256: 257: 258: 259: 260: 261: 262: 263: 264: 265: 266: 267: 268: 269: 270: 271: 272: 273: 274: 275: 276: 277: 278: 279: 280: 281: 282: 283: 284: 285: 286: 287: 288: 289: 290: 291: 292: 293: 294: 295: 296: 297: 298: 299: 300: 301: 302: 303: 304: 305: 306: 307: 308: 309: 310: 311: 312: 313: 314: 315: 316: 317: 318: 319: 320: 321: 322: 323: 324: 325: 326: 327: 328: 329: 330: 331: 332: 333: 334: 335: 336: 337: 338: 339: 340: 341: 342: 343: 344: 345: 346: 347: 348: 349: 350: 351: 352: 353: 354: 355: 356: 357: 358: 359: 360: 361: 362: 363: 364: 365: 366: 367: 368: 369: 370: 371: 372: 373: 374: 375: 376: 377: 378: 379: 380: 381: 382: 383: 384: 385: 386: 387: 388: 389: 390: 391: 392: 393: 394: 395: 396: 397: 398: 399: 400: 401: 402: 403: 404: 405: 406: 407: 408: 409: 410: 411: 412: 413: 414: 415: 416: 417: 418: 419: 420: 421: 422: 423: 424: 425: 426: 427: 428: 429: 430: 431: 432: 433: 434: 435: 436: 437: 438: 439: 440: 441: 442: 443: 444: 445: 446: 447: 448: 449: 450: 451: 452: 453: 454: 455: 456: 457: 458: 459: 460: 461: 462: 463: 464: 465: 466: 467: 468: 469: 470: 471: 472: 473: 474: 475: 476: 477: 478: 479: 480: 481: 482: 483: 484: 485: 486: 487: 488: 489: 490: 491: 492: 493: 494: 495: 496: 497: 498: 499: 500: 501: 502: 503: 504: 505: 506: 507: 508: 509: 510: 511: 512: 513: 514: 515: 516: 517: 518: 519: 520: 521: 522: 523: 524: 525: 526: 527: 528: 529: 530: 531: 532: 533: 534: 535: 536: 537: 538: 539: 540: 541: 542: 543: 544: 545: 546: 547: 548: 549: 550: 551: 552: 553: 554: 555: 556: 557: 558: 559: 560: 561: 562: 563: 564: 565: 566: 567: 568: 569: 570: 571: 572: 573: 574: 575: 576: 577: 578: 579: 580: 581: 582: 583: 584: 585: 586: 587: 588: 589: 590: 591: 592: 593: 594: 595: 596: 597: 598: 599: 600: 601: 602: 603: 604: 605: 606: 607: 608: 609: 610: 611: 612: 613: 614: 615: 616: 617: 618: 619: 620: 621: 622: 623: 624: 625: 626: 627: 628: 629: 630: 631: 632: 633: 634: 635: 636: 637: 638: 639: 640: 641: 642: 643: 644: 645: 646: 647: 648: 649: 650: 651: 652: 653: 654: 655: 656: 657: 658: 659: 660: 661: 662: 663: 664: 665: 666: 667: 668: 669: 670: 671: 672: 673: 674: 675: 676: 677: 678: 679: 680: 681: 682: 683: 684: 685: 686: 687: 688: 689: 690: 691: 692: 693: 694: 695: 696: 697: 698: 699: 700: 701: 702: 703: 704: 705: 706: 707: 708: 709: 710: 711: 712: 713: 714: 715: 716: 717: 718: 719: 720: 721: 722: 723: 724: 725: 726: 727: 728: 729: 730: 731: 732: 733: 734: 735: 736: 737: 738: 739: 740: 741: 742: 743: 744: 745: 746: 747: 748: 749: 750: 751: 752: 753: 754: 755: 756: 757: 758: 759: 760: 761: 762: 763: 764: 765: 766: 767: 768: 769: 770: 771: 772: 773: 774: 775: 776: 777: 778: 779: 780: 781: 782: 

<?php

namespace DispatchModule\Tools;

/**
 * DbTool
 * - retrieves information from database
 * - used (mainly) by search
 */
class DbTool extends \DispatchModule\Tools\BaseTool implements \DispatchModule\Tools\ToolInterface {

    /**
     * @var \BaseModule\Repository\TestRepository Test repository
     * @Inject
     */
    protected $repository;

    /**
     * @var array array of wanted parameters
     */
    protected $wantedParameters;

    /**
     * @var int offset of results
     */
    protected $offset = 0;

    /**
     * @var int limitation of result count
     */
    protected $limit = 0;

    /**
     * @var string short name of the tool
     */
    public $label;

    /**
     * @var bool if true, only record with longest sequence each accession number is returned
     */
    protected $aggregateByAccession = false;

    /**
     * @var bool indicates whether simple search loading only basic sequence info should be performed
     */
    protected $simpleSearch = false;

    /**
     * Equals rule (can be also used as IN rule, if value is an array)
     * @var string
     */
    const RULE_EQ = 'eq';

    /**
     * Greater than rule
     * @var string
     */
    const RULE_GT = 'gt';

    /**
     * Less than than rule
     * @var string
     */
    const RULE_LT = 'lt';

    /**
     * Like rule
     * @var string
     */
    const RULE_LIKE = 'like';

    /**
     * Prefix like rule
     * @var string
     */
    const RULE_PREFIX = 'prefix';

    /**
     * @var array list of criterias for query
     */
    protected $criteria = array(
        self::RULE_EQ => array(),
        self::RULE_GT => array(),
        self::RULE_LT => array(),
        self::RULE_LIKE => array(),
        self::RULE_PREFIX => array()
    );

    /**
     * Constructor used to save container
     *
     * @param \Nette\DI\Container $container
     * @param ...Object $initialization triplet label, description, wanted parameters; or an array with database tools to union
     */
    public function __construct(\Nette\DI\Container $container, ...$initialization) {
        parent::__construct($container);
        if (count($initialization) === 3) {
            $this->label = $initialization[0];
            $this->description = $initialization[1];
            $this->wantedParameters = $initialization[2];
        } else {
            $this->label = 'Database';
            $this->description = 'Union of DB tools';
            $this->wantedParameters = array(
                'export_accession' => array('hidden', 'Accessions'),
                'start' => array('hidden', 'Start position'),
                'stop' => array('hidden', 'Stop position'),
            );
            if (count($initialization) === 1) {
                foreach ($initialization[0] as $tool) {
                    $this->wantedParameters = array_merge($this->wantedParameters, $tool->getWantedParameters());
                }
            }
        }
    }

    /**
     * Adds search criteria
     *
     * @param string $name
     * @param mixed $value
     * @return boolean
     */
    public function addCriteria($name, $value) {
//        if(is_array($value)) { // arrays are handled in a customized way in each procedure separately
//            $value = implode('', $value);
//        }

        if (is_string($value)) {
            $value = trim($value);
        }
        // check reserved words first (offset, limit, aggregateByAccession)
        if ($this->checkReservedWord($name, $value)) {
            return true;
        }

        // check whether user is not trying to insert invalid data
        if (!$this->requireParameter($name) || ($value == '' && $this->containsAttribute($name . '_array') === false)) {
            return false;
        }

        // add criteria for specified type
        $dbCriteria = $this->getCriteriaParam($name, $value);
        if ($dbCriteria !== false) {
            $this->criteria[$dbCriteria['criteria']][] = array($dbCriteria['key'] => $dbCriteria['value']);
        }

        return true;
    }

    /**
     * Executes query
     *
     * @return ResultSet
     */
    public function execute() {
        $base = $this->chooseBaseTable();
        $result = $this->prepareCriteria($base . '.*, sequences:*, qualities:*, ' . $base . '.start AS dbStartPosition, ' . $base . '.stop AS dbStopPosition, ' . $base . '.start AS startPosition, ' . $base . '.stop AS stopPosition, LENGTH(sequences:sequence) AS regionLength, ' . $base . '.id AS accessionId', $base);
        $result->order($base . '.stop - ' . $base . '.start DESC, ' . $base . '.accession, ' . $base . '.start, ' . $base . '.stop, ' . $base . '.id');

        $return = new \DispatchModule\ResultSet;
        foreach ($result as $res) {
            // create sequence
            $sequence = $this->setResults($res);
            if (!$this->simpleSearch) {
                // load references with authors
                $references = $this->repository->getTable('references')->select('id, title, consortium, submission_date, journal, year, volume, issue, first_page, last_page, comment, reference_location, type, number, location')->where('fk_annotation', $res['fk_annotation']);
                $referenceArray = array();
                $i = 0;
                foreach ($references as $reference) {
                    $referenceArray[$i] = $reference->toArray();
                    $authors = $reference->related('authors');
                    foreach ($authors as $author) {
                        $referenceArray[$i]['authors'][] = $author->author['name'];
                    }

                    $applicants = $reference->related('applicants');
                    foreach ($applicants as $applicant) {
                        $referenceArray[$i]['applicants'][] = $applicant->applicant['name'];
                    }
                    $i++;
                }
                $sequence->loadData(array('references' => $referenceArray));

                // load features with qualifiers
                $featureArray = array();
                if ($res['fk_annotation'] != '') {
                    $features = $this->repository->getTable('features')->select('id, name, location')->where('fk_annotation', $res['fk_annotation']);
                    $j = 0;
                    foreach ($features as $feature) {
                        $featureArray[$j] = $feature->toArray();
                        $qualifiers = $this->repository->getTable('qualifiers')->select('key, value')->where('fk_feature', $feature['id']);
                        foreach ($qualifiers as $qualifier) {
                            $featureArray[$j]['qualifiers'][$qualifier['key']] = $qualifier['value'];
                        }
                        $j++;
                    }
                }
                $sequence->loadData(array('features' => $featureArray));

                // load xrefs
                $xrefArray = array();
                if ($res['fk_annotation'] != '') {
                    $xrefs = $this->repository->getTable('xrefs')->select('*')->where('fk_annotation', $res['fk_annotation']);
                    foreach ($xrefs as $xref) {
                        $xrefArray[] = $xref->toArray();
                    }
                }
                $sequence->loadData(array('xrefs' => $xrefArray));
            }
            // load predictions
            $predictions = $this->repository->getTable('predictions')->select('*')->where('fk_accession', $res['accessionId']);
            $predictionArray = array();
            foreach ($predictions as $prediction) {
                $structure = $this->repository->getTable('structures')->where('fk_prediction', $prediction->id)->fetch()->toArray();
                $wholePrediction['structure'] = $structure['structure'];
                $wholePrediction['algorithm'] = $prediction->algorithm;
                $wholePrediction['structure_id'] = $prediction->id;
                $wholePrediction['template'] = $prediction->template;
                $wholePrediction['similarity'] = $prediction->similarity;
                $wholePrediction['z_score'] = $prediction->z_score;
                if (!$this->simpleSearch) {
                    $wholePrediction['foverhangs'] = $this->getPredictionFeatures($prediction->id, 'foverhangs');
                    $wholePrediction['toverhangs'] = $this->getPredictionFeatures($prediction->id, 'toverhangs');
                    $wholePrediction['helices'] = $this->getPredictionFeatures($prediction->id, 'helices');
                    $wholePrediction['hairpins'] = $this->getPredictionFeatures($prediction->id, 'hairpins');
                    $wholePrediction['bulges'] = $this->getPredictionFeatures($prediction->id, 'bulges');
                    $wholePrediction['loops'] = $this->getPredictionFeatures($prediction->id, 'loops');
                    $wholePrediction['junctions'] = $this->getPredictionFeatures($prediction->id, 'junctions');
                    /*$wholePrediction['pseudoknots'] = $this->getPredictionFeatures($prediction->id, 'pseudoknots');
                    $wholePrediction['unconserveds'] = $this->getPredictionFeatures($prediction->id, 'unconserveds');
                    $wholePrediction['unmeasureds'] = $this->getPredictionFeatures($prediction->id, 'unmeasureds');
                    $wholePrediction['tangleds'] = $this->getPredictionFeatures($prediction->id, 'tangleds');*/
                }
                $predictionArray[] = $wholePrediction;
            }
            $sequence->loadData(array('predictions' => $predictionArray));

            $return->addSequence($sequence);
        }
        return $return;
    }

    /**
     * Executes count query, returns resultset size
     *
     * @param string $accession
     * @param int $start
     * @param int $stop
     * @return int size of the result
     */
    public function getCount($accession = null, $start = null, $stop = null) {
        $base = $this->chooseBaseTable();

        // prepare query
        $result = $this->prepareCriteria('COUNT(*) AS mysize', $base);

        if (isset($accession) && !empty($accession)) {
            $result->where($base . '.accession', $accession);
        }
        if (isset($start) && !empty($start)) {
            $result->where($base . '.start', $start);
        }
        if (isset($stop) && !empty($stop)) {
            $result->where($base . '.stop', $stop);
        }

        $result->limit(10, 0);
        $row = $result->fetch();
        return $row['mysize'];
    }

    /**
     * Checks whether incoming key is a reserved word
     *
     * @param string $name parameter name
     * @param string $value parameter value
     * @return boolean true, if key is reserved word
     */
    protected function checkReservedWord($name, $value) {
        if ($name == 'offset') {
            $this->offset = (int) $value;
            return true;
        }
        if ($name == 'limit') {
            $this->limit = (int) $value;
            return true;
        }
        if ($name == 'aggregateByAccession' && $value) {
            $this->aggregateByAccession = true;
            return true;
        }
        return false;
    }

    /**
     * Check whether the *db_attribute exists in the completeData and returns its prefix.
     *
     * @param string $name name of the input
     * @return string|false prefix with that the attribut exists
     */
    protected function containsAttribute($name) {
        foreach ($this->completeData as $key => $value) {
            if (\Nette\Utils\Strings::endsWith($key, 'db_' . $name) && isset($value)) {
                if (is_array($value)) {
                    foreach ($value as $val) {
                        if (isset($val) && $val != '') {
                            return substr($key, 0, -3-strlen($name));
                        }
                    }
                } elseif (is_string($value) && $value != '') {
                    return substr($key, 0, -3-strlen($name));
                }
            }
        }
        return false;
    }

    /**
     * Returns array with information for db criteria (table attribute name and rule)
     *
     * @param string $name name of the input
     * @param string $value user input value
     * @return array|false contains three elements, criteria ('eq', 'gt'), attribute key and its value; returns false if parameter does not add criteria
     */
    protected function getCriteriaParam($name, $value) {
        switch ($name) {
            case'start':
                $key = '%BASE_VIEW%.start';
                $criteria = self::RULE_EQ;
                break;
            case'stop':
                $key = '%BASE_VIEW%.stop';
                $criteria = self::RULE_EQ;
                break;
            case'sequence':
                $key = 'UPPER(sequences:sequence)';
                $value = preg_replace('/\s+/', '', strtoupper($value));
                $criteria = self::RULE_LIKE;
                break;
            case'name':
                $key = 'LOWER(%BASE_VIEW%.name)';
                $value = strtolower($value);
                $criteria = self::RULE_LIKE;
                break;
            case'taxonomy':
                $key = '%BASE_VIEW%.path_name';
                $ids = explode(',', $value);
                $value = array();
                foreach ($ids as $id) {
                    $value[] = $this->getTaxonomyPath($id);
                }
                $criteria = self::RULE_PREFIX;
                break;
            case'description':
                $key = 'LOWER(%BASE_VIEW%.description)';
                $value = strtolower($value);
                $criteria = self::RULE_LIKE;
                break;
            case'accession':
                $key = 'UPPER(%BASE_VIEW%.accession)';
                $criteria = self::RULE_PREFIX;

                $prefix = $this->containsAttribute('accession_array');
                // OR rule
                if ($prefix !== false) {
                    $accessions = $this->completeData[$prefix . 'db_accession_array'];
                    if (is_array($value)) {
                        $accessions = array_merge($accessions, $value);
                    } else {
                        $accessions[] = $value;
                    }
                    $value = array();
                    foreach ($accessions as $acc) {
                        if (isset($acc) && $acc !== '') {
                            $value[] = strtoupper($acc);
                        }
                    }
                } else {
                    if (is_string($value)) {
                        $value = strtoupper($value);
                    } elseif (is_array($value)) {
                        $value = array_map('strtoupper', $value);
                    }
                }
                break;
            case'export_accession':
                $key = 'UPPER(%BASE_VIEW%.accession)';
                $criteria = self::RULE_EQ;
                $lines = explode("\n", $value);
                $value = array();
                foreach ($lines as $line) {
                    $parts = explode(':', $line);
                    if (count($parts) === 3) {
                        $value[] = strtoupper($parts[0]);
                    }
                }
                break;
            case'firstpublished':
                $key = '%BASE_VIEW%.first_public';

                $prefixDirection = $this->containsAttribute('firstpublished_direction');
                if ($prefixDirection !== false && $this->completeData[$prefixDirection . 'db_firstpublished_direction'] == 'gt') {
                    $criteria = self::RULE_GT;
                } else {
                    $criteria = self::RULE_LT;
                }

                $prefixArray = $this->containsAttribute('firstpublished_array');
                $prefixDirectionArray = $this->containsAttribute('firstpublished_direction_array');
                if ($prefixArray !== false && $prefixDirectionArray !== false && $prefixArray === $prefixDirectionArray) {
                    $this->defineMultipliedRules($key, $this->completeData[$prefixArray . 'db_firstpublished_array'], $this->completeData[$prefixDirectionArray . 'db_firstpublished_direction_array']);
                }
                break;
            case'sequence_quality':
                $key = 'qualities:sequence_quality';
                $criteria = self::RULE_GT;
                break;
            case'region_length':
                $key = '%BASE_VIEW%.region_length';

                $prefixDirection = $this->containsAttribute('region_length_direction');
                if ($prefixDirection !== false && $this->completeData[$prefixDirection . 'db_region_length_direction'] == 'gt') {
                    $criteria = self::RULE_GT;
                } else {
                    $criteria = self::RULE_LT;
                }

                $prefixArray = $this->containsAttribute('region_length_array');
                $prefixDirectionArray = $this->containsAttribute('region_length_direction_array');
                if ($prefixArray !== false && $prefixDirectionArray !== false && $prefixArray === $prefixDirectionArray) {
                    $this->defineMultipliedRules($key, $this->completeData[$prefixArray . 'db_region_length_array'], $this->completeData[$prefixDirectionArray . 'db_region_length_direction_array']);
                }
                break;
            case'molecule_type':
                $key = '%BASE_VIEW%.dataset';
                $criteria = self::RULE_EQ;

                $prefix = $this->containsAttribute('molecule_type_array');
                // OR rule
                if ($prefix !== false) {
                    $moleculetypes = $this->completeData[$prefix . 'db_molecule_type_array'];
                    $moleculetypes[] = $value;
                    $value = $moleculetypes;
                }
                break;
            case'ambiguities':
                $key = 'qualities:ambiguities';

                $prefixDirection = $this->containsAttribute('ambiguities_direction');
                if ($prefixDirection !== false && $this->completeData[$prefixDirection . 'db_ambiguities_direction'] == 'gt') {
                    $criteria = self::RULE_GT;
                } else {
                    $criteria = self::RULE_LT;
                }

                $prefixArray = $this->containsAttribute('ambiguities_array');
                $prefixDirectionArray = $this->containsAttribute('ambiguities_direction_array');
                if ($prefixArray !== false && $prefixDirectionArray !== false && $prefixArray === $prefixDirectionArray) {
                    $this->defineMultipliedRules($key, $this->completeData[$prefixArray . 'db_ambiguities_array'], $this->completeData[$prefixDirectionArray . 'db_ambiguities_direction_array']);
                }
                break;
            case'homopolymers':
                $key = 'qualities:homopolymers';

                $prefixDirection = $this->containsAttribute('homopolymers_direction');
                if ($prefixDirection !== false && $this->completeData[$prefixDirection . 'db_homopolymers_direction'] == 'gt') {
                    $criteria = self::RULE_GT;
                } else {
                    $criteria = self::RULE_LT;
                }

                $prefixArray = $this->containsAttribute('homopolymers_array');
                $prefixDirectionArray = $this->containsAttribute('homopolymers_direction_array');
                if ($prefixArray !== false && $prefixDirectionArray !== false && $prefixArray === $prefixDirectionArray) {
                    $this->defineMultipliedRules($key, $this->completeData[$prefixArray . 'db_homopolymers_array'], $this->completeData[$prefixDirectionArray . 'db_homopolymers_direction_array']);
                }
                break;
            case'base_pair_score':
                $key = 'qualities:base_pair_score';

                $prefixDirection = $this->containsAttribute('base_pair_score_direction');
                if ($prefixDirection !== false && $this->completeData[$prefixDirection . 'db_base_pair_score_direction'] == 'gt') {
                    $criteria = self::RULE_GT;
                } else {
                    $criteria = self::RULE_LT;
                }

                $prefixArray = $this->containsAttribute('base_pair_score_array');
                $prefixDirectionArray = $this->containsAttribute('base_pair_score_direction_array');
                if ($prefixArray !== false && $prefixDirectionArray !== false && $prefixArray === $prefixDirectionArray) {
                    $this->defineMultipliedRules($key, $this->completeData[$prefixArray . 'db_base_pair_score_array'], $this->completeData[$prefixDirectionArray . 'db_base_pair_score_direction_array']);
                }
                break;
            case'aligned_bases':
                $key = 'qualities:aligned_bases';

                $prefixDirection = $this->containsAttribute('aligned_bases_direction');
                if ($prefixDirection !== false && $this->completeData[$prefixDirection . 'db_aligned_bases_direction'] == 'gt') {
                    $criteria = self::RULE_GT;
                } else {
                    $criteria = self::RULE_LT;
                }

                $prefixArray = $this->containsAttribute('aligned_bases_array');
                $prefixDirectionArray = $this->containsAttribute('aligned_bases_direction_array');
                if ($prefixArray !== false && $prefixDirectionArray !== false && $prefixArray === $prefixDirectionArray) {
                    $this->defineMultipliedRules($key, $this->completeData[$prefixArray . 'db_aligned_bases_array'], $this->completeData[$prefixDirectionArray . 'db_aligned_bases_direction_array']);
                }
                break;
            case'alignment_quality':
                $key = 'qualities:alignment_quality';

                $prefixDirection = $this->containsAttribute('alignment_quality_direction');
                if ($prefixDirection !== false && $this->completeData[$prefixDirection . 'db_alignment_quality_direction'] == 'gt') {
                    $criteria = self::RULE_GT;
                } else {
                    $criteria = self::RULE_LT;
                }

                $prefixArray = $this->containsAttribute('alignment_quality_array');
                $prefixDirectionArray = $this->containsAttribute('alignment_quality_direction_array');
                if ($prefixArray !== false && $prefixDirectionArray !== false && $prefixArray === $prefixDirectionArray) {
                    $this->defineMultipliedRules($key, $this->completeData[$prefixArray . 'db_alignment_quality_array'], $this->completeData[$prefixDirectionArray . 'db_alignment_quality_direction_array']);
                }
                break;
            default:
                return false;
        }
        if ($value == '' || (is_array($value) && empty($value))) {
            return false;
        }
        return array('criteria' => $criteria, 'key' => $key, 'value' => $value);
    }

    /**
     * Prepares criteria for query
     *
     * @param string $select select part of the query
     * @param string $base base view to select from - vw_acc_ann_tax, vw_acc_ann_tax_grouped_acc, vw_acc_ann_tax_grouped_name
     * @return \Nette\Database\Table\Selection query with all criteria
     */
    protected function prepareCriteria($select, $base = 'vw_acc_ann_tax') {
        $query = $this->repository->getTable($base)->select($select);

        foreach ($this->criteria[self::RULE_EQ] as $eqRule) {
            foreach ($eqRule as $key => $val) {
                $query->where($this->prepareKey($key, $base), $val);
            }
        }

        foreach ($this->criteria[self::RULE_GT] as $gtRule) {
            foreach ($gtRule as $key => $val) {
                $query->where($this->prepareKey($key, $base) . ' > ?', $val);
            }
        }

        foreach ($this->criteria[self::RULE_LT] as $ltRule) {
            foreach ($ltRule as $key => $val) {
                $query->where($this->prepareKey($key, $base) . ' < ?', $val);
            }
        }

        foreach ($this->criteria[self::RULE_LIKE] as $gtRule) {
            foreach ($gtRule as $key => $val) {
                $query->where($this->prepareKey($key, $base) . ' LIKE ?', '%' . $val . '%');
            }
        }

        foreach ($this->criteria[self::RULE_PREFIX] as $prefRule) {
            foreach ($prefRule as $key => $val) {
                $key = $this->prepareKey($key, $base);
                if (is_array($val)) {
                    $condition = '';
                    foreach ($val as $v) {
                        $condition .= ($condition == '') ? $key : ' OR ' . $key;
                        $condition .= ' LIKE ?';
                    }
                    array_walk($val, function(&$item) {
                        $item = $item . '%';
                    });
                    if (count($val) == 1) {
                        $val = $val[0];
                    }
                    $query->where($condition, $val);
                } else {
                    $query->where($key . ' LIKE ?', $val . '%');
                }
            }
        }

        if ($this->limit != 0) {
            $query->limit($this->limit, $this->offset);
        }

        return $query;
    }

    /**
     * Sets all variables from activerow to newly created sequence object
     *
     * @param \Nette\Database\Table\ActiveRow $tableData
     * @return \DispatchModule\Sequence
     */
    private function setResults(\Nette\Database\Table\ActiveRow $tableData) {
        $sequence = new \DispatchModule\Sequence;
        // create data array for all elements
        $data = array();
        foreach ($tableData as $name => $value) {
            $data[$this->toCamelCase($name)] = $value;
        }
        $sequence->loadData($data);
        return $sequence;
    }

    /**
     * Replaces key for use in SQL query (replaces %BASE_VIEW% for actual view name)
     *
     * @param string $key desired key
     * @param string $base base table
     * @return string database key
     */
    protected function prepareKey($key, $base) {
        return str_replace('%BASE_VIEW%', $base, $key);
    }

    /**
     * Chooses proper base table - vw_acc_ann_tax_grouped_acc, vw_acc_ann_tax_grouped_name or vw_acc_ann_tax
     *
     * @return string base table name
     */
    protected function chooseBaseTable() {
//        if ($this->aggregateByAccession) {
//            $base = 'vw_acc_ann_tax_grouped_acc';
//        } else {
        $base = 'vw_acc_ann_tax';
//        }
        return $base;
    }

    /**
     * Gets human-readable short description (name) of the tool
     *
     * @return string human-readable short description of the tools
     */
    public function getLabel() {
        return $this->label;
    }

    /**
     * Helper function that formats database attribute name into camel case
     *
     * @param string $name string to be converted (e.g. my_attribute_name)
     * @return string camelcase version of a string (e.g. myAttributeName)
     */
    private function toCamelCase($name) {
        $nameArray = explode('_', $name);
        $result = '';
        for ($i = 0; $i < count($nameArray); $i++) {
            if ($i > 0) {
                $nameArray[$i] = \Nette\Utils\Strings::firstUpper($nameArray[$i]);
            }
            $result .= $nameArray[$i];
        }
        return $result;
    }

    /**
     * Gets specified characteristic for prediction (with its positions)
     * 
     * @param int $predictionId id of prediction
     * @param string $type name of the characteristic - bulges, foverhangs, hairpins, helices, junctions, loops or toverhangs
     * @return array arary containing information about predicted characteristics
     */
    protected function getPredictionFeatures($predictionId, $type) {
        $array = array();
        if (in_array($type, array('bulges', 'foverhangs', 'hairpins', 'helices', 'junctions', 'loops', 'toverhangs', 'pseudoknots', 'unconserveds', 'unmeasureds', 'tangleds'))) {
            $fkName = 'fk_' . substr($type, 0, -1);
            $query = 'SELECT T.id, TP.start, TP.stop FROM ' . $type . ' AS T '
                    . 'INNER JOIN ' . $type . '_positions AS TP ON TP.' . $fkName . ' = T.id '
                    . 'WHERE fk_prediction = ? ORDER BY T.id, TP.start';
            $characteristics = $this->repository->getConnection()->queryArgs($query, array($predictionId));
            foreach ($characteristics as $characteristic) {
                $array[] = array($characteristic['start'], $characteristic['stop'], $characteristic['id']);
            }
        }
        return $array;
    }

    /*
     * **************************
     * Attribute-specific rules
     * **************************
     */

    /**
     * Sets possible second length criteria
     *
     * @param string $key attribute key
     * @param array $allValues all length values
     * @param array $allDirections all length directions (lesser than, greater than)
     */
    protected function defineMultipliedRules($key, $allValues, $allDirections) {
        if (isset($allValues[0], $allDirections[0]) && $allValues[0] != '') {
            if ($allDirections[0] == 'gt') {
                $this->criteria[self::RULE_GT][] = array($key => $allValues[0]);
            } else {
                $this->criteria[self::RULE_LT][] = array($key => $allValues[0]);
            }
        }
    }

    /**
     * Gets taxonomy path for specified taxonomy record (category)
     *
     * @param int $value taxonomy.id
     * @return string taxonomy path
     */
    protected function getTaxonomyPath($value) {
        $row = $this->repository->getTable('taxonomy')->where('id', $value)->fetch();
        if ($row) {
            $result = $row['path_name'];
        } else {
            $result = '';
        }
        return $result;
    }

    /**
     * Selects id of the last taxonomic category in the tree (from array of category inputs)
     *
     * @param array $value taxonomy category inputs
     * @return int id of last category
     */
    protected function lastTaxonomySelected($value) {
        $i = 0;
        while (isset($value[$i]) && $value[$i] != '') {
            $i++;
        }
        return $value[$i - 1];
    }

    /**
     * Returns organism names containing $organismName
     * @param String $organismName Organism name
     * @return Array  organism names
     */
    public function getNameValues($organismName) {
        $storage = new \Nette\Caching\Storages\FileStorage('../temp');
        $cache = new \Nette\Caching\Cache($storage);
        $uid = 'getNameValues' . md5($organismName);
        $result = $cache->load($uid);
        if ($result === NULL) {
            $result = array();
            foreach ($this->repository->getTable('vw_acc_ann_tax')
                    ->select('DISTINCT name')
                    ->where('LOWER(name) LIKE ?', '%' . strtolower($organismName) . '%')
                    ->order('name')
                    ->limit(10)
            as $row) {
                $result[] = $row['name'];
            }
            $cache->save($uid, $result, array(
                \Nette\Caching\Cache::EXPIRE => LONG_CACHE_TIME, // month
            ));
        }
        return $result;
    }

    /** OTHER FUNCTIONS * */

    /**
     * Set simple search flag - defines, whether only basic sequence info should be loaded
     *
     * @param bool $val new simpleSearch value
     */
    public function setSimpleSearch($val) {
        $this->simpleSearch = $val;
    }

}