<?php

require_once("../../core/controllers/imp/_imp.php");

class Controllers {

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

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

//here name of controll to known
        $this->controllname = 'receipt';
        $this->shcontrollname = 'Receipt';
        $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] = 'pharmacy'; //controller name or tab name
        $active[1] = 'Receipt'; //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;



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

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

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

        $allMed = R::findAll('phdrugs', 'order by drugname asc');
        $this->smarty->assign('allMed', $allMed);

//calling html files
        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
        $this->smarty->display('reports/drug.html');
        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

    private function drugreportsearch($searchValues) {

        $reportType = $searchValues[0];
        $drugId = $searchValues[1];
        if ($drugId) {
            $storeDrugs = R::findAll('storedrugs', ' deleted = 0 and  drugid = ' . $drugId);
        } else {
            $storeDrugs = R::findAll('storedrugs', ' deleted = 0 ');
        }

        if (count($storeDrugs) > 0) {

            foreach ($storeDrugs as $data) {
                $store = R::load('stores', $data->storeid);
                $storedrugsvalidation = R::findAll('storedrugsvalidation', '  storedrugid = ' . $data->id);
                $data->storedrugsvalidation = $storedrugsvalidation;
                $data->storeName = $store->name;
            }
        }

        $this->smarty->assign('reportType', $reportType);
        $this->smarty->assign('storeDrugs', $storeDrugs);
        $this->smarty->assign('storeDrugscount', count($storeDrugs));
        $this->smarty->display('reports/result.html');
    }

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

        $stores = R::getAll('SELECT id , name FROM stores WHERE deleted  = 0 ');
        $this->smarty->assign('stores', $stores);
        $this->smarty->assign('today', date('Y-m-d'));

//calling html files
        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
        $this->smarty->display('reports/import.html');
        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

    private function importreportsearch($searchValues) {

        $reportType = $searchValues[0];
        $dateFrom = $searchValues[1];
        $dateTo = $searchValues[2];
        $storeId = $searchValues[3];

        if ($storeId == -1 || $storeId == "") {
            $sql = ' select distinct importdrugs.id , phdrugs.drugname , phdrugs.drugtype ,   importbill.invoicenum ,importbill.invoicedate,importdrugs.drugid,stores.name,importdrugs.validto,importdrugs.amount  
                from    importdrugs
                            join importbill      on importbill.id  = importdrugs.billid
                            join stores          on stores.id  = importdrugs.storeid
                            join  phdrugs          on  phdrugs.id  = importdrugs.drugid
                where  importbill.invoicedate between "' . $dateFrom . '"  AND  "' . $dateTo . '" ';
        } else {
            $sql = ' select distinct importdrugs.id , phdrugs.drugname , phdrugs.drugtype, importbill.invoicenum ,importbill.invoicedate,importdrugs.drugid,stores.name,importdrugs.validto,importdrugs.amount  
                from    importdrugs
                            join importbill      on importbill.id  = importdrugs.billid
                            join stores          on stores.id  = importdrugs.storeid
                            join  phdrugs          on  phdrugs.id  = importdrugs.drugid
                where  importbill.invoicedate  between "' . $dateFrom . '"  AND  "' . $dateTo . '"
                and importdrugs.storeid = ' . $storeId . '';
        }

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

        $this->smarty->assign('reportType', $reportType);
        $this->smarty->assign('importdrugs', $importdrugs);
        $this->smarty->assign('importdrugscount', count($importdrugs));
        $this->smarty->display('reports/result.html');
    }

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

        $stores = R::getAll('SELECT id , name FROM stores WHERE deleted  = 0 ');
        $this->smarty->assign('stores', $stores);

