Symfony Profiler

<?php
namespace App\Trinity\WebshopBundle\Repository;
use Doctrine\ORM\EntityRepository;
/**
* OrderRepository
*/
class OrderRepository extends EntityRepository
{
public function countBy($Webshop){
$em = $this->getEntityManager();
$query = $em->createQuery(
'SELECT count(p)
FROM TrinityWebshopBundle:Order p
JOIN p.webshop w
WHERE w.id LIKE :webshopid'
)->setParameter('webshopid', (!empty($Webshop) ? $Webshop->getId() : null));
return $query->getSingleScalarResult();
}
public function findOpenToday(){
$em = $this->getEntityManager();
$time = strtotime('-7 DAY');
$query = $em->createQuery(
"
SELECT p
FROM TrinityWebshopBundle:Order p
WHERE p.payment_status = 'open'
OR (p.payment_status = '' OR p.payment_status = 'pending')
AND p.date > '" . date('Y-m-d H:i:s', $time) . "'
"
);
return $query->getResult();
}
public function findOrdersToNotifyPayment($Webshop, $WebshopSettings){
$em = $this->getEntityManager();
$time = strtotime('-' . $WebshopSettings->getAutoNotifyPeriod() . ' MINUTE');
$query = $em->createQuery(
"
SELECT p
FROM TrinityWebshopBundle:Order p
JOIN p.webshop w WITH (w.id = " . $Webshop->getId() . ")
WHERE p.status = ''
AND p.payment != 'banktransfer'
AND p.payment != 'BANKTRANS'
AND p.payment != 'invoice'
AND p.date_notify IS NULL
AND (
p.payment_status = 'open'
OR
p.payment_status IS NULL
)
AND p.date < '" . date('Y-m-d H:i:s', $time) . "'
"
);
return $query->getResult();
}
public function findOrdersToCancelAfterNotify($Webshop, $WebshopSettings){
$em = $this->getEntityManager();
$time = strtotime('-' . $WebshopSettings->getAutoCancelPeriod() . ' MINUTE');
$query = $em->createQuery(
"
SELECT p
FROM TrinityWebshopBundle:Order p
JOIN p.webshop w WITH (w.id = " . $Webshop->getId() . ")
WHERE p.status = ''
AND p.admin != 1
AND p.payment != 'banktransfer'
AND p.payment != 'BANKTRANS'
AND p.payment != 'invoice'
AND (
p.payment_status = 'open'
OR
p.payment_status = 'expired'
OR
p.payment_status = 'failure'
OR
p.payment_status IS NULL
)
AND (p.date_notify IS NULL OR p.date_notify < '" . date('Y-m-d H:i:s', $time) . "')
AND (p.date < '" . date('Y-m-d H:i:s', $time) . "')
"
);
return $query->getResult();
}
public function findTempOrdersToCancelAfterNotify($Webshop, $WebshopSettings){
$em = $this->getEntityManager();
$time = strtotime('-' . $WebshopSettings->getAutoCancelPeriod() . ' MINUTE');
$query = $em->createQuery(
"
SELECT p
FROM TrinityWebshopBundle:Order p
JOIN p.webshop w WITH (w.id = " . $Webshop->getId() . ")
WHERE p.status = ''
AND p.admin != 1
AND p.payment != 'banktransfer'
AND p.payment != 'BANKTRANS'
AND p.payment != 'invoice'
AND (
p.payment_status = 'open'
OR
p.payment_status = 'expired'
OR
p.payment_status = 'failure'
OR
p.payment_status = 'declined'
OR
p.payment_status IS NULL
)
AND (p.date_notify IS NULL OR p.date_notify > '" . date('Y-m-d H:i:s', $time) . "')
AND (p.date > '" . date('Y-m-d H:i:s', $time) . "')
"
);
return $query->getResult();
}
public function search($Webshop, $q){
$em = $this->getEntityManager();
$query = $em->createQuery(
'SELECT o
FROM TrinityWebshopBundle:Order o
JOIN o.webshop wp
LEFT JOIN o.user u
WHERE wp.id LIKE :webshopid
AND (
o.payment_id LIKE :q
OR
u.firstname LIKE :q
OR
u.lastname LIKE :q
)
ORDER BY o.date DESC'
)
->setParameter('webshopid', (!empty($Webshop) ? $Webshop->getId() : null))
->setParameter('q', '%' . $q . '%');
return $query->getResult();
}
public function filter($doCount = false, $Webshop = null, $offset = 0, $limit = 0, $filter = []){
$em = $this->getEntityManager();
$q = (!empty($filter) && !empty($filter['q']) ? $filter['q'] : null);
$status = (!empty($filter) && isset($filter['status']) && $filter['status'] != '' ? $filter['status'] : null);
$sort = (!empty($filter['sort']) && $filter['sort'] != '' ? $filter['sort'] : 'p.id');
$order = (!empty($filter['order']) && $filter['order'] == 'asc' ? 'asc' : 'desc');
// Force $q to array
if(!is_array($q)){
$q = explode(' ', $q);
// $q = [$q];
}
$search = [];
if(!empty($status)){
if($status == 'open'){
$search[] = "(p.status = '' or p.status is null)";
$search[] = "(p.payment_status != 'cancelled' or p.payment_status IS NULL)";
}else{
if($status == 'paid'){
$search[] = "(p.status != 'done' and p.status != 'send' and p.status != 'credit' and p.payment_status = 'paid' and p.status != 'cancelled')";
}else{
$search[] = "(p.status = '" . $status . "' or p.payment_status = '" . $status . "')";
if($status != 'cancelled'){
$search[] = "p.status != 'cancelled'";
}
}
}
}
if(!empty($filter['start'])){
$search[] = "p.date >= '" . $filter['start'] . " 00:00:00'";
}
if(!empty($filter['end'])){
$search[] = "p.date <= '" . $filter['end'] . " 23:59:59'";
}
if(!empty($q)){
foreach($q as $part){
if(!empty($part)){
$search[] = "
(
p.order_id LIKE '%" . $part . "%' OR
u.firstname LIKE '%" . $part . "%' OR
u.lastname LIKE '%" . $part . "%' OR
p.email LIKE '%" . $part . "%' OR
p.firstname LIKE '%" . $part . "%' OR
p.lastname LIKE '%" . $part . "%'
)
";
}
}
}
$sql = "
SELECT " . ($doCount ? "count(p)" : "p") . "
FROM TrinityWebshopBundle:Order p
" . ($Webshop ? "JOIN p.webshop w" : "") . "
LEFT JOIN p.user c
LEFT JOIN c.user u
WHERE 1 = 1
" . ($Webshop ? "AND w.id LIKE :webshopid" : "") . "
" . ($search ? "AND " . implode(" AND ", $search) : "") . "
ORDER BY {$sort} {$order}
";
$query = $em->createQuery($sql);
if($Webshop){
$query = $query->setParameter('webshopid', (!empty($Webshop) ? $Webshop->getId() : null));
}
if($doCount){
return $query->getSingleScalarResult();
}
return $query->setFirstResult($offset)->setMaxResults($limit)->getResult();
}
public function getBy($Webshop, $offset, $limit){
$em = $this->getEntityManager();
$query = $em->createQuery(
'SELECT p
FROM TrinityWebshopBundle:Order p
JOIN p.webshop w
WHERE w.id LIKE :webshopid
ORDER BY p.date DESC'
)->setParameter('webshopid', (!empty($Webshop) ? $Webshop->getId() : null));
return $query->setFirstResult($offset)->setMaxResults($limit)->getResult();
}
public function thisMonth($Webshop, $date_range){
$stats = [
'earned' => 0,
'orders' => 0,
'visitors' => 0,
'page-views' => 0,
];
$em = $this->getEntityManager();
$sql = "SELECT SUM(o.total_price) as total_price,
COUNT(o.id) as num
FROM TrinityWebshopBundle:Order o
WHERE (o.date BETWEEN '" . $date_range[0] . "' AND '" . $date_range[1] . "')
AND (o.payment_status != 'expired'
AND o.payment_status != 'cancelled'
AND o.status != 'cancelled')
AND o.webshop " . (!empty($Webshop) ? '= ' . $Webshop->getId() : 'is null') . "
";
$query = $em->createQuery($sql);
$res = $query->getSingleResult();
$stats['earned'] = (float)$res['total_price'];
$stats['orders'] = (float)$res['num'];
return $stats;
}
public function recent($Webshop, $limit = 4, $date_range){
$em = $this->getEntityManager();
$query = $em->createQuery("SELECT o FROM TrinityWebshopBundle:Order o WHERE (o.date BETWEEN '" . $date_range[0] . "' AND '" . $date_range[1] . "') AND o.webshop " . (!empty($Webshop) ? '= ' . $Webshop->getId() : 'is null') . " ORDER BY o.date DESC");
return $query->setMaxResults($limit)->getResult();
}
public function today($Webshop){
$em = $this->getEntityManager();
$query = $em->createQuery("SELECT o FROM TrinityWebshopBundle:Order o WHERE o.date LIKE '" . date('Y-m-d') . "%' AND o.webshop " . (!empty($Webshop) ? '= ' . $Webshop->getId() : 'is null') . " ORDER BY o.date ASC");
return $query->getResult();
}
public function day($Webshop){
$em = $this->getEntityManager();
$query = $em->createQuery("SELECT o FROM TrinityWebshopBundle:Order o WHERE o.date LIKE '" . date('Y-m-d') . "%' AND o.webshop " . (!empty($Webshop) ? '= ' . $Webshop->getId() : 'is null') . " ORDER BY o.date ASC");
return $query->getResult();
}
public function month($Webshop){
$em = $this->getEntityManager();
$query = $em->createQuery("SELECT o FROM TrinityWebshopBundle:Order o WHERE o.date LIKE '" . date('Y-m') . "%' AND o.webshop " . (!empty($Webshop) ? '= ' . $Webshop->getId() : 'is null') . " AND o.status != 'cancelled' ORDER BY o.date ASC");
return $query->getResult();
}
public function year($Webshop){
$em = $this->getEntityManager();
$query = $em->createQuery("SELECT o FROM TrinityWebshopBundle:Order o WHERE o.date LIKE '" . date('Y') . "%' AND o.webshop " . (!empty($Webshop) ? '= ' . $Webshop->getId() : 'is null') . " AND o.status != 'cancelled' ORDER BY o.date ASC");
return $query->getResult();
}
public function open($Webshop, $limit = 4, $date_range){
$em = $this->getEntityManager();
$query = $em->createQuery("SELECT o FROM TrinityWebshopBundle:Order o WHERE (o.payment_status = 'open' or o.payment_status = 'banktransfer' or o.payment_status = 'BANKTRANS' or o.payment_status = '' or o.payment_status IS NULL) AND (o.date BETWEEN '" . $date_range[0] . "' AND '" . $date_range[1] . "') AND o.webshop " . (!empty($Webshop) ? '= ' . $Webshop->getId() : 'is null') . " ORDER BY o.date DESC");
return $query->setMaxResults($limit)->getResult();
}
public function paid($Webshop, $limit = 4, $date_range){
$em = $this->getEntityManager();
$query = $em->createQuery("SELECT o FROM TrinityWebshopBundle:Order o WHERE (o.payment_status = 'paid') AND (o.date BETWEEN '" . $date_range[0] . "' AND '" . $date_range[1] . "') AND o.webshop " . (!empty($Webshop) ? '= ' . $Webshop->getId() : 'is null') . " ORDER BY o.date DESC");
return $query->setMaxResults($limit)->getResult();
}
public function profit($Webshop, $date_range){
$em = $this->getEntityManager();
$sql = "SELECT (op.price_excl * op.amount) AS earnings,
((op.price_excl - op.price_in) * op.amount) AS profit,
o.total_delivery_excl as deliverycost,
o.id as orderid,
((((op.price - op.price_in) * op.amount) / (op.price * op.amount)) * 100) AS percentage
FROM TrinityWebshopBundle:OrderProduct op
JOIN TrinityWebshopBundle:Product p WITH (op.product = p)
JOIN TrinityWebshopBundle:Order o WITH (op.order = o)
WHERE (o.payment_status != 'expired' AND o.payment_status != 'cancelled' AND o.status != 'credit')
AND (o.date BETWEEN '" . $date_range[0] . "' AND '" . $date_range[1] . "')
AND o.webshop " . (!empty($Webshop) ? '= ' . $Webshop->getId() : 'is null') . "
";
$query = $em->createQuery($sql);
$result = $query->getResult();
$orders = [];
$data = ['profit' => 0, 'earnings' => 0, 'profitnoshipping' => 0, 'earningsnoshipping' => 0, 'percentage' => 0];
foreach($result as $r){
$data['profit'] += round($r['profit'], 2);
$data['earnings'] += round($r['earnings'], 2);
$data['earningsnoshipping'] += round($r['earnings'], 2);
$data['profitnoshipping'] += round($r['profit'], 2);
if(!in_array($r['orderid'], $orders)){
$data['profit'] += round($r['deliverycost'], 2);
$data['earnings'] += round($r['deliverycost'], 2);
array_push($orders, $r['orderid']);
}
}
if(!empty($data['profitnoshipping']) || !empty($data['profitnoshipping'])){
$data['percentage'] = (!empty($data['profit'] ) ? (($data['profitnoshipping'] / $data['earningsnoshipping']) * 100) : 0);
} else {
$data['percentage'] = 0.0;
}
return $data;
}
public function bestSold($Webshop, $limit = 4, $date_range){
$em = $this->getEntityManager();
$query = $em->createQuery("SELECT COUNT(op.id) as num, p as Product FROM TrinityWebshopBundle:OrderProduct op JOIN TrinityWebshopBundle:Order o WITH o = op.order JOIN TrinityWebshopBundle:Product p WITH p = op.product WHERE (o.date BETWEEN '" . $date_range[0] . "' AND '" . $date_range[1] . "') AND o.webshop " . (!empty($Webshop) ? '= ' . $Webshop->getId() : 'is null') . " GROUP BY op.product ORDER BY num DESC");
return $query->setMaxResults($limit)->getResult();
}
public function filterGuests($doCount = false, $Webshop = null, $offset = 0, $limit = 0, $filter = []){
$q = (!empty($filter) && !empty($filter['q']) ? $filter['q'] : null);
$sort = (!empty($filter['sort']) && $filter['sort'] != '' ? $filter['sort'] : 'P.id');
$order = (!empty($filter['order']) && $filter['order'] == 'desc' ? 'desc' : 'asc');
$em = $this->getEntityManager();
// Force $q to array
if(!is_array($q)){
$q = explode(' ', $q);
// $q = [$q];
}
$queries = [];
foreach($q as $part){
if(empty($part)) continue;
$queries[] = "
(
P.firstname LIKE '%" . $part . "%' OR
P.lastname LIKE '%" . $part . "%' OR
P.email LIKE '%" . $part . "%' OR
P.street LIKE '%" . $part . "%' OR
P.postalcode LIKE '%" . $part . "%' OR
P.city LIKE '%" . $part . "%' OR
P.company LIKE '%" . $part . "%' OR
P.country LIKE '%" . $part . "%'
)
";
}
$sql = "
SELECT " . ($doCount ? "COUNT(DISTINCT P.email)" : "P") . "
FROM TrinityWebshopBundle:Order P
WHERE P.guest = 1
" . (!empty($queries) ? " AND " . implode(" AND ", $queries) : "") . "
" . (!$doCount ? "
GROUP BY P.email
ORDER BY {$sort} {$order}
" : "") . "
";
// if(!$doCount)die( "<pre>" . print_r( $sql, 1 ) . "</pre>" );
$query = $em->createQuery($sql);
// if(!$doCount)die( "<pre>" . print_r( $query->getSql(), 1 ) . "</pre>" );
if($doCount){
return $query->getSingleScalarResult();
}
return $query->setFirstResult($offset)->setMaxResults($limit)->getResult();
}
public function findPossibleMatchingCustomer(){
$em = $this->getEntityManager();
$sql = "
SELECT O.id as order_id,O.lastname as order_lastname,O.email as order_email,WU.id as user_id,U.lastname as user_lastname,U.email as user_email
FROM TrinityWebshopBundle:Order O
JOIN CmsBundle:User U WITH (U.email = O.email)
JOIN TrinityWebshopBundle:User WU WITH (WU.user = U)
WHERE O.user IS NULL
";
$query = $em->createQuery($sql);
return $query->getResult();
}
public function findGuestWhileNotGuest(){
$em = $this->getEntityManager();
$sql = "
SELECT O
FROM TrinityWebshopBundle:Order O
JOIN O.user WU
JOIN WU.user U
WHERE U.username != 'guest'
AND O.guest = 1
";
$query = $em->createQuery($sql);
return $query->getResult();
}
public function findLastOrderidWithPrefix($prefix){
$em = $this->getEntityManager();
$sql = "
SELECT O
FROM TrinityWebshopBundle:Order O
WHERE O.order_id LIKE '{$prefix}%'
ORDER BY O.order_id DESC
";
$query = $em->createQuery($sql);
return $query->setMaxResults(1)->getResult();
}
public function findPossibleUserMismatch(){
$em = $this->getEntityManager();
$sql = "
SELECT O
FROM TrinityWebshopBundle:Order O
JOIN O.user WU
JOIN WU.user U
WHERE U.email != O.email
AND U.username != 'guest'
AND U.firstname != O.firstname
AND U.lastname != O.lastname
";
$query = $em->createQuery($sql);
return $query->getResult();
}
/*public function filterAll($doCount = false, $Webshop = null, $offset = 0, $limit = 0, $filter = []){
$q = (!empty($filter) && !empty($filter['q']) ? $filter['q'] : null);
$sort = (!empty($filter['sort']) && $filter['sort'] != '' ? $filter['sort'] : 'P.id');
$order = (!empty($filter['order']) && $filter['order'] == 'desc' ? 'desc' : 'asc');
$em = $this->getEntityManager();
// Force $q to array
if(!is_array($q)){
$q = [$q];
}
$queries = [];
foreach($q as $part){
if(empty($part)) continue;
$queries[] = "
(
P.firstname LIKE '%" . $part . "%' OR
P.lastname LIKE '%" . $part . "%' OR
P.email LIKE '%" . $part . "%' OR
P.street LIKE '%" . $part . "%' OR
P.postalcode LIKE '%" . $part . "%' OR
P.city LIKE '%" . $part . "%' OR
P.company LIKE '%" . $part . "%' OR
P.country LIKE '%" . $part . "%'
)
";
}
$sql = "
SELECT P.id AS tmpid, CONCAT(P.firstname, ' ', P.lastname) as name, P.email, CONCAT('/', T.id, '/', P.id) AS uri
FROM TrinityWebshopBundle:Order P
JOIN P.user as PT
JOIN PT.user as T
WHERE P.guest = 1
" . (!empty($queries) ? " AND " . implode(" AND ", $queries) : "") . "
UNION
SELECT U.id AS tmpid, CONCAT(T.firstname, ' ', T.lastname) as name, U.email, CONCAT('/', U.id) AS uri
FROM TrinityWebshopBundle:User U
JOIN U.user T
ORDER BY `tmpid` asc
";
// if(!$doCount){ dump($sql);die(); }
// if(!$doCount)die( "<pre>" . print_r( $sql, 1 ) . "</pre>" );
$query = $em->createQuery($sql);
// if(!$doCount)die( "<pre>" . print_r( $query->getSql(), 1 ) . "</pre>" );
if($doCount){
return $query->getSingleScalarResult();
}
return $query->setFirstResult($offset)->setMaxResults($limit)->getResult();
}*/
}

src/Trinity/WebshopBundle/Repository/OrderRepository.php line 275