<?php

namespace App\Komma\Excel;


use App\Komma\Absences\Absence;
use App\Komma\Excel\Types\ExcelGroup;
use App\Komma\Excel\Types\Exports\DefaultExport;
use App\Komma\Excel\Types\Exports\ExpensesExport;
use App\Komma\Excel\Types\Exports\HoursExport;
use App\Komma\Excel\Types\Row\ExpenseRow;
use App\Komma\Excel\Types\Row\FunctionRow;
use App\Komma\Excel\Types\Row\HourRow;
use App\Komma\Excel\Types\Row\TitleRow;
use App\Komma\Subprojects\Subproject;
use App\Komma\Users\User;
use Carbon\Carbon;
use App\Komma\Hours\Hour;
use App\Komma\Projects\ProjectService;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Facades\Excel;

class ExcelService
{
    /**
     * @var excelExportRepository
     */
    private $excelExportRepository;
    private $projectService;

    public function __construct(ExcelExportRepository $excelExportRepository, ProjectService $projectService)
    {
        $this->excelExportRepository = $excelExportRepository;
        $this->projectService = $projectService;
    }

    /**
     * Associate relations to reduce query amount
     *
     * @param String $projectId
     * @return ExcelController
     */
    public function createExcelData($project, $begin, $end, $user)
    {
        //create empty array's
        $exports = [];
        $hours = [];
        $expenses = [];
        $getNumberOfSubproject = [];
        $numberOfHours = [0];

        //loop trough subprojects
        foreach ($project->Subprojects as $subproject) {
            //associate to project
            $subproject->Project()->associate($project);

            foreach ($subproject->Tasks as $task) {
                $numberOfHours[] = count($task->Hours);
            }

            //put data of subprojects in array
            if (array_sum($numberOfHours) != 0 || count($subproject->Expenses) > 0) $exports[] = $this->createExcelDataSubproject($subproject, $begin, $end, $user);
        }

        //loop trough export subprojects
        foreach ($exports as $export) {
            //put all hours of project in array together
            foreach ($export['Hours'] as $hour) {
                $hours[] = $hour;
            }
            //put all Expenses of project in array together
            foreach ($export['Expenses'] as $expense) {
                $expenses[] = $expense;
            }
            //put all getNumberOfSubproject of project in array together
            foreach ($export['getNumberOfSubproject'] as $value) {
                $getNumberOfSubproject[] = $value;
            }
        }

        //return all created data
        return $export = ['Hours' => $hours, 'Expenses' => $expenses, 'name' => $project->name, 'getNumberOfSubproject' => $getNumberOfSubproject];
    }


    /**
     * Get the hours rows for this subproject between the given dates
     *
     * @param  Subproject  $subProject
     * @param  Carbon  $startDate
     * @param  Carbon  $endDate
     * @return \Illuminate\Support\Collection
     */
    public function getHourRowsForSubProject(Subproject $subProject, Carbon $startDate, Carbon $endDate): Collection
    {
        $subProject->loadMissing('Project', 'Tasks','Tasks.TaskTemplate','Tasks.Hours','Tasks.Hours.User');

        $titleRow = new TitleRow($subProject->name);
        $sumRow = new FunctionRow(__('excel.subtotal'), 'D', ['E' => FunctionRow::SUM, 'F' => FunctionRow::SUM]);

        $hourRows = collect();

        // FIXME: SubProject should have a direct relation to hours...
        foreach ($subProject->Tasks as $task) {
            $hoursBetween = $task->Hours->where('date', '>=', $startDate)->where('date', '<=', $endDate);
            if($hoursBetween->isEmpty()) continue;

            /**
             * Push the hours between the given dates into the collection
             * @var Hour $hour
             */
            foreach ($hoursBetween as $hour) {
                $hourRows->push(new HourRow($subProject, $hour));
            }
        }

        // If the hourRows is filled, then we order the rows by date and append the titleRow
        if($hourRows->isNotEmpty()) {
            $hourRows = $hourRows->sortBy('date')->values();
            $hourRows = collect( array_merge([$titleRow], $hourRows->toArray(), [$sumRow]) );
        }

        return $hourRows;
    }

