Passed
Push — master ( 2aa04c...969584 )
by Adrien
08:04
created

Importer::toUpper()   A

Complexity

Conditions 1
Paths 1

Size

Total Lines 5
Code Lines 2

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 3
CRAP Score 1

Importance

Changes 0
Metric Value
cc 1
eloc 2
nc 1
nop 1
dl 0
loc 5
ccs 3
cts 3
cp 1
crap 1
rs 10
c 0
b 0
f 0
1
<?php
2
3
declare(strict_types=1);
4
5
namespace Application\Service;
6
7
use Application\DBAL\Types\MembershipType;
8
use Application\DBAL\Types\ProductTypeType;
9
use Application\Model\Organization;
10
use Application\Model\User;
11
use Application\Repository\OrganizationRepository;
12
use Doctrine\DBAL\Connection;
13
use Ecodev\Felix\Api\Exception;
14
use Laminas\Validator\EmailAddress;
15
use Throwable;
16
17
/**
18
 * Service to import users from CSV with maximal performance.
19
 *
20
 * Users are never deleted, even though technically they should be, to limit the loss of
21
 * data in case of human error in the incoming files. Because it could mean losing all historic
22
 * of purchases.
23
 *
24
 * On the other hand, organizations are **always** deleted, because they don't have any related objects,
25
 * and they are not editable (not even visible) in any way in the app.
26
 */
