助力工业物联网，工业大数据之费用事实指标分析及实现【二十四】

select
    id,--报销单id
    create_user_id,--创建人id
    submoney5, --报销金额
    create_org_id --创建部门id
from ciss_service_expense_report;

select
    id,--服务网点id
    org_id --部门id
from ciss_base_servicestation;

select
    exp_report_id,--报销单id
    submoney5,--项目报销实际金额
    item_id --费用项目id
from ciss_service_exp_report_dtl;

select
       dictid, --项目id
       dictname --项目名称
from one_make_dwb.tmp_dict where dicttypename = '费用报销项目';

drop table if exists one_make_dwb.fact_regular_exp;
create table if not exists one_make_dwb.fact_regular_exp(
      exp_id string comment '费用报销id'
    , ss_id string comment '服务网点id'
    , srv_user_id string comment '服务人员id'
    , actual_exp_money decimal(20,1) comment '费用实际报销金额'
    , exp_item string comment '费用项目id'
    , exp_item_name string comment '费用项目名称'
    , exp_item_money decimal(20,1) comment '费用项目实际金额'
)
partitioned by (dt string)
stored as orc
location '/data/dw/dwb/one_make/fact_regular_exp';

insert overwrite table one_make_dwb.fact_regular_exp partition(dt = '20210101')
select
    /*+repartitions(1) */
    exp.id as exp_id                           --费用报销id
    , ss.id as ss_id                           --服务网点id
    , exp.create_user_id as srv_user_id        --创建人id
    , exp.submoney5 as actual_exp_money        --实际报销金额
    , dict.dictid as exp_item                  --费用项目id
    , dict.dictname as exp_item_name           --费用项目名称
    , exp_dtl.submoney5 as exp_item_money      --费用项目金额
from
--费用信息表
(
    select
      *
  from one_make_dwd.ciss_service_expense_report
    where dt = '20210101' and status = 9 --只取制证会计已审状态
) exp
--服务网点信息表
left join one_make_dwd.ciss_base_servicestation ss
on ss.dt = '20210101' and ss.org_id = exp.create_org_id
--报销明细表
left join one_make_dwd.ciss_service_exp_report_dtl exp_dtl
on exp_dtl.dt = '20210101' and exp.id = exp_dtl.exp_report_id
--数据字典表
left join one_make_dwb.tmp_dict dict
on dict.dicttypename = '费用报销项目' and dict.dictid = exp_dtl.item_id
;

select
    id,--汇总报销单id
    user_id,--报销人id【工程师id】
    status,--汇总单状态：15表示审核通过
    submoney5 --应收报销总金额
from ciss_service_trvl_exp_sum;

select
    exp_report_id,--汇总报销单id
    workorder_travel_exp_id --工单费用单id
from ciss_s_exp_report_wo_payment;

select
    id,--差旅报销单id
    work_order_id --工单id
from ciss_service_travel_expense;

select
    id,--工单id
    service_station_id --服务网点id
from ciss_service_workorder;

select
    travel_expense_id,--费用单id
    item,--费用项目名称
    submoney5 --费用金额
from ciss_service_trvl_exp_dtl;

drop table if exists one_make_dwb.fact_trvl_exp;
create table if not exists one_make_dwb.fact_trvl_exp(
      trvl_exp_id string comment '差旅报销单id'
    , ss_id string comment '服务网点id'
    , srv_user_id string comment '服务人员id'
    , biz_trip_money decimal(20,1) comment '外出差旅费用金额总计'
    , in_city_traffic_money decimal(20,1) comment '市内交通费用金额总计'
    , hotel_money decimal(20,1) comment '住宿费费用金额总计'
    , fars_money decimal(20,1) comment '车船费用金额总计'
    , subsidy_money decimal(20,1) comment '补助费用金额总计'
    , road_toll_money decimal(20,1) comment '过桥过路费用金额总计'
    , oil_money decimal(20,1) comment '油费金额总计'
    , secondary_money decimal(20,1) comment '二单补助费用总计'
    , third_money decimal(20,1) comment '三单补助费用总计'
    , actual_total_money decimal(20,1) comment '费用报销总计'
)
partitioned by (dt string)
stored as orc
location '/data/dw/dwb/one_make/fact_trvl_exp';