    /**
     * Get the Expense rows for this subproject between the given dates
     *
     * @param  Subproject  $subProject
     * @param  Carbon  $startDate
     * @param  Carbon  $endDate
     * @return \Illuminate\Support\Collection
     */
    public function getExpenseRowsForSubProject(Subproject $subProject, Carbon $startDate, Carbon $endDate): Collection
    {
        $subProject->loadMissing('Project','Expenses','Expenses.ExpenseType','Expenses.ExpenseType.ExpenseUnit','Expenses.User');

        $titleRow = new TitleRow($subProject->name);

        $expenseRows = collect();

        $expensesBetween = $subProject->Expenses->where('date', '>=', $startDate)->where('date', '<=', $endDate);
        if($expensesBetween->isNotEmpty()) {
            foreach ($expensesBetween as $expense) {
                $expenseRows->push(new ExpenseRow($subProject, $expense));
            }
        }
        if($expenseRows->isNotEmpty()) {
            $expenseRows = $expenseRows->sortBy('date')->values();
            $expenseRows = collect( array_merge([$titleRow], $expenseRows->toArray()) );
        }

        return $expenseRows;
    }

    /**
     * Make Summary ExcelGroup
     * @return ExcelGroup
     */
    public function getSummaryGroup(): ExcelGroup
    {
        $summaryRow = new FunctionRow(__('excel.total'), 'D', ['E' => FunctionRow::SUM, 'F' => FunctionRow::SUM]);
        $summaryRow->setFunctionType(FunctionRow::TYPE_CELLS);

        $summaryGroup = new ExcelGroup(collect([$summaryRow]));
        $summaryGroup->setCodeName('summary');
        return $summaryGroup;
    }

    /**
     * Create the excel with given name out the given hour and expenses collections
     *
     * @param  Collection  $hours
     * @param  Collection  $expenses
     * @param  string  $fileName
     * @return \Symfony\Component\HttpFoundation\BinaryFileResponse
     */
    public function createExcel(Collection $hours, Collection $expenses, string $fileName)
    {
        $hoursExport = new HoursExport($hours);
        $expensesExport = new ExpensesExport($expenses);

        return Excel::download(
            new DefaultExport($hoursExport,$expensesExport),
            $fileName . '.xlsx'
        );
    }

