Class: Query::OrderQuery

Inherits:
BaseQuery
  • Object
show all
Defined in:
app/services/query/order_query.rb

Overview

Service object: order query.

Instance Method Summary collapse

Constructor Details

#initialize(relation = nil) ⇒ OrderQuery

Returns a new instance of OrderQuery.



4
5
6
 
# File 'app/services/query/order_query.rb', line 4

def initialize(relation = nil)
  super(relation || Order.all)
end

Instance Method Details

#abandoned_carts(reminded = false) ⇒ Object 



56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
 
# File 'app/services/query/order_query.rb', line 56

def abandoned_carts(reminded = false)
  joins = <<-SQL.squish
    inner join parties customers on customers.id = orders.customer_id
  SQL

  min_last_visit_query = <<-SQL.squish
    (select time from visit_events inner join visits on visits.id = visit_events.visit_id where visits.user_id = customers.id order by time desc limit 1) < ?
  SQL

  max_last_visit_query = <<-SQL.squish
    (select time from visit_events inner join visits on visits.id = visit_events.visit_id where visits.user_id = customers.id order by time desc limit 1) > ?
  SQL

  has_email_query = <<-SQL.squish
    (
      exists(select 1 from contact_points where party_id = customers.id and category = 'email')
      or
      exists(select 1 from contact_points inner join parties on parties.id = contact_points.party_id where parties.customer_id = customers.id and category = 'email')
    )
  SQL

  has_items_query = <<-SQL.squish
    (exists(select 1 from line_items where resource_type = 'Order' and resource_id = orders.id and line_items.tax_class = 'g'))
  SQL

  no_recent_orders_query = <<-SQL.squish
    NOT EXISTS (SELECT 1 FROM orders past_orders WHERE past_orders.customer_id = customers.id AND past_orders.state NOT IN ('cart', 'cancelled')  AND past_orders.created_at >= ?)
  SQL

  reminder_state = reminded ? 'first_reminder' : 'reminder_not_sent'
  order_last_udpated = reminded ? 2.days.ago : 1.hour.ago
  last_visit = reminded ? 2.days.ago : 1.hour.ago

  @relation.joins(joins)
           .where(state: 'cart')
           .where(abandoned_cart_reminder: reminder_state)
           .where(has_items_query)
           .where(orders: { updated_at: ...order_last_udpated })
           .where(min_last_visit_query, last_visit)
           .where(max_last_visit_query, 1.week.ago)
           .where(has_email_query)
           .where(no_recent_orders_query, 30.days.ago)
end

#average_invoiced_revenue ⇒ Object 



8
9
10
 
# File 'app/services/query/order_query.rb', line 8

def average_invoiced_revenue
  @relation.invoiced.average(:line_total).round(2)
end

#invoiced_count ⇒ Object 



23
24
25
 
# File 'app/services/query/order_query.rb', line 23

def invoiced_count
  @relation.invoiced.count
end

#invoiced_count_between(start_range, end_range) ⇒ Object 



33
34
35
 
# File 'app/services/query/order_query.rb', line 33

def invoiced_count_between(start_range, end_range)
  invoiced_records_between(start_range, end_range).count
end

#invoiced_count_since(start_range) ⇒ Object 



28
29
30
 
# File 'app/services/query/order_query.rb', line 28

def invoiced_count_since(start_range)
  invoiced_count_between(start_range, Date.current)
end

#invoiced_records_between(start_range, end_range = Date.current) ⇒ Object 



48
49
50
 
# File 'app/services/query/order_query.rb', line 48

def invoiced_records_between(start_range, end_range = Date.current)
  @relation.invoiced.where(orders: { shipped_date: start_range..end_range })
end

#invoiced_revenue ⇒ Object 



18
19
20
 
# File 'app/services/query/order_query.rb', line 18

def invoiced_revenue
  revenue @relation.invoiced
end

#invoiced_revenue_between(start_range, end_range) ⇒ Object 



43
44
45
 
# File 'app/services/query/order_query.rb', line 43

def invoiced_revenue_between(start_range, end_range)
  revenue invoiced_records_between(start_range, end_range)
end

#invoiced_revenue_since(start_range) ⇒ Object 



38
39
40
 
# File 'app/services/query/order_query.rb', line 38

def invoiced_revenue_since(start_range)
  revenue invoiced_records_between(start_range, Date.current)
end

#median_invoiced_order_revenue ⇒ Object 



13
14
15
 
# File 'app/services/query/order_query.rb', line 13

def median_invoiced_order_revenue
  median_record.try(:line_total) || 0.00
end

#revenue(rel) ⇒ Object 



52
53
54
 
# File 'app/services/query/order_query.rb', line 52

def revenue(rel)
  rel.sum(:line_total)
end