1 ALTER TABLE form_encounter
2 ADD billing_note text NOT NULL DEFAULT '';
5 ADD organization varchar(255) NOT NULL DEFAULT '',
6 ADD valedictory varchar(255) NOT NULL DEFAULT '';
8 ALTER TABLE openemr_postcalendar_events
9 ADD pc_facility smallint(6) NOT NULL default '0' COMMENT 'facility id for this event';
12 ADD encounter bigint(20) NOT NULL DEFAULT 0,
15 ALTER TABLE patient_data
16 ADD `usertext1` varchar(255) NOT NULL DEFAULT '',
17 ADD `usertext2` varchar(255) NOT NULL DEFAULT '',
18 ADD `userlist1` varchar(255) NOT NULL DEFAULT '',
19 ADD `userlist2` varchar(255) NOT NULL DEFAULT '',
20 ADD `pricelevel` varchar(255) NOT NULL DEFAULT 'standard';
22 CREATE TABLE list_options (
23 list_id varchar(31) NOT NULL,
24 option_id varchar(31) NOT NULL,
25 title varchar(255) NOT NULL DEFAULT '',
26 seq int(11) NOT NULL DEFAULT 0,
27 is_default tinyint(1) NOT NULL DEFAULT 0,
28 option_value float NOT NULL DEFAULT 0,
29 PRIMARY KEY (list_id, option_id)
32 INSERT INTO list_options VALUES ('yesno' ,'NO' ,'NO' ,1,0,0);
33 INSERT INTO list_options VALUES ('yesno' ,'YES','YES',2,0,0);
35 INSERT INTO list_options VALUES ('titles' ,'Mr.' ,'Mr.' ,1,0,0);
36 INSERT INTO list_options VALUES ('titles' ,'Mrs.' ,'Mrs.' ,2,0,0);
37 INSERT INTO list_options VALUES ('titles' ,'Ms.' ,'Ms.' ,3,0,0);
38 INSERT INTO list_options VALUES ('titles' ,'Dr.' ,'Dr.' ,4,0,0);
40 INSERT INTO list_options VALUES ('sex' ,'Female','Female' ,1,0,0);
41 INSERT INTO list_options VALUES ('sex' ,'Male' ,'Male' ,2,0,0);
43 INSERT INTO list_options VALUES ('marital','married' ,'Married' ,1,0,0);
44 INSERT INTO list_options VALUES ('marital','single' ,'Single' ,2,0,0);
45 INSERT INTO list_options VALUES ('marital','divorced' ,'Divorced' ,3,0,0);
46 INSERT INTO list_options VALUES ('marital','widowed' ,'Widowed' ,4,0,0);
47 INSERT INTO list_options VALUES ('marital','separated' ,'Separated' ,5,0,0);
48 INSERT INTO list_options VALUES ('marital','domestic partner','Domestic Partner',6,0,0);
50 INSERT INTO list_options VALUES ('language','English','English',1,1,0);
51 INSERT INTO list_options VALUES ('language','Spanish','Spanish',2,0,0);
53 INSERT INTO list_options VALUES ('ethrace','Caucasian','Caucasian',1,0,0);
54 INSERT INTO list_options VALUES ('ethrace','Asian' ,'Asian' ,2,0,0);
55 INSERT INTO list_options VALUES ('ethrace','Black' ,'Black' ,3,0,0);
56 INSERT INTO list_options VALUES ('ethrace','Hispanic' ,'Hispanic' ,4,0,0);
58 INSERT INTO list_options VALUES ('userlist1','sample','Sample',1,0,0);
60 INSERT INTO list_options VALUES ('userlist2','sample','Sample',1,0,0);
62 INSERT INTO list_options VALUES ('pricelevel','standard','Standard',1,1,0);
64 CREATE TABLE layout_options (
65 form_id varchar(31) NOT NULL,
66 field_id varchar(31) NOT NULL,
67 group_name varchar(31) NOT NULL DEFAULT '',
68 title varchar(63) NOT NULL DEFAULT '',
69 seq int(11) NOT NULL DEFAULT 0,
70 data_type tinyint(3) NOT NULL DEFAULT 0,
71 uor tinyint(1) NOT NULL DEFAULT 1,
72 fld_length int(11) NOT NULL DEFAULT 15,
73 max_length int(11) NOT NULL DEFAULT 0,
74 list_id varchar(31) NOT NULL DEFAULT '',
75 titlecols tinyint(3) NOT NULL DEFAULT 1,
76 datacols tinyint(3) NOT NULL DEFAULT 1,
77 default_value varchar(255) NOT NULL DEFAULT '',
78 edit_options varchar(36) NOT NULL DEFAULT '',
79 description varchar(255) NOT NULL DEFAULT '',
80 PRIMARY KEY (form_id, field_id, seq)
83 INSERT INTO layout_options VALUES ('DEM','title' ,'1Who','Name' , 1, 1,1, 0, 0,'titles' ,1,1,'','' ,'Title');
84 INSERT INTO layout_options VALUES ('DEM','fname' ,'1Who','' , 2, 2,2,10,63,'' ,0,0,'','C','First Name');
85 INSERT INTO layout_options VALUES ('DEM','mname' ,'1Who','' , 3, 2,1, 2,63,'' ,0,0,'','C','Middle Name');
86 INSERT INTO layout_options VALUES ('DEM','lname' ,'1Who','' , 4, 2,2,10,63,'' ,0,0,'','C','Last Name');
87 INSERT INTO layout_options VALUES ('DEM','pubpid' ,'1Who','External ID' , 5, 2,1,10,15,'' ,1,1,'','' ,'External identifier');
88 INSERT INTO layout_options VALUES ('DEM','DOB' ,'1Who','DOB' , 6, 2,2,10,10,'' ,1,1,'','D','Date of Birth');
89 INSERT INTO layout_options VALUES ('DEM','sex' ,'1Who','Sex' , 7, 1,2, 0, 0,'sex' ,1,1,'','' ,'Sex');
90 INSERT INTO layout_options VALUES ('DEM','ss' ,'1Who','S.S.' , 8, 2,1,11,11,'' ,1,1,'','' ,'Social Security Number');
91 INSERT INTO layout_options VALUES ('DEM','drivers_license','1Who','License/ID' , 9, 2,1,15,63,'' ,1,1,'','' ,'Drivers License or State ID');
92 INSERT INTO layout_options VALUES ('DEM','status' ,'1Who','Marital Status',10, 1,1, 0, 0,'marital',1,3,'','' ,'Marital Status');
93 INSERT INTO layout_options VALUES ('DEM','genericname1' ,'1Who','User Defined' ,11, 2,1,15,63,'' ,1,3,'','' ,'User Defined Field');
94 INSERT INTO layout_options VALUES ('DEM','genericval1' ,'1Who','' ,12, 2,1,15,63,'' ,0,0,'','' ,'User Defined Field');
95 INSERT INTO layout_options VALUES ('DEM','genericname2' ,'1Who','' ,13, 2,1,15,63,'' ,0,0,'','' ,'User Defined Field');
96 INSERT INTO layout_options VALUES ('DEM','genericval2' ,'1Who','' ,14, 2,1,15,63,'' ,0,0,'','' ,'User Defined Field');
97 INSERT INTO layout_options VALUES ('DEM','squad' ,'1Who','Squad' ,15,13,0, 0, 0,'' ,1,3,'','' ,'Squad Membership');
98 INSERT INTO layout_options VALUES ('DEM','pricelevel' ,'1Who','Price Level' ,16, 1,0, 0, 0,'pricelevel',1,1,'','' ,'Discount Level');
100 INSERT INTO layout_options VALUES ('DEM','street' ,'2Contact','Address' , 1, 2,1,25,63,'' ,1,1,'','C','Street and Number');
101 INSERT INTO layout_options VALUES ('DEM','city' ,'2Contact','City' , 2, 2,1,15,63,'' ,1,1,'','C','City Name');
102 INSERT INTO layout_options VALUES ('DEM','state' ,'2Contact','State' , 3, 2,1,15,63,'' ,1,1,'','C','State/Locality');
103 INSERT INTO layout_options VALUES ('DEM','postal_code' ,'2Contact','Postal Code' , 4, 2,1, 6,63,'' ,1,1,'','' ,'Postal Code');
104 INSERT INTO layout_options VALUES ('DEM','country_code' ,'2Contact','Country' , 5, 2,1,10,63,'' ,1,1,'','C','Country');
105 INSERT INTO layout_options VALUES ('DEM','contact_relationship','2Contact','Emergency Contact', 6, 2,1,10,63,'' ,1,1,'','C','Emergency Contact Person');
106 INSERT INTO layout_options VALUES ('DEM','phone_contact' ,'2Contact','Emergency Phone' , 7, 2,1,20,63,'' ,1,1,'','P','Emergency Contact Phone Number');
107 INSERT INTO layout_options VALUES ('DEM','phone_home' ,'2Contact','Home Phone' , 8, 2,1,20,63,'' ,1,1,'','P','Home Phone Number');
108 INSERT INTO layout_options VALUES ('DEM','phone_biz' ,'2Contact','Work Phone' , 9, 2,1,20,63,'' ,1,1,'','P','Work Phone Number');
109 INSERT INTO layout_options VALUES ('DEM','phone_cell' ,'2Contact','Mobile Phone' ,10, 2,1,20,63,'' ,1,1,'','P','Cell Phone Number');
110 INSERT INTO layout_options VALUES ('DEM','email' ,'2Contact','Contact Email' ,11, 2,1,30,95,'' ,1,1,'','' ,'Contact Email Address');
112 INSERT INTO layout_options VALUES ('DEM','providerID' ,'3Choices','Provider' , 1,11,2, 0, 0,'' ,1,3,'','' ,'Referring Provider');
113 INSERT INTO layout_options VALUES ('DEM','pharmacy_id' ,'3Choices','Pharmacy' , 2,12,1, 0, 0,'' ,1,3,'','' ,'Preferred Pharmacy');
114 INSERT INTO layout_options VALUES ('DEM','hipaa_notice' ,'3Choices','HIPAA Notice Received', 3, 1,1, 0, 0,'yesno' ,1,1,'','' ,'Did you receive a copy of the HIPAA Notice?');
115 INSERT INTO layout_options VALUES ('DEM','hipaa_voice' ,'3Choices','Allow Voice Message' , 4, 1,1, 0, 0,'yesno' ,1,1,'','' ,'Allow telephone messages?');
116 INSERT INTO layout_options VALUES ('DEM','hipaa_mail' ,'3Choices','Allow Mail Message' , 5, 1,1, 0, 0,'yesno' ,1,1,'','' ,'Allow email messages?');
117 INSERT INTO layout_options VALUES ('DEM','hipaa_message','3Choices','Leave Message With' , 6, 2,1,20,63,'' ,1,1,'','' ,'With whom may we leave a message?');
119 INSERT INTO layout_options VALUES ('DEM','occupation' ,'4Employer','Occupation' , 1, 2,1,20,63,'' ,1,1,'','C','Occupation');
120 INSERT INTO layout_options VALUES ('DEM','em_name' ,'4Employer','Employer Name' , 2, 2,1,20,63,'' ,1,1,'','C','Employer Name');
121 INSERT INTO layout_options VALUES ('DEM','em_street' ,'4Employer','Employer Address', 3, 2,1,25,63,'' ,1,1,'','C','Street and Number');
122 INSERT INTO layout_options VALUES ('DEM','em_city' ,'4Employer','City' , 4, 2,1,15,63,'' ,1,1,'','C','City Name');
123 INSERT INTO layout_options VALUES ('DEM','em_state' ,'4Employer','State' , 5, 2,1,15,63,'' ,1,1,'','C','State/Locality');
124 INSERT INTO layout_options VALUES ('DEM','em_postal_code','4Employer','Postal Code' , 6, 2,1, 6,63,'' ,1,1,'','' ,'Postal Code');
125 INSERT INTO layout_options VALUES ('DEM','em_country' ,'4Employer','Country' , 7, 2,1,10,63,'' ,1,1,'','C','Country');
127 INSERT INTO layout_options VALUES ('DEM','language' ,'5Stats','Language' , 1, 1,1, 0, 0,'language',1,1,'','' ,'Preferred Language');
128 INSERT INTO layout_options VALUES ('DEM','ethnoracial' ,'5Stats','Race/Ethnicity' , 2, 1,1, 0, 0,'ethrace' ,1,1,'','' ,'Ethnicity or Race');
129 INSERT INTO layout_options VALUES ('DEM','financial_review','5Stats','Financial Review Date', 3, 2,1,10,10,'' ,1,1,'','D','Financial Review Date');
130 INSERT INTO layout_options VALUES ('DEM','family_size' ,'5Stats','Family Size' , 4, 2,1,20,63,'' ,1,1,'','' ,'Family Size');
131 INSERT INTO layout_options VALUES ('DEM','monthly_income' ,'5Stats','Monthly Income' , 5, 2,1,20,63,'' ,1,1,'','' ,'Monthly Income');
132 INSERT INTO layout_options VALUES ('DEM','homeless' ,'5Stats','Homeless, etc.' , 6, 2,1,20,63,'' ,1,1,'','' ,'Homeless or similar?');
133 INSERT INTO layout_options VALUES ('DEM','interpretter' ,'5Stats','Interpreter' , 7, 2,1,20,63,'' ,1,1,'','' ,'Interpreter needed?');
134 INSERT INTO layout_options VALUES ('DEM','migrantseasonal' ,'5Stats','Migrant/Seasonal' , 8, 2,1,20,63,'' ,1,1,'','' ,'Migrant or seasonal worker?');
136 INSERT INTO layout_options VALUES ('DEM','usertext1' ,'6Misc','User Defined Text 1' , 1, 2,0,10,63,'' ,1,1,'','' ,'User Defined');
137 INSERT INTO layout_options VALUES ('DEM','usertext2' ,'6Misc','User Defined Text 2' , 2, 2,0,10,63,'' ,1,1,'','' ,'User Defined');
138 INSERT INTO layout_options VALUES ('DEM','userlist1' ,'6Misc','User Defined List 1' , 3, 1,0, 0, 0,'userlist1',1,1,'','' ,'User Defined');
139 INSERT INTO layout_options VALUES ('DEM','userlist2' ,'6Misc','User Defined List 2' , 4, 1,0, 0, 0,'userlist2',1,1,'','' ,'User Defined');
141 ALTER TABLE transactions
142 ADD `refer_date` date DEFAULT NULL,
143 ADD `refer_from` int(11) NOT NULL DEFAULT 0,
144 ADD `refer_to` int(11) NOT NULL DEFAULT 0,
145 ADD `refer_diag` varchar(255) NOT NULL DEFAULT '',
146 ADD `refer_risk_level` varchar(255) NOT NULL DEFAULT '',
147 ADD `refer_vitals` tinyint(1) NOT NULL DEFAULT 0,
148 ADD `reply_date` date DEFAULT NULL,
149 ADD `reply_from` varchar(255) NOT NULL DEFAULT '',
150 ADD `reply_init_diag` varchar(255) NOT NULL DEFAULT '',
151 ADD `reply_final_diag` varchar(255) NOT NULL DEFAULT '',
152 ADD `reply_documents` varchar(255) NOT NULL DEFAULT '',
153 ADD `reply_findings` text NOT NULL DEFAULT '',
154 ADD `reply_services` text NOT NULL DEFAULT '',
155 ADD `reply_recommend` text NOT NULL DEFAULT '',
156 ADD `reply_rx_refer` text NOT NULL DEFAULT '';
158 INSERT INTO layout_options VALUES ('REF','refer_date' ,'','Referral Date' , 1, 4,2, 0, 0,'' ,1,1,'C','D','Date of referral');
159 INSERT INTO layout_options VALUES ('REF','refer_from' ,'','Refer By' , 2,10,2, 0, 0,'' ,1,1,'' ,'' ,'Referral By');
160 INSERT INTO layout_options VALUES ('REF','refer_to' ,'','Refer To' , 3,14,2, 0, 0,'' ,1,1,'' ,'' ,'Referral To');
161 INSERT INTO layout_options VALUES ('REF','body' ,'','Reason' , 4, 3,2,30, 3,'' ,1,1,'' ,'' ,'Reason for referral');
162 INSERT INTO layout_options VALUES ('REF','refer_diag' ,'','Referrer Diagnosis' , 5, 2,1,30,255,'' ,1,1,'' ,'X','Referrer diagnosis');
163 INSERT INTO layout_options VALUES ('REF','refer_risk_level','','Risk Level' , 6, 1,1, 0, 0,'risklevel',1,1,'' ,'' ,'Level of urgency');
164 INSERT INTO layout_options VALUES ('REF','refer_vitals' ,'','Include Vitals' , 7, 1,1, 0, 0,'boolean' ,1,1,'' ,'' ,'Include vitals data?');
165 INSERT INTO layout_options VALUES ('REF','reply_date' ,'','Reply Date' , 8, 4,1, 0, 0,'' ,1,1,'' ,'D','Date of reply');
166 INSERT INTO layout_options VALUES ('REF','reply_from' ,'','Reply From' , 9, 2,1,30,255,'' ,1,1,'' ,'' ,'Who replied?');
167 INSERT INTO layout_options VALUES ('REF','reply_init_diag' ,'','Presumed Diagnosis' ,10, 2,1,30,255,'' ,1,1,'' ,'' ,'Presumed diagnosis by specialist');
168 INSERT INTO layout_options VALUES ('REF','reply_final_diag','','Final Diagnosis' ,11, 2,1,30,255,'' ,1,1,'' ,'' ,'Final diagnosis by specialist');
169 INSERT INTO layout_options VALUES ('REF','reply_documents' ,'','Documents' ,12, 2,1,30,255,'' ,1,1,'' ,'' ,'Where may related scanned or paper documents be found?');
170 INSERT INTO layout_options VALUES ('REF','reply_findings' ,'','Findings' ,13, 3,1,30, 3,'' ,1,1,'' ,'' ,'Findings by specialist');
171 INSERT INTO layout_options VALUES ('REF','reply_services' ,'','Services Provided' ,14, 3,1,30, 3,'' ,1,1,'' ,'' ,'Service provided by specialist');
172 INSERT INTO layout_options VALUES ('REF','reply_recommend' ,'','Recommendations' ,15, 3,1,30, 3,'' ,1,1,'' ,'' ,'Recommendations by specialist');
173 INSERT INTO layout_options VALUES ('REF','reply_rx_refer' ,'','Prescriptions/Referrals',16, 3,1,30, 3,'' ,1,1,'' ,'' ,'Prescriptions and/or referrals by specialist');
175 INSERT INTO list_options VALUES ('risklevel','low' ,'Low' ,1,0,0);
176 INSERT INTO list_options VALUES ('risklevel','medium','Medium',2,1,0);
177 INSERT INTO list_options VALUES ('risklevel','high' ,'High' ,3,0,0);
179 INSERT INTO list_options VALUES ('boolean','0','No' ,1,0,0);
180 INSERT INTO list_options VALUES ('boolean','1','Yes',2,0,0);
183 ADD related_code varchar(10) NOT NULL DEFAULT '' COMMENT 'may reference a related codes.code';
185 CREATE TABLE prices (
186 pr_id varchar(11) NOT NULL COMMENT 'references codes.id or drugs.id',
187 pr_selector varchar(15) NOT NULL DEFAULT '' COMMENT 'template selector for drugs, empty for codes',
188 pr_level varchar(31) NOT NULL DEFAULT '' COMMENT 'price level',
189 pr_price decimal(12,2) NOT NULL DEFAULT 0 COMMENT 'price in local currency',
190 PRIMARY KEY (pr_id, pr_selector, pr_level)
193 INSERT INTO prices ( pr_id, pr_level, pr_price )
194 SELECT codes.id, 'standard', codes.fee FROM codes
195 WHERE codes.fee IS NOT NULL AND codes.fee > 0;
198 ADD taxrates varchar(255) NOT NULL DEFAULT '' COMMENT 'tax rate names delimited by colons';
200 ALTER TABLE drug_templates
201 ADD taxrates varchar(255) NOT NULL DEFAULT '' COMMENT 'tax rate names delimited by colons';
203 ALTER TABLE drug_sales
204 ADD billed tinyint(1) NOT NULL DEFAULT 0 COMMENT 'indicates if the sale is posted to accounting';
206 SET billed = 1 WHERE encounter > 0;
208 ALTER TABLE form_encounter
209 ADD pc_catid int(11) NOT NULL DEFAULT 5 COMMENT 'event category from openemr_postcalendar_categories';
211 CREATE TABLE fee_sheet_options (
212 fs_category varchar(63) NOT NULL DEFAULT '' COMMENT 'Descriptive category name',
213 fs_option varchar(63) NOT NULL DEFAULT '' COMMENT 'Descriptive option name',
214 fs_codes varchar(255) NOT NULL DEFAULT '' COMMENT 'multiple instances of type:id:selector;'
216 INSERT INTO fee_sheet_options VALUES ('1New Patient','1Brief' ,'CPT4|99201|');
217 INSERT INTO fee_sheet_options VALUES ('1New Patient','2Limited','CPT4|99202|');
218 INSERT INTO fee_sheet_options VALUES ('1New Patient','3Detailed','CPT4|99203|');
219 INSERT INTO fee_sheet_options VALUES ('1New Patient','4Extended','CPT4|99204|');
220 INSERT INTO fee_sheet_options VALUES ('1New Patient','5Comprehensive','CPT4|99205|');
221 INSERT INTO fee_sheet_options VALUES ('2Established Patient','1Brief' ,'CPT4|99211|');
222 INSERT INTO fee_sheet_options VALUES ('2Established Patient','2Limited','CPT4|99212|');
223 INSERT INTO fee_sheet_options VALUES ('2Established Patient','3Detailed','CPT4|99213|');
224 INSERT INTO fee_sheet_options VALUES ('2Established Patient','4Extended','CPT4|99214|');
225 INSERT INTO fee_sheet_options VALUES ('2Established Patient','5Comprehensive','CPT4|99215|');
228 ADD COLUMN `cal_ui` tinyint(4) NOT NULL DEFAULT 1 COMMENT 'Specifies calendar display style';