/**
* @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();
}
