Class: Report::ItemMovedToScrap::ItemMovedToScrapReport

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

Defined Under Namespace

Classes: Result

Class Method Summary collapse

Class Method Details

.result_report(options = {}) ⇒ Object 



8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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
 
# File 'app/services/report/item_moved_to_scrap/item_moved_to_scrap_report.rb', line 8

def self.result_report(options = {})
  start_time = options[:period1_gteq]
  end_time = options[:period1_lteq]
  options[:sku_ids].shift(1)
  sku_ids = options[:sku_ids].map { |e| e}.join(",")
  groupings = options[:groupings].map(&:presence).compact
  grouping_ids = []
  if groupings.present?
    grouping_ids = ProductLine.where(
      "TRIM(SPLIT_PART(lineage_expanded, '>', 1)) IN (?)",
      groupings
    ).pluck(:id).join(",")
  end
  options[:supplier_ids].shift(1)
  supplier_ids = options[:supplier_ids].map { |e| e}.join(",")
  options[:company_ids].shift(1) if options[:company_ids][0] == ""
  company_ids = options[:company_ids][0]

  where_sku = " and le.item_id in (#{sku_ids})" if options[:sku_ids] != []
  where_grouping = " and pl.id in (#{grouping_ids})" if grouping_ids.present?
  where_supplier = " and sp.supplier_id in (#{supplier_ids})" if options[:supplier_ids] != []
  where_company = " and le.store_id = #{company_ids}" if options[:company_ids].any?
  
  sql = "select distinct trim(split_part(lineage_expanded,'>',1)) as grouping,
        case when trim(split_part(lineage_expanded,'>',2)) = '' then 'N/A' else trim(split_part(lineage_expanded,'>',2)) end as sub_grouping,
        pt.full_name as supplier,sku,
        case when le.store_id = 1 then 'WarmlyYours USA' else 'WarmlyYours CAN' end as store,
        ri.returned_reason,r.rma_number,sum(quantity) as quantity
        from item_ledger_entries le
        inner join items it on le.item_id = it.id
        inner join product_lines pl on it.primary_product_line_id = pl.id
        inner join supplier_items sp on it.id = sp.item_id
        inner join parties pt on sp.supplier_id = pt.id
        inner join rma_items ri on le.rma_item_id = ri.id
        inner join rmas r on ri.rma_id = r.id
        where location = 'SCRAP'
        and quantity > 0
        and category = 'RMA_RECEIPT'
        #{where_sku}
        #{where_grouping}
        #{where_supplier}
        #{where_company}
        and le.gl_date between '#{start_time}' and '#{end_time}'
        group by trim(split_part(lineage_expanded,'>',1)),case when trim(split_part(lineage_expanded,'>',2)) = '' then 'N/A' else trim(split_part(lineage_expanded,'>',2)) end,pt.full_name,sku,store_id,ri.returned_reason,r.rma_number
        order by 1,2"
  
  result = ActiveRecord::Base.connection.execute(sql)
  result_data = []
  result.to_a.each do |r|
    imsr = Report::ItemMovedToScrap::ItemMovedScrapResult.new(r)
    result_data << imsr
  end
  
  Result.new(success: true, data: result_data)
end