myportal/soap_service/server_oemruser.php

   1 <?php
   2 // +-----------------------------------------------------------------------------+
   3 // Copyright (C) 2011 Z&H Consultancy Services Private Limited <sam@zhservices.com>
   4 //
   5 //
   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.
  10 //
  11 //
  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.
  16 //
  17 //
  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.
  22 //
  23 // Author:   Eldho Chacko <eldho@zhservices.com>
  24 //           Jacob T Paul <jacob@zhservices.com>
  25 //
  26 // +------------------------------------------------------------------------------+
  27
  28 //SANITIZE ALL ESCAPES
  29 $sanitize_all_escapes=true;
  30 //
  31
  32 //STOP FAKE REGISTER GLOBALS
  33 $fake_register_globals=false;
  34 //
  35
  36 class OEMRUser{
  37
  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
  40
  41     private function getPid($id){
  42         $row = sqlQuery("SELECT pid FROM audit_master WHERE id=? AND approval_status=1",array($id));
  43         return $row['pid'];
  44     }
  45
  46     public function query_formation($data){
  47         global $pid;
  48         switch($data[0]){
  49             case 'U1':
  50             $query="select * from  audit_master where  approval_status='1'   and  (type='1' or type='2' or type='3') ";
  51             return array($query);
  52             break;
  53
  54             case 'U2':
  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));
  57             break;
  58
  59             case 'U3':
  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));
  68             break;
  69
  70             // Entries pending  for approval for Existing Patient and New Patient.
  71             case 'U4':
  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));
  75             break;
  76
  77             // Entries pending  for approval for  documents only (no demo change).
  78             case 'U5':
  79             $pid = $this->getPid($data[1]);
  80                                                 if($pid){
  81                                                                 $query = " select * from  audit_master  where pid=? and  approval_status='1' and  type='3' ";
  82                                                                 return array($query,array($pid));
  83                                                 }else{
  84                                                                 $query = " select * from  audit_master where id=? and approval_status='1' and type='3'";
  85                                                                 return array($query,array($data[1]));
  86                                                 }
  87             break;
  88
  89             case 'P1':
  90             $query= "select MAX(pid)+1 AS pid  from patient_data ";
  91
  92             return array($query);
  93             break;
  94
  95             //for building patient Demo
  96             case 'P2':
  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);
 101             break;
 102
 103             //for building patient Demo   Date of Birth
 104             case 'P3':
 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));
 108             break;
 109
 110            //for building patient Demo   Employer Data
 111             case 'P4':
 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));
 115             break;
 116
 117            //for building patient Demo   Insurance company details for Patient
 118             case 'P5':
 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]);
 124             break;
 125
 126                         // Entries pending  for approval demo and documents.
 127             case 'P6':
 128                                                 $pid = $this->getPid($data[1]);
 129                                                 if($pid){
 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));
 133                                                 }else{
 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]));
 137                                                 }
 138
 139             break;
 140             // Demo building from layout options.
 141
 142             case 'P7':
 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);
 146             break;
 147
 148             //Global specific application for building demo.
 149             case 'P8':
 150             $query="select * from  globals where gl_name ='specific_application' ";
 151             return array($query);
 152             break;
 153
 154             //Global omit employers for building demo.
 155             case 'P9':
 156             $query=" select * from globals where gl_name ='omit_employers' ";
 157             return array($query);
 158             break;
 159
 160             case 'B13':
 161             //patient appointment
 162             $query="select * from facility where service_location != 0 order by name";
 163             return array($query);
 164             break;
 165
 166             case 'E1':
 167             //list of approvals
 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);
 190             break;
 191
 192             case 'E2':
 193             //list of approvals
 194             $query="select * from facility";
 195             return array($query);
 196             break;
 197
 198             case 'E3':
 199             //list of approvals
 200             $query="select id,fname,lname,mname from users where authorized=1";
 201             return array($query);
 202             break;
 203
 204             case 'E4':
 205             //list of approvals
 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);
 209             break;
 210
 211             case 'E5':
 212             //list of approvals
 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']));
 224             break;
 225
 226             case 'F4':
 227             //signing
 228             $query="select * from documents_legal_categories where dlc_category_name=? and dlc_category_type=2";
 229             return array($query,$data[1]);
 230             break;
 231
 232             case 'F5':
 233             //signing
 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]);
 237             break;
 238
 239             case 'F8':
 240             //signing
 241             $pid = $this->getPid($data[1]);
 242                                                 if($pid){
 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));
 245                                                 }else{
 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]));
 248                                                 }
 249             break;
 250
 251             case 'F12':
 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]);
 255             break;
 256
 257             case 'payment_settings_all':
 258             $query = "SELECT service_name,login_id,transaction_key,md5 FROM payment_gateway_details";
 259             return array($query);
 260             break;
 261
 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);
 265             break;
 266
 267             case 'F13':
 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]."%"));
 272         }
 273     }
 274 }
 275 ?>