R/mpt_bw2.R

In takewiki/jlrdspkg: package for jlrds app

#1.0同步数据---------
#' 同步BW2版本的纵表数据
#'
#' @param conn 连接
#' @param FYear 年
#' @param FPeriod 月
#'
#' @return 返回值
#' @export
#'
#' @examples
#'
bw2_sync_data  <- function(conn=tsda::conn_rds('jlrds'),FYear =2021 ,FPeriod =6) {
 #删除已有数据
sql_del <- paste0("delete  from  rds_t_mrpt_ds_bw_rpa_v
where FYear =  ",FYear," and FPeriod =  ",FPeriod)
tsda::sql_update(conn,sql_del)
#插入新的数据
sql_ins <- paste0("insert into rds_t_mrpt_ds_bw_rpa_v
select   *  from vw_mrpt_ds_bw_rpa_v
where FYear = ",FYear," and FPeriod =  ",FPeriod)
tsda::sql_update(conn,sql_ins)

}

#' 维度筛选13A物料组包含
#'
#' @param conn 连接
#' @param FSolutionNumber 方案号
#' @param FSubNumber 子方案号
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' bw2_Filter_F13_in()
bw2_Filter_F13_in <- function(conn=tsda::conn_rds('jlrds'),FSolutionNumber = 'S001', FSubNumber =1 ,
                              FYear =2021 , FPeriod =6){

  sql <- paste0(" select  F13_itemGroupName
								  from  t_mrpt_ds_bw_F13_itemGroupName_in
 where FSolutionNumber = '",FSolutionNumber,"' and FSubNumber =  ",FSubNumber," and FYear = ",FYear," and FPeriod = ",FPeriod,"")
  # print(sql)
  r <- tsda::sql_select(conn,sql)
  ncount <- nrow(r)
  if(ncount >0){
    res <- paste0(" F13_itemGroupName in ( ",sql," )  and")
  }else{
    res <-" "
  }
  return(res)

}


#' 维度筛选13B物料组排除
#'
#' @param conn 连接
#' @param FSolutionNumber 方案号
#' @param FSubNumber 子方案号
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' bw2_Filter_F13_Notin()
bw2_Filter_F13_Notin <- function(conn=tsda::conn_rds('jlrds'),FSolutionNumber = 'S001', FSubNumber =1 ,
                                 FYear =2021 ,FPeriod =6){

  sql <- paste0(" select F13_itemGroupName from  t_mrpt_ds_bw_F13_itemGroupName_Notin
 where FSolutionNumber = '",FSolutionNumber,"' and FSubNumber =  ",FSubNumber," and  FYear = ",FYear," and FPeriod = ",FPeriod," ")
  # print(sql)
  r <- tsda::sql_select(conn,sql)
  ncount <- nrow(r)
  if(ncount >0){
    res <- paste0(" F13_itemGroupName not in ( ",sql," )  and")
  }else{
    res <-" "
  }
  return(res)

}


#' 维度筛选14品牌
#'
#' @param conn 连接
#' @param FSolutionNumber 方案号
#' @param FSubNumber 子方案号
#' @param FPeriod  月份
#' @param FYear 年份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' bw2_Filter_F13_in()
bw2_Filter_F14_in <- function(conn=tsda::conn_rds('jlrds'),FSolutionNumber = 'S001', FSubNumber =1 ,
                              FYear =2021 ,FPeriod =6){

  sql <- paste0(" select  F14_brandName from   t_mrpt_ds_bw_F14_brandName_in
 where FSolutionNumber = '",FSolutionNumber,"' and FSubNumber =  ",FSubNumber," and FYear = ",FYear," and FPeriod = ",FPeriod,"  ")
  # print(sql)
  r <- tsda::sql_select(conn,sql)
  ncount <- nrow(r)
  if(ncount >0){
    res <- paste0(" F14_brandName  in  ( ",sql," )  and")
  }else{
    res <-" "
  }
  return(res)

}


#' 维度筛选14品牌
#'
#' @param conn 连接
#' @param FSolutionNumber 方案号
#' @param FSubNumber 子方案号
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' bw2_Filter_F13_in()
bw2_Filter_F14_Notin <- function(conn=tsda::conn_rds('jlrds'),FSolutionNumber = 'S001', FSubNumber =1
                                 ,FYear =2021 ,FPeriod =6){

  sql <- paste0("  select F14_brandName from  t_mrpt_ds_bw_F14_brandName_Notin
 where FSolutionNumber = '",FSolutionNumber,"' and FSubNumber =  ",FSubNumber,"
and  FYear = ",FYear," and FPeriod = ",FPeriod,"  ")
  # print(sql)
  r <- tsda::sql_select(conn,sql)
  ncount <- nrow(r)
  if(ncount >0){
    res <- paste0(" F14_brandName  not in  ( ",sql," )  and")
  }else{
    res <-" "
  }
  return(res)

}


#' 维度筛选33子渠道
#'
#' @param conn 连接
#' @param FSolutionNumber 方案号
#' @param FSubNumber 子方案号
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' bw2_Filter_F13_in()
bw2_Filter_F33_in <- function(conn=tsda::conn_rds('jlrds'),FSolutionNumber = 'S001', FSubNumber =1
                              ,FYear =2021 ,FPeriod =6){

  sql <- paste0(" select F33_subChannelName from t_mrpt_ds_bw_F33_subChannelName_in
 where FSolutionNumber = '",FSolutionNumber,"' and FSubNumber =  ",FSubNumber,"
and  FYear = ",FYear," and FPeriod = ",FPeriod," ")
  # print(sql)
  r <- tsda::sql_select(conn,sql)
  ncount <- nrow(r)
  if(ncount >0){
    res <- paste0(" F33_subChannelName in   ( ",sql," )  and")
  }else{
    res <-" "
  }
  return(res)

}


#' 维度筛选41渠道
#'
#' @param conn 连接
#' @param FSolutionNumber 方案号
#' @param FSubNumber 子方案号
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' bw2_Filter_F13_in()
bw2_Filter_F41_in <- function(conn=tsda::conn_rds('jlrds'),FSolutionNumber = 'S001', FSubNumber =1
                              ,FYear =2021 ,FPeriod =6){

  sql <- paste0(" select F41_channelName  from t_mrpt_ds_bw_F41_channelName_in
 where FSolutionNumber = '",FSolutionNumber,"' and FSubNumber =  ",FSubNumber," and  FYear = ",FYear," and FPeriod = ",FPeriod," ")
  # print(sql)
  r <- tsda::sql_select(conn,sql)
  ncount <- nrow(r)
  if(ncount >0){
    res <- paste0(" F41_channelName in   ( ",sql," )  and")
  }else{
    res <-" "
  }
  return(res)

}


#' 维度筛选30A客户包含
#'
#' @param conn 连接
#' @param FSolutionNumber 方案号
#' @param FSubNumber 子方案号
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' bw2_Filter_F13_in()
bw2_Filter_F30_in <- function(conn=tsda::conn_rds('jlrds'),FSolutionNumber = 'S001', FSubNumber =1
                              ,FYear =2021 ,FPeriod =6){

  sql <- paste0("select F30_customerNumber from  t_mrpt_ds_bw_F30_customerNumber_in
 where FSolutionNumber = '",FSolutionNumber,"' and FSubNumber =  ",FSubNumber," and  FYear = ",FYear," and FPeriod = ",FPeriod,"  ")
  # print(sql)
  r <- tsda::sql_select(conn,sql)
  ncount <- nrow(r)
  if(ncount >0){
    res <- paste0(" F30_customerNumber  in   ( ",sql," )  and")
  }else{
    res <-" "
  }
  return(res)

}

#' 维度筛选30B客户排除
#'
#' @param conn 连接
#' @param FSolutionNumber 方案号
#' @param FSubNumber 子方案号
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' bw2_Filter_F13_in()
bw2_Filter_F30_Notin <- function(conn=tsda::conn_rds('jlrds'),FSolutionNumber = 'S001', FSubNumber =1
                                 ,FYear =2021 ,FPeriod =6){

  sql <- paste0("select F30_customerNumber from  t_mrpt_ds_bw_F30_customerNumber_Notin
 where FSolutionNumber = '",FSolutionNumber,"' and FSubNumber =  ",FSubNumber,"  and  FYear = ",FYear," and FPeriod = ",FPeriod," ")
  # print(sql)
  r <- tsda::sql_select(conn,sql)
  ncount <- nrow(r)
  if(ncount >0){
    res <- paste0(" F30_customerNumber   not in  ( ",sql," )  and")
  }else{
    res <-" "
  }
  return(res)

}



#' 维度筛选37地区销售部
#'
#' @param conn 连接
#' @param FSolutionNumber 方案号
#' @param FSubNumber 子方案号
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' bw2_Filter_F13_in()
bw2_Filter_F37_in <- function(conn=tsda::conn_rds('jlrds'),FSolutionNumber = 'S001', FSubNumber =1
                              ,FYear =2021 ,FPeriod =6
                              ){

  sql <- paste0("select F37_disctrictSaleDeptName  from t_mrpt_ds_bw_F37_disctrictSaleDeptName_in
 where FSolutionNumber = '",FSolutionNumber,"' and FSubNumber =  ",FSubNumber,"  and  FYear = ",FYear," and FPeriod = ",FPeriod," ")
  # print(sql)
  r <- tsda::sql_select(conn,sql)
  ncount <- nrow(r)
  if(ncount >0){
    res <- paste0(" F37_disctrictSaleDeptName in   ( ",sql," )  and")
  }else{
    res <-" "
  }
  return(res)

}


#' 维度筛选61成本中心控制
#'
#' @param conn 连接
#' @param FSolutionNumber 方案号
#' @param FSubNumber 子方案号
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' bw2_Filter_F61_in()
bw2_Filter_F61_in <- function(conn=tsda::conn_rds('jlrds'),FSolutionNumber = 'S001', FSubNumber =1
                              ,FYear =2021 ,FPeriod =6){

  sql <- paste0("select  F61_costCenterControlNumber from t_mrpt_ds_bw_F61_costCenterControlNumber_in
 where FSolutionNumber = '",FSolutionNumber,"' and FSubNumber =  ",FSubNumber,"  and  FYear = ",FYear," and FPeriod = ",FPeriod," ")
  # print(sql)
  r <- tsda::sql_select(conn,sql)
  ncount <- nrow(r)
  if(ncount >0){
    res <- paste0(" F61_costCenterControlNumber  in   ( ",sql," )  and")
  }else{
    res <-" "
  }
  return(res)

}


#' 维度筛选指标数据,一定是最后使用，不需要and
#'
#' @param conn 连接
#' @param FSolutionNumber 方案号
#' @param FSubNumber 子方案号
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' bw2_Filter_F13_in()
bw2_Filter_valueType <- function(conn=tsda::conn_rds('jlrds'),FSolutionNumber = 'S001', FSubNumber =1
                                 ,FYear =2021 ,FPeriod =6){

  sql <- paste0("select FValueType  from t_mrpt_ds_bw_formula
 where FSolutionNumber = '",FSolutionNumber,"' and FSubNumber =  ",FSubNumber,"  and  FYear = ",FYear," and FPeriod = ",FPeriod," ")
  # print(sql)
  r <- tsda::sql_select(conn,sql)
  ncount <- nrow(r)
  if(ncount >0){
    res <- paste0(" FValueType in   ( ",sql," )  ")
  }else{
    res <-" "
  }
  return(res)

}



#' 维度筛选指标数据,一定是最后使用，不需要and
#'
#' @param conn 连接
#' @param FSolutionNumber 方案号
#' @param FSubNumber 子方案号
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' bw2_Filter_F13_in()
bw2_formula <- function(conn=tsda::conn_rds('jlrds'),FSolutionNumber = 'S001', FSubNumber =1
                        ,FYear =2021 ,FPeriod =6){

  sql <- paste0("select  FBrand,FChannel,FRptItemNumber,FRptItemName,FRate from t_mrpt_ds_bw_formula
 where FSolutionNumber = '",FSolutionNumber,"' and FSubNumber =  ",FSubNumber,"  and  FYear = ",FYear," and FPeriod = ",FPeriod," ")
  # print(sql)
  r <- tsda::sql_select(conn,sql)
  ncount <- nrow(r)
  if(ncount >0){
    res <- r
  }else{
    res <- NULL
  }
  return(res)

}


#' 删除BW2已经计算的数据,优化数据处理的性能
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' bw2_DeleteData()
bw2_DeleteData <- function(conn=tsda::conn_rds('jlrds'),
                           FYear =2021,
                           FPeriod =6 ){
  sql_del <- paste0("delete from rds_t_mrpt_ds_bw_rpa_ruled
where  FYear = ",FYear," and FPeriod = ",FPeriod,"")
  tsda::sql_update(conn,sql_del)



}




#' 整合查询
#'
#' @param conn 连接
#' @param FSolutionNumber 方案号
#' @param FSubNumber 子序号
#'
#' @return 返回值
#' @export
#'
#' @examples
#' bw2_selectData
bw2_selectData <- function(conn=tsda::conn_rds('jlrds'),
                           FSolutionNumber = 'S001',
                           FSubNumber =1,
                           FYear =2021,
                           FPeriod =6 ){

  #查询数据
  info = bw2_formula(conn = conn,FSolutionNumber = FSolutionNumber,FSubNumber = FSubNumber,FYear = FYear,FPeriod = FPeriod)

  #  info$FBrand
  # info$FChannel
  # info$FRptItemNumber
  # info$FRptItemName
  #
  sql_heading = paste0("insert into  rds_t_mrpt_ds_bw_rpa_ruled
select *  , ",FSubNumber," as FSubNumber, '",info$FBrand,"' as FBrand_o,'",info$FChannel,"' as FChannel_o, '", info$FRptItemNumber,"' as FRptItemNumber_o,
'", info$FRptItemName,"' as FRptItemName_0  , ",info$FRate,   "  as FRate  from rds_t_mrpt_ds_bw_rpa_v where  FSolutionNumber = '",FSolutionNumber,"' and   FYear = ",FYear," and  FPeriod =  ",FPeriod,"  and  ")
  sql_all <- paste0(sql_heading,
                    bw2_Filter_F13_in(conn = conn,FSolutionNumber = FSolutionNumber,FSubNumber = FSubNumber,FYear = FYear,FPeriod = FPeriod),
                    bw2_Filter_F13_Notin(conn = conn,FSolutionNumber = FSolutionNumber,FSubNumber = FSubNumber,FYear = FYear,FPeriod = FPeriod),
                    bw2_Filter_F14_in(conn = conn,FSolutionNumber = FSolutionNumber,FSubNumber = FSubNumber,FYear = FYear,FPeriod = FPeriod),
                    bw2_Filter_F30_in(conn = conn,FSolutionNumber = FSolutionNumber,FSubNumber = FSubNumber,FYear = FYear,FPeriod = FPeriod),
                    bw2_Filter_F30_Notin(conn = conn,FSolutionNumber = FSolutionNumber,FSubNumber = FSubNumber,FYear = FYear,FPeriod = FPeriod),
                    bw2_Filter_F33_in(conn = conn,FSolutionNumber = FSolutionNumber,FSubNumber = FSubNumber,FYear = FYear,FPeriod = FPeriod),
                    bw2_Filter_F37_in(conn = conn,FSolutionNumber = FSolutionNumber,FSubNumber = FSubNumber,FYear = FYear,FPeriod = FPeriod),
                    bw2_Filter_F41_in(conn = conn,FSolutionNumber = FSolutionNumber,FSubNumber = FSubNumber,FYear = FYear,FPeriod = FPeriod),
                    bw2_Filter_F61_in(conn = conn,FSolutionNumber = FSolutionNumber,FSubNumber = FSubNumber,FYear = FYear,FPeriod = FPeriod),
                    bw2_Filter_valueType(conn = conn,FSolutionNumber = FSolutionNumber,FSubNumber = FSubNumber,FYear = FYear,FPeriod = FPeriod)

                    )
   cat(sql_all)
    tsda::sql_update(conn,sql_all)


  # ncount <- nrow(r)
  # #如果之前没有数据，也需要对数据进行清除处理的
  # #已经将功能分享出去
  # #sql_del <- paste0("delete from rds_t_mrpt_ds_bw_rpa_ruled
  # #where FSolutionNumber ='",FSolutionNumber,"' and FSubNumber = ",FSubNumber," and FYear = ",FYear," and FPeriod = ",FPeriod,"")
  # #tsda::sql_update(conn,sql_del)
  # if(ncount >0){
  #   #有数据的情况下
  #
  #   #删除已有的数据
  #
  #   #上传数据
  #   tsda::db_writeTable(conn = conn,table_name = 'rds_t_mrpt_ds_bw_rpa_ruled',r_object = r,append = T)
  #
  #
  #
  # }
  #
  # return(r)

}




#' 获取链接
#'
#' @param FYear 年份
#' @param FPeriod  月份
#' @param conn 连接
#'
#' @return 返回值
#' @export
#'
#' @examples
#' bw2_deal_list
bw2_deal_list <- function(conn=tsda::conn_rds('jlrds'),
                           FYear =2021,
                           FPeriod =6 ){
 sql <- paste0("select  FSolutionNumber,FSubNumber  from t_mrpt_ds_bw_formula
               where FYear = ",FYear," and FPeriod = ",FPeriod," ")
 r <- tsda::sql_select(conn,sql)
 #r$FYear <- FYear
 #r$FPeriod <- FPeriod
 ncount <- nrow(r)
 #处理数据

 #先批量清除历史数据
 bw2_DeleteData(conn = conn,FYear = FYear,FPeriod = FPeriod)
 # 然后针对每个数据进行处理

 lapply(1:ncount, function(i){
   FSolutionNumber = r$FSolutionNumber[i]
   FSubNumber =  r$FSubNumber[i]
   print(paste0(FSolutionNumber,'-',FSubNumber))
   bw2_selectData(conn = conn,FSolutionNumber = FSolutionNumber,FSubNumber = FSubNumber,FYear = FYear,FPeriod = FPeriod)



 })

  return(r)

}