<?php

require_once("imp/_imp.php");

class Controllers {

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

// 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 = 'syncstructure';
        $this->shcontrollname = 'syncstructure';
        $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);

        set_time_limit(0);    ## unlimited excution time as this script take a lot of time 
# here show the active page in menu
// array called active[] with level of active element
        $active[0] = 'index'; //controller name or tab name
        $active[1] = 'index'; //controller name
// do name  // switch($do)
        switch ($acty) {
            case '':$active[2] = 'index';
                break;
            case 'show':$active[2] = 'index';
                break;
            default :$active[2] = 'index';
                break;
        }
        $this->active = $active;

        R::ext('xdispense', function ($type) {
            return R::getRedBean()->dispense($type);
        });

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

        //find loading time
        $this->time = microtime(true);
        $this->today = date('Y-m-d');
    }

//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']);

        //call other class with other content
        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
        $this->smarty->display('sync/add.html');
        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

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

        $db_user = DB_USER;
        $db_password = DB_PASS;
        $host = DB_HOST;
        ## we want old database to be the same structure as new database with keeping data 
        $oldDbName = filter_input(INPUT_POST, "oldDbName");     ## old one which need to update 
        $newDbName = filter_input(INPUT_POST, "newDbName");

//        ## define another database
        R::addDatabase("old", 'mysql:host=' . $host . ';dbname=' . $oldDbName . '', '' . $db_user . '', '' . $db_password . '');
        R::addDatabase("new", 'mysql:host=' . $host . ';dbname=' . $newDbName . '', '' . $db_user . '', '' . $db_password . '');

        R::selectDatabase("old");
        R::exec(' SET SESSION interactive_timeout = 28800;');
        R::exec('  SET SESSION wait_timeout = 28800;');
        R::selectDatabase('default');

        $tables = r::getAll('SELECT table_name FROM information_schema.tables 
                WHERE table_schema =  "' . $newDbName . '"');
        print_r(' <label style="color: blue"> No Of Tables =  ' . count($tables) . ' </label>  <br> ');

        $i = 1;
        foreach ($tables as $table) {
            $tableName = $table['table_name'];
            if ($i >= 1 && $i < 50) {
                try {
                    $tableExist = $this->checkTableExist($oldDbName, $tableName);
                    switch ($tableExist) {
                        case 1:
                            ## compare two tables 
                            $diff = $this->compareTables($oldDbName, $newDbName, $tableName);
                            switch ($diff) {
                                case 1:
                                    ## create temp table and copy data to it 
                                    $this->copyTable($oldDbName, $newDbName, $tableName);
                                    break;

                                default:
                                    break;
                            }
                            break;

                        default:
                            ## create the table 
                            $createSql = R::getRow('SHOW CREATE TABLE  ' . $newDbName . '.' . $tableName . ' ');
                            $this->createTable($createSql['Create Table'], $tableName);

                            break;
                    }


                    print_r(' <label style="color: green;width: 33%; background-color: antiquewhite;"> ' . $i . ' -  ' . $tableName . ' Succes</label>   ');
                } catch (Exception $ex) {
                    print_r($ex);
                    print_r(' <label style="color: red; width: 33%;   background-color: antiquewhite;"> ' . $tableName . ' Error</label>  ');
                    $i++;
                    continue;
                }
            }
            $i++;
        }

        echo "Time Elapsed: " . ((microtime(true) - $this->time) / 60) . "m";
    }

    function copyTable($oldDbName, $newDbName, $tableName) {
        R::selectDatabase("old");

        R::exec("SET FOREIGN_KEY_CHECKS = 0; ");

        R::exec('   DROP TABLE IF EXISTS ' . $oldDbName . '.' . $tableName . '_temp ; ');

        R::exec('    CREATE TABLE ' . $oldDbName . '.' . $tableName . '_temp   LIKE ' . $newDbName . '.' . $tableName . ' ;');

        $colums = R::getAll('DESCRIBE ' . $tableName);
        $oldData = R::getAll('select * from ' . $tableName);

        foreach ($oldData as $data) {
            $x = 0;
            $rowCol = '  (  ';
            $rowValues = array();
            $rowMark = '  (  ';
            //       $row = R::xdispense($tableName . '_temp');
            foreach ($colums as $col) {
                $colName = $col['Field'];
                $colExist = r::getAll('SHOW COLUMNS FROM ' . $tableName . '_temp LIKE   "' . $colName . '"');
                if (count($colExist) > 0) {
                    if ($x > 0) {
                        $rowCol .= '  ,  ';
                        $rowMark .= '  ,  ';
                    }
                    $rowCol .= '   ' . $colName;
                    $rowValues[] = $data[$colName];
                    $rowMark .= '  ?  ';
                    $x++;
                }
            }

            $rowCol .= '  ) ';
            $rowMark .= '  )  ';

            if ($x > 0) {
                $sql = ' insert  into   ' . $tableName . '_temp    ' . $rowCol . '        values   ' . $rowMark . ' ';
                R::exec($sql, $rowValues);
            }
        }

        R::exec('   DROP TABLE IF EXISTS ' . $oldDbName . '.' . $tableName . ' ; ');

        R::exec('   ALTER TABLE ' . $oldDbName . '.' . $tableName . '_temp   RENAME ' . $oldDbName . '.' . $tableName . '  ; ');

        R::exec('   DROP TABLE IF EXISTS ' . $oldDbName . '.' . $tableName . '_temp ; ');

        R::exec("SET FOREIGN_KEY_CHECKS = 1; ");

        R::selectDatabase('default');
    }

    function compareTables($oldDbName, $newDbName, $tableName) {
        $compare = r::getRow('SELECT IF(COUNT(1)>0,1,0) Comparison FROM
                (
                    SELECT
                        column_name,ordinal_position,
                        data_type,column_type,COUNT(1) rowcount
                    FROM information_schema.columns
                    WHERE
                    (
                        (table_schema= "' . $newDbName . '" AND table_name= "' . $tableName . '") OR
                        (table_schema= "' . $oldDbName . '" AND table_name= "' . $tableName . '")
                    )
                    AND table_name IN ( "' . $tableName . '")
                    GROUP BY
                        column_name,ordinal_position,
                        data_type,column_type
                    HAVING COUNT(1)=1
                ) A;');

        return $compare['Comparison'];
    }

    function createTable($createSql, $tableName) {
        ## switch to the new database
        R::selectDatabase("old");
        R::exec($createSql);
        R::exec('  ALTER TABLE  ' . $tableName . '  auto_increment = 1;');
        R::selectDatabase('default');
    }

    function checkTableExist($dbName, $tableName) {
        $exist = r::getRow('SELECT COUNT(*) as exist  FROM information_schema.tables 
                            WHERE table_schema = "' . $dbName . '"
                            AND table_name =   "' . $tableName . '"');

        return $exist['exist'];
    }

    function defaults() {
        
    }

    ## move colums from db to another

    function missedColums() {
        $db_user = DB_USER;
        $db_password = DB_PASS;
        $host = DB_HOST;
        ## we want old database to be the same structure as new database with keeping data
        $oldDbName = 'drthaergt4it_clinic';     ## old one which need to update
        $newDbName = 'drthaergt4it_center';

        R::addDatabase("old", 'mysql:host=' . $host . ';dbname=' . $oldDbName . '', '' . $db_user . '', '' . $db_password . '');

        R::selectDatabase("old");
        $oldVisits = R::findAll('visits', ' id >= 2077 ');
        R::selectDatabase('default');
        foreach ($oldVisits as $oldVisit) {
            $visit = R::load('visits', $oldVisit->id);
            if (count($visit) > 0) {
                $visit->visit_time = $oldVisit->approx_time;
                $visit->branch_id = $oldVisit->branch_id;
                $visit->visit_period = $oldVisit->visit_period;
                $visit->approx_time = $oldVisit->approx_time;
                R::store($visit);
                echo " " . $oldVisit->id . " - ";
            }
        }
    }

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

    public function check_missing() {

        $target = "dr_app_old";
        $source = "obgy_app";

        $tables = r::getAll('SELECT table_name FROM information_schema.tables 
                WHERE table_schema =  "' . $target . '"');

        foreach ($tables as $table) {

            $tableName = $table['table_name'];
            $exist = r::getRow('SELECT COUNT(*) as exist  FROM information_schema.tables 
                            WHERE table_schema = "' . $source . '"
                            AND table_name =   "' . $tableName . '"');

            if ($exist['exist'] == 0) {
                print_r('<pre> ');
                print_r($tableName);
                print_r('</pre> ');
            }
        }
    }

    public function check_auto_increment() {

        $dbName = "obor_db";

        $tables = r::getAll('SELECT table_name FROM information_schema.tables 
                WHERE table_schema =  "' . $dbName . '"');

        foreach ($tables as $table) {
            $tableName = $table['table_name'];
            $exist = r::getRow("SELECT EXTRA   ,  TABLE_NAME
                                    FROM INFORMATION_SCHEMA.COLUMNS
                                    WHERE TABLE_NAME = '$tableName'
                                        and table_schema  = '$dbName'
                                        AND COLUMN_NAME = 'id'");

            print_r('<pre> ');
            print_r($exist);
            print_r('</pre> ');
        }
    }

    public function set_auto_increment() {

        $db_user = DB_USER;
        $db_password = DB_PASS;
        $host = DB_HOST;
        $dbName = DB_NAME;
        //
        //
//        ## define another database
        R::addDatabase("obor", 'mysql:host=' . $host . ';dbname=' . $dbName . '', '' . $db_user . '', '' . $db_password . '');

        R::selectDatabase("obor");

        $tables = r::getAll('SELECT table_name FROM information_schema.tables 
                WHERE table_schema =  "' . $dbName . '"');

        R::exec("SET FOREIGN_KEY_CHECKS = 0; ");

        foreach ($tables as $table) {
            $tableName = $table['table_name'];
            r::debug(true, 2);
            r::exec('ALTER TABLE ' . $tableName . '  CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;');
            r::debug(false);
            $exist = r::getRow("SELECT EXTRA   ,  TABLE_NAME
                                    FROM INFORMATION_SCHEMA.COLUMNS
                                    WHERE TABLE_NAME = '$tableName'
                                        and table_schema  = '$dbName'
                                        AND COLUMN_NAME = 'id'");

            print_r('<pre> ');
            print_r($exist);
            print_r('</pre> ');
        }


        R::selectDatabase('default');
    }

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

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

        $patients = R::getAll('select id    from  patients  ');
        foreach ($patients as $patient) {
            $patientId = $patient['id'];
            $balance = 0;

            $lastVisit = R::getAll('SELECT visits.id as visitid , visits.restdetectionvalue as rest 
                                                    FROM visits
                                                    where  visits.patientid = ?   and  visits.restdetectionvalue >0
                                                               and visits.deleted != ?   ', [$patientId, 1]);

            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'])) {
                    $balance = $balance + $data['rest'] - $payVisits['paid'] - $payVisits['paid_discount'];
                }
            }

            if ($balance >= 0 && $patientId > 0) {
                R::exec('update patients set balance =   ' . $balance . '    where  id  = ' . $patientId . ' ');
            }
            print_r(' ( ' . $patientId . '  ,    ' . $balance . ' ) - ');
        }
    }

    public function improve() {
        $db_user = DB_USER;
        $db_password = DB_PASS;
        $host = DB_HOST;
        $oldDbName = "alielhad_obgy";
        R::addDatabase("old", 'mysql:host=' . $host . ';dbname=' . $oldDbName . '', '' . $db_user . '', '' . $db_password . '');

        R::selectDatabase("old");
        $patients = R::getAll('SELECT id   , insert_from , patient_password_hash , phone_qid  from   patients');
        $visits = R::getAll('SELECT id   , mobileappvisit , payedflag , clinic_id  from   visits');

        R::selectDatabase('default');
        foreach ($patients as $patient) {
            try {
                $pat = R::load('patients', $patient['id']);
                $pat->insert_from = $patient['insert_from'];
                $pat->patient_password_hash = $patient['patient_password_hash'];
                $pat->phone_qid = $patient['phone_qid'];
                R::store($pat);
                print_r(' <label style="color: green;width: 33%; background-color: antiquewhite;"> ' . $patient['id'] . '  - Patient  Succes</label>   ');
            } catch (Exception $ex) {
                print_r($ex);
                print_r(' <label style="color: red; width: 33%;   background-color: antiquewhite;"> ' . $patient['id'] . ' - Patient Error</label>  ');
            }
        }

        foreach ($visits as $visit) {
            try {
                $pat = R::load('visits', $visit['id']);
                $pat->mobileappvisit = $visit['mobileappvisit'];
                $pat->payedflag = $visit['payedflag'];
                $pat->clinic_id = $visit['clinic_id'];
                R::store($pat);
                print_r(' <label style="color: green;width: 33%; background-color: antiquewhite;"> ' . $visit['id'] . '  - Visit  Succes</label>   ');
            } catch (Exception $ex) {
                print_r($ex);
                print_r(' <label style="color: red; width: 33%;   background-color: antiquewhite;"> ' . $visit['id'] . ' - Visit  Error</label>  ');
            }
        }
    }

    public function resolveV() {

        $oldVisits = r::getAll('SELECT id , detectionvalue FROM gt4.visits ');

        foreach ($oldVisits as $oldVisit) {

            $visit = R::load('visits', $oldVisit['id']);

            if (count($visit) > 0) {
                $visit->totaldetectionvalue = $oldVisit['detectionvalue'];
                $visit->detectionvalue_cash = $oldVisit['detectionvalue'];
                R::store($visit);
            }
        }
    }

    public function resolve() {

        $oldPats = r::getAll('SELECT id , statusno FROM gt4.patients ');

        foreach ($oldPats as $oldPat) {

            $patient = R::load('patients', $oldPat['id']);

            if (count($patient) > 0) {
                $patient->statusno = $oldPat['statusno'];
                R::store($patient);
                print_r($oldPat['id'] . ' - ');
            }
        }
    }

// at the end of class call
    public function __destruct() {
        //echo "Time Elapsed: ".(microtime(true) - $this->time)."s";
        ##close connection
        R::close();
    }
}
