<?php
include_once(dirname(__FILE__) . '/../Controllers/UtilesController.php');
require_once(dirname(__FILE__) . '/../../vendor/autoload.php');
require_once(dirname(__FILE__) . '/../customConfig/Constants.php');
set_time_limit(CONSTANTS::SET_TIME_LIMIT);

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;

class GenerateExcel {
	private $utilesController;

	function __construct() {
		$this->utilesController = new UtilesController();
	}

	public function dataExcel($resultPOST, $questionIdOutArray) {
		if($resultPOST->status == 'OK') {
			// Preparar array con los id de las preguntas
			$questions = [];
			foreach($resultPOST->questions_excel as $result) {
				if(in_array($result->id, $questionIdOutArray)) {
					continue;
				}

				// Para que se salte por ahora las preguntas tipo '8', 'G' y ';'
				if($result->type == '8' || $result->type == ';' || $result->type == 'G' || $result->type_parent == ';') {
					continue;
				}

				$questions[$result->id]['question'] = $result->question;
				$questions[$result->id]['type'] = $result->type;
				$questions[$result->id]['type_parent'] = $result->type_parent;
				if($result->other == 'Y') {
					$others = $result->sid . 'X' . $result->gid . 'X' . $result->qid . 'other';
					$questions[$others]['question'] = 'Others';
					$questions[$others]['type'] = 'T';
					$questions[$result->id]['type_parent'] = $result->type_parent;
				}
			}

			// Preparar array con los id de las answrers
			$answers = [];
			foreach($resultPOST->answers as $result) {
				$answers[$result->id][$result->answer_code] = $result->answer;
			}
		}

		// Preparar las arrays con los datos
		$result = [];
		$result['questions'] = $questions;
		$result['answers'] = $answers;

		return $result;
	}

