<?php
session_start();

class ObservatorioController extends Controller
{
	public $layout='//layouts/observatorio';

	public function actionIndex(){
				
		$this->render('index');
	}

	public function actionCategorias($id){
		$model = BasesCategorias::model()->findAll(array('condition'=>'id_categoria = '.$id, 'order'=>'id_base_categoria DESC'));
		$modelRecientes = BasesEncuestas::model()->findAll(array('condition'=>'activo = 1', 'order'=>'log DESC', 'limit'=>3));
		$modelVistas = BasesEncuestas::model()->findAll(array('condition'=>'activo = 1', 'order'=>'vistas DESC', 'limit'=>3));

		$this->render('categorias',array('model'=>$model, 'modelRecientes'=>$modelRecientes, 'modelVistas'=>$modelVistas, 'id_tipo' => $id));
	}

	public function actionCategoriaSort(){
		$returnArr = array();
		$order = $_POST['order'];
		$sort = $_POST['sort'];
		$id_tipo = $_POST['id_tipo'];
		$returnArr['contenido'] = '';
		$returnArr['check'] = 1;

		$criteria = new CDbCriteria;
		$criteria->select = 't.*';
		$criteria->join = 'LEFT JOIN tbl_bases_categorias bc ON bc.id_base_encuesta = t.id_encuesta_base';
		$criteria->addCondition('bc.id_categoria = '.$id_tipo);
		$criteria->addCondition('t.activo = 1');
		
		switch ($order) {
			case 'recientes':
				$criteria->order = 't.log '.$sort;
			break;
			
			case 'visitados':
				$criteria->order = 't.vistas '.$sort;
			break;

			case 'alfabeto':
				$criteria->order = 't.encuesta '.$sort;
			break;
		}

		$model = BasesEncuestas::model()->findAll($criteria);

		/*$_SESSION['doc_order'] = $order_var;
		$_SESSION['doc_sort'] = $sort;*/

		if(!empty($model)){
			foreach ($model as $item) {
				$returnArr['contenido'].= '<div class="inner-results"><h3>';
				if(!empty($item->licencia_uso)){
            		$returnArr['contenido'].=  '<a class="mensajeLicencia" href="javascript:void(0)" data-id="'.$item->id_encuesta_base.'" >';
            	}else{
            	 	$returnArr['contenido'].=  '<a href="'.Yii::app()->request->baseUrl.'/index.php/observatorio/grafica/id/'.$item->id_encuesta_base.'.html" data-id="'.$item->id_encuesta_base.'" >';
        	 	}

				$returnArr['contenido'].=  $item->encuesta.'</a></h3><ul class="list-inline up-ul">';

				$categorias = Yii::app()->funciones->listaBaseCategorias($item->id_encuesta_base);

                foreach ($categorias as $id => $nombre) {
                    $returnArr['contenido'].= '<li><a href="'.$this->createUrl('categorias',array('id'=>$id)).'"><i class="fa fa-tag" style="margin-right:8px"></i>'.$nombre.'</a></li>';
                }

                $returnArr['contenido'].= '</ul><div class="row"><div class="col-md-2"><center>'.
                '<img src="'.Yii::app()->request->baseUrl.'/images/icons/database.ico" />'.
                '</center></div><div class="col-md-10"><p>'.Yii::app()->funciones->cortarString($item->resumen,500).'</p></div></div><ul class="list-inline down-ul">';

                if(!empty($item->fecha)){
                	$returnArr['contenido'].= '<li><i class="fa fa-calendar" style="margin-right:8px"></i>'.$item->fecha.'</li>';
                }

                if(!empty($item->autor)){
                	$returnArr['contenido'].= '<li><i class="fa fa-pencil" style="margin-right:8px"></i>'.$item->autor.'</li>';
                }
                
                $returnArr['contenido'].= '</ul></div><hr>';
			}

		}else{
			$returnArr['check'] = 0;
		}

		echo json_encode($returnArr);
		Yii::app()->end();
	}

	public function actionEncuesta($id){
		
		$model = BasesEncuestas::model()->findByPk($id);
		$tabla = 'enc_'.$model->tabla;
		$connection = Yii::app()->db;


		$model = BasesEncuestas::model()->findByPk($id);
		$tbl = 'enc_'.$model->tabla;
		$connect = Yii::app()->db;

		$tableExist = Yii::app()->funciones->checkIfTableExist($tbl);

		if($tableExist){
			$dataReader = $connection->createCommand('SELECT * FROM '.$tabla.' WHERE mostrar = 1 ORDER BY id ASC')->query();

			$columnas = array();
			$clm_enc = array();

			while(($row = $dataReader->read())!==false){
				$columnas[] = $row;
				$clm_enc[] = $row['encabezado'];
			}

			$sql_select = implode(',', $clm_enc);
			$informacion = array();

			$sql = 'SELECT '.$sql_select.' FROM '.$model->tabla.' LIMIT 200';
			$dataReader=$connection->createCommand($sql)->query();

			while(($row = $dataReader->read())!==false){
				$informacion[] = $row;
			}

			$modelArchivos = BasesArchivos::model()->findByAttributes(array('id_base_encuesta' => $id, 'id_tipo_archivo' => 2));

			if(!empty($modelArchivos)){
				$libro_codigos = $modelArchivos->archivo;
			}else{
				$libro_codigos = '';
			}

			/*$model->vistas = $model->vistas + 1;
			$model->saveAttributes(array('vistas'=> $model->vistas));*/
			return array('columnas'=>$columnas, 'informacion'=>$informacion, 'tabla'=>$model->tabla, 'id_encuesta' => $id);
			//$this->render('encuesta',array('model'=>$model, 'columnas'=>$columnas, 'informacion'=>$informacion, 'tabla'=>$model->tabla, 'id_encuesta' => $id, 'libro_codigos'=>$libro_codigos));
		}

		else{
			$model->vistas = $model->vistas + 1;
			$model->saveAttributes(array('vistas'=> $model->vistas));

			$modelArchivos = BasesArchivos::model()->findByAttributes(array('id_base_encuesta' => $id, 'id_tipo_archivo' => 2));

			if(!empty($modelArchivos)){
				$libro_codigos = $modelArchivos->archivo;
			}else{
				$libro_codigos = '';
			}

			$Metadatos = $this->actionMetadatos($id);

			$this->render('grafica',array('model'=>$model, 'modelEnc'=>array(), 'libro_codigos' => $libro_codigos, 'columnas'=> array(), 'informacion'=>array(), 'tabla'=>$model->tabla, 'id_encuesta' => null,'modelBasesArchivos' => $Metadatos['modelBasesArchivos']));
		}

	}

	public function actionGrafica($id){
		$modelEnc = array();
		$model = BasesEncuestas::model()->findByPk($id);
		$tbl = 'enc_'.$model->tabla;
		$connect = Yii::app()->db;

		$tableExist = Yii::app()->funciones->checkIfTableExist($tbl);

		if($tableExist){
			$dataReader = $connect->createCommand('SELECT encabezado, nombre_corto, descripcion FROM '.$tbl.' WHERE mostrar = 1')->query();
			while(($row = $dataReader->read())!==false){
				$modelEnc[] = $row;
			}

			$model->vistas = $model->vistas + 1;
			$model->saveAttributes(array('vistas'=> $model->vistas));

			$modelArchivos = BasesArchivos::model()->findByAttributes(array('id_base_encuesta' => $id, 'id_tipo_archivo' => 2));

			if(!empty($modelArchivos)){
				$libro_codigos = $modelArchivos->archivo;
			}else{
				$libro_codigos = '';
			}
			$Encuesta = $this->actionEncuesta($id);
			$Metadatos = $this->actionMetadatos($id);

			$this->render('grafica',array('model'=>$model, 'modelEnc'=>$modelEnc, 'libro_codigos' => $libro_codigos,'columnas'=>$Encuesta['columnas'], 'informacion'=>$Encuesta['informacion'], 'tabla'=>$model->tabla, 'id_encuesta' => $Encuesta['id_encuesta'],'modelBasesArchivos' => $Metadatos['modelBasesArchivos']));
			//$this->render('grafica',array('model'=>$model, 'modelEnc'=>$modelEnc, 'libro_codigos' => $libro_codigos));
		}
		else{
			$model->vistas = $model->vistas + 1;
			$model->saveAttributes(array('vistas'=> $model->vistas));

			$modelArchivos = BasesArchivos::model()->findByAttributes(array('id_base_encuesta' => $id, 'id_tipo_archivo' => 2));

			if(!empty($modelArchivos)){
				$libro_codigos = $modelArchivos->archivo;
			}else{
				$libro_codigos = '';
			}

			$Metadatos = $this->actionMetadatos($id);

			$this->render('grafica',array('model'=>$model, 'modelEnc'=>array(), 'libro_codigos' => $libro_codigos, 'columnas'=> array(), 'informacion'=>array(), 'tabla'=>$model->tabla, 'id_encuesta' => null,'modelBasesArchivos' => $Metadatos['modelBasesArchivos']));
		}
	}

