# encoding:utf-8 class ReportController < BaseController before_filter :authenticate_admin_user! def index item = Project.select("sum(total_crowd_funding)/100 as s").where("state='success'") @total_investment = item[0].blank? ? 0 : item[0].s @user_amount = User.count() item = ReportWeixinUser.where("appid='superd5c'").last @superd5c_weixin_user_amount = item.blank? ? 0 : item.amount item = ReportWeixinUser.where("appid='d5ctdyh'").last @d5ctdyh_weixin_user_amount = item.blank? ? 0 : item.amount item = ReportWeixinUser.where("appid='diwutegongdui'").last @tegongdui_weixin_user_amount = item.blank? ? 0 : item.amount @now = Time.now @user_total_counts = User.count() year() month() day() # if params[:t]!="123" # render :text => "sorry" # return # end render :layout => false end #按年统计 def year current_month_first_day = Time.new(@now.year, @now.month, @now.day) current_month_first_month = Time.new(@now.year, @now.month) current_month = Time.new(@now.year, @now.month) first_month = current_month - 5.months @liucun = WxUser.where("subscribe_time>=? and subscribe=1",current_month_first_month.to_i).count() @total_sub = WxUser.where("subscribe_time>=?",current_month_first_month.to_i).count() @ms = [] @months = [] @month_users = {} @month_valid_users = {} @month_projects = {} @month_invest_users = {} #月投资 @month_balance_money = {} #月充值 @month_take_cash = {} #月提现 @month_money = {} @month_valid_users = {} #新投资人 6.times do |i| m = (first_month + (i <2 ? i.month : i.months)).month @ms << m @months << "#{m}月" @month_users[m] = 0 @month_valid_users[m] = 0 @month_projects[m] = 0 @month_invest_users[m] = 0 @month_take_cash[m] = 0 @month_balance_money[m] = 0 @month_money[m] = 0 p m end #用户增长 sql = "select date_format(date_add(created_at,interval 8 hour),'%Y%m')ym ,count(*) as c,date_format(date_add(created_at,interval 8 hour), '%m')m from d5c_users where created_at < '#{current_month+1.month}' and created_at >='#{first_month}' group by ym order by ym asc " User.find_by_sql(sql).each do |u| @month_users[u.m.to_i] = u.c.to_i end # 新投资人增长 sql = "select date_format(from_unixtime(pj.min_paied_at),'%Y%m')ym, date_format(from_unixtime(pj.min_paied_at),'%m')m, count(*) as c from (select j.user_id, min(j.paied_at) as min_paied_at from d5c_project_joins j, d5c_projects p where j.project_id = p.id and j.state = 1 and j.is_deleted = 0 and j.is_refunded = 0 and p.ptype = 'stock' group by user_id order by min_paied_at desc) pj where pj.min_paied_at >= #{first_month.to_i} and pj.min_paied_at < #{(current_month+1.month).to_i} group by ym order by ym desc" ProjectJoin.find_by_sql(sql).each do |u| @month_valid_users[u.m.to_i] = u.c.to_i end #粉丝净增 @month_wx_users_fuwuhao = get_report_weixin_users_by_period("superd5c", first_month, current_month, false) @month_wx_users_dingyuehao = get_report_weixin_users_by_period("d5ctdyh", first_month, current_month, false) @month_wx_users_tegongdui = get_report_weixin_users_by_period("diwutegongdui", first_month, current_month, false) #粉丝新增 @month_wx_users_fuwuhao_newly = get_report_weixin_users_by_period("superd5c", first_month, current_month, true) @month_wx_users_dingyuehao_newly = get_report_weixin_users_by_period("d5ctdyh", first_month, current_month, true) @month_wx_users_tegongdui_newly = get_report_weixin_users_by_period("diwutegongdui", first_month, current_month, true) #当月新增 current_month_to_i = (@now.month).to_i @current_month_fuwuhao_increase_count = @month_wx_users_fuwuhao_newly[current_month_to_i].to_i @current_month_dingyuehao_increase_count = @month_wx_users_dingyuehao_newly[current_month_to_i].to_i @current_month_tegongdui_increase_count = @month_wx_users_tegongdui_newly[current_month_to_i].to_i @current_month_user_increase_count = @month_users[current_month_to_i].to_i @total_increase_user_count = @current_month_fuwuhao_increase_count + @current_month_dingyuehao_increase_count + @current_month_tegongdui_increase_count + @current_month_user_increase_count #上线项目 # Project.select("date_format(invest_start_at, '%m') month, date_format(invest_start_at, '%Y%m') ym, count(*) as c") # .where("invest_start_at < ? and invest_start_at > ? and state='success'", current_month_first_day+1.month, first_month).group("ym").order("ym").each do |u| # @month_projects[u.month.to_i] = u.c.to_i # end # # 投资人数 # ProjectJoin.select("date_format(created_at, '%m') month, count(distinct user_id) as c") # .where("paied_at < ? and paied_at > ? and state=1 and is_refunded=0 and is_deleted=0", (current_month_first_day+1.month).to_i, first_month.to_i).group("month").order("month").each do |u| # @month_invest_users[u.month.to_i] = u.c.to_i # end # 充值金额 sql = "select date_format(from_unixtime(paied_at),'%Y%m')ym, date_format(from_unixtime(paied_at),'%m')m,sum(total_price) as c from d5c_balance_orders where state = 1 and paied_at >= #{first_month.to_i} and paied_at < #{(current_month+1.month).to_i} group by ym order by ym desc" BalanceOrder.find_by_sql(sql).each do |u| @month_balance_money[u.m.to_i] = u.c.to_f/100 end # # 提现订单 # TakeCashOrder.select("date_format(created_at, '%m') month, date_format(created_at, '%Y%m') ym, sum(count) as c") # .where("paied_at < ? and paied_at > ? and pay_state = 2 and paied_at >0", (current_month_first_day+1.month).to_i, first_month.to_i).group("ym").order("ym").each do |u| # @month_take_cash[u.month.to_i] = u.c.to_f/100 # end # DATE_FORMAT(FROM_UNIXTIME(paied_at),'%Y%m%d')PYMD #融资总额情况 sql = "select date_format(from_unixtime(j.paied_at),'%Y%m')ym, " + "date_format(from_unixtime(j.paied_at),'%m')m, sum(j.total_price) as c " + "from d5c_project_joins j inner join d5c_projects " + "on j.state=1 and j.is_refunded=0 and j.is_deleted=0 " + "and j.paied_at >= #{first_month.to_i} and j.paied_at < #{(current_month+1.month).to_i} "+ "and d5c_projects.ptype='stock' and j.project_id=d5c_projects.id " + "group by ym order by ym desc" ProjectJoin.find_by_sql(sql).each do |u| @month_money[u.m.to_i] = u.c.to_f/100 end # #数据太难看 # if params[:full].to_i != 1 # i = 11-@now.month.to_i # @months = @months[i..-1] # @month_users.each do |k,v| # if not @months.include?("#{k}月") # @month_users.delete(k) # end # end # @month_valid_users.each do |k,v| # if not @months.include?("#{k}月") # @month_valid_users.delete(k) # end # end # @month_projects.each do |k,v| # if not @months.include?("#{k}月") # @month_projects.delete(k) # end # end # @month_money.each do |k,v| # if not @months.include?("#{k}月") # @month_money.delete(k) # end # end # @month_wx_users_fuwuhao = @month_wx_users_fuwuhao[i..-1] # @month_wx_users_dingyuehao = @month_wx_users_dingyuehao[i..-1] # @month_wx_users_fuwuhao_newly = @month_wx_users_fuwuhao_newly[i..-1] # @month_wx_users_dingyuehao_newly = @month_wx_users_dingyuehao_newly[i..-1] # @month_wx_users_tegongdui_newly = @month_wx_users_tegongdui_newly[i..-1] # end end #按月统计 def month @ds = [] @days = [] @day_users = {} @day_valid_users = {} @day_projects = {} @day_invest_users = {} @day_balance_money = {} @day_take_cash = {} @day_money = {} @now.day.to_i.times do |j| i = j + 1 @ds << i @days << "#{i}号" @day_users[i] = 0 @day_valid_users[i] = 0 @day_invest_users[i] = 0 @day_balance_money[i] = 0 @day_take_cash[i] = 0 @day_money[i] = 0 end # #注册用户增长 # # User.select("date_format(created_at, '%m') month, date_format(created_at, '%Y%m') ym, count(*) as c") # # .where("created_at < ? and created_at > ?", current_month_first_day+1.month, first_month).group("ym").order("ym").each do |u| # # @month_users[u.month.to_i] = u.c.to_i # # end # User.select("date_format(created_at, '%d') day, count(*) as c") # .where("date_format(created_at, '%Y%c') = ?", "#{@now.year}#{@now.month}").group("day").order("day").each do |u| # @day_users[u.day.to_i] = u.c.to_i # end # # 新投资人增长 # valid_user_ids = ProjectJoin.where("state=1 and is_refunded =0 and is_deleted=0").collect {|e| e.user_id} # User.select("date_format(created_at, '%d') day, count(*) as c") # .where("date_format(created_at, '%Y%c') = ? and id in (?)", "#{@now.year}#{@now.month}",valid_user_ids).group("day").order("day").each do |u| # @day_valid_users[u.day.to_i] = u.c.to_i # end # #统计粉丝 # @day_wx_users_fuwuhao = get_report_weixin_users_monthly("superd5c",@ds) # @day_wx_users_dingyuehao = get_report_weixin_users_monthly("d5ctdyh",@ds) # #粉丝新增长 # @day_wx_users_fuwuhao_newly = get_report_weixin_users_monthly("superd5c", @ds, true) # @day_wx_users_dingyuehao_newly = get_report_weixin_users_monthly("d5ctdyh", @ds, true) # @day_wx_users_tegongdui_newly = get_report_weixin_users_monthly("diwutegongdui", @ds, true) # #上线项目 # # Project.select("date_format(invest_start_at, '%m') month, date_format(invest_start_at, '%Y%m') ym, count(*) as c") # # .where("invest_start_at < ? and invest_start_at > ? and state='success'", current_month_first_day+1.month, first_month).group("ym").order("ym").each do |u| # # @month_projects[u.month.to_i] = u.c.to_i # # end # # 投资人数distinct user_id # ProjectJoin.select("date_format(created_at, '%d') day, count(*) as c") # .where("date_format(FROM_UNIXTIME(paied_at), '%Y%c') = ? and state=1 and is_refunded=0 and is_deleted=0", "#{@now.year}#{@now.month}").group("day").order("day").each do |u| # @day_invest_users[u.day.to_i] = u.c.to_i # end # # # 充值金额 # # BalanceOrder.select("date_format(created_at, '%d') day, sum(total_price) as c") # # .where("date_format(created_at, '%Y%c') = ? and state = ?", "#{@now.year}#{@now.month}",true).group("day").order("day").each do |u| # # @day_balance_money[u.day.to_i] = u.c.to_f/100 # # end # # # 提现订单 # # TakeCashOrder.select("date_format(created_at, '%d') day, sum(count) as c") # # .where("date_format(created_at, '%Y%c') = ? and pay_state = 2 and paied_at > 0", "#{@now.year}#{@now.month}" ).group("day").order("day").each do |u| # # @month_take_cash[u.day.to_i] = u.c.to_f/100 # # end # #融资总额情况 # ProjectJoin.select("date_format(created_at, '%d') day, sum(total_price) as c") # .where("date_format(FROM_UNIXTIME(paied_at), '%Y%c') = ? and state=1 and is_refunded=0 and is_deleted=0", "#{@now.year}#{@now.month}").group("day").order("day").each do |u| # @day_money[u.day.to_i] = u.c.to_f/100 # end end # 按日统计 def day first = @now - 1.day # first_day = Time.new(@now.year, @now.month, @now.day-1) first_day = Time.new(first.year, first.month, first.day) end_day = Time.new(@now.year, @now.month, @now.day) first_hour = first_day + 16.hours end_hour = end_day +16.hours @hs = [] @hours = [] @hour_users = {} @hour_valid_users = {} @hour_projects = {} @hour_invest_users = {} @hour_balance_money = {} @hour_take_cash = {} @hour_money = {} t = 0 if @now.hour !=24 t = @now.hour.to_i+1 else t = 1 end t.times do |i| @hs << i @hours << "#{i}时" @hour_users[i] = 0 @hour_valid_users[i] = 0 @hour_invest_users[i] = 0 @hour_balance_money[i] = 0 @hour_take_cash[i] = 0 @hour_money[i] = 0 end # # @report_users_daily_data = ReportUser.where("year=? and month=? and day=?",year, month, day).order("hour").collect do |u| # # u.added_count # # end # #注册用户增长 # User.select("date_format(created_at, '%H') hour, count(*) as c") # .where("UNIX_TIMESTAMP(created_at) >= ? and UNIX_TIMESTAMP(created_at) < ?", (first_hour).to_i,(end_hour).to_i).group("hour").order("hour").each do |u| # t = u.hour.to_i # t = t>16 ? (t-16):(t+8) # @hour_users[t] = u.c.to_i # end # #新投资人增长 # valid_user_ids = ProjectJoin.where("state=1 and is_refunded =0 and is_deleted=0").collect {|e| e.user_id} # User.select("date_format(created_at, '%H') hour, count(*) as c") # .where("UNIX_TIMESTAMP(created_at) >= ? and UNIX_TIMESTAMP(created_at) < ? and id in (?)", (first_hour).to_i,(end_hour).to_i,valid_user_ids).group("hour").order("hour").each do |u| # t = u.hour.to_i # t = t>16 ? (t-16):(t+8) # @hour_valid_users[t] = u.c.to_i # end # # #投资人数（计算所有用户） # # ProjectJoin.select("date_format(created_at, '%H') hour, count(distinct user_id) as c") # # .where("date_format(FROM_UNIXTIME(paied_at), '%Y%c') = ? and date_format(FROM_UNIXTIME(paied_at), '%e') = ? and state=1 and is_refunded=0 and is_deleted=0", "#{@now.year}#{@now.month}","#{@now.day}").group("hour").order("hour").each do |u| # # @hour_invest_users[u.hour.to_i] = u.c.to_i # # end # ProjectJoin.select("date_format(created_at, '%H') hour, count(distinct user_id) as c") # .where("paied_at >= ? and paied_at < ? and state=1 and is_refunded=0 and is_deleted=0", (first_hour).to_i, (end_hour).to_i ).group("hour").order("hour").each do |u| # t = u.hour.to_i # t = t>16 ? (t-16):(t+8) # @hour_invest_users[t] = u.c.to_i # end # #充值金额 # BalanceOrder.select("date_format(created_at, '%H') hour, sum(total_price) as c") # .where("UNIX_TIMESTAMP(created_at) >= ? and UNIX_TIMESTAMP(created_at) < ? and state = 1 and paied_at > 0", (first_hour).to_i,(end_hour).to_i).group("hour").order("hour").each do |u| # t = u.hour.to_i # t = t>16 ? (t-16):(t+8) # @hour_balance_money[t] = u.c.to_f/100 # end # #提现金额 # # TakeCashOrder.select("date_format(created_at, '%H') hour, sum(count) as c") # # .where("date_format(created_at, '%Y%c') = ? and date_format(created_at, '%e') = ? and pay_state=2 and paied_at > 0", "201612","6").group("hour").order("hour").each do |u| # # @hour_take_cash[u.hour.to_i] = u.c.to_f/100 # # end # TakeCashOrder.select("date_format(created_at, '%H') hour, sum(count) as c") # .where("UNIX_TIMESTAMP(created_at) >= ? and UNIX_TIMESTAMP(created_at) < ? and pay_state=2 and paied_at > 0", (first_hour).to_i,(end_hour).to_i).group("hour").order("hour").each do |u| # t = u.hour.to_i # t = t>16 ? (t-16):(t+8) # @hour_take_cash[t] = u.c.to_f/100 # end # # #投资金额 # ProjectJoin.select("date_format(created_at, '%H') hour, sum(total_price) as c") # .where("paied_at >= ? and paied_at < ? and state=1 and is_refunded=0 and is_deleted=0", (first_hour).to_i,(end_hour).to_i).group("hour").order("hour").each do |u| # t = u.hour.to_i # t = t>16 ? (t-16):(t+8) # @hour_money[t] = u.c.to_f/100 # end end # 注册、关注统计 def user ##注：页面参数为time,输入格式"2016-12-12"或"2016-12" @now = Time.now @this_day = Time.new(@now.year, @now.month, @now.day) @last_day = @this_day - 1.day @check_day = Time.new(@last_day.year, @last_day.month, @last_day.day) #默认展示上一天 @check_month = Time.new(@this_day.year,@this_day.month) #这一个月 if !params[:time].blank? s = params[:time].split("-") if s.length < 3 @check_day = Time.new(@last_day.year, @last_day.month, @last_day.day) else @check_day = Time.new(s[0],s[1],s[2]) end if s.length > 1 @check_month = Time.new(s[0],s[1]) end end #总用户 --user @total_user = User.count() #当月用户增长月查询 --user @monthly_users = User.where("? <= created_at and created_at r, :count => u.c, :channel_qrcode_id => u.channel_qrcode_id} end #当月各渠道关注--wx_user @m_sub_groups = [] @m_sub_cq_ids = [] m_sub_user.select("channel_qrcode_id, count(*) as c").group("channel_qrcode_id").order("c desc").each do |u| r = getChannelQrcodeRemark(u.channel_qrcode_id) net_increase = 0 #净增 unsub = m_unsub_user.where("channel_qrcode_id = ?", u.channel_qrcode_id) net_increase = u.c - unsub.count() @m_sub_cq_ids << u.channel_qrcode_id @m_sub_groups << {:channel_qrcode => r, :count => u.c, :channel_qrcode_id => u.channel_qrcode_id, :net_increase => net_increase} end m_all_cq_ids = @m_unsub_cq_ids | @m_sub_cq_ids @m_res_cq_ids = m_all_cq_ids - @m_sub_cq_ids @m_res_groups = [] @m_res_cq_ids.each do |c_id| r = getChannelQrcodeRemark(c_id) r_uncub = 0 @m_unsub_groups.each do |u| if u[:channel_qrcode_id] == c_id r_uncub = 0 - u[:count] end end @m_res_groups << {:channel_qrcode => r, :count => r_uncub, :channel_qrcode_id => c_id} end # ------------------------微信日统计-------------------------------- #当日取消关注人数--wx_user d_unsub_user = WxUser.where("(? <= unsubscribe_time and unsubscribe_time < ?) and (channel_qrcode_id is not null and channel_qrcode_id != 0)",@check_day.to_i,(@check_day+1.day).to_i) @d_unsub_user_count= d_unsub_user.count() d_unsub_other = WxUser.where("(? <= unsubscribe_time and unsubscribe_time < ?) and (channel_qrcode_id is null or channel_qrcode_id = 0)",@check_day.to_i, (@check_day+1.day).to_i) @d_unsub_other_count = d_unsub_other.count() #当日新增关注人数--wx_user d_sub_user = WxUser.where("(? <= subscribe_time and subscribe_time < ?) and (channel_qrcode_id is not null and channel_qrcode_id != 0)",@check_day.to_i, (@check_day+1.day).to_i) @d_sub_user_count = d_sub_user.count() d_sub_other = WxUser.where("(? <= subscribe_time and subscribe_time < ?) and (channel_qrcode_id is null or channel_qrcode_id = 0)",@check_day.to_i,(@check_day+1.day).to_i) @d_sub_other_count = d_sub_other.count() #当日渠道“其他”净增 @d_other_net_increase = @d_sub_other_count - @d_unsub_other_count #当日净增--wx_user @d_net_increase = @d_sub_user_count - @d_unsub_user_count + @d_other_net_increase # 当天各渠道取消关注--wx_user（渠道--新增--渠道ID） @d_unsub_groups = [] @d_unsub_cq_ids = [] d_unsub_user.select("channel_qrcode_id, count(*) as c").group("channel_qrcode_id").order("c desc").each do |u| r = getChannelQrcodeRemark(u.channel_qrcode_id) @d_unsub_cq_ids << u.channel_qrcode_id @d_unsub_groups << {:channel_qrcode => r, :count => u.c, :channel_qrcode_id => u.channel_qrcode_id} end #当天各渠道关注--wx_user（渠道--新增--渠道ID） @d_sub_groups = [] @d_sub_cq_ids = [] d_sub_user.select("channel_qrcode_id, count(*) as c").group("channel_qrcode_id").order("c desc").each do |u| r = getChannelQrcodeRemark(u.channel_qrcode_id) net_increase = 0 unsub = d_unsub_user.where("channel_qrcode_id = ?", u.channel_qrcode_id) net_increase = u.c - unsub.count() @d_sub_cq_ids << u.channel_qrcode_id @d_sub_groups <<{:channel_qrcode => r, :count => u.c, :channel_qrcode_id => u.channel_qrcode_id, :net_increase => net_increase} end d_all_cq_ids = @d_unsub_cq_ids | @d_sub_cq_ids @d_res_cq_ids = d_all_cq_ids - @d_sub_cq_ids @d_res_groups = [] @d_res_cq_ids.each do |c_id| r = getChannelQrcodeRemark(c_id) r_uncub = 0 @d_unsub_groups.each do |u| if u[:channel_qrcode_id] == c_id r_uncub = 0 - u[:count] end end @d_res_groups << {:channel_qrcode => r, :count => r_uncub, :channel_qrcode_id => c_id} end end #新手训练营参加者是否投资 def investor_from_train p_id = params[:p_id].to_i || 0 t_id = params[:t_id].to_i || 0 @investor_list = [] sql = "select distinct joins.user_id,u.real_name,u.tel from d5c_users u,d5c_projects pros, d5c_project_joins joins where u.id = joins.user_id and pros.id = joins.project_id and pros.ptype = 'stock' and joins.state = 1 and joins.is_deleted = 0 and joins.is_refunded = 0 and joins.user_id in (select distinct j.user_id from d5c_project_joins j, d5c_projects p where j.project_id = p.id and p.ptype = 'train' and j.state = 1 and j.is_deleted = 0 and j.is_refunded = 0) " if t_id > 0 if p_id > 0 sql = "select distinct joins.user_id,u.real_name,u.tel from d5c_users u,d5c_projects pros, d5c_project_joins joins where u.id = joins.user_id and pros.id = joins.project_id and pros.ptype = 'stock' and pros.id = #{p_id} and joins.state = 1 and joins.is_deleted = 0 and joins.is_refunded = 0 and joins.user_id in (select distinct j.user_id from d5c_project_joins j, d5c_projects p where j.project_id = p.id and p.ptype = 'train' and j.state = 1 and j.is_deleted = 0 and j.is_refunded = 0 and p.id = #{t_id}) " else sql = "select distinct joins.user_id,u.real_name,u.tel from d5c_users u,d5c_projects pros, d5c_project_joins joins where u.id = joins.user_id and pros.id = joins.project_id and pros.ptype = 'stock' and joins.state = 1 and joins.is_deleted = 0 and joins.is_refunded = 0 and joins.user_id in (select distinct j.user_id from d5c_project_joins j, d5c_projects p where j.project_id = p.id and p.ptype = 'train' and j.state = 1 and j.is_deleted = 0 and j.is_refunded = 0 and p.id = #{t_id}) " end else if p_id > 0 sql = "select distinct joins.user_id,u.real_name,u.tel from d5c_users u,d5c_projects pros, d5c_project_joins joins where u.id = joins.user_id and pros.id = joins.project_id and pros.ptype = 'stock' and pros.id = #{p_id} and joins.state = 1 and joins.is_deleted = 0 and joins.is_refunded = 0 and joins.user_id in (select distinct j.user_id from d5c_project_joins j, d5c_projects p where j.project_id = p.id and p.ptype = 'train' and j.state = 1 and j.is_deleted = 0 and j.is_refunded = 0)" end end investors = User.find_by_sql(sql) investors.each do |u| # join_sql = "select distinct p.title from d5c_projects p,d5c_project_joins j where p.id = j.project_id and p.ptype = 'stock' and j.state = 1 and j.is_deleted = 0 and j.is_refunded = 0 and j.user_id = #{u.user_id}" # p_joins = Project.find_by_sql(join_sql).collect{|e| e.title} #投过的训练营项目 train_sql = "select distinct j.project_id, p.title from d5c_projects p,d5c_project_joins j where p.id = j.project_id and p.ptype = 'train' and j.state = 1 and j.is_deleted = 0 and j.is_refunded = 0 and j.user_id = #{u.user_id}" invest_train = Project.find_by_sql(train_sql).collect{|e| "#{e.title}(#{e.project_id})"} @investor_list << {:name => u.real_name, :user_id => u.user_id, :tel => u.tel, :train_project => invest_train} end end # 客户关系列表 def customer @url = "#{CONFIG_FILE["admin_host"]}/report/customer" @per_page = 200 @page = 1 if params[:page].to_i > 0 @page = params[:page].to_i end if params[:per_page].to_i > 0 @per_page = params[:per_page].to_i end # 四个参数客服微信ID，用户ID，用户微信ID，用户真实姓名,项目ID(投资记录) @cs_wx_uid = 0 @user_id = 0 @wx_uid = 0 # @real_name = "" @p_id = 0 if !params[:c].blank? if params[:c].to_i > 0 @cs_wx_uid = params[:c].to_i end end if !params[:u_id].blank? if params[:u_id].to_i > 0 @user_id = params[:u_id].to_i end end if !params[:wx_uid].blank? if params[:wx_uid].to_i > 0 @wx_uid = params[:wx_uid].to_i end end # if !params[:name].blank? # @real_name = params[:name] # end if !params[:project_id].blank? @p_id = params[:project_id].to_i end # -------------------------------------------------------- # sql = "select s.*,u.real_name,u.tel from (select c.*,w.nickname,w.user_id, wx.nickname as cs_nickname FROM d5c_customers c, d5c_wx_users w, d5c_wx_users wx where c.wx_uid = w.id and c.cs_wx_uid = wx.id) s left join d5c_users u on s.user_id = u.id" # sql = sql + " where s.cs_wx_uid >= 0" # if @cs_wx_uid > 0 # sql = sql + " and s.cs_wx_uid = #{@cs_wx_uid}" # end # if @wx_uid > 0 # sql = sql + " and s.wx_uid = #{@wx_uid}" # elsif @user_id > 0 # sql = sql + " and u.id = #{@user_id}" # end # if @p_id > 0 # sql = sql + " and s.user_id in (select distinct user_id from d5c_project_joins j where j.project_id = #{@p_id} and j.state = 1 and j.is_deleted = 0 and j.is_refunded = 0)" # end # -------------------------------------------------------- sql = "select c.*,w.nickname,w.user_id, wx.nickname as cs_nickname from d5c_customers c, d5c_wx_users w, d5c_wx_users wx where c.wx_uid = w.id and c.cs_wx_uid = wx.id" if @cs_wx_uid > 0 sql = sql + " and c.cs_wx_uid = #{@cs_wx_uid}" end if @wx_uid > 0 sql = sql + " and c.wx_uid = #{@wx_uid}" elsif @user_id > 0 sql = sql + " and w.user_id = #{@user_id}" end if @p_id > 0 sql =sql + " and w.user_id in (select distinct user_id from d5c_project_joins j where j.project_id = #{@p_id} and j.state = 1 and j.is_deleted = 0 and j.is_refunded = 0)" end sql = "select info.*,u.real_name, u.tel from (#{sql}) info left join d5c_users u on info.user_id = u.id order by unix_timestamp(info.created_at) desc" # -------------------------------------------------------- total_count_sql ="select count(*) as c from (#{sql}) table1" @total_count = 0 t = Customer.find_by_sql(total_count_sql).first if !t.blank? @total_count = t.c.to_i end if @page > 0 sql = sql + " limit #{(@page - 1) * @per_page} ,#{@per_page}" else sql = sql + " limit #{@per_page}" end @customer_list = Customer.find_by_sql(sql) # --------------------------分页------------------------ # 总数量 @current_page_count = @customer_list.length @total_pages = @total_count / @per_page if @total_count % @per_page > 0 @total_pages += 1 end @next_page = @page + 1 @pre_page = @page - 1 end def rhpf_survey #填写人数-------------------------------------------------- @filled_user_count = 0 #总星数---------------------------------------------------- @total_star_nums = 0 @survey = RhpfSurvey.where("push_time > ?",0).last @id = @survey.id if !params[:survey].blank? @id = params[:survey].to_i s = RhpfSurvey.where("id = ?", @id).first if !s.blank? @survey = s end end option_ids = @survey.options s = option_ids.split(",") #调查总星数 @survey_total_stars = s.length * 5 @option_info = [] s.each do |u| id = u.to_i option = RhpfOption.where("id = ?", id).first ave = 0 star_count = [] (1..5).each do |i| count = 0 result_by_star = RhpfResult.where("survey_id = ? and star_nums = ? and option_id = ?",@survey.id, i, id) count = result_by_star.count() star_count << count end total_star = star_count[0]*1+star_count[1]*2+star_count[2]*3+star_count[3]*4+star_count[4]*5 @total_star_nums += total_star total_person = star_count[0]+star_count[1]+star_count[2]+star_count[3]+star_count[4] @filled_user_count = total_person ave = total_star/(total_person*1.0) ave = format("%.2f",ave).to_f @option_info << {:option => option.title, :survey => @survey.title, :one => star_count[0], :two => star_count[1], :three => star_count[2], :four => star_count[3], :five => star_count[4], :ave => ave} end #平均总星数------------------------------------------------ @ave_total_stars = @total_star_nums/(@filled_user_count * 1.0) @ave_total_stars = format("%.2f", @ave_total_stars).to_f #推送人数-------------------------------------------------- @push_user_count = 0 pids = @survey.projects projectIds = pids.split(",") sql_p_ids = "" if !@survey.projects.blank? sql_p_ids = "("+@survey.projects+")" end if projectIds.length != 0 sql = "select distinct user_id from d5c_project_joins where state=1 and is_refunded=0 and is_deleted=0 and project_id in #{sql_p_ids}" invest_user = ProjectJoin.find_by_sql(sql) @push_user_count = invest_user.count() else sql = "select distinct user_id from d5c_project_joins where state=1 and is_refunded=0 and is_deleted=0" invest_user = ProjectJoin.find_by_sql(sql) invest_user_count = invest_user.count() @push_user_count = invest_user_count end result_sql = "select sum(star_nums) as t,wx_uid from d5c_rhpf_results where survey_id = #{@survey.id} group by wx_uid" @users = RhpfResult.find_by_sql(result_sql) @not_null_comments = [] @users.each do |u| pass = 0 if u.t >= @survey.pass_stars pass = 1 end t = "" result = RhpfResult.where("wx_uid = ? and survey_id = ?",u.wx_uid, @survey.id).first if !result.blank? t = result.comment end if !t.blank? wx_user = WxUser.where("id = ?",u.wx_uid).first if !wx_user.blank? usr = "" u_id = 0 if !wx_user.user_id.blank? &&wx_user.user_id > 0 u_id = wx_user.user_id user = User.where("id = ?",wx_user.user_id).first if !user.blank? usr = user.real_name end end @not_null_comments << {:wx_user => wx_user.nickname,:wx_uid => u.wx_uid, :comment => t,:pass => pass, :star => u.t, :u_id => wx_user.user_id, :usr => usr} end end end #-----------------------------分页---------------------------------------------------------- #每页评论数量 @total_pages = 0 @per_page = 20 @page = 1 #总页数 if !params[:per_page].blank? @per_page = params[:per_page].to_i end if !params[:page].blank? @page = params[:page].to_i end @next_page = @page + 1 @pre_page = @page - 1 if @pre_page < 1 @pre_page = 1 end @total_pages = (@not_null_comments.length/(@per_page * 1.0)).ceil @url = "#{CONFIG_FILE["admin_host"]}/report/rhpf_survey" @show_comments = [] if @not_null_comments.length <= @per_page @show_comments = @not_null_comments elsif @not_null_comments.length <= (@per_page * (@page-1)) @show_comments = @not_null_comments.first(@per_page) elsif @not_null_comments.length <= (@per_page * @page) @show_comments = @not_null_comments[(@per_page*(@page-1))..(@not_null_comments.length)] @next_page = 1 else @show_comments = @not_null_comments[(@per_page*(@page-1)),@per_page] end end #成功项目，新老投资比 def project_investor @p_id = 0 if !params[:id].blank? @p_id = params[:id].to_i else #取最近众筹中或者众筹成功的项目 states = ["underway","success"] @p_id = Project.where("state in (?)", states).last.id end p = Project.where("id = ?", @p_id).first @invest_user_infos_display = "block" @project_title = "" if !p.blank? @project_title = p.title if p.invest_version == 1 @invest_user_infos_display = "none" end end #回报方式投资人 sql = "select j.user_id,u.nickname,u.tel,u.email,u.real_name,u.tel,u.signup_channel_id,j.project_invest_way_id,w.title,sum(j.copies_count) as c, sum(j.total_price) as tp from d5c_project_joins j, d5c_users u,d5c_project_invest_ways w where j.state=1 and j.is_refunded=0 and j.is_deleted=0 and j.project_id=#{@p_id} and u.id=j.user_id and w.id=j.project_invest_way_id group by j.user_id, j.project_invest_way_id order by c desc,j.user_id asc;" @invest_user_infos = User.find_by_sql(sql) #该项目所有成功投资人 investor_sql = "select DISTINCT user_id, project_id from d5c_project_joins where project_id = #{@p_id} and paied_at >0 and state = 1 and is_deleted = 0 and is_refunded = 0 order by user_id" investors = ProjectJoin.find_by_sql(investor_sql) investors_ids = investors.collect{|i| i.user_id} #总投资金额 @total_invest_money = 0 sql = "select sum(total_price) as c from d5c_project_joins where project_id = #{@p_id} and paied_at >0 and state = 1 and is_deleted = 0 and is_refunded = 0 " t = ProjectJoin.find_by_sql(sql).first if !t.blank? @total_invest_money = t.c.to_f/100.0 end #所有相关注册渠道 channel_ids = User.select("distinct signup_channel_id").where("id in (?)", investors_ids).collect{|i| i.signup_channel_id} #老投资人Id与数目 @total_investor_count = investors.count() old_investor_ids = [] #老投资人id @old_investor_count = 0 #老投资人人数 new_investor_ids = [] #新投资人id @new_investor_count = 0 #新投资人人数 #新投资人 investors_ids.each do |u| is = false is = is_old_investor(u, @p_id) if is old_investor_ids << u @old_investor_count += 1 else new_investor_ids << u @new_investor_count += 1 end end #老用户投资金额 @old_invest_money = 0 o = ProjectJoin.select("sum(total_price) as c").where("project_id = ? and paied_at >0 and state = 1 and is_deleted = 0 and is_refunded = 0 and user_id in (?)", @p_id, old_investor_ids).first if !o.blank? @old_invest_money = o.c.to_f/100.0 end #新用户投资金额 @new_invest_money = 0 n = ProjectJoin.select("sum(total_price) as c").where("project_id = ? and paied_at >0 and state = 1 and is_deleted = 0 and is_refunded = 0 and user_id in (?)", @p_id, new_investor_ids).first if !n.blank? @new_invest_money = n.c.to_f/100.0 end #根据每个渠道划分 # 各渠道人数 @total_investor_of_channel_count = [] @rest_total_investor_count = @total_investor_count @old_investor_of_channel_count = [] @rest_old_investor_count = @old_investor_count @new_investor_of_channel_count = [] @rest_new_investor_count = @new_investor_count #各渠道投资金额 @total_invest_money_of_channel = [] @old_invest_money_of_channel = [] @new_invest_money_of_channel = [] @rest_total_invest_money = @total_invest_money @rest_old_invest_money = @old_invest_money @rest_new_invest_money = @new_invest_money @channels = [] channel_ids.each do |u| c = SignUpChannel.where("id = ?", u).first if c.blank? || c.channel_value == nil next else @channels << c.channel_value end t = User.where("signup_channel_id = ? and id in (?)", u, investors_ids) t_count = t.count() t_ids = [] if !t.blank? t.each do |u| t_ids << u.id end end t_invest = ProjectJoin.select("sum(total_price) as c").where("project_id = ? and paied_at >0 and state = 1 and is_deleted = 0 and is_refunded = 0 and user_id in (?)", @p_id, t_ids).first if !t_invest.blank? @total_invest_money_of_channel << t_invest.c.to_f/100.0 @rest_total_invest_money -= t_invest.c.to_f/100.0 end @total_investor_of_channel_count << t_count @rest_total_investor_count -= t_count o = User.where("signup_channel_id = ? and id in (?)", u, old_investor_ids) o_ids = [] o_count = o.count() if !o.blank? o.each do |u| o_ids << u.id end end o_invest = ProjectJoin.select("sum(total_price) as c").where("project_id = ? and paied_at >0 and state = 1 and is_deleted = 0 and is_refunded = 0 and user_id in (?)", @p_id, o_ids).first if !o_invest.blank? @old_invest_money_of_channel << o_invest.c.to_f/100.0 @rest_old_invest_money -= o_invest.c.to_f/100.0 end @old_investor_of_channel_count << o_count @rest_old_investor_count -= o_count n = User.where("signup_channel_id = ? and id in (?)", u, new_investor_ids) n_ids = [] n_count = n.count() if !n.blank? n.each do |u| n_ids << u.id end end n_invest = ProjectJoin.select("sum(total_price) as c").where("project_id = ? and paied_at >0 and state = 1 and is_deleted = 0 and is_refunded = 0 and user_id in (?)", @p_id, n_ids).first if !n_invest.blank? @new_invest_money_of_channel << n_invest.c.to_f/100.0 @rest_new_invest_money -= n_invest.c.to_f/100.0 end @new_investor_of_channel_count << n_count @rest_new_investor_count -= n_count end render :layout => false end #投资人渠道分布 def investor_channel ##注：页面参数为time,输入格式"2016-12-12"或"2016-12" @now = Time.now @this_day = Time.new(@now.year, @now.month, @now.day) if !params[:time].blank? s = params[:time].split("-") if s.length < 3 @this_day = Time.new(@this_day.year, @this_day.month, @this_day.day) else @this_day = Time.new(s[0],s[1],s[2]) end end @final_day = @this_day + 1.day @total_investor_count = 0 @users_channel_info = [] sql = "select count(distinct s.user_id) as c, u.signup_channel_id from d5c_stock_balances s ,d5c_users u, d5c_projects p where s.user_id = u.id and s.project_id =p.id and p.ptype = 'stock' group by u.signup_channel_id order by c desc;" users = User.find_by_sql(sql) users.each do |u| @total_investor_count = @total_investor_count + u.c.to_i channel_value = get_channel_value_by_id(u.signup_channel_id) @users_channel_info << {:c_id => u.signup_channel_id, :c_value => channel_value, :count => u.c.to_i} end #7天体验金 sql = "select signup_channel_id, count(*) as c from d5c_users where id in (select user_id from d5c_zt_invest_orders where state=1 and unix_timestamp(created_at) >= #{@this_day.to_i} and unix_timestamp(created_at) < #{@final_day.to_i} group by user_id) group by signup_channel_id order by c desc;" @current_seven_days_investors = User.find_by_sql(sql) sql = "select signup_channel_id, count(*) as c from d5c_users where id in (select user_id from d5c_zt_invest_orders where state=1 group by user_id) group by signup_channel_id order by c desc;" @seven_days_investors_info = [] @seven_days_investors_count = 0 User.find_by_sql(sql).each do |u| @seven_days_investors_count = @seven_days_investors_count + u.c.to_i channel_value = get_channel_value_by_id(u.signup_channel_id) current_investor_count = 0 @current_seven_days_investors.each do |i| if i.signup_channel_id == u.signup_channel_id current_investor_count = i.c.to_i end end @seven_days_investors_info << {:c_id => u.signup_channel_id, :c_value => channel_value, :count => u.c.to_i, :current_investor_count => current_investor_count} end #1000元体验金 sql = "select signup_channel_id, count(*) as c from d5c_users where id in (select user_id from d5c_project_joins where state=1 and pay_way = 'trial_coin' and (paied_at >= #{@this_day.to_i} and paied_at < #{@final_day.to_i}) group by user_id) group by signup_channel_id order by c desc;" @current_tyj_investors = User.find_by_sql(sql) sql = "select signup_channel_id, count(*) as c from d5c_users where id in (select user_id from d5c_project_joins where state=1 and pay_way = 'trial_coin' group by user_id) group by signup_channel_id order by c desc;" @tyj_investors_info = [] @tyj_investors_count = 0 User.find_by_sql(sql).each do |u| @tyj_investors_count = @tyj_investors_count + u.c.to_i channel_value = get_channel_value_by_id(u.signup_channel_id) current_investor_count = 0 @current_tyj_investors.each do |i| if i.signup_channel_id == u.signup_channel_id current_investor_count = i.c.to_i end end @tyj_investors_info << {:c_id => u.signup_channel_id, :c_value => channel_value, :count => u.c.to_i, :current_investor_count => current_investor_count} end #新手训练营 sql = "select u.signup_channel_id, count(distinct u.id) as c from d5c_users u, d5c_projects p, d5c_project_joins j where j.user_id = u.id and j.project_id = p.id and p.ptype = 'train' and j.paied_at >= #{@this_day.to_i} and j.paied_at < #{@final_day.to_i} group by u.signup_channel_id order by c desc" @current_day_train_investors = User.find_by_sql(sql) sql = "select u.signup_channel_id, count(distinct u.id) as c from d5c_users u, d5c_projects p, d5c_project_joins j where j.user_id = u.id and j.project_id = p.id and p.ptype = 'train' group by u.signup_channel_id order by c desc" @train_investors = [] @train_investors_count = 0 User.find_by_sql(sql).each do |u| @train_investors_count += u.c.to_i channel_value = get_channel_value_by_id(u.signup_channel_id) current_day_train_investor_count = 0 @current_day_train_investors.each do |i| if i.signup_channel_id == u.signup_channel_id current_day_train_investor_count = i.c.to_i end end @train_investors << {:c_id => u.signup_channel_id, :c_value => channel_value, :count => u.c.to_i, :current_investor_count => current_day_train_investor_count} end #校验用户注册渠道 #以二维码为准，根据时间先后，强制将注册渠道改为和二维码相匹配的渠道。如channel_qrcode_id=64是真功夫桌贴， signup_channel_id=41  是真功夫桌贴， u.created_at>w.created_at这个条件是关键 sql = "select u.id,w.channel_qrcode_id from d5c_wx_users w,d5c_users u, d5c_sign_up_channels s where u.id=w.user_id and u.created_at>w.created_at and w.channel_qrcode_id >0 and (s.id!=u.signup_channel_id or u.signup_channel_id is null or u.signup_channel_id = 0) and s.channel_qrcode_id=w.channel_qrcode_id;" u = User.find_by_sql(sql) u.each do |i| signup_channel = SignUpChannel.where("channel_qrcode_id = ?", i.channel_qrcode_id).first if !signup_channel.blank? usr = get_user_by_id(i.id) if !usr.blank? usr.signup_channel_id = signup_channel.id usr.save end end end sql = "select u.id,w.channel_qrcode_id from d5c_wx_users w,d5c_users u where u.id=w.user_id and u.created_at>w.created_at and w.channel_qrcode_id >0 and (u.signup_channel_id=0 or u.signup_channel_id is NULL);" user_list = User.find_by_sql(sql) user_list.each do |i| c_q = ChannelQrcode.where("id = ?",i.channel_qrcode_id).first if !c_q.blank? && !c_q.sign_up_channel_id.blank? && c_q.sign_up_channel_id > 0 usr = get_user_by_id(i.id) if !usr.blank? usr.signup_channel_id = c_q.sign_up_channel_id usr.save end end end end def monthly_cost @now = Time.now # current_month_first_day = Time.new(@now.year, @now.month, @now.day) current_month = Time.new(@now.year, @now.month) first_month = current_month - 11.months @ms = [] @months = {} @month_article_cost = {} @month_train_club_cost = {} @month_trail_coin_cost = {} @month_zt_invest_cost = {} @month_zhuotie = {} #找到所有训练营项目 sql = "select distinct p.id, p.total_crowd_funding, p.bonus_project_id, p.project_owner_has_crowd_funding from d5c_projects p, d5c_project_bonuses b where p.ptype = 'train' and b.project_id = p.id and t_rate > 100" train_projects = Project.find_by_sql(sql) 12.times do |i| mon = first_month + (i <2 ? i.month : i.months) m = mon.month @ms << m @months[m] = "#{mon.year}-#{m}" @month_article_cost[m] = 0 @month_train_club_cost[m] = 0 @month_trail_coin_cost[m] = 0 @month_zt_invest_cost[m] = 0 @month_zhuotie[m] = 0 #计算训练营每月支出 cost = 0 train_projects.each do |u| b_id = u.id # if !u.bonus_project_id.blank? && u.bonus_project_id > 0 # b_id = u.bonus_project_id # end rate = 0 * 1.0 # 项目这个月累计分红大于100的最后一条记录 sql = "select max(t_rate) as r from d5c_project_bonuses where project_id = #{b_id} and t_rate > 100 and date_add(b_time,interval 8 hour) >= '#{mon}' and date_add(b_time,interval 8 hour) < '#{mon + 1.month}'" bonus = ProjectBonus.find_by_sql(sql).first if !bonus.blank? && !bonus.nil? && !bonus.r.nil? sql = "select max(t_rate) as r from d5c_project_bonuses where project_id = #{b_id} and t_rate > 100 and date_add(b_time,interval 8 hour) < '#{mon}'" last_bonus = ProjectBonus.find_by_sql(sql).first if !last_bonus.blank? && !last_bonus.nil? && !last_bonus.r.nil? rate = bonus.r - last_bonus.r else rate = bonus.r - 100.0 end end cost += (u.total_crowd_funding - u.project_owner_has_crowd_funding) * rate /100.0 end @month_train_club_cost[m] = cost end # 文章每月支出 sql = "select date_format(date_add(created_at,interval 8 hour),'%Y%m') as ym, sum(count) as c, date_format(date_add(created_at,interval 8 hour),'%m')as m from d5c_balances where count>0 and (date_add(created_at,interval 8 hour) >= '#{first_month}') and (date_add(created_at,interval 8 hour) < '#{current_month + 1.month}') and source='art_click_benefit' group by ym asc;" Article.find_by_sql(sql).each do |u| @month_article_cost[u.m.to_i] = u.c.to_i end #投资专题每月支出 sql = "select date_format(date_add(benefit_at,interval 8 hour),'%Y%m') as ym, sum(b_count - total_price) as c, date_format(date_add(benefit_at,interval 8 hour),'%m')as m from d5c_zt_invest_orders where (b_count - total_price)>=0 and (date_add(benefit_at,interval 8 hour) >= '#{first_month}') and (date_add(benefit_at,interval 8 hour) < '#{current_month + 1.month}') group by ym asc;" ZtInvestOrder.find_by_sql(sql).each do |u| @month_zt_invest_cost[u.m.to_i] = u.c.to_i end #一天活动 sql = "select date_format(from_unixtime(benefit_time),'%Y%m') ym, sum(benefit_amount) as c, date_format(from_unixtime(benefit_time),'%m') m from d5c_trial_coin_orders where benefit_state = 1 and benefit_time < #{(current_month+1.month).to_i} and benefit_time >= #{first_month.to_i} group by ym order by ym desc" TrialCoinOrder.find_by_sql(sql).each do |u| @month_trail_coin_cost[u.m.to_i] = u.c.to_i end #桌贴广告费 sql = "select date_format(created_at,'%Y%m') ym, sum(count) as c, date_format(created_at,'%m') m from d5c_balances where source = 'zhuotie_guanggaofei' and created_at < '#{(current_month+1.month)}' and created_at >= '#{first_month}' group by ym order by ym desc" TrialCoinOrder.find_by_sql(sql).each do |u| @month_zhuotie[u.m.to_i] = u.c.to_i end end # 项目电子合同签署情况 def tsign_project_info @unsigned = params[:not_sign_all] || false last_tsign_p_id = TsignConfig.last.project_id id = params[:project_id] || last_tsign_p_id @p_id = id.to_i @all_project_investors_info = [] @p_title = get_project_title(@p_id) #投资该项目所有投资人用户ID sql = "select distinct user_id from d5c_project_joins where project_id = #{@p_id} and state = 1 and is_deleted = 0 and is_refunded = 0" investor_ids = ProjectJoin.find_by_sql(sql) investor_ids.each do |u| real_name = "" user_sex = "" user_tel = "" sub_info = "" sign_held_info = "未签署" sign_invest_info = "未签署" is_sign_all = 0 is_display = "table-row" #单个投资用户签署信息 user_info_sql = "select u.id, u.tel, u.sex, u.real_name, w.nickname, w.subscribe from d5c_users u, d5c_wx_users w where u.id = #{u.user_id} and w.user_id = u.id" user_info = User.find_by_sql(user_info_sql).first if !user_info.blank? real_name = user_info.real_name if user_info.sex == 0 user_sex = "未知" elsif user_info.sex == 1 user_sex = "男性" elsif user_info.sex == 2 user_sex = "女性" end user_tel = user_info.tel sub_info = "未关注公众号" if user_info.subscribe sub_info = "关注" end else sql = "select u.id, u.tel, u.sex, u.real_name from d5c_users u where u.id = #{u.user_id}" user_info = User.find_by_sql(sql).first if !user_info.blank? real_name = user_info.real_name if user_info.sex == 0 user_sex = "未知" elsif user_info.sex == 1 user_sex = "男性" elsif user_info.sex == 2 user_sex = "女性" end user_tel = user_info.tel sub_info = "未关注公众号" end end signed_contract_sql = "select contract_type from d5c_tsign_user_file_signs where user_id = #{u.user_id} and project_id = #{@p_id}" signed_contract = TsignUserFileSign.find_by_sql(signed_contract_sql) signed_contract.each do |u| if u.contract_type == "held" sign_held_info = "签署" elsif u.contract_type == "invest" sign_invest_info = "签署" end end if sign_invest_info == "签署" && sign_held_info == "签署" is_sign_all = 1 end if @unsigned == "true" && is_sign_all == 1 is_display = "none" end @all_project_investors_info << {:user_id => u.user_id, :user_name => real_name, :user_tel => user_tel,:sub_info => sub_info, :sign_held_info => sign_held_info, :sign_invest_info => sign_invest_info, :is_sign_all => is_sign_all, :is_display => is_display, :user_sex => user_sex} end end # 充值订单新老投资人比例 def recharge # 输入方式"2017-03" # 确定查询月份 @now = Time.now @current_month = Time.new(@now.year, @now.month) @check_month = @current_month if @now.month == 12 @check_next_month = Time.new(@now.year+1, 1) else @check_next_month = Time.new(@now.year, @now.month+1) end if !params[:time].blank? m = params[:time].split("-") @check_month = Time.new(m[0],m[1]) if m[1].to_i == 12 @check_next_month = Time.new(m[0].to_i+1, 1) else @check_next_month = Time.new(m[0], m[1].to_i+1) end end # 充值区间 @se = [0, 1, 100000, 500000, 1000000, 5000000] @section = ["0-1","1-1000", "1000-5000","5000-10000", "10000-50000", "50000以上"] #每个区间充值金额 @se_recharges = [] @old_se_recharges = [] @new_se_recharges = [] @se_recharge_user_count = [] @old_se_recharge_user_count = [] @new_se_recharge_user_count = [] size = @se.size se_sql = "select distinct user_id, sum(total_price) as recharge from d5c_balance_orders where paied_at < #{@check_next_month.to_i} and paied_at >= #{@check_month.to_i} and state = 1 group by user_id order by user_id" s = BalanceOrder.find_by_sql(se_sql) for i in 0..(size - 1) @se_recharges[i] = 0 #总充值 @old_se_recharges[i] = 0 #老充值 @new_se_recharges[i] = 0 #新充值 @se_recharge_user_count[i]=0 #总用户充值人数 @old_se_recharge_user_count[i]= 0 #老用户充值人数 @new_se_recharge_user_count[i]= 0 #新用户充值人数 # if i == size-1 # se_sql = "select distinct user_id, sum(total_price) as recharge from d5c_balance_orders where paied_at < #{@check_next_month.to_i} and paied_at >= #{@check_month.to_i} and state = 1 and total_price > #{@se[i]} group by user_id order by id" # else # se_sql = "select distinct user_id, sum(total_price) as recharge from d5c_balance_orders where paied_at < #{@check_next_month.to_i} and paied_at >= #{@check_month.to_i} and state = 1 and total_price > #{@se[i]} and total_price <= #{@se[i+1]} group by user_id order by id" # end # 符合条件的记录，并计算充值总额 # se_re = BalanceOrder.find_by_sql(se_sql) if i == size-1 s.each do |item| t = item.recharge.to_i if t > @se[i] @se_recharge_user_count[i] +=1 @se_recharges[i] += t has_recharged = is_recharge_before_current_month(item.user_id, @check_month) if has_recharged == true @old_se_recharges[i] += t @old_se_recharge_user_count[i] +=1 else @new_se_recharges[i] += t @new_se_recharge_user_count[i] +=1 end end end else s.each do |item| t = item.recharge.to_i if t > @se[i] && t <= @se[i+1] @se_recharges[i] += t @se_recharge_user_count[i] +=1 has_recharged = is_recharge_before_current_month(item.user_id, @check_month) if has_recharged == true @old_se_recharges[i] += t @old_se_recharge_user_count[i] +=1 else @new_se_recharges[i] += t @new_se_recharge_user_count[i] +=1 end end end end # se_re.each do |item| # if item.recharge.to_i > 0 # @se_recharges[i] += item.recharge.to_i # has_recharged = is_recharge_before_current_month(item.user_id, @check_month) # if has_recharged == true # @old_se_recharges[i] += item.recharge.to_i # else # @new_se_recharges[i] += item.recharge.to_i # end # end # end # @se_recharges[i] = s.first.recharge.to_i if @se_recharges[i] !=nil && @se_recharges[i] > 0 @se_recharges[i] /= 100.0 end if @old_se_recharges[i] != nil && @old_se_recharges[i] > 0 @old_se_recharges[i] /= 100.0 end if @new_se_recharges[i] != nil && @new_se_recharges[i] > 0 @new_se_recharges[i] /= 100.0 end end # # 该月总充值金额 # @total_recharge = BalanceOrder.select("sum(total_price) as c") # .where("paied_at < ? and paied_at >= ? and state= 1", (@check_next_month).to_i, @check_month.to_i).last.c.to_i/100.0 #该月充值用户 u_sql = "select distinct user_id, sum(total_price) as recharge from d5c_balance_orders where paied_at < #{@check_next_month.to_i} and paied_at >= #{@check_month.to_i} and state = 1 group by user_id order by user_id" # 各用户充值总额 @user_recharge = BalanceOrder.find_by_sql(u_sql) # 判断用户在查询月之前是否有冲过值,充过则将充值金额加在老用户，否则，为新用户 @total_recharge = 0 @old_user_recharge = 0 @new_user_recharge = 0 # 充值人数 @total_user_recharger = 0 @old_user_recharger = 0 @new_user_recharger = 0 @user_recharge.each do |item| @total_recharge += item.recharge.to_i @total_user_recharger +=1 has_recharged = is_recharge_before_current_month(item.user_id, @check_month) if has_recharged == true @old_user_recharge += item.recharge.to_i @old_user_recharger += 1 else @new_user_recharge += item.recharge.to_i @new_user_recharger += 1 end end if @total_recharge != 0 @total_recharge /= 100.0 end if @old_user_recharge != 0 @old_user_recharge /= 100.0 end if @new_user_recharge != 0 @new_user_recharge /= 100.0 end render :layout => false end #统计文章相关信息 def article last_id = Article.last.id id = params[:id] || last_id @the_id = id article = Article.where("id = ?",id).first title = "" @info = Hash.new if !article.blank? title = article.title click_count = article.click money_count_sql = "select sum(count) as c from d5c_article_click_benefits where article_id = #{id}" money_count = 0 article_benefit = ArticleClickBenefit.find_by_sql(money_count_sql).first if !article_benefit.blank? money_count = article_benefit.c.to_i end # timeline_count_sql = "select * from d5c_share_infos where relate_id = #{id} and share_to = 'timeline'" # timeline_count = ShareInfo.find_by_sql(timeline_count_sql).count() timeline_count = ShareInfo.where("relate_id = ? and share_to = ?", "#{id}", "timeline").count() # group_count_sql = "select * from d5c_share_infos where relate_id = #{id} and share_to = 'group'" # group_count = ShareInfo.find_by_sql(group_count_sql).count() group_count = ShareInfo.where("relate_id = ? and share_to = ?", "#{id}", "group").count() click_url_times_count = 0 click_url_user_count = 0 if !article.url.blank? s = article.url.split("/") len = s.length click_from_channel_id = s[len-1].to_i click_from_channel = ClickFromChannel.where("id = ?", click_from_channel_id).first if !click_from_channel.blank? click_url_times_count = click_from_channel.click_times end click_url_user_count_sql = "select * from d5c_click_from_users where click_from_channel_id = #{click_from_channel_id}" click_url_user_count = ClickFromUser.find_by_sql(click_url_user_count_sql).count() end @info = Hash[:title => title,:click_count => click_count, :money_count => money_count, :timeline_count => timeline_count, :group_count => group_count, :click_url_times_count => click_url_times_count, :click_url_user_count => click_url_user_count] end end def stock_holder_list @project_id = params[:project] || 0 end private #统计年粉丝情况 def get_report_weixin_users_yearly(appid, yearly_months, isnewly = false) report_weixin_users_yearly_data = {} yearly_months.each do |m| report_weixin_users_yearly_data[m] = 0 end if isnewly ReportWeixinUser.select("month, sum(added_count) count").where("year=2016 and month>? and appid=? ", Time.now.month,appid).group("month").order("month").each do |u| report_weixin_users_yearly_data[u.month] = u.count end else ReportWeixinUser.select("month, sum(added_count-canceled_count) count").where("year=2016 and month>? and appid=? ", Time.now.month,appid).group("month").order("month").each do |u| report_weixin_users_yearly_data[u.month] = u.count end end if isnewly ReportWeixinUser.select("month, sum(added_count) as count").where("year=2017 and appid=?",appid).group("month").order("month").each do |u| report_weixin_users_yearly_data[u.month] = u.count end else ReportWeixinUser.select("month, sum(added_count-canceled_count) as count").where("year=2017 and appid=?",appid).group("month").order("month").each do |u| report_weixin_users_yearly_data[u.month] = u.count end end return report_weixin_users_yearly_data.values end #统计月粉丝情况 def get_report_weixin_users_monthly(appid,monthly_days, isnewly = false) report_weixin_users_monthly_data = {} monthly_days.each do |m| report_weixin_users_monthly_data[m] = 0 end if isnewly ReportWeixinUser.select("day, sum(added_count) as count").where("year=? and month=? and appid=?",Time.now.year,Time.now.month,appid).group("day").order("day").each do |u| report_weixin_users_monthly_data[u.day] = u.count end else ReportWeixinUser.select("day, sum(added_count-canceled_count) as count").where("year=? and month=? and appid=?",Time.now.year,Time.now.month,appid).group("day").order("day").each do |u| report_weixin_users_monthly_data[u.day] = u.count end end return report_weixin_users_monthly_data.values end #统计年粉丝，根据开始时间和结束时间 def get_report_weixin_users_by_period(appid,first_time,last_time, is_newly=false) report_weixin_users = {} if first_time.to_i >= last_time.to_i return nil end first_year = first_time.year.to_i first_month = first_time.month.to_i last_year = last_time.year.to_i last_month = last_time.month.to_i if first_year = last_year (first_month...last_month).each do |m| report_weixin_users[m] = 0 end if is_newly sql = "select month,sum(added_count) as c from report_weixin_users where year = #{first_year} and month >= #{first_month} and month <= #{last_month} and appid = '#{appid}' group by month order by month desc" ReportWeixinUser.find_by_sql(sql).each do |u| report_weixin_users[u.month.to_i] = u.c.to_i end return report_weixin_users else sql = "select month,sum(added_count-canceled_count) as c from report_weixin_users where year = #{first_year} and month >= #{first_month} and month <= #{last_month} and appid = '#{appid}' group by month order by month desc" ReportWeixinUser.find_by_sql(sql).each do |u| report_weixin_users[u.month.to_i] = u.c.to_i end return report_weixin_users end elsif first_year < last_year (first_month..12).each do |m| report_weixin_users[m] = 0 end (1...last_month).each do |m| report_weixin_users[m] = 0 end if is_newly sql = "select month,sum(added_count) as c from report_weixin_users where year = #{first_year} and month >= #{first_month} and appid = '#{appid}' group by month order by month desc" ReportWeixinUser.find_by_sql(sql).each do |u| report_weixin_users[u.month.to_i] = u.c.to_i end sql = "select month,sum(added_count) as c from report_weixin_users where year = #{last_year} and month <= #{last_month} and appid = '#{appid}' group by month order by month desc" ReportWeixinUser.find_by_sql(sql).each do |u| report_weixin_users[u.month.to_i] = u.c.to_i end return report_weixin_users else sql = "select month,sum(added_count-canceled_count) as c from report_weixin_users where year = #{first_year} and month >= #{first_month} and appid = '#{appid}' group by month order by month desc" ReportWeixinUser.find_by_sql(sql).each do |u| report_weixin_users[u.month.to_i] = u.c.to_i end sql = "select month,sum(added_count-canceled_count) as c from report_weixin_users where year = #{last_year} and month <= #{last_month} and appid = '#{appid}' group by month order by month desc" ReportWeixinUser.find_by_sql(sql).each do |u| report_weixin_users[u.month.to_i] = u.c.to_i end return report_weixin_users end end end # 判断用户在查询月之前是否有充过值 def is_recharge_before_current_month(u_id, check_month) is_recharge = false sql = "select * from d5c_balance_orders where paied_at < #{check_month.to_i} and state = 1 and user_id = #{u_id}" user_recharge = BalanceOrder.find_by_sql(sql) if !user_recharge.blank? is_recharge = true end return is_recharge end #判断用户是否为新投资用户 def is_old_investor(user_id, project_id) is_old = false #找出该用户所有该项目的最早时间点 invest_time = 0 t = ProjectJoin.where("state=1 and is_refunded =0 and is_deleted=0 and paied_at > 0 and user_id = ? and project_id = ?", user_id, project_id).first invest_time = t.paied_at #是否有体验金之外投资 sql = "select j.* from d5c_projects p, d5c_project_joins j where p.id = j.project_id and p.ptype = 'stock' and j.state = 1 and j.is_deleted = 0 and j.is_refunded = 0 and j.user_id = #{user_id} and j.paied_at < #{invest_time} limit 1" # p = ProjectJoin.where("state=1 and is_refunded =0 and is_deleted=0 and paied_at > 0 and paied_at < ? and user_id = ? and pay_way != ?", invest_time, user_id, "trail_coin") p = ProjectJoin.find_by_sql(sql).first if !p.blank? is_old = true end return is_old end def getChannelQrcodeRemark(channel_qrcode_id) id = channel_qrcode_id r = "" cq = ChannelQrcode.where("id = ?",id).first if !cq.blank? r = cq.remark if r.blank? r = "二维码ID:#{channel_qrcode_id}" end else r = "二维码ID:#{channel_qrcode_id}(已删除)" end return r end # 项目所有投资人通过项目ID def get_all_project_investors(project_id) sql = "select distinct user_id from d5c_project_joins where state=1 and is_refunded=0 and is_deleted=0 and project_id = #{project_id}" all_project_investors = ProjectJoin.find_by_sql(sql) return all_project_investors end #获得所有投资人 def get_all_investors sql = "select distinct j.user_id from d5c_project_joins j, d5c_projects p where j.project_id = p.id and p.ptype = 'stock' and j.state = 1 and j.is_deleted = 0 and is_deleted = 0" all_investors = ProjectJoin.find_by_sql(sql) return all_investors end #获取用户 def get_user_by_id(user_id) user = User.where("id = ?", user_id).first return user end #获取项目名 def get_project_title(project_id) title = "" p = Project.where("id = ?", project_id).first if !p.blank? title = p.title end return title end # 是否关注公众号 def is_sub(user_id) is_sub = false wx_user = WxUser.where("user_id = ?", user_id).first if !wx_user.blank? && wx_user.subscribe = true is_sub = true end return is_sub end # 是否签署合同 def is_sign_contact(user_id, project_id, type) is_sign = false contact = TsignUserFileSign.where("user_id = ? and project_id = ? and contract_type = ?", user_id, project_id, type) if !contact.blank? is_sign = true end return is_sign end #包含 def is_contain(arr, n) i = false arr.each do |u| u = n i = true break end return i end #获取渠道描述 def get_channel_value_by_id(channel_id) v = "" c = SignUpChannel.where("id = ?", channel_id).first if !c.blank? v = c.channel_value end return v end end