/**
* @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;
}
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();
}
private function buildSql() {
$this->sqlBuilder->from->add(Pap_Db_Table_Transactions::getName(), self::TRANSACTION_ALIAS);
$this->buildWhere();
$this->setUserFilter();
$this->setStatusWhere($this->sqlBuilder->where);
$this->rule->setTransactionsWhere($this->sqlBuilder->where, self::TRANSACTION_ALIAS);
}
public function run() {
$time1 = microtime();
Pap3Compatibility_Migration_OutputWriter::logOnce("Deleting migrated data from existing PAP4 tables<br/>");
try {
$this->deleteTable(Pap_Db_Table_Campaigns::getName());
$this->deleteTable(Pap_Db_Table_CommissionGroups::getName());
$this->deleteTable(Pap_Db_Table_CommissionTypes::getName());
$this->deleteTable(Pap_Db_Table_Commissions::getName());
$this->deleteTable(Pap_Db_Table_UserInCommissionGroup::getName());
$this->deleteTable(Gpf_Db_Table_FormFields::getName());
$this->deleteTable(Gpf_Db_Table_FieldGroups::getName());
$this->deleteTable(Pap_Db_Table_Transactions::getName());
$this->deleteTable(Pap_Db_Table_Clicks::getName());
$this->deleteTable(Pap_Db_Table_RawClicks::getName());
$this->deleteTable(Pap_Db_Table_Impressions::getName());
$this->deleteTable(Pap_Db_Table_Banners::getName());
$this->deleteTable(Gpf_Db_Table_FieldGroups::getName());
$this->deleteTable(Pap_Db_Table_PayoutsHistory::getName());
$this->deleteTable(Pap_Db_Table_Payouts::getName());
$this->deleteTable(Gpf_Db_Table_Currencies::getName());
$this->deleteTable(Gpf_Db_Table_MailAccounts::getName());
} catch(Exception $e) {
Pap3Compatibility_Migration_OutputWriter::log(" Errror: ".$e->getMessage()."<br/>");
}
$time2 = microtime();
Pap3Compatibility_Migration_OutputWriter::logDone($time1, $time2);
}
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');
}
/**
*
* @return Gpf_SqlBuilder_SelectBuilder
*/
protected function getTransactionsSelect() {
$transactions = new Gpf_SqlBuilder_SelectBuilder();
$transactions->select->addAll(Pap_Db_Table_Transactions::getInstance(), 't');
$transactions->select->add('c.'.Pap_Db_Table_Commissions::TYPE);
$transactions->select->add('c.'.Pap_Db_Table_Commissions::VALUE);
$transactions->where->add('t.'.Pap_Db_Table_Transactions::USER_ID, '=', $this->getCurrentUserId());
$this->initTransactionSql($transactions);
return $transactions;
}
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);
}
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');
}
/**
* @return Gpf_Data_Record
*/
public function getPendingTransactionsInfo() {
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->add("SUM(IF(".Pap_Db_Table_Transactions::R_STATUS." = '".self::PENDING."',1,0))", "pendingCommissions");
$select->select->add("SUM(IF(".Pap_Db_Table_Transactions::R_STATUS." = '".self::PENDING."',".
Pap_Db_Table_Transactions::COMMISSION.",0))", "totalCommissions");
$select->from->add(Pap_Db_Table_Transactions::getName());
Gpf_Plugins_Engine::extensionPoint('AffiliateNetwork.modifyWhere', new Gpf_Common_SelectBuilderCompoundRecord($select, new Gpf_Data_Record(array())));
$result = $select->getOneRow();
return $result;
}
public function execute() {
$q = new Gpf_SqlBuilder_SelectBuilder();
$q->select->add('fixedcost');
$q->from->add(Pap_Db_Table_Transactions::getName());
$q->limit->set(0,1);
try {
$q->getAllRows();
return;
} catch(Exception $e) {
}
$db = Gpf_DbEngine_Database::getDatabase();
$db->execute("ALTER TABLE qu_pap_transactions ADD fixedcost FLOAT NOT NULL DEFAULT '0'");
}
/**
*
* @return Pap_Common_Transaction
*/
protected function getParentTransaction($futurePayId) {
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->addAll(Pap_Db_Table_Transactions::getInstance());
$select->from->add(Pap_Db_Table_Transactions::getName());
$select->where->add(Pap_Db_Table_Transactions::DATA5, "=", $futurePayId);
$select->where->add(Pap_Db_Table_Transactions::R_TYPE, "IN", array(Pap_Common_Constants::TYPE_SALE, Pap_Common_Constants::TYPE_ACTION, Pap_Common_Constants::TYPE_LEAD));
$select->where->add(Pap_Db_Table_Transactions::TIER, "=", "1");
$select->limit->set(0, 1);
$t = new Pap_Common_Transaction();
$t->fillFromRecord($select->getOneRow());
return $t;
}
protected function isExtraBonusExists() {
$selectBuilder = new Gpf_SqlBuilder_SelectBuilder();
$selectBuilder->select->add('count(*)', 'cnt');
$selectBuilder->from->add(Pap_Db_Table_Transactions::getName());
$selectBuilder->where->add(Pap_Db_Table_Transactions::R_TYPE, '=', Pap_Db_Transaction::TYPE_EXTRA_BONUS);
$selectBuilder->where->add(Pap_Db_Table_Transactions::USER_ID, '=', $this->getCurrentUserId());
$selectBuilder->where->add(Pap_Db_Table_Transactions::CAMPAIGN_ID, '=', $this->transaction->getCampaignId());
$selectBuilder->where->add(Pap_Db_Table_Transactions::DATA1, '=', $this->rule->getId());
$selectBuilder->where->add(Pap_Db_Table_Transactions::DATA2, '=', $this->getToDateRange());
$row = $selectBuilder->getOneRow();
if ($row->get('cnt') > 0) {
return true;
}
return false;
}
/**
* @param Pap_Contexts_Tracking $context
* @return boolean
*/
private function isInCookieLifetimeLimit(Pap_Contexts_Tracking $context) {
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->add(Pap_Db_Table_Transactions::DATE_INSERTED);
$select->from->add(Pap_Db_Table_Transactions::getName());
$select->where->add(Pap_Db_Table_Transactions::DATA1, '=', $this->getIdentifier($context));
$select->where->add(Pap_Db_Table_Transactions::R_TYPE, '=', Pap_Db_Transaction::TYPE_SALE);
$select->orderBy->add(Pap_Db_Table_Transactions::DATE_INSERTED);
$select->limit->set(0, 1);
try {
$firstSale = $select->getOneRow()->get(Pap_Db_Table_Transactions::DATE_INSERTED);
$cookieLifeTime = Pap_Tracking_Cookie::getCookieLifeTimeInDays($context);
if (Gpf_Common_DateUtils::getDifference($firstSale, Gpf_Common_DateUtils::getDateTime(time()), 'day') > $cookieLifeTime) {
$context->debug(' Transaction is not in cookie limit. Date of first transaction: ' . $firstSale . ' is not in cookie limit: ' . $cookieLifeTime . ' days.');
return false;
}
} catch (Gpf_DbEngine_NoRowException $e) {
}
return true;
}
protected function getTransactionsList() {
$types = array(Pap_Common_Constants::TYPE_SALE, Pap_Common_Constants::TYPE_ACTION, Pap_Common_Constants::TYPE_LEAD, Pap_Common_Constants::TYPE_RECURRING);
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->addAll(Pap_Db_Table_Transactions::getInstance(), 't');
$select->from->add(Pap_Db_Table_Transactions::getName(), 't');
$select->from->addInnerJoin(Pap_Db_Table_CommissionTypeAttributes::getName(), 'cta',
'cta.'.Pap_Db_Table_CommissionTypeAttributes::COMMISSION_TYPE_ID.'=t.'.Pap_Db_Table_Transactions::COMMISSIONTYPEID. '
and (cta.'.Pap_Db_Table_CommissionTypeAttributes::NAME."='".AutoApprovalCommissions_Main::AUTO_APPROVAL_COMMISSIONS_DAYS."'
and ".Pap_Db_Table_CommissionTypeAttributes::VALUE . '<>0)
and (unix_timestamp(t.dateinserted) + (cta.'.Pap_Db_Table_CommissionTypeAttributes::VALUE.' * 86400)) <= unix_timestamp()
');
$select->where->add(Pap_Db_Table_Transactions::R_TYPE, "IN", $types);
$select->where->add(Pap_Db_Table_Transactions::R_STATUS, "=", Pap_Common_Constants::STATUS_PENDING);
$transaction = new Pap_Common_Transaction();
return $transaction->loadCollectionFromRecordset($select->getAllRows());
}
/**
* @throws Gpf_DbEngine_NoRowException
* @return Pap_Common_Transcation
*/
protected function getClonedTransactionFromDb(Pap_Common_Transaction $transaction) {
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->addAll(Pap_Db_Table_Transactions::getInstance());
$select->from->add(Pap_Db_Table_Transactions::getName());
$select->where->add(Pap_Db_Table_Transactions::USER_ID, '=', $transaction->getUserId());
$select->where->add(Pap_Db_Table_Transactions::CAMPAIGN_ID, '=', $transaction->getCampaignId());
$select->where->add(Pap_Db_Table_Transactions::BANNER_ID, '=', $transaction->getBannerId());
$select->where->add(Pap_Db_Table_Transactions::CHANNEL, '=', $transaction->getChannel());
$select->where->add(Pap_Db_Table_Transactions::R_STATUS, '=', $transaction->getStatus());
$select->where->add(Pap_Db_Table_Transactions::TIER, '=', $transaction->getTier());
$select->where->add(Pap_Db_Table_Transactions::R_TYPE, '=', $transaction->getType());
$select->where->add(Pap_Db_Table_Transactions::COUNTRY_CODE, '=', $transaction->getCountryCode());
$select->where->add(Pap_Db_Table_Transactions::PAYOUT_STATUS, '=', $transaction->getPayoutStatus());
$select->where->add(Pap_Db_Table_Transactions::DATE_INSERTED, 'like', $this->dateTimeToDate($transaction->getDateInserted()).'%');
$select->orderBy->add(Pap_Db_Table_Transactions::DATE_INSERTED, false);
$select->limit->set(0, 1);
$transaction = new Pap_Common_Transaction();
$transaction->fillFromRecord($select->getOneRow());
return $transaction;
}
/**
* @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);
}
private function getTransactions(Pap_Stats_Params $statsParams) {
return Pap_Db_Table_Transactions::getTransactions($statsParams);
}
protected function initFrom() {
parent::initFrom();
$this->selectBuilder->from->addLeftJoin(Pap_Db_Table_Transactions::getName(), 'p',
'p.'.Pap_Db_Table_Transactions::TRANSACTION_ID.'=t.'.Pap_Db_Table_Transactions::PARRENT_TRANSACTION_ID);
}
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();
}
/**
* @return Pap_Db_Transaction
*/
public function getRefundOrChargebackTransaction() {
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->addAll(Pap_Db_Table_Transactions::getInstance());
$select->from->add(Pap_Db_Table_Transactions::getName());
$select->where->add(Pap_Db_Table_Transactions::PARRENT_TRANSACTION_ID, '=', $this->getId());
$select->where->add(Pap_Db_Table_Transactions::R_TYPE, 'IN', array(Pap_Db_Transaction::TYPE_REFUND, Pap_Db_Transaction::TYPE_CHARGE_BACK));
try {
$record = $select->getOneRow();
$transaction = new Pap_Db_Transaction();
$transaction->fillFromRecord($record);
return $transaction;
} catch (Gpf_Exception $e) {
return null;
}
}
public function readRequestVariables() {
$input = $this->readXmlData();
if (Gpf_Settings::get(Recurly_Config::RESEND_URL) != "") {
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, Gpf_Settings::get(Recurly_Config::RESEND_URL));
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_TIMEOUT, 60);
//curl_setopt($ch, CURLOPT_USERAGENT, $defined_vars['HTTP_USER_AGENT']);
curl_setopt($ch, CURLOPT_HTTPHEADER, Array("Content-Type: text/xml"));
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $input);
curl_exec($ch);
/*
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, Gpf_Settings::get(Recurly_Config::RESEND_URL));
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_TIMEOUT, 60);
curl_setopt($ch, CURLOPT_HTTPHEADER, Array("Content-Type: text/xml"));
curl_setopt($ch, CURLOPT_POSTFIELDS, $_POST);
curl_exec($ch);
*/
}
$this->debug("Input get: " . $input);
try {
$xml = new SimpleXMLElement($input);
} catch (Exception $e) {
$this->setPaymentStatus("Failed");
$this->debug('Wrong XML format!');
return false;
}
// read last tag to find out what kind of request this is, e.g. </new_subscription_notification>
$status = strrpos($input,"</");
$status = substr($input,$status+2,strlen($input)-1);
$status = substr($status,0,strrpos($status,">"));
$this->setType($status);
if ($this->getType() == self::NEWPAYMENT) {
$totalcost_name = "amount_in_cents";
$this->setData1((string)$xml->{"transaction"}->{"invoice_number"});
}
else {
$totalcost_name = "total_amount_in_cents";
$this->setProductID((string)$xml->{"transaction"}->{"plan_code"});
}
$this->setTransactionID((string)$xml->{"account"}->{"account_code"});
$this->setTotalCost((string)$xml->{"transaction"}->{$totalcost_name}/100)*(((string)$xml->{"transaction"}->{"quantity"})?(string)$xml->{"transaction"}->{"quantity"}:1);
// get original Affiliate
$status = array(Pap_Common_Constants::STATUS_APPROVED, Pap_Common_Constants::STATUS_PENDING);
$types = array(Pap_Common_Constants::TYPE_SALE, Pap_Common_Constants::TYPE_ACTION, Pap_Common_Constants::TYPE_LEAD);
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->addAll(Pap_Db_Table_Transactions::getInstance());
$select->from->add(Pap_Db_Table_Transactions::getName());
$select->where->add(Pap_Db_Table_Transactions::ORDER_ID, "=", $this->getOrderID());
$select->where->add(Pap_Db_Table_Transactions::R_TYPE, "IN", $types);
$select->where->add(Pap_Db_Table_Transactions::R_STATUS, "IN", $status);
$transaction = new Pap_Common_Transaction();
$transaction->fillFromSelect($select);
if (($transaction->getUserId() == null) OR ($transaction->getUserId() == "")) {
$this->debug('No affiliate found for order ID: '.$this->getOrderID());
}
else {
$this->setAccountId($transaction->getAccountId());
$this->setAffiliateID($transaction->getUserId());
$this->setProductID($transaction->getProductId());
$this->setCampaignId($transaction->getCampaignId());
}
}
/**
* @throws Gpf_DbEngine_TooManyRowsException
* @throws Gpf_DbEngine_NoRowException
* @param string $transId
* @return Gpf_Data_Record
*/
private function getTransactionData($transId) {
$selectBuilder = new Gpf_SqlBuilder_SelectBuilder();
$selectBuilder->select->add(Pap_Db_Table_Transactions::TRANSACTION_ID, Pap_Db_Table_Transactions::TRANSACTION_ID, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::USER_ID, Pap_Db_Table_Transactions::USER_ID, 't');
$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_Currencies::SYMBOL, Gpf_Db_Table_Currencies::SYMBOL, "c");
$selectBuilder->select->add(Gpf_Db_Table_Currencies::WHEREDISPLAY, Gpf_Db_Table_Currencies::WHEREDISPLAY, "c");
$selectBuilder->select->add(Pap_Db_Table_Campaigns::NAME, "campaignname", "ca");
$selectBuilder->select->add(Pap_Db_Table_Transactions::R_STATUS, Pap_Db_Table_Transactions::R_STATUS, "t");
$selectBuilder->select->add(Pap_Db_Table_Transactions::R_TYPE, Pap_Db_Table_Transactions::R_TYPE, "t");
$selectBuilder->select->add(Pap_Db_Table_Transactions::DATE_INSERTED, Pap_Db_Table_Transactions::DATE_INSERTED, "t");
$selectBuilder->select->add(Pap_Db_Table_Transactions::DATE_APPROVED, Pap_Db_Table_Transactions::DATE_APPROVED, "t");
$selectBuilder->select->add(Pap_Db_Table_Transactions::PAYOUT_STATUS, Pap_Db_Table_Transactions::PAYOUT_STATUS, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::REFERER_URL, Pap_Db_Table_Transactions::REFERER_URL, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::IP, Pap_Db_Table_Transactions::IP, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::COMMISSION, Pap_Db_Table_Transactions::COMMISSION, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::TIER, Pap_Db_Table_Transactions::TIER, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::ORDER_ID, Pap_Db_Table_Transactions::ORDER_ID, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::PRODUCT_ID, Pap_Db_Table_Transactions::PRODUCT_ID, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::TOTAL_COST, Pap_Db_Table_Transactions::TOTAL_COST, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::TRACK_METHOD, Pap_Db_Table_Transactions::TRACK_METHOD, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::FIRST_CLICK_TIME, Pap_Db_Table_Transactions::FIRST_CLICK_TIME, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::FIRST_CLICK_REFERER, Pap_Db_Table_Transactions::FIRST_CLICK_REFERER, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::FIRST_CLICK_IP, Pap_Db_Table_Transactions::FIRST_CLICK_IP, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::FIRST_CLICK_DATA1, Pap_Db_Table_Transactions::FIRST_CLICK_DATA1, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::FIRST_CLICK_DATA2, Pap_Db_Table_Transactions::FIRST_CLICK_DATA2, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::LAST_CLICK_TIME, Pap_Db_Table_Transactions::LAST_CLICK_TIME, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::LAST_CLICK_REFERER, Pap_Db_Table_Transactions::LAST_CLICK_REFERER, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::LAST_CLICK_IP, Pap_Db_Table_Transactions::LAST_CLICK_IP, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::LAST_CLICK_DATA1, Pap_Db_Table_Transactions::LAST_CLICK_DATA1, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::LAST_CLICK_DATA2, Pap_Db_Table_Transactions::LAST_CLICK_DATA2, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::DATA1, Pap_Db_Table_Transactions::DATA1, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::DATA2, Pap_Db_Table_Transactions::DATA2, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::DATA3, Pap_Db_Table_Transactions::DATA3, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::DATA4, Pap_Db_Table_Transactions::DATA4, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::DATA5, Pap_Db_Table_Transactions::DATA5, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::ORIGINAL_CURRENCY_VALUE, Pap_Db_Table_Transactions::ORIGINAL_CURRENCY_VALUE, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::MERCHANTNOTE, Pap_Db_Table_Transactions::MERCHANTNOTE, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::SYSTEMNOTE, Pap_Db_Table_Transactions::SYSTEMNOTE, 't');
$selectBuilder->select->add(Pap_Db_Table_Transactions::VISITOR_ID, Pap_Db_Table_Transactions::VISITOR_ID, 't');
$selectBuilder->from->add(Pap_Db_Table_Transactions::getName(), "t");
$selectBuilder->from->addLeftJoin(Pap_Db_Table_Users::getName(), "pu",
"t.".Pap_Db_Table_Transactions::USER_ID." = pu.".Pap_Db_Table_Users::ID);
$selectBuilder->from->addInnerJoin(Gpf_Db_Table_Users::getName(), "gu",
"pu.".Pap_Db_Table_Users::ACCOUNTUSERID." = gu.".Gpf_Db_Table_Users::ID);
$selectBuilder->from->addInnerJoin(Gpf_Db_Table_AuthUsers::getName(), "au",
"gu.".Gpf_Db_Table_Users::AUTHID." = au.".Gpf_Db_Table_AuthUsers::ID);
$selectBuilder->from->addLeftJoin(Gpf_Db_Table_Currencies::getName(), "c",
"t.".Pap_Db_Table_Transactions::ORIGINAL_CURRENCY_ID." = c.".Gpf_Db_Table_Currencies::ID);
$selectBuilder->from->addLeftJoin(Pap_Db_Table_Campaigns::getName(), "ca",
"t.".Pap_Db_Table_Transactions::CAMPAIGN_ID." = ca.".Pap_Db_Table_Campaigns::ID);
$selectBuilder->where->add(Pap_Db_Table_Transactions::TRANSACTION_ID, '=', $transId);
$row = $selectBuilder->getOneRow();
return $row;
}
/**
* @return Gpf_DbEngine_Row_Collection
*/
protected function getTransactionsCollection() {
$selectBuilder = new Gpf_SqlBuilder_SelectBuilder();
$selectBuilder->select->addAll(Pap_Db_Table_Transactions::getInstance());
$selectBuilder->from->add(Pap_Db_Table_Transactions::getName());
$selectBuilder->where->add(Pap_Db_Table_Transactions::SALE_ID,'=',$this->saleId);
$recordSet = $selectBuilder->getAllRows();
$transaction = new Pap_Common_Transaction();
return $transaction->loadCollectionFromRecordset($recordSet);
}
public function __construct(Pap_Stats_Params $params) {
parent::__construct(Pap_Db_Table_Transactions::getInstance(), $params);
}
protected function buildFrom() {
$this->transactionsSelect->from->add(Pap_Db_Table_Transactions::getName());
$this->statsSelect->from->addSubselect($this->transactionsSelect, self::USER_TRANS_PREFIX);
$this->statsSelect->from->addInnerJoin(Pap_Db_Table_Transactions::getName(),
self::SUBUSER_TRANS_PREFIX, self::SUBUSER_TRANS_PREFIX.'.transid='.self::USER_TRANS_PREFIX.'.parenttransid');
}
protected function loadResultData() {
$this->doMossoHack(Pap_Db_Table_Transactions::getInstance(), 't', Pap_Db_Table_Transactions::TRANSACTION_ID);
return parent::loadResultData();
}
/**
*
* @param $orderId
* @param $includeRefund
* @return Gpf_DbEngine_Row_Collection<Pap_Common_Transaction>
*/
private function getAllTransactionIdsWithOrderId($orderId, $includeRefund){
$status = array (Pap_Common_Constants::STATUS_APPROVED, Pap_Common_Constants::STATUS_PENDING);
$types = array(Pap_Common_Constants::TYPE_SALE, Pap_Common_Constants::TYPE_ACTION, Pap_Common_Constants::TYPE_LEAD);
if ($includeRefund == true) {
$types[] = Pap_Common_Constants::TYPE_REFUND;
}
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->addAll(Pap_Db_Table_Transactions::getInstance());
$select->from->add(Pap_Db_Table_Transactions::getName());
$select->where->add(Pap_Db_Table_Transactions::ORDER_ID, "=", $orderId);
$select->where->add(Pap_Db_Table_Transactions::R_TYPE, "IN", $types);
$select->where->add(Pap_Db_Table_Transactions::R_STATUS, "IN", $status);
$transaction = new Pap_Common_Transaction();
return $transaction->loadCollectionFromRecordset($select->getAllRows());
}
private function getNumberOfSalesActions($userId) {
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->add('count(transid)', 'countColumn');
$select->from->add(Pap_Db_Table_Transactions::getName());
$select->where->add(Pap_Db_Table_Transactions::USER_ID, '=', $userId);
$select->where->add(Pap_Db_Table_Transactions::TIER, '=', '1');
$select->where->add(Pap_Db_Table_Transactions::R_STATUS, '=', Pap_Common_Constants::STATUS_APPROVED);
$compoundWhere = new Gpf_SqlBuilder_CompoundWhereCondition();
$compoundWhere->add(Pap_Db_Table_Transactions::R_TYPE, '=', Pap_Common_Constants::TYPE_SALE, 'OR');
$compoundWhere->add(Pap_Db_Table_Transactions::R_TYPE, '=', Pap_Common_Constants::TYPE_ACTION, 'OR');
$select->where->addCondition($compoundWhere);
$recordSet = new Gpf_Data_RecordSet();
$recordSet->load($select);
foreach($recordSet as $record) {
return $record->get('countColumn');
}
return 0;
}
/**
*
* 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();
}
protected function buildFrom() {
$this->_selectBuilder->from->add(Pap_Db_Table_Transactions::getName(), 'r');
$this->buildStatsFrom();
}
