File indexing completed on 2024-04-21 06:00:37
0001 0002 #Table with monthly support payments 0003 CREATE TABLE `section_support_paypements` ( 0004 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, 0005 `yearmonth` INT UNSIGNED NOT NULL, 0006 `section_support_id` INT UNSIGNED NOT NULL, 0007 `support_id` INT UNSIGNED NOT NULL, 0008 `section_id` INT UNSIGNED NOT NULL, 0009 `amount` FLOAT(10,2) UNSIGNED NOT NULL, 0010 `tier` FLOAT(10,2) UNSIGNED NOT NULL, 0011 `period` VARCHAR(50) NOT NULL, 0012 `period_frequenzy` INT UNSIGNED NOT NULL, 0013 PRIMARY KEY (`id`) 0014 ) 0015 COLLATE='latin1_swedish_ci' 0016 ; 0017 0018 TRUNCATE TABLE section_support_paypements; 0019 0020 0021 #Subscriptions 0022 0023 #201901 0024 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0025 0026 SELECT 201901 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0027 JOIN support su ON su.id = s.support_id 0028 WHERE s.is_active = 1 0029 AND su.status_id = 2 0030 AND su.type_id = 1 0031 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201901' 0032 ; 0033 0034 #201902 0035 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0036 0037 SELECT 201902 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0038 JOIN support su ON su.id = s.support_id 0039 WHERE s.is_active = 1 0040 AND su.status_id = 2 0041 AND su.type_id = 1 0042 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201902' 0043 ; 0044 0045 #201903 0046 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0047 0048 SELECT 201903 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0049 JOIN support su ON su.id = s.support_id 0050 WHERE s.is_active = 1 0051 AND su.status_id = 2 0052 AND su.type_id = 1 0053 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201903' 0054 ; 0055 0056 #201904 0057 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0058 0059 SELECT 201904 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0060 JOIN support su ON su.id = s.support_id 0061 WHERE s.is_active = 1 0062 AND su.status_id = 2 0063 AND su.type_id = 1 0064 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201904' 0065 ; 0066 0067 #201905 0068 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0069 0070 SELECT 201905 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0071 JOIN support su ON su.id = s.support_id 0072 WHERE s.is_active = 1 0073 AND su.status_id = 2 0074 AND su.type_id = 1 0075 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201905' 0076 ; 0077 0078 #201906 0079 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0080 0081 SELECT 201906 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0082 JOIN support su ON su.id = s.support_id 0083 WHERE s.is_active = 1 0084 AND su.status_id = 2 0085 AND su.type_id = 1 0086 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201906' 0087 ; 0088 0089 #201907 0090 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0091 0092 SELECT 201907 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0093 JOIN support su ON su.id = s.support_id 0094 WHERE s.is_active = 1 0095 AND su.status_id = 2 0096 AND su.type_id = 1 0097 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201907' 0098 ; 0099 0100 #201908 0101 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0102 0103 SELECT 201908 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0104 JOIN support su ON su.id = s.support_id 0105 WHERE s.is_active = 1 0106 AND su.status_id = 2 0107 AND su.type_id = 1 0108 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201908' 0109 #AND su.id = 378 0110 ; 0111 0112 #SELECT * FROM section_support_paypements p 0113 #WHERE p.support_id = 378 0114 #; 0115 0116 0117 0118 0119 0120 0121 0122 0123 0124 0125 0126 0127 0128 0129 0130 #One-Time Payments 0131 0132 #201801 0133 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0134 0135 SELECT 201801 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0136 JOIN support su ON su.id = s.support_id 0137 WHERE s.is_active = 1 0138 AND su.status_id = 2 0139 AND su.type_id = 0 0140 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201801' 0141 ; 0142 0143 #201802 0144 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0145 0146 SELECT 201802 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0147 JOIN support su ON su.id = s.support_id 0148 WHERE s.is_active = 1 0149 AND su.status_id = 2 0150 AND su.type_id = 0 0151 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201802' 0152 ; 0153 0154 #201803 0155 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0156 0157 SELECT 201803 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0158 JOIN support su ON su.id = s.support_id 0159 WHERE s.is_active = 1 0160 AND su.status_id = 2 0161 AND su.type_id = 0 0162 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201803' 0163 ; 0164 0165 #201804 0166 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0167 0168 SELECT 201804 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0169 JOIN support su ON su.id = s.support_id 0170 WHERE s.is_active = 1 0171 AND su.status_id = 2 0172 AND su.type_id = 0 0173 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201804' 0174 ; 0175 0176 #201805 0177 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0178 0179 SELECT 201805 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0180 JOIN support su ON su.id = s.support_id 0181 WHERE s.is_active = 1 0182 AND su.status_id = 2 0183 AND su.type_id = 0 0184 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201805' 0185 ; 0186 0187 #201806 0188 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0189 0190 SELECT 201806 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0191 JOIN support su ON su.id = s.support_id 0192 WHERE s.is_active = 1 0193 AND su.status_id = 2 0194 AND su.type_id = 0 0195 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201806' 0196 ; 0197 0198 #201807 0199 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0200 0201 SELECT 201807 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0202 JOIN support su ON su.id = s.support_id 0203 WHERE s.is_active = 1 0204 AND su.status_id = 2 0205 AND su.type_id = 0 0206 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201807' 0207 ; 0208 0209 #201808 0210 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0211 0212 SELECT 201808 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0213 JOIN support su ON su.id = s.support_id 0214 WHERE s.is_active = 1 0215 AND su.status_id = 2 0216 AND su.type_id = 0 0217 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201808' 0218 ; 0219 0220 #201809 0221 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0222 0223 SELECT 201809 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0224 JOIN support su ON su.id = s.support_id 0225 WHERE s.is_active = 1 0226 AND su.status_id = 2 0227 AND su.type_id = 0 0228 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201809' 0229 ; 0230 0231 #201810 0232 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0233 0234 SELECT 201810 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0235 JOIN support su ON su.id = s.support_id 0236 WHERE s.is_active = 1 0237 AND su.status_id = 2 0238 AND su.type_id = 0 0239 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201810' 0240 ; 0241 0242 #201811 0243 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0244 0245 SELECT 201811 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0246 JOIN support su ON su.id = s.support_id 0247 WHERE s.is_active = 1 0248 AND su.status_id = 2 0249 AND su.type_id = 0 0250 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201811' 0251 ; 0252 0253 #201812 0254 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0255 0256 SELECT 201812 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0257 JOIN support su ON su.id = s.support_id 0258 WHERE s.is_active = 1 0259 AND su.status_id = 2 0260 AND su.type_id = 0 0261 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201812' 0262 ; 0263 0264 #201901 0265 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0266 0267 SELECT 201901 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0268 JOIN support su ON su.id = s.support_id 0269 WHERE s.is_active = 1 0270 AND su.status_id = 2 0271 AND su.type_id = 0 0272 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201901' 0273 AND DATE_FORMAT(su.active_time,'%Y%m') > '201801' 0274 ; 0275 0276 #201902 0277 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0278 0279 SELECT 201902 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0280 JOIN support su ON su.id = s.support_id 0281 WHERE s.is_active = 1 0282 AND su.status_id = 2 0283 AND su.type_id = 0 0284 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201902' 0285 AND DATE_FORMAT(su.active_time,'%Y%m') > '201802' 0286 ; 0287 0288 #201903 0289 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0290 0291 SELECT 201903 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0292 JOIN support su ON su.id = s.support_id 0293 WHERE s.is_active = 1 0294 AND su.status_id = 2 0295 AND su.type_id = 0 0296 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201903' 0297 AND DATE_FORMAT(su.active_time,'%Y%m') >= '201803' 0298 ; 0299 0300 #201904 0301 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0302 0303 SELECT 201904 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0304 JOIN support su ON su.id = s.support_id 0305 WHERE s.is_active = 1 0306 AND su.status_id = 2 0307 AND su.type_id = 0 0308 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201904' 0309 AND DATE_FORMAT(su.active_time,'%Y%m') > '201804' 0310 ; 0311 0312 #201905 0313 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0314 0315 SELECT 201905 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0316 JOIN support su ON su.id = s.support_id 0317 WHERE s.is_active = 1 0318 AND su.status_id = 2 0319 AND su.type_id = 0 0320 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201905' 0321 AND DATE_FORMAT(su.active_time,'%Y%m') > '201805' 0322 ; 0323 0324 #201906 0325 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0326 0327 SELECT 201906 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0328 JOIN support su ON su.id = s.support_id 0329 WHERE s.is_active = 1 0330 AND su.status_id = 2 0331 AND su.type_id = 0 0332 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201906' 0333 AND DATE_FORMAT(su.active_time,'%Y%m') > '201806' 0334 ; 0335 0336 #201907 0337 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0338 0339 SELECT 201907 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0340 JOIN support su ON su.id = s.support_id 0341 WHERE s.is_active = 1 0342 AND su.status_id = 2 0343 AND su.type_id = 0 0344 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201907' 0345 AND DATE_FORMAT(su.active_time,'%Y%m') > '201807' 0346 ; 0347 0348 #201908 0349 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0350 0351 SELECT 201908 AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0352 JOIN support su ON su.id = s.support_id 0353 WHERE s.is_active = 1 0354 AND su.status_id = 2 0355 AND su.type_id = 0 0356 AND DATE_FORMAT(su.active_time,'%Y%m') <= '201908' 0357 AND DATE_FORMAT(su.active_time,'%Y%m') > '201808' 0358 ; 0359 0360 0361 0362 0363 0364 0365 0366 #table with all income and payouts per month and section factor 0367 CREATE TABLE `section_funding_stats` ( 0368 `yearmonth` INT(6) NULL DEFAULT NULL, 0369 `section_id` INT(11) NULL DEFAULT '0', 0370 `section_name` VARCHAR(50) NULL, 0371 `sum_support` DOUBLE(19,2) NULL DEFAULT NULL, 0372 `sum_sponsor` DOUBLE NULL DEFAULT NULL, 0373 `sum_dls` DECIMAL(42,0) NULL DEFAULT NULL, 0374 `sum_amount` DECIMAL(46,2) NULL DEFAULT NULL, 0375 `sum_dls_payout` DECIMAL(64,0) NULL DEFAULT NULL, 0376 `sum_amount_payout` DECIMAL(65,2) NULL DEFAULT NULL, 0377 `factor` DOUBLE(19,2) NULL DEFAULT NULL 0378 ) 0379 COLLATE='latin1_swedish_ci' 0380 ENGINE=InnoDB 0381 ; 0382 0383 0384 ALTER TABLE `member_dl_plings` 0385 ADD COLUMN `section_id` INT NULL DEFAULT NULL AFTER `updated_at`, 0386 ADD COLUMN `section_payout_factor` DECIMAL(3,2) NULL DEFAULT 1.00 AFTER `section_id`; 0387 0388 0389 0390 DROP PROCEDURE `generate_section_funding_stats`; 0391 0392 DELIMITER $$ 0393 CREATE PROCEDURE `generate_section_funding_stats`( 0394 IN `p_yearmonth` INT 0395 ) 0396 BEGIN 0397 0398 delete from section_funding_stats where yearmonth = p_yearmonth; 0399 0400 INSERT INTO section_funding_stats 0401 0402 SELECT *, case when sum_support < sum_amount_payout then ROUND(sum_support/sum_amount_payout,2) ELSE 1 END AS factor 0403 FROM ( 0404 0405 SELECT p.yearmonth, s.section_id, s.name AS section_name 0406 ,(SELECT ROUND(SUM(ss.tier),2) AS sum_support FROM section_support_paypements ss 0407 JOIN support su2 ON su2.id = ss.support_id 0408 WHERE s.section_id = ss.section_id 0409 AND ss.yearmonth = p_yearmonth 0410 GROUP BY ss.section_id 0411 ) AS sum_support 0412 ,(SELECT SUM(sp.amount * (ssp.percent_of_sponsoring/100)) AS sum_sponsor FROM sponsor sp 0413 LEFT JOIN section_sponsor ssp ON ssp.sponsor_id = sp.sponsor_id 0414 WHERE sp.is_active = 1 0415 AND ssp.section_id = s.section_id) AS sum_sponsor 0416 , SUM(p.num_downloads) AS sum_dls 0417 , ROUND(SUM(p.probably_payout_amount),2) AS sum_amount 0418 , p3.num_downloads AS sum_dls_payout, p3.amount AS sum_amount_payout 0419 FROM member_dl_plings p 0420 LEFT JOIN section_category sc ON sc.project_category_id = p.project_category_id 0421 LEFT JOIN section s ON s.section_id = sc.section_id 0422 LEFT JOIN ( 0423 SELECT yearmonth, section_id, SUM(num_downloads) AS num_downloads, SUM(amount) AS amount FROM ( 0424 SELECT m.yearmonth, `m`.`member_id`,`m`.`paypal_mail`, s.section_id, sum(`m`.`num_downloads`) AS `num_downloads`,round(sum(`m`.`probably_payout_amount`),2) AS `amount` 0425 from `member_dl_plings` `m` 0426 LEFT JOIN section_category sc ON sc.project_category_id = m.project_category_id 0427 LEFT JOIN section s ON s.section_id = sc.section_id 0428 where ((`m`.`yearmonth` = p_yearmonth) 0429 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)) 0430 group by m.yearmonth, `m`.`member_id`,`m`.`paypal_mail`, s.section_id 0431 HAVING sum(`m`.`probably_payout_amount`) >= 1 0432 ) A GROUP BY yearmonth, section_id 0433 ) p3 ON p3.yearmonth = p.yearmonth AND p3.section_id = s.section_id 0434 WHERE p.yearmonth = p_yearmonth 0435 AND sc.section_id IS NOT null 0436 GROUP BY s.section_id 0437 ) AA 0438 ; 0439 0440 END$$ 0441 DELIMITER ; 0442 0443 0444 0445 CALL `generate_section_funding_stats`('201704'); 0446 CALL `generate_section_funding_stats`('201705'); 0447 CALL `generate_section_funding_stats`('201706'); 0448 CALL `generate_section_funding_stats`('201707'); 0449 CALL `generate_section_funding_stats`('201708'); 0450 CALL `generate_section_funding_stats`('201709'); 0451 CALL `generate_section_funding_stats`('201710'); 0452 CALL `generate_section_funding_stats`('201711'); 0453 CALL `generate_section_funding_stats`('201712'); 0454 0455 CALL `generate_section_funding_stats`('201801'); 0456 CALL `generate_section_funding_stats`('201802'); 0457 CALL `generate_section_funding_stats`('201803'); 0458 CALL `generate_section_funding_stats`('201804'); 0459 CALL `generate_section_funding_stats`('201805'); 0460 CALL `generate_section_funding_stats`('201806'); 0461 CALL `generate_section_funding_stats`('201807'); 0462 CALL `generate_section_funding_stats`('201808'); 0463 CALL `generate_section_funding_stats`('201809'); 0464 CALL `generate_section_funding_stats`('201810'); 0465 CALL `generate_section_funding_stats`('201811'); 0466 CALL `generate_section_funding_stats`('201812'); 0467 0468 CALL `generate_section_funding_stats`('201901'); 0469 CALL `generate_section_funding_stats`('201902'); 0470 CALL `generate_section_funding_stats`('201903'); 0471 CALL `generate_section_funding_stats`('201904'); 0472 CALL `generate_section_funding_stats`('201905'); 0473 CALL `generate_section_funding_stats`('201906'); 0474 CALL `generate_section_funding_stats`('201907'); 0475 CALL `generate_section_funding_stats`('201908'); 0476 CALL `generate_section_funding_stats`('201909'); 0477 CALL `generate_section_funding_stats`('201910'); 0478 CALL `generate_section_funding_stats`('201911'); 0479 CALL `generate_section_funding_stats`('201912'); 0480 0481 #SELECT * FROM section_funding_stats p 0482 #WHERE p.yearmonth = 201901 0483 #; 0484 0485 #Update section factor for current month 0486 UPDATE member_dl_plings p 0487 JOIN section_category sc ON sc.project_category_id = p.project_category_id 0488 SET p.section_id = sc.section_id 0489 WHERE p.yearmonth = DATE_FORMAT(NOW(),'%Y%m'); 0490 0491 CALL `generate_section_funding_stats`(DATE_FORMAT(NOW(),'%Y%m')); 0492 0493 UPDATE member_dl_plings p 0494 JOIN section_funding_stats sfs ON sfs.yearmonth = p.yearmonth AND sfs.section_id = p.section_id 0495 SET p.section_payout_factor = sfs.factor 0496 WHERE p.yearmonth = DATE_FORMAT(NOW(),'%Y%m'); 0497 0498 0499 0500 #Update section factor for last month 0501 UPDATE member_dl_plings p 0502 JOIN section_category sc ON sc.project_category_id = p.project_category_id 0503 SET p.section_id = sc.section_id 0504 WHERE p.yearmonth = DATE_FORMAT(NOW() - INTERVAL 1 MONTH,'%Y%m'); 0505 0506 CALL `generate_section_funding_stats`(DATE_FORMAT(NOW() - INTERVAL 1 MONTH,'%Y%m')); 0507 0508 UPDATE member_dl_plings p 0509 JOIN section_funding_stats sfs ON sfs.yearmonth = p.yearmonth AND sfs.section_id = p.section_id 0510 SET p.section_payout_factor = sfs.factor 0511 WHERE p.yearmonth = DATE_FORMAT(NOW() - INTERVAL 1 MONTH,'%Y%m'); 0512 0513 0514 0515 drop event e_update_member_dl_plings_current_month; 0516 0517 DELIMITER $$ 0518 CREATE DEVENT `e_update_member_dl_plings_current_month` 0519 ON SCHEDULE 0520 EVERY 1 DAY STARTS '2018-06-07 01:00:00' 0521 ON COMPLETION NOT PRESERVE 0522 ENABLE 0523 COMMENT '' 0524 DO BEGIN 0525 0526 #Generate tmp table for active projects 0527 DROP TABLE IF EXISTS tmp_project_for_member_dl_plings; 0528 CREATE TABLE tmp_project_for_member_dl_plings AS 0529 select * from project p where p.ppload_collection_id is not null and p.type_id = 1 and p.`status` = 100; 0530 0531 #ppload_collection_id from char to int 0532 ALTER TABLE `tmp_project_for_member_dl_plings` 0533 CHANGE COLUMN `ppload_collection_id` `ppload_collection_id` INT NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `embed_code`; 0534 0535 #add index 0536 ALTER TABLE `tmp_project_for_member_dl_plings` ADD INDEX `idx_ppload` (`ppload_collection_id`); 0537 ALTER TABLE `tmp_project_for_member_dl_plings` ADD INDEX `idx_pk` (`project_id`); 0538 0539 #fill tmp member_dl_plings table 0540 DROP TABLE IF EXISTS tmp_member_dl_plings; 0541 0542 CREATE TABLE tmp_member_dl_plings LIKE member_dl_plings; 0543 0544 INSERT INTO tmp_member_dl_plings 0545 (SELECT * FROM stat_member_dl_curent_month); 0546 0547 #delete plings from actual month 0548 DELETE FROM member_dl_plings 0549 WHERE yearmonth = (DATE_FORMAT(NOW(),'%Y%m')); 0550 0551 #insert ping for this month from tmp member_dl_plings table 0552 INSERT INTO member_dl_plings 0553 (SELECT * FROM tmp_member_dl_plings); 0554 0555 #remove tmp member_dl_plings table 0556 DROP TABLE tmp_member_dl_plings; 0557 0558 #Update section factor for current month 0559 UPDATE member_dl_plings p 0560 JOIN section_category sc ON sc.project_category_id = p.project_category_id 0561 SET p.section_id = sc.section_id 0562 WHERE p.yearmonth = DATE_FORMAT(NOW(),'%Y%m'); 0563 0564 0565 #Update support payments for the current month 0566 DELETE FROM section_support_paypements WHERE yearmonth = DATE_FORMAT(NOW(),'%Y%m'); 0567 0568 #One-Time-Supports 0569 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0570 0571 SELECT DATE_FORMAT(NOW(),'%Y%m') AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0572 JOIN support su ON su.id = s.support_id 0573 WHERE s.is_active = 1 0574 AND su.status_id = 2 0575 AND su.type_id = 0 0576 AND DATE_FORMAT(su.active_time,'%Y%m') <= DATE_FORMAT(NOW(),'%Y%m') 0577 AND DATE_FORMAT(su.active_time,'%Y%m') > DATE_FORMAT(NOW() - INTERVAL 12 MONTH,'%Y%m') 0578 ; 0579 0580 #Subscriptions 0581 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0582 0583 SELECT DATE_FORMAT(NOW(),'%Y%m') AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0584 JOIN support su ON su.id = s.support_id 0585 WHERE s.is_active = 1 0586 AND su.status_id = 2 0587 AND su.type_id = 1 0588 AND DATE_FORMAT(su.active_time,'%Y%m') <= DATE_FORMAT(NOW(),'%Y%m') 0589 ; 0590 0591 #Canceled Yearly Subscriptions 0592 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0593 0594 SELECT DATE_FORMAT(NOW(),'%Y%m') AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0595 JOIN support su ON su.id = s.support_id 0596 WHERE s.is_active = 1 0597 AND su.status_id = 99 0598 AND su.type_id = 1 0599 AND su.period = 'Y' 0600 AND DATE_FORMAT(su.active_time,'%Y%m') <= DATE_FORMAT(NOW(),'%Y%m') 0601 AND DATE_FORMAT(su.active_time,'%Y%m') > DATE_FORMAT(NOW() - INTERVAL 12 MONTH,'%Y%m') 0602 ; 0603 0604 0605 #Canceled Monthly Subscriptions 0606 INSERT INTO section_support_paypements (yearmonth, section_support_id, support_id, section_id, amount, tier, period, period_frequenzy) 0607 0608 SELECT DATE_FORMAT(NOW(),'%Y%m') AS yearmonth, s.section_support_id, s.support_id, s.section_id, s.amount, s.tier, s.period, s.period_frequency FROM section_support s 0609 JOIN support su ON su.id = s.support_id 0610 WHERE s.is_active = 1 0611 AND su.status_id = 99 0612 AND su.type_id = 1 0613 AND su.period = 'M' 0614 AND DATE_FORMAT(su.active_time,'%Y%m') <= DATE_FORMAT(NOW(),'%Y%m') 0615 AND DATE_FORMAT(su.active_time,'%Y%m') > DATE_FORMAT(NOW() - INTERVAL 1 MONTH,'%Y%m') 0616 ; 0617 0618 0619 CALL `generate_section_funding_stats`(DATE_FORMAT(NOW(),'%Y%m')); 0620 0621 UPDATE member_dl_plings p 0622 JOIN section_funding_stats sfs ON sfs.yearmonth = p.yearmonth AND sfs.section_id = p.section_id 0623 SET p.section_payout_factor = sfs.factor 0624 WHERE p.yearmonth = DATE_FORMAT(NOW(),'%Y%m'); 0625 0626 0627 0628 #fill tmp member_dl_plings_nouk table 0629 DROP TABLE IF EXISTS tmp_member_dl_plings_nouk; 0630 0631 CREATE TABLE tmp_member_dl_plings_nouk LIKE member_dl_plings_nouk; 0632 0633 INSERT INTO tmp_member_dl_plings_nouk 0634 (SELECT * FROM stat_member_dl_curent_month_nouk); 0635 0636 #delete plings from actual month 0637 DELETE FROM member_dl_plings_nouk 0638 WHERE yearmonth = (DATE_FORMAT(NOW(),'%Y%m')); 0639 0640 #insert ping for this month from tmp member_dl_plings table 0641 INSERT INTO member_dl_plings_nouk 0642 (SELECT * FROM tmp_member_dl_plings_nouk); 0643 0644 #remove tmp member_dl_plings table 0645 DROP TABLE tmp_member_dl_plings_nouk; 0646 0647 END$$ 0648 DELIMITER ; 0649 0650 0651 drop event e_update_member_dl_plings_last_month; 0652 0653 DELIMITER $$ 0654 CREATE EVENT `e_update_member_dl_plings_last_month` 0655 ON SCHEDULE 0656 EVERY 1 MONTH STARTS '2017-12-01 01:00:00' 0657 ON COMPLETION NOT PRESERVE 0658 ENABLE 0659 COMMENT '' 0660 DO BEGIN 0661 0662 0663 0664 DELETE FROM member_dl_plings 0665 0666 WHERE yearmonth = (DATE_FORMAT(NOW() - INTERVAL 1 MONTH,'%Y%m')); 0667 0668 0669 0670 INSERT INTO member_dl_plings 0671 0672 (SELECT * FROM stat_member_dl_last_month); 0673 0674 0675 #Update section factor for last month 0676 UPDATE member_dl_plings p 0677 JOIN section_category sc ON sc.project_category_id = p.project_category_id 0678 SET p.section_id = sc.section_id 0679 WHERE p.yearmonth = DATE_FORMAT(NOW() - INTERVAL 1 MONTH,'%Y%m'); 0680 0681 CALL `generate_section_funding_stats`(DATE_FORMAT(NOW() - INTERVAL 1 MONTH,'%Y%m')); 0682 0683 UPDATE member_dl_plings p 0684 JOIN section_funding_stats sfs ON sfs.yearmonth = p.yearmonth AND sfs.section_id = p.section_id 0685 SET p.section_payout_factor = sfs.factor 0686 WHERE p.yearmonth = DATE_FORMAT(NOW() - INTERVAL 1 MONTH,'%Y%m'); 0687 0688 0689 0690 #fill tmp member_dl_plings_nouk table 0691 DROP TABLE IF EXISTS tmp_member_dl_plings_nouk; 0692 0693 CREATE TABLE tmp_member_dl_plings_nouk LIKE member_dl_plings_nouk; 0694 0695 INSERT INTO tmp_member_dl_plings_nouk 0696 (SELECT * FROM stat_member_dl_last_month_nouk); 0697 0698 #delete plings from actual month 0699 DELETE FROM member_dl_plings_nouk 0700 WHERE yearmonth = (DATE_FORMAT(NOW() - INTERVAL 1 MONTH,'%Y%m')); 0701 0702 #insert ping for this month from tmp member_dl_plings table 0703 INSERT INTO member_dl_plings_nouk 0704 (SELECT * FROM tmp_member_dl_plings_nouk); 0705 0706 #remove tmp member_dl_plings table 0707 DROP TABLE tmp_member_dl_plings_nouk; 0708 0709 0710 END$$ 0711 DELIMITER ; 0712 0713 0714 0715 0716