	public function actionMetadatos($id){
		$model = BasesEncuestas::model()->findByPk($id);
		$modelBasesArchivos = BasesArchivos::model()->findAll(array('condition'=>'id_base_encuesta = '.$id));
		return array('modelBasesArchivos' => $modelBasesArchivos);
		//$this->renderPartial('metadatos',array('model'=>$model, 'modelBasesArchivos' => $modelBasesArchivos));
	}

	public function actionDescargables(){
		$model = BasesEncuestas::model()->findAll(array('condition'=>'activo = 1 AND descargable = 1', 'order' => 'log DESC'));
		$modelRecientes = BasesEncuestas::model()->findAll(array('condition'=>'activo = 1', 'order'=>'log DESC', 'limit'=>3));
		$modelVistas = BasesEncuestas::model()->findAll(array('condition'=>'activo = 1', 'order'=>'vistas DESC', 'limit'=>3));

		$this->render('descargables',array('model'=>$model, 'modelRecientes'=>$modelRecientes, 'modelVistas'=>$modelVistas));
	}

	public function actionMetaDescargables($id){
		$model = BasesEncuestas::model()->findByPk($id);
		$modelBasesArchivos = BasesArchivos::model()->findAll(array('condition'=>'id_base_encuesta = '.$id));
		
		$this->render('_metadatosDes',array('model'=>$model, 'modelBasesArchivos' => $modelBasesArchivos));
	}

	public function actionRegistroLicencia(){
		/*$returnArr = array();
		$returnArr['check'] = 0;*/

		if(isset($_POST['id_encuesta'])){
			$id = $_POST['id_encuesta'];
			$nombre = $_POST['nombre'];
			$correo = $_POST['correo'];
			$tipo = $_POST['tipo'];

			$model = new BasesLicencias;
			$model->nombre = $nombre;
			$model->correo = $correo;
			$model->id_base_encuesta = $id;

			if($model->save()){
				//$returnArr['check'] = 1;
				if($tipo == 1) $this->redirect(array('grafica', 'id'=>$id));
				else $this->redirect(array('metaDescargables', 'id'=>$id));
			}
		}

		/*echo json_encode($returnArr);
		Yii::app()->end();*/
	}

