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