
<?php

require_once("imp/_imp.php");

class Controllers {

//public vars
    public $smarty;
    public $hosturl;
    public $controllname;
    public $shcontrollname;
    public $active;
    public $time;
    public $today;

// it will run in everytime with
// default requried
    public function __construct() {
        require_once("../public/aw_config.php");
        require_once("imp/_autho.php"); //general functions
        $this->smarty = $smarty;
        $this->hosturl = $hosturl;

//here name of controll to known
        $this->controllname = 'invsdeptapp';
        $this->shcontrollname = 'invsdeptapp';
        $this->smarty->assign('controllname', $this->controllname);
        $this->smarty->assign('shcontrollname', $this->shcontrollname);



//breadcrumbs
        require_once("../../_library/plugins/Breadcrumbs/Breadcrumbs.php");
        $acty = filter_input(INPUT_GET, 'ac');
        $bc = new breadcrumbs($hosturl, '', '', $acty);
        $bc->crumbs();
        $breadcrumbs = $bc->breadcrumbs;
        $smarty->assign('breadcrumbs', $breadcrumbs);

# here show the active page in menu
// array called active[] with level of active element
        $active[0] = 'invsdeptapp'; //controller name or tab name
        $active[1] = 'invsdeptapp'; //controller name
// do name  // switch($do)
        switch ($acty) {
            case '':$active[2] = 'show';
                break;
            case 'show':$active[2] = 'show';
                break;
            default :$active[2] = 'show';
                break;
        }
        $this->active = $active;

        R::ext('xdispense', function ($type) {
            return R::getRedBean()->dispense($type);
        });



//here will call all php pages
        require_once("_header.php");
        require_once("_patientdata.php");
        require_once("_sidebar.php");
        require_once("_leftsidebar.php");
        require_once("_footer.php");

        //find loading time
        $this->time = microtime(true);
        $this->today = date('Y-m-d');
    }

    ### get labs by patient id

    function display() {
        //check login
        autho::checkautho($this->hosturl);


        $patientId = filter_input(INPUT_GET, "patientid");

        $visits = $this->getPatientCompletedInvests($patientId);

        $this->smarty->assign('patientid', $patientId);
        $this->smarty->assign('visits', $visits);
        $this->smarty->assign('rescount', count($visits));

        print_r('<pre>');
        print_r($visits);
        print_r('</pre>');

//        header::headershow($this->smarty, $this->hosturl);
//        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
//        // patientdata::patientdatashow($this->smarty, $this->hosturl);
//        $this->smarty->display('lab/mobile1.html');
//        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
//        footer::footershow($this->smarty, $this->hosturl);
    }

    function getPatientCompletedInvests($patientId) {

        $sql = ' select distinct investigationresults.visitid , visits.visitdate as visitDate  
                        from    investigationresults
                         join     visits                                               on visits.id  = investigationresults.visitid
                        
                          where     
                                visits.patientid = ' . $patientId . '  and  investigationresults.status = 1  
                                and  investigationresults.deleted = 0   order by visits.visitdate desc ';

        $visits = R::getAll($sql);

        $i = 0;
        foreach ($visits as $visit) {

            $sqlInv = ' select  investigationresults.id  as  investigationResultId  , invests.name as investName   
                            from    investigationresults     
                             join     invests                            on invests.id  = investigationresults.investid
                             where     
                                investigationresults.visitid = ' . $visit['visitid'] . '  and  investigationresults.status = 1  
                                 and  investigationresults.deleted = 0  ';

            $invs = R::getAll($sqlInv);
            $visits[$i]['investigationresults'] = $invs;

            $i++;
        }

        return $visits;
    }

    function displayInvDetails() {
        //check login
        autho::checkautho($this->hosturl);

        $investigationResultId = filter_input(INPUT_GET, "id");
        $this->getInvDetails($investigationResultId);

//        header::headershow($this->smarty, $this->hosturl);
//        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
//        // patientdata::patientdatashow($this->smarty, $this->hosturl);
//        $this->smarty->display('lab/mobile2.html');
//        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
//        footer::footershow($this->smarty, $this->hosturl);
    }

