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::Service; 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::CheckItem', 20 'Kernel::System::DB', 21 'Kernel::System::Log', 22 'Kernel::System::Main', 23 'Kernel::System::Valid', 24); 25 26=head1 NAME 27 28Kernel::System::Service - service lib 29 30=head1 DESCRIPTION 31 32All service functions. 33 34=head1 PUBLIC INTERFACE 35 36=head2 new() 37 38create an object 39 40 my $ServiceObject = $Kernel::OM->Get('Kernel::System::Service'); 41 42=cut 43 44sub new { 45 my ( $Type, %Param ) = @_; 46 47 # allocate new hash for object 48 my $Self = {}; 49 bless( $Self, $Type ); 50 51 $Self->{CacheType} = 'Service'; 52 $Self->{CacheTTL} = 60 * 60 * 24 * 20; 53 54 # load generator preferences module 55 my $GeneratorModule = $Kernel::OM->Get('Kernel::Config')->Get('Service::PreferencesModule') 56 || 'Kernel::System::Service::PreferencesDB'; 57 if ( $Kernel::OM->Get('Kernel::System::Main')->Require($GeneratorModule) ) { 58 $Self->{PreferencesObject} = $GeneratorModule->new(); 59 } 60 61 return $Self; 62} 63 64=head2 ServiceList() 65 66return a hash list of services 67 68 my %ServiceList = $ServiceObject->ServiceList( 69 Valid => 0, # (optional) default 1 (0|1) 70 UserID => 1, 71 ); 72 73=cut 74 75sub ServiceList { 76 my ( $Self, %Param ) = @_; 77 78 # check needed stuff 79 if ( !$Param{UserID} ) { 80 $Kernel::OM->Get('Kernel::System::Log')->Log( 81 Priority => 'error', 82 Message => 'Need UserID!', 83 ); 84 return; 85 } 86 87 # check valid param 88 if ( !defined $Param{Valid} ) { 89 $Param{Valid} = 1; 90 } 91 92 # read cache 93 my $CacheKey = 'ServiceList::Valid::' . $Param{Valid}; 94 95 if ( $Param{Valid} && defined $Param{KeepChildren} && $Param{KeepChildren} eq '1' ) { 96 $CacheKey .= '::KeepChildren::' . $Param{KeepChildren}; 97 } 98 99 my $Cache = $Kernel::OM->Get('Kernel::System::Cache')->Get( 100 Type => $Self->{CacheType}, 101 Key => $CacheKey, 102 ); 103 return %{$Cache} if ref $Cache eq 'HASH'; 104 105 # get database object 106 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 107 108 # ask database 109 $DBObject->Prepare( 110 SQL => 'SELECT id, name, valid_id FROM service', 111 ); 112 113 # fetch the result 114 my %ServiceList; 115 my %ServiceValidList; 116 while ( my @Row = $DBObject->FetchrowArray() ) { 117 $ServiceList{ $Row[0] } = $Row[1]; 118 $ServiceValidList{ $Row[0] } = $Row[2]; 119 } 120 121 if ( !$Param{Valid} ) { 122 $Kernel::OM->Get('Kernel::System::Cache')->Set( 123 Type => $Self->{CacheType}, 124 TTL => $Self->{CacheTTL}, 125 Key => $CacheKey, 126 Value => \%ServiceList, 127 ); 128 return %ServiceList if !$Param{Valid}; 129 } 130 131 # get valid ids 132 my @ValidIDs = $Kernel::OM->Get('Kernel::System::Valid')->ValidIDsGet(); 133 134 # duplicate service list 135 my %ServiceListTmp = %ServiceList; 136 137 # add suffix for correct sorting 138 for my $ServiceID ( sort keys %ServiceListTmp ) { 139 $ServiceListTmp{$ServiceID} .= '::'; 140 } 141 142 my %ServiceInvalidList; 143 SERVICEID: 144 for my $ServiceID ( sort { $ServiceListTmp{$a} cmp $ServiceListTmp{$b} } keys %ServiceListTmp ) 145 { 146 147 my $Valid = scalar grep { $_ eq $ServiceValidList{$ServiceID} } @ValidIDs; 148 149 next SERVICEID if $Valid; 150 151 $ServiceInvalidList{ $ServiceList{$ServiceID} } = 1; 152 delete $ServiceList{$ServiceID}; 153 } 154 155 # delete invalid services and children 156 if ( !defined $Param{KeepChildren} || !$Param{KeepChildren} ) { 157 for my $ServiceID ( sort keys %ServiceList ) { 158 159 INVALIDNAME: 160 for my $InvalidName ( sort keys %ServiceInvalidList ) { 161 162 if ( $ServiceList{$ServiceID} =~ m{ \A \Q$InvalidName\E :: }xms ) { 163 delete $ServiceList{$ServiceID}; 164 last INVALIDNAME; 165 } 166 } 167 } 168 } 169 170 # set cache 171 $Kernel::OM->Get('Kernel::System::Cache')->Set( 172 Type => $Self->{CacheType}, 173 TTL => $Self->{CacheTTL}, 174 Key => $CacheKey, 175 Value => \%ServiceList, 176 ); 177 178 return %ServiceList; 179} 180 181=head2 ServiceListGet() 182 183return a list of services with the complete list of attributes for each service 184 185 my $ServiceList = $ServiceObject->ServiceListGet( 186 Valid => 0, # (optional) default 1 (0|1) 187 UserID => 1, 188 ); 189 190 returns 191 192 $ServiceList = [ 193 { 194 ServiceID => 1, 195 ParentID => 0, 196 Name => 'MyService', 197 NameShort => 'MyService', 198 ValidID => 1, 199 Comment => 'Some Comment' 200 CreateTime => '2011-02-08 15:08:00', 201 ChangeTime => '2011-06-11 17:22:00', 202 CreateBy => 1, 203 ChangeBy => 1, 204 }, 205 { 206 ServiceID => 2, 207 ParentID => 1, 208 Name => 'MyService::MySubService', 209 NameShort => 'MySubService', 210 ValidID => 1, 211 Comment => 'Some Comment' 212 CreateTime => '2011-02-08 15:08:00', 213 ChangeTime => '2011-06-11 17:22:00', 214 CreateBy => 1, 215 ChangeBy => 1, 216 }, 217 # ... 218 ]; 219 220=cut 221 222sub ServiceListGet { 223 my ( $Self, %Param ) = @_; 224 225 # check needed stuff 226 if ( !$Param{UserID} ) { 227 $Kernel::OM->Get('Kernel::System::Log')->Log( 228 Priority => 'error', 229 Message => 'Need UserID!', 230 ); 231 return; 232 } 233 234 # check valid param 235 if ( !defined $Param{Valid} ) { 236 $Param{Valid} = 1; 237 } 238 239 # check cached results 240 my $CacheKey = 'Cache::ServiceListGet::Valid::' . $Param{Valid}; 241 my $Cache = $Kernel::OM->Get('Kernel::System::Cache')->Get( 242 Type => $Self->{CacheType}, 243 Key => $CacheKey, 244 ); 245 return $Cache if defined $Cache; 246 247 # create SQL query 248 my $SQL = 'SELECT id, name, valid_id, comments, create_time, create_by, change_time, change_by ' 249 . 'FROM service'; 250 251 if ( $Param{Valid} ) { 252 $SQL .= ' WHERE valid_id IN (' . join ', ', 253 $Kernel::OM->Get('Kernel::System::Valid')->ValidIDsGet() . ')'; 254 } 255 256 $SQL .= ' ORDER BY name'; 257 258 # get database object 259 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 260 261 # ask database 262 $DBObject->Prepare( 263 SQL => $SQL, 264 ); 265 266 # fetch the result 267 my @ServiceList; 268 my %ServiceName2ID; 269 while ( my @Row = $DBObject->FetchrowArray() ) { 270 my %ServiceData; 271 $ServiceData{ServiceID} = $Row[0]; 272 $ServiceData{Name} = $Row[1]; 273 $ServiceData{ValidID} = $Row[2]; 274 $ServiceData{Comment} = $Row[3] || ''; 275 $ServiceData{CreateTime} = $Row[4]; 276 $ServiceData{CreateBy} = $Row[5]; 277 $ServiceData{ChangeTime} = $Row[6]; 278 $ServiceData{ChangeBy} = $Row[7]; 279 280 # add service data to service list 281 push @ServiceList, \%ServiceData; 282 283 # build service id lookup hash 284 $ServiceName2ID{ $ServiceData{Name} } = $ServiceData{ServiceID}; 285 } 286 287 for my $ServiceData (@ServiceList) { 288 289 # create short name and parentid 290 $ServiceData->{NameShort} = $ServiceData->{Name}; 291 if ( $ServiceData->{Name} =~ m{ \A (.*) :: (.+?) \z }xms ) { 292 my $ParentName = $1; 293 $ServiceData->{NameShort} = $2; 294 $ServiceData->{ParentID} = $ServiceName2ID{$ParentName}; 295 } 296 297 # get service preferences 298 my %Preferences = $Self->ServicePreferencesGet( 299 ServiceID => $ServiceData->{ServiceID}, 300 ); 301 302 # merge hash 303 if (%Preferences) { 304 %{$ServiceData} = ( %{$ServiceData}, %Preferences ); 305 } 306 } 307 308 if (@ServiceList) { 309 310 # set cache 311 $Kernel::OM->Get('Kernel::System::Cache')->Set( 312 Type => $Self->{CacheType}, 313 TTL => $Self->{CacheTTL}, 314 Key => $CacheKey, 315 Value => \@ServiceList, 316 ); 317 } 318 319 return \@ServiceList; 320} 321 322=head2 ServiceGet() 323 324return a service as hash 325 326Return 327 $ServiceData{ServiceID} 328 $ServiceData{ParentID} 329 $ServiceData{Name} 330 $ServiceData{NameShort} 331 $ServiceData{ValidID} 332 $ServiceData{Comment} 333 $ServiceData{CreateTime} 334 $ServiceData{CreateBy} 335 $ServiceData{ChangeTime} 336 $ServiceData{ChangeBy} 337 338 my %ServiceData = $ServiceObject->ServiceGet( 339 ServiceID => 123, 340 UserID => 1, 341 ); 342 343 my %ServiceData = $ServiceObject->ServiceGet( 344 Name => 'Service::SubService', 345 UserID => 1, 346 ); 347 348=cut 349 350sub ServiceGet { 351 my ( $Self, %Param ) = @_; 352 353 # check needed stuff 354 if ( !$Param{UserID} ) { 355 $Kernel::OM->Get('Kernel::System::Log')->Log( 356 Priority => 'error', 357 Message => "Need UserID!", 358 ); 359 return; 360 } 361 362 # either ServiceID or Name must be passed 363 if ( !$Param{ServiceID} && !$Param{Name} ) { 364 $Kernel::OM->Get('Kernel::System::Log')->Log( 365 Priority => 'error', 366 Message => 'Need ServiceID or Name!', 367 ); 368 return; 369 } 370 371 # check that not both ServiceID and Name are given 372 if ( $Param{ServiceID} && $Param{Name} ) { 373 $Kernel::OM->Get('Kernel::System::Log')->Log( 374 Priority => 'error', 375 Message => 'Need either ServiceID OR Name - not both!', 376 ); 377 return; 378 } 379 380 # lookup the ServiceID 381 if ( $Param{Name} ) { 382 $Param{ServiceID} = $Self->ServiceLookup( 383 Name => $Param{Name}, 384 ); 385 } 386 387 # check cached results 388 my $CacheKey = 'Cache::ServiceGet::' . $Param{ServiceID}; 389 my $Cache = $Kernel::OM->Get('Kernel::System::Cache')->Get( 390 Type => $Self->{CacheType}, 391 Key => $CacheKey, 392 ); 393 return %{$Cache} if ref $Cache eq 'HASH'; 394 395 # get database object 396 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 397 398 # get service from db 399 $DBObject->Prepare( 400 SQL => 401 'SELECT id, name, valid_id, comments, create_time, create_by, change_time, change_by ' 402 . 'FROM service WHERE id = ?', 403 Bind => [ \$Param{ServiceID} ], 404 Limit => 1, 405 ); 406 407 # fetch the result 408 my %ServiceData; 409 while ( my @Row = $DBObject->FetchrowArray() ) { 410 $ServiceData{ServiceID} = $Row[0]; 411 $ServiceData{Name} = $Row[1]; 412 $ServiceData{ValidID} = $Row[2]; 413 $ServiceData{Comment} = $Row[3] || ''; 414 $ServiceData{CreateTime} = $Row[4]; 415 $ServiceData{CreateBy} = $Row[5]; 416 $ServiceData{ChangeTime} = $Row[6]; 417 $ServiceData{ChangeBy} = $Row[7]; 418 } 419 420 # check service 421 if ( !$ServiceData{ServiceID} ) { 422 $Kernel::OM->Get('Kernel::System::Log')->Log( 423 Priority => 'error', 424 Message => "No such ServiceID ($Param{ServiceID})!", 425 ); 426 return; 427 } 428 429 # create short name and parentid 430 $ServiceData{NameShort} = $ServiceData{Name}; 431 if ( $ServiceData{Name} =~ m{ \A (.*) :: (.+?) \z }xms ) { 432 $ServiceData{NameShort} = $2; 433 434 # lookup parent 435 my $ServiceID = $Self->ServiceLookup( 436 Name => $1, 437 ); 438 $ServiceData{ParentID} = $ServiceID; 439 } 440 441 # get service preferences 442 my %Preferences = $Self->ServicePreferencesGet( 443 ServiceID => $Param{ServiceID}, 444 ); 445 446 # merge hash 447 if (%Preferences) { 448 %ServiceData = ( %ServiceData, %Preferences ); 449 } 450 451 # set cache 452 $Kernel::OM->Get('Kernel::System::Cache')->Set( 453 Type => $Self->{CacheType}, 454 TTL => $Self->{CacheTTL}, 455 Key => $CacheKey, 456 Value => \%ServiceData, 457 ); 458 459 return %ServiceData; 460} 461 462=head2 ServiceLookup() 463 464return a service name and id 465 466 my $ServiceName = $ServiceObject->ServiceLookup( 467 ServiceID => 123, 468 ); 469 470 or 471 472 my $ServiceID = $ServiceObject->ServiceLookup( 473 Name => 'Service::SubService', 474 ); 475 476=cut 477 478sub ServiceLookup { 479 my ( $Self, %Param ) = @_; 480 481 # check needed stuff 482 if ( !$Param{ServiceID} && !$Param{Name} ) { 483 $Kernel::OM->Get('Kernel::System::Log')->Log( 484 Priority => 'error', 485 Message => 'Need ServiceID or Name!', 486 ); 487 return; 488 } 489 490 if ( $Param{ServiceID} ) { 491 492 # check cache 493 my $CacheKey = 'Cache::ServiceLookup::ID::' . $Param{ServiceID}; 494 my $Cache = $Kernel::OM->Get('Kernel::System::Cache')->Get( 495 Type => $Self->{CacheType}, 496 Key => $CacheKey, 497 ); 498 return $Cache if defined $Cache; 499 500 # get database object 501 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 502 503 # lookup 504 $DBObject->Prepare( 505 SQL => 'SELECT name FROM service WHERE id = ?', 506 Bind => [ \$Param{ServiceID} ], 507 Limit => 1, 508 ); 509 510 my $Result = ''; 511 while ( my @Row = $DBObject->FetchrowArray() ) { 512 $Result = $Row[0]; 513 } 514 515 $Kernel::OM->Get('Kernel::System::Cache')->Set( 516 Type => $Self->{CacheType}, 517 TTL => $Self->{CacheTTL}, 518 Key => $CacheKey, 519 Value => $Result, 520 ); 521 522 return $Result; 523 } 524 else { 525 526 # check cache 527 my $CacheKey = 'Cache::ServiceLookup::Name::' . $Param{Name}; 528 my $Cache = $Kernel::OM->Get('Kernel::System::Cache')->Get( 529 Type => $Self->{CacheType}, 530 Key => $CacheKey, 531 ); 532 return $Cache if defined $Cache; 533 534 # get database object 535 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 536 537 # lookup 538 $DBObject->Prepare( 539 SQL => 'SELECT id FROM service WHERE name = ?', 540 Bind => [ \$Param{Name} ], 541 Limit => 1, 542 ); 543 544 my $Result = ''; 545 while ( my @Row = $DBObject->FetchrowArray() ) { 546 $Result = $Row[0]; 547 } 548 549 $Kernel::OM->Get('Kernel::System::Cache')->Set( 550 Type => $Self->{CacheType}, 551 TTL => $Self->{CacheTTL}, 552 Key => $CacheKey, 553 Value => $Result, 554 ); 555 556 return $Result; 557 } 558} 559 560=head2 ServiceAdd() 561 562add a service 563 564 my $ServiceID = $ServiceObject->ServiceAdd( 565 Name => 'Service Name', 566 ParentID => 1, # (optional) 567 ValidID => 1, 568 Comment => 'Comment', # (optional) 569 UserID => 1, 570 ); 571 572=cut 573 574sub ServiceAdd { 575 my ( $Self, %Param ) = @_; 576 577 # check needed stuff 578 for my $Argument (qw(Name ValidID UserID)) { 579 if ( !$Param{$Argument} ) { 580 $Kernel::OM->Get('Kernel::System::Log')->Log( 581 Priority => 'error', 582 Message => "Need $Argument!", 583 ); 584 return; 585 } 586 } 587 588 # set comment 589 $Param{Comment} ||= ''; 590 591 # cleanup given params 592 for my $Argument (qw(Name Comment)) { 593 $Kernel::OM->Get('Kernel::System::CheckItem')->StringClean( 594 StringRef => \$Param{$Argument}, 595 RemoveAllNewlines => 1, 596 RemoveAllTabs => 1, 597 ); 598 } 599 600 # check service name 601 if ( $Param{Name} =~ m{ :: }xms ) { 602 $Kernel::OM->Get('Kernel::System::Log')->Log( 603 Priority => 'error', 604 Message => "Can't add service! Invalid Service name '$Param{Name}'!", 605 ); 606 return; 607 } 608 609 # create full name 610 $Param{FullName} = $Param{Name}; 611 612 # get parent name 613 if ( $Param{ParentID} ) { 614 my $ParentName = $Self->ServiceLookup( 615 ServiceID => $Param{ParentID}, 616 ); 617 if ($ParentName) { 618 $Param{FullName} = $ParentName . '::' . $Param{Name}; 619 } 620 } 621 622 # get database object 623 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 624 625 # find existing service 626 $DBObject->Prepare( 627 SQL => 'SELECT id FROM service WHERE name = ?', 628 Bind => [ \$Param{FullName} ], 629 Limit => 1, 630 ); 631 632 my $Exists; 633 while ( $DBObject->FetchrowArray() ) { 634 $Exists = 1; 635 } 636 637 # add service to database 638 if ($Exists) { 639 $Kernel::OM->Get('Kernel::System::Log')->Log( 640 Priority => 'error', 641 Message => "A service with the name and parent '$Param{FullName}' already exists.", 642 ); 643 return; 644 } 645 646 return if !$DBObject->Do( 647 SQL => 'INSERT INTO service ' 648 . '(name, valid_id, comments, create_time, create_by, change_time, change_by) ' 649 . 'VALUES (?, ?, ?, current_timestamp, ?, current_timestamp, ?)', 650 Bind => [ 651 \$Param{FullName}, \$Param{ValidID}, \$Param{Comment}, 652 \$Param{UserID}, \$Param{UserID}, 653 ], 654 ); 655 656 # get service id 657 $DBObject->Prepare( 658 SQL => 'SELECT id FROM service WHERE name = ?', 659 Bind => [ \$Param{FullName} ], 660 Limit => 1, 661 ); 662 my $ServiceID; 663 while ( my @Row = $DBObject->FetchrowArray() ) { 664 $ServiceID = $Row[0]; 665 } 666 667 # reset cache 668 $Kernel::OM->Get('Kernel::System::Cache')->CleanUp( 669 Type => $Self->{CacheType}, 670 ); 671 672 return $ServiceID; 673} 674 675=head2 ServiceUpdate() 676 677update an existing service 678 679 my $True = $ServiceObject->ServiceUpdate( 680 ServiceID => 123, 681 ParentID => 1, # (optional) 682 Name => 'Service Name', 683 ValidID => 1, 684 Comment => 'Comment', # (optional) 685 UserID => 1, 686 ); 687 688=cut 689 690sub ServiceUpdate { 691 my ( $Self, %Param ) = @_; 692 693 # check needed stuff 694 for my $Argument (qw(ServiceID Name ValidID UserID)) { 695 if ( !$Param{$Argument} ) { 696 $Kernel::OM->Get('Kernel::System::Log')->Log( 697 Priority => 'error', 698 Message => "Need $Argument!", 699 ); 700 return; 701 } 702 } 703 704 # set default comment 705 $Param{Comment} ||= ''; 706 707 # cleanup given params 708 for my $Argument (qw(Name Comment)) { 709 $Kernel::OM->Get('Kernel::System::CheckItem')->StringClean( 710 StringRef => \$Param{$Argument}, 711 RemoveAllNewlines => 1, 712 RemoveAllTabs => 1, 713 ); 714 } 715 716 # check service name 717 if ( $Param{Name} =~ m{ :: }xms ) { 718 $Kernel::OM->Get('Kernel::System::Log')->Log( 719 Priority => 'error', 720 Message => "Can't update service! Invalid Service name '$Param{Name}'!", 721 ); 722 return; 723 } 724 725 # get old name of service 726 my $OldServiceName = $Self->ServiceLookup( 727 ServiceID => $Param{ServiceID}, 728 ); 729 730 if ( !$OldServiceName ) { 731 $Kernel::OM->Get('Kernel::System::Log')->Log( 732 Priority => 'error', 733 Message => "Can't update service! Service '$Param{ServiceID}' does not exist.", 734 ); 735 return; 736 } 737 738 # create full name 739 $Param{FullName} = $Param{Name}; 740 741 # get parent name 742 if ( $Param{ParentID} ) { 743 744 # lookup service 745 my $ParentName = $Self->ServiceLookup( 746 ServiceID => $Param{ParentID}, 747 ); 748 749 if ($ParentName) { 750 $Param{FullName} = $ParentName . '::' . $Param{Name}; 751 } 752 753 # check, if selected parent was a child of this service 754 if ( $Param{FullName} =~ m{ \A ( \Q$OldServiceName\E ) :: }xms ) { 755 $Kernel::OM->Get('Kernel::System::Log')->Log( 756 Priority => 'error', 757 Message => 'Can\'t update service! Invalid parent was selected.' 758 ); 759 return; 760 } 761 } 762 763 # get database object 764 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 765 766 # find exists service 767 $DBObject->Prepare( 768 SQL => 'SELECT id FROM service WHERE name = ?', 769 Bind => [ \$Param{FullName} ], 770 Limit => 1, 771 ); 772 my $Exists; 773 while ( my @Row = $DBObject->FetchrowArray() ) { 774 if ( $Param{ServiceID} ne $Row[0] ) { 775 $Exists = 1; 776 } 777 } 778 779 # update service 780 if ($Exists) { 781 $Kernel::OM->Get('Kernel::System::Log')->Log( 782 Priority => 'error', 783 Message => "A service with the name and parent '$Param{FullName}' already exists.", 784 ); 785 return; 786 787 } 788 789 # update service 790 return if !$DBObject->Do( 791 SQL => 'UPDATE service SET name = ?, valid_id = ?, comments = ?, ' 792 . ' change_time = current_timestamp, change_by = ? WHERE id = ?', 793 Bind => [ 794 \$Param{FullName}, \$Param{ValidID}, \$Param{Comment}, 795 \$Param{UserID}, \$Param{ServiceID}, 796 ], 797 ); 798 799 my $LikeService = $DBObject->Quote( $OldServiceName, 'Like' ) . '::%'; 800 801 # find all childs 802 $DBObject->Prepare( 803 SQL => "SELECT id, name FROM service WHERE name LIKE ?", 804 Bind => [ \$LikeService ], 805 ); 806 807 my @Childs; 808 while ( my @Row = $DBObject->FetchrowArray() ) { 809 my %Child; 810 $Child{ServiceID} = $Row[0]; 811 $Child{Name} = $Row[1]; 812 push @Childs, \%Child; 813 } 814 815 # update childs 816 for my $Child (@Childs) { 817 $Child->{Name} =~ s{ \A ( \Q$OldServiceName\E ) :: }{$Param{FullName}::}xms; 818 $DBObject->Do( 819 SQL => 'UPDATE service SET name = ? WHERE id = ?', 820 Bind => [ \$Child->{Name}, \$Child->{ServiceID} ], 821 ); 822 } 823 824 # reset cache 825 $Kernel::OM->Get('Kernel::System::Cache')->CleanUp( 826 Type => $Self->{CacheType}, 827 ); 828 829 return 1; 830} 831 832=head2 ServiceSearch() 833 834return service ids as an array 835 836 my @ServiceList = $ServiceObject->ServiceSearch( 837 Name => 'Service Name', # (optional) 838 Limit => 122, # (optional) default 1000 839 UserID => 1, 840 ); 841 842=cut 843 844sub ServiceSearch { 845 my ( $Self, %Param ) = @_; 846 847 # check needed stuff 848 if ( !$Param{UserID} ) { 849 $Kernel::OM->Get('Kernel::System::Log')->Log( 850 Priority => 'error', 851 Message => 'Need UserID!', 852 ); 853 return; 854 } 855 856 # set default limit 857 $Param{Limit} ||= 1000; 858 859 # create sql query 860 my $SQL 861 = "SELECT id FROM service WHERE valid_id IN ( ${\(join ', ', $Kernel::OM->Get('Kernel::System::Valid')->ValidIDsGet())} )"; 862 my @Bind; 863 864 # get database object 865 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 866 867 if ( $Param{Name} ) { 868 869 # quote 870 $Param{Name} = $DBObject->Quote( $Param{Name}, 'Like' ); 871 872 # replace * with % and clean the string 873 $Param{Name} =~ s{ \*+ }{%}xmsg; 874 $Param{Name} =~ s{ %+ }{%}xmsg; 875 my $LikeString = '%' . $Param{Name} . '%'; 876 push @Bind, \$LikeString; 877 878 $SQL .= " AND name LIKE ?"; 879 } 880 881 $SQL .= ' ORDER BY name'; 882 883 # search service in db 884 $DBObject->Prepare( 885 SQL => $SQL, 886 Bind => \@Bind, 887 ); 888 889 my @ServiceList; 890 while ( my @Row = $DBObject->FetchrowArray() ) { 891 push @ServiceList, $Row[0]; 892 } 893 894 return @ServiceList; 895} 896 897=head2 CustomerUserServiceMemberList() 898 899returns a list of customeruser/service members 900 901 ServiceID: service id 902 CustomerUserLogin: customer user login 903 DefaultServices: activate or deactivate default services 904 905 Result: HASH -> returns a hash of key => service id, value => service name 906 Name -> returns an array of user names 907 ID -> returns an array of user ids 908 909 Example (get services of customer user): 910 911 $ServiceObject->CustomerUserServiceMemberList( 912 CustomerUserLogin => 'Test', 913 Result => 'HASH', 914 DefaultServices => 0, 915 ); 916 917 Example (get customer user of service): 918 919 $ServiceObject->CustomerUserServiceMemberList( 920 ServiceID => $ID, 921 Result => 'HASH', 922 ); 923 924=cut 925 926sub CustomerUserServiceMemberList { 927 my ( $Self, %Param ) = @_; 928 929 # check needed stuff 930 if ( !$Param{Result} ) { 931 $Kernel::OM->Get('Kernel::System::Log')->Log( 932 Priority => 'error', 933 Message => 'Need Result!', 934 ); 935 return; 936 } 937 938 # set default (only 1 or 0 is allowed to correctly set the cache key) 939 if ( !defined $Param{DefaultServices} || $Param{DefaultServices} ) { 940 $Param{DefaultServices} = 1; 941 } 942 else { 943 $Param{DefaultServices} = 0; 944 } 945 946 # get options for default services for unknown customers 947 my $DefaultServiceUnknownCustomer 948 = $Kernel::OM->Get('Kernel::Config')->Get('Ticket::Service::Default::UnknownCustomer'); 949 if ( 950 $DefaultServiceUnknownCustomer 951 && $Param{DefaultServices} 952 && !$Param{ServiceID} 953 && !$Param{CustomerUserLogin} 954 ) 955 { 956 $Param{CustomerUserLogin} = '<DEFAULT>'; 957 } 958 959 # check more needed stuff 960 if ( !$Param{ServiceID} && !$Param{CustomerUserLogin} ) { 961 $Kernel::OM->Get('Kernel::System::Log')->Log( 962 Priority => 'error', 963 Message => 'Need ServiceID or CustomerUserLogin!', 964 ); 965 return; 966 } 967 968 # create cache key 969 my $CacheKey = 'CustomerUserServiceMemberList::' . $Param{Result} . '::' 970 . 'DefaultServices::' . $Param{DefaultServices} . '::'; 971 if ( $Param{ServiceID} ) { 972 $CacheKey .= 'ServiceID::' . $Param{ServiceID}; 973 } 974 elsif ( $Param{CustomerUserLogin} ) { 975 $CacheKey .= 'CustomerUserLogin::' . $Param{CustomerUserLogin}; 976 } 977 978 # check cache 979 my $Cache = $Kernel::OM->Get('Kernel::System::Cache')->Get( 980 Type => $Self->{CacheType}, 981 Key => $CacheKey, 982 ); 983 if ( $Param{Result} eq 'HASH' ) { 984 return %{$Cache} if ref $Cache eq 'HASH'; 985 } 986 else { 987 return @{$Cache} if ref $Cache eq 'ARRAY'; 988 } 989 990 # get database object 991 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 992 993 # db quote 994 for ( sort keys %Param ) { 995 $Param{$_} = $DBObject->Quote( $Param{$_} ); 996 } 997 for (qw(ServiceID)) { 998 $Param{$_} = $DBObject->Quote( $Param{$_}, 'Integer' ); 999 } 1000 1001 # sql 1002 my %Data; 1003 my @Data; 1004 my $SQL = 'SELECT scu.service_id, scu.customer_user_login, s.name ' 1005 . ' FROM ' 1006 . ' service_customer_user scu, service s' 1007 . ' WHERE ' 1008 . " s.valid_id IN ( ${\(join ', ', $Kernel::OM->Get('Kernel::System::Valid')->ValidIDsGet())} ) AND " 1009 . ' s.id = scu.service_id AND '; 1010 1011 if ( $Param{ServiceID} ) { 1012 $SQL .= " scu.service_id = $Param{ServiceID}"; 1013 } 1014 elsif ( $Param{CustomerUserLogin} ) { 1015 $SQL .= " scu.customer_user_login = '$Param{CustomerUserLogin}'"; 1016 } 1017 1018 $DBObject->Prepare( SQL => $SQL ); 1019 1020 while ( my @Row = $DBObject->FetchrowArray() ) { 1021 1022 my $Value = ''; 1023 if ( $Param{ServiceID} ) { 1024 $Data{ $Row[1] } = $Row[0]; 1025 $Value = $Row[0]; 1026 } 1027 else { 1028 $Data{ $Row[0] } = $Row[2]; 1029 } 1030 } 1031 if ( 1032 $Param{CustomerUserLogin} 1033 && $Param{CustomerUserLogin} ne '<DEFAULT>' 1034 && $Param{DefaultServices} 1035 && !keys(%Data) 1036 ) 1037 { 1038 %Data = $Self->CustomerUserServiceMemberList( 1039 CustomerUserLogin => '<DEFAULT>', 1040 Result => 'HASH', 1041 DefaultServices => 0, 1042 ); 1043 } 1044 1045 # return result 1046 if ( $Param{Result} eq 'HASH' ) { 1047 $Kernel::OM->Get('Kernel::System::Cache')->Set( 1048 Type => $Self->{CacheType}, 1049 TTL => $Self->{CacheTTL}, 1050 Key => $CacheKey, 1051 Value => \%Data, 1052 ); 1053 return %Data; 1054 } 1055 if ( $Param{Result} eq 'Name' ) { 1056 @Data = values %Data; 1057 } 1058 else { 1059 @Data = keys %Data; 1060 } 1061 $Kernel::OM->Get('Kernel::System::Cache')->Set( 1062 Type => $Self->{CacheType}, 1063 TTL => $Self->{CacheTTL}, 1064 Key => $CacheKey, 1065 Value => \@Data, 1066 ); 1067 return @Data; 1068} 1069 1070=head2 CustomerUserServiceMemberAdd() 1071 1072to add a member to a service 1073 1074if 'Active' is 0, the customer is removed from the service 1075 1076 $ServiceObject->CustomerUserServiceMemberAdd( 1077 CustomerUserLogin => 'Test1', 1078 ServiceID => 6, 1079 Active => 1, 1080 UserID => 123, 1081 ); 1082 1083=cut 1084 1085sub CustomerUserServiceMemberAdd { 1086 my ( $Self, %Param ) = @_; 1087 1088 # check needed stuff 1089 for my $Argument (qw(CustomerUserLogin ServiceID UserID)) { 1090 if ( !$Param{$Argument} ) { 1091 $Kernel::OM->Get('Kernel::System::Log')->Log( 1092 Priority => 'error', 1093 Message => "Need $Argument!", 1094 ); 1095 return; 1096 } 1097 } 1098 1099 # get database object 1100 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 1101 1102 # delete existing relation 1103 return if !$DBObject->Do( 1104 SQL => 'DELETE FROM service_customer_user WHERE customer_user_login = ? AND service_id = ?', 1105 Bind => [ \$Param{CustomerUserLogin}, \$Param{ServiceID} ], 1106 ); 1107 1108 # return if relation is not active 1109 if ( !$Param{Active} ) { 1110 $Kernel::OM->Get('Kernel::System::Cache')->CleanUp( 1111 Type => $Self->{CacheType}, 1112 ); 1113 return; 1114 } 1115 1116 # insert new relation 1117 my $Success = $DBObject->Do( 1118 SQL => 'INSERT INTO service_customer_user ' 1119 . '(customer_user_login, service_id, create_time, create_by) ' 1120 . 'VALUES (?, ?, current_timestamp, ?)', 1121 Bind => [ \$Param{CustomerUserLogin}, \$Param{ServiceID}, \$Param{UserID} ] 1122 ); 1123 1124 $Kernel::OM->Get('Kernel::System::Cache')->CleanUp( 1125 Type => $Self->{CacheType}, 1126 ); 1127 1128 return $Success; 1129} 1130 1131=head2 ServicePreferencesSet() 1132 1133set service preferences 1134 1135 $ServiceObject->ServicePreferencesSet( 1136 ServiceID => 123, 1137 Key => 'UserComment', 1138 Value => 'some comment', 1139 UserID => 123, 1140 ); 1141 1142=cut 1143 1144sub ServicePreferencesSet { 1145 my ( $Self, %Param ) = @_; 1146 1147 $Self->{PreferencesObject}->ServicePreferencesSet(%Param); 1148 1149 $Kernel::OM->Get('Kernel::System::Cache')->CleanUp( 1150 Type => $Self->{CacheType}, 1151 ); 1152 return 1; 1153} 1154 1155=head2 ServicePreferencesGet() 1156 1157get service preferences 1158 1159 my %Preferences = $ServiceObject->ServicePreferencesGet( 1160 ServiceID => 123, 1161 UserID => 123, 1162 ); 1163 1164=cut 1165 1166sub ServicePreferencesGet { 1167 my ( $Self, %Param ) = @_; 1168 1169 return $Self->{PreferencesObject}->ServicePreferencesGet(%Param); 1170} 1171 1172=head2 ServiceParentsGet() 1173 1174return an ordered list all parent service IDs for the given service from the root parent to the 1175current service parent 1176 1177 my $ServiceParentsList = $ServiceObject->ServiceParentsGet( 1178 ServiceID => 123, 1179 UserID => 1, 1180 ); 1181 1182 returns 1183 1184 $ServiceParentsList = [ 1, 2, ...]; 1185 1186=cut 1187 1188sub ServiceParentsGet { 1189 my ( $Self, %Param ) = @_; 1190 1191 # check needed stuff 1192 for my $Needed (qw(UserID ServiceID)) { 1193 if ( !$Param{$Needed} ) { 1194 $Kernel::OM->Get('Kernel::System::Log')->Log( 1195 Priority => 'error', 1196 Message => 'Need $Needed!', 1197 ); 1198 return; 1199 } 1200 } 1201 1202 # read cache 1203 my $CacheKey = 'ServiceParentsGet::' . $Param{ServiceID}; 1204 my $Cache = $Kernel::OM->Get('Kernel::System::Cache')->Get( 1205 Type => $Self->{CacheType}, 1206 Key => $CacheKey, 1207 ); 1208 return $Cache if ref $Cache; 1209 1210 # get the list of services 1211 my $ServiceList = $Self->ServiceListGet( 1212 Valid => 0, 1213 UserID => 1, 1214 ); 1215 1216 # get a service lookup table 1217 my %ServiceLookup; 1218 SERVICE: 1219 for my $ServiceData ( @{$ServiceList} ) { 1220 next SERVICE if !$ServiceData; 1221 next SERVICE if !IsHashRefWithData($ServiceData); 1222 next SERVICE if !$ServiceData->{ServiceID}; 1223 1224 $ServiceLookup{ $ServiceData->{ServiceID} } = $ServiceData; 1225 } 1226 1227 # exit if ServiceID is invalid 1228 return if !$ServiceLookup{ $Param{ServiceID} }; 1229 1230 # to store the return structure 1231 my @ServiceParents; 1232 1233 # get the ServiceParentID from the requested service 1234 my $ServiceParentID = $ServiceLookup{ $Param{ServiceID} }->{ParentID}; 1235 1236 # get all partents for the requested service 1237 while ($ServiceParentID) { 1238 1239 # add service parent ID to the return structure 1240 push @ServiceParents, $ServiceParentID; 1241 1242 # set next ServiceParentID (the parent of the current parent) 1243 $ServiceParentID = $ServiceLookup{$ServiceParentID}->{ParentID} || 0; 1244 1245 } 1246 1247 # reverse the return array to get the list ordered from old to young (in parent context) 1248 my @Data = reverse @ServiceParents; 1249 1250 # set cache 1251 $Kernel::OM->Get('Kernel::System::Cache')->Set( 1252 Type => $Self->{CacheType}, 1253 TTL => $Self->{CacheTTL}, 1254 Key => $CacheKey, 1255 Value => \@Data, 1256 ); 1257 1258 return \@Data; 1259} 1260 1261=head2 GetAllCustomServices() 1262 1263get all custom services of one user 1264 1265 my @Services = $ServiceObject->GetAllCustomServices( UserID => 123 ); 1266 1267=cut 1268 1269sub GetAllCustomServices { 1270 my ( $Self, %Param ) = @_; 1271 1272 # check needed stuff 1273 if ( !$Param{UserID} ) { 1274 $Kernel::OM->Get('Kernel::System::Log')->Log( 1275 Priority => 'error', 1276 Message => 'Need UserID!', 1277 ); 1278 return; 1279 } 1280 1281 # check cache 1282 my $CacheKey = 'GetAllCustomServices::' . $Param{UserID}; 1283 my $Cache = $Kernel::OM->Get('Kernel::System::Cache')->Get( 1284 Type => $Self->{CacheType}, 1285 Key => $CacheKey, 1286 ); 1287 1288 return @{$Cache} if $Cache; 1289 1290 # get database object 1291 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 1292 1293 # search all custom services 1294 return if !$DBObject->Prepare( 1295 SQL => ' 1296 SELECT service_id 1297 FROM personal_services 1298 WHERE user_id = ?', 1299 Bind => [ \$Param{UserID} ], 1300 ); 1301 1302 # fetch the result 1303 my @ServiceIDs; 1304 while ( my @Row = $DBObject->FetchrowArray() ) { 1305 push @ServiceIDs, $Row[0]; 1306 } 1307 1308 # set cache 1309 $Kernel::OM->Get('Kernel::System::Cache')->Set( 1310 Type => $Self->{CacheType}, 1311 TTL => $Self->{CacheTTL}, 1312 Key => $CacheKey, 1313 Value => \@ServiceIDs, 1314 ); 1315 1316 return @ServiceIDs; 1317} 1318 13191; 1320 1321=head1 TERMS AND CONDITIONS 1322 1323This software is part of the OTRS project (L<https://otrs.org/>). 1324 1325This software comes with ABSOLUTELY NO WARRANTY. For details, see 1326the enclosed file COPYING for license information (GPL). If you 1327did not receive this file, see L<https://www.gnu.org/licenses/gpl-3.0.txt>. 1328 1329=cut 1330