Class: SpreeCmCommissioner::AccommodationQuery

Inherits:
Object
  • Object
show all
Includes:
ActiveModel::Validations
Defined in:
app/queries/spree_cm_commissioner/accommodation_query.rb

Constant Summary collapse

MAX_QUERY_DAYS = 
31

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(from_date:, to_date:, province_id: nil, vendor_id: nil) ⇒ AccommodationQuery

Returns a new instance of AccommodationQuery.



11
12
13
14
15
16
 
# File 'app/queries/spree_cm_commissioner/accommodation_query.rb', line 11

def initialize(from_date:, to_date:, province_id: nil, vendor_id: nil)
  @from_date = from_date
  @to_date = to_date
  @province_id = province_id
  @vendor_id = vendor_id
end

Instance Attribute Details

#from_date ⇒ Object (readonly)

Returns the value of attribute from_date.



5
6
7
 
# File 'app/queries/spree_cm_commissioner/accommodation_query.rb', line 5

def from_date
  @from_date
end

#province_id ⇒ Object (readonly)

Returns the value of attribute province_id.



5
6
7
 
# File 'app/queries/spree_cm_commissioner/accommodation_query.rb', line 5

def province_id
  @province_id
end

#to_date ⇒ Object (readonly)

Returns the value of attribute to_date.



5
6
7
 
# File 'app/queries/spree_cm_commissioner/accommodation_query.rb', line 5

def to_date
  @to_date
end

#vendor_id ⇒ Object (readonly)

Returns the value of attribute vendor_id.



5
6
7
 
# File 'app/queries/spree_cm_commissioner/accommodation_query.rb', line 5

def vendor_id
  @vendor_id
end

Instance Method Details

#booked_vendors ⇒ Object 



23
24
25
26
27
28
29
30
31
32
33
 
# File 'app/queries/spree_cm_commissioner/accommodation_query.rb', line 23

def booked_vendors
  # Query to get all vendors group by day that have booking overlaps with from_date and start_date
  # Results: vendor_id, day, total_booking
  scope = Spree::Vendor.select('spree_vendors.id AS vendor_id, date_list.day AS day, sum(line_items.quantity) AS total_booking')
                       .joins('INNER JOIN spree_line_items line_items ON line_items.vendor_id = spree_vendors.id')
                       .joins("INNER JOIN (#{date_list_sql}) date_list ON day >= line_items.from_date AND day <= line_items.to_date")
                       .where(['(line_items.from_date <= day AND day <= line_items.to_date)'])

  scope = apply_filter(scope)
  scope.group('spree_vendors.id, day')
end

#date_list_sql ⇒ Object 



18
19
20
21
 
# File 'app/queries/spree_cm_commissioner/accommodation_query.rb', line 18

def date_list_sql
  end_date = to_date - 1.day
  "SELECT day FROM generate_series('#{from_date}'::date, '#{end_date}', '1 day') AS day"
end

#max_booked_vendor_sql ⇒ Object 



35
36
37
 
# File 'app/queries/spree_cm_commissioner/accommodation_query.rb', line 35

def max_booked_vendor_sql
  "SELECT DISTINCT ON (vendor_id) vendor_id, day, total_booking FROM (#{booked_vendors.to_sql}) AS booked_vendors ORDER BY vendor_id, total_booking DESC"
end

#with_available_inventory ⇒ Object 



39
40
41
42
43
44
 
# File 'app/queries/spree_cm_commissioner/accommodation_query.rb', line 39

def with_available_inventory
  # Get vendors by province that include: day, total_booking and remaining
  scope = Spree::Vendor.select('spree_vendors.*, vendor_id, day, COALESCE(total_booking, 0) AS total_booking, COALESCE((spree_vendors.total_inventory - max_booked_vendors.total_booking), spree_vendors.total_inventory) AS remaining')
                       .joins("LEFT JOIN (#{max_booked_vendor_sql}) max_booked_vendors ON max_booked_vendors.vendor_id = spree_vendors.id")
  apply_filter(scope)
end