package com.kaishu.warehouse.ks.ads

import com.kaishu.tools.{DateUtils, SparkManager}
import org.apache.spark.SparkConf
import org.apache.spark.sql.{SaveMode, SparkSession}

/**
 * 创建人:  xiaotao
 * 创建日期: Created on  2022-05-26
 * 数据开发功能描述: 新用户收入日报表2
 * 目标收益业务方提供
 *  4月“2022年新用户收益”目标3000000，5月3000000，6月3610000
 *  4月新用户14日内收益目标1340000不变，5月1500000，6月1460000
 */

object ads_ks_new_user_income_daily2_i_d {

  val TableInfo = "ads.ads_ks_new_user_income_daily2_i_d" -> "新用户收入日报表2"

  def main(args: Array[String]): Unit = {
    val conf = new SparkConf().setAppName("ads_ks_new_user_income_daily2_i_d")
      .set("hive.exec.dynamic.partition", "true")
      .set("hive.exec.max.dynamic.partitions", "10000")
      .set("hive.exec.dynamic.partition.mode", "nonstrict")

    val spark = SparkManager.getInstance_hive(conf)

    // 设定开始和结束时间
    val instance = DateUtils.getInstance
    var start_date = ""
    var end_date = ""
    var quarter = ""
    var month = ""

    if (args.length == 3 && args(0) == "new") {
      start_date = args(1)
      end_date = args(2)
      while (start_date != end_date) {
        month = start_date.substring(5,7)
        quarter = getQuarter(month)
        new_insert(spark, start_date, end_date, quarter)
        start_date = instance.subDateByDate(start_date, 1)

      }

    } else {
      //默认更新昨天的数据
      start_date = instance.getYesterday
      month = start_date.substring(5,7)
      quarter = getQuarter(month)
      new_insert(spark, start_date, start_date, quarter)
    }
  }

  /**
   * 2022-05-26
   * @param x 月 05
   * @return quarter季度 Q2
   */
  def getQuarter(x:String): String = x match {
    case "01" => "Q1"
    case "02" => "Q1"
    case "03" => "Q1"
    case "04" => "Q2"
    case "05" => "Q2"
    case "06" => "Q2"
    case "07" => "Q3"
    case "08" => "Q3"
    case "09" => "Q3"
    case "10" => "Q4"
    case "11" => "Q4"
    case "12" => "Q4"
  }

