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