R/mrpt51_manually.R

In takewiki/jlrdspkg: package for jlrds app

#' 判断相应的手工管报数据是否存在
#'
#' @param FBrand 品牌
#' @param FChannel 渠道
#' @param FYear 年份
#' @param FPeriod 月份
#' @param conn 连接
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt2_manual_exists()
mrpt2_manual_exists <- function(conn=tsda::conn_rds('jlrds'),
                                FBrand = '植物智慧' ,
                                FChannel = '电商' ,
                                FYear =2021 ,
                                FPeriod  = 6){

sql <- paste0("select count(1) as FCount from t_mrpt_target
where Fbrand ='",FBrand,"' and Fchannel ='",FChannel,"' and Fyear = ",FYear," and Fperiod = ",FPeriod,"")
res <- tsda::sql_select(conn,sql)
ncount <- res$FCount
if(ncount >0){
  info <- TRUE
}else{
  info <- FALSE
}
  return(info)
}
#' 读取手工管报数据
#'
#' @param file_name 文件名
#' @param conn 连接信息
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt2_manual_read()
mrpt2_manual_read <- function(conn=tsda::conn_rds('jlrds'),
                              file_name = "data-raw/manually/上传模板_手工管报.xlsx") {

  #library()
  data <- readxl::read_excel(file_name,
                     col_types = c("text", "text", "numeric",
                                   "numeric", "text", "text", "numeric"))
  ncount <- nrow(data)
  if(ncount >0){
    names(data) <- c('FBrand','FChannel','FYear','FPeriod','FRptItemNumber','FRptItemName','FRptAmt')
    data_selected = unique(data[,c('FBrand','FChannel','FYear','FPeriod')])
    print(data_selected)
    #针对每一行进行处理
    ncount2 = nrow(data_selected)
    #针对每一行进行处理
    lapply(1:ncount2, function(i){
      FBrand = data_selected$FBrand[i]
      FChannel = data_selected$FChannel[i]
      FYear = data_selected$FYear[i]
      FPeriod = data_selected$FPeriod[i]

      data_res = data[data$FBrand == FBrand & data$FChannel == FChannel & data$FYear == FYear & data$FPeriod == FPeriod, ]
      print(data_res)
      #判断数据库中是否存在相应的数据
      if(mrpt2_manual_exists(conn = conn,FBrand = FBrand,FChannel = FChannel,FYear = FYear,FPeriod = FPeriod)){
       print('A')
      }else{
        print('B')
        #上传相应的数据
        data_res$FRptAmt_original <- data_res$FRptAmt
        data_res$FRemark <- ''
        #注意表名，不要填写错误
        tsda::db_writeTable(conn=conn,table_name = 't_mrpt_target',r_object = data_res,append = T)

      }






    })

  }

  return(data)

}



#' 手工管报查询
#'
#' @param conn 连接
#' @param FBrand 品牌
#' @param FChannel 渠道
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回手工管报查询结果
#' @export
#'
#' @examples
#' mrpt2_manual_query()
mrpt2_manual_query <- function(conn=tsda::conn_rds('jlrds'),
                                FBrand = '植物智慧' ,
                                FChannel = '电商' ,
                                FYear =2021 ,
                                FPeriod  = 6){

  sql <- paste0("SELECT   [Fbrand] 品牌
      ,[Fchannel] 渠道
      ,[Fyear] 年份
      ,[Fperiod] 月份
      ,[FRptItemNumber] 报表项目代码
      ,[FRptItemName]   报表项目名称
      ,[FRptAmt]  报表金额
  FROM  [t_mrpt_target]
where Fbrand ='",FBrand,"' and Fchannel ='",FChannel,"' and Fyear = ",FYear," and Fperiod = ",FPeriod,"")
  res <- tsda::sql_select(conn,sql)

  return(res)
}