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: 

<?php

namespace BaseModule\Services;

/**
 * DbService - general service for accessing database
 */
class DbService extends \DispatchModule\Tools\BaseTool {

    /**
     * @var \BaseModule\Repository\BaseRepository Base db repository
     * @Inject
     */
    protected $repository;

    /**
     * Gets names, subtree sizes and ids of child taxonomy categories (not species in the category!)
     *
     * @param int $id taxonomy category
     * @return array childe taxonomy category - array indexed by id containing name and subtreesize
     */
    public function getSubTaxonomyFull($id) {
        $r = $this->repository->getTable('taxonomy')->where('fk_parent', $id)->where('is_specie', false)->order('name');
        $result = array();
        foreach ($r as $row) {
            $result[$row['id']] = array('name' => ($row['name'] == ' ') ? 'unidentified' : $row['name'], 'subtreesize' => $row['subtreesize']);
        }

        return $result;
    }

    /**
     * Gets ids of species in a category
     *
     * @param int $id taxonomy category
     * @return array childe taxonomy category ids
     */
    public function getSubSpeciesFull($id) {
        if ($id != '') {
            $r = $this->repository->getTable('taxonomy')->where('fk_parent', $id)->where('is_specie', true)->order('name');
            $result = array();
            foreach ($r as $row) {
                $result[$row['id']] = array('name' => ($row['name'] == ' ') ? 'unidentified' : $row['name'], 'subtreesize' => $row['subtreesize']);
            }
        } else {
            $result = array();
        }
        return $result;
    }

    /**
     * Prepares array containing ids from children to the root
     * - basically input is id of a category (or organism), and this method traverses up building complete tree "expanded" in the way that the input id is visible
     *
     * @param array $preselected list of selected id's
     * @return array children-parent structure from root to the specified ids
     */
    public function getChildrenTree($preselected) {
        $children = array();
        $cache = array();
        // loop over preselected ids
        foreach ($preselected as $categoryId) {
            $parentId = $categoryId;
            $parent = -1;
            // traverse up to the root
            while ($parent !== false) {
                if (isset($cache[$parentId])) {
                    $parentId = $cache[$parentId];
                } else {
                    $parent = $this->repository->getTable('taxonomy')->select('fk_parent')->where('id', $parentId)->fetch();
                    $cache[$parentId] = $parent['fk_parent'];
                    $parentId = $parent['fk_parent'];
                    $children[$parentId][0] = $this->getSubTaxonomyFull($parentId);
                    $children[$parentId][1] = $this->getSubSpeciesFull($parentId);
                }
            }
        }
        return $children;
    }

    /**
     * Helper function that loads both species and cateogries in taxonomy tree
     * (each record is indexed by it's id and contains name and subtreesize)
     *
     * @param int $id parent id
     * @return array array of arrays indexed by record ids which contains name and subtreesize
     */
    private function _getSub($id) {
        $r = $this->repository->getTable('taxonomy')->select('id, name, is_specie, subtreesize')->where('fk_parent', $id)->order('name');
        $species = array();
        $categories = array();
        foreach ($r as $row) {
            if ($row['is_specie'] == true) {
                $species[$row['id']] = array('name' => ($row['name'] == ' ') ? 'unidentified' : $row['name'], 'subtreesize' => $row['subtreesize']);
            } else {
                $categories[$row['id']] = array('name' => ($row['name'] == ' ') ? 'unidentified' : $row['name'], 'subtreesize' => $row['subtreesize']);
            }
        }
        return array($categories, $species);
    }

    /**
     * Load tree for fulltext matches with path to matches only
     *
     * @param string $fulltext fulltext searched word
     * @return array two arrays, complete tree and ids of matches
     */
    public function getFulltextMatchesTree($fulltext) {
        $ids = array();
        $children = array();
        $matches = $this->repository->getTable('taxonomy')->where('LOWER(name) LIKE ?', '%' . strtolower($fulltext) . '%');
        // loop over preselected ids
        foreach ($matches as $match) {
            $ids[] = $match['id'];
            // first cycle is a little different
            $node = $this->repository->getTable('taxonomy')->select('id, fk_parent, name, subtreesize, is_specie')->where('id', $match['id'])->fetch();
            $parentId = $node['fk_parent'];
            if (isset($children[$parentId][0][$node['id']])) {
                $node = false;
            } else {
                if ($node['is_specie'] == true) {
                    $children[$parentId][0] = array();
                    $children[$parentId][1][$node['id']] = array('name' => $node['name'], 'subtreesize' => $node['subtreesize']);
                } else {
                    $children[$parentId][0][$node['id']] = array('name' => $node['name'], 'subtreesize' => $node['subtreesize']);
                    $children[$parentId][1] = array();
                }
            }
            // traverse up to the root
            while ($parentId !== null && $node !== false) {
                $node = $this->repository->getTable('taxonomy')->select('id, fk_parent, name, subtreesize, is_specie')->where('id', $parentId)->fetch();
                if (isset($children[$node['fk_parent']][0][$node['id']])) {
                    $node = false;
                } else {
                    $parentId = $node['fk_parent'];
                    $children[$parentId][0][$node['id']] = array('name' => $node['name'], 'subtreesize' => $node['subtreesize']);
                    $children[$parentId][1] = array();
                }
            }
        }
        return array($children, $ids);
    }

