$result=$_SESSION['db']->execute("SELECT SUM(re_evaso_bw_nilde)+SUM(re_evaso_bw_posta)+SUM(re_evaso_bw_fax)+SUM(re_evaso_bw_altro)+SUM(re_evaso_bw_ariel)+ SUM(re_inevaso_bw_riferr)+SUM(re_inevaso_bw_nonposs)+SUM(re_inevaso_bw_nondisp)+SUM(re_inevaso_bw_over15)+SUM(re_inevaso_bw_overquota)+SUM(re_inevaso_bw_altro) as tot_rich,re_anno FROM report_biblio WHERE re_anno>=2011 GROUP BY re_anno ORDER BY re_anno ASC");
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$result=$_SESSION['db']->execute("SELECT AVG(re_tempo_risp_bw)/86400 as tasso,re_anno FROM report_biblio WHERE re_anno>=2011 GROUP BY re_anno ORDER BY re_anno ASC");
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$result=$_SESSION['db']->execute("SELECT COUNT(bi_id) AS tot,t_tipoente.tipoente AS ente FROM `biblio` LEFT JOIN ente ON biblio.en_id=ente.en_id LEFT JOIN tipoente ON ente.ti_id=tipoente.ti_id LEFT join t_tipoente ON t_tipoente.ti_id=tipoente.ti_id AND lang_id='".$_SESSION['lang']."' WHERE bi_nilde=1 AND bi_stato IN (".getConstVar('BIBLIO_ATTIVE_SQL_LIST').") GROUP BY ente");
$res_acnp=$_SESSION['db']->execute("SELECT COUNT( tab_acnp.bi_id ) AS tot
FROM tab_acnp, biblio_catalogo, biblio
WHERE biblio_catalogo.bi_id = tab_acnp.bi_id
AND biblio_catalogo.ca_id =1
AND bc_attivo =1
AND biblio.bi_id = biblio_catalogo.bi_id
AND bi_nilde =1
AND biblio.na_id = 'ITA'
AND acnp_attivo IN (0,1,2)
AND bi_stato
IN ( ".getConstVar('BIBLIO_ATTIVE_SQL_LIST').")");
$res_acnp_attivo=$_SESSION['db']->execute("SELECT COUNT( tab_acnp.bi_id ) AS tot
FROM tab_acnp, biblio_catalogo, biblio
WHERE biblio_catalogo.bi_id = tab_acnp.bi_id
AND biblio_catalogo.ca_id =1
AND bc_attivo =1
AND biblio.bi_id = biblio_catalogo.bi_id
AND bi_nilde =1
AND biblio.na_id = 'ITA'
AND acnp_attivo=1
AND bi_stato
IN ( ".getConstVar('BIBLIO_ATTIVE_SQL_LIST').")");
$res_mai=$_SESSION['db']->execute("SELECT COUNT(bi_id) AS tot FROM `biblio_catalogo` LEFT JOIN catalogo ON catalogo.ca_id=biblio_catalogo.ca_id WHERE bc_attivo=1 AND biblio_catalogo.ca_id=3 AND bi_id IN (SELECT bi_id from biblio WHERE bi_nilde=1 AND bi_stato IN (".getConstVar('BIBLIO_ATTIVE_SQL_LIST').") )");
$res_sbn=$_SESSION['db']->execute("SELECT COUNT(bi_id) AS tot FROM `biblio_catalogo` LEFT JOIN catalogo ON catalogo.ca_id=biblio_catalogo.ca_id WHERE bc_attivo=1 AND biblio_catalogo.ca_id=2 AND bi_id IN (SELECT bi_id from biblio WHERE bi_nilde=1 AND bi_stato IN (".getConstVar('BIBLIO_ATTIVE_SQL_LIST').") )");
$res_rebiun=$_SESSION['db']->execute("SELECT COUNT(bi_id) AS tot FROM `biblio_catalogo` LEFT JOIN catalogo ON catalogo.ca_id=biblio_catalogo.ca_id WHERE bc_attivo=1 AND biblio_catalogo.ca_id=5 AND bi_id IN (SELECT bi_id from biblio WHERE bi_nilde=1 AND bi_stato IN (".getConstVar('BIBLIO_ATTIVE_SQL_LIST').") )");
$res_altre=$_SESSION['db']->execute("SELECT COUNT(bi_id) AS tot FROM biblio WHERE bi_nilde=1 AND bi_stato IN (".getConstVar('BIBLIO_ATTIVE_SQL_LIST').") AND bi_id not IN (SELECT distinct(bi_id) from biblio_catalogo WHERE bc_attivo=1)");
$cat_res=$_SESSION['db']->execute("SELECT ca_id,ca_nome from catalogo");
while($c=$_SESSION['db']->fetch($cat_res)){
$key=$c["ca_id"];
$v=$c["ca_nome"];
$ca_array[$key]=$v;
}
$data=array();
$data[]=$tot_acnp;
$data[]=$tot_sbn;
$data[]=$tot_mai;
$data[]=$tot_rebiun;
$data[]=$tot_altre;
$assex[]=$ca_array['1'];
$assex[]=$ca_array['2'];
$assex[]=$ca_array['3'];
$assex[]=$ca_array['5'];
getConstVar("REBIUN");
$assex[]=getConstVar("OTHERS");
$graph=newGraph(500,300);
$graph->img->SetAntiAliasing();
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$result=$_SESSION['db']->execute("SELECT t_disciplina.disciplina AS nome, COUNT(biblio.di_id) AS tot FROM biblio,disciplina,t_disciplina WHERE disciplina.di_id=t_disciplina.di_id AND t_disciplina.lang_id='".$_SESSION["lang"]."' AND biblio.di_id=disciplina.di_id AND bi_nilde=1 AND bi_stato IN (".getConstVar('BIBLIO_ATTIVE_SQL_LIST').") GROUP BY nome");
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$result=$_SESSION['db']->execute("SELECT count(bi_id) as tot, biblio.na_id,na_nome from biblio,nazione where biblio.na_id=nazione.na_id AND bi_nilde=1 AND bi_stato IN (".getConstVar('BIBLIO_ATTIVE_SQL_LIST').") group by biblio.na_id");
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$result=$_SESSION['db']->execute("SELECT COUNT(ut_id) as tot, TE.tipoente as nometipo FROM appartiene as APP, biblio AS B, ente AS E, tipoente AS T,t_tipoente AS TE WHERE TE.ti_id=T.ti_id AND TE.lang_id='".$_SESSION['lang']."' AND APP.ap_stato=1 AND APP.bi_id=B.bi_id AND B.en_id=E.en_id AND E.ti_id=T.ti_id AND B.bi_id IN (SELECT bi_id from biblio WHERE bi_nilde=1 AND bi_stato IN (".getConstVar('BIBLIO_ATTIVE_SQL_LIST').") ) GROUP BY TE.tipoente");
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$result=$_SESSION['db']->execute("SELECT COUNT(ut_id) as tot,TD.disciplina as discip FROM appartiene as APP, biblio AS B, disciplina D,t_disciplina TD WHERE TD.di_id=D.di_id AND TD.lang_id='".$_SESSION['lang']."' AND APP.ap_stato=1 AND APP.bi_id=B.bi_id AND B.di_id=D.di_id AND B.bi_id IN (SELECT bi_id from biblio WHERE bi_nilde=1 AND bi_stato IN (".getConstVar('BIBLIO_ATTIVE_SQL_LIST').") ) GROUP BY TD.disciplina");
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$result=$_SESSION['db']->execute("SELECT SUM(re_evase_dir)+SUM(re_evase_nilde)+SUM(re_inevase_nilde)+SUM(re_inevase_dir) as borrowing, SUM(re_evase_dir)+SUM(re_evase_nilde) as lending, re_anno FROM report_utente WHERE bi_id=".$_SESSION['biblio']->bi_id." GROUP BY re_anno HAVING (borrowing > 0 OR lending > 0 )ORDER BY re_anno ASC ");
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$_SESSION['db']->execute("CREATE VIEW $nameview AS SELECT ut_id,SUM(re_richieste) as num_rich, re_anno FROM report_utente WHERE bi_id=".$_SESSION['biblio']->bi_id." GROUP BY ut_id ");
$result1=$_SESSION['db']->execute("SELECT AVG(num_rich) AS media, re_anno FROM $nameview GROUP BY re_anno ");
$_SESSION['db']->execute("DROP VIEW IF EXISTS $nameview");
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$view=$_SESSION['db']->execute("CREATE VIEW $nameview AS SELECT ut_id,SUM(re_richieste) as re_richieste,re_anno FROM `report_utente` WHERE bi_id=".$_SESSION['biblio']->bi_id." GROUP BY ut_id");
$result=$_SESSION['db']->execute("SELECT re_anno,SUM(CASE when re_richieste <=2 THEN 1 ELSE 0 END) as gruppo1,SUM(CASE when re_richieste >2 AND re_richieste <5 THEN 1 ELSE 0 END) as gruppo2,SUM(CASE when re_richieste >=5 AND re_richieste <=10 THEN 1 ELSE 0 END) as gruppo3,SUM(CASE when re_richieste >10 AND re_richieste <=20 THEN 1 ELSE 0 END) as gruppo4,SUM(CASE when re_richieste >20 AND re_richieste <=50 THEN 1 ELSE 0 END) as gruppo5,SUM(CASE when re_richieste > 50 AND re_richieste <=100 THEN 1 ELSE 0 END) as gruppo6,SUM(CASE when re_richieste >100 THEN 1 ELSE 0 END) as gruppo7 FROM $nameview GROUP by re_anno order by re_anno ASC");
$data=array();
$gruppo1=array();
$gruppo2=array();
$gruppo3=array();
$gruppo4=array();
$gruppo5=array();
$gruppo6=array();
$gruppo7=array();
$assex=array();
$_SESSION['db']->execute("DROP VIEW IF EXISTS $nameview");
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$result=$_SESSION['db']->execute("SELECT YEAR(rm_datarichie) as re_anno, IFNULL(ROUND(AVG(UNIX_TIMESTAMP(STR_TO_DATE(dd_lastmodify, '%Y-%m-%d %H:%i:%s')) - UNIX_TIMESTAMP(STR_TO_DATE(rm_datarichie, '%Y-%m-%d %H:%i:%s')))/86400,3),0) as tempo_medio_L from docdel GROUP BY re_anno");
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$result=$_SESSION['db']->execute("SELECT SUM(re_richieste_bw) as num_rich, SUM(re_evaso_bw_nilde)+SUM(re_evaso_bw_posta)+SUM(re_evaso_bw_fax)+SUM(re_evaso_bw_altro)+SUM(re_evaso_bw_ariel) as articoli_richiesti, SUM(re_richieste_ln) as rich_ln, SUM(re_evaso_ln_nilde)+SUM(re_evaso_ln_posta)+SUM(re_evaso_ln_fax)+SUM(re_evaso_ln_altro)+SUM(re_evaso_ln_ariel) AS articoli_forniti,SUM(re_inevaso_bw_riferr)+SUM(re_inevaso_bw_nonposs)+SUM(re_inevaso_bw_nondisp)+SUM(re_inevaso_bw_over15)+SUM(re_inevaso_bw_overquota)+SUM(re_inevaso_bw_altro) as inevasi_bw,SUM(re_inevaso_ln_riferr)+SUM(re_inevaso_ln_nonposs)+SUM(re_inevaso_ln_nondisp)+SUM(re_inevaso_ln_over15)+SUM(re_inevaso_ln_overquota)+SUM(re_inevaso_ln_altro) as inevasi_ln FROM report_biblio WHERE bi_idrichie=".$_SESSION['biblio']->bi_id." AND re_anno = $year GROUP BY re_anno");
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$result=$_SESSION['db']->execute("SELECT (SUM(re_evaso_bw_nilde)+SUM(re_evaso_bw_posta)+SUM(re_evaso_bw_fax)+SUM(re_evaso_bw_altro)+SUM(re_evaso_bw_ariel))/( SUM(re_evaso_bw_nilde)+SUM(re_evaso_bw_posta)+SUM(re_evaso_bw_fax)+SUM(re_evaso_bw_altro)+SUM(re_evaso_bw_ariel)+SUM(re_inevaso_bw_riferr)+SUM(re_inevaso_bw_nonposs)+SUM(re_inevaso_bw_nondisp)+SUM(re_inevaso_bw_over15)+SUM(re_inevaso_bw_overquota)+SUM(re_inevaso_bw_altro)) as tasso_successo_bw, (SUM(re_evaso_ln_nilde)+SUM(re_evaso_ln_posta)+SUM(re_evaso_ln_fax)+SUM(re_evaso_ln_altro)+SUM(re_evaso_ln_ariel))/( SUM(re_evaso_ln_nilde)+SUM(re_evaso_ln_posta)+SUM(re_evaso_ln_fax)+SUM(re_evaso_ln_altro)+SUM(re_evaso_ln_ariel)+SUM(re_inevaso_ln_riferr)+SUM(re_inevaso_ln_nonposs)+SUM(re_inevaso_ln_nondisp)+SUM(re_inevaso_ln_over15)+SUM(re_inevaso_ln_overquota)+SUM(re_inevaso_ln_altro)) AS tasso_successo_ln FROM report_biblio WHERE bi_idrichie=".$_SESSION['biblio']->bi_id." AND re_anno = $year GROUP BY re_anno");
$result3=$_SESSION['db']->execute("SELECT SUM(re_tempo_risp_bw)/SUM(re_richieste_bw)/86400 as media_giac_bw,SUM(re_tempo_risp_ln)/SUM(re_richieste_ln)/86400 as media_giac_ln FROM report_biblio where re_anno=$year AND bi_idrichie=".$_SESSION['biblio']->bi_id." GROUP BY re_anno");
$result=$_SESSION['db']->execute("SELECT SUM(re_evaso_bw_nilde) AS Nilde, SUM(re_evaso_bw_posta) AS Posta, SUM(re_evaso_bw_fax) AS Fax, SUM(re_evaso_bw_ariel) AS Ariel, SUM(re_evaso_bw_altro) AS Altro, SUM(re_evaso_bw_fn) AS FN FROM report_biblio WHERE re_anno=$year AND bi_idrichie=".$_SESSION['biblio']->bi_id." GROUP BY re_anno");
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$result=$_SESSION['db']->execute("SELECT SUM(re_inevaso_bw_riferr) as RifErrato, SUM(re_inevaso_bw_nonposs) as NonPoss, SUM(re_inevaso_bw_nondisp) as NonDisp, SUM(re_inevaso_bw_over15) as Over15, SUM(re_inevaso_bw_overquota) as OverQuota, SUM(re_inevaso_bw_altro) as Altro, SUM(re_inevaso_bw_fn) as FN FROM report_biblio WHERE re_anno=$year AND bi_idrichie=".$_SESSION['biblio']->bi_id." GROUP BY re_anno");
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$result=$_SESSION['db']->execute("SELECT SUM(re_evaso_ln_nilde) as Nilde, SUM(re_evaso_ln_posta) as Posta, SUM(re_evaso_ln_fax) as Fax, SUM(re_evaso_ln_ariel) as Ariel, SUM(re_evaso_ln_altro) as Altro,SUM(re_evaso_ln_fn) as FN FROM report_biblio WHERE re_anno=$year AND bi_idrichie=".$_SESSION['biblio']->bi_id." GROUP BY re_anno");
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$result=$_SESSION['db']->execute("SELECT SUM(re_inevaso_ln_riferr) as RifErrato, SUM(re_inevaso_ln_nonposs) as NonPoss, SUM(re_inevaso_ln_nondisp) as NonDisp, SUM(re_inevaso_ln_over15) as Over15, SUM(re_inevaso_ln_overquota) as OverQuota, SUM(re_inevaso_ln_altro) as Altro, SUM(re_inevaso_ln_fn) as FN FROM report_biblio WHERE re_anno=$year AND bi_idrichie=".$_SESSION['biblio']->bi_id." GROUP BY re_anno");
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$result=$_SESSION['db']->execute("SELECT bi_nome, prov_id,rb.bi_idforni, SUM(re_richieste_bw) AS ordini_inviati, SUM(re_richieste_ln) AS ordini_ricevuti, SUM(re_evaso_bw_nilde)+SUM(re_evaso_bw_posta)+SUM(re_evaso_bw_fax)+SUM(re_evaso_bw_ariel)+SUM(re_evaso_bw_altro) AS art_ricevuti,SUM(re_evaso_ln_nilde)+SUM(re_evaso_ln_posta)+SUM(re_evaso_ln_fax)+SUM(re_evaso_ln_ariel)+SUM(re_evaso_ln_altro) AS art_forniti,(SUM(re_evaso_ln_nilde)+SUM(re_evaso_ln_posta)+SUM(re_evaso_ln_fax)+SUM(re_evaso_ln_ariel)+SUM(re_evaso_ln_altro))-(SUM(re_evaso_bw_nilde)+SUM(re_evaso_bw_posta)+SUM(re_evaso_bw_fax)+SUM(re_evaso_bw_ariel)+SUM(re_evaso_bw_altro)) AS diff_forni_ricev,SUM(re_richieste_bw_fn) AS ordini_fn_inviati, SUM(re_richieste_ln_fn) AS ordini_fn_ricevuti, SUM(re_evaso_bw_fn) AS art_fn_ricevuti, SUM(re_evaso_ln_fn) AS art_fn_forniti, (SUM(re_evaso_ln_fn))-(SUM(re_evaso_bw_fn)) AS diff_fn_forni_ricev, SUM(re_costo_bw_fn) AS costo_bw_fn,SUM(re_costo_ln_fn) AS costo_ln_fn FROM report_biblio as rb,biblio WHERE biblio.bi_id=rb.bi_idforni AND rb.bi_idrichie=".$_SESSION['biblio']->bi_id." AND re_anno=$year GROUP BY rb.bi_idforni ORDER BY $ord");
$result=$_SESSION['db']->execute("SELECT SUM(re_richieste_bw) as Richieste_Borrowing, SUM(re_evaso_bw_nilde)+SUM(re_evaso_bw_posta)+SUM(re_evaso_bw_fax)+SUM(re_evaso_bw_altro)+SUM(re_evaso_bw_ariel) as num_articoli_ricev, SUM(re_richieste_ln) as Richieste_Lending, SUM(re_evaso_ln_nilde)+SUM(re_evaso_ln_posta)+SUM(re_evaso_ln_fax)+SUM(re_evaso_ln_altro)+SUM(re_evaso_ln_ariel)+SUM(re_evaso_ln_altro) as num_articoli_forniti, SUM(re_inevaso_bw_riferr)+SUM(re_inevaso_bw_nonposs)+SUM(re_inevaso_bw_nondisp)+SUM(re_inevaso_bw_over15)+SUM(re_inevaso_bw_overquota)+SUM(re_inevaso_bw_altro) as inevasi_bw,SUM(re_inevaso_ln_riferr)+SUM(re_inevaso_ln_nonposs)+SUM(re_inevaso_ln_nondisp)+SUM(re_inevaso_ln_over15)+SUM(re_inevaso_ln_overquota)+SUM(re_inevaso_ln_altro) as inevasi_ln,MONTH(STR_TO_DATE(re_mese,'%m')) as mese FROM report_biblio WHERE bi_idrichie=".$_SESSION['biblio']->bi_id." AND re_anno = $year GROUP BY re_mese ORDER BY re_mese ASC ");
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$result=$_SESSION['db']->execute("SELECT rv_nome as rv, SUM(re_art_ricev) as ricevuti
FROM report_monografia WHERE bi_id=".$_SESSION['biblio']->bi_id." AND re_anno=$year GROUP BY rv_nome HAVING (ricevuti>0) order by $ord1 $q1 LIMIT 50");
$result1=$_SESSION['db']->execute("SELECT rv_nome as rv, SUM(re_art_forni) as forniti
FROM report_monografia WHERE bi_id=".$_SESSION['biblio']->bi_id." AND re_anno=$year GROUP BY rv_nome HAVING (forniti>0) order by $ord2 $q2 LIMIT 50");
$result=$_SESSION['db']->execute("SELECT SUM(re_evase_dir) as Evase_direttamente,SUM(re_inevase_dir) as Inevase_direttamente FROM report_utente WHERE bi_id=".$_SESSION['biblio']->bi_id." AND re_anno=$year");
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$result=$_SESSION['db']->execute("SELECT count(distinct ut_id) as num FROM report_utente WHERE bi_id=".$_SESSION['biblio']->bi_id." AND re_anno=$year AND re_richieste >= 1 ");
$_SESSION['db']->execute("CREATE VIEW $nameview AS SELECT SUM(re_richieste) as num_rich, ut_id FROM report_utente WHERE bi_id=".$_SESSION['biblio']->bi_id." AND re_anno=$year GROUP BY ut_id");
$result1=$_SESSION['db']->execute("SELECT AVG(num_rich) as media, MAX(num_rich) as max FROM $nameview");
$x=$_SESSION['db']->execute("DROP VIEW IF EXISTS $nameview");
$result2=$_SESSION['db']->execute("SELECT SUM(re_richieste) as num_rich FROM report_utente WHERE bi_id=".$_SESSION['biblio']->bi_id." AND re_anno=$year");
$result=$_SESSION['db']->execute("SELECT SUM(re_richieste) as Numero_art_richiesti, ut_dip,dip_nome FROM report_utente LEFT JOIN dipartimento ON report_utente.ut_dip = dipartimento.dip_id WHERE report_utente.bi_id=".$_SESSION['biblio']->bi_id." AND re_anno=$year GROUP BY ut_dip; ");
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$result=$_SESSION['db']->execute("SELECT tq.qualifica as nome_qualifica, SUM( re_richieste ) as NUM_ART_RICHIESTI FROM report_utente AS ru, qualifica AS q,t_qualifica AS tq WHERE q.qu_id=tq.qu_id AND tq.lang_id='".$_SESSION['lang']."' AND ru.qu_id = q.qu_id AND ru.bi_id=".$_SESSION['biblio']->bi_id." AND ru.re_anno=$year GROUP BY ru.qu_id ");
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$result=$_SESSION['db']->execute("SELECT COUNT(distinct ut_id) as Numero_utenti, ut_dip,dip_nome FROM report_utente LEFT JOIN dipartimento ON report_utente.ut_dip = dipartimento.dip_id WHERE report_utente.bi_id=".$_SESSION['biblio']->bi_id." AND re_anno=$year GROUP BY ut_dip; ");
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$result=$_SESSION['db']->execute("SELECT COUNT(distinct ut_id) as Numero_utenti, tq.qualifica as Nome_qualifica FROM report_utente as ru, qualifica as q,t_qualifica AS tq WHERE q.qu_id=tq.qu_id AND tq.lang_id='".$_SESSION['lang']."' AND ru.qu_id=q.qu_id AND ru.bi_id=".$_SESSION['biblio']->bi_id." AND ru.re_anno=$year GROUP BY ru.qu_id; ");
$graph->SetMargin(getConstVar("GRAPH_LEFT_MARGIN"),getConstVar("GRAPH_RIGHT_MARGIN"),getConstVar("GRAPH_TOP_MARGIN"),getConstVar("GRAPH_BOTTOM_MARGIN"));//L R TOP BOTTOM
$result=$_SESSION['db']->execute("SELECT u.ut_nome, u.ut_cognome, ru.ut_id, SUM(ru.re_richieste) AS richieste, SUM(ru.re_evase_dir) AS evase_dir, SUM(ru.re_evase_nilde) AS evase_nilde, (SUM(ru.re_inevase_nilde)+SUM(ru.re_inevase_dir)) AS inevase,re_costo_fn FROM report_utente as ru, utente as u WHERE ru.ut_id=u.ut_id AND ru.bi_id=".$_SESSION['biblio']->bi_id." AND ru.re_anno=$year GROUP BY ru.ut_id order by $ord $qual");
$result=$_SESSION['db']->execute("SELECT ut_dip, SUM(re_costo_fn) AS costo_fn,dip_nome FROM report_utente LEFT join dipartimento ON ut_dip=dip_id WHERE report_utente.bi_id=".$_SESSION['biblio']->bi_id." AND re_anno=$year GROUP BY ut_dip HAVING costo_fn > 0 ORDER by $report_order $report_qualif");
$result=$_SESSION['db']->execute("SELECT ente.en_id,en_nome from storico_sottoscrizioni,ente where st_anno_contratto=$year and storico_sottoscrizioni.en_id=".$_SESSION['biblio']->en_id." AND ente.en_id=storico_sottoscrizioni.en_id");
$result=$_SESSION['db']->execute("SELECT DISTINCT pr_id, pr_nome from progetto where pr_id IN (SELECT pr_id from storico_sottoscrizioni where st_tipo_contratto=4 and st_anno_contratto=$year) and pr_id IN (select pr_id from biblio_progetto WHERE bi_id=".$_SESSION['biblio']->bi_id.")");
case'globale1A':$query="SELECT SUM(re_evaso_bw_nilde)+SUM(re_evaso_bw_posta)+SUM(re_evaso_bw_fax)+SUM(re_evaso_bw_altro)+SUM(re_evaso_bw_ariel) as borrowing, SUM(re_evaso_ln_nilde)+SUM(re_evaso_ln_posta)+SUM(re_evaso_ln_fax)+SUM(re_evaso_ln_altro)+SUM(re_evaso_ln_ariel) AS lending, re_anno
FROM report_biblio WHERE bi_idrichie=".$_SESSION['biblio']->bi_id." GROUP BY re_anno HAVING (borrowing > 0 OR lending > 0 )ORDER BY re_anno ASC";
break;
case"globale1E":$query="SELECT re_anno,IFNULL(ROUND(SUM(re_tempo_risp_bw)/SUM(re_richieste_bw)/86400,2),0) as tempo_medio_B,IFNULL(ROUND(SUM(re_tempo_risp_ln)/SUM(re_richieste_ln)/86400,2),0) as tempo_medio_L FROM report_biblio where bi_idrichie=".$_SESSION['biblio']->bi_id." GROUP BY re_anno";
break;
case"scambi_globali_ente":$query="Select RIF.ri_id AS IDRIF,dd_id AS IDRICHIESTA,RIF.ri_titolopub AS TITOLO,RIF.ri_titolopart AS TITOLO_ARTICOLO_o_CAPITOLO,(CASE RIF.ri_tipomateriale WHEN 1 THEN 'articolo' WHEN 2 THEN 'libro' END) AS TIPO_MATERIALE,ri_anno AS ANNO,RIF.ri_issn AS ISSN,RIF.ri_isbn AS ISBN,
BR.bi_id AS ID_RICHIEDENTE,BR.bi_nome as RICHIEDENTE,(CASE BR.bi_nilde WHEN 1 THEN 'N' WHEN 0 THEN 'FN' END) AS TIPO_RICHIEDENTE,BR.prov_id AS PROV_RICHIEDENTE,ENTER.en_nome AS ENTE_RICHIEDENTE,BF.bi_id AS ID_FORNITORE,BF.bi_nome as FORNITORE,(CASE BF.bi_nilde WHEN 1 THEN 'N' WHEN 0 THEN 'FN' END) AS TIPO_FORNITORE,BF.prov_id AS PROV_FORNITORE,ENTEF.en_nome AS ENTE_FORNITORE,
DD.dd_datarichie AS DATA_RICHIESTA,DD.dd_dataeva AS DATA_EVASIONE,
(CASE DD.dd_stato_ddill WHEN 0 THEN 'nuovo' WHEN 10 THEN 'inoltro NILDE' WHEN 11 THEN 'richiesto annullamento' WHEN 20 THEN 'inoltro FN' WHEN 99 THEN 'elaborazione server' WHEN 101 THEN 'evaso NILDE' WHEN 102 THEN 'evaso NILDE (HC)' WHEN 103 THEN 'evaso NILDE (WM)' WHEN 110 THEN 'evaso POSTA' WHEN 111 THEN 'evaso FAX'WHEN 112 THEN 'evaso ARIEL' WHEN 113 THEN 'evaso ALTRO' WHEN 150 THEN 'evaso FN' WHEN 201 THEN 'inevaso (riferim. errato)' WHEN 202 THEN 'inevaso (non posseduto)' WHEN 203 THEN 'inevaso (non disponibile)' WHEN 204 THEN 'inevaso (over 15)' WHEN 205 THEN 'inevaso (over quota)' WHEN 230 THEN 'inevaso ALTRO' WHEN 250 THEN 'inevaso FN' WHEN 299 THEN 'annullata' END ) AS STATO_DD,
(CASE DD.dd_stato_borr WHEN 0 THEN 'nuovo' WHEN 1 THEN 'in corso' WHEN 30 THEN 'attesa accettazione utente' WHEN 31 THEN 'utente rifiuta costo' WHEN 32 THEN 'utente accetta costo' WHEN 34 THEN 'utente non risponde' WHEN 40 THEN 'in gestione' WHEN 50 THEN 'scarìcato' WHEN 51 THEN 'ricevuto' WHEN 33 THEN 'cestinato' WHEN 70 THEN 'inevaso' WHEN 100 THEN 'consegnato ute/archiviato' WHEN 101 THEN 'consegnato (diretta)' WHEN 200 THEN 'non consegnato/arch come inevaso' WHEN 201 THEN 'non consegnato (diretto)' WHEN 202 THEN 'non consegnato (riferim. errato)' WHEN 203 THEN 'non consegnato (cestinato)' WHEN 300 THEN 'reiterato' WHEN 302 THEN 'reiterato (riferimento errato)' WHEN 303 THEN 'reiterato (cestinato)' END) AS STATO_BORROWING,if(ut_id IS NOT NULL AND ut_id>0,'Si','No') AS RICHIESTA_UTENTE
FROM docdel as DD,biblio as BR, biblio as BF, riferimento AS RIF,ente as ENTER,ente as ENTEF
WHERE DD.bi_idrichie=BR.bi_id AND DD.bi_idforni=BF.bi_id AND DD.ri_id=RIF.ri_id AND Year(dd_datarichie)=$year
AND ENTER.en_id=BR.en_id AND ENTEF.en_id=BF.en_id AND (BR.bi_id IN (SELECT bi_id from biblio WHERE en_id=".$_SESSION['biblio']->en_id." AND bi_nilde=1)
OR BF.bi_id IN (SELECT bi_id from biblio WHERE en_id=".$_SESSION['biblio']->en_id." AND bi_nilde=1) )";
break;
case"richieste_interne_ente":$query="SELECT bi_idrichie AS ID, BR.bi_nome AS BIBLIOTECA, BR.prov_id AS PROV,sum( re_richieste_bw ) AS TOT_RICHIESTE_NILDE, sum( re_richieste_bw_fn ) AS TOT_RICHIESTE_FN , sum( re_evaso_bw_nilde ) AS TOT_RICHIESTE_EVASE_NILDE, sum( re_evaso_bw_posta ) AS TOT_RICHIESTE_EVASE_POSTA , sum( re_evaso_bw_fax ) AS TOT_RICHIESTE_EVASE_FAX, sum( re_evaso_bw_ariel ) AS TOT_RICHIESTE_EVASE_ARIEL, sum( re_evaso_bw_altro ) AS TOT_RICHIESTE_EVASE_ALTRO, sum( re_evaso_bw_fn ) AS TOT_RICHIESTE_EVASE_FN, sum( re_inevaso_bw_riferr ) AS TOT_RICHIESTE_INEVASE_RIF_ERRATO, sum( re_inevaso_bw_nonposs ) AS TOT_RICHIESTE_INEVASE_NON_POSSEDUTO, sum( re_inevaso_bw_nondisp ) AS TOT_RICHIESTE_INEVASE_NON_DISPONIBILE, sum( re_inevaso_bw_over15 ) AS TOT_RICHIESTE_INEVASE_OVER15, sum( re_inevaso_bw_overquota ) AS TOT_RICHIESTE_INEVASE_OVER_QUOTA, sum( re_inevaso_bw_altro ) AS TOT_RICHIESTE_INEVASE_ALTRO, sum( re_inevaso_bw_fn ) AS TOT_RICHIESTE_INEVASE_FN, sum( re_richieste_ln ) AS TOT_FORNITE, sum( re_richieste_ln_fn ) AS TOT_FORNITE_FN , sum( re_evaso_ln_nilde ) AS TOT_FORNITE_EVASE_NILDE , sum( re_evaso_ln_posta ) AS TOT_FORNITE_EVASE_POSTA , sum( re_evaso_ln_fax ) AS TOT_FORNITE_EVASE_FAX, sum( re_evaso_ln_ariel ) AS TOT_FORNITE_EVASE_ARIEL , sum( re_evaso_ln_altro ) AS TOT_FORNITE_EVASE_ALTRO, sum( re_evaso_ln_fn ) AS TOT_FORNITE_EVASE_FN, sum( re_inevaso_ln_riferr ) AS TOT_FORNITE_INEVASE_RIF_ERRATO, sum( re_inevaso_ln_nonposs ) AS TOT_FORNITE_INEVASE_NON_POSSEDUTO, sum( re_inevaso_ln_nondisp ) AS TOT_FORNITE_INEVASE_NON_DISPONIBILE , sum( re_inevaso_ln_over15 ) AS TOT_FORNITE_INEVASE_OVER15, sum( re_inevaso_ln_overquota ) AS TOT_FORNITE_INEVASE_OVER_QUOTA_SETTIMANALE, sum( re_inevaso_ln_altro ) AS TOT_FORNITE_INEVAS_ALTRO, sum( re_inevaso_ln_fn ) AS TOT_FORNITE_INEVASO_FN
FROM report_biblio, biblio AS BR,biblio as BF
WHERE report_biblio.bi_idrichie = BR.bi_id
AND report_biblio.bi_idforni = BF.bi_id
AND BR.en_id=".$_SESSION['biblio']->en_id."
AND BR.bi_nilde =1
AND BF.en_id=".$_SESSION['biblio']->en_id."
AND BF.bi_nilde =1
AND re_anno=$year
GROUP BY bi_idrichie";
break;
case"richieste_ente":$query="SELECT bi_idrichie AS ID, BR.bi_nome AS BIBLIOTECA, BR.prov_id AS PROV,sum( re_richieste_bw ) AS TOT_RICHIESTE_NILDE, sum( re_richieste_bw_fn ) AS TOT_RICHIESTE_FN , sum( re_evaso_bw_nilde ) AS TOT_RICHIESTE_EVASE_NILDE, sum( re_evaso_bw_posta ) AS TOT_RICHIESTE_EVASE_POSTA , sum( re_evaso_bw_fax ) AS TOT_RICHIESTE_EVASE_FAX, sum( re_evaso_bw_ariel ) AS TOT_RICHIESTE_EVASE_ARIEL, sum( re_evaso_bw_altro ) AS TOT_RICHIESTE_EVASE_ALTRO, sum( re_evaso_bw_fn ) AS TOT_RICHIESTE_EVASE_FN, sum( re_inevaso_bw_riferr ) AS TOT_RICHIESTE_INEVASE_RIF_ERRATO, sum( re_inevaso_bw_nonposs ) AS TOT_RICHIESTE_INEVASE_NON_POSSEDUTO, sum( re_inevaso_bw_nondisp ) AS TOT_RICHIESTE_INEVASE_NON_DISPONIBILE, sum( re_inevaso_bw_over15 ) AS TOT_RICHIESTE_INEVASE_OVER15, sum( re_inevaso_bw_overquota ) AS TOT_RICHIESTE_INEVASE_OVER_QUOTA, sum( re_inevaso_bw_altro ) AS TOT_RICHIESTE_INEVASE_ALTRO, sum( re_inevaso_bw_fn ) AS TOT_RICHIESTE_INEVASE_FN, sum( re_richieste_ln ) AS TOT_FORNITE, sum( re_richieste_ln_fn ) AS TOT_FORNITE_FN , sum( re_evaso_ln_nilde ) AS TOT_FORNITE_EVASE_NILDE , sum( re_evaso_ln_posta ) AS TOT_FORNITE_EVASE_POSTA , sum( re_evaso_ln_fax ) AS TOT_FORNITE_EVASE_FAX, sum( re_evaso_ln_ariel ) AS TOT_FORNITE_EVASE_ARIEL , sum( re_evaso_ln_altro ) AS TOT_FORNITE_EVASE_ALTRO, sum( re_evaso_ln_fn ) AS TOT_FORNITE_EVASE_FN, sum( re_inevaso_ln_riferr ) AS TOT_FORNITE_INEVASE_RIF_ERRATO, sum( re_inevaso_ln_nonposs ) AS TOT_FORNITE_INEVASE_NON_POSSEDUTO, sum( re_inevaso_ln_nondisp ) AS TOT_FORNITE_INEVASE_NON_DISPONIBILE , sum( re_inevaso_ln_over15 ) AS TOT_FORNITE_INEVASE_OVER15, sum( re_inevaso_ln_overquota ) AS TOT_FORNITE_INEVASE_OVER_QUOTA_SETTIMANALE, sum( re_inevaso_ln_altro ) AS TOT_FORNITE_INEVAS_ALTRO, sum( re_inevaso_ln_fn ) AS TOT_FORNITE_INEVASO_FN
FROM report_biblio, biblio AS BR
WHERE report_biblio.bi_idrichie = BR.bi_id
AND BR.en_id=".$_SESSION['biblio']->en_id."
AND BR.bi_nilde =1
AND re_anno=$year
GROUP BY bi_idrichie";
break;
case"riviste_richieste_ente":$query="select RIF.ri_titolopub AS TITOLO,(CASE RIF.ri_tipomateriale WHEN 1 THEN 'articolo' WHEN 2 THEN 'libro' END) AS TIPO_MATERIALE,RIF.ri_issn AS ISSN,RIF.ri_isbn AS ISBN,COUNT(RIF.ri_id) as TOTALE
FROM docdel as DD,biblio as BR, riferimento AS RIF
WHERE
DD.bi_idrichie=BR.bi_id
AND DD.ri_id=RIF.ri_id
AND BR.en_id=".$_SESSION['biblio']->en_id."
AND BR.bi_nilde=1
AND Year(dd_datarichie)=$year
group by ri_titolopub
order by TOTALE desc";
break;
case"riviste_distinte_richieste_ente":$query="select distinct (RIF.ri_titolopub) AS TITOLO,(CASE RIF.ri_tipomateriale WHEN 1 THEN 'articolo' WHEN 2 THEN 'libro' END) AS TIPO_MATERIALE,RIF.ri_issn AS ISSN,RIF.ri_isbn AS ISBN,COUNT(*) as TOTALE
FROM docdel as DD,biblio as BR, riferimento AS RIF
WHERE
DD.bi_idrichie=BR.bi_id
AND DD.ri_id=RIF.ri_id
AND BR.en_id=".$_SESSION['biblio']->en_id."
AND BR.bi_nilde=1
AND Year(dd_datarichie)=$year
group by ri_titolopub
order by TOTALE desc";
break;
case"riviste_fornite_ente":$query="select RIF.ri_titolopub AS TITOLO,(CASE RIF.ri_tipomateriale WHEN 1 THEN 'articolo' WHEN 2 THEN 'libro' END) AS TIPO_MATERIALE,RIF.ri_issn AS ISSN,RIF.ri_isbn AS ISBN,COUNT(RIF.ri_id) as TOTALE
FROM docdel as DD,biblio as BF, riferimento AS RIF
WHERE
DD.bi_idforni=BF.bi_id
AND DD.ri_id=RIF.ri_id
AND BF.en_id=".$_SESSION['biblio']->en_id."
AND BF.bi_nilde=1
AND Year(dd_datarichie)=$year
group by ri_titolopub
order by TOTALE desc";
break;
case"utenti_attivi_ente":$query="SELECT biblio.bi_id AS ID,bi_nome AS NOME_BIBLIOTECA,biblio.prov_id AS PROV, COUNT(DISTINCT ut_id) as TOTALE
FROM report_utente,biblio
WHERE biblio.bi_id = report_utente.bi_id
AND re_anno=$year
AND re_richieste >= 1
AND biblio.en_id =".$_SESSION['biblio']->en_id."
group by ID
order by TOTALE DESC";
break;
case"utenti_ente":$query="SELECT biblio.bi_id AS ID,bi_nome AS NOME_BIBLIOTECA,biblio.prov_id AS PROV, COUNT(ut_id) as TOTALE, SUM(IF(ap_stato = 0,1,0)) as ATTESA,SUM(IF(ap_stato = 1,1,0)) as ABILITATI,SUM(IF(ap_stato = 2,1,0)) as DISABILITATI
FROM biblio,appartiene
WHERE biblio.bi_id = appartiene.bi_id
AND ap_stato in (0,1,2)
AND biblio.en_id =".$_SESSION['biblio']->en_id."
group by ID
order by TOTALE DESC";
break;
case"tempi_giacenza_ente":$query="Select BR.bi_id AS ID,BR.bi_nome as RICHIEDENTE,BR.prov_id AS PROV_RICHIEDENTE,SUM(re_tempo_risp_ln)/SUM(re_richieste_ln)/86400 as TEMPO_MEDIO_GIACENZA
FROM report_biblio as rep,biblio as BR
WHERE bi_idrichie=BR.bi_id AND BR.en_id=".$_SESSION['biblio']->en_id." AND BR.bi_nilde=1
AND re_anno=$year group by BR.bi_id";
break;
case"biblio_ente":$query="SELECT biblio.bi_id AS ID,bi_nome AS NOME,bi_indi AS INDIRIZZO,bi_cap AS CAP,bi_citta AS CITTA,prov_id AS PROV,bi_tel AS TELEFONO,bi_fax AS FAX,bi_url AS SITO,bi_opac AS URL_OPAC,bi_piva AS PIVA,bi_cf AS CF,bi_referente_dd AS REFERENTE,bi_email_dd AS EMAIL_REFERENTE,bi_tel_dd AS TEL_REFERENTE,bi_data_reg AS DATA_REGISTRAZIONE,acnp_cod AS COD_ACNP,(CASE acnp_attivo WHEN 0 THEN 'in attesa' WHEN 1 THEN 'attivo' WHEN 2 THEN 'disabilitato' END) as ACNP_LINK,sbn_cod AS COD_SBN,sbn_cod_polo AS COD_SBN_POLO from biblio LEFT JOIN tab_acnp ON biblio.bi_id=tab_acnp.bi_id LEFT JOIN tab_sbn ON biblio.bi_id=tab_sbn.bi_id WHERE bi_nilde=1 AND biblio.en_id=".$_SESSION['biblio']->en_id;//bi_stato IN (".getConstVar('BIBLIO_ATTIVE_SQL_LIST').")
break;
case"scambi_globali_progetto":$query="Select RIF.ri_id AS IDRIF,dd_id AS IDRICHIESTA,RIF.ri_titolopub AS TITOLO,RIF.ri_titolopart AS TITOLO_ARTICOLO_o_CAPITOLO,(CASE RIF.ri_tipomateriale WHEN 1 THEN 'articolo' WHEN 2 THEN 'libro' END) AS TIPO_MATERIALE,ri_anno AS ANNO,RIF.ri_issn AS ISSN,RIF.ri_isbn AS ISBN,
BR.bi_id AS ID_RICHIEDENTE,BR.bi_nome as RICHIEDENTE,(CASE BR.bi_nilde WHEN 1 THEN 'N' WHEN 0 THEN 'FN' END) AS TIPO_RICHIEDENTE,BR.prov_id AS PROV_RICHIEDENTE,ENTER.en_nome AS ENTE_RICHIEDENTE,BF.bi_id AS ID_FORNITORE,BF.bi_nome as FORNITORE,(CASE BF.bi_nilde WHEN 1 THEN 'N' WHEN 0 THEN 'FN' END) AS TIPO_FORNITORE,BF.prov_id AS PROV_FORNITORE,ENTEF.en_nome AS ENTE_FORNITORE,
DD.dd_datarichie AS DATA_RICHIESTA,DD.dd_dataeva AS DATA_EVASIONE,
(CASE DD.dd_stato_ddill WHEN 0 THEN 'nuovo' WHEN 10 THEN 'inoltro NILDE' WHEN 11 THEN 'richiesto annullamento' WHEN 20 THEN 'inoltro FN' WHEN 99 THEN 'elaborazione server' WHEN 101 THEN 'evaso NILDE' WHEN 102 THEN 'evaso NILDE (HC)' WHEN 103 THEN 'evaso NILDE (WM)' WHEN 110 THEN 'evaso POSTA' WHEN 111 THEN 'evaso FAX'WHEN 112 THEN 'evaso ARIEL' WHEN 113 THEN 'evaso ALTRO' WHEN 150 THEN 'evaso FN' WHEN 201 THEN 'inevaso (riferim. errato)' WHEN 202 THEN 'inevaso (non posseduto)' WHEN 203 THEN 'inevaso (non disponibile)' WHEN 204 THEN 'inevaso (over 15)' WHEN 205 THEN 'inevaso (over quota)' WHEN 230 THEN 'inevaso ALTRO' WHEN 250 THEN 'inevaso FN' WHEN 299 THEN 'annullata' END ) AS STATO_DD,
(CASE DD.dd_stato_borr WHEN 0 THEN 'nuovo' WHEN 1 THEN 'in corso' WHEN 30 THEN 'attesa accettazione utente' WHEN 31 THEN 'utente rifiuta costo' WHEN 32 THEN 'utente accetta costo' WHEN 34 THEN 'utente non risponde' WHEN 40 THEN 'in gestione' WHEN 50 THEN 'scarìcato' WHEN 51 THEN 'ricevuto' WHEN 33 THEN 'cestinato' WHEN 70 THEN 'inevaso' WHEN 100 THEN 'consegnato ute/archiviato' WHEN 101 THEN 'consegnato (diretta)' WHEN 200 THEN 'non consegnato/arch come inevaso' WHEN 201 THEN 'non consegnato (diretto)' WHEN 202 THEN 'non consegnato (riferim. errato)' WHEN 203 THEN 'non consegnato (cestinato)' WHEN 300 THEN 'reiterato' WHEN 302 THEN 'reiterato (riferimento errato)' WHEN 303 THEN 'reiterato (cestinato)' END) AS STATO_BORROWING,if(ut_id IS NOT NULL AND ut_id>0,'Si','No') AS RICHIESTA_UTENTE
FROM docdel as DD,biblio as BR, biblio as BF, riferimento AS RIF,ente as ENTER,ente as ENTEF
WHERE DD.bi_idrichie=BR.bi_id AND DD.bi_idforni=BF.bi_id AND DD.ri_id=RIF.ri_id AND Year(dd_datarichie)=$year
AND ENTER.en_id=BR.en_id AND ENTEF.en_id=BF.en_id AND
( ( BR.bi_id IN (SELECT bi_id from biblio_progetto WHERE pr_id=$prog) AND BR.bi_nilde=1) OR ( BF.bi_id IN (SELECT bi_id from biblio_progetto WHERE pr_id=$prog) AND BF.bi_nilde=1) )";
break;
case"richieste_interne_progetto":$query="SELECT bi_idrichie AS ID, BR.bi_nome AS BIBLIOTECA, BR.prov_id AS PROV,ENTER.en_nome AS ENTE,sum( re_richieste_bw ) AS TOT_RICHIESTE_NILDE, sum( re_richieste_bw_fn ) AS TOT_RICHIESTE_FN , sum( re_evaso_bw_nilde ) AS TOT_RICHIESTE_EVASE_NILDE, sum( re_evaso_bw_posta ) AS TOT_RICHIESTE_EVASE_POSTA , sum( re_evaso_bw_fax ) AS TOT_RICHIESTE_EVASE_FAX, sum( re_evaso_bw_ariel ) AS TOT_RICHIESTE_EVASE_ARIEL, sum( re_evaso_bw_altro ) AS TOT_RICHIESTE_EVASE_ALTRO, sum( re_evaso_bw_fn ) AS TOT_RICHIESTE_EVASE_FN, sum( re_inevaso_bw_riferr ) AS TOT_RICHIESTE_INEVASE_RIF_ERRATO, sum( re_inevaso_bw_nonposs ) AS TOT_RICHIESTE_INEVASE_NON_POSSEDUTO, sum( re_inevaso_bw_nondisp ) AS TOT_RICHIESTE_INEVASE_NON_DISPONIBILE, sum( re_inevaso_bw_over15 ) AS TOT_RICHIESTE_INEVASE_OVER15, sum( re_inevaso_bw_overquota ) AS TOT_RICHIESTE_INEVASE_OVER_QUOTA, sum( re_inevaso_bw_altro ) AS TOT_RICHIESTE_INEVASE_ALTRO, sum( re_inevaso_bw_fn ) AS TOT_RICHIESTE_INEVASE_FN, sum( re_richieste_ln ) AS TOT_FORNITE, sum( re_richieste_ln_fn ) AS TOT_FORNITE_FN , sum( re_evaso_ln_nilde ) AS TOT_FORNITE_EVASE_NILDE , sum( re_evaso_ln_posta ) AS TOT_FORNITE_EVASE_POSTA , sum( re_evaso_ln_fax ) AS TOT_FORNITE_EVASE_FAX, sum( re_evaso_ln_ariel ) AS TOT_FORNITE_EVASE_ARIEL , sum( re_evaso_ln_altro ) AS TOT_FORNITE_EVASE_ALTRO, sum( re_evaso_ln_fn ) AS TOT_FORNITE_EVASE_FN, sum( re_inevaso_ln_riferr ) AS TOT_FORNITE_INEVASE_RIF_ERRATO, sum( re_inevaso_ln_nonposs ) AS TOT_FORNITE_INEVASE_NON_POSSEDUTO, sum( re_inevaso_ln_nondisp ) AS TOT_FORNITE_INEVASE_NON_DISPONIBILE , sum( re_inevaso_ln_over15 ) AS TOT_FORNITE_INEVASE_OVER15, sum( re_inevaso_ln_overquota ) AS TOT_FORNITE_INEVASE_OVER_QUOTA_SETTIMANALE, sum( re_inevaso_ln_altro ) AS TOT_FORNITE_INEVAS_ALTRO, sum( re_inevaso_ln_fn ) AS TOT_FORNITE_INEVASO_FN
FROM report_biblio, biblio AS BR,biblio as BF,ente AS ENTER
WHERE report_biblio.bi_idrichie = BR.bi_id
AND report_biblio.bi_idforni = BF.bi_id
AND ENTER.en_id=BR.en_id
AND BR.bi_id IN (SELECT bi_id from biblio_progetto WHERE pr_id=$prog)
AND BR.bi_nilde=1
AND BF.bi_id IN (SELECT bi_id from biblio_progetto WHERE pr_id=$prog)
AND BF.bi_nilde=1
AND re_anno=$year
GROUP BY bi_idrichie";
break;
case"richieste_progetto":$query="SELECT bi_idrichie AS ID, BR.bi_nome AS BIBLIOTECA, BR.prov_id AS PROV,ENTER.en_nome AS ENTE,sum( re_richieste_bw ) AS TOT_RICHIESTE_NILDE, sum( re_richieste_bw_fn ) AS TOT_RICHIESTE_FN , sum( re_evaso_bw_nilde ) AS TOT_RICHIESTE_EVASE_NILDE, sum( re_evaso_bw_posta ) AS TOT_RICHIESTE_EVASE_POSTA , sum( re_evaso_bw_fax ) AS TOT_RICHIESTE_EVASE_FAX, sum( re_evaso_bw_ariel ) AS TOT_RICHIESTE_EVASE_ARIEL, sum( re_evaso_bw_altro ) AS TOT_RICHIESTE_EVASE_ALTRO, sum( re_evaso_bw_fn ) AS TOT_RICHIESTE_EVASE_FN, sum( re_inevaso_bw_riferr ) AS TOT_RICHIESTE_INEVASE_RIF_ERRATO, sum( re_inevaso_bw_nonposs ) AS TOT_RICHIESTE_INEVASE_NON_POSSEDUTO, sum( re_inevaso_bw_nondisp ) AS TOT_RICHIESTE_INEVASE_NON_DISPONIBILE, sum( re_inevaso_bw_over15 ) AS TOT_RICHIESTE_INEVASE_OVER15, sum( re_inevaso_bw_overquota ) AS TOT_RICHIESTE_INEVASE_OVER_QUOTA, sum( re_inevaso_bw_altro ) AS TOT_RICHIESTE_INEVASE_ALTRO, sum( re_inevaso_bw_fn ) AS TOT_RICHIESTE_INEVASE_FN, sum( re_richieste_ln ) AS TOT_FORNITE, sum( re_richieste_ln_fn ) AS TOT_FORNITE_FN , sum( re_evaso_ln_nilde ) AS TOT_FORNITE_EVASE_NILDE , sum( re_evaso_ln_posta ) AS TOT_FORNITE_EVASE_POSTA , sum( re_evaso_ln_fax ) AS TOT_FORNITE_EVASE_FAX, sum( re_evaso_ln_ariel ) AS TOT_FORNITE_EVASE_ARIEL , sum( re_evaso_ln_altro ) AS TOT_FORNITE_EVASE_ALTRO, sum( re_evaso_ln_fn ) AS TOT_FORNITE_EVASE_FN, sum( re_inevaso_ln_riferr ) AS TOT_FORNITE_INEVASE_RIF_ERRATO, sum( re_inevaso_ln_nonposs ) AS TOT_FORNITE_INEVASE_NON_POSSEDUTO, sum( re_inevaso_ln_nondisp ) AS TOT_FORNITE_INEVASE_NON_DISPONIBILE , sum( re_inevaso_ln_over15 ) AS TOT_FORNITE_INEVASE_OVER15, sum( re_inevaso_ln_overquota ) AS TOT_FORNITE_INEVASE_OVER_QUOTA_SETTIMANALE, sum( re_inevaso_ln_altro ) AS TOT_FORNITE_INEVAS_ALTRO, sum( re_inevaso_ln_fn ) AS TOT_FORNITE_INEVASO_FN
FROM report_biblio, biblio AS BR,ente as ENTER
WHERE report_biblio.bi_idrichie = BR.bi_id
AND ENTER.en_id=BR.en_id
AND BR.bi_id IN (SELECT bi_id from biblio_progetto WHERE pr_id=$prog)
AND BR.bi_nilde =1
AND re_anno=$year
GROUP BY bi_idrichie";
break;
case"riviste_richieste_progetto":$query="select RIF.ri_titolopub AS TITOLO,(CASE RIF.ri_tipomateriale WHEN 1 THEN 'articolo' WHEN 2 THEN 'libro' END) AS TIPO_MATERIALE,RIF.ri_issn AS ISSN,RIF.ri_isbn AS ISBN,COUNT(RIF.ri_id) as TOTALE
FROM docdel as DD,biblio as BR, riferimento AS RIF
WHERE
DD.bi_idrichie=BR.bi_id
AND DD.ri_id=RIF.ri_id
AND BR.bi_id IN (SELECT bi_id from biblio_progetto WHERE pr_id=$prog)
AND BR.bi_nilde=1
AND Year(dd_datarichie)=$year
group by ri_titolopub
order by TOTALE desc";
break;
case"riviste_distinte_richieste_progetto":$query="select distinct (RIF.ri_titolopub) AS TITOLO,(CASE RIF.ri_tipomateriale WHEN 1 THEN 'articolo' WHEN 2 THEN 'libro' END) AS TIPO_MATERIALE,RIF.ri_issn AS ISSN,RIF.ri_isbn AS ISBN,COUNT(*) as TOTALE
FROM docdel as DD,biblio as BR, riferimento AS RIF
WHERE
DD.bi_idrichie=BR.bi_id
AND DD.ri_id=RIF.ri_id
AND BR.bi_id IN (SELECT bi_id from biblio_progetto WHERE pr_id=$prog)
AND BR.bi_nilde=1
AND Year(dd_datarichie)=$year
group by ri_titolopub
order by TOTALE desc";
break;
case"riviste_fornite_progetto":$query="select RIF.ri_titolopub AS TITOLO,(CASE RIF.ri_tipomateriale WHEN 1 THEN 'articolo' WHEN 2 THEN 'libro' END) AS TIPO_MATERIALE,RIF.ri_issn AS ISSN,RIF.ri_isbn AS ISBN,COUNT(RIF.ri_id) as TOTALE
FROM docdel as DD,biblio as BF, riferimento AS RIF
WHERE
DD.bi_idforni=BF.bi_id
AND DD.ri_id=RIF.ri_id
AND BF.bi_id IN (SELECT bi_id from biblio_progetto WHERE pr_id=$prog)
AND BF.bi_nilde=1
AND Year(dd_datarichie)=$year
group by ri_titolopub
order by TOTALE desc";
break;
case"utenti_attivi_progetto":$query="SELECT biblio.bi_id AS ID,bi_nome AS NOME_BIBLIOTECA,biblio.prov_id AS PROV,ente.en_nome AS ENTE, COUNT(DISTINCT ut_id) as TOTALE
FROM report_utente,biblio, ente
WHERE
ente.en_id=biblio.en_id
AND biblio.bi_id = report_utente.bi_id
AND re_anno=$year
AND re_richieste >= 1
AND biblio.bi_id IN (SELECT bi_id from biblio_progetto WHERE pr_id=$prog)
group by ID
order by TOTALE DESC";
break;
case"utenti_progetto":$query="SELECT biblio.bi_id AS ID,bi_nome AS NOME_BIBLIOTECA,biblio.prov_id AS PROV,ente.en_nome AS ENTE,COUNT(ut_id) as TOTALE, SUM(IF(ap_stato = 0,1,0)) as ATTESA,SUM(IF(ap_stato = 1,1,0)) as ABILITATI,SUM(IF(ap_stato = 2,1,0)) as DISABILITATI
FROM biblio,appartiene,ente
WHERE
ente.en_id=biblio.en_id
AND biblio.bi_id = appartiene.bi_id
AND ap_stato in (0,1,2)
AND biblio.bi_id IN (SELECT bi_id from biblio_progetto WHERE pr_id=$prog)
group by ID
order by TOTALE DESC";
break;
case"tempi_giacenza_progetto":$query="Select BR.bi_id AS ID,BR.bi_nome as RICHIEDENTE,BR.prov_id AS PROV_RICHIEDENTE,ENTER.en_nome AS ENTE_RICHIEDENTE,SUM(re_tempo_risp_ln)/SUM(re_richieste_ln)/86400 as TEMPO_MEDIO_GIACENZA
FROM report_biblio as rep,biblio as BR,ente AS ENTER
WHERE
ENTER.en_id=BR.en_id
AND bi_idrichie=BR.bi_id AND BR.bi_id IN (SELECT bi_id from biblio_progetto WHERE pr_id=$prog) AND BR.bi_nilde=1
AND re_anno=$year group by BR.bi_id";
break;
case"biblio_progetto":$query="SELECT biblio.bi_id AS ID,bi_nome AS NOME,ente.en_nome AS ENTE,bi_indi AS INDIRIZZO,bi_cap AS CAP,bi_citta AS CITTA,prov_id AS PROV,bi_tel AS TELEFONO,bi_fax AS FAX,bi_url AS SITO,bi_opac AS URL_OPAC,bi_piva AS PIVA,bi_cf AS CF,bi_referente_dd AS REFERENTE,bi_email_dd AS EMAIL_REFERENTE,bi_tel_dd AS TEL_REFERENTE,bi_data_reg AS DATA_REGISTRAZIONE,acnp_cod AS COD_ACNP,(CASE acnp_attivo WHEN 0 THEN 'in attesa' WHEN 1 THEN 'attivo' WHEN 2 THEN 'disabilitato' END) as ACNP_LINK,sbn_cod AS COD_SBN,sbn_cod_polo AS COD_SBN_POLO from biblio LEFT JOIN tab_acnp ON biblio.bi_id=tab_acnp.bi_id LEFT JOIN tab_sbn ON biblio.bi_id=tab_sbn.bi_id LEFT JOIN ente ON ente.en_id=biblio.en_id WHERE bi_nilde=1 AND biblio.bi_id IN (SELECT bi_id from biblio_progetto WHERE pr_id=$prog) AND ente.en_id=biblio.en_id";//bi_stato IN (".getConstVar('BIBLIO_ATTIVE_SQL_LIST').")
break;
case"scambi_globali_biblio":$query="Select RIF.ri_id AS IDRIF,dd_id AS IDRICHIESTA,RIF.ri_titolopub AS TITOLO,RIF.ri_titolopart AS TITOLO_ARTICOLO_o_CAPITOLO,(CASE RIF.ri_tipomateriale WHEN 1 THEN 'articolo' WHEN 2 THEN 'libro' END) AS TIPO_MATERIALE,ri_anno AS ANNO,RIF.ri_issn AS ISSN,RIF.ri_isbn AS ISBN,
BR.bi_id AS ID_RICHIEDENTE,BR.bi_nome as RICHIEDENTE,(CASE BR.bi_nilde WHEN 1 THEN 'N' WHEN 0 THEN 'FN' END) AS TIPO_RICHIEDENTE,BR.prov_id AS PROV_RICHIEDENTE,ENTER.en_nome AS ENTE_RICHIEDENTE,BF.bi_id AS ID_FORNITORE,BF.bi_nome as FORNITORE,(CASE BF.bi_nilde WHEN 1 THEN 'N' WHEN 0 THEN 'FN' END) AS TIPO_FORNITORE,BF.prov_id AS PROV_FORNITORE,ENTEF.en_nome AS ENTE_FORNITORE,
DD.dd_datarichie AS DATA_RICHIESTA,DD.dd_dataeva AS DATA_EVASIONE,
(CASE DD.dd_stato_ddill WHEN 0 THEN 'nuovo' WHEN 10 THEN 'inoltro NILDE' WHEN 11 THEN 'richiesto annullamento' WHEN 20 THEN 'inoltro FN' WHEN 99 THEN 'elaborazione server' WHEN 101 THEN 'evaso NILDE' WHEN 102 THEN 'evaso NILDE (HC)' WHEN 103 THEN 'evaso NILDE (WM)' WHEN 110 THEN 'evaso POSTA' WHEN 111 THEN 'evaso FAX'WHEN 112 THEN 'evaso ARIEL' WHEN 113 THEN 'evaso ALTRO' WHEN 150 THEN 'evaso FN' WHEN 201 THEN 'inevaso (riferim. errato)' WHEN 202 THEN 'inevaso (non posseduto)' WHEN 203 THEN 'inevaso (non disponibile)' WHEN 204 THEN 'inevaso (over 15)' WHEN 205 THEN 'inevaso (over quota)' WHEN 230 THEN 'inevaso ALTRO' WHEN 250 THEN 'inevaso FN' WHEN 299 THEN 'annullata' END ) AS STATO_DD,
(CASE DD.dd_stato_borr WHEN 0 THEN 'nuovo' WHEN 1 THEN 'in corso' WHEN 30 THEN 'attesa accettazione utente' WHEN 31 THEN 'utente rifiuta costo' WHEN 32 THEN 'utente accetta costo' WHEN 34 THEN 'utente non risponde' WHEN 40 THEN 'in gestione' WHEN 50 THEN 'scarìcato' WHEN 51 THEN 'ricevuto' WHEN 33 THEN 'cestinato' WHEN 70 THEN 'inevaso' WHEN 100 THEN 'consegnato ute/archiviato' WHEN 101 THEN 'consegnato (diretta)' WHEN 200 THEN 'non consegnato/arch come inevaso' WHEN 201 THEN 'non consegnato (diretto)' WHEN 202 THEN 'non consegnato (riferim. errato)' WHEN 203 THEN 'non consegnato (cestinato)' WHEN 300 THEN 'reiterato' WHEN 302 THEN 'reiterato (riferimento errato)' WHEN 303 THEN 'reiterato (cestinato)' END) AS STATO_BORROWING,if(ut_id IS NOT NULL AND ut_id>0,'Si','No') AS RICHIESTA_UTENTE
FROM docdel as DD,biblio as BR, biblio as BF, riferimento AS RIF,ente as ENTER,ente as ENTEF
WHERE DD.bi_idrichie=BR.bi_id AND DD.bi_idforni=BF.bi_id AND DD.ri_id=RIF.ri_id AND Year(dd_datarichie)=$year
AND ENTER.en_id=BR.en_id AND ENTEF.en_id=BF.en_id AND ( (BR.bi_id=".$_SESSION['biblio']->bi_id." AND BR.bi_nilde=1) OR (BF.bi_id=".$_SESSION['biblio']->bi_id." AND BF.bi_nilde=1) )";
break;
case"richieste_biblio":$query="SELECT sum( re_richieste_bw ) AS TOT_RICHIESTE_NILDE, sum( re_richieste_bw_fn ) AS TOT_RICHIESTE_FN , sum( re_evaso_bw_nilde ) AS TOT_RICHIESTE_EVASE_NILDE, sum( re_evaso_bw_posta ) AS TOT_RICHIESTE_EVASE_POSTA , sum( re_evaso_bw_fax ) AS TOT_RICHIESTE_EVASE_FAX, sum( re_evaso_bw_ariel ) AS TOT_RICHIESTE_EVASE_ARIEL, sum( re_evaso_bw_altro ) AS TOT_RICHIESTE_EVASE_ALTRO, sum( re_evaso_bw_fn ) AS TOT_RICHIESTE_EVASE_FN, sum( re_inevaso_bw_riferr ) AS TOT_RICHIESTE_INEVASE_RIF_ERRATO, sum( re_inevaso_bw_nonposs ) AS TOT_RICHIESTE_INEVASE_NON_POSSEDUTO, sum( re_inevaso_bw_nondisp ) AS TOT_RICHIESTE_INEVASE_NON_DISPONIBILE, sum( re_inevaso_bw_over15 ) AS TOT_RICHIESTE_INEVASE_OVER15, sum( re_inevaso_bw_overquota ) AS TOT_RICHIESTE_INEVASE_OVER_QUOTA, sum( re_inevaso_bw_altro ) AS TOT_RICHIESTE_INEVASE_ALTRO, sum( re_inevaso_bw_fn ) AS TOT_RICHIESTE_INEVASE_FN, sum( re_richieste_ln ) AS TOT_FORNITE, sum( re_richieste_ln_fn ) AS TOT_FORNITE_FN , sum( re_evaso_ln_nilde ) AS TOT_FORNITE_EVASE_NILDE , sum( re_evaso_ln_posta ) AS TOT_FORNITE_EVASE_POSTA , sum( re_evaso_ln_fax ) AS TOT_FORNITE_EVASE_FAX, sum( re_evaso_ln_ariel ) AS TOT_FORNITE_EVASE_ARIEL , sum( re_evaso_ln_altro ) AS TOT_FORNITE_EVASE_ALTRO, sum( re_evaso_ln_fn ) AS TOT_FORNITE_EVASE_FN, sum( re_inevaso_ln_riferr ) AS TOT_FORNITE_INEVASE_RIF_ERRATO, sum( re_inevaso_ln_nonposs ) AS TOT_FORNITE_INEVASE_NON_POSSEDUTO, sum( re_inevaso_ln_nondisp ) AS TOT_FORNITE_INEVASE_NON_DISPONIBILE , sum( re_inevaso_ln_over15 ) AS TOT_FORNITE_INEVASE_OVER15, sum( re_inevaso_ln_overquota ) AS TOT_FORNITE_INEVASE_OVER_QUOTA_SETTIMANALE, sum( re_inevaso_ln_altro ) AS TOT_FORNITE_INEVAS_ALTRO, sum( re_inevaso_ln_fn ) AS TOT_FORNITE_INEVASO_FN
FROM report_biblio
WHERE report_biblio.bi_idrichie=".$_SESSION['biblio']->bi_id."
AND re_anno=$year";
break;
case"riviste_richieste_biblio":$query="select RIF.ri_titolopub AS TITOLO,(CASE RIF.ri_tipomateriale WHEN 1 THEN 'articolo' WHEN 2 THEN 'libro' END) AS TIPO_MATERIALE,RIF.ri_issn AS ISSN,RIF.ri_isbn AS ISBN,COUNT(RIF.ri_id) as TOTALE
FROM docdel as DD,biblio as BR, riferimento AS RIF
WHERE
DD.bi_idrichie=BR.bi_id
AND DD.ri_id=RIF.ri_id
AND BR.bi_id=".$_SESSION['biblio']->bi_id."
AND BR.bi_nilde=1
AND Year(dd_datarichie)=$year
group by ri_titolopub
order by TOTALE desc";
break;
case"riviste_distinte_richieste_biblio":$query="select distinct (RIF.ri_titolopub) AS TITOLO,(CASE RIF.ri_tipomateriale WHEN 1 THEN 'articolo' WHEN 2 THEN 'libro' END) AS TIPO_MATERIALE,RIF.ri_issn AS ISSN,RIF.ri_isbn AS ISBN,COUNT(*) as TOTALE
FROM docdel as DD,biblio as BR, riferimento AS RIF
WHERE
DD.bi_idrichie=BR.bi_id
AND DD.ri_id=RIF.ri_id
AND BR.bi_id=".$_SESSION['biblio']->bi_id."
AND BR.bi_nilde=1
AND Year(dd_datarichie)=$year
group by ri_titolopub
order by TOTALE desc";
break;
case"riviste_fornite_biblio":$query="select RIF.ri_titolopub AS TITOLO,(CASE RIF.ri_tipomateriale WHEN 1 THEN 'articolo' WHEN 2 THEN 'libro' END) AS TIPO_MATERIALE,RIF.ri_issn AS ISSN,RIF.ri_isbn AS ISBN,COUNT(RIF.ri_id) as TOTALE
FROM docdel as DD,biblio as BF, riferimento AS RIF
WHERE
DD.bi_idforni=BF.bi_id
AND DD.ri_id=RIF.ri_id
AND BF.bi_id=".$_SESSION['biblio']->bi_id."
AND BF.bi_nilde=1
AND Year(dd_datarichie)=$year
group by ri_titolopub
order by TOTALE desc";
break;
case"utenti_attivi_biblio":$query="SELECT COUNT(DISTINCT ut_id) as TOTALE
FROM report_utente,biblio
WHERE biblio.bi_id = report_utente.bi_id
AND re_anno=$year
AND re_richieste >= 1
AND biblio.bi_id=".$_SESSION['biblio']->bi_id;
break;
case"utenti_biblio":$query="SELECT COUNT(ut_id) as TOTALE, SUM(IF(ap_stato = 0,1,0)) as ATTESA,SUM(IF(ap_stato = 1,1,0)) as ABILITATI,SUM(IF(ap_stato = 2,1,0)) as DISABILITATI
FROM biblio,appartiene
WHERE biblio.bi_id = appartiene.bi_id
AND ap_stato in (0,1,2)
AND biblio.bi_id=".$_SESSION['biblio']->bi_id;
break;
case"tempi_giacenza_biblio":$query="Select SUM(re_tempo_risp_ln)/SUM(re_richieste_ln)/86400 as TEMPO_MEDIO_GIACENZA
FROM report_biblio as rep,biblio as BR
WHERE bi_idrichie=BR.bi_id AND BR.bi_id=".$_SESSION['biblio']->bi_id." AND BR.bi_nilde=1