Re: Fix for customer search not working (trunk)

[John Locke <..hidden..>]

Hi,

Did this one get committed? Didn't see the change in the last version
pulled down... Huge relief to have this working, good one.

One more change I'd suggest: s/like/ILIKE/ in the legal name searches,
anyway...

On 05/16/2011 06:06 AM, Erik Huelsmann wrote:
> 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.
>
> ------------------------------------------------------------------------------
> Achieve unprecedented app performance and reliability
> What every C/C++ and Fortran developer should know.
> Learn how Intel has extended the reach of its next-generation tools
> to help boost performance applications - inlcuding clusters.
> http://p.sf.net/sfu/intel-dev2devmay
> _______________________________________________
> Ledger-smb-devel mailing list
> ..hidden..
> https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel