· 7 years ago · Feb 11, 2018, 11:28 AM
1<?php
2
3
4namespace tomal\connector;
5
6class YandexMetrix extends Connector
7{
8 private $login;
9 private $countersTable;
10 private $visitsBaseTable;
11 private $visitsBehaviorTable;
12 private $audienceTable;
13 private $ecommerceTable;
14 private $viewsBaseTable;
15 private $viewsBehaviorTable;
16 private $base_api_url;
17 private $token_api_url;
18 private $token;
19
20 public function __construct($module, $token)
21 {
22 $this->countersTable = "{$module}_ya_metrix_counters";
23 $this->visitsBaseTable = "{$module}_ya_metrix_visits_base";
24 $this->visitsBehaviorTable = "{$module}_ya_metrix_visits_behavior";
25 $this->audienceTable = "{$module}_ya_metrix_audience";
26 $this->ecommerceTable = "{$module}_ya_metrix_ecommerce";
27 $this->viewsBaseTable = "{$module}_ya_metrix_views_base";
28 $this->viewsBehaviorTable = "{$module}_ya_metrix_views_behavior";
29 $this->base_api_url = "https://api-metrika.yandex.ru/";
30 $this->token_api_url = "https://oauth.yandex.ru/token";
31
32 $this->token = $token;
33// $this->login = $login;
34
35 $this->dropTable();
36 $this->createTable();
37
38 $this->init();
39
40// if ($auth) {
41// if ($this->getToken($code)) {
42// $this->init();
43// }
44// } else {
45// $this->makeAuth();
46// }
47 }
48
49 private function makeAuth()
50 {
51 echo file_get_contents('https://oauth.yandex.ru/authorize?response_type=code&client_id=1b70b0b5069f4b26af389ff5bc9c4182');
52 return true;
53 }
54
55 private function getToken($code)
56 {
57
58//// Параметры запроÑа к Ñерверу API Метрики
59// $params = array(
60// 'grant_type' => 'authorization_code',
61// 'code' => "$code",
62// 'client_id' => '1b70b0b5069f4b26af389ff5bc9c4182',
63// 'client_secret' => 'd55fb7f4dfc64952a830c773568c5cf1'
64// );
65//
66// $headers = array(
67// "Content-Type: application/x-www-form-urlencoded",
68// "grant_type=authorization_code"
69// );
70//// Ð˜Ð½Ð¸Ñ†Ð¸Ð°Ð»Ð¸Ð·Ð°Ñ†Ð¸Ñ cURL
71// $curl = curl_init();
72// curl_setopt($curl, CURLOPT_URL, $this->token_api_url);
73// curl_setopt($curl, CURLOPT_POST, true);
74// curl_setopt($curl, CURLOPT_POSTFIELDS, http_build_query($params));
75//
76// /*
77// Ð”Ð»Ñ Ð¿Ð¾Ð»Ð½Ð¾Ñ†ÐµÐ½Ð½Ð¾Ð³Ð¾ иÑÐ¿Ð¾Ð»ÑŒÐ·Ð¾Ð²Ð°Ð½Ð¸Ñ Ð¿Ñ€Ð¾Ñ‚Ð¾ÐºÐ¾Ð»Ð° HTTPS можно включить проверку SSL-Ñертификата Ñервера API Метрики.
78// Чтобы включить проверку, уÑтановите опцию CURLOPT_SSL_VERIFYPEER в true, а также раÑкомментируйте Ñтроку Ñ Ð¾Ð¿Ñ†Ð¸ÐµÐ¹ CURLOPT_CAINFO и укажите путь к локальной копии корневого SSL-Ñертификата.
79// */
80// curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
81////curl_setopt($curl, CURLOPT_CAINFO, getcwd().'\CA.pem');
82//
83// curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
84// curl_setopt($curl, CURLOPT_HEADER, true);
85// curl_setopt($curl, CURLINFO_HEADER_OUT, true);
86// curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
87//
88//// Выполнение запроÑа, получение результата
89// $result = curl_exec($curl);
90//
91// $responseHeadersSize = curl_getinfo($curl, CURLINFO_HEADER_SIZE);
92// $responseBody = substr($result, $responseHeadersSize);
93// $this->token = json_decode($responseBody)->{'access_token'};
94// curl_close($curl);
95 return true;
96 }
97
98 private function init()
99 {
100 $counters = $this->getCounters($this->base_api_url, $this->token);
101
102 for ($i = 0; $i < count($counters["counters"]); $i++) {
103 $currentCounter = $counters["counters"][$i];
104 $id = $counters["counters"][$i]["id"];
105 return
106 $this->updateCountersTable($id, $currentCounter["status"], $currentCounter["name"], $currentCounter["site"]) && $this->updateVisitsBaseTable($id) && $this->updateVisitsBehaviorTable($id)
107 && $this->updateAudienceTable($id) && $this->updateEcommerceTable($id) && $this->updateViewsBaseTable($id)
108 && $this->updateViewsBehaviorTable($id);
109 }
110 }
111
112
113 function getCounters($url_api, $token)
114 {
115 return $this->curlGet(
116 $url_api .
117 "management/v1/counters?oauth_token=" .
118 $token
119 );
120 }
121
122 private function updateCountersTable($id, $status, $name, $site)
123 {
124
125 $sql = "
126 INSERT INTO `{$this->countersTable}` (`login`, `counter_id`, `status`, `name`, `site`)
127 VALUES ('{$this->login}', '{$id}', '{$status}', '{$name}', '{$site}');
128 ";
129
130 $command = \Yii::$app->yametrix_db->createCommand($sql);
131
132 try {
133 $command->execute();
134 } catch (\yii\db\Exception $e) {
135 parent::log($e->getMessage());
136 }
137 return true;
138 }
139
140 private function updateVisitsBaseTable($id)
141 {
142 $data = $this->getVisitsTraffic($this->base_api_url, $id, $this->token, date("Ymd"), date("Ymd"));
143 $visits = strval($data["totals"][2]);
144 $pageViews = strval($data["totals"][1]);
145 $users = strval($data["totals"][0]);
146 $sql = "
147 INSERT INTO `{$this->visitsBaseTable}` (`login`, `counter_id`, `visits`, `pageviews`, `users`)
148 VALUES ('{$this->login}', '{$id}', '{$visits}', '{$pageViews}', '{$users}');
149 ";
150 $command = \Yii::$app->yametrix_db->createCommand($sql);
151
152 try {
153 $command->execute();
154 } catch (\yii\db\Exception $e) {
155 parent::log($e->getMessage());
156 }
157 return true;
158
159 }
160
161 private function updateVisitsBehaviorTable($id)
162 {
163 $data = $this->getVisitsBehavior($this->base_api_url, $id, $this->token, date("Ymd"), date("Ymd"));
164 $bounceRate = strval($data["totals"][0]);
165 $pageDepth = strval($data["totals"][1]);
166 $avgVisitDurationSeconds = strval($data["totals"][2]);
167 $visitsPerDay = strval($data["totals"][3]);
168 $visitsPerHour = strval($data["totals"][4]);
169 $visitsPerMinute = strval($data["totals"][5]);
170 $robotPercentage = strval($data["totals"][6]);
171 $sql = "
172 INSERT INTO `{$this->visitsBehaviorTable}` (`login`, `counter_id`, `bounce_rate`, `page_depth`, `avg_visit_duration_seconds`, `visits_per_day`, `visits_per_hour`, `visits_per_minute`, `robot_percentage`)
173 VALUES ('{$this->login}', '{$id}', '{$bounceRate}', '{$pageDepth}', '{$avgVisitDurationSeconds}', '{$visitsPerDay}', '{$visitsPerHour}', '{$visitsPerMinute}', '{$robotPercentage}');
174 ";
175
176 $command = \Yii::$app->yametrix_db->createCommand($sql);
177
178 try {
179 $command->execute();
180 } catch (\yii\db\Exception $e) {
181 parent::log($e->getMessage());
182 }
183
184 return true;
185
186 }
187
188 private function updateAudienceTable($id)
189 {
190 $data = $this->getAudience($this->base_api_url, $id, $this->token, date("Ymd"), date("Ymd"));
191 $manPercentage = strval($data["totals"][0]);
192 $womanPercentage = strval($data["totals"][1]);
193 $under18AgePercentage = strval($data["totals"][2]);
194 $upTo24AgePercentage = strval($data["totals"][3]);
195 $upTo34AgePercentage = strval($data["totals"][4]);
196 $upTo44AgePercentage = strval($data["totals"][5]);
197 $over44AgePercentage = strval($data["totals"][6]);
198 $sql = "
199 INSERT INTO `{$this->audienceTable}` (`login`, `counter_id`, `man_percentage`, `woman_percentage`, `under_18_age_percentage`, `up_to_24_age_percentage`, `up_to_34_age_percentage`, `up_to_44_age_percentage`, `over_44_age_percentage`)
200 VALUES ('{$this->login}', '{$id}', '{$manPercentage}', '{$womanPercentage}', '{$under18AgePercentage}', '{$upTo24AgePercentage}', '{$upTo34AgePercentage}', '{$upTo44AgePercentage}', '{$over44AgePercentage}');
201 ";
202
203 $command = \Yii::$app->yametrix_db->createCommand($sql);
204
205 try {
206 $command->execute();
207 } catch (\yii\db\Exception $e) {
208 parent::log($e->getMessage());
209 }
210
211 return true;
212
213 }
214
215 private function updateEcommerceTable($id)
216 {
217 $data = $this->getEcommerce($this->base_api_url, $id, $this->token, date("Ymd"), date("Ymd"));
218 $productImpressions = strval($data["totals"][0]);
219 $productImpressionsUniq = strval($data["totals"][1]);
220 $productBasketsQuantity = strval($data["totals"][2]);
221 $productBasketsPrice = strval($data["totals"][3]);
222 $productBasketsUniq = strval($data["totals"][4]);
223 $productPurchasedQuantity = strval($data["totals"][5]);
224 $productPurchasedPrice = strval($data["totals"][6]);
225 $productPurchasedUniq = strval($data["totals"][7]);
226 $ecommercePurchases = strval($data["totals"][8]);
227 $ecommerceRevenue = strval($data["totals"][9]);
228 $ecommerceRevenuePerVisit = strval($data["totals"][10]);
229 $ecommerceRevenuePerPurchase = strval($data["totals"][11]);
230 $sql = "
231 INSERT INTO `{$this->ecommerceTable}` (`login`, `counter_id`, `product_impressions`, `product_impressions_uniq`, `product_baskets_quantity`, `product_baskets_price`, `product_baskets_uniq`, `product_purchased_quantity`, `product_purchased_price`, `product_purchased_uniq`, `ecommerce_purchases`, `ecommerce_revenue`, `ecommerce_revenue_per_visit`, `ecommerce_revenue_per_purchase`)
232 VALUES ('{$this->login}', '{$productImpressions}', '{$productImpressionsUniq}', '{$productBasketsQuantity}', '{$productBasketsPrice}', '{$productBasketsUniq}', '{$productPurchasedQuantity}', '{$productPurchasedPrice}', '{$productPurchasedUniq}', '{$ecommercePurchases}', '{$ecommerceRevenue}', '{$ecommerceRevenuePerVisit}', '{$ecommerceRevenuePerPurchase}', '{$ecommerceRevenuePerPurchase}');
233 ";
234
235 $command = \Yii::$app->yametrix_db->createCommand($sql);
236
237 try {
238 $command->execute();
239 } catch (\yii\db\Exception $e) {
240 parent::log($e->getMessage());
241 }
242
243 return true;
244
245 }
246
247 private function updateViewsBaseTable($id)
248 {
249 $data = $this->getViewsTraffic($this->base_api_url, $id, $this->token, date("Ymd"), date("Ymd"));
250 $pageviews = strval($data["totals"][0]);
251 $users = strval($data["totals"][1]);
252 $sql = "
253 INSERT INTO `{$this->viewsBaseTable}` (`login`, `counter_id`, `pageviews`, `users`)
254 VALUES ('{$this->login}', '{$id}', '{$pageviews}', '{$users}');
255 ";
256
257 $command = \Yii::$app->yametrix_db->createCommand($sql);
258
259 try {
260 $command->execute();
261 } catch (\yii\db\Exception $e) {
262 parent::log($e->getMessage());
263 }
264
265 return true;
266
267 }
268
269 private function updateViewsBehaviorTable($id)
270 {
271 $data = $this->getViewsBehavior($this->base_api_url, $id, $this->token, date("Ymd"), date("Ymd"));
272 $visitsPerDay = strval($data["totals"][0]);
273 $visitsPerHour = strval($data["totals"][1]);
274 $visitsPerMinute = strval($data["totals"][2]);
275 $sql = "
276 INSERT INTO `{$this->visitsBehaviorTable}` (`login`, `counter_id`, `pageviews_per_day`, `pageviews_per_hour`, `pageviews_per_minute`)
277 VALUES ('{$this->login}', '{$id}', '{$visitsPerDay}', '{$visitsPerHour}', '{$visitsPerMinute}');
278 ";
279
280 $command = \Yii::$app->yametrix_db->createCommand($sql);
281
282 try {
283 $command->execute();
284 } catch (\yii\db\Exception $e) {
285 parent::log($e->getMessage());
286 }
287
288 return true;
289
290 }
291
292
293 function getVisitsTraffic($url_api, $counter_id, $token, $date1, $date2)
294 {
295 $params = "ym:s:users,ym:s:visits,ym:s:pageviews";
296 return $this->curlGet($this->makeLink($url_api, $counter_id, $params, $token, $date1, $date2));
297 }
298
299 function getVisitsBehavior($url_api, $counter_id, $token, $date1, $date2)
300 {
301 $params = "ym:s:bounceRate,ym:s:pageDepth,ym:s:avgVisitDurationSeconds,ym:s:visitsPerDay,ym:s:visitsPerHour,ym:s:visitsPerMinute,ym:s:robotPercentage";
302 return $this->curlGet($this->makeLink($url_api, $counter_id, $params, $token, $date1, $date2));
303 }
304
305 function getAudience($url_api, $counter_id, $token, $date1, $date2)
306 {
307 $params = "ym:s:manPercentage,ym:s:womanPercentage,ym:s:under18AgePercentage,ym:s:upTo24AgePercentage,ym:s:upTo34AgePercentage,ym:s:upTo44AgePercentage,ym:s:over44AgePercentage";
308 return $this->curlGet($this->makeLink($url_api, $counter_id, $params, $token, $date1, $date2));
309 }
310
311 function getEcommerce($url_api, $counter_id, $token, $date1, $date2)
312 {
313 $params = "ym:s:productImpressions,ym:s:productImpressionsUniq,ym:s:productBasketsQuantity,ym:s:productBasketsPrice,ym:s:productBasketsUniq,ym:s:productPurchasedQuantity,ym:s:productPurchasedPrice,ym:s:productPurchasedUniq,ym:s:ecommercePurchases,ym:s:ecommerceRevenue,ym:s:ecommerceRevenuePerVisit,ym:s:ecommerceRevenuePerPurchase";
314 return $this->curlGet($this->makeLink($url_api, $counter_id, $params, $token, $date1, $date2));
315 }
316
317 function getViewsTraffic($url_api, $counter_id, $token, $date1, $date2)
318 {
319 $params = "ym:pv:pageviews,ym:pv:users";
320 return $this->curlGet($this->makeLink($url_api, $counter_id, $params, $token, $date1, $date2));
321 }
322
323 function getViewsBehavior($url_api, $counter_id, $token, $date1, $date2)
324 {
325 $params = "ym:pv:pageviewsPerDay,ym:pv:pageviewsPerHour,ym:pv:pageviewsPerMinute";
326 return $this->curlGet($this->makeLink($url_api, $counter_id, $params, $token, $date1, $date2));
327 }
328
329 function getClicksTraffic($url_api, $counter_id, $token, $date1, $date2)
330 {
331 $params = "ym:ad:visits,ym:ad:clicks,ym:ad:RUBAdCost,ym:ad:RUBAdCostPerVisit";
332 return $this->curlGet($this->makeLink($url_api, $counter_id, $params, $token, $date1, $date2));
333 }
334
335 function makeLink($url_api, $counter_id, $params, $token, $date1, $date2)
336 {
337 return $url_api . "stat/v1/data?id=" . $counter_id . "&metrics=" . $params . "&pretty=1" . "&date1=" . $date1 . "&date2=" . $date2 . "&oauth_token=" . $token;
338 }
339
340 private function curlGet($url)
341 {
342 if ($url) {
343 $ch = curl_init();
344 curl_setopt($ch, CURLOPT_URL, $url);
345 curl_setopt($ch, CURLOPT_HEADER, 0);
346 curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
347 curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 30);
348 curl_setopt($ch, CURLOPT_TIMEOUT, 30);
349 curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
350
351 $data = json_decode(
352 curl_exec($ch), true
353 );
354 curl_close($ch);
355
356 if (is_array($data)) {
357 return $data;
358 }
359 }
360 return false;
361 }
362
363 private function createTable()
364 {
365 $sql = "
366 CREATE TABLE IF NOT EXISTS `{$this->countersTable}` (
367 `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
368 `login` varchar(255) NOT NULL,
369 `counter_id` varchar(255) NOT NULL,
370 `status` varchar(255) NOT NULL,
371 `name` varchar(255) NOT NULL,
372 `site` varchar(255) NOT NULL
373 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
374
375 CREATE TABLE IF NOT EXISTS `{$this->visitsBaseTable}` (
376 `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
377 `login` varchar(255) NOT NULL,
378 `counter_id` varchar(255) NOT NULL,
379 `visits` varchar(255) NOT NULL,
380 `pageviews` varchar(255) NOT NULL,
381 `users` varchar(255) NOT NULL
382 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
383
384 CREATE TABLE IF NOT EXISTS `{$this->visitsBehaviorTable}` (
385 `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
386 `login` varchar(255) NOT NULL,
387 `counter_id` varchar(255) NOT NULL,
388 `bounce_rate` varchar(255) NOT NULL,
389 `page_depth` varchar(255) NOT NULL,
390 `avg_visit_duration_seconds` varchar(255) NOT NULL,
391 `visits_per_day` text,
392 `visits_per_hour` text,
393 `visits_per_minute` varchar(255),
394 `robot_percentage` varchar(255)
395 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
396
397 CREATE TABLE IF NOT EXISTS `{$this->audienceTable}` (
398 `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
399 `login` varchar(255) NOT NULL,
400 `counter_id` varchar(255) NOT NULL,
401 `man_percentage` varchar(255) NOT NULL,
402 `woman_percentage` varchar(255) NOT NULL,
403 `under_18_age_percentage` varchar(255) NOT NULL,
404 `up_to_24_age_percentage` text,
405 `up_to_34_age_percentage` text,
406 `up_to_44_age_percentage` varchar(255),
407 `over_44_age_percentage` varchar(255)
408 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
409
410 CREATE TABLE IF NOT EXISTS `{$this->ecommerceTable}` (
411 `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
412 `login` varchar(255) NOT NULL,
413 `counter_id` varchar(255) NOT NULL,
414 `product_impressions` varchar(255) NOT NULL,
415 `product_impressions_uniq` varchar(255) NOT NULL,
416 `product_baskets_quantity` varchar(255) NOT NULL,
417 `product_baskets_price` text,
418 `product_baskets_uniq` text,
419 `product_purchased_quantity` varchar(255),
420 `product_purchased_price` varchar(255),
421 `product_purchased_uniq` varchar(255,
422 `ecommerce_purchases` varchar(255) NOT NULL,
423 `ecommerce_revenue` varchar(255) NOT NULL,
424 `ecommerce_revenue_per_visit` varchar(255) NOT NULL,
425 `ecommerce_revenue_per_purchase` varchar(255) NOT NULL
426 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
427
428 CREATE TABLE IF NOT EXISTS `{$this->viewsBaseTable}` (
429 `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
430 `login` varchar(255) NOT NULL,
431 `counter_id` varchar(255) NOT NULL,
432 `pageviews` varchar(255) NOT NULL,
433 `users` varchar(255) NOT NULL
434 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
435
436 CREATE TABLE IF NOT EXISTS `{$this->viewsBehaviorTable}` (
437 `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
438 `login` varchar(255) NOT NULL,
439 `counter_id` varchar(255) NOT NULL,
440 `pageviews_per_day` varchar(255) NOT NULL,
441 `pageviews_per_hour` varchar(255) NOT NULL,
442 `pageviews_per_minute` varchar(255) NOT NULL
443 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
444 ";
445
446
447 $command = \Yii::$app->yametrix_db->createCommand($sql);
448
449 try {
450 $command->execute();
451 } catch (\yii\db\Exception $e) {
452 parent::log($e->getMessage());
453 }
454
455 return true;
456
457 }
458
459 private function dropTable()
460 {
461 $sql = "
462 DROP TABLE IF EXISTS `{$this->countersTable}`;
463 DROP TABLE IF EXISTS `{$this->visitsBaseTable}`;
464 DROP TABLE IF EXISTS `{$this->visitsBehaviorTable}`;
465 DROP TABLE IF EXISTS `{$this->audienceTable}`;
466 DROP TABLE IF EXISTS `{$this->ecommerceTable}`;
467 DROP TABLE IF EXISTS `{$this->viewsBaseTable}`;
468 DROP TABLE IF EXISTS `{$this->viewsBehaviorTable}`;
469 ";
470
471 $command = \Yii::$app->yametrix_db->createCommand($sql);
472
473 try {
474 $command->execute();
475 } catch (\yii\db\Exception $e) {
476 parent::log($e->getMessage());
477 }
478
479 return true;
480 }
481
482}