/**
* Fetch Lead stats for some period of time.
*
* @param integer $quantity of units
* @param string $unit of time php.net/manual/en/class.dateinterval.php#dateinterval.props
* @param array $options
*
* @return mixed
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function getLeadStatsPost($dateFrom, $dateTo, $options)
{
$chartQuery = new ChartQuery($this->getEntityManager()->getConnection(), $dateFrom, $dateTo);
// Load points for selected period
$q = $this->_em->getConnection()->createQueryBuilder();
$q->select('cl.post_count, cl.post_date')->from(MAUTIC_TABLE_PREFIX . 'monitor_post_count', 'cl')->orderBy('cl.post_date', 'ASC');
if (isset($options['monitor_id'])) {
$q->andwhere($q->expr()->eq('cl.monitor_id', (int) $options['monitor_id']));
}
$chartQuery->applyDateFilters($q, 'post_date', 'cl');
$postCount = $q->execute()->fetchAll();
return $postCount;
}
/**
* @param null $channel
* @param null $ids
* @param null $reason
* @param null $listId
* @param ChartQuery|null $chartQuery
*
* @return array|int
*/
public function getCount($channel = null, $ids = null, $reason = null, $listId = null, ChartQuery $chartQuery = null)
{
$q = $this->_em->getConnection()->createQueryBuilder();
$q->select('count(dnc.id) as dnc_count')->from(MAUTIC_TABLE_PREFIX . 'lead_donotcontact', 'dnc');
if ($ids) {
if (!is_array($ids)) {
$ids = [(int) $ids];
}
$q->where($q->expr()->in('dnc.channel_id', $ids));
}
if ($channel) {
$q->andWhere('dnc.channel = :channel')->setParameter('channel', $channel);
}
if ($reason) {
$q->andWhere('dnc.reason = :reason')->setParameter('reason', $reason);
}
if ($listId) {
$q->leftJoin('dnc', MAUTIC_TABLE_PREFIX . 'lead_lists_leads', 'cs', 'cs.lead_id = dnc.lead_id');
if (true === $listId) {
$q->addSelect('cs.leadlist_id')->groupBy('cs.leadlist_id');
} else {
$q->andWhere('cs.leadlist_id = :list_id')->setParameter('list_id', $listId);
}
}
if ($chartQuery) {
$chartQuery->applyDateFilters($q, 'date_added', 'dnc');
}
$results = $q->execute()->fetchAll();
if (true === $listId) {
// Return list group of counts
$byList = [];
foreach ($results as $result) {
$byList[$result['leadlist_id']] = $result['dnc_count'];
}
return $byList;
}
return isset($results[0]) ? $results[0]['dnc_count'] : 0;
}
/**
* Get a list of the most submisions per lead
*
* @param integer $limit
* @param string $dateFrom
* @param string $dateTo
* @param array $filters
* @param boolean $canViewOthers
*
* @return array
*/
public function getTopSubmitters($limit = 10, $dateFrom = null, $dateTo = null, $filters = array(), $canViewOthers = true)
{
$q = $this->em->getConnection()->createQueryBuilder();
$q->select('COUNT(DISTINCT t.id) AS submissions, t.lead_id, l.firstname, l.lastname, l.email')->from(MAUTIC_TABLE_PREFIX . 'form_submissions', 't')->join('t', MAUTIC_TABLE_PREFIX . 'leads', 'l', 'l.id = t.lead_id')->orderBy('submissions', 'DESC')->groupBy('t.lead_id, l.firstname, l.lastname, l.email')->setMaxResults($limit);
if (!$canViewOthers) {
$q->join('t', MAUTIC_TABLE_PREFIX . 'forms', 'f', 'f.id = t.form_id')->andWhere('f.created_by = :userId')->setParameter('userId', $this->factory->getUser()->getId());
}
$chartQuery = new ChartQuery($this->em->getConnection(), $dateFrom, $dateTo);
$chartQuery->applyFilters($q, $filters);
$chartQuery->applyDateFilters($q, 'date_submitted');
$results = $q->execute()->fetchAll();
return $results;
}
/**
* Get hit count
*
* @param $channel
* @param $channelIds
* @param $listId
* @param ChartQuery|null $chartQuery
*
* @return array|int
*/
public function getCount($channel, $channelIds, $listId, ChartQuery $chartQuery = null)
{
$q = $this->_em->getConnection()->createQueryBuilder()->select('count(DISTINCT(cut.redirect_id)) as click_count')->from(MAUTIC_TABLE_PREFIX . 'channel_url_trackables', 'cut')->leftJoin('cut', MAUTIC_TABLE_PREFIX . 'page_hits', 'ph', 'ph.redirect_id = cut.redirect_id');
$q->where('cut.channel = :channel')->setParameter('channel', $channel);
if ($channelIds) {
if (!is_array($channelIds)) {
$channelIds = array((int) $channelIds);
}
$q->where($q->expr()->in('cut.channel_id', $channelIds));
}
if ($listId) {
$q->leftJoin('ph', MAUTIC_TABLE_PREFIX . 'lead_lists_leads', 'cs', 'cs.lead_id = ph.lead_id');
if (true === $listId) {
$q->addSelect('cs.leadlist_id')->groupBy('cs.leadlist_id');
} else {
$q->andWhere('cs.leadlist_id = :list_id')->setParameter('list_id', $listId);
}
}
if ($chartQuery) {
$chartQuery->applyDateFilters($q, 'date_hit', 'ph');
}
$results = $q->execute()->fetchAll();
if (true === $listId) {
// Return array of results
$byList = [];
foreach ($results as $result) {
$byList[$result['leadlist_id']] = $result['click_count'];
}
return $byList;
}
return isset($results[0]) ? $results[0]['click_count'] : 0;
}
/**
* Get a list of emails in a date range
*
* @param integer $limit
* @param \DateTime $dateFrom
* @param \DateTime $dateTo
* @param array $filters
* @param array $options
*
* @return array
*/
public function getEmailList($limit = 10, \DateTime $dateFrom = null, \DateTime $dateTo = null, $filters = [], $options = [])
{
$q = $this->em->getConnection()->createQueryBuilder();
$q->select('t.id, t.name, t.date_added, t.date_modified')->from(MAUTIC_TABLE_PREFIX . 'emails', 't')->setMaxResults($limit);
if (!empty($options['canViewOthers'])) {
$q->andWhere('t.created_by = :userId')->setParameter('userId', $this->user->getId());
}
$chartQuery = new ChartQuery($this->em->getConnection(), $dateFrom, $dateTo);
$chartQuery->applyFilters($q, $filters);
$chartQuery->applyDateFilters($q, 'date_added');
$results = $q->execute()->fetchAll();
return $results;
}
/**
* Get a list of leads in a date range
*
* @param integer $limit
* @param DateTime $dateFrom
* @param DateTime $dateTo
* @param array $filters
* @param array $options
*
* @return array
*/
public function getLeadList($limit = 10, \DateTime $dateFrom = null, \DateTime $dateTo = null, $filters = array(), $options = array())
{
if (!empty($options['canViewOthers'])) {
$filter['owner_id'] = $this->factory->getUser()->getId();
}
$q = $this->em->getConnection()->createQueryBuilder();
$q->select('t.id, t.firstname, t.lastname, t.email, t.date_added, t.date_modified')->from(MAUTIC_TABLE_PREFIX . 'leads', 't')->setMaxResults($limit);
$chartQuery = new ChartQuery($this->em->getConnection(), $dateFrom, $dateTo);
$chartQuery->applyFilters($q, $filters);
$chartQuery->applyDateFilters($q, 'date_added');
$results = $q->execute()->fetchAll();
if ($results) {
foreach ($results as &$result) {
if ($result['firstname'] || $result['lastname']) {
$result['name'] = trim($result['firstname'] . ' ' . $result['lastname']);
} elseif ($result['email']) {
$result['name'] = $result['email'];
} else {
$result['name'] = 'anonymous';
}
unset($result['firstname']);
unset($result['lastname']);
unset($result['email']);
}
}
return $results;
}
/**
* Get a list of top (by leads added) lists
*
* @param integer $limit
* @param string $dateFrom
* @param string $dateTo
* @param array $filters
*
* @return array
*/
public function getTopLists($limit = 10, $dateFrom = null, $dateTo = null, $filters = array())
{
$q = $this->em->getConnection()->createQueryBuilder();
$q->select('COUNT(t.date_added) AS leads, ll.id, ll.name')->from(MAUTIC_TABLE_PREFIX . 'lead_lists_leads', 't')->join('t', MAUTIC_TABLE_PREFIX . 'lead_lists', 'll', 'll.id = t.leadlist_id')->orderBy('leads', 'DESC')->where($q->expr()->eq('ll.is_published', ':published'))->setParameter('published', true)->groupBy('ll.id')->setMaxResults($limit);
if (!empty($options['canViewOthers'])) {
$q->andWhere('ll.created_by = :userId')->setParameter('userId', $this->user->getId());
}
$chartQuery = new ChartQuery($this->em->getConnection(), $dateFrom, $dateTo);
$chartQuery->applyFilters($q, $filters);
$chartQuery->applyDateFilters($q, 'date_added');
$results = $q->execute()->fetchAll();
return $results;
}
/**
* @param null $emailIds
* @param null $listId
* @param ChartQuery|null $chartQuery
*
* @return array|int
*/
public function getFailedCount($emailIds = null, $listId = null, ChartQuery $chartQuery = null)
{
$q = $this->_em->getConnection()->createQueryBuilder();
$q->select('count(s.id) as failed_count')->from(MAUTIC_TABLE_PREFIX . 'email_stats', 's');
if ($emailIds) {
if (!is_array($emailIds)) {
$emailIds = [(int) $emailIds];
}
$q->where($q->expr()->in('s.email_id', $emailIds));
}
if (true === $listId) {
$q->addSelect('s.list_id')->groupBy('s.list_id');
} elseif ($listId) {
$q->andWhere('s.list_id = ' . (int) $listId);
}
$q->andWhere('is_failed = :true')->setParameter('true', true, 'boolean');
if ($chartQuery) {
$chartQuery->applyDateFilters($q, 'date_sent', 's');
}
$results = $q->execute()->fetchAll();
if (true === $listId) {
// Return list group of counts
$byList = [];
foreach ($results as $result) {
$byList[$result['list_id']] = $result['failed_count'];
}
return $byList;
}
return isset($results[0]) ? $results[0]['failed_count'] : 0;
}
/**
* Get a list of pages created in a date range
*
* @param integer $limit
* @param DateTime $dateFrom
* @param DateTime $dateTo
* @param array $filters
* @param boolean $canViewOthers
*
* @return array
*/
public function getPageList($limit = 10, \DateTime $dateFrom = null, \DateTime $dateTo = null, $filters = array(), $canViewOthers = true)
{
$q = $this->em->getConnection()->createQueryBuilder();
$q->select('t.id, t.title AS name, t.date_added, t.date_modified')->from(MAUTIC_TABLE_PREFIX . 'pages', 't')->setMaxResults($limit);
if (!$canViewOthers) {
$q->andWhere('t.created_by = :userId')->setParameter('userId', $this->factory->getUser()->getId());
}
$chartQuery = new ChartQuery($this->em->getConnection(), $dateFrom, $dateTo);
$chartQuery->applyFilters($q, $filters);
$chartQuery->applyDateFilters($q, 'date_added');
$results = $q->execute()->fetchAll();
return $results;
}
