<?php

define('cn_EventEvent', 'Event');
define('SHOW_ALL_EVENTS', 0);
define('SHOW_EDUCATION_EVENTS', 1);
define('SHOW_EXCHANGE_EVENTS', 2);
define('SHOW_MONTHLY_PROGRAMS', 3);

require_once ('class/DB/DBObj.php');

class Event extends DBObj
{
    const GROUP_TYPE = 4;
    var $type = 'announcement';
    var $tableName = 'Event';
    var $_urlParseFields = array('description');
    var $_datetimeFields = array('eventdate', 'registration_start', 'registration_end');
    var $_timeFields = array('time_end');
    var $_filterFields = array('name', 'description');
    var $eventtype = 0;
    var $eventyear = 0;
    var $_member = array();

    function Event($id = null)
    {
        $this->DBObj($id);
        $this->_filterFields[] = "{$this->tableNameDB}Type.title";
    }

    function getTypes()
    {
        $sql = "SELECT id, title FROM {$this->tableNameDB}Type ORDER BY sorter";
        $res = $this->db->queryAll($sql, null, MDB_FETCHMODE_DEFAULT, true);
        if ($this->isDBError($res) || !is_array($res)) {
            $res = array();
        }
        return $res;
    }

    function setMember(array $member)
    {
        $this->_member = $member;
        return $this;
    }

    function getMember($field = false, $default = null)
    {
        return Qs_Array::get($this->_member, $field, $default);
    }

    public function isRegistered()
    {
        $sql = 'SELECT 1 '
            . 'FROM ' . $this->_getPair('EventFormReq', 'efr') . ' '
            . 'WHERE 1 '
            . 'AND (`efr`.`id_event` = ' . $this->db->quote($this->id, 'integer') . ' '
            . '    OR (`efr`.`id_event` = ' . $this->db->quote($this->getData('parent_event_id'), 'integer') . ') '
            . '    AND ' . (int) $this->getData('parent_event_id') . ' > 0'
            . ') '
            . 'AND `efr`.`deleted` = "n" '
            . 'AND `efr`.`id_member` = ' . $this->db->quote($this->getMember('id'), 'integer') . ' '
            . 'LIMIT 1';
        $res = $this->db->queryOne($sql);
        Qs_Db::isError($res);
        return '1' === $res;
    }


    function getDocuments($cDocId=null)
    {
        $sql = "SELECT ".($cDocId?"*":"doc_number, doc_name")." FROM {$this->tableNameDB}DocumentofCommerce".($cDocId?" WHERE doc_number=".$this->db->quote($cDocId):"")." ORDER BY doc_name";
        if ($cDocId) $res = $this->db->queryRow($sql);
        else $res = $this->db->queryAll($sql, null, MDB_FETCHMODE_DEFAULT, true);
        if ($this->isDBError($res) || !is_array($res)) {
            $res = array();
        }
        return $res;
    }

    function getForms()
    {
        $sql = "SELECT id, name FROM {$this->tableNameDB}Form WHERE id>3";
        $res = $this->db->queryAll($sql, null, MDB_FETCHMODE_DEFAULT, true);
        if ($this->isDBError($res) || !is_array($res)) {
            $res = array();
        }
        return $res;
    }

    function initFromForm($form)
    {
        $files = array('image' => $form->getElementValue('image'));
        if ($files['image']['error'] == 4 && $files['image']['del'] != 1) unset($files['image']);
        $data = $form->exportValues();
        $data['description_vcs']=strip_tags($data['description_vcs']);
        $this->arrayParseUrl2Tag($data);
        $this->ParseDateTimeFields($data);
        $this->ParseTimeFields($data);
        $this->_initDateFromForm($form, $data, array('new_renewal_date'));
        $this->initFromArray($data, $files);
        return true;
    }

    function _updateDependency()
    {
        parent::_updateDependency();
        if ('choose_existing' == $this->_data['doc_action']) {
            $this->getTable('EventDocumentofCommerce')->update(
                array(
                    'ce_hours'  => $this->_data['ce_credits'],
                    'status_id' => $this->_data['doc_status_id'],
                ),
                '`doc_number` = ' . $this->db->quote($this->_data['doc_number'])
            );
        }

        /* inserting credits for attended members without ce credit history for current event */
        if (!empty($this->_data['ce_credits'])) {
            $attendedNoCredits = $this->_getAttendedUsersWithoutCredits();
            if (is_array($attendedNoCredits) && !empty($attendedNoCredits)) {
                $this->_insertCreditHistory4Members($this->_data['ce_credits'], $attendedNoCredits);
            }
        }
    }

