time_accountings_controller.rb 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423
  1. # Copyright (C) 2012-2016 Zammad Foundation, http://zammad-foundation.org/
  2. class TimeAccountingsController < ApplicationController
  3. prepend_before_action { authentication_check(permission: 'admin.time_accounting') }
  4. def by_ticket
  5. year = params[:year] || Time.zone.now.year
  6. month = params[:month] || Time.zone.now.month
  7. start_periode = Time.zone.parse("#{year}-#{month}-01")
  8. end_periode = start_periode.end_of_month
  9. time_unit = {}
  10. Ticket::TimeAccounting.where('created_at >= ? AND created_at <= ?', start_periode, end_periode).pluck(:ticket_id, :time_unit, :created_by_id).each do |record|
  11. if !time_unit[record[0]]
  12. time_unit[record[0]] = {
  13. time_unit: 0,
  14. agent_id: record[2],
  15. }
  16. end
  17. time_unit[record[0]][:time_unit] += record[1]
  18. end
  19. customers = {}
  20. organizations = {}
  21. agents = {}
  22. results = []
  23. time_unit.each do |ticket_id, local_time_unit|
  24. ticket = Ticket.lookup(id: ticket_id)
  25. next if !ticket
  26. if !customers[ticket.customer_id]
  27. customers[ticket.customer_id] = '-'
  28. if ticket.customer_id
  29. customer_user = User.lookup(id: ticket.customer_id)
  30. if customer_user
  31. customers[ticket.customer_id] = customer_user.fullname
  32. end
  33. end
  34. end
  35. if !organizations[ticket.organization_id]
  36. organizations[ticket.organization_id] = '-'
  37. if ticket.organization_id
  38. organization = Organization.lookup(id: ticket.organization_id)
  39. if organization
  40. organizations[ticket.organization_id] = organization.name
  41. end
  42. end
  43. end
  44. if !customers[local_time_unit[:agent_id]]
  45. agent_user = User.lookup(id: local_time_unit[:agent_id])
  46. agent = '-'
  47. if agent_user
  48. agents[local_time_unit[:agent_id]] = agent_user.fullname
  49. end
  50. end
  51. result = {
  52. ticket: ticket.attributes,
  53. time_unit: local_time_unit[:time_unit],
  54. customer: customers[ticket.customer_id],
  55. organization: organizations[ticket.organization_id],
  56. agent: agents[local_time_unit[:agent_id]],
  57. }
  58. results.push result
  59. end
  60. if params[:download]
  61. header = [
  62. {
  63. name: 'Ticket#',
  64. width: 15,
  65. },
  66. {
  67. name: 'Title',
  68. width: 30,
  69. },
  70. {
  71. name: 'Customer',
  72. width: 20,
  73. },
  74. {
  75. name: 'Organization',
  76. width: 20,
  77. },
  78. {
  79. name: 'Agent',
  80. width: 20,
  81. },
  82. {
  83. name: 'Time Units',
  84. width: 10,
  85. },
  86. {
  87. name: 'Time Units Total',
  88. width: 10,
  89. },
  90. {
  91. name: 'Created at',
  92. width: 10,
  93. },
  94. {
  95. name: 'Closed at',
  96. width: 10,
  97. },
  98. {
  99. name: 'Close Escalation At',
  100. width: 10,
  101. },
  102. {
  103. name: 'Close In Min',
  104. width: 10,
  105. },
  106. {
  107. name: 'Close Diff In Min',
  108. width: 10,
  109. },
  110. {
  111. name: 'First Response At',
  112. width: 10,
  113. },
  114. {
  115. name: 'First Response Escalation At',
  116. width: 10,
  117. },
  118. {
  119. name: 'First Response In Min',
  120. width: 10,
  121. },
  122. {
  123. name: 'First Response Diff In Min',
  124. width: 10,
  125. },
  126. {
  127. name: 'Update Escalation At',
  128. width: 10,
  129. },
  130. {
  131. name: 'Update In Min',
  132. width: 10,
  133. },
  134. {
  135. name: 'Update Diff In Min',
  136. width: 10,
  137. },
  138. {
  139. name: 'Last Contact At',
  140. width: 10,
  141. },
  142. {
  143. name: 'Last Contact Agent At',
  144. width: 10,
  145. },
  146. {
  147. name: 'Last Contact Customer At',
  148. width: 10,
  149. },
  150. {
  151. name: 'Article Count',
  152. width: 10,
  153. },
  154. {
  155. name: 'Escalation At',
  156. width: 10,
  157. },
  158. ]
  159. result = []
  160. results.each do |row|
  161. row[:ticket].each_key do |field|
  162. next if row[:ticket][field].blank?
  163. next if !row[:ticket][field].is_a?(ActiveSupport::TimeWithZone)
  164. row[:ticket][field] = row[:ticket][field].iso8601
  165. end
  166. result_row = [
  167. row[:ticket]['number'],
  168. row[:ticket]['title'],
  169. row[:customer],
  170. row[:organization],
  171. row[:agent],
  172. row[:time_unit],
  173. row[:ticket]['time_unit'],
  174. row[:ticket]['created_at'],
  175. row[:ticket]['close_at'],
  176. row[:ticket]['close_escalation_at'],
  177. row[:ticket]['close_in_min'],
  178. row[:ticket]['close_diff_in_min'],
  179. row[:ticket]['first_response_at'],
  180. row[:ticket]['first_response_escalation_at'],
  181. row[:ticket]['first_response_in_min'],
  182. row[:ticket]['first_response_diff_in_min'],
  183. row[:ticket]['update_escalation_at'],
  184. row[:ticket]['update_in_min'],
  185. row[:ticket]['update_diff_in_min'],
  186. row[:ticket]['last_contact_at'],
  187. row[:ticket]['last_contact_agent_at'],
  188. row[:ticket]['last_contact_customer_at'],
  189. row[:ticket]['article_count'],
  190. row[:ticket]['escalation_at'],
  191. ]
  192. result.push result_row
  193. end
  194. content = sheet("By Ticket #{year}-#{month}", header, result)
  195. send_data(
  196. content,
  197. filename: "by_ticket-#{year}-#{month}.xls",
  198. type: 'application/vnd.ms-excel',
  199. disposition: 'attachment'
  200. )
  201. return
  202. end
  203. render json: results
  204. end
  205. def by_customer
  206. year = params[:year] || Time.zone.now.year
  207. month = params[:month] || Time.zone.now.month
  208. start_periode = Time.zone.parse("#{year}-#{month}-01")
  209. end_periode = start_periode.end_of_month
  210. time_unit = {}
  211. Ticket::TimeAccounting.where('created_at >= ? AND created_at <= ?', start_periode, end_periode).pluck(:ticket_id, :time_unit, :created_by_id).each do |record|
  212. if !time_unit[record[0]]
  213. time_unit[record[0]] = {
  214. time_unit: 0,
  215. agent_id: record[2],
  216. }
  217. end
  218. time_unit[record[0]][:time_unit] += record[1]
  219. end
  220. customers = {}
  221. time_unit.each do |ticket_id, local_time_unit|
  222. ticket = Ticket.lookup(id: ticket_id)
  223. next if !ticket
  224. if !customers[ticket.customer_id]
  225. organization = nil
  226. if ticket.organization_id
  227. organization = Organization.lookup(id: ticket.organization_id).attributes
  228. end
  229. customers[ticket.customer_id] = {
  230. customer: User.lookup(id: ticket.customer_id).attributes,
  231. organization: organization,
  232. time_unit: local_time_unit[:time_unit],
  233. }
  234. next
  235. end
  236. customers[ticket.customer_id][:time_unit] += local_time_unit[:time_unit]
  237. end
  238. results = []
  239. customers.each_value do |content|
  240. results.push content
  241. end
  242. if params[:download]
  243. header = [
  244. {
  245. name: 'Customer',
  246. width: 30,
  247. },
  248. {
  249. name: 'Organization',
  250. width: 30,
  251. },
  252. {
  253. name: 'Time Units',
  254. width: 10,
  255. }
  256. ]
  257. result = []
  258. results.each do |row|
  259. customer_name = User.find(row[:customer]['id']).fullname
  260. organization_name = ''
  261. if row[:organization].present?
  262. organization_name = row[:organization]['name']
  263. end
  264. result_row = [customer_name, organization_name, row[:time_unit]]
  265. result.push result_row
  266. end
  267. content = sheet("By Customer #{year}-#{month}", header, result)
  268. send_data(
  269. content,
  270. filename: "by_customer-#{year}-#{month}.xls",
  271. type: 'application/vnd.ms-excel',
  272. disposition: 'attachment'
  273. )
  274. return
  275. end
  276. render json: results
  277. end
  278. def by_organization
  279. year = params[:year] || Time.zone.now.year
  280. month = params[:month] || Time.zone.now.month
  281. start_periode = Time.zone.parse("#{year}-#{month}-01")
  282. end_periode = start_periode.end_of_month
  283. time_unit = {}
  284. Ticket::TimeAccounting.where('created_at >= ? AND created_at <= ?', start_periode, end_periode).pluck(:ticket_id, :time_unit, :created_by_id).each do |record|
  285. if !time_unit[record[0]]
  286. time_unit[record[0]] = {
  287. time_unit: 0,
  288. agent_id: record[2],
  289. }
  290. end
  291. time_unit[record[0]][:time_unit] += record[1]
  292. end
  293. organizations = {}
  294. time_unit.each do |ticket_id, local_time_unit|
  295. ticket = Ticket.lookup(id: ticket_id)
  296. next if !ticket
  297. next if !ticket.organization_id
  298. if !organizations[ticket.organization_id]
  299. organizations[ticket.organization_id] = {
  300. organization: Organization.lookup(id: ticket.organization_id).attributes,
  301. time_unit: local_time_unit[:time_unit],
  302. }
  303. next
  304. end
  305. organizations[ticket.organization_id][:time_unit] += local_time_unit[:time_unit]
  306. end
  307. results = []
  308. organizations.each_value do |content|
  309. results.push content
  310. end
  311. if params[:download]
  312. header = [
  313. {
  314. name: 'Organization',
  315. width: 40,
  316. },
  317. {
  318. name: 'Time Units',
  319. width: 20,
  320. }
  321. ]
  322. result = []
  323. results.each do |row|
  324. organization_name = ''
  325. if row[:organization].present?
  326. organization_name = row[:organization]['name']
  327. end
  328. result_row = [organization_name, row[:time_unit]]
  329. result.push result_row
  330. end
  331. content = sheet("By Organization #{year}-#{month}", header, result)
  332. send_data(
  333. content,
  334. filename: "by_organization-#{year}-#{month}.xls",
  335. type: 'application/vnd.ms-excel',
  336. disposition: 'attachment'
  337. )
  338. return
  339. end
  340. render json: results
  341. end
  342. private
  343. def sheet(title, header, result)
  344. # Create a new Excel workbook
  345. temp_file = Tempfile.new('time_tracking.xls')
  346. workbook = WriteExcel.new(temp_file)
  347. # Add a worksheet
  348. worksheet = workbook.add_worksheet
  349. # Add and define a format
  350. format = workbook.add_format # Add a format
  351. format.set_bold
  352. format.set_size(14)
  353. format.set_color('black')
  354. worksheet.set_row(0, 0, header.count)
  355. # Write a formatted and unformatted string, row and column notation.
  356. worksheet.write_string(0, 0, title, format)
  357. format_header = workbook.add_format # Add a format
  358. format_header.set_italic
  359. format_header.set_bg_color('gray')
  360. format_header.set_color('white')
  361. count = 0
  362. header.each do |item|
  363. if item[:width]
  364. worksheet.set_column(count, count, item[:width])
  365. end
  366. worksheet.write_string(2, count, item[:name], format_header)
  367. count += 1
  368. end
  369. row_count = 2
  370. result.each do |row|
  371. row_count += 1
  372. row_item_count = 0
  373. row.each do |item|
  374. if item.acts_like?(:date)
  375. worksheet.write_date_time(row_count, row_item_count, item.to_time.iso8601)
  376. else
  377. worksheet.write_string(row_count, row_item_count, item)
  378. end
  379. row_item_count += 1
  380. end
  381. end
  382. workbook.close
  383. # read file again
  384. file = File.new(temp_file, 'r')
  385. contents = file.read
  386. file.close
  387. contents
  388. end
  389. end