Make endpoint for coverage dates and summaries (financial_summary, coverage_date)

[lbeaufort]

Summary

Design ticket: #4175

2) /presidential/financial_summary/

(right side summary; Expenditure Tab ):
(view on DISCLOSURE.PRES_F3P_TOTALS_CA_CM_LINK_20D / _16)
Filter: candidate_id, election_year

-- TODO: Determine financial summary fields, use readable column names. Maybe bring them all over as readable columns in case we need others later

SELECT distinct 
cand_id AS candidate_id, 
cand_nm AS candidate_name,
SUBSTR(cand_nm, 0, strpos(cand_nm,',')) AS candidate_last_name, 
(CASE WHEN cand_pty_affiliation IS NULL THEN NULL ELSE '('||SUBSTR(CAND_PTY_AFFILIATION,1,1)||')' END) AS candidate_party_affiliation, 
*
FROM DISCLOSURE.PRES_F3P_TOTALS_CA_CM_LINK_20D
ORDER BY 4 DESC

5) /presidential/coverage_date/

(dateThrough) (DISCLOSURE.PRES_NML_FORM_3P_20D / _16)
Filter: candidate_id, election_year

--TODO: Bring in 2016 data

select cand_id AS candidate_id,
to_char(max(cvg_end_dt), 'mm/dd/yyyy') as coverage_date,
'2020' as election_year
from DISCLOSURE.PRES_NML_FORM_3P_20D

Completion criteria

• Make views in database with migration (pair w/DB team)
• Make endpoints based on design
• Use a feature flag
• Add test coverage for new endpoints

[fec-jli]

query for expenditure tab:
SELECT
ROUND(SUM(NVL(OP_EXP_PER,0))) as oper_exp,
ROUND(SUM(NVL(TRANF_TO_OTHER_AUTH_CMTE_PER,0))) as transfer_to_auth_cmte,
ROUND(SUM(NVL(FNDRSG_DISB_PER,0)))as fundraising_exp,
ROUND(SUM(NVL(EXEMPT_LEGAL_ACCTG_DISB_PER,0))) as exmpt_legal_acct_disb,
ROUND(SUM(NVL(TTL_LOAN_REPYMTS_MADE_PER,0))) as total_loan_repayments,
ROUND(SUM(NVL(REPYMTS_LOANS_MADE_BY_CAND_PER,0))) as loan_by_candidate,
ROUND(SUM(NVL(REPYMTS_OTHER_LOANS_PER,0))) as loan_by_other,
ROUND(SUM(NVL(OTHER_DISB_PER,0))) as other_disb,
ROUND(SUM(NVL(OFFSETS_TO_OP_EXP_PER,0))) as offset_to_exp,
ROUND(SUM(NVL(TTL_CONTB_REF_PER,0))) as contb_refunds,
ROUND(SUM(NVL(DEBTS_OWED_BY_CMTE,0))) as debts_owned_by
FROM DISCLOSURE.PRES_F3P_TOTALS_CA_CM_LINK_20D
WHERE CAND_ID=#{candId}

[fec-jli]

query for summary tab:
SELECT DECODE(SUBSTR(CAND_NM,LENGTH(CAND_NM),LENGTH(CAND_NM)-1),',',SUBSTR(CAND_NM,1,LENGTH(CAND_NM)-1),CAND_NM) cand_nm_title,
round(INDV_CONTB_PER - REF_INDV_CONTB_PER)AS ind_contrib,
round(OTHER_POL_CMTE_CONTB_PER - REF_OTHER_POL_CMTE_CONTB_PER) AS pac_contrib,
round(POL_PTY_CMTE_CONTB_PER -REF_POL_PTY_CMTE_CONTB_PER) AS party_contrib,
round(CAND_CONTB_PER + LOANS_RECEIVED_FROM_CAND_PER - REPYMTS_LOANS_MADE_BY_CAND_PER) AS cand_contrib,
round(FED_FUNDS_PER) AS fed_funds,
round(TRANF_FROM_AFFILATED_CMTE_PER) AS transfers_in,
round((OP_EXP_PER -OFFSETS_TO_OP_EXP_PER)+(FNDRSG_DISB_PER-OFFSETS_TO_FNDRSG_EXP_PER)+(EXEMPT_LEGAL_ACCTG_DISB_PER - OFFSETS_TO_LEGAL_ACCTG_PER) + OTHER_DISB_PER) AS disbursements,
round(coh_cop) AS coh_cop,
cmte_id AS cmte_id,
mst_rct_rpt_yr AS rpt_yr,
mst_rct_rpt_tp AS rpt_tp
FROM DISCLOSURE.PRES_F3P_TOTALS_CA_CM_LINK_20D
WHERE cand_id=#{candId}