/** * @dataProvider addDatePartsSelectProvider */ public function testAddDatePartsSelect($start, $end, $expects) { $start = new \DateTime($start, new \DateTimeZone('UTC')); $end = new \DateTime($end, new \DateTimeZone('UTC')); $queryBuilder = new QueryBuilder($this->getTestEntityManager()); $queryBuilder->select('id')->from('Oro\\Bundle\\DashboardBundle\\Tests\\Unit\\Fixtures\\FirstTestBundle\\Entity\\TestEntity', 't'); $this->helper->addDatePartsSelect($start, $end, $queryBuilder, 't.createdAt'); $this->assertEquals($expects, $queryBuilder->getDQL()); }
/** * Returns data grouped by created_at, data_channel_id * * @param AclHelper $aclHelper * @param DateHelper $dateHelper * @param \DateTime $dateFrom * @param \DateTime $dateTo * @param array $ids Filter by channel ids * * @return array */ public function getGroupedByChannelArray(AclHelper $aclHelper, DateHelper $dateHelper, \DateTime $dateFrom, \DateTime $dateTo = null, $ids = []) { $qb = $this->createQueryBuilder('c'); $qb->select('COUNT(c) as cnt', 'IDENTITY(c.dataChannel) as channelId'); $dateHelper->addDatePartsSelect($dateFrom, $dateTo, $qb, 'c.createdAt'); if ($dateTo) { $qb->andWhere($qb->expr()->between('c.createdAt', ':dateFrom', ':dateTo'))->setParameter('dateTo', $dateTo); } else { $qb->andWhere('c.createdAt > :dateFrom'); } $qb->setParameter('dateFrom', $dateFrom); $qb->addGroupBy('c.dataChannel'); if ($ids) { $qb->andWhere($qb->expr()->in('c.dataChannel', ':channelIds'))->setParameter('channelIds', $ids); } return $aclHelper->apply($qb)->getArrayResult(); }
/** * @param AclHelper $aclHelper * @param DateHelper $dateHelper * @param DateTime $from * @param DateTime|null $to * * @return array */ public function getRevenueOverTime(AclHelper $aclHelper, DateHelper $dateHelper, DateTime $from, DateTime $to = null) { $from = clone $from; $to = clone $to; $qb = $this->createQueryBuilder('o')->select('SUM( CASE WHEN o.subtotalAmount IS NOT NULL THEN o.subtotalAmount ELSE 0 END - CASE WHEN o.discountAmount IS NOT NULL THEN ABS(o.discountAmount) ELSE 0 END ) AS amount'); $dateHelper->addDatePartsSelect($from, $to, $qb, 'o.createdAt'); if ($to) { $qb->andWhere($qb->expr()->between('o.createdAt', ':from', ':to'))->setParameter('to', $to); } else { $qb->andWhere('o.createdAt > :from'); } $qb->setParameter('from', $from); $this->applyActiveChannelLimitation($qb); return $aclHelper->apply($qb)->getResult(); }