fohow_admin/app/controllers/report_controller.rb

# 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<?",@check_month,@check_month+1.month)
    @monthly_increase_users = @monthly_users.count()
    # 当日新增用户 --user
    @daily_users = User.where("? <= created_at and created_at<?",@check_day,@check_day+1.day)
    @daily_increase_users = @daily_users.count()
    #当日各渠道用户增长 --user
    @daily_user_groups = @daily_users.select("signup_channel_id, count(*) as c").group("signup_channel_id").order("c desc")
    #当月各渠道用户增长 --user
    @monthly_user_groups = @monthly_users.select("signup_channel_id, count(*) as c").group("signup_channel_id").order("c desc")

    # ------------------------微信月统计------------------------------
    # 总微信用户 --wx
    @total_wx_user = ReportWeixinUser.where("appid = ?","superd5c").last.amount
    #当月微信用户净增长--wx--report_wx_user
    @monthly_increase_wx_users = 0
    ReportWeixinUser.select("added_count-canceled_count as count").where("year= ? and month=? and appid=? ", @check_month.strftime("%Y").to_i, @check_month.strftime("%m").to_i,"superd5c").each do |u|
      @monthly_increase_wx_users += u.count
    end
    #当月新增--report_wx_user
    @monthly_wx_user = 0
    ReportWeixinUser.select("added_count as count").where("year= ? and month=? and appid=? ", @check_month.strftime("%Y").to_i, @check_month.strftime("%m").to_i,"superd5c").each do |u|
      @monthly_wx_user += u.count.to_i
    end

    #当月新增--wx_user
    m_sub_user = WxUser.where("(? <= subscribe_time and subscribe_time < ?) and (channel_qrcode_id is not null and channel_qrcode_id != 0)",@check_month.to_i, (@check_month+1.month).to_i)
    @m_sub_user_count = m_sub_user.count()

    m_sub_other =  WxUser.where("(? <= subscribe_time and subscribe_time < ?) and (channel_qrcode_id is null or channel_qrcode_id = 0)",@check_month.to_i,(@check_month+1.month).to_i)
    @m_sub_other_count = m_sub_other.count()

    #当月取消关注人数
      #相关二维码ID为空或者0
    m_unsub_user = WxUser.where("(? <= unsubscribe_time and unsubscribe_time < ?) and (channel_qrcode_id is not null and channel_qrcode_id != 0)",@check_month.to_i,(@check_month+1.month).to_i)
    @m_unsub_user_count = m_unsub_user.count()

    m_unsub_other = WxUser.where("(? <= unsubscribe_time and unsubscribe_time < ?) and (channel_qrcode_id is null or channel_qrcode_id = 0)",@check_month.to_i,(@check_month+1.month).to_i)
    @m_unsub_other_count = m_unsub_other.count()

    #当月渠道“其他"净增
    @m_other_net_increase = @m_sub_other_count - @m_unsub_other_count

    #当月总净增
    @m_net_increase = @m_sub_user_count - @m_unsub_user_count + @m_other_net_increase
    
    # 当月取消关注人数--分渠道
    @m_unsub_groups = [] 
    @m_unsub_cq_ids = []
    m_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)

      @m_unsub_cq_ids << u.channel_qrcode_id #取消关注的用户二维码ID分布
      @m_unsub_groups << {:channel_qrcode => 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