src/Services/PractitionerRoleService.php

   1 <?php
   2
   3 /**
   4  * PractitionerRoleService
   5  *
   6  * @package   OpenEMR
   7  * @link      http://www.open-emr.org
   8  * @author    Yash Bothra <yashrajbothra786gmail.com>
   9  * @copyright Copyright (c) 2018 Matthew Vita <matthewvita48@gmail.com>
  10  * @license   https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
  11  */
  12
  13 namespace OpenEMR\Services;
  14
  15 use OpenEMR\Common\Database\QueryUtils;
  16 use OpenEMR\Common\Uuid\UuidRegistry;
  17 use OpenEMR\Services\Search\FhirSearchWhereClauseBuilder;
  18 use OpenEMR\Services\Search\TokenSearchField;
  19 use OpenEMR\Validators\ProcessingResult;
  20 use OpenEMR\Validators\BaseValidator;
  21
  22 class PractitionerRoleService extends BaseService
  23 {
  24     private const PRACTITIONER_ROLE_TABLE = "facility_user_ids";
  25     private const PRACTITIONER_TABLE = "users";
  26     private const FACILITY_TABLE = "facility";
  27
  28     /**
  29      * Default constructor.
  30      */
  31     public function __construct()
  32     {
  33         parent::__construct('facility_user_ids');
  34         UuidRegistry::createMissingUuidsForTables([self::PRACTITIONER_ROLE_TABLE, self::PRACTITIONER_TABLE, self::FACILITY_TABLE]);
  35     }
  36
  37     public function getUuidFields(): array
  38     {
  39         // return the individual uuid fields we want converted into strings
  40         return ['facility_uuid', 'facility_role_uuid', 'provider_uuid', 'uuid'];
  41     }
  42
  43     public function search($search, $isAndCondition = true)
  44     {
  45         // note we are optimizing our key indexes by specifying our list_ids for list_options
  46         // note because facility_user_ids is denormalized and stores its form data in a Key Value list in order to grab
  47         // our data in the easiest format from the database and still be able to search on it, we do several joins
  48         // against the same table so we can grab our provider information, provider role info, and provider specialty
  49         // it seems like a pretty big query but its optimized pretty heavily on the indexes.  We may need a few more
  50         // indexes on facility_user_ids but we'll have to test this
  51         $sql = "SELECT
  52                 providers.facility_role_id AS id,
  53                 providers.facility_role_uuid AS uuid,
  54                 providers.user_name,
  55                 providers.provider_id,
  56                 providers.provider_uuid,
  57
  58                 facilities.facility_uuid,
  59                 facilities.facility_name,
  60                 role_codes.role_code,
  61                 role_codes.role_title,
  62                 specialty_codes.specialty_code,
  63                 specialty_codes.specialty_title
  64                 FROM (
  65                     select
  66                         facility_user_ids.uuid AS facility_role_uuid,
  67                         facility_user_ids.id AS facility_role_id,
  68                         -- field_value AS provider_id,
  69                         facility_user_ids.facility_id,
  70                         uid AS user_id,
  71                         -- we are treating the user_id as the provider id
  72                         -- TODO: @adunsulag figure out whether we should actually be using the user entered provider_id
  73                         uid AS provider_id,
  74                         users.uuid AS provider_uuid,
  75                         CONCAT(COALESCE(users.fname,''),
  76                            IF(users.mname IS NULL OR users.mname = '','',' '),COALESCE(users.mname,''),
  77                            IF(users.lname IS NULL OR users.lname = '','',' '),COALESCE(users.lname,'')
  78                         ) as user_name
  79                     FROM
  80                         facility_user_ids
  81                     JOIN users ON
  82                         facility_user_ids.uid = users.id
  83                     WHERE
  84                         field_id='provider_id'
  85
  86                 ) providers
  87                 JOIN (
  88                     select
  89                         field_value AS role_code,
  90                         field_id,
  91                         role.title AS role_title,
  92                         facility_id,
  93                         uid AS user_id
  94                     FROM
  95                         facility_user_ids
  96                     JOIN
  97                         list_options as role ON role.option_id = field_value
  98                     WHERE
  99                         field_value != '' AND field_value IS NOT NULL
 100                         AND role.list_id='us-core-provider-role'
 101                 ) role_codes ON
 102                     providers.user_id = role_codes.user_id AND providers.facility_id = role_codes.facility_id AND role_codes.field_id='role_code'
 103                 JOIN (
 104                     select
 105                         uuid AS facility_uuid
 106                         ,id AS facility_id
 107                         ,name AS facility_name
 108                     FROM
 109                         facility
 110                 ) facilities
 111                     ON providers.facility_id = facilities.facility_id
 112                 LEFT JOIN (
 113                     select
 114                         field_value AS specialty_code,
 115                         specialty.title AS specialty_title,
 116                         field_id,
 117                         facility_id,
 118                         uid AS user_id
 119                      FROM
 120                         facility_user_ids facilities_specialty
 121                     JOIN
 122                         list_options as specialty ON specialty.option_id = field_value
 123                     WHERE
 124                         field_id='specialty_code'
 125                         AND specialty.list_id='us-core-provider-specialty'
 126                 ) specialty_codes ON
 127                     providers.user_id = specialty_codes.user_id AND providers.facility_id = specialty_codes.facility_id AND specialty_codes.field_id='specialty_code'";
 128         $whereClause = FhirSearchWhereClauseBuilder::build($search, $isAndCondition);
 129
 130         $sql .= $whereClause->getFragment();
 131         $sqlBindArray = $whereClause->getBoundValues();
 132         $statementResults =  QueryUtils::sqlStatementThrowException($sql, $sqlBindArray);
 133
 134         $processingResult = new ProcessingResult();
 135         while ($row = sqlFetchArray($statementResults)) {
 136             $resultRecord = $this->createResultRecordFromDatabaseResult($row);
 137             $processingResult->addData($resultRecord);
 138         }
 139         return $processingResult;
 140     }
 141
 142     /**
 143      * Grabs all of the roles and groups them by practitioner.  The data result set will be a hashmap with the keys
 144      * being the practitioner id and the value being an array of practitioner role records.
 145      * @param $practitionerIds
 146      * @return ProcessingResult
 147      */
 148     public function getAllByPractitioners($practitionerIds)
 149     {
 150
 151         $results = $this->search(['provider_id' => new TokenSearchField('provider_id', $practitionerIds)]);
 152
 153         $data = $results->getData() ?? [];
 154         $providerIdMap = [];
 155         foreach ($data as $record) {
 156             $providerId = $record['provider_id'];
 157             if (empty($providerIdMap[$providerId])) {
 158                 $providerIdMap[$providerId] = [];
 159             }
 160             $providerIdMap[$providerId][] = $record;
 161         }
 162         $results->setData($providerIdMap);
 163         return $results;
 164     }
 165
 166     /**
 167      * Returns a list of practitioner-role matching optional search criteria.
 168      * Search criteria is conveyed by array where key = field/column name, value = field value.
 169      * If no search criteria is provided, all records are returned.
 170      *
 171      * @param  $search search array parameters
 172      * @param  $isAndCondition specifies if AND condition is used for multiple criteria. Defaults to true.
 173      * @return ProcessingResult which contains validation messages, internal error messages, and the data
 174      * payload.
 175      */
 176     public function getAll($search = array(), $isAndCondition = true)
 177     {
 178         $sqlBindArray = array();
 179
 180         $sql = "SELECT *,
 181                 role.title as role,
 182                 spec.title as specialty
 183                 FROM (
 184                     SELECT
 185                     prac_role.id as id,
 186                     prac_role.uuid as uuid,
 187                     prac_role.field_id as field,
 188                     (if( prac_role.field_id = 'role_code', prac_role.field_value, null )) as `role_code`,
 189                     (if( specialty.field_id = 'specialty_code', specialty.field_value, null )) as `specialty_code`,
 190                     us.uuid as user_uuid,
 191                     CONCAT(us.fname,
 192                            IF(us.mname IS NULL OR us.mname = '','',' '),us.mname,
 193                            IF(us.lname IS NULL OR us.lname = '','',' '),us.lname
 194                            ) as user_name,
 195                     fac.uuid as facility_uuid,
 196                     fac.name as facility_name
 197                     FROM facility_user_ids as prac_role
 198                     LEFT JOIN users as us ON us.id = prac_role.uid
 199                     LEFT JOIN facility_user_ids as specialty ON specialty.uid = prac_role.uid AND specialty.field_id = 'specialty_code'
 200                     LEFT JOIN facility as fac ON fac.id = prac_role.facility_id) as p_role
 201                 LEFT JOIN list_options as role ON role.option_id = p_role.role_code
 202                 LEFT JOIN list_options as spec ON spec.option_id = p_role.specialty_code
 203                 WHERE p_role.field = 'role_code' AND p_role.role_code != '' AND p_role.role_code IS NOT NULL";
 204
 205         if (!empty($search)) {
 206             $sql .= " AND ";
 207             $whereClauses = array();
 208             $wildcardFields = array('user_name');
 209             foreach ($search as $fieldName => $fieldValue) {
 210                 // support wildcard match on specific fields
 211                 if (in_array($fieldName, $wildcardFields)) {
 212                     array_push($whereClauses, $fieldName . ' LIKE ?');
 213                     array_push($sqlBindArray, '%' . $fieldValue . '%');
 214                 } else {
 215                     // equality match
 216                     array_push($whereClauses, $fieldName . ' = ?');
 217                     array_push($sqlBindArray, $fieldValue);
 218                 }
 219             }
 220             $sqlCondition = ($isAndCondition == true) ? 'AND' : 'OR';
 221             $sql .= implode(' ' . $sqlCondition . ' ', $whereClauses);
 222         }
 223         $sql .= "
 224          GROUP BY p_role.uuid";
 225         $statementResults = sqlStatement($sql, $sqlBindArray);
 226
 227         $processingResult = new ProcessingResult();
 228         while ($row = sqlFetchArray($statementResults)) {
 229             $row['uuid'] = UuidRegistry::uuidToString($row['uuid']);
 230             $row['user_uuid'] = UuidRegistry::uuidToString($row['user_uuid']);
 231             $row['facility_uuid'] = UuidRegistry::uuidToString($row['facility_uuid']);
 232             $processingResult->addData($row);
 233         }
 234
 235         return $processingResult;
 236     }
 237
 238     /**
 239      * Returns a single practitioner-role record by id.
 240      * @param $uuid - The practitioner-role uuid identifier in string format.
 241      * @return ProcessingResult which contains validation messages, internal error messages, and the data
 242      * payload.
 243      */
 244     public function getOne($uuid)
 245     {
 246         $processingResult = new ProcessingResult();
 247
 248         $isValid = BaseValidator::validateId("uuid", "facility_user_ids", $uuid, true);
 249
 250         if ($isValid !== true) {
 251             $validationMessages = [
 252                 'uuid' => ["invalid or nonexisting value" => " value " . $uuid]
 253             ];
 254             $processingResult->setValidationMessages($validationMessages);
 255             return $processingResult;
 256         }
 257
 258         $sql = "SELECT prac_role.id,
 259                 prac_role.uuid,
 260                 prac_role.field_value as role_code,
 261                 specialty.field_value as specialty_code,
 262                 us.uuid as user_uuid,
 263                 us.fname as user_fname,
 264                 us.mname as user_mname,
 265                 us.lname as user_lname,
 266                 fac.uuid as facility_uuid,
 267                 fac.name as facility_name,
 268                 role.title as role,
 269                 spec.title as specialty
 270                 FROM facility_user_ids as prac_role
 271                 LEFT JOIN users as us ON us.id = prac_role.uid
 272                 LEFT JOIN facility_user_ids as specialty ON
 273                 specialty.uid = prac_role.uid AND specialty.field_id = 'specialty_code'
 274                 LEFT JOIN facility as fac ON fac.id = prac_role.facility_id
 275                 LEFT JOIN list_options as role ON role.option_id = prac_role.field_value
 276                 LEFT JOIN list_options as spec ON spec.option_id = specialty.field_value
 277                 WHERE prac_role.uuid = ? AND prac_role.field_id = 'role_code'";
 278
 279         $uuidBinary = UuidRegistry::uuidToBytes($uuid);
 280         $sqlResult = sqlQuery($sql, [$uuidBinary]);
 281         $sqlResult['uuid'] = UuidRegistry::uuidToString($sqlResult['uuid']);
 282         $sqlResult['user_uuid'] = UuidRegistry::uuidToString($sqlResult['user_uuid']);
 283         $sqlResult['facility_uuid'] = UuidRegistry::uuidToString($sqlResult['facility_uuid']);
 284         $processingResult->addData($sqlResult);
 285         return $processingResult;
 286     }
 287 }