<?php
/**
 * The model file of search module of ZDOO.
 *
 * @copyright   Copyright 2009-2018 青岛易软天创网络科技有限公司(QingDao Nature Easy Soft Network Technology Co,LTD, www.cnezsoft.com)
 * @license     ZPL (http://zpl.pub/page/zplv12.html)
 * @author      Chunsheng Wang <chunsheng@cnezsoft.com>
 * @package     search
 * @version     $Id: model.php 5082 2013-07-10 01:14:45Z wyd621@gmail.com $
 * @link        http://www.zdoo.com
 */
?>
<?php
class searchModel extends model
{

    /**
     * Set search params to session. 
     * 
     * @param  array    $searchConfig 
     * @access public
     * @return void
     */
    public function setSearchParams($searchConfig)
    {
        $searchParams['module']       = $searchConfig['module'];
        $searchParams['searchFields'] = json_encode($searchConfig['fields']);
        $searchParams['fieldParams']  = json_encode($searchConfig['params']);
        $searchParams['actionURL']    = $searchConfig['actionURL'];
        $searchParams['queryID']      = isset($searchConfig['queryID']) ? $searchConfig['queryID'] : 0;

        /* remove onlybody for url*/
        $onlybodyParam = $this->config->requestType == 'GET' ? '&onlybody=yes' : '?onlybody=yes';
        $searchParams['actionURL'] = str_replace($onlybodyParam, '', $searchParams['actionURL']);

        $this->session->set('searchParams', $searchParams);
    }

