Symfony Profiler

<?php
namespace App\Repository;
use App\Entity\Product;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Common\Persistence\ManagerRegistry;
use Doctrine\ORM\QueryBuilder;
use Doctrine\ORM\Query\ResultSetMapping;
use App\Entity\User;
use App\Service\UserService;
class ProductRepository extends ServiceEntityRepository
{
private $userServ;
public function __construct(ManagerRegistry $registry, UserService $userServ)
{
parent::__construct($registry, Product::class);
$this->userServ = $userServ;
}
public function searchForProducts($search_query, $countOnly = false, $sorting = "popularity", $order = "desc") {
$search_query_htmlentities = htmlentities($search_query);
$search_query_htmlentities = str_replace(" ", "%", $search_query_htmlentities);
$search_query_htmlentities = "%$search_query_htmlentities%";
$search_query = str_replace("’", "%", $search_query);
$search_query = str_replace("'", "%", $search_query);
$search_query = str_replace("-", "%", $search_query);
$search_query = str_replace(" ", "%", $search_query);
$search_query = str_replace("º", "%", $search_query);
$tag_search_query = $search_query;
if (strlen($tag_search_query) > 3) {
$tag_search_query = "%$tag_search_query%";
}
$search_query = "%$search_query%";
$em = $this->getEntityManager();
$conn = $em->getConnection();
$sql = "
SELECT DISTINCT product.id, product.company_id, product.orig_name, product.creation_date,
IF (product.orig_name LIKE ?, 20, IF (product.orig_brand_name LIKE ?, 10, 0))
+ IF (product.orig_brand_name LIKE ?, 10, 0)
+ IF (company.name LIKE ?, 5, 0)
+ IF (tag.name LIKE ?, 5, 0)
AS weight,
( ( (SELECT COUNT(cart_product.id) FROM cart_product WHERE cart_product.product_id = product.id) + 1 ) / 100 ) as total_cart
FROM product
LEFT JOIN company ON company.id = product.company_id
LEFT JOIN tag_product ON tag_product.product_id = product.id
LEFT JOIN tag ON tag.id = tag_product.tag_id
LEFT JOIN pricing_product ON pricing_product.product_id = product.id
LEFT JOIN pricing ON pricing.id = pricing_product.pricing_id
WHERE (
product.orig_name LIKE ?
OR product.orig_brand_name LIKE ?
OR product.orig_description LIKE ?
OR product.list_of_ingredients LIKE ?
OR company.name LIKE ?
OR tag.name LIKE ?
) AND (
product.draft != 1
AND product.available > 0
AND product.is_hri = 0
AND product.is_displayed_in_association_only = 0
AND product.main_image_id IS NOT NULL
AND company.show_publicly = 1
) AND
(
(
product.qty_ready_to_ship > 0
AND product.delivery_type = 0
) OR (
product.delivery_type != 0
)
)
";
if ($sorting == "popularity") {
if (strtolower($order) == "asc") {
$order = "ASC";
} else {
$order = "DESC";
}
$sql .= "
ORDER BY ( `weight` + `total_cart` ) $order
";
}
if ($sorting == "price") {
if (strtolower($order) == "asc") {
$order = "ASC";
} else {
$order = "DESC";
}
$sql .= "
ORDER BY pricing.price $order
";
}
if ($sorting == "date") {
if (strtolower($order) == "asc") {
$order = "ASC";
} else {
$order = "DESC";
}
$sql .= "
ORDER BY product.creation_date $order
";
}
$stmt = $conn->prepare($sql);
$stmt->bindValue(1, $search_query);
$stmt->bindValue(2, $search_query);
$stmt->bindValue(3, $search_query);
$stmt->bindValue(4, $search_query);
$stmt->bindValue(5, $tag_search_query);
$stmt->bindValue(6, $search_query);
$stmt->bindValue(7, $search_query);
$stmt->bindValue(8, $search_query_htmlentities);
$stmt->bindValue(9, $search_query_htmlentities);
$stmt->bindValue(10, $search_query);
$stmt->bindValue(11, $tag_search_query);
$stmt->execute();
$results = $stmt->fetchAll();
$products = [];
foreach($results as $result) {
$product = $this->createQueryBuilder('p')
->leftJoin('p.company', 'company')
->andWhere('p.id = :selected_product')
->setParameter('selected_product', $result["id"])
->getQuery()->getResult();
;
if ($product) {
$products[current($product)->getId()] = current($product);
}
}
if ($countOnly) {
return count($products);
}
return $products;
}
public function searchForProductsSuggestions($search_query) {
$search_query_htmlentities = htmlentities($search_query);
$search_query_htmlentities = str_replace(" ", "%", $search_query_htmlentities);
$search_query_htmlentities = "%$search_query_htmlentities%";
$search_query = str_replace("’", "%", $search_query);
$search_query = str_replace("'", "%", $search_query);
$search_query = str_replace("-", "%", $search_query);
$search_query = str_replace(" ", "%", $search_query);
$search_query = str_replace("º", "%", $search_query);
$tag_search_query = $search_query;
if (strlen($tag_search_query) > 3) {
$tag_search_query = "%$tag_search_query%";
}
$search_query = "%$search_query%";
$em = $this->getEntityManager();
$conn = $em->getConnection();
$sql = "
SELECT DISTINCT product.id, product.company_id, product.orig_name,
IF (product.orig_name LIKE ?, 20, IF (product.orig_brand_name LIKE ?, 10, 0))
+ IF (product.orig_brand_name LIKE ?, 10, 0)
+ IF (company.name LIKE ?, 5, 0)
+ IF (tag.name LIKE ?, 5, 0)
AS weight,
( ( (SELECT COUNT(cart_product.id) FROM cart_product WHERE cart_product.product_id = product.id) + 1 ) / 100 ) as total_cart
FROM product
LEFT JOIN company ON company.id = product.company_id
LEFT JOIN tag_product ON tag_product.product_id = product.id
LEFT JOIN tag ON tag.id = tag_product.tag_id
WHERE (
product.orig_name LIKE ?
OR product.orig_brand_name LIKE ?
OR product.orig_description LIKE ?
OR product.list_of_ingredients LIKE ?
OR company.name LIKE ?
OR tag.name LIKE ?
) AND (
product.draft != 1
AND product.available > 0
AND product.is_hri = 0
AND product.is_displayed_in_association_only = 0
AND product.main_image_id IS NOT NULL
AND company.show_publicly = 1
) AND
(
(
product.qty_ready_to_ship > 0
AND product.delivery_type = 0
) OR (
product.delivery_type != 0
)
)
ORDER BY ( `weight` + `total_cart` ) DESC
";
$stmt = $conn->prepare($sql);
$stmt->bindValue(1, $search_query);
$stmt->bindValue(2, $search_query);
$stmt->bindValue(3, $search_query);
$stmt->bindValue(4, $search_query);
$stmt->bindValue(5, $tag_search_query);
$stmt->bindValue(6, $search_query);
$stmt->bindValue(7, $search_query);
$stmt->bindValue(8, $search_query_htmlentities);
$stmt->bindValue(9, $search_query_htmlentities);
$stmt->bindValue(10, $search_query);
$stmt->bindValue(11, $tag_search_query);
$stmt->execute();
$results = $stmt->fetchAll();
$products = [];
foreach($results as $result) {
$product = $this->createQueryBuilder('p')
->leftJoin('p.company', 'company')
->andWhere('p.id = :selected_product')
->setParameter('selected_product', $result["id"])
->getQuery()->getResult();
;
if ($product) {
$products[current($product)->getId()] = current($product);
}
}
return $products;
//
// $products = $this->createQueryBuilder('p');
//
// $products
// ->leftJoin('p.tags', 't')
// ->leftJoin('p.company', 'c')
// ->andWhere('p.draft != 1')
// ->andWhere('p.available = 1')
// ->andWhere('c.showPublicly = 1')
// ;
//
// if(!$this->userServ->getAssociationUserIsBrowsing()) {
// $products->andWhere('p.isDisplayedInAssociationOnly != true');
// }
//
// $products
// ->andWhere('p.origName LIKE :search_query OR p.origBrandName LIKE :search_query OR t.name LIKE :search_query OR c.name LIKE :search_query')
// ->setParameter('search_query', $search_query)
// ->orderBy('size(p.inCarts)', 'desc')
// // ->groupBy('p.company')
// ;
//
// if (count($products->getQuery()->getResult()) < 5) {
// $products->groupBy('p.company');
// }
//
// return $products->getQuery();
}
/*
* Return all the products that are low in quantity with no reminder sent
* Only fetch those that sold something to assure to get only those needed
*/
public function findWithLowQuantity($limit=30)
{
$qb = $this->createQueryBuilder('p')
->join('p.inCarts', 'c')
->leftJoin('c.cart', 'cart')
->andWhere('cart.isPaid = true')
->andWhere('p.draft != 1')
->andWhere('p.reminderLowQuantitySent = false')
->andWhere('p.DeliveryType = 0')
->andWhere('p.available = 1')
->andWhere('p.isJustInTime = false')
->andWhere('p.qtyReadyToShip < p.minimumInStorage')
->andWhere('p.minimumInStorage != 0');
// todo is it revelant here ?
if(!$this->userServ->getAssociationUserIsBrowsing())
$qb->andWhere('p.isDisplayedInAssociationOnly != true');
$qb->groupBy('p.id')
->orderBy('p.company', 'DESC')
->setMaxResults($limit);
return $qb->getQuery()->execute();
}
/**
* @param $price
* @return Product[]
*/
public function findAllGreaterThanPrice($price): array
{
// automatically knows to select Products
// the "p" is an alias you'll use in the rest of the query
$qb = $this->createQueryBuilder('p')
->andWhere('p.draft != 1')
->andWhere('p.available = 1')
->andWhere('p.price > :price');
if(!$this->userServ->getAssociationUserIsBrowsing())
$qb->andWhere('p.isDisplayedInAssociationOnly = false');
if(true) // later check if user is browsing HRI
$qb->andWhere('p.isConsumer = true');
$qb->setParameter('price', $price)
->orderBy('p.price', 'ASC')
->getQuery();
return $qb->execute();
// to get just one result:
// $product = $qb->setMaxResults(1)->getOneOrNullResult();
}
/*
* Return the total of Product in a category
*/
public function getTotalProducts($companyId, $categoryId=false){
$product = $this->createQueryBuilder('m')
->select('count(m.id)')
->where('m.company = :companyId')
->andWhere('m.draft != 1')
->andWhere('m.available = 1')
->andWhere('m.mainImage IS NOT NULL');
if(!$this->userServ->getAssociationUserIsBrowsing())
$product->andWhere('m.isDisplayedInAssociationOnly = false');
if(true) // later check if user is browsing HRI
$product->andWhere('m.isConsumer = true');
$product->setParameter('companyId', $companyId);
if($categoryId){
$product->andWhere('m.categories = :categoryId')
->setParameter('categoryId', $categoryId);
}
$amount = $product->getQuery()
->getSingleScalarResult();
return $amount;
}
/*
* Return all the categories used
*/
public function getAllUsedCategories($companyId){
$product = $this->createQueryBuilder('c')
->where('c.company = :companyId')
->andWhere('c.draft != 1')
->andWhere('c.available = 1')
->andWhere('c.mainImage IS NOT NULL')
->setParameter('companyId', $companyId)
->groupBy('c.categories')
->orderBy('c.categories', 'DESC')
->getQuery();
//@TODO Need a better way
$results = $product->getResult();
$cats = array();
foreach($results as $res){
$key = $res->getCategories()->getId();
$cats[$key]=$res->getCategories()->getName();
}
return $cats;
}
public function validateBrowsingAssociationInQuery(QueryBuilder $products): QueryBuilder{
if($this->userServ->getAssociationUserIsBrowsing()){
$products
->leftJoin('company.associations', 'associations')
->andWhere('associations = :association')
->setParameter('association', $this->userServ->getAssociationUserIsBrowsing());
//$products->andWhere('p.isDisplayedInAssociationOnly != true');
} else {
$products->andWhere('p.isDisplayedInAssociationOnly = false');
}
return $products;
}
/*
Return limited numbers of products with a specific category
and that are ready for being shown on the front page
- Results must be limited to $maxresults
- Must be in specified category
- Must be Salable()
- Must be ShippedByMaturin()
- (TODO) Maximum of 2 products per company
- (TODO) Must respect: if (!$associationUserIsBrowsing && $product->getIsDisplayedInAssociationOnly())
*/
public function getProductsFromCategoryForFrontpage($category, $maxResults = 10) {
$em = $this->getEntityManager();
$conn = $em->getConnection();
$subCategories = $category->getAllSubCategories();
$subCategoriesIds = [];
foreach($subCategories as $subCategory) {
$subCategoriesIds[] = $subCategory->getId();
}
$subCategoriesIds = implode(",",$subCategoriesIds);
$sql = "
SELECT DISTINCT product.id, product.company_id
FROM product
LEFT JOIN company ON company.id = product.company_id
WHERE (
product.categories_id = ?
OR product.sub_category_id = ?
OR product.categories_id IN (?)
OR product.sub_category_id IN (?)
) AND (
product.draft != 1
AND product.qty_ready_to_ship > 0
AND product.delivery_type = 0
AND product.delivery_mail = 1
AND product.available > 0
AND product.is_hri = 0
AND product.is_displayed_in_association_only = 0
AND product.main_image_id IS NOT NULL
AND company.show_publicly = 1
)
ORDER BY RAND()
LIMIT 4
";
$stmt = $conn->prepare($sql);
$stmt->bindValue(1, $category->getId());
$stmt->bindValue(2, $category->getId());
$stmt->bindValue(3, $subCategoriesIds);
$stmt->bindValue(4, $subCategoriesIds);
$stmt->execute();
$selected_products = $stmt->fetchAll();
$selected_products = array_slice($selected_products, 0, 4);
shuffle($selected_products);
$products = $this->createQueryBuilder('p')
->leftJoin('p.company', 'company')
->addSelect('company')
->leftJoin('p.storages', 'storages')
->addSelect('storages')
->leftJoin('p.pricings', 'pricings')
->addSelect('pricings')
->leftJoin('pricings.products', 'pproduct')
->addSelect('pproduct')
->andWhere('p.id IN (:selected_products)')
->setParameter('selected_products', $selected_products)
;
return $products->getQuery()->getResult();
}
public function getProductsForCategory($category, $sorting = "popularity", $order = "desc") {
$em = $this->getEntityManager();
$conn = $em->getConnection();
$subCategories = $category->getAllSubCategories();
$subCategoriesIds = [];
foreach($subCategories as $subCategory) {
$subCategoriesIds[] = $subCategory->getId();
}
$subCategoriesIds = implode(",",$subCategoriesIds);
$sql = "
SELECT DISTINCT product.id, product.company_id,
( ( (SELECT COUNT(cart_product.id) FROM cart_product WHERE cart_product.product_id = product.id) + 1 ) / 100 ) as total_cart
FROM product
LEFT JOIN company ON company.id = product.company_id
LEFT JOIN pricing_product ON pricing_product.product_id = product.id
LEFT JOIN pricing ON pricing.id = pricing_product.pricing_id
WHERE (
product.categories_id = ?
OR product.sub_category_id = ?
OR product.categories_id IN (?)
OR product.sub_category_id IN (?)
) AND (
product.draft != 1
AND product.available > 0
AND product.is_hri = 0
AND product.is_displayed_in_association_only = 0
AND product.main_image_id IS NOT NULL
AND company.show_publicly = 1
AND (
(
product.qty_ready_to_ship > 0
AND product.delivery_type = 0
) OR
(
product.delivery_type != 0
)
)
)
";
if ($sorting == "popularity") {
if (strtolower($order) == "asc") {
$order = "ASC";
} else {
$order = "DESC";
}
$sql .= "
ORDER BY ( `total_cart` ) $order
";
}
if ($sorting == "price") {
if (strtolower($order) == "asc") {
$order = "ASC";
} else {
$order = "DESC";
}
$sql .= "
ORDER BY pricing.price $order
";
}
if ($sorting == "date") {
if (strtolower($order) == "asc") {
$order = "ASC";
} else {
$order = "DESC";
}
$sql .= "
ORDER BY product.creation_date $order
";
}
$stmt = $conn->prepare($sql);
$stmt->bindValue(1, $category->getId());
$stmt->bindValue(2, $category->getId());
$stmt->bindValue(3, $subCategoriesIds);
$stmt->bindValue(4, $subCategoriesIds);
$stmt->execute();
$results = $stmt->fetchAll();
$products = [];
foreach($results as $result) {
$product = $this->createQueryBuilder('p')
->leftJoin('p.company', 'company')
->andWhere('p.id = :selected_product')
->setParameter('selected_product', $result["id"])
->getQuery()->getResult();
;
if ($product) {
$products[current($product)->getId()] = current($product);
}
}
return $products;
}
public function getProductsSponsored($category, $sorting = "popularity", $order = "desc") {
$em = $this->getEntityManager();
$conn = $em->getConnection();
$sql = "
SELECT DISTINCT product.id, product.company_id,
( ( (SELECT COUNT(cart_product.id) FROM cart_product WHERE cart_product.product_id = product.id) + 1 ) / 100 ) as total_cart
FROM product
LEFT JOIN company ON company.id = product.company_id
LEFT JOIN pricing_product ON pricing_product.product_id = product.id
LEFT JOIN pricing ON pricing.id = pricing_product.pricing_id
LEFT JOIN product_sponsored ON product_sponsored.product_id = product.id
LEFT JOIN product_sponsored_category_product_sponsored ON product_sponsored_category_product_sponsored.product_sponsored_id = product_sponsored.id
LEFT JOIN product_sponsored_category ON product_sponsored_category.id = product_sponsored_category_product_sponsored.product_sponsored_category_id
WHERE (
product_sponsored_category.url = ?
AND product_sponsored.from_date <= ?
AND product_sponsored.to_date >= ?
AND product_sponsored.active = 1
) AND (
product.draft != 1
AND product.available > 0
AND company.show_publicly = 1
AND (
(
product.qty_ready_to_ship > 0
AND product.delivery_type = 0
) OR
(
product.delivery_type != 0
)
)
)
";
if ($sorting == "popularity") {
if (strtolower($order) == "asc") {
$order = "ASC";
} else {
$order = "DESC";
}
$sql .= "
ORDER BY ( `total_cart` ) $order
";
}
if ($sorting == "price") {
if (strtolower($order) == "asc") {
$order = "ASC";
} else {
$order = "DESC";
}
$sql .= "
ORDER BY pricing.price $order
";
}
if ($sorting == "date") {
if (strtolower($order) == "asc") {
$order = "ASC";
} else {
$order = "DESC";
}
$sql .= "
ORDER BY product.creation_date $order
";
}
$stmt = $conn->prepare($sql);
$now = new \DateTime('now');
$stmt->bindValue(1, $category->getUrl());
$stmt->bindValue(2, $now->format("Y-m-d H:i:s"));
$stmt->bindValue(3, $now->format("Y-m-d H:i:s"));
$stmt->execute();
$results = $stmt->fetchAll();
$products = [];
foreach($results as $result) {
$product = $this->createQueryBuilder('p')
->leftJoin('p.company', 'company')
->andWhere('p.id = :selected_product')
->setParameter('selected_product', $result["id"])
->getQuery()->getResult();
;
if ($product) {
$products[current($product)->getId()] = current($product);
}
}
return $products;
}
public function getProdCategory($category, $sorting = "popularity", $order = "desc"){
$products = $this->createQueryBuilder('p')
->Select('p.id AS id','company.id AS selected_company')
->leftJoin('p.company', 'company')
->leftJoin('p.pricings', 'pricing')
->andWhere('p.categories = :category')
->OrWhere('p.subCategory = :category')
->OrWhere('p.subCategory IN (:subCategory)')
->setParameter('category', $category)
->setParameter('subCategory', $category->getAllSubCategories())
->andWhere('p.draft != 1')
->andWhere('p.mainImage IS NOT NULL')
->andWhere('p.available = 1')
->andWhere('p.isDisplayedInAssociationOnly = 0')
->andWhere('p.isHri = 0')
->andWhere('p.isConsumer = true')
->andWhere('company.showPublicly = true')
->andWhere('p.qtyReadyToShip > 0');
if ($sorting == "popularity") {
if (strtolower($order) == "asc") {
$order = "ASC";
} else {
$order = "DESC";
}
$sorting = 'size(p.inCarts)';
}
if ($sorting == "price") {
if (strtolower($order) == "asc") {
$order = "ASC";
} else {
$order = "DESC";
}
$sorting = "pricing.price";
}
if ($sorting == "date") {
if (strtolower($order) == "asc") {
$order = "ASC";
} else {
$order = "DESC";
}
$sorting ="p.creationDate";
}
$products->orderBy( $sorting, $order );
$results = $products->getQuery()->getResult();
$prod= [];
foreach($results as $result) {
$product = $this->createQueryBuilder('p')
->leftJoin('p.company', 'company')
->andWhere('p.id = :selected_product')
->setParameter('selected_product', $result['id'])
->getQuery()->getResult();
;
if ($product) {
$prod[current($product)->getId()] = current($product);
}
}
return $prod;
}
// récuperer le placement pour ensuite l'organiser par popularité
public function getcategoriesByPlacement($category, $sorting, $order){
$products = $this->createQueryBuilder('p')
->Select('p.id AS id','company.id AS selected_company','size(p.inCarts) AS panier')
->leftJoin('p.company', 'company')
->leftJoin('p.subCategory', 'categ')
->leftJoin('categ.subCategory', 'categSub')
->addSelect('categ.id AS cateId,categ.placement AS firstnameplacement, categ.name As firstname ,categSub.placement AS secondplacement, categSub.name As secondCateg')
->leftJoin('p.pricings', 'pricing')
->andWhere('p.categories = :category')
->OrWhere('p.subCategory = :category')
->OrWhere('p.subCategory IN (:subCategory)')
->setParameter('category', $category)
->setParameter('subCategory', $category->getAllSubCategories())
->andWhere('p.draft != 1')
->andWhere('p.mainImage IS NOT NULL')
->andWhere('p.available = 1')
->andWhere('p.isDisplayedInAssociationOnly = 0')
->andWhere('categ.placement > 0')
->andWhere('categSub.placement > 0')
->andWhere('p.isHri = 0')
->andWhere('p.isConsumer = true')
->andWhere('company.showPublicly = true')
->andWhere('p.qtyReadyToShip > 0');
if($this->userServ->getAssociationUserIsBrowsing()){
$urlnameAssociation = $this->userServ->getAssociationUserIsBrowsing()->getUrlName();
$products->leftJoin('company.associations', 'association')
->andWhere('association.urlName = :urlnameAssociation')
->setParameter('urlnameAssociation', $urlnameAssociation);
}
if ($sorting != null) {
if ($sorting == "popularity") {
if (strtolower($order) == "asc") {
$order = "ASC";
} else {
$order = "DESC";
}
$sorting = 'size(p.inCarts)';
}
if ($sorting == "price") {
if (strtolower($order) == "asc") {
$order = "ASC";
} else {
$order = "DESC";
}
$sorting = "pricing.price";
}
if ($sorting == "date") {
if (strtolower($order) == "asc") {
$order = "ASC";
} else {
$order = "DESC";
}
$sorting ="p.creationDate";
}
$products->orderBy( $sorting , $order);
}else{
$products->orderBy( 'categ.placement');
}
$results = $products->getQuery()->getResult();
$prod= [];
foreach($results as $result) {
$product = $this->createQueryBuilder('p')
->leftJoin('p.company', 'company')
->andWhere('p.id = :selected_product')
->setParameter('selected_product', $result['id'])
->getQuery()->getResult();
;
if ($product) {
$prod[current($product)->getId()] = current($product);
}
}
// dd($categFinal);
return $prod;
}
public function getProductsForAllCategory($sorting = "popularity", $order = "desc") {
$em = $this->getEntityManager();
$conn = $em->getConnection();
$sql = "
SELECT DISTINCT product.id, product.company_id,
( ( (SELECT COUNT(cart_product.id) FROM cart_product WHERE cart_product.product_id = product.id) + 1 ) / 100 ) as total_cart
FROM product
LEFT JOIN company ON company.id = product.company_id
LEFT JOIN pricing_product ON pricing_product.product_id = product.id
LEFT JOIN pricing ON pricing.id = pricing_product.pricing_id
WHERE (
product.draft != 1
AND product.available > 0
AND product.is_hri = 0
AND product.is_displayed_in_association_only = 0
AND product.main_image_id IS NOT NULL
AND company.show_publicly = 1
AND (
(
product.qty_ready_to_ship > 0
AND product.delivery_type = 0
) OR
(
product.delivery_type != 0
)
)
)
";
if ($sorting == "popularity") {
if (strtolower($order) == "asc") {
$order = "ASC";
} else {
$order = "DESC";
}
$sql .= "
ORDER BY ( `total_cart` ) $order
";
}
if ($sorting == "price") {
if (strtolower($order) == "asc") {
$order = "ASC";
} else {
$order = "DESC";
}
$sql .= "
ORDER BY pricing.price $order
";
}
if ($sorting == "date") {
if (strtolower($order) == "asc") {
$order = "ASC";
} else {
$order = "DESC";
}
$sql .= "
ORDER BY product.creation_date $order
";
}
$stmt = $conn->prepare($sql);
$stmt->execute();
$results = $stmt->fetchAll();
$products = [];
foreach($results as $result) {
$product = $this->createQueryBuilder('p')
->leftJoin('p.company', 'company')
->andWhere('p.id = :selected_product')
->setParameter('selected_product', $result["id"])
->getQuery()->getResult();
;
if ($product) {
$products[current($product)->getId()] = current($product);
}
}
return $products;
}
public function getProductsFromCategory($category, $results = true){
$products = $this->createQueryBuilder('p')
->leftJoin('p.company', 'company')
->addSelect('company')
->leftJoin('p.storages', 'storages')
->addSelect('storages')
->leftJoin('p.pricings', 'pricing')
->andWhere('p.categories = :category')
->OrWhere('p.subCategory = :category')
->OrWhere('p.subCategory IN (:subCategory)')
->setParameter('category', $category)
->setParameter('subCategory', $category->getAllSubCategories())
->andWhere('p.draft != 1')
->andWhere('p.mainImage IS NOT NULL')
->andWhere('p.available = 1')
->andWhere('p.isHri = 0')
->andWhere('company.showPublicly = true')
->andWhere('p.qtyReadyToShip > 0');
$products = $this->validateBrowsingAssociationInQuery($products);
if(true) // later check if user is browsing HRI
$products->andWhere('p.isConsumer = true');
$products->orderBy('size(p.inCarts)', "desc");
$products->groupBy('p.id');
if($results)
return $products->getQuery()->getResult();
else
return $products;
}
/*
* Return all products
*/
public function getSalableProductsWithNoDeal($returnResults = true){
$products = $this->createQueryBuilder('p')
->addSelect('p')
->leftJoin('p.pricings', 'pricings')
->addSelect('pricings')
->leftJoin('pricings.products', 'pproduct')
->addSelect('pproduct')
->where('p.draft != 1')
->leftJoin('p.company', 'company')
->andWhere('company.showPublicly = true')
->andWhere('p.mainImage IS NOT NULL')
->andWhere('p.available = 1')
->leftJoin('p.deal', 'deal')
->addSelect('deal')
->leftJoin('p.discountPricings', 'discount')
->addSelect('discount')
->andWhere('deal IS NULL');
if(!$this->userServ->getAssociationUserIsBrowsing())
$products->andWhere('p.isDisplayedInAssociationOnly != true');
if(true) // later check if user is browsing HRI
$products->andWhere('p.isConsumer = true');
$products->orderBy('p.qtyReadyToShip', 'DESC');
if($returnResults)
return $products->getQuery()->getResult();
else
return $products;
}
/*
* Return all products
* Called by the route "allCategory",
*/
public function getAdminProducts($returnResults = true, $available=true ){
$products = $this->createQueryBuilder('p')
->leftJoin('p.pricings', 'pricings')
->addSelect('pricings')
->leftJoin('pricings.products', 'pproduct')
->addSelect('pproduct')
->where('p.draft != 1')
->leftJoin('p.company', 'company')
->andWhere('p.mainImage IS NOT NULL');
if($available)
$products->andWhere('p.available = 1');
if(!$this->userServ->getAssociationUserIsBrowsing())
$products->andWhere('p.isDisplayedInAssociationOnly != true');
if(true) // later check if user is browsing HRI
$products->andWhere('p.isConsumer = true');
$products->orderBy('size(p.inCarts)', 'desc')
->groupBy('p.id')
;
if($returnResults)
return $products->getQuery()->getResult();
else
return $products;
}
/*
* Return all products
* Called by the route "allCategory",
*/
public function getSalableProducts($returnResults = true, $available=true ){
$products = $this->createQueryBuilder('p')
->leftJoin('p.pricings', 'pricings')
->addSelect('pricings')
->leftJoin('pricings.products', 'pproduct')
->addSelect('pproduct')
->where('p.draft != 1')
->leftJoin('p.company', 'company')
->andWhere('company.showPublicly = true')
->andWhere('p.mainImage IS NOT NULL');
if($available){
$products->andWhere('p.available = 1');
}
$products = $this->validateBrowsingAssociationInQuery($products);
if(true) // later check if user is browsing HRI
$products->andWhere('p.isConsumer = true');
$products->orderBy('size(p.inCarts)', 'desc')
->groupBy('p.id')
;
if($returnResults)
return $products->getQuery()->getResult();
else
return $products;
}
/*
* Return amount of salable products
*/
public function countSalableProducts(): int {
$amount = $this->createQueryBuilder('p')
->select('count(p.id)')
->andWhere('p.draft != 1')
->andWhere('p.available = 1')
->andWhere('p.mainImage IS NOT NULL');
if(!$this->userServ->getAssociationUserIsBrowsing())
$amount->andWhere('p.isDisplayedInAssociationOnly != true');
if(true) // later check if user is browsing HRI
$amount->andWhere('p.isConsumer = true');
return (int)$amount->getQuery()->getSingleScalarResult();
}
/*
* Return the total of Product in a category
*/
public function getTotalProductsFromCategory($category){
$amount = $this->createQueryBuilder('p')
->select('count(p.id)')
->where('p.categories = :category')
->setParameter('category', $category)
->OrWhere('p.subCategory IN (:subCategory)')
->setParameter('subCategory', $category->getAllSubCategories())
->andWhere('p.draft != 1')
->andWhere('p.available = 1')
->andWhere('p.mainImage IS NOT NULL');
if(!$this->userServ->getAssociationUserIsBrowsing())
$amount->andWhere('p.isDisplayedInAssociationOnly != true');
if(true) // later check if user is browsing HRI
$amount->andWhere('p.isConsumer = true');
$amount->getQuery()
->getSingleScalarResult();
return $amount;
}
public function countProductsInCategory($category) {
$count = $this->createQueryBuilder('p')
->select('count(p.id)')
->where('p.categories IN (:category)')
->setParameter('category', $category)
->OrWhere('p.subCategory IN (:subCategory)')
->setParameter('subCategory', [$category, $category->getAllSubCategories()])
->andWhere('p.draft != 1')
->andWhere('p.available = 1')
->andWhere('p.mainImage IS NOT NULL')
->getQuery()->getSingleScalarResult();
return $count;
}
/*
* Look products with certain criteria
*/
public function getProductsFromTags($tags=false){
$products = $this->createQueryBuilder('t');
$results = $products
->where('t.draft != 1')
->andWhere('t.available = 1')
->andWhere('t.mainImage IS NOT NULL')
->leftJoin('t.company', 'company')
->andWhere('company.showPublicly = true')
->andWhere('t.qtyReadyToShip > 1');
if(!$this->userServ->getAssociationUserIsBrowsing())
$results->andWhere('t.isDisplayedInAssociationOnly != true');
if(true) // later check if user is browsing HRI
$results->andWhere('t.isConsumer = true');
$results = $results->setMaxResults(20)
->orderBy('RAND()')
->getQuery()->getResult();
if($results)
shuffle($results);
return $results;
}
/*
* Return Products based on a user past 'likes'
* todo: BASED ON LIKES ? mmmmh... not shure..
*/
public function getProductsForUser($user){
$products = $this->createQueryBuilder('p');
$results = $products
->where('p.draft != 1')
->leftJoin('p.company', 'company');
$results = $this->validateBrowsingAssociationInQuery($results);
if(true) // later check if user is browsing HRI
$products->andWhere('p.isConsumer = true');
$results->andWhere('company.showPublicly = true')
->andWhere('p.available = 1')
->andWhere('p.mainImage IS NOT NULL')
->andWhere('p.qtyReadyToShip > 1');
$results = $results
->setMaxResults(20)
->orderBy('RAND()')
->getQuery()
->getResult();
if($results)
shuffle($results);
return $results;
}
/*
* Return Products based on a user past 'likes'
*/
public function getProductsManagedByUser($user=false){
$products = $this->createQueryBuilder('t');
$results = $products
->where('t.draft != 1')
->andWhere('t.available = 1')
->andWhere('t.mainImage IS NOT NULL');
if(!$this->userServ->getAssociationUserIsBrowsing())
$results->andWhere('t.isDisplayedInAssociationOnly != true');
if(true) // later check if user is browsing HRI
$results->andWhere('t.isConsumer = true');
$results
->getQuery()
->getResult();
if($results)
shuffle($results);
return $results;
}
/*
* Return the list a products the users can book inventory storage for
*/
public function getInventoryProducts(User $user=null){
if(empty($user))
$user = $this->userServ->getUser();
$companies = $user->getCompanies();
$products = $this->createQueryBuilder('p')
->join('p.company', 'c')
->where('p.draft != 1')
->andWhere('p.company IN (:companies)')
->setParameter('companies', $companies)
;
if(!$this->userServ->getAssociationUserIsBrowsing())
$products->andWhere('p.isDisplayedInAssociationOnly != true');
if(true) // later check if user is browsing HRI
$products->andWhere('p.isConsumer = true');
return $products;
}
/*
* Return Products of a Company'
*/
public function findProductsForReplenishmentByCompany($companyId, $noFridge = true){
$products = $this->createQueryBuilder('t');
$products
->where('t.draft != 1')
->andWhere('t.company = :companyId')
->andWhere('t.productDelete = 0')
->setParameter('companyId', $companyId)
->andWhere('t.mainImage IS NOT NULL')
;
if($noFridge){
$products->andWhere('t.conservation=7');
;
}
if(!$this->userServ->getAssociationUserIsBrowsing())
$products->andWhere('t.isDisplayedInAssociationOnly != true');
if(true) // later check if user is browsing HRI
$products->andWhere('t.isConsumer = true');
return $products;
}
/*
* Return Products of a Company'
*/
public function getProductsByCompanyId($companyId, $sorting=null, $order=null, $category=false , $all=false, $results = true, $available=true){
$products = $this->createQueryBuilder('t');
$products
->where('t.draft != 1')
->leftJoin('t.pricings', 'pricing')
->leftJoin('t.subCategory', 'categ')
->leftJoin('categ.subCategory', 'categSub')
->andWhere('t.company = :companyId')
->setParameter('companyId', $companyId);
if ($sorting != null) {
if ($sorting == "popularity") {
if (strtolower($order) == "asc") {
$order = "ASC";
} else {
$order = "DESC";
}
$sorting = 'size(t.inCarts)';
}
if ($sorting == "price") {
if (strtolower($order) == "asc") {
$order = "ASC";
} else {
$order = "DESC";
}
$sorting = "pricing.price";
}
if ($sorting == "date") {
if (strtolower($order) == "asc") {
$order = "ASC";
} else {
$order = "DESC";
}
$sorting ="t.creationDate";
}
$products->orderBy( $sorting , $order);
}
if ($category != false){
$products->andWhere('t.categories = :category')
->setParameter('category', $category);
}
if(!$all){
$products
->andWhere('t.mainImage IS NOT NULL');
if($available)
$products->andWhere('t.available = 1');
}
if(!$this->userServ->getAssociationUserIsBrowsing())
$products->andWhere('t.isDisplayedInAssociationOnly != true');
if(true) // later check if user is browsing HRI
$products->andWhere('t.isConsumer = true');
if($results)
return $products->getQuery()->getResult();
else
return $products;
}
/*
* Get Products for Searching
*/
public function getBaseProductsForSearching(){
$products = $this->createQueryBuilder('p')
->leftJoin('p.company', 'company')
->addSelect('company')
->leftJoin('p.storages', 'storages')
->addSelect('storages')
->leftJoin('p.pricings', 'pricings')
->addSelect('pricings')
->leftJoin('pricings.products', 'pproduct')
->addSelect('pproduct')
->where('p.draft != 1')
->andWhere('p.mainImage IS NOT NULL')
->andWhere('p.available = 1');
$products = $this->validateBrowsingAssociationInQuery($products);
if(true) // later check if user is browsing HRI
$products->andWhere('p.isConsumer = true');
$products->orderBy('p.qtyReadyToShip', 'DESC');
return $products;
}
/*
* Return products after filtering
*/
public function findByFilters($filters, $startAt=false, $limit = false, $countOnly = false){
$pricing = [];
$products = $this->createQueryBuilder('p');
if($countOnly)
$products->select('count(p.id)');
$products
->where('p.draft != 1')
->leftJoin('p.company', 'company')
->andWhere('company.showPublicly = true')
->andWhere('p.mainImage IS NOT NULL')
->andWhere('p.available = 1');
foreach($filters as $filter){
$id = $filter->id;
switch(strtolower($filter->type)){
case 'shipping':
if($id)
$products->andWhere('p.deliveryType = 1');
else
$products->andWhere('p.deliveryType = 0');
break;
case 'category':
$products
->andWhere('p.categories= :categoryId OR p.subCategory= :categoryId')
->setParameter('categoryId', $id);
break;
case 'price':
if(empty($pricing))
$products->leftJoin('p.pricings', 'pricing');
//Because of the multiple here, we need to do a query build
switch($id){
case '10';
$pricing[] = 'pricing.price < 10';
break;
case '10-25';
$pricing[] = '(pricing.price >= 10 and pricing.price < 25)';
break;
case '25-50';
$pricing[] = '(pricing.price >= 25 and pricing.price < 50)';
break;
case '50';
$pricing[] = 'pricing.price >= 50';
break;
}
break;
case 'conservation':
$products
->andWhere('p.conservation = :conservationId')
->setParameter('conservationId', $id);
break;
case 'region':
$products
->andWhere('p.region = :regionId')
->setParameter('regionId', $id);
break;
}
}
if(!empty($pricing) && count($pricing) > 0){
if(count($pricing) == 1){
$products->andWhere(current($pricing));
}else{
$query = implode(' OR ', $pricing);
$products->andWhere($query);
}
}
if(!$this->userServ->getAssociationUserIsBrowsing())
$products->andWhere('p.isDisplayedInAssociationOnly != true');
if(true) // later check if user is browsing HRI
$products->andWhere('p.isConsumer = true');
if($countOnly)
return $products->getQuery()->getSingleScalarResult();
else
return $products->getQuery()->getResult();
}
/*
* Used to convert the old products bundle to the new Product Boxes
*/
public function findProductsWithOldBundles()
{
$products = $this->createQueryBuilder('p')
->addSelect('p')
->leftJoin('p.productsInBundle', 'products')
->addSelect('products')
->andWhere('products IS NOT NULL')
;
return $products->getQuery()->getResult();
}
public function createDefaultQueryBuilder() : QueryBuilder
{
return $this->createQueryBuilder('product')
// pricings
->leftJoin('product.pricings', 'pricings')
->addSelect('pricings')
// storages
->leftJoin('product.storages', 'storages')
->addSelect('storages')
// images
->leftJoin('product.mainImage', 'mainImage')
->addSelect('mainImage')
// deals
->leftJoin('product.deal', 'deal')
->addSelect('deal')
// company
->join('product.company', 'company')
->addSelect('company')
// company deposit infos
// TODO : find why it's reauired
->leftJoin('company.depositInfo', 'depositInfo')
->addSelect('depositInfo')
// default shipping
->leftJoin('company.defaultShipping', 'defaultShipping')
->addSelect('defaultShipping')
// association
->join('company.associations', 'associations')
->addSelect('associations');
}
public function findAllAssociationProducts(int $associationId){
return $this->createDefaultQueryBuilder()
->where('product.draft = false')
->andWhere('associations = :association')
->setParameter('association', $associationId)
->orderBy('product.id', 'DESC')
->getQuery()
->getResult();
}
public function getOneProductFromCategory($category, $maxResults = 1){
$products = $this->createQueryBuilder('p')
->leftJoin('p.company', 'company')
->addSelect('company')
->leftJoin('p.storages', 'storages')
->addSelect('storages')
->leftJoin('p.pricings', 'pricing')
->andWhere('p.categories = :category')
->OrWhere('p.subCategory = :category')
->OrWhere('p.subCategory IN (:subCategory)')
->setParameter('category', $category)
->setParameter('subCategory', $category->getAllSubCategories())
->andWhere('p.draft != 1')
->andWhere('p.mainImage IS NOT NULL')
->andWhere('p.DeliveryType = 0')
->andWhere('p.available = 1')
->andWhere('company.showPublicly = true')
->andWhere('p.qtyReadyToShip > 0');
$products = $this->validateBrowsingAssociationInQuery($products);
if(true) // later check if user is browsing HRI
$products->andWhere('p.isConsumer = true');
// $products->orderBy('size(p.inCarts)', "desc");
$products->groupBy('p.id');
return $products->setMaxResults($maxResults)->orderBy('RAND()')->getQuery()->getResult();
}
public function getProductsWithMaturinUpc(){
$products = $this->createQueryBuilder('p')
->andWhere('p.maturinUpc IS NOT NULL');
return $products->getQuery()->getResult();
}
public function exportProductsColabor(){
$listProductsColabor = $this->createQueryBuilder('p')
->leftJoin('p.company', 'company')
->leftJoin('p.pricings', 'pricing')
->leftJoin('p.categories', 'category')
->Where('category.id != 47')
->andWhere('category.id != 48')
//->andWhere('company.id != 76')
//->andWhere('company.id != 146')
//->andWhere('company.id != 165')
//->andWhere('company.id != 170')
//->andWhere('company.id != 207')
//->andWhere('company.id != 219')
//->andWhere('company.id != 222')
//->andWhere('company.id != 251')
->andWhere('company.exportColabor = true')
->andWhere('p.containsAlcohol = false')
->andWhere('company.showPublicly = true')
->andWhere('pricing.price !=0')
->orderBy('p.id', 'ASC');
return $listProductsColabor->getQuery()->getResult();
}
public function productsCTAQ(){
$productsCTAQ = $this->createQueryBuilder('p')
->leftJoin('p.company', 'company')
->Where('p.categories != 47')
->OrWhere('p.categories != 351')
->andWhere('p.available = true')
->andWhere('p.draft = false')
->andWhere('p.isDisplayedInAssociationOnly = false')
->andWhere('company.showPublicly = true')
->orderBy('p.id', 'ASC');
return $productsCTAQ->getQuery()->getResult();
}
public function LastInvoiceBeforeChange($idProduct, $dateMax){
/*$em = $this->getEntityManager();
$conn = $em->getConnection();
$sql = "SELECT cart_product.pricings_used "
."FROM product "
."RIGHT JOIN cart_product ON product.id=cart_product.product_id "
."WHERE product.id=".$idProduct." "
."AND cart_product.date_created < '2021-04-03 00:00:00' "
."AND cart_product.is_shipped = 1 "
."ORDER BY cart_product.date_created "
."DESC LIMIT 1";
$stmt = $conn->prepare($sql);
$stmt->execute();
$results = $stmt->fetchAll();*/
$lastInvoiceBeforeChange = $this->createQueryBuilder('p')
->join('p.inCarts', 'c')
->addSelect('c.pricingsUsed')
->andWhere('c.dateCreated < :dateMax')
->andWhere('c.isShipped = true')
->andwhere('p.id = :idProduct')
->setParameter('idProduct', $idProduct)
->setParameter('dateMax', $dateMax)
->orderBy('c.dateCreated', 'DESC')
->setMaxResults(1);
return $lastInvoiceBeforeChange->getQuery()->getResult();
}
public function getQuantityProductPreOrder($prod){
$products = $this->createQueryBuilder('p')
->Select('c.quantity as qty, cart.preOrderNow')
->innerJoin('p.inCarts', 'c')
->innerJoin('c.cart', 'cart')
->where('cart.preOrderNow = true')
->andWhere('cart.deliveryChoiceDate IS NOT NULL')
->andWhere('cart.isPaid = true')
->andwhere('c.product = :idProduct')
->setParameter('idProduct', $prod->getId());
$results = $products->getQuery()
->getResult();
$totalSellPreOrder=0;
foreach ($results as $sellbyQuantity) {
$totalSellPreOrder += $sellbyQuantity['qty'];
}
return $totalSellPreOrder;
}
public function exportProductsUPA(){
$listProductsUPA = $this->createQueryBuilder('p')
->leftJoin('p.company', 'company')
->leftJoin('p.pricings', 'pricing')
->leftJoin('company.associations', 'assoc')
->Where('assoc.id = 27')
->andWhere('company.showPublicly = true')
->andWhere('pricing.price > 0')
->orderBy('p.id', 'ASC');
return $listProductsUPA->getQuery()->getResult();
}
// récuperer les produits qui coorespondent au filtre
public function getcategoriesByPlacementWithFilter($category, $sorting, $order, $sub1, $region = false){
$products = $this->createQueryBuilder('p')
->Select('p.id AS id','company.id AS selected_company','size(p.inCarts) AS panier')
->leftJoin('p.company', 'company')
->leftJoin('company.mainLocation', 'location')
->leftJoin('location.region', 'region')
->leftJoin('p.diets', 'diet')
->leftJoin('p.subCategory', 'categ')
->leftJoin('p.certifications', 'certif')
->leftJoin('p.conservation', 'conservat')
->leftJoin('categ.subCategory', 'categSub')
->addSelect('categ.id AS cateId,categ.placement AS firstnameplacement, categ.name As firstname ,categSub.placement AS secondplacement, categSub.name As secondCateg')
->leftJoin('p.pricings', 'pricing')
->andWhere('p.categories = :category')
->OrWhere('p.subCategory = :category')
->OrWhere('p.subCategory IN (:subCategory)')
->setParameter('category', $category)
->setParameter('subCategory', $category->getAllSubCategories())
->andWhere('p.draft != 1')
->andWhere('p.mainImage IS NOT NULL')
->andWhere('p.available = 1')
->andWhere('p.isDisplayedInAssociationOnly = 0')
->andWhere('categ.placement > 0')
->andWhere('categSub.placement > 0')
->andWhere('p.isHri = 0')
->andWhere('p.isConsumer = true')
->andWhere('company.showPublicly = true')
->andWhere('p.qtyReadyToShip > 0');
if($this->userServ->getAssociationUserIsBrowsing()){
$urlnameAssociation = $this->userServ->getAssociationUserIsBrowsing()->getUrlName();
$products->leftJoin('company.associations', 'association')
->andWhere('association.urlName = :urlnameAssociation')
->setParameter('urlnameAssociation', $urlnameAssociation);
}
if ($sub1 != null) {
switch ($sub1) {
case 'LivraisonMaturin':
$products->andWhere('p.DeliveryType = 0');
break;
case 'LivraisonIndependante':
$products->andWhere('p.DeliveryType = 1');
break;
case 'EnSolde':
$products->andWhere('pricing.isADiscount = true');
break;
case 'Moins10':
$products->andWhere('pricing.price <= 9.99');
break;
case 'price10-25':
$products->andWhere('pricing.price BETWEEN 10 AND 24.99');
break;
case 'price25-50':
$products->andWhere('pricing.price BETWEEN 25 AND 49.99');
break;
case 'Plus50':
$products->andWhere('pricing.price > 50');
break;
case 'Réfrigéré':
$products->andWhere('conservat.id = 5');
break;
case 'Congelé':
$products->andWhere('conservat.id = 6');
break;
case 'Frais (tablette)':
$products->andWhere('conservat.id = 7');
break;
case 'AlimentsQuebec':
$products->andWhere('p.alimentsDuQuebec = 1');;
break;
case 'PrixLaureat':
$products->andWhere('p.isPrixLaureat = true');
break;
case 'Biologique':
$products->andWhere('certif.category = :organic');
$products->setParameter('organic', 'organic');
break;
case 'Sans_Gluten':
$products->andWhere('diet.id = 3');
break;
case 'Vegane':
$products->andWhere('diet.id = 2');
break;
case 'Sans_Lactose':
$products->andWhere('diet.id = 5');
break;
case 'Sans_Noix':
$products->andWhere('diet.id = 6');
break;
case 'Paleo':
$products->andWhere('diet.id = 4');
break;
}
}
if (is_int($region)) {
$products->andWhere('region.id = :region');
$products->setParameter('region', $region);
}
$products->orderBy('size(p.inCarts)','DESC');
$results = $products->getQuery()->getResult();
$prod= [];
foreach($results as $result) {
$product = $this->createQueryBuilder('p')
->leftJoin('p.company', 'company')
->andWhere('p.id = :selected_product')
->setParameter('selected_product', $result['id'])
->getQuery()->getResult();
;
if ($product) {
$prod[current($product)->getId()] = current($product);
}
}
// dd($categFinal);
return $prod;
}
}

src/Repository/ProductRepository.php line 1308