	public function printExcel($data, $user, $name, $timestamp, $nperf, $excelDataOpt, $headersExcel, $questionIdOut, $consumo, $permanent, $project, $stampMision, $isContinuing) {
		// Array de preguntas a excluir
		$questionIdOutArray = explode(',', $questionIdOut);

		// Obtenemos informacion de las preguntas
		$codes = $this->dataExcel($data, $questionIdOutArray);
		$idSurvey = $data->questions[0]->sid;

		$nameClean = trim(str_replace(' ', '_', $name));

		//Reemplazamos caracteres especiales latinos
		$find = ['á','é','í','ó','ú','â','ê','î','ô','û','ã','õ','ç','ñ'];
		$repl = ['a','e','i','o','u','a','e','i','o','u','a','o','c','n'];
		$nameClean = str_replace($find, $repl, $nameClean);

		//Eliminamos y Reemplazamos caracteres especiales
		$find = ['/[^A-Za-z0-9\_]/'];
		$nameClean = preg_replace($find, '', $nameClean);

		// Excel
		$spreadsheet = new Spreadsheet();
		$sheet = $spreadsheet->getActiveSheet();
		$sheet->setCellValue('A1', 'Hello World !');

		$encabezados = explode(',', $headersExcel);

		// Numero pregunta
		$i = 0;
		$last_id = '';
		$firstR = true;
		foreach($data->questions_excel as $questions) {
			if(in_array($questions->id, $questionIdOutArray)) {
				continue;
			}

			// Para que se salte por ahora las preguntas tipo '8', 'G' y ';'
			if($questions->type == '8' || $questions->type == ';' || $questions->type == 'G' || $questions->type_parent == ';') {
				continue;
			}

			if($questions->parent_qid == 0 && $firstR) {
				$i++;
			}

			if($questions->type == 'R') {
				$firstR = false;
				$row = $i . ' - ' . trim($questions->question);
				array_push($encabezados, $row);
			} else {
				if(!$firstR) {
					$i++;
				}
				$firstR = true;
				$row = $i . ' - ' . trim($questions->question);
				array_push($encabezados, $row);
			}

			if($questions->other == 'Y') {
				$row = $i . ' - ' . 'Others';
				array_push($encabezados, $row);
			}
		}

		// Completar registros usuarios
		$arrayData = [];
		array_push($arrayData, $encabezados);
		foreach($data->result as $user) {
			foreach($user as $result) {
				if($isContinuing == '1') {
                    if (!is_object($result)) {
                        $result = json_decode(json_encode($result),false);
                    }
                    $data = $this->fillExcelRows($result, $nperf, $consumo, $codes, $excelDataOpt, $idSurvey, $isContinuing);
                    array_push($arrayData, $data);
                }
				else {
                    if (is_array($result) && count($result) === 1) {
                        $result = $result[0];
                    }
                    if (!is_object($result)) {
                        $result = json_decode(json_encode($result),false);
                    }
					$data = $this->fillExcelRows($result, $nperf, $consumo, $codes, $excelDataOpt, $idSurvey, $isContinuing);
					array_push($arrayData, $data);
				}
			}
		}

		$spreadsheet->getActiveSheet()
		->fromArray(
			$arrayData, // The data to set
			NULL,		// Array values with this value will not be set
			'A1'		// Top left coordinate of the worksheet range where
						// we want to set these values (default is A1)
		);

		// Estilos encabezados
		$letra = $spreadsheet->getActiveSheet()->getHighestColumn();
		$spreadsheet->getActiveSheet()->getStyle('A1:' . $letra . '1')->applyFromArray(
			[
				'fill' => [
					'type' => Fill::FILL_SOLID,
					'color' => [
						'rgb' => 'E5E4E2'
					]
				],
				'font' => [
					'bold' => true
				]
			]
		);

		// Autoajustar celdas
		$column = 'A';
		while($column != $letra) {
			$spreadsheet->getActiveSheet()->getColumnDimension($column)->setAutoSize(true);
			$column++;
		}
		$spreadsheet->getActiveSheet()->getColumnDimension($column)->setAutoSize(true);

		$carpeta = CONSTANTS::PATH_EXPORT_WORD . '/reports/' . $project . '_' . $stampMision . '_' . $idSurvey . '/';

		if(!file_exists($carpeta)) {
			$createFile = $this->utilesController->createDirectory($carpeta);

			if($createFile['status'] == 'KO') {
				return 'The folder has not been created';
			}
		}

		$filename = 'data.xlsx';

		$writer = new Xlsx($spreadsheet);
		$writer->save($carpeta . '/' . $filename);

		return $filename;
	}

