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));
}
}
public function getStatsSelect(Pap_Stats_StatsSelectContext $statsSelectContext) {
if ($statsSelectContext->getGroupColumn() == Pap_Db_Table_Banners::ID) {
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->cloneObj($statsSelectContext->getSelectBuilder());
$select->select->replaceColumn($statsSelectContext->getGroupColumnAlias(), Pap_Db_Table_BannersInRotators::PARENT_BANNER_ID, $statsSelectContext->getGroupColumnAlias());
$select->groupBy->removeByName(Pap_Db_Table_Banners::ID);
$select->groupBy->add(Pap_Db_Table_BannersInRotators::PARENT_BANNER_ID);
$statsSelectContext->getUnionBuilder()->addSelect($select);
}
}
/**
* Returns banner object
*
* @throws Gpf_DbEngine_NoRowException
* @param string $bannerId banner ID
* @return Pap_Common_Banner
*/
public function getBanner($bannerId) {
if ($bannerId == '') {
throw new Pap_Common_Banner_NotFound($bannerId);
}
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->from->add(Pap_Db_Table_Banners::getName());
$select->select->addAll(Pap_Db_Table_Banners::getInstance());
$select->where->add(Pap_Db_Table_Banners::ID, '=', $bannerId);
return $this->getBannerFromRecord($select->getOneRow());
}
public static function getUserChannels($userId) {
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->add(self::ID);
$select->select->add(self::NAME);
$select->select->add(self::VALUE);
$select->from->add(self::getName());
$select->where->add(self::USER_ID, "=", $userId);
return $select->getAllRows();
}
/**
* Return all mail attachments for given mail id
*
* @param int $mailId
* @return Gpf_Data_RecordSet
*/
public static function getMailAttachments($mailId)
{
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->add('f.*');
$select->select->add('ma.is_included_image', 'is_included_image');
$select->from->add(Gpf_Db_Table_MailAttachments::getName(), 'ma');
$select->from->addInnerJoin(Gpf_Db_Table_Files::getName(), 'f', 'f.fileid=ma.fileid');
$select->where->add('mailid', '=', $mailId);
return $select->getAllRows();
}
private function deactivateInRebrand() {
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->addAll(Pap_Db_Table_Banners::getInstance());
$select->from->add(Pap_Db_Table_Banners::getName());
$select->where->add(Pap_Db_Table_Banners::TYPE, '=', Pap_Features_RebrandPdfBanner_Config::TYPE);
foreach ($select->getAllRowsIterator() as $rebrandData) {
$this->removeCouponConstants($rebrandData);
}
}
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().')';
}
public function getCount() {
$selectBuilder = $this->getSelect();
$selectBuilder->select->add('i.affiliateid', 'affiliateid');
$selectBuilderCount = new Gpf_SqlBuilder_SelectBuilder();
$selectBuilderCount->select->add('count(*)', 'count');
$selectBuilderCount->from->addSubselect($selectBuilder, 'sub');
$row = $selectBuilderCount->getOneRow();
return $row->get('count');
}
public function loadChannels() {
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->add(Pap_Db_Table_Channels::VALUE);
$select->select->add(Pap_Db_Table_Channels::NAME);
$select->from->add(Pap_Db_Table_Channels::getName());
$select->where->add(Pap_Db_Table_Channels::USER_ID, '=', Gpf_Session::getAuthUser()->getPapUserId());
$this->channels = array();
foreach ($select->getAllRowsIterator() as $row) {
$this->channels[$row->get(Pap_Db_Table_Channels::VALUE)] = $row->get(Pap_Db_Table_Channels::NAME);
}
}
/**
* @return Gpf_Data_RecordSet
*/
protected function getMatchingCampaignsRecordSet($productId) {
$selectBuilder = new Gpf_SqlBuilder_SelectBuilder();
$selectBuilder->select->add(Pap_Db_Table_Campaigns::ID);
$selectBuilder->select->add(Pap_Db_Table_Campaigns::PRODUCT_ID);
$selectBuilder->from->add(Pap_Db_Table_Campaigns::getName());
$selectBuilder->where->add(Pap_Db_Table_Campaigns::PRODUCT_ID, 'REGEXP', '[,[:space:]]'.$productId.'[,[:space:]]', 'OR');
$selectBuilder->where->add(Pap_Db_Table_Campaigns::PRODUCT_ID, 'REGEXP', '^'.$productId.'$', 'OR');
$selectBuilder->where->add(Pap_Db_Table_Campaigns::PRODUCT_ID, 'REGEXP', '^'.$productId.'[,[:space:]]', 'OR');
$selectBuilder->where->add(Pap_Db_Table_Campaigns::PRODUCT_ID, 'REGEXP', '[,[:space:]]'.$productId.'$', 'OR');
return $selectBuilder->getAllRows();
}
public function __construct()
{
for ($i = 0; $i < 10; $i++) {
$this->groupId = substr(md5(uniqid(rand(), true)), 0, 16);
$selectBuilder = new Gpf_SqlBuilder_SelectBuilder();
$selectBuilder->select->add(Gpf_Db_Table_QuickTasks::GROUPID);
$selectBuilder->from->add(Gpf_Db_Table_QuickTasks::getName());
$selectBuilder->where->add(Gpf_Db_Table_QuickTasks::GROUPID, '=', $this->groupId);
if ($selectBuilder->getAllRows()->getSize() == 0) {
break;
}
}
}
public function execute() {
$templatesSelect = new Gpf_SqlBuilder_SelectBuilder();
$templatesSelect->select->addAll(Gpf_Db_Table_MailTemplates::getInstance());
$templatesSelect->from->add(Gpf_Db_Table_MailTemplates::getName());
$templatesSelect->where->add(Gpf_Db_Table_MailTemplates::CLASS_NAME, 'IN',
array('Pap_Mail_Reports_AffDailyReport', 'Pap_Mail_Reports_AffMonthlyReport', 'Pap_Mail_Reports_AffWeeklyReport'));
$t = new Gpf_Db_MailTemplate();
$templates = $t->loadCollectionFromRecordset($templatesSelect->getAllRows());
foreach ($templates as $template) {
$this->processTemplate($template);
}
}
/**
*
* @service
* @anonym
* @return Gpf_Rpc_Data
*/
public function ping(Gpf_Rpc_Params $params)
{
$sql = new Gpf_SqlBuilder_SelectBuilder();
$sql->select->addAll(Gpf_Db_Table_AuthUsers::getInstance());
$sql->from->add(Gpf_Db_Table_AuthUsers::getName());
$count = $sql->getAllRows()->getSize();
if ($count == 0) {
throw new Gpf_Exception($this->_('Ping failed'));
}
$data = new Gpf_Rpc_Data();
$data->setValue('status', 'OK');
return $data;
}
/**
* Get recordset of active languages in this account
*
* @return Gpf_Data_IndexedRecordSet
*/
public function getActiveLanguagesNoRpc()
{
$sql = new Gpf_SqlBuilder_SelectBuilder();
$sql->select->add(Gpf_Db_Table_Languages::CODE);
$sql->select->add(Gpf_Db_Table_Languages::ENGLISH_NAME);
$sql->select->add(Gpf_Db_Table_Languages::NAME);
$sql->select->add(Gpf_Db_Table_Languages::IS_DEFAULT);
$sql->from->add(Gpf_Db_Table_Languages::getName());
$sql->where->add(Gpf_Db_Table_Accounts::ID, '=', Gpf_Application::getInstance()->getAccountId());
$sql->where->add(Gpf_Db_Table_Languages::ACTIVE, '=', Gpf::YES);
$sql->orderBy->add(Gpf_Db_Table_Languages::NAME);
return $sql->getAllRowsIndexedBy(Gpf_Db_Table_Languages::CODE);
}
public function getAllNoRpc() {
$selectBuilder = new Gpf_SqlBuilder_SelectBuilder();
$selectBuilder->select->add(self::ID, 'id');
$selectBuilder->select->add(self::CODE, 'code');
$selectBuilder->select->add(self::PARAMS, 'params');
$selectBuilder->select->add(self::TITLE, 'title');
$selectBuilder->select->add(self::ICON, 'icon');
$selectBuilder->select->add(self::DESCRIPTION, 'description');
$selectBuilder->select->add(self::SHOWHEADER, 'showheader');
$selectBuilder->from->add(self::getName());
return $this->replaceUserVariablesInParams($selectBuilder->getAllRows());
}
protected function loadRow(Gpf_DbEngine_Row $row) {
$selectBuilder = new Gpf_SqlBuilder_SelectBuilder();
$selectBuilder->from->add($row->getTable()->name());
$selectBuilder->select->addAll($row->getTable());
foreach ($this->uniqueColumnNames as $columnName=>$value) {
if ($value === false) {
$selectBuilder->where->add($columnName,'=',$row->get($columnName));
continue;
}
$selectBuilder->where->add($columnName,'=',$value);
}
return $selectBuilder->getOneRow();
}
