<?php

class App_Report_AbstractObj extends Qs_Db_Obj
{
    const START_MONTH = 7;

    const STATUS_NOT_SUBMITTED     = 'not-submitted';
    const STATUS_SUBMITTED_ON_TIME = 'submitted-on-time';
    const STATUS_SUBMITTED_LATE    = 'submitted-late';
    const STATUS_PAST_DUE          = 'past-due';
    const REPORT_PATH = 'report';
    const REPORT_ACCESS_PATH = 'report-access';
    const REPORT_ADMIN_ACCESS_PATH = 'report-admin-auth';

    protected $_tableAlias = 'Report';

    public $itemName = 'Report';
    public $itemsName = 'Reports';

    protected static $_monthList = array(
        1  => 'January',
        2  => 'February',
        3  => 'March',
        4  => 'April',
        5  => 'May',
        6  => 'June',
        7  => 'July',
        8  => 'August',
        9  => 'September',
        10 => 'October',
        11 => 'November',
        12 => 'December',
    );

    public static function getMonthName($num)
    {
        return array_key_exists($num, self::$_monthList) ? self::$_monthList[$num] : null;
    }

    public static function getShortMonthName($num)
    {
        return substr(App_Report_AbstractObj::getMonthName($num), 0, 3);
    }

    public static function getStatusPairs()
    {
        return array(
            self::STATUS_NOT_SUBMITTED     => 'Not yet submitted',
            self::STATUS_SUBMITTED_ON_TIME => 'Submitted on Time',
            self::STATUS_SUBMITTED_LATE    => 'Submitted Late',
            self::STATUS_PAST_DUE          => 'Past due. Provide ASAP!',
        );
    }

    public static function getMonthList()
    {
        return array_slice(self::$_monthList, self::START_MONTH - 1, null, true)
        + array_slice(self::$_monthList, 0, self::START_MONTH - 1, true);
    }

    public static function getDayList($month)
    {
        $list = range(1, self::getMonthDayCount($month));
        return array_combine($list, $list);
    }

    public static function getMonthDayCount($month)
    {
        $month = (int) $month;
        $year = date('Y');
        if ((int) date('m') >= self::START_MONTH) {
            $year += $month >= self::START_MONTH ? 0 : 1;
        } else {
            $year += $month >= self::START_MONTH ? -1 : 0;
        }
        return cal_days_in_month(CAL_GREGORIAN, $month, $year);
    }

    public function getSchools4Select($enabledOnly = false)
    {
        $where = array($this->_db->quoteInto('`id` != ?', App_School_AbstractObj::ADMINISTRATION_ID, Zend_Db::INT_TYPE));
        if ($enabledOnly) {
            $where['enabled'] = 'y';
        }
        return (array) $this->_get4Select('School', array('id', 'name'), false, $where, 'sorter');
    }

    /**
     * @param $schoolId
     * @return array ex.: [$reportId => [['id', 'file', 'comments', 'added'], ...], ...]
     */
    protected function _getReportsBySchool($schoolId)
    {
        $select = $this->_db->select();
        $select->from(
            $this->_getPair('UserReport'),
            array('key' => 'reportId', 'id', 'reportId', 'file', 'comments', 'rejected', 'rejectComment', 'rejectDate', 'added')
        );
        $select->where('UserReport.schoolId = ?', $schoolId);
        return $this->_db->fetchAll($select, null, PDO::FETCH_ASSOC | PDO::FETCH_GROUP);
    }

    protected function _prepareDueIdx($month, $day)
    {
        $month = (int) $month;
        $day = (int) $day;
        $shift = ($month >= self::START_MONTH ? -(self::START_MONTH-1) : (12-self::START_MONTH+1));
        return ($month + $shift) * 100 + $day;
    }

