<?php
namespace console\controllers;


use yii\console\Controller;
use Yii;
use common\models\Customer;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use common\models\dummy\DateModel;
use yii\helpers\Url;

class PdfController extends Controller{


	public function actionView(){

		$model=new DateModel();
        
        //		$model->load(Yii::$app->request->post());

		if($model->timeTo=='') $model->timeTo=date('d.m.Y');
		if($model->timeFrom=='')$model->timeFrom = '01.01.1970';
		$date1=(new \DateTime($model->timeFrom))->modify("-1day")->getTimestamp();
		$date2=(new \DateTime($model->timeTo))->modify("+1day")->getTimestamp();
		$date3=(new \DateTime($model->timeTo))->getTimestamp();

		$data = Customer::find()//			->where([
            //				'between',
            //				'created_at',
            //				$date1,
            //				$date2
            //			])
			->all();
		if(count($data)>0){
			$spreadsheet = new Spreadsheet();
			$sheet = $spreadsheet->getActiveSheet();


			$styleArray = [
				'font' => [
					'bold' => true,
				],
				'alignment' => [
					'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
				],

				'fill' => [
					'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
					'rotation' => 90,
					'startColor' => [
						'argb' => 'FFA0A0A0',
					],
					'endColor' => [
						'argb' => 'FFFFFFFF',
					],
				],
			];

			#$spreadsheet->getActiveSheet()->getStyle('A3')->applyFromArray($styleArray);


			$sheet->setCellValue('A1','ФИО')->getStyle('A1')->applyFromArray($styleArray);
			$sheet->setCellValue('B1','Должность')->getStyle('B1')->applyFromArray($styleArray);
			$sheet->setCellValue('C1','Официальный почтовый адрес')->getStyle('C1')->applyFromArray($styleArray);
			$sheet->setCellValue('D1','Email')->getStyle('D1')->applyFromArray($styleArray);
			$sheet->setCellValue('E1','Телефон (рабочий)')->getStyle('E1')->applyFromArray($styleArray);
			$sheet->setCellValue('F1','Телефон (дополнительный)')->getStyle('F1')->applyFromArray($styleArray);
			$sheet->setCellValue('G1','Факс')->getStyle('G1')->applyFromArray($styleArray);
			$sheet->setCellValue('H1','Организация')->getStyle('H1')->applyFromArray($styleArray);
			$sheet->setCellValue('I1','Пол')->getStyle('I1')->applyFromArray($styleArray);
			$sheet->setCellValue('J1','Дата рождения')->getStyle('J1')->applyFromArray($styleArray);
			$sheet->setCellValue('K1','Серия, номер')->getStyle('K1')->applyFromArray($styleArray);
			$sheet->setCellValue('L1','Дата выдачи')->getStyle('L1')->applyFromArray($styleArray);
			$sheet->setCellValue('M1','Орган выдачи')->getStyle('M1')->applyFromArray($styleArray);
			$sheet->setCellValue('N1','Действителен до')->getStyle('N1')->applyFromArray($styleArray);
			$sheet->setCellValue('O1','Нужна виза')->getStyle('O1')->applyFromArray($styleArray);
			$sheet->setCellValue('P1','Особенное питание')->getStyle('P1')->applyFromArray($styleArray);
			$sheet->setCellValue('Q1','Рабочий язык')->getStyle('Q1')->applyFromArray($styleArray);
			$sheet->setCellValue('R1','Ссылка на фотографию')->getStyle('R1')->applyFromArray($styleArray);
			$sheet->setCellValue('S1','Национальность')->getStyle('R1')->applyFromArray($styleArray);
			$sheet->setCellValue('T1','Страна')->getStyle('R1')->applyFromArray($styleArray);
			$sheet->setCellValue('U1','Рабочий статус делегата')->getStyle('R1')->applyFromArray($styleArray);

			$sheet->getColumnDimension('A')->setAutoSize(true);
			$sheet->getColumnDimension('B')->setAutoSize(true);
			$sheet->getColumnDimension('C')->setAutoSize(true);
			$sheet->getColumnDimension('D')->setAutoSize(true);
			$sheet->getColumnDimension('E')->setAutoSize(true);
			$sheet->getColumnDimension('F')->setAutoSize(true);
			$sheet->getColumnDimension('G')->setAutoSize(true);
			$sheet->getColumnDimension('H')->setAutoSize(true);
			$sheet->getColumnDimension('I')->setAutoSize(true);
			$sheet->getColumnDimension('J')->setAutoSize(true);
			$sheet->getColumnDimension('K')->setAutoSize(true);
			$sheet->getColumnDimension('L')->setAutoSize(true);
			$sheet->getColumnDimension('M')->setAutoSize(true);
			$sheet->getColumnDimension('N')->setAutoSize(true);
			$sheet->getColumnDimension('O')->setAutoSize(true);
			$sheet->getColumnDimension('P')->setAutoSize(true);
			$sheet->getColumnDimension('Q')->setAutoSize(true);
			$sheet->getColumnDimension('R')->setAutoSize(true);
			$sheet->getColumnDimension('S')->setAutoSize(true);
			$sheet->getColumnDimension('T')->setAutoSize(true);
			$sheet->getColumnDimension('U')->setAutoSize(true);
			$fileName=$model->timeFrom."-".$model->timeTo."_".substr(md5(time()),0,6).'.xlsx';
			foreach($data as $key => $d){
				$key+=3;
				$gender='';
				switch ($d->gender){
					case Customer::MALE:
						$gender = "Мужчина";
						break;
					case Customer::FEMALE:
						$gender = 'Женщина';
						break;
					default:
						$gender = 'Пол не задан';
						break;

				}
				$needVisa=($d->need_visa==0)?'Нет':'Да';

				$workLang='';
				switch ($d->working_lang){
					case 2:
						$workLang = "Русский";
						break;
					case 1:
						$workLang =  "Английский";
						break;
					case 4:
						$workLang = "Французcкий";
						break;
					default:
						$workLang =  "Русский";
						break;
				}

				$workStatus=false;
				switch ($d->user_work_status){
					case 0:
						$workStatus=$d::WORK_STATUS_0;
						break;
					case 1:
						$workStatus=$d::WORK_STATUS_1;
						break;
					case 2:
						$workStatus=$d::WORK_STATUS_2;
						break;
					case 3:
						$workStatus=$d::WORK_STATUS_3;
						break;
					case 4:
						$workStatus=$d::WORK_STATUS_4;
						break;
				}


				$sheet->setCellValue('A'.$key, $d->name);
				$sheet->setCellValue('B'.$key, $d->organization);
				$sheet->setCellValue('C'.$key, $d->post_address);
				$sheet->setCellValue('D'.$key, $d->email);
				$sheet->setCellValue('E'.$key, $d->phone_office);
				$sheet->setCellValue('F'.$key, $d->phone_other);
				$sheet->setCellValue('G'.$key, $d->fax);
				$sheet->setCellValue('H'.$key, $d->citizenship);
				$sheet->setCellValue('I'.$key, $gender);
				$sheet->setCellValue('J'.$key, $d->birth);
				$sheet->setCellValue('K'.$key, $d->passport_number);
				$sheet->setCellValue('L'.$key, $d->passport_date);
				$sheet->setCellValue('M'.$key, $d->passport_place);
				$sheet->setCellValue('N'.$key, $d->passport_valid);
				$sheet->setCellValue('O'.$key, $needVisa);
				$sheet->setCellValue('P'.$key, $d->special_meal);
				$sheet->setCellValue('Q'.$key, $workLang);
                $sheet->setCellValue('R' . $key, 'http://energyforum.org.ua' . $d->image);
				$sheet->setCellValue('S'.$key,$d->nationality);
				$sheet->setCellValue('T'.$key,$d->country_from);
				$sheet->setCellValue('U'.$key,\Yii::t('app',$workStatus,[],'ru'));
				$writer = new Xlsx($spreadsheet);

				$writer->save(__DIR__.'/../../frontend/web/files/xls/'.$fileName);
			}
			return json_encode([
				'status'        => 'success',
				'file'          => $fileName,
			]);
		}

		return json_encode([
			'status'        => false,
			'reason'        => 'Нет заявок за указанный временной период',
			'code'          => 1
		]);


	}
}