MAth-Cloud-3.0.0/api.ipsocloud.com/api/v1/Models/PatientInterface.class.php

<?php

namespace Models {

  require_once 'Models/User.class.php';
  require_once 'Tools/Random.class.php';
  
  class PatientInterface {
    //
    protected $DataInterface;

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

    /**
     * Get patient files existing data.
     */
    public function patientFilesExistingGet($User) {
      $ID_user = $User->ID;

      $where = ' P.ID = V.fk_patient ';
      if($User->type=='physician') {
        $where .= ' AND P.fk_user = '.$User->ID;
      }
      else if($User->type=='reader') {
        $where .= ' AND V.fk_reader = '.$User->ID.' AND V.completed IS NULL ';
      }
      else {
        $where .= ' AND P.fk_user = '.$User->ID;
      }

      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT JSON_EXTRACT(data, '$.patientListFields') AS data FROM settings"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $settings = json_decode($statement->fetchAll(\PDO::FETCH_ASSOC)[0]['data']);
      $patientListFields=[];
      foreach($settings as $S) {
        $patientListFields[str_replace(' ', '_', $S->name)] = $S->display;
      }

      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT 
         P.*, 
         (SELECT COUNT(visit.ID) FROM visit WHERE fk_patient = P.ID) as visitCount, 
         (SELECT GROUP_CONCAT(CONCAT(visit.ID, ',', visit.visitDate, ',', COALESCE(visit.area, '')) ORDER BY visit.visitDate DESC SEPARATOR ';') FROM visit WHERE fk_patient = P.ID) as visits, 
         (SELECT GROUP_CONCAT(CONCAT(visit.ID, ',', visit.number) ORDER BY visit.visitDate DESC SEPARATOR ';') FROM visit WHERE fk_patient = P.ID) as reader_visits, 
         (SELECT COUNT(media.ID) FROM media, visit WHERE JSON_VALUE(media.metrics, '$.fps') IS NULL AND media.fk_visit = visit.ID AND visit.fk_patient = P.ID) as imageCount, 
         (SELECT COUNT(media.ID) FROM media, visit WHERE media.fk_visit = visit.ID AND visit.fk_patient = P.ID) as mediaCount, 
         V.visitDate AS lastVisit, 
         V.number AS visitNumber, 
         V.ID AS visitID 
         FROM patient P, visit V
         WHERE $where 
         GROUP BY P.ID 
         ORDER BY V.visitDate DESC, V.ID DESC"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $visits = $statement->fetchAll(\PDO::FETCH_ASSOC);

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

    /**
     * Get patient files existing data.
     */
    public function patientFilesExistingPost($User, $data) {
      $ID_user = $User->ID;

      $where = ' P.ID = V.fk_patient ';
      if($User->type=='physician') {
        $where .= ' AND P.fk_user = '.$User->ID;
      }
      else if($User->type=='reader') {
        $where .= ' AND V.fk_reader = '.$User->ID.' AND V.completed IS NULL ';
      }
      else {
        $where .= ' AND P.fk_user = '.$User->ID;
      }

      $filter = '(P.lastname LIKE \'%'.$data['filter'].'%\' OR P.firstname LIKE \'%'.$data['filter'].'%\')';

      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT JSON_EXTRACT(data, '$.patientListFields') AS data FROM settings"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $settings = json_decode($statement->fetchAll(\PDO::FETCH_ASSOC)[0]['data']);
      $patientListFields=[];
      foreach($settings as $S) {
        $patientListFields[str_replace(' ', '_', $S->name)] = $S->display;
      }

      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT 
         P.*, 
         (SELECT COUNT(visit.ID) FROM visit WHERE fk_patient = P.ID) as visitCount, 
         (SELECT GROUP_CONCAT(CONCAT(visit.ID, ',', visit.visitDate, ',', COALESCE(visit.area, '')) ORDER BY visit.visitDate DESC SEPARATOR ';') FROM visit WHERE fk_patient = P.ID) as visits, 
         (SELECT GROUP_CONCAT(CONCAT(visit.ID, ',', visit.number) ORDER BY visit.visitDate DESC SEPARATOR ';') FROM visit WHERE fk_patient = P.ID) as reader_visits, 
         (SELECT COUNT(media.ID) FROM media, visit WHERE JSON_VALUE(media.metrics, '$.fps') IS NULL AND media.fk_visit = visit.ID AND visit.fk_patient = P.ID) as imageCount, 
         (SELECT COUNT(media.ID) FROM media, visit WHERE media.fk_visit = visit.ID AND visit.fk_patient = P.ID) as mediaCount, 
         V.visitDate AS lastVisit, 
         V.number AS visitNumber, 
         V.ID AS visitID 
         FROM patient P, visit V
         WHERE $where AND $filter 
         GROUP BY P.ID 
         ORDER BY V.visitDate DESC, V.ID DESC"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $visits = $statement->fetchAll(\PDO::FETCH_ASSOC);

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

    /**
     * Get patient files new data.
     */
    public function patientFilesNewGet($User, $lang) {
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT JSON_EXTRACT(data, '$.newPatientFields') AS data FROM settings"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $settings = json_decode($statement->fetchAll(\PDO::FETCH_ASSOC)[0]['data']);
      $patientListFields=[];
      foreach($settings as $S) {
        $patientListFields[str_replace(' ', '_', $S->name)] = $S->display;
        $patientListFields[str_replace(' ', '_', $S->name).'_required'] = $S->required;
      }

      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT *, name_$lang AS name FROM country ORDER BY name_$lang"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $countries = $statement->fetchAll(\PDO::FETCH_ASSOC);
      return [
        'result' => 'OK', 
        'countries' => $countries,
        'patientID' => \Tools\Random::getString(10),
        'visitNumber' => 1,
        'patientListFields' => $patientListFields
      ];
    }

    /**
     * Get patient files pacs data.
     */
    public function patientFilesPacsGet($User) {
      // 
      return [
        'result' => 'OK'
      ];
    }

    /**
     * Post patient data.
     */
    public function patientCreatePost($User, $data) {
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT JSON_EXTRACT(data, '$.newPatientFields') AS data FROM settings"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $settings = json_decode($statement->fetchAll(\PDO::FETCH_ASSOC)[0]['data']);
      $patientListFields=[];
      foreach($settings as $S) {
        $patientListFields[str_replace(' ', '_', $S->name)] = $S->required;
      }

      if($data['type']=='investigator' && empty($data['patient']['patientID'])) {
        return ['result' => 'ERROR', 'reason' => 'patientID'];
      }
      if($data['type']=='investigator' && empty($data['patient']['visitNumber'])) {
        return ['result' => 'ERROR', 'reason' => 'visitNumber'];
      }
      if($patientListFields['Visit_date'] && empty($data['patient']['visitDate'])) {
        return ['result' => 'ERROR', 'reason' => 'visitDate'];
      }
      if(empty($data['patient']['visitDate'])) {
        $data['patient']['visitDate']=date('Y-m-d');
      }
      if($patientListFields['Firstname'] && empty($data['patient']['firstname'])) {
        return ['result' => 'ERROR', 'reason' => 'firstname'];
      }
      if(empty($data['patient']['firstname'])) {
        $data['patient']['firstname']='';
      }
      if($patientListFields['Lastname'] && empty($data['patient']['lastname'])) {
        return ['result' => 'ERROR', 'reason' => 'lastname'];
      }
      if(empty($data['patient']['lastname'])) {
        $data['patient']['lastname']='';
      }
      if($patientListFields['Sex'] && empty($data['patient']['gender'])) {
        return ['result' => 'ERROR', 'reason' => 'gender'];
      }
      if($patientListFields['Birthdate'] && empty($data['patient']['birthDate'])) {
        return ['result' => 'ERROR', 'reason' => 'birthDate'];
      }
      if(empty($data['patient']['birthDate'])) {
        $data['patient']['birthDate']=date('Y-m-d');
      }
      if($patientListFields['Birth_country'] && empty($data['patient']['birthCountry'])) {
        return ['result' => 'ERROR', 'reason' => 'birthCountry'];
      }
      if($patientListFields['Residence_country'] && empty($data['patient']['residenceCountry'])) {
        return ['result' => 'ERROR', 'reason' => 'residenceCountry'];
      }
      if($patientListFields['Height'] && empty($data['patient']['height'])) {
        return ['result' => 'ERROR', 'reason' => 'height'];
      }
      if($patientListFields['Weight'] && empty($data['patient']['weight'])) {
        return ['result' => 'ERROR', 'reason' => 'weight'];
      }
      if(empty($data['patient']['height'])) {
        $data['patient']['height'] = null;
      }
      if(empty($data['patient']['weight'])) {
        $data['patient']['weight'] = null;
      }

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

      // Insert patient
      if($data['type']=='investigator') {
        $statement = $this->DataInterface->DatabaseConnection->prepare(
          "INSERT INTO patient(patientID, ctPatientID, firstname, lastname, gender, birthDate, height, weight, race, fk_birthCountry, fk_residenceCountry, fk_user) VALUES(:patientID, :ctPatientID, :firstname, :lastname, :gender, :birthDate, :height, :weight, :race, :fk_birthCountry, :fk_residenceCountry, :fk_user)"
        );
        $statement->bindParam(':patientID', $data['patient']['patientID']);
        $statement->bindParam(':ctPatientID', $data['patient']['patientID']);
      }
      else if($data['type']=='physician') {
        $statement = $this->DataInterface->DatabaseConnection->prepare(
          "INSERT INTO patient(patientID, firstname, lastname, gender, birthDate, height, weight, race, fk_birthCountry, fk_residenceCountry, fk_user) VALUES(:patientID, :firstname, :lastname, :gender, :birthDate, :height, :weight, :race, :fk_birthCountry, :fk_residenceCountry, :fk_user)"
        );
        $statement->bindParam(':patientID', $data['patient']['patientID']);
      }
      $statement->bindParam(':firstname', $data['patient']['firstname']);
      $statement->bindParam(':lastname', $data['patient']['lastname']);
      $statement->bindParam(':gender', $data['patient']['gender']);
      $statement->bindParam(':birthDate', $data['patient']['birthDate']);
      $statement->bindParam(':height', $data['patient']['height']);
      $statement->bindParam(':weight', $data['patient']['weight']);
      $statement->bindParam(':race', $data['patient']['patientRace']);
      $statement->bindParam(':fk_birthCountry', $data['patient']['birthCountry']);
      $statement->bindParam(':fk_residenceCountry', $data['patient']['residenceCountry']);
      $statement->bindParam(':fk_user', $User->ID);
      // Error check
      if(!$statement->execute()) {
        $this->DataInterface->DatabaseConnection->rollback();
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo(), 'data2' => $data];
      }
      $fk_patient = $this->DataInterface->DatabaseConnection->lastInsertId();

      // Insert visit
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "INSERT INTO visit(number, visitDate, fk_patient) VALUES(:number, :visitDate, :fk_patient)"
      );
      $statement->bindParam(':number', $data['patient']['visitNumber']);
      $statement->bindParam(':visitDate', $data['patient']['visitDate']);
      $statement->bindParam(':fk_patient', $fk_patient);
      // Error check
      if(!$statement->execute()) {
        $this->DataInterface->DatabaseConnection->rollback();
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $fk_visit = $this->DataInterface->DatabaseConnection->lastInsertId();

      // Insert context
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "INSERT INTO context(fk_visit) VALUES(:fk_visit)"
      );
      $statement->bindParam(':fk_visit', $fk_visit);
      // 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',
        'fk_patient' => $fk_patient,
        'fk_visit' => $fk_visit
      ];
    }