    /**
     * @param int|string $schoolId ex.: 1 | "School.id"
     * @return Zend_Db_Expr
     */
    protected function _getStatusExpr($schoolId)
    {
        $_currentIdx = (int) $this->_prepareDueIdx(date('m'), date('d'));
        $_schoolId = Qs_Db::quoteIdField($schoolId);
        $_submittedOnTime = $this->_db->quote(self::STATUS_SUBMITTED_ON_TIME);
        $_submittedLate = $this->_db->quote(self::STATUS_SUBMITTED_LATE);
        $_pastDue = $this->_db->quote(self::STATUS_PAST_DUE);
        $_notSubmitted = $this->_db->quote(self::STATUS_NOT_SUBMITTED);

        $expr = "IF(Report.dueDateMonth IS NULL OR UserReport.addedIdx <= Report.dueIdx, {$_submittedOnTime}, {$_submittedLate})";

        $fileCheck = $this->_db->select();
        $fileCheck->from($this->_getPair('UserReport'), array('status' => new Zend_Db_Expr($expr)));
        $fileCheck->where('UserReport.reportId = Report.id');
        $fileCheck->where('UserReport.rejected = "n"');
        $fileCheck->where('UserReport.schoolId = ' . $_schoolId);
        $fileCheck->order('UserReport.id');
        $fileCheck->limit(1);

        $excludeCheck = $this->_db->select();
        $excludeCheck->from($this->_getPair('ReportExclude'), array('id'));
        $excludeCheck->where('ReportExclude.reportId = Report.id');
        $excludeCheck->where('ReportExclude.schoolId = ' . $_schoolId);
        $excludeCheck->limit(1);

        $forcedStatus = $this->_getForcedStatusExpr($schoolId);

        $sql = <<<HEREDOC
    IFNULL(
        {$forcedStatus},
        IF(
            ({$excludeCheck}) IS NOT NULL,
            {$_submittedOnTime},
            IFNULL(
                ({$fileCheck}),
                IF({$_currentIdx} > Report.dueIdx, {$_pastDue}, {$_notSubmitted})
            )
        )
    )
HEREDOC;

        return new Zend_Db_Expr($sql);
    }