insert overwrite table one_make_dwb.fact_trvl_exp partition(dt = '20210101')
select
  --差旅费汇总单id
    exp_sum.id as trvl_exp_id
  --服务网点id
    , wrk_odr.service_station_id as ss_id
  --服务人员id
    , exp_sum.user_id as srv_user_id
  --外出差旅费用金额总计
    , sum(case when trvl_dtl_sum.item = 1 then trvl_dtl_sum.item_money else 0 end) as biz_trip_money
    --市内交通费用金额总计
  , sum(case when trvl_dtl_sum.item = 2 then trvl_dtl_sum.item_money else 0 end) as in_city_traffic_money
    --住宿费费用金额总计
  , sum(case when trvl_dtl_sum.item = 3 then trvl_dtl_sum.item_money else 0 end) as hotel_money
    --车船费用金额总计
  , sum(case when trvl_dtl_sum.item = 4 then trvl_dtl_sum.item_money else 0 end) as fars_money
    --补助费用金额总计
  , sum(case when trvl_dtl_sum.item = 5 then trvl_dtl_sum.item_money else 0 end) as subsidy_money
    --过桥过路费用金额总计
  , sum(case when trvl_dtl_sum.item = 6 then trvl_dtl_sum.item_money else 0 end) as road_toll_money
    --油费金额总计
  , sum(case when trvl_dtl_sum.item = 7 then trvl_dtl_sum.item_money else 0 end) as oil_money
    --二单补助费用总计
  , sum(case when trvl_dtl_sum.item = 8 then trvl_dtl_sum.item_money else 0 end) as secondary_money
    --三单补助费用总计
  , sum(case when trvl_dtl_sum.item = 9 then trvl_dtl_sum.item_money else 0 end) as third_money
    --费用报销总计
  , max(exp_sum.submoney5) as actual_total_money
--差旅报销汇总单
from one_make_dwd.ciss_service_trvl_exp_sum exp_sum
--汇总报销单与工单费用单对照表
inner join one_make_dwd.ciss_s_exp_report_wo_payment r on exp_sum.dt = '20210101' and r.dt = '20210101' and exp_sum.id = r.exp_report_id and exp_sum.status = 15
--差旅报销单信息表
inner join one_make_dwd.ciss_service_travel_expense exp on exp.dt = '20210101' and exp.id = r.workorder_travel_exp_id
--工单信息表
inner join one_make_dwd.ciss_service_workorder wrk_odr on wrk_odr.dt = '20210101' and wrk_odr.id = exp.work_order_id
--获取每种费用项目总金额
inner join  (
        select
          travel_expense_id, item, sum(submoney5) as item_money
        from one_make_dwd.ciss_service_trvl_exp_dtl
        where dt = '20210101'
        group by travel_expense_id, item
    ) as trvl_dtl_sum
  on trvl_dtl_sum.travel_expense_id = exp.id
group by exp_sum.id, wrk_odr.service_station_id, exp_sum.user_id
;

select
    id,--申请单id
    code,--申请单编号
    service_station_code,--网点编号
    logistics_type,--物流公司类型
    logistics_company,--物流公司名称
    warehouse_code --仓库id
from ciss_material_wdwl_sqd;

select
    id,--服务网点id
    code --服务网点编号
from ciss_base_servicestation;

select
    wdwl_sqd_id,--申请单id
    application_reason,--申请理由
    count_approve,--审核数量
    price,--单价
    count --个数
from ciss_material_wdwl_sqd_dtl;

