Class: Crm::Reports::ReportsController

Inherits:
CrmController
  • Object
show all
Defined in:
app/controllers/crm/reports/reports_controller.rb

Overview

Controller: reports.

Instance Method Summary collapse

Instance Method Details

#ar_report ⇒ Object

AR detail report, filterable by store/doc type/order/PO/customer, CSV-exportable.



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
 
# File 'app/controllers/crm/reports/reports_controller.rb', line 57

def ar_report
  # Expose for the form re-render. Single int store_id selects one store; otherwise fall back to the default pair.
  @store_id = params[:store_id].presence&.to_i
  company_filter = @store_id ? [@store_id] : [1, 2]
  @doc_type = params[:document_reference].to_s
  @order_reference = params[:order_number].to_s
  @po_number = params[:po_number].to_s
  @customer_full_name = params[:customer_full_name].to_s
  @customer_id_number = params[:customer_id_number].presence&.to_i

  scope = ViewArDetail.where(company: company_filter)
  scope = scope.where(doc_type: @doc_type)         if @doc_type.present?
  scope = scope.where(order_number: @order_reference) if @order_reference.present?
  scope = scope.where(po_number: @po_number)       if @po_number.present?
  if @customer_full_name.present?
    sanitized = ActiveRecord::Base.sanitize_sql_like(@customer_full_name)
    scope = scope.where(
      "EXISTS (SELECT 1 FROM parties p WHERE p.id = view_ar_details.customer_id AND p.full_name LIKE ?)",
      "%#{sanitized}%"
    )
  end
  if @customer_id_number
    scope = scope.where(
      "customer_id IN (SELECT p1.id FROM parties p1 WHERE p1.parent_id = :cid OR p1.id = :cid)",
      cid: @customer_id_number
    )
  end
  @result_set = scope.select("po_number,company,customer_id,(select p.full_name from parties p where p.id = view_ar_details.customer_id) as customer_name,doc_type,document_number,invoice_id,document_date,due_date,gl_date,order_type,order_number,original_amount,open_amount,currency")

  respond_to do |format|
    format.html
    format.csv do
      csv_string = CSV.generate do |csv|
        original_amount_total = 0
        open_amount_total = 0
        # header row
        csv << ["Company", "Customer_id", "Name", "Doc Type", "Doc Number", "INV", "Doc Date", "Due Date", "GL Date", "Order Type", "Order Number", "Original Amount", "Open Amount", "Currency"]
        # data rows
        @result_set.each do |s|
          original_amount_total += s.original_amount
          open_amount_total += s.open_amount
          csv << [s.company, s.customer_id, s.customer_name, s.doc_type, s.document_number, s.invoice_id, s.document_date, s.due_date, s.gl_date, s.order_type, s.order_number, s.original_amount, s.open_amount, s.currency]
        end
        csv << []
        csv << ["", "", "", "", "", "", "", "", "", "", "TOTALS", number_to_currency(original_amount_total), number_to_currency(open_amount_total), ""]
      end
      # send it to the browsah
      send_data csv_string,
              type: 'text/csv; charset=iso-8859-1; header=present',
              disposition: "attachment; filename=ar_detail_report.csv"
    end
  end
end

#ar_summary_report ⇒ Object

AR aging summary report by billing address, CSV-exportable.



112
113
114
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
 
# File 'app/controllers/crm/reports/reports_controller.rb', line 112

