Symfony Profiler

<?php
namespace App\Service\Kanban;
use App\Entity\GestionComerciale\ArticleCommande;
use App\Entity\GestionComerciale\Commande;
use App\Entity\Kanban\Equipe;
use App\Entity\Notes\Note;
use App\Entity\Utilisateur\Contact;
use Doctrine\ORM\EntityManager;
use Doctrine\ORM\EntityManagerInterface;
use Symfony\Component\DependencyInjection\Container;
use App\Entity\Kanban\Favoris;
use App\Entity\Kanban\Fiche;
use App\Entity\Kanban\Kanban;
use App\Entity\Kanban\Region;
use App\Entity\Kanban\Raison;
use App\Entity\Kanban\Colonne;
use App\Entity\Utilisateur\Utilisateur;
use App\Entity\Notes\Categorie;
use App\Entity\Clients\Client;
use Symfony\Component\Form\FormError;
use Symfony\Component\Security\Core\Security;
use Symfony\Component\Validator\ExecutionContext;
class KanbanService {
private $em;
private Security $security;
public function __construct(EntityManagerInterface $em, Security $security)
{
$this->em = $em;
$this->security = $security;
}
public function est_autorise_voir_fiche($id_fiche) {
$user = $this->security->getUser();
if(is_object($user->getType()) and ($user->getType()->getId() == "1" or $user->getType()->getId() == "2")) {
return true;
}
else if(is_object($user->getType())) {
$repo_fiche = $this->em->getRepository(Fiche::class);
$fiche = $repo_fiche->find($id_fiche);
if($fiche->getUtilisateur()->getId() == $user->getId()) {
return true;
}
else {
$repo_equipe = $this->em->getRepository(Equipe::class);
$equipier_obj = $repo_equipe->findOneBy(["fiche" =>$fiche, "utilisateur" =>$user]);
if(is_object($equipier_obj)) return true;
}
}
return false;
}
public function getTunnels() {
$repo_kanban = $this->em->getRepository(Kanban::class);
$tunnels = $repo_kanban->getTunnels();
return $tunnels;
}
public function equipierDebutee($parametres = []) {
$conn = $this->em->getConnection();
$sql = 'SELECT utilisateur_id,sum(budget) as budget_total
FROM
kanban__fiche
WHERE
dateSuppression is null
AND
date >= "'.$parametres["debut_filtre_date"].' 00:00:00"
AND
date <= "'.$parametres["fin_filtre_date"].' 23:59:59"
GROUP BY utilisateur_id
ORDER BY budget_total desc
';
$stmt = $conn->executeQuery($sql);
$total = $stmt->fetchAllAssociative();
//print_r($total);
//echo "<br/><br/>";
if(is_array($total) && count($total)>0) {
return $total[0];
}
return [];
//return $total[0]["total"];
}
public function equipierConclue($parametres = []) {
$conn = $this->em->getConnection();
$sql = 'SELECT utilisateur_gagne_perdu_id as utilisateur_id,sum(budget) as budget_total
FROM
kanban__fiche
WHERE
utilisateur_gagne_perdu_id is not NULL
AND
statut_fiche_id = 1
AND
dateSuppression is null
AND
date >= "'.$parametres["debut_filtre_date"].' 00:00:00"
AND
date <= "'.$parametres["fin_filtre_date"].' 23:59:59"
GROUP BY utilisateur_gagne_perdu_id
ORDER BY budget_total desc
';
$stmt = $conn->executeQuery($sql);
$total = $stmt->fetchAllAssociative();
//print_r($total);
//echo "<br/><br/>";
if(is_array($total) && count($total)>0) {
return $total[0];
}
return [];
//return $total[0]["total"];
}
public function totalGagneesAffaireCa($parametres = []) {
$user = $this->security->getUser();
$where_equipiers = "";
$where_activite = "";
$where_type_produit = "";
$join_client = "";
if (array_key_exists('typesProduits', $parametres) && count($parametres["typesProduits"]) > 0) {
//activite
$in_type_produit_id = "";
for($o=0;$o<count($parametres["typesProduits"]);$o++) {
$in_type_produit_id .= $parametres["typesProduits"][$o].",";
}
if($in_type_produit_id != "") {
$in_type_produit_id = trim($in_type_produit_id,",,");
$where_type_produit = " AND f.id IN (SELECT distinct fiche_id FROM kanban__fiche_type_produit WHERE type_produit_id IN (".$in_type_produit_id."))";
}
}
if (array_key_exists('activite', $parametres) && $parametres["activite"] > 0) {
//activite
$join_client = "LEFT JOIN client__client as c ON f.client_id = c.id";
$where_activite = " AND c.ape_id = ".$parametres["activite"];
}
if (array_key_exists('equipiers', $parametres) && count($parametres["equipiers"]) > 0) {
foreach($parametres["equipiers"] as $e) {
$where_equipiers .= $e.",";
}
}
if($where_equipiers != "") {
$where_equipiers = trim($where_equipiers,',');
$where_equipiers = " AND utilisateur_id IN (".$where_equipiers.")";
}
if(!is_object($user->getType()) or ($user->getType()->getId() != "1" and $user->getType()->getId() != "2")) {
$where_equipiers = " AND utilisateur_id IN (".$user->getId().")";
}
$where_tunnel = "";
if (array_key_exists('tunnel', $parametres) && count($parametres["tunnel"]) > 0) {
$in_kanban = "";
$repo_kanban = $this->em->getRepository(Kanban::class);
$repo_colonnes = $this->em->getRepository(Colonne::class);
$colonnes_kanban = $repo_colonnes->findBy(["kanban" =>$parametres["tunnel"]]);
$kanban_obj = $repo_kanban->find($parametres["tunnel"]);
$where = "";
$in_colonne = "";
if(is_object($kanban_obj->getColonne()))
{
$colids = [];
$colids = $repo_colonnes->findBy(["kanban" =>$kanban_obj]);
foreach($colids as $coll) {
$in_colonne .= $coll->getId().",";
}
$in_colonne = trim($in_colonne,",,");
$where_tunnel = ' AND colonne_id IN ('.$in_colonne.')';
}
else {
$where_tunnel = 'AND kanban_id IN ('.$parametres["tunnel"].')';
}
}
$sql_region = "";
if (array_key_exists('region', $parametres) and $parametres['region']!="") {
$sql_region = " AND f.region_id = ".$parametres['region'];
//kanban__fiche
}
$conn = $this->em->getConnection();
$sql = 'SELECT SUM(budget) as total
FROM
kanban__fiche as f
'.$join_client.'
WHERE
statut_fiche_id = 1
AND
dateSuppression is null
AND
f.date >= "'.$parametres["debut_filtre_date"].' 00:00:00"
AND
f.date <= "'.$parametres["fin_filtre_date"].' 23:59:59"
'.$where_equipiers.'
'.$where_tunnel.'
'.$where_activite.'
'.$where_type_produit.'
'.$sql_region.'
';
$stmt = $conn->executeQuery($sql);
$total = $stmt->fetchAllAssociative();
return $total[0]["total"];
}
public function totalGagneesAffaire($parametres = []) {
$user = $this->security->getUser();
$where_equipiers = "";
$where_activite = "";
$join_client = "";
$where_type_produit = "";
if (array_key_exists('typesProduits', $parametres) && count($parametres["typesProduits"]) > 0) {
//activite
$in_type_produit_id = "";
for($o=0;$o<count($parametres["typesProduits"]);$o++) {
$in_type_produit_id .= $parametres["typesProduits"][$o].",";
}
if($in_type_produit_id != "") {
$in_type_produit_id = trim($in_type_produit_id,",,");
$where_type_produit = " AND f.id IN (SELECT distinct fiche_id FROM kanban__fiche_type_produit WHERE type_produit_id IN (".$in_type_produit_id."))";
}
}
if (array_key_exists('activite', $parametres) && $parametres["activite"] > 0) {
//activite
$join_client = "LEFT JOIN client__client as c ON f.client_id = c.id";
$where_activite = " AND c.ape_id = ".$parametres["activite"];
}
if (array_key_exists('equipiers', $parametres) && count($parametres["equipiers"]) > 0) {
foreach($parametres["equipiers"] as $e) {
$where_equipiers .= $e.",";
}
}
if($where_equipiers != "") {
$where_equipiers = trim($where_equipiers,',');
$where_equipiers = " AND utilisateur_id IN (".$where_equipiers.")";
}
if(!is_object($user->getType()) or ($user->getType()->getId() != "1" and $user->getType()->getId() != "2")) {
$where_equipiers = " AND utilisateur_id IN (".$user->getId().")";
}
$where_tunnel = "";
if (array_key_exists('tunnel', $parametres) && count($parametres["tunnel"]) > 0) {
$in_kanban = "";
$repo_kanban = $this->em->getRepository(Kanban::class);
$repo_colonnes = $this->em->getRepository(Colonne::class);
$colonnes_kanban = $repo_colonnes->findBy(["kanban" =>$parametres["tunnel"]]);
$kanban_obj = $repo_kanban->find($parametres["tunnel"]);
$where = "";
$in_colonne = "";
if(is_object($kanban_obj->getColonne()))
{
$colids = [];
$colids = $repo_colonnes->findBy(["kanban" =>$kanban_obj]);
foreach($colids as $coll) {
$in_colonne .= $coll->getId().",";
}
$in_colonne = trim($in_colonne,",,");
$where_tunnel = ' AND colonne_id IN ('.$in_colonne.')';
}
else {
$where_tunnel = 'AND kanban_id IN ('.$parametres["tunnel"].')';
}
}
$sql_region = "";
if (array_key_exists('region', $parametres) and $parametres['region']!="") {
$sql_region = " AND f.region_id = ".$parametres['region'];
//kanban__fiche
}
$conn = $this->em->getConnection();
$sql = 'SELECT count(*) as total
FROM
kanban__fiche as f
'.$join_client.'
WHERE
statut_fiche_id = 1
AND
dateSuppression is null
AND
f.date >= "'.$parametres["debut_filtre_date"].' 00:00:00"
AND
f.date <= "'.$parametres["fin_filtre_date"].' 23:59:59"
'.$where_equipiers.'
'.$where_tunnel.'
'.$where_activite.'
'.$where_type_produit.'
'.$sql_region.'
';
$stmt = $conn->executeQuery($sql);
$total = $stmt->fetchAllAssociative();
return $total[0]["total"];
}
public function totalPerduesAffaireCa($parametres = []) {
$user = $this->security->getUser();
$where_equipiers = "";
$where_activite = "";
$where_type_produit = "";
$join_client = "";
if (array_key_exists('typesProduits', $parametres) && count($parametres["typesProduits"]) > 0) {
//activite
$in_type_produit_id = "";
for($o=0;$o<count($parametres["typesProduits"]);$o++) {
$in_type_produit_id .= $parametres["typesProduits"][$o].",";
}
if($in_type_produit_id != "") {
$in_type_produit_id = trim($in_type_produit_id,",,");
$where_type_produit = " AND f.id IN (SELECT distinct fiche_id FROM kanban__fiche_type_produit WHERE type_produit_id IN (".$in_type_produit_id."))";
}
}
if (array_key_exists('activite', $parametres) && $parametres["activite"] > 0) {
//activite
$join_client = "LEFT JOIN client__client as c ON f.client_id = c.id";
$where_activite = " AND c.ape_id = ".$parametres["activite"];
}
if (array_key_exists('equipiers', $parametres) && count($parametres["equipiers"]) > 0) {
foreach($parametres["equipiers"] as $e) {
$where_equipiers .= $e.",";
}
}
if($where_equipiers != "") {
$where_equipiers = trim($where_equipiers,',');
$where_equipiers = " AND utilisateur_id IN (".$where_equipiers.")";
}
if(!is_object($user->getType()) or ($user->getType()->getId() != "1" and $user->getType()->getId() != "2")) {
$where_equipiers = " AND utilisateur_id IN (".$user->getId().")";
}
$where_tunnel = "";
if (array_key_exists('tunnel', $parametres) && count($parametres["tunnel"]) > 0) {
$in_kanban = "";
$repo_kanban = $this->em->getRepository(Kanban::class);
$repo_colonnes = $this->em->getRepository(Colonne::class);
$colonnes_kanban = $repo_colonnes->findBy(["kanban" =>$parametres["tunnel"]]);
$kanban_obj = $repo_kanban->find($parametres["tunnel"]);
$where = "";
$in_colonne = "";
if(is_object($kanban_obj->getColonne()))
{
$colids = [];
$colids = $repo_colonnes->findBy(["kanban" =>$kanban_obj]);
foreach($colids as $coll) {
$in_colonne .= $coll->getId().",";
}
$in_colonne = trim($in_colonne,",,");
$where_tunnel = ' AND colonne_id IN ('.$in_colonne.')';
}
else {
$where_tunnel = 'AND kanban_id IN ('.$parametres["tunnel"].')';
}
}
$sql_region = "";
if (array_key_exists('region', $parametres) and $parametres['region']!="") {
$sql_region = " AND f.region_id = ".$parametres['region'];
//kanban__fiche
}
$conn = $this->em->getConnection();
$sql = 'SELECT SUM(budget) as total
FROM
kanban__fiche as f
'.$join_client.'
WHERE
statut_fiche_id = 2
AND
dateSuppression is null
AND
f.date >= "'.$parametres["debut_filtre_date"].' 00:00:00"
AND
f.date <= "'.$parametres["fin_filtre_date"].' 23:59:59"
'.$where_equipiers.'
'.$where_tunnel.'
'.$where_activite.'
'.$where_type_produit.'
'.$sql_region.'
';
$stmt = $conn->executeQuery($sql);
$total = $stmt->fetchAllAssociative();
return $total[0]["total"];
}
public function totalPerduesAffaire($parametres = []) {
$user = $this->security->getUser();
$where_equipiers = "";
$where_activite = "";
$where_type_produit = "";
$join_client = "";
if (array_key_exists('typesProduits', $parametres) && count($parametres["typesProduits"]) > 0) {
//activite
$in_type_produit_id = "";
for($o=0;$o<count($parametres["typesProduits"]);$o++) {
$in_type_produit_id .= $parametres["typesProduits"][$o].",";
}
if($in_type_produit_id != "") {
$in_type_produit_id = trim($in_type_produit_id,",,");
$where_type_produit = " AND f.id IN (SELECT distinct fiche_id FROM kanban__fiche_type_produit WHERE type_produit_id IN (".$in_type_produit_id."))";
}
}
if (array_key_exists('activite', $parametres) && $parametres["activite"] > 0) {
//activite
$join_client = "LEFT JOIN client__client as c ON f.client_id = c.id";
$where_activite = " AND c.ape_id = ".$parametres["activite"];
}
if (array_key_exists('equipiers', $parametres) && count($parametres["equipiers"]) > 0) {
foreach($parametres["equipiers"] as $e) {
$where_equipiers .= $e.",";
}
}
if($where_equipiers != "") {
$where_equipiers = trim($where_equipiers,',');
$where_equipiers = " AND utilisateur_id IN (".$where_equipiers.")";
}
if(!is_object($user->getType()) or ($user->getType()->getId() != "1" and $user->getType()->getId() != "2")) {
$where_equipiers = " AND utilisateur_id IN (".$user->getId().")";
}
$where_tunnel = "";
if (array_key_exists('tunnel', $parametres) && count($parametres["tunnel"]) > 0) {
$in_kanban = "";
$repo_kanban = $this->em->getRepository(Kanban::class);
$repo_colonnes = $this->em->getRepository(Colonne::class);
$colonnes_kanban = $repo_colonnes->findBy(["kanban" =>$parametres["tunnel"]]);
$kanban_obj = $repo_kanban->find($parametres["tunnel"]);
$where = "";
$in_colonne = "";
if(is_object($kanban_obj->getColonne()))
{
$colids = [];
$colids = $repo_colonnes->findBy(["kanban" =>$kanban_obj]);
foreach($colids as $coll) {
$in_colonne .= $coll->getId().",";
}
$in_colonne = trim($in_colonne,",,");
$where_tunnel = ' AND colonne_id IN ('.$in_colonne.')';
}
else {
$where_tunnel = 'AND kanban_id IN ('.$parametres["tunnel"].')';
}
}
$sql_region = "";
if (array_key_exists('region', $parametres) and $parametres['region']!="") {
$sql_region = " AND f.region_id = ".$parametres['region'];
//kanban__fiche
}
$conn = $this->em->getConnection();
$sql = 'SELECT count(*) as total
FROM
kanban__fiche as f
'.$join_client.'
WHERE
statut_fiche_id = 2
AND
dateSuppression is null
AND
f.date >= "'.$parametres["debut_filtre_date"].' 00:00:00"
AND
f.date <= "'.$parametres["fin_filtre_date"].' 23:59:59"
'.$where_equipiers.'
'.$where_tunnel.'
'.$where_activite.'
'.$where_type_produit.'
'.$sql_region.'
';
$stmt = $conn->executeQuery($sql);
$total = $stmt->fetchAllAssociative();
return $total[0]["total"];
}
public function totalInitieesAffaireCa($parametres = []) {
$user = $this->security->getUser();
$where_equipiers = "";
$where_activite = "";
$where_type_produit = "";
$join_client = "";
if (array_key_exists('typesProduits', $parametres) && count($parametres["typesProduits"]) > 0) {
//activite
$in_type_produit_id = "";
for($o=0;$o<count($parametres["typesProduits"]);$o++) {
$in_type_produit_id .= $parametres["typesProduits"][$o].",";
}
if($in_type_produit_id != "") {
$in_type_produit_id = trim($in_type_produit_id,",,");
$where_type_produit = " AND f.id IN (SELECT distinct fiche_id FROM kanban__fiche_type_produit WHERE type_produit_id IN (".$in_type_produit_id."))";
}
}
if (array_key_exists('activite', $parametres) && $parametres["activite"] > 0) {
//activite
$join_client = "LEFT JOIN client__client as c ON f.client_id = c.id";
$where_activite = " AND c.ape_id = ".$parametres["activite"];
}
if (array_key_exists('equipiers', $parametres) && count($parametres["equipiers"]) > 0) {
foreach($parametres["equipiers"] as $e) {
$where_equipiers .= $e.",";
}
}
if($where_equipiers != "") {
$where_equipiers = trim($where_equipiers,',');
$where_equipiers = " AND utilisateur_id IN (".$where_equipiers.")";
}
if(!is_object($user->getType()) or ($user->getType()->getId() != "1" and $user->getType()->getId() != "2")) {
$where_equipiers = " AND utilisateur_id IN (".$user->getId().")";
}
$where_tunnel = "";
if (array_key_exists('tunnel', $parametres) && count($parametres["tunnel"]) > 0) {
$in_kanban = "";
$repo_kanban = $this->em->getRepository(Kanban::class);
$repo_colonnes = $this->em->getRepository(Colonne::class);
$colonnes_kanban = $repo_colonnes->findBy(["kanban" =>$parametres["tunnel"]]);
$kanban_obj = $repo_kanban->find($parametres["tunnel"]);
$where = "";
$in_colonne = "";
if(is_object($kanban_obj->getColonne()))
{
$colids = [];
$colids = $repo_colonnes->findBy(["kanban" =>$kanban_obj]);
foreach($colids as $coll) {
$in_colonne .= $coll->getId().",";
}
$in_colonne = trim($in_colonne,",,");
$where_tunnel = ' AND colonne_id IN ('.$in_colonne.')';
}
else {
$where_tunnel = 'AND kanban_id IN ('.$parametres["tunnel"].')';
}
}
$sql_region = "";
if (array_key_exists('region', $parametres) and $parametres['region']!="") {
$sql_region = " AND f.region_id = ".$parametres['region'];
//kanban__fiche
}
$conn = $this->em->getConnection();
$sql = 'SELECT SUM(budget) as total
FROM
kanban__fiche as f
'.$join_client.'
WHERE
statut_fiche_id IS NULL
AND
dateSuppression is null
AND
f.date >= "'.$parametres["debut_filtre_date"].' 00:00:00"
AND
f.date <= "'.$parametres["fin_filtre_date"].' 23:59:59"
'.$where_equipiers.'
'.$where_tunnel.'
'.$where_activite.'
'.$where_type_produit.'
'.$sql_region.'
';
$stmt = $conn->executeQuery($sql);
$total = $stmt->fetchAllAssociative();
return $total[0]["total"];
}
public function totalInitieesAffaire($parametres = []) {
$user = $this->security->getUser();
$where_equipiers = "";
$where_activite = "";
$where_type_produit = "";
$join_client = "";
if (array_key_exists('typesProduits', $parametres) && count($parametres["typesProduits"]) > 0) {
//activite
$in_type_produit_id = "";
for($o=0;$o<count($parametres["typesProduits"]);$o++) {
$in_type_produit_id .= $parametres["typesProduits"][$o].",";
}
if($in_type_produit_id != "") {
$in_type_produit_id = trim($in_type_produit_id,",,");
$where_type_produit = " AND f.id IN (SELECT distinct fiche_id FROM kanban__fiche_type_produit WHERE type_produit_id IN (".$in_type_produit_id."))";
}
}
if (array_key_exists('activite', $parametres) && $parametres["activite"] > 0) {
//activite
$join_client = "LEFT JOIN client__client as c ON f.client_id = c.id";
$where_activite = " AND c.ape_id = ".$parametres["activite"];
}
if (array_key_exists('equipiers', $parametres) && count($parametres["equipiers"]) > 0) {
foreach($parametres["equipiers"] as $e) {
$where_equipiers .= $e.",";
}
}
if($where_equipiers != "") {
$where_equipiers = trim($where_equipiers,',');
$where_equipiers = " AND utilisateur_id IN (".$where_equipiers.")";
}
if(!is_object($user->getType()) or ($user->getType()->getId() != "1" and $user->getType()->getId() != "2")) {
$where_equipiers = " AND utilisateur_id IN (".$user->getId().")";
}
$where_tunnel = "";
if (array_key_exists('tunnel', $parametres) && count($parametres["tunnel"]) > 0) {
$in_kanban = "";
$repo_kanban = $this->em->getRepository(Kanban::class);
$repo_colonnes = $this->em->getRepository(Colonne::class);
$colonnes_kanban = $repo_colonnes->findBy(["kanban" =>$parametres["tunnel"]]);
$kanban_obj = $repo_kanban->find($parametres["tunnel"]);
$where = "";
$in_colonne = "";
if(is_object($kanban_obj->getColonne()))
{
$colids = [];
$colids = $repo_colonnes->findBy(["kanban" =>$kanban_obj]);
foreach($colids as $coll) {
$in_colonne .= $coll->getId().",";
}
$in_colonne = trim($in_colonne,",,");
$where_tunnel = ' AND colonne_id IN ('.$in_colonne.')';
}
else {
$where_tunnel = 'AND kanban_id IN ('.$parametres["tunnel"].')';
}
}
$sql_region = "";
if (array_key_exists('region', $parametres) and $parametres['region']!="") {
$sql_region = " AND f.region_id = ".$parametres['region'];
//kanban__fiche
}
$conn = $this->em->getConnection();
$sql = 'SELECT count(*) as total
FROM
kanban__fiche as f
'.$join_client.'
WHERE
statut_fiche_id IS NULL
AND
dateSuppression is null
AND
f.date >= "'.$parametres["debut_filtre_date"].' 00:00:00"
AND
f.date <= "'.$parametres["fin_filtre_date"].' 23:59:59"
'.$where_equipiers.'
'.$where_tunnel.'
'.$where_activite.'
'.$where_type_produit.'
'.$sql_region.'
';
$stmt = $conn->executeQuery($sql);
$total = $stmt->fetchAllAssociative();
return $total[0]["total"];
}
public function totalAffaireCommandeesTypeProduit($typeProduit="",$parametres = []) {
$user = $this->security->getUser();
$where_equipiers = "";
if (array_key_exists('equipiers', $parametres) && count($parametres["equipiers"]) > 0) {
foreach($parametres["equipiers"] as $e) {
$where_equipiers .= $e.",";
}
}
if($where_equipiers != "") {
$where_equipiers = trim($where_equipiers,',');
$where_equipiers = " AND utilisateur_id IN (".$where_equipiers.")";
}
if(!is_object($user->getType()) or ($user->getType()->getId() != "1" and $user->getType()->getId() != "2")) {
$where_equipiers = " AND utilisateur_id IN (".$user->getId().")";
}
$conn = $this->em->getConnection();
$sql = 'SELECT count(*) as total
FROM
kanban__fiche
WHERE
statut_fiche_id = 1
'.$where_equipiers.'
';
if($typeProduit != "") {
$sql .= "AND id IN (SELECT fiche_id FROM kanban__fiche_type_produit WHERE type_produit_id =".$typeProduit.")";
}
$stmt = $conn->executeQuery($sql);
$total = $stmt->fetchAllAssociative();
//echo "<div>TOTAL ". $total[0]["total"]."</div>";
return $total[0]["total"];
}
public function totalAffairePerduesTypeProduit($typeProduit="",$parametres = []) {
$user = $this->security->getUser();
$where_equipiers = "";
if (array_key_exists('equipiers', $parametres) && count($parametres["equipiers"]) > 0) {
foreach($parametres["equipiers"] as $e) {
$where_equipiers .= $e.",";
}
}
if($where_equipiers != "") {
$where_equipiers = trim($where_equipiers,',');
$where_equipiers = " AND utilisateur_id IN (".$where_equipiers.")";
}
if(!is_object($user->getType()) or ($user->getType()->getId() != "1" and $user->getType()->getId() != "2")) {
$where_equipiers = " AND utilisateur_id IN (".$user->getId().")";
}
$conn = $this->em->getConnection();
$sql = 'SELECT count(*) as total
FROM
kanban__fiche
WHERE
statut_fiche_id = 2
'.$where_equipiers.'
';
if($typeProduit != "") {
$sql .= "AND id IN (SELECT fiche_id FROM kanban__fiche_type_produit WHERE type_produit_id =".$typeProduit.")";
}
$stmt = $conn->executeQuery($sql);
$total = $stmt->fetchAllAssociative();
//echo "<div>TOTAL ". $total[0]["total"]."</div>";
return $total[0]["total"];
}
public function totalAffaireCommandees($colonne="",$parametres = []) {
$user = $this->security->getUser();
$where_equipiers = "";
if (array_key_exists('equipiers', $parametres) && count($parametres["equipiers"]) > 0) {
foreach($parametres["equipiers"] as $e) {
$where_equipiers .= $e.",";
}
}
if($where_equipiers != "") {
$where_equipiers = trim($where_equipiers,',');
$where_equipiers = " AND utilisateur_id IN (".$where_equipiers.")";
}
if(!is_object($user->getType()) or ($user->getType()->getId() != "1" and $user->getType()->getId() != "2")) {
$where_equipiers = " AND utilisateur_id IN (".$user->getId().")";
}
$conn = $this->em->getConnection();
$sql = 'SELECT count(*) as total
FROM
kanban__fiche
WHERE
statut_fiche_id = 1
'.$where_equipiers.'
';
if($colonne != "") {
$in_colonne = $colonne;
$repo_colonnes = $this->em->getRepository(Colonne::class);
$repo_kanban = $this->em->getRepository(Kanban::class);
$colonne_obj = $repo_colonnes->find($colonne);
$kanban_colonne_obj = $repo_kanban->findOneBy(["colonne" =>$colonne_obj]);
//if($colonne == 4) {
if(is_object($colonne_obj) && is_object($kanban_colonne_obj)) {
$colonnes = $repo_colonnes->findBy(["kanban" =>$kanban_colonne_obj]);
$in_colonne = "";
foreach ($colonnes as $c) {
$in_colonne .= $c->getId().",";
}
$in_colonne = trim($in_colonne,",,");
}
//echo "<div>IN ".$in_colonne."</div>";
$sql .= " AND colonne_id IN (".$in_colonne.")";
}
$stmt = $conn->executeQuery($sql);
$total = $stmt->fetchAllAssociative();
//echo $sql;
//echo "<div>TOTAL ". $total[0]["total"]."</div>";
return $total[0]["total"];
}
public function totalAffairePerdues($colonne="",$parametres = []) {
$user = $this->security->getUser();
$where_equipiers = "";
if (array_key_exists('equipiers', $parametres) && count($parametres["equipiers"]) > 0) {
foreach($parametres["equipiers"] as $e) {
$where_equipiers .= $e.",";
}
}
if($where_equipiers != "") {
$where_equipiers = trim($where_equipiers,',');
$where_equipiers = " AND utilisateur_id IN (".$where_equipiers.")";
}
if(!is_object($user->getType()) or ($user->getType()->getId() != "1" and $user->getType()->getId() != "2")) {
$where_equipiers = " AND utilisateur_id IN (".$user->getId().")";
}
$conn = $this->em->getConnection();
$sql = 'SELECT count(*) as total
FROM
kanban__fiche
WHERE
statut_fiche_id = 2
'.$where_equipiers.'
';
if($colonne != "") {
$in_colonne = $colonne;
$repo_colonnes = $this->em->getRepository(Colonne::class);
$repo_kanban = $this->em->getRepository(Kanban::class);
$colonne_obj = $repo_colonnes->find($colonne);
$kanban_colonne_obj = $repo_kanban->findOneBy(["colonne" =>$colonne_obj]);
//if($colonne == 4) {
if(is_object($colonne_obj) && is_object($kanban_colonne_obj)) {
$colonnes = $repo_colonnes->findBy(["kanban" =>$kanban_colonne_obj]);
$in_colonne = "";
foreach ($colonnes as $c) {
$in_colonne .= $c->getId().",";
}
$in_colonne = trim($in_colonne,",,");
}
//echo "<div>IN ".$in_colonne."</div>";
$sql .= " AND colonne_id IN (".$in_colonne.")";
}
$stmt = $conn->executeQuery($sql);
$total = $stmt->fetchAllAssociative();
//echo "<div>TOTAL ". $total[0]["total"]."</div>";
return $total[0]["total"];
}
public function statColonneStatut(Colonne $colonne,$statut_affaire,$parametres = []) {
//print_r($parametres);
//echo "<div>".$utilisateur->getReference()."</div>";
$conn = $this->em->getConnection();
$in_colonne = $colonne->getId();
//if(is_object($colonne->getKanban()) && is_object($colonne->getKanban()->getColonne())) {
$repo_colonnes = $this->em->getRepository(Colonne::class);
$repo_kanban = $this->em->getRepository(Kanban::class);
$kanban_colonne_obj = $repo_kanban->findOneBy(["colonne" =>$colonne]);
if(is_object($kanban_colonne_obj)) {
$colonnes = $repo_colonnes->findBy(["kanban" =>$kanban_colonne_obj]);
$in_colonne = "";
foreach ($colonnes as $c) {
$in_colonne .= $c->getId().",";
}
$in_colonne = trim($in_colonne,",,");
}
/*
if($colonne->getId() == 4) {
$repo_colonnes = $this->em->getRepository(Colonne::class);
$colonnes = $repo_colonnes->findBy(array("kanban"=>2));
$in_colonne = "";
foreach ($colonnes as $c) {
$in_colonne .= $c->getId().",";
}
$in_colonne = trim($in_colonne,",,");
}
*/
if($statut_affaire == "O") {
$sql = 'SELECT SUM(budget) as total
FROM
kanban__fiche
WHERE
statut_fiche_id IS NULL
AND
colonne_id IN ('.$in_colonne.')
';
}
else {
$sql = 'SELECT SUM(budget) as total
FROM
kanban__fiche
WHERE
statut_fiche_id = '.$statut_affaire.'
AND
colonne_id IN ('.$in_colonne.')
';
}
if (array_key_exists('date_debut', $parametres) and $parametres['date_debut']!="") {
$dateDebut = \DateTime::createFromFormat('d/m/Y',$parametres['date_debut']);
$dateDebut->setTime(00,00,00);
$sql .= " AND date >= '".$dateDebut->format('Y-m-d H:i:s')."'";
}
if (array_key_exists('date_fin', $parametres) and $parametres['date_fin']!="") {
$dateFin = \DateTime::createFromFormat('d/m/Y',$parametres['date_fin']);
$dateFin->setTime(23,59,59);
$sql .= " AND date <= '".$dateFin->format('Y-m-d H:i:s')."'";
}
if (array_key_exists('region', $parametres) and $parametres['region']!="") {
$sql .= " AND region_id = ".$parametres["region"];
}
//echo "<div>".$sql."</div>";
$stmt = $conn->executeQuery($sql);
$total = $stmt->fetchAllAssociative();
//echo "<div>TOTAL ". $total[0]["total"]."</div>";
return $total[0]["total"];
}
public function entreeSortie($entree_sortie = NULL, $parametres = [], $colonne_id = NULL) {
$conn = $this->em->getConnection();
$dateDebut = NULL;
$dateFin = NULL;
$sql = "";
$sql_region = "";
$join = "";
//echo "<div>".$utilisateur->getId()."</div>";
//print_r($parametres);
/*
if (array_key_exists('region', $parametres) and $parametres['region']!="") {
$sql_region = " AND region_id = ".$parametres['region'];
//kanban__fiche
}
*/
$where_activite = "";
$where_type_produit = "";
$join_client = "";
if (array_key_exists('typesProduits', $parametres) && count($parametres["typesProduits"]) > 0) {
//activite
$in_type_produit_id = "";
for($o=0;$o<count($parametres["typesProduits"]);$o++) {
$in_type_produit_id .= $parametres["typesProduits"][$o].",";
}
if($in_type_produit_id != "") {
$in_type_produit_id = trim($in_type_produit_id,",,");
$where_type_produit = " AND n.fiche_id IN (SELECT distinct fiche_id FROM kanban__fiche_type_produit WHERE type_produit_id IN (".$in_type_produit_id."))";
}
}
if (array_key_exists('activite', $parametres) && $parametres["activite"] > 0) {
//activite
$join_client = "LEFT JOIN client__client as c ON n.client_id = c.id";
$where_activite = " AND c.ape_id = ".$parametres["activite"];
}
if (array_key_exists('region', $parametres) and $parametres['region']!="") {
$sql_region = " AND f.region_id = ".$parametres['region'];
//kanban__fiche
}
$join = "INNER JOIN kanban__fiche as f ON n.fiche_id = f.id";
$repo_kanban = $this->em->getRepository(Kanban::class);
$repo_colonnes = $this->em->getRepository(Colonne::class);
$colonnes_kanban = $repo_colonnes->findBy(["kanban" =>$parametres["tunnel"]]);
$kanban_obj = $repo_kanban->find($parametres["tunnel"]);
$where = "";
$in_colonne = "";
if($colonne_id > 0) {
$in_colonne = $colonne_id;
$where = 'colonne_id IN ('.$colonne_id.')';
$colonne_obj = $repo_colonnes->find($colonne_id);
$tunnel_enfant = $repo_kanban->findOneBy(["colonne" =>$colonne_obj]);
if(is_object($tunnel_enfant)) {
$in_colonne = "";
$colonnes_enfants = $repo_colonnes->findBy(["kanban" =>$tunnel_enfant]);
foreach($colonnes_enfants as $ce) {
$in_colonne .= $ce->getId().",";
}
$in_colonne = trim($in_colonne,",,");
$where = 'colonne_id IN ('.$in_colonne.')';
}
}
//echo $where;
if (array_key_exists('date_debut', $parametres) and $parametres['date_debut']!="") {
$dateDebut = \DateTime::createFromFormat('d/m/Y','01/'.$parametres['date_debut']);
$dateDebut->setTime(00,00,00);
//$sql .= " AND date >= '".$dateDebut->format('Y-m-d H:i:s')."'";
}
if (array_key_exists('date_fin', $parametres) and $parametres['date_fin']!="") {
$dateFin = \DateTime::createFromFormat('d/m/Y','30/'.$parametres['date_fin']);
$dateFin->setTime(23,59,59);
//$sql .= " AND date <= '".$dateFin->format('Y-m-d H:i:s')."'";
}
if($entree_sortie == "entree") {
$sql = "
SELECT count(*) as total
FROM `note__note` as n
".$join."
".$join_client."
WHERE
n.colonne_id IN (".$in_colonne.")
AND
n.dateDebut >= '".$dateDebut->format('Y-m-d H:i:s')."'
AND
n.dateDebut <= '".$dateFin->format('Y-m-d H:i:s')."'
AND
n.dateDebut IS NOT NULL
AND
(f.statut_fiche_id is null or f.statut_fiche_id IN (1,2))
".$where_activite."
".$where_type_produit."
".$sql_region
;
}
else if($entree_sortie == "sortie") {
$sql = "
SELECT count(*) as total
FROM `note__note` as n
".$join."
".$join_client."
WHERE
n.colonne_id IN (".$in_colonne.")
AND
n.dateFin >= '".$dateDebut->format('Y-m-d H:i:s')."'
AND
n.dateFin <= '".$dateFin->format('Y-m-d H:i:s')."'
AND
n.dateFin IS NOT NULL
AND
(f.statut_fiche_id is null or f.statut_fiche_id IN (1,2))
".$where_activite."
".$where_type_produit."
".$sql_region;
}
$stmt = $conn->executeQuery($sql);
$total = $stmt->fetchAllAssociative();
return $total[0]["total"];
}
public function statEtatAffaire($statut_fiche_id = NULL, $parametres = [], $colonne_id = NULL) {
$conn = $this->em->getConnection();
$dateDebut = NULL;
$dateFin = NULL;
$sql = "";
$sql_region = "";
$where_activite = "";
$where_type_produit = "";
$join_client = "";
if (array_key_exists('typesProduits', $parametres) && count($parametres["typesProduits"]) > 0) {
//activite
$in_type_produit_id = "";
for($o=0;$o<count($parametres["typesProduits"]);$o++) {
$in_type_produit_id .= $parametres["typesProduits"][$o].",";
}
if($in_type_produit_id != "") {
$in_type_produit_id = trim($in_type_produit_id,",,");
$where_type_produit = " AND f.id IN (SELECT distinct fiche_id FROM kanban__fiche_type_produit WHERE type_produit_id IN (".$in_type_produit_id."))";
}
}
if (array_key_exists('activite', $parametres) && $parametres["activite"] > 0) {
//activite
$join_client = "LEFT JOIN client__client as c ON f.client_id = c.id";
$where_activite = " AND c.ape_id = ".$parametres["activite"];
}
//echo "<div>".$utilisateur->getId()."</div>";
//print_r($parametres);
if (array_key_exists('region', $parametres) and $parametres['region']!="") {
$sql_region = " AND region_id = ".$parametres['region'];
//kanban__fiche
}
$in_kanban = "";
$repo_kanban = $this->em->getRepository(Kanban::class);
$repo_colonnes = $this->em->getRepository(Colonne::class);
$colonnes_kanban = $repo_colonnes->findBy(["kanban" =>$parametres["tunnel"]]);
$kanban_obj = $repo_kanban->find($parametres["tunnel"]);
if(is_array($colonnes_kanban) && count($colonnes_kanban)>0) {
foreach($colonnes_kanban as $ck) {
//echo "<div>".$ck->getLibelle()."</div>";
$kanban_enfant = $repo_kanban->findOneBy(["colonne" =>$ck]);
if(is_object($kanban_enfant)) {
$in_kanban .= $kanban_enfant->getId().",";
}
}
}
$in_kanban .= $parametres["tunnel"];
$in_kanban = trim($in_kanban,",,");
$where = "";
$in_colonne = "";
if(is_object($kanban_obj->getColonne()))
{
$colids = [];
$colids = $repo_colonnes->findBy(["kanban" =>$kanban_obj]);
foreach($colids as $coll) {
$in_colonne .= $coll->getId().",";
}
$in_colonne = trim($in_colonne,",,");
$where = 'colonne_id IN ('.$in_colonne.')';
}
else {
$where = 'kanban_id IN ('.$parametres["tunnel"].')';
}
if($colonne_id > 0) {
$where = 'colonne_id IN ('.$colonne_id.')';
$colonne_obj = $repo_colonnes->find($colonne_id);
$tunnel_enfant = $repo_kanban->findOneBy(["colonne" =>$colonne_obj]);
if(is_object($tunnel_enfant)) {
$in_colonne = "";
$colonnes_enfants = $repo_colonnes->findBy(["kanban" =>$tunnel_enfant]);
foreach($colonnes_enfants as $ce) {
$in_colonne .= $ce->getId().",";
}
$in_colonne = trim($in_colonne,",,");
$where = 'colonne_id IN ('.$in_colonne.')';
}
}
//echo $where;
if($statut_fiche_id == "0") {
$sql = 'SELECT count(*) as total
FROM kanban__fiche as f
'.$join_client.'
WHERE
'.$where.'
AND
(f.statut_fiche_id is null or f.statut_fiche_id IN (1,2))
'.$where_activite.'
'.$where_type_produit.'
'.$sql_region;
}
else if(is_null($statut_fiche_id) or $statut_fiche_id == NULL or $statut_fiche_id == "NULL") {
$sql = 'SELECT count(*) as total
FROM kanban__fiche as f
'.$join_client.'
WHERE
'.$where.'
AND
statut_fiche_id IS NULL
'.$where_activite.'
'.$where_type_produit.'
'.$sql_region;
}
else {
$sql = 'SELECT count(*) as total
FROM kanban__fiche as f
'.$join_client.'
WHERE
'.$where.'
AND
statut_fiche_id = '.$statut_fiche_id.'
'.$where_activite.'
'.$where_type_produit.'
'.$sql_region;
}
if (array_key_exists('date_debut', $parametres) and $parametres['date_debut']!="") {
$dateDebut = \DateTime::createFromFormat('d/m/Y','01/'.$parametres['date_debut']);
$dateDebut->setTime(00,00,00);
$sql .= " AND f.date >= '".$dateDebut->format('Y-m-d H:i:s')."'";
}
if (array_key_exists('date_fin', $parametres) and $parametres['date_fin']!="") {
$dateFin = \DateTime::createFromFormat('d/m/Y','30/'.$parametres['date_fin']);
$dateFin->setTime(23,59,59);
$sql .= " AND f.date <= '".$dateFin->format('Y-m-d H:i:s')."'";
}
//echo $sql;
//exit;
$stmt = $conn->executeQuery($sql);
$total = $stmt->fetchAllAssociative();
//echo "<div>TOTAL ". $total[0]["total"]."</div>";
return $total[0]["total"];
}
public function tempsMiniMaxiAffaire(Colonne $colonne,$parametres = []) {
$conn = $this->em->getConnection();
$sql = "";
$sql_region = "";
$join = "";
//print_r($parametres);
$mini_maxi = ["mini" =>0, "maxi" =>0, "tauxRespect" =>0, "totalFiches" =>"0"];
$delai_colonne = $colonne->getDelai();
$where_activite = "";
$where_type_produit = "";
$join_client = "";
if (array_key_exists('typesProduits', $parametres) && count($parametres["typesProduits"]) > 0) {
//activite
$in_type_produit_id = "";
for($o=0;$o<count($parametres["typesProduits"]);$o++) {
$in_type_produit_id .= $parametres["typesProduits"][$o].",";
}
if($in_type_produit_id != "") {
$in_type_produit_id = trim($in_type_produit_id,",,");
$where_type_produit = " AND n.fiche_id IN (SELECT distinct fiche_id FROM kanban__fiche_type_produit WHERE type_produit_id IN (".$in_type_produit_id."))";
}
}
if (array_key_exists('activite', $parametres) && $parametres["activite"] > 0) {
//activite
$join_client = "LEFT JOIN client__client as c ON n.client_id = c.id";
$where_activite = " AND c.ape_id = ".$parametres["activite"];
}
$dateDebutBoucle = \DateTime::createFromFormat('d/m/Y','01/'.$parametres['date_debut']);
$dateDebutBoucle->setTime(00,00,01);
$dateFinBoucle = \DateTime::createFromFormat('m/Y',$parametres['date_fin']);
$dateFinBoucle->modify('last day of this month');
$dateFinBoucle->setTime(23,59,59);
$in_colonne = $colonne->getId();
//if(is_object($colonne->getKanban()) && is_object($colonne->getKanban()->getColonne())) {
$repo_colonnes = $this->em->getRepository(Colonne::class);
$repo_kanban = $this->em->getRepository(Kanban::class);
$kanban_colonne_obj = $repo_kanban->findOneBy(["colonne" =>$colonne]);
if(is_object($kanban_colonne_obj)) {
$colonnes = $repo_colonnes->findBy(["kanban" =>$kanban_colonne_obj]);
$in_colonne = "";
foreach ($colonnes as $c) {
$in_colonne .= $c->getId().",";
}
$in_colonne = trim($in_colonne,",,");
}
if (array_key_exists('region', $parametres) and $parametres['region']!="") {
$sql_region = " AND f.region_id = ".$parametres['region'];
//kanban__fiche
}
$join = "INNER JOIN kanban__fiche as f ON n.fiche_id = f.id";
$sql = "
SELECT *
FROM `note__note` as n
".$join."
".$join_client."
WHERE
n.colonne_id IN (".$in_colonne.")
AND
n.dateDebut >= '".$dateDebutBoucle->format('Y-m-d H:i:s')."'
AND
n.dateFin <= '".$dateFinBoucle->format('Y-m-d H:i:s')."'
AND
n.dateDebut IS NOT NULL
AND
dateFin IS NOT NULL
AND
(f.statut_fiche_id is null or f.statut_fiche_id IN (1,2))
".$sql_region."
".$where_type_produit."
".$where_activite;
//echo "<div>DD : ".$dateDebutBoucle->format('Y-m-d H:i:s')."</div>";
//echo "<div>DF : ".$dateFinBoucle->format('Y-m-d H:i:s')."</div>";
//echo $sql."<br/>";
$stmt = $conn->executeQuery($sql);
$fiches = $stmt->fetchAllAssociative();
$tab_jours = [];
$total_fiches = count($fiches);
$totalFicheDansLeDelais = 0;
if(is_array($fiches) && count($fiches)>0) {
foreach($fiches as $f) {
//echo "<div>F ".$f['fiche_id']." (".$f['dateDebut'].") (".$f['dateFin'].")</div>";
$date_deb = strtotime($f['dateDebut']);
$date_fin = strtotime($f['dateFin']);
$datediff = $date_fin - $date_deb;
$nb_jours = round($datediff / (60 * 60 * 24));
//echo "<div>".$nb_jours."</div>";
$tab_jours[]=$nb_jours;
if($nb_jours <= $delai_colonne) {
$totalFicheDansLeDelais++;
}
}
$mini_maxi["maxi"] = max($tab_jours);
$mini_maxi["mini"] = min($tab_jours);
//echo "<div>Colonne :".$colonne->getLibelle()."</div>";
//echo "<div>totalFicheDansLeDelais:".$totalFicheDansLeDelais."</div>";
//echo "<div>total_fiches:".$total_fiches."</div>";
$mini_maxi["tauxRespect"] = round($totalFicheDansLeDelais*100/$total_fiches);
$mini_maxi["totalFiches"] = $total_fiches;
}
//exit;
//print_r($tab_jours);
//print_r($mini_maxi);
return $mini_maxi;
}
public function tempsMoyenAvanceRetardAffaire(Colonne $colonne, $parametres = [], $mois_boucle) {
$difference = 0;
$duree = $colonne->getDelai();
if(is_null($duree) or $duree=="0" or $duree == "") {
return 0;
}
$moyenne = $this->tempsMoyenAffaire($colonne,$parametres,$mois_boucle);
if($moyenne == "") return "";
if($moyenne > $duree) {
$difference = $moyenne-$duree;
round($difference);
$difference = "+".$difference;
}
elseif($moyenne < $duree) {
$difference = $duree-$moyenne;
round($difference);
$difference = "-".$difference;
}
elseif($moyenne == $duree) {
$difference = 0;
}
//echo "<div>MOYENNE (".$moyenne.")</div>";
//echo "<div>DELAI (".$duree.")</div>";
//echo "<div>DIFF (".$difference.")</div>";
//echo "<hr/>";
return $difference;
}
public function tempsMoyenAffaireWidget(Colonne $colonne,$parametres = []) {
$user = $this->security->getUser();
$conn = $this->em->getConnection();
$sql = "";
$join = "";
$sql_region = "";
//print_r($parametres);
//exit;
$moyenne = 0;
$region = "";
$where_equipiers = "";
if (array_key_exists('equipiers', $parametres) && count($parametres["equipiers"]) > 0) {
foreach($parametres["equipiers"] as $e) {
$where_equipiers .= $e.",";
}
}
if($where_equipiers != "") {
$where_equipiers = trim($where_equipiers,',');
$where_equipiers = " AND utilisateur_id IN (".$where_equipiers.")";
}
if(!is_object($user->getType()) or ($user->getType()->getId() != "1" and $user->getType()->getId() != "2")) {
$where_equipiers = " AND utilisateur_id IN (".$user->getId().")";
}
$in_colonne = $colonne->getId();
$repo_colonnes = $this->em->getRepository(Colonne::class);
$repo_kanban = $this->em->getRepository(Kanban::class);
$kanban_colonne_obj = $repo_kanban->findOneBy(["colonne" =>$colonne]);
if(is_object($kanban_colonne_obj)) {
$colonnes = $repo_colonnes->findBy(["kanban" =>$kanban_colonne_obj]);
$in_colonne = "";
foreach ($colonnes as $c) {
$in_colonne .= $c->getId().",";
}
$in_colonne = trim($in_colonne,",,");
}
if (array_key_exists('region', $parametres) and $parametres['region']!="") {
$sql_region = " AND f.region_id = ".$parametres['region'];
//kanban__fiche
$join = "INNER JOIN kanban__fiche as f ON n.fiche_id = f.id";
}
$dateDebutBoucle = new \DateTime($parametres["debut_filtre_date"]);
$dateDebutBoucle->setTime(00,00,01);
$dateFinBoucle = new \DateTime($parametres["fin_filtre_date"]);
//$dateFinBoucle->modify('last day of this month');
$dateFinBoucle->setTime(23,59,59);
$sql = "
SELECT dateDebut,dateFin
FROM `note__note` as n
".$join."
WHERE
n.colonne_id IN (".$in_colonne.")
AND
n.dateDebut >= '".$dateDebutBoucle->format('Y-m-d H:i:s')."'
AND
n.dateDebut <= '".$dateFinBoucle->format('Y-m-d H:i:s')."'
AND
n.dateDebut IS NOT NULL
AND
n.dateFin IS NOT NULL
".$sql_region."
".$where_equipiers
;
//echo "<div>DD : ".$dateDebutBoucle->format('Y-m-d H:i:s')."</div>";
//echo "<div>DF : ".$dateFinBoucle->format('Y-m-d H:i:s')."</div>";
//echo $sql;
$stmt = $conn->executeQuery($sql);
$fiches = $stmt->fetchAllAssociative();
$tab_jours = [];
if(is_array($fiches) && count($fiches)>0) {
foreach($fiches as $f) {
//echo "<div>F ".$f['fiche_id']." (".$f['dateDebut'].") (".$f['dateFin'].")</div>";
$date_deb = strtotime($f['dateDebut']);
$date_fin = strtotime($f['dateFin']);
$datediff = $date_fin - $date_deb;
$nb_jours = round($datediff / (60 * 60 * 24));
//echo "<div>".$nb_jours."</div>";
$tab_jours[]=$nb_jours;
}
$moyenne = round(array_sum($tab_jours)/count($fiches));
}
//print_r($tab_jours);
//echo "<div>AVG :".$moyenne."</div>";
//exit;
return $moyenne;
}
public function tempsMoyenAffaire(Colonne $colonne, $parametres = [], $mois_boucle) {
$conn = $this->em->getConnection();
$sql = "";
$join = "";
$sql_region = "";
//print_r($parametres);
//exit;
$moyenne = 0;
$region = "";
$where_activite = "";
$where_type_produit = "";
$join_client = "";
if (array_key_exists('typesProduits', $parametres) && count($parametres["typesProduits"]) > 0) {
//activite
$in_type_produit_id = "";
for($o=0;$o<count($parametres["typesProduits"]);$o++) {
$in_type_produit_id .= $parametres["typesProduits"][$o].",";
}
if($in_type_produit_id != "") {
$in_type_produit_id = trim($in_type_produit_id,",,");
$where_type_produit = " AND n.fiche_id IN (SELECT distinct fiche_id FROM kanban__fiche_type_produit WHERE type_produit_id IN (".$in_type_produit_id."))";
}
}
if (array_key_exists('activite', $parametres) && $parametres["activite"] > 0) {
//activite
$join_client = "LEFT JOIN client__client as c ON n.client_id = c.id";
$where_activite = " AND c.ape_id = ".$parametres["activite"];
}
$in_colonne = $colonne->getId();
$repo_colonnes = $this->em->getRepository(Colonne::class);
$repo_kanban = $this->em->getRepository(Kanban::class);
$kanban_colonne_obj = $repo_kanban->findOneBy(["colonne" =>$colonne]);
if(is_object($kanban_colonne_obj)) {
$colonnes = $repo_colonnes->findBy(["kanban" =>$kanban_colonne_obj]);
$in_colonne = "";
foreach ($colonnes as $c) {
$in_colonne .= $c->getId().",";
}
$in_colonne = trim($in_colonne,",,");
}
if (array_key_exists('region', $parametres) and $parametres['region']!="") {
$sql_region = " AND f.region_id = ".$parametres['region'];
//kanban__fiche
}
$join = "INNER JOIN kanban__fiche as f ON n.fiche_id = f.id";
//print_r($mois_boucle);
$dateDebutBoucle = \DateTime::createFromFormat('d/m/Y','01/'.$mois_boucle);
$dateDebutBoucle->setTime(00,00,01);
$dateFinBoucle = \DateTime::createFromFormat('m/Y',$mois_boucle);
$dateFinBoucle->modify('last day of this month');
$dateFinBoucle->setTime(23,59,59);
$sql = "
SELECT dateDebut,dateFin
FROM `note__note` as n
".$join."
".$join_client."
WHERE
n.colonne_id IN (".$in_colonne.")
AND
n.dateDebut >= '".$dateDebutBoucle->format('Y-m-d H:i:s')."'
AND
n.dateDebut <= '".$dateFinBoucle->format('Y-m-d H:i:s')."'
AND
n.dateDebut IS NOT NULL
AND
n.dateFin IS NOT NULL
AND
(f.statut_fiche_id is null or f.statut_fiche_id IN (1,2))
".$where_activite."
".$where_type_produit."
".$sql_region
;
//echo "<div>DD : ".$dateDebutBoucle->format('Y-m-d H:i:s')."</div>";
//echo "<div>DF : ".$dateFinBoucle->format('Y-m-d H:i:s')."</div>";
//echo $sql."<br/>";
$stmt = $conn->executeQuery($sql);
$fiches = $stmt->fetchAllAssociative();
$tab_jours = [];
if(is_array($fiches) && count($fiches)>0) {
foreach($fiches as $f) {
//echo "<div>F ".$f['fiche_id']." (".$f['dateDebut'].") (".$f['dateFin'].")</div>";
$date_deb = strtotime($f['dateDebut']);
$date_fin = strtotime($f['dateFin']);
$datediff = $date_fin - $date_deb;
$nb_jours = round($datediff / (60 * 60 * 24));
//echo "<div>".$nb_jours."</div>";
$tab_jours[]=$nb_jours;
}
$moyenne = round(array_sum($tab_jours)/count($fiches));
}
else {
return "";
}
//print_r($tab_jours);
//echo "<div>AVG :".$moyenne."</div>";
//exit;
//echo "<div>MOYENNE : ".$moyenne."</div>";
return $moyenne;
}
public function statPerduRaison(Utilisateur $utilisateur,Raison $raison,$parametres = []) {
$conn = $this->em->getConnection();
$dateDebut = NULL;
$dateFin = NULL;
$sql = "";
$sql_region = "";
$where_activite = "";
$where_type_produit = "";
$join_client = "";
if (array_key_exists('typesProduits', $parametres) && count($parametres["typesProduits"]) > 0) {
//activite
$in_type_produit_id = "";
for($o=0;$o<count($parametres["typesProduits"]);$o++) {
$in_type_produit_id .= $parametres["typesProduits"][$o].",";
}
if($in_type_produit_id != "") {
$in_type_produit_id = trim($in_type_produit_id,",,");
$where_type_produit = " AND f.id IN (SELECT distinct fiche_id FROM kanban__fiche_type_produit WHERE type_produit_id IN (".$in_type_produit_id."))";
}
}
if (array_key_exists('activite', $parametres) && $parametres["activite"] > 0) {
//activite
$join_client = "LEFT JOIN client__client as c ON f.client_id = c.id";
$where_activite = " AND c.ape_id = ".$parametres["activite"];
}
$in_kanban = "";
$repo_kanban = $this->em->getRepository(Kanban::class);
$repo_colonnes = $this->em->getRepository(Colonne::class);
$colonnes_kanban = $repo_colonnes->findBy(["kanban" =>$parametres["tunnel"]]);
$kanban_obj = $repo_kanban->find($parametres["tunnel"]);
if(is_array($colonnes_kanban) && count($colonnes_kanban)>0) {
foreach($colonnes_kanban as $ck) {
//echo "<div>".$ck->getLibelle()."</div>";
$kanban_enfant = $repo_kanban->findOneBy(["colonne" =>$ck]);
if(is_object($kanban_enfant)) {
$in_kanban .= $kanban_enfant->getId().",";
}
}
}
$in_kanban .= $parametres["tunnel"];
$in_kanban = trim($in_kanban,",,");
$where = "";
$in_colonne = "";
if(is_object($kanban_obj->getColonne()))
{
$colids = [];
$colids = $repo_colonnes->findBy(["kanban" =>$kanban_obj]);
foreach($colids as $coll) {
$in_colonne .= $coll->getId().",";
}
$in_colonne = trim($in_colonne,",,");
$where = 'colonne_id IN ('.$in_colonne.')';
}
else {
$where = 'kanban_id IN ('.$parametres["tunnel"].')';
}
//echo "<div>".$utilisateur->getId()."</div>";
//print_r($parametres);
if (array_key_exists('region', $parametres) and $parametres['region']!="") {
$sql_region = " AND region_id = ".$parametres['region'];
//kanban__fiche
}
$sql = 'SELECT count(*) as total
FROM kanban__fiche as f
'.$join_client.'
WHERE
'.$where.'
AND
f.utilisateur_id = '.$utilisateur->getId().'
AND
statut_fiche_id = 2
AND
raison_id = '.$raison->getId().'
'.$where_activite.'
'.$where_type_produit.'
'.$sql_region;
if (array_key_exists('date_debut', $parametres) and $parametres['date_debut']!="") {
$dateDebut = \DateTime::createFromFormat('d/m/Y',"01/".$parametres['date_debut']);
$dateDebut->setTime(00,00,00);
$sql .= " AND f.date >= '".$dateDebut->format('Y-m-d H:i:s')."'";
}
if (array_key_exists('date_fin', $parametres) and $parametres['date_fin']!="") {
$dateFin = \DateTime::createFromFormat('d/m/Y',"30/".$parametres['date_fin']);
$dateFin->setTime(23,59,59);
$sql .= " AND f.date <= '".$dateFin->format('Y-m-d H:i:s')."'";
}
//echo $sql;
//exit;
$stmt = $conn->executeQuery($sql);
$total = $stmt->fetchAllAssociative();
//echo "<div>TOTAL ". $total[0]["total"]."</div>";
return $total[0]["total"];
}
public function statColonneRegion(Colonne $colonne,Region $region,$parametres = []) {
//print_r($parametres);
//echo "<div>".$utilisateur->getReference()."</div>";
$conn = $this->em->getConnection();
$in_colonne = $colonne->getId();
//if(is_object($colonne->getKanban()) && is_object($colonne->getKanban()->getColonne())) {
$repo_colonnes = $this->em->getRepository(Colonne::class);
$repo_kanban = $this->em->getRepository(Kanban::class);
$kanban_colonne_obj = $repo_kanban->findOneBy(["colonne" =>$colonne]);
if(is_object($kanban_colonne_obj)) {
$colonnes = $repo_colonnes->findBy(["kanban" =>$kanban_colonne_obj]);
$in_colonne = "";
foreach ($colonnes as $c) {
$in_colonne .= $c->getId().",";
}
$in_colonne = trim($in_colonne,",,");
}
$sql = 'SELECT SUM(budget) as total
FROM
kanban__fiche
WHERE
region_id = '.$region->getId().'
AND
colonne_id IN ('.$in_colonne.')
';
if (array_key_exists('statut_affaire', $parametres)) {
if($parametres["statut_affaire"] == "all") {
$sql .= " AND (statut_fiche_id is null or statut_fiche_id IN (1,2))";
}
else if($parametres["statut_affaire"] == "0") {
$sql .= " AND statut_fiche_id IS NULL";
}
else $sql .= " AND statut_fiche_id = ".$parametres["statut_affaire"];
}
if (array_key_exists('date_debut', $parametres) and $parametres['date_debut']!="") {
$dateDebut = \DateTime::createFromFormat('d/m/Y',$parametres['date_debut']);
$dateDebut->setTime(00,00,00);
$sql .= " AND date >= '".$dateDebut->format('Y-m-d H:i:s')."'";
}
if (array_key_exists('date_fin', $parametres) and $parametres['date_fin']!="") {
$dateFin = \DateTime::createFromFormat('d/m/Y',$parametres['date_fin']);
$dateFin->setTime(23,59,59);
$sql .= " AND date <= '".$dateFin->format('Y-m-d H:i:s')."'";
}
if (array_key_exists('commercial', $parametres) and $parametres['commercial']!="") {
$sql .= "AND id IN (select fiche_id FROM kanban__equipe WHERE utilisateur_id = '".$parametres['commercial']."')";
}
//echo "<div>".$sql."</div>";
$stmt = $conn->executeQuery($sql);
$total = $stmt->fetchAllAssociative();
//echo "<div>TOTAL ". $total[0]["total"]."</div>";
return $total[0]["total"];
}
public function statColonneCommercial(Colonne $colonne,Utilisateur $utilisateur,$parametres = []) {
//print_r($parametres);
//echo "<div>".$utilisateur->getReference()."</div>";
$conn = $this->em->getConnection();
$in_colonne = $colonne->getId();
//if(is_object($colonne->getKanban()) && is_object($colonne->getKanban()->getColonne())) {
$repo_colonnes = $this->em->getRepository(Colonne::class);
$repo_kanban = $this->em->getRepository(Kanban::class);
$kanban_colonne_obj = $repo_kanban->findOneBy(["colonne" =>$colonne]);
if(is_object($kanban_colonne_obj)) {
$colonnes = $repo_colonnes->findBy(["kanban" =>$kanban_colonne_obj]);
$in_colonne = "";
foreach ($colonnes as $c) {
$in_colonne .= $c->getId().",";
}
$in_colonne = trim($in_colonne,",,");
}
$sql = 'SELECT SUM(budget) as total
FROM
kanban__fiche
WHERE
id IN (select fiche_id FROM kanban__equipe WHERE utilisateur_id = '.$utilisateur->getId().')
AND
colonne_id IN ('.$in_colonne.')
';
if (array_key_exists('statut_affaire', $parametres)) {
if($parametres["statut_affaire"] == "all") {
$sql .= " AND (statut_fiche_id is null or statut_fiche_id IN (1,2))";
}
else if($parametres["statut_affaire"] == "0") {
$sql .= " AND statut_fiche_id IS NULL";
}
else $sql .= " AND statut_fiche_id = ".$parametres["statut_affaire"];
}
if (array_key_exists('date_debut', $parametres) and $parametres['date_debut']!="") {
$dateDebut = \DateTime::createFromFormat('d/m/Y',$parametres['date_debut']);
$dateDebut->setTime(00,00,00);
$sql .= " AND date >= '".$dateDebut->format('Y-m-d H:i:s')."'";
}
if (array_key_exists('date_fin', $parametres) and $parametres['date_fin']!="") {
$dateFin = \DateTime::createFromFormat('d/m/Y',$parametres['date_fin']);
$dateFin->setTime(23,59,59);
$sql .= " AND date <= '".$dateFin->format('Y-m-d H:i:s')."'";
}
if (array_key_exists('region', $parametres) and $parametres['region']!="") {
$sql .= " AND region_id = ".$parametres["region"];
}
//echo "<div>".$sql."</div>";
$stmt = $conn->executeQuery($sql);
$total = $stmt->fetchAllAssociative();
//echo "<div>TOTAL ". $total[0]["total"]."</div>";
return $total[0]["total"];
}
public function estEnRetard(Fiche $fiche) {
$repo_notes = $this->em->getRepository(Note::class);
$notesEnRetard = $repo_notes->getNotesEnRetard($fiche);
if(is_array($notesEnRetard) && count($notesEnRetard) > 0) return true;
return false;
}
public function getTotalActivitesPrevues(Fiche $fiche) {
$repo_notes = $this->em->getRepository(Note::class);
$notes = $repo_notes->findBy(["fiche" =>$fiche, "modifiable" =>"1"], ["dateDebut" =>"DESC"]);
return $notes;
}
public function getColonnesKanban(Kanban $kanban) {
$repo_colonnes = $this->em->getRepository(Colonne::class);
$colonnes = $repo_colonnes->findBy(["kanban" =>$kanban], ["position" =>"ASC"]);
return $colonnes;
}
public function getFicheMoisKanban($periode,$parametres = []) {
$fiches = [];
$user = $this->security->getUser();
$repo_fiche = $this->em->getRepository(Fiche::class);
$fiches = $repo_fiche->getFichesDate($periode,$parametres,$user, $this->container->getParameter('chefProjet'));
return $fiches;
}
public function moisKanban($parametres = []) {
$tab = [];
$tab_libelle_mois = [];
$tab_libelle_mois["01"]="Janvier";
$tab_libelle_mois["02"]="Février";
$tab_libelle_mois["03"]="Mars";
$tab_libelle_mois["04"]="Avril";
$tab_libelle_mois["05"]="Mai";
$tab_libelle_mois["06"]="Juin";
$tab_libelle_mois["07"]="Juillet";
$tab_libelle_mois["08"]="Aout";
$tab_libelle_mois["09"]="Septembre";
$tab_libelle_mois["10"]="Octobre";
$tab_libelle_mois["11"]="Novembre";
$tab_libelle_mois["12"]="Décembre";
if (array_key_exists('date_debut', $parametres) and $parametres['date_debut']!="") {
$dateDebut = \DateTime::createFromFormat('d/m/Y',$parametres['date_debut']);
$dateDebut->modify('first day of this month');
$dateDebut->setTime(00,00,00);
if(array_key_exists('date_fin', $parametres) and $parametres['date_fin']!="") {
$dateFin = \DateTime::createFromFormat('d/m/Y',$parametres['date_fin']);
$dateFin->modify('last day of this month');
$dateFin->setTime(00,00,00);
}
else {
$dateFin = new \DateTime('last day of this year');
$dateFin->setTime(00,00,00);
}
$dateTampon = \DateTime::createFromFormat('d/m/Y',$parametres['date_debut']);
$dateTampon->modify('first day of this month');
$dateTampon->setTime(00,00,00);
while($dateTampon < $dateFin) {
//echo "<div>".$dateTampon->format("m")."</div>";
$annee = $dateTampon->format("Y");
$tab[]= ["libelle" => $tab_libelle_mois[$dateTampon->format("m")]." ".$annee, "date" => $annee."-".$dateTampon->format("m")."-01"];
$dateTampon->add(new \DateInterval('P1M'));
}
//print_r($tab);
}
else {
$date = new \Datetime();
$annee = $date->format("Y");
$tab[]= ["libelle" => "Janvier ".$annee, "date" => $annee."-01-01"];
$tab[]= ["libelle" => "Février ".$annee, "date" => $annee."-02-01"];
$tab[]= ["libelle" => "Mars ".$annee, "date" => $annee."-03-01"];
$tab[]= ["libelle" => "Avril ".$annee, "date" => $annee."-04-01"];
$tab[]= ["libelle" => "Mai ".$annee, "date" => $annee."-05-01"];
$tab[]= ["libelle" => "Juin ".$annee, "date" => $annee."-06-01"];
$tab[]= ["libelle" => "Juillet ".$annee, "date" => $annee."-07-01"];
$tab[]= ["libelle" => "Aout ".$annee, "date" => $annee."-08-01"];
$tab[]= ["libelle" => "Septembre ".$annee, "date" => $annee."-09-01"];
$tab[]= ["libelle" => "Octobre ".$annee, "date" => $annee."-10-01"];
$tab[]= ["libelle" => "Novembre ".$annee, "date" => $annee."-11-01"];
$tab[]= ["libelle" => "Décembre ".$annee, "date" => $annee."-12-01"];
}
return $tab;
}
public function getContactDefaut(Fiche $fiche) {
$repo_contact = $this->em->getRepository(Contact::class);
$contactDefaut = "";
if(is_object($fiche->getClient())) $contactDefaut = $repo_contact->getContactDefautClient($fiche->getClient());
return $contactDefaut;
}
public function getTempsPasse(Fiche $fiche) {
$tempsRestant = "0";
$pourcentagePasse = "0";
$now = time(); // or your date as well
$your_date = strtotime($fiche->getDate()->format("Y-m-d"));
$datediff = $now - $your_date;
$tempsPasse = round($datediff / (60 * 60 * 24));
if(1!=1 and is_object($fiche->getDatePrevisionelle())) {
$now = time();// or your date as well
$your_date = strtotime($fiche->getDatePrevisionelle()->format("Y-m-d"));
$datediff = $your_date-$now;
$tempsRestant = round($datediff / (60 * 60 * 24));
$now = time($fiche->getDate()->format("Y-m-d"));// or your date as well
$your_date = strtotime($fiche->getDatePrevisionelle()->format("Y-m-d"));
$datediff = $your_date-$now;
$tempsTotal = round($datediff / (60 * 60 * 24));
//$pourcentagePasse = round($tempsPasse*100/$tempsTotal);
}
return $tempsPasse;
}
public function getTempsRestant(Fiche $fiche) {
$tempsRestant = "0";
$pourcentagePasse = "0";
$now = time(); // or your date as well
$your_date = strtotime($fiche->getDate()->format("Y-m-d"));
$datediff = $now - $your_date;
$tempsPasse = round($datediff / (60 * 60 * 24));
if(1!=1 and is_object($fiche->getDatePrevisionelle())) {
$now = time();// or your date as well
$your_date = strtotime($fiche->getDatePrevisionelle()->format("Y-m-d"));
$datediff = $your_date-$now;
$tempsRestant = round($datediff / (60 * 60 * 24));
$now = time($fiche->getDate()->format("Y-m-d"));// or your date as well
$your_date = strtotime($fiche->getDatePrevisionelle()->format("Y-m-d"));
$datediff = $your_date-$now;
$tempsTotal = round($datediff / (60 * 60 * 24));
$pourcentagePasse = round($tempsPasse*100/$tempsTotal);
}
return $tempsRestant;
}
/*
private function getColonnesEnfants($colonne, $colonnes = array()){
$colonnes[] = $colonne->getId();
$kanban_colonne_obj = $this->em->getRepository(Kanban::class)->findOneBy(array("colonne"=>$colonne));
if(is_object($kanban_colonne_obj)) {
$colonnesTmp = $this->em->getRepository(Colonne::class)->findBy(array("kanban"=>$kanban_colonne_obj));
foreach($colonnesTmp as $colonneTmp){
$colonnes = array_merge($colonnes, $this->getColonnesEnfants($colonneTmp, $colonnes));
}
}
return $colonnes;
}
*/
public function getDureeFicheColonne(Fiche $fiche,Colonne $colonne) {
$duree = ["annee" =>0, "mois" =>0, "jours" =>"0", "heures" =>"0", "minutes" =>"0"];
$repo_kanban = $this->em->getRepository(Kanban::class);
$repo_colonne = $this->em->getRepository(Colonne::class);
$repo_notes = $this->em->getRepository(Note::class);
$in_colonne = $this->getColonnesEnfants($colonne);
$notes = $repo_notes->findBy(["fiche" =>$fiche, "colonne" =>$in_colonne, "modifiable" =>0], ['dateDebut' =>'DESC']);
$intervalTotal1 = new \Datetime();
$intervalTotal2 = new \Datetime();
$datetime2 = new \DateTime();
if(is_array($notes) && count($notes)>0) {
foreach($notes as $n) {
if(!is_null($n->getDateDebut())) {
$datetime1 = new \DateTime($n->getDateDebut()->format('Y-m-d H:i:s'));
if(!is_null($n->getDateFin())) $datetime2 = new \DateTime($n->getDateFin()->format('Y-m-d H:i:s'));
//else $datetime2 = new \DateTime();
$interval = $datetime1->diff($datetime2);
$intervalTotal1->add($interval);
$datetime2 = clone $datetime1;
}
}
}
$interval = $intervalTotal1->diff($intervalTotal2);
$duree["annee"] += $interval->format('%y');
$duree["mois"] += $interval->format('%m');
$duree["jours"] += $interval->format('%d');
$duree["heures"] += $interval->format('%H');
$duree["minutes"] += $interval->format('%I');
return $duree;
}
public function estFavoris(Fiche $fiche,Utilisateur $user) {
//$favoris = $this->em->getRepository('DTCKanbanBundle:Favoris')->findOneBy(array("utilisateur"=>$user,"fiche"=>$fiche));
$favoris = $this->em->getRepository(Favoris::class)->findOneBy(["fiche" =>$fiche]);
if(is_object($favoris)) return $favoris;
return false;
}
public function getTotalOffresColonne(Colonne $colonne,$parametres = []) {
$user = $this->security->getUser();
return $this->em->getRepository(Colonne::class)->getTotalOffres($colonne,$parametres,$user);
}
public function getTotalBudgetColonne(Colonne $colonne,$parametres = []) {
$user = $this->security->getUser();
$repo_colonnes = $this->em->getRepository(Colonne::class);
$repo_kanban = $this->em->getRepository(Kanban::class);
$kanban_colonne_obj = $repo_kanban->findOneBy(["colonne" =>$colonne]);
$in_colonne = $colonne->getId();
if(is_object($kanban_colonne_obj)) {
$colonnes = $repo_colonnes->findBy(["kanban" =>$kanban_colonne_obj]);
$in_colonne = "";
foreach ($colonnes as $c) {
$in_colonne .= $c->getId().",";
}
$in_colonne = trim($in_colonne,",,");
}
return $this->em->getRepository(Colonne::class)->getTotalBudgetColonne($in_colonne,$parametres,$user);
}
public function getTotalBudgetSansPourcentagesColonne(Colonne $colonne,$parametres = []) {
$user = $this->security->getUser();
$allColonnes = $this->getColonnesEnfants($colonne);
$total = 0;
foreach($allColonnes as $colonneTmp){
$total += $this->em->getRepository(Colonne::class)->getTotalBudgetSansPourcentagesColonne($colonneTmp,$parametres, $user);
}
return $total;
}
public function getTotalBudgetPondereColonne(Colonne $colonne,$parametres = []) {
$user = $this->security->getUser();
$allColonnes = $this->getColonnesEnfants($colonne);
$total = 0;
foreach($allColonnes as $colonneTmp){
$total += $this->em->getRepository(Colonne::class)->getTotalBudgetPondereColonne($colonneTmp, $parametres, $user);
}
return $total;
}
public function getTotalColonne(Kanban $kaban) {
return $this->em->getRepository(Kanban::class)->getTotalColonne($kaban);
}
public function getDureeDerniereNoteFiche(Fiche $fiche,Categorie $categorie) {
$repo_note = $this->em->getRepository(Note::class);
$note = $repo_note->findOneBy(["categorie" =>$categorie, "fiche" =>$fiche], ["dateDebut" =>"desc"]);
$date = new \Datetime();
if(is_object($note) && $note->getDateDebut() < $date) {
$now = time(); // or your date as well
$your_date = strtotime($note->getDateDebut()->format("Y-m-d"));
$datediff = $now-$your_date;
$tempsPasse = round($datediff / (60 * 60 * 24));
return $tempsPasse;
}
else {
return "";
}
}
public function getNombreNoteFicheParCategorie(Fiche $fiche,Categorie $categorie) {
$repo_note = $this->em->getRepository(Note::class);
$notes = $repo_note->findBy(["categorie" =>$categorie, "fiche" =>$fiche], ["dateDebut" =>"desc"]);
return count($notes);
}
public function getCompteurFichesClient(Client $client, $ficheId=''){
if(!empty($ficheId)){
$fiche = $this->em->getRepository(Fiche::class)->find($ficheId);
if(is_object($fiche) && $fiche->getClient() == $client) return $fiche->getNumeroFiche();
}
$nb = 0;
$fiches = $this->em->getRepository(Fiche::class)->findBy([
'client' => $client,
'dateSuppression' => NULL
], ['numeroFiche' =>'DESC'], 1, 0);
if(is_array($fiches) && count($fiches)) $nb = floatval($fiches[0]->getNumeroFiche());
$nb++;
$length = max(3, strlen($nb));
while(strlen($nb) < $length){ $nb = '0'.$nb; }
return $nb;
}
public function getTotalCaLoopgrade($fiche){
$commande = $this->em->getRepository(Commande::class)->findAffaireByFiche($fiche);
if(is_object($commande)) return $this->em->getRepository(ArticleCommande::class)->getTotalCaLoopgrade($commande);
return 0;
}
public function getMontantPondereFicheService($fiche){
return $this->em->getRepository(Fiche::class)->getMontantPondereFiche($fiche);
}
public function getColonnesEnfants($colonne, $retour = [], $count = 0){
if($count > 100) return $retour;
$retour[] = $colonne;
$kanbans = $this->em->getRepository(Kanban::class)->findBy(
[
'colonne' => $colonne
]
);
if(is_array($kanbans) && count($kanbans)){
foreach($kanbans as $kanban){
$colonnes = $this->em->getRepository(Colonne::class)->findBy(
[
'kanban' => $kanban
]
);
if(count($colonnes)) {
foreach($colonnes as $colonneTmp)
$retour = $this->getColonnesEnfants($colonneTmp, $retour, $count + 1);
}
}
}
return $retour;
}
}

src/Service/Kanban/KanbanService.php line 1589