<?php
namespace TheatreCore\Repository;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
use TheatreCore\Entity\Aides;
use TheatreCore\Entity\TextAide;
use TheatreCore\Entity\Texts;
use TheatreCore\Traits\TheatreTrait;
/**
* TextAideRepository
*
* This class was generated by the Doctrine ORM. Add your own custom
* repository methods below.
*/
class TextAideRepository extends ServiceEntityRepository
{
use TheatreTrait;
private $em;
public $__table;
public $__table_object;
public $__idtable;
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, TextAide::class);
$this->__table = $this->getClassMetadata()->getTableName();
$this->__table_object = $this->getClassMetadata()->name;
$this->__idtable = $this->getClassMetadata()->getSingleIdentifierFieldName();
// $this->__table = 'persons';
// $this->__table_object = '\\App\\Entity\\Persons';
}
// {{{ countAides()
/** Compter le nombre d'aide
*
* @access public
* param int idaide identifiant de l'aide
* @return int
*/
public function countAides($idaide = null, $idtext = null, $params = array())
{
if (!empty($params['count_texts'])) {
$sql = 'SELECT COUNT(DISTINCT(idtext))
FROM ' . $this->__table . '
WHERE ';
} else {
$sql = 'SELECT COUNT(*)
FROM ' . $this->__table . '
WHERE ';
}
if (!empty($idaide)) {
$sql_where[] = 'idaide IN (' . $this->arrayToIn($idaide) . ') ';
}
if (!empty($idtext)) {
$sql_where[] = 'idtext IN (' . $this->arrayToIn($idtext) . ') ';
}
$sql = $sql . join(' AND ', $sql_where);
$nb_aides = $this->queryOne($sql);
if ($nb_aides) {
return $nb_aides;
}
}
// }}}
// {{{ getInfosText_aide()
/** Récupèrer les infos
*
* @access public
* param int idaide identifiant de l'aide
* param int idtext identifiant du texte
* @return array
*/
public function getInfosText_aide($idtextaide)
{
try {
$sql = 'SELECT * FROM ' . $this->__table . '
WHERE idtextaide=' . (int)$idtextaide;
$infos = $this->queryRow($sql);
if (empty($infos)) {
return [];
}
$infos['id'] = $infos['idtextaide'];
if ($infos['date_start'] == '0000-00-00 00:00:00') {
$infos['date_start'] = NULL;
}
if ($infos['date_end'] == '0000-00-00 00:00:00') {
$infos['date_end'] = NULL;
}
foreach (array('start', 'end') as $i) {
if (!empty($infos['date_' . $i])) {
$infos['date_' . $i . '_year'] = substr($infos['date_' . $i], 0, 4);
$infos['date_' . $i . '_month'] = substr($infos['date_' . $i], 5, 2);
$infos['date_' . $i . '_day'] = substr($infos['date_' . $i], 8, 2);
}
}
return $infos;
} catch (\Throwable $e) {
return [];
}
}
// }}}
// {{{ getRelationsArray()
/** Décrit les relations de l'objet
*
* @access public
* @param string|int $type si on souhaite retourner uniquement un type precis
* @see Theatre::getLinkedObject()
*/
public function getRelationsArray($type)
{
$array_types = array(
// jointure avec texts
'idtext' => array(// texts associés
'type_join' => 'simple',
'dest_table' => 'texts',
'join_field_src' => 'idtext',
'fields' => array('idtext', 'title', 'valid', 'url_clean'),
'silent_delete_relation' => true,
'description' => 'Texte associé',
),
// jointure avec aides
'idaide' => array(// aides associés
'type_join' => 'simple',
'dest_table' => 'aides',
'join_field_src' => 'idaide',
'fields' => array('idaide', 'aide', 'idcontact'),
'silent_delete_relation' => true,
'description' => 'Aide associé',
),
);
// retourne uniquement un type précis
if ($type == 'all') {
return $array_types;
}
if (!empty($array_types[$type])) {
$array_types[$type]['type'] = $type;
return $array_types[$type];
} else {
return null;
}
}
// }}}
// {{{ deleteTextAide()
/** Supprime une aide d'un text
*
* @access public
* @param int $idtextaide l'aide du texte
* @return bool
*/
public function deleteTextAide($idtextaide)
{
try {
$sql = 'DELETE FROM ' . $this->__table . '
WHERE idtextaide=' . (int)$idtextaide;
// excute
$res = $this->exec($sql);
return true;
} catch (\Throwable $e) {
return false;
}
}
// }}}
// {{{ getAides()
/** Récupèrer les aides d'un text
*
* @access public
* @param array $aueru Les paramètres pour la requete
* @param bool $debug afficher la requête
* @return string
*/
public function getAides($idtext)
{
return $this->getEntityManager()->getRepository(Aides::class)->getListAides(array(
'special' => array(
'idtexts' => array($idtext)
),
'dbg' => array('les_aides', 'Liste des aides du text ' . $idtext)
));
}
// }}}
// {{{ getListTextAide()
/** Texts avec une aide
*
* @access public
* @param array $params_sql paramètre pour la requete SQL
* @return array
*/
public function getListTextAide($params_sql = null, $params_display = array())
{
// si pas de tableau
if (!isset($params_sql['special'])) {
$params_sql['special'] = array();
}
if (!isset($params_sql['params'])) {
$params_sql['params'] = array();
}
// construction de la requête avec le passage de paramètres
$sql = $this->getSQLTextAide($params_sql);
// paramètre de debug
$dbg = (empty($params_sql['dbg'])) ? array() : $dbg = $params_sql['dbg'];
// executer la requete
$datas = $this->query($sql, $dbg);
// debug
$start_boucle = microtime(true);
$text_aide = array();
// on boucle
foreach ($datas as $data) {
$this->loadResult($data);
if (!empty($params_sql['idtext'])) {
$id = $this->idaide;
} else {
$id = $this->idtext;
}
if (!empty($params_sql['get_texts_with_aides'])) {
$id = $this->idtextaide;
}
if (empty($text_aide[$id])) {
$text_aide[$id] = array(
'idtextaide' => $this->idtextaide,
'id' => $this->idtextaide,
'idtext' => $this->idtext,
'object' => 'text_aide',
'publish' => $this->text_aide_publish,
'valid' => $this->valid,
'idaide' => $this->idaide,
'date_start' => $this->date_start,
'date_end' => $this->date_end,
'year_start' => substr($this->date_start, 0, 4),
'comments' => $this->comments,
'url' => $this->url,
'email_to_get_text' => $this->email_to_get_text,
'url_to_get_text' => $this->url_to_get_text,
'aide' => $this->getEntityManager()->getRepository(Aides::class)->getInfosAide($this->idaide, array(
'get_aidecontact' => true,
'get_contact' => true,
)),
);
if ($this->date_end != '' && $this->date_end != '0000-00-00 00:00:00') {
$text_aide[$id]['year_end'] = substr($this->date_end, 0, 4);
} else {
$text_aide[$id]['year_end'] = null;
}
$idtexts[$this->idtext] = $this->idtext;
}
}
// debug de la boucle
if ($this->isTraceMode('list_object')) {
$this->setTraceInfos(array(
'execution_time_boucle' => microtime(true) - $start_boucle,
'nb_occurence' => count($text_aide),
'dbg' => $dbg,
));
}
$this->free();
$text_aide_group = array();
// récupèrer également les texts
if (!empty($idtexts)) {
$params_sql_texts = array(
'special' => array(
'idtext' => $idtexts,
'join' => true,
),
'dbg' => array('list_texts_text_aide', 'Récupération des texts d\'une aide'),
);
$params_sql_texts['params']['limit'] = 'all';
$texts = $this->getEntityManager()->getRepository(Texts::class)->getListTexts($params_sql_texts, $params_display);
if (!empty($texts)) {
foreach ($texts as $k => $v) {
$texts[$v['idtext']]['publisher_first'] = true;
if (!empty($v['publisher'])) {
foreach ($v['publisher'] as $publisher) {
$texts[$v['idtext']]['publisher'] = $publisher;
}
}
}
foreach ($text_aide as $k => $v) {
if (!empty($texts[$v['idtext']])) {
if (!empty($params_sql['group_by_aide_type_aide_text'])) {
$key = $v['idaide'] . '_' . $v['aide']['type_aide'] . '_' . $v['idtext'];
if (empty($text_aide_group[$key])) {
$text_aide_group[$key] = $v;
}
$text_aide_group[$key]['texts'][$v['idtext']] = $texts[$v['idtext']];
} else {
$text_aide[$k]['texts'][$v['idtext']] = $texts[$v['idtext']];
}
}
}
}
}
if (!empty($params_sql['group_by_aide_type_aide_text'])) {
return $text_aide_group;
} else {
return $text_aide;
}
}
// }}}
// {{{ countListTextAide()
/** Compter la liste des aides
*
* @access public
* @param array $params_sql paramètre pour la requete SQL
* @param bool $debug mode débugage
* @return int
*/
public function countListTextAide($params_sql = null, $debug = false)
{
// si pas de limite => tout
if (empty($params_sql['limit'])) {
$params_sql['limit'] = array('all');
}
$params_sql['count_only'] = true;
// construction de la requête avec le passage de paramètres
return $this->queryOne($this->getSQLTextAide($params_sql, $debug));
}
// }}}
// {{{ getSQLTextAide()
/** Générer la requete standard pour des aides
*
* @access public
* @param array $aueru Les paramètres pour la requete
* @param bool $debug afficher la requête
* @return string
*/
public function getSQLTextAide($query = array(), $debug = false)
{
// on prepare les tableaux
foreach (array('select', 'from', 'where', 'order', 'group', 'limit') as $t) {
if (!empty($query[$t])) {
if (!is_array($query[$t])) {
$query[$t][] = $query[$t];
} else {
$query[$t] = $query[$t];
}
} else {
$query[$t] = array();
}
}
$this->addSQLParams('distinct', $query, 'no_primary');
if (empty($query['group'])) {
$this->addSQLParams('group', $query, 'no');
}
// champs par défaut
$this->addSQLParams('select', $query, array(
array('text_aide' => '*'),
array('aides' => '*')
));
$this->addSQLParams('select_more', $query, ',`text_aide`.`publish` as text_aide_publish');
$this->addSQLParams('from', $query, array(
'aides',
));
$this->addSQLParams('join', $query, array(
'aides.`idaide`' => 'text_aide.`idaide`',
));
// publié ou pas
if (empty($query['publish']) && $_ENV['CHECK_IF_ONLINE']) {
$this->addSQLParams('where', $query, array(
array('aides', 'publish', 1)
));
} elseif (!empty($query['publish'])) {
$this->addSQLParams('where', $query, array(
array('aides', 'publish', $query['publish'])
));
}
// recherche par contact
if (!empty($query['special']['aides'])) {
if (!empty($query['special']['aides']['idcontacts'])) {
$this->addSQLParams('where', $query, array(
array('aides', 'idcontact', $query['special']['aides']['idcontacts']),
));
}
if (!empty($query['special']['aides']['type_aides']) && !empty($query['special']['aides']['idaidecontact'])) {
// $this->addSQLParams('where',$query,array(
// array('aides','idaidecontact',$query['special']['aides']['idaidecontact']),
// array('aides','type_aide',$query['special']['aides']['type_aides']),
// ));
$type_aides = $query['special']['aides']['type_aides'];
if (is_array($type_aides)) {
$type_aides = implode(',', $type_aides);
}
$idaidecontact = $query['special']['aides']['idaidecontact'];
if (is_array($idaidecontact)) {
$idaidecontact = implode(',', $idaidecontact);
}
$query['where'][] = 'AND (aides.`type_aide` IN (' . $type_aides . ') OR aides.`idaidecontact` IN (' . $idaidecontact . ') )';
// $this->whereAdd('idtext IN ('.implode(',', $list[$this->IDNUM]).')', 'OR');
} else {
if (!empty($query['special']['aides']['idaidecontact'])) {
$this->addSQLParams('where', $query, array(
array('aides', 'idaidecontact', $query['special']['aides']['idaidecontact']),
));
}
if (!empty($query['special']['aides']['type_aides'])) {
$this->addSQLParams('where', $query, array(
array('aides', 'type_aide', $query['special']['aides']['type_aides']),
));
}
}
if (!empty($query['special']['aides']['idaide'])) {
$this->addSQLParams('where', $query, array(
array('aides', 'idaide', $query['special']['aides']['idaide']),
));
}
}
// pour un texte
if (!empty($query['idtext'])) {
$this->addSQLParams('where', $query, array(
array('text_aide', 'idtext', $query['idtext'])
));
}
if (!empty($query['idtexts'])) {
$where_more = ' AND texts.idtext IN (' . implode(',', $query['idtexts']) . ')';
$this->addSQLParams('where_more', $query, $where_more);
}
// recherche par année
if (!empty($query['special']['period'])) {
if (!empty($query['special']['period']['year'])) {
$this->addSQLParams('where_more', $query, ' AND YEAR(text_aide.`date_start`)=' . $query['special']['period']['year']);
}
if (!empty($query['special']['period']['years'])) {
$where_more = ' AND YEAR(text_aide.`date_start`) BETWEEN ' . $query['special']['period']['years']['start'] . ' AND ' . $query['special']['period']['years']['end'];
$this->addSQLParams('where_more', $query, $where_more);
}
}
$this->addSQLParams('order', $query, array(
array('text_aide' => 'date_start'),
));
return $this->getSQLObject($query);
}
// }}}
// {{{ hasEmailToDownloadText
/**
* Vérifie qu'un email permettant d'effectuer une demande de téléchargement de texte existe en base de données
*
* @access public
* @param int $idtext
* @return boolean
*/
public function hasEmailToDownloadText($idtext)
{
$sql = 'SELECT COUNT(email_to_get_text)
FROM ' . $this->__table . '
WHERE idtext = ' . intval($idtext) . '
AND email_to_get_text IS NOT NULL
AND publish LIKE 1
';
$count = $this->queryOne($sql);
return $count;
}
// }}}
// {{{ getEmailToDownloadText
/**
* Récupérer une adresse email permettant d'effectuer une demande de téléchatgement de texte
*
* @access public
* @param int $idtext L'id du texte
* @return string|null
*/
public function getEmailToDownloadText($idtext)
{
$sql = $this->createQueryBuilder('t')
->select('t.email_to_get_text')
->where('t.idtext = :idtext')
->andWhere('t.url_to_get_text IS NOT NULL')
->andWhere('t.publish = 1')
->orderBy('t.dateupdate', 'DESC')
->setParameter('idtext', intval($idtext));
$query = $sql->getQuery();
$result = $query->setMaxResults(1)->getOneOrNullResult();
if ($result) {
return $result['email_to_get_text'];
} else {
return null;
}
}
// }}}
// {{{ getUrlToDownloadText
/**
* Récupérer une url permettant de télécharger le texte
*
* @access public
* @param int $idtext L'id du texte
* @return string|null
*/
public function getUrlToDownloadText($idtext)
{
$sql = $this->createQueryBuilder('t')
->select('t.url_to_get_text')
->where('t.idtext = :idtext')
->andWhere('t.url_to_get_text IS NOT NULL')
->andWhere('t.publish = 1')
->orderBy('t.dateupdate', 'DESC')
->setParameter('idtext', intval($idtext));
$query = $sql->getQuery();
$result = $query->setMaxResults(1)->getOneOrNullResult();
if ($result) {
return $result['url_to_get_text'];
} else {
return null;
}
}
// }}}
// {{{ getYears()
/** trouver les années où il y a eu des prix
*
* @access public
* @param array $auery Les paramètres pour la requete
* @param bool $debug afficher la requête
* @return array
*/
public function getYears($params = array(), $debug = false)
{
$query = 'SELECT DISTINCT YEAR(text_aide.`date_start`) AS idannee, YEAR(text_aide.`date_start`) as annee
FROM text_aide
JOIN aides ON text_aide.`idaide`=aides.`idaide`
WHERE 1 ';
// type prix ou nominations
if (!empty($params['special']['aides']['type_aides'])) {
$query .= ' AND aides.`type_aide` IN (' . $this->arrayToIn($params['special']['aides']['type_aides']) . ') ';
}
// contact spécifique
if (!empty($params['special']['aides']['idcontacts'])) {
$query .= ' AND aides.`idcontact` IN (' . $this->arrayToIn($params['special']['aides']['idcontacts']) . ') ';
}
if (!empty($params['special']['aides']['idaidecontact'])) {
if (empty($params['special']['aides']['idaidecontact_or_type_aide'])) {
$query .= ' AND ';
}
$type_aides = $params['special']['aides']['idaidecontact'];
if (is_array($type_aides)) {
$type_aides = implode(',', $type_aides);
}
$query .= ' aides.`idaidecontact` IN (' . $type_aides . ')';
if (!empty($params['special']['aides']['idaidecontact_or_type_aide'])) {
$query .= ' ) ';
}
}
$query .= ' HAVING annee <> 0
ORDER BY annee DESC';
$years = $this->getEntityManager()->getConnection()->fetchAllKeyValue($query);
return !empty($years) ? $years : [];
}
// }}}
// {{{ getIndiceNombresAides()
/** Récupère la liste des aides disponibles allant de 1 à X.
*
* @access public
* @param array $auery Les paramètres pour la requete
* @return array
*/
public function getIndiceNombresAides($params = array())
{
$from_more = '';
if (!empty($params['playlists'])) {
$from_more .= ',`playlists`';
if (!empty($params['playlists']['idclassifications'])) {
$from_more .= ' ,`playlist_classification` ';
}
}
$query = 'SELECT totalCount.totalAide
FROM
(SELECT
COUNT(DISTINCT text_aide.idtext) AS totalText,
COUNT(DISTINCT text_aide.idaide) AS totalAide
FROM
`aides`,
`text_aide`
' . $from_more . '
WHERE
1 AND aides.`publish` = 1';
if (!empty($params['type_aides'])) {
if (empty($params['idaidecontact_or_type_aide'])) {
$query .= ' AND ';
} else {
$query .= ' AND (';
}
$type_aides = $params['type_aides'];
if (is_array($type_aides)) {
$type_aides = implode(',', $type_aides);
}
$query .= ' aides.`type_aide` IN (' . $type_aides . ')';
if (!empty($params['idaidecontact_or_type_aide'])) {
$query .= ' OR ';
}
}
if (!empty($params['playlists'])) {
$query .= ' AND `playlists`.`idtext`=text_aide.idtext ';
if (!empty($params['playlists']['idclassifications'])) {
$query .= ' AND `playlist_classification`.`idplaylist`=`playlists`.`idplaylist` ';
$query .= ' AND `playlist_classification`.idclassification IN (' . $this->arrayToIn($params['playlists']['idclassifications']) . ') ';
}
if (!isset($params['playlists']['publish'])) {
$query .= ' AND `playlists`.`publish`=1 ';
}
}
if (!empty($params['idaidecontact'])) {
if (empty($params['idaidecontact_or_type_aide'])) {
$query .= ' AND ';
}
$type_aides = $params['idaidecontact'];
if (is_array($type_aides)) {
$type_aides = implode(',', $type_aides);
}
$query .= ' aides.`idaidecontact` IN (' . $type_aides . ')';
if (!empty($params['idaidecontact_or_type_aide'])) {
$query .= ' ) ';
}
}
$query .= ' AND aides.`idaide` = text_aide.`idaide`
GROUP BY text_aide.idtext
ORDER BY totalAide DESC
) AS totalCount
GROUP BY totalCount.totalAide
ORDER BY totalCount.totalAide DESC';
// AND YEAR(text_aide.`date_start`) BETWEEN 2017 AND 2019
$topDatas = $this->queryAll($query);
if (!empty($topDatas)) {
// krsort($topDatas);
return $topDatas;
} else {
return array();
}
}
// }}}
}