status-monthly-yearly.php

<?php
public function statusPerYear(string $year) : array
    {
        $query = "
			select DATE_FORMAT(end_date, '%b-%y') month,
			sum(CASE WHEN status = 4 THEN 1 ELSE 0 END)  ongoing,
			sum(CASE WHEN status = 8 THEN 1 ELSE 0 END)  success,
			sum(CASE WHEN status = 6 THEN 1 ELSE 0 END)  failed,
			sum(CASE WHEN status = 10 THEN 1 ELSE 0 END)  cancel
			from
			collaborations
			where
			collaborations.status in (4,6,8,10)
			and (
				DATE_FORMAT(start_date, '%Y') = '".$year."'
					or
				DATE_FORMAT(end_date, '%Y') = '".$year."'
			)
			GROUP by DATE_FORMAT(end_date, '%b %y')
			order by end_date
		";

        $months = [];
        for ($m=1; $m<=12; ++$m) {
            $months[date('M', mktime(0, 0, 0, $m, 1)).'-'.substr($year, -2)] = [
                'ongoing' => 0,
                'success' => 0,
                'failed' => 0,
                'cancel' => 0
            ];
        }

        $results = DB::select(DB::raw($query));
        $data = [];
        foreach ($results as $key => $row) {
            $data[$row->month] = [
                'ongoing' => (int)$row->ongoing,
                'success' => (int)$row->success,
                'failed' => (int)$row->failed,
                'cancel' => (int)$row->cancel
            ];
        }
        return array_replace($months, $data);
    }