	// Creación de las líneas del excel
	public function fillExcelRows($result, $nperf, $consumo, $codes, $excelDataOpt, $idSurvey, $isContinuing) {
		$data = [];
		array_push($data, $result->rotationDescription);
		$userName = $result->userNombre.' '.$result->userApellidos.' '.$result->userApellidos2;
		array_push($data, $userName);
		array_push($data, $result->token);
		array_push($data, $result->submitdate);

		// Separación de submitdate en fecha y hora
		$submitdate = explode(" ", $result->submitdate);
		array_push($data, $submitdate[0]);
		array_push($data, $submitdate[1]);

		array_push($data, $result->perf001);
		array_push($data, $result->regionABI);
		array_push($data, $result->ciudad);
		array_push($data, $result->perf002);
		array_push($data, $result->perf002Range);
		array_push($data, $result->perf003);
		if($nperf == 4) {
			array_push($data, $result->perf004);
			array_push($data, $result->perf004Range);
		}
		array_push($data, $result->segmentations);
		array_push($data, $result->consejero);
		array_push($data, $result->enabled);
		array_push($data, $result->estadoMision);
		if($consumo == 1) {
			array_push($data, $result->consumo);
		}

		foreach($codes['questions'] as $key => $value) {
			if($value['type'] == '!' || $value['type'] == 'L' || $value['type'] == '4' || $value['type'] == 'T' || $value['type'] == ':' || $value['type'] == 'F' || $value['type'] == 'R' || $value['type'] == 'H') {
				if($result->$key == '-oth-') {
					$rowValue = 'Otros';
				} else {
					if(isset($result->$key)) {
						switch($excelDataOpt) {
							case '0':
							case '1':
								// Obtener respuesta
								if($value['type'] == 'T') {
                                    $type_parent = $value['type_parent'] ?? "";
									if($type_parent == 'P') {
										if(isset($result->$key)) {
											$comment = $key . 'comment';
											if(isset($result->$comment)) {
												$rowValue = trim($result->$key . ' - ' . $result->$comment);
											} else {
												$rowValue = trim($result->$key);
											}
										} else {
											$rowValue = '';
										}
									} elseif($type_parent == 'H') {
										$rowValue = trim($codes['answers'][$key][$result->$key]);
									} else {
										$rowValue = trim($result->$key);
									}
								} elseif($value['type'] == 'R') {
									$rowValue = str_replace('A', '', trim($result->$key));
								} elseif($value['type'] == ':') {
									if ($result->$key == '1') $rowValue = 'Y';
									else $rowValue = '';
								} else {
									$rowValue = trim($codes['answers'][$key][$result->$key]);
								}
								break;
							case '2':
								// Obtener códigos de respuesta
								if($value['type_parent'] == 'P') {
									if(isset($result->$key)) {
										$comment = $key . 'comment';
										if(isset($result->$comment)) {
											$rowValue = trim($result->$key . ' - ' . $result->$comment);
										} else {
											$rowValue = trim($result->$key);
										}
									} else {
										$rowValue = '';
									}
								} else {
									$rowValue = trim($result->$key);
								}
								break;
							case '3':
								// Obtener códigos de respuesta sin prefijo
								if($value['type'] == 'T' || $value['type'] == ':' || $value['type'] == 'R') {
									$rowValue = str_replace('A', '', trim($result->$key));
									if($rowValue == 'Y') {
										$rowValue = '1';
									} else if($rowValue == 'N') {
										$rowValue = '0';
									}
								} else {
									$rowValue = intval(preg_replace('/[^0-9]+/', '', trim($result->$key), 10));
								}
								break;
						}
					} else {
						$rowValue = '';
					}
				}

				array_push($data, $rowValue);
			} elseif($value['type'] == '|' || $value['type'] == '7' || $value['type'] == 'J') {
				if(isset($result->$key)) {
					$image = json_decode($result->$key);
                    if (isset($image[0])) {
                        $imgFilename = $image[0]->filename ?? "Unknown";
                        $imgExtension = $image[0]->ext ?? "na";
                        $rowValue = CONSTANTS::SERVERSURVEYS . 'upload/surveys/' . $idSurvey . '/files/' . $imgFilename . '.' . $imgExtension;
                    } else {
                        $rowValue = '';
                    }
				} else {
					$rowValue = '';
				}

				array_push($data, $rowValue);
			} else if($value['type'] == '9') {
				// Siempre 14 caracteres y se añaden 0 a la izquierda si hay menos
				$value = $result->$key;
			
				if(strlen($value) < 14) {
					$zeros = 14 - strlen($value);

					for($z = 0; $z < $zeros; $z++) {
						$value = '0' . $value;
					}
				}

                //TODO: REVIEW THIS CODE
				//$rowValue = "\u{200c}" . $value;
				$rowValue = $value;

				array_push($data, $rowValue);
			} else {
				// Añadido para pregunta tipo 'P'
				if(isset($result->$key)) {
					$comment = $key . 'comment';
					if(isset($result->$comment)) {
						$rowValue = trim($result->$key . ' - ' . $result->$comment);
					} else {
						$rowValue = trim($result->$key);
						// Obtener códigos de respuesta sin prefijo
						if($excelDataOpt == '3' && $rowValue == 'Y') {
							$rowValue = '1';
						} else if($excelDataOpt == '3' && $rowValue == 'N') {
							$rowValue = '0';
						}
					}
				} else {
					$rowValue = '';
				}
				array_push($data, $rowValue);
			}
		}

		return $data;
	}

