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                 providers.provider_last_updated,
  58
  59                 facilities.facility_uuid,
  60                 facilities.facility_name,
  61                 role_codes.role_code,
  62                 role_codes.role_title,
  63                 role_codes.role_last_updated,
  64
  65                 specialty_codes.specialty_code,
  66                 specialty_codes.specialty_title,
  67                 specialty_codes.specialty_last_updated,
  68
  69                 physician_types.physician_type_codes,
  70                 physician_types.physician_type,
  71                 physician_types.physician_type_title
  72                 FROM (
  73                     select
  74                         facility_user_ids.uuid AS facility_role_uuid,
  75                         facility_user_ids.id AS facility_role_id,
  76                         facility_user_ids.facility_id,
  77                         uid AS user_id,
  78                         -- we are treating the user_id as the provider id
  79                         -- TODO: @adunsulag figure out whether we should actually be using the user entered provider_id
  80                         uid AS provider_id,
  81                         users.uuid AS provider_uuid,
  82                         users.last_updated AS provider_last_updated,
  83                         users.physician_type,
  84                         CONCAT(COALESCE(users.fname,''),
  85                            IF(users.mname IS NULL OR users.mname = '','',' '),COALESCE(users.mname,''),
  86                            IF(users.lname IS NULL OR users.lname = '','',' '),COALESCE(users.lname,'')
  87                         ) as user_name
  88                     FROM
  89                         facility_user_ids
  90                     JOIN users ON
  91                         facility_user_ids.uid = users.id
  92                     WHERE
  93                         field_id='provider_id'
  94
  95                 ) providers
  96                 JOIN (
  97                     select
  98                         field_value AS role_code,
  99                         field_id,
 100                         role.title AS role_title,
 101                         facility_id,
 102                         uid AS user_id,
 103                         facility_user_ids.last_updated AS role_last_updated,
 104                         facility_user_ids.date_created AS role_date_created
 105                     FROM
 106                         facility_user_ids
 107                     JOIN
 108                         list_options as role ON role.option_id = field_value
 109                     WHERE
 110                         field_value != '' AND field_value IS NOT NULL
 111                         AND role.list_id='us-core-provider-role'
 112                 ) role_codes ON
 113                     providers.user_id = role_codes.user_id AND providers.facility_id = role_codes.facility_id AND role_codes.field_id='role_code'
 114                 JOIN (
 115                     select
 116                         uuid AS facility_uuid
 117                         ,id AS facility_id
 118                         ,name AS facility_name
 119                     FROM
 120                         facility
 121                 ) facilities
 122                     ON providers.facility_id = facilities.facility_id
 123                 LEFT JOIN (
 124                     select
 125                         field_value AS specialty_code,
 126                         specialty.title AS specialty_title,
 127                         field_id,
 128                         facility_id,
 129                         uid AS user_id,
 130                         facilities_specialty.last_updated AS specialty_last_updated,
 131                         facilities_specialty.date_created AS specialty_date_created
 132                      FROM
 133                         facility_user_ids facilities_specialty
 134                     JOIN
 135                         list_options as specialty ON specialty.option_id = field_value
 136                     WHERE
 137                         field_id='specialty_code'
 138                         AND specialty.list_id='us-core-provider-specialty'
 139                 ) specialty_codes ON
 140                     providers.user_id = specialty_codes.user_id AND providers.facility_id = specialty_codes.facility_id AND specialty_codes.field_id='specialty_code'
 141                 LEFT JOIN (
 142                     select
 143                            codes AS physician_type_codes
 144                            ,option_id AS physician_type
 145                            ,title AS physician_type_title
 146                     FROM list_options types
 147                     WHERE types.list_id = 'physician_type'
 148                 ) physician_types ON physician_types.physician_type = providers.physician_type ";
 149         $whereClause = FhirSearchWhereClauseBuilder::build($search, $isAndCondition);
 150
 151         $sql .= $whereClause->getFragment();
 152         $sqlBindArray = $whereClause->getBoundValues();
 153         $statementResults =  QueryUtils::sqlStatementThrowException($sql, $sqlBindArray);
 154
 155         $processingResult = new ProcessingResult();
 156         while ($row = sqlFetchArray($statementResults)) {
 157             $resultRecord = $this->createResultRecordFromDatabaseResult($row);
 158             $processingResult->addData($resultRecord);
 159         }
 160         return $processingResult;
 161     }
 162
 163     /**
 164      * Grabs all of the roles and groups them by practitioner.  The data result set will be a hashmap with the keys
 165      * being the practitioner id and the value being an array of practitioner role records.
 166      * @param $practitionerIds
 167      * @return ProcessingResult
 168      */
 169     public function getAllByPractitioners($practitionerIds)
 170     {
 171
 172         $results = $this->search(['provider_id' => new TokenSearchField('provider_id', $practitionerIds)]);
 173
 174         $data = $results->getData() ?? [];
 175         $providerIdMap = [];
 176         foreach ($data as $record) {
 177             $providerId = $record['provider_id'];
 178             if (empty($providerIdMap[$providerId])) {
 179                 $providerIdMap[$providerId] = [];
 180             }
 181             $providerIdMap[$providerId][] = $record;
 182         }
 183         $results->setData($providerIdMap);
 184         return $results;
 185     }
 186
 187     /**
 188      * Returns a list of practitioner-role matching optional search criteria.
 189      * Search criteria is conveyed by array where key = field/column name, value = field value.
 190      * If no search criteria is provided, all records are returned.
 191      *
 192      * @param  $search search array parameters
 193      * @param  $isAndCondition specifies if AND condition is used for multiple criteria. Defaults to true.
 194      * @return ProcessingResult which contains validation messages, internal error messages, and the data
 195      * payload.
 196      */
 197     public function getAll($search = array(), $isAndCondition = true)
 198     {
 199         $sqlBindArray = array();
 200
 201         $sql = "SELECT *,
 202                 role.title as role,
 203                 spec.title as specialty
 204                 FROM (
 205                     SELECT
 206                     prac_role.id as id,
 207                     prac_role.uuid as uuid,
 208                     prac_role.field_id as field,
 209                     (if( prac_role.field_id = 'role_code', prac_role.field_value, null )) as `role_code`,
 210                     (if( specialty.field_id = 'specialty_code', specialty.field_value, null )) as `specialty_code`,
 211                     us.uuid as user_uuid,
 212                     CONCAT(us.fname,
 213                            IF(us.mname IS NULL OR us.mname = '','',' '),us.mname,
 214                            IF(us.lname IS NULL OR us.lname = '','',' '),us.lname
 215                            ) as user_name,
 216                     fac.uuid as facility_uuid,
 217                     fac.name as facility_name
 218                     FROM facility_user_ids as prac_role
 219                     LEFT JOIN users as us ON us.id = prac_role.uid
 220                     LEFT JOIN facility_user_ids as specialty ON specialty.uid = prac_role.uid AND specialty.field_id = 'specialty_code'
 221                     LEFT JOIN facility as fac ON fac.id = prac_role.facility_id) as p_role
 222                 LEFT JOIN list_options as role ON role.option_id = p_role.role_code
 223                 LEFT JOIN list_options as spec ON spec.option_id = p_role.specialty_code
 224                 WHERE p_role.field = 'role_code' AND p_role.role_code != '' AND p_role.role_code IS NOT NULL";
 225
 226         if (!empty($search)) {
 227             $sql .= " AND ";
 228             $whereClauses = array();
 229             $wildcardFields = array('user_name');
 230             foreach ($search as $fieldName => $fieldValue) {
 231                 // support wildcard match on specific fields
 232                 if (in_array($fieldName, $wildcardFields)) {
 233                     array_push($whereClauses, $fieldName . ' LIKE ?');
 234                     array_push($sqlBindArray, '%' . $fieldValue . '%');
 235                 } else {
 236                     // equality match
 237                     array_push($whereClauses, $fieldName . ' = ?');
 238                     array_push($sqlBindArray, $fieldValue);
 239                 }
 240             }
 241             $sqlCondition = ($isAndCondition == true) ? 'AND' : 'OR';
 242             $sql .= implode(' ' . $sqlCondition . ' ', $whereClauses);
 243         }
 244         $sql .= "
 245          GROUP BY p_role.uuid";
 246         $statementResults = sqlStatement($sql, $sqlBindArray);
 247
 248         $processingResult = new ProcessingResult();
 249         while ($row = sqlFetchArray($statementResults)) {
 250             $row['uuid'] = UuidRegistry::uuidToString($row['uuid']);
 251             $row['user_uuid'] = UuidRegistry::uuidToString($row['user_uuid']);
 252             $row['facility_uuid'] = UuidRegistry::uuidToString($row['facility_uuid']);
 253             $processingResult->addData($row);
 254         }
 255
 256         return $processingResult;
 257     }
 258
 259     /**
 260      * Returns a single practitioner-role record by id.
 261      * @param $uuid - The practitioner-role uuid identifier in string format.
 262      * @return ProcessingResult which contains validation messages, internal error messages, and the data
 263      * payload.
 264      */
 265     public function getOne($uuid)
 266     {
 267         $processingResult = new ProcessingResult();
 268
 269         $isValid = BaseValidator::validateId("uuid", "facility_user_ids", $uuid, true);
 270
 271         if ($isValid !== true) {
 272             $validationMessages = [
 273                 'uuid' => ["invalid or nonexisting value" => " value " . $uuid]
 274             ];
 275             $processingResult->setValidationMessages($validationMessages);
 276             return $processingResult;
 277         }
 278
 279         $sql = "SELECT prac_role.id,
 280                 prac_role.uuid,
 281                 prac_role.field_value as role_code,
 282                 specialty.field_value as specialty_code,
 283                 us.uuid as user_uuid,
 284                 us.fname as user_fname,
 285                 us.mname as user_mname,
 286                 us.lname as user_lname,
 287                 fac.uuid as facility_uuid,
 288                 fac.name as facility_name,
 289                 role.title as role,
 290                 spec.title as specialty
 291                 FROM facility_user_ids as prac_role
 292                 LEFT JOIN users as us ON us.id = prac_role.uid
 293                 LEFT JOIN facility_user_ids as specialty ON
 294                 specialty.uid = prac_role.uid AND specialty.field_id = 'specialty_code'
 295                 LEFT JOIN facility as fac ON fac.id = prac_role.facility_id
 296                 LEFT JOIN list_options as role ON role.option_id = prac_role.field_value
 297                 LEFT JOIN list_options as spec ON spec.option_id = specialty.field_value
 298                 WHERE prac_role.uuid = ? AND prac_role.field_id = 'role_code'";
 299
 300         $uuidBinary = UuidRegistry::uuidToBytes($uuid);
 301         $sqlResult = sqlQuery($sql, [$uuidBinary]);
 302         $sqlResult['uuid'] = UuidRegistry::uuidToString($sqlResult['uuid']);
 303         $sqlResult['user_uuid'] = UuidRegistry::uuidToString($sqlResult['user_uuid']);
 304         $sqlResult['facility_uuid'] = UuidRegistry::uuidToString($sqlResult['facility_uuid']);
 305         $processingResult->addData($sqlResult);
 306         return $processingResult;
 307     }
 308 }