Woocommerce -> MySql -> vOrderAddresses

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