	public function printExcelComparativo($data, $user, $name, $timestamp, $nperf, $excelDataOpt, $headersExcel, $size, $questionIdOut, $consumo) {
		// Array de preguntas a excluir
		$questionIdOutArray = explode(',', $questionIdOut);

		// Obtenemos informacion de las preguntas
		$codes = $this->dataExcel($data, $questionIdOutArray);
		$nameClean = trim(str_replace(' ', '_', $name));

		// Excel
		$spreadsheet = new Spreadsheet();
		$sheet = $spreadsheet->getActiveSheet();
		$sheet->setCellValue('A1', 'Hello World !');

		$encabezados = explode(',', $headersExcel);

		// Número encuesta - Número pregunta
		$i = 0;
		$x = 1;
		$last_id = '';
		foreach($data->questions as $questions) {
			if(in_array($questions->id, $questionIdOutArray)) {
				continue;
			}

			if($questions->parent_qid == 0 && $x == 1) {
				$i++;
			}
			$row = $x . ' - ' . $i . ' - ' . trim($questions->question);
			$x++;
			if($x > $size) {
				$x = 1;
			}
			array_push($encabezados, $row);
		}

		// Completar registros usuarios
		$arrayData = [];
		array_push($arrayData, $encabezados);
		foreach($data->result as $result) {
			$data = [];
			array_push($data, $result->rotationDescription);
			// Nombre añadido a la primera columna
			$userName = $result->userNombre . ' ' . $result->userApellidos . ' ' . $result->userApellidos2;
			array_push($data, $userName);
			array_push($data, $result->token);
			array_push($data, $result->perf001);
			array_push($data, $result->regionABI);
			array_push($data, $result->ciudad);
			array_push($data, $result->perf002);
			array_push($data, $result->perf002Range);
			array_push($data, $result->perf003);
			if($nperf == 4) {
				array_push($data, $result->perf004);
				array_push($data, $result->perf004Range);
			}
			array_push($data, $result->segmentations);
			array_push($data, $result->consejero);
			array_push($data, $result->enabled);
			if($consumo == 1) {
				array_push($data, $result->consumo);
			}

			foreach($codes['questions'] as $key => $value) {
				if($value['type'] == '!' || $value['type'] == 'L' || $value['type'] == '4' || $value['type'] == 'T' || $value['type'] == ':' || $value['type'] == 'F') {
					if($result->$key == '-oth-') {
						$rowValue = 'Otros';
					} else {
						if(isset($result->$key)) {
							switch($excelDataOpt) {
								case '0':
								case '1':
									// Obtener respuesta
									if($value['type'] == 'T' || $value['type'] == ':') {
										$rowValue = trim($result->$key);
									} else {
										$rowValue = trim($codes['answers'][$key][$result->$key]);
									}
									break;
								case '2':
									// Obtener códigos de respuesta
									$rowValue = trim($result->$key);
									break;
								case '3':
									// Obtener códigos de respuesta sin prefijo
									if($value['type'] == 'T' || $value['type'] == ':') {
										$rowValue = str_replace('A', '', trim($result->$key));
										if($rowValue == 'Y') {
											$rowValue = '1';
										} else if($rowValue == 'N') {
											$rowValue = '0';
										}
									} else {
										$rowValue = intval(preg_replace('/[^0-9]+/', '', trim($result->$key), 10));
									}
									break;
							}
						} else {
							$rowValue = '';
						}
					}

					array_push($data, $rowValue);
				} else {
					// Añadido para pregunta tipo 'P'
					if(isset($result->$key)) {
						$comment = $key . 'comment';
						if(isset($result->$comment)) {
							$rowValue = trim($result->$key . ' - ' . $result->$comment);
						} else {
							$rowValue = trim($result->$key);
							// Obtener códigos de respuesta sin prefijo
							if($excelDataOpt == '3' && $rowValue == 'Y') {
								$rowValue = '1';
							} else if($excelDataOpt == '3' && $rowValue == 'N') {
								$rowValue = '0';
							}
						}
					} else {
						$rowValue = '';
					}
					array_push($data, $rowValue);
				}
			}
			array_push($arrayData, $data);
		}

		$spreadsheet->getActiveSheet()
			->fromArray(
				$arrayData, // The data to set
				NULL,		// Array values with this value will not be set
				'A1'		// Top left coordinate of the worksheet range where
							// we want to set these values (default is A1)
			);
		// Estilos encabezados
		$letra = $spreadsheet->getActiveSheet()->getHighestColumn();
		$spreadsheet->getActiveSheet()->getStyle('A1:' . $letra . '1')->applyFromArray(
			[
				'fill' => [
					'type' => Fill::FILL_SOLID,
					'color' => [
						'rgb' => 'E5E4E2'
					]
				],
				'font' => [
					'bold' => true
				]
			]
		);

		// Autoajustar celdas
		$column = 'A';
		while($column != $letra) {
			$spreadsheet->getActiveSheet()->getColumnDimension($column)->setAutoSize(true);
			$column++;
		}
		$spreadsheet->getActiveSheet()->getColumnDimension($column)->setAutoSize(true);

		$carpeta = CONSTANTS::PATH_EXPORT_WORD . '/' . $user;

		if(!file_exists($carpeta)) {
			$createFile = $this->utilesController->createDirectory($carpeta);

			if($createFile['status'] == 'KO') {
				return 'The folder has not been created';
			}
		}

		$fecha = date_create();

		$filename = date_timestamp_get($fecha) . '_' . $nameClean . '.xlsx';

		$writer = new Xlsx($spreadsheet);
		$writer->save($carpeta . '/' . $filename);

		// Guardar reporte en un zip
		// Carpeta donde guardar los elementos a comprimir
		$folder = CONSTANTS::PATH_EXPORT_WORD . '/' . $user . '/';
		if(!file_exists($folder)) { // Si no existe, se crea
			$createFile = $this->utilesController->createDirectory($folder);

			if($createFile['status'] == 'KO') {
				return 'The folder has not been created';
			}
		}

		// Objeto ZIP
		$zip = new ZipArchive();
		$zip->open($folder . $timestamp . '_Analitic_Acumulativo_Mostreo.zip', ZipArchive::CREATE);
		$zip->addFile($carpeta . '/' . $filename, $nameClean . '.xlsx');

		return $filename;
	}

