Class: Report::ShippingRates::ShippingRates

Inherits:
Object
  • Object
show all
Defined in:
app/services/report/shipping_rates/shipping_rates.rb

Defined Under Namespace

Classes: Result

Class Method Summary collapse

Class Method Details

.result(options = {}) ⇒ Object 



8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
# File 'app/services/report/shipping_rates/shipping_rates.rb', line 8

def self.result(options = {})
  report_type = options[:report_type]
  company_id = options[:company_id].first.to_i
  company_code = company_id == 1 ? 'USA' : 'CAN'
  weight = options[:weight].present? ? BigDecimal(options[:weight]) : BigDecimal("0")
  state_ids = options[:state_ids].present? ? options[:state_ids] : State.where(country_iso3: company_code).where.not(code: ['AK','HI']).where.not(region: 'US - Territories').map{ |s| s.code }
  end_date = 1.day.ago.to_date
  start_date = end_date.years_ago(3)
  ptg_rate_added = 0.15
  amazon_shipping_rates = shipping_rates_for_amazon(company_code,state_ids,start_date,end_date,weight) if report_type == 'by_pound'
  etailers_shipping_rates = shipping_rates_for_etailers(company_code,state_ids,start_date,end_date,ptg_rate_added) if report_type == 'by_range'
  states_hash = State.where(code: state_ids)
  states = states_hash.map{ |s| s.name }.compact.uniq.sort
  regions = options[:state_ids].present? ? states_hash.map{ |s| s.region.split(' - ').second }.compact.uniq.sort : ['CONTINENTAL']

  Result.new(success: true, report_type: report_type, 
                            amazon_shipping_rates: amazon_shipping_rates, 
                            etailers_shipping_rates: etailers_shipping_rates, 
                            states: states,
                            regions: regions)
end

.shipping_rates_for_amazon(country, state_ids, start_date, end_date, weight) ⇒ Object 



30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
 
# File 'app/services/report/shipping_rates/shipping_rates.rb', line 30

