7ges ERP Client/Server

💻 Main Function

CREATE OR REPLACE FUNCTION llx_product_generate_multiprices(
    p_product_id integer,
    p_base_price numeric,
    p_price_type varchar(3),
    p_tva_tx numeric,
    p_price_min numeric DEFAULT 0,
    p_user_id integer DEFAULT 1
)
RETURNS TABLE(level integer, price numeric, price_ttc numeric)
LANGUAGE plpgsql AS $$
DECLARE
    v_rule record;
    v_prices numeric[];
    v_limit integer;
BEGIN
    -- Obtener límite de niveles
    v_limit := get_config_value('PRODUIT_MULTIPRICES_LIMIT', 1, '5')::integer;
    v_prices := array_fill(0::numeric, ARRAY[v_limit]);
    
    -- Precio base (nivel 1)
    v_prices[1] := p_base_price;
    
    -- Calcular precios por niveles
    FOR v_rule IN 
        SELECT * FROM llx_product_pricerules 
        WHERE active = 1 
        ORDER BY level
    LOOP
        IF v_rule.level <= v_limit THEN
            IF v_rule.fk_level > 0 AND v_rule.fk_level <= v_limit THEN
                -- Basado en otro nivel
                v_prices[v_rule.level] := v_prices[v_rule.fk_level] * 
                    (1 + v_rule.var_percent / 100);
            ELSE
                -- Basado en precio base
                v_prices[v_rule.level] := p_base_price * 
                    (1 + v_rule.var_percent / 100);
            END IF;
        END IF;
    END LOOP;
    
    -- Retornar resultados
    FOR i IN 1..v_limit LOOP
        RETURN QUERY SELECT 
            i::integer as level,
            v_prices[i] as price,
            v_prices[i] * (1 + p_tva_tx / 100) as price_ttc;
    END LOOP;
END;
$$;