27
class Importer
28
{
29
    private int $lineNumber = 0;
30
31
    private array $reviewByNumber = [];
32
33
    private array $countryByName = [];
34
35
    private Connection $connection;
36
37
    private int $updatedUsers = 0;
38
39
    private int $updatedOrganizations = 0;
40
41
    private int $deletedOrganizations = 0;
42
43
    private array $seenEmails = [];
44
45
    private array $seenPatterns = [];
46
47
    private ?int $currentUser;
48
49 16
    public function import(string $filename): array
50
    {
51 16
        $start = microtime(true);
52 16
        $this->connection = _em()->getConnection();
53 16
        $this->fetchReviews();
54 16
        $this->fetchCountries();
55 16
        $this->currentUser = User::getCurrent() ? User::getCurrent()->getId() : null;
56 16
        $this->updatedUsers = 0;
57 16
        $this->updatedOrganizations = 0;
58 16
        $this->deletedOrganizations = 0;
59 16
        $this->seenEmails = [];
60 16
        $this->seenPatterns = [];
61
62 16
        if (!file_exists($filename)) {
63 1
            throw new Exception('File not found: ' . $filename);
64
        }
65
66 15
        $file = fopen($filename, 'rb');
67 15
        if ($file === false) {
68
            throw new Exception('Could not read file: ' . $filename);
69
        }
70
71 15
        $this->skipBOM($file);
72
73
        try {
74 15
            $this->connection->beginTransaction();
75 15
            $this->markToDelete();
76 15
            $this->read($file);
77 5
            $this->deleteOldOrganizations();
78
79
            // Give user automatic access via organization
80
            /** @var OrganizationRepository $organizationRepository */
81 5
            $organizationRepository = _em()->getRepository(Organization::class);
82 5
            $organizationRepository->applyOrganizationAccesses();
83
84 5
            $this->connection->commit();
85 10
        } catch (Throwable $exception) {
86 10
            $this->connection->rollBack();
87
88 10
            throw $exception;
89 5
        } finally {
90 15
            fclose($file);
91
        }
92
93 5
        $totalUsers = (int) $this->connection->fetchColumn('SELECT COUNT(*) FROM user');
94 5
        $totalOrganizations = (int) $this->connection->fetchColumn('SELECT COUNT(*) FROM organization');
95
96 5
        $time = round(microtime(true) - $start, 1);
97
98
        return [
99 5
            'updatedUsers' => $this->updatedUsers,
100 5
            'updatedOrganizations' => $this->updatedOrganizations,
101 5
            'deletedOrganizations' => $this->deletedOrganizations,
102 5
            'totalUsers' => $totalUsers,
103 5
            'totalOrganizations' => $totalOrganizations,
104 5
            'totalLines' => $this->lineNumber,
105 5
            'time' => $time,
106
        ];
107
    }
108
109 16
    private function fetchReviews(): void
110
    {
111 16
        $records = $this->connection->fetchAll('SELECT id, review_number FROM product WHERE review_number IS NOT NULL');
112
113 16
        $this->reviewByNumber = [];
114 16
        foreach ($records as $r) {
115 16
            $this->reviewByNumber[$r['review_number']] = $r['id'];
116
        }
117 16
    }
118
119 16
    private function fetchCountries(): void
120
    {
121 16
        $records = $this->connection->fetchAll('SELECT id, LOWER(name) AS name FROM country');
122
123 16
        $this->countryByName = [];
124 16
        foreach ($records as $r) {
125 16
            $r['name'] = $this->toUpper($r['name']);
126 16
            $this->countryByName[$r['name']] = $r;
127
        }
128 16
    }
129
130
    /**
131
     * @param resource $file
132
     */
133 15
    private function skipBOM($file): void
134
    {
135
        // Consume BOM, but if not BOM, rewind to beginning
136 15
        if (fgets($file, 4) !== "\xEF\xBB\xBF") {
137 13
            rewind($file);
138
        }
139 15
    }
140
141
    /**
142
     * @param resource $file
143
     */
144 15
    private function read($file): void
145
    {
146 15
        $this->lineNumber = 0;
147 15
        $expectedColumnCount = 14;
148 15
        while ($line = fgetcsv($file, 0, "\t")) {
149 15
            ++$this->lineNumber;
150
151 15
            $actualColumnCount = count($line);
152 15
            if ($actualColumnCount !== $expectedColumnCount) {
153 1
                $this->throw("Doit avoir exactement $expectedColumnCount colonnes, mais en a " . $actualColumnCount);
154
            }
155
156
            // un-escape all fields
157 14
            $line = array_map(fn ($r) => html_entity_decode($r), $line);
158
159
            [
160 14
                $email,
161
                $pattern,
162
                $subscriptionType,
163
                $lastReviewNumber,
164
                $ignored,
165
                $firstName,
166
                $lastName,
167
                $street,
168
                $street2,
169
                $postcode,
170
                $locality,
171
                $country,
172
                $phone,
173
                $membership,
174
            ] = $line;
175
176 14
            if (!$email && !$pattern) {
177 1
                $this->throw('Il faut soit un email, soit un pattern, mais aucun existe');
178
            }
179
180 13
            $lastReviewId = $this->readReviewId($lastReviewNumber);
181
182 11
            if ($email) {
183 9
                $this->assertEmail($email);
184 8
                $membership = $this->readMembership($membership);
185 8
                $country = $this->readCountryId($country);
186 7
                $subscriptionType = $this->readSubscriptionType($subscriptionType);
187
188 6
                $this->updateUser(
189 6
                    $email,
190
                    $subscriptionType,
191
                    $lastReviewId,
192
                    $membership,
193
                    $firstName,
194
                    $lastName,
195 6
                    trim(implode(' ', [$street, $street2])),
196
                    $postcode,
197
                    $locality,
198
                    $country,
199
                    $phone
200
                );
201
            }
202
203 8
            if ($pattern) {
204 4
                $this->assertPattern($pattern);
205
206 3
                $this->updateOrganization(
207 3
                    $pattern,
208
                    $lastReviewId
209
                );
210
            }
211
        }
212 5
    }
213
214 9
    private function assertEmail(string $email): void
215
    {
216 9
        $validator = new EmailAddress();
217 9
        if (!$validator->isValid($email)) {
218 1
            $this->throw("Ce n'est pas une addresse email valide : " . $email);
219
        }
220
221 8
        if (array_key_exists($email, $this->seenEmails)) {
222 1
            $this->throw('L\'email "' . $email . '" est dupliqué et a déjà été vu à la ligne ' . $this->seenEmails[$email]);
223
        }
224
225 8
        $this->seenEmails[$email] = $this->lineNumber;
226 8
    }
227
228 4
    private function assertPattern(string $pattern): void
229
    {
230 4
        if (@preg_match('~' . $pattern . '~', '') === false) {
231 1
            $this->throw("Ce n'est pas une expression régulière valide : " . $pattern);
232
        }
233
234 3
        if (array_key_exists($pattern, $this->seenPatterns)) {
235 1
            $this->throw('Le pattern "' . $pattern . '" est dupliqué et a déjà été vu à la ligne ' . $this->seenPatterns[$pattern]);
236
        }
237
238 3
        $this->seenPatterns[$pattern] = $this->lineNumber;
239 3
    }
240
241 13
    private function readReviewId(string $reviewNumber): ?string
242
    {
243 13
        if (!$reviewNumber) {
244 9
            return null;
245
        }
246
247 5
        if ($reviewNumber && !preg_match('~^\d+$~', $reviewNumber)) {
248 1
            $this->throw('Un numéro de revue doit être entièrement numérique, mais est : ' . $reviewNumber);
249
        }
250
251 4
        $reviewNumberNumeric = (int) $reviewNumber;
252 4
        if (!array_key_exists($reviewNumberNumeric, $this->reviewByNumber)) {
253 1
            $this->throw('Revue introuvable pour le numéro de revue : ' . $reviewNumber);
254
        }
255
256 3
        return $this->reviewByNumber[$reviewNumberNumeric];
257
    }
258
259 8
    private function readCountryId(string $country): ?string
260
    {
261 8
        if (!$country) {
262 5
            return null;
263
        }
264
265
        // Case insensitive match
266 4
        $upper = $this->toUpper($country);
267 4
        if (array_key_exists($upper, $this->countryByName)) {
268 3
            return $this->countryByName[$upper]['id'];
269
        }
270
271
        // Suggest our best guess, so user can fix their data without lookup up countries manually
272 1
        $best = 0;
273 1
        $bestGuess = 0;
274 1
        foreach ($this->countryByName as $r) {
275 1
            similar_text($upper, $r['name'], $percent);
276 1
            if ($percent > $best) {
277 1
                $best = $percent;
278 1
                $bestGuess = $r;
279
            }
280
        }
281
282 1
        $this->throw('Pays "' . $country . '" introuvable. Vouliez-vous dire "' . $bestGuess['name'] . '" ?');
283
    }
284
285 10
    private function throw(string $message): void
286
    {
287 10
        throw new Exception('A la ligne ' . $this->lineNumber . ' : ' . $message);
288
    }
289
290 5
    private function deleteOldOrganizations(): void
291
    {
292 5
        $sql = 'DELETE FROM organization WHERE should_delete';
293 5
        $this->deletedOrganizations += $this->connection->executeUpdate($sql);
294 5
    }
295
296 8
    private function readMembership($membership): string
297
    {
298 8
        if ($membership === '1') {
299 1
            return MembershipType::MEMBER;
300
        }
301
302 8
        return MembershipType::NONE;
303
    }
304
305 7
    private function readSubscriptionType(string $subscriptionType): ?string
306
    {
307 7
        if (!$subscriptionType) {
308 5
            return null;
309
        }
310
311 3
        if ($subscriptionType === 'Web') {
312 2
            return ProductTypeType::DIGITAL;
313
        }
314
315 2
        if ($subscriptionType === 'Papier') {
316 1
            return ProductTypeType::PAPER;
317
        }
318
319 2
        if ($subscriptionType === 'Papier/web') {
320 1
            return ProductTypeType::BOTH;
321
        }
322
323 1
        $this->throw('Le subscriptionType est invalide : ' . $subscriptionType);
324
    }
325
326 6
    private function updateUser(...$args): void
327
    {
328 6
        $sql = 'INSERT INTO user (
329
                            email,
330
                            subscription_type,
331
                            subscription_last_review_id,
332
                            membership,
333
                            first_name,
334
                            last_name,
335
                            street,
336
                            postcode,
337
                            locality,
338
                            country_id,
339
                            phone,
340
                            web_temporary_access,
341
                            should_delete,
342
                            password,
343
                            creator_id,
344
                            creation_date
345
                        )
346
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
347
                        ON DUPLICATE KEY UPDATE
348
                            email = VALUES(email),
349
                            subscription_type = VALUES(subscription_type),
350
                            subscription_last_review_id = VALUES(subscription_last_review_id),
351
                            membership = VALUES(membership),
352
                            first_name = VALUES(first_name),
353
                            last_name = VALUES(last_name),
354
                            street = VALUES(street),
355
                            postcode = VALUES(postcode),
356
                            locality = VALUES(locality),
357
                            country_id = VALUES(country_id),
358
                            phone = VALUES(phone),
359
                            web_temporary_access = VALUES(web_temporary_access),
360
                            should_delete = VALUES(should_delete),
361
                            updater_id = VALUES(creator_id),
362
                            update_date = NOW()';
363
364 6
        $params = $args;
365 6
        $params[] = false; // web_temporary_access
366 6
        $params[] = false; // should_delete
367 6
        $params[] = ''; // password
368 6
        $params[] = $this->currentUser;
369
370 6
        $changed = $this->connection->executeUpdate($sql, $params);
371
372 6
        if ($changed) {
373 6
            ++$this->updatedUsers;
374
        }
375 6
    }
