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