R/mrpt00_runRPA.R

In takewiki/jlrdspkg: package for jlrds app

#' 写入SAP数据
#'
#' @param conn 连接
#' @param FYear 年
#' @param FPeriod 月
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_write_sap()
mrpt_write_sap <- function(conn = tsda::conn_rds('jlrds'),
                           FYear = 2021 ,
                           FPeriod =6) {
  sql_del <- paste0("  delete from  t_mrpt_data_sap
  where FYear = ",FYear," and FPeriod =   ", FPeriod)
  tsda::sql_update(conn,sql_del)
  sql_ins <- paste0("insert into t_mrpt_data_sap
select * from rds_t_mrpt_data_sap2
where FYear = ",FYear," and FPeriod =   ", FPeriod)
  tsda::sql_update(conn,sql_ins)



}

#' 写入BW报表数据
#'
#' @param conn 连接
#' @param FYear 年
#' @param FPeriod 月
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_write_bw2()
mrpt_write_bw2 <- function(conn = tsda::conn_rds('jlrds'),
                           FYear = 2021 ,
                           FPeriod =6) {
  sql_del <- paste0(" delete   from  rds_t_mrpt_data_bw2
  where FYear = ",FYear," and FPeriod =   ", FPeriod)
  tsda::sql_update(conn,sql_del)
  #增加了BW报表分配共享的功能
  sql_ins <- paste0("Insert into  rds_t_mrpt_data_bw2
select FSolutionNumber,FSubNumber,FBrand_o,FChannel_o,FValueType,FValue*FRate as FValue,FYear,FPeriod,FRptItemName_o,FRptItemNumber_o
from rds_t_mrpt_ds_bw_rpa_ruled
where FYear = ",FYear," and FPeriod =   ", FPeriod)
  tsda::sql_update(conn,sql_ins)



}

#' 管报分配数
#'
#' @param conn 连接
#' @param FYear 年
#' @param FPeriod 月
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_res_current()
mrpt_res_allocated <- function(conn = tsda::conn_rds('jlrds'),
                             FYear = 2021 ,
                             FPeriod =6) {
  sql_del <- paste0(" delete from  mrpt2_t_ds_all_Allocated
  where FYear = ",FYear," and FPeriod =   ", FPeriod)
  tsda::sql_update(conn,sql_del)
  #下面增加第1，2步的明细数据
  sql_ins <- paste0("insert into mrpt2_t_ds_all_Allocated
select * from mrpt2_vw_ds_all_Allocated
where FYear = ",FYear," and FPeriod =   ", FPeriod)
  tsda::sql_update(conn,sql_ins)
  #下面增加第3步的货架，大客户的明细数据，用于所有汇总逻辑
  # 现在只计算明细渠信息，不需要再计划大客户,因此取消此功能--2021-11-11
#   sql_ins3 <- paste0("insert into mrpt2_t_ds_all_Allocated
# select * from mrpt2_vw_ds_all_Allocated_step3
# where FYear = ",FYear," and FPeriod =   ", FPeriod)
#   tsda::sql_update(conn,sql_ins3)




}



#' 管报分配数
#'
#' @param conn 连接
#' @param FYear 年
#' @param FPeriod 月
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_res_current()
mrpt_res_allocated_group <- function(conn = tsda::conn_rds('jlrds'),
                               FYear = 2021 ,
                               FPeriod =6) {

  #下面增加第4步，用于全集团的数据汇总
  sql_ins4 <- paste0("insert into mrpt2_t_ds_all_Allocated
select * from mrpt2_vw_ds_all_Allocated_step4
where FYear = ",FYear," and FPeriod =   ", FPeriod)
  tsda::sql_update(conn,sql_ins4)




}


#' 处理中后台费用
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_res_allocated_mpv()
mrpt_res_allocated_mpv <- function(conn = tsda::conn_rds('jlrds'),
                                     FYear = 2021 ,
                                     FPeriod =6) {

  #检出是否存在手工数据
  sql_count <- paste0("   select count(1) as FCount from  mrpt2_vw_ds_mpv_manual
   where FYear = ",FYear," and FPeriod = ",FPeriod," ")
  data_count <- tsda::sql_select(conn,sql_count)
  ncount <- data_count$FCount
  if(ncount >0){
    #存在手工数据,使用手工数据
    sql_ins4 <- paste0("insert into mrpt2_t_ds_all_Allocated
select * from mrpt2_vw_ds_mpv_manual
where FYear = ",FYear," and FPeriod =   ", FPeriod)
  }else{
    #使用RPA数据
    sql_ins4 <- paste0("insert into mrpt2_t_ds_all_Allocated
    select * from mrpt2_vw_ds_mpv_rpa
    where FYear = ",FYear," and FPeriod =   ", FPeriod)

  }
  tsda::sql_update(conn,sql_ins4)
}















#' 报表处理结果当期数
#'
#' @param conn 连接
#' @param FYear 年
#' @param FPeriod 月
#' @param simple 针对模板进行简化处理
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_res_current()
mrpt_res_current <- function(conn = tsda::conn_rds('jlrds'),
                           FYear = 2021 ,
                           FPeriod =6,simple =TRUE) {
  if(simple){
    #简化处理
    sql_del <- paste0(" delete    from  t_mrpt_res
  where FYear = ",FYear," and FPeriod =   ", FPeriod)
    tsda::sql_update(conn,sql_del)
    sql_ins <- paste0("insert into t_mrpt_res
  select    FYear,FPeriod,FBrand,FChannel,FRptItemNumber,FRptItemName,FAcualAmt as FRptAmt from  vw_mrpt_res_level_ALL2
where FYear = ",FYear," and FPeriod =   ", FPeriod)
  }else{
    #保留原有的
    sql_del <- paste0(" delete    from  rds_t_mrpt_res_current_rpa
  where FYear = ",FYear," and FPeriod =   ", FPeriod)
    tsda::sql_update(conn,sql_del)
    sql_ins <- paste0("insert into rds_t_mrpt_res_current_rpa
select *
from   vw_mrpt_res_level_ALL4
where FYear = ",FYear," and FPeriod =   ", FPeriod)
  }

  tsda::sql_update(conn,sql_ins)



}

#' 报表处理结果本年累计数
#'
#' @param conn 连接
#' @param FYear 年
#' @param FPeriod 月
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_res_ytdCumsum()
mrpt_res_ytdCumsum <- function(conn = tsda::conn_rds('jlrds'),
                             FYear = 2021 ,
                             FPeriod =6) {
  sql_del <- paste0(" delete    from  rds_t_mrpt_res_cumsum_rpa
  where FYear = ",FYear," and FPeriod =   ", FPeriod)
  tsda::sql_update(conn,sql_del)
  sql_ins <- paste0("insert into  rds_t_mrpt_res_cumsum_rpa
SELECT [FYear]
      , ",FPeriod," as [FPeriod]
      ,[FBrand]
      ,[FChannel]
      ,[FSubChannel]
      ,[FRptItemNumber]
      ,[FRptItemName]
      ,sum(FAcualAmt) as FAcualCumAmt
      ,sum(FBudgetAmt) as FBudgetCumAmt
  ,case sum(FBudgetAmt) when 0 then 0 else sum(FAcualAmt)/sum(FBudgetAmt) end as  FAchiveCumRatio


      ,sum([FAcualAmt_Lag1]) as FAcualCumAmt_Lag1
  ,case  sum([FAcualAmt_Lag1]) when 0 then 0 else sum(FAcualAmt) /sum([FAcualAmt_Lag1]) end as FAchiveCumRatio_Lag1


      ,sum([FAcualAmt_Lag2]) as FAcualCumAmt_Lag2
  ,case  sum([FAcualAmt_Lag2]) when 0 then 0 else sum(FAcualAmt)/sum([FAcualAmt_Lag2]) end as FAchiveCumRatio_Lag2




  FROM [dbo].[rds_t_mrpt_res_current_rpa]
  where  FYear = ",FYear," and  FPeriod <=   ",FPeriod,"
  group by [FYear], FBrand,FChannel,FSubChannel,FRptItemNumber,FRptItemName")
  tsda::sql_update(conn,sql_ins)

}


#' 报表处理结果当期数
#'
#' @param conn 连接
#' @param FYear 年
#' @param FPeriod 月
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_res_current()
mrpt_T_FI_RPA_rds <- function(conn = tsda::conn_rds('jlrds'),
                             FYear = 2021 ,
                             FPeriod =6) {
  sql_del <- paste0(" delete    from  T_FI_RPA
  where FYear = ",FYear," and FPeriod =   ", FPeriod)
  tsda::sql_update(conn,sql_del)
  sql_ins <- paste0("insert into T_FI_RPA
select * from  rds_vw_T_FI_RPA_all
where  FYear =  ",FYear," and  FPeriod =  ",FPeriod)
  tsda::sql_update(conn,sql_ins)



}

#' 报表处理结果当期数
#'
#' @param conn 连接
#' @param FYear 年
#' @param FPeriod 月
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_T_DETAILFI_RPA_rds()
mrpt_T_DETAILFI_RPA_rds <- function(conn = tsda::conn_rds('jlrds'),
                              FYear = 2021 ,
                              FPeriod =6) {
  sql_del <- paste0(" delete    from  T_DETAILFI_RPA
  where FYear = ",FYear," and FPeriod =   ", FPeriod)
  tsda::sql_update(conn,sql_del)
  sql_ins <- paste0("insert into T_DETAILFI_RPA
SELECT [FYear]
      ,[FPeriod]
      ,[FBrand]
      ,[FChannel]
      ,[FSubChannel]
      ,[FRptItemNumber]
      ,[FRptItemName]
      ,[FAllocAmt]  as FAcualAmt
      ,[FDataSource]
      ,[FSolutionNumber]
      ,[FSubNumber]
      ,[FValueType]
      ,[F13_itemGroupNumber]
      ,[F13_itemGroupName]
      ,[F14_brandNumber]
      ,[F14_brandName]
      ,[F30_customerNumber]
      ,[F30_customerName]
      ,[F33_subChannelNumber]
      ,[F33_subChannelName]
      ,[F37_districtSaleDeptNumber]
      ,[F37_districtSaleDeptName]
      ,[F41_channelName]
      ,[F61_costCenterControlNumber]
      ,[F61_costCenterControlName]
      ,[FSAP_costCenterCode]
      ,[FSAP_costElementName]
      ,[FSAP_voucherNumber]
      ,[FRptAmt] as FSap_vchAmt,
	  '' as FSAP_vchTxt
      ,[FFeeRate] as FSap_ChannelFeeRate
      ,FFeeType as FSap_costCenterType
      ,FRptAmt as FJL_marketFeeAmt
      ,FFeeRate as FJL_marketFeeRate
  FROM [dbo].[mrpt2_t_ds_all_Allocated]
where  FYear =  ",FYear," and  FPeriod =  ",FPeriod)
  tsda::sql_update(conn,sql_ins)



}






#' 计算RPA的自动
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_run()
mrpt_run <- function(conn = tsda::conn_rds('jlrds'),
                     FYear = 2021 ,
                     FPeriod =7
                     ) {
  #同步上月成本中心至本月
  if (jlrdspkg::mrpt_md_ui_costCenter_checkStatus(conn = conn,FYear = FYear,FPeriod = FPeriod)){
    jlrdspkg::mrpt_md_ui_costCenter_syncLastOne(conn = conn,FYear = FYear,FPeriod = FPeriod)
  }
  #同步上月成本要素至本月
  if(jlrdspkg::mrpt_md_ui_costItem_checkStatus(conn = conn,FYear = FYear,FPeriod = FPeriod)){
    jlrdspkg::mrpt_md_ui_costItem_syncLastOne(conn = conn,FYear = FYear,FPeriod = FPeriod)
  }
  #同步上月BW规则至本月
  if(jlrdspkg::mrpt_md_rule_bw2_checkStatus(conn = conn,FYear = FYear,FPeriod = FPeriod)){
    jlrdspkg::mrpt_md_rule_bw2_syncLastOne(conn = conn,FYear = FYear,FPeriod = FPeriod)
  }
  #功能可以独立出来
  print('Step-01:计算BW分配规则')
  mrpt_md_rule_bw2_dim_allocAll(conn = conn,FYear = FYear,FPeriod = FPeriod)

  print('Step-02写入SAP数据')
  mrpt_write_sap(conn = conn,FYear = FYear,FPeriod = FPeriod )
  print('Step03-写入BW数据')
  bw2_sync_data(conn = conn,FYear = FYear,FPeriod = FPeriod)
  print('step04-计算BW报表结果,核心算法')
  bw2_deal_list(conn = conn,FYear = FYear,FPeriod = FPeriod)
  print('step05-将BW处理后的数据写入相应的表')
  mrpt_write_bw2(conn = conn,FYear = FYear,FPeriod = FPeriod)


  print('10-写入管报分配明细数据结果，后续数据都是在此基础上的汇总')
  #mrpt2_vw_ds_all_Allocated 所有已分配的数据
  #mrpt2_vw_ds_all_Allocated_step3 第三步的数据
  mrpt_res_allocated(conn = conn,FYear = FYear,FPeriod = FPeriod)

  # print('10A增加全集团的明细数据')
  # 填写全集团费用没有意义20211024
  # mrpt_res_allocated_group(conn = conn,FYear = FYear,FPeriod = FPeriod)


  # print('10B增加全集团的中后台明细数据')
  # 取消中后台费用 20211024
  # mrpt_res_allocated_mpv(conn = conn,FYear = FYear,FPeriod = FPeriod)


  print('11-计算结果当期数据')
  #算法待改进 1024,计算出本期结果后即提前结束
  #vw_mrpt_res_level_ALL4 包含了历史同比数据,需要向前追溯
  #vw_mrpt_res_level_ALL3 包含了预算数据
  # vw_mrpt_res_level_ALL2 当期数据
  #数据处理
  mrpt_res_current(conn = conn,FYear = FYear,FPeriod = FPeriod,simple = FALSE)



  #print('12-计算累计期数据')
  # 功能不再使用
  mrpt_res_ytdCumsum(conn = conn,FYear = FYear,FPeriod = FPeriod)

  #print('21写入BW结果表')
  #写入结果表应该不用了
  #不再写入结果表,应该追踪表当期数据即可
  #rds_vw_T_FI_RPA_all 视图包含了当期及累计数据
  mrpt_T_FI_RPA_rds(conn = conn,FYear = FYear,FPeriod = FPeriod)



  print('写入BW过程表')
  mrpt_T_DETAILFI_RPA_rds(conn = conn,FYear = FYear,FPeriod = FPeriod)

  #只计算了当期的数据-20220301

  print('写入当期计算数据')
  graph_rpa_period(conn = conn,FYear = FYear,FPeriod = FPeriod)
  print('写入累计 计算数据')
  mrpt_calc_cumSum_Period_res(conn = conn,FYear = FYear,FPeriod = FPeriod)
  #计算去年的历史数据
  graph_actual_period(conn = conn,FYear = FYear -1,FPeriod = FPeriod)
  #计算去年的历史累计数
  mrpt_calc_cumSum_Period(conn = conn,FYear = FYear -1,FPeriod = FPeriod)
  #计算预算数据
  graph_budget_period(conn = conn,FYear = FYear,FPeriod = FPeriod)
  #计算预算累计数据
  mrpt_calc_cumSum_Period_budget(conn = conn,FYear = FYear,FPeriod = FPeriod)

  print('计算差异表')
  mrpt_diff_calc(conn = conn,FYear = FYear,FPeriod = FPeriod)





}


#' 检查
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_run()
mrpt_preCheck <- function(conn = tsda::conn_rds('jlrds'),
                     FYear = 2021 ,
                     FPeriod =6
) {

}

#' 追踪
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_run()
mrpt_traceBack <- function(conn = tsda::conn_rds('jlrds'),
                          FYear = 2021 ,
                          FPeriod =6
) {

}