	public function actionActualizarTabla(){
		$tbl = $_POST['tabla'];
		$returnArr = array();
		$inf = array();
		$ocultar = array();
		$maximo = array();
		$minimo = array();
		$suma = array();
		$promedio = array();					
	    $columnas = array();
	    $columnas1 = array();
	    $columnas_sql = array();
		$tabla = '';
		$encabezado = '';
		$pr_id = '';
		$where = '';
		$agrupar = '';

		$connection = Yii::app()->db;
		$informacion = array();

		//CONSULTA TODAS LAS COLUMNAS DE LA BD
		$sql = 'SHOW COLUMNS FROM '.$tbl;
	    $command = $connection->createCommand($sql);
	    $dataReader=$command->query();

	    while(($row = $dataReader->read())!==false){
			$columnas[] = $row['Field'];
		}
		//END CONSULTA TODAS LAS COLUMNAS DE LA BD


		//COMPRUEBA SI SE AGREGAN O QUITAN CHKS
		if($_POST['chk'] == 1){
			$opcion = $_POST['id'];

			switch ($opcion) {
			    case "agrupar": 
			        $_SESSION['group'] = $_POST['query'];
			    break;

			    case "ocultar":			        
			        if(isset($_SESSION['ocultar'])){
						
						$ocultar = $_SESSION['ocultar'];
						array_push($ocultar, $_POST['query']);
						$_SESSION['ocultar'] = $ocultar;
						
					}else{
						array_push($ocultar, $_POST['query']);
						$_SESSION['ocultar'] = $ocultar;
					}
			    break;

			    case "maximo":			       
			        if(isset($_SESSION['maximo'])){
						
						$maximo = $_SESSION['maximo'];
						array_push($maximo, $_POST['query']);
						$_SESSION['maximo'] = $maximo;
						
					}else{
						array_push($maximo, $_POST['query']);
						$_SESSION['maximo'] = $maximo;
					}
			    break;

			    case "minimo":			        
			        if(isset($_SESSION['minimo'])){
						
						$minimo = $_SESSION['minimo'];
						array_push($minimo, $_POST['query']);
						$_SESSION['minimo'] = $minimo;
						
					}else{ 
						array_push($minimo, $_POST['query']);
						$_SESSION['minimo'] = $minimo;
					}
			    break;

			    case "promedio":			        
			        if(isset($_SESSION['promedio'])){
						
						$promedio = $_SESSION['promedio'];
						array_push($promedio, $_POST['query']);
						$_SESSION['promedio'] = $promedio;
						
					}else{ 
						array_push($promedio, $_POST['query']);
						$_SESSION['promedio'] = $promedio;
					}
			    break;

			    case "suma":			        
			        if(isset($_SESSION['suma'])){
						
						$suma = $_SESSION['suma'];
						array_push($suma, $_POST['query']);
						$_SESSION['suma'] = $suma;
						
					}else{ 
						array_push($suma, $_POST['query']);
						$_SESSION['suma'] = $suma;
					}
			    break;

			    default:
			        // 0 => nombre_columna 		1=>contenido
					$query = explode('-', $_POST['query']);
					$columna = $query[0];
					$contenido = $query[1];

					if (isset($_SESSION['query'])){
						$inf = $_SESSION['query'];

						if(array_key_exists($columna, $inf)){
							if(!in_array($contenido, $inf[$columna])){
								array_push($inf[$columna], $contenido);
								$_SESSION['query'] = $inf;
							}
						}else{
							$inf[$columna] = array($contenido);
							$_SESSION['query'] = $inf;
						}

					}else{
						$inf[$columna] = array($contenido);
						$_SESSION['query'] = $inf;
					}
			}
		}else if($_POST['chk'] == 0){

			$opcion = $_POST['id'];

			switch ($opcion) {
			    
			    case "agrupar":
			        unset($_SESSION['group']);
			        unset($_SESSION['maximo']);
			        unset($_SESSION['minimo']);
			        unset($_SESSION['promedio']);
			        unset($_SESSION['suma']);
			    break;

			    case "ocultar":
			        $ocultar = $_SESSION['ocultar'];
					if(in_array($_POST['query'],$ocultar)){

						foreach ($ocultar as $id => $oculto) {
							if($oculto == $_POST['query']){
								unset($ocultar[$id]);
							}
						}

						if(count($ocultar) == 0){
							unset($_SESSION['ocultar']);
						}else{
							$_SESSION['ocultar'] = $ocultar;
						}

					}
			    break;

			    case "maximo":
			        $maximo = $_SESSION['maximo'];
					if(in_array($_POST['query'],$maximo)){

						foreach ($maximo as $id => $max) {
							if($max == $_POST['query']){
								unset($maximo[$id]);
							}
						}

						if(count($maximo) == 0){
							unset($_SESSION['maximo']);
						}else{
							$_SESSION['maximo'] = $maximo;
						}

					}
			    break;

			    case "minimo":
			        $minimo = $_SESSION['minimo'];
					if(in_array($_POST['query'],$minimo)){

						foreach ($minimo as $id => $min) {
							if($min == $_POST['query']){
								unset($minimo[$id]);
							}
						}

						if(count($minimo) == 0){
							unset($_SESSION['minimo']);
						}else{
							$_SESSION['minimo'] = $minimo;
						}

					}
			    break;

			    case "promedio":
			        $promedio = $_SESSION['promedio'];
					if(in_array($_POST['query'],$promedio)){

						foreach ($promedio as $id => $prom) {
							if($prom == $_POST['query']){
								unset($promedio[$id]);
							}
						}

						if(count($promedio) == 0){
							unset($_SESSION['promedio']);
						}else{
							$_SESSION['promedio'] = $promedio;
						}

					}
			    break;

			    case "suma":
			        $suma = $_SESSION['suma'];
					if(in_array($_POST['query'],$suma)){

						foreach ($suma as $id => $sum) {
							if($sum == $_POST['query']){
								unset($suma[$id]);
							}
						}

						if(count($suma) == 0){
							unset($_SESSION['suma']);
						}else{
							$_SESSION['suma'] = $suma;
						}

					}
			    break;

			    default:
			        
			        $where = '';
					// 0 => nombre_columna 		1=>contenido
					$query = explode('-', $_POST['query']);
					$columna = $query[0];
					$contenido = $query[1];

					if (isset($_SESSION['query'])){
						$inf = $_SESSION['query'];

						if(array_key_exists($columna, $inf)){
							if(in_array($contenido, $inf[$columna])){
								$temp = array();
								$aux = '';
								$otro_query = array();
								$temp = $inf[$columna];

								//AQUI ME QUEDE count($inf)>1
								//print_r($temp);
								if(count($temp) > 1){

									foreach ($temp as $id => $valor) {
										if($contenido != $valor){
											$aux = $valor;
										}else{
											$aux = '';
										}

										if($aux != ''){$otro_query[] = $aux;}
									}

									$inf[$columna] = $otro_query;
									
								}else{
									unset($inf[$columna]);
								}
							}

							if(count($inf)>0){
								$_SESSION['query'] = $inf;
							}else{
								unset($_SESSION['query']);
							} //end if
						} //enf if array_key_exists
					}// end if isset($_SESSION['query'])	
			}
		} //END COMPRUEBA SI SE AGREGAN O QUITAN CHKS

		//SE LLENAN LOS ARRAYS DE LAS OPERACIONES
		if(isset($_SESSION['maximo'])){ $maximo =  $_SESSION['maximo']; }
		if(isset($_SESSION['minimo'])){ $minimo =  $_SESSION['minimo']; }
		if(isset($_SESSION['promedio'])){ $promedio =  $_SESSION['promedio']; }
		if(isset($_SESSION['suma'])){ $suma =  $_SESSION['suma']; }

		//SE CREAN LAS SENTENCIAS SQL PARA AGRUPAR Y/O OCULTAR DATOS
		if(isset($_SESSION['ocultar']) && isset($_SESSION['group'])){

			//Se revisa si es columna de numeros o string 
			if(Yii::app()->funciones->checkBD($tbl,$_SESSION['group']) == 1){
				$order = ' ORDER BY '.$_SESSION['group'].' * 1';
			}else{
				$order = ' ORDER BY '.$_SESSION['group'].' ASC';
			}

			$agrupar = ' GROUP BY '.$_SESSION['group'].$order;
			$ocultar = $_SESSION['ocultar'];

			foreach ($columnas as $id => $columna) {
				$temp = '';

				foreach ($ocultar as $id => $oculto) {
					if($columna == $oculto){
						$temp = '';
						break;	
					}else{
						$temp = $columna;
					}
				}

				if($temp != ''){ $columnas_sql[] = $temp; $columnas1[] = $temp;}
			}

			if(count($maximo)>0){
				foreach ($columnas_sql as $id => $columna) {
					foreach ($maximo as $key => $max) {
						if($max == $columna){
							$columnas_sql[$id] = 'MAX('.$columna.')';
							break;	
						}else{
							$columnas_sql[$id] = $columna;
						}
					}//end foreach maximo
				}//end foreach columnas_sql
			}//en if maximo

			if(count($minimo)>0){
				foreach ($columnas_sql as $id => $columna) {
					foreach ($minimo as $key => $min) {
						if($min == $columna){
							$columnas_sql[$id] = 'MIN('.$columna.')';
							break;	
						}else{
							$columnas_sql[$id] = $columna;
						}
					}//end foreach minimo
				}//end foreach columnas_sql
			}//en if minimo

			if(count($promedio)>0){
				foreach ($columnas_sql as $id => $columna) {
					foreach ($promedio as $key => $prom) {
						if($prom == $columna){
							$columnas_sql[$id] = 'AVG('.$columna.')';
							break;	
						}else{
							$columnas_sql[$id] = $columna;
						}
					}//end foreach promedio
				}//end foreach columnas_sql
			}//en if promedio

			if(count($suma)>0){
				foreach ($columnas_sql as $id => $columna) {
					foreach ($suma as $key => $sum) {
						if($sum == $columna){
							$columnas_sql[$id] = 'SUM('.$columna.')';
							break;	
						}else{
							$columnas_sql[$id] = $columna;
						}
					}//end foreach suma
				}//end foreach columnas_sql
			}//end if suma
		} else if(isset($_SESSION['group'])){

			//Se revisa si es columna de numeros o string 
			if(Yii::app()->funciones->checkBD($tbl,$_SESSION['group']) == 1){
				$order = ' ORDER BY '.$_SESSION['group'].' * 1';
			}else{
				$order = ' ORDER BY '.$_SESSION['group'].' ASC';
			}

			$agrupar = ' GROUP BY '.$_SESSION['group'].$order;

			$columnas1 = $columnas;
			$columnas_sql = $columnas;

			if(count($maximo)>0){
				foreach ($columnas_sql as $id => $columna) {
					foreach ($maximo as $key => $max) {
						if($max == $columna){
							$columnas_sql[$id] = 'MAX('.$columna.')';
							break;	
						}else{
							$columnas_sql[$id] = $columna;
						}
					}//end foreach maximo
				}//end foreach columnas_sql
			}//en if maximo

			if(count($minimo)>0){
				foreach ($columnas_sql as $id => $columna) {
					foreach ($minimo as $key => $min) {
						if($min == $columna){
							$columnas_sql[$id] = 'MIN('.$columna.')';
							break;	
						}else{
							$columnas_sql[$id] = $columna;
						}
					}//end foreach minimo
				}//end foreach columnas_sql
			}//en if minimo

			if(count($promedio)>0){
				foreach ($columnas_sql as $id => $columna) {
					foreach ($promedio as $key => $prom) {
						if($prom == $columna){
							$columnas_sql[$id] = 'AVG('.$columna.')';
							break;	
						}else{
							$columnas_sql[$id] = $columna;
						}
					}//end foreach promedio
				}//end foreach columnas_sql
			}//en if promedio

			if(count($suma)>0){
				foreach ($columnas_sql as $id => $columna) {
					foreach ($suma as $key => $sum) {
						if($sum == $columna){
							$columnas_sql[$id] = 'SUM('.$columna.')';
							break;	
						}else{
							$columnas_sql[$id] = $columna;
						}
					}//end foreach suma
				}//end foreach columnas_sql
			}//end if suma

		} else if(isset($_SESSION['ocultar'])){
			$ocultar = $_SESSION['ocultar'];

			foreach ($columnas as $id => $columna) {
				$temp = '';
				foreach ($ocultar as $key => $oculto) {
					if($columna == $oculto){
						$temp = '';
						break;
					}else{
						$temp = $columna;
					}
				}

				if($temp != ''){$columnas_sql[] = $temp; $columnas1[] = $temp;}
			}

		}else{
			$columnas_sql = $columnas;
			$columnas1 = $columnas;
		}
		//END SE CREAN LAS SENTENCIAS SQL PARA AGRUPAR Y/O OCULTAR DATOS

		//SE REALIZAN LAS CONSULTAS A LAS BASES DE DATOS CON LOS DATOS DADOS
		if(isset($_SESSION['query'])){

			$inf = $_SESSION['query'];

			foreach ($inf as $columna => $contenido) {
				$pr_id = $columna;
				$condicion = array();
				
				foreach ($contenido as $id => $info) {

					if(!is_numeric($info)){
						$info = '"'.$info.'"';
					}

					$condicion[]= $pr_id.' LIKE '.$info;
				}

				$where .= '('.implode(' OR ',$condicion).') AND ';

			}// end foreach

			$where = substr($where, 0, -4);
			$where = ' WHERE '.$where;
			$str_columnas = implode(', ', $columnas1);
			$sql = 'SELECT '.$str_columnas.' FROM '.$tbl.$where;
		    $command = $connection->createCommand($sql);
		    $dataReader=$command->query();

		    while(($row = $dataReader->read())!==false){
				$informacion[] = $row;
			}
		}else{

			$str_columnas = implode(', ', $columnas_sql);

			if($agrupar != ''){
				$sql = 'SELECT '.$str_columnas.' FROM '.$tbl.$agrupar;
			}else{
				$sql = 'SELECT '.$str_columnas.' FROM '.$tbl.' LIMIT 200';
			}

			$command = $connection->createCommand($sql);
			$dataReader=$command->query();

			while(($row = $dataReader->read())!==false){
				$informacion[] = $row;
			}
		}
		//END SE REALIZAN LAS CONSULTAS A LAS BASES DE DATOS CON LOS DATOS DADOS

		//SE CREA EL ENCABEZADO DE LA TABLA
		if( count($maximo)>0 || count($minimo)>0 || count($promedio)>0 || count($suma)>0 || count($ocultar)>0 ){
			$encabezado .= '<tr>';
			foreach ($columnas_sql as $id => $columna) {
				$encabezado .= '<th nowrap>'.$columna.' <div class="dropdownc"> <i class="fa fa-caret-down"></i> <span class="dropdownc-content">';
                
                if($agrupar != ''){
                	if($_SESSION['group'] == $columna){
                		$encabezado .= '<input type="checkbox" name="agrupar" id="agrupar-'.$columnas1[$id].'" value="'.$columnas1[$id].'" checked> Agrupar <br>'.
                		'<input type="checkbox" name="ocultar" id="ocultar-'.$columnas1[$id].'" value="'.$columnas1[$id].'" disabled> Ocultar <br> <hr style="margin: 10px 0 !important;">'.
                		'<input type="checkbox" name="maximo" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'" disabled> Máximo <br>'.
                		'<input type="checkbox" name="minimo" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'" disabled> Mínimo <br>'.
                		'<input type="checkbox" name="suma" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'" disabled> Sumar <br>'.
                		'<input type="checkbox" name="promedio" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'" disabled> Promedio <br> <hr style="margin: 10px 0 !important;">';
                	}else{
                		$encabezado .= '<input type="checkbox" name="agrupar" id="agrupar-'.$columnas1[$id].'" value="'.$columnas1[$id].'"> Agrupar <br>'.
                		'<input type="checkbox" name="ocultar" id="ocultar-'.$columnas1[$id].'" value="'.$columnas1[$id].'"> Ocultar <br> <hr style="margin: 10px 0 !important;">';

                		if(in_array($columnas1[$id], $maximo)){ $chk_max = ' checked '; }else{ $chk_max = ''; }
                		if(in_array($columnas1[$id], $minimo)){ $chk_min = ' checked '; }else{ $chk_min = ''; }
                		if(in_array($columnas1[$id], $suma)){ $chk_sum = ' checked '; }else{ $chk_sum = ''; }
                		if(in_array($columnas1[$id], $promedio)){ $chk_prom = ' checked '; }else{ $chk_prom = ''; }

                		
                		$encabezado .= '<input type="checkbox" name="maximo" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'"'.$chk_max.'> Máximo <br>'.
                		'<input type="checkbox" name="minimo" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'"'.$chk_min.'> Mínimo <br>'.
                		'<input type="checkbox" name="suma" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'"'.$chk_sum.'> Sumar <br>'.
                		'<input type="checkbox" name="promedio" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'"'.$chk_prom.'> Promedio <br> <hr style="margin: 10px 0 !important;">';
                	}
                }else{
                	$encabezado .= '<input type="checkbox" name="agrupar" id="agrupar-'.$columnas1[$id].'" value="'.$columnas1[$id].'"> Agrupar <br>'.
                	'<input type="checkbox" name="ocultar" id="ocultar-'.$columnas1[$id].'" value="'.$columnas1[$id].'"> Ocultar <br> <hr style="margin: 10px 0 !important;">'.
                	'<input type="checkbox" name="maximo" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'" disabled> Máximo <br>'.
                	'<input type="checkbox" name="minimo" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'" disabled> Mínimo <br>'.
                	'<input type="checkbox" name="suma" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'" disabled> Sumar <br>'.
                	'<input type="checkbox" name="promedio" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'" disabled> Promedio <br> <hr style="margin: 10px 0 !important;">';
                }
                
                if(Yii::app()->funciones->checkBD($tbl,$columnas1[$id]) == 1){
                    $order = ' ORDER BY '.$columnas1[$id].' * 1';
                }else{
                    $order = ' ORDER BY '.$columnas1[$id].' ASC';
                }

                $sql_dif = 'SELECT DISTINCT '.$columnas1[$id].' FROM '.$tbl.$order;
                $command = $connection->createCommand($sql_dif);
                $dataReader=$command->query();

                while(($row = $dataReader->read())!==false){
	            	$encabezado .= '<input type="checkbox" name="respuestas" id="respuestas" value="'.$columnas1[$id].'-'.$row[$columnas1[$id]].'"> '.$row[$columnas1[$id]].'<br>';
	            }

	            $encabezado .= '</span></div></th>';
			}

			$encabezado .= '</tr>';
		}else{
			$encabezado .= '<tr>';
			foreach ($columnas_sql as $id => $columna) {
				$encabezado .= '<th nowrap>'.$columna.' <div class="dropdownc"> <i class="fa fa-caret-down"></i> <span class="dropdownc-content">';
                
                if($agrupar != ''){
                	if($_SESSION['group'] == $columnas1[$id]){
                		$encabezado .= '<input type="checkbox" name="agrupar" id="agrupar-'.$columnas1[$id].'" value="'.$columnas1[$id].'" checked> Agrupar <br>'.
                		'<input type="checkbox" name="ocultar" id="ocultar-'.$columnas1[$id].'" value="'.$columnas1[$id].'" disabled> Ocultar <br> <hr style="margin: 10px 0 !important;">'.
                		'<input type="checkbox" name="maximo" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'" disabled> Máximo <br>'.
                		'<input type="checkbox" name="minimo" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'" disabled> Mínimo <br>'.
                		'<input type="checkbox" name="suma" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'" disabled> Sumar <br>'.
                		'<input type="checkbox" name="promedio" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'" disabled> Promedio <br> <hr style="margin: 10px 0 !important;">';
                	}else{
                		$encabezado .= '<input type="checkbox" name="agrupar" id="agrupar-'.$columnas1[$id].'" value="'.$columnas1[$id].'"> Agrupar <br>'.
                		'<input type="checkbox" name="ocultar" id="ocultar-'.$columnas1[$id].'" value="'.$columnas1[$id].'"> Ocultar <br> <hr style="margin: 10px 0 !important;">'.
                		'<input type="checkbox" name="maximo" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'"> Máximo <br>'.
                		'<input type="checkbox" name="minimo" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'"> Mínimo <br>'.
                		'<input type="checkbox" name="suma" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'"> Sumar <br>'.
                		'<input type="checkbox" name="promedio" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'"> Promedio <br> <hr style="margin: 10px 0 !important;">';
                	}
                }else{
                	$encabezado .= '<input type="checkbox" name="ocultar" id="ocultar-'.$columnas1[$id].'" value="'.$columnas1[$id].'"> Ocultar <br> <hr style="margin: 10px 0 !important;">'.
                	'<input type="checkbox" name="maximo" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'" disabled> Máximo <br>'.
                	'<input type="checkbox" name="minimo" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'" disabled> Mínimo <br>'.
                	'<input type="checkbox" name="suma" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'" disabled> Sumar <br>'.
                	'<input type="checkbox" name="promedio" id="operaciones-'.$columnas1[$id].'" value="'.$columnas1[$id].'" disabled> Promedio <br> <hr style="margin: 10px 0 !important;">';
                }
                
                if(Yii::app()->funciones->checkBD($tbl,$columnas1[$id]) == 1){
                    $order = ' ORDER BY '.$columnas1[$id].' * 1';
                }else{
                    $order = ' ORDER BY '.$columnas1[$id].' ASC';
                }

                $sql_dif = 'SELECT DISTINCT '.$columnas1[$id].' FROM '.$tbl.$order;
                $command = $connection->createCommand($sql_dif);
                $dataReader=$command->query();

                while(($row = $dataReader->read())!==false){
	            	$encabezado .= '<input type="checkbox" name="respuestas" id="respuestas" value="'.$columnas1[$id].'-'.$row[$columnas1[$id]].'"> '.$row[$columnas1[$id]].'<br>';
	            }

	            $encabezado .= '</span></div></th>';

			}

			$encabezado .= '</tr>';
		}
		//END SE CREA EL ENCABEZADO DE LA TABLA

		//SE CREA EL CUERPO DE LA TABLA
		foreach ($informacion as $id => $valor) {
            $tabla .= '<tr>';

            foreach($columnas_sql as $id => $columna){
            	 $tabla .= '<td>'.$valor[$columna].'</td>';
            }

            $tabla .= '</tr>';
        }
        //END SE CREA EL CUERPO DE LA TABLA

		$returnArr['informacion'] = $sql; 
		$returnArr['tabla'] = $tabla;
		$returnArr['encabezado'] = $encabezado;				
		echo json_encode($returnArr);
		Yii::app()->end();		
	}
	
public function actionCrearCSV(){

		$returnArr = array();
		//$returnArr['error'] = '';
		//$returnArr['url'] = '';

		$connection = Yii::app()->db;
		$informacion = array();

		//CONSULTA A LA BD
		$sql = 'SELECT * FROM tbl_bases_licencias';

	    $command = $connection->createCommand($sql);
	    $dataReader=$command->query();

	    while(($row = $dataReader->read())!==false){
			$informacion[] = $row;
		}

		$file = 'logs_'.date('Ymd_His');
		$filename = $_SERVER['DOCUMENT_ROOT'].'/resiliencia/R/Files/'.$file.'.csv';
		//$filename = dirname(__FILE__).'/R/files/id.csv';	

		$handler = fopen($filename, "w");
		
		fputcsv($handler,array('nombre','correo','id_base_encuesta','log'),';');	

		foreach ($informacion as $array) {

			$nombre = $array['nombre'];
			$correo = $array['correo'];	
			$encuesta = $array['id_base_encuesta'];	
			$log = $array['log'];	

			fputcsv($handler,array($nombre,$correo,$encuesta,$log),';');					
		}

		if(fclose($handler)){
			echo "OK";
			
		}else{
			echo "NO OK";
		}

		//echo json_encode($returnArr);
		Yii::app()->end();		
		
	}