	public function counterWords($data, $user, $token, $zipName = null, $permanent = false, $savePath = '') {
		// Excel
		$spreadsheet = new Spreadsheet();
		$sheet = $spreadsheet->getActiveSheet();

		$letter = 'A';
		$number = 1;

		foreach($data as $question) {
			$arrayData = [];

			$tit = str_replace('=', ' ', $question['title']);
			$title = [htmlspecialchars($tit), ''];
			$header = ['Palabra', 'Contador'];

			array_push($arrayData, $title);
			array_push($arrayData, $header);
			// Obtenemos data
			$controller = new Controller;

			$url = CONSTANTS::SERVERAPI . 'getAnalyticsTagCloudMostreo';
			$params = array('survey' => $question['survey'], 'pregunta' => $question['pregunta'], 'token' => $token);

			$resultPOST = $controller->sendCurlPOST($url, $params);
			if($resultPOST->status == 'OK') {
				if(count($resultPOST->result) > 0) {
					foreach($resultPOST->result as $result) {
						$word = str_replace('=', ' ', $result->word);

						$values = [htmlspecialchars($word), $result->counter];
						array_push($arrayData, $values);
					}
				}
			}
			$cell = $letter . $number;

			$spreadsheet->getActiveSheet()
				->fromArray(
					$arrayData, // The data to set
					NULL,		// Array values with this value will not be set
					$cell		// Top left coordinate of the worksheet range where
								// we want to set these values (default is A1)
				);

			$letter++;
			$letter++;
		}

		// Estilos encabezados
		$letra = $spreadsheet->getActiveSheet()->getHighestColumn();
		$spreadsheet->getActiveSheet()->getStyle('A1:' . $letra . '1')->applyFromArray(
			[
				'fill' => [
					'type' => Fill::FILL_SOLID,
					'color' => [
						'rgb' => 'E5E4E2'
					]
				],
				'font' => [
					'bold' => true
				]
			]
		);

		// Autoajustar celdas
		$column = 'A';
		while($column != $letra) {
			$spreadsheet->getActiveSheet()->getColumnDimension($column)->setAutoSize(true);
			$column++;
		}
		$spreadsheet->getActiveSheet()->getColumnDimension($column)->setAutoSize(true);

		$carpeta = CONSTANTS::PATH_EXPORT_WORD . '/' . $user;

		if(!file_exists($carpeta)) {
			$createFile = $this->utilesController->createDirectory($carpeta);

			if($createFile['status'] == 'KO') {
				return 'The folder has not been created';
			}
		}
		$fecha = date_create();

		$filename = date_timestamp_get($fecha) . '_Analitics_Mostreo.xlsx';

		$writer = new Xlsx($spreadsheet);
		if($permanent) {
			$writer->save($savePath . '/wordcloud_count.xlsx');
			return 'OK';
		} else {
			$writer->save($carpeta . '/' . $filename);
		}

		if($zipName) {
			// Carpeta donde guardar los elementos a comprimir
			$folder = CONSTANTS::PATH_EXPORT_WORD . '/' . $user . '/';
			if(!file_exists($folder)) { // Si no existe, se crea
				$createFile = $this->utilesController->createDirectory($folder);

				if($createFile['status'] == 'KO') {
					return 'The folder has not been created';
				}
			}
			// Objeto ZIP
			$zip = new ZipArchive();
			$zip->open($folder . $zipName, ZipArchive::CREATE);
			$zip->addFile($carpeta . '/' . $filename, 'Analitics_Mostreo.xlsx');
		}

		return $filename;
	}