    /**
     * Build the query to execute.
     * 
     * @access public
     * @return void
     */
    public function buildQuery()
    {
        /* Init vars. */
        $where      = '';
        $groupItems = $this->config->search->groupItems;
        $groupAndOr = strtoupper($this->post->groupAndOr);
        if($groupAndOr != 'AND' and $groupAndOr != 'OR') $groupAndOr = 'AND';

        for($i = 1; $i <= $groupItems * 2; $i ++)
        {
            /* The and or between two groups. */
            if($i == 1) $where .= '(( 1  ';
            if($i == $groupItems + 1) $where .= " ) $groupAndOr ( 1 ";

            /* Set var names. */
            $fieldName    = "field$i";
            $andOrName    = "andOr$i";
            $operatorName = "operator$i";
            $valueName    = "value$i";

            /* Skip empty values. */
            if($this->post->$valueName == false) continue; 
            if($this->post->$valueName == 'null') $this->post->$valueName = '';  // Null is special, stands to empty.
            if($this->post->$valueName == 'ZERO') $this->post->$valueName = 0;   // ZERO is special, stands to 0.

            /* Set and or. */
            $andOr = strtoupper($this->post->$andOrName);
            if($andOr != 'AND' and $andOr != 'OR') $andOr = 'AND';
            $where .= " $andOr ";

            /* Set filed name. not use `` if has alias. */
            if(strpos($this->post->$fieldName, '.') === false)
            {
                $where .= '`' . $this->post->$fieldName . '` ';
            }
            elseif($this->post->$fieldName == 'contract.productLine')
            {
                /* Search contracts by product or product line. */
                $where .= ' id ';
            }
            else
            {
                $where .= $this->post->$fieldName . ' ';
            }

            /* Set operator. */
            $value    = trim($this->post->$valueName);
            $operator = $this->post->$operatorName;
            if(!isset($this->lang->search->operators[$operator])) $operator = '=';
            if($operator == '=' and $this->post->$fieldName == 'createdDate') $operator = 'include';

            if($this->post->$fieldName == 'contract.productLine')
            {
                $productLines = $this->dao->select('id')->from(TABLE_CATEGORY)
                    ->where('type')->eq('product')
                    ->beginIF($operator == "include" || $operator == "notinclude")->andWhere('name')->like("%$value%")->fi()
                    ->beginIF($operator == "=" || $operator == "!=")->andWhere('name')->eq($value)->fi()
                    ->fetchPairs();
                $products = $this->dao->select('id')->from(TABLE_PRODUCT)
                    ->where('category')->in($productLines)
                    ->beginIF($operator == "include" || $operator == "notinclude")->orWhere('name')->like("%$value%")->fi()
                    ->beginIF($operator == "=" || $operator == "!=")->orWhere('name')->eq($value)->fi()
                    ->fetchPairs();
                $orders = array();
                foreach($products as $product)
                {
                    $orders += $this->dao->select('id')->from(TABLE_ORDER)->where('product')->like("%,$product,%")->fetchPairs();
                }
                $contracts = $this->dao->select('contract')->from(TABLE_CONTRACTORDER)->where('`order`')->in($orders)->fetchPairs();
            }

            if($operator == "include")
            {
                if($this->post->$fieldName == 'contract.productLine')
                {
                    $where .= helper::dbIN($contracts);
                }
                else
                {
                    if($this->post->$fieldName == 'o.product') $value = ',' . $value . ',';
                    if($this->post->module == 'contract' && $this->post->$fieldName == 'product') $value = ',' . $value . ',';
                    $where .= ' LIKE ' . $this->dbh->quote("%$value%");
                }
            }
            elseif($operator == "notinclude")
            {
                if($this->post->$fieldName == 'contract.productLine')
                {
                    $where .= ' NOT ' . helper::dbIN($contracts);
                }
                else
                {
                    $where .= ' NOT LIKE ' . $this->dbh->quote("%$value%"); 
                }
            }
            elseif($operator == 'belong')
            {
                if($this->post->$fieldName == 'module' or $this->post->$fieldName == 'area' or $this->post->$fieldName == 'industry')
                {
                    if($value == 0) $where .= ' RLIKE ' . $this->dbh->quote("[0-9]*");
                    $allModules = $this->loadModel('tree')->getAllChildId($value);
                    if($allModules) $where .= helper::dbIN($allModules);
                }
                elseif($this->post->$fieldName == 'dept')
                {
                    $allDepts = $this->loadModel('dept')->getAllChildId($value);
                    $where .= helper::dbIN($allDepts);
                }
                elseif($this->post->$fieldName == 'category')
                {
                    $categories = $this->loadModel('tree')->getFamily($value);
                    $where .= helper::dbIn($categories);
                }
                elseif($this->post->$fieldName == 'contract.productLine')
                {
                    $where .= " = '-1'";
                }
                else
                {
                    $where .= ' = ' . $this->dbh->quote($value) . ' ';
                }
            }
            else
            {
                if($this->post->$fieldName == 'contract.productLine')
                {
                    if($operator == '=') 
                    {
                        $where .= helper::dbIN($contracts);
                    }
                    elseif($operator == '!=') 
                    {
                        $where .= ' NOT ' . helper::dbIN($contracts);
                    }
                    else 
                    {
                        $where .= " = '-1'";
                    }
                }
                else
                {
                    $where .= $operator . ' ' . $this->dbh->quote($value) . ' ';
                }
            }
        }

        $where .=" ))";

        /* Save to session. */
        $querySessionName = $this->post->module . 'Query';
        $formSessionName  = $this->post->module . 'Form';
        $this->session->set($querySessionName, $where);
        $this->session->set($formSessionName,  $_POST);
    }

    /**
     * Init the search session for the first time search.
     * 
     * @param  string   $module 
     * @param  array    $fields 
     * @param  array    $fieldParams 
     * @access public
     * @return void
     */
    public function initSession($module, $fields, $fieldParams)
    {
        $formSessionName  = $module . 'Form';
        if($this->session->$formSessionName != false) return;

        for($i = 1; $i <= $this->config->search->groupItems * 2; $i ++)
        {
            /* Var names. */
            $fieldName    = "field$i";
            $andOrName    = "andOr$i";
            $operatorName = "operator$i";
            $valueName    = "value$i";

            $currentField = key($fields);
            $operator     = isset($fieldParams[$currentField]['operator']) ? $fieldParams[$currentField]['operator'] : '=';

            $queryForm[$fieldName]    = key($fields);
            $queryForm[$andOrName]    = 'and';
            $queryForm[$operatorName] = $operator;
            $queryForm[$valueName]    =  '';

            if(!next($fields)) reset($fields);
        }
        $queryForm['groupAndOr'] = 'and';
        $this->session->set($formSessionName, $queryForm);
    }

