aonestar.public

Parameters

Name	Type	Mode
report_date	date	IN
user_name	text	IN (DEFAULT NULL)
status	text	INOUT (DEFAULT NULL)
msg	text	INOUT (DEFAULT NULL)

Definition

DECLARE
    v_export record;
    v_url text;
    v_success_count integer := 0;
    v_failed_count integer := 0;
    v_failure_details text := '';
    v_sqlstate text;
    v_error_message text;
    v_detail text;
    v_hint text;
    v_context text;
    v_prop_name text;
    v_temp_log_id bigint;
    v_candidate_count integer;
BEGIN
    IF report_date IS NULL THEN
        RAISE EXCEPTION 'report_date is required';
    END IF;

user_name := COALESCE(user_name, public.fn_current_user());

SELECT count(*)
    INTO v_candidate_count
    FROM public.fn_endday_register_card_exports(report_date);

IF v_candidate_count = 0 THEN
        status := 'success';
        msg := '0 register card(s) generated, 0 failed';
        RETURN;
    END IF;

SELECT result_data
    INTO v_temp_log_id
    FROM dblink(
        public.fn_conn_authen(),
        format(
            'SELECT public.fn_create_temp_report_token(%L, %L)',
            user_name,
            public.fn_prop_id()
        )
    ) AS response(result_data bigint);

FOR v_export IN
        SELECT *
        FROM public.fn_endday_register_card_exports(report_date)
    LOOP
        BEGIN
            SELECT result_data
            INTO v_url
            FROM dblink(
                public.fn_conn_authen(),
                format(
                    'SELECT public.sp_export_register_card_pdf(%L, %L, %L::date, %L, %L)',
                    public.fn_prop_id(),
                    v_export.register_id,
                    report_date,
                    v_export.file_name,
                    v_temp_log_id
                )
            ) AS response(result_data text);

IF NULLIF(v_url, '') IS NULL THEN
                RAISE EXCEPTION 'Report service returned an empty URL';
            END IF;

v_success_count := v_success_count + 1;
        EXCEPTION WHEN OTHERS THEN
            GET STACKED DIAGNOSTICS
                v_sqlstate = RETURNED_SQLSTATE,
                v_error_message = MESSAGE_TEXT,
                v_detail = PG_EXCEPTION_DETAIL,
                v_hint = PG_EXCEPTION_HINT,
                v_context = PG_EXCEPTION_CONTEXT;

v_failed_count := v_failed_count + 1;
            IF v_failed_count <= 20 THEN
                v_failure_details := v_failure_details || format(
                    E'Register %s (%s): %s\n',
                    v_export.register_id,
                    v_export.file_name,
                    v_error_message
                );
            END IF;

PERFORM public.sp_log_error(
                'sp_export_register_cards',
                v_error_message,
                v_detail,
                v_sqlstate,
                v_hint,
                v_context,
                jsonb_build_object(
                    'register_id', v_export.register_id,
                    'file_name', v_export.file_name,
                    'report_date', report_date
                ),
                user_name,
                force_write => true,
                notify_admin => false
            );
        END;
    END LOOP;

PERFORM result_data
    FROM dblink(
        public.fn_conn_authen(),
        format('SELECT public.fn_close_temp_report_token(%L)', v_temp_log_id)
    ) AS response(result_data boolean);

IF v_failed_count > 0 THEN
        SELECT pi.name
        INTO v_prop_name
        FROM public.property_info pi
        LIMIT 1;

PERFORM public.sp_system_notify(
            'Register Card Export Error',
            format(
                E'Prop: %s\nDate: %s\nGenerated: %s\nFailed: %s\n\n%s%s',
                COALESCE(v_prop_name, public.fn_prop_code()),
                to_char(report_date, 'YYYY-MM-DD'),
                v_success_count,
                v_failed_count,
                v_failure_details,
                CASE
                    WHEN v_failed_count > 20 THEN format('... and %s more failure(s)', v_failed_count - 20)
                    ELSE ''
                END
            )
        );
    END IF;

status := 'success';
    msg := format(
        '%s register card(s) generated, %s failed',
        v_success_count,
        v_failed_count
    );
END

sp_export_register_cards

Description

Parameters

Definition