R/calcData.R

In takewiki/jlrdspkg: package for jlrds app

#' 检验品牌渠道信息
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param plan_table  计划表
#' @param data_table 数据表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_plan()
mrpt_calc_plan <- function(conn=tsda::conn_rds('jlrds'),
                                          FYear =2021,
                                          FPeriod =1,
                                          plan_table = 't_mrpt2_calcPlan',
                                          data_table = 'vw_mrpt_target_count'

                           ){
sql_del <- paste0("delete from   ",plan_table,"
where FYear=  ",FYear," and FPeriod = ",FPeriod," ")
cat(sql_del)
tsda::sql_update(conn,sql_del)

sql <- paste0(" insert into  ",plan_table,"
select  ",FYear," as FYear, ",FPeriod," as FPeriod,a.fbrandname,a.fchannelname,b.Fbrand,b.Fchannel,a.fbrandchannelNUMBER,a.fcalcStep,a.fparentnumber_run,a.fparentnumber_roll,a.FParentNumber_roll2,a.fcalcMode, 0 as FIsDo
from t_mrpt3_md_brandChannel a
left join (select * from   ",data_table," where Fyear =  ",FYear," and Fperiod =  ",FPeriod,") b
on a.fbrandname = b.fbrand
and a.fchannelname = b.Fchannel
where   a.fchannelnumber <> 'M'
")
cat(sql)

data <- tsda::sql_update(conn,sql)
return(data)

}




#' 设置步骤1已处理的数据
#'
#' @param conn  连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param plan_table 计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_brandChannel_step1_setDone()
mrpt_calc_brandChannel_step1_setDone <- function(conn=tsda::conn_rds('jlrds'),
                                            FYear =2021,
                                            FPeriod =1,
                                            plan_table = 't_mrpt2_calcPlan'
                                            ){
#针对手工没有提供的管报，设置标记为2
sql <- paste0("update a set FIsDo = 2  from  ",plan_table," a
where  FYear =  ",FYear," and FPeriod = ",FPeriod," and fcalcstep =1  and Fchannel is null ")
cat(sql)
tsda::sql_update(conn,sql)

}



#' 写入步骤1的结果
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#' @param res_table 结果表
#' @param src_table  来源于
#' @param plan_table 计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_brandChannel_step1_writeRes()
mrpt_calc_brandChannel_step1_writeRes<- function(conn=tsda::conn_rds('jlrds'),
                                                 FYear =2021,
                                                 FPeriod =1,
                                                 res_table = 't_mrpt3_res',
                                                 src_table = 't_mrpt_target',
                                                 plan_table = 't_mrpt2_calcPlan'

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

  #删除之前的数据
  sql_del <- paste0("delete from    ",res_table,"
where fyear =  ",FYear," and fperiod =  ",FPeriod," and fcalcstep = 1")
  cat(sql_del)
  tsda::sql_update(conn,sql_del)
  sql <- paste0("
insert into  ",res_table,"
select distinct   a.Fyear,a.Fperiod,a.Fbrand,a.Fchannel,a.FRptItemNumber,FRptItemName,FRptAmt ,b.fcalcstep

from   ",src_table," a
left join   ",plan_table," b
on a.Fyear =b.FYear and a.Fperiod = b.FPeriod and a.Fbrand =b.Fbrand and a.Fchannel =b.Fchannel
where  b.FYear =  ",FYear," and b.FPeriod = ",FPeriod," and b.fcalcstep =1   and b.FIsDo  =0")
  cat(sql)
  tsda::sql_update(conn,sql)

  sql_rate = paste0("select FYear,FPeriod,fbrandname,fchannelname from ",plan_table,"
where  FYear =  ",FYear," and FPeriod =  ",FPeriod," and fcalcstep = 1   and  FIsDo  =0 ")
  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)


    })
  }



}


#' 更新步骤1的状态
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param plan_table 计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_brandChannel_step1_updateStatus()
mrpt_calc_brandChannel_step1_updateStatus<- function(conn=tsda::conn_rds('jlrds'),
                                                 FYear =2021,
                                                 FPeriod =1,
                                                 plan_table = 't_mrpt2_calcPlan'
                                                 ){
sql <- paste0("update a set  a.FIsDo =1  from  ",plan_table," a
where  FYear = ",FYear," and FPeriod =  ",FPeriod," and fcalcstep =1   and FIsDo  =0")
cat(sql)
tsda::sql_update(conn,sql)

}



#' 更新毛利数据
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#' @param FBrand 品牌
#' @param FChannel 渠道
#' @param res_table 结果表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_updateProfitRate()
mrpt_calc_updateProfit<- function(conn=tsda::conn_rds('jlrds'),
                                      FYear =2021,
                                      FPeriod =1,
                                      FBrand ='自然堂',
                                      FChannel='大客户KA小计',
                                      res_table = 't_mrpt3_res'

){
  sql <- paste0("select   FRptItemNumber,FRptAmt from  ",res_table,"
where fyear = ",FYear," and fperiod = ",FPeriod,"  and
fbrand ='",FBrand,"' and FChannel ='",FChannel,"' and FRptItemNumber in  ('I04','I05')
")
  cat(sql)


  data <- tsda::sql_select(conn,sql)

  ncount = nrow(data)
  if(ncount >0){

    data_I04 = data[data$FRptItemNumber == 'I04','FRptAmt']
    data_I05 = data[data$FRptItemNumber == 'I05','FRptAmt']

      data_I06 = round(data_I04-data_I05,4)


    sql_update = paste0("update  a  set  FRptAmt  =  ",data_I06,"  from  ",res_table," a
where fyear = ",FYear," and fperiod = ",FPeriod,"  and
fbrand ='",FBrand,"' and FChannel ='",FChannel,"' and FRptItemNumber  ='I06'")
    cat(sql_update)
    tsda::sql_update(conn,sql_update)

  }




}




#' 渠道利润
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#' @param FBrand 品牌
#' @param FChannel 渠道
#' @param res_table 结果表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_updateProfitRate()
mrpt_calc_updateChannelProfit<- function(conn=tsda::conn_rds('jlrds'),
                                  FYear =2021,
                                  FPeriod =1,
                                  FBrand ='自然堂',
                                  FChannel='大客户KA小计',
                                  res_table = 't_mrpt3_res'

){
  sql <- paste0("select   FRptItemNumber,FRptAmt from  ",res_table,"
where fyear = ",FYear," and fperiod = ",FPeriod,"  and
fbrand ='",FBrand,"' and FChannel ='",FChannel,"' and FRptItemNumber in  ('I06','I08')
")
  cat(sql)


  data <- tsda::sql_select(conn,sql)

  ncount = nrow(data)
  if(ncount >0){

    data_I06 = data[data$FRptItemNumber == 'I06','FRptAmt']
    data_I08 = data[data$FRptItemNumber == 'I08','FRptAmt']

    data_I26 = round(data_I06-data_I08,4)


    sql_update = paste0("update  a  set  FRptAmt  =  ",data_I26,"  from  ",res_table," a
where fyear = ",FYear," and fperiod = ",FPeriod,"  and
fbrand ='",FBrand,"' and FChannel ='",FChannel,"' and FRptItemNumber  ='I26'")
    cat(sql_update)
    tsda::sql_update(conn,sql_update)

  }




}



#' 市场利润
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#' @param FBrand 品牌
#' @param FChannel 渠道
#' @param res_table 结果表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_updateProfitRate()
mrpt_calc_updateMarketProfit<- function(conn=tsda::conn_rds('jlrds'),
                                         FYear =2021,
                                         FPeriod =1,
                                         FBrand ='自然堂',
                                         FChannel='大客户KA小计',
                                         res_table = 't_mrpt3_res'

){
  sql <- paste0("select   FRptItemNumber,FRptAmt from  ",res_table,"
where fyear = ",FYear," and fperiod = ",FPeriod,"  and
fbrand ='",FBrand,"' and FChannel ='",FChannel,"' and FRptItemNumber in  ('I26','I27')
")
  cat(sql)


  data <- tsda::sql_select(conn,sql)

  ncount = nrow(data)
  if(ncount >0){

    data_I26 = data[data$FRptItemNumber == 'I26','FRptAmt']
    data_I27 = data[data$FRptItemNumber == 'I27','FRptAmt']

    data_I44 = round(data_I26-data_I27,4)


    sql_update = paste0("update  a  set  FRptAmt  =  ",data_I44,"  from  ",res_table," a
where fyear = ",FYear," and fperiod = ",FPeriod,"  and
fbrand ='",FBrand,"' and FChannel ='",FChannel,"' and FRptItemNumber  ='I44'")
    cat(sql_update)
    tsda::sql_update(conn,sql_update)

  }




}






#' 更新毛利率数据
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#' @param FBrand 品牌
#' @param FChannel 渠道
#' @param res_table 结果表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_updateProfitRate()
mrpt_calc_updateProfitRate<- function(conn=tsda::conn_rds('jlrds'),
                                                 FYear =2021,
                                                 FPeriod =1,
                                                 FBrand ='自然堂',
                                                 FChannel='大客户KA小计',
                                                 res_table = 't_mrpt3_res'

                                      ){
sql <- paste0("select   FRptItemNumber,FRptAmt from  ",res_table,"
where fyear = ",FYear," and fperiod = ",FPeriod,"  and
fbrand ='",FBrand,"' and FChannel ='",FChannel,"' and FRptItemNumber in  ('I04','I06')
")
cat(sql)


 data <- tsda::sql_select(conn,sql)

 ncount = nrow(data)
 if(ncount >0){

   data_I04 = data[data$FRptItemNumber == 'I04','FRptAmt']
   data_I06 = data[data$FRptItemNumber == 'I06','FRptAmt']
   #兼容收入为0的情况
   print(data_I04)
   if (data_I04 == 0 ){
     data_I07 = 0
   }else{
     data_I07 = round(data_I06/data_I04,4)
   }

   sql_update = paste0("update  a  set  FRptAmt  =  ",data_I07,"  from  ",res_table," a
where fyear = ",FYear," and fperiod = ",FPeriod,"  and
fbrand ='",FBrand,"' and FChannel ='",FChannel,"' and FRptItemNumber  ='I07'")
   cat(sql_update)
   tsda::sql_update(conn,sql_update)

 }




}





#' 更新中后台累计计算
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param FBrand 品牌
#' @param FChannel 渠道
#' @param res_table 表名
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_updateProfit_MPV()
mrpt_calc_updateProfit_MPV<- function(conn=tsda::conn_rds('jlrds'),
                                      FYear =2021,
                                      FPeriod =1,
                                      FBrand ='自然堂',
                                      FChannel='大客户KA小计',
                                      res_table = 't_mrpt3_res'

){

  if(FBrand=='JALA集团' & FChannel == '全渠道'){
  #code here

    sql <- paste0("select   FRptItemNumber,FRptAmt from  ",res_table,"
where fyear = ",FYear," and fperiod = ",FPeriod,"  and
fbrand ='",FBrand,"' and FChannel ='",FChannel,"' and FRptItemNumber in  ('I44','I45')
")
    cat(sql)


    data <- tsda::sql_select(conn,sql)

    ncount = nrow(data)
    if(ncount >0){

      data_I44 = data[data$FRptItemNumber == 'I44','FRptAmt']
      data_I45 = data[data$FRptItemNumber == 'I45','FRptAmt']

        data_I46 = data_I44 -data_I45


      sql_update = paste0("update  a  set  FRptAmt  =  ",data_I46,"  from  ",res_table," a
where fyear = ",FYear," and fperiod = ",FPeriod,"  and
fbrand ='",FBrand,"' and FChannel ='",FChannel,"' and FRptItemNumber  ='I46'")
      cat(sql_update)
      tsda::sql_update(conn,sql_update)


  #code end here
  }


  }
}









#' 处理第2步的数据
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param res_table 结果表
#' @param plan_table 计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_brandChannel_step2_writeRes()
mrpt_calc_brandChannel_step2_writeRes<- function(conn=tsda::conn_rds('jlrds'),
                                                 FYear =2021,
                                                 FPeriod =1,
                                                 res_table = 't_mrpt3_res',
                                                 plan_table ='t_mrpt2_calcPlan'


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

  #删除之前的数据
  sql_del <- paste0("delete from   ",res_table,"
where fyear =  ",FYear," and fperiod =  ",FPeriod," and fcalcstep = 2")
  cat(sql_del)
  tsda::sql_update(conn,sql_del)
  #针对数据处理汇总
  sql <- paste0("
insert into  ",res_table,"
select  a.FYear,a.FPeriod,c.fbrandname,c.fchannelName,a.FRptItemNumber,a.FRptItemName,sum(FRptAmt) as FRptAmt,2 as FCalcStep from  t_mrpt3_res  a
inner join   ",plan_table," b
on  a.fyear = b.FYear and a.fperiod =  b.FPeriod and a.fbrand = b.Fbrand and a.fchannel = b.Fchannel
left join t_mrpt3_md_brandChannel c
on b.fparentnumber_run = c.fbrandChannelNumber
where  b.FYear =  ",FYear," and b.FPeriod =  ",FPeriod," and b.fcalcstep = 1   and FIsDo  = 1
and   b.fparentnumber_run is not null
group by a.FYear,a.FPeriod,c.fbrandname,c.fchannelName,a.FRptItemNumber,a.FRptItemName
                ")
  cat(sql)
  tsda::sql_update(conn,sql)
  #毛利率不支持汇总处理

  sql_rate = paste0("select FYear,FPeriod,fbrandname,fchannelname from ",plan_table,"
where  FYear =  ",FYear," and FPeriod =  ",FPeriod," and fcalcstep = 2   and  FIsDo  =0 ")
  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)


    })
  }




}

#' 更新步骤2的状态
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param plan_table 计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_brandChannel_step1_updateStatus()
mrpt_calc_brandChannel_step2_updateStatus<- function(conn=tsda::conn_rds('jlrds'),
                                                     FYear =2021,
                                                     FPeriod =1,
                                                     plan_table = 't_mrpt2_calcPlan'
                                                     ){
  sql <- paste0("update a set  a.FIsDo =1  from  ",plan_table," a
where  FYear = ",FYear," and FPeriod =  ",FPeriod," and fcalcstep = 2   and FIsDo  =0")
  cat(sql)
  tsda::sql_update(conn,sql)

}


#' 步骤3设置未提供管报的数据部分
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#' @param plan_table 计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_brandChannel_step3_setDone()
mrpt_calc_brandChannel_step3_setDone<- function(conn=tsda::conn_rds('jlrds'),
                                                     FYear =2021,
                                                     FPeriod =1,
                                                     plan_table = 't_mrpt2_calcPlan'

                                                ){
  # 由于下级没有提供相应的数据，因此上一级也得不到数据，设置状态为2
  sql <- paste0("update a set a.FIsDo =2  from  ",plan_table," a
where FBrandChannelNumber in
(select   fparentNumber_roll from  ",plan_table," where FIsDo = 2 and fcalcStep =1
and  Fyear = ",FYear," and fperiod =  ",FPeriod,"
) and Fyear = ",FYear," and fperiod =  ",FPeriod," and Fchannel is null and FIsDo = 0")
  cat(sql)
  tsda::sql_update(conn,sql)

}


#' 处理第2步的数据
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_brandChannel_step3_writeRes()
mrpt_calc_brandChannel_step3_writeRes<- function(conn=tsda::conn_rds('jlrds'),
                                                 FYear =2021,
                                                 FPeriod =1,
                                                 res_table = 't_mrpt3_res',
                                                 plan_table = 't_mrpt2_calcPlan',
                                                 src_table = 't_mrpt_target'
                                                 ){
  #针对手工没有提供的管报，设置标记为2

  #删除之前的数据
  sql_del <- paste0("delete from   ",res_table,"
where fyear =  ",FYear," and fperiod =  ",FPeriod," and fcalcstep = 3")
  cat(sql_del)
  tsda::sql_update(conn,sql_del)
  #针对数据处理汇总,大客户事业部合计数
  sql <- paste0("
insert into  ",res_table,"
select  a.FYear,a.FPeriod,c.fbrandname,c.fchannelName,a.FRptItemNumber,a.FRptItemName,sum(FRptAmt) as FRptAmt,3 as FCalcStep from  ",res_table,"  a
inner join   ",plan_table," b
on  a.fyear = b.FYear and a.fperiod =  b.FPeriod and a.fbrand = b.Fbrandname and a.fchannel = b.Fchannelname
left join t_mrpt3_md_brandChannel c
on b.fparentnumber_roll = c.fbrandChannelNumber
where  b.FYear =  ",FYear," and b.FPeriod = ",FPeriod," and b.fcalcstep <=2   and b.FIsDo  =1 and c.FCalcStep =3
and   b.fparentnumber_roll is not null
group by a.FYear,a.FPeriod,c.fbrandname,c.fchannelName,a.FRptItemNumber,a.FRptItemName ")
  cat(sql)
  tsda::sql_update(conn,sql)

  sql2 <- paste0("
insert into ",res_table,"
select  distinct  a.Fyear,a.Fperiod,a.Fbrand,a.Fchannel,a.FRptItemNumber,FRptItemName,FRptAmt ,b.fcalcstep

from  ",src_table," a
left join  ",plan_table," b
on a.Fyear =b.FYear and a.Fperiod = b.FPeriod and a.Fbrand =b.Fbrand and a.Fchannel =b.Fchannel
where  b.FYear =  ",FYear," and b.FPeriod = ",FPeriod," and b.fcalcstep = 3   and b.FIsDo  =0")
  cat(sql2)
  tsda::sql_update(conn,sql2)


  #毛利率不支持汇总处理

  sql_rate = paste0("select FYear,FPeriod,fbrandname,fchannelname from ",plan_table,"
where  FYear =  ",FYear," and FPeriod =  ",FPeriod," and fcalcstep = 3   and  FIsDo  =0 ")
  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)


    })
  }




}



#' 更新步骤3的状态
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param plan_table 计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_brandChannel_step3_updateStatus
mrpt_calc_brandChannel_step3_updateStatus<- function(conn=tsda::conn_rds('jlrds'),
                                                     FYear =2021,
                                                     FPeriod =1,
                                                     plan_table = 't_mrpt2_calcPlan'
                                                     ){
  sql <- paste0("update a set  a.FIsDo =1  from  ",plan_table,"  a
where  FYear = ",FYear," and FPeriod =  ",FPeriod," and fcalcstep = 3   and FIsDo  =0")
  tsda::sql_update(conn,sql)

}


#' 处理第4步的数据
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param res_table 结果表
#' @param plan_table 计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_brandChannel_step3_writeRes()
mrpt_calc_brandChannel_step4_writeRes<- function(conn=tsda::conn_rds('jlrds'),
                                                 FYear =2021,
                                                 FPeriod =1,
                                                 res_table = 't_mrpt3_res',
                                                 plan_table = 't_mrpt2_calcPlan'

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

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

  sql2 <- paste0("
insert into  ",res_table,"
select  a.FYear,a.FPeriod,c.fbrandname,c.fchannelName,a.FRptItemNumber,a.FRptItemName,sum(FRptAmt) as FRptAmt,4 as FCalcStep from  ",res_table,"  a
inner join   ",plan_table," b
on  a.fyear = b.FYear and a.fperiod =  b.FPeriod and a.fbrand = b.Fbrandname and a.fchannel = b.Fchannelname
left join t_mrpt3_md_brandChannel c
on b.fparentnumber_roll2 = c.fbrandChannelNumber
where  b.FYear = ",FYear," and b.FPeriod =  ",FPeriod,"
and   b.fparentnumber_roll2 is not null
group by a.FYear,a.FPeriod,c.fbrandname,c.fchannelName,a.FRptItemNumber,a.FRptItemName")
  tsda::sql_update(conn,sql2)


  #毛利率不支持汇总处理

  sql_rate = paste0("select FYear,FPeriod,fbrandname,fchannelname from ",plan_table,"
where  FYear =  ",FYear," and FPeriod =  ",FPeriod," and fcalcstep = 4   and  FIsDo  =0 ")
  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)


    })
  }




}


#' 更新步骤4的状态
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#' @param plan_table  计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_brandChannel_step4_updateStatus()
mrpt_calc_brandChannel_step4_updateStatus<- function(conn=tsda::conn_rds('jlrds'),
                                                     FYear =2021,
                                                     FPeriod =1,
                                                     plan_table = 't_mrpt2_calcPlan'
                                                     ){
  sql <- paste0("update a set  a.FIsDo =1  from ",plan_table," a
where  FYear = ",FYear," and FPeriod =  ",FPeriod," and fcalcstep = 4   and FIsDo  =0")
  tsda::sql_update(conn,sql)

}



#' 处理第5步的数据
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param res_table 结果表
#' @param plan_table 计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_brandChannel_step5_writeRes()
mrpt_calc_brandChannel_step5_writeRes<- function(conn=tsda::conn_rds('jlrds'),
                                                 FYear =2021,
                                                 FPeriod =1,
                                                 res_table = 't_mrpt3_res',
                                                 plan_table ='t_mrpt2_calcPlan'
                                                 ){
  #针对手工没有提供的管报，设置标记为2

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

  sql2 <- paste0("
insert into  ",res_table,"
select  a.FYear,a.FPeriod,c.fbrandname,c.fchannelName,a.FRptItemNumber,a.FRptItemName,sum(FRptAmt) as FRptAmt,5 as FCalcStep from  ",res_table,"  a
inner join   ",plan_table," b
on  a.fyear = b.FYear and a.fperiod =  b.FPeriod and a.fbrand = b.Fbrandname and a.fchannel = b.Fchannelname
left join t_mrpt3_md_brandChannel c
on b.fparentnumber_roll = c.fbrandChannelNumber
where  b.FYear =  ",FYear," and b.FPeriod =  ",FPeriod,"
and   b.fparentnumber_roll  like '00.%'  and b.fcalcstep <5 and b.FIsDo = 1
group by a.FYear,a.FPeriod,c.fbrandname,c.fchannelName,a.FRptItemNumber,a.FRptItemName")
  tsda::sql_update(conn,sql2)
  print('B')


  #毛利率不支持汇总处理

  sql_rate = paste0("select FYear,FPeriod,fbrandname,fchannelname from ",plan_table,"
where  FYear =  ",FYear," and FPeriod =  ",FPeriod," and fcalcstep = 5   and  FIsDo  =0 ")
  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)


    })
  }





}



#' 更新步骤5的状态
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#' @param plan_table 计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_brandChannel_step4_updateStatus()
mrpt_calc_brandChannel_step5_updateStatus<- function(conn=tsda::conn_rds('jlrds'),
                                                     FYear =2021,
                                                     FPeriod =1,
                                                     plan_table = 't_mrpt2_calcPlan'
                                                     ){
  sql <- paste0("update a set  a.FIsDo =1  from  ",plan_table," a
where  FYear = ",FYear," and FPeriod =  ",FPeriod," and fcalcstep = 5   and FIsDo  =0")
  tsda::sql_update(conn,sql)

}




#' 处理第6步的数据
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param res_table 结果表
#' @param plan_table 计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_brandChannel_step5_writeRes()
mrpt_calc_brandChannel_step6_writeRes<- function(conn=tsda::conn_rds('jlrds'),
                                                 FYear =2021,
                                                 FPeriod =1,
                                                 res_table = 't_mrpt3_res',
                                                 plan_table = 't_mrpt2_calcPlan'
                                                 ){
  #针对手工没有提供的管报，设置标记为2

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

  sql2 <- paste0("
insert into ",res_table,"
select  a.FYear,a.FPeriod,c.fbrandname,c.fchannelName,a.FRptItemNumber,a.FRptItemName,sum(FRptAmt) as FRptAmt,6 as FCalcStep from  ",res_table,"  a
inner join   ",plan_table," b
on  a.fyear = b.FYear and a.fperiod =  b.FPeriod and a.fbrand = b.Fbrandname and a.fchannel = b.Fchannelname
left join t_mrpt3_md_brandChannel c
on b.fparentnumber_roll = c.fbrandChannelNumber
where  b.FYear =  ",FYear," and b.FPeriod = ",FPeriod,"
and   b.fparentnumber_roll  =  '00.00'   and b.fcalcstep = 5 and b.FIsDo = 1
group by a.FYear,a.FPeriod,c.fbrandname,c.fchannelName,a.FRptItemNumber,a.FRptItemName")
  tsda::sql_update(conn,sql2)
  print('B')


  #毛利率不支持汇总处理

  sql_rate = paste0("select FYear,FPeriod,fbrandname,fchannelname from ",plan_table,"
where  FYear =  ",FYear," and FPeriod =  ",FPeriod," and fcalcstep = 6   and  FIsDo  =0 ")
  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)


    })
  }




}


#' 更新步骤6的状态
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#' @param plan_table 计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_brandChannel_step4_updateStatus()
mrpt_calc_brandChannel_step6_updateStatus<- function(conn=tsda::conn_rds('jlrds'),
                                                     FYear =2021,
                                                     FPeriod =1,
                                                     plan_table ='t_mrpt2_calcPlan'
                                                     ){
  sql <- paste0("update a set  a.FIsDo =1  from ",plan_table," a
where  FYear = ",FYear," and FPeriod =  ",FPeriod," and fcalcstep = 5   and FIsDo  =0")
  tsda::sql_update(conn,sql)

}




#' 计划结果计算
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param plan_table 计划表
#' @param src_table 来源表
#' @param res_table 结果表
#' @param data_table 数据表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_runAll()
mrpt_calc_runAll <- function(conn=tsda::conn_rds('jlrds'),
                             FYear =2021,
                             FPeriod =1,
                             plan_table ='t_mrpt2_calcPlan',
                             src_table = 't_mrpt_target',
                             res_table = 't_mrpt3_res',
                             data_table = 'vw_mrpt_target_count'){
  mrpt_calc_plan(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table,data_table = data_table)
  mrpt_calc_brandChannel_step1_setDone(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step1_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,src_table = src_table,plan_table = plan_table )
  mrpt_calc_brandChannel_step1_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step2_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table)
  mrpt_calc_brandChannel_step2_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step3_setDone(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step3_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table,src_table = src_table)
  mrpt_calc_brandChannel_step3_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step4_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table)
  mrpt_calc_brandChannel_step4_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step5_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table)
  mrpt_calc_brandChannel_step5_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step6_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table)
  mrpt_calc_brandChannel_step6_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)

}



#' 计划结果计算-手工管报数据
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param plan_table 计划表
#' @param src_table 来源表
#' @param res_table 结果表
#' @param data_table 数据表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_runAll()
mrpt_calc_runAll_target <- function(conn=tsda::conn_rds('jlrds'),
                             FYear =2021,
                             FPeriod =1,
                             plan_table ='t_mrpt2_calcPlan',
                             src_table = 't_mrpt_target',
                             res_table = 't_mrpt3_res',
                             data_table = 'vw_mrpt_target_count'){
  mrpt_calc_plan(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table,data_table = data_table)
  mrpt_calc_brandChannel_step1_setDone(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step1_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,src_table = src_table,plan_table = plan_table )
  mrpt_calc_brandChannel_step1_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step2_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table)
  mrpt_calc_brandChannel_step2_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step3_setDone(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step3_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table,src_table = src_table)
  mrpt_calc_brandChannel_step3_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step4_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table)
  mrpt_calc_brandChannel_step4_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step5_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table)
  mrpt_calc_brandChannel_step5_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step6_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table)
  mrpt_calc_brandChannel_step6_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  #添加集团管报中后台费用的计算
  mrpt_calc_mpv(conn = conn,data_table = res_table,FYear = FYear,FPeriod = FPeriod)

}



#' 计划结果计算-RPA数据
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param plan_table 计划表
#' @param src_table 来源表
#' @param res_table 结果表
#' @param data_table 数据表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_runAll()
mrpt_calc_runAll_rpa <- function(conn=tsda::conn_rds('jlrds'),
                                    FYear =2021,
                                    FPeriod =7,
                                    plan_table ='t_mrpt2_calcPlan',
                                    src_table = 'vw_fi_rpa',
                                    res_table = 't_mrpt3_res',
                                    data_table = 'vw_mrpt_rpa_count'){
  mrpt_calc_plan(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table,data_table = data_table)
  mrpt_calc_brandChannel_step1_setDone(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step1_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,src_table = src_table,plan_table = plan_table )
  mrpt_calc_brandChannel_step1_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step2_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table)
  mrpt_calc_brandChannel_step2_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step3_setDone(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step3_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table,src_table = src_table)
  mrpt_calc_brandChannel_step3_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step4_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table)
  mrpt_calc_brandChannel_step4_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step5_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table)
  mrpt_calc_brandChannel_step5_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step6_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table)
  mrpt_calc_brandChannel_step6_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  #添加集团管报中后台费用的计算
  mrpt_calc_mpv(conn = conn,data_table = res_table,FYear = FYear,FPeriod = FPeriod)

}




