Composants de cette interface
Les classes
Les classes PHP5 suivantes fournissent la logique métier de cette interface :
Source de la classe pub
<?php
/**
* Classe de gestion de la publicité
*
*/
class pub extends database {
/**
* Table des campagnes
*
* @var string
*/
protected $pubTable = 'pub';
/**
* Constructeur de la classe
*
*/
public function __construct() {
parent::__construct();
}
public function insertCampaign($nom,$url,$desc,$contact,$ban,$logo,$limit_aff,$valid,$debut,$fin) {
$sql = 'INSERT INTO '.$this->pubTable.'
(nom_site,url_site,description_site,contact_site,url_banniere,url_logo,date_debut,date_fin,
affichages,limite_affichages,valide) VALUES (';
$sql .= "'".$this->SQLescape($nom)."' , ";
$sql .= "'".$this->SQLescape($url)."' , ";
$sql .= "'".$this->SQLescape($desc)."' , ";
$sql .= "'".$this->SQLescape($contact)."' , ";
$sql .= "'".$this->SQLescape($ban)."' , ";
$sql .= "'".$this->SQLescape($logo)."' , ";
$sql .= "'".$this->SQLescape($debut)."' , ";
$sql .= "'".$this->SQLescape($fin)."' , ";
$sql .= "'0' , ";
$sql .= "'".$this->SQLescape($limit_aff)."' , ";
$sql .= "'".$this->SQLescape($valid)."' ";
$sql .= ')';
//echo $sql;
$this->conn->query($sql);
}
/**
* Retourne une campagne valide au hasard et incrémente le nombre d'affichage
*
* @return array Résultat
*/
public function getRandomCampaign() {
$now = date('Y-m-d H:i:s');
$sql = 'SELECT id,url_site,url_banniere FROM '. $this->pubTable;
$sql .= " WHERE valide = 1 ";
$sql .= " AND ( affichages < limite_affichages OR limite_affichages = 0 ) ";
$sql .= " AND ( date_debut < '".$now."' OR date_debut = '0000-00-00 00:00:00' ) ";
$sql .= " AND ( date_fin <= '".$now."' OR date_fin = '0000-00-00 00:00:00' ) ";
$sql .= " ORDER BY RANDOM(id) LIMIT 1";
$r = $this->conn->arrayQuery($sql, SQLITE_ASSOC);
if(!isset($r[0]['id'])) {
return false;
}
$this->incrementView($r[0]['id']);
return $r;
}
public function updateCampaign($id,$nom,$url,$desc,$contact,$ban,$logo,$limit_aff,$valid,$debut,$fin) {
$sql = 'UPDATE '.$this->pubTable.' SET ';
$sql .= "nom_site = '".$this->SQLescape($nom)."' , ";
$sql .= "url_site = '".$this->SQLescape($url)."' , ";
$sql .= "description_site = '".$this->SQLescape($desc)."' , ";
$sql .= "contact_site = '".$this->SQLescape($contact)."' , ";
$sql .= "url_banniere = '".$this->SQLescape($ban)."' , ";
$sql .= "url_logo = '".$this->SQLescape($logo)."' , ";
$sql .= "date_debut = '".$this->SQLescape($debut)."' , ";
$sql .= "date_fin = '".$this->SQLescape($fin)."' , ";
$sql .= "limite_affichages = '".$this->SQLescape($limit_aff)."' , ";
$sql .= "valide = '".$this->SQLescape($valid)."' ";
$sql .= ' WHERE id = '.$this->SQLescape($id);
$this->conn->query($sql);
}
/**
* Incrémente le compteur d'affichage selon l' ID
*
* @param int $id ID de la campagne
*/
protected function incrementView($id) {
$sql = " UPDATE pub SET affichages = affichages + 1 WHERE id = '$id' ";
$this->conn->query($sql);
}
/**
* Retourne TOUTES les campagnes de la bdd
*
* @return array Résultat
*/
public function getAllCampaign() {
$sql = 'SELECT id,nom_site,url_site,valide,affichages FROM '.$this->pubTable;
return $this->conn->arrayQuery($sql, SQLITE_ASSOC);
}
/**
* Retourne les campagnes actives
*
* @return array Résultat
*/
public function getActiveCampaign() {
$sql = "SELECT id,nom_site,url_site,valide,affichages
FROM ".$this->pubTable." WHERE valide = '1'";
return $this->conn->arrayQuery($sql, SQLITE_ASSOC);
}
/**
* Retourne les campagnes inactives
*
* @return array Résultat
*/
public function getInactiveCampaign() {
$sql = " SELECT id,nom_site,url_site,valide,affichages
FROM ".$this->pubTable." WHERE valide = '0'";
return $this->conn->arrayQuery($sql, SQLITE_ASSOC);
}
/**
* Retourne une campagne selon son ID
*
* @param $id ID de la campagne
* @return array Résultat
*/
public function getCampaignByID($id) {
//$sql = " SELECT * FROM pub WHERE id = '".$this->SQLescape($id)."' ";
$sql = " SELECT nom_site, url_site, description_site, contact_site,url_banniere, url_logo, date_debut, date_fin, limite_affichages, valide, affichages
FROM pub
WHERE id = '".$this->SQLescape($id)."' ";
return $this->conn->arrayQuery($sql, SQLITE_ASSOC);
}
} // end class
?>
Les tables SQLite
Cette interface utilise uniquement des bases de données SQLite, voici les tables utilisées :
CREATE TABLE project_users ( id INTEGER AUTOINCREMENT, user VARCHAR , email VARCHAR , firstname VARCHAR , lastname VARCHAR , passhash VARCHAR, controlhash VARCHAR, inscription DATETIME, level INT , description TEXT, PRIMARY KEY(id) )
CREATE TABLE project_lost_pass ( id INTEGER AUTOINCREMENT, user INTEGER , controlhash VARCHAR, modification DATETIME, PRIMARY KEY(id) )
CREATE TABLE pub ( id INTEGER AUTOINCREMENT, nom_site VARCHAR , url_site VARCHAR , description_site VARCHAR, contact_site VARCHAR, url_banniere VARCHAR, url_logo VARCHAR, date_debut DATETIME, date_fin DATETIME, affichages INT, limite_affichages INT, valide INT , PRIMARY KEY(id) )
CREATE TABLE project_faq ( id INTEGER AUTOINCREMENT, question VARCHAR , response VARCHAR , cat INTEGER, valide INT , PRIMARY KEY(id) )
CREATE TABLE project_faq_cat ( id INTEGER AUTOINCREMENT, name VARCHAR , description VARCHAR , PRIMARY KEY(id) )
CREATE TABLE project_files ( id INTEGER AUTOINCREMENT, user VARCHAR , file VARCHAR , directory VARCHAR, add_date DATETIME, hash VARCHAR, valide INT , PRIMARY KEY(id) )
CREATE TABLE old_project_files ( id INTEGER AUTOINCREMENT, user VARCHAR , file VARCHAR , directory VARCHAR, add_date DATETIME, del_date DATETIME, hash VARCHAR, PRIMARY KEY(id) )
CREATE TABLE project_wall ( id INTEGER AUTOINCREMENT, name VARCHAR , add_date DATETIME, description VARCHAR, code TEXT, valide INT , PRIMARY KEY(id) )