	public function licences($data, $rsc) {
		try {
			// Excel
			$spreadsheet = new Spreadsheet();
			$sheet = $spreadsheet->getActiveSheet();

			$letter = 'A';
			$number = 1;

			$cell = $letter . $number;
			// regiones abi
			$arrayData = [];
			$title = $rsc['perfstatus_region_abi']['title'];
			array_push($arrayData, $title);
			$headers = $rsc['perfstatus_region_abi']['headers'];
			array_push($arrayData, $headers);
			foreach($data['perfstatus_region_abi'] as $key => $abi) {
				unset($abi['idTenant']);
				unset($abi['idCPG']);
				array_push($arrayData, $abi);
			}

			$spreadsheet->getActiveSheet()
				->fromArray(
					$arrayData, // The data to set
					NULL,		// Array values with this value will not be set
					$cell		// Top left coordinate of the worksheet range where
								// we want to set these values (default is A1)
				);

			$letter = 'G';
			$number = 1;

			$cell = $letter . $number;
			// regiones
			$arrayData = [];
			$title = $rsc['perfstatus_region']['title'];
			array_push($arrayData, $title);
			$headers = $rsc['perfstatus_region']['headers'];
			array_push($arrayData, $headers);
			foreach($data['perfstatus_region'] as $abi) {
				unset($abi['idTenant']);
				unset($abi['idCPG']);
				array_push($arrayData, $abi);
			}

			$spreadsheet->getActiveSheet()
				->fromArray(
					$arrayData, // The data to set
					NULL,		// Array values with this value will not be set
					$cell		// Top left coordinate of the worksheet range where
								// we want to set these values (default is A1)
				);

			$letter = 'M';
			$number = 1;

			$cell = $letter . $number;
			// edad
			$arrayData = [];
			$title = $rsc['perfstatus_edad']['title'];
			array_push($arrayData, $title);
			$headers = $rsc['perfstatus_edad']['headers'];
			array_push($arrayData, $headers);
			foreach($data['perfstatus_edad'] as $abi) {
				unset($abi['idTenant']);
				unset($abi['idCPG']);
				unset($abi['max']);
				unset($abi['min']);
				array_push($arrayData, $abi);
			}

			$spreadsheet->getActiveSheet()
				->fromArray(
					$arrayData, // The data to set
					NULL,		// Array values with this value will not be set
					$cell		// Top left coordinate of the worksheet range where
								// we want to set these values (default is A1)
				);

			$letter = 'S';
			$number = 1;

			$cell = $letter . $number;
			// género
			$arrayData = [];
			$title = $rsc['perfstatus_genero']['title'];
			array_push($arrayData, $title);
			$headers = $rsc['perfstatus_genero']['headers'];
			array_push($arrayData, $headers);
			foreach($data['perfstatus_genero'] as $abi) {
				unset($abi['idTenant']);
				unset($abi['idCPG']);
				array_push($arrayData, $abi);
			}

			$spreadsheet->getActiveSheet()
				->fromArray(
					$arrayData, // The data to set
					NULL,		// Array values with this value will not be set
					$cell		// Top left coordinate of the worksheet range where
								// we want to set these values (default is A1)
				);

			$letter = 'Y';
			$number = 1;

			$cell = $letter . $number;
			// Integración
			$arrayData = [];
			$title = $rsc['dataintegration']['title'];
			array_push($arrayData, $title);
			$headers = $rsc['dataintegration']['headers'];
			array_push($arrayData, $headers);
			foreach($data['dataintegration'] as $abi) {
				unset($abi['idTenant']);
				unset($abi['idCPG']);
				array_push($arrayData, $abi);
			}

			$spreadsheet->getActiveSheet()
				->fromArray(
					$arrayData, // The data to set
					NULL,		// Array values with this value will not be set
					$cell		// Top left coordinate of the worksheet range where
								// we want to set these values (default is A1)
				);

			// Estilos encabezados
			$letra = $spreadsheet->getActiveSheet()->getHighestColumn();
			$spreadsheet->getActiveSheet()->getStyle('A1:' . $letra . '1')->applyFromArray(
				[
					'fill' => [
						'type' => Fill::FILL_SOLID,
						'color' => [
							'rgb' => 'E5E4E2'
						]
					],
					'font' => [
						'bold' => true
					]
				]
			);
			$spreadsheet->getActiveSheet()->getStyle('A2:' . $letra . '2')->applyFromArray(
				[
					'fill' => [
						'type' => Fill::FILL_SOLID,
						'color' => [
							'rgb' => 'E5E4E2'
						]
					],
					'font' => [
						'bold' => true
					]
				]
			);

			// Autoajustar celdas
			$column = 'A';
			while($column != 'AD') {
				if($column != 'A' && $column != 'G' && $column != 'M' && $column != 'S' && $column != 'Y' && $column != 'F' && $column != 'L' && $column != 'R' && $column != 'X') {
					$spreadsheet->getActiveSheet()->getColumnDimension($column)->setAutoSize(true);
				}
				$column++;
			}
			$spreadsheet->getActiveSheet()->getColumnDimension($column)->setAutoSize(true);

			$carpeta = CONSTANTS::PATH_EXPORT_WORD . '/';

			if(!file_exists($carpeta)) {
				$createFile = $this->utilesController->createDirectory($carpeta);

				if($createFile['status'] == 'KO') {
					return 'The folder has not been created';
				}
			}
			$fecha = date_create();

			$filename = date_timestamp_get($fecha) . '_Distribucion_por_licencias.xlsx';

			$writer = new Xlsx($spreadsheet);
			$writer->save($carpeta . '/' . $filename);

			$response['status'] = 'OK';
			$response['response'] = $filename;
		} catch (Exception $e) {
			$response['status'] = 'KO';
		}
		return $response;
	}