def ar_summary_report
  @end_date = params[:end_date].blank? ? Date.current.to_date : params[:end_date].to_date
  @store_id = params[:store_id].presence&.to_i
  company_filter = @store_id ? [@store_id] : [1, 2]
  @pos_neg = case params[:balance_type]
             when '-' then '<'
             when '+' then '>'
             else '<>'
             end
  @billing_address_id = params[:billing_address_id].presence&.to_i
  @customer_id = params[:customer_id_number].presence&.to_i

  join_sql = ActiveRecord::Base.sanitize_sql_array([
    "inner join (select customer_id, sum(open_amount) as total_amount, \
      case when document_date >= current_date - interval '30 days' and document_date <= current_date then sum(open_amount) ELSE 0 END as current, \
      case when document_date >= current_date - interval '60 days' and document_date < current_date - interval '30 days' then sum(open_amount) ELSE 0 END as over_30, \
      case when document_date >= current_date - interval '90 days' and document_date < current_date - interval '60 days' then sum(open_amount) ELSE 0 END as over_60, \
      case when document_date < current_date - interval '90 days' then sum(open_amount) ELSE 0 END as over_90 \
      from view_ar_details where document_date <= ? group by document_date,customer_id) a on a.customer_id = parties.id",
    @end_date
  ])

  scope = Party.joins(join_sql)
  scope = scope.where("parties.billing_address_id = ?", @billing_address_id) if @billing_address_id
  if @customer_id
    scope = scope.where(
      "(SELECT p1.id FROM parties p1 WHERE p1.id = (SELECT a.party_id FROM addresses a WHERE a.id = parties.billing_address_id)) = ?",
      @customer_id
    )
  end
  # @pos_neg is whitelisted to one of '<', '>', '<>' above.
  scope = scope.where("total_amount #{@pos_neg} 0")
  scope = scope.where(
    "(SELECT (SELECT c.store_id FROM catalogs c WHERE c.id = p1.catalog_id) FROM parties p1 WHERE p1.id = (SELECT a.party_id FROM addresses a WHERE a.id = parties.billing_address_id)) IN (?)",
    company_filter
  )
  @result_set = scope.select("(select p1.id from parties p1 where p1.id = (select a.party_id from addresses a where a.id = parties.billing_address_id)) as cust_id,(select a.state_code from addresses a where a.id = parties.billing_address_id) as state_code, \
                      (select p1.full_name from parties p1 where p1.id = (select a.party_id from addresses a where a.id = parties.billing_address_id)) as customer_name, \
                      (select (select (select s.country_iso3 from stores s where s.id = c.store_id) from catalogs c where c.id = p1.catalog_id) from parties p1 where p1.id = (select a.party_id from addresses a where a.id = parties.billing_address_id)) as store_id,(select p1.catalog_id from parties p1 where p1.id = (select a.party_id from addresses a where a.id = parties.billing_address_id)) as catalog_id, \
                      parties.billing_address_id,sum(a.total_amount) as total_amount,sum(a.current) as current_amount,sum(a.over_30) as over_30,sum(a.over_60) as over_60,sum(a.over_90) as over_90")
                     .group("parties.billing_address_id")

  respond_to do |format|
    format.html
    format.csv do
      csv_string = CSV.generate do |csv|
        total_amount = 0
        current = 0
        over_30 = 0
        over_60 = 0
        over_90 = 0
        # header row
        csv << ["Store_id", "Customer_id", "Name", "Billing Address ID", "State", "Total Amt", "Current", "30", "60", "90"]
        # data rows
        @result_set.sort_by { |a| a.total_amount.to_i }.each do |r|
          total_amount += r.total_amount.to_i
          current += r.current.to_i
          over_30 += r.over_30.to_i
          over_60 += r.over_60.to_i
          over_90 += r.over_90.to_i

          csv << [r.store_id, r.cust_id, r.customer_name, r.billing_address_id, r.state_code, number_to_currency(r.total_amount), number_to_currency(r.current), number_to_currency(r.over_30), number_to_currency(r.over_60),
                  number_to_currency(r.over_90)]
        end
        csv << []
        csv << ["", "", "", "", "TOTALS", number_to_currency(total_amount), number_to_currency(current), number_to_currency(over_30), number_to_currency(over_60), number_to_currency(over_90)]
      end
      # send it to the browsah
      send_data csv_string,
              type: 'text/csv; charset=iso-8859-1; header=present',
              disposition: "attachment; filename=ar_summary_report.csv"
    end
  end
end

#export_to_csv(header_titles, data, title) ⇒ void

This method returns an undefined value.

Parameters:

  • header_titles (Array<String>) —

    CSV header row values

  • data (Array<Array>) —

    CSV data rows

  • title (String) —

    the download filename, without extension



191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
 
# File 'app/controllers/crm/reports/reports_controller.rb', line 191