#' 针对历史数据进行处理
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param plan_table 计划表
#' @param src_table 来源表
#' @param res_table 结果表
#' @param data_table 数据表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_runAll_actual()
mrpt_calc_runAll_actual <- function(conn=tsda::conn_rds('jlrds'),
                             FYear =2021,
                             FPeriod =1,
                             plan_table ='t_mrpt2_calcPlan_actual',
                             src_table = 'vw_mrpt_actual',
                             res_table = 't_mrpt3_res_actual',
                             data_table = 'vw_mrpt_actual_count'){
  mrpt_calc_plan(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table,data_table = data_table)
  mrpt_calc_brandChannel_step1_setDone(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step1_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,src_table = src_table,plan_table = plan_table )
  mrpt_calc_brandChannel_step1_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step2_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table)
  mrpt_calc_brandChannel_step2_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step3_setDone(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step3_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table,src_table = src_table)
  mrpt_calc_brandChannel_step3_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step4_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table)
  mrpt_calc_brandChannel_step4_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step5_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table)
  mrpt_calc_brandChannel_step5_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step6_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table)
  mrpt_calc_brandChannel_step6_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  #增加中后台费用的计算
  mrpt_calc_mpv(conn =conn,data_table = res_table,FYear = FYear,FPeriod = FPeriod )
}




