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
25 -- arguments: table_name colname value
26 -- behavior: If the table table_name does not have a row where colname = value, the block will be executed.
29 -- arguments: table_name colname value colname2 value2
30 -- behavior: If the table table_name does not have a row where colname = value AND colname2 = value2, the block will be executed.
33 -- arguments: table_name colname value colname2 value2 colname3 value3
34 -- 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.
37 -- arguments: table_name colname value colname2 value2 colname3 value3 colname4 value4
38 -- 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.
41 -- 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.
42 -- arguments: table_name colname value colname2 value2 colname3 value3
43 -- behavior: The block will be executed if both statements below are true:
44 -- 1) The table table_name does not have a row where colname = value AND colname2 = value2.
45 -- 2) The table table_name does not have a row where colname = value AND colname3 = value3.
48 -- arguments: table_name colname value colname2 value2
49 -- behavior: If the table table_name does have a row where colname = value AND colname2 = value2, the block will be executed.
52 -- arguments: table_name colname value colname2 value2 colname3 value3
53 -- behavior: If the table table_name does have a row where colname = value AND colname2 = value2 AND colname3 = value3, the block will be executed.
56 -- desc: This function is most often used for dropping of indexes/keys.
57 -- arguments: table_name colname
58 -- behavior: If the table and index exist the relevant statements are executed, otherwise not.
61 -- desc: This function will allow adding of indexes/keys.
62 -- arguments: table_name colname
63 -- behavior: If the index does not exist, it will be created
66 -- all blocks are terminated with a #EndIf statement.
69 -- Custom function for creating Reaction List
71 -- #IfNotListOccupation
72 -- Custom function for creating Occupation List
74 -- #IfTextNullFixNeeded
75 -- desc: convert all text fields without default null to have default null.
79 -- desc: Execute SQL if the table has been created with given engine specified.
80 -- arguments: table_name engine
81 -- behavior: Use when engine conversion requires more than one ALTER TABLE
83 -- #IfInnoDBMigrationNeeded
84 -- desc: find all MyISAM tables and convert them to InnoDB.
86 -- behavior: can take a long time.
88 #IfMissingColumn list_options edit_options
89 ALTER TABLE `list_options` ADD `edit_options` TINYINT(1) NOT NULL DEFAULT '1';
92 #IfMissingColumn list_options timestamp
93 ALTER TABLE `list_options` ADD `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;
96 #IfNotTable multiple_db
97 CREATE TABLE `multiple_db` (
98 `id` int(11) NOT NULL AUTO_INCREMENT,
99 `namespace` varchar(255) NOT NULL,
100 `username` varchar(255) NOT NULL,
102 `dbname` varchar(255) NOT NULL,
103 `host` varchar(255) NOT NULL DEFAULT 'localhost',
104 `port` smallint(4) NOT NULL DEFAULT '3306',
105 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
106 UNIQUE KEY `namespace` (namespace),
111 #IfNotRow2D list_options list_id page_validation option_id therapy_groups_add#addGroup
112 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `notes`, `activity`) VALUES ('page_validation', 'therapy_groups_add#addGroup', '/interface/therapy_groups/index.php?method=addGroup', 120, '{group_name:{presence: true}}', 1);
115 #IfNotRow2D list_options list_id page_validation option_id therapy_groups_edit#editGroup
116 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `notes`, `activity`) VALUES ('page_validation', 'therapy_groups_edit#editGroup', '/interface/therapy_groups/index.php?method=groupDetails', 125, '{group_name:{presence: true}}', 1);
119 #IfNotRow2D list_options list_id page_validation option_id tg_add#add-participant-form
120 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `notes`, `activity`) VALUES ('page_validation', 'tg_add#add-participant-form', '/interface/therapy_groups/index.php?method=groupParticipants', 130, '{participant_name:{presence: true}, group_patient_start:{presence: true}}', 1);
123 #IfNotRow2D list_options list_id page_validation option_id add_edit_event#theform_groups
124 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`)
125 VALUES ('page_validation','add_edit_event#theform_groups','/interface/main/calendar/add_edit_event.php?group=true',150,0,0,'','{form_group:{presence: true}}','',0,0,1,'');
128 #IfNotRow2D list_options list_id page_validation option_id common#new-encounter-form
129 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `notes`, `activity`) VALUES ('page_validation', 'common#new-encounter-form', '/interface/forms/newGroupEncounter/common.php', 160, '{pc_catid:{exclusion: ["_blank"]}}', 1);
133 #IfNotTable therapy_groups
134 CREATE TABLE `therapy_groups` (
135 `group_id` int(11) NOT NULL auto_increment,
136 `group_name` varchar(255) NOT NULL ,
137 `group_start_date` date NOT NULL ,
138 `group_end_date` date,
139 `group_type` tinyint NOT NULL,
140 `group_participation` tinyint NOT NULL,
141 `group_status` int(11) NOT NULL,
143 `group_guest_counselors` varchar(255),
144 PRIMARY KEY (`group_id`)
148 #IfNotTable therapy_groups_participants
149 CREATE TABLE `therapy_groups_participants` (
150 `group_id` int(11) NOT NULL,
151 `pid` int(11) NOT NULL ,
152 `group_patient_status` int(11) NOT NULL,
153 `group_patient_start` date NOT NULL ,
154 `group_patient_end` date,
155 `group_patient_comment` text,
156 PRIMARY KEY (`group_id`,`pid`)
160 #IfNotTable therapy_groups_participant_attendance
161 CREATE TABLE `therapy_groups_participant_attendance` (
162 `form_id` int(11) NOT NULL,
163 `pid` int(11) NOT NULL ,
164 `meeting_patient_comment` text ,
165 `meeting_patient_status` varchar(15),
166 PRIMARY KEY (`form_id`,`pid`)
170 #IfNotTable therapy_groups_counselors
171 CREATE TABLE `therapy_groups_counselors`(
172 `group_id` int(11) NOT NULL,
173 `user_id` int(11) NOT NULL,
174 PRIMARY KEY (`group_id`,`user_id`)
178 #IfMissingColumn openemr_postcalendar_events pc_gid
179 ALTER TABLE openemr_postcalendar_events ADD pc_gid int(11) DEFAULT 0;
182 #IfNotRow2D list_options list_id lists option_id groupstat
183 INSERT INTO list_options (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`) VALUES ('lists', 'groupstat', 'Group Statuses', '13', '0', '0');
184 INSERT INTO list_options (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `notes`) VALUES ('groupstat', '-', '- None', '10', '0', '0', 'FEFDCF|0');
185 INSERT INTO list_options (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `notes`) VALUES ('groupstat', '=', '= Took Place', '20', '0', '0', 'FF2414|0');
186 INSERT INTO list_options (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `notes`) VALUES ('groupstat', '>', '> Did Not Take Place', '30', '0', '0', 'BFBFBF|0');
187 INSERT INTO list_options (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `notes`) VALUES ('groupstat', '<', '< Not Reported', '40', '0', '0', 'FEFDCF|0');
190 #IfNotRow openemr_postcalendar_categories pc_catname Group Therapy
191 INSERT INTO openemr_postcalendar_categories (`pc_catname`, `pc_catcolor`, `pc_recurrspec`, `pc_duration` ,`pc_cattype` , `pc_active` , `pc_seq`)
192 VALUES ('Group Therapy' , '#BFBFBF' , 'a:5:{s:17:"event_repeat_freq";s:1:"0";s:22:"event_repeat_freq_type";s:1:"0";s:19:"event_repeat_on_num";s:1:"1";s:19:"event_repeat_on_day";s:1:"0";s:20:"event_repeat_on_freq";s:1:"0";}', '3600', '3', '1', '90');
196 #IfNotTable form_groups_encounter
197 CREATE TABLE `form_groups_encounter` (
198 `id` bigint(20) NOT NULL auto_increment,
199 `date` datetime default NULL,
202 `facility_id` int(11) NOT NULL default '0',
203 `group_id` bigint(20) default NULL,
204 `encounter` bigint(20) default NULL,
205 `onset_date` datetime default NULL,
206 `sensitivity` varchar(30) default NULL,
208 `pc_catid` int(11) NOT NULL default '5' COMMENT 'event category from openemr_postcalendar_categories',
209 `last_level_billed` int NOT NULL DEFAULT 0 COMMENT '0=none, 1=ins1, 2=ins2, etc',
210 `last_level_closed` int NOT NULL DEFAULT 0 COMMENT '0=none, 1=ins1, 2=ins2, etc',
211 `last_stmt_date` date DEFAULT NULL,
212 `stmt_count` int NOT NULL DEFAULT 0,
213 `provider_id` INT(11) DEFAULT '0' COMMENT 'default and main provider for this visit',
214 `supervisor_id` INT(11) DEFAULT '0' COMMENT 'supervising provider, if any, for this visit',
215 `invoice_refno` varchar(31) NOT NULL DEFAULT '',
216 `referral_source` varchar(31) NOT NULL DEFAULT '',
217 `billing_facility` INT(11) NOT NULL DEFAULT 0,
218 `external_id` VARCHAR(20) DEFAULT NULL,
219 `pos_code` tinyint(4) default NULL,
220 `counselors` VARCHAR (255),
221 `appt_id` INT(11) default NULL,
223 KEY `pid_encounter` (`group_id`, `encounter`),
224 KEY `encounter_date` (`date`)
225 ) ENGINE=InnoDB AUTO_INCREMENT=1 ;
228 #IfMissingColumn forms therapy_group_id
229 ALTER TABLE `forms` ADD `therapy_group_id` INT(11) DEFAULT NULL;
232 #IfMissingColumn registry patient_encounter
233 ALTER TABLE `registry` ADD `patient_encounter` TINYINT NOT NULL DEFAULT '1';
236 #IfMissingColumn registry therapy_group_encounter
237 ALTER TABLE `registry` ADD `therapy_group_encounter` TINYINT NOT NULL DEFAULT '0';
241 #IfNotRow2D list_options list_id lists option_id attendstat
242 INSERT INTO list_options (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`) VALUES ('lists', 'attendstat', 'Group Attendance Statuses', '15', '0', '0');
243 INSERT INTO list_options (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `notes`, `toggle_setting_1`) VALUES ('attendstat', '-', '- Not Reported', '10', '0', '0', 'FEFDCF|0', '0');
244 INSERT INTO list_options (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `notes`, `toggle_setting_1`) VALUES ('attendstat', '@', '@ Attended', '20', '0', '0', 'FF2414|0', '1');
245 INSERT INTO list_options (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `notes`, `toggle_setting_1`) VALUES ('attendstat', '?', '? Did Not Attend', '30', '0', '0', 'BFBFBF|0', '1');
246 INSERT INTO list_options (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `notes`, `toggle_setting_1`) VALUES ('attendstat', '~', '~ Late Arrival', '40', '0', '0', 'BFBFBF|0', '1');
247 INSERT INTO list_options (`list_id`, `option_id`, `title`, `seq`, `is_default`, `option_value`, `notes`, `toggle_setting_1`) VALUES ('attendstat', 'x', 'x Cancelled', '50', '0', '0', 'FEFDCF|0', '0');
250 #IfNotRow registry directory group_attendance
251 INSERT INTO registry (name, state, directory, sql_run, unpackaged, date, priority, category, nickname, patient_encounter, therapy_group_encounter) VALUES ('Group Attendance Form', 1, 'group_attendance', 1, 1, '2015-10-15 00:00:00', 0, 'Clinical', '',0,1);
254 #IfNotRow registry directory newGroupEncounter
255 INSERT INTO registry (name, state, directory, sql_run, unpackaged, date, priority, category, nickname, patient_encounter, therapy_group_encounter) VALUES ('New Group Encounter Form', 1, 'newGroupEncounter', 1, 1, '2015-10-15 00:00:00', 0, 'Clinical', '',0,1);
258 #IfNotTable form_therapy_groups_attendance
259 CREATE TABLE `form_therapy_groups_attendance` (
260 id bigint(20) auto_increment,
264 groupname varchar(255),
265 authorized tinyint(4),
266 encounter_id int(11),
272 #IfNotRow2D list_options list_id lists option_id files_white_list
273 INSERT INTO list_options (`list_id`, `option_id`, `title`) VALUES ('lists', 'files_white_list', 'Files type white list');
276 #IfNotTable onsite_documents
277 CREATE TABLE `onsite_documents` (
278 `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
279 `pid` int(10) UNSIGNED DEFAULT NULL,
280 `facility` int(10) UNSIGNED DEFAULT NULL,
281 `provider` int(10) UNSIGNED DEFAULT NULL,
282 `encounter` int(10) UNSIGNED DEFAULT NULL,
283 `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
284 `doc_type` varchar(255) NOT NULL,
285 `patient_signed_status` smallint(5) UNSIGNED NOT NULL,
286 `patient_signed_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
287 `authorize_signed_time` datetime DEFAULT NULL,
288 `accept_signed_status` smallint(5) NOT NULL,
289 `authorizing_signator` varchar(50) NOT NULL,
290 `review_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
291 `denial_reason` varchar(255) NOT NULL,
292 `authorized_signature` text,
293 `patient_signature` text,
294 `full_document` blob,
295 `file_name` varchar(255) NOT NULL,
296 `file_path` varchar(255) NOT NULL,
298 ) ENGINE=InnoDB AUTO_INCREMENT=1 ;
301 #IfNotTable onsite_mail
302 CREATE TABLE `onsite_mail` (
303 `id` bigint(20) NOT NULL AUTO_INCREMENT,
304 `date` datetime DEFAULT NULL,
305 `owner` bigint(20) DEFAULT NULL,
306 `user` varchar(255) DEFAULT NULL,
307 `groupname` varchar(255) DEFAULT NULL,
308 `activity` tinyint(4) DEFAULT NULL,
309 `authorized` tinyint(4) DEFAULT NULL,
310 `header` varchar(255) DEFAULT NULL,
311 `title` varchar(255) DEFAULT NULL,
313 `recipient_id` varchar(128) DEFAULT NULL,
314 `recipient_name` varchar(255) DEFAULT NULL,
315 `sender_id` varchar(128) DEFAULT NULL,
316 `sender_name` varchar(255) DEFAULT NULL,
317 `assigned_to` varchar(255) DEFAULT NULL,
318 `deleted` tinyint(4) DEFAULT '0' COMMENT 'flag indicates note is deleted',
319 `delete_date` datetime DEFAULT NULL,
320 `mtype` varchar(128) DEFAULT NULL,
321 `message_status` varchar(20) NOT NULL DEFAULT 'New',
322 `mail_chain` int(11) DEFAULT NULL,
323 `reply_mail_chain` int(11) DEFAULT NULL,
324 `is_msg_encrypted` tinyint(2) DEFAULT '0' COMMENT 'Whether messsage encrypted 0-Not encrypted, 1-Encrypted',
327 ) ENGINE=InnoDB AUTO_INCREMENT=1 ;
330 #IfNotTable onsite_messages
331 CREATE TABLE `onsite_messages` (
332 `id` int(11) NOT NULL AUTO_INCREMENT,
333 `username` varchar(64) NOT NULL,
335 `ip` varchar(15) NOT NULL,
336 `date` datetime NOT NULL,
337 `sender_id` int(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'who sent id',
338 `recip_id` varchar(255) NOT NULL COMMENT 'who to id array',
340 ) ENGINE=InnoDB COMMENT='Portal messages' AUTO_INCREMENT=1 ;
343 #IfNotTable onsite_online
344 CREATE TABLE `onsite_online` (
345 `hash` varchar(32) NOT NULL,
346 `ip` varchar(15) NOT NULL,
347 `last_update` datetime NOT NULL,
348 `username` varchar(64) NOT NULL,
349 `userid` int(11) UNSIGNED DEFAULT NULL,
354 #IfNotTable onsite_portal_activity
355 CREATE TABLE `onsite_portal_activity` (
356 `id` bigint(20) NOT NULL AUTO_INCREMENT,
357 `date` datetime DEFAULT NULL,
358 `patient_id` bigint(20) DEFAULT NULL,
359 `activity` varchar(255) DEFAULT NULL,
360 `require_audit` tinyint(1) DEFAULT '1',
361 `pending_action` varchar(255) DEFAULT NULL,
362 `action_taken` varchar(255) DEFAULT NULL,
363 `status` varchar(255) DEFAULT NULL,
364 `narrative` longtext,
365 `table_action` longtext,
366 `table_args` longtext,
367 `action_user` int(11) DEFAULT NULL,
368 `action_taken_time` datetime DEFAULT NULL,
372 ) ENGINE=InnoDB AUTO_INCREMENT=1 ;
375 #IfNotTable onsite_signatures
376 CREATE TABLE `onsite_signatures` (
377 `id` bigint(20) NOT NULL AUTO_INCREMENT,
378 `status` varchar(128) NOT NULL DEFAULT 'waiting',
379 `type` varchar(128) NOT NULL,
380 `created` int(11) NOT NULL,
381 `lastmod` datetime NOT NULL,
382 `pid` bigint(20) DEFAULT NULL,
383 `encounter` int(11) DEFAULT NULL,
384 `user` varchar(255) DEFAULT NULL,
385 `activity` tinyint(4) NOT NULL DEFAULT '0',
386 `authorized` tinyint(4) DEFAULT NULL,
387 `signator` varchar(255) NOT NULL,
390 `sig_hash` varchar(128) NOT NULL,
391 `ip` varchar(46) NOT NULL,
393 UNIQUE KEY `pid` (`pid`,`user`),
394 KEY `encounter` (`encounter`)
395 ) ENGINE=InnoDB AUTO_INCREMENT=1 ;
398 #IfNotRow categories name Onsite Portal
399 INSERT INTO categories select (select MAX(id) from categories) + 1, 'Onsite Portal', '', 1, rght, rght + 5 from categories where name = 'Categories';
400 INSERT INTO categories select (select MAX(id) from categories) + 1, 'Patient', '', (select id from categories where name = 'Onsite Portal'), rght + 1, rght + 2 from categories where name = 'Categories';
401 INSERT INTO categories select (select MAX(id) from categories) + 1, 'Reviewed', '', (select id from categories where name = 'Onsite Portal'), rght + 3, rght + 4 from categories where name = 'Categories';
402 UPDATE categories SET rght = rght + 6 WHERE name = 'Categories';
403 UPDATE categories_seq SET id = (select MAX(id) from categories);
406 #IfNotRow2D list_options list_id apptstat option_id ^
407 INSERT INTO list_options ( `list_id`, `option_id`, `title`, `seq`, `is_default`, `notes` ) VALUES ('apptstat','^','^ Pending',70,0,'FEFDCF|0');
410 #IfMissingColumn registry aco_spec
411 ALTER TABLE `registry` ADD `aco_spec` varchar(63) NOT NULL default 'encounters|notes';
412 UPDATE `registry` SET `aco_spec` = 'patients|appt' WHERE directory = 'newpatient';
413 UPDATE `registry` SET `aco_spec` = 'patients|appt' WHERE directory = 'newGroupEncounter';
414 UPDATE `registry` SET `aco_spec` = 'encounters|coding' WHERE directory = 'fee_sheet';
415 UPDATE `registry` SET `aco_spec` = 'encounters|coding' WHERE directory = 'misc_billing_options';
416 UPDATE `registry` SET `aco_spec` = 'patients|lab' WHERE directory = 'procedure_order';
419 #IfNotColumnType lbf_data field_value longtext
420 ALTER TABLE `lbf_data` CHANGE `field_value` `field_value` longtext;
423 #IfMissingColumn issue_types aco_spec
424 ALTER TABLE `issue_types` ADD `aco_spec` varchar(63) NOT NULL default 'patients|med';
427 #IfMissingColumn categories aco_spec
428 ALTER TABLE `categories` ADD `aco_spec` varchar(63) NOT NULL default 'patients|docs';
431 #IfNotRow background_services name ccdaservice
432 INSERT INTO `background_services` (`name`, `title`, `execute_interval`, `function`, `require_once`, `sort_order`) VALUES ('ccdaservice', 'C-CDA Node Service', 1, 'runCheck', '/ccdaservice/ssmanager.php', 95);
433 ALTER TABLE `background_services` CHANGE `running` `running` TINYINT(1) NOT NULL DEFAULT '-1' COMMENT 'True indicates managed service is busy. Skip this interval.';
436 #IfNotColumnType onsite_mail owner varchar(128)
437 ALTER TABLE `onsite_mail` CHANGE `owner` `owner` varchar(128) DEFAULT NULL;
440 #IfNotColumnType openemr_postcalendar_events pc_facility int(11)
441 ALTER TABLE `openemr_postcalendar_events` CHANGE `pc_facility` `pc_facility` int(11) NOT NULL DEFAULT '0' COMMENT 'facility id for this event';
444 #IfMissingColumn form_misc_billing_options onset_date
445 ALTER TABLE `form_misc_billing_options` ADD `onset_date` date default NULL;
446 UPDATE `list_options` SET `option_id` = 'DK', `title` = 'Ordering Provider' WHERE `list_id` = 'provider_qualifier_code' AND `option_id` = 'dk';
447 UPDATE `list_options` SET `option_id` = 'DN', `title` = 'Referring Provider', `is_default` = '1' WHERE `list_id` = 'provider_qualifier_code' AND `option_id` = 'dn';
450 #IfNotRow2D list_options list_id provider_qualifier_code option_id DQ
451 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `is_default`) VALUES ('provider_qualifier_code', 'DQ', 'Supervising Provider', '30', '0');
454 #IfMissingColumn users main_menu_role
455 ALTER TABLE `users` ADD `main_menu_role` VARCHAR(50) NOT NULL DEFAULT 'standard';
458 #IfMissingColumn openemr_postcalendar_categories aco_spec
459 ALTER TABLE `openemr_postcalendar_categories` ADD COLUMN `aco_spec` VARCHAR(63) NOT NULL DEFAULT 'encounters|notes';
461 #IfNotRow2D list_options list_id lists option_id apps
462 INSERT INTO list_options (list_id,option_id,title) VALUES ('lists','apps','Apps');
463 INSERT INTO list_options (list_id,option_id,title,seq,is_default,activity) VALUES ('apps','*OpenEMR','main/main_screen.php',10,1,0);
464 INSERT INTO list_options (list_id,option_id,title,seq,is_default,activity) VALUES ('apps','Calendar','main/calendar/index.php',20,0,0);
467 #IfNotColumnType list_options list_id varchar(100)
468 ALTER TABLE `list_options` CHANGE `list_id` `list_id` VARCHAR(100) NOT NULL DEFAULT '';
471 #IfNotColumnType list_options option_id varchar(100)
472 ALTER TABLE `list_options` CHANGE `option_id` `option_id` VARCHAR(100) NOT NULL DEFAULT '';
475 #IfNotColumnType layout_options list_id varchar(100)
476 ALTER TABLE `layout_options` CHANGE `list_id` `list_id` VARCHAR(100) NOT NULL DEFAULT '';
479 #IfNotColumnType layout_options list_backup_id varchar(100)
480 ALTER TABLE `layout_options` CHANGE `list_backup_id` `list_backup_id` VARCHAR(100) NOT NULL DEFAULT '';
483 #IfNotTable patient_birthday_alert
484 CREATE TABLE `patient_birthday_alert` (
485 `pid` bigint(20) NOT NULL DEFAULT 0,
486 `user_id` bigint(20) NOT NULL DEFAULT 0,
487 `turned_off_on` date NOT NULL,
488 PRIMARY KEY (`pid`,`user_id`)
492 #IfNotRow4D supported_external_dataloads load_type ICD10 load_source CMS load_release_date 2017-10-01 load_filename 2018-ICD-10-PCS-Order-File.zip
493 INSERT INTO `supported_external_dataloads` (`load_type`, `load_source`, `load_release_date`, `load_filename`, `load_checksum`) VALUES
494 ('ICD10', 'CMS', '2017-10-01', '2018-ICD-10-PCS-Order-File.zip', '264b342310236f2b3927062d2c72cfe3');
497 #IfNotRow4D supported_external_dataloads load_type ICD10 load_source CMS load_release_date 2017-10-01 load_filename 2018-ICD-10-CM-General-Equivalence-Mappings.zip
498 INSERT INTO `supported_external_dataloads` (`load_type`, `load_source`, `load_release_date`, `load_filename`, `load_checksum`) VALUES
499 ('ICD10', 'CMS', '2017-10-01', '2018-ICD-10-CM-General-Equivalence-Mappings.zip', '787a025fdcf6e1da1a85be779004f670');
502 UPDATE `supported_external_dataloads` SET `load_filename`='2018-ICD-10-Code-Descriptions.zip' WHERE `load_filename`='2018-ICD-10-Code-Dedcriptions.zip' AND `load_release_date`='2017-10-01';
503 #IfNotRow4D supported_external_dataloads load_type ICD10 load_source CMS load_release_date 2017-10-01 load_filename 2018-ICD-10-Code-Descriptions.zip
504 INSERT INTO `supported_external_dataloads` (`load_type`, `load_source`, `load_release_date`, `load_filename`, `load_checksum`) VALUES
505 ('ICD10', 'CMS', '2017-10-01', '2018-ICD-10-Code-Descriptions.zip', '6f9c77440132e30f565222ca9bb6599c');
508 #IfNotRow4D supported_external_dataloads load_type ICD10 load_source CMS load_release_date 2017-10-01 load_filename 2018-ICD-10-PCS-General-Equivalence-Mappings.zip
509 INSERT INTO `supported_external_dataloads` (`load_type`, `load_source`, `load_release_date`, `load_filename`, `load_checksum`) VALUES
510 ('ICD10', 'CMS', '2017-10-01', '2018-ICD-10-PCS-General-Equivalence-Mappings.zip', 'bb73c80e272da28712887d7979b1cebf');
513 #IfColumn x12_partners x12_version
514 ALTER TABLE `x12_partners` DROP COLUMN `x12_version`;
517 #IfNotRow2D list_options list_id page_validation option_id add_edit_event#theform_prov
518 INSERT INTO `list_options` (`list_id`, `option_id`, `title`, `seq`, `notes`, `activity`) VALUES
519 ('page_validation', 'add_edit_event#theform_prov', '/interface/main/calendar/add_edit_event.php?prov=true', 170, '{}', 1);
522 #IfMissingColumn claims submitted_claim
523 ALTER TABLE `claims` ADD COLUMN `submitted_claim` TEXT COMMENT 'This claims form claim data';
526 #IfMissingColumn billing revenue_code
527 ALTER TABLE `billing` ADD COLUMN `revenue_code` varchar(6) NOT NULL DEFAULT "" COMMENT 'Item revenue code';
530 #IfMissingColumn codes revenue_code
531 ALTER TABLE `codes` ADD COLUMN `revenue_code` varchar(6) NOT NULL DEFAULT "" COMMENT 'Item revenue code';
534 #IfMissingColumn users weno_prov_id
535 ALTER TABLE `users` ADD `weno_prov_id` VARCHAR(15) DEFAULT NULL;
538 #IfMissingColumn prescriptions ntx
539 ALTER TABLE `prescriptions` ADD `ntx` INT(2) DEFAULT NULL;
542 #IfMissingColumn prescriptions rtx
543 ALTER TABLE `prescriptions` ADD `rtx` INT(2) DEFAULT NULL;
546 #IfMissingColumn prescriptions txDate
547 ALTER TABLE `prescriptions` ADD `txDate` DATE NOT NULL;
550 #IfMissingColumn pharmacies ncpdp
551 ALTER TABLE `pharmacies` ADD `ncpdp` INT(12) DEFAULT NULL;
554 #IfMissingColumn pharmacies npi
555 ALTER TABLE `pharmacies` ADD `npi` INT(12) DEFAULT NULL;
558 #IfNotRow2Dx2 list_options list_id state option_id PR title Puerto Rico
559 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('state','PR','Puerto Rico',39,0);
562 #IfNotTable erx_drug_paid
563 CREATE TABLE `erx_drug_paid` (
564 `drugid` int(11) NOT NULL AUTO_INCREMENT,
565 `drug_label_name` varchar(45) NOT NULL,
566 `ahfs_descr` varchar(45) NOT NULL,
567 `ndc` bigint(12) NOT NULL,
568 `price_per_unit` decimal(5,2) NOT NULL,
569 `avg_price` decimal(6,2) NOT NULL,
570 `avg_price_paid` int(6) NOT NULL,
571 `avg_savings` decimal(6,2) NOT NULL,
572 `avg_percent` decimal(6,2) NOT NULL,
573 PRIMARY KEY (`drugid`)
577 #IfNotTable erx_rx_log
578 CREATE TABLE `erx_rx_log` (
579 `id` int(20) NOT NULL AUTO_INCREMENT,
580 `prescription_id` int(6) NOT NULL,
581 `date` varchar(25) NOT NULL,
582 `time` varchar(15) NOT NULL,
583 `code` int(6) NOT NULL,
585 `message_id` varchar(100) DEFAULT NULL,
586 `read` int(1) DEFAULT NULL,
591 #IfNotTable erx_narcotics
592 CREATE TABLE `erx_narcotics` (
593 `id` int(11) NOT NULL AUTO_INCREMENT,
594 `drug` varchar(255) NOT NULL,
595 `dea_number` varchar(5) NOT NULL,
596 `csa_sch` varchar(2) NOT NULL,
597 `narc` varchar(2) NOT NULL,
598 `other_names` varchar(255) NOT NULL,
603 UPDATE `globals` SET `gl_value`='style_red.css' WHERE `gl_name`='css_header' AND `gl_value`='style_flat_red.css';
604 UPDATE `globals` SET `gl_value`='style_manila.css' WHERE `gl_name`='css_header' AND `gl_value`='style_tan.css';
605 UPDATE `globals` SET `gl_value`='style_light.css' WHERE `gl_name`='css_header' AND (`gl_value`='style_babyblu.css'
606 OR `gl_value`='style_metal.css'
607 OR `gl_value`='style_oemr.css'
608 OR `gl_value`='style_purple.css'
609 OR `gl_value`='style_radiant.css'
610 OR `gl_value`='style_sky_blue.css');
612 UPDATE `user_settings` SET `setting_value`='style_red.css' WHERE `setting_label`='global:css_header' AND `setting_value`='style_flat_red.css';
613 UPDATE `user_settings` SET `setting_value`='style_manila.css' WHERE `setting_label`='global:css_header' AND `setting_value`='style_tan.css';
614 UPDATE `user_settings` SET `setting_value`='style_light.css' WHERE `setting_label`='global:css_header' AND (`setting_value`='style_babyblu.css'
615 OR `setting_value`='style_metal.css'
616 OR `setting_value`='style_oemr.css'
617 OR `setting_value`='style_purple.css'
618 OR `setting_value`='style_radiant.css'
619 OR `setting_value`='style_sky_blue.css');
621 #IfNotColumnType facility country_code varchar(30)
622 ALTER TABLE `facility` CHANGE `country_code` `country_code` varchar(30) NOT NULL default '';
625 #IfNotColumnType layout_options group_name varchar(255)
626 ALTER TABLE `layout_options` CHANGE `group_name` `group_name` varchar(255) NOT NULL default '';
629 #IfMissingColumn forms issue_id
630 ALTER TABLE `forms` ADD COLUMN `issue_id` bigint(20) NOT NULL default 0 COMMENT 'references lists.id to identify a case';
633 #IfMissingColumn forms provider_id
634 ALTER TABLE `forms` ADD COLUMN `provider_id` bigint(20) NOT NULL default 0 COMMENT 'references users.id to identify a provider';
637 #IfNotTable layout_group_properties
638 CREATE TABLE `layout_group_properties` (
639 grp_form_id varchar(31) not null,
640 grp_group_id varchar(31) not null default '' comment 'empty when representing the whole form',
641 grp_title varchar(63) not null default '' comment 'descriptive name of the form or group',
642 grp_subtitle varchar(63) not null default '' comment 'for display under the title',
643 grp_mapping varchar(31) not null default '' comment 'the form category',
644 grp_seq int(11) not null default 0 comment 'optional order within mapping',
645 grp_activity tinyint(1) not null default 1,
646 grp_repeats int(11) not null default 0,
647 grp_columns int(11) not null default 0,
648 grp_size int(11) not null default 0,
649 grp_issue_type varchar(75) not null default '',
650 grp_aco_spec varchar(63) not null default '',
651 grp_services varchar(4095) not null default '',
652 grp_products varchar(4095) not null default '',
653 grp_diags varchar(4095) not null default '',
654 PRIMARY KEY (grp_form_id, grp_group_id)
656 ALTER TABLE layout_options ADD COLUMN group_id VARCHAR(31) NOT NULL default '' AFTER group_name;
657 #ConvertLayoutProperties
658 ALTER TABLE layout_options DROP COLUMN group_name;
659 DELETE FROM list_options WHERE list_id = 'lbfnames';
660 DELETE FROM list_options WHERE list_id = 'lists' AND option_id = 'lbfnames';
661 DELETE FROM list_options WHERE list_id = 'transactions';
662 DELETE FROM list_options WHERE list_id = 'lists' AND option_id = 'transactions';