<?php

require_once ('app/Event/Form/Req/AbstractObj.php');

define('cn_EventFormReqReq', 'EventForm_Req');

class EventForm_Req extends App_Event_Form_Req_AbstractObj
{
    var $id_fm = null;
    var $id_event = null;
    var $id_member = null;
    var $date_registered = null;
    var $delMethod = DB_OBJ_DEL_METHOD_LOGICAL;
    var $_filterFields = array('m.first_name', 'm.middle_initial', 'm.last_name', 'c.name');
    var $itemName = 'Registration';
    var $report = null;
    protected $_eventsData = array();

    function EventForm_Req($id_fm, $id = null, $id_event = null)
    {
        $this->tableName = 'EventFormReq';
        $this->id_fm = intval($id_fm);
        $this->id_event = intval($id_event);
        parent::DBObj($id);
    }

    protected function _insertAdditionalMemberRegistration($data, $authorizeTransactionId)
    {
        $registered = Session::getData('event', 'registered');
        $registered[] = parent::insert($data);
        Session::setData('event', 'registered', $registered);
        $sql = "UPDATE {$this->tableNameDB} SET ip = INET_ATON(" . $this->db->quote($_SERVER['REMOTE_ADDR'])
            . ") WHERE id = " . $this->db->quote($this->id, 'integer');
        $this->db->query($sql);
        if (null !== $authorizeTransactionId) {
            $dataVal = array('id_fmr' => $this->id, 'id_fmfld' => 4, 'sorter' => 7, 'type' => 'text', 'label' => 'TransactionID');
            $id_fmrf = $this->fld->insert($dataVal);
            $dataVal = array('id_fmrf' => $id_fmrf, 'value' => $authorizeTransactionId);
            $this->fldVal->insert($dataVal);
        }
        return $this;
    }

