Re: [Ledger-smb-devel] Fix for customer search not working (trunk)
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
Re: [Ledger-smb-devel] Fix for customer search not working (trunk)
committed this, commenting out a partially done function for customer history for now On Mon, May 23, 2011 at 6:11 PM, John Locke m...@freelock.com wrote: 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. -- vRanger cuts backup time in half-while increasing security. With the market-leading solution for virtual backup and recovery, you get blazing-fast, flexible, and affordable data protection. Download your free trial now. http://p.sf.net/sfu/quest-d2dcopy1 ___ Ledger-smb-devel mailing list Ledger-smb-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
[Ledger-smb-devel] Fix for customer search not working (trunk)
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