    protected function _getUserReportDateExpr()
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('UserReport'), array('added'));
        $select->where('UserReport.reportId = Report.id');
        $select->where('UserReport.rejected = "n"');
        $select->where('UserReport.schoolId = School.id');
        $select->order('UserReport.id');
        $select->limit(1);
        return new Zend_Db_Expr('(' . $select . ')');
    }

    public function getReportById($reportId)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('Report'), array('*'));
        $select->where('id = ?', $reportId);
        return $this->_db->fetchRow($select);
    }

    public function getReports()
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('UserReport'), array('id', 'file'));
        return $this->_db->fetchPairs($select);
    }

    public function deleteUserReport($reportId, $fileId)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('UserReport'), array('file'));
        $select->where('reportId = ?', $reportId);
        $select->where('id = ?', $fileId);
        $select->limit(1);

        if (($file = $this->_db->fetchOne($select))) {
            $this->_getTable('UserReport')->delete(array('reportId = ?' => $reportId, 'id = ?' => $fileId));
            $fileTransferAdapter = new Qs_File_Transfer_Adapter_Db();
            $fileTransferAdapter->setDefaultDestination(App_Report_AbstractObj::REPORT_PATH);
            $fileTransferAdapter->delete($file);
        }
    }

    public static function getForceStatusPairs()
    {
        return array(
            self::STATUS_NOT_SUBMITTED     => 'Not Submitted',
            self::STATUS_SUBMITTED_ON_TIME => 'On Time',
            self::STATUS_SUBMITTED_LATE    => 'Late',
            self::STATUS_PAST_DUE          => 'Past due',
        );
    }

    /**
     * Should be used as sub select for Reports table
     *
     * @param int|string $schoolId ex.: 1 | "School.id"
     * @return Zend_Db_Expr
     */
    protected function _getForcedStatusExpr($schoolId)
    {
        $_schoolId = Qs_Db::quoteIdField($schoolId);
        $select = Qs_Db::getInstance()->select();
        $select->from(Qs_Db::getPair('ReportForceStatus'), array('status'));
        $select->where('Report.dueDateMonth IS NULL');
        $select->where('ReportForceStatus.reportId = Report.id');
        $select->where('ReportForceStatus.schoolId = ' . $_schoolId);
        $select->limit(1);
        return new Zend_Db_Expr('(' . $select . ')');
    }

    public function forceReportStatus($reportId, $schoolId, $statusId)
    {
        if (empty($statusId)) {
            $this->_getTable('ReportForceStatus')->delete(
                array('reportId = ?' => $reportId, 'schoolId = ?' => $schoolId)
            );
            return true;
        }

        if (!array_key_exists($statusId, self::getForceStatusPairs())) {
            return false;
        }

        $_reportForceStatus = $this->_getTableName('ReportForceStatus');
        $_reportId = (int) $reportId;
        $_schoolId = (int) $schoolId;
        $_statusId = $this->_db->quote($statusId);
        $_date = $this->_db->quote(date('Y-m-d H:i:s'));

        $sql = <<<HEREDOC
    INSERT INTO `{$_reportForceStatus}` (`reportId`, `schoolId`, `status`, `changed`) VALUES
    ({$_reportId}, {$_schoolId}, {$_statusId}, {$_date})
    ON DUPLICATE KEY UPDATE
    `status` = {$_statusId}, `changed` = {$_date}
HEREDOC;

        $this->_db->query($sql);
        return true;
    }

    public function rejectUserReport($fileId, $comment = null)
    {
        $this->_getTable('UserReport')->update(
            array('rejected' => 'y', 'rejectComment' => $comment, 'rejectDate' => date('Y-m-d H:i:s')),
            array('id = ?' => $fileId)
        );
        return true;
    }

    public function getReportFileRow($fileId)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('UserReport'), array('*'));
        $select->where('id = ?', $fileId, Qs_Db::INT_TYPE);
        $select->limit(1);
        return $this->_db->fetchRow($select);
    }

    public function getReportRejectMailData($fileId)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('UserReport'), array('*'));
        $select->join(
            $this->_getPair('Report'),
            'Report.id = UserReport.reportId',
            array(
                'reportSection' => 'section',
                'reportTopic'   => 'topic',
            )
        );
        $select->join(
            $this->_getPair('School'),
            'School.id = UserReport.schoolId',
            array('schoolName' => 'name')
        );
        $select->where('UserReport.id = ?', $fileId, Qs_Db::INT_TYPE);
        $select->limit(1);
        return $this->_db->fetchRow($select);
    }

    public function getSchoolReportContacts($schoolId)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('User'), array('id', 'name', 'email'));
        $select->where('User.idSchool = ?', $schoolId, Qs_Db::INT_TYPE);
        $select->where('User.reportContact = "y"');
        return $this->_db->fetchAll($select);
    }

    public function getSchoolReportsAccessUsers($schoolId, $userId = null)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('User'), array('id', 'name', 'email'));
        $select->where('User.idSchool = ?', $schoolId, Qs_Db::INT_TYPE);
        $select->where('User.reportsAccess = "y"');
        if ($userId) {
            $select->where('User.id = ?', $userId, Qs_Db::INT_TYPE);
        }
        return $this->_db->fetchAll($select);
    }

    public function hasUserReports($schoolId = null)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('UserReport'), 'id')
            ->limit(1);
        if ($schoolId) {
            $select->where('schoolId = ?', $schoolId, Qs_Db::INT_TYPE);
        }
        return (bool) $this->_db->fetchOne($select);
    }

    public function getStatusStatistics($schoolId = null, $calculatePercents = true)
    {
        if (!$schoolId) {
            throw new Exception('School ID is not set.');
        }

        if (!is_array($schoolId)) {
            $schoolId = array($schoolId);
        }

        $result = array();
        foreach ($schoolId as $id) {
            $statuses = $this->_db->select();
            $statuses->from($this->_getPair('Report'), array('status' => $this->_getStatusExpr($id)));

            $sql = 'SELECT `Statuses`.`status`, COUNT(`Statuses`.`status`) AS `count` '
                 . 'FROM (' . $statuses . ') AS `Statuses` '
                 . 'GROUP BY `Statuses`.`status`';

            $statuses = $this->_db->fetchPairs($sql);
            if ($calculatePercents) {
                $statuses = $this->_prepareStatusStatistics($statuses);
            }
            $result[$id] = $statuses;
        }

        if (1 == count($result)) {
            return reset($result);
        }
        return $result;
    }

    protected function _prepareStatusStatistics($statuses)
    {
        $keys = array(
            self::STATUS_NOT_SUBMITTED     => 'notSubmitted',
            self::STATUS_SUBMITTED_ON_TIME => 'submittedOnTime',
            self::STATUS_SUBMITTED_LATE    => 'submittedLate',
            self::STATUS_PAST_DUE          => 'pastDue',
        );

        $statistics = array();
        foreach ($keys as $status => $key) {
            $count = $percents = 0;
            if (array_key_exists($status, $statuses)) {
                $count = (int) $statuses[$status];
            }
            $statistics[$key] = array(
                'count'    => $count,
                'percents' => $percents,
            );
        }

        // Does not include "Not yet submitted"
        $statistics['total'] = array(
            'count' => $statistics['submittedOnTime']['count']
                + $statistics['submittedLate']['count']
                + $statistics['pastDue']['count'],
        );

        foreach (array('submittedOnTime', 'submittedLate', 'pastDue') as $key) {
            $statistics[$key]['percents'] = round($statistics[$key]['count'] * 100 / $statistics['total']['count']);
        }

        return $statistics;
    }
}