· 7 years ago · Feb 01, 2019, 12:10 PM
1ALTER TABLE whmcs_upgrades ADD `amount_base` DECIMAL(20,10) NOT NULL AFTER `amount`;
2ALTER TABLE whmcs_upgrades ADD `amount_rate` DECIMAL(20,10) NOT NULL AFTER `amount_base`;
3ALTER TABLE whmcs_upgrades ADD `recurringchange_base` DECIMAL(20,10) NOT NULL AFTER `recurringchange`;
4ALTER TABLE whmcs_upgrades ADD `recurringchange_rate` DECIMAL(20,10) NOT NULL AFTER `recurringchange_base`;
5
6update whmcs_upgrades set amount_base=amount;
7update whmcs_upgrades set amount=0;
8update whmcs_upgrades set recurringchange_base=recurringchange;
9update whmcs_upgrades set recurringchange=0;
10
11update whmcs_upgrades,
12(select
13 whmcs_id,
14 amount_rate,
15 ((amount_base)/(amount_rate)) as amount,
16 ((recurringchange_base)/(amount_rate)) as recurringchange
17 from (
18 select wu.whmcs_id,
19 COALESCE(
20 (select rate from whmcs_rates where date=date_format(wi.datepaid,'%Y-%m-%d') and currency_id=wc.currency),
21 (select rate from whmcs_rates where date=wi.duedate and currency_id=wc.currency),
22 (select rate from whmcs_rates where date=wc.datecreated and currency_id=wc.currency),
23 (select rate from whmcs_currencies where whmcs_id=wc.currency)
24 ) AS amount_rate,
25 wu.amount_base,
26 wu.recurringchange_base
27 from whmcs_upgrades wu
28 join whmcs_orders wo on wu.orderid=wo.whmcs_id
29 join whmcs_clients wc on wo.userid = wc.whmcs_id
30 join whmcs_currencies wcu on wc.currency = wcu.whmcs_id
31 left join whmcs_invoices wi on wo.invoiceid = wi.whmcs_id
32 ) as temp_calc
33) as calculation
34set whmcs_upgrades.amount=calculation.amount,
35 whmcs_upgrades.amount_rate=calculation.amount_rate,
36 whmcs_upgrades.recurringchange=calculation.recurringchange,
37 whmcs_upgrades.recurringchange_rate=calculation.amount_rate
38where whmcs_upgrades.whmcs_id=calculation.whmcs_id;
39
40DROP TABLE IF EXISTS `whmcs__mrr`;
41CREATE TABLE `whmcs__mrr` (
42 `type` INT(11) NULL DEFAULT NULL,
43 `relation_id` INT(11) NULL DEFAULT NULL,
44 `mrr` FLOAT NULL DEFAULT NULL,
45 `date` DATE NULL DEFAULT NULL,
46 INDEX `type` (`type`),
47 INDEX `date` (`date`),
48 INDEX `relation_id` (`relation_id`)
49) COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB;
50
51INSERT INTO whmcs__mrr
52 SELECT
53 type,
54 relation_id,
55 sum(mrr) as mrr,
56 date
57 FROM (
58 SELECT
59 1 as `type`,
60 mrr.relid as `relation_id`,
61 mrr.mrr as `mrr`,
62 wti.`interval_date` as `date`
63 FROM (SELECT
64 (amount2/months) as mrr,
65 result.`from`,
66 result.`to`,
67 result.relid
68 FROM (
69 SELECT
70 (SELECT sum(amount) FROM whmcs_invoiceitems subwii where subwii.invoiceid=wii.invoiceid and subwii.relid=wii.relid and subwii.duedate=wii.duedate and subwii.type in ('PromoHosting','Hosting')) as 'amount2',
71 wcs.billing_cycle,
72 (SELECT wbc.months FROM whmcs_billing_cycles wbc where wbc.name=wcs.billing_cycle) as months,
73 wcs.regdate as hostregdate,
74 COALESCE((SELECT max(duedate) FROM whmcs_invoiceitems subwii where subwii.relid=wii.relid and subwii.duedate<=wii.duedate and type in ('Hosting') limit 1)) as 'from',
75 DATE_SUB(COALESCE((SELECT duedate FROM whmcs_invoiceitems subwii where subwii.relid=wii.relid and subwii.duedate>wii.duedate and type in ('Hosting') limit 1),wh.nextduedate),INTERVAL 1 DAY) as 'to',
76 wii.relid,
77 wi.date,
78 wi.duedate as dduedate,
79 wi.datepaid,
80 wi.`status`
81 FROM whmcs_invoiceitems wii
82 join whmcs__client_services wcs on wii.relid=wcs.service_id and wcs.`type`='hosting'
83 join whmcs_invoices wi on wii.invoiceid=wi.whmcs_id
84 join whmcs_hosting wh on wii.relid=wh.whmcs_id
85 where wii.`type` = 'Hosting' and (wi.datepaid>0 or wi.`status`='Paid') and wcs.billing_cycle NOT IN ('Free Account', 'One Time')
86 ) as result
87 ) as mrr join whmcs_time_intervals wti on wti.interval_date between mrr.FROM and mrr.to group by CONCAT(relation_id, "-",date)
88 UNION
89 SELECT
90 1 as `type`,
91 mrr.relid as `relation_id`,
92 mrr.mrr as `mrr`,
93 wti.`interval_date` as `date`
94 FROM (
95 SELECT
96 (amount/months) as mrr,
97 result.`from`,
98 result.`to`,
99 result.relid
100 FROM (
101 SELECT
102 (SELECT wbc.months FROM whmcs_billing_cycles wbc where wbc.name=wcs.billing_cycle) as months,
103 wu.date as 'from',
104 (SELECT DATE_SUB(duedate,INTERVAL 1 DAY) FROM whmcs_invoiceitems wii where wu.relid=wii.relid and wii.duedate>wu.date limit 1) as 'to',
105 wu.recurringchange as amount,
106 relid as relid
107 FROM whmcs_upgrades wu
108 join whmcs__client_services wcs on wu.relid=wcs.service_id and wcs.`type`='hosting' where wu.status='Completed'
109 ) as result
110 ) as mrr join whmcs_time_intervals wti on wti.interval_date between mrr.FROM and mrr.to group by CONCAT(relation_id, "-",date)) as lastTable group by CONCAT(relation_id, "-",date);
111
112
113INSERT INTO whmcs__mrr
114SELECT
115 2 as `type`,
116 mrr.relid as `relation_id`,
117 mrr.mrr as `mrr`,
118 wti.`interval_date` as `date`
119FROM (SELECT
120 (amount2/months) as mrr,
121 result.`from`,
122 result.`to`,
123 result.relid
124 FROM (
125 SELECT
126 (SELECT sum(amount) FROM whmcs_invoiceitems subwii where subwii.invoiceid=wii.invoiceid and subwii.relid=wii.relid and subwii.duedate=wii.duedate and subwii.type in ('Domain','DomainRegister','DomainTransfer','PromoDomain')) as 'amount2',
127 amount,
128 (wcs.billing_cycle*12) as months,
129 wcs.regdate as hostregdate,
130 COALESCE((SELECT max(duedate) FROM whmcs_invoiceitems subwii where subwii.relid=wii.relid and subwii.duedate<=wii.duedate limit 1)) as 'from',
131 DATE_SUB(COALESCE((SELECT duedate FROM whmcs_invoiceitems subwii where subwii.relid=wii.relid and subwii.duedate>wii.duedate limit 1),wd.nextduedate),INTERVAL 1 DAY) as 'to',
132 wii.relid,
133 wi.date,
134 wi.duedate as dduedate,
135 wi.datepaid,
136 wi.`status`
137 FROM whmcs_invoiceitems wii
138 join whmcs__client_services wcs on wii.relid=wcs.service_id and wcs.`type`='domain'
139 join whmcs_invoices wi on wii.invoiceid=wi.whmcs_id
140 join whmcs_domains wd on wii.relid=wd.whmcs_id
141 where wii.`type` in ('Domain','DomainRegister','DomainTransfer') and (wi.datepaid>0 or wi.`status`='Paid') and wcs.billing_cycle NOT IN ('Free Account', 'One Time')
142 ) as result
143 ) as mrr join whmcs_time_intervals wti on wti.interval_date between mrr.FROM and mrr.to group by CONCAT(relation_id, "-",date);
144
145INSERT INTO whmcs__mrr
146SELECT
147 3 as `type`,
148 mrr.relid as `relation_id`,
149 mrr.mrr as `mrr`,
150 wti.`interval_date` as `date`
151FROM (SELECT
152 (amount/months) as mrr,
153 result.`from`,
154 result.`to`,
155 result.relid
156 FROM (
157 SELECT
158 amount,
159 wcs.billing_cycle,
160 (SELECT wbc.months FROM whmcs_billing_cycles wbc where wbc.name=wcs.billing_cycle) as months,
161 wcs.regdate as hostregdate,
162 COALESCE((SELECT max(duedate) FROM whmcs_invoiceitems subwii where subwii.relid=wii.relid and subwii.duedate<=wii.duedate limit 1)) as 'from',
163 DATE_SUB(COALESCE((SELECT duedate FROM whmcs_invoiceitems subwii where subwii.relid=wii.relid and subwii.duedate>wii.duedate limit 1),wha.nextduedate),INTERVAL 1 DAY) as 'to',
164 wii.relid,
165 wi.date,
166 wi.duedate as dduedate,
167 wi.datepaid,
168 wi.`status`
169 FROM whmcs_invoiceitems wii
170 join whmcs__client_services wcs on wii.relid=wcs.service_id and wcs.`type`='addon'
171 join whmcs_invoices wi on wii.invoiceid=wi.whmcs_id
172 join whmcs_hostingaddons wha on wii.relid=wha.whmcs_id
173 where wii.`type` in ('Addon') and (wi.datepaid>0 or wi.`status`='Paid') and wcs.billing_cycle NOT IN ('Free Account', 'One Time')
174 ) as result
175 ) as mrr join whmcs_time_intervals wti on wti.interval_date between mrr.FROM and mrr.to group by CONCAT(relation_id, "-",date);