3
4
5
6
7
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
63
64
65
66
67
68
69
70
71
72
73
74
|
# File 'app/controllers/crm/reports/source_report_controller.rb', line 3
def show
@start_date = Date.strptime(params[:start_date], '%Y-%m-%d') rescue nil
@end_date = Date.strptime(params[:end_date], '%Y-%m-%d') rescue nil
@start_date ||= Date.current.beginning_of_month
@end_date ||= Date.current.end_of_month
@source_id = params[:source_id] if params[:source_id].present?
@parent_source = Source.find(@source_id) if @source_id
sources = Source.where(:parent_id => @source_id).select("name, full_name, id").order("name")
@sources = {}
sources.each do |s|
descendants_ids = s.self_and_descendants_ids
revenue = Invoice.joins(:customer).
sales_orders.
where("parties.source_id" => descendants_ids).
where("invoices.shipped_date >= ? and invoices.shipped_date < ?", @start_date, @end_date).
sum("invoices.revenue_consolidated")
customers = Customer.non_guests.where(:source_id => descendants_ids).where(Customer[:created_at].gteq(@start_date)).where(Customer[:created_at].lteq(@end_date)).count("parties.id")
opportunities = Opportunity.where(source_id: descendants_ids).
where(Opportunity[:created_at].gteq(@start_date)).
where(Opportunity[:created_at].lteq(@end_date)).
group(:state).count(:id)
opps_created = opportunities.sum{|k,v| v } || 0
opps_won = opportunities['won'] || 0
opps_conversion_ratio = opps_created.positive? ? ((opps_won.to_f / opps_created) * 100).round(1) : 0.0
rooms = RoomConfiguration.joins(:opportunity).
where(opportunities: { source_id: descendants_ids }).
where(RoomConfiguration[:created_at].gteq(@start_date)).
where(RoomConfiguration[:created_at].lteq(@end_date)).
count("room_configurations.id")
orders = Order.invoiced.
where(Order[:created_at].gteq(@start_date)).
where(Order[:created_at].lteq(@end_date)).
where(source_id: descendants_ids).
count("orders.id")
if revenue.positive? || customers.positive? || opps_created.positive? || opps_won.positive? || opps_conversion_ratio.positive? || orders.positive? || rooms.positive?
@sources[s] = {
:revenue => revenue,
:customers => customers,
:opps_created => opps_created,
:opps_won => opps_won,
:opps_conversion_ratio => opps_conversion_ratio,
:orders => orders,
:rooms => rooms,
:descendants_ids => descendants_ids
}
end
end
respond_to do |format|
format.html
format.csv do
csv = CSV.generate do |csv|
csv << ["source_name", "source_id", "customers_all_time", "customers", "opps_created","opps_won","opps_conversion_ratio","rooms","orders","revenue"]
@sources.each do |s,v|
csv << [s.name, s.id, v[:customers_all_time], v[:customers], v[:opps_created], v[:opps_won], v[:opps_conversion_ratio], v[:rooms], v[:orders], v[:revenue]]
end
end
send_data csv,
:type => 'text/csv; charset=iso-8859-1; header=present',
:disposition => "attachment; filename=source_report.csv"
end
end
end
|