/**
* Returns date/time like Today, 10:00 AM
*
* @param $datetime
* @param string $timezone
* @param string $fromFormat
*/
public function toText($datetime, $timezone = 'local', $fromFormat = 'Y-m-d H:i:s')
{
if (empty($datetime)) {
return '';
}
$this->helper->setDateTime($datetime, $fromFormat, $timezone);
$textDate = $this->helper->getTextDate();
$dt = $this->helper->getLocalDateTime();
if ($textDate) {
return $this->translator->trans('mautic.core.date.' . $textDate, array('%time%' => $dt->format("g:i a")));
} else {
$interval = $this->helper->getDiff('now', null, true);
return $this->translator->trans('mautic.core.date.ago', array('%days%' => $interval->days));
}
}
/**
* Returns date/time like Today, 10:00 AM
*
* @param $datetime
* @param string $timezone
* @param string $fromFormat
* @param bool $forceDateForNonText If true, return as full date/time rather than "29 days ago"
*
* @return string
*/
public function toText($datetime, $timezone = 'local', $fromFormat = 'Y-m-d H:i:s', $forceDateForNonText = false)
{
if (empty($datetime)) {
return '';
}
$this->helper->setDateTime($datetime, $fromFormat, $timezone);
$textDate = $this->helper->getTextDate();
$dt = $this->helper->getLocalDateTime();
if ($textDate) {
return $this->translator->trans('mautic.core.date.' . $textDate, array('%time%' => $dt->format("g:i a")));
} else {
$interval = $this->helper->getDiff('now', null, true);
if ($interval->invert && !$forceDateForNonText) {
// In the past
return $this->translator->trans('mautic.core.date.ago', array('%days%' => $interval->days));
} else {
// In the future
return $this->toFullConcat($datetime, $timezone, $fromFormat);
}
}
}
/**
* @param $filters
* @param $parameters
* @param \Doctrine\DBAL\Query\QueryBuilder $q
* @param bool|false $not
* @param null|int $leadId
*
* @return \Doctrine\DBAL\Query\Expression\CompositeExpression
*/
public function getListFilterExpr($filters, &$parameters, QueryBuilder $q, $not = false, $leadId = null)
{
static $leadTable;
if (!count($filters)) {
return $q->expr()->andX();
}
// Get table columns
if (null === $leadTable) {
$schema = $this->_em->getConnection()->getSchemaManager();
/** @var \Doctrine\DBAL\Schema\Column[] $leadTable */
$leadTable = $schema->listTableColumns(MAUTIC_TABLE_PREFIX . 'leads');
}
$options = $this->getFilterExpressionFunctions();
$groups = array();
$groupExpr = $q->expr()->andX();
foreach ($filters as $k => $details) {
$column = isset($leadTable[$details['field']]) ? $leadTable[$details['field']] : false;
//DBAL does not have a not() function so we have to use the opposite
$func = !$not ? $options[$details['operator']]['expr'] : $options[$details['operator']]['negate_expr'];
$field = "l.{$details['field']}";
// Format the field based on platform specific functions that DBAL doesn't support natively
if ($column) {
$formatter = AbstractFormatter::createFormatter($this->_em->getConnection());
$columnType = $column->getType();
switch ($details['type']) {
case 'datetime':
if (!$columnType instanceof UTCDateTimeType) {
$field = $formatter->toDateTime($field);
}
break;
case 'date':
if (!$columnType instanceof DateType && !$columnType instanceof UTCDateTimeType) {
$field = $formatter->toDate($field);
}
break;
case 'time':
if (!$columnType instanceof TimeType && !$columnType instanceof UTCDateTimeType) {
$field = $formatter->toTime($field);
}
break;
case 'number':
if (!$columnType instanceof IntegerType && !$columnType instanceof FloatType) {
$field = $formatter->toNumeric($field);
}
break;
}
}
//the next one will determine the group
$glue = isset($filters[$k + 1]) ? $filters[$k + 1]['glue'] : $details['glue'];
if ($glue == "or" || $details['glue'] == 'or') {
// Create a new group of andX expressions
if ($groupExpr->count()) {
$groups[] = $groupExpr;
$groupExpr = $q->expr()->andX();
}
}
$parameter = $this->generateRandomParameterName();
$exprParameter = ":{$parameter}";
$ignoreAutoFilter = false;
// Special handling of relative date strings
if ($details['type'] == 'datetime' || $details['type'] == 'date') {
$relativeDateStrings = $this->getRelativeDateStrings();
// Check if the column type is a date/time stamp
$isTimestamp = $columnType instanceof UTCDateTimeType || $details['type'] == 'datetime';
$getDate = function (&$string) use($isTimestamp, $relativeDateStrings, &$details, &$func, $not) {
$key = array_search($string, $relativeDateStrings);
$dtHelper = new DateTimeHelper('midnight today', null, 'local');
$requiresBetween = in_array($func, array('eq', 'neq')) && $isTimestamp;
$timeframe = str_replace('mautic.lead.list.', '', $key);
$modifier = false;
$isRelative = true;
switch ($timeframe) {
case 'today':
case 'tomorrow':
case 'yesterday':
if ($timeframe == 'yesterday') {
$dtHelper->modify('-1 day');
} elseif ($timeframe == 'tomorrow') {
$dtHelper->modify('+1 day');
}
// Today = 2015-08-28 00:00:00
if ($requiresBetween) {
// eq:
// field >= 2015-08-28 00:00:00
// field < 2015-08-29 00:00:00
// neq:
// field < 2015-08-28 00:00:00
// field >= 2015-08-29 00:00:00
$modifier = '+1 day';
} else {
// lt:
// field < 2015-08-28 00:00:00
// gt:
// field > 2015-08-28 23:59:59
// lte:
// field <= 2015-08-28 23:59:59
// gte:
// field >= 2015-08-28 00:00:00
if (in_array($func, array('gt', 'lte'))) {
$modifier = '+1 day -1 second';
}
}
break;
case 'week_last':
case 'week_next':
case 'week_this':
$interval = str_replace('week_', '', $timeframe);
$dtHelper->setDateTime('midnight monday ' . $interval . ' week', null);
// This week: Monday 2015-08-24 00:00:00
if ($requiresBetween) {
// eq:
// field >= Mon 2015-08-24 00:00:00
// field < Mon 2015-08-31 00:00:00
// neq:
// field < Mon 2015-08-24 00:00:00
// field >= Mon 2015-08-31 00:00:00
$modifier = '+1 week';
} else {
// lt:
// field < Mon 2015-08-24 00:00:00
// gt:
// field > Sun 2015-08-30 23:59:59
// lte:
// field <= Sun 2015-08-30 23:59:59
// gte:
// field >= Mon 2015-08-24 00:00:00
if (in_array($func, array('gt', 'lte'))) {
$modifier = '+1 week -1 second';
}
}
break;
case 'month_last':
case 'month_next':
case 'month_this':
$interval = substr($key, -4);
$dtHelper->setDateTime('midnight first day of ' . $interval . ' month', null);
// This month: 2015-08-01 00:00:00
if ($requiresBetween) {
// eq:
// field >= 2015-08-01 00:00:00
// field < 2015-09:01 00:00:00
// neq:
// field < 2015-08-01 00:00:00
// field >= 2016-09-01 00:00:00
$modifier = '+1 month';
} else {
// lt:
// field < 2015-08-01 00:00:00
// gt:
// field > 2015-08-31 23:59:59
// lte:
// field <= 2015-08-31 23:59:59
// gte:
// field >= 2015-08-01 00:00:00
if (in_array($func, array('gt', 'lte'))) {
$modifier = '+1 month -1 second';
}
}
break;
case 'year_last':
case 'year_next':
case 'year_this':
$interval = substr($key, -4);
$dtHelper->setDateTime('midnight first day of ' . $interval . ' year', null);
// This year: 2015-01-01 00:00:00
if ($requiresBetween) {
// eq:
// field >= 2015-01-01 00:00:00
// field < 2016-01-01 00:00:00
// neq:
// field < 2015-01-01 00:00:00
// field >= 2016-01-01 00:00:00
$modifier = '+1 year';
} else {
// lt:
// field < 2015-01-01 00:00:00
// gt:
// field > 2015-12-31 23:59:59
// lte:
// field <= 2015-12-31 23:59:59
// gte:
// field >= 2015-01-01 00:00:00
if (in_array($func, array('gt', 'lte'))) {
$modifier = '+1 year -1 second';
}
}
break;
default:
$isRelative = false;
break;
}
if ($isRelative) {
if ($requiresBetween) {
$startWith = $isTimestamp ? $dtHelper->toUtcString('Y-m-d H:i:s') : $dtHelper->toUtcString('Y-m-d');
$dtHelper->modify($modifier);
$endWith = $isTimestamp ? $dtHelper->toUtcString('Y-m-d H:i:s') : $dtHelper->toUtcString('Y-m-d');
// Use a between statement
$func = $func == 'neq' ? 'notBetween' : 'between';
$details['filter'] = array($startWith, $endWith);
} else {
if ($modifier) {
$dtHelper->modify($modifier);
}
$details['filter'] = $isTimestamp ? $dtHelper->toUtcString('Y-m-d H:i:s') : $dtHelper->toUtcString('Y-m-d');
}
}
};
if (is_array($details['filter'])) {
foreach ($details['filter'] as &$filterValue) {
$getDate($filterValue);
}
} else {
$getDate($details['filter']);
}
}
// Generate a unique alias
$alias = $this->generateRandomParameterName();
switch ($details['field']) {
case 'dnc_bounced':
case 'dnc_unsubscribed':
// Special handling of do not email
$column = str_replace('dnc_', '', $details['field']);
$func = $func == 'eq' && $details['filter'] || $func == 'neq' && !$details['filter'] ? 'EXISTS' : 'NOT EXISTS';
$subqb = $this->_em->getConnection()->createQueryBuilder()->select('null')->from(MAUTIC_TABLE_PREFIX . 'email_donotemail', $alias)->where($q->expr()->andX($q->expr()->eq($alias . '.' . $column, $exprParameter), $q->expr()->eq($alias . '.lead_id', 'l.id')));
// Specific lead
if (!empty($leadId)) {
$subqb->andWhere($subqb->expr()->eq($alias . '.lead_id', $leadId));
}
$groupExpr->add(sprintf('%s (%s)', $func, $subqb->getSQL()));
// Filter will always be true and differentiated via EXISTS/NOT EXISTS
$details['filter'] = true;
break;
case 'leadlist':
case 'tags':
// Special handling of lead lists and tags
$func = in_array($func, array('eq', 'in')) ? 'EXISTS' : 'NOT EXISTS';
if ($details['field'] == 'leadlist') {
$table = 'lead_lists_leads';
$column = 'leadlist_id';
} else {
$table = 'lead_tags_xref';
$column = 'tag_id';
}
// DBAL requires an array for in()
$ignoreAutoFilter = true;
foreach ($details['filter'] as &$value) {
$value = (int) $value;
}
$subExpr = $q->expr()->andX($q->expr()->in(sprintf('%s.%s', $alias, $column), $details['filter']), $q->expr()->eq($alias . '.lead_id', 'l.id'));
$subqb = $this->_em->getConnection()->createQueryBuilder()->select('null')->from(MAUTIC_TABLE_PREFIX . $table, $alias);
// Specific lead
if (!empty($leadId)) {
$subExpr->add($subqb->expr()->eq($alias . '.lead_id', $leadId));
}
if ($table == 'lead_lists_leads') {
$falseParameter = $this->generateRandomParameterName();
$subExpr->add($subqb->expr()->eq($alias . '.manually_removed', ":{$falseParameter}"));
$parameters[$falseParameter] = false;
}
$subqb->where($subExpr);
$groupExpr->add(sprintf('%s (%s)', $func, $subqb->getSQL()));
break;
default:
switch ($func) {
case 'in':
case 'notIn':
foreach ($details['filter'] as &$value) {
$value = $q->expr()->literal(InputHelper::clean($value));
}
$groupExpr->add($q->expr()->{$func}($field, $details['filter']));
$ignoreAutoFilter = true;
break;
case 'between':
case 'notBetween':
// Filter should be saved with double || to separate options
$parameter2 = $this->generateRandomParameterName();
$parameters[$parameter] = $details['filter'][0];
$parameters[$parameter2] = $details['filter'][1];
$exprParameter2 = ":{$parameter2}";
$ignoreAutoFilter = true;
if ($func == 'between') {
$groupExpr->add($q->expr()->andX($q->expr()->gte($field, $exprParameter), $q->expr()->lt($field, $exprParameter2)));
} else {
$groupExpr->add($q->expr()->andX($q->expr()->lt($field, $exprParameter), $q->expr()->gte($field, $exprParameter2)));
}
break;
case 'notEmpty':
$groupExpr->add($q->expr()->andX($q->expr()->isNotNull($field), $q->expr()->neq($field, $q->expr()->literal(''))));
break;
case 'empty':
$groupExpr->add($q->expr()->orX($q->expr()->isNull($field), $q->expr()->eq($field, $q->expr()->literal(''))));
break;
case 'neq':
$groupExpr->add($q->expr()->orX($q->expr()->isNull($field), $q->expr()->neq($field, $exprParameter)));
break;
case 'like':
case 'notLike':
if (strpos($details['filter'], '%') === false) {
$details['filter'] = '%' . $details['filter'] . '%';
}
default:
$groupExpr->add($q->expr()->{$func}($field, $exprParameter));
break;
}
}
if (!$ignoreAutoFilter) {
if (!is_array($details['filter'])) {
switch ($details['type']) {
case 'number':
$details['filter'] = (double) $details['filter'];
break;
case 'boolean':
$details['filter'] = (bool) $details['filter'];
break;
}
}
$parameters[$parameter] = $details['filter'];
}
}
// Get the last of the filters
if ($groupExpr->count()) {
$groups[] = $groupExpr;
}
if (count($groups) === 1) {
// Only one andX expression
$expr = $groups[0];
} else {
// Sets of expressions grouped by OR
$orX = $q->expr()->orX();
$orX->addMultiple($groups);
// Wrap in a andX for other functions to append
$expr = $q->expr()->andX($orX);
}
return $expr;
}
/**
* 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 getLeadStats($quantity, $unit, $options = array())
{
$graphData = GraphHelper::prepareDatetimeLineGraphData($quantity, $unit, array('viewed'));
// Load points for selected period
$q = $this->getEntityManager()->getConnection()->createQueryBuilder();
$q->select(sprintf('count(*) as count, DATE(cl.date_added) as date_added'))->from(MAUTIC_TABLE_PREFIX . 'campaign_leads', 'cl');
$utc = new \DateTimeZone('UTC');
$graphData['fromDate']->setTimezone($utc);
$q->andwhere($q->expr()->andX($q->expr()->gte('cl.date_added', ':date'), $q->expr()->eq('cl.manually_removed', ':false')))->setParameter('date', $graphData['fromDate']->format('Y-m-d H:i:s'))->setParameter('false', false, 'boolean')->groupBy('DATE(cl.date_added)')->orderBy('date_added', 'ASC');
if (isset($options['campaign_id'])) {
$q->andwhere($q->expr()->gte('cl.campaign_id', (int) $options['campaign_id']));
}
$datesAdded = $q->execute()->fetchAll();
$format = GraphHelper::getDateLabelFormat($unit);
$formattedDates = array();
$dt = new DateTimeHelper();
foreach ($datesAdded as &$date) {
$dt->setDateTime($date['date_added'], 'Y-m-d', 'utc');
$key = $dt->getDateTime()->format($format);
$formattedDates[$key] = (int) $date['count'];
}
foreach ($graphData['labels'] as $key => $label) {
$graphData['datasets'][0]['data'][$key] = isset($formattedDates[$label]) ? $formattedDates[$label] : 0;
}
unset($graphData['fromDate']);
return $graphData;
}
