PHP Gpf_SqlBuilder_SelectBuilder类代码示例

 public function execute() {
     $task = new Gpf_Db_Task();
     $task->setClassName('Gpf_Mail_OutboxRunner');
     $task->loadFromData(array(Gpf_Db_Table_Tasks::CLASSNAME));
     
     $select = new Gpf_SqlBuilder_SelectBuilder();
     $select->select->add(Gpf_Db_Table_MailOutbox::ID);
     $select->from->add(Gpf_Db_Table_MailOutbox::getName());
     $select->orderBy->add(Gpf_Db_Table_MailOutbox::ID);
     $select->limit->set(0,1);
     try {
     	$row = $select->getOneRow();
     	$begining = $row->get(Gpf_Db_Table_MailOutbox::ID);
     } catch (Gpf_Exception $e) {
     	$begining = 1;
     }
     
     try {
         $task->load();
         $task->setParams($begining);
         $task->setWorkingAreaFrom($begining);   
         $task->setWorkingAreaTo(Gpf_Mail_OutboxRunner::MAX_MAIL_WORKERS_COUNT);
         $task->update(array(Gpf_Db_Table_Tasks::WORKING_AREA_FROM, Gpf_Db_Table_Tasks::WORKING_AREA_TO, Gpf_Db_Table_Tasks::PARAMS));
     } catch (Gpf_Exception $e) {
         throw new Gpf_Exception('Cannot update Gpf_Mail_OutboxRunner task to paralel version');
     }
 }

    /**
     *
     * @service affiliate_stats read
     * @param $data
     */
    public function load(Gpf_Rpc_Params $params) {
        $data = new Gpf_Rpc_Data($params);
        $filters = $data->getFilters();
		$statsParams = new Pap_Stats_Params();
		$statsParams->initFrom($filters);				
		$transactions = new Pap_Stats_Transactions($statsParams);                

        $select = new Gpf_SqlBuilder_SelectBuilder();
        $select->from->add(Pap_Db_Table_Users::getName(), "pu");
        $select->from->addInnerJoin(Gpf_Db_Table_Users::getName(), "gu", "gu.accountuserid = pu.accountuserid");
        $select->select->add("SUM(IF(pu.rtype = '". Pap_Application::ROLETYPE_AFFILIATE ."', 1, 0))", "countAffiliates");
        $select->select->add("SUM(IF(gu.rstatus = 'A', 1, 0))", "approvedAffiliates");
        $select->select->add("SUM(IF(gu.rstatus = 'P', 1, 0))", "pendingAffiliates");
        $filters->addTo($select->where);
        $select->where->add("pu.".Pap_Db_Table_Users::TYPE, "=", Pap_Application::ROLETYPE_AFFILIATE);        
        $row = $select->getOneRow();

        $data->setValue("countAffiliates", $this->checkNullValue($row->get("countAffiliates")));
        $data->setValue("approvedAffiliates", $this->checkNullValue($row->get("approvedAffiliates")));
        $data->setValue("pendingAffiliates", $this->checkNullValue($row->get("pendingAffiliates")));
        $data->setValue("totalSales", $transactions->getTotalCost()->getAll());
        $data->setValue("approvedCommissions", $transactions->getCommission()->getApproved());
        $data->setValue("pendingCommissions", $transactions->getCommission()->getPending());

        return $data;
    }

 public function computeCommissions() {
     $this->sqlBuilder = new Gpf_SqlBuilder_SelectBuilder();
     $this->sqlBuilder->select->add("SUM(".self::TRANSACTION_ALIAS.'.'.Pap_Db_Table_Transactions::COMMISSION.")", self::VALUE);
     $this->buildSql();
     $record = $this->sqlBuilder->getOneRow();
     return $record->get(self::VALUE);
 }

	/**
	 * @return array
	 */
	public function getChildren(Pap_Common_User $parent, $offset = '', $limit = '') {
		$children = array();

		$selectBuilder = new Gpf_SqlBuilder_SelectBuilder();
		$selectBuilder->select->addAll(Pap_Db_Table_Users::getInstance(), 'pu');
		$selectBuilder->select->addAll(Gpf_Db_Table_AuthUsers::getInstance(), 'au');

		$selectBuilder->from->add(Pap_Db_Table_Users::getName(), 'pu');
		$selectBuilder->from->addInnerJoin(Gpf_Db_Table_Users::getName(), 'gu', 'pu.accountuserid = gu.accountuserid');
		$selectBuilder->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(), 'au', 'gu.authid = au.authid');

		$selectBuilder->where->add(Pap_Db_Table_Users::PARENTUSERID, '=', $parent->getId());
		$selectBuilder->where->add(Gpf_Db_Table_Users::STATUS, 'IN', array(Pap_Common_Constants::STATUS_APPROVED, Pap_Common_Constants::STATUS_PENDING));
		$selectBuilder->orderBy->add(Pap_Db_Table_Users::DATEINSERTED);

		$selectBuilder->limit->set($offset, $limit);
			
		foreach ($selectBuilder->getAllRowsIterator() as $userRecord) {
			$user = new Pap_Common_User();
			$user->fillFromRecord($userRecord);
			$children[] = $user;
		}

		return $children;
	}

 /**
  * @param String $campaignId
  * @return Gpf_SqlBuilder_SelectIterator
  */
 private function getRules($campaignId) {
     $select = new Gpf_SqlBuilder_SelectBuilder();
     $select->select->addAll(Pap_Db_Table_Rules::getInstance());
     $select->from->add(Pap_Db_Table_Rules::getName());
     $select->where->add(Pap_Db_Table_Rules::CAMPAIGN_ID, '=', $campaignId);
     return $select->getAllRowsIterator();
 }

	/**
	 *
	 * @service banner_stats read
	 * @param $data
	 */
	public function loadFullStatistics(Gpf_Rpc_Params $params) {
		$data = new Gpf_Rpc_Data($params);

		$select = new Gpf_SqlBuilder_SelectBuilder();
		$select->from->add(Pap_Db_Table_Banners::getName());
		$select->select->add("COUNT(".Pap_Db_Table_Banners::ID.")", "count");
		$select->select->add(Pap_Db_Table_Banners::TYPE, 'type');
		$select->groupBy->add(Pap_Db_Table_Banners::TYPE);
		
		Gpf_Plugins_Engine::extensionPoint('AffiliateNetwork.modifyWhere', 
        new Gpf_Common_SelectBuilderCompoundRecord($select, new Gpf_Data_Record(array(), array())));
		
        $result = $select->getAllRowsIndexedBy('type');
		$bannerTypes = explode(',', $data->getParam('bannerTypes'));		
		
		$bannersCount = 0;
		foreach ($bannerTypes as $bannerType) {
			$bannerTypeCount = 0;
			try {
				$bannerTypeCount = $result->getRecord($bannerType)->get('count');
			} catch (Gpf_Data_RecordSetNoRowException $e) {				
			}
			$data->setValue($bannerType, "$bannerTypeCount");
			$bannersCount += $bannerTypeCount;
		}
		$data->setValue("bannersCount", "$bannersCount");		

		return $data;
	}

    /**
     * @service pay_affiliate read
     * @param $data
     */
    public function load(Gpf_Rpc_Params $params) {
        $data = new Gpf_Rpc_Data($params);

        $select = new Gpf_SqlBuilder_SelectBuilder();
        $select->select->add("au.username","userName");
        $select->select->add("au.firstname","firstName");
        $select->select->add("au.lastname","lastname");
        $select->select->add("SUM(t.".Pap_Db_Table_Transactions::COMMISSION.")", "payout");
        $select->select->add("pu.".Pap_Db_Table_Users::MINIMUM_PAYOUT, "minimumPayout");
        $select->select->add("IFNULL(po.".Gpf_Db_Table_FieldGroups::NAME.", 'undefined')", "payoutMethod");
        $select->from->add(Pap_Db_Table_Transactions::getName(), "t");
        $select->from->addInnerJoin(Pap_Db_Table_Users::getName(), "pu", "t.userid = pu.userid");
        $select->from->addInnerJoin(Gpf_Db_Table_Users::getName(), "gu", "pu.accountuserid = gu.accountuserid");
        $select->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(), "au", "gu.authid = au.authid");
        $select->groupBy->add("t.".Pap_Db_Table_Transactions::USER_ID);
        $select->from->addLeftJoin(Gpf_Db_Table_FieldGroups::getName(), "po", "pu.payoutoptionid = po.fieldgroupid");
        $select->where->add("t.".Pap_Db_Table_Transactions::USER_ID, "=", $params->get("id"));
        $row = $select->getOneRow();

        $data->setValue("userName", $row->get("userName"));
        $data->setValue("firstName", $row->get("firstName"));
        $data->setValue("lastname", $row->get("lastname"));
        $data->setValue("payout", $row->get("payout"));
        $data->setValue("minimumPayout", $row->get("minimumPayout"));
        $data->setValue("payoutMethod", $row->get("payoutMethod"));

        return $data;
    }

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

 protected function getChildAffiliateTransactionsCount($userId){
     $select = new Gpf_SqlBuilder_SelectBuilder();
     $select->select->add('count('.Pap_Db_Table_Transactions::USER_ID.')','numberOfTransactions');
     $select->from->add(Pap_Db_Table_Transactions::getName());
     $select->where->add(Pap_Db_Table_Transactions::USER_ID,'=',$userId);
     $row = $select->getOneRow();
     return $row->get('numberOfTransactions');
 }

 private function isMasterMerchant($authId) {
     $select = new Gpf_SqlBuilder_SelectBuilder();
     $select->select->add(Gpf_Db_Table_Users::ROLEID);
     $select->from->add(Gpf_Db_Table_AuthUsers::getName(), 'au');
     $select->from->addInnerJoin(Gpf_Db_Table_Users::getName(), 'u', 'au.' . Gpf_Db_Table_AuthUsers::ID . '=u.' . Gpf_Db_Table_Users::AUTHID);
     $select->where->add('au.' . Gpf_Db_Table_AuthUsers::ID, '=', $authId);
     return $select->getOneRow()->get(Gpf_Db_Table_Users::ROLEID) == Pap_Application::DEFAULT_ROLE_MERCHANT;
 }

 private function getLogsCount()
 {
     $select = new Gpf_SqlBuilder_SelectBuilder();
     $select->select->add('count(*)', 'numrows');
     $select->from->add(Gpf_Db_Table_Logs::getName());
     $row = $select->getOneRow();
     return $row->get('numrows');
 }

 /**
  * @param $bannerID
  * @return Iterator
  */
 protected function getCoupons($bannerID, $userID) {
     $selectBuilder = new Gpf_SqlBuilder_SelectBuilder();
     $selectBuilder->select->addAll(Pap_Db_Table_Coupons::getInstance());
     $selectBuilder->from->add(Pap_Db_Table_Coupons::getName());
     $selectBuilder->where->add(Pap_Db_Table_Coupons::BANNERID, '=', $bannerID);
     $selectBuilder->where->add(Pap_Db_Table_Coupons::USERID, '=', $userID);
     $selectBuilder->limit->set(0, 1);
     return $selectBuilder->getAllRowsIterator();
 }

 private function getOldestEntry($table) {
     $selectBuilder = new Gpf_SqlBuilder_SelectBuilder();
     $selectBuilder->select->add('day');
     $selectBuilder->from->add($table);
     $selectBuilder->orderBy->add('day');
     $selectBuilder->limit->set(0, 1);
     $row = $selectBuilder->getOneRow();
     return $row->get('day');
 }

 public function loadAttributes($userId)
 {
     $select = new Gpf_SqlBuilder_SelectBuilder();
     $select->select->add(self::NAME);
     $select->select->add(self::VALUE);
     $select->from->add(self::getName(), 'ua');
     $select->where->add(self::ACCOUNT_USER_ID, '=', $userId);
     $this->attributes = $select->getAllRowsIndexedBy('name');
 }

 protected function processAllRows(Gpf_SqlBuilder_SelectBuilder $select) {
     $this->isComputed = true;
     if (!$select->select->existsAlias(self::ACTIONS_COMMISSIONTYPEID)) {
         $select->select->add('s.'.self::ACTIONS_COMMISSIONTYPEID);
         $select->groupBy->add('s.'.self::ACTIONS_COMMISSIONTYPEID);
     }
     foreach ($select->getAllRowsIterator() as $resultRow) {
         $this->processRow($resultRow, $resultRow->get(self::ACTIONS_COMMISSIONTYPEID));
     }
 }