How calculate the sum of the values in rows and columns?

Question

I have a table of values ​​which is filled with an array of month names and sums. How to calculate the sum of these values ​​in rows and columns and display the result in the resulting string in the result column?

public function getReport($year=false)
    {           
        if (!empty($_GET['year'])) $year = (int)$_GET['year'];
        else $year = date ('Y');           

        $from_date = $year.'-01-01 00:00:00';
        $to_date = $year.'-12-31 00:00:00';

        // собираем гигантский запрос
        $fields = 'all_months.fio, all_months.active_count AS sum_active_count, all_months.active_sum AS sum_active_sum';
        $joins = "";

        $months = array('jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sep', 'oct', 'nov', 'decem');
        $months_ru = array('Январь', 'Февраль', 'Март', 'Апрель', 'Май', 'Июнь', 'Июль', 'Август', 'Сентябрь', 'Октябрь', 'Ноябрь', 'Декабрь');

        // результирующий запрос
        $data = array();
        // блок итого
        $data['result'] = array();

        // ставим ограничение по месяцу, если год текущий

        if ($year == (int)date('Y')) $max_month = date('m')-1;
        else $max_month = 11;

        foreach ($months as $key => $m)
        {
            if ($key > $max_month) break;

            // заголовки колонок запроса
            $data['months'][] = $m;
            $data['months_ru'][] = $months_ru[$key];

            $k = sprintf('%02.0d', ($key + 1));
            $fields .= ', '."\n".$m.'.active_count as '.$m.'_active_count, '.$m.'.active_sum as '.$m.'_active_sum,
                          '."\n".$m.'.realized_count as '.$m.'_realized_count, '.$m.'.realized_sum as '.$m.'_realized_sum';
            $joins .=  "\n"."LEFT JOIN tbl_report ".$m." ON ".$m.".date > '".$year."-".$k."-01 00:00:00' AND 
                ".$m.".date < '".$year."-".$k."-31 24:59:59' AND 
                ".$m.".fio = all_months.fio ";
        }

        // поулчаем данные запроса
        $query = "SELECT DISTINCT ".$fields."
                  FROM tbl_report all_months
                  ".$joins."
                  WHERE all_months.date > '$from_date' AND all_months.date < '$to_date' GROUP BY fio";
    //echo '<!--'.$query.'-->';

        //$db_data = Report::model()->findAllBySql($query);
        $db_data = Yii::app()->db->createCommand($query)->queryAll();

        foreach ($db_data as $row)
        {
            $data['rows'][] = $row;
        }

        // Собираем отчет по суммам по месяцам
        $fields = ' SUM(all_months.active_count) as all_active_count, SUM(all_months.active_sum) as all_active_sum,
                    SUM(all_months.realized_count) as all_realized_count, SUM(all_months.realized_sum) as all_realized_sum ';


        $joins = '';
        foreach ($months as $key => $m)
        {
            if ($key > $max_month) break;

            $k = sprintf('%02.0d', ($key + 1));
            $fields .= ', '."\n SUM(".$m.'.active_count) as '.$m.'_active_count, SUM('.$m.'.active_sum) as '.$m.'_active_sum,
                          '."\n SUM(".$m.'.realized_count) as '.$m.'_realized_count, SUM('.$m.'.realized_sum) as '.$m.'_realized_sum';
            $joins .=  "\n"."LEFT JOIN tbl_report ".$m." ON ".$m.".date > '".$year."-".$k."-01 00:00:00' AND 
                ".$m.".date < '".$year."-".$k."-31 24:59:59' AND 
                ".$m.".fio = all_months.fio ";
        }

        $query = "SELECT DISTINCT ".$fields."
                  FROM tbl_report all_months
                  ".$joins."
                  WHERE all_months.date > '".$year."-01-01 00:00:00' AND all_months.date < '".$year."-12-31 24:59:59'";
        //echo $query;
        $data['summary'] = Yii::app()->db->createCommand($query)->queryRow();

        // форматируем числа
        if (!empty($data['rows']) && is_array($data['rows']))
        foreach ($data['rows'] as &$row)
            if (!empty($data['months']) && is_array($data['months']))
            foreach ($data['months'] as $month)
            {
                $row[$month.'_active_count'] = (int)$row[$month.'_active_count'];
                $row[$month.'_active_sum'] = FormatHelper::MF($row[$month.'_active_sum']);
                $row[$month.'_realized_count'] = (int) $row[$month.'_realized_count'];
                $row[$month.'_realized_sum'] = FormatHelper::MF($row[$month.'_realized_sum']);
            }

        // форматируем результирующие строки
        if (!empty($data['months']) && is_array($data['months']))
        foreach ($data['months'] as $month)
            {
                $data['summary'][$month.'_active_count'] = (int)$data['summary'][$month.'_active_count'];
                $data['summary'][$month.'_active_sum'] = FormatHelper::MF($data['summary'][$month.'_active_sum']);
                $data['summary'][$month.'_realized_count'] = (int) $data['summary'][$month.'_realized_count'];
                $data['summary'][$month.'_realized_sum'] = FormatHelper::MF($data['summary'][$month.'_realized_sum']);
            }

        // разворачиваем порядок месяцев в обратном направлении
        //$data['months'] = array_reverse($data['months']);
        //$data['months_ru'] = array_reverse($data['months_ru']);
        //print_r( $months_ru );
        return $data;
    }

Answer 1

Follow this link :: Here

this chunk of code goes to your view file

'columns'=>array(
                ..........
                array(
                        'name'=>'age',
                        'footer'=>'Total: ' . $model->getTotal($model->search()->getData(), 'age'),
                ),
                array(
                        'name'=>'weight',
                        'footer'=>'Total: ' . $model->getTotal($model->search()->getData(), 'weight'),
                ),
                ..........
        ),

and this one goes to your model file

 public function getTotal($records, $column)
        {
                $total = 0;
                foreach ($records as $record) {
                        $total += $record->$column;
                }
                return $total;
        }