	public function actionChartUpdate(){
		$returnArr = array();
		$connect = Yii::app()->db;
		$informacion = array();
		$inf = array();
		$diccionario = array();
		$chartData = array();
		$eje_y = array();

		if(isset($_POST['eje_x']) && isset($_POST['eje_y'])){
			
			foreach ($_POST['eje_y'] as $key => $value) {
				$eje_y[] = $value;
			}
			
			$eje_x = $_POST['eje_x'];
			$tabla = $_POST['tbl'];
			$option_y = $_POST['optiony'];
			$tbl_enc = 'enc_'.$tabla;
			$returnArr['chartInfo'] = array();
			$bandx = 0; $bandy = 0;

			if(Yii::app()->funciones->checkBD($tabla,$eje_x) == 1){
				$order = ' ORDER BY '.$eje_x.' * 1';
			}else{
				$order = ' ORDER BY '.$eje_x.' ASC';
			}

			/*
			*Aqui empieza lo de los diccionarios
			*/

			$id_diccionario = $connect->createCommand('SELECT id_diccionario FROM '.$tbl_enc.' WHERE encabezado = "'.$eje_x.'"')->queryScalar();

			if($eje_x != 'municipio'){

				switch ($option_y) {
					case 'suma':
						$op_suma = '';
						foreach ($eje_y as $key => $value) {
							$op_suma .= 'SUM(IF('.$value.' != "9999", '.$value.'*1,0)) AS '.$value.',';
						}

						$sql = 'SELECT '.$op_suma.$eje_x.' FROM '.$tabla.' GROUP BY '.$eje_x.$order;
					break;

					case 'max':
						$op_max = '';
						foreach ($eje_y as $key => $value) {
							$op_max .= 'MAX(IF('.$value.' = "9999", 0, '.$value.'*1)) AS '.$value.',';
						}

						$sql = 'SELECT '.$op_max.$eje_x.' FROM '.$tabla.' GROUP BY '.$eje_x.$order;
					break;

					case 'min':
						$op_min = '';
						foreach ($eje_y as $key => $value) {
							$op_min .= 'MIN(IF('.$value.' != "9999", '.$value.'*1,0)) AS '.$value.',';
						}
						$sql = 'SELECT '.$op_min.$eje_x.' FROM '.$tabla.' GROUP BY '.$eje_x.$order;
					break;

					case 'prom':
						$op_prom = '';
						foreach ($eje_y as $key => $value) {
							$op_prom .= 'AVG(IF('.$value.' != "9999", '.$value.'*1,0)) AS '.$value.',';
						}
						$sql = 'SELECT '.$op_prom.$eje_x.' FROM '.$tabla.' GROUP BY '.$eje_x.$order;
					break;

					case 'contar':
						$op_cont = '';
						$wh_cont = '';
						foreach ($eje_y as $key => $value) {
							//$op_cont .= 'COUNT(IF('.$value.' != 9999, '.$value.',0)) AS '.$value.',';
							$op_cont .= 'COUNT('.$value.') AS '.$value.',';
							$wh_cont .= $value.'<> "9999" AND ';
						}

						$wh_cont = substr($wh_cont,0,-4);

						$sql = 'SELECT '.$op_cont.$eje_x.' FROM '.$tabla.' WHERE '.$wh_cont.' GROUP BY '.$eje_x.$order;
					break;
					
					/*default:
						$sql = 'SELECT '.$eje_x.','.$eje_y.' FROM '.$tabla.' WHERE '.$eje_y.'<> 9999 GROUP BY '.$eje_x.$order;
						break;*/
				}

				$dataReader = $connect->createCommand($sql)->query();
				while(($row = $dataReader->read())!==false){
					$informacion[] = $row;
				}
								
				if($id_diccionario != 0){
					$modelDiccionario = Diccionarios::model()->findByPk($id_diccionario);
					
					$dataReader = $connect->createCommand('SELECT * FROM '.$modelDiccionario->tabla)->query();
					while(($row = $dataReader->read())!==false){
						$diccionario[$row['id']] = $row['nombre'];

					}

					$bandx = 1;
				}

				if(!empty($informacion)){
					foreach ($informacion as $item) {
						
						if($bandx == 1){
							$id_x = $item[$eje_x];
							$category = $diccionario[$id_x];
						}else{
							$category = $item[$eje_x];
						}

						if(count($eje_y) > 1){
							$temp_arr = array();

							$temp_arr = array('category' => utf8_decode($category));

							for ($i=0; $i < count($eje_y); $i++) { 
								if($i == 0){
									$value = 'value'; 
								}else{
									$value = 'value'.$i;
								}
								$temp_arr = array_merge($temp_arr, array( $value => $item[$eje_y[$i]]));
							}

							array_push($returnArr['chartInfo'], $temp_arr);
							
						}else{
							array_push($returnArr['chartInfo'], array(	'category' => utf8_decode($category),
																		'value' => $item[$eje_y[0]]));
						}
						
					}
				}
				
			}else{

				switch ($option_y) {
					case 'suma':
						$op_suma = '';
						foreach ($eje_y as $key => $value) {
							$op_suma .= 'SUM(IF('.$value.' != "9999", '.$value.'*1,0)) AS '.$value.',';
						}

						$sql = 'SELECT '.$op_suma.$eje_x.',estado FROM '.$tabla.' GROUP BY '.$eje_x.$order;
					break;

					case 'max':
						$op_max = '';
						foreach ($eje_y as $key => $value) {
							$op_max .= 'MAX(IF('.$value.' = "9999", 0, '.$value.'*1)) AS '.$value.',';
						}

						$sql = 'SELECT '.$op_max.$eje_x.',estado FROM '.$tabla.' GROUP BY '.$eje_x.$order;
					break;

					case 'min':
						$op_min = '';
						foreach ($eje_y as $key => $value) {
							$op_min .= 'MIN(IF('.$value.' != "9999", '.$value.'*1,0)) AS '.$value.',';
						}
						$sql = 'SELECT '.$op_min.$eje_x.',estado FROM '.$tabla.' GROUP BY '.$eje_x.$order;
					break;

					case 'prom':
						$op_prom = '';
						foreach ($eje_y as $key => $value) {
							$op_prom .= 'AVG(IF('.$value.' != "9999", '.$value.'*1,0)) AS '.$value.',';
						}
						$sql = 'SELECT '.$op_prom.$eje_x.',estado FROM '.$tabla.' GROUP BY '.$eje_x.$order;
					break;

					case 'contar':
						$op_cont = '';
						$wh_cont = '';
						foreach ($eje_y as $key => $value) {
							//$op_cont .= 'COUNT(IF('.$value.' != 9999, '.$value.',0)) AS '.$value.',';
							$op_cont .= 'COUNT('.$value.') AS '.$value.',';
							$wh_cont .= $value.'<> "9999" AND ';
						}

						$wh_cont = substr($wh_cont,0,-4);

						$sql = 'SELECT '.$op_cont.$eje_x.',estado FROM '.$tabla.' WHERE '.$wh_cont.' GROUP BY '.$eje_x.$order;
					break;
					
					/*default:
						$sql = 'SELECT '.$eje_x.','.$eje_y.' FROM '.$tabla.' WHERE '.$eje_y.'<> 9999 GROUP BY '.$eje_x.$order;
						break;*/
				}

				$dataReader = $connect->createCommand($sql)->query();
				while(($row = $dataReader->read())!==false){
					$informacion[] = $row;
				}

				if($id_diccionario != 0){
					$modelDiccionario = Diccionarios::model()->findByPk($id_diccionario);
					
					$dataReader = $connect->createCommand('SELECT * FROM '.$modelDiccionario->tabla)->query();
					while(($row = $dataReader->read())!==false){
						$diccionario[$row['id_estado']][$row['id_municipio']] = $row['municipio'];

					}

					$bandx = 1;
				}

				if(!empty($informacion)){
					foreach ($informacion as $item) {
						
						if($bandx == 1){
							$id_x = $item[$eje_x];
							$id_edo = $item['estado'];
							$category = $diccionario[$id_edo][$id_x];
						}else{
							$category = $item[$eje_x];
						}

						if(count($eje_y) > 1){
							$temp_arr = array();

							$temp_arr = array('category' => utf8_decode($category));

							for ($i=0; $i < count($eje_y); $i++) { 
								if($i == 0){
									$value = 'value'; 
								}else{
									$value = 'value'.$i;
								}
								$temp_arr = array_merge($temp_arr, array( $value => $item[$eje_y[$i]]));
							}

							array_push($returnArr['chartInfo'], $temp_arr);
							
						}else{
							array_push($returnArr['chartInfo'], array(	'category' => utf8_decode($category),
																		'value' => $item[$eje_y[0]]));
						}
						
					}
				}
			}

			/*
			*Aqui termina lo de los diccionarios
			*/			

			if(!empty($returnArr['chartInfo'])){
				$returnArr['check'] = 1;
			}else{
				$returnArr['check'] = 0;
			}

			//$returnArr['chartInfo'] = $chartData;
		}

		echo json_encode($returnArr);
		Yii::app()->end();
	}

