https://pastein.ru/t/Zxf
Загрузка данных
-- DROP FUNCTION smev.f3_load_sfr_register(text);
CREATE OR REPLACE FUNCTION smev.f3_load_sfr_register(sid text)
RETURNS integer
LANGUAGE plpgsql
AS $function$
declare
smev_processes record;
loaders record;
loader_var text;
v_var_name text;
v_var_value text;
etl_id_key bigint;
v_multi_load boolean;
query_text text;
process_j json;
queryDataSourceOptions text;
file_loader_data_j json; -- json of data{} object for file-loader call
process_result_stack json[];
metadata text[];
file_loader_metadata text[];
extracted_xmlns text[];
smev_file_type text;
file_type_trustgate text;
smev_file_version int;
xi int;
yi int;
loaded_file_id int;
messages_count int;
i_next int4;
i_json jsonb;
postProcessRun boolean;
smev_file record;
file_get_code int;
v_response_content bytea;
curl_response bytea;
curl_response_code int;
v_cookies text;
v_base varchar(255);
v_smev_re varchar(255);
v_smev_ve varchar(255);
v_file_j json;
v_file_id int4;
v_user_id int4;
v_datasource_code varchar(50);
floader smev.ctx_floader_ex2; -- foreach variable
v_datasources smev.ctx_floader_ex2[];
v_datasource smev.ctx_floader_ex2;
v_context_vars smev.ctx_var[]; -- Variables array for file-loader context (if loader use it)
v_version_ex int;
v_oper_code varchar(50);
v_response_result json;
p_type text;
-- var for processing status message
v_status_message varchar(50);
v_robot text;
-- next vars for УПП document:
v_ver_name text;
v_verified_name text;
v_ver_bytes bytea;
v_ver_destination_id int;
v_upp_doctype text;
-- next vars for exception case:
v_processing_files robot.input_floader_info_ex[];
v_process_file robot.input_floader_info_ex;
v_local_bool boolean;
-- Try catch vars:
err_code text;
msg_text text;
exc_context text;
begin
begin -- exception
v_robot:= 'aa_robot';
v_processing_files := null;
-- perform robot.log_message('работа', 'Вход в smev.f3_load_sfr_register', sid);
-- select value into v_base from robot.params p where p.name = 'server_path'; (old)
-- next select - without additional check (as we there, robot.params are actual):
select id into v_user_id
from security."USER" u
where u.username = v_robot;
--(select value from robot.params p where p.name = 'login');
select web_base into v_base from tools.settings limit 1;
--v_base:= 'https://npf360test.npff.ru/ourpension/';
-- ******
-- LOGIN (actually a little later): curl_easy_setopt_accepttimeout_ms <- ftp
perform curl.curl_easy_setopt_timeout_ms(1000000);
yi := 1;
--RAISE NOTICE 'f3_load_sfr_register Перед циклом:';
for smev_file in
select id, file_name, transliter_file_name,
coalesce(processing_count, 0) processing_count, processing_text,
case when (from_trustgate = false)
then content -- simply get content bytea
else
lo_get(cast(convert_from(content, 'UTF8') as oid)) -- files from adapter were stored as reference to large object
end
content,
service,
coalesce(delay_count, 0) delay_count,
file_type,
from_trustgate
from robot.input_files
where status = 'loaded' and processing = false
order by id desc
--for update
loop
-- ************
-- preparation:
--RAISE NOTICE 'f3_load_sfr_register нагрузка';
if (smev_file.delay_count > 0)then
-- we only process files with 0 in delay_count field!
-- subtract 1 from current delay_count, updating delay_count and go on to the next file
update robot.input_files set delay_count = smev_file.delay_count - 1
where id = smev_file.id;
continue;
end if;
--RAISE NOTICE 'f3_load_sfr_register после нагрузки';
-- put the file id and file name into processing buffer (if exception is coming we save status)
v_process_file := null;
v_process_file.id := smev_file.id;
v_process_file.status := true;
v_process_file.pcount := smev_file.processing_count + 1; -- count of attempts are incrementing
v_process_file.ptext := 'взято в обработку'; -- smev_file.processing_text
v_process_file.fname := smev_file.file_name;
v_processing_files[yi] := v_process_file;
-- ***************
-- try to process:
--RAISE NOTICE 'f3_load_sfr_register Взято в обработку:';
if(smev_file.from_trustgate) then
file_type_trustgate:= smev_file.file_type;
smev_file_type:= robot.get_document_type(3, left(convert_from(smev_file.content, 'UTF8'),500));
extracted_xmlns:= string_to_array(smev_file_type, ';');
smev_file_type:= extracted_xmlns[1];
smev_file_version:= extracted_xmlns[2];
else
if (smev_file.service = 'BANK STATEMENT') then
smev_file_type:= 'БАНК';
smev_file_version:= 0;
elsif(smev_file.service = 'SMEF PFR') then
v_processing_files[yi].ptext := 'декодирование UTF8';
smev_file_type:= robot.get_document_type(3, left(convert_from(smev_file.content, 'UTF8'),500));
extracted_xmlns:= string_to_array(smev_file_type, ';');
smev_file_type:= extracted_xmlns[1];
smev_file_version:= extracted_xmlns[2];
elsif(smev_file.service = 'XML VERIFICATION') then
v_processing_files[yi].ptext := 'декодирование win1251';
smev_file_type:= robot.get_document_type(4, left(convert_from(convert(smev_file.content, 'windows_1251', 'utf8'), 'utf8'),500));
extracted_xmlns:= string_to_array(smev_file_type, ';');
smev_file_type:= extracted_xmlns[1];
smev_file_version:= extracted_xmlns[2];
elsif (smev_file.service = 'BANK ORDER') then
v_processing_files[yi].ptext := 'определение банка по файлу реестра';
smev_file_type := 'БАНК-РЕЕСТР';
smev_file_version := robot.incoming_order_version_ex(smev_file.content);
elsif (smev_file.service = 'REFUSAL') then
v_processing_files[yi].ptext := 'загрузка отказов от банка';
smev_file_type := 'БАНК-ОТКАЗЫ';
smev_file_version := 0;
else
smev_file_type:= 'NafT';
smev_file_version:= -1;
end if;
end if;
if smev_file_version < 0 then
v_processing_files[yi].ptext:= 'загрузчик не найден';
perform robot.log_message('загрузчик', concat(smev_file_type::text, ' version: ', smev_file_version::text, ' ', smev_file.file_name::text), sid);
RAISE EXCEPTION 'No such file-loader';
end if;
-- perform robot.log_message('работа', smev_file_type::text, sid);
RAISE NOTICE 'Report: %, %', smev_file_type::text || ' ' || 'version: ' || smev_file_version::text , smev_file.file_name;
--RAISE NOTICE 'Report extracted: %', smev_file_type;
-- Get the loader code, version of document (loader)
select id, load_querydatasource_code, multi_load, context_vars, version_ex
into etl_id_key, v_datasource_code, v_multi_load, v_context_vars, v_version_ex
from smev.smev_report_load_etl where report = smev_file_type and version_ex = smev_file_version;
if v_datasource_code is not null then
v_datasource:= Null;
v_datasources:=null;
loaders:= null;
xi:= 1;
v_datasource.id:= 0;
v_datasource.version_ex:= v_version_ex;
v_datasource.code:= v_datasource_code;
v_datasource.text:= 'Основной загрузчик файла';
v_datasource.context:= v_context_vars;
v_datasources[xi]:= v_datasource;
if v_multi_load then
for loaders in
select id_loader, loader_code, loader_description, multi_load, context_vars, version_ex
from smev.smev_report_file_loaders
where id_etl = etl_id_key and version_ex = v_version_ex order by id_loader
loop
if loaders.multi_load then
xi:= xi + 1;
v_datasource:= Null;
v_datasource.id:= loaders.id_loader;
v_datasource.version_ex:= loaders.version_ex;
v_datasource.code:= loaders.loader_code;
v_datasource.text:= loaders.loader_description;
v_datasource.context:= loaders.context_vars;
v_datasources[xi]:= v_datasource;
end if;
end loop;
end if;
-- ***************
-- MAIN LOOP here:
-- ***************
v_processing_files[yi].ptext := 'старт загрузчика';
foreach floader in array v_datasources
loop
file_loader_metadata:= null;
--v_parts:= null;
--v_req:= null;
process_result_stack:= null;
--raise notice 'ARRAY process_result_stack %', array_to_string(process_result_stack, ',','*' );
v_response_result:= null;
--v_response:= null;
curl_response:= null;
v_response_content:= null;
smev_processes:= null;
v_cookies:= null;
v_cookies := robot.login_ex2(v_base, v_robot);
perform curl.curl_easy_reset();
if coalesce(v_cookies, '') = '' then
perform robot.log_message('работа', 'Err get cookies after login', sid);
RAISE EXCEPTION 'Err get cookies after login';
end if;
RAISE NOTICE '-File-loader login ok % loader-code: %', sid, floader.code;
--perform robot.log_message('работа', 'File-loader login ok', sid);
perform curl.curl_easy_reset();
if not curl.curl_mime_data(data:= smev_file.content,
type:= 'text/plain',
name:= 'queryDataSourceFile',
file:= smev_file.transliter_file_name
) then
RAISE Notice 'curl_mime_data queryDataSourceFile';
end if;
--RAISE Notice 'curl_mime_data -2-qdts %, %', 'queryDataSourceFile', convert_from(smev_file.content, 'utf8');
--perform robot.log_message('работа', 'after queryDataSourceFile' , sid);
if not curl.curl_mime_data(data:= floader.code, -- Convert text value to bytea
name:= 'queryDataSourceCode'
) then
RAISE Notice 'curl_mime_data queryDataSourceCode';
end if;
-- RAISE Notice 'curl_mime_data -1-qdts %, %', 'queryDataSourceCode', floader.code;
--perform robot.log_message('работа', 'after queryDataSourceCode' , sid);
queryDataSourceOptions:='{"charset": null, "sectionBegin": null, "sectionEnd": null, "xPaths": null, "delimiter": null,
"quote": null, "footer": null, "buffer": null, "header": null, "sheetNumber": null, "tableNumber": null}';
if not curl.curl_mime_data(data:= queryDataSourceOptions,
name:= 'queryDataSourceOptions'
) then
RAISE Notice 'curl_mime_data queryDataSourceOptions';
end if;
-- *****************************************************8*
-- Processing variables for file-loader from context here:
if (floader.context is not null) then
file_loader_metadata:= smev.f_smev_data_json_build(floader.context, process_result_stack, sid);
end if;
if (cardinality(file_loader_metadata) <> 0 and cardinality(file_loader_metadata) is not null) then
RAISE Notice 'queryDataSource cardinality % ', cardinality(file_loader_metadata)::character varying;
v_var_name:= null;
v_var_value:= null;
foreach loader_var in array file_loader_metadata
loop
if (v_var_name is null) then
v_var_name:= loader_var;
continue;
end if;
if (v_var_value is null) then
v_var_value:=loader_var;
end if;
if (v_var_name is not null and v_var_value is not null)then
if not curl.curl_mime_data(v_var_value,
name := v_var_name
) then
RAISE Notice 'curl_mime_data %', v_var_name;
end if;
--RAISE Notice 'curl_mime_data 1-qdts %, %', v_var_name, v_var_value;
v_var_name:= null;
v_var_value:=null;
end if;
end loop;
end if;
-- !
-- add version_ex variable as 'v_ex' to request header for possible use:
if not curl.curl_mime_data(floader.version_ex::text, name := 'v_ex'::text) then
RAISE Notice 'curl_mime_data %', 'v_ex';
end if;
-- end processing variables for file-loader from context
-- *****************************************************8*
--raise notice 'Before begin_request';
--perform robot.log_message('работа', 'after queryDataSourceOptions' , sid);
if not curl.curl_easy_setopt_url(v_base || 'queryDataSource/upload') then
RAISE Notice 'curl_easy_setopt_url %', v_base || 'queryDataSource/upload';
end if;
--perform robot.log_message('работа', 'after ' || v_base || 'queryDataSource/upload' , sid);
if coalesce(v_cookies, '') <> '' then
if not curl.curl_header_append('Cookie', v_cookies) then
perform robot.log_message('работа', 'Err set cookies', sid);
RAISE EXCEPTION 'Err set cookies';
end if;
end if;
-- raise notice 'Before multipart.send';
if not curl.curl_easy_perform() then -- Execute the request
RAISE EXCEPTION 'Error perform queryDataSource/upload';
end if;
--raise notice 'Code curl response %, %',(curl.curl_easy_getinfo_errcode())::text, curl.curl_easy_getinfo_errdesc();
curl_response_code:= curl.curl_easy_getinfo_response_code();
-- skip next post-processes as file-loader is not properly worked:
if curl_response_code = 200 then
v_processing_files[yi].ptext := 'загрузчик отработал';
raise notice 'загрузчик 200 : %, %', floader.code::text, floader.version_ex::text;
else
v_processing_files[yi].ptext := left(concat('загрузчик не отработал ',
floader.code, ' ',
curl_response_code::text, ' ',
curl.curl_easy_getinfo_errcode()::text, ' ',
curl.curl_easy_getinfo_errdesc()), 255);
raise notice 'Code curl response %, %',(curl.curl_easy_getinfo_errcode())::text, curl.curl_easy_getinfo_errdesc();
-- SKIP CURRENT:
continue;
end if;
curl_response:= curl.curl_easy_getinfo_data_in(); -- Get the response body
raise notice 'Curl response: %', convert_from(curl_response, 'utf8');
raise notice 'Code curl response %', (curl_response_code)::text;
v_response_result := convert_from(curl_response, 'UTF8')::json;
--perform robot.log_message('работа', 'after file-loader worked ' , sid);
-- *************************
-- get just loaded file_id:
select max(id) into v_file_id
from dev.query_datasource_file
where query_datasource_id = (select id from dev.query_datasource where code = floader.code)
and user_id = v_user_id limit 1;
-- record to the robot.files_loaders:
INSERT INTO robot.files_loaders
(id, id_input, file_id, loader_code, description, "version")
VALUES(nextval('robot.files_loaders_id_seq'::regclass),
smev_file.id, v_file_id, floader.code::varchar(50), '', now());
--raise notice 'get uploaded file id %', v_file_id::text;
-- ***************************
if not robot.logout_ex(v_base, v_cookies) then
RAISE EXCEPTION 'Logout error';
else
RAISE NOTICE '-File-loader logout ok %', sid;
end if;
perform pg_sleep(1);
-- *****************************************************************************************************************************
-- ANALYZE file-loaders result:
-- as example:
-- {"result":true,"data":null,"messages":[{"messageType":"WARNING","message":"Загружено 0 записей","code":null}]} - it's not ok
-- (common reason - xml structure is different) we must log this fact (***)
-- {"result":false,"data":null,"messages":[{"messageType":"DANGER","message":"Длина текста \"ПРЕДСТАВИТЕЛЬ\" больше ожидаемой.
-- Ожидалось: 2, имеется: 13 в строке 10, в колонке 26","code":null}]} - seems it's not ok
-- {"result":true,"data":null,"messages":[]} - seems it's ok
-- *****************************************************************************************************************************
if v_response_result->>'result' is not null and v_response_result->>'result' = 'true' then -- upload ok
-- let analyze (***)
postProcessRun:= true;
if (v_response_result::json)->>'messages' is not null then
messages_count:= jsonb_array_length((v_response_result::jsonb)->'messages');
if messages_count is not null and messages_count > 0 then
for i_next in 0..(messages_count-1) loop
i_json:= ((v_response_result::jsonb)->'messages'->i_next);
if(upper((i_json->>'messageType')::text) = 'WARNING') then
perform robot.log_message('загрузчик: ' || floader.code::text || ' ' || floader.version_ex::text, (i_json->>'message')::text, sid);
end if;
if (upper((i_json->>'messageType')::text) = 'DANGER') then
perform robot.log_message('загрузчик: ' || floader.code::text || ' ' || floader.version_ex::text, (i_json->>'message')::text, sid);
postProcessRun:= false;
end if;
end loop;
end if;
end if;
if not postProcessRun then
-- block for futher processing (no infinite trying to process such type of files)
--perform robot.log_message('Ошибка загрузчика', smev_file.file_name || ' код:' || floader.code , sid);
raise notice 'Загрузчик с ошибкой % код: % %', smev_file.file_name, floader.code::text, floader.version_ex::text;
v_processing_files[yi].ptext := 'DANGER ' || floader.code;
update robot.input_files set processing_text = v_processing_files[yi].ptext, processing = true, processing_count = 2
where id = smev_file.id;
-- SKIP CURRENT:
continue;
end if;
-- *********************************************************************************************************
-- Post processes: (from smev.smev_report_post_processes srpp on smev.smev_report_load_etl.id = srpp.id_etl)
-- *********************************************************************************************************
v_processing_files[yi].ptext := 'Постпроцессы стартовали';
metadata:= null;
--raise notice 'ARRAY metadata: %', array_to_string(metadata, ',','*');
v_cookies:= null;
v_cookies := robot.login_ex2(v_base, v_robot);
if coalesce(v_cookies, '') = '' then
perform robot.log_message('работа post process', 'Err get cookies after login', sid);
RAISE EXCEPTION 'Err get cookies postprocess';
end if;
RAISE NOTICE '-Post process login ok %', sid;
--perform robot.log_message('работа', 'Post process login ok', sid);
for smev_processes in
select id_etl, id_loader, id_process, process_type, load_id_var, load_post_process, process_description
from smev.smev_report_post_processes
where id_etl = etl_id_key and id_loader = floader.id and version_ex = floader.version_ex
loop
RAISE notice 'smev_processes.id_process %, %, %, %, % ', smev_processes.id_etl, smev_processes.id_loader ,
smev_processes.id_process, smev_processes.process_type, smev_processes.load_post_process;
v_oper_code:= smev_processes.load_post_process;
v_processing_files[yi].ptext := 'постпроцесс: ' || v_oper_code;
process_j:= Null;
v_file_j:= Null;
p_type:= null;
-- build data json object from smev_processes.load_id_var[] and process_type:
metadata:= smev.f_smev_data_json_build(smev_processes.load_id_var, process_result_stack, sid);
if smev_processes.process_type = 1 then
-- **********************
-- process is queryModel:
query_text:= 'queryModel?queryModelCode=' || v_oper_code;
if (cardinality(metadata) <> 0 and cardinality(metadata) is not null) then
process_j:= json_object(metadata);
else
-- empty data object {}
process_j:= json_build_object();
end if;
p_type:='application/x-www-form-urlencoded';
elsif smev_processes.process_type = 2 then
-- ***********************
-- process is queryAction:
query_text:= 'queryAction/action';
-- adding to metadata two elements (special for queryAction call):
metadata:= array_prepend(v_oper_code, metadata);
metadata:= array_prepend('queryActionCode', metadata);
process_j:= json_object(metadata);
p_type:='application/x-www-form-urlencoded; charset=UTF-8';
elsif smev_processes.process_type = 3 then
-- ***********************
-- process is queryValidator:
query_text:= 'queryValidator/unattended';
metadata:= array_prepend(v_oper_code, metadata);
metadata:= array_prepend('queryValidatorCode', metadata);
process_j:= json_object(metadata);
p_type:='application/x-www-form-urlencoded; charset=UTF-8';
end if;
--v_response_content:= robot.get_page_data(v_base, query_text, v_cookies, process_j);
v_response_content:= robot.get_page_data_curl(v_base, query_text, v_cookies, process_j, 'POST', p_type);
-- ToDo curl
raise notice 'get oper response %', convert_from(v_response_content, 'UTF8');
--perform robot.log_message('Работа', '+++:' || convert_from(v_response_content, 'UTF8')::text, sid);
v_processing_files[yi].ptext := 'постпроцесс: ' || v_oper_code || ' отработал';
raise notice 'get before result ';
v_file_j:= convert_from(v_response_content, 'UTF8')::json;
raise notice 'get postpro result %', v_file_j::text;
-- story to process stack:
-- result_stack_id:= result_stack_id + 1;
process_result_stack[smev_processes.id_process::int] := v_file_j;
perform array_append(process_result_stack, v_file_j);
v_processing_files[yi].ptext := 'постпроцесс: ' || v_oper_code || ' результат сохранён';
if (v_file_j->>'result' = 'false') then
if (select * from smev.f_check_errors_when_create_sfr_register(v_file_j::text)) then
perform smev.f_log_errors_when_create_sfr_register(v_file_j::text, sid); -- текс ошибки из конструктора операций/модели
else
perform robot.log_message('Работа', 'Ошибка:' || query_text || ' ' || v_oper_code, sid);
end if;
else
if (v_file_j->>'result' = 'true') then
if (select * from smev.f_check_errors_when_create_sfr_register(v_file_j::text)) then
perform smev.f_log_errors_when_create_sfr_register(v_file_j::text, sid); -- текс сообщения из конструктора операций/модели
else
perform robot.log_message('Работа', 'ОК:' || query_text || ' ' || v_oper_code, sid);
end if;
end if;
end if;
v_processing_files[yi].ptext := '';
--perform robot.log_message('Работа', 'Массив сохр ' || left(process_result_stack[smev_processes.id_process::int]::text, 150), sid);
end loop; -- smev_processes
if not robot.logout_ex(v_base, v_cookies) then
RAISE EXCEPTION 'Post process Logout error';
else
RAISE NOTICE '-Post process logout ok %', sid;
end if;
v_status_message := 'register created';
-- **************************************************************************
-- special code for updating documents depends on doctypes please, place here
-- **************************************************************************
if(smev_file_type = 'УПП')then
v_status_message := 'UPP created';
-- by just loaded file_id we find record id from dev.model_file_to_verification
-- to update corresponding fields (about UPP).
-- ! Only the last (max id for current file name will be updated)
--RAISE NOTICE 'Before sql UPP %', sid;
select rif.file_name, sslx.verified_doc, sslx.verified_doc_type, qdf.bytes
into v_ver_name, v_verified_name, v_upp_doctype, v_ver_bytes
from data.sfr_smev_load_xmlupp sslx
inner join dev.query_datasource_file qdf on sslx.file_id = qdf.id
inner join robot.files_loaders fl on qdf.id = fl.file_id
inner join robot.input_files rif on fl.id_input = rif.id
where sslx.file_id = v_file_id
group by rif.file_name, sslx.verified_doc, sslx.verified_doc_type, qdf.bytes;
--get the last:
--RAISE NOTICE 'Past sql UPP%', sid;
v_ver_destination_id := -1;
select id into v_ver_destination_id
from dev.v_model_file_to_verification v
where v."name" = v_verified_name
order by id desc limit 1;
--RAISE NOTICE 'limit 1 sql UPP%', sid;
--update
if (v_ver_destination_id > 0) then
v_status_message := 'UPP reconciled';
-- get the document type for wich the receipt is
if (v_upp_doctype = 'ЗРСПН-НПФ') then
-- special case for 'ЗРСПН-НПФ' (second validation)
--RAISE NOTICE 'ЗРСПН-НПФ sql UPP%', sid;
update dev.model_file_to_verification
set rname= v_ver_name,
rbytes = v_ver_bytes,
count = (case when smev.zrspn_second_check(v_file_id) = true
then 3 else 4 end)
where dev.model_file_to_verification.id = v_ver_destination_id;
--RAISE NOTICE 'ЗРСПН-НПФ sql PAST UPP%', sid;
else
RAISE NOTICE 'OTHER sql UPP % %', v_file_id::text, v_ver_destination_id::text;
-- all other upp verified_doc_type (common case)
update dev.model_file_to_verification
set rname= v_ver_name,
rbytes = v_ver_bytes,
count = (select case when Count(check_result) = 0 then 3 else 4 end status
from data.sfr_smev_load_xmlupp sslx1 -- 00 - без ошибок, 10, 20 - предупреждения
where sslx1.file_id = v_file_id and check_result in ('30', '40', '50')) -- коды ошибок, согласно альбому схем СФР
where dev.model_file_to_verification.id = v_ver_destination_id;
RAISE NOTICE 'OTHER sql PAST UPP%', sid;
end if;
end if;
end if;
--RAISE NOTICE '-Post process++ %', sid;
-- Logging:
-- Canceling status for processed file:
v_processing_files[yi].status := false;
update robot.input_files
set status = v_status_message,
processing = v_processing_files[yi].status,
processing_text = v_processing_files[yi].ptext
where id = smev_file.id;
--RAISE NOTICE 'Report: %', '!!!!!!!!!!!' || ' ' || smev_file.id::text || ' ' || v_status_message || ' ' || v_processing_files[yi].status || ' ' || v_processing_files[yi].ptext ; --добавил кайдалло
perform robot.log_message('работа', smev_file.file_name || ' статус: ' || v_status_message, sid);
perform pg_sleep(1);
else
-- upload not ok:
perform robot.log_message('Ошибка загрузчика', smev_file.file_name || ' код:' || floader.code , sid);
raise notice 'Ошибка загрузчика % код: %', smev_file.file_name, floader.code::text;
-- block for futher processing (no infinite trying to process such type of files)
v_processing_files[yi].ptext := 'Ошибка загрузчика ' || floader.code;
update robot.input_files set processing_text = v_processing_files[yi].ptext, processing = true, processing_count = 2
where id = smev_file.id;
end if; -- upload ok
--RAISE NOTICE 'loop next %', sid;
end loop; -- floader
-- Logging:
-- Save count of attempts:
update robot.input_files set processing_count = v_processing_files[yi].pcount
where id = smev_file.id;
raise notice 'Count has updated %', v_processing_files[yi].pcount;
else
-- v_datasource_code is null (файл не распознан)
-- smev_file.processing_text
v_processing_files[yi].ptext := 'файл нераспознан';
update robot.input_files set status = v_processing_files[yi].ptext, processing = true, processing_count = 2
where id = smev_file.id;
end if; -- v_datasource_code
yi := yi + 1;
RAISE NOTICE 'loop EXTERNAL %', sid;
end loop; -- smev_file
-- EXCEPTIONS:
exception when others
-- set the preliminary status ('processing')
-- and if garbage in IN we do not process this file again (hanging task) it
-- will remain in this status untill special manual incident investigation
then
if v_processing_files is not null then
foreach v_process_file in array v_processing_files
loop
if v_process_file.status then
v_local_bool := false;
if (v_process_file.pcount >= 2) then -- не пытаемся работать более двух раз (!)
v_local_bool := true;
end if;
perform robot.set_processing_for_input_files(v_process_file.id);
update robot.input_files
set processing = v_local_bool,
processing_text = v_process_file.ptext,
processing_count = v_process_file.pcount
where id = v_process_file.id;
RAISE NOTICE 'Report: %', 'exception' || ' ' || v_process_file.id::text;
perform robot.log_message('работа', left(v_process_file.fname, 30) || 'статус: processing fail' , sid);
end if;
end loop;
end if;
GET STACKED DIAGNOSTICS
err_code = RETURNED_SQLSTATE,
msg_text = MESSAGE_TEXT,
exc_context = PG_CONTEXT;
RAISE NOTICE 'ERROR CODE: % MESSAGE TEXT: % CONTEXT: %',
err_code, msg_text, exc_context;
end;
v_processing_files := null;
return 0;
END;
$function$
;
