matomo / app / core / Tracker / Model.php

Model.php in Matomo Analytics – Powerful, Privacy-First Insights for WordPress 5.13.0, at app/core/Tracker/Model.php

576 lines 20.9 KB

Raw Download Zip

1	<?php
2
3	/**
4	* Matomo - free/libre analytics platform
5	*
6	* @link https://matomo.org
7	* @license https://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
8	*/
9	namespace Piwik\Tracker;
10
11	use Exception;
12	use Piwik\Common;
13	use Piwik\Container\StaticContainer;
14	use Piwik\Tracker;
15	use Piwik\Log\LoggerInterface;
16	class Model
17	{
18	public const CACHE_KEY_INDEX_IDSITE_IDVISITOR_TIME = 'log_visit_has_index_idsite_idvisitor_time';
19	/**
20	* Write a visit action record to the database
21	*
22	* @param array $visitAction
23	*
24	* @return int
25	*/
26	public function createAction($visitAction)
27	{
28	$fields = implode(", ", array_keys($visitAction));
29	$values = Common::getSqlStringFieldsArray($visitAction);
30	$table = Common::prefixTable('log_link_visit_action');
31	$sql = "INSERT INTO {$table} ({$fields}) VALUES ({$values})";
32	$bind = array_values($visitAction);
33	$db = $this->getDb();
34	$db->query($sql, $bind);
35	$id = $db->lastInsertId();
36	return $id;
37	}
38	/**
39	* Write a goal conversion to the database
40	*
41	* @param array $conversion
42	*
43	* @return bool
44	* @throws Db\DbException
45	*/
46	public function createConversion($conversion)
47	{
48	$fields = implode(", ", array_keys($conversion));
49	$bindFields = Common::getSqlStringFieldsArray($conversion);
50	$table = Common::prefixTable('log_conversion');
51	$sql = "INSERT IGNORE INTO {$table} ({$fields}) VALUES ({$bindFields}) ";
52	$bind = array_values($conversion);
53	$db = $this->getDb();
54	$result = $db->query($sql, $bind);
55	// If a record was inserted, we return true
56	return $db->rowCount($result) > 0;
57	}
58	/**
59	* Update an existing goal conversion in the database
60	*
61	* @param int $idVisit
62	* @param int $idGoal
63	* @param array $newConversion
64	*
65	* @return bool
66	* @throws \DI\DependencyException
67	* @throws \DI\NotFoundException
68	*/
69	public function updateConversion($idVisit, $idGoal, $newConversion)
70	{
71	$updateWhere = ['idvisit' => $idVisit, 'idgoal' => $idGoal, 'buster' => 0];
72	$updateParts = $sqlBind = $updateWhereParts = [];
73	foreach ($newConversion as $name => $value) {
74	$updateParts[] = $name . " = ?";
75	$sqlBind[] = $value;
76	}
77	foreach ($updateWhere as $name => $value) {
78	$updateWhereParts[] = $name . " = ?";
79	$sqlBind[] = $value;
80	}
81	$parts = implode(', ', $updateParts);
82	$table = Common::prefixTable('log_conversion');
83	$sql = "UPDATE {$table} SET {$parts} WHERE " . implode(' AND ', $updateWhereParts);
84	try {
85	$this->getDb()->query($sql, $sqlBind);
86	} catch (Exception $e) {
87	StaticContainer::get(LoggerInterface::class)->error("There was an error while updating the Conversion: {exception}", ['exception' => $e]);
88	return \false;
89	}
90	return \true;
91	}
92	/**
93	* Returns the ecommerce items currently stored in the cart/order for the given visit.
94	*
95	* @param array $goal
96	* @param int $defaultIdOrder
97	* @return array
98	*/
99	public function getAllItemsCurrentlyInTheCart($goal, $defaultIdOrder)
100	{
101	$sql = "SELECT idaction_sku, idaction_name, idaction_category, idaction_category2, idaction_category3, idaction_category4, idaction_category5, price, quantity, deleted, idorder AS idorder_original_value\n\t\t\t\tFROM `" . Common::prefixTable('log_conversion_item') . "`\n\t\t\t\tWHERE idvisit = ? AND (idorder = ? OR idorder = ?)";
102	$bind = [$goal['idvisit'], isset($goal['idorder']) ? $goal['idorder'] : $defaultIdOrder, $defaultIdOrder];
103	$itemsInDb = $this->getDb()->fetchAll($sql, $bind);
104	Common::printDebug("Items found in current cart, for conversion_item (visit,idorder)=" . var_export($bind, \true));
105	Common::printDebug($itemsInDb);
106	return $itemsInDb;
107	}
108	/**
109	* Write ecommerce item to the conversion item table
110	*
111	* @param array $ecommerceItems
112	*
113	* @throws Db\DbException
114	*/
115	public function createEcommerceItems($ecommerceItems)
116	{
117	$sql = "INSERT IGNORE INTO " . Common::prefixTable('log_conversion_item');
118	$i = 0;
119	$bind = [];
120	foreach ($ecommerceItems as $item) {
121	if ($i === 0) {
122	$fields = implode(', ', array_keys($item));
123	$sql .= ' (' . $fields . ') VALUES ';
124	} elseif ($i > 0) {
125	$sql .= ',';
126	}
127	$newRow = array_values($item);
128	$sql .= " ( " . Common::getSqlStringFieldsArray($newRow) . " ) ";
129	$bind = array_merge($bind, $newRow);
130	$i++;
131	}
132	Common::printDebug($sql);
133	Common::printDebug($bind);
134	try {
135	$this->getDb()->query($sql, $bind);
136	} catch (Exception $e) {
137	if ($e->getCode() == 23000 \|\| \false !== strpos($e->getMessage(), 'Duplicate entry') \|\| \false !== strpos($e->getMessage(), 'Integrity constraint violation')) {
138	Common::printDebug('Did not create ecommerce item as item was already created');
139	} else {
140	throw $e;
141	}
142	}
143	}
144	/**
145	* Inserts a new action into the log_action table. If there is an existing action that was inserted
146	* due to another request pre-empting this one, the newly inserted action is deleted.
147	*
148	* @param string $name
149	* @param int $type
150	* @param int $urlPrefix
151	* @return int The ID of the action (can be for an existing action or new action).
152	*/
153	public function createNewIdAction($name, $type, $urlPrefix)
154	{
155	$newActionId = $this->insertNewAction($name, $type, $urlPrefix);
156	$realFirstActionId = $this->getIdActionMatchingNameAndType($name, $type);
157	// if the inserted action ID is not the same as the queried action ID, then that means we inserted
158	// a duplicate, so remove it now
159	if ($realFirstActionId != $newActionId) {
160	$this->deleteDuplicateAction($newActionId);
161	}
162	return $realFirstActionId;
163	}
164	/**
165	* Insert a new action into the DB
166	*
167	* @param string $name
168	* @param int $type
169	* @param string $urlPrefix
170	*
171	* @return int
172	* @throws Db\DbException
173	*/
174	private function insertNewAction($name, $type, $urlPrefix)
175	{
176	$table = Common::prefixTable('log_action');
177	$sql = "INSERT INTO {$table} (name, hash, type, url_prefix) VALUES (?,CRC32(?),?,?)";
178	$db = $this->getDb();
179	$db->query($sql, [$name, $name, $type, $urlPrefix]);
180	$actionId = $db->lastInsertId();
181	return $actionId;
182	}
183	/**
184	* Get an idaction key from the DB
185	*
186	* @return string
187	*/
188	private function getSqlSelectActionId()
189	{
190	// it is possible for multiple actions to exist in the DB (due to rare concurrency issues), so the ORDER BY and
191	// LIMIT are important
192	$sql = "SELECT idaction, type, name FROM `" . Common::prefixTable('log_action') . "`" . " WHERE " . $this->getSqlConditionToMatchSingleAction() . " " . "ORDER BY idaction ASC LIMIT 1";
193	return $sql;
194	}
195	/**
196	* Get an idaction key from the DB by name and type
197	*
198	* @param string $name
199	* @param int $type
200	*
201	* @return bool\|mixed\|string
202	* @throws Exception
203	*/
204	public function getIdActionMatchingNameAndType($name, $type)
205	{
206	$sql = $this->getSqlSelectActionId();
207	$bind = [$name, $name, $type];
208	$idAction = $this->getDb()->fetchOne($sql, $bind);
209	return $idAction;
210	}
211	/**
212	* Returns the IDs for multiple actions based on name + type values.
213	*
214	* @param array $actionsNameAndType Array like `[ ['name' => '...', 'type' => 1], ... ]`
215	* @return array\|false Array of DB rows w/ columns: idaction, type, name.
216	*/
217	public function getIdsAction($actionsNameAndType)
218	{
219	$sql = "SELECT `idaction`, `type`, `name` FROM `" . Common::prefixTable('log_action') . "` WHERE";
220	$bind = [];
221	$i = 0;
222	foreach ($actionsNameAndType as $actionNameType) {
223	$name = $actionNameType['name'];
224	if (empty($name)) {
225	continue;
226	}
227	if ($i > 0) {
228	$sql .= " OR";
229	}
230	$sql .= " " . $this->getSqlConditionToMatchSingleAction() . " ";
231	$bind[] = $name;
232	$bind[] = $name;
233	$bind[] = $actionNameType['type'];
234	$i++;
235	}
236	// Case URL & Title are empty
237	if (empty($bind)) {
238	return \false;
239	}
240	$rows = $this->getDb()->fetchAll($sql, $bind);
241	$actionsPerType = [];
242	foreach ($rows as $row) {
243	$name = $row['name'];
244	$type = $row['type'];
245	if (!isset($actionsPerType[$type])) {
246	$actionsPerType[$type] = [];
247	}
248	if (!isset($actionsPerType[$type][$name])) {
249	$actionsPerType[$type][$name] = $row;
250	} elseif ($row['idaction'] < $actionsPerType[$type][$name]['idaction']) {
251	// keep the lowest idaction for this type, name
252	$actionsPerType[$type][$name] = $row;
253	}
254	}
255	$actionsToReturn = [];
256	foreach ($actionsPerType as $type => $actionsPerName) {
257	foreach ($actionsPerName as $actionPerName) {
258	$actionsToReturn[] = $actionPerName;
259	}
260	}
261	return $actionsToReturn;
262	}
263	/**
264	* Update an existing ecommerce item in the conversion items table
265	*
266	* @param string $originalIdOrder
267	* @param array $newItem
268	*
269	* @throws Db\DbException
270	*/
271	public function updateEcommerceItem($originalIdOrder, $newItem)
272	{
273	$updateParts = $sqlBind = [];
274	foreach ($newItem as $name => $value) {
275	$updateParts[] = $name . " = ?";
276	$sqlBind[] = $value;
277	}
278	$parts = implode(', ', $updateParts);
279	$table = Common::prefixTable('log_conversion_item');
280	$sql = "UPDATE {$table} SET {$parts} WHERE idvisit = ? AND idorder = ? AND idaction_sku = ?";
281	$sqlBind[] = $newItem['idvisit'];
282	$sqlBind[] = $originalIdOrder;
283	$sqlBind[] = $newItem['idaction_sku'];
284	$this->getDb()->query($sql, $sqlBind);
285	}
286	/**
287	* Create new visit in the DB
288	*
289	* @param array $visit
290	*
291	* @return int
292	* @throws Db\DbException
293	*/
294	public function createVisit($visit)
295	{
296	$fields = array_keys($visit);
297	$fields = implode(", ", $fields);
298	$values = Common::getSqlStringFieldsArray($visit);
299	$table = Common::prefixTable('log_visit');
300	$sql = "INSERT INTO {$table} ({$fields}) VALUES ({$values})";
301	$bind = array_values($visit);
302	$db = $this->getDb();
303	$db->query($sql, $bind);
304	return $db->lastInsertId();
305	}
306	/**
307	* Update an existing visit in the DB
308	*
309	* @param int $idSite
310	* @param int $idVisit
311	* @param $valuesToUpdate
312	*
313	* @return bool
314	* @throws Db\DbException
315	*/
316	public function updateVisit($idSite, $idVisit, $valuesToUpdate)
317	{
318	[$updateParts, $sqlBind] = $this->fieldsToQuery($valuesToUpdate);
319	$parts = implode(', ', $updateParts);
320	$table = Common::prefixTable('log_visit');
321	$sqlQuery = "UPDATE {$table} SET {$parts} WHERE idsite = ? AND idvisit = ?";
322	$sqlBind[] = $idSite;
323	$sqlBind[] = $idVisit;
324	$db = $this->getDb();
325	$result = $db->query($sqlQuery, $sqlBind);
326	$wasInserted = $db->rowCount($result) != 0;
327	if (!$wasInserted) {
328	Common::printDebug("Visitor with this idvisit wasn't found in the DB.");
329	Common::printDebug("{$sqlQuery} --- ");
330	Common::printDebug($sqlBind);
331	}
332	return $wasInserted;
333	}
334	/**
335	* Update an existing action in the database
336	*
337	* @param $idLinkVa
338	* @param $valuesToUpdate
339	*
340	* @return bool\|void
341	* @throws Db\DbException
342	*/
343	public function updateAction($idLinkVa, $valuesToUpdate)
344	{
345	if (empty($idLinkVa)) {
346	return;
347	}
348	[$updateParts, $sqlBind] = $this->fieldsToQuery($valuesToUpdate);
349	$parts = implode(', ', $updateParts);
350	$table = Common::prefixTable('log_link_visit_action');
351	$sqlQuery = "UPDATE {$table} SET {$parts} WHERE idlink_va = ?";
352	$sqlBind[] = $idLinkVa;
353	$db = $this->getDb();
354	$result = $db->query($sqlQuery, $sqlBind);
355	$wasInserted = $db->rowCount($result) != 0;
356	if (!$wasInserted) {
357	Common::printDebug("Action with this idLinkVa wasn't found in the DB.");
358	Common::printDebug("{$sqlQuery} --- ");
359	Common::printDebug($sqlBind);
360	}
361	return $wasInserted;
362	}
363	public function updateIdVisitorInLogTable(string $logTable, string $idVisitor, array $conditions) : bool
364	{
365	if (empty($idVisitor) \|\| empty($conditions)) {
366	return \false;
367	}
368	$table = Common::prefixTable($logTable);
369	$sqlQuery = "UPDATE `{$table}` SET `idvisitor` = ? WHERE ";
370	$sqlConditions = [];
371	$sqlBind = [$idVisitor];
372	foreach ($conditions as $name => $value) {
373	$sqlConditions[] = $name . " = ?";
374	$sqlBind[] = $value;
375	}
376	$sqlQuery .= implode(' AND ', $sqlConditions);
377	$db = $this->getDb();
378	$result = $db->query($sqlQuery, $sqlBind);
379	return $db->rowCount($result) != 0;
380	}
381	/**
382	* Attempt to find an existing visit record in the database
383	*
384	* @param int $idSite
385	* @param string $configId
386	* @param string $idVisitor
387	* @param string $userId
388	* @param array $fieldsToRead
389	* @param bool $shouldMatchOneFieldOnly
390	* @param bool $isVisitorIdToLookup
391	* @param string $timeLookBack
392	* @param string $timeLookAhead
393	*
394	* @return array\|bool\|mixed
395	*/
396	public function findVisitor($idSite, $configId, $idVisitor, $userId, $fieldsToRead, $shouldMatchOneFieldOnly, $isVisitorIdToLookup, $timeLookBack, $timeLookAhead)
397	{
398	$selectFields = implode(', ', $fieldsToRead);
399	$select = "SELECT {$selectFields} ";
400	$from = "FROM `" . Common::prefixTable('log_visit') . "`";
401	// Two use cases:
402	// 1) there is no visitor ID so we try to match only on config_id (heuristics)
403	// Possible causes of no visitor ID: no browser cookie support, direct Tracking API request without visitor ID passed,
404	// importing server access logs with import_logs.py, etc.
405	// In this case we use config_id heuristics to try find the visitor in tahhhe past. There is a risk to assign
406	// this page view to the wrong visitor, but this is better than creating artificial visits.
407	// 2) there is a visitor ID and we trust it (config setting trust_visitors_cookies, OR it was set using &cid= in tracking API),
408	// and in these cases, we force to look up this visitor id
409	$configIdWhere = "visit_last_action_time >= ? AND visit_last_action_time <= ? AND idsite = ?";
410	$configIdbindSql = [$timeLookBack, $timeLookAhead, $idSite];
411	$visitorIdWhere = 'idsite = ? AND visit_last_action_time <= ?';
412	$visitorIdbindSql = [$idSite, $timeLookAhead];
413	if ($shouldMatchOneFieldOnly && $isVisitorIdToLookup) {
414	$visitRow = $this->findVisitorByVisitorId($idVisitor, $select, $from, $visitorIdWhere, $visitorIdbindSql);
415	} elseif ($shouldMatchOneFieldOnly) {
416	$visitRow = $this->findVisitorByConfigId($configId, $select, $from, $configIdWhere, $configIdbindSql);
417	} else {
418	if (!empty($idVisitor)) {
419	$visitRow = $this->findVisitorByVisitorId($idVisitor, $select, $from, $visitorIdWhere, $visitorIdbindSql);
420	} else {
421	$visitRow = \false;
422	}
423	if (empty($visitRow)) {
424	if (!empty($userId)) {
425	$configIdWhere .= ' AND ( user_id IS NULL OR user_id = ? )';
426	$configIdbindSql[] = $userId;
427	}
428	$visitRow = $this->findVisitorByConfigId($configId, $select, $from, $configIdWhere, $configIdbindSql);
429	}
430	}
431	return $visitRow;
432	}
433	/**
434	* Return true if a visit record exists for the idvisit key and site
435	*
436	* @param int $idSite
437	* @param int $idVisit
438	*
439	* @return bool
440	* @throws Exception
441	*/
442	public function hasVisit($idSite, $idVisit)
443	{
444	// will use INDEX index_idsite_idvisitor_time (idsite, idvisitor, visit_last_action_time)
445	$sql = 'SELECT idsite FROM `' . Common::prefixTable('log_visit') . '` WHERE idvisit = ? LIMIT 1';
446	$bindSql = [$idVisit];
447	$val = $this->getDb()->fetchOne($sql, $bindSql);
448	return $val == $idSite;
449	}
450	/**
451	* Attempt to find an existing visit record in the database by visitor id and passed query fragments
452	*
453	* @param string $idVisitor
454	* @param string $select
455	* @param string $from
456	* @param string $where
457	* @param array $bindSql
458	*
459	* @return array\|bool\|mixed
460	*/
461	private function findVisitorByVisitorId($idVisitor, $select, $from, $where, $bindSql)
462	{
463	$cache = \Piwik\Tracker\Cache::getCacheGeneral();
464	// use INDEX index_idsite_idvisitor_time (idsite, idvisitor, visit_last_action_time) if available
465	if (array_key_exists(self::CACHE_KEY_INDEX_IDSITE_IDVISITOR_TIME, $cache) && \true === $cache[self::CACHE_KEY_INDEX_IDSITE_IDVISITOR_TIME]) {
466	$from .= ' FORCE INDEX (index_idsite_idvisitor_time) ';
467	}
468	$where .= ' AND idvisitor = ?';
469	$bindSql[] = $idVisitor;
470	return $this->fetchVisitor($select, $from, $where, $bindSql);
471	}
472	/**
473	* Attempt to find an existing visit record in the database by config id and passed query fragments
474	*
475	* @param string $configId
476	* @param string $select
477	* @param string $from
478	* @param string $where
479	* @param array $bindSql
480	*
481	* @return array\|bool\|mixed
482	*/
483	private function findVisitorByConfigId($configId, $select, $from, $where, $bindSql)
484	{
485	// will use INDEX index_idsite_config_datetime (idsite, config_id, visit_last_action_time)
486	$where .= ' AND config_id = ?';
487	$bindSql[] = $configId;
488	return $this->fetchVisitor($select, $from, $where, $bindSql);
489	}
490	/**
491	* Retrieve a visit row from the database using the passed query fragments
492	*
493	* @param string $select
494	* @param string $from
495	* @param string $where
496	* @param array $bindSql
497	*
498	* @return array\|bool\|mixed
499	* @throws Db\DbException
500	*/
501	private function fetchVisitor($select, $from, $where, $bindSql)
502	{
503	$sql = "{$select} {$from} WHERE " . $where . "\n ORDER BY visit_last_action_time DESC\n LIMIT 1";
504	$visitRow = $this->getDb()->fetch($sql, $bindSql);
505	return $visitRow;
506	}
507	/**
508	* Returns true if the site doesn't have raw data.
509	*
510	* @param int $siteId
511	* @return bool
512	*/
513	public function isSiteEmpty($siteId)
514	{
515	$sql = sprintf('SELECT idsite FROM `%s` WHERE idsite = ? limit 1', Common::prefixTable('log_visit'));
516	$result = \Piwik\Db::fetchOne($sql, [$siteId]);
517	return $result == null;
518	}
519	/**
520	* Build an array of fields and bind values
521	*
522	* @param array $valuesToUpdate
523	*
524	* @return array[]
525	*/
526	private function fieldsToQuery($valuesToUpdate)
527	{
528	$updateParts = [];
529	$sqlBind = [];
530	foreach ($valuesToUpdate as $name => $value) {
531	// Case where bind parameters don't work
532	if ($value === $name . ' + 1') {
533	//$name = 'visit_total_events'
534	//$value = 'visit_total_events + 1';
535	$updateParts[] = " {$name} = {$value} ";
536	} else {
537	$updateParts[] = $name . " = ?";
538	$sqlBind[] = $value;
539	}
540	}
541	return [$updateParts, $sqlBind];
542	}
543	/**
544	* Delete an action record by key
545	*
546	* @param int $newActionId
547	*
548	* @throws Db\DbException
549	*/
550	private function deleteDuplicateAction($newActionId)
551	{
552	$sql = "DELETE FROM `" . Common::prefixTable('log_action') . "` WHERE idaction = ?";
553	$db = $this->getDb();
554	$db->query($sql, [$newActionId]);
555	}
556	/**
557	* Get the tracker DB object
558	*
559	* @return \Piwik\Db\|Db\Mysqli\|Db\Pdo\Mysql\|null
560	* @throws Db\DbException
561	*/
562	private function getDb()
563	{
564	return Tracker::getDatabase();
565	}
566	/**
567	* Get sql query where clauses used to match a single action
568	*
569	* @return string
570	*/
571	private function getSqlConditionToMatchSingleAction()
572	{
573	return "( hash = CRC32(?) AND name = ? AND type = ? )";
574	}
575	}
576