-- Group: vendors
-- 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 
  vend_id, vend_vendtype_id, 
  vend_active, vend_number, vend_name,
  vendtype_code,
<? 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 ?>
  cntct.*,
  CASE WHEN (cntctaddr.addr_id IS NOT NULL) THEN cntctaddr.addr_id ELSE vendaddr.addr_id END AS addr_id,
  CASE WHEN (cntctaddr.addr_id IS NOT NULL) THEN cntctaddr.addr_active ELSE vendaddr.addr_active END AS addr_active,
  CASE WHEN (cntctaddr.addr_id IS NOT NULL) THEN cntctaddr.addr_line1 ELSE vendaddr.addr_line1 END AS addr_line1,
  CASE WHEN (cntctaddr.addr_id IS NOT NULL) THEN cntctaddr.addr_line2 ELSE vendaddr.addr_line2 END AS addr_line2,
  CASE WHEN (cntctaddr.addr_id IS NOT NULL) THEN cntctaddr.addr_line3 ELSE vendaddr.addr_line3 END AS addr_line3,
  CASE WHEN (cntctaddr.addr_id IS NOT NULL) THEN cntctaddr.addr_city ELSE vendaddr.addr_city END AS addr_city,
  CASE WHEN (cntctaddr.addr_id IS NOT NULL) THEN cntctaddr.addr_state ELSE vendaddr.addr_state END AS addr_state,
  CASE WHEN (cntctaddr.addr_id IS NOT NULL) THEN cntctaddr.addr_postalcode ELSE vendaddr.addr_postalcode END AS addr_postalcode,
  CASE WHEN (cntctaddr.addr_id IS NOT NULL) THEN cntctaddr.addr_country ELSE vendaddr.addr_country END AS addr_country,
  CASE WHEN (cntctaddr.addr_id IS NOT NULL) THEN cntctaddr.addr_notes ELSE vendaddr.addr_notes END AS addr_notes,
  CASE WHEN (cntctaddr.addr_id IS NOT NULL) THEN cntctaddr.addr_number ELSE vendaddr.addr_number END AS addr_number
FROM vendinfo 
  JOIN vendtype ON (vend_vendtype_id=vendtype_id)
  LEFT OUTER JOIN cntct ON (vend_cntct1_id=cntct_id) 
  LEFT OUTER JOIN addr AS vendaddr ON (vend_addr_id=vendaddr.addr_id)
  LEFT OUTER JOIN addr AS  cntctaddr ON (cntct_addr_id=cntctaddr.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='V') 
      AND  (charass_alias<? literal("char_id_text_list") ?>.charass_target_id=vend_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='V') 
      AND  (charass_alias<? literal("char_id_list_list") ?>.charass_target_id=vend_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='V') 
      AND  (charass_alias<? literal("char_id_date_list") ?>.charass_target_id=vend_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 not exists("showInactive") ?> 
  AND vend_active 
<? endif ?>
<? if exists("search_pattern") ?>
  AND ((vend_number ~* <? value("search_pattern") ?>)
       OR (vend_name ~* <? value("search_pattern") ?>)
       OR (vendtype_code ~* <? 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(cntctaddr.addr_line1, vendaddr.addr_line1, '') || ' ' || COALESCE(cntctaddr.addr_line2, vendaddr.addr_line2,'') || ' ' || COALESCE(cntctaddr.addr_line3, vendaddr.addr_line3, '') ~* <? value("search_pattern") ?>)
       OR (COALESCE(cntctaddr.addr_city, vendaddr.addr_city,'') ~* <? value("search_pattern") ?>)
       OR (COALESCE(cntctaddr.addr_state, vendaddr.addr_state,'') ~* <? value("search_pattern") ?>)
       OR (COALESCE(cntctaddr.addr_postalcode, vendaddr.addr_postalcode,'') ~* <? value("search_pattern") ?>)
       OR (COALESCE(cntctaddr.addr_country, vendaddr.addr_country,'') ~* <? value("search_pattern") ?>))
<? endif ?>
<? if exists("vend_number_pattern") ?>
  AND (vend_number ~* <? value("vend_number_pattern") ?>)
<? endif ?>
<? if exists("vend_name_pattern") ?>
  AND (vend_name ~* <? value("vend_name_pattern") ?>)
<? endif ?>
<? if exists("vendtype_code_pattern") ?>
  AND (vendtype_code ~* <? value("vendtype_code_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(cntctaddr.addr_line1, vendaddr.addr_line1,'') || ' ' || COALESCE(cntctaddr.addr_line2,vendaddr.addr_line2,'') || ' ' || COALESCE(cntctaddr.addr_line3,vendaddr.addr_line3, '') ~* <? value("addr_street_pattern") ?>)
<? endif ?>
<? if exists("addr_city_pattern") ?>
  AND (COALESCE(cntctaddr.addr_city, vendaddr.addr_city,'') ~* <? value("addr_city_pattern") ?>)
<? endif ?>
<? if exists("addr_state_pattern") ?>
  AND (COALESCE(cntctaddr.addr_state,vendaddr.addr_state,'') ~* <? value("addr_state_pattern") ?>)
<? endif ?>
<? if exists("addr_postalcode_pattern") ?>
  AND (COALESCE(cntctaddr.addr_postalcode,vendaddr.addr_postalcode,'') ~* <? value("addr_postalcode_pattern") ?>)
<? endif ?>
<? if exists("addr_country_pattern") ?>
  AND (COALESCE(cntctaddr.addr_country, vendaddr.addr_country,'') ~* <? value("addr_country_pattern") ?>)
<? endif ?>
<? literal("charClause") ?>
ORDER BY vend_number; 
