<?php
namespace TheatreCore\Repository;
use TheatreCore\Classes\Images\Images;
use TheatreCore\Entity\Classifications;
use TheatreCore\Entity\Contents;
use TheatreCore\Entity\Files;
use TheatreCore\Entity\ObjectContent;
use TheatreCore\Traits\TheatreTrait;
use TheatreCore\Entity\Spectacles;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
use Symfony\Contracts\Translation\TranslatorInterface;
use Theatre_Extranetusers;
/**
* ContentsRepository
*
* This class was generated by the Doctrine ORM. Add your own custom
* repository methods below.
*/
class ContentsRepository extends ServiceEntityRepository
{
use TheatreTrait {
getItemsList as protected getItemsListTrait;
}
private $em;
public $__table;
public $__table_object;
public $__idtable;
private $translator;
public function __construct(ManagerRegistry $registry, TranslatorInterface $translator, Images $images)
{
parent::__construct($registry, Contents::class);
$this->__table = $this->getClassMetadata()->getTableName();
$this->__table_object = $this->getClassMetadata()->name;
$this->__idtable = $this->getClassMetadata()->getSingleIdentifierFieldName();
$this->setFields = $this->getClassMetadata()->getColumnNames();
$this->translator = $translator;
$this->images = $images;
// $this->__table = 'persons';
// $this->__table_object = '\\App\\Entity\\Persons';
}
// {{{ getInfosContent()
/** Informations sur un contenu associe
*
* @access public
* @param int $idcontent identifiant du contenus associé
* @return array
*/
public function getInfosContent($idcontent = null, $params = array())
{
$infos = array();
// selectionne aussi la classification
$sql = 'SELECT c.*, cl.classification, c.`range` as `range`, e.idextranetuser, e.login
FROM ' . $this->__table . ' c
LEFT JOIN extranetusers e ON c.idextranetuser=e.idextranetuser, classifications cl, content_classification cc
WHERE cc.idcontent=c.idcontent AND cc.idclassification=cl.idclassification
AND c.idcontent=' . ((int)$idcontent);
// restreindre à un extranetuser
/*if(!empty($params['idextranetuser'])) {
}*/
$infos = $this->queryRow($sql);
if (!empty($infos)) {
// est-ce qu'on converti le wiki en HTML ?
if (!empty($params['wiki_convert'])) {
$infos['text'] = $this->wikiTransform($infos['text'], $infos['markdown']);
$infos['deck'] = $this->wikiTransform($infos['deck'], $infos['markdown']);
}
// est-ce qu'on récup aussi les fichiers
if (!empty($params['get_files'])) {
$datafile = $this->getAssociateFileContent($idcontent);
if (!empty($datafile)) {
$infos['files'] = $datafile;
}
}
// selectionne éalement le journaliste associé et le journal et les contacts associes
foreach (array('idcontact_journalist', 'idcontact_newspaper') as $f) {
if (!empty($infos[$f])) {
$infos[$f] = $this->getLinkedContact($infos['idcontent'], $f);
}
}
// contact signature
$infos['idcontact'] = $this->getLinkedContact($infos['idcontent'], 'idcontact');
$infos['idperson'] = $this->getLinkedPerson($infos['idcontent'], 'content_person');
// selectionne aussi la/les classification(s)
$classifications_list = $this->getClassifications($idcontent);
if (!empty($classifications_list)) {
$infos['classifications'] = $classifications_list;
$infos['classification_code'] = $infos['classification'];
$infos['classification'] = $this->translator->trans($infos['classification']);
}
// récupèrer les infos sur l'extranetuser
if (!empty($infos['login'])) {
$infos['extranetuser'] = array(
'login' => $infos['login'],
'idextranetuser' => $infos['idextranetuser'],
);
}
}
// langue
if (!empty($infos['language'])) {
$infos['language_string'] = $this->getNameLanguage($infos['language']);
}
return $infos;
}
// }}}
// {{{ getInfosObject()
/** Alias de getInfosContent
*
* @access public
* @param int $idobject identifiant
* @return array
* @see getInfosObject()
*/
public function getInfosObject($idobject = null)
{
return $this->getInfosContent($idobject);
}
// }}}
// {{{ addContent()
/** Ajoute un contenu et l'associe si besoin
*
*
* @access public
* @param string $objet le type d'objet à insérer (personnes, spectacles...)
* @param string $idobject (id de l'utilisateur pour le nom de son répertoire)
* @param string $idcontent (id du contenu à modifier si on est en mode modification)
* @param string $sort classement
*/
public function addContent($object, $idobject, $idcontent = null, $sort = 0, $classifications = array(), $return_value = false, $group_idclassification = null, $values = null)
{
/**
* En modification, on doit verifier que le contenu ne soit pas deja utilise
* si il est utilise, on doit juste supprimer la relation
*/
$em = $this->getEntityManager();
$content = null;
if (!is_null($idcontent)) {
$content = $em->getRepository(Contents::class)->find($idcontent);
}
if ($content) {
if ($x = $em->getRepository(ObjectContent::class)->findBy(['idcontent' => $content])) {
// si associe a quelque chose d'autre
// if($this->isUseObjectContent($idcontent)) {
// $id = null;
// supression de la relation dans object_content
$this->deleteDataRelation('object_content', 'idcontent', $idcontent, 'idobject', $idobject, 'object', $object);
// creation d'un nouveau contenu
$newidcontent = $this->setData($idcontent, true);
} else {
// modification simple
if ($return_value) {
$this->setData($idcontent);
return $idcontent;
} else {
// mettre à jour le classement/groupement dans object_content si nécessaire
$object_content = $this->getEntityManager()->getRepository(ObjectContent::class);
// ajout dans object_content
$object_content->setUpdateFields(array(
'idclassification' => $group_idclassification
));
$object_content->setData(null, false, true, 'idobject=' . $idobject . ' AND idcontent=' . $idcontent . ' AND object=\'' . $object . '\' ');
return $this->setData($idcontent);
}
}
} else { // creation
$newidcontent = $this->setData($idcontent, true);
}
// ajout dans object_content si nouveau contenu
if (isset($newidcontent)) {
$idcontent = $newidcontent;
$content_created = $this->find($idcontent);
//objet dans la table object_content
$object_content = new ObjectContent();
// ajout dans object_content
$object_content->setIdobject($idobject);
$object_content->setObject($object);
$object_content->setIdcontent($content_created);
$object_content->setRange($sort);
$object_content->setIdclassification($group_idclassification);
// ajout dans object_content
try {
$em->persist($object_content);
$em->flush();
} catch (Exception $e) {
$em->getConnection()->rollBack();
throw $e;
}
}
// ajouter aussi les classifications ?
if (!empty($classifications)) {
foreach ($classifications as $class) {
$class_tab[$idcontent] = $class;
}
try {
$this->setRelationSelect('content_classification', 'idclassification', 'idcontent', $class_tab, $idcontent);
} catch (\Exception $e) {
return $e->getError();
}
}
//retourner l'idcontent du contenu
return $idcontent;
}
// }}}
// {{{ setRelationSelectObject()
/** Enregistrement dans les table relationnelle avec object
*
* Permet l'enregistrement multiple de valeur dans les tables
* de relation avec object
*
* @access public
* @param string $table Nom de la table de relation
* @param string $key Clé dans le tableau
* @param string $col Colonne
* @param array $array_values Valeur à enregistrer
* @param int $id Id de l'enregistrement principal
* @param string $object Nom de l'objet
* @return bool
*/
public function setRelationSelectObject($table, $key, $col, $array_values, $id, $object, $allRights = true, $group_idclassification = null)
{
// $this->setOption('quote_identifier', false);
$em = $this->getEntityManager()->getConnection();
if ($allRights) {
$rights = array('update' => 1);
}
if ($this->isCheckRights() || !$allRights) {
$extranetusers = Theatre_Extranetusers::factory('extranetusers');
$rights = $extranetusers->getRights();
}
if (isset($rights) && $rights['update'] == 1) {
// on supprime puis on insère (replace ne marche pas)
$sql_del = 'DELETE FROM ' . $table . ' WHERE ' . $key . '=\'' . $id . '\' AND object=\'' . $this->escape($object) . '\'';
$this->logDeletion('[Contents::setRelationSelectObject] Supprime de relations ' . $sql_del);
$em->exec($sql_del);
// si array_values est vide, c'est qu'on veux supprimer ?
if (empty($array_values)) {
return true;
}
// on prepare
if (!empty($group_idclassification)) {
$sql = 'INSERT INTO ' . $table . ' (' . $key . ', object, ' . $col . ', idclassification) VALUES (?, ?, ?, ?)';
} else {
$sql = 'INSERT INTO ' . $table . ' (' . $key . ', object, ' . $col . ') VALUES (?, ?, ?)';
}
$prep = $em->prepare($sql);
foreach ($array_values as $key => $value) {
if (!empty($group_idclassification)) {
$prep_values[] = array($id, $object, $value, $group_idclassification);
} else {
$prep_values[] = array($id, $object, $value);
}
}
foreach ($prep_values as $value) {
$res = $prep->execute($value);
}
} else {
throw new \Exception($this->translator->trans('no_right') . 'setRelationSelectObject');
}
}
// }}}
// {{{ deleteContent()
/** Supprimer un contenu
*
* On verifie que le contenu est utilise et on supprime
* la liaison et le contenu si plus utilise
*
* @access public
* @param string $id id du contentu à supprimer
* @param string $object type de fichier à supprimer (ex : persons, contacts...)
* @param string $idobject id de la personne qui possède le fichier
*/
public function deleteContent($id, $object, $idobject)
{
//le contenu n'est pas utilisé, on peut le supprimer
if (!$this->isUseContent($id)) {
return $this->deleteData($id);
}
//dans touts les cas, supprimer l'enregistrement dans la relation object_file
return $this->deleteDataRelation('object_content', 'idcontent', $id, 'idobject', $idobject, 'object', $object);
}
// }}}
// {{{ isUseContent()
/** Vérifier si un contenu est utilisé par un autre objet
*
*
* @access public
* @param string $id id du contenu à vérifier
*/
public function isUseContent($idcontent)
{
$sql = 'SELECT COUNT(DISTINCT(idobject)) AS total
FROM object_content
WHERE object_content.idcontent=' . (int)$idcontent;
$total = $this->queryOne($sql);
//on a au moins 2 enregistrements liés
return ($total > 1);
}
// }}}
// {{{ countAssociateContent()
/** compter les contenus associes par type
*
* @access public
* @param string|int|array $classification classification recherchee
* @param int $idobject id de l'objet lié
* @param string $objet le type d'objet (personnes, spectacles...)
* @return array
* @see Theatre_Classifications::getAssociate())
*/
public function countAssociateContent($object, $idobject, $classification, $extra = array())
{
// string ou int pour classification
$con = $this->getEntityManager();
$classifications = $con->getRepository(Classifications::class);
if (!empty($extra['publish_status'])) {
$classifications->setPublishStatus($extra['publish_status']);
} else {
$classifications->setPublishStatus($this->publish_status);
}
return $classifications->getAssociate($idobject, $object, $classification, false, $extra);
}
// }}}
// {{{ getAssociateContents()
/** Retourner les contenus lies a un objet
*
* Retourne un tableau associatif des contenus associes
* a un objet
*
* @access public
* @param string $idobject id de l'objet lié
* @param string $objet le type d'objet (personnes, spectacles...)
* @param int|string|array $classification classification(s)
* @param string $params parametre (no_by_lang => 'pas de classement par langue', 'get_files' => demander également les fichiers)
* @see Theatre_Classifications::getAssociate())
*/
public function getAssociateContents($idobject, $object, $classification = null, $params = array())
{
if (!empty($params['group_content'])) {
$fields = 'DISTINCT(c.idcontent)';
} else {
$fields = 'c.idcontent';
}
$fields .= ', c.idextranetuser, c.publish, c.title, c.language, oc.object, oc.idobject, l.fr';
// récupèrer aussi les texts
if (!empty($params['get_texts'])) {
$fields .= ', c.text, c.deck,c.dateinsert';
}
//if(!empty($classification)) {
$fields .= ', cl.classification';
//}
$extra = array(
'tables' => ',languages l',
'where' => 'AND c.language=l.id',
'order' => 'c.language,cc.idclassification,c.dateinsert',
);
// on exclut certains con
if (!empty($params['exclude_contents'])) {
$extra['where'] .= ' AND c.idcontent NOT IN(' . implode(',', $params['exclude_contents']) . ')';
}
if (!empty($params['group_content'])) {
$extra['group_content'] = true;
}
$classifications = $this->getEntityManager()->getRepository(Classifications::class);
$classifications->setPublishStatus($this->publish_status);
$tabcontent = $classifications->getAssociate($idobject, $object, $classification, $fields, $extra);
// si il y a des resultat
if (!empty($tabcontent)) {
// objet traduction
$tr = $this->translator;
// classement par langue
if (empty($params['no_by_lang'])) {
// liste des langues
$sql_lng = 'SELECT c.language
FROM object_content oc, contents c
WHERE oc.idcontent=c.idcontent
AND oc.idobject=\'' . $idobject . '\'
AND oc.object=\'' . $object . '\'
GROUP BY c.language ORDER BY c.language';
$languages = $this->queryAll($sql_lng);
// valeurs
foreach ($languages as $k => $v) {
foreach ($tabcontent as $key => $value) {
if ($tabcontent[$key]['language'] == $v['language']) {
// si text
if (!empty($value['text'])) {
$tabcontent[$key]['text'] = $this->wikiTransform($value['text'], $value['markdown']);
}
$tabcontent[$key]['classification'] = $tr->trans($value['classification']);
$tabcontent[$key]['key_classification'] = $value['classification'];
$tabcontent[$key]['language_term'] = $tabcontent[$key]['fr'];
if ($idobject == 3 && $value['classification'] == 'class_journal') {
$tabcontent[$key]['is_collapse'] = 'display: none;';
}
// est-ce qu'on récup aussi les fichiers
if (!empty($params['get_files']) && array_key_exists('idcontent', $value)) {
$tabcontent[$key]['files'] = $this->getAssociateFileContent($value['idcontent']);
}
if (!empty($params['api'])) {
$tabcontent[$key] = $this->getApiTab($tabcontent[$key]);
}
$tabbylang[$v['language']][] = $tabcontent[$key];
}
}
}
return $tabbylang;
} else {
foreach ($tabcontent as $key => $value) {
$tabcontent[$key]['classification'] = $tr->trans($value['classification']);
$tabcontent[$key]['key_classification'] = $value['classification'];
// est-ce qu'on récup aussi les fichiers
if (!empty($params['get_files']) && array_key_exists('idcontent', $value)) {
$tabcontent[$key]['files'] = $this->getAssociateFileContent($value['idcontent']);
}
}
return $tabcontent;
}
} else {
return null;
}
}
// }}}
// {{{ getAssociateContent()
/** Recuperer un contenu associe
*
* Recuperer les infos sur un seul contenu associe
*
* @access public
* @param int $idobject id de l'objet lié
* @param string $objet le type d'objet (personnes, spectacles...)
* @param string|int|array $classification classification recherchee
* @param array $params_content options supplémentaires (media=>true|false, check_lng=>true|false, wiki_convert=>true|false,get_files=>true|false)
* @return array
* @see Theatre_Classifications::getAssociate())
*/
public function getAssociateContent($idobject, $object, $classification, $params_content = array())
{
$params_content['row'] = true;
// string ou int pour classification
$conn = $this->getEntityManager();
$classifications = $conn->getRepository(Classifications::class);
$classifications->setPublishStatus($this->publish_status);
$data = $classifications->getAssociate($idobject, $object, $classification, '*', $params_content);
if (!empty($data)) {
// les classifications
$classifications_list = $classifications->getObjectClassification($this->__table, $data['idcontent'], $this->getRelationsArray('content_classification'));
$data['classifications'] = $classifications_list;
// est-ce qu'on converti le wiki en HTML ?
if (!empty($params_content['wiki_convert']) && !empty($data['text'])) {
$data['text'] = $this->wikiTransform($data['text'], $data['markdown']);
}
// est-ce qu'on récup aussi les fichiers
if (!empty($params_content['get_files'])) {
if (array_key_exists('idcontent', $data)) {
$datafile = $this->getAssociateFileContent($data['idcontent']);
if (!empty($datafile)) {
$data['files'] = $datafile;
}
}
}
if (!empty($data['dateinsert'])) {
$data['dateinsert_lng'] = $this->getFormeDate($data['dateinsert'], "%A %d %B %Y", true);
$data['dateinsert_lng_withtout_day'] = $this->getFormeDate($data['dateinsert'], "%d %B %Y", true);
}
if (!empty($data['dateupdate'])) {
$data['dateupdate_lng'] = $this->getFormeDate($data['dateupdate'], "%A %d %B %Y", true);
$data['dateupdate_lng_without_day'] = $this->getFormeDate($data['dateupdate'], "%d %B %Y", true);
}
//retourner le contenu
return $data;
}
return null;
}
// }}}
// {{{ getMultimediaContents()
/** Recuperer les contenus associes qui ont un doc multimedia
*
*
* @access public
* @param int $idobject id de l'objet lié
* @param string $objet le type d'objet (personnes, spectacles...)
* @param int|string|array $classification classification(s) (par défaut: toutes)
* @return array
* @see Theatre_Classifications::getAssociate())
*/
public function getMultimediaContents($idobject, $object, $classification = null)
{
$fields = 'c.idcontent, c.title, om.idmultimedia, cl.classification, cc.idclassification';
$extra = array(
'tables' => ',object_multimedia om',
'where' => 'AND c.idcontent=om.idobject AND om.object=\'contents\'',
);
$classifications = $this->getEntityManager()->getRepository(Classifications::class);
$classifications->setPublishStatus($this->publish_status);
$tabcontent = $classifications->getAssociate($idobject, $object, $classification, $fields, $extra);
//retourner les contenus
if (!empty($tabcontent)) {
foreach ($tabcontent as $k => $v) {
$tabcontent[$k]['classification'] = $this->translator->trans($v['classification']);
}
return $tabcontent;
}
return null;
}
// }}}
// {{{ getMoreAssociateContent()
/** Recuperer des contenus associes
*
*
* @access public
* @param int $idobject id de l'objet lié
* @param string $objet le type d'objet (personnes, spectacles...)
* @param int|string|array $classification classification recherchee
* @return array
* @see Theatre_Classifications::getAssociate())
*/
public function getMoreAssociateContent($idobject, $object, $classification, $media = false, $extra = array())
{
$fields = '*';
$extra['media'] = $media;
$conn = $this->getEntityManager();
$classifications = $conn->getRepository(Classifications::class);
$classifications->setPublishStatus($this->publish_status);
$tabcontent = $classifications->getAssociate($idobject, $object, $classification, $fields, $extra);
//retourner les contenus
if (!empty($tabcontent)) {
return $tabcontent;
}
return null;
}
// }}}
// {{{ getAssociateFileContent()
/** Recuperer les fichiers associes a un contenu
*
*
* @access public
* @param int $idobject id de l'objet lié
* @param string $object type d'objet
* @param string|int|array $classification classification recherchee
* @return array
* @see Theatre_Classifications::getAssociate())
*/
public function getAssociateFileContent($idobject, $object = 'contents', $classification = null)
{
$conn = $this->getEntityManager();
$files = $conn->getRepository(Files::class);
$files->setPublishStatus($this->publish_status);
$infos_files = $files->getObjectFiles($idobject, $object, $classification);
return $infos_files;
}
// }}}
// {{{ getObjectTitle()
/** Infos sur l'item recherche
*
* Affichage 'standard' du titre de l'item
*
* @access public
* @param int $idobject Identifiant de l'item
* @param array $infos_object Informations pour construire le titre
* @param array $params Paramétres supplémentaires
* @return string
*/
public function getObjectTitle($idobject = null, $infos_object = array(), $params = array())
{
$title = '';
if (!$idobject) {
$idobject = $this->idcontent;
}
// champs
$fields = array('idcontent', 'title', 'idclassification');
if (!empty($infos_object)) {
foreach ($fields as $k => $v) {
if (array_key_exists($v, $infos_object)) {
$$v = $infos_object[$v];
} else {
$$v = '';
}
}
} else {
//charger les infos que si necessaire
if (empty($this->idclassification)) {
$sql = 'SELECT c.idcontent,c.title,cc.idclassification
FROM contents c, content_classification cc
WHERE cc.idcontent=c.idcontent AND c.idcontent=' . (int)$idobject;
$data = $this->queryRow($sql);
}
foreach ($fields as $k => $v) {
if (!empty($data[$v])) {
$$v = trim($data[$v]);
} else {
$$v = '';
}
}
}
if (empty($idclassification)) {
//error_log('Classification non trouvée'."\n".debug_string_backtrace());
return $title;
}
$query = 'SELECT cl.classification FROM classifications cl WHERE cl.idclassification=' . (int)$idclassification;
$classification = $this->queryOne($query);
$classification = $this->translator->trans($classification);
return $title . ' / ' . $classification;
}
// }}}
// {{{ getIllustrations()
/** Récupère les illustrations
*
* Affiche pour les spectacles, couverture pour les textes
*
* @access public
* @param int $idobject id de l'objet lié
* @param string $object type d'objet
* @param string|int|array $classification classification recherchee
* @return array
* @see getAssociateFileContent()
*/
public function getIllustrations($idobject, $object, $classification, $format = array())
{
// recuperer la couv
$illustrations = $this->getAssociateFileContent($idobject, $object, $classification);
$tab_illustrations = array();
if (!empty($illustrations)) {
// vignettes des illustrations
foreach ($illustrations as $k => $illustration) {
$illustrations[$k]['vignette'] = $this->getThumbnail($illustrations[$k]['file'], 255, 'L');
$illustrations[$k]['vignette_flou'] = $this->getThumbnail($illustrations[$k]['file'], 255, 'L', ['blur' => true]);
}
$tab_illustrations['illustrations'] = $illustrations;
// insctancier l'objet lié et trouver la méthode getVignetteObject
$obj = $this->getEntityManager()->getRepository('TheatreCore\Entity\\' . ucfirst($object));
$methode = 'getVignette' . ucfirst(substr($object, 0, -1));
if (method_exists($obj, $methode)) {
$vignette = $obj->$methode($idobject, $format);
if (!preg_match('/-def\.jpg$/', $vignette)) {
$tab_illustrations['vignette_object'] = $vignette;
}
}
}
return $tab_illustrations;
}
// }}}
// {{{ getListSearch()
/** Liste des contenus associés
*
* Pour les champs Ajax de relations et les recherches diverses
*
* @access public
* @param string $lettres Pour la recherche alphabetique
* @param string $type Pour le type recherche (editeurs, revues...)
* @param string $keywords Recherche par mots cles
* @return array
*/
public function getListSearch($lettre = null, $type = null, $keywords = null, $only = null)
{
$qb = $this->createQueryBuilder('c');
$qb->select('c')
->orderBy('c.dateArticle', 'ASC');
return $qb;
// titre, classification
$sql = 'SELECT c.idcontent, c.title, cl.classification
FROM contents c, classifications cl, content_classification cc
WHERE cc.idclassification=cl.idclassification AND c.idcontent=cc.idcontent';
// par lettre
if (!empty($lettre)) {
$sql .= ' AND LOWER(LEFT(LTRIM(c.title),1))=\'' . $this->escape($lettre) . '\'';
}
// recherche avec un mot cle
$list = $this->splitKeywords($keywords);
if ($list) {
$subsql = '';
if (!empty($list[$this->IDSTR])) {
// mots clés
foreach ($list[$this->IDSTR] as $s) {
if ($subsql) $subsql .= ' AND ';
$subsql .= 'c.title LIKE \'%' . $s . '%\'';
}
}
if (!empty($list[$this->IDNUM])) {
// recherche par numéro d'id pour les mots clés numériques
if ($subsql) $subsql .= ' OR ';
$subsql .= 'c.idcontent IN (' . implode(',', $list[$this->IDNUM]) . ')';
}
if ($subsql) $sql .= ' AND (' . $subsql . ')';
}
$this->query($sql);
$r = array();
while ($this->fetch()) {
$r[$this->idcontent] = $this->title . ' (' . $this->translator->trans($this->classification) . ')';
// si trouvé par id, on l'affiche en fin de titre
if (!empty($list[$this->IDNUM]) && in_array($this->idcontent, $list[$this->IDNUM])) {
$r[$this->idcontent] .= ' - n°' . $this->idcontent;
}
}
$this->free();
return $r;
}
// }}}
// {{{ getRelationsArray()
/** Tableau des relations
*
* @access public
* @param string|int $type le type de champ
*/
public function getRelationsArray($type = null)
{
$array_types = array(
'multimedia_content' => array(// documents multimedia associés
'type_join' => 'method',
'method' => 'getMultimediaList',
/*'type_join' => 'multiple',
'dest_table' => 'multimedias',
'join_table' => 'multimedia_content',
'fields' => array('idmultimedia','sujet_titre','url_clean'),*/
),
'object_multimedia' => array(// documents multimedia associés
'type_join' => 'multiple',
'dest_table' => 'multimedias',
'join_table' => 'object_multimedia',
'fields' => array('idmultimedia', 'sujet_titre', 'url_clean'),
),
'idcontact_newspaper' => array(// journal associé
'type_join' => 'simple',
'dest_table' => 'contacts',
'join_field_src' => 'idcontact_newspaper',
'fields' => array('idcontact', 'organisation', 'url', 'type'),
),
'idcontact_journalist' => array(// journaliste associé
'type_join' => 'simple',
'dest_table' => 'contacts',
'join_field_src' => 'idcontact_journalist',
'fields' => array('idcontact', 'organisation', 'lastname', 'firstname', 'type'),
),
'idcontact' => array(// contact associé
'type_join' => 'multiple',
'dest_table' => 'contacts',
'join_table' => 'content_contact',
// 'join_field_src' => 'idcontact',
// 'join_field_dest' => 'contact_idcontact',
'fields' => array('idcontact', 'organisation', 'url', 'type', 'city'),
),
'content_contact' => array(// contacts associés
'type_join' => 'multiple',
'dest_table' => 'contacts',
'join_table' => 'content_contact',
'fields' => array('idcontact', 'organisation', 'lastname', 'firstname', 'type'),
),
'content_person' => array(// persons associés
'type_join' => 'multiple',
'dest_table' => 'persons',
'join_table' => 'content_person',
'fields' => array('idperson', 'lastname', 'firstname'),
),
'content_classification' => array(// classifications associées
'type_join' => 'multiple',
'dest_table' => 'classifications',
'join_table' => 'content_classification',
'fields' => array('idclassification', 'classification', 'type', 'idgroupclassification'),
),
'content_publisher' => array(// classifications associées
'type_join' => 'multiple',
'dest_table' => 'contacts',
'join_table' => 'content_publisher',
'join_field_dest' => 'idcontact',
'fields' => array('idcontact', 'organisation', 'lastname', 'firstname', 'type'),
),
'content_content' => array(// contenus associés
'type_join' => 'multiple',
'dest_table' => 'contents',
'join_table' => 'object_content',
'fields' => array('idcontent', 'title', 'dateinsert'),
),
'content_template' => array(// classifications associées
'type_join' => 'multiple',
'dest_table' => 'templates',
'join_table' => 'content_template',
'fields' => array('idtemplate', 'description', 'fichier'),
),
/*'object_content' => array(// classifications associées
'type_join' => 'multiple',
'dest_table' => 'content',
'join_table' => 'object_content',
'fields' => array('idcontent','title','subtitle'),
),*/
);
if (!empty($array_types[$type])) {
$array_types[$type]['type'] = $type;
return $array_types[$type];
} else {
return $array_types;
}
}
// }}}
// {{{ getLinkedPerson()
/** Tableau des relations pour les biographies
*
* @access public
* @param string|int $type le type de champ
*/
public function getLinkedPerson($idcontent, $type, $type_value = null, $infos_link = array())
{
$conn = $this->getEntityManager();
if (empty($infos_link)) {
$infos_link = $this->getRelationsArray($type);
}
$infos_linked_obj = $this->getLinkedObject($infos_link, $idcontent, $type, 'contents');
return $infos_linked_obj;
}
// }}}
// {{{ getLinkedContact()
/** Tableau des relations
*
* @access public
* @param string|int $type le type de champ
*/
public function getLinkedContact($idcontent, $type, $type_value = null, $infos_link = array())
{
$conn = $this->getEntityManager();
if (empty($infos_link)) {
$infos_link = $this->getRelationsArray($type);
}
$infos_linked_obj = $this->getLinkedObject($infos_link, $idcontent, $type, 'contents');
foreach ($infos_linked_obj as $k => $v) {
// on veux également le logo
$files = $conn->getRepository(Files::class);
$logos = $files->getObjectFiles($v['idcontact'], 'contacts', 'class_logo_normal');
if (empty($logos)) {
$logos = $files->getObjectFiles($v['idcontact'], 'contacts', 'class_logo_accueil');
}
if (!empty($logos)) {
$logos[0]['vignette'] = $this->getThumbnail($logos[0]['file'], 100, 'thumb');
$logos[0]['vignette_flou'] = $this->getThumbnail($logos[0]['file'], 100, 'thumb', ['blur' => true]);
$logos[0]['vignette_moyenne'] = $this->getThumbnail($logos[0]['file'], 200, 'thumb');
$logos[0]['vignette_moyenne_flou'] = $this->getThumbnail($logos[0]['file'], 200, 'thumb', ['blur' => true]);
$infos_linked_obj[$k]['logo'] = $logos[0];
}
}
return $infos_linked_obj;
}
// }}}
// {{{ getAssociateObjects()
/** Liste des objets associés au contenus par type à partir de l'id du contenu
*
* @access public
* @param string|int $type le type de champ
*/
public function getAssociateObjects($idcontent, $object)
{
$sql = 'SELECT * FROM object_content WHERE 1 ';
// préciser le type d'objet
if (!empty($object)) {
$sql .= ' AND object=\'' . $object . '\' ';
}
// préciser le contenu
if (!empty($idcontent)) {
if (is_array($idcontent)) {
$sql .= ' AND idcontent IN (' . join(',', $idcontent) . ') ';
} else {
$sql .= ' AND idcontent=' . ((int)$idcontent) . ' ';
}
}
return $this->queryAll($sql);
}
// }}}
// {{{ getListContents()
/** Liste des contenus associés
*
* @access public
* @param string|int $type le type de champ
*/
public function getListContents($params_sql = null, $params_display = array(), $debug = false)
{
$count_query = false;
$sql_select = 'SELECT DISTINCT(ct.`idcontent`),ct.`title` ';
// en plus
if (!empty($params_sql['sql']['select'])) {
$sql_select .= ',' . $params_sql['sql']['select'];
}
$sql_from = ' FROM contents ct ';
$sql_where = ' WHERE 1 ';
if (!empty($params_sql['special']['in'])) {
if (is_array($params_sql['special']['in'])) {
$sql_where .= ' AND ct.idcontent IN (' . join(',', $params_sql['special']['in']) . ') ';
} else {
$sql_where .= ' AND ct.idcontent IN (' . $params_sql['special']['in'] . ') ';
}
}
// recherche par classifications
if (!empty($params_sql['special']['by_classification'])) {
$sql_from .= ' JOIN content_classification ccl ON ct.idcontent=ccl.idcontent ';
$sql_where .= ' AND ccl.idclassification IN (' . join(',', $params_sql['special']['by_classification']) . ') ';
}
// on veut l'uilisateur
if (!empty($params_sql['special']['get_extranetuser'])) {
$sql_select .= ',e.login,e.idextranetuser';
$sql_from .= ' JOIN extranetusers e ON ct.idextranetuser=e.idextranetuser ';
}
// recherche par spectacles
if (!empty($params_sql['special']['idspectacle'])) {
if (!is_array($params_sql['special']['idspectacle'])) {
$params_sql['special']['idspectacle'] = (array)$params_sql['special']['idspectacle'];
}
$sql_from .= ' JOIN object_content oc ON ct.`idcontent`=oc.`idcontent` ';
$sql_where .= ' AND oc.`object`=\'spectacles\' AND oc.`idobject` IN (' . join(',', $params_sql['special']['idspectacle']) . ') ';
}
// on ligne ou pas ?
if (!empty($params_sql['special']['get_extranetuser'])) {
if (isset($params_sql['special']['publish']) && $params_sql['special']['publish'] != 2) {
$sql_where .= ' AND ct.publish=' . (int)$params_sql['special']['publish'];
}
if (isset($params_sql['special']['valid']) && $params_sql['special']['valid'] != 2) {
$sql_where .= ' AND ct.valid=' . (int)$params_sql['special']['valid'];
}
}
// recherche par spectacle/contact
if (!empty($params_sql['special']['get_critiques_idcontact'])) {
if (!empty($params_sql['special']['get_critiques_idcontact_count'])) {
$count_query = true;
$sql_select = 'SELECT COUNT(DISTINCT(ct.`idcontent`)) AS total ';
} else {
$sql_select = 'SELECT DISTINCT(ct.`idcontent`), ct.title as title, ct.*, e.`login`, e.`idextranetuser` ';
}
$sql_from = ' FROM contents ct
JOIN content_classification cc ON ct.`idcontent`=cc.`idcontent`
JOIN object_content oc ON ct.`idcontent`=oc.`idcontent`
JOIN spectacles s ON oc.`idobject`=s.`idspectacle`
JOIN schedules sc ON s.`idspectacle`=sc.`idspectacle`
JOIN extranetusers e ON ct.`idextranetuser`=e.`idextranetuser` ';
$sql_where = ' WHERE s.`publish`=1 AND s.`valid`=1
AND ct.`publish`=1 AND ct.`valid`=1
AND cc.`idclassification`=101 ';
if (!empty($params_sql['special']['get_critiques_idcontact_idspectacle'])) {
$sql_where .= '
AND (
sc.`idspectacle` IN (' . $this->arrayToIn($params_sql['special']['get_critiques_idcontact_idspectacle']) . ')
OR (
sc.`idcontact`=' . $params_sql['special']['get_critiques_idcontact'] . '
OR sc.`idcontact_festival`=' . $params_sql['special']['get_critiques_idcontact'] . '
)
)';
} else {
$sql_where = ' WHERE s.`publish`=1 AND s.`valid`=1
AND ct.`publish`=1 AND ct.`valid`=1
AND cc.`idclassification`=101
AND (sc.`idcontact`=' . $params_sql['special']['get_critiques_idcontact'] . '
OR sc.`idcontact_festival`=' . $params_sql['special']['get_critiques_idcontact'] . ') ';
}
// en plus
if (!empty($params_sql['sql']['where'])) {
$sql_where .= $params_sql['sql']['where'];
}
// classement des critiques anté
$sql_order = ' ORDER BY ct.`date_article` DESC';
}
if (!empty($params_sql['special']['get_critiques_publisher'])) {
if (!empty($params_sql['special']['get_critiques_publisher_count'])) {
$count_query = true;
$sql_select = 'SELECT COUNT(DISTINCT(ct.`idcontent`)) AS total ';
} else {
$sql_select = 'SELECT DISTINCT(ct.`idcontent`), ct.title as title, ct.*, e.`login`, e.`idextranetuser` ';
}
$sql_from = ' FROM contents ct
JOIN content_classification cc ON ct.`idcontent`=cc.`idcontent`
JOIN object_content oc ON ct.`idcontent`=oc.`idcontent`
JOIN spectacles s ON oc.`idobject`=s.`idspectacle`
JOIN text_spectacle ts ON s.`idspectacle` = ts.idspectacle
JOIN texts t ON ts.idtext = t.idtext
JOIN schedules sc ON s.`idspectacle`=sc.`idspectacle`
JOIN extranetusers e ON ct.`idextranetuser`=e.`idextranetuser` ';
$sql_where = ' WHERE s.`publish`=1 AND s.`valid`=1
AND ct.`publish`=1 AND ct.`valid`=1
AND cc.`idclassification`=101 ';
$sql_where = ' WHERE s.`publish`=1 AND s.`valid`=1
AND ct.`publish`=1 AND ct.`valid`=1
AND cc.`idclassification`=101
AND t.`idcontact_publisher`=' . $params_sql['special']['get_critiques_publisher'] . ' ';
// en plus
if (!empty($params_sql['sql']['where'])) {
$sql_where .= $params_sql['sql']['where'];
}
// classement des critiques anté
$sql_order = ' ORDER BY ct.`date_article` DESC';
}
// recherche dernières critques
if (!empty($params_sql['special']['get_critiques'])) {
$sql_select = 'SELECT DISTINCT(ct.`idcontent`), ct.title as title, ct.*, e.`login`, e.`idextranetuser` ';
$sql_from = ' FROM contents ct
JOIN content_classification cc ON ct.`idcontent`=cc.`idcontent`
JOIN object_content oc ON ct.`idcontent`=oc.`idcontent`
JOIN spectacles s ON oc.`idobject`=s.`idspectacle`
JOIN schedules sc ON s.`idspectacle`=sc.`idspectacle`
JOIN extranetusers e ON ct.`idextranetuser`=e.`idextranetuser` ';
$sql_where = ' WHERE s.`publish`=1 AND s.`valid`=1
AND ct.`publish`=1 AND ct.`valid`=1
AND cc.`idclassification`=101';
// en plus
if (!empty($params_sql['sql']['where'])) {
$sql_where .= $params_sql['sql']['where'];
}
// classement des critiques anté
$sql_order = ' ORDER BY ct.`date_article` DESC';
}
if (!empty($params_sql['special']['prochains'])) {
$dateend = strftime('%Y-%m-%d', strtotime(date('Y-m-d')) + $params_sql['special']['prochains'] * 24 * 3600);
$sql_where .= ' AND (sc.date BETWEEN \'' . date('Y-m-d') . '\' AND \'' . $dateend . '\') ';
}
// recherche par spectacle/person/text
if (!empty($params_sql['special']['get_critiques_idperson'])) {
if (!empty($params_sql['special']['get_critiques_idperson']['count'])) {
$count_query = true;
$sql_select = 'SELECT COUNT(DISTINCT(ct.`idcontent`)) AS total ';
} else {
$sql_select = 'SELECT DISTINCT(ct.`idcontent`), ct.title as title, ct.*, e.`login`, e.`idextranetuser` ';
}
$sql_from = ' FROM contents ct
JOIN content_classification cc ON ct.`idcontent`=cc.`idcontent`
JOIN object_content oc ON ct.`idcontent`=oc.`idcontent`
JOIN spectacles s ON oc.`idobject`=s.`idspectacle`
JOIN schedules sc ON s.`idspectacle`=sc.`idspectacle`
LEFT JOIN spectacle_person sp ON s.`idspectacle`=sp.`idspectacle`
LEFT JOIN text_spectacle ts ON s.`idspectacle`=ts.`idspectacle`
JOIN extranetusers e ON ct.`idextranetuser`=e.`idextranetuser` ';
$sql_where = ' WHERE s.`publish`=1 AND s.`valid`=1
AND ct.`publish`=1 AND ct.`valid`=1
AND cc.`idclassification`=101
AND
(
sp.`idperson`=' . $params_sql['special']['get_critiques_idperson']['idperson'];
if (!empty($params_sql['special']['get_critiques_idperson']['idtexts_person'])) {
$sql_where .= ' OR ts.`idtext` IN (' . $params_sql['special']['get_critiques_idperson']['idtexts_person'] . ')';
}
$sql_where .= ')';
// en plus
if (!empty($params_sql['sql']['where'])) {
$sql_where .= $params_sql['sql']['where'];
}
}
// classement
if (empty($params_sql['sql']['order']) && empty($sql_order)) {
$sql_order = ' ORDER BY ct.dateinsert DESC';
} elseif (!empty($params_sql['sql']['order'])) {
$sql_order = ' ORDER BY ' . $params_sql['sql']['order'];
}
// en plus
$sql_groupby = '';
if (!empty($params_sql['sql']['groupby'])) {
$sql_groupby .= ' GROUP BY ' . $params_sql['sql']['groupby'];
}
// limite
if (empty($params_sql['sql']['limit'])) {
$limit = ' LIMIT 0,10';
} else {
if ($params_sql['sql']['limit'] != 'all') {
if (is_array($params_sql['sql']['limit'])) {
$limit = ' LIMIT ' . $params_sql['sql']['limit'][0] . ',' . $params_sql['sql']['limit'][1];
} else {
if (strpos($params_sql['sql']['limit'], 'LIMIT') !== false) {
$limit = ' ' . $params_sql['sql']['limit'];
} else {
$limit = ' LIMIT ' . $params_sql['sql']['limit'];
}
}
} else {
$limit = ' ';
}
}
$sql = $sql_select . $sql_from . $sql_where . $sql_groupby . $sql_order . $limit;
// paramètre de debug
$dbg = (empty($params_sql['dbg'])) ? array() : $dbg = $params_sql['dbg'];
if ($count_query) {
return $this->queryOne($sql, $dbg);
} else {
$this->query($sql, $dbg);
$ct = array();
// debug
$start_boucle = microtime(true);
// mettre en forme chaque reponse
while ($this->fetch()) {
$idcontent = $this->idcontent;
$cont = $this->getInfosContentRow($params_display, array('param_sql' => $params_sql));
$ct[$idcontent] = $cont;
}
// libère
$this->free();
// debug de la boucle
if ($this->isTraceMode('list_object')) {
$this->setTraceInfos(array(
'execution_time_boucle' => (microtime(true) - $start_boucle),
'dbg' => $dbg,
));
}
return $ct;
}
}
// }}}
// {{{ getInfosContentRow()
/** Infos standard sur un contenu
*
* Cette méthode peut-être utilisée dans les boucle while/fetch et récupère
* la plus part des infos utiles dans les listes de résultats
*
* @access public
* @param array $format_vignette Format des vignettes
* @return array
*/
public function getInfosContentRow($params_display = null, $params_more = array())
{
$cont = array();
$idcontent = $this->idcontent;
$cont['idcontent'] = $idcontent;
$cont['title'] = $this->title;
if (array_key_exists('shorten_title', $params_display) && $params_display['shorten_title']) {
$cont['shorten_title'] = substr($this->title, 0, 50);
if (strlen($this->title) >= 50) {
$cont['shorten_title'] = $cont['shorten_title'] . " ...";
}
}
// récup aussi les classifications
if (!empty($params_display['get_classifications'])) {
$classifications = $this->getEntityManager()->getRepository(Classifications::class);
$classifications_list = $classifications->getObjectClassification($this->__table, $idcontent, $this->getRelationsArray('content_classification'));
$cont['classifications'] = $classifications_list;
}
// pour les critiques
if (!empty($params_display['get_critiques'])) {
$params_display['fields'] = array(
'title',
'deck',
'idextranetuser',
'login',
'dateinsert',
'date_article',
'writer',
'writer_group',
'writer_group_url',
'link'
);
$params_display['get_infos_spectacle'] = array(
'vignette' => [
'width' => $_ENV['VIGN_SPECTACLE_WIDTH'],
'height' => $_ENV['VIGN_SPECTACLE_HEIGHT'],
'direction' => 'crop'
],
'stylemore' => [
'imagep' => 'width:70px;',
'descsp' => 'width:300px;'
],
);
// recuperer les fichiers associes a ce contenu
$datafile = $this->getAssociateFileContent($cont['idcontent']);
if (!empty($datafile)) {
foreach ($datafile as $k => $v) {
if (!empty($v['is_image'])) {
// dimension des vignettes
$thumb_size = (!empty($params_display['get_critiques_vignette_large'])) ? 350 : 250;
//vignettes des critiques
$cont['vignette'] = $this->getThumbnail(
$v['file'],
$thumb_size,
'thumb'
);
//vignettes flous des critiques
$cont['vignette_flou'] = $this->getThumbnail(
$v['file'],
$thumb_size,
'thumb',
[
'blur' => true,
]
);
}
}
}
}
if (!empty($params_display['fields'])) {
foreach ($params_display['fields'] as $f => $v) {
if (isset($this->$v)) {
$cont[$v] = $this->$v;
}
}
}
if (!empty($cont['dateinsert'])) {
$cont['dateinsert_lng'] = $this->getFormeDate($cont['dateinsert'], "%A %d %B %Y", true);
}
if (!empty($cont['date_article'])) {
$cont['date_article_lng'] = $this->getFormeDate($cont['date_article'], "%A %d %B %Y", true);
$cont['date_article_lng_simple'] = $this->getFormeDate($cont['date_article'], "%a %d %b %Y", true);
}
// trouver les infos sur le spectacle associé
if (!empty($params_display['get_infos_spectacle']) && !empty($this->idspectacle)) {
$conn = $this->getEntityManager();
$spectacles = $conn->getRepository(Spectacles::class);
$cont['spectacles'] = $spectacles->getListSpectacles(array(
'params' => array(
'where' => ' AND s.idspectacle=' . $this->idspectacle
),
'dbg' => array('spec' . $this->idspectacle, 'Spectacle ' . $this->idspectacle),
), $params_display['get_infos_spectacle']);
// une seule url de spectacle
if (!empty($cont['spectacles'])) {
foreach ($cont['spectacles'] as $s) {
$cont['spectacle']['url_clean'] = $s['url_clean'];
}
}
}
return $cont;
}
// }}}
// {{{ getClassifications()
/** récup les classifications d'un fichier
*
* @access public
* @param int $idcontent id du contenu
* @return array
*/
public function getClassifications($idcontent)
{
$conn = $this->getEntityManager();
$classifications = $conn->getRepository(Classifications::class);
return $classifications->getObjectClassification($this->__table, $idcontent, $this->getRelationsArray('content_classification'));
}
// }}}
// }}}
// {{{ getItemsList()
/** récup les classifications d'un fichier
*
* @access public
* @param int $idcontent id du contenu
* @return array
*/
public function getItemsList($idobject = null, $type, $src = null)
{
// trouver les groupe de menu
if ($type == 'group_idclassification') {
$sql = 'SELECT * FROM object_content oc
JOIN classifications cl ON oc.`idclassification`=cl.`idclassification`
WHERE oc.`object`=\'' . $src . '\'
AND oc.`idcontent`=' . $idobject;
$results = $this->queryAll($sql);
$itemslist = array();
if (!empty($results)) {
foreach ($results as $r) {
$itemslist[$r['idclassification']]['id'] = $r['idclassification'];
$itemslist[$r['idclassification']]['view_name'] = $GLOBALS['context']['tr']->trans($r['classification']);
}
}
return $itemslist;
}
return $this->getItemsListTrait($idobject, $type, $src);
}
// }}}
// {{{ getApiFields()
/** champs à renvoyer par l'API
*
* @access public
* @param array tableau de données
*/
protected function getApiFields()
{
return array(
'title' => 'title',
'text' => 'text', // url complète
'dateinsert' => 'insert_date',
);
}
// }}}
// {{{ countContents()
/** statistiques contenus
*
* @access public
*/
public function countContents($params = array())
{
// pour les spectacles d'une période
if (!empty($params['periode'])) {
$sql = 'SELECT COUNT(DISTINCT(c.`idcontent`))
FROM contents c
JOIN object_content oc ON c.`idcontent`=oc.`idcontent`
JOIN schedules sc ON sc.`idspectacle`=oc.`idobject`
JOIN content_classification cc ON c.`idcontent`=cc.`idcontent`
JOIN classifications cl ON cc.`idclassification`=cl.`idclassification`
WHERE oc.`object`=\'spectacles\'
AND sc.`date` BETWEEN \'' . $params['periode']['datestart'] . '\' AND \'' . $params['periode']['dateend'] . '\' ';
} else {
$sql = 'SELECT COUNT(DISTINCT(c.`idcontent`))
FROM contents c ';
$sql .= ' JOIN content_classification cc ON c.`idcontent`=cc.`idcontent`
JOIN classifications cl ON cc.`idclassification`=cl.`idclassification` ';
$sql .= ' WHERE 1 ';
}
$sql .= ' AND cl.`type`=\'contents\' ';
if (!empty($params['classifications']) || !empty($params['idclassifications'])) {
if (!empty($params['classifications'])) {
$sql .= ' AND cl.`classification` IN (' . $this->arrayToIn($params['classifications']) . ') ';
}
if (!empty($params['idclassifications'])) {
$sql .= ' AND cc.`idclassification` IN (' . $this->arrayToIn($params['idclassifications']) . ') ';
}
}
$result = $this->queryOne($sql);
return $result;
}
// }}}
// {{{ countSpectaclesWithContentsFromTexts()
/** si il n'y a pas de contenus-pedagogiques sur un texte, vérifier que les mises en scène liées non pas elle un contenu
*
* @access public
*/
public function countSpectaclesWithContentsFromTexts($idtext, $idclassification)
{
$sql = 'SELECT COUNT(s.`idspectacle`) FROM spectacles s
JOIN text_spectacle ts ON ts.`idspectacle`=s.`idspectacle`
JOIN object_content oc ON oc.`idobject`=s.`idspectacle`
JOIN contents c ON c.`idcontent`=oc.`idcontent`
JOIN content_classification cc ON cc.`idcontent`=c.`idcontent`
WHERE c.publish=1
AND oc.object=\'spectacles\'
AND cc.idclassification=' . $idclassification . '
AND ts.idtext IN (' . $idtext . ')
AND s.`publish`=1 AND s.`valid`=1';
return $this->queryOne($sql);
}
// }}}
}