    protected function _insertCreditHistory4Members($ceCredits, array $memberIds)
    {
        if (empty($ceCredits) || empty($memberIds)) {
            return $this;
        }
        $sql = "
            INSERT INTO {$this->db->tblEventCECreditHistory}
                (`id_individual`, `id_event`, `event_date`, `name`, `credits`, `added`, `changed`)
            VALUES
        ";

        $sqlValues = array();
        foreach ($memberIds as $memberId) {
            $values = array (
                $this->db->quote($memberId, 'integer'),  // id_individual
                $this->db->quote($this->id, 'integer'),  // id_event
                date('Y-m-d', strtotime($this->_data['eventdate'])), // event_date
                $this->db->quote($this->_data['name']), //name
                $this->_data['ce_credits'], //credits
                'NOW()', //added
                'NOW()', //changed
            );
            $sqlValues[] = '(' . implode(',', $values) . ')';
        }

        if (!empty($sqlValues) && is_array($sqlValues)) {
            $sql .= implode(', ', $sqlValues);
            Qs_Db::isError($this->db->query($sql));
        }
        return $this;
    }

    protected function _getAttendedUsersWithoutCredits()
    {
        $sql = "
            SELECT `m`.`id` FROM {$this->_getPair('Member', 'm')}
            JOIN {$this->_getPair('EventFormReq', 'efr')}
                ON `efr`.`id_member` = `m`.`id`
                AND `efr`.`deleted` = 'n'
                AND `efr`.`attended` = 1
                AND `efr`.`id_event` = {$this->db->quote($this->id, 'integer')}
            LEFT JOIN {$this->_getPair('EventCECreditHistory', 'ech')}
                ON `ech`.`id_event` = `efr`.`id_event`
                AND `ech`.`id_individual` = `efr`.`id_member`
            WHERE
                `ech`.`credits` IS NULL

        ";
        $members = $this->db->queryCol($sql);
        return Qs_Db::isError($members) ? array() : $members;
    }


    function ParseTimeFields(&$data)
    {
        foreach ($this->_timeFields as $cField) {
            if (isset($data[$cField]) && is_array($data[$cField])) {
                $data[$cField] = ($data[$cField]['g'] % 12 + ($data[$cField]['A'] == 'PM' ? 12 : 0)) . ':' . $data[$cField]['i'];
            }
        }
    }

    function ParseDateTimeFields(&$data)
    {
        foreach ($this->_datetimeFields as $cField) {
            if (isset($data[$cField]) && is_array($data[$cField])) {
                $data[$cField] = $data[$cField]['Y'] . '-' . $data[$cField]['M'] . '-' . $data[$cField]['d'] . ' ' . ($data[$cField]['g'] % 12 + ($data[$cField]['A'] == 'PM' ? 12 : 0)) . ':' . $data[$cField]['i'];
            }
        }
    }

    function _getJoin4Grid()
    {
        return "INNER JOIN {$this->tableNameDB}Type ON {$this->tableName}.id_type={$this->tableNameDB}Type.id";
    }

    function _getWhat4Grid($opt = array())
    {
        return "{$this->tableName}.*,
            IF({$this->tableName}.registration_end<NOW(),1,0) AS registration_closed,
            IF({$this->tableName}.eventdate<CURRENT_DATE(),1,0) AS ended,
            {$this->tableNameDB}Type.title,
            (SELECT count(id) FROM mn_EventFormReq efr WHERE  efr.id_event={$this->tableName}.id AND efr.deleted = 'n') registered_count";
    }

    function getList4Show($nMonth, $nYear, $nFilter)
    {
        $sql = "SELECT DATE_FORMAT(eventdate, '%e') day, e.id, name, et.title
        FROM  {$this->tableNameDB} AS e
            INNER JOIN {$this->tableNameDB}Type AS et ON e.id_type=et.id
        WHERE DATE_FORMAT(eventdate, '%c')=" . (int) $nMonth . "
            AND DATE_FORMAT(eventdate, '%Y')=" . (int) $nYear . (($nFilter == SHOW_EDUCATION_EVENTS) ? " AND education=1" : "") . (($nFilter == SHOW_EXCHANGE_EVENTS) ? " AND exchange=1" : "") . (($nFilter == SHOW_MONTHLY_PROGRAMS) ? " AND montly_program=1" : "");
        return $this->db->queryAll($sql);
    }