	public function ponderacionesByCountry($data, $rsc) {
		try {
			// Excel
			$spreadsheet = new Spreadsheet();
			$sheet = $spreadsheet->getActiveSheet();

			$letter = 'A';
			$number = 1;

			$cell = $letter . $number;
			// NSE
			$arrayData = [];
			$title = $rsc['nse']['title'];
			array_push($arrayData, $title);
			$headers = $rsc['nse']['headers'];
			array_push($arrayData, $headers);
			foreach($data['nse'] as $key => $abi) {
				array_push($arrayData, $abi);
			}

			$spreadsheet->getActiveSheet()
				->fromArray(
					$arrayData, // The data to set
					NULL,		// Array values with this value will not be set
					$cell		// Top left coordinate of the worksheet range where
								// we want to set these values (default is A1)
				);

			$letter = 'D';
			$number = 1;

			$cell = $letter . $number;
			// Edad
			$arrayData = [];
			$title = $rsc['age']['title'];
			array_push($arrayData, $title);
			$headers = $rsc['age']['headers'];
			array_push($arrayData, $headers);
			foreach($data['age'] as $abi) {
				array_push($arrayData, $abi);
			}

			$spreadsheet->getActiveSheet()
				->fromArray(
					$arrayData, // The data to set
					NULL,		// Array values with this value will not be set
					$cell		// Top left coordinate of the worksheet range where
								// we want to set these values (default is A1)
				);

			$letter = 'G';
			$number = 1;

			$cell = $letter . $number;
			// Región
			$arrayData = [];
			$title = $rsc['region']['title'];
			array_push($arrayData, $title);
			$headers = $rsc['region']['headers'];
			array_push($arrayData, $headers);
			foreach($data['region'] as $abi) {
				array_push($arrayData, $abi);
			}

			$spreadsheet->getActiveSheet()
				->fromArray(
					$arrayData, // The data to set
					NULL,		// Array values with this value will not be set
					$cell		// Top left coordinate of the worksheet range where
								// we want to set these values (default is A1)
				);

			$letter = 'J';
			$number = 1;

			$cell = $letter . $number;
			// Género
			$arrayData = [];
			$title = $rsc['gender']['title'];
			array_push($arrayData, $title);
			$headers = $rsc['gender']['headers'];
			array_push($arrayData, $headers);
			foreach($data['gender'] as $abi) {
				array_push($arrayData, $abi);
			}

			$spreadsheet->getActiveSheet()
				->fromArray(
					$arrayData, // The data to set
					NULL,		// Array values with this value will not be set
					$cell		// Top left coordinate of the worksheet range where
								// we want to set these values (default is A1)
				);

			// Estilos encabezados
			$letra = $spreadsheet->getActiveSheet()->getHighestColumn();
			$spreadsheet->getActiveSheet()->getStyle('A1:' . $letra . '1')->applyFromArray(
				[
					'fill' => [
						'type' => Fill::FILL_SOLID,
						'color' => [
							'rgb' => 'E5E4E2'
						]
					],
					'font' => [
						'bold' => true
					]
				]
			);
			$spreadsheet->getActiveSheet()->getStyle('A2:' . $letra . '2')->applyFromArray(
				[
					'fill' => [
						'type' => Fill::FILL_SOLID,
						'color' => [
							'rgb' => 'E5E4E2'
						]
					],
					'font' => [
						'bold' => true
					]
				]
			);

			// Autoajustar celdas
			$column = 'A';
			while($column != 'AD') {
				if($column != 'A' && $column != 'D' && $column != 'G' && $column != 'J') {
					$spreadsheet->getActiveSheet()->getColumnDimension($column)->setAutoSize(true);
				}
				$column++;
			}
			$spreadsheet->getActiveSheet()->getColumnDimension($column)->setAutoSize(true);

			$carpeta = CONSTANTS::PATH_EXPORT_WORD . '/';

			if(!file_exists($carpeta)) {
				$createFile = $this->utilesController->createDirectory($carpeta);

				if($createFile['status'] == 'KO') {
					return 'The folder has not been created';
				}
			}
			$fecha = date_create();

			$filename = date_timestamp_get($fecha) . '_ponderaciones.xlsx';

			$writer = new Xlsx($spreadsheet);
			$writer->save($carpeta . '/' . $filename);

			$response['status'] = 'OK';
			$response['response'] = $filename;
		} catch (Exception $e) {
			$response['status'] = 'KO';
		}
		return $response;
	}
}