    function getInvDetails($investigationResultId) {

###########################################
        $invResult = array();

        $in = R::load('investigationresults', $investigationResultId);
        $obj = R::load('invests', $in->investid);
        $cat = R::load('investcats', $obj->investcatid);
        $visit = R::load('visits', $in->visitid);
        $patient = R::load('patients', $visit->patientid);

        if ($visit->for_husband == 1) {
            $col = 'husbanddateofbirth';
            $gender = 1;
        } else {
            $col = 'dateofbirth';
            $gender = 0;
        }

        $age = $this->calcAge($patient->$col);


        $invResult['catName'] = $cat->name;
        $invResult['invName'] = $obj->name;
        $invResult['special_inv_type'] = $obj->special_inv_type;
        $invResult['elements'] = '';


        switch ($obj->special_inv_type) {

            ## urine 
            case 2:
                $urine_count = R::count('investigationresults_urine', 'investigationresults_id = ?', [$in->id]);
                if ($urine_count < 1) {
                    $urine = R::xdispense('investigationresults_urine');
                    $urine->investigationresults_id = $in->id;
                    r::store($urine);
                }
                $invResult['elements'] = $this->printUrine($in, $gender, $age);
                break;

            ## stool 
            case 3:
                $stool_count = R::count('investigationresults_stool', 'investigationresults_id = ?', [$in->id]);
                if ($stool_count < 1) {
                    $stool = R::xdispense('investigationresults_stool');
                    $stool->investigationresults_id = $in->id;
                    r::store($stool);
                }
                $invResult['elements'] = $this->printStool($in, $gender, $age);
                break;

            ## blood picture 
            case 1:
                $blood_count = R::count('investigationresults_blood', 'investigationresults_id = ?', [$in->id]);
                if ($blood_count < 1) {
                    $blood = R::xdispense('investigationresults_blood');
                    $blood->investigationresults_id = $in->id;
                    r::store($blood);
                }
                $invResult['elements'] = $this->printBlood($in, $gender, $age);
                break;

            ## Culture
            case 4:

                $culture_count = R::count('investigationresults_culture', 'investigationresults_id = ?', [$in->id]);
                if ($culture_count < 1) {
                    $culture = R::xdispense('investigationresults_culture');
                    $culture->investigationresults_id = $in->id;
                    r::store($culture);
                }

                $invResult['elements'] = $this->printCulture($in);
                break;

            ## ESR
            case 5:
                $esr_count = R::count('investigationresults_esr', 'investigationresults_id = ?', [$in->id]);
                if ($esr_count < 1) {
                    $esr = R::xdispense('investigationresults_esr');
                    $esr->investigationresults_id = $in->id;
                    r::store($esr);
                }
//                    $in->esr = $this->printEsr($in, $gender, $age);
                $invResult['elements'] = $this->loadEsrRanges($in, $gender, $age);
                break;

            ## Cross Matching
            case 6:
                $cross_count = R::count('investigationresults_cross', 'investigationresults_id = ?', [$in->id]);
                if ($cross_count < 1) {
                    $cross = R::xdispense('investigationresults_cross');
                    $cross->investigationresults_id = $in->id;
                    r::store($cross);
                }

                $invResult['elements'] = $this->printCross($in);

                break;

            ## HB sag
            case 7:
                break;

            ## Clothing time
            case 8:
                $clothing_count = R::count('investigationresults_times', 'investigationresults_id = ?', [$in->id]);
                if ($clothing_count < 1) {
                    $clothing = R::xdispense('investigationresults_times');
                    $clothing->investigationresults_id = $in->id;
                    r::store($clothing);
                }

                $invResult['elements'] = $this->loadClothingDetails($in, $gender, $age);
                break;

//            ## PT
//            case 9:
//                $pt_count = R::count('investigationresults_pt', 'investigationresults_id = ?', [$in->id]);
//                if ($pt_count < 1) {
//                    $pt = R::xdispense('investigationresults_pt');
//                    $pt->investigationresults_id = $in->id;
//                    r::store($pt);
//                }
//
//                $invResult['elements'] = $this->loadPtDetails($in, $gender, $age);
//                break;


            default :
                $invResult = $this->printCommon($in, $invResult);
                break;
        }

########################################################
        //      $this->smarty->assign('result', $invResults);

        print_r('<pre>');
        print_r($invResult);
        print_r('</pre>');
        //    return $invResult;
    }

