-- OTRS Reporting supplementary code
-- Copyright 2014, Dominik George <d.george@tarent.de>
--
-- Licensed under the terms and conditions of the MIT license.
--
-- This function calls the OTRS SOAP API through HTTPS to retrieve the
-- netto time between two timestamps in relation to a calendar
-- configured for queue working times.
--
-- Results are cached in otrs_workingtime_cache and retrieved from there
-- to save API calls on known values (very important for large
-- datasets). Run something like this to clear the cache if a calendar
-- changed:
--
--    DELETE FROM otrs_workingtime_cache WHERE calendar='4';

CREATE OR REPLACE FUNCTION otrs_workingtime (timestamp, timestamp, character varying) RETURNS integer AS $$
  my $res = spi_exec_query("SELECT working_time FROM otrs_workingtime_cache WHERE starttime='$_[0]' AND stoptime='$_[1]' AND calendar='$_[2]'", 1);

  if ($res->{processed} > 0) {
    return $res->{rows}[0]->{working_time};
  } else {
    use Date::Parse;

    my $starttime = str2time($_[0]);
    my $stoptime  = str2time($_[1]);
    my $calendar  = $_[2];

    use SOAP::Lite;
    my $Client = SOAP::Lite->new(
      proxy => 'https://example.com/otrs/rpc.pl',
      ns => '/Core'
    );
    my $User     = 'soap';
    my $Password = '';

    my $working_time = $Client->Dispatch($User, $Password,
      'TimeObject', 'WorkingTime',
      'StartTime', $starttime,
      'StopTime', $stoptime,
      'Calendar', $calendar
    )->result;

    spi_exec_query("INSERT INTO otrs_workingtime_cache VALUES ('$_[0]', '$_[1]', '$_[2]', $working_time)");

    return $working_time;
  }
$$ LANGUAGE plperlu VOLATILE;