    function getFromDB($id, $field = false)
    {
        $sql = 'SELECT '
             . '    `' . $this->tableName . '`.*, '
             . '    IF(`registration_end` < NOW(), 1, 0) AS `closed`, '
             . '    `edc`.`status_id` AS `doc_status_id`, '
             . '    ' . $this->_getIsAllowedRegistrationExpr() . ' AS `is_allowed_registration` '
             . 'FROM ' . $this->_getPair() . ' '
             . 'LEFT JOIN ' . $this->_getPair('EventDocumentofCommerce', 'edc') . ' '
             . '    ON `edc`.`doc_number` = ' . $this->quoteField('doc_number') . ' '
             . 'WHERE ' . $this->quoteField('id') . ' = ' . $this->db->quote($id);
        $res = $this->db->queryRow($sql);
        $this->arrayParseTag2Url($res);
        return $this->getSubElem($res, $field);
    }

    protected function _getIsAllowedRegistrationExpr()
    {
        $sql = "
            IF(`Event`.`registration_start` = '0000-00-00 00:00:00'
                OR (NOW() BETWEEN `Event`.`registration_start` AND `Event`.`registration_end`),
                'y',
                'n'
            )
        ";
        return $sql;
    }

    function _getWhere4Grid($opt = array())
    {
        if (DB_OBJ_DEL_METHOD_LOGICAL == $this->delMethod) {
            $where = " WHERE {$this->tableName}.deleted = 'n' ";
        } else {
            $where = ' WHERE 1 ';
        }
        $where .= ' ' . (string) $opt['addonWhere'] . ' ';
        $where .= ' ' . $this->_filterSql();
        if ($this->eventyear) $where .= ' AND YEAR(eventdate)='.(int)$this->eventyear;
//      if ($this->eventtype == 1) $where .= ' AND exchange=1';
//      if ($this->eventtype == 2) $where .= ' AND education=1';
//      if ($this->eventtype == 3) $where .= ' AND montly_program=1';
        return $where;
    }

    function initFromDB()
    {
        if (null === ($this->_data = $this->getFromDB($this->id))) {
            return false;
        }
        $this->_data['registered_count'] = $this->db->queryOne(
                "SELECT count(id) FROM mn_EventFormReq efr WHERE efr.deleted = 'n' AND efr.id_event={$this->id}");
        $this->_data['doc_action'] = 'choose_existing';
        return true;
    }

    function delete()
    {
        $this->_files = array('image' => array('del' => 1));
        return parent::delete();
    }

    function getList4Select($where = '1')
    {
        $sql = "select id, name from {$this->tableNameDB} where {$where} order by eventdate desc";
        return $this->db->queryAll($sql, null, MDB_FETCHMODE_DEFAULT, true);
    }