create table if not exists one_make_dwb.fact_srv_stn_ma(
      ma_id string comment '申请单id'
    , ma_code string comment '申请单编码'
    , ss_id string comment '服务网点id'
    , logi_id string comment '物流类型id'
    , logi_cmp_id string comment '物流公司id'
    , warehouse_id string comment '仓库id'
    , total_m_num decimal(10,0) comment '申请物料总数量'
    , total_m_money decimal(10,1) comment '申请物料总金额'
    , ma_form_num decimal(10,0) comment '申请单数量'
    , inst_m_num decimal(10,0) comment '安装申请物料数量'
    , inst_m_money decimal(10,1) comment '安装申请物料金额'
    , bn_m_num decimal(10,0) comment '保内申请物料数量'
    , bn_m_money decimal(10,1) comment '保内申请物料金额'
    , rmd_m_num decimal(10,0) comment '改造申请物料数量'
    , rmd_m_money decimal(10,1) comment '改造申请物料金额'
    , rpr_m_num decimal(10,0) comment '维修申请物料数量'
    , rpr_m_money decimal(10,1) comment '维修申请物料金额'
    , sales_m_num decimal(10,0) comment '销售申请物料数量'
    , sales_m_money decimal(10,1) comment '销售申请物料金额'
    , insp_m_num decimal(10,0) comment '巡检申请物料数量'
    , insp_m_money decimal(10,1) comment '巡检申请物料金额'
)
partitioned by (dt string)
stored as orc
location '/data/dw/dwb/one_make/fact_srv_stn_ma';

insert overwrite table one_make_dwb.fact_srv_stn_ma partition(dt = '20210101')
select    
  /*+repartition(1) */ 
    ma.id as ma_id,                          --物料申请单id
  ma.code as ma_code,                        --申请单编号
  stn.id as ss_id,                           --服务网点id
  ma.logistics_type as logi_id,              --物流类型id
  ma.logistics_company as logi_cmp_id,       --物流公司id
    ma.warehouse_code as warehouse_id,         --仓库id
  sum(m_smry.cnt) as total_m_num ,           --申请物料总数量
  sum(m_smry.money) as total_m_money,        --申请物料总金额
    count(1) as ma_form_num,                   --申请单数量
  sum(case when m_smry.ma_rsn = 1 then m_smry.cnt else 0 end) as inst_m_num,        --安装申请物料数量   
    sum(case when m_smry.ma_rsn = 1 then m_smry.money else 0 end) as inst_m_money,    --安装申请物料金额
    sum(case when m_smry.ma_rsn = 2 then m_smry.cnt else 0 end) as bn_m_num,          --保内申请物料数量
    sum(case when m_smry.ma_rsn = 2 then m_smry.money else 0 end) as bn_m_money,      --保内申请物料金额
    sum(case when m_smry.ma_rsn = 3 then m_smry.cnt else 0 end) as rmd_m_num,         --改造申请物料数量
    sum(case when m_smry.ma_rsn = 3 then m_smry.money else 0 end) as rmd_m_money,     --改造申请物料金额
    sum(case when m_smry.ma_rsn = 4 then m_smry.cnt else 0 end) as rpr_m_num,         --维修申请物料数量
    sum(case when m_smry.ma_rsn = 4 then m_smry.money else 0 end) as rpr_m_money,     --维修申请物料金额
    sum(case when m_smry.ma_rsn = 5 then m_smry.cnt else 0 end) as sales_m_num,       --销售申请物料数量
    sum(case when m_smry.ma_rsn = 5 then m_smry.money else 0 end) as sales_m_money,   --销售申请物料金额
    sum(case when m_smry.ma_rsn = 6 then m_smry.cnt else 0 end) as insp_m_num,        --巡检申请物料数量
    sum(case when m_smry.ma_rsn = 6 then m_smry.money else 0 end) as insp_m_money     --巡检申请物料金额
--物料申请信息表:8为审核通过
from (
    select * 
    from one_make_dwd.ciss_material_wdwl_sqd 
    where dt = '20210101' and status = 8 
   ) ma
--关联站点信息表，获取站点id
left join one_make_dwd.ciss_base_servicestation stn 
  on stn.dt = '20210101' and ma.service_station_code = stn.code
--关联物料申请费用明细
left join (
       select 
        dtl.wdwl_sqd_id as wdwl_sqd_id, 
        dtl.application_reason as ma_rsn, 
        sum(dtl.count_approve) as cnt,
                sum(dtl.price * dtl.count) as money
             from one_make_dwd.ciss_material_wdwl_sqd_dtl dtl
       where dtl.dt = '20210101'
       group by dtl.wdwl_sqd_id, dtl.application_reason
          ) m_smry on m_smry.wdwl_sqd_id = ma.id
group by ma.id, ma.code, stn.id, ma.logistics_type, ma.logistics_company, ma.warehouse_code
;