R/mrpt01_bw_ui.R

In takewiki/jlrdspkg: package for jlrds app

#' 查看BW报表表头
#'
#' @param conn 连接
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_bw_ui_getHeadingName()
mrpt_bw_ui_getHeadingName <- function(conn=tsda::conn_rds('jlrds')) {
  sql <- paste0("select FInterId,FName_show,FName_sql from t_mrpt_valueType
where FDataSource='bw'
order by FIndex")
  res <- tsda::sql_select(conn,sql)
  ncount <- nrow(res)
  if(ncount >0){
    names(res) <- c('序号','显示名称','存储名称')
  }
  return(res)
}


#' BW报表的维度名称
#'
#' @param conn 连接
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_bw_ui_getDimName()
mrpt_bw_ui_getDimName <- function(conn=tsda::conn_rds('jlrds')) {
  sql <- paste0(" select   FSulotionNumber,FIndex,FName_show,FName_sql   from t_mrpt_dimType")
  res <- tsda::sql_select(conn,sql)
  ncount <- nrow(res)
  if(ncount >0){
    names(res) <- c('方案号','序号','显示名称','存储名称')
  }
  return(res)
}


#' BW报表的维度名称上传
#'
#' @param conn 连接
#' @param file_name 文件名
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_bw_ui_getDimName_read()
mrpt_bw_ui_getDimName_read <- function(conn=tsda::conn_rds('jlrds'),
                                       file_name = "data-raw/md/BW维度模板.xlsx") {


  data <- readxl::read_excel(file_name)

  sql_ins <- paste0(" insert into  t_mrpt_dimType_bak select  *   from t_mrpt_dimType")
  tsda::sql_update(conn,sql_ins)
  sql_del <- paste0(" delete  from t_mrpt_dimType")
  tsda::sql_update(conn,sql_del)

  ncount <- nrow(data)
  if(ncount >0){
    names(data) <- c('FSulotionNumber','FIndex','FName_show','FName_sql')
    data$FInterId <- 1:ncount
    data$FDataSource <-'bw'
    data <- data[,c('FInterId','FSulotionNumber','FIndex','FName_show','FName_sql','FDataSource')]
    #上传数据
    tsda::db_writeTable(conn = conn,table_name = 't_mrpt_dimType',r_object = data,append = T)


  }
  return(data)
}




#' BW报表的业务处理规则
#'
#' @param conn 连接
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_bw_ui_businessRule()
mrpt_bw_ui_businessRule <- function(conn=tsda::conn_rds('jlrds')) {
  sql <- paste0("   select  FInterId,FSolutionNumber,FValueName,FBrand_o,FChannel_o,FRptItem_o,FRptItemNumber    from t_mrpt_rule_bw")
  res <- tsda::sql_select(conn,sql)
  ncount <- nrow(res)
  if(ncount >0){
    names(res) <- c('序号','方案号','指标名称','品牌','渠道','报表项目名称','报表项目代码')
  }
  return(res)
}



#' 获取BW数据源
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_bw_ds_data()
mrpt_bw_ds_data <- function(conn=tsda::conn_rds('jlrds'),FYear =2021,FPeriod =5) {
  sql <- paste0("    select FBrand,FChannel,FValueName,FSolutionNumber,FValue,FYear,FPeriod from t_mrpt_data_bw
 where FYear =  ",FYear," and FPeriod =   ",FPeriod,"
 order by FBrand,FChannel,  FValueName,FSolutionNumber")
  res <- tsda::sql_select(conn,sql)
  ncount <- nrow(res)
  if(ncount >0){
    names(res) <- c('品牌','渠道','指标名称','方案号','金额','年','月')
  }
  return(res)
}







#' 读取BW处理中间表
#'
#' @param conn 连接
#' @param FBrand 品牌
#' @param FChannel 渠道
#' @param FYear 年
#' @param FPeriod 月
#'
#' @return 返回
#' @export
#'
#' @examples
#' bw_res_ui_fromDB()
bw_res_ui_fromDB <-function(conn=tsda::conn_rds('jlrds'),
                             FBrand ='自然堂',
                             FChannel='商超',
                             FYear=2021,
                             FPeriod=5){
  sql <- paste0("select  * from vw_mrpt_data_bw
where FBrand ='",FBrand,"' and FChannel ='",FChannel,"' and FYear = ",FYear ," and FPeriod =   ",FPeriod)
  res <- tsda::sql_select(conn,sql)
  ncount =nrow(res)
  if(ncount >0){
    names(res) <- c('方案号','品牌','渠道','BW指标','报表金额','年','月','报表项目名称',
                    '报表项目代码')
  }
  return(res)


}






#' BW报表规则V2读取内容
#'
#' @param conn 连接
#' @param file_name 文件名
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_md_rule_bw2_read()
mrpt_md_rule_bw2_read <-function(conn=tsda::conn_rds('jlrds'),
                                 file_name ="data-raw/md/01-BW报表业务规则模板.xlsx",
                                 FYear =2021,
                                 FPeriod =8){
  data <- readxl::read_excel(file_name,
                             col_types = c("text", "numeric", "text",
                                           "text", "text", "text", "text", "text",
                                           "text", "text", "text", "text", "text",
                                           "text", "text", "text", "text", "text",
                                           "text", "text", "text","numeric", "numeric",
                                           "numeric", "text", "text"))
  ncount <- nrow(data)
  if(ncount >0){

    #存在数据的情况下，对数据进行处理
    # sql_ins <- paste0("insert into t_mrpt_rule_bw2_bak  select * from t_mrpt_rule_bw2
    #                   where FYear = ",FYear," and FPeriod = ",FPeriod,"")
    # tsda::sql_update(conn,sql_ins)
    sql_del <- paste0("delete  from t_mrpt_rule_bw2   where FYear = ",FYear," and FPeriod = ",FPeriod)
    tsda::sql_update(conn,sql_del)
    #针对数据进行分页处理优化

    total_vol = nrow(data)
    pages = tsdo::paging_setting(total_vol,each_page = 500)
    n_page = nrow(pages)
    lapply(1:n_page, function(i){
      print(pages[i,])
      FStart = pages$FStart[i]
      FEnd = pages$FEnd[i]
      data = data[FStart:FEnd,]
      tsda::db_writeTable(conn,table_name = 't_mrpt_rule_bw2',r_object = data,append = T)


    })


       # tsda::db_writeTable(conn,table_name = 't_mrpt_rule_bw2',r_object = data,append = T)

  }


}


#' 检查相关的状态
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_md_rule_bw2_checkStatus()
mrpt_md_rule_bw2_checkStatus <-function(conn=tsda::conn_rds('jlrds'),

                                        FYear =2021,
                                        FPeriod =9){
  sql <- paste0("select 1  from t_mrpt_rule_bw2   where FYear = ",FYear," and FPeriod = ",FPeriod)
  data = tsda::sql_select(conn,sql)
  ncount =nrow(data)
  if(ncount >0){
    res <- FALSE
  }else{
    res <- TRUE
  }
  return(res)
}




#' BW报表规则V2同步上月BW规则
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_md_rule_bw2_syncLastOne()
mrpt_md_rule_bw2_syncLastOne <-function(conn=tsda::conn_rds('jlrds'),

                                 FYear =2021,
                                 FPeriod =9){
    #获取上期信息
   last_info = md_getLastYearPeriod(conn = conn,FYear = FYear,FPeriod = FPeriod)
   FYear_last = last_info[1]
   FPeriod_last =last_info[2]



    sql_del <- paste0("delete  from t_mrpt_rule_bw2   where FYear = ",FYear," and FPeriod = ",FPeriod)
    cat(sql_del)
    tsda::sql_update(conn,sql_del)
    sql_ins <- paste0("insert into  t_mrpt_rule_bw2
     SELECT  [FSolutionNumber]
      ,[FSubNumber]
      ,[F13_ItemGroupName_in]
      ,[F13_ItemGroupName_Notin]
      ,[F14_brandName_in]
      ,[F14_brandName_Notin]
      ,[F30_customerNumber_in]
      ,[F30_customerNumber_Notin]
      ,[F33_subChannelName_in]
      ,[F33_subChannelName_Notin]
      ,[F37_disctrictSaleDeptName_in]
      ,[F37_disctrictSaleDeptName_Notin]
      ,[F41_channelName_in]
      ,[F41_channelName_Notin]
      ,[F61_costCenterControlNumber_in]
      ,[F61_costCenterControlNumber_Notin]
      ,[FValueType]
      ,[FBrand]
      ,[FChannel]
      ,[FRptItemNumber]
      ,[FRptItemName]
      ,[FRate]
      ,",FYear,"  as [FYear]
      ,",FPeriod,"  as [FPeriod]
      ,[FSolutionName]
      ,[FDescription]
  FROM [t_mrpt_rule_bw2]
  where FYear = ",FYear_last," and FPeriod =  ",FPeriod_last," ")
    cat(sql_ins)
    tsda::sql_update(conn,sql_ins)




}




#' BW报表规则V2读取内容
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_md_rule_bw2_select()
mrpt_md_rule_bw2_select <-function(conn=tsda::conn_rds('jlrds'),
                                   FYear =2021,
                                   FPeriod =6
                                 ){
  sql_ins <- paste0(" select * from t_mrpt_rule_bw2
  where FYear = ",FYear," and FPeriod =  ",FPeriod,"
order by FSolutionNumber,FSubNumber")
  data <-tsda::sql_select(conn,sql_ins)

  ncount <- nrow(data)
  if(ncount >0){
    #存在数据的情况下，对数据进行处理

  names(data) <-c(
    '方案号',
    '方案序号',
    '13物料组(物料主数据)-名称-包含',
    '13物料组(物料主数据)-名称-排除',
    '14品牌(物料主数据)-名称-包含',
    '14品牌(物料主数据)-名称-排除',
    '30客户-代码-包含',
    '30客户-代码-排除',
    '33子渠道（SAP客户组）(客户主数据)-名称-包含',
    '33子渠道（SAP客户组）(客户主数据)-名称-排除',
    '37地区销售部(客户主数据)-名称-包含',
    '37地区销售部(客户主数据)-名称-排除',
    '41渠道(分析用)-名称-包含',
    '41渠道(分析用)-名称-排除',
    '61成本中心(控制)-代码-包含',
    '61成本中心(控制)-代码-排除',
    '指标名称',
    '品牌',
    '渠道',
    '报表项目代码',
    '报表项目名称',
    '分配比例',
    '年份',
    '月份',
    '方案名称',
    '方案描述'


  )

  }
  return(data)


}



#' BW报表规则V2读取内容
#'
#' @param conn 连接
#' @param FDimName 维度
#' @param FDimTable 维度表
#' @param FPrefix 前缀处理
#' @param FYear 年份
#' @param FPeriod 月份
#' @param opt 优化参数
#'
#' @return 返回值
#' @export
#'
#' @examples
#'mrpt_md_rule_bw2_dim_allocation()
mrpt_md_rule_bw2_dim_allocation <-function(conn=tsda::conn_rds('jlrds'),

                                       FDimName ='F13_ItemGroupName_in',
                                       FPrefix ='',
                                       FDimTable='t_mrpt_ds_bw_F13_itemGroupName_in',
                                       FYear =2021,
                                       FPeriod =6,
                                       opt = FALSE
){

  if( opt){

    print(1)

    sql_ins <- paste0(" select FSolutionNumber,FSubNumber , ",FDimName,"   from t_mrpt_rule_bw2
where FYear = ",FYear," and FPeriod =  ",FPeriod,"
order by FSolutionNumber,FSubNumber")
    cat(sql_ins)
    col_selected <- c("FSolutionNumber","FSubNumber", FDimName)
    mydata <-tsda::sql_select(conn,sql_ins)
    print(2)
    ncount <- nrow(mydata)
    if(ncount >0){
      #存在数据的情况下
      mydata$flag <- paste0(mydata$FSolutionNumber,'_',mydata$FSubNumber)
      mydata_split <- split(mydata,mydata$flag)
      res_split <- lapply(mydata_split, function(data){
        print(3)
        print(data)
        row_split <- strsplit(data[1,FDimName],'\\+')
        ncount <- length(row_split)
        FSolutionNumber <- rep(data$FSolutionNumber[1],ncount)
        FSubNumber <- rep(data$FSubNumber[1],ncount)
        res <- data.frame( FSolutionNumber,FSubNumber,row_split,stringsAsFactors = F)
        names(res) <- col_selected
        rownames(res) <- NULL
        return(res)

      })
     print(4)
      mydata <- do.call('rbind',res_split)
      mydata[,FDimName] <- paste0(FPrefix,mydata[,FDimName])
      #删除之前的数据
      print(5)
      sql_del <- paste0("delete from  ",FDimTable," where FYear = ",FYear," and FPeriod =  ",FPeriod,"" )
      tsda::sql_update(conn,sql_del)

      #写入数据库
      #添加2个字段
      mydata$FYear = FYear
      mydata$FPeriod = FPeriod
      print(6)
      print(mydata)
      print(nrow(mydata))
      total_vol = nrow(mydata)
      pages = tsdo::paging_setting(total_vol,each_page = 500)
      n_page = nrow(pages)
      lapply(1:n_page, function(i){
        print(pages[i,])
        FStart = pages$FStart[i]
        FEnd = pages$FEnd[i]
        data = mydata[FStart:FEnd,]
        tsda::db_writeTable(conn = conn,table_name = FDimTable,r_object = data,append =T )


      })


      #tsda::db_writeTable(conn = conn,table_name = FDimTable,r_object = mydata,append =T )



    }

  }else{
    # 原有的功能

    sql_ins <- paste0(" select * from t_mrpt_rule_bw2
where FYear = ",FYear," and FPeriod =  ",FPeriod,"
order by FSolutionNumber,FSubNumber")
    mydata <-tsda::sql_select(conn,sql_ins)
    ncount <- nrow(mydata)
    if(ncount >0){
      col_selected <- c("FSolutionNumber","FSubNumber", FDimName)
      mydata <- mydata[!is.na(mydata[,FDimName]),  ]
      ncount2 <- nrow(mydata)
      #过滤后进行判断
      if(ncount2 >0){
        mydata$flag <- paste0(mydata$FSolutionNumber,'_',mydata$FSubNumber)
        mydata_split <- split(mydata,mydata$flag)
        res_split <- lapply(mydata_split, function(data){
          row_split <- strsplit(data[1,FDimName],'\\+')
          ncount <- length(row_split)
          FSolutionNumber <- rep(data$FSolutionNumber[1],ncount)
          FSubNumber <- rep(data$FSubNumber[1],ncount)
          res <- data.frame( FSolutionNumber,FSubNumber,row_split,stringsAsFactors = F)
          names(res) <- col_selected
          rownames(res) <- NULL
          return(res)

        })

        mydata <- do.call('rbind',res_split)
        mydata[,FDimName] <- paste0(FPrefix,mydata[,FDimName])
        #删除之前的数据
        sql_del <- paste0("delete from  ",FDimTable," where FYear = ",FYear," and FPeriod =  ",FPeriod,"" )
        tsda::sql_update(conn,sql_del)

        #写入数据库
        #添加2个字段
        mydata$FYear = FYear
        mydata$FPeriod = FPeriod
        total_vol = nrow(mydata)
        pages = tsdo::paging_setting(total_vol,each_page = 500)
        n_page = nrow(pages)
        lapply(1:n_page, function(i){
          print(pages[i,])
          FStart = pages$FStart[i]
          FEnd = pages$FEnd[i]
          data = mydata[FStart:FEnd,]
          tsda::db_writeTable(conn = conn,table_name = FDimTable,r_object = data,append =T )


        })
        #tsda::db_writeTable(conn = conn,table_name = FDimTable,r_object = mydata,append =T )

      }





    }

  }


    return(mydata)

}


#' BW报表规则V2读取内容
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#'mrpt_md_rule_bw2_dim_allocation()
mrpt_md_rule_bw2_value_allocation <-function(conn=tsda::conn_rds('jlrds'),
                                             FYear =2021,
                                             FPeriod = 6

){
  sql_ins <- paste0("
 select FSolutionNumber,FSubNumber,FValueType,FBrand,FChannel,FRptItemNumber,FRptItemName,FYear,FPeriod,FRate  from t_mrpt_rule_bw2
 where FYear = ",FYear," and FPeriod =   ",FPeriod,"
order by FSolutionNumber,FSubNumber")
  mydata <-tsda::sql_select(conn,sql_ins)
  print(mydata)
  ncount <- nrow(mydata)
  if(ncount >0){


      #删除之前的数据
      sql_del <- paste0("delete from   t_mrpt_ds_bw_formula where FYear = ",FYear," and FPeriod =   ",FPeriod," ")
      print(sql_del)
      tsda::sql_update(conn,sql_del)

      # #写入数据库
      # print(ncount)
      # tsda::db_writeTable(conn = conn,table_name = 't_mrpt_ds_bw_formula',r_object = mydata,append =T )

      sql_insert2 <- paste0("insert into t_mrpt_ds_bw_formula
 select FSolutionNumber,FSubNumber,FValueType,FBrand,FChannel,FRptItemNumber,FRptItemName,FYear,FPeriod,FRate  from t_mrpt_rule_bw2
 where FYear = ",FYear," and FPeriod =   ",FPeriod,"
order by FSolutionNumber,FSubNumber")
      mydata <-tsda::sql_update(conn,sql_insert2)



  }

  return(mydata)

}


#' 针对BW业务规则表进行分析
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_md_rule_bw2_dim_allocAll()
mrpt_md_rule_bw2_dim_allocAll <-function(conn=tsda::conn_rds('jlrds'),
                                         FYear = 2021,
                                         FPeriod = 6){
  #降维处理--
  print(1)
  mrpt_md_rule_bw2_dim_allocation(conn = conn,FDimName = 'F13_ItemGroupName_in',
                                  FDimTable = 't_mrpt_ds_bw_F13_itemGroupName_in',
                                  FPrefix = '',
                                  FYear = FYear,
                                  FPeriod = FPeriod,opt = FALSE)
  print(2)
  mrpt_md_rule_bw2_dim_allocation(conn = conn,FDimName = 'F13_ItemGroupName_Notin',
                                  FDimTable = 't_mrpt_ds_bw_F13_itemGroupName_Notin',
                                  FPrefix = '',
                                  FYear = FYear,
                                  FPeriod = FPeriod,opt = FALSE)
  print(3)
  mrpt_md_rule_bw2_dim_allocation(conn = conn,FDimName = 'F14_brandName_in',
                                  FDimTable = 't_mrpt_ds_bw_F14_brandName_in',
                                  FPrefix = '',
                                  FYear = FYear,
                                  FPeriod = FPeriod,opt = FALSE)
  print(4)
  mrpt_md_rule_bw2_dim_allocation(conn = conn,FDimName = 'F14_brandName_Notin',
                                  FDimTable = 't_mrpt_ds_bw_F14_brandName_Notin',
                                  FPrefix = '',
                                  FYear = FYear,
                                  FPeriod = FPeriod,opt = FALSE)
  print(5)
 mrpt_md_rule_bw2_dim_allocation(conn = conn,FDimName = 'F30_customerNumber_in',
                                  FDimTable = 't_mrpt_ds_bw_F30_customerNumber_in',
                                  FPrefix = '',
                                  FYear = FYear,
                                  FPeriod = FPeriod,opt = FALSE)
  print(6)
  mrpt_md_rule_bw2_dim_allocation(conn = conn,FDimName = 'F30_customerNumber_Notin',
                                  FDimTable = 't_mrpt_ds_bw_F30_customerNumber_Notin',
                                  FPrefix = '',
                                  FYear = FYear,
                                  FPeriod = FPeriod,opt = FALSE)
  print(7)
  mrpt_md_rule_bw2_dim_allocation(conn = conn,FDimName = 'F33_subChannelName_in',
                                  FDimTable = 't_mrpt_ds_bw_F33_subChannelName_in',
                                  FPrefix = '',
                                  FYear = FYear,
                                  FPeriod = FPeriod,opt = FALSE)
  print(8)
  mrpt_md_rule_bw2_dim_allocation(conn = conn,FDimName = 'F33_subChannelName_Notin',
                                  FDimTable = 't_mrpt_ds_bw_F33_subChannelName_Notin',
                                  FPrefix = '',
                                  FYear = FYear,
                                  FPeriod = FPeriod,opt = FALSE)
  print(9)
  mrpt_md_rule_bw2_dim_allocation(conn = conn,FDimName = 'F37_disctrictSaleDeptName_in',
                                  FDimTable = 't_mrpt_ds_bw_F37_disctrictSaleDeptName_in',
                                  FPrefix = '',
                                  FYear = FYear,
                                  FPeriod = FPeriod,opt = FALSE)
  print(10)
  mrpt_md_rule_bw2_dim_allocation(conn = conn,FDimName = 'F37_disctrictSaleDeptName_Notin',
                                  FDimTable = 't_mrpt_ds_bw_F37_disctrictSaleDeptName_Notin',
                                  FPrefix = '',
                                  FYear = FYear,
                                  FPeriod = FPeriod,opt = FALSE)
  print(11)
  mrpt_md_rule_bw2_dim_allocation(conn = conn,FDimName = 'F41_channelName_in',
                                  FDimTable = 't_mrpt_ds_bw_F41_channelName_in',
                                  FPrefix = '',
                                  FYear = FYear,
                                  FPeriod = FPeriod,opt = FALSE)
  print(12)
  mrpt_md_rule_bw2_dim_allocation(conn = conn,FDimName = 'F41_channelName_Notin',
                                  FDimTable = 't_mrpt_ds_bw_F41_channelName_Notin',
                                  FPrefix = '',
                                  FYear = FYear,
                                  FPeriod = FPeriod,opt = FALSE)
  mrpt_md_rule_bw2_dim_allocation(conn = conn,FDimName = 'F61_costCenterControlNumber_in',
                                  FDimTable = 't_mrpt_ds_bw_F61_costCenterControlNumber_in',
                                  FPrefix = '1000/',
                                  FYear = FYear,
                                  FPeriod = FPeriod,opt = FALSE)
  print(13)
  mrpt_md_rule_bw2_dim_allocation(conn = conn,FDimName = 'F61_costCenterControlNumber_Notin',
                                  FDimTable = 't_mrpt_ds_bw_F61_costCenterControlNumber_Notin',
                                  FPrefix = '1000/',
                                  FYear = FYear,
                                  FPeriod = FPeriod,opt = FALSE)
  #添加到相应的指标计算
  print(14)
  mrpt_md_rule_bw2_value_allocation(conn = conn,
                                    FYear = FYear,
                                    FPeriod = FPeriod)




}