<?php
define('cn_EventEventMember', 'EventMember');
require_once ('class/DB/DBObj.php');
class EventMember extends DBObj
{
    /** @var EventMember */
    protected static  $_instance = null;

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

    protected function _getWhat4MemberSearchQuery($type)
    {
        $sql = "
            SELECT
                `Member`.*,
                CONCAT(
                    `last_name`,
                    ', ',
                    `first_name`,
                    ' ',
                    `Member`.`middle_initial`,
                    IF(`Member`.`middle_initial` <> '', '. ', ''),
        ";

        if ('company' == $type) {
            $sql .= " IF(`Company`.`name` IS NULL,'', CONCAT(' - ', `Company`.`name`))";
        } else {
            $sql .= "IFNULL(`Location`.`name`,'no location')";
        }

        $sql .= ") AS `member`";

        $sql .= " FROM {$this->_getPair('Member')}";
        return $sql;
    }

    protected function _getJoins4MemberSearchQuery($type)
    {
        $sql = "
            LEFT JOIN {$this->_getPair('EventFormReq')}
                ON `Member`.`id` = `EventFormReq`.`id_member`
                AND `EventFormReq`.`deleted` = 'n'
                AND `EventFormReq`.`id_event` = {$this->db->quote($_REQUEST['id_event'], 'integer')}
            LEFT JOIN {$this->_getPair('Location')}
                ON `Location`.`id` = `Member`.`id_location`";

        if ('company' == $type) {
            $sql .= "
            LEFT JOIN {$this->_getPair('Company')}
                ON `Company`.`id` = `Location`.`id_company`
            ";
        }
        return $sql;
    }

    protected function _getWhere4memberSearchQuery($type, $filter, $excludedMemberId = null)
    {
        $sql = "
            WHERE
                (
                    `first_name` LIKE'%{$this->db->escape($filter)}%'
                    OR `last_name` LIKE '%{$this->db->escape($filter)}%'
                    OR `email` LIKE '%{$this->db->escape($filter)}%'
        ";

        if ('company' == $type) {
            $sql .= "
                   OR CONCAT(`first_name`, ' ', `last_name`) LIKE '%{$this->db->escape($filter)}%'
            ";
        } else {
            $sql .= "
                  OR CONCAT(`first_name`, ' ', `last_name`, ' - ',IFNULL(`Location`.`name`,'no location'))
                        LIKE '%{$this->db->escape($filter)}%'
            ";
        }

        $sql .= "
                )
                AND `EventFormReq`.`id_member` IS NULL
        ";

        if (null !== $excludedMemberId) {
            $sql .= "
                AND `Member`.`id` != {$this->db->quote($excludedMemberId, 'integer')}
            ";
        }
        return $sql;
    }

    protected function _getOrder4MemberSearchQuery()
    {
        return "  ORDER BY `last_name`, `first_name`";
    }

    protected function _getQuery4MemberSearch($type, $filter, $excludedMemberId = null)
    {
        $sql = $this->_getWhat4MemberSearchQuery($type);
        $sql .= $this->_getJoins4MemberSearchQuery($type);
        $sql .= $this->_getWhere4memberSearchQuery($type, $filter, $excludedMemberId);
        $sql .= $this->_getOrder4MemberSearchQuery();
        return $sql;
    }

    /**
     * @param string $filter
     * @param int|null $excludedMemberId
     * @return array
     */
    public function getMembersWithCompanyForEventRegistration($filter, $excludedMemberId = null)
    {
        $members = $this->db->queryAll($this->_getQuery4MemberSearch('company', $filter, $excludedMemberId));
        return Qs_Db::isError($members) ? array() : $members;
    }

    /**
     * @param string $filter
     * @param int|null $excludedMemberId
     * @return array
     */
    public function getMembersWithLocationForEventRegistration($filter, $excludedMemberId = null)
    {
        $members = $this->db->queryAll($this->_getQuery4MemberSearch('location', $filter, $excludedMemberId));
        return Qs_Db::isError($members) ? array() : $members;
    }
}