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


        //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, $address = "", $patientId = 0, $hPatientId = 0) {

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

        $ext = '';

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

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


        $this->commonSearch2($fdate, $todate, $branch_id, $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');

        $this->commonSearch2($fdate, $todate, $branch_id, $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) {
            $tables = array('gynadrugs', 'mainantenentaldrugs', 'ancsheetdrugs', 'infertilitydrugs'
                , 'gynasheetdrugs', 'infertilitysheetdrugs');

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

//            print_r($sql);

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

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

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

}
