1 #IfNotRow categories name Advance Directive
2 INSERT INTO categories select (select MAX(id) from categories) + 1, 'Advance Directive', '', 1, rght, rght + 7 from categories where name = 'Categories';
3 INSERT INTO categories select (select MAX(id) from categories) + 1, 'Do Not Resuscitate Order', '', (select id from categories where name = 'Advance Directive'), rght + 1, rght + 2 from categories where name = 'Categories';
4 INSERT INTO categories select (select MAX(id) from categories) + 1, 'Durable Power of Attorney', '', (select id from categories where name = 'Advance Directive'), rght + 3, rght + 4 from categories where name = 'Categories';
5 INSERT INTO categories select (select MAX(id) from categories) + 1, 'Living Will', '', (select id from categories where name = 'Advance Directive'), rght + 5, rght + 6 from categories where name = 'Categories';
6 UPDATE categories SET rght = rght + 8 WHERE name = 'Categories';
7 UPDATE categories_seq SET id = (select MAX(id) from categories);
10 #IfMissingColumn patient_data completed_ad
11 ALTER TABLE patient_data
12 ADD completed_ad VARCHAR(3) NOT NULL DEFAULT 'NO',
13 ADD ad_reviewed date DEFAULT NULL;
16 #IfNotRow2D list_options list_id lists option_id apptstat
17 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists' ,'apptstat','Appointment Statuses', 1,0);
18 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','-' ,'- None' , 5,0);
19 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','*' ,'* Reminder done' ,10,0);
20 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','+' ,'+ Chart pulled' ,15,0);
21 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','x' ,'x Canceled' ,20,0);
22 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','?' ,'? No show' ,25,0);
23 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','@' ,'@ Arrived' ,30,0);
24 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','~' ,'~ Arrived late' ,35,0);
25 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','!' ,'! Left w/o visit' ,40,0);
26 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','#' ,'# Ins/fin issue' ,45,0);
27 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','<' ,'< In exam room' ,50,0);
28 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','>' ,'> Checked out' ,55,0);
29 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','$' ,'$ Coding done' ,60,0);
30 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('apptstat','%' ,'% Canceled < 24h' ,65,0);
31 ALTER TABLE openemr_postcalendar_events CHANGE pc_apptstatus pc_apptstatus varchar(15) NOT NULL DEFAULT '-';
34 #IfNotRow2D layout_options form_id DEM field_id referral_source
35 INSERT INTO `layout_options` VALUES ('DEM', 'referral_source', '5Stats', 'Referral Source',10, 26, 1, 0, 0, 'refsource', 1, 1, '', '', 'How did they hear about us');
38 #IfMissingColumn list_options notes
39 ALTER TABLE list_options
40 CHANGE mapping mapping varchar(31) NOT NULL DEFAULT '',
41 ADD notes varchar(255) NOT NULL DEFAULT '';
44 #IfNotRow2D list_options list_id lists option_id warehouse
45 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','warehouse','Warehouses',21,0);
46 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('warehouse','onsite','On Site', 5,0);
49 #IfNotRow2D list_options list_id lists option_id abook_type
50 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','abook_type' ,'Address Book Types' , 1,0);
51 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('abook_type','ord_img','Imaging Service' , 5,0);
52 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('abook_type','ord_imm','Immunization Service',10,0);
53 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('abook_type','ord_lab','Lab Service' ,15,0);
54 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('abook_type','spe' ,'Specialist' ,20,0);
55 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('abook_type','oth' ,'Other' ,95,0);
58 #IfMissingColumn users abook_type
60 ADD abook_type varchar(31) NOT NULL DEFAULT '';
63 #IfNotRow2D list_options list_id lists option_id proc_type
64 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','proc_type','Procedure Types', 1,0);
65 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_type','img','Imaging' ,10,0);
66 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_type','imm','Immunization' ,20,0);
67 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_type','lab','Lab' ,30,0);
68 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_type','rec','Recommendation',80,0);
69 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_type','oth','Other' ,90,0);
72 #IfNotRow2D list_options list_id lists option_id proc_body_site
73 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','proc_body_site','Procedure Body Sites', 1,0);
74 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_body_site','arm' ,'Arm' ,10,0);
75 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_body_site','buttock','Buttock',20,0);
76 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_body_site','oth' ,'Other' ,90,0);
79 #IfNotRow2D list_options list_id lists option_id proc_specimen
80 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','proc_specimen','Procedure Specimen Types', 1,0);
81 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_specimen','blood' ,'Blood' ,10,0);
82 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_specimen','saliva','Saliva',20,0);
83 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_specimen','urine' ,'Urine' ,30,0);
84 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_specimen','oth' ,'Other' ,90,0);
87 #IfNotRow2D list_options list_id lists option_id proc_route
88 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','proc_route','Procedure Routes', 1,0);
89 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_route','inj' ,'Injection',10,0);
90 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_route','oral','Oral' ,20,0);
91 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_route','oth' ,'Other' ,90,0);
94 #IfNotRow2D list_options list_id lists option_id proc_lat
95 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','proc_lat','Procedure Lateralities', 1,0);
96 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_lat','left' ,'Left' ,10,0);
97 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_lat','right','Right' ,20,0);
98 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_lat','bilat','Bilateral',30,0);
101 #IfNotRow2D list_options list_id lists option_id proc_unit
102 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('lists','proc_unit','Procedure Units', 1);
103 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','bool' ,'Boolean' , 5);
104 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','cu_mm' ,'CU.MM' , 10);
105 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','fl' ,'FL' , 20);
106 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','g_dl' ,'G/DL' , 30);
107 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','gm_dl' ,'GM/DL' , 40);
108 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','hmol_l' ,'HMOL/L' , 50);
109 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','iu_l' ,'IU/L' , 60);
110 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','mg_dl' ,'MG/DL' , 70);
111 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','mil_cu_mm' ,'Mil/CU.MM' , 80);
112 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','percent' ,'Percent' , 90);
113 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','percentile' ,'Percentile' ,100);
114 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','pg' ,'PG' ,110);
115 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','ratio' ,'Ratio' ,120);
116 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','thous_cu_mm','Thous/CU.MM',130);
117 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','units' ,'Units' ,140);
118 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','units_l' ,'Units/L' ,150);
119 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','days' ,'Days' ,600);
120 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','weeks' ,'Weeks' ,610);
121 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','months' ,'Months' ,620);
122 INSERT INTO list_options ( list_id, option_id, title, seq ) VALUES ('proc_unit','oth' ,'Other' ,990);
125 #IfNotRow2D list_options list_id lists option_id ord_priority
126 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','ord_priority','Order Priorities', 1,0);
127 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ord_priority','high' ,'High' ,10,0);
128 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ord_priority','normal','Normal',20,0);
131 #IfNotRow2D list_options list_id lists option_id ord_status
132 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','ord_status','Order Statuses', 1,0);
133 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ord_status','pending' ,'Pending' ,10,0);
134 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ord_status','routed' ,'Routed' ,20,0);
135 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ord_status','complete','Complete',30,0);
136 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('ord_status','canceled','Canceled',40,0);
139 #IfNotRow2D list_options list_id lists option_id proc_rep_status
140 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','proc_rep_status','Procedure Report Statuses', 1,0);
141 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_rep_status','final' ,'Final' ,10,0);
142 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_rep_status','review','Reviewed' ,20,0);
143 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_rep_status','prelim','Preliminary',30,0);
144 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_rep_status','cancel','Canceled' ,40,0);
145 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_rep_status','error' ,'Error' ,50,0);
148 #IfNotRow2D list_options list_id lists option_id proc_res_abnormal
149 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','proc_res_abnormal','Procedure Result Abnormal', 1,0);
150 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_res_abnormal','no' ,'No' ,10,0);
151 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_res_abnormal','yes' ,'Yes' ,20,0);
152 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_res_abnormal','high','High',30,0);
153 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_res_abnormal','low' ,'Low' ,40,0);
156 #IfNotRow2D list_options list_id lists option_id proc_res_status
157 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','proc_res_status','Procedure Result Statuses', 1,0);
158 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_res_status','final' ,'Final' ,10,0);
159 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_res_status','prelim','Preliminary',20,0);
160 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_res_status','cancel','Canceled' ,30,0);
161 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_res_status','error' ,'Error' ,40,0);
164 #IfNotRow2D list_options list_id lists option_id proc_res_bool
165 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('lists','proc_res_bool','Procedure Boolean Results', 1,0);
166 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_res_bool','neg' ,'Negative',10,0);
167 INSERT INTO list_options ( list_id, option_id, title, seq, is_default ) VALUES ('proc_res_bool','pos' ,'Positive',20,0);
170 #IfNotTable procedure_type
171 CREATE TABLE `procedure_type` (
172 `procedure_type_id` bigint(20) NOT NULL AUTO_INCREMENT,
173 `parent` bigint(20) NOT NULL DEFAULT 0 COMMENT 'references procedure_type.procedure_type_id',
174 `name` varchar(63) NOT NULL DEFAULT '' COMMENT 'name for this category, procedure or result type',
175 `lab_id` bigint(20) NOT NULL DEFAULT 0 COMMENT 'references users.id, 0 means default to parent',
176 `procedure_code` varchar(31) NOT NULL DEFAULT '' COMMENT 'code identifying this procedure',
177 `procedure_type` varchar(31) NOT NULL DEFAULT '' COMMENT 'lab, imaging, ...',
178 `body_site` varchar(31) NOT NULL DEFAULT '' COMMENT 'where to do injection, e.g. arm, buttok',
179 `specimen` varchar(31) NOT NULL DEFAULT '' COMMENT 'blood, urine, saliva, etc.',
180 `route_admin` varchar(31) NOT NULL DEFAULT '' COMMENT 'oral, injection',
181 `laterality` varchar(31) NOT NULL DEFAULT '' COMMENT 'left, right, ...',
182 `description` varchar(255) NOT NULL DEFAULT '' COMMENT 'descriptive text for procedure_code',
183 `standard_code` varchar(255) NOT NULL DEFAULT '' COMMENT 'industry standard code type and code (e.g. CPT4:12345)',
184 `related_code` varchar(255) NOT NULL DEFAULT '' COMMENT 'suggested code(s) for followup services if result is abnormal',
185 `is_discrete` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'can this be referenced by lab_test_result?',
186 `is_orderable` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'can this test type be ordered?',
187 `units` varchar(31) NOT NULL DEFAULT '' COMMENT 'default for procedure_result.units',
188 `range` varchar(255) NOT NULL DEFAULT '' COMMENT 'default for procedure_result.range',
189 PRIMARY KEY (`procedure_type_id`),
194 #IfNotTable procedure_order
195 CREATE TABLE `procedure_order` (
196 `procedure_order_id` bigint(20) NOT NULL AUTO_INCREMENT,
197 `procedure_type_id` bigint(20) NOT NULL COMMENT 'references procedure_type.procedure_type_id',
198 `provider_id` bigint(20) NOT NULL DEFAULT 0 COMMENT 'references users.id',
199 `patient_id` bigint(20) NOT NULL COMMENT 'references patient_data.pid',
200 `encounter_id` bigint(20) NOT NULL DEFAULT 0 COMMENT 'references form_encounter.encounter',
201 `date_collected` datetime DEFAULT NULL COMMENT 'time specimen collected',
202 `date_ordered` date DEFAULT NULL,
203 `order_priority` varchar(31) NOT NULL DEFAULT '',
204 `order_status` varchar(31) NOT NULL DEFAULT '' COMMENT 'pending,routed,complete,canceled',
205 `patient_instructions` text NOT NULL DEFAULT '',
206 `activity` tinyint(1) NOT NULL DEFAULT 1 COMMENT '0 if deleted',
207 PRIMARY KEY (`procedure_order_id`),
208 KEY datepid (date_ordered, patient_id)
212 #IfNotTable procedure_report
213 CREATE TABLE `procedure_report` (
214 `procedure_report_id` bigint(20) NOT NULL AUTO_INCREMENT,
215 `procedure_order_id` bigint(20) DEFAULT NULL COMMENT 'references procedure_order.procedure_order_id',
216 `date_collected` datetime DEFAULT NULL,
217 `date_report` date DEFAULT NULL,
218 `source` bigint(20) NOT NULL DEFAULT 0 COMMENT 'references users.id, who entered this data',
219 `specimen_num` varchar(63) NOT NULL DEFAULT '',
220 `report_status` varchar(31) NOT NULL DEFAULT '' COMMENT 'received,complete,error',
221 PRIMARY KEY (`procedure_report_id`),
222 KEY procedure_order_id (procedure_order_id)
226 #IfNotTable procedure_result
227 CREATE TABLE `procedure_result` (
228 `procedure_result_id` bigint(20) NOT NULL AUTO_INCREMENT,
229 `procedure_report_id` bigint(20) NOT NULL COMMENT 'references procedure_report.procedure_report_id',
230 `procedure_type_id` bigint(20) NOT NULL COMMENT 'references procedure_type.procedure_type_id',
231 `date` datetime DEFAULT NULL COMMENT 'lab-provided date specific to this result',
232 `facility` varchar(255) NOT NULL DEFAULT '' COMMENT 'lab-provided testing facility ID',
233 `units` varchar(31) NOT NULL DEFAULT '',
234 `result` varchar(255) NOT NULL DEFAULT '',
235 `range` varchar(255) NOT NULL DEFAULT '',
236 `abnormal` varchar(31) NOT NULL DEFAULT '' COMMENT 'no,yes,high,low',
237 `comments` text NOT NULL DEFAULT '' COMMENT 'comments from the lab',
238 `document_id` bigint(20) NOT NULL DEFAULT 0 COMMENT 'references documents.id if this result is a document',
239 `result_status` varchar(31) NOT NULL DEFAULT '' COMMENT 'preliminary, cannot be done, final, corrected, incompete...etc.',
240 PRIMARY KEY (`procedure_result_id`),
241 KEY procedure_report_id (procedure_report_id)
245 #IfMissingColumn history_data recreational_drugs
246 ALTER TABLE history_data ADD recreational_drugs longtext;
249 update layout_options set seq = 1, data_type = 28, titlecols = 1, datacols = 3 where form_id = 'HIS' and field_id = 'coffee';
250 update layout_options set seq = 6, data_type = 28, titlecols = 1, datacols = 3 where form_id = 'HIS' and field_id = 'exercise_patterns';
251 update layout_options set seq = 2, data_type = 28, titlecols = 1, datacols = 3 where form_id = 'HIS' and field_id = 'tobacco';
252 update layout_options set seq = 3, data_type = 28, titlecols = 1, datacols = 3 where form_id = 'HIS' and field_id = 'alcohol';
253 update layout_options set seq = 5, data_type = 28, titlecols = 1, datacols = 3 where form_id = 'HIS' and field_id = 'counseling';
254 update layout_options set seq = 7, data_type = 28, titlecols = 1, datacols = 3 where form_id = 'HIS' and field_id = 'hazardous_activities';
255 update layout_options set seq = 8, titlecols = 1, datacols = 3 where form_id = 'HIS' and field_id = 'sleep_patterns';
256 update layout_options set seq = 9, titlecols = 1, datacols = 3 where form_id = 'HIS' and field_id = 'seatbelt_use';
258 #IfNotRow2D layout_options form_id HIS field_id recreational_drugs
259 INSERT INTO layout_options (form_id, field_id, group_name, title, seq, data_type, uor, fld_length, max_length, list_id, titlecols, datacols, default_value, edit_options, description) VALUES ('HIS','recreational_drugs','4Lifestyle','Recreational Drugs',4,28,1,20,255,'',1,3,'','' ,'Recreational drugs use');
263 #IfMissingColumn users pwd_expiration_date
264 ALTER TABLE users ADD pwd_expiration_date date DEFAULT NULL;
267 #IfMissingColumn users pwd_history1
268 ALTER TABLE users ADD pwd_history1 longtext DEFAULT NULL;
271 #IfMissingColumn users pwd_history2
272 ALTER TABLE users ADD pwd_history2 longtext DEFAULT NULL;