<?php

namespace App\Repository;

use App\Entity\ContratPlage;
use App\Entity\ContratRegroupement;
use App\Entity\Produit;
use App\Entity\Site;
use App\Entity\Equipement;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;

/**
 * @method Produit|null find($id, $lockMode = null, $lockVersion = null)
 * @method Produit|null findOneBy(array $criteria, array $orderBy = null)
 * @method Produit[]    findAll()
 * @method Produit[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class ProduitRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Produit::class);
    }

    public function findAllByIndexTypePrestationByType($id_type)
    {
        $qb = $this->createQueryBuilder('o')->innerJoin('o.produit_type', 'pt')->andWhere('pt.id = :id_type')->setParameter('id_type', $id_type);
        $query = $qb->indexBy('o', 'o.designation')->orderBy('o.designation', 'ASC')->getQuery();
        return $query->getResult();
    }
    public function findAllByIndexTypePrestationId()
    {
        $qb = $this->createQueryBuilder('o')->innerJoin('o.produit_type', 'pt')->andWhere('pt.id = 2');
        $query = $qb->indexBy('o', 'o.id')->orderBy('o.designation', 'ASC')->getQuery();
        return $query->getResult();
    }

    public function getConsommablesSite($skip = null, $take = null, $filters = null,  $sort = null) {
        $qb = $this->createQueryBuilder('p')
            ->select("p.id as id, CONCAT(p.id,'-', s.id) as idproduitsite, s.nom as s_nom,p.code as p_code, p.designation as p_designation, p.gtin as p_gtin, pt.emplacement as pt_emplacement, CASE WHEN pt.gere_en_stock =true then 'oui' else 'non' END as pt_gere_en_stock, p.prix_ht as p_prix_ht, string_agg(cast(pp.nom as varchar), ',') as pp_photo")
            ->innerJoin('p.site', 's')
            ->innerJoin('p.produitStockSites', 'pt')
            ->leftJoin('p.produitPhotos', 'pp')
            ->where('p.famille_agi is null')
            ->groupBy('id,idproduitsite, s_nom,p_code, p_designation, p_gtin,pt_emplacement, pt.gere_en_stock,pp.photo', 'p_prix_ht')
        ;

        if ($skip !== null && $take !== null) $qb->setFirstResult( $skip )->setMaxResults( $take );
        if ($filters['AND']) {
            foreach ($filters['AND'] as $filter) {
                $qb->andWhere($filter['filtersVariables']);
                foreach ($filter['parameters'] as $parameter) {
                    $qb->setParameter(':' . $parameter['key'],$parameter['value']);
                }
            }
        }
        if ($sort) {
            $pos = stripos($sort[0]['selector'], '_');
            $sortBy = substr_replace($sort[0]['selector'],'.',$pos,1);
            $order = ($sort[0]['desc']) ? " DESC" : " ASC";
            $qb->orderBy($sortBy, $order);
        } else $qb->orderBy('p.designation');

        $query = $qb->getQuery();
        return $query->getArrayResult();
    }

    public function countConsommablesSite($filters = null) {
        $qb = $this->createQueryBuilder('p')
            ->select('count(p.id)')
            ->innerJoin('p.site', 's')
            ->innerJoin('p.produitStockSites', 'pt')
            ->where('p.famille_agi is null');

        if ($filters['AND']) {
            foreach ($filters['AND'] as $filter) {
                $qb->andWhere($filter['filtersVariables']);
                foreach ($filter['parameters'] as $parameter) {
                    $qb->setParameter(':' . $parameter['key'],$parameter['value']);
                }
            }
        }
        return $qb->getQuery()
            ->getSingleScalarResult();
        ;
    }

    public function getPiecesTablette(Site $site) {
        $qb = $this->createQueryBuilder('p');
        $expr = $qb->expr();
        $qb->innerJoin('App\Entity\ProduitStockSite', 'ps', 'WITH', 'ps.produit = p.id')
            ->innerJoin('p.famille_agi', 'fa')
            ->where("fa.libelle <> 'T35'")->andWhere("ps.visible_tablette = true")->andWhere($expr->in('ps.site',  ':ids'))
            ->setParameter('ids', [$site->getId(), null])
        ;

        $query = $qb->getQuery();
        return $query->getResult();
    }

    public function getConsommablesTablette(Site $site) {
        $qb = $this->createQueryBuilder('p');
        $expr = $qb->expr();
        $qb->innerJoin('App\Entity\ProduitStockSite', 'ps', 'WITH', 'ps.produit = p.id')
            ->innerJoin('p.famille_agi', 'fa')
            ->where("fa.libelle = 'T35'")->andWhere("ps.visible_tablette = true")->andWhere($expr->in('ps.site',  ':ids'))
            ->setParameter('ids', [$site->getId(), null])
        ;

        $query = $qb->getQuery();
        return $query->getResult();
    }

    public function getForfaits(ContratRegroupement $contratRegroupement = null, ContratPlage $contratPlage = null) {
        $qb = $this->createQueryBuilder('p');
        $expr = $qb->expr();

        $qb
            ->select('p.id as id, p.code as code, p.designation as designation, p.gtin as gtin, p.gu_etendue as gu_etendue, pp.prix_ht as prix_ht')
            ->innerJoin('p.produitPrestationTarifs', 'pp')
            ->where("p.produit_type = '2'");

        if($contratPlage == null){
            $qb->andWhere("pp.contrat_plage IS null");
        }else{
            $qb->andWhere("pp.contrat_plage = :contratPlage")
                ->setParameter('contratPlage', $contratPlage);
        }
        if($contratRegroupement == null){
            $qb->andWhere("pp.contrat_regroupement IS null");
        }else{
            $qb->andWhere("pp.contrat_regroupement = :contratRegroupement")
                ->setParameter('contratRegroupement', $contratRegroupement);
        }
        $query = $qb->getQuery();
        return $query->getResult();
    }

    public function getIdLibelle($typeProduit)
    {
        $qb = $this->createQueryBuilder('t')
            ->select('t.designation,t.id, t.id as idproduit_type')
            ->andWhere('t.produit_type = :type_produit')->setParameter('type_produit', $typeProduit)
            ->orderBy('t.designation', 'ASC');
        ;
        return $qb->getQuery()->getArrayResult();
    }


    public function getPiecesChangees(Equipement $equipement){

    }
}