<?php

namespace Models {

  require_once 'Models/User.class.php';
  
  require_once 'Tools/Crypto.class.php';
  
  class CtAdminInterface {
    //
    protected $DataInterface;

    /**
     * 
     */
    public function __construct($DataInterface) {
      $this->DataInterface = $DataInterface;
    }

    /**
     * Check login/password and create JWT token.
     */
    public function CtAdminLogin(&$User, $email, $clearPassword) {
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT 
          ID, password, firstname, lastname 
        FROM
          user
        WHERE 
          active = 1 AND 
          email = '$email' AND
          type = 'cro'"
      );
      if(!$statement->execute()) {
        $results = Array('result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo());
      }
      else {
        $results = $statement->fetchAll(\PDO::FETCH_ASSOC);
        if(count($results)){
          if(\Tools\Crypto::verify($clearPassword, $results[0]['password'])) {
            // Generate JWT token
            $issuer_claim = \Config\Settings::getTokenIssuer();
            $audience_claim = \Config\Settings::getAdminTokenAudience();
            $issuedat_claim = time(); // issued at
            $notbefore_claim = $issuedat_claim + \Config\Settings::getTokenNotBefore();
            $expire_claim = $issuedat_claim + \Config\Settings::getTokenExpiration();
            $token = array(
              "iss" => $issuer_claim,
              "aud" => $audience_claim,
              "iat" => $issuedat_claim,
              "nbf" => $notbefore_claim,
              "exp" => $expire_claim,
              "data" => array(
                "ID" => $results[0]['ID'],
                "firstname" => $results[0]['firstname'],
                "lastname" => $results[0]['lastname'],
                "email" => $email
              )
            );
            $jwt = \Firebase\JWT\JWT::encode($token, \Config\Settings::getTokenPrivateKey());
            // OK
            $results = Array(
              "result" => "OK",
              "token" => $jwt,
              "email" => $email,
              "expireAt" => $expire_claim
            );
          }
          else {
            $results = Array('result' => 'ERROR', 'reason' => 'bad_password', 'message' => 'Invalid password');
          }
        }
        else {
          $results = Array('result' => 'ERROR', 'reason' => 'unknown', 'message' => 'No such user');
        }
      }
      return $results;
    }

    /**
     * Logout.
     */
    public function CtAdminLogout(&$User) {
      $User->logout();
      return Array('result' => 'OK');
    }

    /**
     * Get profile data.
     */
    public function CtAdminProfileGet($User) {
      $userID = $User->ID;

      // select centers
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT ct_center.ID, organization.name AS org_name, probe.name AS probe_name FROM organization, probe, ct_center WHERE ct_center.fk_organization = organization.ID AND ct_center.fk_probe = probe.ID ORDER by stamp"
      );
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $centers = $statement->fetchAll(\PDO::FETCH_ASSOC);
      $ID_center = count($centers)?$centers[0]['ID']:0;

      // select investigators
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT * FROM user WHERE type = 'investigator' ORDER BY ID"
      );
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $investigators = $statement->fetchAll(\PDO::FETCH_ASSOC);
      $ID_investigator = count($investigators)?$investigators[0]['ID']:0;

