time_accountings_controller.rb 8.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327
  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 = Date.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 { |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. }
  19. customers = {}
  20. organizations = {}
  21. agents = {}
  22. results = []
  23. time_unit.each { |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. }
  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. result = []
  92. results.each { |row|
  93. result_row = [
  94. row[:ticket]['number'],
  95. row[:ticket]['title'],
  96. row[:customer],
  97. row[:organization],
  98. row[:agent],
  99. row[:time_unit],
  100. row[:ticket]['time_unit'],
  101. ]
  102. result.push result_row
  103. }
  104. content = sheet("By Ticket #{year}-#{month}", header, result)
  105. send_data(
  106. content,
  107. filename: "by_ticket-#{year}-#{month}.xls",
  108. type: 'application/vnd.ms-excel',
  109. disposition: 'attachment'
  110. )
  111. return
  112. end
  113. render json: results
  114. end
  115. def by_customer
  116. year = params[:year] || Time.zone.now.year
  117. month = params[:month] || Time.zone.now.month
  118. start_periode = Date.parse("#{year}-#{month}-01")
  119. end_periode = start_periode.end_of_month
  120. time_unit = {}
  121. Ticket::TimeAccounting.where('created_at >= ? AND created_at <= ?', start_periode, end_periode).pluck(:ticket_id, :time_unit, :created_by_id).each { |record|
  122. if !time_unit[record[0]]
  123. time_unit[record[0]] = {
  124. time_unit: 0,
  125. agent_id: record[2],
  126. }
  127. end
  128. time_unit[record[0]][:time_unit] += record[1]
  129. }
  130. customers = {}
  131. time_unit.each { |ticket_id, local_time_unit|
  132. ticket = Ticket.lookup(id: ticket_id)
  133. next if !ticket
  134. if !customers[ticket.customer_id]
  135. organization = nil
  136. if ticket.organization_id
  137. organization = Organization.lookup(id: ticket.organization_id).attributes
  138. end
  139. customers[ticket.customer_id] = {
  140. customer: User.lookup(id: ticket.customer_id).attributes,
  141. organization: organization,
  142. time_unit: local_time_unit[:time_unit],
  143. }
  144. next
  145. end
  146. customers[ticket.customer_id][:time_unit] += local_time_unit[:time_unit]
  147. }
  148. results = []
  149. customers.each { |_customer_id, content|
  150. results.push content
  151. }
  152. if params[:download]
  153. header = [
  154. {
  155. name: 'Customer',
  156. width: 30,
  157. },
  158. {
  159. name: 'Organization',
  160. width: 30,
  161. },
  162. {
  163. name: 'Time Units',
  164. width: 10,
  165. }
  166. ]
  167. result = []
  168. results.each { |row|
  169. customer_name = User.find(row[:customer]['id']).fullname
  170. organization_name = ''
  171. if row[:organization].present?
  172. organization_name = row[:organization]['name']
  173. end
  174. result_row = [customer_name, organization_name, row[:time_unit]]
  175. result.push result_row
  176. }
  177. content = sheet("By Customer #{year}-#{month}", header, result)
  178. send_data(
  179. content,
  180. filename: "by_customer-#{year}-#{month}.xls",
  181. type: 'application/vnd.ms-excel',
  182. disposition: 'attachment'
  183. )
  184. return
  185. end
  186. render json: results
  187. end
  188. def by_organization
  189. year = params[:year] || Time.zone.now.year
  190. month = params[:month] || Time.zone.now.month
  191. start_periode = Date.parse("#{year}-#{month}-01")
  192. end_periode = start_periode.end_of_month
  193. time_unit = {}
  194. Ticket::TimeAccounting.where('created_at >= ? AND created_at <= ?', start_periode, end_periode).pluck(:ticket_id, :time_unit, :created_by_id).each { |record|
  195. if !time_unit[record[0]]
  196. time_unit[record[0]] = {
  197. time_unit: 0,
  198. agent_id: record[2],
  199. }
  200. end
  201. time_unit[record[0]][:time_unit] += record[1]
  202. }
  203. organizations = {}
  204. time_unit.each { |ticket_id, local_time_unit|
  205. ticket = Ticket.lookup(id: ticket_id)
  206. next if !ticket
  207. next if !ticket.organization_id
  208. if !organizations[ticket.organization_id]
  209. organizations[ticket.organization_id] = {
  210. organization: Organization.lookup(id: ticket.organization_id).attributes,
  211. time_unit: local_time_unit[:time_unit],
  212. }
  213. next
  214. end
  215. organizations[ticket.organization_id][:time_unit] += local_time_unit[:time_unit]
  216. }
  217. results = []
  218. organizations.each { |_customer_id, content|
  219. results.push content
  220. }
  221. if params[:download]
  222. header = [
  223. {
  224. name: 'Organization',
  225. width: 40,
  226. },
  227. {
  228. name: 'Time Units',
  229. width: 20,
  230. }
  231. ]
  232. result = []
  233. results.each { |row|
  234. organization_name = ''
  235. if row[:organization].present?
  236. organization_name = row[:organization]['name']
  237. end
  238. result_row = [organization_name, row[:time_unit]]
  239. result.push result_row
  240. }
  241. content = sheet("By Organization #{year}-#{month}", header, result)
  242. send_data(
  243. content,
  244. filename: "by_organization-#{year}-#{month}.xls",
  245. type: 'application/vnd.ms-excel',
  246. disposition: 'attachment'
  247. )
  248. return
  249. end
  250. render json: results
  251. end
  252. private
  253. def sheet(title, header, result)
  254. # Create a new Excel workbook
  255. temp_file = Tempfile.new('time_tracking.xls')
  256. workbook = WriteExcel.new(temp_file)
  257. # Add a worksheet
  258. worksheet = workbook.add_worksheet
  259. # Add and define a format
  260. format = workbook.add_format # Add a format
  261. format.set_bold
  262. format.set_size(14)
  263. format.set_color('black')
  264. worksheet.set_row(0, 0, header.count)
  265. # Write a formatted and unformatted string, row and column notation.
  266. worksheet.write(0, 0, title, format)
  267. format_header = workbook.add_format # Add a format
  268. format_header.set_italic
  269. format_header.set_bg_color('gray')
  270. format_header.set_color('white')
  271. count = 0
  272. header.each { |item|
  273. if item[:width]
  274. worksheet.set_column(count, count, item[:width])
  275. end
  276. worksheet.write(2, count, item[:name], format_header)
  277. count += 1
  278. }
  279. row_count = 2
  280. result.each { |row|
  281. row_count += 1
  282. row_item_count = 0
  283. row.each { |item|
  284. worksheet.write(row_count, row_item_count, item)
  285. row_item_count += 1
  286. }
  287. }
  288. workbook.close
  289. # read file again
  290. file = File.new(temp_file, 'r')
  291. contents = file.read
  292. file.close
  293. contents
  294. end
  295. end