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