    /**
     * @param Subproject $subproject
     * @param Carbon $startDate
     * @param Carbon $endDate
     */
    public function createExcelDataSubproject(Subproject $subproject, Carbon $startDate, Carbon $endDate)
    {

        //create empty variables
        $counter = 0;
        $getNumberOfSubproject = [];
        $hours = [];
        $expenses = [];

        //create title of subproject
        $hours[] = [
            'Klant' => $subproject->SubprojectTemplate->name,
            'Project' => "",
            'Deelproject' => "",
            'Taak' => "",
            'Uurprijs' => "",
            'Aantal facturabel' => "",
            'Aantal niet facturabel' => "",
            'Omschrijving' => "",
            'Interne opmerking' => "",
            'Datum' => "",
            'Gebruiker' => "",
            'Facturabel' => "",
            'Gefactureerd' => "",
            'bug' => ""
        ];



        //loop trough tasks
        foreach ($subproject->Tasks as $task) {

            //associate to subproject
            $task->Subproject->associate($subproject);

            //loop trough hours
            foreach ($task->Hours as $hour) {
                //associate to task
                $hour->Task()->associate($task);

                $hourDate =  Carbon::parse($hour->date)->addMinute(); // ensure it will be later than startOfDay();

                $beginDate = Carbon::parse($begin)->startOfDay();

                $endDate = Carbon::parse($end)->endOfDay();

                //transform value to integer
                $value = (float)$hour->value;
                //set bool to yes or no
                $billable = "nee";
                if ($hour->billable == 1 and $hour->exceed_subproject == 0) $billable = "ja";
                $bug = $hour->bug == 1 ? "ja" : "nee";
                $billed = $hour->billed_at != null ? "ja" : "nee";

                //filter by begin and end date
                if ($hourDate->between($beginDate, $endDate)) {
                    $hourDate->subMinute();
                    //filter one or more users
                    if (!empty($user)) {
                        //filter by chosen user
                        if ($hour->User->name == $user) {
                            $counter++;
                            //if hour is billable or not
                            if ($hour->billable == 1 and $hour->exceed_subproject == 0) {
                                //store hour in array
                                $hours[] = [
                                    'Klant' => $hour->Task->Subproject->Project->Company->name,
                                    'Project' => $hour->Task->Subproject->Project->name,
                                    'Deelproject' => $hour->Task->Subproject->name,
                                    'Taak' => $hour->Task->TaskTemplate->name,
                                    'Uurprijs' => floatval($hour->Task->Subproject->hourly_rate),
                                    'Aantal facturabel' => floatval($value),
                                    'Aantal niet facturabel' => "",
                                    'Omschrijving' => $hour->description,
                                    'Interne opmerking' => $hour->intern_description,
                                    'Datum' => $hourDate->format('d-m-Y'),
                                    'Gebruiker' => $hour->User->name,
                                    'Facturabel' => $billable,
                                    'Gefactureerd' => $billed,
                                    'bug' => $bug
                                ];
                                //else is not billable
                            } else {
                                //store hour in array
                                $hours[] = [
                                    'Klant' => $hour->Task->Subproject->Project->Company->name,
                                    'Project' => $hour->Task->Subproject->Project->name,
                                    'Deelproject' => $hour->Task->Subproject->name,
                                    'Taak' => $hour->Task->TaskTemplate->name,
                                    'Uurprijs' => floatval($hour->Task->Subproject->hourly_rate),
                                    'Aantal facturabel' => "",
                                    'Aantal niet facturabel' => floatval($value),
                                    'Omschrijving' => $hour->description,
                                    'Interne opmerking' => $hour->intern_description,
                                    'Datum' => $hourDate->format('d-m-Y'),
                                    'Gebruiker' => $hour->User->name,
                                    'Facturabel' => $billable,
                                    'Gefactureerd' => $billed,
                                    'bug' => $bug
                                ];
                            }
                        }
                        //else is more users
                    } else {
                        $counter++;
                        //if is billable
                        if ($hour->billable == 1 and $hour->exceed_subproject == 0) {
                            //store hour in array
                            $hours[] = [
                                'Klant' => $hour->Task->Subproject->Project->Company->name,
                                'Project' => $hour->Task->Subproject->Project->name,
                                'Deelproject' => $hour->Task->Subproject->name,
                                'Taak' => $hour->Task->TaskTemplate->name,
                                'Uurprijs' => floatval($hour->Task->Subproject->hourly_rate),
                                'Aantal facturabel' => floatval($value),
                                'Aantal niet facturabel' => "",
                                'Omschrijving' => $hour->description,
                                'Interne opmerking' => $hour->intern_description,
                                'Datum' => $hourDate->format('d-m-Y'),
                                'Gebruiker' => $hour->User->name,
                                'Facturabel' => $billable,
                                'Gefactureerd' => $billed,
                                'bug' => $bug
                            ];
                            //else is not billable
                        } else {
                            //store hour in array
                            $hours[] = [
                                'Klant' => $hour->Task->Subproject->Project->Company->name,
                                'Project' => $hour->Task->Subproject->Project->name,
                                'Deelproject' => $hour->Task->Subproject->name,
                                'Taak' => $hour->Task->TaskTemplate->name,
                                'Uurprijs' => floatval($hour->Task->Subproject->hourly_rate),
                                'Aantal facturabel' => "",
                                'Aantal niet facturabel' => floatval($value),
                                'Omschrijving' => $hour->description,
                                'Interne opmerking' => $hour->intern_description,
                                'Datum' => $hourDate->format('d-m-Y'),
                                'Gebruiker' => $hour->User->name,
                                'Facturabel' => $billable,
                                'Gefactureerd' => $billed,
                                'bug' => $bug
                            ];
                        }
                    }
                }
            }
        }
        $hours = $this->msort($hours, array('Deelproject', 'Datum'));

        //if there is no hour in subproject
        if ($counter == 0) {
            //remove title from array
            array_pop($hours);
        } else {
            //add two empty rows in array
            $hours[] = ['Klant' => ''];
            $hours[] = ['Klant' => ' '];
        }
        //store total hour rows in array
        $getNumberOfSubproject[] = $counter;
        //make counter 0
        $counter = 0;

        //get expenses
        foreach ($subproject->Expenses as $expense) {
            //associate to subproject
            $expense->Subproject()->associate($subproject);
            //set bool to yes or no
            $billable = $expense->billable == 1 ? "ja" : "nee";
            $bug = $expense->bug == 1 ? "ja" : "nee";
            $billed = $expense->billed_at != null ? "ja" : "nee";
            $value = $expense->value;

            $expenseDate =  Carbon::parse($expense->date)->addMinute(); // ensure it will be later than startOfDay();

            $beginDate = Carbon::parse($begin)->startOfDay();

            $endDate = Carbon::parse($end)->endOfDay();

            if ($expenseDate->between($beginDate, $endDate)) {
                $expenseDate->subMinute();

                //store expense to array
                $expenses[] = [
                    'Klant' => $expense->Subproject->Project->Company->name,
                    'Project' => $expense->Subproject->Project->name,
                    'Deelproject' => $expense->Subproject->name,
                    'Type' => $expense->ExpenseType->name,
                    'Prijs' => floatval($expense->costs),
                    'Aantal' => floatval($value),
                    'Eenheid' => $expense->ExpenseType->ExpenseUnit->name,
                    'Omschrijving' => $expense->description,
                    'Interne opmerking' => $expense->intern_description,
                    'Datum' => $expenseDate->format('d-m-Y'),
                    'Gebruiker' => $expense->User->name,
                    'Facturabel' => $billable,
                    'Gefactureerd' => $billed,
                    'bug' => $bug
                ];
            }
        }

        $expenses = $this->msort($expenses, array('Deelproject', 'Datum'));

        //return all created data
        return $export = ['Hours' => $hours, 'Expenses' => $expenses, 'name' => $subproject->SubprojectTemplate->name, 'getNumberOfSubproject' => $getNumberOfSubproject];
    }


