Class: Query::OrderQuery

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

Instance Method Summary collapse

Constructor Details

#initialize(relation = nil) ⇒ OrderQuery

Returns a new instance of OrderQuery.



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

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

Instance Method Details

#abandoned_carts(reminded = false) ⇒ Object 



55
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
 
# File 'app/services/query/order_query.rb', line 55

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

  min_last_visit_query = <<-SQL
    (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
    (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
    (
      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
    (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
    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 



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

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

#invoiced_count ⇒ Object 



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

def invoiced_count
	@relation.invoiced.count
end

#invoiced_count_between(start_range, end_range) ⇒ Object 



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

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

#invoiced_count_since(start_range) ⇒ Object 



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

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

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



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

def invoiced_records_between(start_range, end_range = Date.current)
	@relation.invoiced.where("orders.shipped_date >= ? and orders.shipped_date <= ?", start_range, end_range)
end

#invoiced_revenue ⇒ Object 



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

def invoiced_revenue
	revenue @relation.invoiced
end

#invoiced_revenue_between(start_range, end_range) ⇒ Object 



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

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

#invoiced_revenue_since(start_range) ⇒ Object 



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

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

#median_invoiced_order_revenue ⇒ Object 



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

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

#revenue(rel) ⇒ Object 



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

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