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::DynamicFieldValue;
10
11use strict;
12use warnings;
13
14use Kernel::System::VariableCheck qw(:all);
15
16our @ObjectDependencies = (
17    'Kernel::Config',
18    'Kernel::System::Cache',
19    'Kernel::System::DateTime',
20    'Kernel::System::DB',
21    'Kernel::System::Log',
22);
23
24=head1 NAME
25
26Kernel::System::DynamicFieldValue
27
28=head1 DESCRIPTION
29
30DynamicField values backend
31
32=head1 PUBLIC INTERFACE
33
34=head2 new()
35
36create a DynamicFieldValue object. Do not use it directly, instead use:
37
38    my $DynamicFieldValueObject = $Kernel::OM->Get('Kernel::System::DynamicFieldValue');
39
40=cut
41
42sub new {
43    my ( $Type, %Param ) = @_;
44
45    # allocate new hash for object
46    my $Self = {};
47    bless( $Self, $Type );
48
49    return $Self;
50}
51
52=head2 ValueSet()
53
54sets a dynamic field value. This is represented by one or more rows in the dynamic_field_value
55table, each storing one text, date and int field. Please see how they will be returned by
56L</ValueGet()>.
57
58    my $Success = $DynamicFieldValueObject->ValueSet(
59        FieldID  => $FieldID,                 # ID of the dynamic field
60        ObjectID => $ObjectID,                # ID of the current object that the field
61                                              #   must be linked to, e. g. TicketID
62        Value    => [
63            {
64                ValueText          => 'some text',            # optional, one of these fields must be provided
65                ValueDateTime      => '1977-12-12 12:00:00',  # optional
66                ValueInt           => 123,                    # optional
67            },
68            ...
69        ],
70        UserID   => $UserID,
71    );
72
73=cut
74
75sub ValueSet {
76    my ( $Self, %Param ) = @_;
77
78    # check needed stuff
79    for my $Needed (qw(FieldID ObjectID Value)) {
80        if ( !$Param{$Needed} ) {
81            $Kernel::OM->Get('Kernel::System::Log')->Log(
82                Priority => 'error',
83                Message  => "Need $Needed!"
84            );
85            return;
86        }
87    }
88
89    # return if no Value was provided
90    if ( ref $Param{Value} ne 'ARRAY' || !$Param{Value}->[0] )
91    {
92        $Kernel::OM->Get('Kernel::System::Log')->Log(
93            Priority => 'error',
94            Message  => "Need Param{Value}!"
95        );
96        return;
97    }
98
99    my @Values;
100
101    my $Counter = 0;
102    VALUE:
103    while (1) {
104        if ( ref $Param{Value}->[$Counter] ne 'HASH' ) {
105            last VALUE;
106        }
107
108        if (
109            (
110                !defined $Param{Value}->[$Counter]->{ValueText}
111
112                # do not accept an empty string as it is the same as NULL on oracle
113                || !length $Param{Value}->[$Counter]->{ValueText}
114            )
115            && !defined $Param{Value}->[$Counter]->{ValueInt}
116            && !defined $Param{Value}->[$Counter]->{ValueDateTime}
117            )
118        {
119            last VALUE;
120        }
121
122        my %Value = (
123            ValueText     => scalar $Param{Value}->[$Counter]->{ValueText},
124            ValueInt      => scalar $Param{Value}->[$Counter]->{ValueInt},
125            ValueDateTime => scalar $Param{Value}->[$Counter]->{ValueDateTime},
126        );
127
128        # data validation
129        return if !$Self->ValueValidate( Value => \%Value );
130
131        # data conversions
132
133        # set ValueDateTime column to NULL
134        if ( exists $Value{ValueDateTime} && !$Value{ValueDateTime} ) {
135            $Value{ValueDateTime} = undef;
136        }
137
138        # set Int Zero
139        if ( defined $Value{ValueInt} && !$Value{ValueInt} ) {
140            $Value{ValueInt} = '0';
141        }
142
143        push @Values, \%Value;
144        $Counter++;
145    }
146
147    # delete existing value
148    $Self->ValueDelete(
149        FieldID  => $Param{FieldID},
150        ObjectID => $Param{ObjectID},
151        UserID   => $Param{UserID},
152    );
153
154    # get database object
155    my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
156
157    for my $Value (@Values) {
158
159        # create a new value entry
160        return if !$DBObject->Do(
161            SQL =>
162                'INSERT INTO dynamic_field_value (field_id, object_id, value_text, value_date, value_int)'
163                . ' VALUES (?, ?, ?, ?, ?)',
164            Bind => [
165                \$Param{FieldID}, \$Param{ObjectID},
166                \$Value->{ValueText}, \$Value->{ValueDateTime}, \$Value->{ValueInt},
167            ],
168        );
169    }
170
171    # delete cache
172    $Self->_DeleteFromCache(%Param);
173
174    return 1;
175}
176
177=head2 ValueGet()
178
179get a dynamic field value. For each table row there will be one entry in the
180result list.
181
182    my $Value = $DynamicFieldValueObject->ValueGet(
183        FieldID            => $FieldID,                 # ID of the dynamic field
184        ObjectID           => $ObjectID,                # ID of the current object that the field
185                                                        #   is linked to, e. g. TicketID
186    );
187
188    Returns [
189        {
190            ID                 => 437,
191            ValueText          => 'some text',
192            ValueDateTime      => '1977-12-12 12:00:00',
193            ValueInt           => 123,
194        },
195    ];
196
197=cut
198
199sub ValueGet {
200    my ( $Self, %Param ) = @_;
201
202    # check needed stuff
203    for my $Needed (qw(FieldID ObjectID)) {
204        if ( !$Param{$Needed} ) {
205            $Kernel::OM->Get('Kernel::System::Log')->Log(
206                Priority => 'error',
207                Message  => "Need $Needed!"
208            );
209            return;
210        }
211    }
212
213    # get cache object
214    my $CacheObject = $Kernel::OM->Get('Kernel::System::Cache');
215
216    #
217    # Special caching strategy: cache all fields of an object in one cache file.
218    #   This avoids too many cache files on systems with many fields for many objects.
219    #
220
221    my $CacheKey = 'ValueGet::ObjectID::' . $Param{ObjectID};
222
223    my $Cache = $CacheObject->Get(
224        Type => 'DynamicFieldValue',
225        Key  => $CacheKey,
226    );
227
228    # Check if a cache entry exists
229    if ( ref $Cache eq 'HASH' ) {
230
231        if ( exists $Cache->{ $Param{FieldID} } ) {
232            return $Cache->{ $Param{FieldID} };
233        }
234        return [];
235    }
236
237    # get database object
238    my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
239
240    # We'll populate cache with all object's dynamic fields to reduce
241    # number of db accesses (only one db query for all dynamic fields till
242    # cache expiration); return only specified one dynamic field
243    return if !$DBObject->Prepare(
244        SQL =>
245            'SELECT id, value_text, value_date, value_int, field_id
246            FROM dynamic_field_value
247            WHERE object_id = ?
248            ORDER BY id',
249        Bind => [ \$Param{ObjectID} ],
250    );
251
252    my %CacheData;
253
254    while ( my @Data = $DBObject->FetchrowArray() ) {
255
256        # cleanup time stamps (some databases are using e. g. 2008-02-25 22:03:00.000000
257        # and 0000-00-00 00:00:00 time stamps)
258        if ( $Data[2] ) {
259            if ( $Data[2] eq '0000-00-00 00:00:00' ) {
260                $Data[2] = undef;
261            }
262            $Data[2] =~ s/^(\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2})\..+?$/$1/;
263        }
264
265        push @{ $CacheData{ $Data[4] } }, {
266            ID            => $Data[0],
267            ValueText     => $Data[1],
268            ValueDateTime => $Data[2],
269            ValueInt      => $Data[3],
270        };
271    }
272
273    # get the cache TTL (in seconds)
274    my $CacheTTL = $Kernel::OM->Get('Kernel::Config')->Get('DynamicField::CacheTTL') || 60 * 60 * 12;
275
276    # set cache
277    $CacheObject->Set(
278        Type  => 'DynamicFieldValue',
279        Key   => $CacheKey,
280        Value => \%CacheData,
281        TTL   => $CacheTTL,
282    );
283
284    if ( exists $CacheData{ $Param{FieldID} } ) {
285        return $CacheData{ $Param{FieldID} };
286    }
287
288    return [];
289}
290
291=head2 ValueDelete()
292
293delete a Dynamic field value entry. All associated rows will be deleted.
294
295    my $Success = $DynamicFieldValueObject->ValueDelete(
296        FieldID            => $FieldID,                 # ID of the dynamic field
297        ObjectID           => $ObjectID,                # ID of the current object that the field
298                                                        #   is linked to, e. g. TicketID
299        UserID  => 123,
300    );
301
302    Returns 1.
303
304=cut
305
306sub ValueDelete {
307    my ( $Self, %Param ) = @_;
308
309    # check needed stuff
310    for my $Needed (qw(FieldID ObjectID UserID)) {
311        if ( !$Param{$Needed} ) {
312            $Kernel::OM->Get('Kernel::System::Log')->Log(
313                Priority => 'error',
314                Message  => "Need $Needed!"
315            );
316            return;
317        }
318    }
319
320    # delete dynamic field value
321    return if !$Kernel::OM->Get('Kernel::System::DB')->Do(
322        SQL  => 'DELETE FROM dynamic_field_value WHERE field_id = ? AND object_id = ?',
323        Bind => [ \$Param{FieldID}, \$Param{ObjectID} ],
324    );
325
326    # delete cache
327    $Self->_DeleteFromCache(%Param);
328
329    return 1;
330}
331
332=head2 AllValuesDelete()
333
334delete all entries of a dynamic field .
335
336    my $Success = $DynamicFieldValueObject->AllValuesDelete(
337        FieldID            => $FieldID,                 # ID of the dynamic field
338        UserID  => 123,
339    );
340
341    Returns 1.
342
343=cut
344
345sub AllValuesDelete {
346    my ( $Self, %Param ) = @_;
347
348    # check needed stuff
349    for my $Needed (qw(FieldID UserID)) {
350        if ( !$Param{$Needed} ) {
351            $Kernel::OM->Get('Kernel::System::Log')->Log(
352                Priority => 'error',
353                Message  => "Need $Needed!"
354            );
355            return;
356        }
357    }
358
359    # delete dynamic field value
360    return if !$Kernel::OM->Get('Kernel::System::DB')->Do(
361        SQL  => 'DELETE FROM dynamic_field_value WHERE field_id = ?',
362        Bind => [ \$Param{FieldID} ],
363    );
364
365    # Cleanup entire cache!
366    $Kernel::OM->Get('Kernel::System::Cache')->CleanUp(
367        Type => 'DynamicFieldValue',
368    );
369
370    return 1;
371}
372
373=head2 ObjectValuesDelete()
374
375Delete all entries of a dynamic field values for object ID.
376
377    my $Success = $DynamicFieldValueObject->ObjectValuesDelete(
378        ObjectType => 'Ticket',    # Dynamic Field object type ( e. g. Ticket, Article, FAQ)
379        ObjectID   => $ObjectID,   # ID of the current object that the field
380                                   #   is linked to, e. g. TicketID
381        UserID     => 123,
382    );
383
384    Returns 1.
385
386=cut
387
388sub ObjectValuesDelete {
389    my ( $Self, %Param ) = @_;
390
391    # Check needed stuff.
392    for my $Needed (qw(ObjectID ObjectType UserID)) {
393        if ( !$Param{$Needed} ) {
394            $Kernel::OM->Get('Kernel::System::Log')->Log(
395                Priority => 'error',
396                Message  => "Need $Needed!"
397            );
398            return;
399        }
400    }
401
402    # Delete dynamic field value.
403    return if !$Kernel::OM->Get('Kernel::System::DB')->Do(
404        SQL => '
405            DELETE FROM dynamic_field_value
406                WHERE
407                    field_id IN (
408                        SELECT id FROM dynamic_field
409                        WHERE object_type = ?
410                    )
411                    AND object_id = ?
412        ',
413        Bind => [ \$Param{ObjectType}, \$Param{ObjectID} ],
414    );
415
416    # Clear ValueGet cache.
417    $Kernel::OM->Get('Kernel::System::Cache')->Delete(
418        Type => 'DynamicFieldValue',
419        Key  => 'ValueGet::ObjectID::' . $Param{ObjectID},
420    );
421
422    return 1;
423}
424
425=head2 ValueValidate()
426
427checks if the given value is valid for the value type.
428
429    my $Success = $DynamicFieldValueObject->ValueValidate(
430        Value    =>  {
431                ValueText          => 'some text',            # optional, one of these fields must be provided
432                ValueDateTime      => '1977-12-12 12:00:00',  # optional
433                ValueInt           => 123,                    # optional
434            },
435        UserID   => $UserID,
436    );
437
438=cut
439
440sub ValueValidate {
441    my ( $Self, %Param ) = @_;
442
443    return if !IsHashRefWithData( $Param{Value} );
444
445    my %Value = %{ $Param{Value} };
446
447    # validate date
448    if ( $Value{ValueDateTime} ) {
449
450        # get time object
451        my $DateTimeObject = $Kernel::OM->Create('Kernel::System::DateTime');
452
453        # convert the DateTime value to system time to check errors
454        my $SystemTime = $DateTimeObject->Set(
455            String => $Value{ValueDateTime},
456        );
457
458        return if !defined $SystemTime;
459
460        # convert back to time stamp to check errors
461        my $TimeStamp = $DateTimeObject->ToString();
462
463        return if !$TimeStamp;
464
465        # compare if the date is the same
466        return if !( $Value{ValueDateTime} eq $TimeStamp );
467    }
468
469    # validate integer
470    if ( $Value{ValueInt} ) {
471
472        if ( $Value{ValueInt} !~ m{\A  -? \d+ \z}smx ) {
473            $Kernel::OM->Get('Kernel::System::Log')->Log(
474                Priority => 'error',
475                Message  => "Invalid Integer '$Value{ValueInt}'!"
476            );
477
478            return;
479        }
480    }
481
482    return 1;
483}
484
485=head2 HistoricalValueGet()
486
487get all distinct values from a field stored on the database
488
489    my $HistoricalValues = $DynamicFieldValueObject->HistoricalValueGet(
490        FieldID   => $FieldID,                  # ID of the dynamic field
491        ValueType => 'Text',                    # or 'DateTime' or 'Integer'. Default 'Text'
492    );
493
494    Returns:
495
496    $HistoricalValues{
497        ValueA => 'ValueA',
498        ValueB => 'ValueB',
499        ValueC => 'ValueC'
500    };
501
502=cut
503
504sub HistoricalValueGet {
505    my ( $Self, %Param ) = @_;
506
507    # check needed stuff
508    for my $Needed (qw(FieldID)) {
509        if ( !$Param{$Needed} ) {
510            $Kernel::OM->Get('Kernel::System::Log')->Log(
511                Priority => 'error',
512                Message  => "Need $Needed!"
513            );
514            return;
515        }
516    }
517
518    my $ValueType = 'value_text';
519    if ( $Param{ValueType} && $Param{ValueType} eq 'DateTime' ) {
520        $ValueType = 'value_date';
521    }
522    elsif ( $Param{ValueType} && $Param{ValueType} eq 'Integer' ) {
523        $ValueType = 'value_int';
524    }
525
526    # get cache object
527    my $CacheObject = $Kernel::OM->Get('Kernel::System::Cache');
528
529    # check cache
530    my $CacheKey = join '::', 'HistoricalValueGet::FieldID', $Param{FieldID}, 'ValueType',
531        $ValueType;
532
533    my $Cache = $CacheObject->Get(
534        Type => 'DynamicFieldValue',
535        Key  => $CacheKey,
536    );
537
538    # get data from cache
539    return $Cache if ($Cache);
540
541    # get database object
542    my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
543
544    return if !$DBObject->Prepare(
545        SQL =>
546            "SELECT DISTINCT($ValueType) FROM dynamic_field_value WHERE field_id = ?",
547        Bind => [ \$Param{FieldID} ],
548    );
549
550    my %Data;
551    while ( my @Row = $DBObject->FetchrowArray() ) {
552
553        # check if the value is already stored
554        if ( $Row[0] && !$Data{ $Row[0] } ) {
555
556            if ( $ValueType eq 'value_date' ) {
557
558                # cleanup time stamps (some databases are using e. g. 2008-02-25 22:03:00.000000
559                # and 0000-00-00 00:00:00 time stamps)
560                if ( $Row[0] eq '0000-00-00 00:00:00' ) {
561                    $Row[0] = undef;
562                }
563                $Row[0] =~ s/^(\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2})\..+?$/$1/;
564            }
565
566            # store the results
567            $Data{ $Row[0] } = $Row[0];
568        }
569    }
570
571    # get the cache TTL (in seconds)
572    my $CacheTTL = $Kernel::OM->Get('Kernel::Config')->Get('DynamicField::CacheTTL') || 60 * 60 * 12;
573
574    # set cache
575    $CacheObject->Set(
576        Type  => 'DynamicFieldValue',
577        Key   => $CacheKey,
578        Value => \%Data,
579        TTL   => $CacheTTL,
580    );
581
582    return \%Data;
583}
584
585=head2 ValueSearch()
586
587Searches/fetches dynamic field value.
588
589    my $Value = $DynamicFieldValueObject->ValueSearch(
590        FieldID            => 142,             # ID of dynamic field to search
591        Search             => 'test',
592        SearchSQL          => "dynamic_field_value.value_text = 'test'",
593    );
594
595    Returns [
596        {
597            ID            => 437,
598            FieldID       => 123,
599            ObjectID      => 133,
600            ValueText     => 'some text',
601            ValueDateTime => '1977-12-12 12:00:00',
602            ValueInt      => 123,
603        },
604        # ...
605    ];
606
607=cut
608
609sub ValueSearch {
610    my ( $Self, %Param ) = @_;
611
612    # check mandatory parameters
613    if ( !$Param{FieldID} ) {
614        $Kernel::OM->Get('Kernel::System::Log')->Log(
615            Priority => 'error',
616            Message  => "Need FieldID!"
617        );
618        return;
619    }
620
621    for my $Param (qw( Search SearchSQL )) {
622        if ( !defined $Param{$Param} || !length $Param{$Param} ) {
623            $Kernel::OM->Get('Kernel::System::Log')->Log(
624                Priority => 'error',
625                Message  => "Need $Param!"
626            );
627            return;
628        }
629    }
630
631    my @Values;
632
633    # Cache handling
634    my $CacheObject = $Kernel::OM->Get('Kernel::System::Cache');
635
636    my $CacheType      = 'DynamicFieldValue';
637    my $CacheKey       = 'ValueSearch::' . $Param{FieldID};
638    my $CacheSearchKey = $Param{Search};
639
640    my $Cache = $CacheObject->Get(
641        Type => $CacheType,
642        Key  => $CacheKey,
643    );
644
645    # Check if a cache entry exists
646    if (
647        IsHashRefWithData($Cache)
648        && exists $Cache->{$CacheSearchKey}
649        && IsArrayRefWithData( $Cache->{$CacheSearchKey} )
650        )
651    {
652        for my $Value ( @{ $Cache->{$CacheSearchKey} } ) {
653            push @Values, $Value;
654        }
655    }
656
657    return \@Values if @Values;
658
659    my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
660
661    my $SQL = '
662        SELECT id, field_id, object_id, value_text, value_date, value_int
663        FROM  dynamic_field_value
664        WHERE  dynamic_field_value.field_id = ?
665            AND ';
666
667    $SQL .= $Param{SearchSQL};
668
669    return if !$DBObject->Prepare(
670        SQL  => $SQL,
671        Bind => [
672            \$Param{FieldID},
673
674            # @{ $QueryCondition{Values} },
675        ],
676    );
677
678    while ( my @Data = $DBObject->FetchrowArray() ) {
679        push @Values, {
680            ID        => $Data[0],
681            FieldID   => $Data[1],
682            ObjectID  => $Data[2],
683            ValueText => $Data[3],
684            ValueDate => $Data[4],
685            ValueInt  => $Data[5],
686        };
687    }
688
689    # get the cache TTL (in seconds)
690    my $CacheTTL = $Kernel::OM->Get('Kernel::Config')->Get('DynamicField::CacheTTL') || 60 * 60 * 12;
691
692    # set cache for new field ID if it isn't set yet.
693    # note: it's possible there is no database record for a given field ID. in this case, an empty
694    # but defined value has to be stored. Otherwise there would be a database query the next time.
695    if ( !ref $Cache || ref $Cache ne 'HASH' ) {
696        $Cache = {
697            $CacheSearchKey => undef,
698        };
699    }
700
701    if (@Values) {
702        for my $Value (@Values) {
703            push @{ $Cache->{$CacheSearchKey} }, $Value;
704        }
705    }
706    else {
707        $Cache->{$CacheSearchKey} = undef;
708    }
709
710    $CacheObject->Set(
711        Type  => $CacheType,
712        Key   => $CacheKey,
713        Value => $Cache,
714        TTL   => $CacheTTL,
715    );
716
717    return \@Values;
718}
719
720#
721# Deletes all needed cache entries for a given DynamicFieldValue.
722#
723sub _DeleteFromCache {
724
725    my ( $Self, %Param ) = @_;
726
727    # check needed stuff
728    for my $Needed (qw(FieldID ObjectID)) {
729        if ( !$Param{$Needed} ) {
730            $Kernel::OM->Get('Kernel::System::Log')->Log(
731                Priority => 'error',
732                Message  => "Need $Needed!"
733            );
734            return;
735        }
736    }
737
738    # get cache object
739    my $CacheObject = $Kernel::OM->Get('Kernel::System::Cache');
740
741    # Clear ValueGet cache
742    $CacheObject->Delete(
743        Type => 'DynamicFieldValue',
744        Key  => 'ValueGet::ObjectID::' . $Param{ObjectID},
745    );
746
747    # Clear HistoricalValueGet caches
748    $CacheObject->Delete(
749        Type => 'DynamicFieldValue',
750        Key  => 'HistoricalValueGet::FieldID::' . $Param{FieldID} . '::ValueType::Text',
751    );
752    $CacheObject->Delete(
753        Type => 'DynamicFieldValue',
754        Key  => 'HistoricalValueGet::FieldID::' . $Param{FieldID} . '::ValueType::DateTime',
755    );
756    $CacheObject->Delete(
757        Type => 'DynamicFieldValue',
758        Key  => 'HistoricalValueGet::FieldID::' . $Param{FieldID} . '::ValueType::Integer',
759    );
760
761    # Make sure to clear appropriate cache according to data value type.
762    if ( IsArrayRefWithData( $Param{Value} ) ) {
763        my $ValueType = 'value_text';
764        if ( $Param{Value}->[0]->{ValueDateTime} ) {
765            $ValueType = 'value_date';
766        }
767        elsif ( $Param{Value}->[0]->{ValueInt} ) {
768            $ValueType = 'value_int';
769        }
770        $CacheObject->Delete(
771            Type => 'DynamicFieldValue',
772            Key  => 'HistoricalValueGet::FieldID::' . $Param{FieldID} . '::ValueType::' . $ValueType,
773        );
774    }
775
776    $CacheObject->Delete(
777        Type => 'DynamicFieldValue',
778        Key  => 'ValueSearch::' . $Param{FieldID},
779    );
780
781    return 1;
782}
783
7841;
785
786=head1 TERMS AND CONDITIONS
787
788This software is part of the OTRS project (L<https://otrs.org/>).
789
790This software comes with ABSOLUTELY NO WARRANTY. For details, see
791the enclosed file COPYING for license information (GPL). If you
792did not receive this file, see L<https://www.gnu.org/licenses/gpl-3.0.txt>.
793
794=cut
795