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::SLA; 10 11use strict; 12use warnings; 13 14our @ObjectDependencies = ( 15 'Kernel::Config', 16 'Kernel::System::Cache', 17 'Kernel::System::CheckItem', 18 'Kernel::System::DB', 19 'Kernel::System::Log', 20 'Kernel::System::Valid', 21); 22 23=head1 NAME 24 25Kernel::System::SLA - sla lib 26 27=head1 DESCRIPTION 28 29All sla functions. 30 31=head1 PUBLIC INTERFACE 32 33=head2 new() 34 35Don't use the constructor directly, use the ObjectManager instead: 36 37 my $SLAObject = $Kernel::OM->Get('Kernel::System::SLA'); 38 39=cut 40 41sub new { 42 my ( $Type, %Param ) = @_; 43 44 # allocate new hash for object 45 my $Self = {}; 46 bless( $Self, $Type ); 47 48 # get configured preferences object 49 my $GeneratorModule = $Kernel::OM->Get('Kernel::Config')->Get('SLA::PreferencesModule') 50 || 'Kernel::System::SLA::PreferencesDB'; 51 52 # get preferences object 53 $Self->{PreferencesObject} = $Kernel::OM->Get($GeneratorModule); 54 55 $Self->{CacheType} = 'SLA'; 56 $Self->{CacheTTL} = 60 * 60 * 24 * 20; 57 58 return $Self; 59} 60 61=head2 SLAList() 62 63return a hash list of slas 64 65 my %SLAList = $SLAObject->SLAList( 66 ServiceID => 1, # (optional) 67 Valid => 0, # (optional) default 1 (0|1) 68 UserID => 1, 69 ); 70 71=cut 72 73sub SLAList { 74 my ( $Self, %Param ) = @_; 75 76 # check needed stuff 77 if ( !$Param{UserID} ) { 78 $Kernel::OM->Get('Kernel::System::Log')->Log( 79 Priority => 'error', 80 Message => 'Need UserID!' 81 ); 82 return; 83 } 84 85 # set valid param 86 if ( !defined $Param{Valid} ) { 87 $Param{Valid} = 1; 88 } 89 90 # get database object 91 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 92 93 # add ServiceID 94 my %SQLTable; 95 $SQLTable{sla} = 'sla s'; 96 my @SQLWhere; 97 if ( $Param{ServiceID} ) { 98 99 # quote 100 $Param{ServiceID} = $DBObject->Quote( $Param{ServiceID}, 'Integer' ); 101 102 $SQLTable{service} = 'service_sla r'; 103 push @SQLWhere, "s.id = r.sla_id AND r.service_id = $Param{ServiceID}"; 104 } 105 106 # add valid part 107 if ( $Param{Valid} ) { 108 109 # get valid object 110 my $ValidObject = $Kernel::OM->Get('Kernel::System::Valid'); 111 112 # create the valid list 113 my $ValidIDs = join ', ', $ValidObject->ValidIDsGet(); 114 115 push @SQLWhere, "s.valid_id IN ( $ValidIDs )"; 116 } 117 118 # create the table and where strings 119 my $TableString = join q{, }, values %SQLTable; 120 my $WhereString = @SQLWhere ? ' WHERE ' . join q{ AND }, @SQLWhere : ''; 121 122 # ask database 123 $DBObject->Prepare( 124 SQL => "SELECT s.id, s.name FROM $TableString $WhereString", 125 ); 126 127 # fetch the result 128 my %SLAList; 129 while ( my @Row = $DBObject->FetchrowArray() ) { 130 $SLAList{ $Row[0] } = $Row[1]; 131 } 132 133 return %SLAList; 134} 135 136=head2 SLAGet() 137 138Returns an SLA as a hash 139 140 my %SLAData = $SLAObject->SLAGet( 141 SLAID => 123, 142 UserID => 1, 143 ); 144 145Returns: 146 147 my %SLAData = ( 148 'SLAID' => '2', 149 'Name' => 'Diamond Pacific - S2', 150 'Calendar' => '2', 151 'FirstResponseTime' => '60', # in minutes according to business hours 152 'FirstResponseNotify' => '70', # in percent 153 'UpdateTime' => '360', # in minutes according to business hours 154 'UpdateNotify' => '70', # in percent 155 'SolutionTime' => '960', # in minutes according to business hours 156 'SolutionNotify' => '80', # in percent 157 'ServiceIDs' => [ '4', '7', '8' ], 158 'ValidID' => '1', 159 'Comment' => 'Some Comment', 160 'CreateBy' => '93', 161 'CreateTime' => '2011-06-16 22:54:54', 162 'ChangeBy' => '93', 163 'ChangeTime' => '2011-06-16 22:54:54', 164 ); 165 166=cut 167 168sub SLAGet { 169 my ( $Self, %Param ) = @_; 170 171 # check needed stuff 172 for my $Argument (qw(SLAID UserID)) { 173 if ( !$Param{$Argument} ) { 174 $Kernel::OM->Get('Kernel::System::Log')->Log( 175 Priority => 'error', 176 Message => "Need $Argument!", 177 ); 178 return; 179 } 180 } 181 182 # check if result is already cached 183 my $CacheKey = 'Cache::SLAGet::' . $Param{SLAID}; 184 my $Cached = $Kernel::OM->Get('Kernel::System::Cache')->Get( 185 Type => $Self->{CacheType}, 186 Key => $CacheKey, 187 CacheInMemory => 1, 188 CacheInBackend => 0, 189 ); 190 191 if ( ref $Cached eq 'HASH' ) { 192 return %{$Cached}; 193 } 194 195 # get database object 196 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 197 198 # get sla from db 199 $DBObject->Prepare( 200 SQL => 'SELECT id, name, calendar_name, first_response_time, first_response_notify, ' 201 . 'update_time, update_notify, solution_time, solution_notify, ' 202 . 'valid_id, comments, create_time, create_by, change_time, change_by ' 203 . 'FROM sla WHERE id = ?', 204 Bind => [ 205 \$Param{SLAID}, 206 ], 207 Limit => 1, 208 ); 209 210 # fetch the result 211 my %SLAData; 212 while ( my @Row = $DBObject->FetchrowArray() ) { 213 $SLAData{SLAID} = $Row[0]; 214 $SLAData{Name} = $Row[1]; 215 $SLAData{Calendar} = $Row[2] || ''; 216 $SLAData{FirstResponseTime} = $Row[3]; 217 $SLAData{FirstResponseNotify} = $Row[4]; 218 $SLAData{UpdateTime} = $Row[5]; 219 $SLAData{UpdateNotify} = $Row[6]; 220 $SLAData{SolutionTime} = $Row[7]; 221 $SLAData{SolutionNotify} = $Row[8]; 222 $SLAData{ValidID} = $Row[9]; 223 $SLAData{Comment} = $Row[10] || ''; 224 $SLAData{CreateTime} = $Row[11]; 225 $SLAData{CreateBy} = $Row[12]; 226 $SLAData{ChangeTime} = $Row[13]; 227 $SLAData{ChangeBy} = $Row[14]; 228 } 229 230 # check sla 231 if ( !$SLAData{SLAID} ) { 232 $Kernel::OM->Get('Kernel::System::Log')->Log( 233 Priority => 'error', 234 Message => "No such SLAID ($Param{SLAID})!", 235 ); 236 return; 237 } 238 239 # get all service ids 240 $DBObject->Prepare( 241 SQL => 'SELECT service_id FROM service_sla WHERE sla_id = ? ORDER BY service_id ASC', 242 Bind => [ \$SLAData{SLAID} ], 243 ); 244 245 # fetch the result 246 my @ServiceIDs; 247 while ( my @Row = $DBObject->FetchrowArray() ) { 248 push @ServiceIDs, $Row[0]; 249 } 250 251 # add the ids 252 $SLAData{ServiceIDs} = \@ServiceIDs; 253 254 # get sla preferences 255 my %Preferences = $Self->SLAPreferencesGet( SLAID => $Param{SLAID} ); 256 257 # merge hash 258 if (%Preferences) { 259 %SLAData = ( %SLAData, %Preferences ); 260 } 261 262 # cache result 263 $Kernel::OM->Get('Kernel::System::Cache')->Set( 264 Type => $Self->{CacheType}, 265 TTL => $Self->{CacheTTL}, 266 Key => $CacheKey, 267 268 # make a local copy of the sla data to avoid it being altered in-memory later 269 Value => {%SLAData}, 270 CacheInMemory => 1, 271 CacheInBackend => 0, 272 ); 273 274 return %SLAData; 275} 276 277=head2 SLALookup() 278 279returns the name or the sla id 280 281 my $SLAName = $SLAObject->SLALookup( 282 SLAID => 123, 283 ); 284 285 or 286 287 my $SLAID = $SLAObject->SLALookup( 288 Name => 'SLA Name', 289 ); 290 291=cut 292 293sub SLALookup { 294 my ( $Self, %Param ) = @_; 295 296 # check needed stuff 297 if ( !$Param{SLAID} && !$Param{Name} ) { 298 $Kernel::OM->Get('Kernel::System::Log')->Log( 299 Priority => 'error', 300 Message => 'Need SLAID or Name!', 301 ); 302 return; 303 } 304 305 # get database object 306 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 307 308 if ( $Param{SLAID} ) { 309 310 # check cache 311 my $CacheKey = 'Cache::SLALookup::ID::' . $Param{SLAID}; 312 my $Cached = $Kernel::OM->Get('Kernel::System::Cache')->Get( 313 Type => $Self->{CacheType}, 314 Key => $CacheKey, 315 CacheInMemory => 1, 316 CacheInBackend => 0, 317 ); 318 if ( defined $Cached ) { 319 return $Cached; 320 } 321 322 # lookup 323 $DBObject->Prepare( 324 SQL => 'SELECT name FROM sla WHERE id = ?', 325 Bind => [ \$Param{SLAID}, ], 326 Limit => 1, 327 ); 328 329 # fetch the result 330 my $Name = ''; 331 while ( my @Row = $DBObject->FetchrowArray() ) { 332 $Name = $Row[0]; 333 } 334 335 # cache 336 $Kernel::OM->Get('Kernel::System::Cache')->Set( 337 Type => $Self->{CacheType}, 338 TTL => $Self->{CacheTTL}, 339 Key => $CacheKey, 340 Value => $Name, 341 CacheInMemory => 1, 342 CacheInBackend => 0, 343 ); 344 345 return $Name; 346 } 347 else { 348 349 # check cache 350 my $CacheKey = 'Cache::SLALookup::Name::' . $Param{Name}; 351 my $Cached = $Kernel::OM->Get('Kernel::System::Cache')->Get( 352 Type => $Self->{CacheType}, 353 Key => $CacheKey, 354 CacheInMemory => 1, 355 CacheInBackend => 0, 356 ); 357 if ( defined $Cached ) { 358 return $Cached; 359 } 360 361 # lookup 362 $DBObject->Prepare( 363 SQL => 'SELECT id FROM sla WHERE name = ?', 364 Bind => [ \$Param{Name} ], 365 Limit => 1, 366 ); 367 368 # fetch the result 369 my $SLAID = ''; 370 while ( my @Row = $DBObject->FetchrowArray() ) { 371 $SLAID = $Row[0]; 372 } 373 374 # cache 375 $Kernel::OM->Get('Kernel::System::Cache')->Set( 376 Type => $Self->{CacheType}, 377 TTL => $Self->{CacheTTL}, 378 Key => $CacheKey, 379 Value => $SLAID, 380 CacheInMemory => 1, 381 CacheInBackend => 0, 382 ); 383 384 return $SLAID; 385 } 386} 387 388=head2 SLAAdd() 389 390add a sla 391 392 my $SLAID = $SLAObject->SLAAdd( 393 ServiceIDs => [ 1, 5, 7 ], # (optional) 394 Name => 'SLA Name', 395 Calendar => 'Calendar1', # (optional) 396 FirstResponseTime => 120, # (optional) 397 FirstResponseNotify => 60, # (optional) notify agent if first response escalation is 60% reached 398 UpdateTime => 180, # (optional) 399 UpdateNotify => 80, # (optional) notify agent if update escalation is 80% reached 400 SolutionTime => 580, # (optional) 401 SolutionNotify => 80, # (optional) notify agent if solution escalation is 80% reached 402 ValidID => 1, 403 Comment => 'Comment', # (optional) 404 UserID => 1, 405 ); 406 407=cut 408 409sub SLAAdd { 410 my ( $Self, %Param ) = @_; 411 412 # check needed stuff 413 for my $Argument (qw(Name ValidID UserID)) { 414 if ( !$Param{$Argument} ) { 415 $Kernel::OM->Get('Kernel::System::Log')->Log( 416 Priority => 'error', 417 Message => "Need $Argument!", 418 ); 419 return; 420 } 421 } 422 423 # check service ids 424 if ( defined $Param{ServiceIDs} && ref $Param{ServiceIDs} ne 'ARRAY' ) { 425 $Kernel::OM->Get('Kernel::System::Log')->Log( 426 Priority => 'error', 427 Message => 'ServiceIDs needs to be an array reference!', 428 ); 429 return; 430 } 431 432 # set default values 433 $Param{ServiceIDs} ||= []; 434 $Param{Calendar} ||= ''; 435 $Param{Comment} ||= ''; 436 $Param{FirstResponseTime} ||= 0; 437 $Param{FirstResponseNotify} ||= 0; 438 $Param{UpdateTime} ||= 0; 439 $Param{UpdateNotify} ||= 0; 440 $Param{SolutionTime} ||= 0; 441 $Param{SolutionNotify} ||= 0; 442 443 # get check item object 444 my $CheckItemObject = $Kernel::OM->Get('Kernel::System::CheckItem'); 445 446 # cleanup given params 447 for my $Argument (qw(Name Comment)) { 448 $CheckItemObject->StringClean( 449 StringRef => \$Param{$Argument}, 450 RemoveAllNewlines => 1, 451 RemoveAllTabs => 1, 452 ); 453 } 454 455 # get database object 456 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 457 458 # find exiting sla's with the same name 459 $DBObject->Prepare( 460 SQL => 'SELECT id FROM sla WHERE name = ?', 461 Bind => [ \$Param{Name} ], 462 Limit => 1, 463 ); 464 465 # fetch the result 466 my $NoAdd; 467 while ( $DBObject->FetchrowArray() ) { 468 $NoAdd = 1; 469 } 470 471 # abort insert of new sla, if name already exists 472 if ($NoAdd) { 473 $Kernel::OM->Get('Kernel::System::Log')->Log( 474 Priority => 'error', 475 Message => "An SLA with the name '$Param{Name}' already exists.", 476 ); 477 return; 478 } 479 480 # add sla to database 481 return if !$DBObject->Do( 482 SQL => 'INSERT INTO sla ' 483 . '(name, calendar_name, first_response_time, first_response_notify, ' 484 . 'update_time, update_notify, solution_time, solution_notify, ' 485 . 'valid_id, comments, create_time, create_by, change_time, change_by) VALUES ' 486 . '(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, current_timestamp, ?, current_timestamp, ?)', 487 Bind => [ 488 \$Param{Name}, \$Param{Calendar}, \$Param{FirstResponseTime}, 489 \$Param{FirstResponseNotify}, \$Param{UpdateTime}, \$Param{UpdateNotify}, 490 \$Param{SolutionTime}, \$Param{SolutionNotify}, \$Param{ValidID}, \$Param{Comment}, 491 \$Param{UserID}, \$Param{UserID}, 492 ], 493 ); 494 495 # get sla id 496 return if !$DBObject->Prepare( 497 SQL => 'SELECT id FROM sla WHERE name = ?', 498 Bind => [ \$Param{Name} ], 499 Limit => 1, 500 ); 501 502 # fetch the result 503 my $SLAID; 504 while ( my @Row = $DBObject->FetchrowArray() ) { 505 $SLAID = $Row[0]; 506 } 507 508 # check sla id 509 if ( !$SLAID ) { 510 $Kernel::OM->Get('Kernel::System::Log')->Log( 511 Priority => 'error', 512 Message => "Can't find SLAID for '$Param{Name}'!", 513 ); 514 return; 515 } 516 517 # remove all existing allocations 518 $DBObject->Do( 519 SQL => 'DELETE FROM service_sla WHERE sla_id = ?', 520 Bind => [ \$SLAID ], 521 ); 522 523 # add the new allocations 524 for my $ServiceID ( @{ $Param{ServiceIDs} } ) { 525 526 # add one allocation 527 $DBObject->Do( 528 SQL => 'INSERT INTO service_sla (service_id, sla_id) VALUES (?, ?)', 529 Bind => [ \$ServiceID, \$SLAID ], 530 ); 531 } 532 533 return $SLAID; 534} 535 536=head2 SLAUpdate() 537 538update a existing sla 539 540 my $True = $SLAObject->SLAUpdate( 541 SLAID => 2, 542 ServiceIDs => [ 1, 2, 3 ], # (optional) 543 Name => 'Service Name', 544 Calendar => 'Calendar1', # (optional) 545 FirstResponseTime => 120, # (optional) 546 FirstResponseNotify => 60, # (optional) notify agent if first response escalation is 60% reached 547 UpdateTime => 180, # (optional) 548 UpdateNotify => 80, # (optional) notify agent if update escalation is 80% reached 549 SolutionTime => 580, # (optional) 550 SolutionNotify => 80, # (optional) notify agent if solution escalation is 80% reached 551 ValidID => 1, 552 Comment => 'Comment', # (optional) 553 UserID => 1, 554 ); 555 556=cut 557 558sub SLAUpdate { 559 my ( $Self, %Param ) = @_; 560 561 # check needed stuff 562 for my $Argument (qw(SLAID Name ValidID UserID)) { 563 if ( !$Param{$Argument} ) { 564 $Kernel::OM->Get('Kernel::System::Log')->Log( 565 Priority => 'error', 566 Message => "Need $Argument!", 567 ); 568 return; 569 } 570 } 571 572 # check service ids 573 if ( defined $Param{ServiceIDs} && ref $Param{ServiceIDs} ne 'ARRAY' ) { 574 $Kernel::OM->Get('Kernel::System::Log')->Log( 575 Priority => 'error', 576 Message => 'ServiceIDs need to be an array reference!', 577 ); 578 return; 579 } 580 581 # set default values 582 $Param{ServiceIDs} ||= []; 583 $Param{Calendar} ||= ''; 584 $Param{Comment} ||= ''; 585 $Param{FirstResponseTime} ||= 0; 586 $Param{FirstResponseNotify} ||= 0; 587 $Param{UpdateTime} ||= 0; 588 $Param{UpdateNotify} ||= 0; 589 $Param{SolutionTime} ||= 0; 590 $Param{SolutionNotify} ||= 0; 591 592 # get check item object 593 my $CheckItemObject = $Kernel::OM->Get('Kernel::System::CheckItem'); 594 595 # cleanup given params 596 for my $Argument (qw(Name Comment)) { 597 $CheckItemObject->StringClean( 598 StringRef => \$Param{$Argument}, 599 RemoveAllNewlines => 1, 600 RemoveAllTabs => 1, 601 ); 602 } 603 604 # get database object 605 my $DBObject = $Kernel::OM->Get('Kernel::System::DB'); 606 607 # find exiting sla's with the same name 608 return if !$DBObject->Prepare( 609 SQL => 'SELECT id FROM sla WHERE name = ?', 610 Bind => [ \$Param{Name} ], 611 Limit => 1, 612 ); 613 614 # fetch the result 615 my $Update = 0; 616 while ( my @Row = $DBObject->FetchrowArray() ) { 617 if ( $Row[0] != $Param{SLAID} ) { 618 $Update = $Row[0]; 619 } 620 } 621 622 # abort update of sla, if name already exists 623 if ($Update) { 624 $Kernel::OM->Get('Kernel::System::Log')->Log( 625 Priority => 'error', 626 Message => "An SLA with the name '$Param{Name}' already exists.", 627 ); 628 return; 629 } 630 631 # reset cache 632 $Kernel::OM->Get('Kernel::System::Cache')->Delete( 633 Type => $Self->{CacheType}, 634 Key => 'Cache::SLAGet::' . $Param{SLAID}, 635 ); 636 $Kernel::OM->Get('Kernel::System::Cache')->Delete( 637 Type => $Self->{CacheType}, 638 Key => 'Cache::SLALookup::Name::' . $Param{Name}, 639 ); 640 $Kernel::OM->Get('Kernel::System::Cache')->Delete( 641 Type => $Self->{CacheType}, 642 Key => 'Cache::SLALookup::ID::' . $Param{SLAID}, 643 ); 644 645 # update service 646 return if !$DBObject->Do( 647 SQL => 'UPDATE sla SET name = ?, calendar_name = ?, ' 648 . 'first_response_time = ?, first_response_notify = ?, ' 649 . 'update_time = ?, update_notify = ?, solution_time = ?, solution_notify = ?, ' 650 . 'valid_id = ?, comments = ?, change_time = current_timestamp, change_by = ? ' 651 . 'WHERE id = ?', 652 Bind => [ 653 \$Param{Name}, \$Param{Calendar}, \$Param{FirstResponseTime}, 654 \$Param{FirstResponseNotify}, \$Param{UpdateTime}, \$Param{UpdateNotify}, 655 \$Param{SolutionTime}, \$Param{SolutionNotify}, \$Param{ValidID}, \$Param{Comment}, 656 \$Param{UserID}, \$Param{SLAID}, 657 ], 658 ); 659 660 # remove all existing allocations 661 return if !$DBObject->Do( 662 SQL => 'DELETE FROM service_sla WHERE sla_id = ?', 663 Bind => [ \$Param{SLAID}, ] 664 ); 665 666 # add the new allocations 667 for my $ServiceID ( @{ $Param{ServiceIDs} } ) { 668 669 # add one allocation 670 return if !$DBObject->Do( 671 SQL => 'INSERT INTO service_sla (service_id, sla_id) VALUES (?, ?)', 672 Bind => [ \$ServiceID, \$Param{SLAID} ], 673 ); 674 } 675 676 return 1; 677} 678 679=head2 SLAPreferencesSet() 680 681set SLA preferences 682 683 $SLAObject->SLAPreferencesSet( 684 SLAID => 123, 685 Key => 'UserComment', 686 Value => 'some comment', 687 UserID => 123, 688 ); 689 690=cut 691 692sub SLAPreferencesSet { 693 my ( $Self, %Param ) = @_; 694 695 return $Self->{PreferencesObject}->SLAPreferencesSet(%Param); 696} 697 698=head2 SLAPreferencesGet() 699 700get SLA preferences 701 702 my %Preferences = $SLAObject->SLAPreferencesGet( 703 SLAID => 123, 704 UserID => 123, 705 ); 706 707=cut 708 709sub SLAPreferencesGet { 710 my ( $Self, %Param ) = @_; 711 712 return $Self->{PreferencesObject}->SLAPreferencesGet(%Param); 713} 714 7151; 716 717=head1 TERMS AND CONDITIONS 718 719This software is part of the OTRS project (L<https://otrs.org/>). 720 721This software comes with ABSOLUTELY NO WARRANTY. For details, see 722the enclosed file COPYING for license information (GPL). If you 723did not receive this file, see L<https://www.gnu.org/licenses/gpl-3.0.txt>. 724 725=cut 726