VnBthAD2

1SET group_concat_max_len=20000;
2SET @emails="viktor.bondar@clickio.com,daniil.zabolotny@clickio.com,anna.kondrashova@clickio.com,mikhail.banduryan@clickio.com";
3SET @mail_subject="Статистка по Lazy load ариям";
4
5# Список арий
6DROP TEMPORARY TABLE IF EXISTS temp_areas_list;
7CREATE TEMPORARY TABLE temp_areas_list
8SELECT * FROM rabota_db.lazy_load_areas_list l
9;
10
11# Статистика по всем ариям за вчерашний день
12DROP TEMPORARY TABLE IF EXISTS  temp_area_stat;
13CREATE TEMPORARY TABLE temp_area_stat
14SELECT
15  c.`date`,
16  c.`site_area_id`,
17  c.`user_id`,
18  c.site_id,
19  SUM(c.`partner_gain_base`) AS 'partner_gain_base',
20  SUM(c.`partner_gain`) AS 'partner_gain',
21  SUM(c.`block_first_request_count`) AS 'requests',
22  ROUND(SUM(c.`partner_gain_base`) / SUM(c.`block_first_request_count`) * 1000,3) AS 'rcpm', 
23  l.lazy_load_start_date AS 'lazy_load_start_date'
24FROM
25  `npm_site_area_stat_cache` c
26  JOIN temp_areas_list l
27    ON l.site_area_id = c.site_area_id
28WHERE 1 = 1
29  AND c.`date` = CURDATE() - INTERVAL 1 DAY
30GROUP BY c.site_area_id;
31
32# данные по ариям за неделю перед включением lazy load   
33DROP TEMPORARY TABLE IF EXISTS  temp_previous_data;
34CREATE TEMPORARY TABLE temp_previous_data
35 SELECT
36  sc.`site_area_id`,
37  sc.`date`,
38  SUM(sc.`partner_gain_base`) AS partner_gain_base,
39  SUM(sc.`partner_gain`) AS partner_gain,
40  SUM(sc.`block_first_request_count`) AS block_first_request_count
41FROM
42  temp_areas_list
43  LEFT JOIN rabota_db.`npm_site_area_stat_cache` sc
44    ON sc.site_area_id = temp_areas_list.site_area_id
45WHERE (sc.`date` BETWEEN temp_areas_list.lazy_load_start_date - INTERVAL 8 DAY AND temp_areas_list.lazy_load_start_date - INTERVAL 1 DAY)
46GROUP BY sc.`site_area_id`, sc.`date` 
47  ;
48  
49# все cpm за неделю перед включением lazy load для исключения равных нулю
50DROP TEMPORARY  TABLE IF EXISTS temp_all_rcpm;
51CREATE TEMPORARY TABLE IF NOT EXISTS temp_all_rcpm
52SELECT
53sc.`site_area_id`,
54  ROUND((SUM(sc.`partner_gain_base`) / SUM(sc.`block_first_request_count`) * 1000),3) AS 'rcpm'
55FROM
56  temp_areas_list
57  LEFT JOIN rabota_db.`npm_site_area_stat_cache` sc
58    ON sc.site_area_id = temp_areas_list.site_area_id
59WHERE (
60    sc.`date` BETWEEN temp_areas_list.lazy_load_start_date - INTERVAL 8 DAY
61          AND temp_areas_list.lazy_load_start_date - INTERVAL 1 DAY
62  )
63GROUP BY sc.`site_area_id`,
64  sc.`date` 
65HAVING (SUM(sc.`partner_gain_base`) / SUM(sc.`block_first_request_count`) * 1000) > 0
66ORDER BY sc.`site_area_id`, rcpm
67;
68
69# AVG rCPM и min CPM за неделю перед включением lazy load
70DROP TEMPORARY  TABLE IF EXISTS temp_avgrcpm;
71CREATE TEMPORARY TABLE IF NOT EXISTS temp_avgrcpm
72SELECT 
73      d.`site_area_id`,
74      ROUND(MIN(c.`rcpm`), 3) AS 'min_rcpm',
75      ROUND(SUM(d.`partner_gain_base`)/ SUM(d.`block_first_request_count`) * 1000,3) AS 'avg_rcpm_before_rub',
76      ROUND(SUM(d.`partner_gain`) / SUM(d.`block_first_request_count`) * 1000,3) AS 'avg_rcpm_before_cur'
77FROM 
78    temp_previous_data d
79    LEFT JOIN temp_all_rcpm c ON d.`site_area_id` = c.`site_area_id`
80GROUP BY d.`site_area_id`
81;
82
83# основные данные
84DROP TEMPORARY TABLE IF EXISTS  temp_basic_data;
85CREATE TEMPORARY TABLE temp_basic_data
86SELECT
87	a.*,
88	s.url,
89        sa.name,
90        b.avg_rcpm_before_rub,
91	IF(b.min_rcpm > a.rcpm AND a.rcpm <> 0, ABS(a.partner_gain - b.avg_rcpm_before_cur*a.requests/1000),0) AS to_compensate,
92	IF(b.min_rcpm > a.rcpm AND a.rcpm <> 0, ABS(a.partner_gain_base - b.avg_rcpm_before_rub*a.requests/1000),0) AS to_compensate_rub,
93	cur.`sign` AS pub_currency
94FROM temp_area_stat a
95LEFT JOIN site s ON s.site_id=a.site_id # для добавления url сайта
96LEFT JOIN site_area sa ON sa.site_area_id = a.site_area_id # для названия арии
97LEFT JOIN temp_avgrcpm b ON a.site_area_id = b.site_area_id # для среднего rCPM из временной таблицы
98LEFT JOIN `adopsuser` u ON a.`user_id` = u.`user_id` # для получения валюты юзера
99LEFT JOIN `currency` cur ON u.`cur_id` = cur.`currency_id` # для добавления знака валюты
100GROUP BY a.site_area_id
101;
102
103# таблица для записи данных в lazy_load_compensations
104DROP TEMPORARY TABLE IF EXISTS  temp_extract_data;
105CREATE TEMPORARY TABLE temp_extract_data
106SELECT
107  NOW() AS `date`,
108  d.user_id,
109  l.contract_id AS contract_id, c.business_unit_id AS business_unit_id,
110  CONCAT('Compensaton for ',DATE_FORMAT(d.date, '%Y-%e-%d')) AS `comment`,
111  d.date AS compensation_date,
112  ROUND(SUM(d.to_compensate), 2) AS ammount
113FROM
114  temp_basic_data d
115JOIN `user` u ON u.user_id = d.user_id
116JOIN contract_user_link l ON l.user_id = u.user_id AND l.active = 1 AND l.active = 1
117JOIN contract c ON c.contract_id = l.contract_id AND c.payment_zone = 1
118WHERE d.to_compensate > 0
119GROUP BY d.user_id
120;
121
122SELECT COUNT(1) FROM temp_extract_data INTO @inserted_rows;
123
124# собственно, перекидывание данных в lazy_load_compensations
125INSERT INTO lazy_load_compensations (`date`,user_id,contract_id,business_unit_id,`comment`,compensation_date,amount)
126SELECT * FROM temp_extract_data
127ON DUPLICATE KEY UPDATE amount=VALUES(amount),`comment`=VALUES(`comment`)
128;
129
130# таблица для вывода данных
131DROP TEMPORARY TABLE IF EXISTS  temp_export_data;
132CREATE TEMPORARY TABLE temp_export_data
133SELECT
134  t.site_id AS site_id,
135  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(t.url, '/', 3), '://', -1), '/', 1), '?', 1),'www.',-1) AS domain, # домен
136  t.site_area_id AS site_area_id,
137  t.name AS area_name,
138  ROUND(t.to_compensate, 2) AS to_compensate,
139  t.pub_currency AS pub_currency,
140  ROUND(t.to_compensate_rub, 2) AS to_compensate_rub,
141  t.rcpm AS rcpm,
142  t.avg_rcpm_before_rub AS rcpm_before_rub,
143  CONCAT(
144	'http://id',
145	t.`user_id`,
146	'.medianet.adlabsnetworks.com/site/PtzFixedBlock/?id=',
147	t.`site_area_id`,
148	'#tab=tab3') AS area_url
149FROM
150  temp_basic_data t
151WHERE t.to_compensate > 0
152ORDER BY to_compensate_rub DESC
153;
154
155# оформление для отправки писем
156SELECT
157	1 AS `status`, 
158	CONCAT (<table>'
159		,'<tr><th>site id</th><th>domain</th><th>site area id</th><th>area name</th><th>to compensate</th><th></th><th>to compensate (rub)</th><th>rCPM</th><th>rCPM before lazy load (rub)</th><th>area url</th></tr>',
160		GROUP_CONCAT('<tr><td>', e.site_id, '</td>',
161                      '<td>', e.domain, '</td>',
162                      '<td>', e.site_area_id, '</td>',
163                      '<td>', e.area_name, '</td>',
164                      '<td>', e.to_compensate, '</td>', 
165                      '<td>', e.pub_currency, '</td>', 
166                      '<td>', e.to_compensate_rub, '</td>',
167                      '<td>', e.rcpm, '</td>',
168                      '<td>', e.rcpm_before_rub, '</td>',
169                      '<td>', e.area_url,
170                      '</tr>'
171                      SEPARATOR "\n"),
172                '</table>',
173		"<br><br>",
174		"В таблицу lazy_load_compensations добавлено ",@inserted_rows," записей",
175		"<br><br>",		
176		"Получатели этого письма: ",@emails
177		) AS param_body,
178    @mail_subject AS param_subject,
179    @emails AS param_emails
180FROM
181  temp_export_data e;