PIDs, LVA: NQ Cancer Type according to new intake Talcum fields

[jesusitd]

New SQL statement, with list of qualified Talcum Cancer Types as of today:

SELECT
    leads.id AS lead_id,
    -- leads.date_entered,
    -- id_intake_talc.talc_diagnosis_cancer_other_type,
    -- id_intake_talc.talc_diagnosis1_cancer_type,
    -- id_intake_talc.talc_diagnosis2_cancer_type,
    CASE
        WHEN lead_form_product_id = '601c66b4-4904-7dd0-a948-59e1332cf4c1' THEN REPLACE(
            COALESCE(
                id_intake_talc.talc_diagnosis_cancer_other_type,
                NULLIF(
                    CONCAT_WS(
                        ',',
                        CASE
                            WHEN id_intake_talc.talc_diagnosis1_cancer_type NOT IN (
                                'ovarian_cancer',
                                'serous_epithelial_ovca',
                                'serous_endometrioid_ovca',
                                'clear_cell_epithelial_ovca',
                                'invasive_fallopian_tube_cancer',
                                'borderline_mucinous_tumors',
                                -- 'endometrial_cancer',
                                -- 'uterine_cancer',
                                'invasive_well_differentiated_ovca',
                                'primary_peritoneal_cancer'
                            ) THEN CONCAT(
                                '^',
                                id_intake_talc.talc_diagnosis1_cancer_type,
                                '^'
                            )
                            ELSE NULL
                        END,
                        CASE
                            WHEN id_intake_talc.talc_diagnosis2_cancer_type NOT IN (
                                'ovarian_cancer',
                                'serous_epithelial_ovca',
                                'serous_endometrioid_ovca',
                                'clear_cell_epithelial_ovca',
                                'invasive_fallopian_tube_cancer',
                                'borderline_mucinous_tumors',
                                -- 'endometrial_cancer',
                                -- 'uterine_cancer',
                                'invasive_well_differentiated_ovca',
                                'primary_peritoneal_cancer'
                            ) THEN CONCAT(
                                '^',
                                id_intake_talc.talc_diagnosis2_cancer_type,
                                '^'
                            )
                            ELSE NULL
                        END
                    ),
                    ''
                ),
                '(No Input)'
            ),
            '_',
            ' '
        )
        ELSE NULL
    END AS 'Talcum',
    CASE
        WHEN lead_form_product_id = 'c4100f29-210a-342e-a146-5a15ab787997' THEN COALESCE (
            CASE
                WHEN NULLIF(leads.roundup_diagnosis_cancer_other_type, '') IS NULL
                AND NULLIF(leads.roundup_diagnosis3_cancer_type, '') IS NULL
                AND NULLIF(leads.roundup_diagnosis4_cancer_type, '') IS NULL THEN NULL
                WHEN NULLIF(leads.roundup_diagnosis_cancer_other_type, '') IS NOT NULL THEN REPLACE(
                    leads.roundup_diagnosis_cancer_other_type,
                    '_',
                    ' '
                )
                ELSE REPLACE(
                    CONCAT_WS(
                        ',',
                        CASE
                            WHEN leads.roundup_diagnosis3_cancer_type IS NOT NULL THEN CONCAT('^', leads.roundup_diagnosis3_cancer_type, '^')
                        END,
                        CASE
                            WHEN leads.roundup_diagnosis4_cancer_type IS NOT NULL THEN CONCAT('^', leads.roundup_diagnosis4_cancer_type, '^')
                        END
                    ),
                    '_',
                    ' '
                )
            END,
            '(No Input)'
        )
        ELSE NULL
    END AS 'Roundup'
FROM
    leads
    JOIN leads_cstm ON leads.id = leads_cstm.id_c -- AND leads_cstm.pid_c = '3000'
    LEFT JOIN id_intake_talc ON leads.id = id_intake_talc.lead_id
WHERE
    leads.deleted = 0
    AND (
        (
            leads.lead_form_product_id = 'c4100f29-210a-342e-a146-5a15ab787997'
            AND leads.roundup_decline_reason = 'non_qualifying_cancer_type'
        )
        OR (
            leads.lead_form_product_id = '601c66b4-4904-7dd0-a948-59e1332cf4c1'
            AND leads.talcum_powder_decline_reason = 'non_qualifying_cancer_type'
        )
    )
ORDER BY
    leads.date_entered DESC;

[jesusitd]

PID 8000: Added Borderline Mucinous Tumors and Invasive / Well-diff Mucinous OVCA as qualified cancers.

PID 2000 / 3000: Added Borderline Mucinous Tumors as qualified cancer.

[jesusitd]

Added Non-Qualifying Cancer Types tables to the 3 PID reports, Lead Form Talcum tab: