<?php
define('cn_VolunteerMemberVolunteerMember', 'VolunteerMember');
require_once ('class/DB/DBObj.php');
require_once ('class/DB/DBTable.php');

class VolunteerMember extends DBObj
{
    const STAFF_LIAISON = 'staff-liaison';
    const MEMBER = 'member';
    const CHAIRS = 'chairs';
    const PRESIDENT = 'president';
    const PAST_PRESIDENT = 'past-president';
    const TREASURER = 'treasurer';
    const DIRECTOR = 'director';

    protected $_parentId = null;
    public $tableName = 'Member';
    public $itemName = 'Member';
    public $itemsName = 'Members';
    protected $_groupPosition = array(
        self::CHAIRS        => 'Chair',
        self::STAFF_LIAISON => 'Staff Liaison',
        self::MEMBER        => 'Member'
    );
    protected $_boardGroupPosition = array(
        self::PRESIDENT      => 'President',
        self::PAST_PRESIDENT => 'Immediate Past President',
        self::TREASURER      => 'President-Elect/Treasurer',
        self::DIRECTOR       => 'Director'
    );
    protected $_groupData = null;

    var $_filterFields = array('Member.last_name', 'Member.first_name', 'Member.middle_initial');

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

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

    public function setId($id)
    {
        $this->id = $id;
        return $this;
    }

    public function setParentId($parentId)
    {
        $this->_parentId = $parentId;
        return $this;
    }

    public function getAllPositions()
    {
        return array_merge($this->_boardGroupPosition, $this->_groupPosition);
    }

    public function getGroupData()
    {
        if (empty($this->_groupData)) {
            $sql = "
                SELECT * FROM {$this->db->tblVolunteer}
                WHERE `id` = " . $this->db->quote($this->_parentId, 'integer');
            $res = $this->db->queryRow($sql);
            if (!$this->isDBError($res)) {
                $this->_groupData = $res;
            }
        }
        return $this->_groupData;
    }

    protected function _initGroupData()
    {
        $this->_groupData = $this->getGroupData();
        return $this;
    }

    public function getParentId()
    {
        return $this->_parentId;
    }

    function _getWhat4Grid($opt = array())
    {
        return parent::_getWhat4Grid($opt) . ', `Member2Volunteer`.`type`, '
            . '`DMncarMemberType`.`title` AS `mncar_type`,'
            . '`DMncarMemberStatus`.`title` AS `mncar_status`,'
            . '`DMncarLMemberType`.`title` AS `mncar_l_type`,'
            . '`DMncarLMemberStatus`.`title` AS `mncar_l_status`,'
            . '`DMiscMemberType`.`title` AS `misc_type`,'
            . '`DMiscMemberStatus`.`title` AS `misc_status`'
            ;
    }

    public function _getJoin4Grid()
    {
        $sql = "
        LEFT JOIN {$this->_getPair('DMncarMemberType')}
            ON `DMncarMemberType`.`id` = `Member`.`id_mncar_type`

        LEFT JOIN {$this->_getPair('DMemberStatus', 'DMncarMemberStatus')}
            ON `DMncarMemberStatus`.`id` = `Member`.`id_mncar_status`

        LEFT JOIN {$this->_getPair('DMncarLMemberType')}
            ON `DMncarLMemberType`.`id` = `Member`.`id_mncar_l_type`

        LEFT JOIN {$this->_getPair('DMemberStatus', 'DMncarLMemberStatus')}
            ON `DMncarLMemberStatus`.`id` = `Member`.`id_mncar_l_status`

        LEFT JOIN {$this->_getPair('DMiscMemberType')}
            ON `DMiscMemberType`.`id` = `Member`.`id_misc_type`

        LEFT JOIN {$this->_getPair('DMemberStatus', 'DMiscMemberStatus')}
            ON `DMiscMemberStatus`.`id` = `Member`.`id_misc_status`

        JOIN {$this->_getPair('Member2Volunteer')}
          ON `Member2Volunteer`.`id_parent` = `Member`.`id`
          AND `Member2Volunteer`.`id_volunteer` = " . $this->db->quote($this->_parentId, 'integer');
        return $sql;
    }