  def new_insert(spark: SparkSession, start_date: String = null, end_date: String = null, quarter: String = null): Unit = {

    spark.sql(
      s"""
         |
         |select 9770000                                  as year_new_user_income_target_q      --取Qx22年新用户目标 每个季度修改
         |      ,year_new_user_income_practical_q                                               --取Qx22年新用户实际收入（当前季度累计值）
         |      ,year_new_user_income_practical_q/9770000 as year_new_user_income_finish_rate_q --实际/目标
         |      ,3000000                                  as year_new_user_income_target_m      --取x月22年新用户目标 每个月修改
         |      ,year_new_user_income_practical_m                                               --取x月22年新用户目标（当月累计值）
         |      ,year_new_user_income_practical_m/3000000 as year_new_user_income_finish_rate_m --实际/目标
         |      ,cast(substr(date_sub(current_date(),1),9,10) as int) /
         |                (datediff(last_day(date_sub(current_date(),1)),trunc(date_sub(current_date(),1),'MM')) + 1)  as year_new_user_income_time_plan --收入统计时间/当月时间
         |      ,4500000                                  as mon_new_user_income_target_q       --取Qx新用户14日内收益目标	每个季度修改
         |      ,mon_new_user_income_practical_q                                                --取Qx新用户14日内收益目标（当前季度累计值）
         |      ,mon_new_user_income_practical_q/4500000  as mon_new_user_income_finish_rate_q  --实际/目标
         |      ,1500000                                  as mon_new_user_income_target_m       --取x月新用户14日内收益目标 每个月修改
         |      ,mon_new_user_income_practical_m                                                --取x月新用户14日内收益目标（当月累计值）
         |      ,mon_new_user_income_practical_m/1500000  as mon_new_user_income_finish_rate_m  --实际/目标
         |      ,cast(substr(date_sub(current_date(),1),9,10) as int) /
         |               (datediff(last_day(date_sub(current_date(),1)),trunc(date_sub(current_date(),1),'MM')) + 1)  as mon_new_user_income_time_plan --收入统计时间/当月时间
         |      ,'2022-05-25' as pts
         |from (
         |      select o3.quarter
         |            ,max(case when o3.month = '全部' then gmv else 0 end) as year_new_user_income_practical_q
         |            ,max(case when o3.month = '全部' then pay_amount_14_gmv else 0 end) as mon_new_user_income_practical_q
         |            ,max(case when o3.month = substr(date_sub(current_date(),1),0,7) then gmv else 0 end) as year_new_user_income_practical_m
         |            ,max(case when o3.month = substr(date_sub(current_date(),1),0,7) then pay_amount_14_gmv else 0 end) as mon_new_user_income_practical_m
         |      from (
         |             select quarter --季度
         |                   ,nvl(month,'全部') as `month` --月
         |                   ,sum(pay_amount) as gmv --2022年新用户收入当前季度累计，当前月累计
         |             from (
         |                   select
         |                          month
         |                         ,case when quarter_month in ('01','02','03') then 'Q1'
         |                               when quarter_month in ('04','05','06') then 'Q2'
         |                               when quarter_month in ('07','08','09') then 'Q3'
         |                               when quarter_month in ('10','11','12') then 'Q4'
         |                          end as quarter --季度
         |                         ,pay_amount
         |                   from (
         |                          select substr(order_time,0,7) as month --月
         |                                ,substr(order_time,6,2) as quarter_month
         |                                ,pay_amount
         |                          from  dws.dws_ks_device_channel_detail_a_d a
         |                                    left join dws.dws_ks_order_virtual_device_detail_s_d b on a.device_id=b.device_id
         |                                    left join dim.dim_product_audio_video_type c on b.product_id=c.product_id
         |                          where b.pts=date_sub(`current_date`(),1)
         |                            and  b.content_type in (1,2,3,15)
         |                            --and b.pay_amount_type in ('新设备新账号','新设备回流账号')
         |                            and c.product_id is null
         |                            and pay_amount_type not in ('新设备活跃账号')
         |                            and b.order_time>='2022-01-01' --2022年订单
         |                            and b.order_time<='$start_date'
         |                            and a.activate_time>='2022-01-01' --写死的时间不要修改 2022年新用户
         |                            and a.activate_time<='$start_date'
         |                         ) o1
         |             ) o2
         |             group by quarter,month
         |                 grouping sets ((quarter),(quarter,month))
         |      ) o3
         |      left join (
         |                 select quarter --季度
         |                       ,nvl(month,'全部') as `month` --月
         |                       ,sum(pay_amount_14) as pay_amount_14_gmv --x月新用户14天内收入当前季度累计，当前月累计
         |                 from (
         |                        select month --年月
         |                              ,case when quarter_month in ('01','02','03') then 'Q1'
         |                                    when quarter_month in ('04','05','06') then 'Q2'
         |                                    when quarter_month in ('07','08','09') then 'Q3'
         |                                    when quarter_month in ('10','11','12') then 'Q4'
         |                               end as quarter --季度
         |                              ,pay_amount_14
         |                        from (
         |                               select substr(pts,0,7) as month -- 需要改成月份
         |                                     ,substr(pts,6,2) as quarter_month
         |                                     ,pay_amount_14
         |                               from ads.ads_ks_new_growth_daily_data_day_a_d
         |                               where pts>='2022-01-01' --每年修改一次
         |                                and pts<='$start_date'
         |                                and project_code='全部'
         |                                and channel_two_level='全部'
         |                                and channel_one_level='全部'
         |                             ) o1
         |                 ) o2
         |                 group by quarter,month
         |                     grouping sets ((quarter),(quarter,month))
         |      ) o4
         |      on o3.quarter = o4.quarter and o3.month = o4.month
         |      where o3.quarter='$quarter' and o3.month in (substr('$start_date',0,7),'全部')
         |      group by o3.quarter
         |) o5
         |
              """.stripMargin).coalesce(1).write.mode(SaveMode.Overwrite).insertInto(TableInfo._1)

  }
}