<?php
class Funciones extends CApplicationComponent{
	public $someconfig='somedefault';
	public function init() {
		// Init this component
	}
	/********************** BASES DE DATOS *******************************/
	//CONECCION A LA BD
	public function connectDB(){
		$servername = "localhost";
		$username = "root";
		$password = "PkJXPQFykKLmS3cWhqmZKNYE";
		$dbname = "PPD";
		/*$servername = "localhost";
		$username = "root";
		$password = "PkJXPQFykKLmS3cWhqmZKNYE";
		$dbname = "ppd";*/
		// Create connection
		$conn = new mysqli($servername, $username, $password, $dbname);
		$conn->set_charset("utf8");
		// Check connection
		if ($conn->connect_error) {
			die("Connection failed: " . $conn->connect_error);
		}
		return $conn;
	}
	//CREAR UNA NUEVA TABLA
	public function createDB($tbl, $clmn, $long){
		$conn = $this->connectDB();
		$sql = 'CREATE TABLE IF NOT EXISTS '.$tbl.'('.$clmn.' VARCHAR('.$long.') NOT NULL)  DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;';
		if ($conn->query($sql) !== TRUE) {
			echo "Error creating database: " . $conn->error;
			//echo "Database created successfully";
		}
		$conn->close();
		return true;
	}
	//INSERTAR DATOS EN LA TABLA
	public function insertDB($tbl, $clmn, $value){
		$conn = $this->connectDB();
		$sql = 'INSERT INTO '.$tbl.'('.$clmn.') values("'.$value.'")';
		if ($conn->query($sql) !== TRUE) {
			echo "Error: " . $sql . "<br>" . $conn->error;
			//echo "New record created successfully";
		}
		$conn->close();
		return true;
	}
	//VERIFICAR SI ES NUMERICO O STRING
	public function checkBD($tbl, $columna){
		$tipo = 0;
		$sql = 'SELECT SUM('.$columna.') AS total FROM '.$tbl;
		$total = Yii::app()->db->createCommand($sql)->queryScalar();
		if($total > 0){
			$tipo = 1;
		}
		return $tipo;
	}
	//VERIFICA SI YA EXISTE LA BD
	public function existsBD($tbl){
		$conn = $this->connectDB();
		$return = 0;
		$sql = 'SHOW TABLES LIKE '.$tbl;
		if ($result = $conn->query($sql)) {
			$return = $result->num_rows;
		}
		$conn->close();
		return $return;
	}
	//ELIMINAR TABLA DE LA BD
	public function deleteBD($tbl){
		$return = false;
		$conn = $this->connectDB();
		$consulta = "DROP TABLE ".$tbl;
		$resultado = $conn -> query($consulta);
		if ($resultado){
			$return = true;
		}
		$conn->close();
		return $return;
	}
	//mostrar tablas de la BD
	public function get_tables(){
		$tableList = array();
		$conn = $this->connectDB();
		$res = mysqli_query($conn,"SHOW TABLES");
		while($cRow = mysqli_fetch_array($res)){
			$tableList[] = $cRow[0];
		}
		return $tableList;
	}
	/******************************** MIS FUNCIONES ****************************************/
	public function cortarString($string, $largo){
		$marca = "<!--corte-->";
		if (strlen($string) > $largo){
			$string = wordwrap($string, $largo, $marca);
			$string = explode($marca, $string);
			$string = $string[0];
			return $string.'...';
		}else{
			return $string;
		}
	}
	public function saltosString($string, $largo){
		$marca = "<br>";
		$string = wordwrap($string, $largo, $marca);
		return $string;
	}
	function invFecha($fecha){
		$arrFecha=explode('-',$fecha);
		return implode('-',array_reverse($arrFecha));
	}
	public function listaCategorias(){
		$model = Categorias::model()->findAll(array("condition"=>"activo = 1 and parent_id IS NULL", "order"=>"nombre ASC"));
		$returnArr = array();
		foreach($model as $item){
			$returnArr[$item->id_categoria] = $item->nombre;
		}
		return $returnArr;
	}
	public function listaSubcategorias($id){
		$model = Categorias::model()->findAll(array("condition"=>"activo = 1 and parent_id = ".$id, "order"=>"nombre ASC"));
		$returnArr = array();
		foreach($model as $item){
			$returnArr[$item->id_categoria] = $item->nombre;
		}
		return $returnArr;
	}
	public function listaBaseCategorias($id){
		$modelCategorias = BasesCategorias::model()->findAll(array('condition'=>'id_base_encuesta = '.$id));
		$returnArr = array();
		foreach($modelCategorias as $item){
			$model = Categorias::model()->findByPk($item->id_categoria);
			$returnArr[$model->id_categoria] = $model->nombre;
		}
		return $returnArr;
	}
	public function listaTiposDocumentos(){
		$model = TiposDocumentos::model()->findAll();
		$returnArr = array();
		foreach($model as $item){
			//$returnArr[$item->id_tipo_documento] = $item->nombre;
			if($item->activo == 1) $returnArr[$item->id_tipo_documento] = $item->nombre;
		}
		return $returnArr;
	}
	public function listaColaboradores(){
		/*
		$model = Colaboladores::model()->findAll(array('condition' => 'activo = 1', 'order' => 'nombre_completo ASC'));
		$returnArr = array();
		foreach($model as $item){
			$returnArr[$item->id_colaborador] = $item->nombre_completo;
		}
		return $returnArr;
		*/
		$model = Colaboladores::model()->findAll(array('condition' => 'activo = 1', 'order' => 'nombre_completo ASC'));
		$returnArr = array();
		foreach($model as $item){
			$returnArr[$item->id_colaborador] = array($item->nombre_completo,$item->puesto);
			// $returnArr[$item->id_colaborador] = $item->nombre_completo;
		}

		return $returnArr;
	}


