<?php
define('cn_VolunteerVolunteer', 'Volunteer');
require_once ('app/Volunteer/Member/VolunteerMember.php');
require_once ('class/DB/DBObj.php');

class Volunteer extends DBObj {

    const ALIAS = 'resource/volunteer';
    public $_filterFields = array('title');
    public $tableName = 'Volunteer';
    public $itemName = 'Group';
    public $itemsName = 'Groups';
    var $_urlParseFields = array('mission');
    protected $_filter = array();

    /** @var Volunteer */
    protected static $_instance;

    public static function getInstance($id = null)
    {
        if (empty(self::$_instance)) {
            self::$_instance = new self($id);
        }
        return self::$_instance;
    }

    public function getVolunteers4Menu($alias = '')
    {
        $sql = "
            SELECT CONCAT(" . $this->db->quote($alias . '/') .  ", `id`) AS `alias`, `title`, 'volunteer' as `id_parent`
            FROM {$this->tableNameDB}
            WHERE `type` IN ('taskforce', 'committee')
            ORDER BY `sorter`
        ";
        return $this->db->queryAll($sql);
    }

    protected function _getMembersCountSelect()
    {
        return '
            SELECT COUNT(*) FROM `mn_Member2Volunteer` AS `Member2Volunteer`
            JOIN `mn_Member` as `m` ON `m`.`id`  = `Member2Volunteer`.`id_parent`
            WHERE `Member2Volunteer`.`id_volunteer` = `' . $this->tableName . '`.`id`
        ';
    }

    protected function _getMembersMailTo()
    {
        return "
            SELECT GROUP_CONCAT(`email` SEPARATOR ', ') FROM `mn_Member` AS `Member`
            JOIN `mn_Member2Volunteer` AS `Member2Volunteer` ON `Member2Volunteer`.`id_parent` = `Member`.`id`
            WHERE `Member2Volunteer`.`id_volunteer` = `{$this->tableName}`.`id`
        ";
    }

    function _getWhat4Grid($opt = array())
    {
        return "
            {$this->tableName}.*,
            ({$this->_getMembersCountSelect()}) AS `membersCount`,
            ({$this->_getMembersMailTo()}) AS `mailToList`
        ";
    }

    protected function _getSorterCondition()
    {
        return "`sorter` IS NOT NULL " ;
    }

    public function insert($data = null, $files = null)
    {
        if (!is_null($data)) {
            $this->initFromArray($data, $files);
        }
        $this->_initTable();
        $this->_beforeInsert();
        $data = $this->_data;
        unset($data['id']);
        $fields = $this->getSimpleFieldList();
        if (isset($fields['sorter'])) {
            $data['sorter'] = 'board' == $data['type']
                            ? null
                            : $this->getSorter($this->_getSorterCondition());
        }
        $this->id = $this->table->insert($data);
        $this->_insertDependency();
        $this->handleFiles();
        return $this->id;
    }

    function getSorter($where = '1', $field = 'sorter', $table_name_db = '')
    {
        if ($table_name_db == '') {
            $table_name_db = $this->tableNameDB;
        }

        $fields = $this->getSimpleFieldList($table_name_db);
        if (!isset($fields[$field])) {
            return false;
        }

        $sql = "select min({$field}) from {$table_name_db} where " . $where;
        $sorter = $this->db->queryOne($sql);
        if ($this->isDBError($sorter)) {
            $sorter = 0;
        }
        $sorter = (int)$sorter;
        --$sorter;
        return $sorter;
    }

    public function _insertDependency()
    {
        $this->_saveDependency();
        parent::_insertDependency();
        return $this;
    }

    public function _updateDependency()
    {
        $this->_saveDependency();
        parent::_updateDependency();
        return $this;
    }

    protected function _saveDependency()
    {
        $this->deleteIds($this->tableNameDB . 'Goal', 'id_volunteer');
        if (!empty($this->_data['goals']['title']) && is_array($this->_data['goals']['title'])) {
            foreach ($this->_data['goals']['title'] as $key => $goal) {
                if (!empty($goal)) {
                    $data = array(
                        'sorter'       => $key,
                        'goal'         => $goal,
                        'id_volunteer' => $this->id,
                    );
                    $this->getTable($this->tableName . 'Goal')->insert($data);
                }
            }
        }
        return $this;
    }

