示例#1
0
 /**
  * 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));
     }
 }
示例#2
0
 /**
  * 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;
 }
示例#4
0
 /**
  * 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;
 }