File indexing completed on 2024-05-12 05:58:47
0001 <?php 0002 0003 /** 0004 * ocs-webserver 0005 * 0006 * Copyright 2016 by pling GmbH. 0007 * 0008 * This file is part of ocs-webserver. 0009 * 0010 * This program is free software: you can redistribute it and/or modify 0011 * it under the terms of the GNU Affero General Public License as 0012 * published by the Free Software Foundation, either version 3 of the 0013 * License, or (at your option) any later version. 0014 * 0015 * This program is distributed in the hope that it will be useful, 0016 * but WITHOUT ANY WARRANTY; without even the implied warranty of 0017 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 0018 * GNU Affero General Public License for more details. 0019 * 0020 * You should have received a copy of the GNU Affero General Public License 0021 * along with this program. If not, see <http://www.gnu.org/licenses/>. 0022 * 0023 * Created: 16.12.2016 0024 **/ 0025 class Default_Model_StatDownload 0026 { 0027 0028 public function getUserDownloads($member_id) 0029 { 0030 $sql = " 0031 SELECT 0032 `member_dl_plings`.*, 0033 CASE WHEN (SELECT count(1) AS `sum_plings` FROM `project_plings` `pp` WHERE `pp`.`project_id` = `member_dl_plings`.`project_id` AND `pp`.`is_deleted` = 0 AND `is_active` = 1 GROUP BY `pp`.`project_id`) > 0 THEN (SELECT count(1) AS `sum_plings` FROM `project_plings` `pp` WHERE `pp`.`project_id` = `member_dl_plings`.`project_id` AND `pp`.`is_deleted` = 0 AND `is_active` = 1 GROUP BY `pp`.`project_id`) + 1 ELSE 1 END AS `num_plings_now`, 0034 `project`.`title`, 0035 `project`.`image_small`, 0036 `project_category`.`title` AS `cat_title`, 0037 laplace_score(`project`.`count_likes`, `project`.`count_dislikes`)/100 AS `laplace_score`, 0038 `member_payout`.`amount`, 0039 `member_payout`.`status`, 0040 `member_payout`.`payment_transaction_id`, 0041 CASE WHEN `tag_object`.`tag_item_id` IS NULL THEN 1 ELSE 0 END AS `is_license_missing_now`, 0042 CASE WHEN ((`project_category`.`source_required` = 1 AND ((`project`.`source_url` IS NOT NULL AND LENGTH(`project`.`source_url`) > 0) OR `project`.gitlab_project_id IS NOT NULL)) OR (`project_category`.`source_required` = 0)) THEN 0 ELSE 1 END AS `is_source_missing_now`, 0043 `project`.`pling_excluded` AS `is_pling_excluded_now` 0044 FROM 0045 `member_dl_plings` 0046 STRAIGHT_JOIN 0047 `project` ON `project`.`project_id` = `member_dl_plings`.`project_id` 0048 STRAIGHT_JOIN 0049 `project_category` ON `project_category`.`project_category_id` = `member_dl_plings`.`project_category_id` 0050 LEFT JOIN 0051 `member_payout` ON `member_payout`.`member_id` = `member_dl_plings`.`member_id` 0052 AND `member_payout`.`yearmonth` = `member_dl_plings`.`yearmonth` 0053 LEFT JOIN `tag_object` ON `tag_object`.`tag_type_id` = 1 AND `tag_object`.`tag_group_id` = 7 AND `tag_object`.`is_deleted` = 0 AND `tag_object`.`tag_object_id` = `project`.`project_id` 0054 WHERE 0055 `member_dl_plings`.`member_id` = :member_id 0056 0057 ORDER BY `member_dl_plings`.`yearmonth` DESC, `project_category`.`title`, `project`.`title` 0058 "; 0059 $result = Zend_Db_Table::getDefaultAdapter()->query($sql, array('member_id' => $member_id)); 0060 0061 if ($result->rowCount() > 0) { 0062 return $result->fetchAll(); 0063 } else { 0064 return array(); 0065 0066 } 0067 } 0068 0069 0070 public function getUserDownloadsAndViewsForMonth($member_id, $yearmonth) 0071 { 0072 return $this->getUserDownloadsAndViewsForMonthAndSection($member_id, $yearmonth, null); 0073 } 0074 0075 0076 public function getUserDownloadsAndViewsForMonthAndSection($member_id, $yearmonth, $section_id = null) 0077 { 0078 $sql = " 0079 SELECT 0080 `micro_payout`.`yearmonth`,`micro_payout`.`project_id`,`micro_payout`.`project_category_id`,`micro_payout`.`category_pling_factor`,`project_category`.`title`, `project`.`title`,`micro_payout`.`paypal_mail`, 0081 `micro_payout`.is_license_missing, 0082 `micro_payout`.is_source_missing, 0083 `micro_payout`.is_pling_excluded, 0084 CASE WHEN (SELECT count(1) AS `sum_plings` FROM `project_plings` `pp` WHERE `pp`.`project_id` = `micro_payout`.`project_id` AND `pp`.`is_deleted` = 0 AND `is_active` = 1 GROUP BY `pp`.`project_id`) > 0 THEN (SELECT count(1) AS `sum_plings` FROM `project_plings` `pp` WHERE `pp`.`project_id` = `micro_payout`.`project_id` AND `pp`.`is_deleted` = 0 AND `is_active` = 1 GROUP BY `pp`.`project_id`) + 1 ELSE 1 END AS `num_plings_now`, 0085 `project`.`title`, 0086 `project`.`image_small`, 0087 `project_category`.`title` AS `cat_title`, 0088 laplace_score(`project`.`count_likes`, `project`.`count_dislikes`)/100 AS `laplace_score`, 0089 `member_payout`.`amount`, 0090 `member_payout`.`status`, 0091 `member_payout`.`payment_transaction_id`, 0092 CASE WHEN `tag_object`.`tag_item_id` IS NULL THEN 1 ELSE 0 END AS `is_license_missing_now`, 0093 CASE WHEN ((`project_category`.`source_required` = 1 AND ((`project`.`source_url` IS NOT NULL AND LENGTH(`project`.`source_url`) > 0) OR `project`.gitlab_project_id IS NOT NULL)) OR (`project_category`.`source_required` = 0)) THEN 0 ELSE 1 END AS `is_source_missing_now`, 0094 `project`.`pling_excluded` AS `is_pling_excluded_now`, 0095 (SELECT SUM(u.num_plings) FROM micro_payout u 0096 WHERE u.member_id = `micro_payout`.`member_id` 0097 and u.project_id = `micro_payout`.`project_id` 0098 AND u.yearmonth = `micro_payout`.yearmonth 0099 AND u.`type` = 0 0100 GROUP BY u.yearmonth, u.project_id, u.member_id) AS num_downloads_micropayout, 0101 (SELECT SUM(u.credits_plings)/100 FROM micro_payout u 0102 WHERE u.member_id = `micro_payout`.`member_id` 0103 AND u.project_id = `micro_payout`.`project_id` 0104 AND u.yearmonth = `micro_payout`.yearmonth 0105 AND u.`type` = 0 0106 GROUP BY u.yearmonth, u.project_id, u.member_id) AS amount_downloads_micropayout, 0107 (SELECT SUM(u.num_plings) FROM micro_payout u 0108 WHERE u.member_id = `micro_payout`.`member_id` 0109 and u.project_id = `micro_payout`.`project_id` 0110 AND u.yearmonth = `micro_payout`.yearmonth 0111 AND u.`type` = 1 0112 GROUP BY u.yearmonth, u.project_id, u.member_id) AS num_views_micropayout, 0113 (SELECT SUM(u.credits_plings)/100 FROM micro_payout u 0114 WHERE u.member_id = `micro_payout`.`member_id` 0115 AND u.project_id = `micro_payout`.`project_id` 0116 AND u.yearmonth = `micro_payout`.yearmonth 0117 AND u.`type` = 1 0118 GROUP BY u.yearmonth, u.project_id, u.member_id) AS amount_views_micropayout, 0119 0120 (SELECT SUM(u.credits_plings)/100 FROM micro_payout u 0121 WHERE u.member_id = `micro_payout`.`member_id` 0122 AND u.project_id = `micro_payout`.`project_id` 0123 AND u.yearmonth = `micro_payout`.yearmonth 0124 GROUP BY u.yearmonth, u.project_id, u.member_id) AS amount_plings_micropayout, 0125 0126 (SELECT SUM(u.credits_plings) FROM micro_payout u 0127 WHERE u.member_id = `micro_payout`.`member_id` 0128 AND u.project_id = `micro_payout`.`project_id` 0129 AND u.yearmonth = `micro_payout`.yearmonth 0130 GROUP BY u.yearmonth, u.project_id, u.member_id) AS num_plings_micropayout, 0131 0132 (SELECT SUM(u.credits_section)/100 FROM micro_payout u 0133 WHERE u.member_id = `micro_payout`.`member_id` 0134 AND u.project_id = `micro_payout`.`project_id` 0135 AND u.yearmonth = `micro_payout`.yearmonth 0136 GROUP BY u.yearmonth, u.project_id, u.member_id) AS amount_section_micropayout, 0137 0138 (SELECT round(sfs.sum_support/DATE_FORMAT(NOW() + INTERVAL 1 MONTH - INTERVAL DATE_FORMAT(NOW(),'%d') DAY,'%d')*DATE_FORMAT(NOW(),'%d') /sfs.sum_amount_payout,2) AS factor FROM section_funding_stats sfs WHERE sfs.yearmonth = `micro_payout`.yearmonth AND sfs.section_id = `micro_payout`.section_id) AS now_section_payout_factor, 0139 0140 `micro_payout`.section_id, `micro_payout`.section_payout_factor, `micro_payout`.org_factor 0141 0142 FROM 0143 `micro_payout` 0144 STRAIGHT_JOIN 0145 `project` ON `project`.`project_id` = `micro_payout`.`project_id` 0146 STRAIGHT_JOIN 0147 `project_category` ON `project_category`.`project_category_id` = `micro_payout`.`project_category_id` 0148 LEFT JOIN 0149 `member_payout` ON `member_payout`.`member_id` = `micro_payout`.`member_id` 0150 AND `member_payout`.`yearmonth` = `micro_payout`.`yearmonth` 0151 LEFT JOIN `tag_object` ON `tag_object`.`tag_type_id` = 1 AND `tag_object`.`tag_group_id` = 7 AND `tag_object`.`is_deleted` = 0 AND `tag_object`.`tag_object_id` = `project`.`project_id` 0152 WHERE 0153 `micro_payout`.`member_id` = :member_id 0154 AND `micro_payout`.`yearmonth` = :yearmonth 0155 "; 0156 0157 if(null != $section_id) { 0158 $sql .= " AND `micro_payout`.`section_id` = ".$section_id; 0159 } 0160 0161 $sql .= " GROUP BY `micro_payout`.`yearmonth`, `micro_payout`.`project_id` 0162 ORDER BY `micro_payout`.`yearmonth` DESC, `project_category`.`title`, `project`.`title` 0163 "; 0164 $result = Zend_Db_Table::getDefaultAdapter()->query($sql, array('member_id' => $member_id, 'yearmonth' => $yearmonth)); 0165 0166 if ($result->rowCount() > 0) { 0167 return $result->fetchAll(); 0168 } else { 0169 return array(); 0170 0171 } 0172 } 0173 0174 0175 public function getUserDownloadsAndViewsForProject($member_id, $yearmonth, $section_id, $project_id) 0176 { 0177 $sql = " 0178 SELECT 0179 `micro_payout`.`yearmonth`,`micro_payout`.`project_id`,`micro_payout`.`project_category_id`,`micro_payout`.`category_pling_factor`,`project_category`.`title`, `project`.`title`,`micro_payout`.`paypal_mail`, 0180 `micro_payout`.is_license_missing, 0181 `micro_payout`.is_source_missing, 0182 `micro_payout`.is_pling_excluded, 0183 CASE WHEN (SELECT count(1) AS `sum_plings` FROM `project_plings` `pp` WHERE `pp`.`project_id` = `micro_payout`.`project_id` AND `pp`.`is_deleted` = 0 AND `is_active` = 1 GROUP BY `pp`.`project_id`) > 0 THEN (SELECT count(1) AS `sum_plings` FROM `project_plings` `pp` WHERE `pp`.`project_id` = `micro_payout`.`project_id` AND `pp`.`is_deleted` = 0 AND `is_active` = 1 GROUP BY `pp`.`project_id`) + 1 ELSE 1 END AS `num_plings_now`, 0184 `project`.`title`, 0185 `project`.`image_small`, 0186 `project_category`.`title` AS `cat_title`, 0187 laplace_score(`project`.`count_likes`, `project`.`count_dislikes`)/100 AS `laplace_score`, 0188 `member_payout`.`amount`, 0189 `member_payout`.`status`, 0190 `member_payout`.`payment_transaction_id`, 0191 CASE WHEN `tag_object`.`tag_item_id` IS NULL THEN 1 ELSE 0 END AS `is_license_missing_now`, 0192 CASE WHEN ((`project_category`.`source_required` = 1 AND ((`project`.`source_url` IS NOT NULL AND LENGTH(`project`.`source_url`) > 0) OR `project`.gitlab_project_id IS NOT NULL)) OR (`project_category`.`source_required` = 0)) THEN 0 ELSE 1 END AS `is_source_missing_now`, 0193 `project`.`pling_excluded` AS `is_pling_excluded_now`, 0194 (SELECT SUM(u.num_plings) FROM micro_payout u 0195 WHERE u.member_id = `micro_payout`.`member_id` 0196 and u.project_id = `micro_payout`.`project_id` 0197 AND u.yearmonth = `micro_payout`.yearmonth 0198 AND u.`type` = 0 0199 GROUP BY u.yearmonth, u.project_id, u.member_id) AS num_downloads_micropayout, 0200 (SELECT SUM(u.credits_plings)/100 FROM micro_payout u 0201 WHERE u.member_id = `micro_payout`.`member_id` 0202 AND u.project_id = `micro_payout`.`project_id` 0203 AND u.yearmonth = `micro_payout`.yearmonth 0204 AND u.`type` = 0 0205 GROUP BY u.yearmonth, u.project_id, u.member_id) AS amount_downloads_micropayout, 0206 (SELECT SUM(u.num_plings) FROM micro_payout u 0207 WHERE u.member_id = `micro_payout`.`member_id` 0208 and u.project_id = `micro_payout`.`project_id` 0209 AND u.yearmonth = `micro_payout`.yearmonth 0210 AND u.`type` = 1 0211 GROUP BY u.yearmonth, u.project_id, u.member_id) AS num_views_micropayout, 0212 (SELECT SUM(u.credits_plings)/100 FROM micro_payout u 0213 WHERE u.member_id = `micro_payout`.`member_id` 0214 AND u.project_id = `micro_payout`.`project_id` 0215 AND u.yearmonth = `micro_payout`.yearmonth 0216 AND u.`type` = 1 0217 GROUP BY u.yearmonth, u.project_id, u.member_id) AS amount_views_micropayout, 0218 0219 (SELECT SUM(u.credits_plings)/100 FROM micro_payout u 0220 WHERE u.member_id = `micro_payout`.`member_id` 0221 AND u.project_id = `micro_payout`.`project_id` 0222 AND u.yearmonth = `micro_payout`.yearmonth 0223 GROUP BY u.yearmonth, u.project_id, u.member_id) AS amount_plings_micropayout, 0224 0225 (SELECT SUM(u.credits_plings) FROM micro_payout u 0226 WHERE u.member_id = `micro_payout`.`member_id` 0227 AND u.project_id = `micro_payout`.`project_id` 0228 AND u.yearmonth = `micro_payout`.yearmonth 0229 GROUP BY u.yearmonth, u.project_id, u.member_id) AS num_plings_micropayout, 0230 0231 (SELECT SUM(u.credits_section)/100 FROM micro_payout u 0232 WHERE u.member_id = `micro_payout`.`member_id` 0233 AND u.project_id = `micro_payout`.`project_id` 0234 AND u.yearmonth = `micro_payout`.yearmonth 0235 GROUP BY u.yearmonth, u.project_id, u.member_id) AS amount_section_micropayout, 0236 0237 (SELECT round(sfs.sum_support/DATE_FORMAT(NOW() + INTERVAL 1 MONTH - INTERVAL DATE_FORMAT(NOW(),'%d') DAY,'%d')*DATE_FORMAT(NOW(),'%d') /sfs.sum_amount_payout,2) AS factor FROM section_funding_stats sfs WHERE sfs.yearmonth = `micro_payout`.yearmonth AND sfs.section_id = `micro_payout`.section_id) AS now_section_payout_factor, 0238 0239 `micro_payout`.section_id, `micro_payout`.section_payout_factor, `micro_payout`.org_factor 0240 0241 FROM 0242 `micro_payout` 0243 STRAIGHT_JOIN 0244 `project` ON `project`.`project_id` = `micro_payout`.`project_id` 0245 STRAIGHT_JOIN 0246 `project_category` ON `project_category`.`project_category_id` = `micro_payout`.`project_category_id` 0247 LEFT JOIN 0248 `member_payout` ON `member_payout`.`member_id` = `micro_payout`.`member_id` 0249 AND `member_payout`.`yearmonth` = `micro_payout`.`yearmonth` 0250 LEFT JOIN `tag_object` ON `tag_object`.`tag_type_id` = 1 AND `tag_object`.`tag_group_id` = 7 AND `tag_object`.`is_deleted` = 0 AND `tag_object`.`tag_object_id` = `project`.`project_id` 0251 WHERE 0252 `micro_payout`.`member_id` = :member_id 0253 AND `micro_payout`.`yearmonth` = :yearmonth 0254 AND `micro_payout`.`section_id` = :section_id 0255 AND `micro_payout`.`project_id` = :project_id 0256 GROUP BY `micro_payout`.`yearmonth`, `micro_payout`.`project_id` 0257 ORDER BY `micro_payout`.`yearmonth` DESC, `project_category`.`title`, `project`.`title` 0258 "; 0259 $result = Zend_Db_Table::getDefaultAdapter()->query($sql, array('member_id' => $member_id, 'yearmonth' => $yearmonth, 'section_id' => $section_id, 'project_id' => $project_id)); 0260 0261 if ($result->rowCount() > 0) { 0262 return $result->fetchAll(); 0263 } else { 0264 return array(); 0265 0266 } 0267 } 0268 0269 0270 public function getUserAffiliatesForMonth($member_id, $yearmonth) 0271 { 0272 return $this->getUserAffiliatesForMonthAndSection($member_id, $yearmonth, null); 0273 } 0274 0275 public function getUserAffiliatesForMonthAndSection($member_id, $yearmonth, $section_id = null) 0276 { 0277 $sql = " 0278 SELECT 0279 yearmonth, se.section_id, se.name AS section_name, se.`order` AS section_order, su.member_id AS supporter_member_id, m.username AS supporter_username 0280 ,SUM(p.tier) AS sum_donations 0281 ,(SELECT percent FROM affiliate_config WHERE p.yearmonth >= active_from AND p.yearmonth <= active_until) AS affiliate_percent 0282 from section_support_paypements p 0283 JOIN section_support s ON s.section_support_id = p.section_support_id 0284 JOIN support su ON su.id = s.support_id 0285 JOIN project pr ON pr.project_id = s.project_id 0286 LEFT JOIN section_category sc ON sc.project_category_id = pr.`project_category_id` 0287 LEFT JOIN section se ON se.section_id = sc.section_id 0288 JOIN member m ON m.member_id = su.member_id 0289 WHERE 0290 pr.member_id = :member_id 0291 AND p.`yearmonth` = :yearmonth 0292 0293 "; 0294 0295 if(null != $section_id) { 0296 $sql .= " AND se.`section_id` = ".$section_id; 0297 } 0298 0299 $sql .= " GROUP BY su.member_id 0300 ORDER BY su.active_time desc 0301 "; 0302 $result = Zend_Db_Table::getDefaultAdapter()->query($sql, array('member_id' => $member_id, 'yearmonth' => $yearmonth)); 0303 0304 if ($result->rowCount() > 0) { 0305 return $result->fetchAll(); 0306 } else { 0307 return array(); 0308 0309 } 0310 } 0311 0312 0313 public function getUserDownloadsForMonth($member_id, $yearmonth) 0314 { 0315 return $this->getUserDownloadsForMonthAndSection($member_id, $yearmonth, null); 0316 } 0317 0318 0319 public function getUserDownloadsForMonthAndSection($member_id, $yearmonth, $section_id = null) 0320 { 0321 $sql = " 0322 SELECT 0323 `member_dl_plings`.*, 0324 CASE WHEN (SELECT count(1) AS `sum_plings` FROM `project_plings` `pp` WHERE `pp`.`project_id` = `member_dl_plings`.`project_id` AND `pp`.`is_deleted` = 0 AND `is_active` = 1 GROUP BY `pp`.`project_id`) > 0 THEN (SELECT count(1) AS `sum_plings` FROM `project_plings` `pp` WHERE `pp`.`project_id` = `member_dl_plings`.`project_id` AND `pp`.`is_deleted` = 0 AND `is_active` = 1 GROUP BY `pp`.`project_id`) + 1 ELSE 1 END AS `num_plings_now`, 0325 `project`.`title`, 0326 `project`.`image_small`, 0327 `project_category`.`title` AS `cat_title`, 0328 laplace_score(`project`.`count_likes`, `project`.`count_dislikes`)/100 AS `laplace_score`, 0329 `member_payout`.`amount`, 0330 `member_payout`.`status`, 0331 `member_payout`.`payment_transaction_id`, 0332 CASE WHEN `tag_object`.`tag_item_id` IS NULL THEN 1 ELSE 0 END AS `is_license_missing_now`, 0333 CASE WHEN ((`project_category`.`source_required` = 1 AND ((`project`.`source_url` IS NOT NULL AND LENGTH(`project`.`source_url`) > 0) OR `project`.gitlab_project_id IS NOT NULL)) OR (`project_category`.`source_required` = 0)) THEN 0 ELSE 1 END AS `is_source_missing_now`, 0334 `project`.`pling_excluded` AS `is_pling_excluded_now`, 0335 (SELECT u.num_downloads FROM member_dl_plings_nouk u WHERE u.member_id = `member_dl_plings`.`member_id` and u.project_id = `member_dl_plings`.`project_id` AND u.yearmonth = `member_dl_plings`.yearmonth) AS num_downloads_nouk, 0336 (SELECT u.probably_payout_amount FROM member_dl_plings_nouk u WHERE u.member_id = `member_dl_plings`.`member_id` and u.project_id = `member_dl_plings`.`project_id` AND u.yearmonth = `member_dl_plings`.yearmonth) AS probably_payout_amount_nouk 0337 ,sc.section_id,s.name AS section_name,`member_dl_plings`.section_payout_factor 0338 FROM 0339 `member_dl_plings` 0340 STRAIGHT_JOIN 0341 `project` ON `project`.`project_id` = `member_dl_plings`.`project_id` 0342 STRAIGHT_JOIN 0343 `project_category` ON `project_category`.`project_category_id` = `member_dl_plings`.`project_category_id` 0344 LEFT JOIN 0345 `member_payout` ON `member_payout`.`member_id` = `member_dl_plings`.`member_id` 0346 AND `member_payout`.`yearmonth` = `member_dl_plings`.`yearmonth` 0347 LEFT JOIN `tag_object` ON `tag_object`.`tag_type_id` = 1 AND `tag_object`.`tag_group_id` = 7 AND `tag_object`.`is_deleted` = 0 AND `tag_object`.`tag_object_id` = `project`.`project_id` 0348 LEFT JOIN section_category sc ON sc.project_category_id = `member_dl_plings`.`project_category_id` 0349 LEFT JOIN section s ON s.section_id = sc.section_id 0350 WHERE 0351 `member_dl_plings`.`member_id` = :member_id 0352 AND `member_dl_plings`.`yearmonth` = :yearmonth "; 0353 0354 if(null != $section_id) { 0355 $sql .= " AND `member_dl_plings`.`section_id` = ".$section_id; 0356 } 0357 0358 $sql .= " ORDER BY `member_dl_plings`.`yearmonth` DESC, `project_category`.`title`, `project`.`title` 0359 "; 0360 $result = Zend_Db_Table::getDefaultAdapter()->query($sql, array('member_id' => $member_id, 'yearmonth' => $yearmonth)); 0361 0362 if ($result->rowCount() > 0) { 0363 return $result->fetchAll(); 0364 } else { 0365 return array(); 0366 0367 } 0368 } 0369 0370 public function getUserSectionsForMonth($member_id, $yearmonth) 0371 { 0372 $sql = " 0373 SELECT yearmonth, section_id, section_name, section_order, section_payout_factor, COUNT(project_id) AS count_projects, SUM(num_downloads) AS num_downloads, SUM(probably_payout_amount) AS sum_probably_payout_amount, SUM(real_payout_amount) AS sum_real_payout_amount, MAX(amount) AS payout_amount, MAX(STATUS) AS payout_status, MAX(payment_transaction_id) AS payout_payment_transaction_id, MAX(paypal_mail) AS paypal_mail 0374 FROM ( 0375 SELECT 0376 `member_dl_plings`.*, 0377 CASE WHEN (SELECT count(1) AS `sum_plings` FROM `project_plings` `pp` WHERE `pp`.`project_id` = `member_dl_plings`.`project_id` AND `pp`.`is_deleted` = 0 AND `is_active` = 1 GROUP BY `pp`.`project_id`) > 0 THEN (SELECT count(1) AS `sum_plings` FROM `project_plings` `pp` WHERE `pp`.`project_id` = `member_dl_plings`.`project_id` AND `pp`.`is_deleted` = 0 AND `is_active` = 1 GROUP BY `pp`.`project_id`) + 1 ELSE 1 END AS `num_plings_now`, 0378 `project`.`title`, 0379 `project`.`image_small`, 0380 `project_category`.`title` AS `cat_title`, 0381 laplace_score(`project`.`count_likes`, `project`.`count_dislikes`)/100 AS `laplace_score`, 0382 `member_payout`.`amount`, 0383 `member_payout`.`status`, 0384 `member_payout`.`payment_transaction_id`, 0385 CASE WHEN `tag_object`.`tag_item_id` IS NULL THEN 1 ELSE 0 END AS `is_license_missing_now`, 0386 CASE WHEN ((`project_category`.`source_required` = 1 AND ((`project`.`source_url` IS NOT NULL AND LENGTH(`project`.`source_url`) > 0) OR `project`.gitlab_project_id IS NOT NULL)) OR (`project_category`.`source_required` = 0)) THEN 0 ELSE 1 END AS `is_source_missing_now`, 0387 `project`.`pling_excluded` AS `is_pling_excluded_now`, 0388 (SELECT u.num_downloads FROM member_dl_plings_nouk u WHERE u.member_id = `member_dl_plings`.`member_id` and u.project_id = `member_dl_plings`.`project_id` AND u.yearmonth = `member_dl_plings`.yearmonth) AS num_downloads_nouk, 0389 (SELECT u.probably_payout_amount FROM member_dl_plings_nouk u WHERE u.member_id = `member_dl_plings`.`member_id` and u.project_id = `member_dl_plings`.`project_id` AND u.yearmonth = `member_dl_plings`.yearmonth) AS probably_payout_amount_nouk 0390 ,s.name AS section_name 0391 ,s.`order` AS section_order 0392 , case when is_license_missing = 1 OR is_source_missing = 1 OR is_pling_excluded = 1 then 0 ELSE probably_payout_amount END AS real_payout_amount 0393 0394 FROM 0395 `member_dl_plings` 0396 STRAIGHT_JOIN 0397 `project` ON `project`.`project_id` = `member_dl_plings`.`project_id` 0398 STRAIGHT_JOIN 0399 `project_category` ON `project_category`.`project_category_id` = `member_dl_plings`.`project_category_id` 0400 LEFT JOIN 0401 `member_payout` ON `member_payout`.`member_id` = `member_dl_plings`.`member_id` 0402 AND `member_payout`.`yearmonth` = `member_dl_plings`.`yearmonth` 0403 LEFT JOIN `tag_object` ON `tag_object`.`tag_type_id` = 1 AND `tag_object`.`tag_group_id` = 7 AND `tag_object`.`is_deleted` = 0 AND `tag_object`.`tag_object_id` = `project`.`project_id` 0404 LEFT JOIN section_category sc ON sc.project_category_id = `member_dl_plings`.`project_category_id` 0405 LEFT JOIN section s ON s.section_id = sc.section_id 0406 WHERE 0407 `member_dl_plings`.`member_id` = :member_id 0408 AND `member_dl_plings`.`yearmonth` = :yearmonth 0409 ) A 0410 GROUP BY yearmonth, section_id, section_name, section_payout_factor 0411 ORDER BY section_order 0412 "; 0413 $result = Zend_Db_Table::getDefaultAdapter()->query($sql, array('member_id' => $member_id, 'yearmonth' => $yearmonth)); 0414 0415 if ($result->rowCount() > 0) { 0416 return $result->fetchAll(); 0417 } else { 0418 return array(); 0419 0420 } 0421 } 0422 0423 0424 public function getUserSectionsForDownloadAndViewsForMonth($member_id, $yearmonth) 0425 { 0426 /* 0427 $sql = " 0428 SELECT yearmonth, section_id, section_name, section_order, section_payout_factor, COUNT(project_id) AS count_projects, SUM(credits_plings) AS num_credits_plings, SUM(credits_section) AS num_credits_section, SUM(credits_plings)/100 AS sum_amount_credits_plings, SUM(credits_section)/100 AS sum_amount_credits_section 0429 , SUM(real_credits_plings) AS num_real_credits_plings 0430 , SUM(real_credits_section) AS num_real_credits_section 0431 ,(SELECT round(sfs.sum_support/DATE_FORMAT(NOW() + INTERVAL 1 MONTH - INTERVAL DATE_FORMAT(NOW(),'%d') DAY,'%d')*DATE_FORMAT(NOW(),'%d') /sfs.sum_amount_payout,2) AS factor FROM section_funding_stats sfs WHERE sfs.yearmonth = A.yearmonth AND sfs.section_id = A.section_id) AS now_section_payout_factor 0432 , MAX(amount) AS payout_amount, MAX(STATUS) AS payout_status, MAX(payment_transaction_id) AS payout_payment_transaction_id, MAX(paypal_mail) AS paypal_mail 0433 FROM ( 0434 SELECT 0435 SUM(credits_plings) as credits_plings, 0436 SUM(credits_section) AS credits_section, 0437 `micro_payout`.yearmonth, 0438 `micro_payout`.section_id, 0439 `micro_payout`.section_payout_factor, 0440 `micro_payout`.project_id, 0441 `micro_payout`.paypal_mail, 0442 `project`.`title`, 0443 `project`.`image_small`, 0444 `project_category`.`title` AS `cat_title`, 0445 laplace_score(`project`.`count_likes`, `project`.`count_dislikes`)/100 AS `laplace_score`, 0446 `member_payout`.`amount`, 0447 `member_payout`.`status`, 0448 `member_payout`.`payment_transaction_id`, 0449 CASE WHEN `tag_object`.`tag_item_id` IS NULL THEN 1 ELSE 0 END AS `is_license_missing_now`, 0450 CASE WHEN ((`project_category`.`source_required` = 1 AND `project`.`source_url` IS NOT NULL AND LENGTH(`project`.`source_url`) > 0) OR (`project_category`.`source_required` = 0)) THEN 0 ELSE 1 END AS `is_source_missing_now`, 0451 `project`.`pling_excluded` AS `is_pling_excluded_now`, 0452 s.name AS section_name, 0453 s.`order` AS section_order, 0454 SUM(case when is_license_missing = 1 OR is_source_missing = 1 OR is_pling_excluded = 1 then 0 ELSE credits_plings END) AS real_credits_plings, 0455 SUM(case when is_license_missing = 1 OR is_source_missing = 1 OR is_pling_excluded = 1 then 0 ELSE credits_section END) AS real_credits_section 0456 0457 FROM 0458 `micro_payout` 0459 STRAIGHT_JOIN 0460 `project` ON `project`.`project_id` = `micro_payout`.`project_id` 0461 STRAIGHT_JOIN 0462 `project_category` ON `project_category`.`project_category_id` = `project`.`project_category_id` 0463 LEFT JOIN 0464 `member_payout` ON `member_payout`.`member_id` = `project`.`member_id` 0465 AND `member_payout`.`yearmonth` = `micro_payout`.`yearmonth` 0466 LEFT JOIN `tag_object` ON `tag_object`.`tag_type_id` = 1 AND `tag_object`.`tag_group_id` = 7 AND `tag_object`.`is_deleted` = 0 AND `tag_object`.`tag_object_id` = `project`.`project_id` 0467 LEFT JOIN section_category sc ON sc.project_category_id = `project`.`project_category_id` 0468 LEFT JOIN section s ON s.section_id = sc.section_id 0469 WHERE 0470 `micro_payout`.`member_id` = :member_id 0471 AND `micro_payout`.`yearmonth` = :yearmonth 0472 GROUP BY `micro_payout`.`project_id` 0473 0474 ) A 0475 GROUP BY yearmonth, section_id, section_name, section_payout_factor 0476 ORDER BY section_order 0477 "; 0478 * 0479 */ 0480 0481 $sql = "select 0482 m.yearmonth, m.section_id, s.name AS section_name, s.order as section_order, m.section_payout_factor 0483 ,COUNT(DISTINCT project_id) AS count_projects 0484 , SUM(credits_plings) AS num_credits_plings 0485 , SUM(credits_section) AS num_credits_section 0486 , SUM(credits_org) AS num_credits_plings_org 0487 , SUM(credits_org*section_payout_factor) AS num_credits_section_org 0488 , SUM(credits_plings)/100 AS sum_amount_credits_plings 0489 , SUM(credits_section)/100 AS sum_amount_credits_section, 0490 SUM(case when is_license_missing = 1 OR is_source_missing = 1 OR is_pling_excluded = 1 then 0 ELSE credits_plings END) AS num_real_credits_plings, 0491 SUM(case when is_license_missing = 1 OR is_source_missing = 1 OR is_pling_excluded = 1 then 0 ELSE credits_section END) AS num_real_credits_section, 0492 SUM(case when is_license_missing = 1 OR is_source_missing = 1 OR is_pling_excluded = 1 then 0 ELSE credits_org END) AS num_real_credits_plings_org 0493 ,(SELECT round(sfs.sum_support/DATE_FORMAT(NOW() + INTERVAL 1 MONTH - INTERVAL DATE_FORMAT(NOW(),'%d') DAY,'%d')*DATE_FORMAT(NOW(),'%d') /sfs.sum_amount_payout,2) AS factor FROM section_funding_stats sfs WHERE sfs.yearmonth = m.yearmonth AND sfs.section_id = m.section_id) AS now_section_payout_factor 0494 , MAX(amount) AS payout_amount, MAX(STATUS) AS payout_status, MAX(payment_transaction_id) AS payout_payment_transaction_id, MAX(m.paypal_mail) AS paypal_mail 0495 from micro_payout m 0496 LEFT JOIN section s ON s.section_id = m.section_id 0497 LEFT JOIN 0498 `member_payout` ON `member_payout`.`member_id` = m.`member_id` 0499 AND `member_payout`.`yearmonth` = m.`yearmonth` 0500 where m.member_id = :member_id 0501 and m.yearmonth = :yearmonth 0502 group by m.section_id 0503 ORDER BY s.`order`"; 0504 0505 $result = Zend_Db_Table::getDefaultAdapter()->query($sql, array('member_id' => $member_id, 'yearmonth' => $yearmonth)); 0506 0507 if ($result->rowCount() > 0) { 0508 return $result->fetchAll(); 0509 } else { 0510 return array(); 0511 0512 } 0513 } 0514 0515 0516 public function getUserAffiliateSectionsForMonth($member_id, $yearmonth) 0517 { 0518 $sql = " 0519 SELECT yearmonth, section_id, section_name, section_order, COUNT(supporter_member_id) AS count_supporters, SUM(sum_donations) AS sum_donations, 0520 (SELECT percent FROM affiliate_config WHERE A.yearmonth >= active_from AND A.yearmonth <= active_until) AS affiliate_percent 0521 FROM ( 0522 SELECT 0523 yearmonth, se.section_id, se.name AS section_name, se.`order` AS section_order, su.member_id AS supporter_member_id, m.username AS supporter_username 0524 ,SUM(p.tier) AS sum_donations 0525 from section_support_paypements p 0526 JOIN section_support s ON s.section_support_id = p.section_support_id 0527 JOIN support su ON su.id = s.support_id 0528 JOIN project pr ON pr.project_id = s.project_id 0529 LEFT JOIN section_category sc ON sc.project_category_id = pr.`project_category_id` 0530 LEFT JOIN section se ON se.section_id = sc.section_id 0531 JOIN member m ON m.member_id = su.member_id 0532 WHERE 0533 pr.member_id = :member_id 0534 AND p.`yearmonth` = :yearmonth 0535 GROUP BY su.member_id 0536 0537 ) A 0538 GROUP BY yearmonth, section_id, section_name 0539 ORDER BY section_order 0540 "; 0541 $result = Zend_Db_Table::getDefaultAdapter()->query($sql, array('member_id' => $member_id, 'yearmonth' => $yearmonth)); 0542 0543 if ($result->rowCount() > 0) { 0544 return $result->fetchAll(); 0545 } else { 0546 return array(); 0547 0548 } 0549 } 0550 0551 0552 0553 public function getUserDownloadMonths($member_id, $year) 0554 { 0555 $sql = " 0556 SELECT 0557 DISTINCT `member_dl_plings`.`yearmonth`, `member_payout`.payment_transaction_id, `member_payout`.`status` 0558 FROM 0559 `member_dl_plings` 0560 STRAIGHT_JOIN 0561 `project` ON `project`.`project_id` = `member_dl_plings`.`project_id` 0562 STRAIGHT_JOIN 0563 `project_category` ON `project_category`.`project_category_id` = `member_dl_plings`.`project_category_id` 0564 LEFT JOIN 0565 `member_payout` ON `member_payout`.`member_id` = `member_dl_plings`.`member_id` 0566 AND `member_payout`.`yearmonth` = `member_dl_plings`.`yearmonth` 0567 LEFT JOIN `tag_object` ON `tag_object`.`tag_type_id` = 1 AND `tag_object`.`tag_group_id` = 7 AND `tag_object`.`is_deleted` = 0 AND `tag_object`.`tag_object_id` = `project`.`project_id` 0568 WHERE 0569 `member_dl_plings`.`member_id` = :member_id 0570 AND SUBSTR(`member_dl_plings`.`yearmonth`,1,4) = :year 0571 ORDER BY `member_dl_plings`.`yearmonth` DESC 0572 "; 0573 $result = Zend_Db_Table::getDefaultAdapter()->query($sql, array('member_id' => $member_id, 'year' => $year)); 0574 0575 if ($result->rowCount() > 0) { 0576 return $result->fetchAll(); 0577 } else { 0578 return array(); 0579 0580 } 0581 } 0582 0583 0584 public function getUserDownloadsAndViewsMonths($member_id, $year) 0585 { 0586 $sql = " 0587 SELECT 0588 `yearmonth`, max(payment_transaction_id) AS payment_transaction_id, MAX(`status`) AS `status` 0589 FROM 0590 0591 ( 0592 0593 SELECT 0594 DISTINCT `micro_payout`.`yearmonth`, `member_payout`.payment_transaction_id, `member_payout`.`status` 0595 FROM 0596 0597 `micro_payout` 0598 STRAIGHT_JOIN 0599 `project` ON `project`.`project_id` = `micro_payout`.`project_id` 0600 STRAIGHT_JOIN 0601 `project_category` ON `project_category`.`project_category_id` = `micro_payout`.`project_category_id` 0602 LEFT JOIN 0603 `member_payout` ON `member_payout`.`member_id` = `micro_payout`.`member_id` 0604 AND `member_payout`.`yearmonth` = `micro_payout`.`yearmonth` 0605 LEFT JOIN `tag_object` ON `tag_object`.`tag_type_id` = 1 AND `tag_object`.`tag_group_id` = 7 AND `tag_object`.`is_deleted` = 0 AND `tag_object`.`tag_object_id` = `project`.`project_id` 0606 WHERE 0607 `micro_payout`.`member_id` = :member_id 0608 AND SUBSTR(`micro_payout`.`yearmonth`,1,4) = :year 0609 0610 UNION ALL 0611 0612 SELECT 0613 DISTINCT p.yearmonth, null as payment_transaction_id, NULL AS `status` 0614 from section_support_paypements p 0615 JOIN section_support s ON s.section_support_id = p.section_support_id 0616 JOIN project pr ON pr.project_id = s.project_id 0617 WHERE 0618 pr.member_id = :member_id 0619 AND SUBSTR(p.yearmonth,1,4) = :year 0620 0621 ) A 0622 GROUP BY `yearmonth` 0623 ORDER BY `yearmonth` DESC 0624 "; 0625 $result = Zend_Db_Table::getDefaultAdapter()->query($sql, array('member_id' => $member_id, 'year' => $year)); 0626 0627 if ($result->rowCount() > 0) { 0628 return $result->fetchAll(); 0629 } else { 0630 return array(); 0631 0632 } 0633 } 0634 0635 0636 public function getUserAffiliatesMonths($member_id, $year) 0637 { 0638 $sql = " 0639 SELECT 0640 DISTINCT p.yearmonth 0641 from section_support_paypements p 0642 JOIN section_support s ON s.section_support_id = p.section_support_id 0643 JOIN project pr ON pr.project_id = s.project_id 0644 WHERE 0645 pr.member_id = :member_id 0646 AND SUBSTR(p.yearmonth,1,4) = :year 0647 ORDER BY p.yearmonth DESC 0648 "; 0649 $result = Zend_Db_Table::getDefaultAdapter()->query($sql, array('member_id' => $member_id, 'year' => $year)); 0650 0651 if ($result->rowCount() > 0) { 0652 return $result->fetchAll(); 0653 } else { 0654 return array(); 0655 0656 } 0657 } 0658 0659 public function getUserDownloadYears($member_id) 0660 { 0661 $sql = " 0662 SELECT 0663 0664 SUBSTR(`member_dl_plings`.`yearmonth`,1,4) as year, 0665 MAX(`member_dl_plings`.`yearmonth`) as max_yearmonth, 0666 SUM(`member_payout`.amount) as sum_amount 0667 FROM 0668 `member_dl_plings` 0669 STRAIGHT_JOIN 0670 `project` ON `project`.`project_id` = `member_dl_plings`.`project_id` 0671 STRAIGHT_JOIN 0672 `project_category` ON `project_category`.`project_category_id` = `member_dl_plings`.`project_category_id` 0673 LEFT JOIN 0674 `member_payout` ON `member_payout`.`member_id` = `member_dl_plings`.`member_id` 0675 AND `member_payout`.`yearmonth` = `member_dl_plings`.`yearmonth` 0676 LEFT JOIN `tag_object` ON `tag_object`.`tag_type_id` = 1 AND `tag_object`.`tag_group_id` = 7 AND `tag_object`.`is_deleted` = 0 AND `tag_object`.`tag_object_id` = `project`.`project_id` 0677 WHERE 0678 `member_dl_plings`.`member_id` = :member_id 0679 GROUP BY SUBSTR(`member_dl_plings`.`yearmonth`,1,4) 0680 ORDER BY SUBSTR(`member_dl_plings`.`yearmonth`,1,4) DESC 0681 "; 0682 $result = Zend_Db_Table::getDefaultAdapter()->query($sql, array('member_id' => $member_id)); 0683 0684 if ($result->rowCount() > 0) { 0685 return $result->fetchAll(); 0686 } else { 0687 return array(); 0688 0689 } 0690 } 0691 0692 0693 public function getUserDownloadsAndViewsYears($member_id) 0694 { 0695 $sql = " 0696 SELECT 0697 0698 SUBSTR(`micro_payout`.`yearmonth`,1,4) as year, 0699 MAX(`micro_payout`.`yearmonth`) as max_yearmonth, 0700 SUM(`member_payout`.amount) as sum_amount 0701 FROM 0702 `micro_payout` 0703 STRAIGHT_JOIN 0704 `project` ON `project`.`project_id` = `micro_payout`.`project_id` 0705 STRAIGHT_JOIN 0706 `project_category` ON `project_category`.`project_category_id` = `micro_payout`.`project_category_id` 0707 LEFT JOIN 0708 `member_payout` ON `member_payout`.`member_id` = `micro_payout`.`member_id` 0709 AND `member_payout`.`yearmonth` = `micro_payout`.`yearmonth` 0710 LEFT JOIN `tag_object` ON `tag_object`.`tag_type_id` = 1 AND `tag_object`.`tag_group_id` = 7 AND `tag_object`.`is_deleted` = 0 AND `tag_object`.`tag_object_id` = `project`.`project_id` 0711 WHERE 0712 `micro_payout`.`member_id` = :member_id 0713 GROUP BY SUBSTR(`micro_payout`.`yearmonth`,1,4) 0714 ORDER BY SUBSTR(`micro_payout`.`yearmonth`,1,4) DESC 0715 "; 0716 $result = Zend_Db_Table::getDefaultAdapter()->query($sql, array('member_id' => $member_id)); 0717 0718 if ($result->rowCount() > 0) { 0719 return $result->fetchAll(); 0720 } else { 0721 return array(); 0722 0723 } 0724 } 0725 0726 0727 public function getUserAffiliatesYears($member_id) 0728 { 0729 $sql = " 0730 SELECT YEAR,max(max_yearmonth) AS max_yearmonth, max(sum_amount) AS sum_amount FROM ( 0731 SELECT 0732 0733 SUBSTR(`micro_payout`.`yearmonth`,1,4) as year, 0734 MAX(`micro_payout`.`yearmonth`) as max_yearmonth, 0735 SUM(`member_payout`.amount) as sum_amount 0736 FROM 0737 `micro_payout` 0738 STRAIGHT_JOIN 0739 `project` ON `project`.`project_id` = `micro_payout`.`project_id` 0740 STRAIGHT_JOIN 0741 `project_category` ON `project_category`.`project_category_id` = `micro_payout`.`project_category_id` 0742 LEFT JOIN 0743 `member_payout` ON `member_payout`.`member_id` = `micro_payout`.`member_id` 0744 AND `member_payout`.`yearmonth` = `micro_payout`.`yearmonth` 0745 LEFT JOIN `tag_object` ON `tag_object`.`tag_type_id` = 1 AND `tag_object`.`tag_group_id` = 7 AND `tag_object`.`is_deleted` = 0 AND `tag_object`.`tag_object_id` = `project`.`project_id` 0746 WHERE 0747 `micro_payout`.`member_id` = :member_id 0748 GROUP BY SUBSTR(`micro_payout`.`yearmonth`,1,4) 0749 0750 0751 UNION ALL 0752 SELECT 0753 SUBSTR(p.yearmonth,1,4) as year, 0754 null as max_yearmonth, 0755 null as sum_amount 0756 from section_support_paypements p 0757 JOIN section_support s ON s.section_support_id = p.section_support_id 0758 JOIN project pr ON pr.project_id = s.project_id 0759 WHERE s.project_id IS NOT NULL 0760 AND pr.member_id = :member_id 0761 GROUP BY SUBSTR(p.yearmonth,1,4) 0762 ) A 0763 GROUP BY year 0764 ORDER BY year DESC 0765 0766 "; 0767 $result = Zend_Db_Table::getDefaultAdapter()->query($sql, array('member_id' => $member_id)); 0768 0769 if ($result->rowCount() > 0) { 0770 return $result->fetchAll(); 0771 } else { 0772 return array(); 0773 0774 } 0775 } 0776 0777 public function getMonthEarn($member_id,$yyyymm) 0778 { 0779 $sql = " select sum(probably_payout_amount) amount 0780 from member_dl_plings 0781 where member_id=:member_id 0782 and yearmonth=:yyyymm 0783 and is_pling_excluded = 0 0784 and is_license_missing = 0"; 0785 0786 $resultSet = Zend_Db_Table::getDefaultAdapter()->fetchAll($sql, array('member_id' => $member_id,'yyyymm' =>$yyyymm)); 0787 return array_pop($resultSet); 0788 0789 0790 0791 } 0792 0793 0794 public function getLastMonthEarn($member_id) 0795 { 0796 $sql = "SELECT TRUNCATE(SUM(sum_payout),2) AS amount 0797 FROM ( 0798 SELECT 0799 `micro_payout`.yearmonth, 0800 SUM(case when is_license_missing = 1 OR is_source_missing = 1 OR is_pling_excluded = 1 then 0 ELSE credits_section END)/100 AS sum_payout 0801 FROM 0802 `micro_payout` 0803 WHERE `micro_payout`.`member_id` = :member_id 0804 AND `micro_payout`.yearmonth = DATE_FORMAT(NOW() - INTERVAL 1 MONTH,'%Y%m') 0805 GROUP BY `micro_payout`.yearmonth 0806 0807 UNION ALL 0808 0809 SELECT 0810 p2.yearmonth 0811 ,round(SUM(p2.tier)*(SELECT percent FROM affiliate_config WHERE p2.yearmonth >= active_from AND p2.yearmonth <= active_until),2) AS sum_payout 0812 from section_support_paypements p2 0813 JOIN section_support s2 ON s2.section_support_id = p2.section_support_id 0814 JOIN support su2 ON su2.id = s2.support_id 0815 JOIN project pr2 ON pr2.project_id = s2.project_id 0816 JOIN member m2 ON m2.member_id = su2.member_id 0817 WHERE 0818 pr2.member_id = :member_id 0819 AND p2.yearmonth = DATE_FORMAT(NOW() - INTERVAL 1 MONTH,'%Y%m') 0820 0821 0822 ) A2 0823 GROUP BY yearmonth"; 0824 0825 $resultSet = Zend_Db_Table::getDefaultAdapter()->fetchAll($sql, array('member_id' => $member_id)); 0826 return array_pop($resultSet); 0827 0828 0829 0830 } 0831 0832 0833 0834 public function getPayoutHistory($member_id) 0835 { 0836 $sql=" 0837 SELECT pl.yearmonth 0838 ,TRUNCATE(sum(probably_payout_amount*section_payout_factor), 2) amount 0839 ,(select count(1) from member_payout p where p.yearmonth=pl.yearmonth and p.member_id = pl.member_id) cnt 0840 from member_dl_plings pl 0841 where pl.member_id =:member_id and yearmonth > 201704 0842 and is_license_missing = 0 0843 and is_source_missing = 0 0844 and is_pling_excluded = 0 0845 group by yearmonth 0846 order by yearmonth 0847 "; 0848 $resultSet = Zend_Db_Table::getDefaultAdapter()->fetchAll($sql, array('member_id' => $member_id)); 0849 return $resultSet; 0850 } 0851 0852 public function getPayoutHistory2($member_id) { 0853 0854 $cacheName = __FUNCTION__ . md5(serialize($member_id)); 0855 $cache = Zend_Registry::get('cache'); 0856 0857 $result = $cache->load($cacheName); 0858 0859 if ($result) { 0860 return $result; 0861 } 0862 0863 0864 $sql = "SELECT A2.yearmonth,TRUNCATE(SUM(sum_payout),2) AS amount, cnt 0865 FROM ( 0866 SELECT yearmonth, section_id 0867 ,case when yearmonth = DATE_FORMAT(NOW(),'%Y%m') then sum(real_credits_plings*now_section_payout_factor)/100 ELSE sum(real_credits_plings*section_payout_factor)/100 END AS sum_payout 0868 ,(select count(1) from member_payout p where p.yearmonth=A.yearmonth and p.member_id = A.member_id) cnt 0869 FROM ( 0870 0871 SELECT 0872 SUM(case when is_license_missing = 1 OR is_source_missing = 1 OR is_pling_excluded = 1 then 0 ELSE credits_plings END) AS real_credits_plings, 0873 `micro_payout`.yearmonth, 0874 `micro_payout`.section_id, 0875 `member_payout`.member_id, 0876 `micro_payout`.section_payout_factor, 0877 (SELECT round(sfs.sum_support/DATE_FORMAT(NOW() + INTERVAL 1 MONTH - INTERVAL DATE_FORMAT(NOW(),'%d') DAY,'%d')*DATE_FORMAT(NOW(),'%d') /sfs.sum_amount_payout,2) AS factor FROM section_funding_stats sfs WHERE sfs.yearmonth = `micro_payout`.yearmonth AND sfs.section_id = `micro_payout`.section_id) AS now_section_payout_factor 0878 FROM 0879 `micro_payout` 0880 STRAIGHT_JOIN 0881 `project` ON `project`.`project_id` = `micro_payout`.`project_id` 0882 STRAIGHT_JOIN 0883 `project_category` ON `project_category`.`project_category_id` = `project`.`project_category_id` 0884 LEFT JOIN 0885 `member_payout` ON `member_payout`.`member_id` = `project`.`member_id` 0886 AND `member_payout`.`yearmonth` = `micro_payout`.`yearmonth` 0887 LEFT JOIN `tag_object` ON `tag_object`.`tag_type_id` = 1 AND `tag_object`.`tag_group_id` = 7 AND `tag_object`.`is_deleted` = 0 AND `tag_object`.`tag_object_id` = `project`.`project_id` 0888 LEFT JOIN section_category sc ON sc.project_category_id = `project`.`project_category_id` 0889 LEFT JOIN section s ON s.section_id = sc.section_id 0890 WHERE `micro_payout`.`member_id` = :member_id 0891 AND `micro_payout`.yearmonth > 201704 0892 GROUP BY `micro_payout`.yearmonth, `micro_payout`.`project_id` 0893 ) A 0894 GROUP BY yearmonth,section_id 0895 0896 0897 UNION ALL 0898 0899 SELECT 0900 p2.yearmonth, s2.section_id 0901 ,round(SUM(p2.tier)*(SELECT percent FROM affiliate_config WHERE p2.yearmonth >= active_from AND p2.yearmonth <= active_until),2) AS sum_payout 0902 ,0 AS cnt 0903 from section_support_paypements p2 0904 JOIN section_support s2 ON s2.section_support_id = p2.section_support_id 0905 JOIN support su2 ON su2.id = s2.support_id 0906 JOIN project pr2 ON pr2.project_id = s2.project_id 0907 JOIN member m2 ON m2.member_id = su2.member_id 0908 WHERE 0909 pr2.member_id = :member_id 0910 GROUP BY p2.yearmonth, s2.section_id 0911 0912 0913 ) A2 0914 0915 GROUP BY yearmonth 0916 #HAVING amount > 0 0917 order by yearmonth 0918 "; 0919 0920 $resultSet = Zend_Db_Table::getDefaultAdapter()->fetchAll($sql, array('member_id' => $member_id)); 0921 0922 $cache->save($resultSet, $cacheName); 0923 0924 return $resultSet; 0925 } 0926 0927 }