fohow_admin/app/controllers/zt_report_controller.rb

# encoding:utf-8
class ZtReportController < BaseController
  before_filter :authenticate_admin_user!
  def xccj

    render :layout => false
  end

  def fanhongbao
  	time_0125 = Time.new(2017, 1, 27, 20, 0, 0)
    #合体成功总金额单位:分
    @success_total = 0    
    records = ZtFanhongbaoRecord.where("state=?", 1)

    records.each do |record|
      @success_total +=  record.first_num*100 + record.last_num*10
    end

    #合体总次数
    @total_times  = records.length#ZtFanhongbaoRecord.where("state=?", 1).length

    #绑定金额 balance
    @total_balance =  Balance.where("source=? or source=?", "new_year_fanhongbao_", "new_year_fhb_2017").sum("count")        

    #提现金额(金额小于50元的)
    @take_cash_total =  Balance.where("source=? and count > -10000 and created_at > ?", "extract", time_0125).sum("count")

    #新增关注用户
    @new_wx_sub_users = WxUser.where("created_at > ? and subscribe = ?", time_0125, true).length
    #新注册用户
    @new_users = User.where("created_at>? and tel != ''", time_0125).length
    #新增绑定身份证用户
    @binding_id = User.where("created_at>? and identity_card != ''", time_0125).length
    #新增绑卡用户
    @binding_card = UserBankcard.where("created_at>?", time_0125).length

  	render :layout => false
  end

  def share_info    

    # sql = "select sum(first_num*100 + last_num*10) as total from d5c_zt_fanhongbao_records where state=1"
    # @success_total = ZtFanhongbaoRecord.find_by_sql(sql).first.total


    time_0125 = Time.new(2017, 1, 27, 20, 0, 0)

    #参与总人数
    @total_size  = ZtFanhongbaoRecord.group("launcher_id").all.length
    #成功人数
    @success_size = ZtFanhongbaoRecord.where("state=?", 1).group("launcher_id").length
    
    #合体次数用满的人数
    # @full_time_people = ZtFanhongbaoRecord.where("state=?", 1).group("")    
    #分享朋友圈
    @share_timeline = 0 #ShareInfo.where("share_code=? and share_to=?", "spring_szhb", "timeline").length
    #分享人或群
    @share_group = 0#ShareInfo.where("share_code=? and share_to=?", "spring_szhb", "group").length
    ShareInfo.where("share_code=?", "spring_szhb").each do |item|
      if item.share_to == "group"
        @share_group += 1    
      elsif item.share_to == "timeline"
        @share_timeline += 1
      end
    end

    #分享人数
    @share_people = ShareInfo.where("share_code=?", "spring_szhb").group("wx_user_id").length
  end

  def top10
    #发起用户合体次数top10
    sql = "select count(*) as c, launcher_id, d5c_wx_users.nickname, d5c_wx_users.user_id from d5c_zt_fanhongbao_records 
           left join d5c_wx_users on d5c_wx_users.id = launcher_id
           where state=1 group by launcher_id order by c DESC limit 10"
    @launch_top_10 = ZtFanhongbaoRecord.find_by_sql(sql)
    #被邀请合体次数top10
    sql = "select count(*) as c, partner_id, d5c_wx_users.nickname, d5c_wx_users.user_id from d5c_zt_fanhongbao_records 
           left join d5c_wx_users on d5c_wx_users.id = partner_id
           where state=1 group by partner_id order by c DESC limit 10"
    @partner_top_10 = ZtFanhongbaoRecord.find_by_sql(sql)
        
    @money_top_10 = []
    sql = "select sum(count) as total, d5c_balances.user_id, d5c_users.nickname, d5c_users.tel from d5c_balances left join d5c_users on d5c_users.id = d5c_balances.user_id  where (source='new_year_fanhongbao_' or source='new_year_fhb_2017') group by user_id order by total DESC limit 50"
    @money_top_10 = Balance.find_by_sql(sql)
  end

  def fhb_day
    @list = []
    ZtFanhongbaoRecord.select("date_format(open_time, '%Y-%m-%d') date, date_format(open_time, '%d') day, count(*) as c, sum(last_num*10 + first_num*100)/100 as total")
    .where("state=1")
    .group("day").order("day").each do |item| 
      # p "*********** #{item.day}, #{item.c}, #{item.total}" 
      @list << {:day => item.date, :count => item.c, :total => item.total}
    end

    @bk_list = []
    UserBankcard.select("date_format(created_at, '%Y-%m-%d') date, date_format(created_at, '%d') day, count(*) as c")
    .where("created_at > '2017-01-27'")
    .group("day").order("day").each do |item|       
      @bk_list << {:day => item.date, :count => item.c}
    end

  end

  def kj_infos
    id = params[:id] || 0
    @act_infos =[]
    if id == 0
      # sql = "select id, title, purchase_price, open_ad_code, banner_ad_code, stock from d5c_zt_kj_infos where state = 1"
      sql = "select id,title from d5c_zt_kj_infos where state = 1"
      acts = ZtKjInfo.find_by_sql(sql)
      # p "----------------------------------------------------------------------------"
      # # p acts
      # p "----------------------------------------------------------------------------"
      acts.each do |u|
        act_info = get_act_statistic_info_by_act_id(u.id)
        @act_infos << act_info
      end

    else
      act_id = id.to_i
      act_info = get_act_statistic_info_by_act_id(act_id)
      @act_infos << act_info     
    end
    render :layout => false
  end
  # 通过活动id 获取该活动统计信息
  def get_act_statistic_info_by_act_id(act_id)
    act = ZtKjInfo.where("id = ?", act_id).first
    if !act.blank?
      order_sql = "select id from d5c_zt_kj_orders where act_id = #{act_id}"
      join_user_count = ZtKjOrder.find_by_sql(order_sql).count()
      reduce_sql = "select id from d5c_zt_kj_reduces where act_id = #{act_id}"
      kj_count = ZtKjReduce.find_by_sql(reduce_sql).count()
      unit_price = 0
      success_kj_sql = "select id from d5c_zt_kj_orders where act_id = #{act_id} and left_price = #{act.min_price}"
      success_kj_count = ZtKjOrder.find_by_sql(success_kj_sql).count()
      # 成功下单数
      success_order_sql = "select id from d5c_zt_kj_orders where act_id = #{act_id} and left_price = #{act.min_price} and (status = 'processing' or status = 'complete' or status = 'dispatch')"
      success_order_count = ZtKjOrder.find_by_sql(success_order_sql).count()
      # 总成本
      cost = act.purchase_price * success_order_count
      # 分享朋友圈统计
      share_timeline_count = ShareInfo.where("relate_parent = ? and share_to = ? and share_code = ?", act.id, "timeline", "bargain_activity").count()
      share_group_count = ShareInfo.where("relate_parent = ? and share_to = ? and share_code = ?", act.id, "group", "bargain_activity").count()

      click_open_ad_count = 0
      show_open_ad_count = 0
      if !act.open_ad_code.blank?
        ad_items = get_ad_items_by_ad_position_code(act.open_ad_code)
        if !ad_items.blank?
          ad_items.each do |ad_item|
            click_count = get_click_times_by_ad_item(ad_item)
            click_open_ad_count = click_open_ad_count + click_count

            show_count = get_show_times_by_ad_item(ad_item)
            show_open_ad_count = show_open_ad_count + show_count
          end
        end
      end

      click_banner_ad_count = 0
      if !act.banner_ad_code.blank?
        ad_items = get_ad_items_by_ad_position_code(act.banner_ad_code)
        if !ad_items.blank?
          ad_items.each do |ad_item|
            click_count = get_click_times_by_ad_item(ad_item)
            click_banner_ad_count = click_banner_ad_count + click_count
          end
        end
      end

      unit_price = cost/((click_open_ad_count + click_banner_ad_count) * 1.0)
      unit_price = format("%.2f",unit_price).to_f
      act_info = Hash.new
      act_info = Hash[:title => act.title, :count => act.stock, :join_user_count => join_user_count, :kj_count =>kj_count, :show_open_ad_count => show_open_ad_count, :click_open_ad_count => click_open_ad_count, :click_banner_ad_count => click_banner_ad_count, :cost => cost, :unit_price => unit_price, :success_kj_count => success_kj_count, :success_order_count => success_order_count, :share_timeline_count => share_timeline_count, :share_group_count => share_group_count]
      return act_info
    end
  end
  # 通过广告位标识符获取所有广告项
  def get_ad_items_by_ad_position_code(code)
    ad_position = AdPosition.where("code = ?", code).first
    if !ad_position.blank?
      id = ad_position.id
      sql = "select id from d5c_ad_items where ad_position_id = #{id}"
      ad_items = AdItem.find_by_sql(sql)
      if !ad_items.blank?
        return ad_items
      end
    end
  end
  #通过广告项获取点击次数
  def get_click_times_by_ad_item(item)
    count = 0
    AdItemClickStatistic.select("ad_item_id, click_times").where("ad_item_id = ?", item.id).each do |u|
      count = count + u.click_times
    end
    return count
  end
  # 通过广告项获取展示次数
  def get_show_times_by_ad_item(item)
    count = 0
    AdItemShowStatistic.select("ad_item_id, show_times").where("ad_item_id = ?", item.id).each do |u|
      count = count + u.show_times
    end
    return count   
  end
end