stagfe2/app/Helpers/fdb.php

<?php

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

if(!function_exists('side_effects_info')) {
    function side_effects_info($_drugs) {

        $result = [];

        // collect gcn-seq-nos
        $gcnSeqNos = [];
        $rxMap = [];
        foreach ($_drugs as $drug) {
            if(@$drug->data && $drug->data->gcnSeqno) {
                $gcnSeqNos[] = $drug->data->gcnSeqno;
                $rxMap[$drug->data->gcnSeqno] = $drug->data->name;
            }
        }
        if(!count($gcnSeqNos)) return $result;

        $gcnSeqNos = implode(',', array_map(function($_x) {
            return "'" . $_x . "'";
        }, $gcnSeqNos));

        $sides = DB::connection('pgsql_fdb')->select("
SELECT r1.gcn_seqno, 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 IN (" . $gcnSeqNos . ")
ORDER BY r1.gcn_seqno, sm.side_sev DESC, sm.side_freq ASC
            "
        );

        $seMap = [];

        foreach ($sides as $se) {

            if(!isset($seMap[$rxMap[$se->gcn_seqno]])) {
                $seMap[$rxMap[$se->gcn_seqno]] = [];
            }

            $seMap[$rxMap[$se->gcn_seqno]][] = [
                "gcn_seqno" => $se->gcn_seqno,
                "side_freq" => $se->side_freq,
                "side_sev" => $se->side_sev,
                "dxid_desc56" => $se->dxid_desc56,
            ];
        }

        $result = $seMap;

        return $result;
    }
}

if(!function_exists('precautions_info')) {
    function precautions_info($_drugs) {

    }
}

if(!function_exists('contraindications_info')) {
    function contraindications_info($_drugs) {

        $result = [];

        // collect routed med ids
        $routedMedIds = [];
        $rxMap = [];
        foreach ($_drugs as $drug) {
            if(@$drug->data && $drug->data->routedMedId) {
                $routedMedIds[] = $drug->data->routedMedId;
                $rxMap[$drug->data->routedMedId] = $drug->data->name;
            }
        }
        if(!count($routedMedIds)) return $result;

        $routedMedIds = implode(',', array_map(function($_x) {
            return "'" . $_x . "'";
        }, $routedMedIds));

        $contraindications = DB::connection('pgsql_fdb')->select("
SELECT r1.routed_med_id, 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 IN (" . $routedMedIds . ")
ORDER BY r1.routed_med_id, r3.dxid_desc56, r2.ddxcn_sl
            "
        );

        $ciMap = [];

        foreach ($contraindications as $ci) {

            if(!isset($ciMap[$rxMap[$ci->routed_med_id]])) {
                $ciMap[$rxMap[$ci->routed_med_id]] = [];
            }

            $ciMap[$rxMap[$ci->routed_med_id]][] = [
                "dxid_desc56" => $ci->dxid_desc56,
            ];
        }

        $result = $ciMap;

        return $result;

    }
}