    /**
     * Set default params for selection.
     * 
     * @param  array  $fields 
     * @param  array  $params 
     * @access public
     * @return array
     */
    public function setDefaultParams($fields, $params)
    {
        $hasProduct = false;
        $hasProject = false;
        $hasUser    = false;
        $hasDept    = false;

        $fields     = array_keys($fields);
        foreach($fields as $fieldName)
        {
            if(empty($params[$fieldName])) continue;
            if($params[$fieldName]['values'] == 'products') $hasProduct = true;
            if($params[$fieldName]['values'] == 'users')    $hasUser    = true;
            if($params[$fieldName]['values'] == 'depts')    $hasDept    = true;
            if($params[$fieldName]['values'] == 'projects') $hasProject = true;
        }

        if($hasUser)
        {
            $users         = $this->loadModel('user')->getPairs('nodeleted,noforbidden');
            $users['$@me'] = $this->lang->search->me;
        }
        if($hasDept)    $depts    = array('' => '') + $this->loadModel('tree')->getOptionMenu('dept');
        if($hasProduct) $products = array('' => '') + $this->loadModel('product')->getPairs();
        if($hasProject) $projects = array('' => '') + $this->loadModel('project', 'proj')->getPairs();

        foreach($fields as $fieldName)
        {
            if(!isset($params[$fieldName])) $params[$fieldName] = array('operator' => '=', 'control' => 'input', 'values' => '');
            if($params[$fieldName]['values'] == 'users')    $params[$fieldName]['values']  = $users;
            if($params[$fieldName]['values'] == 'depts')    $params[$fieldName]['values']  = $depts;
            if($params[$fieldName]['values'] == 'products') $params[$fieldName]['values']  = $products;
            if($params[$fieldName]['values'] == 'projects') $params[$fieldName]['values']  = $projects;
            if(is_array($params[$fieldName]['values']))
            {
                /* For build right sql when key is 0 and is not null.  e.g. confirmed field. */
                if(isset($params[$fieldName]['values'][0]) and $params[$fieldName]['values'][0] !== '')
                {
                    $params[$fieldName]['values'] = array('ZERO' => $params[$fieldName]['values'][0]) + $params[$fieldName]['values'];
                    unset($params[$fieldName]['values'][0]);
                }
                else
                {
                    $params[$fieldName]['values']  = $params[$fieldName]['values'] + array('null' => $this->lang->search->null);
                }
            }
        }
        return $params;
    }

    /**
     * Get a query.
     * 
     * @param  int    $queryID 
     * @access public
     * @return string
     */
    public function getQuery($queryID)
    {
        $query = $this->dao->findByID($queryID)->from(TABLE_USERQUERY)->fetch();
        if(!$query) return false;

        /* Decode html encode. */
        $query->form = htmlspecialchars_decode($query->form, ENT_QUOTES);
        $query->sql  = htmlspecialchars_decode($query->sql, ENT_QUOTES);

        $query->form = unserialize($query->form);
        $query->sql  = $this->replaceDynamic($query->sql);
        return $query;
    }

    /**
     * Save current query to db.
     * 
     * @access public
     * @return void
     */
    public function saveQuery()
    {
        $sqlVar  = $this->post->module  . 'Query';
        $formVar = $this->post->module  . 'Form';
        $sql     = $this->session->$sqlVar;
        if(!$sql) $sql = ' 1 = 1 ';

        $query = fixer::input('post')
            ->add('account', $this->app->user->account)
            ->add('form', serialize($this->session->$formVar))
            ->add('sql',  $sql)
            ->get();
        $this->dao->insert(TABLE_USERQUERY)->data($query)->autoCheck()->check('title', 'notempty')->exec();
    }

    /**
     * Get title => id pairs of a user.
     * 
     * @param  string    $module 
     * @access public
     * @return array
     */
    public function getQueryPairs($module)
    {
        $queries = $this->dao->select('id, title')
            ->from(TABLE_USERQUERY)
            ->where('account')->eq($this->app->user->account)
            ->andWhere('module')->eq($module)
            ->orderBy('id_asc')
            ->fetchPairs();
        if(!$queries) return array('' => $this->lang->search->myQuery);
        $queries = array('' => $this->lang->search->myQuery) + $queries;
        return $queries;
    }

