<?php

/*
 * awframework from aw.inc present aw controller
 * it will help you develop easy and fast with secure tools
 * you can edit and add what you feel right
 * don't change in strucure of file
 */

require_once("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("imp/_autho.php"); //general functions
        $this->smarty = $smarty;
        $this->hosturl = $hosturl;

        //here name of controll to known
        $this->controllname = 'financialreport';
        $this->shcontrollname = 'Home';
        $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] = 'financialreport'; //controller name or tab name
        $this->active = $active;


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

    //here the frist page and
    //the frist action in this class
    public function index() {
        //check login
        autho::checkautho($this->hosturl);
        //check authoize
        autho::checkauthoize($this->controllname, $this->hosturl, $_SESSION['role_id']);
        //here will load the menu show
        //R::debug(true);

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

        $py_all_departs = R::findAll('awrole', 'visit_show = 1 ');
        $this->smarty->assign('py_all_departs', $py_all_departs);

        $py_all_doc = R::getAll('select awusers.* from awusers
               join  awrole   on awrole.id =   awusers.role_id
                where awrole.is_doctor =  1 ');
        $this->smarty->assign('py_all_doc', $py_all_doc);

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

        //$this->commonSearch($today, $today);

        $doctors = R::getAll('select user_id , name  from awusers 
                where deleted != 1 and    role_id in ( select id from awrole where visit_show = 1   )');
        $this->smarty->assign('doctors', $doctors);

        $recep_users = R::getAll('select * from awusers where deleted != 1 and    role_id  = 3 ');
        $this->smarty->assign('recep_users', $recep_users);

        $this->smarty->assign('login_role', $_SESSION['role_id']);

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

        $detections = R::getAll('select  id , title from detections where del = 0');
        $this->smarty->assign('detections', $detections);

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

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

        $fdate = filter_input(INPUT_POST, 'fdate');
        $todate = filter_input(INPUT_POST, 'todate');
        $patientId = filter_input(INPUT_POST, 'value');
        $hPatientId = filter_input(INPUT_POST, 'hvalue');
        $userId = filter_input(INPUT_POST, 'userId');
        $fordepartment = filter_input(INPUT_POST, 'fordepartment');
        $repDoctors = filter_input(INPUT_POST, 'repDoctors');
        $labType = filter_input(INPUT_POST, 'labType');
        $offerId = filter_input(INPUT_POST, 'offerId');
        $detection = filter_input(INPUT_POST, 'detection');
        $referDrId = filter_input(INPUT_POST, 'referDrId');

        $this->commonSearch($fdate, $todate, $patientId, $hPatientId, $userId, $fordepartment
                , $repDoctors, $labType, $offerId, $detection, $referDrId);

        $this->smarty->assign('login_role', $_SESSION['role_id']);
        $this->smarty->display('reports/financialsearch.html');
    }

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

        $fdate = filter_input(INPUT_POST, 'fdate');
        $todate = filter_input(INPUT_POST, 'todate');
        $patientId = filter_input(INPUT_POST, 'value');
        $hPatientId = filter_input(INPUT_POST, 'hvalue');
        $userId = filter_input(INPUT_POST, 'userId');
        $fordepartment = filter_input(INPUT_POST, 'fordepartment');
        $repDoctors = filter_input(INPUT_POST, 'repDoctors');
        $labType = filter_input(INPUT_POST, 'labType');
        $offerId = filter_input(INPUT_POST, 'offerId');
        $detection = filter_input(INPUT_POST, 'detection');
        $referDrId = filter_input(INPUT_POST, 'referDrId');

        $this->commonSearch($fdate, $todate, $patientId, $hPatientId, $userId, $fordepartment
                , $repDoctors, $labType, $offerId, $detection, $referDrId);

        $this->smarty->assign('login_role', $_SESSION['role_id']);
        $this->smarty->display('reports/financialprint.html');
    }

    public function commonSearch($fdate, $todate, $patientId = 0, $hPatientId = 0, $userId = 0
            , $fordepartment = 0, $repDoctors = 0, $labType = 0, $offerId = 0, $detection = 0, $referDrId = 0) {

        $ext = '';
        if (!empty($userId) && $userId > 0) {
            $ext .= ' and visits.user_id =   ' . $userId;
        }

        if (!empty($detection) && $detection > 0) {
            $ext .= ' and visits.detectionid =   ' . $detection;
        }

        if (!empty($fordepartment) && $fordepartment > 0) {
            $ext .= ' and visits.for_department =   ' . $fordepartment;
        }

        if (!empty($repDoctors) && $repDoctors > 0) {
            $ext .= ' and visits.for_doctor =   ' . $repDoctors;
        }

        if (!empty($patientId) && $patientId > 0) {
            $ext .= ' and visits.patientid = ' . $patientId;
        } elseif (!empty($hPatientId) && $hPatientId > 0) {
            $ext .= ' and visits.patientid = ' . $hPatientId;
        }

        if (!empty($referDrId) && ($referDrId > 0 || $referDrId[0] == "*" || $referDrId == -1)) {
            $refer_doctor_tb = 0;

            if ($referDrId[0] == "*") {
                $referDrId = ltrim($referDrId, '*');
                $refer_doctor_tb = 1;
            }

            $ext .= ' and  visits.refer_doctor =   ' . $referDrId . '  and visits.refer_doctor_tb =  ' . $refer_doctor_tb;
        }


        if ($fordepartment == 12) {
            switch ($labType) {
                case 1:      ##   Offers
                    if ($offerId > 0) {
                        $ext .= ' and investigationresults.offer_id =   ' . $offerId;
                    } else {
                        $ext .= ' and investigationresults.offer_id >  0  ';
                    }
                    break;
                case 2:     ##  invests
                    $ext .= ' and investigationresults.offer_id <=  0  ';
                    break;
            }
        }

        $this->executeSql($fdate, $todate, $ext);
    }

    public function executeSql($fdate, $todate, $ext) {

        /* $sql = 'select distinct  visits.id  as visitId  ,  detectionvalue_visa , detectionvalue_cash
          , totaldetectionvalue, restdetectionvalue , detectionid
          ,  detections.title as type, visits.patientid ,visits.user_id    , awrole.name    as deptName   , visits.discount
          ,visits.for_husband , patients.wifename, patients.husdandname, patients.address   ,visits.for_department  as dept
          , patients.statusno  ,visits.visitdate    ,visits.notes    as vnotes   , visits.visitid as original_visitid
          , IF(visits.rfc = 1, "Yes", "No") AS rfc
          , cu.name as doctor , hcu.name as h_doctor   , vu.name as visitDoctor  , 0 as refund   , visits.printserial
          , visits.customer_add       , visits.child     , "" as visitHour
          , visits.refer_doctor   , visits.refer_doctor_tb
          from visits
          join  patients                         on  visits.patientid = patients.id
          left   join  awrole                  on  visits.for_department = awrole.id
          left join  detections                on  visits.detectionid = detections.id
          left  join awusers  vu            on vu.user_id  =  visits.for_doctor
          left  join awusers  cu              on cu.user_id  =  patients.doctor1
          left  join awusers  hcu          on hcu.user_id  =  patients.h_doctor1
          -- left  join  investigationresults                  on  visits.id = investigationresults.visitid
          where visits.deleted = 0    and visits.customer_add != 1
          and visitdate >= "' . $fdate . '" and visitdate <= "' . $todate . '"

          ' . $ext; */

        $sql = 'SELECT DISTINCT
                    visits.id AS visitId,
                    detectionvalue_visa,
                    detectionvalue_cash,
                    totaldetectionvalue,
                    restdetectionvalue,
                    detectionid,
                    detections.title AS TYPE,
                    visits.patientid,
                    visits.user_id,
                    awrole.name AS deptName,
                    visits.discount,
                    visits.for_husband,
                    patients.wifename,
                    patients.husdandname,
                    patients.address,
                    visits.for_department AS dept,
                    patients.statusno,
                    visits.visitdate,
                    visits.notes AS vnotes,
                    visits.visitid AS original_visitid,
                    IF(visits.rfc = 1, "Yes", "No") AS rfc,
                    cu.name AS doctor,
                    hcu.name AS h_doctor,
                    vu.name AS visitDoctor,
                    0 AS refund,
                    visits.printserial,
                    visits.customer_add,
                    visits.child,
                    "" AS visitHour,
                    visits.refer_doctor,
                    visits.refer_doctor_tb
                    FROM
                        visits
                        JOIN patients ON visits.patientid = patients.id
                        LEFT JOIN awrole ON visits.for_department = awrole.id
                        LEFT JOIN detections ON visits.detectionid = detections.id
                        LEFT JOIN awusers vu ON vu.user_id = visits.for_doctor
                        LEFT JOIN awusers cu ON cu.user_id = patients.doctor1
                        LEFT JOIN awusers hcu ON hcu.user_id = patients.h_doctor1
                 		LEFT JOIN investigationresults ON visits.id = investigationresults.visitid
                    WHERE
                        visits.deleted = 0 AND visits.customer_add != 1 AND visitdate >= "' . $fdate . '" AND visitdate <= "' . $todate . '" ' . $ext;

        //AND investigationresults.date >= "' . $fdate . '" AND investigationresults.date <= "' . $todate . '"   ' . $ext;

        $allData = R::getAll($sql);
        $this->common($allData);
    }

    function searchForId($id, $array, $string = 'id') {
        foreach ($array as $key => $val) {
            if ($val[$string] === $id) {
                return $key;
            }
        }
        return null;
    }

    function searchForIds($id, $array, $string = 'id') {
        $keys = [];
        foreach ($array as $key => $val) {
            if ($val[$string] === $id) {
                $keys[] = $key;
            }
        }
        return $keys;
    }

    function common($allData) {
        $totalCost = 0;

        $totalCash = 0;
        $totalVisa = 0;
        $totalDept = 0;
        $totalDiscount = 0;

        $totalPaid = 0;

        $totalOldCash = 0;
        $totalOldVisa = 0;
        $totalOld = 0;

        $totalRefundCash = 0;
        $totalRefundVisa = 0;

        $total = 0;
        $totalC = 0;
        $totalV = 0;

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

        // get all ids
        list($refer_doctors, $refers, $insert_users, $visits_ids, $original_visitId) = [[], [], [], [], []];
        foreach ($allData as $datum) {
            if ($datum['refer_doctor'] > 0) {
                if ($datum['refer_doctor_tb'] == 1) {
                    $refer_doctors[] = $datum['refer_doctor'];
                } else {
                    $refers[] = $datum['refer_doctor'];
                }
            }
            $insert_users[] = $datum['user_id'];
            $visits_ids[] = $datum['visitId'];
            if ($datum['original_visitid'] > 0) {
                $original_visitId[] = $datum['original_visitid'];
            }
        }
        !empty($refer_doctors) ?: $refer_doctors = [0];
        !empty($refers) ?: $refers = [0];
        !empty($insert_users) ?: $insert_users = [0];
        !empty($visits_ids) ?: $visits_ids = [0];
        !empty($original_visitId) ?: $original_visitId = [0];

        // queries
        $refer_doctors_data = R::getAll('select user_id,name from awusers where user_id in (' . implode(',', $refer_doctors) . ')');
        $refers_data = R::getAll('select id,name from refer where id in (' . implode(',', $refers) . ')');
        $insert_users_data = R::getAll('select user_id,name from awusers where user_id in (' . implode(',', array_unique($insert_users)) . ')');
        $cal_refund_data = R::getAll('SELECT COALESCE(SUM(detectionvalue_cash),0) as refund_cash , COALESCE(SUM(detectionvalue_visa),0) as refund_visa,visitid FROM visits where deleted != 1 and detectionid = 9999 and visitid in (' . implode(',', $visits_ids) . ') GROUP BY visitid');
        $original_visits_data = R::getAll('SELECT id,detectionid FROM visits where id in (' . implode(',', $original_visitId) . ')');
        $invest_details_data = R::getAll('SELECT invests.name,investigationresults.visitid FROM invests JOIN investigationresults ON invests.id = investigationresults.investid JOIN visits ON visits.id = investigationresults.visitid WHERE investigationresults.deleted = 0 AND investigationresults.visitid in (' . implode(',', $visits_ids) . ')');
        $ray_details_data = R::getAll('SELECT rays.name,raysresults.visitid FROM rays JOIN raysresults ON rays.id = raysresults.rayid JOIN visits ON visits.id = raysresults.visitid WHERE raysresults.deleted = 0 AND raysresults.visitid in (' . implode(',', $visits_ids) . ')');
        $reserves_data = R::getAll('SELECT * FROM visit_hours where visit_id in (' . implode(',', $visits_ids) . ') and deleted = 0');

        // get all ids level 2
        $detections_ids = [];
        foreach ($original_visits_data as $datum) {
            $detections_ids[] = $datum['detectionid'];
        }
        !empty($detections_ids) ?: $detections_ids = [0];
        $detections_data = R::getAll('SELECT id,title FROM detections where id in (' . implode(',', array_unique($detections_ids)) . ')');


        foreach ($allData as $data) {

            ### old way
            /* if ($data['refer_doctor'] > 0) {
              if ($data['refer_doctor_tb'] == 1) {
              $refer = R::getRow('select name from awusers where user_id = ?', [$data['refer_doctor']]);
              $data['refer'] = $refer['name'];
              } else {
              $refer = R::load('refer', $data['refer_doctor']);
              $data['refer'] = $refer->name;
              }
              } ##############
              else {
              $data['refer'] = "لا يوجد ";
              } */

            ### new way
            if ($data['refer_doctor'] > 0) {
                if ($data['refer_doctor_tb'] == 1) {
                    $refer_index = $this->searchForId($data['refer_doctor'], $refer_doctors_data, 'user_id');
                    if ($refer_index) {
                        $data['refer'] = $refer_doctors_data[$refer_index]['name'];
                    }
                } else {
                    $refer_index = $this->searchForId($data['refer_doctor'], $refers_data);
                    if ($refer_index) {
                        $data['refer'] = $refers_data[$refer_index]['name'];
                    }
                }
            } else {
                $data['refer'] = "لا يوجد ";
            }

            $totalCost += $data['totaldetectionvalue'];

            $totalDept += $data['restdetectionvalue'];

            $totalDiscount += $data['discount'];

            ### old way
            /* $user_name = R::getRow('select * from awusers where user_id = ?', [$data['user_id']]);
              $data['insert_user'] = $user_name['name']; */

            ### new way
            $insert_user_index = $this->searchForId($data['user_id'], $insert_users_data, 'user_id');
            if ($insert_user_index) {
                $data['insert_user'] = $insert_users_data[$insert_user_index]['name'];
            }

            if ($data['detectionid'] == 999) {
                ### old way
                //$originalVisit = R::load('visits', $data['original_visitid']);
                ### new way
                $original_visit_index = $this->searchForId($data['original_visitid'], $original_visits_data);
                if ($original_visit_index) {
                    $detection_id = $original_visits_data[$original_visit_index]['detectionid'];
                }

                ### old way
                //$det = R::load('detections', $originalVisit->detectionid);
                ### new way
                $detection_index = $this->searchForId($detection_id, $detections_data);
                $title = '';
                if ($detection_index) {
                    $title = $detections_data[$detection_index]['title'];
                }


                $data['type'] = 'دفع متبقى ';
                //$data['type'] .= ' ( ' . $det->title . ' ) ';
                $data['type'] .= ' ( ' . $title . ' ) ';


                $totalOldVisa += $data['detectionvalue_visa'];
                $totalOldCash += $data['detectionvalue_cash'];

                $totalOld += $data['detectionvalue_visa'];
                $totalOld += $data['detectionvalue_cash'];
            } elseif ($data['detectionid'] == 9999) {

                ### old way
                //$originalVisit = R::load('visits', $data['original_visitid']);
                ### new way
                $original_visit_index = $this->searchForId($data['original_visitid'], $original_visits_data);
                if ($original_visit_index) {
                    $detection_id = $original_visits_data[$original_visit_index]['detectionid'];
                }

                ### old way
                //$det = R::load('detections', $originalVisit->detectionid);
                ### new way
                $detection_index = $this->searchForId($detection_id, $detections_data);
                $title = '';
                if ($detection_index) {
                    $title = $detections_data[$detection_index]['title'];
                }


                $data['type'] = 'مرتجع العملاء  ';
                //$data['type'] .= ' ( ' . $det->title . ' ) ';
                $data['type'] .= ' ( ' . $title . ' ) ';


                $totalRefundVisa += $data['detectionvalue_visa'];
                $totalRefundCash += $data['detectionvalue_cash'];
            } else {

                $totalVisa += $data['detectionvalue_visa'];
                $totalCash += $data['detectionvalue_cash'];

                $totalPaid += $data['detectionvalue_visa'];
                $totalPaid += $data['detectionvalue_cash'];

                ### old way
                //$data['refund'] = $this->calcRefund($data['visitid']);
                ### new way
                $refund_index = $this->searchForId($data['visitId'], $cal_refund_data, 'visitid');
                if ($refund_index) {
                    $refund = $cal_refund_data[$refund_index]['refund_cash'] + $cal_refund_data[$refund_index]['refund_visa'];
                    $data['refund'] = $refund;
                }
            }

            ## 12  investigations dept     ,  5   rays dept
            if ($data['dept'] == 5 && $data['detectionid'] != 999 && $data['detectionid'] != 9999) {
                ### old way
                /* $type = $this->raysDetails($data['visitId']);
                  $data['type'] = $type; */

                ### new way
                $ray_details_indexes = $this->searchForIds($data['visitId'], $ray_details_data, 'visitid');
                $type = $this->raysDetailsNew($ray_details_indexes, $ray_details_data);
                $data['type'] = $type;
            }

            if ($data['dept'] == 12 && $data['detectionid'] != 999 && $data['detectionid'] != 9999) {
                ### old way
                /* $type = $this->invsDetails($data['visitId']);
                  $data['type'] = $type; */

                ### new way
                $invest_details_indexes = $this->searchForIds($data['visitId'], $invest_details_data, 'visitid');
                $type = $this->investDetailsNew($invest_details_indexes, $invest_details_data);
                $data['type'] = $type;
            }

            ### old way
            /* $reserve = R::findOne('visit_hours', ' visit_id = ? and deleted = 0 ', [$data['visitId']]);
              if (count($reserve) > 0) {
              $data['visitHour'] = date('h:i A', strtotime($reserve->visit_hour));
              } */

            ### new way
            $reserve_index = $this->searchForId($data['visitId'], $reserves_data, 'visit_id');
            if ($reserve_index) {
                $data['visitHour'] = date('h:i A', strtotime($reserves_data[$reserve_index]['visit_hour']));
            }


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

        $total = $totalPaid + $totalOld;
        $totalC = $totalCash + $totalOldCash - $totalRefundCash;
        $totalV = $totalVisa + $totalOldVisa - $totalRefundVisa;

        $this->smarty->assign('totalC', $totalC);
        $this->smarty->assign('totalV', $totalV);
        $this->smarty->assign('totalCost', $totalCost);
        $this->smarty->assign('totalCash', $totalCash);
        $this->smarty->assign('totalVisa', $totalVisa);
        $this->smarty->assign('totalDept', $totalDept);
        $this->smarty->assign('totalPaid', $totalPaid);
        $this->smarty->assign('totalOldCash', $totalOldCash);
        $this->smarty->assign('totalOldVisa', $totalOldVisa);
        $this->smarty->assign('totalOld', $totalOld);
        $this->smarty->assign('totalDiscount', $totalDiscount);
        $this->smarty->assign('totalRefundVisa', $totalRefundVisa);
        $this->smarty->assign('totalRefundCash', $totalRefundCash);
        $this->smarty->assign('total', $total);

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

        $this->smarty->assign('drDeptName', $programesetting->fin_doc_dept);


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

    function investDetailsNew($keys, $source) {

        $details = ' <ul style="margin: 0 ; padding: 0;"> ';
        $x = 1;
        foreach ($keys as $key) {
            $data = $source[$key];
            $details .= '<li>';
            if ($x != 1) {
                $details .= ' , ';
            }
            $details .= ' ' . $data['name'] . ' </li>';
            $x++;
        }
        $details .= ' </ul> ';
        return $details;
    }

    function raysDetailsNew($keys, $source) {

        $details = ' <ul style="margin: 0 ; padding: 0;"> ';
        foreach ($keys as $key) {
            $data = $source[$key];
            $details .= '<li>' . $data['name'] . ' </li>';
        }
        $details .= ' </ul> ';
        return $details;
    }

    function invsDetails($visitId) {

        $details = '';

        $sql = 'select   invests.name   from invests  
                               join  investigationresults                  on  invests.id = investigationresults.investid  
                                join  visits                                       on  visits.id = investigationresults.visitid 

                                where 
                                   investigationresults.deleted = 0 and  investigationresults.visitid =  ' . $visitId;

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

        if (count($result) > 0) {

            $details = ' <ul style="margin: 0 ; padding: 0;"> ';

            $x = 1;
            foreach ($result as $data) {
                $details .= '<li>';
                if ($x != 1) {
                    $details .= ' , ';
                }
                $details .= ' ' . $data['name'] . ' </li>';
                $x++;
            }

            $details .= ' </ul> ';
        }


        return $details;
    }

    function raysDetails($visitId) {

        $details = '';

        $sql = 'select   rays.name   from rays  
                               join  raysresults                               on  rays.id = raysresults.rayid  
                                join  visits                                       on  visits.id = raysresults.visitid 

                                where 
                                    raysresults.deleted = 0 and raysresults.visitid =  ' . $visitId;


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

        if (count($result) > 0) {

            $details = ' <ul style="margin: 0 ; padding: 0;"> ';

            foreach ($result as $data) {
                $details .= '<li>' . $data['name'] . ' </li>';
            }

            $details .= ' </ul> ';
        }


        return $details;
    }

    function calcRefund($visitId) {

        ## refund 
        $refundVisits = R::getRow('SELECT COALESCE(SUM(detectionvalue_cash),0) as refund_cash , COALESCE(SUM(detectionvalue_visa),0) as refund_visa 
                                    FROM visits  
                                    where deleted != ? and detectionid = 9999 and visitid = ?   ', [1, $visitId]);

        $refund = $refundVisits['refund_cash'] + $refundVisits['refund_visa'];

        return $refund;
    }

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

    public function drReport() {
        //check login
        autho::checkautho($this->hosturl);
        //check authoize
        autho::checkauthoize($this->controllname, $this->hosturl, $_SESSION['role_id']);
        //here will load the menu show
        //R::debug(true);

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

        $py_all_departs = R::findAll('awrole', 'visit_show = 1 ');
        $this->smarty->assign('py_all_departs', $py_all_departs);

        $doctors = R::getAll('select user_id , name  from awusers 
                where role_id in ( select id from awrole where visit_show = 1   )');
        $this->smarty->assign('doctors', $doctors);

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

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

        $fdate = filter_input(INPUT_POST, 'fdate');
        $todate = filter_input(INPUT_POST, 'todate');
        $repDoctor = filter_input(INPUT_POST, 'repDoctors');

        $fdate = date('Y/m/d', strtotime($fdate));
        $todate = date('Y/m/d', strtotime($todate));

        $days = $this->daysDetails($fdate, $todate, $repDoctor);
        $this->smarty->assign('days', $days);
        $this->smarty->display('reports/share/dr_report_search.html');
    }

    function daysDetails($fdate, $todate, $doctorId) {
        $days = array();

        $vCount = 0;
        $vCost = 0;
        $hCount = 0;
        $hCost = 0;
        $net = 0;

        for ($i = 1; $i <= 7; $i++) {
            $dayDet = $this->getDayName($i);

            $user = R::getRow('select hour_price  from awusers where user_id = ?', [$doctorId]);
            $hour_price = $user['hour_price'];

//            $total_hours = array('1' => 0, '2' => 0, '3' => 0, '4' => 0,
//                '5' => 0, '6' => 0, '0' => 0);

            $visits = $this->dayVisits($dayDet['dayNameEn'], $fdate, $todate, $doctorId);
            $hours = $this->drReservedHours($i, $fdate, $todate, $doctorId);
            $hoursPrice = $hours * $hour_price;

            $net = $visits['total'] - $hoursPrice;

            $day = array('no' => $i, 'dayNameEn' => $dayDet['dayNameEn']
                , 'visitsCount' => $visits['vCount'], 'total' => $visits['total']
                , 'hours' => $hours, 'hoursPrice' => $hoursPrice, 'net' => $net
            );


            array_push($days, $day);
            $vCount += $visits['vCount'];
            $vCost += $visits['total'];
            $hCount += $hours;
            $hCost += $hoursPrice;
        }

        $net = $vCost - $hCost;

        $this->smarty->assign('vCount', $vCount);
        $this->smarty->assign('vCost', $vCost);
        $this->smarty->assign('hCount', $hCount);
        $this->smarty->assign('hCost', $hCost);
        $this->smarty->assign('net', $net);
        return $days;
    }

    function drReservedHours($dayNo, $fdate, $todate, $doctorId) {

        switch ($dayNo) {
            case 2:
                $no = 0;
                break;
            case 3:
                $no = 1;
                break;
            case 4:
                $no = 2;
                break;
            case 5:
                $no = 3;
                break;
            case 6:
                $no = 4;
                break;
            case 7:
                $no = 5;
                break;
            case 1:
                $no = 6;
                break;
        }

        $total_hours = 0;


        ## ACTIVE 
        ##   1  -    start_date    between   $fdate and  $todate     
        ##  2  -    start_date    <   $fdate 
        #
        #
        ## NOT  ACTIVE  
        ##   3  -       start_date    <  $fdate         AND     cancel_date     between   $fdate and  $todate 
        ##   4  -       start_date    <  $fdate         AND     cancel_date     >  $todate 
        ##   5  -       start_date    between   $fdate and  $todate          AND     cancel_date     between   $fdate and  $todate 
        ##  6  -       start_date    between   $fdate and  $todate         AND    cancel_date     >  $todate 
#########################      1          #################
        $sql1 = 'select    id , reserve_hour , start_date    from clinic_reserves  
                   where    deleted != 1 and is_active = 1 and start_date <= "' . $todate . '"  and start_date >= "' . $fdate . '"    
                       and reserve_day_no = ' . $dayNo . '  and doctor_id =   ' . $doctorId;

        $reserve_hours1 = R::getAll($sql1);

        foreach ($reserve_hours1 as $data) {
            $beginDate = $data['start_date'];
            $noOfDays = $this->noOfDays($beginDate, $todate, $no);
            $total_hours = $total_hours + $noOfDays;
        }

#########################     2        #################

        $sql2 = 'select    id , reserve_hour , start_date    from clinic_reserves  
                   where    deleted != 1 and is_active = 1 and start_date < "' . $fdate . '"  
                       and reserve_day_no = ' . $dayNo . '  and doctor_id =   ' . $doctorId;

        $reserve_hours2 = R::getAll($sql2);

        foreach ($reserve_hours2 as $data) {
            $beginDate = $fdate;
            $noOfDays = $this->noOfDays($beginDate, $todate, $no);
            $total_hours = $total_hours + $noOfDays;
        }

#########################     3        #################
##   3  -       start_date    <  $fdate         AND     cancel_date     between   $fdate and  $todate 
        $sql3 = 'select    id , reserve_hour , start_date , cancel_date   from clinic_reserves  
                   where    is_active != 1 and deleted = 1  and start_date < "' . $fdate . '"   
                       and cancel_date <= "' . $todate . '"  and cancel_date >= "' . $fdate . '"    
                       and reserve_day_no = ' . $dayNo . '  and doctor_id =   ' . $doctorId;

        $reserve_hours3 = R::getAll($sql3);

        foreach ($reserve_hours3 as $data) {
            $beginDate = $fdate;
            $endDate = $data['cancel_date'];
            $noOfDays = $this->noOfDays($beginDate, $endDate, $no);
            $total_hours = $total_hours + $noOfDays;
        }


#########################     4        #################
###   4  -       start_date    <  $fdate         AND     cancel_date     >  $todate 

        $sql4 = 'select    id , reserve_hour , start_date  , cancel_date    from clinic_reserves  
                   where    is_active != 1 and deleted = 1  and start_date < "' . $fdate . '"   
                       and cancel_date > "' . $todate . '"  
                       and reserve_day_no = ' . $dayNo . '  and doctor_id =   ' . $doctorId;

        $reserve_hours4 = R::getAll($sql4);

        foreach ($reserve_hours4 as $data) {
            $beginDate = $fdate;
            $endDate = $todate;
            $noOfDays = $this->noOfDays($beginDate, $endDate, $no);
            $total_hours = $total_hours + $noOfDays;
        }
#########################     5        #################
###   5  -       start_date    between   $fdate and  $todate          AND     cancel_date     between   $fdate and  $todate 

        $sql5 = 'select    id , reserve_hour , start_date  , cancel_date    from clinic_reserves  
                   where    is_active != 1 and deleted = 1   
                   and start_date <= "' . $todate . '"  and start_date >= "' . $fdate . '"    
                       and cancel_date <= "' . $todate . '"  and cancel_date >= "' . $fdate . '"    
                       and reserve_day_no = ' . $dayNo . '  and doctor_id =   ' . $doctorId;

        $reserve_hours5 = R::getAll($sql5);

        foreach ($reserve_hours5 as $data) {
            $beginDate = $data['start_date'];
            $endDate = $data['cancel_date'];
            $noOfDays = $this->noOfDays($beginDate, $endDate, $no);
            $total_hours = $total_hours + $noOfDays;
        }

#########################    6        #################
# ##  6  -       start_date    between   $fdate and  $todate         AND    cancel_date     >  $todate 

        $sql6 = 'select    id , reserve_hour , start_date   , cancel_date    from clinic_reserves  
                   where    is_active != 1 and deleted = 1   
                   and start_date <= "' . $todate . '"  and start_date >= "' . $fdate . '"    
                       and cancel_date > "' . $todate . '"  
                       and reserve_day_no = ' . $dayNo . '  and doctor_id =   ' . $doctorId;

        $reserve_hours6 = R::getAll($sql6);

        foreach ($reserve_hours6 as $data) {
            $beginDate = $data['start_date'];
            $endDate = $todate;
            $noOfDays = $this->noOfDays($beginDate, $endDate, $no);
            $total_hours = $total_hours + $noOfDays;
        }
###########################################################
        return $total_hours;
    }

    function noOfDays($startDate, $endDate, $no) {
//        $startDate = "01-01-2018";
//        $endDate = "01-01-2019";
        // input start and end date 
//        $resultDays = array('Monday' => 0, 'Tuesday' => 0, 'Wednesday' => 0, 'Thursday' => 0,
//            'Friday' => 0, 'Saturday' => 0, 'Sunday' => 0);

        $resultDays = array('1' => 0, '2' => 0, '3' => 0, '4' => 0,
            '5' => 0, '6' => 0, '0' => 0);

        // change string to date time object 
        $startDate = new DateTime($startDate);
        $endDate = new DateTime($endDate);

        // iterate over start to end date 
        while ($startDate <= $endDate) {
            // find the timestamp value of start date 
            $timestamp = strtotime($startDate->format('d-m-Y'));

            // find out the day for timestamp and increase particular day 
            //    $weekDay = date('l', $timestamp); 
            $weekDay = date('w', $timestamp);
            $resultDays[$weekDay] = $resultDays[$weekDay] + 1;

            // increase startDate by 1 
            $startDate->modify('+1 day');
        }
        // print the result 
        return $resultDays[$no];
    }

    function dayVisits($dayName, $fdate, $todate, $doctorId) {

        $sql = 'select    detectionvalue_visa , detectionvalue_cash , totaldetectionvalue
                            , restdetectionvalue , discount   , center_discount_value
                       , id  as visitId  , visitid as original_visitid     , detectionid
                    from visits  
                   where deleted = 0  and visitdate >= "' . $fdate . '" and visitdate <= "' . $todate . '"   
                       and DAYNAME(visitdate)  = "' . $dayName . '"   and visits.for_doctor =   ' . $doctorId;

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

        $res = $this->drReportCommon($visits);
        return $res;
    }

    function drReportCommon($visits) {
        $totalCost = 0;

        $totalCash = 0;
        $totalVisa = 0;
        $totalDept = 0;
        $totalDiscount = 0;
        $totalCenterDiscount = 0;

        $totalPaid = 0;

        $totalOldCash = 0;
        $totalOldVisa = 0;
        $totalOld = 0;

        $totalRefundCash = 0;
        $totalRefundVisa = 0;

        $total = 0;
        $totalC = 0;
        $totalV = 0;

        $i = 0;
        foreach ($visits as $data) {
            $totalCost += $data['totaldetectionvalue'];
            $totalDept += $data['restdetectionvalue'];
            $totalDiscount += $data['discount'];
            $totalCenterDiscount += $data['center_discount_value'];

            if ($data['detectionid'] == 999) {
                $totalOldVisa += $data['detectionvalue_visa'];
                $totalOldCash += $data['detectionvalue_cash'];

                $totalOld += $data['detectionvalue_visa'];
                $totalOld += $data['detectionvalue_cash'];
            } elseif ($data['detectionid'] == 9999) {
                $totalRefundVisa += $data['detectionvalue_visa'];
                $totalRefundCash += $data['detectionvalue_cash'];
            } else {
                $totalVisa += $data['detectionvalue_visa'];
                $totalCash += $data['detectionvalue_cash'];

                $totalPaid += $data['detectionvalue_visa'];
                $totalPaid += $data['detectionvalue_cash'];
            }

            $i++;
        }

        $total = $totalPaid + $totalOld;
        $totalC = $totalCash + $totalOldCash - $totalRefundCash;
        $totalV = $totalVisa + $totalOldVisa - $totalRefundVisa;

        $totalAll = $totalC + $totalV - $totalCenterDiscount;

        $res = array('vCount' => $i, 'totalC' => $totalC, 'totalV' => $totalV, 'total' => $totalAll);
        return $res;
    }

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

        $fdate = filter_input(INPUT_POST, 'fdate');
        $todate = filter_input(INPUT_POST, 'todate');
        $repDoctor = filter_input(INPUT_POST, 'repDoctors');

        $fdate = date('Y/m/d', strtotime($fdate));
        $todate = date('Y/m/d', strtotime($todate));

        $days = $this->daysDetails($fdate, $todate, $repDoctor);
        $this->smarty->assign('days', $days);
        $this->smarty->display('reports/share/dr_report_print.html');
    }

    private function getDayName($no) {

        switch ($no) {
            case 2:
                $name = 'الأحد  ';
                $nameEn = 'Sunday ';
                break;
            case 3:
                $name = ' الإثنين';
                $nameEn = 'Monday ';
                break;
            case 4:
                $name = ' الثلاثاء';
                $nameEn = 'Tuesday ';
                break;
            case 5:
                $name = 'الأربعاء ';
                $nameEn = 'Wednesday ';
                break;
            case 6:
                $name = 'الخميس  ';
                $nameEn = 'Thursday ';
                break;
            case 7:
                $name = 'الجمعة ';
                $nameEn = 'Friday ';
                break;
            case 1:
                $name = 'السبت ';
                $nameEn = 'Saturday ';
                break;
        }

        $res = array('dayNo' => $no, 'dayName' => $name, 'dayNameEn' => $nameEn);

        return $res;
    }

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

    function noOfDaysArr($startDate, $endDate, $no) {
//        $startDate = "01-01-2018";
//        $endDate = "01-01-2019";
        // input start and end date 
//        $resultDays = array('Monday' => 0, 'Tuesday' => 0, 'Wednesday' => 0, 'Thursday' => 0,
//            'Friday' => 0, 'Saturday' => 0, 'Sunday' => 0);

        $resultDays = array('1' => 0, '2' => 0, '3' => 0, '4' => 0,
            '5' => 0, '6' => 0, '0' => 0);

        // change string to date time object 
        $startDate = new DateTime($startDate);
        $endDate = new DateTime($endDate);

        // iterate over start to end date 
        while ($startDate <= $endDate) {
            // find the timestamp value of start date 
            $timestamp = strtotime($startDate->format('d-m-Y'));

            // find out the day for timestamp and increase particular day 
            //    $weekDay = date('l', $timestamp); 
            $weekDay = date('w', $timestamp);
            $resultDays[$weekDay] = $resultDays[$weekDay] + 1;

            // increase startDate by 1 
            $startDate->modify('+1 day');
        }
        // print the result 
        return $resultDays[$no];
    }

    public function centerReport() {
        //check login
        autho::checkautho($this->hosturl);
        //check authoize
        autho::checkauthoize($this->controllname, $this->hosturl, $_SESSION['role_id']);
        //here will load the menu show
        //R::debug(true);

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

        $py_all_departs = R::findAll('awrole', 'visit_show = 1 ');
        $this->smarty->assign('py_all_departs', $py_all_departs);

        $doctors = R::getAll('select user_id , name  from awusers 
                where role_id in ( select id from awrole where visit_show = 1   )');
        $this->smarty->assign('doctors', $doctors);

        $result = $this->centerReportSearching($today, $today, 0, 0);
        $this->smarty->assign('result', $result);

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

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


        $fdate = filter_input(INPUT_POST, 'fdate');
        $todate = filter_input(INPUT_POST, 'todate');
        $deptId = filter_input(INPUT_POST, 'deptId');
        $doctorId = filter_input(INPUT_POST, 'repDoctors');

        $result = $this->centerReportSearching($fdate, $todate, $deptId, $doctorId);
        $this->smarty->assign('result', $result);
        $this->smarty->display('reports/share/center_report_search.html');
    }

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

        $fdate = filter_input(INPUT_POST, 'fdate');
        $todate = filter_input(INPUT_POST, 'todate');
        $deptId = filter_input(INPUT_POST, 'deptId');
        $doctorId = filter_input(INPUT_POST, 'repDoctors');

        $result = $this->centerReportSearching($fdate, $todate, $deptId, $doctorId);
        $this->smarty->assign('result', $result);
        $this->smarty->display('reports/share/center_report_print.html');
    }

    function centerReportSearching($fdate, $todate, $deptId, $doctorId) {
        
    }

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

    public function organizationReport() {
        //check login
        autho::checkautho($this->hosturl);
        //check authoize
        autho::checkauthoize($this->controllname, $this->hosturl, $_SESSION['role_id']);
        //here will load the menu show
        //R::debug(true);

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

        $py_all_departs = R::findAll('awrole', 'visit_show = 1 ');
        $this->smarty->assign('py_all_departs', $py_all_departs);

        $doctors = R::getAll('select user_id , name  from awusers 
                where role_id in ( select id from awrole where visit_show = 1   )');
        $this->smarty->assign('doctors', $doctors);

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

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

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

        $fdate = filter_input(INPUT_POST, 'fdate');
        $todate = filter_input(INPUT_POST, 'todate');
        $repDoctor = filter_input(INPUT_POST, 'repDoctors');
        $organization = filter_input(INPUT_POST, 'organization');

        $fdate = date('Y/m/d', strtotime($fdate));
        $todate = date('Y/m/d', strtotime($todate));

        $doctors = $this->orgsDetails($fdate, $todate, $repDoctor, $organization);
        $this->smarty->assign('doctors', $doctors);
        $this->smarty->display('reports/share/org_report_search.html');
    }

    function orgsDetails($fdate, $todate, $doctorId, $organizatioId) {
        $extDoc = '';
        $extOrg = '';

        if (!empty($doctorId) && $doctorId > 0) {
            $extDoc = ' and for_doctor =   ' . $doctorId;
        }

        if (!empty($organizatioId) && $organizatioId > 0) {
            $extOrg = ' and organization_id =   ' . $organizatioId;
        }


        $sql = 'select distinct  for_doctor as doctorId   , vu.name as doctorName  
                       ,   0  as patientDiscount    ,   0  as centerDiscount    ,   0  as net
                        from visits    
                         left  join awusers  vu            on vu.user_id  =  visits.for_doctor    
                         where visits.deleted = 0  and visitdate >= "' . $fdate . '"  
                      and visitdate <= "' . $todate . '"     ' . $extDoc . ' ' . $extOrg;

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

        $i = 0;
        foreach ($doctors as $doctor) {
            $sql = 'select COALESCE(SUM(center_discount_value),0)   as centerDiscount 
                     ,  COALESCE(SUM(organization_discount_value),0)   as organizationDiscount 
                        from visits    
                         where visits.deleted = 0  and visitdate >= "' . $fdate . '"  and  for_doctor = ' . $doctor['doctorId'] . '    
                    and visitdate <= "' . $todate . '"  ' . $extOrg;

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

            $doctors[$i]['centerDiscount'] = $visits['centerDiscount'];
            $doctors[$i]['organizationDiscount'] = $visits['organizationDiscount'];
            $doctors[$i]['patientDiscount'] = $visits['organizationDiscount'] - $visits['centerDiscount'];
            $i++;
        }

        return $doctors;
    }

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

    public function rfcChange() {
        //check login
        autho::checkautho($this->hosturl);
        //check authoize
//        autho::checkauthoize($this->controllname, $this->hosturl, $_SESSION['role_id']);
        //here will load the menu show
        //R::debug(true);

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

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

    public function rfcSearch() {
        $Id = filter_input(INPUT_POST, 'value');
        $hId = filter_input(INPUT_POST, 'hvalue');

        if (!empty($Id) && $Id > 0) {
            $patientId = $Id;
        } elseif (!empty($hId) && $hId > 0) {
            $patientId = $hId;
        }


        $sql = 'select id   , CASE    WHEN rfc = 1  THEN "Yes"     ELSE "No"    END as rfc 
                                     , CASE    WHEN  wrfc = 1  THEN "Yes"     ELSE "No"    END as wrfc 
                   from patients  
                         where id = ' . $patientId . '  ';

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

        $sql = 'select id , DATE(date_edit)  as dateEdit   , date_edit   ,  awusers.name as  userid_edit
                       , CASE    WHEN patients_updates.rfc = 1  THEN "Yes"     ELSE "No"    END as rfc 
                         , CASE    WHEN patients_updates.wrfc = 1  THEN "Yes"     ELSE "No"    END as wrfc 
                   from patients_updates  
                   join awusers   on    awusers.user_id= patients_updates.userid_edit
                         where patientid_edit = ' . $patientId . '    order by date_edit desc
                  ';
        $patient = R::getAll($sql);

        $res = array();
        if (count($patient) > 0) {
            $one = array();
            $one['date_edit'] = $patient[0]['dateEdit'];
            $one['wrfc'] = $patient[0]['wrfc'];
            $one['rfc'] = $patient[0]['rfc'];
            $one['new_wrfc'] = $latest['wrfc'];
            $one['new_rfc'] = $latest['rfc'];
            $one['userid_edit'] = $patient[0]['userid_edit'];

            array_push($res, $one);
            $i = 0;
            foreach ($patient as $value) {
                $i++;
                if (($patient[$i - 1]['wrfc'] != $patient[$i]['wrfc']) || ($patient[$i - 1]['rfc'] != $patient[$i]['rfc'])) {
                    $one = array();
                    $one['date_edit'] = $patient[$i]['dateEdit'];
                    $one['wrfc'] = $patient[$i]['wrfc'];
                    $one['rfc'] = $patient[$i]['rfc'];
                    $one['new_wrfc'] = $patient[$i - 1]['wrfc'];
                    $one['new_rfc'] = $patient[$i - 1]['rfc'];
                    $one['userid_edit'] = $patient[$i]['userid_edit'];
                    array_push($res, $one);
                }
            }
        }

        $this->smarty->assign('res', $res);
        $this->smarty->display('reports/rfc/rfcsearch.html');
    }

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

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


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

        $py_all_departs = R::findAll('awrole', 'visit_show = 1 ');
        $this->smarty->assign('py_all_departs', $py_all_departs);

        $doctors = R::getAll('select user_id , name  from awusers 
                where role_id in ( select id from awrole where visit_show = 1   )');
        $this->smarty->assign('doctors', $doctors);

        $recep_users = R::getAll('select * from awusers where role_id  = 3 ');
        $this->smarty->assign('recep_users', $recep_users);

        $this->smarty->assign('login_role', $_SESSION['role_id']);

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

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

        $fdate = filter_input(INPUT_POST, 'fdate');
        $todate = filter_input(INPUT_POST, 'todate');
        $patientId = filter_input(INPUT_POST, 'value');
        $hPatientId = filter_input(INPUT_POST, 'hvalue');
        $userId = filter_input(INPUT_POST, 'userId');
        $fordepartment = filter_input(INPUT_POST, 'fordepartment');
        $repDoctors = filter_input(INPUT_POST, 'repDoctors');

        $this->commonSearchPackage($fdate, $todate, $patientId, $hPatientId, $userId
                , $fordepartment, $repDoctors);

        $this->smarty->assign('login_role', $_SESSION['role_id']);
        $this->smarty->display('reports/package/financialsearch.html');
    }

    public function commonSearchPackage($fdate, $todate, $patientId = 0, $hPatientId = 0, $userId = 0
            , $fordepartment = 0, $repDoctors = 0) {

        $ext = '';
        if (!empty($userId) && $userId > 0) {
            $ext .= ' and visits.user_id =   ' . $userId;
        }

        if (!empty($fordepartment) && $fordepartment > 0) {
            $ext .= ' and visits.for_department =   ' . $fordepartment;
        }

        if (!empty($repDoctors) && $repDoctors > 0) {
            $ext .= ' and visits.for_doctor =   ' . $repDoctors;
        }

        if (!empty($patientId) && $patientId > 0) {
            $ext .= ' and visits.patientid = ' . $patientId;
        } elseif (!empty($hPatientId) && $hPatientId > 0) {
            $ext .= ' and visits.patientid = ' . $hPatientId;
        }

        $this->executeSqlPackage($fdate, $todate, $ext);
    }

    public function executeSqlPackage($fdate, $todate, $ext) {

        $sql = 'select distinct  visits.id  as visitId  ,  detectionvalue_visa , detectionvalue_cash
                    , totaldetectionvalue, restdetectionvalue , detectionid   ,   detections.units as detectUnits  
                    ,   detections.type as detectionType   
                 ,  detections.title as type, visits.patientid ,visits.user_id    , awrole.name    as deptName   , visits.discount
                  ,visits.for_husband , patients.wifename, patients.husdandname, patients.address   ,visits.for_department  as dept 
                     , patients.statusno  ,visits.visitdate    ,visits.notes    as vnotes   , visits.visitid as original_visitid    
                       , CASE    WHEN visits.rfc = 1  THEN "Yes"     ELSE "No"    END as rfc 
                    , cu.name as doctor , hcu.name as h_doctor   , vu.name as visitDoctor  , 0 as refund   , visits.printserial 
                     , visits.customer_add       , visits.child     , "" as visitHour   , "" as unitsUsed, "" as unitsRest 
                      , visits.refer_doctor   , visits.refer_doctor_tb    , visits.units as visitUnits    
                   from visits  
                               join  patients                         on  visits.patientid = patients.id 
                                 left   join  awrole                  on  visits.for_department = awrole.id
                               left join  detections                on  visits.detectionid = detections.id 
                                 left  join awusers  vu            on vu.user_id  =  visits.for_doctor    
                               left  join awusers  cu              on cu.user_id  =  patients.doctor1   
                                 left  join awusers  hcu          on hcu.user_id  =  patients.h_doctor1     
                                 left  join  investigationresults                  on  visits.id = investigationresults.visitid  
                         where visits.deleted = 0    and visits.customer_add != 1    
                         and visitdate >= "' . $fdate . '" and visitdate <= "' . $todate . '"   ' . $ext;


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

        $this->commonPackage($allData);
    }

    function commonPackage($allData) {
        $totalCost = 0;

        $totalCash = 0;
        $totalVisa = 0;
        $totalDept = 0;
        $totalDiscount = 0;

        $totalPaid = 0;

        $totalOldCash = 0;
        $totalOldVisa = 0;
        $totalOld = 0;

        $totalRefundCash = 0;
        $totalRefundVisa = 0;

        $total = 0;
        $totalC = 0;
        $totalV = 0;


        $pulseVisits = 0;
        $sessionVisits = 0;
        $pulseUnits = 0;
        $sessionUnits = 0;
        $pulseConsumed = 0;
        $sessionConsumed = 0;
        $pulseRest = 0;
        $sessionRest = 0;

        $new_allData = array();
        $i = 0;
        foreach ($allData as $data) {

            if ($data['refer_doctor'] > 0) {

                if ($data['refer_doctor_tb'] == 1) {
                    $refer = R::getRow('select name from awusers where user_id = ?', [$data['refer_doctor']]);
                    $data['refer'] = $refer['name'];
                } else {
                    $refer = R::load('refer', $data['refer_doctor']);
                    $data['refer'] = $refer->name;
                }
            } ##############
            else {
                $data['refer'] = "لا يوجد ";
            }


            $totalCost += $data['totaldetectionvalue'];

            $totalDept += $data['restdetectionvalue'];

            $totalDiscount += $data['discount'];

            //get user name
            $user_name = R::getRow('select * from awusers where user_id = ?', [$data['user_id']]);
            $data['insert_user'] = $user_name['name'];

            if ($data['detectionid'] == 999) {

                $originalVisit = R::load('visits', $data['original_visitid']);
                $det = R::load('detections', $originalVisit->detectionid);

                $data['type'] = 'دفع متبقى ';
                $data['type'] .= ' ( ' . $det->title . ' ) ';

                $totalOldVisa += $data['detectionvalue_visa'];
                $totalOldCash += $data['detectionvalue_cash'];

                $totalOld += $data['detectionvalue_visa'];
                $totalOld += $data['detectionvalue_cash'];
            } elseif ($data['detectionid'] == 9999) {

                $originalVisit = R::load('visits', $data['original_visitid']);
                $det = R::load('detections', $originalVisit->detectionid);

                $data['type'] = 'مرتجع العملاء  ';
                $data['type'] .= ' ( ' . $det->title . ' ) ';

                $totalRefundVisa += $data['detectionvalue_visa'];
                $totalRefundCash += $data['detectionvalue_cash'];
            } elseif ($data['detectionid'] == -88) {

                $originalVisit = R::load('visits', $data['original_visitid']);
                $det = R::load('detections', $originalVisit->detectionid);

                $data['type'] = 'متابعة   ';
                $data['type'] .= ' ( ' . $det->title . ' ) ';

                $totalRefundVisa += $data['detectionvalue_visa'];
                $totalRefundCash += $data['detectionvalue_cash'];
            } else {
                $totalVisa += $data['detectionvalue_visa'];
                $totalCash += $data['detectionvalue_cash'];

                $totalPaid += $data['detectionvalue_visa'];
                $totalPaid += $data['detectionvalue_cash'];

                $data['refund'] = $this->calcRefund($data['visitId']);

                $unitVisits = R::getRow('SELECT COALESCE(SUM(units),0)   as units_attend 
                                    FROM visits   where  patientid = ?    
                               and deleted != ? and detectionid = -88 and visitid = ?   ', [$data['patientid'], 1, $data['visitId']]);


                $data['unitsUsed'] = $data['visitUnits'] + $unitVisits['units_attend'];
                $data['unitsRest'] = $data['detectUnits'] - $data['unitsUsed'];

                switch ($data['detectionType']) {
                    case 1:
                        $pulseVisits++;
                        $pulseUnits += $data['detectUnits'];
                        $pulseConsumed += $data['unitsUsed'];
                        $pulseRest += $data['unitsRest'];
                        break;

                    case 2:
                        $sessionVisits++;
                        $sessionUnits += $data['detectUnits'];
                        $sessionConsumed += $data['unitsUsed'];
                        $sessionRest += $data['unitsRest'];
                        break;
                }
            }

            ## 12  investigations dept     ,  5   rays dept 

            if ($data['dept'] == 5 && $data['detectionid'] != 999 && $data['detectionid'] != 9999) {

                $type = $this->raysDetails($data['visitId']);
                $data['type'] = $type;
            }

            if ($data['dept'] == 12 && $data['detectionid'] != 999 && $data['detectionid'] != 9999) {

                $type = $this->invsDetails($data['visitId']);
                $data['type'] = $type;
            }

            $reserve = R::findOne('visit_hours', ' visit_id = ? and deleted = 0 ', [$data['visitId']]);
            if (count($reserve) > 0) {
                $data['visitHour'] = date('h:i A', strtotime($reserve->visit_hour));
            }

            array_push($new_allData, $data);

            $i++;
        }

        $total = $totalPaid + $totalOld;
        $totalC = $totalCash + $totalOldCash - $totalRefundCash;
        $totalV = $totalVisa + $totalOldVisa - $totalRefundVisa;

        $this->smarty->assign('totalC', $totalC);
        $this->smarty->assign('totalV', $totalV);
        $this->smarty->assign('totalCost', $totalCost);
        $this->smarty->assign('totalCash', $totalCash);
        $this->smarty->assign('totalVisa', $totalVisa);
        $this->smarty->assign('totalDept', $totalDept);
        $this->smarty->assign('totalPaid', $totalPaid);
        $this->smarty->assign('totalOldCash', $totalOldCash);
        $this->smarty->assign('totalOldVisa', $totalOldVisa);
        $this->smarty->assign('totalOld', $totalOld);
        $this->smarty->assign('totalDiscount', $totalDiscount);
        $this->smarty->assign('totalRefundVisa', $totalRefundVisa);
        $this->smarty->assign('totalRefundCash', $totalRefundCash);
        $this->smarty->assign('total', $total);

        $this->smarty->assign('pulseVisits', $pulseVisits);
        $this->smarty->assign('sessionVisits', $sessionVisits);
        $this->smarty->assign('pulseUnits', $pulseUnits);
        $this->smarty->assign('sessionUnits', $sessionUnits);
        $this->smarty->assign('pulseConsumed', $pulseConsumed);
        $this->smarty->assign('sessionConsumed', $sessionConsumed);
        $this->smarty->assign('pulseRest', $pulseRest);
        $this->smarty->assign('sessionRest', $sessionRest);

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

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

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


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

        $py_all_departs = R::findAll('awrole', 'visit_show = 1 ');
        $this->smarty->assign('py_all_departs', $py_all_departs);

        $doctors = R::getAll('select user_id , name  from awusers 
                where role_id in ( select id from awrole where visit_show = 1   )');
        $this->smarty->assign('doctors', $doctors);

        $recep_users = R::getAll('select * from awusers where role_id  = 3 ');
        $this->smarty->assign('recep_users', $recep_users);

        $this->smarty->assign('login_role', $_SESSION['role_id']);

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

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

        $fdate = filter_input(INPUT_POST, 'fdate');
        $todate = filter_input(INPUT_POST, 'todate');
        $repDoctors = filter_input(INPUT_POST, 'repDoctors');

        $this->commonSearchDoctors($fdate, $todate, $repDoctors);

        $this->smarty->assign('login_role', $_SESSION['role_id']);
        $this->smarty->display('reports/doctors/financialsearch.html');
    }

    public function commonSearchDoctors($fdate, $todate, $repDoctors) {

        $sql = 'select distinct  visits.id  as visitId  
                    , totaldetectionvalue, restdetectionvalue , detectionid   ,   detections.units as detectUnits  
                    ,   detections.type as detectionType     ,  detections.title as det_type, visits.patientid    
                        , CASE    WHEN detections.type = 1  THEN "نبضات"  
                                WHEN detections.type = 2  THEN "جلسات"      ELSE "عادى"    END as unitType       ,visits.doctor_percent
                  ,visits.for_husband , patients.wifename, patients.husdandname, patients.address  
                     , patients.statusno  ,visits.visitdate    ,visits.notes    as vnotes   , visits.visitid as original_visitid    
                      , vu.name as visitDoctor  , visits.customer_add       , visits.child    , visits.printserial  
                      , visits.refer_doctor   , visits.refer_doctor_tb    , visits.units as visitUnits    
                   from visits  
                               join  patients                         on  visits.patientid = patients.id 
                                 left   join  awrole                  on  visits.for_department = awrole.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.customer_add != 1    
                         and visits.detectionid   != 999 and visits.detectionid   != 9999 
                         and visitdate >= "' . $fdate . '" and visitdate <= "' . $todate . '"  
                     and visits.for_doctor =   ' . $repDoctors;


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

        $this->commonDoctors($allData);
    }

    function commonDoctors($allData) {
        $new_allData = array();

        $drShare = 0;
        $visitUnits = 0;
        $visitCost = 0;
        foreach ($allData as $data) {

            if ($data['detectionid'] == -88) {
                $originalVisit = R::load('visits', $data['original_visitid']);
                $det = R::load('detections', $originalVisit->detectionid);

                $data['det_type'] = 'متابعة   ';
                $data['det_type'] .= ' ( ' . $det->title . ' ) ';

                switch ($det->type) {
                    case 1:
                        $data['unitType'] = 'نبضات   ';
                        break;

                    case 2:
                        $data['unitType'] = 'جلسات   ';
                        break;
                }
            } else {
                $det = R::load('detections', $data['detectionid']);
            }

            $percent = $data['doctor_percent'];

            $unitPrice = $det->detectionval / $det->units;


            $data['unitPrice'] = round($unitPrice, 2);
            $data['percent'] = $percent;
            $data['visitCost'] = round($data['visitUnits'] * $unitPrice, 2);
            $data['drCost'] = round($data['visitUnits'] * $unitPrice * ($percent / 100), 2);

            $drShare += $data['drCost'];
            $visitCost += $data['visitCost'];
            $visitUnits += $data['visitUnits'];
            array_push($new_allData, $data);
        }

        $this->smarty->assign('drShare', round($drShare));
        $this->smarty->assign('visitUnits', $visitUnits);
        $this->smarty->assign('visitCost', round($visitCost));
        $this->smarty->assign('allData', $new_allData);
    }

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

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

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

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

    public function visitSearch() {
        $Id = filter_input(INPUT_POST, 'value');
        $hId = filter_input(INPUT_POST, 'hvalue');

        if (!empty($Id) && $Id > 0) {
            $patientId = $Id;
        } elseif (!empty($hId) && $hId > 0) {
            $patientId = $hId;
        }

        $sql = '(select distinct (patientid_edit) as visitId , visitdate   , 0 as updatesCount , deleted
                         from visits_updates  
                         where patientid = ' . $patientId . '   )   
                             
                    union 
                    
                 (   select id   as visitId  , visitdate       , 0 as updatesCount   , deleted
                         from visits  
                         where patientid = ' . $patientId . '    )
                             
                  order by visitdate desc , visitId asc 
                 ';

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

        $i = 0;
        foreach ($visits as $visit) {
            $visitsUpdates = R::findAll('visits_updates', ' patientid_edit = ? ', [$visit['visitId']]);
            $visits[$i]['updatesCount'] = count($visitsUpdates);
            if ($visit['deleted'] == 1) {
                $visits[$i]['updatesCount'] = $visits[$i]['updatesCount'] + 1;
            }
            $i++;
        }

        $this->smarty->assign('visits', $visits);
        $this->smarty->display('reports/visit/visits.html');
    }

    public function visitUpdates() {

        $visitId = filter_input(INPUT_POST, 'visitId');

        $sql = 'select   visits_updates.patientid , visits_updates.visitdate  , visits_updates.notes 
                                 ,    visits_updates.date_edit    ,    visits_updates.for_husband   
                                , patients.wifename  , patients.husdandname  , patients.statusno  
                                ,  awusers.name  as usName    ,  edit.name  as editUsName   
                         from visits_updates   
                          join  patients                             on  visits_updates.patientid = patients.id  
                              left  join awusers                     on awusers.user_id  =  visits_updates.user_id    
                                  left  join awusers edit                on edit.user_id  =  visits_updates.userid_edit    
                         where visits_updates.patientid_edit = ' . $visitId . '   
                          order by visits_updates.id asc , visits_updates.date_edit asc        
                 ';

        $visitsUpdates = R::getAll($sql);
        $vCount = count($visitsUpdates);

        $i = 0;
        foreach ($visitsUpdates as $value) {
            $i++;
            if ($i < $vCount) {
                $visitsUpdates[$i]['editDate'] = $visitsUpdates[$i - 1]['date_edit'];
                $visitsUpdates[$i]['userName'] = $visitsUpdates[$i - 1]['editUsName'];
            }

            if ($i == 1) {
                $visitsUpdates[0]['userName'] = $visitsUpdates[0]['usName'];
            }
        }


        $sql = 'select   visits.patientid , visits.visitdate ,  visits.user_id   ,    visits.for_husband     
                                , patients.wifename, patients.husdandname  , patients.statusno , visits.notes 
                                , visits.deleted ,  awusers.name  as deleteUsName   , visits.date_delete  
                         from visits  
                          join  patients                                  on  visits.patientid = patients.id  
                         left  join awusers                 on awusers.user_id  =  visits.user_delete    
                         where visits.id = ' . $visitId . '         
                 ';

        $lastRecord = R::getRow($sql);
        $lastRecord['editDate'] = $visitsUpdates[$i - 1]['date_edit'];
        $lastRecord['userName'] = $visitsUpdates[$i - 1]['editUsName'];


        $this->smarty->assign('visitsUpdates', $visitsUpdates);
        $this->smarty->assign('lastRecord', $lastRecord);
        $this->smarty->display('reports/visit/visitsearch.html');
    }

    // at the end of class call
    public function __destruct() {
        //close connection
        R::close();
    }

}