    function insert($nEventID, $aAuthNetData, $lang = 'eng')
    {
        require_once 'class/DB/Lang/DLang.php';
        $DLang = new DLang();
        $doc = null;
        $EventForm = SiteMap::getObj('Event/Form/Form.php', $this->id_fm);
        $form = $EventForm->getForm($doc, $nEventID, $DLang->getDefault());
        $data = $this->_data;
        $data['id_fm'] = $this->id_fm;
        $data['lang'] = $lang;
        $data['id_event'] = $nEventID;
        if (isset($_REQUEST['amount'])) $data['amount'] = $_REQUEST['amount'];
        else $data['amount'] = $this->db->queryOne('SELECT member_cost FROM mn_Event WHERE id=' . (int) $nEventID);
        if (isset($_REQUEST['fild'][5])) {
            $aPaymentType=$this->db->queryAll('SELECT name, id FROM mn_EventFormReqType', 0, null, true);
            $data['payment_type']=$aPaymentType[$_REQUEST['fild'][5]];
        }
        if (is_array($_REQUEST['added'])) $data['added']=$_REQUEST['added']['Y'].'-'.$_REQUEST['added']['M'].'-'.$_REQUEST['added']['d'];

        $nEventTypeID = $this->db->queryOne('SELECT id_type FROM mn_Event WHERE id=' . (int) $nEventID);
        if (isset($_POST['team_option'])) {
            if ($_POST['team_option'] == 'newteam') {
                require_once ('class/DB/DBTable.php');
                $teamtable = new DBTable('EventTeam');
                $data['id_team'] = $teamtable->insert(array('id_event' => $nEventID, 'teamname' => $_POST['teamname']));
            } elseif ($_POST['team_option'] == 'exisitingteam') {
                $data['id_team'] = (int) $_POST['id_team'];
            }
        }

        $transactionId = isset($aAuthNetData['transaction_id']) ? $aAuthNetData['transaction_id'] : null;
        if (isset($_POST['memberId']) && is_array($_POST['memberId'])) {
            $membersIds = array_unique($_POST['memberId']);
            foreach ($membersIds as $memberId) {
                $data['id_member'] = $memberId;
                $this->_insertAdditionalMemberRegistration($data, $transactionId);
            }
        }

        if (isset($_POST['anothermember_emails'])) {
            foreach ($_POST['anothermember_emails'] as $cEmail) {
                if ($cEmail) {
                    $data['id_member'] = $this->db->queryOne("SELECT id FROM mn_Member WHERE email = " . $this->db->quote($cEmail));
                    if ($data['id_member']) {
                        $this->_insertAdditionalMemberRegistration($data, $transactionId);
                    }
                }
            }
        }
        if (intval($this->id_member)) {
            $data['id_member'] = intval($this->id_member);
        } else if (strpos(CURR_PAGE, 'admin') === false) {
            require_once ('class/Session.php');
            $member = Session::getData('individual_user', 'individual_user');
            if (is_null($member)) $member = Session::getData('individual_user_support', 'individual_user_support');
            $data['id_member'] = $member['id'];
            $data['attended']=$this->db->queryOne('SELECT IF(eventdate<NOW(),1,0) FROM mn_Event WHERE id=' . (int) $nEventID);
        } else {
            $data['id_member'] = $_REQUEST['id_member'];
            if (!$data['id_member'])
                $data['id_member']=$this->db->queryOne('SELECT id FROM mn_Member WHERE email='.$this->db->quote($_REQUEST['member']));
        }

        $aRegistered=Session::getData('event','registered');
        $aRegistered[] = parent::insert($data);
        Session::setData('event','registered',$aRegistered);

        /*sendmail*/
        if (strpos(CURR_PAGE, 'admin') === false || isset($_REQUEST['sendemail'])) {
            include ('lib/htmlMimeMail/htmlMimeMail.php');
            $mime = & new htmlMimeMail();
            $Event = SiteMap::getObj('Event/Event.php', $data['id_event']);
            $Event->InitFromDB();
            $Member = SiteMap::getObj('Member/Member.php', $data['id_member']);
            $Member->InitFromDB();

            $nEventStart=strtotime($Event->getData('eventdate'));
            $nEventEndTime=strtotime($Event->getData('time_end'));

            $commonMsgBody = 'Name: ' . $Event->getData('name') . '
' . ($Event->getData('address') ? ('Location: ' . $Event->getData('address') . '
') : '') .
                'Date: ' . strftime("%B %d, %Y", $nEventStart) . '
Start time: ' . strftime("%I:%M %p", $nEventStart) . '
End time: ' . strftime("%I:%M %p", $nEventEndTime) . '
Cost: $' . number_format((int)$data['amount'], 2) . '

Reference Number: ' . $data['id_event'] . '-' . $data['id_member'] . '

SAVE THIS EVENT IN OUTLOOK!

Simply open the attached file and save to add this event to your Outlook calendar.
';

            $cBody='***************************** CONFIRMATION *****************************

Thank you for registering for the following event:

' . $commonMsgBody;

        if (isset($aAuthNetData['amount'])) {
            $cBody.='
========= BILLING INFORMATION =========

Amount -- $'.number_format($aAuthNetData['amount'],2).'
Date -- '.date('m/d/y').'
Transaction ID -- '.$aAuthNetData['transaction_id'].'
==================================
';
        }
        $cBody.='
*****************************************************************
Thank You';
            $mime->setText($cBody);
            $mime->addAttachment($Event->getVCSContent(), $Event->getData('name').'.vcs', 'text/x-vCalendar');
            unlink($Event->getData('name').'.vcs');
            $mime->setFrom('info@mncar.org');
            $mime->setSubject('MNCAR - '.$Event->getData('name').' Registration');

            if (isset($_POST['anothermember_emails'])) $aEmails=$_POST['anothermember_emails'];
            else $aEmails=array();
            $aEmails[]=$Member->getData('email');
            $mime->send($aEmails);

            if (isset($_POST['memberId'])) {
                $memberName = $Member->getData('first_name') . ' ';
                if ($Member->getData('middle_initial')) {
                    $memberName .= $Member->getData('middle_initial') . '. ';
                }
                $memberName .= $Member->getData('last_name');
                $mime->setText($this->_getEmailText4AdditionalMember($commonMsgBody, $memberName));
                $mime->buildMessage();
                $mime->send($this->_getMemberEmailsByIds($_POST['memberId']));
            }
        }

        $sql = "UPDATE {$this->tableNameDB} SET ip = INET_ATON(" . $this->db->quote($_SERVER['REMOTE_ADDR']) . ") WHERE id = " . $this->db->quote(
                $this->id);

        $this->db->query($sql);
        foreach ($form->getElements() as $sorter => $element) {
            $aValues=$this->_getValues($element);
            if (intval(str_replace(array(']', 'fild['), '', $element->getName()))==2)
                $aValues=array($aValues['month'].$aValues['year']);
            if ($element->getLabel() && intval(str_replace(array(']', 'fild['), '', $element->getName()))) {
                $data = array('id_fmr' => $this->id,
                        'id_fmfld' => intval(str_replace(array(']', 'fild['), '', $element->getName())),
                        'sorter' => $sorter,
                        'type' => $element->getType(),
                        'label' => $element->getLabel());
                $id_fmrf = $this->fld->insert($data);
                foreach ($aValues as $value) {
                    if (intval(str_replace(array(']', 'fild['), '', $element->getName())) == 1) $value = str_pad(substr($value, -4),
                            strlen($value),
                            'x',
                            STR_PAD_LEFT);
                    $data = array('id_fmrf' => $id_fmrf, 'value' => $value);
                    $this->fldVal->insert($data);
                    $cElementValue = $value;
                }
            }
        }
        if (isset($aAuthNetData['transaction_id'])) {
            $data = array('id_fmr' => $this->id, 'id_fmfld' => 4, 'sorter' => 7, 'type' => 'text', 'label' => 'TransactionID');
            $id_fmrf = $this->fld->insert($data);
            $data = array('id_fmrf' => $id_fmrf, 'value' => $aAuthNetData['transaction_id']);
            $this->fldVal->insert($data);
        }
        if (isset($aAuthNetData['amount'])) {
            $data = array('id_fmr' => $this->id, 'id_fmfld' => 3, 'sorter' => 5, 'type' => 'text', 'label' => 'Amount');
            $id_fmrf = $this->fld->insert($data);
            $data = array('id_fmrf' => $id_fmrf, 'value' => '$'.$aAuthNetData['amount']);
            $this->fldVal->insert($data);
        }
        if (isset($aAuthNetData['amount']) || isset($aAuthNetData['transaction_id'])) {
            $data = array('id_fmr' => $this->id, 'id_fmfld' => 5, 'sorter' => 9, 'type' => 'select', 'label' => 'Payment Type');
            $id_fmrf = $this->fld->insert($data);
            $data = array('id_fmrf' => $id_fmrf, 'value' => 'Online Credit');
            $this->fldVal->insert($data);
        }

        $nID=$this->id;

        if ($nEventTypeID == 3) {
                // Brokerage events
                $this->db->query('INSERT INTO `mn_EventFormReq` SELECT null, e.id, `id_member`, `id_team`, `id_fm`, `payment_type`, `amount`, `lang`, `ip`,
                `attended`, `deleted`, efr.`added`, efr.`changed`
                FROM `mn_EventFormReq` efr INNER JOIN mn_Event  e ON efr.id_event=e.parent_event_id
                WHERE efr.id='.(int)$nID);
        }

        return $nID;
    }

    protected function _getEmailText4AdditionalMember($dataMessage, $memberName)
    {
        $msg = "***************************** CONFIRMATION *****************************

You have been successfully registered by " . $memberName . " for the following event:

" . $dataMessage . "

*********************************************************************************";
        return $msg;
    }

    protected function _getMemberEmailsByIds(array $ids)
    {
        if (empty($ids)) {
            return array();
        }
        $sql = "SELECT `email` FROM {$this->_getPair('Member') } WHERE `id` IN (" . Qs_Db::quote($ids, 'integer') . ")";
        return $this->db->queryCol($sql);
    }

    function _getValues($element)
    {
        $values = (array) $this->getSubElem($this->_data, $element->getName());
        switch ($element->getType()) {
            case 'group' :
                $elements = $element->getElements();
                if (count($elements)) {
                    if ('checkbox' == $elements[0]->getType()) {
                        $values = array_keys($values);
                    }
                }
                break;
            case 'file' :
                $val = $element->getValue();
                $this->_initFile();
                $values = array((string) $this->file->handleUpload($element->getValue()));
                break;
        }
        return $values;
    }

    function _initTable()
    {
        if (!is_object($this->table)) {
            require_once ('class/DB/DBTable.php');
            $this->table = new DBTable($this->tableName);
            $this->fld = new DBTable($this->tableName . 'Fld');
            $this->fldVal = new DBTable($this->tableName . 'FldVal');
        }
        return true;
    }

    function save($nEventID, $aAuthNetData = array(), $lang = 'eng')
    {
        $this->insert($nEventID, $aAuthNetData, $lang);
        $this->send();
    }

    function send()
    {
        $res = $this->getFromDB($this->id);
        if ($res['fm']['email_to']) {
            $mime = $this->_getMailmime($res);
            if ($res['fm']['email_from']) {
                $mime->setFrom($res['fm']['email_from']);
            }
            $mime->setSubject($res['fm']['subject']);
            foreach (explode(',', $res['fm']['email_to']) as $email) {
                $email = trim($email);
                if (!empty($email)) {
                    $mime->send(array($email));
                }
            }
            return true;
        }
        return false;
    }

    function _getMailmime($res)
    {
        include ('lib/htmlMimeMail/htmlMimeMail.php');
        $mime = & new htmlMimeMail();
        require_once ('class/Doc/Doc.php');
        $Doc = new Doc(array('tpl' => SiteMap::getPath('Event/Form/tpl/body.tpl')));
        $Doc->addContent(array('tpl' => SiteMap::getPath('Event/Form/tpl/mail.tpl'), 'mail' => $res));
        $mime->setText($Doc->fetch());
        $this->_initFile();
        foreach ($res['fields'] as $field) {
            if ('file' == $field['type']) {
                $fileArr = $this->file->getArr($field['value'][0]);
                $filePath = $this->file->saveFS('tmp/', $fileArr['name']);
                $mime->addAttachment($mime->getFile($filePath), $fileArr['name'], $fileArr['type']);
                unlink($filePath);
            }
        }
        return $mime;
    }

    function getFromDbSql($id)
    {
        $sql = 'SELECT '
             . '    `' . $this->tableName . '`.*, '
             . '    `et`.`id` AS `id_team`, '
             . '    `et`.`teamname`, '
             . '    `m`.`email` AS `member_email`, '
             . '     `e`.`ce_credits`, '
             . '    ' . $this->getColumnCertificateAllowed($this->tableName, 'e', 'm', 'ech') . ' AS `certificate_allowed` '
             . 'FROM ' . $this->_getPair() . ' '
             . $this->_leftJoinEventCeCreditHistory() . ' '
             . $this->_joinEvent() . ' '
             . $this->_joinMember() . ' '
             . 'LEFT JOIN `' . $this->db->tblEventTeam . '` AS `et` '
             . '    ON `et`.`id` = ' . $this->quoteField('id_team') . ' '
             . 'WHERE 1 '
             . 'AND `' . $this->tableName . '`.`id` = ' . $this->db->quote($id, 'integer') . ' '
             . 'AND ' . $this->quoteField('deleted') . ' = "n"';
        return $sql;
    }

    function getFromDB($id, $field = false)
    {
        if (null === ($res = parent::getFromDB($id))) {
            return $res;
        }
        $sql = "SELECT * FROM {$this->tableNameDB}Fld WHERE  id_fmr = " . $this->db->quote($id);
        $res['fields'] = array();
        $res['fields'][] = array('label' => 'Registered', 'value' => array(date('m/d/Y h:i:s A',strtotime($res['added']))));
        $res['fields'][] = array('label' => 'Attendee',
                'value' => $this->db->queryOne("SELECT CONCAT(first_name, ' ', last_name) FROM mn_Member WHERE  id = " . (int)$res['id_member']));
        $res['fields'][] = array('label' => 'Company',
                'value' => $this->db->queryOne(
                        "SELECT c.name FROM mn_Member m LEFT JOIN mn_Location l ON m.id_location=l.id
            LEFT JOIN mn_Company c ON l.id_company=c.id WHERE m.id = " . (int)$res['id_member']));
        $res['fields'][] = array('label' => 'Phone', 'value' => $this->db->queryOne("SELECT primary_phone FROM mn_Member WHERE  id = " . (int)$res['id_member']));
        $cEmail=$this->db->queryOne("SELECT email FROM mn_Member WHERE  id = " . (int)$res['id_member']);
        $res['fields'][] = array('label' => 'Email', 'value' => '<a href="mailto:'.$cEmail.'">'.$cEmail.'</a>');
        $res['fields'] = array_merge($res['fields'], $this->db->queryAll($sql));
        $nTransactionID=0;
        foreach ($res['fields'] as $key => &$fld) {
            if ($key > 4) {
                $sql = "SELECT value FROM {$this->tableNameDB}FldVal WHERE   id_fmrf = " . $this->db->quote($fld['id']);
                $fld['value'] = $this->db->queryCol($sql);
            }
            if ($fld['label']=='TransactionID') $nTransactionID=$fld['value'][0];
        }
        if ($nTransactionID) {
            $aResult=$this->db->queryRow("SELECT f.id_fmr, COUNT(f2.id) fieldcount FROM {$this->tableNameDB}FldVal fv INNER JOIN {$this->tableNameDB}Fld f ON f.id=fv.id_fmrf
                INNER JOIN {$this->tableNameDB}Fld f2 ON f.id_fmr=f2.id_fmr
                WHERE fv.value = " . $this->db->quote($nTransactionID).' AND f.id_fmr<>'.$this->db->quote($id).'
                GROUP BY f.id_fmr ORDER BY fieldcount DESC LIMIT 1');
            if($aResult)
                $res['fields'][] = array('label' => 'This member was registered together with', 'value' => '<a href="'.BASE_URL.'/admin/event/report?action=edit&id='.$aResult['id_fmr'].'">registration #'.$aResult['id_fmr'].'</a>');
        }
        $EventForm = SiteMap::getObj('Event/Form/Form.php');
        $res['fm'] = $EventForm->getFromDB($res['id_fm']);
        return $this->getSubElem($res, $field);
    }

    function _getWhere4Grid($opt)
    {
        $cWhere = parent::_getWhere4Grid($opt);
        $cWhere .= $this->_filterWhere();
        if ($this->id_event) $cWhere .= " AND {$this->tableName}.id_event = " . $this->db->quote($this->id_event) . ' ';
        if ($this->id_fm) $cWhere .= " AND {$this->tableName}.id_fm = " . $this->db->quote($this->id_fm) . ' ';
        if ($this->date_registered) $cWhere .= " AND DATEDIFF({$this->tableName}.added, " . $this->db->quote($this->date_registered) . ')=0 ';
        if ($this->payment) $cWhere .= " AND {$this->tableName}.payment = 1";
        if (!$this->id_event && !$this->id_fm && !$this->date_registered) mail('roo@fr-wd.com','MNCAR EventReq', $_SERVER['REQUEST_URI']);
        return $cWhere;
    }

    protected function _filterWhere()
    {
        $parts = '';
        foreach ($this->_filter as $field => $value) {
            if (empty($value)) {
                continue;
            }
            switch ($field) {
                case 'id':
                    $parts[]= Qs_Db::equalCondition($field, $value, $this->tableName);
                    break;
                default:
                    break;
            }
        }
        if (empty($parts)) {
            return '';
        }
        return ' AND ' . implode(' AND ', $parts);
    }

    function _getWhat4Grid()
    {
        if ($this->report == 'signin') {
            return "CONCAT(m.last_name, ', ', m.first_name) person, email";
        } elseif ($this->report == 'daily') {
            return "{$this->tableName}.added, m.id member_id, t.name AS payment,  'Events' AS type,
            CONCAT(m.last_name, ', ', m.first_name, ' ', m.middle_initial, IF(m.middle_initial<>'','. ',''), IF(m.designations<>'',', ',''), m.designations) member_name,
            c.name company_name, amount, e.name event_name";
        } elseif ($this->report == 'roster') {
            return "{$this->tableName}.added, m.nrds_id, t.name payment,
            CONCAT(m.last_name, ', ', m.first_name, ' ', m.middle_initial, IF(m.middle_initial<>'','. ',''), IF(m.designations<>'',', ',''), m.designations) member_name,
            m.primary_phone, m.email, c.name company_name, amount";
        } elseif ($this->report == 'mail') {
            return "m.id, l.id_company, first_name, last_name, m.primary_phone, m.designations, email, re_license_number, c.name, IF(attended = 1, 'Yes', 'No') as attended,
            l.mailing_address, l.mailing_city, l.mailing_state,
            l.mailing_zip, amount, t.name payment, DATE_FORMAT({$this->tableName}.added,'%m/%d/%Y') added";
        } elseif ($this->report == 'payments') {
            return "{$this->tableName}.id, {$this->tableName}.added, amount,
            CONCAT(m.last_name, ', ', m.first_name, ' ', m.middle_initial, IF(m.middle_initial<>'','. ','')), m.last_name, ', ', IFNULL(c.name<>'','')) attendee";
        } elseif ($this->report == 'cecredits') {
            return "{$this->tableName}.id,
            CONCAT(m.last_name, ', ', m.first_name, ' ', m.middle_initial, IF(m.middle_initial<>'','. ','')) member_name,
            c.name company, CONCAT(l.name,' (',l.physical_city,')') location, m.nrds_id, attended, credits";
        } else {
            return "{$this->tableName}.id, "
                 . "{$this->tableName}.added, "
                 . "CONCAT(m.last_name, ', ', m.first_name, ' ', m.middle_initial, "
                 . "       IF(m.middle_initial<>'','. ',''), IF(c.name<>'','- ',''), IFNULL(c.name,'')"
                 . ") AS `attendee`, "
                 . '`m`.`re_license_number` as `member_re_license_number`, '
                 . "t.name payment, "
                 . "m.primary_phone, "
                 . "email, "
                 . "industry_experience, "
                 . 'attended, '
                 . "IF(attended = 1, 'Yes', 'No') AS `attended_title`, "
                 . '`e`.`doc_number`, '
                 . "amount, "
                 . "`{$this->tableName}`.`certificate`, "
                 . $this->getColumnCertificateAllowed($this->tableName, 'e', 'm', 'ech') . ' AS `certificate_allowed`, '
                 . 'IFNULL(`ecsq`.`status`, "notSent") AS `send_status` ';
        }
    }

    function _getGroup4Grid()
    {
        return "GROUP BY {$this->tableName}.id";
    }

    protected function _leftJoinLocation()
    {
        return 'LEFT ' . $this->_joinLocation();
    }

    protected function _joinLocation()
    {
        return 'JOIN ' . $this->_getPair('Location', 'l') . ' '
             . '    ON `l`.`id` = ' . $this->quoteField('id_location', 'm');
    }

    protected function _leftJoinCompany()
    {
        return 'LEFT ' . $this->_joinCompany();
    }

    protected function _joinCompany()
    {
        return 'JOIN ' . $this->_getPair('Company', 'c') . ' '
             . '    ON `c`.`id` = ' . $this->quoteField('id_company', 'l');
    }

    protected function _leftJoinQueue()
    {
        return ' LEFT JOIN ' . $this->_getPair('EventCertificateSendQueue', 'ecsq') . ' '
               . 'ON  `ecsq`.`id_event` = `e`.`id` '
               . 'AND `ecsq`.`id_member` = `m`.`id` ';
    }

    function _getJoin4Grid()
    {
        $sql = $this->_joinMember() . ' '
             . $this->_joinEvent() . ' '
             . $this->_leftJoinLocation() . ' '
             . $this->_leftJoinCompany() . ' '
             . 'JOIN ' . $this->_getPair($this->tableName . 'Type', 't') . ' '
             . '    ON `t`.`id` = ' . $this->quoteField('payment_type') . ' '
             . 'LEFT JOIN ' . $this->_getPair('EventCECreditHistory', 'ech') . ' '
             . '    ON  `ech`.`id_event` = ' . $this->quoteField('id_event') . ' '
             . '    AND `ech`.`id_individual` = `m`.`id` '
             . $this->_leftJoinQueue();
        return $sql;
    }

    public function getAverageIndustryExperience()
    {
        $sql = 'SELECT ROUND(AVG(`m`.`industry_experience`), 2) '
             . 'FROM ' . $this->_getPair('Member', 'm') . ' '
             . 'INNER JOIN ' . $this->_getPair('EventFormReq', 'efr') . ' '
             . '    ON `m`.`id` = `efr`.`id_member` '
             . 'WHERE `m`.`industry_experience` > 0 '
             . '    AND `efr`.`id_event` = ' . $this->db->quote($this->id_event, 'integer');
        $res = $this->db->queryOne($sql);
        Qs_Db::isError($res);
        return $res;
    }

    function getEventNameByID()
    {
        $Events = SiteMap::getObj('Event/Event.php', $this->id_event);
        $Events->initFromDB();
        return $Events->getData('name');
    }

    function getEventDateByID()
    {
        $Events = SiteMap::getObj('Event/Event.php', $this->id_event);
        $Events->initFromDB();
        return $Events->getData('eventdate');
    }

    function getEventTypeByID()
    {
        $Events = SiteMap::getObj('Event/Event.php', $this->id_event);
        $Events->initFromDB();
        return $Events->getData('id_type');
    }

    public function getEventCreditsById()
    {
        return $this->getEventDataById('ce_credits');
    }

    public function getEventDataById($field = false)
    {
        if (empty($this->_eventsData[$this->id_event])) {
            /** @var $Events Event */
            $Events = SiteMap::getObj('Event/Event.php', $this->id_event);
            $Events->initFromDB();
            $this->_eventsData[$this->id_event] = $Events->getData();
        }
        return $field ? Qs_Array::get($this->_eventsData[$this->id_event], $field) : $this->_eventsData[$this->id_event];
    }

    function getGroupNameByID()
    {
        $Events = SiteMap::getObj('Event/Event.php', $this->id_event);
        $Events->initFromDB();
        return $Events->getData('group_name') ? $Events->getData('group_name') : 'Foursome';
    }

    function getFormIDByID()
    {
        $Events = SiteMap::getObj('Event/Event.php', $this->id_event);
        $Events->initFromDB();
        return $Events->getData('id_form');
    }

    function getCECreditsByID()
    {
        $Events = SiteMap::getObj('Event/Event.php', $this->id_event);
        $Events->initFromDB();
        return $Events->getData('ce_credits');
    }

    function getList4Grid($opt = array())
    {
        $list = parent::getList4Grid($opt);
        if ($this->report == 'payments') {
            foreach ($list['list'] as $key => $item) {
                $sql = "SELECT {$this->tableNameDB}Fld.id_fmfld, {$this->tableNameDB}FldVal.value
                    FROM {$this->tableNameDB}Fld
                    INNER JOIN {$this->tableNameDB}FldVal ON {$this->tableNameDB}FldVal.id_fmrf = {$this->tableNameDB}Fld.id
                    WHERE {$this->tableNameDB}Fld.id_fmr = " . $this->db->quote($item['id']) . " AND {$this->tableNameDB}Fld.id_fmfld=4";
                $vals = $this->db->queryAll($sql, null, MDB2_FETCHMODE_ASSOC, true, false, true);
                $list['list'][$key]['id_transaction'] = $vals[4][0];
            }
        }
        if ($this->report == 'daily') {
            $sql = "SELECT SQL_CALC_FOUND_ROWS i.paid_date AS added, m.id member_id, 'MNCAR Invoices' AS payment, 'Membership Payments' AS type,
                    CONCAT(m.last_name, ', ', m.first_name, ' ', m.middle_initial,
                        IF(m.middle_initial<>'','. ',''), IF(m.designations<>'',', ',''), m.designations) member_name,
                    c.name company_name, amount, '' AS event_name
                FROM {$this->db->tblInvoice} AS i INNER JOIN mn_Member m ON i.id_member=m.id
                    LEFT JOIN mn_Location l ON m.id_location=l.id
                    LEFT JOIN mn_Company c ON l.id_company=c.id
                WHERE DATEDIFF(i.paid_date, " . $this->db->quote($this->date_registered) . ")=0
                    AND payment_method = 'online' AND paid = 'y'
                " . $this->_getOrder4Grid();
            $list['list'] = array_merge($list['list'], $this->db->queryAll($sql));
            $sql = "SELECT SQL_CALC_FOUND_ROWS i.paid_date AS added, m.id member_id, 'Exchange Invoices' AS payment, 'Membership Payments' AS type,
                    CONCAT(m.last_name, ', ', m.first_name, ' ', m.middle_initial,
                        IF(m.middle_initial<>'','. ',''), IF(m.designations<>'',', ',''), m.designations) member_name,
                    c.name company_name, amount, '' AS event_name
                FROM {$this->db->tblInvoice} AS i INNER JOIN mn_Location l ON i.id_location=l.id
                    LEFT JOIN mn_Member m ON l.id_pay_member=m.id
                    LEFT JOIN mn_Company c ON l.id_company=c.id
                WHERE DATEDIFF(i.paid_date, " . $this->db->quote($this->date_registered) . ")=0
                    AND payment_method = 'online' AND paid = 'y'
                " . $this->_getOrder4Grid();
            $list['list'] = array_merge($list['list'], $this->db->queryAll($sql));
        }
        return $list;
    }

    function savecredits($id_event, $aAttended, $aNewAttendee, $nCredits)
    {
        foreach ($aAttended as $key => $value)
            $aAttended[$key] = $this->db->quote($value);
        foreach ($aNewAttendee as $key => $value)
            $aNewAttendee[$key] = '(' . $this->db->quote($id_event, 'integer') . ',' . $this->db->quote($value) . ',2)';
        $this->db->query('UPDATE mn_EventFormReq SET attended=0 WHERE id_event=' . $this->db->quote($id_event, 'integer'));
        if (count($aAttended)) $this->db->query(
                'UPDATE mn_EventFormReq SET attended=1 WHERE id_event=' . $this->db->quote($id_event, 'integer') . ' AND id IN (' . implode(',',
                        $aAttended) . ')');
        if (count($aNewAttendee)) $this->db->query(
                'INSERT INTO mn_EventFormReq (id_event, id_member, payment_type) VALUES ' . implode(',', $aNewAttendee));
        $this->db->query(
                'DELETE FROM mn_EventCECreditHistory
            WHERE id_event=' . $this->db->quote($id_event, 'integer') . '
                AND id_individual NOT IN (SELECT id_member FROM mn_EventFormReq WHERE id_event=' . $this->db->quote($id_event, 'integer') . ' AND attended=1)');
        $this->db->query(
                'UPDATE mn_EventCECreditHistory SET credits=' . $this->db->quote($nCredits) . '
            WHERE id_event=' . $this->db->quote($id_event, 'integer') . '
                AND id_individual IN (SELECT id_member FROM mn_EventFormReq WHERE id_event=' . $this->db->quote($id_event, 'integer') . ' AND attended=1)');
        $this->db->query(
                'INSERT INTO mn_EventCECreditHistory (id_individual, id_event, event_date, name, credits, added, changed)
            SELECT id_member, e.id, eventdate, name, ' . $this->db->quote($nCredits) . ', NOW(), NOW()
                FROM mn_EventFormReq efr INNER JOIN mn_Event e ON efr.id_event=e.id
                WHERE e.id=' . $this->db->quote($id_event, 'integer') . ' AND attended=1 AND id_member NOT IN (
                    SELECT id_individual FROM mn_EventCECreditHistory WHERE id_event=' . $this->db->quote($id_event, 'integer') . ')');
    }

    function delete()
    {
      $this->_initTable();
        $aData=$this->db->queryRow('SELECT id_member, id_event, parent_event_id FROM mn_Event e INNER JOIN mn_EventFormReq efr ON efr.id_event=e.id WHERE efr.id='.$this->db->quote($this->id, 'integer'));
        $nParentEventID=$aData['id_event'];
        if ($aData['parent_event_id']) $nParentEventID=$aData['parent_event_id'];
        $this->table->update(array('deleted' => 'y'), 'id_member = '. $this->db->quote($aData['id_member'], 'integer') . ' AND id_event IN  (SELECT id FROM mn_Event WHERE id = '. $this->db->quote($nParentEventID, 'integer'). ' OR parent_event_id = '. $this->db->quote($nParentEventID, 'integer').')');
        $deleteCeCreditsWhere = "`id_individual` = {$this->db->quote($aData['id_member'], 'integer')}
        AND `id_event` = {$this->db->quote($aData['id_event'], 'integer')}";
        Qs_Db::isError($this->getTable('EventCECreditHistory')->delete($deleteCeCreditsWhere));
        $this->deleteCertificate();
        $whereQueue = "`id_event` = {$this->db->quote($aData['id_event'])} "
            . " AND `id_member` = {$aData['id_member']}";
        Qs_Db::isError($this->getTable('EventCertificateSendQueue')->delete($whereQueue));
        return $this;
    }

    function hasTeamPlace($id_event, $id_team)
    {
        $sql = ' SELECT
            IF (`e`.`group_limit` > 0,
                 '
            . ' `e`.`group_limit` - ('
            . '    SELECT COUNT(*) '
            . '    FROM `' . $this->db->tblEventFormReq . '` AS `efr` '
            . '    WHERE 1 '
            . '        AND `efr`.`id_event` = `e`.`id` '
            . '        AND `efr`.`deleted` = "n" '
            . ' AND `efr`.`id_team` = ' . $this->db->quote($id_team, 'integer') . '   '
            . '        AND `efr`.`id` != ' . $this->db->quote($this->id, 'integer') . ' '
            . '  ) '
            . ', 1) '
            . ' FROM `' . $this->db->tblEvent . '` AS `e` '
            . ' WHERE 1 '
            . '    AND `e`.`id` = ' . $this->db->quote($id_event, 'integer') . ' '
            . ' LIMIT 1;';
        $res = $this->db->queryOne($sql);
        $this->isDBError($res);
        return $res > 0;
    }

    /**
     * @return MDB2_BufferedResult_mysql
     */
    public function getPulsePortalExportStatement()
    {
        $sql = 'SELECT '
             . '    "MN" AS `StateAbbreviation`, '
             . '    "21014627" AS `ProviderID`, '
             . '    `edoc`.`doc_number` AS `CourseID`, '
             . '    DATE_FORMAT(`e`.`eventdate`, "%c/%d/%Y") AS `CourseOfferingEndDate`, '
             . '    DATE_FORMAT(`e`.`eventdate`, "%c/%d/%Y") AS `CourseOfferingBeginDate`, '
             . '    DATE_FORMAT(`e`.`eventdate`, "%l:%i %p") AS `CourseOfferingBeginTime`, '
             . '    "55435" AS `PostalCodeforCourseOffering`, '
             . '    "" AS `InstructionHours`, '
             . '    "" AS `SocialSecurityNumber`, '
             . '    "" AS `NationalProducerNumber`, '
             . '    `m`.`re_license_number` AS `LicenseNumber`, '
             . '    `m`.`last_name` AS `LastName`, '
             . '    "" AS `InstructionCategoryCode` '
             . 'FROM ' . $this->_getPair('Event', 'e') . ' '
             . 'JOIN ' . $this->_getPair('EventDocumentofCommerce', 'edoc') . ' '
             . '    ON `edoc`.`doc_number` = `e`.`doc_number` '
             . 'JOIN ' . $this->_getPair('EventFormReq', 'efr') . ' '
             . '    ON `efr`.`id_event` = `e`.`id` '
             . '    AND `efr`.`deleted` = "n" '
             . '    AND `efr`.`attended` = "1" '
             . 'JOIN ' . $this->_getPair('Member', 'm') . ' '
             . '    ON `m`.`id` = `efr`.`id_member` '
             . 'WHERE `e`.`id` = ' . $this->db->quote($this->id_event, 'integer') . ' ';

        $statement = $this->db->query($sql);
        Qs_Db::isError($statement);
        return $statement;
    }

    public function deleteCertificate()
    {
        $obj = new App_Event_Certificate_Obj();
        $obj->id = $this->id;
        $obj->deleteFile();
    }

    public function getCertificateRecipientsCount()
    {
        $sql = $this->_getCertificateRecipientsSql('COUNT(*)');
        $res = $this->db->queryOne($sql);
        Qs_Db::isError($res);
        return $res;
    }

    protected function _joinCertificateSendQueue($sendQueueAlias = 'ecsq', $eventAlias = 'e', $memberAlias = 'm')
    {
        return 'JOIN ' . $this->_getPair('EventCertificateSendQueue', $sendQueueAlias) . ' '
            . "    ON  `ecsq`.`id_event` = `{$eventAlias}`.`id` "
            . "    AND `ecsq`.`id_member` = `{$memberAlias}`.`id` ";
    }

    protected function _getCertificateRecipientsSql($columns)
    {
        if (is_array($columns)) {
            $_columns = array();
            foreach ($columns as $value) {
                if (false !== strpos($value, '(')) {
                    $_columns[] = $value;
                } else {
                    $_columns[] = Qs_Db::quoteIdentifier($value);
                }
            }
        } else {
            $_columns = array((string)$columns);
        }
        $sql = 'SELECT  ' . implode(', ', $_columns) . ' '
             . 'FROM ' . $this->_getPair() . ' '
             . $this->_joinEvent() . ' '
             . $this->_joinMember() . ' '
             . $this->_leftJoinLocation() . ' '
             . $this->_leftJoinEventCeCreditHistory() . ' '
             . $this->_leftJoinCompany() . ' '
             . 'LEFT ' . $this->_joinCertificateSendQueue() . ' '
             . 'WHERE '
             . '    ' . $this->quoteField('id_event') . ' = ' . $this->db->quote($this->id_event, 'integer') . ' '
             . '    AND ' . $this->quoteField('deleted') . ' =  "n" '
             . '    AND "y" = ' . $this->getColumnCertificateAllowed($this->tableName, 'e', 'm', 'ech') . ' '
             . '    AND `ecsq`.`id_event` IS NULL';
        return $sql;
    }

    public function removeQueue($filter = array())
    {
        $sql = 'DELETE `ecsq` '
             . 'FROM ' . $this->_getPair('EventCertificateSendQueue', 'ecsq') . ' '
             . 'WHERE 1 ';
        if (!empty($filter)) {
            $sql .= ' AND ' . Qs_Db::filter($filter, 'ecsq');
        }
        $res = $this->db->exec($sql);
        Qs_Db::isError($res);
        return $res;
    }

    public function addQueue($filter = array())
    {
        $sql = 'INSERT IGNORE  INTO ' . Qs_Db::getTableName('EventCertificateSendQueue') . ' '
             . '(`id_event`, `id_member`, `added`, `changed`) '
             . $this->_getCertificateRecipientsSql(array('e.id', 'm.id', 'NOW()', 'NOW()'));
        if (!empty($filter)) {
            $sql .= ' AND ' . Qs_Db::filter($filter, $this->tableName);
        }
        $res = $this->db->exec($sql);
        Qs_Db::isError($res);
        return $res;
    }

    public function getMembersCountWithoutReLicenseNumber()
    {
        $sql = 'SELECT COUNT(*) '
             . 'FROM ' . $this->_getPair() . ' '
             . $this->_joinEvent() . ' '
             . $this->_joinMember() . ' '
             . $this->_leftJoinLocation() . ' '
             . $this->_leftJoinCompany() . ' '
             . 'WHERE '
             . '    ' . $this->quoteField('id_event') . ' = ' . $this->db->quote($this->id_event, 'integer') . ' '
             . '    AND ' . $this->quoteField('deleted') . ' =  "n" '
             . '    AND LENGTH(`m`.`re_license_number`) = 0 ';
        $res = $this->db->queryOne($sql);
        Qs_Db::isError($res);
        return $res;
    }

    public function getRecipientsLeft()
    {
        $sql = 'SELECT COUNT(*) '
             . 'FROM ' . $this->_getPair() . ' '
             . $this->_joinEvent() . ' '
             . $this->_joinMember() . ' '
             . $this->_leftJoinEventCeCreditHistory() . ' '
             . $this->_leftJoinLocation() . ' '
             . $this->_leftJoinCompany() . ' '
             . $this->_joinCertificateSendQueue() . ' '
             . 'WHERE '
             . '    ' . $this->quoteField('id_event') . ' = ' . $this->db->quote($this->id_event, 'integer') . ' '
             . '    AND ' . $this->quoteField('deleted') . ' =  "n" '
             . '    AND "y" = ' . $this->getColumnCertificateAllowed($this->tableName, 'e', 'm', 'ech') . ' '
             . '    AND `ecsq`.`status` != "sent" ';
        $res = $this->db->queryOne($sql);
        Qs_Db::isError($res);
        return (int) $res;
    }

    public function getNotCompletedQueueItemIds()
    {
        $sql = 'SELECT ' . $this->quoteField('id') . ' '
            . 'FROM ' . $this->_getPair() . ' '
            . $this->_joinEvent() . ' '
            . $this->_joinMember() . ' '
            . $this->_leftJoinLocation() . ' '
            . $this->_leftJoinEventCeCreditHistory() . ' '
            . $this->_leftJoinCompany() . ' '
            . 'LEFT ' . $this->_joinCertificateSendQueue() . ' '
            . 'WHERE '
            . '    ' . $this->quoteField('id_event') . ' = ' . $this->db->quote($this->id_event, 'integer') . ' '
            . '    AND ' . $this->quoteField('deleted') . ' =  "n" '
            . '    AND "y" = ' . $this->getColumnCertificateAllowed($this->tableName, 'e', 'm', 'ech') . ' '
            . '    AND `ecsq`.`status` IN ("new", "processing")';
        $res = $this->db->queryCol($sql);
        Qs_Db::isError($res);
        return $res;
    }
}