<?php
namespace App\Trinity\WebshopBundle\Repository;
use Doctrine\ORM\EntityRepository;
use App\Trinity\WebshopBundle\Entity\Category;
use App\CmsBundle\Classes\SqlFormatter;
/**
* ProductRepository
*/
class ProductRepository extends EntityRepository
{
public function filter($doCount = false, $Webshop = null, $offset = 0, $limit = 0, $filter = []){
$em = $this->getEntityManager();
$q = (isset($filter['q']) && !empty($filter['q']) ? $filter['q'] : null);
$type = (isset($filter['type']) && $filter['type'] != '' ? $filter['type'] : null);
$status = (isset($filter['status']) && $filter['status'] != '' ? $filter['status'] : null);
$featured = (isset($filter['featured']) && $filter['featured'] != '' ? $filter['featured'] : null);
$category = (isset($filter['category']) && $filter['category'] != '' ? $filter['category'] : null);
$tax = (isset($filter['tax']) &&!empty($filter['tax']) && $filter['tax'] != '' ? $filter['tax'] : null);
$stock = (isset($filter['stock']) && $filter['stock'] != '' ? $filter['stock'] : null);
$sale = (isset($filter['sale']) && $filter['sale'] != '' ? $filter['sale'] : null);
$sales = (isset($filter['sales']) && $filter['sales'] != '' ? $filter['sales'] : null);
$media = (isset($filter['media']) && $filter['media'] != '' ? $filter['media'] : null);
$visibility = (isset($filter['visibility']) && $filter['visibility'] != '' ? $filter['visibility'] : null);
$sort = (!empty($filter['sort']) && $filter['sort'] != '' ? $filter['sort'] : 'p.label');
$order = (!empty($filter['order']) && $filter['order'] == 'desc' ? 'desc' : 'asc');
// Force $q to array
if(!is_array($q)){
$q = [$q];
}
$partDone = [];
$queries = [];
foreach($q as $part){
// Cleanup
$part = preg_replace('/^(.*?)\'.*?$/', '$1', $part);
$part = str_replace('\'', '_', $part);
if(empty($part) || strlen($part) <= 1 || in_array($part, $partDone)) continue;
$partDone[] = $part;
$queries[] = "
(
p.label LIKE '%" . $part . "%' OR
p.ean LIKE '%" . $part . "%' OR
p.sku LIKE '%" . $part . "%' OR
p.number LIKE '%" . $part . "%'
)
";
}
if($doCount && empty($category) && $stock == null && ($type == null) && $tax == null && $sale == null && $sales == null && $visibility == null && $status == null && $featured == null && $media == null && empty($queries)){
$sql = "
SELECT count(p.id)
FROM TrinityWebshopBundle:Product p
WHERE 1 = 1
" . ($Webshop ? "AND p.webshop = :webshopid" : "") . "
";
}else{
if($category != null){
$sql = "
SELECT " . (!$doCount ? "wp" : "count(wp.id)") . "
FROM TrinityWebshopBundle:WebshopProduct wp
JOIN wp.product p
WHERE 1 = 1
" . ($Webshop ? "AND p.webshop = :webshopid" : "") . "
" . ($category != null ? "
AND wp.category = " . $category . "
" : "") . "
" . ($stock != null ? ($stock == 1 ?
"AND (p.stock = 0 OR (p.stock = 1 AND (p.stock_amount - p.out_of_stock_quantity) > 0))"
:
"AND p.toggle_stock = 0 OR (p.stock = 1 AND ((p.stock_amount - p.out_of_stock_quantity) <= 0 or (p.child_stock_amount - p.out_of_stock_quantity) <= 0))"
)
:
"") . "
" . ($type != null ? "
AND p.type = " . $type . "
" : "") . "
" . ($tax != null ? "
AND p.tax = " . $tax . "
" : "") . "
" . ($sale != null ? "
AND p.price_sale " . ($sale ? " > 0" : "<= 0") . "
AND (p.price_sale < p.price_incl OR p.price_sale > p.price_incl)
" : "") . "
" . ($sales != null ? "
AND (SELECT COUNT(op) FROM TrinityWebshopBundle:OrderProduct op WHERE op.product = p) >= " . $sales . "
" : "") . "
" . ($visibility === '1' ? "
AND p.visible = 1
AND p.enabled = 1
" : "") . "
" . ($visibility === '0' ? "
AND (p.visible = 0
OR p.enabled = 0)
" : "") . "
" . ($status != null ? "
AND p.enabled = " . $status . "
" : "") . "
" . ($featured != null ? "
AND p.featured = " . $featured . "
" : "") . "
" . (empty($media) && !is_null($media) ? "AND wp.has_media != 1" : "") . "
" . (!empty($queries) ? " AND " . implode(" AND ", $queries) : "") . "
" . (!$doCount ? "GROUP BY p.id" : "") . "
ORDER BY {$sort} {$order}
";
}else{
$sql = "
SELECT " . (!$doCount ? "p" : "count(p.id)") . "
FROM TrinityWebshopBundle:Product p
" . ($category != null ? " JOIN p.category cp JOIN cp.category c " : "") . "
" . (empty($media) && !is_null($media) ? "LEFT JOIN p.media m" : "") . "
WHERE 1 = 1
" . ($Webshop ? "AND p.webshop = :webshopid" : "") . "
" . ($category != null ? "
AND c.id = " . $category . "
" : "") . "
" . ($stock != null ? ($stock == 1 ?
"AND (p.stock = 0 OR (p.stock = 1 AND (p.stock_amount - p.out_of_stock_quantity) > 0))"
:
"AND p.toggle_stock = 0 OR (p.stock = 1 AND ((p.stock_amount - p.out_of_stock_quantity) <= 0))"
)
:
"") . "
" . ($type != null ? "
AND p.type = " . $type . "
" : "") . "
" . ($tax != null ? "
AND p.tax = " . $tax . "
" : "") . "
" . ($sale != null ? "
AND p.price_sale " . ($sale ? " > 0" : "<= 0") . "
AND (p.price_sale < p.price_incl OR p.price_sale > p.price_incl)
" : "") . "
" . ($sales != null ? "
AND (SELECT COUNT(op) FROM TrinityWebshopBundle:OrderProduct op WHERE op.product = p) >= " . $sales . "
" : "") . "
" . ($visibility === '1' ? "
AND p.visible = 1
AND p.enabled = 1
" : "") . "
" . ($visibility === '0' ? "
AND (p.visible = 0
OR p.enabled = 0)
" : "") . "
" . ($status != null ? "
AND p.enabled = " . $status . "
" : "") . "
" . ($featured != null ? "
AND p.featured = " . $featured . "
" : "") . "
" . (empty($media) && !is_null($media) ? "AND p.has_media = 0" : "") . "
" . (!empty($queries) ? " AND " . implode(" AND ", $queries) : "") . "
" . (!$doCount ? "GROUP BY p.id" : "") . "
ORDER BY {$sort} {$order}
";
}
/*$sql = "
SELECT " . (!$doCount ? "p" : "count(p.id)") . "
FROM TrinityWebshopBundle:Product p
LEFT JOIN p.product_link pl
" . ($category != null ? " JOIN p.category cp JOIN cp.category c " : "") . "
" . (!empty($media) ? "JOIN p.media m" : "") . "
" . (empty($media) && !is_null($media) ? "LEFT JOIN p.media m" : "") . "
WHERE 1 = 1
" . ($Webshop ? "AND p.webshop = :webshopid" : "") . "
" . ($category != null ? "
AND c.id = " . $category . "
" : "") . "
" . ($stock != null ? "
AND p.stock_amount " . ($stock ? " > 0" : "<= 0") . "
" : "") . "
" . ($type != null ? "
AND p.type = " . $type . "
" : "") . "
" . ($tax != null ? "
AND p.tax = " . $tax . "
" : "") . "
" . ($sale != null ? "
AND p.price_sale " . ($sale ? " > 0" : "<= 0") . "
AND (p.price_sale < p.price_incl OR p.price_sale > p.price_incl)
" : "") . "
" . ($sales != null ? "
AND (SELECT COUNT(op) FROM TrinityWebshopBundle:OrderProduct op WHERE op.product = p) >= " . $sales . "
" : "") . "
" . ($visibility === '1' ? "
AND p.visible = 1
AND p.enabled = 1
" : "") . "
" . ($visibility === '0' ? "
AND (p.visible = 0
OR p.enabled = 0)
" : "") . "
" . ($status != null ? "
AND p.enabled = " . $status . "
" : "") . "
" . ($featured != null ? "
AND p.featured = " . $featured . "
" : "") . "
" . (empty($media) && !is_null($media) ? "AND m.id IS NULL" : "") . "
" . (!empty($queries) ? " AND " . implode(" AND ", $queries) : "") . "
" . (!$doCount ? "GROUP BY p.id" : "") . "
ORDER BY {$sort} {$order}
";*/
}
// dump($sql);die();
/*if(!$doCount){
dump($sql);
die();
}*/
$query = $em->createQuery($sql);
if ($Webshop) {
$query->setParameter('webshopid', (!empty($Webshop) ? $Webshop : 0));
}
if($doCount){
try{
return $query->getSingleScalarResult();
}catch(\Exception $e){
return 0;
}
}
if(!empty($limit)){
try{
$all_results = $query->setFirstResult($offset)->setMaxResults($limit)->getResult();
foreach($all_results as $i => $r){
if($r instanceof \App\Trinity\WebshopBundle\Entity\WebshopProduct){
$all_results[$i] = $r->getProduct();
}
}
return $all_results;
}catch(\Exception $e){
return [];
}
}
$all_results = $query->getResult();
foreach($all_results as $i => $r){
if($r instanceof \App\Trinity\WebshopBundle\Entity\WebshopProduct){
$all_results[$i] = $r->getProduct();
}
}
return $all_results;
}
public function getPriceRange($Webshop = null, $Category = null){
$em = $this->getEntityManager();
$sql = "
SELECT MAX(p.sell_price_incl) as max_price, MIN(p.sell_price_incl) as min_price
FROM TrinityWebshopBundle:Product p
JOIN p.category cp
JOIN p.product_link wp
JOIN cp.category c
" . ($Webshop ? "JOIN c.webshop w" : "") . "
WHERE p.visible = 1
AND p.sell_price_incl >= 1
AND p.enabled = 1
AND (p.stock_amount > 0 or p.child_stock_amount > 0 or p.stock = 0 or p.visible_no_stock = 1)
" . ($Webshop ? "AND w.id LIKE :webshopid" : "") . "
" . ($Category && $Category instanceof Category ? "AND c.id LIKE :categoryid" : "") . "
" . ($Category && is_array($Category) ? "AND wp.category IN (:categoryids)" : ""). "
";
$query = $em->createQuery($sql);
if ($Webshop) {
$query->setParameter('webshopid', (!empty($Webshop) ? $Webshop->getId() : 0));
}
if ($Category && $Category instanceof Category) {
$query->setParameter('categoryid', (!empty($Category) ? $Category->getId() : 0));
} elseif ($Category && is_array($Category)) {
$query->setParameter('categoryids', $Category, \Doctrine\DBAL\Connection::PARAM_INT_ARRAY);
}
return $query->getResult();
}
public function countBy($Webshop = null, $q = null, $type = null){
$em = $this->getEntityManager();
$query = $em->createQuery(
"SELECT count(p)
FROM TrinityWebshopBundle:Product p
JOIN p.category cp
JOIN cp.category c
" . ($Webshop ? "JOIN c.webshop w" : "") . "
WHERE 1 = 1
" . ($Webshop ? "AND w.id LIKE :webshopid" : "") . "
" . ($type ? "
AND p.type = " . $type . "
" : "") . "
" . ($q ? "
AND (
p.label LIKE '%" . $q . "%' OR
p.ean LIKE '%" . $q . "%' OR
p.sku LIKE '%" . $q . "%' OR
p.number LIKE '%" . $q . "%' OR
c.label LIKE '%" . $q . "%'
)
" : "") . "
"
);
if ($Webshop) {
$query->setParameter('webshopid', (!empty($Webshop) ? $Webshop->getId() : 0));
}
return $query->getSingleScalarResult();
}
public function findOrders($id, $q = null, $doCount = false, $offset = null, $limit = null){
$em = $this->getEntityManager();
if(!empty($q)){
$sql = "SELECT " . ($doCount ? "count(o)" : "o") . "
FROM TrinityWebshopBundle:Order o
JOIN o.products p
JOIN p.product op
WHERE op.id = " . $id . "
AND (
o.order_id LIKE '%" . $q . "%' OR
o.firstname LIKE '%" . $q . "%' OR
o.lastname LIKE '%" . $q . "%' OR
o.email LIKE '%" . $q . "%'
)
ORDER BY o.date DESC
";
}else{
$sql = "SELECT " . ($doCount ? "count(o)" : "o") . "
FROM TrinityWebshopBundle:Order o
JOIN o.products p
JOIN p.product op
WHERE op.id = " . $id . "
ORDER BY o.date DESC
";
}
$query = $em->createQuery($sql);
if($doCount){
return (int)$query->getSingleScalarResult();
}
return $query->setFirstResult($offset)->setMaxResults($limit)->getResult();
}
public function getUsingSkuOtherShops($sku, $Product, $Webshop = null){
$em = $this->getEntityManager();
$query = $em->createQuery(
"SELECT p
FROM TrinityWebshopBundle:Product p
WHERE 1 = 1
AND p.sku = '$sku'
" . ($Product ? "AND p.id != " . $Product->getId() . "" : "") . "
" . ($Webshop ? "AND p.webshop = :webshopid" : "") . "
"
);
if ($Webshop) {
$query->setParameter('webshopid', (!empty($Webshop) ? $Webshop->getId() : 0));
}
$results = $query->getResult();
return $results;
}
public function getUsingEanOtherShops($sku, $Product, $Webshop = null){
$em = $this->getEntityManager();
$query = $em->createQuery(
"SELECT p
FROM TrinityWebshopBundle:Product p
WHERE 1 = 1
AND p.sku = '$sku'
" . ($Product ? "AND p.id != " . $Product->getId() . "" : "") . "
" . ($Webshop ? "AND p.webshop = :webshopid" : "") . "
"
);
if ($Webshop) {
$query->setParameter('webshopid', (!empty($Webshop) ? $Webshop->getId() : 0));
}
$results = $query->getResult();
return $results;
}
public function countWithImage($Webshop = null){
$em = $this->getEntityManager();
$query = $em->createQuery(
"SELECT count(p)
FROM TrinityWebshopBundle:Product p
JOIN p.category cp
JOIN cp.category c
JOIN p.media m
" . ($Webshop ? "JOIN c.webshop w" : "") . "
WHERE 1 = 1
" . ($Webshop ? "AND w.id LIKE :webshopid" : "") . "
"
);
if ($Webshop) {
$query->setParameter('webshopid', (!empty($Webshop) ? $Webshop->getId() : 0));
}
return $query->getSingleScalarResult();
}
public function selector($Webshop, $q = '', $c = ''){
$em = $this->getEntityManager();
$query = $em->createQuery(
"SELECT p
FROM TrinityWebshopBundle:Product p
JOIN p.category cp
JOIN cp.category c
JOIN c.webshop w
WHERE w.id LIKE :webshopid
" . ($q ? "
AND (
p.label LIKE '%" . $q . "%' OR
p.ean LIKE '%" . $q . "%' OR
p.sku LIKE '%" . $q . "%' OR
p.number LIKE '%" . $q . "%' OR
c.label LIKE '%" . $q . "%'
)
" : "") . "
" . ($c ? "
AND c.id = " . $c . "
" : "") . "
ORDER BY p.label ASC
"
);
$query->setParameter('webshopid', (!empty($Webshop) ? $Webshop->getId() : 0));
return $query->getResult();
}
public function getBy($Webshop, $offset, $limit, $q = null, $type = null){
$em = $this->getEntityManager();
$query = $em->createQuery(
"SELECT p
FROM TrinityWebshopBundle:Product p
JOIN p.category cp
JOIN cp.category c
" . ($Webshop ? "JOIN c.webshop w" : "") . "
WHERE 1 = 1
" . ($Webshop ? "AND w.id LIKE :webshopid" : "") . "
" . ($type ? "
AND p.type = " . $type . "
" : "") . "
" . ($q ? "
AND (
p.label LIKE '%" . $q . "%' OR
p.ean LIKE '%" . $q . "%' OR
p.sku LIKE '%" . $q . "%' OR
p.number LIKE '%" . $q . "%' OR
c.label LIKE '%" . $q . "%'
)
" : "") . "
ORDER BY p.label"
);
if ($Webshop) {
$query->setParameter('webshopid', (!empty($Webshop) ? $Webshop->getId() : 0));
}
return $query->setFirstResult($offset)->setMaxResults($limit)->getResult();
}
public function findByCategoryAndSlug($category, $slug, $preview = false){
$em = $this->getEntityManager();
$sql = "
SELECT p
FROM TrinityWebshopBundle:Product p
JOIN p.category cp
JOIN cp.category c
JOIN p.language pl
JOIN c.webshop w
JOIN w.language l WITH l.id = pl.id
WHERE c.id = " . $category->getId() . "
AND p.slug = '" . $slug . "'
";
$query = $em->createQuery($sql);
try {
return $query->getSingleResult();
} catch (\Exception $e) {}
return null;
}
public function findBySpecs($specs, $Product){
$em = $this->getEntityManager();
$joins = [];
$ands = [];
$orderbys = [];
if(!empty($specs['spec'])){
foreach($specs['spec'] as $specid => $value){
$joins[] = " JOIN p.spec_values s" . $specid;
$ands[] = " AND s" . $specid . ".spec = " . $specid . "";
$ands[] = " AND s" . $specid . ".value = '" . $value . "'";
$orderbys[] = "s" . $specid . ".position ASC";
}
}/*else{
$joins[] = " JOIN p.spec_values sx";
$orderbys[] = "sx.position ASC";
}*/
$sql = 'SELECT p
FROM TrinityWebshopBundle:Product p
JOIN p.linked_to l
' . implode(' ', $joins) . '
WHERE l.id = :product
AND p.enabled = 1
' . implode(' ', $ands) . '
' . (!empty($orderbys) ? 'ORDER BY ' . implode(', ', $orderbys) : '') . '
';
$query = $em->createQuery($sql)
->setParameter('product', $Product);
return $query->getResult();
}
public function findAnyBySpecs($specs, $price_range = []){
$em = $this->getEntityManager();
$joins = [];
$ands = [];
$orderbys = [];
if(!empty($specs['spec'])){
foreach($specs['spec'] as $specid => $value){
$joins[] = " JOIN p.spec_values s" . $specid;
$ands[] = " AND s" . $specid . ".spec = " . $specid . "";
$ands[] = " AND s" . $specid . ".value = '" . $value . "'";
$orderbys[] = "s" . $specid . ".position ASC";
}
}/*else{
$joins[] = " JOIN p.spec_values sx";
$orderbys[] = "sx.position ASC";
}*/
$sql = 'SELECT p
FROM TrinityWebshopBundle:Product p
' . implode(' ', $joins) . '
WHERE 1 = 1
AND p.enabled = 1
' . implode(' ', $ands) . '
' . (!empty($price_range) ? 'AND ((p.sell_price_incl BETWEEN ' . $price_range[0] . ' AND ' . $price_range[1] . '))' : '') . '
' . (!empty($orderbys) ? 'ORDER BY ' . implode(', ', $orderbys) : '') . '
';
$query = $em->createQuery($sql);
return $query->getResult();
}
public function getFeatured($Webshop, $limit = 0, $offset = 0){
$em = $this->getEntityManager();
$query = $em->createQuery(
'SELECT p
FROM TrinityWebshopBundle:Product p
JOIN p.category cp
JOIN cp.category c
JOIN c.webshop w
WHERE w.id LIKE :webshopid
AND p.featured = 1
AND p.visible = 1
AND p.enabled = 1
GROUP BY p.id
ORDER BY p.label'
)->setParameter('webshopid', (!empty($Webshop) ? $Webshop->getId() : 0));
if($limit > 0){
return $query->setFirstResult($offset)->setMaxResults($limit)->getResult();
}else{
return $query->getResult();
}
}
public function getNew($Webshop, $limit = 0, $offset = 0){
$em = $this->getEntityManager();
/*$products = $this->createQueryBuilder('p')
->select('p')
->join('p.category', 'c')
->join('c.webshop', 'w')
->where('w = :webshop')
->setParameter('webshop', $Webshop)
->orderBy('p.id', 'desc')
->setMaxResults($limit)
->setFirstResult($offset)
->getQuery()
->getSql();
dump($products);die();*/
$query = $em->createQuery(
'SELECT p
FROM TrinityWebshopBundle:Product p
JOIN p.category cp
JOIN cp.category c
JOIN c.webshop w
WHERE w.id LIKE :webshopid
AND p.visible = 1
AND p.enabled = 1
GROUP BY p.id
ORDER BY p.id DESC'
)->setParameter('webshopid', (!empty($Webshop) ? $Webshop->getId() : 0));
if($limit > 0){
return $query->setFirstResult($offset)->setMaxResults($limit)->getResult();
}else{
return $query->getResult();
}
}
public function getBestViewed($Webshop, $limit = 0, $offset = 0){
$em = $this->getEntityManager();
$query = $em->createQuery(
'SELECT p
FROM TrinityWebshopBundle:Product p
JOIN p.category cp
JOIN cp.category c
JOIN c.webshop w
WHERE w.id LIKE :webshopid
AND p.visible = 1
AND p.enabled = 1
GROUP BY p.id
ORDER BY p.views DESC'
)->setParameter('webshopid', (!empty($Webshop) ? $Webshop->getId() : 0));
if($limit > 0){
return $query->setFirstResult($offset)->setMaxResults($limit)->getResult();
}else{
return $query->getResult();
}
}
public function findByCategoryFront($category_id, $sort, $limit = 0, $offset = 0){
$em = $this->getEntityManager();
$s = ['p.id desc'];
if(!empty($sort)){
$s = [];
foreach($sort as $k => $v){
$s[] = "p.$k $v";
}
}
if(!is_array($category_id)){
$category_id = [$category_id];
}
$query = $em->createQuery(
'SELECT p
FROM TrinityWebshopBundle:Product p
JOIN p.category cp JOIN cp.category c WITH c.id IN (' . implode(',', $category_id) . ')
WHERE p.enabled = 1
AND p.visible = 1
GROUP BY p.id
ORDER BY ' . implode(',', $s)
);
if($limit > 0){
return $query->setFirstResult($offset)->setMaxResults($limit)->getResult();
}else{
return $query->getResult();
}
}
public function searchSimple($q = ''){
$em = $this->getEntityManager();
$query = $em->createQuery(
"
SELECT p
FROM TrinityWebshopBundle:Product p
WHERE p.label LIKE '%" . $q . "%'
OR p.sku LIKE '%" . $q . "%'
OR p.number LIKE '%" . $q . "%'
"
);
return $query->getResult();
}
public function getBestSold($Webshop, $limit = 0, $offset = 0){
$em = $this->getEntityManager();
$query = $em->createQuery(
'SELECT p as Product, COUNT(op.id) AS num
FROM TrinityWebshopBundle:Product p
JOIN p.category cp
JOIN cp.category c
JOIN c.webshop w
JOIN TrinityWebshopBundle:OrderProduct op WITH op.product = p
WHERE w.id LIKE :webshopid
AND p.enabled = 1
AND p.visible = 1
GROUP BY p.id
ORDER BY num DESC'
)->setParameter('webshopid', (!empty($Webshop) ? $Webshop->getId() : 0));
if($limit > 0){
return $query->setFirstResult($offset)->setMaxResults($limit)->getResult();
}else{
return $query->getResult();
}
}
public function getBestSoldAllowed($Webshop, $limit = 0, $offset = 0){
$em = $this->getEntityManager();
$query = $em->createQuery(
'SELECT p as Product, COUNT(op.id) AS num
FROM TrinityWebshopBundle:Product p
JOIN p.category cp
JOIN cp.category c
JOIN c.webshop w
JOIN TrinityWebshopBundle:OrderProduct op WITH op.product = p
WHERE w.id LIKE :webshopid
AND p.visible = 1
AND p.enabled = 1
GROUP BY p.id
ORDER BY num DESC'
)->setParameter('webshopid', (!empty($Webshop) ? $Webshop->getId() : 0));
if($limit > 0){
return $query->setFirstResult($offset)->setMaxResults($limit)->getResult();
}else{
return $query->getResult();
}
}
public function countProducts(){
$em = $this->getEntityManager();
$qb = $em->createQueryBuilder();
$qb->select('count(product.id)');
$qb->from('TrinityWebshopBundle:Product','product');
$count = $qb->getQuery()->getSingleScalarResult();
return $count;
}
public function search($Webshop, $filters = array(), $order = null, $orderDir = 'asc', $noLimit = false, $ignoreStock = false, $page = 1, $limit = 9, $offset = 0, $thisIsFilter = false){
$em = $this->getEntityManager();
if(isset($filters['q']) && is_array($filters['q'])){
$filters['q'] = implode(' ', $filters['q']);
}
if(isset($filters['q']) && strpos($filters['q'], 'linkableproducts') !== false){
$ignoreStock = true;
}
$featured = (!empty($filters['featured']) && $filters['featured'] === true);
$debug = false;
if(isset($filters['debug'])){
$debug = true;
unset($filters['debug']);
}
$join = [];
$joinNorForCount = [];
$and = [];
$or = [];
$start = 0;
if(empty($limit) || !is_numeric($limit)){
$limit = 9; // 9 is default, can be overridden in Trinity
}
// Check category
if(isset($filters['page']) && $page <= 1){
$page = (int)$filters['page'];
}
$webshop_id = (!empty($Webshop) ? $Webshop->getId() : 0);
$ignore_ids = [];
if(!empty($filters['ignore_ids'])){
$ignore_ids = $filters['ignore_ids'];
if(!is_array($ignore_ids)){
$ignore_ids = [$ignore_ids];
}
unset($filters['ignore_ids']);
}
$showHidden = false;
if(!empty($filters['q'])){
if(preg_match('/linkableproducts=1/', $filters['q'])){
$filters['q'] = preg_replace('/&linkableproducts=1/', '', $filters['q']);
$showHidden = true;
}
}
$ranges = [];
$ranges_joins = [];
$ranges_where = [];
$fn = 0;
if(!empty($filters['filters'])){
foreach($filters['filters'] as $spec_id => $value){
if(is_array($value) && isset($value['range'])){
foreach($value as $t => $v){
if(!empty($v[0]) && !empty($v[1]) && $v[0] != 'undefined' && $v[1] != 'undefined'){
$ranges_joins[] = "JOIN p.spec_values sv{$fn}x1 WITH sv{$fn}x1.spec = '" . $spec_id . "'";
$ranges_where[] = "AND (sv{$fn}x1.value BETWEEN " . implode(' AND ', $v) . " OR sv{$fn}x1.value = '' OR sv{$fn}x1.value IS NULL)";
$fn++;
}
}
}
}
}
$sliders = (!empty($filters['sliders']) ? $filters['sliders'] : []);
$sliders_range = (!empty($filters['sliders_range']) ? $filters['sliders_range'] : []);
if(!empty($filters['filters'])){
foreach($filters['filters'] as $spec_id => $value){
if(in_array($spec_id, $sliders) && (int)$value[0] > 0){
$v = [0, (int)$value[0]];
$ranges_joins[] = "JOIN p.spec_values sv{$fn}x1 WITH sv{$fn}x1.spec = '" . $spec_id . "'";
$ranges_where[] = "AND (sv{$fn}x1.value BETWEEN " . implode(' AND ', $v) . ")";
$fn++;
}
if(in_array($spec_id, $sliders_range)){
$v = [(int)$value['min'], (int)$value['max']];
$ranges_joins[] = "JOIN p.spec_values sv{$fn}x1 WITH sv{$fn}x1.spec = '" . $spec_id . "'";
$ranges_where[] = "AND (sv{$fn}x1.value BETWEEN " . implode(' AND ', $v) . ")";
$fn++;
}
/*if(is_array($value) && isset($value['range'])){
foreach($value as $t => $v){
if(!empty($v[0]) && !empty($v[1]) && $v[0] != 'undefined' && $v[1] != 'undefined'){
$ranges_joins[] = "JOIN p.spec_values sv{$fn}x1 WITH sv{$fn}x1.spec = '" . $spec_id . "'";
$ranges_where[] = "AND (sv{$fn}x1.value BETWEEN " . implode(' AND ', $v) . " OR sv{$fn}x1.value = '' OR sv{$fn}x1.value IS NULL)";
$fn++;
}
}
}*/
}
}
$linkableproducts = (!empty($_GET['linkableproducts']) ? true : false);
$search_raw = (!empty($filters['q']) ? (!is_array($filters['q']) ? [$filters['q']] : $filters['q']) : null);
$visible = (!empty($filters['visible']) ? $filters['visible'] : null);
$featured = (!empty($filters['featured']) ? $filters['featured'] : null);
$category = (!empty($filters['category']) ? $filters['category'] : null);
$relation = (!empty($filters['relation']) ? $filters['relation'] : null);
$newproducts = (!empty($filters['newproducts']) ? $filters['newproducts'] : null);
$search = [];
if(!empty($search_raw)){
foreach($search_raw as $sr){
$sr = explode(' ', $sr);
foreach($sr as $srp){
$search[] = $srp;
}
}
}
$typeFilter = "((p.type = 0 or p.type = 3 or p.type = 4) or ((p.type = 1 or p.type = 2) and p.child_stock_amount > 0))";
if(!empty($_GET['linkableproducts']) && (empty($_GET['link']) || $_GET['link'] != 'relations')){
$visible = false;
$typeFilter = "((p.type = 0 or p.type = 3 or p.type = 4))";
}
$tags = [];
if(!empty($filters['tags'])){
$tags = $filters['tags'];
}
$sql = "
SELECT p
FROM TrinityWebshopBundle:Product p
JOIN p.product_link wp
" . (!empty($category) && ($category instanceof Category || is_array($category)) ? "LEFT JOIN p.category cp WITH (cp.category = wp.category)" : "") . "
" . (!empty($relation) ? "JOIN p.relation_to cp WITH (cp.id = " . $relation->getId() . ")" : "") . "
" . (!empty($tags) ? "JOIN p.tags pt WITH (pt.id IN (" . implode(', ', $tags) . "))" : "") . "
" . (!empty($ranges_joins) ? implode("\n", $ranges_joins) : "") . "
WHERE 1 = 1
AND " . ($linkableproducts ? "1 = 1" : "((p.type = 0 and ((p.stock_amount - p.out_of_stock_quantity) > 0 or p.stock = 0 or p.visible_no_stock = 1)) or p.type > 0)") . "
AND " . $typeFilter . "
" . (!empty($ranges_where) ? implode("\n", $ranges_where) : "") . "
";
if(!empty($ignore_ids)){
$sql .= "\nAND p.id NOT IN (" . implode(',', $ignore_ids) . ")";
}
$sql .= "\nAND p.enabled = 1";
$s = [];
if($search){
foreach($search as $q){
$q = str_replace('\'', '_', $q);
if($thisIsFilter){
$s[] = "((p.label LIKE '%" . $q . "%') OR (p.number LIKE '%" . $q . "%') OR (wp.specs LIKE '%" . $q . "%') OR (wp.child_specs LIKE '%" . $q . "%') OR p.extra_search_tags LIKE '%" . $q . "%')";
}else{
$s[] = "((wp.specs LIKE '%" . $q . "%' OR (
p.label LIKE '%" . $q . "%'
OR
p.extra_search_tags LIKE '%" . $q . "%'
OR
p.label_sub LIKE '%" . $q . "%'
OR
p.compatibility LIKE '%" . $q . "%'
OR
p.ean LIKE '%" . $q . "%'
OR
p.number LIKE '%" . $q . "%'
OR
p.sku LIKE '%" . $q . "%'
)) OR (wp.child_specs LIKE '%" . $q . "%' OR (
p.label LIKE '%" . $q . "%'
OR
p.extra_search_tags LIKE '%" . $q . "%'
OR
p.label_sub LIKE '%" . $q . "%'
OR
p.compatibility LIKE '%" . $q . "%'
OR
p.ean LIKE '%" . $q . "%'
OR
p.number LIKE '%" . $q . "%'
OR
p.sku LIKE '%" . $q . "%'
)))";
}
}
$sql .= "\nAND (" . implode(" AND ", $s) . ")";
}
if($visible){
$sql .= "\nAND p.visible = 1";
}
if($featured){
$sql .= "\nAND p.featured = 1";
}
if ($newproducts) {
$sql .= "\nAND p.new = 1";
$sql .= "\nAND (p.new_from is null OR p.new_from <= NOW())";
$sql .= "\nAND (p.new_till is null OR p.new_till > NOW())";
}
if($category && $category instanceof Category){
$sql .= "\nAND wp.category = " . $category->getId();
}
if($category && is_array($category)){
$sql .= "\nAND wp.category IN (" . implode(', ', $category) . ")";
}
if($webshop_id && is_numeric($webshop_id)){
$sql .= "\nAND wp.webshop = " . $webshop_id;
}
if(!empty($filters['price']) && is_array($filters['price']) && (!isset($filters['ignore_pricing']) || $filters['ignore_pricing'] == false)){
// $sql .= "\nAND ((p.sell_price_incl BETWEEN " . $filters['price'][0] . " AND " . $filters['price'][1] . ") OR p.sell_price_incl < 1)";
$sql .= "\nAND ((p.sell_price_incl BETWEEN " . $filters['price'][0] . " AND " . $filters['price'][1] . "))";
}
$strict_search = [265];
if(!empty($filters['filters'])){
foreach($filters['filters'] as $spec_id => $value){
if(in_array($spec_id, $sliders) || in_array($spec_id, $sliders_range)){
continue;
}
if(!isset($value['range']) && $spec_id != 'q'){
if(!is_array($value)) $value = [$value];
foreach($value as $t => $v){
$v = str_replace('\'', '_', $v);
$v = json_encode($v);
$v = str_replace('"', '', $v);
$v = str_replace('\\', '\\\\', $v);
if(in_array($spec_id, $strict_search) || $thisIsFilter){
$sql .= "\nAND (((wp.specs LIKE '%\"" . $spec_id . "\":%' AND (wp.specs LIKE '%\"" . $v . "\"%'))) OR ((wp.child_specs LIKE '%\"" . $spec_id . "\":%' AND (wp.child_specs LIKE '%\"" . $v . "\"%'))))";
}else{
$sql .= "\nAND (((wp.specs LIKE '%\"" . $spec_id . "\":%' AND (wp.specs LIKE '%\"%" . $v . "%\"%' OR wp.specs LIKE '%," . $v . "%' OR wp.specs LIKE '%" . $v . ",%')) OR (
p.label LIKE '%" . $v . "%'
OR
p.extra_search_tags LIKE '%" . $v . "%'
OR
p.label_sub LIKE '%" . $v . "%'
OR
p.compatibility LIKE '%" . $v . "%'
OR
p.ean LIKE '%" . $v . "%'
OR
p.number LIKE '%" . $v . "%'
OR
p.sku LIKE '%" . $v . "%'
)) OR ((wp.child_specs LIKE '%\"" . $spec_id . "\":%' AND (wp.child_specs LIKE '%\"%" . $v . "%\"%' OR wp.child_specs LIKE '%," . $v . "%' OR wp.child_specs LIKE '%" . $v . ",%')) OR (
p.label LIKE '%" . $v . "%'
OR
p.extra_search_tags LIKE '%" . $v . "%'
OR
p.label_sub LIKE '%" . $v . "%'
OR
p.compatibility LIKE '%" . $v . "%'
OR
p.ean LIKE '%" . $v . "%'
OR
p.number LIKE '%" . $v . "%'
OR
p.sku LIKE '%" . $v . "%'
)))";
}
}
}
}
}
/*foreach($search as $v){
$sql .= "\nAND ";
}*/
$sql_order = 'wp.pos asc'; // Default
$custom_order = false;
if(!empty($order)){
if(substr($order, 0, 3) == 'pos'){
if(preg_match('/asc|desc/', strtolower($order))){
$sql_order = "cp." . $order;
}else{
$sql_order = "cp." . $order . " " . $orderDir;
}
if (!str_contains($order, 'price_incl')) {
$sql_order .= " , cp.id " . $orderDir;
}
}else{
if(preg_match('/asc|desc/', strtolower($order))){
$sql_order = "p." . $order;
}else{
$sql_order = "p." . $order . " " . $orderDir;
}
if (!str_contains($order, 'price_incl')) {
$sql_order .= ", p.id " . $orderDir;
}
$custom_order = true;
}
}
if(!$custom_order && empty($category)){
$sql_order = "p.id desc";
}
$sql_order = str_replace('cp.pos', 'wp.pos', $sql_order);
if(!empty($filters['q']) && $sql_order == 'cp.pos asc'){
$fq = explode(' ', $filters['q']);
$sql_order = "";
foreach($fq as $fqp){
if(empty($sql_order)){
$sql_order .= "FIND_IN_SET('" . $fqp . "', p.label)";
}else{
$sql_order .= ",FIND_IN_SET('" . $fqp . "', p.label)";
}
}
}
// $sql .= "\nGROUP BY p.id";
$sql .= "\nORDER BY " . $sql_order;
// dump($sql);die();
/*if(isset($filters['dev']) && $filters['dev'] == true){
dump($sql);die();
}*/
// dump($sql);die();
if (!empty($relation)) {
/*dump($sql);die();
$query = $em->createQuery($sql);
echo ( '<pre>' . print_r( SqlFormatter::format($query->getSql()), 1 ) . '</pre>' );
dump(count($query->getResult()));
die();*/
}
// dump($sql);die();
/*$query = $em->createQuery($sql);
echo ( '<pre>' . print_r( SqlFormatter::format($query->getSql()), 1 ) . '</pre>' );
dump(count($query->getResult()));
die();*/
// dump($sql);die();
$query = $em->createQuery($sql);
// die($query->getSql());
if($debug){
die($query->getSql());
}
// $em = $this->getEntityManager();
/*$sql = "SELECT p
FROM TrinityWebshopBundle:Product p
" . implode("\n", $join) . "
" . implode("\n", $joinNorForCount) . "
" . ($Webshop ? "JOIN c.webshop w WITH w.id = " . $webshop_id : "") . "
WHERE p.id > 0
AND p.enabled = 1
AND
(
(
(p.type = 1 OR p.type = 2)
" . implode("\n", $filterValuesLinked) . "
)
OR
(
(p.type != 1 AND p.type != 2)
" . implode("\n", $filterValuesSimple) . "
)
)
AND c.label != 'Ongesorteerd'
" . ($featured ? "AND p.featured = 1" : "") . "
" . (!$ignoreStock ? "
AND ((p.type = 0 and ((p.stock_amount - p.out_of_stock_quantity) > 0 or p.stock = 0 or p.visible_no_stock = 1)) or p.type > 0)
AND ((p.type = 0 or p.type = 3 or p.type = 4) or ((p.type = 1 or p.type = 2) and p.child_stock_amount > 0))
" : "") .
(!empty($and) ? "
AND
(
" . implode("\nAND ", $and) . "
)
" : '') .
(!empty($or) ? "
AND
(
" . implode("\nOR ", $or) . "
)
" : '')
. (!empty($specFilter) ? "AND (" . implode("\nOR ", $specFilter) . ")" : "") . "
ORDER BY " . (!empty($order) ? (substr($order, 0, 3) == 'pos' ? "cp." . $order : "p." . $order) : "cp.pos asc");
$query = $em->createQuery($sql);*/
if(!empty($page)){
$start = (($page * $limit) - $limit);
}else if(!empty($filters['limit'])){
$start = (int)$filters['limit']['start'];
$limit = (int)$filters['limit']['limit'];
}
if($offset > 0){
$start = $offset;
}
if($noLimit == false){
$query->setMaxResults($limit)->setFirstResult($start);
}
// Doctrine Paginator experiment
$results = new \Doctrine\ORM\Tools\Pagination\Paginator($query, $fetchJoinCollection = true);
// $results->setUseOutputWalkers(false);
$total = count($results);
$resultCount = count($results->getIterator()->getArrayCopy());
$results = $results->getIterator()->getArrayCopy();
if(!empty($filters['filters'])){
// Walk through filtered results
foreach($results as $k => $res){
// Only for grouped or configurable products
if($res->getType() > 0){
// Walk through linked products
foreach($res->getLinkedProducts() as $lp){
// See if spec values are given to product
if($lp->getSpecValues()->count() > 0){
// Walk through spec values
foreach($lp->getSpecValues() as $sv){
$Spec = $sv->getSpec();
if($Spec){
try {
// Limit product display on colors
if($Spec->getType() == 'color' || $Spec->getCode() == 'color'){
if(array_key_exists($Spec->getId(), $filters['filters'])){
// If color matches the filtered colors
if(in_array($sv->getValue(), $filters['filters'][$Spec->getId()])){
// Set linked product as display product
$results[$k]->setDisplayProduct($lp);
break(3);
}
}
}
} catch (\Exception $ex) {
//dump($ex);
}
}
}
}
}
}
}
}
return [
'total' => $total,
'current' => $resultCount,
'start' => $start,
'limit' => $limit,
'pages' => (int)ceil($total / $limit),
'page' => (int)$page,
'results' => $results
];
}
public function searchInApi($Webshop, $filters = array(), $order = null, $orderDir = 'asc', $noLimit = false, $ignoreStock = false, $page = 1, $limit = 9, $offset = 0, $thisIsFilter = false){
$em = $this->getEntityManager();
if(isset($filters['q']) && is_array($filters['q'])){
$filters['q'] = implode(' ', $filters['q']);
}
if(isset($filters['q']) && strpos($filters['q'], 'linkableproducts') !== false){
$ignoreStock = true;
}
$featured = (!empty($filters['featured']) && $filters['featured'] === true);
$debug = false;
if(isset($filters['debug'])){
$debug = true;
unset($filters['debug']);
}
$join = [];
$joinNorForCount = [];
$and = [];
$or = [];
$start = 0;
if(empty($limit) || !is_numeric($limit)){
$limit = 9; // 9 is default, can be overridden in Trinity
}
// Check category
if(isset($filters['page']) && $page <= 1){
$page = (int)$filters['page'];
}
$webshop_id = (!empty($Webshop) ? $Webshop->getId() : 0);
$ignore_ids = [];
if(!empty($filters['ignore_ids'])){
$ignore_ids = $filters['ignore_ids'];
if(!is_array($ignore_ids)){
$ignore_ids = [$ignore_ids];
}
unset($filters['ignore_ids']);
}
$showHidden = false;
if(!empty($filters['q'])){
if(preg_match('/linkableproducts=1/', $filters['q'])){
$filters['q'] = preg_replace('/&linkableproducts=1/', '', $filters['q']);
$showHidden = true;
}
}
$ranges = [];
$ranges_joins = [];
$ranges_where = [];
$fn = 0;
if(!empty($filters['filters'])){
foreach($filters['filters'] as $spec_id => $value){
if(is_array($value) && isset($value['range'])){
foreach($value as $t => $v){
if(!empty($v[0]) && !empty($v[1]) && $v[0] != 'undefined' && $v[1] != 'undefined'){
$ranges_joins[] = "JOIN p.spec_values sv{$fn}x1 WITH sv{$fn}x1.spec = '" . $spec_id . "'";
$ranges_where[] = "AND (sv{$fn}x1.value BETWEEN " . implode(' AND ', $v) . " OR sv{$fn}x1.value = '' OR sv{$fn}x1.value IS NULL)";
$fn++;
}
}
}
}
}
$sliders = (!empty($filters['sliders']) ? $filters['sliders'] : []);
$sliders_range = (!empty($filters['sliders_range']) ? $filters['sliders_range'] : []);
if(!empty($filters['filters'])){
foreach($filters['filters'] as $spec_id => $value){
if(in_array($spec_id, $sliders) && (int)$value[0] > 0){
$v = [0, (int)$value[0]];
$ranges_joins[] = "JOIN p.spec_values sv{$fn}x1 WITH sv{$fn}x1.spec = '" . $spec_id . "'";
$ranges_where[] = "AND (sv{$fn}x1.value BETWEEN " . implode(' AND ', $v) . ")";
$fn++;
}
if(in_array($spec_id, $sliders_range)){
$v = [(int)$value['min'], (int)$value['max']];
$ranges_joins[] = "JOIN p.spec_values sv{$fn}x1 WITH sv{$fn}x1.spec = '" . $spec_id . "'";
$ranges_where[] = "AND (sv{$fn}x1.value BETWEEN " . implode(' AND ', $v) . ")";
$fn++;
}
/*if(is_array($value) && isset($value['range'])){
foreach($value as $t => $v){
if(!empty($v[0]) && !empty($v[1]) && $v[0] != 'undefined' && $v[1] != 'undefined'){
$ranges_joins[] = "JOIN p.spec_values sv{$fn}x1 WITH sv{$fn}x1.spec = '" . $spec_id . "'";
$ranges_where[] = "AND (sv{$fn}x1.value BETWEEN " . implode(' AND ', $v) . " OR sv{$fn}x1.value = '' OR sv{$fn}x1.value IS NULL)";
$fn++;
}
}
}*/
}
}
$linkableproducts = (!empty($_GET['linkableproducts']) ? true : false);
$search_raw = (!empty($filters['q']) ? (!is_array($filters['q']) ? [$filters['q']] : $filters['q']) : null);
$visible = (!empty($filters['visible']) ? $filters['visible'] : null);
$featured = (!empty($filters['featured']) ? $filters['featured'] : null);
$category = (!empty($filters['category']) ? $filters['category'] : null);
$relation = (!empty($filters['relation']) ? $filters['relation'] : null);
$newproducts = (!empty($filters['newproducts']) ? $filters['newproducts'] : null);
$search = [];
if(!empty($search_raw)){
foreach($search_raw as $sr){
$sr = explode(' ', $sr);
foreach($sr as $srp){
$search[] = $srp;
}
}
}
$typeFilter = "((p.type = 0 or p.type = 3 or p.type = 4) or ((p.type = 1 or p.type = 2) and p.child_stock_amount > 0))";
if(!empty($_GET['linkableproducts']) && (empty($_GET['link']) || $_GET['link'] != 'relations')){
$visible = false;
$typeFilter = "((p.type = 0 or p.type = 3 or p.type = 4))";
}
$tags = [];
if(!empty($filters['tags'])){
$tags = $filters['tags'];
}
$sql = "
SELECT p
FROM TrinityWebshopBundle:Product p
JOIN p.product_link wp
" . (!empty($category) && ($category instanceof Category || is_array($category)) ? "JOIN p.category cp WITH (cp.category = wp.category)" : "") . "
" . (!empty($relation) ? "JOIN p.relation_to cp WITH (cp.id = " . $relation->getId() . ")" : "") . "
" . (!empty($tags) ? "JOIN p.tags pt WITH (pt.id IN (" . implode(', ', $tags) . "))" : "") . "
" . (!empty($ranges_joins) ? implode("\n", $ranges_joins) : "") . "
WHERE 1 = 1
AND " . ($linkableproducts ? "1 = 1" : "((p.type = 0 and ((p.stock_amount - p.out_of_stock_quantity) > 0 or p.stock = 0 or p.visible_no_stock = 1)) or p.type > 0)") . "
AND " . $typeFilter . "
" . (!empty($ranges_where) ? implode("\n", $ranges_where) : "") . "
";
if(!empty($ignore_ids)){
$sql .= "\nAND p.id NOT IN (" . implode(',', $ignore_ids) . ")";
}
$s = [];
if($search){
foreach($search as $q){
$q = str_replace('\'', '_', $q);
if($thisIsFilter){
$s[] = "((p.label LIKE '%" . $q . "%') OR (p.number LIKE '%" . $q . "%') OR (wp.specs LIKE '%" . $q . "%') OR (wp.child_specs LIKE '%" . $q . "%') OR p.extra_search_tags LIKE '%" . $q . "%')";
}else{
$s[] = "((wp.specs LIKE '%" . $q . "%' OR (
p.label LIKE '%" . $q . "%'
OR
p.extra_search_tags LIKE '%" . $q . "%'
OR
p.label_sub LIKE '%" . $q . "%'
OR
p.compatibility LIKE '%" . $q . "%'
OR
p.ean LIKE '%" . $q . "%'
OR
p.number LIKE '%" . $q . "%'
OR
p.sku LIKE '%" . $q . "%'
)) OR (wp.child_specs LIKE '%" . $q . "%' OR (
p.label LIKE '%" . $q . "%'
OR
p.extra_search_tags LIKE '%" . $q . "%'
OR
p.label_sub LIKE '%" . $q . "%'
OR
p.compatibility LIKE '%" . $q . "%'
OR
p.ean LIKE '%" . $q . "%'
OR
p.number LIKE '%" . $q . "%'
OR
p.sku LIKE '%" . $q . "%'
)))";
}
}
$sql .= "\nAND (" . implode(" AND ", $s) . ")";
}
if($visible){
$sql .= "\nAND p.visible = 1";
}
if($featured){
$sql .= "\nAND p.featured = 1";
}
if ($newproducts) {
$sql .= "\nAND p.new = 1";
$sql .= "\nAND (p.new_from is null OR p.new_from <= NOW())";
$sql .= "\nAND (p.new_till is null OR p.new_till > NOW())";
}
if($category && $category instanceof Category){
$sql .= "\nAND wp.category = " . $category->getId();
}
if($category && is_array($category)){
$sql .= "\nAND wp.category IN (" . implode(', ', $category) . ")";
}
if($webshop_id && is_numeric($webshop_id)){
$sql .= "\nAND wp.webshop = " . $webshop_id;
}
if(!empty($filters['price']) && is_array($filters['price']) && (!isset($filters['ignore_pricing']) || $filters['ignore_pricing'] == false)){
// $sql .= "\nAND ((p.sell_price_incl BETWEEN " . $filters['price'][0] . " AND " . $filters['price'][1] . ") OR p.sell_price_incl < 1)";
$sql .= "\nAND ((p.sell_price_incl BETWEEN " . $filters['price'][0] . " AND " . $filters['price'][1] . "))";
}
$strict_search = [265];
if(!empty($filters['filters'])){
foreach($filters['filters'] as $spec_id => $value){
if(in_array($spec_id, $sliders) || in_array($spec_id, $sliders_range)){
continue;
}
if(!isset($value['range']) && $spec_id != 'q'){
if(!is_array($value)) $value = [$value];
foreach($value as $t => $v){
$v = str_replace('\'', '_', $v);
if(in_array($spec_id, $strict_search) || $thisIsFilter){
$sql .= "\nAND (((wp.specs LIKE '%\"" . $spec_id . "\":%' AND (wp.specs LIKE '%\"" . $v . "\"%'))) OR ((wp.child_specs LIKE '%\"" . $spec_id . "\":%' AND (wp.child_specs LIKE '%\"" . $v . "\"%'))))";
}else{
$sql .= "\nAND (((wp.specs LIKE '%\"" . $spec_id . "\":%' AND (wp.specs LIKE '%\"%" . $v . "%\"%' OR wp.specs LIKE '%," . $v . "%' OR wp.specs LIKE '%" . $v . ",%')) OR (
p.label LIKE '%" . $v . "%'
OR
p.extra_search_tags LIKE '%" . $v . "%'
OR
p.label_sub LIKE '%" . $v . "%'
OR
p.compatibility LIKE '%" . $v . "%'
OR
p.ean LIKE '%" . $v . "%'
OR
p.number LIKE '%" . $v . "%'
OR
p.sku LIKE '%" . $v . "%'
)) OR ((wp.child_specs LIKE '%\"" . $spec_id . "\":%' AND (wp.child_specs LIKE '%\"%" . $v . "%\"%' OR wp.child_specs LIKE '%," . $v . "%' OR wp.child_specs LIKE '%" . $v . ",%')) OR (
p.label LIKE '%" . $v . "%'
OR
p.extra_search_tags LIKE '%" . $v . "%'
OR
p.label_sub LIKE '%" . $v . "%'
OR
p.compatibility LIKE '%" . $v . "%'
OR
p.ean LIKE '%" . $v . "%'
OR
p.number LIKE '%" . $v . "%'
OR
p.sku LIKE '%" . $v . "%'
)))";
}
}
}
}
}
/*foreach($search as $v){
$sql .= "\nAND ";
}*/
$sql_order = 'cp.pos asc'; // Default
$custom_order = false;
if(!empty($order)){
if(substr($order, 0, 3) == 'pos'){
if(preg_match('/asc|desc/', strtolower($order))){
$sql_order = "cp." . $order;
}else{
$sql_order = "cp." . $order . " " . $orderDir;
}
if (!str_contains($order, 'price_incl')) {
$sql_order .= " , cp.id " . $orderDir;
}
}else{
if(preg_match('/asc|desc/', strtolower($order))){
$sql_order = "p." . $order;
}else{
$sql_order = "p." . $order . " " . $orderDir;
}
if (!str_contains($order, 'price_incl')) {
$sql_order .= ", p.id " . $orderDir;
}
$custom_order = true;
}
}
if(!$custom_order && empty($category)){
$sql_order = "p.id desc";
}
if(!empty($filters['q']) && $sql_order == 'cp.pos asc'){
$fq = explode(' ', $filters['q']);
$sql_order = "";
foreach($fq as $fqp){
if(empty($sql_order)){
$sql_order .= "FIND_IN_SET('" . $fqp . "', p.label)";
}else{
$sql_order .= ",FIND_IN_SET('" . $fqp . "', p.label)";
}
}
}
// $sql .= "\nGROUP BY p.id";
$sql .= "\nORDER BY " . $sql_order;
// dump($sql);die();
/*if(isset($filters['dev']) && $filters['dev'] == true){
dump($sql);die();
}*/
// dump($sql);die();
if (!empty($relation)) {
/*dump($sql);die();
$query = $em->createQuery($sql);
echo ( '<pre>' . print_r( SqlFormatter::format($query->getSql()), 1 ) . '</pre>' );
dump(count($query->getResult()));
die();*/
}
// dump($sql);die();
/*$query = $em->createQuery($sql);
echo ( '<pre>' . print_r( SqlFormatter::format($query->getSql()), 1 ) . '</pre>' );
dump(count($query->getResult()));
die();*/
// dump($sql);die();
$query = $em->createQuery($sql);
// die($query->getSql());
if($debug){
die($query->getSql());
}
// $em = $this->getEntityManager();
/*$sql = "SELECT p
FROM TrinityWebshopBundle:Product p
" . implode("\n", $join) . "
" . implode("\n", $joinNorForCount) . "
" . ($Webshop ? "JOIN c.webshop w WITH w.id = " . $webshop_id : "") . "
WHERE p.id > 0
AND p.enabled = 1
AND
(
(
(p.type = 1 OR p.type = 2)
" . implode("\n", $filterValuesLinked) . "
)
OR
(
(p.type != 1 AND p.type != 2)
" . implode("\n", $filterValuesSimple) . "
)
)
AND c.label != 'Ongesorteerd'
" . ($featured ? "AND p.featured = 1" : "") . "
" . (!$ignoreStock ? "
AND ((p.type = 0 and ((p.stock_amount - p.out_of_stock_quantity) > 0 or p.stock = 0 or p.visible_no_stock = 1)) or p.type > 0)
AND ((p.type = 0 or p.type = 3 or p.type = 4) or ((p.type = 1 or p.type = 2) and p.child_stock_amount > 0))
" : "") .
(!empty($and) ? "
AND
(
" . implode("\nAND ", $and) . "
)
" : '') .
(!empty($or) ? "
AND
(
" . implode("\nOR ", $or) . "
)
" : '')
. (!empty($specFilter) ? "AND (" . implode("\nOR ", $specFilter) . ")" : "") . "
ORDER BY " . (!empty($order) ? (substr($order, 0, 3) == 'pos' ? "cp." . $order : "p." . $order) : "cp.pos asc");
$query = $em->createQuery($sql);*/
if(!empty($page)){
$start = (($page * $limit) - $limit);
}else if(!empty($filters['limit'])){
$start = (int)$filters['limit']['start'];
$limit = (int)$filters['limit']['limit'];
}
if($offset > 0){
$start = $offset;
}
if($noLimit == false){
$query->setMaxResults($limit)->setFirstResult($start);
}
// Doctrine Paginator experiment
$results = new \Doctrine\ORM\Tools\Pagination\Paginator($query, $fetchJoinCollection = true);
// $results->setUseOutputWalkers(false);
$total = count($results);
$resultCount = count($results->getIterator()->getArrayCopy());
$results = $results->getIterator()->getArrayCopy();
if(!empty($filters['filters'])){
// Walk through filtered results
foreach($results as $k => $res){
// Only for grouped or configurable products
if($res->getType() > 0){
// Walk through linked products
foreach($res->getLinkedProducts() as $lp){
// See if spec values are given to product
if($lp->getSpecValues()->count() > 0){
// Walk through spec values
foreach($lp->getSpecValues() as $sv){
$Spec = $sv->getSpec();
if($Spec){
try {
// Limit product display on colors
if($Spec->getType() == 'color' || $Spec->getCode() == 'color'){
if(array_key_exists($Spec->getId(), $filters['filters'])){
// If color matches the filtered colors
if(in_array($sv->getValue(), $filters['filters'][$Spec->getId()])){
// Set linked product as display product
$results[$k]->setDisplayProduct($lp);
break(3);
}
}
}
} catch (\Exception $ex) {
//dump($ex);
}
}
}
}
}
}
}
}
return [
'total' => $total,
'current' => $resultCount,
'start' => $start,
'limit' => $limit,
'pages' => (int)ceil($total / $limit),
'page' => (int)$page,
'results' => $results
];
}
public function getOneBy($slug = '', $Webshop, $Category = null){
$em = $this->getEntityManager();
$query = $this->createQueryBuilder('p')
->innerJoin('p.category', 'c')
->innerJoin('c.webshop', 'wp')
->where('wp.id = :webshopid')
->setParameter('webshopid', (!empty($Webshop) ? (!empty($Webshop) ? $Webshop->getId() : 0) : 0));
if(!empty($slug)) $query->andWhere('p.slug = \'' . $slug . '\'');
if($Category instanceof \App\Trinity\WebshopBundle\Entity\Category){
$query->andWhere('c.id = :categoryid')
->setParameter('categoryid', $Category->getId());
}
return $query->getQuery()->getSingleResult();
}
public function gatherProducts($cats){
$em = $this->getEntityManager();
$sql = "SELECT cp
FROM TrinityWebshopBundle:CategoryProduct cp
JOIN cp.category c
WHERE c.id IN (" . implode(', ', $cats) . ")";
$query = $em->createQuery($sql);
return $query->getResult();
}
public function recentlyChanged($Webshop, $limit = 4){
$em = $this->getEntityManager();
// $query = $em->createQuery("SELECT ph.date,a as Product FROM TrinityWebshopBundle:ProductHistory ph JOIN TrinityWebshopBundle:Product a WITH ph.Product = a JOIN a.category cp JOIN cp.category c WHERE (ph.label LIKE '%gewijzigd%') AND c.webshop = " . (!empty($Webshop) ? $Webshop->getId() : 0) . " GROUP BY ph.Product ORDER BY ph.date DESC");
$query = $em->createQuery("SELECT ph.date,a as Product FROM TrinityWebshopBundle:ProductHistory ph JOIN TrinityWebshopBundle:Product a WITH ph.Product = a JOIN a.category cp JOIN cp.category c WHERE (ph.label LIKE '%gewijzigd%') AND c.webshop = " . (!empty($Webshop) ? $Webshop->getId() : 0) . " ORDER BY ph.date DESC");
return $query->setMaxResults($limit)->getResult();
}
public function recentlyAdded($Webshop, $limit = 4){
$em = $this->getEntityManager();
// $query = $em->createQuery("SELECT ph.date,a as Product FROM TrinityWebshopBundle:ProductHistory ph JOIN TrinityWebshopBundle:Product a WITH ph.Product = a JOIN a.category cp JOIN cp.category c WHERE (ph.label LIKE '%geïmporteerd%' OR ph.label LIKE '%toegevoegd%') AND c.webshop = " . (!empty($Webshop) ? $Webshop->getId() : 0) . " GROUP BY ph.Product ORDER BY ph.date DESC");
$query = $em->createQuery("SELECT ph.date,a as Product FROM TrinityWebshopBundle:ProductHistory ph JOIN TrinityWebshopBundle:Product a WITH ph.Product = a JOIN a.category cp JOIN cp.category c WHERE (ph.label LIKE '%geïmporteerd%' OR ph.label LIKE '%toegevoegd%') AND c.webshop = " . (!empty($Webshop) ? $Webshop->getId() : 0) . " ORDER BY ph.date DESC");
return $query->setMaxResults($limit)->getResult();
}
public function recentReviews($Webshop, $limit = 4){
$em = $this->getEntityManager();
$query = $em->createQuery("SELECT r FROM TrinityWebshopBundle:Review r JOIN r.product p JOIN p.category cp JOIN cp.category c WHERE c.webshop = " . (!empty($Webshop) ? $Webshop->getId() : 0) . " GROUP BY r.id ORDER BY r.date DESC");
return $query->setMaxResults($limit)->getResult();
}
public function getReviewsByWebshop($Webshop){
$em = $this->getEntityManager();
$query = $em->createQuery("SELECT r FROM TrinityWebshopBundle:Review r JOIN r.product p JOIN p.category cp JOIN cp.category c WHERE c.webshop = " . (!empty($Webshop) ? $Webshop->getId() : 0) . " GROUP BY r.id ORDER BY r.date DESC");
return $query->getResult();
}
public function bestSold($Webshop, $limit = 4){
$em = $this->getEntityManager();
$query = $em->createQuery("SELECT p as Product, COUNT(op.id) AS num FROM TrinityWebshopBundle:Product p JOIN TrinityWebshopBundle:OrderProduct op WITH op.product = p GROUP BY p.id ORDER BY num DESC");
return $query->setMaxResults($limit)->getResult();
}
public function discounted($Webshop, $limit = 4){
$list = [];
$em = $this->getEntityManager();
$sql = "SELECT p
FROM TrinityWebshopBundle:Product p
JOIN p.promotions pm
WHERE (
pm.date_start IS NULL
OR
pm.date_start <= '" . date('Y-m-d H:i:s') . "'
)
AND (
pm.date_end IS NULL
OR
pm.date_end >= '" . date('Y-m-d H:i:s') . "'
)
GROUP BY p.id
";
$query = $em->createQuery($sql);
$list = $query->setMaxResults($limit)->getResult();
if(count($list) < $limit){
$sql = "SELECT p
FROM TrinityWebshopBundle:Product p
JOIN p.category c
JOIN c.promotions pm
WHERE (
pm.date_start IS NULL
OR
pm.date_start <= '" . date('Y-m-d H:i:s') . "'
)
AND (
pm.date_end IS NULL
OR
pm.date_end >= '" . date('Y-m-d H:i:s') . "'
)
AND c.webshop = " . (!empty($Webshop) ? $Webshop->getId() : 0) . "
GROUP BY p.id
";
$query = $em->createQuery($sql);
$list = array_merge($list, $query->setMaxResults($limit - count($list))->getResult());
}
return $list;
}
public function getByWebshop($Webshop){
$em = $this->getEntityManager();
$query = $em->createQuery(
'SELECT p
FROM TrinityWebshopBundle:Product p
JOIN p.category cp
JOIN cp.category c
JOIN c.webshop w
WHERE w.id LIKE :webshopid'
)->setParameter('webshopid', (!empty($Webshop) ? $Webshop->getId() : 0));
return $query->getResult();
}
public function getByDiscounted($Webshop, $limit = 9){
$sql = "SELECT p
FROM TrinityWebshopBundle:Product p
JOIN p.category cp
JOIN cp.category c
JOIN c.webshop w
WHERE w.id LIKE :webshopid
AND p.enabled = 1
AND p.visible = 1
AND p.discount = 1
ORDER BY p.label ASC";
echo ( '<pre>' . print_r( $sql, 1 ) . '</pre>' );
$em = $this->getEntityManager();
$query = $em->createQuery($sql)
->setParameter('webshopid', (!empty($Webshop) ? $Webshop->getId() : 0));
$query->setMaxResults($limit);
return $query->getResult();
}
public function findLowStock(){
$em = $this->getEntityManager();
$query = $em->createQuery(
"
SELECT p
FROM TrinityWebshopBundle:Product p
WHERE p.stock = 1
AND p.stock_amount < p.notify_on_stock_below
"
);
return $query->getResult();
}
public function findSimpleForLinking($Webshop, $q){
$em = $this->getEntityManager();
$query = $em->createQuery(
'
SELECT p
FROM TrinityWebshopBundle:Product p
JOIN p.category cp
JOIN cp.category c
JOIN c.webshop w
WHERE w.id LIKE :webshopid
AND p.label LIKE \'%' . $q . '%\'
AND p.type = 0
'
)->setParameter('webshopid', (!empty($Webshop) ? $Webshop->getId() : 0));
return $query->getResult();
}
public function findByLabelInWebshop($Webshop, $label){
$em = $this->getEntityManager();
$query = $em->createQuery(
'SELECT p
FROM TrinityWebshopBundle:Product p
JOIN p.category cp
JOIN cp.category c
JOIN c.webshop w
WHERE w.id LIKE :webshopid
AND p.label LIKE :label'
)
->setParameter('webshopid', (!empty($Webshop) ? $Webshop->getId() : 0))
->setParameter('label', $label);
return $query->getOneOrNullResult();
}
/**
* Count unlinked products (likely after language value added to product)
*
* @return integer
*/
public function countUnlinked(){
$sql = "SELECT COUNT(P)
FROM TrinityWebshopBundle:Product AS P
WHERE P.language IS NULL
";
return (int)$this->getEntityManager()->createQuery($sql)->getSingleScalarResult();
}
/**
* Count unlinked products (likely after language value added to product)
*
* @return integer
*/
public function countReviews($product_id, $page, $perpage, $moderate = false){
$sql = "SELECT COUNT(R)
FROM TrinityWebshopBundle:Review AS R
WHERE R.product = " . $product_id . "
AND R.parent IS NULL
" . ($moderate ? "AND R.moderated = 1" : '') . "
";
return (int)$this->getEntityManager()->createQuery($sql)->getSingleScalarResult();
}
/**
* Count unlinked products (likely after language value added to product)
*
* @return integer
*/
public function fetchReviews($product_id, $page, $perpage, $moderate = false){
$sql = "SELECT R
FROM TrinityWebshopBundle:Review AS R
WHERE R.product = " . $product_id . "
AND R.parent IS NULL
" . ($moderate ? "AND R.moderated = 1" : '') . "
ORDER BY R.date DESC
";
$query = $this->getEntityManager()->createQuery($sql);
$query->setMaxResults($perpage);
$query->setFirstResult(($page * $perpage) - $perpage);
return $query->getResult();
}
/**
* Get product labels based on base and optional parent ID
*
* @return array
*/
public function findLabelsByBase($webshop_id, $base_id, $parent_id = null){
$sql = "SELECT GROUP_CONCAT(CONCAT(P.id,'|',P.label)) AS list,GROUP_CONCAT(CONCAT(P.id,'|',R.id)) AS relations
FROM TrinityWebshopBundle:Product AS P
LEFT JOIN P.relation_to R
WHERE P.webshop = " . $webshop_id . "
AND P.base = " . $base_id . "
ORDER BY P.label ASC
";
// dump($sql);
$query = $this->getEntityManager()->createQuery($sql);
$result = $query->getResult();
// dump($result);die();
$return = [];
if(!empty($result[0]['list'])){
$return_raw = explode(',', $result[0]['list']);
foreach($return_raw as $r){
$r = explode('|', $r);
$return[$r[0]] = ['label' => $r[1], 'relation' => null];
}
}
if(!empty($result[0]['relations'])){
$return_raw = explode(',', $result[0]['relations']);
foreach($return_raw as $r){
$r = explode('|', $r);
if(!empty($return[$r[0]])){
$return[$r[0]]['relation'] = $r[1];
}
}
}
// dump($return);die();
return $return;
}
}