Create calendar grid/view from query

[Marty]

The query (below) will show date as a calender view with years as a row and months as column.

Result:

YEAR	JAN	FEB	MAA	APR	MEI	JUN	JUL	AUG	SEP	OKT	NOV	DEC
2012	5	1	3	6	4	4	2	5	7	2	4	7
2013	9	3	2	4	3	4	7	1	3	8	3	7
2014	8	5	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)	(null)

CREATE TABLE fundperformance

( `id` int auto_increment primary key,

      `fund_id` int,

      `date` date, 

      `performance` decimal(8,4))

SELECT YEAR(p.`date`) AS Year,

  ROUND(MAX(case when month(p.`date`)= 1 then p.`performance` end),2) AS Jan,

  ROUND(MAX(case when month(p.`date`)= 2 then p.`performance` end),2) AS Feb,

  ROUND(MAX(case when month(p.`date`)= 3 then p.`performance` end),2) AS Mar,

  ROUND(MAX(case when month(p.`date`)= 4 then p.`performance` end),2) AS Apr,

  ROUND(MAX(case when month(p.`date`)= 5 then p.`performance` end),2) AS May,

  ROUND(MAX(case when month(p.`date`)= 6 then p.`performance` end),2) AS Jun,

  ROUND(MAX(case when month(p.`date`)= 7 then p.`performance` end),2) AS Jul,

  ROUND(MAX(case when month(p.`date`)= 8 then p.`performance` end),2) AS Aug,

  ROUND(MAX(case when month(p.`date`)= 9 then p.`performance` end),2) AS Sep,

  ROUND(MAX(case when month(p.`date`)=10 then p.`performance` end),2) AS Oct,

  ROUND(MAX(case when month(p.`date`)=11 then p.`performance` end),2) AS Nov,

  ROUND(MAX(case when month(p.`date`)=12 then p.`performance` end),2) AS 'Dec'

FROM `fundperformance` p

WHERE p.`fund_id` = 1

GROUP BY 1

I created a sqlfiddle as example: http://sqlfiddle.com/#!2/6e989/11

To present the data like this would be much better (imo) then a the default grid/crud.

How to create this query in a model or dsql?

And would it be possible to show this in a grid? or is it better to create a  view or use a template?

Any suggestions on how to this (best) in ATK4 are more then welcome.

[Marty]

Maybe it's better to ask how can we do a PIVOT table with ATK..

[Marty]

What I've come up so far may not be the best but is working:

class Model_Calendarreturn extends Model_Table {

public $table="fundperformance";

function init(){

parent::init();

$this->addField('fund_id');

//$this->addField('date');

$this->addExpression('YEAR')->set('YEAR(`date`)');

$this->addExpression('JAN')->set('ROUND(MAX(case when month(`date`)= 1 then `performance` end),2)');

$this->addExpression('FEB')->set('ROUND(MAX(case when month(`date`)= 2 then `performance` end),2)');

$this->addExpression('MAR')->set('ROUND(MAX(case when month(`date`)= 3 then `performance` end),2)');

$this->addExpression('APR')->set('ROUND(MAX(case when month(`date`)= 4 then `performance` end),2)');

$this->addExpression('MAY')->set('ROUND(MAX(case when month(`date`)= 5 then `performance` end),2)');

$this->addExpression('JUN')->set('ROUND(MAX(case when month(`date`)= 6 then `performance` end),2)');

$this->addExpression('JUL')->set('ROUND(MAX(case when month(`date`)= 7 then `performance` end),2)');

$this->addExpression('AUG')->set('ROUND(MAX(case when month(`date`)= 8 then `performance` end),2)');

$this->addExpression('SEP')->set('ROUND(MAX(case when month(`date`)= 9 then `performance` end),2)');

$this->addExpression('OCT')->set('ROUND(MAX(case when month(`date`)= 10 then `performance` end),2)');

$this->addExpression('NOV')->set('ROUND(MAX(case when month(`date`)= 11 then `performance` end),2)');

$this->addExpression('DEC')->set('ROUND(MAX(case when month(`date`)= 12 then `performance` end),2)');

$this->addCondition('fund_id','=',14);

$this->dsql->group($this->dsql->expr('YEAR(date)'));

}

} 

class page_calendarreturn extends Page {

function init(){

parent::init();

$m = $this->add('Model_Calendarreturn')->debug();

                $this->add('Grid')->setModel($m);

}

}

[Romans Malinovskis]

I recently needed something similar, but i needed to pull number of users who reached certain level of the game. 

<user>

<level>

<user_level>  - many-to-many table.

Populate expressions:

        $m->addExpression('l1')->set([$this,'levelExpr'])->display('offs');

        $m->addExpression('l2')->set([$this,'levelExpr'])->display('offs');

        $m->addExpression('l3')->set([$this,'levelExpr'])->display('offs');

        $m->addExpression('l4')->set([$this,'levelExpr'])->display('offs');

        $m->addExpression('l5')->set([$this,'levelExpr'])->display('offs');

        $m->addExpression('l6')->set([$this,'levelExpr'])->display('offs');

        $m->addExpression('l7')->set([$this,'levelExpr'])->display('offs');

        $m->addExpression('l8')->set([$this,'levelExpr'])->display('offs');

Here is the method:

    function levelExpr($m,$b,$c){

        $level = $c->short_name;

        // refSQL re-uses same model, so we need to clone.

        $mm= clone $m->refSQL('UserLevel')

            ->setLimit(1)

            ;

        // get select.

        $mm->join('level')->addField('level_order','order');

        $mm->addCondition('level_order',$level[1]);

        return $mm    ->fieldQuery('updated_ts');

    }

Here I used the field name ($c->short_name) to figure out which field was using the callback.