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');
    }

    // 1. medication suggest
    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'));
    }

    // 1.1 medication suggest (json response)
    public function medSuggestJSON(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 as text FROM rminmid1_med_name WHERE med_status_cd = '0' AND med_name ILIKE :term ORDER BY med_name",
            ['term' => '%' . $term . '%']
        );
        return json_encode([
            "success" => true,
            "data" => $matches
        ]);
    }

    // 2. routed meds from men name
    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);
    }

    // 3. routed dosage from routed med
    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);
    }

    // 4. strengths from routed med
    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);
    }

    // side effects for a given rx
    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'));
    }

    // ger prec for a given rx
    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'));
    }

    // indication of a given rx
    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'));
    }

    // contra-indications of a given rx
    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'));
    }

    // dx suggest
    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 dxSuggestJSON(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 as text
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 response()->json(['success'=>true, 'data'=>$matches]);
    }

    public function dxICDsForDxID(Request $request) {
        $matches = DB::connection('pgsql_fdb')->select("
select r1.search_icd_cd, r1.icd_cd_type, r2.icd_desc
from RFMLISR1_ICD_SEARCH r1
left join RFMLINM1_ICD_DESC r2 on r1.search_icd_cd = r2.icd_cd
where r1.related_dxid = :dxid
  and (/*r1.icd_cd_type = '01' or */r1.icd_cd_type = '05')
  and r1.fml_clin_code = '01'
  /*and fml_nav_code = '01'*/
",
            ['dxid' => $request->input('dxid')]
        );
        return response()->json($matches);
    }

    // allergy suggest
    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'));
    }

    // allergy suggest (json response)
    public function allergySuggestJSON(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 as text
FROM rdamca0_concept r1
WHERE (r1.dam_concept_id_desc ILIKE :term)
ORDER BY r1.dam_concept_id_desc
",
            ['term' => '%' . $term . '%']
        );
        return json_encode([
            "success" => true,
            "data" => $matches
        ]);
    }

    // drug <-> allergy match making
    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("<br>", $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);
    }

    // drug <-> drug match making
    public function drugDrugInteraction(Request $request) {

        if($request->input('test')) {
            // override
            $rx = json_decode(json_encode([
                [
                    "gcn_seqno" => "45190",
                    "med_name_id" => "18604",
                    "medid" => "234539",
                    "routed_dosage_form_med_id" => "95130",
                    "routed_med_id" => "19876",
                    "rx" => "Zyprexa Zydis",
                ],
                [
                    "gcn_seqno" => "49853",
                    "med_name_id" => "26164",
                    "medid" => "400058",
                    "routed_dosage_form_med_id" => "36194",
                    "routed_med_id" => "32562",
                    "rx" => "Orfadin",
                ],
            ]));
        }
        else {
            $input = json_decode($request->input('data'));
            $rx = $input->rx;
        }

        if(count($rx) < 2) return "";

        $leftIndex = 0;

        $output = [];

        for ($i=$leftIndex; $i<count($rx) - 1; $i++) {
            for ($j=$i + 1; $j<count($rx); $j++) {
                $output[] = $this->drugDrugInteractionSinglePair($rx[$i], $rx[$j]);
            }
        }

        $output = array_filter($output, function($_x) {
            return !!$_x;
        });

        return implode("<br>", $output);
    }

        private function drugDrugInteractionSinglePair($_rx1, $_rx2) {

            $output = [];

            // get active ingredient DDI_CODEX values for drug 1 and 2
            $rx1ActiveDdiCodex = $this->getActiveDdiCodexFromGcnSeqNo($_rx1->gcn_seqno);
            $rx2ActiveDdiCodex = $this->getActiveDdiCodexFromGcnSeqNo($_rx2->gcn_seqno);
            if(!$rx1ActiveDdiCodex || !$rx2ActiveDdiCodex || !count($rx1ActiveDdiCodex) || !count($rx2ActiveDdiCodex)) return "";

    //        dump($rx1_DDI_CODEX);
    //        dump($rx2_DDI_CODEX);

            // get inactive ingredient DDI_CODEX values for drug 1 and 2
            // to get this we need to first get the NDCs of the drugs
            $rx1Ndc = $this->getNdcFromMedId($_rx1->medid);
            $rx2Ndc = $this->getNdcFromMedId($_rx2->medid);

    //        dump($rx1Ndc);
    //        dump($rx2Ndc);

            $rx1InactiveDdiCodex = $this->getInactiveDdiCodexFromNdc($rx1Ndc);
            $rx2InactiveDdiCodex = $this->getInactiveDdiCodexFromNdc($rx2Ndc);
            // if(!$rx1InactiveDdiCodex || !$rx2InactiveDdiCodex || !count($rx1InactiveDdiCodex) || !count($rx2InactiveDdiCodex)) return "";

    //        dump($rx1InactiveDdiCodex);
    //        dump($rx2InactiveDdiCodex);

            // get ddi codex - monox pairs for drug 1 & 2
            $rx1ActiveDdiCodexMonoxPairs = $this->getDdiCodexMonoxPairs($rx1ActiveDdiCodex);
            $rx1InactiveDdiCodexMonoxPairs = $this->getDdiCodexMonoxPairs($rx1InactiveDdiCodex);
            $rx2ActiveDdiCodexMonoxPairs = $this->getDdiCodexMonoxPairs($rx2ActiveDdiCodex);
            $rx2InactiveDdiCodexMonoxPairs = $this->getDdiCodexMonoxPairs($rx2InactiveDdiCodex);

    //        dump($rx1ActiveDdiCodexMonoxPairs);
    //        dump($rx1InactiveDdiCodexMonoxPairs);
    //        dump($rx2ActiveDdiCodexMonoxPairs);
    //        dump($rx2InactiveDdiCodexMonoxPairs);

            // compare 1-active to 2-active and 2-inactive
            $activeCatches = [];
            foreach ($rx1ActiveDdiCodexMonoxPairs as $compareLeft) {
                foreach ($rx2ActiveDdiCodexMonoxPairs as $compareRight) {
                    if($compareLeft->ddi_monox == $compareRight->ddi_monox && $compareLeft->ddi_codex != $compareRight->ddi_codex) {
                        $activeCatches[] = $compareLeft->ddi_codex;
                    }
                }
                foreach ($rx2InactiveDdiCodexMonoxPairs as $compareRight) {
                    if($compareLeft->ddi_monox == $compareRight->ddi_monox && $compareLeft->ddi_codex != $compareRight->ddi_codex) {
                        $activeCatches[] = $compareLeft->ddi_codex;
                    }
                }
            }

            // compare 1-inactive to 2-active and 2-inactive
            $inactiveCatches = [];
            foreach ($rx1InactiveDdiCodexMonoxPairs as $compareLeft) {
                foreach ($rx2ActiveDdiCodexMonoxPairs as $compareRight) {
                    if($compareLeft->ddi_monox == $compareRight->ddi_monox && $compareLeft->ddi_codex != $compareRight->ddi_codex) {
                        $inactiveCatches[] = $compareLeft->ddi_codex;
                    }
                }
                foreach ($rx2InactiveDdiCodexMonoxPairs as $compareRight) {
                    if($compareLeft->ddi_monox == $compareRight->ddi_monox && $compareLeft->ddi_codex != $compareRight->ddi_codex) {
                        $inactiveCatches[] = $compareLeft->ddi_codex;
                    }
                }
            }

            if(count($activeCatches)) {
                $output[] = "<b>{$_rx2->rx}</b> interacts with one or more active ingredients in <b>{$_rx1->rx}</b>.";
            }
            if(count($inactiveCatches)) {
                $output[] = "<b>{$_rx2->rx}</b> interacts with one or more inactive ingredients in <b>{$_rx1->rx}</b>.";
            }

            // TODO: find out and show the names of the actual ingredients causing interaction

            return implode("<br>", $output);
        }

        private function getActiveDdiCodexFromGcnSeqNo($_gcnSeqNo) {
            $ddiCodexArray = [];
            $query = DB::connection('pgsql_fdb')->select("
    SELECT r1.ddi_codex
    FROM RADIMGC4_GCNSEQNO_LINK r1
    WHERE r1.gcn_seqno = :gcnSeqNo
                ",
                ['gcnSeqNo' => $_gcnSeqNo]
            );
            if(count($query)) {
                $ddiCodexArray = array_map(function($_x) {
                    return $_x->ddi_codex;
                }, $query);
            }
            return $ddiCodexArray;
        }

        private function getNdcFromMedId($_medId) {
            $ndcArray = [];
            $query = DB::connection('pgsql_fdb')->select("
    select ndc from rmindc1_ndc_medid where medid = :medId
                ",
                ['medId' => $_medId]
            );
            if(count($query)) {
                $ndcArray = array_map(function($_x) {
                    return $_x->ndc;
                }, $query);
            }
            return $ndcArray;
        }

        private function getInactiveDdiCodexFromNdc($_ndc) {
            $ddiCodexArray = [];
            $query = DB::connection('pgsql_fdb')->select("
    SELECT distinct r1.ddi_codex
    FROM RDDIMIN0_NDC_INACTV_DDIM_LINK r1
    WHERE r1.ddi_ndc IN (" . implode(',', array_map(function($_x) {return "'" . $_x . "'";}, $_ndc)) . ")
                "
            );
            if(count($query)) {
                $ddiCodexArray = array_map(function($_x) {
                    return $_x->ddi_codex;
                }, $query);
            }
            return $ddiCodexArray;
        }

        private function getDdiCodexMonoxPairs($_ddiCodexArray) {
        $ddiCodexMonoxPairsArray = [];
        if(count($_ddiCodexArray)) {
            $ddiCodexMonoxPairsArray = DB::connection('pgsql_fdb')->select("
SELECT r1.ddi_codex, r1.ddi_monox
FROM RADIMMA5_MSTR r1
WHERE r1.ddi_codex IN (" . implode(',', array_map(function($_x) {return "'" . $_x . "'";}, $_ddiCodexArray)) . ")
            "
            );
        }
        return $ddiCodexMonoxPairsArray;
    }

    // drug <-> drug coadministration notes
    public function drugCoadministration(Request $request) {
        $gcnSeqnos = $request->input('gcn-seqnos') ? trim($request->input('gcn-seqnos')) : '';
        if (empty($gcnSeqnos)) return '';
        //$gcnSeqnos = explode(",", $gcnSeqnos);

        $coadministration = DB::connection('pgsql_fdb')->select("
SELECT distinct r1.coadmin_dosing_text
FROM radige0_ddi_gcnseqno_except r1
WHERE r1.side_a_gcn_seqno in ($gcnSeqnos) AND r1.side_b_gcn_seqno in ($gcnSeqnos)
            "
        );
        return view('app.fdb-pg.fdb-coadministration', compact('coadministration'));
    }

    // duplicate therapy indications
    public function duplicateTherapy(Request $request) {

        if($request->input('test')) {
            // override
            $rx = json_decode(json_encode([
                [
                    "gcn_seqno" => "4376",
                    "med_name_id" => "1076",
                    "medid" => "172480",
                    "routed_dosage_form_med_id" => "5870",
                    "routed_med_id" => "1082",
                    "rx" => "aspirin 325",
                ],
                [
                    "gcn_seqno" => "4377",
                    "med_name_id" => "1076",
                    "medid" => "216092",
                    "routed_dosage_form_med_id" => "5870",
                    "routed_med_id" => "1082",
                    "rx" => "aspirin 500",
                ],
            ]));
        }
        else {
            $input = json_decode($request->input('data'));
            $rx = $input->rx;
        }

        $dptClasses = [];
        foreach ($rx as $rxItem) {
            $rxItem->dpt = $this->getDptClassFromGcnSeqNo($rxItem->gcn_seqno);
        }

        // dd($rx);

        $leftIndex = 0;
        $matches = [];
        for ($i=$leftIndex; $i<count($rx) - 1; $i++) {
            for ($j=$i + 1; $j<count($rx); $j++) {
                $compareResult = $this->compareDPTs($rx[$i]->dpt, $rx[$j]->dpt);
                foreach ($compareResult as $c) {
                    $matches[] = "<b>{$rx[$i]->rx}</b> and <b>{$rx[$j]->rx}</b> both participate in the duplicate therapy class <b>{$c->dpt_class_desc}</b> (duplicates allowed: {$c->dpt_allowance})";
                }
            }
        }

        return "<ol class='pl-0 ml-3'>" . implode("", array_map(function($_x) {
                return "<li class='mb-2'>" . $_x . "</li>";
            }, $matches)) . "</ol>";
    }

        private function getDptClassFromGcnSeqNo($_gcnSeqNo) {
            return DB::connection('pgsql_fdb')->select("
    SELECT distinct r1.dpt_class_id, r2.dpt_allowance, r2.dpt_class_desc
    FROM RDPTGC0_GCNSEQNO_LINK r1
    JOIN RDPTCL0_CLASS_ID r2 on r1.dpt_class_id = r2.dpt_class_id
    WHERE r1.gcn_seqno = :gcnSeqNo
                ",
                ['gcnSeqNo' => $_gcnSeqNo]
            );
        }

        private function compareDPTs($_dptArray1, $_dptArray2) {
        $output = [];
        for ($i=0; $i<count($_dptArray1); $i++) {
            for ($j=0; $j<count($_dptArray2); $j++) {
                if($_dptArray1[$i]->dpt_class_id == $_dptArray2[$j]->dpt_class_id) {
                    $output[] = json_decode(json_encode([
                        "dpt_allowance" => $_dptArray1[$i]->dpt_allowance,
                        "dpt_class_desc" => $_dptArray1[$i]->dpt_class_desc
                    ]));
                }
            }
        }
        return $output;
    }
}