    function urineSql() {

        $sql = ' select investigationresults_urine.id , investigationresults_urine.comment  ';

        $colmsSql = '';

        $joinSql = '';


        $tables = array("volume", "color", "odor", "ova", "bacteria", "mucus", "trophazite"
            , "pbcs", "pus2", "pus", "blood", "leucocytes", "aspect", "deposit", "reaction"
            , "protein", "fungi", "artifacts", "crystal", "pbcs2", "casts2", "casts", "epithelial"
            , "urobilinigen", "bilirubin", "gravity", "nitrite", "acetone", "sugar", "bacteria2"
            , "amorphous2", "amorphous", "crystal2"
        );

        foreach ($tables as $tableName) {
            ## selected colums 
            $colmsSql .= '  , urine_' . $tableName . '.name as  ' . $tableName;

            ## join tables 
            $joinSql .= ' left  join urine_' . $tableName . '  on   urine_' . $tableName . '.id  =  investigationresults_urine.' . $tableName;
        }

        $sql .= $colmsSql;
        $sql .= ' from investigationresults_urine ';
        $sql .= $joinSql;

        return $sql;
    }

    function stoolSql() {

        $sql = ' select investigationresults_stool.id , investigationresults_stool.comment  ';

        $colmsSql = '';

        $joinSql = '';


        $tables = array("blood", "ciliate", "color", "consistency", "cysts", "flagellates"
            , "prog", "parasitology2", "parasitology", "ova2", "larva2", "cysts2"
            , "prog2", "rbc", "rbc2", "reaction", "mucus", "flagellates2", "trophozoite",
            "trophozoite2", "trophozoite3", "undigested", "odor", "food", "undigested2"
            , "undigested3", "undigested4", "wbc", "ova", "larva", "wbc2"
        );

        foreach ($tables as $tableName) {
            ## selected colums 
            $colmsSql .= '  , stool_' . $tableName . '.name as  ' . $tableName;

            ## join tables 
            $joinSql .= ' left  join stool_' . $tableName . '  on   stool_' . $tableName . '.id  =  investigationresults_stool.' . $tableName;
        }

        $sql .= $colmsSql;
        $sql .= ' from investigationresults_stool ';
        $sql .= $joinSql;

        return $sql;
    }

    function cultureSql() {

        $sql = ' select investigationresults_culture.*    ';

        $colmsSql = '';

        $joinSql = '';

        $tables = array("sample", "organism_a", "organism_b", "organism_c"
            , "condition", "condition1", "colony_count", "sensitivity_cat");

        foreach ($tables as $tableName) {
            ## selected colums 
            $colmsSql .= '  , culture_' . $tableName . '.name as  ' . $tableName . '_value';

            ## join tables 
            $joinSql .= ' left  join culture_' . $tableName . '  on   culture_' . $tableName . '.id  =  investigationresults_culture.' . $tableName;
        }


        $sql .= $colmsSql;

        $sql .= ' from investigationresults_culture ';

        $sql .= $joinSql;


        return $sql;
    }