	public function actionPieUpdate(){
		$returnArr = array();
		
		if(isset($_POST['field'])){
			$field = $_POST['field'];
			$tbl = $_POST['tbl'];
			$tbl_enc = 'enc_'.$tbl;
			$returnArr['chartInfo'] = array();
			$diccionario = array();
			$temp_arr = array();
			$connect = Yii::app()->db;

			$id_diccionario = $connect->createCommand('SELECT id_diccionario FROM '.$tbl_enc.' WHERE encabezado = "'.$field.'"')->queryScalar();

			$dataReader = $connect->createCommand('SELECT '.$field.', COUNT(*) AS total FROM '.$tbl.' GROUP BY '.$field)->query();

			if($id_diccionario != 0){
				$modelDiccionario = Diccionarios::model()->findByPk($id_diccionario);
					
				$dataReader = $connect->createCommand('SELECT * FROM '.$modelDiccionario->tabla)->query();
				while(($row = $dataReader->read())!==false){
					$diccionario[$row['id']] = $row['nombre'];
				}
			}

			while(($row = $dataReader->read())!==false){
				if($id_diccionario != 0){
					$category = $diccionario[$row[$field]];
				}else{
					$category = $row[$field];
				}

				$temp_arr = array('category'=>utf8_decode($category), 'value'=>$row['total']);
				array_push($returnArr['chartInfo'], $temp_arr);
			}//end while

			if(count($returnArr['chartInfo'])>0){
				$returnArr['check'] = 1;
			}else{
				$returnArr['check'] = 0;
			}// end if count
		} //end if

		echo json_encode($returnArr);
		Yii::app()->end();
	}

