/**
* @param $affiliateId
* @return int
*/
public function countAffiliateEventsLog($affiliateId)
{
$clickEvents = $this->clickDao->countByAffiliateId($affiliateId);
$signupEvents = $this->signupDao->countByAffiliateId($affiliateId);
$saleEvents = $this->saleDao->countByAffiliateId($affiliateId);
return $clickEvents + $signupEvents + $saleEvents;
}
/**
* Returns an instance of class.
*
* @return OCSAFFILIATES_BOL_SaleDao
*/
public static function getInstance()
{
if (self::$classInstance === null) {
self::$classInstance = new self();
}
return self::$classInstance;
}
/**
* Returns all events log for affiliate
*
* @param $affiliateId
* @param $offset
* @param $limit
* @return array
*/
public function getEventsLog($affiliateId, $offset, $limit)
{
$signupDao = OCSAFFILIATES_BOL_SignupDao::getInstance();
$saleDao = OCSAFFILIATES_BOL_SaleDao::getInstance();
$sql = "SELECT result.* FROM\n (\n SELECT `id`, `bonusAmount`, `clickDate` AS `timestamp`, 'click' AS `type` FROM `" . $this->getTableName() . "`\n WHERE `affiliateId` = :id\n\n UNION\n\n SELECT `id`, `bonusAmount`, `signupDate` AS `timestamp`, 'signup' AS `type` FROM `" . $signupDao->getTableName() . "`\n WHERE `affiliateId` = :id\n\n UNION\n\n SELECT `id`, `bonusAmount`, `saleDate` AS `timestamp`, 'sale' AS `type` FROM `" . $saleDao->getTableName() . "`\n WHERE `affiliateId` = :id\n ) AS `result`\n ORDER BY `result`.`timestamp` DESC LIMIT :offset, :limit";
return $this->dbo->queryForList($sql, array('id' => $affiliateId, 'offset' => $offset, 'limit' => $limit));
}
/**
* @param $offset
* @param $limit
* @param $sortBy
* @param $sortOrder
* @return array
*/
public function getList($offset, $limit, $sortBy, $sortOrder)
{
$clickDao = OCSAFFILIATES_BOL_ClickDao::getInstance();
$signupDao = OCSAFFILIATES_BOL_SignupDao::getInstance();
$saleDao = OCSAFFILIATES_BOL_SaleDao::getInstance();
$payoutDao = OCSAFFILIATES_BOL_PayoutDao::getInstance();
if (in_array($sortBy, array('name', 'registerStamp', 'status'))) {
$sortBy = '`a`.`' . $sortBy . '`';
} else {
$sortBy = '`' . $sortBy . '`';
}
$sql = "SELECT `a`.*, `clickCount`, `clickAmount`, `signupCount`, `signupAmount`, `saleCount`, `saleAmount`,\n \t `clickAmount` + `signupAmount` + `saleAmount` AS `earnings`, `payouts`,\n \t `clickAmount` + `signupAmount` + `saleAmount` - `payouts` AS `balance`\n \t FROM\n \t (\n \t SELECT `a`.*,\n \t ( SELECT COUNT(`cc`.`id`) FROM `" . $clickDao->getTableName() . "` AS `cc` WHERE `cc`.`affiliateId` = `a`.`id` GROUP BY `cc`.`affiliateId` ) AS `clickCount`,\n \t ( SELECT COALESCE(SUM(`ca`.`bonusAmount`), 0) FROM `" . $clickDao->getTableName() . "` AS `ca` WHERE `ca`.`affiliateId` = `a`.`id` GROUP BY `ca`.`affiliateId` ) AS `clickAmount`,\n \t ( SELECT COUNT(`suc`.`id`) FROM `" . $signupDao->getTableName() . "` AS `suc` WHERE `suc`.`affiliateId` = `a`.`id` GROUP BY `suc`.`affiliateId` ) AS `signupCount`,\n \t ( SELECT COALESCE(SUM(`sua`.`bonusAmount`), 0) FROM `" . $signupDao->getTableName() . "` AS `sua` WHERE `sua`.`affiliateId` = `a`.`id` GROUP BY `sua`.`affiliateId` ) AS `signupAmount`,\n ( SELECT COUNT(`sc`.`id`) FROM `" . $saleDao->getTableName() . "` AS `sc` WHERE `sc`.`affiliateId` = `a`.`id` GROUP BY `sc`.`affiliateId` ) AS `saleCount`,\n \t ( SELECT COALESCE(SUM(`sa`.`bonusAmount`), 0) FROM `" . $saleDao->getTableName() . "` AS `sa` WHERE `sa`.`affiliateId` = `a`.`id` GROUP BY `sa`.`affiliateId` ) AS `saleAmount`,\n \t ( SELECT COALESCE(SUM(`p`.`amount`), 0) FROM `" . $payoutDao->getTableName() . "` AS `p` WHERE `p`.`affiliateId` = `a`.`id` GROUP BY `p`.`affiliateId`) AS `payouts`\n \t FROM `" . $this->getTableName() . "` AS `a`\n \t ) AS `a`\n \t ORDER BY " . $sortBy . " " . $sortOrder . "\n \t LIMIT :offset, :limit";
return $this->dbo->queryForList($sql, array('offset' => $offset, 'limit' => $limit));
}