highcharts test script

controller.SiteController.php

<?php

class SiteController extends Controller
{
    /**
     * Declares class-based actions.
     */
    public function actions()
    {
        return array(
            // captcha action renders the CAPTCHA image displayed on the contact page
            'captcha'=>array(
                'class'=>'CCaptchaAction',
                'backColor'=>0xFFFFFF,
            ),
            // page action renders "static" pages stored under 'protected/views/site/pages'
            // They can be accessed via: index.php?r=site/page&view=FileName
            'page'=>array(
                'class'=>'CViewAction',
            ),
        );
    }

    /**
     * This is the default 'index' action that is invoked
     * when an action is not explicitly requested by users.
     */
    public function actionIndex()
    {
        $subject = new Subject();
        $student = new Student();
        $mark = new Mark();

        $averageMarks = new CArrayDataProvider($subject->getAverageMarks());
        $averageMarkOfStudent = new CArrayDataProvider($student->getAverageMarkOfStudentBySubject());
        $numberOfStudents = $student->getNumberOfSuccessfulMathematicians();
        $subjectWithLosers = new CArrayDataProvider($subject->getSubjectWithLosers());
        $chartData = $mark->getDataForChart();

        $this->render('index', array(
            'averageMarks' => $averageMarks,
            'averageMarkOfStudent' => $averageMarkOfStudent,
            'numberOfStudents' => $numberOfStudents,
            'subjectWithLosers' => $subjectWithLosers,
            'chartData' => $chartData,
        ));
    }

    /**
     * This is the action to handle external exceptions.
     */
    public function actionError()
    {
        if($error=Yii::app()->errorHandler->error)
        {
            if(Yii::app()->request->isAjaxRequest)
                echo $error['message'];
            else
                $this->render('error', $error);
        }
    }
}

models.Mark.php

<?php

/**
 * This is the model class for table "mark".
 *
 * The followings are the available columns in table 'mark':
 * @property integer $id
 * @property integer $student_id
 * @property integer $subject_id
 * @property integer $value
 * @property string $date
 *
 * The followings are the available model relations:
 * @property Subject $subject
 * @property Student $student
 */
class Mark extends CActiveRecord
{
    /**
     * @return string the associated database table name
     */
    public function tableName()
    {
        return 'mark';
    }

    /**
     * @return array validation rules for model attributes.
     */
    public function rules()
    {
        // NOTE: you should only define rules for those attributes that
        // will receive user inputs.
        return array(
            array('student_id, subject_id, value, date', 'required'),
            array('student_id, subject_id, value', 'numerical', 'integerOnly'=>true),
            // The following rule is used by search().
            // @todo Please remove those attributes that should not be searched.
            array('id, student_id, subject_id, value, date', 'safe', 'on'=>'search'),
        );
    }

    /**
     * @return array relational rules.
     */
    public function relations()
    {
        // NOTE: you may need to adjust the relation name and the related
        // class name for the relations automatically generated below.
        return array(
            'subject' => array(self::BELONGS_TO, 'Subject', 'subject_id'),
            'student' => array(self::BELONGS_TO, 'Student', 'student_id'),
        );
    }

    /**
     * @return array customized attribute labels (name=>label)
     */
    public function attributeLabels()
    {
        return array(
            'id' => 'ID',
            'student_id' => 'Student',
            'subject_id' => 'Subject',
            'value' => 'Value',
            'date' => 'Date',
        );
    }

    /**
     * Retrieves a list of models based on the current search/filter conditions.
     *
     * Typical usecase:
     * - Initialize the model fields with values from filter form.
     * - Execute this method to get CActiveDataProvider instance which will filter
     * models according to data in model fields.
     * - Pass data provider to CGridView, CListView or any similar widget.
     *
     * @return CActiveDataProvider the data provider that can return the models
     * based on the search/filter conditions.
     */
    public function search()
    {
        // @todo Please modify the following code to remove attributes that should not be searched.

        $criteria=new CDbCriteria;

        $criteria->compare('id',$this->id);
        $criteria->compare('student_id',$this->student_id);
        $criteria->compare('subject_id',$this->subject_id);
        $criteria->compare('value',$this->value);
        $criteria->compare('date',$this->date,true);

        return new CActiveDataProvider($this, array(
            'criteria'=>$criteria,
        ));
    }

    /**
     * Returns the static model of the specified AR class.
     * Please note that you should have this exact method in all your CActiveRecord descendants!
     * @param string $className active record class name.
     * @return Mark the static model class
     */
    public static function model($className=__CLASS__)
    {
        return parent::model($className);
    }

