1# --
2# Copyright (C) 2001-2020 OTRS AG, https://otrs.com/
3# --
4# This software comes with ABSOLUTELY NO WARRANTY. For details, see
5# the enclosed file COPYING for license information (GPL). If you
6# did not receive this file, see https://www.gnu.org/licenses/gpl-3.0.txt.
7# --
8
9package Kernel::System::CommunicationLog::DB;
10
11use strict;
12use warnings;
13
14use Kernel::System::VariableCheck qw(:all);
15
16our @ObjectDependencies = (
17    'Kernel::Config',
18    'Kernel::System::Log',
19    'Kernel::System::DB',
20    'Kernel::System::Main',
21    'Kernel::System::DateTime',
22);
23
24=head1 NAME
25
26Kernel::System::CommunicationLog::DB - Database interface to Communication Log
27
28=head1 DESCRIPTION
29
30Global module to handle all the Database operations for the Communication Log.
31
32=head1 PUBLIC INTERFACE
33
34=head2 new()
35
36Create a Communication Log Database object. Do not use it directly, instead use:
37
38    my $CommunicationDBObject = $Kernel::OM->Get('Kernel::System::CommunicationLog::DB');
39
40=cut
41
42sub new {
43    my ( $Type, %Param ) = @_;
44
45    my $Self = {};
46    bless( $Self, $Type );
47
48    return $Self;
49}
50
51=head2 CommunicationCreate()
52
53Create a new communication element.
54
55    my $CommunicationID = $CommunicationDBObject->CommunicationCreate(
56        Transport       => '...',
57        Direction       => '...',
58        Status          => '...',
59        AccountType     => '...',
60        AccountID       => '...',
61    );
62
63Returns the created ID.
64
65=cut
66
67sub CommunicationCreate {
68    my ( $Self, %Param ) = @_;
69
70    # Check for required data.
71    for my $Argument (qw(Transport Direction Status)) {
72        if ( !$Param{$Argument} ) {
73            return $Self->_LogError("Need $Argument!");
74        }
75    }
76
77    return if !$Self->_IsValidStatus( Status => $Param{Status} );
78
79    return if !$Self->_IsValidDirection( Direction => $Param{Direction} );
80
81    my $RandomString = $Kernel::OM->Get('Kernel::System::Main')->GenerateRandomString(
82        Length => 32,
83    );
84
85    my $InsertFingerprint = $$ . '-' . $RandomString;
86
87    my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
88
89    return if !$DBObject->Do(
90        SQL => '
91            INSERT INTO communication_log
92            (insert_fingerprint, transport, direction, status, account_type, account_id, start_time)
93            VALUES ( ?, ?, ?, ?, ?, ?, current_timestamp )
94        ',
95        Bind => [
96            \$InsertFingerprint,
97            \$Param{Transport},
98            \$Param{Direction},
99            \$Param{Status},
100            \$Param{AccountType},
101            \$Param{AccountID},
102        ],
103    );
104
105    # get communication id
106    return if !$DBObject->Prepare(
107        SQL => 'SELECT id FROM communication_log
108            WHERE insert_fingerprint = ?
109            ORDER BY id DESC',
110        Bind  => [ \$InsertFingerprint ],
111        Limit => 1,
112    );
113
114    my $CommunicationID;
115    while ( my @Row = $DBObject->FetchrowArray() ) {
116        $CommunicationID = $Row[0];
117    }
118
119    # return if there is not article created
120    if ( !$CommunicationID ) {
121        return $Self->_LogError("Could not create a Communication element.");
122    }
123
124    return $CommunicationID;
125}
126
127=head2 CommunicationUpdate()
128
129Update Communication elements.
130
131    my $Result = $CommunicationDBObject->CommunicationUpdate(
132        CommunicationID => '...',
133        Status         => '[Successful|Warning|Failed]',
134    );
135
136Returns 1 or undef.
137
138=cut
139
140sub CommunicationUpdate {
141    my ( $Self, %Param, ) = @_;
142
143    for my $Argument (qw(CommunicationID Status)) {
144        if ( !$Param{$Argument} ) {
145            return $Self->_LogError("Need $Argument!");
146        }
147    }
148
149    # Check if is a valid status.
150    return if !$Self->_IsValidStatus( Status => $Param{Status} );
151
152    return if !$Kernel::OM->Get('Kernel::System::DB')->Do(
153        SQL => '
154            UPDATE communication_log
155            SET status = ?, end_time = current_timestamp
156            WHERE id = ?
157        ',
158        Bind => [
159            \$Param{Status}, \$Param{CommunicationID},
160        ],
161    );
162
163    return 1;
164}
165
166=head2 CommunicationList()
167
168List communication entries. If parameters are given, the listing will be filtered,
169otherwise all available entries will be returned.
170
171    my $CommunicationList = $CommunicationDBObject->CommunicationList(
172        Transport   => 'Email',      # (optional) Log type/transport/module
173        Direction   => 'Incoming',   # (optional) 'Incoming' or 'Outgoing'
174        Status      => 'Processing', # (optional) 'Successful', 'Processing' or 'Failed'
175        Date        => '2017-07-03', # (optional) List communications just from the given date.
176        StartDate   => '2017-07-03', # (optional) List communications starting from the given date.
177        OlderThan   => '2017-07-03', # (optional) List communications older than the given date.
178        Result      => 'ARRAY'       # (optional) Can be ARRAY or AVERAGE. ARRAY returns the results as
179                                     #            an array while AVERAGE returns the communication average in seconds.
180                                     #            Default: ARRAY
181        OrderBy     => 'Down',       # (optional) Down|Up; Default: Down
182        SortBy      => 'StartTime',  # (optional) Transport|Direction|Status|StartTime|EndTime|Duration, default: StartTime
183        AccountType => 'POP3',       # (optional) The used account type
184        AccountID   => 123,          # (optional) The used account id
185    );
186
187Returns:
188
189    $CommunicationList = [
190        {
191            CommunicationID => 33,
192            Transport       => 'Email',
193            Direction       => 'Incoming',
194            Status          => 'Failed',
195            AccountType     => 'IMAPS',
196            AccountID       => 1,
197            StartTime       => '2017-07-20 08:57:56',
198            EndTime         => '2017-07-20 08:57:57',
199            Duration        => 1,
200        },
201        {
202            CommunicationID => 34,
203            Transport       => 'Email',
204            Direction       => 'Outgoing',
205            Status          => 'Successful',
206            AccountType     => 'DoNotSendEmail',
207            AccountID       => undef,
208            StartTime       => '2017-07-20 08:58:43',
209            EndTime         => '2017-07-20 08:58:49'
210            Duration        => 6,
211        },
212    ];
213
214=cut
215
216sub CommunicationList {
217    my ( $Self, %Param ) = @_;
218
219    $Param{Result}  ||= 'ARRAY';
220    $Param{OrderBy} ||= 'Down';
221    $Param{SortBy}  ||= 'StartTime';
222
223    my $DurationSQL = $Self->_DurationSQL(
224        Start => 'c.start_time',
225        End   => 'c.end_time'
226    );
227    my $SQL = "
228        SELECT c.id, c.transport, c.direction, c.status, c.account_type, c.account_id,
229               c.start_time, c.end_time, (${ DurationSQL })
230        FROM communication_log c
231    ";
232
233    # prepare possible where clause
234    my @FilterFields;
235    my @Bind;
236
237    if ( $Param{CommunicationID} ) {
238        push @FilterFields, ' (c.id = ?) ';
239        push @Bind,         \$Param{CommunicationID};
240    }
241
242    if ( $Param{Transport} ) {
243        push @FilterFields, ' (c.transport = ?) ';
244        push @Bind,         \$Param{Transport};
245    }
246
247    if ( $Param{Direction} ) {
248        push @FilterFields, ' (c.direction = ?) ';
249        push @Bind,         \$Param{Direction};
250    }
251
252    if ( $Param{Status} ) {
253        push @FilterFields, ' (c.status = ?) ';
254        push @Bind,         \$Param{Status};
255    }
256
257    if ( $Param{AccountType} ) {
258        push @FilterFields, ' (c.account_type = ?) ';
259        push @Bind,         \$Param{AccountType};
260    }
261
262    if ( $Param{AccountID} ) {
263        push @FilterFields, ' (c.account_id = ?) ';
264        push @Bind,         \$Param{AccountID};
265    }
266
267    if ( $Param{Date} ) {
268
269        my $DateTimeObject = $Kernel::OM->Create(
270            'Kernel::System::DateTime',
271            ObjectParams => {
272                String => $Param{Date}
273            },
274        );
275
276        if ($DateTimeObject) {
277            my $NextDayObj = $DateTimeObject->Clone();
278            $NextDayObj->Add( Days => 1 );
279
280            push @FilterFields, ' (c.start_time BETWEEN ? and ?) ';
281            push @Bind, \$DateTimeObject->Format( Format => '%Y-%m-%d' ), \$NextDayObj->Format( Format => '%Y-%m-%d' );
282        }
283    }
284
285    if ( $Param{StartDate} ) {
286
287        my $DateTimeObject = $Kernel::OM->Create(
288            'Kernel::System::DateTime',
289            ObjectParams => {
290                String => $Param{StartDate}
291            },
292        );
293
294        if ($DateTimeObject) {
295            push @FilterFields, ' (c.start_time >= ?) ';
296            push @Bind,         \$Param{StartDate};
297        }
298    }
299
300    if ( $Param{OlderThan} ) {
301
302        my $DateTimeObject = $Kernel::OM->Create(
303            'Kernel::System::DateTime',
304            ObjectParams => {
305                String => $Param{OlderThan}
306            }
307        );
308
309        if ($DateTimeObject) {
310            push @FilterFields, ' (c.start_time < ?) ';
311            push @Bind,         \$Param{OlderThan};
312        }
313    }
314
315    if (@FilterFields) {
316        $SQL .= ' WHERE ' . join ' AND ', @FilterFields;
317    }
318
319    my %OrderByMap = (
320        CommunicationID => 'c.id',
321        Transport       => 'c.transport',
322        Direction       => 'c.direction',
323        Status          => 'c.status',
324        AccountType     => 'c.account_type',
325        AccountID       => 'c.account_id',
326        StartTime       => 'c.start_time',
327        EndTime         => 'c.end_time',
328        Duration        => $DurationSQL,
329    );
330
331    my $SortBy  = $OrderByMap{ $Param{SortBy} } || 'c.status';
332    my $OrderBy = lc $Param{OrderBy} eq 'up' ? 'ASC' : 'DESC';
333
334    $SQL .= "ORDER BY $SortBy $OrderBy";
335
336    my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
337
338    return if !$DBObject->Prepare(
339        SQL  => $SQL,
340        Bind => \@Bind,
341    );
342
343    my @CommunicationList;
344    my $ProcessingTime = 0;
345
346    while ( my @Row = $DBObject->FetchrowArray() ) {
347
348        my %Communication = (
349            CommunicationID => $Row[0],
350            Transport       => $Row[1],
351            Direction       => $Row[2],
352            Status          => $Row[3],
353            AccountType     => $Row[4],
354            AccountID       => $Row[5],
355            StartTime       => $Row[6],
356            EndTime         => $Row[7],
357            Duration        => $Row[7] ? $Row[8] : undef,
358        );
359
360        push @CommunicationList, \%Communication;
361
362        if ( $Param{Result} eq 'AVERAGE' && $Row[8] ) {
363            $ProcessingTime += $Row[8];
364        }
365    }
366
367    # Compute average processing time by dividing sum of durations with number of communications.
368    if ( $Param{Result} eq 'AVERAGE' ) {
369        return 0 if !@CommunicationList;
370        return sprintf( "%.1f", ( $ProcessingTime / scalar @CommunicationList ) );
371    }
372
373    return \@CommunicationList;
374}
375
376=head2 CommunicationDelete()
377
378Deletes a Communication entry if specified. Otherwise deletes all communications.
379
380    my $Result = $CommunicationDBObject->CommunicationDelete(
381        CommunicationID => 1,            # (optional) Communication ID
382        Status          => 'Processing', # (optional) 'Successful', 'Processing' or 'Failed'
383                                         # for example, using '!Processing', means different from
384        Date            => '2017-07-03', # (optional) Delete communications just from the given date.
385        OlderThan       => '2017-07-03', # (optional) Delete communications older than the given date.
386    );
387
388Returns:
389
390    C<undef> - in case of error
391    1        - in case of success
392
393=cut
394
395sub CommunicationDelete {
396    my ( $Self, %Param ) = @_;
397
398    my $SQL = 'DELETE FROM communication_log';
399
400    # Prepare possible where clause.
401    my @FilterFields;
402    my @Bind;
403
404    if ( $Param{CommunicationID} ) {
405        push @FilterFields, ' (id = ?) ';
406        push @Bind,         \$Param{CommunicationID};
407    }
408
409    if ( $Param{Status} ) {
410        my $Operator = '=';
411        my $Status   = $Param{Status};
412        if ( substr( $Status, 0, 1 ) eq '!' ) {
413            $Operator = '!=';
414            $Status   = substr( $Status, 1, );
415        }
416
417        push @FilterFields, " (status ${ Operator } ?) ";
418        push @Bind,         \$Status;
419    }
420
421    if ( $Param{Date} ) {
422
423        my $DateTimeObject = $Kernel::OM->Create(
424            'Kernel::System::DateTime',
425            ObjectParams => {
426                String => $Param{Date}
427            },
428        );
429
430        if ($DateTimeObject) {
431            my $NextDayObj = $DateTimeObject->Clone();
432            $NextDayObj->Add( Days => 1 );
433
434            push @FilterFields, ' (start_time BETWEEN ? and ?) ';
435            push @Bind, \$DateTimeObject->Format( Format => '%Y-%m-%d' ), \$NextDayObj->Format( Format => '%Y-%m-%d' );
436        }
437    }
438
439    if ( $Param{OlderThan} ) {
440
441        my $DateTimeObject = $Kernel::OM->Create(
442            'Kernel::System::DateTime',
443            ObjectParams => {
444                String => $Param{OlderThan}
445            },
446        );
447
448        if ($DateTimeObject) {
449            push @FilterFields, ' (start_time < ?) ';
450            push @Bind,         \$Param{OlderThan};
451        }
452    }
453
454    # Delete communication dependencies.
455    my $DependenciesDeleteResult = 1;
456    if (@FilterFields) {
457        my $Where = 'WHERE ' . join ' AND ', @FilterFields;
458        $SQL .= " ${ Where }";
459
460        $DependenciesDeleteResult = $Self->ObjectLogDelete(
461            CommunicationFilters => {
462                Where => $Where,
463                Binds => \@Bind,
464            },
465        );
466    }
467    else {
468        $DependenciesDeleteResult = $Self->ObjectLogDelete();
469    }
470
471    if ( !$DependenciesDeleteResult ) {
472        $Self->_LogError( "Error deleting communication(s) dependencies!", );
473        return;
474    }
475
476    # Delete the communication records.
477    my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
478    my $Result   = $DBObject->Do(
479        SQL  => $SQL,
480        Bind => \@Bind,
481    );
482
483    if ( !$Result ) {
484        $Self->_LogError( "Error deleting the communications: ${ SQL }", );
485    }
486
487    return 1;
488}
489
490=head2 CommunicationGet()
491
492Get a communication entry data.
493
494    my $CommunicationData = $CommunicationDBObject->CommunicationGet(
495        CommunicationID => 123, # Required
496    );
497
498Returns:
499
500    $CommunicationData = {
501        CommunicationID => 123,
502        Transport       => 'Email',
503        Direction       => 'Incoming',
504        Status          => 'Processing',
505        StartTime       => '2017-05-31 09:26:20',
506        EndTime         => '2017-05-31 09:30:15',
507        Duration        => 235,
508    };
509
510=cut
511
512sub CommunicationGet {
513    my ( $Self, %Param, ) = @_;
514
515    # Check for required data.
516    if ( !$Param{CommunicationID} ) {
517        return $Self->_LogError("Need CommunicationID!");
518    }
519
520    my $List = $Self->CommunicationList( CommunicationID => $Param{CommunicationID} );
521
522    return $List->[0] if IsArrayRefWithData($List);
523
524    return {};
525}
526
527=head2 CommunicationAccountLinkGet()
528
529Get relative link information if AccountType and AccountID are present.
530
531    my $ParamString = $CommunicationDBObject->CommunicationAccountLinkGet();
532
533Returns something like this:
534
535    $ParamString = "Action=AdminMailAccount;Subaction=Update;ID=2";
536
537=cut
538
539sub CommunicationAccountLinkGet {
540    my ( $Self, %Param ) = @_;
541
542    my $TransportModule = $Self->_GetTransportModule(%Param);
543    return if !$TransportModule;
544
545    return $TransportModule->CommunicationAccountLinkGet(%Param);
546}
547
548=head2 CommunicationAccountLabelGet()
549
550Get related account label if AccountType and AccountID are present.
551
552    my $AccountLabel = $CommunicationDBObject->CommunicationAccountLabelGet();
553
554Returns something like this:
555
556    $AccountLabel = "Example.com / Alice (IMAPS)";
557
558=cut
559
560sub CommunicationAccountLabelGet {
561    my ( $Self, %Param ) = @_;
562
563    my $TransportModule = $Self->_GetTransportModule(%Param);
564    return if !$TransportModule;
565
566    return $TransportModule->CommunicationAccountLabelGet(%Param);
567}
568
569=head2 ObjectLogCreate()
570
571Creates the logging for a specific communication object.
572
573    my $ObjectLogID = $CommunicationDBObject->ObjectLogCreate(
574        CommunicationID => 123,          # (required) The CommunicationID of the related ongoing communication.
575        ObjectLogType   => 'Connection', # (required) Must be 'Connection' or 'Message'.
576        Status          => 'Processing', # (optional) Needs to be either 'Successful', 'Processing' or 'Failed'
577    );
578
579=cut
580
581sub ObjectLogCreate {
582    my ( $Self, %Param ) = @_;
583
584    for my $Argument (qw(CommunicationID ObjectLogType)) {
585        if ( !$Param{$Argument} ) {
586            return $Self->_LogError("Need $Argument!");
587        }
588    }
589
590    $Param{Status} //= 'Processing';
591
592    # Check if is a valid status.
593    return if !$Self->_IsValidStatus( Status => $Param{Status} );
594
595    # Check if is a valid ObjectLogType.
596    return if !$Self->_IsValidObjectLogType( ObjectLogType => $Param{ObjectLogType} );
597
598    my $RandomString = $Kernel::OM->Get('Kernel::System::Main')->GenerateRandomString(
599        Length => 32,
600    );
601    my $InsertFingerprint = $$ . '-' . $RandomString;
602
603    my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
604
605    my $Result = $DBObject->Do(
606        SQL => '
607            INSERT INTO communication_log_object (
608                insert_fingerprint, communication_id, object_type, status, start_time
609            )
610            VALUES ( ?, ?, ?, ?, current_timestamp )
611        ',
612        Bind => [
613            \$InsertFingerprint,
614            \$Param{CommunicationID},
615            \$Param{ObjectLogType},
616            \$Param{Status},
617        ],
618    );
619
620    if ( !$Result ) {
621        return $Self->_LogError(
622            sprintf(
623                q{Error while starting object log type '%s' for CommunicationID '%s'},
624                $Param{ObjectLogType},
625                $Param{CommunicationID},
626            )
627        );
628    }
629
630    # get communication id
631    return if !$DBObject->Prepare(
632        SQL => 'SELECT id FROM communication_log_object
633            WHERE insert_fingerprint = ?
634            ORDER BY id DESC',
635        Bind  => [ \$InsertFingerprint ],
636        Limit => 1,
637    );
638
639    my $ObjectLogID;
640    while ( my @Row = $DBObject->FetchrowArray() ) {
641        $ObjectLogID = $Row[0];
642    }
643
644    # return if there is not article created
645    if ( !$ObjectLogID ) {
646        return $Self->_LogError(
647            "Can't get ObjectLogID from object log start with CommunicationID '$Param{CommunicationID}'!"
648        );
649    }
650
651    return $ObjectLogID;
652}
653
654=head2 ObjectLogUpdate()
655
656Stop the logging for a specific communication object.
657
658    my $Result = $CommunicationDBObject->ObjectLogUpdate(
659        CommunicationID => 123,             # (required) The CommunicationID of the related ongoing communication.
660        ObjectLogID     => 234,             # (required) The ObjectLogID to be used
661        ObjectLogType   => 'Connection',    # (required) Must be 'Connection' or 'Message'.
662        Status          => 'Processing',    # (optional) Needs to be either 'Successful', 'Processing' or 'Failed'
663    );
664
665=cut
666
667sub ObjectLogUpdate {
668    my ( $Self, %Param ) = @_;
669
670    for my $Argument (qw(ObjectLogID CommunicationID ObjectLogType Status)) {
671        if ( !$Param{$Argument} ) {
672            return $Self->_LogError("Need $Argument!");
673        }
674    }
675
676    # Check if is a valid status.
677    return if !$Self->_IsValidStatus( Status => $Param{Status} );
678
679    # Check if is a valid ObjectLogType.
680    return if !$Self->_IsValidObjectLogType( ObjectLogType => $Param{ObjectLogType} );
681
682    my $Result = $Kernel::OM->Get('Kernel::System::DB')->Do(
683        SQL => '
684            UPDATE communication_log_object
685            SET status = ?, end_time = current_timestamp
686            WHERE id = ? and communication_id = ? and object_type = ?
687        ',
688        Bind => [
689            \$Param{Status},
690            \$Param{ObjectLogID},
691            \$Param{CommunicationID},
692            \$Param{ObjectLogType},
693        ],
694    );
695
696    if ( !$Result ) {
697        return $Self->_LogError(
698            sprintf(
699                q{Error while stopping object '%s' (%s) for Communication '%s'},
700                $Param{ObjectLogID},
701                $Param{ObjectLogType},
702                $Param{CommunicationID},
703            )
704        );
705    }
706
707    return 1;
708}
709
710=head2 ObjectLogList()
711
712Get the object list for a specific communication.
713
714    my $Result = $CommunicationDBObject->ObjectLogList(
715        CommunicationID    => '123',         # (optional)
716        ObjectLogID        => '123',         # (optional)
717        ObjectLogType      => 'Connection',  # (optional)
718        StartDate          => '2017-07-03',  # (optional) List communications starting from the given date.
719        ObjectLogStartTime => '2017-07-03',  # (optional)
720        ObjectLogEndTime   => '2017-07-03',  # (optional)
721        ObjectLogStatus    => 'Successful',  # (optional)
722        OrderBy            => 'Down',        # (optional) Down|Up; Default: Down
723        SortBy             => 'ID',          # (optional) ID|CommunicationID|ObjectLogType|StartTime|EndTime|Status|Duration, default: ID
724    );
725
726Returns:
727
728    $Result = [
729        {
730            ObjectLogID        => '19',
731            CommunicationID    => '11',
732            ObjectLogStatus    => 'Successful',
733            ObjectLogType      => 'Connection',
734            ObjectLogStartTime => '2017-07-20 10:50:22',
735            ObjectLogEndTime   => '2017-07-20 10:50:22',
736            ObjectLogDuration  => '0',
737        },
738        {
739            ObjectLogID        => '18',
740            CommunicationID    => '11',
741            ObjectLogType      => 'Message',
742            ObjectLogStatus    => 'Successful',
743            ObjectLogStartTime => '2017-07-20 10:50:21',
744            ObjectLogEndTime   => '2017-07-20 10:50:22',
745            ObjectLogDuration  => '1',
746        },
747    ];
748
749=cut
750
751sub ObjectLogList {
752    my ( $Self, %Param ) = @_;
753
754    my $DurationSQL = $Self->_DurationSQL(
755        Start => 'clo.start_time',
756        End   => 'clo.end_time'
757    );
758    my @SQL = (
759        "SELECT clo.id, clo.communication_id, clo.object_type, clo.status,
760            clo.start_time, clo.end_time, (${ DurationSQL })",
761        'FROM communication_log_object clo',
762    );
763
764    # prepare possible where clause
765    my @FilterFields;
766    my @Bind;
767
768    my @PossibleFilters = (
769        {
770            Param  => 'ObjectLogID',
771            DBName => 'clo.id'
772        },
773        {
774            Param  => 'CommunicationID',
775            DBName => 'clo.communication_id'
776        },
777        {
778            Param  => 'ObjectLogType',
779            DBName => 'clo.object_type'
780        },
781        {
782            Param  => 'ObjectLogStartTime',
783            DBName => 'clo.start_time'
784        },
785        {
786            Param  => 'ObjectLogEndTime',
787            DBName => 'clo.end_time'
788        },
789        {
790            Param  => 'ObjectLogDuration',
791            DBName => $DurationSQL,
792        },
793        {
794            Param  => 'ObjectLogStatus',
795            DBName => 'clo.status'
796        },
797    );
798
799    my %OrderByMap = map { $_->{Param} => $_->{DBName} } @PossibleFilters;
800
801    if ( $Param{StartDate} ) {
802
803        my $DateTimeObject = $Kernel::OM->Create(
804            'Kernel::System::DateTime',
805            ObjectParams => {
806                String => $Param{StartDate},
807            },
808        );
809
810        if ($DateTimeObject) {
811            push @FilterFields, ' (clo.start_time >= ?) ';
812            push @Bind,         \$Param{StartDate};
813        }
814    }
815
816    for my $PossibleFilter (@PossibleFilters) {
817        my $Value = $Param{ $PossibleFilter->{Param} };
818        if ($Value) {
819            push @FilterFields, sprintf( ' (%s = ?) ', $PossibleFilter->{DBName} );
820            push @Bind,         \$Value;
821        }
822    }
823
824    if (@FilterFields) {
825        push @SQL, 'WHERE';
826        push @SQL, join( ' AND ', @FilterFields );
827    }
828
829    my $SortBy  = $OrderByMap{ $Param{SortBy} || 'ObjectLogID' };
830    my $OrderBy = $Param{OrderBy} && lc $Param{OrderBy} eq 'up' ? 'ASC' : 'DESC';
831
832    push @SQL, "ORDER BY $SortBy $OrderBy";
833
834    my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
835    return if !$DBObject->Prepare(
836        SQL   => join( q{ }, @SQL ),
837        Bind  => \@Bind,
838        Limit => $Param{Limit},
839    );
840
841    my @List = ();
842    while ( my @Row = $DBObject->FetchrowArray() ) {
843
844        push @List, {
845            ObjectLogID        => $Row[0],
846            CommunicationID    => $Row[1],
847            ObjectLogType      => $Row[2],
848            ObjectLogStatus    => $Row[3],
849            ObjectLogStartTime => $Row[4],
850            ObjectLogEndTime   => $Row[5],
851            ObjectLogDuration  => $Row[5] ? $Row[6] : undef,
852        };
853    }
854
855    return \@List;
856}
857
858=head2 ObjectLogDelete()
859
860Delete the logging.
861
862    my $Result = $CommunicationDBObject->ObjectLogDelete(
863        CommunicationID => '...',    # optional
864        ObjectLogID     => '...',    # optional
865        ObjectLogStatus => '...',    # optional
866    );
867
868=cut
869
870sub ObjectLogDelete {
871    my ( $Self, %Param ) = @_;
872
873    my $ExecDeleteStmt = sub {
874        my %LocalParam = @_;
875
876        my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
877        my $Result   = $DBObject->Do(
878            SQL  => $LocalParam{SQL},
879            Bind => $LocalParam{Bind},
880        );
881
882        if ( !$Result ) {
883            $Self->_LogError( $LocalParam{ErrorMessage} );
884            return;
885        }
886
887        return 1;
888    };
889
890    my %SQL = (
891        Objects => {
892            Stmt  => 'DELETE FROM communication_log_object',
893            Binds => [],
894        },
895        Entries => {
896            Stmt  => 'DELETE FROM communication_log_object_entry',
897            Binds => [],
898        },
899        Lookup => {
900            Stmt  => 'DELETE FROM communication_log_obj_lookup',
901            Binds => [],
902        },
903    );
904    my @DeleteOrder = qw( Lookup Entries Objects );
905    my %DBNames     = (
906        CommunicationID => 'communication_id',
907        ObjectLogStatus => 'status',
908        ObjectLogID     => {
909            Objects => 'id',
910            Default => 'communication_log_object_id',
911        },
912    );
913
914    my @PossibleFilters = qw( CommunicationFilters CommunicationID ObjectLogStatus ObjectLogID );
915    POSSIBLE_FILTER:
916    for my $PossibleFilter (@PossibleFilters) {
917        my $Value = $Param{$PossibleFilter};
918        next POSSIBLE_FILTER if !( defined $Value ) || !( length $Value );
919
920        ITEM:
921        for my $Item (@DeleteOrder) {
922            my $ItemSQL    = $SQL{$Item}->{Stmt};
923            my $WhereORAnd = ( $ItemSQL =~ m/\s+where\s+/i ) ? 'AND' : 'WHERE';
924
925            if ( $PossibleFilter eq 'CommunicationFilters' ) {
926                my $CommunicationSELECT = 'SELECT id FROM communication_log ' . $Value->{Where};
927
928                if ( $Item eq 'Objects' ) {
929                    $ItemSQL .= " ${ WhereORAnd } communication_id IN (${ CommunicationSELECT })";
930                }
931                else {
932                    $ItemSQL
933                        .= " ${ WhereORAnd } communication_log_object_id IN (SELECT id FROM communication_log_object WHERE communication_id IN (${ CommunicationSELECT }))";
934                }
935
936                $SQL{$Item}->{Stmt} = $ItemSQL;
937                push @{ $SQL{$Item}->{Binds} }, @{ $Value->{Binds} };
938
939                next ITEM;
940            }
941
942            my $ColumnDBName = $DBNames{$PossibleFilter};
943            if ( ref $ColumnDBName ) {
944                $ColumnDBName = $ColumnDBName->{$Item} || $ColumnDBName->{Default};
945            }
946
947            if ( $Item eq 'Objects' ) {
948                $ItemSQL .= " ${ WhereORAnd } ${ ColumnDBName } = ?";
949            }
950            else {
951                $ItemSQL
952                    .= " ${ WhereORAnd } communication_log_object_id IN (SELECT id FROM communication_log_object WHERE ${ ColumnDBName } = ?)";
953            }
954
955            $SQL{$Item}->{Stmt} = $ItemSQL;
956            push @{ $SQL{$Item}->{Binds} }, \$Value;
957        }
958    }
959
960    for my $Item ( sort keys %SQL ) {
961        return if !$ExecDeleteStmt->(
962            SQL  => $SQL{$Item}->{Stmt},
963            Bind => $SQL{$Item}->{Binds},
964            ErrorMessage =>
965                sprintf(
966                q{Error deleting communication log %s: %s},
967                lc($Item),
968                $SQL{$Item}->{Stmt},
969                ),
970        );
971    }
972
973    return 1;
974}
975
976=head2 ObjectLogGet()
977
978Returns the Communication Log Object by ID
979
980    my $Result = $CommunicationDBObject->ObjectLogGet(
981        CommunicationID  => '...',
982        ObjectLogID      => '...',
983    );
984
985    Returns something like:
986
987    $Result = {
988        ObjectLogID        => '18',
989        CommunicationID    => '11',
990        ObjectLogType      => 'Message',
991        ObjectLogStatus    => 'Successful',
992        ObjectLogStartTime => '2017-07-20 10:50:21',
993        ObjectLogEndTime   => '2017-07-20 10:50:22',
994        ObjectLogDuration  => '1',
995    };
996
997=cut
998
999sub ObjectLogGet {
1000
1001    my ( $Self, %Param ) = @_;
1002
1003    for my $Argument (qw(ObjectLogID)) {
1004        if ( !$Param{$Argument} ) {
1005            return $Self->_LogError("Need $Argument!");
1006        }
1007    }
1008
1009    my $Result = $Self->ObjectLogList(
1010        ObjectLogID => $Param{ObjectLogID},
1011    );
1012
1013    return              if !$Result;
1014    return $Result->[0] if IsArrayRefWithData($Result);
1015    return {};
1016}
1017
1018=head2 ObjectLogEntryCreate()
1019
1020Create a log entry for the specific communication object.
1021
1022    my $Result = $CommunicationDBObject->ObjectLogEntryCreate(
1023        ObjectLogID => '...', # required
1024        Key         => '...', # required
1025        Value       => '...', # required
1026        Priority    => '...', # required
1027    );
1028
1029    Returns 1 on success.
1030
1031=cut
1032
1033sub ObjectLogEntryCreate {
1034    my ( $Self, %Param ) = @_;
1035
1036    for my $Argument (qw(ObjectLogID Key Value Priority)) {
1037        if ( !$Param{$Argument} ) {
1038            return $Self->_LogError("Need $Argument!");
1039        }
1040    }
1041
1042    # Check if priority is valid.
1043    my %ValidPriorities = (
1044        Error  => 1,
1045        Warn   => 1,
1046        Notice => 1,
1047        Info   => 1,
1048        Debug  => 1,
1049        Trace  => 1,
1050    );
1051
1052    if ( !$ValidPriorities{ $Param{Priority} } ) {
1053        return $Self->_LogError( sprintf( q{Invalid Priority '%s'!}, $Param{Priority} ) );
1054    }
1055
1056    # Insert log in database.
1057    my $Result = $Kernel::OM->Get('Kernel::System::DB')->Do(
1058        SQL => '
1059            INSERT INTO communication_log_object_entry (
1060                communication_log_object_id, log_key, log_value, priority, create_time )
1061            VALUES ( ?, ?, ?, ?, current_timestamp )
1062        ',
1063        Bind => [
1064            \$Param{ObjectLogID}, \$Param{Key}, \$Param{Value}, \$Param{Priority},
1065        ],
1066    );
1067
1068    if ( !$Result ) {
1069        return $Self->_LogError(
1070            sprintf(
1071                q{Error while creating log for Communication object '%s': Key: %s, Value: %s, Priority: %s},
1072                $Param{ObjectLogID},
1073                $Param{Key},
1074                $Param{Value},
1075                $Param{Priority},
1076            )
1077        );
1078    }
1079
1080    return 1;
1081}
1082
1083=head2 ObjectLogEntryList()
1084
1085Get the logging list for a specific communication.
1086
1087    my $Result = $CommunicationDBObject->ObjectLogEntryList(
1088        CommunicationID     => '...',
1089        ObjectLogID         => '...',   # optional
1090        ObjectLogType       => '...',   # optional
1091        ObjectLogStartTime  => '...',   # optional
1092        ObjectLogEndTime    => '...',   # optional
1093        ObjectLogStatus     => '...',   # optional
1094        LogID               => '...',   # optional
1095        LogKey              => '...',   # optional
1096        LogValue            => '...',   # optional
1097        LogPriority         => '...',   # optional
1098        LogCreateTime       => '...',   # optional
1099        OrderBy             => 'Down',  # (optional) Down|Up; Default: Down
1100        SortBy              => 'LogID',    # (optional) ObjectLogID|ObjectLogType|ObjectStartTime|ObjectEndTime|ObjectStatus|LogID|LogKey|LogPriority|LogCreateTime; Default: LogID
1101    );
1102
1103=cut
1104
1105sub ObjectLogEntryList {
1106    my ( $Self, %Param ) = @_;
1107
1108    my @SQL = (
1109        'SELECT clo.id, clo.communication_id, clo.object_type, clo.status, clo.start_time, clo.end_time',
1110        ',cloe.id, cloe.log_key, cloe.log_value, cloe.priority, cloe.create_time',
1111        'FROM communication_log_object clo JOIN communication_log_object_entry cloe',
1112        'ON cloe.communication_log_object_id = clo.id',
1113    );
1114
1115    # prepare possible where clause
1116    my @FilterFields = ();
1117    my @Bind         = ();
1118
1119    my @PossibleFilters = (
1120        {
1121            Param  => 'CommunicationID',
1122            DBName => 'clo.communication_id'
1123        },
1124        {
1125            Param  => 'ObjectLogID',
1126            DBName => 'clo.id'
1127        },
1128        {
1129            Param  => 'ObjectLogType',
1130            DBName => 'clo.object_type'
1131        },
1132        {
1133            Param  => 'ObjectLogStartTime',
1134            DBName => 'clo.start_time'
1135        },
1136        {
1137            Param  => 'ObjectLogEndTime',
1138            DBName => 'clo.end_time'
1139        },
1140        {
1141            Param  => 'ObjectLogStatus',
1142            DBName => 'clo.status'
1143        },
1144        {
1145            Param  => 'LogID',
1146            DBName => 'cloe.id'
1147        },
1148        {
1149            Param  => 'LogKey',
1150            DBName => 'cloe.log_key'
1151        },
1152        {
1153            Param  => 'LogValue',
1154            DBName => 'cloe.log_value'
1155        },
1156        {
1157            Param  => 'LogPriority',
1158            DBName => 'cloe.priority'
1159        },
1160        {
1161            Param  => 'LogCreateTime',
1162            DBName => 'cloe.create_time'
1163        },
1164    );
1165
1166    my %OrderByMap = map { $_->{Param} => $_->{DBName} } @PossibleFilters;
1167    delete $OrderByMap{LogValue};
1168
1169    for my $PossibleFilter (@PossibleFilters) {
1170        my $Value = $Param{ $PossibleFilter->{Param} };
1171        if ($Value) {
1172            push @FilterFields, sprintf( ' (%s = ?) ', $PossibleFilter->{DBName} );
1173            push @Bind,         \$Value;
1174        }
1175    }
1176
1177    if (@FilterFields) {
1178        push @SQL, 'WHERE';
1179        push @SQL, join( ' AND ', @FilterFields );
1180    }
1181
1182    my $SortBy  = $OrderByMap{ $Param{SortBy} || 'LogID' };
1183    my $OrderBy = $Param{OrderBy} && lc $Param{OrderBy} eq 'up' ? 'ASC' : 'DESC';
1184
1185    push @SQL, "ORDER BY $SortBy $OrderBy";
1186
1187    my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
1188
1189    return if !$DBObject->Prepare(
1190        SQL   => join( q{ }, @SQL ),
1191        Bind  => \@Bind,
1192        Limit => $Param{Limit},
1193    );
1194
1195    my @List = ();
1196    while ( my @Row = $DBObject->FetchrowArray() ) {
1197        push @List, {
1198            ObjectLogID        => $Row[0],
1199            CommunicationID    => $Row[1],
1200            ObjectLogType      => $Row[2],
1201            ObjectLogStatus    => $Row[3],
1202            ObjectLogStartTime => $Row[4],
1203            ObjectLogEndTime   => $Row[5],
1204            LogID              => $Row[6],
1205            LogKey             => $Row[7],
1206            LogValue           => $Row[8],
1207            LogPriority        => $Row[9],
1208            LogCreateTime      => $Row[10],
1209        };
1210    }
1211
1212    return \@List;
1213}
1214
1215=head2 GetConnectionsObjectsAndCommunications()
1216
1217Method specifically created for optimization purposes for the Support Data Collector.
1218Joins the Communication Log Object and Communications.
1219
1220    my $Result = $CommunicationDBObject->GetConnectionsObjectsAndCommunications(
1221        ObjectLogStartDate => '...',    # Required
1222        Status             => '...',    # Optional
1223    );
1224
1225Returns Arrayref of Hashes.
1226
1227    $Result = [
1228        {
1229            CommunicationID => '...',
1230            ObjectLogStatus => '...',
1231            AccountType     => '...',
1232            AccountID       => '...',
1233        },
1234        {...},
1235    ];
1236
1237=cut
1238
1239sub GetConnectionsObjectsAndCommunications {
1240    my ( $Self, %Param ) = @_;
1241
1242    if ( !defined $Param{ObjectLogStartDate} ) {
1243        return $Self->_LogError("Need ObjectLogStartDate!");
1244    }
1245
1246    my $SQL = "SELECT c.id, clo.status, c.account_type, c.account_id, c.transport
1247    FROM communication_log_object clo
1248    JOIN communication_log c on c.id = clo.communication_id
1249    WHERE clo.object_type = 'Connection'";
1250
1251    my @Binds;
1252    if ( $Param{ObjectLogStartDate} ) {
1253        my $DateTimeObject = $Kernel::OM->Create(
1254            'Kernel::System::DateTime',
1255            ObjectParams => {
1256                String => $Param{ObjectLogStartDate},
1257            },
1258        );
1259
1260        return if !$DateTimeObject;
1261
1262        $SQL .= "AND clo.start_time >= ?";
1263        push @Binds, \$Param{ObjectLogStartDate};
1264    }
1265
1266    # Add Status where clause if there is a given valid status.
1267    if ( $Param{Status} ) {
1268        return if !$Self->_IsValidStatus( Status => $Param{Status} );
1269        $SQL .= " AND clo.status = ?";
1270        push @Binds, \$Param{Status};
1271    }
1272
1273    my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
1274
1275    return if !$DBObject->Prepare(
1276        SQL   => $SQL,
1277        Bind  => \@Binds,
1278        Limit => $Param{Limit},
1279    );
1280
1281    my @List = ();
1282    while ( my @Row = $DBObject->FetchrowArray() ) {
1283        push @List, {
1284            CommunicationID => $Row[0],
1285            ObjectLogStatus => $Row[1],
1286            AccountType     => $Row[2] || 'Unknown',
1287            AccountID       => $Row[3],
1288            Transport       => $Row[4],
1289        };
1290    }
1291
1292    return \@List;
1293}
1294
1295=head2 ObjectLookupSet()
1296
1297Inserts or updates a lookup information.
1298
1299    my $Result = $CommunicationDBObject->ObjectLookupSet(
1300        ObjectLogID      => '123',     # (required)
1301        TargetObjectType => 'Article', # (required)
1302        TargetObjectID   => '123',     # (required)
1303    );
1304
1305Returns:
1306
1307    1 in case of success, <undef> in case of errors
1308
1309=cut
1310
1311sub ObjectLookupSet {
1312    my ( $Self, %Param ) = @_;
1313
1314    # Check for required arguments.
1315    for my $Argument (qw(ObjectLogID TargetObjectType TargetObjectID)) {
1316        if ( !$Param{$Argument} ) {
1317            return $Self->_LogError("Need $Argument!");
1318        }
1319    }
1320
1321    my $SQL = '
1322        INSERT INTO communication_log_obj_lookup (communication_log_object_id, object_type, object_id)
1323        VALUES (?, ?, ?)
1324    ';
1325
1326    my @Bind = (
1327        \$Param{ObjectLogID},
1328        \$Param{TargetObjectType},
1329        \$Param{TargetObjectID},
1330    );
1331
1332    my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
1333
1334    # Check if the record already exists.
1335    return if !$DBObject->Prepare(
1336        SQL => '
1337            SELECT id FROM communication_log_obj_lookup
1338            WHERE object_type = ? AND object_id = ?
1339        ',
1340        Bind  => [ \$Param{TargetObjectType}, \$Param{TargetObjectID}, ],
1341        Limit => 1,
1342    );
1343
1344    if ( my @Row = $DBObject->FetchrowArray() ) {
1345
1346        # Record already exists, lets just update it.
1347        $SQL = '
1348            UPDATE communication_log_obj_lookup
1349            SET communication_log_object_id = ?
1350            WHERE object_type = ? AND object_id = ?
1351        ';
1352    }
1353
1354    return if !$DBObject->Do(
1355        SQL  => $SQL,
1356        Bind => \@Bind,
1357    );
1358
1359    return 1;
1360}
1361
1362=head2 ObjectLookupSearch()
1363
1364Get a list of the objects lookup information.
1365
1366    my $List = $CommunicationDBObject->ObjectLookupSearch(
1367        ObjectLogID      => '123',     # (optional)
1368        ObjectLogType    => 'Message', # (optional)
1369        TargetObjectType => 'Article', # (optional)
1370        TargetObjectID   => '123',     # (optional)
1371        CommunicationID  => '123',     # (optional)
1372    );
1373
1374Returns:
1375
1376    <undef> - if any error occur
1377    An arrayref of object lookup - in case of success
1378    $List = [
1379        {
1380            ObjectLogID      => '...',
1381            TargetObjectType => '...',
1382            TargetObjectID   => '...',
1383        },
1384        ...
1385    ];
1386
1387=cut
1388
1389sub ObjectLookupSearch {
1390    my ( $Self, %Param ) = @_;
1391
1392    my @SQL = (
1393        'SELECT clo.communication_id, clol.communication_log_object_id, clol.object_type',
1394        ',clol.object_id',
1395        'FROM communication_log_obj_lookup clol',
1396        'JOIN communication_log_object clo ON clol.communication_log_object_id = clo.id'
1397    );
1398
1399    my %PossibleFilters = (
1400        ObjectLogID      => 'clol.communication_log_object_id',
1401        ObjectLogType    => 'clo.object_type',
1402        TargetObjectType => 'clol.object_type',
1403        TargetObjectID   => 'clol.object_id',
1404        CommunicationID  => 'clo.communication_id',
1405    );
1406
1407    my @FilterFields = ();
1408    my @Bind         = ();
1409
1410    POSSIBLE_FILTER:
1411    for my $PossibleFilter ( sort keys %PossibleFilters ) {
1412        my $Value = $Param{$PossibleFilter};
1413        if ( !defined $Value || !length $Value ) {
1414            next POSSIBLE_FILTER;
1415        }
1416
1417        my $FilterDBName = $PossibleFilters{$PossibleFilter};
1418
1419        push @FilterFields, sprintf( '(%s = ?)', $FilterDBName );
1420        push @Bind,         \$Value;
1421    }
1422
1423    if (@FilterFields) {
1424        my $FilterSQL = join ' AND ', @FilterFields;
1425        push @SQL, 'WHERE', $FilterSQL;
1426    }
1427
1428    my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
1429    return if !$DBObject->Prepare(
1430        SQL   => join( ' ', @SQL ),
1431        Bind  => \@Bind,
1432        Limit => $Param{Limit},
1433    );
1434
1435    my @List = ();
1436    while ( my @Row = $DBObject->FetchrowArray() ) {
1437        push @List, {
1438            CommunicationID  => $Row[0],
1439            ObjectLogID      => $Row[1],
1440            TargetObjectType => $Row[2],
1441            TargetObjectID   => $Row[3],
1442        };
1443    }
1444
1445    return \@List;
1446}
1447
1448=head2 ObjectLookupGet()
1449
1450Gets the object lookup information.
1451
1452    my $Result = $CommunicationDBObject->ObjectLookupGet(
1453        ObjectLogID      => '123',         # (optional)
1454        TargetObjectID   => '123',         # (optional)
1455        TargetObjectType => '123',         # (optional)
1456    );
1457
1458Returns:
1459
1460    $Result = {
1461        CommunicationID  => '...',
1462        ObjectLogID      => '...',
1463        TargetObjectType => '...',
1464        TargetObjectID   => '...',
1465    }
1466
1467    <undef> - if any error occur
1468    An hashref with object lookup information - in case info exists
1469    An empty hasref                           - in case info doesn't exists
1470
1471=cut
1472
1473sub ObjectLookupGet {
1474    my ( $Self, %Param ) = @_;
1475
1476    # Check for required arguments.
1477    my @RequiredCombinations = (
1478        [qw( ObjectLogID TargetObjectType )],
1479        [qw( TargetObjectID TargetObjectType )],
1480    );
1481
1482    my $MatchedCombination;
1483    REQUIRED_COMBINATION:
1484    for my $RequiredCombination (@RequiredCombinations) {
1485        $MatchedCombination = $RequiredCombination;
1486        for my $RequiredParam ( @{$RequiredCombination} ) {
1487            if ( !$Param{$RequiredParam} ) {
1488                $MatchedCombination = undef;
1489            }
1490        }
1491
1492        last REQUIRED_COMBINATION if $MatchedCombination;
1493    }
1494
1495    if ( !$MatchedCombination ) {
1496        my @ErrorMessage = (
1497            'Need',
1498            ( join ' OR ', map { join ' and ', @{$_} } @RequiredCombinations ),
1499            '!',
1500        );
1501
1502        return $Self->_LogError( join ' ', @ErrorMessage );
1503    }
1504
1505    my $List = $Self->ObjectLookupSearch(
1506        CommunicationID => $Param{CommunicationID},
1507        map { $_ => $Param{$_} } @{$MatchedCombination},
1508    );
1509
1510    if ($List) {
1511        return $List->[0] if IsArrayRefWithData($List);
1512        return {};    # Record not found.
1513    }
1514
1515    return;           # Some error occurred.
1516}
1517
1518=head2 CommunicationGetByObjectLogID()
1519
1520Get a communication entry data by a communication object id.
1521
1522    my %CommunicationData = $CommunicationDBObject->CommunicationGetByObjectLogID(
1523        ObjectLogID => 123,
1524    );
1525
1526Returns:
1527
1528    %CommunicationData = (
1529        CommunicationID => 123,
1530        Transport       => 'Email',
1531        Direction       => 'Incoming',
1532        Status          => 'Processing',
1533        AccountType     => '...',
1534        AccountID       => '...',
1535        StartTime       => '2017-05-31 09:26:20',
1536        EndTime         => '2017-05-31 09:30:15',
1537    );
1538
1539=cut
1540
1541sub CommunicationGetByObjectLogID {
1542    my ( $Self, %Param ) = @_;
1543
1544    if ( !$Param{ObjectLogID} ) {
1545        return $Self->_LogError("Need ObjectLogID!");
1546    }
1547
1548    my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
1549
1550    return if !$DBObject->Prepare(
1551        SQL => '
1552            SELECT c.id, c.transport, c.direction, c.status, c.account_type,
1553                   c.account_id, c.start_time, c.start_time, c.end_time
1554            FROM communication_log c
1555            JOIN communication_log_object clo ON clo.communication_id = c.id
1556            WHERE clo.id = ?
1557        ',
1558        Bind  => [ \$Param{ObjectLogID} ],
1559        Limit => 1,
1560    );
1561
1562    if ( my @Row = $DBObject->FetchrowArray() ) {
1563        return {
1564            CommunicationID => $Row[0],
1565            Transport       => $Row[1],
1566            Direction       => $Row[2],
1567            Status          => $Row[3],
1568            AccountType     => $Row[4],
1569            AccountID       => $Row[5],
1570            StartTime       => $Row[6],
1571            EndTime         => $Row[7],
1572        };
1573    }
1574
1575    return {};
1576
1577}
1578
1579=head2 _GetTransportModule()
1580
1581Lookup for the transport module.
1582
1583Returns:
1584
1585    undef  - case not found
1586    module - case found
1587
1588=cut
1589
1590sub _GetTransportModule {
1591    my ( $Self, %Param ) = @_;
1592
1593    my $Transport = $Param{Transport};
1594    return if !$Transport;
1595
1596    # Get the communication log transport configuration.
1597    my $ModuleConfigs = $Kernel::OM->Get('Kernel::Config')->Get('CommunicationLog::Transport');
1598    my $Module        = $ModuleConfigs->{$Transport};
1599    if ( !$Module || !$Module->{Module} ) {
1600        return $Self->_LogError("Couldn't create a backend object for transport '${ Transport }'!");
1601    }
1602
1603    return $Kernel::OM->Get( $Module->{Module} );
1604}
1605
1606=head2 _LogError()
1607
1608Helper Method for logging.
1609
1610=cut
1611
1612sub _LogError {
1613    my ( $Self, $Message ) = @_;
1614
1615    $Kernel::OM->Get('Kernel::System::Log')->Log(
1616        Priority => 'error',
1617        Message  => $Message,
1618    );
1619
1620    return;
1621}
1622
1623=head2 _IsValidDirection()
1624
1625Check if the given direction is valid.
1626
1627    my $Result = $LogModuleObject->_IsValidDirection(
1628        Direction => '...',
1629    );
1630
1631=cut
1632
1633sub _IsValidDirection {
1634    my ( $Self, %Param ) = @_;
1635
1636    my $Direction    = $Param{Direction};
1637    my %DirectionMap = (
1638        Incoming => 1,
1639        Outgoing => 1,
1640    );
1641
1642    if ( !$DirectionMap{$Direction} ) {
1643        return $Self->_LogError("Invalid Direction '${ Direction }'!");
1644    }
1645
1646    return 1;
1647}
1648
1649=head2 _IsValidObjectLogType()
1650
1651Check if the given Object Log Type is valid.
1652
1653    my $Result = $LogModuleObject->_IsValidObjectLogType(
1654        ObjectLogType => '...',
1655    );
1656
1657=cut
1658
1659sub _IsValidObjectLogType {
1660    my ( $Self, %Param ) = @_;
1661
1662    my $ObjectLogType = $Param{ObjectLogType};
1663    my %TypeMap       = (
1664        Connection => 1,
1665        Message    => 1,
1666    );
1667
1668    if ( !$TypeMap{$ObjectLogType} ) {
1669        return $Self->_LogError("Invalid ObjectLogType '${ ObjectLogType }'!");
1670    }
1671
1672    return 1;
1673}
1674
1675=head2 _IsValidStatus()
1676
1677Check if the given status is valid.
1678
1679    my $Result = $LogModuleObject->_IsValidStatus(
1680        Status => '...',
1681    );
1682
1683=cut
1684
1685sub _IsValidStatus {
1686    my ( $Self, %Param ) = @_;
1687
1688    my $Status    = $Param{Status};
1689    my %StatusMap = (
1690        Successful => 1,
1691        Processing => 1,
1692        Warning    => 1,
1693        Failed     => 1,
1694    );
1695
1696    if ( !$StatusMap{$Status} ) {
1697        return $Self->_LogError("Invalid Status '${ Status }'!");
1698    }
1699
1700    return 1;
1701}
1702
1703=head2 _DurationSQL()
1704
1705Return the SQL expression to get the difference between two dates in seconds.
1706
1707=cut
1708
1709sub _DurationSQL {
1710    my ( $Self, %Param ) = @_;
1711
1712    my $DBType = $Kernel::OM->Get('Kernel::System::DB')->{'DB::Type'};
1713    my $Start  = $Param{Start};
1714    my $End    = $Param{End};
1715
1716    my %Templates = (
1717        oracle     => q{EXTRACT(SECOND FROM(%s - %s) DAY TO SECOND)},
1718        mysql      => q{TIME_TO_SEC(TIMEDIFF(%s, %s))},
1719        postgresql => q{EXTRACT(EPOCH FROM(%s - %s))},
1720    );
1721
1722    return sprintf( $Templates{$DBType}, $End, $Start );
1723}
1724
17251;
1726
1727=head1 TERMS AND CONDITIONS
1728
1729This software is part of the OTRS project (L<https://otrs.org/>).
1730
1731This software comes with ABSOLUTELY NO WARRANTY. For details, see
1732the enclosed file COPYING for license information (GPL). If you
1733did not receive this file, see L<https://www.gnu.org/licenses/gpl-3.0.txt>.
1734
1735=cut
1736