<?php

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

require_once("imp/_imp.php");

class Controllers {

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

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

      //here name of controll to known
      $this->controllname = 'financialreport';
      $this->shcontrollname = 'Home';
      $this->smarty->assign('controllname', $this->controllname);
      $this->smarty->assign('shcontrollname', $this->shcontrollname);

      //breadcrumbs
      require_once("../../_library/plugins/Breadcrumbs/Breadcrumbs.php");
      $acty = filter_input(INPUT_GET, 'ac');
      $bc = new breadcrumbs($hosturl, '', '', $acty);
      $bc->crumbs();
      $breadcrumbs = $bc->breadcrumbs;
      $smarty->assign('breadcrumbs', $breadcrumbs);

      # here show the active page in menu
      // array called active[] with level of active element
      $active[0] = 'financialreport'; //controller name or tab name
      $this->active = $active;

      $this->smarty->assign('showDate', 1);
      date_default_timezone_set('Africa/Cairo');

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

   //here the frist page and
   //the frist action in this class

   public function commonSearch2($fdate, $todate, $branch_id = 0, $detection = 0, $address = "", $patientId = 0, $hPatientId = 0) {

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

      $ext = '';

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

      if ($branch_id > 0) {
         $ext .= '  and visits.branch_id =  ' . $branch_id;
      }

      if (!empty($address) && $address != '') {
         $ext .= " AND  patients.address LIKE '%$address%'";
      }

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


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

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

      $sql = 'select distinct  visits.id  as visitId  ,  detectionvalue_visa , detectionvalue_cash 
                        , totaldetectionvalue, restdetectionvalue , detectionid   , visits.discount 
                 ,  detections.title as type, visits.patientid ,visits.user_id  
                  , patients.wifename, patients.husdandname, patients.address   
                     , patients.statusno  ,visits.visitdate    ,visits.notes    as vnotes   , visits.visitid as original_visitid    
                    , 0 as refund   
                   from visits  
                               join  patients                         on  visits.patientid = patients.id 
                               left join  detections                on  visits.detectionid = detections.id 
                         where visits.deleted = 0  and visitdate >= "' . $fdate . '" and visitdate <= "' . $todate . '"   ' . $ext;

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

      $this->common($allData, $fdate, $todate);
   }

   function calcRefund($visitId) {

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

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

      return $refund;
   }