#' 针对执行预算进行处理
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param plan_table 计划表
#' @param src_table 来源表
#' @param res_table 结果表
#' @param data_table 数据表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_runAll_actual()
mrpt_calc_runAll_budget <- function(conn=tsda::conn_rds('jlrds'),
                                    FYear =2021,
                                    FPeriod =1,
                                    plan_table ='t_mrpt2_calcPlan_budget',
                                    src_table = 'vw_mrpt_budget',
                                    res_table = 't_mrpt3_res_budget',
                                    data_table = 'vw_mrpt_budget_count'){
  mrpt_calc_plan(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table,data_table = data_table)
  mrpt_calc_brandChannel_step1_setDone(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step1_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,src_table = src_table,plan_table = plan_table )
  mrpt_calc_brandChannel_step1_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step2_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table)
  mrpt_calc_brandChannel_step2_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step3_setDone(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step3_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table,src_table = src_table)
  mrpt_calc_brandChannel_step3_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step4_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table)
  mrpt_calc_brandChannel_step4_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step5_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table)
  mrpt_calc_brandChannel_step5_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)
  mrpt_calc_brandChannel_step6_writeRes(conn = conn,FYear = FYear,FPeriod = FPeriod,res_table = res_table,plan_table = plan_table)
  mrpt_calc_brandChannel_step6_updateStatus(conn = conn,FYear = FYear,FPeriod = FPeriod,plan_table = plan_table)

}