	public function actionChartInit(){
		$returnArr = array();
		$tabla =  (isset($_POST['tbl']))? $_POST['tbl'] : '';

		if($tabla == ''){
			$returnArr['check'] = 0;
		}else{
			$command = Yii::app()->db->createCommand('SELECT COUNT(*) AS total_clm FROM '.$tabla);
		    $total_clm = $command->queryScalar();

		    $returnArr['registros'] = $total_clm;
		    $returnArr['check'] = 1;
		}

	    echo json_encode($returnArr);
		Yii::app()->end();
	}

	public function actionMapUpdate(){
		$returnArr = array();
		$returnArr['chartInfo'] = array();
		$returnArr['check'] = 0;
		$returnArr['minimo'] = 0;
		$returnArr['maximo'] = 0;
		$connect = Yii::app()->db;
		$informacion = array();
		$valores = array();
		$eje_x = '';

		$option_y = $_POST['optiony'];	
		$tabla = $_POST['tbl'];
		$eje_x = $_POST['eje_x'];
		$eje_y = $_POST['eje_y'];
		$tbl_enc = 'enc_'.$tabla;

		$id_diccionario = $connect->createCommand('SELECT id_diccionario FROM '.$tbl_enc.' WHERE encabezado = "'.$eje_x.'"')->queryScalar();
		
		if($id_diccionario > 1){
			$id_edo = array('MX-MEX','MX-JAL','MX-SIN','MX-NAY');
		}else{
			$id_edo = array('MX-AGU','MX-BCN','MX-BCS','MX-CAM','MX-COA','MX-COL','MX-CHP','MX-CHH',
						'MX-DIF','MX-DUR','MX-GUA','MX-GRO','MX-HID','MX-JAL','MX-MEX','MX-MIC',
						'MX-MOR','MX-NAY','MX-NLE','MX-OAX','MX-PUE','MX-QUE','MX-ROO','MX-SLP',
						'MX-SIN','MX-SON','MX-TAB','MX-TAM','MX-TLA','MX-VER','MX-YUC','MX-ZAC');
		}

		$id_edo_name = array( 'aguascalientes' => 'MX-AGU', 'baja california'=> 'MX-BCN','baja california sur'=>'MX-BCS',
							 'campeche' => 'MX-CAM', 'coahuila' => 'MX-COA', 'colima' => 'MX-COL', 'chiapas' => 'MX-CHP',
							 'chihuahua'=> 'MX-CHH', 'ciudad de mexico'=>'MX-DIF', 'distrito federal' => 'MX-DIF',
							 'durango' => 'MX-DUR', 'guanajuato' => 'MX-GUA','guerrero'=>'MX-GRO', 'hidalgo'=> 'MX-HID', 
							 'jalisco' =>'MX-JAL', 'mexico'=>'MX-MEX', 'michoacan'=>'MX-MIC','morelos'=>'MX-MOR',
							 'nayarit'=>'MX-NAY','nuevo leon'=>'MX-NLE', 'oaxaca'=> 'MX-OAX','puebla' =>'MX-PUE', 
							 'queretaro' => 'MX-QUE', 'quintana roo' => 'MX-ROO', 'san luis potosi'=> 'MX-SLP',
							 'sinaloa' => 'MX-SIN','sonora'=>'MX-SON','tabasco' => 'MX-TAB', 'tamaulipas'=> 'MX-TAM',
							 'tlaxcala' => 'MX-TLA','veracruz' => 'MX-VER', 'yucatan' => 'MX-YUC','zacatecas' => 'MX-ZAC');

		if(Yii::app()->funciones->checkBD($tabla,$eje_x) == 1){
			$order = ' ORDER BY '.$eje_x.' * 1';
		}else{
			$order = ' ORDER BY '.$eje_x.' ASC';
		}
		yii::log($eje_x);
		switch ($option_y) {
			case 'suma':
				$sql = 'SELECT SUM(IF('.$eje_y.' = 9999, 0, '.$eje_y.')) AS '.$eje_y.', '.$eje_x.' FROM '.$tabla.' GROUP BY '.$eje_x.$order;
			break;

			case 'max':
				$sql = 'SELECT MAX(IF('.$eje_y.'  = 9999, 0, '.$eje_y.')) AS '.$eje_y.', '.$eje_x.' FROM '.$tabla.' GROUP BY '.$eje_x.$order;
			break;

			case 'min':
				$sql = 'SELECT MIN(IF('.$eje_y.' = 9999, 0, '.$eje_y.')) AS '.$eje_y.', '.$eje_x.' FROM '.$tabla.' GROUP BY '.$eje_x.$order;
			break;

			case 'prom':
				$sql = 'SELECT AVG(IF('.$eje_y.' = 9999, 0, '.$eje_y.')) AS '.$eje_y.', '.$eje_x.' FROM '.$tabla.' GROUP BY '.$eje_x.$order;
			break;

			case 'contar':
				$sql = 'SELECT COUNT('.$eje_y.') AS '.$eje_y.', '.$eje_x.' FROM '.$tabla.' WHERE '.$eje_y.'<> 9999 GROUP BY '.$eje_x.$order;
			break;
		}

		$dataReader = $connect->createCommand($sql)->query();
		while(($row = $dataReader->read())!==false){
			$informacion[] = $row;
		}

		if(!empty($informacion)){
			foreach ($informacion as $item) {
				if('string'==gettype($item[$eje_x])){
					$edo = strtolower($item[$eje_x]);
				    $edo = str_replace(
				        array('á', 'é', 'í','ó','ú'),
				        array('a', 'e', 'i','o','u'),
				        $edo
				    );
				    if(isset($id_edo_name[$edo])){
						$id = $id_edo_name[$edo];
				    }else{
				    	if(is_numeric($edo) && isset($id_edo[$edo-1])){
				    		$edo = $edo-1;
				    		$id = $id_edo[$edo];	
						}
				    }
				}
				
				array_push($returnArr['chartInfo'], array('id' => $id, 'value' => $item[$eje_y]));
				$valores[] = $item[$eje_y];
				
			}

			$tmp_min = min($valores);
			$tmp_max = max($valores);

			$returnArr['minimo'] = $tmp_min;
			$returnArr['maximo'] = $tmp_max;
			$returnArr['check'] = 1;
		}

		echo json_encode($returnArr);
		Yii::app()->end();
	}