   public function 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);

      $allPatients = R::findAll('patients');
      $this->smarty->assign('allPatients', $allPatients);

      $extraTime = R::findOne('programesetting');
      $branchSql = ' ';
      $branch_id = 0;
      if ($extraTime->branches == 1 && $_SESSION['role_id'] == 3) {
         $user = R::findOne('awusers', 'user_id = ?', [$_SESSION['user_id']]);
         $branchSql = '  and id  =  ' . $user->branch_id;
         $branch_id = $user->branch_id;
      }

      $branches = R::findAll('branches', ' id > 0 ' . $branchSql);
      $this->smarty->assign('branches', $branches);
      $this->smarty->assign('setting', $extraTime);

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

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

      $this->commonSearch2($today, $today, $branch_id);
      $this->newPatients($today, $today);

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

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

      $fdate = filter_input(INPUT_POST, 'fdate');
      $todate = filter_input(INPUT_POST, 'todate');
      $patientId = filter_input(INPUT_POST, 'value');
      $hPatientId = filter_input(INPUT_POST, 'hvalue');
      $address = filter_input(INPUT_POST, 'address');
      $branch_id = filter_input(INPUT_POST, 'branch_id');
      $detection = filter_input(INPUT_POST, 'detection');

      $this->commonSearch2($fdate, $todate, $branch_id, $detection, $address, $patientId, $hPatientId);

      $this->newPatients($fdate, $todate);

      $this->smarty->assign('fdate', $fdate);
      $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');
      $address = filter_input(INPUT_POST, 'address');
      $branch_id = filter_input(INPUT_POST, 'branch_id');
      $detection = filter_input(INPUT_POST, 'detection');

      $this->commonSearch2($fdate, $todate, $branch_id, $detection, $address, $patientId, $hPatientId);

      $this->newPatients($fdate, $todate);

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

   public function onesetup() {

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

      try {
         //delete all but with only default
         #1 clean roles only managment
         #2 clean menu only default
         #3 clean controls only default
         #4 users clean users
         #delete all menus
         $menus = R::findAll('awmenu', 'id > 15');
         #delete from awrolemenu
         foreach ($menus as $one) {
            //get propes
            $rolemenu = R::findAll('awrolemenu', 'menu_id = ?', [$one->id]);
            R::trashAll($rolemenu);
         }
         R::trashAll($menus);
         #delete controles
         $controlls = R::findAll('awcontroll', 'id > 4 and id < 18');
         foreach ($controlls as $one) {
            //get propes
            $inprop = R::findAll('awcontrollprop', 'awcontroll_id = ?', [$one->id]);
            #delete from awrolecontroleprop
            foreach ($inprop as $one) {
               //get propes
               $roleprop = R::findAll('awrolecontrollprop', 'controllprop_id = ?', [$one->id]);
               R::trashAll($roleprop);
            }
            R::trashAll($inprop);
         }
         R::trashAll($controlls);
         #delete othre roles
         $roles = R::findAll('awrole', 'id > 1');
         R::trashAll($roles);
         #delete other users
         //$users = R::findAll('awusers', 'user_id > 1');
         //R::exec('Delete from awusers where user_id > 1');
         //R::trashAll($users);
         ############################
         //new one
         $menusx = R::findAll('awmenu', ' id = 4 or id = 5 or id = 7 or id = 8 or id = 10 or id = 11 or id = 13 or id = 14 or id = 15 ');
         #delete from awrolemenu
         foreach ($menusx as $one) {
            //get propes
            $rolemenu = R::findAll('awrolemenu', 'menu_id = ?', [$one->id]);
            R::trashAll($rolemenu);
         }
         R::trashAll($menusx);

         print_r('trash all , install is good');
      } catch (Exception $ex) {
         print_r('something go wrong  , try again');
      }
   }

   public function balance() {
      //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);

      $allPatients = R::findAll('patients');
      $this->smarty->assign('allPatients', $allPatients);

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

      $sq1 = 'select sum(balance) as detectionvalue, "إضافة للمبلغ  الاجمالى" as type'
              . ' , totalbalance.patientid , patients.wifename, patients.husdandname, patients.address'
              . ' from totalbalance, patients '
              . 'where totalbalance.deleted = 0 and totalbalance.patientid = patients.id '
              . ' and   adddate = "' . $today . '" group by patientid';
      $sq1 .= ' union  all  ';
      $sq1 .= 'select sum(detectionvalue) as detectionvalue, "دفع قسط" as type'
              . ' , visits.patientid , patients.wifename, patients.husdandname, patients.address from visits,  '
              . ' patients  where visits.deleted = 0 and visits.patientid = patients.id '
              . '    and  visitdate = "' . $today . '"  and visits.detectionid = -99   group by patientid';

      $allData = R::getAll($sq1);
      $totalCost = 0;
      foreach ($allData as $data) {
         if ($data['type'] == "دفع قسط") {
            $totalCost -= $data['detectionvalue'];
         } else {
            $totalCost += $data['detectionvalue'];
         }
      }

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

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

   function newPatients($from, $to) {

      $from = date('Y-m-d', strtotime($from));
      $to = date('Y-m-d', strtotime($to));

      $sql = 'select id , wifename, husdandname, address  , entrydate , statusno
                from  patients where patients.deleted = 0
                and DATE(entrydate) between "' . $from . '"  and  "' . $to . '" ';

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

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

   function common($allData, $fdate, $todate) {
      $totalCost = 0;
      $totalCash = 0;
      $totalVisa = 0;
      $totalDept = 0;
      $totalDiscount = 0;

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

         switch ($data['detectionid']) {
            case -99:
               $allData[$i]['type'] = 'دفع قسط';
               break;

            case 9999:
               $originalVisit = R::load('visits', $data['original_visitid']);
               $det = R::load('detections', $originalVisit->detectionid);
               $allData[$i]['type'] = 'مرتجع العملاء';
               $allData[$i]['type'] .= ' ( ' . $det->title . ' ) ';
               break;

            case 999:
               $originalVisit = R::load('visits', $data['original_visitid']);
               $det = R::load('detections', $originalVisit->detectionid);
               $allData[$i]['type'] = 'دفع متبقى ';
               $allData[$i]['type'] .= ' ( ' . $det->title . ' ) ';
               break;

            default:
               $allData[$i]['refund'] = $this->calcRefund($data['visitId']);
               break;
         }

         $totalCost += $data['totaldetectionvalue'];
         $totalDept += $data['restdetectionvalue'];
         $totalVisa += $data['detectionvalue_visa'];
         $totalCash += $data['detectionvalue_cash'];
         $totalDiscount += $data['discount'];

         $entrydate = date('Y-m-d', strtotime($data['entrydate']));

         $from = date('Y-m-d', strtotime($fdate));
         $to = date('Y-m-d', strtotime($todate));

         if (($entrydate >= $from) && ($entrydate <= $to)) {
            $allData[$i]['newPat'] = 1;
         } else {
            $allData[$i]['newPat'] = 0;
         }

         $i++;
      }

      $this->smarty->assign('totalCost', $totalCost);
      $this->smarty->assign('totalDept', $totalDept);
      $this->smarty->assign('totalVisa', $totalVisa);
      $this->smarty->assign('totalCash', $totalCash);
      $this->smarty->assign('totalDiscount', $totalDiscount);
      $this->smarty->assign('allData', $allData);
   }

   ############################# drug use report ###################################

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

      $drugs = R::getAll('SELECT  id , drugname   FROM drugs order by drugname asc');
      $allDrugcats = R::getAll('SELECT distinct  drugcat   FROM drugs order by drugcat asc');
//      $allDrugTypes = R::getAll('SELECT distinct  drugtype  FROM drugs order by drugtype asc');
//      $allDrugDoses = R::getAll('SELECT distinct  drugdos   FROM drugs order by drugdos asc');

      $this->smarty->assign('drugs', $drugs);
      $this->smarty->assign('allDrugcats', $allDrugcats);
//      $this->smarty->assign('allDrugTypes', $allDrugTypes);
//      $this->smarty->assign('allDrugDoses', $allDrugDoses);

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

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

   public function drugByCat() {       ## search in database
      //check login
      autho::checkautho($this->hosturl);

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

      if ($catId) {
         $drugs = R::getAll('select a.id , a.drugname from drugs a inner join (select distinct drugname, min(id) '
                         . 'as id from drugs group by drugname ) as b on a.drugname = b.drugname and a.id = b.id where a.drugcat = "' . $catId . '"');
      } else {
         $drugs = R::getAll('select a.id , a.drugname from drugs a inner join
                     (select distinct drugname, min(id) as id from drugs group by drugname ) 
                     as b on a.drugname = b.drugname and a.id = b.id');
      }

      $this->smarty->assign('drugs', $drugs);
      $this->smarty->display('reports/drug/newselectvalues.html');
   }

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

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

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

      $this->commonSearch($fdate, $todate, $drugId);
      $this->smarty->display('reports/drug/drugsearch.html');
   }

   function commonSearch($fdate, $todate, $drugId) {
      if ($drugId > 0) {
         $allData = array();
         $drugsCount = 0;
         $tables = array('gynadrugs', 'mainantenentaldrugs', 'ancsheetdrugs', 'infertilitydrugs'
             , 'gynasheetdrugs', 'infertilitysheetdrugs');

         $patients = $this->drugPatients($tables, $fdate, $todate, $drugId);
         foreach ($patients as $pat) {
            $patient = array();
            $patDrugsCount = $this->patientDrugs($pat['patientid'], $tables, $fdate, $todate, $drugId);
            $patient['id'] = $pat['patientid'];
            $patient['name'] = $pat['wifename'];
            $patient['statusno'] = $pat['statusno'];
            $patient['drugsCount'] = $patDrugsCount;
            array_push($allData, $patient);
            $drugsCount = $drugsCount + $patDrugsCount;
         }
      }

      $this->smarty->assign('allData', $allData);
      $this->smarty->assign('dataCount', count($allData));
      $this->smarty->assign('drugsCount', $drugsCount);
   }

   function patientDrugs($patientId, $tables, $fdate, $todate, $drugId) {
      $i = 1;
      $sql = '';

      foreach ($tables as $tableName) {
         if ($i > 1) {
            $sql .= ' union all ';
         }

         $sql .= '   SELECT      ' . $tableName . '.id  
                    FROM ' . $tableName . '     
                    where ' . $tableName . '.drugid =  ' . $drugId . '    and        ' . $tableName . '.patientid =      ' . $patientId . ' 
                                  and ' . $tableName . '.date between  "' . $fdate . '"  and  "' . $todate . '"  ';

         $i++;
      }
//            print_r($sql);

      $drugs = R::getAll($sql);
      return count($drugs);
   }

   function drugPatients($tables, $fdate, $todate, $drugId) {
      $i = 1;
      $sql = '';

      foreach ($tables as $tableName) {
         if ($i > 1) {
            $sql .= ' union ';
         }

         $sql .= '   SELECT  distinct  ' . $tableName . '.patientid   ,  patients.wifename   
                    ,  patients.statusno      
                    FROM ' . $tableName . '     
                        join  patients   on   patients.id =   ' . $tableName . '.patientid
                    where ' . $tableName . '.drugid =  ' . $drugId . '  and ' . $tableName . '.date between  "' . $fdate . '"  and  "' . $todate . '"  ';

         $i++;
      }


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

      return $patients;
   }

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

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

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

      $this->commonSearch($fdate, $todate, $drugId);

      $this->smarty->display('reports/drug/drugprint.html');
   }

   ############################# payment rest  report ###################################

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

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

      $allPatients = R::findAll('patients');
      $this->smarty->assign('allPatients', $allPatients);

      $this->paymentSearch($today, $today);

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

   public function searchPayment() {
      //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');
      $address = filter_input(INPUT_POST, 'address');

      $this->paymentSearch($fdate, $todate, $address, $patientId, $hPatientId);

      $this->smarty->display('reports/payment/restsearch.html');
   }

   public function paymentSearch($fdate, $todate, $address = "", $patientId = 0, $hPatientId = 0) {

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

      $ext = '';

      if (!empty($address) && $address != '') {
         $ext .= " AND  patients.address LIKE '%$address%'";
      }

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


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

   public function executePaySql($fdate, $todate, $ext) {
      $sql = 'select distinct  visits.id  as visitId  ,  detectionvalue_visa , detectionvalue_cash 
                        , totaldetectionvalue, restdetectionvalue as rest     , visits.discount 
                 ,  detections.title as type, visits.patientid   , patients.wifename, patients.husdandname
                     , patients.statusno  ,visits.visitdate    ,visits.notes    as vnotes    
                   from visits  
                               join  patients                         on  visits.patientid = patients.id 
                               left join  detections                on  visits.detectionid = detections.id 
                         where visits.deleted = 0 and  visits.restdetectionvalue >0     
                         and visitdate >= "' . $fdate . '" and visitdate <= "' . $todate . '"   ' . $ext;

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

      $i = 0;
      foreach ($allVisit 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    deleted != ? and detectionid = 999 and visitid = ?   ', [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'])) {

            $v['visitdate'] = $data['visitdate'];
            $v['dept'] = $data['rest'] - $payVisits['paid'] - $payVisits['paid_discount'];
            $v['wifename'] = $data['wifename'];
            $v['husdandname'] = $data['husdandname'];
            $v['type'] = $data['type'];
            $v['patientid'] = $data['patientid'];
            $v['totaldetectionvalue'] = $data['totaldetectionvalue'];
            $v['statusno'] = $data['statusno'];
            $v['vnotes'] = $data['vnotes'];

            array_push($visits, $v);
         }
      }

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

   public function printPayment() {
      //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');
      $address = filter_input(INPUT_POST, 'address');

      $this->paymentSearch($fdate, $todate, $address, $patientId, $hPatientId);

      $this->smarty->display('reports/payment/restprint.html');
   }

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