R/checkRpt.R

In takewiki/jlrdspkg: package for jlrds app

#' 查询报表结果
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param FBrand 品牌
#' @param FChannel 渠道
#'
#' @return 返回值
#' @export
#'
#' @examples
#' check_rptBrandChannel()
check_rptBrandChannel  <- function(conn=tsda::conn_rds('jlrds'),
                                   FYear =2021 ,
                                   FPeriod =9,
                                   FBrand='ASSASSINA',
                                   FChannel='电商') {
if(FBrand == ''){
  sql <- 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 mrpt3_vw_FI_RPA
where FYear = ",FYear," and FPeriod = ",FPeriod," order
                by FYear,FPeriod,FBrandChannelNumber,FRptItemNumber")
}  else{
  sql <- 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 mrpt3_vw_FI_RPA
where FYear = ",FYear," and FPeriod = ",FPeriod,"
and FBrand = '",FBrand,"' and FChannel ='",FChannel,"' order
                by FYear,FPeriod,FBrandChannelNumber,FRptItemNumber")
}

data = tsda::sql_select(conn,sql)
ncount =nrow(data)
if(ncount >0){
  names(data) <-c( "年份"
                   ,"月份"
                   ,"品牌代码"
                   ,"品牌"
                   ,"渠道代码"
                   ,"渠道"
                   ,"子渠道"
                   ,"报表项目代码"
                   ,"报表项目名称"
                   ,"当期金额"
                   ,"当期执行预算"
                   ,"当期达成率"
                   ,"去年同期金额"
                   ,"去年同比"
                   ,"前年同期金额"
                   ,"前年同比"
                   ,"当期累计金额"
                   ,"当期累计执行预算"
                   ,"当期累计达成率"
                   ,"去年累计金额"
                   ,"去年累计达成率"
                   ,"前年累计金额"
                   ,"前年累计达成率"
                   ,"品牌渠道代码"
                   ,"品牌渠道名称")
}
return(data)

}