<?php

namespace app\controllers;

use Yii;
use app\models\TrainingRequests;
use app\models\TrainingRequestsSearch;
use app\models\Branches;
use app\models\VUniversitiesAndFacultiesSearch;
use yii\filters\AccessControl;

class ReportsController extends \yii\web\Controller
{
    public function behaviors()
    {
        return [
            'access' => [
                'class' => AccessControl::class,
                'rules' => [
                    [
                        'allow' => false,
                        'roles' => ['?']
                    ],
                    [
                        'allow' => true,
                        'roles' => ['Admin']
                    ],
                ],
                'denyCallback' => function(){
                    if(Yii::$app->user->isGuest){
                        return Yii::$app->response->redirect(['/users/login']);
                    }
                    else{
                        return Yii::$app->response->redirect(['/dashboard/index']);
                    }
                }
            ],
        ];
    }

    public function actionYearlyReport($selected_year = null){
        $selected_year  = $selected_year == null ? $this->getYearlyReportMaxYear() : $selected_year;
        $result         = $this->getYearlyReportData($selected_year);
        $branches_names = $this->getBranchesFullNames();
        $exist_years    = $this->getYearlyReportExistYears();

        $branch_months = [];
        for($i = 1; $i <= 12; $i++){
            $branch_months[] = 0;
        }

        $data = [];
        foreach($branches_names as $branch_name){
            $data[$branch_name] = ['name' => $branch_name, 'data' => $branch_months];
        }

        foreach($result as $element){
            if($element['month'] != null){
                $data[$element['branch_name']]['data'][$element['month'] - 1] = $element['count'];
            }
        }

        return $this->render('yearly-report', [
            'data' => $data,
            'exist_years' => $exist_years,
            'selected_year' => $selected_year
        ]);
    }

    public function getBranchesFullNames(){
        $branches = Branches::find()->all();
        $names = [];
        foreach($branches as $branch){
            $names[] = $branch->branch . ' ' . $branch->address;
        }
        return $names;
    }

    public function getYearlyReportExistYears(){
        $command = Yii::$app->db->createCommand(
            "SELECT DISTINCT(YEAR(start_date)) AS year FROM trainings"
        );
        $all_years = $command->queryAll();
        $years = [];
        foreach($all_years as $year){
            $years[$year['year']] = $year['year']; 
        }
        return $years;
    }

    public function getYearlyReportMaxYear(){
        $max_year = Yii::$app->db->createCommand(
            "SELECT MAX(YEAR(start_date)) AS max_year FROM trainings"
        );
        return $max_year->queryOne()['max_year'];
    }

    public function getYearlyReportData($selected_year){
        $command = Yii::$app->db->createCommand(
            "SELECT COUNT(T.id) AS count, CONCAT(B.branch, ' ', B.address) AS branch_name, MONTH(T.start_date) AS month
                FROM (
                    SELECT * FROM trainings WHERE YEAR(start_date) = :year
                ) AS T 
                RIGHT JOIN branches AS B
                ON T.branch_id = B.id
                GROUP BY MONTH(T.start_date), branch_name"
        );

        $command->bindValue(':year', $selected_year);

        return $command->queryAll();
    }

    public function actionExportYearlyReport(){
        $data = Yii::$app->request->post();
        $year = $data['year'];
        $months = $data['months'];
        $data = $data['data'];
        $name = Yii::t('app', 'Yearly Report');
        array_unshift($months, Yii::t('app', 'Branch'));
        $exported_data = [];
        foreach($data as $element){
            array_unshift($element['data'], $element['name']);
            $exported_data[] = $element['data'];
        }
        
        $file = Yii::createObject([
            'class' => 'codemix\excelexport\ExcelFile',
            'sheets' => [
                $name => [
                    'data' => $exported_data,
                    'titles' => $months
                ]
            ]
        ]);
        
        $filename = $name . '-' . $year . '.xls';
        $file->saveAs($filename);
        return $filename;
    }

    public function actionDownloadFile($link){
        $path = Yii::getAlias('@webroot') . '/' . $link;
        $file = $path;
        
        if (file_exists($file)){
            Yii::$app->response->sendFile($file)->on(\yii\web\Response::EVENT_AFTER_SEND, function($event) {
                unlink($event->data);
            }, $file);
        }
    }

    public function actionAdvancedSearch($from_date = null, $to_date = null){
        $model = new TrainingRequests();

        if(Yii::$app->request->isPost){
            $data = Yii::$app->request->post();
            if(isset($data['TrainingRequests'])){
                $from_date = $data['TrainingRequests']['from_date'];
                $to_date = $data['TrainingRequests']['to_date'];

                return $this->redirect(['trainees-report', 'from_date' => $from_date, 'to_date' => $to_date]);
            }
        }

        $model->from_date = $from_date;
        $model->to_date   = $to_date;
        
        return $this->renderAjax('advanced-search', [
            'model' => $model
        ]);
    }

