<?php

/**
 * This class has been auto-generated by the Doctrine ORM Framework
 */
class Regal extends BaseRegal {
    /*
     * Lucene fulltext
     */

    public function updateLuceneIndex() {
        $index = $this->getTable()->getLuceneIndex();

        // remove existing entries
        foreach ($index->find('pk:' . $this->getId()) as $hit) {
            $index->delete($hit->id);
        }

        $doc = new Zend_Search_Lucene_Document();

        // store job primary key to identify it in the search results
        $doc->addField(Zend_Search_Lucene_Field::Keyword('pk', $this->getId()));

        // index job fields
        $doc->addField(Zend_Search_Lucene_Field::UnStored('name', $this->name, 'UTF-8'));
        $doc->addField(Zend_Search_Lucene_Field::UnStored('surname', $this->surname, 'UTF-8'));
        $doc->addField(Zend_Search_Lucene_Field::UnStored('code', $this->code, 'UTF-8'));
        $doc->addField(Zend_Search_Lucene_Field::UnStored('company', $this->company, 'UTF-8'));
        $doc->addField(Zend_Search_Lucene_Field::UnStored('company_code', $this->company_code, 'UTF-8'));
        $doc->addField(Zend_Search_Lucene_Field::UnStored('city', $this->city, 'UTF-8'));
        $doc->addField(Zend_Search_Lucene_Field::UnStored('street', $this->street, 'UTF-8'));
        $doc->addField(Zend_Search_Lucene_Field::UnStored('zipcode', $this->zipcode, 'UTF-8'));
        $doc->addField(Zend_Search_Lucene_Field::UnStored('phone', $this->phone, 'UTF-8'));
        $doc->addField(Zend_Search_Lucene_Field::UnStored('description', $this->description, 'UTF-8'));
        $doc->addField(Zend_Search_Lucene_Field::UnStored('serialnumber', $this->serialnumber, 'UTF-8'));
        $doc->addField(Zend_Search_Lucene_Field::UnStored('note', $this->note, 'UTF-8'));

        // add job to the index
        $index->addDocument($doc);
        $index->commit();
    }

    // pretizeni ukladani pro zpracovani indexu
    public function save(Doctrine_Connection $conn = null) {
        $ret = parent::save($conn);
        $this->updateLuceneIndex();
        $this->prepocetPropojeni();

        return $ret;
    }


    public function pocetKaret(){

         $karty = Doctrine_Query::create()
             ->from('Card c')
            ->leftJoin('c.Regal r')
            ->where('r.regal_id = ?', $this->id)
            ->groupBy('c.id')
            ;

            return count($karty);

    }


    public function prepocetPropojeni() {

        $this->smazaniStarychZaznamu();

        // Jmeno
        $this->najdiShoduJmena($this->name, $this->surname);
        // ICO
        $this->najdiShodu('client_code', $this->company_code, 'company_code');
        // RC
        $this->najdiShodu('client_code', $this->code, 'code');
        // Název společnosti
        $this->najdiShodu('client_name', $this->company, 'company');
        $this->najdiShodu('employer', $this->company, 'company');
        //Telefon
        //$this->najdiShodu('phone', $this->phone, 'phone');
        //$this->najdiShodu('cell', $this->phone, 'phone');
        //$this->najdiShodu('address_cell', $this->phone, 'phone');
        //$this->najdiShodu('address_phone', $this->phone, 'phone');
        //$this->najdiShodu('address_phone2', $this->phone, 'phone');
        //$this->najdiShodu('altaddress_cell', $this->phone, 'phone');
        //$this->najdiShodu('altaddress_phone', $this->phone, 'phone');
        //$this->najdiShodu('altaddress_phone2', $this->phone, 'phone');
        //VIN kod
        $this->najdiShoduVeVin('serialnumber', $this->serialnumber, 'serialnumber');
        //Adresa
        $this->najdiShoduAdresa($this->zipcode, $this->city, $this->street);
        $this->najdiShoduAdresa2($this->zipcode, $this->city, $this->street);
        $this->najdiShoduAdresa3($this->zipcode, $this->city, $this->street);


        $this->nastaveniRegaluKaret();
    }

    private function smazaniStarychZaznamu() {
        $regal_card = Doctrine_Query::create()
                        ->from('Card_regal')
                        ->where("regal_id = '" . $this->id . "'")
                        ->execute();

        $regal_card->delete();

        $q = Doctrine_Query::create()
                        ->update('Card')
                        ->set('regal_connected', '?', FALSE)
                        ->execute();
    }

    private function nastaveniRegaluKaret() {


        $sql = "UPDATE card c SET c.regal_connected = TRUE WHERE exists (SELECT r.id FROM card_regal r WHERE c.id = r.card_id)";

        $q = Doctrine_Manager::getInstance()->getCurrentConnection();
        $q->execute($sql);
    }