    /**
     * Post patient data modification.
     */
    public function patientModifyPost($User, $data) {
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT JSON_EXTRACT(data, '$.newPatientFields') AS data FROM settings"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $settings = json_decode($statement->fetchAll(\PDO::FETCH_ASSOC)[0]['data']);
      $patientListFields=[];
      foreach($settings as $S) {
        $patientListFields[str_replace(' ', '_', $S->name)] = $S->required;
      }

      if(empty($data['patient']['patientID'])) {
        return ['result' => 'ERROR', 'reason' => 'patientID'];
      }
      if($patientListFields['Firstname'] && empty($data['patient']['firstname'])) {
        return ['result' => 'ERROR', 'reason' => 'firstname'];
      }
      if(empty($data['patient']['firstname'])) {
        $data['patient']['firstname']='';
      }
      if($patientListFields['Lastname'] && empty($data['patient']['lastname'])) {
        return ['result' => 'ERROR', 'reason' => 'lastname'];
      }
      if(empty($data['patient']['lastname'])) {
        $data['patient']['lastname']='';
      }
      if($patientListFields['Sex'] && empty($data['patient']['gender'])) {
        return ['result' => 'ERROR', 'reason' => 'gender'];
      }
      if($patientListFields['Birthdate'] && empty($data['patient']['birthDate'])) {
        return ['result' => 'ERROR', 'reason' => 'birthDate'];
      }
      if(empty($data['patient']['birthDate'])) {
        $data['patient']['birthDate']=date('Y-m-d');
      }
      if($patientListFields['Birth_country'] && empty($data['patient']['birthCountry'])) {
        return ['result' => 'ERROR', 'reason' => 'birthCountry'];
      }
      if($patientListFields['Residence_country'] && empty($data['patient']['residenceCountry'])) {
        return ['result' => 'ERROR', 'reason' => 'residenceCountry'];
      }
      if($patientListFields['Height'] && empty($data['patient']['height'])) {
        return ['result' => 'ERROR', 'reason' => 'height'];
      }
      if($patientListFields['Weight'] && empty($data['patient']['weight'])) {
        return ['result' => 'ERROR', 'reason' => 'weight'];
      }
      if(empty($data['patient']['height'])) {
        $data['patient']['height'] = null;
      }
      if(empty($data['patient']['weight'])) {
        $data['patient']['weight'] = null;
      }

      // Insert patient
      $patientID = $data['patient']['patientID'];
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "UPDATE patient SET firstname=:firstname, lastname=:lastname, gender=:gender, birthDate=:birthDate, height=:height, weight=:weight, race=:race, fk_birthCountry=:fk_birthCountry, fk_residenceCountry=:fk_residenceCountry WHERE patientID='$patientID'"
      );
      $statement->bindParam(':firstname', $data['patient']['firstname']);
      $statement->bindParam(':lastname', $data['patient']['lastname']);
      $statement->bindParam(':gender', $data['patient']['gender']);
      $statement->bindParam(':birthDate', $data['patient']['birthDate']);
      $statement->bindParam(':height', $data['patient']['height']);
      $statement->bindParam(':weight', $data['patient']['weight']);
      $statement->bindParam(':race', $data['patient']['patientRace']);
      $statement->bindParam(':fk_birthCountry', $data['patient']['birthCountry']);
      $statement->bindParam(':fk_residenceCountry', $data['patient']['residenceCountry']);
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      return [
        'result' => 'OK'
      ];
    }
    
    /**
     * Post patient data.
     */
    public function patientCreateVisitPost($User, $data) {
      // Count visit
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT COUNT(ID) AS cnt FROM visit WHERE fk_patient = :fk_patient"
      );
      $statement->bindParam(':fk_patient', $data['patientID']);
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $visitCount = intval($statement->fetchAll(\PDO::FETCH_ASSOC)[0]['cnt']) + 1;

      // Insert visit
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "INSERT INTO visit(number, visitDate, fk_patient) VALUES(:number, NOW(), :fk_patient)"
      );
      $statement->bindParam(':number', $visitCount);
      $statement->bindParam(':fk_patient', $data['patientID']);
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $fk_visit = $this->DataInterface->DatabaseConnection->lastInsertId();

      // Insert context
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "INSERT INTO context(fk_visit) VALUES(:fk_visit)"
      );
      $statement->bindParam(':fk_visit', $fk_visit);
      // Error check
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }

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

    /**
     * Post patient context data.
     */
    public function patientContextPost($User, $data) {
      $field = $data['what'];
      $values = json_encode($data['data']);
      $fk_visit = $data['patient']['visitID'];
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "UPDATE context SET $field = '$values' WHERE fk_visit = $fk_visit"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      return [
        'result' => 'OK',
        'field' => $data['what'],
        'values' => $data['data']
      ];
    }

    /**
     * Get patient fix data.
     */
    public function patientFixGet($User, $patientID, $lang) {
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT JSON_EXTRACT(data, '$.newPatientFields') AS data FROM settings"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $settings = json_decode($statement->fetchAll(\PDO::FETCH_ASSOC)[0]['data']);
      $patientListFields=[];
      foreach($settings as $S) {
        $patientListFields[str_replace(' ', '_', $S->name)] = $S->display;
        $patientListFields[str_replace(' ', '_', $S->name).'_required'] = $S->required;
      }

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

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

      // 
      return [
        'result' => 'OK',
        'patientListFields' => $patientListFields,
        'countries' => $countries,
        'patient' => $patient
      ];
    }

    /**
     * Get patient risks data.
     */
    public function patientRisksGet($User, $fk_visit) {
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT patient.* FROM patient, visit WHERE patient.ID = visit.fk_patient AND visit.ID = $fk_visit"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $patient = $statement->fetchAll(\PDO::FETCH_ASSOC)[0];

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

      // 
      return [
        'result' => 'OK',
        'patient' => $patient,
        'context' => $context
      ];
    }
    
    /**
     * Get patient history data.
     */
    public function patientHistoryGet($User, $fk_visit, $lang) {
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT patient.* FROM patient, visit WHERE patient.ID = visit.fk_patient AND visit.ID = $fk_visit"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $patient = $statement->fetchAll(\PDO::FETCH_ASSOC)[0];

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

      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT ID, JSON_UNQUOTE(JSON_EXTRACT(code, '$.$lang')) AS code FROM lst_coronary_type"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $lst_coronary_type = $statement->fetchAll(\PDO::FETCH_ASSOC);

      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT ID, JSON_UNQUOTE(JSON_EXTRACT(code, '$.$lang')) AS code FROM lst_stroke_type"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $lst_stroke_type = $statement->fetchAll(\PDO::FETCH_ASSOC);

      // 
      return [
        'result' => 'OK',
        'patient' => $patient,
        'context' => $context,
        'lst_coronary_type' => $lst_coronary_type,
        'lst_stroke_type' => $lst_stroke_type
      ];
    }    

    /**
     * Get patient family data.
     */
    public function patientFamilyGet($User, $fk_visit) {
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT patient.* FROM patient, visit WHERE patient.ID = visit.fk_patient AND visit.ID = $fk_visit"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $patient = $statement->fetchAll(\PDO::FETCH_ASSOC)[0];

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

    /**
     * Get patient examination data.
     */
    public function patientExaminationGet($User, $fk_visit) {
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT patient.* FROM patient, visit WHERE patient.ID = visit.fk_patient AND visit.ID = $fk_visit"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $patient = $statement->fetchAll(\PDO::FETCH_ASSOC)[0];

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

      // 
      return [
        'result' => 'OK',
        'patient' => $patient,
        'context' => $context
      ];
    }  

    /**
     * Get patient treatments data.
     */
    public function patientTreatmentsGet($User, $fk_visit) {
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT patient.* FROM patient, visit WHERE patient.ID = visit.fk_patient AND visit.ID = $fk_visit"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $patient = $statement->fetchAll(\PDO::FETCH_ASSOC)[0];

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

      // 
      return [
        'result' => 'OK',
        'patient' => $patient,
        'context' => $context
      ];
    }  

    /**
     * 
     */
    public function patientPacsQueryPost($User, $data) {
      $userID = $User->ID;
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT settings_pacs.data AS data FROM settings_pacs, user WHERE settings_pacs.fk_physician = $userID OR (settings_pacs.fk_center = user.fk_center AND user.ID = $userID)"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $conf = json_decode($statement->fetchAll(\PDO::FETCH_ASSOC)[0]['data']);

      $cmdLines = [];

      $cmdLine = 'findscu '.$conf->serverAddress.' '.$conf->queryPort.' -aet '.$conf->callingAET.' -aec '.$conf->calledAET.
        ' -P -k QueryRetrieveLevel="PATIENT" -k PatientID="*" -k PatientName="*'.$data['patient'].'*" -k PatientBirthDate="*" -k PatientSex="*"'.
        ' -v 2>&1';
      $cmdLines[] = $cmdLine;

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

      // clean
      for($i=0; $i<count($output); $i++) {
        if($output[$i]=="I:") {
          array_splice($output, $i, 1);
        }
      }

      // parse
      $patients = [];
      $patient = [];
      for($i=0; $i<count($output); $i++) {
        if(strpos($output[$i], "I: Find Response: ")===0) {
          if(count($patient)!=0) {
            $patients[] = $patient;
            $patient = [];
          }
        }
        else if(strpos($output[$i], "I: (0010,0010)")===0) {
          $patient['PatientName'] = trim(explode(']', explode('[', $output[$i])[1])[0]);
          //$patient['PatientName'] = trim(str_replace('^',' ',$patient['PatientName']));
        }
        else if(strpos($output[$i], "I: (0010,0020)")===0) {
          $patient['PatientID'] = trim(explode(']', explode('[', $output[$i])[1])[0]);
        }
        else if(strpos($output[$i], "I: (0010,0030)")===0) {
          $patient['PatientBirthDate'] = trim(explode(']', explode('[', $output[$i])[1])[0]);
        }
        else if(strpos($output[$i], "I: (0010,0040)")===0) {
          $patient['PatientSex'] = trim(explode(']', explode('[', $output[$i])[1])[0]);
        }
      }
      if(count($patient)!=0) {
        $patients[] = $patient;
      }
      array_splice($patients, 0, 1);

      // studies
      $studies = [];
      foreach($patients as $P) {
        $cmdLine = 'findscu '.$conf->serverAddress.' '.$conf->queryPort.' -aet '.$conf->callingAET.' -aec '.$conf->calledAET.
          ' -P -k QueryRetrieveLevel="STUDY" -k PatientID="'.$P['PatientID'].'" -k StudyInstanceUID="" -k StudyID="" -k StudyDate="" -k StudyTime=""'.
          ' -v 2>&1';

        $cmdLines[] = $cmdLine;

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

        $study = [
          'PatientName' => $P['PatientName'],
          'PatientBirthDate' => $P['PatientBirthDate'],
          'PatientSex' => $P['PatientSex']
        ];
        $hasFound = false;
        for($i=0; $i<count($output); $i++) {
          if(strpos($output[$i], "I: Find Response: ")===0) {
            if($hasFound) {
              $studies[] = $study;
              $study = [
                'PatientName' => $P['PatientName'],
                'PatientBirthDate' => $P['PatientBirthDate'],
                'PatientSex' => $P['PatientSex']
              ];
            }
            $hasFound = true;
          }
          else if(strpos($output[$i], "I: (0008,0020)")===0) {
            $study['StudyDate'] = trim(explode(']', explode('[', $output[$i])[1])[0]);
          }
          else if(strpos($output[$i], "I: (0008,0030)")===0) {
            $study['StudyTime'] = trim(explode(']', explode('[', $output[$i])[1])[0]);
          }
          else if(strpos($output[$i], "I: (0020,0010)")===0) {
            $study['StudyID'] = trim(explode(']', explode('[', $output[$i])[1])[0]);
          }
          else if(strpos($output[$i], "I: (0020,000d)")===0) {
            $study['StudyInstanceUID'] = trim(explode(']', explode('[', $output[$i])[1])[0]);
          }
          else if(strpos($output[$i], "I: (0010,0020)")===0) {
            $study['PatientID'] = trim(explode(']', explode('[', $output[$i])[1])[0]);
          }
        }
        $studies[] = $study;
        $studies2[] = $output;
      }

      /*return [
        'result' => 'ERROR',
        'studies2' => $studies2, 
        'studies' => $studies,
        'cmdLines' => $cmdLines
      ];*/

      //sudo movescu www.dicomserver.co.uk 11112 +P 11112 -P -k QueryRetrieveLevel="PATIENT" -k PatientID="874688" -v

      return [
        'studies' => $studies,
        'studies2' => $studies2, 
        'result' => 'OK',
        'cmdLines' => $cmdLines
      ];
    }
    
    /**
     * 
     */
    public function patientPacsRetrievePost($User, $data) {
      /*return [
        'result' => 'ERROR',
        'data' => $data
      ];*/
      $userID = $User->ID;
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT settings_pacs.data AS data FROM settings_pacs, user WHERE settings_pacs.fk_physician = $userID OR (settings_pacs.fk_center = user.fk_center AND user.ID = $userID)"
      );
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $conf = json_decode($statement->fetchAll(\PDO::FETCH_ASSOC)[0]['data']);

      // create or select patient
      $patientID = 0;
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT * FROM patient WHERE patientID = :patientID OR ctPatientID = :ctPatientID"
      );
      $statement->bindParam(':patientID', $data['all']['PatientID']);
      $statement->bindParam(':ctPatientID', $data['all']['PatientID']);
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $patients = $statement->fetchAll(\PDO::FETCH_ASSOC);
      // insert patient
      if(count($patients)==0) {
        $statement = $this->DataInterface->DatabaseConnection->prepare(
          "INSERT INTO patient(ID, patientID, ctPatientID, firstname, lastname, gender, birthDate, fk_user) VALUES(0, :patientID, :ctPatientID, :firstname, :lastname, :gender, :birthDate, :fk_user)"
        );
        $lastname = $data['all']['PatientName'];
        $firstname = "";
        $t = explode('^', $lastname);
        if(count($t==2)) {
          $lastname = $t[0];
          $firstname = $t[1];
        }
        $birth = substr($data['all']['PatientBirthDate'],0,4).'-'.substr($data['all']['PatientBirthDate'],4,2).'-'.substr($data['all']['PatientBirthDate'],6,2);
        $statement->bindParam(':patientID', $data['all']['PatientID']);
        $statement->bindParam(':ctPatientID', $data['all']['PatientID']);
        $statement->bindParam(':firstname', $firstname);
        $statement->bindParam(':lastname', $lastname);
        $statement->bindParam(':gender', $data['all']['PatientSex']);
        $statement->bindParam(':birthDate', $birth);
        $statement->bindParam(':fk_user', $userID);
        if(!$statement->execute()) {
          return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
        }
        $patientID = $this->DataInterface->DatabaseConnection->lastInsertId();
      }
      // select patient
      else {
        $patientID = $patients[0]['ID'];
      }

      // create or select visit
      $visitID = 0;
      $statement = $this->DataInterface->DatabaseConnection->prepare(
        "SELECT visit.* FROM visit, patient WHERE patient.ID = visit.fk_patient AND visit.studyInstanceUID = :studyInstanceUID"
      );
      $statement->bindParam(':studyInstanceUID', $data['all']['StudyInstanceUID']);
      if(!$statement->execute()) {
        return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
      }
      $visits = $statement->fetchAll(\PDO::FETCH_ASSOC);
      // insert visit
      if(count($visits)==0) {
        $statement = $this->DataInterface->DatabaseConnection->prepare(
          "INSERT INTO visit(ID, number, visitDate, fk_patient, studyInstanceUID) VALUES(0, :number, :visitDate, :fk_patient, :studyInstanceUID)"
        );
        $sdate = substr($data['all']['StudyDate'],0,4).'-'.substr($data['all']['StudyDate'],4,2).'-'.substr($data['all']['StudyDate'],6,2);
        $statement->bindParam(':number', $data['all']['StudyID']);
        $statement->bindParam(':visitDate', $sdate);
        $statement->bindParam(':fk_patient', $patientID);
        $statement->bindParam(':studyInstanceUID', $data['all']['StudyInstanceUID']);
        if(!$statement->execute()) {
          return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
        }
        $visitID = $this->DataInterface->DatabaseConnection->lastInsertId();

        // Insert context
        $statement = $this->DataInterface->DatabaseConnection->prepare(
          "INSERT INTO context(fk_visit) VALUES(:fk_visit)"
        );
        $statement->bindParam(':fk_visit', $visitID);
        // Error check
        if(!$statement->execute()) {
          $this->DataInterface->DatabaseConnection->rollback();
          return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
        }
      }
      // select visit
      else {
        $visitID = $visits[0]['ID'];
      }

      $od = '../../storage/media/'.$visitID.'/';
      \Tools\FS::mkpath($od);

      //sudo  www.dicomserver.co.uk 11112 +P 11112 -P -k QueryRetrieveLevel="PATIENT" -k PatientID="874688" -v
      $cmdLine = 'movescu '.$conf->serverAddress.' '.$conf->queryPort.' +P 11112 -aet '.$conf->callingAET.' -aec '.$conf->calledAET.' -aem '.$conf->callingAET.
        ' -P -k QueryRetrieveLevel="STUDY" -k StudyInstanceUID="'.$data['all']['StudyInstanceUID'].'"'.
        ' -od '.$od.
        ' -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
        ];
      }

      // get files
      $files = glob("$od*");
      $existingFiles = 0;
      $newFiles = 0;
      foreach($files as $F) {
        // skip previously existing files
        if(strpos($F, ".dicom")!==false || strpos($F, ".jpeg")!==false || strpos($F, ".mp4")!==false) {
          // do nothing
        }
        // new dicom file
        else {
          // existing file
          if(file_exists("$F.dicom")) {
            // do nothing
            $existingFiles++;
            unlink($F);
          }
          // real new file
          else {
            $newFiles++;
            rename($F, "$F.dicom");

            // multiframe?
            $cmdLine = 'dcmdump +P 0028,0008 "'.$F.'.dicom"';
            $output=null;
            $retval=null;
            exec($cmdLine, $output, $retval);
            // OK
            if($retval === 0 && count($output)==1) {
              $tab = explode(' ',trim($output[0]));
              $frameCount = intval(str_replace(['[', ']'], '', $tab[2]));
              // error
              if($frameCount===0) {
                return [
                  'result' => 'ERROR',
                  'output' => $output, 
                ];
              }

              // make JPEG
              $frames = [];
              for($i=1; $i<=$frameCount; $i++) {
                $src = "$F.dicom";
                $pad = str_pad($i, 3, "0", STR_PAD_LEFT);
                $dst = str_replace('.dicom', '-'.$pad.'.jpeg', $src);
                $cmdLine = 'dcmj2pnm "'.$src.'" "'.$dst.'" +oj +Jq 100 +F '.$i.' -v 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
                  ];
                }

                $frames[] = $dst;
              }

              // make MP4
              $src = "$F.dicom";
              $dst = str_replace('.dicom', '-%03d.jpeg', $src);
              $mp4 = str_replace('.dicom', '.mp4', $src);
              
              $cmdLine = 'ffmpeg -f image2 -pattern_type sequence -r 11 -i '.$dst.' -y -c:v libx264 -pix_fmt yuv420p '.$mp4;
              $output=null;
              $retval=null;
              exec($cmdLine, $output, $retval);
              // error
              if($retval !== 0) {
                return [
                  'result' => 'ERROR',
                  'cmdLine' => $cmdLine,
                  'output' => $output, 
                  'retval' => $retval
                ];
              }
              
              // cleanup
              for($i=0; $i<count($frames); $i++) {
                unlink($frames[$i]);
              }

              // get metrics
              $cmdLine = 'ffprobe -v error -show_entries stream=width,height,r_frame_rate -of csv=p=0 '.$mp4;
              $output=null;
              $retval=null;
              exec($cmdLine, $output, $retval);
              // error
              if($retval !== 0 || count($output)!=1) {
                return [
                  'result' => 'ERROR',
                  'cmdLine' => $cmdLine,
                  'output' => $output, 
                  'retval' => $retval
                ];
              }
    
              $tab = explode(',',trim($output[0]));
              if(count($tab)!=3) {
                return [
                  'result' => 'ERROR',
                  'cmdLine' => $cmdLine,
                  'output' => $output, 
                  'retval' => $retval
                ];
              }
    
              $metrics['width'] = intval($tab[0]);
              $metrics['height'] = intval($tab[1]);
    
              $fps = $tab[2];
              $tab = explode('/', $fps);
              if(count($tab) == 2) {
                $fps = intval($tab[0]);
              }
              else {
                $fps = intval($fps);
              }
              $metrics['fps'] = $fps;              
            }
            // singleframe
            else {
              $cmdLine = "dcmj2pnm $F.dicom $F.jpeg +oj +Jq 100 +F 1 -v 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
                ];
              }              

              // image metrics
              $metrics = ['width' => 0, 'height' => 0, 'pxwidth' => 0, 'pxheight' => 0];
              list($metrics['width'], $metrics['height'], $type, $attr) = getimagesize("$F.jpeg");
            }

            // dicom metrics
            $cmdLine = 'dcmdump -s +P 0018,602c +P 0018,602e +P 0028,0008 "'.$F.'.dicom"';
            $output=null;
            $retval=null;
            exec($cmdLine, $output, $retval);
            // error
            if($retval !== 0) {
              return [
                'result' => 'ERROR',
                'cmdLine' => $cmdLine,
                'output' => $output, 
                'retval' => $retval
              ];
            }
            if(count($output)>=2) {
              $tab = explode(' ',trim($output[0]));
              $metrics['pxwidth'] = floatval($tab[2])*10.0;
              $tab = explode(' ',trim($output[1]));
              $metrics['pxheight'] = floatval($tab[2])*10.0;
            }
            if(count($output)==3) {
              $tab = explode(' ',trim($output[2]));
              $metrics['frameCount'] = intval(str_replace(['[', ']'], '', $tab[2]));
            }
    
            // insert new media
            $statement = $this->DataInterface->DatabaseConnection->prepare(
              "INSERT INTO media(ID, side, filename, metrics, fk_visit) VALUES(0, 'unknown', :filename, :metrics, :fk_visit)"
            );
            $statement->bindParam(':filename', basename("$F.dicom"));
            $statement->bindParam(':metrics', json_encode($metrics));
            $statement->bindParam(':fk_visit', $visitID);
            if(!$statement->execute()) {
              return ['result' => 'ERROR', 'reason' => 'internal_error', 'message' => 'Database error', 'data' => $statement->errorInfo()];
            }
    
          }
        }

      }

      return [
        'result' => 'OK',
        'cmdLine' => $cmdLine,
        'files' => $files,
        'output' => $output,
        'patientID' => $patientID,
        'visitID' => $visitID,
        'existingFiles' => $existingFiles,
        'newFiles' => $newFiles
      ];
    }  

  }
}