def self.shipping_rates_for_amazon(country,state_ids,start_date,end_date,weight)
  states_sql = " and s.code in (#{state_ids.map{ |s| "'" + s + "'" }.join(',')})"

  sql = <<-SQL
    with tw_data as (
        select sh.order_id,sum(greatest((sh.weight + 1).round,(((sh.length + 1) * (sh.width + 1) * (sh.height + 1)) / 139 ).round)) as total_weight
        from shipments sh
        inner join deliveries d on sh.delivery_id = d.id
        inner join shipping_options so on d.shipping_option_id = so.id
        inner join orders o on sh.order_id = o.id
        inner join addresses ad on d.destination_address_id = ad.id
        inner join states s on ad.state_code = s.code
        where o.state = 'invoiced'
        and sh.state = 'label_complete'
        and s.country_iso3 = '#{country}'
        #{states_sql}
        and (so.name ilike '%ground%' or so.name ilike '%fedex_twoday%' or so.name ilike '%threedayselect%')
        and o.order_type in ('SO','TO','MO')
        and (d.bill_shipping_to_customer = false or d.bill_shipping_to_customer is null)
        and timezone('America/Chicago', timestamptz(sh.created_at))::date between '#{start_date}' and '#{end_date}'
        group by sh.order_id
    
    ), shipping_data as (
        select case when so.name ilike '%ground%' then 'ground'
                    when (so.name ilike '%fedex_twoday%' or so.name ilike '%threedayselect%') then 'second_day' end as shipping_type,
                ad.country_iso3,ad.state_code,s.region,so.carrier,so.description,sh.order_id,o.order_type,
                o.actual_shipping_cost as shipping_cost,sh.length,sh.width,sh.height,
                (sh.weight + 1).round as boxed_shipping_weight,(((sh.length + 1) * (sh.width + 1) * (sh.height + 1)) / 139 ).round as dimensional_weight,(sh.length + ((sh.width + sh.height) * 2)).round as girth,
                greatest((sh.weight + 1).round,(((sh.length + 1) * (sh.width + 1) * (sh.height + 1)) / 139 ).round) as billable_weight,
                (case when (((sh.length + 1) + (((sh.width + 1) + (sh.height + 1)) * 2)).round > 130 or sh.length > 96) then true else false end) as oversize
        from shipments sh
        inner join deliveries d on sh.delivery_id = d.id
        inner join shipping_options so on d.shipping_option_id = so.id
        inner join orders o on sh.order_id = o.id
        inner join addresses ad on d.destination_address_id = ad.id
        inner join states s on ad.state_code = s.code
        where o.state = 'invoiced'
        and sh.state = 'label_complete'
        and s.country_iso3 = '#{country}'
        #{states_sql}
        and (so.name ilike '%ground%' or so.name ilike '%fedex_twoday%' or so.name ilike '%threedayselect%')
        and o.order_type in ('SO','TO','MO')
        and (d.bill_shipping_to_customer = false or d.bill_shipping_to_customer is null)
        and timezone('America/Chicago', timestamptz(sh.created_at))::date between '#{start_date}' and '#{end_date}'
    
    ), shipping_cost_by_box_data as (
        select shipping_type,country_iso3,state_code,region,shipping_cost, round((case when total_weight = 0 then 0 else billable_weight/total_weight end) * shipping_cost, 2) as shipping_cost_by_box,billable_weight,oversize
        from shipping_data sd
        inner join tw_data tw on sd.order_id = tw.order_id
    
    ), result as (
        select case when shipping_type = 'ground' then (percentile_disc(0.5) WITHIN GROUP(ORDER BY shipping_cost_by_box) - regr_intercept(shipping_cost_by_box,billable_weight)) end as shipping_cost_calculated,
              case when shipping_type = 'ground' then regr_slope(shipping_cost_by_box,billable_weight) end as cost_per_lb_calculated,
              case when shipping_type = 'ground' then percentile_disc(0.5) WITHIN GROUP(ORDER BY shipping_cost_by_box) end as shipping_cost_recommended,
              case when shipping_type = 'ground' then (percentile_disc(0.5) WITHIN GROUP(ORDER BY shipping_cost_by_box) * regr_slope(shipping_cost_by_box,billable_weight)) / (percentile_disc(0.5) WITHIN GROUP(ORDER BY shipping_cost_by_box) - regr_intercept(shipping_cost_by_box,billable_weight)) end as cost_per_lb_recommended
        from shipping_cost_by_box_data
        where oversize = false
        and billable_weight > #{weight}
        group by shipping_type
        union all
        select case when shipping_type = 'ground' then avg(shipping_cost_by_box) end as shipping_cost_calculated,
              case when shipping_type = 'ground' then avg(shipping_cost_by_box) / max(billable_weight) end as cost_per_lb_calculated,
              case when shipping_type = 'ground' then percentile_disc(0.5) WITHIN GROUP(ORDER BY shipping_cost_by_box) end as shipping_cost_recommended,
              case when shipping_type = 'ground' then (percentile_disc(0.5) WITHIN GROUP(ORDER BY shipping_cost_by_box) * regr_slope(shipping_cost_by_box,billable_weight)) / (percentile_disc(0.5) WITHIN GROUP(ORDER BY shipping_cost_by_box) - regr_intercept(shipping_cost_by_box,billable_weight)) end as cost_per_lb_recommended
        from shipping_cost_by_box_data
        where oversize = true
        group by shipping_type
        union all
        select case when shipping_type = 'second_day' then (percentile_disc(0.4) WITHIN GROUP(ORDER BY shipping_cost_by_box) - regr_intercept(shipping_cost_by_box,billable_weight)) end as shipping_cost_calculated,
              case when shipping_type = 'second_day' then regr_slope(shipping_cost_by_box,billable_weight) end as cost_per_lb_calculated,
              case when shipping_type = 'second_day' then percentile_disc(0.5) WITHIN GROUP(ORDER BY shipping_cost_by_box) end as shipping_cost_recommended,
              case when shipping_type = 'second_day' then (percentile_disc(0.5) WITHIN GROUP(ORDER BY shipping_cost_by_box) * regr_slope(shipping_cost_by_box,billable_weight)) / (percentile_disc(0.5) WITHIN GROUP(ORDER BY shipping_cost_by_box) - regr_intercept(shipping_cost_by_box,billable_weight)) end as cost_per_lb_recommended
        from shipping_cost_by_box_data
        where oversize = false
        and billable_weight > #{weight}
        group by shipping_type
        union all
        select 0,0,0,0
    )
    select * from result where shipping_cost_calculated is not null;
  SQL

  result = ActiveRecord::Base.connection.execute(sql).to_a.map{ |r| r.symbolize_keys }
end

.shipping_rates_for_etailers(country, state_ids, start_date, end_date, rate) ⇒ Object 



115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
 
# File 'app/services/report/shipping_rates/shipping_rates.rb', line 115

