function get_articulo73()
{
$sql = "select sigla,descripcion from unidad_acad ";
$sql = toba::perfil_de_datos()->filtrar($sql);
$perfil = toba::db('designa')->consultar($sql);
if (count($perfil) > 0) {
$ua = $perfil[0]['sigla'];
//veo cuales son los docentes son interinos vigentes de esta facultad
$sql = " SELECT distinct a.legajo" . " from docente a, designacion b" . " where a.id_docente=b.id_docente" . " and b.desde <= '2016-09-30' and (b.hasta >= '2016-06-01' or b.hasta is null)\r\n and ((b.carac='I' and b.cat_estat<>'AYS' and b.cat_estat<>'PTR' and b.cat_estat<>'PAS') or (b.carac='R' and b.cat_estat='ASDEnc' ))\r\n and b.uni_acad='" . $ua . "'";
$legajos = toba::db('designa')->consultar($sql);
if (count($legajos) > 0) {
//si hay docentes
$doc = array();
foreach ($legajos as $value) {
$leg[] = $value['legajo'];
}
$conjunto = implode(",", $leg);
//recupero de mapuche la antiguedad de los legajos que van como argumento
$datos_mapuche = consultas_mapuche::get_antiguedad_docente($conjunto);
if (count($datos_mapuche) > 0) {
$sql = " CREATE LOCAL TEMP TABLE auxi(\r\n nro_legaj integer,\r\n antiguedad integer\r\n );";
toba::db('designa')->consultar($sql);
//creo la tabla auxi
foreach ($datos_mapuche as $valor) {
$sql = " insert into auxi values (" . $valor['nro_legaj'] . "," . $valor['antig'] . ")";
toba::db('designa')->consultar($sql);
}
$sql = "SELECT a.*,b.antiguedad from (" . " SELECT distinct a.legajo,b.id_designacion,a.apellido||', '||a.nombre||'('||b.cat_estat||b.dedic||'-'||b.id_designacion||')' as descripcion " . " from docente a, designacion b,mocovi_costo_categoria c, imputacion d, mocovi_programa e" . " where a.id_docente=b.id_docente" . " and b.desde <= '2016-09-30' and (b.hasta >= '2016-06-01' or b.hasta is null)\r\n and (b.carac='I' and (b.cat_estat<>'AYS' and b.cat_estat<>'PTR' and b.cat_estat<>'PAS')\r\n or\r\n (b.carac='R' and b.cat_estat='ASDEnc')\r\n )\r\n \r\n and c.codigo_siu=b.cat_mapuche\r\n and c.id_periodo=2--periodo 2016\r\n and c.costo_diario<=751.13\r\n and b.uni_acad='" . $ua . "'" . " and b.id_designacion=d.id_designacion" . " and e.id_programa=d.id_programa" . " and e.id_tipo_programa=1 " . ") a INNER JOIN auxi b " . " ON (a.legajo=b.nro_legaj)" . " order by descripcion";
//and c.id_periodo=2--periodo 2016
//c.costo_diario<=751,12 --costo de PAD1=ADJE
$res = toba::db('designa')->consultar($sql);
return $res;
}
}
}
}
function get_listado_sin_legajo($where = null)
{
if (!is_null($where)) {
$where = ' and ' . $where;
} else {
$where = '';
}
//veo cuales son los docentes que tienen legajo 0
$sql = " SELECT distinct a.nro_docum " . " from docente a, designacion b" . " where a.id_docente=b.id_docente" . $where . " and a.legajo=0";
$documentos = toba::db('designa')->consultar($sql);
if (count($documentos) > 0) {
//si hay docentes sin legajo
$doc = array();
foreach ($documentos as $value) {
$doc[] = $value['nro_docum'];
}
$conjunto = implode(",", $doc);
//recupero de mapuche los datos de las personas con documento x
$datos_mapuche = consultas_mapuche::get_dh01($conjunto);
if (count($datos_mapuche) > 0) {
$sql = " CREATE LOCAL TEMP TABLE auxi(\n nro_legaj integer,\n desc_appat character(20),\n desc_nombr character(20),\n tipo_doc character(4),\n nro_doc integer, \n nro_cuil3 integer,\n nro_cuil4 integer,\n nro_cuil5 integer,\n sexo character(1),\n nacim date\n );";
toba::db('designa')->consultar($sql);
foreach ($datos_mapuche as $valor) {
$sql = " insert into auxi values (" . $valor['nro_legaj'] . ",'" . str_replace('\'', '', $valor['desc_appat']) . "','" . str_replace('\'', '', $valor['desc_nombr']) . "','" . $valor['tipo_docum'] . "'," . $valor['nro_docum'] . "," . $valor['nro_cuil1'] . "," . $valor['nro_cuil'] . "," . $valor['nro_cuil2'] . ",'" . $valor['tipo_sexo'] . "','" . $valor['fec_nacim'] . "')";
toba::db('designa')->consultar($sql);
}
$sql = "SELECT * from (" . " SELECT distinct a.id_docente,a.legajo,a.apellido,a.nombre,a.tipo_docum,a.nro_docum ,tipo_sexo,a.fec_nacim " . " from docente a, designacion b" . " where a.id_docente=b.id_docente" . $where . " and a.legajo=0) a INNER JOIN auxi b " . " ON (a.nro_docum=b.nro_doc)";
return toba::db('designa')->consultar($sql);
} else {
//no encontro nada en mapuche
return array();
//retorna arreglo vacio
}
} else {
//no hay docentes sin legajo
return array();
}
}
function get_comparacion($filtro)
{
//print_r($filtro);exit();// Array ( [uni_acad] => FAIF [anio] => 2016 )
$salida = array();
$pdia = dt_mocovi_periodo_presupuestario::primer_dia_periodo_anio($filtro['anio']);
$udia = dt_mocovi_periodo_presupuestario::ultimo_dia_periodo_anio($filtro['anio']);
$where2 = "";
if (isset($filtro['tipo'])) {
switch ($filtro['tipo']) {
case 1:
$where2 = " where id_designacion=-1 and chkstopliq=0 and lic='NO'";
break;
case 2:
$where2 = " where nro_cargo is null";
break;
case 3:
$where2 = " where id_designacion<>-1 and nro_cargo is not null";
break;
}
}
$ua = $filtro['uni_acad'];
if ($filtro['uni_acad'] == 'ESCM') {
$filtro['uni_acad'] = 'IBMP';
}
//recupero los cargos de mapuche de ese periodo y esa ua
$datos_mapuche = consultas_mapuche::get_cargos($filtro['uni_acad'], $udia, $pdia);
$sql = " CREATE LOCAL TEMP TABLE auxi\n ( id_desig integer,\n chkstopliq integer,\n ua character(5),\n nro_legaj integer,\n ape character varying(100),\n nom character varying(100),\n nro_cargo integer,\n codc_categ character varying(4),\n caracter character varying(4),\n fec_alta date,\n fec_baja date,\n lic text\n );";
toba::db('designa')->consultar($sql);
foreach ($datos_mapuche as $valor) {
if (isset($valor['fec_baja'])) {
$concat = "'" . $valor['fec_baja'] . "'";
} else {
$concat = "null";
}
$sql = " insert into auxi values (null," . $valor['chkstopliq'] . ",'" . $ua . "'," . $valor['nro_legaj'] . ",'" . str_replace('\'', '', $valor['desc_appat']) . "','" . $valor['desc_nombr'] . "'," . $valor['nro_cargo'] . ",'" . $valor['codc_categ'] . "','" . $valor['codc_carac'] . "','" . $valor['fec_alta'] . "'," . $concat . ",'" . $valor['lic'] . "')";
toba::db('designa')->consultar($sql);
}
//------------------------------------------------------
$where = '';
if (isset($filtro['uni_acad'])) {
$where = " and t_d.uni_acad='" . $filtro['uni_acad'] . "'";
}
$sql = "select * from( select distinct a.id_designacion,a.uni_acad,a.apellido,a.nombre,a.legajo,a.check_presup,a.cat_mapuche,a.carac,b.caracter,a.desde,a.hasta,b.fec_alta,b.fec_baja,b.nro_cargo,b.chkstopliq,b.lic,a.licd from " . "(select a.*,case when c.id_novedad is null then 'NO' else 'SI' end as licd from (select t_d.id_designacion,t_d.uni_acad,t_do.apellido,t_do.nombre,t_do.legajo,t_d.cat_mapuche,t_d.cat_estat,t_d.dedic,case when t_d.carac='R' then 'ORDI' else 'INTE' end as carac, t_d.desde,t_d.hasta,t_d.check_presup" . " from designacion t_d, docente t_do\n where t_d.desde <= '" . $udia . "' and (t_d.hasta >= '" . $pdia . "' or t_d.hasta is null)\n and t_d.id_docente=t_do.id_docente" . $where . ")a " . " LEFT OUTER JOIN novedad c\n\t\t\t\t\t\t\tON(a.id_designacion=c.id_designacion\n\t\t\t\t\t\t\tand c.tipo_nov in(2,4,5)\n\t\t\t\t\t\t\tand c.desde <= '" . $udia . "' and (c.hasta >= '" . $pdia . "' or c.hasta is null)\n\t\t\t\t\t\t\t)" . ")a" . " LEFT OUTER JOIN auxi b ON (a.cat_mapuche=b.codc_categ\n and a.legajo=b.nro_legaj\n and a.uni_acad=b.ua\n and b.fec_alta <= '" . $udia . "' and (b.fec_baja >= '" . $pdia . "' or b.fec_baja is null)\n )" . " UNION " . "select '-1' as id_desig,ua,ape,nom,nro_legaj,null,codc_categ,null as check_presup,caracter,null,null,fec_alta,fec_baja,nro_cargo,chkstopliq,lic,null" . " from auxi b " . " where\n not exists (select * from designacion c, docente d\n where \n c.id_docente=d.id_docente\n and d.legajo=b.nro_legaj\n and c.uni_acad=b.ua \n and c.cat_mapuche=b.codc_categ\n ) " . " order by uni_acad,apellido,nombre,id_designacion,nro_cargo) d {$where2}";
$resul = toba::db('designa')->consultar($sql);
return $resul;
}
