<?php

namespace App\Komma\ExcelExports;


use Carbon\Carbon;
use App\Komma\Hours\Hour;
use App\Komma\Projects\ProjectService;

class ExcelExportService
{
    /**
     * @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];
    }


    /**
     * @param $subproject
     * @param $begin
     * @param $end
     * @param $user
     * @return array
     */
    public function createExcelDataSubproject($subproject, $begin, $end, $user)
    {
        //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;
            //remove 00:00:00 from date
            $date = str_replace(" 00:00:00", "", $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;
    }



}