package mobvista.dmp.datasource.adn_request_sdk

/**
  * @package: mobvista.dmp.datasource.adn_request_sdk
  * @author: wangjf
  * @date: 2020/4/4
  * @time: 11:13 上午
  * @email: jinfeng.wang@mobvista.com
  * @phone: 152-1062-7698
  */
object Constant {
  val adn_sql: String =
    s"""
       |select
       |   'M' as system,
       |   re as region,
       |   case
       |   when platform in ('ios') and idfa not in ('', '0', '00000000-0000-0000-0000-000000000000', '0000000000000000', '00000000') and length(idfa) > 10 then idfa
       |   when platform in ('android') and gaid not in ('', '0', '00000000-0000-0000-0000-000000000000', '0000000000000000', '00000000') and length(gaid) >10  then gaid
       |   when platform in ('ios') and (length(idfa) < 10 or idfa in ('', '0', '00000000-0000-0000-0000-000000000000', '0000000000000000', '00000000')) then
       |         case
       |         when ext_sysId like "%,%" and length(split(ext_sysId,",")[0])>0 then split(ext_sysId,",")[0]
       |         when ext_sysId like "%,%" and length(split(ext_sysId,",")[0])=0 and length(split(ext_sysId,",")[1])>0 then split(ext_sysId,",")[1]
       |         else '' end
       |   when platform in ('android') and (length(gaid) < 10  or gaid in ('', '0', '00000000-0000-0000-0000-000000000000', '0000000000000000', '00000000'))  then
       |         case
       |         when   (imei not regexp '^0*$$') then imei
       |         when   (imei regexp '^0*$$') then
       |               case
       |               when  (dev_id not regexp "^0*$$") then dev_id
       |               when  (dev_id regexp "^0*$$") then
       |                     case
       |                     when ext_sysId like "%,%" and length(split(ext_sysId,",")[0])>0 then split(ext_sysId,",")[0]
       |                     when ext_sysId like "%,%" and length(split(ext_sysId,",")[0])=0 and length(split(ext_sysId,",")[1])>0 then split(ext_sysId,",")[1]
       |                     else '' end
       |               else '' end
       |         else '' end
       |   else '' end as devid
       |   FROM
       |    dwh.ods_adn_trackingnew_request
       |   WHERE
       |    concat(yyyy, mm, dd) = '20200401'
       |    AND
       |    strategy like ('%MNormalAlphaModelRanker%')
      """.stripMargin

  val dsp_sql: String =
    s"""
       |SELECT
       |   'DSP' as system,
       |   rg as region,
       |   googleadid as devid
       |FROM
       |   adn_dsp.log_adn_dsp_request_orc_hour
       |WHERE
       |    concat(yr, mt, dt) = '20200401'
       |    AND
       |    googleadid not in ('', '0', '00000000-0000-0000-0000-000000000000', '0000000000000000', '00000000')
      """.stripMargin
}