R/brandChannel_graph.R

In takewiki/jlrdspkg: package for jlrds app

#一、以下为关于品牌渠道的所有计算图谱------
#1.1 计算步骤-----
#1.1.1计算所有明细品牌渠道包含子渠道(核心)-------
#这是我们多次要找的子渠道
#' 计算所有明细渠道信息
#'
#' @param conn 连接
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_graph_calc_step1()
mrpt_graph_calc_step1 <- function(conn=tsda::conn_rds('jlrds')){

sql <- paste0("select * from vw_mrpt_md_brandchannel_graph_calc_step1")
data = tsda::sql_select(conn,sql)
return(data)

}


#1.1.2计算KA合计与特通合计-------
#' 计算KA合计与特通合计
#'
#' @param conn 连接
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_graph_calc_step2()
mrpt_graph_calc_step2 <- function(conn=tsda::conn_rds('jlrds')){

  sql <- paste0("select * from vw_mrpt_md_brandchannel_graph_calc_step2")
  data = tsda::sql_select(conn,sql)
  return(data)

}

#1.2 卷算1-----
#1.2.1 计算大客户及货架-----
#' 计算KA合计与特通合计
#'
#' @param conn 连接
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_graph_rollup1_step1()
mrpt_graph_rollup1_step1 <- function(conn=tsda::conn_rds('jlrds')){

  sql <- paste0("select * from vw_mrpt_md_brandchannel_graph_rollup1_step1")
  data = tsda::sql_select(conn,sql)
  return(data)

}


#1.2.2 计算事业部-----
#' 计算事业部这一层级
#'
#' @param conn 连接
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_graph_rollup1_step2()
mrpt_graph_rollup1_step2 <- function(conn=tsda::conn_rds('jlrds')){

  sql <- paste0("select * from vw_mrpt_md_brandchannel_graph_rollup1_step2")
  data = tsda::sql_select(conn,sql)
  return(data)

}


#1.2.2 计算集团管报-----
#' 计算集团级管理这一层级
#'
#' @param conn 连接
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_graph_rollup1_step3()
mrpt_graph_rollup1_step3 <- function(conn=tsda::conn_rds('jlrds')){

  sql <- paste0("select * from vw_mrpt_md_brandchannel_graph_rollup1_step3")
  data = tsda::sql_select(conn,sql)
  return(data)

}

#1.3 卷算2-----
#1.3.1计算品牌全渠道管报合计
#' 计算品牌全渠道的费用合计
#'
#' @param conn 连接
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_graph_rollup2_step1()
mrpt_graph_rollup2_step1 <- function(conn=tsda::conn_rds('jlrds')){

  sql <- paste0("select * from vw_mrpt_md_brandchannel_graph_rollup2_step1")
  data = tsda::sql_select(conn,sql)
  return(data)

}