def export_to_csv(header_titles, data, title)
  respond_to do |format|
    format.html
    format.csv do
      csv_string = CSV.generate do |csv|
        # header row
        csv << header_titles

        # data rows
        data.each do |d|
          csv << d
        end
      end

      # send it to the browsah
      send_data csv_string,
                type: 'text/csv; charset=iso-8859-1; header=present',
                disposition: "attachment; filename=#{title}.csv"
    end
  end
end

#gross_sales_legacy ⇒ Object

Legacy gross-sales-by-rep report, grouped by day and currency.



23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
 
# File 'app/controllers/crm/reports/reports_controller.rb', line 23

def gross_sales_legacy
  @year = (params[:year] || Date.current.year).to_i
  @month = (params[:month] || Date.current.month).to_i
  start_date = Date.new(@year, @month, 1)
  end_date = start_date + 1.month
  @days = (1..Time.days_in_month(@month, @year)).to_a.reverse!

  if (@month == Date.current.month) && (@year == Date.current.year)
    day_today = Date.current.day
    @days.delete_if { |d| d > day_today }
  end

  @orders = ViewSalesGrossByRep
            .where(shipped_date: start_date...end_date)
            .order(:currency, :shipped_date, :sales_rep)
            .group_by(&:currency)

  return unless @year < 2009

  flash[:error] = "No report is available before 2009"
  redirect_to action: :index
end

#index ⇒ Object

Renders the reports index.



7
 
# File 'app/controllers/crm/reports/reports_controller.rb', line 7

def index; end

#invoice_matching ⇒ Object

Discontinued.

Raises:

  • (RuntimeError) —

    always



49
50
51
 
# File 'app/controllers/crm/reports/reports_controller.rb', line 49

def invoice_matching
  raise "Discontinued"
end

#parse_date_or_default(date_string, default = nil) ⇒ Date, ...

Returns the parsed date, or default if unparseable.

Parameters:

  • date_string (String) —

    the date string to parse, in %Y-%m-%d format

  • default (Object, nil) (defaults to: nil) —

    the value to return if parsing fails

Returns:

  • (Date, Object, nil) —

    the parsed date, or default if unparseable



288
289
290
291
292
 
# File 'app/controllers/crm/reports/reports_controller.rb', line 288

def parse_date_or_default(date_string, default = nil)
  Date.strptime(date_string, '%Y-%m-%d')
rescue StandardError
  default
end

#pto_report ⇒ Object

Renders the PTO report.



54
 
# File 'app/controllers/crm/reports/reports_controller.rb', line 54

def pto_report; end

#quote_conversion ⇒ Object

Obsolete.

Raises:

  • (RuntimeError) —

    always



224
225
226
227
228
229
230
 
# File 'app/controllers/crm/reports/reports_controller.rb', line 224

def quote_conversion
  @start_date = params[:start_date].presence || (Date.current.beginning_of_month - 3.months)
  @end_date = params[:end_date].presence || (Date.current.end_of_month - 1.month)
  @start_date_last = params[:start_date].blank? ? (Date.current.beginning_of_month - 3.months) - 1.year : params[:start_date].to_date - 1.year
  @end_date_last = params[:end_date].blank? ? (Date.current.end_of_month - 1.month) - 1.year : params[:end_date].to_date - 1.year
  raise "Obsolete Report"
end

#rma_report ⇒ Object

RMA report by customer/store, CSV-exportable.



233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
 
# File 'app/controllers/crm/reports/reports_controller.rb', line 233