#' 计算累计数
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#' @param FBrand 品牌
#' @param FChannel 渠道
#' @param table_data  数据表汇总前
#' @param table_cumSum 数据表汇总后
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_cumSum_item()
mrpt_calc_cumSum_item <- function(conn=tsda::conn_rds('jlrds'),
                             FYear = 2019,
                             FPeriod = 2,
                             FBrand ='春夏',
                             FChannel ='电商',
                             table_data = 't_mrpt3_res_actual',
                             table_cumSum = 't_mrpt3_res_actual_cumSum'




                             ){

#删除已有数据
  sql_del <- paste0("delete  from  ",table_cumSum,"

  where FYear =  ",FYear," and FPeriod  =  ",FPeriod," and FBrand ='",FBrand,"' and FChannel ='",FChannel,"'")
  tsda::sql_update(conn,sql_del)
#插入数据
sql_ins <- paste0("insert into  ",table_cumSum,"
select FYear, ",FPeriod," as FPeriod,FBrand,FChannel,FRptItemNumber,FRptItemName,sum(FRptAmt) as FRptAmt
  from ",table_data,"
  where FYear =  ",FYear," and FPeriod  <=  ",FPeriod," and FBrand ='",FBrand,"' and FChannel ='",FChannel,"'
  group by FYear,FBrand,FChannel,FRptItemNumber,FRptItemName
")
tsda::sql_update(conn,sql_ins)
#修复数据
mrpt_calc_updateProfitRate(conn = conn,FYear = FYear ,FPeriod = FPeriod,FBrand = FBrand,FChannel = FChannel,res_table = table_cumSum)
mrpt_calc_updateProfit(conn = conn,FYear = FYear ,FPeriod = FPeriod,FBrand = FBrand,FChannel = FChannel,res_table = table_cumSum)

mrpt_calc_updateChannelProfit(conn = conn,FYear = FYear ,FPeriod = FPeriod,FBrand = FBrand,FChannel = FChannel,res_table = table_cumSum)
mrpt_calc_updateMarketProfit(conn = conn,FYear = FYear ,FPeriod = FPeriod,FBrand = FBrand,FChannel = FChannel,res_table = table_cumSum)

#添加中后台的处理

mrpt_calc_updateProfit_MPV(conn = conn,FYear = FYear ,FPeriod = FPeriod,FBrand = FBrand,FChannel = FChannel,res_table = table_cumSum)

}



#' 计算累计数
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#' @param table_data 数据表明细
#' @param table_cumSum 数据表汇总
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_cumSum_Period()
mrpt_calc_cumSum_Period <- function(conn=tsda::conn_rds('jlrds'),
                                  FYear = 2019,
                                  FPeriod = 2,
                                  table_data = 't_mrpt3_res_actual',
                                  table_cumSum = 't_mrpt3_res_actual_cumSum'




){

  sql = paste0("  select  distinct   FBrand,FChannel  from   ",table_data,"
   where FYear = ",FYear," and FPeriod  <=  ",FPeriod," ")
  data <- tsda::sql_select(conn,sql)
  ncount <- nrow(data)
  if(ncount  >0){
    #存在数据的情况下
    lapply(1:ncount, function(i){
      FBrand = data$FBrand[i]
      FChannel = data$FChannel[i]
      #针对第一项进行处理
      mrpt_calc_cumSum_item(conn = conn,FYear = FYear,FPeriod = FPeriod,FBrand = FBrand,FChannel = FChannel,table_data = table_data,table_cumSum = table_cumSum)





    })



  }





}



#' 按年计算累计数
#'
#' @param conn  连接
#' @param FYear 年份
#' @param table_data 明细表
#'
#' @param table_cumSum 汇总表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_cumSum_Year()
mrpt_calc_cumSum_Year <- function(conn=tsda::conn_rds('jlrds'),
                                    FYear = 2019,

                                    table_data = 't_mrpt3_res_actual',
                                    table_cumSum = 't_mrpt3_res_actual_cumSum'




){

lapply(1:12, function(FPeriod){
  #针对每个月份数据进行处理
  print(FPeriod)
  mrpt_calc_cumSum_Period(conn = conn,FYear = FYear,FPeriod = FPeriod,table_data = table_data,table_cumSum = table_cumSum)

})


}

#' 计算累计数_历史数据
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_cumSum_Period_actual()
mrpt_calc_cumSum_Period_actual <- function(conn=tsda::conn_rds('jlrds'),
                                    FYear = 2019,
                                    FPeriod = 2




){
  #针对历史数据进行处理
  mrpt_calc_cumSum_Period(conn = conn,FYear = FYear,FPeriod = FPeriod,table_data = 't_mrpt3_res_actual',
                          table_cumSum = 't_mrpt3_res_actual_cumSum')

}


#' 按年计算累计数——历史数据
#'
#' @param conn  连接
#' @param FYear 年份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_cumSum_Year()
mrpt_calc_cumSum_Year_actual <- function(conn=tsda::conn_rds('jlrds'),
                                  FYear = 2019




){
  mrpt_calc_cumSum_Year(conn = conn,FYear = FYear,table_data = 't_mrpt3_res_actual',
                        table_cumSum = 't_mrpt3_res_actual_cumSum')

}



#' 计算累计数_执行预算
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_cumSum_Period_actual()
mrpt_calc_cumSum_Period_budget <- function(conn=tsda::conn_rds('jlrds'),
                                           FYear = 2019,
                                           FPeriod = 2




){
  #针对历史数据进行处理
  mrpt_calc_cumSum_Period(conn = conn,FYear = FYear,FPeriod = FPeriod,table_data = 't_mrpt3_res_budget',
                          table_cumSum = 't_mrpt3_res_budget_cumSum')

}


#' 按年计算累计数——执行预算
#'
#' @param conn  连接
#' @param FYear 年份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_cumSum_Year()
mrpt_calc_cumSum_Year_budget <- function(conn=tsda::conn_rds('jlrds'),
                                         FYear = 2019




){
  mrpt_calc_cumSum_Year(conn = conn,FYear = FYear,table_data = 't_mrpt3_res_budget',
                        table_cumSum = 't_mrpt3_res_budget_cumSum')

}


#' 计算累计数_实际数
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 期间
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_cumSum_Period_actual()
mrpt_calc_cumSum_Period_res <- function(conn=tsda::conn_rds('jlrds'),
                                           FYear = 2019,
                                           FPeriod = 2




){
  #针对历史数据进行处理
  mrpt_calc_cumSum_Period(conn = conn,FYear = FYear,FPeriod = FPeriod,table_data = 't_mrpt3_res',
                          table_cumSum = 't_mrpt3_res_cumSum')

}


#' 按年计算累计数——实际数
#'
#' @param conn  连接
#' @param FYear 年份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_cumSum_Year()
mrpt_calc_cumSum_Year_res <- function(conn=tsda::conn_rds('jlrds'),
                                         FYear = 2019




){
  mrpt_calc_cumSum_Year(conn = conn,FYear = FYear,table_data = 't_mrpt3_res',
                        table_cumSum = 't_mrpt3_res_cumSum')

}


#' 计算中后台费用
#'
#' @param conn 连接
#' @param data_table 表名
#' @param FYear 连接
#' @param FPeriod 期间
#'
#' @return 返回值
#' @export
#'
#' @examples
#' mrpt_calc_mpv()
mrpt_calc_mpv <-function(conn=tsda::conn_rds('jlrds'),
                         data_table ='t_mrpt3_res',
                         FYear = 2021,
                         FPeriod = 7
                         ){
sql <- paste0("select FRptItemNumber,FRptAmt  from  ",data_table,"  a
left join t_mrpt3_md_brandChannel b
on  a.FBrand = b.FBrandName and a.FChannel = b.FChannelName
where FYear = ",FYear," and FPeriod =  ",FPeriod,"
 and  b.FBrandChannelNumber ='00.00'
 and a.FRptItemNumber in('I44')")




data <- tsda::sql_select(conn,sql)
ncount <- nrow(data)
if(ncount >0){
   item_I44 <- data[data$FRptItemNumber == 'I44','FRptAmt']

   sql2 = paste0("  select FRptItemNumber,FRptAmt  from  vw_mrpt_ds_mpv   a
left join t_mrpt3_md_brandChannel b
on  a.FBrand = b.FBrandName and a.FChannel = b.FChannelName
where FYear =  ",FYear,"  and FPeriod =  ",FPeriod,"
 and  b.FBrandChannelNumber ='00.00'
 and a.FRptItemNumber in('I45')")
   data2 =data <- tsda::sql_select(conn,sql2)
   ncount2 <- nrow(data2)
   if(ncount2 >0){
     item_I45 <- data2[data2$FRptItemNumber == 'I45','FRptAmt']
   }else{
     item_I45 = 0
   }

   #计算I46
   item_I46 = item_I44 - item_I45

   #清除数据
   sql_del <- paste0("delete     from  ",data_table,"
where FBrand = (select FBrandName from t_mrpt3_md_brandChannel  where FBrandChannelNumber ='00.00')
and  FChannel = (select FChannelName from t_mrpt3_md_brandChannel  where FBrandChannelNumber ='00.00')
and  FYear = ",FYear," and FPeriod = ",FPeriod,"
 and FRptItemNumber in('I45','I46')")

   tsda::sql_update(conn,sql_del)
   #写入数据
   sql_45 <- paste0("insert into ",data_table," values ( ",FYear,",",FPeriod,",'JALA集团','全渠道','I45','中后台费用',",item_I45,",6)")
   tsda::sql_update(conn,sql_45)
   sql_46 <- paste0("insert into ",data_table," values ( ",FYear,",",FPeriod,",'JALA集团','全渠道','I46','净利润',",item_I46,",6)")
   tsda::sql_update(conn,sql_46)



}


}