123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423 |
- # Copyright (C) 2012-2016 Zammad Foundation, http://zammad-foundation.org/
- class TimeAccountingsController < ApplicationController
- prepend_before_action { authentication_check(permission: 'admin.time_accounting') }
- def by_ticket
- year = params[:year] || Time.zone.now.year
- month = params[:month] || Time.zone.now.month
- start_periode = Time.zone.parse("#{year}-#{month}-01")
- end_periode = start_periode.end_of_month
- time_unit = {}
- Ticket::TimeAccounting.where('created_at >= ? AND created_at <= ?', start_periode, end_periode).pluck(:ticket_id, :time_unit, :created_by_id).each do |record|
- if !time_unit[record[0]]
- time_unit[record[0]] = {
- time_unit: 0,
- agent_id: record[2],
- }
- end
- time_unit[record[0]][:time_unit] += record[1]
- end
- customers = {}
- organizations = {}
- agents = {}
- results = []
- time_unit.each do |ticket_id, local_time_unit|
- ticket = Ticket.lookup(id: ticket_id)
- next if !ticket
- if !customers[ticket.customer_id]
- customers[ticket.customer_id] = '-'
- if ticket.customer_id
- customer_user = User.lookup(id: ticket.customer_id)
- if customer_user
- customers[ticket.customer_id] = customer_user.fullname
- end
- end
- end
- if !organizations[ticket.organization_id]
- organizations[ticket.organization_id] = '-'
- if ticket.organization_id
- organization = Organization.lookup(id: ticket.organization_id)
- if organization
- organizations[ticket.organization_id] = organization.name
- end
- end
- end
- if !customers[local_time_unit[:agent_id]]
- agent_user = User.lookup(id: local_time_unit[:agent_id])
- agent = '-'
- if agent_user
- agents[local_time_unit[:agent_id]] = agent_user.fullname
- end
- end
- result = {
- ticket: ticket.attributes,
- time_unit: local_time_unit[:time_unit],
- customer: customers[ticket.customer_id],
- organization: organizations[ticket.organization_id],
- agent: agents[local_time_unit[:agent_id]],
- }
- results.push result
- end
- if params[:download]
- header = [
- {
- name: 'Ticket#',
- width: 15,
- },
- {
- name: 'Title',
- width: 30,
- },
- {
- name: 'Customer',
- width: 20,
- },
- {
- name: 'Organization',
- width: 20,
- },
- {
- name: 'Agent',
- width: 20,
- },
- {
- name: 'Time Units',
- width: 10,
- },
- {
- name: 'Time Units Total',
- width: 10,
- },
- {
- name: 'Created at',
- width: 10,
- },
- {
- name: 'Closed at',
- width: 10,
- },
- {
- name: 'Close Escalation At',
- width: 10,
- },
- {
- name: 'Close In Min',
- width: 10,
- },
- {
- name: 'Close Diff In Min',
- width: 10,
- },
- {
- name: 'First Response At',
- width: 10,
- },
- {
- name: 'First Response Escalation At',
- width: 10,
- },
- {
- name: 'First Response In Min',
- width: 10,
- },
- {
- name: 'First Response Diff In Min',
- width: 10,
- },
- {
- name: 'Update Escalation At',
- width: 10,
- },
- {
- name: 'Update In Min',
- width: 10,
- },
- {
- name: 'Update Diff In Min',
- width: 10,
- },
- {
- name: 'Last Contact At',
- width: 10,
- },
- {
- name: 'Last Contact Agent At',
- width: 10,
- },
- {
- name: 'Last Contact Customer At',
- width: 10,
- },
- {
- name: 'Article Count',
- width: 10,
- },
- {
- name: 'Escalation At',
- width: 10,
- },
- ]
- result = []
- results.each do |row|
- row[:ticket].each_key do |field|
- next if row[:ticket][field].blank?
- next if !row[:ticket][field].is_a?(ActiveSupport::TimeWithZone)
- row[:ticket][field] = row[:ticket][field].iso8601
- end
- result_row = [
- row[:ticket]['number'],
- row[:ticket]['title'],
- row[:customer],
- row[:organization],
- row[:agent],
- row[:time_unit],
- row[:ticket]['time_unit'],
- row[:ticket]['created_at'],
- row[:ticket]['close_at'],
- row[:ticket]['close_escalation_at'],
- row[:ticket]['close_in_min'],
- row[:ticket]['close_diff_in_min'],
- row[:ticket]['first_response_at'],
- row[:ticket]['first_response_escalation_at'],
- row[:ticket]['first_response_in_min'],
- row[:ticket]['first_response_diff_in_min'],
- row[:ticket]['update_escalation_at'],
- row[:ticket]['update_in_min'],
- row[:ticket]['update_diff_in_min'],
- row[:ticket]['last_contact_at'],
- row[:ticket]['last_contact_agent_at'],
- row[:ticket]['last_contact_customer_at'],
- row[:ticket]['article_count'],
- row[:ticket]['escalation_at'],
- ]
- result.push result_row
- end
- content = sheet("By Ticket #{year}-#{month}", header, result)
- send_data(
- content,
- filename: "by_ticket-#{year}-#{month}.xls",
- type: 'application/vnd.ms-excel',
- disposition: 'attachment'
- )
- return
- end
- render json: results
- end
- def by_customer
- year = params[:year] || Time.zone.now.year
- month = params[:month] || Time.zone.now.month
- start_periode = Time.zone.parse("#{year}-#{month}-01")
- end_periode = start_periode.end_of_month
- time_unit = {}
- Ticket::TimeAccounting.where('created_at >= ? AND created_at <= ?', start_periode, end_periode).pluck(:ticket_id, :time_unit, :created_by_id).each do |record|
- if !time_unit[record[0]]
- time_unit[record[0]] = {
- time_unit: 0,
- agent_id: record[2],
- }
- end
- time_unit[record[0]][:time_unit] += record[1]
- end
- customers = {}
- time_unit.each do |ticket_id, local_time_unit|
- ticket = Ticket.lookup(id: ticket_id)
- next if !ticket
- if !customers[ticket.customer_id]
- organization = nil
- if ticket.organization_id
- organization = Organization.lookup(id: ticket.organization_id).attributes
- end
- customers[ticket.customer_id] = {
- customer: User.lookup(id: ticket.customer_id).attributes,
- organization: organization,
- time_unit: local_time_unit[:time_unit],
- }
- next
- end
- customers[ticket.customer_id][:time_unit] += local_time_unit[:time_unit]
- end
- results = []
- customers.each_value do |content|
- results.push content
- end
- if params[:download]
- header = [
- {
- name: 'Customer',
- width: 30,
- },
- {
- name: 'Organization',
- width: 30,
- },
- {
- name: 'Time Units',
- width: 10,
- }
- ]
- result = []
- results.each do |row|
- customer_name = User.find(row[:customer]['id']).fullname
- organization_name = ''
- if row[:organization].present?
- organization_name = row[:organization]['name']
- end
- result_row = [customer_name, organization_name, row[:time_unit]]
- result.push result_row
- end
- content = sheet("By Customer #{year}-#{month}", header, result)
- send_data(
- content,
- filename: "by_customer-#{year}-#{month}.xls",
- type: 'application/vnd.ms-excel',
- disposition: 'attachment'
- )
- return
- end
- render json: results
- end
- def by_organization
- year = params[:year] || Time.zone.now.year
- month = params[:month] || Time.zone.now.month
- start_periode = Time.zone.parse("#{year}-#{month}-01")
- end_periode = start_periode.end_of_month
- time_unit = {}
- Ticket::TimeAccounting.where('created_at >= ? AND created_at <= ?', start_periode, end_periode).pluck(:ticket_id, :time_unit, :created_by_id).each do |record|
- if !time_unit[record[0]]
- time_unit[record[0]] = {
- time_unit: 0,
- agent_id: record[2],
- }
- end
- time_unit[record[0]][:time_unit] += record[1]
- end
- organizations = {}
- time_unit.each do |ticket_id, local_time_unit|
- ticket = Ticket.lookup(id: ticket_id)
- next if !ticket
- next if !ticket.organization_id
- if !organizations[ticket.organization_id]
- organizations[ticket.organization_id] = {
- organization: Organization.lookup(id: ticket.organization_id).attributes,
- time_unit: local_time_unit[:time_unit],
- }
- next
- end
- organizations[ticket.organization_id][:time_unit] += local_time_unit[:time_unit]
- end
- results = []
- organizations.each_value do |content|
- results.push content
- end
- if params[:download]
- header = [
- {
- name: 'Organization',
- width: 40,
- },
- {
- name: 'Time Units',
- width: 20,
- }
- ]
- result = []
- results.each do |row|
- organization_name = ''
- if row[:organization].present?
- organization_name = row[:organization]['name']
- end
- result_row = [organization_name, row[:time_unit]]
- result.push result_row
- end
- content = sheet("By Organization #{year}-#{month}", header, result)
- send_data(
- content,
- filename: "by_organization-#{year}-#{month}.xls",
- type: 'application/vnd.ms-excel',
- disposition: 'attachment'
- )
- return
- end
- render json: results
- end
- private
- def sheet(title, header, result)
- # Create a new Excel workbook
- temp_file = Tempfile.new('time_tracking.xls')
- workbook = WriteExcel.new(temp_file)
- # Add a worksheet
- worksheet = workbook.add_worksheet
- # Add and define a format
- format = workbook.add_format # Add a format
- format.set_bold
- format.set_size(14)
- format.set_color('black')
- worksheet.set_row(0, 0, header.count)
- # Write a formatted and unformatted string, row and column notation.
- worksheet.write_string(0, 0, title, format)
- format_header = workbook.add_format # Add a format
- format_header.set_italic
- format_header.set_bg_color('gray')
- format_header.set_color('white')
- count = 0
- header.each do |item|
- if item[:width]
- worksheet.set_column(count, count, item[:width])
- end
- worksheet.write_string(2, count, item[:name], format_header)
- count += 1
- end
- row_count = 2
- result.each do |row|
- row_count += 1
- row_item_count = 0
- row.each do |item|
- if item.acts_like?(:date)
- worksheet.write_date_time(row_count, row_item_count, item.to_time.iso8601)
- else
- worksheet.write_string(row_count, row_item_count, item)
- end
- row_item_count += 1
- end
- end
- workbook.close
- # read file again
- file = File.new(temp_file, 'r')
- contents = file.read
- file.close
- contents
- end
- end
|