#' 针对品牌渠道信息进行检查
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param src_table 来源表，用于日志中FTable
#' @param graph_table 图表,这个很有意思
#' @param count_table 历史数据表按品牌渠道进行统计
#' @param log_table 日志表
#' @param FPhase 阶段,用于日志分析
#' @param FStep 步骤步用于2-3-1分析
#' @param FStatus 状态表
#' @param valid 是否合法
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_preCheck_aux()
graph_preCheck_aux <- function(conn=tsda::conn_rds('jlrds'),
                               FYear =2019,
                               FPeriod =1,
                               src_table = 't_mrpt_actual',
                               graph_table = 'vw_mrpt_md_brandchannel_graph_calc_step1',
                               count_table = 'vw_mrpt_actual_count',
                               log_table = 't_mrpt_brandChannel_preCheck_log',
                               FPhase = 'calc',
                               FStep =1,
                               FStatus =2,
                               valid = FALSE
) {
  #说明本来应该有,实际没有提供
  if(!valid){
    suffix = "null"
  }else{
    suffix = " not null"
  }
  sql <- paste0("select  a.*, b.FBrand,b.FChannel from ",graph_table," a
left join  (select FBrand,FChannel,FBrand + FChannel as FBrandChannel from ",count_table,"
where FYear = ",FYear," and  FPeriod = ",FPeriod,"
) b
on  a.source = b.FBrand +b.FChannel
where b.FBrandChannel is ", suffix)
  #显示完整的SQL
  cat(sql)
  data = tsda::sql_select(conn,sql)

  ncount = nrow(data)
  if(ncount >0){
    data$FYear = FYear
    data$FPeriod = FPeriod
    # 增加相应的判断
    if(!valid){
      data$FBrand =''
      data$FChannel =''
    }

    data$FPhase = FPhase
    data$FStep = FStep
    data$FStatus =FStatus
    data$FTable = src_table
    #针对数据进行处理
    sql_del = paste0("delete from ",log_table,"  where FYear = ",FYear," and FPeriod = ",FPeriod,"
                   and FPhase = '",FPhase,"' and FStep = ",FStep," and FStatus = ",FStatus," and FTable = '",src_table,"'")
    tsda::sql_update(conn,sql_del)
    #然后再写入数据
    tsda::db_writeTable(conn = conn,table_name = log_table,r_object = data,append = T)


  }
  return(data)
}



#' 按目标更新状态
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#' @param log_table 日志表
#' @param FPhase 阶段
#' @param FStatus 状态
#' @param FStep 步骤
#' @param src_table 来源表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_updateStatus_target()
graph_updateStatus_target<- function(conn=tsda::conn_rds('jlrds'),
                                     FYear =2019,
                                     FPeriod =1,
                                     log_table = 't_mrpt_brandChannel_preCheck_log',
                                     FPhase='calc',
                                     FStatus  =0,
                                     FStep = 1,
                                     src_table = 't_mrpt_actual'

){
  sql <- paste0("update b set b.FStatus =1  from ",log_table,"  b
where
b.FYear =  ",FYear," and b.FPeriod = ",FPeriod," and  b.FPhase='",FPhase,"'   and b.FStatus  = ",FStatus," and b.FStep =  ",FStep,"
                and FTable ='",src_table,"' ")
  cat(sql)
  tsda::sql_update(conn,sql)

}



#' 更新数据结果,可以重复的步骤
#'
#' @param conn  连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param graph_table 衅表
#' @param count_table 实际表
#' @param src_table 业务来源表
#' @param log_table 日志表
#' @param FPhase 阶段
#' @param FStep 步骤
#' @param FStatus 状态
#' @param valid 是否合法性
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_preCheck_aux_target()
graph_preCheck_aux_target <- function(conn=tsda::conn_rds('jlrds'),
                                      FYear =2019,
                                      FPeriod =1,
                                      graph_table = 'vw_mrpt_md_brandchannel_graph_calc_step2',
                                      count_table ='vw_mrpt_actual_count',
                                      src_table = 't_mrpt_actual',
                                      log_table = 't_mrpt_brandChannel_preCheck_log',
                                      FPhase ='calc',
                                      FStep = 2,
                                      FStatus = 0,
                                      valid = FALSE

) {
  #添加判断
  if(!valid){
    suffix = "null"
  }else{
    suffix = " not null"
  }

  sql <- paste0("select  distinct  a.target as source,a.target_brand as source_brand,a.target_channel as source_channel,
b.FBrand,b.FChannel from ",graph_table," a
left join  (select FBrand,FChannel,FBrand + FChannel as FBrandChannel from ",count_table,"
where FYear =  ",FYear," and  FPeriod =  ",FPeriod,"
) b
on  a.target  = b.FBrand +b.FChannel
where b.FBrandChannel is  ",suffix,"")
  data = tsda::sql_select(conn,sql)

  ncount = nrow(data)
  if(ncount >0){
    #针对数据进行处理
    data$FYear = FYear
    data$FPeriod = FPeriod
    if(!valid){
      data$FBrand =''
      data$FChannel =''
    }

    data$FPhase =FPhase
    data$FStep = FStep
    data$FStatus = FStatus
    data$FTable = src_table
    sql_del = paste0("delete from  ",log_table,"  where FYear = ",FYear," and FPeriod = ",FPeriod,"
                   and FPhase = '",FPhase,"' and FStep = ",FStep," and FStatus = ",FStatus," and FTable = '",src_table,"'
                   ")
    tsda::sql_update(conn,sql_del)
    #然后再写入数据
    tsda::db_writeTable(conn = conn,table_name = log_table,r_object = data,append = T)


  }
  return(data)
}



#' 处理第2步的数据
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param src_view 来源视图
#' @param src_table 来源表
#' @param graph_table 图表
#' @param log_table 日志表
#' @param fcalcstep 计算步骤
#' @param FPhase 阶段
#' @param FStep 步骤
#' @param res_table 结果表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_writeRes()
graph_writeRes_target<- function(conn=tsda::conn_rds('jlrds'),
                                 FYear =2019,
                                 FPeriod =1,
                                 res_table = 't_mrpt3_res_actual',
                                 src_view = 'vw_mrpt_actual',
                                 src_table ='t_mrpt_actual',
                                 graph_table = 'vw_mrpt_md_brandchannel_graph_calc_step2',
                                 log_table = 't_mrpt_brandChannel_preCheck_log',
                                 fcalcstep = 2,
                                 FPhase='calc',

                                 FStep = 2

){
  #针对手工没有提供的管报，设置标记为2

  #删除之前的数据
  sql_del <- paste0("delete from   ",res_table,"
where fyear =  ",FYear," and fperiod =  ",FPeriod," and fcalcstep =  ",fcalcstep,"")
  cat(sql_del)
  tsda::sql_update(conn,sql_del)

  #分为2部分处理
  sql_1  = paste0("select *   from ",log_table," b
where
b.FYear =  ",FYear," and b.FPeriod = ",FPeriod," and  b.FPhase='",FPhase,"'   and b.FStatus  = ",1,"  and
b.FStep = ",FStep,"
and FTable ='",src_table,"'")
  cat(sql_1)
  data1 =  tsda::sql_select(conn,sql_1)
  ncount1 = nrow(data1)
  if(ncount1 >0){
    print('A')
    #存在数据的情况下,已有数据的情况下，直接引入，不再重算
    sql_1A <- paste0("
  insert into  ",res_table,"
select distinct   a.Fyear,a.Fperiod,a.Fbrand,a.Fchannel,a.FRptItemNumber,FRptItemName,FRptAmt , ",fcalcstep," as fcalcstep
from   ",src_view,"  a
left join   ",log_table,"  b
on a.Fyear =b.FYear and a.Fperiod = b.FPeriod and a.Fbrand =b.source_brand and a.Fchannel =b.source_channel
where  b.FYear =  ",FYear," and b.FPeriod = ",FPeriod," and  b.FPhase='",FPhase,"'   and b.FStatus  = ",1," and b.FStep = ",FStep," and b.FTable ='",src_table,"'
")
    cat(sql_1A)
    tsda::sql_update(conn,sql_1A)


    sql_rate = paste0("select FYear,FPeriod,source_brand as fbrandname,source_channel as fchannelname
from  ",log_table," b
where
b.FYear =  ",FYear," and b.FPeriod = ",FPeriod," and  b.FPhase='",FPhase,"'   and b.FStatus  = ",1," and b.FStep = ",FStep,"  and FTable ='",src_table,"'   ")
    cat(sql_rate)
    data_rate = tsda::sql_select(conn,sql_rate)
    ncount <- nrow(data_rate)
    if(ncount >0){
      lapply(1:ncount, function(i){
        # 处理年月
        FYear = data_rate$FYear[i]
        FPeriod = data_rate$FPeriod[i]
        FBrand = data_rate$fbrandname[i]
        FChannel = data_rate$fchannelname[i]
        #更新每一个毛利率
        #注意添加相应的参数，以实现通用性
        mrpt_index_update(conn = conn,FYear = FYear,FPeriod = FPeriod,FBrand = FBrand,FChannel = FChannel,res_table = res_table)
        # mrpt_calc_updateProfit(conn = conn,FYear = FYear,FPeriod = FPeriod,FBrand = FBrand,FChannel = FChannel,res_table = res_table)
        # mrpt_calc_updateProfitRate(conn = conn,FYear = FYear,FPeriod = FPeriod,FBrand = FBrand,FChannel = FChannel,res_table = res_table)
        # mrpt_calc_updateChannelProfit(conn = conn,FYear = FYear,FPeriod = FPeriod,FBrand = FBrand,FChannel = FChannel,res_table = res_table)
        # mrpt_calc_updateMarketProfit(conn = conn,FYear = FYear,FPeriod = FPeriod,FBrand = FBrand,FChannel = FChannel,res_table = res_table)


      })
    }


  }


  #若没有相应的数据，的确需要进行重算
  sql_0  = paste0("select *   from ",log_table," b
where
b.FYear =  ",FYear," and b.FPeriod = ",FPeriod," and  b.FPhase='",FPhase,"'   and b.FStatus  =  0  and b.FStep = ",FStep,"
and FTable ='",src_table,"'")
  data0 =  tsda::sql_select(conn,sql_0)
  ncount0 = nrow(data0)
  cat(sql_0)

  if(ncount0 >0){
    print('B')
    #数据没有的情况，自动生成
    sql0A = paste0("
     insert into  ",res_table,"
    select a.Fyear,a.Fperiod,b.target_brand as FBrand,b.target_channel as FChannel,
a.FRptItemNumber,a.FRptItemName,sum(a.FRptAmt) as FRptAmt, ",fcalcstep," as FCalcStep

from  ",res_table,"  a
inner join  ",graph_table," b
on a.Fbrand = b.source_brand and a.Fchannel = b.source_channel

where a.Fyear =  ",FYear," and a.Fperiod = ",FPeriod,"
and b.target in
(select  source   from ",log_table," b
where
b.FYear =  ",FYear," and b.FPeriod = ",FPeriod," and  b.FPhase='",FPhase,"'   and b.FStatus  =0  and b.FStep = ",FStep,"
and FTable ='",src_table,"')
group by a.Fyear,a.Fperiod,b.target_brand,b.target_channel ,
a.FRptItemNumber,a.FRptItemName
")
    tsda::sql_update(conn,sql0A)


    sql_rate = paste0("select FYear,FPeriod,source_brand as fbrandname,source_channel as fchannelname
from  ",log_table," b
where
b.FYear =  ",FYear," and b.FPeriod = ",FPeriod," and  b.FPhase='",FPhase,"'   and b.FStatus  = 0 and b.FStep = ",FStep,"  and FTable ='",src_table,"'   ")
    cat(sql_rate)
    data_rate = tsda::sql_select(conn,sql_rate)
    ncount <- nrow(data_rate)
    if(ncount >0){
      lapply(1:ncount, function(i){
        # 处理年月
        FYear = data_rate$FYear[i]
        FPeriod = data_rate$FPeriod[i]
        FBrand = data_rate$fbrandname[i]
        FChannel = data_rate$fchannelname[i]
        #更新每一个毛利率
        #注意添加相应的参数，以实现通用性
        mrpt_calc_updateProfit(conn = conn,FYear = FYear,FPeriod = FPeriod,FBrand = FBrand,FChannel = FChannel,res_table = res_table)
        mrpt_calc_updateProfitRate(conn = conn,FYear = FYear,FPeriod = FPeriod,FBrand = FBrand,FChannel = FChannel,res_table = res_table)
        mrpt_calc_updateChannelProfit(conn = conn,FYear = FYear,FPeriod = FPeriod,FBrand = FBrand,FChannel = FChannel,res_table = res_table)
        mrpt_calc_updateMarketProfit(conn = conn,FYear = FYear,FPeriod = FPeriod,FBrand = FBrand,FChannel = FChannel,res_table = res_table)


      })
    }





  }





}