/**
* 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 line chart data of stages.
*
* @param char $unit {@link php.net/manual/en/function.date.php#refsect1-function.date-parameters}
* @param DateTime $dateFrom
* @param DateTime $dateTo
* @param string $dateFormat
* @param array $filter
* @param bool $canViewOthers
*
* @return array
*/
public function getStageLineChartData($unit, \DateTime $dateFrom, \DateTime $dateTo, $dateFormat = null, $filter = [], $canViewOthers = true)
{
$chart = new LineChart($unit, $dateFrom, $dateTo, $dateFormat);
$query = new ChartQuery($this->em->getConnection(), $dateFrom, $dateTo);
$q = $query->prepareTimeDataQuery('lead_stages_change_log', 'date_added', $filter);
if (!$canViewOthers) {
$q->join('t', MAUTIC_TABLE_PREFIX . 'leads', 'l', 'l.id = t.lead_id')->andWhere('l.owner_id = :userId')->setParameter('userId', $this->factory->getUser()->getId());
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->factory->getTranslator()->trans('mautic.stage.changes'), $data);
return $chart->render();
}
/**
* 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;
}
public function getLifeCycleSegmentChartData($unit, \DateTime $dateFrom, \DateTime $dateTo, $dateFormat = null, $filter = array(), $canViewOthers = true, $listName)
{
$chart = new PieChart();
$query = new ChartQuery($this->em->getConnection(), $dateFrom, $dateTo);
if (!$canViewOthers) {
$filter['owner_id'] = $this->user->getId();
}
if (isset($filter['flag'])) {
unset($filter['flag']);
}
$allLists = $query->getCountQuery('leads', 'id', 'date_added', null);
$lists = $query->count('leads', 'id', 'date_added', $filter, null);
$all = $query->fetchCount($allLists);
$identified = $lists;
$chart->setDataset($listName, $identified);
if (isset($filter['leadlist_id']['value'])) {
$chart->setDataset($this->translator->trans('mautic.lead.lifecycle.graph.pie.all.lists'), $all);
}
return $chart->render(false);
}
/**
* @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 line chart data of campaign events.
*
* @param char $unit {@link php.net/manual/en/function.date.php#refsect1-function.date-parameters}
* @param \DateTime $dateFrom
* @param \DateTime $dateTo
* @param string $dateFormat
* @param array $filter
* @param bool $canViewOthers
*
* @return array
*/
public function getEventLineChartData($unit, \DateTime $dateFrom, \DateTime $dateTo, $dateFormat = null, $filter = [], $canViewOthers = true)
{
$chart = new LineChart($unit, $dateFrom, $dateTo, $dateFormat);
$query = new ChartQuery($this->em->getConnection(), $dateFrom, $dateTo);
$q = $query->prepareTimeDataQuery('campaign_lead_event_log', 'date_triggered', $filter);
if (!$canViewOthers) {
$q->join('t', MAUTIC_TABLE_PREFIX . 'campaigns', 'c', 'c.id = c.campaign_id')->andWhere('c.created_by = :userId')->setParameter('userId', $this->userHelper->getUser()->getId());
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->translator->trans('mautic.campaign.triggered.events'), $data);
return $chart->render();
}
/**
* Get line chart data of hits.
*
* @param char $unit {@link php.net/manual/en/function.date.php#refsect1-function.date-parameters}
* @param \DateTime $dateFrom
* @param \DateTime $dateTo
* @param string $dateFormat
* @param array $filter
* @param bool $canViewOthers
*
* @return array
*/
public function getHitsLineChartData($unit, \DateTime $dateFrom, \DateTime $dateTo, $dateFormat = null, $filter = [], $canViewOthers = true)
{
$flag = null;
if (isset($filter['flag'])) {
$flag = $filter['flag'];
unset($filter['flag']);
}
$chart = new LineChart($unit, $dateFrom, $dateTo, $dateFormat);
$query = new ChartQuery($this->em->getConnection(), $dateFrom, $dateTo);
if (!$flag || $flag === 'total_and_unique') {
$q = $query->prepareTimeDataQuery('push_notification_stats', 'date_sent', $filter);
if (!$canViewOthers) {
$this->limitQueryToCreator($q);
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->translator->trans('mautic.notification.show.total.sent'), $data);
}
return $chart->render();
}
/**
* Get line chart data of hits
*
* @param char $unit {@link php.net/manual/en/function.date.php#refsect1-function.date-parameters}
* @param DateTime $dateFrom
* @param DateTime $dateTo
* @param string $dateFormat
* @param array $filter
*
* @return array
*/
public function getCampaignMetricsLineChartData($unit, \DateTime $dateFrom, \DateTime $dateTo, $dateFormat = null, $filter = array())
{
$events = array();
$chart = new LineChart($unit, $dateFrom, $dateTo, $dateFormat);
$query = new ChartQuery($this->em->getConnection(), $dateFrom, $dateTo);
$contacts = $query->fetchTimeData('campaign_leads', 'date_added', $filter);
$chart->setDataset($this->translator->trans('mautic.campaign.campaign.leads'), $contacts);
if (isset($filter['campaign_id'])) {
$rawEvents = $this->getEventRepository()->getCampaignEvents($filter['campaign_id']);
// Group events by type
if ($rawEvents) {
foreach ($rawEvents as $event) {
if (isset($events[$event['type']])) {
$events[$event['type']][] = $event['id'];
} else {
$events[$event['type']] = array($event['id']);
}
}
}
if ($events) {
foreach ($events as $type => $eventIds) {
$filter['event_id'] = $eventIds;
$q = $query->prepareTimeDataQuery('campaign_lead_event_log', 'date_triggered', $filter);
$rawData = $q->execute()->fetchAll();
if (!empty($rawData)) {
$triggers = $query->completeTimeData($rawData);
$chart->setDataset($this->translator->trans('mautic.campaign.' . $type), $triggers);
}
}
unset($filter['event_id']);
}
}
return $chart->render();
}
/**
* 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->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;
}
/**
* Get line chart data of hits
*
* @param char $unit {@link php.net/manual/en/function.date.php#refsect1-function.date-parameters}
* @param \DateTime $dateFrom
* @param \DateTime $dateTo
* @param string $dateFormat
* @param array $filter
* @param boolean $canViewOthers
*
* @return array
*/
public function getHitsLineChartData($unit, \DateTime $dateFrom, \DateTime $dateTo, $dateFormat = null, $filter = array(), $canViewOthers = true)
{
$flag = null;
if (isset($filter['flag'])) {
$flag = $filter['flag'];
unset($filter['flag']);
}
$chart = new LineChart($unit, $dateFrom, $dateTo, $dateFormat);
$query = new ChartQuery($this->em->getConnection(), $dateFrom, $dateTo);
if (!$flag || $flag === 'total_and_unique') {
$q = $query->prepareTimeDataQuery('dynamic_content_stats', 'date_sent', $filter);
if (!$canViewOthers) {
$this->limitQueryToCreator($q);
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->translator->trans('mautic.dynamicContent.show.total.views'), $data);
}
if ($flag === 'unique' || $flag === 'total_and_unique') {
$q = $query->prepareTimeDataQuery('dynamic_content_stats', 'date_sent', $filter);
$q->groupBy('t.lead_id, t.date_sent');
if (!$canViewOthers) {
$this->limitQueryToCreator($q);
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->translator->trans('mautic.dynamicContent.show.unique.views'), $data);
}
return $chart->render();
}
/**
* Add an event to the container.
*
* The data should be an associative array with the following data:
* 'event' => string The event name
* 'timestamp' => \DateTime The timestamp of the event
* 'extra' => array An optional array of extra data for the event
*
* @param array $data Data array for the table
*/
public function addEvent(array $data)
{
if ($this->countOnly) {
// BC support for old format
if ($this->groupUnit && $this->chartQuery) {
$countData = [['date' => $data['timestamp'], 'count' => 1]];
$count = $this->chartQuery->completeTimeData($countData);
$this->addToCounter($data['event'], $count);
} else {
if (!isset($this->totalEvents[$data['event']])) {
$this->totalEvents[$data['event']] = 0;
}
++$this->totalEvents[$data['event']];
}
} else {
if (!isset($this->events[$data['event']])) {
$this->events[$data['event']] = [];
}
$this->events[$data['event']][] = $data;
}
}
/**
* @param Focus $focus
* @param $unit
* @param \DateTime|null $dateFrom
* @param \DateTime|null $dateTo
* @param null $dateFormat
* @param bool $canViewOthers
*
* @return array
*/
public function getStats(Focus $focus, $unit, \DateTime $dateFrom = null, \DateTime $dateTo = null, $dateFormat = null, $canViewOthers = true)
{
$chart = new LineChart($unit, $dateFrom, $dateTo, $dateFormat);
$query = new ChartQuery($this->em->getConnection(), $dateFrom, $dateTo, $unit);
$q = $query->prepareTimeDataQuery('focus_stats', 'date_added');
if (!$canViewOthers) {
$this->limitQueryToCreator($q);
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->translator->trans('mautic.focus.graph.views'), $data);
if ($focus->getType() != 'notification') {
if ($focus->getType() == 'link') {
$q = $query->prepareTimeDataQuery('focus_stats', 'date_added', ['type' => Stat::TYPE_CLICK]);
if (!$canViewOthers) {
$this->limitQueryToCreator($q);
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->translator->trans('mautic.focus.graph.clicks'), $data);
} else {
$q = $query->prepareTimeDataQuery('focus_stats', 'date_added', ['type' => Stat::TYPE_FORM]);
if (!$canViewOthers) {
$this->limitQueryToCreator($q);
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->translator->trans('mautic.focus.graph.submissions'), $data);
}
}
return $chart->render();
}
