<?php
define_class(__FILE__, 'CECreditHistory');

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

class CECreditHistory extends App_Event_Form_Req_AbstractObj
{
    var $tableName = 'EventCECreditHistory';
    var $itemName = 'CE Credit';
    var $itemsName = 'CE Credits';

    function initFromForm(&$frm)
    {
        $files = array();
        $data = $frm->exportValues();
        $data['id_individual'] = intval($this->memberData['id']);
        $data['event_date'] = $this->initDateFromForm($data['event_date']);
        $this->arrayParseUrl2Tag($data);
        $this->initFromArray($data, $files);
        return true;
    }

    function initDateFromForm($date)
    {
        if (intval($date['Y']) && intval($date['M']) && intval($date['d'])) {
            return sprintf('%04d-%02d-%02d', $date['Y'], $date['M'], $date['d']);
        }
        return null;
    }

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


    function _getWhat4Grid($opt = array())
    {
        $sql = $this->tableName . '.*, '
             . 'EventFormReq.id eventformreqid, '
             . 'IF(attended = 1, "Yes", "No") as attended, '
             . $this->_getCreditsColumn() . ' as credits, '
             . '`EventFormReq`.`id` AS `registration_id`, '
             . $this->getColumnCertificateAllowed('EventFormReq', 'e', 'm', $this->tableName) . ' 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 _getCreditsColumn()
    {
        return "IF(attended = 1 OR EventFormReq.id IS NULL, {$this->tableName}.credits, 0)";
    }

    protected function _leftJoinEventFormReq()
    {
        return 'LEFT JOIN ' . $this->_getPair('EventFormReq') . ' '
             . '    ON  `EventFormReq`.`id_event` = ' . $this->quoteField('id_event') . ' '
             . '    AND `EventFormReq`.`id_member` = ' . $this->quoteField('id_individual') . ' '
             . '    AND `EventFormReq`.`deleted` = "n"';
    }

    function _getJoin4Grid()
    {
        $sql = $this->_leftJoinEventFormReq() . ' '
            . $this->_leftJoinEvent('EventFormReq') . ' '
            . $this->_leftJoinCertificateQueue() . ' '
            . $this->_leftJoinMember('EventFormReq') . ' ';
        return $sql;
    }

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

    function _getMemberEventPeriods()
    {
        $_memberId = (int) $this->memberData['id'];
        $sql = "
            SELECT
              YEAR(`{$this->tableName}`.`event_date`) AS `event_year`,
            IF(MONTH(`{$this->tableName}`.`event_date`) < 7, 1, 2) as `event_period`
            FROM {$this->tableNameDB} AS `{$this->tableName}`
            LEFT JOIN {$this->db->tblEventFormReq} AS `EventFormReq`
                ON `EventFormReq`.`id_event` = `{$this->tableName}`.`id_event`
                AND `EventFormReq`.`id_member` = `{$this->tableName}`.`id_individual`
                AND (`EventFormReq`.`attended` = 1 OR EventFormReq.id IS NULL)
            WHERE `{$this->tableName}`.`id_individual` = {$_memberId}
            GROUP BY `event_year`, `event_period`
        ";
        return $this->db->queryAll($sql);
    }

    /**
     * @return array|null array($value, $options)
     */
    function getPeriodsInfo()
    {
        $periods = $this->_getMemberEventPeriods();
        if ($this->isDBError($periods)) {
            return null;
        }

        $value = null;
        $options = array();

        $curDate = date('Y-m-d');
        foreach ($periods as $period) {
            $year = (1 == $period['event_period']) ? $period['event_year'] - 1 : $period['event_year'];
            $startDate = $year . '-07-01';
            $endDate = ($year + 1) . '-06-30';
            $optionValue = $startDate . ' ' . $endDate;
            $optionTitle = 'July 1, ' . $year . ' - June 30, ' . ($year + 1);
            $options[$optionValue] = $optionTitle;

            if (null === $value && $startDate <= $curDate && $curDate <= $endDate) {
                $value = $optionValue;
            }
        }
        return array($value, $options);
    }

    function setFilter($filter)
    {
        if (isset($filter['query']) && !empty($filter['query'])) {
            $this->_filterQuery = $filter['query'];
            unset($filter['query']);
        }
        $this->filter = $filter;
    }

    function _filterSql()
    {
        require_once('class/DB/DBObj/Filter.php');
        $sql = DBObj_Filter::perseQr($this->db, $this->_filterFields, $this->_filterQuery);

        // filter periods
        if (!empty($this->_filter['period'])
            && ($periods = explode(' ', $this->_filter['period']))
        ) {
            $sql = $sql ? $sql : '';
            list($startDate, $endDate) = $periods;
            $_startDate = $this->db->quote($startDate);
            $_endDate = $this->db->quote($endDate);
            $sql .= " AND (`{$this->tableName}`.`event_date` BETWEEN {$_startDate} AND {$_endDate})";
        }

        return (empty($sql)) ? false : $sql;
    }

    function getTotal4Period()
    {
        $res = $this->_getRes4Grid('SUM(' . $this->_getCreditsColumn() . ')', array());
        Qs_Db::isError($res);
        return floatval($res->fetchOne());
    }

    function _getWhere4Grid($opt = array())
    {
        $where = parent::_getWhere4Grid($opt);
        $where .= " AND {$this->tableName}.id_individual = ".$this->db->quote($this->memberData['id'], 'integer')."
            AND (
                {$this->tableName}.id_event > 0 AND e.id IS NOT NULL
                OR {$this->tableName}.id_event = 0
            )
        ";
        return $where;
    }
}