PHP DatabaseCompatibilityUtil::getQuote方法代码示例

 /**
  * Searching Many To Many on a custom field (dropdown)
  */
 public function testManyManyCustomFieldSearch()
 {
     $quote = DatabaseCompatibilityUtil::getQuote();
     $searchAttributeData = array();
     $searchAttributeData['clauses'] = array(1 => array('attributeName' => 'opportunities', 'relatedModelData' => array('attributeName' => 'stage', 'relatedAttributeName' => 'value', 'operatorType' => 'oneOf', 'value' => array(0 => 'something'))));
     $searchAttributeData['structure'] = '1';
     //Build the query 'where' and 'joins'. Confirm they are as expected
     $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Contact');
     $where = ModelDataProviderUtil::makeWhere('Contact', $searchAttributeData, $joinTablesAdapter);
     $compareWhere = "({$quote}customfield{$quote}.{$quote}value{$quote} IN('something'))";
     $this->assertEquals($compareWhere, $where);
     $this->assertEquals(0, $joinTablesAdapter->getFromTableJoinCount());
     $this->assertEquals(3, $joinTablesAdapter->getLeftTableJoinCount());
     $leftTables = $joinTablesAdapter->getLeftTablesAndAliases();
     $this->assertEquals('contact_opportunity', $leftTables[0]['tableName']);
     $this->assertEquals('opportunity', $leftTables[1]['tableName']);
     $this->assertEquals('customfield', $leftTables[2]['tableName']);
     $this->assertTrue($joinTablesAdapter->getSelectDistinct());
     //Now test that the subsetSQL query produced is correct.
     $subsetSql = Contact::makeSubsetOrCountSqlQuery('contact', $joinTablesAdapter, 1, 5, $where, null, false, $joinTablesAdapter->getSelectDistinct());
     $compareSubsetSql = "select distinct {$quote}contact{$quote}.{$quote}id{$quote} id ";
     $compareSubsetSql .= "from {$quote}contact{$quote} ";
     $compareSubsetSql .= "left join {$quote}contact_opportunity{$quote} on ";
     $compareSubsetSql .= "{$quote}contact_opportunity{$quote}.{$quote}contact_id{$quote} = {$quote}contact{$quote}.{$quote}id{$quote} ";
     $compareSubsetSql .= "left join {$quote}opportunity{$quote} on ";
     $compareSubsetSql .= "{$quote}opportunity{$quote}.{$quote}id{$quote} = {$quote}contact_opportunity{$quote}.{$quote}opportunity_id{$quote} ";
     $compareSubsetSql .= "left join {$quote}customfield{$quote} on ";
     $compareSubsetSql .= "{$quote}customfield{$quote}.{$quote}id{$quote} = {$quote}opportunity{$quote}.{$quote}stage_customfield_id{$quote} ";
     $compareSubsetSql .= "where " . $compareWhere . ' ';
     $compareSubsetSql .= 'limit 5 offset 1';
     $this->assertEquals($compareSubsetSql, $subsetSql);
     //Make sure the sql runs properly.
     $data = Contact::getSubset($joinTablesAdapter, 0, 5, $where, null, null, $joinTablesAdapter->getSelectDistinct());
 }

 protected static function getGroupBySqlPart($tableName, $columnName)
 {
     assert('$tableName');
     assert('$columnName');
     $quote = DatabaseCompatibilityUtil::getQuote();
     return "{$quote}{$tableName}{$quote}.{$quote}{$columnName}{$quote}";
 }

 public function testRun()
 {
     $quote = DatabaseCompatibilityUtil::getQuote();
     $super = User::getByUsername('super');
     Yii::app()->user->userModel = $super;
     $box = EmailBox::resolveAndGetByName(EmailBox::NOTIFICATIONS_NAME);
     $folder = EmailFolder::getByBoxAndType($box, EmailFolder::TYPE_SENT);
     //Create 2 sent notifications, and set one with a date over a week ago (8 days ago) for the modifiedDateTime
     $emailMessage = EmailMessageTestHelper::createDraftSystemEmail('My Email Message', $super);
     $emailMessage->folder = $folder;
     $saved = $emailMessage->save();
     $this->assertTrue($saved);
     $modifiedDateTime = DateTimeUtil::convertTimestampToDbFormatDateTime(time() - 60 * 60 * 24 * 8);
     $sql = "Update item set modifieddatetime = '" . $modifiedDateTime . "' where id = " . $emailMessage->getClassId('Item');
     ZurmoRedBean::exec($sql);
     $emailMessage2 = EmailMessageTestHelper::createDraftSystemEmail('My Email Message 2', $super);
     $emailMessage2->folder = $folder;
     $saved = $emailMessage2->save();
     $this->assertTrue($saved);
     $this->assertEquals(2, EmailMessage::getCount());
     $job = new ClearSentNotificationsEmailJob();
     $this->assertTrue($job->run());
     $emailMessages = EmailMessage::getAll();
     $this->assertEquals(1, count($emailMessages));
     $this->assertEquals($emailMessage2->id, $emailMessages[0]->id);
 }

 protected static function makeSqlQuery($searchAttributeData)
 {
     $quote = DatabaseCompatibilityUtil::getQuote();
     $where = null;
     $selectDistinct = false;
     $autoresponderTableName = Autoresponder::getTableName('Autoresponder');
     $autoresponderItemTableName = AutoresponderItem::getTableName('AutoresponderItem');
     $emailMessageTableName = EmailMessage::getTableName('EmailMessage');
     $sentDateTimeColumnName = EmailMessage::getColumnNameByAttribute('sentDateTime');
     $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Autoresponder');
     $selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct);
     $queuedEmailsSelectPart = "sum(CASE WHEN {$quote}{$emailMessageTableName}{$quote}.{$quote}{$sentDateTimeColumnName}" . $quote . " = '0000-00-00 00:00:00' OR {$quote}{$emailMessageTableName}{$quote}" . ".{$quote}{$sentDateTimeColumnName}{$quote} IS NULL THEN 1 ELSE 0 END)";
     // Not Coding Standard
     $sentEmailsSelectPart = "sum(CASE WHEN {$quote}{$emailMessageTableName}{$quote}.{$quote}{$sentDateTimeColumnName}" . $quote . " > '0000-00-00 00:00:00' THEN 1 ELSE 0 END)";
     $uniqueOpensSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_OPEN);
     $uniqueClicksSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_CLICK);
     $bouncedSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_BOUNCE);
     $optedOutSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_UNSUBSCRIBE);
     static::addEmailMessageDayDateClause($selectQueryAdapter, $sentDateTimeColumnName);
     static::addEmailMessageFirstDayOfWeekDateClause($selectQueryAdapter, $sentDateTimeColumnName);
     static::addEmailMessageFirstDayOfMonthDateClause($selectQueryAdapter, $sentDateTimeColumnName);
     $selectQueryAdapter->addNonSpecificCountClause();
     $selectQueryAdapter->addClauseByQueryString($queuedEmailsSelectPart, static::QUEUED);
     $selectQueryAdapter->addClauseByQueryString($sentEmailsSelectPart, static::SENT);
     $selectQueryAdapter->addClauseByQueryString("count((" . $uniqueOpensSelectPart . "))", static::UNIQUE_OPENS);
     $selectQueryAdapter->addClauseByQueryString("count((" . $uniqueClicksSelectPart . "))", static::UNIQUE_CLICKS);
     $selectQueryAdapter->addClauseByQueryString("count((" . $bouncedSelectPart . "))", static::BOUNCED);
     $selectQueryAdapter->addClauseByQueryString("count((" . $optedOutSelectPart . "))", static::UNSUBSCRIBED);
     $joinTablesAdapter->addLeftTableAndGetAliasName($autoresponderItemTableName, 'id', $autoresponderTableName, 'autoresponder_id');
     $joinTablesAdapter->addLeftTableAndGetAliasName($emailMessageTableName, 'emailmessage_id', $autoresponderItemTableName, 'id');
     $where = RedBeanModelDataProvider::makeWhere('Autoresponder', $searchAttributeData, $joinTablesAdapter);
     $sql = SQLQueryUtil::makeQuery($autoresponderTableName, $selectQueryAdapter, $joinTablesAdapter, null, null, $where);
     return $sql;
 }

 protected static function generateCampaignItems($campaign, $pageSize)
 {
     if ($pageSize == null) {
         $pageSize = self::DEFAULT_CAMPAIGNITEMS_TO_CREATE_PAGE_SIZE;
     }
     $contacts = array();
     $quote = DatabaseCompatibilityUtil::getQuote();
     $marketingListMemberTableName = RedBeanModel::getTableName('MarketingListMember');
     $campaignItemTableName = RedBeanModel::getTableName('CampaignItem');
     $sql = "select {$quote}{$marketingListMemberTableName}{$quote}.{$quote}contact_id{$quote} from {$quote}{$marketingListMemberTableName}{$quote}";
     // Not Coding Standard
     $sql .= "left join {$quote}{$campaignItemTableName}{$quote} on ";
     $sql .= "{$quote}{$campaignItemTableName}{$quote}.{$quote}contact_id{$quote} ";
     $sql .= "= {$quote}{$marketingListMemberTableName}{$quote}.{$quote}contact_id{$quote}";
     $sql .= "AND {$quote}{$campaignItemTableName}{$quote}.{$quote}campaign_id{$quote} = " . $campaign->id . " ";
     $sql .= "where {$quote}{$marketingListMemberTableName}{$quote}.{$quote}marketinglist_id{$quote} = " . $campaign->marketingList->id;
     $sql .= " and {$quote}{$campaignItemTableName}{$quote}.{$quote}id{$quote} is null limit " . $pageSize;
     $ids = R::getCol($sql);
     foreach ($ids as $contactId) {
         $contacts[] = Contact::getById((int) $contactId);
     }
     if (!empty($contacts)) {
         //todo: if the return value is false, then we might need to catch that since it didn't go well.
         CampaignItem::registerCampaignItemsByCampaign($campaign, $contacts);
         if (count($ids) < $pageSize) {
             return true;
         }
     } else {
         return true;
     }
     return false;
 }

 /**
  * @param string $tableAliasName
  * @param string $columnName
  * @return string
  */
 public static function makeColumnNameWithTableAlias($tableAliasName, $columnName)
 {
     assert('is_string($tableAliasName)');
     assert('is_string($columnName)');
     $quote = DatabaseCompatibilityUtil::getQuote();
     return $quote . $tableAliasName . $quote . '.' . $quote . $columnName . $quote;
 }

 public function testRun()
 {
     $quote = DatabaseCompatibilityUtil::getQuote();
     //Create 2 imports, and set one with a date over a week ago (8 days ago) for the modifiedDateTime
     $import = new Import();
     $serializedData['importRulesType'] = 'ImportModelTestItem';
     $import->serializedData = serialize($serializedData);
     $this->assertTrue($import->save());
     ImportTestHelper::createTempTableByFileNameAndTableName('importAnalyzerTest.csv', $import->getTempTableName(), true);
     $modifiedDateTime = DateTimeUtil::convertTimestampToDbFormatDateTime(time() - 60 * 60 * 24 * 8);
     $sql = "Update item set modifieddatetime = '" . $modifiedDateTime . "' where id = " . $import->getClassId('Item');
     ZurmoRedBean::exec($sql);
     $staleImportId = $import->id;
     $import2 = new Import();
     $serializedData['importRulesType'] = 'ImportModelTestItem';
     $import2->serializedData = serialize($serializedData);
     $this->assertTrue($import2->save());
     ImportTestHelper::createTempTableByFileNameAndTableName('importAnalyzerTest.csv', $import2->getTempTableName(), true);
     $this->assertEquals(2, Import::getCount());
     $tableExists = ZurmoRedBean::$writer->doesTableExist($import->getTempTableName());
     $this->assertTrue($tableExists);
     $job = new ImportCleanupJob();
     $this->assertTrue($job->run());
     $tableExists = ZurmoRedBean::$writer->doesTableExist($import->getTempTableName());
     $this->assertFalse($tableExists);
     $imports = Import::getAll();
     $this->assertEquals(1, count($imports));
     $this->assertEquals($import2->id, $imports[0]->id);
 }

 protected function resolveExtraSql(RedBeanModelJoinTablesQueryAdapter &$joinTablesAdapter, &$where)
 {
     if ($where != '') {
         $where .= ' and ';
     }
     $user = Yii::app()->user->userModel;
     $quote = DatabaseCompatibilityUtil::getQuote();
     $starredTableName = StarredUtil::getStarredTableName($this->modelClassName);
     $extraOnQueryPart = "and {$quote}{$starredTableName}{$quote}.{$quote}user_id{$quote} = {$user->id}";
     $starredTableAliasName = $joinTablesAdapter->addLeftTableAndGetAliasName($starredTableName, 'id', null, 'model_id', $extraOnQueryPart);
     $where .= "{$quote}{$starredTableAliasName}{$quote}.{$quote}user_id{$quote} = {$user->id}";
 }

 public static function getByLayoutIdAndUserSortedById($layoutId, $userId)
 {
     $portletCollection = array();
     assert('is_integer($userId) && $userId >= 1');
     $quote = DatabaseCompatibilityUtil::getQuote();
     $sql = "select id, {$quote}column{$quote}, position " . 'from portlet ' . "where layoutid = '{$layoutId}' and _user_id = {$userId} " . 'order by id;';
     foreach (R::getAll($sql) as $row) {
         $portlet = Portlet::getById(intval($row['id']));
         $portletCollection[$row['id']] = $portlet;
     }
     return $portletCollection;
 }

 public static function getByLayoutId($layoutId)
 {
     $portletCollection = array();
     assert('is_string($layoutId)');
     $quote = DatabaseCompatibilityUtil::getQuote();
     $sql = "select id, {$quote}column{$quote}, position " . 'from portlet ' . "where layoutid = '{$layoutId}'" . 'order by id;';
     foreach (R::getAll($sql) as $row) {
         $portlet = Portlet::getById(intval($row['id']));
         $portletCollection[$row['id']] = $portlet;
     }
     return $portletCollection;
 }

 /**
  * Given an id of a currency model, determine if any currency values are using this currency.
  * @return true if at least one currency value model is using this currency.
  * @param integer $currencyId
  */
 public static function isCurrencyInUseById($currencyId)
 {
     assert('is_int($currencyId)');
     $columnName = RedBeanModel::getForeignKeyName('CurrencyValue', 'currency');
     $quote = DatabaseCompatibilityUtil::getQuote();
     $where = "{$quote}{$columnName}{$quote} = '{$currencyId}'";
     $count = CurrencyValue::getCount(null, $where);
     if ($count > 0) {
         return true;
     }
     return false;
 }

 public function testRun()
 {
     $quote = DatabaseCompatibilityUtil::getQuote();
     $super = User::getByUsername('super');
     Yii::app()->user->userModel = $super;
     $this->assertEquals(0, count(EmailMessage::getAll()));
     $job = new TestOutboundEmailJob();
     $this->assertTrue($job->run());
     $emailMessages = EmailMessage::getAll();
     $this->assertEquals(1, count($emailMessages));
     $this->assertEquals(EmailFolder::TYPE_SENT, $emailMessages[0]->folder->type);
 }

 protected function addReadOptimizationWhereClause(&$where, $whereKey, $tableAliasName)
 {
     assert('is_array($where)');
     assert('is_int($whereKey)');
     assert('is_string($tableAliasName)');
     $q = DatabaseCompatibilityUtil::getQuote();
     $columnWithTableAlias = self::makeColumnNameWithTableAlias($tableAliasName, $this->modelAttributeToDataProviderAdapter->getColumnName());
     $mungeTableName = ReadPermissionsOptimizationUtil::getMungeTableName($this->modelAttributeToDataProviderAdapter->getModelClassName());
     $mungeIds = ReadPermissionsOptimizationUtil::getMungeIdsByUser(Yii::app()->user->userModel);
     $whereContent = $columnWithTableAlias . " " . SQLOperatorUtil::getOperatorByType('equals') . " ";
     $whereContent .= "(select securableitem_id from {$q}{$mungeTableName}{$q} " . "where {$q}securableitem_id{$q} = {$columnWithTableAlias} and {$q}munge_id{$q}" . " in ('" . join("', '", $mungeIds) . "') limit 1)";
     $where[$whereKey] = $whereContent;
 }

 public function testFullNameOnContactsSearchFormSearch()
 {
     $super = User::getByUsername('super');
     Yii::app()->user->userModel = $super;
     $_FAKEPOST['Contact'] = array();
     $_FAKEPOST['Contact']['fullName'] = 'Jackie Tyler';
     $metadataAdapter = new SearchDataProviderMetadataAdapter(new ContactsSearchForm(new Contact(false)), 1, $_FAKEPOST['Contact']);
     $searchAttributeData = $metadataAdapter->getAdaptedMetadata();
     $compareData = array('clauses' => array(1 => array('attributeName' => 'firstName', 'operatorType' => 'startsWith', 'value' => 'Jackie Tyler'), 2 => array('attributeName' => 'lastName', 'operatorType' => 'startsWith', 'value' => 'Jackie Tyler'), 3 => array('concatedAttributeNames' => array('firstName', 'lastName'), 'operatorType' => 'startsWith', 'value' => 'Jackie Tyler')), 'structure' => '(1 or 2 or 3)');
     $this->assertEquals($compareData, $searchAttributeData);
     $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Contact');
     $quote = DatabaseCompatibilityUtil::getQuote();
     $where = RedBeanModelDataProvider::makeWhere('Contact', $searchAttributeData, $joinTablesAdapter);
     $compareWhere = "(({$quote}person{$quote}.{$quote}firstname{$quote} like 'Jackie Tyler%') or ";
     $compareWhere .= "({$quote}person{$quote}.{$quote}lastname{$quote} like 'Jackie Tyler%') or ";
     $compareWhere .= "(concat({$quote}person{$quote}.{$quote}firstname{$quote}, ' ', ";
     $compareWhere .= "{$quote}person{$quote}.{$quote}lastname{$quote}) like 'Jackie Tyler%'))";
     $this->assertEquals($compareWhere, $where);
     //Now test that the joinTablesAdapter has correct information.
     $this->assertEquals(1, $joinTablesAdapter->getFromTableJoinCount());
     $this->assertEquals(0, $joinTablesAdapter->getLeftTableJoinCount());
     $fromTables = $joinTablesAdapter->getFromTablesAndAliases();
     $this->assertEquals('person', $fromTables[0]['tableName']);
     //Make sure the sql runs properly.
     $dataProvider = new RedBeanModelDataProvider('Contact', null, false, $searchAttributeData);
     $data = $dataProvider->getData();
     $this->assertEquals(0, count($data));
     ContactTestHelper::createContactByNameForOwner('Dino', $super);
     $dataProvider->getTotalItemCount(true);
     //refreshes the total item count
     $data = $dataProvider->getData();
     $this->assertEquals(0, count($data));
     ContactTestHelper::createContactByNameForOwner('Jackie', $super);
     $dataProvider->getTotalItemCount(true);
     //refreshes the total item count
     $data = $dataProvider->getData();
     $this->assertEquals(0, count($data));
     ContactsModule::loadStartingData();
     $contact = new Contact();
     $contact->firstName = 'Jackie';
     $contact->lastName = 'Tyler';
     $contact->owner = $super;
     $contact->state = ContactsUtil::getStartingState();
     $this->assertTrue($contact->save());
     $dataProvider->getTotalItemCount(true);
     //refreshes the total item count
     $data = $dataProvider->getData(true);
     $this->assertEquals(1, count($data));
     $this->assertEquals($contact->id, $data[0]->id);
 }

 /**
  * Gets the date of all meetings as defined by the searchAttributeData
  */
 public function makeSqlQuery()
 {
     assert('get_class($this->model) == "Meeting"');
     $modelClassName = 'Meeting';
     $quote = DatabaseCompatibilityUtil::getQuote();
     $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter($modelClassName);
     $where = ModelDataProviderUtil::makeWhere($modelClassName, $this->searchAttributeData, $joinTablesAdapter);
     $selectDistinct = false;
     $modelClassName::resolveReadPermissionsOptimizationToSqlQuery(Yii::app()->user->userModel, $joinTablesAdapter, $where, $selectDistinct);
     $selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct);
     $selectQueryAdapter->addClause('meeting', 'startdatetime');
     $sql = SQLQueryUtil::makeQuery('meeting', $selectQueryAdapter, $joinTablesAdapter, null, null, $where);
     return $sql;
 }