//calling html files
        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
        $this->smarty->display('reports/minimum.html');
        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

    private function minimumsearch($searchValues) {
        $reportType = $searchValues[0];
        $storeId = $searchValues[1];
        $storedrugscount = 0;

        if ($storeId == -1 || $storeId == "" || $storeId == -2) {
            $storedrugsDis = R::getAll('SELECT DISTINCT storedrugid  FROM storedrugsvalidation');
            $i = 0;
            foreach ($storedrugsDis as $data) {
                $sql = ' select sum(storedrugsvalidation.currentbalance) as currentsum
                from        storedrugsvalidation
                where  storedrugsvalidation.storedrugid =  ' . $data['storedrugid'];

                $currentsum = R::getRow($sql);

                $storedrugs = R::load('storedrugs', $data['storedrugid']);
                $drug = R::load('phdrugs', $storedrugs->drugid);
                $store = R::load('stores', $storedrugs->storeid);

                $storedrugsDis[$i]['show'] = 0;

                //if ($currentsum['currentsum'] <= $drug->minimumbalance) {
                if ($currentsum['currentsum'] > 0 ) {
                    $storedrugsDis[$i]['show'] = 1;
                    $storedrugsDis[$i]['name'] = $store->name;
                    $storedrugsDis[$i]['drugname'] = $drug->drugname;
                    $storedrugsDis[$i]['drugtype'] = $drug->drugtype;
                    $storedrugsDis[$i]['amount'] = $currentsum['currentsum'];
                    $storedrugscount++;

                    //}
                }

                $i++;
            }
        } else {
            $storedrugsDis = R::getAll('SELECT DISTINCT storedrugid  
                FROM storedrugsvalidation 
                 join storedrugs    on storedrugs.id = storedrugsvalidation.storedrugid
                    where  storedrugs.storeid =  ' . $storeId);
            $i = 0;
            foreach ($storedrugsDis as $data) {
                $sql = ' select sum(storedrugsvalidation.currentbalance) as currentsum
                from        storedrugsvalidation
                where  storedrugsvalidation.storedrugid =  ' . $data['storedrugid'];

                $currentsum = R::getRow($sql);

                $storedrugs = R::load('storedrugs', $data['storedrugid']);
                $drug = R::load('phdrugs', $storedrugs->drugid);
                $store = R::load('stores', $storedrugs->storeid);

                $storedrugsDis[$i]['show'] = 0;

                //if ($currentsum['currentsum'] <= $drug->minimumbalance) {
                if ($currentsum['currentsum'] > 0 ) {
                    $storedrugsDis[$i]['show'] = 1;
                    $storedrugsDis[$i]['name'] = $store->name;
                    $storedrugsDis[$i]['drugname'] = $drug->drugname;
                    $storedrugsDis[$i]['drugtype'] = $drug->drugtype;
                    $storedrugsDis[$i]['amount'] = $currentsum['currentsum'];
                    $storedrugscount++;
                }

                //}

                $i++;
            }
        }

        $this->smarty->assign('reportType', $reportType);
        $this->smarty->assign('storedrugsDis', $storedrugsDis);
        $this->smarty->assign('storedrugscount', $storedrugscount);
        $this->smarty->display('reports/result.html');
    }

    public function searchresult() {
//check login
        autho::checkautho($this->hosturl . "/../core");
//check authoize
// autho::checkauthoize($this->controllname, $this->hosturl . "/../core", $_SESSION['role_id']);
        $searchValues = json_decode(stripslashes(filter_input(INPUT_POST, 'jsonString')));
        $reportType = $searchValues[0];

        switch ($reportType) {
            case 'drug':
                $this->drugreportsearch($searchValues);
                break;

            case 'import':
                $this->importreportsearch($searchValues);
                break;

            case 'external':
                $this->externalexportsearch($searchValues);
                break;
            case 'internal':
                $this->internalexporttsearch($searchValues);
                break;

            case 'minimum':
                $this->minimumsearch($searchValues);
                break;

            case 'validation':
                $this->validationsearch($searchValues);
                break;

            case 'doctor':
                $this->doctorreportsearch($searchValues);
                break;


            default:
                break;
        }
    }

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

        $allMed = R::findAll('phdrugs', 'order by drugname asc');
        $this->smarty->assign('allMed', $allMed);

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


//calling html files
        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
        $this->smarty->display('reports/external.html');
        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

    private function externalexportsearch($searchValues) {
        $reportType = $searchValues[0];
        $dateFrom = $searchValues[1];
        $dateTo = $searchValues[2];
        $drugId = $searchValues[3];

        if ($drugId == -1 || $drugId == "") {
            $sql = 'SELECT t.* FROM recepittmp  t  
                    join receiptdrugs  d        on t.id = d.receiptid
                    join storedrugsvalidation   v    on v.id = d.storedrugsvalidationid
                    join storedrugs   s    on s.id = v.storedrugid
                    where t.type = 1
                    and t.receiptdate  between "' . $dateFrom . '"  AND  "' . $dateTo . '"';

            $res = R::getAll($sql);
            $gg = 0;
            foreach ($res as $data) {
                if ($data['doctorid'] != 0) {
                    $docName = R::getRow('SELECT user_name FROM awusers WHERE user_id = ? ', [$data['doctorid']]);
                    $res[$gg]['doctorname'] = $docName['user_name'];
                }

                if ($data['patientid'] != 0) {
                    $patient = R::load('patients', $data['patientid']);
                    $res[$gg]['patientname'] = $patient->wifename;
                }
######################################################
                $recepitTmpId = $data['id'];
                $receiptdrugs = R::findAll('receiptdrugs', ' receiptid = ' . $recepitTmpId);
                $storedrugsIds = array();
                foreach ($receiptdrugs as $data1) {
                    $sql = ' select storedrugid
                from        storedrugsvalidation
                where  id =  ' . $data1->storedrugsvalidationid;

                    $storedrugsId = R::getRow($sql);
                    array_push($storedrugsIds, $storedrugsId['storedrugid']);
                }

                $res1 = array();
                $storedrugsIds = array_values(array_unique($storedrugsIds));
                $i = 0;
                foreach ($storedrugsIds as $data2) {
                    $storedrugsvalidation = R::findAll('storedrugsvalidation', '  storedrugid = ' . $data2);
                    $res1[$i]['amountcached'] = 0;

                    foreach ($storedrugsvalidation as $valid) {
                        $sql = ' select sum(receiptdrugs.amount) as currentsum
                from        receiptdrugs
                where  receiptdrugs.receiptid =  ' . $recepitTmpId . ' and receiptdrugs.storedrugsvalidationid =  ' . $valid->id;

                        $currentsum = R::getRow($sql);
                        $res1[$i]['amountcached'] = $res1[$i]['amountcached'] + $currentsum['currentsum'];
                    }
                    $storedrugs = R::load('storedrugs', $data2);
                    $drug = R::load('phdrugs', $storedrugs->drugid);
                    $res1[$i]['drugname'] = $drug->drugname;
                    $res1[$i]['drugtype'] = $drug->drugtype;
                    $i++;
                }

                $res[$gg]['res'] = $res1;
                $gg++;
            }
        } else {
            $sql = 'SELECT distinct t.id , t.doctorid  ,t.patientid  , t.doctorname  ,t.patientname   FROM recepittmp  t  
                    join receiptdrugs  d        on t.id = d.receiptid
                    join storedrugsvalidation   v    on v.id = d.storedrugsvalidationid
                    join storedrugs   s    on s.id = v.storedrugid
                    where s.drugid = ' . $drugId . '  and  t.type = 1
                    and t.receiptdate  between "' . $dateFrom . '"  AND  "' . $dateTo . '" ';


            $res = R::getAll($sql);
            $gg = 0;
            foreach ($res as $data) {
                if ($data['doctorid'] != 0) {
                    $docName = R::getRow('SELECT user_name FROM awusers WHERE user_id = ? ', [$data['doctorid']]);
                    $res[$gg]['doctorname'] = $docName['user_name'];
                }

                if ($data['patientid'] != 0) {
                    $patient = R::load('patients', $data['patientid']);
                    $res[$gg]['patientname'] = $patient->wifename;
                }
######################################################
                $recepitTmpId = $data['id'];
                $receiptdrugs = R::findAll('receiptdrugs', ' receiptid = ' . $recepitTmpId);

                $storedrugsIds = array();
                foreach ($receiptdrugs as $data1) {
                    $sql = ' select storedrugid 
                from        storedrugsvalidation
                join       storedrugs      on storedrugs.id = storedrugsvalidation.storedrugid  and storedrugs.drugid = ' . $drugId . '  
                where  storedrugsvalidation.id =  ' . $data1->storedrugsvalidationid;
                    $storedrugsId = R::getRow($sql);

                    array_push($storedrugsIds, $storedrugsId['storedrugid']);
                }

                $res1 = array();
                $storedrugsIds = array_values(array_unique($storedrugsIds));
                $i = 0;

                foreach ($storedrugsIds as $data2) {
                    if ($data2 != "") {
                        $storedrugsvalidation = R::findAll('storedrugsvalidation', 'storedrugid = ' . $data2);
                        $res1[$i]['amountcached'] = 0;

                        foreach ($storedrugsvalidation as $valid) {
                            $sql = ' select sum(receiptdrugs.amount) as currentsum
                from        receiptdrugs
                where  receiptdrugs.receiptid =  ' . $recepitTmpId . ' and receiptdrugs.storedrugsvalidationid =  ' . $valid->id;

                            $currentsum = R::getRow($sql);
                            $res1[$i]['amountcached'] = $res1[$i]['amountcached'] + $currentsum['currentsum'];
                        }
                        $storedrugs = R::load('storedrugs', $data2);
                        $drug = R::load('phdrugs', $storedrugs->drugid);
                        $res1[$i]['drugname'] = $drug->drugname;
                        $res1[$i]['drugtype'] = $drug->drugtype;
                        $i++;
                    }
                }

                $res[$gg]['res'] = $res1;
                $gg++;
            }
        }



        $this->smarty->assign('reportType', $reportType);
        $this->smarty->assign('res', $res);
        $this->smarty->assign('rescount', count($res));
        $this->smarty->display('reports/result.html');
    }

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

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

//calling html files
        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
        $this->smarty->display('reports/validation.html');
        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

    private function validationsearch($searchValues) {
        $reportType = $searchValues[0];
        $dateFrom = $searchValues[1];
        $dateTo = $searchValues[2];

        $sql = 'SELECT v.storedrugid , ss.name , d.drugname , d.drugtype , v.currentbalance , v.validateto from  storedrugsvalidation  v      
                 join storedrugs s  on s.id = v.storedrugid
                 join stores     ss  on ss.id = s.storeid
                 join phdrugs      d  on d.id = s.drugid
                    where v.validateto  between "' . $dateFrom . '"  AND  "' . $dateTo . '" and v.currentbalance > 0 ';


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

        $this->smarty->assign('reportType', $reportType);
        $this->smarty->assign('res', $res);
        $this->smarty->assign('rescount', count($res));
        $this->smarty->display('reports/result.html');
    }

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

        $doctors = R::getAll('SELECT user_id , user_name FROM awusers WHERE role_id  in ( 1 , 4 ) ');
        $this->smarty->assign('doctors', $doctors);

        $allMed = R::findAll('phdrugs', 'order by drugname asc');
        $this->smarty->assign('allMed', $allMed);


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


//calling html files
        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
        $this->smarty->display('reports/internal.html');
        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

    private function internalexporttsearch($searchValues) {
        $reportType = $searchValues[0];
        $dateFrom = $searchValues[1];
        $dateTo = $searchValues[2];
        $doctorId = $searchValues[3];
        $drugId = $searchValues[4];

        $drSql = '';

        if ($doctorId == -1 || $doctorId == "") {
            $drSql = '';
        } else {
            $drSql = ' and  t.doctorid = ' . $doctorId . '  ';
        }

        if ($drugId == -1 || $drugId == "") {
            $sql = 'SELECT distinct t.id , t.doctorid  ,t.patientid ,t.patientid  FROM recepittmp  t  
                    join receiptdrugs  d        on t.id = d.receiptid
                    join storedrugsvalidation   v    on v.id = d.storedrugsvalidationid
                    join storedrugs   s    on s.id = v.storedrugid
                    where t.type = 0 ' . $drSql . '  
                    and t.receiptdate  between "' . $dateFrom . '"  AND  "' . $dateTo . '"';


            $res = R::getAll($sql);
            $gg = 0;
            foreach ($res as $data) {
                if ($data['doctorid'] != 0) {
                    $docName = R::getRow('SELECT user_name FROM awusers WHERE user_id = ? ', [$data['doctorid']]);
                    $res[$gg]['doctorname'] = $docName['user_name'];
                }

                if ($data['patientid'] != 0) {
                    $patient = R::load('patients', $data['patientid']);
                    $res[$gg]['patientname'] = $patient->wifename;
                }
######################################################
                $recepitTmpId = $data['id'];
                $receiptdrugs = R::findAll('receiptdrugs', ' receiptid = ' . $recepitTmpId);
                $storedrugsIds = array();
                foreach ($receiptdrugs as $data1) {
                    $sql = ' select storedrugid
                from        storedrugsvalidation
                where  id =  ' . $data1->storedrugsvalidationid;

                    $storedrugsId = R::getRow($sql);
                    array_push($storedrugsIds, $storedrugsId['storedrugid']);
                }

                $res1 = array();
                $storedrugsIds = array_values(array_unique($storedrugsIds));
                $i = 0;
                foreach ($storedrugsIds as $data2) {
                    $storedrugsvalidation = R::findAll('storedrugsvalidation', '  storedrugid = ' . $data2);
                    $res1[$i]['amountcached'] = 0;

                    foreach ($storedrugsvalidation as $valid) {
                        $sql = ' select sum(receiptdrugs.amount) as currentsum
                from        receiptdrugs
                where  receiptdrugs.receiptid =  ' . $recepitTmpId . ' and receiptdrugs.storedrugsvalidationid =  ' . $valid->id;

                        $currentsum = R::getRow($sql);
                        $res1[$i]['amountcached'] = $res1[$i]['amountcached'] + $currentsum['currentsum'];
                    }
                    $storedrugs = R::load('storedrugs', $data2);
                    $drug = R::load('phdrugs', $storedrugs->drugid);
                    $res1[$i]['drugname'] = $drug->drugname;
                    $res1[$i]['drugtype'] = $drug->drugtype;
                    $i++;
                }

                $res[$gg]['res'] = $res1;
                $gg++;
            }
        } else {

            $sql = 'SELECT distinct t.id , t.doctorid  ,t.patientid ,t.patientid  FROM recepittmp  t  
                    join receiptdrugs  d        on t.id = d.receiptid
                    join storedrugsvalidation   v    on v.id = d.storedrugsvalidationid
                    join storedrugs   s    on s.id = v.storedrugid
                    where s.drugid = ' . $drugId . '  and  t.type = 0 ' . $drSql . '     
                     and t.receiptdate  between "' . $dateFrom . '"  AND  "' . $dateTo . '" ';


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

            $gg = 0;
            foreach ($res as $data) {
                if ($data['doctorid'] != 0) {
                    $docName = R::getRow('SELECT user_name FROM awusers WHERE user_id = ? ', [$data['doctorid']]);
                    $res[$gg]['doctorname'] = $docName['user_name'];
                }

                if ($data['patientid'] != 0) {
                    $patient = R::load('patients', $data['patientid']);
                    $res[$gg]['patientname'] = $patient->wifename;
                }
######################################################
                $recepitTmpId = $data['id'];
                $receiptdrugs = R::findAll('receiptdrugs', ' receiptid = ' . $recepitTmpId);

                $storedrugsIds = array();
                foreach ($receiptdrugs as $data1) {
                    $sql = ' select storedrugid 
                from        storedrugsvalidation
                join       storedrugs      on storedrugs.id = storedrugsvalidation.storedrugid  and storedrugs.drugid = ' . $drugId . '  
                where  storedrugsvalidation.id =  ' . $data1->storedrugsvalidationid;
                    $storedrugsId = R::getRow($sql);

                    array_push($storedrugsIds, $storedrugsId['storedrugid']);
                }

                $res1 = array();
                $storedrugsIds = array_values(array_unique($storedrugsIds));
                $i = 0;

                foreach ($storedrugsIds as $data2) {
                    if ($data2 != "") {
                        $storedrugsvalidation = R::findAll('storedrugsvalidation', 'storedrugid = ' . $data2);
                        $res1[$i]['amountcached'] = 0;

                        foreach ($storedrugsvalidation as $valid) {
                            $sql = ' select sum(receiptdrugs.amount) as currentsum
                from        receiptdrugs
                where  receiptdrugs.receiptid =  ' . $recepitTmpId . ' and receiptdrugs.storedrugsvalidationid =  ' . $valid->id;

                            $currentsum = R::getRow($sql);
                            $res1[$i]['amountcached'] = $res1[$i]['amountcached'] + $currentsum['currentsum'];
                        }
                        $storedrugs = R::load('storedrugs', $data2);
                        $drug = R::load('phdrugs', $storedrugs->drugid);
                        $res1[$i]['drugname'] = $drug->drugname;
                        $res1[$i]['drugtype'] = $drug->drugtype;
                        $i++;
                    }
                }

                $res[$gg]['res'] = $res1;
                $gg++;
            }
        }

        $this->smarty->assign('reportType', $reportType);
        $this->smarty->assign('res', $res);
        $this->smarty->assign('rescount', count($res));
        $this->smarty->display('reports/result.html');
    }

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

        $doctors = R::getAll('SELECT user_id , user_name FROM awusers WHERE role_id  in ( 1 , 4 , 8 ) ');
        $this->smarty->assign('doctors', $doctors);

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

//calling html files
        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
        $this->smarty->display('reports/doctor.html');
        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

    private function doctorreportsearch($searchValues) {
        $reportType = $searchValues[0];
        $dateFrom = $searchValues[1];
        $dateTo = $searchValues[2];
        $doctorId = $searchValues[3];


        if ($doctorId == -1 || $doctorId == "") {
            $drSql = '';

            $sql = 'SELECT distinct  d.drugname  , d.drugtype     FROM receiptdrugs d  
                   join recepittmp  t        on t.id = d.receiptid
                    where  ' . $drSql . '       
                      t.receiptdate  between "' . $dateFrom . '"  AND  "' . $dateTo . '" ';

            $res = R::getAll($sql);
        }
        elseif ($doctorId == 0) {
            $drSql = '';

            $sql = 'SELECT distinct  d.drugname  FROM receiptdrugs d  
                   join recepittmp  t        on t.id = d.receiptid
                    where  ' . $drSql . '       
                      t.receiptdate  between "' . $dateFrom . '"  AND  "' . $dateTo . '" ';

            $res_med = R::getAll($sql);
            $this->smarty->assign('medicine_result', $res_med);

            //get all doctors
            $doctors_sql = 'SELECT distinct t.doctorid  FROM receiptdrugs d  
                   join recepittmp  t        on t.id = d.receiptid
                    where  ' . $drSql . '       
                      t.receiptdate  between "' . $dateFrom . '"  AND  "' . $dateTo . '" ';
            $doctor_res = R::getAll($doctors_sql);
            $doctor_res_array = array();
            foreach ($doctor_res as $doc_res){
                $user_info = R::getRow('select * from awusers where user_id = ?',[$doc_res['doctorid']]);
                $doc_res['doctor_name'] = $user_info['name'];


                $gg_med = 0;
                foreach ($res_med as $drug) {
                    $sql = ' select  sum(d.amount)  as drugSum   from        receiptdrugs d  
                 join recepittmp  t        on t.id = d.receiptid
                    where  t.`doctorid` = '.$doc_res['doctorid'].' and       
                      d.drugname = "' . $drug['drugname'] . '"   and t.receiptdate  between "' . $dateFrom . '"  AND  "' . $dateTo . '" ';

                    $drugSum = R::getRow($sql);
                    $res_med[$gg_med]['drugSum'] = $drugSum['drugSum'];
                    $gg_med++;

                }

                $doc_res['drugs'] = $res_med;

                array_push($doctor_res_array,$doc_res);

            }



            $this->smarty->assign('doctor_res_array', $doctor_res_array);

        }
        else {
            $drSql = ' t.doctorid = ' . $doctorId . ' and   ';

            $sql = 'SELECT distinct  d.drugname  , d.drugtype     FROM receiptdrugs d  
                   join recepittmp  t        on t.id = d.receiptid
                    where  ' . $drSql . '       
                      t.receiptdate  between "' . $dateFrom . '"  AND  "' . $dateTo . '" ';

            $res = R::getAll($sql);
        }





        $gg = 0;
        foreach ($res as $drug) {

            if ($doctorId == 0) {

            }else{
                $sql = ' select  sum(d.amount)  as drugSum   from        receiptdrugs d  
                 join recepittmp  t        on t.id = d.receiptid
                    where  ' . $drSql . '       
                      d.drugname = "' . $drug['drugname'] . '"   and  d.drugtype = "' . $drug['drugtype'] . '"    and t.receiptdate  between "' . $dateFrom . '"  AND  "' . $dateTo . '" ';

            }


            $drugSum = R::getRow($sql);
            $res[$gg]['drugSum'] = $drugSum['drugSum'];
            $gg++;
        }
        $this->smarty->assign('is_all', $doctorId);
        $this->smarty->assign('reportType', $reportType);
        $this->smarty->assign('res', $res);
        $this->smarty->assign('rescount', count($res));
        $this->smarty->display('reports/result.html');
    }

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

    public function __destruct() {
//close connection
        R::close();
    }

}