    public function getDataForChart()
    {
        $query = Yii::app()->db->createcommand("
            SELECT subject.title, mark.date, AVG(mark.value) AS avg_mark
            FROM subject
            INNER JOIN mark ON subject.id = mark.subject_id
            GROUP BY mark.date, subject.title
            ORDER BY mark.date
        ")->queryAll();

        //here we prepare data to chart
        $data = array();
        foreach($query as $key => $value){
            $date = new DateTime($value['date']);
            $date->modify('-1 month');
            $data[$value['title']] .= "[Date.UTC(".str_replace('-', ', ',$date->format('Y-m-d'))."), {$value['avg_mark']}],";
        }
        return $data;
    }
}

models.Student.php

<?php

/**
 * This is the model class for table "student".
 *
 * The followings are the available columns in table 'student':
 * @property integer $id
 * @property string $name
 *
 * The followings are the available model relations:
 * @property Mark[] $marks
 */
class Student extends CActiveRecord
{
    /**
     * @return string the associated database table name
     */
    public function tableName()
    {
        return 'student';
    }

    /**
     * @return array validation rules for model attributes.
     */
    public function rules()
    {
        // NOTE: you should only define rules for those attributes that
        // will receive user inputs.
        return array(
            array('name', 'required'),
            array('name', 'length', 'max'=>255),
            // The following rule is used by search().
            // @todo Please remove those attributes that should not be searched.
            array('id, name', 'safe', 'on'=>'search'),
        );
    }

    /**
     * @return array relational rules.
     */
    public function relations()
    {
        // NOTE: you may need to adjust the relation name and the related
        // class name for the relations automatically generated below.
        return array(
            'marks' => array(self::HAS_MANY, 'Mark', 'student_id'),
        );
    }

    /**
     * @return array customized attribute labels (name=>label)
     */
    public function attributeLabels()
    {
        return array(
            'id' => 'ID',
            'name' => 'Name',
        );
    }

    /**
     * Retrieves a list of models based on the current search/filter conditions.
     *
     * Typical usecase:
     * - Initialize the model fields with values from filter form.
     * - Execute this method to get CActiveDataProvider instance which will filter
     * models according to data in model fields.
     * - Pass data provider to CGridView, CListView or any similar widget.
     *
     * @return CActiveDataProvider the data provider that can return the models
     * based on the search/filter conditions.
     */
    public function search()
    {
        // @todo Please modify the following code to remove attributes that should not be searched.

        $criteria=new CDbCriteria;

        $criteria->compare('id',$this->id);
        $criteria->compare('name',$this->name,true);

        return new CActiveDataProvider($this, array(
            'criteria'=>$criteria,
        ));
    }

    /**
     * Returns the static model of the specified AR class.
     * Please note that you should have this exact method in all your CActiveRecord descendants!
     * @param string $className active record class name.
     * @return Student the static model class
     */
    public static function model($className=__CLASS__)
    {
        return parent::model($className);
    }

