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