File indexing completed on 2024-04-14 05:53:07
0001 CREATE TABLE `affiliate_config` ( 0002 `id` INT NOT NULL AUTO_INCREMENT, 0003 `percent` DOUBLE NOT NULL DEFAULT '0.15', 0004 `active_from` INT NOT NULL DEFAULT '201701', 0005 `active_until` INT NOT NULL DEFAULT '209912', 0006 PRIMARY KEY (`id`) 0007 ) 0008 COLLATE='latin1_swedish_ci' 0009 ; 0010 INSERT INTO `pling`.`affiliate_config` (`id`) VALUES ('1'); 0011 0012 0013 0014 0015 0016 //new support sum = support - affiliate payout 0017 0018 DROP PROCEDURE `generate_section_funding_stats_micro_payout`; 0019 0020 DELIMITER $$ 0021 CREATE PROCEDURE `generate_section_funding_stats_micro_payout`( 0022 IN `p_yearmonth` INT 0023 ) 0024 BEGIN 0025 0026 delete from section_funding_stats where yearmonth = p_yearmonth; 0027 0028 INSERT INTO section_funding_stats 0029 SELECT yearmonth,section_id,section_name 0030 ,case when sum_affiliate_payout IS NOT NULL then (sum_support-sum_affiliate_payout) ELSE sum_support END AS sum_support 0031 ,sum_sponsor,sum_dls,sum_amount,sum_dls_payout, sum_amount_payout 0032 ,case when sum_affiliate_payout IS NOT NULL then ROUND((sum_support-sum_affiliate_payout)/sum_amount_payout,2) ELSE ROUND(sum_support/sum_amount_payout,2) END AS factor 0033 FROM ( 0034 SELECT p.yearmonth, p.section_id, se.name AS section_name 0035 ,(SELECT ROUND(SUM(ss.tier),2) AS sum_support FROM section_support_paypements ss 0036 JOIN support su2 ON su2.id = ss.support_id 0037 WHERE p.section_id = ss.section_id 0038 AND ss.yearmonth = p_yearmonth 0039 GROUP BY p.section_id 0040 ) AS sum_support 0041 ,affiliate_payout.sum_affiliate_payout 0042 ,(SELECT SUM(sp.amount * (ssp.percent_of_sponsoring/100)) AS sum_sponsor FROM sponsor sp 0043 LEFT JOIN section_sponsor ssp ON ssp.sponsor_id = sp.sponsor_id 0044 WHERE sp.is_active = 1 0045 AND ssp.section_id = p.section_id) AS sum_sponsor 0046 , SUM(p.num_plings) AS sum_dls 0047 , ROUND(SUM(p.credits_plings)/100,2) AS sum_amount 0048 , p3.num_downloads AS sum_dls_payout, p3.amount AS sum_amount_payout 0049 FROM micro_payout p 0050 JOIN section se ON se.section_id = p.section_id 0051 LEFT JOIN ( 0052 SELECT yearmonth, section_id, SUM(num_downloads) AS num_downloads, round(SUM(amount),2) AS amount FROM ( 0053 SELECT m.yearmonth, `m`.`member_id`,`m`.`paypal_mail`, `m`.section_id, sum(`m`.`num_plings`) AS `num_downloads`,sum(`m`.`credits_plings`)/100 AS `amount` 0054 from `micro_payout` `m` 0055 where ((`m`.`yearmonth` = p_yearmonth) 0056 and (length(`m`.`paypal_mail`) > 0) and (`m`.`paypal_mail` regexp '^[A-Z0-9._%-]+@[A-Z0-9.-]+.[A-Z]{2,4}$') and (`m`.`is_license_missing` = 0) and (`m`.`is_source_missing` = 0) and (`m`.`is_pling_excluded` = 0) and (`m`.`is_member_pling_excluded` = 0)) 0057 group by m.yearmonth, `m`.`member_id`,`m`.`paypal_mail`, `m`.section_id 0058 #HAVING sum(`m`.`probably_payout_amount`) >= 1 0059 ) A GROUP BY yearmonth, section_id 0060 ) p3 ON p3.yearmonth = p.yearmonth AND p3.section_id = p.section_id 0061 LEFT JOIN (SELECT yearmonth, section_id, round(sum_donations * affiliate_percent,2) AS sum_affiliate_payout FROM ( 0062 SELECT yearmonth, section_id, COUNT(supporter_member_id) AS count_supporters, SUM(sum_donations) AS sum_donations, 0063 (SELECT percent FROM affiliate_config WHERE A2.yearmonth >= active_from AND A2.yearmonth <= active_until) AS affiliate_percent 0064 FROM ( 0065 SELECT 0066 ssp2.`yearmonth`, ssp2.section_id, su3.member_id AS supporter_member_id 0067 ,SUM(ssp2.tier) AS sum_donations 0068 from section_support_paypements ssp2 0069 JOIN section_support ss2 ON ss2.section_support_id = ssp2.section_support_id 0070 JOIN support su3 ON su3.id = ss2.support_id 0071 JOIN project pr3 ON pr3.project_id = ss2.project_id 0072 WHERE 0073 ssp2.`yearmonth` = p_yearmonth 0074 GROUP BY ssp2.`yearmonth`, ssp2.section_id, su3.member_id 0075 0076 ) A2 0077 GROUP BY A2.yearmonth, A2.section_id 0078 ) A3) affiliate_payout ON affiliate_payout.yearmonth = p.yearmonth AND affiliate_payout.section_id = p.section_id 0079 WHERE p.yearmonth = p_yearmonth 0080 AND p.section_id IS NOT null 0081 GROUP BY p.section_id 0082 ) AA 0083 ; 0084 END$$ 0085 DELIMITER ;