report.php

<?php

namespace backend\models\charts;

use common\models\ChecklistLineInstance;
use Yii;
use yii\db\Query;

function queryLines(): Query
{
    $clines = (new Query())
        ->select(['cli.*', 'ci.created_at', "CONCAT(e.apellido, ' ', e.nombre)", 'c.type'])
        ->from('checklist c')
        ->innerJoin('checklist_instance ci', 'c.id = ci.checklist_id')
        ->innerJoin('checklist_line_instance cli', 'cli.checklist_instance_id = ci.id')
        ->innerJoin('user u', 'u.id = ci.created_by')
        ->innerJoin('empleado e', 'e.id = u.empleado_id');

    return $clines;
}

class ReporteMensual
{
    const CLOSED_STATES = [
        ChecklistLineInstance::ESTADO_CERRADA,
        ChecklistLineInstance::ESTADO_BLOQUEADA
    ];

    /**
     * Selects the union between
     * - all actions that aren't closed o blocked before the start of the month
     * - all actions started on this month
     * 
     * @param string $month from 1: January to 12: December
     * @param string $year i.e. '2020'
     * @return array the data selected by the query described above
     */
    public static function getAcciones($year, $month)
    {
        $select = (new Query())
            ->select(['[[type]] as tipo', 'count([[type]]) as cnt'])
            ->from('clines')
            ->where([
                'or',
                [
                    'and',
                    ['<=', 'year([[created_at]])', $year],
                    ['<', 'month([[created_at]])', $month],
                    ['not in', '[[estado]]', self::CLOSED_STATES],
                ],
                [
                    'and',
                    ['=', 'year([[created_at]])', $year],
                    ['=', 'month([[created_at]])', $month],
                ]
            ])
            ->groupBy('[[type]]');

        return $select
            ->withQuery(queryLines(), 'clines')
            ->all();
    }

    /**
     * Selects the union between
     * - all actions that aren't closed o blocked before the start of the month
     * - all actions started on this month
     * 
     * @param int $month from 1: January to 12: December
     * @param int $year
     * @return array the data selected by the query described above
     */
    public static function getCriticidades($year, $month)
    {

        $select = (new Query())
            ->select(['[[criticidad]]', 'count([[criticidad]]) as cnt'])
            ->from('clines')
            ->where([
                'or',
                [
                    'and',
                    ['<=', 'year([[created_at]])', $year],
                    ['<', 'month([[created_at]])', $month],
                    ['not in', '[[estado]]', self::CLOSED_STATES],
                    ['not', ['[[criticidad]]' => null]]
                ],
                [
                    'and',
                    ['=', 'year([[created_at]])', $year],
                    ['=', 'month([[created_at]])', $month],
                    ['not', ['[[criticidad]]' => null]]
                ]
            ])
            ->groupBy('[[criticidad]]');

        return $select
            ->withQuery(queryLines(), 'clines')
            ->all();
    }
}