	public function listaImagenesColaboradores(){
		$model = Colaboladores::model()->findAll(array('condition' => 'activo = 1', 'order' => 'nombre_completo ASC'));
		$returnArr = array();
		foreach($model as $item){
			$returnArr[$item->id_colaborador] = $item->imagen;
		}

		return $returnArr;
	}
	public function connectDBBDD(){
		$servername = "localhost";
		$username = "root";
		$password = "PkJXPQFykKLmS3cWhqmZKNYE";
		$dbname =	 "PPD";
		$conn = new mysqli($servername, $username, $password, $dbname);
		$conn->set_charset("utf8");
		if ($conn->connect_error) {
			die("Connection failed: " . $conn->connect_error);
		}
		return $conn;
		return 2;
	}
	public function readtableDB2($tbl){
		$conn = $this->connectDBBDD();
		$res = mysqli_query($conn,"SELECT * FROM ".$tbl.";");
		$info = array();
		while($cRow = mysqli_fetch_array($res)){
			$info[$cRow[0]] = $cRow[2];
		}
		$conn->close();
		//echo "<pre>";print_r($info);echo "</pre>";
		return $info;
	}
	public function readtableDB3(){
		$conn = $this->connectDBBDD();
		$res = mysqli_query($conn,"SELECT * FROM tbl_colaboladores;");
		$info = array();
		while($cRow = mysqli_fetch_array($res)){
			$info[$cRow[0]] = $cRow[12];
		}
		$conn->close();
		//echo "<pre>";print_r($info);echo "</pre>";
		return $info;
	}
	public function listaTipoArchivos(){
		$model = TiposArchivos::model()->findAll();
		$returnArr = array();
		foreach($model as $item){
			$returnArr[$item->id_tipo_archivo] = $item->nombre;
		}
		return $returnArr;
	}
	public function eliminarHtml($cadena){
		$return = strip_tags($cadena);
		return $return;
	}
	public function cleanText($text){
		//$text = htmlentities($text);
		//$text = preg_replace('/\&(.)[^;]*;/', '\\1', $text);
		$text = $this->cleanString($text);
		$text = str_replace(' ', '_', $text);
		//$text = preg_replace('/[^A-Z0-9]+/i', '_', $text);
		$text = strtolower(trim($text, '_'));
		//$text = preg_replace('@[\s!:;_\?=\\\+\*/%&#]+@', '-', $text);
		return $text;
	}
	public function cleanColumns($text){
		//$text = htmlentities($text);
		//$text = preg_replace('/\&(.)[^;]*;/', '\\1', $text);
		$text = $this->cleanString($text);
		$text = str_replace(' ', '_', $text);
		//$text = preg_replace('/[^A-Z0-9]+/i', '_', $text);
		$text = strtolower(trim($text, '_'));
		$text = str_replace('.', '_', $text);
		//$text = preg_replace('@[\s!:;_\?=\\\+\*/%&#]+@', '-', $text);
		return $text;
	}
	/**
	 * Reemplaza todos los caracteres con acentos y tildes por sus equivalentes sin ellos
	 *
	 * @param $string
	 *	string la cadena a limpiar
	 *
	 * @return $string
	 *	string limpia
	 */
	public function cleanString($string){
		$string = trim($string);
		$string = str_replace(
			array('á', 'à', 'ä', 'â', 'ª', 'Á', 'À', 'Â', 'Ä'),
			array('a', 'a', 'a', 'a', 'a', 'A', 'A', 'A', 'A'),
			$string
		);
		$string = str_replace(
			array('é', 'è', 'ë', 'ê', 'É', 'È', 'Ê', 'Ë'),
			array('e', 'e', 'e', 'e', 'E', 'E', 'E', 'E'),
			$string
		);
		$string = str_replace(
			array('í', 'ì', 'ï', 'î', 'Í', 'Ì', 'Ï', 'Î'),
			array('i', 'i', 'i', 'i', 'I', 'I', 'I', 'I'),
			$string
		);
		$string = str_replace(
			array('ó', 'ò', 'ö', 'ô', 'Ó', 'Ò', 'Ö', 'Ô'),
			array('o', 'o', 'o', 'o', 'O', 'O', 'O', 'O'),
			$string
		);
		$string = str_replace(
			array('ú', 'ù', 'ü', 'û', 'Ú', 'Ù', 'Û', 'Ü'),
			array('u', 'u', 'u', 'u', 'U', 'U', 'U', 'U'),
			$string
		);
		$string = str_replace(
			array('ñ', 'Ñ', 'ç', 'Ç'),
			array('n', 'N', 'c', 'C',),
			$string
		);
		//eliminar cualquier caracter extraño
		$string = str_replace(
			array("\\", "¨", "º", "-", "~",
				"#", "@", "|", "!", "\"",
				"·", "$", "%", "&", "/",
				"(", ")", "?", "'", "¡",
				"¿", "[", "^", "`", "]",
				"+", "}", "{", "¨", "´",
				">", "< ", ";", ",", ":",
			),
			'',
			$string
		);
		return $string;
	} //end function
	public function getEncuestaById($id){
		$conn = $this->connectDBBDD();
		$res = mysqli_query($conn, "SELECT * FROM tbl_bases_encuestas WHERE id_encuesta_base = ".$id.";");
		$row = mysqli_fetch_array($res);
		$conn->close();

		$encuesta_name = $row[1];
		$descargable = $row['descargable'];
		$categoria = $this->getCategoriaFromEncuestas($id);

		return array($categoria[0], $categoria[1], $encuesta_name, $descargable);
	}
	public function getCategoriaFromEncuestas($id){
		$conn = $this->connectDBBDD();
		$res = mysqli_query($conn, "SELECT tbl_categorias.id_categoria, tbl_categorias.nombre FROM tbl_categorias, tbl_bases_categorias, tbl_bases_encuestas WHERE tbl_categorias.id_categoria = tbl_bases_categorias.id_categoria AND tbl_bases_categorias.id_base_encuesta = tbl_bases_encuestas.id_encuesta_base AND tbl_bases_encuestas.id_encuesta_base = ".$id.";");
		$row = mysqli_fetch_array($res);
		$conn->close();

		return array($row['id_categoria'], $row['nombre']);
	}
	public function checkIfTableExist($tbl){
		$conn = $this->connectDBBDD();
		if(mysqli_num_rows(mysqli_query($conn, "SHOW TABLES LIKE '".$tbl."'"))==1)
			return true;
		else return false;
	}
}
?>