stagfe2/app/Http/Controllers/FDBPGController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class FDBPGController extends Controller
{

    public function rx(Request $request)
    {
        return view('app.fdb-pg.fdb-rx');
    }

    public function medSuggest(Request $request)
    {
        $term = $request->input('term') ? trim($request->input('term')) : '';
        if (empty($term)) return '';
        $matches = DB::connection('pgsql_fdb')->select(
            "SELECT med_name_id, med_name FROM rminmid1_med_name WHERE med_status_cd = '0' AND med_name ILIKE :term ORDER BY med_name",
            ['term' => '%' . $term . '%']
        );
        return view('app.fdb-pg.fdb-med-suggest', compact('matches'));
    }

    public function routedMeds(Request $request)
    {
        $medNameID = $request->input('med-name-id') ? trim($request->input('med-name-id')) : '';
        if (empty($medNameID)) return '';
        $matches = DB::connection('pgsql_fdb')->select(
            "SELECT routed_med_id, med_routed_med_id_desc FROM rmirmid1_routed_med WHERE med_status_cd = '0' AND med_name_id = :medNameID ORDER BY med_routed_med_id_desc",
            ['medNameID' => $medNameID]
        );
        return json_encode($matches);
    }

    public function routedDosages(Request $request)
    {
        $routedMedID = $request->input('routed-med-id') ? trim($request->input('routed-med-id')) : '';
        if (empty($routedMedID)) return '';
        $matches = DB::connection('pgsql_fdb')->select(
            "SELECT routed_dosage_form_med_id, med_routed_df_med_id_desc FROM rmidfid1_routed_dose_form_med WHERE med_status_cd = '0' AND routed_med_id = :routedMedID ORDER BY med_routed_df_med_id_desc",
            ['routedMedID' => $routedMedID]
        );
        return json_encode($matches);
    }

    public function meds(Request $request)
    {
        $dosageFormMedId = $request->input('dosage-form-med-id') ? trim($request->input('dosage-form-med-id')) : '';
        if (empty($dosageFormMedId)) return '';
        $matches = DB::connection('pgsql_fdb')->select(
            "SELECT medid, med_medid_desc, gcn_seqno FROM rmiid1_med WHERE med_status_cd = '0' AND routed_dosage_form_med_id = :dosageFormMedId ORDER BY med_medid_desc",
            ['dosageFormMedId' => $dosageFormMedId]
        );
        return json_encode($matches);
    }

    public function sideEffects(Request $request)
    {
        $gcnSeqNo = $request->input('gcn-seq-no') ? trim($request->input('gcn-seq-no')) : '';
        if (empty($gcnSeqNo)) return '';
        $sides = DB::connection('pgsql_fdb')->select("
SELECT r1.side, sm.side_freq, sm.side_sev, sm.dxid, dx.dxid_desc56 
FROM rsidegc0_gcnseqno_link r1 
    JOIN rsidema3_mstr sm ON r1.side = sm.side
    JOIN rfmldx0_dxid dx ON sm.dxid = dx.dxid
WHERE r1.gcn_seqno = :gcnSeqNo
ORDER BY sm.side_sev DESC, sm.side_freq ASC
            ",
            ['gcnSeqNo' => $gcnSeqNo]
        );
        return view('app.fdb-pg.fdb-side-effects', compact('sides'));
    }

    public function geriatricPrecautions(Request $request)
    {
        $gcnSeqNo = $request->input('gcn-seq-no') ? trim($request->input('gcn-seq-no')) : '';
        if (empty($gcnSeqNo)) return '';
        $precautions = DB::connection('pgsql_fdb')->select("
SELECT r1.geri_code, gm.geri_sl, gm.geri_desc, gm.geri_narrative
FROM rgerigc0_geri_gcnseqno_link r1 
    JOIN rgerima1_geri_mstr gm ON r1.geri_code = gm.geri_code
WHERE r1.gcn_seqno = :gcnSeqNo
ORDER BY gm.geri_desc
            ",
            ['gcnSeqNo' => $gcnSeqNo]
        );
        return view('app.fdb-pg.fdb-geriatric-precautions', compact('precautions'));
    }

    public function indications(Request $request)
    {
        $gcnSeqNo = $request->input('gcn-seq-no') ? trim($request->input('gcn-seq-no')) : '';
        if (empty($gcnSeqNo)) return '';
        $indications = DB::connection('pgsql_fdb')->select("
SELECT r1.indcts, r2.indcts_sn, r2.indcts_lbl, r2.dxid, r2.proxy_ind, r3.dxid_desc56
FROM rindmgc0_indcts_gcnseqno_link r1 
    JOIN rindmma2_indcts_mstr r2 ON r1.indcts = r2.indcts
    JOIN rfmldx0_dxid r3 ON r2.dxid = r3.dxid
WHERE r1.gcn_seqno = :gcnSeqNo
ORDER BY r3.dxid_desc56
            ",
            ['gcnSeqNo' => $gcnSeqNo]
        );
        return view('app.fdb-pg.fdb-indications', compact('indications'));
    }

    public function contraindications(Request $request)
    {
        $routedMedID = $request->input('routed-med-id') ? trim($request->input('routed-med-id')) : '';
        if (empty($routedMedID)) return '';
        $contraindications = DB::connection('pgsql_fdb')->select("
SELECT r1.ddxcn, r2.dxid, r2.ddxcn_sl, r3.dxid_desc56
FROM rddcmrm0_routed_med_link r1 
    JOIN rddcmma1_contra_mstr r2 ON r1.ddxcn = r2.ddxcn
    JOIN rfmldx0_dxid r3 ON r2.dxid = r3.dxid
WHERE r1.routed_med_id = :routedMedID
ORDER BY r2.ddxcn_sl
            ",
            ['routedMedID' => $routedMedID]
        );
        return view('app.fdb-pg.fdb-contraindications', compact('contraindications'));
    }

    public function dxSuggest(Request $request)
    {
        $term = $request->input('term') ? trim($request->input('term')) : '';
        if (empty($term)) return '';
        $matches = DB::connection('pgsql_fdb')->select("
SELECT distinct(r1.dxid), r1.dxid_desc56 
FROM rfmldx0_dxid r1
JOIN rfmlsyn0_dxid_syn r2 ON r1.dxid = r2.dxid
WHERE (r1.dxid_desc56 ILIKE :term OR r1.dxid_desc100 ILIKE :term OR r2.dxid_syn_desc56 ILIKE :term OR r2.dxid_syn_desc100 ILIKE :term)
ORDER BY r1.dxid_desc56
",
            ['term' => '%' . $term . '%']
        );
        return view('app.fdb-pg.fdb-dx-suggest', compact('matches'));
    }

    public function allergySuggest(Request $request)
    {
        $term = $request->input('term') ? trim($request->input('term')) : '';
        if (empty($term)) return '';
        $matches = DB::connection('pgsql_fdb')->select("
SELECT r1.dam_concept_id, r1.dam_concept_id_typ, r1.dam_concept_id_desc
FROM rdamca0_concept r1
WHERE (r1.dam_concept_id_desc ILIKE :term)
ORDER BY r1.dam_concept_id_desc
",
            ['term' => '%' . $term . '%']
        );
        return view('app.fdb-pg.fdb-allergy-suggest', compact('matches'));
    }

    public function drugAllergies(Request $request) {

        // override
        if($request->input('test')) {
            $x = new \stdClass();
            $x->allergen = 'Pollen';
            $x->dam_concept_id_typ = 6;
            $allergies = [$x];
            $y = new \stdClass();
            $y->rx = 'Brufen';
            $rx = [$y];
        }
        else {
            $input = json_decode($request->input('data'));
            $allergies = $input->allergies;
            $rx = $input->rx;
        }

        $output = [];

        /*
        for each allergy
            if dam_concept_id_typ = 1 // allergen-group-id
                // https://docs.fdbhealth.com/display/MKDOCUS/Screening+an+NDC+for+a+DAM_ALRGN_GRP+Allergen+-+Illustration+of+Scenario+C
                ...
            elseif dam_concept_id_typ = 2 // medication-name-id
                // https://docs.fdbhealth.com/display/MKDOCUS/Screening+an+NDC+for+a+MED_NAME_ID+Allergen+-+Illustration+of+Scenario+B
                ...
            elseif dam_concept_id_typ = 6 // base-ingredient-id
                // https://docs.fdbhealth.com/display/MKDOCUS/Screening+an+NDC+for+an+Ingredient+Allergen+-+Illustration+of+Scenario+A
                ...
            endif
        endfor
        */

        foreach ($allergies as $allergy) {
            foreach ($rx as $rxItem) {
                if($allergy->dam_concept_id_typ == 6) { // ingredient
                    if ($this->drugAllergyIngredientAllergenVsSingleRx($allergy, $rxItem)) {
                        $output[] = "<b>{$rxItem->rx}</b> can cause allergic reactions since the patient is allergic to <b>{$allergy->allergen}</b>.";
                    }
                }
                else if($allergy->dam_concept_id_typ == 2) { // medication
                    if ($this->drugAllergyMedicationAllergenVsSingleRx($allergy, $rxItem)) {
                        $output[] = "<b>{$rxItem->rx}</b> can cause allergic reactions since the patient is allergic to <b>{$allergy->allergen}</b>.";
                    }
                }
                else if($allergy->dam_concept_id_typ == 1) { // allergen group
                    if ($this->drugAllergyGroupAllergenVsSingleRx($allergy, $rxItem)) {
                        $output[] = "<b>{$rxItem->rx}</b> can cause allergic reactions since the patient is allergic to <b>{$allergy->allergen}</b>.";
                    }
                }
            }
        }

        return implode("\n", $output);

    }

    private function drugAllergyIngredientAllergenVsSingleRx($_allergen, $_rx) {

        $matches = DB::connection('pgsql_fdb')->select("
(
    -- ingredients from medication
    SELECT R1.related_hic_seqn as hic_seqn, R2.hic_desc
    FROM RHICHCR0_HIC_HIC_LINK R1
             JOIN RHICD5_HIC_DESC R2 ON R1.related_hic_seqn = R2.hic_seqn
    WHERE R1.hic_seqn IN (
        SELECT S2.dam_alrgn_hic_seqn
        FROM RMEDMHL0_MED_HICLSEQNO_LINK S1
                 JOIN RDAMHHA0_HIC_HICL_ALG_LINK S2 ON S1.hicl_seqno = S2.hicl_seqno
        WHERE S1.med_concept_id = :medid
          AND S1.med_concept_id_typ = 3
    )
)
INTERSECT
(
    -- all ingredients directly and related from allergens
    (
        SELECT R1.related_hic_seqn as hic_seqn, R2.hic_desc
        FROM RHICHCR0_HIC_HIC_LINK R1
                 JOIN RHICD5_HIC_DESC R2 ON R1.related_hic_seqn = R2.hic_seqn
        WHERE R1.hic_seqn = :allergenConceptID
    )
    UNION
    -- all ingredients via related dam allergen groups
    (
        SELECT r3.hic_seqn, r4.hic_desc
        FROM RDAMGHC0_HIC_ALRGN_GRP_LINK R1
                 JOIN rdamagd1_alrgn_grp_desc R2 on R1.dam_alrgn_grp = R2.dam_alrgn_grp
                 JOIN RDAMGHC0_HIC_ALRGN_GRP_LINK R3 on R1.dam_alrgn_grp = R3.dam_alrgn_grp
                 JOIN RHICD5_HIC_DESC R4 on r3.hic_seqn = r4.hic_seqn
        WHERE R1.hic_seqn = :allergenConceptID
          AND R2.dam_alrgn_grp_status_cd = 0
        ORDER BY r3.hic_seqn
    )
)
",
            ['medid' => $_rx->medid, 'allergenConceptID' => $_allergen->dam_concept_id]
        );

        return !!count($matches);
    }

    private function drugAllergyMedicationAllergenVsSingleRx($_allergen, $_rx) {

        $matches = DB::connection('pgsql_fdb')->select("
(
    -- ingredients from medication
    SELECT R1.related_hic_seqn as hic_seqn
    FROM RHICHCR0_HIC_HIC_LINK R1
             JOIN RHICD5_HIC_DESC R2 ON R1.related_hic_seqn = R2.hic_seqn
    WHERE R1.hic_seqn IN (
        SELECT S2.dam_alrgn_hic_seqn
        FROM RMEDMHL0_MED_HICLSEQNO_LINK S1
                 JOIN RDAMHHA0_HIC_HICL_ALG_LINK S2 ON S1.hicl_seqno = S2.hicl_seqno
        WHERE S1.med_concept_id = :medid
          AND S1.med_concept_id_typ = 3
    )
)
INTERSECT
(
    -- all ingredients directly and related from allergens
    SELECT R1.dam_alrgn_hic_seqn as hic_seqn
    FROM RDAMHHA0_HIC_HICL_ALG_LINK R1
    WHERE R1.hicl_seqno IN (
        SELECT R1.hicl_seqno
        FROM RMEDMHL0_MED_HICLSEQNO_LINK R1
        WHERE R1.med_concept_id_typ = 1
          AND R1.med_concept_id = :allergenConceptID
          AND MED_CONCEPT_HICL_SRC_CD = 0
    )
)
",
            ['medid' => $_rx->medid, 'allergenConceptID' => $_allergen->dam_concept_id]
        );

        return !!count($matches);
    }

    private function drugAllergyGroupAllergenVsSingleRx($_allergen, $_rx) {

        $matches = DB::connection('pgsql_fdb')->select("
(
    -- ingredients from medication
    SELECT R1.related_hic_seqn as hic_seqn
    FROM RHICHCR0_HIC_HIC_LINK R1
             JOIN RHICD5_HIC_DESC R2 ON R1.related_hic_seqn = R2.hic_seqn
    WHERE R1.hic_seqn IN (
        SELECT S2.dam_alrgn_hic_seqn
        FROM RMEDMHL0_MED_HICLSEQNO_LINK S1
                 JOIN RDAMHHA0_HIC_HICL_ALG_LINK S2 ON S1.hicl_seqno = S2.hicl_seqno
        WHERE S1.med_concept_id = :medid
          AND S1.med_concept_id_typ = 3
    )
)
INTERSECT
(
    -- ingredients from medication allergen
    (
        SELECT R1.hic_seqn as hic_seqn
        FROM RDAMGHC0_HIC_ALRGN_GRP_LINK R1
        WHERE R1.DAM_ALRGN_GRP = :allergenConceptID
    )
    UNION
    (
        SELECT distinct s1.hic_seqn
        FROM RDAMXHC0_HIC_ALRGN_XSENSE_LINK S1
        WHERE S1.dam_alrgn_xsense IN (
            SELECT R1.dam_alrgn_xsense
            FROM RDAMGX0_ALRGN_GRP_XSENSE_LINK R1
                     JOIN RDAMCSD1_XSENSIT_ALLERGY_DESC R2 on R1.dam_alrgn_xsense = R2.dam_alrgn_xsense
            WHERE R1.dam_alrgn_grp = :allergenConceptID
              AND R2.dam_alrgn_xsense_status_cd = 0
        )
    )
)
",
            ['medid' => $_rx->medid, 'allergenConceptID' => $_allergen->dam_concept_id]
        );

        return !!count($matches);
    }
}