    function printUrine($in, $gender, $age) {

        $elementsResult = array();

        $urineSql = $this->urineSql();

        $urine = R::getRow($urineSql . ' where investigationresults_urine.investigationresults_id = ? ', [$in->id]);


        $elements = array("Volume" => "volume", "Color" => "color", "Odor" => "odor", "Aspect" => "aspect"
            , "Deposit" => "deposit", "Reaction" => "reaction", "Protein" => "protein", "Sugar" => "sugar", "Acetone" => "acetone"
            , "Nitrite" => "nitrite", "Specific Gravity" => "gravity", "Bilirubin" => "bilirubin", "Urobilinigen" => "urobilinigen"
            , "Leucocytes" => "leucocytes", "Blood" => "blood", "Pus Cells" => "pus", "R.B.Cs" => "pbcs"
            , "Epithelial Cells" => "epithelial", "Casts" => "casts", "Crystal" => "crystal", "Amorphous" => "amorphous"
            , "Ova" => "ova", "Trophazite" => "trophazite", "Mucus" => "mucus", "Urine Artifacts" => "artifacts"
            , "Fungi" => "fungi", "Bacteria" => "bacteria"
        );


        foreach ($elements as $showName => $dbName) {

            $elementRes = array();

            $elementRes['result'] = $urine[$dbName];
            $elementRes['dbName'] = $dbName;
            $elementRes['showName'] = $showName;

            $inRangeName = $dbName . '_inRange';
            $elementRes[$inRangeName] = '';

            $range = Null;

            $range = R::findOne('inv_ranges', ' ( gender = ? || gender = ?  || gender = ? )  and agefrom <= ? 
                    and ageto >= ?  and investid = ?  and age_units = ?   and invest_child = ?  
                    ', [$gender, '', -1, $age, $age, $in->investid, 1, $showName]);

            if (!(count($range) > 0 )) {

                $range = R::findOne('inv_ranges', ' ( gender = ? || gender = ?   || gender = ?)  and ( agefrom = ?  || agefrom = ?  ) 
                    and ( ageto = ?  || ageto = ?  )   and investid = ?   and invest_child = ?  
                    ', [$gender, '', -1, Null, '', Null, '', $in->investid, $showName]);
            }

            if (count($range) > 0) {

                $n1 = $dbName . '_range_txt';
                $n2 = $dbName . '_range_low';
                $n3 = $dbName . '_range_high';
                $n4 = $dbName . '_invest_units';

                $elementRes[$n1] = $range->range_txt;
                $elementRes[$n2] = $range->range_low;
                $elementRes[$n3] = $range->range_high;
                $elementRes[$n4] = $range->invest_units;

                if (is_numeric($range->range_low) && is_numeric($urine[$dbName]) && $urine[$dbName] < $range->range_low) {
                    $elementRes[$inRangeName] = 'L';
                }

                ####
                elseif (is_numeric($range->range_high) && is_numeric($urine[$dbName]) && $urine[$dbName] > $range->range_high) {
                    $elementRes[$inRangeName] = 'H';
                }
            }


            $elementsResult[$dbName] = $elementRes;
        }

        return $elementsResult;
    }

    function printStool($in, $gender, $age) {

        $elementsResult = array();

        $stoolSql = $this->stoolSql();

        $stool = R::getRow($stoolSql . ' where investigationresults_stool.investigationresults_id = ? ', [$in->id]);


        $elements = array("Consistency" => "consistency", "Food Particles" => "food", "Blood" => "blood"
            , "Color" => "color", "Reaction" => "reaction", "Odor" => "odor"
            , "Mucus" => "mucus", "Proglottids" => "prog", "Flagellates" => "flagellates"
            , "Ciliate" => "ciliate", "Undigested Food" => "undigested", "Parasitology Artifacts" => "parasitology"
            , "WBCs" => "wbc", "RBCs" => "rbc", "Trophozoite" => "trophozoite"
            , "Cysts" => "cysts", "Ova" => "ova", "Larva" => "larva"
        );

        foreach ($elements as $showName => $dbName) {

            $elementRes = array();

            $elementRes['result'] = $stool[$dbName];
            $elementRes['dbName'] = $dbName;
            $elementRes['showName'] = $showName;

            $inRangeName = $dbName . '_inRange';
            $elementRes[$inRangeName] = '';

            $range = Null;

            $range = R::findOne('inv_ranges', ' ( gender = ? || gender = ?  || gender = ? )  and agefrom <= ? 
                    and ageto >= ?  and investid = ?  and age_units = ?   and invest_child = ?  
                    ', [$gender, '', -1, $age, $age, $in->investid, 1, $showName]);

            if (!(count($range) > 0 )) {

                $range = R::findOne('inv_ranges', ' ( gender = ? || gender = ?   || gender = ?)  and ( agefrom = ?  || agefrom = ?  ) 
                    and ( ageto = ?  || ageto = ?  )   and investid = ?   and invest_child = ?  
                    ', [$gender, '', -1, Null, '', Null, '', $in->investid, $showName]);
            }

            if (count($range) > 0) {

                $n1 = $dbName . '_range_txt';
                $n2 = $dbName . '_range_low';
                $n3 = $dbName . '_range_high';
                $n4 = $dbName . '_invest_units';

                $elementRes[$n1] = $range->range_txt;
                $elementRes[$n2] = $range->range_low;
                $elementRes[$n3] = $range->range_high;
                $elementRes[$n4] = $range->invest_units;


                if (is_numeric($range->range_low) && is_numeric($stool[$dbName]) && $stool[$dbName] < $range->range_low) {
                    $elementRes[$inRangeName] = 'L';
                }

                ####
                elseif (is_numeric($range->range_high) && is_numeric($stool[$dbName]) && $stool[$dbName] > $range->range_high) {
                    $elementRes[$inRangeName] = 'H';
                }
            }

            $elementsResult[$dbName] = $elementRes;
        }

        return $elementsResult;
    }

    function calcAge($dateOfBirth) {
        if ($dateOfBirth == '0000-00-00' || is_null($dateOfBirth) || $dateOfBirth == '') {
            $age = 0;
        } else {
            ## calculate wife age
            $dateWife = new DateTime($dateOfBirth);
            $today1 = new DateTime(date('Y-m-d'));
            $interva2 = $today1->diff($dateWife);
            $age = $interva2->y;
        }

        return $age;
    }

    function printCommon($in, $invResult) {


        $invResult['result'] = $in->investresult;

        $invResult['inRange'] = '';
        ######   range details 

        if ($in->rangeid > 0) {
            $range = R::load('inv_ranges', $in->rangeid);

            if (is_numeric($range->range_low) && is_numeric($in->investresult) && $in->investresult < $range->range_low) {
                $invResult['inRange'] = 'L';
            } elseif (is_numeric($range->range_high) && is_numeric($in->investresult) && $in->investresult > $range->range_high) {
                $invResult['inRange'] = 'H';
            }

            #### range details
            $invResult['range_txt'] = $range->range_txt;
            $invResult['range_low'] = $range->range_low;
            $invResult['range_high'] = $range->range_high;
            $invResult['invest_units'] = $range->invest_units;
        }

        return $invResult;
    }

    function printBlood($in, $gender, $age) {

        $elementsResult = array();

        $blood = R::findOne('investigationresults_blood', 'investigationresults_id = ? ', [$in->id]);

        $elements = array("Hgb" => "hgb", "RBC" => "rbc", "Plt" => "plt", "WBC" => "wbc"
            , "Hct" => "hct", "MCV" => "mcv", "MCH" => "mch", "MCHC" => "mchc"
            , "RDW" => "rdw", "Mentzer Index %" => "mindex", "Other Cells" => "othercells"
            , "Segmented" => "segmented", "Band" => "band", "Pct" => "pct", "MPV" => "mpv"
            , "PDW" => "pdw", "NRBC / 100 WBC" => "nrbc", "Promyelocyte" => "promyelocyte"
            , "Myelocytes" => "myelocyte", "Juvenile" => "juvenile", "Blast" => "blast", "Hgb %" => "hgbpercent"
            , "Relative Neutrophil" => "neutrophil_r", "Relative Lymphocytes" => "lymphocytes_r"
            , "Relative Monocytes" => "monocytes_r", "Relative Eosinophils" => "eosinophils_r"
            , "Relative Basophils" => "basophils_r", "Absolute Neutrophil" => "neutrophil_abs"
            , "Absolute Lymphocytes" => "lymphocytes_abs", "Absolute Monocytes" => "monocytes_abs"
            , "Absolute Eosinophils" => "eosinophils_abs", "Absolute Basophils" => "basophils_abs"
        );


        foreach ($elements as $showName => $dbName) {

            $elementRes = array();

            $elementRes['result'] = $blood->$dbName;
            $elementRes['dbName'] = $dbName;
            $elementRes['showName'] = $showName;

            $inRangeName = $dbName . '_inRange';
            $elementRes[$inRangeName] = '';

            $range = Null;


            $rangeid = $dbName . '_rangeid';

            if ($blood->$rangeid && $blood->$rangeid > 0) {
                $range = R::load('inv_ranges', $blood->$rangeid);
            }


            if (!(count($range) > 0 )) {
                $range = R::findOne('inv_ranges', ' ( gender = ? || gender = ?  || gender = ? )  and agefrom <= ? 
                    and ageto >= ?  and investid = ?  and age_units = ?   and invest_child = ?  
                    ', [$gender, '', -1, $age, $age, $in->investid, 1, $showName]);

                if (!(count($range) > 0 )) {

                    $range = R::findOne('inv_ranges', ' ( gender = ? || gender = ?   || gender = ?)  and ( agefrom = ?  || agefrom = ?  ) 
                    and ( ageto = ?  || ageto = ?  )   and investid = ?   and invest_child = ?  
                    ', [$gender, '', -1, Null, '', Null, '', $in->investid, $showName]);
                }
            }



            if (count($range) > 0) {

                $n1 = $dbName . '_range_txt';
                $n2 = $dbName . '_range_low';
                $n3 = $dbName . '_range_high';
                $n4 = $dbName . '_invest_units';

                $elementRes[$n1] = $range->range_txt;
                $elementRes[$n2] = $range->range_low;
                $elementRes[$n3] = $range->range_high;
                $elementRes[$n4] = $range->invest_units;

                if (is_numeric($range->range_low) && is_numeric($blood->$dbName) && $blood->$dbName < $range->range_low) {
                    $elementRes[$inRangeName] = 'L';
                }

                ####
                elseif (is_numeric($range->range_high) && is_numeric($blood->$dbName) && $blood->$dbName > $range->range_high) {
                    $elementRes[$inRangeName] = 'H';
                }
            }
            $elementsResult[$dbName] = $elementRes;
        }

        return $elementsResult;
    }

    function printCulture($in) {

        $cultureSql = $this->cultureSql();

        $culture = R::getRow($cultureSql . ' where investigationresults_culture.investigationresults_id = ? ', [$in->id]);


        $culture['investigationresults_culture_hs'] = $this->cultureFor('investigationresults_culture_hs', $culture['id']);
        $culture['investigationresults_culture_ls'] = $this->cultureFor('investigationresults_culture_ls', $culture['id']);
        $culture['investigationresults_culture_ms'] = $this->cultureFor('investigationresults_culture_ms', $culture['id']);
        $culture['investigationresults_culture_rs'] = $this->cultureFor('investigationresults_culture_rs', $culture['id']);

        return $culture;
    }

    function cultureFor($tableName, $culture_id) {

        $sql = ' select  ' . $tableName . '.id  ,   ' . $tableName . '.sensitive    ';
        $sql .= '  ,   antibiotic_sensitivity.abbr ,   antibiotic_sensitivity.scientific_name ,   antibiotic_sensitivity.sensitivity   ';

        $sql .= '  from ' . $tableName . '   ';

        $sql .= '   join antibiotic_sensitivity  on   ' . $tableName . '.antibiotic_sensitivity_id  =  antibiotic_sensitivity.id';

        $sql .= '  where ' . $tableName . '.investigationresults_culture_id = ' . $culture_id . ' and  ' . $tableName . '.deleted != 1   ';


        $result = R::getAll($sql);

        return $result;
    }

    function esrSql() {

        $sql = ' select investigationresults_esr.*    ';

        $colmsSql = '';

        $joinSql = '';


        $tables = array("hour1", "hour2");

        foreach ($tables as $tableName) {
            ## selected colums 
            $colmsSql .= '  , esr_' . $tableName . '.name as  ' . $tableName;

            ## join tables 
            $joinSql .= ' left  join esr_' . $tableName . '  on   esr_' . $tableName . '.id  =  investigationresults_esr.' . $tableName;
        }

        $sql .= $colmsSql;
        $sql .= ' from investigationresults_esr ';
        $sql .= $joinSql;

        return $sql;
    }

    function loadEsrRanges($in, $gender, $age) {

        $elementsResult = array();

        $esrSql = $this->esrSql();

        $esr = R::getRow($esrSql . ' where investigationresults_esr.investigationresults_id = ? ', [$in->id]);

        $elements = array("hour1", "hour2");

        foreach ($elements as $single) {
            $elementRes = $this->elementRanges($in, $gender, $age, $single, $esr, 'investigationresults_esr');
            $elementsResult[$single] = $elementRes;
        }

        return $elementsResult;
    }

    function elementRanges($in, $gender, $age, $element, $res_obj, $table) {

        $elementRes = array();

        $rangeid = $element . '_rangeid';

        $elementRes['result'] = $res_obj[$element];
        $elementRes['dbName'] = $element;

        $inRangeName = $element . '_inRange';
        $elementRes[$inRangeName] = '';

        $range = Null;


        if ($res_obj[$rangeid] < 1) {
            $range = R::findOne('inv_ranges', ' ( gender = ? || gender = ?  || gender = ? )  and agefrom <= ? 
                    and ageto >= ?  and investid = ?  and age_units = ?   and invest_child = ?  
                    ', [$gender, '', -1, $age, $age, $in->investid, 1, $element]);

            if (count($range) > 0 && $res_obj[$rangeid] < 1) {
                r::exec(' update ' . $table . ' set  ' . $rangeid . ' = ' . $range->id . '  where id =  ' . $res_obj['id']);
            } else {


                $range = R::findOne('inv_ranges', ' ( gender = ? || gender = ?  || gender = ? )  and ( agefrom = ?  || agefrom = ?  ) 
                    and ( ageto = ?  || ageto = ?  )   and investid = ?   and invest_child = ?  
                    ', [$gender, '', -1, Null, '', Null, '', $in->investid, $element]);

                if (count($range) > 0 && $res_obj[$rangeid] < 1) {
                    r::exec(' update ' . $table . ' set  ' . $rangeid . ' = ' . $range->id . '  where id =  ' . $res_obj['id']);
                }
            }
        }


        if ($res_obj[$rangeid] > 0) {

            $n1 = $element . '_range_txt';
            $n2 = $element . '_range_low';
            $n3 = $element . '_range_high';
            $n4 = $element . '_invest_units';



            $range = R::load('inv_ranges', $res_obj[$rangeid]);

            $elementRes[$n1] = $range->range_txt;
            $elementRes[$n2] = $range->range_low;
            $elementRes[$n3] = $range->range_high;
            $elementRes[$n4] = $range->invest_units;

            if (is_numeric($range->range_low) && is_numeric($res_obj[$element]) && $res_obj[$element] < $range->range_low) {
                $elementRes[$inRangeName] = 'L';
            }

            ####
            elseif (is_numeric($range->range_high) && is_numeric($res_obj[$element]) && $res_obj[$element] > $range->range_high) {
                $elementRes[$inRangeName] = 'H';
            }
        }

        return $elementRes;
    }

    function printCross($in) {

        $crossSql = $this->crossSql();
        $crossDonnerSql = $this->crossDonnerSql();

        $cross = R::getRow($crossSql . ' where investigationresults_cross.investigationresults_id = ? ', [$in->id]);

        $donners = R::getAll($crossDonnerSql . ' where investigationresults_cross_donners.investigationresults_cross_id = ? ', [$cross['id']]);


        if (count($donners) < 1) {
            $donner = R::xdispense('investigationresults_cross_donners');
            $donner->investigationresults_cross_id = $cross['id'];
            r::store($donner);
        }


        $donners = R::getAll($crossDonnerSql . ' where investigationresults_cross_donners.investigationresults_cross_id = ? ', [$cross['id']]);

        $cross['donnersItr'] = count($donners);
        $cross['donners'] = $donners;


        return $cross;
    }

    function crossSql() {

        $sql = ' select investigationresults_cross.*    ';

        $colmsSql = '';

        $joinSql = '';

        $tables = array("abo", "rh");

        foreach ($tables as $tableName) {
            ## selected colums 
            $colmsSql .= '  , cross_' . $tableName . '.name as  ' . $tableName . '_value';

            ## join tables 
            $joinSql .= ' left  join cross_' . $tableName . '  on   cross_' . $tableName . '.id  =  investigationresults_cross.' . $tableName;
        }


        $sql .= $colmsSql;

        $sql .= ' from investigationresults_cross ';

        $sql .= $joinSql;


        return $sql;
    }

    function crossDonnerSql() {

        $sql = ' select investigationresults_cross_donners.*    ';

        $colmsSql = '';

        $joinSql = '';

        $tables = array("abo", "rh", "compatibility", "hcv", "hbsag", "hv");

        foreach ($tables as $tableName) {
            ## selected colums 
            $colmsSql .= '  , cross_' . $tableName . '.name as  ' . $tableName . '_value';

            ## join tables 
            $joinSql .= ' left  join cross_' . $tableName . '  on   cross_' . $tableName . '.id  =  investigationresults_cross_donners.' . $tableName;
        }


        $sql .= $colmsSql;

        $sql .= ' from investigationresults_cross_donners ';

        $sql .= $joinSql;

        return $sql;
    }

    function loadClothingDetails($in, $gender, $age) {

        $elementsResult = array();

        $clothing = R::findOne('investigationresults_times', 'investigationresults_id = ? ', [$in->id]);

        $elements = array("Bleeding Time" => "bleeding", "Clothing Time" => "clothing");

        foreach ($elements as $showName => $dbName) {

            $elementRes = array();

            $secName = $dbName . '_sec';

            $elementRes[$dbName] = $clothing->$dbName;
            $elementRes[$dbName . '_sec'] = $clothing->$secName;
            $elementRes['dbName'] = $dbName;
            $elementRes['showName'] = $showName;

            $inRangeName = $dbName . '_inRange';
            $elementRes[$inRangeName] = '';

            $range = Null;

            $rangeid = $dbName . '_rangeid';


            if ($clothing->$rangeid && $clothing->$rangeid > 0) {
                $range = R::load('inv_ranges', $clothing->$rangeid);
            }

            if (!(count($range) > 0 )) {
                $range = R::findOne('inv_ranges', ' ( gender = ? || gender = ?  || gender = ? )  and agefrom <= ? 
                    and ageto >= ?  and investid = ?  and age_units = ?   and invest_child = ?  
                    ', [$gender, '', -1, $age, $age, $in->investid, 1, $showName]);


                if (!(count($range) > 0 )) {

                    $range = R::findOne('inv_ranges', ' ( gender = ? || gender = ?   || gender = ?)  and ( agefrom = ?  || agefrom = ?  ) 
                    and ( ageto = ?  || ageto = ?  )   and investid = ?   and invest_child = ?  
                    ', [$gender, '', -1, Null, '', Null, '', $in->investid, $showName]);
                }
            }


            if (count($range) > 0) {

                $n1 = $dbName . '_range_txt';
                $n2 = $dbName . '_range_low';
                $n3 = $dbName . '_range_high';
                $n4 = $dbName . '_invest_units';

                $elementRes[$n1] = $range->range_txt;
                $elementRes[$n2] = $range->range_low;
                $elementRes[$n3] = $range->range_high;
                $elementRes[$n4] = $range->invest_units;

                if (is_numeric($range->range_low) && is_numeric($clothing->$dbName) && $clothing->$dbName < $range->range_low) {
                    $elementRes[$inRangeName] = 'L';
                }

                ####
                elseif (is_numeric($range->range_high) && is_numeric($clothing->$dbName) && $clothing->$dbName > $range->range_high) {
                    $elementRes[$inRangeName] = 'H';
                }
            }


            $elementsResult[$dbName] = $elementRes;
        }

        return $elementsResult;
    }

//    function loadPtDetails($in, $gender, $age) {
//
//        $elementsResult = array();
//
//        $pt = R::findOne('investigationresults_pt', 'investigationresults_id = ? ', [$in->id]);
//
//        $elements = array("PT Patient's Time" => "patient_time", "APTT Patient's Time" => "patient_time1"
//            , "Concentration" => "concentration", "I.N.R" => "inr", "Ratio" => "ratio"
//            , "Fibrinogen" => "fibrinogen", "FDP" => "fdp");
//
//        foreach ($elements as $showName => $dbName) {
//
//            $elementRes = array();
//
//            $elementRes[$dbName] = $pt->$dbName;
//            $elementRes['dbName'] = $dbName;
//            $elementRes['showName'] = $showName;
//
//            $inRangeName = $dbName . '_inRange';
//            $elementRes[$inRangeName] = '';
//
//            $range = Null;
//
//            $rangeid = $dbName . '_rangeid';
//
//
//            if ($pt->$rangeid && $pt->$rangeid > 0) {
//                $range = R::load('inv_ranges', $pt->$rangeid);
//            }
//
//            if (!(count($range) > 0 )) {
//                $range = R::findOne('inv_ranges', ' ( gender = ? || gender = ?  || gender = ? )  and agefrom <= ? 
//                    and ageto >= ?  and investid = ?  and age_units = ?   and invest_child = ?  
//                    ', [$gender, '', -1, $age, $age, $in->investid, 1, $showName]);
//
//                if (!(count($range) > 0 )) {
//
//                    $range = R::findOne('inv_ranges', ' ( gender = ? || gender = ?   || gender = ?)  and ( agefrom = ?  || agefrom = ?  ) 
//                    and ( ageto = ?  || ageto = ?  )   and investid = ?   and invest_child = ?  
//                    ', [$gender, '', -1, Null, '', Null, '', $in->investid, $showName]);
//                }
//            }
//
//
//            if (count($range) > 0) {
//
//                $n1 = $dbName . '_range_txt';
//                $n2 = $dbName . '_range_low';
//                $n3 = $dbName . '_range_high';
//                $n4 = $dbName . '_invest_units';
//
//                $elementRes[$n1] = $range->range_txt;
//                $elementRes[$n2] = $range->range_low;
//                $elementRes[$n3] = $range->range_high;
//                $elementRes[$n4] = $range->invest_units;
//
//
//                if (is_numeric($range->range_low) && is_numeric($pt->$dbName) && $pt->$dbName < $range->range_low) {
//                    $elementRes[$inRangeName] = 'L';
//                }
//
//                ####
//                elseif (is_numeric($range->range_high) && is_numeric($pt->$dbName) && $pt->$dbName > $range->range_high) {
//                    $elementRes[$inRangeName] = 'H';
//                }
//            }
//
//            $elementsResult[$dbName] = $elementRes;
//        }
//
//        return $pt;
//    }
    #####################################################################
// at the end of class call

    public function __destruct() {
        //echo "Time Elapsed: ".(microtime(true) - $this->time)."s";
//close connection
        R::close();
    }

}
