<?php
namespace TheatreCore\Repository;
use TheatreCore\Entity\Aides;
use TheatreCore\Entity\SpectacleAide;
use TheatreCore\Entity\Spectacles;
use TheatreCore\Traits\TheatreTrait;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
/**
* SpectacleAideRepository
*
* This class was generated by the Doctrine ORM. Add your own custom
* repository methods below.
*/
class SpectacleAideRepository extends ServiceEntityRepository
{
use TheatreTrait;
private $em;
public $__table;
public $__table_object;
public $__idtable;
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, SpectacleAide::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';
}
// get list of nb of existing recommandations for all spectacle
public function getIndiceNombresRecommandations(): array
{
$query = "SELECT totalCount.totalRecommandation
FROM
(SELECT
COUNT(DISTINCT spectacle_aide.idspectacle) AS totalSpectacle,
COUNT(DISTINCT spectacle_aide.idaide) AS totalRecommandation
FROM
`aides`,
`spectacle_aide`,
`aidecontacts`
WHERE
aides.`publish` = 1
AND aides.`idaide` = spectacle_aide.`idaide`
AND aidecontacts.`title` LIKE 'Spectacle recommandé'
GROUP BY spectacle_aide.idspectacle
ORDER BY totalRecommandation DESC
) AS totalCount
GROUP BY totalCount.totalRecommandation
ORDER BY totalCount.totalRecommandation DESC";
$data = $this->queryAll($query);
if (!empty($data)) {
$return = $data;
} else {
$return = [];
}
return $return;
}
// {{{ countAides()
/** Compter le nombre d'aide
*
* @access public
* param int idaide identifiant de l'aide
* @return int
*/
public function countAides($idaide = null, $idspectacle = null, $params = array())
{
if (!empty($params['count_spectacles'])) {
$sql = 'SELECT COUNT(DISTINCT(idspectacle))
FROM ' . $this->__table . '
WHERE ';
} else {
$sql = 'SELECT COUNT(*)
FROM ' . $this->__table . '
WHERE ';
}
if (!empty($idaide)) {
$sql_where[] = 'idaide IN (' . $this->arrayToIn($idaide) . ') ';
}
if (!empty($idspectacle)) {
$sql_where[] = 'idspectacle IN (' . $this->arrayToIn($idspectacle) . ') ';
}
$sql = $sql . join(' AND ', $sql_where);
$nb_aides = $this->queryOne($sql);
return $nb_aides;
}
// }}}
// {{{ getInfosSpectacle_aide()
/** R&écupèrer les infos
*
* @access public
* param int idaide identifiant de l'aide
* param int idspectacle identifiant du spectaclee
* @return array
*/
public function getInfosSpectacle_aide($idspectacleaide)
{
try {
$sql = 'SELECT * FROM ' . $this->__table . '
WHERE idspectacleaide=' . (int)$idspectacleaide;
$infos = $this->queryRow($sql);
if (empty($infos)) {
return [];
}
$infos['id'] = $this->idspectacleaide;
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 spectacles
'idspectacle' => array(// spectacles associés
'type_join' => 'simple',
'dest_table' => 'spectacles',
'join_field_src' => 'idspectacle',
'fields' => array('idspectacle', '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;
}
}
// }}}
// {{{ deleteAide()
/** Supprime une aide d'un spectacle
*
* @access public
* @param int $idaide l'aide
* @param int $idspectacle le spectacle
* @return bool
*/
public function deleteAide($idaide, $idspectacle)
{
try {
$sql = 'DELETE FROM ' . $this->__table . '
WHERE idaide=' . (int)$idaide . '
AND idspectacle=' . (int)$idspectacle;
// excute
$res = $this->exec($sql);
return true;
} catch (\Throwable $e) {
return false;
}
}
// }}}
// {{{ getAides()
/** Récupèrer les aides d'un spectacle
*
* @access public
* @param array $aueru Les paramètres pour la requete
* @param bool $debug afficher la requête
* @return string
*/
public function getAides($idspectacle)
{
return $this->getEntityManager()->getRepository(Aides::class)->getListAides(array(
'special' => array(
'idspectacles' => array($idspectacle)
),
'dbg' => array('les_aides', 'Liste des aides du spectacle ' . $idspectacle)
));
}
// }}}
// {{{ getListSpectacleAide()
/** Spectacles avec une aide
*
* @access public
* @param array $params_sql paramètre pour la requete SQL
* @return array
*/
public function getListSpectacleAide($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->getSQLSpectacleAide($params_sql);
// paramètre de debug
$dbg = (empty($params_sql['dbg'])) ? array() : $dbg = $params_sql['dbg'];
// executer la requete
$this->query($sql, $dbg);
// debug
$start_boucle = microtime(true);
$spectacle_aide = array();
$conn = $this->getEntityManager();
// on boucle
while ($this->fetch()) {
if (!empty($params_sql['idspectacle'])) {
$id = $this->idaide;
} else {
$id = $this->idspectacle;
}
if (!empty($params_sql['get_spectacles_with_aides'])) {
$id = $this->idspectacleaide;
}
$spectacle_aide[$id] = array(
'idspectacleaide' => $this->idspectacleaide,
'id' => $this->idspectacleaide,
'idspectacle' => $this->idspectacle,
'object' => 'spectacle_aide',
'publish' => $this->spectacle_aide_publish,
'valid' => $this->valid,
'idaide' => $this->idaide,
'date_start' => $this->date_start,
'date_end' => $this->date_end,
'comments' => $this->comments,
'url' => $this->url,
'year_start' => substr($this->date_start, 0, 4),
);
if (!empty($params_sql['recommandations_only'])) {
$spectacle_aide[$id]["aide"] = [];
} else {
$spectacle_aide[$id]["aide"] = $conn->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') {
$spectacle_aide[$id]['year_end'] = substr($this->date_end, 0, 4);
} else {
$spectacle_aide[$id]['year_end'] = null;
}
$idspectacles[$this->idspectacle] = $this->idspectacle;
}
// debug de la boucle
if ($this->isTraceMode('list_object')) {
$this->setTraceInfos(array(
'execution_time_boucle' => microtime(true) - $start_boucle,
'nb_occurence' => count($spectacle_aide),
'dbg' => $dbg,
));
}
$this->free();
$spectacle_aide_group = array();
// récupèrer également les spectacles
if (!empty($idspectacles) && count($idspectacles) > 0) {
$params_sql_spectacles = array(
'special' => array(
'idspectacle' => $idspectacles,
),
'params' => array(
'limit' => 'all'
),
'dbg' => array('list_spectacles_spectacle_aide', 'Récupération des spectacles d\'une aide'),
);
$spectacles = $conn->getRepository(Spectacles::class)->getListSpectacles($params_sql_spectacles, null, false, ['affiche_date_creation' => true, 'carrer_couleurs' => true, 'allauthors' => true]);
foreach ($spectacle_aide as $k => $v) {
if (!empty($spectacles[$v['idspectacle']])) {
$spectacles[$v['idspectacle']]['display']['affiche_date_creation'] = true;
$spectacles[$v['idspectacle']]['annee_creation'] = $conn->getRepository(Spectacles::class)->getFormeDate($spectacles[$v['idspectacle']]['creation_date'], '%Y', true); //%a %d/%m/%Y
if (!empty($params_sql['group_by_aide_type_aide_spectacle'])) {
$key = $v['idaide'] . '_' . $v['aide']['type_aide'] . '_' . $v['idspectacle'];
if (empty($spectacle_aide_group[$key])) {
$spectacle_aide_group[$key] = $v;
}
$spectacle_aide_group[$key]['spectacles'][$v['idspectacle']] = $spectacles[$v['idspectacle']];
} else {
$spectacle_aide[$k]['spectacles'][$v['idspectacle']] = $spectacles[$v['idspectacle']];
}
}
}
}
if (!empty($params_sql['group_by_aide_type_aide_spectacle'])) {
return $spectacle_aide_group;
} else {
return $spectacle_aide;
}
}
// }}}
// {{{ countListSpectacleAide()
/** 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 countListSpectacleAide($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->getSQLSpectacleAide($params_sql, $debug));
}
// }}}
// {{{ getSQLSpectacleAide()
/** 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 getSQLSpectacleAide($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');
$this->addSQLParams('group', $query, 'no');
// champs par défaut
$this->addSQLParams('select', $query, array(
array('spectacle_aide' => '*'),
array('aides' => '*')
));
$this->addSQLParams('select_more', $query, ',`spectacle_aide`.`publish` as spectacle_aide_publish');
$this->addSQLParams('from', $query, array(
'aides',
));
$this->addSQLParams('join', $query, array(
'aides.`idaide`' => 'spectacle_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'])) {
$this->addSQLParams('where', $query, array(
array('aides', 'type_aide', $query['special']['aides']['type_aides']),
));
}
if (!empty($query['special']['aides']['idaidecontact'])) {
$this->addSQLParams('where', $query, array(
array('aides', 'idaidecontact', $query['special']['aides']['idaidecontact']),
));
}
if (!empty($query['special']['aides']['idaide'])) {
$this->addSQLParams('where', $query, array(
array('aides', 'idaide', $query['special']['aides']['idaide']),
));
}
}
// pour un spectacle
if (!empty($query['idspectacle'])) {
$this->addSQLParams('where', $query, array(
array('spectacle_aide', 'idspectacle', $query['idspectacle'])
));
}
// recherche par année
if (!empty($query['special']['period'])) {
if (!empty($query['special']['period']['year'])) {
$this->addSQLParams('where_more', $query, ' AND YEAR(spectacle_aide.`date_start`)=' . $query['special']['period']['year']);
}
}
$this->addSQLParams('order', $query, array(
array('spectacle_aide' => 'date_start'),
));
return $this->getSQLObject($query);
}
// }}}
// {{{ 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(spectacle_aide.`date_start`), YEAR(spectacle_aide.`date_start`) as annee
FROM spectacle_aide
JOIN aides ON spectacle_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']) . ') ';
}
$query .= ' HAVING annee <> 0
ORDER BY annee DESC';
$years = $this->getEntityManager()->getConnection()->fetchAllKeyValue($query);
return !empty($years) ? $years : [];
}
// }}}
}