· 8 years ago · Jun 06, 2018, 09:20 AM
1drop table if exists mrr__hosting_calculation;
2
3CREATE TABLE mrr__hosting_calculation (
4 date DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
5 termination_date DATE DEFAULT NULL,
6 whmcs_id INT(11) NOT NULL,
7 item_id INT(11) NOT NULL,
8 promo DECIMAL(10, 2) DEFAULT NULL,
9 real_amount DECIMAL(32, 2) DEFAULT NULL,
10 promo_amount DECIMAL(14, 6) DEFAULT NULL,
11 type VARCHAR(30) NOT NULL,
12 datepaid DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
13 group_name VARCHAR(255) DEFAULT NULL,
14 name VARCHAR(500) NOT NULL,
15 regdate DATE NOT NULL,
16 product_group_id INT(11) NOT NULL,
17 product_type VARCHAR(255) DEFAULT NULL,
18 hosting_termination DATE NOT NULL DEFAULT '0000-00-00',
19 nextduedate DATE DEFAULT NULL,
20 nextinvoicedate DATE NOT NULL,
21 amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
22 months SMALLINT(6) NOT NULL DEFAULT 0,
23 duedate DATE DEFAULT NULL,
24 country VARCHAR(2) NOT NULL DEFAULT '',
25 client_group_id INT(11) NOT NULL
26)
27ENGINE = INNODB,
28CHARACTER SET utf8,
29COLLATE utf8_general_ci
30PARTITION BY HASH ( YEAR(date))
31PARTITIONS 100;
32
33insert into mrr__hosting_calculation (
34 SELECT
35 `whmcs_invoices`.`datepaid` AS `date`,
36 `whmcs_hosting`.`termination_date`,
37 `whmcs_hosting`.whmcs_id,
38 `whmcs_invoiceitems`.whmcs_id as item_id,
39 CASE WHEN (`whmcs_invoiceitems`.`type` = 'Hosting') THEN
40 (
41 select wii.amount from `whmcs_invoiceitems` as wii
42 join `whmcs_invoices` wi on wii.invoiceid=wi.whmcs_id
43 where
44 relid=`whmcs_hosting`.whmcs_id
45 and wii.type in ('PromoHosting')
46 and wi.datepaid=`whmcs_invoices`.`datepaid`
47 order by wi.datepaid limit 1
48
49 ) ELSE 0 END AS promo,
50
51 CASE WHEN (`whmcs_invoiceitems`.`type` = 'Hosting') THEN
52 (
53
54
55 select sum(wii.amount) from `whmcs_invoiceitems` as wii
56 join `whmcs_invoices` wi on wii.invoiceid=wi.whmcs_id
57 where relid=`whmcs_hosting`.whmcs_id and wii.type in ('Hosting','Setup') and wi.datepaid=`whmcs_invoices`.`datepaid`
58
59
60 ) ELSE 0 END AS real_amount,
61
62 ( (
63 CASE WHEN (`whmcs_invoiceitems`.`type` = 'PromoHosting') THEN (
64 `whmcs_invoiceitems`.`amount`/(select count(wi.whmcs_id) from `whmcs_invoiceitems` as wii
65 join `whmcs_invoices` wi on wii.invoiceid=wi.whmcs_id
66 where relid=`whmcs_hosting`.whmcs_id and wii.type in ('Hosting','Setup') and wi.datepaid=`whmcs_invoices`.`datepaid`
67 )
68 ) ELSE 0 END)) AS promo_amount,
69 `whmcs_invoiceitems`.`type`,
70 `whmcs_invoices`.`datepaid` AS `datepaid`,
71 `whmcs_productgroups`.name as group_name,
72 `whmcs_products`.name,
73 `whmcs_hosting`.regdate,
74 `whmcs_productgroups`.whmcs_id as product_group_id,
75 `whmcs_products`.type as product_type,
76 hosting.termination_date as hosting_termination,
77 hosting.nextduedate as nextduedate,
78 hosting.nextinvoicedate as nextinvoicedate,
79 `whmcs_invoiceitems`.`amount`,
80 `whmcs_billing_cycles`.`months`,
81 whmcs_invoiceitems.duedate,
82 whmcs_clients.country,
83 whmcs_clients.groupid as client_group_id
84 FROM whmcs_view_hostings AS `whmcs_hosting`
85 JOIN `whmcs_hosting` as hosting ON `whmcs_hosting`.whmcs_id = hosting.`whmcs_id` and whmcs_hosting.regdate<>'00.00.0000'
86 JOIN `whmcs_products` ON `whmcs_hosting`.`packageid` = `whmcs_products`.`whmcs_id`
87 JOIN `whmcs_productgroups` ON `whmcs_products`.gid = `whmcs_productgroups`.whmcs_id
88 JOIN `whmcs_items` AS items ON `whmcs_hosting`.`whmcs_id` = `items`.`relid`
89 JOIN `whmcs_invoiceitems` ON `whmcs_invoiceitems`.`invoiceid` = `items`.`invoiceid` AND `whmcs_invoiceitems`.`type` IN ('Hosting', 'PromoHosting', 'GroupDiscount')
90 and `whmcs_invoiceitems`.relid=`whmcs_hosting`.whmcs_id
91 JOIN `whmcs_invoices` ON `whmcs_invoices`.`whmcs_id` = `items`.`invoiceid` AND `whmcs_invoices`.`status` = 'Paid'
92 JOIN whmcs_clients ON whmcs_invoiceitems.userid = whmcs_clients.whmcs_id
93 JOIN `whmcs_billing_cycles` ON `whmcs_hosting`.`billingcycle` = `whmcs_billing_cycles`.`name` AND `whmcs_hosting`.`billingcycle` NOT IN ('Free Account', 'One Time')
94 GROUP BY whmcs_hosting.whmcs_id, `whmcs_invoiceitems`.`type`, date_format(`whmcs_invoices`.`datepaid`, '%Y-%m-%d')
95);
96
97
98
99ALTER TABLE `mrr__hosting_calculation`
100 ADD KEY `amount` (`amount`),
101 ADD KEY `client_group_id` (`client_group_id`),
102 ADD KEY `country` (`country`),
103 ADD KEY `date` (`date`),
104 ADD KEY `datepaid` (`datepaid`),
105 ADD KEY `duedate` (`duedate`),
106 ADD KEY `group_name` (`group_name`),
107 ADD KEY `months` (`months`),
108 ADD KEY `product_group_id` (`product_group_id`),
109 ADD KEY `product_type` (`product_type`),
110 ADD KEY `promo` (`promo`),
111 ADD KEY `promo_amount` (`promo_amount`),
112 ADD KEY `real_amount` (`real_amount`),
113 ADD KEY `regdate` (`regdate`),
114 ADD KEY `termination_date` (`termination_date`),
115 ADD KEY `type` (`type`),
116 ADD KEY `whmcs_id` (`whmcs_id`);
117
118drop table if exists `mrr__hosting`;
119
120
121CREATE TABLE `mrr__hosting` (
122 `date` datetime DEFAULT NULL,
123 `amount` decimal(10,2) NOT NULL DEFAULT '0.00',
124 `months` smallint(6) NOT NULL DEFAULT '0',
125 `id` int(11) NOT NULL,
126 `item_id` int(11) NOT NULL,
127 `promo_amount` decimal(14,6) DEFAULT NULL,
128 `real_amount` decimal(32,2) DEFAULT NULL,
129 `promo` decimal(10,2) DEFAULT NULL,
130 `type` varchar(30) NOT NULL,
131 `group_name` varchar(255) DEFAULT NULL,
132 `name` varchar(500) NOT NULL,
133 `hosting_termination` date NOT NULL DEFAULT '0000-00-00',
134 `nextduedate` date DEFAULT NULL,
135 `nextinvoicedate` date NOT NULL,
136 `termination_date` date DEFAULT NULL,
137 `regdate` date NOT NULL,
138 `datepaid` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
139 `duedate` date DEFAULT NULL,
140 `product_group_id` int(11) NOT NULL,
141 `product_type` varchar(255) DEFAULT NULL,
142 `country` varchar(2) NOT NULL DEFAULT '',
143 `client_group_id` int(11) NOT NULL,
144 `relid` int(11) DEFAULT NULL,
145 `recurringchange` decimal(10,2) DEFAULT NULL,
146 `upgrade_date` date DEFAULT NULL
147) ENGINE=InnoDB DEFAULT CHARSET=utf8
148PARTITION BY HASH ( YEAR(date))
149PARTITIONS 100;
150
151
152insert into mrr__hosting (
153 SELECT
154 date_format(whmcs_time_intervals.interval_start,'%Y-%m-%d') as date,
155 `storage`.`amount`,
156 months,
157 `storage`.whmcs_id as id,
158 item_id,
159 promo_amount,
160 real_amount,
161 ( (CASE WHEN (promo is null) THEN 0 ELSE promo END) ) AS promo,
162 `storage`.`type`,
163 group_name,
164 name,
165 hosting_termination,
166 nextduedate,
167 nextinvoicedate,
168 `storage`.`termination_date`,
169 regdate,
170 `storage`.`datepaid`,
171 duedate,
172 product_group_id,
173 product_type,
174 country,
175 client_group_id,
176 whmcs_upgrades.relid,
177 whmcs_upgrades.recurringchange,
178 whmcs_upgrades.date as upgrade_date
179 FROM whmcs_time_intervals
180 JOIN mrr__hosting_calculation AS `storage`
181 ON whmcs_time_intervals.interval_start >= date_format(`storage`.date, '%Y-%m-%d 00:00:00') and
182 (whmcs_time_intervals.interval_start<=date_format(`storage`.termination_date, '%Y-%m-%d 00:00:00') or `storage`.termination_date = '0000-00-00')
183 and duedate<=date_format(whmcs_time_intervals.interval_start,'%Y-%m-%d') and duedate>DATE_ADD(date_format(whmcs_time_intervals.interval_start,'%Y-%m-%d'),INTERVAL -months MONTH)
184 and regdate<>'00.00.0000' and whmcs_time_intervals.interval_start BETWEEN DATE_ADD(CURRENT_DATE(),INTERVAL -5 YEAR) and CURRENT_DATE()
185 left join whmcs_upgrades on `storage`.whmcs_id=whmcs_upgrades.relid and whmcs_upgrades.date<=date_format(whmcs_time_intervals.interval_start,'%Y-%m-%d')
186);
187
188
189
190ALTER TABLE `mrr__hosting`
191 ADD KEY `date` (`date`),
192 ADD KEY `upgrade_date` (`upgrade_date`),
193 ADD KEY `amount` (`amount`),
194 ADD KEY `real_amount` (`real_amount`),
195 ADD KEY `promo` (`promo`),
196 ADD KEY `recurringchange` (`recurringchange`),
197 ADD KEY `id` (`id`),
198 ADD KEY `product_group_id` (`product_group_id`),
199 ADD KEY `type` (`type`),
200 ADD KEY `product_type` (`product_type`),
201 ADD KEY `name` (`name`),
202 ADD KEY `country` (`country`),
203 ADD KEY `client_group_id` (`client_group_id`);