历经数月，呕心沥血之作，特此记录
create or replace package PKG_TSK_Formulas_2_DZCOL_23 as
PROCEDURE PROC_replaceSScope(INPUT_TYPE varchar2,INPUT_NAME varchar2,INPUT_COLUMNCODE varchar2);
PROCEDURE update_IF_to_ternary(INPUT_TYPE varchar2);
PROCEDURE PROC_deal_type0;
PROCEDURE split_Max_And_Min;
PROCEDURE split_DTF(INPUT_TYPE varchar2,INPUT_NAME varchar2);
PROCEDURE PROC_type10(INPUT_NAME varchar2);
PROCEDURE PROC_deal_type10;
PROCEDURE PROC_deal_type16;
PROCEDURE PROC_setQIF_type5;
PROCEDURE PROC_deal_type5;
PROCEDURE split_DQJF(INPUT_TYPE varchar2,INPUT_NAME varchar2);
PROCEDURE split_QJF(INPUT_TYPE varchar2,INPUT_NAME varchar2);
PROCEDURE PROC_type1(INPUT_NAME varchar2);
PROCEDURE addTableCode_thisTable(thisTable varchar2);
PROCEDURE replaceExecFormula_thisTable(thisTable varchar2);
PROCEDURE PROC_deal_type1;
PROCEDURE set_ALL_CDS(update_tableName varchar2);
PROCEDURE PROC_deal_type3;
PROCEDURE PROC_deal_type15;
end PKG_TSK_Formulas_2_DZCOL_23;
/
create or replace package body PKG_TSK_Formulas_2_DZCOL_23 as
--------------------------------- [ 大正公式迁移过程 ] -------------------------------
/*
将23年大正公式 转成总决算公式格式,
包括期间取数,录入表简表计算/生成表取数/审核
坐标加表名前缀
*/
procedure PROC_replaceSScope(INPUT_TYPE in varchar2,INPUT_NAME in varchar2,INPUT_COLUMNCODE in varchar2) as
/* 翻译SSCOPE至tsk表冗余字段SSCOPE_CODE
单位级次信息=(地方合计,03)|(省级,04)|(省本级,05)|(地市合计,06)|(地市级,07)|(地市本级,08)|(区县合计,09)|(区县级,10)
为
DW_LEVEL IN ( '03' ,'04' ,'05' ,'06' ,'07' ,'08' ,'09' ,'10' )
*/
PARAM_V_BOOKCODE varchar2(20); -- v_bookcode
PARAM_V_TASKCODE varchar2(20); -- v_TASKcode
begin
select case when v_bookname = 'IB' then 'ZJS001FJNB' else s.v_taskcode end,s.v_bookcode into PARAM_V_TASKCODE,PARAM_V_BOOKCODE from tsk_v_doc_2023 s
where v_bookname = INPUT_NAME;
-- 作用范围
if INPUT_COLUMNCODE = 'SSCOPE_CODE' then
update Tsk_SheetFormulas f set f.SSCOPE_CODE = SSCOPE
where f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE and i_formulatype = INPUT_TYPE
and trim(sscope) is not null and v_periodid = '2023' ;
update Tsk_SheetFormulas f set f.SSCOPE_CODE =
multiple_replace(SSCOPE_CODE,
NEW t_text('单位级次信息=','(全国合计,01)','(中央本级,02)','(地方合计,03)','(省级,04)','(省本级,05)','(地市合计,06)','(地市级,07)','(地市本级,08)','(区县合计,09)','(区县级,10)','(其他,11)','|'),
NEW t_text('DW_LEVEL=~', '[01]','[02]','[03]','[04]','[05]','[06]','[07]','[08]','[09]','[10]','[11]',','))
where f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE and i_formulatype = INPUT_TYPE and v_periodid = '2023' ;
update Tsk_SheetFormulas f set f.SSCOPE_CODE =
multiple_replace(SSCOPE_CODE,
NEW t_text('所在地区类型信息=','(省,1)','(自治区,2)','(直辖市,3)','(否,4)', ';'),
NEW t_text('DIV_CLASS=~','[1]', '[2]','[3]','[4]', ' '))
where f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE and i_formulatype = INPUT_TYPE and v_periodid = '2023' ;
-- $计划单列市属性信息=(无,2)
update Tsk_SheetFormulas f set f.SSCOPE_CODE =
multiple_replace(SSCOPE_CODE,
NEW t_text('计划单列市属性信息=','省直管县信息=', '(是,1)','(否,2)', '(有,1)','(无,2)'),
NEW t_text('IS_DIRECT_PLAN==', 'IS_DIRECT_COUNTY==','''1''','''2''', '''1''','''2'''))
where f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE and i_formulatype = INPUT_TYPE and v_periodid = '2023' ;
-- CONTAINSFUN 这个用单等于号
update Tsk_SheetFormulas f set f.SSCOPE_CODE =
multiple_replace(SSCOPE_CODE,
NEW t_text('^IS_DIRECT_PLAN==''1''', '^IS_DIRECT_PLAN==''2''', '^IS_DIRECT_COUNTY==''1''', '^IS_DIRECT_COUNTY==''2'''),
NEW t_text('CONTAINSFUN(IS_DIRECT_PLAN=''1'',^)','CONTAINSFUN(IS_DIRECT_PLAN=''2'',^)','CONTAINSFUN(IS_DIRECT_COUNTY=''1'',^)','CONTAINSFUN(IS_DIRECT_COUNTY=''2'',^)'))
where f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE and i_formulatype = INPUT_TYPE and v_periodid = '2023' ;
update Tsk_SheetFormulas f set f.SSCOPE_CODE =
multiple_replace(SSCOPE_CODE,
NEW t_text('$IS_DIRECT_PLAN==''1''', '$IS_DIRECT_PLAN==''2''', '$IS_DIRECT_COUNTY==''1''', '$IS_DIRECT_COUNTY==''2'''),
NEW t_text('CONTAINSFUN(IS_DIRECT_PLAN=''1'',$)','CONTAINSFUN(IS_DIRECT_PLAN=''2'',$)','CONTAINSFUN(IS_DIRECT_COUNTY=''1'',$)','CONTAINSFUN(IS_DIRECT_COUNTY=''2'',$)'))
where f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE and i_formulatype = INPUT_TYPE and v_periodid = '2023' ;
-- FlexRow 这个用单等于号
update Tsk_SheetFormulas f set f.SSCOPE_CODE =
multiple_replace(SSCOPE_CODE,
NEW t_text('$公式左边浮动行(FlexRow):1','$公式左边浮动行(FlexRow):2','$公式右边浮动行(FlexRow):1','$公式右边浮动行(FlexRow):2'),
NEW t_text('FlexRow_L=1','FlexRow_L=2','FlexRow_R=1','FlexRow_R=2'))
where f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE and i_formulatype = INPUT_TYPE and v_periodid = '2023' ;
update Tsk_SheetFormulas f set f.SSCOPE_CODE = replace(SSCOPE_CODE,'],[', ',')
where f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE
and i_formulatype = INPUT_TYPE and v_periodid = '2023';
-- 这些指定具体省市的SSCOPE 特殊处理
update Tsk_SheetFormulas f set f.SSCOPE_CODE =
(SELECT mof_div_code FROM tsk_sscopeTo_MofDivCode M WHERE trim(F.SSCOPE_CODE) = Trim(M.SSCOPE) )
where i_formulatype = INPUT_TYPE and f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE
and sscope is not null and v_periodid = '2023' AND f.SSCOPE_CODE like '单位=%'
and exists (select 1 from tsk_sscopeTo_MofDivCode M WHERE trim(F.SSCOPE_CODE) = Trim(M.SSCOPE)) ;
-- 剩下的SSCOPE_CODE挨个替换
for item in (
select PROVINCECITY, PROVINCECITY_CODE from tsk_sscopeTo_MofDivCode_sub
)loop
update Tsk_SheetFormulas f set f.SSCOPE_CODE = replace(f.sscope_code,item.PROVINCECITY,'MOF_DIV_CODE =^ '''||item.PROVINCECITY_CODE||'''')
where f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE and i_formulatype = INPUT_TYPE
and v_periodid = '2023'
AND f.SSCOPE_CODE like '单位=%'
AND f.SSCOPE_CODE like '%'||item.PROVINCECITY||'%' ;
end loop;
update Tsk_SheetFormulas f set f.SSCOPE_CODE = replace(f.sscope_code,',MOF_DIV_CODE','||MOF_DIV_CODE')
where f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE and i_formulatype = INPUT_TYPE
and v_periodid = '2023'
AND f.SSCOPE_CODE like '%MOF_DIV_CODE%';
--有些不规范的写法也要兼容
update Tsk_SheetFormulas f set f.SSCOPE_CODE = replace(f.sscope_code,'单位=')
where f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE and i_formulatype = INPUT_TYPE
and v_periodid = '2023'
AND f.SSCOPE_CODE like '单位=%';
update Tsk_SheetFormulas f set f.SSCOPE_CODE = replace(f.sscope_code,'位级次信息=','DW_LEVEL=~')
where f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE and i_formulatype = INPUT_TYPE
and v_periodid = '2023'
AND f.SSCOPE_CODE like '位级次信息=%';
-- if QIF [和 sscope里的写法不一样] 通常在DQJF中取数用,是一个等号
elsif INPUT_COLUMNCODE = 'QIF' then
update Tsk_SheetFormulas f set qif = replace(qif,'FWBM=();')
where f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE and i_formulatype = INPUT_TYPE
and v_periodid = '2023';
update Tsk_SheetFormulas f set qif = replace(qif,'FWBM=();')
where f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE and i_formulatype = INPUT_TYPE
and v_periodid = '2023';
update Tsk_SheetFormulas f set f.qif =
multiple_replace(qif,
NEW t_text('单位级次信息=','(全国合计#01)','(中央本级#02)','(地方合计#03)','(省级#04)','(省本级#05)','(地市合计#06)','(地市级#07)','(地市本级#08)','(区县合计#09)','(区县级#10)','(其他#11)'),
NEW t_text('DW_LEVEL=~', '[01]','[02]','[03]','[04]','[05]','[06]','[07]','[08]','[09]','[10]','[11]'))
where f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE and i_formulatype = INPUT_TYPE and v_periodid = '2023'
and qif like '%单位级次信息%' ;
update Tsk_SheetFormulas f set f.qif =
multiple_replace(qif,
NEW t_text('所在地区类型信息=','(省#1)','(自治区#2)','(直辖市#3)','(否#4)', ';'),
NEW t_text('DIV_CLASS=~' ,'[1]', '[2]', '[3]', '[4]', ' '))
where f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE
and i_formulatype = INPUT_TYPE and v_periodid = '2023'
and t_formula like '%所在地区类型信息%';
update Tsk_SheetFormulas f set f.qif =
multiple_replace(qif,
NEW t_text('计划单列市属性信息=(有#1)','计划单列市属性信息=(无#2)','省直管县信息=(是#1)','省直管县信息=(否#2)'),
NEW t_text('IS_DIRECT_PLAN=1','IS_DIRECT_PLAN==2','IS_DIRECT_COUNTY==1','IS_DIRECT_COUNTY==2'))
where f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE
and i_formulatype = INPUT_TYPE and v_periodid = '2023'
and t_formula like '%FWBM=%';
update Tsk_SheetFormulas f set f.qif =
multiple_replace(qif,
NEW t_text('自治州属性—信息=','(有#1)','(无#2)', ';'),
NEW t_text('NATIONAL_AUTO_SET=','1', '2', ' '))
where f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE
and i_formulatype = INPUT_TYPE and v_periodid = '2023'
and t_formula like '%FWBM=%';
update Tsk_SheetFormulas f set f.qif =
multiple_replace(qif,
NEW t_text('自治县信息=','(是#1)','(否#2)', ';'),
NEW t_text('IS_COUNTY_AUTO=','1', '2', ' '))
where f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE
and i_formulatype = INPUT_TYPE and v_periodid = '2023'
and t_formula like '%FWBM=%';
update Tsk_SheetFormulas f set f.qif =
multiple_replace(qif,
NEW t_text('国家扶贫重点县信息=','(是#1)','(否#2)','(已脱贫#3)', '|'),
NEW t_text('IS_POVERTY=~[' , '1', '2', '3]', '##')) -- 不替换成逗号, 影响后续根据逗号分割内容
where f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE
and i_formulatype = INPUT_TYPE and v_periodid = '2023'
and t_formula like '%国家扶贫重点县信息%'; -- 目前固定就是 1或3
update Tsk_SheetFormulas f set f.qif = replace(qif,'],[','##')
where f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE
and i_formulatype = INPUT_TYPE and v_periodid = '2023';
else
RAISE_APPLICATION_ERROR(-20123,'输入参数['||INPUT_COLUMNCODE||']不正确,请输入更新字段 例如:SSCOPE_CODE、QIF、EXEC_FORMULA');
end if;
commit; --固定信息翻译 这里提交
end PROC_replaceSScope;
procedure update_IF_to_ternary(INPUT_TYPE in varchar2) as
/* 处理 IF() 将字符串里的IF(A,B,C)替换为三元表达式 (A?B:C)
注意:如果子项中有逗号要替换成别的,防止解析失败
*/
PARAM_NUM NUMBER;
begin
update Tsk_SheetFormulas f set f.qif =replace(qif,'if(','IF(')
where f.v_periodid = '2023' and f.i_formulatype = INPUT_TYPE
and upper(T_FORMULA) like '%IF(%';
-- 找到最后一个匹配的 IF(
update Tsk_SheetFormulas f set f.if_body = substr(qif,instr(qif,'IF(',-1))
where f.v_periodid = '2023' and f.i_formulatype = INPUT_TYPE
and upper(T_FORMULA) like '%IF(%';
-- 之前的放到 if_head里
update Tsk_SheetFormulas f set f.if_head = substr(qif,0,instr(qif,'IF(',-1)-1)
where f.v_periodid = '2023' and f.i_formulatype = INPUT_TYPE
and upper(T_FORMULA) like '%IF(%';
-- 替换 if_body里第一个逗号
update Tsk_SheetFormulas f set f.if_body = regexp_replace(if_body,',','?',1,1)
where f.v_periodid = '2023' and f.i_formulatype = INPUT_TYPE
and if_body like '%IF(%';
-- 继续替换if_body里第一个逗号
update Tsk_SheetFormulas f set f.if_body = regexp_replace(if_body,',',':',1,1)
where f.v_periodid = '2023' and f.i_formulatype = INPUT_TYPE
and if_body like '%IF(%';
-- 这轮if_body完毕 去掉 IF(
update Tsk_SheetFormulas f set f.if_body = replace(if_body,'IF(','(')
where f.v_periodid = '2023' and f.i_formulatype = INPUT_TYPE
and if_body like '%IF(%';
-- 第二轮:如果if_head 还有IF( 的话 找到if_head最后一个匹配的 IF( 之后的拼到if_body
for i in 1..3 loop -- 1+3轮够多了
select count(1) INTO PARAM_NUM from Tsk_SheetFormulas f
where f.v_periodid = '2023' and f.i_formulatype = INPUT_TYPE
and if_head like '%IF(%';
if PARAM_NUM = 0 then
-- dbms_output.put_line('第'||to_number(i)||'-1次,没有IF(就算了');
exit;
end if;
update Tsk_SheetFormulas f set f.if_body = substr(if_head,instr(if_head,'IF(',-1))||if_body
where f.v_periodid = '2023' and f.i_formulatype = INPUT_TYPE
and if_head like '%IF(%';
-- if_head 存IF( 之前的内容
update Tsk_SheetFormulas f set f.if_head = substr(if_head,0,instr(if_head,'IF(',-1)-1)
where f.v_periodid = '2023' and f.i_formulatype = INPUT_TYPE
and if_head like '%IF(%';
-- 替换 if_body里第一个逗号
update Tsk_SheetFormulas f set f.if_body = regexp_replace(if_body,',','?',1,1)
where f.v_periodid = '2023' and f.i_formulatype = INPUT_TYPE
and if_body like '%IF(%';
-- 继续替换 if_body里第一个逗号
update Tsk_SheetFormulas f set f.if_body = regexp_replace(if_body,',',':',1,1)
where f.v_periodid = '2023' and f.i_formulatype = INPUT_TYPE
and if_body like '%IF(%';
-- 这轮if_body完毕 去掉 IF(
update Tsk_SheetFormulas f set f.if_body = replace(if_body,'IF(','(')
where f.v_periodid = '2023' and f.i_formulatype = INPUT_TYPE
and if_body like '%IF(%';
end loop;
-- 有头尾的 拼起来当EXEC_FORMULA
update Tsk_SheetFormulas f set f.qif = f.if_head||f.if_body
where f.v_periodid = '2023' and f.i_formulatype = INPUT_TYPE
and upper(T_FORMULA) like '%IF(%';
end update_IF_to_ternary;
procedure split_Max_And_Min as
/*
解析MAX和MIN ,用解析SUM的方式拆解所有字段,然后用逗号拼接 更新qif字段
*/
new_qif VARCHAR2(4000);
result_str VARCHAR2(4000);
begin
for item in (
select exec_formula,formula_con,data_id,report_table from gfa_datacheckset_ccq23 t
where t.set_year= '2023'
and (upper(exec_formula) like '%MAX(%' or upper(exec_formula) like '%MIN(%' )
)loop
--dbms_output.put_line('old_qif------'|| item.exec_formula);
if upper(item.exec_formula) like '%MIN%' then
new_qif := substr(upper(item.exec_formula),instr(upper(item.exec_formula),'MIN'));
else
new_qif := substr(upper(item.exec_formula),instr(upper(item.exec_formula),'MAX'));
end if;
new_qif := substr(new_qif,1,instr(new_qif,')'));
new_qif := replace(replace(new_qif,'MAX(','SUM('),'MIN(','SUM(');
--dbms_output.put_line('new_qif------'|| new_qif);
result_str := Excel_Sum_to_individual_cols(new_qif,item.report_table);
if upper(item.exec_formula) like '%MIN(%' then
-- dbms_output.put_line('result_str------'|| 'MIN'||replace(result_str,'+',','));
update gfa_datacheckset_ccq23 set exec_formula = replace(exec_formula
,replace(new_qif,'SUM(','MIN(')
,'MIN'||replace(result_str,'+',','))
where data_id = item.data_id;
else
-- dbms_output.put_line('result_str------'|| 'MAX'||replace(result_str,'+',','));
update gfa_datacheckset_ccq23 set exec_formula = replace(exec_formula
,replace(new_qif,'SUM(','MAX(')
,'MAX'||replace(result_str,'+',','))
where data_id = item.data_id;
end if;
new_qif := null;
result_str := null;
end loop;
end split_Max_And_Min;
--------------------------------- [ FormulaType=0 公式迁移过程 ] -------------------------------
procedure PROC_deal_type0 as
begin
-- 开始处理类型0 的qif
update Tsk_SheetFormulas f set f.qif = t_formula
where v_periodid = '2023'
and f.i_formulatype = 0;
for item in (
select v.v_bookname,v.v_taskcode,v.V_BOOKCODE from tsk_v_doc_2023 v
-- where v.V_BOOKNAME like 'J16%'
)loop
-- 先处理sum 再处理IF , 因为 SUM里的冒号逗号影响IF解析
update tsk_sheetformulas f
set qif = Excel_Sum_to_individual_cols(f.t_formula,item.v_bookname)
where f.v_periodid = 2023
and f.v_taskcode = item.v_taskcode
and f.v_bookcode = item.v_bookcode
and f.i_formulatype = 0
and UPPER(f.t_formula) like '%SUM%' ;
end loop;
-- 如果qif 里有IF 更新为三元表达式
PKG_TSK_Formulas_2_DZCOL_23.update_IF_to_ternary('0');
-- 更新到表样中 ---(很慢)------------------------------
update gfa_rptfixedconfig_ccq23 t set exec_formula = (
select f.qif from tsk_sheetformulas f
left join tsk_v_doc_2023 v
on f.v_periodid = v.v_periodid
and f.v_taskcode = v.V_TASKCODE
and f.v_bookcode = v.v_bookcode
where f.v_periodid = 2023
and f.i_formulatype = 0
and f.v_periodid = t.set_year
and v.v_bookname = t.report_code
and (select e.enchar from t_numTOEnChar e where e.num = f.i_col)||f.i_row = t.cd
and f.n_formulaid not in ('1072008','1072009') -- 这俩多余了 不必理会
)
where t.set_year = 2023
and data_type = 0
and t.report_code in (select v_bookname from tsk_v_doc_2023 v -- where v.V_BOOKNAME like 'J16%'
)--(很慢)
;
-- 公式中本表坐标也带上 表名前缀
for books in (
select v.v_bookname from tsk_v_doc_2023 v --where v.V_BOOKNAME like 'J16%'
)loop
dbms_output.put_line('开始处理----'||books.v_bookname);
PKG_TSK_Formulas_2_DZCOL_23.addTableCode_thisTable(books.v_bookname);
end loop;
update gfa_rptfixedconfig_ccq23 t set con_formula = report_code||'!'||cd||'='||replace(exec_formula,'zz','!')
where t.set_year = 2023 and t.data_type = 0 and t.report_code in ('F18','F19')
and t.cd in ('B7','D7','F7','H7','L7');
update gfa_rptfixedconfig_ccq23 t set con_formula = report_code||'!'||cd||'='||replace(exec_formula,'zz','!')
where t.set_year = 2023 and t.data_type = 0 and t.report_code in ('F20')
and t.cd in ('B7','D7','F7','H7');
/*-- 处理本表 CDS -- 可以在PROC_deal_type10 中执行
-- PKG_TSK_Formulas_2_DZCOL_23.set_ALL_CDS('gfa_rptfixedconfig_ccq23');
--补 con_formula -- 可以在PROC_deal_type10 中执行
update gfa_rptfixedconfig_ccq23 set con_formula = report_code||'!'||cd||'='||replace(exec_formula,'zz','!')
where set_year = 2023
and data_type = 0
and report_code in (select v_bookname from tsk_v_doc_2023 v)
and exec_formula is not null
and con_formula is null;*/
end PROC_deal_type0;
--------------------------------- [ FormulaType=10 公式迁移过程 ] -------------------------------
procedure split_DTF(INPUT_TYPE in varchar2,INPUT_NAME in varchar2) as
PARAM_V_BOOKCODE varchar2(20); -- v_bookcode
PARAM_V_TASKCODE varchar2(20); -- v_TASKcode
new_cd varchar2(30);
new_block varchar2(100);
year_str varchar2(10);
indicator_year varchar2(10);
count_Num number;
block_FWBM varchar2(200);
begin
select case when v_bookname = 'IB' then 'ZJS001FJNB' else s.v_taskcode end
,s.v_bookcode into PARAM_V_TASKCODE,PARAM_V_BOOKCODE
from tsk_v_doc_2023 s
where v_bookname = INPUT_NAME; -- IB 表在类型16中处理
-- 1 都是 &开头 直接去掉
if INPUT_TYPE = 10 then
update Tsk_SheetFormulas f set f.qif = substr(t_formula,2)
where f.v_taskcode = 'ZJS001FJNB' and v_bookcode = PARAM_V_BOOKCODE
and i_formulatype = INPUT_TYPE
and v_periodid = '2023';
end if;
-- DQJ[N,+0{0}]取当年 直接去掉
update Tsk_SheetFormulas f set f.qif = replace(qif,'DQJ[N,+0{0}]')
where f.v_taskcode = 'ZJS001FJNB' and v_bookcode = PARAM_V_BOOKCODE
and i_formulatype = INPUT_TYPE
and v_periodid = '2023';
-- DQJ[N,-1{0}] 取上年
update Tsk_SheetFormulas f set f.qif = replace(qif,'DQJ[N,-1{0}]','-1')
where f.v_taskcode = 'ZJS001FJNB' and v_bookcode = PARAM_V_BOOKCODE
and i_formulatype = INPUT_TYPE
and v_periodid = '2023';
-- 都是 ZJS001FJNB 直接去掉
update Tsk_SheetFormulas f set f.qif = replace(qif,'DTF(ZJS001FJNB,,','DQJF(')
where f.v_taskcode = 'ZJS001FJNB' and v_bookcode = PARAM_V_BOOKCODE
and i_formulatype = INPUT_TYPE
and v_periodid = '2023'
and qif like '%DTF(ZJS001FJNB,,%' ;
update Tsk_SheetFormulas f set f.qif = replace(qif,'DTF(ZJS001FJNB,','DQJF(')
where f.v_taskcode = 'ZJS001FJNB' and v_bookcode = PARAM_V_BOOKCODE
and i_formulatype = INPUT_TYPE
and v_periodid = '2023';
-- 3 替换QIF中的 单位级次 等信息
PKG_TSK_Formulas_2_DZCOL_23.PROC_replaceSScope(INPUT_TYPE,INPUT_NAME,'QIF');
-- 4
for item in (
select T_formula,split(qif,'DQJF(') as sp,n_formulaid from Tsk_SheetFormulas f
where f.v_taskcode = 'ZJS001FJNB' --都是 ZJS001FJNB
and i_formulatype = INPUT_TYPE
and v_periodid = '2023'
-- and v_bookcode in (select v_bookcode from tsk_v_doc_2023)
and v_bookcode = PARAM_V_BOOKCODE
-- and n_formulaid = '1125877' --
)loop
for oneRecord in ( -- 循环split拆分的 DTF式子
select * from table(item.sp)
)loop
if oneRecord.column_value is null or oneRecord.column_value = '(' or oneRecord.column_value not like '%\_%'escape '\' then
continue;
end if ;
--dbms_output.put_line('oneRecord------'||oneRecord.column_value);
block_FWBM := '';
for oneBlock in ( -- 循环 一个DTF 找到 v_indicatorcode 然后替换为 我们的坐标
select rownum rn,a.* from table(split(oneRecord.column_value,',')) a
)
loop
--dbms_output.put_line('oneBlock.column_value------'||oneBlock.column_value||'::'||oneBlock.rn);
if oneBlock.column_value = '(' then
continue;
elsif oneBlock.column_value like '%年%' then
select replace(oneBlock.column_value,'年') into year_str from dual;
indicator_year := year_str;
elsif instr(oneBlock.column_value,'_') = 1 then ----'||oneBlock.column_value);
if oneBlock.column_value like '%)%' then -- 没有单位括号结尾
select substr(oneBlock.column_value,1,instr(oneBlock.column_value,')')-1) into new_block from dual;
else -- 直接用
select oneBlock.column_value into new_block from dual;
end if;
if year_str is null then
year_str := '2023'; -- 取当年
indicator_year := '0';
end if;
select count(1) into count_Num from Tsk_IndicatorSheet i
where v_periodid = year_str
and i.v_taskcode = 'ZJS001FJNB'
and i.v_indicatorcode = new_block;
if count_Num != 1 then --year_str||'年无数据
new_cd := '0';
continue;
else
--dbms_output.put_line('new_block------'||new_block||'======='||year_str||'年');
select v.v_bookname||'!'||(select e.enchar from t_numTOEnChar e where e.num = dz.i_col)
||dz.i_row into new_cd from Tsk_IndicatorSheet dz
left join tsk_sheet_info v -- 查往年数据用这个表
on dz.v_periodid = v.v_periodid
and dz.v_taskcode = v.V_TASKCODE
and dz.v_bookcode = v.v_bookcode
where dz.v_periodid = year_str
and dz.i_booklevel = 1
and dz.v_taskcode = 'ZJS001FJNB'
and dz.v_indicatorcode = new_block
group by dz.v_periodid,v.v_bookname,dz.i_col,dz.i_row,v_indicatorcode;
--dbms_output.put_line('new_cd------'||new_cd);
end if;
else
block_FWBM := substr(oneBlock.column_value,1,instr(oneBlock.column_value,')')-1);
-- dbms_output.put_line('oneBlock.column_value 的else情况------'||oneBlock.column_value);
end if;
end loop;
--dbms_output.put_line('替换前----'|| '('||substr(oneRecord.column_value,1,instr(oneRecord.column_value,')')-1));
--dbms_output.put_line('替换后----'|| '('||block_FWBM||','||indicator_year||','||new_cd);
if block_FWBM is null and indicator_year = 0 then
-- 都是取当年的数据, 就不用DQJF()了
update Tsk_SheetFormulas set qif = replace(qif
,'DQJF('||substr(oneRecord.column_value,1,instr(oneRecord.column_value,')'))
, new_cd)
where n_formulaid = item.n_formulaid;
else
if block_FWBM is null then -- 防止程序解析报错
block_FWBM := 'NULL';
end if;
block_FWBM := replace(block_FWBM,'||',','); --DQJF条件中||改为逗号
update Tsk_SheetFormulas set qif = replace(qif
,'('||substr(oneRecord.column_value,1,instr(oneRecord.column_value,')')-1)
, '('||block_FWBM||','||indicator_year||','||new_cd)
where n_formulaid = item.n_formulaid;
end if;
end loop;
end loop;
end split_DTF;
procedure PROC_type10(INPUT_NAME in varchar2) as
/*
* 处理i_FormulaType=10 (全是生成表的取数公式)
* 定义生成表从录入表取数的公式。
*/
INPUT_NAME_UNDERLINE varchar2(10);
begin
SELECT REPLACE(INPUT_NAME,'-','_') INTO INPUT_NAME_UNDERLINE FROM DUAL;
-- gfa_rptfixedconfig_ccq23表是 J01_2
-- 第一步 : 设置 QIF
-- 第二步 : 替换QIF 中的坐标
PKG_TSK_Formulas_2_DZCOL_23.split_DTF('10',INPUT_NAME);
--第三步 : 拼 con_formula ,类型10带等号
update gfa_rptfixedconfig_ccq23 t set t.exec_formula = (
select replace(f.qif,'!','zz') from tsk_sheetformulas f
left join tsk_v_doc_2023 v
on f.v_periodid = v.v_periodid
and f.v_taskcode = v.V_TASKCODE
and f.v_bookcode = v.v_bookcode
where f.v_periodid = 2023
and f.i_formulatype = 10
and v.v_bookname = INPUT_NAME
and (select e.enchar from t_numTOEnChar e where e.num = f.i_col)||f.i_row = t.cd
)
where t.set_year = 2023
and t.report_code = INPUT_NAME_UNDERLINE
and data_type = 0;
end PROC_type10;
procedure PROC_deal_type10 as
/** 循环所有类型10涉及到的表( 全是生成表)
*/
begin
for item in (
select v.v_bookname from tsk_v_doc_2023 v
)loop
PKG_TSK_Formulas_2_DZCOL_23.PROC_type10(item.v_bookname);
end loop;
-- 处理本表 CDS
PKG_TSK_Formulas_2_DZCOL_23.set_ALL_CDS('gfa_rptfixedconfig_ccq23');
update gfa_rptfixedconfig_ccq23 set exec_formula = replace(exec_formula,'##',',')
where set_year = 2023
and data_type = 0
and report_code in (select v_bookname from tsk_v_doc_2023 v)
and exec_formula like '%##%';
-- 这些多年前的坐标指标表中没有 故手动设置
update gfa_rptfixedconfig_ccq23 t set exec_formula = 'DQJF(NULL,2019,L01zzC5)' where t.set_year = 2023 and t.report_code = 'F18' and t.cd = 'B7' and t.data_type = 0 ;
update gfa_rptfixedconfig_ccq23 t set exec_formula = '(DQJF(NULL,2019,L01zzC5)-DQJF(NULL,2018,L01zzC5))/DQJF(NULL,2018,L01zzC5)' where t.set_year = 2023 and t.report_code = 'F18' and t.cd = 'D7' and t.data_type = 0 ;
update gfa_rptfixedconfig_ccq23 t set exec_formula = 'DQJF(NULL,2020,L01zzC5)' where t.set_year = 2023 and t.report_code = 'F18' and t.cd = 'F7' and t.data_type = 0 ;
update gfa_rptfixedconfig_ccq23 t set exec_formula = '(DQJF(NULL,2020,L01zzC5)-DQJF(NULL,2019,L01zzC5))/DQJF(NULL,2019,L01zzC5)' where t.set_year = 2023 and t.report_code = 'F18' and t.cd = 'H7' and t.data_type = 0 ;
update gfa_rptfixedconfig_ccq23 t set exec_formula = '(DQJF(NULL,2021,L01zzC5)-DQJF(NULL,2020,L01zzC5))/DQJF(NULL,2020,L01zzC5)' where t.set_year = 2023 and t.report_code = 'F18' and t.cd = 'L7' and t.data_type = 0 ;
update gfa_rptfixedconfig_ccq23 t set exec_formula = 'DQJF(NULL,2019,L02zzC5)' where t.set_year = 2023 and t.report_code = 'F19' and t.cd = 'B7' and t.data_type = 0 ;
update gfa_rptfixedconfig_ccq23 t set exec_formula = '(DQJF(NULL,2019,L02zzC5)-DQJF(NULL,2018,L02zzC5))/DQJF(NULL,2018,L02zzC5)' where t.set_year = 2023 and t.report_code = 'F19' and t.cd = 'D7' and t.data_type = 0 ;
update gfa_rptfixedconfig_ccq23 t set exec_formula = 'DQJF(NULL,2020,L02zzC5)' where t.set_year = 2023 and t.report_code = 'F19' and t.cd = 'F7' and t.data_type = 0 ;
update gfa_rptfixedconfig_ccq23 t set exec_formula = '(DQJF(NULL,2020,L02zzC5)-DQJF(NULL,2019,L02zzC5))/DQJF(NULL,2019,L02zzC5)' where t.set_year = 2023 and t.report_code = 'F19' and t.cd = 'H7' and t.data_type = 0 ;
update gfa_rptfixedconfig_ccq23 t set exec_formula = '(DQJF(NULL,2021,L02zzC5)-DQJF(NULL,2020,L02zzC5))/DQJF(NULL,2020,L02zzC5)' where t.set_year = 2023 and t.report_code = 'F19' and t.cd = 'L7' and t.data_type = 0 ;
update gfa_rptfixedconfig_ccq23 t set exec_formula = 'DQJF(NULL,2019,L01zzC5)/DQJF(NULL,2019,L24zzB90)' where t.set_year = 2023 and t.report_code = 'F20' and t.cd = 'B7' and t.data_type = 0 ;
update gfa_rptfixedconfig_ccq23 t set exec_formula = 'DQJF(NULL,2019,L02zzC5)/DQJF(NULL,2019,L24zzB90)' where t.set_year = 2023 and t.report_code = 'F20' and t.cd = 'D7' and t.data_type = 0 ;
update gfa_rptfixedconfig_ccq23 t set exec_formula = 'DQJF(NULL,2020,L01zzC5)/DQJF(NULL,2020,L24zzB90)' where t.set_year = 2023 and t.report_code = 'F20' and t.cd = 'F7' and t.data_type = 0 ;
update gfa_rptfixedconfig_ccq23 t set exec_formula = 'DQJF(NULL,2020,L02zzC5)/DQJF(NULL,2020,L24zzB90)' where t.set_year = 2023 and t.report_code = 'F20' and t.cd = 'H7' and t.data_type = 0 ;
-- 这个特殊
update gfa_rptfixedconfig_ccq23 t set con_formula = 'F13!B7=DQJF(IS_POVERTY=~[1,3],0,DW_NAME)'
,exec_formula = 'DQJF(IS_POVERTY=~[1,3],0,DW_NAME)'
,cds = 'IS_POVERTY;DW_NAME'
where t.set_year = 2023 and t.report_code = 'F13' and t.cd = 'B7' and t.data_type = 0 ;
--补 con_formula
update gfa_rptfixedconfig_ccq23 set con_formula = report_code||'!'||cd||'='||replace(exec_formula,'zz','!')
where set_year = 2023
and data_type = 0
and report_code in (select v_bookname from tsk_v_doc_2023 v)
and exec_formula is not null
and con_formula is null;
end PROC_deal_type10;
--------------------------------- [ FormulaType=16 公式迁移过程 ] -------------------------------
procedure PROC_deal_type16 as
new_left varchar2(100);
new_cd varchar2(300);
new_right varchar2(2000);
indicator varchar2(100);
indicator_year varchar2(10);
indicator_tail varchar2(100);
year_str varchar2(10);
taskcode_str varchar2(20);
sequ number;
count_Num number;
cd_lastYear varchar2(50);
begin
update Tsk_SheetFormulas f set qif = t_formula
where v_periodid = '2023'
and i_formulatype = '16';
update Tsk_SheetFormulas f set qif = replace(qif,'(0,','(')
where v_periodid = '2023'
and i_formulatype = '16';
for item in (
select split(qif,'[') as sp,T_formula,n_formulaid,i_row,i_col,
(select v_bookname from tsk_v_doc_2023 where v_bookcode = f.v_bookcode
and v_taskcode = f.v_taskcode) report_code
from Tsk_SheetFormulas f
where v_periodid = '2023'
and i_formulatype = '16'
-- and f.v_taskcode = param_taskcode
-- and n_formulaid = '920018'
)loop
-- dbms_output.put_line('T_formula------'||item.T_formula);
sequ := 0;
for oneRecord in ( -- 循环split拆分的 DTF式子
select * from table(item.sp)
)loop
if oneRecord.column_value is null then
-- dbms_output.put_line('oneBlock.column_value空 continue------');
continue;
end if ;
sequ := sequ +1;
-- dbms_output.put_line('第'||sequ||'个方括号------'||oneRecord.column_value);
for oneBlock in ( -- 循环 一个DTF 找到 v_indicatorcode 然后替换为 我们的坐标
select * from table(split(oneRecord.column_value,','))
)
loop
-- dbms_output.put_line('一个逗号区间oneBlock.column_value------'||oneBlock.column_value);
if oneBlock.column_value = '(' then
continue;
elsif instr(oneBlock.column_value,'_') = 1 then
if sequ = 1 then -- 这是审核公式左边,因为都是取当年且是当前格子,直接取
select (select d.col_code from tsk_v_IB_to_DW d where d.i_col = item.i_col and d.i_row = item.i_row) into new_cd from dual;
new_left := new_cd ||substr(oneRecord.column_value,instr(oneRecord.column_value,']')+1);
continue;
else -- 审核公式右边
if indicator_year = 20 then -- 表中能截成20 的都是"2021N" 这里按上年来处理
indicator_year := -1;
end if;
select replace(substr(oneBlock.column_value,instr(oneBlock.column_value,'(')+1,2) ,')') into indicator_year from dual;
select substr(oneBlock.column_value,1,instr(oneBlock.column_value,'(')-1) into indicator from dual;
if oneBlock.column_value like '%]%' then
select substr(oneBlock.column_value,instr(oneBlock.column_value,']')+1) into indicator_tail from dual;
end if;
if indicator_year = 0 then -- 先处理都是当年的
taskcode_str := 'ZJS001FJNB';
else -- 不是当年,需要根据表样对照关系查对应年的坐标
taskcode_str := 'ZJS001FJNB';
end if;
end if;
elsif instr(oneBlock.column_value,')]') >= 1 then
select substr(oneBlock.column_value,1,instr(oneBlock.column_value,')]')-1) into taskcode_str from dual;
year_str := '';
else
dbms_output.put_line('else情况--'||item.n_formulaid||'----'||oneBlock.column_value);
null;
end if;
end loop; -- 结束逗号区间
if sequ <> 1 then
if indicator_year = 0 then -- 当年的
select count(1) into count_Num from Tsk_IndicatorSheet i
where v_periodid = to_char(indicator_year+2023)
and i.v_taskcode = taskcode_str
and i.v_indicatorcode = indicator;
--new_cd := '未匹配';
if count_Num = 1 then -- 有数据
select (select d.col_code from tsk_v_IB_to_DW d where d.i_col = dz.i_col and d.i_row = dz.i_row) into new_cd
from Tsk_IndicatorSheet dz
where v_periodid = to_char(indicator_year+2023)
and dz.v_taskcode = taskcode_str
and dz.v_indicatorcode = indicator;
indicator_year := '';
taskcode_str := '';
indicator := '';
-- dbms_output.put_line('new_cd------'||new_cd);
else -- 22年无数据
dbms_output.put_line('没查到数据----------'||item.n_formulaid||'------'
||to_char(indicator_year+2023)||'||'||taskcode_str||'||'||indicator);
end if;
count_Num := 0;
elsif indicator_year = -1 then --上年度,需要根据表样对照关系查对应年的坐标
select (select d.col_code from tsk_v_IB_to_DW d where d.i_col = dz.i_col and d.i_row = dz.i_row) into cd_lastYear
from Tsk_IndicatorSheet dz
where v_periodid = to_char(0+2023) -- 当年
and dz.v_taskcode = taskcode_str
and dz.v_indicatorcode = indicator;
-- dbms_output.put_line('cd_thisYear----- '||cd_thisYear);
-- dbms_output.put_line('to_char(indicator_year)----- '||to_char(indicator_year));
-- 封面表校验 用getLastYearData
new_cd := 'getLastYearData('||cd_lastYear||',-1)';
else --其他年度,需要根据表样对照关系查对应年的坐标
new_cd := indicator_year||'特殊处理';
end if;
new_right := new_right || new_cd||indicator_tail;
indicator_tail := '';
-- dbms_output.put_line('new_right-----'||new_right);
end if;
end loop; -- 结束 一个方括号内
-- dbms_output.put_line('new_left||new_right-----'||new_left||new_right );
update Tsk_SheetFormulas set qif = replace(new_left||new_right ,'=','==')
where v_periodid = '2023'
and n_formulaid = item.n_formulaid;
new_right := '';
-- commit;
end loop;-- 结束 单行数据
delete from gfa_datacheckset_ccq23 where set_year = 2023 and formula_type_dz = 16;
-- 录入表和简表各自插入
insert into gfa_datacheckset_ccq23 (DATA_ID, SET_YEAR, CHECK_NAME, FORMULA_CON, CHECK_TYPE, EXCTYPE, USERTYPE, ERRORDESC, IS_USING, ORDERID, LINKCONFIG, CHECKSQL, BEANURL, CREATE_TIME, IS_DELETED, UPDATE_TIME, MOF_DIV_CODE, CREATE_USER, UPDATE_USER, REPORT_TABLE, CHECK_CODE, CHECK_CODE_JQ, CHECK_CODE_JQ_2, IS_CHECK, REMARK, LOC_ITEMS, CDS, SSCOPE, EXEC_FORMULA, FORMULA_TYPE, FORMULA_TYPE_DZ)
select aa.N_formulaID, '2023',aa.report_code||'：【“'||aa.name||'”（'||aa.i_row||'行'||aa.i_col||'栏）'||aa.t_formuladescription||'】',aa.QIF, 0, 3001, 3, '应与上年总决算一致', 1, '3001'
||'000'
||lpad(i_row,4,'0')||lpad(i_col,4,'0') as orderid, '', '', '', to_date('03-01-2024 16:40:56', 'dd-mm-yyyy hh24:mi:ss'), 2, to_date('03-01-2024 15:47:55', 'dd-mm-yyyy hh24:mi:ss'), '460000000', 'CCQ_type_16', '1', 'N01', 'ZJSJB001FJNB', '', '', '', '', 'N01!'||substr(qif,1,instr(qif,'=')-1), null, '', aa.qif, 0, '16'
from (select f.*,d.col_name as name,
'录入表封面' as report_code
from Tsk_SheetFormulas f
left join tsk_v_IB_to_DW d
on f.i_col = d.i_col
and f.i_row = d.i_row
where v_periodid = '2023'
and i_formulatype = '16'
and f.v_taskcode = 'ZJS001FJNB'
and f.v_bookcode = '001'
) aa;
-- 录入表和简表各自插入
insert into gfa_datacheckset_ccq23 (DATA_ID, SET_YEAR, CHECK_NAME, FORMULA_CON, CHECK_TYPE, EXCTYPE, USERTYPE, ERRORDESC, IS_USING, ORDERID, LINKCONFIG, CHECKSQL, BEANURL, CREATE_TIME, IS_DELETED, UPDATE_TIME, MOF_DIV_CODE, CREATE_USER, UPDATE_USER, REPORT_TABLE, CHECK_CODE, CHECK_CODE_JQ, CHECK_CODE_JQ_2, IS_CHECK, REMARK, LOC_ITEMS, CDS, SSCOPE, EXEC_FORMULA, FORMULA_TYPE, FORMULA_TYPE_DZ)
select aa.N_formulaID, '2023',aa.report_code||'：【“'||aa.name||'”（'||aa.i_row||'行'||aa.i_col||'栏）'||aa.t_formuladescription||'】',aa.QIF, 0, 5001, 3, '应与上年总决算一致', 1, '5001'
||'000'
||lpad(i_row,4,'0')||lpad(i_col,4,'0') as orderid, '', '', '', to_date('03-01-2024 16:40:56', 'dd-mm-yyyy hh24:mi:ss'), 2, to_date('03-01-2024 15:47:55', 'dd-mm-yyyy hh24:mi:ss'), '460000000', 'CCQ_type_16', '1', 'N01', 'ZJSJB001FJNB', '', '', '', '', 'N01!'||substr(qif,1,instr(qif,'=')-1), null, '', aa.qif, 0, '16'
from (select f.*,d.col_name as name,
'简表封面' as report_code
from Tsk_SheetFormulas f
left join tsk_v_IB_to_DW d
on f.i_col = d.i_col
and f.i_row = d.i_row
where v_periodid = '2023'
and i_formulatype = '16'
and f.v_taskcode = 'ZJSJB001FJNB'
and f.v_bookcode = '001'
) aa;
-- 封面表录入表同简表一样, 所以这两类公式的取值也一样,都应取自上年
update gfa_datacheckset_ccq23 t
set formula_con = replace(formula_con,'==','==getLastYearData(')||',-1)'
,exec_formula = replace(exec_formula,'==','==getLastYearData(')||',-1)'
,cds = replace(exec_formula,'==','==getLastYearData(')||',-1)'
where set_year = 2023 and formula_type_dz = 16
and exec_formula not like '%getLastYearData(%' ;
update gfa_datacheckset_ccq23 t
set cds = replace(replace(exec_formula,'==getLastYearData(',';'),',-1)',';')
where set_year = 2023 and formula_type_dz = 16;
end PROC_deal_type16;
--------------------------------- [ FormulaType=5 公式迁移过程 ] -------------------------------
/* i_FormulaType=5 审核公式。主要是用于表间数据勾稽关系审核。
t_formula 格式:
[_01_029_0029_00006_20102302(0)]>=[_01_029_0029_00006_20102302(-1)]
*/
procedure PROC_setQIF_type5 as
new_left varchar2(100);
new_cd varchar2(130);
new_right varchar2(2000);
indicator varchar2(100);
indicator_year varchar2(10);
indicator_tail varchar2(100);
taskcode_str varchar2(20);
sequ number;
count_Num number;
begin
for item in (
select split(T_formula,'[') as sp,T_formula,n_formulaid,i_row,i_col,v_taskcode,
(select v_bookname from tsk_v_doc_2023
where v_bookcode = f.v_bookcode
and v_taskcode = f.v_taskcode) report_code
from Tsk_SheetFormulas f
where v_periodid = '2023'
and i_formulatype = '5'
-- and n_formulaid = '1013160'
-- and t_formula like '%(-1)%' -- 先处理都是当年的
and t_formula not like '%,CZSZ%' -- 先不处理旬月报
)loop
-- dbms_output.put_line('T_formula------'||item.T_formula);
sequ := 0;
taskcode_str := item.v_taskcode;
for oneRecord in ( -- 循环split拆分的 DTF式子
select * from table(item.sp)
)loop
if oneRecord.column_value is null then
-- dbms_output.put_line('oneBlock.column_value空 continue------');
continue;
end if ;
sequ := sequ +1;
-- dbms_output.put_line('第'||sequ||'个方括号------'||oneRecord.column_value);
for oneBlock in ( -- 根据逗号分隔,提取出code,是否取上年,是否简表,以及是否乘系数
select * from table(split(oneRecord.column_value,','))
)loop -- 循环
-- dbms_output.put_line('一个逗号区间oneBlock.column_value------'||oneBlock.column_value);
if oneBlock.column_value = '(' then
continue;
elsif instr(oneBlock.column_value,'_') = 1 then
if sequ = 1 then -- 这是审核公式左边,因为都是取当年且是当前格子,直接取
select item.report_code||'zz'||(select e.enchar from t_numTOEnChar e where e.num = item.i_col)||item.i_row as cd into new_cd from dual;
new_left := new_cd ||substr(oneBlock.column_value,instr(oneBlock.column_value,']')+1);
continue;
else -- 审核公式右边
select substr(oneBlock.column_value,instr(oneBlock.column_value,'(')+1) into indicator_year from dual;
if indicator_year like '%,%' then
select substr(indicator_year,1,instr(indicator_year,',')-1) into indicator_year from dual;
elsif indicator_year = '0' then
null;
elsif indicator_year like '%)%' then
select substr(indicator_year,1,instr(indicator_year,')')-1) into indicator_year from dual;
end if;
-- dbms_output.put_line('indicator_year-----::'||indicator_year);
if indicator_year like '20%' then -- 表中能截成20 的都是"2021N" 这里按上年来处理
indicator_year := -1;
end if;
-- select replace(substr(oneBlock.column_value,instr(oneBlock.column_value,'(')+1,2) ,')') into indicator_year from dual;
select substr(oneBlock.column_value,1,instr(oneBlock.column_value,'(')-1) into indicator from dual;
if oneBlock.column_value like '%]%' then
select substr(oneBlock.column_value,instr(oneBlock.column_value,']')+1) into indicator_tail from dual;
end if;
/*
if indicator_year = 0 then -- 先处理都是当年的
taskcode_str := 'ZJS001FJNB';
else -- 不是当年,需要根据表样对照关系查对应年的坐标
taskcode_str := 'ZJS001FJNB';
end if;*/
end if;
elsif instr(oneBlock.column_value,')]') >= 1 then
-- dbms_output.put_line('instr(oneBlock.column_value,'')]'') >= 1');
select substr(oneBlock.column_value,1,instr(oneBlock.column_value,')]')-1) into taskcode_str from dual;
select substr(oneBlock.column_value,instr(oneBlock.column_value,']')+1) into indicator_tail from dual;
else
dbms_output.put_line('else情况--'||item.n_formulaid||'----'||oneBlock.column_value);
null;
end if;
end loop; -- 结束逗号区间
if sequ <> 1 then
select count(1) into count_Num from Tsk_IndicatorSheet i
where v_periodid = to_char(indicator_year+2023)
and i.v_taskcode = taskcode_str
and i.v_indicatorcode = indicator;
if count_Num = 1 then -- 有数据
if indicator_year = '0' then -- 当年的
select (select v_bookname from tsk_v_doc_2023 d --取上年 这里是22
where d.v_bookcode = dz.v_bookcode
and d.v_taskcode = dz.v_taskcode
)||'!'||(select e.enchar from t_numTOEnChar e where e.num = dz.i_col)||dz.i_row into new_cd
from Tsk_IndicatorSheet dz
where v_periodid = to_char(indicator_year+2023)
and dz.v_taskcode = taskcode_str
and dz.v_indicatorcode = indicator;
elsif indicator_year = '-1' then --上年度,需要根据表样对照关系查对应年的坐标
select (select v_bookname from tsk_v_doc_2022 d --取上年 这里是22
where d.v_bookcode = dz.v_bookcode
and d.v_taskcode = dz.v_taskcode
)||'!'||(select e.enchar from t_numTOEnChar e where e.num = dz.i_col)||dz.i_row into new_cd
from Tsk_IndicatorSheet dz
where v_periodid = to_char(indicator_year+2023)
and dz.v_taskcode = taskcode_str
and dz.v_indicatorcode = indicator;
elsif indicator_year is not null then --其他年度,需要根据表样对照关系查对应年的坐标
null;
end if;
indicator := '';
else -- 没查到数据
null;
-- dbms_output.put_line('没查到数据----------'||item.n_formulaid||'------'||to_char(indicator_year+2023)||'||'||taskcode_str||'||'||indicator);
end if;
count_Num := 0;
if indicator_year = '0' then -- 当年的
null;
elsif indicator_year = '-1' then --上年度,需要根据表样对照关系查对应年的坐标
--转成: extractData_2021_null_L07_null_W21_L070014_JE6
-- new_cd := 'extractData_'||to_char(indicator_year+2023)||'_null_'||item.report_code||'_null_'||substr(new_cd,instr(new_cd,'!')+1)||'_null_null';
new_cd := 'getDataYear('||new_cd||',-1)';
elsif indicator_year is not null then --其他年度,需要根据表样对照关系查对应年的坐标
new_cd := indicator_year||'特殊处理';
end if;
new_right := new_right||new_cd||indicator_tail;
new_cd := '';
indicator_tail := '';
-- dbms_output.put_line('new_right-----'||new_right);
end if;
end loop; -- 结束 一个方括号内
-- dbms_output.put_line('new_left||new_right-----'||new_left||new_right );
update Tsk_SheetFormulas set qif = new_left||new_right
where v_periodid = '2023' and n_formulaid = item.n_formulaid;
new_right := '';
-- commit;
end loop;-- 结束 单行数据
--特殊情况特殊处理
update tsk_sheetformulas f set qif = '-1*L07!P7<=L05!D53' where v_periodid = '2023' and f.n_formulaid = '929661';
end PROC_setQIF_type5;
procedure PROC_deal_type5 as
begin
-- 第一步 : 翻译 t_formula为 QIF
PKG_TSK_Formulas_2_DZCOL_23.PROC_setQIF_type5;
--第二步 : 设置 SSCOPE_CODE
for item in (
select v.v_bookname from tsk_sheetformulas f
left join tsk_v_doc_2023 v
on f.v_taskcode = v.V_TASKCODE
and f.v_bookcode = v.v_bookcode
where f.v_periodid = '2023' and f.i_formulatype = 5
and v.booktype not like '4%'
group by v.v_bookname
)loop
PKG_TSK_Formulas_2_DZCOL_23.PROC_replaceSScope('5',item.v_bookname,'SSCOPE_CODE'); -- 翻译类型5的SSCOPE到SSCOPE_CODE
end loop;
--第三步 : 设置 QIF
update Tsk_SheetFormulas f set f.qif =
multiple_replace(qif,
NEW t_text('<=','>=','=='),
NEW t_text('<@@','>@@','@@@@')
)
where v_periodid = '2023'
and i_formulatype = 5;
-- 剩下的 = 替换为 ==
update Tsk_SheetFormulas f set f.qif = replace(qif,'=','==')
where v_periodid = '2023'
and i_formulatype = 5;
-- 把之前的@@ 替回 =
update Tsk_SheetFormulas f set f.qif = replace(qif,'@@','=')
where v_periodid = '2023'
and i_formulatype = 5;
/* 特殊情况特殊处理 ---------------------- */
update tsk_sheetformulas f set qif = '-1*L07!P7<=L05!D53' where f.n_formulaid = '1088059';
--第四步 : 全部插入
delete from gfa_datacheckset_ccq23 where set_year = 2023 and formula_type_dz = 5;
insert into gfa_datacheckset_ccq23 (DATA_ID, SET_YEAR, CHECK_NAME, FORMULA_CON, CHECK_TYPE, EXCTYPE, USERTYPE, ERRORDESC, IS_USING, ORDERID, LINKCONFIG, CHECKSQL, BEANURL, CREATE_TIME, IS_DELETED, UPDATE_TIME, MOF_DIV_CODE, CREATE_USER, UPDATE_USER, REPORT_TABLE, CHECK_CODE, CHECK_CODE_JQ, CHECK_CODE_JQ_2, IS_CHECK, REMARK, LOC_ITEMS, CDS, SSCOPE, EXEC_FORMULA, FORMULA_TYPE, FORMULA_TYPE_DZ)
select aa.n_formulaid, '2023', aa.v_bookname||'表：【“'||replace(r.report_item_name,' ')||'”（'||aa.cd||'）'||aa.t_formuladescription ||'】'
,aa.qif, 0, (case when aa.v_bookname like 'JB%' then 5001 when aa.v_bookname like 'L%' then 3001 else 4001 end), 3, aa.t_formuladescription, 1,
(case when aa.v_bookname like 'JB%' then '5001' when aa.v_bookname like 'L%' then '3001' else '4001' end)
||rpad(regexp_substr(replace(aa.v_bookname,'-'),'\d+'),3,'0')
||lpad(i_row,4,'0')||lpad(i_col,4,'0') as orderid
, '', '', ''
, to_date('03-01-2024 16:40:56', 'dd-mm-yyyy hh24:mi:ss'), 2, to_date('03-01-2024 16:40:56', 'dd-mm-yyyy hh24:mi:ss')
, '460000000', 'CCQ_type_5', '1', aa.v_bookname, case when aa.v_bookname like 'JB%' then 'ZJSJB001FJNB' else 'ZJS001FJNB' end, '', '', '', ''
, replace(aa.v_bookname,'-','_')||'!'||aa.cd, null, aa.sscope_code, aa.qif, 0, '5'
from ( select v.v_bookname,(select e.enchar from t_numTOEnChar e where e.num = f.i_col)||f.i_row as cd,
f.* from tsk_sheetformulas f
left join tsk_v_doc_2023 v
on f.v_taskcode = v.V_TASKCODE
and f.v_bookcode = v.v_bookcode
where f.v_periodid = '2023'
and f.i_formulatype = 5
and t_formula not like '%,CZSZ%' -- 先不处理旬月报
)aa
left join gfa_rptfixedconfig_ccq23 r
on r.set_year = aa.v_periodid and r.report_code = aa.V_BOOKNAME
and r.cd = aa.cd;
update gfa_datacheckset_ccq23 t set t.exec_formula
= replace(exec_formula,'!','zz')
where t.set_year=2023 and formula_type_dz = 5;
end PROC_deal_type5;
--------------------------------- [ FormulaType=1 公式迁移过程 ] -------------------------------
procedure split_DQJF(INPUT_TYPE in varchar2,INPUT_NAME in varchar2) as
/* 将多个 DQJF(FWBM=(&BBDW);单位级次信息=(省本级#05),0,_01_006_006_004_00008)-DQJF(FWBM=(&BBDW);单位级次信息=(地市合计#06),0,_01_006_006_002_00008)
拆分处理
*/
PARAM_V_BOOKCODE varchar2(20); -- v_bookcode
PARAM_V_TASKCODE varchar2(20); -- v_TASKcode
new_cd varchar2(30);
new_block varchar2(100);
year_str varchar2(10);
count_Num number;
count_Num_year number;
block_FWBM varchar2(200);
begin
select case when v_bookname = 'IB' then 'ZJS001FJNB' else s.v_taskcode end,s.v_bookcode into PARAM_V_TASKCODE,PARAM_V_BOOKCODE from tsk_v_doc_2023 s
where v_bookname = INPUT_NAME;
-- 1 处理 等号 把不需要替换的先改成 @@
update Tsk_SheetFormulas f set f.qif =
multiple_replace(qif,
NEW t_text('<=','>=', '==', '信息=', 'FWBM=('),
NEW t_text('<@@','>@@','@@@@','信息@@','FWBM@@(')
)
where v_periodid = '2023'
and i_formulatype = INPUT_TYPE
and f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE;
-- 剩下的 = 替换为 ==
update Tsk_SheetFormulas f set f.qif = replace(qif,'=','==')
where v_periodid = '2023'
and i_formulatype = INPUT_TYPE
and f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE;
-- 把之前的@@ 替回 =
update Tsk_SheetFormulas f set f.qif = replace(qif,'@@','=')
where v_periodid = '2023'
and i_formulatype = INPUT_TYPE
and f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE;
-- 3 替换QIF中的 单位级次 等信息
PKG_TSK_Formulas_2_DZCOL_23.PROC_replaceSScope(INPUT_TYPE,INPUT_NAME,'QIF');
for item in (
select T_formula,split(qif,'DQJF(') as sp,n_formulaid from Tsk_SheetFormulas f
where f.v_taskcode = PARAM_V_TASKCODE
and i_formulatype = INPUT_TYPE
and v_periodid = '2023'
-- and v_bookcode in (select v_bookcode from tsk_v_doc_2023)
and v_bookcode = PARAM_V_BOOKCODE
and f.t_formula like '%DQJF(%'
-- and n_formulaid in ('924593','924594')
)loop
for oneRecord in ( -- 循环split拆分的 DTF式子
select * from table(item.sp)
)loop
if oneRecord.column_value is null then
continue;
end if ;
-- dbms_output.put_line('oneRecord------'||oneRecord.column_value);
block_FWBM := '';
for oneBlock in ( -- 循环 一个DTF 找到 v_indicatorcode 然后替换为 我们的坐标
select rownum rn,a.* from table(split(oneRecord.column_value,',')) a
)
loop
-- dbms_output.put_line('oneBlock.column_value------'||oneBlock.column_value||'::'||oneBlock.rn);
if oneBlock.column_value = '(' then
continue;
elsif instr(oneBlock.column_value,'_') = 1 then ----'||oneBlock.column_value);
if oneBlock.column_value like '%)%' then -- 没有单位括号结尾
select substr(oneBlock.column_value,1,instr(oneBlock.column_value,')')-1) into new_block from dual;
else -- 直接用
select oneBlock.column_value into new_block from dual;
end if;
select count(1) into count_Num from Tsk_IndicatorSheet i
where v_periodid = '2023'
and i.v_taskcode = PARAM_V_TASKCODE
and i.v_indicatorcode = new_block;
if count_Num = 1 then -- 正常情况 取23年
-- dbms_output.put_line('正常情况 取23年------'||new_block);
select v.v_bookname||'!'||(select e.enchar from t_numTOEnChar e where e.num = dz.i_col)
||dz.i_row into new_cd from Tsk_IndicatorSheet dz
left join tsk_v_doc_2023 v
on dz.v_taskcode = v.V_TASKCODE
and dz.v_bookcode = v.v_bookcode
where dz.v_periodid = '2023' -- 默认当年
and dz.v_taskcode = PARAM_V_TASKCODE
and dz.v_indicatorcode =new_block;
else -- 23年无数据
--dbms_output.put_line('当年无数据 ------'||new_block);
select count(1) into count_Num_year from (select substr(i.v_periodid,1,4) as year,i.* from Tsk_IndicatorSheet i
where i.v_indicatorcode = new_block
order by year desc) where rownum = 1;
if count_Num_year = 0 then
-- continue; -- v_indicatorcode没有匹配的格子,跳过这一条数据
new_cd := '0';
else
select year into year_str from (select substr(i.v_periodid,1,4) as year,i.* from Tsk_IndicatorSheet i
where i.v_indicatorcode = new_block
order by year desc) where rownum = 1;
select v.v_bookname||'!'||(select e.enchar from t_numTOEnChar e where e.num = dz.i_col)
||dz.i_row into new_cd from Tsk_IndicatorSheet dz
left join tsk_sheet_info v -- 查往年数据用这个表
on dz.v_taskcode = v.V_TASKCODE
and dz.v_bookcode = v.v_bookcode
where dz.v_periodid = year_str -- 之前年
and dz.i_booklevel = 1
and dz.v_taskcode = PARAM_V_TASKCODE -- 之前年
and dz.v_indicatorcode = new_block
group by dz.v_periodid,v.v_bookname,dz.i_col,dz.i_row,v_indicatorcode;
year_str := '_last_'||year_str;
end if;
end if;
elsif oneBlock.column_value like '%=%' then
block_FWBM := oneBlock.column_value;
end if;
end loop;
if new_cd is not null then
/* dbms_output.put_line('替换前----'||new_block);
dbms_output.put_line('替换后----'||new_cd);
-- 没有FWBM 括号也不要了
update Tsk_SheetFormulas set qif = replace(qif,'DQJF('||substr(oneRecord.column_value,1,instr(oneRecord.column_value,')')), new_cd)
where n_formulaid = item.n_formulaid
and t_formula not like '%FWBM=%';*/
-- 否则保留 DQJF
if block_FWBM is null then
-- dbms_output.put_line('block_FWBM is null----');
update Tsk_SheetFormulas set qif = replace(qif,'DQJF('||substr(oneRecord.column_value,1,instr(oneRecord.column_value,')')-1), '('||new_cd)
where n_formulaid = item.n_formulaid
and t_formula like '%FWBM=%';
else
-- dbms_output.put_line('block_FWBM is not null----'||block_FWBM);
update Tsk_SheetFormulas set qif = replace(qif,'('||substr(oneRecord.column_value,1,instr(oneRecord.column_value,')')-1), '('||block_FWBM||',0,'||new_cd)
where n_formulaid = item.n_formulaid
and t_formula like '%FWBM=%';
end if;
new_cd := null;
end if;
end loop;
end loop;
end split_DQJF;
procedure split_QJF(INPUT_TYPE in varchar2,INPUT_NAME in varchar2) as
PARAM_V_BOOKCODE varchar2(20); -- v_bookcode
PARAM_V_TASKCODE varchar2(20); -- v_TASKcode
new_cd varchar2(1000);
new_block varchar2(100);
indicator_year varchar2(10);
begin
select case when v_bookname = 'IB' then 'ZJS001FJNB' else s.v_taskcode end,s.v_bookcode into PARAM_V_TASKCODE,PARAM_V_BOOKCODE from tsk_v_doc_2023 s
where v_bookname = INPUT_NAME;
for item in (
select T_formula,split(qif,'QJF(') as sp,n_formulaid from Tsk_SheetFormulas f
where f.v_taskcode = PARAM_V_TASKCODE
and i_formulatype = INPUT_TYPE
and v_periodid = '2023'
and v_bookcode = PARAM_V_BOOKCODE
and t_formula like '%QJF(%'
and t_formula not like '%DQJF(%'
--and n_formulaid in ('1076819')
)loop
for oneRecord in ( -- 循环split拆分的 DTF式子
select * from table(item.sp)
)loop
if oneRecord.column_value is null or instr(oneRecord.column_value,'_') = 0 then
continue;
end if ;
-- dbms_output.put_line('oneRecord------'||oneRecord.column_value);
for oneBlock in ( -- 循环 一个DTF 找到 v_indicatorcode 然后替换为 我们的坐标
select rownum rn,a.* from table(split(oneRecord.column_value,',')) a
)
loop
-- dbms_output.put_line('oneBlock.column_value------'||oneBlock.column_value||'::'||oneBlock.rn);
if oneBlock.column_value = '(' then
continue;
elsif instr(oneBlock.column_value,'_') = 1 then ----'||oneBlock.column_value);
if oneBlock.column_value like '%)%' then -- 没有单位括号结尾
select substr(oneBlock.column_value,1,instr(oneBlock.column_value,')')-1) into new_block from dual;
else -- 直接用
select oneBlock.column_value into new_block from dual;
end if;
select v.v_bookname||'!'||(select e.enchar from t_numTOEnChar e where e.num = dz.i_col)
||dz.i_row into new_cd from Tsk_IndicatorSheet dz
left join tsk_sheet_info v -- 查往年数据用这个表
on dz.v_taskcode = v.V_TASKCODE
and dz.v_bookcode = v.v_bookcode
where dz.v_periodid = to_char(indicator_year+2023) -- 之前年
and dz.i_booklevel = 1
and dz.v_taskcode = PARAM_V_TASKCODE -- 之前年
and dz.v_indicatorcode = new_block
group by dz.v_periodid,v.v_bookname,dz.i_col,dz.i_row,v_indicatorcode;
else
indicator_year := oneBlock.column_value;
end if;
if indicator_year is not null and new_cd is not null then
exit;
end if ;
end loop;
if indicator_year = '0' then -- 当年的
null;
elsif indicator_year = '-1' then --上年度,需要根据表样对照关系查对应年的坐标
new_cd := 'getDataYear('||new_cd||'##-1)';-- 用##代替逗号,防止三元表达解析异常
elsif indicator_year is not null then --其他年度,需要根据表样对照关系查对应年的坐标
new_cd := indicator_year||'特殊处理';
end if;
-- dbms_output.put_line('替换前-----QJF('||indicator_year||','||new_block||')' );
-- dbms_output.put_line('替换后-----'||new_cd );
update Tsk_SheetFormulas set qif = replace(qif
,'QJF('||indicator_year||','||new_block||')'
,new_cd)
where v_periodid = '2023' and n_formulaid = item.n_formulaid;
indicator_year:= null;
new_cd:= null;
end loop;
end loop;
end split_QJF;
--------------------------------- [ FormulaType=1 公式迁移过程 ] -------------------------------
procedure PROC_type1(INPUT_NAME in varchar2) as
/*
* 处理i_FormulaType=1 审核公式。
*/
PARAM_V_BOOKCODE varchar2(20); -- v_bookcode
PARAM_V_TASKCODE varchar2(20); -- v_TASKcode
begin
select case when v_bookname = 'IB' then 'ZJS001FJNB' else s.v_taskcode end,s.v_bookcode into PARAM_V_TASKCODE,PARAM_V_BOOKCODE from tsk_v_doc_2023 s
where v_bookname = INPUT_NAME;
update Tsk_SheetFormulas f set f.qif = t_formula
where v_periodid = '2023'
and i_formulatype = '1'
and f.v_taskcode = PARAM_V_TASKCODE and v_bookcode = PARAM_V_BOOKCODE;
--第一步 : 替换QIF 中的坐标
PKG_TSK_Formulas_2_DZCOL_23.split_DQJF('1',INPUT_NAME);
PKG_TSK_Formulas_2_DZCOL_23.split_QJF('1',INPUT_NAME);
--第二步 : 设置 SSCOPE_CODE
PKG_TSK_Formulas_2_DZCOL_23.PROC_replaceSScope('1',INPUT_NAME,'SSCOPE_CODE'); -- 翻译类型1的SSCOPE到SSCOPE_CODE
--第三步 : 如果qif 里有IF 更新为三元表达式
PKG_TSK_Formulas_2_DZCOL_23.update_IF_to_ternary('1');
--把之前的##替回来
update tsk_sheetformulas f set qif = replace(qif,'##',',')
where f.v_periodid = '2023'
and f.i_formulatype = 1
and qif like '%##%' ;
--第四步 : 逐表插入
-- 这里不删, 在外面一起删
insert into gfa_datacheckset_ccq23 (DATA_ID, SET_YEAR, CHECK_NAME
, FORMULA_CON, CHECK_TYPE, EXCTYPE, USERTYPE, ERRORDESC, IS_USING
, ORDERID, LINKCONFIG, CHECKSQL, BEANURL, CREATE_TIME, IS_DELETED, UPDATE_TIME
, MOF_DIV_CODE, CREATE_USER, UPDATE_USER, REPORT_TABLE, CHECK_CODE, CHECK_CODE_JQ, CHECK_CODE_JQ_2, IS_CHECK, REMARK, IS_DESCRIPTION, LOC_ITEMS, CDS, SSCOPE, EXEC_FORMULA, FORMULA_TYPE, FORMULA_TYPE_DZ)
select aa.n_formulaid, '2023', aa.v_bookname||'表：【“'||replace(replace(r.report_item_name,' '),' ')||'”（'||aa.cd_2||'）'||aa.t_formuladescription ||'】'
,aa.qif, 0, (case when aa.v_bookname like 'JB%' then 5001 when aa.v_bookname like 'L%' then 3001 when aa.v_bookname like 'F%' then 7001 else 4001 end), 3, aa.t_formuladescription, 1,
(case when aa.v_bookname like 'JB%' then '5001' when aa.v_bookname like 'L%' then '3001' when aa.v_bookname like 'F%' then '7001' else '4001' end)
||rpad(regexp_substr(replace(aa.v_bookname,'-'),'\d+'),3,'0')
||lpad(i_row,4,'0')||lpad(i_col,4,'0') as orderid
, '', '', ''
, to_date('03-01-2024 16:40:56', 'dd-mm-yyyy hh24:mi:ss'), 2, to_date('03-01-2024 16:40:56', 'dd-mm-yyyy hh24:mi:ss')
, '460000000', 'CCQ_type_1', '1', aa.v_bookname, case when aa.v_bookname like 'JB%' then 'ZJSJB001FJNB' else 'ZJS001FJNB' end, '', '', '', '', null
, replace(aa.v_bookname,'-','_')||'!'||aa.cd, null, aa.sscope_code, aa.qif, 0, '1'
from ( select v.v_bookname,(select e.enchar from t_numTOEnChar e where e.num = f.i_col)||f.i_row as cd,
f.i_row||'行'||f.i_col||'栏' as cd_2,
f.* from tsk_sheetformulas f
left join tsk_v_doc_2023 v
on f.v_taskcode = v.V_TASKCODE
and f.v_bookcode = v.v_bookcode
where f.v_periodid = '2023'
and f.i_formulatype = 1
and v.v_bookname = INPUT_NAME
and t_formula not like '%,CZSZ%' -- 先不处理旬月报
)aa
left join gfa_rptfixedconfig_ccq23 r
on r.set_year = aa.v_periodid and r.report_code = aa.V_BOOKNAME
and r.cd = aa.cd;
end PROC_type1;
procedure addTableCode_thisTable(thisTable in varchar2) as
/* 公式中本表坐标也带上 表名前缀 如 'D9==DQJF(DW_LEVEL=08,0,D9)-J24!C5-J24'
*/
inputStr varchar2(4000);
cnt_other number;
cnt_this number;
cd varchar2(400);
cd_other varchar2(400);
newCD varchar2(400);
temp varchar2(4000);
thisTable_temp varchar2(2000);
begin
for item in (
select * from gfa_rptfixedconfig_ccq23 t where set_year = 2023
and data_type = 0
and exec_formula is not null
and report_code = thisTable
)loop
inputStr := item.exec_formula;
-- J01_2 特殊处理
inputStr := replace(inputStr,'J01_2','J9999');
temp := inputStr;
thisTable_temp := regexp_replace(thisTable,'J01-2','J9999');
-- 先把外表的全改成小写,后续再改回来
select regexp_count(inputStr,'[A-Z]+[0-9]+![A-Z]+[0-9]+') into cnt_other from dual;
for i in 1..cnt_other loop
select regexp_substr(temp,'[A-Z]+[0-9]+![A-Z]+[0-9]+',1,1) into cd_other from dual;
temp := regexp_replace(temp,cd_other,lower(cd_other));
--dbms_output.put_line('替换第'||i||'次完之后的temp-----'||temp);
end loop;
-- 替换本表的
select regexp_count(inputStr,'[A-Z]+[0-9]+')- 2*regexp_count(inputStr,'[A-Z]+[0-9]+![A-Z]+[0-9]+') into cnt_this from dual;
for i in 1..cnt_this loop
--dbms_output.put_line('第i----------------'||i);
select regexp_substr(temp,'[A-Z]+[0-9]+',1,1) into cd from dual;
--dbms_output.put_line('拿到截取的cd-----'||cd);
newCD := lower(thisTable_temp)||'!'||lower(cd);
--dbms_output.put_line('拿到要替换的起始位置 instr(temp,cd)-----'||instr(temp,cd));
temp := regexp_replace(temp,cd,newCD,instr(temp,cd),1);
--dbms_output.put_line('替换第'||i||'次完之后的temp-----'||temp);
end loop;
-- 把改成小写的坐标改回来
select regexp_count(temp,'[a-z]+[0-9]+![a-z]+[0-9]+') into cnt_other from dual;
for i in 1..cnt_other loop
select regexp_substr(temp,'[a-z]+[0-9]+![a-z]+[0-9]+',1,1) into cd_other from dual;
temp := regexp_replace(temp,cd_other,upper(cd_other));
end loop;
--dbms_output.put_line(item.exec_formula||'--------'||temp);
-- JBFB 的特殊处理一下
select regexp_count(temp,'jbfb![a-z]+[0-9]+') into cnt_other from dual;
for i in 1..cnt_other loop
select regexp_substr(temp,'jbfb![a-z]+[0-9]+',1,1) into cd_other from dual;
--dbms_output.put_line('拿到截取的 cd_other-----'||cd_other);
temp := regexp_replace(temp,cd_other,upper(cd_other));
end loop;
temp := replace(temp,'J9999','J01_2');
update gfa_rptfixedconfig_ccq23 set exec_formula = temp
where set_year = 2023
and data_type = 0
and report_code = thisTable
and cd = item.cd
and exec_formula not like '%DQJF%' ;
end loop;
update gfa_rptfixedconfig_ccq23 t set t.exec_formula
= replace(exec_formula,'!','zz')
where t.set_year=2023 and t.report_code = thisTable and exec_formula is not null;
end addTableCode_thisTable;
procedure replaceExecFormula_thisTable(thisTable in varchar2) as
/* 公式中本表坐标也带上 表名前缀 如 'D9==DQJF(DW_LEVEL=08,0,D9)-J24!C5-J24'
*/
inputStr varchar2(4000);
cnt_other number;
cnt_this number;
cd varchar2(400);
cd_other varchar2(400);
newCD varchar2(400);
temp varchar2(4000);
thisTable_temp varchar2(400);
begin
for item in (
select exec_formula,data_id from gfa_datacheckset_ccq23
where set_year = 2023 and formula_type_dz = 1
and report_table = thisTable
)loop
inputStr := item.exec_formula;
-- J01_2 特殊处理
inputStr := replace(inputStr,'J01_2','J9999');
temp := inputStr;
thisTable_temp := regexp_replace(thisTable,'J01-2','J9999');
-- 先把外表的全改成小写,后续再改回来
select regexp_count(inputStr,'[A-Z]+[0-9]+![A-Z]+[0-9]+') into cnt_other from dual;
for i in 1..cnt_other loop
select regexp_substr(temp,'[A-Z]+[0-9]+![A-Z]+[0-9]+',1,1) into cd_other from dual;
temp := regexp_replace(temp,cd_other,lower(cd_other));
--dbms_output.put_line('替换第'||i||'次完之后的temp-----'||temp);
end loop;
-- 替换本表的
select regexp_count(inputStr,'[A-Z]+[0-9]+')- 2*regexp_count(inputStr,'[A-Z]+[0-9]+![A-Z]+[0-9]+') into cnt_this from dual;
for i in 1..cnt_this loop
--dbms_output.put_line('第i----------------'||i);
select regexp_substr(temp,'[A-Z]+[0-9]+',1,1) into cd from dual;
--dbms_output.put_line('拿到截取的cd-----'||cd);
newCD := lower(thisTable_temp)||'!'||lower(cd);
--dbms_output.put_line('拿到要替换的起始位置 instr(temp,cd)-----'||instr(temp,cd));
temp := regexp_replace(temp,cd,newCD,instr(temp,cd),1);
--dbms_output.put_line('替换第'||i||'次完之后的temp-----'||temp);
end loop;
-- 把改成小写的坐标改回来
select regexp_count(temp,'[a-z]+[0-9]+![a-z]+[0-9]+') into cnt_other from dual;
for i in 1..cnt_other loop
select regexp_substr(temp,'[a-z]+[0-9]+![a-z]+[0-9]+',1,1) into cd_other from dual;
temp := regexp_replace(temp,cd_other,upper(cd_other));
end loop;
--dbms_output.put_line(item.exec_formula||'--------'||temp);
-- JBFB 的特殊处理一下
select regexp_count(temp,'jbfb![a-z]+[0-9]+') into cnt_other from dual;
for i in 1..cnt_other loop
select regexp_substr(temp,'jbfb![a-z]+[0-9]+',1,1) into cd_other from dual;
--dbms_output.put_line('拿到截取的 cd_other-----'||cd_other);
temp := regexp_replace(temp,cd_other,upper(cd_other));
end loop;
temp := replace(temp,'J9999','J01_2');
update gfa_datacheckset_ccq23 set exec_formula = temp
where set_year = 2023 and formula_type_dz = 1
and data_id = item.data_id;
end loop;
update gfa_datacheckset_ccq23 t set t.exec_formula
= replace(exec_formula,'!','zz')
where t.set_year=2023 and formula_type_dz = 1 and t.report_table = thisTable;
end replaceExecFormula_thisTable;
/* 替换 ExecFormula '************************************************ */
procedure PROC_deal_type1 as
/*
* 处理i_FormulaType=1 审核公式。
*/
begin
-- 先删
delete from gfa_datacheckset_ccq23 where set_year = 2023 and formula_type_dz = 1;
for item in (
select v.v_bookname from tsk_v_doc_2023 v
)loop
dbms_output.put_line('item.v_bookname----'||item.v_bookname);
PKG_TSK_Formulas_2_DZCOL_23.PROC_type1(item.v_bookname);
-- 这个表不应该有BB列, 如果有 则替换为 B
update gfa_datacheckset_ccq23 t set exec_formula
= replace(exec_formula,'BB','B')
where set_year = 2023
AND t.report_table = 'J04' ;
--将sum() 函数 展开 截取 SUM开头到第一个右括号 进行替换
update gfa_datacheckset_ccq23 t set exec_formula
= Excel_Sum_to_individual_cols(replace(exec_formula,'sum','SUM'),item.v_bookname)
where set_year = 2023
and upper(exec_formula) like '%SUM%'
AND t.report_table = item.v_bookname ;
--Max_And_Min
PKG_TSK_Formulas_2_DZCOL_23.split_Max_And_Min;
-- 处理所有表 ''J01-2'' 为 J01_2
update gfa_datacheckset_ccq23 t set exec_formula = replace(exec_formula,'''J01-2''','J01_2')
where t.set_year = 2023
AND t.report_table = item.v_bookname
AND t.exec_formula like '%''J01-2''%';
update gfa_datacheckset_ccq23 t set exec_formula = replace(exec_formula,'J01-2','J01_2')
where t.set_year = 2023
AND t.report_table = item.v_bookname
AND t.exec_formula like '%J01-2%';
--去掉 $ exec_formula中不应该有
update gfa_datacheckset_ccq23 t set exec_formula = replace(exec_formula,'$')
where t.set_year = 2023
AND t.report_table = item.v_bookname
AND t.exec_formula like '%$%';
-- 公式中本表坐标也带上 表名前缀
PKG_TSK_Formulas_2_DZCOL_23.replaceExecFormula_thisTable(item.v_bookname);
end loop;
-- 处理 <>
update gfa_datacheckset_ccq23 t set exec_formula = replace(exec_formula,'<>','!=')
where t.set_year = 2023 AND t.exec_formula like '%<>%';
-- 设置 formula_con
update gfa_datacheckset_ccq23 set formula_con = 'QIF R_KEY='''||loc_items||''' QTHEN '||replace(exec_formula,'zz','!')
where set_year = 2023
and sscope is null;
update gfa_datacheckset_ccq23 set formula_con = 'QIF R_KEY='''||loc_items||''' AND '||SSCOPE||' QTHEN '||replace(exec_formula,'zz','!')
where set_year = 2023
and sscope is not null;
-- 处理本表 CDS
PKG_TSK_Formulas_2_DZCOL_23.set_ALL_CDS('gfa_datacheckset_ccq23');
--单独处理
-- 这三个 有AND() 函数, 手动处理为 ( && )
-- 出 .sql脚本&& 要改成 '||'&'||''||'&'||'
update gfa_datacheckset_ccq23 set EXEC_FORMULA = 'L03zzE61==(L03zzE6==0?L03zzE61:(L03zzE61>=L07zzT109+L07zzT110+L07zzT122-L03zzE63 L03zzE61<=L07zzT96+L07zzT109+L07zzT110+L07zzT122-L03zzE63)?L03zzE61:(L03zzE61+1))'
where SET_YEAR = 2023 and formula_con like '%L03!E61==(L03!E6==0?%';
update gfa_datacheckset_ccq23 set EXEC_FORMULA = 'L03zzC61==(L03zzC61==0?L03zzC61:(L03zzC61>=L07zzC109+L07zzC110+L07zzC122-L03zzC63 L03zzC61<=L07zzC96+L07zzC109+L07zzC110+L07zzC122-L03zzC63)?L03zzC61:(L03zzC61+1))'
where SET_YEAR = 2023 and formula_con like '%L03!C61==(L03!C61==0?%';
update gfa_datacheckset_ccq23 set EXEC_FORMULA = 'L03zzC61==(L03zzE6==0?(L03zzC6<>0?(L03zzC61>=L07zzT109+L07zzT110+L07zzT122-L03zzC63 L03zzC61<=L07zzT96+L07zzT109+L07zzT110+L07zzT122-L03zzC63)?L03zzC61:(L03zzC61+1)):0):L03zzC61)'
where SET_YEAR = 2023 and formula_con like '%L03!C61==(L03!E6==0?%';
-- MOD 就一条 单独设置吧
update gfa_datacheckset_ccq23 t set exec_formula
= 'L23zzB98==L23zzB98+(L23zzB98 % 10000)'
where set_year = 2023
and exec_formula = 'L23zzB98==L23zzB98+MOD(L23zzB98,10000)';
-- 这些坐标大正范围写的不对 应取 (省级,04)|(省本级,05)
update gfa_datacheckset_ccq23 t set t.sscope= 'DW_LEVEL =~ [''04'',''05'']'
where t.set_year = 2023 and loc_items in ('J08!C10','J08!C11','J08!C13','J08!C14','J08!C16','J08!C17','J08!C20','J08!C21','J08!C22','J08!C23','J08!C24','J08!C26','J08!C27','J08!C40','J08!C5','J08!C6','J08!C7','J08!C8','J08!C9','J08!J10','J08!J11','J08!J12','J08!J13','J08!J14','J08!J15','J08!J16','J08!J17','J08!J18','J08!J19','J08!J20','J08!J22','J08!J23','J08!J24','J08!J25','J08!J27','J08!J28','J08!J40','J08!J5','J08!J7','J08!J8','J08!J9','J17!B41','J17!D41','J17!I41','J17!K41','J17!L41','J21!C41','J21!C5','J21!C6','J21!C7','J21!J41','J21!J6','J21!J5','J21!J7','J21!J8','J08!J21','J08!C25','J08!J26','J21!C9','J21!C8','J08!J6');
update gfa_datacheckset_ccq23 t set formula_con = replace(formula_con,'FlexRow_L=1','DW_LEVEL IN [''04'',''05'']')
where t.set_year = 2023 and loc_items in ('J08!C10','J08!C11','J08!C13','J08!C14','J08!C16','J08!C17','J08!C20','J08!C21','J08!C22','J08!C23','J08!C24','J08!C26','J08!C27','J08!C40','J08!C5','J08!C6','J08!C7','J08!C8','J08!C9','J08!J10','J08!J11','J08!J12','J08!J13','J08!J14','J08!J15','J08!J16','J08!J17','J08!J18','J08!J19','J08!J20','J08!J22','J08!J23','J08!J24','J08!J25','J08!J27','J08!J28','J08!J40','J08!J5','J08!J7','J08!J8','J08!J9','J17!B41','J17!D41','J17!I41','J17!K41','J17!L41','J21!C41','J21!C5','J21!C6','J21!C7','J21!J41','J21!J6','J21!J5','J21!J7','J21!J8','J08!J21','J08!C25','J08!J26','J21!C9','J21!C8','J08!J6');
update gfa_datacheckset_ccq23 t set formula_con = replace(formula_con,'FlexRow_L=2','DW_LEVEL IN [''04'',''05'']')
where t.set_year = 2023 and loc_items in ('J08!C10','J08!C11','J08!C13','J08!C14','J08!C16','J08!C17','J08!C20','J08!C21','J08!C22','J08!C23','J08!C24','J08!C26','J08!C27','J08!C40','J08!C5','J08!C6','J08!C7','J08!C8','J08!C9','J08!J10','J08!J11','J08!J12','J08!J13','J08!J14','J08!J15','J08!J16','J08!J17','J08!J18','J08!J19','J08!J20','J08!J22','J08!J23','J08!J24','J08!J25','J08!J27','J08!J28','J08!J40','J08!J5','J08!J7','J08!J8','J08!J9','J17!B41','J17!D41','J17!I41','J17!K41','J17!L41','J21!C41','J21!C5','J21!C6','J21!C7','J21!J41','J21!J6','J21!J5','J21!J7','J21!J8','J08!J21','J08!C25','J08!J26','J21!C9','J21!C8','J08!J6');
update gfa_datacheckset_ccq23 t set formula_con = replace(formula_con,'FlexRow_R=1','DW_LEVEL IN [''04'',''05'']')
where t.set_year = 2023 and loc_items in ('J08!C10','J08!C11','J08!C13','J08!C14','J08!C16','J08!C17','J08!C20','J08!C21','J08!C22','J08!C23','J08!C24','J08!C26','J08!C27','J08!C40','J08!C5','J08!C6','J08!C7','J08!C8','J08!C9','J08!J10','J08!J11','J08!J12','J08!J13','J08!J14','J08!J15','J08!J16','J08!J17','J08!J18','J08!J19','J08!J20','J08!J22','J08!J23','J08!J24','J08!J25','J08!J27','J08!J28','J08!J40','J08!J5','J08!J7','J08!J8','J08!J9','J17!B41','J17!D41','J17!I41','J17!K41','J17!L41','J21!C41','J21!C5','J21!C6','J21!C7','J21!J41','J21!J6','J21!J5','J21!J7','J21!J8','J08!J21','J08!C25','J08!J26','J21!C9','J21!C8','J08!J6');
update gfa_datacheckset_ccq23 t set formula_con = replace(formula_con,'FlexRow_R=2','DW_LEVEL IN [''04'',''05'']')
where t.set_year = 2023 and loc_items in ('J08!C10','J08!C11','J08!C13','J08!C14','J08!C16','J08!C17','J08!C20','J08!C21','J08!C22','J08!C23','J08!C24','J08!C26','J08!C27','J08!C40','J08!C5','J08!C6','J08!C7','J08!C8','J08!C9','J08!J10','J08!J11','J08!J12','J08!J13','J08!J14','J08!J15','J08!J16','J08!J17','J08!J18','J08!J19','J08!J20','J08!J22','J08!J23','J08!J24','J08!J25','J08!J27','J08!J28','J08!J40','J08!J5','J08!J7','J08!J8','J08!J9','J17!B41','J17!D41','J17!I41','J17!K41','J17!L41','J21!C41','J21!C5','J21!C6','J21!C7','J21!J41','J21!J6','J21!J5','J21!J7','J21!J8','J08!J21','J08!C25','J08!J26','J21!C9','J21!C8','J08!J6');
update gfa_datacheckset_ccq23 t set t.cds = t.cds||';DW_LEVEL'
where t.set_year='2023' and t.sscope like '%DW%' and t.cds not like '%DW%';
--
end PROC_deal_type1;
procedure set_ALL_CDS(update_tableName in varchar2) as
/*
* 处理[update_tableName] 表 中cds字段
*/
cd varchar2(4000);
newcds varchar2(4000);
counts number;
List_DW_CDS varchar2(1000);
begin
List_DW_CDS := 'DW_LEVEL,DIV_CLASS,FlexRow_R,FlexRow_L,IS_DIRECT_COUNTY,IS_DIRECT_PLAN,NATIONAL_AUTO_SET,IS_COUNTY_AUTO,IS_POVERTY,MOF_DIV_CODE';
if update_tableName = 'gfa_rptfixedconfig_ccq23' then
for item in (
select exec_formula,report_code,cd from gfa_rptfixedconfig_ccq23
where set_year = 2023
and data_type = 0
and exec_formula is not null
)loop
newcds := '';
select regexp_count(item.exec_formula,'[A-Z]+[0-9]+zz[A-Z]+[0-9]+') into counts from dual;
if counts is null then
continue;
end if;
for i in 1..counts loop
--拿到截取的坐标
select regexp_substr(item.exec_formula,'[A-Z]+[0-9]+zz[A-Z]+[0-9]+',1,i) into cd from dual;
-- 如果前后都没有感叹号 则需要加前缀
newcds := newcds||cd||';';
end loop;
update gfa_rptfixedconfig_ccq23 t set t.cds = newcds
where set_year = 2023
and t.report_code = item.report_code
and t.cd = item.cd
and data_type = 0;
--dbms_output.put_line('item.cd-------'||item.report_code||'!'||item.cd||'-----newcds---'||newcds);
end loop;
-- sscope里的变量也要拼上
/*update gfa_rptfixedconfig_ccq23 t
set t.cds = t.cds|| ';DW_LEVEL'
where T.SET_YEAR = 2023
--and t.report_table in (select v_bookname from tsk_v_doc_2023 v where v.booktype not like '4%')
AND t.exec_formula LIKE '%DW_LEVEL%' and t.cds not LIKE '%DW_LEVEL%' ;*/
for DW_CDS in (
select * from table(split(List_DW_CDS,','))
) loop
update gfa_rptfixedconfig_ccq23 t
set t.cds = t.cds||DW_CDS.COLUMN_VALUE|| ';'
where T.SET_YEAR = 2023
AND t.exec_formula LIKE '%'||DW_CDS.COLUMN_VALUE||'%' and t.cds not LIKE '%'||DW_CDS.COLUMN_VALUE||'%' ;
end loop;
elsif update_tableName = 'gfa_datacheckset_ccq23' then
-- gfa_datacheckset_ccq23 这个表CDS 全刷 不区分类型
for item in (
select exec_formula,data_id from gfa_datacheckset_ccq23
where set_year = 2023
and exec_formula is not null
-- and report_table = 'J08'
)loop
newcds := '';
select regexp_count(item.exec_formula,'[A-Z]+[0-9]+zz[A-Z]+[0-9]+') into counts from dual;
for i in 1..counts loop
--拿到截取的坐标
select regexp_substr(item.exec_formula,'[A-Z]+[0-9]+zz[A-Z]+[0-9]+',1,i) into cd from dual;
newcds := newcds||cd||';';
end loop;
update gfa_datacheckset_ccq23 t set t.cds = newcds
where set_year = 2023
and data_id = item.data_id;
-- dbms_output.put_line(item.exec_formula||'--------'||inputStr);
end loop;
-- sscope里的变量也要拼上
for DW_CDS in (
select * from table(split(List_DW_CDS,','))
) loop
update gfa_datacheckset_ccq23 t
set t.cds = t.cds||DW_CDS.COLUMN_VALUE||';'
where T.SET_YEAR = 2023
AND t.sscope LIKE '%'||DW_CDS.COLUMN_VALUE||'%' and t.cds not LIKE '%'||DW_CDS.COLUMN_VALUE||'%' ;
end loop;
else
RAISE_APPLICATION_ERROR(-20123,'输入参数['||update_tableName||']不正确');
end if;
end set_ALL_CDS;
procedure PROC_deal_type3 as
/*
* 处理i_FormulaType=3 期间取数公式。
*/
begin
delete from gfa_infreport_ccq23 t where t.set_year = 2023 and INFCON_TYPE_CODE = 'GET_PERIOD_DATA';
insert into gfa_infreport_ccq23 (INFREPORT_ID, SET_YEAR, INFCON_TYPE_CODE, REPORT_CODE, REPORT_ITEM_CODE, FIELD_CODE, COM_KEY, CD, C_FORMULA, C_USING, DW_PROPERTIES, DW_CODE, MOF_DIV_CODE, UPDATE_TIME, IS_DELETED, CREATE_TIME, CREATE_USER, UPDATE_USER, DETAILFCODE, ELE_CODE, CDS, EXEC_FORMULA, FORMULA_DESCRIPTION, REPORT_ITEM_NAME)
select f.n_formulaid , '2023', 'GET_PERIOD_DATA', v.v_bookname , 'REPORT_ITEM_CODE', 'FIELD_CODE', 'COM_KEY', (select e.enchar from t_numTOEnChar e where e.num = f.i_col)||f.i_row, f.t_formula, '0', '997', '', '460000000', sysdate, 2, sysdate, 'CCQ_type_3', '460000000', '', 'ELE_CODE', 'CDS', 'EXEC_FORMULA', f.t_formuladescription, 'REPORT_ITEM_NAME'
from tsk_sheetformulas f
left join tsk_v_doc_2023 v on
f.v_taskcode = v.V_TASKCODE
and f.v_bookcode = v.v_bookcode
where f.v_periodid = 2023 and f.i_formulatype = 3;
--
update gfa_infreport_ccq23 t set (t.report_item_code,t.report_item_name, t.field_code,t.com_key,t.ele_code) =
(select c.report_item_code,c.report_item_name,c.field_code,c.com_key,c.ele_code
from gfa_rptfixedconfig c where t.set_year = c.set_year and t.report_code = c.report_code and c.cd = t.cd)
where t.set_year = 2023 and INFCON_TYPE_CODE = 'GET_PERIOD_DATA';
--
update gfa_infreport_ccq23 t set t.c_formula = replace(t.formula_description,'取自本年财政总决算简表中')
where t.set_year = 2023 and INFCON_TYPE_CODE = 'GET_PERIOD_DATA'
and formula_description like '%取自本年财政总决算简表中%' ;
update gfa_infreport_ccq23 t set t.c_formula = 'getDataYear('||replace(replace(t.formula_description,'QJF(DQJ[N,-1{0}],'),'取自上年财政总决算报表中')||',-1)'
where t.set_year = 2023 and INFCON_TYPE_CODE = 'GET_PERIOD_DATA'
and t.formula_description like '%取自上年财政总决算报表中%' ;
update gfa_infreport_ccq23 t set t.c_formula = replace(replace(t.formula_description,'应与简表'),'数据一致，如不一致请在报表附注中说明出错原因及金额')
where t.set_year = 2023 and INFCON_TYPE_CODE = 'GET_PERIOD_DATA'
and t.formula_description like '%应与简表%数据一致，如不一%' ;
update gfa_infreport_ccq23 t set t.c_formula = replace(t.c_formula,'),',',')
where t.set_year = 2023 and INFCON_TYPE_CODE = 'GET_PERIOD_DATA'
and t.c_formula like '%getDataYear(%' ;
update gfa_infreport_ccq23 t set t.c_formula = substr(t.c_formula,1,instr(c_formula,':')-1)
where t.set_year = 2023 and INFCON_TYPE_CODE = 'GET_PERIOD_DATA'
and t.c_formula like '%！%' ;
update gfa_infreport_ccq23 t set t.exec_formula = replace(t.c_formula,'!','zz')
where t.set_year = 2023 and INFCON_TYPE_CODE = 'GET_PERIOD_DATA';
update gfa_infreport_ccq23 t set t.cds = replace(replace(t.exec_formula,'getDataYear('),',-1)')
where t.set_year = 2023 and INFCON_TYPE_CODE = 'GET_PERIOD_DATA';
end PROC_deal_type3;
procedure PROC_deal_type15 as
/*
* 处理i_FormulaType=15 生成表浮动表 CompareSuperiorLowCheck。
*/
begin
delete from gfa_datacheckset_CCQ23 where set_year = 2023 and formula_type_dz = 15;
insert into gfa_datacheckset_CCQ23 (DATA_ID, SET_YEAR, CHECK_NAME, FORMULA_CON, CHECK_TYPE, EXCTYPE, USERTYPE, ERRORDESC, IS_USING, ORDERID, LINKCONFIG, CHECKSQL, BEANURL, CREATE_TIME, IS_DELETED, UPDATE_TIME, MOF_DIV_CODE, CREATE_USER, UPDATE_USER, REPORT_TABLE, CHECK_CODE, CHECK_CODE_JQ, CHECK_CODE_JQ_2, IS_CHECK, REMARK, IS_DESCRIPTION, LOC_ITEMS, CDS, SSCOPE, EXEC_FORMULA, FORMULA_TYPE, FORMULA_TYPE_DZ, CREATE_MOF_DIV_CODE, SCOPE_TYPE)
select aa.n_formulaid, '2023', aa.v_bookname||'表：【“'||r.report_item_name||'”（'||i_row||'行'||i_col||'栏）'||aa.t_formuladescription ||'】'
, aa.v_bookname||'!'||aa.cd||'=='||aa.t_formula, 0, 4001, 3, aa.t_formuladescription, 1, '40011000000010', '', '', ''
, to_date('14-12-2023 16:40:56', 'dd-mm-yyyy hh24:mi:ss'), 2, to_date('14-12-2023 15:47:44', 'dd-mm-yyyy hh24:mi:ss')
, '460000000', 'CCQ_type_15', '1', aa.v_bookname, 'ZJS001FJNB', '', '', '', '', null
, aa.v_bookname||'!'||aa.cd, aa.v_bookname||'zz'||aa.cd, null, null, 0, '15', '', null
from ( select v.v_bookname,(select e.enchar from t_numTOEnChar e where e.num = f.i_col)||f.i_row as cd,
f.* from tsk_sheetformulas f
left join tsk_v_doc_2023 v
on f.v_taskcode = v.V_TASKCODE
and f.v_bookcode = v.v_bookcode
where f.v_periodid = 2023 and f.i_formulatype = 15
)aa
left join gfa_rptfixedconfig_ccq23 r
on r.report_code = aa.V_BOOKNAME
and r.cd = aa.cd
and r.set_year = aa.v_periodid;
update gfa_datacheckset_CCQ23 t
set exec_formula =
replace(replace(t.formula_con,'LowCheck(','LowCheck('||report_table||'zz'),'!','zz')
where set_year = 2023 and formula_type_dz = 15;
end PROC_deal_type15;
procedure PROC_UPDATE_TO_REALTABLE as
/* 将临时表里处理好的数据 更新/插入到正式表 请谨慎使用*/
begin
RAISE_APPLICATION_ERROR(-20123,'更新/插入到正式表 ? 谨慎使用!');
-- 把gfa_rptfixedconfig_ccq23 搞过去
update gfa_rptfixedconfig t set (t.exec_formula,t.cds) =
(select exec_formula,cds
from gfa_rptfixedconfig_ccq23 n
where t.set_year = n.set_year
and t.report_code = n.report_code
and t.cd = n.cd
and t.data_type = n.data_type
)
where set_year = 2023
and data_type = 0
and exec_formula is null;
--
delete from gfa_datacheckset where set_year = 2023 and formula_type_dz in (1,5,15,16);
insert into gfa_datacheckset (DATA_ID, SET_YEAR, CHECK_NAME, FORMULA_CON, CHECK_TYPE, EXCTYPE, USERTYPE, ERRORDESC, IS_USING, ORDERID, LINKCONFIG, CHECKSQL, BEANURL, CREATE_TIME, IS_DELETED, UPDATE_TIME, MOF_DIV_CODE, CREATE_USER, UPDATE_USER, REPORT_TABLE, CHECK_CODE, CHECK_CODE_JQ, CHECK_CODE_JQ_2, IS_CHECK, REMARK, LOC_ITEMS, CDS, SSCOPE, EXEC_FORMULA, FORMULA_TYPE, FORMULA_TYPE_DZ)
select DATA_ID, SET_YEAR, CHECK_NAME, FORMULA_CON, CHECK_TYPE, EXCTYPE, USERTYPE, ERRORDESC, IS_USING, ORDERID, LINKCONFIG, CHECKSQL, BEANURL, CREATE_TIME, IS_DELETED, UPDATE_TIME, MOF_DIV_CODE, CREATE_USER, UPDATE_USER, REPORT_TABLE, CHECK_CODE, CHECK_CODE_JQ, CHECK_CODE_JQ_2, IS_CHECK, REMARK, LOC_ITEMS, CDS, SSCOPE, EXEC_FORMULA, FORMULA_TYPE, FORMULA_TYPE_DZ
from gfa_datacheckset_CCQ23 where set_year = 2023 and formula_type_dz in (1,5,15,16);
--
delete from gfa_infreport t where set_year = 2023 and INFCON_TYPE_CODE = 'GET_PERIOD_DATA';
/*insert into gfa_infreport (INFREPORT_ID, SET_YEAR, INFCON_TYPE_CODE, REPORT_CODE, REPORT_ITEM_CODE, FIELD_CODE, COM_KEY, CD, C_FORMULA, C_USING, DW_PROPERTIES, DW_CODE, MOF_DIV_CODE, UPDATE_TIME, IS_DELETED, CREATE_TIME, CREATE_USER, UPDATE_USER, DETAILFCODE, ELE_CODE, CDS, EXEC_FORMULA, FORMULA_DESCRIPTION, REPORT_ITEM_NAME)
select INFREPORT_ID, SET_YEAR, INFCON_TYPE_CODE, REPORT_CODE, REPORT_ITEM_CODE, FIELD_CODE, COM_KEY, CD, C_FORMULA, C_USING, DW_PROPERTIES, DW_CODE, MOF_DIV_CODE, UPDATE_TIME, IS_DELETED, CREATE_TIME, CREATE_USER, UPDATE_USER, DETAILFCODE, ELE_CODE, CDS, EXEC_FORMULA, FORMULA_DESCRIPTION, REPORT_ITEM_NAME
from gfa_infreport_ccq23 where set_year = 2023 and INFCON_TYPE_CODE = 'GET_PERIOD_DATA';
*/
end PROC_UPDATE_TO_REALTABLE;
end PKG_TSK_Formulas_2_DZCOL_23;
/
2958
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
