-- OTRS Reporting supplementary code
-- Copyright 2014, Dominik George <d.george@tarent.de>
--
-- Licensed under the terms and conditions of the MIT license.
--
-- View providing base data for all queues

CREATE OR REPLACE VIEW reporting_dyn_tickets AS
  SELECT
    ticket.id                                            AS ticketid,
    EXTRACT(YEAR FROM ticket.create_time)                AS jahr,
    TO_CHAR(ticket.create_time, 'MM_Mon')                AS monat_lang,
    CASE
      WHEN ticket_state.name IN ('closed successful', 'closed unsuccessful') THEN
        'closed'
      ELSE
        'open'
    END                                                  AS state,
    EXTRACT(EPOCH FROM (MIN(ticket_history.create_time) - ticket.create_time)) AS responsegross,
    CASE
      WHEN ticket_state.name IN ('closed successful', 'closed unsuccessful') THEN
        EXTRACT(EPOCH FROM (MAX(ticket_history.create_time) - ticket.create_time))
      ELSE
        NULL
    END  	                                         AS solutiongross,
    CASE
      WHEN ticket_state.name IN ('closed successful', 'closed unsuccessful') THEN
        EXTRACT(EPOCH FROM (MAX(ticket_history.create_time) - ticket.create_time))
      ELSE
        EXTRACT(EPOCH FROM (NOW() - ticket.create_time))
    END                                                  AS durationgross,
    otrs_workingtime(CAST(ticket.create_time AS timestamp without time zone), CAST(MIN(ticket_history.create_time) AS timestamp without time zone), queue.calendar_name) AS responsenet,
    CASE
      WHEN ticket_state.name IN ('closed successful', 'closed unsuccessful') THEN
        otrs_workingtime(CAST(ticket.create_time AS timestamp without time zone), CAST(MAX(ticket_history.create_time) AS timestamp without time zone), queue.calendar_name)
      ELSE
        NULL
    END                                                  AS solutionnet,
    CASE
      WHEN ticket_state.name IN ('closed successful', 'closed unsuccessful') THEN
        otrs_workingtime(CAST(ticket.create_time AS timestamp without time zone), CAST(MAX(ticket_history.create_time) AS timestamp without time zone), queue.calendar_name)
      ELSE
        otrs_workingtime(CAST(ticket.create_time AS timestamp without time zone), CAST(NOW() AS timestamp without time zone), queue.calendar_name)
    END                                                  AS durationnet

  FROM
    ticket,
    ticket_state,
    ticket_history,
    ticket_history_type,
    queue

  WHERE
        ticket.queue_id                =      queue.id
    AND ticket.ticket_state_id         =      ticket_state.id
    AND ticket_history.ticket_id       =      ticket.id
    AND ticket_history.history_type_id =      ticket_history_type.id
    AND ticket_history_type.name       IN     ('StateUpdate')
    AND ticket_state.name              NOT IN ('merged')
    AND ticket.queue_id                IN     (59, 81)

  GROUP BY
    ticket.id,
    ticket_state.name,
    ticket.create_time,
    queue.calendar_name
;
