/**
* Constructor.
*/
private function __construct()
{
$this->affiliateDao = OCSAFFILIATES_BOL_AffiliateDao::getInstance();
$this->affiliateUserDao = OCSAFFILIATES_BOL_AffiliateUserDao::getInstance();
$this->bannerDao = OCSAFFILIATES_BOL_BannerDao::getInstance();
$this->clickDao = OCSAFFILIATES_BOL_ClickDao::getInstance();
$this->payoutDao = OCSAFFILIATES_BOL_PayoutDao::getInstance();
$this->resetPasswordDao = OCSAFFILIATES_BOL_ResetPasswordDao::getInstance();
$this->saleDao = OCSAFFILIATES_BOL_SaleDao::getInstance();
$this->signupDao = OCSAFFILIATES_BOL_SignupDao::getInstance();
$this->verificationDao = OCSAFFILIATES_BOL_VerificationDao::getInstance();
$this->visitDao = OCSAFFILIATES_BOL_VisitDao::getInstance();
}
/**
* @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));
}