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