R/mrpt_res.R

In takewiki/jlrdspkg: package for jlrds app

#' 管报结果表结果
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param FBrand 品牌
#' @param FChannel 渠道
#' @param view_name 视图名称
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_res_query()
mrpt_res_query <- function(conn=tsda::conn_rds('jlrds'),
                           FYear = 2021,
                           FPeriod = 6,
                           FBrand = '自然堂',
                           FChannel ='大客户',
                           view_name = 'mrpt3_vw_FI_RPA') {

  sql_str <- paste0("  select  [FYear]
      ,[FPeriod]
      ,[FBrandNumber]
      ,[FBrand]
      ,[FChannelNumber]
      ,[FChannel]
      ,[FSubChannel]
      ,[FRptItemNumber]
      ,[FRptItemName]
      ,[FAcualAmt]
      ,[FBudgetAmt]
      ,[FAchiveRatio]
      ,[FAcualAmt_Lag1]
      ,[FAchiveRatio_Lag1]
      ,[FAcualAmt_Lag2]
      ,[FAchiveRatio_Lag2]
      ,[FAcualCumAmt]
      ,[FBudgetCumAmt]
      ,[FAchiveCumRatio]
      ,[FAcualCumAmt_Lag1]
      ,[FAchiveCumRatio_Lag1]
      ,[FAcualCumAmt_Lag2]
      ,[FAchiveCumRatio_Lag2]
      ,[FBrandChannelNumber]
      ,[FBrandChannelName]
                    from ",view_name," where FYear = ",FYear," and FPeriod =  ",FPeriod,"
                    and FBrand  = '",FBrand,"'  and FChannel  ='",FChannel,"'")
  data <- tsda::sql_select(conn, sql_str)
  return(data)
}