    public function getGoals()
    {
        $sql = "
            SELECT `goal` FROM {$this->db->tblVolunteerGoal}
            WHERE `id_volunteer` = " . $this->db->quote($this->id, 'integer');
        ;
        $res = $this->db->queryCol($sql);
        if ($this->isDBError($res)) {
            return array();
        }
        return $res;
    }

    /**
     * method change positions of all members at group to 'member' or 'director' (for board group)
     * @param $groupType
     * @return Volunteer
     */
    protected function _setDefaultType($groupType)
    {
        $type = 'board' == $groupType ? VolunteerMember::DIRECTOR : VolunteerMember::MEMBER;
        $where = "`id_volunteer` = " . $this->db->quote($this->id, 'integer');
        $this->getTable('Member2Volunteer')->update(array('type' => $type), $where);
        return $this;
    }

    public function _beforeUpdate()
    {
        //check does we need to change group's members types to member
        $oldData = $this->getFromDB($this->id);
        if ($oldData['type'] != $this->_data['type']) {
            if (in_array('board', array($oldData['type'], $this->_data['type']))) {
                $this->_setDefaultType($this->_data['type']);
            }
        }
        if ('board' == $this->_data['type']) {
            $this->_data['sorter'] = null;
        } elseif ('board' == $this->getFromDB($this->id, 'type')) {
            $this->_data['sorter'] = $this->getSorter($this->_getSorterCondition());
        }
        return $this;
    }

    public function getIdOfFirstDaughterPage()
    {
        $sql = "SELECT `id`  FROM {$this->tableNameDB} WHERE `type` != 'board' ORDER BY `sorter` LIMIT 1";
        return $this->db->queryOne($sql);
    }

    public function getMembers()
    {
        $sql = "
            SELECT
                `Member`.`id`,
                `Member`.`last_name`,
                `Member`.`first_name`,
                `Member`.`middle_initial`,
                `Member`.`email`,
                `Member`.`primary_phone`,
                `Member`.`img`,
                `Member`.`id_mncar_type`,
                `Member`.`id_mncar_status`,
                `Member`.`id_mncar_l_type`,
                `Member`.`id_mncar_l_status`,
                `Member`.`id_misc_type`,
                `Member`.`id_misc_status`,
                `Member2Volunteer`.`type` AS `type`
            FROM `{$this->db->tblMember}` AS `Member`

            JOIN `{$this->db->tblMember2Volunteer}` AS `Member2Volunteer`
                ON (
                    `Member2Volunteer`.`id_parent` = `Member`.`id`
                    AND `Member2Volunteer`.`id_volunteer` = " . $this->db->quote($this->id, 'integer') . "
                )

            ORDER BY `last_name`, `first_name`";
        $members = $this->db->queryAll($sql);
        $this->isDBError($members);
        foreach ($members as &$member) {
            $member['active'] = Member::isMemberAvailableForFindABroker($member);
        }
        unset($member);
        return $members;
    }

    function clearFilter()
    {
        $this->_filter = array();
        $this->_filterQuery = '';
    }

    function addFilter($filter)
    {
        if (is_string($filter)) {
            $this->setFilterValue('query', $filter);
        } elseif (is_array($filter)) {
            foreach ($filter as $field => $value) {
                $this->setFilterValue($field, $value);
            }
        }
    }

    function setFilterValue($field, $value)
    {
        if ('query' == $field) {
            $this->_filterQuery = $value;
        } else {
            $this->_filter[$field] = $value;
        }
    }

    function setFilter($filter)
    {
        $this->clearFilter();
        $this->addFilter($filter);
    }

    function getFilter($field = false)
    {
        return Qs_Array::get($this->_filter, $field);
    }

    function _filterSql()
    {
        require_once('class/DB/DBObj/Filter.php');

        $queryFilter = DBObj_Filter::perseQr($this->db, $this->_filterFields, $this->_filterQuery);

        if (!empty($this->_filter) && is_array($this->_filter)) {
            foreach ($this->_filter as $field => $value) {
                if (!empty($value)) {
                    $queryFilter .= " AND `{$field}` LIKE {$this->db->quote('%' . $value . '%')} ";
                }
            }
        }
        return $queryFilter;
    }
}