-- Group: crmaccounts
-- Name:  detail
-- Notes: 
-- Copyright (c) 1999-2015 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.

SELECT DISTINCT ON (crmacct_number)
  crmacct_id AS id, crmacct_number, crmacct_name, crmacct_owner_username, crmacct_active,
  cntct.*, addr.*,
  (crmacct_cust_id       IS NOT NULL) AS cust,
  (crmacct_prospect_id   IS NOT NULL) AS prospect,
  (crmacct_vend_id       IS NOT NULL) AS vend,
  (crmacct_competitor_id IS NOT NULL) AS competitor,
  (crmacct_partner_id    IS NOT NULL) AS partner,
  (crmacct_taxauth_id    IS NOT NULL) AS taxauth,
  (crmacct_usr_username  IS NOT NULL) AS usr,
  (crmacct_emp_id        IS NOT NULL) AS emp,
  (crmacct_salesrep_id   IS NOT NULL) AS salesrep
<? foreach("char_id_text_list") ?>
  , charass_alias<? literal("char_id_text_list") ?>.charass_value AS char<? literal("char_id_text_list") ?>
<? endforeach ?>
<? foreach("char_id_list_list") ?>
  , charass_alias<? literal("char_id_list_list") ?>.charass_value AS char<? literal("char_id_list_list") ?>
<? endforeach ?>
<? foreach("char_id_date_list") ?>
  , charass_alias<? literal("char_id_date_list") ?>.charass_value::date AS char<? literal("char_id_date_list") ?>
<? endforeach ?>
FROM crmacct() LEFT OUTER JOIN
  cntct ON (crmacct_cntct_id_1=cntct_id) LEFT OUTER JOIN
  addr ON (cntct_addr_id=addr_id) 
<? foreach("char_id_text_list") ?>
  LEFT OUTER JOIN charass charass_alias<? literal("char_id_text_list") ?> ON ((charass_alias<? literal("char_id_text_list") ?>.charass_target_type='CRMACCT') 
                                                                    AND  (charass_alias<? literal("char_id_text_list") ?>.charass_target_id=crmacct_id)
                                                                    AND  (charass_alias<? literal("char_id_text_list") ?>.charass_char_id=<? value("char_id_text_list") ?>))
  LEFT OUTER JOIN char char_alias<? literal("char_id_text_list") ?> ON (charass_alias<? literal("char_id_text_list") ?>.charass_char_id=char_alias<? literal("char_id_text_list") ?>.char_id)
<? endforeach ?>
<? foreach("char_id_list_list") ?>
  LEFT OUTER JOIN charass charass_alias<? literal("char_id_list_list") ?> ON ((charass_alias<? literal("char_id_list_list") ?>.charass_target_type='CRMACCT') 
                                                                    AND  (charass_alias<? literal("char_id_list_list") ?>.charass_target_id=crmacct_id)
                                                                    AND  (charass_alias<? literal("char_id_list_list") ?>.charass_char_id=<? value("char_id_list_list") ?>))
  LEFT OUTER JOIN char char_alias<? literal("char_id_list_list") ?> ON (charass_alias<? literal("char_id_list_list") ?>.charass_char_id=char_alias<? literal("char_id_list_list") ?>.char_id)
<? endforeach ?>
<? foreach("char_id_date_list") ?>
  LEFT OUTER JOIN charass charass_alias<? literal("char_id_date_list") ?> ON ((charass_alias<? literal("char_id_date_list") ?>.charass_target_type='CRMACCT') 
                                                                    AND  (charass_alias<? literal("char_id_date_list") ?>.charass_target_id=crmacct_id)
                                                                    AND  (charass_alias<? literal("char_id_date_list") ?>.charass_char_id=<? value("char_id_date_list") ?>))
  LEFT OUTER JOIN char char_alias<? literal("char_id_date_list") ?> ON (charass_alias<? literal("char_id_date_list") ?>.charass_char_id=char_alias<? literal("char_id_date_list") ?>.char_id)
<? endforeach ?>
WHERE true
<? if exists("owner_username") ?> 
  AND (crmacct_owner_username=<? value("owner_username") ?>) 
<? elseif exists("owner_usr_pattern") ?>
  AND (crmacct_owner_username ~ <? value("owner_usr_pattern") ?>) 
<? endif ?>
<? if not exists("showInactive") ?> 
  AND crmacct_active 
