/**
* @return array
*/
protected function makeCombinedData()
{
$combinedRows = array();
//todo: should fix and get proper table name of attribute instead of passing in item
$groupBy = $this->resolveGroupBy('EmailMessage', 'sentDateTime');
$beginDateTime = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeBeginningOfDay($this->beginDate);
$endDateTime = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeEndOfDay($this->endDate);
if ($this->marketingList == null) {
$searchAttributeData = static::makeCampaignsSearchAttributeData('createdDateTime', $beginDateTime, $endDateTime, $this->campaign);
$sql = static::makeCampaignsSqlQuery($searchAttributeData, $groupBy);
$rows = ZurmoRedBean::getAll($sql);
foreach ($rows as $row) {
$this->addNewRowToCombinedRows($row, $combinedRows);
}
}
if ($this->campaign == null) {
$searchAttributeData = static::makeAutorespondersSearchAttributeData('createdDateTime', $beginDateTime, $endDateTime, $this->marketingList);
$sql = static::makeAutorespondersSqlQuery($searchAttributeData, $groupBy);
$rows = ZurmoRedBean::getAll($sql);
foreach ($rows as $row) {
$this->addNewRowToCombinedRows($row, $combinedRows);
}
}
return $combinedRows;
}
/**
* @return array
*/
protected function makeCombinedData()
{
$combinedRows = array();
$groupBy = $this->resolveGroupBy('EmailMessage', 'sentDateTime');
$beginDateTime = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeBeginningOfDay($this->beginDate);
$endDateTime = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeEndOfDay($this->endDate);
if ($this->marketingList == null) {
$searchAttributeData = static::makeCampaignsSearchAttributeData('sentDateTime', $beginDateTime, $endDateTime, $this->campaign);
$sql = static::makeCampaignsSqlQuery($searchAttributeData, $groupBy);
$rows = R::getAll($sql);
foreach ($rows as $row) {
$chartIndexToCompare = $row[$this->resolveIndexGroupByToUse()];
$combinedRows[$chartIndexToCompare] = $row;
}
}
if ($this->campaign == null) {
$searchAttributeData = static::makeAutorespondersSearchAttributeData('sentDateTime', $beginDateTime, $endDateTime, $this->marketingList);
$sql = static::makeAutorespondersSqlQuery($searchAttributeData, $groupBy);
$rows = R::getAll($sql);
foreach ($rows as $row) {
$chartIndexToCompare = $row[$this->resolveIndexGroupByToUse()];
if (!isset($combinedRows[$chartIndexToCompare])) {
$combinedRows[$chartIndexToCompare] = $row;
} else {
$combinedRows[$chartIndexToCompare][self::COUNT] += $row[self::COUNT];
$combinedRows[$chartIndexToCompare][self::UNIQUE_OPENS] += $row[self::UNIQUE_OPENS];
$combinedRows[$chartIndexToCompare][self::UNIQUE_CLICKS] += $row[self::UNIQUE_CLICKS];
}
}
}
return $combinedRows;
}
protected function makeSearchAttributeData($stringTime = null)
{
assert('is_string($stringTime) || $stringTime == null');
$searchAttributeData = array();
$searchAttributeData['clauses'] = array(1 => array('attributeName' => 'startDateTime', 'operatorType' => 'greaterThan', 'value' => DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeBeginningOfDay(DateTimeUtil::getFirstDayOfAMonthDate($stringTime))), 2 => array('attributeName' => 'startDateTime', 'operatorType' => 'lessThan', 'value' => DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeEndOfDay(DateTimeUtil::getLastDayOfAMonthDate($stringTime))));
$searchAttributeData['structure'] = '(1 and 2)';
return $searchAttributeData;
}
/**
* @param null $stringTime
* @return array
*/
protected function makeSearchAttributeData($stringTime = null)
{
assert('is_string($stringTime) || $stringTime == null');
$searchAttributeData = array();
$searchAttributeData['clauses'] = array(1 => array('attributeName' => 'startDateTime', 'operatorType' => 'greaterThan', 'value' => DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeBeginningOfDay(DateTimeUtil::getFirstDayOfAMonthDate($stringTime))), 2 => array('attributeName' => 'startDateTime', 'operatorType' => 'lessThan', 'value' => DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeEndOfDay(DateTimeUtil::getLastDayOfAMonthDate($stringTime))), 3 => array('attributeName' => 'logged', 'operatorType' => 'doesNotEqual', 'value' => true), 4 => array('attributeName' => 'logged', 'operatorType' => 'isNull', 'value' => null), 5 => array('attributeName' => 'activityItems', 'relatedAttributeName' => 'id', 'operatorType' => 'equals', 'value' => (int) $this->params['relationModel']->getClassId('Item')));
$searchAttributeData['structure'] = '(1 and 2 and (3 or 4) and 5)';
return $searchAttributeData;
}
/**
* The value['type'] determines how the attributeAndRelations is structured.
* @param string $attributeName
* @param array $attributeAndRelations
* @param mixed $value
*/
public static function resolveAttributesAndRelations($attributeName, &$attributeAndRelations, $value)
{
assert('is_string($attributeName)');
assert('$attributeAndRelations == "resolveEntireMappingByRules"');
assert('empty($value) || $value == null || is_array($value)');
$delimiter = FormModelUtil::DELIMITER;
$parts = explode($delimiter, $attributeName);
if (count($parts) != 2) {
throw new NotSupportedException();
}
list($realAttributeName, $type) = $parts;
if (isset($value['type']) && $value['type'] != null) {
if ($value['type'] == self::TYPE_YESTERDAY || $value['type'] == self::TYPE_TODAY || $value['type'] == self::TYPE_TOMORROW || $value['type'] == self::TYPE_ON) {
$dateValue = static::resolveValueDataIntoUsableValue($value);
$greaterThanValue = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeBeginningOfDay($dateValue);
$lessThanValue = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeEndOfDay($dateValue);
$attributeAndRelations = array(array($realAttributeName, null, 'greaterThanOrEqualTo', $greaterThanValue, true), array($realAttributeName, null, 'lessThanOrEqualTo', $lessThanValue, true));
} elseif ($value['type'] == self::TYPE_AFTER) {
$dateValue = static::resolveValueDataIntoUsableValue($value);
$greaterThanValue = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeBeginningOfDay($dateValue);
$attributeAndRelations = array(array($realAttributeName, null, 'greaterThanOrEqualTo', $greaterThanValue));
} elseif ($value['type'] == self::TYPE_BEFORE) {
$dateValue = static::resolveValueDataIntoUsableValue($value);
$lessThanValue = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeEndOfDay($dateValue);
$attributeAndRelations = array(array($realAttributeName, null, 'lessThanOrEqualTo', $lessThanValue));
} elseif ($value['type'] == self::TYPE_BETWEEN) {
$firstDateValue = static::resolveValueDataForBetweenIntoUsableFirstDateValue($value);
$secondDateValue = static::resolveValueDataForBetweenIntoUsableSecondDateValue($value);
$greaterThanValue = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeBeginningOfDay($firstDateValue);
$lessThanValue = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeEndOfDay($secondDateValue);
$attributeAndRelations = array(array($realAttributeName, null, 'greaterThanOrEqualTo', $greaterThanValue, true), array($realAttributeName, null, 'lessThanOrEqualTo', $lessThanValue, true));
} elseif ($value['type'] == self::TYPE_NEXT_7_DAYS) {
$today = static::calculateNewDateByDaysFromNow(0);
$todayPlusSevenDays = static::calculateNewDateByDaysFromNow(7);
$greaterThanValue = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeBeginningOfDay($today);
$lessThanValue = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeEndOfDay($todayPlusSevenDays);
$attributeAndRelations = array(array($realAttributeName, null, 'greaterThanOrEqualTo', $greaterThanValue, true), array($realAttributeName, null, 'lessThanOrEqualTo', $lessThanValue, true));
} elseif ($value['type'] == self::TYPE_LAST_7_DAYS) {
$today = static::calculateNewDateByDaysFromNow(0);
$todayMinusSevenDays = static::calculateNewDateByDaysFromNow(-7);
$greaterThanValue = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeBeginningOfDay($todayMinusSevenDays);
$lessThanValue = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeEndOfDay($today);
$attributeAndRelations = array(array($realAttributeName, null, 'greaterThanOrEqualTo', $greaterThanValue, true), array($realAttributeName, null, 'lessThanOrEqualTo', $lessThanValue, true));
} else {
throw new NotSupportedException();
}
} else {
$attributeAndRelations = array(array($realAttributeName, null, null, 'resolveValueByRules'));
}
}
/**
* @return array
*/
public function getChartData()
{
$chartData = array();
$groupedDateTimeData = static::makeGroupedDateTimeData($this->beginDate, $this->endDate, $this->groupBy, false);
foreach ($groupedDateTimeData as $groupData) {
$beginDateTime = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeBeginningOfDay($groupData['beginDate']);
$endDateTime = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeEndOfDay($groupData['endDate']);
$searchAttributedata = static::makeSearchAttributeData($endDateTime, $this->marketingList);
$sql = static::makeColumnSqlQuery($beginDateTime, $searchAttributedata);
$row = ZurmoRedBean::getRow($sql);
$columnData = array(MarketingChartDataProvider::NEW_SUBSCRIBERS_COUNT => ArrayUtil::getArrayValueAndResolveNullAsZero($row, static::NEW_SUBSCRIBERS_COUNT), MarketingChartDataProvider::EXISTING_SUBSCRIBERS_COUNT => ArrayUtil::getArrayValueAndResolveNullAsZero($row, static::EXISTING_SUBSCRIBERS_COUNT), 'displayLabel' => $groupData['displayLabel'], 'dateBalloonLabel' => $this->resolveDateBalloonLabel($groupData['displayLabel']));
$chartData[] = $columnData;
}
return $chartData;
}
public function testGetMetadataForDynamicDateTimeAttributeThatIsOnManyRelatedModel()
{
$super = User::getByUsername('super');
Yii::app()->user->userModel = $super;
$searchForm = new ASearchFormTestModel(new MixedRelationsModel());
//Make sure the timeZone is different than UTC for testing.
Yii::app()->user->userModel->timeZone = 'America/Chicago';
//TEST when no value present
$metadata = SearchFormAttributesToSearchDataProviderMetadataUtil::getMetadata($searchForm, 'dateDateTimeADate__Date', null);
$compareData = array(array('manyMany' => array('value' => array('aDate' => null))));
$this->assertEquals($compareData, $metadata);
//Test Date = Today
$value = array();
$value['type'] = MixedDateTypesSearchFormAttributeMappingRules::TYPE_TODAY;
$metadata = SearchFormAttributesToSearchDataProviderMetadataUtil::getMetadata($searchForm, 'dateDateTimeADateTime__DateTime', $value);
$todayDateTime = new DateTime(null, new DateTimeZone(Yii::app()->timeZoneHelper->getForCurrentUser()));
$today = Yii::app()->dateFormatter->format(DatabaseCompatibilityUtil::getDateFormat(), $todayDateTime->getTimeStamp());
$compareData = array(array('manyMany' => array('value' => array('aDateTime' => DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeBeginningOfDay($today)), 'operatorType' => 'greaterThanOrEqualTo', 'appendStructureAsAnd' => true)), array('manyMany' => array('value' => array('aDateTime' => DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeEndOfDay($today)), 'operatorType' => 'lessThanOrEqualTo', 'appendStructureAsAnd' => true)));
$this->assertEquals($compareData, $metadata);
}
protected static function resolveLeaderboardWhereClausesByType($type, &$where)
{
if ($type == static::LEADERBOARD_TYPE_OVERALL) {
$where = '(' . $where . ')';
return;
}
$quote = DatabaseCompatibilityUtil::getQuote();
$today = MixedDateTimeTypesSearchFormAttributeMappingRules::calculateNewDateByDaysFromNow(0);
if ($type == static::LEADERBOARD_TYPE_WEEKLY) {
$todayMinusSevenDays = MixedDateTimeTypesSearchFormAttributeMappingRules::calculateNewDateByDaysFromNow(-7);
$greaterThanValue = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeBeginningOfDay($todayMinusSevenDays);
$lessThanValue = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeEndOfDay($today);
if ($where != null) {
$where = '(' . $where . ') and ';
}
$where .= "{$quote}gamepointtransaction{$quote}.{$quote}createdDateTime{$quote} >= '" . $greaterThanValue . "'";
$where .= " and ";
$where .= "{$quote}gamepointtransaction{$quote}.{$quote}createdDateTime{$quote} <= '" . $lessThanValue . "'";
} elseif ($type == static::LEADERBOARD_TYPE_MONTHLY) {
$todayMinusThirtyDays = MixedDateTimeTypesSearchFormAttributeMappingRules::calculateNewDateByDaysFromNow(-30);
$greaterThanValue = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeBeginningOfDay($todayMinusThirtyDays);
$lessThanValue = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeEndOfDay($today);
if ($where != null) {
$where = '(' . $where . ') and ';
}
$where .= "{$quote}gamepointtransaction{$quote}.{$quote}createdDateTime{$quote} >= '" . $greaterThanValue . "'";
$where .= " and ";
$where .= "{$quote}gamepointtransaction{$quote}.{$quote}createdDateTime{$quote} <= '" . $lessThanValue . "'";
} else {
throw new NotSupportedException();
}
}
public function testSearchFormDynamicAttributes()
{
$super = User::getByUsername('super');
Yii::app()->user->userModel = $super;
$searchAttributes = array('aaaMember' => 'Vomitorio Corp', 'bbb' => array('relatedData' => true, 'ccc' => array('relatedData' => true, 'date__Date' => array('type' => MixedDateTypesSearchFormAttributeMappingRules::TYPE_AFTER, 'firstDate' => '1991-03-04'), 'dateTime__DateTime' => array('type' => MixedDateTypesSearchFormAttributeMappingRules::TYPE_TODAY), 'dateTime2__DateTime' => array('value' => null), 'eee' => array('relatedData' => true, 'eeeMember' => 'eeeMemberValue'), 'iii' => array('relatedData' => true, 'date__Date' => array('type' => MixedDateTypesSearchFormAttributeMappingRules::TYPE_AFTER, 'firstDate' => '1991-03-04'), 'dateTime__DateTime' => array('type' => MixedDateTypesSearchFormAttributeMappingRules::TYPE_TODAY), 'dateTime2__DateTime' => array('value' => null)))));
$metadataAdapter = new SearchDataProviderMetadataAdapter(new AAA(false), 1, $searchAttributes);
$todayDateTime = new DateTime(null, new DateTimeZone(Yii::app()->timeZoneHelper->getForCurrentUser()));
$today = Yii::app()->dateFormatter->format(DatabaseCompatibilityUtil::getDateFormat(), $todayDateTime->getTimeStamp());
$todayPlus7Days = MixedDateTypesSearchFormAttributeMappingRules::calculateNewDateByDaysFromNow(7);
$metadata = $metadataAdapter->getAdaptedMetadata();
$compareClauses = array(1 => array('attributeName' => 'aaaMember', 'operatorType' => 'startsWith', 'value' => 'Vomitorio Corp'), 2 => array('attributeName' => 'bbb', 'relatedModelData' => array('attributeName' => 'ccc', 'relatedModelData' => array('attributeName' => 'date', 'operatorType' => 'greaterThanOrEqualTo', 'value' => '1991-03-04'))), 3 => array('attributeName' => 'bbb', 'relatedModelData' => array('attributeName' => 'ccc', 'relatedModelData' => array('attributeName' => 'dateTime', 'operatorType' => 'greaterThanOrEqualTo', 'value' => DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeBeginningOfDay($today)))), 4 => array('attributeName' => 'bbb', 'relatedModelData' => array('attributeName' => 'ccc', 'relatedModelData' => array('attributeName' => 'dateTime', 'operatorType' => 'lessThanOrEqualTo', 'value' => DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeEndOfDay($today)))), 5 => array('attributeName' => 'bbb', 'relatedModelData' => array('attributeName' => 'ccc', 'relatedModelData' => array('attributeName' => 'eee', 'relatedModelData' => array('attributeName' => 'eeeMember', 'operatorType' => 'startsWith', 'value' => 'eeeMemberValue')))), 6 => array('attributeName' => 'bbb', 'relatedModelData' => array('attributeName' => 'ccc', 'relatedModelData' => array('attributeName' => 'iii', 'relatedModelData' => array('attributeName' => 'date', 'operatorType' => 'greaterThanOrEqualTo', 'value' => '1991-03-04')))), 7 => array('attributeName' => 'bbb', 'relatedModelData' => array('attributeName' => 'ccc', 'relatedModelData' => array('attributeName' => 'iii', 'relatedModelData' => array('attributeName' => 'dateTime', 'operatorType' => 'greaterThanOrEqualTo', 'value' => DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeBeginningOfDay($today))))), 8 => array('attributeName' => 'bbb', 'relatedModelData' => array('attributeName' => 'ccc', 'relatedModelData' => array('attributeName' => 'iii', 'relatedModelData' => array('attributeName' => 'dateTime', 'operatorType' => 'lessThanOrEqualTo', 'value' => DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeEndOfDay($today))))));
$compareStructure = '1 and (2) and (3 and 4) and 5 and (6) and (7 and 8)';
$this->assertEquals($compareClauses, $metadata['clauses']);
$this->assertEquals($compareStructure, $metadata['structure']);
}
protected function makeSearchAttributeData()
{
assert('!($this->ownerOnly && $this->relationModel != null)');
$searchAttributeData = array();
$searchAttributeData['clauses'] = array(1 => array('attributeName' => 'startDateTime', 'operatorType' => 'greaterThanOrEqualTo', 'value' => DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeBeginningOfDay($this->stringTime)), 2 => array('attributeName' => 'startDateTime', 'operatorType' => 'lessThanOrEqualTo', 'value' => DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeEndOfDay($this->stringTime)));
$searchAttributeData['structure'] = '(1 and 2)';
if ($this->ownerOnly) {
$searchAttributeData['clauses'][3] = array('attributeName' => 'owner', 'operatorType' => 'equals', 'value' => Yii::app()->user->userModel->id);
$searchAttributeData['structure'] = '(1 and 2 and 3)';
}
//The assertion above ensures that either ownerOnly or relationModel is populated but not both.
if ($this->relationModel != null) {
$searchAttributeData['clauses'][3] = array('attributeName' => 'activityItems', 'relatedAttributeName' => 'id', 'operatorType' => 'equals', 'value' => (int) $this->relationModel->getClassId('Item'));
$searchAttributeData['structure'] = '(1 and 2 and 3)';
}
return $searchAttributeData;
}
public function testSearchFormDynamicAttributes()
{
$super = User::getByUsername('super');
Yii::app()->user->userModel = $super;
$searchAttributes = array('date__Date' => array('type' => MixedDateTypesSearchFormAttributeMappingRules::TYPE_AFTER, 'firstDate' => '1991-03-04'), 'dateTime__DateTime' => array('type' => MixedDateTypesSearchFormAttributeMappingRules::TYPE_TODAY), 'dateTime2__DateTime' => array('value' => null));
$searchForm = new MixedRelationsModelSearchFormTestModel(new MixedRelationsModel());
$metadataAdapter = new SearchDataProviderMetadataAdapter($searchForm, $super->id, $searchAttributes);
$todayDateTime = new DateTime(null, new DateTimeZone(Yii::app()->timeZoneHelper->getForCurrentUser()));
$today = Yii::app()->dateFormatter->format(DatabaseCompatibilityUtil::getDateFormat(), $todayDateTime->getTimeStamp());
$todayPlus7Days = MixedDateTypesSearchFormAttributeMappingRules::calculateNewDateByDaysFromNow(7);
$metadata = $metadataAdapter->getAdaptedMetadata();
$compareClauses = array(1 => array('attributeName' => 'date', 'operatorType' => 'greaterThanOrEqualTo', 'value' => '1991-03-04'), 2 => array('attributeName' => 'dateTime', 'operatorType' => 'greaterThanOrEqualTo', 'value' => DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeBeginningOfDay($today)), 3 => array('attributeName' => 'dateTime', 'operatorType' => 'lessThanOrEqualTo', 'value' => DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeEndOfDay($today)));
$compareStructure = '(1) and (2 and 3)';
$this->assertEquals($compareClauses, $metadata['clauses']);
$this->assertEquals($compareStructure, $metadata['structure']);
}
public function testConvertDateIntoTimeZoneAdjustedDateTimeEndOfDay()
{
SecurityTestHelper::createSuperAdmin();
Yii::app()->user->userModel = User::getByUsername('super');
$super = Yii::app()->user->userModel;
$super->timeZone = 'Africa/Nairobi';
$saved = $super->save();
if (!$saved) {
throw new FailedToSaveModelException();
}
$endDateTime = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeEndOfDay('2014-03-03');
$this->assertEquals($endDateTime, '2014-03-03 20:59:59');
$super->timeZone = 'Asia/Hong_Kong';
$saved = $super->save();
if (!$saved) {
throw new FailedToSaveModelException();
}
$endDateTime = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeEndOfDay('2014-03-03');
$this->assertEquals($endDateTime, '2014-03-03 15:59:59');
$super->timeZone = 'America/Chicago';
$saved = $super->save();
if (!$saved) {
throw new FailedToSaveModelException();
}
$endDateTime = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeEndOfDay('2014-03-03');
$this->assertEquals($endDateTime, '2014-03-04 05:59:59');
$endDateTime = DateTimeUtil::convertDateIntoTimeZoneAdjustedDateTimeEndOfDay('2014-03-29');
$this->assertEquals($endDateTime, '2014-03-30 04:59:59');
}