    private function najdiShodu($sloupec, $hodnota, $regal) {

        if ($hodnota != '') {

            $dotazy = Doctrine_Query::create()
                            ->from('Card')
                            ->where("{$sloupec} = '" . $hodnota . "'")
                            ->andWhere("{$sloupec} IS NOT NULL")
                            ->execute();


            foreach ($dotazy as $dotaz) {

                $card_regal = new Card_regal();
                $card_regal->card_id = $dotaz->id;
                $card_regal->regal_id = $this->id;
                $card_regal->column_match = $regal;
                $card_regal->save();
            }
        }
    }

    private function najdiShoduVeVin($sloupec, $hodnota, $regal) {

        if ($hodnota != '') {

            $dotazy = Doctrine_Query::create()
                            ->from('Attachment')
                            ->where("{$sloupec} = '" . $hodnota . "'")
                            ->execute();


            foreach ($dotazy as $dotaz) {

                $card_regal = new Card_regal();
                $card_regal->card_id = $dotaz->id;
                $card_regal->regal_id = $this->id;
                $card_regal->column_match = $regal;
                $card_regal->save();
            }
        }
    }

    private function najdiShoduAdresa($psc, $mesto, $adresa) {

        if ($mesto != '' OR $adresa != '') {
            if ($psc == '') {

                $dotazy = Doctrine_Query::create()
                                ->from('Card')
                                ->where("address_city = '" . $mesto . "'")
                                ->andWhere("address_street = '" . $adresa . "'")
                                ->execute();
            } else {
                $dotazy = Doctrine_Query::create()
                                ->from('Card')
                                ->where("address_zipcode = '" . $psc . "'")
                                ->andWhere("address_city = '" . $mesto . "'")
                                ->andWhere("address_street = '" . $adresa . "'")
                                ->execute();
            }


            foreach ($dotazy as $dotaz) {

                $card_regal = new Card_regal();
                $card_regal->card_id = $dotaz->id;
                $card_regal->regal_id = $this->id;
                $card_regal->column_match = 'street';
                $card_regal->save();
            }
        }
    }

    private function najdiShoduAdresa2($psc, $mesto, $adresa) {
        if ($mesto != '' OR $adresa != '') {
          if ($psc == '') {

            $dotazy = Doctrine_Query::create()
                            ->from('Card')
                            ->where("altaddress_city = '" . $mesto . "'")
                            ->andWhere("altaddress_street = '" . $adresa . "'")
                            ->execute();
        } else {
            $dotazy = Doctrine_Query::create()
                            ->from('Card')
                            ->where("altaddress_zipcode = '" . $psc . "'")
                            ->andWhere("altaddress_city = '" . $mesto . "'")
                            ->andWhere("altaddress_street = '" . $adresa . "'")
                            ->execute();
        }

        $unsafeValues = $dotazy;
        $dotazy = array();
        foreach ($unsafeValues AS $value) {
            if (!empty($value) && (strlen(trim($value)) > 0))
                $dotazy[] = $value;
        }

        foreach ($dotazy as $dotaz) {

            $card_regal = new Card_regal();
            $card_regal->card_id = $dotaz->id;
            $card_regal->regal_id = $this->id;
            $card_regal->column_match = 'street';
            $card_regal->save();
          }
        }
    }

    private function najdiShoduAdresa3($psc, $mesto, $adresa) {
        if ($mesto != '' OR $adresa != '') {
          if ($psc == '') {

            $dotazy = Doctrine_Query::create()
                            ->from('Card')
                            ->where("employer_city = '" . $mesto . "'")
                            ->andWhere("employer_street = '" . $adresa . "'")
                            ->execute();
        } else {
            $dotazy = Doctrine_Query::create()
                            ->from('Card')
                            ->where("employer_zipcode = '" . $psc . "'")
                            ->andWhere("employer_city = '" . $mesto . "'")
                            ->andWhere("employer_street = '" . $adresa . "'")
                            ->execute();
        }

        $unsafeValues = $dotazy;
        $dotazy = array();
        foreach ($unsafeValues AS $value) {
            if (!empty($value) && (strlen(trim($value)) > 0))
                $dotazy[] = $value;
        }

        foreach ($dotazy as $dotaz) {

            $card_regal = new Card_regal();
            $card_regal->card_id = $dotaz->id;
            $card_regal->regal_id = $this->id;
            $card_regal->column_match = 'street';
            $card_regal->save();
        
           }
        }
    }

    private function najdiShoduJmena($jmeno, $prijmeni) {

        $hodnota = $jmeno . ' ' . $prijmeni;

        if ($hodnota != '') {

            $dotazy = Doctrine_Query::create()
                            ->from('Card')
                            ->where("client_name = '" . $hodnota . "'")
                            ->execute();


            foreach ($dotazy as $dotaz) {

                $card_regal = new Card_regal();
                $card_regal->card_id = $dotaz->id;
                $card_regal->regal_id = $this->id;
                $card_regal->column_match = 'surname';
                $card_regal->save();
            }
        }
    }

}