File indexing completed on 2024-04-21 06:00:37
0001 DROP TABLE IF EXISTS micro_payout; 0002 0003 CREATE TABLE `micro_payout` ( 0004 `yearmonth` INT(6) NOT NULL, 0005 `type` INT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0 = download, 1 = mediaviews', 0006 `project_id` INT(11) NOT NULL, 0007 `project_category_id` INT(11) NOT NULL, 0008 `collection_id` INT(11) NOT NULL, 0009 `file_id` INT(11) NOT NULL, 0010 `member_id` INT(11) NOT NULL, 0011 `mail` VARCHAR(255) NULL DEFAULT NULL, 0012 `paypal_mail` VARCHAR(255) NULL DEFAULT NULL, 0013 `num_plings` BIGINT(21) NULL DEFAULT NULL, 0014 `category_pling_factor` DECIMAL(3,2) NOT NULL DEFAULT '0.00', 0015 `credits_plings` DECIMAL(11,2) NULL DEFAULT NULL, 0016 `section_id` INT(11) NULL DEFAULT NULL, 0017 `section_payout_factor` DECIMAL(3,2) NULL DEFAULT '1.00', 0018 `credits_section` DECIMAL(11,2) NULL DEFAULT NULL, 0019 `is_license_missing` INT(1) UNSIGNED NULL DEFAULT '0', 0020 `is_source_missing` INT(1) UNSIGNED NULL DEFAULT '0', 0021 `is_pling_excluded` INT(1) UNSIGNED NULL DEFAULT '0', 0022 `is_member_pling_excluded` INT(1) UNSIGNED NULL DEFAULT '0', 0023 `created_at` DATETIME NULL DEFAULT NULL, 0024 `updated_at` DATETIME NULL DEFAULT NULL, 0025 UNIQUE INDEX `uk_month_proj` (`yearmonth`,`type` , `member_id`, `project_id`, `file_id`), 0026 INDEX `idx_yearmonth` (`yearmonth`) 0027 ) 0028 COLLATE='latin1_swedish_ci' 0029 ENGINE=InnoDB 0030 ; 0031 0032 DROP TABLE IF EXISTS micro_payout_types; 0033 CREATE TABLE `micro_payout_types` ( 0034 `type_id` INT(10) UNSIGNED NOT NULL, 0035 `name` VARCHAR(50) NULL DEFAULT NULL, 0036 `description` VARCHAR(255) NULL DEFAULT NULL, 0037 PRIMARY KEY (`type_id`) 0038 ) 0039 ENGINE=InnoDB 0040 ; 0041 0042 INSERT INTO micro_payout_types (`type_id`,`name`,`description`) VALUES (0,'downloads','Downlaods'); 0043 INSERT INTO micro_payout_types (`type_id`,`name`,`description`) VALUES (1,'mediaviews','Media-Views'); 0044 0045 0046 0047 0048 0049 0050 0051 0052 #INSERT 0053 0054 #1. Downloads 0055 DROP VIEW stat_micro_payout_dl_curent_month; 0056 0057 CREATE VIEW stat_micro_payout_dl_curent_month AS 0058 0059 SELECT 0060 date_format(`d`.`downloaded_timestamp`,'%Y%m') AS `yearmonth` 0061 ,0 AS `type` 0062 ,`p`.`project_id` AS `project_id` 0063 ,`p`.`project_category_id` AS `project_category_id` 0064 ,p.ppload_collection_id AS `collection_id` 0065 ,`d`.`file_id` 0066 ,`d`.`owner_id` AS `member_id` 0067 ,`m`.`mail` AS `mail` 0068 ,`m`.`paypal_mail` AS `paypal_mail` 0069 ,count(`d`.`id`) AS `num_plings` 0070 ,`c`.`dl_pling_factor` AS `category_pling_factor` 0071 ,(count(`d`.`id`) * `c`.`dl_pling_factor`) AS `credits_plings` 0072 ,0 as section_id 0073 ,1.00 as section_payout_factor #case when sfs.factor IS NULL then 1.00 ELSE sfs.factor end AS section_payout_factor 0074 ,(count(`d`.`id`) * `c`.`dl_pling_factor`) AS credits_section# (count(`d`.`id`) * `c`.`dl_pling_factor` * (case when sfs.factor IS NULL then 1.00 ELSE sfs.factor end)) AS credits_section 0075 ,(case when isnull(`tag`.`tag_item_id`) then 1 else 0 end) AS `is_license_missing` 0076 ,(case when (((`c`.`source_required` = 1) and (`p`.`source_url` is not null) and (length(`p`.`source_url`) > 0)) or (`c`.`source_required` = 0)) then 0 else 1 end) AS `is_source_missing` 0077 ,`p`.`pling_excluded` AS `is_pling_excluded` 0078 ,`m`.`pling_excluded` AS `is_member_pling_excluded` 0079 0080 ,NOW() AS `created_at` 0081 ,NULL AS `updated_at` 0082 from ((((`ppload`.`ppload_files_downloaded_unique` `d` 0083 join `pling`.`member` `m` on(((`m`.`member_id` = `d`.`owner_id`) and (`m`.`is_active` = 1)))) 0084 join `pling`.`tmp_project_for_micro_payout` `p` on((`p`.`ppload_collection_id` = `d`.`collection_id`))) 0085 join `pling`.`project_category` `c` on((`c`.`project_category_id` = `p`.`project_category_id`))) 0086 #left JOIN `pling`.section_category sc ON sc.project_category_id = p.project_category_id 0087 #left JOIN `pling`.section_funding_stats sfs ON sfs.section_id = sc.section_id AND sfs.yearmonth = DATE_FORMAT(`d`.`downloaded_timestamp`,'%Y%m') 0088 left join `pling`.`tag_object` `tag` on(((`tag`.`tag_type_id` = 1) and (`tag`.`tag_group_id` = 7) and (`tag`.`tag_object_id` = `p`.`project_id`)))) 0089 where (`d`.`downloaded_timestamp` >= concat(left(now(),7),'-01 00:00:00')) 0090 GROUP BY DATE_FORMAT(`d`.`downloaded_timestamp`,'%Y%m'),`d`.`owner_id`,`p`.`project_id`,p.ppload_collection_id,`d`.`file_id` 0091 ; 0092 0093 0094 0095 0096 DROP VIEW stat_micro_payout_dl_last_month; 0097 CREATE VIEW stat_micro_payout_dl_last_month AS 0098 0099 SELECT 0100 date_format(`d`.`downloaded_timestamp`,'%Y%m') AS `yearmonth` 0101 ,0 AS `type` 0102 ,`p`.`project_id` AS `project_id` 0103 ,`p`.`project_category_id` AS `project_category_id` 0104 ,p.ppload_collection_id AS `collection_id` 0105 ,`d`.`file_id` 0106 ,`d`.`owner_id` AS `member_id` 0107 ,`m`.`mail` AS `mail` 0108 ,`m`.`paypal_mail` AS `paypal_mail` 0109 ,count(`d`.`id`) AS `num_plings` 0110 ,`c`.`dl_pling_factor` AS `category_pling_factor` 0111 ,(count(`d`.`id`) * `c`.`dl_pling_factor`) AS `credits_plings` 0112 ,0 as section_id 0113 ,1.00 as section_payout_factor #case when sfs.factor IS NULL then 1.00 ELSE sfs.factor end AS section_payout_factor 0114 ,(count(`d`.`id`) * `c`.`dl_pling_factor`) AS credits_section# (count(`d`.`id`) * `c`.`dl_pling_factor` * (case when sfs.factor IS NULL then 1.00 ELSE sfs.factor end)) AS credits_section 0115 ,(case when isnull(`tag`.`tag_item_id`) then 1 else 0 end) AS `is_license_missing` 0116 ,(case when (((`c`.`source_required` = 1) and (`p`.`source_url` is not null) and (length(`p`.`source_url`) > 0)) or (`c`.`source_required` = 0)) then 0 else 1 end) AS `is_source_missing` 0117 ,`p`.`pling_excluded` AS `is_pling_excluded` 0118 ,`m`.`pling_excluded` AS `is_member_pling_excluded` 0119 0120 ,NOW() AS `created_at` 0121 ,NULL AS `updated_at` 0122 from ((((`ppload`.`ppload_files_downloaded_unique` `d` 0123 join `pling`.`member` `m` on(((`m`.`member_id` = `d`.`owner_id`) and (`m`.`is_active` = 1)))) 0124 join `pling`.`tmp_project_for_micro_payout` `p` on((`p`.`ppload_collection_id` = `d`.`collection_id`))) 0125 join `pling`.`project_category` `c` on((`c`.`project_category_id` = `p`.`project_category_id`))) 0126 #left JOIN `pling`.section_category sc ON sc.project_category_id = p.project_category_id 0127 #left JOIN `pling`.section_funding_stats sfs ON sfs.section_id = sc.section_id AND sfs.yearmonth = DATE_FORMAT(`d`.`downloaded_timestamp`,'%Y%m') 0128 left join `pling`.`tag_object` `tag` on(((`tag`.`tag_type_id` = 1) and (`tag`.`tag_group_id` = 7) and (`tag`.`tag_object_id` = `p`.`project_id`)))) 0129 where (`d`.`downloaded_timestamp` >= concat(left((now() - interval 1 month),7),'-01 00:00:00')) AND (`d`.`downloaded_timestamp` <= concat(left(now(),7),'-01 00:00:00')) 0130 GROUP BY DATE_FORMAT(`d`.`downloaded_timestamp`,'%Y%m'),`d`.`owner_id`,`p`.`project_id`,p.ppload_collection_id,`d`.`file_id` 0131 ; 0132 0133 0134 0135 0136 #Generate tmp table for active projects 0137 DROP TABLE IF EXISTS tmp_project_for_micro_payout; 0138 CREATE TABLE tmp_project_for_micro_payout AS 0139 select * from project p where p.ppload_collection_id is not null and p.type_id = 1 and p.`status` = 100; 0140 0141 #ppload_collection_id from char to int 0142 ALTER TABLE `tmp_project_for_micro_payout` 0143 CHANGE COLUMN `ppload_collection_id` `ppload_collection_id` INT NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `embed_code`; 0144 0145 #add index 0146 ALTER TABLE `tmp_project_for_micro_payout` ADD INDEX `idx_ppload` (`ppload_collection_id`); 0147 ALTER TABLE `tmp_project_for_micro_payout` ADD INDEX `idx_pk` (`project_id`); 0148 0149 #fill tmp micro_payout table 0150 DROP TABLE IF EXISTS tmp_micro_payout; 0151 0152 CREATE TABLE tmp_micro_payout LIKE micro_payout; 0153 0154 INSERT INTO tmp_micro_payout 0155 (SELECT * FROM stat_micro_payout_dl_curent_month); 0156 0157 #delete plings from actual month 0158 DELETE FROM micro_payout 0159 WHERE yearmonth = (DATE_FORMAT(NOW(),'%Y%m')) 0160 AND TYPE = 0; 0161 0162 #insert ping for this month from tmp member_dl_plings table 0163 INSERT INTO micro_payout 0164 (SELECT * FROM tmp_micro_payout); 0165 0166 #remove tmp micro_payout table 0167 DROP TABLE tmp_micro_payout; 0168 0169 0170 #SELECT * FROM micro_payout m 0171 UPDATE micro_payout m 0172 JOIN section_category sc ON sc.project_category_id = m.project_category_id 0173 JOIN section_funding_stats sfs ON sfs.yearmonth = m.yearmonth AND sfs.section_id = sc.section_id 0174 SET m.section_id = sc.section_id, 0175 m.section_payout_factor = sfs.factor, 0176 m.credits_section = m.credits_plings * sfs.factor 0177 WHERE m.yearmonth = DATE_FORMAT(NOW(),'%Y%m') 0178 AND m.`type` = 0; 0179 0180 0181 0182 SELECT * FROM micro_payout p 0183 WHERE p.yearmonth = 201909 0184 AND p.`type` = 0 0185 LIMIT 100; 0186 0187 0188 #2. Mediaviews 0189 0190 #CREATE VIEW stat_micro_payout_curent_month AS 0191 0192 DROP VIEW stat_micro_payout_mv_curent_month; 0193 CREATE VIEW stat_micro_payout_mv_curent_month AS 0194 SELECT 0195 DATE_FORMAT(mv.start_timestamp,'%Y%m') AS `yearmonth` 0196 ,1 AS `type` 0197 ,`p`.`project_id` AS `project_id` 0198 ,`p`.`project_category_id` AS `project_category_id` 0199 ,p.ppload_collection_id AS `collection_id` 0200 ,`mv`.`file_id` 0201 ,`pr`.`member_id` 0202 ,`m`.`mail` AS `mail` 0203 ,`m`.`paypal_mail` AS `paypal_mail` 0204 ,count(`mv`.media_view_id) AS `num_plings` 0205 ,`c`.`mv_pling_factor` AS `category_pling_factor` 0206 ,(count(`mv`.media_view_id) * `c`.`mv_pling_factor`) AS `credits_plings` 0207 ,0 as section_id 0208 ,1.0 AS section_payout_factor 0209 ,0 AS credits_section 0210 ,(case when isnull(`tag`.`tag_item_id`) then 1 else 0 end) AS `is_license_missing` 0211 ,(case when (((`c`.`source_required` = 1) and (`p`.`source_url` is not null) and (length(`p`.`source_url`) > 0)) or (`c`.`source_required` = 0)) then 0 else 1 end) AS `is_source_missing` 0212 ,`p`.`pling_excluded` AS `is_pling_excluded` 0213 ,`m`.`pling_excluded` AS `is_member_pling_excluded` 0214 0215 ,NOW() AS `created_at` 0216 ,NULL AS `updated_at` 0217 FROM media_views mv 0218 JOIN project pr ON pr.project_id = mv.project_id 0219 join `pling`.`member` `m` ON `m`.`member_id` = `pr`.`member_id` and `m`.`is_active` = 1 0220 join `pling`.`tmp_project_for_micro_payout` `p` ON `p`.`project_id` = `mv`.`project_id` 0221 join `pling`.`project_category` `c` ON `c`.`project_category_id` = `p`.`project_category_id` 0222 #left JOIN `pling`.section_category sc ON sc.project_category_id = p.project_category_id 0223 #left JOIN `pling`.section_funding_stats sfs ON sfs.section_id = sc.section_id AND sfs.yearmonth = DATE_FORMAT(mv.start_timestamp,'%Y%m') 0224 left join `pling`.`tag_object` `tag` ON (`tag`.`tag_type_id` = 1) and (`tag`.`tag_group_id` = 7) AND (`tag`.`tag_object_id` = `p`.`project_id`) 0225 WHERE mv.start_timestamp >= concat(left(now(),7),'-01 00:00:00') 0226 GROUP BY DATE_FORMAT(mv.start_timestamp,'%Y%m'),pr.member_id, mv.project_id,`p`.`project_category_id`,p.ppload_collection_id,`mv`.`file_id` 0227 ; 0228 0229 0230 DROP VIEW stat_micro_payout_mv_last_month; 0231 CREATE VIEW stat_micro_payout_mv_last_month AS 0232 SELECT 0233 DATE_FORMAT(mv.start_timestamp,'%Y%m') AS `yearmonth` 0234 ,1 AS `type` 0235 ,`p`.`project_id` AS `project_id` 0236 ,`p`.`project_category_id` AS `project_category_id` 0237 ,p.ppload_collection_id AS `collection_id` 0238 ,`mv`.`file_id` 0239 ,`mv`.`member_id` 0240 ,`m`.`mail` AS `mail` 0241 ,`m`.`paypal_mail` AS `paypal_mail` 0242 ,count(`mv`.media_view_id) AS `num_plings` 0243 ,`c`.`mv_pling_factor` AS `category_pling_factor` 0244 ,(count(`mv`.media_view_id) * `c`.`mv_pling_factor`) AS `credits_plings` 0245 ,0 as section_id 0246 ,1.0 AS section_payout_factor 0247 ,0 AS credits_section 0248 ,(case when isnull(`tag`.`tag_item_id`) then 1 else 0 end) AS `is_license_missing` 0249 ,(case when (((`c`.`source_required` = 1) and (`p`.`source_url` is not null) and (length(`p`.`source_url`) > 0)) or (`c`.`source_required` = 0)) then 0 else 1 end) AS `is_source_missing` 0250 ,`p`.`pling_excluded` AS `is_pling_excluded` 0251 ,`m`.`pling_excluded` AS `is_member_pling_excluded` 0252 0253 ,NOW() AS `created_at` 0254 ,NULL AS `updated_at` 0255 FROM media_views mv 0256 JOIN project pr ON pr.project_id = mv.project_id 0257 join `pling`.`member` `m` ON `m`.`member_id` = `pr`.`member_id` and `m`.`is_active` = 1 0258 join `pling`.`tmp_project_for_micro_payout` `p` ON `p`.`project_id` = `mv`.`project_id` 0259 join `pling`.`project_category` `c` ON `c`.`project_category_id` = `p`.`project_category_id` 0260 #left JOIN `pling`.section_category sc ON sc.project_category_id = p.project_category_id 0261 #left JOIN `pling`.section_funding_stats sfs ON sfs.section_id = sc.section_id AND sfs.yearmonth = DATE_FORMAT(mv.start_timestamp,'%Y%m') 0262 left join `pling`.`tag_object` `tag` ON (`tag`.`tag_type_id` = 1) and (`tag`.`tag_group_id` = 7) AND (`tag`.`tag_object_id` = `p`.`project_id`) 0263 WHERE (`mv`.start_timestamp >= concat(left((now() - interval 1 month),7),'-01 00:00:00')) and (`mv`.start_timestamp <= concat(left(now(),7),'-01 00:00:00')) 0264 GROUP BY DATE_FORMAT(mv.start_timestamp,'%Y%m'),pr.member_id, mv.project_id,`p`.`project_category_id`,p.ppload_collection_id,`mv`.`file_id` 0265 ; 0266 0267 #fill tmp micro_payout table 0268 DROP TABLE IF EXISTS tmp_micro_payout_mv; 0269 0270 CREATE TABLE tmp_micro_payout_mv LIKE micro_payout; 0271 0272 INSERT INTO tmp_micro_payout_mv 0273 (SELECT * FROM stat_micro_payout_mv_curent_month); 0274 0275 #delete plings from actual month 0276 DELETE FROM micro_payout 0277 WHERE yearmonth = (DATE_FORMAT(NOW(),'%Y%m')) 0278 AND TYPE = 1; 0279 0280 #insert ping for this month from tmp member_dl_plings table 0281 INSERT INTO micro_payout 0282 (SELECT * FROM tmp_micro_payout_mv); 0283 0284 #remove tmp micro_payout table 0285 DROP TABLE tmp_micro_payout_mv; 0286 0287 0288 UPDATE micro_payout m 0289 JOIN section_category sc ON sc.project_category_id = m.project_category_id 0290 JOIN section_funding_stats sfs ON sfs.yearmonth = m.yearmonth AND sfs.section_id = sc.section_id 0291 SET m.section_id = sc.section_id, 0292 m.section_payout_factor = sfs.factor, 0293 m.credits_section = m.credits_plings * sfs.factor 0294 WHERE m.yearmonth = DATE_FORMAT(NOW(),'%Y%m') 0295 AND m.`type` = 1; 0296 0297 0298 0299 0300 SELECT * FROM micro_payout p 0301 WHERE p.yearmonth = 201909 0302 AND p.`type` = 1 0303 ; 0304 0305 0306 SELECT * FROM section_funding_stats s 0307 WHERE s.yearmonth = 201909; 0308 0309 0310 0311 0312 0313 0314 0315 0316 0317 0318 0319 0320 0321 0322 0323 CREATE EVENT `e_update_micro_payout_current_month` 0324 ON SCHEDULE 0325 EVERY 1 DAY STARTS '2019-09-17 01:30:00' 0326 DO BEGIN 0327 #Generate tmp table for active projects 0328 DROP TABLE IF EXISTS tmp_project_for_micro_payout; 0329 CREATE TABLE tmp_project_for_micro_payout AS 0330 select * from project p where p.ppload_collection_id is not null and p.type_id = 1 and p.`status` = 100; 0331 0332 #ppload_collection_id from char to int 0333 ALTER TABLE `tmp_project_for_micro_payout` 0334 CHANGE COLUMN `ppload_collection_id` `ppload_collection_id` INT NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `embed_code`; 0335 0336 #add index 0337 ALTER TABLE `tmp_project_for_micro_payout` ADD INDEX `idx_ppload` (`ppload_collection_id`); 0338 ALTER TABLE `tmp_project_for_micro_payout` ADD INDEX `idx_pk` (`project_id`); 0339 0340 #fill tmp micro_payout table 0341 DROP TABLE IF EXISTS tmp_micro_payout; 0342 0343 CREATE TABLE tmp_micro_payout LIKE micro_payout; 0344 0345 INSERT INTO tmp_micro_payout 0346 (SELECT * FROM stat_micro_payout_dl_curent_month); 0347 0348 #delete plings from actual month 0349 DELETE FROM micro_payout 0350 WHERE yearmonth = (DATE_FORMAT(NOW(),'%Y%m')) 0351 AND TYPE = 0; 0352 0353 #insert ping for this month from tmp member_dl_plings table 0354 INSERT INTO micro_payout 0355 (SELECT * FROM tmp_micro_payout); 0356 0357 #remove tmp micro_payout table 0358 DROP TABLE tmp_micro_payout; 0359 0360 UPDATE micro_payout m 0361 JOIN section_category sc ON sc.project_category_id = m.project_category_id 0362 JOIN section_funding_stats sfs ON sfs.yearmonth = m.yearmonth AND sfs.section_id = sc.section_id 0363 SET m.section_id = sc.section_id, 0364 m.section_payout_factor = sfs.factor, 0365 m.credits_section = m.credits_plings * sfs.factor 0366 WHERE m.yearmonth = DATE_FORMAT(NOW(),'%Y%m') 0367 AND m.`type` = 0; 0368 0369 0370 0371 #fill tmp micro_payout table 0372 DROP TABLE IF EXISTS tmp_micro_payout_mv; 0373 0374 CREATE TABLE tmp_micro_payout_mv LIKE micro_payout; 0375 0376 INSERT INTO tmp_micro_payout_mv 0377 (SELECT * FROM stat_micro_payout_mv_curent_month); 0378 0379 #delete plings from actual month 0380 DELETE FROM micro_payout 0381 WHERE yearmonth = (DATE_FORMAT(NOW(),'%Y%m')) 0382 AND TYPE = 1; 0383 0384 #insert ping for this month from tmp member_dl_plings table 0385 INSERT INTO micro_payout 0386 (SELECT * FROM tmp_micro_payout_mv); 0387 0388 #remove tmp micro_payout table 0389 DROP TABLE tmp_micro_payout_mv; 0390 0391 UPDATE micro_payout m 0392 JOIN section_category sc ON sc.project_category_id = m.project_category_id 0393 JOIN section_funding_stats sfs ON sfs.yearmonth = m.yearmonth AND sfs.section_id = sc.section_id 0394 SET m.section_id = sc.section_id, 0395 m.section_payout_factor = sfs.factor, 0396 m.credits_section = m.credits_plings * sfs.factor 0397 WHERE m.yearmonth = DATE_FORMAT(NOW(),'%Y%m') 0398 AND m.`type` = 1; 0399 END 0400 0401 0402 CREATE EVENT `e_update_micro_payout_last_month` 0403 ON SCHEDULE 0404 EVERY 1 MONTH STARTS '2019-10-01 01:45:00' 0405 DO BEGIN 0406 #Generate tmp table for active projects 0407 DROP TABLE IF EXISTS tmp_project_for_micro_payout; 0408 CREATE TABLE tmp_project_for_micro_payout AS 0409 select * from project p where p.ppload_collection_id is not null and p.type_id = 1 and p.`status` = 100; 0410 0411 #ppload_collection_id from char to int 0412 ALTER TABLE `tmp_project_for_micro_payout` 0413 CHANGE COLUMN `ppload_collection_id` `ppload_collection_id` INT NULL DEFAULT NULL COLLATE 'utf8_general_ci' AFTER `embed_code`; 0414 0415 #add index 0416 ALTER TABLE `tmp_project_for_micro_payout` ADD INDEX `idx_ppload` (`ppload_collection_id`); 0417 ALTER TABLE `tmp_project_for_micro_payout` ADD INDEX `idx_pk` (`project_id`); 0418 0419 #fill tmp micro_payout table 0420 DROP TABLE IF EXISTS tmp_micro_payout; 0421 0422 CREATE TABLE tmp_micro_payout LIKE micro_payout; 0423 0424 INSERT INTO tmp_micro_payout 0425 (SELECT * FROM stat_micro_payout_dl_last_month); 0426 0427 #delete plings from actual month 0428 DELETE FROM micro_payout 0429 WHERE yearmonth = (DATE_FORMAT(NOW() - INTERVAL 1 MONTH,'%Y%m')) 0430 AND TYPE = 0; 0431 0432 #insert ping for this month from tmp member_dl_plings table 0433 INSERT INTO micro_payout 0434 (SELECT * FROM tmp_micro_payout); 0435 0436 #remove tmp micro_payout table 0437 DROP TABLE tmp_micro_payout; 0438 0439 UPDATE micro_payout m 0440 JOIN section_category sc ON sc.project_category_id = m.project_category_id 0441 JOIN section_funding_stats sfs ON sfs.yearmonth = m.yearmonth AND sfs.section_id = sc.section_id 0442 SET m.section_id = sc.section_id, 0443 m.section_payout_factor = sfs.factor, 0444 m.credits_section = m.credits_plings * sfs.factor 0445 WHERE m.yearmonth = (DATE_FORMAT(NOW() - INTERVAL 1 MONTH,'%Y%m')) 0446 AND m.`type` = 0; 0447 0448 0449 0450 #fill tmp micro_payout table 0451 DROP TABLE IF EXISTS tmp_micro_payout_mv; 0452 0453 CREATE TABLE tmp_micro_payout_mv LIKE micro_payout; 0454 0455 INSERT INTO tmp_micro_payout_mv 0456 (SELECT * FROM stat_micro_payout_mv_last_month); 0457 0458 #delete plings from actual month 0459 DELETE FROM micro_payout 0460 WHERE yearmonth = (DATE_FORMAT(NOW() - INTERVAL 1 MONTH,'%Y%m')) 0461 AND TYPE = 1; 0462 0463 #insert ping for this month from tmp member_dl_plings table 0464 INSERT INTO micro_payout 0465 (SELECT * FROM tmp_micro_payout_mv); 0466 0467 #remove tmp micro_payout table 0468 DROP TABLE tmp_micro_payout_mv; 0469 0470 0471 UPDATE micro_payout m 0472 JOIN section_category sc ON sc.project_category_id = m.project_category_id 0473 JOIN section_funding_stats sfs ON sfs.yearmonth = m.yearmonth AND sfs.section_id = sc.section_id 0474 SET m.section_id = sc.section_id, 0475 m.section_payout_factor = sfs.factor, 0476 m.credits_section = m.credits_plings * sfs.factor 0477 WHERE m.yearmonth = (DATE_FORMAT(NOW() - INTERVAL 1 MONTH,'%Y%m')) 0478 AND m.`type` = 1; 0479 END