<?php

define_class(__FILE__, 'MemberEventHistory');

require_once ('app/Event/Form/Req/AbstractObj.php');
require_once('app/Event/Certificate/Obj.php');
require_once('class/DB/DBObj.php');
require_once('app/Event/Certificate/Send/Obj.php');

class MemberEventHistory extends App_Event_Form_Req_AbstractObj
{
    var $tableName = 'EventFormReq';
    var $itemName = 'Event History';
    var $delMethod   = DB_OBJ_DEL_METHOD_LOGICAL;
    var $individualData = null;

    function MemberEventHistory($id = null, $id_individual = null)
    {
        $this->DBObj($id);
        $this->id_individual = intval($id_individual);
        $this->individualData = $this->getMemberById($this->id_individual);
    }

    function getFromDbSql($id)
    {
        $sql = 'SELECT '
             . '    `' . $this->tableName . '`.*, '
             . '    ' . $this->getColumnCertificateAllowed($this->tableName, 'e', 'm', 'ech') . ' AS `certificate_allowed` '
             . 'FROM `' . $this->tableNameDB . '` AS `' . $this->tableName . '` '
             . $this->_joinEvent() . ' '
             . $this->_joinMember() . ' '
             . $this->_leftJoinEventCeCreditHistory() . ' '
             . 'WHERE `' . $this->tableName . '`.`id` = ' . $this->db->quote($id, 'integer') . ' '
             . 'AND ' . $this->quoteField('deleted') . ' = "n"';
        return $sql;
    }

    function _getWhat4Grid($opt = array())
    {
        $sql = "{$this->tableName}.*, "
             . "IF({$this->tableName}.attended = 1, 'Yes', 'No') as attended, "
             . 'e.name as event_name, '
             . 'e.eventdate as event_date, '
             .  $this->getColumnCertificateAllowed($this->tableName, 'e', 'm', 'ech') . ' AS `certificate_allowed` ';
        return $sql;
    }

    public function getColumnCertificateAllowed($requestAlias, $eventAlias, $memberAlias, $ceHistoryAlias)
    {
        return "
            IF (
                `EventCertificateSendQueue`.`status` = {$this->db->quote(App_Event_Certificate_Send_Obj::STATUS_SENT)},
                " . App_Event_Certificate_Obj::getColumnCertificateAllowed(
                                    $requestAlias,
                                    $eventAlias,
                                    $memberAlias,
                                    $ceHistoryAlias
                    ) . ",
                'n'
                )
        ";
    }

    protected function _leftJoinCertificateQueue()
    {
        return "
            LEFT JOIN {$this->_getPair('EventCertificateSendQueue')}
                ON `EventCertificateSendQueue`.`id_event` = {$this->quoteField('id_event')}
                AND `EventCertificateSendQueue`.`id_member` = {$this->quoteField('id_member')}
        ";
    }

    function _getJoin4Grid()
    {
        $sql  = $this->_joinEvent() . ' '
              . $this->_joinMember() . ' '
              . $this->_leftJoinEventCeCreditHistory() . ' '
              . $this->_leftJoinCertificateQueue();
        return $sql;
    }

    function _getGroup4Grid()
    {
        return "GROUP BY e.id";
    }

    function _getWhere4Grid($opt = array())
    {
        $where = parent::_getWhere4Grid($opt). " AND {$this->tableName}.id_member=".$this->db->quote($this->id_individual, 'integer');
        return $where;
    }

    function findEvent($q)
    {
        $addonWhere = '';
        if (!Member::hasEventRegistration($this->individualData)) {
            $addonWhere = ' AND Event.id_type = 2 ';
        }
        $sql = "
            SELECT *
            FROM {$this->db->tblEvent} as Event
            WHERE (Event.name LIKE '".$this->db->escape($q)."%' OR DATE_FORMAT(Event.eventdate, '%m/%d/%Y') LIKE '".$this->db->escape($q)."%' )
                {$addonWhere}
            ORDER BY Event.eventdate DESC
        ";
        $res = $this->db->queryAll($sql);
        $this->_sqlError($res);
        return $res;
    }

    function isUniqueEvent($id_individual, $id_event)
    {
        $sql = "
            SELECT COUNT(*)
            FROM {$this->tableNameDB}
            WHERE 	id_member = ".$this->db->quote($id_individual, 'integer')."
                    AND id_event = ".$this->db->quote($id_event, 'integer')."
                    AND deleted = 'n'
        ";
        $res = $this->db->queryOne($sql);
        $this->_sqlError($res);
        return !(bool)$res;
    }

    function getEvent4Select()
    {
        $addonWhere = '';
        if (!Member::hasEventRegistration($this->individualData)) {
            $addonWhere = ' AND Event.id_type = 2 ';
        }
        $t = mktime(0, 0, 0, date('m'), date('d'), date('Y')-1);
        $sql = "
            SELECT Event.id, Event.name
            FROM {$this->db->tblEvent} as Event
            WHERE 1
                AND UNIX_TIMESTAMP(Event.eventdate) > {$t}
                {$addonWhere}
            ORDER BY Event.eventdate DESC
        ";
        $res = $this->db->queryAll($sql, 0, null, true);
        $this->_sqlError($res);
        return $res;
    }
}