function get_relance($id_empr)
{
global $dbh, $charset, $msg, $pmb_gestion_financiere, $pmb_gestion_amende;
global $pmb_lecteurs_localises;
// liste des relances
if ($pmb_gestion_financiere && $pmb_gestion_amende) {
$amende = new amende($id_empr);
$level = $amende->get_max_level();
$niveau_min = $level["level_min"];
$id_expl = $level["level_min_id_expl"];
$total_amende = $amende->get_total_amendes();
}
$niveau_suppose = $level["level_normal"];
$cpt_id = comptes::get_compte_id_from_empr($id_empr, 2);
$cpt = new comptes($cpt_id);
$frais_relance = $cpt->summarize_transactions("", "", 0, $realisee = -1);
if ($frais_relance < 0) {
$frais_relance = -$frais_relance;
} else {
$frais_relance = 0;
}
$empr = new emprunteur($id_empr, '', FALSE, 0);
//Groupes
if (count($empr->groupes)) {
$lib_groupes = strip_tags(implode(" / ", $empr->groupes));
} else {
$lib_groupes = "";
}
$info_empr = "\n\t<td>" . $empr->cb . "</td>\n\t<td>" . $empr->nom . "</td>\n\t<td>" . $empr->prenom . "</td>\n\t<td>" . $empr->adr1 . "</td>\n\t<td>" . $empr->adr2 . "</td>\n\t<td>" . $empr->cp . "</td>\n\t<td>" . $empr->ville . "</td>\n\t<td>" . $empr->mail . "</td>\n\t<td>" . $empr->tel1 . "</td>\n\t<td>" . $empr->tel2 . "</td>\n\t" . ($pmb_lecteurs_localises ? "<td>" . $empr->empr_location_l . "</td>" : "") . "\n\t<td>" . $empr->cat_l . "</td>\n\t<td>" . $empr->cstat_l . "</td>\n\t<td>" . $empr->empr_statut_libelle . "</td>\n\t<td>" . $lib_groupes . "</td>\n\t<td>" . $niveau_suppose . "</td>\n\t<td>" . $total_amende . "</td>\n\t<td>" . $frais_relance . "</td>\n\t";
$reqexpl = "select pret_idexpl as expl from pret where pret_retour<CURDATE() and pret_idempr={$id_empr}";
$resexple = pmb_mysql_query($reqexpl, $dbh);
while ($liste = pmb_mysql_fetch_object($resexple)) {
$dates_resa_sql = " date_format(pret_date, '" . $msg["format_date"] . "') as aff_pret_date, date_format(pret_retour, '" . $msg["format_date"] . "') as aff_pret_retour ";
$requete = "SELECT notices_m.notice_id as m_id, notices_s.notice_id as s_id, pret_idempr, expl_id, expl_cb,expl_cote, pret_date, pret_retour,\n\t\tniveau_relance,\n\t\tdate_relance,\n\t\tprinted,\t\t\n\t\ttdoc_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, " . $dates_resa_sql . ", ";
$requete .= " 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_id='" . $liste->expl . "' and expl_typdoc = idtyp_doc and expl_section = idsection and expl_location = idlocation and pret_idexpl = expl_id ";
$res_det_expl = pmb_mysql_query($requete);
$expl = pmb_mysql_fetch_object($res_det_expl);
$amd = $amende->get_amende($liste->expl);
$reqlog = "select sum(log.log_printed) as printed, sum(log.log_mail) as mail\n\t\t\tfrom log_retard as log, log_expl_retard as expl where log.idempr={$id_empr} and log.niveau_reel='" . $expl->niveau_relance . "'\n\t\t \tand expl.num_log_retard=log.id_log and expl_id='" . $liste->expl . "' ";
$reslog = pmb_mysql_query($reqlog);
if ($log = pmb_mysql_fetch_object($reslog)) {
$printed = $log->printed;
$mail = $log->mail;
} else {
$printed = 0;
$mail = 0;
}
$info .= "\n\t\t<tr>\n\t\t\t{$info_empr}\n\t\t\t<td>" . htmlentities($expl->tit, ENT_QUOTES, $charset) . "</td>\n\t\t\t<td>" . htmlentities($expl->expl_cb, ENT_QUOTES, $charset) . "</td>\n\t\t\t<td>" . format_date($expl->pret_date) . "</td>\n\t\t\t<td>" . format_date($expl->pret_retour) . "</td>\n\t\t\t<td>" . $expl->niveau_relance . "</td>\n\t\t\t<td>" . format_date($expl->date_relance) . "</td>\n\t\t\t<td>" . htmlentities($mail, ENT_QUOTES, $charset) . "</td>\n\t\t\t<td>" . htmlentities($printed, ENT_QUOTES, $charset) . "</td>\n\t\t\t<td>" . htmlentities($amd["valeur"], ENT_QUOTES, $charset) . "</td>\n\t\t</tr>\n\t\t";
}
return $info;
}
function filter_loc_expl($liste_ids, $champ, $selected = "", $sort = false)
{
global $dbh, $charset;
global $all_level;
$ret = "";
if ($selected && is_array($selected) && !(count($selected) == 1 && $selected[0] == -1)) {
if (is_array($all_level) && count($all_level)) {
//Je repart des bons lecteurs
$liste_ids = array_keys($all_level);
}
$requete = "SELECT pret_idempr FROM pret JOIN exemplaires ON pret_idexpl=expl_id JOIN docs_location ON expl_location=idlocation WHERE location_libelle IN ('" . implode("','", $selected) . "') ";
if (count($liste_ids)) {
$requete .= "AND pret_idempr IN (" . implode(",", $liste_ids) . ")";
}
$requete .= " GROUP BY pret_idempr";
$res = pmb_mysql_query($requete, $dbh);
if ($res && pmb_mysql_num_rows($res)) {
$new_all_level = array(0);
while ($empr = pmb_mysql_fetch_object($res)) {
if ($all_level[$empr->pret_idempr]) {
$new_all_level[$empr->pret_idempr] = $all_level[$empr->pret_idempr];
} else {
$amende = new amende($empr->pret_idempr);
$new_all_level[$empr->pret_idempr] = $amende->get_max_level();
}
}
$all_level = $new_all_level;
} else {
$all_level = array(0);
}
}
if (!is_array($selected)) {
$selected = array();
}
$requete = "SELECT idlocation, location_libelle FROM docs_location GROUP BY location_libelle ORDER BY location_libelle";
$res = pmb_mysql_query($requete, $dbh);
if ($res && pmb_mysql_num_rows($res)) {
while ($ligne = pmb_mysql_fetch_object($res)) {
$ret .= "<option value='" . htmlentities($ligne->location_libelle, ENT_QUOTES, $charset) . "'";
if (in_array($ligne->location_libelle, $selected)) {
$ret .= " selected";
}
$ret .= ">" . htmlentities($ligne->location_libelle, ENT_QUOTES, $charset) . "</option>";
}
}
return $ret;
}
function buildPdfLoansDelayReaders($t_empr, $f_loc = 0, $niveau_relance = 0)
{
global $ourPDF, $fpdf, $pdflettreretard_1largeur_page, $pdflettreretard_1hauteur_page, $pdflettreretard_1format_page;
global $pmb_lecteurs_localises, $deflt2docs_location, $pdflettreretard_impression_tri;
global $empr_sms_activation, $empr_sms_msg_retard;
global $mailretard_priorite_email, $pmb_gestion_financiere, $pmb_gestion_amende;
$largeur_page = $pdflettreretard_1largeur_page;
$hauteur_page = $pdflettreretard_1hauteur_page;
$taille_doc = array($largeur_page, $hauteur_page);
$format_page = $pdflettreretard_1format_page;
$ourPDF = new $fpdf($format_page, 'mm', $taille_doc);
$ourPDF->Open();
$restrict_localisation = "";
if ($t_empr) {
$restrict_localisation = " id_empr in (" . implode(",", $t_empr) . ") and ";
}
if ($pmb_lecteurs_localises) {
if (!$f_loc) {
$f_loc = $deflt2docs_location;
}
} else {
$f_loc = $deflt2docs_location;
}
$this->infos_biblio($f_loc);
// parametre listant les champs de la table empr pour effectuer le tri d'impression des lettres
if ($pdflettreretard_impression_tri) {
$order_by = " ORDER BY {$pdflettreretard_impression_tri}";
} else {
$order_by = "";
}
$rqt = "select id_empr, concat(empr_nom,' ',empr_prenom) as empr_name, empr_cb, empr_mail, empr_tel1, empr_sms, count(pret_idexpl) as empr_nb, {$pdflettreretard_impression_tri} from empr, pret, exemplaires where {$restrict_localisation} pret_retour<curdate() and pret_idempr=id_empr and pret_idexpl=expl_id group by id_empr {$order_by}";
$req = pmb_mysql_query($rqt);
while ($r = pmb_mysql_fetch_object($req)) {
if ($pmb_gestion_financiere && $pmb_gestion_amende) {
$amende = new amende($r->id_empr);
$level = $amende->get_max_level();
$niveau_min = $level["level_min"];
$printed = $level["printed"];
if ($printed == 2) {
$printed = 0;
}
pmb_mysql_query("update pret set printed=1 where printed=2 and pret_idempr=" . $r->id_empr);
$not_mail = true;
if ($mailretard_priorite_email == 1 && $r->empr_mail && $niveau_min < 3) {
$not_mail = false;
}
if (($print_all || !$printed) && $niveau_min && $not_mail) {
$niveau_relance = $niveau_min;
$this->get_texts($niveau_relance);
lettre_retard_par_lecteur($r->id_empr);
$ourPDF->SetMargins($marge_page_gauche, $marge_page_gauche);
}
} else {
if (!$niveau_relance) {
$niveau_relance = 1;
}
$this->get_texts($niveau_relance);
lettre_retard_par_lecteur($r->id_empr);
$ourPDF->SetMargins($marge_page_gauche, $marge_page_gauche);
}
if ($r->empr_tel1 && $r->empr_sms && $empr_sms_msg_retard) {
$res_envoi_sms = send_sms(0, $niveau_relance, $r->empr_tel1, $empr_sms_msg_retard);
}
}
// fin while
return $ourPDF;
}
if ($empr_location_id != 0) {
$restrict_localisation = " empr_location='{$empr_location_id}' AND ";
}
}
// parametre listant les champs de la table empr pour effectuer le tri d'impression des lettres
if ($pdflettreretard_impression_tri) {
$order_by = " ORDER BY {$pdflettreretard_impression_tri}";
} else {
$order_by = "";
}
$rqt = "select id_empr, concat(empr_nom,' ',empr_prenom) as empr_name, empr_cb, empr_mail, empr_tel1, empr_sms, count(pret_idexpl) as empr_nb, {$pdflettreretard_impression_tri} from empr, pret, exemplaires where {$restrict_localisation} pret_retour<curdate() and pret_idempr=id_empr and pret_idexpl=expl_id group by id_empr {$order_by}";
$req = mysql_query($rqt) or die('Erreur SQL !<br />' . $rqt . '<br />' . mysql_error());
while ($r = mysql_fetch_object($req)) {
if ($pmb_gestion_financiere && $pmb_gestion_amende) {
$amende = new amende($r->id_empr);
$level = $amende->get_max_level();
$niveau_min = $level["level_min"];
$printed = $level["printed"];
if ($printed == 2 || $mailretard_priorite_email == 2 && $niveau_min < 3) {
$printed = 0;
}
mysql_query("update pret set printed=1 where printed=2 and pret_idempr=" . $r->id_empr);
if (($print_all || !$printed) && $niveau_min) {
$niveau = $niveau_min;
get_texts($niveau);
lettre_retard_par_lecteur($r->id_empr);
$ourPDF->SetMargins($marge_page_gauche, $marge_page_gauche);
}
} else {
if (!$niveau) {
$niveau = 1;
function do_tablo_relance()
{
global $msg, $dbh;
$tableau = "";
$amende = new amende($this->id);
$level = $amende->get_max_level();
$niveau = $level["level"];
$niveau_min = $level["level_min"];
$niveau_normal = $level["level_normal"];
$printed = $level["printed"];
$date_relance = $level["level_min_date_relance"];
$list_dates[$date_relance] = format_date($date_relance);
if ($niveau_min || $niveau_normal) {
$requete = "select count(pret_idexpl) as empr_nb from empr, pret, exemplaires where \n\t\t\tpret_retour < CURDATE() and pret_idempr=id_empr and pret_idexpl=expl_id and id_empr='" . $this->id . "'";
$res = mysql_query($requete, $dbh);
$empr = mysql_fetch_object($res);
$tableau = "<table width='100%' >";
// $tableau .= "<tr><th>".$msg["relance_nb_retard"]."</th><th>".$msg["relance_dernier_niveau"]."</th><th>".$msg["relance_date_derniere"]."</th><th>".$msg["relance_imprime"]."</th><th>".$msg["relance_niveau_suppose"]."</th></tr>";
$tableau .= "<tr>\n\t\t\t<td>" . $msg["relance_nb_retard"] . ": {$empr->empr_nb}</td>\n\t\t\t<td>" . $msg["relance_dernier_niveau"] . ": {$niveau_min}</td>\n\t\t\t<td>" . $msg["relance_date_derniere"] . ": " . $list_dates[$date_relance] . "</td>\n\t\t\t<td>" . $msg["relance_imprime"] . ": " . ($printed ? "" . $msg[40] . "" : "" . $msg[39] . "") . "</td>\n\t\t\t<td>" . $msg["relance_niveau_suppose"] . ": {$niveau_normal}</td>\n\t\t\t</tr>";
$tableau .= "</table>";
}
return $tableau;
}
