R/rpt_diff.R

In takewiki/jlrdspkg: package for jlrds app

#' 针对事业部的数据进行分析
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param FRptItemName 报表项目名称
#' @param digit 保留小数位数
#' @param FCalcType 计算类型
#'
#' @return 返回值
#' @export
#'
#' @examples
#' div_diff_analysis()
div_diff_analysis <- function(conn =  tsda::conn_rds('jlrds'),
                      FYear = 2021,
                      FPeriod =5,
                      FRptItemName ='四、营业收入',
                      digit = 0,
                      FCalcType='累计数'

                      ) {
  if(FCalcType == '累计数'){
    #本年累计数
    sql <- paste0("select   FType,FBrand,FChannel,frptamt from vw_mrpt_division_diff
where FYear =  ",FYear," and FPeriod = ",FPeriod," and FRptItemName ='",FRptItemName,"' ")
  }else{
    #当期数据
    sql <- paste0("select   FType,FBrand,FChannel,frptamt from vw_mrpt_division_diff2
where FYear =  ",FYear," and FPeriod = ",FPeriod," and FRptItemName ='",FRptItemName,"' ")
  }

  data <- tsda::sql_select(conn,sql)
  #获取相应的获取字段
  FType = unique(data$FType)

  ncount = length(FType)
  data2 = reshape2::dcast(data = data,formula = FBrand+ FChannel~FType,value.var = 'frptamt',fun.aggregate = sum)
  #针对字段进行处理
  lapply(1:ncount, function(i){
    data2[ ,FType[i]] <<- round(data2[ ,FType[i]],digits = digit)
  })

  return(data2)




}


#' 获取报表名称
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' div_diff_getUniqueRptName()
div_diff_getUniqueRptName <- function(conn =  tsda::conn_rds('jlrds'),
                                      FYear = 2021,
                                      FPeriod =5) {
  sql <- paste0("select   distinct FRptItemNumber, FRptItemName from vw_mrpt_division_diff
where FYear = ",FYear," and FPeriod= ",FPeriod,"
order by FRptItemNumber")
  data <- tsda::sql_select(conn,sql)
  res = tsdo::vect_as_list(data$FRptItemName)
  return(res)


}

#' 获取版本号
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_diff_getVersion()
mrpt_diff_getVersion<- function(conn =  tsda::conn_rds('jlrds'),
                          FYear = 2021,
                          FPeriod =5){
sql_hist <- paste0("select FVersion from t_mrpt_diff_all_version
where FYear = ",FYear," and FPeriod = ",FPeriod,"")
data  =tsda::sql_select(conn,sql_hist)
ncount = nrow(data)
if(ncount >0){
  res = data$FVersion
}else{
  #初始化版本
  sql_ins <- paste0("insert into  t_mrpt_diff_all_version values(",FYear,",",FPeriod,",1)")
  tsda::sql_update(conn,sql_ins)
  res = 1
}

return(res)
}




#'设置版本
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_diff_setVersion()
mrpt_diff_setVersion<- function(conn =  tsda::conn_rds('jlrds'),
                                FYear = 2021,
                                FPeriod =5){
  sql_hist <- paste0("select FVersion from t_mrpt_diff_all_version
where FYear = ",FYear," and FPeriod = ",FPeriod,"")
  data  =tsda::sql_select(conn,sql_hist)
  ncount = nrow(data)
  if(ncount >0){
    #进行版本升级
    sql_udp =  paste0("update a set  FVersion = FVersion + 1  from t_mrpt_diff_all_version a
where FYear = ",FYear," and FPeriod = ",FPeriod," ")
    tsda::sql_update(conn,sql_udp)
    res =2
  }else{
    #初始化版本
    sql_ins <- paste0("insert into  t_mrpt_diff_all_version values(",FYear,",",FPeriod,",1)")
    tsda::sql_update(conn,sql_ins)
    res =1
  }

  return(res)
}




#' 计算差异表
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_diff_calc()
mrpt_diff_calc <-function(conn =  tsda::conn_rds('jlrds'),
                  FYear = 2021,
                  FPeriod =5){
#获取版本号
  FVersion =  mrpt_diff_getVersion(conn = conn,FYear = FYear,FPeriod = FPeriod)
#备份历史数据
sql_bak <- paste0("insert into t_mrpt_diff_all_del
select * , ",FVersion," as FVersion from t_mrpt_diff_all
where FYear = ",FYear," and FPeriod = ",FPeriod,"")
tsda::sql_update(conn,sql_bak)
#删除现有数据
sql_del <- paste0("delete  from t_mrpt_diff_all
where FYear = ",FYear," and FPeriod = ",FPeriod,"")
tsda::sql_update(conn,sql_del)
#写入新的数据
sql_ins <- paste0("insert into t_mrpt_diff_all
select *  from rds_vw_T_FI_RPA2
where FYear = ",FYear," and FPeriod = ",FPeriod,"")
tsda::sql_update(conn,sql_ins)
#更新版本号
mrpt_diff_setVersion(conn = conn,FYear = FYear,FPeriod = FPeriod)
#写入日志
ftime = as.character(Sys.time())
sql_log = paste0("insert into t_mrpt_diff_all_log values(",FYear,",",FPeriod,",",FVersion,",'",ftime,"')")
tsda::sql_update(conn,sql_log)

}