· 8 years ago · May 17, 2018, 04:46 PM
1SET FOREIGN_KEY_CHECKS=0;
2drop table if exists whmcs__mrr_domains;
3CREATE TABLE `whmcs__mrr_domains` (
4 `mrr` decimal(36,6) DEFAULT NULL,
5 `date` datetime DEFAULT NULL,
6 `tld` varchar(100) CHARACTER SET utf8 NOT NULL,
7 `domain_id` int(11) NOT NULL,
8 `country` char(2) CHARACTER SET utf8 NOT NULL,
9 `client_group_id` int(11) NOT NULL
10) ENGINE=InnoDB DEFAULT CHARSET=utf8;
11
12insert into whmcs__mrr_domains
13 SELECT
14 SUM(whmcs_invoiceitems.amount/(whmcs_domains.registrationperiod*12)) AS mrr,
15 whmcs_time_intervals.interval_start AS date,
16 whmcs_domains.tld,
17 whmcs_domains.whmcs_id as domain_id,
18 whmcs_clients.country,
19 whmcs_clients.groupid as client_group_id
20
21 FROM whmcs_view_domains AS whmcs_domains
22 JOIN whmcs_clients ON whmcs_domains.userid=whmcs_clients.whmcs_id
23
24
25 LEFT JOIN whmcs_time_intervals ON registrationdate<=whmcs_time_intervals.interval_start AND
26 (whmcs_domains.termination_date='0000-00-00' OR whmcs_time_intervals.interval_end<=whmcs_domains.termination_date)
27 JOIN whmcs_invoiceitems ON whmcs_domains.whmcs_id=whmcs_invoiceitems.relid
28 AND `whmcs_invoiceitems`.`type` IN ('DomainRegister', 'DomainTransfer', 'PromoDomain', 'DomainAddonEMF','DomainAddonDNS', 'DomainAddonIDP')
29 GROUP BY whmcs_domains.whmcs_id,whmcs_time_intervals.interval_start
30 ORDER BY whmcs_time_intervals.interval_start;
31
32
33ALTER TABLE `whmcs__mrr_domains` ADD INDEX(`mrr`);
34ALTER TABLE `whmcs__mrr_domains` ADD INDEX(`date`);
35ALTER TABLE `whmcs__mrr_domains` ADD INDEX(`tld`);
36ALTER TABLE `whmcs__mrr_domains` ADD INDEX(`domain_id`);
37ALTER TABLE `whmcs__mrr_domains` ADD INDEX(`country`);
38ALTER TABLE `whmcs__mrr_domains` ADD INDEX(`client_group_id`);
39
40drop table if exists whmcs__mrr_hosting;
41
42CREATE TABLE `whmcs__mrr_hosting` (
43 `date` date DEFAULT NULL,
44 `product_id` int(11) NOT NULL,
45 `product_group_id` int(11) NOT NULL,
46 `item_id` int(11) NOT NULL,
47 `item_type` varchar(50) CHARACTER SET utf8 NOT NULL,
48 `product_type` varchar(25) CHARACTER SET utf8 NOT NULL,
49 `group_name` varchar(500) CHARACTER SET utf8 NOT NULL,
50 `product_name` varchar(500) CHARACTER SET utf8 NOT NULL,
51 `termination_date` date DEFAULT NULL,
52 `country` char(2) CHARACTER SET utf8 NOT NULL,
53 `client_group_id` int(11) NOT NULL,
54 `mrr` decimal(14,6) DEFAULT NULL,
55 `months` int(11) NOT NULL
56) ENGINE=InnoDB DEFAULT CHARSET=utf8;
57
58insert into whmcs__mrr_hosting
59select
60 date,
61 id as product_id,
62 product_group_id,
63 item_id,
64 type as item_type,
65 product_type,
66 group_name,
67 name as product_name,
68 termination_date,
69 country,
70 client_group_id,
71 mrr2 as mrr,
72 months
73 from (
74 select
75 d.date,
76 `datepaid`,
77 regdate,
78 d.id,
79 d.item_id,
80 d.promo_amount,
81 d.real_amount,
82 d.promo,
83 d.type,
84 group_name,
85 name,
86 termination_date,
87 hosting_termination,
88 nextduedate,
89 nextinvoicedate,
90 product_group_id,
91 product_type,
92 d.amount,
93 ( (CASE WHEN (whmcs_upgrades.date<=d.date) THEN d.`amount` + recurringchange ELSE d.`amount` END) / d.`months`) AS mrr,
94 ( (CASE WHEN (whmcs_upgrades.date<=d.date) THEN (d.`amount`-((d.`amount` / d.real_amount )*ABS(d.promo)))+ recurringchange ELSE (d.`amount`-((d.`amount` / d.real_amount )*ABS(d.promo))) END) / d.`months` ) AS mrr2,
95 whmcs_upgrades.relid,
96 whmcs_upgrades.recurringchange,
97 whmcs_upgrades.date as upgrade_date,
98 duedate,
99 country,
100 client_group_id,
101 months
102 from (
103 SELECT
104 date_format(whmcs_time_intervals.interval_start,'%Y-%m-%d') as date,
105 `amount`,
106 months,
107 whmcs_id as id,
108 item_id,
109 promo_amount,
110 real_amount,
111 ( (CASE WHEN (promo is null) THEN 0 ELSE promo END) ) AS promo,
112 `type`,
113 group_name,
114 name,
115 hosting_termination,
116 nextduedate,
117 nextinvoicedate,
118 `storage`.`termination_date`,
119 regdate,
120 `storage`.`datepaid`,
121 duedate,
122 product_group_id,
123 product_type,
124 country,
125 client_group_id
126 FROM whmcs_time_intervals
127 RIGHT JOIN (
128 SELECT
129 `whmcs_invoices`.`datepaid` AS `date`,
130 `whmcs_hosting`.`termination_date`,
131 `whmcs_hosting`.whmcs_id,
132 `whmcs_invoiceitems`.whmcs_id as item_id,
133 CASE WHEN (`whmcs_invoiceitems`.`type` = 'Hosting') THEN
134 (
135 select wii.amount from `whmcs_invoiceitems` as wii
136 join `whmcs_invoices` wi on wii.invoiceid=wi.whmcs_id
137 where
138 relid=`whmcs_hosting`.whmcs_id
139 and wii.type in ('PromoHosting')
140 and wi.datepaid=`whmcs_invoices`.`datepaid`
141 order by wi.datepaid limit 1
142
143 ) ELSE 0 END AS promo,
144
145 CASE WHEN (`whmcs_invoiceitems`.`type` = 'Hosting') THEN
146 (
147
148
149 select sum(wii.amount) from `whmcs_invoiceitems` as wii
150 join `whmcs_invoices` wi on wii.invoiceid=wi.whmcs_id
151 where relid=`whmcs_hosting`.whmcs_id and wii.type in ('Hosting','Setup') and wi.datepaid=`whmcs_invoices`.`datepaid`
152
153
154 ) ELSE 0 END AS real_amount,
155
156 ( (
157 CASE WHEN (`whmcs_invoiceitems`.`type` = 'PromoHosting') THEN (
158 `whmcs_invoiceitems`.`amount`/(select count(wi.whmcs_id) from `whmcs_invoiceitems` as wii
159 join `whmcs_invoices` wi on wii.invoiceid=wi.whmcs_id
160 where relid=`whmcs_hosting`.whmcs_id and wii.type in ('Hosting','Setup') and wi.datepaid=`whmcs_invoices`.`datepaid`
161 )
162 ) ELSE 0 END)) AS promo_amount,
163 `whmcs_invoiceitems`.`type`,
164 `whmcs_invoices`.`datepaid` AS `datepaid`,
165 `whmcs_productgroups`.name as group_name,
166 `whmcs_products`.name,
167 `whmcs_hosting`.regdate,
168 `whmcs_productgroups`.whmcs_id as product_group_id,
169 `whmcs_products`.type as product_type,
170 hosting.termination_date as hosting_termination,
171 hosting.nextduedate as nextduedate,
172 hosting.nextinvoicedate as nextinvoicedate,
173 `whmcs_invoiceitems`.`amount`,
174 `whmcs_billing_cycles`.`months`,
175 whmcs_invoiceitems.duedate,
176 whmcs_clients.country,
177 whmcs_clients.groupid as client_group_id
178 FROM whmcs_view_hostings AS `whmcs_hosting`
179 JOIN `whmcs_hosting` as hosting ON `whmcs_hosting`.whmcs_id = hosting.`whmcs_id`
180 JOIN `whmcs_products` ON `whmcs_hosting`.`packageid` = `whmcs_products`.`whmcs_id`
181 JOIN `whmcs_productgroups` ON `whmcs_products`.gid = `whmcs_productgroups`.whmcs_id
182 JOIN `whmcs_items` AS items ON `whmcs_hosting`.`whmcs_id` = `items`.`relid`
183 JOIN `whmcs_invoiceitems` ON `whmcs_invoiceitems`.`invoiceid` = `items`.`invoiceid` AND `whmcs_invoiceitems`.`type` IN ('Hosting', 'PromoHosting', 'GroupDiscount')
184 and `whmcs_invoiceitems`.relid=`whmcs_hosting`.whmcs_id
185 JOIN `whmcs_invoices` ON `whmcs_invoices`.`whmcs_id` = `items`.`invoiceid` AND `whmcs_invoices`.`status` = 'Paid'
186 JOIN whmcs_clients ON whmcs_invoiceitems.userid = whmcs_clients.whmcs_id
187 JOIN `whmcs_billing_cycles` ON `whmcs_hosting`.`billingcycle` = `whmcs_billing_cycles`.`name` AND `whmcs_hosting`.`billingcycle` NOT IN ('Free Account', 'One Time')
188 GROUP BY whmcs_hosting.whmcs_id, `whmcs_invoiceitems`.`type`, date_format(`whmcs_invoices`.`datepaid`, '%Y-%m-%d')
189 )
190 AS `storage`
191 ON whmcs_time_intervals.interval_start >= date_format(`storage`.date, '%Y-%m-%d 00:00:00') and
192 (whmcs_time_intervals.interval_start<=date_format(`storage`.termination_date, '%Y-%m-%d 00:00:00') or `storage`.termination_date = '0000-00-00')
193) d
194 left join whmcs_upgrades on d.id=relid
195 order by date
196) dupen
197where
198 duedate<=date and duedate>DATE_ADD(date,INTERVAL -months MONTH) group by date, id, type, duedate;
199
200ALTER TABLE `whmcs__mrr_hosting` ADD INDEX(`date`);
201ALTER TABLE `whmcs__mrr_hosting` ADD INDEX(`product_id`);
202ALTER TABLE `whmcs__mrr_hosting` ADD INDEX(`product_group_id`);
203ALTER TABLE `whmcs__mrr_hosting` ADD INDEX(`item_id`);
204ALTER TABLE `whmcs__mrr_hosting` ADD INDEX(`item_type`);
205ALTER TABLE `whmcs__mrr_hosting` ADD INDEX(`product_type`);
206ALTER TABLE `whmcs__mrr_hosting` ADD INDEX(`group_name`);
207ALTER TABLE `whmcs__mrr_hosting` ADD INDEX(`product_name`);
208ALTER TABLE `whmcs__mrr_hosting` ADD INDEX(`termination_date`);
209ALTER TABLE `whmcs__mrr_hosting` ADD INDEX(`country`);
210ALTER TABLE `whmcs__mrr_hosting` ADD INDEX(`client_group_id`);
211ALTER TABLE `whmcs__mrr_hosting` ADD INDEX(`mrr`);
212ALTER TABLE `whmcs__mrr_hosting` ADD INDEX(`months`);
213
214
215drop table if exists whmcs__mrr_addons;
216CREATE TABLE `whmcs__mrr_addons` (
217 `date` date DEFAULT NULL,
218 `addon_id` int(11) NOT NULL,
219 `addon_name` varchar(500) CHARACTER SET utf8 NOT NULL,
220 `itemid` int(11) NOT NULL,
221 `type` varchar(100) CHARACTER SET utf8 NOT NULL,
222 `termination_date` date DEFAULT NULL,
223 `amount` decimal(14,6) DEFAULT NULL,
224 `mrr` decimal(14,6) DEFAULT NULL,
225 `setupfee` decimal(14,6) DEFAULT NULL,
226 `country` char(2) CHARACTER SET utf8 NOT NULL,
227 `client_group_id` int(11) NOT NULL
228) ENGINE=InnoDB DEFAULT CHARSET=utf8;
229
230
231insert into whmcs__mrr_addons
232 select
233 date,
234 id as addon_id,
235 addon_name,
236 itemid,
237 type,
238 termination_date,
239 `amount`,
240 dupen2 as mrr,
241 dupen1 as setupfee,
242 country,
243 client_group_id from (
244 select
245 date,
246 `datepaid`,
247 id,
248 addon_name,
249 itemid,
250 regdate,
251 type,
252 termination_date,
253 nextduedate,
254 nextinvoicedate,
255 `months`,
256 `amount`,
257 mrr,
258 dupen1,
259 dupen2,
260 country,
261 client_group_id
262 from (
263 SELECT
264 date_format(whmcs_time_intervals.interval_start,'%Y-%m-%d') as date,
265 `storage`.mrr AS mrr,
266 dupen1,
267 whmcs_id as id,
268 addon_name,
269 itemid,
270 regdate,
271 `type`,
272 nextduedate,
273 nextinvoicedate,
274 `months`,
275 `amount`,
276 `storage`.`termination_date`,
277 `storage`.`datepaid`,
278 (
279 CASE WHEN (dupen1<0 and DATE_ADD(regdate,INTERVAL months MONTH)<=whmcs_time_intervals.interval_start)
280 THEN (amount-dupen1)/months
281 ELSE mrr END
282 ) as dupen2,
283 country,
284 client_group_id
285 FROM whmcs_time_intervals
286 RIGHT JOIN (
287 SELECT
288 date_format(`whmcs_invoices`.`datepaid`, '%Y-%m-%d') AS `date`,
289 `whmcs_view_addons`.`termination_date`,
290 `whmcs_invoiceitems`.`type`,
291 `whmcs_view_addons`.whmcs_id,
292 whmcs_addons.name as addon_name,
293 `whmcs_view_addons`.regdate,
294 whmcs_hostingaddons.nextduedate,
295 whmcs_hostingaddons.nextinvoicedate,
296 `whmcs_invoices`.`duedate` AS `duedate`,
297 `whmcs_invoiceitems`.relid AS itemid,
298 `whmcs_invoiceitems`.`invoiceid` AS `invoiceid`,
299 `whmcs_invoices`.`datepaid` AS `datepaid`,
300 `whmcs_billing_cycles`.`months` AS `months`,
301 `whmcs_invoiceitems`.`amount`,
302 ((CASE WHEN (`whmcs_view_addons`.setupfee>0)
303 THEN `whmcs_invoiceitems`.`amount` - `whmcs_view_addons`.setupfee
304 ELSE `whmcs_invoiceitems`.`amount` END) / `whmcs_billing_cycles`.`months`) AS mrr,
305 `whmcs_view_addons`.setupfee as dupen1,
306 whmcs_clients.country,
307 whmcs_clients.groupid as client_group_id
308 FROM `whmcs_view_addons`
309
310 JOIN whmcs_addons ON `whmcs_view_addons`.addonid = whmcs_addons.`whmcs_id`
311 JOIN whmcs_hostingaddons ON whmcs_addons.whmcs_id = whmcs_hostingaddons.addonid
312 JOIN `whmcs_items` as items ON `whmcs_view_addons`.`whmcs_id` = `items`.`relid`
313 JOIN `whmcs_invoiceitems` ON `whmcs_invoiceitems`.`invoiceid` = `items`.`invoiceid` AND `whmcs_invoiceitems`.`type` IN ('Addon')
314
315 and `whmcs_invoiceitems`.relid=`whmcs_view_addons`.whmcs_id
316
317 JOIN `whmcs_invoices` ON `whmcs_invoices`.`whmcs_id` = `items`.`invoiceid` AND `whmcs_invoices`.`status` = 'Paid'
318 JOIN whmcs_clients ON whmcs_invoiceitems.userid=whmcs_clients.whmcs_id
319 JOIN `whmcs_billing_cycles` ON `whmcs_view_addons`.`billingcycle` = `whmcs_billing_cycles`.`name` and `whmcs_view_addons`.`billingcycle` NOT IN ('Free Account', 'One Time')
320
321 GROUP BY `whmcs_view_addons`.whmcs_id,`whmcs_invoiceitems`.`type`, date_format(`whmcs_invoices`.`datepaid`, '%Y-%m-%d')
322 )
323 AS `storage`
324 ON whmcs_time_intervals.interval_start >= date_format(`storage`.date, '%Y-%m-%d 00:00:00') and
325 (whmcs_time_intervals.interval_start<=date_format(`storage`.termination_date, '%Y-%m-%d 00:00:00') or `storage`.termination_date = '0000-00-00')
326 and date_format(regdate, '%Y-%m-%d 00:00:00')<=whmcs_time_intervals.interval_start
327) d order by date
328) dupen
329group by date,id;
330
331
332ALTER TABLE `whmcs__mrr_addons` ADD INDEX(`date`);
333ALTER TABLE `whmcs__mrr_addons` ADD INDEX(`addon_id`);
334ALTER TABLE `whmcs__mrr_addons` ADD INDEX(`addon_name`);
335ALTER TABLE `whmcs__mrr_addons` ADD INDEX(`itemid`);
336ALTER TABLE `whmcs__mrr_addons` ADD INDEX(`type`);
337ALTER TABLE `whmcs__mrr_addons` ADD INDEX(`termination_date`);
338ALTER TABLE `whmcs__mrr_addons` ADD INDEX(`amount`);
339ALTER TABLE `whmcs__mrr_addons` ADD INDEX(`mrr`);
340ALTER TABLE `whmcs__mrr_addons` ADD INDEX(`setupfee`);
341ALTER TABLE `whmcs__mrr_addons` ADD INDEX(`country`);
342ALTER TABLE `whmcs__mrr_addons` ADD INDEX(`client_group_id`);
343
344
345SET FOREIGN_KEY_CHECKS=1;