    /**
     * @param $absences
     * @param $begin
     * @param $end
     * @param $user
     * @return array
     */
    public function createExcelDataIntern($absences, $begin, $end, $user)
    {
        $counter = 0;

        //create empty title
        $title = "";

        //create empty array
        $AbsenceHours = [];
        $getNumberOfSubproject = [];

        //loop trough absences
        foreach ($absences as $absence) {

            $title = $absence->AbsenceType->name;
            $value = (float) $absence->value;

            $date = Carbon::createFromFormat(Carbon::DEFAULT_TO_STRING_FORMAT, $absence->date);

            //filter absence by begin and end date
            if ($absence->date >= $begin and $absence->date <= $end) {

                //check if get single user or all users
                if (!empty($user) && $user != "all") {
                    if ($absence->User->name == $user) {

                        //single user filtered by date
                        $AbsenceHours[] = [
                            'Type' => $absence->AbsenceType->name,
                            'Omschrijving' => $absence->description,
                            'Aantal gebruikt' => floatval($value),
                            'Datum' => $date->format('d-m-Y'),
                            'Gebruiker' => $absence->User->name,
                            'Betaald' => $absence->payed == 1 ? "ja" : "nee"
                        ];
                        $counter++;
                    }
                } else {
                    //all users filtered by date
                    $AbsenceHours[] = [
                        'Type' => $absence->AbsenceType->name,
                        'Omschrijving' => $absence->description,
                        'Aantal gebruikt' => floatval($value),
                        'Datum' => $date->format('d-m-Y'),
                        'Gebruiker' => $absence->User->name,
                        'Betaald' => $absence->payed == 1 ? "ja" : "nee"
                    ];
                    $counter++;
                }
            }
        }
        //if there is no hour in subproject
        if ($counter == 0) {
            //remove title of absence from array
            array_pop($AbsenceHours);
        } else {
            //add two empty rows to array
            $AbsenceHours[] = ['Type' => ""];
            $AbsenceHours[] = ['Type' => " "];
        }
        //store number of rows in array
        $getNumberOfSubproject[] = $counter;

        //return all created data
        return $export = ['absences' => $AbsenceHours, 'title' => $title, 'getNumberOfSubproject' => $getNumberOfSubproject];
    }


    /**
     * @param $hours
     * @return array
     */
    public
    function getAllColoredRows($hours, $expenses)
    {
        //create variables
        $getRows = 2;
        $getBugRowsHours = [];
        $getBugRowsExpenses = [];

        //loop trough hour array
        foreach ($hours as $index => $hour) {
            //if column is not empty
            if (!empty($expenses[$index]['Aantal niet facturabel'])) {
                //add row number to array
                $getBugRowsExpenses[] = $getRows;
            }
            //if column is not empty
            if (!empty($hour['Aantal niet facturabel'])) {
                //add row number to array
                $getBugRowsHours[] = $getRows;
            }
            //next row
            $getRows++;
        }

        //return array's
        return compact('getBugRowsHours', 'getBugRowsExpenses');
    }


