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();
}
