123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345 |
- # Copyright (C) 2012-2022 Zammad Foundation, https://zammad-foundation.org/
- class Report::Base
- # :object
- # :type created|updated
- # :attribute
- # :value_from
- # :value_to
- # :start
- # :end
- # :selector
- def self.history_count(params)
- history_object = History::Object.lookup(name: params[:object])
- query, bind_params, tables = Ticket.selector2sql(params[:selector])
- # created
- if params[:type] == 'created'
- history_type = History::Type.lookup(name: 'created')
- return History.select('histories.o_id').joins('INNER JOIN tickets ON tickets.id = histories.o_id')
- .where(
- 'histories.created_at >= ? AND histories.created_at <= ? AND histories.history_object_id = ? AND histories.history_type_id = ?', params[:start], params[:end], history_object.id, history_type.id
- )
- .where(query, *bind_params).joins(tables).count
- end
- # updated
- if params[:type] == 'updated'
- history_type = History::Type.lookup(name: 'updated')
- history_attribute = History::Attribute.lookup(name: params[:attribute])
- result = nil
- if !history_attribute || !history_type
- result = 0
- elsif params[:id_not_from] && params[:id_to]
- result = History.select('histories.o_id').joins('INNER JOIN tickets ON tickets.id = histories.o_id')
- .where(query, *bind_params).joins(tables)
- .where(
- 'histories.created_at >= ? AND histories.created_at <= ? AND histories.history_object_id = ? AND histories.history_type_id = ? AND histories.history_attribute_id IN (?) AND histories.id_from NOT IN (?) AND histories.id_to IN (?)',
- params[:start],
- params[:end],
- history_object.id,
- history_type.id,
- history_attribute.id,
- params[:id_not_from],
- params[:id_to],
- ).count
- elsif params[:id_from] && params[:id_not_to]
- result = History.select('histories.o_id').joins('INNER JOIN tickets ON tickets.id = histories.o_id')
- .where(query, *bind_params).joins(tables)
- .where(
- 'histories.created_at >= ? AND histories.created_at <= ? AND histories.history_object_id = ? AND histories.history_type_id = ? AND histories.history_attribute_id IN (?) AND histories.id_from IN (?) AND histories.id_to NOT IN (?)',
- params[:start],
- params[:end],
- history_object.id,
- history_type.id,
- history_attribute.id,
- params[:id_from],
- params[:id_not_to],
- ).count
- elsif params[:value_from] && params[:value_not_to]
- result = History.joins('INNER JOIN tickets ON tickets.id = histories.o_id')
- .where(query, *bind_params).joins(tables)
- .where(
- 'histories.created_at >= ? AND histories.created_at <= ? AND histories.history_object_id = ? AND histories.history_type_id = ? AND histories.history_attribute_id IN (?) AND histories.value_from IN (?) AND histories.value_to NOT IN (?)',
- params[:start],
- params[:end],
- history_object.id,
- history_type.id,
- history_attribute.id,
- params[:value_from],
- params[:value_not_to],
- ).count
- elsif params[:value_to]
- result = History.select('histories.o_id').joins('INNER JOIN tickets ON tickets.id = histories.o_id')
- .where(query, *bind_params).joins(tables)
- .where(
- 'histories.created_at >= ? AND histories.created_at <= ? AND histories.history_object_id = ? AND histories.history_type_id = ? AND histories.history_attribute_id IN (?) AND histories.value_to IN (?)',
- params[:start],
- params[:end],
- history_object.id,
- history_type.id,
- history_attribute.id,
- params[:value_to],
- ).count
- elsif params[:id_to]
- result = History.select('histories.o_id').joins('INNER JOIN tickets ON tickets.id = histories.o_id')
- .where(query, *bind_params).joins(tables)
- .where(
- 'histories.created_at >= ? AND histories.created_at <= ? AND histories.history_object_id = ? AND histories.history_type_id = ? AND histories.history_attribute_id IN (?) AND histories.id_to IN (?)',
- params[:start],
- params[:end],
- history_object.id,
- history_type.id,
- history_attribute.id,
- params[:id_to],
- ).count
- end
- return result if !result.nil?
- raise "UNKOWN params (#{params.inspect})!"
- end
- raise "UNKOWN :type (#{params[:type]})!"
- end
- # :object
- # :type created|updated
- # :attribute
- # :value_from
- # :value_to
- # :start
- # :end
- # :condition
- def self.history(data)
- history_object = History::Object.lookup(name: data[:object])
- query, bind_params, tables = Ticket.selector2sql(data[:selector])
- count = 0
- ticket_ids = []
- # created
- if data[:type] == 'created'
- history_type = History::Type.lookup(name: 'created')
- histories = History.select('histories.o_id').joins('INNER JOIN tickets ON tickets.id = histories.o_id')
- .where(
- 'histories.created_at >= ? AND histories.created_at <= ? AND histories.history_object_id = ? AND histories.history_type_id = ?', data[:start], data[:end], history_object.id, history_type.id
- )
- .where(query, *bind_params).joins(tables)
- histories.each do |history|
- count += 1
- ticket_ids.push history.o_id
- end
- return {
- count: count,
- ticket_ids: ticket_ids,
- }
- end
- # updated
- if data[:type] == 'updated'
- history_type = History::Type.lookup(name: 'updated')
- history_attribute = History::Attribute.lookup(name: data[:attribute])
- if !history_attribute || !history_type
- count = 0
- else
- if data[:id_not_from] && data[:id_to]
- histories = History.select('histories.o_id').joins('INNER JOIN tickets ON tickets.id = histories.o_id')
- .where(query, *bind_params).joins(tables)
- .where(
- 'histories.created_at >= ? AND histories.created_at <= ? AND histories.history_object_id = ? AND histories.history_type_id = ? AND histories.history_attribute_id IN (?) AND histories.id_from NOT IN (?) AND histories.id_to IN (?)',
- data[:start],
- data[:end],
- history_object.id,
- history_type.id,
- history_attribute.id,
- data[:id_not_from],
- data[:id_to],
- )
- elsif data[:id_from] && data[:id_not_to]
- histories = History.select('histories.o_id').joins('INNER JOIN tickets ON tickets.id = histories.o_id')
- .where(query, *bind_params).joins(tables)
- .where(
- 'histories.created_at >= ? AND histories.created_at <= ? AND histories.history_object_id = ? AND histories.history_type_id = ? AND histories.history_attribute_id IN (?) AND histories.id_from IN (?) AND histories.id_to NOT IN (?)',
- data[:start],
- data[:end],
- history_object.id,
- history_type.id,
- history_attribute.id,
- data[:id_from],
- data[:id_not_to],
- )
- elsif data[:value_from] && data[:value_not_to]
- histories = History.joins('INNER JOIN tickets ON tickets.id = histories.o_id')
- .where(query, *bind_params).joins(tables)
- .where(
- 'histories.created_at >= ? AND histories.created_at <= ? AND histories.history_object_id = ? AND histories.history_type_id = ? AND histories.history_attribute_id IN (?) AND histories.value_from IN (?) AND histories.value_to NOT IN (?)',
- data[:start],
- data[:end],
- history_object.id,
- history_type.id,
- history_attribute.id,
- data[:value_from],
- data[:value_not_to],
- )
- elsif data[:value_to]
- histories = History.select('histories.o_id').joins('INNER JOIN tickets ON tickets.id = histories.o_id')
- .where(query, *bind_params).joins(tables)
- .where(
- 'histories.created_at >= ? AND histories.created_at <= ? AND histories.history_object_id = ? AND histories.history_type_id = ? AND histories.history_attribute_id IN (?) AND histories.value_to IN (?)',
- data[:start],
- data[:end],
- history_object.id,
- history_type.id,
- history_attribute.id,
- data[:value_to],
- )
- elsif data[:id_to]
- histories = History.select('histories.o_id').joins('INNER JOIN tickets ON tickets.id = histories.o_id')
- .where(query, *bind_params).joins(tables)
- .where(
- 'histories.created_at >= ? AND histories.created_at <= ? AND histories.history_object_id = ? AND histories.history_type_id = ? AND histories.history_attribute_id IN (?) AND histories.id_to IN (?)',
- data[:start],
- data[:end],
- history_object.id,
- history_type.id,
- history_attribute.id,
- data[:id_to],
- )
- end
- histories.each do |history|
- count += 1
- ticket_ids.push history.o_id
- end
- end
- return {
- count: count,
- ticket_ids: ticket_ids,
- }
- end
- raise "UNKOWN :type (#{data[:type]})!"
- end
- # :type
- # :start
- # :end
- # :condition
- def self.time_average(data)
- query, bind_params, tables = Ticket.selector2sql(data[:condition])
- ticket_list = Ticket.where('tickets.created_at >= ? AND tickets.created_at <= ?', data[:start], data[:end])
- .where(query, *bind_params).joins(tables)
- tickets = 0
- time_total = 0
- ticket_list.each do |ticket|
- timestamp = ticket[ data[:type].to_sym ]
- next if !timestamp
- # puts 'FR:' + first_response.to_s
- # puts 'CT:' + ticket.created_at.to_s
- diff = timestamp - ticket.created_at
- # puts 'DIFF:' + diff.to_s
- time_total += diff
- tickets += 1
- end
- if time_total.zero? || tickets.zero?
- tickets = -0.001
- else
- tickets = time_total / tickets / 60
- tickets = tickets.to_i
- end
- {
- count: tickets,
- }
- end
- # :type
- # :start
- # :end
- # :condition
- def self.time_min(data)
- query, bind_params, tables = Ticket.selector2sql(data[:condition])
- ticket_list = Ticket.where('tickets.created_at >= ? AND tickets.created_at <= ?', data[:start], data[:end])
- .where(query, *bind_params).joins(tables)
- time_min = 0
- ticket_ids = []
- ticket_list.each do |ticket|
- timestamp = ticket[ data[:type].to_sym ]
- next if !timestamp
- ticket_ids.push ticket.id
- # puts 'FR:' + first_response.to_s
- # puts 'CT:' + ticket.created_at.to_s
- diff = timestamp - ticket.created_at
- # puts 'DIFF:' + diff.to_s
- if !time_min
- time_min = diff
- end
- if diff < time_min
- time_min = diff
- end
- end
- tickets = if time_min.zero?
- -0.001
- else
- (time_min / 60).to_i
- end
- {
- count: tickets,
- ticket_ids: ticket_ids,
- }
- end
- # :type
- # :start
- # :end
- # :condition
- def self.time_max(data)
- query, bind_params, tables = Ticket.selector2sql(data[:condition])
- ticket_list = Ticket.where('tickets.created_at >= ? AND tickets.created_at <= ?', data[:start], data[:end])
- .where(query, *bind_params).joins(tables)
- time_max = 0
- ticket_ids = []
- ticket_list.each do |ticket|
- timestamp = ticket[ data[:type].to_sym ]
- next if !timestamp
- ticket_ids.push ticket.id
- # puts "#{data[:type].to_s} - #{timestamp} - #{ticket.inspect}"
- # puts 'FR:' + ticket.first_response.to_s
- # puts 'CT:' + ticket.created_at.to_s
- diff = timestamp - ticket.created_at
- # puts 'DIFF:' + diff.to_s
- if !time_max
- time_max = diff
- end
- if diff > time_max
- time_max = diff
- end
- end
- tickets = if time_max.zero?
- -0.001
- else
- (time_max / 60).to_i
- end
- {
- count: tickets,
- ticket_ids: ticket_ids,
- }
- end
- def self.ticket_condition(ticket_id, condition)
- ticket = Ticket.lookup(id: ticket_id)
- condition.each do |key, value|
- if ticket[key.to_sym] != value
- return false
- end
- end
- true
- end
- end
|