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(); }
/** * @param Gpf_SqlBuilder_SelectBuilder $selectBuilder * @return boolean */ public function equals(Gpf_SqlBuilder_SelectBuilder $selectBuilder) { return $selectBuilder->toString() == $this->toString(); }