public function getNumberOfSubaffiliates($userid = null) {
$result = new Gpf_Data_RecordSet();
$selectBuilder = new Gpf_SqlBuilder_SelectBuilder();
$selectBuilder->select->add('COUNT(u.userid)', 'amount');
$selectBuilder->from->add(Pap_Db_Table_Users::getName(), "u");
$selectBuilder->from->addInnerJoin(Gpf_Db_Table_Users::getName(), "gu", "u.accountuserid = gu.accountuserid");
$selectBuilder->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(), "au", "gu.authid = au.authid");
$selectBuilder->where->add('u.deleted', '=', Gpf::NO);
if ($userid === null) {
$selectBuilder->where->add('u.parentuserid', '=', Gpf_Session::getAuthUser()->getPapUserId());
} else {
$selectBuilder->where->add('u.parentuserid', '=', $userid);
}
$selectBuilder->where->add('gu.rstatus', '<>', 'D');
if ($this->params->isDateFromDefined()) {
$selectBuilder->where->add('u.' . Pap_Stats_Table::DATEINSERTED, '>=', $this->params->getDateFrom()->toDateTime());
}
if ($this->params->isDateToDefined()) {
$selectBuilder->where->add('u.' . Pap_Stats_Table::DATEINSERTED, '<=', $this->params->getDateTo()->toDateTime());
}
$selectBuilder->limit->set(0, 1);
$result->load($selectBuilder);
if($result->getSize() == 0) {
return 0;
}
foreach($result as $record) {
return $record->get('amount');
}
}
/**
* @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;
}
/**
* @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 static function getInstance()
{
if (self::$instance === null) {
self::$instance = new self();
}
return self::$instance;
}
protected function buildFrom()
{
$this->_selectBuilder->from->add(Gpf_Db_Table_LoginsHistory::getName(), 'l');
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_Users::getName(), 'u', 'l.accountuserid=u.accountuserid');
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(), 'au', 'u.authid=au.authid');
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_Roles::getName(), 'r', 'r.roleid=u.roleid');
}
function buildFrom() {
$this->_selectBuilder->from->add(Pap_Db_Table_Payouts::getName(), "p");
$this->_selectBuilder->from->addInnerJoin(Pap_Db_Table_Users::getName(), "pu", "p.userid = pu.userid");
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_Users::getName(), "gu", "pu.accountuserid = gu.accountuserid");
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(), "au", "gu.authid = au.authid");
$this->_selectBuilder->from->addLeftJoin(Gpf_Db_Table_FieldGroups::getName(), "po", "pu.payoutoptionid = po.fieldgroupid");
$this->_selectBuilder->from->addLeftJoin(Gpf_Db_Table_Currencies::getName(), "c", "p.currencyid = c.currencyid");
}
function buildFrom() {
$this->_selectBuilder->from->add(Pap_Db_Table_Payouts::getName(), "p");
$this->_selectBuilder->from->addInnerJoin(Pap_Db_Table_PayoutsHistory::getName(), "ph", "p.payouthistoryid = ph.payouthistoryid");
$this->_selectBuilder->from->addInnerJoin(Pap_Db_Table_Users::getName(), "pu", "p.userid = pu.userid");
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_Users::getName(), "gu", "pu.accountuserid = gu.accountuserid");
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(), "au", "gu.authid = au.authid");
$this->_selectBuilder->from->addLeftJoin(Gpf_Db_Table_Currencies::getName(), "c", "p.currencyid = c.currencyid");
}
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;
}
protected function buildFrom() {
$this->_selectBuilder->from->add(Pap_Db_Table_AffiliateTrackingCodes::getName(), 'atc');
$this->_selectBuilder->from->addInnerJoin(Pap_Db_Table_CommissionTypes::getName(), 'com', 'com.commtypeid=atc.commtypeid');
$this->_selectBuilder->from->addInnerJoin(Pap_Db_Table_Campaigns::getName(), 'cam', 'cam.campaignid=com.campaignid');
$this->_selectBuilder->from->addInnerJoin(Pap_Db_Table_Users::getName(), 'u', 'u.userid=atc.userid');
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_Users::getName(), 'gu', 'u.accountuserid=gu.accountuserid');
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(), 'au', 'au.authid=gu.authid');
}
protected function buildFrom() {
$this->_selectBuilder->from->add(Pap_Db_Table_Coupons::getName(), 'c');
$this->_selectBuilder->from->addInnerJoin(Pap_Db_Table_Users::getName(),
'pu', 'pu.'.Pap_Db_Table_Users::ID.'=c.'.Pap_Db_Table_Coupons::USERID);
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_Users::getName(),
'gu', 'pu.'.Pap_Db_Table_Users::ACCOUNTUSERID.'=gu.'.Gpf_Db_Table_Users::ID);
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(),
'au', 'au.'.Gpf_Db_Table_AuthUsers::ID.'=gu.'.Gpf_Db_Table_Users::AUTHID);
}
protected function buildFrom() {
$this->_selectBuilder->from->add(Pap_Db_Table_DirectLinkUrls::getName(), "l");
$this->_selectBuilder->from->addInnerJoin(Pap_Db_Table_Users::getName(), "pu", "l.userid = pu.userid");
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_Users::getName(), "gu", "pu.accountuserid = gu.accountuserid");
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(), "au", "gu.authid = au.authid");
$this->_selectBuilder->from->addLeftJoin(Pap_Db_Table_Channels::getName(), "ch", "ch.channelid = l.channelid");
$this->_selectBuilder->from->addLeftJoin(Pap_Db_Table_Banners::getName(), "b", "b.bannerid = l.bannerid");
$this->_selectBuilder->from->addLeftJoin(Pap_Db_Table_Campaigns::getName(), "c", "c.campaignid = l.campaignid");
}
protected function buildFrom() {
$this->_selectBuilder->from->add(Pap_Db_Table_UserInCommissionGroup::getName(), 'ucg');
$this->_selectBuilder->from->addInnerJoin(Pap_Db_Table_CommissionGroups::getName(), 'cg',
'cg.'.Pap_Db_Table_CommissionGroups::ID.'=ucg.'.Pap_Db_Table_UserInCommissionGroup::COMMISSION_GROUP_ID);
$this->_selectBuilder->from->addInnerJoin(Pap_Db_Table_Users::getName(),
'u', 'ucg.userid=u.userid');
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_Users::getName(),
'gu', 'u.accountuserid=gu.accountuserid');
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(),
'au', 'au.authid=gu.authid');
}
function buildFrom() {
$this->_selectBuilder->from->add(Pap_Db_Table_RecurringCommissions::getName(), 'rc');
$this->_selectBuilder->from->addLeftJoin(Pap_Db_Table_RecurringCommissionEntries::getName(),
"rce", "rce.recurringcommissionid = rc.recurringcommissionid AND rce.tier = 1");
$this->_selectBuilder->from->addLeftJoin(Pap_Db_Table_Users::getName(), "pu", "rce.userid = pu.userid");
$this->_selectBuilder->from->addLeftJoin(Gpf_Db_Table_Users::getName(), "gu", "pu.accountuserid = gu.accountuserid");
$this->_selectBuilder->from->addLeftJoin(Gpf_Db_Table_AuthUsers::getName(), "au", "gu.authid = au.authid");
$this->_selectBuilder->from->addLeftJoin(Pap_Db_Table_Transactions::getName(), "t", "t.transid = rc.transid");
$this->_selectBuilder->from->addLeftJoin(Gpf_Db_Table_RecurrencePresets::getName(), 'rp', 'rp.recurrencepresetid = rc.recurrencepresetid');
}
protected function buildFrom() {
$this->_selectBuilder->from->add(Pap_Db_Table_Users::getName(), 'u');
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_Users::getName(),
'gu', 'u.accountuserid=gu.accountuserid');
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(),
'au', 'au.authid=gu.authid');
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_Accounts::getName(),
'a', 'a.accountid=gu.accountid');
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_Roles::getName(),
'r', 'r.roleid=gu.roleid');
}
/**
* @return Gpf_SqlBuilder_SelectBuilder
*/
protected function createSelectBuilder()
{
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->add("users." . Gpf_Db_Table_Users::ID, self::ID);
$select->select->add("authUsers." . Gpf_Db_Table_AuthUsers::USERNAME, 'username');
$select->select->add("authUsers." . Gpf_Db_Table_AuthUsers::FIRSTNAME, 'firstname');
$select->select->add("authUsers." . Gpf_Db_Table_AuthUsers::LASTNAME, 'lastname');
$select->from->add(Gpf_Db_Table_Users::getName(), 'users');
$select->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(), 'authUsers', "users." . Gpf_Db_Table_Users::AUTHID . "=authUsers." . Gpf_Db_Table_AuthUsers::ID);
Gpf_Plugins_Engine::extensionPoint('Gpf_Common_UserRichListBox.createSelectBuilder', $select->where);
$select->orderBy->add("authUsers." . Gpf_Db_Table_AuthUsers::USERNAME);
return $select;
}
/**
*
* @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;
}
protected function getUserId($username) {
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->add('pu.'.Pap_Db_Table_Users::ID, 'id');
$select->from->add(Pap_Db_Table_Users::getName(), 'pu');
$select->from->addInnerJoin(Gpf_Db_Table_Users::getName(), 'gu',
'pu.'.Pap_Db_Table_Users::ACCOUNTUSERID.'=gu.'.Gpf_Db_Table_Users::ID);
$select->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(), 'au',
'gu.'.Gpf_Db_Table_Users::AUTHID.'=au.'.Gpf_Db_Table_AuthUsers::ID);
$select->where->add('pu.'.Pap_Db_Table_Users::TYPE, '=', Pap_Application::ROLETYPE_AFFILIATE);
$select->where->add('pu.'.Pap_Db_Table_Users::DELETED, '<>', Gpf::YES);
$select->where->add('au.'.Gpf_Db_Table_AuthUsers::USERNAME, '=', $username);
return $select->getOneRow()->get('id');
}
/**
* @throws Gpf_DbEngine_NoRowException
* @return Pap_Common_User
*/
public function getMerchant() {
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->add('pu.'.Pap_Db_Table_Users::ID, Pap_Db_Table_Users::ID);
$select->from->add(Pap_Db_Table_Users::getName(), 'pu');
$select->from->addInnerJoin(Gpf_Db_Table_Users::getName(), 'gu',
'pu.'.Pap_Db_Table_Users::ACCOUNTUSERID.'=gu.'.Gpf_Db_Table_Users::ID);
$select->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(), 'au',
'au.'.Gpf_Db_Table_AuthUsers::ID.'=gu.'.Gpf_Db_Table_Users::AUTHID);
$select->where->add('au.'.Gpf_Db_Table_AuthUsers::USERNAME, '=', $this->getEmail());
$select->where->add('gu.'.Gpf_Db_Table_Users::ACCOUNTID, '=', $this->getId());
$merchantId = $select->getOneRow()->get(Pap_Db_Table_Users::ID);
return Pap_Merchants_User::getUserById($merchantId);
}
/**
* Load Location of last 20 IP addresses of logins of selected affiliate
*
* @service online_user read
* @return Gpf_Data_RecordSet
*/
public function getAffiliateLogins(Gpf_Rpc_Params $params) {
$sql = new Gpf_SqlBuilder_SelectBuilder();
$sql->select->add('l.'.Gpf_Db_Table_LoginsHistory::IP);
$sql->select->add('MAX(l.'.Gpf_Db_Table_LoginsHistory::LOGIN . ')', 'login');
$sql->from->add(Gpf_Db_Table_LoginsHistory::getName(), 'l');
$sql->from->addInnerJoin(Gpf_Db_Table_Users::getName(), 'u', 'l.accountuserid=u.accountuserid');
$sql->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(), 'au', 'u.authid=au.authid');
$sql->where->add(Gpf_Db_Table_AuthUsers::USERNAME, '=', $params->get('username'));
$sql->where->add('l.'.Gpf_Db_Table_LoginsHistory::IP, '<>', '127.0.0.1');
$sql->orderBy->add(Gpf_Db_Table_LoginsHistory::LOGIN, false);
$sql->groupBy->add('l.'.Gpf_Db_Table_LoginsHistory::IP);
$sql->limit->set(0, 20);
$recordset = $sql->getAllRows();
$recordset->addColumn('countryCode', '');
$recordset->addColumn('countryName', '');
$recordset->addColumn('city', '');
$recordset->addColumn('latitude', '');
$recordset->addColumn('longitude', '');
$recordset->addColumn('postalCode', '');
$recordset->addColumn('region', '');
foreach ($recordset as $record) {
$location = new GeoIp_Location();
$location->setIpString($record->get('ip'));
$location->load();
$record->set('countryCode', $location->getCountryCode());
$record->set('countryName', $location->getCountryName());
$record->set('city', $location->getCity());
$record->set('latitude', $location->getLatitude());
$record->set('longitude', $location->getLongitude());
$record->set('postalCode', $location->getPostalCode());
$record->set('region', $location->getRegion());
$record->set('login', Gpf_Common_DateUtils::getHumanRelativeTime(Gpf_Common_DateUtils::getClientTime(
Gpf_Common_DateUtils::mysqlDateTime2Timestamp($record->get('login')))));
}
return $recordset;
}
/**
* @return Gpf_SqlBuilder_SelectBuilder
*/
private function getAffiliatesToPaySelect($payoutOptionId = null) {
$selectBuilder = new Gpf_SqlBuilder_SelectBuilder();
$selectBuilder->select->addAll(Pap_Db_Table_Users::getInstance(), 'pu');
$selectBuilder->select->addAll(Gpf_Db_Table_AuthUsers::getInstance(), 'au');
$selectBuilder->select->add('SUM('.Pap_Db_Table_Transactions::COMMISSION.')', 'amountRaw');
$selectBuilder->from->add(Pap_Db_Table_Transactions::getName(), 't');
$selectBuilder->from->addInnerJoin(Pap_Db_Table_Users::getName(), 'pu', 't.userid = pu.userid');
$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');
if ($payoutOptionId != null) {
$selectBuilder->where->add('pu.'.Pap_Db_Table_Users::PAYOUTOPTION_ID, '=', $payoutOptionId);
}
$selectBuilder->where->add('t.'.Pap_Db_Table_Transactions::PAYOUTHISTORY_ID, '=', Pap_Common_Transaction::PAYMENT_PENDING_ID);
$selectBuilder->groupBy->add(Pap_Db_Table_Transactions::USER_ID);
return $selectBuilder;
}
/**
* @return Gpf_SqlBuilder_SelectBuilder
*/
protected function createSelectBuilder() {
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->add("users.".Pap_Db_Table_Users::ID, self::ID);
$select->select->add("users.".Pap_Db_Table_Users::REFID, 'refid');
$select->select->add("authUsers.".Gpf_Db_Table_AuthUsers::USERNAME, 'username');
$select->select->add("authUsers.".Gpf_Db_Table_AuthUsers::FIRSTNAME, 'firstname');
$select->select->add("authUsers.".Gpf_Db_Table_AuthUsers::LASTNAME, 'lastname');
$select->from->add(Pap_Db_Table_Users::getName(), 'users');
$select->from->addInnerJoin(Gpf_Db_Table_Users::getName(), 'accountUsers',
'users.'.Pap_Db_Table_Users::ACCOUNTUSERID."=accountUsers.".Gpf_Db_Table_Users::ID);
$select->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(), 'authUsers',
"accountUsers.".Gpf_Db_Table_Users::AUTHID."=authUsers.".Gpf_Db_Table_AuthUsers::ID);
$select->where->add('users.'.Pap_Db_Table_Users::TYPE,
'=', Pap_Application::ROLETYPE_AFFILIATE);
$select->where->add('users.'.Pap_Db_Table_Users::DELETED,
'<>', Gpf::YES);
$select->orderBy->add("authUsers.".Gpf_Db_Table_AuthUsers::USERNAME);
$this->modifySelect($select);
return $select;
}
public function addUsernameConstraint(Gpf_Db_Table_AuthUsers $usersTable) {
$usersTable->addConstraint(new Gpf_DbEngine_Row_RegExpConstraint(Gpf_Db_Table_AuthUsers::USERNAME, Gpf_Settings::get(UsernameConstraint_Config::CUSTOM_USERNAME_FORMAT), Gpf_Settings::get(UsernameConstraint_Config::CUSTOM_ERROR_MESSAGE)));
}
/**
* Get recordset of merchants recipients
*
* @param $from
* @return Gpf_Data_RecordSet
*/
private function getMerchantsRecipients($from) {
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->addAll(Gpf_Db_Table_AuthUsers::getInstance(), 'au');
$select->select->addAll(Gpf_Db_Table_Accounts::getInstance(), 'a');
$select->select->addAll(Gpf_Db_Table_Users::getInstance(), 'gu');
$select->select->addAll(Pap_Db_Table_Users::getInstance(), 'u');
$select->from->add(Pap_Db_Table_Users::getName(), 'u');
$select->from->addInnerJoin(Gpf_Db_Table_Users::getName(), 'gu', 'u.'.Pap_Db_Table_Users::ACCOUNTUSERID.'=gu.'.Gpf_Db_Table_Users::ID.
' AND u.'.Pap_Db_Table_Users::DELETED . ' = \'' . Gpf::NO . '\''.
' AND u.' .Pap_Db_Table_Users::TYPE . ' = \'' . Pap_Application::ROLETYPE_MERCHANT . '\'');
$select->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(), 'au', 'au.'.Gpf_Db_Table_AuthUsers::ID.'=gu.'.Gpf_Db_Table_Users::AUTHID);
$select->from->addInnerJoin(Gpf_Db_Table_Accounts::getName(), 'a', 'a.'.Gpf_Db_Table_Accounts::ID.'=gu.'.Gpf_Db_Table_Users::ACCOUNTID);
$select->limit->set($from, Pap_Mail_MassMailAffiliatesGrid::MAX_ROWS_PER_SQL);
return $select->getAllRows();
}
private function deleteAffiliates() {
Pap3Compatibility_Migration_OutputWriter::logOnce(" Deleting affiliates .....");
// pap users
$sql = new Gpf_SqlBuilder_DeleteBuilder();
$sql->from->add(Pap_Db_Table_Users::getName());
$sql->where->add('rtype', '=', 'A');
$sql->execute();
// g_users
$inSelect = new Gpf_SqlBuilder_SelectBuilder();
$inSelect->select->add(Pap_Db_Table_Users::ACCOUNTUSERID);
$inSelect->from->add(Pap_Db_Table_Users::getName());
$sql = new Gpf_SqlBuilder_DeleteBuilder();
$sql->from->add(Gpf_Db_Table_Users::getName());
$sql->where->add('accountuserid', 'NOT IN', $inSelect, 'AND', false);
$sql->execute();
// g_authusers
$inSelect = new Gpf_SqlBuilder_SelectBuilder();
$inSelect->select->add(Gpf_Db_Table_Users::AUTHID);
$inSelect->from->add(Gpf_Db_Table_Users::getName());
$sql = new Gpf_SqlBuilder_DeleteBuilder();
$sql->from->add(Gpf_Db_Table_AuthUsers::getName());
$sql->where->add('authid', 'NOT IN', $inSelect, 'AND', false);
$sql->execute();
// g_userattributes
$inSelect = new Gpf_SqlBuilder_SelectBuilder();
$inSelect->select->add(Gpf_Db_Table_Users::ID);
$inSelect->from->add(Gpf_Db_Table_Users::getName());
$sql = new Gpf_SqlBuilder_DeleteBuilder();
$sql->from->add(Gpf_Db_Table_UserAttributes::getName());
$sql->where->add('accountuserid', 'NOT IN', $inSelect, 'AND', false);
$sql->execute();
// g_gadgets
$inSelect = new Gpf_SqlBuilder_SelectBuilder();
$inSelect->select->add(Gpf_Db_Table_Users::ID);
$inSelect->from->add(Gpf_Db_Table_Users::getName());
$sql = new Gpf_SqlBuilder_DeleteBuilder();
$sql->from->add(Gpf_Db_Table_Gadgets::getName());
$sql->where->add('accountuserid', 'NOT IN', $inSelect, 'AND', false);
$sql->execute();
Pap3Compatibility_Migration_OutputWriter::log("DONE<br/>");
}
/**
* Pap alert application handle, do not modifi this source!
*
* @return Gpf_Data_Record
*/
public static function getAffiliatesCount($date)
{
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->add("SUM(IF(gu." . Gpf_Db_Table_Users::STATUS . "= 'A', 1, 0))", 'affiliates_approved');
$select->select->add("SUM(IF(gu." . Gpf_Db_Table_Users::STATUS . "= 'P', 1, 0))", 'affiliates_pending');
$select->select->add("SUM(IF(gu." . Gpf_Db_Table_Users::STATUS . "= 'D', 1, 0))", 'affiliates_declined');
$select->from->add(Gpf_Db_Table_Users::getName(), 'gu');
$select->from->addInnerJoin(Pap_Db_Table_Users::getName(), 'pu', 'gu.' . Gpf_Db_Table_Users::ID . '=pu.' . Pap_Db_Table_Users::ACCOUNTUSERID);
$select->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(), 'au', 'gu.' . Gpf_Db_Table_Users::AUTHID . '=au.' . Gpf_Db_Table_AuthUsers::ID);
$select->where->add('gu.' . Gpf_Db_Table_Users::ROLEID, "=", Pap_Application::DEFAULT_ROLE_AFFILIATE);
$select->where->add('pu.' . Pap_Db_Table_Users::DATEINSERTED, ">=", $date);
$row = $select->getOneRow();
return $row;
}
protected function buildFrom() {
if ($this->filters->isFilter('orderid')) {
$transSelect = new Gpf_SqlBuilder_SelectBuilder();
$transSelect->select->setDistinct();
$transSelect->select->add(Pap_Db_Table_Transactions::USER_ID);
$transSelect->from->add(Pap_Db_Table_Transactions::getName());
$this->addOrderIdFilterToSelect($transSelect, $this->filters);
$this->_selectBuilder->from->addSubselect($transSelect, 't');
$this->_selectBuilder->from->addInnerJoin(Pap_Db_Table_Users::getName(),
'u', 'u.userid=t.userid');
} else {
$this->_selectBuilder->from->add(Pap_Db_Table_Users::getName(), 'u');
}
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_Users::getName(),
'gu', 'u.accountuserid=gu.accountuserid');
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(),
'au', 'au.authid=gu.authid');
$this->_selectBuilder->from->addLeftJoin(Pap_Db_Table_Users::getName(),
'pu', 'u.parentuserid=pu.userid');
$this->_selectBuilder->from->addLeftJoin(Gpf_Db_Table_Users::getName(),
'pgu', 'pu.accountuserid=pgu.accountuserid');
$this->_selectBuilder->from->addLeftJoin(Gpf_Db_Table_AuthUsers::getName(),
'pau', 'pau.authid=pgu.authid');
$this->_selectBuilder->from->addLeftJoin(Gpf_Db_Table_FieldGroups::getName(),
'pay', 'pay.fieldgroupid=u.payoutoptionid AND pay.rtype=\'P\' AND pay.rstatus=\'' .
Gpf_Db_FieldGroup::ENABLED . '\'');
$this->buildStatsFrom();
}
function buildFrom() {
$this->_selectBuilder->from->add(Pap_Db_Table_Transactions::getName(), "t");
$onCondition = "t.".Pap_Db_Table_Transactions::CAMPAIGN_ID." = c.".Pap_Db_Table_Campaigns::ID;
$this->_selectBuilder->from->addLeftJoin(Pap_Db_Table_Campaigns::getName(), 'c', $onCondition);
$this->_selectBuilder->from->addInnerJoin(Pap_Db_Table_Users::getName(), "pu", "t.userid = pu.userid");
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_Users::getName(), "gu", "pu.accountuserid = gu.accountuserid");
$this->_selectBuilder->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(), "au", "gu.authid = au.authid");
$this->_selectBuilder->from->addLeftJoin(Pap_Db_Table_Channels::getName(), "ch", "t.channel = ch.channelid");
$this->_selectBuilder->from->addLeftJoin(Pap_Db_Table_CommissionTypes::getName(), "ct", "t.commtypeid = ct.commtypeid");
$this->_selectBuilder->from->addLeftJoin(Gpf_Db_Table_Currencies::getName(), 'cs', 't.originalcurrencyid = cs.currencyid');
$this->_selectBuilder->from->addLeftJoin(Pap_Db_Table_Banners::getName(), 'b', 'b.bannerid = t.bannerid');
$this->_selectBuilder->from->addLeftJoin(Pap_Db_Table_PayoutsHistory::getName(), 'ph',
'ph.'.Pap_Db_Table_PayoutsHistory::ID.' = t.'.Pap_Db_Table_Transactions::PAYOUTHISTORY_ID);
}
public function isExists()
{
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->add('au.' . Gpf_Db_Table_AuthUsers::ID, Gpf_Db_Table_AuthUsers::ID);
$select->from->add(Gpf_Db_Table_Users::getName(), 'u');
$select->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(), 'au', 'u.' . Gpf_Db_Table_Users::AUTHID . '=au.' . Gpf_Db_Table_AuthUsers::ID);
$select->from->addInnerJoin(Gpf_Db_Table_Roles::getName(), 'r', 'u.' . Gpf_Db_Table_Users::ROLEID . '=r.' . Gpf_Db_Table_Roles::ID);
$select->where->add('au.' . Gpf_Db_Table_AuthUsers::ID, '=', $this->authId);
$select->where->add('u.' . Gpf_Db_Table_Roles::ID, '=', $this->roleid);
$select->where->add('u.' . Gpf_Db_Table_Users::ACCOUNTID, '=', $this->accountid);
try {
$select->getOneRow();
return true;
} catch (Gpf_Exception $e) {
}
return false;
}
/**
*
* Pap alert application handle, do not modify this source!
*
* @param String $dateFrom
* @param String $dateTo
* @param String $userId
* @return Gpf_Data_RecordSet
*/
public static function getTransactions(Pap_Stats_Params $statsParams) {
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->add('tr.'.Pap_Db_Table_Transactions::USER_ID, 'userid');
$select->select->add('au.'.Gpf_Db_Table_AuthUsers::FIRSTNAME, 'name');
$select->select->add('au.'.Gpf_Db_Table_AuthUsers::LASTNAME, 'surname');
$select->select->add('au.'.Gpf_Db_Table_AuthUsers::USERNAME, 'username');
$select->select->add('pu.data1', 'weburl');
$select->select->add('tr.'.Pap_Db_Table_Transactions::TRANSACTION_ID, 'transid');
$select->select->add('tr.'.Pap_Db_Table_Transactions::TOTAL_COST, 'totalcost');
$select->select->add('tr.'.Pap_Db_Table_Transactions::FIXED_COST, 'fixedcost');
$select->select->add('tr.'.Pap_Db_Table_Transactions::ORDER_ID, 'orderid');
$select->select->add('tr.'.Pap_Db_Table_Transactions::PRODUCT_ID, 'productid');
$select->select->add('tr.'.Pap_Db_Table_Transactions::DATE_INSERTED, 'dateinserted');
$select->select->add('tr.'.Pap_Db_Table_Transactions::R_STATUS, 'rstatus');
$select->select->add('tr.'.Pap_Db_Table_Transactions::R_TYPE, 'transtype');
$select->select->add('tr.'.Pap_Db_Table_Transactions::PARRENT_TRANSACTION_ID, 'transkind');
$select->select->add('tr.'.Pap_Db_Table_Transactions::PAYOUT_STATUS, 'payoutstatus');
$select->select->add('tr.'.Pap_Db_Table_Transactions::DATE_APPROVED, 'dateapproved');
$select->select->add('tr.'.Pap_Db_Table_Transactions::COMMISSION, 'commission');
$select->select->add('tr.'.Pap_Db_Table_Transactions::REFERER_URL, 'refererurl');
$select->select->add('c.'.Pap_Db_Table_Campaigns::ID, 'campcategoryid');
$select->select->add('c.'.Pap_Db_Table_Campaigns::NAME, 'campaign');
$select->select->add('tr.data1', 'data1');
$select->select->add('tr.data2', 'data2');
$select->select->add('tr.data3', 'data3');
$select->select->add('tr.'.Pap_Db_Table_Transactions::COUNTRY_CODE, 'countrycode');
$select->from->add(Pap_Db_Table_Transactions::getName(), 'tr');
$select->from->addInnerJoin(Pap_Db_Table_Campaigns::getName(), 'c',
'tr.'.Pap_Db_Table_Transactions::CAMPAIGN_ID.'=c.'.Pap_Db_Table_Campaigns::ID);
$select->from->addInnerJoin(Pap_Db_Table_Users::getName(), 'pu',
'tr.'.Pap_Db_Table_Transactions::USER_ID.'=pu.'.Pap_Db_Table_Users::ID);
$select->from->addInnerJoin(Gpf_Db_Table_Users::getName(), 'gu',
'gu.'.Gpf_Db_Table_Users::ID.'=pu.'.Pap_Db_Table_Users::ACCOUNTUSERID);
$select->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(), 'au',
'gu.'.Gpf_Db_Table_Users::AUTHID.'=au.'.Gpf_Db_Table_AuthUsers::ID);
if ($statsParams->isDateFromDefined()) {
$select->where->add('tr.'.Pap_Db_Table_Transactions::DATE_INSERTED, '>=', $statsParams->getDateFrom()->toDateTime());
}
if ($statsParams->isDateToDefined()) {
$select->where->add('tr.'.Pap_Db_Table_Transactions::DATE_INSERTED, '<=', $statsParams->getDateTo()->toDateTime());
}
if ($statsParams->getAffiliateId() != '') {
$select->where->add('tr.'.Pap_Db_Table_Transactions::USER_ID, '=', $statsParams->getAffiliateId());
}
return $select->getAllRows();
}
private function getPayoutsForHistoryIds($payoutHistoryIds) {
$result = new Gpf_Data_RecordSet();
$selectBuilder = new Gpf_SqlBuilder_SelectBuilder();
$selectBuilder->select->add(Pap_Db_Table_Payouts::PAYOUT_HISTORY_ID, Pap_Db_Table_Payouts::PAYOUT_HISTORY_ID);
$selectBuilder->select->add(Pap_Db_Table_Payouts::AMOUNT, Pap_Db_Table_Payouts::AMOUNT);
$selectBuilder->select->add(Gpf_Db_Table_AuthUsers::USERNAME, Gpf_Db_Table_AuthUsers::USERNAME, 'au');
$selectBuilder->select->add(Gpf_Db_Table_AuthUsers::FIRSTNAME, Gpf_Db_Table_AuthUsers::FIRSTNAME, 'au');
$selectBuilder->select->add(Gpf_Db_Table_AuthUsers::LASTNAME, Gpf_Db_Table_AuthUsers::LASTNAME, 'au');
$selectBuilder->select->add(Gpf_Db_Table_AuthUsers::NOTIFICATION_EMAIL, Gpf_Db_Table_AuthUsers::NOTIFICATION_EMAIL, 'au');
$selectBuilder->select->add(Pap_Db_Table_Users::ID, Pap_Db_Table_Users::ID, 'pu');
$selectBuilder->select->add(Pap_Db_Table_Users::getDataColumnName(3), Pap_Db_Table_Users::getDataColumnName(3), 'pu');
$selectBuilder->select->add(Pap_Db_Table_Users::getDataColumnName(4), Pap_Db_Table_Users::getDataColumnName(4), 'pu');
$selectBuilder->select->add(Pap_Db_Table_Users::getDataColumnName(5), Pap_Db_Table_Users::getDataColumnName(5), 'pu');
$selectBuilder->select->add(Pap_Db_Table_Users::getDataColumnName(6), Pap_Db_Table_Users::getDataColumnName(6), 'pu');
$selectBuilder->select->add(Pap_Db_Table_Users::getDataColumnName(7), Pap_Db_Table_Users::getDataColumnName(7), 'pu');
$selectBuilder->select->add(Pap_Db_Table_Users::getDataColumnName(8), Pap_Db_Table_Users::getDataColumnName(8), 'pu');
$selectBuilder->select->add(Pap_Db_Table_Users::getDataColumnName(9), Pap_Db_Table_Users::getDataColumnName(9), 'pu');
$selectBuilder->select->add(Gpf_Db_Table_Currencies::SYMBOL, Gpf_Db_Table_Currencies::SYMBOL, 'c');
$selectBuilder->select->add(Gpf_Db_Table_Currencies::WHEREDISPLAY, Gpf_Db_Table_Currencies::WHEREDISPLAY, 'c');
$selectBuilder->from->add(Pap_Db_Table_Payouts::getName(), 'p');
$selectBuilder->from->addInnerJoin(Pap_Db_Table_Users::getName(), 'pu', 'p.userid = pu.userid');
$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->from->addLeftJoin(Gpf_Db_Table_Currencies::getName(), 'c', 'p.currencyid = c.currencyid');
$selectBuilder->where->add(Pap_Db_Table_Payouts::PAYOUT_HISTORY_ID, 'IN', $payoutHistoryIds);
$result->load($selectBuilder);
return $result;
}
