PHP Gpf_SqlBuilder_SelectBuilder::toString方法代码示例

 public function getCurrentAvgEurRate($targetcurrency, Gpf_DateTime_Range $range)
 {
     $sql = new Gpf_SqlBuilder_SelectBuilder();
     $sql->select->add('avg(' . Gpf_Db_Table_CurrencyRates::RATE . ')', Gpf_Db_Table_CurrencyRates::RATE);
     $sql->from->add(Gpf_Db_Table_CurrencyRates::getName());
     $sql->where->add(Gpf_Db_Table_CurrencyRates::SOURCE_CURRENCY, '=', 'EUR');
     $sql->where->add(Gpf_Db_Table_CurrencyRates::TARGET_CURRENCY, '=', $targetcurrency);
     $sql->where->add(Gpf_Db_Table_CurrencyRates::TYPE, '=', Gpf_Db_CurrencyRate::TYPE_DAILY);
     $dateCondition = new Gpf_SqlBuilder_CompoundWhereCondition();
     $coumpoundCondWithin = new Gpf_SqlBuilder_CompoundWhereCondition();
     $coumpoundCondWithin->add(Gpf_Db_Table_CurrencyRates::VALID_FROM, '>=', $range->getFrom()->toDateTime());
     $coumpoundCondWithin->add(Gpf_Db_Table_CurrencyRates::VALID_TO, '<=', $range->getTo()->toDateTime());
     $dateCondition->addCondition($coumpoundCondWithin, 'OR');
     $coumpoundCondBefore = new Gpf_SqlBuilder_CompoundWhereCondition();
     $coumpoundCondBefore->add(Gpf_Db_Table_CurrencyRates::VALID_FROM, '<=', $range->getFrom()->toDateTime());
     $coumpoundCondBefore->add(Gpf_Db_Table_CurrencyRates::VALID_TO, '>=', $range->getFrom()->toDateTime());
     $dateCondition->addCondition($coumpoundCondBefore, 'OR');
     $coumpoundCondAfter = new Gpf_SqlBuilder_CompoundWhereCondition();
     $coumpoundCondAfter->add(Gpf_Db_Table_CurrencyRates::VALID_FROM, '<=', $range->getTo()->toDateTime());
     $coumpoundCondAfter->add(Gpf_Db_Table_CurrencyRates::VALID_TO, '>=', $range->getTo()->toDateTime());
     $dateCondition->addCondition($coumpoundCondAfter, 'OR');
     $sql->where->addCondition($dateCondition);
     Gpf_Log::debug($sql->toString());
     Gpf_Log::debug('Avg rate: ' . $sql->getOneRow()->get(Gpf_Db_Table_CurrencyRates::RATE));
     return $sql->getOneRow()->get(Gpf_Db_Table_CurrencyRates::RATE);
 }

	private function  getDefaultCommissionGroup() {
		$select = new Gpf_SqlBuilder_SelectBuilder();
		$select->select->add(Pap_Db_Table_CommissionGroups::ID);
		$select->from->add(Pap_Db_Table_CommissionGroups::getName());
		$select->where->add(Pap_Db_Table_CommissionGroups::CAMPAIGN_ID, '=', 'c.campaignid', 'AND', false);
        $select->where->add(Pap_Db_Table_CommissionGroups::IS_DEFAULT, '=', Gpf::YES);
        $select->limit->set(0,1);
		
		return '('.$select->toString().')';
	}

 protected function getContent()
 {
     $selectBuilder = new Gpf_SqlBuilder_SelectBuilder();
     $selectBuilder->select->add('content');
     $selectBuilder->from->add(Gpf_Db_Table_FileContents::getName());
     $selectBuilder->where->add('fileid', '=', $this->file->getFileId());
     $selectBuilder->orderBy->add('contentid', true);
     $sth = $this->createDatabase()->execute($selectBuilder->toString());
     while ($row = $sth->fetchArray()) {
         echo $row['content'];
     }
     return '';
 }

 public function toString()
 {
     return '(' . $this->subSelect->toString() . ') ' . $this->alias;
 }

 public function load(Gpf_SqlBuilder_SelectBuilder $select)
 {
     $this->init();
     foreach ($select->select->getColumns() as $column) {
         $this->_header->add($column->getAlias());
     }
     $statement = $this->createDatabase()->execute($select->toString());
     while ($rowArray = $statement->fetchRow()) {
         $this->add($rowArray);
     }
 }

 /**
  *
  * @return Gpf_Data_RecordSet
  */
 private function getPendingEmail($outboxids)
 {
     $this->debug('Getting pending email...');
     $select = new Gpf_SqlBuilder_SelectBuilder();
     $select->select->add('o.' . Gpf_Db_Table_MailOutbox::ID, Gpf_Db_Table_MailOutbox::ID);
     $select->select->add('o.' . Gpf_Db_Table_MailOutbox::RETRY_NR, Gpf_Db_Table_MailOutbox::RETRY_NR);
     $select->select->add('m.*');
     $select->select->add('ma.*');
     $select->from->add(Gpf_Db_Table_MailOutbox::getName(), 'o');
     $select->from->addInnerJoin(Gpf_Db_Table_MailAccounts::getName(), 'ma', 'o.' . Gpf_Db_Table_MailOutbox::MAILACCOUNTID . ' = ma.' . Gpf_Db_Table_MailAccounts::ID);
     $select->from->addInnerJoin(Gpf_Db_Table_Mails::getName(), 'm', 'o.' . Gpf_Db_Table_MailOutbox::MAIL_ID . ' = m.' . Gpf_Db_Table_Mails::ID);
     if (!empty($outboxids)) {
         $select->where->add('o.' . Gpf_Db_Table_MailOutbox::ID, 'IN', $outboxids);
     } else {
         $select->where->add('o.' . Gpf_Db_Table_MailOutbox::ID, 'BETWEEN', $this->task->getWorkingAreaFrom() . ' AND ' . $this->task->getWorkingAreaTo(), 'AND', false);
     }
     //load just mails with status pending
     $select->where->add('o.' . Gpf_Db_Table_MailOutbox::STATUS, '=', Gpf_Db_Table_MailOutbox::STATUS_PENDING);
     // load just mails, which are already scheduled
     $select->where->add('o.' . Gpf_Db_Table_MailOutbox::SCHNEDULET_AT, '<=', $this->createDatabase()->getDateString());
     //if retry number is too high, don't repeat sending
     $select->where->add('o.' . Gpf_Db_Table_MailOutbox::RETRY_NR, '<', Gpf_Db_Table_MailOutbox::MAX_RETRY_NR);
     $this->debug('email select: ' . $select->toString());
     $select->limit->set(0, 1);
     return $select->getAllRows();
 }

    private function getCustomFields(Pap_Common_User $user) {
        $customFields = explode(',', Gpf_Settings::get(GetResponseSignup_Config::CUSTOM_DATA_FIELDS));

        $select = new Gpf_SqlBuilder_SelectBuilder();
        $select->select->add(Gpf_Db_Table_FormFields::NAME);
        $select->select->add(Gpf_Db_Table_FormFields::CODE);
        $select->from->add(Gpf_Db_Table_FormFields::getName());

        array_walk($customFields, create_function('&$val', '$val = trim(strtolower($val));'));
        $select->where->add(Gpf_Db_Table_FormFields::CODE, 'IN', $customFields);
        $select->where->add(Gpf_Db_Table_FormFields::FORMID, '=', Pap_Merchants_Config_AffiliateFormDefinition::FORMID);
        $customs = array();
        $x = $select->toString();
        foreach($select->getAllRows() as $row) {
            $customs[] = array(
                'name' => str_replace(' ', '_', Gpf_Lang::_localizeRuntime($row->get(Gpf_Db_Table_FormFields::NAME))),
                'content' => $user->getData(str_replace('data', '', $row->get(Gpf_Db_Table_FormFields::CODE)))
            );
        }
        return $customs;
    }

 /**
  * @return String
  */
 protected function getSql(Gpf_SqlBuilder_SelectBuilder $select, $prefix = '')
 {
     return $prefix . $select->toString();
 }

    protected function buildFrom() {
        $this->_selectBuilder->from->add(Pap_Db_Table_Users::getName(), 'u');
        $this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_Users::getName(),
            'gu', 'u.accountuserid=gu.accountuserid AND u.' . Pap_Db_Table_Users::DELETED . ' = \'' . Gpf::NO . '\''.
        ' AND u.' .Pap_Db_Table_Users::TYPE . ' = \'' . Pap_Application::ROLETYPE_AFFILIATE . '\'');
        $this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(),
            'au', 'au.authid=gu.authid');

        $this->_selectBuilder->from->addLeftJoin(Pap_Db_Table_Users::getName(),
            'pu', 'u.parentuserid=pu.userid');
        $this->_selectBuilder->from->addLeftJoin(Gpf_Db_Table_Users::getName(),
            'pgu', 'pu.accountuserid=pgu.accountuserid');
        $this->_selectBuilder->from->addLeftJoin(Gpf_Db_Table_AuthUsers::getName(),
            'pau', 'pau.authid=pgu.authid');

        $this->_selectBuilder->from->addLeftJoin(Gpf_Db_Table_FieldGroups::getName(),
            'pay', 'pay.fieldgroupid=u.payoutoptionid AND pay.rtype=\'P\' AND pay.rstatus=\'' .
        Gpf_Db_FieldGroup::ENABLED . '\'');


        if ($this->areColumnsRequiredOrInFilter(array('lastlogin', 'loginscount', 'loginsnr'))) {
            $select = new Gpf_SqlBuilder_SelectBuilder();
            $select->select->add('MAX(login)', 'lastlogin');
            $select->select->add('accountuserid');
            $select->select->add('COUNT(*)', 'loginsnr');
            $select->from->add(Gpf_Db_Table_LoginsHistory::getName());
            $select->groupBy->add(Gpf_Db_Table_LoginsHistory::ACCOUNTUSERID);

            $this->_selectBuilder->from->addLeftJoin('('.$select->toString().')',
                    'lo', 'u.' . Gpf_Db_Table_LoginsHistory::ACCOUNTUSERID . '=lo.'.Gpf_Db_Table_LoginsHistory::ACCOUNTUSERID);
        }
        $this->buildStatsFrom();
    }

 /**
  * @param Pap_Stats_Params $statParams
  * @param $tableAlias
  */
 protected function addJoinSelectToSelectBuilder(Gpf_SqlBuilder_SelectBuilder $select, $tableAlias) {
     $this->_selectBuilder->from->addRightJoin('('.$select->toString().')',
                 $tableAlias, $tableAlias.'.'.self::GROUP_COLUMN_ALIAS.'='.$this->mainTablePreffix. '.'. $this->mainTableColumn);
 }

 protected function getCountryCodes() {
     $subSelect = new Gpf_SqlBuilder_SelectBuilder();
     $subSelect->select->add('GROUP_CONCAT(DISTINCT ct.'.Pap_Db_Table_CommissionTypes::COUNTRYCODES.')','countrycode');
     $subSelect->from->add(Pap_Db_Table_CommissionTypes::getName(),'ct');
     $subSelect->where->add('ct.'.Pap_Db_Table_CommissionTypes::CAMPAIGNID,'=','c.'.Pap_Db_Table_Campaigns::ID,'AND',false);
     return $subSelect->toString();
 }

    /**
     * @return String
     */
    protected function createChannelsWithEmptyRow(Gpf_SqlBuilder_SelectBuilder $select) {
        $selectNullRow = new Gpf_SqlBuilder_SelectClause();
        $selectNullRow->add('\'\'', Pap_Db_Table_Channels::ID);
        $selectNullRow->add('\' ' . $this->_('None channel') . '\'', Pap_Db_Table_Channels::NAME);

        return $select->toString() . ' UNION ' . $selectNullRow->toString();
    }

 public function fromSelect(Gpf_SqlBuilder_SelectBuilder $selectBuilder)
 {
     $this->fromSelect = $selectBuilder->toString();
     foreach ($selectBuilder->select->getColumns() as $column) {
         if ($this->table !== null && !$this->table->hasColumn($column->getAlias())) {
             throw new Gpf_Exception('Column ' . $column->getAlias() . " doesn't exist in {$this->tableName}.");
         }
         $i = count($this->columns);
         $this->columns[$i]['column'] = $column->getAlias();
     }
 }

 public function getAllCount()
 {
     try {
         $select = new Gpf_SqlBuilder_SelectBuilder();
         $select->select->add('count(*)');
         $select->from->addSubselect($this->createSelect(), 's');
         $sth = $this->createDatabase()->execute($select->toString());
         $row = $sth->fetchRow();
         return min(array($row[0], $this->getRecordsPerPage() * 3));
     } catch (Exception $e) {
         return 0;
     }
 }

 protected function getCount(Gpf_SqlBuilder_SelectBuilder $selectBuilder) {
 	$sth = $this->createDatabase()->execute($selectBuilder->toString());
     return $sth->rowCount();
 }