File indexing completed on 2024-04-21 06:00:35
0001 DROP VIEW `stat_dl_payment_last_month`; 0002 0003 CREATE VIEW `stat_dl_payment_last_month` AS 0004 #Letzter Monat 0005 SELECT (DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y%m')) AS `yearmonth`, 0006 count(`d`.`id`) AS `num_downloads`, 0007 `d`.`owner_id` AS `member_id`, 0008 `m`.`username`, 0009 count(`d`.`id`) / 100 AS `amount`, 0010 `m`.`mail`, 0011 `m`.`paypal_mail` 0012 FROM `ppload`.`ppload_files_downloaded` `d` 0013 JOIN `member` `m` ON `m`.`member_id` = `d`.`owner_id` 0014 WHERE (`d`.`downloaded_timestamp` BETWEEN CONCAT(LEFT(NOW() - INTERVAL 1 MONTH, 7), '-01 00:00:00') AND CONCAT(LEFT(NOW(), 7), '-01 00:00:00')) 0015 # AND count(d.id) > 100 0016 GROUP BY `d`.`owner_id` 0017 ORDER BY count(`d`.`id`) DESC; 0018 0019 0020 CREATE TABLE `payout` 0021 ( 0022 `id` INT(11) NOT NULL AUTO_INCREMENT, 0023 `yearmonth` INT(11) NOT NULL, 0024 `member_id` INT(11) NOT NULL, 0025 `mail` VARCHAR(50) NOT NULL, 0026 `paypal_mail` VARCHAR(50) NULL DEFAULT NULL, 0027 `amount` DOUBLE NOT NULL, 0028 `num_downloads` INT(11) NOT NULL, 0029 `status` INT(11) NOT NULL DEFAULT '0' COMMENT '0=new,1=start request,2=money recieved,99=error', 0030 `timestamp_create` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 0031 `timestamp_masspay_start` TIMESTAMP NULL DEFAULT NULL, 0032 `timestamp_masspay_last_ipn` TIMESTAMP NULL DEFAULT NULL, 0033 `paypal_ipn` TEXT NULL, 0034 PRIMARY KEY (`id`), 0035 UNIQUE INDEX `UK_PAYOUT` (`yearmonth`, `member_id`) 0036 ) 0037 COMMENT ='Table for our monthly payouts' 0038 COLLATE = 'latin1_swedish_ci' 0039 ENGINE = InnoDB 0040 ; 0041