Fix for customer search not working (trunk)

[Erik Huelsmann <..hidden..>]

Hi all,

This afternoon I found the problem of trunk's customer search not
working. Mailed the fix to Chris privately, but now I figure others
may benefit from the fix as much as I do.

The function 'company__search' needs a fix; fixed version inline below, with
the changed line marked ####. The old version tests meta_number for
nullity, where that should have been 'in_meta_number'.

Thanks again!

Regards,


Erik.



CREATE OR REPLACE FUNCTION company__search
(in_account_class int, in_contact text, in_contact_info text[],
       in_meta_number text, in_address text, in_city text, in_state text,
       in_mail_code text, in_country text, in_date_from date, in_date_to date,
       in_business_id int, in_legal_name text, in_control_code text)
RETURNS SETOF company_search_result AS $$
DECLARE
       out_row company_search_result;
       loop_count int;
       t_contact_info text[];
BEGIN
       t_contact_info = in_contact_info;


       FOR out_row IN
               SELECT e.id, e.control_code, c.id, ec.id, ec.meta_number,
                       ec.description, ec.entity_class,
                       c.legal_name, c.sic_code, b.description , ec.curr::text
               FROM (select * from entity where in_control_code = control_code
                     union
                     select * from entity where in_control_code is null) e
               JOIN (SELECT * FROM company
                      WHERE legal_name like  '%' || in_legal_name || '%'
                     UNION ALL
                     SELECT * FROM company
                      WHERE in_legal_name IS NULL) c ON (e.id = c.entity_id)
               JOIN (SELECT * FROM entity_credit_account
                      WHERE meta_number = in_meta_number
                     UNION ALL
                     SELECT * from entity_credit_account
####                       WHERE in_meta_number IS NULL) ec ON
(ec.entity_id = e.id)
               LEFT JOIN business b ON (ec.business_id = b.id)
               WHERE ec.entity_class = in_account_class
                       AND (c.id IN (select company_id FROM company_to_contact
                               WHERE contact LIKE ALL(t_contact_info))
                               OR '' LIKE ALL(t_contact_info))

                       AND (c.legal_name like '%' || in_legal_name || '%'
                               OR in_legal_name IS NULL)
                       AND ((in_address IS NULL AND in_city IS NULL
                                       AND in_state IS NULL
                                       AND in_country IS NULL)
                               OR (c.id IN
                               (select company_id FROM company_to_location
                               WHERE location_id IN
                                       (SELECT id FROM location
                                       WHERE line_one
                                               ilike '%' ||
                                                       coalesce(in_address, '')
                                                       || '%'
                                               AND city ILIKE
                                                       '%' ||
                                                       coalesce(in_city, '')
                                                       || '%'
                                               AND state ILIKE
                                                       '%' ||
                                                       coalesce(in_state, '')
                                                       || '%'
                                               AND mail_code ILIKE
                                                       '%' ||
                                                       coalesce(in_mail_code,
                                                               '')
                                                       || '%'
                                               AND country_id IN
                                                       (SELECT id FROM country
                                                       WHERE name LIKE '%' ||
                                                               in_country ||'%'
                                                               OR short_name
                                                               ilike
                                                               in_country)))))
                       AND (ec.business_id =
                               coalesce(in_business_id, ec.business_id)
                               OR (ec.business_id IS NULL
                                       AND in_business_id IS NULL))
                       AND (ec.startdate <= coalesce(in_date_to,
                                               ec.startdate)
                               OR (ec.startdate IS NULL))
                       AND (ec.enddate >= coalesce(in_date_from, ec.enddate)
                               OR (ec.enddate IS NULL))
       LOOP
               RETURN NEXT out_row;
       END LOOP;
END;
$$ language plpgsql;

bye,

Erik.