    /**
     * @param $hours
     * @param $expenses
     * @return array
     */
    public function getAllBoldRows($hours, $column)
    {
        //create variables
        $getRows = 2;
        $getBoldRowsHours = [];
        //loop though hour array
        foreach ($hours as $index => $hour) {
            //if column is not empty
            if (empty($hour[$column])) {
                //add row number to array
                $getBoldRowsHours[] = $getRows;
            }
            //next row
            $getRows++;
        }
        //return array
        return compact('getBoldRowsHours');
    }

    /**
     * @param $hours
     * @return array
     */
    public function getAllSumRows($hours, $column)
    {
        //create variables
        $getRows = 2;
        $getSumRowsHours = [];
        //loop trough hour array
        foreach ($hours as $index => $hour) {
            //if column is not empty
            if (empty($hour[$column])) {
                //add row number to array
                $getSumRowsHours[] = $getRows;
            }
            //next row
            $getRows++;
        }
        //return array
        return compact('getSumRowsHours');
    }


    /**
     * @param $request
     * @return array
     */
    public function downloadExcelProject($request, $begin, $end)
    {
        //load project with relations
        $project = $this->excelExportRepository->FullProject($request->project);

        //get first date
        $firstDate = !empty($this->projectService->getFirstDateProject($project)[0]) ? $this->projectService->getFirstDateProject($project)[0] : $project->created_at->toDateString();
        $begin = !empty($begin) ? $begin : $firstDate;

        //get data
        $export = $this->createExcelData($project, $begin, $end, $request->user);
        $hours = $export['Hours'];
        $expenses = $export['Expenses'];
        $name = $export['name'];
        $getNumberOfSubproject = $export['getNumberOfSubproject'];

        //load special rows
        $specialRows = (object)$this->getSpacialRows($hours, $expenses);

        return compact('name', 'hours', 'expenses', 'getNumberOfSubproject', 'specialRows');
    }


    /**
     * @param $request
     * @param $begin
     * @param $end
     * @return array
     */
    public function downloadExcelSubproject($request, $begin, $end)
    {
        //load project with relations
        $subproject = $this->excelExportRepository->FullSubproject($request->subproject);

        //get first date
        $firstDate = !empty($this->projectService->getFirstDateSubproject($subproject)[0]) ? $this->projectService->getFirstDateSubproject($subproject)[0] : $subproject->created_at->toDateString();
        $begin = !empty($begin) ? $begin : $firstDate;

        //get data
        $export = $this->createExcelDataSubproject($subproject, $begin, $end, $request->user);
        $hours = $export['Hours'];
        $expenses = $export['Expenses'];
        $name = $export['name'];
        $getNumberOfSubproject = $export['getNumberOfSubproject'];

        //load special rows
        $specialRows = (object)$this->getSpacialRows($hours, $expenses);

        return compact('name', 'hours', 'expenses', 'getNumberOfSubproject', 'specialRows');
    }


    /**
     * @param $request
     * @return array
     */
    public function downloadExcelAllProjects($request, $begin, $end)
    {
        $hours = [];
        $expenses = [];
        $getNumberOfSubproject = [];
        //load all projects with relations
        $projects = $this->excelExportRepository->AllProjects();

        //get first date
        $hourDates = Hour::all()->pluck('date');
        $firstDates = $hourDates->all();
        usort($firstDates, function($a, $b){
            return strtotime($a) - strtotime($b);
        });
        $firstDate = Carbon::parse($firstDates[0])->toDateString();
        $begin = !empty($begin) ? $begin : $firstDate;

        //get all data from each project
        foreach ($projects as $project) {
            $exports[] = $this->createExcelData($project, $begin, $end, $request->user);
        }
        //title of file
        $name = "alle projecten";

        //loop trough exported projects
        foreach ($exports as $export) {
            //only show the projects with value
            if (!empty($export['Hours'])) {
                //make title project row
                $hours[] = ['Klant' => $export['name'], 'Project' => "", 'Deelproject' => "", 'Taak' => "", 'Uurprijs' => "", 'Aantal facturabel' => "", 'Aantal niet facturabel' => "", 'Omschrijving' => "", 'Interne opmerking' => "", 'Datum' => "", 'Gebruiker' => "", 'Facturabel' => "", 'bug' => ""
                ];
                //put all hours of project in array
                foreach ($export['Hours'] as $hour) {
                    $hours[] = $hour;
                }

                //make title expense row
                $expenses[] = ['Klant' => $export['name'], 'Project' => "", 'Deelproject' => "", 'Type' => "", 'Prijs' => "", 'Aantal' => "", 'Eenheid' => "", 'Omschrijving' => "", 'Interne opmerking' => "", 'Datum' => "", 'Gebruiker' => "", 'Facturabel' => "", 'bug' => ""
                ];
                //put all expenses of project in array
                foreach ($export['Expenses'] as $expense) {
                    $expenses[] = $expense;
                }
                //make clear row
                $expenses[] = ['Klant' => ''];

                //make the many arrays one array
                foreach ($export['getNumberOfSubproject'] as $item) {
                    $getNumberOfSubproject[] = $item;
                }
            }
        }
        //get all special rows
        $specialRows = (object)$this->getSpacialRows($hours, $expenses);

        return compact('name', 'hours', 'expenses', 'getNumberOfSubproject', 'specialRows');
    }