if(!function_exists('drug_allergy_info')) {
    function drug_allergy_info($_drugs, $_allergies) {
        $output = [];

        /*
        for each allergy
            if damConceptIdType = 1 // allergen-group-id
                // https://docs.fdbhealth.com/display/MKDOCUS/Screening+an+NDC+for+a+DAM_ALRGN_GRP+Allergen+-+Illustration+of+Scenario+C
                ...
            elseif damConceptIdType = 2 // medication-name-id
                // https://docs.fdbhealth.com/display/MKDOCUS/Screening+an+NDC+for+a+MED_NAME_ID+Allergen+-+Illustration+of+Scenario+B
                ...
            elseif damConceptIdType = 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 ($_drugs as $rxItem) {
                if($allergy->data->damConceptIdType == 6) { // ingredient
                    if (drugAllergyIngredientAllergenVsSingleRx($allergy, $rxItem)) {
                        $output[] = "<b>{$rxItem->data->name}</b> can cause allergic reactions since the patient is allergic to <b>{$allergy->data->name}</b>.";
                    }
                }
                else if($allergy->data->damConceptIdType == 2) { // medication
                    if (drugAllergyMedicationAllergenVsSingleRx($allergy, $rxItem)) {
                        $output[] = "<b>{$rxItem->data->name}</b> can cause allergic reactions since the patient is allergic to <b>{$allergy->data->name}</b>.";
                    }
                }
                else if($allergy->data->damConceptIdType == 1) { // allergen group
                    if (drugAllergyGroupAllergenVsSingleRx($allergy, $rxItem)) {
                        $output[] = "<b>{$rxItem->data->name}</b> can cause allergic reactions since the patient is allergic to <b>{$allergy->data->name}</b>.";
                    }
                }
            }
        }

        return implode("<br>", $output);
    }
}
if(!function_exists('drugAllergyIngredientAllergenVsSingleRx')) {
    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->data->medId, 'allergenConceptID' => $_allergen->data->damConceptId]
        );

        return !!count($matches);
    }
}
if(!function_exists('drugAllergyMedicationAllergenVsSingleRx')) {
    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->data->medId, 'allergenConceptID' => $_allergen->data->damConceptId]
        );

        return !!count($matches);
    }
}
if(!function_exists('drugAllergyGroupAllergenVsSingleRx')) {
    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->data->medId, 'allergenConceptID' => $_allergen->data->damConceptId]
        );

        return !!count($matches);
    }
}

if(!function_exists('drug_drug_interaction_info')) {
    function drug_drug_interaction_info($_drugs) {

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

        $leftIndex = 0;

        $output = [];

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

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

        return implode("<br>", $output);
    }
}
if(!function_exists('drugDrugInteractionSinglePair')) {
    function drugDrugInteractionSinglePair($_rx1, $_rx2)
    {

        $output = [];

        // get active ingredient DDI_CODEX values for drug 1 and 2
        $rx1ActiveDdiCodex = getActiveDdiCodexFromGcnSeqNo($_rx1->data->gcnSeqno);
        $rx2ActiveDdiCodex = getActiveDdiCodexFromGcnSeqNo($_rx2->data->gcnSeqno);
        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 = getNdcFromMedId($_rx1->data->medId);
        $rx2Ndc = getNdcFromMedId($_rx2->data->medId);

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

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

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

        // get ddi codex - monox pairs for drug 1 & 2
        $rx1ActiveDdiCodexMonoxPairs = getDdiCodexMonoxPairs($rx1ActiveDdiCodex);
        $rx1InactiveDdiCodexMonoxPairs = getDdiCodexMonoxPairs($rx1InactiveDdiCodex);
        $rx2ActiveDdiCodexMonoxPairs = getDdiCodexMonoxPairs($rx2ActiveDdiCodex);
        $rx2InactiveDdiCodexMonoxPairs = 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->data->name}</b> interacts with one or more active ingredients in <b>{$_rx1->data->name}</b>.";
        }
        if (count($inactiveCatches)) {
            $output[] = "<b>{$_rx2->data->name}</b> interacts with one or more inactive ingredients in <b>{$_rx1->data->name}</b>.";
        }

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

        return implode("<br>", $output);
    }
}
if(!function_exists('getActiveDdiCodexFromGcnSeqNo')) {
    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;
    }
}
if(!function_exists('getNdcFromMedId')) {
    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;
    }
}
if(!function_exists('getInactiveDdiCodexFromNdc')) {
    function getInactiveDdiCodexFromNdc($_ndc)
    {
        $ddiCodexArray = [];
        if(!count($_ndc)) return $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;
    }
}
if(!function_exists('getDdiCodexMonoxPairs')) {
    function getDdiCodexMonoxPairs($_ddiCodexArray)
    {
        $ddiCodexMonoxPairsArray = [];
        if(!count($_ddiCodexArray)) return $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;
    }
}

if(!function_exists('duplicate_therapy_info')) {
    function duplicate_therapy_info($_drugs) {

        $dptClasses = [];
        foreach ($_drugs as $drug) {
            $drug->dpt = getDptClassFromGcnSeqNo($drug->data->gcnSeqno);
        }

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

        if(!count($matches)) return '';

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

if (!function_exists('getDptClassFromGcnSeqNo')) {
    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]
        );
    }
}
if (!function_exists('compareDPTs')) {
    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;
    }
}

if(!function_exists('coadministration_info')) {
    function coadministration_info($_drugs) {

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

        // collect gcn-seq-nos
        $gcnSeqNos = [];
        $rxMap = [];
        foreach ($_drugs as $drug) {
            if(@$drug->data && $drug->data->gcnSeqno) {
                $gcnSeqNos[] = $drug->data->gcnSeqno;
                $rxMap[$drug->data->gcnSeqno] = $drug->data->name;
            }
        }
        if(!count($gcnSeqNos)) return $result;

        $gcnSeqNos = implode(',', array_map(function($_x) {
            return "'" . $_x . "'";
        }, $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)
            "
        );

        if(!$coadministration || !count($coadministration)) return '';

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

        return $coadministration;
    }
}