<?php

class App_Report_CronObj extends App_Report_AbstractObj
{
    /**
     * @param int $x
     * @return array|null
     */
    public function getOverdueWithinXDayReports($x)
    {
        $timestamp = time() + ($x * 24 * 60 * 60);
        return $this->getOverdueInDateReports($timestamp);
    }

    public function getOverdueTodayReports()
    {
        $timestamp = time();
        return $this->getOverdueInDateReports($timestamp);
    }

    /**
     * @param null $timestamp
     *
     * @return array|null ex.:
     * [
     *     'timestamp' =>,
     *     'date' =>,
     *     'list' => [
     *         [
     *             'schoolId' =>,
     *             'schoolName' =>,
     *             'userList' => [
     *                    ['id'=>, 'name'=>, 'email'=>]
     *              ],
     *              'list' => [
     *                  ['id', 'dueDateMonth', 'dueDateDay', 'dueIdx', 'section', 'topic', 'description'],
     *                  ...
     *              ]
     *         ...
     *         ],
     *     ]
     * ]
     */
    public function getOverdueInDateReports($timestamp = null)
    {
        $timestamp = (null === $timestamp) ? time() : $timestamp;
        $dueIdx = $this->_prepareDueIdx(date('m', $timestamp), date('d', $timestamp));
        $select = $this->_getMissingSchoolReportsSelect();
        $select->where('Report.dueIdx = ?', $dueIdx, Qs_Db::INT_TYPE);
        if (null == ($list = $this->_db->fetchAll($select))) {
            return null;
        }
        $list = $this->_prepareOverdueReportList($list);
        return array(
            'timestamp' => $timestamp,
            'date'      => date('Y-m-d', $timestamp),
            'list'      => $list,
        );
    }

    protected function _prepareOverdueReportList(array $list)
    {
        $result = array();
        $schoolIds = array();
        foreach ($list as $row) {
            if (!array_key_exists($row['schoolId'], $result)) {
                $schoolIds[] = $row['schoolId'];
                $result[$row['schoolId']] = array(
                    'schoolId'   => $row['schoolId'],
                    'schoolName' => $row['schoolName'],
                    'list'       => array(),
                );
            }
            $result[$row['schoolId']]['list'][] = $row;
        }
        if ($schoolIds && ($schoolUsers  = $this->_getSchoolUsers($schoolIds))) {
            foreach ($schoolUsers as $schoolId => $userList) {
                if (isset($result[$schoolId])) {
                    $result[$schoolId]['userList'] = $userList;
                }
            }
        }
        return $result;
    }

    protected function _getMissingSchoolReportsSelect()
    {
        $fileCheck = $this->_db->select();
        $fileCheck->from($this->_getPair('UserReport'), array('id'));
        $fileCheck->where('UserReport.reportId = Report.id');
        $fileCheck->where('UserReport.schoolId = School.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 = School.id');
        $excludeCheck->limit(1);

        $select = $this->_db->select();
        $select->from($this->_getPair('Report'), array('*'));
        $select->join($this->_getPair('School'), 'School.enabled = "y"', array('schoolId' => 'id', 'schoolName' => 'name'));
        $select->where('NOT EXISTS(' . $fileCheck . ')');
        $select->where('NOT EXISTS(' . $excludeCheck . ')');
        $select->order('Report.dueIdx');

        return $select;
    }

    protected function _getSchoolUsers(array $schoolIds)
    {
        if (empty($schoolIds)) {
            return $schoolIds;
        }

        $select = $this->_db->select();
        $select->from($this->_getPair('User'), array('idSchool', 'id', 'name', 'email'));
        $select->where('User.idSchool IN(?)', $schoolIds, Qs_Db::INT_TYPE);
        $select->where('User.reportContact = "y"');
        return $this->_db->fetchAll($select, null, Qs_Db::FETCH_GROUP);
    }
}