<?php

class App_Search_Tool_Obj extends App_Process_Obj
{
    protected $_hasFilter = true;

    public function getListSelect($options = array())
    {
        set_time_limit(0);
        ini_set('memory_limit', '2048M');
        $versionFilterSelect = $this->_db->select()->reset();
        $versionFilterSelect->from(
                Qs_Db::getPair('Part2ProcessData', 'p2pd2'),
                array(new Zend_Db_Expr('MAX(p2pd2.id)')))
            ->where('p2pd2.processId = p2pd.processId');
        if ($this->_filter['currentRevision'] == 'y') {
            $versionFilterSelect
                ->join(Qs_Db::getPair('PartVersion', 'pv2'), 'pv2.id = p2pd2.versionId', array())
                ->where('pv2.partId = p.id AND pv2.id <= p.currentVersion');
        } else {
            $versionFilterSelect->where('p2pd2.versionId = pv.id');
        }
        $this->select = $this->_db->select()->reset()
            ->from(Qs_Db::getPair('Process', 'pr'), array('processName' => 'name', 'processId' => 'id'))
            ->join(Qs_Db::getPair('Part2Process', 'p2p'), 'pr.id = p2p.processId', array())
            ->join(Qs_Db::getPair('Part', 'p'), 'p.id = p2p.partId', array('partId' => 'id'))
            ->join(Qs_Db::getPair('PartVersion', 'pv'), 'pv.partId = p.id AND pv.id <= p.currentVersion',
                array('partName' => 'name', 'partNumber' => 'number', 'versionId' => 'id', 'revisionDate' => 'changed',
                    'revision' => new Zend_Db_Expr('CONCAT(\'v\', CASE pv.preProduction WHEN \'y\' THEN \'PP\' END, pv.version)')))
            ->join(Qs_Db::getPair('PartVersion', 'pvc'), 'pvc.partId = p.id AND pvc.id = p.currentVersion',
                array('revisionDateCurrent' => 'changed',
                    'revisionCurrent' => new Zend_Db_Expr('CONCAT(\'v\', CASE pvc.preProduction WHEN \'y\' THEN \'PP\' END, pvc.version)')))
            ->join(Qs_Db::getPair('Part2ProcessData', 'p2pd'), 'p2pd.processId IN (0, pr.id) AND p2pd.versionId = pv.id',
                array('setId'))
            ->join(Qs_Db::getPair('FormElement', 'fe'),
                'fe.formType = \'process\' AND fe.formId = pr.id AND fe.draftFor IS NULL'
                , array('elementId' => 'id'))
            ->joinLeft(Qs_Db::getPair('FormSubmissionValue', 'fsv'),
                'fsv.submissionId = p2pd.id AND fsv.elementId = fe.id', array())
            ->joinLeft(Qs_Db::getPair('FormElementPropertyValue', 'fel'),
                'fel.elementId = fe.id AND fel.propertyId = 1', array())
            ->joinLeft(Qs_Db::getPair('FormElementPropertyValue', 'fen'),
                'fen.elementId = fe.id AND fen.propertyId = 8',
                array('element' => new Zend_Db_Expr('ISNULL(fel.value, fen.value)')))
            ->where('p2pd.id = (' . $versionFilterSelect . ')');

        if ($this->_filter['partNumber']) {
            $this->select->where('pv.number LIKE ?', '%' . $this->_filter['partNumber'] . '%');
        }
        if ($this->_filter['element']) {
            $this->select->where('ISNULL(fel.value, fen.value) LIKE ?', '%' . $this->_filter['element'] . '%');
        }
        if ($this->_filter['process']) {
            $this->select->where('pr.name LIKE ?', '%' . $this->_filter['process'] . '%');
        }

        $this->select->where('fsv.value LIKE ?', '%' . $this->_filter['query'] . '%');

        $this->_prepareListOptions($options);
        return $this->select;
    }

    public function getList($options = array())
    {
        if ($this->_filter['query']) {
            return parent::getList($options);
        }
        return array();

    }


}