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::Queue; 10 11use strict; 12use warnings; 13 14use parent qw(Kernel::System::EventHandler); 15 16our @ObjectDependencies = ( 17 'Kernel::Config', 18 'Kernel::System::Cache', 19 'Kernel::System::CustomerGroup', 20 'Kernel::System::DB', 21 'Kernel::System::Group', 22 'Kernel::System::Log', 23 'Kernel::System::Main', 24 'Kernel::System::StandardTemplate', 25 'Kernel::System::SysConfig', 26 'Kernel::System::Valid', 27); 28 29=head1 NAME 30 31Kernel::System::Queue - queue lib 32 33=head1 DESCRIPTION 34 35All queue functions. E. g. to add queue or other functions. 36 37=head1 PUBLIC INTERFACE 38 39=head2 new() 40 41Don't use the constructor directly, use the ObjectManager instead: 42 43 my $QueueObject = $Kernel::OM->Get('Kernel::System::Queue'); 44 45=cut 46 47sub new { 48 my ( $Type, %Param ) = @_; 49 50 # allocate new hash for object 51 my $Self = {}; 52 bless( $Self, $Type ); 53 54 $Self->{QueueID} = $Param{QueueID} || ''; 55 56 $Self->{CacheType} = 'Queue'; 57 $Self->{CacheTTL} = 60 * 60 * 24 * 20; 58 59 # load generator preferences module 60 my $GeneratorModule = $Kernel::OM->Get('Kernel::Config')->Get('Queue::PreferencesModule') 61 || 'Kernel::System::Queue::PreferencesDB'; 62 if ( $Kernel::OM->Get('Kernel::System::Main')->Require($GeneratorModule) ) { 63 $Self->{PreferencesObject} = $GeneratorModule->new(); 64 } 65 66 # --------------------------------------------------- # 67 # default queue settings # 68 # these settings are used by the CLI version # 69 # --------------------------------------------------- # 70 $Self->{QueueDefaults} = { 71 Calendar => '', 72 UnlockTimeout => 0, 73 FirstResponseTime => 0, 74 FirstResponseNotify => 0, 75 UpdateTime => 0, 76 UpdateNotify => 0, 77 SolutionTime => 0, 78 SolutionNotify => 0, 79 SystemAddressID => 1, 80 SalutationID => 1, 81 SignatureID => 1, 82 FollowUpID => 1, 83 FollowUpLock => 0, 84 }; 85 86 # init of event handler 87 $Self->EventHandlerInit( 88 Config => 'Queue::EventModulePost', 89 ); 90 91 return $Self; 92} 93 94=head2 GetSystemAddress() 95 96get a queue system email address as hash (Email, RealName) 97 98 my %Address = $QueueObject->GetSystemAddress( 99 QueueID => 123, 100 ); 101 102=cut 103 104sub GetSystemAddress { 105 my ( $Self, %Param ) = @_; 106 107 # get database object 108 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 109 110 my %Address; 111 my $QueueID = $Param{QueueID} || $Self->{QueueID}; 112 113 return if !$DBObject->Prepare( 114 SQL => 'SELECT sa.value0, sa.value1 FROM system_address sa, queue sq ' 115 . 'WHERE sq.id = ? AND sa.id = sq.system_address_id', 116 Bind => [ \$QueueID ], 117 Limit => 1, 118 ); 119 120 while ( my @Row = $DBObject->FetchrowArray() ) { 121 $Address{Email} = $Row[0]; 122 $Address{RealName} = $Row[1]; 123 } 124 125 # prepare realname quote 126 if ( $Address{RealName} =~ /(,|@|\(|\)|:)/ && $Address{RealName} !~ /^("|')/ ) { 127 $Address{RealName} =~ s/"/\"/g; 128 $Address{RealName} = '"' . $Address{RealName} . '"'; 129 } 130 131 return %Address; 132} 133 134=head2 GetSignature() 135 136get a queue signature 137 138 my $Signature = $QueueObject->GetSignature(QueueID => 123); 139 140=cut 141 142sub GetSignature { 143 my ( $Self, %Param ) = @_; 144 145 # check needed stuff 146 if ( !$Param{QueueID} ) { 147 $Kernel::OM->Get('Kernel::System::Log')->Log( 148 Priority => 'error', 149 Message => 'Need QueueID!', 150 ); 151 return; 152 } 153 154 # get database object 155 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 156 157 return if !$DBObject->Prepare( 158 SQL => 'SELECT text FROM signature si, queue sq ' 159 . ' WHERE sq.id = ? AND sq.signature_id = si.id', 160 Bind => [ \$Param{QueueID} ], 161 Limit => 1, 162 ); 163 164 # fetch the result 165 my $String = ''; 166 while ( my @Row = $DBObject->FetchrowArray() ) { 167 $String = $Row[0]; 168 } 169 170 return $String; 171} 172 173=head2 QueueStandardTemplateMemberAdd() 174 175to add a template to a queue 176 177 my $Success = $QueueObject->QueueStandardTemplateMemberAdd( 178 QueueID => 123, 179 StandardTemplateID => 123, 180 Active => 1, # to set/confirm (1) or remove (0) the relation 181 UserID => 123, 182 ); 183 184=cut 185 186sub QueueStandardTemplateMemberAdd { 187 my ( $Self, %Param ) = @_; 188 189 # check needed stuff 190 for my $Argument (qw(QueueID StandardTemplateID UserID)) { 191 if ( !$Param{$Argument} ) { 192 $Kernel::OM->Get('Kernel::System::Log')->Log( 193 Priority => 'error', 194 Message => "Need $Argument!", 195 ); 196 return; 197 } 198 } 199 200 # get database object 201 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 202 203 # delete existing relation 204 return if !$DBObject->Do( 205 SQL => 'DELETE FROM queue_standard_template 206 WHERE queue_id = ? 207 AND standard_template_id = ?', 208 Bind => [ \$Param{QueueID}, \$Param{StandardTemplateID} ], 209 ); 210 211 # return if relation is not active 212 if ( !$Param{Active} ) { 213 $Kernel::OM->Get('Kernel::System::Cache')->CleanUp( 214 Type => $Self->{CacheType}, 215 ); 216 return 1; 217 } 218 219 # insert new relation 220 my $Success = $DBObject->Do( 221 SQL => ' 222 INSERT INTO queue_standard_template (queue_id, standard_template_id, create_time, 223 create_by, change_time, change_by) 224 VALUES (?, ?, current_timestamp, ?, current_timestamp, ?)', 225 Bind => [ \$Param{QueueID}, \$Param{StandardTemplateID}, \$Param{UserID}, \$Param{UserID} ], 226 ); 227 228 $Kernel::OM->Get('Kernel::System::Cache')->CleanUp( 229 Type => $Self->{CacheType}, 230 ); 231 return $Success; 232} 233 234=head2 QueueStandardTemplateMemberList() 235 236get standard responses of a queue 237 238 my %Templates = $QueueObject->QueueStandardTemplateMemberList( QueueID => 123 ); 239 240Returns: 241 %Templates = ( 242 1 => 'Some Name', 243 2 => 'Some Name', 244 ); 245 246 my %Responses = $QueueObject->QueueStandardTemplateMemberList( 247 QueueID => 123, 248 TemplateTypes => 1, 249 ); 250 251Returns: 252 %Responses = ( 253 Answer => { 254 1 => 'Some Name', 255 2 => 'Some Name', 256 }, 257 # ... 258 ); 259 260 my %Queues = $QueueObject->QueueStandardTemplateMemberList( StandardTemplateID => 123 ); 261 262Returns: 263 %Queues = ( 264 1 => 'Some Name', 265 2 => 'Some Name', 266 ); 267 268=cut 269 270sub QueueStandardTemplateMemberList { 271 my ( $Self, %Param ) = @_; 272 273 # check needed stuff 274 if ( !$Param{QueueID} && !$Param{StandardTemplateID} ) { 275 $Kernel::OM->Get('Kernel::System::Log')->Log( 276 Priority => 'error', 277 Message => 'Got no StandardTemplateID or QueueID!', 278 ); 279 return; 280 } 281 282 # get needed objects 283 my $ValidObject = $Kernel::OM->Get('Kernel::System::Valid'); 284 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 285 286 my $TemplateTypes = $Param{TemplateTypes} || '0'; 287 288 my $CacheKey; 289 290 if ( $Param{QueueID} ) { 291 292 # check if this result is present (in cache) 293 $CacheKey = "StandardTemplates::$Param{QueueID}::$TemplateTypes"; 294 my $Cache = $Kernel::OM->Get('Kernel::System::Cache')->Get( 295 Type => $Self->{CacheType}, 296 Key => $CacheKey, 297 ); 298 return %{$Cache} if ref $Cache eq 'HASH'; 299 300 # get std. templates 301 my $SQL = "SELECT st.id, st.name, st.template_type " 302 . " FROM standard_template st, queue_standard_template qst WHERE " 303 . " qst.queue_id IN (" 304 . $DBObject->Quote( $Param{QueueID}, 'Integer' ) 305 . ") AND " 306 . " qst.standard_template_id = st.id AND " 307 . " st.valid_id IN ( ${\(join ', ', $ValidObject->ValidIDsGet())} )" 308 . " ORDER BY st.name"; 309 310 return if !$DBObject->Prepare( SQL => $SQL ); 311 312 # fetch the result 313 my %StandardTemplates; 314 while ( my @Row = $DBObject->FetchrowArray() ) { 315 316 if ( $Param{TemplateTypes} ) { 317 $StandardTemplates{ $Row[2] }->{ $Row[0] } = $Row[1]; 318 } 319 else { 320 $StandardTemplates{ $Row[0] } = $Row[1]; 321 } 322 } 323 324 # store std templates (in cache) 325 $Kernel::OM->Get('Kernel::System::Cache')->Set( 326 Type => $Self->{CacheType}, 327 TTL => $Self->{CacheTTL}, 328 Key => $CacheKey, 329 Value => \%StandardTemplates, 330 331 ); 332 return %StandardTemplates; 333 } 334 335 else { 336 337 # check if this result is present (in cache) 338 $CacheKey = "Queues::$Param{StandardTemplateID}"; 339 my $Cache = $Kernel::OM->Get('Kernel::System::Cache')->Get( 340 Type => $Self->{CacheType}, 341 Key => $CacheKey, 342 ); 343 return %{$Cache} if ref $Cache eq 'HASH'; 344 345 # get queues 346 my $SQL = "SELECT q.id, q.name " 347 . " FROM queue q, queue_standard_template qst WHERE " 348 . " qst.standard_template_id IN (" 349 . $DBObject->Quote( $Param{StandardTemplateID}, 'Integer' ) 350 . ") AND " 351 . " qst.queue_id = q.id AND " 352 . " q.valid_id IN ( ${\(join ', ', $ValidObject->ValidIDsGet())} )" 353 . " ORDER BY q.name"; 354 355 return if !$DBObject->Prepare( SQL => $SQL ); 356 357 # fetch the result 358 my %Queues; 359 while ( my @Row = $DBObject->FetchrowArray() ) { 360 $Queues{ $Row[0] } = $Row[1]; 361 } 362 363 # store queues (in cache) 364 $Kernel::OM->Get('Kernel::System::Cache')->Set( 365 Type => $Self->{CacheType}, 366 TTL => $Self->{CacheTTL}, 367 Key => $CacheKey, 368 Value => \%Queues, 369 ); 370 371 return %Queues; 372 } 373} 374 375=head2 GetAllQueues() 376 377get all valid system queues 378 379 my %Queues = $QueueObject->GetAllQueues(); 380 381get all system queues of a user with permission type (e. g. C<ro>, C<move_into>, C<rw>, ...) 382 383 my %Queues = $QueueObject->GetAllQueues( UserID => 123, Type => 'ro' ); 384 385=cut 386 387sub GetAllQueues { 388 my ( $Self, %Param ) = @_; 389 390 my $Type = $Param{Type} || 'ro'; 391 392 # get needed objects 393 my $ValidObject = $Kernel::OM->Get('Kernel::System::Valid'); 394 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 395 396 # fetch all queues 397 my $CacheKey; 398 if ( $Param{UserID} ) { 399 400 # get group list 401 my %GroupList = $Kernel::OM->Get('Kernel::System::Group')->PermissionUserGet( 402 UserID => $Param{UserID}, 403 Type => $Type, 404 ); 405 406 return if !%GroupList; 407 408 my $GroupString = join ', ', sort keys %GroupList; 409 $CacheKey = "GetAllQueues::UserID::${Type}::${GroupString}::$Param{UserID}"; 410 411 # check cache 412 my $Cache = $Kernel::OM->Get('Kernel::System::Cache')->Get( 413 Type => $Self->{CacheType}, 414 Key => $CacheKey, 415 ); 416 return %{$Cache} if $Cache; 417 418 return if !$DBObject->Prepare( 419 SQL => "SELECT id, name FROM queue WHERE " 420 . " group_id IN ( $GroupString ) AND " 421 . " valid_id IN ( ${\(join ', ', $ValidObject->ValidIDsGet())} )", 422 ); 423 } 424 elsif ( $Param{CustomerUserID} ) { 425 426 # get group ids 427 my @GroupIDs = $Kernel::OM->Get('Kernel::System::CustomerGroup')->GroupMemberList( 428 UserID => $Param{CustomerUserID}, 429 Type => $Type, 430 Result => 'ID', 431 ); 432 433 return if !@GroupIDs; 434 435 my $GroupString = join ', ', sort @GroupIDs; 436 $CacheKey = "GetAllQueues::CustomerUserID::${Type}::${GroupString}::$Param{CustomerUserID}"; 437 438 # check cache 439 my $Cache = $Kernel::OM->Get('Kernel::System::Cache')->Get( 440 Type => $Self->{CacheType}, 441 Key => $CacheKey, 442 ); 443 return %{$Cache} if $Cache; 444 445 return if !$DBObject->Prepare( 446 SQL => "SELECT id, name FROM queue WHERE " 447 . " group_id IN ( $GroupString ) AND " 448 . " valid_id IN ( ${\(join ', ', $ValidObject->ValidIDsGet())} )", 449 ); 450 } 451 else { 452 453 $CacheKey = 'GetAllQueues'; 454 455 # check cache 456 my $Cache = $Kernel::OM->Get('Kernel::System::Cache')->Get( 457 Type => $Self->{CacheType}, 458 Key => $CacheKey, 459 ); 460 return %{$Cache} if $Cache; 461 462 return if !$DBObject->Prepare( 463 SQL => "SELECT id, name FROM queue WHERE valid_id IN " 464 . "( ${\(join ', ', $ValidObject->ValidIDsGet())} )", 465 ); 466 } 467 468 my %MoveQueues; 469 while ( my @Row = $DBObject->FetchrowArray() ) { 470 $MoveQueues{ $Row[0] } = $Row[1]; 471 } 472 473 # set cache 474 $Kernel::OM->Get('Kernel::System::Cache')->Set( 475 Type => $Self->{CacheType}, 476 TTL => $Self->{CacheTTL}, 477 Key => $CacheKey, 478 Value => \%MoveQueues, 479 ); 480 481 return %MoveQueues; 482} 483 484=head2 GetAllCustomQueues() 485 486get all custom queues of one user 487 488 my @Queues = $QueueObject->GetAllCustomQueues( UserID => 123 ); 489 490=cut 491 492sub GetAllCustomQueues { 493 my ( $Self, %Param ) = @_; 494 495 # check needed stuff 496 if ( !$Param{UserID} ) { 497 $Kernel::OM->Get('Kernel::System::Log')->Log( 498 Priority => 'error', 499 Message => 'Need UserID!' 500 ); 501 return; 502 } 503 504 # check cache 505 my $CacheKey = 'GetAllCustomQueues::' . $Param{UserID}; 506 my $Cache = $Kernel::OM->Get('Kernel::System::Cache')->Get( 507 Type => $Self->{CacheType}, 508 Key => $CacheKey, 509 ); 510 return @{$Cache} if $Cache; 511 512 # get database object 513 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 514 515 # search all custom queues 516 return if !$DBObject->Prepare( 517 SQL => 'SELECT queue_id FROM personal_queues WHERE user_id = ?', 518 Bind => [ \$Param{UserID} ], 519 ); 520 521 # fetch the result 522 my @QueueIDs; 523 while ( my @Row = $DBObject->FetchrowArray() ) { 524 push @QueueIDs, $Row[0]; 525 } 526 527 # set cache 528 $Kernel::OM->Get('Kernel::System::Cache')->Set( 529 Type => $Self->{CacheType}, 530 TTL => $Self->{CacheTTL}, 531 Key => $CacheKey, 532 Value => \@QueueIDs, 533 ); 534 535 return @QueueIDs; 536} 537 538=head2 QueueLookup() 539 540get id or name for queue 541 542 my $Queue = $QueueObject->QueueLookup( QueueID => $QueueID ); 543 544 my $QueueID = $QueueObject->QueueLookup( Queue => $Queue ); 545 546=cut 547 548sub QueueLookup { 549 my ( $Self, %Param ) = @_; 550 551 # check needed stuff 552 if ( !$Param{Queue} && !$Param{QueueID} ) { 553 $Kernel::OM->Get('Kernel::System::Log')->Log( 554 Priority => 'error', 555 Message => 'Got no Queue or QueueID!' 556 ); 557 return; 558 } 559 560 # get (already cached) queue data 561 my %QueueList = $Self->QueueList( 562 Valid => 0, 563 ); 564 565 my $Key; 566 my $Value; 567 my $ReturnData; 568 if ( $Param{QueueID} ) { 569 $Key = 'QueueID'; 570 $Value = $Param{QueueID}; 571 $ReturnData = $QueueList{ $Param{QueueID} }; 572 } 573 else { 574 $Key = 'Queue'; 575 $Value = $Param{Queue}; 576 my %QueueListReverse = reverse %QueueList; 577 $ReturnData = $QueueListReverse{ $Param{Queue} }; 578 } 579 580 # check if data exists 581 if ( !$ReturnData ) { 582 $Kernel::OM->Get('Kernel::System::Log')->Log( 583 Priority => 'error', 584 Message => "Found no $Key for $Value!", 585 ); 586 return; 587 } 588 589 return $ReturnData; 590} 591 592=head2 GetFollowUpOption() 593 594get FollowUpOption for the given QueueID 595 596 my $FollowUpOption = $QueueObject->GetFollowUpOption( QueueID => $QueueID ); 597 598returns any of 'possible', 'reject', 'new ticket'. 599 600=cut 601 602sub GetFollowUpOption { 603 my ( $Self, %Param ) = @_; 604 605 # check needed stuff 606 if ( !$Param{QueueID} ) { 607 $Kernel::OM->Get('Kernel::System::Log')->Log( 608 Priority => 'error', 609 Message => 'Need QueueID!' 610 ); 611 return; 612 } 613 614 # get database object 615 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 616 617 # fetch queues data 618 return if !$DBObject->Prepare( 619 SQL => 'SELECT sf.name FROM follow_up_possible sf, queue sq ' 620 . ' WHERE sq.follow_up_id = sf.id AND sq.id = ?', 621 Bind => [ \$Param{QueueID} ], 622 Limit => 1, 623 ); 624 625 my $Return = ''; 626 while ( my @Row = $DBObject->FetchrowArray() ) { 627 $Return = $Row[0]; 628 } 629 630 return $Return; 631} 632 633=head2 GetFollowUpOptionList() 634 635get Follow-Up Option list 636 637 my %FollowUpOptionList = $QueueObject->GetFollowUpOptionList( 638 Valid => 0, # (optional) default 1 639 ); 640 641Return: 642 643 %FollowUpOptionList = ( 644 '1' => 'possible', 645 '3' => 'new ticket', 646 '2' => 'reject' 647 ) 648 649=cut 650 651sub GetFollowUpOptionList { 652 my ( $Self, %Param ) = @_; 653 654 # set default value 655 my $Valid = $Param{Valid} ? 1 : 0; 656 657 # create the valid list 658 my $ValidIDs = join ', ', $Kernel::OM->Get('Kernel::System::Valid')->ValidIDsGet(); 659 660 # build SQL 661 my $SQL = 'SELECT id, name FROM follow_up_possible'; 662 663 # add WHERE statement 664 if ($Valid) { 665 $SQL .= ' WHERE valid_id IN (' . $ValidIDs . ')'; 666 } 667 668 # get database object 669 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 670 671 # get data from database 672 return if !$DBObject->Prepare( 673 SQL => $SQL, 674 ); 675 676 # fetch the result 677 my %FollowUpOptionList; 678 while ( my @Row = $DBObject->FetchrowArray() ) { 679 $FollowUpOptionList{ $Row[0] } = $Row[1]; 680 } 681 682 return %FollowUpOptionList; 683} 684 685=head2 GetFollowUpLockOption() 686 687get FollowUpLockOption for the given QueueID 688 689 my $FollowUpLockOption = $QueueObject->GetFollowUpLockOption( QueueID => $QueueID ); 690 691returns '1' if ticket should be locked after a follow up, '0' if not. 692 693=cut 694 695sub GetFollowUpLockOption { 696 my ( $Self, %Param ) = @_; 697 698 # check needed stuff 699 if ( !$Param{QueueID} ) { 700 $Kernel::OM->Get('Kernel::System::Log')->Log( 701 Priority => 'error', 702 Message => 'Need QueueID!' 703 ); 704 return; 705 } 706 707 # get (already cached) queue data 708 my %Queue = $Self->QueueGet( 709 ID => $Param{QueueID}, 710 ); 711 712 return if !%Queue; 713 return $Queue{FollowUpLock}; 714} 715 716=head2 GetQueueGroupID() 717 718get GroupID defined for the given QueueID. 719 720 my $GroupID = $QueueObject->GetQueueGroupID( QueueID => $QueueID ); 721 722=cut 723 724sub GetQueueGroupID { 725 my ( $Self, %Param ) = @_; 726 727 # check needed stuff 728 if ( !$Param{QueueID} ) { 729 $Kernel::OM->Get('Kernel::System::Log')->Log( 730 Priority => 'error', 731 Message => 'Need QueueID!' 732 ); 733 return; 734 } 735 736 # get (already cached) queue data 737 my %Queue = $Self->QueueGet( 738 ID => $Param{QueueID}, 739 ); 740 741 return if !%Queue; 742 return $Queue{GroupID}; 743} 744 745=head2 QueueAdd() 746 747add queue with attributes 748 749 my $QueueID = $QueueObject->QueueAdd( 750 Name => 'Some::Queue', 751 ValidID => 1, 752 GroupID => 1, 753 Calendar => '1', # (optional) 754 FirstResponseTime => 120, # (optional) 755 FirstResponseNotify => 60, # (optional, notify agent if first response escalation is 60% reached) 756 UpdateTime => 180, # (optional) 757 UpdateNotify => 80, # (optional, notify agent if update escalation is 80% reached) 758 SolutionTime => 580, # (optional) 759 SolutionNotify => 80, # (optional, notify agent if solution escalation is 80% reached) 760 UnlockTimeout => 480, # (optional) 761 FollowUpID => 3, # possible (1), reject (2) or new ticket (3) (optional, default 0) 762 FollowUpLock => 0, # yes (1) or no (0) (optional, default 0) 763 DefaultSignKey => 'key name', # (optional) 764 SystemAddressID => 1, 765 SalutationID => 1, 766 SignatureID => 1, 767 Comment => 'Some comment', 768 UserID => 123, 769 ); 770 771=cut 772 773sub QueueAdd { 774 my ( $Self, %Param ) = @_; 775 776 # check if this request is from web and not from command line 777 if ( !$Param{NoDefaultValues} ) { 778 for ( 779 qw(UnlockTimeout FirstResponseTime FirstResponseNotify UpdateTime UpdateNotify SolutionTime SolutionNotify 780 FollowUpLock SystemAddressID SalutationID SignatureID 781 FollowUpID FollowUpLock DefaultSignKey Calendar) 782 ) 783 { 784 785 # I added default values in the Load Routine 786 if ( !$Param{$_} ) { 787 $Param{$_} = $Self->{QueueDefaults}->{$_} || 0; 788 } 789 } 790 } 791 792 for (qw(Name GroupID SystemAddressID SalutationID SignatureID ValidID UserID FollowUpID)) { 793 if ( !$Param{$_} ) { 794 $Kernel::OM->Get('Kernel::System::Log')->Log( 795 Priority => 'error', 796 Message => "Need $_!" 797 ); 798 return; 799 } 800 } 801 802 # cleanup queue name 803 $Param{Name} =~ s/(\n|\r)//g; 804 $Param{Name} =~ s/\s$//g; 805 806 # check queue name 807 if ( $Param{Name} =~ /::$/i ) { 808 $Kernel::OM->Get('Kernel::System::Log')->Log( 809 Priority => 'error', 810 Message => "Invalid Queue name '$Param{Name}'!", 811 ); 812 return; 813 } 814 815 # check if a queue with this name already exists 816 if ( $Self->NameExistsCheck( Name => $Param{Name} ) ) { 817 $Kernel::OM->Get('Kernel::System::Log')->Log( 818 Priority => 'error', 819 Message => "A queue with the name '$Param{Name}' already exists.", 820 ); 821 return; 822 } 823 824 # get needed objects 825 my $ConfigObject = $Kernel::OM->Get('Kernel::Config'); 826 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 827 828 return if !$DBObject->Do( 829 SQL => 'INSERT INTO queue (name, group_id, unlock_timeout, system_address_id, ' 830 . ' calendar_name, default_sign_key, salutation_id, signature_id, ' 831 . ' first_response_time, first_response_notify, update_time, ' 832 . ' update_notify, solution_time, solution_notify, follow_up_id, ' 833 . ' follow_up_lock, valid_id, comments, create_time, create_by, ' 834 . ' change_time, change_by) VALUES ' 835 . ' (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ' 836 . ' ?, current_timestamp, ?, current_timestamp, ?)', 837 Bind => [ 838 \$Param{Name}, \$Param{GroupID}, \$Param{UnlockTimeout}, \$Param{SystemAddressID}, 839 \$Param{Calendar}, \$Param{DefaultSignKey}, \$Param{SalutationID}, \$Param{SignatureID}, 840 \$Param{FirstResponseTime}, \$Param{FirstResponseNotify}, \$Param{UpdateTime}, 841 \$Param{UpdateNotify}, \$Param{SolutionTime}, \$Param{SolutionNotify}, 842 \$Param{FollowUpID}, \$Param{FollowUpLock}, \$Param{ValidID}, 843 \$Param{Comment}, \$Param{UserID}, \$Param{UserID}, 844 ], 845 ); 846 847 # get new id 848 return if !$DBObject->Prepare( 849 SQL => 'SELECT id FROM queue WHERE name = ?', 850 Bind => [ \$Param{Name} ], 851 Limit => 1, 852 ); 853 854 # fetch the result 855 my $QueueID = ''; 856 while ( my @Row = $DBObject->FetchrowArray() ) { 857 $QueueID = $Row[0]; 858 } 859 860 # reset cache 861 $Kernel::OM->Get('Kernel::System::Cache')->CleanUp( 862 Type => $Self->{CacheType}, 863 ); 864 865 my $StandardTemplate2QueueByCreating = $ConfigObject->Get('StandardTemplate2QueueByCreating'); 866 867 # add default responses (if needed), add response by name 868 if ( 869 $StandardTemplate2QueueByCreating 870 && ref $StandardTemplate2QueueByCreating eq 'ARRAY' 871 && @{$StandardTemplate2QueueByCreating} 872 ) 873 { 874 875 # get standard template object 876 my $StandardTemplateObject = $Kernel::OM->Get('Kernel::System::StandardTemplate'); 877 878 ST: 879 for my $ST ( @{$StandardTemplate2QueueByCreating} ) { 880 881 my $StandardTemplateID = $StandardTemplateObject->StandardTemplateLookup( 882 StandardTemplate => $ST, 883 ); 884 885 next ST if !$StandardTemplateID; 886 887 $Self->QueueStandardTemplateMemberAdd( 888 QueueID => $QueueID, 889 StandardTemplateID => $StandardTemplateID, 890 Active => 1, 891 UserID => $Param{UserID}, 892 ); 893 } 894 } 895 896 # get standard template id 897 my $StandardTemplateID2QueueByCreating = $ConfigObject->Get(' StandardTemplate2QueueByCreating'); 898 899 # get queue data with updated name for QueueCreate event 900 my %Queue = $Self->QueueGet( Name => $Param{Name} ); 901 902 # trigger event 903 $Self->EventHandler( 904 Event => 'QueueCreate', 905 Data => { 906 Queue => \%Queue, 907 }, 908 UserID => $Param{UserID}, 909 ); 910 911 return $QueueID if !$StandardTemplateID2QueueByCreating; 912 return $QueueID if ref $StandardTemplateID2QueueByCreating ne 'ARRAY'; 913 return $QueueID if !@{$StandardTemplateID2QueueByCreating}; 914 915 # add template by id 916 for my $StandardTemplateID ( @{$StandardTemplateID2QueueByCreating} ) { 917 918 $Self->QueueStandardTemplateMemberAdd( 919 QueueID => $QueueID, 920 StandardTemplateID => $StandardTemplateID, 921 Active => 1, 922 UserID => $Param{UserID}, 923 ); 924 } 925 926 return $QueueID; 927} 928 929=head2 QueueGet() 930 931get queue attributes 932 933 my %Queue = $QueueObject->QueueGet( 934 ID => 123, 935 ); 936 937 my %Queue = $QueueObject->QueueGet( 938 Name => 'Some::Queue', 939 ); 940 941=cut 942 943sub QueueGet { 944 my ( $Self, %Param ) = @_; 945 946 # check needed stuff 947 if ( !$Param{ID} && !$Param{Name} ) { 948 $Kernel::OM->Get('Kernel::System::Log')->Log( 949 Priority => 'error', 950 Message => 'Need ID or Name!' 951 ); 952 return; 953 } 954 955 # check runtime cache 956 my $CacheKey; 957 my $Key; 958 my $Value; 959 if ( $Param{ID} ) { 960 $CacheKey = 'QueueGetID::' . $Param{ID}; 961 $Key = 'ID'; 962 $Value = $Param{ID}; 963 } 964 else { 965 $CacheKey = 'QueueGetName::' . $Param{Name}; 966 $Key = 'Name'; 967 $Value = $Param{Name}; 968 } 969 970 # check cache 971 my $Cache = $Kernel::OM->Get('Kernel::System::Cache')->Get( 972 Type => $Self->{CacheType}, 973 Key => $CacheKey, 974 ); 975 return %{$Cache} if $Cache; 976 977 # sql 978 my @Bind; 979 my $SQL = 'SELECT q.name, q.group_id, q.unlock_timeout, ' 980 . 'q.system_address_id, q.salutation_id, q.signature_id, q.comments, q.valid_id, ' 981 . 'q.first_response_time, q.first_response_notify, ' 982 . 'q.update_time, q.update_notify, q.solution_time, q.solution_notify, ' 983 . 'q.follow_up_id, q.follow_up_lock, sa.value0, sa.value1, q.id, ' 984 . 'q.default_sign_key, q.calendar_name, q.create_time, q.change_time FROM queue q, ' 985 . 'system_address sa WHERE q.system_address_id = sa.id AND '; 986 987 if ( $Param{ID} ) { 988 $SQL .= 'q.id = ?'; 989 push @Bind, \$Param{ID}; 990 } 991 else { 992 $SQL .= 'q.name = ?'; 993 push @Bind, \$Param{Name}; 994 } 995 996 # get database object 997 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 998 999 return if !$DBObject->Prepare( 1000 SQL => $SQL, 1001 Bind => \@Bind, 1002 Limit => 1, 1003 ); 1004 1005 # fetch the result 1006 my %Data; 1007 while ( my @Data = $DBObject->FetchrowArray() ) { 1008 %Data = ( 1009 QueueID => $Data[18], 1010 Name => $Data[0], 1011 GroupID => $Data[1], 1012 UnlockTimeout => $Data[2], 1013 FirstResponseTime => $Data[8], 1014 FirstResponseNotify => $Data[9], 1015 UpdateTime => $Data[10], 1016 UpdateNotify => $Data[11], 1017 SolutionTime => $Data[12], 1018 SolutionNotify => $Data[13], 1019 FollowUpID => $Data[14], 1020 FollowUpLock => $Data[15], 1021 SystemAddressID => $Data[3], 1022 SalutationID => $Data[4], 1023 SignatureID => $Data[5], 1024 Comment => $Data[6], 1025 ValidID => $Data[7], 1026 Email => $Data[16], 1027 RealName => $Data[17], 1028 DefaultSignKey => $Data[19], 1029 Calendar => $Data[20] || '', 1030 CreateTime => $Data[21], 1031 ChangeTime => $Data[22], 1032 ); 1033 } 1034 1035 # check if data exists 1036 if ( !%Data ) { 1037 $Kernel::OM->Get('Kernel::System::Log')->Log( 1038 Priority => 'error', 1039 Message => "Found no $Key for $Value!", 1040 ); 1041 return; 1042 } 1043 1044 # get queue preferences 1045 my %Preferences = $Self->QueuePreferencesGet( QueueID => $Data{QueueID} ); 1046 1047 # merge hash 1048 if (%Preferences) { 1049 %Data = ( %Data, %Preferences ); 1050 } 1051 1052 # set cache 1053 $Kernel::OM->Get('Kernel::System::Cache')->Set( 1054 Type => $Self->{CacheType}, 1055 TTL => $Self->{CacheTTL}, 1056 Key => $CacheKey, 1057 Value => \%Data, 1058 ); 1059 1060 return %Data; 1061} 1062 1063=head2 QueueUpdate() 1064 1065update queue attributes 1066 1067 my $Success = $QueueObject->QueueUpdate( 1068 QueueID => 123, 1069 Name => 'Some::Queue', 1070 ValidID => 1, 1071 GroupID => 1, 1072 Calendar => '1', # (optional) default '' 1073 FirstResponseTime => 120, # (optional) 1074 FirstResponseNotify => 60, # (optional, notify agent if first response escalation is 60% reached) 1075 UpdateTime => 180, # (optional) 1076 UpdateNotify => 80, # (optional, notify agent if update escalation is 80% reached) 1077 SolutionTime => 580, # (optional) 1078 SolutionNotify => 80, # (optional, notify agent if solution escalation is 80% reached) 1079 SystemAddressID => 1, 1080 SalutationID => 1, 1081 SignatureID => 1, 1082 UserID => 123, 1083 FollowUpID => 1, 1084 Comment => 'Some Comment2', 1085 DefaultSignKey => '' 1086 UnlockTimeOut => '' 1087 FollowUpLock => 1, 1088 ParentQueueID => '', 1089 ); 1090 1091=cut 1092 1093sub QueueUpdate { 1094 my ( $Self, %Param ) = @_; 1095 1096 # check needed stuff 1097 for ( 1098 qw(QueueID Name ValidID GroupID SystemAddressID SalutationID SignatureID UserID FollowUpID) 1099 ) 1100 { 1101 if ( !$Param{$_} ) { 1102 $Kernel::OM->Get('Kernel::System::Log')->Log( 1103 Priority => 'error', 1104 Message => "Need $_!" 1105 ); 1106 return; 1107 } 1108 } 1109 1110 # FollowUpLock 0 | 1 1111 $Param{FollowUpLock} = $Param{FollowUpLock} || 0; 1112 1113 # DefaultSignKey '' || 'string' 1114 $Param{DefaultSignKey} = $Param{DefaultSignKey} || ''; 1115 1116 # Calendar string '', '1', '2', '3', '4', '5' default '' 1117 $Param{Calendar} ||= ''; 1118 1119 # content -> time in seconds 1120 for my $Time (qw( UnlockTimeout FirstResponseTime UpdateTime SolutionTime )) { 1121 1122 $Param{$Time} = $Param{$Time} || 0; 1123 1124 if ( $Param{$Time} !~ m{^\d+$}smx ) { 1125 $Kernel::OM->Get('Kernel::System::Log')->Log( 1126 Priority => 'error', 1127 Message => "$Time is not numeric!" 1128 ); 1129 return; 1130 } 1131 } 1132 1133 # content integer from 0 - 99 1134 for my $Notify (qw(FirstResponseNotify UpdateNotify SolutionNotify)) { 1135 1136 $Param{$Notify} = $Param{$Notify} || 0; 1137 1138 if ( $Param{$Notify} !~ m{^\d{1,2}}smx ) { 1139 $Kernel::OM->Get('Kernel::System::Log')->Log( 1140 Priority => 'error', 1141 Message => "$Notify must be an integer in the range from 0 to 99!", 1142 ); 1143 return; 1144 } 1145 } 1146 1147 # cleanup queue name 1148 $Param{Name} =~ s/(\n|\r)//g; 1149 $Param{Name} =~ s/\s$//g; 1150 1151 # check queue name 1152 if ( $Param{Name} =~ /::$/i ) { 1153 $Kernel::OM->Get('Kernel::System::Log')->Log( 1154 Priority => 'error', 1155 Message => "Invalid Queue name '$Param{Name}'!", 1156 ); 1157 return; 1158 } 1159 1160 # check if queue name exists 1161 my %AllQueue = $Self->QueueList( Valid => 0 ); 1162 my %OldQueue = $Self->QueueGet( ID => $Param{QueueID} ); 1163 1164 # check if a queue with this name already exists 1165 if ( 1166 $Self->NameExistsCheck( 1167 ID => $Param{QueueID}, 1168 Name => $Param{Name} 1169 ) 1170 ) 1171 { 1172 $Kernel::OM->Get('Kernel::System::Log')->Log( 1173 Priority => 'error', 1174 Message => "A queue with the name '$Param{Name}' already exists.", 1175 ); 1176 return; 1177 } 1178 1179 # get database object 1180 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 1181 1182 # SQL 1183 return if !$DBObject->Do( 1184 SQL => ' 1185 UPDATE queue 1186 SET name = ?, comments = ?, group_id = ?, unlock_timeout = ?, first_response_time = ?, 1187 first_response_notify = ?, update_time = ?, update_notify = ?, solution_time = ?, 1188 solution_notify = ?, follow_up_id = ?, follow_up_lock = ?, system_address_id = ?, 1189 calendar_name = ?, default_sign_key = ?, salutation_id = ?, signature_id = ?, 1190 valid_id = ?, change_time = current_timestamp, change_by = ? 1191 WHERE id = ?', 1192 Bind => [ 1193 \$Param{Name}, \$Param{Comment}, \$Param{GroupID}, \$Param{UnlockTimeout}, 1194 \$Param{FirstResponseTime}, \$Param{FirstResponseNotify}, \$Param{UpdateTime}, 1195 \$Param{UpdateNotify}, \$Param{SolutionTime}, \$Param{SolutionNotify}, 1196 \$Param{FollowUpID}, \$Param{FollowUpLock}, \$Param{SystemAddressID}, 1197 \$Param{Calendar}, \$Param{DefaultSignKey}, \$Param{SalutationID}, 1198 \$Param{SignatureID}, \$Param{ValidID}, \$Param{UserID}, 1199 \$Param{QueueID}, 1200 ], 1201 ); 1202 1203 # get queue data with updated name for QueueUpdate event 1204 my %Queue = $Self->QueueGet( Name => $Param{Name} ); 1205 1206 # trigger event 1207 $Self->EventHandler( 1208 Event => 'QueueUpdate', 1209 Data => { 1210 Queue => \%Queue, 1211 OldQueue => \%OldQueue, 1212 }, 1213 UserID => $Param{UserID}, 1214 ); 1215 1216 # reset cache 1217 $Kernel::OM->Get('Kernel::System::Cache')->CleanUp( 1218 Type => $Self->{CacheType}, 1219 ); 1220 1221 # updated all sub queue names 1222 my @ParentQueue = split( /::/, $OldQueue{Name} ); 1223 1224 for my $QueueID ( sort keys %AllQueue ) { 1225 1226 my @SubQueue = split( /::/, $AllQueue{$QueueID} ); 1227 1228 if ( $#SubQueue > $#ParentQueue ) { 1229 1230 if ( $AllQueue{$QueueID} =~ /^\Q$OldQueue{Name}::\E/i ) { 1231 1232 my $NewQueueName = $AllQueue{$QueueID}; 1233 $NewQueueName =~ s/\Q$OldQueue{Name}\E/$Param{Name}/; 1234 1235 return if !$DBObject->Do( 1236 SQL => ' 1237 UPDATE queue 1238 SET name = ?, change_time = current_timestamp, change_by = ? 1239 WHERE id = ?', 1240 Bind => [ \$NewQueueName, \$Param{UserID}, \$QueueID ], 1241 ); 1242 1243 # reset cache 1244 $Kernel::OM->Get('Kernel::System::Cache')->CleanUp( 1245 Type => $Self->{CacheType}, 1246 ); 1247 } 1248 } 1249 } 1250 1251 return 1; 1252} 1253 1254=head2 QueueList() 1255 1256get all queues 1257 1258 my %Queues = $QueueObject->QueueList(); 1259 1260 my %Queues = $QueueObject->QueueList( Valid => 1 ); 1261 1262=cut 1263 1264sub QueueList { 1265 my ( $Self, %Param ) = @_; 1266 1267 # set valid option 1268 my $Valid = $Param{Valid}; 1269 if ( !defined $Valid || $Valid ) { 1270 $Valid = 1; 1271 } 1272 else { 1273 $Valid = 0; 1274 } 1275 1276 # check cache 1277 my $CacheKey = 'QueueList::' . $Valid; 1278 my $Cache = $Kernel::OM->Get('Kernel::System::Cache')->Get( 1279 Type => $Self->{CacheType}, 1280 Key => $CacheKey, 1281 ); 1282 return %{$Cache} if $Cache; 1283 1284 # get database object 1285 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 1286 1287 # sql query 1288 if ($Valid) { 1289 return if !$DBObject->Prepare( 1290 SQL => "SELECT id, name FROM queue WHERE valid_id IN " 1291 . "( ${\(join ', ', $Kernel::OM->Get('Kernel::System::Valid')->ValidIDsGet())} )", 1292 ); 1293 } 1294 else { 1295 return if !$DBObject->Prepare( 1296 SQL => 'SELECT id, name FROM queue', 1297 ); 1298 } 1299 1300 # fetch the result 1301 my %Queues; 1302 while ( my @Row = $DBObject->FetchrowArray() ) { 1303 $Queues{ $Row[0] } = $Row[1]; 1304 } 1305 1306 # set cache 1307 $Kernel::OM->Get('Kernel::System::Cache')->Set( 1308 Type => $Self->{CacheType}, 1309 TTL => $Self->{CacheTTL}, 1310 Key => $CacheKey, 1311 Value => \%Queues, 1312 ); 1313 1314 return %Queues; 1315} 1316 1317=head2 QueuePreferencesSet() 1318 1319set queue preferences 1320 1321 $QueueObject->QueuePreferencesSet( 1322 QueueID => 123, 1323 Key => 'UserComment', 1324 Value => 'some comment', 1325 UserID => 123, 1326 ); 1327 1328=cut 1329 1330sub QueuePreferencesSet { 1331 my ( $Self, %Param ) = @_; 1332 1333 # delete cache 1334 my $Name = $Self->QueueLookup( QueueID => $Param{QueueID} ); 1335 my @CacheKeys = ( 1336 'QueueGetID::' . $Param{QueueID}, 1337 'QueueGetName::' . $Name, 1338 ); 1339 for my $CacheKey (@CacheKeys) { 1340 $Kernel::OM->Get('Kernel::System::Cache')->Delete( 1341 Type => $Self->{CacheType}, 1342 Key => $CacheKey, 1343 ); 1344 } 1345 1346 return $Self->{PreferencesObject}->QueuePreferencesSet(%Param); 1347} 1348 1349=head2 QueuePreferencesGet() 1350 1351get queue preferences 1352 1353 my %Preferences = $QueueObject->QueuePreferencesGet( 1354 QueueID => 123, 1355 UserID => 123, 1356 ); 1357 1358=cut 1359 1360sub QueuePreferencesGet { 1361 my ( $Self, %Param ) = @_; 1362 1363 return $Self->{PreferencesObject}->QueuePreferencesGet(%Param); 1364} 1365 1366sub DESTROY { 1367 my $Self = shift; 1368 1369 # execute all transaction events 1370 $Self->EventHandlerTransaction(); 1371 1372 return 1; 1373} 1374 1375=head2 NameExistsCheck() 1376 1377return 1 if another queue with this name already exists 1378 1379 $Exist = $QueueObject->NameExistsCheck( 1380 Name => 'Some::Queue', 1381 ID => 1, # optional 1382 ); 1383 1384=cut 1385 1386sub NameExistsCheck { 1387 my ( $Self, %Param ) = @_; 1388 1389 # get database object 1390 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 1391 return if !$DBObject->Prepare( 1392 SQL => 'SELECT id FROM queue WHERE name = ?', 1393 Bind => [ \$Param{Name} ], 1394 ); 1395 1396 # fetch the result 1397 my $Flag; 1398 while ( my @Row = $DBObject->FetchrowArray() ) { 1399 if ( !$Param{ID} || $Param{ID} ne $Row[0] ) { 1400 $Flag = 1; 1401 } 1402 } 1403 1404 if ($Flag) { 1405 return 1; 1406 } 1407 1408 return 0; 1409} 1410 14111; 1412 1413=head1 TERMS AND CONDITIONS 1414 1415This software is part of the OTRS project (L<https://otrs.org/>). 1416 1417This software comes with ABSOLUTELY NO WARRANTY. For details, see 1418the enclosed file COPYING for license information (GPL). If you 1419did not receive this file, see L<https://www.gnu.org/licenses/gpl-3.0.txt>. 1420 1421=cut 1422