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 . "
" . $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 = "";
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 = "
";
$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 "";print_r($info);echo "
";
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 "";print_r($info);echo "
";
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;
}
}
?>