    /**
     * @param $request
     * @param $begin
     * @param $end
     * @return array
     */
    public function downloadExcelCompany($request, $begin, $end)
    {
        $hours = [];
        $expenses = [];
        //load all projects with relations
        $company = $this->excelExportRepository->FullCompany($request->company);

        //get first date
        $firstDates = Hour::all()->pluck('date')->all();
        usort($firstDates, function($a, $b){
            return strtotime($a) - strtotime($b);
        });
        $firstDate = Carbon::parse($firstDates[0])->toDateString();
        $begin = !empty($begin) ? $begin : $firstDate;

        //get all data from each project
        foreach ($company->Projects as $project) {
            $project->Company()->associate($company);
            $exports[] = $this->createExcelData($project, $begin, $end, $request->user);
        }
        //title of file
        $name = $company->name;

        //loop trough exported projects
        foreach ($exports as $export) {
            //only show the projects with value
            if (!empty($export['Hours'])) {
                //make title project row
                $hours[] = ['Klant' => $export['name'], 'Project' => "", 'Deelproject' => "", 'Taak' => "", 'Uurprijs' => "", 'Aantal facturabel' => "", 'Aantal niet facturabel' => "", 'Omschrijving' => "", 'Interne opmerking' => "", 'Datum' => "", 'Gebruiker' => "", 'Facturabel' => "", 'bug' => ""
                ];
                //put all hours of project in array
                foreach ($export['Hours'] as $hour) {
                    $hours[] = $hour;
                }

                //make title expense row
                $expenses[] = ['Klant' => $export['name'], 'Project' => "", 'Deelproject' => "", 'Type' => "", 'Prijs' => "", 'Aantal' => "", 'Eenheid' => "", 'Omschrijving' => "", 'Interne opmerking' => "", 'Datum' => "", 'Gebruiker' => "", 'Facturabel' => "", 'bug' => ""
                ];
                //put all expenses of project in array
                foreach ($export['Expenses'] as $expense) {
                    $expenses[] = $expense;
                }
                //make clear row
                $expenses[] = ['Klant' => ''];

                //make the many arrays one array
                foreach ($export['getNumberOfSubproject'] as $item) {
                    $getNumberOfSubproject[] = $item;
                }
            }
        }
        //get all special rows
        $specialRows = (object)$this->getSpacialRows($hours, $expenses);

        return compact('name', 'hours', 'expenses', 'getNumberOfSubproject', 'specialRows');
    }


    /**
     * @param $request
     * @param $begin
     * @param $end
     * @return array
     */
    public function downloadExcelAbsence($request, $begin, $end, $user)
    {
        //load project with relations
        $absences = $this->excelExportRepository->FullAbsence($request->absence);

        //excel title
        $name = $absences->first()->AbsenceType->name;

        //get data
        $export = $this->createExcelDataIntern($absences, $begin, $end, $user);

        $hours = $export['absences'];

        return compact('name', 'hours');
    }


