ticket.rb 40 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199
  1. # Copyright (C) 2012-2016 Zammad Foundation, http://zammad-foundation.org/
  2. class Ticket < ApplicationModel
  3. include HasActivityStreamLog
  4. include ChecksClientNotification
  5. include ChecksLatestChangeObserved
  6. include HasHistory
  7. include HasTags
  8. include HasSearchIndexBackend
  9. include HasOnlineNotifications
  10. include HasKarmaActivityLog
  11. include HasLinks
  12. include Ticket::ChecksAccess
  13. include Ticket::Escalation
  14. include Ticket::Subject
  15. load 'ticket/assets.rb'
  16. include Ticket::Assets
  17. load 'ticket/search_index.rb'
  18. include Ticket::SearchIndex
  19. extend Ticket::Search
  20. store :preferences
  21. before_create :check_generate, :check_defaults, :check_title, :set_default_state, :set_default_priority
  22. after_create :check_escalation_update
  23. before_update :check_defaults, :check_title, :reset_pending_time
  24. after_update :check_escalation_update
  25. validates :group_id, presence: true
  26. activity_stream_permission 'ticket.agent'
  27. activity_stream_attributes_ignored :organization_id, # organization_id will channge automatically on user update
  28. :create_article_type_id,
  29. :create_article_sender_id,
  30. :article_count,
  31. :first_response_at,
  32. :first_response_escalation_at,
  33. :first_response_in_min,
  34. :first_response_diff_in_min,
  35. :close_at,
  36. :close_escalation_at,
  37. :close_in_min,
  38. :close_diff_in_min,
  39. :update_escalation_at,
  40. :update_in_min,
  41. :update_diff_in_min,
  42. :last_contact_at,
  43. :last_contact_agent_at,
  44. :last_contact_customer_at,
  45. :last_owner_update_at,
  46. :preferences
  47. history_attributes_ignored :create_article_type_id,
  48. :create_article_sender_id,
  49. :article_count,
  50. :preferences
  51. belongs_to :group, class_name: 'Group'
  52. has_many :articles, class_name: 'Ticket::Article', after_add: :cache_update, after_remove: :cache_update, dependent: :destroy
  53. has_many :ticket_time_accounting, class_name: 'Ticket::TimeAccounting', dependent: :destroy
  54. belongs_to :organization, class_name: 'Organization'
  55. belongs_to :state, class_name: 'Ticket::State'
  56. belongs_to :priority, class_name: 'Ticket::Priority'
  57. belongs_to :owner, class_name: 'User'
  58. belongs_to :customer, class_name: 'User'
  59. belongs_to :created_by, class_name: 'User'
  60. belongs_to :updated_by, class_name: 'User'
  61. belongs_to :create_article_type, class_name: 'Ticket::Article::Type'
  62. belongs_to :create_article_sender, class_name: 'Ticket::Article::Sender'
  63. self.inheritance_column = nil
  64. attr_accessor :callback_loop
  65. =begin
  66. get user access conditions
  67. conditions = Ticket.access_condition( User.find(1) , 'full')
  68. returns
  69. result = [user1, user2, ...]
  70. =end
  71. def self.access_condition(user, access)
  72. if user.permissions?('ticket.agent')
  73. ['group_id IN (?)', user.group_ids_access(access)]
  74. elsif !user.organization || ( !user.organization.shared || user.organization.shared == false )
  75. ['tickets.customer_id = ?', user.id]
  76. else
  77. ['(tickets.customer_id = ? OR tickets.organization_id = ?)', user.id, user.organization.id]
  78. end
  79. end
  80. =begin
  81. processes tickets which have reached their pending time and sets next state_id
  82. processed_tickets = Ticket.process_pending
  83. returns
  84. processed_tickets = [<Ticket>, ...]
  85. =end
  86. def self.process_pending
  87. result = []
  88. # process pending action tickets
  89. pending_action = Ticket::StateType.find_by(name: 'pending action')
  90. ticket_states_pending_action = Ticket::State.where(state_type_id: pending_action)
  91. .where.not(next_state_id: nil)
  92. if ticket_states_pending_action.present?
  93. next_state_map = {}
  94. ticket_states_pending_action.each do |state|
  95. next_state_map[state.id] = state.next_state_id
  96. end
  97. tickets = where(state_id: next_state_map.keys)
  98. .where('pending_time <= ?', Time.zone.now)
  99. tickets.each do |ticket|
  100. Transaction.execute do
  101. ticket.state_id = next_state_map[ticket.state_id]
  102. ticket.updated_at = Time.zone.now
  103. ticket.updated_by_id = 1
  104. ticket.save!
  105. end
  106. result.push ticket
  107. end
  108. end
  109. # process pending reminder tickets
  110. pending_reminder = Ticket::StateType.find_by(name: 'pending reminder')
  111. ticket_states_pending_reminder = Ticket::State.where(state_type_id: pending_reminder)
  112. if ticket_states_pending_reminder.present?
  113. reminder_state_map = {}
  114. ticket_states_pending_reminder.each do |state|
  115. reminder_state_map[state.id] = state.next_state_id
  116. end
  117. tickets = where(state_id: reminder_state_map.keys)
  118. .where('pending_time <= ?', Time.zone.now)
  119. tickets.each do |ticket|
  120. article_id = nil
  121. article = Ticket::Article.last_customer_agent_article(ticket.id)
  122. if article
  123. article_id = article.id
  124. end
  125. # send notification
  126. Transaction::BackgroundJob.run(
  127. object: 'Ticket',
  128. type: 'reminder_reached',
  129. object_id: ticket.id,
  130. article_id: article_id,
  131. user_id: 1,
  132. )
  133. result.push ticket
  134. end
  135. end
  136. result
  137. end
  138. =begin
  139. processes escalated tickets
  140. processed_tickets = Ticket.process_escalation
  141. returns
  142. processed_tickets = [<Ticket>, ...]
  143. =end
  144. def self.process_escalation
  145. result = []
  146. # get max warning diff
  147. tickets = where('escalation_at <= ?', Time.zone.now + 15.minutes)
  148. tickets.each do |ticket|
  149. # get sla
  150. sla = ticket.escalation_calculation_get_sla
  151. article_id = nil
  152. article = Ticket::Article.last_customer_agent_article(ticket.id)
  153. if article
  154. article_id = article.id
  155. end
  156. # send escalation
  157. if ticket.escalation_at < Time.zone.now
  158. Transaction::BackgroundJob.run(
  159. object: 'Ticket',
  160. type: 'escalation',
  161. object_id: ticket.id,
  162. article_id: article_id,
  163. user_id: 1,
  164. )
  165. result.push ticket
  166. next
  167. end
  168. # check if warning need to be sent
  169. Transaction::BackgroundJob.run(
  170. object: 'Ticket',
  171. type: 'escalation_warning',
  172. object_id: ticket.id,
  173. article_id: article_id,
  174. user_id: 1,
  175. )
  176. result.push ticket
  177. end
  178. result
  179. end
  180. =begin
  181. processes tickets which auto unassign time has reached
  182. processed_tickets = Ticket.process_auto_unassign
  183. returns
  184. processed_tickets = [<Ticket>, ...]
  185. =end
  186. def self.process_auto_unassign
  187. # process pending action tickets
  188. state_ids = Ticket::State.by_category(:work_on).pluck(:id)
  189. return [] if state_ids.blank?
  190. result = []
  191. groups = Group.where(active: true).where('assignment_timeout IS NOT NULL AND groups.assignment_timeout != 0')
  192. return [] if groups.blank?
  193. groups.each do |group|
  194. next if group.assignment_timeout.blank?
  195. ticket_ids = Ticket.where('state_id IN (?) AND owner_id != 1 AND group_id = ? AND last_owner_update_at IS NOT NULL', state_ids, group.id).limit(600).pluck(:id)
  196. ticket_ids.each do |ticket_id|
  197. ticket = Ticket.find_by(id: ticket_id)
  198. next if !ticket
  199. minutes_since_last_assignment = Time.zone.now - ticket.last_owner_update_at
  200. next if (minutes_since_last_assignment / 60) <= group.assignment_timeout
  201. Transaction.execute do
  202. ticket.owner_id = 1
  203. ticket.updated_at = Time.zone.now
  204. ticket.updated_by_id = 1
  205. ticket.save!
  206. end
  207. result.push ticket
  208. end
  209. end
  210. result
  211. end
  212. =begin
  213. merge tickets
  214. ticket = Ticket.find(123)
  215. result = ticket.merge_to(
  216. ticket_id: 123,
  217. user_id: 123,
  218. )
  219. returns
  220. result = true|false
  221. =end
  222. def merge_to(data)
  223. # prevent cross merging tickets
  224. target_ticket = Ticket.find_by(id: data[:ticket_id])
  225. raise 'no target ticket given' if !target_ticket
  226. raise Exceptions::UnprocessableEntity, 'ticket already merged, no merge into merged ticket possible' if target_ticket.state.state_type.name == 'merged'
  227. # check different ticket ids
  228. raise Exceptions::UnprocessableEntity, 'Can\'t merge ticket with it self!' if id == target_ticket.id
  229. # update articles
  230. Transaction.execute do
  231. Ticket::Article.where(ticket_id: id).each(&:touch)
  232. # quiet update of reassign of articles
  233. Ticket::Article.where(ticket_id: id).update_all(['ticket_id = ?', data[:ticket_id]])
  234. # update history
  235. # create new merge article
  236. Ticket::Article.create(
  237. ticket_id: id,
  238. type_id: Ticket::Article::Type.lookup(name: 'note').id,
  239. sender_id: Ticket::Article::Sender.lookup(name: 'Agent').id,
  240. body: 'merged',
  241. internal: false,
  242. created_by_id: data[:user_id],
  243. updated_by_id: data[:user_id],
  244. )
  245. # add history to both
  246. # reassign links to the new ticket
  247. Link.where(
  248. link_object_source_id: Link::Object.find_by(name: 'Ticket').id,
  249. link_object_source_value: id,
  250. ).update_all(link_object_source_value: data[:ticket_id])
  251. Link.where(
  252. link_object_target_id: Link::Object.find_by(name: 'Ticket').id,
  253. link_object_target_value: id,
  254. ).update_all(link_object_target_value: data[:ticket_id])
  255. # link tickets
  256. Link.add(
  257. link_type: 'parent',
  258. link_object_source: 'Ticket',
  259. link_object_source_value: data[:ticket_id],
  260. link_object_target: 'Ticket',
  261. link_object_target_value: id
  262. )
  263. # set state to 'merged'
  264. self.state_id = Ticket::State.lookup(name: 'merged').id
  265. # rest owner
  266. self.owner_id = 1
  267. # save ticket
  268. save!
  269. # touch new ticket (to broadcast change)
  270. target_ticket.touch
  271. end
  272. true
  273. end
  274. =begin
  275. check if online notifcation should be shown in general as already seen with current state
  276. ticket = Ticket.find(1)
  277. seen = ticket.online_notification_seen_state(user_id_check)
  278. returns
  279. result = true # or false
  280. check if online notifcation should be shown for this user as already seen with current state
  281. ticket = Ticket.find(1)
  282. seen = ticket.online_notification_seen_state(check_user_id)
  283. returns
  284. result = true # or false
  285. =end
  286. def online_notification_seen_state(user_id_check = nil)
  287. state = Ticket::State.lookup(id: state_id)
  288. state_type = Ticket::StateType.lookup(id: state.state_type_id)
  289. # always to set unseen for ticket owner and users which did not the update
  290. if state_type.name != 'merged'
  291. if user_id_check
  292. return false if user_id_check == owner_id && user_id_check != updated_by_id
  293. end
  294. end
  295. # set all to seen if pending action state is a closed or merged state
  296. if state_type.name == 'pending action' && state.next_state_id
  297. state = Ticket::State.lookup(id: state.next_state_id)
  298. state_type = Ticket::StateType.lookup(id: state.state_type_id)
  299. end
  300. # set all to seen if new state is pending reminder state
  301. if state_type.name == 'pending reminder'
  302. if user_id_check
  303. return false if owner_id == 1
  304. return false if updated_by_id != owner_id && user_id_check == owner_id
  305. return true
  306. end
  307. return true
  308. end
  309. # set all to seen if new state is a closed or merged state
  310. return true if state_type.name == 'closed'
  311. return true if state_type.name == 'merged'
  312. false
  313. end
  314. =begin
  315. get count of tickets and tickets which match on selector
  316. ticket_count, tickets = Ticket.selectors(params[:condition], limit, current_user, 'full')
  317. =end
  318. def self.selectors(selectors, limit = 10, current_user = nil, access = 'full')
  319. raise 'no selectors given' if !selectors
  320. query, bind_params, tables = selector2sql(selectors, current_user)
  321. return [] if !query
  322. ActiveRecord::Base.transaction(requires_new: true) do
  323. begin
  324. if !current_user
  325. ticket_count = Ticket.where(query, *bind_params).joins(tables).count
  326. tickets = Ticket.where(query, *bind_params).joins(tables).limit(limit)
  327. return [ticket_count, tickets]
  328. end
  329. access_condition = Ticket.access_condition(current_user, access)
  330. ticket_count = Ticket.where(access_condition).where(query, *bind_params).joins(tables).count
  331. tickets = Ticket.where(access_condition).where(query, *bind_params).joins(tables).limit(limit)
  332. return [ticket_count, tickets]
  333. rescue ActiveRecord::StatementInvalid => e
  334. Rails.logger.error e.inspect
  335. Rails.logger.error e.backtrace
  336. raise ActiveRecord::Rollback
  337. end
  338. end
  339. []
  340. end
  341. =begin
  342. generate condition query to search for tickets based on condition
  343. query_condition, bind_condition, tables = selector2sql(params[:condition], current_user)
  344. condition example
  345. {
  346. 'ticket.title' => {
  347. operator: 'contains', # contains not
  348. value: 'some value',
  349. },
  350. 'ticket.state_id' => {
  351. operator: 'is',
  352. value: [1,2,5]
  353. },
  354. 'ticket.created_at' => {
  355. operator: 'after (absolute)', # after,before
  356. value: '2015-10-17T06:00:00.000Z',
  357. },
  358. 'ticket.created_at' => {
  359. operator: 'within next (relative)', # before,within,in,after
  360. range: 'day', # minute|hour|day|month|year
  361. value: '25',
  362. },
  363. 'ticket.owner_id' => {
  364. operator: 'is', # is not
  365. pre_condition: 'current_user.id',
  366. },
  367. 'ticket.owner_id' => {
  368. operator: 'is', # is not
  369. pre_condition: 'specific',
  370. value: 4711,
  371. },
  372. 'ticket.escalation_at' => {
  373. operator: 'is not', # not
  374. value: nil,
  375. },
  376. 'ticket.tags' => {
  377. operator: 'contains all', # contains all|contains one|contains all not|contains one not
  378. value: 'tag1, tag2',
  379. },
  380. }
  381. =end
  382. def self.selector2sql(selectors, current_user = nil)
  383. current_user_id = UserInfo.current_user_id
  384. if current_user
  385. current_user_id = current_user.id
  386. end
  387. return if !selectors
  388. # remember query and bind params
  389. query = ''
  390. bind_params = []
  391. like = Rails.application.config.db_like
  392. # get tables to join
  393. tables = ''
  394. selectors.each do |attribute, selector|
  395. selector = attribute.split(/\./)
  396. next if !selector[1]
  397. next if selector[0] == 'ticket'
  398. next if tables.include?(selector[0])
  399. if query != ''
  400. query += ' AND '
  401. end
  402. if selector[0] == 'customer'
  403. tables += ', users customers'
  404. query += 'tickets.customer_id = customers.id'
  405. elsif selector[0] == 'organization'
  406. tables += ', organizations'
  407. query += 'tickets.organization_id = organizations.id'
  408. elsif selector[0] == 'owner'
  409. tables += ', users owners'
  410. query += 'tickets.owner_id = owners.id'
  411. elsif selector[0] == 'article'
  412. tables += ', ticket_articles articles'
  413. query += 'tickets.id = articles.ticket_id'
  414. else
  415. raise "invalid selector #{attribute.inspect}->#{selector.inspect}"
  416. end
  417. end
  418. # add conditions
  419. selectors.each do |attribute, selector_raw|
  420. # validation
  421. raise "Invalid selector #{selector_raw.inspect}" if !selector_raw
  422. raise "Invalid selector #{selector_raw.inspect}" if !selector_raw.respond_to?(:key?)
  423. selector = selector_raw.stringify_keys
  424. raise "Invalid selector, operator missing #{selector.inspect}" if !selector['operator']
  425. # validate value / allow blank but only if pre_condition exists and is not specific
  426. if !selector.key?('value') || ((selector['value'].class == String || selector['value'].class == Array) && (selector['value'].respond_to?(:empty?) && selector['value'].empty?))
  427. return nil if selector['pre_condition'].nil?
  428. return nil if selector['pre_condition'].respond_to?(:blank?) && selector['pre_condition'].blank?
  429. return nil if selector['pre_condition'] == 'specific'
  430. end
  431. # validate pre_condition values
  432. return nil if selector['pre_condition'] && selector['pre_condition'] !~ /^(not_set|current_user\.|specific)/
  433. # get attributes
  434. attributes = attribute.split(/\./)
  435. attribute = "#{attributes[0]}s.#{attributes[1]}"
  436. # magic selectors
  437. if attributes[0] == 'ticket' && attributes[1] == 'out_of_office_replacement_id'
  438. attribute = "#{attributes[0]}s.owner_id"
  439. end
  440. if attributes[0] == 'ticket' && attributes[1] == 'tags'
  441. selector['value'] = selector['value'].split(/,/).collect(&:strip)
  442. end
  443. if query != ''
  444. query += ' AND '
  445. end
  446. if selector['operator'] == 'is'
  447. if selector['pre_condition'] == 'not_set'
  448. if attributes[1] =~ /^(created_by|updated_by|owner|customer|user)_id/
  449. query += "#{attribute} IN (?)"
  450. bind_params.push 1
  451. else
  452. query += "#{attribute} IS NULL"
  453. end
  454. elsif selector['pre_condition'] == 'current_user.id'
  455. raise "Use current_user.id in selector, but no current_user is set #{selector.inspect}" if !current_user_id
  456. if attributes[1] == 'out_of_office_replacement_id'
  457. query += "#{attribute} IN (?)"
  458. bind_params.push User.find(current_user_id).out_of_office_agent_of.pluck(:id)
  459. else
  460. query += "#{attribute} IN (?)"
  461. bind_params.push current_user_id
  462. end
  463. elsif selector['pre_condition'] == 'current_user.organization_id'
  464. raise "Use current_user.id in selector, but no current_user is set #{selector.inspect}" if !current_user_id
  465. query += "#{attribute} IN (?)"
  466. user = User.lookup(id: current_user_id)
  467. bind_params.push user.organization_id
  468. else
  469. # rubocop:disable Style/IfInsideElse
  470. if selector['value'].nil?
  471. query += "#{attribute} IS NULL"
  472. else
  473. if attributes[1] == 'out_of_office_replacement_id'
  474. query += "#{attribute} IN (?)"
  475. bind_params.push User.find(selector['value']).out_of_office_agent_of.pluck(:id)
  476. else
  477. query += "#{attribute} IN (?)"
  478. bind_params.push selector['value']
  479. end
  480. end
  481. # rubocop:enable Style/IfInsideElse
  482. end
  483. elsif selector['operator'] == 'is not'
  484. if selector['pre_condition'] == 'not_set'
  485. if attributes[1] =~ /^(created_by|updated_by|owner|customer|user)_id/
  486. query += "#{attribute} NOT IN (?)"
  487. bind_params.push 1
  488. else
  489. query += "#{attribute} IS NOT NULL"
  490. end
  491. elsif selector['pre_condition'] == 'current_user.id'
  492. if attributes[1] == 'out_of_office_replacement_id'
  493. query += "#{attribute} NOT IN (?)"
  494. bind_params.push User.find(current_user_id).out_of_office_agent_of.pluck(:id)
  495. else
  496. query += "#{attribute} NOT IN (?)"
  497. bind_params.push current_user_id
  498. end
  499. elsif selector['pre_condition'] == 'current_user.organization_id'
  500. query += "#{attribute} NOT IN (?)"
  501. user = User.lookup(id: current_user_id)
  502. bind_params.push user.organization_id
  503. else
  504. # rubocop:disable Style/IfInsideElse
  505. if selector['value'].nil?
  506. query += "#{attribute} IS NOT NULL"
  507. else
  508. if attributes[1] == 'out_of_office_replacement_id'
  509. query += "#{attribute} NOT IN (?)"
  510. bind_params.push User.find(selector['value']).out_of_office_agent_of.pluck(:id)
  511. else
  512. query += "#{attribute} NOT IN (?)"
  513. bind_params.push selector['value']
  514. end
  515. end
  516. # rubocop:enable Style/IfInsideElse
  517. end
  518. elsif selector['operator'] == 'contains'
  519. query += "#{attribute} #{like} (?)"
  520. value = "%#{selector['value']}%"
  521. bind_params.push value
  522. elsif selector['operator'] == 'contains not'
  523. query += "#{attribute} NOT #{like} (?)"
  524. value = "%#{selector['value']}%"
  525. bind_params.push value
  526. elsif selector['operator'] == 'contains all' && attributes[0] == 'ticket' && attributes[1] == 'tags'
  527. query += "? = (
  528. SELECT
  529. COUNT(*)
  530. FROM
  531. tag_objects,
  532. tag_items,
  533. tags
  534. WHERE
  535. tickets.id = tags.o_id AND
  536. tag_objects.id = tags.tag_object_id AND
  537. tag_objects.name = 'Ticket' AND
  538. tag_items.id = tags.tag_item_id AND
  539. tag_items.name IN (?)
  540. )"
  541. bind_params.push selector['value'].count
  542. bind_params.push selector['value']
  543. elsif selector['operator'] == 'contains one' && attributes[0] == 'ticket' && attributes[1] == 'tags'
  544. query += "1 <= (
  545. SELECT
  546. COUNT(*)
  547. FROM
  548. tag_objects,
  549. tag_items,
  550. tags
  551. WHERE
  552. tickets.id = tags.o_id AND
  553. tag_objects.id = tags.tag_object_id AND
  554. tag_objects.name = 'Ticket' AND
  555. tag_items.id = tags.tag_item_id AND
  556. tag_items.name IN (?)
  557. )"
  558. bind_params.push selector['value']
  559. elsif selector['operator'] == 'contains all not' && attributes[0] == 'ticket' && attributes[1] == 'tags'
  560. query += "0 = (
  561. SELECT
  562. COUNT(*)
  563. FROM
  564. tag_objects,
  565. tag_items,
  566. tags
  567. WHERE
  568. tickets.id = tags.o_id AND
  569. tag_objects.id = tags.tag_object_id AND
  570. tag_objects.name = 'Ticket' AND
  571. tag_items.id = tags.tag_item_id AND
  572. tag_items.name IN (?)
  573. )"
  574. bind_params.push selector['value']
  575. elsif selector['operator'] == 'contains one not' && attributes[0] == 'ticket' && attributes[1] == 'tags'
  576. query += "(
  577. SELECT
  578. COUNT(*)
  579. FROM
  580. tag_objects,
  581. tag_items,
  582. tags
  583. WHERE
  584. tickets.id = tags.o_id AND
  585. tag_objects.id = tags.tag_object_id AND
  586. tag_objects.name = 'Ticket' AND
  587. tag_items.id = tags.tag_item_id AND
  588. tag_items.name IN (?)
  589. ) BETWEEN ? AND ?"
  590. bind_params.push selector['value']
  591. bind_params.push selector['value'].count - 1
  592. bind_params.push selector['value'].count
  593. elsif selector['operator'] == 'before (absolute)'
  594. query += "#{attribute} <= ?"
  595. bind_params.push selector['value']
  596. elsif selector['operator'] == 'after (absolute)'
  597. query += "#{attribute} >= ?"
  598. bind_params.push selector['value']
  599. elsif selector['operator'] == 'within last (relative)'
  600. query += "#{attribute} >= ?"
  601. time = nil
  602. if selector['range'] == 'minute'
  603. time = Time.zone.now - selector['value'].to_i.minutes
  604. elsif selector['range'] == 'hour'
  605. time = Time.zone.now - selector['value'].to_i.hours
  606. elsif selector['range'] == 'day'
  607. time = Time.zone.now - selector['value'].to_i.days
  608. elsif selector['range'] == 'month'
  609. time = Time.zone.now - selector['value'].to_i.months
  610. elsif selector['range'] == 'year'
  611. time = Time.zone.now - selector['value'].to_i.years
  612. else
  613. raise "Unknown selector attributes '#{selector.inspect}'"
  614. end
  615. bind_params.push time
  616. elsif selector['operator'] == 'within next (relative)'
  617. query += "#{attribute} <= ?"
  618. time = nil
  619. if selector['range'] == 'minute'
  620. time = Time.zone.now + selector['value'].to_i.minutes
  621. elsif selector['range'] == 'hour'
  622. time = Time.zone.now + selector['value'].to_i.hours
  623. elsif selector['range'] == 'day'
  624. time = Time.zone.now + selector['value'].to_i.days
  625. elsif selector['range'] == 'month'
  626. time = Time.zone.now + selector['value'].to_i.months
  627. elsif selector['range'] == 'year'
  628. time = Time.zone.now + selector['value'].to_i.years
  629. else
  630. raise "Unknown selector attributes '#{selector.inspect}'"
  631. end
  632. bind_params.push time
  633. elsif selector['operator'] == 'before (relative)'
  634. query += "#{attribute} <= ?"
  635. time = nil
  636. if selector['range'] == 'minute'
  637. time = Time.zone.now - selector['value'].to_i.minutes
  638. elsif selector['range'] == 'hour'
  639. time = Time.zone.now - selector['value'].to_i.hours
  640. elsif selector['range'] == 'day'
  641. time = Time.zone.now - selector['value'].to_i.days
  642. elsif selector['range'] == 'month'
  643. time = Time.zone.now - selector['value'].to_i.months
  644. elsif selector['range'] == 'year'
  645. time = Time.zone.now - selector['value'].to_i.years
  646. else
  647. raise "Unknown selector attributes '#{selector.inspect}'"
  648. end
  649. bind_params.push time
  650. elsif selector['operator'] == 'after (relative)'
  651. query += "#{attribute} >= ?"
  652. time = nil
  653. if selector['range'] == 'minute'
  654. time = Time.zone.now + selector['value'].to_i.minutes
  655. elsif selector['range'] == 'hour'
  656. time = Time.zone.now + selector['value'].to_i.hours
  657. elsif selector['range'] == 'day'
  658. time = Time.zone.now + selector['value'].to_i.days
  659. elsif selector['range'] == 'month'
  660. time = Time.zone.now + selector['value'].to_i.months
  661. elsif selector['range'] == 'year'
  662. time = Time.zone.now + selector['value'].to_i.years
  663. else
  664. raise "Unknown selector attributes '#{selector.inspect}'"
  665. end
  666. bind_params.push time
  667. else
  668. raise "Invalid operator '#{selector['operator']}' for '#{selector['value'].inspect}'"
  669. end
  670. end
  671. [query, bind_params, tables]
  672. end
  673. =begin
  674. perform changes on ticket
  675. ticket.perform_changes({}, 'trigger', item, current_user_id)
  676. =end
  677. def perform_changes(perform, perform_origin, item = nil, current_user_id = nil)
  678. logger.debug "Perform #{perform_origin} #{perform.inspect} on Ticket.find(#{id})"
  679. # if the configuration contains the deletion of the ticket then
  680. # we skip all other ticket changes because they does not matter
  681. if perform['ticket.action'].present? && perform['ticket.action']['value'] == 'delete'
  682. perform.each do |key, _value|
  683. (object_name, attribute) = key.split('.', 2)
  684. next if object_name != 'ticket'
  685. next if attribute == 'action'
  686. perform.delete(key)
  687. end
  688. end
  689. changed = false
  690. perform.each do |key, value|
  691. (object_name, attribute) = key.split('.', 2)
  692. raise "Unable to update object #{object_name}.#{attribute}, only can update tickets and send notifications!" if object_name != 'ticket' && object_name != 'notification'
  693. # send notification
  694. if object_name == 'notification'
  695. # value['recipient'] was a string in the past (single-select) so we convert it to array if needed
  696. value_recipient = value['recipient']
  697. if !value_recipient.is_a?(Array)
  698. value_recipient = [value_recipient]
  699. end
  700. recipients_raw = []
  701. value_recipient.each do |recipient|
  702. if recipient == 'article_last_sender'
  703. if item && item[:article_id]
  704. article = Ticket::Article.lookup(id: item[:article_id])
  705. if article.reply_to.present?
  706. recipients_raw.push(article.reply_to)
  707. elsif article.from.present?
  708. recipients_raw.push(article.from)
  709. elsif article.origin_by_id
  710. email = User.lookup(id: article.origin_by_id).email
  711. recipients_raw.push(email)
  712. elsif article.created_by_id
  713. email = User.lookup(id: article.created_by_id).email
  714. recipients_raw.push(email)
  715. end
  716. end
  717. elsif recipient == 'ticket_customer'
  718. email = User.lookup(id: customer_id).email
  719. recipients_raw.push(email)
  720. elsif recipient == 'ticket_owner'
  721. email = User.lookup(id: owner_id).email
  722. recipients_raw.push(email)
  723. elsif recipient == 'ticket_agents'
  724. User.group_access(group_id, 'full').sort_by(&:login).each do |user|
  725. recipients_raw.push(user.email)
  726. end
  727. else
  728. logger.error "Unknown email notification recipient '#{recipient}'"
  729. next
  730. end
  731. end
  732. recipients_checked = []
  733. recipients_raw.each do |recipient_email|
  734. skip_user = false
  735. users = User.where(email: recipient_email)
  736. users.each do |user|
  737. next if user.preferences[:mail_delivery_failed] != true
  738. next if !user.preferences[:mail_delivery_failed_data]
  739. till_blocked = ((user.preferences[:mail_delivery_failed_data] - Time.zone.now - 60.days) / 60 / 60 / 24).round
  740. next if till_blocked.positive?
  741. logger.info "Send no trigger based notification to #{recipient_email} because email is marked as mail_delivery_failed for #{till_blocked} days"
  742. skip_user = true
  743. break
  744. end
  745. next if skip_user
  746. # send notifications only to email adresses
  747. next if !recipient_email
  748. next if recipient_email !~ /@/
  749. # check if address is valid
  750. begin
  751. recipient_email = Mail::Address.new(recipient_email).address
  752. rescue
  753. next # because unable to parse
  754. end
  755. # do not sent notifications to this recipients
  756. send_no_auto_response_reg_exp = Setting.get('send_no_auto_response_reg_exp')
  757. begin
  758. next if recipient_email =~ /#{send_no_auto_response_reg_exp}/i
  759. rescue => e
  760. logger.error "ERROR: Invalid regex '#{send_no_auto_response_reg_exp}' in setting send_no_auto_response_reg_exp"
  761. logger.error 'ERROR: ' + e.inspect
  762. next if recipient_email =~ /(mailer-daemon|postmaster|abuse|root|noreply|noreply.+?|no-reply|no-reply.+?)@.+?/i
  763. end
  764. # check if notification should be send because of customer emails
  765. if item && item[:article_id]
  766. article = Ticket::Article.lookup(id: item[:article_id])
  767. if article && article.preferences['is-auto-response'] == true && article.from && article.from =~ /#{Regexp.quote(recipient_email)}/i
  768. logger.info "Send no trigger based notification to #{recipient_email} because of auto response tagged incoming email"
  769. next
  770. end
  771. end
  772. # loop protection / check if maximal count of trigger mail has reached
  773. map = {
  774. 10 => 10,
  775. 30 => 15,
  776. 60 => 25,
  777. 180 => 50,
  778. 600 => 100,
  779. }
  780. skip = false
  781. map.each do |minutes, count|
  782. already_sent = Ticket::Article.where(
  783. ticket_id: id,
  784. sender: Ticket::Article::Sender.find_by(name: 'System'),
  785. type: Ticket::Article::Type.find_by(name: 'email'),
  786. ).where('ticket_articles.created_at > ? AND ticket_articles.to LIKE ?', Time.zone.now - minutes.minutes, "%#{recipient_email.strip}%").count
  787. next if already_sent < count
  788. logger.info "Send no trigger based notification to #{recipient_email} because already sent #{count} for this ticket within last #{minutes} minutes (loop protection)"
  789. skip = true
  790. break
  791. end
  792. next if skip
  793. map = {
  794. 10 => 30,
  795. 30 => 60,
  796. 60 => 120,
  797. 180 => 240,
  798. 600 => 360,
  799. }
  800. skip = false
  801. map.each do |minutes, count|
  802. already_sent = Ticket::Article.where(
  803. sender: Ticket::Article::Sender.find_by(name: 'System'),
  804. type: Ticket::Article::Type.find_by(name: 'email'),
  805. ).where('ticket_articles.created_at > ? AND ticket_articles.to LIKE ?', Time.zone.now - minutes.minutes, "%#{recipient_email.strip}%").count
  806. next if already_sent < count
  807. logger.info "Send no trigger based notification to #{recipient_email} because already sent #{count} in total within last #{minutes} minutes (loop protection)"
  808. skip = true
  809. break
  810. end
  811. next if skip
  812. email = recipient_email.downcase.strip
  813. next if recipients_checked.include?(email)
  814. recipients_checked.push(email)
  815. end
  816. next if recipients_checked.blank?
  817. recipient_string = recipients_checked.join(', ')
  818. group = self.group
  819. next if !group
  820. email_address = group.email_address
  821. if !email_address
  822. logger.info "Unable to send trigger based notification to #{recipient_string} because no email address is set for group '#{group.name}'"
  823. next
  824. end
  825. if !email_address.channel_id
  826. logger.info "Unable to send trigger based notification to #{recipient_string} because no channel is set for email address '#{email_address.email}' (id: #{email_address.id})"
  827. next
  828. end
  829. objects = {
  830. ticket: self,
  831. article: articles.last,
  832. }
  833. # get subject
  834. subject = NotificationFactory::Mailer.template(
  835. templateInline: value['subject'],
  836. locale: 'en-en',
  837. objects: objects,
  838. quote: false,
  839. )
  840. subject = subject_build(subject)
  841. body = NotificationFactory::Mailer.template(
  842. templateInline: value['body'],
  843. locale: 'en-en',
  844. objects: objects,
  845. quote: true,
  846. )
  847. Ticket::Article.create(
  848. ticket_id: id,
  849. to: recipient_string,
  850. subject: subject,
  851. content_type: 'text/html',
  852. body: body,
  853. internal: false,
  854. sender: Ticket::Article::Sender.find_by(name: 'System'),
  855. type: Ticket::Article::Type.find_by(name: 'email'),
  856. preferences: {
  857. perform_origin: perform_origin,
  858. },
  859. updated_by_id: 1,
  860. created_by_id: 1,
  861. )
  862. next
  863. end
  864. # update tags
  865. if key == 'ticket.tags'
  866. next if value['value'].blank?
  867. tags = value['value'].split(/,/)
  868. if value['operator'] == 'add'
  869. tags.each do |tag|
  870. tag_add(tag)
  871. end
  872. elsif value['operator'] == 'remove'
  873. tags.each do |tag|
  874. tag_remove(tag)
  875. end
  876. else
  877. logger.error "Unknown #{attribute} operator #{value['operator']}"
  878. end
  879. next
  880. end
  881. # delete ticket
  882. if key == 'ticket.action'
  883. next if value['value'].blank?
  884. next if value['value'] != 'delete'
  885. destroy
  886. next
  887. end
  888. # lookup pre_condition
  889. if value['pre_condition']
  890. if value['pre_condition'] =~ /^not_set/
  891. value['value'] = 1
  892. elsif value['pre_condition'] =~ /^current_user\./
  893. raise 'Unable to use current_user, got no current_user_id for ticket.perform_changes' if !current_user_id
  894. value['value'] = current_user_id
  895. end
  896. end
  897. # update ticket
  898. next if self[attribute].to_s == value['value'].to_s
  899. changed = true
  900. self[attribute] = value['value']
  901. logger.debug "set #{object_name}.#{attribute} = #{value['value'].inspect}"
  902. end
  903. return if !changed
  904. save
  905. end
  906. =begin
  907. get all email references headers of a ticket, to exclude some, parse it as array into method
  908. references = ticket.get_references
  909. result
  910. ['message-id-1234', 'message-id-5678']
  911. ignore references header(s)
  912. references = ticket.get_references(['message-id-5678'])
  913. result
  914. ['message-id-1234']
  915. =end
  916. def get_references(ignore = [])
  917. references = []
  918. Ticket::Article.select('in_reply_to, message_id').where(ticket_id: id).each do |article|
  919. if !article.in_reply_to.empty?
  920. references.push article.in_reply_to
  921. end
  922. next if !article.message_id
  923. next if article.message_id.empty?
  924. references.push article.message_id
  925. end
  926. ignore.each do |item|
  927. references.delete(item)
  928. end
  929. references
  930. end
  931. =begin
  932. get all articles of a ticket in correct order (overwrite active record default method)
  933. artilces = ticket.articles
  934. result
  935. [article1, articl2]
  936. =end
  937. def articles
  938. Ticket::Article.where(ticket_id: id).order(:created_at, :id)
  939. end
  940. def history_get(fulldata = false)
  941. list = History.list(self.class.name, self['id'], 'Ticket::Article')
  942. return list if !fulldata
  943. # get related objects
  944. assets = {}
  945. list.each do |item|
  946. record = Kernel.const_get(item['object']).find(item['o_id'])
  947. assets = record.assets(assets)
  948. if item['related_object']
  949. record = Kernel.const_get(item['related_object']).find( item['related_o_id'])
  950. assets = record.assets(assets)
  951. end
  952. end
  953. {
  954. history: list,
  955. assets: assets,
  956. }
  957. end
  958. private
  959. def check_generate
  960. return true if number
  961. self.number = Ticket::Number.generate
  962. true
  963. end
  964. def check_title
  965. return true if !title
  966. title.gsub!(/\s|\t|\r/, ' ')
  967. true
  968. end
  969. def check_defaults
  970. if !owner_id
  971. self.owner_id = 1
  972. end
  973. return true if !customer_id
  974. customer = User.find_by(id: customer_id)
  975. return true if !customer
  976. return true if organization_id == customer.organization_id
  977. self.organization_id = customer.organization_id
  978. true
  979. end
  980. def reset_pending_time
  981. # ignore if no state has changed
  982. return true if !changes_to_save['state_id']
  983. # ignore if new state is blank and
  984. # let handle ActiveRecord the error
  985. return if state_id.blank?
  986. # check if new state isn't pending*
  987. current_state = Ticket::State.lookup(id: state_id)
  988. current_state_type = Ticket::StateType.lookup(id: current_state.state_type_id)
  989. # in case, set pending_time to nil
  990. return true if current_state_type.name =~ /^pending/i
  991. self.pending_time = nil
  992. true
  993. end
  994. def check_escalation_update
  995. escalation_calculation
  996. true
  997. end
  998. def set_default_state
  999. return true if state_id
  1000. default_ticket_state = Ticket::State.find_by(default_create: true)
  1001. return true if !default_ticket_state
  1002. self.state_id = default_ticket_state.id
  1003. true
  1004. end
  1005. def set_default_priority
  1006. return true if priority_id
  1007. default_ticket_priority = Ticket::Priority.find_by(default_create: true)
  1008. return true if !default_ticket_priority
  1009. self.priority_id = default_ticket_priority.id
  1010. true
  1011. end
  1012. end