-- Databricks notebook source
--------------------------------------------------------------------------------
--修改时间：20241025
--修改人：FanXujia
--修改内容：
--协和的报销情况、报销类型、处方来源等字段与产品无关，不能放packinfo表，因此放事实表里
--------------------------------------------------------------------------------
--------------------------------------对协和raw data 数据挂上pack geo信息,并获取本同期数据---------------------------------------------------
insert overwrite table dws.dws_ext_xiehe_raw_data 
select
     yyyymm 
    ,IQVIA_PACK_CODE
    ,AUDIT_COD 
    ,h_level
    ,reimburse
    ,reimburse_type
    ,prescription_source
    ,area
    ,dept_name
    ,new_code
    ,sum(prescription)      prescription
    ,SUM(sales_vol)			counting_unit
    ,SUM(price)             price
    ,SUM(sales_value)       sales_value
    ,sum(prescription_ly)   prescription_ly
    ,SUM(sales_vol_ly)      counting_unit_ly
    ,SUM(price_ly)          price_ly
    ,SUM(sales_value_ly)    sales_value_ly
    ,from_utc_timestamp(current_timestamp(),'UTC+8')	etl_insert_dt
    ,from_utc_timestamp(current_timestamp(),'UTC+8')	etl_update_dt
    FROM (
        select
            cast(concat('20',left(yq,2))*100 + right(yq,1)*3  as int ) yyyymm 
            ,if( b.IQVIA_PACK_CODE REGEXP '^[0-9]',right(concat('000000000000',b.IQVIA_PACK_CODE),12),b.IQVIA_PACK_CODE) IQVIA_PACK_CODE --right(concat('0000000',b.IQVIA_PACK_CODE),7 )
            ,c.AUDIT_COD 
            ,a.h_level
            ,a.reimburse
            ,a.reimburse_type
            ,a.prescription_source
            ,a.area
            ,a.dept_name
            ,a.new_code
            ,a.prescription
            ,a.sales_vol
            ,a.price
            ,a.sales_value
            ,0         as prescription_ly
            ,0         as sales_vol_ly
            ,0         as price_ly
            ,0         as sales_value_ly
        from  dwd.dwd_gnd_ext_xiehe_raw_data a 
        left join (select DISTINCT pack_code,IQVIA_PACK_CODE from dwd.dwd_gnd_xiehe_pack_info) b on upper(b.pack_code) =  upper(a.new_code)
        left join dm.dm_ims_td_geo c on c.CITY_C = a.area 
        union all 
        select
            cast(concat('20',left(yq,2))*100 + right(yq,1)*3 +100  as int ) yyyymm 
            ,if( b.IQVIA_PACK_CODE REGEXP '^[0-9]',right(concat('000000000000',b.IQVIA_PACK_CODE),12),b.IQVIA_PACK_CODE) IQVIA_PACK_CODE --right(concat('0000000',b.IQVIA_PACK_CODE),7 )
            ,c.AUDIT_COD 
            ,a.h_level
            ,a.reimburse
            ,a.reimburse_type
            ,a.prescription_source
            ,a.area
            ,a.dept_name
            ,a.new_code
            ,0                       as prescription
            ,0                       as sales_vol    
            ,0                       as price      	
            ,0                       as sales_value  
            ,a.prescription          as prescription_ly
            ,a.sales_vol             as sales_vol_ly
            ,a.price      			 as price_ly
            ,a.sales_value      	 as sales_value_ly
        from  dwd.dwd_gnd_ext_xiehe_raw_data a 
        left join (select DISTINCT pack_code,IQVIA_PACK_CODE from dwd.dwd_gnd_xiehe_pack_info) b on upper(b.pack_code) =  upper(a.new_code)
        left join dm.dm_ims_td_geo c on c.CITY_C = a.area 
        where cast(concat('20',left(yq,2))*100 + right(yq,1)*3 +100  as int )
            <=(SELECT MAX(cast(concat('20',left(yq,2))*100 + right(yq,1)*3 as int )) from dwd.dwd_gnd_ext_xiehe_raw_data )
    )
GROUP BY 
     yyyymm 
    ,IQVIA_PACK_CODE
    ,AUDIT_COD 
    ,h_level
    ,reimburse
    ,reimburse_type
    ,prescription_source
    ,area
    ,dept_name
    ,new_code

-- COMMAND ----------

----------raw data 数据抽取到dm------------
insert overwrite table dm.dm_ext_xiehe_sales 
select 
    yyyymm
    ,IQVIA_PACK_CODE
    ,AUDIT_COD
    ,h_level
    ,reimburse
    ,reimburse_type
    ,prescription_source
    ,area
    ,dept_name
    ,new_code
    ,prescription
    ,counting_unit
    ,price
    ,sales_value
    ,prescription_ly
    ,counting_unit_ly
    ,price_ly
    ,sales_value_ly
from dws.dws_ext_xiehe_raw_data