Symfony Profiler

<?php
namespace App\Repository;
use App\Entity\Dossier;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Common\Persistence\ManagerRegistry;
/**
* @method Dossier|null find($id, $lockMode = null, $lockVersion = null)
* @method Dossier|null findOneBy(array $criteria, array $orderBy = null)
* @method Dossier[] findAll()
* @method Dossier[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class DossierRepository extends ServiceEntityRepository
{
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, Dossier::class);
}
public function countBySubcode($subcode) {
$conn = $this->getEntityManager()->getConnection();
$sql = "SELECT d.id
FROM walkinn_dossier d
WHERE d.code_walkinn LIKE '" . $subcode . "%'
ORDER BY d.id ASC
";
$stmt = $conn->prepare($sql);
$stmt->execute();
$results = $stmt->fetchAll();
return count($results) +1;
}
public function getClientsIds($dossierId)
{
$conn = $this->getEntityManager()->getConnection();
$sql =
" SELECT DISTINCT(segment.client_id)
FROM walkinn_dossier_segment segment
WHERE 1 = 1
AND segment.actif = 1
AND segment.dossier_id = :dossierId
ORDER BY segment.date_depart ASC "
;
$stmt = $conn->prepare($sql);
$stmt->execute(['dossierId' => $dossierId]);
$results = $stmt->fetchAll(\PDO::FETCH_COLUMN);
return $results;
}
public function getSupplierIds($dossierId)
{
$conn = $this->getEntityManager()->getConnection();
$sql =
" SELECT service.prestataire_id
, (SELECT MIN(jour) FROM walkinn_dossier_segment_service_day WHERE dossier_segment_service_id = segment_service.id) AS min_day
, tampon.id AS tampon_id
FROM walkinn_dossier_segment_service segment_service
LEFT JOIN walkinn_service service ON service.id = segment_service.service_id
LEFT JOIN walkinn_dossier_segment segment ON segment.id = segment_service.segment_id
LEFT JOIN walkinn_dossier_commande_fournisseur_tampon tampon ON tampon.dossier_id = :dossierId AND tampon.fournisseur_id = service.prestataire_id
WHERE 1 = 1
AND segment.actif = 1
AND segment.dossier_id = :dossierId
AND service.prestataire_id IS NOT NULL
GROUP BY service.prestataire_id
ORDER BY min_day
"
;
$stmt = $conn->prepare($sql);
$stmt->execute(['dossierId' => $dossierId]);
$results = $stmt->fetchAll();
return $results;
}
public function getSegmentsIds($dossierId)
{
$conn = $this->getEntityManager()->getConnection();
$sql =
" SELECT DISTINCT(segment.id)
FROM walkinn_dossier_segment segment
WHERE 1 = 1
AND segment.actif = 1
AND segment.dossier_id = :dossierId
ORDER BY segment.date_depart ASC "
;
$stmt = $conn->prepare($sql);
$stmt->execute(['dossierId' => $dossierId]);
$results = $stmt->fetchAll(\PDO::FETCH_COLUMN);
return $results;
}
public function countAllFromWeb()
{
$conn = $this->getEntityManager()->getConnection();
$sql = "SELECT wd.id
FROM walkinn_dossier wd
INNER JOIN kernix_reservation kr ON wd.id = kr.dossier_id;
";
$stmt = $conn->prepare($sql);
$stmt->execute();
$results = $stmt->fetchAll();
return count($results);
}
public function infosForCommandesTdb()
{
$conn = $this->getEntityManager()->getConnection();
$sql = "SELECT
u.id AS utilisateur_id,
u.firstname,
COUNT(CASE WHEN wd.dossier_etat_id = 4 THEN 1 END) AS done,
COUNT(CASE WHEN wd.dossier_etat_id != 4 AND wd.dossier_seen = 1 THEN 1 END) AS doing,
COUNT(CASE WHEN wd.dossier_etat_id != 4 AND (wd.dossier_seen IS NULL OR wd.dossier_seen != 1) THEN 1 END) AS todo
FROM
ba_user u
INNER JOIN doli_user du ON du.rowid = u.uid
LEFT JOIN
(
SELECT wd.id, wd.charge_dossier_id, wd.dossier_etat_id, wd.dossier_seen
FROM walkinn_dossier wd
INNER JOIN kernix_reservation kr ON kr.dossier_id = wd.id
WHERE wd.actif = 1 AND wd.is_valide = 1
) AS wd ON wd.charge_dossier_id = u.id
WHERE u.is_active = 1 AND du.statut = 1 AND du.employee = 1
GROUP BY
u.id, u.firstname
ORDER BY
u.firstname;
";
$stmt = $conn->prepare($sql);
$stmt->execute();
$results = $stmt->fetchAll();
return $results;
}
public function findCommandeWebUserToSet($destination_id) {
/**valeur à renvoyer si aucune trouvée */
$fallout_value = null;
$conn = $this->getEntityManager()->getConnection();
/** On récupère l'utilisateur affecté à la destination fournie */
$sql_1 = "SELECT rowid
, ba_user.is_active as user_is_active
, ba_user.id as user_id
FROM doli_user_extrafields
INNER JOIN ba_user ON ba_user.id = doli_user_extrafields.fk_object
WHERE FIND_IN_SET('" . $destination_id . "', demandes_destinations) > 0;"
;
$stmt_1 = $conn->prepare($sql_1);
$stmt_1->execute();
$user_assigned_to_destination = $stmt_1->fetch();
$user_reaffected_to_destination = null;
if ($user_assigned_to_destination) { /** Si je trouve un utilisateur 1 affecté */
/** On récupère un éventuel utilisateur 2 auquel on aurait réaffecté les dossiers de l'utilisateur 1 */
$sql_2 = "SELECT walkinn_user_dossiers_reattribution.id
, u_destination.id as user_id
, u_destination.is_active as user_is_active
FROM walkinn_user_dossiers_reattribution walkinn_user_dossiers_reattribution
INNER JOIN ba_user u_from ON walkinn_user_dossiers_reattribution.user_from_id = u_from.id
INNER JOIN ba_user u_destination ON walkinn_user_dossiers_reattribution.user_destination_id = u_destination.id
WHERE walkinn_user_dossiers_reattribution.active = 1 AND walkinn_user_dossiers_reattribution.user_from_id = " . $user_assigned_to_destination['user_id'] . " AND u_destination.is_active = 1"
;
$stmt_2 = $conn->prepare($sql_2);
$stmt_2->execute();
$user_reaffected_to_destination = $stmt_2->fetch();
if ($user_reaffected_to_destination && $user_reaffected_to_destination['user_is_active']) { /** Si un utilisateur 2 actif a été réaffecté aux dossiers de cette destination, on retourne l'id de cet utilisateur 2 */
$value_to_return = $user_reaffected_to_destination['user_id'];
} else if ($user_assigned_to_destination['user_is_active']) {/** Si aucun utilisateur 2 n'a été réaffecté et que l'utilisateur 1 est actif */
$value_to_return = $user_assigned_to_destination['user_id'];
} else {/** Si Si aucun utilisateur 2 n'a été réaffecté et que l'utilisateur 1 est inactif */
$value_to_return = $fallout_value;
}
} else { /** S'il n'y a aucun utilisateur affecté' */
$value_to_return = $fallout_value;
}
return $value_to_return;
}
public function findVoyageurDolibarrByDossier($dossier, $type = 'email') {
$conn = $this->getEntityManager()->getConnection();
$sql = "SELECT soc.email, soc.rowid
FROM walkinn_dossier d
LEFT JOIN walkinn_dossier_segment ds ON ds.dossier_id = d.id
LEFT JOIN walkinn_dossier_segment_pax dsp ON dsp.segment_id = ds.id
LEFT JOIN doli_socpeople sp ON sp.rowid = dsp.contact_id
LEFT JOIN doli_societe soc ON soc.rowid = sp.fk_soc
LEFT JOIN doli_socpeople_extrafields spe ON spe.fk_object = sp.rowid
WHERE 1 = 1
AND d.id = :id
-- AND soc.email = sp.email
AND soc.email IS NOT NULL
AND soc.email != ''
";
$stmt = $conn->prepare($sql);
$stmt->execute([':id' => $dossier->getId()]);
$results = $stmt->fetchAll();
return $results[0][$type] ?? null;
}
// type : 'supp' ou 'reduc'
public function findLignesForKernix($dossierId, $type = 'supp')
{
$dossier = $this->findOneById($dossierId);
$lignes = [];
if (null !== $dossier->getKernixReservation()) {
foreach ($dossier->getKernixReservation()->getKernixReservationLignes() as $ligne) {
if ($type == $ligne->getTypeLigne()) {
$arr = [
'montant' => abs($ligne->getMontant())
, 'libelle' => $ligne->getLibelle()
, 'quantite' => $ligne->getQuantite()
, 'code' => $ligne->getCode()
];
$lignes[] = $arr;
}
}
}
return $lignes;
}
public function findParticipantsForKernix($dossierId)
{
$conn = $this->getEntityManager()->getConnection();
$sql = "SELECT
sp.rowid AS contact_id
, sp.lastname AS nom
, sp.firstname AS prenom
, sp.address AS `adresse`
, sp.zip AS code_postal
, sp.town AS ville
, cc.code AS pays
, sp.birthday AS date_naissance
, sp.email AS email
, sp.default_lang AS langue
, spe.taille AS taille
, spe.taille_pied AS taille_pied
, spe.taille_pouce AS taille_pouce
, spe.taille_pied_pouce AS taille_pied_pouce
, spe.location_velo AS location_velo
, spe.velo AS velo
, spe.prix_velo AS prix_velo
, spe.souscription_assurance AS souscription_assurance
, spe.location_casque AS location_casque
, IFNULL(dsp.is_sup_single,0) AS supp_single
FROM walkinn_dossier d
LEFT JOIN walkinn_dossier_segment ds ON ds.dossier_id = d.id
INNER JOIN walkinn_dossier_segment_pax dsp ON dsp.segment_id = ds.id
INNER JOIN doli_socpeople sp ON sp.rowid = dsp.contact_id
LEFT JOIN doli_c_country cc ON cc.rowid = sp.fk_pays
LEFT JOIN doli_societe soc ON soc.rowid = sp.fk_soc
LEFT JOIN doli_socpeople_extrafields spe ON spe.fk_object = sp.rowid
-- LEFT JOIN doli_societe_contacts sc ON sc.fk_soc = soc.rowid AND sc.fk_socpeople = sp.rowid
-- LEFT JOIN doli_c_type_contact ctc ON ctc.rowid = sc.fk_c_type_contact
WHERE 1 = 1
AND d.id = :dossierId
AND ds.actif = 1
AND dsp.actif = 1
GROUP BY d.id, sp.rowid
";
$stmt = $conn->prepare($sql);
$stmt->execute([':dossierId' => $dossierId]);
$results = $stmt->fetchAll();
//$return = [];
//foreach ($results as $key => $result) {
// $return[$result['dossier_id']][] = $result;
//}
return $results;
}
public function findAjustementsForKernix($dossierId)
{
$conn = $this->getEntityManager()->getConnection();
$sql = "SELECT
a.id AS ajustement_id
, ds.prix_vente AS montant
, ds.dossier_id AS dossier_id
, ds.id AS segment_id
, ds.date_depart AS date_depart
, DATE_ADD(ds.date_depart, INTERVAL ds.nb_jours DAY) AS date_retour
, ds.nb_jours AS nb_jours
, a.type_id AS type_id
FROM walkinn_dossier_segment ds
INNER JOIN walkinn_ajustement a ON a.prestation_id = ds.voyage_id
INNER JOIN walkinn_voyage_ajustement va ON va.voyage_id = (SELECT MAX(voyage_id)
FROM walkinn_dossier_segment
WHERE walkinn_dossier_segment.dossier_id = :dossierId AND is_prestation_seche = 0)
WHERE 1 = 1
AND ds.dossier_id = :dossierId
AND ds.actif = 1
AND ds.is_prestation_seche = 1
AND va.ajustement_id = a.id
";
$stmt = $conn->prepare($sql);
$stmt->execute([':dossierId' => $dossierId]);
$ajustements = $stmt->fetchAll();
$rows = [];
foreach ($ajustements as $ajustement) {
$dateExtra = (in_array($ajustement['type_id'], [3,5,7])) ? true : false;
$id = $ajustement['ajustement_id'];
$segmentId = $ajustement['segment_id'];
$montant = $ajustement['montant'];
$dates = [];
if (true === $dateExtra) {
$dates[] = $ajustement['date_depart'];
if ($ajustement['nb_jours'] > 1) {
$dates[] = $ajustement['date_retour'];
}
}
$sql = "SELECT
p.contact_id AS contact_id,
sp.service_id AS tarif_id
FROM walkinn_dossier_segment ds
INNER JOIN walkinn_dossier_segment_pax p ON p.segment_id = ds.id
LEFT JOIN walkinn_dossier_segment_service_pax sp ON sp.dossier_segment_pax_id = p.id
WHERE 1 = 1
AND p.actif = 1
AND ds.id = :segmentId
GROUP BY p.contact_id
";
$stmt = $conn->prepare($sql);
$stmt->execute([':segmentId' => $segmentId]);
$participants = $stmt->fetchAll();
if (!isset($rows[$id])) {
$rows[$id] = [
"id" => $id,
"data" => [
[
"montant" => $montant,
"dates" => $dates,
"participants" => $participants,
]
]
];
} else {
$rows[$id]['data'][] = [
"montant" => $montant,
"dates" => $dates,
"participants" => $participants,
];
}
}
return array_values($rows);
}
public function findHebergementsForKernix($dossierId)
{
$conn = $this->getEntityManager()->getConnection();
$sql = "SELECT
r.id, pr.code, r.numero_chambre, p.contact_id AS contact_id
FROM walkinn_dossier_segment ds
INNER JOIN walkinn_dossier_segment_pax p ON p.segment_id = ds.id
INNER JOIN walkinn_dossier_segment_pax_rooming r ON r.id = p.rooming_id
INNER JOIN walkinn_pax_rooming pr ON pr.id = r.pax_rooming_id
WHERE 1 = 1
AND ds.dossier_id = :dossierId
AND ds.actif = 1
AND ds.is_prestation_seche = 0
";
$stmt = $conn->prepare($sql);
$stmt->execute([':dossierId' => $dossierId]);
$results = $stmt->fetchAll();
$rows = [];
foreach ($results as $item) {
$key = $item['code'] . '|' . $item['numero_chambre'];
if (!isset($rows[$key])) {
$n = 0;
$rows[$key] = [
'code' => $item['code'],
'numero_chambre' => $item['numero_chambre'],
'participants' => []
];
}
$rows[$key]['participants'][] = [
'contact_id' => $item['contact_id'],
//'numero' => count($rows[$key]['participants'])
];
}
return array_values($rows);
}
public function findActivitesForKernix($dossierId)
{
$conn = $this->getEntityManager()->getConnection();
$sql = "
SELECT
we.id AS id,
dsp.contact_id AS contact_id
FROM walkinn_service AS serv
INNER JOIN walkinn_dossier_segment_service dss ON dss.service_id = serv.id AND dss.actif=1
INNER JOIN walkinn_dossier_segment ds ON ds.id = dss.segment_id AND ds.dossier_id = :dossierId AND ds.is_prestation_seche = 0 AND ds.actif = 1
INNER JOIN walkinn_voyage v ON v.id = ds.voyage_id
INNER JOIN walkinn_voyage_service vs ON vs.voyage_id = v.id AND vs.service_id = serv.id AND vs.actif = 1
INNER JOIN walkinn_editorial we ON we.service_id = serv.id
INNER JOIN walkinn_dossier_segment_service_pax dssp ON dssp.dossier_segment_service_id = dss.id AND dssp.actif = 1
INNER JOIN walkinn_dossier_segment_pax dsp ON dsp.id = dssp.dossier_segment_pax_id
WHERE 1 = 1
AND serv.type_id = :serviceTypeId";
$stmt = $conn->prepare($sql);
$stmt->execute([':dossierId' => $dossierId, ':serviceTypeId' => \App\Enum\ServiceTypeEnum::ACTIVITE]);
$results = $stmt->fetchAll();
$grouped = [];
foreach ($results as $row) {
$id = $row['id'];
if (!isset($grouped[$id])) {
$grouped[$id] = [
'id' => $id,
'participants' => []
];
}
$grouped[$id]['participants'][] = [
'contact_id' => $row['contact_id'],
//'numero' => count($grouped[$id]['participants'])
];
}
return array_values($grouped);
}
public function findLivraisonPropalForKernix($dossierId)
{
$element = "propal";
return $this->findLivraisonForKernix($dossierId, $element);
}
public function findLivraisonForKernix($dossierId, $element)
{
$code = "SHIPPING";
return $this->findAdresseForKernix($dossierId, $element, $code);
}
public function findFacturationForKernix($dossierId, $element)
{
$code = "BILLING";
return $this->findAdresseForKernix($dossierId, $element, $code);
}
public function findAdresseForKernix($dossierId, $element, $code)
{
$conn = $this->getEntityManager()->getConnection();
$txt = "";
if ($code == 'SHIPPING') {
$txt .= "
, IF( ctc.rowid IS NOT NULL, spe.mode_transport , spe.mode_transport ) AS mode_transport
, IF( ctc.rowid IS NOT NULL, spe.format_livraison, spe.format_livraison ) AS format_livraison
, IF( ctc.rowid IS NOT NULL, spe.lieu_livraison , spe.lieu_livraison ) AS lieu_livraison
, IF( ctc.rowid IS NOT NULL, spe.societe , spe.societe ) AS societe ";
}
$sql = "SELECT
IF( ctc.rowid IS NOT NULL, sp.rowid , sp.rowid ) AS contact_id
, IF( ctc.rowid IS NOT NULL, sp.lastname , sp.lastname ) AS nom
, IF( ctc.rowid IS NOT NULL, sp.firstname , sp.firstname ) AS prenom
, IF( ctc.rowid IS NOT NULL, sp.phone_mobile , sp.phone_mobile ) AS telephone
, IF( ctc.rowid IS NOT NULL, sp.email , sp.email ) AS email
, IF( ctc.rowid IS NOT NULL, sp.address , sp.address ) AS `adresse`
, IF( ctc.rowid IS NOT NULL, sp.zip , sp.zip ) AS code_postal
, IF( ctc.rowid IS NOT NULL, sp.town , sp.town ) AS ville
, IF( ctc.rowid IS NOT NULL, cc.code , sp.fk_pays ) AS pays
, IF( ctc.rowid IS NOT NULL, sp.default_lang , sp.default_lang ) AS langue
{$txt}
-- , ctc.*
FROM walkinn_dossier d
LEFT JOIN walkinn_dossier_segment ds ON ds.dossier_id = d.id
LEFT JOIN walkinn_dossier_segment_pax dsp ON dsp.segment_id = ds.id
LEFT JOIN doli_socpeople sp1 ON sp1.rowid = dsp.contact_id
LEFT JOIN doli_societe soc ON soc.rowid = sp1.fk_soc
LEFT JOIN doli_socpeople sp ON soc.rowid = sp.fk_soc
LEFT JOIN doli_c_country cc ON cc.rowid = sp.fk_pays
LEFT JOIN doli_socpeople_extrafields spe ON spe.fk_object = sp.rowid
LEFT JOIN doli_societe_contacts sc ON sc.fk_soc = soc.rowid AND sc.fk_socpeople = sp.rowid
LEFT JOIN doli_c_type_contact ctc ON ctc.rowid = sc.fk_c_type_contact
WHERE 1 = 1
AND d.id = :dossierId
AND ctc.element = :element
AND ctc.code = :code
GROUP BY d.id, soc.rowid
";
$stmt = $conn->prepare($sql);
$stmt->execute([':dossierId' => $dossierId, ':element' => $element, ':code' => $code]);
$results = $stmt->fetchAll();
return $results;
}
public function findPaymentsForKernix($factureId, $isPending)
{
$conn = $this->getEntityManager()->getConnection();
if ($isPending == 1) {
$txt = "";
$sql = "SELECT
kp.montant AS 'montant',
kp.mode_paiement AS 'mode_paiement',
kp.code_reduction AS 'code_reduction',
kp.emetteur AS 'emetteur'
-- kp.created_at AS 'date'
{$txt}
FROM kernix_reservation kr
INNER JOIN kernix_reservation_paiement kp ON kp.reservation_id = kr.id
WHERE 1 = 1
AND kr.facture_id = :factureId ";
$stmt = $conn->prepare($sql);
$stmt->execute([':factureId' => $factureId]);
}
else {
$txt = "";
$sql = "SELECT
pay.amount AS 'montant',
dp.fk_paiement AS 'mode_paiement',
dp.note AS 'code_reduction',
db.emetteur AS 'emetteur'
-- dp.datec AS 'date'
{$txt}
FROM doli_paiement_facture pay
INNER JOIN doli_paiement dp ON dp.rowid = pay.fk_paiement
LEFT JOIN doli_bank db ON db.rowid = dp.fk_bank
WHERE 1 = 1
AND pay.fk_facture = :rowId ";
$stmt = $conn->prepare($sql);
$stmt->execute([':rowId' => $factureId]);
}
$results = $stmt->fetchAll();
return $results;
}
public function findPaymentsForKernixByDossierId($dossierId, $isPending)
{
$conn = $this->getEntityManager()->getConnection();
if ($isPending == 1) {
$txt = "";
$sql = "SELECT
ROUND(kp.montant,2) AS 'montant',
kp.mode_paiement AS 'mode_paiement',
IFNULL(kp.code_reduction, '') AS 'code_reduction',
IFNULL(kp.emetteur, '') AS 'emetteur'
-- kp.created_at AS 'date'
{$txt}
FROM kernix_reservation kr
INNER JOIN kernix_reservation_paiement kp ON kp.reservation_id = kr.id
WHERE 1 = 1
AND kr.dossier_id = :dossierId ";
$stmt = $conn->prepare($sql);
$stmt->execute([':dossierId' => $dossierId]);
}
else {
$txt = "";
$sql = "SELECT
ROUND(pay.amount,2) AS 'montant',
dp.fk_paiement AS 'mode_paiement',
IFNULL(dp.note, '') AS 'code_reduction',
IFNULL(db.emetteur, '') AS 'emetteur'
-- dp.datec AS 'date'
{$txt}
FROM doli_paiement_facture pay
INNER JOIN doli_paiement dp ON dp.rowid = pay.fk_paiement
LEFT JOIN doli_bank db ON db.rowid = dp.fk_bank
LEFT JOIN kernix_reservation kr ON kr.facture_id = pay.fk_facture
WHERE 1 = 1
AND kr.dossier_id = :dossierId ";
$stmt = $conn->prepare($sql);
$stmt->execute([':dossierId' => $dossierId]);
}
$results = $stmt->fetchAll();
return $results;
}
// /**
// * @return Dossier[] Returns an array of Dossier objects
// */
/*
public function findByExampleField($value)
{
return $this->createQueryBuilder('d')
->andWhere('d.exampleField = :val')
->setParameter('val', $value)
->orderBy('d.id', 'ASC')
->setMaxResults(10)
->getQuery()
->getResult()
;
}
*/
/*
public function findOneBySomeField($value): ?Dossier
{
return $this->createQueryBuilder('d')
->andWhere('d.exampleField = :val')
->setParameter('val', $value)
->getQuery()
->getOneOrNullResult()
;
}
*/
}

src/Repository/DossierRepository.php line 237