<?php

class App_Report_Summary_Obj extends App_Report_AbstractObj
{
    public function getSchools($schoolId = null)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('School'))
            ->where('`School`.`enabled` = "y"');
        $select->order('School.sorter');

        if ($schoolId) {
            $select->where('School.id = ?', $schoolId, Qs_Db::INT_TYPE);
        }

        return $this->_db->fetchAll($select);
    }

    public function getReportStatuses($schoolId = null)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('Report'), array('reportId' => 'id'));
        $select->join(
            $this->_getPair('School'),
            '`School`.`enabled` = "y"',
            array(
                'schoolId' => 'id',
                'status'   => $this->_getStatusExpr('School.id')
            )
        );
        if ($schoolId) {
            $select->where('School.id = ?', $schoolId, Qs_Db::INT_TYPE);
        }
        $list = $this->_db->fetchAll($select);

        $result = array();
        foreach ((array) $list as $row) {
            $result[$row['reportId']][$row['schoolId']] = $row['status'];
        }
        return $result;
    }

    public function getStatusesStatistics()
    {
        $statuses = $this->_db->select();
        $statuses->from($this->_getPair('Report'), array());
        $statuses->join(
            $this->_getPair('School'),
            '`School`.`enabled` = "y"',
            array(
                'status'   => $this->_getStatusExpr('School.id')
            )
        );

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

        return $this->_prepareStatusStatistics($statuses);
    }

    public function getReportFileDates($schoolId = null)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('Report'), array('reportId' => 'id'));
        $select->join(
            $this->_getPair('School'),
            '`School`.`enabled` = "y"',
            array(
                'schoolId' => 'id',
                'added'     => $this->_getUserReportDateExpr(),
            )
        );
        if ($schoolId) {
            $select->where('School.id = ?', $schoolId, Qs_Db::INT_TYPE);
        }
        $list = $this->_db->fetchAll($select);

        $result = array();
        foreach ((array) $list as $row) {
            $result[$row['reportId']][$row['schoolId']] = $row['added'];
        }
        return $result;
    }

    public function getUserReports()
    {
        $select = $this->_db->select();
        $select->from(
            $this->_getPair('UserReport'),
            array('reportId', 'schoolId', 'file', 'comments', 'addedIdx', 'rejected', 'rejectComment', 'rejectDate', 'added')
        );
        $select->order('added ASC');
        $list = $this->_db->fetchAll($select);

        $result = array();
        foreach ((array) $list as $row) {
            $result[$row['reportId']][$row['schoolId']][] = $row;
        }

        return $result;
    }

    public function getForcedStatuses($schoolId = null)
    {
        $select = $this->_db->select();
        $select->from(Qs_Db::getPair('ReportForceStatus'), array('reportId', 'schoolId', 'status'));
        $select->join(Qs_Db::getPair('Report'), 'Report.id = ReportForceStatus.reportId AND Report.dueDateMonth IS NULL');
        if ($schoolId) {
            $select->where('ReportForceStatus.schoolId = ?', $schoolId, Qs_Db::INT_TYPE);
        }
        $list = $this->_db->fetchAll($select);

        $result = array();
        foreach ((array) $list as $row) {
            $result[$row['reportId']][$row['schoolId']] = $row['status'];
        }

        return $result;
    }

}