<?php

Class BlkPersonal
{

  private $b_blk1=array();
  private $b_blk2=array();
  private $b_blk3=array();

  public function buscar_detpersonal_activo($con,$idj,$ids,$idtp,$idtc,$nom)
  {
      $cad='';

      if($nom!=''){
        $cad = $cad." and (a.ua_apepat like '%$nom%' or  a.ua_apemat like '%$nom%' or a.ua_nom like '%$nom%') ";
      }else{
            if($idj>0){
              $cad = $cad." and b.j_id='$idj' ";
            }

            if($ids>0){
              $cad = $cad." and b.s_id='$ids' ";
            }

            if($idtp>0){
              $cad = $cad." and b.tp_id='$idtp' ";
            }

            if($idtc>0){
              $cad = $cad." and b.tc_idcont='$idtc' ";
            }
        }

      $sql = "select a.ug_id as cod, concat(a.ua_apepat,' ',a.ua_apemat,' ',a.ua_nom) as nombre, (select s_abrev from tb_servicio where s_id=b.s_id and j_id=b.j_id ) as servicio,(select esp_desc from tb_especialidad ";
      $sql = $sql." where ua_idespec=b.ua_idespec) as especialidad, date_format(b.ud_fechaing,'%d/%m/%Y') as fingreso, (datediff(now(), b.ud_fechaing)) div 365 as tiempo, (select tc_abrev from tb_tipocont ";
      $sql = $sql." where tc_idcont=b.tc_idcont) as tipocontrato, (select tp_desc from tb_tipoperson where tp_id=b.tp_id) as tpersona from tb_usuasist a inner join tb_usudep b on ";
      $sql = $sql." a.ug_id = b.ug_id where b.ua_estado in (1,2,4) ".$cad." order by nombre, especialidad";
      //echo $sql;
      $res=mysqli_query($con, $sql);
      while ($reg=mysqli_fetch_assoc($res))
      {
        $this->b_blk1[]=$reg;
      }
        return $this->b_blk1;
  }


  public function buscar_contratopersonal($con,$id)
  {
       $sql = "select a.pz_id as idpz, ug_id as idug, uc_cod as codp,";
       $sql = $sql."date_format(a.uc_fechaing,'%d/%m/%Y') as fechaini, date_format(a.uc_fecharot,'%d/%m/%Y') as fechafin,";
       $sql = $sql."(case when a.uc_fechaing = a.uc_fecharot then (datediff(now(), a.uc_fechaing)) div 365 when a.uc_fechaing <> a.uc_fecharot then (datediff(a.uc_fecharot, a.uc_fechaing)) div 365 end) as tiempo, a.uc_obs as obs, a.uc_estado as estado,";
       $sql = $sql."b.j_id as idj, (select j_desc from tb_jefatura where j_id = b.j_id) as jefatura, ";
       $sql = $sql."b.s_id as ids, (select s_desc from tb_servicio where j_id = b.j_id and s_id = b.s_id) as servicio, ";
       $sql = $sql."b.tp_id as idtp, (select tp_desc from tb_tipoperson where tp_id = b.tp_id) as tpersonal, ";
       $sql = $sql."b.c_id  as idc, (select c_desc from tb_cargo where c_id = b.c_id) as cargo, ";
       $sql = $sql."b.tc_id as idtc, (select tc_desccont from tb_tipocont where tc_idcont = b.tc_id) as tcontrato, a.uc_numplanilla as planilla ";
       $sql = $sql."from tb_usucontrato a inner join tb_plaza b on a.pz_id=b.pz_id where a.ug_id='$id' order by a.uc_estado, a.uc_fecharot asc";
         //echo $sql;
      $res=mysqli_query($con, $sql);
      while ($reg=mysqli_fetch_assoc($res))
      {
        $this->b_blk2[]=$reg;
      }
        return $this->b_blk2;
  }

  public function buscar_grupoocupacionalxidj($con,$idj)
  {
       $sql = "select distinct(a.tp_id) as id,
       (select tp_desc from tb_tipoperson where tp_id=a.tp_id) as nom
       from tb_areatipopersona a where a.j_id='$idj'";
         //echo $sql;
      $res=mysqli_query($con, $sql);
      while ($reg=mysqli_fetch_assoc($res))
      {
        $this->b_blk3[]=$reg;
      }
        return $this->b_blk3;
  }

  public function buscar_tipocontratoxidj($con,$idj)
  {
       $sql = "select distinct(a.tp_id) as id,
       (select tp_desc from tb_tipoperson where tp_id=a.tp_id) as nom
       from tb_areatipopersona a where a.j_id='$idj'";
         //echo $sql;
      $res=mysqli_query($con, $sql);
      while ($reg=mysqli_fetch_assoc($res))
      {
        $this->b_blk3[]=$reg;
      }
        return $this->b_blk3;
  }


  public function buscar_regvacaciones($con,$idj,$ids,$idtp,$est,$anno,$mes)
  {
      $cad='';

            if($idj>0){
              $cad = $cad." and c.j_id='$idj' ";
            }

            if($ids>0){
              $cad = $cad." and c.s_id='$ids' ";
            }

            if($idtp>0){
              $cad = $cad." and c.tp_id='$idtp' ";
            }

        $sql = " Select b.ua_numplanilla as nplan, concat(b.ua_apepat,' ',b.ua_apemat,' ',b.ua_nom) as nom,	";
      	$sql = $sql." (select s_abrev from tb_servicio where s_id=c.s_id and j_id=c.j_id) as servicio, (select tp_desc from tb_tipoperson where tp_id=c.tp_id) as tipo_personal, ";
      	$sql = $sql." a.v_periodo as per, a.v_estado as est, date_format(a.v_fechaini,'%d/%m/%Y') as fini, date_format(a.v_fechafin,'%d/%m/%Y') as ffin, concat(date_format(a.v_fechaini,'%d/%m/%Y'),' - ', date_format(a.v_fechafin,'%d/%m/%Y')) as fecha, a.v_numdias as ndias ";
      	$sql = $sql." FROM tb_vacaciones a inner join tb_usuasist b on a.ug_id= b.ug_id inner join tb_usudep c on b.ug_id=c.ug_id where a.v_estado='$est' and (month(a.v_fechaini)='$mes' or ";
      	$sql = $sql." month(a.v_fechafin)='$mes') and (year(a.v_fechaini)='$anno' or year(a.v_fechafin)='$anno') and c.ua_estado='1' ".$cad." order by tipo_personal, nom";
        //echo $sql;
        $res=mysqli_query($con, $sql);
        while ($reg=mysqli_fetch_assoc($res))
        {
          $this->b_blk1[]=$reg;
        }
          return $this->b_blk1;
  }

}

 ?>
