R/rbu_calc.R

In takewiki/jlrdspkg: package for jlrds app

#' 写入临时表
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#' @param res_table 临时表
#' @param src_table 费用分配表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_res_pushTemp()
mrpt_res_pushTemp_allocated <- function(conn = tsda::conn_rds('jlrds'),
                              FYear = 2021 ,
                              FPeriod =6,
                              res_table = 't_mrpt_res_tmp',
                              src_table ='mrpt2_t_ds_all_Allocated') {
#删除已有数据
sql_del <- paste0(" delete from  ",res_table,"
 where FYear = ",FYear," and FPeriod = ",FPeriod,"")
tsda::sql_update(conn,sql_del)
#从已经分配的数据中得出汇总数据
#将渠道为空的剔除
#将报表项目为空的剔除
# 待处理问题
# 品牌渠道中处理了集团、大客户、发展部合计、货架等信息

  sql <- paste0(" insert into  ",res_table,"
  select FYear,FPeriod,FBrand,FChannel,FRptItemNumber,FRptItemName,sum(FAllocAmt)
 as FRptAmt
 from  ",src_table,"
 where FYear = ",FYear," and FPeriod = ",FPeriod,"  and FChannel is not null and FRptItemNumber is not null
 group by FYear,FPeriod,FBrand,FChannel,FRptItemNumber,FRptItemName ")
  tsda::sql_update(conn,sql)
}



#' 整体公司零售的零售
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#' @param res_table 结果表
#' @param src_table 来源表
#' @param FRptItemNumber  报表项目代码
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_res_pushTemp_RetailSales()
mrpt_res_pushTemp_RetailSales <- function(conn = tsda::conn_rds('jlrds'),
                              FYear = 2021 ,
                              FPeriod =6,
                              res_table = 't_mrpt_res_tmp',
                              src_table ='vw_mrpt_res_I02_RetailSales',
                              FRptItemNumber ='I02'
                              ) {

  sql_del <- paste0(" delete from  ",res_table,"
 where FYear = ",FYear," and FPeriod = ",FPeriod," and FRptItemNumber ='",FRptItemNumber,"'")
  tsda::sql_update(conn,sql_del)
#添加相应的数据
sql <- paste0("insert into   ",res_table,"
              select * from  ",src_table, "
              where FYear =  ",FYear," and FPeriod = ",FPeriod,"")
tsda::sql_update(conn,sql)

}


#' 整体公司零售的收款数据
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#' @param res_table 结果表
#' @param src_table 来源表
#' @param FRptItemNumber  报表项目代码
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_res_pushTemp_RetailSales()
mrpt_res_pushTemp_receive <- function(conn = tsda::conn_rds('jlrds'),
                                          FYear = 2021 ,
                                          FPeriod =6,
                                          res_table = 't_mrpt_res_tmp',
                                          src_table ='vw_mrpt_res_I03_receive',
                                          FRptItemNumber ='I03'
) {

  sql_del <- paste0(" delete from  ",res_table,"
 where FYear = ",FYear," and FPeriod = ",FPeriod," and FRptItemNumber ='",FRptItemNumber,"'")
  tsda::sql_update(conn,sql_del)
  #添加相应的数据
  sql <- paste0("insert into   ",res_table,"
              select * from  ",src_table, "
              where FYear =  ",FYear," and FPeriod = ",FPeriod,"")
  tsda::sql_update(conn,sql)

}



#' 增值报表的单位转换
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param res_table 结果表
#' @param src_table 报表名
#' @param unit 报表金额单位
#' @param digit 小数位数
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_resTemp_UnitWan()
mrpt_resTemp_UnitWan <- function(conn = tsda::conn_rds('jlrds'),
                                      FYear = 2021 ,
                                      FPeriod =6,
                                      res_table = 't_mrpt_res_tmpWan',
                                      src_table ='t_mrpt_res_tmp',
                                      unit =10000,
                                      digit =2


) {

  sql_del <- paste0(" delete from  ",res_table,"
 where FYear = ",FYear," and FPeriod = ",FPeriod,"")
  tsda::sql_update(conn,sql_del)
  #添加相应的数据
  sql <- paste0("insert into   ",res_table,"
                select FYear,FPeriod,FBrand,FChannel,FRptItemNumber,FRptItemName,round(sum(FRptAmt)/",unit,",",digit,") as FRptAmt from  ",src_table,
                " where FYear = ",FYear," and FPeriod = ",FPeriod,
                "  group by FYear,FPeriod,FBrand,FChannel,FRptItemNumber,FRptItemName")
  tsda::sql_update(conn,sql)

}



#' 增加单位补全功能
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param res_table 结果表
#' @param graph_table 新增一个图的数据
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_resTemp_UnitWan()
mrpt_resTemp_AppendZero <- function(conn = tsda::conn_rds('jlrds'),
                                 FYear = 2021 ,
                                 FPeriod =6,
                                 res_table = 't_mrpt_res_tmpWan',
                                 graph_table = 'vw_mrpt_md_brandchannel_graph_calc'



) {

  #不执行删除功能
  #添加相应的数据,根据图表所做数据应用更好一些
  sql <- paste0(" insert into   ",res_table,"
  select  ",FYear," as FYear, ",FPeriod," as FPeriod,  source_brand as FBrand,source_channel as FChannel  ,
  b.FRptItemNumber,b.FRptItemName,0 as FRptAmt
  from ",graph_table,"   a,
  t_mrpt_rptItem b  ")
  tsda::sql_update(conn,sql)

}



#' 将数据写入结果表
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param src_table  来源表
#' @param res_table 结果表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_resTemp_UnitWan()
mrpt_resTemp_sumup <- function(conn = tsda::conn_rds('jlrds'),
                                    FYear = 2021 ,
                                    FPeriod =6,
                                    res_table = 't_mrpt_res',
                                    src_table ='t_mrpt_res_tmpWan'



) {

  #删除结果表的内容
  sql_del <- paste0(" delete from  ",res_table,"
 where FYear = ",FYear," and FPeriod = ",FPeriod,"")
  tsda::sql_update(conn,sql_del)
  #增加相关内容
  sql <- paste0("insert into ",res_table,"
  select FYear,FPeriod,FBrand,FChannel,FRptItemNumber,FRptItemName,sum(FRptAmt) as FRptAmt from ",src_table,"
   where FYear = ",FYear," and FPeriod = ",FPeriod,
                "  group by FYear,FPeriod,FBrand,FChannel,FRptItemNumber,FRptItemName")
  tsda::sql_update(conn,sql)

}



#' 针对指标进行计算
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param FBrand 品牌
#' @param FChannel 渠道
#' @param res_table 结果表
#' @param FRptItemNumber_from 报表项目代码从
#' @param FOperator 操作符
#' @param FRptItemNumber_end 报表项目到
#' @param FRptItemNumber_udp  待更新字段
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_index_baseType()
mrpt_index_baseType<- function(conn=tsda::conn_rds('jlrds'),
                                      FYear =2021,
                                      FPeriod =6,
                                      FBrand ='自然堂',
                                      FChannel='美妆',
                                      res_table = 't_mrpt_res',
                                      FRptItemNumber_from='I04',
                                         FOperator = '-',
                                      FRptItemNumber_end ='I06',
                                      FRptItemNumber_udp ='I07'



){
  sql <- paste0("select   FRptItemNumber,FRptAmt from  ",res_table,"
where fyear = ",FYear," and fperiod = ",FPeriod,"  and
fbrand ='",FBrand,"' and FChannel ='",FChannel,"' and FRptItemNumber in  ('",FRptItemNumber_from,"','",FRptItemNumber_end,"')")
  cat(sql)


  data <- tsda::sql_select(conn,sql)

  ncount = nrow(data)
  if(ncount >0){

    data_from = data[data$FRptItemNumber == FRptItemNumber_from,'FRptAmt']
    #针对第二项没有的情况进行处理
    if(ncount == 1){
      data_end = 0
    }else{
      data_end = data[data$FRptItemNumber == FRptItemNumber_end,'FRptAmt']
    }

    if (FOperator == '+' ){
      data_res = data_from + data_end

    }

    if (FOperator == '-' ){
      data_res = data_from - data_end

    }

    if (FOperator == '*' ){
      data_res = data_from * data_end

    }

    if (FOperator == '/' ){
      #兼容收入为0的情况

      if (data_end == 0 ){
        data_res = 0
      }else{
        data_res = round(data_from / data_end,4)
      }
    }

 sql_update = paste0("update  a  set  FRptAmt  =  ",data_res,"  from  ",res_table," a
where fyear = ",FYear," and fperiod = ",FPeriod,"  and
fbrand ='",FBrand,"' and FChannel ='",FChannel,"' and FRptItemNumber  ='",FRptItemNumber_udp,"'")
    cat(sql_update)
    tsda::sql_update(conn,sql_update)

  }




}


#' 针对指标汇总产生的字段
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#' @param FBrand 品牌
#' @param FChannel 渠道
#' @param res_table 结果表
#' @param FRptItemNumber_from 从
#' @param FRptItemNumber_end 到
#' @param FRptItemNumber_udp 待更新字段
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_index_sumup()
mrpt_index_sumup<- function(conn=tsda::conn_rds('jlrds'),
                               FYear =2021,
                               FPeriod =6,
                               FBrand ='自然堂',
                               FChannel='美妆',
                               res_table = 't_mrpt_res',
                               FRptItemNumber_from='I27.01',
                               FRptItemNumber_end ='I43',
                               FRptItemNumber_udp ='I27'



){
  sql <- paste0("select sum(FRptAmt) as FRptAmt from  ",res_table,"
where fyear = ",FYear," and fperiod = ",FPeriod,"  and
fbrand ='",FBrand,"' and FChannel ='",FChannel,"' and FRptItemNumber between  '",FRptItemNumber_from,"' and '",FRptItemNumber_end,"'   ")
  cat(sql)


  data <- tsda::sql_select(conn,sql)

  ncount = nrow(data)
  if(ncount >0){

    data_res = data$FRptAmt[1]



    sql_update = paste0("update  a  set  FRptAmt  =  ",data_res,"  from  ",res_table," a
where fyear = ",FYear," and fperiod = ",FPeriod,"  and
fbrand ='",FBrand,"' and FChannel ='",FChannel,"' and FRptItemNumber  ='",FRptItemNumber_udp,"'")
    cat(sql_update)
    tsda::sql_update(conn,sql_update)

  }




}






#' 报表指标搏缝版
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param FBrand 品牌
#' @param FChannel 渠道
#' @param res_table 结果表作为参数，增加灵活性
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_index_update()
mrpt_index_update<- function(conn=tsda::conn_rds('jlrds'),
                            FYear =2021,
                            FPeriod =6,
                            FBrand ='自然堂',
                            FChannel='美妆',
                            res_table = 't_mrpt_res'
){
  #更新渠道费用
  mrpt_index_sumup(conn = conn,FYear = FYear,FPeriod = FPeriod,FBrand = FBrand,FChannel = FChannel, res_table = res_table,
                   FRptItemNumber_from='I09',FRptItemNumber_end ='I25' ,FRptItemNumber_udp ='I08'  )
  #更新市场费用合计
  mrpt_index_sumup(conn = conn,FYear = FYear,FPeriod = FPeriod,FBrand = FBrand,FChannel = FChannel, res_table = res_table,
                   FRptItemNumber_from='I27.01',FRptItemNumber_end ='I43' ,FRptItemNumber_udp ='I27'  )
  #更新毛利
  mrpt_index_baseType(conn = conn,FYear = FYear,FPeriod = FPeriod,FBrand = FBrand,FChannel = FChannel, res_table = res_table,
                   FRptItemNumber_from='I04',FRptItemNumber_end ='I05' ,FRptItemNumber_udp ='I06',FOperator = '-'  )
  #更新毛利率
  mrpt_index_baseType(conn = conn,FYear = FYear,FPeriod = FPeriod,FBrand = FBrand,FChannel = FChannel, res_table = res_table,
                      FRptItemNumber_from='I06',FRptItemNumber_end ='I04' ,FRptItemNumber_udp ='I07',FOperator = '/'  )
  #计算渠道利润
  mrpt_index_baseType(conn = conn,FYear = FYear,FPeriod = FPeriod,FBrand = FBrand,FChannel = FChannel, res_table = res_table,
                      FRptItemNumber_from='I06',FRptItemNumber_end ='I08' ,FRptItemNumber_udp ='I26',FOperator = '-'  )
  #计算销售利润
  mrpt_index_baseType(conn = conn,FYear = FYear,FPeriod = FPeriod,FBrand = FBrand,FChannel = FChannel, res_table = res_table,
                      FRptItemNumber_from='I26',FRptItemNumber_end ='I27' ,FRptItemNumber_udp ='I44',FOperator = '-'  )
  #计算净利润
  mrpt_index_baseType(conn = conn,FYear = FYear,FPeriod = FPeriod,FBrand = FBrand,FChannel = FChannel, res_table = res_table,
                      FRptItemNumber_from='I44',FRptItemNumber_end ='I45' ,FRptItemNumber_udp ='I46',FOperator = '-'  )






}


#' 更新相应的指标
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#' @param res_table 结果图
#' @param graph_table 图表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_index_updateAll()更新了所有的指标
mrpt_index_updateAll<- function(conn=tsda::conn_rds('jlrds'),
                             FYear =2021,
                             FPeriod =6,
                             res_table = 't_mrpt_res',
                             graph_table ='vw_mrpt_md_brandchannel_graph_calc'
){
  sql <- paste0("  select  source_brand as FBrand,source_channel as FChannel

  from  ",graph_table,"  ")
  data = tsda::sql_select(conn,sql)
  ncount =nrow(data)
  if(ncount >0){
    lapply(1:ncount, function(i){
      FBrand = data$FBrand[i]
      FChannel = data$FChannel[i]
      mrpt_index_update(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table ,FBrand = FBrand,FChannel = FChannel)



    })

  }



}