copy(
select
min(t."LabelName") as labelname,
min(t."ISRC") as "ISRC",
min(t."TrackArtist") as "Band / Artist Name",
min(
case
when t."TrackVersion" like '-' then t."TrackTitle"
else t."TrackTitle" || ' (' || t."TrackVersion" || ')'
end
)as "Recording Title",
'' as "Version Type",
'no' as "Is Remastered",
min(t.genre_name) as "Genre",
'Audio' as "Content Type",
min(substring(t.release_date, 0,5)) as "(P)Date (YYYY)",
min(t.p_line) as "(P)Name",
'United Kingdom' as "Primary Country of Recording",
'United Kingdom' as "Country of Commissioning",
'' as "Alternative Title",
min(substring(t."TrackDuration", 4,6)) as "Duration (mm:ss)",
'' as "Explicit",
min(substring(t.release_date, 9,2) || '/' || substring(t.release_date, 6,2) || '/' || substring(t.release_date, 0,5)) as "Recording Date (DD/MM/YYYY)",
'' as "Language",
'' as "Country of First Publication",
'' as "Recording Venue",
'' as "Local Recording ID",
'' as "This Recording Uses a Sample",
'' as "This Recording Is a Medley",
'' as "My Line-up Default",
min(cardinality(string_to_array(t."TrackArtistAdd", ';'))) as "Number of Featured Performers",
'0' as "Number of Non-Featured Performers",
'yes' as "I Own The Rights to This Recording",
'104113014' as "Rightsholder ID 1",
'Exclusive Licensee' as "Rights Ownership Type 1",
'PPL Territories' as "Rights Country Default 1",
min(substring(t.release_date, 9,2) || '/' || substring(t.release_date, 6,2) || '/' || substring(t.release_date, 0,5)) as "Rights Begin Date 1",
'' as "Rights End Date 1",
'100' as "Rights % 1",
'' as "Rightsholder ID 2",
'' as "Rights Ownership Type 2",
'' as "Rights Country Default 2",
'' as "Rights Begin Date 2",
'' as "Rights End Date 2",
'' as "Rights % 2",
'' as "Rightsholder ID 3",
'' as "Rights Ownership Type 3",
'' as "Rights Country Default 3",
'' as "Rights Begin Date 3",
'' as "Rights End Date 3",
'' as "Rights % 3",
'' as "ightsholder ID 4",
'' as "Rights Ownership Type 4",
'' as "Rights Country Default 4",
'' as "Rights Begin Date 4",
'' as "Rights End Date 4",
'' as "Rights % 4",
'' as "Rightsholder ID 5",
'' as "Rights Ownership Type 5",
'' as "Rights Country Default 5",
'' as "Rights Begin Date 5",
'' as "Rights End Date 5",
'' as "Rights % 5",
'' as "Rightsholder ID 6",
'' as "Rights Ownership Type 6",
'' as "Rights Country Default 6",
'' as "Rights Begin Date 6",
'' as "Rights End Date 6",
'' as "Rights % 6"
from
(
select * from mmpfull as mmp
inner join
(select *
from details_releases as release
left join details_products as product
on release.release_id = product.release_id
left join details_product_tracks as prodtrack
on product.product_id = prodtrack.product_id
left join details_tracks as track
on prodtrack.track_id = track.track_id
) as details
on details.isrc = mmp."ISRC" and
(trim( leading '0' from details.barcode) = trim( leading '0' from mmp."AlbumEAN_UPC"::text))
left join
(
select
max(genre_name) as genre_name,
translation_ems
from genrecodes
group by translation_ems
) as genrecodes
on mmp."TrackGenre"::text = genrecodes.translation_ems
) as t
where
"ISRC" is not null and
release_type != 'compilation' and
(format_name = 'Digital Album' or format_name = 'Digital Single' or format_name = 'Digital One Track') and
"LabelID" = any(array['101a0','101a129','101a64','101a114','101a2','101a154','101a117','101a189','101a171','101a175','101a152','101a170','101a136','101a120','101a140','101a122','101a49','101a115','101a141','101a177','101a131','101a155','101a181','101a167','101a180','101a172','101a178','101a113','101a119','101a128','101a133','101a207','101a95','101a98','101a153','101a148','101a163','101a144','101a116','101a157','101a160','101a143','101a69','101a42','101a182','101a179','101a127','101a19','101a104']) and
"ISRC" != all(select isrc from ppl_complete)
group by
"ISRC"
) to '/tmp/20200110_ppl_export.csv' csv header