R/mrpt12_actual.R

In takewiki/jlrdspkg: package for jlrds app

#以下为历史数据的上传处理
# 以下为新增内容
#' 进行报表数据的以预检验
#'
#' @param conn 连接
#' @param FBrand 品牌
#' @param FChannel 渠道
#' @param FYear 年
#' @param FPeriod 月份
#' @param FSubChannel 子渠道
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_budget_readData_ByDivision_currentPreCheck()
mrpt_actual_readData_ByDivision_currentPreCheck <- function(
  conn=tsda::conn_rds('jlrds'),

  FBrand='珀芙研',
  FChannel='电商',
  FYear=2021,
  FPeriod =5,
  FSubChannel=NA) {
  if (is.na(FSubChannel)){
    sql <- paste0("delete  from t_mrpt_actual
where FBrand ='",FBrand,"' and FChannel='",FChannel,"' and FYear = ",FYear," and FPeriod = ",FPeriod,"")
  }else{
    sql <- paste0("delete from t_mrpt_actual
where FBrand ='",FBrand,"' and FChannel='",FChannel,"' and FYear = ",FYear," and FPeriod = ",FPeriod," and FSubChannel ='",FSubChannel,"'")
  }

  tsda::sql_update(conn,sql)


}





#' 获取当前期间的预算数
#'
#' @param file 文件
#' @param conn 连接
#' @param sheet 页答
#' @param FBrand 品牌
#' @param FChannel 渠道
#' @param FYear 年
#' @param FPeriod 月
#' @param FSubChannel  子渠道
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_budget_readData_ByDivision_currentPeriod()
mrpt_actual_readData_ByDivision_currentPeriod <- function(file="data-raw/budget/执行预算_RDS_珀_OK.xlsx",
                                                          conn=tsda::conn_rds('jlrds'),
                                                          sheet = "电商",
                                                          FBrand='珀芙研',
                                                          FChannel='电商',
                                                          FYear=2021,
                                                          FPeriod =5,
                                                          FSubChannel=NA) {
  library(readxl)
  data <- read_excel(file,
                     sheet = sheet, col_types = c("text",
                                                  "numeric", "numeric", "numeric",
                                                  "numeric", "numeric", "numeric",
                                                  "numeric", "numeric", "numeric",
                                                  "numeric", "numeric", "numeric",
                                                  "skip"), skip = 1)
  #print(data)
  #print(1)
  col_names <- names(data)
  print(col_names)
  col_count <- length(col_names)
  lapply(2:col_count, function(i){
    data[i] <<- round(data[i],2)
  })
  data[is.na(data)] <- 0
  #确保第一个报表项目不会出错
  col_names[1] <- '报表项目'
  names(data) <- col_names
  data$`报表项目代码` <- mrpt_rptItem_getNumber(conn = conn)
  data2 <- reshape2::melt(data = data,id.vars=c('报表项目代码','报表项目'),variable.name='FPeriod',value.name='FAmt')
  data2$FPeriod <- as.integer(  stringr::str_replace(data2$FPeriod,'月',''))
  data2$FYear <- FYear
  data2$FBrand <-FBrand
  data2$FChannel <-FChannel
  data2$FSubChannel <- FSubChannel
  # print(data2)
  data2 <- data2[,c('FBrand','FChannel','FSubChannel','FYear','FPeriod','报表项目代码','报表项目','FAmt')]
  names(data2) <- c('FBrand','FChannel','FSubChannel','FYear','FPeriod','FRptItemNumber','FRptItemName','FAmt')
  #小于当前月份，不提供更多数据
  data2 <- data2[data2$FPeriod == FPeriod, ]
  ncount2 <- nrow(data2)
  if(ncount2 >0){
    #针对数据进行检查，支持多次重写
    mrpt_actual_readData_ByDivision_currentPreCheck(conn = conn,FBrand = FBrand,FChannel = FChannel,FYear = FYear,FPeriod = FPeriod,FSubChannel = FSubChannel)
    tsda::db_writeTable(conn = conn,table_name = 't_mrpt_actual',r_object = data2,append = T)
  }
  return(data2)
}


#' 进行报表数据的以预检验
#'
#' @param conn 连接
#' @param FBrand 品牌
#' @param FChannel 渠道
#' @param FYear 年
#' @param FPeriod 月份
#' @param FSubChannel 子渠道
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_budget_readData_ByDivision_cumPreCheck()
mrpt_actual_readData_ByDivision_cumPreCheck <- function(
  conn=tsda::conn_rds('jlrds'),

  FBrand='珀芙研',
  FChannel='电商',
  FYear=2021,
  FPeriod =5,
  FSubChannel=NA) {
  if (is.na(FSubChannel)){
    sql <- paste0("delete  from t_mrpt_actual
where FBrand ='",FBrand,"' and FChannel='",FChannel,"' and FYear = ",FYear," and FPeriod <= ",FPeriod,"")
  }else{
    sql <- paste0("delete from t_mrpt_actual
where FBrand ='",FBrand,"' and FChannel='",FChannel,"' and FYear = ",FYear," and FPeriod <= ",FPeriod," and FSubChannel ='",FSubChannel,"'")
  }

  tsda::sql_update(conn,sql)


}


#' 按事业部及预算格式读取预算数据
#'
#' @param file 文件
#' @param conn 连接
#' @param sheet 页答
#' @param FBrand 品牌
#' @param FChannel 渠道
#' @param FYear 年份
#' @param FSubChannel  子渠道
#' @param FPeriod  月份数据
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_budget_readData_ByDivision()
mrpt_actual_readData_ByDivision_cumPeriod <- function(file="data-raw/budget/执行预算_RDS_珀_OK.xlsx",
                                                      conn=tsda::conn_rds('jlrds'),
                                                      sheet = "电商",
                                                      FBrand='珀芙研',
                                                      FChannel='电商',
                                                      FYear=2021,
                                                      FPeriod =5,
                                                      FSubChannel=NA) {
  library(readxl)
  data <- read_excel(file,
                     sheet = sheet, col_types = c("text",
                                                  "numeric", "numeric", "numeric",
                                                  "numeric", "numeric", "numeric",
                                                  "numeric", "numeric", "numeric",
                                                  "numeric", "numeric", "numeric",
                                                  "skip"), skip = 1)
  #print(data)
  #print(1)
  col_names <- names(data)
  col_count <- length(col_names)
  lapply(2:col_count, function(i){
    data[i] <<- round(data[i],2)
  })
  data[is.na(data)] <- 0
  col_names[1] <- '报表项目'
  #确保第一个报表项目不会出错
  names(data) <- col_names

  data$`报表项目代码` <- mrpt_rptItem_getNumber(conn = conn)
  data2 <- reshape2::melt(data = data,id.vars=c('报表项目代码','报表项目'),variable.name='FPeriod',value.name='FAmt')
  data2$FPeriod <- as.integer(  stringr::str_replace(data2$FPeriod,'月',''))
  data2$FYear <- FYear
  data2$FBrand <-FBrand
  data2$FChannel <-FChannel
  data2$FSubChannel <- FSubChannel
  # print(data2)
  data2 <- data2[,c('FBrand','FChannel','FSubChannel','FYear','FPeriod','报表项目代码','报表项目','FAmt')]
  names(data2) <- c('FBrand','FChannel','FSubChannel','FYear','FPeriod','FRptItemNumber','FRptItemName','FAmt')
  #小于当前月份，不提供更多数据
  data2 <- data2[data2$FPeriod <= FPeriod, ]
  ncount2 <- nrow(data2)
  if(ncount2 >0){
    #支持多次重写
    mrpt_actual_readData_ByDivision_cumPreCheck(conn = conn,FBrand = FBrand,FChannel = FChannel,FYear = FYear,FPeriod = FPeriod,FSubChannel = FSubChannel)
    tsda::db_writeTable(conn = conn,table_name = 't_mrpt_actual',r_object = data2,append = T)
  }
  return(data2)
}




#' 针对历史数据检查报表项目
#'
#' @param conn 连接
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_actual_checkRptItem()
mrpt_actual_checkRptItem  <- function(conn=tsda::conn_rds('jlrds')) {
sql <- paste0("select FBrand,FChannel,FYear,FPeriod,a.FRptItemNumber,a.FRptItemName,b.FRptItemName,FAmt as FRptAmt from t_mrpt_actual a
left join t_mrpt_rptItem b
on a.FRptItemNumber =  b.FRptItemNumber
where a.FRptItemName <> b.FRptItemName")
data = tsda::sql_select(conn,sql)
return(data)

}