def self.shipping_rates_for_etailers(country,state_ids,start_date,end_date,rate)
  states_sql = " and s.code in (#{state_ids.map{ |s| "'" + s + "'" }.join(',')})"

  sql = <<-SQL
    with data as (
        select so.carrier,so.description,sh.order_id,o.order_type,li.price,o.actual_shipping_cost,case when li.price < o.actual_shipping_cost then o.actual_shipping_cost else li.price end as shipping_cost,
              ad.country_iso3,ad.state_code,round(sum(sh.weight::numeric),0) as base_weight,sum(sh.width * sh.length * sh.height) as volume
        from shipments sh
        inner join deliveries d on sh.delivery_id = d.id
        inner join shipping_options so on d.shipping_option_id = so.id
        inner join orders o on sh.order_id = o.id
        inner join invoices i on o.id = i.order_id
        inner join line_items li on resource_type = 'Order' and resource_id = sh.order_id and li.tax_class = 'shp'
        inner join addresses ad on d.destination_address_id = ad.id
        inner join states s on ad.state_code = s.code
        where o.state = 'invoiced'
        and sh.state = 'label_complete'
        and s.country_iso3 = '#{country}'
        #{states_sql}
        and so.name ilike '%ground%'
        and o.order_type in ('SO','TO','MO')
        and (d.bill_shipping_to_customer = false or d.bill_shipping_to_customer is null)
        and timezone('America/Chicago', timestamptz(sh.created_at))::date between '#{start_date}' and '#{end_date}'
        group by so.carrier,so.description,sh.order_id,o.order_type,li.price,o.shipping_cost,o.actual_shipping_cost,ad.country_iso3,ad.state_code
        having sum(case when sh.length >= 48 or sh.width >= 48 or sh.height >= 48 then 1 else 0 end) = 0

    ), data_detail as (
        select base_weight,avg(shipping_cost) as shipping_cost,avg(volume) as volume
        from data 
        group by base_weight
        order by base_weight

    ), data_breakdown as (
        select
        case when base_weight between 0 and 5 then 1
              when base_weight between 6 and 10 then 2
              when base_weight between 11 and 15 then 3
              when base_weight between 16 and 20 then 4
              when base_weight between 21 and 25 then 5
              when base_weight between 26 and 30 then 6
              when base_weight between 31 and 40 then 7
              when base_weight between 41 and 50 then 8
              when base_weight between 51 and 60 then 9
              when base_weight between 61 and 80 then 10
              when base_weight between 81 and 100 then 11
              when base_weight between 101 and 150 then 12
              when base_weight between 151 and 200 then 13
              when base_weight between 201 and 300 then 14
              when base_weight between 301 and 400 then 15
              else null end weight_breakdown,
        case when base_weight between 0 and 5 then 5
              when base_weight between 6 and 10 then 10
              when base_weight between 11 and 15 then 15
              when base_weight between 16 and 20 then 20
              when base_weight between 21 and 25 then 25
              when base_weight between 26 and 30 then 30
              when base_weight between 31 and 40 then 40
              when base_weight between 41 and 50 then 50
              when base_weight between 51 and 60 then 60
              when base_weight between 61 and 80 then 80
              when base_weight between 81 and 100 then 100
              when base_weight between 101 and 150 then 150
              when base_weight between 151 and 200 then 200
              when base_weight between 201 and 300 then 300
              when base_weight between 301 and 400 then 400
              else null end weight_param,*
        from data

    ), tb_datail as (
        select distinct weight_breakdown as wb,weight_param,
        case when weight_breakdown = 1 then '0 lb - 5 lb'
          when weight_breakdown = 2 then '5.1 lb - 10 lb'
          when weight_breakdown = 3 then '10.1 lb - 15 lb'
          when weight_breakdown = 4 then '15.1 lb - 20 lb'
          when weight_breakdown = 5 then '20.1 lb - 25 lb'
          when weight_breakdown = 6 then '25.1 lb - 30 lb'
          when weight_breakdown = 7 then '30.1 lb - 40 lb'
          when weight_breakdown = 8 then '40.1 lb - 50 lb'
          when weight_breakdown = 9 then '50.1 lb - 60 lb'
          when weight_breakdown = 10 then '60.1 lb - 80 lb'
          when weight_breakdown = 11 then '80.1 lb - 100 lb'
          when weight_breakdown = 12 then '100.1 lb - 150 lb'
          when weight_breakdown = 13 then '150.1 lb - 200 lb'
          when weight_breakdown = 14 then '200.1 lb - 300 lb'
          when weight_breakdown = 15 then '300.1 lb - 400 lb'
          else null end as wb_detail
        from data_breakdown

    ), weight_cost as (
        select weight_breakdown,avg(shipping_cost) as avg_shp_cost,count(*) as num_samples,sum(shipping_cost) as total_shipping_cost
        from data_breakdown
        group by weight_breakdown

    ), slope_intercept as (
        select
        regr_slope(shipping_cost,base_weight) as slope_shipcost,regr_intercept(shipping_cost,base_weight) as intercept_shipcost,
        regr_slope(volume,base_weight) as slope_volume,regr_intercept(volume,base_weight) as intercept_volume
        from data_detail

    ), trend as (
        select weight_param,wb_detail,((weight_param * slope_shipcost) + intercept_shipcost) as shp_cost_trend,wb,((weight_param * slope_volume) + intercept_volume) as shp_volumen
        from tb_datail,slope_intercept
        where wb is not null
    )
    select weight_param,wb_detail,coalesce(avg_shp_cost,0) as avg_shp_cost,shp_cost_trend,(shp_cost_trend + (shp_cost_trend * #{rate})) as shp_cost_recommended--,shp_volumen
    from trend t
    left join weight_cost wc on t.wb = wc.weight_breakdown
    order by weight_param;
  SQL

  result = ActiveRecord::Base.connection.execute(sql).to_a.map{ |r| r.symbolize_keys }
end