R/mrpt02_sap2.R

In takewiki/jlrdspkg: package for jlrds app

#' 上传SAP凭证带重分类
#'
#' @param file_name 文件名
#' @param conn 连接
#'
#' @return 返回值
#' @export
#'
#' @examples
#' sap2_voucher_upload()
sap2_voucher_upload <- function(file_name = "data-raw/mrpt06/附件12-SAP凭证export_KSB1_Jun_Kalin_V1.1_20210709.XLSX",
                                conn=tsda::conn_rds('jlrds')) {

  #library(readxl)
  data <- readxl::read_excel(file_name,
                             col_types = c("date", "date", "text",
                                           "text", "text", "text", "numeric",
                                           "text", "text", "text"))

  tsda::db_writeTable(conn = conn,table_name = 't_mrpt_data_sap_withReClassified',r_object = data,append = T)


}


#1.0同步数据---------
#' 同步SAP2版本的对应SAP凭证包括重分类替代后的结果
#'
#' @param conn 连接
#' @param FYear 年
#' @param FPeriod 月
#'
#' @return 返回值
#' @export
#'
#' @examples
#'
sap2_sync_data  <- 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)

}