<? endif ?>
<? if exists("search_pattern") ?>
  AND ((crmacct_number ~* <? value("search_pattern") ?>)
       OR (crmacct_name ~* <? value("search_pattern") ?>)
       OR (COALESCE(cntct_first_name,'') || ' ' || COALESCE(cntct_last_name,'') ~* <? value("search_pattern") ?>)
       OR (COALESCE(cntct_phone,'') || ' ' || COALESCE(cntct_phone2,'') || ' ' || COALESCE(cntct_fax,'') ~* <? value("search_pattern") ?>)
       OR (COALESCE(cntct_email,'') ~* <? value("search_pattern") ?>)
       OR (COALESCE(addr_line1,'') || ' ' || COALESCE(addr_line2,'') || ' ' || COALESCE(addr_line3,'') ~* <? value("search_pattern") ?>)
       OR (COALESCE(addr_city,'') ~* <? value("search_pattern") ?>)
       OR (COALESCE(addr_state,'') ~* <? value("search_pattern") ?>)
       OR (COALESCE(addr_postalcode,'') ~* <? value("search_pattern") ?>)
       OR (addr_country ~* <? value("search_pattern") ?>))
<? endif ?>
<? if exists("crmacct_number_pattern") ?>
  AND (crmacct_number ~* <? value("crmacct_number_pattern") ?>)
<? endif ?>
<? if exists("crmacct_name_pattern") ?>
  AND (crmacct_name ~* <? value("crmacct_name_pattern") ?>)
<? endif ?>
<? if exists("cntct_name_pattern") ?>
  AND (COALESCE(cntct_first_name,'') || ' ' || COALESCE(cntct_last_name,'') ~* <? value("cntct_name_pattern") ?>)
<? endif ?>
<? if exists("cntct_phone_pattern") ?>
  AND (COALESCE(cntct_phone,'') || ' ' || COALESCE(cntct_phone2,'') || ' ' || COALESCE(cntct_fax,'') ~* <? value("cntct_phone_pattern") ?>)
<? endif ?>
<? if exists("cntct_email_pattern") ?>
  AND (COALESCE(cntct_email,'') ~* <? value("cntct_email_pattern") ?>)
<? endif ?>
<? if exists("addr_street_pattern") ?>
  AND (COALESCE(addr_line1,'') || ' ' || COALESCE(addr_line2,'') || ' ' || COALESCE(addr_line3,'') ~* <? value("addr_street_pattern") ?>)
<? endif ?>
<? if exists("addr_city_pattern") ?>
  AND (COALESCE(addr_city,'') ~* <? value("addr_city_pattern") ?>)
<? endif ?>
<? if exists("addr_state_pattern") ?>
  AND (COALESCE(addr_state,'') ~* <? value("addr_state_pattern") ?>)
<? endif ?>
<? if exists("addr_postalcode_pattern") ?>
  AND (COALESCE(addr_postalcode,'') ~* <? value("addr_postalcode_pattern") ?>)
<? endif ?>
<? if exists("addr_country_pattern") ?>
  AND (COALESCE(addr_country,'') ~* <? value("addr_country_pattern") ?>)
<? endif ?>
<? if exists("id") ?>
  AND (crmacct_id=<? value("id") ?>)
<? endif ?>
<? literal("charClause") ?>

<? if exists("excludeMergeWIP") ?>
  AND (crmacct_id NOT IN (SELECT mrgundo_base_id
                            FROM mrgundo
                           WHERE (mrgundo_base_schema='public')
                             AND (mrgundo_base_table='crmacct')
                          UNION
                          SELECT crmacctsel_src_crmacct_id
                            FROM crmacctsel
                          UNION
                          SELECT crmacctsel_dest_crmacct_id
                            FROM crmacctsel))
<? endif ?>
<? if exists("crmacct_acct_type") ?>
  AND CASE WHEN <? value("crmacct_acct_type") ?> = 1 THEN crmacct_cust_id IS NOT NULL
           WHEN <? value("crmacct_acct_type") ?> = 2 THEN crmacct_prospect_id IS NOT NULL
           WHEN <? value("crmacct_acct_type") ?> = 3 THEN crmacct_vend_id IS NOT NULL
           WHEN <? value("crmacct_acct_type") ?> = 4 THEN crmacct_competitor_id IS NOT NULL
           WHEN <? value("crmacct_acct_type") ?> = 5 THEN crmacct_partner_id IS NOT NULL
           WHEN <? value("crmacct_acct_type") ?> = 6 THEN crmacct_taxauth_id IS NOT NULL
           WHEN <? value("crmacct_acct_type") ?> = 7 THEN crmacct_usr_username IS NOT NULL
           WHEN <? value("crmacct_acct_type") ?> = 8 THEN crmacct_emp_id IS NOT NULL
           WHEN <? value("crmacct_acct_type") ?> = 9 THEN crmacct_salesrep_id IS NOT NULL
      END
<? endif ?>
ORDER BY crmacct_number; 