xx
Todo: change from br to p tags
alter view vOrderAddresses
as
-- 20180822 LSR
-- Added Post_excerpt first 20 chars(Customer comment added at checkout)
-- 20180807 LSR
-- Added maint <a> link
-- 20180805 LSR
-- Original
select ID
,concat('<a href="addressedit.php?order=',ID,'" target="_blank">OL', ID, '</a>') as edit_shipping_href
,case when concat(
coalesce(billing_first_name, '')
,coalesce(billing_last_name, '')
,coalesce(billing_company, '')
,coalesce(billing_address_1, '')
,coalesce(billing_address_2, '')
,coalesce(billing_city, '')
,coalesce(billing_postcode, '')
,coalesce(billing_state, '')
,coalesce(billing_country), '')
<> concat(
coalesce(shipping_first_name, '')
,coalesce(shipping_last_name, '')
,coalesce(shipping_company, '')
,coalesce(shipping_address_1, '')
,coalesce(shipping_address_2, '')
,coalesce(shipping_city, '')
,coalesce(shipping_postcode, '')
,coalesce(shipping_state, '')
,coalesce(shipping_country, '')) then 1 else 0 end as hasshiptoaddress
,concat(
case when coalesce(billing_first_name, '') <> '' then concat(billing_first_name, ' ') else '' end
,case when coalesce(billing_last_name, '') <> '' then concat(billing_last_name, '</br>') else '</br>' end
,case when coalesce(billing_company, '') <> '' then concat(billing_company, '</br>') else '' end
,case when coalesce(billing_address_1, '') <> '' then concat(billing_address_1, '</br>') else '' end
,case when coalesce(billing_address_2, '') <> '' then concat(billing_address_2, '</br>') else '' end
,case when coalesce(billing_city, '') <> '' then concat(billing_city, ' ') else '' end
,case when coalesce(shipping_postcode, '') <> '' then concat(shipping_postcode, '</br>') else '</br>' end
-- no state for now
,case when (coalesce(billing_country, '') <> ''
and billing_country not like 'nz'
and billing_country not like '%zealand') then billing_country else '' end) as billingaddresshtml
,concat(
case when coalesce(shipping_first_name, '') <> '' then concat(shipping_first_name, ' ') else '' end
,case when coalesce(shipping_last_name, '') <> '' then concat(shipping_last_name, '</br>') else '</br>' end
,case when coalesce(shipping_company, '') <> '' then concat(shipping_company, '</br>') else '' end
,case when coalesce(shipping_address_1, '') <> '' then concat(shipping_address_1, '</br>') else '' end
,case when coalesce(shipping_address_2, '') <> '' then concat(shipping_address_2, '</br>') else '' end
,case when coalesce(shipping_city, '') <> '' then concat(shipping_city, ' ') else '' end
,case when coalesce(shipping_postcode, '') <> '' then concat(shipping_postcode, '</br>') else '</br>' end
-- no state for now
,case when (coalesce(shipping_country, '') <> ''
and shipping_country not like 'nz'
and shipping_country not like '%zealand') then shipping_country else '' end) as shippingaddresshtml
,concat(left(post_excerpt, 20), case when char_length(post_excerpt) > 20 then '...' else '' end) as post_excerpt20
from vOrderMetaData;
xx