write(0,0,$titre_page);
break;
case "TABLEAUHTML":
echo "".$titre_page."
" ;
break;
default:
echo "".$titre_page."
" ;
break;
}
// Pour localiser les éditions : $deflt2docs_location, $pmb_lecteurs_localises, $empr_location_id ;
// Calcul du nombre de pages à afficher
$sql = "SELECT count(1) ";
$sql.= "FROM (((exemplaires LEFT JOIN notices AS notices_m ON expl_notice = notices_m.notice_id ) ";
$sql.= "LEFT JOIN bulletins ON expl_bulletin = bulletins.bulletin_id) ";
$sql.= "LEFT JOIN notices AS notices_s ON bulletin_notice = notices_s.notice_id), ";
$sql.= "docs_type , pret, empr ";
$sql.= "WHERE ";
if ($pmb_lecteurs_localises) {
if ($empr_location_id=="")
$empr_location_id = $deflt2docs_location ;
if ($empr_location_id!=0)
$sql.= "empr_location='$empr_location_id' AND ";
if ($docs_location_id=="")
$docs_location_id = 0 ;
if ($docs_location_id!=0)
$sql.= "expl_location='$docs_location_id' AND ";
}
if($empr_categ_filter) {
$sql.= " empr_categ= '".$empr_categ_filter."' AND ";
}
if($empr_codestat_filter) {
$sql.= " empr_codestat= '".$empr_codestat_filter."' AND ";
}
$sql.= "expl_typdoc = idtyp_doc and pret_idexpl = expl_id and empr.id_empr = pret.pret_idempr ";
$sql.= $critere_requete;
$req_nombre_lignes_pret = pmb_mysql_query($sql, $dbh);
$nombre_lignes_pret = pmb_mysql_result($req_nombre_lignes_pret, 0, 0);
//Si aucune limite_page n'a été passée, valeur par défaut : 10
if ($nb_per_page=="") {
$nb_per_page = 10;
}
if(!$page) $page=1;
$limite_mysql =($page-1)*$nb_per_page;
// Comptage retard/en cours
$sql_count = "SELECT IF(pret_retour>=CURDATE(),'ENCOURS','RETARDS') as retard, count(pret_idexpl) as combien ";
$sql_count.= "FROM (((exemplaires LEFT JOIN notices AS notices_m ON expl_notice = notices_m.notice_id ) ";
$sql_count.= "LEFT JOIN bulletins ON expl_bulletin = bulletins.bulletin_id) ";
$sql_count.= "LEFT JOIN notices AS notices_s ON bulletin_notice = notices_s.notice_id), ";
$sql_count.= "pret, empr ";
$sql_count.= "WHERE ";
if ($pmb_lecteurs_localises) {
if ($empr_location_id!=0)
$sql_count.= "empr_location='$empr_location_id' AND ";
if ($docs_location_id!=0)
$sql_count.= "expl_location='$docs_location_id' AND ";
}
if($empr_categ_filter) {
$sql_count.= " empr_categ= '".$empr_categ_filter."' AND ";
}
if($empr_codestat_filter) {
$sql_count.= " empr_codestat= '".$empr_codestat_filter."' AND ";
}
$sql_count.= "pret_idexpl = expl_id and empr.id_empr = pret.pret_idempr ";
$sql_count.=(($pmb_short_loan_management==1 && strpos($sub,'short_loans')!==false)?"and short_loan_flag='1' ":' ');
$sql_count.= "group by retard ";
$req_count = pmb_mysql_query($sql_count) or die("Erreur SQL !
".$sql_count."
".pmb_mysql_error());
while ($data_count = pmb_mysql_fetch_object($req_count)) {
$nbtotal_prets[$data_count->retard]=$data_count->combien;
}
if(!isset($nbtotal_prets['RETARDS'])) $nbtotal_prets['RETARDS'] = 0;
if(!isset($nbtotal_prets['ENCOURS'])) $nbtotal_prets['ENCOURS'] = 0;
// construction du message ## prêts en retard sur un total de ##
$msg['n_retards_sur_total_de'] = str_replace ("!!nb_retards!!",$nbtotal_prets['RETARDS']*1,$msg['n_retards_sur_total_de']);
$msg['n_retards_sur_total_de'] = str_replace ("!!nb_total!!",($nbtotal_prets['RETARDS']+$nbtotal_prets['ENCOURS'])*1,$msg['n_retards_sur_total_de']);
//REINITIALISATION DE LA REQUETE SQL
$sql = "SELECT date_format(pret_date, '".$msg['format_date']."') as aff_pret_date, ";
$sql .= "date_format(pret_retour, '".$msg['format_date']."') as aff_pret_retour, ";
$sql .= "IF(pret_retour>=CURDATE(),0,1) as retard, ";
$sql .= "id_empr, empr_nom, empr_prenom, empr_mail, empr_cb, expl_cote, expl_cb, expl_notice, expl_bulletin, notices_m.notice_id as idnot, trim(concat(ifnull(notices_m.tit1,''),ifnull(notices_s.tit1,''),' ',ifnull(bulletin_numero,''), if (mention_date, concat(' (',mention_date,')') ,''))) as tit, tdoc_libelle, ";
$sql .= "short_loan_flag ";
$sql .= "FROM (((exemplaires LEFT JOIN notices AS notices_m ON expl_notice = notices_m.notice_id ) ";
$sql .= "LEFT JOIN bulletins ON expl_bulletin = bulletins.bulletin_id) ";
$sql .= "LEFT JOIN notices AS notices_s ON bulletin_notice = notices_s.notice_id), ";
$sql .= "docs_type, pret, empr ";
$sql .= "WHERE ";
if ($pmb_lecteurs_localises) {
if ($empr_location_id!=0)
$sql.= "empr_location='$empr_location_id' AND ";
if ($docs_location_id!=0)
$sql.= "expl_location='$docs_location_id' AND ";
}
if($empr_categ_filter) {
$sql.= " empr_categ= '".$empr_categ_filter."' AND ";
}
if($empr_codestat_filter) {
$sql.= " empr_codestat= '".$empr_codestat_filter."' AND ";
}
$sql.= "expl_typdoc = idtyp_doc and pret_idexpl = expl_id and empr.id_empr = pret.pret_idempr ";
$sql.= $critere_requete;
if ($nombre_lignes_pret > 0) {
$expl_list = '';
switch($dest) {
case "TABLEAU":
$res = @pmb_mysql_query($sql, $dbh);
$nbr_champs = @pmb_mysql_num_fields($res);
for($n=0; $n < $nbr_champs; $n++) {
$worksheet->write_string(2,$n,pmb_mysql_field_name($res,$n));
}
for($i=0; $i < $nombre_lignes_pret; $i++) {
$row = pmb_mysql_fetch_row($res);
$j=0;
foreach($row as $dummykey=>$col) {
if(!$col) $col=" ";
$worksheet->write_string(($i+3),$j,$col);
$j++;
}
}
$worksheet->download('edition.xls');
break;
case "TABLEAUHTML":
$res = @pmb_mysql_query($sql, $dbh);
$expl_list .= "" ;
$expl_list .= "
| $msg[4014] |
$msg[4016] |
$msg[294] |
$msg[233] |
$msg[234] |
$msg[empr_nom_prenom] |
$msg[circ_date_emprunt] |
$msg[circ_date_retour] |
";
while(($data=pmb_mysql_fetch_array($res))) {
$header_aut = "";
$responsabilites = get_notice_authors($data['idnot']) ;
$as = array_search ("0", $responsabilites["responsabilites"]) ;
if ($as!== FALSE && $as!== NULL) {
$auteur_0 = $responsabilites["auteurs"][$as] ;
$auteur = new auteur($auteur_0["id"]);
$header_aut .= $auteur->isbd_entry;
} else {
$aut1_libelle=array();
$as = array_keys ($responsabilites["responsabilites"], "1" ) ;
for ($i = 0 ; $i < count($as) ; $i++) {
$indice = $as[$i] ;
$auteur_1 = $responsabilites["auteurs"][$indice] ;
$auteur = new auteur($auteur_1["id"]);
$aut1_libelle[]= $auteur->isbd_entry;
}
$header_aut .= implode (", ",$aut1_libelle) ;
}
$header_aut ? $auteur=$header_aut : $auteur="";
$expl_list .= "";
$expl_list .= " | ".$data["empr_cb"]." |
".$data["expl_cote"]." |
".$data["tdoc_libelle"]." |
".$data["tit"]." |
".$auteur." |
".$data['empr_nom'].", ".$data["empr_prenom"]." |
".$data["aff_pret_date"]." |
".$data['aff_pret_retour']." |
";
}
$expl_list .= "
" ;
echo $expl_list ;
break;
default:
echo ''.$msg['n_retards_sur_total_de'].'
";
echo "";
$sql_for_print = $sql;
$sql = $sql." LIMIT ".$limite_mysql.", ".$nb_per_page;
$res = @pmb_mysql_query($sql, $dbh);
$parity=1;
$expl_list .= "
| $msg[4014] |
$msg[4016] |
$msg[294] |
$msg[233] |
$msg[234] |
".$msg['empr_nom_prenom']." |
".$msg['circ_date_emprunt']." |
".$msg['circ_date_retour']." |
$msg[369] |
";
$odd_even=0;
while(($data=pmb_mysql_fetch_array($res))) {
$empr_nom = $data['empr_nom'];
$empr_prenom = $data['empr_prenom'];
$empr_mail = $data['empr_mail'];
$id_empr = $data['id_empr'];
$empr_cb = $data['empr_cb'];
$aff_pret_date = $data['aff_pret_date'];
$aff_pret_retour = $data['aff_pret_retour'];
$retard = $data['retard'];
$cote_expl = $data['expl_cote'];
$id_expl =$data['expl_cb'];
$titre = $data['tit'];
$support = $data['tdoc_libelle'];
$id_empr=$data['id_empr'];
$short_loan_flag=$data['short_loan_flag'];
$header_aut = "";
$responsabilites = get_notice_authors($data['idnot']) ;
$as = array_search ("0", $responsabilites["responsabilites"]) ;
if ($as!== FALSE && $as!== NULL) {
$auteur_0 = $responsabilites["auteurs"][$as] ;
$auteur = new auteur($auteur_0["id"]);
$header_aut .= $auteur->isbd_entry;
} else {
$aut1_libelle=array();
$as = array_keys ($responsabilites["responsabilites"], "1" ) ;
for ($i = 0 ; $i < count($as) ; $i++) {
$indice = $as[$i] ;
$auteur_1 = $responsabilites["auteurs"][$indice] ;
$auteur = new auteur($auteur_1["id"]);
$aut1_libelle[]= $auteur->isbd_entry;
}
$header_aut .= implode (", ",$aut1_libelle) ;
}
$header_aut ? $auteur=$header_aut : $auteur="";
if($retard || ($sub=='encours') || (strpos($sub,'short_loans')!==false)) {
// on affiche les résultats
if ($retard) $tit_color="color='RED'";
else $tit_color="";
if ($odd_even==0) {
$pair_impair = "odd";
$odd_even=1;
} elseif ($odd_even==1) {
$pair_impair = "even";
$odd_even=0;
}
$tr_javascript=" onmouseover=\"this.className='surbrillance'\" onmouseout=\"this.className='$pair_impair'\"";
$expl_list .= "";
if (SESSrights & CIRCULATION_AUTH) {
$expl_list .= "| ".$id_expl." | ";
} else {
$expl_list .= "".$id_expl." | ";
}
$expl_list .= "".$cote_expl." | ";
$expl_list .= "".$support." | ";
if (SESSrights & CATALOGAGE_AUTH) {
if ($data['expl_notice']) {
$expl_list .= "".$titre." | "; // notice de monographie
} elseif ($data['expl_bulletin']) {
$expl_list .= "".$titre." | "; // notice de bulletin
} else {
$expl_list .= "".$titre." | ";
}
} else {
$expl_list .= "".$titre." | ";
}
$expl_list .= "".$auteur." | ";
// **************** ajout icône ajout panier
if ($empr_show_caddie) {
$img_ajout_empr_caddie="![basket](./images/basket_empr.gif "\\"${msg[400]}\\"")
";
}
$expl_list .= "$img_ajout_empr_caddie".$empr_nom.", ".$empr_prenom." | ";
$expl_list .= "".$aff_pret_date;
$expl_list .= (($pmb_short_loan_management && $short_loan_flag)?"  ":'');
$expl_list .= " | ";
$expl_list .= "".$aff_pret_retour." | ";
/* test de date de retour dépassée */
if ($retard) {
$imprime_click = "onclick=\"openPopUp('./pdf.php?pdfdoc=lettre_retard&cb_doc=$id_expl&id_empr=$id_empr', 'lettre', 600, 500, -2, -2, 'toolbar=no, dependent=yes, resizable=yes'); return(false) \"";
$mail_click = "onclick=\"if (confirm('".$msg["mail_retard_confirm"]."')) {openPopUp('./mail.php?type_mail=mail_retard&cb_doc=$id_expl&id_empr=$id_empr', 'mail', 600, 500, -2, -2, 'toolbar=no, dependent=yes, resizable=yes, scrollbars=yes');} return(false) \"";
$expl_list .= "\n![\"".$msg['lettre_retard']."\"](\"./images/new.gif\") | \n";
if (($empr_mail)&&($biblio_email)) {
$expl_list .= "![\"".$msg['mail_retard']."\"](\"./images/mail.png\") | ";
} else {
$expl_list .= " | ";
}
} else {
$expl_list .= " | | ";
}
$expl_list .= "
\n";
}
}
print "";
print pmb_bidi("");
// formulaire d'action tout imprimer, dispo uniquement si pas de relances pultiples
if ($pmb_gestion_amende==0 || $pmb_gestion_financiere==0) {
$bouton_imprime_tout ="" ;
$restrict_localisation ="";
if ($pmb_lecteurs_localises) {
if ($empr_location_id!="") $restrict_localisation .= "&empr_location_id=$empr_location_id" ;
if ($docs_location_id!="") $restrict_localisation .= "&docs_location_id=$docs_location_id" ;
}
switch($sub) {
case "pargroupe" :
$bouton_imprime_tout = "";
break;
case "retard" :
case "retard_par_date" :
$bouton_imprime_tout = "";
break;
case "owner" :
break;
case "encours" :
default :
break;
}
if ($bouton_imprime_tout) echo "
";
}
$nav_bar = aff_pagination ("$url_page?categ=$categ&sub=$sub&empr_location_id=$empr_location_id&docs_location_id=$docs_location_id", $nombre_lignes_pret, $nb_per_page, $page, 10, false, true) ;
print $nav_bar;
//impression/emails (on est dans le cas retards/retards par date)
if ($act=="print") {
$not_all_mail = array();
$mail_sended_id_empr = array();
if ($nombre_lignes_pret) {
$resultat=pmb_mysql_query($sql_for_print);
while ($r=pmb_mysql_fetch_object($resultat)) {
$mail_sended = 0;
if ((($mailretard_priorite_email==1)||($mailretard_priorite_email==2))&&($r->empr_mail)) {
if ((!count($mail_sended_id_empr)) || (!in_array($r->id_empr,$mail_sended_id_empr))) {
if (!$relance) $relance = 1;
// l'objet du mail
$var = "mailretard_".$relance."objet";
eval ("\$objet=\"".${$var}."\";");
// la formule de politesse du bas (le signataire)
$var = "mailretard_".$relance."fdp";
eval ("\$fdp=\"".${$var}."\";");
// le texte après la liste des ouvrages en retard
$var = "mailretard_".$relance."after_list";
eval ("\$after_list=\"".${$var}."\";");
// le texte avant la liste des ouvrges en retard
$var = "mailretard_".$relance."before_list";
eval ("\$before_list=\"".${$var}."\";");
// le "Madame, Monsieur," ou tout autre truc du genre "Cher adhérent,"
$var = "mailretard_".$relance."madame_monsieur";
eval ("\$madame_monsieur=\"".${$var}."\";");
$texte_mail='';
if($madame_monsieur) $texte_mail.=$madame_monsieur."\r\n\r\n";
if($before_list) $texte_mail.=$before_list."\r\n\r\n";
//Récupération des exemplaires
$rqt = "select expl_cb from pret, exemplaires where pret_idempr='".$r->id_empr."' and pret_retour < curdate() and pret_idexpl=expl_id order by pret_date " ;
$req_cb = pmb_mysql_query($rqt) or die('Erreur SQL !
'.$rqt.'
'.pmb_mysql_error());
while ($data = pmb_mysql_fetch_array($req_cb)) {
/* Récupération des infos exemplaires et prêt */
$requete = "SELECT notices_m.notice_id as m_id, notices_s.notice_id as s_id, expl_cb, pret_date, pret_retour, tdoc_libelle, section_libelle, location_libelle, trim(concat(ifnull(notices_m.tit1,''),ifnull(notices_s.tit1,''),' ',ifnull(bulletin_numero,''), if (mention_date, concat(' (',mention_date,')') ,''))) as tit, ";
$requete.= " date_format(pret_date, '".$msg["format_date"]."') as aff_pret_date, ";
$requete.= " date_format(pret_retour, '".$msg["format_date"]."') as aff_pret_retour, ";
$requete.= " IF(pret_retour>sysdate(),0,1) as retard, notices_m.tparent_id, notices_m.tnvol " ;
$requete.= "FROM (((exemplaires LEFT JOIN notices AS notices_m ON expl_notice = notices_m.notice_id ) LEFT JOIN bulletins ON expl_bulletin = bulletins.bulletin_id) LEFT JOIN notices AS notices_s ON bulletin_notice = notices_s.notice_id), docs_type, docs_section, docs_location, pret ";
$requete.= "WHERE expl_cb='".addslashes($data['expl_cb'])."' and expl_typdoc = idtyp_doc and expl_section = idsection and expl_location = idlocation and pret_idexpl = expl_id ";
$req = pmb_mysql_query($requete);
$expl = pmb_mysql_fetch_object($req);
$responsabilites=array() ;
$header_aut = "" ;
$responsabilites = get_notice_authors(($expl->m_id+$expl->s_id)) ;
$as = array_search ("0", $responsabilites["responsabilites"]) ;
if ($as!== FALSE && $as!== NULL) {
$auteur_0 = $responsabilites["auteurs"][$as] ;
$auteur = new auteur($auteur_0["id"]);
$header_aut .= $auteur->isbd_entry;
} else {
$aut1_libelle=array();
$as = array_keys ($responsabilites["responsabilites"], "1" ) ;
for ($i = 0 ; $i < count($as) ; $i++) {
$indice = $as[$i] ;
$auteur_1 = $responsabilites["auteurs"][$indice] ;
$auteur = new auteur($auteur_1["id"]);
$aut1_libelle[]= $auteur->isbd_entry;
}
$header_aut .= implode (", ",$aut1_libelle) ;
}
$header_aut ? $auteur=" / ".$header_aut : $auteur="";
// récupération du titre de série
$tit_serie="";
if ($expl->tparent_id && $expl->m_id) {
$parent = new serie($expl->tparent_id);
$tit_serie = $parent->name;
if($expl->tnvol)
$tit_serie .= ', '.$expl->tnvol;
}
if($tit_serie) {
$expl->tit = $tit_serie.'. '.$expl->tit;
}
$texte_mail.=$expl->tit.$auteur."\r\n";
$texte_mail.=" -".$msg[fpdf_date_pret]." ".$expl->aff_pret_date." ".$msg[fpdf_retour_prevu]." ".$expl->aff_pret_retour."\r\n";
$texte_mail.=" -".$expl->location_libelle." : ".$expl->section_libelle." (".$expl->expl_cb.")\r\n\r\n\r\n";
}
$texte_mail.="\r\n";
if($after_list) $texte_mail.=$after_list."\r\n\r\n";
if($fdp) $texte_mail.=$fdp."\r\n\r\n";
$texte_mail.=mail_bloc_adresse() ;
//Si mail de rappel affecté au responsable du groupe
$requete="select id_groupe,resp_groupe from groupe,empr_groupe where id_groupe=groupe_id and empr_id=".$r->id_empr." and resp_groupe and mail_rappel limit 1";
$req=pmb_mysql_query($requete);
/* Récupération du nom, prénom et mail du lecteur destinataire */
if(pmb_mysql_num_rows($req) > 0) {
$requete="select id_empr, empr_mail, empr_nom, empr_prenom from empr where id_empr='".pmb_mysql_result($req, 0,1)."'";
$result=pmb_mysql_query($requete);
$coords_dest=pmb_mysql_fetch_object($result);
} else {
$requete="select id_empr, empr_mail, empr_nom, empr_prenom from empr where id_empr=".$r->id_empr;
$result=pmb_mysql_query($requete);
$coords_dest=pmb_mysql_fetch_object($result);
}
/* Récupération du nom, prénom et mail du lecteur concerné */
$requete="select id_empr, empr_mail, empr_nom, empr_prenom, empr_cb from empr where id_empr=".$r->id_empr;
$req=pmb_mysql_query($requete);
$coords=pmb_mysql_fetch_object($req);
//remplacement nom et prenom
$texte_mail=str_replace("!!empr_name!!", $coords->empr_nom,$texte_mail);
$texte_mail=str_replace("!!empr_first_name!!", $coords->empr_prenom,$texte_mail);
$headers .= "Content-type: text/plain; charset=".$charset."\n";
$mail_sended=mailpmb($coords_dest->empr_prenom." ".$coords_dest->empr_nom, $coords_dest->empr_mail, $objet." : ".$coords->empr_prenom." ".mb_strtoupper($coords->empr_nom,$charset)." (".$coords->empr_cb.")",$texte_mail, $biblio_name, $biblio_email,$headers, "", $PMBuseremailbcc,1);
} else {
$mail_sended = 1;
}
}
if (!$mail_sended) {
$not_all_mail[] = $r->id_empr;
} else {
$mail_sended_id_empr[] = $r->id_empr;
}
}
}
if (count($not_all_mail) > 0) {
print "
";
}
}
break;
} //switch($dest)
} else {
// la requête n'a produit aucun résultat
switch($dest) {
case "TABLEAU":
break;
case "TABLEAUHTML":
break;
default:
echo ''.$msg['n_retards_sur_total_de'].'
";
error_message($msg[46], str_replace('!!form_cb!!', $form_cb, $msg['edit_lect_aucun_trouve']), 1, './edit.php?categ=empr&sub='.$sub);
}
}