<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Model_Personal extends CI_Model{

	function __construct(){
		parent::__construct();
	}

	function find($pk){
		$query = $this->db->query("SELECT cp.pk_cat_personal,cp.nombre,cp.apat,cp.amat,cg.correo,cp.usuario 
									FROM cat_personal cp, cat_generales cg 
									WHERE cp.fk_cat_generales=cg.pk_cat_generales and cp.pk_cat_personal=".$pk);
		return $query->row();
	}

	function update($registro){
		//print_r($registro);
		$this->db->query("UPDATE cat_personal cp, cat_generales cg 
							SET cp.nombre='".$registro["nombre"]."',
								cp.apat='".$registro["apat"]."',
								cp.amat='".$registro["amat"]."',
								cp.usuario='".$registro["usuario"]."',
								cg.correo='".$registro["correo"]."' 
								WHERE cp.fk_cat_generales=cg.pk_cat_generales and cp.pk_cat_personal=".$registro["pk_cat_personal"]);
	}

	function find_pass($pk){
		$this->db->where('pk_cat_personal',$pk);
		return $this->db->get('cat_personal')->row();
	}

	function update_pass($registro){
		$this->db->set($registro);
		$this->db->where('pk_cat_personal',$registro['pk_cat_personal']);
		$this->db->update('cat_personal');
	}

	function cargar_personal(){
		$query = $this->db->query("SELECT pk_cat_personal,CONCAT_WS(' ',nombre,apat,amat) as nombre FROM cat_personal WHERE fk_cat_tipo_usuario!=1 and status='A'");
		return $query->result();
	}

	function obtener_menu(){
		$query = $this->db->list_fields('privilegios');
		return $query;
	}

	function menus_asignados($pk_cat_personal){
		$this->db->where('fk_cat_personal',$pk_cat_personal);
		return $this->db->get('privilegios')->row();
		return $query;
	}

	function permisos($pk_cat_personal){
		$query = $this->db->query("SELECT editar FROM cat_personal WHERE pk_cat_personal=".$pk_cat_personal);
		return $query->result();
	}

	function insert_privilegios($registro){
		//$this->db->set($registro);
		$this->db->insert('privilegios',$registro);
	}

	function update_privilegios($registro){
		$this->db->set($registro);
		$this->db->where('fk_cat_personal',$registro['fk_cat_personal']);
		$this->db->update('privilegios');
	}

	function update_permisos($registro){
		$this->db->set($registro);
		$this->db->where('pk_cat_personal',$registro['pk_cat_personal']);
		$this->db->update('cat_personal');
	}

	/*vista principal*/
	//Actividades
	function v_actividades($pk_cat_personal){
		$query = $this->db->query("SELECT *,DATEDIFF(fecha_actividad,NOW()) as dif 
									FROM v_actividades 
									WHERE realizada='NO' and fk_cat_personal=".$pk_cat_personal."
									ORDER BY status,actividad,nombre;");
		return $query->result_array();
	}

	function mostrar_info_act($pk_cat_personal,$com,$having){
		$query = $this->db->query("SELECT *,DATEDIFF(fecha_actividad,NOW()) as dif
									FROM v_actividades 
									WHERE realizada='NO' and fk_cat_personal=".$pk_cat_personal." and ".$com." 
									HAVING ".$having." 
									ORDER BY status,actividad,nombre;");
		return $query->result();
	}
	//Status de Venta
	function ciclos_prospecto($pk_cat_personal){
		$query = $this->db->query("SELECT * FROM u_prospecto_ciclo WHERE fk_cat_personal=".$pk_cat_personal." and status='A';");
		return $query->result_array();
	}

	function calientes($pk_cat_personal){
		$query = $this->db->query("SELECT * FROM cotizacion WHERE fk_cat_personal=".$pk_cat_personal." and status='A' and tipo='DE VENTA' GROUP BY fk_u_prospecto_ciclo;");
		return $query->num_rows();
	}

	function tibias($pk_cat_personal){//SOLO VAMOS A OBTENER EL NUMERO DE FILAS
		$query = $this->db->query("SELECT COUNT(*) as num FROM u_prospecto_ciclo WHERE status='A' and fk_cat_personal=".$pk_cat_personal." and 
									pk_u_prospecto_ciclo NOT IN (SELECT fk_u_prospecto_ciclo FROM cotizacion WHERE status='A')
									GROUP BY fk_prospecto HAVING num > 1
									UNION ALL
									SELECT COUNT(*) FROM cotizacion WHERE fk_cat_personal=".$pk_cat_personal." and status='A' and tipo='INFORMATIVA' GROUP BY fk_u_prospecto_ciclo;");
		return $query->num_rows();
	}

	function frias($pk_cat_personal){
		$query = $this->db->query("SELECT COUNT(*) as tot
									FROM u_prospecto_ciclo u, (SELECT *,COUNT(*) as num 
																FROM u_prospecto_ciclo 
																WHERE status='A' and fk_cat_personal=".$pk_cat_personal."
																GROUP BY fk_prospecto
																HAVING num = 1) as tbl 
									WHERE u.pk_u_prospecto_ciclo=tbl.pk_u_prospecto_ciclo and u.status='A' and u.concluido='NO' and 
									u.pk_u_prospecto_ciclo NOT IN (SELECT fk_u_prospecto_ciclo FROM cotizacion WHERE status='A');");
		return $query->result();
	}

}