    public function getMemberPosition($memberId)
    {
        $sql = "
            SELECT `type` FROM `{$this->db->tblMember2Volunteer}` AS `Member2Volunteer`
            WHERE `id_parent` = ". $this->db->quote($memberId, 'integer') . "
                AND `id_volunteer` = " . $this->db->quote($this->_parentId, 'integer') . "
        ";
        return $this->db->queryOne($sql);
    }

    function delete()
    {
        $where = '
            `id_parent` = ' . $this->db->quote($this->id, 'integer') . '
            AND `id_volunteer` = ' . $this->db->quote($this->_parentId, 'integer') . '
        ';
        $this->getTable('Member2Volunteer')->delete($where);
        return $this;
    }

    public function getMembers4Ajax($cFilter = null)
    {
        $cFilter = '%' . $this->db->escape($cFilter) . '%';
        $sql = "
            SELECT
                `Member`.`id`,
                `Member`.`id_misc_status`,
                `Member`.`id_misc_type`,
                CONCAT(
                    `last_name`,
                    ', ',
                    `first_name`,
                    ' ',
                    `Member`.`middle_initial`,
                    IF(`Member`.`middle_initial` <> '','. ',''),
                    '- ',
                    IFNULL(`Location`.`name`,'no location')
                ) AS `member`
            FROM `{$this->db->tblMember}` AS `Member`
            LEFT JOIN `{$this->db->tblLocation}` AS `Location`
                ON `Location`.`id` = `Member`.`id_location`
            LEFT JOIN `{$this->db->tblMember2Volunteer}` AS `Member2Volunteer`
                ON (
                    `Member2Volunteer`.`id_parent` = `Member`.`id`
                    AND `Member2Volunteer`.`id_volunteer` = " . $this->db->quote($this->_parentId, 'integer') .  "
                )
            WHERE
                (
                    `first_name` LIKE '" . $cFilter . "'
                    OR `last_name` LIKE '" . $cFilter . "'
                    OR `email` LIKE '" . $cFilter . "'
                    OR CONCAT(`first_name`, ' ', `last_name`, ' - ', IFNULL(`Location`.`name`,'no location')) LIKE '" . $cFilter . "'
                )
                AND (
                    `id_mncar_status` IN (1, 3)
                    OR `id_mncar_l_status` IN (1, 3)
                    OR `id_misc_status` IN (1, 3)
                )
                AND `Member2Volunteer`.`id_parent` IS NULL"  . "
            ORDER BY `last_name`, `first_name`";
        $members = $this->db->queryAll($sql);

        return $this->isDBError($members) ? null : $members;
    }

    public function getGroupPositions()
    {
        $this->_initGroupData();
        if ('board' != $this->_groupData['type']) {
            return $this->_groupPosition;
        } else {
            return $this->_boardGroupPosition;
        }
    }

    function insert($data = null, $files = null)
    {
        $this->table = new DBTable('Member2Volunteer');
        return parent::insert($data, $files);
    }

    function update($data = null, $files = null)
    {
        $this->table = new DBTable('Member2Volunteer');
        return parent::update($data, $files);
    }

    public function isPositionAvailable4Member($memberId, $position)
    {
        $groupPosition = $this->getGroupPositions();

        if (!in_array($position, array_keys($groupPosition))) {
            return false;
        }

        $this->id = $memberId;
        if (empty($this->_data)) {
            $this->initFromDB();
        }
        $memberData = $this->getData();

        /* if try to set staff liaison for not Misc:Staff:Active */
        if (self::STAFF_LIAISON == $position && !(1 == $memberData['id_misc_status'] && 2 == $memberData['id_misc_type'])) {
            return false;
        }

        foreach (array('id_mncar_status', 'id_mncar_l_status', 'id_misc_status') as $status) {
            if (in_array($memberData[$status], array(1, 3))) {
                return true;
            }
        }

        return false;
    }

    public function updatePosition($memberId, $position)
    {
        $where = '`id_parent` = ' . $this->db->quote($memberId, 'integer')
            . ' AND `id_volunteer` = ' . $this->db->quote($this->_parentId, 'integer');
        $this->getTable('Member2Volunteer')->update(array('type' => $position), $where);
        return $this;
    }

}