<?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 = 'detection';
        $this->shcontrollname = 'detection';
        $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] = 'detection'; //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);
        });

        require_once("visits_common.php"); //general functions
//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 show() {
        //check login
        autho::checkautho($this->hosturl);
        //check authoize
        autho::checkauthoize($this->controllname, $this->hosturl, $_SESSION['role_id']);

        ####################################################################
        $programesetting = R::findOne('programesetting');
        $this->smarty->assign('programesetting', $programesetting);

        $detections = R::findAll('detections', 'del = 0');
        $this->smarty->assign('detections', $detections);
        if ($programesetting->system_type == 2) {
            $allDocs = R::getAll('SELECT awusers.user_id  , awusers.name    FROM awusers
                                                join  awrole     on awrole.id =  awusers.role_id
                                                WHERE awusers.deleted = 0  and awrole.doctor_show = 1 and awrole.visit_show = 1  ');

            $this->smarty->assign('allDocs', $allDocs);
        }
        #####################################################################
        //call other class with other content
        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
        $this->smarty->display('detection/show.html');
        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

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

        $programesetting = R::findOne('programesetting');
        $this->smarty->assign('programesetting', $programesetting);

        $detection = R::dispense('detections');
        $detection->del = 0;
        $detection->detectionval = 0;
        $detection->title = '';
        $detection->create_date = date('Y-m-d');
        try {
            $detection->id = R::store($detection);
            $this->erpProduct($detection);

            if ($programesetting->system_type == 2) {
                $allDocs = R::getAll('SELECT awusers.user_id  , awusers.name    FROM awusers
                                                join  awrole     on awrole.id =  awusers.role_id
                                                WHERE awusers.deleted = 0  and awrole.doctor_show = 1 and awrole.visit_show = 1  ');

                $this->smarty->assign('allDocs', $allDocs);
            }
            $this->smarty->assign('mydata', $detection);
            $this->smarty->display('detection/detection_row.html');
        } catch (Exception $e) {
            echo -1;
        }
    }

    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);
            $this->erpProduct($obj);
        } catch (Exception $e) {
            
        }
    }

    public function updateajax() {

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

        $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);
            $this->erpProduct($obj);
        } catch (Exception $e) {
            
        }
    }

    public function erpProduct($detection) {//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()) {
                    $productData = R::getRow('select productId from product where obygyDetectionId = ' . $detection->id);
                    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($detection, $programesetting->erpdb);
                    } else {
                        //here use id to update
                        $this->curlupdateProduct($detection, $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($detection) {//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()) {
                    $productData = R::getRow('select productId from product where obygyDetectionId = ' . $detection->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"
    //category has fixed val = 'services'
    public function curlAddProduct($detection, $erpDB) {
        // set post fields
        $post = [
            'erpDB' => $erpDB,
            'detection_id' => $detection->id,
            'new_cat_id' => 'obygy',
            'level' => 'obygy',
            'obygy_cat_name' => 'Services', //'obygy services'
            'new_productName' => $detection->title,
            'new_parcode' => '',
            'new_productBuyPrice' => 0,
            'new_productbuypricereal' => 0,
            'new_buypricereal_precentage1' => 0,
            'new_productSellAllPrice' => $detection->detectionval,
            'new_buytotal_precentage1' => 0,
            'new_productSellHalfPrice' => $detection->detectionval,
            'new_buyhalf_precentage1' => 0,
            'new_productSellUnitPrice' => $detection->detectionval,
            '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);
    }

    //category has fixed val = 'obygy services'
    public function curlupdateProduct($detection, $erpDB, $productId) {
        // set post fields
        $post = [
            'erpDB' => $erpDB,
            'detection_id' => $detection->id,
            'productId' => $productId,
            'productCatId' => 'obygy',
            'level' => 'obygy',
            'obygy_cat_name' => 'Services', //'obygy services'
            'productName1' => $detection->title,
            'parcode1' => '',
            'productBuyPrice1' => 0,
            'productbuypricereal' => 0,
            'buypricereal_precentage1' => 0,
            'productSellAllPrice1' => $detection->detectionval,
            'buytotal_precentage1' => 0,
            'productSellHalfPrice1' => $detection->detectionval,
            'buyhalf_precentage1' => 0,
            'productSellUnitPrice1' => $detection->detectionval,
            '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);
    }

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

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

        ####################################################################
        $price_lists = R::getAll('select id , name from price_lists where deleted = 0');
        $this->smarty->assign('price_lists', $price_lists);

        $invoffers = R::findall('invoffer', 'del = ? and active = ? ', [0, 1]);
        $this->smarty->assign('invoffers', $invoffers);

        #####################################################################
        //call other class with other content
        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
        $this->smarty->display('organizations/lists.html');
        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

    public function discountDetails() {
        //check login
        autho::checkautho($this->hosturl);
        $id = filter_input(INPUT_POST, "id");
        $cat = filter_input(INPUT_POST, "cat");
        $listId = filter_input(INPUT_POST, "organization");

        ## $cat =   1=>detection     , 2 =>invest    , 3 =>  ray   , 4 =>  offer inv
        try {
            $this->loadListObj($listId);
            $this->loadDetectObj($id, $cat);
            $org_discount = $this->listDiscountObj($listId, $id, $cat);
            $this->smarty->assign('org_discount', $org_discount);
            $this->smarty->display('organizations/details_modal.html');
        } catch (Exception $e) {
            echo $e;
        }
    }

    function listDiscountObj($list_id, $detect_id, $detect_cat, $offerId = 0) {
        $org_discount = R::findOne('organization_discount', ' list_id = ? and detect_id = ?
            and detect_cat = ? and offer_id = ?
                and deleted = ?  ', [$list_id, $detect_id, $detect_cat, $offerId, 0]);
        if (!($org_discount)) {
            $org = r::xdispense('organization_discount');
            $org->list_id = $list_id;
            $org->detect_id = $detect_id;
            $org->detect_cat = $detect_cat;
            $org->offer_id = $offerId;
            r::store($org);
            $org_discount = R::findOne('organization_discount', ' list_id = ? and detect_id = ?    and detect_cat = ?
                 and offer_id = ?   and deleted = ?  ', [$list_id, $detect_id, $detect_cat, $offerId, 0]);
        }

        return $org_discount;
    }

    ## $cat =   1=>detection     , 2 =>invest    , 3 =>  ray   , 4 =>  offer inv

    function loadDetectObj($detect_id, $detect_cat) {
        switch ($detect_cat) {
            case 1:           ## detection
                $detectObj = R::getRow('select id , title as detName , detectionval as detPrice from  detections where id  = ?  ', [$detect_id]);
                break;

            case 2:           ## invest
                $detectObj = R::getRow('select id , name as detName , price as detPrice from  invests where id  = ?  ', [$detect_id]);
                break;

            case 3:           ## ray
                $detectObj = R::getRow('select id , name as detName , price as detPrice from  rays where id  = ?  ', [$detect_id]);
                break;
        }
        $this->smarty->assign('detectObj', $detectObj);
    }

    function loadOrgObj($organization_id) {
        $orgObj = R::getRow('select id , organization_name from  organizations where id  = ?  ', [$organization_id]);
        $this->smarty->assign('orgObj', $orgObj);
    }

    function loadListObj($list_id) {
        $listObj = R::getRow('select id , name from  price_lists where id  = ?  ', [$list_id]);
        $this->smarty->assign('listObj', $listObj);
    }

    public function loadOrgDet() {
        //check login
        autho::checkautho($this->hosturl);
        $listId = filter_input(INPUT_POST, "id");

        try {
            $this->detectionByListId($listId);
            $this->investsByListId($listId);
            $this->raysByListId($listId);
            $this->smarty->display('organizations/details.html');
        } catch (Exception $e) {
            echo $e;
        }
    }

    function detectionByListId($listId) {
        $sql = ' select  id ,   title , detectionval           from    detections     where del != 1  and title != ""      order by title asc ';
        $detections = R::getAll($sql);

        $i = 0;
        foreach ($detections as $detection) {
            $detections[$i]['orgDiscount'] = $this->listDiscountObj($listId, $detection['id'], 1);
            $i++;
        }
        $this->smarty->assign('detections', $detections);
    }

    function investsByListId($listId) {
        $sql = ' select   invests.id ,  invests.name , invests.price  ,   investcats.name  as catName
                                from    invests   
                                join  investcats                     on investcats.id   = invests.investcatid 
                                where  invests.deleted != 1   and invests.name  != ""      order by  invests.investcatid asc ,   invests.name asc  ';
        $invests = R::getAll($sql);

        $i = 0;
        foreach ($invests as $invest) {
            $invests[$i]['orgDiscount'] = $this->listDiscountObj($listId, $invest['id'], 2);
            $i++;
        }
        $this->smarty->assign('invests', $invests);
    }

    function raysByListId($listId) {
        $sql = ' select   rays.id ,  rays.name , rays.price  ,   rayscats.name  as catName
                                from    rays   
                                join  rayscats                       on rayscats.id   = rays.rayscatid 
                                where  rays.deleted != 1   and rays.name  != ""        order by  rays.rayscatid asc ,   rays.name asc  ';
        $rays = R::getAll($sql);

        $i = 0;
        foreach ($rays as $ray) {
            $rays[$i]['orgDiscount'] = $this->listDiscountObj($listId, $ray['id'], 3);
            $i++;
        }
        $this->smarty->assign('rays', $rays);
    }

    function orgOfferDetail() {
        $listId = filter_input(INPUT_POST, "listId");
        $offerId = filter_input(INPUT_POST, "offerId");
        $this->offerByListId($offerId, $listId);
        $this->offerName($offerId);
        $this->loadListObj($listId);
        $this->smarty->display('organizations/offer_invs.html');
    }

    function offerName($offerId) {
        $offerObj = R::getRow('select id , name from  invoffer where id  = ?  ', [$offerId]);
        $this->smarty->assign('offerName', $offerObj['name']);
    }

    function offerByListId($offerId, $listId) {
        $sql = ' select   invofferdetails.id ,  invests.name , invofferdetails.price 
                              ,   investcats.name  as catName   , invofferdetails.invoffer_id
                                from    invofferdetails
                                join  invoffer                          on invoffer.id   = invofferdetails.invoffer_id
                                join  invests                          on invests.id   = invofferdetails.inv_id
                                join  investcats                     on investcats.id   = invests.investcatid
                                where  invests.deleted != 1   and invests.name  != ""
                                and invofferdetails.del  != 1  and invofferdetails.invoffer_id =  ' . $offerId . '
                             order by  invests.investcatid asc ,   invests.name asc  ';
        $offerInvs = R::getAll($sql);

        $i = 0;
        foreach ($offerInvs as $offerInv) {
            $offerInvs[$i]['orgDiscount'] = $this->listDiscountObj($listId, $offerInv['id'], 4, $offerId);
//            print_r($offerInvs[$i]['orgDiscount']);
            $i++;
        }
        $this->smarty->assign('offerInvs', $offerInvs);
        $this->smarty->assign('listId', $listId);
    }

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

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

        ####################################################################
        $organizations = R::getAll('select id , organization_name from organizations where deleted = 0');
        $this->smarty->assign('organizations', $organizations);

        $this->smarty->assign('today', date('Y/m/d'));

        #####################################################################
        //call other class with other content
        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
        $this->smarty->display('organizations/org_report.html');
        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

    public function orgSearch() {

        autho::checkautho($this->hosturl);

        $fdate = filter_input(INPUT_POST, 'fdate');
        $todate = filter_input(INPUT_POST, 'todate');
        $orgId = filter_input(INPUT_POST, "orgId");
        $print = filter_input(INPUT_POST, "print");

        ####################################################################
        $sql = 'SELECT DISTINCT
                    visits.id AS visitId,
                    detections.title AS type,
                    visits.patientid,
                    visits.for_husband,
                    patients.wifename,
                    patients.husdandname,
                    patients.statusno,
                    visits.visitdate,
                    vu.name AS visitDoctor,
                  visits.contract_price ,  visits.patient_hold ,  visits.organ_discount_type , 0 as restPrice

                    FROM
                        visits
                        JOIN patients ON visits.patientid = patients.id
                        LEFT JOIN detections ON visits.detectionid = detections.id
                        LEFT JOIN awusers vu ON vu.user_id = visits.for_doctor
                    WHERE
                        visits.deleted = 0    and visits.detectionid not in ( 999 , 9999 ) and    visits.organization_id = ' . $orgId . '      
                        AND visitdate >= "' . $fdate . '" AND visitdate <= "' . $todate . '"  ';

        $allData = R::getAll($sql);
        $this->common($allData);
        #####################################################################
        if ($print == 1) {
            $this->smarty->display('organizations/org_print.html');
        } else {
            $this->smarty->display('organizations/org_search.html');
        }
    }

    private function common($allData) {
        $contract_price = 0;
        $patient_hold = 0;
        $restPrice = 0;

        $new_allData = array();
        $i = 0;

        foreach ($allData as $data) {

            if ($data['organ_discount_type'] == 1) {
                $data['patient_hold_price'] = ($data['contract_price'] * $data['patient_hold']) / 100;
                $data['patient_hold'] = $data['patient_hold'] . ' % ';
            } else {
                $data['patient_hold_price'] = $data['patient_hold'];
            }

            $data['restPrice'] = $data['contract_price'] - $data['patient_hold_price'];

            $contract_price += $data['contract_price'];
            $patient_hold += $data['patient_hold_price'];
            $restPrice += $data['restPrice'];

            $new_allData[] = $data;
            $i++;
        }

        $this->smarty->assign('contract_price', $contract_price);
        $this->smarty->assign('patient_hold', $patient_hold);
        $this->smarty->assign('restPrice', $restPrice);
        $this->smarty->assign('allData', $new_allData);
    }

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

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

        $listId = filter_input(INPUT_GET, 'listId');

        if ($listId > 0) {
            $sIndexColumn = "id";
            $sTable = "detections";

            $searchEx = ' and organization_discount.detect_cat = 1 and  organization_discount.list_id  = ' . $listId . '    ';

            $aColumns = array('detections.id', 'detections.title', 'detections.detectionval'
                , 'organization_discount.contract_price', 'organization_discount.patient_hold', 'organization_discount.discount_type');

            $sJoin .= '  left  join organization_discount                          on  detections.id   =   organization_discount.detect_id  ' . $searchEx;

            /*
             * 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 = "WHERE (";

            if (isset($_GET['sSearch']) && $_GET['sSearch'] != "") {

                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 .= " AND detections.del != 1    ";
            } else {
                $sWhere .= " detections.del != 1    ";
            }


            $sWhere .= ' )';

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

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

            foreach ($rResult as $aRow) {
                $row = array();
                $row[] = $aRow['title'];
                $row[] = $aRow['detectionval'];
                $row[] = ' <input type="text" class="form-control updateOrgDis"  data-listId="' . $listId . '"
                                                               data-cat="1"    data-detectId="' . $aRow['id'] . '" data-colName="contract_price" 
                                                               value="' . $aRow['contract_price'] . '">';

                $row[] = ' <input type="text" class="form-control updateOrgDis"  data-listId="' . $listId . '"
                                                               data-cat="1"    data-detectId="' . $aRow['id'] . '" data-colName="patient_hold" 
                                                               value="' . $aRow['patient_hold'] . '">';

                $rowValue = '     <div class="md-radio-inline">
                                                            <div class="md-radio">
                                                                <input type="radio" data-colName="discount_type"  name="discount_type1_' . $aRow['id'] . '" 
                                                                         data-listId="' . $listId . '"  value="1"
                                                                       class="form-control updateOrgDis"  id="discount_typeDetects1_' . $aRow['id'] . '"
                                                                       data-detectId="' . $aRow['id'] . '"      data-cat="1"     ';
                if ($aRow['discount_type'] == 1) {
                    $rowValue .= '  checked    ';
                }


                $rowValue .= '  > <label for="discount_typeDetects1_' . $aRow['id'] . '">
                                                                    <span></span>
                                                                    <span class="check"></span>
                                                                    <span class="box"></span>
                                                                    نسبة %          </label>
                                                            </div>

                                                            <div class="md-radio">
                                                                <input type="radio" data-colName="discount_type"  name="discount_type1_' . $aRow['id'] . '" 
                                                                       data-listId="' . $listId . '"   value="0"
                                                                       class="form-control updateOrgDis"  id="discount_typeDetects0_' . $aRow['id'] . '"
                                                                       data-detectId="' . $aRow['id'] . '"     data-cat="1"   ';

                if ($aRow['discount_type'] != 1) {
                    $rowValue .= '  checked     ';
                }

                $rowValue .= '    >  <label for="discount_typeDetects0_' . $aRow['id'] . '">
                                                                    <span></span>
                                                                    <span class="check"></span>
                                                                    <span class="box"></span>
                                                                    قيمة         </label>
                                                            </div>
                                                        </div>';

                $row[] = $rowValue;

                $output['aaData'][] = $row;
            }
        } else {
            $output = array(
                //"sEcho" => intval($_GET['sEcho']),
                "iTotalRecords" => 0,
                "iTotalDisplayRecords" => 0,
                "aaData" => array()
            );
        }
        //print_r($output);
        echo json_encode($output);
    }

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

        $listId = filter_input(INPUT_GET, 'listId');

        if ($listId > 0) {


            $sIndexColumn = "id";
            $sTable = "invests";

            $searchEx = ' and organization_discount.detect_cat = 2 and  organization_discount.list_id  = ' . $listId . '    ';

            $aColumns = array('invests.id', 'invests.name', 'invests.price'
                , 'organization_discount.contract_price', 'organization_discount.patient_hold', 'organization_discount.discount_type');

            $sJoin .= '   join investcats                          on  investcats.id   =   invests.investcatid and  investcats.deleted != 1 AND invests.deleted != 1  ';
            $sJoin .= '  left  join organization_discount                          on  invests.id   =   organization_discount.detect_id  ' . $searchEx;

            /*
             * 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 = "WHERE (";

            if (isset($_GET['sSearch']) && $_GET['sSearch'] != "") {

                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 .= " AND invests.deleted != 1    ";
            } else {
                $sWhere .= " invests.deleted != 1    ";
            }


            $sWhere .= ' )';

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

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

            foreach ($rResult as $aRow) {
                $row = array();
                $row[] = $aRow['name'];
                $row[] = $aRow['price'];
                $row[] = ' <input type="text" class="form-control updateOrgDis"  data-listId="' . $listId . '"
                                                               data-cat="2"    data-detectId="' . $aRow['id'] . '" data-colName="contract_price" 
                                                               value="' . $aRow['contract_price'] . '">';

                $row[] = ' <input type="text" class="form-control updateOrgDis"  data-listId="' . $listId . '"
                                                               data-cat="2"    data-detectId="' . $aRow['id'] . '" data-colName="patient_hold" 
                                                               value="' . $aRow['patient_hold'] . '">';

                $rowValue = '     <div class="md-radio-inline">
                                                            <div class="md-radio">
                                                                <input type="radio" data-colName="discount_type"  name="discount_type2_' . $aRow['id'] . '" 
                                                                         data-listId="' . $listId . '"  value="1"
                                                                       class="form-control updateOrgDis"  id="discount_typeInvests1_' . $aRow['id'] . '"
                                                                       data-detectId="' . $aRow['id'] . '"  data-cat="2"    ';
                if ($aRow['discount_type'] == 1) {
                    $rowValue .= '  checked   ';
                }


                $rowValue .= '  >  <label for="discount_typeInvests1_' . $aRow['id'] . '">
                                                                    <span></span>
                                                                    <span class="check"></span>
                                                                    <span class="box"></span>
                                                                    نسبة %          </label>
                                                            </div>

                                                            <div class="md-radio">
                                                                <input type="radio" data-colName="discount_type"  name="discount_type2_' . $aRow['id'] . '" 
                                                                       data-listId="' . $listId . '"   value="0"
                                                                       class="form-control updateOrgDis"  id="discount_typeInvests0_' . $aRow['id'] . '"
                                                                       data-detectId="' . $aRow['id'] . '"  data-cat="2"   ';

                if ($aRow['discount_type'] != 1) {
                    $rowValue .= '  checked   ';
                }

                $rowValue .= '     >  <label for="discount_typeInvests0_' . $aRow['id'] . '">
                                                                    <span></span>
                                                                    <span class="check"></span>
                                                                    <span class="box"></span>
                                                                    قيمة         </label>
                                                            </div>
                                                        </div>';

                $row[] = $rowValue;

                $output['aaData'][] = $row;
            }
        } else {
            $output = array(
                //"sEcho" => intval($_GET['sEcho']),
                "iTotalRecords" => 0,
                "iTotalDisplayRecords" => 0,
                "aaData" => array()
            );
        }
        //print_r($output);
        echo json_encode($output);
    }

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

        $listId = filter_input(INPUT_GET, 'listId');

        if ($listId > 0) {


            $sIndexColumn = "id";
            $sTable = "rays";

            $searchEx = ' and organization_discount.detect_cat = 3 and  organization_discount.list_id  = ' . $listId . '    ';

            $aColumns = array('rays.id', 'rays.name', 'rays.price'
                , 'organization_discount.contract_price', 'organization_discount.patient_hold', 'organization_discount.discount_type');

            $sJoin .= '   join rayscats                          on  rayscats.id   =   rays.rayscatid and  rayscats.deleted != 1  ';
            $sJoin .= '  left  join organization_discount                          on  rays.id   =   organization_discount.detect_id  ' . $searchEx;

            /*
             * 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 = "WHERE (";

            if (isset($_GET['sSearch']) && $_GET['sSearch'] != "") {

                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 .= " AND rays.deleted != 1    ";
            } else {
                $sWhere .= " rays.deleted != 1    ";
            }


            $sWhere .= ' )';

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

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

            foreach ($rResult as $aRow) {
                $row = array();
                $row[] = $aRow['name'];
                $row[] = $aRow['price'];
                $row[] = ' <input type="text" class="form-control updateOrgDis"  data-listId="' . $listId . '"
                                                               data-cat="3"    data-detectId="' . $aRow['id'] . '" data-colName="contract_price" 
                                                               value="' . $aRow['contract_price'] . '">';

                $row[] = ' <input type="text" class="form-control updateOrgDis"  data-listId="' . $listId . '"
                                                               data-cat="3"    data-detectId="' . $aRow['id'] . '" data-colName="patient_hold" 
                                                               value="' . $aRow['patient_hold'] . '">';

                $rowValue = '     <div class="md-radio-inline">
                                                            <div class="md-radio">
                                                                <input type="radio" data-colName="discount_type"  name="discount_type3_' . $aRow['id'] . '" 
                                                                         data-listId="' . $listId . '"  value="1"
                                                                       class="form-control updateOrgDis"  id="discount_typeRays1_' . $aRow['id'] . '"
                                                                       data-detectId="' . $aRow['id'] . '" data-cat="3"    ';
                if ($aRow['discount_type'] == 1) {
                    $rowValue .= '  checked    ';
                }


                $rowValue .= ' >  <label for="discount_typeRays1_' . $aRow['id'] . '">
                                                                    <span></span>
                                                                    <span class="check"></span>
                                                                    <span class="box"></span>
                                                                    نسبة %          </label>
                                                            </div>

                                                            <div class="md-radio">
                                                                <input type="radio" data-colName="discount_type"  name="discount_type3_' . $aRow['id'] . '" 
                                                                       data-listId="' . $listId . '"   value="0"
                                                                       class="form-control updateOrgDis"  id="discount_typeRays0_' . $aRow['id'] . '"
                                                                       data-detectId="' . $aRow['id'] . '"  data-cat="3"  ';

                if ($aRow['discount_type'] != 1) {
                    $rowValue .= '  checked   ';
                }

                $rowValue .= '      >  <label for="discount_typeRays0_' . $aRow['id'] . '">
                                                                    <span></span>
                                                                    <span class="check"></span>
                                                                    <span class="box"></span>
                                                                    قيمة         </label>
                                                            </div>
                                                        </div>';

                $row[] = $rowValue;

                $output['aaData'][] = $row;
            }
        } else {
            $output = array(
                //"sEcho" => intval($_GET['sEcho']),
                "iTotalRecords" => 0,
                "iTotalDisplayRecords" => 0,
                "aaData" => array()
            );
        }
        //print_r($output);
        echo json_encode($output);
    }

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

        $listId = filter_input(INPUT_GET, 'listId');

        if ($listId > 0) {


            $sIndexColumn = "id";
            $sTable = "invoffer";

            $aColumns = array('id', 'name');

            /*
             * 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 = "WHERE (";

            if (isset($_GET['sSearch']) && $_GET['sSearch'] != "") {

                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 .= " AND del = 0  and active = 1    ";
            } else {
                $sWhere .= " del = 0  and active = 1   ";
            }


            $sWhere .= ' )';

            $sQuery = "SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $aColumns)) . " FROM  $sTable   $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);

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

            foreach ($rResult as $aRow) {
                $row = array();
                $row[] = $aRow['name'];

                $offerdetail = R::getRow(' select  sum(price) as totalPrice   from    invofferdetails
                where del = 0 and invoffer_id = ? ', [$aRow['id']]);
                $row[] = $offerdetail['totalPrice'];

                $orgDiscount = $this->offerListPrice($aRow['id'], $listId);

                $row[] = ' <input type = "text" class = "form-control updateOrgDis" data-listId = "' . $listId . '"
                data-cat = "4" data-detectId = "' . $aRow['id'] . '"
                data-colName = "contract_price"
                value = "' . $orgDiscount['contract_price'] . '">';

                $row[] = ' <input type = "text" class = "form-control updateOrgDis" data-listId = "' . $listId . '"
                data-cat = "4" data-detectId = "' . $aRow['id'] . '"
                data-colName = "patient_hold"
                value = "' . $orgDiscount['patient_hold'] . '">';

                $rowValue = ' <div class = "md-radio-inline">
                <div class = "md-radio">
                <input type = "radio" data-colName = "discount_type" name = "discount_type2_' . $aRow['id'] . '"
                data-listId = "' . $listId . '" value = "1"
                class = "form-control updateOrgDis"
                id = "discount_typeOffers1_' . $aRow['id'] . '"
                data-detectId = "' . $aRow['id'] . '" data-cat = "4" ';
                if ($orgDiscount['discount_type'] == 1) {
                    $rowValue .= ' checked ';
                }


                $rowValue .= ' > <label for = "discount_typeOffers1_' . $aRow['id'] . '">
                <span></span>
                <span class = "check"></span>
                <span class = "box"></span>
                نسبة % </label>
                </div>

                <div class = "md-radio">
                <input type = "radio" data-colName = "discount_type" name = "discount_type2_' . $aRow['id'] . '"
                data-listId = "' . $listId . '" value = "0"
                class = "form-control updateOrgDis" id = "discount_typeOffers0_' . $aRow['id'] . '"
                data-detectId = "' . $aRow['id'] . '" data-cat = "4" ';

                if ($orgDiscount['discount_type'] != 1) {
                    $rowValue .= ' checked ';
                }

                $rowValue .= ' > <label for = "discount_typeOffers0_' . $aRow['id'] . '">
                <span></span>
                <span class = "check"></span>
                <span class = "box"></span>
                قيمة </label>
                </div>
                </div>';

                $row[] = $rowValue;

                $output['aaData'][] = $row;
            }
        } else {
            $output = array(
                //"sEcho" => intval($_GET['sEcho']),
                "iTotalRecords" => 0,
                "iTotalDisplayRecords" => 0,
                "aaData" => array()
            );
        }
        //print_r($output);
        echo json_encode($output);
    }

    public function updateOrgDis() {

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

        $listId = filter_input(INPUT_POST, "listId");
        $catId = filter_input(INPUT_POST, "catId");
        $detectId = filter_input(INPUT_POST, "detectId");
        $colName = filter_input(INPUT_POST, "colName");
        $value = filter_input(INPUT_POST, "value");
        $offerId = (filter_input(INPUT_POST, 'offerId') > 0) ? filter_input(INPUT_POST, 'offerId') : 0;

        try {
            if ($listId > 0 && $catId > 0 && $detectId > 0) {
                if ($catId != 4) {
                    $org_discount = $this->listDiscountObj($listId, $detectId, $catId, $offerId);
                    $org_discount->$colName = $value;
                    R::store($org_discount);
                } else {
                    $this->updateOfferPriceList($listId, $detectId, $colName, $value);
                }

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

    function updateOfferPriceList($listId, $offerId, $colName, $value) {

        $offerInvs = R::findall('invofferdetails', 'del = ? and invoffer_id = ?  ', [0, $offerId]);
        $totalPrice = R::getRow(' select  sum(price) as totalPrice   from    invofferdetails
                where del = 0 and invoffer_id = ? ', [$offerId]);

        $i = count($offerInvs);
        $last = 0;
        foreach ($offerInvs as $offerInv) {
            $orgDiscount = $this->listDiscountObj($listId, $offerInv->id, 4, $offerId);
            switch ($colName) {
                case 'discount_type':
                    $orgDiscount->$colName = $value;
                    break;

                default:
                    if ($i == 1) {
                        $price = $value - $last;
                    } else {
                        $invPercent = $offerInv->price / $totalPrice['totalPrice'];
                        $price = round($invPercent * $value);
                        $last += $price;
                    }
                    $orgDiscount->$colName = $price;
                    break;
            }
            R::store($orgDiscount);
            $i--;
        }
    }

    ######################## price lists ################################

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

        ####################################################################
        $organizations = R::getAll('select id, organization_name, parent_id, list_id
        from organizations where deleted = 0');
        $this->smarty->assign('organizations', $organizations);

        $orgParents = R::getAll('select id, name from organizations_parents where deleted = 0');
        $this->smarty->assign('orgParents', $orgParents);

        $price_lists = R::getAll('select id, name from price_lists where deleted = 0');
        $this->smarty->assign('price_lists', $price_lists);

        #####################################################################
        //call other class with other content
        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
        $this->smarty->display('organizations/show.html');
        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

    public function addNewOrg() {

//check login
        autho::checkautho($this->hosturl);
        try {
            $ogj = R::xdispense('organizations');
            $ogj->deleted = 0;
            $ogj->id = R::store($ogj);

            $this->smarty->assign('data', $ogj);

            $orgParents = R::getAll('select id, name from organizations_parents where deleted = 0');
            $this->smarty->assign('orgParents', $orgParents);

            $price_lists = R::getAll('select id, name from price_lists where deleted = 0');
            $this->smarty->assign('price_lists', $price_lists);

            $this->smarty->display('organizations/neworg.html');
        } catch (Exception $ex) {
            echo -1;
        }
    }

    ######################## REFER DRS  ################################

    function storeOldRecord($tableName, $oldRecord) {
        $visitEdit = R::xdispense($tableName . '_updates');
        $colums = R::getAll('DESCRIBE ' . $tableName);

        foreach ($colums as $col) {
            $colName = $col['Field'];
            if ($colName != id) {
                $visitEdit->$colName = $oldRecord->$colName;
            }
        }

        $visitEdit->userid_edit = $_SESSION['user_id'];
        $visitEdit->date_edit = date('Y-m-d H:i:s'); //$this->today;
        $visitEdit->original_id = $oldRecord->id;
        R::store($visitEdit);
    }

    public function del() {
        autho::checkautho($this->hosturl);

        $id = filter_input(INPUT_POST, 'id');
        $tableName = filter_input(INPUT_POST, 'tableName');
        try {

            if ($tableName == "refer") {
                $oldRecord = R::load($tableName, $id);
                $this->storeOldRecord($tableName, $oldRecord);
            }
            R::exec('update ' . $tableName . ' set deleted = 1 WHERE id = ' . $id);
            echo 1;
        } catch (Exception $e) {
            echo $e;
        }
    }

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

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

        $refer = R::getAll('select id, name, refer_id
        from refer
        where deleted = 0 order by name');
        $this->smarty->assign('refer', $refer);

        #####################################################################
        //call other class with other content
        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
        $this->smarty->display('organizations/refer_show.html');
        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

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

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

        $price_lists = R::getAll('select id, name from price_lists where deleted = 0');
        $this->smarty->assign('price_lists', $price_lists);

        $sales_persons = R::getAll('select id, name from sales_persons where deleted = 0');
        $this->smarty->assign('sales_persons', $sales_persons);

        #####################################################################
        //call other class with other content
        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
        $this->smarty->display('organizations/refer_add.html');
        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

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

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

        $refer_id = filter_input(INPUT_POST, 'refer_id');
        $name = filter_input(INPUT_POST, 'name');
        $phone = filter_input(INPUT_POST, 'phone');
        $location = filter_input(INPUT_POST, 'location');
        $cash_transfer = filter_input(INPUT_POST, 'cash_transfer');
        $email = filter_input(INPUT_POST, 'email');
        $specialist_group = filter_input(INPUT_POST, 'specialist_group');
        $price_list = filter_input(INPUT_POST, 'price_list');
        $sales_persons = filter_input(INPUT_POST, 'sales_persons');

        try {
            $refer = R::xdispense('refer');

            $refer->refer_id = $refer_id;
            $refer->name = $name;
            $refer->phone = $phone;
            $refer->location = $location;
            $refer->cash_transfer = $cash_transfer;
            $refer->email = $email;
            $refer->specialist_group = $specialist_group;
            $refer->price_list = $price_list;
            $refer->sales_persons = $sales_persons;
            $refer->user_id = $_SESSION['user_id'];
            $refer->create_date = date('Y-m-d H:i:s');
            $refer->deleted = 0;
            R::store($refer);
            header('Location:' . $this->hosturl . '/controllers/' . $this->controllname . '.php?ac = refer_show');
        } catch (Exception $e) {
            header('Location:' . $this->hosturl . '/error/notadd/' . $this->controllname);
        }
    }

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

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

        $id = filter_input(INPUT_GET, 'id');

        $refer = R::load('refer', $id);
        $this->smarty->assign('refer', $refer);

        $price_lists = R::getAll('select id, name from price_lists where deleted = 0');
        $this->smarty->assign('price_lists', $price_lists);

        $sales_persons = R::getAll('select id, name from sales_persons where deleted = 0');
        $this->smarty->assign('sales_persons', $sales_persons);

        #####################################################################
        //call other class with other content
        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
        $this->smarty->display('organizations/refer_edit.html');
        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

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

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

        $id = filter_input(INPUT_POST, 'id');
        $refer_id = filter_input(INPUT_POST, 'refer_id');
        $name = filter_input(INPUT_POST, 'name');
        $phone = filter_input(INPUT_POST, 'phone');
        $location = filter_input(INPUT_POST, 'location');
        $cash_transfer = filter_input(INPUT_POST, 'cash_transfer');
        $email = filter_input(INPUT_POST, 'email');
        $specialist_group = filter_input(INPUT_POST, 'specialist_group');
        $price_list = filter_input(INPUT_POST, 'price_list');
        $sales_persons = filter_input(INPUT_POST, 'sales_persons');

        try {
            $oldRecord = R::load('refer', $id);
            $refer = R::load('refer', $id);
            $refer->refer_id = $refer_id;
            $refer->name = $name;
            $refer->phone = $phone;
            $refer->location = $location;
            $refer->cash_transfer = $cash_transfer;
            $refer->email = $email;
            $refer->specialist_group = $specialist_group;
            $refer->price_list = $price_list;
            $refer->sales_persons = $sales_persons;
            R::store($refer);

            $this->storeOldRecord('refer', $oldRecord);
            header('Location:' . $this->hosturl . '/controllers/' . $this->controllname . '.php?ac = refer_show');
        } catch (Exception $e) {
            header('Location:' . $this->hosturl . '/error/notadd/' . $this->controllname);
        }
    }

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

        $id = filter_input(INPUT_POST, 'id');
        $refer = R::load('refer', $id);
        $this->smarty->assign('imgName', $refer->refer_id);

        $programesetting = R::findOne('programesetting');
        $this->smarty->assign('programesetting', $programesetting);

        if ($refer->refer_id != '') {
            $this->createbarcode($refer->refer_id);
            $this->smarty->assign('name', $refer->name);
            $this->smarty->display('organizations/bqprint.html');
        } else {
            echo -1;
        }
    }

    public function createbarcode($imgName) {
        if (file_exists('../../upload/referbarcodes/' . $imgName . '.png')) {
            
        } else {
            $url = $this->hosturl . '/../_library/barcode/image.php?code=' . $imgName . '&type=C128A';
            $ch = curl_init();
            curl_setopt($ch, CURLOPT_URL, $url);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
            $barcode = curl_exec($ch);

            if ($barcode === false) {
                $curl_result = curl_error($ch);
            } else {
                // header( 'Content-Type: application/json' );
                ## save the file to disk
                file_put_contents('../../upload/referbarcodes/' . $imgName . '.png', $barcode);
            }
            curl_close($ch);
        }
    }

    ######################## organizations ################################

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

        ####################################################################
        $organizations = R::getAll('select id, organization_name, org_id
        from organizations where deleted = 0');
        $this->smarty->assign('organizations', $organizations);

        #####################################################################
        //call other class with other content
        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
        $this->smarty->display('organizations/org_show.html');
        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

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

        ####################################################################
        $orgParents = R::getAll('select id, name from organizations_parents where deleted = 0');
        $this->smarty->assign('orgParents', $orgParents);

        $price_lists = R::getAll('select id, name from price_lists where deleted = 0');
        $this->smarty->assign('price_lists', $price_lists);

        $sales_persons = R::getAll('select id, name from sales_persons where deleted = 0');
        $this->smarty->assign('sales_persons', $sales_persons);

        #####################################################################
        //call other class with other content
        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
        $this->smarty->display('organizations/org_add.html');
        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

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

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

        $org_id = filter_input(INPUT_POST, 'org_id');
        $name = filter_input(INPUT_POST, 'name');
        $parent_id = filter_input(INPUT_POST, 'parent_id');
        $org_type = filter_input(INPUT_POST, 'org_type');
        $location = filter_input(INPUT_POST, 'location');
        $phone = filter_input(INPUT_POST, 'phone');
        $contact_person = filter_input(INPUT_POST, 'contact_person');
        $price_list = filter_input(INPUT_POST, 'price_list');
        $sales_persons = filter_input(INPUT_POST, 'sales_persons');
        $contract_start = filter_input(INPUT_POST, 'contract_start');
        $email = filter_input(INPUT_POST, 'email');
        $vat_no = filter_input(INPUT_POST, 'vat_no');
        $credit_limit = filter_input(INPUT_POST, 'credit_limit');

        $username = filter_input(INPUT_POST, 'username');
        $password = filter_input(INPUT_POST, 'password');
        $user_name = trim($username);
        $hash_cost_factor = (defined('HASH_COST_FACTOR') ? HASH_COST_FACTOR : null);
        $user_password_hash = password_hash($password, PASSWORD_DEFAULT, array('cost' => $hash_cost_factor));

        try {
            $org = R::xdispense('organizations');

            $org->org_id = $org_id;
            $org->organization_name = $name;
            $org->parent_id = $parent_id;
            $org->org_type = $org_type;
            $org->location = $location;
            $org->phone = $phone;
            $org->contact_person = $contact_person;
            $org->price_list = $price_list;
            $org->list_id = $price_list;
            $org->sales_persons = $sales_persons;
            $org->contract_start = $contract_start;
            $org->email = $email;
            $org->vat_no = $vat_no;
            $org->credit_limit = $credit_limit;
            $org->user_id = $_SESSION['user_id'];
            $org->create_date = date('Y-m-d H:i:s');
            $org->deleted = 0;

            $org->user_name = $user_name;
            $org->user_password_hash = $user_password_hash;
            R::store($org);
            header('Location:' . $this->hosturl . '/controllers/' . $this->controllname . '.php?ac=organizations');
        } catch (Exception $e) {
            header('Location:' . $this->hosturl . '/error/notadd/' . $this->controllname);
        }
    }

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

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

        $id = filter_input(INPUT_GET, 'id');

        $org = R::load('organizations', $id);
        $this->smarty->assign('org', $org);

        $orgParents = R::getAll('select id, name from organizations_parents where deleted = 0');
        $this->smarty->assign('orgParents', $orgParents);

        $price_lists = R::getAll('select id, name from price_lists where deleted = 0');
        $this->smarty->assign('price_lists', $price_lists);

        $sales_persons = R::getAll('select id, name from sales_persons where deleted = 0');
        $this->smarty->assign('sales_persons', $sales_persons);

        #####################################################################
        //call other class with other content
        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
        $this->smarty->display('organizations/org_edit.html');
        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

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

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

        $id = filter_input(INPUT_POST, 'id');
        $org_id = filter_input(INPUT_POST, 'org_id');
        $name = filter_input(INPUT_POST, 'name');
        $parent_id = filter_input(INPUT_POST, 'parent_id');
        $org_type = filter_input(INPUT_POST, 'org_type');
        $location = filter_input(INPUT_POST, 'location');
        $phone = filter_input(INPUT_POST, 'phone');
        $contact_person = filter_input(INPUT_POST, 'contact_person');
        $price_list = filter_input(INPUT_POST, 'price_list');
        $sales_persons = filter_input(INPUT_POST, 'sales_persons');
        $contract_start = filter_input(INPUT_POST, 'contract_start');
        $email = filter_input(INPUT_POST, 'email');
        $vat_no = filter_input(INPUT_POST, 'vat_no');
        $credit_limit = filter_input(INPUT_POST, 'credit_limit');

        $username = filter_input(INPUT_POST, 'username');
        $user_name = trim($username);

        try {
            $oldRecord = R::load('organizations', $id);
            $org = R::load('organizations', $id);
            $org->org_id = $org_id;
            $org->organization_name = $name;
            $org->parent_id = $parent_id;
            $org->org_type = $org_type;
            $org->location = $location;
            $org->phone = $phone;
            $org->contact_person = $contact_person;
            $org->price_list = $price_list;
            $org->list_id = $price_list;
            $org->sales_persons = $sales_persons;
            $org->contract_start = $contract_start;
            $org->email = $email;
            $org->vat_no = $vat_no;
            $org->credit_limit = $credit_limit;
            $org->user_name = $user_name;

            $epassword = filter_input(INPUT_POST, 'epassword');
            if (empty($epassword)) {
                
            } else {
                $hash_cost_factor = (defined('HASH_COST_FACTOR') ? HASH_COST_FACTOR : null);
                $user_password_hash = password_hash($epassword, PASSWORD_DEFAULT, array('cost' => $hash_cost_factor));
                $org->user_password_hash = $user_password_hash;
            }


            R::store($org);

            $this->storeOldRecord('refer', $oldRecord);
            header('Location:' . $this->hosturl . '/controllers/' . $this->controllname . '.php?ac=organizations');
        } catch (Exception $e) {
            header('Location:' . $this->hosturl . '/error/notadd/' . $this->controllname);
        }
    }

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

        $id = filter_input(INPUT_POST, 'id');
        $org = R::load('organizations', $id);
        $this->smarty->assign('imgName', $org->org_id);

        $programesetting = R::findOne('programesetting');
        $this->smarty->assign('programesetting', $programesetting);

        if ($org->org_id != '') {
            $this->createbarcode($org->org_id);
            $this->smarty->assign('name', $org->organization_name);
            $this->smarty->display('organizations/bqprint.html');
        } else {
            echo -1;
        }
    }

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

    public function prepareTestExcel() {
        require_once "Classes/PHPExcel/IOFactory.php";
        $invests = R::findAll('invests', ' invests.deleted != 1 and invests.name != "" order by invests.id asc');
        $filename = 'all_tests.xlsx';
        $table = $this->createhtmltable($invests);
        $this->createSingleExcel($table, $filename, $invests);
    }

    public function preparePackExcel() {
        require_once "Classes/PHPExcel/IOFactory.php";
        $packs = R::findAll('invoffer', 'del = ? and active = ? order by id asc ', [0, 1]);
        $filename = 'all_packs.xlsx';
        $table = $this->createhtmltable($packs);
        $this->createSingleExcel($table, $filename, $packs);
    }

    function createhtmltable($invests) {
        $table = '<table>';
        $table .= '<tr> ';
        $table .= '<td> ID </td><td> Name</td><td> Contract Price </td> <td> Hold </td> <td> Type (0 for value, 1 for % ) </td>';
        $table .= '</tr> ';
        foreach ($invests as $invest) {
            $table .= '<tr> ';
            $table .= '<td>' . $invest->id . ' </td><td>' . $invest->name . ' </td><td></td><td>0</td><td>0 </td>';
            $table .= '</tr> ';
        }
        $table .= '</table>';
        return $table;
    }

    function createSingleExcel($table, $filename, $datas) {
        ################## Save excel sheet  ##########################################
        ## Put the html into a temporary file
        $tmpfile = time() . '.html';
        file_put_contents($tmpfile, $table);

        ## Read the contents of the file into PHPExcel Reader class
        $reader = new PHPExcel_Reader_HTML;
        $content = $reader->load($tmpfile);
        $content = $this->contentAfter($content, $datas);
###############################################################################################################
        //prepare download
        // Pass to writer and output as needed

        $objWriter = PHPExcel_IOFactory::createWriter($content, 'Excel2007');

        $objWriter->save('../../upload/' . $filename);
        //$objWriter->save(str_replace($filename,'../excel_backups/xx.xls',$filename));   ## this replace if file with the same name exist
        // Delete temporary file
        unlink($tmpfile);
    }

    function contentAfter($content, $datas) {
        $i = 3;
        foreach ($datas as $data) {
            $content->getActiveSheet()->setCellValue('B' . $i, $data->name);
            $i++;
        }
        return $content;
    }

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

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

        $listId = filter_input(INPUT_POST, 'listId');
        $listObj = R::getRow('select id, name from price_lists where id = ? ', [$listId]);
        $this->smarty->assign('listObj', $listObj);

        if (count($listObj) > 0) {
            $this->ListInvests($listId);
            $this->ListOffers($listId);
            $this->smarty->display('organizations/list_print.html');
        } else {
            echo -1;
        }
    }

    public function ListInvests($listId) {
        $sql = ' select invests.id, invests.name
        from invests
        join investcats on investcats.id = invests.investcatid
        where invests.deleted != 1 and invests.name != "" and investcats.deleted != 1
        order by invests.name asc ';
        $invests = R::getAll($sql);

        $i = 0;
        foreach ($invests as $invest) {
            $orgDiscount = $this->listDiscountObj($listId, $invest['id'], 2);
            if ($orgDiscount->contract_price > 0) {
                $invests[$i]['contract_price'] = $orgDiscount->contract_price;
                $invests[$i]['patient_hold'] = $orgDiscount->patient_hold;
                $invests[$i]['discount_type'] = $orgDiscount->discount_type;
            } else {
                unset($invests[$i]);
            }

            $i++;
        }
        $this->smarty->assign('invests', $invests);
        $this->smarty->assign('investsCount', count($invests));
    }

    public function ListOffers($listId) {
        $offersAll = R::findall('invoffer', 'del = ? and active = ? ', [0, 1]);
        $offers = array();
        foreach ($offersAll as $offer) {
            $orgDiscount = $this->offerListPrice($offer->id, $listId);
            if ($orgDiscount['contract_price'] > 0) {
                $offer->contract_price = $orgDiscount['contract_price'];
                $offer->patient_hold = $orgDiscount['patient_hold'];
                $offer->discount_type = $orgDiscount['discount_type'];

                $offers[] = $offer;
            }
        }
        $this->smarty->assign('offers', $offers);
        $this->smarty->assign('offersCount', count($offers));
    }

    function offerListPrice($offerId, $listId) {
        $sql = ' select invofferdetails.id
        from invofferdetails
        join invoffer on invoffer.id = invofferdetails.invoffer_id
        join invests on invests.id = invofferdetails.inv_id
        join investcats on investcats.id = invests.investcatid
        where invests.deleted != 1 and invests.name != ""
        and invofferdetails.del != 1 and invofferdetails.invoffer_id = ' . $offerId . ' ';
        $offerInvs = R::getAll($sql);

        $result['contract_price'] = 0;
        $result['patient_hold'] = 0;

        foreach ($offerInvs as $offerInv) {
            $orgDiscount = $this->listDiscountObj($listId, $offerInv['id'], 4, $offerId);
            $result['contract_price'] += $orgDiscount['contract_price'];
            $result['patient_hold'] += $orgDiscount['patient_hold'];
            $result['discount_type'] = $orgDiscount['discount_type

        '];
        }
        return $result;
    }

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

    public function checkusername() {

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

        $username = filter_input(INPUT_POST, 'username');
        $id = filter_input(INPUT_POST, 'id');

        $sql = ' SELECT * FROM organizations WHERE user_name = ?  ';
        if ($id > 0) {
            $sql .= ' and id !=  ' . $id;
        }

        $checkusername = R::getRow($sql, [$username]);
        if (count($checkusername) > 0) {
            echo "yes";
        } else {
            echo "no";
        }
    }

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

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

        ####################################################################
        $organizations = R::getAll('select id , organization_name from organizations where deleted = 0');
        $this->smarty->assign('organizations', $organizations);

        $this->smarty->assign('today', date('Y/m/d'));
        $this->smarty->assign('searchType', 'complete');

        #####################################################################
        //call other class with other content
        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
        $this->smarty->display('organizations/org_report.html');
        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

    public function orgFinSearch() {

        autho::checkautho($this->hosturl);

        $fdate = filter_input(INPUT_POST, 'fdate');
        $todate = filter_input(INPUT_POST, 'todate');
        $orgId = filter_input(INPUT_POST, "orgId");
        $print = filter_input(INPUT_POST, "print");

        $org = R::load('organizations', $orgId);
        $this->smarty->assign('orgName', $org->organization_name);
        $this->smarty->assign('fdate', $fdate);
        $this->smarty->assign('todate', $todate);
        ####################################################################
        $sql = 'SELECT DISTINCT   visits.id AS visitId,  visits.organization_id  ,
                    visits.patientid, visits.for_husband,patients.wifename,visits.vat  ,
                    patients.husdandname,  patients.statusno, visits.visitdate,
                  visits.contract_price ,  visits.patient_hold ,  visits.organ_discount_type , 0 as restPrice

                    FROM  visits
                        JOIN patients ON visits.patientid = patients.id
                    WHERE
                        visits.deleted = 0    and visits.detectionid not in ( 999 , 9999 )
                        and    visits.organization_id = ' . $orgId . '
                        AND visitdate >= "' . $fdate . '" AND visitdate <= "' . $todate . '"  ';

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

        $this->commonFin($allData);
        #####################################################################
        if ($print == 1) {
            $this->smarty->display('organizations/org_fin_print.html');
        } else {
            $this->smarty->display('organizations/org_fin_search.html');
        }
    }

    private function commonFin($allData) {
        $totalDue = 0;
        $totalVat = 0;
        $totalCost = 0;
        $new_allData = array();

        foreach ($allData as $data) {
            $visitDetails = $this->visitTests($data['visitId'], $data['vat']);
            $data['testsWithPrice'] = $visitDetails['testsWithPrice'];
            $data['tests'] = $visitDetails['tests'];
            $data['price'] = $visitDetails['price'];
            $data['vat'] = $visitDetails['vat'];
            $data['cost'] = $visitDetails['cost'];
            $data['total'] = $visitDetails['cost'] + $visitDetails['vat'];
            $data['code'] = $this->patientOrgCode($data['patientid'], $data['organization_id']);
            $patArray = $this->preparePatArray($data['visitId']);
            $data['patient_name'] = $patArray['name'];
            $new_allData[] = $data;

            $totalDue = $totalDue + $data['total'];
            $totalVat = $totalVat + $visitDetails['vat'];
            $totalCost = $totalCost + $visitDetails['cost'];
        }
        $this->smarty->assign('allData', $new_allData);
        $this->smarty->assign('totalDue', $totalDue);
        $this->smarty->assign('totalVat', $totalVat);
        $this->smarty->assign('totalCost', $totalCost);
    }

    function patientOrgCode($patientId, $orgId) {
        $orgPatNo = R::findOne('organizations_patient_no', ' deleted = 0 and patient_id = ?
                            and  organization_id = ? ', [$patientId, $orgId]);

        return $orgPatNo->patient_no;
    }

    function visitTests($visitId, $vatPercent = 0) {
        $cost = 0;
        $detailsP = '<ul style="margin: 0 ; padding: 0;     text-align: left;"> ';
        $detailsN = '<ul style="margin: 0 ; padding: 0;     text-align: left;"> ';
        $detailsAll = '<ul style="margin: 0 ; padding: 0;     text-align: left;"> ';
        ######################################

        $offers = R::getAll('SELECT distinct investigationresults.offer_id ,   invoffer.name 
                FROM investigationresults
                JOIN invoffer                               ON invoffer.id = investigationresults.offer_id
                WHERE investigationresults.deleted = 0 AND investigationresults.visitid  = ' . $visitId);
        foreach ($offers as $offer) {
            $price = 0;

            $offerTests = R::getAll('SELECT  contract_price , patient_hold
                FROM investigationresults
                WHERE deleted = 0 and  offer_id = ?  and visitid  =  ? ', [$offer['offer_id'], $visitId]);
            foreach ($offerTests as $test) {
                $price = $price + $test['contract_price'] - $test['patient_hold'];
            }

            $detailsP .= ' <li style="text-align:center ;">' . $price . '  </li>';
            $detailsN .= ' <li style="text-align:center ;">' . $offer['name'] . '  </li>';
//            $detailsAll .= ' <li style="text-align:center ;">' . $offer['name'] . ' ( ' . $price . '  )   </li>';
            $detailsAll .= ' <li">
                                            <span style="float:left ; width: 80%;text-align: left;">' . $offer['name'] . ' </span>
                                           <span style="float:right ; width: 20%;text-align: right;">' . $price . ' </span>
                                   </li>';

            $cost = $cost + $price;
        }

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

        $tests = R::getAll('SELECT invests.name , investigationresults.contract_price
            , investigationresults.patient_hold
                FROM invests
                JOIN investigationresults        ON invests.id = investigationresults.investid
                JOIN visits                               ON visits.id = investigationresults.visitid
                WHERE investigationresults.deleted = 0  and investigationresults.offer_id = 0 
                AND investigationresults.visitid  = ' . $visitId);

        foreach ($tests as $test) {
            $price = $test['contract_price'] - $test['patient_hold'];
            $detailsP .= ' <li style="text-align:center ;">' . $price . '  </li>';
            $detailsN .= ' <li style="text-align:center ;">' . $test['name'] . '  </li>';
            //  $detailsAll .= ' <li style="text-align:center ;">' . $test['name'] . ' ( ' . $price . '  )   </li>';
            $detailsAll .= ' <li>
                                            <span style="float:left ; width: 80%;text-align: left;">' . $test['name'] . ' </span>
                                           <span style="float:right ; width: 20%;text-align: right;">' . $price . ' </span>
                                   </li>';

            $cost = $cost + $price;
        }

        ######################################
        $detailsP .= ' </ul> ';
        $detailsN .= ' </ul> ';
        $detailsAll .= ' </ul> ';

        $visitDetails = array();
        $visitDetails['testsWithPrice'] = $detailsAll;
        $visitDetails['tests'] = $detailsN;
        $visitDetails['cost'] = $cost;
        $visitDetails['price'] = $detailsP;
        $visitDetails['vat'] = ($cost * $vatPercent) / 100;
        return $visitDetails;
    }

    function preparePatArray($visitId) {
        $visit = R::load('visits', $visitId);
        $programesetting = R::findOne('programesetting');

        $patient = R::load('patients', $visit->patientid);

        if ($programesetting->system_lang == 2) {
            $name = $patient->wifename;
            $date = $patient->dateofbirth;
            $phone = $patient->phone;
            if ($patient->gender == 1) {
                $sex = 'M';
                $gender = 1;
            } else {
                $sex = 'F';
                $gender = 0;
            }
        } else {

            if ($visit->for_husband == 1) {
                $date = $patient->husbanddateofbirth;
                $gender = 1;
                $phone = $patient->hus_mobile;

                $name = $patient->husdandname;
                $sex = 'M';
            }
###
            else if ($visit->for_husband == 2) {
                $child = R::load('patients_childs', $visit->child);
                $date = $child->birth_date;
                $phone = $patient->phone;

                if ($child->gender == 1) {
                    $gender = 1;
                    $title = 'الطفل  ';
                    $sex = 'M  ';
                } else {
                    $gender = 0;
                    $title = 'الطفلة  ';
                    $sex = 'F  ';
                }

                $name = $child->child_name;
            }
###
            else {
                $date = $patient->dateofbirth;
                $gender = 0;
                $phone = $patient->phone;
                $name = $patient->wifename;
                $sex = 'F';
            }
        }

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

        if ($interval->y > 0) {
            $ageAll = $interval->y . ' Years';
            $age = $interval->y;
            $ageUnit = 1;
            $ageName = 'Years';
        } elseif ($interval->m > 0) {
            $ageAll = $interval->m . ' Months';
            $age = $interval->m;
            $ageUnit = 2;
            $ageName = 'Months';
        } elseif ($interval->d >= 0) {
            $ageAll = 1 + $interval->d . ' Days';
            $age = 1 + $interval->d;
            $ageUnit = 3;
            $ageName = 'Days';
        }


        $patArray = array();
        $patArray['name'] = $name;
        $patArray['statusno'] = $patient->statusno;
        $patArray['patientId'] = $patient->wifenationalid;
        $patArray['patId'] = $patient->id;
        $patArray['ageAll'] = $ageAll;
        $patArray['sex'] = $sex;
        $patArray['patientid'] = $visit->patientid;
        $patArray['phone'] = $phone;
        $patArray['age'] = $age;
        $patArray['ageUnit'] = $ageUnit;
        $patArray['ageName'] = $ageName;
        $patArray['gender'] = $gender;
        $patArray['visitId'] = $visitId;

        return $patArray;
    }

    #############################################################
// at the end of class call

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