R/mpv.R

In takewiki/jlrdspkg: package for jlrds app

#' 中后台费用查询
#'
#' @param conn 连接
#' @param FBrand 品牌
#' @param FChannel 渠道
#' @param FYear 年份
#'
#' @return 返回查询结果
#' @export
#'
#' @examples
#' mpv_query()
mpv_query <- function(conn=tsda::conn_rds('jlrds'),
                      FBrand ='JALA集团',
                      FChannel ='全渠道',
                      FYear =2021
                       ){
sql <- paste0("SELECT [FYear]
      ,[FPeriod]
      ,[FBrand]
      ,[FChannel]
      ,[FRptItemNumber]
      ,[FRptItemName]
      ,round(FRptAmt,2) as [FRptAmt]
  FROM [dbo].[t_mrpt_ds_mpv_manual]
where FBrand ='",FBrand,"' and FChannel ='",FChannel,"' and FYear = ",FYear,"")

res <- tsda::sql_select(conn,sql)
ncount <- nrow(res)
if(ncount >0){
  names(res) <-c('年份','月份','品牌','渠道','报表项目代码','报表项目名称','报表金额')
}

return(res)

}





#' 上传服务器
#'
#' @param file_name 文件名
#' @param conn 连接
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mpv_upload()
mpv_upload <- function(file_name = "data-raw/mpv/中后台费用模板.xlsx",conn=tsda::conn_rds('jlrds')

){

  #library(readxl)
  data <- readxl::read_excel(file_name)

  #View(data)
  ncount <- nrow(data)
  if(ncount >0){
    names(data) <-c('FYear','FPeriod','FBrand','FChannel','FRptItemNumber','FRptItemName','FRptAmt')
    data$FRptAmt <- round(data$FRptAmt,2)
    lapply(1:ncount, function(i){
      FYear = data$FYear[i]
      FPeriod = data$FPeriod[i]
      FBrand = data$FBrand[i]
      FChannel = data$FChannel[i]
      sql <- paste0("delete FROM [dbo].[t_mrpt_ds_mpv_manual]
where FBrand ='",FBrand,"' and FChannel ='",FChannel,"' and FYear = ",FYear," and FPeriod = ",FPeriod,"")

      tsda::sql_update(conn,sql)

    })

    #上传数据库
    tsda::db_writeTable(conn = conn,table_name = 't_mrpt_ds_mpv_manual',r_object = data,append = T)

  }

}









#' 更新当期的中后台费用
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mpv_update_period()
mpv_update_period <- function(conn=tsda::conn_rds('jlrds'),

                      FYear =2021,
                      FPeriod =11
){
sql_udp <- paste0("update a set a.FRptAmt = b.FRptAmt   from t_mrpt3_res a   inner join   vw_mrpt_ds_mpv b
on  a.Fyear = b.FYear and a.Fperiod  =  b.FPeriod
and a.Fbrand = b.FBrand and a.Fchannel = b.FChannel
and a.FRptItemNumber = b.FRptItemNumber
where a.Fyear = ",FYear," and  a.Fperiod = ",FPeriod,"")
print(sql_udp)
tsda::sql_update(conn,sql_udp)
#更新相关指标
sql_44 <- paste0("select FRptAmt from t_mrpt3_res
where Fyear = ",FYear," and Fperiod =  ",FPeriod,"
and Fbrand ='JALA集团' and Fchannel ='全渠道'
and FRptItemNumber ='I44'")
res_44 <- tsda::sql_select(conn,sql_44)
if(nrow(res_44) >0){
  item_44 = res_44$FRptAmt
}else{
  item_44 = 0
}


sql_45 <- paste0("select FRptAmt from t_mrpt3_res
where Fyear = ",FYear," and Fperiod =  ",FPeriod,"
and Fbrand ='JALA集团' and Fchannel ='全渠道'
and FRptItemNumber ='I45'")
res_45 <- tsda::sql_select(conn,sql_45)
if(nrow(res_45) >0){
  item_45 = res_45$FRptAmt
}else{
  item_45 = 0
}

item_46 = item_44 - item_45


sql_46 <- paste0("update a set  a.FRptAmt =  ",item_46,"  from t_mrpt3_res a
where Fyear = ",FYear," and Fperiod = ",FPeriod,"
and Fbrand ='JALA集团' and Fchannel ='全渠道'
and FRptItemNumber ='I46' ")
tsda::sql_update(conn,sql_46)






}