      // select readers
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT * FROM user WHERE type = 'reader' ORDER BY ID"
      );
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $readers = $statement->fetchAll(\PDO::FETCH_ASSOC);
      $ID_reader = count($readers)?$readers[0]['ID']:0;

      // OK
      return array(
        'result' => 'OK',
        'ID' => $User->ID,
        'firstname' => $User->firstname,
        'lastname' => $User->lastname,
        'email' => $User->email,
        'ID_center' => $ID_center,
        'ID_investigator' => $ID_investigator,
        'ID_reader' => $ID_reader
      );
    }

    /**
     * 
     */
    public function CtAdminSettingsGet($User) {
      $userID = $User->ID;

      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT * FROM clinical_trial"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $settings = $statement->fetchAll(\PDO::FETCH_ASSOC)[0];
      $settings['comment'] = stripslashes($settings['comment']);

      // OK
      return array(
        'result' => 'OK',
        'settings' => $settings
      );
    }    

    /**
     * 
     */
    public function CtAdminPatientsGet($User, $ID_center) {
      $userID = $User->ID;

      // select centers
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT ct_center.ID, organization.name AS org_name, probe.name AS probe_name FROM organization, probe, ct_center WHERE ct_center.fk_organization = organization.ID AND ct_center.fk_probe = probe.ID ORDER by stamp"
      );
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $centers = $statement->fetchAll(\PDO::FETCH_ASSOC);

      // select patients
      $statement = $this->DataInterface->DatabaseConnection->prepare("
        SELECT 
          P.ctPatientID, 
          V.*, 
          (SELECT COUNT(media.ID) FROM media WHERE JSON_VALUE(media.metrics, '$.fps') IS NULL AND media.fk_visit = V.ID) as imageCount, 
          (SELECT COUNT(media.ID) FROM media WHERE media.fk_visit = V.ID) as mediaCount, 
          CONCAT(U.lastname,' ',U.firstname) AS investigator, 
          (SELECT MAX(measure.created) FROM measure, media WHERE media.fk_visit = V.ID AND media.ID = measure.fk_media) as measureLastDate
        FROM patient P, visit V, user U, ct_center C 
        WHERE V.fk_patient = P.ID  
        AND P.fk_user = U.ID AND U.type = 'investigator' 
        AND C.ID = U.fk_center AND C.ID = $ID_center 
        ORDER BY V.visitDate DESC
      ");
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $visits = $statement->fetchAll(\PDO::FETCH_ASSOC);

      // select readers
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT * FROM user WHERE type = 'reader'"
      );
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $readers = $statement->fetchAll(\PDO::FETCH_ASSOC);

      // OK
      return [
        'result' => 'OK', 
        'visits' => $visits,
        'readers' => $readers,
        'centers' => $centers,
        'user' => $User,
        'ID_center' => $ID_center
      ];
    }

    /**
     * 
     */
    public function CtAdminVisitsGet($User, $ID) {
      $userID = $User->ID;

      // select visits
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT * FROM visit WHERE ID = $ID"
      );
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $visits = $statement->fetchAll(\PDO::FETCH_ASSOC);
      foreach($visits as &$V) {
        $ID_visit = $V['ID'];
        // select media
        $statement = $this->DataInterface->DatabaseConnection->prepare(
          "SELECT * FROM media WHERE fk_visit = $ID_visit"
        );
        // Error check
        if(!$statement->execute()) {
          return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
        }
        $V['media'] = $statement->fetchAll(\PDO::FETCH_ASSOC);
        foreach($V['media'] as &$M) {
          $M['metrics'] = json_decode($M['metrics']);
        }
      }

      return [
        'result' => 'OK', 
        'visits' => $visits
      ];
    }    

    /**
     * 
     */
    public function CtAdminUsersGet($User, $type) {
      $userID = $User->ID;

      // countries
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT * FROM country ORDER BY name_en"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $countries = $statement->fetchAll(\PDO::FETCH_ASSOC);

      if($type=='reader') {
        // readers
        $statement = $this->DataInterface->DatabaseConnection->prepare(
          "SELECT * FROM user WHERE type = '$type'"
        );
        if(!$statement->execute()) {
          return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
        }
        $users = $statement->fetchAll(\PDO::FETCH_ASSOC);

        foreach($users as &$U) {
          $ID = $U['ID'];
          // organization
          $statement = $this->DataInterface->DatabaseConnection->prepare(
            "SELECT * FROM organization WHERE fk_user = $ID"
          );
          if(!$statement->execute()) {
            return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
          }
          $U['org'] = $statement->fetchAll(\PDO::FETCH_ASSOC);
        }

        $organizations = [];
      }
      else if($type=='investigator') {
        // organization
        $statement = $this->DataInterface->DatabaseConnection->prepare(
          "SELECT ct_center.ID AS center_ID, organization.name AS org_name, probe.name AS probe_name FROM organization, probe, ct_center WHERE ct_center.fk_organization = organization.ID AND ct_center.fk_probe = probe.ID ORDER by stamp"
        );
        if(!$statement->execute()) {
          return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
        }
        $organizations = $statement->fetchAll(\PDO::FETCH_ASSOC);

        // investigators
        $statement = $this->DataInterface->DatabaseConnection->prepare(
          "SELECT * FROM user WHERE fk_center != 0 AND type = '$type'"
        );
        if(!$statement->execute()) {
          return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
        }
        $users = $statement->fetchAll(\PDO::FETCH_ASSOC);
      }

      // OK
      return array(
        'result' => 'OK',
        'users' => $users,
        'organizations' => $organizations,
        'type' => $type,
        'countries' => $countries
      );
    }

    /**
     * 
     */
    public function CtAdminCROsGet($User) {
      $userID = $User->ID;

      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT * FROM user WHERE type = 'cro'"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $users = $statement->fetchAll(\PDO::FETCH_ASSOC);

      // OK
      return array(
        'result' => 'OK',
        'users' => $users
      );
    }    

    /**
     * 
     */
    public function CtAdminUsersPost($User, $data) {
      $userID = intval($data['userID']);

      if($data['type']=='investigator') {
        // insert
        if($userID==0) {
          // Insert user
          $statement = $this->DataInterface->DatabaseConnection->prepare(
            "INSERT INTO user(activation_token, activation_expire, activation, password, firstname, lastname, email, phone, type, active, fk_center) 
                      VALUES(:activation_token, :activation_expire, NOW(), :password, :firstname, :lastname, :email, :phone, :type, :active, :fk_center)"
          );
          $activation_token = \Tools\UUID::v4();
          $activation_expire = date('Y-m-d H:i:s', strtotime(date('Y-m-d H:i:s'). ' + 1 days'));
          $statement->bindParam(':activation_token', $activation_token);
          $statement->bindParam(':activation_expire', $activation_expire);
          $password = \Tools\UUID::v4();
          $statement->bindParam(':password', $password);
          $statement->bindParam(':firstname', $data['user']['firstname']);
          $statement->bindParam(':lastname', $data['user']['lastname']);
          $statement->bindParam(':email', $data['user']['email']);
          $statement->bindParam(':phone', $data['user']['phone']);
          $statement->bindParam(':active', $data['user']['active']);
          $statement->bindParam(':type', $data['type']);
          $statement->bindParam(':fk_center', $data['user']['centerID']);
          // Error check
          if(!$statement->execute()) {
            return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
          }
        }
        // update
        else {
          // Update user
          $statement = $this->DataInterface->DatabaseConnection->prepare(
            "UPDATE user SET firstname=:firstname, lastname=:lastname, email=:email, phone=:phone, active=:active, fk_center=:fk_center WHERE ID=:userID"
          );
          $statement->bindParam(':firstname', $data['user']['firstname']);
          $statement->bindParam(':lastname', $data['user']['lastname']);
          $statement->bindParam(':email', $data['user']['email']);
          $statement->bindParam(':phone', $data['user']['phone']);
          $statement->bindParam(':active', $data['user']['active']);
          $statement->bindParam(':fk_center', $data['user']['centerID']);
          $statement->bindParam(':userID', $userID);
          // Error check
          if(!$statement->execute()) {
            return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
          }
        }  
      }
      else if($data['type']=='reader') {
        // insert
        if($userID==0) {
          // Insert user
          $statement = $this->DataInterface->DatabaseConnection->prepare(
            "INSERT INTO user(activation_token, activation_expire, activation, password, firstname, lastname, email, phone, type, active, fk_center) 
                      VALUES(:activation_token, :activation_expire, NOW(), :password, :firstname, :lastname, :email, :phone, :type, :active, :fk_center)"
          );
          $activation_token = \Tools\UUID::v4();
          $activation_expire = date('Y-m-d H:i:s', strtotime(date('Y-m-d H:i:s'). ' + 1 days'));
          $statement->bindParam(':activation_token', $activation_token);
          $statement->bindParam(':activation_expire', $activation_expire);
          $password = \Tools\UUID::v4();
          $statement->bindParam(':password', $password);
          $statement->bindParam(':firstname', $data['user']['firstname']);
          $statement->bindParam(':lastname', $data['user']['lastname']);
          $statement->bindParam(':email', $data['user']['email']);
          $statement->bindParam(':phone', $data['user']['phone']);
          $statement->bindParam(':active', $data['user']['active']);
          $statement->bindParam(':type', $data['type']);
          $statement->bindParam(':fk_center', $data['user']['centerID']);
          // Error check
          if(!$statement->execute()) {
            return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
          }
          $fk_user = $this->DataInterface->DatabaseConnection->lastInsertId();

          // Insert organization
          $statement = $this->DataInterface->DatabaseConnection->prepare(
            "INSERT INTO organization VALUES(0, :fk_user, :name, :fk_country, :zip, :city, :address, :phone)"
          );
          $statement->bindParam(':fk_user', $fk_user);
          $statement->bindParam(':name', $data['user']['org_name']);
          $statement->bindParam(':fk_country', $data['user']['org_country']);
          $statement->bindParam(':zip', $data['user']['org_zip']);
          $statement->bindParam(':city', $data['user']['org_city']);
          $statement->bindParam(':address', $data['user']['org_address']);
          $statement->bindParam(':phone', $data['user']['org_phone']);
          // Error check
          if(!$statement->execute()) {
            $this->DataInterface->DatabaseConnection->rollback();
            return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
          }
        }
        // update
        else {
          // Update user
          $statement = $this->DataInterface->DatabaseConnection->prepare(
            "UPDATE user SET firstname=:firstname, lastname=:lastname, email=:email, phone=:phone, active=:active, fk_center=:fk_center WHERE ID=:userID"
          );
          $statement->bindParam(':firstname', $data['user']['firstname']);
          $statement->bindParam(':lastname', $data['user']['lastname']);
          $statement->bindParam(':email', $data['user']['email']);
          $statement->bindParam(':phone', $data['user']['phone']);
          $statement->bindParam(':active', $data['user']['active']);
          $statement->bindParam(':fk_center', $data['user']['centerID']);
          $statement->bindParam(':userID', $userID);
          // Error check
          if(!$statement->execute()) {
            return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
          }

          // Update organization
          $statement = $this->DataInterface->DatabaseConnection->prepare(
            "UPDATE organization SET name=:name, fk_country=:fk_country, zip=:zip, city=:city, address=:address, phone=:phone WHERE fk_user=:userID"
          );
          $statement->bindParam(':address', $data['user']['org_address']);
          $statement->bindParam(':city', $data['user']['org_city']);
          $statement->bindParam(':zip', $data['user']['org_zip']);
          $statement->bindParam(':name', $data['user']['org_name']);
          $statement->bindParam(':phone', $data['user']['org_phone']);
          $statement->bindParam(':fk_country', $data['user']['org_country']);
          $statement->bindParam(':userID', $userID);
          // Error check
          if(!$statement->execute()) {
            return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
          }
        }
      }

      // OK
      return array(
        'result' => 'OK',
        'data' => $data,
        'password_token' => $password
      );
    }    

    /**
     * 
     */
    public function CtAdminCROsPost($User, $data) {
      $type = 'cro';

      $userID = intval($data['userID']);
      // insert
      if($userID==0) {
        // Insert user
        $statement = $this->DataInterface->DatabaseConnection->prepare(
          "INSERT INTO user(activation_token, activation_expire, activation, password, firstname, lastname, email, phone, type, active) 
                    VALUES(:activation_token, :activation_expire, NOW(), :password, :firstname, :lastname, :email, :phone, :type, :active)"
        );
        $activation_token = \Tools\UUID::v4();
        $activation_expire = date('Y-m-d H:i:s', strtotime(date('Y-m-d H:i:s'). ' + 1 days'));
        $statement->bindParam(':activation_token', $activation_token);
        $statement->bindParam(':activation_expire', $activation_expire);
        $password = \Tools\UUID::v4();
        $statement->bindParam(':password', $password);
        $statement->bindParam(':firstname', $data['user']['firstname']);
        $statement->bindParam(':lastname', $data['user']['lastname']);
        $statement->bindParam(':email', $data['user']['email']);
        $statement->bindParam(':phone', $data['user']['phone']);
        $statement->bindParam(':active', $data['user']['active']);
        $statement->bindParam(':type', $type);
        // Error check
        if(!$statement->execute()) {
          return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
        }
      }
      // update
      else {
        // Update user
        $statement = $this->DataInterface->DatabaseConnection->prepare(
          "UPDATE user SET firstname=:firstname, lastname=:lastname, email=:email, phone=:phone, active=:active WHERE ID=:userID"
        );
        $statement->bindParam(':firstname', $data['user']['firstname']);
        $statement->bindParam(':lastname', $data['user']['lastname']);
        $statement->bindParam(':email', $data['user']['email']);
        $statement->bindParam(':phone', $data['user']['phone']);
        $statement->bindParam(':active', $data['user']['active']);
        $statement->bindParam(':userID', $userID);
        // Error check
        if(!$statement->execute()) {
          return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
        }
      }

      // OK
      return array(
        'result' => 'OK',
        'data' => $data,
        'userID' => $userID,
        'password_token' => $password
      );
    }    

    /**
     * 
     */
    public function ctAdminPasswordPost($data) {
      if (strlen($data['newPassword']) < 8 || 
          !preg_match("#[0-9]+#", $data['newPassword']) ||
          !preg_match("#[a-z]+#", $data['newPassword']) ||
          !preg_match("#[A-Z]+#", $data['newPassword'])) {
        return ['result' => 'ERROR', 'reason' => 'password_strength'];
      }
      
      $password = \Tools\Crypto::getHashPassword($data['newPassword']);
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "UPDATE user SET password = :newPassword WHERE password = :token"
      );
      $statement->bindParam(':newPassword', $password);
      $statement->bindParam(':token', $data['token']);
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }

      // 
      return [
        'result' => 'OK'
      ];
    }

    /**
     * 
     */
    public function ctAdminReaderPost($User, $data) {
      $userID = $User->ID;
      $readerID = $data['readerID'];
      $visitID = $data['visitID'];

      // Update
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "UPDATE visit SET fk_reader = $readerID WHERE ID = $visitID"
      );
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }

      // OK
      return array(
        'result' => 'OK'
      );
    }    

    /**
     * 
     */
    public function CtAdminCentersGet($User) {
      $userID = $User->ID;

      // countries
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT * FROM country ORDER BY name_en"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $countries = $statement->fetchAll(\PDO::FETCH_ASSOC);

      // select data
      $statement = $this->DataInterface->DatabaseConnection->prepare("
        SELECT ct_center.ID AS centerID, 
        organization.ID AS org_ID, 
        organization.name AS org_name, 
        organization.fk_country AS org_country, 
        organization.zip AS org_zip, 
        organization.city AS org_city, 
        organization.address AS org_address, 
        organization.phone AS org_phone, 
        probe.ID AS probe_ID, 
        probe.name AS probe_name, 
        probe.brand AS probe_brand, 
        probe.year AS probe_year, 
        probe.frequency AS probe_frequency 
        FROM organization, probe, ct_center 
        WHERE ct_center.fk_organization = organization.ID AND ct_center.fk_probe = probe.ID ORDER by stamp"
      );
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $centers = $statement->fetchAll(\PDO::FETCH_ASSOC);

      // OK
      return array(
        'result' => 'OK',
        'countries' => $countries,
        'center' => $centers
      );
    }    

    /**
     * 
     */
    public function CtAdminCentersPost($User, $data) {
      $ID_user = $User->ID;

      // Begin transaction
      $this->DataInterface->DatabaseConnection->beginTransaction();

      $centerID = intval($data['centerID']);
      // insert
      if($centerID==0) {
        // Insert organization
        $statement = $this->DataInterface->DatabaseConnection->prepare(
          "INSERT INTO organization VALUES(0, $ID_user, :name, :fk_country, :zip, :city, :address, :phone)"
        );
        $statement->bindParam(':name', $data['organization']['org_name']);
        $statement->bindParam(':fk_country', $data['organization']['org_country']);
        $statement->bindParam(':zip', $data['organization']['org_zip']);
        $statement->bindParam(':city', $data['organization']['org_city']);
        $statement->bindParam(':address', $data['organization']['org_address']);
        $statement->bindParam(':phone', $data['organization']['org_phone']);
        // Error check
        if(!$statement->execute()) {
          $this->DataInterface->DatabaseConnection->rollback();
          return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
        }
        $fk_organization = $this->DataInterface->DatabaseConnection->lastInsertId();

        // Insert probe
        $statement = $this->DataInterface->DatabaseConnection->prepare(
          "INSERT INTO probe VALUES(0, $ID_user, :name, :brand, '', :year, :frequency)"
        );
        $statement->bindParam(':name', $data['probe']['probe_name']);
        $statement->bindParam(':brand', $data['probe']['probe_brand']);
        $year = $data['probe']['probe_year']?$data['probe']['probe_year']:null;
        $statement->bindParam(':year', $year);
        $statement->bindParam(':frequency', $data['probe']['probe_frequency']);
        // Error check
        if(!$statement->execute()) {
          $this->DataInterface->DatabaseConnection->rollback();
          return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
        }
        $fk_probe = $this->DataInterface->DatabaseConnection->lastInsertId();

        // Insert center
        $statement = $this->DataInterface->DatabaseConnection->prepare(
          "INSERT INTO ct_center VALUES(0, $fk_organization, $fk_probe, NOW())"
        );
        // Error check
        if(!$statement->execute()) {
          $this->DataInterface->DatabaseConnection->rollback();
          return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
        }
      }
      // update
      else  {
        // Update organization
        $statement = $this->DataInterface->DatabaseConnection->prepare(
          "UPDATE organization SET name=:name, fk_country=:fk_country, zip=:zip, city=:city, address=:address, phone=:phone WHERE ID=:org_ID"
        );
        $statement->bindParam(':name', $data['organization']['org_name']);
        $statement->bindParam(':fk_country', $data['organization']['org_country']);
        $statement->bindParam(':zip', $data['organization']['org_zip']);
        $statement->bindParam(':city', $data['organization']['org_city']);
        $statement->bindParam(':address', $data['organization']['org_address']);
        $statement->bindParam(':phone', $data['organization']['org_phone']);
        $statement->bindParam(':org_ID', $data['organization']['org_ID']);
        // Error check
        if(!$statement->execute()) {
          $this->DataInterface->DatabaseConnection->rollback();
          return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
        }

        // Update probe
        $statement = $this->DataInterface->DatabaseConnection->prepare(
          "UPDATE probe SET name=:name, brand=:brand, year=:year, frequency=:frequency WHERE ID=:probe_ID"
        );
        $statement->bindParam(':name', $data['probe']['probe_name']);
        $statement->bindParam(':brand', $data['probe']['probe_brand']);
        $year = $data['probe']['probe_year']?$data['probe']['probe_year']:null;
        $statement->bindParam(':year', $year);
        $statement->bindParam(':frequency', $data['probe']['probe_frequency']);
        $statement->bindParam(':probe_ID', $data['probe']['probe_ID']);
        // Error check
        if(!$statement->execute()) {
          $this->DataInterface->DatabaseConnection->rollback();
          return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
        }        
      }

      // Commit
      $this->DataInterface->DatabaseConnection->commit();
      return [
        'result' => 'OK',
        'data' => $data
      ];
    }    
        
    /**
     * 
     */
    public function ctAdminSettingsPost($User, $data) {
      $userID = $User->ID;

      // update data
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "UPDATE clinical_trial SET number=:number, name=:name, start=:start, patient_expected=:patient_expected, center_count=:center_count, inclusion_month=:inclusion_month, followup_month=:followup_month, visit_per_patient=:visit_per_patient, visit_interval=:visit_interval, image_per_visit=:image_per_visit, video_per_visit=:video_per_visit, measure_per_timepoint=:measure_per_timepoint, comment=:comment, anon_percent=:anon_percent, fk_user=:fk_user WHERE 1"
      );
      $statement->bindParam(':number', $data['ct']['number']);
      $statement->bindParam(':name', addslashes($data['ct']['name']));
      $statement->bindParam(':start', $data['ct']['start']);
      $statement->bindParam(':patient_expected', $data['ct']['patient_expected']);
      $statement->bindParam(':center_count', $data['ct']['center_count']);
      $statement->bindParam(':inclusion_month', $data['ct']['inclusion_month']);
      $statement->bindParam(':followup_month', $data['ct']['followup_month']);
      $statement->bindParam(':visit_per_patient', $data['ct']['visit_per_patient']);
      $statement->bindParam(':visit_interval', $data['ct']['visit_interval']);
      $statement->bindParam(':image_per_visit', $data['ct']['image_per_visit']);
      $statement->bindParam(':video_per_visit', $data['ct']['video_per_visit']);
      $statement->bindParam(':measure_per_timepoint', $data['ct']['measure_per_timepoint']);
      $statement->bindParam(':comment', addslashes($data['ct']['comment']));
      $statement->bindParam(':anon_percent', $data['ct']['anon_percent']);
      $statement->bindParam(':fk_user', $userID);
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }

      // OK
      return array(
        'result' => 'OK',
        'data' => $data
      );
    }    

    /**
     * Get pacs data.
     */
    public function ctAdminPacsGet($User) {
      $userID = $User->ID;

      // centers
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT ct_center.ID, organization.name AS org_name, probe.name AS probe_name FROM organization, probe, ct_center WHERE ct_center.fk_organization = organization.ID AND ct_center.fk_probe = probe.ID ORDER by stamp"
      );
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $centers = $statement->fetchAll(\PDO::FETCH_ASSOC);

      // pacs
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT ID, data FROM settings_pacs WHERE fk_physician IS NULL AND fk_center IS NOT NULL ORDER BY ID"
      );
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $pacs = $statement->fetchAll(\PDO::FETCH_ASSOC);
      foreach($pacs as &$p) {
        $p['data'] = json_decode($p['data']);
      }

      // OK
      return array(
        'result' => 'OK',
        'centers' => $centers,
        'ourAET' => 'IIMT',
        'pacs' => $pacs
      );
    }

    /**
     * 
     */
    public function ctAdminEchoPost($User, $data) {
      $cmdLine = 'echoscu '.$data['serverAddress'].' '.$data['queryPort'].' -v 2>&1';

      $output=null;
      $retval=null;
      exec($cmdLine, $output, $retval);
      if($retval !== 0 || count($output)<1) {
        return [
          'result' => 'ERROR',
          'cmdLine' => $cmdLine,
          'output' => $output, 
          'retval' => $retval
        ];
      }

      return [
        'result' => 'OK',
        'output' => $output
      ];
    }

    /**
     * Post pacs data.
     */
    public function ctAdminPacsPost($User, $data) {
      $userID = $User->ID;
      $fk_center = $data['data']['centerID'];

      // insert
      if(intval($data['data']['PACSID'])==0) {
        $statement = $this->DataInterface->DatabaseConnection->prepare(
          "INSERT INTO settings_pacs VALUES(0, :data, NULL, :fk_center)"
        );
        unset($data['data']['PACSID']);
        $data = json_encode($data['data'], JSON_NUMERIC_CHECK);
        $statement->bindParam(':data', $data);
        $statement->bindParam(':fk_center', $fk_center);
        // Error check
        if(!$statement->execute()) {
          return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
        }
      }
      // update
      else {
        $statement = $this->DataInterface->DatabaseConnection->prepare(
          "UPDATE settings_pacs SET data=:data, fk_center=:fk_center WHERE ID = ".$data['data']['PACSID']
        );
        unset($data['data']['PACSID']);
        $data = json_encode($data['data'], JSON_NUMERIC_CHECK);
        $statement->bindParam(':data', $data);
        $statement->bindParam(':fk_center', $fk_center);
        // Error check
        if(!$statement->execute()) {
          return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
        }
      }

      // OK
      return array(
        'result' => 'OK',
        'data' => $data
      );
    }
    
    /**
     * Auditlog data.
     */
    public function ctAdminAuditLogPost($User, $data) {
      $userID = $User->ID;
      $start = $data['start'];
      $end = $data['end'];

      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT A.*, R.* FROM iimt_mathcloud_audit.ray R, iimt_mathcloud_audit.activity A WHERE A.ID_ray = R.ID AND DATE(A.stamp) >= '$start' AND DATE(A.stamp) <= '$end' ORDER BY A.stamp"
      );
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $data = $statement->fetchAll();

      $header = [
        'Activity ID', 'Ray ID', 'Activity data', 'Activity stamp',
        'Ray ID', 'User agent', 'API key', 'IP data', 'Location data', 'User data', 'Activity stamp'
      ];
      $header = implode(';', $header);

      $res = [];
      $res[] = $header;
      foreach($data as $d) {
        $line = implode(';', [
          '"'.$d[0].'"', '"'.$d[1].'"', '"'.$d[2].'"', '"'.$d[3].'"', '"'.$d[4].'"', '"'.$d[5].'"', '"'.$d[6].'"', '"'.$d[7].'"', '"'.$d[8].'"', '"'.$d[9].'"', '"'.$d[10].'"'
        ]);
        $res[] = $line;
      }

      // OK
      return array(
        'result' => 'OK',
        'data' => base64_encode(implode("\n", $res)),
        'start' => $start,
        'end' => $end
      );
    }

    /**
     * eCRF data.
     */
    public function ctAdminAuditECRFPost($User, $data) {
      $userID = $User->ID;

      // CT general data
      $statement = $this->DataInterface->DatabaseConnection->prepare("
        SELECT * FROM clinical_trial
      ");
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $clinical_trial = $statement->fetchAll(\PDO::FETCH_ASSOC)[0];

      $od = '../../storage/tmp/'.$clinical_trial['number'].'/';
      \Tools\FS::mkpath($od);

      $data = [];
      // header
      $data[] = 
        'Visit_PatientID,Visit_Date,Visit_Created,Visit_Completion,Visit_Area,'.
        'Visit_ReaderID,Visit_InvestigatorID,Visit_CenterName,'.
        'Media_Location,Media_Incidence,Media_Filename,Media_Width,Media_Height,Media_PixelWidth,Media_PixelHeight,Media_FrameCount,Media_FramePerSecond,'.
        'Measure_Created,Measure_Frame,Measure_Distance,Measure_ImtMean,Measure_ImtMax,Measure_ImtStddev,Measure_IntimaMean,Measure_MediaMean,Measure_NearWall,Measure_QualityIndex,Measure_NumberOfPoints';

      // visit
      $statement = $this->DataInterface->DatabaseConnection->prepare("
        SELECT patient.ctPatientID, patient.fk_user AS fk_investigator, visit.* 
        FROM patient, visit 
        WHERE patient.ID = visit.fk_patient 
        AND visit.completed IS NOT NULL
        AND visit.area = 'carotid'
      ");
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $visits = $statement->fetchAll(\PDO::FETCH_ASSOC);
      foreach($visits as $visit) {
        // center
        $fk_investigator = $visit['fk_investigator'];
        $statement = $this->DataInterface->DatabaseConnection->prepare("
          SELECT organization.name  
          FROM user, organization, ct_center
          WHERE user.ID = $fk_investigator 
          AND user.fk_center = ct_center.ID 
          AND ct_center.fk_organization = organization.ID 
        ");
        // Error check
        if(!$statement->execute()) {
          return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
        }
        $center = $statement->fetchAll(\PDO::FETCH_ASSOC)[0];

        $fk_visit = $visit['ID'];
        // media
        $statement = $this->DataInterface->DatabaseConnection->prepare("
          SELECT media.* 
          FROM media
          WHERE fk_visit = $fk_visit
        ");
        // Error check
        if(!$statement->execute()) {
          return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
        }
        $medias = $statement->fetchAll(\PDO::FETCH_ASSOC);
        foreach($medias as $media) {
          $fk_media = $media['ID'];
          // measure
          $statement = $this->DataInterface->DatabaseConnection->prepare("
            SELECT measure.* 
            FROM measure
            WHERE fk_media = $fk_media 
            AND type = 'imt'
          ");
          // Error check
          if(!$statement->execute()) {
            return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
          }
          $measures = $statement->fetchAll(\PDO::FETCH_ASSOC);

          // save file
          if(count($measures)) {
            copy('../../storage/media/'.$visit['ID'].'/'.$media['filename'], $od.'/'.$media['filename']);
          }
          
          foreach($measures as $measure) {
            $V['Visit_PatientID'] = $visit['ctPatientID'];
            $V['Visit_Date'] = $visit['visitDate'];
            $V['Visit_Created'] = $visit['created'];
            $V['Visit_Completion'] = $visit['completed'];
            $V['Visit_Area'] = $visit['area'];

            $V['Visit_ReaderID'] = $visit['fk_reader'];
            $V['Visit_InvestigatorID'] = $visit['fk_investigator'];
            $V['Visit_CenterName'] = $center['name'];

            $V['Media_Location'] = $media['location'];
            $V['Media_Incidence'] = $media['incidence'];
            $V['Media_Filename'] = $media['filename'];
            $metrics = json_decode($media['metrics']);
            $V['Media_Width'] = $metrics->width;
            $V['Media_Height'] = $metrics->height;
            $V['Media_PixelWidth'] = $metrics->pxwidth;
            $V['Media_PixelHeight'] = $metrics->pxheight;
            $V['Media_FrameCount'] = $metrics->frameCount;
            $V['Media_FramePerSecond'] = $metrics->fps;

            $V['Measure_Created'] = $measure['created'];
            $V['Measure_Frame'] = $measure['frame'];
            $computation = json_decode($measure['computation']);
            $V['Measure_Distance'] = $computation->distance;
            $V['Measure_ImtMean'] = $computation->imt_mean;
            $V['Measure_ImtMax'] = $computation->imt_max;
            $V['Measure_ImtStddev'] = $computation->imt_stddev;
            $V['Measure_IntimaMean'] = $computation->intima_mean;
            $V['Measure_MediaMean'] = $computation->media_mean;
            $V['Measure_Location'] = $computation->nearWall?'Proximal':'Distal';
            $V['Measure_QualityIndex'] = $computation->qualityIndex;
            $V['Measure_NumberOfPoints'] = $computation->numberOfPoints;
    
            //$data[] = $V;
            $data[] = implode(",", $V);
          }
        }
      }

      unlink('../../storage/tmp/'.$clinical_trial['number']);

      // make archive
      $dst = '../../storage/tmp/'.$clinical_trial['number'].'.zip';
      unlink($dst);

      $cmdLine = 'cd ../../storage/tmp && zip -r '.$clinical_trial['number'].'.zip '.$clinical_trial['number'].'/ 2>&1';
      $output=null;
      $retval=null;
      exec($cmdLine, $output, $retval);
      // error
      if($retval !== 0 || count($output)<1) {
        return [
          'result' => 'ERROR',
          'cmdLine' => $cmdLine,
          'output' => $output, 
          'retval' => $retval
        ];
      }

      // make csv
      unlink('../../storage/tmp/'.$clinical_trial['number'].'.csv');
      file_put_contents('../../storage/tmp/'.$clinical_trial['number'].'.csv', implode("\n", $data));

      // OK
      return array(
        'result' => 'OK',
        'clinical_trial' => $clinical_trial,
        //'data' => $data,
        'csv' => 'tmp/'.$clinical_trial['number'].'.csv',
        'zip' => 'tmp/'.$clinical_trial['number'].'.zip'
      );
    }
    
    /**
     * 
     */
    public function ctAdminOverviewGet($User) {
      $userID = $User->ID;

      // settings
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT * FROM clinical_trial"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $settings = $statement->fetchAll(\PDO::FETCH_ASSOC)[0];

      // active centers
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT DISTINCT(ct_center.ID) FROM ct_center, user, patient WHERE patient.fk_user = user.ID AND user.type = 'investigator' AND user.fk_center = ct_center.ID"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $activeCenters = count($statement->fetchAll(\PDO::FETCH_ASSOC));

      // included patients
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT COUNT(patient.ID) AS cnt FROM patient, user WHERE patient.fk_user = user.ID AND user.type = 'investigator'"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $includedPatients = $statement->fetchAll(\PDO::FETCH_ASSOC)[0]['cnt'];

      // completed timepoints
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT COUNT(visit.ID) FROM visit, user WHERE visit.fk_reader = user.ID AND user.type = 'reader' AND visit.completed IS NOT NULL"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $completedTimepoints = $statement->fetchAll(\PDO::FETCH_ASSOC)[0]['cnt'];

      // patients & timepoints
      $statement = $this->DataInterface->DatabaseConnection->prepare("
        SELECT DATE_FORMAT(patient.created, '%Y-%m') AS m, COUNT(patient.ID) AS patients 
        FROM patient, user 
        WHERE patient.fk_user = user.ID AND user.type = 'investigator' 
        GROUP BY DATE_FORMAT(patient.created, '%Y-%m')
      ");
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $resPatients = $statement->fetchAll(\PDO::FETCH_ASSOC);

      $statement = $this->DataInterface->DatabaseConnection->prepare("
        SELECT DATE_FORMAT(visit.created, '%Y-%m') AS m, COUNT(visit.ID) AS timepoints  
        FROM patient, visit, user 
        WHERE visit.fk_patient = patient.ID AND patient.fk_user = user.ID AND user.type = 'investigator' 
        GROUP BY DATE_FORMAT(visit.created, '%Y-%m')
      ");
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $resVisits = $statement->fetchAll(\PDO::FETCH_ASSOC);

      $patientsTimepoints = [];
      $start    = (new \DateTime($resPatients[0]['m'].'-01'))->modify('first day of this month');
      $end      = (new \DateTime(date('Y-m-d')))->modify('first day of next month');
      $interval = \DateInterval::createFromDateString('1 month');
      $period   = new \DatePeriod($start, $interval, $end);
      foreach($period as $dt) {
        $patientsTimepoints[] = array('m' => $dt->format("Y-m"), 'patients' => 0, 'timepoints' => 0);
      }
      foreach($resPatients as $R) {
        foreach($patientsTimepoints as &$PT) {
          if($PT['m'] == $R['m']) {
            $PT['patients'] = intval($R['patients']);
          }
        }
      }
      foreach($resVisits as $R) {
        foreach($patientsTimepoints as &$PT) {
          if($PT['m'] == $R['m']) {
            $PT['timepoints'] = intval($R['timepoints']);
          }
        }
      }

      // readers & measures
      $statement = $this->DataInterface->DatabaseConnection->prepare("
        SELECT DATE_FORMAT(measure.created, '%Y-%m') AS m, COUNT(measure.ID) AS measures, COUNT(DISTINCT measure.fk_user) AS readers  
        FROM measure, user  
        WHERE measure.fk_user = user.ID AND user.type = 'reader' 
        GROUP BY DATE_FORMAT(measure.created, '%Y-%m')
      ");
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $resMeasures = $statement->fetchAll(\PDO::FETCH_ASSOC);

      $readersMeasures = [];
      $start    = (new \DateTime($resMeasures[0]['m'].'-01'))->modify('first day of this month');
      $end      = (new \DateTime(date('Y-m-d')))->modify('first day of next month');
      $interval = \DateInterval::createFromDateString('1 month');
      $period   = new \DatePeriod($start, $interval, $end);
      foreach($period as $dt) {
        $readersMeasures[] = array('m' => $dt->format("Y-m"), 'readers' => 0, 'measures' => 0);
      }
      foreach($resMeasures as $R) {
        foreach($readersMeasures as &$RM) {
          if($RM['m'] == $R['m']) {
            $RM['measures'] = intval($R['measures']);
            $RM['readers'] = intval($R['readers']);
          }
        }
      }

      // OK
      return array(
        'result' => 'OK',
        'settings' => $settings,
        'activeCenters' => $activeCenters,
        'includedPatients' => $includedPatients,
        'completedTimepoints' => $completedTimepoints,
        'patientsTimepoints' => $patientsTimepoints,
        'readersMeasures' => $readersMeasures
      );
    }

    /**
     * 
     */
    public function ctAdminInvestigatorsGet($User, $ID) {
      $userID = $User->ID;

      // select users
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT * FROM user WHERE type = 'investigator'"
      );
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $users = $statement->fetchAll(\PDO::FETCH_ASSOC);

      $userData = [];
      foreach($users as &$U) {
        // select visits
        $statement = $this->DataInterface->DatabaseConnection->prepare("
          SELECT 
            patient.ctPatientID, 
            V.*, 
            (SELECT COUNT(media.ID) FROM media WHERE JSON_VALUE(media.metrics, '$.fps') IS NULL AND media.fk_visit = V.ID) as imageCount, 
            (SELECT COUNT(media.ID) FROM media WHERE media.fk_visit = V.ID) as mediaCount 
          FROM patient, visit V 
          WHERE V.fk_patient = patient.ID AND patient.fk_user = ".$U['ID']." 
          ORDER BY V.visitDate DESC
        ");
        // Error check
        if(!$statement->execute()) {
          return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
        }
        $userData[$U['ID']]['visits'] = $statement->fetchAll(\PDO::FETCH_ASSOC);
        
        /*foreach($userData[$U['ID']]['visits'] as &$V) {
          // select media
          $statement = $this->DataInterface->DatabaseConnection->prepare("
            SELECT * FROM media 
            WHERE fk_visit = ".$V['ID']."
          ");
          // Error check
          if(!$statement->execute()) {
            return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
          }
          $V['media'] = $statement->fetchAll(\PDO::FETCH_ASSOC);
          foreach($V['media'] as &$M) {
            $M['metrics'] = json_decode($M['metrics']);
            // select measure
            $statement = $this->DataInterface->DatabaseConnection->prepare("
              SELECT * FROM measure 
              WHERE fk_media = ".$M['ID']."
            ");
            // Error check
            if(!$statement->execute()) {
              return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
            }
            $M['measure'] = $statement->fetchAll(\PDO::FETCH_ASSOC);
            foreach($M['measure'] as &$MS) {
              $MS['computation'] = json_decode($MS['computation']);
            }
          }
        }*/
      }
      
      // OK
      return [
        'result' => 'OK', 
        'ID' => $ID,
        'users' => $users,
        'userData' => $userData
      ];
    }

    /**
     * 
     */
    public function ctAdminReadersGet($User, $ID) {
      $userID = $User->ID;

      // select users
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT * FROM user WHERE type = 'reader'"
      );
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $users = $statement->fetchAll(\PDO::FETCH_ASSOC);

      $userData = [];
      foreach($users as &$U) {
        // select visits
        $statement = $this->DataInterface->DatabaseConnection->prepare("
          SELECT 
            P.ctPatientID, 
            V.*, 
            (SELECT COUNT(media.ID) FROM media WHERE JSON_VALUE(media.metrics, '$.fps') IS NULL AND media.fk_visit = V.ID) as imageCount, 
            (SELECT COUNT(media.ID) FROM media WHERE media.fk_visit = V.ID) as mediaCount, 
            (SELECT MAX(measure.created) FROM measure, media WHERE media.fk_visit = V.ID AND media.ID = measure.fk_media) as measureLastDate
          FROM patient P, visit V 
          WHERE V.fk_patient = P.ID AND V.fk_reader = ".$U['ID']." 
          ORDER BY V.visitDate DESC
        ");
        // Error check
        if(!$statement->execute()) {
          return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
        }
        $userData[$U['ID']]['visits'] = $statement->fetchAll(\PDO::FETCH_ASSOC);
        
        foreach($userData[$U['ID']]['visits'] as &$V) {
          // select media
          $statement = $this->DataInterface->DatabaseConnection->prepare("
            SELECT * FROM media 
            WHERE fk_visit = ".$V['ID']."
          ");
          // Error check
          if(!$statement->execute()) {
            return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
          }
          $V['media'] = $statement->fetchAll(\PDO::FETCH_ASSOC);
          foreach($V['media'] as &$M) {
            $M['metrics'] = json_decode($M['metrics']);
            // select measure
            $statement = $this->DataInterface->DatabaseConnection->prepare("
              SELECT * FROM measure 
              WHERE fk_media = ".$M['ID']."
            ");
            // Error check
            if(!$statement->execute()) {
              return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
            }
            $M['measure'] = $statement->fetchAll(\PDO::FETCH_ASSOC);
            foreach($M['measure'] as &$MS) {
              $MS['computation'] = json_decode($MS['computation']);
            }
          }
        }
      }
      
      // OK
      return [
        'result' => 'OK', 
        'ID' => $ID,
        'users' => $users,
        'userData' => $userData
      ];
    }
    
    
  }
}