Proyectos de Subversion LeadersLinked - Antes de SPA

Rev

Rev 1 | Rev 630 | Ir a la última revisión | | Comparar con el anterior | Ultima modificación | Ver Log |

Rev Autor Línea Nro. Línea
1 www 1
<?php
2
 
3
declare(strict_types=1);
4
 
5
namespace LeadersLinked\Mapper;
6
 
7
use Laminas\Db\Adapter\AdapterInterface;
8
use Laminas\Db\Sql\Expression;
9
use Laminas\Log\LoggerInterface;
10
 
11
use LeadersLinked\Model\User;
12
use LeadersLinked\Mapper\Common\MapperCommon;
13
use LeadersLinked\Hydrator\ObjectPropertyHydrator;
14
use Laminas\Hydrator\ArraySerializableHydrator;
15
use Laminas\Paginator\Paginator;
16
use Laminas\Paginator\Adapter\DbSelect;
17
use Laminas\Db\ResultSet\HydratingResultSet;
18
use LeadersLinked\Model\UserType;
19
use LeadersLinked\Model\CompanyUser;
20
use LeadersLinked\Model\CompanyMicrolearningCapsuleUser;
21
 
22
 
23
class UserMapper extends MapperCommon
24
{
25
    const _TABLE = 'tbl_users';
26
 
27
    /**
28
     *
29
     * @var UserMapper
30
     */
31
    private static $_instance;
32
 
33
    /**
34
     *
35
     * @param AdapterInterface $adapter
36
     */
37
    private function __construct($adapter)
38
    {
39
        parent::__construct($adapter);
40
    }
41
 
42
    /**
43
     *
44
     * @param AdapterInterface $adapter
45
     * @return UserMapper
46
     */
47
    public static function getInstance($adapter)
48
    {
49
        if(self::$_instance == null) {
50
            self::$_instance = new UserMapper($adapter);
51
        }
52
        return self::$_instance;
53
    }
54
 
55
    /**
56
     *
57
     * @param string $uuid
58
     * @return User
59
     */
60
    public function fetchOneByUuid($uuid)
61
    {
62
        $prototype = new User();
63
        $select = $this->sql->select(self::_TABLE);
64
        $select->where->equalTo('uuid', $uuid);
65
 
66
 
67
        return $this->executeFetchOneObject($select, $prototype);
68
    }
69
 
70
    /**
71
     *
72
     * @param int $id
73
     * @return User
74
     */
75
    public function fetchOne($id)
76
    {
77
        $prototype = new User();
78
        $select = $this->sql->select(self::_TABLE);
79
        $select->where->equalTo('id', $id);
80
 
81
        return $this->executeFetchOneObject($select, $prototype);
82
    }
83
 
84
    /**
85
     *
86
     * @param string $email
87
     * @return void|User
88
     */
89
    public function fetchOneByEmail($email)
90
    {
91
        $prototype = new User();
92
        $select = $this->sql->select(self::_TABLE);
93
        $select->where->equalTo('email', $email);
94
 
95
        //echo $select->getSqlString($this->adapter->platform); exit;
96
 
97
        return $this->executeFetchOneObject($select, $prototype);
98
    }
99
 
100
    /**
101
     *
102
     * @param string $password_reset_key
103
     * @return void|User
104
     */
105
    public function fetchOneByPasswordResetKey($password_reset_key)
106
    {
107
        $prototype = new User();
108
        $select = $this->sql->select(self::_TABLE);
109
        $select->where->equalTo('password_reset_key', $password_reset_key);
110
 
111
        return $this->executeFetchOneObject($select, $prototype);
112
    }
113
 
114
    /**
115
     *
116
     * @param string $activation_key
117
     * @return void|User
118
     */
119
    public function fetchOneByActivationKey($activation_key)
120
    {
121
        $prototype = new User();
122
        $select = $this->sql->select(self::_TABLE);
123
        $select->where->equalTo('activation_key', $activation_key);
124
 
125
        return $this->executeFetchOneObject($select, $prototype);
126
    }
127
 
128
 
129
 
130
    /**
131
     *
132
     * @param string $keyword
133
     * @param int $current_user
134
     * @return User[]
135
     */
136
    public function fetchAllByKeyword($keyword, $current_user = 0)
137
    {
138
 
139
        $prototype = new User();
140
 
141
        $select = $this->sql->select(self::_TABLE);
142
 
143
        $select->where->equalTo('status', User::STATUS_ACTIVE);
144
        $select->where->notEqualTo('id', $current_user);
145
        $select->where->and->nest()
146
            ->like('first_name', '%' . $keyword . '%')
147
            ->or->like('last_name', '%' . $keyword . '%')
148
            ->or->like(new Expression("CONCAT(first_name,' ',last_name)"), '%'. $keyword . '%')
149
            ->unnest();
150
 
151
 
152
        return $this->executeFetchAllObject($select, $prototype);
153
    }
154
 
155
    /**
156
     *
157
     * @param int[] $ids
158
     * @param int $current_user
159
     * @param string $status
160
     * @return User[]
161
     */
162
    public function fetchAllByIds($ids, $current_user = 0, $status = User::STATUS_ACTIVE)
163
    {
164
 
165
        $prototype = new User();
166
        $select = $this->sql->select(self::_TABLE);
167
 
168
 
169
        $select->where->equalTo('status', User::STATUS_ACTIVE);
170
        $select->where->and->in('id',$ids)->equalTo('status', $status);
171
 
172
        if($current_user) {
173
            $select->where->and->notEqualTo('id ', $current_user);
174
        }
175
 
176
        return $this->executeFetchAllObject($select, $prototype);
177
    }
178
 
179
 
180
    /**
181
     *
182
     * @return User[]
183
     */
184
    public function fetchAllByActives()
185
    {
186
 
187
        $prototype = new User();
188
        $select = $this->sql->select(self::_TABLE);
189
 
190
 
191
        $select->where->equalTo('status', User::STATUS_ACTIVE);
192
        $select->where->equalTo('email_verified',User::EMAIL_VERIFIED_YES);
193
 
194
 
195
        return $this->executeFetchAllObject($select, $prototype);
196
    }
197
 
198
    /**
199
     *
200
     * @param int $user_id
201
     * @param string $password_reset_key
202
     * @return boolean
203
     */
204
    public function updatePasswordResetKey($user_id, $password_reset_key)
205
    {
206
        $values = [
207
            'password_reset_key' => $password_reset_key,
208
            'password_generated_on' => date('Y-m-d H:i:s'),
209
            'updated_on' => new Expression('NOW()')
210
        ];
211
 
212
        $update = $this->sql->update(self::_TABLE);
213
        $update->set($values);
214
        $update->where->equalTo('id', $user_id);
215
 
216
        return $this->executeUpdate($update);
217
    }
218
 
219
 
220
 
221
    /**
222
     *
223
     * @param User $user
224
     * @param string $password_hash
225
     * @return boolean
226
     */
227
    public function updatePassword($user, $password_hash)
228
    {
229
        $values = [
230
            'password'              => $password_hash,
231
            'password_reset_key'    => '',
232
            'password_updated_on'   => date('Y-m-d H:i:s'),
233
            'login_attempt'         => 0,
234
            'blocked'               => User::BLOCKED_NO,
235
            'updated_on'            => new Expression('NOW()')
236
        ];
237
 
238
        $update = $this->sql->update(self::_TABLE);
239
        $update->set($values);
240
        $update->where->equalTo('id', $user->id);
241
 
242
        return $this->executeUpdate($update);
243
    }
244
 
245
 
246
    /**
247
     *
248
     * @param User $user
249
     * @return boolean
250
     */
251
    public function unblock($user)
252
    {
253
        $values = [
254
            'login_attempt'         => 0,
255
            'blocked'               => User::BLOCKED_NO,
256
            'updated_on'            => new Expression('NOW()')
257
        ];
258
 
259
        $update = $this->sql->update(self::_TABLE);
260
        $update->set($values);
261
        $update->where->equalTo('id', $user->id);
262
 
263
        return $this->executeUpdate($update);
264
    }
265
 
266
 
267
 
268
 
269
    /**
270
     *
271
     * @param User $user
272
     * @param string $one_time_password
273
     * @return boolean
274
     */
275
    public function updateOneTimePassword($user, $one_time_password)
276
    {
277
        $values = [
278
            'one_time_password' => $one_time_password,
279
            'updated_on'        => new Expression('NOW()')
280
        ];
281
 
282
        $update = $this->sql->update(self::_TABLE);
283
        $update->set($values);
284
        $update->where->equalTo('id', $user->id);
285
 
286
        return $this->executeUpdate($update);
287
    }
288
 
289
 
290
    /**
291
     *
292
     * @param User $user
293
     * @return boolean
294
     */
295
    public function update($user)
296
    {
297
        $hydrator = new ObjectPropertyHydrator();
298
        $values = $hydrator->extract($user);
299
        $value = $this->removeEmpty($values);
300
 
301
        $values['updated_on'] = new Expression('NOW()') ;
302
 
303
        $update = $this->sql->update(self::_TABLE);
304
        $update->set($values);
305
        $update->where->equalTo('id',$user->id);
306
 
307
        return $this->executeUpdate($update);
308
    }
309
 
310
    /**
311
     *
312
     * @param User $user
313
     * @return boolean
314
     */
315
    public function updatePrivacy($user)
316
    {
317
        $values = [
318
            'show_in_search' => $user->show_in_search,
319
            'updated_on' => new Expression('NOW()')
320
        ];
321
 
322
        $update = $this->sql->update(self::_TABLE);
323
        $update->set($values);
324
        $update->where->equalTo('id',$user->id);
325
 
326
        return $this->executeUpdate($update);
327
    }
328
 
329
    /**
330
     *
331
     * @param User $user
332
     * @return boolean
333
     */
334
    public function updateBasic($user)
335
    {
336
        $values = [
337
            'first_name' => $user->first_name,
338
            'last_name' => $user->last_name,
339
            'phone' => $user->phone,
340
            'gender' => $user->gender,
341
            'updated_on' => new Expression('NOW()')
342
        ];
343
 
344
        $update = $this->sql->update(self::_TABLE);
345
        $update->set($values);
346
        $update->where->equalTo('id',$user->id);
347
 
348
        return $this->executeUpdate($update);
349
    }
350
 
351
 
352
    /**
353
     *
354
     * @param User $user
355
     * @return boolean
356
     */
357
    public function updateImage($user)
358
    {
359
        $values = [
360
            'image' => $user->image,
361
            'updated_on' => new Expression('NOW()')
362
        ];
363
 
364
        $update = $this->sql->update(self::_TABLE);
365
        $update->set($values);
366
        $update->where->equalTo('id',$user->id);
367
 
368
        return $this->executeUpdate($update);
369
    }
370
 
371
    /**
372
     *
373
     * @param User $user
374
     * @return boolean
375
     */
376
    public function updateLocation($user)
377
    {
378
        $values = [
379
            'location_id' => $user->location_id,
380
            'updated_on' => new Expression('NOW()')
381
        ];
382
 
383
        $update = $this->sql->update(self::_TABLE);
384
        $update->set($values);
385
        $update->where->equalTo('id',$user->id);
386
 
387
        return $this->executeUpdate($update);
388
    }
389
 
390
    /**
391
     *
392
     * @param int $user_id
393
     * @return boolean
394
     */
395
    public function activateAccount($user_id)
396
    {
397
        $values = [
398
            'email_verified'    => User::EMAIL_VERIFIED_YES,
399
            'status'            => User::STATUS_ACTIVE,
400
            'activation_key'    => '',
401
            'updated_on' => new Expression('NOW()')
402
        ];
403
 
404
        $update = $this->sql->update(self::_TABLE);
405
        $update->set($values);
406
        $update->where->equalTo('id', $user_id);
407
 
408
        return $this->executeUpdate($update);
409
    }
410
 
411
    /**
412
     *
413
     * @param User $user
414
     * @return boolean
415
     */
416
    public function insert($user)
417
    {
418
 
419
        $hydrator = new ObjectPropertyHydrator();
420
        $values = $hydrator->extract($user);
421
        $values = $this->removeEmpty($values);
422
 
423
        $insert = $this->sql->insert(self::_TABLE);
424
        $insert->values($values);
425
 
426
        $response = $this->executeInsert($insert);
427
        if($response) {
428
            $user->id = $this->lastInsertId;
429
        }
430
 
431
        return $response;
432
    }
433
 
434
 
435
    /**
436
     *
437
     * @return boolean
438
     */
439
    public function truncate()
440
    {
441
        $sql = sprintf('TRUNCATE TABLE `%s` ', self::_TABLE);
442
        return $this->executeSentenceWithParameters($sql);
443
    }
444
 
445
    /**
446
     *
447
     * @param int $company_id
448
     * @param string $status
449
     * @param string $search
450
     * @param int $page
451
     * @param int $records_per_page
452
     * @param string $order_field
453
     * @param string $order_direction
454
     * @return Paginator
455
     */
456
    public function fetchAllDataTableByCompanyId($company_id, $status = '', $search, $page = 1, $records_per_page = 10, $order_field= 'name', $order_direction = 'ASC')
457
    {
458
        $select = $this->sql->select();
459
        $select->columns(['status', 'backend', 'creator']);
460
        $select->from(['tb1' => CompanyUserMapper::_TABLE]);
461
        $select->join(['tb2' => self::_TABLE] , 'tb1.user_id = tb2.id', [
462
            'id', 'uuid', 'first_name', 'last_name', 'email', 'last_activity_on', 'image',
463
            'blocked', 'login_attempt', 'email_verified'
464
 
465
        ]);
466
 
467
        $select->where->equalTo('tb1.company_id', $company_id);
468
        $select->where->equalTo('tb1.owner', CompanyUser::OWNER_NO);
469
 
470
        if($status) {
471
            $select->where->equalTo('tb1.status', $status);
472
 
473
        }
474
 
475
        if($search) {
476
            $select->where->nest()
477
            ->like('first_name', '%' . $search . '%')
478
            ->or->like('last_name', '%' . $search . '%')
479
            ->or->like('email', '%' . $search . '%')
480
            ->unnest();
481
 
482
        }
483
 
484
 
485
        $select->order($order_field . ' ' . $order_direction);
486
 
487
        $hydrator   = new ArraySerializableHydrator();
488
        $resultset  = new HydratingResultSet($hydrator);
489
 
490
        $adapter = new DbSelect($select, $this->sql, $resultset);
491
        $paginator = new Paginator($adapter);
492
        $paginator->setItemCountPerPage($records_per_page);
493
        $paginator->setCurrentPageNumber($page);
494
 
495
 
496
        return $paginator;
497
    }
498
 
499
 
500
    /**
501
     *
502
     * @param int $company_id
503
     * @param string $search
504
     * @param int $page
505
     * @param int $records_per_page
506
     * @param string $order_field
507
     * @param string $order_direction
508
     * @return Paginator
509
     */
510
    public function fetchAllDataTableStudensByCompanyId($company_id,  $search, $page = 1, $records_per_page = 10, $order_field= 'name', $order_direction = 'ASC')
511
    {
512
        $date = date('Y-m-d');
513
 
514
        $selectCapsuleUser = $this->getSql()->select();
515
        $selectCapsuleUser->columns(['user_id' => new Expression('DISTINCT(user_id)')  ]);
516
        $selectCapsuleUser->from(CompanyMicrolearningCapsuleUserMapper::_TABLE);
517
        $selectCapsuleUser->where->equalTo('company_id', $company_id);
518
        $selectCapsuleUser->where->nest->equalTo('access', CompanyMicrolearningCapsuleUser::ACCESS_UNLIMITED)->or->nest()
519
        ->equalTo('access', CompanyMicrolearningCapsuleUser::ACCESS_PAY_PERIOD)
520
        ->and->lessThanOrEqualTo(new Expression('DATE(paid_from)'), $date)
521
        ->and->greaterThanOrEqualTo(new Expression('DATE(paid_to)'), $date )->unnest()->unnest();
522
 
523
        //echo $select->getSqlString($this->adapter->platform); exit;
524
 
525
        $select = $this->sql->select();
526
        $select->columns(['id' , 'uuid', 'first_name', 'last_name', 'email','blocked']);
527
        $select->from(UserMapper::_TABLE);
528
        $select->where->in('id', $selectCapsuleUser);
529
 
530
 
531
        if($search) {
532
            $select->where->nest()
533
            ->like('first_name', '%' . $search . '%')
534
            ->or->like('last_name', '%' . $search . '%')
535
            ->or->like('email', '%' . $search . '%')
536
            ->unnest();
537
 
538
        }
539
 
540
 
541
        $select->order($order_field . ' ' . $order_direction);
542
 
543
        $hydrator   = new ArraySerializableHydrator();
544
        $resultset  = new HydratingResultSet($hydrator);
545
 
546
        $adapter = new DbSelect($select, $this->sql, $resultset);
547
        $paginator = new Paginator($adapter);
548
        $paginator->setItemCountPerPage($records_per_page);
549
        $paginator->setCurrentPageNumber($page);
550
 
551
 
552
        return $paginator;
553
    }
554
 
555
    /**
556
     *
557
     * @param string $search
558
     * @return User[]
559
     */
560
    public function fetchAllSuggest($search)
561
    {
562
 
563
        $select = $this->sql->select();
564
        $select->from(self::_TABLE);
565
        $select->where->equalTo('status', User::STATUS_ACTIVE);
566
        $select->where->equalTo('email_verified', User::EMAIL_VERIFIED_YES);
567
 
568
        if($search) {
569
            $select->where->nest()
570
            ->like('first_name', '%' . $search . '%')
571
            ->or->like('last_name', '%' . $search . '%')
572
            ->or->like('email', '%' . $search . '%')
573
            ->unnest();
574
 
575
        }
576
 
577
        $select->order(['first_name', 'last_name']);
578
 
579
        // echo $select->getSqlString($this->adapter->platform); exit;
580
 
581
        $prototype = new User();
582
 
583
        return $this->executeFetchAllObject($select, $prototype);
584
    }
585
 
586
 
587
    /**
588
     *
589
     * @param int $company_id
590
     * @param string $search
591
     * @return User[]
592
     */
593
    public function fetchAllSuggestForInvitationByCompanyId($company_id, $search)
594
    {
595
        $selectCompanyUsers = $this->sql->select(CompanyUserMapper::_TABLE);
596
        $selectCompanyUsers->columns(['user_id']);
597
        $selectCompanyUsers->where->equalTo('company_id', $company_id);
598
        $selectCompanyUsers->where->in('status', [
599
            CompanyUser::STATUS_ACCEPTED,
600
            CompanyUser::STATUS_PENDING,
601
            CompanyUser::STATUS_SENT,
602
 
603
        ]);
604
 
605
        //echo $selectCompanyUsers->getSqlString($this->adapter->platform); exit;
606
 
607
        $select = $this->sql->select();
608
        $select->from(self::_TABLE);
609
        $select->where->notIn('id', $selectCompanyUsers);
610
        $select->where->equalTo('status', User::STATUS_ACTIVE);
611
        $select->where->equalTo('email_verified', User::EMAIL_VERIFIED_YES);
612
 
613
        if($search) {
614
            $select->where->nest()
615
            ->like('first_name', '%' . $search . '%')
616
            ->or->like('last_name', '%' . $search . '%')
617
            ->or->like('email', '%' . $search . '%')
618
            ->unnest();
619
 
620
        }
621
 
622
        $select->order(['first_name', 'last_name']);
623
 
624
       // echo $select->getSqlString($this->adapter->platform); exit;
625
 
626
        $prototype = new User();
627
 
628
        return $this->executeFetchAllObject($select, $prototype);
629
    }
630
 
631
    /**
632
     *
633
     * @param int $group_id
634
     * @param string $search
635
     * @return User[]
636
     */
637
    public function fetchAllSuggestForInvitationByGroupId($group_id, $search)
638
    {
639
        $selectGroupMembers = $this->sql->select(GroupMemberMapper::_TABLE);
640
        $selectGroupMembers->columns(['user_id']);
641
        $selectGroupMembers->where->equalTo('group_id', $group_id);
642
        $selectGroupMembers->where->in('status', [
643
         CompanyUser::STATUS_ACCEPTED,
644
        ]);
645
 
646
        //echo $selectGroupMembers->getSqlString($this->adapter->platform); exit;
647
 
648
        $select = $this->sql->select();
649
        $select->from(self::_TABLE);
650
        $select->where->notIn('id', $selectGroupMembers);
651
        $select->where->equalTo('status', User::STATUS_ACTIVE);
652
        $select->where->equalTo('email_verified', User::EMAIL_VERIFIED_YES);
653
 
654
        if($search) {
655
            $select->where->nest()
656
            ->like('first_name', '%' . $search . '%')
657
            ->or->like('last_name', '%' . $search . '%')
658
            ->or->like('email', '%' . $search . '%')
659
            ->unnest();
660
 
661
        }
662
 
663
        $select->order(['first_name', 'last_name']);
664
 
665
        // echo $select->getSqlString($this->adapter->platform); exit;
666
 
667
        $prototype = new User();
668
 
669
        return $this->executeFetchAllObject($select, $prototype);
670
    }
671
 
672
    /**
673
     *
674
     * @param string $search
675
     * @param int $page
676
     * @param int $records_per_page
677
     * @param string $order_field
678
     * @param string $order_direction
679
     * @return Paginator
680
     */
681
    public function fetchAllDataTable($search, $page = 1, $records_per_page = 10, $order_field= 'name', $order_direction = 'ASC')
682
    {
683
        $prototype = new User();
684
        $select = $this->sql->select(self::_TABLE);
685
        $select->where->in('usertype_id', [UserType::ADMIN, UserType::USER]);
686
 
687
 
688
        if($search) {
689
            $select->where->nest()
690
            ->like('first_name', '%' . $search . '%')
691
            ->or->like('last_name', '%' . $search . '%')
692
            ->or->like('email', '%' . $search . '%')
693
            ->unnest();
694
 
695
        }
696
 
697
 
698
        $select->order($order_field . ' ' . $order_direction);
699
 
700
        $hydrator   = new ObjectPropertyHydrator();
701
        $resultset  = new HydratingResultSet($hydrator, $prototype);
702
 
703
        $adapter = new DbSelect($select, $this->sql, $resultset);
704
        $paginator = new Paginator($adapter);
705
        $paginator->setItemCountPerPage($records_per_page);
706
        $paginator->setCurrentPageNumber($page);
707
 
708
 
709
        return $paginator;
710
    }
711
 
712
    /**
713
     *
714
     * @param int $id
715
     * @return boolean
716
     */
717
    public function updateChatOnlineStatus($id)
718
    {
719
        $update = $this->sql->update(self::_TABLE);
720
        $update->set([
721
            'online' => 1,
722
        ]);
723
        $update->where->equalTo('id', $id);
724
 
725
        return $this->executeUpdate($update);
726
    }
727
 
728
    /**
729
     *
730
     * @param int $id
731
     * @return boolean
732
     */
733
    public function updateChatOfflineStatus($id)
734
    {
735
        $update = $this->sql->update(self::_TABLE);
736
        $update->set([
737
            'online' => 0,
738
        ]);
739
        $update->where->equalTo('id', $id);
740
 
741
        return $this->executeUpdate($update);
742
    }
743
 
744
    /**
745
     *
746
     * @param int $id
747
     * @return boolean
748
     */
749
    public function updateLastActivity($id)
750
    {
751
        $update = $this->sql->update(self::_TABLE);
752
        $update->set([
753
            'last_activity_on' => new Expression('NOW()'),
754
        ]);
755
        $update->where->equalTo('id', $id);
756
 
757
        return $this->executeUpdate($update);
758
    }
759
 
760
 
761
    /**
762
     *
763
     * @param int $id
764
     * @return boolean
765
     */
766
    public function updateLastHeartBeat($id)
767
    {
768
        $update = $this->sql->update(self::_TABLE);
769
        $update->set([
770
            'last_heart_beat_at' => new Expression('NOW()'),
242 efrain 771
            'online' => 1,
772
 
1 www 773
        ]);
774
        $update->where->equalTo('id', $id);
775
 
776
        return $this->executeUpdate($update);
777
    }
242 efrain 778
 
779
    /**
780
     *
781
     * @param int $id
782
     * @return boolean
783
     */
784
    public function emailVerifyAndActive($id)
785
    {
786
        $update = $this->sql->update(self::_TABLE);
787
        $update->set([
788
            'email_verified' => User::EMAIL_VERIFIED_YES,
789
            'status' => User::STATUS_ACTIVE,
790
        ]);
791
        $update->where->equalTo('id', $id);
1 www 792
 
242 efrain 793
        return $this->executeUpdate($update);
794
    }
795
 
1 www 796
}