_bdd = $bdd;
}
/**
* Renvoit les informations concernant une inscription
*$inscrits =
* @param int $id Identifiant de la personne
* @param string $champs Champs � renvoyer
* @access public
* @return array
*/
function obtenir($id, $champs = 'i.*')
{
$requete = 'SELECT';
$requete .= ' ' . $champs . ' ';
$requete .= 'FROM';
$requete .= ' afup_inscription_forum i ';
$requete .= 'LEFT JOIN';
$requete .= ' afup_facturation_forum f ON i.reference = f.reference ';
$requete .= 'WHERE i.id=' . intval($id);
return $this->_bdd->obtenirEnregistrement($requete);
}
/**
* Renvoie la liste des inscriptions pour lesquels md5(concat('id', 'reference')) = $code_md5 (1er argument)
* (concaténation des champs 'id' et 'reference', passée à la fonction md5)
*
* @param $code_md5 string Md5 de la concaténation des champs "id" et "reference"
* @param string $champs Liste des champs à récupérer en BD
* @return array
*/
function obtenirInscription($code_md5, $champs = 'i.*')
{
$requete = "SELECT $champs FROM afup_inscription_forum i ";
$requete .= "LEFT JOIN afup_facturation_forum f ON i.reference = f.reference ";
$requete .= "WHERE md5(CONCAT(i.id, i.reference)) = '$code_md5'";
return $this->_bdd->obtenirEnregistrement($requete);
}
/**
* Retrieve the registrations associated to the same reference
* Used by example to send a confirmation email to every people associated
* to the same payment.
* @param string $reference The reference shared
* @return array The people we want ;)
*/
public function getRegistrationsByReference($reference)
{
$ref = $this->_bdd->echapper($reference);
$sql = <<_bdd->obtenirTous($sql);
return $registrations;
}
function obtenirSuivi($id_forum)
{
$forum = new Forum($this->_bdd);
$id_forum_precedent = $forum->obtenirForumPrecedent($id_forum);
$now = new \DateTime();
$dateForum = \DateTime::createFromFormat('U', $forum->obtenir($id_forum)['date_fin_vente']);
$daysToEndOfSales = 0;
if ($dateForum >= $now) {
$daysToEndOfSales = $dateForum->diff($now)->format('%r%a');
}
$requete = 'SELECT
COUNT(*) as nombre,
DATEDIFF(FROM_UNIXTIME(date, \'%Y-%m-%d\'), FROM_UNIXTIME(af.date_fin_vente, \'%Y-%m-%d\')) as jour,
id_forum
FROM
afup_inscription_forum i
RIGHT JOIN afup_forum_tarif aft ON (aft.id = i.type_inscription AND aft.default_price > 0)
LEFT JOIN afup_forum af ON af.id = i.id_forum
WHERE
i.id_forum IN (' . (int)$id_forum . ', ' . (int)$id_forum_precedent . ')
AND
etat <> 1
GROUP BY jour, i.id_forum
HAVING jour < 0
ORDER BY jour ASC ';
$nombre_par_date = $this->_bdd->obtenirTous($requete);
if ([] === $nombre_par_date) {
$nombre_par_date = [['jour' => 1]];
}
$suivis = [];
for($i = $nombre_par_date[0]['jour']; $i <= 0; $i++) {
$infoForum = array_sum(array_map(function ($info) use ($i, $id_forum) {
if ($info['id_forum'] == $id_forum && $info['jour'] <= $i) {
return $info['nombre'];
}
return 0;
}, $nombre_par_date));
$infoN1 = array_sum(array_map(function ($info) use ($i, $id_forum_precedent) {
if ($info['id_forum'] == $id_forum_precedent && $info['jour'] <= $i) {
return $info['nombre'];
}
return 0;
}, $nombre_par_date));
$suivis[$i] = [
'jour' => $i,
'n' => $daysToEndOfSales >= $i ? $infoForum : null,
'n_1' => $infoN1
];
}
return [
'suivi' => $suivis,
'min' => $nombre_par_date[0]['jour'],
'max' => $i,
'daysToEndOfSales' => $daysToEndOfSales
];
}
function obtenirListePourEmargement($id_forum = null)
{
$requete = 'SELECT';
$requete .= ' i.*, f.societe ';
$requete .= 'FROM';
$requete .= ' afup_inscription_forum i ';
$requete .= 'LEFT JOIN';
$requete .= ' afup_facturation_forum f ON i.reference = f.reference ';
$requete .= 'WHERE i.id_forum =' . $id_forum . ' ';
$requete .= 'AND i.type_inscription NOT IN (9, 10, 11, 12, 15) '; // pas orga, conférencier, sponsor, presse
$requete .= 'ORDER BY i.nom ASC';
$liste_emargement = array();
$liste = $this->_bdd->obtenirTous($requete);
$derniere_lettre = "";
foreach ($liste as $inscrit) {
$premiere_lettre = strtoupper($inscrit['nom'][0]);
if ($derniere_lettre != $premiere_lettre) {
$liste_emargement[] = array(
'nom' => $premiere_lettre,
'etat' => -1,
);
$derniere_lettre = $premiere_lettre;
}
$liste_emargement[] = $inscrit;
}
return $liste_emargement;
}
function obtenirListePourEmargementConferencierOrga($id_forum = null)
{
$requete = 'SELECT';
$requete .= ' i.*, f.societe ';
$requete .= 'FROM';
$requete .= ' afup_inscription_forum i ';
$requete .= 'LEFT JOIN';
$requete .= ' afup_facturation_forum f ON i.reference = f.reference ';
$requete .= 'WHERE i.id_forum =' . $id_forum . ' ';
$requete .= 'AND i.type_inscription IN (9, 10, 11, 12, 15) '; // seulement orga, conférencier, sponsor, presse
$requete .= 'ORDER BY i.nom ASC';
$liste_emargement = array();
$liste = $this->_bdd->obtenirTous($requete);
$derniere_lettre = "";
foreach ($liste as $inscrit) {
$premiere_lettre = strtoupper($inscrit['nom'][0]);
if ($derniere_lettre != $premiere_lettre) {
$liste_emargement[] = array(
'nom' => $premiere_lettre,
'etat' => -1,
);
$derniere_lettre = $premiere_lettre;
}
$liste_emargement[] = $inscrit;
}
return $liste_emargement;
}
/**
* Renvoit la liste des inscriptions au forum
*
* @param string $champs Champs � renvoyer
* @param string $ordre Tri des enregistrements
* @param bool $associatif Renvoyer un tableau associatif ?
* @access public
* @return array
*/
function obtenirListe($id_forum = null,
$champs = 'i.*',
$ordre = 'i.date',
$associatif = false,
$filtre = false)
{
$requete = 'SELECT
' . $champs . ' ,
CASE WHEN i.id_member IS NOT NULL
THEN ( SELECT MAX(ac.date_fin) AS lastsubcription FROM afup_cotisations ac WHERE ac.type_personne = i.member_type AND ac.id_personne = i.id_member )
ELSE (SELECT MAX(ac.date_fin) AS lastsubcription
FROM afup_personnes_physiques app
LEFT JOIN afup_personnes_morales apm ON apm.id = app.id_personne_morale
LEFT JOIN afup_cotisations ac ON ac.type_personne = IF(apm.id IS NULL, 0, 1) AND ac.id_personne = IFNULL(apm.id, app.id)
WHERE app.email COLLATE latin1_swedish_ci = i.email
GROUP BY app.`id`
)
END AS lastsubscription
FROM
afup_inscription_forum i
LEFT JOIN afup_facturation_forum f ON i.reference = f.reference
WHERE 1=1
AND i.id_forum =' . $id_forum . ' ';
if ($filtre) {
$requete .= sprintf('AND CONCAT(i.nom, i.prenom) LIKE %1$s OR f.societe LIKE %1$s ', $this->_bdd->echapper('%' . $filtre . '%'));
}
$requete .= 'ORDER BY ' . $ordre;
if ($associatif) {
return $this->_bdd->obtenirAssociatif($requete);
} else {
return $this->_bdd->obtenirTous($requete);
}
}
function modifierInscription($id, $reference, $type_inscription, $civilite, $nom, $prenom,
$email, $telephone, $coupon, $citer_societe, $newsletter_afup,
$newsletter_nexen, $mail_partenaire, $commentaires, $etat, $facturation)
{
$requete = 'UPDATE ';
$requete .= ' afup_inscription_forum ';
$requete .= 'SET';
$requete .= ' reference=' . $this->_bdd->echapper($reference) . ',';
$requete .= ' type_inscription=' . $this->_bdd->echapper($type_inscription) . ',';
$requete .= ' montant=' . $GLOBALS['AFUP_Tarifs_Forum'][$type_inscription] . ',';
$requete .= ' civilite=' . $this->_bdd->echapper($civilite) . ',';
$requete .= ' nom=' . $this->_bdd->echapper($nom) . ',';
$requete .= ' prenom=' . $this->_bdd->echapper($prenom) . ',';
$requete .= ' email=' . $this->_bdd->echapper($email) . ',';
$requete .= ' telephone=' . $this->_bdd->echapper($telephone) . ',';
$requete .= ' coupon=' . $this->_bdd->echapper($coupon) . ',';
$requete .= ' citer_societe=' . $this->_bdd->echapper($citer_societe) . ',';
$requete .= ' newsletter_afup=' . $this->_bdd->echapper($newsletter_afup) . ',';
$requete .= ' newsletter_nexen=' . $this->_bdd->echapper($newsletter_nexen) . ',';
$requete .= ' mail_partenaire=' . $this->_bdd->echapper($mail_partenaire) . ',';
$requete .= ' commentaires=' . $this->_bdd->echapper($commentaires) . ',';
$requete .= ' etat=' . $this->_bdd->echapper($etat) . ',';
$requete .= ' facturation=' . $this->_bdd->echapper($facturation);
$requete .= 'WHERE';
$requete .= ' id=' . $id;
$this->modifierEtatInscription($reference, $etat);
return $this->_bdd->executer($requete);
}
function supprimerInscription($id)
{
$requete = 'DELETE FROM afup_inscription_forum WHERE id=' . $id;
return $this->_bdd->executer($requete);
}
function modifierEtatInscription($reference, $etat)
{
$requete = 'UPDATE afup_inscription_forum ';
$requete .= 'SET etat=' . $etat . ' ';
$requete .= 'WHERE reference=' . $this->_bdd->echapper($reference);
$this->_bdd->executer($requete);
$requete = 'UPDATE afup_facturation_forum ';
$requete .= 'SET etat=' . $etat . ' ';
$requete .= 'WHERE reference=' . $this->_bdd->echapper($reference);
return $this->_bdd->executer($requete);
}
function ajouterRappel($email, $id_forum = null)
{
if ($id_forum == null) {
require_once dirname(__FILE__) . '/Forum.php';
$forum = new Forum($this->_bdd);
$id_forum = $forum->obtenirDernier();
}
$requete = 'INSERT INTO afup_inscriptions_rappels (email, date, id_forum) VALUES (' . $this->_bdd->echapper($email) . ', ' . time() . ', ' . $id_forum . ')';
return $this->_bdd->executer($requete);
}
public function obtenirListeEmailAncienVisiteurs()
{
$requete = "SELECT group_concat(DISTINCT email SEPARATOR ';')
FROM afup_inscription_forum
WHERE `email` <> ''
AND right(email, 9) <> '@afup.org'
AND type_inscription <> 12
AND locate('xxx', email) = 0";
return $this->_bdd->obtenirUn($requete);
}
}
?>