<?php
/**
 * This class has been auto-generated by the Doctrine ORM Framework
 */
class CardTable extends Doctrine_Table
{
    public static $stateEnum = array(
            1 => 'inkaso',
            2 => 'zabávka',
            3 => 'trestní oznámení',
            4 => 'pojistná událost',
            5 => 'vráceno bez fakturace',
            6 => 'splátkový kalendář'
        );

    public static function getStateEnum() {
        return self::$stateEnum;
    }

    public function getListProRegal($regal_id) {
        return $this->createQuery('c')
            ->leftJoin('c.Regal r')
            ->where('r.regal_id = ?', $regal_id)
            ->groupBy('c.id')
            ->orderBy('c.created_at DESC');
    }

    public function getListForSupervisorQuery() {
        return $this->createQuery('c')
            ->where('c.user_id IS NOT NULL')
            ->andWhere('c.user_finished = 0')
            ->orderBy('c.created_at DESC');
    }
    public function getListForSupervisorQueryWhereForFilter($query) {
        return $query
            ->andWhere('user_finished = 0')
            ->orderBy('created_at DESC');
    }
    public function getListForUserQuery($user_id) {
        return $this->createQuery('c')
            ->where('c.user_id = ?', $user_id)
            ->andWhere('c.user_finished = 0')
            ->orderBy('c.created_at DESC');
    }
    public function getListOfImportedQuery() {
        return $this->createQuery('c')
            ->where('c.user_id IS NULL')
            ->orderBy('c.created_at DESC');
    }
    public function getListOfUser_finishedQuery() {
        return $this->createQuery('c')
            ->where('c.user_id IS NOT NULL')
            ->andWhere('c.user_finished = 1')
            ->andWhere('c.state IS NULL')
            ->orderBy('c.created_at DESC');
    }
    public function getListOfClosedQuery() {
        return $this->createQuery('c')
            ->andWhere('c.state IS NOT NULL')
            ->orderBy('c.state_date DESC');
    }

    public function getListOfCriticalForUser($user_id, $days) {
        $conn = $this->getConnection();
        $sql = "SELECT DATE_SUB(DATE_ADD(t1.created_at, INTERVAL t1.days_to_solve DAY), INTERVAL {$days} DAY) AS zbyva, t1.*
                FROM   card t1
                WHERE  t1.user_id = {$user_id} AND
                       t1.user_finished = 0 AND
                       DATE(DATE_SUB(DATE_ADD(t1.created_at, INTERVAL t1.days_to_solve DAY), INTERVAL {$days} DAY)) <= DATE(NOW()) AND
                       state IS NULL
                ORDER BY zbyva ASC";
        $statement = $conn->execute($sql);
        return $statement->fetchAll();
    }

    public function getListOfCriticalForSupervisor($days) {
        $conn = $this->getConnection();
        $sql = "SELECT DATE_SUB(DATE_ADD(t1.created_at, INTERVAL t1.days_to_solve DAY), INTERVAL {$days} DAY) AS zbyva, t1.*
                FROM   card t1
                WHERE  DATE(DATE_SUB(DATE_ADD(t1.created_at, INTERVAL t1.days_to_solve DAY), INTERVAL {$days} DAY)) <= DATE(NOW()) AND
                       state IS NULL
                ORDER BY zbyva ASC";
        $statement = $conn->execute($sql);
        return $statement->fetchAll();
    }

    /*
     * Lucene fulltext
     */
    static public function getLuceneIndex() {
        ProjectConfiguration::registerZend();

        $index = self::getLuceneIndexFile();
        if (file_exists($index)) {
            return Zend_Search_Lucene::open($index);
        } else {
            return Zend_Search_Lucene::create($index);
        }
    }

    static public function getLuceneIndexFile() {
        return sfConfig::get('sf_data_dir').'/lucene/card.index';
    }

    public function getForLuceneQuery($query) {
        $index = $this->getLuceneIndex();
        $hits = $index->find($query);

        $pks = array();
        foreach ($hits as $hit) {
            $pks[] = $hit->pk;
        }

        if (empty($pks)) {
            return array();
        }

        $q = $this->createQuery('p')
                  ->whereIn('p.id', $pks)
                  ->limit(20);

        return $q->execute();
    }
}