	public function actionMapLabels(){
		$connect = Yii::app()->db;
		$returnArr = array();
		$returnArr['check'] = 0;
		$returnArr['label'] = array();
		$tabla 		= $_POST['tbl'];
		$eje_x 		= $_POST['eje_x'];
		$eje_y 		= $_POST['eje_y'];
		$tbl_enc 	= 'enc_'.$tabla;

		$order = ' ORDER BY '.$eje_x.' * 1';
		$where = ' WHERE longitude <> "null" AND ' .$eje_x. ' <> "null" ';
		$sql = 'SELECT '.$eje_y.', '.$eje_x. ', longitude FROM '.$tabla.$where.$order;
		$dataReader = $connect->createCommand($sql)->query();

		while(($row = $dataReader->read())!==false){
			$informacion[] = $row;
		}
			
		if(!empty($informacion)){
			$pointLocation = new pointLocation();
			foreach ($informacion as $item) {
				$point = array("x" => $item[$eje_x], "y" => $item["longitude"]);
				$posicion = $pointLocation->pointInPolygon($point);
				if($posicion=="inside" || $posicion=="boundary"){
					if(isset($returnArr['label'][utf8_decode($item[$eje_y])])){
						$returnArr['label'][utf8_decode($item[$eje_y])]++;	
					}else{
						$returnArr['label'][utf8_decode($item[$eje_y])]=1;	
					}
				}
			}
			$returnArr['check'] = 1;
		}

		 $returnArr['label'] = array_filter($returnArr['label'], function($value){
		 	return ($value > 0);
		 });
		 //Yii::log(json_encode($returnArr['label']));

		 arsort($returnArr['label']); //ordena el arreglo por más frecuentes
		 $returnArr['label'] = array_slice($returnArr['label'],0,10,true); //regresa los 10 más frecuentes, true para que procese labels numéricos
		 
		 if(max($returnArr['label']) == 1 ){      //si el máximo de los que se repiten es 1, todos son diferentes, limpia el arreglo para no regresarlos
		     //Yii::log("valores unicos");
		     $returnArr['label'] = array();
		 }

		echo json_encode($returnArr);
		Yii::app()->end();
	}

