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