<?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 = 'addinvestigation';
        $this->shcontrollname = 'addinvestigation';
        $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] = 'programesetting'; //controller name or tab name
        $active[1] = 'addinvestigation'; //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("_sidebar.php");
        require_once("_leftsidebar.php");
        require_once("_footer.php");

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

    public function index() {
        //check login
        autho::checkautho($this->hosturl);
        //check authoize
        autho::checkauthoize($this->controllname, $this->hosturl, $_SESSION['role_id']);

        #################################################################### Heba : ## get all investigation category ##############################################################################
        $allInv = R::findall('investcats', 'deleted = ? and displayorder != ?', [0, -1]);
        $this->smarty->assign('allInv', $allInv);
        ################################################################################################################################################################################
        //call other class with other content
        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
        $this->smarty->display('addinvestigation/show.html');
        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

    public function addnewcat() {
        //check login
        autho::checkautho($this->hosturl);
        //check authoize
        autho::checkauthoize($this->controllname, $this->hosturl, $_SESSION['role_id']);

        #################################################################### Heba : ## add investigation cat  #############################################################################
        $last = R::getAll('select max(displayorder) from investcats');
        $lastOrder = $last[0]['max(displayorder)'];
        $newOrder = $lastOrder + 1;
        $newcat = R::dispense('investcats');
        $newcat->name = "";
        $newcat->displayorder = $newOrder;
        try {
            $id = R::store($newcat);
            ##fatma
            $newcat->id = $id;
            $this->erpProductCategory($newcat);
            ##
            echo $id;
        } catch (Exception $e) {
            header('Location:' . $this->hosturl . '/error/notupdate/' . $this->controllname);
        }
        ################################################################################################################################################################################
    }

    public function addnewinv() {

        //check login
        autho::checkautho($this->hosturl);
        //check authoize
        autho::checkauthoize($this->controllname, $this->hosturl, $_SESSION['role_id']);

        #################################################################### Heba : ## add investigation cat  #############################################################################
        $catId = filter_input(INPUT_POST, "catId");
        $invest_itr = filter_input(INPUT_POST, "invest_itr");

        $newInv = R::dispense('invests');
        $newInv->name = "";
        $newInv->investcatid = $catId;
        $newInv->price = 0;
        $newInv->lab_to_lab_price = 0;
        try {

            $newInv->id = R::store($newInv);

            $this->smarty->assign('invest_itr', $invest_itr);
            $this->smarty->assign('one', $newInv);

            $sampletype = R::findAll('sampletype', 'deleted = 0 order by name');
            $this->smarty->assign('sampletype', $sampletype);

            $this->erpProduct($newInv);

            $this->smarty->display('addinvestigation/investrow.html');
        } catch (Exception $e) {
            header('Location:' . $this->hosturl . '/error/notupdate/' . $this->controllname);
        }
        ################################################################################################################################################################################
    }

    public function update() {

        //check login
        autho::checkautho($this->hosturl);
        //check authoize
        autho::checkauthoize($this->controllname, $this->hosturl, $_SESSION['role_id']);

        $id = filter_input(INPUT_POST, "id");
        $tablename = filter_input(INPUT_POST, "tableName");
        $colname = filter_input(INPUT_POST, "colName");
        $value = filter_input(INPUT_POST, "value");

        try {
            $obj = R::load($tablename, $id);

            $obj->$colname = $value;
            R::store($obj);

            ##fatma
            switch ($tablename) {
                case 'invests':
                    if ($colname == "name" || $colname == "price")
                        $this->erpProduct($obj);
                    break;
                case 'investcats':
                    $this->erpProductCategory($obj);
                    break;
            }
            ##
            //header('Location:' . $this->hosturl . '/controllers/' . $this->controllname.'.php?ac=index' );
        } catch (Exception $e) {
            header('Location:' . $this->hosturl . '/error/notupdate/' . $this->controllname);
        }
    }

    public function del() {      ## delete row from investcat table
        //check login
        autho::checkautho($this->hosturl);
        //check authoize
        autho::checkauthoize($this->controllname, $this->hosturl, $_SESSION['role_id']);

        $id = filter_input(INPUT_POST, "id");
        $tableName = filter_input(INPUT_POST, "tableName");

        $ogj = R::load($tableName, $id);
        $ogj->deleted = 1;

        try {
            R::store($ogj);

            ##fatma
            switch ($tableName) {
                case 'invests':
                    $this->erpProductDel($ogj);
                    break;
                case 'investcats':
                    $this->erpProductCatDel($ogj);
                    break;
            }

            echo 1;
        } catch (Exception $e) {
            echo 22;
        }
    }

    public function getinvbycatid() {      ## get all investigation in certain category
        //check login
        autho::checkautho($this->hosturl);
        //check authoize
        autho::checkauthoize($this->controllname, $this->hosturl, $_SESSION['role_id']);

        $catId = filter_input(INPUT_POST, "catId");

        $sampletype = R::findAll('sampletype', 'deleted = 0 order by name');
        $this->smarty->assign('sampletype', $sampletype);



        $invests = R::findall('invests', 'investcatid = ? and deleted = ?', [$catId, 0]);
        foreach ($invests as $value) {
            $value->range = str_ireplace('<br />', "\r\n", $value->range);
            $value->range = trim($value->range);
        }

        $this->smarty->assign('invests', $invests);
        $this->smarty->assign('invest_itr', count($invests));
        $this->smarty->display('addinvestigation/invests.html');
    }

    public function addtofavorite() {      ## get all investigation in certain category
        //check login
        autho::checkautho($this->hosturl);
        //check authoize
        autho::checkauthoize($this->controllname, $this->hosturl, $_SESSION['role_id']);

        $invId = filter_input(INPUT_POST, "invId");
        $value = filter_input(INPUT_POST, "value");

        try {
            $inv = R::load('invests', $invId);

            $inv->favorite = $value;
            R::store($inv);
        } catch (Exception $e) {
            header('Location:' . $this->hosturl . '/error/notupdate/' . $this->controllname);
        }
    }

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

        //vars here
        $investid = filter_input(INPUT_POST, 'investid');
        $newitem = filter_input(INPUT_POST, 'newitem');

        try {


            $record = R::dispense('sampletype');
            $record->name = $newitem;
            $record->deleted = 0;
            $id = R::store($record);


            $invests = R::load('invests', $investid);
            $invests->sampletype = $id;
            R::store($invests);

            echo $id;
        } catch (Exception $e) {
            
        }
    }

    public function ranges() {
        //check login
        autho::checkautho($this->hosturl);
        //check authoize
        autho::checkauthoize($this->controllname, $this->hosturl, $_SESSION['role_id']);

        ###########################################################################
//        $allCats = R::findall('investcats', 'deleted = ? and displayorder != ?', [0, -1]);
//        $this->smarty->assign('allCats', $allCats);
        ###########################################################################
        //call other class with other content
        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
        $this->smarty->display('addinvestigation/ranges.html');
        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

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


        $sIndexColumn = "id";

        /* DB table to use */
        $sTable = "inv_ranges";

        $sJoin = ' ';

        $aColumns = array('inv_ranges.id', 'invests.name', 'inv_ranges.invest_child'
            , 'investcats.name'
            , 'inv_ranges.range_txt', 'inv_ranges.agefrom', 'inv_ranges.age_units', 'inv_ranges.range_low'
            , 'inv_ranges.range_high', 'inv_ranges.invest_units', 'inv_ranges.ageto', 'inv_ranges.gender');

        $sJoin .= ' JOIN invests        ON inv_ranges.investid = invests.id';
        $sJoin .= ' JOIN investcats   ON invests.investcatid = investcats.id';

        // Indexed column (used for fast and accurate table cardinality)

        /*
         * Paging
         */
        $sLimit = "";
        if (isset($_GET['iDisplayStart']) && $_GET['iDisplayLength'] != '-1') {
            $sLimit = "LIMIT " . intval($_GET['iDisplayStart']) . ", " .
                    intval($_GET['iDisplayLength']);
        }


        /*
         * Ordering
         */
        $sOrder = "";
        if (isset($_GET['iSortCol_0'])) {
            $sOrder = "ORDER BY  ";
            for ($i = 0; $i < intval($_GET['iSortingCols']); $i++) {
                if ($_GET['bSortable_' . intval($_GET['iSortCol_' . $i])] == "true") {
                    $sOrder .= $aColumns[intval($_GET['iSortCol_' . $i])] . "
                    " . ($_GET['sSortDir_' . $i] === 'asc' ? 'asc' : 'desc') . ", ";
                }
            }

            $sOrder = substr_replace($sOrder, "", -2);
            if ($sOrder == "ORDER BY") {
                $sOrder = "";
            }
        }


        /*
         * Filtering
         * NOTE this does not match the built-in DataTables filtering which does it
         * word by word on any field. It's possible to do here, but concerned about efficiency
         * on very large tables, and MySQL's regex functionality is very limited
         */
        $sWhere = "";
        if (isset($_GET['sSearch']) && $_GET['sSearch'] != "") {
            $sWhere = "WHERE (";
            for ($i = 0; $i < count($aColumns); $i++) {
                if (isset($_GET['bSearchable_' . $i]) && $_GET['bSearchable_' . $i] == "true") {
                    $sWhere .= $aColumns[$i] . " LIKE '%" . $_GET['sSearch'] . "%' OR ";
                }
            }
            $sWhere = substr_replace($sWhere, "", -3);
            $sWhere .= ')';
        }

        /* Individual column filtering */
        for ($i = 0; $i < count($aColumns); $i++) {
            if (isset($_GET['bSearchable_' . $i]) && $_GET['bSearchable_' . $i] == "true" && $_GET['sSearch_' . $i] != '') {
                if ($sWhere == "") {
                    $sWhere = "WHERE ";
                } else {
                    $sWhere .= " AND ";
                }
                $sWhere .= $aColumns[$i] . " LIKE '%" . $_GET['sSearch_' . $i] . "%' ";
            }
        }


        $aColumns = array('inv_ranges.id', 'invests.name as invname', 'inv_ranges.invest_child'
            , 'investcats.name as catname'
            , 'CASE   WHEN inv_ranges.age_units = 1 then "Year "
                            WHEN inv_ranges.age_units = 2 then "Month  "
                            WHEN inv_ranges.age_units = 3 then "Day"
                            WHEN inv_ranges.age_units = 4 then "Hour"
                              WHEN inv_ranges.age_units = 5 then "Minute"
                                WHEN inv_ranges.age_units = 6 then "Second"
                             ELSE "" END
                             AS age_units'
            , 'CASE   WHEN inv_ranges.gender = 0 then "F "
                            WHEN inv_ranges.gender = 1 then "M  "
                             ELSE "All" END
                             AS gender'
            , 'inv_ranges.range_txt', 'inv_ranges.agefrom', 'inv_ranges.ageto', 'inv_ranges.range_low'
            , 'inv_ranges.range_high', 'inv_ranges.invest_units');

        $sQuery = "SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $aColumns)) . " FROM  $sTable $sJoin  $sWhere $sOrder $sLimit ";
//        print_r($sQuery);
        $rResult = R::getAll($sQuery);

        /* Data set length after filtering */
        $sQuery = "SELECT FOUND_ROWS()";

        $rResultFilterTotal = R::getAll($sQuery);
        //$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
        $iFilteredTotal = $rResultFilterTotal[0];
        //print_r($iFilteredTotal["FOUND_ROWS()"]);
        /* Total data set length */
        $sQuery = "SELECT $sIndexColumn FROM  $sTable ";
        $rResultTotal = R::getAll($sQuery);
        //$aResultTotal = mysql_fetch_array($rResultTotal);
        $iTotal = count($rResultTotal);

        $aColumns = array('id', 'catname', 'invname', 'invest_child'
            , 'range_txt', 'range_low', 'range_high', 'invest_units', 'agefrom', 'ageto', 'age_units', 'gender');



        $output = array(
            //"sEcho" => intval($_GET['sEcho']),
            "iTotalRecords" => $iTotal,
            "iTotalDisplayRecords" => $iFilteredTotal["FOUND_ROWS()"],
            "aaData" => array()
        );


        foreach ($rResult as $aRow) {
            $row = array();
            for ($i = 0; $i < count($aColumns); $i++) {
                if ($aColumns[$i] == "agefrom") {
                    $row[] = $aRow["agefrom"] . ' - ' . $aRow["ageto"];
                } else if ($aColumns[$i] == "ageto") {
                    
                } else if ($aColumns[$i] != ' ') {
                    /* General output */
                    $row[] = $aRow[$aColumns[$i]];
                }
            }
            $row[] = "<a class=edit href=javascript:;>Edit </a>";
            $row[] = "<a class=delete href=javascript:;>Delete </a>";
            $output['aaData'][] = $row;
        }
//        print_r($output);
        echo json_encode($output);
    }

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

        $type = filter_input(INPUT_POST, 'type');
        switch ($type) {
#############################################################################
            case 'cats':
                $data = R::findall('investcats', 'deleted = ? and displayorder != ?', [0, -1]);
                echo '<option value=""></option>';
                foreach ($data as $single) {
                    echo '<option value="' . rtrim($single->id) . '"> ' . rtrim($single->name) . ' </option>';
                }
                break;
#############################################################################
            case 'invests':
                $catId = filter_input(INPUT_POST, 'catid');
                $invests = R::findall('invests', 'investcatid = ? and deleted = ?', [$catId, 0]);
                echo '<option value=""></option>';
                foreach ($invests as $single) {
                    echo '<option value="' . rtrim($single->id) . '"> ' . rtrim($single->name) . ' </option>';
                }
                break;
#############################################################################
            case 'elements':
                $investid = filter_input(INPUT_POST, 'investid');
                $this->loadElements($investid);
                break;
#############################################################################
        }
    }

    function loadElements($investid) {
        $invest = R::load('invests', $investid);
        switch ($invest->special_inv_type) {
            #############################################################################
            case 1:
                $elements = array("Hgb", "RBC", "Plt", "WBC", "Hct", "MCV", "MCH", "MCHC", "RDW", "Hgb %", "Mentzer Index %"
                    , "Other Cells", "Segmented", "Band", "Pct", "MPV", "PDW", "NRBC / 100 WBC", "Promyelocyte"
                    , "Myelocytes", "Juvenile", "Blast"
                    , "Relative Neutrophil", "Relative Lymphocytes", "Relative Monocytes", "Relative Eosinophils", "Relative Basophils"
                    , "Absolute Neutrophil", "Absolute Lymphocytes", "Absolute Monocytes", "Absolute Eosinophils", "Absolute Basophils"
                );
                break;

            #############################################################################
            case 2:
                $elements = array("Volume", "Color", "Odor", "Aspect", "Deposit", "Reaction", "Protein"
                    , "Sugar", "Acetone", "Nitrite", "Specific Gravity", "Bilirubin", "Urobilinigen", "Leucocytes"
                    , "Blood", "Pus Cells", "R.B.Cs", "Epithelial Cells", "Casts", "Crystal", "Amorphous"
                    , "Ova", "Trophazite", "Mucus", "Urine Artifacts", "Fungi", "Bacteria"
                );
                break;

            #############################################################################
            case 3:
                $elements = array("Consistency", "Food Particles", "Blood", "Color", "Reaction", "Odor"
                    , "Mucus", "Proglottids", "Flagellates", "Ciliate", "Undigested Food", "Parasitology Artifacts"
                    , "WBCs", "RBCs", "Trophozoite", "Cysts", "Ova", "Larva"
                );
                break;

            #############################################################################
            case 4:

                break;

            #############################################################################
            case 5:
                $elements = array("hour1", "hour2");
                break;

            #############################################################################
            case 8:
                $elements = array("Bleeding Time", "Clothing Time");
                break;

            #############################################################################
            case 9:
                $elements = array("PT Patient's Time", "APTT Patient's Time", "Concentration"
                    , "I.N.R", "Ratio", "Fibrinogen", "FDP");
                break;

            #############################################################################
            case 10:

                break;
            #############################################################################
            case 11:
                $elements = R::findAll('invest_elements', 'investid = ?  and  deleted = ?  ', [$investid, 0]);
                break;

            #############################################################################
            default :
                $elements = array();
                break;
        }

        echo '<option value=""></option>';

        if ($invest->special_inv_type == 11) {
            foreach ($elements as $single) {
                echo '<option value="' . rtrim($single->show_name) . '"> ' . rtrim($single->show_name) . ' </option>';
            }
        } else {
            foreach ($elements as $single) {
                echo '<option value="' . rtrim($single) . '"> ' . rtrim($single) . ' </option>';
            }
        }
    }

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

        $investid = filter_input(INPUT_POST, 'investid');
        $element = filter_input(INPUT_POST, 'element');
        $range_txt = filter_input(INPUT_POST, 'range_txt');
        $range_low = filter_input(INPUT_POST, 'range_low');
        $range_high = filter_input(INPUT_POST, 'range_high');
        $invest_units = filter_input(INPUT_POST, 'invest_units');
        $agefrom = filter_input(INPUT_POST, 'agefrom');
        $ageto = filter_input(INPUT_POST, 'ageto');
        $age_units = filter_input(INPUT_POST, 'age_units');
        $gender = filter_input(INPUT_POST, 'gender');
        $id = filter_input(INPUT_POST, 'id');


        $invest = R::load('invests', $investid);

        if ($id > 0) {
            $range = R::load('inv_ranges', $id);
        } else {
            $range = R::xdispense('inv_ranges');
        }

        $range_txt = str_replace("<br>", "\n", $range_txt);

        $range->investid = $investid;
        $range->invest_child = $element;
        $range->special_inv_type = $invest->special_inv_type;
        $range->range_txt = $range_txt;
        $range->agefrom = $agefrom;
        $range->ageto = $ageto;
        $range->age_units = $age_units;
        $range->range_low = $range_low;
        $range->range_high = $range_high;
        $range->invest_units = $invest_units;
        $range->gender = $gender;

        try {
            R::store($range);
            echo 1;
        } catch (Exception $ex) {
            echo 'sorry !!!! Error happened .. try again later  ';
        }
    }

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


        //here will load the menu show
        $id = filter_input(INPUT_POST, 'id');
        $range = R::load('inv_ranges', $id);
        try {
            R::trash($range);
            echo 1;
        } catch (Exception $ex) {
            echo 'sorry !!!! drug could not be deleted';
        }
    }

    function geteditedrow() {
        $id = filter_input(INPUT_POST, 'id');

        $range = R::getRow('select * from inv_ranges where id = ? ', [$id]);

        $invest = R::load('invests', $range['investid']);

        $range['catid'] = $invest->investcatid;

        echo json_encode($range);
    }

    ################################################################

    public function getinvpreview() {
        //check login
        autho::checkautho($this->hosturl);
        //check authoize
//        autho::checkauthoize($this->controllname, $this->hosturl, $_SESSION['role_id']);
//        $investId = 11;
        $investId = filter_input(INPUT_GET, "investId");

        $invest = R::load('invests', $investId);

        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);

        $this->smarty->display('addinvestigation/preview/labs_head.html');

        switch ($invest->special_inv_type) {
            ## urine
            case 2:
                $this->loadUrineSelects();
                $this->smarty->assign('invest', $invest);
                $this->smarty->display('addinvestigation/preview/labs_2.html');
                break;

            ## stool
            case 3:
                $this->loadStoolSelects();
                $this->smarty->assign('invest', $invest);
                $this->smarty->display('addinvestigation/preview/labs_3.html');
                break;

            ## blood picture
            case 1:
                $invest = $this->loadBloodRanges($invest);
                $this->smarty->assign('invest', $invest);
                $this->smarty->display('addinvestigation/preview/labs_1.html');
                break;

            ## Culture
            case 4:
                $this->loadCultureSelects();
                $this->loadAllAbbr();
                $this->smarty->assign('invest', $invest);
                $this->smarty->display('addinvestigation/preview/labs_4.html');
                break;

            ## ESR
            case 5:
                $this->loadEsrSelects();
                $invest = $this->loadEsrRanges($invest);
                $this->smarty->assign('invest', $invest);
                $this->smarty->display('addinvestigation/preview/labs_5.html');
                break;

            ## Cross Matching
            case 6:
                $this->loadCrossSelects();
                $this->smarty->assign('invest', $invest);
                $this->smarty->display('addinvestigation/preview/labs_6.html');
                break;

            ## Fluid
            case 7:

                $this->smarty->assign('invest', $invest);
                $this->smarty->display('addinvestigation/preview/labs_7.html');
                break;

            ## Custom
            case 11:
                $this->loadCustomSelects();
                $invest = $this->customInvDetails($invest);
                $this->smarty->assign('invest', $invest);
                $this->smarty->display('addinvestigation/preview/labs_11.html');
                break;

            ## Clothing time
            case 8:
                $invest = $this->loadClothingDetails($invest);
                $this->smarty->assign('invest', $invest);
                $this->smarty->display('addinvestigation/preview/labs_8.html');
                break;

            ## PT
            case 9:
                $invest = $this->loadPtDetails($invest);
                $this->smarty->assign('invest', $invest);
                $this->smarty->display('addinvestigation/preview/labs_9.html');
                break;

            ## ABO/RH
            case 10:
                $this->loadAborhSelects();
                $this->smarty->assign('invest', $invest);
                $this->smarty->display('addinvestigation/preview/labs_10.html');
                break;

            default:
                $invest->ranges = $this->commonRanges($investId);
                $invest->rangesCount = count($invest->ranges);
                $this->smarty->assign('invest', $invest);
                $this->smarty->display('addinvestigation/preview/labs_normal.html');
                break;
        }

        $this->smarty->display('addinvestigation/preview/labs_footer.html');

        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

    function customInvDetails($invest, $print = 0) {
        $elements = R::findAll('invest_elements', 'investid = ?  and  deleted = ?  ', [$invest->id, 0]);

        foreach ($elements as $element) {
            $ranges = R::findAll('inv_ranges', '  investid = ?  and invest_child = ?
                    ', [$invest->id, $element->show_name]);

            $element->ranges = $ranges;
        }


        $invest->elements = $elements;
        return $invest;
    }

    function loadCustomSelects() {
        $data = R::findAll('custom_select', ' deleted = 0 ');
        $this->smarty->assign('custom_select', $data);
    }

    function commonRanges($investId) {
        $ranges = R::findAll('inv_ranges', '  investid = ?   ', [$investId]);
        return $ranges;
    }

    function loadUrineSelects() {
        $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) {
            $tableName = 'urine_' . $tableName;
            $data = R::findAll($tableName, ' deleted = 0 ');
            $this->smarty->assign($tableName, $data);
        }
    }

    function loadStoolSelects() {
        $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) {
            $tableName = 'stool_' . $tableName;
            $data = R::findAll($tableName, ' deleted = 0 ');
            $this->smarty->assign($tableName, $data);
        }
    }

    function loadBloodRanges($invest) {

        $elements = array("Hgb" => "hgb", "RBC" => "rbc", "Plt" => "plt", "WBC" => "wbc");

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

            $rangestxt = $dbName . '_ranges';

            $ranges = R::findAll('inv_ranges', '  investid = ?  and invest_child = ?
                    ', [$invest->id, $showName]);

            $invest->$rangestxt = $ranges;
        }

        return $invest;
    }

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

        foreach ($tables as $tableName) {
            $tableName = 'culture_' . $tableName;
            $data = R::findAll($tableName, ' deleted = 0 ');
            $this->smarty->assign($tableName, $data);
        }


        #### urine part

        $table = array("reaction", "pus", "pus2", "pbcs", "pbcs2", "epithelial", "crystal"
            , "fungi", "bacteria", "other", "other1", "other2", "other3", "other4"
            , "gram", "zn_film"
        );

        foreach ($table as $tableNam) {
            $tableNam = 'urine_' . $tableNam;
            $data = R::findAll($tableNam, ' deleted = 0 ');
            $this->smarty->assign($tableNam, $data);
        }
    }

    function loadAllAbbr() {

        $sql = 'select * from antibiotic_sensitivity where deleted != 1
                 order by abbr asc ';


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

        $this->smarty->assign('antibiotic_sensitivity', $antibiotic_sensitivity);
        $this->smarty->assign('allAbrRslt', $antibiotic_sensitivity);
    }

    function loadEsrSelects() {
        $tables = array("hour1", "hour2");

        foreach ($tables as $tableName) {
            $tableName = 'esr_' . $tableName;
            $data = R::findAll($tableName, ' deleted = 0 ');
            $this->smarty->assign($tableName, $data);
        }
    }

    function loadEsrRanges($invest) {

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

        foreach ($elements as $element) {
            $rangestxt = $element . '_ranges';

            $ranges = R::findAll('inv_ranges', '  investid = ?  and invest_child = ?
                    ', [$invest->id, $element]);

            $invest->$rangestxt = $ranges;
        }

        return $invest;
    }

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

        foreach ($tables as $tableName) {
            $tableName = 'cross_' . $tableName;
            $data = R::findAll($tableName, ' deleted = 0 ');
            $this->smarty->assign($tableName, $data);
        }
    }

    function loadAborhSelects() {
        $tables = array("abo", "rh");

        foreach ($tables as $tableName) {
            $tableName = 'cross_' . $tableName;
            $data = R::findAll($tableName, ' deleted = 0 ');
            $this->smarty->assign($tableName, $data);
        }
    }

    function loadClothingDetails($invest) {

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

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

            $rangestxt = $dbName . '_ranges';

            $ranges = R::findAll('inv_ranges', '  investid = ?  and invest_child = ?
                    ', [$invest->id, $showName]);

            $invest->$rangestxt = $ranges;
        }

        return $invest;
    }

    function loadPtDetails($invest) {

        $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) {

            $rangestxt = $dbName . '_ranges';

            $ranges = R::findAll('inv_ranges', '  investid = ?  and invest_child = ?
                    ', [$invest->id, $showName]);

            $invest->$rangestxt = $ranges;
        }

        return $invest;
    }

    ###########################################  custom investigations ########

    public function getcustom() {      ## get custom  investigation details
        //check login
        autho::checkautho($this->hosturl);
        //check authoize
//        autho::checkauthoize($this->controllname, $this->hosturl, $_SESSION['role_id']);

        $invId = filter_input(INPUT_POST, "invId");
        $invest = R::load('invests', $invId);

        $this->smarty->assign('customInvId', $invId);
        $this->smarty->assign('customInvName', $invest->name);

        $elements = R::findAll('invest_elements', 'investid = ?  and  deleted = ?  ', [$invId, 0]);
        $this->smarty->assign('invest_elements', $elements);

        $this->smarty->assign('custom_itr', count($elements));
        $this->smarty->display('addinvestigation/custom.html');
    }

    public function newcustomelement() {

        //check login
        autho::checkautho($this->hosturl);
        //check authoize
//        autho::checkauthoize($this->controllname, $this->hosturl, $_SESSION['role_id']);
        #################################################################### Heba : ## add investigation cat  #############################################################################
        $invId = filter_input(INPUT_POST, "invId");

        $element = R::xdispense('invest_elements');
        $element->show_name = "";
        $element->store_name = "";
        $element->investid = $invId;
        $element->deleted = 0;
        try {

            $id = R::store($element);
            $element->store_name = 'element' . $id;
            R::store($element);

            echo $id;
        } catch (Exception $e) {
            echo -1;
        }
        ################################################################################################################################################################################
    }

    ##fatma

    public function addAllInvestigationsToERP() {
        $programesetting = R::load('programesetting', 1);
        if (!empty($programesetting->erpdb)) {//if exist addDatabase erpDB
            try {
                R::addDatabase('erpDB', 'mysql:host=' . DB_HOST . ';dbname=' . $programesetting->erpdb . '', DB_USER, DB_PASS);
                $lastId = 0;
                $limit = 50;
                R::selectDatabase('default');
                do {
                    $rows = 0;
                    R::selectDatabase('default');
                    $allInvestCats = R::findAll("investcats", " where id > $lastId and deleted = 0 order by id asc limit $limit ");
                    R::selectDatabase('erpDB');
                    if (R::testConnection()) {
                        foreach ($allInvestCats as $investgationCat) {
                            $productCatData = R::getRow('select productCatId from productcat where obygyInvestigationCatId = ' . $investgationCat->id);
                            if (empty($productCatData['productCatId'])) {
                                //here use NULL for new insert
                                //better to add client using erp files it self so use  curl "it is like post requist but in php"
                                $this->curlAddProductCategory($investgationCat, $programesetting->erpdb);
                            } else {
                                //here use id to update
                                $this->curlupdateProductCategory($investgationCat, $programesetting->erpdb, $productCatData['productCatId']);
                            }
                            $rows++;
                            $lastId = $investgationCat->id;
                        }
                    } else {
                        throw new Exception('Unable to connect to erpDB,make sure database name is ok and connected or clear its name in setup');
                    }
                } while ($rows > 0);
                //////////now
                $lastId = 0;
                $limit = 50;
                R::selectDatabase('default');
                do {
                    $rows = 0;
                    R::selectDatabase('default');
                    $allInvests = R::findAll("invests", " where id > $lastId and deleted=0  order by id asc limit $limit ");
                    R::selectDatabase('erpDB');
                    if (R::testConnection()) {
                        foreach ($allInvests as $investigation) {
                            $productData = R::getRow('select productId from product where obygyInvestigationId = ' . $investigation->id);
                            $productCatData = R::getRow('select productCatId from productcat where obygyInvestigationCatId = ' . $investigation['investcatid']);
                            $investigation->catid = $productCatData['productCatId'];
                            if (empty($productData['productId'])) {
                                //here use NULL for new insert
                                //better to add client using erp files it self so use  curl "it is like post requist but in php"
                                $this->curlAddProduct($investigation, $programesetting->erpdb);
                            } else {
                                //here use id to update
                                $this->curlupdateProduct($investigation, $programesetting->erpdb, $productData['productId']);
                            }
                            $rows++;
                            $lastId = $investigation->id;
                        }
                    } else {
                        throw new Exception('Unable to connect to erpDB,make sure database name is ok and connected or clear its name in setup');
                    }
                } while ($rows > 0);
                echo 1;
            } catch (Exception $exc) {
                echo -1;
                //header('Location:' . $this->hosturl . '/controllers/' . $this->controllname . '.php?ac=show');
            }
            R::selectDatabase('default');
        }
    }

    /* -- cat ------------------------ */

    public function erpProductCategory($investgationCat) {//detections
        $programesetting = R::load('programesetting', 1);
        if (!empty($programesetting->erpdb)) {//if exist addDatabase erpDB
            try {
                R::addDatabase('erpDB', 'mysql:host=' . DB_HOST . ';dbname=' . $programesetting->erpdb . '', DB_USER, DB_PASS);
                R::selectDatabase('erpDB');
                if (R::testConnection()) {
                    $productCatData = R::getRow('select productCatId from productcat where obygyInvestigationCatId = ' . $investgationCat->id);
                    if (empty($productCatData['productCatId'])) {
                        //here use NULL for new insert
                        //better to add client using erp files it self so use  curl "it is like post requist but in php"
                        $this->curlAddProductCategory($investgationCat, $programesetting->erpdb);
                    } else {
                        //here use id to update
                        $this->curlupdateProductCategory($investgationCat, $programesetting->erpdb, $productCatData['productCatId']);
                    }
                } else {
                    throw new Exception('Unable to connect to erpDB,make sure database name is ok and connected or clear its name in setup');
                }
            } catch (Exception $exc) {
                //header('Location:' . $this->hosturl . '/controllers/' . $this->controllname . '.php?ac=show');
                //echo -1;
            }
            R::selectDatabase('default');
        }
    }

    public function erpProductCatDel($investgationCat) {//detections
        $programesetting = R::load('programesetting', 1);
        if (!empty($programesetting->erpdb)) {//if exist addDatabase erpDB
            try {
                R::addDatabase('erpDB', 'mysql:host=' . DB_HOST . ';dbname=' . $programesetting->erpdb . '', DB_USER, DB_PASS);
                R::selectDatabase('erpDB');
                if (R::testConnection()) {
                    $productCatData = R::getRow('select productCatId from productcat where obygyInvestigationCatId = ' . $investgationCat["id"]);
                    $this->curlDelProductCategory($programesetting->erpdb, $productCatData['productCatId']);
                } else {
                    throw new Exception('Unable to connect to erpDB,make sure database name is ok and connected or clear its name in setup');
                }
                R::selectDatabase('default');
            } catch (Exception $exc) {
                //header('Location:' . $this->hosturl . '/controllers/' . $this->controllname . '.php?ac=show');
                //echo -1;
            }
        }
    }

    //better to add client using erp files it self so use  curl "it is like post requist but in php"
    //category has fixed val = 'obygy services'
    public function curlAddProductCategory($investgationCat, $erpDB) {
        // set post fields
        $post = [
            'erpDB' => $erpDB,
            'obygyInvestigationCatId' => $investgationCat->id,
            'productCatName' => $investgationCat["name"],
            'curlpost' => 1,
        ];
        try {
            $ch = curl_init(HOST_URL_ERP . '/controllers/productCatController.php?do=add2SimpleReturn');
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
            curl_setopt($ch, CURLOPT_POSTFIELDS, $post);

            // execute!
            $response = curl_exec($ch);

            // close the connection, release resources used
            curl_close($ch);
        } catch (Exception $exc) {
            //header('Location:' . $this->hosturl . '/controllers/' . $this->controllname . '.php?ac=show');
            //echo -1;
        }
        // do anything you want with your response
//        var_dump($response);
    }

    //category has fixed val = 'obygy services'
    public function curlupdateProductCategory($investgationCat, $erpDB, $productCatIdInERP) {
        // set post fields
        $post = [
            'erpDB' => $erpDB,
            'productCatId' => $productCatIdInERP,
            'productCatName' => $investgationCat["name"],
            'curlpost' => 1,
        ];
        try {
            $ch = curl_init(HOST_URL_ERP . '/controllers/productCatController.php?do=update');
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
            curl_setopt($ch, CURLOPT_POSTFIELDS, $post);

            // execute!
            $response = curl_exec($ch);

            // close the connection, release resources used
            curl_close($ch);
        } catch (Exception $exc) {
            //header('Location:' . $this->hosturl . '/controllers/' . $this->controllname . '.php?ac=show');
            //echo -1;
        }

        // do anything you want with your response
//        var_dump($response);
    }

    public function curlDelProductCategory($erpDB, $productCatId) {
        // set post fields
        $post = [
            'erpDB' => $erpDB,
        ];
        try {
            $ch = curl_init(HOST_URL_ERP . '/controllers/productCatController.php?do=tempdelete&id=' . $productCatId);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
            curl_setopt($ch, CURLOPT_POSTFIELDS, $post);

            // execute!
            $response = curl_exec($ch);

            // close the connection, release resources used
            curl_close($ch);
        } catch (Exception $exc) {
            //header('Location:' . $this->hosturl . '/controllers/' . $this->controllname . '.php?ac=show');
            //echo -1;
        }

        // do anything you want with your response
//        var_dump($response);
    }

    /* -- product ------------------------ */

    public function erpProduct($investigation) {//invests
        $programesetting = R::load('programesetting', 1);
        if (!empty($programesetting->erpdb)) {//if exist addDatabase erpDB
            try {//investcatid
                R::addDatabase('erpDB', 'mysql:host=' . DB_HOST . ';dbname=' . $programesetting->erpdb . '', DB_USER, DB_PASS);
                R::selectDatabase('erpDB');
                if (R::testConnection()) {
                    $productData = R::getRow('select productId from product where obygyInvestigationId = ' . $investigation->id);
                    $productCatData = R::getRow('select productCatId from productcat where obygyInvestigationCatId = ' . $investigation['investcatid']);
                    $investigation->catid = $productCatData['productCatId'];
                    if (empty($productData['productId'])) {
                        //here use NULL for new insert
                        //better to add client using erp files it self so use  curl "it is like post requist but in php"
                        $this->curlAddProduct($investigation, $programesetting->erpdb);
                    } else {
                        //here use id to update
                        $this->curlupdateProduct($investigation, $programesetting->erpdb, $productData['productId']);
                    }
                } else {
                    throw new Exception('Unable to connect to erpDB,make sure database name is ok and connected or clear its name in setup');
                }
            } catch (Exception $exc) {
                //header('Location:' . $this->hosturl . '/controllers/' . $this->controllname . '.php?ac=show');
                //echo -1;
            }
            R::selectDatabase('default');
        }
    }

    public function erpProductDel($investigation) {//invests
        $programesetting = R::load('programesetting', 1);
        if (!empty($programesetting->erpdb)) {//if exist addDatabase erpDB
            try {
                R::addDatabase('erpDB', 'mysql:host=' . DB_HOST . ';dbname=' . $programesetting->erpdb . '', DB_USER, DB_PASS);
                R::selectDatabase('erpDB');
                if (R::testConnection()) {
                    $productData = R::getRow('select productId from product where obygyInvestigationId = ' . $investigation["id"]);
                    $this->curlDelProduct($programesetting->erpdb, $productData['productId']);
                } else {
                    throw new Exception('Unable to connect to erpDB,make sure database name is ok and connected or clear its name in setup');
                }
                R::selectDatabase('default');
            } catch (Exception $exc) {
                //header('Location:' . $this->hosturl . '/controllers/' . $this->controllname . '.php?ac=show');
                //echo -1;
            }
        }
    }

    //better to add client using erp files it self so use  curl "it is like post requist but in php"
    public function curlAddProduct($investigation, $erpDB) {
        // set post fields
        $post = [
            'erpDB' => $erpDB,
            'investigation_id' => $investigation->id,
            'new_cat_id' => $investigation->catid,
            'level' => 'obygy',
            'new_productName' => $investigation->name,
            'new_parcode' => '',
            'new_productBuyPrice' => 0,
            'new_productbuypricereal' => 0,
            'new_buypricereal_precentage1' => 0,
            'new_productSellAllPrice' => $detection->price,
            'new_buytotal_precentage1' => 0,
            'new_productSellHalfPrice' => $detection->price,
            'new_buyhalf_precentage1' => 0,
            'new_productSellUnitPrice' => $detection->price,
            'new_buypart_precentage1' => 0,
            'new_productquantity' => 0,
            'new_isservice1' => 1,
            'inMenu1' => 1,
            'type1' => 0,
            'new_productDate' => date('Y-m-d'),
            'expireDate' => 0,
            'productunit1' => 1,
            'productnumber1' => 1,
            'unitsItr' => 1,
        ];
        try {
            $ch = curl_init(HOST_URL_ERP . '/controllers/buyBillControllerAjax.php?do=temp&itr=1&tabIndex=-1&billnameid=1');
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
            curl_setopt($ch, CURLOPT_POSTFIELDS, $post);

            // execute!
            $response = curl_exec($ch);

            // close the connection, release resources used
            curl_close($ch);
        } catch (Exception $exc) {
            //header('Location:' . $this->hosturl . '/controllers/' . $this->controllname . '.php?ac=show');
            //echo -1;
        }
        // do anything you want with your response
//        var_dump($response);
    }

    public function curlupdateProduct($investigation, $erpDB, $productId) {
        // set post fields
        $post = [
            'erpDB' => $erpDB,
            'productId' => $productId,
            'investigation_id' => $investigation->id,
            'productCatId' => $investigation->catid,
            'level' => 'obygy',
            'productName1' => $investigation->name,
            'parcode1' => '',
            'productBuyPrice1' => 0,
            'productbuypricereal' => 0,
            'buypricereal_precentage1' => 0,
            'productSellAllPrice1' => $investigation->price,
            'buytotal_precentage1' => 0,
            'productSellHalfPrice1' => $investigation->price,
            'buyhalf_precentage1' => 0,
            'productSellUnitPrice1' => $investigation->price,
            'buypart_precentage1' => 0,
            'productquantity1' => 0,
            'isservice1' => 1,
            'inMenu1' => 1,
            'manufacture' => 0,
            'productDate1' => date('Y-m-d'),
            'expireDate1' => 0,
            'productunit1' => 1,
            'productnumber1' => 1,
            'unitsItr1' => 1,
        ];
        try {
            $ch = curl_init(HOST_URL_ERP . '/controllers/productControllerAjax.php?do=update');
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
            curl_setopt($ch, CURLOPT_POSTFIELDS, $post);

            // execute!
            $response = curl_exec($ch);

            // close the connection, release resources used
            curl_close($ch);
        } catch (Exception $exc) {
            //header('Location:' . $this->hosturl . '/controllers/' . $this->controllname . '.php?ac=show');
            //echo -1;
        }

        // do anything you want with your response
//        var_dump($response);
    }

    public function curlDelProduct($erpDB, $productId) {
        // set post fields
        $post = [
            'erpDB' => $erpDB,
            'productId' => $productId,
        ];
        try {
            $ch = curl_init(HOST_URL_ERP . '/controllers/productControllerAjax.php?do=deleteFinaly');
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
            curl_setopt($ch, CURLOPT_POSTFIELDS, $post);

            // execute!
            $response = curl_exec($ch);

            // close the connection, release resources used
            curl_close($ch);
        } catch (Exception $exc) {
            //header('Location:' . $this->hosturl . '/controllers/' . $this->controllname . '.php?ac=show');
            //echo -1;
        }

        // do anything you want with your response
//        var_dump($response);
    }

    ############################################################################

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

}
