""" Factory functions for SQL queries used in the consumer lambda. """ # flake8: noqa: E501 from common.src.typings import SqlString, OrchLabelId from .enums import ColumnAlias from ..typings import PeriodId # noinspection SqlNoDataSourceInspection def royalties_by_composer(label_id: OrchLabelId, period_id: PeriodId) -> SqlString: """Query to get royalties by composer. Based on the Look #23866 https://theorchard.looker.com/looks/23866?toggle=dat,fil,pik,vis """ return SqlString( f""" WITH royalty_accounting_prod_vw_abacus_fact_sales_publishing AS (WITH Composers AS ( SELECT phc.label_id, pub_song_id, pc.id AS composition_id, psw.pub_writer_id, pa.controlled, psw.legal_name, psw.pro, psw.ipi, pca.split / 100 AS split, IFF(pa.controlled, pca.split, 0) AS _split_controlled, SUM(_split_controlled/100) OVER(PARTITION BY label_id,pub_song_id) AS total_controlled_pct, IFF(total_controlled_pct = 0, 0, _split_controlled/total_controlled_pct)/100 AS split_controlled FROM FACTS.PROD.PUBLISHING_COMPOSITION pc LEFT JOIN FACTS.PROD.PUBLISHING_COMPOSITION_AGREEMENT pca ON pc.id = pca.composition_id LEFT JOIN FACTS.PROD.PUBLISHING_SONGWRITER_AGREEMENT psa ON pca.agreement_id = psa.agreement_id LEFT JOIN FACTS.PROD.PUBLISHING_SONG_WRITER psw ON psa.songwriter_id = psw.id LEFT JOIN FACTS.PROD.PUBLISHING_AGREEMENT pa ON pca.agreement_id = pa.id LEFT JOIN FACTS.PROD.PUBLISHING_HAS_COMPOSITION phc ON pca.composition_id = phc.composition_id ) SELECT ROW_NUMBER() OVER (ORDER BY C.pub_writer_id) AS _pk_row_id, C.*, P.* FROM COMPOSERS C RIGHT JOIN ROYALTY_ACCOUNTING.PROD.VW_ABACUS_FACT_SALES_PUBLISHING P ON P.external_song_id = C.pub_song_id AND P.account_id = C.label_id WHERE C.pub_writer_id IS NOT NULL -- Prevents "Controlled: yes/no" cartesian joins in certain cases ) SELECT royalty_accounting_prod_vw_abacus_fact_sales_publishing.legal_name AS {ColumnAlias.LEGAL_NAME}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.pub_writer_id AS {ColumnAlias.PUB_WRITER_ID}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.ipi AS {ColumnAlias.IPI}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.pro AS {ColumnAlias.PRO}, COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE( royalty_accounting_prod_vw_abacus_fact_sales_publishing.adjusted_gross * royalty_accounting_prod_vw_abacus_fact_sales_publishing.split_controlled ,0)*(1000000*1.0)) AS DECIMAL(38,0))) + (TO_NUMBER(MD5( royalty_accounting_prod_vw_abacus_fact_sales_publishing._pk_row_id ), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') % 1.0e27)::NUMERIC(38, 0) ) - SUM(DISTINCT (TO_NUMBER(MD5( royalty_accounting_prod_vw_abacus_fact_sales_publishing._pk_row_id ), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') % 1.0e27)::NUMERIC(38, 0)) ) AS DOUBLE PRECISION) / CAST((1000000*1.0) AS DOUBLE PRECISION), 0) AS {ColumnAlias.ADJUSTED_GROSS}, COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE( royalty_accounting_prod_vw_abacus_fact_sales_publishing.net_revenue * royalty_accounting_prod_vw_abacus_fact_sales_publishing.split_controlled ,0)*(1000000*1.0)) AS DECIMAL(38,0))) + (TO_NUMBER(MD5( royalty_accounting_prod_vw_abacus_fact_sales_publishing._pk_row_id ), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') % 1.0e27)::NUMERIC(38, 0) ) - SUM(DISTINCT (TO_NUMBER(MD5( royalty_accounting_prod_vw_abacus_fact_sales_publishing._pk_row_id ), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') % 1.0e27)::NUMERIC(38, 0)) ) AS DOUBLE PRECISION) / CAST((1000000*1.0) AS DOUBLE PRECISION), 0) AS {ColumnAlias.NET_REVENUE}, COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE( royalty_accounting_prod_vw_abacus_fact_sales_publishing.net_revenue_preferred_currency * royalty_accounting_prod_vw_abacus_fact_sales_publishing.split_controlled ,0)*(1000000*1.0)) AS DECIMAL(38,0))) + (TO_NUMBER(MD5( royalty_accounting_prod_vw_abacus_fact_sales_publishing._pk_row_id ), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') % 1.0e27)::NUMERIC(38, 0) ) - SUM(DISTINCT (TO_NUMBER(MD5( royalty_accounting_prod_vw_abacus_fact_sales_publishing._pk_row_id ), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') % 1.0e27)::NUMERIC(38, 0)) ) AS DOUBLE PRECISION) / CAST((1000000*1.0) AS DOUBLE PRECISION), 0) AS {ColumnAlias.NET_REVENUE_PREFERRED_CURRENCY} FROM royalty_accounting_prod_vw_abacus_fact_sales_publishing WHERE (royalty_accounting_prod_vw_abacus_fact_sales_publishing.account_id ) = {label_id} AND royalty_accounting_prod_vw_abacus_fact_sales_publishing.controlled AND royalty_accounting_prod_vw_abacus_fact_sales_publishing.period_id = {period_id} GROUP BY 1, 2, 3, 4 """ ) # noinspection SqlNoDataSourceInspection def full_detail(label_id: OrchLabelId, period_id: PeriodId) -> SqlString: """Query to get full detail. Based on the Look #23773 https://theorchard.looker.com/looks/23773?toggle=dat,fil,pik,vis """ return SqlString( f""" WITH publishing_metadata_simplified AS (with base as ( SELECT DISTINCT hc.label_id as label_id, v.company as label_name, pc.delivered, pc.delivery_date, pc.title, pc.id, pc.alternate_titles, pc.pub_song_id, pc.iswc, pc.created_at, pc.submitted_at, psw.legal_name as composer_name, psw.pro as composer_pro, psw.pub_writer_id as composer_pub_writer_id, psw.ipi as composer_ipi, pca.has_lyrics_contribution as composer_lyrics_contribution, pca.has_music_contribution as composer_music_contribution, --CASE --WHEN pca.has_lyrics_contribution = 'true' AND pca.has_music_contribution = 'true' THEN 'CA' --WHEN pca.has_lyrics_contribution = 'true' AND pca.has_music_contribution = 'false' THEN 'C' --WHEN pca.has_lyrics_contribution = 'false' AND pca.has_music_contribution = 'true' THEN 'A' --ELSE '' --END AS composer_capacity, pca.split as composer_split, pa.controlled as composer_controlled, pp.name as publisher_name, pp.pro as publisher_pro, pp.pub_publisher_id publisher_id, pp.ipi as publisher_ipi FROM facts.prod.publishing_composition pc INNER JOIN facts.prod.publishing_has_composition hc on pc.id = hc.composition_id INNER JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v ON hc.label_id = v.vendor_id INNER JOIN facts.prod.publishing_composition_agreement pca on pca.composition_id = pc.id INNER JOIN facts.prod.publishing_agreement pa on pa.id = pca.agreement_id INNER JOIN facts.prod.publishing_songwriter_agreement psa on psa.agreement_id = pa.id INNER JOIN facts.prod.publishing_song_writer psw on psw.id = psa.songwriter_id LEFT JOIN facts.prod.publishing_publisher_agreement ppa on ppa.agreement_id = pa.id LEFT JOIN facts.prod.publishing_publisher pp on pp.id = ppa.publisher_id LEFT JOIN facts.prod.publishing_has_sound_recording hgs ON pc.id = hgs.composition_id LEFT JOIN facts.prod.global_sound_recording gs ON hgs.global_sound_recording_id = gs.id LEFT JOIN facts.prod.publishing_has_label_sound_recording hls ON pc.id = hls.composition_id LEFT JOIN facts.prod.label_sound_recording ls ON hls.label_sound_recording_id = ls.id ) select label_id, label_name, delivered, delivery_date, title, id, alternate_titles, pub_song_id, iswc, created_at, submitted_at, listagg(composer_name,', ') as composer_names, --listagg(composer_capacity,', ') as composer_capacities, listagg(composer_pro,', ') as composer_pros, listagg(composer_pub_writer_id,', ') as composer_pub_writer_ids, listagg(composer_ipi,', ') as composer_ipis, listagg(composer_lyrics_contribution,', ') as composer_lyrics_contributions, listagg(composer_music_contribution,', ') as composer_music_contributions, listagg(composer_split,', ') as composer_splits, listagg(composer_controlled,', ') as composer_controlled, listagg(publisher_name,', ') as publisher_names, listagg(publisher_pro,', ') as publisher_pros, listagg(publisher_id,', ') as publisher_ids, listagg(publisher_ipi,', ') as publisher_ipis from base group by 1,2,3,4,5,6,7,8,9,10,11 ) SELECT royalty_accounting_prod_vw_abacus_fact_sales_publishing.song_no AS {ColumnAlias.SONG_NO}, publishing_metadata_simplified.title AS {ColumnAlias.SONG}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.writer AS {ColumnAlias.WRITER}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.source_name AS {ColumnAlias.SOURCE1_NAME}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.source_country AS {ColumnAlias.SOURCE1_COUNTRY}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.source2_name AS {ColumnAlias.SOURCE2_NAME}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.source2_country AS {ColumnAlias.SOURCE2_COUNTRY}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.source3_name AS {ColumnAlias.SOURCE3_NAME}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.source3_country AS {ColumnAlias.SOURCE3_COUNTRY}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.source4_name AS {ColumnAlias.SOURCE4_NAME}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.source4_country AS {ColumnAlias.SOURCE4_COUNTRY}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.income_type AS {ColumnAlias.INCOME_TYPE}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.sh_id AS {ColumnAlias.SH_ID}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.rptg_pd AS {ColumnAlias.REPORTING_PERIOD}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.sales_pd AS {ColumnAlias.SALES_PERIOD}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.product_number AS {ColumnAlias.PRODUCT_NUMBER}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.artist_product_number AS {ColumnAlias.ARTIST_PRODUCT_NUMBER}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.song_share_percent AS {ColumnAlias.SONG_SHARE_PCT}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.control_percent AS {ColumnAlias.CONTROL_PCT}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.df AS {ColumnAlias.DF}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.source_product AS {ColumnAlias.SOURCE_PRODUCT}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.iswc AS {ColumnAlias.ISWC_CD}, publishing_metadata_simplified.pub_song_id AS {ColumnAlias.PUB_SONG_ID}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.artist AS {ColumnAlias.ARTIST}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.source_song AS {ColumnAlias.SOURCE_SONG}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.isrc AS {ColumnAlias.ISRC}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.account_id AS {ColumnAlias.VENDOR_ID}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.fee_percent AS {ColumnAlias.FEE_PCT}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.preferred_currency AS {ColumnAlias.PREFERRED_CURRENCY}, royalty_accounting_prod_vw_abacus_fact_sales_publishing.currency_conversion_rate AS {ColumnAlias.CURRENCY_CONVERSION_RATE}, COALESCE(SUM(royalty_accounting_prod_vw_abacus_fact_sales_publishing.units ), 0) AS {ColumnAlias.UNITS}, COALESCE(SUM(royalty_accounting_prod_vw_abacus_fact_sales_publishing.amount ), 0) AS {ColumnAlias.AMOUNT}, COALESCE(SUM(royalty_accounting_prod_vw_abacus_fact_sales_publishing.adjusted_gross ), 0) AS {ColumnAlias.ADJUSTED_GROSS}, COALESCE(SUM(royalty_accounting_prod_vw_abacus_fact_sales_publishing.net_revenue ), 0) AS {ColumnAlias.NET_REVENUE}, COALESCE(SUM(royalty_accounting_prod_vw_abacus_fact_sales_publishing.net_revenue_preferred_currency ), 0) AS {ColumnAlias.NET_REVENUE_PREFERRED_CURRENCY} FROM ROYALTY_ACCOUNTING.PROD.VW_ABACUS_FACT_SALES_PUBLISHING AS royalty_accounting_prod_vw_abacus_fact_sales_publishing LEFT JOIN publishing_metadata_simplified ON royalty_accounting_prod_vw_abacus_fact_sales_publishing.external_song_id = publishing_metadata_simplified.pub_song_id and royalty_accounting_prod_vw_abacus_fact_sales_publishing.account_id = publishing_metadata_simplified.label_id WHERE royalty_accounting_prod_vw_abacus_fact_sales_publishing.account_id = {label_id} AND royalty_accounting_prod_vw_abacus_fact_sales_publishing.period_id = {period_id} GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30 """ )