7ges ERP Client/Server

CREATE OR REPLACE FUNCTION llx_societe_before_insert()
RETURNS trigger AS $$
BEGIN
    -- Validate required name
    IF NEW.nom IS NULL OR trim(NEW.nom) = '' THEN
        RAISE EXCEPTION 'ErrorFieldRequired: nom';
    END IF;
    
    -- Validate email
    IF NEW.email IS NOT NULL AND NEW.email != '' THEN
        NEW.email = trim(lower(NEW.email));
        IF NOT (NEW.email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$') THEN
            RAISE EXCEPTION 'ErrorBadEMail: %', NEW.email;
        END IF;
    END IF;
    
    -- Generate client code automatically
    IF NEW.client = 1 AND (NEW.code_client IS NULL OR trim(NEW.code_client) = '') THEN
        NEW.code_client := llx_societe_get_next_code('C', NEW.entity);
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION llx_societe_get_next_code(
    p_type varchar DEFAULT 'C', 
    p_entity integer DEFAULT 1
) RETURNS varchar AS $$
DECLARE
    v_current_num integer;
    v_new_code varchar(50);
BEGIN
    -- Find the next number according to type
    CASE p_type
        WHEN 'C' THEN
            SELECT COALESCE(MAX(
                CAST(substring(code_client from '[0-9]+$') AS integer)
            ), 0) + 1 INTO v_current_num
            FROM llx_societe
            WHERE code_client ~ '^CU[0-9]+$' AND entity = p_entity;
            
            v_new_code := 'CU' || lpad(v_current_num::text, 4, '0');
            
        WHEN 'F' THEN
            SELECT COALESCE(MAX(
                CAST(substring(code_fournisseur from '[0-9]+$') AS integer)
            ), 0) + 1 INTO v_current_num
            FROM llx_societe
            WHERE code_fournisseur ~ '^FO[0-9]+$' AND entity = p_entity;
            
            v_new_code := 'FO' || lpad(v_current_num::text, 4, '0');
    END CASE;
    
    RETURN v_new_code;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION llx_societe_id_prof_exists(
    p_field varchar, 
    p_value varchar, 
    p_id integer DEFAULT 0
) RETURNS boolean AS $$
DECLARE
    v_count integer;
BEGIN
    IF p_value IS NULL OR trim(p_value) = '' THEN
        RETURN false;
    END IF;
    
    EXECUTE format('SELECT COUNT(*) FROM llx_societe WHERE %I = $1 AND rowid != $2', p_field)
    INTO v_count
    USING p_value, p_id;
    
    RETURN v_count > 0;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION llx_product_before_insert()
RETURNS trigger AS $$
BEGIN
    -- Validate required reference
    IF NEW.ref IS NULL OR trim(NEW.ref) = '' THEN
        RAISE EXCEPTION 'ErrorFieldRequired: ref';
    END IF;
    
    -- Validate reference uniqueness
    IF EXISTS (SELECT 1 FROM llx_product WHERE ref = NEW.ref AND entity = NEW.entity) THEN
        RAISE EXCEPTION 'ErrorRefAlreadyExists: %', NEW.ref;
    END IF;
    
    -- Calculate TTC price automatically
    IF NEW.price IS NOT NULL AND NEW.tva_tx IS NOT NULL THEN
        NEW.price_ttc := NEW.price * (1 + NEW.tva_tx / 100);
    END IF;
    
    -- Default values
    NEW.entity := COALESCE(NEW.entity, 1);
    NEW.tosell := COALESCE(NEW.tosell, 1);
    NEW.tobuy := COALESCE(NEW.tobuy, 1);
    NEW.datec := COALESCE(NEW.datec, NOW());
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION llx_product_manage_price_history()
RETURNS trigger AS $$
BEGIN
    -- Only create history if price or VAT changed
    IF OLD.price IS DISTINCT FROM NEW.price OR 
       OLD.price_ttc IS DISTINCT FROM NEW.price_ttc OR 
       OLD.tva_tx IS DISTINCT FROM NEW.tva_tx THEN
        
        INSERT INTO llx_product_price (
            fk_product, date_price, price, price_ttc, tva_tx, 
            fk_user_author, entity
        ) VALUES (
            NEW.rowid, NOW(), NEW.price, NEW.price_ttc, NEW.tva_tx,
            COALESCE(NEW.fk_user_modif, NEW.fk_user_author, 1), NEW.entity
        );
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION llx_propal_get_next_ref(p_entity integer DEFAULT 1)
RETURNS varchar AS $$
DECLARE
    v_current_year varchar(2);
    v_current_month varchar(2);
    v_current_num integer;
    v_new_ref varchar(30);
BEGIN
    -- Get current year and month
    v_current_year := to_char(CURRENT_DATE, 'YY');
    v_current_month := to_char(CURRENT_DATE, 'MM');
    
    -- Find the next number for this year-month
    SELECT COALESCE(MAX(
        CAST(
            CASE 
                WHEN ref ~ '^PR[0-9]{2}[0-9]{2}-[0-9]{4}$' 
                THEN substring(ref from 8 for 4)
                ELSE '0'
            END AS integer
        )
    ), 0) + 1 INTO v_current_num
    FROM llx_propal
    WHERE ref ~ ('^PR' || v_current_year || v_current_month || '-[0-9]{4}$')
    AND entity = p_entity;
    
    -- Generate new reference with format PR-YYMM-NNNN
    v_new_ref := 'PR' || v_current_year || v_current_month || '-' || 
                 lpad(v_current_num::text, 4, '0');
    
    RETURN v_new_ref;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION llx_propal_update_totals(p_propal_id integer)
RETURNS void AS $$
DECLARE
    v_total_ht numeric(24,8) := 0;
    v_total_tva numeric(24,8) := 0;
    v_total_ttc numeric(24,8) := 0;
BEGIN
    -- Calculate totals from detail lines
    SELECT 
        COALESCE(SUM(total_ht), 0),
        COALESCE(SUM(total_tva), 0),
        COALESCE(SUM(total_ttc), 0)
    INTO v_total_ht, v_total_tva, v_total_ttc
    FROM llx_propaldet
    WHERE fk_propal = p_propal_id;
    
    -- Update the quote header
    UPDATE llx_propal
    SET 
        total_ht = v_total_ht,
        total_tva = v_total_tva,
        total_ttc = v_total_ttc,
        tms = NOW()
    WHERE rowid = p_propal_id;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION llx_commande_before_update()
RETURNS trigger AS $$
BEGIN
    -- Control of state transitions
    IF OLD.fk_statut != NEW.fk_statut THEN
        -- Do not allow return to draft once validated
        IF OLD.fk_statut >= 1 AND NEW.fk_statut = 0 THEN
            RAISE EXCEPTION 'Cannot return a validated order to draft';
        END IF;
        
        -- Do not allow modifying closed orders
        IF OLD.fk_statut = 3 THEN
            RAISE EXCEPTION 'Cannot modify a closed order';
        END IF;
        
        -- Set validation date when validating
        IF NEW.fk_statut = 1 AND OLD.fk_statut = 0 THEN
            NEW.date_valid := COALESCE(NEW.date_valid, NOW());
            
            -- Generate definitive reference when validating
            IF NEW.ref LIKE '(PROV%' THEN
                NEW.ref := llx_commande_get_next_ref(NEW.entity, NEW.fk_soc);
            END IF;
        END IF;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;