Symfony Profiler

<?php
namespace App\Repository\Articles;
use App\Entity\Articles\Article;
use App\Entity\Articles\ConditionAchat;
use App\Entity\GestionComerciale\ArticleCommande;
use App\Entity\GestionComerciale\StatutCommande;
use App\Model\GestionCommerciale\TypeDocumentCommercial;
use DateInterval;
use DateTime;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
use Symfony\Component\Process\ProcessBuilder;
/**
* ArticleRepository
*
* This class was generated by the Doctrine ORM. Add your own custom
* repository methods below.
*/
class ArticleRepository extends ServiceEntityRepository
{
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, Article::class);
}
public function getArticleParReferenceV2($q)
{
$query = $this->createQueryBuilder('p')
//->select("COUNT(p.id) as total")
//->leftJoin('p.articleCategorie', 'ac')
->leftJoin('p.conditionnementAchatEan', 'ca')
->leftJoin('p.conditionsAchat', 'condHa')
->where(
'(
p.reference = :q
or
p.referenceAppel1 = :q
or
p.referenceAppel2 = :q
or
p.referenceAppel3 = :q
or
p.referenceAppel4 = :q
or
p.referenceAppel5 = :q
or
p.referenceAppel6 = :q
or
p.referenceAppel7 = :q
or
p.referenceAppel8 = :q
or
p.referenceAppel9 = :q
or
p.referenceAppel10 = :q
or
p.referenceAppel11 = :q
or
ca.identifiant = :q
or
condHa.referenceFournisseur = :q
)
and
(p.archive is null or p.archive = :archive)'
)
->setParameters(['q' => $q, 'archive' => '0']);
$res = $query->getQuery()->getOneOrNullResult();
return $res;
}
public function getArticleParReference($q, $parametres = ["mouvemente" => "1"])
{
$where_mouvemente = " (p.mouvemente = :mouvemente or p.mouvemente is null)";
if ($parametres["mouvemente"] == "1") {
$where_mouvemente = " p.mouvemente = :mouvemente";
}
$query = $this->createQueryBuilder('p')
//->select("COUNT(p.id) as total")
//->leftJoin('p.articleCategorie', 'ac')
->leftJoin('p.conditionnementAchatEan', 'ca')
->where(
'(
p.reference = :q
or
p.unspsc = :q
or
p.referenceAppel1 = :q
or
p.referenceAppel2 = :q
or
p.referenceAppel3 = :q
or
p.referenceAppel4 = :q
or
p.referenceAppel5 = :q
or
p.referenceAppel6 = :q
or
p.referenceAppel7 = :q
or
p.referenceAppel8 = :q
or
p.referenceAppel9 = :q
or
p.referenceAppel10 = :q
or
p.referenceAppel11 = :q
or
ca.identifiant = :q
)
and ('.$where_mouvemente.')
and
(p.archive is null or p.archive = :archive)'
)
->setParameters(['q' => $q, 'archive' => '0', 'mouvemente' => $parametres["mouvemente"]])
->setMaxResults(1);
//$res = $query->getQuery()->getOneOrNullResult();
$res = $query->getQuery()->getOneOrNullResult();
return $res;
}
public function testReferenceArticleExiste($reference = "")
{
$sql = "select count(id) as total FROM article__article where date_supression is NULL and reference = '".$reference."'";
$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
$resultats = $stmt->executeQuery()->fetchAllAssociative();
return $resultats;
}
public function getXmlArticles($param = [])
{
$where = 'p.mouvemente = :mouvemente and (p.divers is null or p.divers = :zero) and (p.diversComposes is null or p.diversComposes = :zero) and (p.mainOeuvre is null or p.mainOeuvre = :zero) and (p.prestation is null or p.prestation = :zero) and (p.nonStocke is null or p.nonStocke = :zero)';
$parametres_query = ['zero' => '0', 'mouvemente' => 1];
$where = 'p.surPrestashop = :surPrestashop and (p.divers is null or p.divers = :zero) and (p.diversComposes is null or p.diversComposes = :zero) and (p.mainOeuvre is null or p.mainOeuvre = :zero) and (p.prestation is null or p.prestation = :zero) and (p.nonStocke is null or p.nonStocke = :zero)';
$parametres_query = ['zero' => '0', 'surPrestashop' => 1];
if (array_key_exists('id_article', $param) && $param["id_article"] != "") {
$where .= " and p.id = :id_article";
$parametres_query["id_article"] = $param["id_article"];
} elseif (array_key_exists('type', $param) && $param["type"] == "date") {
$date = (new DateTime())->modify('-3 hours');
$where .= " and (p.dateMaj >= :dateMaj OR (p.stockReserve IS NOT NULL and p.stockReserve != 0) OR p.virtuel = 1)";
$parametres_query["dateMaj"] = $date;
} elseif (array_key_exists('type', $param) && $param["type"] == "ref-appel") {
}
//echo $where;
//exit;
$query = $this->createQueryBuilder('p')
//->select("COUNT(p.id) as total")
->leftJoin('p.marque', 'm')
->where($where)
->setParameters($parametres_query)
->setMaxResults(10000000000);
$res = $query->getQuery()->getResult();
return $res;
}
public function getArticlesId()
{
$query = $this->createQueryBuilder('p')
->select("p.id");
$res = $query->getQuery()->getResult();
return $res;
}
public function getArticlesIdPumpNull()
{
$query = $this->createQueryBuilder('p')
->select("p.id")
->where('p.pump is null or p.pump = 0 or p.pumpHorsFrais is null or p.pumpHorsFrais = 0');
$res = $query->getQuery()->getResult();
return $res;
}
public function getTotalArticleCategorie($categorie)
{
$query = $this->createQueryBuilder('p')
->select("COUNT(p.id) as total")
->leftJoin('p.articleCategorie', 'ac')
->where('ac.categorie = :categorie and p.temporaire is null and (p.archive = :archive or p.archive is null)')
->setParameters(['categorie' => $categorie, 'archive' => '0']);
$res = $query->getQuery()->getOneOrNullResult();
return $res;
}
public function getTotalArticleMarque($marque)
{
$query = $this->createQueryBuilder('p')
->select("COUNT(p.id) as total")
->where('p.marque = :marque and p.temporaire is null and (p.archive = :archive or p.archive is null)')
->setParameters(['marque' => $marque, 'archive' => '0']);
$res = $query->getQuery()->getOneOrNullResult();
return $res;
}
public function getRechercheArticle($recherche, $maxResults = 500, $parametres = ["mouvemente" => "1"])
{
$cond = $recherche."%";
$where_mouvemente = " (p.mouvemente = :mouvemente or p.mouvemente is null)";
if ($parametres["mouvemente"] == "1") {
$where_mouvemente = " p.mouvemente = :mouvemente";
}
if (strpos($cond, "*") !== false) {
//$cond = str_replace("*","%",$cond);
}
$query = $this->createQueryBuilder('p')
//->select("CONCAT(CONCAT(p.prenom, ' '), p.nom) as libelle, p.id")
//->where('(p.libelle LIKE :cond or p.reference LIKE :cond) and p.temporaire is null and p.archive = :archive')
->where(
'( p.referenceAppel1 LIKE :cond
or p.referenceAppel2 LIKE :cond
or p.referenceAppel3 LIKE :cond
or p.referenceAppel4 LIKE :cond
or p.referenceAppel5 LIKE :cond
or p.referenceAppel6 LIKE :cond
or p.referenceAppel7 LIKE :cond
or p.referenceAppel8 LIKE :cond
or p.referenceAppel9 LIKE :cond
or p.referenceAppel10 LIKE :cond
or p.referenceAppel11 LIKE :cond
or p.reference LIKE :cond )
and p.temporaire is null
and ('.$where_mouvemente.')
'
)
//->leftJoin('p.conditionsAchat', 'ha')
->setParameters(['cond' => $cond, 'mouvemente' => $parametres["mouvemente"]])
//->orderBy('p.prenom', 'ASC');
->setFirstResult(0)
->setMaxResults($maxResults)
->orderBy('p.reference', 'ASC');
//dump($query->getQuery()->getSql());
$res = $query->getQuery()->getResult();
return $res;
}
public function oldgetRechercheArticle($recherche, $maxResults = 500, $parametres = ["mouvemente" => "1"])
{
$cond = $recherche."%";
$where_mouvemente = " (p.mouvemente = :mouvemente or p.mouvemente is null)";
if ($parametres["mouvemente"] == "1") {
$where_mouvemente = " p.mouvemente = :mouvemente";
}
if (strpos($cond, "*") !== false) {
//$cond = str_replace("*","%",$cond);
}
$query = $this->createQueryBuilder('p')
//->select("CONCAT(CONCAT(p.prenom, ' '), p.nom) as libelle, p.id")
//->where('(p.libelle LIKE :cond or p.reference LIKE :cond) and p.temporaire is null and p.archive = :archive')
->where(
'( p.referenceAppel1 LIKE :cond
or
p.referenceAppel2 LIKE :cond
or
p.referenceAppel3 LIKE :cond
or
p.referenceAppel4 LIKE :cond
or
p.referenceAppel5 LIKE :cond
or
p.referenceAppel6 LIKE :cond
or
p.referenceAppel7 LIKE :cond
or
p.referenceAppel8 LIKE :cond
or
p.referenceAppel9 LIKE :cond
or
p.referenceAppel10 LIKE :cond
or
p.referenceAppel11 LIKE :cond
or
p.libelle LIKE :cond or p.reference LIKE :cond or ha.referenceFournisseur LIKE :cond or ha.libelle LIKE :cond)
and p.temporaire is null
and ('.$where_mouvemente.')
and p.parent IS NULL'
)
->leftJoin('p.conditionsAchat', 'ha')
->setParameters(['cond' => $cond, 'mouvemente' => $parametres["mouvemente"]])
//->orderBy('p.prenom', 'ASC');
->setFirstResult(0)
->setMaxResults($maxResults)
->orderBy('p.reference', 'ASC');
$res = $query->getQuery()->getResult();
return $res;
}
public function _____getRechercheArticle($recherche, $maxResults = 500)
{
$cond = $recherche."%";
if (strpos($cond, "*") !== false) {
$cond = str_replace("*", "%", $cond);
}
$query = $this->createQueryBuilder('p')
//->select("CONCAT(CONCAT(p.prenom, ' '), p.nom) as libelle, p.id")
//->where('(p.libelle LIKE :cond or p.reference LIKE :cond) and p.temporaire is null and p.archive = :archive')
->where('(p.libelle LIKE :cond or p.reference LIKE :cond or p.referenceWeb = :recherche) and p.temporaire is null and p.parent IS NULL')
//->setParameters(array('cond' => $cond,'archive'=>'0'))
->setParameters(['cond' => $cond, 'recherche' => $recherche])
//->orderBy('p.prenom', 'ASC');
->setFirstResult(0)
->setMaxResults($maxResults)
->orderBy('p.reference', 'ASC');
$res = $query->getQuery()->getResult();
return $res;
}
//$resultats = $this->getRechercheArticleSelect($recherche, $produitInterne, $fournisseur, $articlesAExclure,$stock,$reference_search,$libelle_search,$prix_search,$tri_reference,$tri_libelle,$tri_prix,$tri_stock, $categorie, 0);
public function getRechercheArticleSelect($recherche, $produitInterne = null, $fournisseur = null, $articlesAExclure = null, $stock = "", $reference_search = "", $libelle_search = "",
$prix_search = "", $tri_reference = "", $tri_libelle = "", $tri_prix = "", $tri_stock = "", $categories = "", $mouvemente = 1, $inventaire = "",
$declinaisons = false
) {
//$echo "(".$recherche.")";
$match = 'p.reference,p.libelle,p.reference_appel_1,p.reference_appel_2,p.reference_appel_3,p.reference_appel_4,p.reference_appel_5,p.reference_appel_6,p.reference_appel_7,p.reference_appel_8,p.reference_appel_9,p.reference_appel_10,p.reference_appel_11';
$match2 = 'ca.reference_fournisseur, ca.reference_fournisseur_appel';
$select = "";
$jointureFromInventaire = '';
$jointureWhereInventaire = "";
$recherche = str_replace("*", "%", $recherche);
$recherche = str_replace(".", "", $recherche);
$recherche = str_replace("/", "\/", $recherche);
$recherche = str_replace(" ", " ", $recherche);
$recherche = str_replace(" ", " ", $recherche);
$recherche_ref_fourn = $recherche;
//$recherche = str_replace("-"," ",$recherche);
$recherche_tmp = $recherche;
$avecEtoile = false;
$pos = strpos($recherche, "*");
if ($pos !== false) {
$avecEtoile = true;
}
//echo "<div>R(".$recherche.")</div>";
$wherePrix = "";
$tampon_recherche = $recherche;
$recherche = trim($recherche);
if ( ! $avecEtoile) {
$recherche = str_replace(" ", "* ", $recherche);
}
if ( ! $avecEtoile) {
$recherche .= "*";
}
$whereSuppl = " and p.parent_id is null and (p.statut is null or p.statut = 1)";
$whereStock = "";
if ($stock == 1) {
$whereStock .= " and p.stock > 0";
}
$order = "";
if ($tri_reference != "") {
$order = "p.reference ".$tri_reference.", ";
}
if ($tri_prix != "") {
$order = "p.prix_vente ".$tri_prix.", ";
}
if ($tri_stock != "") {
$order = "p.stock ".$tri_stock.", ";
}
if ($tri_libelle != "") {
$order = "p.libelle ".$tri_libelle.", ";
}
if ($order == "") {
$order = "p.reference ASC ";
}
$order = str_replace(",", "", $order);
//echo "<div>order : ".$order."</div>";
$jointureFrom = "";
$jointureWhere = "";
$jointureFrom .= ' LEFT JOIN article__condition_achat ca on ca.article_id = p.id ';
if ( ! ($fournisseur == null || $fournisseur == '')) {
$match2 = 'ca.reference_fournisseur, ca.reference_fournisseur_appel';
$jointureFrom .= " INNER JOIN article__condition_achat as ca on ca.article_id = p.id";
//$jointureWhere .= " and (p.non_commandable = 0 or p.non_commandable = 1 or p.non_commandable is null)";
$jointureWhere .= "(p.id IN (select
ca.article_id
FROM article__condition_achat as ca
WHERE
ca.fournisseur_id = ".$fournisseur." )
OR p.divers=1 ";
if ($categories != '') {
$jointureFrom .= ' LEFT JOIN article__article_categorie acat on acat.article_id = p.id LEFT JOIN article__categorie as cat on cat.id = acat.categorie_id';
foreach ($categories as $categorie) {
$jointureWhere .= " OR cat.libelle LIKE '".$categorie."'";
}
}
$jointureWhere .= ")";
$whereSuppl .= " and (p.arret_gamme != 1 or p.arret_gamme IS NULL)";
}
if (($fournisseur == null || $fournisseur == '') && $categories != '') {
$jointureFrom .= ' LEFT JOIN article__article_categorie acat on acat.article_id = p.id JOIN article__categorie as cat on cat.id = acat.categorie_id';
//$jointureWhere .= " and cat.libelle LIKE '".$categorie."%'";
foreach ($categories as $key => $categorie) {
if ($key == 0) {
$jointureWhere .= " and (";
} else {
$jointureWhere .= " OR";
}
$jointureWhere .= " cat.libelle LIKE '".$categorie."'";
}
$jointureWhere .= ")";
}
if ($produitInterne == true || $produitInterne == 'true') {
$whereSuppl .= ' and p.produit_interne =1 and (p.virtuel = 0 or p.virtuel is null)';
}
if (count($articlesAExclure) > 0) {
$whereSuppl .= " and p.id NOT IN ( '".implode($articlesAExclure, "', '")."' )";// != :articleAExclure and p.id != :articleAExclure
}
/*
if ($mouvemente == "")
$whereSuppl .= " and p.divers = 1";
else
$whereSuppl .= " and (p.mouvemente = ".$mouvemente." or p.divers = 1)";
*
*/
if ($mouvemente != "0" && $mouvemente !== null or ($mouvemente == "")) {
$mouvemente = 1;
$whereSuppl .= " and (p.mouvemente = ".$mouvemente." or p.divers = 1)";
} else {
//echo "titit (".$mouvemente.")";
$whereSuppl .= " and (p.mouvemente = ".$mouvemente." or p.mouvemente =1 or p.mouvemente is null or p.divers = 1)";
}
//echo 'mouvemente : '.$mouvemente;
if ( ! $avecEtoile) {
//$recherche = str_replace("-", "", $recherche);
}
if ($inventaire != '') {
//$select .= ', count(ai.id) as nbArticleInventaire';
$select .= ', COUNT(CASE WHEN ai.inventaire_id = '.$inventaire.' then 1 ELSE NULL END) as "nbArticleInventaire"';
$jointureFromInventaire .= ' LEFT JOIN inventaire__article_inventaire ai ON ai.article_id = p.id';
//$jointureWhereInventaire .= ' and (ai.inventaire_id = '.$inventaire.' OR ai.inventaire_id IS NULL)';
}
//echo "<div>S : ".$recherche."</div>";
if ($recherche != "" and $recherche != "*") {
if ($prix_search == 1) {
$sql = "
select
p.libelle_secondaire,
p.reference_appel_1,
p.reference_appel_2,
p.reference_appel_3,
p.reference_appel_4,
p.reference_appel_5,
p.reference_appel_6,
p.reference_appel_7,
p.reference_appel_8,
p.reference_appel_9,
p.reference_appel_10,
p.reference_appel_11,
CONCAT(CONCAT(p.reference, ' '), p.libelle) as libelle_construit,
p.virtuel,
p.arret_gamme,
p.article_remplacement_id,
p.libelle,
p.id,
p.prix_vente as prixVente,
p.prix_base as prixAchat,
p.poids,
p.stock,
p.stock_reserve_fabrication,
p.stock_reserve,
p.reference,
p.pump,
p.cpump,
MATCH (".$match.") AGAINST ('".$recherche."' IN BOOLEAN MODE) as ponderation"
.$select
." FROM article__article as p"
.$jointureFromInventaire
." WHERE
p.temporaire is null
and
(p.archive = 0 or p.archive is null)
and
p.prix_vente like '".$tampon_recherche."%'
".$whereStock."
".$whereSuppl.$jointureWhereInventaire."
GROUP BY p.id
order by ".$order.", p.id desc
LIMIT 0,100
";
//print_r($sql);
} else {
/*
p.reference LIKE '".$recherche."'
OR
p.libelle LIKE '".$recherche."'
OR
reference_appel_1 LIKE '".$recherche."'
OR
reference_appel_2 LIKE '".$recherche."'
OR
reference_appel_3 LIKE '".$recherche."'
OR
reference_appel_4 LIKE '".$recherche."'
OR
reference_appel_5 LIKE '".$recherche."'
OR
reference_appel_6 LIKE '".$recherche."'
OR
reference_appel_7 LIKE '".$recherche."'
OR
reference_appel_8 LIKE '".$recherche."'
OR
reference_appel_9 LIKE '".$recherche."'
OR
reference_appel_10 LIKE '".$recherche."'
OR
reference_appel_11 LIKE '".$recherche."'
*/
if ($avecEtoile) {
if ($order == "") {
$order = "p.reference";
}
$order = str_replace(",", "", $order);
$recherche .= "%";
$sql = "
select
p.libelle_secondaire,
p.reference_appel_1,
p.reference_appel_2,
p.reference_appel_3,
p.reference_appel_4,
p.reference_appel_5,
p.reference_appel_6,
p.reference_appel_7,
p.reference_appel_8,
p.reference_appel_9,
p.reference_appel_10,
p.reference_appel_11,
CONCAT(CONCAT(p.reference, ' '), p.libelle) as libelle_construit,
p.arret_gamme,
p.virtuel,
p.article_remplacement_id,
p.libelle,
p.id,
p.prix_vente as prixVente,
p.prix_base as prixAchat,
p.poids,
p.stock,
p.stock_reserve_fabrication,
p.stock_reserve,
p.pump,
p.cpump,
p.reference"
.$select
." FROM article__article as p ".$jointureFrom.$jointureFromInventaire."
WHERE
p.temporaire is null
and
(p.archive = 0 or p.archive is null) ".$whereSuppl."
and
(
p.reference LIKE '".$recherche."'
OR
p.libelle LIKE '".$recherche."'
OR
p.reference_appel_1 LIKE '".$recherche."'
OR
p.reference_appel_2 LIKE '".$recherche."'
OR
p.reference_appel_3 LIKE '".$recherche."'
OR
p.reference_appel_4 LIKE '".$recherche."'
OR
p.reference_appel_5 LIKE '".$recherche."'
OR
p.reference_appel_6 LIKE '".$recherche."'
OR
p.reference_appel_7 LIKE '".$recherche."'
OR
p.reference_appel_8 LIKE '".$recherche."'
OR
p.reference_appel_9 LIKE '".$recherche."'
OR
p.reference_appel_10 LIKE '".$recherche."'
OR
p.reference_appel_11 LIKE '".$recherche."'";
if ($match2 != "") {
$sql .= "OR ca.reference_fournisseur LIKE '".$recherche."'";
$sql .= "OR ca.reference_fournisseur_appel LIKE '".$recherche."'";
}
$sql .= " )
".$wherePrix.$whereStock.$jointureWhere.$jointureWhereInventaire."
GROUP BY p.id
order by ".$order."
LIMIT 0,100
";
//echo $sql;
//exit;
} else {
//echo "OK";
//exit;
$recherche_contruit = "";
$recherche_tab = explode(" ", $recherche);
//print_r($recherche_tab);
for ($r = 0; $r < count($recherche_tab); $r++) {
//$recherche_contruit .="+".$recherche_tab[$r]." ";
$recherche_tab[$r] = str_replace("+", "#+", $recherche_tab[$r]);
$recherche_contruit .= "+".$recherche_tab[$r]." ";
}
//echo "<div>SUPPL ".$whereSuppl."</div>";
$recherche = trim($recherche, "*");
$sql = "
select
p.dateProchaineDispo,
p.reference_appel_1,
p.libelle_secondaire,
p.reference_appel_2,
p.reference_appel_3,
p.reference_appel_4,
p.reference_appel_5,
p.reference_appel_6,
p.reference_appel_7,
p.reference_appel_8,
p.reference_appel_9,
p.reference_appel_10,
p.reference_appel_11,
p.stock_reserve_fabrication,
p.virtuel,
CONCAT(CONCAT(p.reference, ' '), p.libelle) as libelle_construit,
p.article_remplacement_id,
p.libelle,
p.id,
p.prix_vente as prixVente,
p.prix_base as prixAchat,
p.poids,
p.stock,
p.stock_reserve,
p.reference,
p.pump,
p.cpump,
p.arret_gamme,
marque.libelle as libelle_marque,
marque.id as id_marque,
marque.logo as logo_marque,
MATCH (".$match.") AGAINST ('".$recherche_contruit."' IN BOOLEAN MODE) as ponderation"
.$select
." FROM article__article as p ".$jointureFrom.$jointureFromInventaire."
LEFT JOIN article__marque as marque ON marque.id = p.marque_id
WHERE ";
if ($jointureWhere != "") {
$sql .= $jointureWhere." and ";
}
$sql .= " p.temporaire is null
and ( marque.statut = 1 or marque.id is null) and (p.archive = 0 or p.archive is null) ".$whereSuppl."
and
(MATCH (".$match.")
AGAINST ('".$recherche_contruit."' IN BOOLEAN MODE)";
if ($match2 != "" and 1 != 1) {
$sql .= "OR ca.reference_fournisseur LIKE '".$recherche_ref_fourn."%'";
$sql .= "OR ca.reference_fournisseur_appel LIKE '".$recherche_ref_fourn."%'";
}
// if($match2 != "") $sql .= " OR MATCH (".$match2.") AGAINST ('".$recherche_ref_fourn."' IN BOOLEAN MODE) as ponderation2";
$sql .= "";
$sql .= $wherePrix.$whereStock;
$sql .= " OR (p.reference LIKE '%".$recherche."%') ";
$sql .= ")";
$sql .= $jointureWhereInventaire;
$sql .= " GROUP BY p.id
order by ".$order.", ponderation desc
LIMIT 0,100
";
// echo $sql;
/*
$jointureFrom = "";
$sql = "
select
p.stock_reserve_fabrication,
p.virtuel,
CONCAT(CONCAT(p.reference, ' '), p.libelle) as libelle_construit,
p.article_remplacement_id,
p.libelle,
p.id,
p.prix_vente as prixVente,
p.prix_base as prixAchat,
p.poids,
p.stock,
p.stock_reserve,
p.reference,
p.pump,
p.cpump,
p.arret_gamme
"
.$select
." FROM article__article as p ".$jointureFrom.$jointureFromInventaire."
WHERE ";
if($jointureWhere != "") $sql .= $jointureWhere." and ";
$sql .= " p.temporaire is null
and (p.archive = 0 or p.archive is null) ".$whereSuppl."
and
p.reference LIKE '".$recherche_tmp."%'
";
if($match2 != "" and 1!=1) {
$sql .= "OR ca.reference_fournisseur LIKE '".$recherche_ref_fourn."%'";
$sql .= "OR ca.reference_fournisseur_appel LIKE '".$recherche_ref_fourn."%'";
}
// if($match2 != "") $sql .= " OR MATCH (".$match2.") AGAINST ('".$recherche_ref_fourn."' IN BOOLEAN MODE) ";
$sql .= "";
$sql .= $wherePrix.$whereStock;
//$sql .= ")";
$sql .= $jointureWhereInventaire;
$sql .= " GROUP BY p.id
order by ".$order."
LIMIT 0,100
";
*/
//echo $sql;
//exit;
}
}
//echo "ORDER(".$order.")";
} else {
//$order .= "p.reference asc";
$sql = "
select
p.reference_appel_1,
p.libelle_secondaire,
p.reference_appel_2,
p.reference_appel_3,
p.reference_appel_4,
p.reference_appel_5,
p.reference_appel_6,
p.reference_appel_7,
p.reference_appel_8,
p.reference_appel_9,
p.reference_appel_10,
p.reference_appel_11,
CONCAT(CONCAT(p.reference, ' '), p.libelle) as libelle_construit,
p.arret_gamme,
p.virtuel,
p.article_remplacement_id,
p.virtuel,
p.libelle,
p.id,
p.prix_vente as prixVente,
p.prix_base as prixAchat,
p.poids,
p.stock,
p.stock_reserve_fabrication,
p.stock_reserve,
p.cpump,
p.reference"
.$select
." FROM article__article as p ".$jointureFrom.$jointureFromInventaire."
WHERE ";
if ($jointureWhere != "") {
$sql .= $jointureWhere." and ";
}
$sql .= " p.temporaire is null
and
(p.archive = 0 or p.archive is null)
".$whereSuppl.$whereStock.$jointureWhereInventaire."
GROUP BY p.id
order by ".$order."
LIMIT 0,100
";
}
//where p.libelle like '%".$recherche."%' or p.reference like '%".$recherche."%'
//echo $sql;
//exit;
$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
$resultats = $stmt->executeQuery()->fetchAllAssociative();
//$resultats = array();
//echo $sql;
//exit;
//echo "<div>------(".$mouvemente.")</div>";
//Si la recherche dans les articles mouvementés ne donne rien, on effectue la requete sur les articles non mouvementés
if (count($resultats) == 0 && $mouvemente == "1") {
//$mouvemente =0;
/*if(!$avecEtoile) {
$recherche = str_replace("*","",$recherche);
}
*/
//echo "<div>ototo ".$recherche."</div>";
//echo "--".$mouvemente;
//exit;
$resultats = $this->getRechercheArticleSelect(
$recherche_tmp,
$produitInterne,
$fournisseur,
$articlesAExclure,
$stock,
$reference_search,
$libelle_search,
$prix_search,
$tri_reference,
$tri_libelle,
$tri_prix,
$tri_stock,
$categories,
"0",
$inventaire
);
//exit;
}
//print_r($resultats);
//exit;
return $resultats;
}
public function getRechercheArticleSelect2($recherche, $produitInterne = null, $fournisseur = null, $articlesAExclure = null, $stock = "", $reference_search = "", $libelle_search = "",
$prix_search = "", $tri_reference = "", $tri_libelle = "", $tri_prix = "", $tri_stock = "", $categories = "", $mouvemente = 1, $inventaire = ''
) {
$sql2 = "";
$sql3 = "";
$match = 'p.reference,p.libelle,p.reference_appel_1,p.reference_appel_2,p.reference_appel_3,p.reference_appel_4,p.reference_appel_5,p.reference_appel_6,p.reference_appel_7,p.reference_appel_8,p.reference_appel_9,p.reference_appel_10,p.reference_appel_11';
$match2 = "";
$select = '';
$jointureFromInventaire = "";
$jointureWhereInventaire = "";
$recherche = str_replace("*", "%", $recherche);
//$recherche = str_replace("-"," ",$recherche);
$recherche = str_replace(".", "", $recherche);
$recherche = str_replace("/", "\/", $recherche);
$recherche = str_replace(" ", " ", $recherche);
$recherche_tmp = $recherche;
$avecEtoile = false;
$pos = strpos($recherche, "*");
if ($pos !== false) {
$avecEtoile = true;
}
$recherche = str_replace("*", "%", $recherche);
$wherePrix = "";
$tampon_recherche = $recherche;
$recherche = trim($recherche);
if ( ! $avecEtoile) {
$recherche = str_replace(" ", "* ", $recherche);
}
if ( ! $avecEtoile) {
$recherche .= "*";
}
$whereSuppl = " and p.parent_id is null and (p.statut = 1 or p.statut is null) ";
$whereStock = "";
if ($stock == 1) {
$whereStock .= " and p.stock > 0";
}
$order = "";
if ($tri_reference != "") {
$order = "p.reference ".$tri_reference.", ";
}
if ($tri_prix != "") {
$order = "p.prix_vente ".$tri_prix.", ";
}
if ($tri_stock != "") {
$order = "p.stock ".$tri_stock.", ";
}
if ($tri_libelle != "") {
$order = "p.libelle ".$tri_libelle.", ";
}
if ($order == "") {
$order = "p.reference ASC, ";
}
//echo "<div>order : ".$order."</div>";
$jointureFrom = "";
$jointureWhere = "";
$jointureWhere2 = "";
if ( ! ($fournisseur == null || $fournisseur == '')) {
$match2 = 'ca.reference_fournisseur, ca.reference_fournisseur_appel';
if ($categories != '') {
$selectTemp = "(select id FROM article__categorie cat WHERE ";
foreach ($categories as $key => $categorie) {
if ($key == 0) {
$selectTemp .= " ";
} else {
$selectTemp .= " OR";
}
$selectTemp .= " cat.reference LIKE '".$categorie."'";
}
$selectTemp .= ") as tempAC ";
$jointureFrom .= "LEFT JOIN ".$selectTemp." LEFT JOIN article__article_categorie as aac ON aac.categorie_id = tempAC.id";
}
$whereSuppl .= " and (p.arret_gamme != 1 or p.arret_gamme IS NULL)";
}
if ($produitInterne == true || $produitInterne == 'true') {
$whereSuppl .= ' and p.produit_interne =1';
}
if (count($articlesAExclure) > 0) {
$whereSuppl .= " and p.id NOT IN ( '".implode($articlesAExclure, "', '")."' )";// != :articleAExclure and p.id != :articleAExclure
}
if ($mouvemente != "0" && $mouvemente !== null or ($mouvemente == "")) {
$whereSuppl .= " and (p.mouvemente = 1 or p.divers = 1)";
} else {
//$whereSuppl .= " and p.divers = 1";
}
if ( ! $avecEtoile) {
$recherche = str_replace("-", "", $recherche);
}
if ($inventaire != '') {
//$select .= ', count(ai.id) as nbArticleInventaire';
$select .= ', COUNT(CASE WHEN ai.inventaire_id = '.$inventaire.' then 1 ELSE NULL END) as "nbArticleInventaire"';
$jointureFromInventaire .= ' LEFT JOIN inventaire__article_inventaire ai ON ai.article_id = p.id';
//$jointureWhereInventaire .= ' and (ai.inventaire_id = '.$inventaire.' OR ai.inventaire_id IS NULL)';
}
if ($recherche != "" and $recherche != "*") {
if ($prix_search == 1) {
$sql = "
select
CONCAT(CONCAT(p.reference, ' '), p.libelle) as libelle_construit,
p.reference_appel_1,
p.reference_appel_2,
p.reference_appel_3,
p.reference_appel_4,
p.reference_appel_5,
p.reference_appel_6,
p.reference_appel_7,
p.reference_appel_8,
p.reference_appel_9,
p.reference_appel_10,
p.reference_appel_11,
p.arret_gamme,
p.virtuel,
p.divers,
p.article_remplacement_id,
p.libelle,
p.id,
p.reference_fournisseur,
p.prix_vente as prixVente,
p.prix_base as prixAchat,
p.poids,
p.stock,
p.stock_reserve_fabrication,
p.stock_reserve,
p.reference,
p.pump,
p.cpump,
MATCH (".$match.") AGAINST ('".$recherche."' IN BOOLEAN MODE) as ponderation"
.$select
." FROM article__article as p"
.$jointureFromInventaire
." WHERE
p.temporaire is null
and
(p.archive = 0 or p.archive is null)
and
p.prix_vente like '".$tampon_recherche."%'
".$whereStock."
".$whereSuppl.$jointureWhereInventaire."
GROUP BY p.id
order by ".$order." p.id desc
LIMIT 0,100
";
} else {
/*
p.reference LIKE '".$recherche."'
OR
p.libelle LIKE '".$recherche."'
OR
reference_appel_1 LIKE '".$recherche."'
OR
reference_appel_2 LIKE '".$recherche."'
OR
reference_appel_3 LIKE '".$recherche."'
OR
reference_appel_4 LIKE '".$recherche."'
OR
reference_appel_5 LIKE '".$recherche."'
OR
reference_appel_6 LIKE '".$recherche."'
OR
reference_appel_7 LIKE '".$recherche."'
OR
reference_appel_8 LIKE '".$recherche."'
OR
reference_appel_9 LIKE '".$recherche."'
OR
reference_appel_10 LIKE '".$recherche."'
OR
reference_appel_11 LIKE '".$recherche."'
*/
if ($avecEtoile) {
if ($order == "") {
$order = "p.reference";
}
$order = str_replace(",", "", $order);
$recherche .= "%";
$sql = "
select
CONCAT(CONCAT(p.reference, ' '), p.libelle) as libelle_construit,
p.reference_appel_1,
p.reference_appel_2,
p.reference_appel_3,
p.reference_appel_4,
p.reference_appel_5,
p.reference_appel_6,
p.reference_appel_7,
p.reference_appel_8,
p.reference_appel_9,
p.reference_appel_10,
p.reference_appel_11,
p.arret_gamme,
p.virtuel,
p.divers,
p.article_remplacement_id,
p.libelle,
p.id,
p.reference_fournisseur,
p.prix_vente as prixVente,
p.prix_base as prixAchat,
p.poids,
p.stock,
p.stock_reserve_fabrication,
p.stock_reserve,
p.pump,
p.cpump,
p.reference"
.$select
." FROM article__article as p ".$jointureFrom.$jointureFromInventaire."
WHERE
p.temporaire is null
and
(p.archive = 0 or p.archive is null) ".$whereSuppl."
and
(
p.reference LIKE '".$recherche."'
OR
p.libelle LIKE '".$recherche."'
OR
p.reference_appel_1 LIKE '".$recherche."'
OR
p.reference_appel_2 LIKE '".$recherche."'
OR
p.reference_appel_3 LIKE '".$recherche."'
OR
p.reference_appel_4 LIKE '".$recherche."'
OR
p.reference_appel_5 LIKE '".$recherche."'
OR
p.reference_appel_6 LIKE '".$recherche."'
OR
p.reference_appel_7 LIKE '".$recherche."'
OR
p.reference_appel_8 LIKE '".$recherche."'
OR
p.reference_appel_9 LIKE '".$recherche."'
OR
p.reference_appel_10 LIKE '".$recherche."'
OR
p.reference_appel_11 LIKE '".$recherche."'";
if ($match2 != "") {
$sql .= "OR ca.reference_fournisseur LIKE '".$recherche."'";
$sql .= "OR ca.reference_fournisseur_appel LIKE '".$recherche."'";
}
$sql .= " )
".$wherePrix.$whereStock.$jointureWhere.$jointureWhereInventaire."
GROUP BY p.id
order by ".$order."
LIMIT 0,100
";
//echo $sql;
//exit;
} else {
$recherche_contruit = "";
$recherche_tab = explode(" ", $recherche);
for ($r = 0; $r < count($recherche_tab); $r++) {
//$recherche_contruit .="+".$recherche_tab[$r]." ";
$recherche_tab[$r] = str_replace("+", "#+", $recherche_tab[$r]);
$recherche_contruit .= "+".$recherche_tab[$r]." ";
}
//echo "<div>SUPPL ".$whereSuppl."</div>";
/*
$sql = "
select
p.stock_reserve_fabrication,
CONCAT(CONCAT(p.reference, ' '), p.libelle) as libelle_construit,
p.article_remplacement_id,
p.libelle,
p.id,
p.prix_vente as prixVente,
p.prix_base as prixAchat,
p.poids,
p.stock,
p.stock_reserve,
p.reference,
p.pump,
p.arret_gamme,
MATCH (".$match.") AGAINST ('".$recherche_contruit."' IN BOOLEAN MODE) as ponderation
FROM article__article as p ".$jointureFrom."
WHERE ";
if($jointureWhere != "") $sql .= $jointureWhere." and ";
$sql .= " p.temporaire is null
and p.archive = 0".$whereSuppl."
and
(MATCH (".$match.")
AGAINST ('".$recherche_contruit."' IN BOOLEAN MODE)";
if($match2 != "") $sql .= " OR MATCH (".$match2.") AGAINST ('".$recherche_contruit."' IN BOOLEAN MODE) ";
$sql .= "";
$sql .= $wherePrix.$whereStock;
$sql .= ")
GROUP BY p.id
order by ".$order.", ponderation desc
LIMIT 0,100
";
//echo $sql;
}
*/
//pre select on condition achat before match
if ($match2 != '') {
$sql = "select
p.libelle_secondaire,
p.reference_appel_1,
p.reference_appel_2,
p.reference_appel_3,
p.reference_appel_4,
p.reference_appel_5,
p.reference_appel_6,
p.reference_appel_7,
p.reference_appel_8,
p.reference_appel_9,
p.reference_appel_10,
p.reference_appel_11,
temp.reference_fournisseur as reference_fournisseur,
p.stock_reserve_fabrication,
CONCAT(CONCAT(p.reference, ' '), p.libelle) as libelle_construit,
p.article_remplacement_id,
p.libelle,
p.virtuel,
p.divers,
p.id,
p.reference_fournisseur as old,
p.prix_vente as prixVente,
p.prix_base as prixAchat,
p.poids,
p.stock,
p.stock_reserve,
p.reference,
p.pump,
p.cpump,
p.arret_gamme,
MATCH (".$match.") AGAINST ('".$recherche_contruit."' IN BOOLEAN MODE) as ponderation";
$sql .= $select;
$sql .= " FROM (select
*
FROM article__condition_achat
WHERE
date_supression is null and
fournisseur_id = ".$fournisseur." ) AS temp ";
$sql .= " LEFT JOIN article__article as p ON temp.article_id = p.id";
$sql .= $jointureFromInventaire
//. " LEFT JOIN article__condition_achat ca ON ca.id = p.id"
." WHERE ";
if ($jointureWhere2 != "") {
$sql .= $jointureWhere2." and ";
}
$sql .= " p.temporaire is null
and (p.archive = 0 or p.archive is null) ".$whereSuppl."
and
(MATCH (".$match.")
AGAINST ('".$recherche_contruit."' IN BOOLEAN MODE)"
." OR MATCH (temp.reference_fournisseur, temp.reference_fournisseur_appel) AGAINST ('".$recherche_contruit."' IN BOOLEAN MODE) ";
$sql .= $wherePrix.$whereStock;
$sql .= ")";
$sql .= $jointureWhereInventaire;
$sql .= " GROUP BY p.id
order by ".$order." ponderation desc
LIMIT 0,100
";
$sql2 = "select
p.stock_reserve_fabrication,
CONCAT(CONCAT(p.reference, ' '), p.libelle) as libelle_construit,
p.reference_appel_1,
p.libelle_secondaire,
p.reference_appel_2,
p.reference_appel_3,
p.reference_appel_4,
p.reference_appel_5,
p.reference_appel_6,
p.reference_appel_7,
p.reference_appel_8,
p.reference_appel_9,
p.reference_appel_10,
p.reference_appel_11,
p.article_remplacement_id,
p.libelle,
p.virtuel,
p.divers,
p.id,
p.reference_fournisseur,
p.prix_vente as prixVente,
p.prix_base as prixAchat,
p.poids,
p.stock,
p.stock_reserve,
p.reference,
p.pump,
p.cpump,
p.arret_gamme,
MATCH (".$match.") AGAINST ('".$recherche_contruit."' IN BOOLEAN MODE) as ponderation ";
$sql2 .= "FROM ".$selectTemp;
$sql2 .= $jointureFromInventaire;
$sql2 .= "LEFT JOIN article__article_categorie as aac ON aac.categorie_id = tempAC.id LEFT JOIN article__article as p ON aac.article_id = p.id"
//. " LEFT JOIN article__condition_achat ca ON ca.id = p.id"
." WHERE ";
if ($jointureWhere2 != "") {
$sql .= $jointureWhere2." and ";
}
$sql2 .= " p.temporaire is null
and (p.archive = 0 or p.archive is null) ".$whereSuppl."
and
(MATCH (".$match.")
AGAINST ('".$recherche_contruit."' IN BOOLEAN MODE)"//. " OR MATCH (ca.reference_fournisseur, ca.reference_fournisseur_appel) AGAINST ('".$recherche_contruit."' IN BOOLEAN MODE) ";
;
$sql2 .= $wherePrix.$whereStock;
$sql2 .= ")";
$sql2 .= $jointureWhereInventaire;
$sql2 .= " GROUP BY p.id
order by ".$order." ponderation desc
LIMIT 0,100
";
//sql3
$sql3 = "select
p.stock_reserve_fabrication,
CONCAT(CONCAT(p.reference, ' '), p.libelle) as libelle_construit,
p.reference_appel_1,
p.reference_appel_2,
p.reference_appel_3,
p.reference_appel_4,
p.reference_appel_5,
p.reference_appel_6,
p.reference_appel_7,
p.reference_appel_8,
p.reference_appel_9,
p.reference_appel_10,
p.reference_appel_11,
p.article_remplacement_id,
p.libelle,
p.virtuel,
p.divers,
p.id,
p.reference_fournisseur,
p.prix_vente as prixVente,
p.prix_base as prixAchat,
p.poids,
p.stock,
p.stock_reserve,
p.reference,
p.pump,
p.cpump,
p.arret_gamme,
MATCH (".$match.") AGAINST ('".$recherche_contruit."' IN BOOLEAN MODE) as ponderation ";
$preselectWhere = "divers = 1 and parent_id IS NULL and temporaire is null ";
$sql3 .= "FROM (select * FROM article__article WHERE ".$preselectWhere." and (archive = 0 or archive is null)) AS p";
$sql3 .= $jointureFromInventaire;
$sql3 .= " WHERE (MATCH (".$match.")
AGAINST ('".$recherche_contruit."' IN BOOLEAN MODE)";
$sql3 .= $wherePrix.$whereStock;
$sql3 .= ")";
$sql3 .= $jointureWhereInventaire;
$sql3 .= " GROUP BY p.id
order by ".$order." ponderation desc
LIMIT 0,100
";
}
}
}
} else {
$order .= "p.reference asc";
$sql = "
select
p.reference_appel_1,
p.reference_appel_2,
p.reference_appel_3,
p.reference_appel_4,
p.reference_appel_5,
p.reference_appel_6,
p.reference_appel_7,
p.reference_appel_8,
p.reference_appel_9,
p.reference_appel_10,
p.reference_appel_11,
p.arret_gamme,
p.virtuel,
p.divers,
p.article_remplacement_id,
p.libelle,
p.id,
p.reference_fournisseur,
p.prix_vente as prixVente,
p.prix_base as prixAchat,
p.poids,
p.stock,
p.stock_reserve_fabrication,
p.stock_reserve,
p.pump,
p.cpump,
p.reference"
.$select
//." FROM ".$selectTemp." LEFT JOIN article__article_categorie as aac ON aac.categorie_id = tempAC.id LEFT JOIN article__article as p ON aac.article_id = p.id"
." FROM (select
*
FROM article__condition_achat
WHERE
fournisseur_id = ".$fournisseur." ) AS temp "
." LEFT JOIN article__article as p ON temp.article_id = p.id"
.$jointureFromInventaire
." WHERE ";
if ($jointureWhere != '') {
$sql .= $jointureWhere." and";
}
$sql .= " p.temporaire is null
and
(p.archive = 0 or p.archive is null)
"
//.$whereSuppl
.$whereStock.$jointureWhereInventaire.
"
GROUP BY p.id
order by ".$order."
LIMIT 0,100
";
}
//where p.libelle like '%".$recherche."%' or p.reference like '%".$recherche."%'
// echo $sql;
$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
$resultats1 = $stmt->executeQuery()->fetchAllAssociative();
$resultats = $resultats1;
//var_dump($resultats);
if ($sql3 != "") {
$stmt = $em->getConnection()->prepare($sql3);
$resultats3 = $stmt->executeQuery()->fetchAllAssociative();
//var_dump($resultats2);
$resultats = array_merge($resultats, $resultats3);
//var_dump($resultats);
}
if ($sql2 != "" && count($resultats) == 0) {
$stmt = $em->getConnection()->prepare($sql2);
$resultats2 = $stmt->executeQuery()->fetchAllAssociative();
//var_dump($resultats2);
$resultats = array_merge($resultats, $resultats2);
//var_dump($resultats);
}
//echo $sql;
//exit;
//echo "<div>------(".$mouvemente.")</div>";
//Si la recherche dans les articles mouvementés ne donne rien, on effectue la requete sur les articles non mouvementés
if (count($resultats1) == 0 && count($resultats) == 0 && $mouvemente == "1") {
//$mouvemente =0;
/*if(!$avecEtoile) {
$recherche = str_replace("*","",$recherche);
}
*/
//echo "<div>ototo ".$recherche."</div>";
//echo "--".$mouvemente;
//exit;
$resultats = $this->getRechercheArticleSelect2(
$recherche_tmp,
$produitInterne,
$fournisseur,
$articlesAExclure,
$stock,
$reference_search,
$libelle_search,
$prix_search,
$tri_reference,
$tri_libelle,
$tri_prix,
$tri_stock,
$categories,
"0",
$inventaire
);
//exit;
}
//exit;
return $resultats;
}
public function getRechercheArticleSelect3($recherche, $produitInterne = null, $fournisseur = null, $articlesAExclure = null, $stock = "", $reference_search = "", $libelle_search = "",
$prix_search = "", $tri_reference = "", $tri_libelle = "", $tri_prix = "", $tri_stock = "", $categories = "", $mouvemente = 1, $inventaire = ''
) {
$match = 'p.reference,p.libelle,p.reference_appel_1,p.reference_appel_2,p.reference_appel_3,p.reference_appel_4,p.reference_appel_5,p.reference_appel_6,p.reference_appel_7,p.reference_appel_8,p.reference_appel_9,p.reference_appel_10,p.reference_appel_11';
$select = '';
$jointureFromInventaire = "";
$jointureWhereInventaire = "";
$recherche = str_replace("*", "%", $recherche);
$recherche = str_replace("-", " ", $recherche);
$recherche = str_replace(".", "", $recherche);
$recherche = str_replace("/", "\/", $recherche);
$recherche = str_replace(" ", " ", $recherche);
$recherche = str_replace(" ", " ", $recherche);
$recherche_tmp = $recherche;
$avecEtoile = false;
$pos = strpos($recherche, "*");
if ($pos !== false) {
$avecEtoile = true;
}
$recherche = str_replace("*", "%", $recherche);
$wherePrix = "";
$tampon_recherche = $recherche;
$recherche = trim($recherche);
if ( ! $avecEtoile) {
$recherche = str_replace(" ", "* ", $recherche);
}
if ( ! $avecEtoile) {
$recherche .= "*";
}
$whereSuppl = " and p.parent_id is null";
$whereStock = "";
if ($stock == 1) {
$whereStock .= " and p.stock > 0";
}
$order = "";
if ($tri_reference != "") {
$order = "p.reference ".$tri_reference.", ";
}
if ($tri_prix != "") {
$order = "p.prix_vente ".$tri_prix.", ";
}
if ($tri_stock != "") {
$order = "p.stock ".$tri_stock.", ";
}
if ($tri_libelle != "") {
$order = "p.libelle ".$tri_libelle.", ";
}
if ($order == "") {
$order = "p.reference ASC ";
}
//echo "<div>order : ".$order."</div>";
$jointureFrom = "";
$jointureWhere = "";
/*
if($categories != ''){
$jointureFrom .= ' LEFT JOIN article__article_categorie acat on acat.article_id = p.id JOIN article__categorie as cat on cat.id = acat.categorie_id';
//$jointureWhere .= " and cat.libelle LIKE '".$categorie."%'";
foreach($categories as $key=>$categorie){
if($key == 0 )
$jointureWhere .= " and (";
else
$jointureWhere .= " OR";
$jointureWhere .= " cat.libelle LIKE '".$categorie."'";
}
$jointureWhere .= ")";
}
*
*/
if ($categories != '') {
/*
$jointureFrom .= ' LEFT JOIN article__article_categorie acat on acat.article_id = p.id LEFT JOIN article__categorie as cat on cat.id = acat.categorie_id';
foreach($categories as $categorie){
$jointureWhere .= " OR cat.libelle LIKE '".$categorie."'";
}
*
*/
$selectTemp = "(select id FROM article__categorie cat WHERE ";
foreach ($categories as $key => $categorie) {
if ($key == 0) {
$selectTemp .= " ";
} else {
$selectTemp .= " OR";
}
$selectTemp .= " cat.reference LIKE '".$categorie."'";
}
$selectTemp .= ") as tempAC ";
$jointureFrom .= "LEFT JOIN ".$selectTemp." LEFT JOIN article__article_categorie as aac ON aac.categorie_id = tempAC.id";
}
if ($produitInterne == true || $produitInterne == 'true') {
$whereSuppl .= ' and p.produit_interne =1';
}
if (count($articlesAExclure) > 0) {
$whereSuppl .= " and p.id NOT IN ( '".implode($articlesAExclure, "', '")."' )";// != :articleAExclure and p.id != :articleAExclure
}
/*
if ($mouvemente == "")
$whereSuppl .= " and p.divers = 1";
else
$whereSuppl .= " and (p.mouvemente = ".$mouvemente." or p.divers = 1)";
*
*/
if ($mouvemente != "0" && $mouvemente !== null or ($mouvemente == "")) {
$mouvemente = 1;
$whereSuppl .= " and (p.mouvemente = ".$mouvemente." or p.divers = 1)";
} else {
//echo "titit (".$mouvemente.")";
$whereSuppl .= " and (p.mouvemente = ".$mouvemente." or p.mouvemente =1 or p.mouvemente is null or p.divers = 1)";
}
//echo 'mouvemente : '.$mouvemente;
if ( ! $avecEtoile) {
$recherche = str_replace("-", "", $recherche);
}
if ($inventaire != '') {
//$select .= ', count(ai.id) as nbArticleInventaire';
$select .= ', COUNT(CASE WHEN ai.inventaire_id = '.$inventaire.' then 1 ELSE NULL END) as "nbArticleInventaire"';
$jointureFromInventaire .= ' LEFT JOIN inventaire__article_inventaire ai ON ai.article_id = p.id';
//$jointureWhereInventaire .= ' and (ai.inventaire_id = '.$inventaire.' OR ai.inventaire_id IS NULL)';
}
//echo "<div>S : ".$recherche."</div>";
if ($recherche != "" and $recherche != "*") {
if ($prix_search == 1) {
$sql = "
select
CONCAT(CONCAT(p.reference, ' '), p.libelle) as libelle_construit,
p.arret_gamme,
p.virtuel,
p.article_remplacement_id,
p.libelle,
p.id,
p.prix_vente as prixVente,
p.prix_base as prixAchat,
p.poids,
p.stock,
p.stock_reserve_fabrication,
p.stock_reserve,
p.reference,
p.pump,
p.cpump,
MATCH (".$match.") AGAINST ('".$recherche."' IN BOOLEAN MODE) as ponderation"
.$select
." FROM article__article as p"
.$jointureFromInventaire
." WHERE
p.temporaire is null
and
(p.archive = 0 or p.archive is null)
and
p.prix_vente like '".$tampon_recherche."%'
".$whereStock."
".$whereSuppl.$jointureWhereInventaire."
GROUP BY p.id
order by ".$order." p.id desc
LIMIT 0,100
";
} else {
/*
p.reference LIKE '".$recherche."'
OR
p.libelle LIKE '".$recherche."'
OR
reference_appel_1 LIKE '".$recherche."'
OR
reference_appel_2 LIKE '".$recherche."'
OR
reference_appel_3 LIKE '".$recherche."'
OR
reference_appel_4 LIKE '".$recherche."'
OR
reference_appel_5 LIKE '".$recherche."'
OR
reference_appel_6 LIKE '".$recherche."'
OR
reference_appel_7 LIKE '".$recherche."'
OR
reference_appel_8 LIKE '".$recherche."'
OR
reference_appel_9 LIKE '".$recherche."'
OR
reference_appel_10 LIKE '".$recherche."'
OR
reference_appel_11 LIKE '".$recherche."'
*/
if ($avecEtoile) {
if ($order == "") {
$order = "p.reference";
}
$order = str_replace(",", "", $order);
$recherche .= "%";
$sql = "
select
CONCAT(CONCAT(p.reference, ' '), p.libelle) as libelle_construit,
p.arret_gamme,
p.virtuel,
p.article_remplacement_id,
p.libelle,
p.id,
p.prix_vente as prixVente,
p.prix_base as prixAchat,
p.poids,
p.stock,
p.stock_reserve_fabrication,
p.stock_reserve,
p.pump,
p.cpump,
p.reference"
.$select
." FROM article__article as p ".$jointureFrom.$jointureFromInventaire."
WHERE
p.temporaire is null
and
(p.archive = 0 or p.archive is null) ".$whereSuppl."
and
(
p.reference LIKE '".$recherche."'
OR
p.libelle LIKE '".$recherche."'
OR
p.reference_appel_1 LIKE '".$recherche."'
OR
p.reference_appel_2 LIKE '".$recherche."'
OR
p.reference_appel_3 LIKE '".$recherche."'
OR
p.reference_appel_4 LIKE '".$recherche."'
OR
p.reference_appel_5 LIKE '".$recherche."'
OR
p.reference_appel_6 LIKE '".$recherche."'
OR
p.reference_appel_7 LIKE '".$recherche."'
OR
p.reference_appel_8 LIKE '".$recherche."'
OR
p.reference_appel_9 LIKE '".$recherche."'
OR
p.reference_appel_10 LIKE '".$recherche."'
OR
p.reference_appel_11 LIKE '".$recherche."'";
if ($match2 != "") {
$sql .= "OR ca.reference_fournisseur LIKE '".$recherche."'";
$sql .= "OR ca.reference_fournisseur_appel LIKE '".$recherche."'";
}
$sql .= " )
".$wherePrix.$whereStock.$jointureWhere.$jointureWhereInventaire."
GROUP BY p.id
order by ".$order."
LIMIT 0,100
";
//echo $sql;
//exit;
} else {
//echo "OK";
//exit;
$recherche_contruit = "";
$recherche_tab = explode(" ", $recherche);
for ($r = 0; $r < count($recherche_tab); $r++) {
//$recherche_contruit .="+".$recherche_tab[$r]." ";
$recherche_tab[$r] = str_replace("+", "#+", $recherche_tab[$r]);
$recherche_contruit .= "+".$recherche_tab[$r]." ";
}
//echo "<div>SUPPL ".$whereSuppl."</div>";
$sql = "
select
p.stock_reserve_fabrication,
CONCAT(CONCAT(p.reference, ' '), p.libelle) as libelle_construit,
p.article_remplacement_id,
p.virtuel,
p.libelle,
p.id,
p.prix_vente as prixVente,
p.prix_base as prixAchat,
p.poids,
p.stock,
p.stock_reserve,
p.reference,
p.pump,
p.cpump,
p.arret_gamme,
MATCH (".$match.") AGAINST ('".$recherche_contruit."' IN BOOLEAN MODE) as ponderation";
$sql .= $select;
$sql .= " FROM ".$selectTemp;
$sql .= "LEFT JOIN article__article_categorie as aac ON aac.categorie_id = tempAC.id LEFT JOIN article__article as p ON aac.article_id = p.id ";
$sql .= $jointureFromInventaire
." WHERE ";
if ($jointureWhere != "") {
$sql .= $jointureWhere." and ";
}
$sql .= " p.temporaire is null
and (p.archive = 0 or p.archive is null) ".$whereSuppl."
and
(MATCH (".$match.")
AGAINST ('".$recherche_contruit."' IN BOOLEAN MODE)";
$sql .= "";
$sql .= $wherePrix.$whereStock;
$sql .= ")";
$sql .= $jointureWhereInventaire;
$sql .= " GROUP BY p.id
order by ".$order.", ponderation desc
LIMIT 0,100
";
//echo $sql;
}
}
} else {
//$order .= "p.reference asc";
$sql = "
select
p.arret_gamme,
p.virtuel,
p.article_remplacement_id,
p.libelle,
p.id,
p.prix_vente as prixVente,
p.prix_base as prixAchat,
p.poids,
p.stock,
p.stock_reserve_fabrication,
p.stock_reserve,
p.pump,
p.cpump,
p.reference";
$sql .= $select;
$sql .= " FROM ".$selectTemp;
$sql .= "LEFT JOIN article__article_categorie as aac ON aac.categorie_id = tempAC.id LEFT JOIN article__article as p ON aac.article_id = p.id ";
$sql .= $jointureFromInventaire
." WHERE ";
if ($jointureWhere != "") {
$sql .= $jointureWhere." and ";
}
$sql .= " p.temporaire is null
and
(p.archive = 0 or p.archive is null)
".$whereSuppl.$whereStock.$jointureWhereInventaire."
GROUP BY p.id
order by ".$order."
LIMIT 0,100
";
}
//where p.libelle like '%".$recherche."%' or p.reference like '%".$recherche."%'
//echo $sql;
$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
$resultats = $stmt->executeQuery()->fetchAllAssociative();
//$resultats = array();
//echo $sql;
//exit;
//echo "<div>------(".$mouvemente.")</div>";
//Si la recherche dans les articles mouvementés ne donne rien, on effectue la requete sur les articles non mouvementés
if (count($resultats) == 0 && $mouvemente == "1") {
//$mouvemente =0;
/*if(!$avecEtoile) {
$recherche = str_replace("*","",$recherche);
}
*/
//echo "<div>ototo ".$recherche."</div>";
//echo "--".$mouvemente;
//exit;
$resultats = $this->getRechercheArticleSelect3(
$recherche_tmp,
$produitInterne,
$fournisseur,
$articlesAExclure,
$stock,
$reference_search,
$libelle_search,
$prix_search,
$tri_reference,
$tri_libelle,
$tri_prix,
$tri_stock,
$categories,
"0",
$inventaire
);
//exit;
}
//exit;
return $resultats;
}
public function findLibelleOrRefLike($query, $articleId)
{
$tampon_recherche = $query;
$recherche = trim($query);
$recherche = str_replace(" ", "* ", $recherche);
$recherche .= "*";
$recherche = str_replace("-", "", $recherche);
$jointureFrom = "";
$whereSuppl = "";
$wherePrix = "";
$whereStock = "";
$jointureWhere = "";
$order = "";
$sql = "
select
CONCAT(CONCAT(p.reference, ' '), p.libelle) as libelle,
p.id,
p.virtuel,
p.prix_vente as prixVente,
p.prix_base as prixAchat,
p.poids,
p.stock,
p.reference,
MATCH (p.reference,p.libelle,p.reference_appel_1,p.reference_appel_2,p.reference_appel_3,p.reference_appel_4,p.reference_appel_5,p.reference_appel_6,p.reference_appel_7,p.reference_appel_8,p.reference_appel_9,p.reference_appel_10,p.reference_appel_11) AGAINST ('".$recherche."' IN BOOLEAN MODE) as ponderation
FROM article__article as p ".$jointureFrom."
WHERE
p.temporaire is null
and
p.consigne = 0
and (p.archive = 0 or p.archive is null) ".$whereSuppl."
and
(MATCH (
p.reference,
p.libelle,
reference_appel_1,
reference_appel_2,
reference_appel_3,
reference_appel_4,
reference_appel_5,
reference_appel_6,
reference_appel_7,
reference_appel_8,
reference_appel_9,
reference_appel_10,
reference_appel_11
)
AGAINST ('".$recherche."' IN BOOLEAN MODE)
".$wherePrix.$whereStock.$jointureWhere."
)
order by ".$order." ponderation desc
LIMIT 0,100
";
/*
return $this->getEntityManager()
->createQuery(
"select r.id, CONCAT(r.libelle, '[', COALESCE(r.reference, ''), ']') as libelle FROM App\Entity\Articles\Article r WHERE r.archive = 0 and r.reference LIKE '%".$query."%' or r.libelle LIKE '%".$query."%' and r.id NOT IN (:id) "
)
->setParameters(array('id' => $articleId))
->getResult();
*/
$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
return $stmt->executeQuery()->fetchAllAssociative();
}
public function agetRechercheArticleSelect($recherche, $produitInterne = null, $fournisseur = null, $articlesAExclure = null, $stock = "", $reference_search = "", $libelle_search = "",
$prix_search = "", $tri_reference = "", $tri_prix = "", $tri_stock = ""
) {
$where = "";
//$cond = "%".$recherche . "%";
$cond = $recherche."%";
//$parameters = array('field' => $recherche,'archive'=>'0','cond' => $cond);
$parameters = ['field' => $recherche, 'archive' => '0'];
//if($recherche != "") $parameters = array('field' => $recherche,'archive'=>'0','cond' => $cond);
if ($recherche != "") {
$parameters = ['field' => $recherche, 'archive' => '0'];
}
$qb = $this->getEntityManager()->createQueryBuilder();
$qb
->select(
"p.libelle,p.id,p.prixVente,p.poids,p.stock,p.reference,(MATCH (p.reference,p.libelle,p.referenceAppel1,p.referenceAppel2,p.referenceAppel3,p.referenceAppel4,p.referenceAppel5,p.referenceAppel6,p.referenceAppel7,p.referenceAppel8,p.referenceAppel9,p.referenceAppel10) AGAINST (:field) as ponderation"
)
->from(Article::class, 'p');
//if($recherche != "") $where = '(MATCH (p.reference,p.libelle) AGAINST (:field) > 0 or p.prixVente like :cond) and ';
if ($recherche != "") {
$wherePrix = "";
if ($prix_search == 1) {
$parameters = ['field' => $recherche, 'archive' => '0', 'cond' => $cond];
$wherePrix = ' or p.prixVente like :cond ';
}
$where = '(MATCH (
p.reference,
p.libelle,
p.referenceAppel1,
p.referenceAppel2,
p.referenceAppel3,
p.referenceAppel4,
p.referenceAppel5,
p.referenceAppel6,
p.referenceAppel7,
p.referenceAppel8,
p.referenceAppel9,
p.referenceAppel10
) AGAINST (:field) > 0)'.$wherePrix.'and ';
}
$where .= 'p.temporaire is null and p.archive = :archive ';
if ($fournisseur == true) {
//ajout des tables conditions d'achat et fournisseur
$select .= ', f.id as fournisseur';
$query->join('p.conditionsAchat', 'ca');
$query->innerJoin('ca.fournisseur', 'f', 'WITH', 'ca.fournisseur = f.id');
$where .= ' and f.id = :fournisseur';
$parameters['fournisseur'] = $fournisseur;
}
if ($produitInterne == true) {
$where .= ' and p.produitInterne =1';
}
if (count($articlesAExclure) > 0) {
$where .= " and p.id NOT IN ( '".implode($articlesAExclure, "', '")."' )";// != :articleAExclure and p.id != :articleAExclure
}
if ($stock == 1) {
$where .= " and p.stock > 0";
}
if ($where != "") {
$qb->where($where)
->setParameters($parameters)
->setFirstResult(0)
->setMaxResults(1000)
->groupBy('p.id')//->orderBy('ponderation', 'desc')
;
}
if ($prix_search == 1) {
} else {
$qb->addOrderBy('ponderation', 'desc');
}
if ($tri_reference != "") {
}
if ($tri_prix != "") {
$qb->addOrderBy('p.prixVente', $tri_prix);
}
if ($tri_stock != "") {
$qb->addOrderBy('p.stock', $tri_stock);
}
$res = $qb->getQuery()->getResult();
//\Doctrine\Common\Util\Debug::dump($res);
return $res;
}
public function ZgetRechercheArticleSelect($recherche, $produitInterne = null, $fournisseur = null, $articlesAExclure = null, $stock = "")
{
//echo "V";
$cond = "%".$recherche."%";
$query = $this->createQueryBuilder('p')
//->select("CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(p.reference, ' '), p.libelle),' (Stock : '),p.stock),') - '),p.prixVente),' €') as libelle, p.id,p.prixBase,p.poids,p.stock,p.reference");
->select("p.libelle, p.id,p.prixVente,p.poids,p.stock,p.reference");
$select = '';
//$where = "MATCH (p.reference, p.libelle) AGAINST (:cond)";
//$where = "MATCH_AGAINST(p.reference, p.libelle) > 0";
$where = '(p.libelle LIKE :cond OR p.reference LIKE :cond OR p.prixVente LIKE :cond) and p.temporaire is null and p.archive = :archive';
$parameters = ['cond' => $cond, 'archive' => '0'];
if ($fournisseur == true) {
//ajout des tables conditions d'achat et fournisseur
$select .= ', f.id as fournisseur';
$query->join('p.conditionsAchat', 'ca');
$query->innerJoin('ca.fournisseur', 'f', 'WITH', 'ca.fournisseur = f.id');
$where .= ' and f.id = :fournisseur';
$parameters['fournisseur'] = $fournisseur;
}
if ($produitInterne == true) {
$where .= ' and p.produitInterne =1';
}
if (count($articlesAExclure) > 0) {
$where .= " and p.id NOT IN ( '".implode($articlesAExclure, "', '")."' )";// != :articleAExclure and p.id != :articleAExclure
}
if ($stock == 1) {
$where .= " and p.stock > 0";
}
//echo "AA ".$where;
$query->where($where)
->setParameters($parameters)//array('cond' => $cond)
//->orderBy('p.prenom', 'ASC');
->setFirstResult(0)
->setMaxResults(1000)
->orderBy('p.reference', 'ASC');
$res = $query->getQuery()->getResult();
return $res;
}
public function getRechercheClient($recherche)
{
$cond = $recherche."%";
$query = $this->createQueryBuilder('p')
->select("CONCAT(CONCAT(p.prenom, ' '), p.nom) as libelle, p.id,p.telephone")
->where('p.prenom LIKE :cond or p.nom like :cond')
->setParameters(['cond' => $cond])
->orderBy('p.prenom', 'ASC');
$res = $query->getQuery()->getResult();
return $res;
}
public function findByRefOrName($query)
{
return $this->getEntityManager()
->createQuery(
"select a.id, a.reference, a.libelle, a.prixBase
FROM App\Entity\Articles\Article a
WHERE p.temporaire is null and p.archive = 0 and (a.reference LIKE '%".$query."%' OR a.libelle LIKE '%".$query."%' OR a.referenceFournisseur LIKE '%".$query."%)'
ORDER BY a.libelle ASC"
)
->getResult();
}
public function genererCpumpArticle($id, $date = '', $quantiteTotal = 0)
{
//$article = $this->getEntityManager()->getRepository(Article::class)->find($id);
mail("thomas@noogaa.com", "BB", "B");
$builder = new ProcessBuilder();
$builder->setArguments(['php', '../app/console', 'genererCpumpArticle', $id]);
$builder->getProcess()->start();
mail("thomas@noogaa.com", "CC", "C");
}
public function getPumpC($id, $date = '', $quantiteTotal = 0)
{
return 0;
$pump = "0";
$repo_condha = $this->getEntityManager()->getRepository(ConditionAchat::class);
$article = $this->getEntityManager()->getRepository(Article::class)->find($id);
if (is_object($article) && $article->getStock() > 0 && $date != '') {
$nbResultats = floatval($article->getStock());
} else {
$nbResultats = 30;
}
$where = "WHERE a.id = :id and m.prixAchat is not null and r.action = 'augmenter' and m.quantite > 0 and c.id IS NULL and r.id NOT IN (19)";//and m.fournisseur is not null
$where = "WHERE a.id = :id and m.idImport is NULL";
if ($date != '') {
$where .= " and m.dateMvt <= '".$date->format("Y-m-d H:i:s")."'";
}
$nbResultats = 2;
$stocks = $this->getEntityManager()
->createQuery(
"select m.id,a.stock, m.quantite, m.prixAchat,m.cpump,r.action,m.stock as stockMvt,r.id as raison_id
FROM App\Entity\Articles\Article a
LEFT JOIN App\Entity\Articles\MouvementStock m with m.article = a.id
LEFT JOIN App\Entity\GestionComerciale\Commande c with m.commande = c.id
LEFT JOIN App\Entity\Articles\RaisonMouvementStock r with m.raisonMouvementStock = r.id
".$where."
ORDER BY m.dateMvt DESC,m.id DESC"
)
->setParameters(['id' => $id])
->setMaxResults($nbResultats)
->getResult();
if (count($stocks) == "2") {
//echo "<div>OKOKO</div>";
$dernierMouvement = $stocks[0];
$mouvement = $stocks[1];
if ($dernierMouvement["action"] == "augmenter" and ($dernierMouvement["raison_id"] == "3" or $dernierMouvement["raison_id"] == "15")) {
if ($dernierMouvement["stockMvt"] == 0) {
return 0;
} else {
if ($mouvement["cpump"] > 0) {
//echo "AZERTY";
$pump = (($mouvement["stockMvt"] * $mouvement["cpump"]) + ($dernierMouvement["quantite"] * $dernierMouvement["prixAchat"])) / $dernierMouvement["stockMvt"];
//return $pump;
} else {
//echo "QWERTY";
//print_r($dernierMouvement);
$pump = ($dernierMouvement["quantite"] * $dernierMouvement["prixAchat"]) / $dernierMouvement["quantite"];
//return $pump;
}
}
} else {
$pump = $mouvement["cpump"];
}
} elseif (count($stocks) == "1") {
$dernierMouvement = $stocks[0];
if ($dernierMouvement["action"] == "augmenter" and ($dernierMouvement["raison_id"] == "3" or $dernierMouvement["raison_id"] == "15")) {
if ($dernierMouvement["stockMvt"] == 0) {
return 0;
} else {
$pump = ($dernierMouvement["quantite"] * $dernierMouvement["prixAchat"]) / $dernierMouvement["stockMvt"];
}
} //else if($dernierMouvement["action"] == "diminuer") {
else {
$pump = 0;
}
} else {
$pump = 0;
}
if ($pump == "0" or $pump == "") {
$condHa = $repo_condha->findOneBy(["article" => $article->getId(), "defaut" => 1]);
if (is_object($condHa)) {
//echo "<div>CONDHA OK</div>";
$prix = $condHa->getPrixAchatNet();
if ($condHa->getCoefficientConditionnement() != 0) {
$prix = $prix / $condHa->getCoefficientConditionnement();
}
$tauxChange = 1;
$fournisseur = $condHa->getFournisseur();
if (is_object($fournisseur)) {
$devise = $fournisseur->getDevise();
}
if (is_object($devise) && $devise->getTauxChange() != 0) {
$tauxChange = $devise->getTauxChange();
}
$prix = $prix * $tauxChange;
$pump = $prix;
} else {
$condHa = $repo_condha->findOneBy(["article" => $article->getId()]);
if (is_object($condHa)) {
//echo "<div>CONDHA OK</div>";
$prix = $condHa->getPrixAchatNet();
if ($condHa->getCoefficientConditionnement() != 0) {
$prix = $prix / $condHa->getCoefficientConditionnement();
}
$tauxChange = 1;
$fournisseur = $condHa->getFournisseur();
if (is_object($fournisseur)) {
$devise = $fournisseur->getDevise();
}
if (is_object($devise) && $devise->getTauxChange() != 0) {
$tauxChange = $devise->getTauxChange();
}
$prix = $prix * $tauxChange;
$pump = $prix;
} else {
$pump = 0;
}
}
}
if ($pump == "") {
return 0;
} else {
return $pump;
}
$pump = 0;
$totalTmp = 0;
$totalPrixTmp = 0;
//echo $quantiteTotal;
if ($quantiteTotal <= 0) {
if (is_object($article)) {
$quantiteTotal = $article->getStock();
}
}
if (count($stocks) > 0 && $quantiteTotal > 0) {
//$pump = $stocks[0]->getPrixAchat();
//$quantiteTotal = $stocks[0]['stock'];
$quantiteTmp = $quantiteTotal;
foreach ($stocks as $stock)//reverse
{
$quantite = $stock['quantite'];
$prix = $stock['prixAchat'];
//var_dump($quantite.' -> '.$prix);
/*
if (($quantiteTmp-$quantite) >= 0){
$pump += $quantite * $prix;
$totalTmp += $quantite;
}else{
$pump += $quantiteTmp * $prix;
$totalTmp += $quantiteTmp;
}
$quantiteTmp -= $quantite;
if($quantiteTmp <= 0) break;
*/
$totalPrixTmp += $quantite * $prix;
$totalTmp += $quantite;
}
if ($totalTmp > 0) {
$pump = $totalPrixTmp / $totalTmp;
}
}
//echo "PUMP ".$pump;
//exit;
return $pump;
}
public function getPumpR($id, $date = '', $quantiteTotal = 0, $output = '')
{
$article = $this->getEntityManager()->getRepository(Article::class)->find($id);
/*
if(is_object($article) && $article->getStock() > 0 && $date != '')
$nbResultats = floatval($article->getStock());
else
*/
$nbResultats = 100;
$where = "WHERE a.id = :id and m.prixAchat is not null and r.action = 'augmenter' and m.quantite > 0 and c.id IS NULL and r.id NOT IN (19)";//and m.fournisseur is not null
if ($date != '') {
$where .= " and m.dateMvt <= '".$date->format("Y-m-d H:i:s")."'";
}
$stocks = $this->getEntityManager()
->createQuery(
"select a.stock, m.quantite, m.prixAchatUnitaireAvecFrais as prixAchat, m.prixAchat as prixAchat2
FROM App\Entity\Articles\Article a
LEFT JOIN App\Entity\Articles\MouvementStock m with m.article = a.id
LEFT JOIN App\Entity\GestionComerciale\Commande c with m.commande = c.id
LEFT JOIN App\Entity\Articles\RaisonMouvementStock r with m.raisonMouvementStock = r.id
".$where."
ORDER BY m.date DESC"
)
->setParameters(['id' => $id])
->setMaxResults($nbResultats)
->getResult();
$pump = 0;
$totalTmp = 0;
//echo $quantiteTotal;
if ($quantiteTotal <= 0) {
if (is_object($article)) {
$quantiteTotal = $article->getStock();
}
}
if (count($stocks) > 0 && $quantiteTotal > 0) {
//$pump = $stocks[0]->getPrixAchat();
//$quantiteTotal = $stocks[0]['stock'];
$quantiteTmp = $quantiteTotal;
foreach ($stocks as $stock)//reverse
{
$quantite = $stock['quantite'];
$prix = $stock['prixAchat'];
if (is_null($prix)) {
$prix = $stock['prixAchat2'];
}
if (is_object($output)) {
$output->writeln('qte : '.$quantite.' / prix achat : '.$prix);
}
//var_dump($quantite.' -> '.$prix);
/*
if (($quantiteTmp-$quantite) >= 0){
$pump += $quantite * $prix;
$totalTmp += $quantite;
}else{
$pump += $quantiteTmp * $prix;
$totalTmp += $quantiteTmp;
}
*/
if ($quantite >= $quantiteTmp) {
$pump += $quantiteTmp * $prix;
$totalTmp += $quantiteTmp;
} else {
$pump += $quantite * $prix;
$totalTmp += $quantite;
}
$quantiteTmp -= $quantite;
if ($quantiteTmp <= 0) {
break;
}
}
if ($totalTmp > 0) {
$pump = $pump / $totalTmp;
}
}
return $pump;
}
public function getPumpHorsFrais($id, $date = '', $quantiteTotal = 0)
{
$article = $this->getEntityManager()->getRepository(Article::class)->find($id);
if (is_object($article) && $article->getStock() > 0 && $date != '') {
$nbResultats = floatval($article->getStock());
} else {
$nbResultats = 30;
}
$where = "WHERE a.id = :id and m.prixAchat is not null and r.action = 'augmenter' and m.quantite > 0 and c.id IS NULL and r.id NOT IN (19)";//and m.fournisseur is not null
if ($date != '') {
$where .= " and m.dateMvt <= '".$date->format("Y-m-d H:i:s")."'";
}
$stocks = $this->getEntityManager()
->createQuery(
"select a.stock, m.quantite, m.prixAchat
FROM App\Entity\Articles\Article a
LEFT JOIN App\Entity\Articles\MouvementStock m with m.article = a.id
LEFT JOIN App\Entity\GestionComerciale\Commande c with m.commande = c.id
LEFT JOIN App\Entity\Articles\RaisonMouvementStock r with m.raisonMouvementStock = r.id
".$where."
ORDER BY m.date DESC"
)
->setParameters(['id' => $id])
->setMaxResults($nbResultats)
->getResult();
$pump = 0;
$totalTmp = 0;
//echo $quantiteTotal;
if ($quantiteTotal <= 0) {
if (is_object($article)) {
$quantiteTotal = $article->getStock();
}
}
if (count($stocks) > 0 && $quantiteTotal > 0) {
//$pump = $stocks[0]->getPrixAchat();
//$quantiteTotal = $stocks[0]['stock'];
$quantiteTmp = $quantiteTotal;
foreach ($stocks as $stock)//reverse
{
$quantite = $stock['quantite'];
$prix = $stock['prixAchat'];
//var_dump($quantite.' -> '.$prix);
/*
if (($quantiteTmp-$quantite) >= 0){
$pump += $quantite * $prix;
$totalTmp += $quantite;
}else{
$pump += $quantiteTmp * $prix;
$totalTmp += $quantiteTmp;
}
*/
if ($quantite >= $quantiteTmp) {
$pump += $quantiteTmp * $prix;
$totalTmp += $quantiteTmp;
} else {
$pump += $quantite * $prix;
$totalTmp += $quantite;
}
$quantiteTmp -= $quantite;
if ($quantiteTmp <= 0) {
break;
}
}
if ($totalTmp > 0) {
$pump = $pump / $totalTmp;
}
}
return $pump;
}
public function gethistoriqueVente($articleId)//ajouter filtre statut commande
{
$histo = $this->getEntityManager()
->createQuery(
"select c.reference, c.date, ac.quantite
FROM App\Entity\GestionComerciale\Commande c
JOIN App\Entity\GestionComerciale\ArticleCommande ac with ac.commande = c.id"
." WHERE ac.article = :id and c.typeDocumentCommercial = ".TypeDocumentCommercial::COMMANDE."
ORDER BY c.date DESC"
)
->setParameters(['id' => $articleId])
->setMaxResults(5)
->getResult();
return $histo;
}
public function getNbArticlesVendus($articleId, $periode)//ajouter filtre statut commande
{
$date = new DateTime();
$date->sub(new DateInterval('P'.$periode.'D'));
$nbArticlesVendus = $this->getEntityManager()
->createQuery(
"select SUM(ac.quantite) as quantite
FROM App\Entity\GestionComerciale\Commande c
JOIN App\Entity\GestionComerciale\ArticleCommande ac with ac.commande = c.id
WHERE ac.article = :id and c.date > :date and c.typeDocumentCommercial = ".TypeDocumentCommercial::COMMANDE."
ORDER BY c.date DESC"
)
->setParameters(['id' => $articleId, 'date' => $date])
->getResult();
return $nbArticlesVendus;
}
public function getNbArticlesVendusMois($articleId, $m, $y)//ajouter filtre statut commande
{
$dateDebut = new DateTime($y.'-'.$m.'-01 00:00:00');
$date = new DateTime($y.'-'.$m.'-01 00:00:00');
//$date = new \DateTime('01/'.$m.'/'.$y);
//$date = new \DateTime($m.'/01/'.$y);
$periode = $this->nbJoursMois($m, $y);
$dateFin = $date->add(new DateInterval('P'.$periode.'D'));
//$dateFin = $date->sub(new \DateInterval('P'.$periode.'D'));
//return $dateDebut->format('d-m-Y').'/'.$dateFin->format('d-m-Y');
$nbArticlesVendus = $this->getEntityManager()
->createQuery(
"select SUM(ac.quantite) as quantite
FROM App\Entity\GestionComerciale\Commande c
JOIN App\Entity\GestionComerciale\ArticleCommande ac with ac.commande = c.id
WHERE ac.article = :id and c.date >= :dateDebut and c.date < :dateFin and c.typeDocumentCommercial = ".TypeDocumentCommercial::COMMANDE."
and c.statutCommande != :statutCommande
"
)
->setParameters(['id' => $articleId, 'dateDebut' => $dateDebut, 'dateFin' => $dateFin, 'statutCommande' => '7'])//
->getResult();
return $nbArticlesVendus;
}
public function getArticlesAcommander()
{
$query = $this->createQueryBuilder('p')
->select("p.libelle, p.id, p.stock, p.seuilMiniCommandeFournisseur, p.seuilMaxiCommandeFournisseur")
//->innerJoin('p.fournisseur', 'f', 'WITH', 'p.fournisseur = f.id')
//->join("App\Entity\Fournisseurs\Four f")
->where('p.stock < p.seuilMiniCommandeFournisseur and p.archive = 0')
//->setParameters(array('cond' => $cond))
//->orderBy('p.prenom', 'ASC');
//->setFirstResult(0)
//->setMaxResults(5)
//->orderBy('p.id', 'DESC')
;
$res = $query->getQuery()->getResult();
return $res;
}
public function getArticlesAcommanderGroupByFournisseur()
{
$query = $this->createQueryBuilder('p')
->select(
"COALESCE(SUM(DISTINCT(ac.ral)),0) as ralTotal, (COALESCE(SUM(DISTINCT(ac.ral)),0) + p.stock) as stockVirtuel, p.libelle, p.id, p.stock, p.seuilMiniCommandeFournisseur, p.seuilMaxiCommandeFournisseur, f.id as fournisseurId, f.libelle as fournisseur"
)
//->leftJoin('p.fournisseurs', 'f')//, 'WITH', 'p.fournisseur = f.id'
->leftJoin('p.mouvementStock', 'ms')
->join('ms.fournisseur', 'f')
->leftJoin(ArticleCommande::class, 'ac', 'with', 'ac.article = p.id')
->leftJoin('ac.commandeFournisseur', 'c', 'with', 'c.statutCommande IN (1,2)')
//->join("App\Entity\Fournisseurs\Four f")
->where('p.archive = :archive')
->setParameters(['archive' => "0"])
->groupBy('f.id, p.id')
->having('stockVirtuel < p.seuilMiniCommandeFournisseur')//p.stock + ral fournisseur < p.seuilMiniCommandeFournisseur
;
$res = $query->getQuery()->getResult();
return $res;
}
public function getNbArticlesVendusParMois($articleId, $nbMois)//ajouter filtre statut commande
{
$date = new DateTime();
$mois = (int)$date->format('m');
$annee = (int)$date->format('Y');
//$date->sub(new \DateInterval('P'.$periode.'D'));
$moisTmp = $mois;
for ($i = 1; $i <= $nbMois; $i++) {
if ($moisTmp < 10) {
$id = $annee.'-0'.$moisTmp;
} else {
$id = $annee.'-'.$moisTmp;
}
$nbArticlesVendusMois = $this->getNbArticlesVendusMois($articleId, $moisTmp, $annee);
if ($nbArticlesVendusMois[0]['quantite'] != '') {
$retour[$id] = $nbArticlesVendusMois[0]['quantite'];
} else {
$retour[$id] = 0;
}
//$retour[$nbArticlesVendusMois] = 10;
if ($moisTmp - 1 > 0) {
$moisTmp -= 1;
} else {
$moisTmp = 12;
$annee -= 1;
}
}
return array_reverse($retour);
}
public function getQuantitePrixMoyenVendusMois($articleId, $m, $y)//ajouter filtre statut commande
{
$dateDebut = new DateTime($y.'-'.$m.'-01 00:00:00');
$dateFin = new DateTime($y.'-'.$m.'-01 00:00:00');
//$date = new \DateTime('01/'.$m.'/'.$y);
//$date = new \DateTime($m.'/01/'.$y);
$periode = $this->nbJoursMois($m, $y);
$dateFin->add(new DateInterval('P'.$periode.'D'));
//$dateFin = $date->sub(new \DateInterval('P'.$periode.'D'));
//return $dateDebut->format('d-m-Y').'/'.$dateFin->format('d-m-Y');
/*
* $nbArticlesVendus = $this->getEntityManager()
->createQuery(
"select SUM(ac.quantite) as quantite, SUM(ac.totalHt) as totalHt
FROM App\Entity\GestionComerciale\Commande c
JOIN App\Entity\GestionComerciale\ArticleCommande ac with ac.commande = c.id
WHERE ac.article = :id and c.date >= :dateDebut and c.date < :dateFin and c.typeDocumentCommercial = ".TypeDocumentCommercial::COMMANDE."
"
)
*/
$nbArticlesVendus = $this->getEntityManager()
->createQuery(
"select SUM(ac.quantite) as quantite, SUM(ac.totalHt) as totalHt
FROM App\Entity\GestionComerciale\ArticleCommande ac
JOIN App\Entity\GestionComerciale\Commande c with ac.commande = c.id
WHERE ac.article = :id and c.date >= :dateDebut and c.date < :dateFin and c.statutCommande = 22
"
)
->setParameters(['id' => $articleId, 'dateDebut' => $dateDebut, 'dateFin' => $dateFin])//
->getResult();
return $nbArticlesVendus;
}
public function getPrixMoyenAchatMois($articleId, $m, $y)
{
$dateDebut = new DateTime($y.'-'.$m.'-01 00:00:00');
$date = new DateTime($y.'-'.$m.'-01 00:00:00');
//$date = new \DateTime('01/'.$m.'/'.$y);
//$date = new \DateTime($m.'/01/'.$y);
$periode = $this->nbJoursMois($m, $y);
$dateFin = $date->add(new DateInterval('P'.$periode.'D'));
//$dateFin = $date->sub(new \DateInterval('P'.$periode.'D'));
//return $dateDebut->format('d-m-Y').'/'.$dateFin->format('d-m-Y');
$prixMoyenAchatResult = $this->getEntityManager()
->createQuery(
"select COUNT(ms.id) as total, SUM(ms.prixAchat) as prix
FROM App\Entity\Articles\MouvementStock ms
WHERE ms.article = :id and ms.dateMvt >= :dateDebut and ms.dateMvt < :dateFin and ms.raisonMouvementStock = 3
"
)
->setParameters(['id' => $articleId, 'dateDebut' => $dateDebut, 'dateFin' => $dateFin])//
->getResult();
$prixMoyenAchat = 0;
if ($prixMoyenAchatResult[0]['total'] != 0) {
$prixMoyenAchat = $prixMoyenAchatResult[0]['prix'] / $prixMoyenAchatResult[0]['total'];
}
return $prixMoyenAchat;
}
public function getPrixAchatVenteParMois($articleId, $nbMois)//ajouter filtre statut commande
{
$date = new DateTime();
$mois = (int)$date->format('m');
$annee = (int)$date->format('Y');
//$date->sub(new \DateInterval('P'.$periode.'D'));
$moisTmp = $mois;
for ($i = 1; $i <= $nbMois; $i++) {
if ($moisTmp < 10) {
$id = $annee.'-0'.$moisTmp;
} else {
$id = $annee.'-'.$moisTmp;
}
$quantitePrixVenteMois = $this->getQuantitePrixMoyenVendusMois($articleId, $moisTmp, $annee);
if ($quantitePrixVenteMois[0]['totalHt'] != '' && $quantitePrixVenteMois[0]['quantite'] != '' && $quantitePrixVenteMois[0]['quantite'] != 0) {
$retour[$id]['vente']['prix'] = $quantitePrixVenteMois[0]['totalHt'] / $quantitePrixVenteMois[0]['quantite'];
} else {
$retour[$id]['vente']['prix'] = 0;
}
$retour[$id]['achat']['prix'] = $this->getPrixMoyenAchatMois($articleId, $moisTmp, $annee);
//$retour[$nbArticlesVendusMois] = 10;
if ($moisTmp - 1 > 0) {
$moisTmp -= 1;
} else {
$moisTmp = 12;
$annee -= 1;
}
}
return array_reverse($retour);
}
private function nbJoursMois($m, $y)
{
$mois = mktime(0, 0, 0, $m, 1, $y);
//setlocale('LC_ALL', 'fr_FR');
return date("t", $mois);
}
public function findLibelleOrRefLikeInConsigne($query, $articleId)
{
return $this->getEntityManager()
->createQuery(
"select r.id, CONCAT(r.libelle, '[', COALESCE(r.reference, ''), ']') as libelle FROM App\Entity\Articles\Article r WHERE r.archive = 0 and r.reference LIKE '%".$query."%' and r.consigne = 1"
)
->getResult();
}
public function getArticlesInternesAFabriquer()
{
$query = $this->createQueryBuilder('p')
->select("p.id, p.reference, p.description, (p.stock-COALESCE(p.stockReserve,0)+COALESCE(p.ralFournisseur,0)) as quantiteAFabriquer")
->leftJoin('p.articlesComposants', 'ac')
->where(
'(p.stock-COALESCE(p.stockReserve,0)+COALESCE(p.ralFournisseur,0)) < p.seuilMiniCommandeFournisseur and (p.produitInterne = 1 OR p.virtuel = 1) and ac.id is not null and p.archive = 0'
);
$res = $query->getQuery()->getResult();
return $res;
}
public function getArticlesAfabriquerSeuilMini()
{
$query = $this->createQueryBuilder('p')
//->select("p.id, p.reference, p.description, (p.stock-COALESCE(p.stockReserveFabrication,0)+COALESCE(p.ralFournisseur,0)) as quantiteAFabriquer")
//->leftJoin('p.articlesComposants', 'ac')
->where(
'(p.stock-COALESCE(p.stockReserveFabrication,0)+COALESCE(p.ralFournisseur,0)) < p.seuilMiniCommandeFournisseur and (p.produitInterne = 1 OR p.virtuel = 1) and (p.archive = 0 OR p.archive IS NULL)'
);
$res = $query->getQuery()->getResult();
return $res;
}
public function getQuantiteAFabriquer($article)
{
$query = $this->createQueryBuilder('p')
->select("(p.stock-COALESCE(p.stockReserve,0)+COALESCE(p.ralFournisseur,0)) as quantiteAFabriquer")
->where('p.id = :id')
->setParameters(['id' => $article->getId()]);
$res = $query->getQuery()->getResult();
if ($article->getVirtuel() == 1) //Pas de stock
{
$seuilMini = 0;
} else {
$seuilMini = $article->getSeuilMiniCommandeFournisseur();
}
$quantiteMini = $seuilMini - $res[0]['quantiteAFabriquer'];
$repo_condha = $this->getEntityManager()->getRepository(ConditionAchat::class);
$condHa = $repo_condha->findOneBy(["defaut" => "1", "fournisseur" => 132, "article" => $article]);
if (is_object($condHa)) {
$qteCondHa = $condHa->getQuantite();
if ($qteCondHa > $quantiteMini) {
$quantiteMini = $qteCondHa;
}
}
return $quantiteMini;
}
public function getClientsEnRal(Article $article)
{
$typeDocument = [TypeDocumentCommercial::COMMANDE, TypeDocumentCommercial::ORDREREPARATION];
$query = $this->createQueryBuilder('p')
->select("DISTINCT(cl.id) as id")
->leftJoin(ArticleCommande::class, 'ac', 'with', 'ac.article = p.id')
->join('ac.commande', 'c')
//->leftJoin(TypeDocumentCommercial::class, 'td', 'with', 'td.id = c.typeDocumentCommercial')
->leftJoin(StatutCommande::class, 'sc', 'with', 'sc.id = c.statutCommande')
->join('c.client', 'cl')
->where('ac.ralClient > 0 and p.id = :id and c.typeDocumentCommercial IN (:typeDocument) and sc.ordre != 0')
->setParameters(['id' => $article->getId(), 'typeDocument' => $typeDocument]);
$res = $query->getQuery()->getResult();
return $res;
}
public function getBpPourCommandeFournisseur($articles, $commentaire = 1209715)
{
$query = $this->createQueryBuilder('p')
->select("DISTINCT(c.id) as id, c.reference")
->leftJoin(ArticleCommande::class, 'ac', 'with', 'ac.article = p.id')
->join('ac.commande', 'c')
->leftJoin(TypeDocumentCommercial::class, 'td', 'with', 'td.id = c.typeDocumentCommercial')
//->join('c.client', 'cl')
->where('ac.ralClient > 0 and p.id IN (:ids) and td.id IN (:typeDocument) and c.visible=1 and p.id != :com ')
->setParameters(['ids' => $articles, 'typeDocument' => [4], 'com' => $commentaire]);
$res = $query->getQuery()->getResult();
//print_r($res);
return $res;
//return array(0=>array('id'=>1));
}
public function getCommandes($articles)
{
$query = $this->createQueryBuilder('p')
->select("DISTINCT(c.id) as id, c.reference")
->leftJoin(ArticleCommande::class, 'ac', 'with', 'ac.article = p.id')
->join('ac.commande', 'c')
//->leftJoin(TypeDocumentCommercial::class, 'td', 'with', 'td.id = c.typeDocumentCommercial')
->join('c.client', 'cl')
->where('ac.ralClient > 0 and p.id IN (:ids) and c.typeDocumentCommercial IN (:typeDocument)')
->setParameters(['ids' => $articles, 'typeDocument' => [2, 3]]);
$res = $query->getQuery()->getResult();
return $res;
//return array(0=>array('id'=>1));
}
public function updateACommander()//ajouter filtre statut commande
{
$query = $this->createQueryBuilder('p')
->update(Article::class, 'p')
->set('p.aCommander', '0')
->where('p.aCommander = 1 ')
->getQuery()
->execute();
$query = $this->createQueryBuilder('p')
->update(Article::class, 'p')
->set('p.aCommander', '1')
->join('p.conditionsAchat', 'cond')
//->where('(p.stock-COALESCE(p.stockReserve,0)+COALESCE(p.ralFournisseur,0)) < COALESCE(p.seuilMiniCommandeFournisseur,0) and p.produitInterne != 1 and p.virtuel != 1 and p.consigne != 1')
->where(
'
(p.virtuel != 1 or p.virtuel IS NULL)
and (p.consigne != 1 or p.consigne IS NULL)
and (p.nonStocke != 1 or p.nonStocke IS NULL)
and (p.nonCommandable != 1 or p.nonCommandable IS NULL)
and (p.arretGamme != 1 or p.arretGamme IS NULL)
and (p.stock-COALESCE(p.stockReserve,0)+COALESCE(p.ralFournisseur,0)) < COALESCE(p.seuilMiniCommandeFournisseur,0)
'
// nonStocke nonCommandable
//and (p.produitInterne != 1 or p.produitInterne IS NULL)
)
->getQuery()
->execute();
}
public function updateASuggerer()
{
$query = $this->createQueryBuilder('p')
->update(Article::class, 'p')
->set('p.aSuggerer', '0')
->where('p.aSuggerer = 1 ')
->getQuery()
->execute();
$query = $this->createQueryBuilder('p')
->update(Article::class, 'p')
->set('p.aSuggerer', '1')
->join('p.conditionsAchat', 'cond')
//->where('(p.stock-COALESCE(p.stockReserve,0)+COALESCE(p.ralFournisseur,0)) < COALESCE(p.seuilMiniCommandeFournisseur,0) and p.produitInterne != 1 and p.virtuel != 1 and p.consigne != 1')
->where(
'
(p.virtuel != 1 or p.virtuel IS NULL)
and (p.consigne != 1 or p.consigne IS NULL)
and (p.nonStocke != 1 or p.nonStocke IS NULL)
and (p.nonCommandable != 1 or p.nonCommandable IS NULL)
and (p.arretGamme != 1 or p.arretGamme IS NULL)
and (p.stock-COALESCE(p.stockReserve,0)+COALESCE(p.ralFournisseur,0)) < (COALESCE(p.seuilMiniCommandeFournisseur,0) * 1.05)
and (p.stock-COALESCE(p.stockReserve,0)+COALESCE(p.ralFournisseur,0)) > COALESCE(p.seuilMiniCommandeFournisseur,0)
'
// nonStocke nonCommandable
//and (p.produitInterne != 1 or p.produitInterne IS NULL)
)
->getQuery()
->execute();
}
public function historiquePrix($article)
{
$sql = "
select
p.date_maj,
p.libelle,
p.reference,
p.utilisateur_id,
p.prix_base,
p.prix_vente,
p.coefficient_px_base_px_vente,
p.marge,
p.taux_marge,
p.pump,
p.cpump,
p.marge_reelle,
u.prenom as equipier_prenom,
u.nom as equipier_nom
FROM article__article_audit as p
LEFT JOIN utilisateur__utilisateurs as u on u.id = p.utilisateur_id
WHERE
p.id = ".$article->getId()."
and p.date_maj_prix is NOT NULL
GROUP BY p.date_maj_prix
ORDER BY p.rev DESC
";
//echo $sql;
$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$prix = $stmt->fetchAll();
return $prix;
//print_r($prix);
}
public function setMiniMaxi($ids, $donnees)
{
//print_r($donnees);
if ( ! array_key_exists('stockAuto', $donnees)) {
$donnees['stockAuto'] = 0;
}
$chaine = "";
for ($i = 0; $i < count($ids); $i++) {
$chaine .= $ids[$i].",";
}
$chaine .= ",";
$chaine = str_replace(",,", "", $chaine);
$sql = " UPDATE
article__article
SET
seuil_commande_f_mini = ".floatval($donnees['mini']).",
seuil_commande_f_maxi = ".floatval($donnees['maxi']).",
stock_auto = ".$donnees['stockAuto']."
WHERE
id IN (".$chaine.")
";
$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
}
public function valorisation($parametres)
{
$where = "";
if (array_key_exists('date', $parametres) && $parametres['date'] != "") {
//$dateDebut = new \Datetime($param['param']['date']);
$dateDebut = DateTime::createFromFormat('d/m/Y', $parametres['date']);
$dateDebut->setTime(23, 59, 59);
} else {
$dateDebut = new DateTime();
$dateDebut->setTime(23, 59, 59);
}
if (array_key_exists('article', $parametres) && $parametres['article'] != "") {
if ($where != "") {
$where .= " and ";
}
$where .= "p.reference like '".$parametres['article']."%'";
}
if (array_key_exists('marque', $parametres) && $parametres['marque'] != "") {
if ($where != "") {
$where .= " and ";
}
$where .= "p.marque_id = ".$parametres['marque']."";
}
/*
if(array_key_exists('inventaire', $parametres) && $parametres['inventaire'] != "") {
if($where != "") $where .= " and ";
$where .= "p.inventaire_id = ".$parametres['inventaire']."";
}
*
*/
if (array_key_exists('stock', $parametres) && $parametres['stock'] == "") {
$parametres['stock'] = 0;
}
if (array_key_exists('stock', $parametres) && $parametres['stock'] == 0) {
if ($where != "") {
$where .= " and ";
}
//$where .= "p.stock > 0";
$where .= "article__mouvement_stock.stock > 0";
}
$orderBy = "";
if (array_key_exists('tri', $parametres) && $parametres['tri'] != "") {
$orderBy = ' ORDER BY p.'.$parametres['tri'].' ASC';
}
//echo $parametres['stock']." Date debut ".$dateDebut->format("d-m-Y H:i:s");
/*
$sql = "
select
p.id,
p.libelle,
p.reference,
p.stock,
p.pump,
s.stock as stock_date,
s.id as mv_id,
s.date_mouvement
FROM article__article as p
LEFT JOIN (select article_id,date_mouvement,stock,id FROM `article__mouvement_stock` ORDER BY `date_mouvement` DESC) as `s` ON s.article_id=p.id
WHERE (s.date_mouvement <= '".$dateDebut->format("Y-m-d H:i:s")."' or s.date_mouvement is null)
".$where."
GROUP BY p.id
ORDER BY s.date_mouvement DESC
LIMIT 0,5000
";
*/
/*
$sql = "
select p.id as article_id, p.libelle, p.reference, p.stock, p.pump,p.cpump, lastMessage.id,lastMessage.date_mouvement,article__mouvement_stock.id,article__mouvement_stock.stock as stock_date
FROM article__article as p
LEFT JOIN (
select MAX(id) as id, MAX(date_mouvement) AS date_mouvement,article_id
FROM article__mouvement_stock
WHERE date_mouvement <= '".$dateDebut->format("Y-m-d H:i:s")."'
GROUP BY article_id
) AS lastMessage
ON p.id = lastMessage.article_id
LEFT JOIN article__mouvement_stock
ON article__mouvement_stock.id = lastMessage.id";
*
*/
if (array_key_exists('inventaire', $parametres) && $parametres['inventaire'] != "" && 1 == 0) {
if ($where != "") {
$where .= " and ";
}
$where .= "ai.inventaire_id = ".$parametres['inventaire'];
$sql = "select p.id as article_id, p.libelle, p.reference, p.stock, p.pump,p.cpump,p.pump_hors_frais
FROM inventaire__article_inventaire ai
LEFT JOIN article__article as p ON p.id = ai.article_id
";
if ($where != "") {
$sql .= " WHERE ".$where;
}
} else {
$sql = "
select p.id as article_id, p.libelle, p.reference, p.stock, p.pump,p.cpump,p.pump_hors_frais
FROM article__article as p
LEFT JOIN article__mouvement_stock ON article__mouvement_stock.article_id = p.id ";
if ($where != "") {
$where .= " and ";
}
//$where .= "p.marque_id = ".$parametres['marque']."";
$where .= "
(
(p.est_commentaire is null or p.est_commentaire = 0)
and
(p.virtuel is null or p.virtuel = 0)
and
(p.main_oeuvre is null or p.main_oeuvre = 0)
and
(p.non_stocke is null or p.non_stocke = 0)
and
(p.divers is null or p.divers = 0)
and
(p.divers_composes is null or p.divers_composes = 0)
)";
if ($where != "") {
$sql .= " WHERE ".$where;
}
$sql .= " GROUP BY p.id";
if ($orderBy != "") {
$sql .= $orderBy;
} else {
$sql .= " ORDER BY article__mouvement_stock.date_mouvement DESC";
}
}
//$sql .= " LIMIT 0,500";
/*
$sql = "
select p.id, p.libelle, p.reference, p.stock, p.pump, s.stock as stock_date, s.id as mv_id, s.date_mouvement FROM article__article as p LEFT JOIN (select article_id,date_mouvement,stock,id FROM `article__mouvement_stock` WHERE (date_mouvement <= '2016-09-09 23:59:59' or date_mouvement is null) ORDER BY `date_mouvement` DESC) as `s` ON s.article_id=p.id WHERE p.reference like 'TOY-04465-YZZAL%' GROUP BY p.id ORDER BY s.date_mouvement DESC LIMIT 0,500
";
*/
$sql = trim($sql);
//var_dump($sql);die;
//echo "<br/><br/>".$sql;
$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
$articles = $stmt->executeQuery()->fetchAllAssociative();
return $articles;
}
public function getPrixAchat($idArticle, $date)
{
//$idArticle = "2345678904567890";
$date = DateTime::createFromFormat('d/m/Y', $date);
$date->setTime(23, 59, 59);
//echo "<div>".$idArticle." DATE : ".$date->format("d/m/Y H:i:s")."</div>";
$sql = "
select ca.prix_achat_net, d.taux_change as taux_change
FROM article__condition_achat_audit as ca
LEFT JOIN fournisseur__fournisseur f ON f.id = ca.fournisseur_id
LEFT JOIN article__devise d ON d.id = f.devise_id
WHERE
ca.date <= '".$date->format("Y-m-d H:i:s")."'
and
ca.statut = 1
and
ca.article_id = ".$idArticle."
ORDER BY rev DESC
LIMIT 0,1
";
$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
$prix = $stmt->executeQuery()->fetchAllAssociative();
if (count($prix) > 0) {
$prixAchat = $prix[0]['prix_achat_net'];
if ($prix[0]['taux_change'] != 0 && $prix[0]['taux_change'] != '0' && $prix[0]['taux_change'] != '') {
$prixAchat = $prixAchat * floatval($prix[0]['taux_change']);
}
return $prixAchat;
} else {
$sql = "
select ca.prix_achat_net, d.taux_change as taux_change
FROM article__condition_achat as ca
LEFT JOIN fournisseur__fournisseur f ON f.id = ca.fournisseur_id
LEFT JOIN article__devise d ON d.id = f.devise_id
WHERE
ca.statut = 1
and
ca.article_id = ".$idArticle."
ORDER BY ca.id DESC
LIMIT 0,1
";
$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
$prix = $stmt->executeQuery()->fetchAllAssociative();
if (count($prix) > 0) {
$prixAchat = $prix[0]['prix_achat_net'];
if ($prix[0]['taux_change'] != 0 && $prix[0]['taux_change'] != '0' && $prix[0]['taux_change'] != '') {
$prixAchat = $prixAchat * floatval($prix[0]['taux_change']);
}
return $prixAchat;
} else {
$sql = "
select ca.prix_base
FROM `article__article_audit` ca
WHERE
ca.date <= '".$date->format("Y-m-d H:i:s")."'
and
ca.id = ".$idArticle."
ORDER BY rev DESC
LIMIT 0,1
";
$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
$prix = $stmt->executeQuery()->fetchAllAssociative();
if (count($prix) > 0) {
return $prix[0]['prix_base'];
} else {
$sql = "
select ca.prix_base
FROM `article__article` ca
WHERE
ca.date <= '".$date->format("Y-m-d H:i:s")."'
and
ca.id = ".$idArticle."
";
$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
$prix = $stmt->executeQuery()->fetchAllAssociative();
if (count($prix) > 0) {
return $prix[0]['prix_base'];
} else {
return "0";
}
}
}
}
}
public function getDernierPrixAchat($idArticle, $date)
{
//$idArticle = "2345678904567890";
$date = DateTime::createFromFormat('d/m/Y', $date);
$date->setTime(23, 59, 59);
//echo "<div>".$idArticle." DATE : ".$date->format("d/m/Y H:i:s")."</div>";
$sql = "
select ac.prix_base
FROM `commerciale__article_commande` as ac
LEFT JOIN commerciale__commande_fournisseur as c on ac.commande_fournisseur_id = c.id
LEFT JOIN article__article as ar on ar.id = ac.article_id
WHERE
c.date <= '".$date->format("Y-m-d H:i:s")."'
and
c.statut_commande_id = 6
and
ac.article_id = ".$idArticle."
ORDER BY c.date DESC
LIMIT 0,1
";
$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
$prix = $stmt->executeQuery()->fetchAllAssociative();
if (count($prix) > 0) {
return $prix[0]['prix_base'];
} else {
return "0";
}
}
public function getArticleEquivalent($idArticle)
{
$sql = "
select
a.id,
a.libelle,
a.reference,
m.libelle as marque
FROM article_article as aa
LEFT JOIN article__article as a on a.id = aa.article_target
LEFT JOIN article__marque as m on m.id = a.marque_id
WHERE
aa.article_source = ".$idArticle."
";
$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
$equivalences = $stmt->executeQuery()->fetchAllAssociative();
return $equivalences;
}
public function estUneEquivalenceReciproque($article_equivalent, $article_source)
{
$sql = "
select
aa.article_source
FROM article_article as aa
WHERE
aa.article_source = ".$article_source."
and
aa.article_target = ".$article_equivalent."
";
//echo $sql;
$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
$reciproque = $stmt->executeQuery()->fetchAllAssociative();
if (count($reciproque) == 0) {
return false;
} else {
return true;
}
}
public function getArticlesAinventorierSansEmplacement($inventaire)
{
$date = "";
if ( ! is_null($inventaire->getDateMouvement())) {
$date = $inventaire->getDateMouvement();
}
//$where = "(eplct.id is null OR eplct.visibilite IS NULL OR eplct.visibilite = 0) AND (p.virtuel = 0 or p.virtuel is null) AND (p.prestation = 0 or p.prestation is null) AND (p.mainOeuvre = 0 or p.mainOeuvre is null) AND (p.nonCommandable = 0 or p.nonCommandable is null)";
/*
$where = "(p.virtuel = 0 or p.virtuel is null) AND (p.prestation = 0 or p.prestation is null) AND (p.mainOeuvre = 0 or p.mainOeuvre is null) AND (p.nonCommandable = 0 or p.nonCommandable is null)";
if($date != ''){
$where .= " and ((p.stock is not null AND p.stock != 0) or (mvt.dateMvt > '".$date->format("Y-m-d H:i:s")."' AND mvt.quantite != 0))";
} else {
//$where .= " AND p.stock is not null AND p.stock != 0";
$where .= " and ((p.stock is not null AND p.stock != 0) OR mvt.quantite != 0)";
}
*/
$where = "(p.virtuel = 0 or p.virtuel is null) AND (p.prestation = 0 or p.prestation is null) AND (p.mainOeuvre = 0 or p.mainOeuvre is null) AND (p.nonCommandable = 0 or p.nonCommandable is null)";
if ($inventaire->getInclureArticleDifferentZero()) {
$where .= " and ((p.stock is not null and p.stock > 0)";
if ($inventaire->getInclureArticleDateMouvement()) {
if ($date != "") {
$where .= " or (mvt.dateMvt > '".$date->format("Y-m-d H:i:s")."' and (mvt.quantite != 0 and p.stock <= 0)) ";
} else {
$where .= " OR (mvt.quantite != 0 and p.stock <= 0)";
}
}
$where .= ")";
} elseif ($inventaire->getInclureArticleDateMouvement() && $date != "") {
//$where = "((p.stock is not null and p.stock != 0) or (mvt.dateMvt > '".$date->format("Y-m-d H:i:s")."' and mvt.quantite != 0))";
$where .= "and ((mvt.dateMvt > '".$date->format("Y-m-d H:i:s")."' and (mvt.quantite != 0 and p.stock <= 0)) ";
if ($inventaire->getInclureArticleDifferentZero()) {
$where .= " OR (p.stock is not null and p.stock > 0) ";
}
$where .= ")";
} else {
$where .= "and 1=1 ";
}
$where .= ' and (p.nonStocke = 0 OR p.nonStocke IS NULL)';
$where .= ' and r.id NOT IN (15)';
$query = $this->createQueryBuilder('p')
->select(
'p.id, COUNT(mvt.id) as nbMvts, SUM(CASE WHEN eplct.visibilite = 1 AND eplct.dateSuppression IS NULL THEN 1 ELSE 0 END) AS nbEmplacements'
)//, COUNT(eplct.id) as nbeplcts
->leftJoin('p.mouvementStock', 'mvt')
->join('mvt.raisonMouvementStock', 'r')
->leftJoin('p.emplacements', 'eplct')
->where($where)
->orderBy('p.reference', 'ASC')
->groupBy('p.id');
//->having('nbMvts > 0 AND nbEmplacements = 0')
$having = "nbEmplacements = 0";
if ($inventaire->getInclureArticleDifferentZero()) {
if ($inventaire->getInclureArticleDateMouvement()) {
$having .= " and nbMvts >0";
}
} elseif ($inventaire->getInclureArticleDateMouvement() && $date != "") {
$having .= " and nbMvts >0";
if ($inventaire->getInclureArticleDifferentZero()) {
}
} else {
}
if ($having != "") {
$query->having($having);
}
//echo $query->getQuery()->getSql();
//exit;
//->setParameters(array('marque' => $marque,'archive'=>'0'))
$res = $query->getQuery()->getResult();
return $res;
}
public function getArticlesAinventorierParEmplacement($emplacement, $date = '')
{
$where = "p.stock is not null and p.stock != 0";
if ($date != '') {
$where = "((p.stock is not null and p.stock != 0) or (mvt.date > '".$date->format("Y-m-d H:i:s")."' and mvt.quantite != 0))";
}
if (is_object($emplacement->getNiveauUn())) {
$where .= ' and n1.id = '.$emplacement->getNiveauUn()->getId();
}
if (is_object($emplacement->getNiveauDeux())) {
$where .= ' and n2.id = '.$emplacement->getNiveauDeux()->getId();
}
if (is_object($emplacement->getNiveauTrois())) {
$where .= ' and n3.id = '.$emplacement->getNiveauTrois()->getId();
}
if (is_object($emplacement->getNiveauQuatre())) {
$where .= ' and n4.id = '.$emplacement->getNiveauQuatre()->getId();
}
/*
if(is_object($emplacement->getNiveauDeux())) $recherche['niveauDeux'] = $emplacement->getNiveauDeux();
if(is_object($emplacement->getNiveauTrois())) $recherche['niveauTrois'] = $emplacement->getNiveauTrois();
if(is_object($emplacement->getNiveauQuatre())) $recherche['niveauQuatre'] = $emplacement->getNiveauQuatre();
*
*/
$query = $this->createQueryBuilder('p')
->select('p.id, COUNT(mvt.id) as total, COUNT(eplct.id) as nbArticleOuEmplacement')
->leftJoin('p.mouvementStock', 'mvt')
->leftJoin('p.emplacements', 'eplct')
->leftJoin('eplct.niveauUn', 'n1')
->leftJoin('eplct.niveauDeux', 'n2')
->leftJoin('eplct.niveauTrois', 'n3')
->leftJoin('eplct.niveauQuatre', 'n4')
->where($where)
->groupBy('p.id')
->having('total > 0')//->setParameters(array('marque' => $marque,'archive'=>'0'))
;
$res = $query->getQuery()->getResult();
return $res;
}
public function getEmplacementsAinventorierParEmplacement($emplacement, $inventaire)
{
$date = "";
if ( ! is_null($inventaire->getDateMouvement())) {
$date = $inventaire->getDateMouvement();
}
/*
$where = "((p.stock is not null and p.stock != 0) OR mvt.quantite != 0)";
if($date != ''){
$where = "((p.stock is not null and p.stock != 0) or (mvt.dateMvt > '".$date->format("Y-m-d H:i:s")."' and mvt.quantite != 0))";
}
*/
if ($inventaire->getInclureArticleDifferentZero()) {
$where = "((p.stock is not null and p.stock > 0)";
if ($inventaire->getInclureArticleDateMouvement()) {
if ($date != "") {
$where .= " or (mvt.dateMvt > '".$date->format("Y-m-d H:i:s")."' and mvt.quantite != 0 and p.stock <=0)";
} else {
$where .= " OR (mvt.quantite != 0 and p.stock <= 0) ";
}
}
$where .= ")";
} elseif ($inventaire->getInclureArticleDateMouvement() && $date != "") {
//$where = "((p.stock is not null and p.stock != 0) or (mvt.dateMvt > '".$date->format("Y-m-d H:i:s")."' and mvt.quantite != 0))";
$where = "(( mvt.dateMvt > '".$date->format("Y-m-d H:i:s")."' and mvt.quantite != 0 and p.stock <= 0)";
if ($inventaire->getInclureArticleDifferentZero()) {
$where .= " OR (p.stock is not null and p.stock > 0) ";
}
$where .= ")";
} else {
$where = "1=1";
}
$where .= ' and eplct.visibilite = 1';
$where .= ' and (p.nonStocke = 0 OR p.nonStocke IS NULL)';
$where .= ' and (r.id NOT IN (15) or r.id is null)';
if (is_object($emplacement) and is_object($emplacement->getNiveauUn())) {
$where .= ' and n1.id = '.$emplacement->getNiveauUn()->getId();
}
if (is_object($emplacement) and is_object($emplacement->getNiveauDeux())) {
$where .= ' and n2.id = '.$emplacement->getNiveauDeux()->getId();
}
if (is_object($emplacement) and is_object($emplacement->getNiveauTrois())) {
$where .= ' and n3.id = '.$emplacement->getNiveauTrois()->getId();
}
if (is_object($emplacement) and is_object($emplacement->getNiveauQuatre())) {
$where .= ' and n4.id = '.$emplacement->getNiveauQuatre()->getId();
}
/*
if(is_object($emplacement->getNiveauDeux())) $recherche['niveauDeux'] = $emplacement->getNiveauDeux();
if(is_object($emplacement->getNiveauTrois())) $recherche['niveauTrois'] = $emplacement->getNiveauTrois();
if(is_object($emplacement->getNiveauQuatre())) $recherche['niveauQuatre'] = $emplacement->getNiveauQuatre();
*
*/
$query = $this->createQueryBuilder('p')
->select('eplct.id,p.stock, COUNT(mvt.id) as total, COUNT(eplct.id) as nbArticleOuEmplacement')
->leftJoin('p.mouvementStock', 'mvt')
->leftJoin('mvt.raisonMouvementStock', 'r')
->leftJoin('p.emplacements', 'eplct')
->leftJoin('eplct.niveauUn', 'n1')
->leftJoin('eplct.niveauDeux', 'n2')
->leftJoin('eplct.niveauTrois', 'n3')
->leftJoin('eplct.niveauQuatre', 'n4')
->where($where);
if ($emplacement !== null && is_object($emplacement) or 1 == 1) {
//echo "YYYY";
//exit;
$query->addOrderBy('eplct.libelle', 'ASC');
}
$query->addOrderBy('p.reference', 'ASC')
//->orderBy('p.reference', 'ASC')
->groupBy('eplct.id');
$having = "";
//->having('(total > 0 or (p.stock is not null and p.stock != 0))')
if ($inventaire->getInclureArticleDifferentZero()) {
$having = "((p.stock is null or p.stock > 0)";
if ($inventaire->getInclureArticleDateMouvement()) {
$having .= " or (total > 0 and p.stock <=0)";
}
$having .= ")";
} elseif ($inventaire->getInclureArticleDateMouvement() && $date != "") {
$having = "((total > 0 and p.stock <=0)";
if ($inventaire->getInclureArticleDifferentZero()) {
$having .= " or (p.stock is not null or p.stock > 0)";
}
$having .= ")";
} else {
}
if ($having != "") {
$query->having($having);
}//->setParameters(array('marque' => $marque,'archive'=>'0'))
;
//echo "IIII";
//exit;
//echo $query->getQuery()->getSql();
//exit;
$res = $query->getQuery()->getResult();
return $res;
}
public function getArticlesAinventorierParMarque($marque, $date = '')
{
$where = "p.marque = :marque and p.stock is not null and p.stock != 0";
if ($date != '') {
$where = "p.marque = :marque and ((p.stock is not null and p.stock != 0) or (mvt.dateMvt > '".$date->format("Y-m-d H:i:s")."' and mvt.quantite != 0))";
}
$query = $this->createQueryBuilder('p')
->select(
'p.id,p.stock, COUNT(mvt.id) as total, CASE
WHEN COUNT(eplct.id) > COUNT(p.id) THEN COUNT(eplct.id)
ELSE COUNT(p.id)
as nbArticleOuEmplacement'
)
->leftJoin('p.mouvementStock', 'mvt')
->leftJoin('p.emplacements', 'eplct')
->where($where)
->groupBy('p.id')
->having('(total > 0 or (p.stock is not null and p.stock != 0))')
->setParameters(['marque' => $marque]);
$res = $query->getQuery()->getResult();
return $res;
}
public function getArticlesAinventorierParMarqueEtEmplacement($marque, $emplacement = null, $inventaire,$date = '')
{
$selectId = 'p';
$parameters = ['marque' => $marque];
/*
$where = "p.marque = :marque and ((p.stock is not null and p.stock != 0) OR mvt.quantite != 0)";
if($date != ''){
$where = "p.marque = :marque and ((p.stock is not null and p.stock != 0) or (mvt.dateMvt > '".$date->format("Y-m-d H:i:s")."' and mvt.quantite != 0))";
}
*/
$where = "p.marque = :marque and ((p.stock is not null and p.stock != 0) OR mvt.quantite != 0)";
if ($inventaire->getInclureArticleDifferentZero()) {
$where .= " and ((p.stock is not null and p.stock > 0)";
if ($inventaire->getInclureArticleDateMouvement()) {
if ($date != "") {
$where .= " OR (mvt.dateMvt > '".$date->format("Y-m-d H:i:s")."' and mvt.quantite != 0 and p.stock <= 0)";
} else {
$where .= " OR (mvt.quantite > 0 and p.stock <= 0) ";
}
}
$where .= ")";
} elseif ($inventaire->getInclureArticleDateMouvement() && $date != "") {
//$where = "((p.stock is not null and p.stock != 0) or (mvt.dateMvt > '".$date->format("Y-m-d H:i:s")."' and mvt.quantite != 0))";
$where .= "((mvt.dateMvt > '".$date->format("Y-m-d H:i:s")."' and mvt.quantite != 0 and p.stock <= 0)";
if ($inventaire->getInclureArticleDifferentZero()) {
$where .= " OR (p.stock is not null and p.stock > 0) ";
}
$where .= ")";
}
$where .= ' and (p.nonStocke = 0 OR p.nonStocke IS NULL)';
$where .= ' and (r.id NOT IN (15) or r.id is null)';
if ($emplacement !== null && is_object($emplacement)) {
if (is_object($emplacement->getNiveauUn())) {
$where .= ' and n1.id = '.$emplacement->getNiveauUn()->getId();
}
if (is_object($emplacement->getNiveauDeux())) {
$where .= ' and n2.id = '.$emplacement->getNiveauDeux()->getId();
}
if (is_object($emplacement->getNiveauTrois())) {
$where .= ' and n3.id = '.$emplacement->getNiveauTrois()->getId();
}
if (is_object($emplacement->getNiveauQuatre())) {
$where .= ' and n4.id = '.$emplacement->getNiveauQuatre()->getId();
}
/*
$where .= " and eplct.id = :emplacement";
$parameters['emplacement'] = $emplacement->getId();
*
*/
$where .= ' and eplct.visibilite = 1';
$selectId = 'eplct';
}
$query = $this->createQueryBuilder('p')
->select(
$selectId.'.id,p.stock, COUNT(mvt.id) as total, CASE
WHEN COUNT(eplct.id) > COUNT(p.id) THEN COUNT(eplct.id)
ELSE COUNT(p.id)
as nbArticleOuEmplacement'
)
->leftJoin('p.mouvementStock', 'mvt')
->leftJoin('mvt.raisonMouvementStock', 'r')
->leftJoin('p.emplacements', 'eplct')
->leftJoin('eplct.niveauUn', 'n1')
->leftJoin('eplct.niveauDeux', 'n2')
->leftJoin('eplct.niveauTrois', 'n3')
->leftJoin('eplct.niveauQuatre', 'n4')
->where($where);
$query->addOrderBy('p.reference', 'ASC');
if ($emplacement !== null && is_object($emplacement)) {
$query->addOrderBy('eplct.libelle', 'ASC');
}
$query->groupBy($selectId.'.id')
->setParameters($parameters);
//->having('(total > 0 or (p.stock is not null and p.stock != 0))')
$having = "";
if ($inventaire->getInclureArticleDifferentZero()) {
$having = "((p.stock is null or p.stock > 0)";
if ($inventaire->getInclureArticleDateMouvement()) {
$having .= " or (total > 0 and p.stock <=0)";
}
$having .= ")";
} elseif ($inventaire->getInclureArticleDateMouvement() && $date != "") {
$having = "((total > 0 and p.stock <=0)";
if ($inventaire->getInclureArticleDifferentZero()) {
$having .= " or (p.stock is not null or p.stock > 0)";
}
$having .= ")";
} else {
}
if ($having != "") {
$query->having($having);
}
$res = $query->getQuery()->getResult();
return $res;
}
public function getArticlesMouvementes($date = '')
{
$where = "(p.stock is not null and p.stock != 0) OR mvt.quantite != 0";
if ($date != '') {
$where = "(p.stock is not null and p.stock != 0) or (mvt.dateMvt > '".$date->format("Y-m-d H:i:s")."' and mvt.quantite != 0)";
//exclure certains mvt
}
$query = $this->createQueryBuilder('p')
->select('p.id, COUNT(mvt.id) as nbMvts')//, COUNT(eplct.id) as nbeplcts
->leftJoin('p.mouvementStock', 'mvt')
->where($where)
->groupBy('p.id')
->having('nbMvts > 0')//->setParameters(array('marque' => $marque,'archive'=>'0'))
;
$res = $query->getQuery()->getResult();
return $res;
}
public function getArticlesMouvementesPump($date = '')
{
$where = "mvt.quantite > 0 and r.action = 'augmenter'";
if ($date != '') {
$where = "mvt.dateMvt > '".$date->format("Y-m-d H:i:s")."' AND mvt.quantite > 0 AND r.action = 'augmenter'";
//exclure certains mvt
}
$query = $this->createQueryBuilder('p')
->select('p.id, COUNT(mvt.id) as nbMvts')//, COUNT(eplct.id) as nbeplcts
->leftJoin('p.mouvementStock', 'mvt')
->leftJoin('mvt.raisonMouvementStock', 'r')
->where($where)
->groupBy('p.id')
->having('nbMvts > 0')//->setParameters(array('marque' => $marque,'archive'=>'0'))
;
$res = $query->getQuery()->getResult();
return $res;
}
public function getArticlesPumpNull()
{
$query = $this->createQueryBuilder('p')
->select('p.id')//, COUNT(eplct.id) as nbeplcts
->join('p.conditionsAchat', 'ca')
->where('p.pump IS NULL OR p.pump = 0')
->groupBy('p.id');
$res = $query->getQuery()->getResult();
return $res;
}
public function articleCommandeSupprimableCommande($ac)
{
$commande = $ac->getCommande();
$article = $ac->getArticle();
$res = $this->getEntityManager()
->createQuery(
"select count(ac.id) as total
FROM App\Entity\GestionComerciale\ArticleCommande ac
LEFT JOIN App\Entity\GestionComerciale\Commande doc with ac.commande = doc.id
LEFT JOIN App\Entity\GestionComerciale\StatutCommande s with doc.statutCommande = s.id
WHERE doc.commande = :commande and ac.article = :article and doc.typeDocumentCommercial IN (:typeDocumentCommercial) and s.ordre NOT IN (:statut)
"
)
->setParameters(['commande' => $commande->getId(), 'article' => $article->getId(), 'typeDocumentCommercial' => [TypeDocumentCommercial::BP, TypeDocumentCommercial::BL, TypeDocumentCommercial::FACTURE], 'statut' => [0]])
->getOneOrNullResult();
if ($res === null || $res['total'] == 0) {
return true;
}
return false;
}
public function findByStockReserveNotNull()
{
$where = "p.stockReserve is not null and p.stockReserve != 0 and (p.divers = 0 or p.divers is null)";// and (p.divers_composes = 0 or p.divers_composes is null)";
$query = $this->createQueryBuilder('p')
->where($where)//->setMaxResults(10);
;
//$res = $query->getQuery()->getResult();
return $query;
}
public function findByStockReserveInferieur0()
{
$where = "p.stockReserve < 0 and (p.divers = 0 or p.divers is null) and (p.diversComposes = 0 or p.diversComposes is null)";
$query = $this->createQueryBuilder('p')
->where($where)//->setMaxResults(10);
;
//$res = $query->getQuery()->getResult();
return $query;
}
public function getArticleAvecCoeffCondit()
{
$where = "p.majPrix = 1 and c.defaut = 1 and c.coefficientConditionnement > 1";
$query = $this->createQueryBuilder('p')
//->select('p.id')
->leftJoin('p.conditionsAchat', 'c')
//->groupBy('p.id')
->where($where);
return $query;
}
public function findByCommande($commandeId)
{
$query = $this->createQueryBuilder('p')
//->select('p.id')
->leftJoin('p.articleCommande', 'ac')
->leftJoin('ac.commande', 'c')
->groupBy('p.id')
->where('c.id = :commande')
->setParameters(['commande' => $commandeId]);
return $query;
}
public function getArticleByRef($ref, $categorie)
{
$query = $this->createQueryBuilder('p')
//->select('p.id')
->leftJoin('p.articleCategorie', 'ac')
->leftJoin('ac.categorie', 'c')
->where("p.ref LIKE ':ref'")
->setParameters(['ref' => '%|'.$ref]);
return $query;
}
public function findRandomArticles(int $limit = 3,$categorie_id,$client_id)
{
$conn = $this->getEntityManager()->getConnection();
$sql = 'SELECT *
FROM article__article
WHERE
dispo > :dispo and
statut = :statut
ORDER BY RAND()
LIMIT :limit';
$sql = 'SELECT a.*
FROM article__article a
INNER JOIN article__article_categorie ac
ON a.id = ac.article_id
WHERE a.dispo > :dispo
AND a.statut = :statut
AND ac.categorie_id = :categorie_id
ORDER BY RAND()
LIMIT :limit
';
$sql = 'SELECT a.*
FROM article__article a
INNER JOIN article__article_categorie ac ON a.id = ac.article_id
LEFT JOIN article__marque m ON a.marque_id = m.id
WHERE (a.dispo > :dispo or 1=1)
AND a.statut = :statut
AND ac.categorie_id = :categorie_id
AND (m.id is null or m.statut = 1)
ORDER BY RAND()
LIMIT :limit
';
$sql = 'SELECT a.*
FROM article__article a
INNER JOIN article__article_categorie ac ON a.id = ac.article_id
LEFT JOIN article__marque m ON a.marque_id = m.id
LEFT JOIN client__client_marque ccm ON ccm.marque_id = a.marque_id AND ccm.client_id = :client_id
WHERE (a.dispo > :dispo or 1=1)
AND a.statut = :statut
AND ac.categorie_id = :categorie_id
AND (m.id is null or m.statut = 1)
AND ccm.marque_id IS NULL
ORDER BY RAND()
LIMIT :limit
';
$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('client_id', $client_id);
$stmt->bindValue('dispo', 1);
$stmt->bindValue('statut', 1);
$stmt->bindValue('categorie_id', $categorie_id);
//$stmt->bindValue('public', true);
$stmt->bindValue('limit', $limit, \PDO::PARAM_INT);
$hasard = $stmt->executeQuery()->fetchAllAssociative();
return $hasard;
}
public function getRechercheArticleSelectObi(
$recherche,
$produitInterne = null,
$fournisseur = null,
$articlesAExclure = null,
$stock = "",
$reference_search = "",
$libelle_search = "",
$prix_search = "",
$tri_reference = "",
$tri_libelle = "",
$tri_prix = "",
$tri_stock = "",
$categories = "",
$mouvemente = 1,
$inventaire = "",
$declinaisons = false
) {
$select = "";
$recherche = str_replace("*", "%", $recherche);
$recherche = str_replace(".", "", $recherche);
$recherche = str_replace("/", "\/", $recherche);
$recherche = preg_replace('/\s+/', ' ', $recherche);
$recherche = trim($recherche);
$whereStock = "";
if ($stock == 1) {
$whereStock .= " AND p.stock > 0";
}
// Gestion du tri
$order = "";
if ($tri_reference != "") { $order = "p.reference " . $tri_reference . ", "; }
if ($tri_prix != "") { $order = "p.prix_vente " . $tri_prix . ", "; }
if ($tri_stock != "") { $order = "p.stock " . $tri_stock . ", "; }
if ($tri_libelle != "") { $order = "p.libelle " . $tri_libelle . ", "; }
if ($order == "") { $order = "p.reference ASC "; }
$order = str_replace(",", "", $order);
// Construction SQL
$sql = "
SELECT
p.afficher_composant_pdf_externe,
p.virtuel,
p.dateProchaineDispo,
p.libelle_secondaire,
CONCAT(p.reference, ' ', p.libelle) as libelle_construit,
p.libelle,
p.id,
p.prix_vente as prixVente,
p.prix_base as prixAchat,
p.poids,
p.stock,
p.dispo,
p.stock_reserve,
p.reference,
p.arret_gamme,
marque.libelle as libelle_marque,
marque.id as id_marque,
marque.logo as logo_marque
$select
FROM article__article as p
LEFT JOIN article__marque as marque ON marque.id = p.marque_id
WHERE
(p.archive IS NULL OR p.archive = 0)
AND (p.arret_gamme IS NULL OR p.arret_gamme = 0)
AND (p.parent_id IS NULL)
AND (p.statut IS NULL OR p.statut = 1)
";
$params = [];
if (!empty($recherche)) {
// -----------------------------
// 1️⃣ Recherche multi-mots sur libelle
// -----------------------------
$words = preg_split('/\s+/', $recherche);
$libelleConditions = [];
$i = 0;
foreach ($words as $word) {
if ($word == '') continue;
$key = "libelle_$i";
$libelleConditions[] = "LOWER(p.libelle) LIKE :$key";
$params[$key] = "%" . strtolower($word) . "%";
$i++;
}
if (!empty($libelleConditions)) {
$sql .= " AND ((" . implode(" AND ", $libelleConditions) . ")";
}
// -----------------------------
// 2️⃣ Recherche complète sur references
// -----------------------------
$sql .= " OR (
p.reference LIKE :ref_full
OR p.reference_appel_1 LIKE :ref_full
OR p.reference_appel_2 LIKE :ref_full
OR p.reference_appel_3 LIKE :ref_full
OR p.reference_appel_4 LIKE :ref_full
OR p.reference_appel_5 LIKE :ref_full
OR p.reference_appel_6 LIKE :ref_full
OR p.reference_appel_7 LIKE :ref_full
OR p.reference_appel_8 LIKE :ref_full
OR p.reference_appel_9 LIKE :ref_full
OR p.reference_appel_10 LIKE :ref_full
OR p.reference_appel_11 LIKE :ref_full
))";
$params['ref_full'] = "%$recherche%";
}
$sql .= $whereStock;
$sql .= "
ORDER BY $order, p.libelle ASC
LIMIT 0,100
";
$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
//echo $sql;
// exit;
$resultats = $stmt->executeQuery($params)->fetchAllAssociative();
return $resultats;
}
public function old___getRechercheArticleSelectObi($recherche, $produitInterne = null, $fournisseur = null, $articlesAExclure = null, $stock = "", $reference_search = "", $libelle_search = "",
$prix_search = "", $tri_reference = "", $tri_libelle = "", $tri_prix = "", $tri_stock = "", $categories = "", $mouvemente = 1, $inventaire = "",
$declinaisons = false
) {
$match = 'p.reference,p.libelle,p.reference_appel_1,p.reference_appel_2,p.reference_appel_3,p.reference_appel_4,p.reference_appel_5,p.reference_appel_6,p.reference_appel_7,p.reference_appel_8,p.reference_appel_9,p.reference_appel_10,p.reference_appel_11';
$match2 = 'ca.reference_fournisseur, ca.reference_fournisseur_appel';
$select = "";
$jointureFromInventaire = '';
$jointureWhereInventaire = "";
$recherche = str_replace("*", "%", $recherche);
$recherche = str_replace(".", "", $recherche);
$recherche = str_replace("/", "\/", $recherche);
$recherche = str_replace(" ", " ", $recherche);
$recherche = str_replace(" ", " ", $recherche);
$recherche_ref_fourn = $recherche;
//$recherche = str_replace("-"," ",$recherche);
$recherche_tmp = $recherche;
$avecEtoile = false;
$pos = strpos($recherche, "*");
if ($pos !== false) {
$avecEtoile = true;
}
$wherePrix = "";
$tampon_recherche = $recherche;
$recherche = trim($recherche);
if (!$avecEtoile) {
// $recherche = str_replace(" ", "* ", $recherche);
}
if (!$avecEtoile) {
// $recherche .= "*";
}
$whereSuppl = " and p.parent_id is null and (p.statut is null or p.statut = 1)";
$whereStock = "";
if ($stock == 1) {
$whereStock .= " and p.stock > 0";
}
$order = "";
if ($tri_reference != "") {
$order = "p.reference ".$tri_reference.", ";
}
if ($tri_prix != "") {
$order = "p.prix_vente ".$tri_prix.", ";
}
if ($tri_stock != "") {
$order = "p.stock ".$tri_stock.", ";
}
if ($tri_libelle != "") {
$order = "p.libelle ".$tri_libelle.", ";
}
if ($order == "") {
$order = "p.reference ASC ";
}
$order = str_replace(",", "", $order);
$jointureFrom = "";
$jointureWhere = "";
if ($recherche != "" and $recherche != "*" or 1==1) {
if(1==1) {
if(1==1) {
$recherche_contruit = "";
$recherche_tab = explode(" ", $recherche);
for ($r = 0; $r < count($recherche_tab); $r++)
{
$recherche_tab[$r] = str_replace("+", "#+", $recherche_tab[$r]);
$recherche_contruit .= "+".$recherche_tab[$r]." ";
}
$recherche = trim($recherche, "*");
$sql = "
select
p.afficher_composant_pdf_externe,
p.virtuel,
p.dateProchaineDispo,
p.libelle_secondaire,
CONCAT(CONCAT(p.reference, ' '), p.libelle) as libelle_construit,
p.libelle,
p.id,
p.prix_vente as prixVente,
p.prix_base as prixAchat,
p.poids,
p.stock,
p.dispo,
p.stock_reserve,
p.reference,
p.arret_gamme,
marque.libelle as libelle_marque,
marque.id as id_marque,
marque.logo as logo_marque
"
.$select
." FROM article__article as p
LEFT JOIN article__marque as marque ON marque.id = p.marque_id
WHERE ";
$sql .= " ((p.reference LIKE '%".$recherche."%')
or
(p.reference_appel_1 LIKE '%".$recherche."%')
or
(p.reference_appel_2 LIKE '%".$recherche."%')
or
(p.reference_appel_3 LIKE '%".$recherche."%')
or
(p.reference_appel_4 LIKE '%".$recherche."%')
or
(p.reference_appel_5 LIKE '%".$recherche."%')
or
(p.reference_appel_6 LIKE '%".$recherche."%')
or
(p.reference_appel_7 LIKE '%".$recherche."%')
or
(p.reference_appel_8 LIKE '%".$recherche."%')
or
(p.reference_appel_9 LIKE '%".$recherche."%')
or
(p.reference_appel_10 LIKE '%".$recherche."%')
or
(p.libelle LIKE '%".$recherche."%'))
AND
(p.archive is null or p.archive = 0)
AND
(p.arret_gamme is null or p.arret_gamme = 0)";
//$sql .= ")";
$sql .= $jointureWhereInventaire;
// $sql .= " GROUP BY p.id
$sql .= " order by ".$order.", libelle asc
LIMIT 0,100
";
}
}
}
// echo $sql;
$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
$resultats = $stmt->executeQuery()->fetchAllAssociative();
return $resultats;
}
public function ___getRechercheArticleSelectObi($recherche, $produitInterne = null, $fournisseur = null, $articlesAExclure = null, $stock = "", $reference_search = "", $libelle_search = "",
$prix_search = "", $tri_reference = "", $tri_libelle = "", $tri_prix = "", $tri_stock = "", $categories = "", $mouvemente = 1, $inventaire = "",
$declinaisons = false
) {
$match = 'p.reference,p.libelle,p.reference_appel_1,p.reference_appel_2,p.reference_appel_3,p.reference_appel_4,p.reference_appel_5,p.reference_appel_6,p.reference_appel_7,p.reference_appel_8,p.reference_appel_9,p.reference_appel_10,p.reference_appel_11';
$match2 = 'ca.reference_fournisseur, ca.reference_fournisseur_appel';
$select = "";
$jointureFromInventaire = '';
$jointureWhereInventaire = "";
$recherche = str_replace("*", "%", $recherche);
$recherche = str_replace(".", "", $recherche);
$recherche = str_replace("/", "\/", $recherche);
$recherche = str_replace(" ", " ", $recherche);
$recherche = str_replace(" ", " ", $recherche);
$recherche_ref_fourn = $recherche;
//$recherche = str_replace("-"," ",$recherche);
$recherche_tmp = $recherche;
$avecEtoile = false;
$pos = strpos($recherche, "*");
if ($pos !== false) {
$avecEtoile = true;
}
$wherePrix = "";
$tampon_recherche = $recherche;
$recherche = trim($recherche);
if (!$avecEtoile) {
$recherche = str_replace(" ", "* ", $recherche);
}
if (!$avecEtoile) {
$recherche .= "*";
}
$whereSuppl = " and p.parent_id is null and (p.statut is null or p.statut = 1)";
$whereStock = "";
if ($stock == 1) {
$whereStock .= " and p.stock > 0";
}
$order = "";
if ($tri_reference != "") {
$order = "p.reference ".$tri_reference.", ";
}
if ($tri_prix != "") {
$order = "p.prix_vente ".$tri_prix.", ";
}
if ($tri_stock != "") {
$order = "p.stock ".$tri_stock.", ";
}
if ($tri_libelle != "") {
$order = "p.libelle ".$tri_libelle.", ";
}
if ($order == "") {
$order = "p.reference ASC ";
}
$order = str_replace(",", "", $order);
$jointureFrom = "";
$jointureWhere = "";
if ($recherche != "" and $recherche != "*" or 1==1) {
if(1==1) {
if(1==1) {
$recherche_contruit = "";
$recherche_tab = explode(" ", $recherche);
for ($r = 0; $r < count($recherche_tab); $r++)
{
$recherche_tab[$r] = str_replace("+", "#+", $recherche_tab[$r]);
$recherche_contruit .= "+".$recherche_tab[$r]." ";
}
$recherche = trim($recherche, "*");
$sql = "
select
p.dateProchaineDispo,
p.libelle_secondaire,
p.reference_appel_2,
p.reference_appel_3,
p.reference_appel_4,
p.reference_appel_5,
p.reference_appel_6,
p.reference_appel_7,
p.reference_appel_8,
p.reference_appel_9,
p.reference_appel_10,
p.reference_appel_11,
p.stock_reserve_fabrication,
p.virtuel,
CONCAT(CONCAT(p.reference, ' '), p.libelle) as libelle_construit,
p.article_remplacement_id,
p.libelle,
p.id,
p.prix_vente as prixVente,
p.prix_base as prixAchat,
p.poids,
p.stock,
p.dispo,
p.stock_reserve,
p.reference,
p.pump,
p.cpump,
p.arret_gamme,
marque.libelle as libelle_marque,
marque.id as id_marque,
marque.logo as logo_marque,
MATCH (".$match.") AGAINST ('".$recherche_contruit."' IN BOOLEAN MODE) as ponderation"
.$select
." FROM article__article as p ".$jointureFrom.$jointureFromInventaire."
LEFT JOIN article__marque as marque ON marque.id = p.marque_id
WHERE ";
if ($jointureWhere != "") {
$sql .= $jointureWhere." and ";
}
$sql .= " p.temporaire is null
and ( marque.statut = 1 or marque.id is null) and (p.archive = 0 or p.archive is null) ".$whereSuppl."
and
(MATCH (".$match.")
AGAINST ('".$recherche_contruit."' IN BOOLEAN MODE)";
$sql .= "";
$sql .= $wherePrix.$whereStock;
$sql .= " OR (p.reference LIKE '%".$recherche."%') ";
$sql .= ")";
$sql .= $jointureWhereInventaire;
$sql .= " GROUP BY p.id
order by ".$order.", ponderation desc
LIMIT 0,100
";
}
}
}
$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
$resultats = $stmt->executeQuery()->fetchAllAssociative();
return $resultats;
}
}

src/Repository/Articles/ArticleRepository.php line 3824