· 8 years ago · Jun 08, 2018, 08:16 AM
1drop table if exists mrr_hosting_data;
2
3CREATE TABLE mrr_hosting_data (
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;
31
32insert into mrr_hosting_data (
33 SELECT
34 `whmcs_invoices`.`datepaid` AS `date`,
35 date_format(`whmcs_invoices`.`datepaid`, '%Y') as year,
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_data`
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
118
119drop table if exists mrr__hosting_data;
120
121CREATE TABLE `mrr__hosting_data` (
122 `mrr` decimal(32,2) DEFAULT NULL,
123 `start` datetime DEFAULT NULL,
124 `termination_date` date DEFAULT NULL,
125 `product_name` varchar(500) NOT NULL,
126 `product_id` int(11) NOT NULL,
127 `product_group_id` int(11) NOT NULL,
128 `product_type` varchar(255) DEFAULT NULL,
129 `country` varchar(2) NOT NULL DEFAULT '',
130 `client_group_id` int(11) NOT NULL
131) ENGINE=InnoDB DEFAULT CHARSET=utf8;
132
133insert into mrr__hosting_data (
134 select
135 mrr2 as mrr,
136 date_format(date,'%Y-%m-%d') as start,
137 termination_date,
138 name as product_name,
139 whmcs_id as product_id,
140 product_group_id,
141 product_type,
142 country,
143 client_group_id
144 from (
145 select
146 ( (CASE WHEN (whmcs_upgrades.date<=d.date) THEN (d.`amount`-((d.`amount` / d.real_amount )*ABS((CASE WHEN (promo is null) THEN 0 ELSE promo END))))+ recurringchange
147 ELSE
148 (d.`amount`-((d.`amount` / d.real_amount )*ABS((CASE WHEN (promo is null) THEN 0 ELSE promo END)))) END) / d.`months` ) AS mrr2,
149 d.* from mrr_hosting_data as d
150 left join whmcs_upgrades on d.whmcs_id=whmcs_upgrades.relid and d.date<=whmcs_upgrades.date
151 where d.date>0
152 group by date
153 ) ssss
154 group by mrr2, product_id
155);
156
157ALTER TABLE `mrr__hosting_data`
158 ADD KEY `mrr` (`mrr`),
159 ADD KEY `client_group_id` (`client_group_id`),
160 ADD KEY `country` (`country`),
161 ADD KEY `start` (`start`),
162 ADD KEY `product_group_id` (`product_group_id`),
163 ADD KEY `termination_date` (`termination_date`),
164 ADD KEY `product_type` (`product_type`),
165 ADD KEY `product_name` (`product_name`),
166 ADD KEY `product_id` (`product_id`);