42 lines
2.6 KiB
MySQL
42 lines
2.6 KiB
MySQL
|
-- SQL tarafında açılan belgenin (yordamın ürettiği teklif, sipariş) numarasını numaratörden alır.
|
|||
|
|
-- Biçim ve sıfırlama kuralı Sequence.GetNextSequence ile birebir aynıdır; biri değişirse diğeri de değişir.
|
|||
|
|
CREATE OR REPLACE PROCEDURE public."Adm_P_SequenceNext"(p_name varchar, p_tenant_id uuid, INOUT p_value varchar DEFAULT NULL)
|
|||
|
|
LANGUAGE plpgsql AS $$
|
|||
|
|
DECLARE v_id uuid; v_reset int; v_period varchar(16); v_number bigint; v_step int; v_padding int;
|
|||
|
|
v_prefix varchar(64); v_suffix varchar(64); v_template varchar(256); v_text varchar(32); v_now timestamp := LOCALTIMESTAMP;
|
|||
|
|
BEGIN
|
|||
|
|
SELECT "Id", "ResetPeriod" INTO v_id, v_reset
|
|||
|
|
FROM "Adm_T_Sequence"
|
|||
|
|
WHERE "Name" = p_name AND "IsActive" = true AND "IsDeleted" = false AND "TenantId" IS NOT DISTINCT FROM p_tenant_id
|
|||
|
|
LIMIT 1
|
|||
|
|
FOR UPDATE;
|
|||
|
|
IF v_id IS NULL THEN
|
|||
|
|
RAISE EXCEPTION 'Numaratör bulunamadı: %', p_name USING ERRCODE = 'P0001', DETAIL = '50190';
|
|||
|
|
END IF;
|
|||
|
|
|
|||
|
|
v_period := CASE v_reset WHEN 1 THEN to_char(v_now, 'YYYY') WHEN 2 THEN to_char(v_now, 'YYYYMM') WHEN 3 THEN to_char(v_now, 'YYYYMMDD')
|
|||
|
|
WHEN 4 THEN to_char(v_now, 'YYYYMMDDHH24') WHEN 5 THEN to_char(v_now, 'YYYYMMDDHH24MI') END;
|
|||
|
|
|
|||
|
|
SELECT CASE WHEN v_period IS NOT NULL AND COALESCE("LastPeriodKey", '') <> v_period THEN "StartNumber" ELSE "NextNumber" END,
|
|||
|
|
"IncrementStep", "PaddingSize", COALESCE("Prefix", ''), COALESCE("Suffix", ''),
|
|||
|
|
CASE WHEN TRIM(COALESCE("FormatTemplate", '')) = '' THEN '{Prefix}{Number}{Suffix}' ELSE "FormatTemplate" END
|
|||
|
|
INTO v_number, v_step, v_padding, v_prefix, v_suffix, v_template
|
|||
|
|
FROM "Adm_T_Sequence" WHERE "Id" = v_id;
|
|||
|
|
|
|||
|
|
UPDATE "Adm_T_Sequence"
|
|||
|
|
SET "NextNumber" = v_number + v_step, "LastPeriodKey" = COALESCE(v_period, "LastPeriodKey")
|
|||
|
|
WHERE "Id" = v_id;
|
|||
|
|
|
|||
|
|
v_text := v_number::text;
|
|||
|
|
IF length(v_text) < v_padding THEN v_text := lpad(v_text, v_padding, '0'); END IF;
|
|||
|
|
|
|||
|
|
p_value := replace(replace(replace(v_template, '{Prefix}', v_prefix), '{Suffix}', v_suffix), '{Number}', v_text);
|
|||
|
|
p_value := replace(replace(p_value, '{yyyy}', to_char(v_now, 'YYYY')), '{yy}', to_char(v_now, 'YY'));
|
|||
|
|
p_value := replace(replace(p_value, '{MM}', to_char(v_now, 'MM')), '{M}', to_char(v_now, 'FMMM'));
|
|||
|
|
p_value := replace(replace(p_value, '{dd}', to_char(v_now, 'DD')), '{d}', to_char(v_now, 'FMDD'));
|
|||
|
|
p_value := replace(replace(p_value, '{HH}', to_char(v_now, 'HH24')), '{H}', to_char(v_now, 'FMHH24'));
|
|||
|
|
p_value := replace(replace(p_value, '{mm}', to_char(v_now, 'MI')), '{m}', to_char(v_now, 'FMMI'));
|
|||
|
|
p_value := replace(replace(p_value, '{ss}', to_char(v_now, 'SS')), '{s}', to_char(v_now, 'FMSS'));
|
|||
|
|
END;
|
|||
|
|
$$;
|