File indexing completed on 2024-04-21 16:41:58
0001 ALTER TABLE `member_dl_plings` 0002 ADD COLUMN `is_license_missing` INT(1) UNSIGNED NULL DEFAULT '0' AFTER `probably_payout_amount`, 0003 ADD COLUMN `is_source_missing` INT(1) UNSIGNED NULL DEFAULT '0' AFTER `is_license_missing`, 0004 ADD COLUMN `is_pling_excluded` INT(1) UNSIGNED NULL DEFAULT '0' AFTER `is_source_missing`; 0005 0006 0007 DROP VIEW `stat_member_dl_curent_month`; 0008 CREATE VIEW `stat_member_dl_curent_month` 0009 AS 0010 SELECT DATE_FORMAT(NOW(), '%Y%m') AS `yearmonth`, 0011 `p`.`project_id` AS `project_id`, 0012 `p`.`project_category_id` AS `project_category_id`, 0013 `d`.`owner_id` AS `member_id`, 0014 `m`.`mail` AS `mail`, 0015 `m`.`paypal_mail` AS `paypal_mail`, 0016 COUNT(`d`.`id`) AS `num_downloads`, 0017 `c`.`dl_pling_factor` AS `dl_pling_factor`, 0018 ((COUNT(`d`.`id`) * `c`.`dl_pling_factor`) / 100) AS `amount`, 0019 (CASE WHEN ISNULL(`tag`.`tag_item_id`) THEN 1 ELSE 0 END) AS `is_license_missing`, 0020 (CASE 0021 WHEN (((`c`.`source_required` = 1) AND (`p`.`source_url` IS NOT NULL) AND (LENGTH(`p`.`source_url`) > 0)) OR 0022 (`c`.`source_required` = 0)) THEN 0 0023 ELSE 1 END) AS `is_source_missing`, 0024 `p`.`pling_excluded` AS `is_pling_excluded`, 0025 NULL AS `created_at`, 0026 NULL AS `updated_at` 0027 FROM ((((`ppload`.`ppload_files_downloaded` `d` 0028 JOIN `member` `m` ON (((`m`.`member_id` = `d`.`owner_id`) AND (`m`.`is_active` = 1)))) 0029 JOIN `project` `p` ON (((CAST(`p`.`ppload_collection_id` AS UNSIGNED) = `d`.`collection_id`) AND 0030 (`p`.`status` = 100)))) 0031 JOIN `project_category` `c` ON ((`c`.`project_category_id` = `p`.`project_category_id`))) 0032 LEFT JOIN.`tag_object` `tag` ON (((`tag` . `tag_type_id` = 1) AND (`tag` . `tag_group_id` = 7) AND (`tag` . `tag_object_id` = `p` . `project_id`)))) 0033 WHERE ((`d`.`downloaded_timestamp` >= CONCAT( 0034 LEFT(NOW(), 7), '-01 00:00:00')) AND (`p`.`ppload_collection_id` IS NOT NULL) AND 0035 (LENGTH(`p`.`ppload_collection_id`) > 0) AND (NOT ((`p`.`ppload_collection_id` LIKE '!%')))) 0036 GROUP BY `d`.`owner_id`, `p`.`project_id` 0037 ; 0038 0039 0040 DROP VIEW `stat_member_dl_last_month`; 0041 CREATE VIEW `stat_member_dl_last_month` 0042 AS 0043 SELECT DATE_FORMAT((NOW() - INTERVAL 1 MONTH), '%Y%m') AS `yearmonth`, 0044 `p`.`project_id` AS `project_id`, 0045 `p`.`project_category_id` AS `project_category_id`, 0046 `d`.`owner_id` AS `member_id`, 0047 `m`.`mail` AS `mail`, 0048 `m`.`paypal_mail` AS `paypal_mail`, 0049 COUNT(`d`.`id`) AS `num_downloads`, 0050 `c`.`dl_pling_factor` AS `dl_pling_factor`, 0051 ((COUNT(`d`.`id`) * `c`.`dl_pling_factor`) / 100) AS `amount`, 0052 (CASE WHEN ISNULL(`tag`.`tag_item_id`) THEN 1 ELSE 0 END) AS `is_license_missing`, 0053 (CASE 0054 WHEN (((`c`.`source_required` = 1) AND (`p`.`source_url` IS NOT NULL) AND (LENGTH(`p`.`source_url`) > 0)) OR 0055 (`c`.`source_required` = 0)) THEN 0 0056 ELSE 1 END) AS `is_source_missing`, 0057 `p`.`pling_excluded` AS `is_pling_excluded`, 0058 NULL AS `created_at`, 0059 NULL AS `updated_at` 0060 FROM (((`ppload`.`ppload_files` `d` 0061 JOIN `member` `m` ON (((`m`.`member_id` = `d`.`owner_id`) AND (`m`.`is_active` = 1)))) 0062 JOIN `project` `p` ON (((CAST(`p`.`ppload_collection_id` AS UNSIGNED) = `d`.`collection_id`) AND 0063 (`p`.`status` = 100)))) 0064 JOIN `project_category` `c` ON ((`c`.`project_category_id` = `p`.`project_category_id`)) 0065 LEFT JOIN.`tag_object` `tag` ON (((`tag` . `tag_type_id` = 1) AND (`tag` . `tag_group_id` = 7) AND (`tag` . `tag_object_id` = `p` . `project_id`)))) 0066 WHERE ((`d`.`downloaded_timestamp` >= CONCAT( 0067 LEFT((NOW() - INTERVAL 1 MONTH), 7), '-01 00:00:00')) AND (`p`.`ppload_collection_id` IS NOT NULL) AND 0068 (LENGTH(`p`.`ppload_collection_id`) > 0) AND (NOT ((`p`.`ppload_collection_id` LIKE '!%')))) 0069 GROUP BY `d`.`owner_id`, `p`.`project_id` 0070 ; 0071 0072 0073 INSERT INTO `activity_log_types` (`activity_log_type_id`, `type_text`) 0074 VALUES ('70', 'ProjectLicenseChanged'); 0075 0076 0077