/**
* @param $selectBuilder
* @param $search
*/
protected function addSearchCondition(Gpf_SqlBuilder_SelectBuilder $selectBuilder, $search) {
$condition = new Gpf_SqlBuilder_CompoundWhereCondition();
$condition->add('b.'.Pap_Db_Table_Banners::NAME, 'LIKE', '%'.$search.'%', 'OR');
$condition->add('b.'.Pap_Db_Table_Banners::SIZE, 'LIKE', '%'.$search.'%', 'OR');
$condition->add('c.'.Pap_Db_Table_Campaigns::NAME, 'LIKE', '%'.$search.'%', 'OR');
$selectBuilder->where->addCondition($condition);
}
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 addSearch(Gpf_SqlBuilder_Filter $filter) {
$whereCond = new Gpf_SqlBuilder_CompoundWhereCondition();
$whereCond->add('av.'.Gpf_Db_Table_ActiveViews::VIEWTYPE, 'LIKE', '%' . $filter->getValue() .'%', 'OR');
$whereCond->add('av.'.Gpf_Db_Table_ActiveViews::ACTIVEVIEWID, 'LIKE', '%' . $filter->getValue() .'%', 'OR');
$whereCond->add('v.'.Gpf_Db_Table_Views::NAME, 'LIKE', '%' . $filter->getValue() .'%', 'OR');
$this->_selectBuilder->where->addCondition($whereCond);
}
/**
* @param $selectBuilder
* @param $search
*/
protected function addSearchCondition(Gpf_SqlBuilder_SelectBuilder $selectBuilder, $search)
{
$condition = new Gpf_SqlBuilder_CompoundWhereCondition();
$condition->add('authUsers.' . Gpf_Db_Table_AuthUsers::USERNAME, 'LIKE', '%' . $search . '%', 'OR');
$condition->add('authUsers.' . Gpf_Db_Table_AuthUsers::FIRSTNAME, 'LIKE', '%' . $search . '%', 'OR');
$condition->add('authUsers.' . Gpf_Db_Table_AuthUsers::LASTNAME, 'LIKE', '%' . $search . '%', 'OR');
$selectBuilder->where->addCondition($condition);
}
public function execute() {
$update = new Gpf_SqlBuilder_UpdateBuilder();
$update->from->add(Pap_Db_Table_Transactions::getName());
$dateTime = Gpf_DateTime::min();
$update->set->add(Pap_Db_Table_Transactions::DATA2, $dateTime->toDateTime());
$compoundCondition = new Gpf_SqlBuilder_CompoundWhereCondition();
$compoundCondition->add(Pap_Db_Table_Transactions::DATA2,'=' ,'AT');
$compoundCondition->add(Pap_Db_Table_Transactions::DATA2,'=' , 'AUC', 'OR');
$update->where->addCondition($compoundCondition);
$update->where->add(Pap_Db_Table_Transactions::R_TYPE, '=', Pap_Db_Transaction::TYPE_EXTRA_BONUS);
}
private function addSearch(Gpf_SqlBuilder_Filter $filter) {
$condition = new Gpf_SqlBuilder_CompoundWhereCondition();
$condition->add('atc.'.Pap_Db_Table_AffiliateTrackingCodes::AFFILIATEID, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('au.'.Gpf_Db_Table_AuthUsers::USERNAME, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('au.'.Gpf_Db_Table_AuthUsers::FIRSTNAME, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('au.'.Gpf_Db_Table_AuthUsers::LASTNAME, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('cam.'.Pap_Db_Table_Campaigns::NAME, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('com.'.Pap_Db_Table_CommissionTypes::NAME, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('atc.'.Pap_Db_Table_AffiliateTrackingCodes::CODE, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$this->_selectBuilder->where->addCondition($condition);
}
protected function buildFrom() {
$affiliateWhere = new Gpf_SqlBuilder_CompoundWhereCondition();
$affiliateWhere->add(Pap_Db_Table_AffiliateTrackingCodes::AFFILIATEID, '=', Gpf_Session::getAuthUser()->getPapUserId(), 'OR');
$affiliateWhere->add(Pap_Db_Table_AffiliateTrackingCodes::AFFILIATEID, 'IS', 'NULL', 'OR', false);
$this->_selectBuilder->from->add(Pap_Db_Table_CommissionTypes::getName(), 'ct');
$onCondition = new Gpf_SqlBuilder_CompoundWhereCondition();
$onCondition->add('ct.'.Pap_Db_Table_CommissionTypes::ID, '=', 'c.'.Pap_Db_Table_AffiliateTrackingCodes::COMMTYPEID, 'AND', false);
$onCondition->addCondition($affiliateWhere);
$this->_selectBuilder->from->addLeftJoin(Pap_Db_Table_AffiliateTrackingCodes::getName(), 'c',
$onCondition->toString());
}
/**
* @return Gpf_SqlBuilder_SelectBuilder
*/
protected function createSelectBuilder()
{
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->add(Gpf_Db_Table_RecurrencePresets::ID, self::ID);
$select->select->add(Gpf_Db_Table_RecurrencePresets::NAME, self::VALUE);
$select->from->add(Gpf_Db_Table_RecurrencePresets::getName());
$condition = new Gpf_SqlBuilder_CompoundWhereCondition();
$condition->add(Gpf_Db_Table_RecurrencePresets::ACCOUNTID, 'is', 'NULL', 'OR', false);
$condition->add(Gpf_Db_Table_RecurrencePresets::ACCOUNTID, '=', Gpf_Session::getAuthUser()->getAccountId(), 'OR');
$select->where->addCondition($condition);
$select->orderBy->add(Gpf_Db_Table_RecurrencePresets::NAME);
return $select;
}
/**
* @return Gpf_SqlBuilder_SelectBuilder
*/
protected function createSelectBuilder() {
$filters = new Gpf_Rpc_FilterCollection($this->params);
$selectBuilder = new Gpf_SqlBuilder_SelectBuilder();
$selectBuilder->select->add('v.'.Gpf_Db_Table_Views::ID, self::ID);
$selectBuilder->select->add('v.'.Gpf_Db_Table_Views::NAME, self::VALUE);
$selectBuilder->from->add(Gpf_Db_Table_Views::getName(), 'v');
$whereCond = new Gpf_SqlBuilder_CompoundWhereCondition();
$whereCond->add('v.' . Gpf_Db_Table_Views::ACCOUNTUSERID, '=', Gpf_Session::getAuthUser()->getUserData()->get(Gpf_Db_Table_Users::ID));
$whereCond->add('v.' . Gpf_Db_Table_Views::ACCOUNTUSERID, '=', '', 'OR');
$selectBuilder->where->addCondition($whereCond);
if ($filters->isFilter('viewtype')) {
$selectBuilder->where->add('v.' . Gpf_Db_Table_Views::VIEWTYPE, '=', $filters->getFilterValue('viewtype'));
}
return $selectBuilder;
}
/**
* @param String $viewtype
*
* @return Gpf_Data_RecordSet
*/
public function getAllViews($viewtype)
{
$result = new Gpf_Data_RecordSet('id');
$selectBuilder = new Gpf_SqlBuilder_SelectBuilder();
$selectBuilder->select->add(self::ID, 'id');
$selectBuilder->select->add(self::NAME, self::NAME);
$selectBuilder->select->add(self::ROWSPERPAGE, self::ROWSPERPAGE);
$selectBuilder->select->add(self::ACCOUNTUSERID, self::ACCOUNTUSERID);
$selectBuilder->from->add(self::getName());
$selectBuilder->where->add(self::VIEWTYPE, '=', $viewtype);
$condition = new Gpf_SqlBuilder_CompoundWhereCondition();
$condition->add(self::ACCOUNTUSERID, '=', Gpf_Session::getAuthUser()->getAccountUserId(), 'OR');
$condition->add(self::ACCOUNTUSERID, '=', '', 'OR');
$selectBuilder->where->addCondition($condition);
$selectBuilder->orderBy->add(self::ACCOUNTUSERID, false);
$result->load($selectBuilder);
return $result;
}
/**
* Validate Db_Row
*
* @param Gpf_DbEngine_Row $row
* @throws Gpf_DbEngine_Row_ConstraintException
*/
public function validate(Gpf_DbEngine_Row $row)
{
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->add('COUNT(*)', 'cnt');
$select->from->add($row->getTable()->name());
foreach ($row->getPrimaryColumns() as $primaryColumn) {
$select->where->add($primaryColumn->getName(), '<>', $row->get($primaryColumn->getName()));
}
$conditionNotEqalColumns = new Gpf_SqlBuilder_CompoundWhereCondition();
foreach ($this->columnsNotEqualNames as $columnNotEqualName) {
$conditionNotEqalColumns->add($columnNotEqualName, '=', $row->get($this->columnName), 'OR');
}
$select->where->addCondition($conditionNotEqalColumns);
$select->limit->set(0, 1);
if ($select->getOneRow()->get('cnt') > 0) {
$this->throwException();
}
}
protected function modifySelect(Gpf_SqlBuilder_SelectBuilder $selectBuilder) {
$condition = new Gpf_SqlBuilder_CompoundWhereCondition();
$condition->add('accountUsers.'.Gpf_Db_Table_Users::ID, '=',
'ua.'.Gpf_Db_Table_UserAttributes::ACCOUNT_USER_ID, 'AND', false);
$condition->add('ua.'.Gpf_Db_Table_UserAttributes::NAME, '=',
'apply_vat_invoicing');
$selectBuilder->from->addLeftJoin(Gpf_Db_Table_UserAttributes::getName(), 'ua',
$condition->toString());
if ($this->getVat() == Gpf::NO) {
$condition = new Gpf_SqlBuilder_CompoundWhereCondition();
$condition->add('ua.'.Gpf_Db_Table_UserAttributes::VALUE, '=', Gpf::NO, 'OR');
$condition->add('ua.'.Gpf_Db_Table_UserAttributes::VALUE, '=', null, 'OR');
$selectBuilder->where->addCondition($condition);
return;
}
$selectBuilder->where->add('ua.'.Gpf_Db_Table_UserAttributes::VALUE, '=', Gpf::YES);
}
private function addSearch(Gpf_SqlBuilder_Filter $filter) {
$condition = new Gpf_SqlBuilder_CompoundWhereCondition();
$condition->add('au.username', 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('au.firstname', 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('au.lastname', 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add(Pap_Db_Table_DirectLinkUrls::URL, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('b.name', 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('c.name', 'LIKE', '%'.$filter->getValue().'%', 'OR');
$this->_selectBuilder->where->addCondition($condition);
}
/**
* @return Gpf_SqlBuilder_SelectBuilder
*/
protected function createBannersSelect(Gpf_Rpc_Params $params) {
$selectBuilder = new Gpf_SqlBuilder_SelectBuilder();
$selectBuilder->select->add('b.'.Pap_Db_Table_Banners::ID, self::ID);
$selectBuilder->select->add('b.'.Pap_Db_Table_Banners::NAME, self::VALUE);
$selectBuilder->from->add(Pap_Db_Table_Banners::getName(), 'b');
$cond = new Gpf_SqlBuilder_CompoundWhereCondition();
$cond->add('b.'.Pap_Db_Table_Banners::STATUS, '=', 'A');
$cond->add('b.'.Pap_Db_Table_Banners::STATUS, '=', 'H','OR');
$selectBuilder->where->addCondition($cond,'AND');
if ($params->get('campaignid') != '') {
$selectBuilder->where->add('b.'.Pap_Db_Table_Banners::CAMPAIGN_ID, '=', $params->get('campaignid'));
}
Gpf_Plugins_Engine::extensionPoint('PostAffiliate.BannerListbox.getBannerSelect', $selectBuilder);
return $selectBuilder;
}
private function getCommissionTypeSelect($commissionType, $code = '',$countryCode = '') {
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->addAll(Pap_Db_Table_CommissionTypes::getInstance());
$select->from->add(Pap_Db_Table_CommissionTypes::getName());
$select->where->add(Pap_Db_Table_CommissionTypes::CAMPAIGNID, '=', $this->getId());
$select->where->add(Pap_Db_Table_CommissionTypes::TYPE, '=', $commissionType);
$select->where->add(Pap_Db_Table_CommissionTypes::STATUS, '=', Pap_Db_CommissionType::STATUS_ENABLED);
if ($code != null && $code != '') {
$select->where->add(Pap_Db_Table_CommissionTypes::CODE, '=', $code);
}
if (!strlen($countryCode)) {
$compoundCondition = new Gpf_SqlBuilder_CompoundWhereCondition();
$compoundCondition->add(Pap_Db_Table_CommissionTypes::PARENT_COMMISSIONTYPE_ID, '=', null, 'OR');
$compoundCondition->add(Pap_Db_Table_CommissionTypes::PARENT_COMMISSIONTYPE_ID, '=', '', 'OR');
$select->where->addCondition($compoundCondition);
} else {
$select->where->add(Pap_Db_Table_CommissionTypes::PARENT_COMMISSIONTYPE_ID, '!=', null);
$select->where->add(Pap_Db_Table_CommissionTypes::COUNTRYCODES, 'like', '%' . $countryCode . '%');
}
return $select;
}
/**
* @return Gpf_SqlBuilder_SelectBuilder
*/
protected function createSelectBuilder() {
$selectBuilder = new Gpf_SqlBuilder_SelectBuilder();
$selectBuilder->select->add(Gpf_Db_Table_Roles::ID, self::ID);
$selectBuilder->select->add(Gpf_Db_Table_Roles::NAME, self::VALUE);
$selectBuilder->from->add(Gpf_Db_Table_Roles::getName());
$accountCondition = new Gpf_SqlBuilder_CompoundWhereCondition();
if (Gpf_Session::getAuthUser()->hasPrivilege(Gpf_Privileges::ROLE, Gpf_Privileges::P_READ) ||
Gpf_Session::getAuthUser()->hasPrivilege(Pap_Privileges::ROLE_NAME, Pap_Privileges::P_READ)) {
$accountCondition->add(Gpf_Db_Table_Accounts::ID, '!=', '', 'OR');
} else {
$accountCondition->add(Gpf_Db_Table_Accounts::ID, '=', Gpf_Session::getInstance()->getAuthUser()->getAccountId(), 'OR');
}
$accountCondition->add(Gpf_Db_Table_Accounts::ID, '=', null, 'OR');
$selectBuilder->where->addCondition($accountCondition);
$selectBuilder->where->add(Gpf_Db_Table_Roles::TYPE, '=', Pap_Application::ROLETYPE_MERCHANT);
$selectBuilder->orderBy->add(Gpf_Db_Table_Accounts::ID);
$selectBuilder->orderBy->add(Gpf_Db_Table_Roles::NAME);
return $selectBuilder;
}
/**
* Get list of roles
*
* @service
* @anonym
* @param Gpf_Rpc_Params $parmas
*/
public function getRolesList(Gpf_Rpc_Params $params)
{
if (!Gpf_Session::getAuthUser()->hasPrivilege(Gpf_Privileges::ROLE, Gpf_Privileges::P_READ) && !Gpf_Session::getAuthUser()->hasPrivilege(Gpf_Privileges::ROLE, Pap_Privileges::P_READ_OWN)) {
throw new Gpf_Rpc_PermissionDeniedException('Gpf_Db_Table_Roles', 'getRolesList');
}
$sql = new Gpf_SqlBuilder_SelectBuilder();
$sql->from->add(Gpf_Db_Table_Roles::getName());
$sql->select->addAll(Gpf_Db_Table_Roles::getInstance());
$accountCondition = new Gpf_SqlBuilder_CompoundWhereCondition();
if (Gpf_Session::getAuthUser()->hasPrivilege(Gpf_Privileges::ROLE, Gpf_Privileges::P_READ)) {
$accountCondition->add(Gpf_Db_Table_Accounts::ID, '!=', '', 'OR');
} else {
$accountCondition->add(Gpf_Db_Table_Accounts::ID, '=', Gpf_Session::getInstance()->getAuthUser()->getAccountId(), 'OR');
}
$accountCondition->add(Gpf_Db_Table_Accounts::ID, '=', null, 'OR');
$sql->where->addCondition($accountCondition);
if ($params->exists('roleTypes') && $params->get('roleTypes') !== '') {
$sql->where->add(Gpf_Db_Table_Roles::TYPE, 'IN', explode(',', $params->get('roleTypes')));
}
$sql->orderBy->add(Gpf_Db_Table_Accounts::ID);
$sql->orderBy->add(Gpf_Db_Table_Roles::NAME);
return $sql->getAllRows();
}
public function scheduleTasks($inclusion_type, $inclusion_tasks)
{
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->addAll(Gpf_Db_Table_PlannedTasks::getInstance());
$select->from->add(Gpf_Db_Table_PlannedTasks::getName());
$condition = new Gpf_SqlBuilder_CompoundWhereCondition();
$condition->add(Gpf_Db_Table_PlannedTasks::LASTPLANDATE, '<', Gpf_Common_DateUtils::now(), 'OR');
$condition->add(Gpf_Db_Table_PlannedTasks::LASTPLANDATE, 'is', 'NULL', 'OR', false);
$select->where->addCondition($condition);
if ($inclusion_type == Gpf_Tasks_Runner::INCLUDE_TASKS) {
$select->where->add(Gpf_Db_Table_PlannedTasks::CLASSNAME, 'IN', $inclusion_tasks);
} else {
if ($inclusion_type == Gpf_Tasks_Runner::EXCLUDE_TASKS) {
$select->where->add(Gpf_Db_Table_PlannedTasks::CLASSNAME, 'NOT IN', $inclusion_tasks);
}
}
foreach ($select->getAllRows() as $plannedTaskRow) {
$plannedTask = new Gpf_Db_PlannedTask();
$plannedTask->fillFromRecord($plannedTaskRow);
if ($plannedTask->getLastPlanDate() == null) {
$plannedTask->setLastPlanDate(Gpf_Common_DateUtils::now());
}
$task = new Gpf_Db_Task();
$task->setClassName($plannedTask->getClassName());
$task->setParams($plannedTask->getParams());
$task->setAccountId($plannedTask->getAccountId());
$task->save();
$preset = new Gpf_Recurrence_Preset();
$preset->setId($plannedTask->getRecurrencePresetId());
$preset->load();
$nextDate = $preset->getNextDate(Gpf_Common_DateUtils::mysqlDateTime2Timestamp($plannedTask->getLastPlanDate()));
if ($nextDate != null && $nextDate > 0) {
$plannedTask->setLastPlanDate(Gpf_Common_DateUtils::getDateTime($nextDate));
$plannedTask->update();
}
}
}
/**
* @param array $userIds
* @param array $orderIds
* @return Gpf_DbEngine_Row_Collection
*/
public function getAffectedTransactionsList($userIds, $orderIds = array()) {
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->addAll(Pap_Db_Table_Transactions::getInstance());
$select->from->add(Pap_Db_Table_Transactions::getName());
$dateRangeFilter = new Gpf_SqlBuilder_Filter();
$dateRange = $dateRangeFilter->decodeDatePreset(Pap_Features_CompressedCommissionPlacementModel_Processor::getRecurrencePreset());
$select->where->add(Pap_Db_Table_Transactions::DATE_INSERTED, '>', $dateRange['dateFrom']);
$select->where->add(Pap_Db_Table_Transactions::DATE_INSERTED, '<', $dateRange['dateTo']);
$select->where->add(Pap_Db_Table_Transactions::USER_ID, 'IN', $userIds);
if (!is_null($orderIds) && count($orderIds) > 0) {
$compoundCondition = new Gpf_SqlBuilder_CompoundWhereCondition();
foreach ($orderIds as $orderId) {
$compoundCondition->add(Pap_Db_Table_Transactions::ORDER_ID, 'LIKE', '%'.$orderId.'%', 'OR');
}
$select->where->addCondition($compoundCondition);
}
$select->orderBy->add(Pap_Db_Table_Transactions::TIER);
$transaction = new Pap_Db_Transaction();
$transactionsRecordSet = $select->getAllRows();
$unpaidTransactions = new Gpf_Data_RecordSet();
$unpaidTransactions->setHeader($transactionsRecordSet->getHeader());
foreach ($transactionsRecordSet as $trans) {
if ($trans->get(Pap_Db_Table_Transactions::PAYOUT_STATUS) == Pap_Common_Constants::PSTATUS_UNPAID) {
$unpaidTransactions->add($trans);
} else {
$this->log('Removing paid transaction from affected transactions: ' . $trans->get(Pap_Db_Table_Transactions::TRANSACTION_ID));
}
}
return $transaction->loadCollectionFromRecordset($unpaidTransactions);
}
protected function initSearchAffiliateCondition(Gpf_SqlBuilder_Filter $filter) {
$this->_affiliateCondition = new Gpf_SqlBuilder_CompoundWhereCondition();
$this->_affiliateCondition->add('au.'.Gpf_Db_Table_AuthUsers::USERNAME, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$this->_affiliateCondition->add('au.'.Gpf_Db_Table_AuthUsers::FIRSTNAME, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$this->_affiliateCondition->add('au.'.Gpf_Db_Table_AuthUsers::LASTNAME, 'LIKE', '%'.$filter->getValue().'%', 'OR');
}
protected function buildFilter() {
if ($this->filterId && $this->filterId != 'custom') {
$sql = new Gpf_SqlBuilder_SelectBuilder();
$sql->select->addAll(Gpf_Db_Table_FilterConditions::getInstance());
$sql->from->add(Gpf_Db_Table_FilterConditions::getName());
$sql->where->add('filterid', '=', $this->filterId);
$conditions = $sql->getAllRows();
foreach ($conditions as $condition) {
$filterArray = array(
Gpf_SqlBuilder_Filter::FILTER_CODE => $condition->get('code'),
Gpf_SqlBuilder_Filter::FILTER_OPERATOR => $condition->get('operator'),
Gpf_SqlBuilder_Filter::FILTER_VALUE => $condition->get('value'));
$filter = new Gpf_SqlBuilder_Filter($filterArray);
if (array_key_exists($filter->getCode(), $this->dataColumns)) {
$dataColumn = $this->dataColumns[$filter->getCode()];
$filter->setCode($dataColumn->getName());
$filter->addTo($this->_selectBuilder->where);
} else {
$this->addFilter($filter);
}
}
}
if (!empty($this->recipients)) {
$condition = new Gpf_SqlBuilder_CompoundWhereCondition();
$condition->add('au.'.Gpf_Db_Table_AuthUsers::NOTIFICATION_EMAIL, 'IN', $this->recipients, 'OR');
$condition->add('au.username', 'IN', $this->recipients, 'OR');
$this->_selectBuilder->where->addCondition($condition);
}
}
private function addSearch(Gpf_SqlBuilder_Filter $filter) {
$condition = new Gpf_SqlBuilder_CompoundWhereCondition();
$condition->add('u.' . Pap_Db_Table_Users::ID, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('u.' . Pap_Db_Table_Users::REFID, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('au.' . Gpf_Db_Table_AuthUsers::USERNAME, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('au.' . Gpf_Db_Table_AuthUsers::FIRSTNAME, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('au.' . Gpf_Db_Table_AuthUsers::LASTNAME, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('concat(au.' . Gpf_Db_Table_AuthUsers::FIRSTNAME . ', \' \', au.' . Gpf_Db_Table_AuthUsers::LASTNAME . ')', 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('concat(au.' . Gpf_Db_Table_AuthUsers::LASTNAME . ', \' \', au.' . Gpf_Db_Table_AuthUsers::FIRSTNAME . ')', 'LIKE', '%'.$filter->getValue().'%', 'OR');
for ($i=1; $i<=25; $i++) {
$condition->add('u.data'.$i, 'LIKE', '%'.$filter->getValue().'%', 'OR');
}
$condition->add('pau.' . Gpf_Db_Table_AuthUsers::USERNAME, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('pau.' . Gpf_Db_Table_AuthUsers::FIRSTNAME, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('pau.' . Gpf_Db_Table_AuthUsers::LASTNAME, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('pay.' . Gpf_Db_Table_FieldGroups::NAME, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('au.' . Gpf_Db_Table_AuthUsers::IP, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$this->_selectBuilder->where->addCondition($condition);
}
private function addSearch(Gpf_SqlBuilder_Filter $filter) {
$condition = new Gpf_SqlBuilder_CompoundWhereCondition();
$condition->add('r.'.$this->groupColumn, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$this->_selectBuilder->where->addCondition($condition);
}
/**
* @param filterType
* @service filter read
*/
public function getFilterNames(Gpf_Rpc_Params $params)
{
$condition = new Gpf_SqlBuilder_CompoundWhereCondition();
$condition->add('f.userid', '=', Gpf_Session::getAuthUser()->getUserId(), 'OR');
$condition->add('f.userid', '=', null, 'OR');
$selectBuilder = new Gpf_SqlBuilder_SelectBuilder();
$selectBuilder->select->add('f.filterid', 'id');
$selectBuilder->select->add('f.name', 'name');
$selectBuilder->from->add(Gpf_Db_Table_Filters::getName(), 'f');
$selectBuilder->where->add('f.filtertype', '=', $params->get('filterType'));
$selectBuilder->where->addCondition($condition);
$selectBuilder->orderBy->add('f.name');
$response = new Gpf_Data_RecordSet();
$response->load($selectBuilder);
return $response;
}
/**
*
* @return Gpf_Db_Task
*/
protected function getPendingTask($lastRunTaskId, $inclusion_type, $inclusion_tasks)
{
$sql = new Gpf_SqlBuilder_SelectBuilder();
$sql->select->addAll(Gpf_Db_Table_Tasks::getInstance());
$sql->from->add(Gpf_Db_Table_Tasks::getName());
$sql->where->add(Gpf_Db_Table_Tasks::DATEFINISHED, '=', null);
$sql->where->add(Gpf_Db_Table_Tasks::TYPE, '=', Gpf_Db_Task::TYPE_CRON);
if ($inclusion_type == self::INCLUDE_TASKS) {
$sql->where->add(Gpf_Db_Table_Tasks::CLASSNAME, 'IN', $inclusion_tasks);
} else {
if ($inclusion_type == self::EXCLUDE_TASKS) {
$sql->where->add(Gpf_Db_Table_Tasks::CLASSNAME, 'NOT IN', $inclusion_tasks);
}
}
$andCondition = new Gpf_SqlBuilder_CompoundWhereCondition();
$andCondition->add(Gpf_Db_Table_Tasks::IS_EXECUTING, '<>', Gpf::YES, 'OR');
$orCondition = new Gpf_SqlBuilder_CompoundWhereCondition();
$orCondition->add(Gpf_Db_Table_Tasks::IS_EXECUTING, '=', Gpf::YES);
$orCondition->add(Gpf_Db_Table_Tasks::DATECHANGED, '<', Gpf_Common_DateUtils::getDateTime(time() - self::MAX_TASK_RUN_TIME_WITHOUT_UPDATE));
$andCondition->addCondition($orCondition, 'OR');
$sql->where->addCondition($andCondition);
$sleepCondition = new Gpf_SqlBuilder_CompoundWhereCondition();
$sleepCondition->add(Gpf_Db_Table_Tasks::SLEEP_UNTIL, '=', null, 'OR');
$sleepCondition->add(Gpf_Db_Table_Tasks::SLEEP_UNTIL, '<=', Gpf_Common_DateUtils::now(), 'OR');
$sql->where->addCondition($sleepCondition);
$sql->orderBy->add(Gpf_Db_Table_Tasks::WORKING_AREA_TO . '-' . Gpf_Db_Table_Tasks::WORKING_AREA_FROM, false);
$sql->orderBy->add(Gpf_Db_Table_Tasks::DATECHANGED);
$recordset = $sql->getAllRows();
if ($recordset->getSize() == 0) {
return false;
}
if ($lastRunTaskId == null) {
return $this->getTask($recordset->get(0));
}
$recordId = $this->findLastRunTaskRecordsetPosition($recordset, $lastRunTaskId);
if ($recordId === null) {
return $this->getTask($recordset->get(0));
}
if ($recordId == $recordset->getSize() - 1) {
return false;
}
if ($recordset->get($recordId + 1) == null) {
return false;
}
return $this->getTask($recordset->get($recordId + 1));
}
/**
*
* @param $campaignId
* @param $commissionType
* @param $affiliateId
* @return Gpf_Data_RecordSet
*/
public function getAllUserCommissionTypes($campaignId = null, $commissionType = null, $affiliateId = null) {
$selectBuilder = $this->getAllCommissionTypesSelect($campaignId, $commissionType);
$selectBuilder->select->add(Pap_Db_Table_Campaigns::NAME, 'campaignname', 'c');
$selectBuilder->from->addInnerJoin(Pap_Db_Table_Campaigns::getName(), 'c', 'ct.'.self::CAMPAIGNID.'=c.'.Pap_Db_Table_Campaigns::ID);
if (Gpf_Session::getAuthUser()->getAccountId() != Gpf_Db_Account::DEFAULT_ACCOUNT_ID) {
$selectBuilder->where->add(Pap_Db_Table_Campaigns::ACCOUNTID, '=', Gpf_Session::getAuthUser()->getAccountId());
}
if ($affiliateId !== null && $affiliateId !== '') {
$selectBuilder->from->addLeftJoin(Pap_Db_Table_CommissionGroups::getName(), 'cg',
'ct.'.self::CAMPAIGNID.'=cg.'.Pap_Db_Table_CommissionGroups::CAMPAIGN_ID);
$selectBuilder->from->addLeftJoin(Pap_Db_Table_UserInCommissionGroup::getName(), 'uicg',
'cg.'.Pap_Db_Table_CommissionGroups::ID.'=uicg.'.Pap_Db_Table_UserInCommissionGroup::COMMISSION_GROUP_ID);
$condition = new Gpf_SqlBuilder_CompoundWhereCondition();
$subCondition = new Gpf_SqlBuilder_CompoundWhereCondition();
$subCondition->add('uicg.'.Pap_Db_Table_UserInCommissionGroup::USER_ID, '=', $affiliateId);
$subCondition->add('uicg.'.Pap_Db_Table_UserInCommissionGroup::STATUS, '=', 'A');
$condition->addCondition($subCondition, 'OR');
$condition->add('c.'.Pap_Db_Table_Campaigns::TYPE, '=', 'P', 'OR');
$selectBuilder->where->addCondition($condition);
$selectBuilder->groupBy->add('ct.'.self::ID);
}
return $selectBuilder->getAllRows();
}
protected function updateStatusSignupAndReferral()
{
$update = new Gpf_SqlBuilder_UpdateBuilder();
$update->from->add(Pap_Db_Table_Transactions::getName());
$update->set->add(Pap_Db_Table_Transactions::R_STATUS, Pap_Common_Constants::STATUS_APPROVED);
$update->where->add(Pap_Db_Table_Transactions::DATA5, '=', $this->getId());
$update->where->add(Pap_Db_Table_Transactions::R_STATUS, '=', Pap_Common_Constants::STATUS_PENDING);
$typeWhere = new Gpf_SqlBuilder_CompoundWhereCondition();
$typeWhere->add(Pap_Db_Table_Transactions::R_TYPE, '=', Pap_Db_Transaction::TYPE_SIGNUP_BONUS, 'OR');
$typeWhere->add(Pap_Db_Table_Transactions::R_TYPE, '=', Pap_Db_Transaction::TYPE_REFERRAL, 'OR');
$update->where->addCondition($typeWhere);
$update->execute();
}
/**
* @param string $formid
* @param string/array $status
* @return Gpf_Data_RecordSet
*/
public function getFieldsNoRpc($formid, $status = null, $mainFields = null)
{
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->add("f." . self::ID, "id");
$select->select->add("f." . self::CODE, "code");
$select->select->add("f." . self::NAME, "name");
$select->select->add("f." . self::TYPE, "type");
$select->select->add("f." . self::STATUS, "status");
$select->select->add("f." . self::AVAILABLEVALUES, "availablevalues");
$select->from->add($this->getName(), "f");
$select->where->add(Gpf_Db_Table_Accounts::ID, '=', Gpf_Application::getInstance()->getAccountId());
$select->where->add(self::FORMID, '=', $formid);
if ($status != null) {
if (is_array($status)) {
$condition = new Gpf_SqlBuilder_CompoundWhereCondition();
foreach ($status as $statusCode) {
$condition->add(self::STATUS, '=', $statusCode, 'OR');
}
$select->where->addCondition($condition);
} else {
$select->where->add(self::STATUS, '=', $status);
}
}
if ($mainFields != null && $mainFields == Gpf::YES) {
$condition = new Gpf_SqlBuilder_CompoundWhereCondition();
$condition->add(Gpf_Db_Table_Accounts::ID, '=', Gpf_Application::getInstance()->getAccountId());
$condition->add(self::FORMID, '=', $formid);
$conditionInner = new Gpf_SqlBuilder_CompoundWhereCondition();
$conditionInner->add(self::CODE, '=', 'parentuserid', 'OR');
$conditionInner->add(self::CODE, '=', 'refid', 'OR');
$conditionInner->add(self::CODE, '=', 'notificationemail', 'OR');
$condition->addCondition($conditionInner);
$select->where->addCondition($condition, 'OR');
}
//$select->orderBy->add("section");
$select->orderBy->add(self::ORDER);
$result = $select->getAllRows();
$result->addColumn("help", "");
return $result;
}
/**
* @param Gpf_SqlBuilder_SelectBuilder $select
* @param Gpf_Rpc_FilterCollection $allFilters
*/
private function addOrderIdFilterToSelect(Gpf_SqlBuilder_SelectBuilder $select, Gpf_Rpc_FilterCollection $allFilters) {
$filter = $this->getFirstElement($allFilters->getFilter('orderid'));
if ($filter === null) {
return;
}
$orderIds = preg_split("/[,;(\n)]/", $filter->getValue());
$condition = new Gpf_SqlBuilder_CompoundWhereCondition();
for ($i = 0; $i < count($orderIds); $i++) {
if(trim($orderIds[$i]) != '') {
$condition->add('orderid', 'LIKE', '%'.trim($orderIds[$i]).'%', 'OR');
}
}
$select->where->addCondition($condition);
}
private function initVisitsWhere(Gpf_SqlBuilder_WhereClause $where) {
$visitorCondition = new Gpf_SqlBuilder_CompoundWhereCondition();
$visitorCondition->add(Pap_Db_Table_Visits::VISITORID, '=', $this->visitorId);
if ($this->ip != '') {
$visitorCondition->add(Pap_Db_Table_Visits::IP, '=', $this->ip, 'OR');
}
$where->addCondition($visitorCondition);
$accountCondition = new Gpf_SqlBuilder_CompoundWhereCondition();
$accountCondition->add(Pap_Db_Table_Visits::ACCOUNTID, '=', $this->accountId);
if ($this->accountId == Gpf_Db_Account::DEFAULT_ACCOUNT_ID) {
$accountCondition->add(Pap_Db_Table_Visits::ACCOUNTID, '=', '', 'OR');
}
$where->addCondition($accountCondition);
$where->add(Pap_Db_Table_Visits::DATEVISIT, '<=', $this->toDate->toDateTime());
}
