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 exists but the column does not, the block will be executed
17 -- arguments: table_name colname value
18 -- 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
21 -- arguments: table_name colname value
22 -- behavior: If the table table_name does not have a row where colname = value, the block will be executed.
25 -- arguments: table_name colname value colname2 value2
26 -- behavior: If the table table_name does not have a row where colname = value AND colname2 = value2, the block will be executed.
29 -- arguments: table_name colname value colname2 value2 colname3 value3
30 -- 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.
33 -- arguments: table_name colname value colname2 value2 colname3 value3 colname4 value4
34 -- 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.
37 -- 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.
38 -- arguments: table_name colname value colname2 value2 colname3 value3
39 -- behavior: The block will be executed if both statements below are true:
40 -- 1) The table table_name does not have a row where colname = value AND colname2 = value2.
41 -- 2) The table table_name does not have a row where colname = value AND colname3 = value3.
44 -- arguments: table_name colname value colname2 value2
45 -- behavior: If the table table_name does have a row where colname = value AND colname2 = value2, the block will be executed.
48 -- desc: This function is most often used for dropping of indexes/keys.
49 -- arguments: table_name colname
50 -- behavior: If the table and index exist the relevant statements are executed, otherwise not.
53 -- desc: This function will allow adding of indexes/keys.
54 -- arguments: table_name colname
55 -- behavior: If the index does not exist, it will be created
58 -- all blocks are terminated with a #EndIf statement.
60 #IfNotTable report_results
61 CREATE TABLE `report_results` (
62 `report_id` bigint(20) NOT NULL,
63 `field_id` varchar(31) NOT NULL default '',
65 PRIMARY KEY (`report_id`,`field_id`)
69 #IfMissingColumn version v_acl
70 ALTER TABLE `version` ADD COLUMN `v_acl` int(11) NOT NULL DEFAULT 0;
73 #IfMissingColumn documents_legal_detail dld_moved
74 ALTER TABLE `documents_legal_detail` ADD COLUMN `dld_moved` tinyint(4) NOT NULL DEFAULT '0';
78 #IfMissingColumn documents_legal_detail dld_patient_comments
79 ALTER TABLE `documents_legal_detail` ADD COLUMN `dld_patient_comments` text COMMENT 'Patient comments stored here';
82 #IfMissingColumn documents_legal_master dlm_upload_type
83 ALTER TABLE `documents_legal_master` ADD COLUMN `dlm_upload_type` tinyint(4) DEFAULT '0' COMMENT '0-Provider Uploaded,1-Patient Uploaded';
86 #IfMissingColumn list_options codes
87 ALTER TABLE `list_options` ADD COLUMN `codes` varchar(255) NOT NULL DEFAULT '';
88 UPDATE list_options SET `codes`='SNOMED-CT:449868002' WHERE list_id='smoking_status' AND option_id='1' AND title='Current every day smoker';
89 UPDATE list_options SET `codes`='SNOMED-CT:428041000124106' WHERE list_id='smoking_status' AND option_id='2' AND title='Current some day smoker';
90 UPDATE list_options SET `codes`='SNOMED-CT:8517006' WHERE list_id='smoking_status' AND option_id='3' AND title='Former smoker';
91 UPDATE list_options SET `codes`='SNOMED-CT:266919005' WHERE list_id='smoking_status' AND option_id='4' AND title='Never smoker';
92 UPDATE list_options SET `codes`='SNOMED-CT:77176002' WHERE list_id='smoking_status' AND option_id='5' AND title='Smoker, current status unknown';
93 UPDATE list_options SET `codes`='SNOMED-CT:266927001' WHERE list_id='smoking_status' AND option_id='9' AND title='Unknown if ever smoked';
96 #IfNotRow2Dx2 list_options list_id smoking_status option_id 15 title Heavy tobacco smoker
97 INSERT INTO list_options ( list_id, option_id, title, seq, is_default, codes ) VALUES ('smoking_status', '15', 'Heavy tobacco smoker', 70, 0, "SNOMED-CT:428071000124103");
100 #IfNotRow2Dx2 list_options list_id smoking_status option_id 16 title Light tobacco smoker
101 INSERT INTO list_options ( list_id, option_id, title, seq, is_default, codes ) VALUES ('smoking_status', '16', 'Light tobacco smoker', 80, 0, "SNOMED-CT:428061000124105");
104 #IfMissingColumn code_types ct_term
105 ALTER TABLE `code_types` ADD COLUMN ct_term tinyint(1) NOT NULL default 0 COMMENT '1 if this is a clinical term';
108 #IfNotRow code_types ct_key SNOMED-CT
109 DROP TABLE IF EXISTS `temp_table_one`;
110 CREATE TABLE `temp_table_one` (
111 `id` int(11) NOT NULL DEFAULT '0',
112 `seq` int(11) NOT NULL DEFAULT '0'
114 INSERT INTO `temp_table_one` (`id`, `seq`) VALUES ( IF( ((SELECT MAX(`ct_id`) FROM `code_types`)>=100), ((SELECT MAX(`ct_id`) FROM `code_types`) + 1), 100 ) , IF( ((SELECT MAX(`ct_seq`) FROM `code_types`)>=100), ((SELECT MAX(`ct_seq`) FROM `code_types`) + 1), 100 ) );
115 INSERT INTO code_types (ct_key, ct_id, ct_seq, ct_mod, ct_just, ct_fee, ct_rel, ct_nofs, ct_diag, ct_active, ct_label, ct_external, ct_claim, ct_proc, ct_term ) VALUES ('SNOMED-CT' , (SELECT MAX(`id`) FROM `temp_table_one`), (SELECT MAX(`seq`) FROM `temp_table_one`), 0, '', 0, 0, 0, 0, 0, 'SNOMED Clinical Term', 7, 0, 0, 1);
116 DROP TABLE `temp_table_one`;
119 #IfNotColumnType codes code varchar(25)
120 ALTER TABLE `codes` CHANGE `code` `code` varchar(25) NOT NULL default '';
123 #IfNotColumnType billing code varchar(20)
124 ALTER TABLE `billing` CHANGE `code` `code` varchar(20) default NULL;
127 #IfNotColumnType ar_activity code varchar(20)
128 ALTER TABLE `ar_activity` CHANGE `code` `code` varchar(20) NOT NULL COMMENT 'empty means claim level';