<?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 $hosturl_ERP;
   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;
      $this->hosturl_ERP = $hosturl_ERP;

      //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("erp_common.php");  ## added by heba 
      require_once("branch_common.php");
      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)));

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

      if ($_SESSION['role_id'] == 12) {
         $py_all_departs = R::findAll('awrole', 'visit_show = 1 and id = 12');
      } else {
         $py_all_departs = R::findAll('awrole', 'visit_show = 1  ||  id = 12 ');
      }
      $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    financial_user   = 1 ');
      $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);

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

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

   function branchSql($branchesActivate, $branch_id) {
      $branchSql = ' ';
      if ($branchesActivate == 1 && ($branch_id == -1 || $branch_id > 0)) {
         $branchSql = '  and visits.branch_id  =   ' . $branch_id . '   ';
      }

      $this->smarty->assign('selectedBranch', $branch_id);
      $this->smarty->assign('branchesActivate', $branchesActivate);
      return $branchSql;
   }

   function billBranchSql($branchesActivate, $branch_id) {
      $branchSql = ' ';
      if ($branchesActivate == 1 && ($branch_id == -1 || $branch_id > 0)) {
         $branchSql = '  and bill_paying.branch_id  =   ' . $branch_id . '   ';
      }
      return $branchSql;
   }

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

      if ($_SESSION['role_id'] == 12) {
         $fordepartment = 12;
      } else {
         $fordepartment = filter_input(INPUT_POST, 'fordepartment');
      }

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

      $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');
      $netRes = filter_input(INPUT_POST, 'netRes');
      $branch_id = filter_input(INPUT_POST, 'branch_id');

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

      $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');
      $netRes = filter_input(INPUT_POST, 'netRes');
      $branch_id = filter_input(INPUT_POST, 'branch_id');

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

      $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, $netRes = 0, $branch_id = '') {
      $billShow = 1;
      $ext = '';

      $role = R::load('awrole', $_SESSION['role_id']);
      switch ($role->financial_visits) {
         case 1:
            $billShow = 0;
            $ext .= ' and visits.for_department =   ' . $_SESSION['role_id'];
            break;
         case 2:
            $billShow = 0;
            $ext .= ' and visits.for_doctor =   ' . $_SESSION['user_id'];
            break;
      }

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

      $programesetting = R::findOne('programesetting');
      $ext .= $this->branchSql($programesetting->branches, $branch_id);

      $billBranchSql = $this->billBranchSql($programesetting->branches, $branch_id);

      if (!empty($netRes) && $netRes == 1) {
         $ext .= ' and visits.detectionid not in ( 999 , 9999 )  ';
      }


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

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

      if (!empty($patientId) && $patientId > 0) {
         $ext .= ' and visits.patientid = ' . $patientId;
         $this->getrestvaluesP($patientId);
      } elseif (!empty($hPatientId) && $hPatientId > 0) {
         $ext .= ' and visits.patientid = ' . $hPatientId;
         $this->getrestvaluesP($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;
         }
      }

      if (!empty($fordepartment) && $fordepartment > 0) {
         $ext .= ' and visits.for_department =   ' . $fordepartment;
         $billsSql = '';
         $sql = $this->visitSql($fdate, $todate, $ext, $billsSql);
      } elseif ($fordepartment == -5) {
         $sql = $this->billsPayingSql($fdate, $todate, $billShow, $patientId, $hPatientId, $userId, $billBranchSql);
      } else {
         if (!empty($repDoctors) && $repDoctors > 0) {
            $billShow = 0;
         }
         $billsSql = $this->billsPayingSql($fdate, $todate, $billShow, $patientId, $hPatientId, $userId, $billBranchSql);
         if (!empty($billsSql) && $billsSql != '') {
            $billsSql = '  union all    ' . $billsSql;
         }
         $sql = $this->visitSql($fdate, $todate, $ext, $billsSql);
      }

      //print_r($sql);
//      r::debug(true, 2);
      $allData = R::getAll($sql);
      $this->common($allData);
   }

   function billsPayingSql($fdate, $todate, $billShow, $patientId = 0, $hPatientId = 0, $userId = 0, $billBranchSql = '') {
      $ext = '';
      $billsSql = '';

      if ($billShow == 1) {

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

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

         $billsSql = ' 
                       SELECT    0 AS visitId , bill_paying.branch_id ,  ""  as branch_name      , 
                    detectionvalue_visa , 
                    detectionvalue_cash,
                    totaldetectionvalue,
                      0 as restdetectionvalue,
                     0 as detectionid,
                    "صيدلية " AS type,
                    bill_paying.type  as billType  , 
                    patient_id as patientid,
                    bill_paying.user_id , 
                   "الصيدلية"  AS deptName,
                    discount,
                    ""  AS for_husband,
                    patients.wifename,
                    patients.husdandname,
                     patients.wifenationalid,
                    patients.husbandnationalid,
                    patients.address,
                    ""  AS   dept,
                    patients.statusno,
                    pay_date as visitdate , 
                     bill_paying.notes  COLLATE utf8mb4_unicode_ci     AS   vnotes ,
                    0 AS original_visitid,
                   ""  AS rfc,
                    cu.name AS doctor,
                    hcu.name AS h_doctor,
                    "" AS visitDoctor,
                    0 AS refund,
                    printserial,
                    0 as customer_add,
                    "" AS child,
                    "" AS visitHour,
                    0  as drFees ,
                      0  as assistant_salary ,
                        0  as refer_salary ,
                    "" AS refer_doctor,
                    "" AS  refer_doctor_tb , 
                  0 AS contract_price 
                    ,  0 as organization_id   ,  ""  as orgName  
                  
                    FROM   bill_paying
                        JOIN patients                        ON        bill_paying.patient_id = patients.id
                        LEFT JOIN awusers cu       ON        cu.user_id = patients.doctor1
                        LEFT JOIN awusers hcu     ON        hcu.user_id = patients.h_doctor1
 
                       WHERE  bill_paying.deleted = 0 AND  
                         pay_date >= "' . $fdate . '" AND pay_date <= "' . $todate . '"   ' . $ext . '    ' . $billBranchSql . '    ' . $billsSql;
      }
      return $billsSql;
   }

   public function visitSql($fdate, $todate, $ext, $billsSql) {
      $sql = 'SELECT DISTINCT
                    visits.id AS visitId,
                    visits.branch_id  ,  ""  as branch_name      , 
                    detectionvalue_visa,
                    detectionvalue_cash,
                    totaldetectionvalue,
                    restdetectionvalue,
                    detectionid,
                    detections.title AS type,
                    0  as billType , 
                    visits.patientid,
                    visits.user_id,
                    awrole.name AS deptName,
                    visits.discount,
                    visits.for_husband,
                    patients.wifename,
                    patients.husdandname,
                     patients.wifenationalid,
                    patients.husbandnationalid,
                    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.dr_salary  as drFees ,
                      visits.assistant_salary  as assistant_salary ,
                        visits.refer_salary  as refer_salary ,
                    visits.refer_doctor,
                    visits.refer_doctor_tb ,        
                  visits.contract_price   
                  ,  visits.organization_id   ,  ""  as orgName  

                    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 . '  ' . $billsSql;

      return $sql;
   }

   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;
   }

   private 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;
      $totalDrFees = 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'];
            }
         }
         //    print_r($datum['user_id'] . ' - ');

         if ($datum['user_id'] > 0) {
            $insert_users[] = $datum['user_id'];
         } else {
            $insert_users[] = Null;
         }
         $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)) . ')');

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

      $programesetting = R::findOne('programesetting');

      $showBranchCol = showBranchCol($programesetting->branches);
      $this->smarty->assign('showBranchCol', $showBranchCol);

      foreach ($allData as $data) {

         ### 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 !== null) {
                  $data['refer'] = $refer_doctors_data[$refer_index]['name'];
               }
            } else {
               $refer_index = $this->searchForId($data['refer_doctor'], $refers_data);
               if ($refer_index !== null) {
                  $data['refer'] = $refers_data[$refer_index]['name'];
               }
            }
         } else {
            $data['refer'] = "لا يوجد ";
         }



         if ($data['organization_id'] > 0 && $programesetting->organizations != 1) {
            $visitOrg = R::load('organizations', $data['organization_id']);
            $data['orgName'] = $visitOrg->organization_name;
         }



         $totalCost += $data['totaldetectionvalue'];
         $totalDept += $data['restdetectionvalue'];
         $totalDiscount += $data['discount'];
         $totalDrFees += $data['drFees'];

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

         if ($data['detectionid'] === '999') {
            ### new way
            $original_visit_index = $this->searchForId($data['original_visitid'], $original_visits_data);
            if ($original_visit_index !== null) {
               $detection_id = $original_visits_data[$original_visit_index]['detectionid'];
            }
            ### new way
            $detection_index = $this->searchForId($detection_id, $detections_data);
            $title = '';
            if ($detection_index !== null) {
               $title = $detections_data[$detection_index]['title'];
            }
            $data['type'] = 'دفع متبقي ';
            if ($data['dept'] != '12' && $data['dept'] != '5') {
               $data['type'] .= ' ( ' . $title . ' ) ';
            } else {
               $data['type'] .= ' ( تحاليل طبية  ) ';
            }
            //  $data['type'] .= ' ( ' . $title . ' ) ';

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

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

            ### new way
            $original_visit_index = $this->searchForId($data['original_visitid'], $original_visits_data);
            if ($original_visit_index !== null) {
               $detection_id = $original_visits_data[$original_visit_index]['detectionid'];
            }

            ### new way
            $detection_index = $this->searchForId($detection_id, $detections_data);
            $title = '';
            if ($detection_index !== null) {
               $title = $detections_data[$detection_index]['title'];
            }

            $data['type'] = 'مرتجع العملاء  ';
            if ($data['dept'] != '12' && $data['dept'] != '5') {
               $data['type'] .= ' ( ' . $title . ' ) ';
            } else {
               $data['type'] .= ' ( تحاليل طبية  ) ';
            }
            //   $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'];

            ### new way
            $refund_index = $this->searchForId($data['visitId'], $cal_refund_data, 'visitid');
            if ($refund_index !== null) {
               $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') {
            ### 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') {
            ### 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;
         }


         ############################
         /*
           if ($data['dept'] === '5' && ( $data['detectionid'] == '999' || $data['detectionid'] == '9999')) {
           ### new way
           $ray_details_indexes = $this->searchForIds($data['original_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')) {
           ### new way
           $invest_details_indexes = $this->searchForIds($data['original_visitid'], $invest_details_data, 'visitid');
           $type = $this->investDetailsNew($invest_details_indexes, $invest_details_data);
           $data['type'] .= ' ( ' . $type . ' ) ';
           }
          */
         ###############################



         if ($data['billType'] == 1) {
            $data['type'] = 'مرتجع العملاء  ';
            $data['type'] .= ' ( صيدلية) ';

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

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

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

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

         if ($showBranchCol == 1 && $data['branch_id'] > 0) {
            $branch = R::load('branches', $data['branch_id']);
            $data['branch_name'] = $branch->name;
         }

         $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('totalDrFees', $totalDrFees);

      $this->smarty->assign('share', $programesetting->system_type);
      $this->smarty->assign('referDrShow', $programesetting->refer_doctor);
      $this->smarty->assign('assDrShow', $programesetting->assistant_doctor);

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

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

   private 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;
   }

   private 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;
   }

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

      $programesetting = R::findOne('programesetting');

      if (empty($programesetting->erpdb)) {
         $this->getrestvaluesPRoyal($patientId);
      } else {
         $this->getrestvaluesPErp($patientId);
      }
   }

   public function getrestvaluesPErp($patientId) {
      $erpApiLink = 'clientdept&id=0&royalid=' . $patientId;
      $result = orderFromErp($erpApiLink, $this->hosturl_ERP);
      $this->smarty->assign('patTotalDept', $result);
      $this->smarty->assign('patTotalDeptId', $patientId);
      //   echo $result;
   }

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

      $visits = array();
      $totalDept = 0;

      $lastVisit = R::getAll('SELECT visits.id as visitid , visits.restdetectionvalue as rest , visits.visitdate
                                                    , detections.title  as  detectionName
                                                    FROM visits
                                                     left join  detections                on  visits.detectionid = detections.id
                                                    where  visits.patientid = ?   and  visits.restdetectionvalue >0
                                                               and visits.deleted != ?   ', [$patientId, 1]);
      $i = 0;
      foreach ($lastVisit as $data) {
         $payVisits = R::getRow('SELECT COALESCE(SUM(detectionvalue_cash),0)   as paid_cash
                                                                 , COALESCE(SUM(detectionvalue_visa),0)   as paid_visa
                                                                  , COALESCE(SUM(discount),0)   as paid_discount
                                                                 , 0 as paid
                                    FROM visits   where  patientid = ?
                               and deleted != ? and detectionid = 999 and visitid = ?   ', [$patientId, 1, $data['visitid']]);

#               ## what pt paid after  creating visit
         $payVisits['paid'] = $payVisits['paid_cash'] + $payVisits['paid_visa'];
         if ($data['rest'] > ($payVisits['paid'] + $payVisits['paid_discount'])) {
            $totalDept = $totalDept + $data['rest'] - $payVisits['paid'] - $payVisits['paid_discount'];
         }
      }
      $this->smarty->assign('patTotalDept', $totalDept);
      $this->smarty->assign('patTotalDeptId', $patientId);
      //c echo $totalDept;
   }

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

   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  ||  id = 12 ');
      $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));

      $programesetting = R::findOne('programesetting');
      if ($programesetting->fin_system == 1) {
         $days = $this->daysDetailsPercent($fdate, $todate, $repDoctor);
         $this->smarty->assign('days', $days);
         $this->smarty->display('reports/share/dr_report_search_percent.html');
      } else {
         $days = $this->daysDetails($fdate, $todate, $repDoctor);
         $this->smarty->assign('days', $days);
         $this->smarty->display('reports/share/dr_report_search.html');
      }
   }

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

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

      $date = filter_input(INPUT_POST, 'date');
      $doctorId = filter_input(INPUT_POST, 'doctorId');
      $value = (filter_input(INPUT_POST, 'value') > 0) ? filter_input(INPUT_POST, 'value') : 0;

      try {
         R::exec(' update visits set dr_percent_receive =   ' . $value . '  where deleted = 0  and  for_doctor =  ' . $doctorId . '  
                 and visitdate = "' . $date . '"  ');
      } catch (Exception $ex) {
         echo -1;
      }
   }

   private function getDayNameFnAr($date) {
      $timestamp = strtotime($date);

      $day = date('w', $timestamp);

      switch ($day) {
         case 0:
            $name = 'الأحد  ';
            break;
         case 1:
            $name = ' الإثنين';
            break;
         case 2:
            $name = ' الثلاثاء';
            break;
         case 3:
            $name = 'الأربعاء ';
            break;
         case 4:
            $name = 'الخميس  ';
            break;
         case 5:
            $name = 'الجمعة ';
            break;
         case 6:
            $name = 'السبت ';
            break;
      }

      return $name;
   }

   function daysDetailsPercent($fdate, $todate, $doctorId) {

      $sql = 'select    distinct  visitdate  from visits  
                   where deleted = 0  and visitdate >= "' . $fdate . '" and visitdate <= "' . $todate . '"   
                              and visits.for_doctor =   ' . $doctorId;

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

      $i = 0;
      foreach ($days as $day) {
         $days[$i]['dayName'] = $this->getDayNameFnAr($day['visitdate']);
         $dayVisits = $this->dateVisits($day['visitdate']
                 , $doctorId);

         $days[$i]['visitsCount'] = $dayVisits['vCount'];
         $days[$i]['total'] = $dayVisits['total'];
         $days[$i]['net'] = $dayVisits['total'];
         $days[$i]['dr_percent'] = $dayVisits['dr_percent'];
         $days[$i]['dr_percent_receive'] = $dayVisits['dr_percent_receive'];
         $days[$i]['dr_percent_total'] = $dayVisits['dr_percent_total'];

         $vCount += $dayVisits['vCount'];
         $vCost += $dayVisits['total'];
         $drCost += $dayVisits['dr_percent_total'];
         $i++;
      }


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

   function dateVisits($date, $doctorId) {

      $sql = 'select    detectionvalue_visa , detectionvalue_cash , totaldetectionvalue 
                            , restdetectionvalue , discount   , center_discount_value
                       , id  as visitId  , visitid as original_visitid     , detectionid , dr_percent   , dr_percent_receive 
                    from visits  
                   where deleted = 0  and visitdate = "' . $date . '"      and visits.for_doctor =   ' . $doctorId;

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

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

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

      $vCount = 0;
      $vCost = 0;
      $drCost = 0;
      $net = 0;

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

         $visits = $this->dayVisits($dayDet['dayNameEn'], $fdate, $todate, $doctorId);

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

         array_push($days, $day);
         $vCount += $visits['vCount'];
         $vCost += $visits['total'];
         $drCost += $visits['dr_percent_total'];
      }


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

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

   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 , dr_percent   , dr_percent_receive 
                    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;

      $dr_percent = 0;
      $dr_percent_total = 0;
      $dr_percent_receive = 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'];
         }

         if ($data['dr_percent_receive'] == 1) {
            $dr_percent_receive = 1;
         }

         if ($data['dr_percent'] > 0 && $dr_percent == 0) {
            $dr_percent = $data['dr_percent'];
         }

         $i++;
      }


      $dr_percent_total = ( $totalCost * $dr_percent ) / 100;

      $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
          , 'dr_percent' => $dr_percent, 'dr_percent_total' => $dr_percent_total, 'dr_percent_receive' => $dr_percent_receive);
      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  ||  id = 12 ');
      $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  ||  id = 12 ');
      $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)));

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

      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');
      $field = filter_input(INPUT_POST, 'field');
      $fieldName = filter_input(INPUT_POST, 'fieldName');

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


      switch ($field) {
         case 'rfc':
            $this->rfcSearchEx($patientId);
            $this->smarty->display('reports/rfc/rfcsearch.html');
            break;

         default:
            $this->fieldSearch($patientId, $field);
            $this->smarty->assign('fieldName', $fieldName);
            $this->smarty->display('reports/rfc/fieldsearch.html');
            break;
      }
   }

   public function rfcSearchEx($patientId) {
      $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 patients_updates.id , patients_updates.date_edit  as dateEdit   
            , patients_updates.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 patients_updates.patientid_edit = ' . $patientId . '    order by patients_updates.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);
   }

   public function fieldSearch($patientId, $field) {
      $sql = 'select id   , ' . $field . '  from patients  where id = ' . $patientId . '  ';
      $latest = R::getRow($sql);

      $sql = 'select patients_updates.id , patients_updates.date_edit  as dateEdit   
            , patients_updates.date_edit   ,  awusers.name as  userid_edit  , patients_updates.' . $field . '
                   from patients_updates  
                   join awusers   on    awusers.user_id= patients_updates.userid_edit
                         where patients_updates.patientid_edit = ' . $patientId . '    order by patients_updates.date_edit desc ';
      $patient = R::getAll($sql);

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

         if ($field == 'branch_id') {
            $branch = R::load('branches', $patient[0][$field]);
            $one['oldValue'] = $branch->name;
            $branch = R::load('branches', $latest[$field]);
            $one['newValue'] = $branch->name;
         } else {
            $one['oldValue'] = $patient[0][$field];
            $one['newValue'] = $latest[$field];
         }

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

               if ($field == 'branch_id') {
                  $branch = R::load('branches', $patient[$i][$field]);
                  $one['oldValue'] = $branch->name;
                  $branch = R::load('branches', $patient[$i - 1][$field]);
                  $one['newValue'] = $branch->name;
               } else {
                  $one['oldValue'] = $patient[$i][$field];
                  $one['newValue'] = $patient[$i - 1][$field];
               }

               array_push($res, $one);
            }
         }
      }

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

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

   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  ||  id = 12 ');
      $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  ||  id = 12 ');
      $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)));

      $users = R::getAll('select awusers.user_id, awusers.name from awusers
        join awrole on awusers.role_id = awrole.id
        where awusers.deleted != 1 and awrole.is_doctor != 1');
      $this->smarty->assign('users', $users);

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

      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');
      $dateFrom = filter_input(INPUT_POST, 'findate');
      $dateTo = filter_input(INPUT_POST, 'todate');
      $userId = (filter_input(INPUT_POST, 'userId') > 0) ? filter_input(INPUT_POST, 'userId') : 0;
      $branch_id = (filter_input(INPUT_POST, 'branch_id') > 0) ? filter_input(INPUT_POST, 'branch_id') : 0;

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

      $patExt = ' ';
      $addUser = ' ';
      $entryDate = ' ';
      if (!empty($patientId) && $patientId > 0) {
         $patExt .= ' and patientid = ' . $patientId;

         $patient = R::load('patients', $patientId);
         $entryDate = $patient->entrydate;
         if ($patient->userid > 0) {
            $user_name = R::getRow('select * from awusers where user_id = ?', [$patient->userid]);
            $addUser = $user_name['name'];
         }
      }
      $this->smarty->assign('addUser', $addUser);
      $this->smarty->assign('entryDate', $entryDate);

      $userVExt = ' ';
      $userVUpExt = ' ';

      if (!empty($userId) && $userId > 0) {
         $userVExt .= ' and user_id = ' . $userId;
         $userVUpExt .= ' and userid_edit = ' . $userId;
      }

      $branchExt = ' ';
      if (!empty($branch_id) && $branch_id > 0) {
         $branchExt .= ' and branch_id = ' . $branch_id;
      }

      $sql = '(select distinct (patientid_edit) as visitId, visitdate, 0 as updatesCount, deleted 
            , create_date , detectionid , visitid as orgId 
        from visits_updates
        where visitdate >= "' . $dateFrom . '"    ' . $branchExt . ' 
        AND visitdate <= "' . $dateTo . '" ' . $patExt . ' ' . $userVUpExt . ' )

        union

        ( select id as visitId, visitdate, 0 as updatesCount, deleted  , create_date  , detectionid , visitid as orgId 
        from visits
        where visitdate >= "' . $dateFrom . '" AND visitdate <= "' . $dateTo . '"   ' . $branchExt . '  ' . $patExt . ' ' . $userVExt . ' )

        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;
         }

         if ($visit['detectionid'] == 9999) {
            $originalVisit = R::load('visits', $visit['orgId']);
            $detection = R::load('detections', $originalVisit->detectionid);
            $visits[$i]['detectionName'] = 'مرتجع العملاء';
            $visits[$i]['detectionName'] .= ' ( ' . $detection->title . ' ) ';
         } elseif ($visit['detectionid'] == 999) {
            $originalVisit = R::load('visits', $visit['orgId']);
            $detection = R::load('detections', $originalVisit->detectionid);
            $visits[$i]['detectionName'] = 'دفع متبقى ';
            $visits[$i]['detectionName'] .= ' ( ' . $detection->title . ' ) ';
         } elseif ($visit['detectionid'] == -88) {
            $originalVisit = R::load('visits', $visit['orgId']);
            $detection = R::load('detections', $originalVisit->detectionid);
            $visits[$i]['detectionName'] = 'متابعة ';
            $visits[$i]['detectionName'] .= ' ( ' . $detection->title . ' ) ';
         } else {
            $detection = R::load('detections', $visit['detectionid']);
            $visits[$i]['detectionName'] = $detection->title;
         }
         $i++;
      }

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

   public function visitUpdates() {
//check login
      autho::checkautho($this->hosturl);
      $visitId = filter_input(INPUT_POST, 'visitId');

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

      $sql = 'select visits_updates.patientid, visits_updates.visitdate, visits_updates.notes
        , visits_updates.date_edit, visits_updates.for_husband , visits_updates.branch_id
        , patients.wifename, patients.husdandname, patients.statusno
        , awusers.name as usName, edit.name as editUsName 
        , visits_updates.create_date
        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) {

         if ($showBranchCol == 1 && $value['branch_id'] > 0) {
            $branch = R::load('branches', $value['branch_id']);
            $visitsUpdates[$i]['branchName'] = $branch->name;
         }


         $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'];
            $visitsUpdates[0]['editDate'] = $visitsUpdates[0]['create_date'];
         }
      }


      $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  , visits.branch_id
        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'];

      if ($showBranchCol == 1 && $lastRecord['branch_id'] > 0) {
         $branch = R::load('branches', $lastRecord['branch_id']);
         $lastRecord['branchName'] = $branch->name;
      }


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

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

   public function erpFinancial() {
      //check login
      autho::checkautho($this->hosturl);
      //R::debug(true);

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

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

      if ($_SESSION['role_id'] == 12) {
         $py_all_departs = R::findAll('awrole', 'visit_show = 1 and id = 12');
      } else {
         $py_all_departs = R::findAll('awrole', 'visit_show = 1  ||  id = 12 ');
      }
      $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    financial_user   = 1 ');
      $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);

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

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

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

      if ($_SESSION['role_id'] == 12) {
         $fordepartment = 12;
      } else {
         $fordepartment = filter_input(INPUT_POST, 'fordepartment');
      }

      $fdate = filter_input(INPUT_POST, 'fdate');
      $todate = filter_input(INPUT_POST, 'todate');
      $repDoctors = filter_input(INPUT_POST, 'repDoctors');
      $labType = filter_input(INPUT_POST, 'labType');
      $referDrId = filter_input(INPUT_POST, 'referDrId');
      $branch_id = (filter_input(INPUT_POST, 'branch_id') > 0) ? filter_input(INPUT_POST, 'branch_id') : 0;
      $detection = (filter_input(INPUT_POST, 'detection') > 0) ? filter_input(INPUT_POST, 'detection') : 0;
      $offerId = (filter_input(INPUT_POST, 'offerId') > 0) ? filter_input(INPUT_POST, 'offerId') : 0;
      $userId = (filter_input(INPUT_POST, 'userId') > 0) ? filter_input(INPUT_POST, 'userId') : 0;
      $patientId = (filter_input(INPUT_POST, 'value') > 0) ? filter_input(INPUT_POST, 'value') : 0;
      $hPatientId = (filter_input(INPUT_POST, 'hvalue') > 0) ? filter_input(INPUT_POST, 'hvalue') : 0;

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

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

   public function commonErpSearch($fdate, $todate, $patientId = 0, $hPatientId = 0, $userId = 0, $fordepartment = 0
           , $repDoctors = 0, $labType = 0, $offerId = 0, $detection = 0, $referDrId = 0, $branch_id = '') {
      $programesetting = R::findOne('programesetting');
      $data = array(
          'saveId' => 0
          , 'userId' => 0
          , 'from' => date('Y-m-d', strtotime($fdate))
          , 'to' => date('Y-m-d', strtotime($todate))
          , 'order' => 'asc'
          , 'bure' => 1
      );

      $erpApiLink = 'savedaily';
      $res = orderFromErp($erpApiLink, $this->hosturl_ERP, $data);
      $results = json_decode($res);

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

      $ext = $this->sqlErpExt($patientId, $hPatientId, $userId, $fordepartment, $repDoctors, $labType
              , $offerId, $detection, $referDrId, $branch_id);

      $return = array(
          'totalCost' => 0, 'totalCash' => 0, 'totalVisa' => 0, 'totalDept' => 0, 'totalDiscount' => 0
          , 'totalPaid' => 0, 'totalOldCash' => 0, 'totalOldVisa ' => 0, 'totalOld' => 0, 'total' => 0
          , 'totalC' => 0, 'totalV' => 0, 'totalDrFees' => 0, 'resArray' => array());

      $return = $this->erpCommon($return, $results->savePayments, 'username', 'savedailychangeamount'
              , 'detectionvalue_cash', 'savedailydate', $ext, 0);
      $return = $this->erpCommon($return, $results->bankPayments, 'employeename', 'accountmovementamount'
              , 'detectionvalue_visa', 'accountmovementdate', $ext, 1);

      $total = $return['totalPaid'] + $return['totalOld'];

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

      $this->smarty->assign('share', $programesetting->system_type);
      $this->smarty->assign('referDrShow', $programesetting->refer_doctor);
      $this->smarty->assign('drDeptName', $programesetting->fin_doc_dept);
      $this->smarty->assign('organizations', $programesetting->organizations);
      $this->smarty->assign('isErp', 1);
      $this->smarty->assign('allData', $return['resArray']);
   }

   function sqlErpExt($patientId, $hPatientId, $userId, $fordepartment
           , $repDoctors, $labType, $offerId, $detection, $referDrId, $branch_id) {
      $ext = '';
      $role = R::load('awrole', $_SESSION['role_id']);
      switch ($role->financial_visits) {
         case 1:
            $billShow = 0;
            $ext .= ' and visits.for_department =   ' . $_SESSION['role_id'];
            break;
         case 2:
            $billShow = 0;
            $ext .= ' and visits.for_doctor =   ' . $_SESSION['user_id'];
            break;
      }

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

      $programesetting = R::findOne('programesetting');
      $ext .= $this->branchSql($programesetting->branches, $branch_id);

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

      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;
         }
      }

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


      return $ext;
   }

   function getReferredDr($referred_dr_id, $refer_doctor_tb) {

      if ($referred_dr_id > 0) {
         switch ($refer_doctor_tb) {
            case 1:
               $refer = R::getRow('select name from awusers where user_id = ?', [$referred_dr_id]);
               $ref_dr = $refer['name'];
               break;

            default:
               $refer = R::load('refer', $referred_dr_id);
               $ref_dr = $refer->name;
               break;
         }
      } else {
         $ref_dr = 'لا يوجد';
      }
      return $ref_dr;
   }

   function erpCommon($return, $allData, $userCol, $amountErpCol, $amountCol, $dateCol, $ext, $isVisa) {
//        print_r('<pre>');
//        print_r($allData);
//        print_r('</pre>');
      foreach ($allData as $data) {

         if ($data->obygyVisitId > 0) {
            
         } else {
            $res = $this->notVisitObj($data, $userCol, $amountErpCol, $amountCol, $dateCol);
            $return['resArray'][] = $res;
         }
      }

      return $return;
   }

   function notVisitObj($data, $userCol, $amountErpCol, $amountCol, $dateCol) {

      $res = R::getRow('   select    patients.id  as patientid , patients.wifename 
                ,  patients.husdandname , patients.statusno 
                from patients 
                where patients.id = ? ', [$data->obygyVisitId]);

      $res['type'] = $data->processname;

      if ($data->obygyUserId > 0) {
         $user_name = R::getRow('select * from awusers where user_id = ?', [$data->obygyUserId]);
         $res['insert_user'] = $user_name['name'];
      } else {
         $res['insert_user'] = $data->$userCol;
      }

      $res[$amountCol] = $data->$amountErpCol;
      $res['visitdate'] = date('Y/m/d', strtotime($data->$dateCol));
      $res['visitdate'] = date('Y/m/d', strtotime($data->$dateCol));
      $res['vnotes'] = $data->note;

      return $res;
   }

   function erpCommonaaa($return, $allData, $userCol, $amountErpCol, $amountCol, $dateCol, $ext, $isVisa) {
      $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'];
//                }
//            }
//            //    print_r($datum['user_id'] . ' - ');
//            $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)) . ')');

      $visitIds = array();
      print_r('<pre>');
      print_r($allData);
      print_r('</pre>');
      foreach ($allData as $data) {
         print_r($data->obygyVisitId);
//            print_r('<pre>');
//            print_r($visitIds);
//            print_r('</pre>');
         if (!(in_array($data->obygyVisitId, $visitIds))) {
            array_push($visitIds, $data->obygyVisitId);
            $exist = 0;
         } else {
            $exist = 1;
         }
         //      R::debug(true, 2);
         $res = R::getRow('   select    visits.printserial , visits.patientid , visits.for_husband, patients.wifename 
                ,  patients.husdandname , patients.statusno , visits.refer_doctor , visits.refer_doctor_tb  
                , visits.dr_salary  as drFees  , visits.totaldetectionvalue , visits.restdetectionvalue 
                , visits.discount     , visits.detectionid
                from visits 
                JOIN patients ON visits.patientid = patients.id
                LEFT JOIN investigationresults ON visits.id = investigationresults.visitid
                where visits.id = ?  ' . $ext . '  ', [$data->obygyVisitId]);
         //    r::debug(false);
         if (count($res) > 0) {
            $res['insert_user'] = $data->$userCol;
            $res[$amountCol] = $data->$amountErpCol;
            $res['visitdate'] = date('Y/m/d', strtotime($data->$dateCol));

            $res['refer'] = $this->getReferredDr($res['refer_doctor'], $res['refer_doctor_tb']);
            print_r($exist);
            echo ' - ';
            if ($exist != 1) {

               $return['totalCost'] += $res['totaldetectionvalue'];
               $return['totalDept'] += $res['restdetectionvalue'];
               $return['totalDiscount'] += $res['discount'];
               $return['totalDrFees'] += $res['drFees'];
            }
            if ($res['detectionid'] === '999') {
               ### new way
               $original_visit_index = $this->searchForId($res['original_visitid'], $original_visits_data);
               if ($original_visit_index !== null) {
                  $detection_id = $original_visits_data[$original_visit_index]['detectionid'];
               }
               ### new way
               $detection_index = $this->searchForId($detection_id, $detections_data);
               $title = '';
               if ($detection_index !== null) {
                  $title = $detections_data[$detection_index]['title'];
               }
               $res['type'] = 'دفع متبقي ';
               $res['type'] .= ' ( ' . $title . ' ) ';

               $return['totalOldVisa'] += $res['detectionvalue_visa'];
               $return['totalOldCash'] += $res['detectionvalue_cash'];

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

               ### new way
               $original_visit_index = $this->searchForId($res['original_visitid'], $original_visits_data);
               if ($original_visit_index !== null) {
                  $detection_id = $original_visits_data[$original_visit_index]['detectionid'];
               }

               ### new way
               $detection_index = $this->searchForId($detection_id, $detections_data);
               $title = '';
               if ($detection_index !== null) {
                  $title = $detections_data[$detection_index]['title'];
               }

               $res['type'] = 'مرتجع العملاء  ';
               $res['type'] .= ' ( ' . $title . ' ) ';

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

               $return['totalVisa'] += $res['detectionvalue_visa'];
               $return['totalCash'] += $res['detectionvalue_cash'];

               $return['totalPaid'] += $res['detectionvalue_visa'];
               $return['totalPaid'] += $res['detectionvalue_cash'];

               ### new way
               $refund_index = $this->searchForId($res['visitId'], $cal_refund_data, 'visitid');
               if ($refund_index !== null) {
                  $refund = $cal_refund_data[$refund_index]['refund_cash'] + $cal_refund_data[$refund_index]['refund_visa'];
                  $res['refund'] = $refund;
               }
            }
//
//            ## 12  investigations dept     ,  5   rays dept
//            if ($data['dept'] === '5' && $data['detectionid'] !== '999' && $data['detectionid'] !== '9999') {
//                ### 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') {
//                ### 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;
//            }
//
//            if ($data['billType'] == 1) {
//                $data['type'] = 'مرتجع العملاء  ';
//                $data['type'] .= ' ( صيدلية) ';
//
//                $totalRefundVisa += $data['detectionvalue_visa'];
//                $totalRefundCash += $data['detectionvalue_cash'];
//
//                $totalVisa -= $data['detectionvalue_visa'];
//                $totalCash -= $data['detectionvalue_cash'];
//
//                $totalPaid -= $data['detectionvalue_visa'];
//                $totalPaid -= $data['detectionvalue_cash'];
//            }
//
//            ### new way
//            $reserve_index = $this->searchForId($data['visitId'], $reserves_data, 'visit_id');
//            if ($reserve_index !== null) {
//                $data['visitHour'] = date('h:i A', strtotime($reserves_data[$reserve_index]['visit_hour']));
//            }
//
//            if ($showBranchCol == 1 && $data['branch_id'] > 0) {
//                $branch = R::load('branches', $data['branch_id']);
//                $data['branch_name'] = $branch->name;
//            }

            $return['resArray'][] = $res;
         }
         $i++;
      }

      //   $return['resArray'] = $new_allData;
      return $return;
   }

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

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