    function getEventsYears($id_type = null, $shownextyear = false)
    {
        $aRes=$this->db->queryAll(
                "SELECT DISTINCT DATE_FORMAT(eventdate,'%Y') id, CONCAT(DATE_FORMAT(eventdate,'%Y')".($shownextyear?",'-',DATE_FORMAT(eventdate,'%Y')+1":"").")
                        FROM {$this->tableNameDB}
                        WHERE eventdate " . ($id_type ? " AND id_type=" . $this->db->quote(
                        $id_type) : ""),
                null,
                MDB2_FETCHMODE_ASSOC,
                true);
                return $aRes;
    }

    function getVCSContent()
    {
        $format = 'Ymd\THisO';
        list($date,) = explode(' ', $this->getData('eventdate'));
        $startTime = strtotime($this->getData('eventdate'));
        $endTime = strtotime($date . ' ' . $this->getData('time_end'));
        $sDTSTART = date($format, $startTime);
        $sDTEND = date($format, $endTime);
        return "BEGIN:VCALENDAR\nVERSION:1.0\nPRODID:PHP\nBEGIN:VEVENT\nDTSTART:"
              . $sDTSTART . "\nDTEND:" . $sDTEND . "\n"
              . ($this->getData('address')?"LOCATION:".$this->getData('address')."\n":"")
              . "UID:EVENTNO" . $this->id
              . "@MNCAR.ORG\nCATEGORIES:Business\nDESCRIPTION;ENCODING=QUOTED-PRINTABLE:=\n"
              . str_replace(array("\n","\r"), array("=0D","=0A") , $this->getData('description_vcs'))
              . "\nSUMMARY:" . $this->getData('name') . "\nPRIORITY:3\nEND:VEVENT\nEND:VCALENDAR\n";
    }

    function getVCSFile()
    {
        $fileName = $this->getData('name');
        $fileName = str_replace('"', '\"', $fileName);
        header("Content-Type: text/x-vCalendar");
        header('Content-Disposition: inline; filename="' . $fileName . '.vcs"');
        echo $this->getVCSContent();
        die();
    }

    function getRes4ExportNoEthicsReportAttendee()
    {
        $sql = '
            SELECT
                `m`.id,
                `m`.`first_name`,
                `m`.`last_name`,
                `c`.`name` AS `company_name`,
                `m`.`email`,
                `m`.`primary_phone`,
                ' . Member::_getWhatMemberType() . ',
                ' . Member::_getWhatMemberStatus() . ',
                `efr`.`id_member`
             FROM ' . $this->db->tblMember . ' AS `m`
             ' . Member::_getJoinMemberType('m') . '
             ' . Member::_getJoinMemberStatus('m') . '
             LEFT JOIN ' . $this->db->tblLocation . ' AS `l` ON `m`.`id_location` = `l`.`id`
             LEFT JOIN ' . $this->db->tblCompany . ' AS `c` ON `l`.`id_company` = `c`.`id`
             LEFT JOIN ' . $this->db->tblEventFormReq . ' AS `efr` ON `efr`.`id_member` = `m`.`id` AND `id_event` = 653
             WHERE 1
                AND `m`.`id_mncar_type` IN (1, 2, 3)
                AND `m`.`id_mncar_status` IN (1, 3)
             ORDER BY `m`.`last_name`
        ';
        $res = $this->db->query($sql);
        $this->isDBError($res);
        return $res;
    }

    public function getAttendeesList()
    {
        $sql = "
            SELECT
                `Member`.`id`,
                `Member`.`first_name`,
                `Member`.`middle_initial`,
                `Member`.`last_name`,
                `Company`.`name` AS `company_name`,
                IF( " . Member::getAddonWhereForMemberFind('Member') . ",1, 0) as 'active_member'
                FROM `{$this->db->tblMember}` AS `Member`
            JOIN `{$this->db->tblEventFormReq}` AS `EventFormReq`
                ON `EventFormReq`.`id_member` = `Member`.`id`
                    AND `EventFormReq`.`deleted` = 'n'
                    AND `EventFormReq`.`id_event` = " . $this->db->quote($this->id, 'integer')  . "
            LEFT JOIN `{$this->db->tblLocation}` AS `Location`
                ON `Location`.`id` = `Member`.`id_location`
            LEFT JOIN `{$this->db->tblCompany}` AS `Company`
                ON `Company`.`id` = `Location`.`id_company`
            WHERE `EventFormReq`.`id_event` = " . $this->db->quote($this->id, 'integer') . "
            ORDER BY `Member`.`last_name`, `Member`.`middle_initial`, `Member`.`first_name`, `company_name`
        ";
        $res = $this->db->queryAll($sql);
        $this->isDBError($res);
        return $res;
    }

    function getlistAttendeeOrientationEvent($year)
    {
        $sql = '
            SELECT
                `efr`.`id_member`,
                `e`.`name`,
                `e`.`eventdate`
            FROM `' . $this->db->tblEvent . '` AS `e`
            INNER JOIN `' . $this->db->tblEventFormReq . '` AS `efr` ON `e`.`id` = `efr`.`id_event`
			WHERE 1
                AND e.name LIKE "%orientation%"
                AND YEAR(eventdate)=' . (int) $year . '
        ';
        $res = $this->db->queryAll($sql);
        $this->isDBError($res);
        $list = array();
        foreach ($res as $row) {
            $list[$row['id_member']][] = $row;
        }
        return $list;
    }

    public function getTeamPersons4Report()
    {
        $sql = 'SELECT '
             . '`efr`.`id_team`'
             . ', `et`.`teamname`'
             . ', CONCAT(`m`.`first_name`, " ", `m`.`last_name`) AS `person`'
             . 'FROM `' . $this->db->tblEventFormReq . '` AS  `efr` '
             . 'INNER JOIN `' . $this->db->tblMember . '` AS `m` '
             . '    ON `m`.`id` = `efr`.`id_member` '
             . 'INNER JOIN `' . $this->db->tblEventTeam . '` AS `et` '
             . '    ON  `efr`.`id_event` = `et`.`id_event` '
             . '    AND `efr`.`id_team`  = `et`.`id` '
             . 'WHERE 1 '
             . 'AND `efr`.`id_event` = ' . $this->db->quote($this->id, 'integer') . ' '
             . 'AND `efr`.`deleted` = "n" '
             . 'ORDER BY `efr`.`id` DESC';
        $res = $this->db->queryAll($sql);
        $this->isDBError($res);
        $result = array();
        foreach ($res as $row) {
            if (!isset($result[$row['id_team']])) {
                $result[$row['id_team']] = array(
                    'id'      => $row['id_team'],
                    'name'    => $row['teamname'],
                    'persons' => array()
                );
            }
            $result[$row['id_team']]['persons'][] = $row['person'];
        }
        return $result;
    }

    function getMemberRegistration()
    {
        $sql = 'SELECT `efr`.*, `et`.`id` AS `id_team`, `et`.`teamname` '
             . 'FROM `' . $this->db->tblEventFormReq . '` AS `efr` '
             . 'LEFT JOIN `' . $this->db->tblEventTeam . '` AS `et` '
             . '    ON `et`.`id` = `efr`. `id_team` '
             . 'WHERE 1 '
             . 'AND `efr`.`id_event` = ' . $this->db->quote($this->id, 'integer') . ' '
             . 'AND `efr`.`id_member` =  ' . $this->db->quote($this->getMember('id'), 'integer')
             . ' AND `efr`.`deleted` = "n" '
             . 'LIMIT 1';
        $result = $this->db->queryRow($sql);
        $this->isDBError($result);
        if ($result['id_team']) {
            $sql = 'SELECT `m`.`id`, CONCAT(`m`.`first_name`, " ", `m`.`last_name`) AS `member_name` '
                 . 'FROM `' . $this->db->tblEventFormReq . '` AS `efr` '
                 . 'JOIN `' . $this->db->tblMember . '` AS `m` '
                 . '    ON `m`.`id` = `efr`.`id_member` '
                 . 'WHERE 1 '
                 . 'AND `efr`.`id_team` = ' . $this->db->quote($result['id_team']) . ' '
                 . 'AND `efr`.`id_event` = ' . $this->db->quote($this->id, 'integer') . ' '
                 . 'AND `efr`.`deleted` = "n" '
                 . 'ORDER BY member_name';
            $result['team_members'] = $this->db->queryAll($sql, 0, null, true);
            $this->isDBError($result['team_members']);
        }
        return $result;
    }

    function getExistingTeams4Select($id_team = null)
    {
        $sql = 'SELECT '
             . '`et`.`id`, '
             . 'IF(`et`.`teamname` = "", "No Name", `et`.`teamname`) AS `teamname`, '
             . '`e`.`group_limit`, '
             . '`e`.`group_limit` - IFNULL((SELECT COUNT(`id_member`) FROM `mn_EventFormReq` WHERE `id_team` = `et`.`id` AND `deleted` = "n"), 0) AS `remain` '
             . 'FROM `' . $this->db->tblEventTeam . '` AS `et` '
             . 'LEFT JOIN `' . $this->db->tblEvent. '` AS `e` '
             . '    ON `et`.`id_event` = `e`.`id` '
             . 'WHERE `et`.`id_event` = ' . $this->db->quote($this->id, 'integer') . ' '
             . 'HAVING remain > 0 OR group_limit = 0';
        if (null !== $id_team) {
            $sql .= ' OR `et`.`id` = ' . $this->db->quote($id_team, 'integer');
        }
        $sql .= ' ORDER BY teamname';
        $res = $this->db->queryAll($sql);
        $options = array();
        $this->isDBError($res);
        foreach ($res as $row) {
            $options[$row['id']] = empty($row['teamname']) ? 'No Name' : $row['teamname'];
            if ($row['group_limit']) {
                $options[$row['id']] .= ' (' . $row['remain'] . ' ' . Qs_Translate::getPlural('spot', $row['remain'])
                                      . ' available)';
            }
        }
        return $options;
    }

    function findRequest()
    {
        $sql = 'SELECT * '
             . 'FROM `' . $this->db->tblEventFormReq . '` '
             . 'WHERE 1 '
             . '    AND `id_event` = ' . $this->db->quote($this->id, 'integer') . ' '
             . '    AND `id_member` = ' . $this->db->quote($this->getMember('id'), 'integer') . ' '
             . '    AND `deleted` = "n" '
             . 'LIMIT 1';
        $res = $this->db->queryRow($sql);
        $this->isDBError($res);
        return (null === $res) ? false : $res;
    }

    function assignToTeam($id_team)
    {
        if (false === ($request = $this->findRequest())) {
            return false;
        }
        $this->getTable('EventFormReq')->update(
            array('id_team' => strval($id_team)),
            '`id` = ' . $this->db->quote($request['id'])
        );
        return true;
    }

    function getTeamName($id_team)
    {
        $sql = 'SELECT `teamname` '
             . 'FROM `' . $this->db->tblEventTeam . '` '
             . 'WHERE 1 '
             . 'AND `id` = ' . $this->db->quote($id_team, 'integer') . ' '
             . 'LIMIT 1';
        $res = $this->db->queryOne($sql);
        $this->isDBError($res);
        return $res;
    }

    function _beforeInsert()
    {
        parent::_beforeInsert();
        $this->_beforeSave();
    }

    function _beforeUpdate()
    {
        parent::_beforeUpdate();
        $this->_beforeSave();
    }

    protected function _beforeSave()
    {
        if ('new' == $this->_data['doc_action']) {
            $doc = Qs_Array::map(
                $this->_data,
                array(
                    'doc_number'   => 'new_doc_number',
                    'doc_name'     => 'new_doc_name',
                    'ce_hours'     => 'new_ce_hours',
                    'renewal_date' => 'new_renewal_date',
                    'status_id'    => 'new_status_id',
                )
            );
            $this->getTable('EventDocumentofCommerce')->insert($doc);
            $this->_data['doc_number'] = $doc['doc_number'];
            $this->_data['ce_credits'] = $doc['ce_hours'];
            $this->_data['doc_status_id'] = $doc['status_id'];
        }
    }

    /**
     * method return 2 arrays - teams and individuals without team
     * @param $eventId int
     * @return array|null -
     */
    public function getGroupedRegistrants($eventId)
    {
        $sqlTeamCount = "
            SELECT COUNT(`id_member`)
            FROM {$this->_getPair('EventFormReq')}
            WHERE `id_team` = `EventTeam`.`id` AND `EventFormReq`.`deleted` = 'n'
        ";
        $sql = "
            SELECT
                `EventTeam`.`id` AS `team_id`,
                `EventTeam`.`id`,
                IF(`EventTeam`.`teamname` = '', 'No Name', `EventTeam`.`teamname`) AS `teamname`,
                `Event`.`group_limit`,
                `Event`.`group_limit` - IFNULL(({$sqlTeamCount}), 0) AS `remain`
            FROM {$this->_getPair('EventTeam')}
            LEFT JOIN {$this->_getPair('Event')}
                ON `EventTeam`.`id_event` = `Event`.`id`
            WHERE `EventTeam`.`id_event` = {$this->db->quote($eventId, 'integer')}
            ORDER BY `teamname`
        ";

        $teams = $this->db->queryAll($sql, null, MDB2_FETCHMODE_ASSOC, true);
        if (Qs_Db::isError($teams)) {
            return null;
        }

        $sql = "
            SELECT
                `Member`.`id`,
                CONCAT(
                    `Member`.`first_name`,
                    ' ',
                    IF('' != `Member`.`middle_initial`, CONCAT(`Member`.`middle_initial`, '. '), ''),
                    `Member`.`last_name`
                ) AS `name`,
                `EventFormReq`.`id_team`
            FROM {$this->_getPair('Member')}
            JOIN {$this->_getPair('EventFormReq')}
               ON (`EventFormReq`.`id_member` = `Member`.`id` AND  `EventFormReq`.`deleted` = 'n')
            WHERE `EventFormReq`.`id_event` = {$this->db->quote($eventId, 'integer')}
            ORDER BY `Member`.`last_name`, `Member`.`first_name`
        ";

        $members = $this->db->queryAll($sql);

        if (Qs_Db::isError($members)) {
            return null;
        }

        $individualsNoTeam = array();

        foreach ($members as $member) {
            if (!empty($teams[$member['id_team']])) {
                $teams[$member['id_team']]['members'][] = $member;
            }
            if (empty($member['id_team'])) {
                $individualsNoTeam[] = $member;
            }
        }

        return array('teams' => $teams, 'individualsNoTeam' => $individualsNoTeam); // this is not the first return
    }
}