data-raw/src/graph_actual_bak.R
In takewiki/jlrdspkg: package for jlrds app
#' 更新数据结果
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_preCheck_invalid()
graph_actual_calc_step2_preCheck_invalid_bak <- 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
) {
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 null")
data = tsda::sql_select(conn,sql)
ncount = nrow(data)
if(ncount >0){
#针对数据进行处理
data$FYear = FYear
data$FPeriod = FPeriod
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 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_preCheck_invalid()
graph_actual_calc_step2_preCheck_valid_bak <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
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 vw_mrpt_md_brandchannel_graph_calc_step2 a
left join (select FBrand,FChannel,FBrand + FChannel as FBrandChannel from vw_mrpt_actual_count
where FYear = ",FYear," and FPeriod = ",FPeriod,"
) b
on a.target = b.FBrand +b.FChannel
where b.FBrandChannel is not null")
data = tsda::sql_select(conn,sql)
ncount = nrow(data)
if(ncount >0){
#针对数据进行处理
data$FYear = FYear
data$FPeriod = FPeriod
data$FBrand =''
data$FChannel =''
data$FPhase ='calc'
data$FStep = 2
data$FStatus = 1
data$FTable = 't_mrpt_actual'
sql_del = paste0("delete from t_mrpt_brandChannel_preCheck_log where FYear = ",FYear," and FPeriod = ",FPeriod,"
and FPhase = 'calc' and FStep =2 and FStatus = 1 and FTable = 't_mrpt_actual'
")
tsda::sql_update(conn,sql_del)
#然后再写入数据
tsda::db_writeTable(conn = conn,table_name = 't_mrpt_brandChannel_preCheck_log',r_object = data,append = T)
}
return(data)
}
#' 处理第2步的数据
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param res_table 结果表
#' @param plan_table 计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_writeRes()
graph_actual_calc_step2_writeRes_bak<- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1,
res_table = 't_mrpt3_res_actual_g',
src_table = 'vw_mrpt_actual',
plan_table = 't_mrpt_brandChannel_preCheck_log'
){
#针对手工没有提供的管报，设置标记为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)
#分为2部分处理
sql_1 = paste0("select * from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='calc' and b.FStatus = 1 and b.FStep =2
and FTable ='t_mrpt_actual'")
data1 = tsda::sql_select(conn,sql_1)
ncount1 = nrow(data1)
if(ncount1 >0){
#存在数据的情况下,已有数据的情况下，直接引入，不再重算
sql_1A <- paste0("
insert into ",res_table,"
select distinct a.Fyear,a.Fperiod,a.Fbrand,a.Fchannel,a.FRptItemNumber,FRptItemName,FRptAmt , 2 as fcalcstep
from ",src_table," a
left join ",plan_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='calc' and b.FStatus =1 and b.FStep =2 and b.FTable ='t_mrpt_actual'
")
cat(sql_1A)
tsda::sql_update(conn,sql_1A)
sql_rate = paste0("select FYear,FPeriod,source_brand as fbrandname,source_channel as fchannelname
from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='calc' and b.FStatus =1 and b.FStep =2 and FTable ='t_mrpt_actual' ")
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)
})
}
}
#若没有相应的数据，的确需要进行重算
sql_0 = paste0("select * from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='calc' and b.FStatus = 0 and b.FStep =2
and FTable ='t_mrpt_actual'")
data0 = tsda::sql_select(conn,sql_0)
ncount0 = nrow(data0)
if(ncount0 >0){
#数据没有的情况，自动生成
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, 2 as FCalcStep
from ",res_table," a
inner join vw_mrpt_md_brandchannel_graph_calc_step2 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 t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='calc' and b.FStatus =0 and b.FStep =2
and FTable ='t_mrpt_actual')
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 t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='calc' and b.FStatus =0 and b.FStep =2 and FTable ='t_mrpt_actual' ")
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)
})
}
}
}
graph_actual_calc_step2_updateStatus<- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1,
log_table = 't_mrpt_brandChannel_preCheck_log'
){
sql <- paste0("update b set b.FStatus =1 from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='calc' and b.FStatus =0 and b.FStep =2
and FTable ='t_mrpt_actual' ")
cat(sql)
tsda::sql_update(conn,sql)}
#' 更新数据结果
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_preCheck_invalid()
graph_actual_rollup1_step1_preCheck_invalid <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
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 vw_mrpt_md_brandchannel_graph_rollup1_step1 a
left join (select FBrand,FChannel,FBrand + FChannel as FBrandChannel from vw_mrpt_actual_count
where FYear = ",FYear," and FPeriod = ",FPeriod,"
) b
on a.target = b.FBrand +b.FChannel
where b.FBrandChannel is null")
data = tsda::sql_select(conn,sql)
ncount = nrow(data)
if(ncount >0){
#针对数据进行处理
data$FYear = FYear
data$FPeriod = FPeriod
data$FBrand =''
data$FChannel =''
data$FPhase ='rollup1'
data$FStep = 1
data$FStatus = 0
data$FTable = 't_mrpt_actual'
sql_del = paste0("delete from t_mrpt_brandChannel_preCheck_log where FYear = ",FYear," and FPeriod = ",FPeriod,"
and FPhase = 'rollup1' and FStep =1 and FStatus = 0 and FTable = 't_mrpt_actual'
")
tsda::sql_update(conn,sql_del)
#然后再写入数据
tsda::db_writeTable(conn = conn,table_name = 't_mrpt_brandChannel_preCheck_log',r_object = data,append = T)
}
return(data)
}
#' 更新数据结果
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_preCheck_invalid()
graph_actual_rollup1_step1_preCheck_valid <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
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 vw_mrpt_md_brandchannel_graph_rollup1_step1 a
left join (select FBrand,FChannel,FBrand + FChannel as FBrandChannel from vw_mrpt_actual_count
where FYear = ",FYear," and FPeriod = ",FPeriod,"
) b
on a.target = b.FBrand +b.FChannel
where b.FBrandChannel is not null")
data = tsda::sql_select(conn,sql)
ncount = nrow(data)
if(ncount >0){
#针对数据进行处理
data$FYear = FYear
data$FPeriod = FPeriod
data$FBrand =''
data$FChannel =''
data$FPhase ='rollup1'
data$FStep = 1
data$FStatus = 1
data$FTable = 't_mrpt_actual'
sql_del = paste0("delete from t_mrpt_brandChannel_preCheck_log where FYear = ",FYear," and FPeriod = ",FPeriod,"
and FPhase = 'rollup1' and FStep =1 and FStatus = 1 and FTable = 't_mrpt_actual'
")
tsda::sql_update(conn,sql_del)
#然后再写入数据
tsda::db_writeTable(conn = conn,table_name = 't_mrpt_brandChannel_preCheck_log',r_object = data,append = T)
}
return(data)
}
#' 步骤2可以进行预检验
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step1_preCheck()
graph_actual_rollup1_step1_preCheck <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
graph_actual_rollup1_step1_preCheck_valid(conn = conn,FYear = FYear,FPeriod = FPeriod)
graph_actual_rollup1_step1_preCheck_invalid(conn = conn,FYear = FYear,FPeriod = FPeriod)
}
#' 处理第2步的数据
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param res_table 结果表
#' @param plan_table 计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_writeRes()
graph_actual_rollup1_step1_writeRes<- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1,
res_table = 't_mrpt3_res_actual_g',
src_table = 'vw_mrpt_actual',
plan_table = 't_mrpt_brandChannel_preCheck_log'
){
#针对手工没有提供的管报，设置标记为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)
#分为2部分处理
sql_1 = paste0("select * from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus = 1 and b.FStep =1
and FTable ='t_mrpt_actual'")
data1 = tsda::sql_select(conn,sql_1)
ncount1 = nrow(data1)
if(ncount1 >0){
#存在数据的情况下,已有数据的情况下，直接引入，不再重算
sql_1A <- paste0("
insert into ",res_table,"
select distinct a.Fyear,a.Fperiod,a.Fbrand,a.Fchannel,a.FRptItemNumber,FRptItemName,FRptAmt , 3 as fcalcstep
from ",src_table," a
left join ",plan_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='rollup1' and b.FStatus =1 and b.FStep =1 and b.FTable ='t_mrpt_actual'
")
cat(sql_1A)
tsda::sql_update(conn,sql_1A)
sql_rate = paste0("select FYear,FPeriod,source_brand as fbrandname,source_channel as fchannelname
from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =1 and b.FStep =1 and FTable ='t_mrpt_actual' ")
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)
})
}
}
#若没有相应的数据，的确需要进行重算
sql_0 = paste0("select * from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus = 0 and b.FStep =1
and FTable ='t_mrpt_actual'")
data0 = tsda::sql_select(conn,sql_0)
ncount0 = nrow(data0)
if(ncount0 >0){
#数据没有的情况，自动生成
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, 3 as FCalcStep
from ",res_table," a
inner join vw_mrpt_md_brandchannel_graph_rollup1_step1 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 t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =0 and b.FStep =1
and FTable ='t_mrpt_actual')
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 t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =0 and b.FStep =1 and FTable ='t_mrpt_actual' ")
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
#' graph_actual_calc_step2_updateStatus()
graph_actual_rollup1_step1_updateStatus<- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1,
plan_table = 't_mrpt_brandChannel_preCheck_log'
){
sql <- paste0("update b set b.FStatus =1 from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =0 and b.FStep =1
and FTable ='t_mrpt_actual' ")
cat(sql)
tsda::sql_update(conn,sql)
}
#' 更新数据结果
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_preCheck_invalid()
graph_actual_rollup1_step2_preCheck_invalid <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
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 vw_mrpt_md_brandchannel_graph_rollup1_step2 a
left join (select FBrand,FChannel,FBrand + FChannel as FBrandChannel from vw_mrpt_actual_count
where FYear = ",FYear," and FPeriod = ",FPeriod,"
) b
on a.target = b.FBrand +b.FChannel
where b.FBrandChannel is null")
data = tsda::sql_select(conn,sql)
ncount = nrow(data)
if(ncount >0){
#针对数据进行处理
data$FYear = FYear
data$FPeriod = FPeriod
data$FBrand =''
data$FChannel =''
data$FPhase ='rollup1'
data$FStep = 2
data$FStatus = 0
data$FTable = 't_mrpt_actual'
sql_del = paste0("delete from t_mrpt_brandChannel_preCheck_log where FYear = ",FYear," and FPeriod = ",FPeriod,"
and FPhase = 'rollup1' and FStep =2 and FStatus = 0 and FTable = 't_mrpt_actual'
")
tsda::sql_update(conn,sql_del)
#然后再写入数据
tsda::db_writeTable(conn = conn,table_name = 't_mrpt_brandChannel_preCheck_log',r_object = data,append = T)
}
return(data)
}
#' 更新数据结果
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_preCheck_invalid()
graph_actual_rollup1_step2_preCheck_valid <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
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 vw_mrpt_md_brandchannel_graph_rollup1_step2 a
left join (select FBrand,FChannel,FBrand + FChannel as FBrandChannel from vw_mrpt_actual_count
where FYear = ",FYear," and FPeriod = ",FPeriod,"
) b
on a.target = b.FBrand +b.FChannel
where b.FBrandChannel is not null")
data = tsda::sql_select(conn,sql)
ncount = nrow(data)
if(ncount >0){
#针对数据进行处理
data$FYear = FYear
data$FPeriod = FPeriod
data$FBrand =''
data$FChannel =''
data$FPhase ='rollup1'
data$FStep = 2
data$FStatus = 1
data$FTable = 't_mrpt_actual'
sql_del = paste0("delete from t_mrpt_brandChannel_preCheck_log where FYear = ",FYear," and FPeriod = ",FPeriod,"
and FPhase = 'rollup1' and FStep =2 and FStatus = 1 and FTable = 't_mrpt_actual'
")
tsda::sql_update(conn,sql_del)
#然后再写入数据
tsda::db_writeTable(conn = conn,table_name = 't_mrpt_brandChannel_preCheck_log',r_object = data,append = T)
}
return(data)
}
#' 步骤2可以进行预检验
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step1_preCheck()
graph_actual_rollup1_step2_preCheck_bak <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
graph_actual_rollup1_step2_preCheck_valid(conn = conn,FYear = FYear,FPeriod = FPeriod)
graph_actual_rollup1_step2_preCheck_invalid(conn = conn,FYear = FYear,FPeriod = FPeriod)
}
#' 处理第2步的数据
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param res_table 结果表
#' @param plan_table 计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_writeRes()
graph_actual_rollup1_step2_writeRes_bak<- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1,
res_table = 't_mrpt3_res_actual_g',
src_table = 'vw_mrpt_actual',
plan_table = 't_mrpt_brandChannel_preCheck_log'
){
#针对手工没有提供的管报，设置标记为2
#删除之前的数据
sql_del <- paste0("delete from ",res_table,"
where fyear = ",FYear," and fperiod = ",FPeriod," and fcalcstep = 4")
cat(sql_del)
tsda::sql_update(conn,sql_del)
#分为2部分处理
sql_1 = paste0("select * from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus = 1 and b.FStep =2
and FTable ='t_mrpt_actual'")
data1 = tsda::sql_select(conn,sql_1)
ncount1 = nrow(data1)
if(ncount1 >0){
#存在数据的情况下,已有数据的情况下，直接引入，不再重算
sql_1A <- paste0("
insert into ",res_table,"
select distinct a.Fyear,a.Fperiod,a.Fbrand,a.Fchannel,a.FRptItemNumber,FRptItemName,FRptAmt , 4 as fcalcstep
from ",src_table," a
left join ",plan_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='rollup1' and b.FStatus =1 and b.FStep =2 and b.FTable ='t_mrpt_actual'
")
cat(sql_1A)
tsda::sql_update(conn,sql_1A)
sql_rate = paste0("select FYear,FPeriod,source_brand as fbrandname,source_channel as fchannelname
from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =1 and b.FStep =2 and FTable ='t_mrpt_actual' ")
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)
})
}
}
#若没有相应的数据，的确需要进行重算
sql_0 = paste0("select * from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus = 0 and b.FStep =2
and FTable ='t_mrpt_actual'")
data0 = tsda::sql_select(conn,sql_0)
ncount0 = nrow(data0)
if(ncount0 >0){
#数据没有的情况，自动生成
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, 4 as FCalcStep
from ",res_table," a
inner join vw_mrpt_md_brandchannel_graph_rollup1_step2 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 t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =0 and b.FStep =2
and FTable ='t_mrpt_actual')
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 t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =0 and b.FStep =2 and FTable ='t_mrpt_actual' ")
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
#' graph_actual_calc_step2_updateStatus()
graph_actual_rollup1_step2_updateStatus_bak<- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1,
plan_table = 't_mrpt_brandChannel_preCheck_log'
){
sql <- paste0("update b set b.FStatus =1 from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =0 and b.FStep =2
and FTable ='t_mrpt_actual' ")
cat(sql)
tsda::sql_update(conn,sql)
}
#' 更新数据结果
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_preCheck_invalid()
graph_actual_rollup1_step3_preCheck_invalid_bak <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
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 vw_mrpt_md_brandchannel_graph_rollup1_step3 a
left join (select FBrand,FChannel,FBrand + FChannel as FBrandChannel from vw_mrpt_actual_count
where FYear = ",FYear," and FPeriod = ",FPeriod,"
) b
on a.target = b.FBrand +b.FChannel
where b.FBrandChannel is null")
data = tsda::sql_select(conn,sql)
ncount = nrow(data)
if(ncount >0){
#针对数据进行处理
data$FYear = FYear
data$FPeriod = FPeriod
data$FBrand =''
data$FChannel =''
data$FPhase ='rollup1'
data$FStep = 3
data$FStatus = 0
data$FTable = 't_mrpt_actual'
sql_del = paste0("delete from t_mrpt_brandChannel_preCheck_log where FYear = ",FYear," and FPeriod = ",FPeriod,"
and FPhase = 'rollup1' and FStep =3 and FStatus = 0 and FTable = 't_mrpt_actual'
")
tsda::sql_update(conn,sql_del)
#然后再写入数据
tsda::db_writeTable(conn = conn,table_name = 't_mrpt_brandChannel_preCheck_log',r_object = data,append = T)
}
return(data)
}
#' 更新数据结果
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_preCheck_invalid()
graph_actual_rollup1_step3_preCheck_valid_bak <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
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 vw_mrpt_md_brandchannel_graph_rollup1_step3 a
left join (select FBrand,FChannel,FBrand + FChannel as FBrandChannel from vw_mrpt_actual_count
where FYear = ",FYear," and FPeriod = ",FPeriod,"
) b
on a.target = b.FBrand +b.FChannel
where b.FBrandChannel is not null")
data = tsda::sql_select(conn,sql)
ncount = nrow(data)
if(ncount >0){
#针对数据进行处理
data$FYear = FYear
data$FPeriod = FPeriod
data$FBrand =''
data$FChannel =''
data$FPhase ='rollup1'
data$FStep = 3
data$FStatus = 1
data$FTable = 't_mrpt_actual'
sql_del = paste0("delete from t_mrpt_brandChannel_preCheck_log where FYear = ",FYear," and FPeriod = ",FPeriod,"
and FPhase = 'rollup1' and FStep =3 and FStatus = 1 and FTable = 't_mrpt_actual'
")
tsda::sql_update(conn,sql_del)
#然后再写入数据
tsda::db_writeTable(conn = conn,table_name = 't_mrpt_brandChannel_preCheck_log',r_object = data,append = T)
}
return(data)
}
#' 步骤2可以进行预检验
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step1_preCheck()
graph_actual_rollup1_step3_preCheck_bak <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
graph_actual_rollup1_step3_preCheck_valid(conn = conn,FYear = FYear,FPeriod = FPeriod)
graph_actual_rollup1_step3_preCheck_invalid(conn = conn,FYear = FYear,FPeriod = FPeriod)
}
#' 处理第3步的数据
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param res_table 结果表
#' @param plan_table 计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_writeRes()
graph_actual_rollup1_step3_writeRes_bak<- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1,
res_table = 't_mrpt3_res_actual_g',
src_table = 'vw_mrpt_actual',
plan_table = 't_mrpt_brandChannel_preCheck_log'
){
#针对手工没有提供的管报，设置标记为2
#删除之前的数据
sql_del <- paste0("delete from ",res_table,"
where fyear = ",FYear," and fperiod = ",FPeriod," and fcalcstep = 5")
cat(sql_del)
tsda::sql_update(conn,sql_del)
#分为2部分处理
sql_1 = paste0("select * from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus = 1 and b.FStep =3
and FTable ='t_mrpt_actual'")
data1 = tsda::sql_select(conn,sql_1)
ncount1 = nrow(data1)
if(ncount1 >0){
#存在数据的情况下,已有数据的情况下，直接引入，不再重算
sql_1A <- paste0("
insert into ",res_table,"
select distinct a.Fyear,a.Fperiod,a.Fbrand,a.Fchannel,a.FRptItemNumber,FRptItemName,FRptAmt , 5 as fcalcstep
from ",src_table," a
left join ",plan_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='rollup1' and b.FStatus =1 and b.FStep =3 and b.FTable ='t_mrpt_actual'
")
cat(sql_1A)
tsda::sql_update(conn,sql_1A)
sql_rate = paste0("select FYear,FPeriod,source_brand as fbrandname,source_channel as fchannelname
from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =1 and b.FStep =3 and FTable ='t_mrpt_actual' ")
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)
})
}
}
#若没有相应的数据，的确需要进行重算
sql_0 = paste0("select * from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus = 0 and b.FStep =3
and FTable ='t_mrpt_actual'")
data0 = tsda::sql_select(conn,sql_0)
ncount0 = nrow(data0)
if(ncount0 >0){
#数据没有的情况，自动生成
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, 5 as FCalcStep
from ",res_table," a
inner join vw_mrpt_md_brandchannel_graph_rollup1_step3 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 t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =0 and b.FStep =3
and FTable ='t_mrpt_actual')
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 t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =0 and b.FStep =3 and FTable ='t_mrpt_actual' ")
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
#' graph_actual_calc_step2_updateStatus()
graph_actual_rollup1_step3_updateStatus_bak<- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1,
plan_table = 't_mrpt_brandChannel_preCheck_log'
){
sql <- paste0("update b set b.FStatus =1 from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =0 and b.FStep =3
and FTable ='t_mrpt_actual' ")
cat(sql)
tsda::sql_update(conn,sql)
}
#' 更新数据结果
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_preCheck_invalid()
graph_actual_rollup2_step1_preCheck_invalid_bak <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
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 vw_mrpt_md_brandchannel_graph_rollup2_step1 a
left join (select FBrand,FChannel,FBrand + FChannel as FBrandChannel from vw_mrpt_actual_count
where FYear = ",FYear," and FPeriod = ",FPeriod,"
) b
on a.target = b.FBrand +b.FChannel
where b.FBrandChannel is null")
data = tsda::sql_select(conn,sql)
ncount = nrow(data)
if(ncount >0){
#针对数据进行处理
data$FYear = FYear
data$FPeriod = FPeriod
data$FBrand =''
data$FChannel =''
data$FPhase ='rollup2'
data$FStep = 1
data$FStatus = 0
data$FTable = 't_mrpt_actual'
sql_del = paste0("delete from t_mrpt_brandChannel_preCheck_log where FYear = ",FYear," and FPeriod = ",FPeriod,"
and FPhase = 'rollup2' and FStep =1 and FStatus = 0 and FTable = 't_mrpt_actual'
")
tsda::sql_update(conn,sql_del)
#然后再写入数据
tsda::db_writeTable(conn = conn,table_name = 't_mrpt_brandChannel_preCheck_log',r_object = data,append = T)
}
return(data)
}
#' 更新数据结果
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_preCheck_invalid()
graph_actual_rollup2_step1_preCheck_valid_bak <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
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 vw_mrpt_md_brandchannel_graph_rollup2_step1 a
left join (select FBrand,FChannel,FBrand + FChannel as FBrandChannel from vw_mrpt_actual_count
where FYear = ",FYear," and FPeriod = ",FPeriod,"
) b
on a.target = b.FBrand +b.FChannel
where b.FBrandChannel is not null")
data = tsda::sql_select(conn,sql)
ncount = nrow(data)
if(ncount >0){
#针对数据进行处理
data$FYear = FYear
data$FPeriod = FPeriod
data$FBrand =''
data$FChannel =''
data$FPhase ='rollup2'
data$FStep = 1
data$FStatus = 1
data$FTable = 't_mrpt_actual'
sql_del = paste0("delete from t_mrpt_brandChannel_preCheck_log where FYear = ",FYear," and FPeriod = ",FPeriod,"
and FPhase = 'rollup2' and FStep =1 and FStatus = 1 and FTable = 't_mrpt_actual'
")
tsda::sql_update(conn,sql_del)
#然后再写入数据
tsda::db_writeTable(conn = conn,table_name = 't_mrpt_brandChannel_preCheck_log',r_object = data,append = T)
}
return(data)
}
#' 步骤2可以进行预检验
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step1_preCheck()
graph_actual_rollup2_step1_preCheck_bak <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
graph_actual_rollup2_step1_preCheck_valid(conn = conn,FYear = FYear,FPeriod = FPeriod)
graph_actual_rollup2_step1_preCheck_invalid(conn = conn,FYear = FYear,FPeriod = FPeriod)
}
#' 处理第2步的数据
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param res_table 结果表
#' @param plan_table 计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_writeRes()
graph_actual_rollup2_step1_writeRes_bak<- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1,
res_table = 't_mrpt3_res_actual_g',
src_table = 'vw_mrpt_actual',
plan_table = 't_mrpt_brandChannel_preCheck_log'
){
#针对手工没有提供的管报，设置标记为2
#删除之前的数据
sql_del <- paste0("delete from ",res_table,"
where fyear = ",FYear," and fperiod = ",FPeriod," and fcalcstep = 6")
cat(sql_del)
tsda::sql_update(conn,sql_del)
#分为2部分处理
sql_1 = paste0("select * from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup2' and b.FStatus = 1 and b.FStep =1
and FTable ='t_mrpt_actual'")
data1 = tsda::sql_select(conn,sql_1)
ncount1 = nrow(data1)
if(ncount1 >0){
#存在数据的情况下,已有数据的情况下，直接引入，不再重算
sql_1A <- paste0("
insert into ",res_table,"
select distinct a.Fyear,a.Fperiod,a.Fbrand,a.Fchannel,a.FRptItemNumber,FRptItemName,FRptAmt , 6 as fcalcstep
from ",src_table," a
left join ",plan_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='rollup2' and b.FStatus =1 and b.FStep =1 and b.FTable ='t_mrpt_actual'
")
cat(sql_1A)
tsda::sql_update(conn,sql_1A)
sql_rate = paste0("select FYear,FPeriod,source_brand as fbrandname,source_channel as fchannelname
from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup2' and b.FStatus =1 and b.FStep =1 and FTable ='t_mrpt_actual' ")
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)
})
}
}
#若没有相应的数据，的确需要进行重算
sql_0 = paste0("select * from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup2' and b.FStatus = 0 and b.FStep =1
and FTable ='t_mrpt_actual'")
data0 = tsda::sql_select(conn,sql_0)
ncount0 = nrow(data0)
if(ncount0 >0){
#数据没有的情况，自动生成
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, 6 as FCalcStep
from ",res_table," a
inner join vw_mrpt_md_brandchannel_graph_rollup2_step1 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 t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup2' and b.FStatus =0 and b.FStep =1
and FTable ='t_mrpt_actual')
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 t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup2' and b.FStatus =0 and b.FStep =1 and FTable ='t_mrpt_actual' ")
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
#' graph_actual_calc_step2_updateStatus()
graph_actual_rollup2_step1_updateStatus_bak<- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1,
plan_table = 't_mrpt_brandChannel_preCheck_log'
){
sql <- paste0("update b set b.FStatus =1 from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup2' and b.FStatus =0 and b.FStep =1
and FTable ='t_mrpt_actual' ")
cat(sql)
tsda::sql_update(conn,sql)
}
takewiki/jlrdspkg documentation built on March 29, 2022, 3:17 a.m.
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
#' 更新数据结果
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_preCheck_invalid()
graph_actual_calc_step2_preCheck_invalid_bak <- 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
) {
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 null")
data = tsda::sql_select(conn,sql)
ncount = nrow(data)
if(ncount >0){
#针对数据进行处理
data$FYear = FYear
data$FPeriod = FPeriod
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 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_preCheck_invalid()
graph_actual_calc_step2_preCheck_valid_bak <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
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 vw_mrpt_md_brandchannel_graph_calc_step2 a
left join (select FBrand,FChannel,FBrand + FChannel as FBrandChannel from vw_mrpt_actual_count
where FYear = ",FYear," and FPeriod = ",FPeriod,"
) b
on a.target = b.FBrand +b.FChannel
where b.FBrandChannel is not null")
data = tsda::sql_select(conn,sql)
ncount = nrow(data)
if(ncount >0){
#针对数据进行处理
data$FYear = FYear
data$FPeriod = FPeriod
data$FBrand =''
data$FChannel =''
data$FPhase ='calc'
data$FStep = 2
data$FStatus = 1
data$FTable = 't_mrpt_actual'
sql_del = paste0("delete from t_mrpt_brandChannel_preCheck_log where FYear = ",FYear," and FPeriod = ",FPeriod,"
and FPhase = 'calc' and FStep =2 and FStatus = 1 and FTable = 't_mrpt_actual'
")
tsda::sql_update(conn,sql_del)
#然后再写入数据
tsda::db_writeTable(conn = conn,table_name = 't_mrpt_brandChannel_preCheck_log',r_object = data,append = T)
}
return(data)
}
#' 处理第2步的数据
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param res_table 结果表
#' @param plan_table 计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_writeRes()
graph_actual_calc_step2_writeRes_bak<- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1,
res_table = 't_mrpt3_res_actual_g',
src_table = 'vw_mrpt_actual',
plan_table = 't_mrpt_brandChannel_preCheck_log'
){
#针对手工没有提供的管报，设置标记为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)
#分为2部分处理
sql_1 = paste0("select * from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='calc' and b.FStatus = 1 and b.FStep =2
and FTable ='t_mrpt_actual'")
data1 = tsda::sql_select(conn,sql_1)
ncount1 = nrow(data1)
if(ncount1 >0){
#存在数据的情况下,已有数据的情况下，直接引入，不再重算
sql_1A <- paste0("
insert into ",res_table,"
select distinct a.Fyear,a.Fperiod,a.Fbrand,a.Fchannel,a.FRptItemNumber,FRptItemName,FRptAmt , 2 as fcalcstep
from ",src_table," a
left join ",plan_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='calc' and b.FStatus =1 and b.FStep =2 and b.FTable ='t_mrpt_actual'
")
cat(sql_1A)
tsda::sql_update(conn,sql_1A)
sql_rate = paste0("select FYear,FPeriod,source_brand as fbrandname,source_channel as fchannelname
from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='calc' and b.FStatus =1 and b.FStep =2 and FTable ='t_mrpt_actual' ")
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)
})
}
}
#若没有相应的数据，的确需要进行重算
sql_0 = paste0("select * from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='calc' and b.FStatus = 0 and b.FStep =2
and FTable ='t_mrpt_actual'")
data0 = tsda::sql_select(conn,sql_0)
ncount0 = nrow(data0)
if(ncount0 >0){
#数据没有的情况，自动生成
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, 2 as FCalcStep
from ",res_table," a
inner join vw_mrpt_md_brandchannel_graph_calc_step2 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 t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='calc' and b.FStatus =0 and b.FStep =2
and FTable ='t_mrpt_actual')
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 t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='calc' and b.FStatus =0 and b.FStep =2 and FTable ='t_mrpt_actual' ")
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)
})
}
}
}
graph_actual_calc_step2_updateStatus<- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1,
log_table = 't_mrpt_brandChannel_preCheck_log'
){
sql <- paste0("update b set b.FStatus =1 from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='calc' and b.FStatus =0 and b.FStep =2
and FTable ='t_mrpt_actual' ")
cat(sql)
tsda::sql_update(conn,sql)}
#' 更新数据结果
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_preCheck_invalid()
graph_actual_rollup1_step1_preCheck_invalid <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
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 vw_mrpt_md_brandchannel_graph_rollup1_step1 a
left join (select FBrand,FChannel,FBrand + FChannel as FBrandChannel from vw_mrpt_actual_count
where FYear = ",FYear," and FPeriod = ",FPeriod,"
) b
on a.target = b.FBrand +b.FChannel
where b.FBrandChannel is null")
data = tsda::sql_select(conn,sql)
ncount = nrow(data)
if(ncount >0){
#针对数据进行处理
data$FYear = FYear
data$FPeriod = FPeriod
data$FBrand =''
data$FChannel =''
data$FPhase ='rollup1'
data$FStep = 1
data$FStatus = 0
data$FTable = 't_mrpt_actual'
sql_del = paste0("delete from t_mrpt_brandChannel_preCheck_log where FYear = ",FYear," and FPeriod = ",FPeriod,"
and FPhase = 'rollup1' and FStep =1 and FStatus = 0 and FTable = 't_mrpt_actual'
")
tsda::sql_update(conn,sql_del)
#然后再写入数据
tsda::db_writeTable(conn = conn,table_name = 't_mrpt_brandChannel_preCheck_log',r_object = data,append = T)
}
return(data)
}
#' 更新数据结果
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_preCheck_invalid()
graph_actual_rollup1_step1_preCheck_valid <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
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 vw_mrpt_md_brandchannel_graph_rollup1_step1 a
left join (select FBrand,FChannel,FBrand + FChannel as FBrandChannel from vw_mrpt_actual_count
where FYear = ",FYear," and FPeriod = ",FPeriod,"
) b
on a.target = b.FBrand +b.FChannel
where b.FBrandChannel is not null")
data = tsda::sql_select(conn,sql)
ncount = nrow(data)
if(ncount >0){
#针对数据进行处理
data$FYear = FYear
data$FPeriod = FPeriod
data$FBrand =''
data$FChannel =''
data$FPhase ='rollup1'
data$FStep = 1
data$FStatus = 1
data$FTable = 't_mrpt_actual'
sql_del = paste0("delete from t_mrpt_brandChannel_preCheck_log where FYear = ",FYear," and FPeriod = ",FPeriod,"
and FPhase = 'rollup1' and FStep =1 and FStatus = 1 and FTable = 't_mrpt_actual'
")
tsda::sql_update(conn,sql_del)
#然后再写入数据
tsda::db_writeTable(conn = conn,table_name = 't_mrpt_brandChannel_preCheck_log',r_object = data,append = T)
}
return(data)
}
#' 步骤2可以进行预检验
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step1_preCheck()
graph_actual_rollup1_step1_preCheck <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
graph_actual_rollup1_step1_preCheck_valid(conn = conn,FYear = FYear,FPeriod = FPeriod)
graph_actual_rollup1_step1_preCheck_invalid(conn = conn,FYear = FYear,FPeriod = FPeriod)
}
#' 处理第2步的数据
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param res_table 结果表
#' @param plan_table 计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_writeRes()
graph_actual_rollup1_step1_writeRes<- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1,
res_table = 't_mrpt3_res_actual_g',
src_table = 'vw_mrpt_actual',
plan_table = 't_mrpt_brandChannel_preCheck_log'
){
#针对手工没有提供的管报，设置标记为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)
#分为2部分处理
sql_1 = paste0("select * from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus = 1 and b.FStep =1
and FTable ='t_mrpt_actual'")
data1 = tsda::sql_select(conn,sql_1)
ncount1 = nrow(data1)
if(ncount1 >0){
#存在数据的情况下,已有数据的情况下，直接引入，不再重算
sql_1A <- paste0("
insert into ",res_table,"
select distinct a.Fyear,a.Fperiod,a.Fbrand,a.Fchannel,a.FRptItemNumber,FRptItemName,FRptAmt , 3 as fcalcstep
from ",src_table," a
left join ",plan_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='rollup1' and b.FStatus =1 and b.FStep =1 and b.FTable ='t_mrpt_actual'
")
cat(sql_1A)
tsda::sql_update(conn,sql_1A)
sql_rate = paste0("select FYear,FPeriod,source_brand as fbrandname,source_channel as fchannelname
from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =1 and b.FStep =1 and FTable ='t_mrpt_actual' ")
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)
})
}
}
#若没有相应的数据，的确需要进行重算
sql_0 = paste0("select * from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus = 0 and b.FStep =1
and FTable ='t_mrpt_actual'")
data0 = tsda::sql_select(conn,sql_0)
ncount0 = nrow(data0)
if(ncount0 >0){
#数据没有的情况，自动生成
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, 3 as FCalcStep
from ",res_table," a
inner join vw_mrpt_md_brandchannel_graph_rollup1_step1 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 t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =0 and b.FStep =1
and FTable ='t_mrpt_actual')
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 t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =0 and b.FStep =1 and FTable ='t_mrpt_actual' ")
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
#' graph_actual_calc_step2_updateStatus()
graph_actual_rollup1_step1_updateStatus<- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1,
plan_table = 't_mrpt_brandChannel_preCheck_log'
){
sql <- paste0("update b set b.FStatus =1 from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =0 and b.FStep =1
and FTable ='t_mrpt_actual' ")
cat(sql)
tsda::sql_update(conn,sql)
}
#' 更新数据结果
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_preCheck_invalid()
graph_actual_rollup1_step2_preCheck_invalid <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
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 vw_mrpt_md_brandchannel_graph_rollup1_step2 a
left join (select FBrand,FChannel,FBrand + FChannel as FBrandChannel from vw_mrpt_actual_count
where FYear = ",FYear," and FPeriod = ",FPeriod,"
) b
on a.target = b.FBrand +b.FChannel
where b.FBrandChannel is null")
data = tsda::sql_select(conn,sql)
ncount = nrow(data)
if(ncount >0){
#针对数据进行处理
data$FYear = FYear
data$FPeriod = FPeriod
data$FBrand =''
data$FChannel =''
data$FPhase ='rollup1'
data$FStep = 2
data$FStatus = 0
data$FTable = 't_mrpt_actual'
sql_del = paste0("delete from t_mrpt_brandChannel_preCheck_log where FYear = ",FYear," and FPeriod = ",FPeriod,"
and FPhase = 'rollup1' and FStep =2 and FStatus = 0 and FTable = 't_mrpt_actual'
")
tsda::sql_update(conn,sql_del)
#然后再写入数据
tsda::db_writeTable(conn = conn,table_name = 't_mrpt_brandChannel_preCheck_log',r_object = data,append = T)
}
return(data)
}
#' 更新数据结果
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_preCheck_invalid()
graph_actual_rollup1_step2_preCheck_valid <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
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 vw_mrpt_md_brandchannel_graph_rollup1_step2 a
left join (select FBrand,FChannel,FBrand + FChannel as FBrandChannel from vw_mrpt_actual_count
where FYear = ",FYear," and FPeriod = ",FPeriod,"
) b
on a.target = b.FBrand +b.FChannel
where b.FBrandChannel is not null")
data = tsda::sql_select(conn,sql)
ncount = nrow(data)
if(ncount >0){
#针对数据进行处理
data$FYear = FYear
data$FPeriod = FPeriod
data$FBrand =''
data$FChannel =''
data$FPhase ='rollup1'
data$FStep = 2
data$FStatus = 1
data$FTable = 't_mrpt_actual'
sql_del = paste0("delete from t_mrpt_brandChannel_preCheck_log where FYear = ",FYear," and FPeriod = ",FPeriod,"
and FPhase = 'rollup1' and FStep =2 and FStatus = 1 and FTable = 't_mrpt_actual'
")
tsda::sql_update(conn,sql_del)
#然后再写入数据
tsda::db_writeTable(conn = conn,table_name = 't_mrpt_brandChannel_preCheck_log',r_object = data,append = T)
}
return(data)
}
#' 步骤2可以进行预检验
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step1_preCheck()
graph_actual_rollup1_step2_preCheck_bak <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
graph_actual_rollup1_step2_preCheck_valid(conn = conn,FYear = FYear,FPeriod = FPeriod)
graph_actual_rollup1_step2_preCheck_invalid(conn = conn,FYear = FYear,FPeriod = FPeriod)
}
#' 处理第2步的数据
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param res_table 结果表
#' @param plan_table 计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_writeRes()
graph_actual_rollup1_step2_writeRes_bak<- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1,
res_table = 't_mrpt3_res_actual_g',
src_table = 'vw_mrpt_actual',
plan_table = 't_mrpt_brandChannel_preCheck_log'
){
#针对手工没有提供的管报，设置标记为2
#删除之前的数据
sql_del <- paste0("delete from ",res_table,"
where fyear = ",FYear," and fperiod = ",FPeriod," and fcalcstep = 4")
cat(sql_del)
tsda::sql_update(conn,sql_del)
#分为2部分处理
sql_1 = paste0("select * from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus = 1 and b.FStep =2
and FTable ='t_mrpt_actual'")
data1 = tsda::sql_select(conn,sql_1)
ncount1 = nrow(data1)
if(ncount1 >0){
#存在数据的情况下,已有数据的情况下，直接引入，不再重算
sql_1A <- paste0("
insert into ",res_table,"
select distinct a.Fyear,a.Fperiod,a.Fbrand,a.Fchannel,a.FRptItemNumber,FRptItemName,FRptAmt , 4 as fcalcstep
from ",src_table," a
left join ",plan_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='rollup1' and b.FStatus =1 and b.FStep =2 and b.FTable ='t_mrpt_actual'
")
cat(sql_1A)
tsda::sql_update(conn,sql_1A)
sql_rate = paste0("select FYear,FPeriod,source_brand as fbrandname,source_channel as fchannelname
from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =1 and b.FStep =2 and FTable ='t_mrpt_actual' ")
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)
})
}
}
#若没有相应的数据，的确需要进行重算
sql_0 = paste0("select * from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus = 0 and b.FStep =2
and FTable ='t_mrpt_actual'")
data0 = tsda::sql_select(conn,sql_0)
ncount0 = nrow(data0)
if(ncount0 >0){
#数据没有的情况，自动生成
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, 4 as FCalcStep
from ",res_table," a
inner join vw_mrpt_md_brandchannel_graph_rollup1_step2 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 t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =0 and b.FStep =2
and FTable ='t_mrpt_actual')
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 t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =0 and b.FStep =2 and FTable ='t_mrpt_actual' ")
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
#' graph_actual_calc_step2_updateStatus()
graph_actual_rollup1_step2_updateStatus_bak<- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1,
plan_table = 't_mrpt_brandChannel_preCheck_log'
){
sql <- paste0("update b set b.FStatus =1 from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =0 and b.FStep =2
and FTable ='t_mrpt_actual' ")
cat(sql)
tsda::sql_update(conn,sql)
}
#' 更新数据结果
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_preCheck_invalid()
graph_actual_rollup1_step3_preCheck_invalid_bak <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
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 vw_mrpt_md_brandchannel_graph_rollup1_step3 a
left join (select FBrand,FChannel,FBrand + FChannel as FBrandChannel from vw_mrpt_actual_count
where FYear = ",FYear," and FPeriod = ",FPeriod,"
) b
on a.target = b.FBrand +b.FChannel
where b.FBrandChannel is null")
data = tsda::sql_select(conn,sql)
ncount = nrow(data)
if(ncount >0){
#针对数据进行处理
data$FYear = FYear
data$FPeriod = FPeriod
data$FBrand =''
data$FChannel =''
data$FPhase ='rollup1'
data$FStep = 3
data$FStatus = 0
data$FTable = 't_mrpt_actual'
sql_del = paste0("delete from t_mrpt_brandChannel_preCheck_log where FYear = ",FYear," and FPeriod = ",FPeriod,"
and FPhase = 'rollup1' and FStep =3 and FStatus = 0 and FTable = 't_mrpt_actual'
")
tsda::sql_update(conn,sql_del)
#然后再写入数据
tsda::db_writeTable(conn = conn,table_name = 't_mrpt_brandChannel_preCheck_log',r_object = data,append = T)
}
return(data)
}
#' 更新数据结果
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_preCheck_invalid()
graph_actual_rollup1_step3_preCheck_valid_bak <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
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 vw_mrpt_md_brandchannel_graph_rollup1_step3 a
left join (select FBrand,FChannel,FBrand + FChannel as FBrandChannel from vw_mrpt_actual_count
where FYear = ",FYear," and FPeriod = ",FPeriod,"
) b
on a.target = b.FBrand +b.FChannel
where b.FBrandChannel is not null")
data = tsda::sql_select(conn,sql)
ncount = nrow(data)
if(ncount >0){
#针对数据进行处理
data$FYear = FYear
data$FPeriod = FPeriod
data$FBrand =''
data$FChannel =''
data$FPhase ='rollup1'
data$FStep = 3
data$FStatus = 1
data$FTable = 't_mrpt_actual'
sql_del = paste0("delete from t_mrpt_brandChannel_preCheck_log where FYear = ",FYear," and FPeriod = ",FPeriod,"
and FPhase = 'rollup1' and FStep =3 and FStatus = 1 and FTable = 't_mrpt_actual'
")
tsda::sql_update(conn,sql_del)
#然后再写入数据
tsda::db_writeTable(conn = conn,table_name = 't_mrpt_brandChannel_preCheck_log',r_object = data,append = T)
}
return(data)
}
#' 步骤2可以进行预检验
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step1_preCheck()
graph_actual_rollup1_step3_preCheck_bak <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
graph_actual_rollup1_step3_preCheck_valid(conn = conn,FYear = FYear,FPeriod = FPeriod)
graph_actual_rollup1_step3_preCheck_invalid(conn = conn,FYear = FYear,FPeriod = FPeriod)
}
#' 处理第3步的数据
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param res_table 结果表
#' @param plan_table 计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_writeRes()
graph_actual_rollup1_step3_writeRes_bak<- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1,
res_table = 't_mrpt3_res_actual_g',
src_table = 'vw_mrpt_actual',
plan_table = 't_mrpt_brandChannel_preCheck_log'
){
#针对手工没有提供的管报，设置标记为2
#删除之前的数据
sql_del <- paste0("delete from ",res_table,"
where fyear = ",FYear," and fperiod = ",FPeriod," and fcalcstep = 5")
cat(sql_del)
tsda::sql_update(conn,sql_del)
#分为2部分处理
sql_1 = paste0("select * from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus = 1 and b.FStep =3
and FTable ='t_mrpt_actual'")
data1 = tsda::sql_select(conn,sql_1)
ncount1 = nrow(data1)
if(ncount1 >0){
#存在数据的情况下,已有数据的情况下，直接引入，不再重算
sql_1A <- paste0("
insert into ",res_table,"
select distinct a.Fyear,a.Fperiod,a.Fbrand,a.Fchannel,a.FRptItemNumber,FRptItemName,FRptAmt , 5 as fcalcstep
from ",src_table," a
left join ",plan_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='rollup1' and b.FStatus =1 and b.FStep =3 and b.FTable ='t_mrpt_actual'
")
cat(sql_1A)
tsda::sql_update(conn,sql_1A)
sql_rate = paste0("select FYear,FPeriod,source_brand as fbrandname,source_channel as fchannelname
from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =1 and b.FStep =3 and FTable ='t_mrpt_actual' ")
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)
})
}
}
#若没有相应的数据，的确需要进行重算
sql_0 = paste0("select * from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus = 0 and b.FStep =3
and FTable ='t_mrpt_actual'")
data0 = tsda::sql_select(conn,sql_0)
ncount0 = nrow(data0)
if(ncount0 >0){
#数据没有的情况，自动生成
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, 5 as FCalcStep
from ",res_table," a
inner join vw_mrpt_md_brandchannel_graph_rollup1_step3 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 t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =0 and b.FStep =3
and FTable ='t_mrpt_actual')
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 t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =0 and b.FStep =3 and FTable ='t_mrpt_actual' ")
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
#' graph_actual_calc_step2_updateStatus()
graph_actual_rollup1_step3_updateStatus_bak<- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1,
plan_table = 't_mrpt_brandChannel_preCheck_log'
){
sql <- paste0("update b set b.FStatus =1 from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup1' and b.FStatus =0 and b.FStep =3
and FTable ='t_mrpt_actual' ")
cat(sql)
tsda::sql_update(conn,sql)
}
#' 更新数据结果
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_preCheck_invalid()
graph_actual_rollup2_step1_preCheck_invalid_bak <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
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 vw_mrpt_md_brandchannel_graph_rollup2_step1 a
left join (select FBrand,FChannel,FBrand + FChannel as FBrandChannel from vw_mrpt_actual_count
where FYear = ",FYear," and FPeriod = ",FPeriod,"
) b
on a.target = b.FBrand +b.FChannel
where b.FBrandChannel is null")
data = tsda::sql_select(conn,sql)
ncount = nrow(data)
if(ncount >0){
#针对数据进行处理
data$FYear = FYear
data$FPeriod = FPeriod
data$FBrand =''
data$FChannel =''
data$FPhase ='rollup2'
data$FStep = 1
data$FStatus = 0
data$FTable = 't_mrpt_actual'
sql_del = paste0("delete from t_mrpt_brandChannel_preCheck_log where FYear = ",FYear," and FPeriod = ",FPeriod,"
and FPhase = 'rollup2' and FStep =1 and FStatus = 0 and FTable = 't_mrpt_actual'
")
tsda::sql_update(conn,sql_del)
#然后再写入数据
tsda::db_writeTable(conn = conn,table_name = 't_mrpt_brandChannel_preCheck_log',r_object = data,append = T)
}
return(data)
}
#' 更新数据结果
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_preCheck_invalid()
graph_actual_rollup2_step1_preCheck_valid_bak <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
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 vw_mrpt_md_brandchannel_graph_rollup2_step1 a
left join (select FBrand,FChannel,FBrand + FChannel as FBrandChannel from vw_mrpt_actual_count
where FYear = ",FYear," and FPeriod = ",FPeriod,"
) b
on a.target = b.FBrand +b.FChannel
where b.FBrandChannel is not null")
data = tsda::sql_select(conn,sql)
ncount = nrow(data)
if(ncount >0){
#针对数据进行处理
data$FYear = FYear
data$FPeriod = FPeriod
data$FBrand =''
data$FChannel =''
data$FPhase ='rollup2'
data$FStep = 1
data$FStatus = 1
data$FTable = 't_mrpt_actual'
sql_del = paste0("delete from t_mrpt_brandChannel_preCheck_log where FYear = ",FYear," and FPeriod = ",FPeriod,"
and FPhase = 'rollup2' and FStep =1 and FStatus = 1 and FTable = 't_mrpt_actual'
")
tsda::sql_update(conn,sql_del)
#然后再写入数据
tsda::db_writeTable(conn = conn,table_name = 't_mrpt_brandChannel_preCheck_log',r_object = data,append = T)
}
return(data)
}
#' 步骤2可以进行预检验
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step1_preCheck()
graph_actual_rollup2_step1_preCheck_bak <- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1) {
graph_actual_rollup2_step1_preCheck_valid(conn = conn,FYear = FYear,FPeriod = FPeriod)
graph_actual_rollup2_step1_preCheck_invalid(conn = conn,FYear = FYear,FPeriod = FPeriod)
}
#' 处理第2步的数据
#'
#' @param conn 连接
#' @param FYear 年份
#' @param FPeriod 月份
#' @param res_table 结果表
#' @param plan_table 计划表
#'
#' @return 返回值
#' @export
#'
#' @examples
#' graph_actual_calc_step2_writeRes()
graph_actual_rollup2_step1_writeRes_bak<- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1,
res_table = 't_mrpt3_res_actual_g',
src_table = 'vw_mrpt_actual',
plan_table = 't_mrpt_brandChannel_preCheck_log'
){
#针对手工没有提供的管报，设置标记为2
#删除之前的数据
sql_del <- paste0("delete from ",res_table,"
where fyear = ",FYear," and fperiod = ",FPeriod," and fcalcstep = 6")
cat(sql_del)
tsda::sql_update(conn,sql_del)
#分为2部分处理
sql_1 = paste0("select * from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup2' and b.FStatus = 1 and b.FStep =1
and FTable ='t_mrpt_actual'")
data1 = tsda::sql_select(conn,sql_1)
ncount1 = nrow(data1)
if(ncount1 >0){
#存在数据的情况下,已有数据的情况下，直接引入，不再重算
sql_1A <- paste0("
insert into ",res_table,"
select distinct a.Fyear,a.Fperiod,a.Fbrand,a.Fchannel,a.FRptItemNumber,FRptItemName,FRptAmt , 6 as fcalcstep
from ",src_table," a
left join ",plan_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='rollup2' and b.FStatus =1 and b.FStep =1 and b.FTable ='t_mrpt_actual'
")
cat(sql_1A)
tsda::sql_update(conn,sql_1A)
sql_rate = paste0("select FYear,FPeriod,source_brand as fbrandname,source_channel as fchannelname
from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup2' and b.FStatus =1 and b.FStep =1 and FTable ='t_mrpt_actual' ")
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)
})
}
}
#若没有相应的数据，的确需要进行重算
sql_0 = paste0("select * from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup2' and b.FStatus = 0 and b.FStep =1
and FTable ='t_mrpt_actual'")
data0 = tsda::sql_select(conn,sql_0)
ncount0 = nrow(data0)
if(ncount0 >0){
#数据没有的情况，自动生成
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, 6 as FCalcStep
from ",res_table," a
inner join vw_mrpt_md_brandchannel_graph_rollup2_step1 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 t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup2' and b.FStatus =0 and b.FStep =1
and FTable ='t_mrpt_actual')
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 t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup2' and b.FStatus =0 and b.FStep =1 and FTable ='t_mrpt_actual' ")
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
#' graph_actual_calc_step2_updateStatus()
graph_actual_rollup2_step1_updateStatus_bak<- function(conn=tsda::conn_rds('jlrds'),
FYear =2019,
FPeriod =1,
plan_table = 't_mrpt_brandChannel_preCheck_log'
){
sql <- paste0("update b set b.FStatus =1 from t_mrpt_brandChannel_preCheck_log b
where
b.FYear = ",FYear," and b.FPeriod = ",FPeriod," and b.FPhase='rollup2' and b.FStatus =0 and b.FStep =1
and FTable ='t_mrpt_actual' ")
cat(sql)
tsda::sql_update(conn,sql)
}
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
Embedding an R snippet on your website
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.