|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
declare(strict_types=1); |
|
4
|
|
|
|
|
5
|
|
|
namespace Application\Repository; |
|
6
|
|
|
|
|
7
|
|
|
use Application\Enum\ProductType; |
|
8
|
|
|
use Application\Model\Organization; |
|
9
|
|
|
|
|
10
|
|
|
/** |
|
11
|
|
|
* @extends AbstractRepository<Organization> |
|
12
|
|
|
*/ |
|
13
|
|
|
class OrganizationRepository extends AbstractRepository |
|
14
|
|
|
{ |
|
15
|
|
|
/** |
|
16
|
|
|
* Apply the organizations accesses to all users. |
|
17
|
|
|
* |
|
18
|
|
|
* - If a user email matches the org pattern and the org access is better, then user will get the org access |
|
19
|
|
|
* - If many organizations match then only the best one is used |
|
20
|
|
|
*/ |
|
21
|
12 |
|
public function applyOrganizationAccesses(): void |
|
22
|
|
|
{ |
|
23
|
12 |
|
$connection = $this->getEntityManager()->getConnection(); |
|
24
|
|
|
|
|
25
|
12 |
|
$sqlUpgrade = <<<STRING |
|
26
|
|
|
UPDATE user |
|
27
|
|
|
INNER JOIN ( |
|
28
|
|
|
SELECT user.email, MAX(product.review_number) AS bestReviewNumber |
|
29
|
|
|
FROM organization |
|
30
|
|
|
INNER JOIN product ON organization.subscription_last_review_id = product.id |
|
31
|
|
|
INNER JOIN user ON user.email REGEXP organization.pattern |
|
32
|
|
|
GROUP BY user.email |
|
33
|
|
|
) AS bestMatch ON user.email = bestMatch.email |
|
34
|
|
|
INNER JOIN product AS orgProduct ON orgProduct.review_number = bestMatch.bestReviewNumber |
|
35
|
|
|
LEFT JOIN product AS userProduct ON userProduct.id = user.subscription_last_review_id |
|
36
|
|
|
SET |
|
37
|
|
|
user.subscription_last_review_id = IF(userProduct.id IS NULL OR orgProduct.review_number > userProduct.review_number, orgProduct.id, userProduct.id), |
|
38
|
|
|
user.subscription_type = IF(user.subscription_type IN (:paper, :both), :both, :digital) |
|
39
|
12 |
|
STRING; |
|
40
|
|
|
|
|
41
|
12 |
|
$params = [ |
|
42
|
12 |
|
'paper' => ProductType::Paper->value, |
|
43
|
12 |
|
'both' => ProductType::Both->value, |
|
44
|
12 |
|
'digital' => ProductType::Digital->value, |
|
45
|
12 |
|
]; |
|
46
|
|
|
|
|
47
|
12 |
|
$connection->executeStatement($sqlUpgrade, $params); |
|
48
|
|
|
} |
|
49
|
|
|
} |
|
50
|
|
|
|
Ecodev /
artisans
