<?php

/**
 * This class has been auto-generated by the Doctrine ORM Framework
 */
class RegalTable extends Doctrine_Table {

    public function checkRelatedForObject(sfDoctrineRecord $record, $card_id = null) {

        $count = 0;
        switch (get_class($record)) {
            case 'Card':


                // test name a surname
                $nameArr = explode(' ', ereg_replace('\ {2,}', ' ', $record->client_name));
                $count += $this->najdiJmeno($nameArr[0], $nameArr[1], $card_id);
                // code
                $count += $this->processColumn('code', array($record->client_code), 'LIKE', $card_id);
                // company
                $count += $this->processColumn('company', array($record->client_name, $record->employer), 'LIKE', $card_id);
                // company_code
                $count += $this->processColumn('company_code', array($record->client_code), 'LIKE', $card_id);
                // address
                $count += $this->najdiAdresu($record->address_city, $record->address_street, $record->address_zipcode, $card_id);
                // altaddress
                $count += $this->najdiAdresu($record->altaddress_city, $record->altaddress_street, $record->altaddress_zipcode, $card_id);
                // employer
                $count += $this->najdiAdresu($record->employer_city, $record->employer_street, $record->employer_zipcode, $card_id);
                // phone
                $count += $this->processColumn('phone', array($record->cell, $record->phone, $record->address_cell, $record->address_phone, $record->address_phone2, $record->altaddress_cell, $record->altaddress_phone, $record->altaddress_phone2), 'LIKE', $card_id);
                break;
            case 'Attachment':
                // serialnumber
                $count += $this->processColumn('serialnumber', array($record->serialnumber), 'LOCATE', $card_id);
                break;
        }

        return $count;
    }


    function najdiJmeno($jmeno,  $prijmeni, $karta){

        $q = $this->createQuery()
             ->where("name = '".$jmeno."'")
             ->andWhere("surname = '".$prijmeni."'")
              ->execute();

        $pocet = count($q);

          foreach ($q AS $related) {
            if ($related->name != NULL OR $related->surname != NULL) {
                $card_regal = new Card_regal();
                $card_regal->card_id = $karta;
                $card_regal->regal_id = $related->id;
                $card_regal->column_match = 'surname';
                $card_regal->save();
            } else {
                $pocet--;
            }
        }


        return $pocet;


    }


    function najdiAdresu($mesto, $adresa, $psc, $card_id) {

        $sPsc = $this->createQuery()
             ->where("zipcode = '".$psc."'")
             ->andWhere("city = '".$mesto."'")
             ->andWhere("street = '".$adresa."'")
             ->execute();

        $bezPsc = $this->createQuery()
             ->where("zipcode = ''")
             ->andWhere("city = '".$mesto."'")
             ->andWhere("street = '".$adresa."'")
             ->execute();


        $pocet = count($sPsc) + count($bezPsc);


        foreach ($sPsc AS $related) {
            if ($related->city != NULL OR $related->street != NULL OR $related->street != NULL) {
                $card_regal = new Card_regal();
                $card_regal->card_id = $card_id;
                $card_regal->regal_id = $related->id;
                $card_regal->column_match = 'street';
                $card_regal->save();
            } else {
                $pocet--;
            }
        }

        foreach ($bezPsc AS $related) {
            if ($related->city != NULL OR $related->street != NULL) {
                $card_regal = new Card_regal();
                $card_regal->card_id = $card_id;
                $card_regal->regal_id = $related->id;
                $card_regal->column_match = 'street';
                $card_regal->save();
            } else {
                $pocet--;
            }
        }


        return $pocet;
    }

    private function processColumn($column, $values, $type = 'LIKE', $card_id = null) {
        $res = $this->executeColumnQuery($column, $values, $type);
        if (count($res)) {
            if ($card_id) {
                foreach ($res AS $related) {
                    $card_regal = new Card_regal();
                    $card_regal->card_id = $card_id;
                    $card_regal->regal_id = $related->id;
                    $card_regal->column_match = $column;
                    try {
                        $card_regal->save();
                    } catch (Exception $e) {

                    }
                }
            } else {
                //var_dump($column, $values);
            }
        }
        return count($res);
    }

    private function executeColumnQuery($column, $values, $type = 'LIKE') {
        $unsafeValues = $values;
        $values = array();
        foreach ($unsafeValues AS $value)
            if (!empty($value) && (strlen(trim($value)) > 0))
                $values[] = $value;

        $q = $this->createQuery();
        $q->where("{$column} IS NOT NULL");
        $q->andWhere("{$column} <> ''");
        $funcName = "prepareColumn{$type}";
        $q = $this->$funcName($q, $column, $values);
        return $q->execute();
    }

    private function prepareColumnLIKE($query, $column, $values) {
        if (count($values) == 0) {
            $v = 'xxxxxxxxxxxxxxxxxxx';
            $query->andWhere("{$column} LIKE ?", "{$v}");
            return $query;
        } else {
            $query->andWhere("{$column} LIKE ?", "{$this->prepareColumnValue($values[0])}");
            for ($i = 1; $i < count($values); ++$i)
                if (!empty($values[$i]))
                    $query->orWhere("{$column} LIKE ?", "{$this->prepareColumnValue($values[$i])}");

            return $query;
        }
    }

    private function prepareColumnLOCATE($query, $column, $values) {
        if (count($values) == 0) {
            $v = 'xxxxxxxxxxxxxxxxxxx';
            $query->andWhere("LOCATE({$column}, ?)", $v);
            return $query;
        } else {
            $query->andWhere("LOCATE({$column}, ?)", $this->prepareColumnValue($values[0]));
            for ($i = 1; $i < count($values); ++$i)
                if (!empty($values[$i]))
                    $query->orWhere("LOCATE({$column}, ?)", $this->prepareColumnValue($values[$i]));
            return $query;
        }
    }

    private function prepareColumnValue($value) {
        //var_dump($value);
        return trim($value);
    }

    /*
     * Lucene fulltext
     */

    static public function getLuceneIndex() {
        ProjectConfiguration::registerZend();

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

    static public function getLuceneIndexFile() {
        return sfConfig::get('sf_data_dir') . '/lucene/regal.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();
    }

}