2 -- Comment Meta Language Constructs:
5 -- argument: table_name
6 -- behavior: if the table_name does not exist, the block will be executed
9 -- argument: table_name
10 -- behavior: if the table_name does exist, the block will be executed
13 -- arguments: table_name colname
14 -- behavior: if the table and column exist, the block will be executed
17 -- arguments: table_name colname
18 -- behavior: if the table exists but the column does not, the block will be executed
21 -- arguments: table_name colname value
22 -- behavior: If the table table_name does not have a column colname with a data type equal to value, then the block will be executed
24 -- #IfNotColumnTypeDefault
25 -- arguments: table_name colname value value2
26 -- behavior: If the table table_name does not have a column colname with a data type equal to value and a default equal to value2, then the block will be executed
29 -- arguments: table_name colname value
30 -- behavior: If the table table_name does not have a row where colname = value, the block will be executed.
33 -- arguments: table_name colname value colname2 value2
34 -- behavior: If the table table_name does not have a row where colname = value AND colname2 = value2, the block will be executed.
37 -- arguments: table_name colname value colname2 value2 colname3 value3
38 -- behavior: If the table table_name does not have a row where colname = value AND colname2 = value2 AND colname3 = value3, the block will be executed.
41 -- arguments: table_name colname value colname2 value2 colname3 value3 colname4 value4
42 -- behavior: If the table table_name does not have a row where colname = value AND colname2 = value2 AND colname3 = value3 AND colname4 = value4, the block will be executed.
45 -- desc: This is a very specialized function to allow adding items to the list_options table to avoid both redundant option_id and title in each element.
46 -- arguments: table_name colname value colname2 value2 colname3 value3
47 -- behavior: The block will be executed if both statements below are true:
48 -- 1) The table table_name does not have a row where colname = value AND colname2 = value2.
49 -- 2) The table table_name does not have a row where colname = value AND colname3 = value3.
52 -- arguments: table_name colname value
53 -- behavior: If the table table_name does have a row where colname = value, the block will be executed.
56 -- arguments: table_name colname value colname2 value2
57 -- behavior: If the table table_name does have a row where colname = value AND colname2 = value2, the block will be executed.
60 -- arguments: table_name colname value colname2 value2 colname3 value3
61 -- behavior: If the table table_name does have a row where colname = value AND colname2 = value2 AND colname3 = value3, the block will be executed.
64 -- desc: This function is most often used for dropping of indexes/keys.
65 -- arguments: table_name colname
66 -- behavior: If the table and index exist the relevant statements are executed, otherwise not.
69 -- desc: This function will allow adding of indexes/keys.
70 -- arguments: table_name colname
71 -- behavior: If the index does not exist, it will be created
74 -- argument: table_name
75 -- behavior: this will add and populate a uuid column into table
77 -- #IfUuidNeedUpdateId
78 -- argument: table_name primary_id
79 -- behavior: this will add and populate a uuid column into table
81 -- #IfUuidNeedUpdateVertical
82 -- argument: table_name table_columns
83 -- behavior: this will add and populate a uuid column into vertical table for combinations of table_columns given
86 -- all blocks are terminated with a #EndIf statement.
89 -- Custom function for creating Reaction List
91 -- #IfNotListOccupation
92 -- Custom function for creating Occupation List
94 -- #IfTextNullFixNeeded
95 -- desc: convert all text fields without default null to have default null.
99 -- desc: Execute SQL if the table has been created with given engine specified.
100 -- arguments: table_name engine
101 -- behavior: Use when engine conversion requires more than one ALTER TABLE
103 -- #IfInnoDBMigrationNeeded
104 -- desc: find all MyISAM tables and convert them to InnoDB.
106 -- behavior: can take a long time.
108 -- #IfDocumentNamingNeeded
109 -- desc: populate name field with document names.
112 #IfUuidNeedUpdate patient_data
115 #IfUuidNeedUpdate form_encounter
118 #IfUuidNeedUpdate users
121 #IfUuidNeedUpdateVertical facility_user_ids uid:facility_id
124 #IfUuidNeedUpdate facility
127 #IfUuidNeedUpdate immunizations
130 #IfUuidNeedUpdate lists
133 #IfUuidNeedUpdateId procedure_order procedure_order_id
136 #IfUuidNeedUpdateId drugs drug_id
139 #IfUuidNeedUpdate prescriptions
142 #IfUuidNeedUpdateId procedure_result procedure_result_id
145 #IfUuidNeedUpdate ccda
148 #IfMissingColumn insurance_companies uuid
149 ALTER TABLE `insurance_companies` ADD `uuid` binary(16) DEFAULT NULL;
152 #IfNotIndex insurance_companies uuid
153 CREATE UNIQUE INDEX `uuid` ON `insurance_companies` (`uuid`);
156 #IfUuidNeedUpdate insurance_companies
159 #IfMissingColumn insurance_data uuid
160 ALTER TABLE `insurance_data` ADD `uuid` binary(16) DEFAULT NULL;
163 #IfNotIndex insurance_data uuid
164 CREATE UNIQUE INDEX `uuid` ON `insurance_data` (`uuid`);
167 #IfUuidNeedUpdate insurance_data
170 #IfMissingColumn facility weno_id
171 ALTER TABLE `facility` ADD `weno_id` VARCHAR(10) DEFAULT NULL;
174 #IfMissingColumn x12_partners x12_gs03
175 ALTER TABLE `x12_partners` ADD COLUMN `x12_gs03` varchar(15) DEFAULT NULL;
178 #IfMissingColumn x12_partners x12_submitter_name
179 ALTER TABLE `x12_partners` ADD COLUMN `x12_submitter_name` varchar(255) DEFAULT NULL;
182 #IfMissingColumn x12_partners x12_sftp_login
183 ALTER TABLE `x12_partners` ADD COLUMN `x12_sftp_login` varchar(255) DEFAULT NULL;
186 #IfMissingColumn x12_partners x12_sftp_pass
187 ALTER TABLE `x12_partners` ADD COLUMN `x12_sftp_pass` varchar(255) DEFAULT NULL;
190 #IfMissingColumn x12_partners x12_sftp_host
191 ALTER TABLE `x12_partners` ADD COLUMN `x12_sftp_host` varchar(255) DEFAULT NULL;
194 #IfMissingColumn x12_partners x12_sftp_port
195 ALTER TABLE `x12_partners` ADD COLUMN `x12_sftp_port` varchar(255) DEFAULT NULL;
198 #IfMissingColumn x12_partners x12_sftp_local_dir
199 ALTER TABLE `x12_partners` ADD COLUMN `x12_sftp_local_dir` varchar(255) DEFAULT NULL;
202 #IfMissingColumn x12_partners x12_sftp_remote_dir
203 ALTER TABLE `x12_partners` ADD COLUMN `x12_sftp_remote_dir` varchar(255) DEFAULT NULL;
206 #IfNotRow background_services name X12_SFTP
207 INSERT INTO `background_services` (`name`, `title`, `active`, `running`, `next_run`, `execute_interval`, `function`, `require_once`, `sort_order`) VALUES
208 ('X12_SFTP', 'SFTP Claims to X12 Partner Service', 0, 0, '2021-01-18 11:25:10', 1, 'start_X12_SFTP', '/library/billing_sftp_service.php', 100);
211 #IfNotTable x12_remote_tracker
212 CREATE TABLE `x12_remote_tracker` (
213 `id` bigint(20) NOT NULL AUTO_INCREMENT,
214 `x12_partner_id` int(11) NOT NULL,
215 `x12_filename` varchar(255) NOT NULL,
216 `status` varchar(255) NOT NULL,
219 `created_at` datetime DEFAULT NULL,
220 `updated_at` datetime DEFAULT NULL,
224 #IfNotRow2D list_options list_id lists option_id Procedure_Billing
225 INSERT INTO list_options (list_id,option_id,title, seq, is_default, option_value) VALUES ('lists','Procedure_Billing','Procedure Billing',0, 1, 0);
226 INSERT INTO list_options (list_id,option_id,title,seq,is_default,activity) VALUES ('Procedure_Billing','T','Third-Party',10,1,1);
227 INSERT INTO list_options (list_id,option_id,title,seq,is_default,activity) VALUES ('Procedure_Billing','P','Self Pay',20,0,1);
228 INSERT INTO list_options (list_id,option_id,title,seq,is_default,activity) VALUES ('Procedure_Billing','C','Bill Clinic',30,0,1);
231 #IfMissingColumn procedure_order billing_type
232 ALTER TABLE `procedure_order` ADD `billing_type` VARCHAR(4) DEFAULT NULL;
235 #IfMissingColumn procedure_order specimen_fasting
236 ALTER TABLE `procedure_order` ADD `specimen_fasting` VARCHAR(31) DEFAULT NULL;
239 #IfMissingColumn procedure_order order_psc
240 ALTER TABLE `procedure_order` ADD `order_psc` TINYINT(4) DEFAULT NULL;
243 #IfMissingColumn procedure_order order_abn
244 ALTER TABLE `procedure_order` ADD `order_abn` VARCHAR(31) NOT NULL DEFAULT 'not_required';
247 #IfMissingColumn procedure_order collector_id
248 ALTER TABLE `procedure_order` ADD `collector_id` BIGINT(11) NOT NULL DEFAULT '0';
251 #IfMissingColumn procedure_order account
252 ALTER TABLE `procedure_order` ADD `account` VARCHAR(60) DEFAULT NULL;
255 #IfMissingColumn procedure_order account_facility
256 ALTER TABLE `procedure_order` ADD `account_facility` int(11) DEFAULT NULL;
259 #IfMissingColumn procedure_order provider_number
260 ALTER TABLE `procedure_order` ADD `provider_number` VARCHAR(30) DEFAULT NULL;
263 #IfMissingColumn procedure_order procedure_order_type
264 ALTER TABLE `procedure_order` ADD `procedure_order_type` varchar(32) NOT NULL DEFAULT 'laboratory_test';
267 #IfMissingColumn procedure_order_code procedure_type
268 ALTER TABLE `procedure_order_code` ADD `procedure_type` VARCHAR(31) DEFAULT NULL;
271 #IfMissingColumn procedure_order_code transport
272 ALTER TABLE `procedure_order_code` ADD `transport` VARCHAR(31) DEFAULT NULL;
275 #IfMissingColumn procedure_type transport
276 ALTER TABLE `procedure_type` ADD `transport` VARCHAR(31) DEFAULT NULL;
279 #IfMissingColumn procedure_providers type
280 ALTER TABLE `procedure_providers` ADD `type` VARCHAR(31) DEFAULT NULL;
283 #IfMissingColumn procedure_answers procedure_code
284 ALTER TABLE `procedure_answers` ADD `procedure_code` VARCHAR(31) DEFAULT NULL;
287 #IfNotRow users username oe-system
288 INSERT INTO `users`(`username`,`password`,`lname`,`authorized`,`active`) VALUES ('oe-system','NoLogin','System Operation User',0,0);
289 INSERT INTO `gacl_aro`(`id`, `section_value`, `value`, `order_value`, `name`, `hidden`)
290 SELECT max(`id`)+1,'users','oe-system',10,'System Operation User', 0 FROM `gacl_aro`;
291 INSERT INTO `gacl_groups_aro_map`(`group_id`, `aro_id`)
293 (SELECT `id` FROM `gacl_aro_groups` WHERE parent_id=10 AND value='admin')
294 ,(SELECT `id` FROM `gacl_aro` WHERE `section_value` = 'users' AND `value` = 'oe-system')
298 #IfNotTable export_job
299 CREATE TABLE `export_job` (
300 `id` bigint(20) NOT NULL AUTO_INCREMENT,
301 `uuid` binary(16) DEFAULT NULL ,
302 `user_id` varchar(40) NOT NULL,
303 `client_id` varchar(80) NOT NULL,
304 `status` varchar(40) NOT NULL,
305 `start_time` datetime DEFAULT NULL,
306 `resource_include_time` datetime DEFAULT NULL,
307 `output_format` varchar(128) NOT NULL,
308 `request_uri` varchar(128) NOT NULL,
312 `access_token_id` text,
315 ) ENGINE=InnoDB COMMENT='fhir export jobs';
318 #IfNotRow categories name FHIR Export Document
319 SET @max_rght = (SELECT MAX(rght) FROM categories);
320 INSERT INTO categories(`id`,`name`, `value`, `parent`, `lft`, `rght`, `aco_spec`) select (select MAX(id) from categories) + 1, 'FHIR Export Document', '', 1, @max_rght, @max_rght + 1, 'admin|super' from categories where name = 'Categories';
321 UPDATE categories SET rght = rght + 2 WHERE name = 'Categories';
322 UPDATE categories_seq SET id = (select MAX(id) from categories);
325 #IfMissingColumn documents date_expires
326 ALTER TABLE `documents` ADD COLUMN `date_expires` DATETIME DEFAULT NULL;
329 #IfMissingColumn documents foreign_reference_id
330 ALTER TABLE `documents` ADD COLUMN `foreign_reference_id` bigint(20) default NULL,
331 ADD COLUMN `foreign_reference_table` VARCHAR(40) default NULL;
332 ALTER TABLE `documents` ADD KEY `foreign_reference` (`foreign_reference_id`, `foreign_reference_table`);
334 #IfNotRow background_services name WenoExchange
335 INSERT INTO `background_services` (`name`, `title`, `active`, `running`, `next_run`, `execute_interval`, `function`, `require_once`, `sort_order`) VALUES
336 ('WenoExchange', 'Weno Log Sync', 0, 0, '2021-01-18 11:25:10', 0, 'start_weno', '/library/weno_log_sync.php', 100);
339 #IfNotRow2D list_options list_id Eye_Defaults_for_GENERAL option_id ODVITREOUS
340 INSERT INTO list_options (list_id,option_id,title,seq,is_default,option_value,mapping,notes,codes,toggle_setting_1,toggle_setting_2,activity,subtype) VALUES ('Eye_Defaults_for_GENERAL', 'ODVITREOUS', 'clear', 504, 0, 0,'', 'RETINA','', 0, 0, 1,'');
343 #IfNotRow2D list_options list_id Eye_Defaults_for_GENERAL option_id OSVITREOUS
344 INSERT INTO list_options (list_id,option_id,title,seq,is_default,option_value,mapping,notes,codes,toggle_setting_1,toggle_setting_2,activity,subtype) VALUES ('Eye_Defaults_for_GENERAL', 'OSVITREOUS', 'clear', 506, 0, 0,'', 'RETINA','', 0, 0, 1,'');
347 DELETE FROM medex_icons;
348 INSERT INTO `medex_icons` (`i_UID`, `msg_type`, `msg_status`, `i_description`, `i_html`, `i_blob`) VALUES
349 (1, 'SMS', 'ALLOWED', '', '<i title="SMS is possible." class="far fa-comment-dots fa-fw"></i>', ''),
350 (2, 'SMS', 'NotAllowed', '', '<span class="fas fa-stack" title="SMS not possible"><i title="SMS is not possible." class="fas fa-comment-dots fa-fw"></i><i class="fas fa-ban fa-stack-2x text-danger"></i></span>', ''),
351 (3, 'SMS', 'SCHEDULED', '', '<span class="btn scheduled" title="SMS scheduled"><i class="fas fa-comment-dots fa-fw"></i></span>', ''),
352 (4, 'SMS', 'SENT', '', '<span class="btn" title="SMS Sent - in process" style="background-color:yellow;"><i aria-hidden="true" class="fas fa-comment-dots fa-fw"></i></span>', ''),
353 (5, 'SMS', 'READ', '', '<span class="btn" title="SMS Delivered - waiting for response" aria-label="SMS Delivered" style="background-color:#146abd;"><i aria-hidden="true" class="fas fa-comment-dots fa-inverse fa-flip-horizontal fa-fw"></i></span>', ''),
354 (6, 'SMS', 'FAILED', '', '<span class="btn" title="SMS Failed to be delivered" style="background-color:#ffc4c4;"><i aria-hidden="true" class="fas fa-comment-dots fa-fw"></i></span>', ''),
355 (7, 'SMS', 'CONFIRMED', '', '<span class="btn" title="Confirmed by SMS" style="background-color:green;"><i aria-hidden="true" class="fas fa-comment-dots fa-inverse fa-flip-horizontal fa-fw"></i></span>', ''),
356 (8, 'SMS', 'CALL', '', '<span class="btn" style="background-color: red;" title="Patient requests Office Call"><i class="fas fa-flag fa-inverse fa-fw"></i></span>', ''),
357 (9, 'SMS', 'EXTRA', '', '<span class="btn" title="EXTRA" style="background-color:#000;color:#fff;"><i class="fas fa-terminal fa-fw"></i></span>', ''),
358 (10, 'SMS', 'STOP', '', '<span class="btn btn-danger fas fa-comment-dots" title="OptOut of SMS Messaging. Demographics updated." aria-label=\'Optout SMS\'> STOP</span>', ''),
359 (11, 'AVM', 'ALLOWED', '', '<span title="Automated Voice Messages are possible" class="fas fa-phone fa-fw"></span>', ''),
360 (12, 'AVM', 'NotAllowed', '', '<span class="fas fa-stack" title="Automated Voice Messages are not allowed"><i class="fas fa-phone fa-fw fa-stack-1x"></i><i class="fas fa-ban fa-stack-2x text-danger"></i></span>', ''),
361 (13, 'AVM', 'SCHEDULED', '', '<span class="btn scheduled" title="AVM scheduled"><i class="fas fa-phone fa-fw"></i></span>', ''),
362 (14, 'AVM', 'SENT', '', '<span class="btn" title="AVM in process, no response" style="background-color:yellow;"><i class="fas fa-phone-volume fa-reverse fa-fw"></i></span>', ''),
363 (15, 'AVM', 'FAILED', '', '<span class="btn" title="AVM: Failed. Check patient\'s phone numbers." style="background-color:#ffc4c4;"><i class="fas fa-phone fa-fw"></i></span>', ''),
364 (16, 'AVM', 'CONFIRMED', '', '<span class="btn" title="Confirmed by AVM" style="padding:5px;background-color:green;"><i class="fas fa-phone fa-inverse fa-fw"></i></span>', ''),
365 (17, 'AVM', 'CALL', '', '<span class="btn" style="background-color: red;" title="Patient requests Office Call">\r\n<i class="fas fa-flag fa-inverse fa-fw"></i></span>', ''),
366 (18, 'AVM', 'Other', '', '<span class="fas fa-stack fa-lg"><i class="fas fa-square fa-stack-2x"></i><i class="fas fa-terminal fa-fw fa-stack-1x fa-inverse"></i></span>', ''),
367 (19, 'AVM', 'STOP', '', '<span class="btn btn-danger" title="OptOut of Voice Messaging. Demographics updated." aria-label="Optout AVM"><i class="fas fa-phone" aria-hidden="true"> STOP</i></span>', ''),
368 (20, 'EMAIL', 'ALLOWED', '', '<span title="EMAIL is possible" class="fas fa-envelope fa-fw"></span>', ''),
369 (21, 'EMAIL', 'NotAllowed', '', '<span class="fas fa-stack" title="EMAIL is not possible"><i class="fas fa-envelope fa-fw fa-stack-1x"></i><i class="fas fa-ban fa-stack-2x text-danger"></i></span>', ''),
370 (22, 'EMAIL', 'SCHEDULED', '', '<span class="btn scheduled" title="EMAIL scheduled"><i class="fas fa-envelope fa-fw"></i></span>', ''),
371 (23, 'EMAIL', 'SENT', '', '<span class="btn" style="background-color:yellow;" title="EMAIL Message sent, not opened"><i class="fas fa-envelope fa-fw"></i></span>', ''),
372 (24, 'EMAIL', 'READ', '', '<span class="btn" style="background-color:#146abd;" title="E-Mail was read/opened by patient" aria-label="Read via email"><i aria-hidden="true" class="fas fa-envelope fa-inverse fa-fw"></i></span>', ''),
373 (25, 'EMAIL', 'FAILED', '', '<span class="btn" title="EMAIL: Failed. Check patient\'s email address." style="background-color:#ffc4c4;"><i class="fas fa-envelope fa-fw"></i></span>', ''),
374 (26, 'EMAIL', 'CONFIRMED', '', '<span class="btn" title="Confirmed by E-Mail" aria-label="Confirmed via email" style="background-color: green;"><i aria-hidden="true" class="fas fa-envelope fa-inverse fa-fw"></i></span>', ''),
375 (27, 'EMAIL', 'CALL', '', '<span class="btn" style="background-color: red;" title="Patient requests Office Call"><i class="fas fa-flag fa-inverse fa-fw"></i></span>', ''),
376 (28, 'EMAIL', 'Other', '', '<span class="fas fa-stack fa-lg"><i class="fas fa-square fa-stack-2x"></i><i class="fas fa-terminal fa-fw fa-stack-1x fa-inverse fa-fw"></i></span>', ''),
377 (29, 'EMAIL', 'STOP', '', '<span class="btn btn-danger" title="OptOut of EMAIL Messaging. Demographics updated." aria-label="Optout EMAIL"><i class="fas fa-envelope-o" aria-hidden="true"> STOP</i></span>', ''),
378 (30, 'POSTCARD', 'SENT', '', '<span class="btn" title="Postcard Sent - in process" style="padding:5px;background-color:yellow;color:black"><i class="fas fa-image fa-fw"></i></span>', ''),
379 (31, 'POSTCARD', 'READ', '', '<span class="btn" style="background-color:#146abd;" title="e-Postcard was delivered" aria-label="Postcard Delivered"><i class="fas fa-image fa-fw" aria-hidden="true"></i></span>', ''),
380 (32, 'POSTCARD', 'FAILED', '', '<span class="fas fa-stack fa-lg" title="Delivery Failure - check Address for this patient"><i class="fas fa-image fa-fw fa-stack-1x"></i><i class="fas fa-ban fa-stack-2x text-danger"></i></span>', ''),
381 (33, 'POSTCARD', 'SCHEDULED', '', '<span class="btn scheduled" title="Postcard Campaign Event is scheduled."><i class="fas fa-image fa-fw"></i></span>', ''),
382 (36, 'AVM', 'READ', '', '<span class="btn" title="AVM completed - waiting for manual response" aria-label="AVM Delivered" style="padding:5px;background-color:#146abd;"><i class="fas fa-inverse fa-phone fa-fw" aria-hidden="true"></i></span>', ''),
383 (37, 'SMS', 'CALLED', '', '<span class="btn" style="background-color:#146abd;" title="Patient requests Office Call: COMPLETED"><i class="fas fa-flag fa-fw"></i></span>', ''),
384 (38, 'AVM', 'CALLED', '', '<span class="btn" style="background-color:#146abd;" title="Patient requests Office Call: COMPLETED"><i class="fas fa-flag fa-fw"></i></span> ', ''),
385 (39, 'EMAIL', 'CALLED', '', '<span class="btn" style="background-color:#146abd;" title="Patient requests Office Call: COMPLETED"><i class="fas fa-flag fa-fw"></i></span>', '');
387 #IfNotRow2D list_options list_id sex option_id UNK
388 INSERT INTO list_options ( list_id, option_id, title, seq, is_default, codes ) VALUES ('sex', 'UNK', 'Unknown', 10, 0, 'HL7:UNK');
391 #IfNotRow3D list_options list_id sex option_id Female codes HL7:F
392 UPDATE `list_options` SET `codes` = 'HL7:F' WHERE `list_id` = 'sex' AND `option_id` = 'Female';
395 #IfNotRow3D list_options list_id sex option_id Male codes HL7:M
396 UPDATE `list_options` SET `codes` = 'HL7:M' WHERE `list_id` = 'sex' AND `option_id` = 'Male';
399 #IfMissingColumn patient_data sexual_orientation
400 ALTER TABLE patient_data ADD sexual_orientation TEXT;
403 #IfMissingColumn patient_data gender_identity
404 ALTER TABLE patient_data ADD gender_identity TEXT;
407 #IfNotRow2D list_options list_id lists option_id sexual_orientation
408 INSERT INTO list_options (`list_id`, `option_id`, `title`, `seq`) VALUES ('lists', 'sexual_orientation', 'Sexual Orientation', '13');
409 INSERT INTO `list_options`(`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `codes`) VALUES ('sexual_orientation','20430005','Straight or heterosexual',10,0,0,'SNOMED:20430005');
410 INSERT INTO `list_options`(`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `codes`) VALUES ('sexual_orientation','38628009','Lesbian, gay or homosexual',20,0,0,'SNOMED:38628009');
411 INSERT INTO `list_options`(`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `codes`) VALUES ('sexual_orientation','42035005','Bisexual',30,0,0,'SNOMED:42035005');
412 INSERT INTO `list_options`(`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `codes`) VALUES ('sexual_orientation','comment_OTH','Something else, please describe',40,0,0,'HL7:OTH');
413 INSERT INTO `list_options`(`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `codes`) VALUES ('sexual_orientation','UNK','Don\'t know',50,0,0,'HL7:UNK');
414 INSERT INTO `list_options`(`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `codes`) VALUES ('sexual_orientation','ASKU','Choose not to disclose',60,0,0,'HL7:ASKU');
417 #IfNotRow2D list_options list_id lists option_id gender_identity
418 INSERT INTO list_options (`list_id`, `option_id`, `title`, `seq`) VALUES ('lists', 'gender_identity', 'Gender Identity', '1');
419 INSERT INTO `list_options`(`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `codes`) VALUES ('gender_identity','446151000124109','Identifies as Male',10,0,0,'SNOMED:446151000124109');
420 INSERT INTO `list_options`(`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `codes`) VALUES ('gender_identity','446141000124107','Identifies as Female',20,0,0,'SNOMED:446141000124107');
421 INSERT INTO `list_options`(`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `codes`) VALUES ('gender_identity','407377005','Female-to-Male (FTM)/Transgender Male/Trans Man',30,0,0,'SNOMED:407377005');
422 INSERT INTO `list_options`(`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `codes`) VALUES ('gender_identity','407376001','Male-to-Female (MTF)/Transgender Female/Trans Woman',40,0,0,'SNOMED:407376001');
423 INSERT INTO `list_options`(`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `codes`) VALUES ('gender_identity','446131000124102','Genderqueer, neither exclusively male nor female',50,0,0,'SNOMED:446131000124102');
424 INSERT INTO `list_options`(`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `codes`) VALUES ('gender_identity','comment_OTH','Additional gender category or other, please specify',60,0,0,'HL7:OTH');
425 INSERT INTO `list_options`(`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `codes`) VALUES ('gender_identity','ASKU','Choose not to disclose',70,0,0,'HL7:ASKU');
428 #IfNotRow2D layout_options form_id DEM field_id gender_identity
429 SET @group_id = (SELECT group_id FROM layout_options WHERE field_id='sex' AND form_id='DEM');
430 SET @backup_group_id = (SELECT group_id FROM layout_options WHERE field_id='DOB' AND form_id='DEM');
431 SET @seq = (SELECT MAX(seq) FROM layout_options WHERE group_id = IFNULL(@group_id,@backup_group_id) AND form_id='DEM');
432 INSERT INTO `layout_options` (`form_id`,`field_id`,`group_id`,`title`,`seq`,`data_type`,`uor`,`fld_length`,`max_length`,`list_id`,`titlecols`,`datacols`,`default_value`,`edit_options`,`description`,`fld_rows`) VALUES ('DEM', 'gender_identity', IFNULL(@group_id,@backup_group_id), 'Gender Identity', @seq+1, 46, 1, 0, 100, 'gender_identity' , 1 , 1 , '' , 'N' , 'Gender Identity', 0);
435 #IfNotRow2D layout_options form_id DEM field_id sexual_orientation
436 SET @group_id = (SELECT group_id FROM layout_options WHERE field_id='sex' AND form_id='DEM');
437 SET @backup_group_id = (SELECT group_id FROM layout_options WHERE field_id='DOB' AND form_id='DEM');
438 SET @seq = (SELECT MAX(seq) FROM layout_options WHERE group_id = IFNULL(@group_id,@backup_group_id) AND form_id='DEM');
439 INSERT INTO `layout_options` (`form_id`,`field_id`,`group_id`,`title`,`seq`,`data_type`,`uor`,`fld_length`,`max_length`,`list_id`,`titlecols`,`datacols`,`default_value`,`edit_options`,`description`,`fld_rows`) VALUES ('DEM', 'sexual_orientation', IFNULL(@group_id,@backup_group_id), 'Sexual Orientation', @seq+1, 46, 1, 0, 100, 'sexual_orientation', 1, 1, '' ,'N' ,'Sexual Orientation', 0);
442 #IfMissingColumn users google_signin_email
443 ALTER TABLE `users` ADD COLUMN `google_signin_email` VARCHAR(255) UNIQUE DEFAULT NULL;
446 #IfNotRow background_services name UUID_Service
447 INSERT INTO `background_services` (`name`, `title`, `active`, `running`, `next_run`, `execute_interval`, `function`, `require_once`, `sort_order`) VALUES
448 ('UUID_Service', 'Automated UUID Creation Service', 1, 0, '2021-01-18 11:25:10', 240, 'autoPopulateAllMissingUuids', '/library/uuid.php', 100);
451 #IfNotRow codes code_text SARS-COV-2 (COVID-19) vaccine, vector non-replicating, recombinant spike protein-Ad26, preservative free, 0.5 mL
452 SET @codetypeid = (SELECT `ct_id` FROM `code_types` WHERE `ct_key` = 'CVX');
453 INSERT INTO `codes` (`id`, `code_text`, `code_text_short`, `code`, `code_type`, `modifier`, `units`, `fee`, `superbill`, `related_code`, `taxrates`, `active`)
455 (NULL, "SARS-COV-2 (COVID-19) vaccine, vector non-replicating, recombinant spike protein-Ad26, preservative free, 0.5 mL", "COVID-19 vaccine, vector-nr, rS-Ad26, PF, 0.5 mL", 212, @codetypeid, '', 0, 0, '', '', '', 1);
458 #IfNotRow2D list_options list_id lists option_id Document_Template_Categories
459 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `mapping`, `notes`, `codes`, `toggle_setting_1`, `toggle_setting_2`, `activity`) VALUES ('lists','Document_Template_Categories','Document Template Categories',0,1,0,'',NULL,'',0,0,1);
460 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `mapping`, `notes`, `codes`, `toggle_setting_1`, `toggle_setting_2`, `activity`) VALUES ('Document_Template_Categories','repository','Repository',1,1,0,'','','',0,0,1);
462 #IfMissingColumn layout_group_properties grp_save_close
463 ALTER TABLE `layout_group_properties` ADD COLUMN `grp_save_close` tinyint(1) not null default 0;
466 #IfMissingColumn layout_group_properties grp_init_open
467 ALTER TABLE `layout_group_properties` ADD COLUMN `grp_init_open` tinyint(1) not null default 0;
468 UPDATE layout_group_properties AS p, layout_options AS o SET p.grp_init_open = 1 WHERE
469 o.form_id = p.grp_form_id AND o.group_id = p.grp_group_id AND o.uor > 0 AND o.edit_options LIKE '%I%';
470 UPDATE layout_group_properties AS p SET p.grp_init_open = 1 WHERE p.grp_group_id = '1' AND
471 (SELECT count(*) FROM layout_options AS o WHERE o.form_id = p.grp_form_id AND o.uor > 0 AND o.edit_options LIKE '%I%') = 0;
474 #IfMissingColumn layout_group_properties grp_last_update
475 ALTER TABLE `layout_group_properties` ADD COLUMN `grp_last_update` timestamp NULL;
478 #---------- Support for Referrals section of LBFs. ----------#
479 #IfMissingColumn layout_group_properties grp_referrals
480 ALTER TABLE `layout_group_properties` ADD COLUMN `grp_referrals` tinyint(1) not null default 0;
483 #IfMissingColumn drug_sales trans_type
484 ALTER TABLE drug_sales
485 ADD trans_type tinyint NOT NULL DEFAULT 1 COMMENT '1=sale, 2=purchase, 3=return, 4=transfer, 5=adjustment';
486 UPDATE drug_sales SET trans_type = 4 WHERE pid = 0 AND xfer_inventory_id != 0;
487 UPDATE drug_sales SET trans_type = 5 WHERE trans_type = 1 AND pid = 0 AND fee = 0;
488 UPDATE drug_sales SET trans_type = 2 WHERE trans_type = 1 AND pid = 0 AND quantity >= 0;
489 UPDATE drug_sales SET trans_type = 3 WHERE trans_type = 1 AND pid = 0;
492 #IfMissingColumn ar_activity post_date
493 ALTER TABLE ar_activity
494 ADD post_date date DEFAULT NULL COMMENT 'Posting date if specified at payment time';
495 UPDATE ar_activity SET post_date = post_time;
498 #IfMissingColumn form_encounter shift
499 ALTER TABLE form_encounter ADD shift varchar(31) NOT NULL DEFAULT '';
502 #IfNotRow2D list_options list_id lists option_id shift
503 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','shift','Shifts', 1, 0);
506 #IfMissingColumn form_encounter voucher_number
507 ALTER TABLE form_encounter ADD voucher_number varchar(255) NOT NULL DEFAULT '' COMMENT 'also called referral number';
510 #IfMissingColumn billing chargecat
511 ALTER TABLE `billing` ADD COLUMN `chargecat` varchar(31) default '';
514 #IfMissingColumn drug_sales chargecat
515 ALTER TABLE `drug_sales` ADD COLUMN `chargecat` varchar(31) default '';
517 #IfMissingColumn users_facility warehouse_id
518 ALTER TABLE `users_facility` ADD COLUMN `warehouse_id` varchar(31) NOT NULL default '';
519 ALTER TABLE `users_facility` DROP PRIMARY KEY, ADD PRIMARY KEY (`tablename`,`table_id`,`facility_id`,`warehouse_id`);
522 #IfNotColumnType drugs form varchar(31)
523 ALTER TABLE `drugs` CHANGE `form` `form` varchar(31) NOT NULL default '0';
525 #IfNotColumnType drugs unit varchar(31)
526 ALTER TABLE `drugs` CHANGE `unit` `unit` varchar(31) NOT NULL default '0';
528 #IfNotColumnType drugs route varchar(31)
529 ALTER TABLE `drugs` CHANGE `route` `route` varchar(31) NOT NULL default '0';
532 #IfMissingColumn drug_templates pkgqty
533 ALTER TABLE `drug_templates` ADD COLUMN `pkgqty` float NOT NULL DEFAULT 1.0 COMMENT 'Number of product items per template item';
536 #IfMissingColumn voids reason
537 ALTER TABLE `voids` ADD COLUMN `reason` VARCHAR(31) default '';
540 #IfMissingColumn voids notes
541 ALTER TABLE `voids` ADD COLUMN `notes` VARCHAR(255) default '';
544 #IfNotRow2D list_options list_id lists option_id void_reasons
545 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`) VALUES ('lists','void_reasons','Void Reasons',1,0);
546 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`) VALUES ('void_reasons','one' ,'Reason 1',10,1);
547 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`) VALUES ('void_reasons','two' ,'Reason 2',20,0);
550 #IfNotIndex log patient_id
551 CREATE INDEX `patient_id` ON `log` (`patient_id`);
554 #IfNotRow2D list_options list_id lists option_id paymethod
555 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`) VALUES ('lists','paymethod','Payment Methods', 1,0);
556 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`) VALUES ('paymethod','Cash' ,'Cash' ,10,0);
557 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`) VALUES ('paymethod','Check','Check',20,0);
558 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`) VALUES ('paymethod','MC' ,'MC' ,30,0);
559 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`) VALUES ('paymethod','VISA' ,'VISA' ,40,0);
560 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`) VALUES ('paymethod','AMEX' ,'AMEX' ,50,0);
561 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`) VALUES ('paymethod','DISC' ,'DISC' ,60,0);
562 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`) VALUES ('paymethod','Other','Other',70,0);
565 #IfNotRow2D issue_types category default type medical_device
566 INSERT INTO `issue_types` (`ordering`,`category`,`type`,`plural`,`singular`,`abbreviation`,`style`,`force_show`) VALUES ('35','default','medical_device','Medical Devices','Device','I','0','0');
569 #IfMissingColumn lists udi
570 ALTER TABLE `lists` ADD COLUMN `udi` varchar(255) default NULL;
573 #IfMissingColumn lists udi_data
574 ALTER TABLE `lists` ADD COLUMN `udi_data` text;
577 #IfNotRow globals gl_name gbl_fac_warehouse_restrictions
578 INSERT INTO `globals` (gl_name, gl_index, gl_value) SELECT 'gbl_fac_warehouse_restrictions', gl_index, gl_value
579 FROM globals WHERE gl_name = 'restrict_user_facility';
582 #IfNotRow2D list_options list_id lists option_id chargecats
583 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`)
584 VALUES ('lists','chargecats','Customers', 1,0);
587 #IfNotRow2D list_options list_id lists option_id Clinical_Note_Type
588 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `mapping`, `notes`, `codes`, `toggle_setting_1`, `toggle_setting_2`, `activity`, `subtype`, `edit_options`) VALUES ('lists','Clinical_Note_Type','Clinical Note Type',0,1,0,'',NULL,'',0,0,1,'',1);
589 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `mapping`, `notes`, `codes`, `toggle_setting_1`, `toggle_setting_2`, `activity`, `subtype`, `edit_options`) VALUES ('Clinical_Note_Type','progress_note','Progress Note',10,0,0,'','LOINC:11506-3','',0,0,1,'',1);
590 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `mapping`, `notes`, `codes`, `toggle_setting_1`, `toggle_setting_2`, `activity`, `subtype`, `edit_options`) VALUES ('Clinical_Note_Type','nurse_note','Nurse Note',20,0,0,'','LOINC:34746-8','',0,0,1,'',1);
591 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `mapping`, `notes`, `codes`, `toggle_setting_1`, `toggle_setting_2`, `activity`, `subtype`, `edit_options`) VALUES ('Clinical_Note_Type','history_physical','History & Physical',30,0,0,'','LOINC:34117-2','',0,0,1,'',1);
592 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `mapping`, `notes`, `codes`, `toggle_setting_1`, `toggle_setting_2`, `activity`, `subtype`, `edit_options`) VALUES ('Clinical_Note_Type','general_note','General Note',40,0,0,'','LOINC:34109-9','',0,0,1,'',1);
593 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `mapping`, `notes`, `codes`, `toggle_setting_1`, `toggle_setting_2`, `activity`, `subtype`, `edit_options`) VALUES ('Clinical_Note_Type','discharge_summary','Discharge Summary Note',50,0,0,'','LOINC:18842-5','',0,0,1,'',1);
594 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `mapping`, `notes`, `codes`, `toggle_setting_1`, `toggle_setting_2`, `activity`, `subtype`, `edit_options`) VALUES ('Clinical_Note_Type','procedure_note','Procedure Note',60,0,0,'','LOINC:28570-0','',0,0,1,'',1);
595 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `mapping`, `notes`, `codes`, `toggle_setting_1`, `toggle_setting_2`, `activity`, `subtype`, `edit_options`) VALUES ('Clinical_Note_Type','consultation_note','Consultation Note',70,0,0,'','LOINC:81222-2','',0,0,1,'',1);
596 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `mapping`, `notes`, `codes`, `toggle_setting_1`, `toggle_setting_2`, `activity`, `subtype`, `edit_options`) VALUES ('Clinical_Note_Type','imaging_narrative','Imaging Narrative',80,0,0,'','LOINC:28570-0','',0,0,1,'',1);
597 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `mapping`, `notes`, `codes`, `toggle_setting_1`, `toggle_setting_2`, `activity`, `subtype`, `edit_options`) VALUES ('Clinical_Note_Type','laboratory_report_narrative','Laboratory Report Narrative',90,0,0,'','','',0,0,1,'',1);
598 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `mapping`, `notes`, `codes`, `toggle_setting_1`, `toggle_setting_2`, `activity`, `subtype`, `edit_options`) VALUES ('Clinical_Note_Type','pathology_report_narrative','Pathology Report Narrative',100,0,0,'','','',0,0,1,'',1);
601 #IfNotTable form_clinical_notes
602 CREATE TABLE `form_clinical_notes` (
603 `id` bigint(20) NOT NULL,
604 `date` DATE DEFAULT NULL,
605 `pid` bigint(20) DEFAULT NULL,
606 `encounter` varchar(255) DEFAULT NULL,
607 `user` varchar(255) DEFAULT NULL,
608 `groupname` varchar(255) DEFAULT NULL,
609 `authorized` tinyint(4) DEFAULT NULL,
610 `activity` tinyint(4) DEFAULT NULL,
611 `code` varchar(255) DEFAULT NULL,
614 `external_id` VARCHAR(30) DEFAULT NULL,
615 `clinical_notes_type` varchar(100) DEFAULT NULL
617 INSERT INTO `registry` (`name`, `state`, `directory`, `sql_run`, `unpackaged`, `date`, `priority`, `category`, `nickname`, `patient_encounter`, `therapy_group_encounter`, `aco_spec`) VALUES ('Clinical Notes', 1, 'clinical_notes', 1, 1, '2015-09-09 00:00:00', 0, 'Clinical', '', 1, 0, 'encounters|notes');