Spaces:
No application file
No application file
File size: 4,768 Bytes
d2897cd |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 |
<?php
namespace Mautic\CoreBundle\Entity;
use Doctrine\DBAL\Exception as DBALException;
/**
* @extends CommonRepository<IpAddress>
*/
class IpAddressRepository extends CommonRepository
{
/**
* Count how many unique IP addresses is there.
*/
public function countIpAddresses(): int
{
$q = $this->createQueryBuilder('i');
$q->select('COUNT(DISTINCT i.id) as unique');
$results = $q->getQuery()->getSingleResult();
if (!isset($results['unique'])) {
return 0;
}
return (int) $results['unique'];
}
/**
* Get IP addresses that are not being used in any other table.
*
* @return array<int, array<int>>
*
* @throws DBALException
*/
public function getUnusedIpAddressesIds(int $limit): array
{
$prefix = MAUTIC_TABLE_PREFIX;
$sql = <<<SQL
SELECT {$prefix}ip_addresses.id FROM {$prefix}ip_addresses
LEFT JOIN {$prefix}asset_downloads
ON {$prefix}asset_downloads.ip_id = {$prefix}ip_addresses.id
LEFT JOIN {$prefix}campaign_lead_event_log
ON {$prefix}campaign_lead_event_log.ip_id = {$prefix}ip_addresses.id
LEFT JOIN {$prefix}email_stats
ON {$prefix}email_stats.ip_id = {$prefix}ip_addresses.id
LEFT JOIN {$prefix}email_stats_devices
ON {$prefix}email_stats_devices.ip_id = {$prefix}ip_addresses.id
LEFT JOIN {$prefix}form_submissions
ON {$prefix}form_submissions.ip_id = {$prefix}ip_addresses.id
LEFT JOIN {$prefix}lead_ips_xref
ON {$prefix}lead_ips_xref.ip_id = {$prefix}ip_addresses.id
LEFT JOIN {$prefix}lead_points_change_log
ON {$prefix}lead_points_change_log.ip_id = {$prefix}ip_addresses.id
LEFT JOIN {$prefix}page_hits
ON {$prefix}page_hits.ip_id = {$prefix}ip_addresses.id
LEFT JOIN {$prefix}point_lead_action_log
ON {$prefix}point_lead_action_log.ip_id = {$prefix}ip_addresses.id
LEFT JOIN {$prefix}point_lead_event_log
ON {$prefix}point_lead_event_log.ip_id = {$prefix}ip_addresses.id
LEFT JOIN {$prefix}push_notification_stats
ON {$prefix}push_notification_stats.ip_id = {$prefix}ip_addresses.id
LEFT JOIN {$prefix}sms_message_stats
ON {$prefix}sms_message_stats.ip_id = {$prefix}ip_addresses.id
LEFT JOIN {$prefix}stage_lead_action_log
ON {$prefix}stage_lead_action_log.ip_id = {$prefix}ip_addresses.id
LEFT JOIN {$prefix}video_hits
ON {$prefix}video_hits.ip_id = {$prefix}ip_addresses.id
WHERE {$prefix}asset_downloads.id IS NULL
AND {$prefix}campaign_lead_event_log.id IS NULL
AND {$prefix}email_stats.id IS NULL
AND {$prefix}email_stats_devices.id IS NULL
AND {$prefix}form_submissions.id IS NULL
AND {$prefix}lead_ips_xref.lead_id IS NULL
AND {$prefix}lead_points_change_log.id IS NULL
AND {$prefix}page_hits.id IS NULL
AND {$prefix}point_lead_action_log.point_id IS NULL
AND {$prefix}point_lead_event_log.event_id IS NULL
AND {$prefix}push_notification_stats.id IS NULL
AND {$prefix}sms_message_stats.id IS NULL
AND {$prefix}stage_lead_action_log.stage_id IS NULL
AND {$prefix}video_hits.id IS NULL
LIMIT :limit
SQL;
$params = ['limit' => $limit];
$types = ['limit' => \PDO::PARAM_INT];
return $this->_em->getConnection()->executeQuery($sql, $params, $types)->fetchFirstColumn();
}
/**
* @param array<int, array<int>> $ids
*
* @throws DBALException
*/
public function deleteUnusedIpAddresses(array $ids): int
{
$prefix = MAUTIC_TABLE_PREFIX;
$ids = implode(',', $ids);
$deleteSql = <<<SQL
DELETE FROM {$prefix}ip_addresses WHERE {$prefix}ip_addresses.id IN ({$ids});
SQL;
return $this->_em->getConnection()->executeStatement($deleteSql);
}
/**
* @throws DBALException
*/
public function anonymizeAllIpAddress(): int
{
$table_name = $this->getTableName();
$sql = "UPDATE {$table_name} SET ip_address = '*.*.*.*', ip_details = 'N;' WHERE ip_address != '*.*.*.*'";
$conn = $this->getEntityManager()->getConnection();
return $conn->executeQuery($sql)->rowCount();
}
}
|