    /**
     * @return mixed
     */
    public function getAverageMarkOfStudentBySubject()
    {
        $date = new DateTime();
        //запрос работает при условии что у каждого студента/предмета есть оценка в текущем месяце
        //в противном случае просто не выводит студента у которого нет оценки по предмету за текущий месяц
        //сделал так потому что сходу придумать правильный запрос не получилось а времени осталось мало
        //на то что бы отправить задание сегодня, для того что бы оно было проверено к моменту моего
        //прихода на собеседование во вторник утром
        return Yii::app()->db->createCommand("
            SELECT student.name, subject.title, AVG(mark.value) AS avg_mark
            FROM subject
            INNER JOIN mark ON subject.id = mark.subject_id
            INNER JOIN student ON student.id = mark.student_id
            WHERE mark.date >= '{$date->format('Y-m')}'
            GROUP BY student.name, subject.title
            ORDER BY student.name
        ")->queryAll();
    }

    /**
     * @return mixed
     */
    public function getNumberOfSuccessfulMathematicians()
    {
        $query = Yii::app()->db->createCommand("
            SELECT student_id
            FROM mark
            WHERE subject_id=3
            GROUP BY student_id
            HAVING AVG(value)>4
        ")->queryAll();

        return count($query);
    }
}

models.Subject.php

<?php

/**
 * This is the model class for table "subject".
 *
 * The followings are the available columns in table 'subject':
 * @property integer $id
 * @property string $title
 *
 * The followings are the available model relations:
 * @property Mark[] $marks
 */
class Subject extends CActiveRecord
{
    /**
     * @return string the associated database table name
     */
    public function tableName()
    {
        return 'subject';
    }

    /**
     * @return array validation rules for model attributes.
     */
    public function rules()
    {
        // NOTE: you should only define rules for those attributes that
        // will receive user inputs.
        return array(
            array('title', 'required'),
            array('title', 'length', 'max'=>255),
            // The following rule is used by search().
            // @todo Please remove those attributes that should not be searched.
            array('id, title', 'safe', 'on'=>'search'),
        );
    }

    /**
     * @return array relational rules.
     */
    public function relations()
    {
        // NOTE: you may need to adjust the relation name and the related
        // class name for the relations automatically generated below.
        return array(
            'marks' => array(self::HAS_MANY, 'Mark', 'subject_id'),
        );
    }

    /**
     * @return array customized attribute labels (name=>label)
     */
    public function attributeLabels()
    {
        return array(
            'id' => 'ID',
            'title' => 'Title',
        );
    }

    /**
     * Retrieves a list of models based on the current search/filter conditions.
     *
     * Typical usecase:
     * - Initialize the model fields with values from filter form.
     * - Execute this method to get CActiveDataProvider instance which will filter
     * models according to data in model fields.
     * - Pass data provider to CGridView, CListView or any similar widget.
     *
     * @return CActiveDataProvider the data provider that can return the models
     * based on the search/filter conditions.
     */
    public function search()
    {
        // @todo Please modify the following code to remove attributes that should not be searched.

        $criteria=new CDbCriteria;

        $criteria->compare('id',$this->id);
        $criteria->compare('title',$this->title,true);

        return new CActiveDataProvider($this, array(
            'criteria'=>$criteria,
        ));
    }

    /**
     * Returns the static model of the specified AR class.
     * Please note that you should have this exact method in all your CActiveRecord descendants!
     * @param string $className active record class name.
     * @return Subject the static model class
     */
    public static function model($className=__CLASS__)
    {
        return parent::model($className);
    }

    /**
     * @return mixed
     */
    public function getAverageMarks()
    {
        $date = new DateTime();
        return Yii::app()->db->createCommand("
            SELECT subject.title, AVG(mark.value) AS avg_mark
            FROM subject
            INNER JOIN mark ON subject.id = mark.subject_id
            WHERE mark.date >= '{$date->format('Y-m')}'
            GROUP BY subject.title
            ORDER BY avg_mark DESC
        ")->queryAll();

    }

    /**
     * @return mixed
     */
    public function getSubjectWithLosers()
    {
        return Yii::app()->db->createCommand("
            SELECT subject.title
            FROM subject
            INNER JOIN mark ON subject.id=mark.subject_id
            WHERE mark.value=2
            GROUP BY subject.title
            HAVING COUNT(mark.id)>2
        ")->queryAll();
    }
}

vews.site.index.php

<?php
/* @var $this SiteController */

$this->pageTitle=Yii::app()->name;

echo CHtml::tag('b', array(), 'Cредняя оценка по предмету по всем ученикам за текущий месяц:');
$this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider' => $averageMarks,
    'columns' => array(
        'title',
        'avg_mark'
    ),
));

echo CHtml::tag('b', array(), 'Cредняя оценка студента по предмету за текущий месяц:');
$this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider' => $averageMarkOfStudent,
    'columns' => array(
        'name',
        'title',
        'avg_mark'
    ),
));

echo CHtml::tag('b', array(), "Предметы, где количество двоешников больше двух:");
$this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider' => $subjectWithLosers,
    'columns' => array(
        'title',
    ),
));

echo CHtml::tag('b', array(), "Количество учеников, у которых средняя оценка по математике выше четверки = {$numberOfStudents}");

Yii::app()->clientScript->registerScriptFile('http://code.highcharts.com/highcharts.js', CClientScript::POS_HEAD);
Yii::app()->clientScript->registerScriptFile('http://code.highcharts.com/modules/exporting.js', CClientScript::POS_HEAD);
Yii::app()->clientScript->registerScript('myLineChart', "
    $(function () {
        $('#chart_container').highcharts({
            chart: {
                type: 'spline'
            },
            title: {
                text: 'График средней успеваемости учеников по дням'
            },
            xAxis: {
                type: 'datetime',
            },
            yAxis: {
                title: {
                    text: 'Оценка'
                },
                min: 2,
                max: 5
            },
            tooltip: {
                formatter: function() {
                        return '<b>'+ this.series.name +'</b><br/>'+
                        Highcharts.dateFormat('%e. %b', this.x) +': '+'mark='+ this.y;
                }
            },

            series: [{
                name: 'chemistry',
                data: [{$chartData['chemistry']}]
            }, {
                name: 'english',
                data: [{$chartData['english']}]
            }, {
                name: 'biology',
                data: [{$chartData['biology']}]
            }, {
                name: 'math',
                data: [{$chartData['math']}]
            }, {
                name: 'literature',
                data: [{$chartData['literature']}]
            }]
        });
    });
", CClientScript::POS_HEAD);
?>
<div id="chart_container" style="width: 900px; height: 500px; margin-top: 50px;"></div>