376
377 3
    private function updateOrganization(...$args): void
378
    {
379 3
        $sql = 'INSERT INTO organization (pattern, subscription_last_review_id, creator_id, creation_date)
380
                        VALUES (?, ?, ?, NOW())
381
                        ON DUPLICATE KEY UPDATE
382
                        pattern = VALUES(pattern),
383
                        subscription_last_review_id = VALUES(subscription_last_review_id),
384
                        updater_id = VALUES(creator_id),
385
                        update_date = NOW()';
386
387 3
        $params = $args;
388 3
        $params[] = $this->currentUser;
389
390 3
        $changed = $this->connection->executeUpdate($sql, $params);
391
392 3
        if ($changed) {
393 3
            ++$this->updatedOrganizations;
394
        }
395 3
    }
396
397 15
    private function markToDelete(): void
398
    {
399 15
        $this->connection->executeUpdate('UPDATE user SET should_delete = 1');
400 15
        $this->connection->executeUpdate('UPDATE organization SET should_delete = 1');
401 15
    }
402
403
    /**
404
     * To upper without any accent
405
     */
406 16
    private function toUpper(string $name): string
407
    {
408 16
        $withoutAccent = iconv('UTF-8', 'ASCII//TRANSLIT', mb_strtolower($name));
409
410 16
        return trim(mb_strtoupper($withoutAccent));
411
    }
412
}
413