    /**
     * @param $begin
     * @param $end
     * @param $user
     * @return array
     */
    public function downloadExcelAllAbsences($begin, $end, $user)
    {
        //create empty array
        $hours = [];
        $exports = [];

        //load project with relations
        $allAbsences = $this->excelExportRepository->AllAbsences();
        $grouped = $allAbsences->groupBy('absence_type_id');

        //excel title
        $name = "All absences";

        //get data
        foreach ($grouped as $absences) {
            $exports[] = $this->createExcelDataIntern($absences, $begin, $end, $user);
        }

        foreach ($exports as $export) {
            //only show the projects with value
            if (!empty($export['absences'])) {
                //make title project row
                $hours[] = ['Type' => $export['title'], 'Omschrijving' => "", 'Aantal gebruikt' => "", 'Datum' => "", 'Gebruiker' => "", 'Betaald' => ""];
                //put all hours of project in array
                foreach ($export['absences'] as $hour) {
                    $hours[] = $hour;
                }

                //make the many arrays one array
                foreach ($export['getNumberOfSubproject'] as $item) {
                    $getNumberOfSubproject[] = $item;
                }
            }
        }

        //get number of each row that must be bold
        $specialRows = $this->getAllBoldRows($hours, "Datum");
        //get number of each row where add subtotal
        $specialRows += $this->getAllSumRows($hours, "Type");
        //get totals
        $specialRows += $this->getTotal($specialRows['getSumRowsHours'], "C");

        //transform array to object
        $specialRows = (object)$specialRows;


        //return
        return compact('name', 'hours', 'specialRows', 'getNumberOfSubproject');
    }


    /**
     * @param $hours
     * @param $expenses
     * @return array
     */
    public function getSpacialRows($hours, $expenses)
    {
        //get number of each row that must be colored
        $getBugRows = $this->getAllColoredRows($hours, $expenses);
        $getBugRowsHours = $getBugRows['getBugRowsHours'];
        $getBugRowsExpenses = $getBugRows['getBugRowsExpenses'];

        //get number of each row that must be bold
        $getBoldRowsHours = $this->getAllBoldRows($hours, "Project")['getBoldRowsHours'];

        //get number of each row where add subtotal
        $getSumRowsHours = $this->getAllSumRows($hours, "Klant")['getSumRowsHours'];

        //get totals
        $getAllSum = $this->getTotal($getSumRowsHours, "F")['getAllSum'];
        $getAllSumNotFac = $this->getTotal($getSumRowsHours, "G")['getAllSum'];

        return compact('getBugRowsHours', 'getBugRowsExpenses', 'getBoldRowsHours', 'getSumRowsHours', 'getAllSum', 'getAllSumNotFac');
    }


    /**
     * @param $getSumRowsHours
     * @param $column
     * @return string
     */
    public function getTotal($getSumRowsHours, $column)
    {
        $getAllSum = "";

        //loop though subtotal rows
        foreach ($getSumRowsHours as $getSum) {
            //make string of all subtotals
            $getAllSum = $getAllSum . ',' . $column . $getSum;
        }
        //remove first comma
        $getAllSum = substr($getAllSum, +1);

        return compact('getAllSum');
    }


    /**
     * @bron: https://blog.jachim.be/2009/09/php-msort-multidimensional-array-sort/comment-page-1/
     * @return array
     */
    public function msort($array, $key, $sort_flags = SORT_REGULAR) {
        if (is_array($array) && count($array) > 0) {

            if (!empty($key)) {
                $mapping = array();

                foreach ($array as $k => $v) {
                    $sort_key = '';

                    if (!is_array($key)) {
                        $sort_key = $v[$key];
                    } else {
                        // will be sorted as string
                        foreach ($key as $key_key) {
                            $sort_key .= $v[$key_key];
                        }
                        $sort_flags = SORT_STRING;
                    }

                    $mapping[$k] = $sort_key;
                }

                asort($mapping, $sort_flags);
                $sorted = array();

                foreach ($mapping as $k => $v) {
                    $sorted[] = $array[$k];
                }

                return $sorted;
            }
        }

        return $array;
    }

    public static function getHorizontalColumnKey(int $columnNumber): string
    {

        if($columnNumber == 0) throw new \OutOfRangeException(self::class.': We have mapped it like A = 1 to use the column loop iteration. Therefor 0 should not be used.');
        if($columnNumber >= 130) throw new \OutOfRangeException(self::class.': We have not made the function further then DZ.');

        $letters = range(chr(65),chr(90));

        for($l = 1; $l < 5; $l++) {
            for($i = 0; $i < 26; $i++) {
                $letters[26 * $l + $i] = $letters[($l - 1)] . $letters[$i];
            }
        }

        return $letters[$columnNumber - 1];
    }


}