R/quickRpt.R

In takewiki/jlrdspkg: package for jlrds app

#' 写入经营快报数据
#'
#' @param file_name 文件名
#' @param conn 连接
#' @param table_name 表名
#'
#' @return 返顺值
#' @export
#'
#' @examples
#' quickRpt_upload()
quickRpt_upload <- function(file_name = "data-raw/qr/经营快报模板_公司零售额.xlsx",
                            conn=tsda::conn_rds('jlrds'),
                            table_name = 'rds_t_mrpt_res_I02_RetailSales') {

  #library(readxl)
  data <- readxl::read_excel(file_name)
  ncount = nrow(data)
  if(ncount >0){
    FYear  = as.integer(data[1 ,'年份'])
    FPeriod = as.integer(data[1,'月份'])
    #删除已有数据
    sql_del = paste0("delete  from ",table_name,"
where  FYear =  ",FYear," and  FPeriod =  ",FPeriod,"")
    tsda::sql_update(conn,sql_del)
    #写入数据
    tsda::db_writeTable(conn = conn,table_name = table_name,r_object = data,append = T)



  }

  #View(data)

}





#' 快查询
#'
#' @param conn  连接
#' @param table_name 报表名称
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' quickRpt_query()
quickRpt_query <- function(conn=tsda::conn_rds('jlrds'),
                           table_name = 'rds_t_mrpt_res_I02_RetailSales',
                           FYear =2021,
                           FPeriod =9) {


    sql = paste0("select *   from ",table_name,"
where  FYear =  ",FYear," and  FPeriod =  ",FPeriod,"")
    data = tsda::sql_select(conn,sql)
    ncount =nrow(data)
    if(ncount >0){
      names(data) <-c('年份','月份','品牌','渠道','报表项目代码','报表项目名称','报表金额')

    }
    return(data)

}