	public function actionMapUpdateLat(){
		$returnArr = array();
		$returnArr['chartInfo'] = array();
		$returnArr['check'] = 0;
		$returnArr['minimo'] = 0;
		$returnArr['maximo'] = 0;
		$connect = Yii::app()->db;
		$informacion = array();
		$result = array();
		$valores = array();
		$eje_x = '';

		$option_y = $_POST['optiony'];	
		$porcentaje = $_POST['porcentaje'];	
		$tabla = $_POST['tbl'];
		$eje_x = $_POST['eje_x'];
		$eje_y = $_POST['eje_y'];
		$tbl_enc = 'enc_'.$tabla;
		if(isset($_POST['filter'])){
			$filter = utf8_encode($_POST['filter']);
		}

		$targetSVG = "M9,0C4.029,0,0,4.029,0,9s4.029,9,9,9s9-4.029,9-9S13.971,0,9,0z M9,15.93 c-3.83,0-6.93-3.1-6.93-6.93S5.17,2.07,9,2.07s6.93,3.1,6.93,6.93S12.83,15.93,9,15.93 M12.5,9c0,1.933-1.567,3.5-3.5,3.5S5.5,10.933,5.5,9S7.067,5.5,9,5.5 S12.5,7.067,12.5,9z";

		$order = ' ORDER BY '.$eje_x.' * 1';
		
		if(isset($filter) && $filter!="all"){
			$where = ' WHERE longitude <> "null" AND ' .$eje_x. ' <> "null" AND ' .$eje_y. ' = "' .$filter. '"';
		}else{
			$where = ' WHERE longitude <> "null" AND ' .$eje_x. ' <> "null" ';
		}

		$sql = 'SELECT '.$eje_y.', '.$eje_x. ', longitude FROM '.$tabla.$where.$order;
		$dataReader = $connect->createCommand($sql)->query();

		while(($row = $dataReader->read())!==false){
			$informacion[] = $row;
		}
			
		if(!empty($informacion)){
			$pointLocation = new pointLocation();
			$counter = 0;

			foreach ($informacion as $item) {
				$point = array("x" => $item[$eje_x], "y" => $item["longitude"]);
				$posicion = $pointLocation->pointInPolygon($point);
				if($posicion=="inside" || $posicion=="boundary"){
			       array_push($returnArr['chartInfo'], 
				   array('user' => $counter, 'latitude' => $item[$eje_x],'longitude' => $item["longitude"],
				    'title' => utf8_decode($item[$eje_y]),'svgPath' => $targetSVG, 'zoomlevel' => 5, 'scale' => 0.5));
					$counter++;
					$valores[] = utf8_decode($item[$eje_y]);	
				}
			}

			if(!empty($returnArr['chartInfo'])){
				$index = round((sizeof($returnArr['chartInfo'])*$porcentaje)/100,0);
				shuffle($returnArr['chartInfo']);
				shuffle($returnArr['chartInfo']);
				shuffle($returnArr['chartInfo']);
				if($index == 0){
					$returnArr['chartInfo'] = array_slice($returnArr['chartInfo'],0,1);
				}else{
					$returnArr['chartInfo'] = array_slice($returnArr['chartInfo'],0,$index);
				}

				$tmp_min = min($valores);
				$tmp_max = max($valores);
				$returnArr['minimo'] = $tmp_min;
				$returnArr['maximo'] = $tmp_max;
				$returnArr['check'] = 1;
			}
		}

		echo json_encode($returnArr);
		Yii::app()->end();
	}

	public function actionObtenerTexto(){

		$returnArr = array();

		$model = BasesEncuestas::model()->findByPk($_POST['id']);

		if(!empty($model)){
			$returnArr['check'] = 1;
			$returnArr['texto'] = $model->licencia_uso;
		}else{
			$returnArr['check'] = 0;
		}

		echo json_encode($returnArr);
		Yii::app()->end();	
	}

	public function actionObtenerResumen(){
		$returnArr = array();

		$model = BasesEncuestas::model()->findByPk($_POST['id']);

		if(!empty($model)){
			$returnArr['check'] = 1;
			$returnArr['resumen'] = $model->resumen;
		}else{
			$returnArr['check'] = 0;
		}

		echo json_encode($returnArr);
		Yii::app()->end();	
	}

	// Uncomment the following methods and override them if needed
	/*
	public function filters()
	{
		// return the filter configuration for this controller, e.g.:
		return array(
			'inlineFilterName',
			array(
				'class'=>'path.to.FilterClass',
				'propertyName'=>'propertyValue', 
			),
		);
	}

	public function actions()
	{
		// return external action classes, e.g.:
		return array(
			'action1'=>'path.to.ActionClass',
			'action2'=>array(
				'class'=>'path.to.AnotherActionClass',
				'propertyName'=>'propertyValue',
			),
		);
	}
	*/

	public function actionMulticapa(){
		$this->render('multicapa');
	}
}

class pointLocation {
    var $pointOnVertex = true; // Check if the point sits exactly on one of the vertices?
 	var $polygon = array(array("x" => 32.50116, "y" => -117.10436),
						 array("x" => 32.79717, "y" => -114.77526),
						 array("x" => 31.41996, "y" => -111.03991),
						 array("x" => 31.4762,  "y" => -108.24938 ),
						 array("x" => 31.8689,  "y" => -106.5135),
						 array("x" => 29.16244, "y" => -103.28356),
						 array("x" => 29.92706, "y" =>  -102.36071),
						 array("x" => 28.58521, "y" => -100.20739 ),
						 array("x" => 26.00813, "y" => -97.15319),
						 array("x" => 21.52637, "y" => -86.75905),
						 array("x" => 18.36116, "y" => -88.15544),
						 array("x" => 17.77627, "y" => -89.82536),
						 array("x" => 17.14747, "y" => -91.09978),
						 array("x" => 16.30576, "y" => -90.33073),
						 array("x" => 15.98917, "y" => -90.924),
						 array("x" => 15.73554, "y" => -91.8029),
						 array("x" => 14.63287, "y" => -92.15447),
						 array("x" => 13.57645, "y" => -97.25819),
						 array("x" => 15.52623, "y" =>  -107.46375 ),
						 array("x" => 21.88937, "y" => -114.29849),
						 array("x" => 32.50116, "y" => -117.10436),
						);
			
    function pointLocation() {
    }
 
    function pointInPolygon($point, $pointOnVertex = true) {
        $this->pointOnVertex = $pointOnVertex;
        
        $vertices = array(); 
        $vertices = $this->polygon;
 
        // Check if the point sits exactly on a vertex
        if ($this->pointOnVertex == true and $this->pointOnVertex($point, $vertices) == true) {
            return "vertex";
        }
 
        // Check if the point is inside the polygon or on the boundary
        $intersections = 0; 
        $vertices_count = count($vertices);
 
        for ($i=1; $i < $vertices_count; $i++) {
            $vertex1 = $vertices[$i-1]; 
            $vertex2 = $vertices[$i];
            if ($vertex1['y'] == $vertex2['y'] and $vertex1['y'] == $point['y'] and $point['x'] > min($vertex1['x'], $vertex2['x']) and $point['x'] < max($vertex1['x'], $vertex2['x'])) { // Check if point is on an horizontal polygon boundary
                return "boundary";
            }
            if ($point['y'] > min($vertex1['y'], $vertex2['y']) and $point['y'] <= max($vertex1['y'], $vertex2['y']) and $point['x'] <= max($vertex1['x'], $vertex2['x']) and $vertex1['y'] != $vertex2['y']) { 
                $xinters = ($point['y'] - $vertex1['y']) * ($vertex2['x'] - $vertex1['x']) / ($vertex2['y'] - $vertex1['y']) + $vertex1['x']; 
                if ($xinters == $point['x']) { // Check if point is on the polygon boundary (other than horizontal)
                    return "boundary";
                }
                if ($vertex1['x'] == $vertex2['x'] || $point['x'] <= $xinters) {
                    $intersections++; 
                }
            } 
        } 
        // If the number of edges we passed through is odd, then it's in the polygon. 
        if ($intersections % 2 != 0) {
            return "inside";
        } else {
            return "outside";
        }
    }
 
    function pointOnVertex($point, $vertices) {
        foreach($vertices as $vertex) {
            if ($point == $vertex) {
                return true;
            }
        }
    }
}