    /**
     * Load complete (expanded) tree for fulltext matches
     *
     * @param string $fulltext fulltext searched word
     * @return array two arrays, complete tree and ids of matches
     */
    public function getFulltextWholeTree($fulltext) {
        $ids = array();
        $children = array();
        $cache = array();
        $r = $this->repository->getTable('taxonomy')->where('LOWER(name) LIKE ?', '%' . strtolower($fulltext) . '%');
        // loop over preselected ids
        foreach ($r as $parent) {
            $parentId = $parent['id'];
            $ids[] = $parentId;
            // traverse up to the root
            while ($parent !== false) {
                if (isset($cache[$parentId])) {
                    $parent = false;
                } else {
                    $parent = $this->repository->getTable('taxonomy')->select('fk_parent')->where('id', $parentId)->fetch();
                    $cache[$parentId] = $parent['fk_parent'];
                    $parentId = $parent['fk_parent'];
                    list($children[$parentId][0], $children[$parentId][1]) = $this->_getSub($parentId);
                }
            }
        }
        return array($children, $ids);
    }

    /**
     * Load complete (expanded) tree for fulltext matches
     *
     * @param array $ids array of ids of matches
     * @return array complete tree
     */
    public function getFulltextWholeTreeWithHelp($ids) {
        $children = array();
        $cache = array();
        // loop over preselected ids
        foreach ($ids as $id) {
            $parentId = $id;
            // traverse up to the root
            while ($parentId !== null) {
                if (isset($cache[$parentId])) {
                    $parentId = null;
                } else {
                    $parent = $this->repository->getTable('taxonomy')->select('fk_parent')->where('id', $parentId)->fetch();
                    $cache[$parentId] = $parent['fk_parent'];
                    $parentId = $parent['fk_parent'];
                    list($children[$parentId][0], $children[$parentId][1]) = $this->_getSub($parentId);
                }
            }
        }
        return $children;
    }

    /**
     * Gets ids for fulltext match
     *
     * @param string $fulltext search string
     * @return array array of ids of categories & species, in which names the string is contained
     */
    public function getFulltextIds($fulltext) {
        $ids = array();
        $r = $this->repository->getTable('taxonomy')->where('LOWER(name) LIKE ?', '%' . strtolower($fulltext) . '%');
        foreach ($r as $row) {
            $ids[] = $row['id'];
        }
        return $ids;
    }

    /**
     * Gets rows for fulltext match
     *
     * @param string $fulltext search string
     * @return array array of records, in which names the string is contained
     */
    public function getFulltextSpecie($fulltext) {
        $result = array();
        $r = $this->repository->getTable('taxonomy')->where('LOWER(name) LIKE ?', strtolower($fulltext))->order('path_name');
        foreach ($r as $row) {
            $result[] = $row;
        }
        return $result;
    }

    /**
     * Get basic information for accession numbers (faster than DbTool)
     *
     * @param array $accessions list of accesion numbers along with stops and starts (split by ':')
     * @return array list of basic information about each sequence
     */
    public function getBasicInfoForAccessions($accessions) {
        $query = '';
        $args = array();
        foreach ($accessions as $i => $acc) {
            $accRecord = explode(':', $acc);
            $query .= $query == '' ? '(accession ? AND start ? AND stop ?)' : ' OR (accession ? AND start ? AND stop ?)';
            $args = array_merge($args, array($accRecord[0], $accRecord[1], $accRecord[2]));
        }
        $q = $this->repository->getTable('vw_acc_ann_tax')->where($query, $args);
        $result = array();
        foreach ($q as $row) {
            if (!array_key_exists($row->accession, $result)) {
                $result[$row->accession] = array('accession' => $row->accession, 'name' => $row->name, 'start' => $row->start, 'stop' => $row->stop);
            }
        }
        return $result;
    }

    /**
     * Gets last RSS news
     *
     * @param int $amount limit of RSS news to display
     * @return ActiveRow list of last RSS news
     */
    public function getRssData($amount = 15) {
        return $this->repository->getTable('rss')->order('published DESC')->limit($amount);
    }

    /**
     * Update RSS record
     *
     * @param array $data data to update
     * @param int $id record id
     * @return int number of records updated
     */
    public function updateRss($data, $id) {
        return $this->repository->getTable('rss')->where('id', $id)->update($data);
    }

    /**
     * Insert RSS record
     *
     * @param array $data
     * @return \Nette\Database\Table\ActiveRow
     */
    public function insertRss($data) {
        return $this->repository->getTable('rss')->insert($data);
    }

    /**
     * Find RSS record
     *
     * @param array $id
     * @return \Nette\Database\Table\ActiveRow
     */
    public function findRssById($id) {
        return $this->repository->getTable('rss')->where('id', $id)->fetch();
    }

    /**
     * Deletes RSS record
     *
     * @param int $id record id
     * @return int number of records deleted
     */
    public function deleteRss($id) {
        return $this->repository->getTable('rss')->where('id', $id)->delete();
    }

}