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 



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

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 



103
104
105
106
107
108
109
110
111
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
 
# File 'app/controllers/crm/reports/reports_controller.rb', line 103

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) ⇒ Object 



178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
 
# File 'app/controllers/crm/reports/reports_controller.rb', line 178

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 



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

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 



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

def index; end

#invoice_matching ⇒ Object 



43
44
45
 
# File 'app/controllers/crm/reports/reports_controller.rb', line 43

def invoice_matching
  raise "Discontinued"
end

#parse_date_or_default(date_string, default = nil) ⇒ Object 



265
266
267
268
269
 
# File 'app/controllers/crm/reports/reports_controller.rb', line 265

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

#pto_report ⇒ Object 



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

def pto_report; end

#quote_conversion ⇒ Object 



205
206
207
208
209
210
211
 
# File 'app/controllers/crm/reports/reports_controller.rb', line 205

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 



213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
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
 
# File 'app/controllers/crm/reports/reports_controller.rb', line 213

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

require_role "employee"



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

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) ⇒ Object 



200
201
202
203
 
# File 'app/controllers/crm/reports/reports_controller.rb', line 200

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