    public function actionTraineesReport($from_date = null, $to_date = null){
        $model = new TrainingRequestsSearch();
        $searchModel = new TrainingRequestsSearch();
        $dataProvider = $searchModel->search(Yii::$app->request->queryParams);

        if($from_date != null || $to_date != null){
            if($from_date != null && $to_date != null){
                if(strtotime($from_date) > strtotime($to_date)){
                    Yii::$app->session->setFlash('wrong_selected_dates', Yii::t('app', 'Wrong Selected Dates'));
                }
                else{
                    $dataProvider->query->andWhere(['between', 'create_date', $from_date, $to_date]);
                }
            }
            elseif($from_date != null && $to_date == null){
                $dataProvider->query->andWhere(['>=', 'create_date', $from_date]);
            }
            elseif($from_date == null && $to_date != null){
                $dataProvider->query->andWhere(['<=', 'create_date', $to_date]);
            }
            $model->from_date = $from_date;
            $model->to_date = $to_date;
        }

        return $this->render('trainees-report', [
            'model' => $model,
            'searchModel' => $searchModel,
            'dataProvider' => $dataProvider,
            'from_date' => $from_date,
            'to_date' => $to_date
        ]);
    }

    public function actionUniversitiesAndFacultiesReport(){
        $searchModel = new VUniversitiesAndFacultiesSearch();
        $dataProvider = $searchModel->search(Yii::$app->request->queryParams);

        $universities_names = TrainingRequests::find()->select(['university_name'])->distinct()->all();
        $faculties_names = TrainingRequests::find()->select(['faculty'])->distinct()->all();

        return $this->render('universities-and-faculties-report', [
            'searchModel' => $searchModel,
            'dataProvider' => $dataProvider,
            'universities_names' => $universities_names,
            'faculties_names' => $faculties_names
        ]);
    }

    public function actionAdvancedSearchForTraineesInBranches($from_date = null, $to_date = null){
        $model = new TrainingRequests();

        if(Yii::$app->request->isPost){
            $data = Yii::$app->request->post();
            if(isset($data['TrainingRequests'])){
                $from_date = $data['TrainingRequests']['from_date'];
                $to_date = $data['TrainingRequests']['to_date'];

                return $this->redirect(['trainees-in-branches-within-dates-report', 'from_date' => $from_date, 'to_date' => $to_date]);
            }
        }

        $model->from_date = $from_date;
        $model->to_date   = $to_date;
        
        return $this->renderAjax('advanced-search', [
            'model' => $model
        ]);
    }

    public function getTraineesInBranchesWithinDatesReportData($from_date, $to_date){
        $command = Yii::$app->db->createCommand(
            "SELECT COUNT(T.id) AS count, B.id, B.branch AS branch_name, T.start_date, T.end_date
                FROM (
                    SELECT * FROM trainings WHERE start_date BETWEEN :from_date AND :to_date OR end_date BETWEEN :from_date AND :to_date
                ) AS T 
                RIGHT JOIN branches AS B
                ON T.branch_id = B.id
                GROUP BY B.id"
        );

        $command->bindValues([
            ':from_date' => $from_date,
            ':to_date' => $to_date,
        ]);

        return $command->queryAll();
    }

    public function actionTraineesInBranchesWithinDatesReport($from_date = null, $to_date = null){
        if($from_date != null && $to_date != null){
            if(strtotime($from_date) > strtotime($to_date)){
                Yii::$app->session->setFlash('wrong_selected_dates', Yii::t('app', 'Wrong Selected Dates'));
            }
        }
        elseif($from_date != null && $to_date == null){
            $to_date = date('Y-m-d');
        }
        elseif($from_date == null && $to_date != null){
            $from_date = '1970-01-01';
        }
        else{
            $from_date = '1970-01-01';
            $to_date = date('Y-m-d');
        }

        $query_data = $this->getTraineesInBranchesWithinDatesReportData($from_date, $to_date);
        $data = [];
        $branches = [];
        foreach($query_data as $element){
            $branches[] = $element['branch_name'];
            $data[] = $element['count'];
        }

        return $this->render('trainees-in-branches-within-dates-report', [
            'data' => $data,
            'branches' => $branches
        ]);
    }

    public function actionExportTraineesInBranchesWithinDatesReport(){
        $data = Yii::$app->request->post();
        $branches = $data['branches'];
        $data = $data['data'];
        $name = Yii::t('app', 'Trainees In Branches');

        $file = Yii::createObject([
            'class' => 'codemix\excelexport\ExcelFile',
            'sheets' => [
                $name => [
                    'data' => [$data],
                    'titles' => $branches
                ]
            ]
        ]);
        
        $filename = $name . '.xls';
        $file->saveAs($filename);
        return $filename;
    }
}