def rma_report
  @start_date = (params[:start_date].presence || (Date.current - 1.month)).to_date
  @end_date = (params[:end_date].presence || Date.current).to_date
  @customer_id = params[:customer_id].presence&.to_i
  @company_id = params[:store_id].presence&.to_i

  scope = Rma.select("c.short_name, rmas.customer_id,(select rep.full_name from parties rep where rep.id in (select p.primary_sales_rep_id from parties p where p.id = rmas.customer_id)) as primary_rep")
             .select("(select p.full_name from parties p where p.id = rmas.customer_id) as full_name")
             .select("CASE WHEN rmas.state in ('requested','awaiting_return') THEN (select sum(o.total) from orders o where o.id = li.resource_id) ELSE 0 END as rma_pending")
             .select("CASE WHEN rmas.state in ('returned') THEN (select sum(o.total) from orders o where o.id = li.resource_id) ELSE 0 END as rma_returned")
             .select("CASE WHEN rmas.state in ('credited','offset') THEN (select sum(o.total) from orders o where o.id = li.resource_id) ELSE 0 END as rma_credited")
             .select("array(select distinct(r1.returned_reason) from rma_items r1 where r1.rma_id = rmas.id ) as return_code, date(rmas.created_at) as rma_date")
             .joins("inner join (select rma_items.id,rma_items.rma_id,rma_items.state,rma_items.returned_reason from rma_items where rma_items.state <> 'voided') ri on ri.rma_id = rmas.id inner join (select companies.short_name,companies.id from companies) c on c.id = rmas.company_id inner join (select distinct(line_items.resource_id),line_items.resource_type,line_items.credit_rma_item_id from line_items where line_items.resource_type = 'Order') li on li.credit_rma_item_id = ri.id")
             .where(rmas: { created_at: @start_date..@end_date })
  scope = scope.where(rmas: { customer_id: @customer_id }) if @customer_id
  scope = scope.where(rmas: { company_id: @company_id })   if @company_id
  @results = scope.group("rmas.state,(select sum(o.total) from orders o where o.id = li.resource_id),rmas.customer_id,date(rmas.created_at),c.short_name,array(select distinct(r1.returned_reason) from rma_items r1 where r1.rma_id = rmas.id )")
                  .order("rmas.customer_id,date(rmas.created_at)")

  respond_to do |format|
    format.html
    format.csv do
      csv_string = CSV.generate do |csv|
        # header row
        csv << ["Store", "Customer_id", "Name", "Primary Rep", "Return Reason(s)", "Received", "Pending", "Credited", "RMA Created Date"]
        total_credited = 0
        total_received = 0
        total_pending = 0
        # data rows
        @results.group_by(&:rma_date).each do |rma_date, d|
          total_credited += d.sum { |t| t.rma_credited.to_f }
          total_received += d.sum { |t| t.rma_returned.to_f }
          total_pending += d.sum { |t| t.rma_pending.to_f }
          d.group_by(&:customer_id).each do |customer_id, r|
            csv << [r.map(&:short_name).uniq, customer_id, r.map(&:full_name).uniq, r.map(&:primary_rep).uniq, r.map(&:return_code).uniq, number_to_currency(r.sum { |t| t.rma_returned.to_f }), number_to_currency(r.sum do |t|
              t.rma_pending.to_f
            end), number_to_currency(r.sum do |t|
                    t.rma_credited.to_f
                  end), rma_date]
          end
        end
        csv << ["", "", "", "", "TOTAL", number_to_currency(total_received), number_to_currency(total_pending), number_to_currency(total_credited)]
      end

      # send it to the browsah
      send_data csv_string,
                type: 'text/csv; charset=iso-8859-1; header=present',
                disposition: "attachment; filename=rma_report.csv"
    end
  end
end

#show ⇒ Object

Renders a legacy report identified by params, resolved via Report.build.



11
12
13
14
15
16
17
18
19
20
 
# File 'app/controllers/crm/reports/reports_controller.rb', line 11

def show
  @report = Report.build(params)
  @report.perform if params[:commit].present?
  respond_to do |format|
    format.html { render action: "#{@report.report_key}/report", layout: 'crm/crm_report' }
  end
rescue NameError => e
  flash[:error] = "Report does not exist. #{e}"
  redirect_back_or_to dashboard_path
end

#week_names(week_num, week_year) ⇒ String

Returns a "Week of: MM/DD/YY" label.

Parameters:

  • week_num (Integer) —

    the ISO week number

  • week_year (Integer) —

    the year the week falls in

Returns:

  • (String) —

    a "Week of: MM/DD/YY" label



216
217
218
219
 
# File 'app/controllers/crm/reports/reports_controller.rb', line 216

def week_names(week_num, week_year)
  week_start = Date.commercial(week_year, week_num, 1)
  "Week of: #{week_start.strftime("%m/%d/%y")}"
end