Example #1
0
 /**
  * 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();
 }
Example #2
0
 /**
  * @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));
 }