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;
}
