2 // +-----------------------------------------------------------------------------+
3 // Copyright (C) 2011 Z&H Consultancy Services Private Limited <sam@zhservices.com>
6 // This program is free software; you can redistribute it and/or
7 // modify it under the terms of the GNU General Public License
8 // as published by the Free Software Foundation; either version 2
9 // of the License, or (at your option) any later version.
12 // This program is distributed in the hope that it will be useful,
13 // but WITHOUT ANY WARRANTY; without even the implied warranty of
14 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 // GNU General Public License for more details.
18 // A copy of the GNU General Public License is included along with this program:
19 // openemr/interface/login/GnuGPL.html
20 // For more information write to the Free Software
21 // Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
23 // Author: Eldho Chacko <eldho@zhservices.com>
24 // Jacob T Paul <jacob@zhservices.com>
26 // +------------------------------------------------------------------------------+
28 //SANITIZE ALL ESCAPES
29 $sanitize_all_escapes=true;
32 //STOP FAKE REGISTER GLOBALS
33 $fake_register_globals=false;
38 //this will return the query string along with the parameter array, according to the case case.
39 //actual execution is done in the select_query function in Server_side
41 private function getPid($id){
42 $row = sqlQuery("SELECT pid FROM audit_master WHERE id=? AND approval_status=1",array($id));
46 public function query_formation($data){
50 $query="select * from audit_master where approval_status='1' and (type='1' or type='2' or type='3') ";
55 $query=" select * from audit_master where approval_status='1' and (type='1' or type='2' or type='3') order by id limit ?,1";
56 return array($query,array($data[1][0]-1));
60 $pid = $this->getPid($data[1]);
61 $query="SELECT * FROM documents_legal_master AS dlm " .
62 "LEFT OUTER JOIN documents_legal_detail as dld ON dlm_document_id=dld_master_docid WHERE " .
63 " dlm_subcategory not in (SELECT dlc_id FROM `documents_legal_categories` where dlc_category_name='Layout Signed'".
64 " and dlc_category_type=2) and dlm_effective_date <= now() AND dlm_effective_date<>'0000-00-00 00:00:00' " .
65 "AND dld_id IS NOT NULL AND dld_pid=? and dld_signed='0' " .
66 "ORDER BY dlm_effective_date DESC";
67 return array($query,array($pid));
70 // Entries pending for approval for Existing Patient and New Patient.
72 $pid = $this->getPid($data[1]);
73 $query= "select * from audit_master where pid=? and approval_status='1' and (type='1' or type='2')";
74 return array($query,array($pid));
77 // Entries pending for approval for documents only (no demo change).
79 $pid = $this->getPid($data[1][0]);
81 $query = " select * from audit_master where pid=? and approval_status='1' and type='3' ";
82 return array($query,array($pid));
84 $query = " select * from audit_master where id=? and approval_status='1' and type='3'";
85 return array($query,array($data[1][0]));
90 $query= "select MAX(pid)+1 AS pid from patient_data ";
95 //for building patient Demo
97 $query="select * from layout_options " .
98 "WHERE form_id = 'DEM' AND uor > 0 AND field_id != '' " .
99 "ORDER BY group_name, seq";
100 return array($query);
103 //for building patient Demo Date of Birth
105 $pid = $this->getPid($data[1]);
106 $query="select *, DATE_FORMAT(DOB,'%Y-%m-%d') as DOB_YMD from patient_data where pid=? order by date DESC limit 0,1 ";
107 return array($query,array($pid));
110 //for building patient Demo Employer Data
112 $pid = $this->getPid($data[1]);
113 $query="select * from employer_data where pid=? order by date DESC limit 0,1 ";
114 return array($query,array($pid));
117 //for building patient Demo Insurance company details for Patient
119 $data[1][0] = $this->getPid($data[1][0]);
120 $query=" select insd.*, ic.name as provider_name from insurance_data as insd " .
121 "left join insurance_companies as ic on ic.id = insd.provider " .
122 "where pid = ? and type =? order by date DESC limit 1 ";
123 return array($query,$data[1]);
126 // Entries pending for approval demo and documents.
128 $pid = $this->getPid($data[1]);
130 $query=" select * from audit_master as am,audit_details as ad WHERE am.id=ad.audit_master_id and am.pid=? and am.approval_status='1'
131 and (am.type='1' or am.type='2' or am.type='3') order by ad.id";
132 return array($query,array($pid));
134 $query=" select * from audit_master as am,audit_details as ad WHERE am.id=ad.audit_master_id and am.id=? and am.approval_status='1'
135 and (am.type='1' or am.type='2' or am.type='3') order by ad.id";
136 return array($query,array($data[1]));
140 // Demo building from layout options.
143 $query=" select * from layout_options WHERE form_id = 'DEM' AND uor > 0 AND field_id != '' " .
144 " ORDER BY group_name, seq";
145 return array($query);
148 //Global specific application for building demo.
150 $query="select * from globals where gl_name ='specific_application' ";
151 return array($query);
154 //Global omit employers for building demo.
156 $query=" select * from globals where gl_name ='omit_employers' ";
157 return array($query);
161 //patient appointment
162 $query="select * from facility where service_location != 0 order by name";
163 return array($query);
168 $query = "SELECT CASE WHEN am.type = 3 AND am.pid = 0 THEN CONCAT('am-', am.id) ELSE am.id END AS audit_master_id, am.*, COALESCE(
169 pd.lname,(SELECT field_value FROM audit_details WHERE audit_master_id = am.id AND table_name = 'patient_data' AND field_name = 'lname'),
170 (SELECT ad2.field_value FROM audit_details ad JOIN audit_details ad1 ON ad1.table_name = 'patient_access_offsite' AND
171 ad1.field_name = 'portal_username' AND ad1.field_value = ad.field_value JOIN audit_details ad2 ON ad2.table_name = 'patient_data'
172 AND ad2.field_name = 'lname' AND ad2.audit_master_id = ad1.audit_master_id WHERE ad.audit_master_id = am.id AND
173 ad.table_name = 'patient_access_offsite' AND ad.field_name = 'portal_username')) AS lname,COALESCE(pd.fname,(SELECT field_value
174 FROM audit_details WHERE audit_master_id = am.id AND table_name = 'patient_data' AND field_name = 'fname'),(SELECT ad2.field_value
175 FROM audit_details ad JOIN audit_details ad1 ON ad1.table_name = 'patient_access_offsite' AND ad1.field_name = 'portal_username' AND
176 ad1.field_value = ad.field_value JOIN audit_details ad2 ON ad2.table_name = 'patient_data' AND ad2.field_name = 'fname' AND
177 ad2.audit_master_id = ad1.audit_master_id WHERE ad.audit_master_id = am.id AND ad.table_name = 'patient_access_offsite' AND
178 ad.field_name = 'portal_username')) AS fname,COALESCE(pd.mname,(SELECT field_value FROM audit_details WHERE audit_master_id = am.id
179 AND table_name = 'patient_data' AND field_name = 'mname'),(SELECT ad2.field_value FROM audit_details ad JOIN audit_details ad1 ON
180 ad1.table_name = 'patient_access_offsite' AND ad1.field_name = 'portal_username' AND ad1.field_value = ad.field_value
181 JOIN audit_details ad2 ON ad2.table_name = 'patient_data' AND ad2.field_name = 'mname' AND ad2.audit_master_id = ad1.audit_master_id
182 WHERE ad.audit_master_id = am.id AND ad.table_name = 'patient_access_offsite' AND ad.field_name = 'portal_username')) AS mname,
183 COALESCE(pd.dob,(SELECT field_value FROM audit_details WHERE audit_master_id = am.id AND table_name = 'patient_data' AND
184 field_name = 'dob'),(SELECT ad2.field_value FROM audit_details ad JOIN audit_details ad1 ON ad1.table_name = 'patient_access_offsite'
185 AND ad1.field_name = 'portal_username' AND ad1.field_value = ad.field_value JOIN audit_details ad2 ON ad2.table_name = 'patient_data'
186 AND ad2.field_name = 'dob' AND ad2.audit_master_id = ad1.audit_master_id WHERE ad.audit_master_id = am.id AND
187 ad.table_name = 'patient_access_offsite' AND ad.field_name = 'portal_username')) AS DOB FROM audit_master am LEFT JOIN patient_data pd
188 ON am.pid = pd.pid WHERE am.approval_status = '1' ORDER BY am.id";
189 return array($query);
194 $query="select * from facility";
195 return array($query);
200 $query="select id,fname,lname,mname from users where authorized=1";
201 return array($query);
206 $query="select * from audit_master,patient_data,audit_details where audit_master.pid=patient_data.pid and
207 audit_master.approval_status='1' and audit_master.type = 10 and audit_master_id=audit_master.id order by audit_master.id";
208 return array($query);
213 $query="select * from audit_master where audit_master.id=?";
214 $row = sqlQuery($query,$data[1]);
215 return array("SELECT ad3.field_value AS dld_filename, dlm.dlm_document_id, CONCAT('am-',ad.audit_master_id) AS dld_id, dlm.dlm_document_name
216 FROM audit_details ad JOIN audit_details ad2 ON ad2.table_name = 'documents_legal_detail' AND ad2.field_name = 'dld_signed'
217 AND ad2.field_value = '0' AND ad2.audit_master_id = ad.audit_master_id JOIN audit_details ad3 ON ad3.table_name = 'documents_legal_detail'
218 AND ad3.field_name = 'dld_filename' AND ad3.audit_master_id = ad.audit_master_id JOIN documents_legal_master dlm ON dlm.dlm_document_id = ad.field_value
219 WHERE ad.audit_master_id = ? AND ad.table_name = 'documents_legal_detail' AND dlm_subcategory NOT IN (SELECT dlc_id FROM `documents_legal_categories`
220 WHERE dlc_category_name = 'Layout Signed' AND dlc_category_type = 2) UNION SELECT dld_filename, dlm.dlm_document_id, dld.dld_id, dlm.dlm_document_name
221 FROM documents_legal_detail dld JOIN documents_legal_master dlm ON dld_master_docid = dlm_document_id WHERE dld_pid = ? AND dld_signed = '0'
222 AND dlm_document_id = dld_master_docid AND dlm_subcategory NOT IN (SELECT dlc_id FROM `documents_legal_categories` WHERE dlc_category_name = 'Layout Signed'
223 AND dlc_category_type = 2)",array($row['id'],$row['pid']));
228 $query="select * from documents_legal_categories where dlc_category_name=? and dlc_category_type=2";
229 return array($query,$data[1]);
234 $query="select * from documents_legal_master LEFT OUTER JOIN documents_legal_categories ON dlm_category=dlc_id WHERE
235 dlm_subcategory <> ? and dlm_filename<>'' and dlm_upload_type = 0";
236 return array($query,$data[1]);
241 $pid = $this->getPid($data[1][0]);
243 $query = " select * from audit_master where pid=? and approval_status='1' and (type='1' or type='2' or type='3')";
244 return array($query,array($pid));
246 $query = " select * from audit_master where id=? and approval_status='1' and (type='1' or type='2' or type='3')";
247 return array($query,array($data[1][0]));
252 //Selection from master document for showing to patient
253 $query="select * from documents_legal_master WHERE dlm_document_name=?";
254 return array($query,$data[1]);
257 case 'payment_settings_all':
258 $query = "SELECT service_name,login_id,transaction_key,md5 FROM payment_gateway_details";
259 return array($query);
262 case 'payment_gateways_list':
263 $query = "SELECT option_id, title FROM list_options WHERE list_id = 'payment_gateways' ORDER BY seq";
264 return array($query);
268 $query = "SELECT pid ,fname,lname,mname,DOB FROM patient_data
269 where fname like ? or lname like ? or mname like ? or
270 CONCAT(lname,' ',fname,' ',mname) like ? or pid like ? ORDER BY lname";
271 return array($query,array($data[1]."%",$data[1]."%",$data[1]."%",$data[1]."%",$data[1]."%"));