    /**
     * Get records by the conditon.
     * 
     * @param  string    $module 
     * @param  string    $moduleIds 
     * @param  string    $conditions 
     * @access public
     * @return array
     */
    public function getBySelect($module, $moduleIds, $conditions)
    {
        if($module == 'task')
        {
            $pairs = 'id,name';
            $table = TABLE_TASK;
        }
        $query    = '`' . $conditions['field1'] . '`';
        $operator = $conditions['operator1'];
        $value    = $conditions['value1'];
        
        if(!isset($this->lang->search->operators[$operator])) $operator = '=';
        if($operator == "include")
        {
            $query .= ' LIKE ' . $this->dbh->quote("%$value%");
        }
        elseif($operator == "notinclude")
        {
            $where .= ' NOT LIKE ' . $this->dbh->quote("%$value%"); 
        }
        else
        {
            $query .= $operator . ' ' . $this->dbh->quote($value) . ' ';
        }
        
        foreach($moduleIds as $id)
        {
            if(!$id) continue;
            $title = $this->dao->select($pairs)
                ->from($table)
                ->where('id')->eq((int)$id)
                ->andWhere($query)
                ->fetch();
            if($title) $results[$id] = $title;
        }
        if(!isset($results)) return array();
        return $this->formatResults($results, $module);
    }

    /**
     * Format the results.
     * 
     * @param  array    $results 
     * @param  string   $module 
     * @access public
     * @return array
     */
    public function formatResults($results, $module)
    {
        /* Get title field. */
        $title = ($module == 'story') ? 'title' : 'name';
        $resultPairs = array('' => '');
        foreach($results as $result) $resultPairs[$result->id] = $result->id . ':' . $result->$title;
        return $resultPairs;
    }

    /**
      Replace dynamic account and date. 
     * 
     * @param  string $query 
     * @access public
     * @return string 
     */
    public function replaceDynamic($query)
    {
        $this->app->loadClass('date');
        $lastWeek  = date::getLastWeek();
        $thisWeek  = date::getThisWeek();
        $lastMonth = date::getLastMonth();
        $thisMonth = date::getThisMonth();
        $thisYear  = date::getThisYear();
        $lastYear  = date::getLastYear();
        $yesterday = date::yesterday();
        $today     = date(DT_DATE1);
        if(strpos($query, '$') !== false)
        {
            $query = str_replace('$@me', $this->app->user->account, $query);
            $query = str_replace("'\$lastYear'",  "'" . $lastYear['begin']       . "' and '" . $lastYear['end']         . "'", $query);
            $query = str_replace("'\$thisYear'",  "'" . $thisYear['begin']       . "' and '" . $thisYear['end']         . "'", $query);
            $query = str_replace("'\$lastMonth'", "'" . $lastMonth['begin']      . "' and '" . $lastMonth['end']        . "'", $query);
            $query = str_replace("'\$thisMonth'", "'" . $thisMonth['begin']      . "' and '" . $thisMonth['end']        . "'", $query);
            $query = str_replace("'\$lastWeek'",  "'" . $lastWeek['begin']       . "' and '" . $lastWeek['end']         . "'", $query);
            $query = str_replace("'\$thisWeek'",  "'" . $thisWeek['begin']       . "' and '" . $thisWeek['end']         . "'", $query);
            $query = str_replace("'\$yesterday'", "'" . $yesterday . ' 00:00:00' . "' and '" . $yesterday . ' 23:59:59' . "'", $query);
            $query = str_replace("'\$today'",     "'" . $today     . ' 00:00:00' . "' and '" . $today     . ' 23:59:59' . "'", $query);
        }
        return $query;
    }

    /** 
     * replace defined table names. 
     * 
     * @param  string $sql 
     * @access public
     * @return void
     */
    public function replaceTableNames($sql)
    {   
        if(preg_match_all("/TABLE_[A-Z]+/", $sql, $out))
        {   
            foreach($out[0] as $table)
            {   
                if(!defined($table)) continue;
                $sql = str_replace($table, trim(constant($table), '`'), $sql);
            }   
        }    
        return $sql;
    } 
}
